<?php
/**
 * Migration: add search-keyword columns to `affilun_products`.
 *
 * @package Affilun\Infrastructure\Persistence\Migrations
 */

declare(strict_types=1);

namespace Affilun\Infrastructure\Persistence\Migrations;

use Affilun\Infrastructure\Persistence\SchemaVersion;

/**
 * Adds cross-provider search-keyword columns to the products table.
 */
final class AddProductSearchKeywordsColumns extends AbstractCreateTableMigration {

	/**
	 * Target schema version.
	 */
	public function targetVersion(): SchemaVersion {
		return SchemaVersion::of( 8 );
	}

	/**
	 * Human-readable description.
	 */
	public function description(): string {
		return 'add product search keyword columns';
	}

	/**
	 * Logical table name.
	 */
	protected function logicalTableName(): string {
		return CreateProductsTable::TABLE_LOGICAL_NAME;
	}

	/**
	 * Product table columns, including the v8 additions.
	 *
	 * @return list<string>
	 */
	protected function columnDefinitions(): array {
		return array(
			'id bigint unsigned NOT NULL AUTO_INCREMENT',
			'provider varchar(32) NOT NULL',
			'provider_item_id varchar(191) NOT NULL',
			'canonical_hash char(64) NOT NULL',
			'title text NOT NULL',
			'brand varchar(191) NULL',
			'description longtext NULL',
			'image_primary_url text NULL',
			'image_gallery_json longtext NULL',
			'price_amount decimal(12,2) NULL',
			'price_currency varchar(8) NULL',
			'price_label varchar(191) NULL',
			'availability_status varchar(32) NULL',
			'detail_url text NOT NULL',
			'affiliate_url text NULL',
			'raw_payload_json longtext NULL',
			'search_keywords varchar(512) NULL',
			'search_keywords_source varchar(32) NULL',
			'snapshot_version int unsigned NOT NULL DEFAULT 1',
			"sync_state varchar(16) NOT NULL DEFAULT 'fresh'",
			"lifecycle_state varchar(16) NOT NULL DEFAULT 'active'",
			'archived_at datetime NULL',
			'last_synced_at datetime NULL',
			'last_rendered_at datetime NULL',
			'last_sync_attempted_at datetime NULL',
			'last_sync_error_code varchar(64) NULL',
			'last_sync_error_message text NULL',
			'created_at datetime NOT NULL',
			'updated_at datetime NOT NULL',
		);
	}

	/**
	 * Product table indexes.
	 *
	 * @return list<string>
	 */
	protected function indexDefinitions(): array {
		return array(
			'PRIMARY KEY  (id)',
			'UNIQUE KEY uq_provider_item (provider, provider_item_id)',
			'KEY idx_canonical_hash (canonical_hash)',
			'KEY idx_last_synced_at (last_synced_at)',
			'KEY idx_sync_state (sync_state)',
			'KEY idx_provider_sync_state (provider, sync_state)',
			'KEY idx_lifecycle_state (lifecycle_state)',
			'KEY idx_created_at (created_at)',
		);
	}
}
