<?php
// SPDX-License-Identifier: GPL-3.0-or-later
declare(strict_types=1);

namespace Aiwend\Db;

/**
 * Versioned, restartable migrations (B12).
 *
 * Every statement is idempotent, so a step interrupted after some statements can be re-run: CREATE ... IF NOT EXISTS and ALTER ... MODIFY
 * to a fixed definition are by nature, and an ALTER TABLE ... ADD COLUMN / ADD KEY is made so by checking the physical table first
 * (applyStatement), because the recorded version is only a note and can lag the tables (a crash after the ALTER, a restore, a manual
 * repair). The recorded version advances only after a step completes. On MySQL/MariaDB the work runs under a per-site advisory lock
 * (GET_LOCK) and re-reads the version under it, so two simultaneous first requests never alter the same table at once and the one
 * that waited finds the work done; a site that is already current never takes the lock. Destructive downgrades are intentionally
 * unsupported.
 *
 * v1: initial tables.
 * v2: binary collation for identifier columns on MySQL/MariaDB, so request IDs,
 *     idempotency keys, budget buckets and token hashes compare case-sensitively
 *     (as they already do on SQLite).
 * v3: knowledge sources, versions, chunks and per-bot source allowlists (M2).
 * v14: AI Forms submissions (additive only, Stage E3): the table form_submissions (one row per visitor inquiry, with its own expiry and
 *     optional manual-analysis result). One CREATE TABLE IF NOT EXISTS statement per driver, so an interrupted or repeated run is harmless.
 *     Nothing existing is altered; older code ignores the table.
 * v13: provider usage details (additive only): reservations.provider, served_model, processing_tier, provider_cached_input_tokens, provider_reasoning_tokens and
 *     provider_other_tokens, NULL wherever the provider did not report them. Older code ignores the columns.
 * v12: usage measurements (additive only): an index (site_id, created_at) and reservations.request_model (the model identifier the request carried),
 *     provider_input_tokens and provider_output_tokens (the provider's own counts, only for provider-reported usage and only when both
 *     are known). NULL on every earlier row and whenever a measurement was not made: unknown, never zero. Nothing is rewritten or dropped;
 *     older code ignores the columns.
 * v10: builder hardening (additive only): knowledge_claims (per generation+source ownership leases with fencing) and
 *     knowledge_generations.cursor_source_id (fair scheduling cursor). Nothing is rewritten or dropped.
 * v9: index generations (additive only): knowledge_generations, knowledge_site_index and knowledge_versions.generation_id (NULL = the
 *     existing single-index path, unchanged). Nothing is rewritten or dropped; older code ignores the new tables and column.
 */
final class Schema
{
    public const VERSION = 14;

    /** @param int $lockTimeout seconds a migrator waits for another one (MySQL/MariaDB only) before giving up and leaving the work to a later request */
    public function __construct(private Connection $db, private int $lockTimeout = 30)
    {
    }

    /** One lock per site (the schema table carries the site's table prefix); at most 64 characters. */
    public function lockName(): string
    {
        return 'aiwend_migrate_' . md5($this->db->table('schema'));
    }

    public function currentVersion(): int
    {
        $this->db->execute('CREATE TABLE IF NOT EXISTS {schema} (id INTEGER PRIMARY KEY, version INTEGER NOT NULL)');
        $row = $this->db->selectOne('SELECT version FROM {schema} WHERE id = 1');
        return $row ? (int) $row['version'] : 0;
    }

    /** @param int|null $upTo stop at this version (used to build prior-schema fixtures) */
    public function migrate(?int $upTo = null): int
    {
        $version = $this->currentVersion();
        $steps = $this->steps();
        $pending = array_filter(array_keys($steps), static fn(int $t): bool => $t > $version && ($upTo === null || $t <= $upTo));
        if ($pending === []) {
            return $version;
        }
        $locked = $this->lock();
        try {
            // Another migrator may have finished while this one waited for the lock.
            $version = $this->currentVersion();
            foreach ($steps as $target => $statements) {
                if ($target <= $version || ($upTo !== null && $target > $upTo)) {
                    continue;
                }
                foreach ($statements as $sql) {
                    $this->applyStatement($sql);
                }
                if ($version === 0) {
                    $this->db->execute('INSERT INTO {schema} (id, version) VALUES (1, ?)', [$target]);
                } else {
                    $this->db->execute('UPDATE {schema} SET version = ? WHERE id = 1', [$target]);
                }
                $version = $target;
            }
        } finally {
            if ($locked) {
                $this->unlock();
            }
        }
        return $version;
    }

    /** @return bool true when the advisory lock is held (MySQL/MariaDB); SQLite has one writer and needs none */
    private function lock(): bool
    {
        if ($this->db->driver() !== 'mysql') {
            return false;
        }
        $row = $this->db->selectOne('SELECT GET_LOCK(?, ?) AS got', [$this->lockName(), $this->lockTimeout]);
        if ((string) ($row['got'] ?? '') !== '1') {
            throw new DatabaseException('Could not obtain the schema migration lock; another request is migrating this site.');
        }
        return true;
    }

