<?php
/**
 * Migration: create `clb_logs` table (schema version 6).
 *
 * Installs the operational-log table described in
 * `specifications/database-schema.md` §9. The table holds runtime
 * observability records — API failures, sync errors, configuration
 * issues, diagnostics notes — produced by CLB's provider, sync,
 * render, cache, REST, block, cron, and migration subsystems. Per §9.1
 * this table is deliberately scoped to operational logs; the audit
 * trail of configuration changes and destructive operations lives in
 * `clb_audit_events` (v7) with longer retention and stricter access
 * controls.
 *
 * This migration targets {@see SchemaVersion::of(6)} — the sixth step
 * in Commerce Link Builder's schema pipeline, installed after
 * `clb_products` (v1), `clb_product_meta` (v2), `clb_embeds` (v3),
 * `clb_embed_items` (v4), and `clb_sync_jobs` (v5).
 *
 * Scope of this migration:
 *
 *   - Creates `{$prefix}clb_logs` with the full column set defined in
 *     §9.2 (eight columns: surrogate primary key, channel and level
 *     discriminators, message, structured context JSON, optional
 *     product and job cross-references, and a creation timestamp —
 *     logs are append-only so there is no `updated_at`).
 *
 *   - Adds the six secondary indexes listed in §9.3 (`idx_channel`,
 *     `idx_level`, `idx_created_at`, `idx_related_product_id`,
 *     `idx_related_job_id`, `idx_channel_level_created`) that support
 *     the common diagnostics query patterns: drill down by channel,
 *     filter by severity, window by time, and correlate back to a
 *     product or job.
 *
 *   - 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 (level IN (...))` constraint. §9.2 enumerates the allowed
 *     PSR-3 level values (`debug`, `info`, `notice`, `warning`,
 *     `error`, `critical`; ADR-0008 / ADR-0019), but `dbDelta()` does
 *     not reliably round-trip `CHECK` constraints across MySQL /
 *     MariaDB versions (per §13.4). Application-layer validation on
 *     the log-write path is the canonical enforcement point.
 *
 *   - `CHECK (channel IN (...))` constraint. §9.2 enumerates the
 *     initial-version logical channel enum (`provider.amazon`,
 *     `provider.rakuten`, `provider.yahoo_shopping`, `sync`, `render`,
 *     `cache`, `rest`, `block`, `audit`, `security`, `cron`,
 *     `migration`, with `other` as the catch-all). Enforcement is
 *     likewise left to the application layer so unknown channels fall
 *     back to `other` rather than being silently dropped.
 *
 *   - JSON-extraction function indexes on `context_json`
 *     (`idx_event_type_created`, `idx_request_id`,
 *     `idx_correlation_id`). §9.3 lists these as "必要に応じて"
 *     (optional) and explicitly permits substituting alternatives
 *     depending on database dialect and operational constraints, so
 *     they are not part of the initial schema. Future migrations may
 *     add them on environments where the `JSON_EXTRACT` functional
 *     index form is supported (MySQL 8.0+ / MariaDB 10.5+).
 *
 *   - Foreign keys to `clb_products.id` / `clb_sync_jobs.id` for the
 *     `related_*` columns. Per §11.2 and §13.4, CLB does not declare
 *     foreign keys at the DB level; the cross-references are advisory
 *     for the diagnostics surface rather than referentially-enforced
 *     links.
 *
 *   - `DEFAULT CURRENT_TIMESTAMP` on `created_at`. Per §13.4,
 *     environment-sensitive default clauses are avoided; the timestamp
 *     is supplied by the application layer on insert.
 *
 *   - Log retention / purge logic (§9.4). This migration only creates
 *     the table that makes retention possible; the level-aware
 *     retention windows and scheduled purge job belong in the runtime
 *     cleanup path (`cleanup_logs` in `clb_sync_jobs`, §8.4).
 *
 *   - 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 CommerceLinkBuilder\Infrastructure\Persistence\Migrations
 */

declare(strict_types=1);

namespace CommerceLinkBuilder\Infrastructure\Persistence\Migrations;

use CommerceLinkBuilder\Infrastructure\Persistence\SchemaVersion;

/**
 * Creates the `clb_logs` table at schema version 6.
 *
 * 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 CLB table-creation
 * migration.
 */
final class CreateLogsTable extends AbstractCreateTableMigration {

	/**
	 * Logical, unprefixed CLB 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 = 'clb_logs';

	/**
	 * Target schema version installed by this migration.
	 *
	 * Returns version 6 — the revision that adds the operational-log
	 * table on top of `clb_products` (v1), `clb_product_meta` (v2),
	 * `clb_embeds` (v3), `clb_embed_items` (v4), and `clb_sync_jobs`
	 * (v5). See `database-schema.md` §13.2 for the canonical version
	 * numbering.
	 */
	public function targetVersion(): SchemaVersion {
		return SchemaVersion::of( 6 );
	}

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

	/**
	 * Logical, unprefixed CLB 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 \CommerceLinkBuilder\Infrastructure\Persistence\TableNameResolver}.
	 */
	protected function logicalTableName(): string {
		return self::TABLE_LOGICAL_NAME;
	}

	/**
	 * Column definitions for `clb_logs`, in the canonical order
	 * defined by `database-schema.md` §9.2.
	 *
	 * The table is append-only: there is no `updated_at` column
	 * because log entries are never mutated in place. Retention is
	 * handled by the scheduled `cleanup_logs` job (§8.4 / §9.4), not
	 * by row-level updates.
	 *
	 * `channel` is sized `varchar(64)` per §9.2 to accommodate the
	 * dotted logical channel names such as `provider.yahoo_shopping`
	 * with comfortable headroom; `level` is sized `varchar(16)` for
	 * the six PSR-3 level strings, of which `critical` (8 characters)
	 * is the longest.
	 *
	 * 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',
			'channel varchar(64) NOT NULL',
			'level varchar(16) NOT NULL',
			'message text NOT NULL',
			'context_json longtext NULL',
			'related_product_id bigint unsigned NULL',
			'related_job_id bigint unsigned NULL',
			'created_at datetime NOT NULL',
		);
	}

	/**
	 * Index definitions for `clb_logs`, in the canonical order
	 * defined by `database-schema.md` §9.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 `idx_channel_level_created` index supports the
	 * common diagnostics query pattern "show me errors and above on
	 * channel X in the last N hours" without forcing a full table
	 * scan; the single-column indexes on `channel`, `level`, and
	 * `created_at` support the simpler drill-down cases. The
	 * `idx_related_*` indexes accelerate cross-references back to a
	 * specific product or job from the diagnostics surface.
	 *
	 * Optional JSON-extraction function indexes on `context_json`
	 * (`idx_event_type_created`, `idx_request_id`,
	 * `idx_correlation_id`) listed in §9.3 are NOT installed here;
	 * see the file-level docblock for the rationale.
	 *
	 * @return list<string>
	 */
	protected function indexDefinitions(): array {
		return array(
			'PRIMARY KEY  (id)',
			'KEY idx_channel (channel)',
			'KEY idx_level (level)',
			'KEY idx_created_at (created_at)',
			'KEY idx_related_product_id (related_product_id)',
			'KEY idx_related_job_id (related_job_id)',
			'KEY idx_channel_level_created (channel, level, created_at)',
		);
	}
}
