Sources/PromptGarageCore/Database/DatabaseSchema.swift

import Foundation
import GRDB

public enum DatabaseSchema {
    /// Columns that are required in both the legacy Python database and the
    /// native application database. `prompted_at` is intentionally not part of
    /// this set: source databases predate that column and must remain valid.
    public static let requiredLegacyColumns: Set<String> = [
        "id", "title", "content", "source", "is_starred", "tags", "model",
        "rating", "use_count", "created_at", "updated_at",
    ]

    public static let promptedAtColumn = "prompted_at"

    public static let createSQL = """
        CREATE TABLE IF NOT EXISTS prompts (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            source TEXT,
            is_starred INTEGER DEFAULT 0,
            tags TEXT,
            model TEXT,
            rating INTEGER,
            use_count INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now')),
            updated_at TEXT DEFAULT (datetime('now')),
            prompted_at TEXT
        );

        CREATE VIRTUAL TABLE IF NOT EXISTS prompts_fts USING fts5(
            title, content, tags, content=prompts, content_rowid=id
        );

        CREATE TRIGGER IF NOT EXISTS prompts_ai AFTER INSERT ON prompts BEGIN
            INSERT INTO prompts_fts(rowid, title, content, tags)
            VALUES (new.id, new.title, new.content, new.tags);
        END;

        CREATE TRIGGER IF NOT EXISTS prompts_ad AFTER DELETE ON prompts BEGIN
            INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
            VALUES('delete', old.id, old.title, old.content, old.tags);
        END;

        CREATE TRIGGER IF NOT EXISTS prompts_au AFTER UPDATE ON prompts BEGIN
            INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
            VALUES('delete', old.id, old.title, old.content, old.tags);
            INSERT INTO prompts_fts(rowid, title, content, tags)
            VALUES (new.id, new.title, new.content, new.tags);
        END;
        """

    public static func createIfNeeded(in db: Database) throws {
        try db.execute(sql: createSQL)
        try migrateDestinationIfNeeded(in: db)
        try repairSearchIndexIfNeeded(in: db)
    }

    /// Applies native-only, forward migrations. This must only be called for
    /// the application-owned copy, never while validating a selected source.
    /// Existing rows remain NULL by SQLite definition; there is deliberately
    /// no backfill from `created_at` or `updated_at`.
    public static func migrateDestinationIfNeeded(in db: Database) throws {
        let columns = try promptColumns(in: db)
        guard !columns.contains(promptedAtColumn) else { return }
        try db.execute(sql: "ALTER TABLE prompts ADD COLUMN prompted_at TEXT")
    }

    /// Verifies the actual FTS5 index against the external content table and
    /// rebuilds it only in the writable app copy when needed. `COUNT(*)` on an
    /// external-content FTS table mirrors `prompts` and cannot detect a stale
    /// or empty index by itself.
    public static func repairSearchIndexIfNeeded(in db: Database) throws {
        do {
            try verifySearchIndex(in: db)
        } catch {
            try db.execute(
                sql: "INSERT INTO prompts_fts(prompts_fts) VALUES('rebuild')"
            )
            try verifySearchIndex(in: db)
        }
    }

    /// Performs full validation on an application-owned writable database (or
    /// a writable safety copy). FTS5's external-content integrity command is
    /// syntactically an INSERT, even though it only checks the index, so this
    /// method must not be called directly on a read-only source connection.
    public static func validate(_ queue: DatabaseQueue) throws -> DatabaseValidationReport {
        let report = try validateStructureAndCounts(queue)
        do {
            try queue.writeWithoutTransaction { db in
                try verifySearchIndex(in: db)
            }
        } catch {
            throw DatabaseValidationError.ftsIndexInvalid(error.localizedDescription)
        }
        return report
    }

    /// Fully validates a read-only database without executing any write
    /// statement against it. The source is copied with SQLite's online backup
    /// API and the FTS integrity command runs only against the in-memory copy.
    public static func validateReadOnly(
        _ source: DatabaseQueue
    ) throws -> DatabaseValidationReport {
        let snapshot = try DatabaseQueue()
        defer { try? snapshot.close() }
        try source.backup(to: snapshot)
        return try validate(snapshot)
    }

