<?php
/**
 * Schema manager — owns DB table creation and version-pinned migrations.
 *
 * Mirrors the SEOFORGE_Schema_Manager pattern: idempotent, two get_option
 * calls on the hot path once the install is current.
 */

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

class GUARDFORGE_Schema_Manager {

    const VERSION_OPTION = 'guardforge_db_version';

    public static function maybe_upgrade() {
        $current = (string) get_option( self::VERSION_OPTION, '' );
        if ( $current === GUARDFORGE_DB_VERSION ) {
            return; // fast path
        }
        self::run_dbdelta();
        self::stamp_enabled_since();
        update_option( self::VERSION_OPTION, GUARDFORGE_DB_VERSION, false );
    }

    /**
 * 1.6.0: the first screen dates a switch from the moment it was turned on,
 * and a site that turned it on before this version has no such moment.
 * Give it "now", once, here — on the upgrade — rather than leaving it
 * empty (a switch with no date can never be called overdue) or inventing
 * an older date (the screen would go red the day the update lands).
 * Idempotent: a stored date is never moved, a switch that is off stores none.
 * The feed class is not in the wp.org package, hence the guard.
 */
    public static function stamp_enabled_since() {
        if ( class_exists( 'GUARDFORGE_Integrity' ) ) {
            GUARDFORGE_Integrity::sync_enabled_since();
        }
        if ( class_exists( 'GUARDFORGE_Vuln_Feed' ) ) {
            GUARDFORGE_Vuln_Feed::sync_enabled_since();
        }
    }

