<?php
/**
 * Schema migrator.
 *
 * @package Kayvandra\MarginBeacon
 */

declare(strict_types=1);

namespace Kayvandra\MarginBeacon\Migrations;

use Kayvandra\MarginBeacon\Support\Constants;

/**
 * Applies dbDelta migrations idempotently.
 */
final class Migrator {

	/**
	 * Request-scoped result after a successful verify (avoids duplicate deep checks in one boot).
	 *
	 * @var bool|null
	 */
	private static $request_ok = null;

	/**
	 * Upgrade schema when version differs.
	 *
	 * Deep SHOW TABLES/COLUMNS/INDEX verification runs on version change, repair,
	 * activation/diagnostics force, or when the verified stamp is missing/mismatched.
	 * Healthy current schema skips deep inspection on subsequent normal requests.
	 *
	 * @param bool $force_deep Force full invariant verification even when stamped.
	 * @return bool True when schema version matches and all invariants hold.
	 */
	public function maybe_upgrade( $force_deep = false ) {
		$force_deep = (bool) $force_deep;
		if ( ! $force_deep && true === self::$request_ok ) {
			return true;
		}

		$installed = get_option( Constants::OPTION_SCHEMA_VERSION, '' );
		if ( version_compare( (string) $installed, AKSMB_SCHEMA_VERSION, '>=' ) ) {
			if ( ! $force_deep && $this->is_schema_verified_for_current() ) {
				self::$request_ok = true;
				return true;
			}
			return $this->ensure_current_schema_healthy();
		}

		$this->clear_schema_verified();

		$ok = true;
		if ( '' !== (string) $installed && version_compare( (string) $installed, '1.2.1', '<' ) ) {
			$ok = $this->upgrade_to_1_2_1() && $ok;
		}
		if ( '' !== (string) $installed && version_compare( (string) $installed, '1.3.0', '<' ) ) {
			$ok = $this->upgrade_to_1_3_0() && $ok;
		}
		if ( '' !== (string) $installed && version_compare( (string) $installed, '1.4.0', '<' ) ) {
			$ok = $this->upgrade_to_1_4_0() && $ok;
		}

		$ok = $this->install_verified() && $ok;
		$ok = $this->schema_invariants_ok() && $ok;

		if ( ! $ok ) {
			self::$request_ok = null;
			return false; // Do not bump version — retry next request.
		}

		update_option( Constants::OPTION_SCHEMA_VERSION, AKSMB_SCHEMA_VERSION, true );
		// Re-verify: option write may fail silently; confirm stored value matches expected.
		$verify = (string) get_option( Constants::OPTION_SCHEMA_VERSION, '' );
		if ( '' !== $verify && version_compare( $verify, AKSMB_SCHEMA_VERSION, '>=' ) ) {
			$this->mark_schema_verified();
			self::$request_ok = true;
			return true;
		}
		$this->clear_schema_verified();
		self::$request_ok = null;
		return false;
	}

	/**
	 * Explicit schema verification (activation / diagnostics). Always deep-checks.
	 *
	 * @return bool
	 */
	public function verify_schema() {
		self::$request_ok = null;
		return $this->maybe_upgrade( true );
	}

	/**
	 * Deep-verify (and repair if needed) when version is already current.
	 *
	 * @return bool
	 */
	private function ensure_current_schema_healthy() {
		if ( $this->schema_invariants_ok() ) {
			$this->mark_schema_verified();
			self::$request_ok = true;
			return true;
		}

		$this->clear_schema_verified();
		if ( ! $this->repair_1_4_invariants() ) {
			self::$request_ok = null;
			return false;
		}

		// repair_* ends with schema_invariants_ok(); stamp only on success.
		$this->mark_schema_verified();
		self::$request_ok = true;
		return true;
	}

	/**
	 * Whether a verified stamp matches the current plugin schema version.
	 *
	 * @return bool
	 */
	private function is_schema_verified_for_current() {
		$stamp = (string) get_option( Constants::OPTION_SCHEMA_VERIFIED, '' );
		return '' !== $stamp && hash_equals( (string) AKSMB_SCHEMA_VERSION, $stamp );
	}