    private function unlock(): void
    {
        try {
            $this->db->selectOne('SELECT RELEASE_LOCK(?) AS released', [$this->lockName()]);
        } catch (\Throwable) {
            // The lock dies with the connection; nothing more can be done here.
        }
    }

    /**
     * Runs one step statement. ALTER TABLE ... ADD COLUMN / ADD KEY is not idempotent, so each added column or index that the physical
     * table already has is dropped from the statement (the rest, if any, still runs as one ALTER). Only "already there" is tolerated:
     * every other failure raises and the version is not advanced. Anything else is executed unchanged.
     */
    private function applyStatement(string $sql): void
    {
        if (!preg_match('/^ALTER TABLE \{([a-z_][a-z0-9_]*)\}\s+(ADD\s.+)$/s', $sql, $m)) {
            $this->db->execute($sql);
            return;
        }
        $existing = [];
        $missing = [];
        foreach ($this->splitTopLevel($m[2]) as $item) {
            if (preg_match('/^ADD\s+COLUMN\s+([a-z_][a-z0-9_]*)\b/i', $item, $p)) {
                $kind = 'column';
            } elseif (preg_match('/^ADD\s+(?:KEY|INDEX)\s+([a-z_][a-z0-9_]*)\b/i', $item, $p)) {
                $kind = 'index';
            } else {
                $this->db->execute($sql); // a form this runner does not know how to check
                return;
            }
            $existing[$kind] ??= $this->existingNames($m[1], $kind);
            if (!in_array(strtolower($p[1]), $existing[$kind], true)) {
                $missing[] = $item;
            }
        }
        if ($missing !== []) {
            $this->db->execute('ALTER TABLE {' . $m[1] . '} ' . implode(', ', $missing));
        }
    }

    /** @return list<string> lower-case names of the table's columns or indexes */
    private function existingNames(string $table, string $kind): array
    {
        $physical = $this->db->table($table);
        if ($this->db->driver() === 'sqlite') {
            $rows = $this->db->select($kind === 'column' ? "PRAGMA table_info('$physical')" : "PRAGMA index_list('$physical')");
            return array_map(static fn(array $r): string => strtolower((string) $r['name']), $rows);
        }
        $rows = $this->db->select(($kind === 'column' ? 'SHOW COLUMNS FROM ' : 'SHOW INDEX FROM ') . '`' . $physical . '`');
        return array_map(static fn(array $r): string => strtolower((string) ($kind === 'column' ? $r['Field'] : $r['Key_name'])), $rows);
    }

    /** @return list<string> the comma-separated items of an ALTER, ignoring commas inside parentheses */
    private function splitTopLevel(string $list): array
    {
        $items = [];
        $depth = 0;
        $start = 0;
        for ($i = 0, $n = strlen($list); $i < $n; $i++) {
            if ($list[$i] === '(') {
                $depth++;
            } elseif ($list[$i] === ')') {
                $depth--;
            } elseif ($list[$i] === ',' && $depth === 0) {
                $items[] = trim(substr($list, $start, $i - $start));
                $start = $i + 1;
            }
        }
        $items[] = trim(substr($list, $start));
        return $items;
    }

