<?php
/**
 * Database schema migrations.
 *
 * @package AgentFirewall
 */

declare(strict_types=1);

namespace AgentFirewall\Database;

defined( 'ABSPATH' ) || exit; // @codeCoverageIgnore

/**
 * Creates and migrates the five adaf_* tables via dbDelta (PRD §3, ADR-6).
 *
 * dbDelta dialect rules: one column per line, lowercase column types,
 * `KEY`/`UNIQUE KEY` (never `INDEX`), two spaces after `PRIMARY KEY`,
 * no `IF NOT EXISTS`, no foreign keys (integrity enforced in PHP).
 */
final class Schema {

	/**
	 * Bump when the schema changes; drives future delta migrations.
	 */
	public const DB_VERSION = '9';

	/**
	 * Option storing the installed schema version.
	 */
	public const OPTION = 'adaf_db_version';

	/**
	 * Run pending migrations when the installed schema is behind.
	 *
	 * The activation hook is not enough: WordPress reactivates silently after
	 * an update, so a schema change shipped in a release would otherwise only
	 * ever reach fresh installs. Checked on load, which costs one autoloaded
	 * option read on a request that changes nothing.
	 *
	 * @return bool Whether a migration ran.
	 */
	public function maybe_upgrade(): bool {
		if ( self::DB_VERSION === get_option( self::OPTION ) ) {
			return false;
		}

		$this->install();

		return true;
	}

	/**
	 * Create or update all plugin tables.
	 *
	 * Safe to run repeatedly: dbDelta only applies differences.
	 *
	 * @return array<string, string> dbDelta change report (empty when schema already matches).
	 */
	public function install(): array {
		global $wpdb;

		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}

		$charset_collate = $wpdb->get_charset_collate();
		$prefix          = $wpdb->prefix . 'adaf_';

		$tables = array(
			"CREATE TABLE {$prefix}agents (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				agent_key varchar(64) NOT NULL,
				name varchar(191) NOT NULL,
				wp_user_id bigint(20) unsigned NOT NULL,
				token_hash varchar(128) NULL,
				scopes_json longtext NULL,
				app_password_uuid varchar(36) NULL,
				status varchar(32) NOT NULL DEFAULT 'active',
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uq_agent_key (agent_key),
				UNIQUE KEY uq_app_password (app_password_uuid),
				KEY idx_wp_user_id (wp_user_id)
			) {$charset_collate}",
			"CREATE TABLE {$prefix}policies (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				agent_id bigint(20) unsigned NULL,
				rules_json longtext NOT NULL,
				priority int(11) NOT NULL DEFAULT 10,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY idx_agent_id (agent_id)
			) {$charset_collate}",
			"CREATE TABLE {$prefix}ledger (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				ts datetime NOT NULL,
				agent_id bigint(20) unsigned NOT NULL,
				wp_user_id bigint(20) unsigned NOT NULL,
				source varchar(64) NOT NULL,
				action_cat varchar(64) NOT NULL,
				entity_type varchar(64) NOT NULL,
				entity_id bigint(20) unsigned NULL,
				entity_label varchar(191) NULL,
				ability_name varchar(191) NULL,
				status varchar(32) NOT NULL,
				input_json longtext NULL,
				result_json longtext NULL,
				before_hash varchar(64) NULL,
				chain_hash varchar(64) NULL,
				policy_id bigint(20) unsigned NULL,
				rule_index int(11) NULL,
				prev_id bigint(20) unsigned NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY uniq_prev (prev_id),
				KEY idx_agent_ts (agent_id, ts),
				KEY idx_entity (entity_type, entity_id),
				KEY idx_status (status),
				KEY idx_ts (ts, id),
				KEY idx_agent_status_entity_ts (agent_id, status, entity_type, ts)
			) {$charset_collate}",
			"CREATE TABLE {$prefix}pending (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				ledger_id bigint(20) unsigned NOT NULL,
				agent_id bigint(20) unsigned NOT NULL,
				ability_name varchar(191) NOT NULL,
				stored_input longtext NOT NULL,
				request_json longtext NULL,
				initial_entity_modified_at datetime NULL,
				expires_at datetime NOT NULL,
				status varchar(32) NOT NULL DEFAULT 'pending',
				decided_by bigint(20) unsigned NULL,
				decided_at datetime NULL,
				PRIMARY KEY  (id),
				KEY idx_status_expires (status, expires_at),
				KEY idx_ledger (ledger_id)
			) {$charset_collate}",
			"CREATE TABLE {$prefix}snapshots (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				ledger_id bigint(20) unsigned NOT NULL,
				entity_type varchar(64) NOT NULL,
				entity_id bigint(20) unsigned NOT NULL,
				before_state longtext NOT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY idx_ledger (ledger_id),
				KEY idx_entity (entity_type, entity_id)
			) {$charset_collate}",
		);

		$changes = array();
		foreach ( $tables as $sql ) {
			$changes = array_merge( $changes, dbDelta( $sql ) );
		}

		// Recorded only once the migration verifiably took: dbDelta does not
		// report a failed ALTER, and a missing key would silently end the
		// serialized appends ADR-17 depends on. Left behind, the next load
		// tries again.
		if ( $this->ledger_is_current( $prefix . 'ledger' ) ) {
			update_option( self::OPTION, self::DB_VERSION );
		}

		return $changes;
	}

	/**
	 * Whether the ledger has the predecessor column and its unique key
	 * (DB_VERSION 9, AF-24.5).
	 *
	 * @param string $table Ledger table name.
	 */
	private function ledger_is_current( string $table ): bool {
		global $wpdb;

		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema introspection, uncached by design.
		$column = $wpdb->get_var( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $table, 'prev_id' ) );
		$key    = $wpdb->get_row( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'uniq_prev' ), ARRAY_A );
		// phpcs:enable

		return null !== $column && is_array( $key ) && 'prev_id' === $key['Column_name'] && '0' === (string) $key['Non_unique'];
	}
}