	/**
	 * Record that deep invariants passed for the current schema version.
	 *
	 * @return void
	 */
	private function mark_schema_verified() {
		update_option( Constants::OPTION_SCHEMA_VERIFIED, AKSMB_SCHEMA_VERSION, true );
	}

	/**
	 * Drop verified stamp so the next check must deep-verify.
	 *
	 * @return void
	 */
	private function clear_schema_verified() {
		delete_option( Constants::OPTION_SCHEMA_VERIFIED );
		self::$request_ok = null;
	}

	/**
	 * Create/update all tables; returns false if critical tables missing after dbDelta.
	 *
	 * @return bool
	 */
	public function install() {
		return $this->install_verified();
	}

	/**
	 * @return bool
	 */
	private function install_verified() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		foreach ( Schema::tables() as $sql ) {
			dbDelta( $sql );
		}

		return $this->all_required_tables_exist();
	}

	/**
	 * All seven Free schema tables must exist.
	 *
	 * @return bool
	 */
	private function all_required_tables_exist() {
		global $wpdb;
		$required = array(
			$wpdb->prefix . 'marginbeacon_scans',
			$wpdb->prefix . 'marginbeacon_findings',
			$wpdb->prefix . 'marginbeacon_price_revisions',
			$wpdb->prefix . 'marginbeacon_order_risk',
			$wpdb->prefix . 'marginbeacon_order_risk_lines',
			$wpdb->prefix . 'marginbeacon_acknowledgements',
			$wpdb->prefix . 'marginbeacon_operational_events',
		);
		foreach ( $required as $table ) {
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
				return false;
			}
		}
		return true;
	}

	/**
	 * Backfill + dedupe for 1.2.x unique indexes (fail-closed).
	 *
	 * @return bool
	 */
	private function upgrade_to_1_2_1() {
		return $this->repair_1_2_invariants();
	}

	/**
	 * @return bool
	 */
	private function repair_1_2_invariants() {
		global $wpdb;

		// Re-run dbDelta to recreate any missing tables (e.g. operational_events, order_risk_lines, price_revisions).
		if ( ! $this->install_verified() ) {
			return false;
		}

		$ack_ok      = true;
		$findings_ok = true;

		$ack = $wpdb->prefix . 'marginbeacon_acknowledgements';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $ack ) ) === $ack ) {
			$ack_ok = $this->migrate_acknowledgements_1_2( $ack );
		}

		$findings = $wpdb->prefix . 'marginbeacon_findings';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $findings ) ) === $findings ) {
			$findings_ok = $this->migrate_findings_1_2( $findings );
		}

		return $ack_ok && $findings_ok && $this->schema_invariants_ok();
	}

	/**
	 * @return bool
	 */
	private function repair_1_3_invariants() {
		if ( ! $this->repair_1_2_invariants() ) {
			return false;
		}
		if ( ! $this->migrate_scans_execution_key_1_3() ) {
			return false;
		}
		return $this->schema_invariants_ok();
	}

	/**
	 * @return bool
	 */
	private function upgrade_to_1_3_0() {
		if ( ! $this->repair_1_2_invariants() ) {
			return false;
		}
		if ( ! $this->migrate_scans_execution_key_1_3() ) {
			return false;
		}
		return $this->schema_invariants_ok();
	}

	/**
	 * Add payment_result_state for preliminary vs confirmed order risk.
	 *
	 * @return bool
	 */
	private function upgrade_to_1_4_0() {
		return $this->repair_1_4_invariants();
	}

	/**
	 * @return bool
	 */
	private function repair_1_4_invariants() {
		// Install/recreate tables first, then add 1.4 columns before nested
		// repairs re-run full schema_invariants_ok() (which requires 1.4 fields).
		if ( ! $this->install_verified() ) {
			return false;
		}
		if ( ! $this->migrate_order_risk_payment_result_state_1_4() ) {
			return false;
		}
		if ( ! $this->repair_1_3_invariants() ) {
			return false;
		}
		return $this->schema_invariants_ok();
	}

	/**
	 * Ensure order_risk.payment_result_state exists; backfill legacy rows as confirmed.
	 *
	 * @return bool
	 */
	private function migrate_order_risk_payment_result_state_1_4() {
		global $wpdb;
		$table = $wpdb->prefix . 'marginbeacon_order_risk';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col = $wpdb->get_results( "SHOW COLUMNS FROM {$table} LIKE 'payment_result_state'", ARRAY_A );
		if ( empty( $col ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
			$altered = $wpdb->query( "ALTER TABLE {$table} ADD COLUMN payment_result_state varchar(16) NOT NULL DEFAULT 'confirmed' AFTER presentation_state" );
			if ( false === $altered ) {
				return false;
			}
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted table name from $wpdb->prefix.
		$backfill = $wpdb->query( "UPDATE {$table} SET payment_result_state = 'confirmed' WHERE payment_result_state IS NULL OR payment_result_state = ''" );
		if ( false === $backfill ) {
			return false;
		}
		return $this->ensure_key_index( $table, 'payment_result_state', 'payment_result_state' );
	}

	/**
	 * Add execution_key column and UNIQUE index to scans table; dedupe existing dupes.
	 *
	 * @return bool
	 */
	private function migrate_scans_execution_key_1_3() {
		global $wpdb;
		$scans = $wpdb->prefix . 'marginbeacon_scans';
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $scans ) ) !== $scans ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col = $wpdb->get_results( "SHOW COLUMNS FROM {$scans} LIKE 'execution_key'", ARRAY_A );
		if ( empty( $col ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$altered = $wpdb->query( "ALTER TABLE {$scans} ADD COLUMN execution_key varchar(128) DEFAULT NULL AFTER evaluation_scope" );
			if ( false === $altered ) {
				return false;
			}
		}
		// Dedupe: if multiple rows share same non-null execution_key, keep lowest id.
		$dupes = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
			"SELECT execution_key, MIN(id) AS keep_id, COUNT(*) AS c FROM {$scans} WHERE execution_key IS NOT NULL GROUP BY execution_key HAVING c > 1",
			ARRAY_A
		);
		if ( is_array( $dupes ) ) {
			foreach ( $dupes as $d ) {
				$r = $wpdb->query(
					$wpdb->prepare(
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
						"UPDATE {$scans} SET execution_key = NULL WHERE execution_key = %s AND id <> %d",
						(string) $d['execution_key'],
						(int) $d['keep_id']
					)
				);
				if ( false === $r ) {
					return false;
				}
			}
		}
		return $this->ensure_unique_index( $scans, 'uk_scan_execution_key', 'execution_key' );
	}

	/**
	 * @param string $ack Full table name.
	 * @return bool
	 */
	private function migrate_acknowledgements_1_2( $ack ) {
		global $wpdb;

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col = $wpdb->get_results( "SHOW COLUMNS FROM {$ack} LIKE 'active_key'", ARRAY_A );
		if ( empty( $col ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$altered = $wpdb->query( "ALTER TABLE {$ack} ADD COLUMN active_key varchar(160) NULL AFTER status" );
			if ( false === $altered ) {
				return false;
			}
		}

		$dupes = $wpdb->get_results(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
			"SELECT scope_type, scope_ref, MAX(id) AS keep_id, COUNT(*) AS c FROM {$ack} WHERE status = 'active' GROUP BY scope_type, scope_ref HAVING c > 1",
			ARRAY_A
		);
		if ( is_array( $dupes ) ) {
			foreach ( $dupes as $d ) {
				$r = $wpdb->query(
					$wpdb->prepare(
						// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
						"UPDATE {$ack} SET status = 'revoked', active_key = NULL, revoke_reason = 'schema_1_2_dedupe', revoked_at_gmt = %s WHERE status = 'active' AND scope_type = %s AND scope_ref = %s AND id <> %d",
						gmdate( 'Y-m-d H:i:s' ),
						(string) $d['scope_type'],
						(string) $d['scope_ref'],
						(int) $d['keep_id']
					)
				);
				if ( false === $r ) {
					return false;
				}
			}
		}

		if ( false === $wpdb->query(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
			"UPDATE {$ack} SET active_key = CONCAT(scope_type, ':', scope_ref) WHERE status = 'active' AND (active_key IS NULL OR active_key = '')"
		) ) {
			return false;
		}
		if ( false === $wpdb->query(
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
			"UPDATE {$ack} SET active_key = NULL WHERE status <> 'active'"
		) ) {
			return false;
		}

		return $this->ensure_unique_index( $ack, 'active_key', 'active_key' );
	}

	/**
	 * @param string $findings Full table name.
	 * @return bool
	 */
	private function migrate_findings_1_2( $findings ) {
		global $wpdb;

		// Include empty input_hash — UNIQUE (scan_id, '') allows only one per scan.
		$groups = self::findings_dedupe_groups(
			$wpdb->get_results(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
				"SELECT scan_id, input_hash, MAX(id) AS keep_id, COUNT(*) AS c FROM {$findings} WHERE scan_id IS NOT NULL GROUP BY scan_id, input_hash HAVING c > 1",
				ARRAY_A
			)
		);

		foreach ( $groups as $d ) {
			$r = $wpdb->query(
				$wpdb->prepare(
					// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- trusted migration table name.
					"DELETE FROM {$findings} WHERE scan_id = %d AND input_hash = %s AND id <> %d",
					(int) $d['scan_id'],
					(string) $d['input_hash'],
					(int) $d['keep_id']
				)
			);
			if ( false === $r ) {
				return false;
			}
		}

		return $this->ensure_unique_index( $findings, 'scan_input_hash', 'scan_id, input_hash' );
	}

	/**
	 * Normalize dedupe group rows (pure helper for tests).
	 *
	 * @param array|null $rows Raw GROUP BY rows.
	 * @return array<int,array{scan_id:int,input_hash:string,keep_id:int}>
	 */
	public static function findings_dedupe_groups( $rows ) {
		if ( ! is_array( $rows ) ) {
			return array();
		}
		$out = array();
		foreach ( $rows as $d ) {
			if ( ! is_array( $d ) || empty( $d['keep_id'] ) ) {
				continue;
			}
			$out[] = array(
				'scan_id'    => (int) $d['scan_id'],
				'input_hash' => isset( $d['input_hash'] ) ? (string) $d['input_hash'] : '',
				'keep_id'    => (int) $d['keep_id'],
			);
		}
		return $out;
	}

	/**
	 * Full schema health: seven tables + 1.2.x / 1.3.x unique indexes.
	 *
	 * @return bool
	 */
	private function schema_invariants_ok() {
		global $wpdb;

		if ( ! $this->all_required_tables_exist() ) {
			return false;
		}

		$ack      = $wpdb->prefix . 'marginbeacon_acknowledgements';
		$findings = $wpdb->prefix . 'marginbeacon_findings';
		$scans    = $wpdb->prefix . 'marginbeacon_scans';

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col = $wpdb->get_results( "SHOW COLUMNS FROM {$ack} LIKE 'active_key'", ARRAY_A );
		if ( empty( $col ) ) {
			return false;
		}
		if ( ! $this->index_exists( $ack, 'active_key', 'active_key' ) ) {
			return false;
		}
		if ( ! $this->index_exists( $findings, 'scan_input_hash', 'scan_id,input_hash' ) ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col2 = $wpdb->get_results( "SHOW COLUMNS FROM {$scans} LIKE 'execution_key'", ARRAY_A );
		if ( empty( $col2 ) ) {
			return false;
		}
		if ( ! $this->index_exists( $scans, 'uk_scan_execution_key', 'execution_key' ) ) {
			return false;
		}

		$order_risk = $wpdb->prefix . 'marginbeacon_order_risk';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$col3 = $wpdb->get_results( "SHOW COLUMNS FROM {$order_risk} LIKE 'payment_result_state'", ARRAY_A );
		if ( empty( $col3 ) ) {
			return false;
		}
		return true;
	}

	/**
	 * @param string $table Full table.
	 * @param string $name  Index name.
	 * @param string $columns Expected column-order (comma-joined), empty to skip.
	 * @return bool
	 */
	private function index_exists( $table, $name, $columns = '' ) {
		$rows = $this->get_index_rows( $table, $name );
		if ( empty( $rows ) ) {
			return false;
		}
		// Verify UNIQUE flag (same on every row of a composite index).
		if ( (int) $rows[0]['Non_unique'] !== 0 ) {
			return false;
		}
		if ( '' !== (string) $columns ) {
			$actual = array();
			foreach ( $rows as $c ) {
				$actual[] = (string) $c['Column_name'];
			}
			$expected = array_map( 'trim', explode( ',', $columns ) );
			if ( $actual !== $expected ) {
				return false;
			}
		}
		return true;
	}

	/**
	 * SHOW INDEX rows for a key, ordered by Seq_in_index (MariaDB has no ORDER BY in SHOW INDEX).
	 *
	 * @param string $table Full table.
	 * @param string $name  Index name.
	 * @return array<int,array<string,mixed>>
	 */
	private function get_index_rows( $table, $name ) {
		global $wpdb;
		$rows = $wpdb->get_results(
			$wpdb->prepare(
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SHOW INDEX requires trusted table name.
				"SHOW INDEX FROM {$table} WHERE Key_name = %s",
				$name
			),
			ARRAY_A
		);
		if ( ! is_array( $rows ) || empty( $rows ) ) {
			return array();
		}
		usort(
			$rows,
			static function ( $a, $b ) {
				return (int) $a['Seq_in_index'] <=> (int) $b['Seq_in_index'];
			}
		);
		return $rows;
	}

	/**
	 * Check if an index exists by name only (ignoring definition correctness).
	 *
	 * @param string $table Full table.
	 * @param string $name  Index name.
	 * @return bool
	 */
	private function index_exists_by_name_only( $table, $name ) {
		return ! empty( $this->get_index_rows( $table, $name ) );
	}

	/**
	 * Ensure a UNIQUE KEY index exists with correct definition.
	 * If the index exists but is malformed (non-unique or wrong columns), DROP and recreate.
	 *
	 * @param string $table   Full table name.
	 * @param string $name    Index name.
	 * @param string $columns Column list for UNIQUE constraint.
	 * @return bool
	 */
	private function ensure_unique_index( $table, $name, $columns ) {
		global $wpdb;
		if ( $this->index_exists( $table, $name, $columns ) ) {
			return true;
		}

		// Index exists but is malformed (non-unique, wrong columns).
		if ( $this->index_exists_by_name_only( $table, $name ) ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$dropped = $wpdb->query( "ALTER TABLE {$table} DROP INDEX `{$name}`" );
			if ( false === $dropped ) {
				return false;
			}
		}

		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$result = $wpdb->query( "ALTER TABLE {$table} ADD UNIQUE KEY `{$name}` ({$columns})" );
		if ( false === $result ) {
			return false;
		}
		return $this->index_exists( $table, $name, $columns );
	}

	/**
	 * Ensure a non-unique KEY index exists.
	 *
	 * @param string $table   Full table name.
	 * @param string $name    Index name.
	 * @param string $columns Column list.
	 * @return bool
	 */
	private function ensure_key_index( $table, $name, $columns ) {
		global $wpdb;
		$rows = $this->get_index_rows( $table, $name );
		if ( ! empty( $rows ) ) {
			$actual = array();
			foreach ( $rows as $c ) {
				$actual[] = (string) $c['Column_name'];
			}
			$expected = array_map( 'trim', explode( ',', $columns ) );
			if ( $actual === $expected ) {
				return true;
			}
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$dropped = $wpdb->query( "ALTER TABLE {$table} DROP INDEX `{$name}`" );
			if ( false === $dropped ) {
				return false;
			}
		}
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$result = $wpdb->query( "ALTER TABLE {$table} ADD KEY `{$name}` ({$columns})" );
		if ( false === $result ) {
			return false;
		}
		$rows = $this->get_index_rows( $table, $name );
		return ! empty( $rows );
	}
}