<?php
/**
 * Migration: create `affilun_products` table (schema version 1).
 *
 * Installs the canonical product snapshot table described in
 * `specifications/database-schema.md` §4 and §17.3. The table holds the
 * normalized product mastered from external providers (Amazon /
 * Rakuten / Yahoo!) and is the read model that powers front-end
 * rendering, the admin list view, and the re-sync decision loop.
 *
 * This migration is the first step in affilun's schema
 * pipeline and therefore targets {@see SchemaVersion::of(1)} — the
 * "initial release" revision numbered in `database-schema.md` §13.2.
 *
 * Scope of this migration:
 *
 *   - Creates `{$prefix}affilun_products` with the full column set defined
 *     in §4.2 (28 columns covering identity, descriptive fields,
 *     pricing, availability, URLs, raw payload snapshot, lifecycle and
 *     sync state axes, and audit timestamps).
 *
 *   - Adds the unique constraint on `(provider, provider_item_id)`
 *     that enforces the "one row per provider item" invariant and the
 *     six secondary indexes listed in §4.3 (canonical hash, last sync
 *     time, sync state, provider × sync state, lifecycle state,
 *     created_at).
 *
 *   - Uses the charset/collation returned by `$wpdb->get_charset_collate()`
 *     (per §17.2) and relies on `InnoDB` as the storage engine (§17.1).
 *
 * Scope explicitly NOT covered here:
 *
 *   - Foreign keys. Per §11.2 and §13.4, AFFILUN does not declare foreign
 *     keys at the DB level; referential integrity is enforced by the
 *     application layer.
 *
 *   - `DEFAULT CURRENT_TIMESTAMP` on `created_at` / `updated_at`. Per
 *     §13.4, environment-sensitive default clauses are avoided;
 *     timestamps are supplied by the application layer on insert /
 *     update.
 *
 *   - Any DML. This migration creates the empty table only; seed data
 *     and backfills belong in later migrations or runtime code paths.
 *
 * The shared DDL-assembly and installer-dispatch skeleton lives in
 * {@see AbstractCreateTableMigration}; this subclass only declares the
 * bits that actually differ between tables (target version, description,
 * logical name, column manifest, index manifest). See the base class's
 * file-level docblock for the idempotency and failure-propagation
 * guarantees inherited here.
 *
 * @package Affilun\Infrastructure\Persistence\Migrations
 */

declare(strict_types=1);

namespace Affilun\Infrastructure\Persistence\Migrations;

use Affilun\Infrastructure\Persistence\SchemaVersion;

/**
 * Creates the `affilun_products` table at schema version 1.
 *
 * Concrete {@see AbstractCreateTableMigration} subclass. See the
 * file-level docblock for the scope of this migration and the columns
 * and indexes it installs; see the base class for the formatting rules
 * and failure semantics that apply to every AFFILUN table-creation
 * migration.
 */
final class CreateProductsTable extends AbstractCreateTableMigration {

	/**
	 * Logical, unprefixed AFFILUN table name installed by this migration.
	 *
	 * Kept as a class-level constant so tests can reference the exact
	 * name without duplicating the string literal, and so a future
	 * rename has a single location to update.
	 */
	public const TABLE_LOGICAL_NAME = 'affilun_products';

	/**
	 * Target schema version installed by this migration.
	 *
	 * Returns version 1 — the "initial release" revision per
	 * `database-schema.md` §13.2.
	 */
	public function targetVersion(): SchemaVersion {
		return SchemaVersion::of( 1 );
	}

	/**
	 * Short, human-readable description of this migration.
	 *
	 * Used by the diagnostics surface and migration logs. Kept terse
	 * and operator-friendly per the
	 * {@see \Affilun\Infrastructure\Persistence\Migration::description()}
	 * contract.
	 */
	public function description(): string {
		return 'create affilun_products table';
	}

	/**
	 * Logical, unprefixed AFFILUN table name installed by this migration.
	 *
	 * Consumed by {@see AbstractCreateTableMigration::up()} and
	 * {@see AbstractCreateTableMigration::tableName()} to resolve the
	 * physical, prefix-applied name through the injected
	 * {@see \Affilun\Infrastructure\Persistence\TableNameResolver}.
	 */
	protected function logicalTableName(): string {
		return self::TABLE_LOGICAL_NAME;
	}

	/**
	 * Column definitions for `affilun_products`, in the canonical order
	 * defined by `database-schema.md` §4.2 / §17.3.
	 *
	 * Each entry is a single column definition without its trailing
	 * comma; the base class adds the indent and inter-column commas.
	 *
	 * @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',
		);
	}

	/**
	 * Index definitions for `affilun_products`, in the canonical order
	 * defined by `database-schema.md` §4.3 / §17.3.
	 *
	 * The `PRIMARY KEY` entry uses the two-space separator that
	 * `dbDelta()` relies on to detect the primary key line — this is
	 * a WordPress-specific formatting quirk, not a typo.
	 *
	 * @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)',
		);
	}
}