    /** @return array<int, list<string>> */
    private function steps(): array
    {
        $id = $this->db->driver() === 'sqlite'
            ? 'id INTEGER PRIMARY KEY AUTOINCREMENT'
            : 'id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY';
        $text = $this->db->driver() === 'sqlite' ? 'TEXT' : 'LONGTEXT';
        $engine = $this->db->driver() === 'sqlite' ? '' : ' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci';
        $idx = fn(string $name, string $table, string $cols, bool $unique = false): string => sprintf(
            'CREATE %sINDEX IF NOT EXISTS %s ON %s (%s)',
            $unique ? 'UNIQUE ' : '',
            $this->db->table($table) . '_' . $name,
            $this->db->table($table),
            $cols
        );

        if ($this->db->driver() === 'mysql') {
            // MySQL lacks CREATE INDEX IF NOT EXISTS; indexes are declared inline instead.
            return [1 => $this->mysqlV1($id, $text, $engine), 2 => $this->mysqlV2(), 3 => $this->mysqlV3($id, $text, $engine), 4 => $this->usageBasisV4(true), 5 => $this->appearanceV5(), 6 => $this->feedbackV6($engine), 7 => $this->outcomeV7(), 8 => $this->attributionV8(true), 9 => $this->generationsV9(true, $id, $engine), 10 => $this->claimsV10(true, $id, $engine), 11 => $this->groundingV11(), 12 => $this->measurementsV12(true), 13 => $this->detailsV13(true), 14 => $this->formsV14(true, $id, $text, $engine)];
        }

        return [
            1 => [
                "CREATE TABLE IF NOT EXISTS {connections} ($id, site_id INTEGER NOT NULL, provider VARCHAR(64) NOT NULL, label VARCHAR(191) NOT NULL, ciphertext $text NOT NULL, key_version INTEGER NOT NULL, fingerprint VARCHAR(64) NOT NULL, status VARCHAR(32) NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL)",
                "CREATE TABLE IF NOT EXISTS {bots} ($id, site_id INTEGER NOT NULL, name VARCHAR(191) NOT NULL, status VARCHAR(32) NOT NULL, connection_id INTEGER NULL, model VARCHAR(191) NOT NULL, instruction $text NOT NULL, instruction_version INTEGER NOT NULL, is_public INTEGER NOT NULL, max_output_tokens INTEGER NOT NULL, response_language VARCHAR(35) NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL)",
                "CREATE TABLE IF NOT EXISTS {budgets} ($id, site_id INTEGER NOT NULL, bucket VARCHAR(191) NOT NULL, limit_units INTEGER NOT NULL, reserved_units INTEGER NOT NULL DEFAULT 0, spent_units INTEGER NOT NULL DEFAULT 0, updated_at INTEGER NOT NULL)",
                $idx('bucket', 'budgets', 'site_id, bucket', true),
                "CREATE TABLE IF NOT EXISTS {reservations} ($id, site_id INTEGER NOT NULL, request_id VARCHAR(64) NOT NULL, buckets $text NOT NULL, reserved_units INTEGER NOT NULL, settled_units INTEGER NULL, state VARCHAR(32) NOT NULL, provider_ref VARCHAR(191) NULL, note VARCHAR(191) NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL)",
                $idx('request', 'reservations', 'site_id, request_id', true),
                $idx('state', 'reservations', 'state'),
                "CREATE TABLE IF NOT EXISTS {jobs} ($id, site_id INTEGER NOT NULL, type VARCHAR(100) NOT NULL, idempotency_key VARCHAR(191) NOT NULL, payload $text NOT NULL, state VARCHAR(32) NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, max_attempts INTEGER NOT NULL, lease_owner VARCHAR(64) NULL, lease_expires INTEGER NULL, next_run_at INTEGER NOT NULL, last_error VARCHAR(500) NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL)",
                $idx('dedupe', 'jobs', 'site_id, type, idempotency_key', true),
                $idx('ready', 'jobs', 'state, next_run_at'),
                "CREATE TABLE IF NOT EXISTS {chat_sessions} ($id, site_id INTEGER NOT NULL, bot_id INTEGER NOT NULL, user_id INTEGER NULL, token_hash VARCHAR(64) NOT NULL, expires_at INTEGER NOT NULL, revoked INTEGER NOT NULL DEFAULT 0, created_at INTEGER NOT NULL)",
                $idx('token', 'chat_sessions', 'token_hash', true),
                "CREATE TABLE IF NOT EXISTS {messages} ($id, site_id INTEGER NOT NULL, session_id INTEGER NOT NULL, bot_id INTEGER NOT NULL, seq INTEGER NOT NULL, role VARCHAR(16) NOT NULL, content $text NOT NULL, request_id VARCHAR(64) NOT NULL, created_at INTEGER NOT NULL)",
                $idx('seq', 'messages', 'session_id, seq', true),
                "CREATE TABLE IF NOT EXISTS {audit} ($id, site_id INTEGER NOT NULL, actor VARCHAR(100) NOT NULL, action VARCHAR(100) NOT NULL, object_ref VARCHAR(191) NOT NULL, outcome VARCHAR(32) NOT NULL, request_id VARCHAR(64) NULL, detail VARCHAR(500) NULL, created_at INTEGER NOT NULL)",
                $idx('created', 'audit', 'created_at'),
            ],
            // SQLite compares TEXT with BINARY collation by default: nothing to change.
            2 => [],
            3 => [
                "CREATE TABLE IF NOT EXISTS {knowledge_sources} ($id, site_id INTEGER NOT NULL, owner_user_id INTEGER NULL, origin VARCHAR(16) NOT NULL, origin_ref VARCHAR(191) NOT NULL, title VARCHAR(191) NOT NULL, url VARCHAR(500) NULL, visibility VARCHAR(16) NOT NULL, status VARCHAR(32) NOT NULL, current_version_id INTEGER NULL, language VARCHAR(35) NOT NULL, error VARCHAR(500) NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL)",
                $idx('origin', 'knowledge_sources', 'site_id, origin, origin_ref', true),
                $idx('status', 'knowledge_sources', 'site_id, status'),
                "CREATE TABLE IF NOT EXISTS {knowledge_versions} ($id, site_id INTEGER NOT NULL, source_id INTEGER NOT NULL, version_no INTEGER NOT NULL, content_hash VARCHAR(64) NOT NULL, state VARCHAR(32) NOT NULL, embedding_model VARCHAR(64) NOT NULL, dimension INTEGER NOT NULL, chunk_count INTEGER NOT NULL DEFAULT 0, error VARCHAR(500) NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL)",
                $idx('srcver', 'knowledge_versions', 'source_id, version_no', true),
                "CREATE TABLE IF NOT EXISTS {knowledge_chunks} ($id, site_id INTEGER NOT NULL, source_id INTEGER NOT NULL, version_id INTEGER NOT NULL, seq INTEGER NOT NULL, anchor VARCHAR(191) NOT NULL, heading VARCHAR(191) NOT NULL, text $text NOT NULL, embedding $text NOT NULL, created_at INTEGER NOT NULL)",
                $idx('version', 'knowledge_chunks', 'version_id, seq', true),
                $idx('source', 'knowledge_chunks', 'source_id'),
                "CREATE TABLE IF NOT EXISTS {bot_sources} ($id, site_id INTEGER NOT NULL, bot_id INTEGER NOT NULL, source_id INTEGER NOT NULL, created_at INTEGER NOT NULL)",
                $idx('pair', 'bot_sources', 'bot_id, source_id', true),
                "CREATE TABLE IF NOT EXISTS {message_citations} ($id, site_id INTEGER NOT NULL, message_id INTEGER NOT NULL, n INTEGER NOT NULL, source_id INTEGER NOT NULL, version_id INTEGER NOT NULL, chunk_id INTEGER NOT NULL, anchor VARCHAR(191) NOT NULL, title VARCHAR(191) NOT NULL, url VARCHAR(500) NULL)",
                $idx('msg', 'message_citations', 'message_id, n', true),
                // Original uploaded text, kept in the database (not a web-served folder) so access is
                // policy-checked and deletion purges it.
                "CREATE TABLE IF NOT EXISTS {knowledge_blobs} ($id, site_id INTEGER NOT NULL, source_id INTEGER NOT NULL, content_type VARCHAR(16) NOT NULL, content $text NOT NULL, content_hash VARCHAR(64) NOT NULL, created_at INTEGER NOT NULL)",
                $idx('src', 'knowledge_blobs', 'source_id', true),
            ],
            4 => $this->usageBasisV4(false),
            5 => $this->appearanceV5(),
            6 => $this->feedbackV6($engine),
            7 => $this->outcomeV7(),
            8 => $this->attributionV8(false),
            9 => $this->generationsV9(false, $id, $engine),
            10 => $this->claimsV10(false, $id, $engine),
            11 => $this->groundingV11(),
            12 => $this->measurementsV12(false),
            13 => $this->detailsV13(false),
            14 => $this->formsV14(false, $id, $text, $engine),
        ];
    }

