<?php
/**
 * Migration: create Amazon discount product table.
 *
 * @package Affilun\Infrastructure\Persistence\Migrations
 */

declare(strict_types=1);

namespace Affilun\Infrastructure\Persistence\Migrations;

use Affilun\Infrastructure\Persistence\SchemaVersion;

/**
 * Creates the shortcode-only daily Amazon discount snapshot table.
 */
final class CreateAmazonDiscountProductsTable extends AbstractCreateTableMigration {

	public const TABLE_LOGICAL_NAME = 'affilun_amazon_discount_products';

	/**
	 * Return migration target version.
	 */
	public function targetVersion(): SchemaVersion {
		return SchemaVersion::of( 12 );
	}

	/**
	 * Return migration description.
	 */
	public function description(): string {
		return 'create Amazon discount product table';
	}

	/**
	 * Return the logical table name.
	 */
	protected function logicalTableName(): string {
		return self::TABLE_LOGICAL_NAME;
	}

	/**
	 * Return table columns.
	 *
	 * @return list<string>
	 */
	protected function columnDefinitions(): array {
		return array(
			'id bigint unsigned NOT NULL AUTO_INCREMENT',
			'search_index varchar(80) NOT NULL',
			'position int unsigned NOT NULL',
			'asin varchar(32) NOT NULL',
			'title text NOT NULL',
			'brand varchar(191) NULL',
			'detail_url text NOT NULL',
			'affiliate_url text NOT NULL',
			'image_url text NULL',
			'price_amount decimal(12,2) NULL',
			'price_currency varchar(8) NULL',
			'price_label varchar(191) NULL',
			'savings_percentage tinyint unsigned NULL',
			'min_saving_percent tinyint unsigned NOT NULL',
			'fetched_at datetime NOT NULL',
			'updated_at datetime NOT NULL',
		);
	}

	/**
	 * Return table indexes.
	 *
	 * @return list<string>
	 */
	protected function indexDefinitions(): array {
		return array(
			'PRIMARY KEY  (id)',
			'UNIQUE KEY uq_category_asin (search_index, asin)',
			'KEY idx_category_position (search_index, position)',
			'KEY idx_category_savings (search_index, savings_percentage)',
			'KEY idx_fetched_at (fetched_at)',
		);
	}
}
