<?php
/**
 * Initial JJ\'s Post Scheduler schema.
 *
 * @package ScheduledChanges
 */

namespace ScheduledChanges\Database\Migrations;

// phpcs:disable WordPress.DB -- Scheduler-owned custom tables require direct wpdb queries.
// phpcs:disable WordPress.Security.EscapeOutput.ExceptionNotEscaped -- Exception messages are escaped at UI boundaries.

use ScheduledChanges\Database\Migration;
use ScheduledChanges\Database\TableNames;

/**
 * Creates scheduler-owned tables and indexes.
 */
final class InitialSchemaMigration implements Migration {
	/**
	 * {@inheritDoc}
	 */
	public function version(): int {
		return 1;
	}

	/**
	 * {@inheritDoc}
	 */
	public function up( \wpdb $wpdb ): void {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$tables        = $this->requiredTables();
		$collate       = $wpdb->get_charset_collate();
		$table_options = trim( 'ENGINE=InnoDB ' . $collate );

		$schemas = array(
			"CREATE TABLE {$tables['launches']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				uuid char(36) NOT NULL,
				name varchar(191) NOT NULL,
				description longtext NULL,
				owner_id bigint(20) unsigned NOT NULL DEFAULT 0,
				status varchar(32) NOT NULL,
				timezone varchar(64) NOT NULL,
				start_at_utc datetime NULL,
				end_at_utc datetime NULL,
				recurrence_rule text NULL,
				current_version_id bigint(20) unsigned NULL,
				conflict_policy varchar(24) NOT NULL,
				failure_policy varchar(32) NOT NULL,
				creation_source varchar(24) NOT NULL,
				created_at_utc datetime NOT NULL,
				updated_at_utc datetime NOT NULL,
				scheduled_at_utc datetime NULL,
				completed_at_utc datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uuid (uuid),
				KEY status_start (status, start_at_utc),
				KEY calendar_start (start_at_utc, status),
				KEY calendar_end (end_at_utc, status),
				KEY owner_status (owner_id, status),
				KEY current_version_id (current_version_id),
				KEY updated_at_utc (updated_at_utc, id)
			) {$table_options};",
			"CREATE TABLE {$tables['launch_versions']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				launch_id bigint(20) unsigned NOT NULL,
				version_number int(10) unsigned NOT NULL,
				status varchar(32) NOT NULL DEFAULT 'draft',
				action_scheduler_id bigint(20) unsigned NULL,
				scheduled_at_utc datetime NULL,
				occurrence_at_utc datetime NULL,
				attempt_number int(10) unsigned NOT NULL DEFAULT 1,
				trigger_source varchar(32) NOT NULL DEFAULT 'scheduled',
				claimed_at_utc datetime NULL,
				updated_at_utc datetime NOT NULL,
				superseded_at_utc datetime NULL,
				version_role varchar(16) NOT NULL DEFAULT 'start',
				linked_version_id bigint(20) unsigned NULL,
				plan_json longtext NOT NULL,
				schema_version int(10) unsigned NOT NULL,
				checksum char(64) NOT NULL,
				validation_summary_json longtext NULL,
				compatibility_json longtext NULL,
				required_providers_json longtext NULL,
				creator_id bigint(20) unsigned NOT NULL DEFAULT 0,
				created_at_utc datetime NOT NULL,
				human_summary longtext NULL,
				fingerprints_json longtext NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY launch_version (launch_id, version_number),
				UNIQUE KEY launch_checksum (launch_id, checksum),
				KEY status_scheduled (status, scheduled_at_utc),
				KEY action_scheduler_id (action_scheduler_id),
				KEY role_linked (version_role, linked_version_id),
				KEY created_at_utc (created_at_utc)
			) {$table_options};",
			"CREATE TABLE {$tables['launch_targets']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				launch_version_id bigint(20) unsigned NOT NULL,
				target_uuid char(36) NOT NULL,
				target_reference varchar(191) NOT NULL,
				target_id bigint(20) unsigned NULL,
				target_type varchar(64) NOT NULL DEFAULT '',
				subtype varchar(64) NULL,
				parent_reference varchar(191) NULL,
				provider_id varchar(191) NOT NULL,
				baseline_fingerprint char(64) NULL,
				execution_order int(11) NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				UNIQUE KEY version_target (launch_version_id, target_uuid),
				KEY target_lookup (target_type, target_id),
				KEY provider_id (provider_id)
			) {$table_options};",
			"CREATE TABLE {$tables['operations']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				launch_version_id bigint(20) unsigned NOT NULL,
				operation_uuid char(36) NOT NULL,
				target_uuid char(36) NOT NULL,
				operation_type varchar(64) NOT NULL,
				field_path varchar(191) NULL,
				provider_id varchar(191) NOT NULL,
				before_json longtext NULL,
				after_json longtext NULL,
				value_type varchar(32) NULL,
				schema_json longtext NULL,
				validation_json longtext NULL,
				dependencies_json longtext NULL,
				idempotency_key char(64) NOT NULL,
				reversibility varchar(32) NOT NULL,
				conflict_strategy varchar(24) NOT NULL,
				redaction_json longtext NULL,
				summary text NULL,
				execution_order int(11) NOT NULL DEFAULT 0,
				PRIMARY KEY  (id),
				UNIQUE KEY operation_uuid (operation_uuid),
				UNIQUE KEY idempotency_key (idempotency_key),
				KEY version_order (launch_version_id, execution_order),
				KEY target_uuid (target_uuid),
				KEY provider_id (provider_id),
				KEY field_path (field_path)
			) {$table_options};",
			"CREATE TABLE {$tables['recording_sessions']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				uuid char(36) NOT NULL,
				source_id bigint(20) unsigned NOT NULL,
				workspace_id bigint(20) unsigned NULL,
				source_provider_id varchar(191) NOT NULL DEFAULT '',
				source_type varchar(64) NOT NULL DEFAULT '',
				source_reference varchar(191) NULL,
				workspace_provider_id varchar(191) NULL,
				workspace_type varchar(64) NULL,
				workspace_reference varchar(191) NULL,
				provider_map_json longtext NULL,
				baseline_snapshot_json longtext NOT NULL,
				current_snapshot_json longtext NULL,
				diff_json longtext NULL,
				selected_changes_json longtext NULL,
				owner_id bigint(20) unsigned NOT NULL,
				status varchar(32) NOT NULL,
				expires_at_utc datetime NOT NULL,
				created_at_utc datetime NOT NULL,
				updated_at_utc datetime NOT NULL,
				cleanup_status varchar(32) NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uuid (uuid),
				KEY source_status (source_id, status),
				KEY owner_status (owner_id, status),
				KEY expires_at_utc (expires_at_utc),
				KEY workspace_id (workspace_id),
				KEY updated_at_utc (updated_at_utc, id),
				KEY source_identity (source_type, source_reference),
				KEY workspace_identity (workspace_type, workspace_reference),
				KEY source_provider_id (source_provider_id),
				KEY workspace_provider_id (workspace_provider_id)
			) {$table_options};",
			"CREATE TABLE {$tables['runs']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				uuid char(36) NOT NULL,
				launch_id bigint(20) unsigned NOT NULL,
				launch_version_id bigint(20) unsigned NOT NULL,
				action_scheduler_id bigint(20) unsigned NULL,
				trigger_source varchar(32) NOT NULL,
				attempt_number int(10) unsigned NOT NULL DEFAULT 1,
				scheduled_at_utc datetime NULL,
				started_at_utc datetime NULL,
				completed_at_utc datetime NULL,
				status varchar(32) NOT NULL,
				lock_token varchar(191) NULL,
				environment_fingerprint char(64) NULL,
				validation_json longtext NULL,
				result_json longtext NULL,
				warning_count int(10) unsigned NOT NULL DEFAULT 0,
				error_count int(10) unsigned NOT NULL DEFAULT 0,
				rollback_run_id bigint(20) unsigned NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uuid (uuid),
				KEY launch_status (launch_id, status),
				KEY version_status (launch_version_id, status),
				KEY action_scheduler_id (action_scheduler_id),
				KEY scheduled_at_utc (scheduled_at_utc),
				KEY launch_completed (launch_id, completed_at_utc, id),
				KEY version_scheduled (launch_version_id, scheduled_at_utc),
				KEY started_at_utc (started_at_utc, id),
				KEY scheduled_started (scheduled_at_utc, started_at_utc)
			) {$table_options};",
			"CREATE TABLE {$tables['run_operations']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				run_id bigint(20) unsigned NOT NULL,
				operation_id bigint(20) unsigned NOT NULL,
				status varchar(32) NOT NULL,
				attempt_number int(10) unsigned NOT NULL DEFAULT 1,
				started_at_utc datetime NULL,
				completed_at_utc datetime NULL,
				result_json longtext NULL,
				warning_json longtext NULL,
				error_json longtext NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY run_operation (run_id, operation_id),
				KEY run_status (run_id, status),
				KEY operation_id (operation_id),
				KEY operation_status (operation_id, status)
			) {$table_options};",
			"CREATE TABLE {$tables['snapshots']} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				uuid char(36) NOT NULL,
				run_id bigint(20) unsigned NULL,
				launch_version_id bigint(20) unsigned NOT NULL,
				target_reference varchar(191) NOT NULL,
				provider_id varchar(191) NOT NULL,
				provider_version varchar(64) NOT NULL,
				snapshot_kind varchar(32) NOT NULL,
				data_json longtext NOT NULL,
				checksum char(64) NOT NULL,
				redaction_json longtext NULL,
				encryption_marker varchar(64) NULL,
				captured_at_utc datetime NOT NULL,
				retain_until_utc datetime NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uuid (uuid),
				KEY run_id (run_id),
				KEY run_kind (run_id, snapshot_kind),
				KEY version_target (launch_version_id, target_reference),
				KEY provider_id (provider_id),
				KEY retain_until_utc (retain_until_utc)
			) {$table_options};",
				"CREATE TABLE {$tables['audit_events']} (
					id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
					uuid char(36) NOT NULL,
					launch_id bigint(20) unsigned NULL,
				run_id bigint(20) unsigned NULL,
				actor_id bigint(20) unsigned NOT NULL DEFAULT 0,
				event_type varchar(64) NOT NULL,
				severity varchar(16) NOT NULL,
				context_json longtext NULL,
				created_at_utc datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uuid (uuid),
				KEY launch_created (launch_id, created_at_utc),
				KEY run_created (run_id, created_at_utc),
					KEY event_type (event_type),
					KEY severity (severity)
				) {$table_options};",
				"CREATE TABLE {$tables['notifications']} (
					id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
					uuid char(36) NOT NULL,
					launch_id bigint(20) unsigned NULL,
					run_id bigint(20) unsigned NULL,
					channel varchar(32) NOT NULL,
					event_type varchar(64) NOT NULL,
					status varchar(32) NOT NULL,
					destination varchar(191) NULL,
					payload_json longtext NULL,
					delivery_id varchar(191) NULL,
					idempotency_key char(64) NOT NULL,
					attempt_number int(10) unsigned NOT NULL DEFAULT 0,
					scheduled_at_utc datetime NULL,
					sent_at_utc datetime NULL,
					last_error_json longtext NULL,
					created_at_utc datetime NOT NULL,
					PRIMARY KEY  (id),
					UNIQUE KEY uuid (uuid),
					UNIQUE KEY idempotency_key (idempotency_key),
					KEY launch_status (launch_id, status),
					KEY run_id (run_id),
					KEY scheduled_at_utc (scheduled_at_utc),
					KEY created_at_utc (created_at_utc)
				) {$table_options};",
				"CREATE TABLE {$tables['locks']} (
					id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
					lock_key varchar(191) NOT NULL,
				owner_token varchar(191) NOT NULL,
				target_reference varchar(191) NOT NULL,
				acquired_at_utc datetime NOT NULL,
				renewed_at_utc datetime NOT NULL,
				expires_at_utc datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY lock_key (lock_key),
				KEY target_reference (target_reference),
				KEY expires_at_utc (expires_at_utc)
			) {$table_options};",
		);

		foreach ( $schemas as $schema ) {
			dbDelta( $schema );
		}

		foreach ( $tables as $table ) {
			$found = $wpdb->get_var(
				$wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) )
			);

			if ( $found !== $table ) {
				throw new \RuntimeException( 'Failed to create JJ\'s Post Scheduler table: ' . $table );
			}
		}
	}

	/**
	 * Returns every table covered by this schema version.
	 *
	 * Schema versions certify the complete free core schema used by active
	 * scheduling, recording, execution, rollback, audit, and diagnostic flows.
	 *
	 * @return array<string,string>
	 */
	private function requiredTables(): array {
		return ( new TableNames() )->all();
	}
}
