import Foundation
import GRDB
import XCTest
@testable import PromptGarageCore

final class DatabaseSchemaTests: XCTestCase {
    private func makeDatabaseQueue() throws -> DatabaseQueue {
        let directory = FileManager.default.temporaryDirectory
            .appendingPathComponent("PromptGarageTests-\(UUID().uuidString)", isDirectory: true)
        try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true)
        addTeardownBlock {
            try? FileManager.default.removeItem(at: directory)
        }
        return try DatabaseQueue(path: directory.appendingPathComponent("prompts.db").path)
    }

    func testSchemaCreationAndValidationAreIdempotent() throws {
        let queue = try makeDatabaseQueue()

        try queue.write { db in
            try DatabaseSchema.createIfNeeded(in: db)
            try DatabaseSchema.createIfNeeded(in: db)
        }

        let report = try DatabaseSchema.validate(queue)
        XCTAssertEqual(report.integrity, "ok")
        XCTAssertEqual(report.promptCount, 0)
        XCTAssertEqual(report.ftsCount, 0)
    }

    func testFTSTriggersFollowInsertUpdateAndDelete() throws {
        let queue = try makeDatabaseQueue()
        try queue.write { db in
            try DatabaseSchema.createIfNeeded(in: db)
            try db.execute(
                sql: "INSERT INTO prompts (title, content, tags) VALUES (?, ?, ?)",
                arguments: ["Swift Search", "prefix matching with SQLite", "swift,search"]
            )
        }

        var counts = try queue.read { db in
            (
                try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? -1,
                try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? -1
            )
        }
        XCTAssertEqual(counts.0, 1)
        XCTAssertEqual(counts.1, 1)

        try queue.write { db in
            try db.execute(sql: "UPDATE prompts SET content = ? WHERE id = 1", arguments: ["updated text"])
        }
        let updatedContent = try queue.read { db in
            try String.fetchOne(db, sql: "SELECT content FROM prompts_fts WHERE rowid = 1")
        }
        XCTAssertEqual(updatedContent, "updated text")

        try queue.write { db in
            try db.execute(sql: "DELETE FROM prompts WHERE id = 1")
        }
        counts = try queue.read { db in
            (
                try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts") ?? -1,
                try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? -1
            )
        }
        XCTAssertEqual(counts.0, 0)
        XCTAssertEqual(counts.1, 0)
    }

    func testValidationRejectsDatabaseWithoutPromptSchema() throws {
        let queue = try makeDatabaseQueue()
        try queue.write { db in
            try db.execute(sql: "CREATE TABLE unrelated (id INTEGER PRIMARY KEY)")
        }

        XCTAssertThrowsError(try DatabaseSchema.validate(queue)) { error in
            XCTAssertEqual(error as? DatabaseValidationError, .missingTables)
        }
    }

    func testRepairRebuildsStaleExternalContentFTSIndex() throws {
        let queue = try makeDatabaseQueue()
        try queue.write { db in
            try DatabaseSchema.createIfNeeded(in: db)
            try db.execute(
                sql: "INSERT INTO prompts (title, content, tags) VALUES (?, ?, ?)",
                arguments: ["Swift title", "native search", "swift"]
            )
            try db.execute(
                sql: """
                    INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
                    VALUES('delete', 1, 'Swift title', 'native search', 'swift')
                    """
            )
        }

        // External-content FTS count mirrors `prompts`, even though MATCH is
        // stale. This is the exact failure that plain row parity misses.
        let before = try queue.read { db in
            (
                try Int.fetchOne(db, sql: "SELECT COUNT(*) FROM prompts_fts") ?? -1,
                try Int.fetchOne(
                    db,
                    sql: "SELECT COUNT(*) FROM prompts_fts WHERE prompts_fts MATCH 'swift'"
                ) ?? -1
            )
        }
        XCTAssertEqual(before.0, 1)
        XCTAssertEqual(before.1, 0)

        try queue.write { db in
            try DatabaseSchema.repairSearchIndexIfNeeded(in: db)
        }
        let matches = try queue.read { db in
            try Int.fetchOne(
                db,
                sql: "SELECT COUNT(*) FROM prompts_fts WHERE prompts_fts MATCH 'swift'"
            )
        }
        XCTAssertEqual(matches, 1)
    }
}