<?php
/**
 * Per-site Evidence Memory schema and lifecycle.
 */

if (!defined('ABSPATH')) {
    exit;
}

final class AIFQ_SR_Memory_Schema {
    public const SCHEMA_VERSION = 2;
    public const EVIDENCE_LIMIT = 250;

    public const OPTION_SCHEMA_VERSION = 'aifq_sr_memory_schema_version';

    // Legacy v1.13 option names are retained only for cleanup/compatibility
    // governance. Production continuity code must not read or write them.
    public const OPTION_MEMORY_STARTED_AT = 'aifq_sr_memory_started_at';
    public const OPTION_HISTORY_STARTED_AT = 'aifq_sr_memory_history_started_at';
    public const OPTION_CURRENT_SNAPSHOT = 'aifq_sr_memory_current_snapshot';

    public static function evidence_table_name(): string {
        global $wpdb;
        return self::safe_table_name($wpdb->prefix . 'aifq_sr_evidence');
    }

    public static function relations_table_name(): string {
        global $wpdb;
        return self::safe_table_name($wpdb->prefix . 'aifq_sr_evidence_relations');
    }

    public static function state_table_name(): string {
        global $wpdb;
        return self::safe_table_name($wpdb->prefix . 'aifq_sr_memory_state');
    }

    public static function ensure_current_site_schema(): bool {
        $stored_version = (int) get_option(self::OPTION_SCHEMA_VERSION, 0);
        if ($stored_version === self::SCHEMA_VERSION && self::tables_exist()) {
            return true;
        }

        global $wpdb;
        $evidence_table = self::evidence_table_name();
        $relations_table = self::relations_table_name();
        $state_table = self::state_table_name();
        if ($evidence_table === '' || $relations_table === '' || $state_table === '') {
            return false;
        }

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

        $charset_collate = $wpdb->get_charset_collate();

        $evidence_sql = "CREATE TABLE {$evidence_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            event_id varchar(64) NOT NULL,
            schema_version smallint(5) unsigned NOT NULL DEFAULT 1,
            evidence_type varchar(16) NOT NULL,
            specificity varchar(24) NOT NULL,
            event_type varchar(64) NOT NULL,
            entity_type varchar(32) NOT NULL DEFAULT '',
            entity_id varchar(255) NOT NULL DEFAULT '',
            previous_state longtext NULL,
            new_state longtext NULL,
            source varchar(64) NOT NULL,
            observed_at bigint(20) unsigned NULL,
            detected_after bigint(20) unsigned NULL,
            detected_before bigint(20) unsigned NULL,
            metadata longtext NULL,
            idempotency_key char(64) NOT NULL,
            created_at bigint(20) unsigned NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY event_id (event_id),
            UNIQUE KEY idempotency_key (idempotency_key),
            KEY created_at (created_at),
            KEY entity_lookup (entity_type, entity_id(120)),
            KEY event_type (event_type)
        ) {$charset_collate};";

        $relations_sql = "CREATE TABLE {$relations_table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            relation_id varchar(64) NOT NULL,
            from_event_id varchar(64) NOT NULL,
            to_event_id varchar(64) NOT NULL,
            relation_type varchar(32) NOT NULL,
            created_at bigint(20) unsigned NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY relation_id (relation_id),
            UNIQUE KEY relation_tuple (from_event_id, to_event_id, relation_type),
            KEY from_event_id (from_event_id),
            KEY to_event_id (to_event_id)
        ) {$charset_collate};";

        $state_sql = "CREATE TABLE {$state_table} (
            state_key varchar(64) NOT NULL,
            schema_version smallint(5) unsigned NOT NULL DEFAULT 2,
            site_identity char(64) NOT NULL,
            history_boundary_id char(36) NOT NULL,
            memory_started_at bigint(20) unsigned NOT NULL,
            history_started_at bigint(20) unsigned NOT NULL,
            updated_at bigint(20) unsigned NOT NULL,
            payload longtext NOT NULL,
            payload_hash char(64) NOT NULL,
            PRIMARY KEY  (state_key),
            KEY site_identity (site_identity),
            KEY history_boundary_id (history_boundary_id)
        ) {$charset_collate};";

        dbDelta($evidence_sql);
        dbDelta($relations_sql);
        dbDelta($state_sql);

        if (!self::tables_exist(true)) {
            return false;
        }

        update_option(self::OPTION_SCHEMA_VERSION, self::SCHEMA_VERSION, false);
        return true;
    }

    public static function tables_exist(bool $refresh = false): bool {
        static $request_cache = [];

        global $wpdb;
        $evidence_table = self::evidence_table_name();
        $relations_table = self::relations_table_name();
        $state_table = self::state_table_name();
        if ($evidence_table === '' || $relations_table === '' || $state_table === '') {
            return false;
        }

        $cache_key = strtolower($evidence_table . '|' . $relations_table . '|' . $state_table);
        if (!$refresh && array_key_exists($cache_key, $request_cache)) {
            return $request_cache[$cache_key];
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema integrity check for local AIFQ tables.
        $evidence_found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($evidence_table)));
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema integrity check for local AIFQ tables.
        $relations_found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($relations_table)));
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Schema integrity check for local AIFQ tables.
        $state_found = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($state_table)));

        $request_cache[$cache_key] = is_string($evidence_found)
            && strtolower($evidence_found) === strtolower($evidence_table)
            && is_string($relations_found)
            && strtolower($relations_found) === strtolower($relations_table)
            && is_string($state_found)
            && strtolower($state_found) === strtolower($state_table);

        return $request_cache[$cache_key];
    }

    public static function uninstall_current_site(): void {
        global $wpdb;
        $evidence_table = self::evidence_table_name();
        $relations_table = self::relations_table_name();
        $state_table = self::state_table_name();

        if ($state_table !== '') {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Explicit uninstall cleanup of AIFQ-owned local table.
            $wpdb->query("DROP TABLE IF EXISTS `{$state_table}`");
        }
        if ($relations_table !== '') {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Explicit uninstall cleanup of AIFQ-owned local table.
            $wpdb->query("DROP TABLE IF EXISTS `{$relations_table}`");
        }
        if ($evidence_table !== '') {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Explicit uninstall cleanup of AIFQ-owned local table.
            $wpdb->query("DROP TABLE IF EXISTS `{$evidence_table}`");
        }

        delete_option(self::OPTION_SCHEMA_VERSION);
        delete_option(self::OPTION_MEMORY_STARTED_AT);
        delete_option(self::OPTION_HISTORY_STARTED_AT);
        delete_option(self::OPTION_CURRENT_SNAPSHOT);
    }

    public static function uninstall_all_sites(): void {
        if (!is_multisite() || !function_exists('get_sites') || !function_exists('switch_to_blog')) {
            self::uninstall_current_site();
            return;
        }

        $site_ids = get_sites([
            'fields' => 'ids',
            'number' => 0,
        ]);

        if (!is_array($site_ids) || empty($site_ids)) {
            self::uninstall_current_site();
            return;
        }

        foreach ($site_ids as $site_id) {
            switch_to_blog((int) $site_id);
            self::uninstall_current_site();
            restore_current_blog();
        }
    }

    private static function safe_table_name(string $table): string {
        return preg_match('/^[A-Za-z0-9_]+$/', $table) ? $table : '';
    }
}