    private function feedbackV6(string $engine): array
    {
        return ["CREATE TABLE IF NOT EXISTS {message_feedback} (site_id BIGINT NOT NULL, session_id BIGINT NOT NULL, message_id BIGINT NOT NULL, rating INTEGER NOT NULL, updated_at BIGINT NOT NULL, PRIMARY KEY (site_id, message_id))$engine"];
    }

    /**
     * v4: where a settled amount comes from (Reservation::BASIS_*) and the original estimate
     * when an estimate is later replaced. One ALTER on MySQL/MariaDB, so it applies atomically.
     * Rows settled earlier are marked: the old "charged-reserved-estimate" ones as estimates,
     * all others as legacy (their basis was not recorded).
     *
     * @return list<string>
     */
    private function usageBasisV4(bool $mysql): array
    {
        $alter = $mysql
            ? ['ALTER TABLE {reservations} ADD COLUMN usage_basis VARCHAR(24) NULL, ADD COLUMN estimate_units BIGINT NULL']
            : ['ALTER TABLE {reservations} ADD COLUMN usage_basis VARCHAR(24) NULL', 'ALTER TABLE {reservations} ADD COLUMN estimate_units INTEGER NULL'];
        return array_merge($alter, [
            "UPDATE {reservations} SET usage_basis = 'estimated_unconfirmed', estimate_units = settled_units WHERE state = 'settled' AND note = 'charged-reserved-estimate'",
            "UPDATE {reservations} SET usage_basis = 'legacy' WHERE state = 'settled' AND usage_basis IS NULL",
        ]);
    }