    /// Structural/count validation is intentionally separate from the full
    /// check. It is used while holding a read-only source open during an atomic
    /// backup; the resulting writable copy is fully validated before install.
    static func validateStructureAndCounts(
        _ reader: any DatabaseReader
    ) throws -> DatabaseValidationReport {
        try reader.read { db in
            let integrity = try String.fetchOne(db, sql: "PRAGMA integrity_check") ?? "unknown"
            guard integrity.lowercased() == "ok" else {
                throw DatabaseValidationError.integrityFailed(integrity)
            }

            let tableNames = Set(try String.fetchAll(
                db,
                sql: "SELECT name FROM sqlite_master WHERE type IN ('table', 'view')"
            ))
            guard tableNames.contains("prompts"), tableNames.contains("prompts_fts") else {
                throw DatabaseValidationError.missingTables
            }

            let columns = try promptColumns(in: db)
            let missingColumns = requiredLegacyColumns.subtracting(columns)
            guard missingColumns.isEmpty else {
                throw DatabaseValidationError.missingColumns(missingColumns.sorted())
            }

            let triggerNames = Set(try String.fetchAll(
                db,
                sql: "SELECT name FROM sqlite_master WHERE type = 'trigger'"
            ))
            let requiredTriggers: Set<String> = ["prompts_ai", "prompts_ad", "prompts_au"]
            let missingTriggers = requiredTriggers.subtracting(triggerNames)
            guard missingTriggers.isEmpty else {
                throw DatabaseValidationError.missingTriggers(missingTriggers.sorted())
            }

            let promptCount = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? 0
            let ftsCount = try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? 0
            guard promptCount == ftsCount else {
                throw DatabaseValidationError.ftsMismatch(prompts: promptCount, fts: ftsCount)
            }

            return DatabaseValidationReport(
                integrity: integrity,
                promptCount: promptCount,
                ftsCount: ftsCount,
                hasPromptedAt: columns.contains(promptedAtColumn)
            )
        }
    }

    private static func promptColumns(in db: Database) throws -> Set<String> {
        let rows = try Row.fetchAll(db, sql: "PRAGMA table_info(prompts)")
        return Set(rows.compactMap { row -> String? in row["name"] })
    }

    private static func verifySearchIndex(in db: Database) throws {
        // rank=1 asks FTS5 to compare the index with the external content
        // table, not merely to validate its internal b-tree structure.
        try db.execute(
            sql: """
                INSERT INTO prompts_fts(prompts_fts, rank)
                VALUES('integrity-check', 1)
                """
        )
    }
}

public struct DatabaseValidationReport: Equatable, Sendable {
    public let integrity: String
    public let promptCount: Int
    public let ftsCount: Int
    public let hasPromptedAt: Bool

    public init(
        integrity: String,
        promptCount: Int,
        ftsCount: Int,
        hasPromptedAt: Bool = false
    ) {
        self.integrity = integrity
        self.promptCount = promptCount
        self.ftsCount = ftsCount
        self.hasPromptedAt = hasPromptedAt
    }
}

public enum DatabaseValidationError: LocalizedError, Equatable, Sendable {
    case integrityFailed(String)
    case missingTables
    case missingColumns([String])
    case missingTriggers([String])
    case ftsMismatch(prompts: Int, fts: Int)
    case ftsIndexInvalid(String)

    public var errorDescription: String? {
        switch self {
        case let .integrityFailed(message):
            "Die SQLite-Integritätsprüfung ist fehlgeschlagen: \(message)"
        case .missingTables:
            "Die Datenbank enthält nicht die Tabellen prompts und prompts_fts."
        case let .missingColumns(columns):
            "Der prompts-Tabelle fehlen Spalten: \(columns.joined(separator: ", "))."
        case let .missingTriggers(triggers):
            "Der Datenbank fehlen FTS-Trigger: \(triggers.joined(separator: ", "))."
        case let .ftsMismatch(prompts, fts):
            "Prompt- und FTS-Zeilen stimmen nicht überein (\(prompts) vs. \(fts))."
        case let .ftsIndexInvalid(message):
            "Der FTS-Suchindex ist inkonsistent oder nicht prüfbar: \(message)"
        }
    }
}