    private static function run_dbdelta() {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        $charset = $wpdb->get_charset_collate();

        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_login_log (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            event_time DATETIME NOT NULL,
            ip VARCHAR(45) NOT NULL,
            username VARCHAR(200) NOT NULL,
            result VARCHAR(20) NOT NULL,
            user_agent TEXT,
            country VARCHAR(2) DEFAULT NULL,
            PRIMARY KEY (id),
            KEY ip (ip),
            KEY event_time (event_time),
            KEY result (result)
        ) {$charset};" );

        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_integrity (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            scan_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            file_path TEXT NOT NULL,
            file_hash VARCHAR(64) NOT NULL,
            scope VARCHAR(20) NOT NULL DEFAULT 'core',
            scanned_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY scan_id (scan_id),
            KEY scope (scope)
        ) {$charset};" );

        // prev_hash / row_hash: the hash chain (GF-3). Both CHAR(64) to match
        // the event_key convention below — a sha256 hex digest, fixed width.
        // NOT NULL DEFAULT '' rather than NULL: a row written before this
        // migration has to read back as "unhashed" through a plain string
        // comparison, not as a NULL that every caller has to special-case.
        // GUARDFORGE_Audit_Log::verify_chain reads that empty string as
        // "this row predates the chain" and treats the next hashed row as a
        // fresh genesis rather than a break.
        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_audit_log (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            event_time DATETIME NOT NULL,
            user_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            user_login VARCHAR(200) NOT NULL DEFAULT '',
            event_type VARCHAR(60) NOT NULL,
            object_type VARCHAR(60) NOT NULL DEFAULT '',
            object_name TEXT,
            details TEXT,
            ip VARCHAR(45) NOT NULL DEFAULT '',
            prev_hash CHAR(64) NOT NULL DEFAULT '',
            row_hash CHAR(64) NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            KEY event_time (event_time),
            KEY event_type (event_type),
            KEY user_id (user_id)
        ) {$charset};" );

        // Two columns arrived with the free delayed feed (1.2.1).

        // `source` is what keeps the two feeds from overwriting each other's
        // rows: the free client owns 'free' and replaces that set wholesale on
        // every pull (the public feed is a full snapshot, not a watermarked
        // delta), while the licensed client owns 'pro' and merges. Without the
        // column a site that buys Pro would have its free rows silently
        // adopted as though the real-time feed had produced them.

        // `patched_in` is the version the advisory says fixes it, and it is
        // the fact the "patch by update" rule in the add-on refuses to act
        // without: an update that is merely NEWER is not evidence that it is
        // the one that closes this CVE.
        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_vulndb (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            slug VARCHAR(190) NOT NULL,
            type VARCHAR(20) NOT NULL DEFAULT 'plugin',
            affected_range VARCHAR(190) NOT NULL DEFAULT '',
            severity VARCHAR(20) NOT NULL DEFAULT 'medium',
            cve VARCHAR(40) DEFAULT NULL,
            summary TEXT,
            patched_in VARCHAR(40) DEFAULT NULL,
            source VARCHAR(10) NOT NULL DEFAULT 'pro',
            published_at DATETIME DEFAULT NULL,
            fetched_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY slug (slug),
            KEY type (type),
            KEY severity (severity),
            KEY source (source)
        ) {$charset};" );

        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_2fa (
            user_id BIGINT(20) UNSIGNED NOT NULL,
            totp_secret VARCHAR(64) NOT NULL,
            recovery_hashes TEXT NOT NULL,
            enrolled_at DATETIME NOT NULL,
            last_used_at DATETIME DEFAULT NULL,
            PRIMARY KEY (user_id)
        ) {$charset};" );

        // One row per calendar day. UNIQUE KEY captured_on is what lets the
        // writer use $wpdb->replace, so a manual recalculation overwrites
        // today instead of piling up beside the cron's row.
        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_score_history (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            captured_at DATETIME NOT NULL,
            captured_on DATE NOT NULL,
            score TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
            earned SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
            applicable SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
            items_failed SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
            snapshot TEXT,
            PRIMARY KEY (id),
            UNIQUE KEY captured_on (captured_on),
            KEY captured_at (captured_at)
        ) {$charset};" );

        // One paid answer per event, addressed by the content of the event
        // rather than by the row that carried it. UNIQUE KEY event_key is what
        // makes the promise on the button true: the same event asked twice is
        // bought once. event_key is a hex sha256 — 64 ASCII characters, 256
        // bytes under utf8mb4, comfortably inside the index-length limit on
        // old InnoDB, which is why it is CHAR(64) and not VARCHAR(255).
        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_event_explain (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            event_key CHAR(64) NOT NULL,
            source VARCHAR(20) NOT NULL,
            source_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            severity VARCHAR(20) NOT NULL DEFAULT 'medium',
            narrative TEXT,
            actions_json TEXT,
            cost_credits SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY event_key (event_key),
            KEY source_id (source, source_id),
            KEY created_at (created_at)
        ) {$charset};" );

        // Cases (§6.4 K1-a). One row per investigation, not per observation —
        // `case_key` is the deterministic identity of what is being
        // investigated (which file; which plugin+CVE; which IP+username
        // pair), so fifty repeats of the same underlying event update ONE
        // row's evidence instead of opening fifty cases. No UNIQUE KEY on
        // case_key alone: a case that was closed (fixed/explained) and later
        // recurs must be able to open a fresh one, so "is the most recent
        // case for this key still open, and if not, is this candidate
        // actually new evidence or the same stored result read again" is an
        // application-level decision
        // (GUARDFORGE_Cases::find_or_create_case +
        // all_evidence_already_known), not a DB constraint.
        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_cases (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            case_key CHAR(64) NOT NULL,
            type VARCHAR(20) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'open',
            confidence VARCHAR(10) NOT NULL DEFAULT 'medium',
            confidence_reason TEXT,
            subject VARCHAR(190) NOT NULL DEFAULT '',
            coverage_json TEXT,
            assigned_user BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            resolution_basis TEXT,
            first_seen DATETIME NOT NULL,
            last_seen DATETIME NOT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY case_key (case_key),
            KEY type (type),
            KEY status (status)
        ) {$charset};" );

        // Evidence timeline for a case (§6.4 K1-a). Every row names the
        // observation it came from (scan_id / feed fetched_at / login_log row
        // id) via `source` + `source_ref`, per the plan's acceptance line
        // "every finding cites an observation and a time". `event_key` is the
        // content address of the underlying observation — GUARDFORGE_Case_
        // Model::evidence_key, a canonicalisation deliberately SEPARATE
        // from GUARDFORGE_Event_Explainer::event_key (see that method's
        // own docblock for why unifying them is not just a rename) — and is
        // what makes re-ingesting the same scan or the same login-log rows a
        // no-op rather than duplicate evidence: the UNIQUE KEY is scoped to
        // (case_id, event_key), not event_key alone, because the same
        // underlying file+hash observation legitimately belongs to at most
        // one case but a fresh case opened after an old one closed must
        // still be able to record it.
        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_case_events (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            case_id BIGINT(20) UNSIGNED NOT NULL,
            event_key CHAR(64) NOT NULL,
            observed_at DATETIME NOT NULL,
            source VARCHAR(20) NOT NULL,
            source_ref VARCHAR(190) NOT NULL DEFAULT '',
            summary TEXT,
            payload_json TEXT,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY case_event (case_id, event_key),
            KEY case_id (case_id),
            KEY observed_at (observed_at)
        ) {$charset};" );

        // Trusted baseline (§6.4 K1-e). One row per (scope, path) — an
        // operator's own approval of a file's exact bytes at one installed
        // package version, recorded only when a file_change case is closed
        // as "expected change" (GUARDFORGE_Case_Actions::handle_expected_
        // change). `baseline_key` is the SAME sha256 identity a
        // file_change case's own case_key uses (GUARDFORGE_Case_Model::
        // case_key('file_change', [scope, path])), so a later scan looks
        // this table up with an identity it already computes for the case
        // itself — no second hashing scheme. UNIQUE, unlike guardforge_cases'
        // own case_key: a baseline is a standing opinion about one file,
        // not an investigation history, so a fresh approval REPLACES the
        // one before it (GUARDFORGE_Trusted_Baseline::record) rather than
        // accumulating a second row nobody reads.
        dbDelta( "CREATE TABLE {$wpdb->prefix}guardforge_trusted_baseline (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            baseline_key CHAR(64) NOT NULL,
            scope VARCHAR(20) NOT NULL DEFAULT 'core',
            path VARCHAR(300) NOT NULL DEFAULT '',
            file_hash CHAR(64) NOT NULL DEFAULT '',
            package_version VARCHAR(40) NOT NULL DEFAULT '',
            case_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            approved_by BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
            approved_by_login VARCHAR(200) NOT NULL DEFAULT '',
            reason TEXT,
            approved_at DATETIME NOT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY baseline_key (baseline_key),
            KEY scope (scope)
        ) {$charset};" );
    }
}