    /** @return list<string> */
    private function mysqlV3(string $id, string $text, string $engine): array
    {
        $bin = 'CHARACTER SET ascii COLLATE ascii_bin';
        return [
            "CREATE TABLE IF NOT EXISTS {knowledge_sources} ($id, site_id BIGINT NOT NULL, owner_user_id BIGINT NULL, origin VARCHAR(16) $bin NOT NULL, origin_ref VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, title VARCHAR(191) NOT NULL, url VARCHAR(500) NULL, visibility VARCHAR(16) $bin NOT NULL, status VARCHAR(32) $bin NOT NULL, current_version_id BIGINT NULL, language VARCHAR(35) $bin NOT NULL, error VARCHAR(500) NULL, created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL, UNIQUE KEY origin (site_id, origin, origin_ref), KEY status (site_id, status))$engine",
            "CREATE TABLE IF NOT EXISTS {knowledge_versions} ($id, site_id BIGINT NOT NULL, source_id BIGINT NOT NULL, version_no INT NOT NULL, content_hash CHAR(64) $bin NOT NULL, state VARCHAR(32) $bin NOT NULL, embedding_model VARCHAR(64) $bin NOT NULL, dimension INT NOT NULL, chunk_count INT NOT NULL DEFAULT 0, error VARCHAR(500) NULL, created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL, UNIQUE KEY srcver (source_id, version_no))$engine",
            "CREATE TABLE IF NOT EXISTS {knowledge_chunks} ($id, site_id BIGINT NOT NULL, source_id BIGINT NOT NULL, version_id BIGINT NOT NULL, seq INT NOT NULL, anchor VARCHAR(191) NOT NULL, heading VARCHAR(191) NOT NULL, text $text NOT NULL, embedding $text NOT NULL, created_at BIGINT NOT NULL, UNIQUE KEY version (version_id, seq), KEY source (source_id))$engine",
            "CREATE TABLE IF NOT EXISTS {bot_sources} ($id, site_id BIGINT NOT NULL, bot_id BIGINT NOT NULL, source_id BIGINT NOT NULL, created_at BIGINT NOT NULL, UNIQUE KEY pair (bot_id, source_id))$engine",
            "CREATE TABLE IF NOT EXISTS {message_citations} ($id, site_id BIGINT NOT NULL, message_id BIGINT NOT NULL, n INT NOT NULL, source_id BIGINT NOT NULL, version_id BIGINT NOT NULL, chunk_id BIGINT NOT NULL, anchor VARCHAR(191) NOT NULL, title VARCHAR(191) NOT NULL, url VARCHAR(500) NULL, UNIQUE KEY msg (message_id, n))$engine",
            "CREATE TABLE IF NOT EXISTS {knowledge_blobs} ($id, site_id BIGINT NOT NULL, source_id BIGINT NOT NULL, content_type VARCHAR(16) $bin NOT NULL, content $text NOT NULL, content_hash CHAR(64) $bin NOT NULL, created_at BIGINT NOT NULL, UNIQUE KEY src (source_id))$engine",
        ];
    }

    /** @return list<string> */
    private function mysqlV2(): array
    {
        $bin = 'CHARACTER SET ascii COLLATE ascii_bin';
        return [
            "ALTER TABLE {reservations} MODIFY request_id VARCHAR(64) $bin NOT NULL",
            "ALTER TABLE {messages} MODIFY request_id VARCHAR(64) $bin NOT NULL",
            "ALTER TABLE {audit} MODIFY request_id VARCHAR(64) $bin NULL",
            "ALTER TABLE {jobs} MODIFY type VARCHAR(100) $bin NOT NULL",
            // Idempotency keys are caller-defined and may be non-ASCII: byte-wise utf8mb4.
            "ALTER TABLE {jobs} MODIFY idempotency_key VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL",
            "ALTER TABLE {budgets} MODIFY bucket VARCHAR(191) $bin NOT NULL",
            "ALTER TABLE {chat_sessions} MODIFY token_hash CHAR(64) $bin NOT NULL",
        ];
    }

    /** @return list<string> */
    private function mysqlV1(string $id, string $text, string $engine): array
    {
        return [
            "CREATE TABLE IF NOT EXISTS {connections} ($id, site_id BIGINT NOT NULL, provider VARCHAR(64) NOT NULL, label VARCHAR(191) NOT NULL, ciphertext $text NOT NULL, key_version INT NOT NULL, fingerprint VARCHAR(64) NOT NULL, status VARCHAR(32) NOT NULL, created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL)$engine",
            "CREATE TABLE IF NOT EXISTS {bots} ($id, site_id BIGINT NOT NULL, name VARCHAR(191) NOT NULL, status VARCHAR(32) NOT NULL, connection_id BIGINT NULL, model VARCHAR(191) NOT NULL, instruction $text NOT NULL, instruction_version INT NOT NULL, is_public TINYINT NOT NULL, max_output_tokens INT NOT NULL, response_language VARCHAR(35) NOT NULL, created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL)$engine",
            "CREATE TABLE IF NOT EXISTS {budgets} ($id, site_id BIGINT NOT NULL, bucket VARCHAR(191) NOT NULL, limit_units BIGINT NOT NULL, reserved_units BIGINT NOT NULL DEFAULT 0, spent_units BIGINT NOT NULL DEFAULT 0, updated_at BIGINT NOT NULL, UNIQUE KEY bucket (site_id, bucket))$engine",
            "CREATE TABLE IF NOT EXISTS {reservations} ($id, site_id BIGINT NOT NULL, request_id VARCHAR(64) NOT NULL, buckets $text NOT NULL, reserved_units BIGINT NOT NULL, settled_units BIGINT NULL, state VARCHAR(32) NOT NULL, provider_ref VARCHAR(191) NULL, note VARCHAR(191) NULL, created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL, UNIQUE KEY request (site_id, request_id), KEY state (state))$engine",
            "CREATE TABLE IF NOT EXISTS {jobs} ($id, site_id BIGINT NOT NULL, type VARCHAR(100) NOT NULL, idempotency_key VARCHAR(191) NOT NULL, payload $text NOT NULL, state VARCHAR(32) NOT NULL, attempts INT NOT NULL DEFAULT 0, max_attempts INT NOT NULL, lease_owner VARCHAR(64) NULL, lease_expires BIGINT NULL, next_run_at BIGINT NOT NULL, last_error VARCHAR(500) NULL, created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL, UNIQUE KEY dedupe (site_id, type, idempotency_key), KEY ready (state, next_run_at))$engine",
            "CREATE TABLE IF NOT EXISTS {chat_sessions} ($id, site_id BIGINT NOT NULL, bot_id BIGINT NOT NULL, user_id BIGINT NULL, token_hash CHAR(64) NOT NULL, expires_at BIGINT NOT NULL, revoked TINYINT NOT NULL DEFAULT 0, created_at BIGINT NOT NULL, UNIQUE KEY token (token_hash))$engine",
            "CREATE TABLE IF NOT EXISTS {messages} ($id, site_id BIGINT NOT NULL, session_id BIGINT NOT NULL, bot_id BIGINT NOT NULL, seq INT NOT NULL, role VARCHAR(16) NOT NULL, content $text NOT NULL, request_id VARCHAR(64) NOT NULL, created_at BIGINT NOT NULL, UNIQUE KEY seq (session_id, seq))$engine",
            "CREATE TABLE IF NOT EXISTS {audit} ($id, site_id BIGINT NOT NULL, actor VARCHAR(100) NOT NULL, action VARCHAR(100) NOT NULL, object_ref VARCHAR(191) NOT NULL, outcome VARCHAR(32) NOT NULL, request_id VARCHAR(64) NULL, detail VARCHAR(500) NULL, created_at BIGINT NOT NULL, KEY created (created_at))$engine",
        ];
    }

