<?php
/**
 * Migration: create `affilun_sync_jobs` table (schema version 5).
 *
 * Installs the background-job queue table described in
 * `specifications/database-schema.md` §8. The table holds pending,
 * running, failed, completed, and cancelled jobs for product re-sync,
 * render-cache rebuilds, usage-index recalculation, and log cleanup
 * (§8.4). Per §8.1 and ADR-0021, AFFILUN uses its own `affilun_sync_jobs`
 * table combined with WP-Cron as the canonical job substrate; Action
 * Scheduler is deliberately NOT adopted.
 *
 * This migration targets {@see SchemaVersion::of(5)} — the fifth step
 * in affilun's schema pipeline, installed after
 * `affilun_products` (v1), `affilun_product_meta` (v2), `affilun_embeds` (v3), and
 * `affilun_embed_items` (v4).
 *
 * Scope of this migration:
 *
 *   - Creates `{$prefix}affilun_sync_jobs` with the full column set defined
 *     in §8.2 (fifteen columns covering identity, job type, payload,
 *     status, priority and retry counters, scheduling and locking
 *     timestamps, finish time, error summary, and audit timestamps).
 *
 *   - Adds the four secondary indexes listed in §8.3
 *     (`idx_status_available`, `idx_job_type_status`, `idx_locked_at`,
 *     `idx_priority_available`) that support the worker's
 *     "pick next runnable job" query pattern described in §8.5.
 *
 *   - 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:
 *
 *   - `CHECK (status IN (...))` constraint. §8.2 enumerates the
 *     allowed status values (`pending`, `running`, `failed`,
 *     `completed`, `cancelled`), but `dbDelta()` does not reliably
 *     round-trip `CHECK` constraints across MySQL / MariaDB versions
 *     (per §13.4). Application-layer validation on the job write path
 *     is the canonical enforcement point.
 *
 *   - `DEFAULT CURRENT_TIMESTAMP` on `created_at` / `updated_at` /
 *     `available_at`. Per §13.4, environment-sensitive default
 *     clauses are avoided; timestamps are supplied by the application
 *     layer on insert / update.
 *
 *   - Worker lock-timeout reclamation (§8.5). This migration only
 *     creates the table that makes lock tracking possible; the
 *     periodic "reclaim jobs that have been `running` too long"
 *     sweep belongs in the job-runner runtime code path.
 *
 *   - 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_sync_jobs` table at schema version 5.
 *
 * 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 CreateSyncJobsTable 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_sync_jobs';

	/**
	 * Target schema version installed by this migration.
	 *
	 * Returns version 5 — the revision that adds the background-job
	 * queue table on top of `affilun_products` (v1), `affilun_product_meta`
	 * (v2), `affilun_embeds` (v3), and `affilun_embed_items` (v4). See
	 * `database-schema.md` §13.2 for the canonical version numbering.
	 */
	public function targetVersion(): SchemaVersion {
		return SchemaVersion::of( 5 );
	}

	/**
	 * 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_sync_jobs 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_sync_jobs`, in the canonical order
	 * defined by `database-schema.md` §8.2.
	 *
	 * The `status` column is sized `varchar(32)` to fit the longest
	 * value in the initial enum (`cancelled`, 9 characters) with
	 * comfortable headroom for future additions, and defaults to
	 * `pending` so a newly-inserted job row is immediately eligible
	 * for the worker's "pick next runnable job" query once
	 * `available_at` elapses.
	 *
	 * `priority` defaults to `100`; lower values mean higher priority
	 * (§8.2), matching the `idx_priority_available` index's intended
	 * ascending sort.
	 *
	 * `max_attempts` defaults to `5`, the initial-version retry cap
	 * documented in §8.2; the worker consults this column to decide
	 * when to give up rather than re-queue a failing job.
	 *
	 * 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',
			'job_type varchar(64) NOT NULL',
			'payload_json longtext NOT NULL',
			"status varchar(32) NOT NULL DEFAULT 'pending'",
			'priority int unsigned NOT NULL DEFAULT 100',
			'attempts int unsigned NOT NULL DEFAULT 0',
			'max_attempts int unsigned NOT NULL DEFAULT 5',
			'available_at datetime NOT NULL',
			'locked_at datetime NULL',
			'locked_by varchar(64) NULL',
			'finished_at datetime NULL',
			'last_error_code varchar(64) NULL',
			'last_error text NULL',
			'created_at datetime NOT NULL',
			'updated_at datetime NOT NULL',
		);
	}

	/**
	 * Index definitions for `affilun_sync_jobs`, in the canonical order
	 * defined by `database-schema.md` §8.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.
	 *
	 * The composite indexes `idx_status_available`,
	 * `idx_job_type_status`, and `idx_priority_available` together
	 * support the worker's "pick the next runnable job" query pattern
	 * described in §8.5, where candidate rows must satisfy `status =
	 * 'pending' AND available_at <= now()` with ordering by priority.
	 * `idx_locked_at` supports the lock-timeout reclamation sweep.
	 *
	 * @return list<string>
	 */
	protected function indexDefinitions(): array {
		return array(
			'PRIMARY KEY  (id)',
			'KEY idx_status_available (status, available_at)',
			'KEY idx_job_type_status (job_type, status)',
			'KEY idx_locked_at (locked_at)',
			'KEY idx_priority_available (priority, available_at)',
		);
	}
}