    /**
     * v5: per-assistant widget appearance (JSON: display name, welcome message, accent colour,
     * placement), validated by BotRepository before it is stored. NULL = defaults.
     *
     * @return list<string>
     */
    private function appearanceV5(): array
    {
        // A single statement on every engine, so it applies atomically.
        return ['ALTER TABLE {bots} ADD COLUMN appearance TEXT NULL'];
    }

    /**
     * v7: how an assistant response ended: 'completed' or 'stopped' (incomplete). NULL on rows written
     * before v7 means completed. One statement, so it applies atomically.
     */
    private function outcomeV7(): array
    {
        return ['ALTER TABLE {messages} ADD COLUMN outcome VARCHAR(16) NULL'];
    }

    /**
     * v8: usage attribution. origin = simulated | provider | test (an owner-authorized connection
     * test); bot_id and connection_id say which assistant and connection a request used. NULL on
     * rows written before v8 (origin unknown unless other stored evidence shows it).
     */
    /**
     * v10: builder hardening (additive only): knowledge_claims (per generation+source ownership leases with fencing) and
 *     knowledge_generations.cursor_source_id (fair scheduling cursor). Nothing is rewritten or dropped.
 * v9: index generations. A generation is one complete, versioned index of a site's knowledge built with one provider, model and
     * dimension; the knowledge_site_index row holds the single active generation pointer and a revision used for compare-and-swap
     * activation. All statements are additive: tables are created if missing first and the one ALTER comes last, so a partly applied
     * step can be re-run.
     */
    private function generationsV9(bool $mysql, string $id, string $engine): array
    {
        if ($mysql) {
            return [
                "CREATE TABLE IF NOT EXISTS {knowledge_generations} ($id, site_id BIGINT NOT NULL, state VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, provider VARCHAR(32) NOT NULL, model VARCHAR(64) NOT NULL, dimension INT NOT NULL, fingerprint CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, config_version INT NOT NULL, sources_total INT NOT NULL DEFAULT 0, sources_built INT NOT NULL DEFAULT 0, sources_failed INT NOT NULL DEFAULT 0, error VARCHAR(40) NULL, created_at BIGINT NOT NULL, activated_at BIGINT NULL, superseded_at BIGINT NULL, retain_until BIGINT NULL, updated_at BIGINT NOT NULL, KEY site_state (site_id, state))$engine",
                "CREATE TABLE IF NOT EXISTS {knowledge_site_index} (site_id BIGINT NOT NULL PRIMARY KEY, active_generation_id BIGINT NULL, previous_generation_id BIGINT NULL, revision INT NOT NULL DEFAULT 0, updated_at BIGINT NOT NULL)$engine",
                'ALTER TABLE {knowledge_versions} ADD COLUMN generation_id BIGINT NULL, ADD KEY generation (generation_id, source_id)',
            ];
        }
        return [
            "CREATE TABLE IF NOT EXISTS {knowledge_generations} ($id, site_id INTEGER NOT NULL, state VARCHAR(16) NOT NULL, provider VARCHAR(32) NOT NULL, model VARCHAR(64) NOT NULL, dimension INTEGER NOT NULL, fingerprint VARCHAR(64) NOT NULL, config_version INTEGER NOT NULL, sources_total INTEGER NOT NULL DEFAULT 0, sources_built INTEGER NOT NULL DEFAULT 0, sources_failed INTEGER NOT NULL DEFAULT 0, error VARCHAR(40) NULL, created_at INTEGER NOT NULL, activated_at INTEGER NULL, superseded_at INTEGER NULL, retain_until INTEGER NULL, updated_at INTEGER NOT NULL)",
            'CREATE INDEX IF NOT EXISTS ' . $this->db->table('knowledge_generations') . '_site_state ON ' . $this->db->table('knowledge_generations') . ' (site_id, state)',
            'CREATE TABLE IF NOT EXISTS {knowledge_site_index} (site_id INTEGER NOT NULL PRIMARY KEY, active_generation_id INTEGER NULL, previous_generation_id INTEGER NULL, revision INTEGER NOT NULL DEFAULT 0, updated_at INTEGER NOT NULL)',
            'ALTER TABLE {knowledge_versions} ADD COLUMN generation_id INTEGER NULL',
            'CREATE INDEX IF NOT EXISTS ' . $this->db->table('knowledge_versions') . '_generation ON ' . $this->db->table('knowledge_versions') . ' (generation_id, source_id)',
        ];
    }

    /**
     * v10: knowledge_claims is one row per (site, generation, source) task: the owner token of the worker that holds it, a lease expiry,
     * a state (active | blocked), the content hash it was claimed for and, once embedding starts, the operation id prefix and batch
     * count (so a successor can look up what a dead worker may have dispatched). knowledge_generations.cursor_source_id is the
     * scheduling cursor. Table first (IF NOT EXISTS), the single ALTER last, so a partly applied step can be re-run.
     */
    private function claimsV10(bool $mysql, string $id, string $engine): array
    {
        if ($mysql) {
            return [
                "CREATE TABLE IF NOT EXISTS {knowledge_claims} ($id, site_id BIGINT NOT NULL, generation_id BIGINT NOT NULL, source_id BIGINT NOT NULL, owner VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, state VARCHAR(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, content_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, operation VARCHAR(100) CHARACTER SET ascii COLLATE ascii_bin NULL, batches INT NOT NULL DEFAULT 0, beats BIGINT NOT NULL DEFAULT 0, expires_at BIGINT NOT NULL, created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL, UNIQUE KEY task (site_id, generation_id, source_id), KEY source (site_id, source_id))$engine",
                'ALTER TABLE {knowledge_generations} ADD COLUMN cursor_source_id BIGINT NOT NULL DEFAULT 0',
            ];
        }
        return [
            'CREATE TABLE IF NOT EXISTS {knowledge_claims} (' . $id . ', site_id INTEGER NOT NULL, generation_id INTEGER NOT NULL, source_id INTEGER NOT NULL, owner VARCHAR(32) NOT NULL, state VARCHAR(12) NOT NULL, content_hash VARCHAR(64) NOT NULL, operation VARCHAR(100) NULL, batches INTEGER NOT NULL DEFAULT 0, beats INTEGER NOT NULL DEFAULT 0, expires_at INTEGER NOT NULL, created_at INTEGER NOT NULL, updated_at INTEGER NOT NULL)',
            'CREATE UNIQUE INDEX IF NOT EXISTS ' . $this->db->table('knowledge_claims') . '_task ON ' . $this->db->table('knowledge_claims') . ' (site_id, generation_id, source_id)',
            'ALTER TABLE {knowledge_generations} ADD COLUMN cursor_source_id INTEGER NOT NULL DEFAULT 0',
        ];
    }

    /**
     * v11: grounding provenance of an assistant message: the ids of every source whose text was sent to the model for that answer, as
     * ",3,7," ('' = nothing was sent). NULL on rows written before v11 (provenance unknown, treated conservatively). One statement,
     * so it applies atomically; erasing a message erases its provenance with it.
     */
    private function groundingV11(): array
    {
        return ['ALTER TABLE {messages} ADD COLUMN grounding VARCHAR(255) NULL'];
    }

    /**
     * v12: what a request measured, plus the one index the reports need. One statement on MySQL and MariaDB (applied atomically); four
     * on SQLite, like v8. The index (site_id, created_at) is justified by a measured plan: on 200,000 rows the report queries over a
     * time window scanned the whole site's rows (MariaDB: type ALL, 198,048 rows; MySQL: ref on the request key, about 99,000) and use
     * a range of about 23,000 rows with it (evidence: tools/itest-evidence/phase118/bench-*.json, docs/stage-b1-cost-transparency-design.md).
     */
    private function measurementsV12(bool $mysql): array
    {
        return $mysql
            ? ['ALTER TABLE {reservations} ADD COLUMN request_model VARCHAR(191) NULL, ADD COLUMN provider_input_tokens BIGINT NULL, ADD COLUMN provider_output_tokens BIGINT NULL, ADD INDEX site_time (site_id, created_at)']
            : [
                'ALTER TABLE {reservations} ADD COLUMN request_model VARCHAR(191) NULL',
                'ALTER TABLE {reservations} ADD COLUMN provider_input_tokens INTEGER NULL',
                'ALTER TABLE {reservations} ADD COLUMN provider_output_tokens INTEGER NULL',
                'CREATE INDEX IF NOT EXISTS {reservations}_site_time ON {reservations} (site_id, created_at)',
            ];
    }

    /**
     * v13: what a price needs to know about a provider-reported request (additive, all NULL = not reported): the provider, the model the
     * provider says answered, and the cached, reasoning and other token counts that the input/output totals contain or leave out.
     * One statement on MySQL and MariaDB (atomic), six on SQLite. No new index: the cost reports are bounded by the v12 (site_id,
     * created_at) index. Nothing is rewritten; history keeps NULL (unknown), so it is never priced as if the details were zero.
     */
    private function detailsV13(bool $mysql): array
    {
        return $mysql
            ? ['ALTER TABLE {reservations} ADD COLUMN provider VARCHAR(32) NULL, ADD COLUMN served_model VARCHAR(191) NULL, ADD COLUMN provider_cached_input_tokens BIGINT NULL, ADD COLUMN provider_reasoning_tokens BIGINT NULL, ADD COLUMN provider_other_tokens BIGINT NULL, ADD COLUMN processing_tier VARCHAR(16) NULL']
            : [
                'ALTER TABLE {reservations} ADD COLUMN provider VARCHAR(32) NULL',
                'ALTER TABLE {reservations} ADD COLUMN served_model VARCHAR(191) NULL',
                'ALTER TABLE {reservations} ADD COLUMN provider_cached_input_tokens INTEGER NULL',
                'ALTER TABLE {reservations} ADD COLUMN provider_reasoning_tokens INTEGER NULL',
                'ALTER TABLE {reservations} ADD COLUMN provider_other_tokens INTEGER NULL',
                'ALTER TABLE {reservations} ADD COLUMN processing_tier VARCHAR(16) NULL',
            ];
    }

    /**
     * v14: AI Forms inbox. The visitor's name (optional), email, topic and message are the only personal data; no IP address, user agent, referrer
     * or user ID is stored. `dedupe` is a keyed hash that makes a repeated identical submission within a short window a no-op (unique key).
     * Indexes follow the actual queries: the inbox (site, form, newest first), the expiry sweep (site, expires_at), the unread count
     * (site, read_at) and the privacy tools (site, email).
     *
     * @return list<string>
     */
    private function formsV14(bool $mysql, string $id, string $text, string $engine): array
    {
        if ($mysql) {
            return ["CREATE TABLE IF NOT EXISTS {form_submissions} ($id, site_id BIGINT NOT NULL, form_id BIGINT NOT NULL, created_at BIGINT NOT NULL, expires_at BIGINT NOT NULL, read_at BIGINT NULL, topic VARCHAR(100) NOT NULL DEFAULT '', name VARCHAR(100) NOT NULL DEFAULT '', email VARCHAR(254) NOT NULL, message $text NOT NULL, ack TINYINT NOT NULL DEFAULT 0, dedupe CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, analysis_state VARCHAR(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'none', analysis_request VARCHAR(40) CHARACTER SET ascii COLLATE ascii_bin NULL, analysis_at BIGINT NULL, summary TEXT NULL, category VARCHAR(20) NULL, reply $text NULL, analysis_model VARCHAR(128) NULL, UNIQUE KEY dedupe_key (site_id, form_id, dedupe), KEY inbox_key (site_id, form_id, created_at), KEY expiry_key (site_id, expires_at), KEY unread_key (site_id, read_at), KEY email_key (site_id, email))$engine"];
        }
        $t = $this->db->table('form_submissions');
        return [
            "CREATE TABLE IF NOT EXISTS {form_submissions} ($id, site_id INTEGER NOT NULL, form_id INTEGER NOT NULL, created_at INTEGER NOT NULL, expires_at INTEGER NOT NULL, read_at INTEGER NULL, topic VARCHAR(100) NOT NULL DEFAULT '', name VARCHAR(100) NOT NULL DEFAULT '', email VARCHAR(254) NOT NULL, message $text NOT NULL, ack INTEGER NOT NULL DEFAULT 0, dedupe VARCHAR(64) NOT NULL, analysis_state VARCHAR(12) NOT NULL DEFAULT 'none', analysis_request VARCHAR(40) NULL, analysis_at INTEGER NULL, summary TEXT NULL, category VARCHAR(20) NULL, reply $text NULL, analysis_model VARCHAR(128) NULL)",
            "CREATE UNIQUE INDEX IF NOT EXISTS {$t}_dedupe_key ON $t (site_id, form_id, dedupe)",
            "CREATE INDEX IF NOT EXISTS {$t}_inbox_key ON $t (site_id, form_id, created_at)",
            "CREATE INDEX IF NOT EXISTS {$t}_expiry_key ON $t (site_id, expires_at)",
            "CREATE INDEX IF NOT EXISTS {$t}_unread_key ON $t (site_id, read_at)",
            "CREATE INDEX IF NOT EXISTS {$t}_email_key ON $t (site_id, email)",
        ];
    }

    private function attributionV8(bool $mysql): array
    {
        return $mysql
            ? ['ALTER TABLE {reservations} ADD COLUMN origin VARCHAR(16) NULL, ADD COLUMN bot_id BIGINT NULL, ADD COLUMN connection_id BIGINT NULL']
            : ['ALTER TABLE {reservations} ADD COLUMN origin VARCHAR(16) NULL', 'ALTER TABLE {reservations} ADD COLUMN bot_id INTEGER NULL', 'ALTER TABLE {reservations} ADD COLUMN connection_id INTEGER NULL'];
    }
}