import Foundation
import GRDB
import XCTest
@testable import PromptGarageCore

final class DatabaseBootstrapServiceTests: XCTestCase {
    func testInstallCopiesLegacyDatabaseWithoutChangingSourceOrBackfillingDates() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageBootstrap")
        let sourceURL = root.appendingPathComponent("legacy-prompts.db")
        try createLegacyDatabase(at: sourceURL)

        let sourceBefore = try Data(contentsOf: sourceURL)
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )

        let sourceReport = try service.validateSource(at: sourceURL)
        XCTAssertFalse(sourceReport.hasPromptedAt)
        XCTAssertEqual(sourceReport.promptCount, 1)

        let repository = try service.installDatabase(from: sourceURL)

        XCTAssertEqual(try Data(contentsOf: sourceURL), sourceBefore)
        XCTAssertFalse(try columnNames(at: sourceURL).contains("prompted_at"))
        XCTAssertTrue(try columnNames(at: service.databaseURL).contains("prompted_at"))
        XCTAssertNil(try repository.prompt(id: 1)?.promptedAt)
        XCTAssertEqual(try repository.legacyUndatedCount(), 1)
        XCTAssertTrue(try repository.dayGroups().isEmpty)

        let destinationReport = try DatabaseSchema.validate(
            DatabaseQueue(path: service.databaseURL.path)
        )
        XCTAssertTrue(destinationReport.hasPromptedAt)
        XCTAssertEqual(destinationReport.promptCount, destinationReport.ftsCount)
    }

    func testNewImportsAreDatedWithoutChangingLegacyTagsOrDates() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageChronology")
        let sourceURL = root.appendingPathComponent("legacy-prompts.db")
        try createLegacyDatabase(at: sourceURL)
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        let repository = try service.installDatabase(from: sourceURL)

        let promptedAt = "2026-07-15T22:30:00.000Z"
        let statistics = try repository.importCandidates([
            ImportCandidate(
                title: "A new Mac prompt",
                content: "Explain a safe native macOS migration with a real end to end verification step",
                source: "manual-import:0123456789ab",
                tags: "swift,mac",
                model: nil,
                promptedAt: promptedAt
            ),
        ])

        XCTAssertEqual(statistics.imported, 1)
        XCTAssertEqual(try repository.legacyUndatedCount(), 1)
        let expectedLocalDay = try localDay(for: promptedAt)
        XCTAssertEqual(
            try repository.dayGroups(),
            [CountedValue(name: expectedLocalDay, count: 1)]
        )
        XCTAssertEqual(
            try repository.page(matching: PromptQuery(promptedDay: expectedLocalDay)).total,
            1
        )
        XCTAssertEqual(try repository.prompt(id: 1)?.tags, "legacy,testing")
        XCTAssertNil(try repository.prompt(id: 1)?.promptedAt)

        let topics = try repository.topicGroups()
        XCTAssertEqual(topics.first(where: { $0.name == "legacy" })?.count, 1)
        XCTAssertEqual(topics.first(where: { $0.name == "swift" })?.count, 1)
    }

    func testStaleFTSIsRejectedReadOnlyButRepairedInWritableAppCopy() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageStaleFTS")
        let databaseURL = root.appendingPathComponent("prompts.db")
        try createLegacyDatabase(at: databaseURL)

        let corruptor = try DatabaseQueue(path: databaseURL.path)
        try corruptor.write { db in
            try db.execute(sql: """
                INSERT INTO prompts_fts(prompts_fts, rowid, title, content, tags)
                VALUES('delete', 1, 'Legacy prompt',
                       'This legacy prompt must retain its original metadata without inferred dates',
                       'legacy,testing')
                """)
        }
        try corruptor.close()

        var readOnlyConfiguration = Configuration()
        readOnlyConfiguration.readonly = true
        let readOnly = try DatabaseQueue(
            path: databaseURL.path,
            configuration: readOnlyConfiguration
        )
        defer { try? readOnly.close() }
        XCTAssertThrowsError(try DatabaseSchema.validateReadOnly(readOnly)) { error in
            guard let validationError = error as? DatabaseValidationError,
                  case .ftsIndexInvalid = validationError
            else {
                return XCTFail("Expected ftsIndexInvalid, got \(error)")
            }
        }
        XCTAssertThrowsError(
            try PromptRepository(databaseURL: databaseURL, readOnly: true)
        )

        let writable = try PromptRepository(databaseURL: databaseURL)
        XCTAssertEqual(try writable.page(matching: PromptQuery(text: "Legacy")).total, 1)
    }

    func testInstallRefusesValidExistingDestinationAndPreservesEveryByte() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageNoReplacement")
        let sourceURL = root.appendingPathComponent("source.db")
        try createLegacyDatabase(at: sourceURL)
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        try createLegacyDatabase(at: service.databaseURL)
        let destinationBefore = try Data(contentsOf: service.databaseURL)

        XCTAssertThrowsError(try service.installDatabase(from: sourceURL)) { error in
            XCTAssertEqual(
                error as? DatabaseBootstrapError,
                .destinationAlreadyExists(service.databaseURL)
            )
            XCTAssertTrue(error.localizedDescription.contains("niemals automatisch"))
            XCTAssertTrue(error.localizedDescription.contains("verschiebe oder benenne"))
        }

        XCTAssertEqual(try Data(contentsOf: service.databaseURL), destinationBefore)
        XCTAssertFalse(try columnNames(at: service.databaseURL).contains("prompted_at"))
    }

    func testDestinationAppearingAfterLaunchCheckRefusesBeforeSourceAccessAndPreservesBytes() throws {
        let root = try makeTemporaryDirectory(prefix: "PromptGarageInvalidNoReplacement")
        let service = try DatabaseBootstrapService(
            applicationSupportRoot: root.appendingPathComponent("ApplicationSupport")
        )
        XCTAssertEqual(service.inspect(), .needsDatabase)

        // Simulate a destination appearing after AppModel's first-launch state
        // check but before its detached install operation begins. The selected
        // source is intentionally missing: destinationAlreadyExists must win,
        // proving installDatabase did not inspect or open the source first.
        let invalidBytes = Data("not a sqlite database; preserve me exactly".utf8)
        try invalidBytes.write(to: service.databaseURL)
        let missingSource = root.appendingPathComponent("missing-source.db")

        XCTAssertThrowsError(try service.installDatabase(from: missingSource)) { error in
            XCTAssertEqual(
                error as? DatabaseBootstrapError,
                .destinationAlreadyExists(service.databaseURL)
            )
        }

        XCTAssertEqual(try Data(contentsOf: service.databaseURL), invalidBytes)
    }

    func testFourWayParityGateRejectsAnyCountMismatch() throws {
        let source = DatabaseValidationReport(
            integrity: "ok",
            promptCount: 7,
            ftsCount: 7
        )
        let destination = DatabaseValidationReport(
            integrity: "ok",
            promptCount: 7,
            ftsCount: 6,
            hasPromptedAt: true
        )

        XCTAssertThrowsError(try DatabaseBootstrapService.validateCopyParity(
            source: source,
            destination: destination
        )) { error in
            XCTAssertEqual(
                error as? DatabaseBootstrapError,
                .copyParityMismatch(
                    sourcePrompts: 7,
                    sourceFTS: 7,
                    destinationPrompts: 7,
                    destinationFTS: 6
                )
            )
        }
    }

    private func makeTemporaryDirectory(prefix: String) throws -> URL {
        let directory = FileManager.default.temporaryDirectory
            .appendingPathComponent("\(prefix)-\(UUID().uuidString)", isDirectory: true)
        try FileManager.default.createDirectory(at: directory, withIntermediateDirectories: true)
        addTeardownBlock { try? FileManager.default.removeItem(at: directory) }
        return directory
    }

    private func createLegacyDatabase(at url: URL) throws {
        let queue = try DatabaseQueue(path: url.path)
        try queue.write { db in
            try db.execute(sql: """
                CREATE TABLE 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'))
                );
                CREATE VIRTUAL TABLE prompts_fts USING fts5(
                    title, content, tags, content=prompts, content_rowid=id
                );
                CREATE TRIGGER 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 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 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;
                INSERT INTO prompts (
                    id, title, content, source, tags, created_at, updated_at
                ) VALUES (
                    1,
                    'Legacy prompt',
                    'This legacy prompt must retain its original metadata without inferred dates',
                    'manual',
                    'legacy,testing',
                    '2024-01-02 03:04:05',
                    '2024-01-02 03:04:05'
                );
                """)
        }
        try queue.close()
    }

    private func columnNames(at url: URL) throws -> Set<String> {
        var configuration = Configuration()
        configuration.readonly = true
        let queue = try DatabaseQueue(path: url.path, configuration: configuration)
        defer { try? queue.close() }
        return try queue.read { db in
            let rows = try Row.fetchAll(db, sql: "PRAGMA table_info(prompts)")
            return Set(rows.compactMap { row -> String? in row["name"] })
        }
    }

    private func localDay(for timestamp: String) throws -> String {
        let parser = ISO8601DateFormatter()
        parser.formatOptions = [.withInternetDateTime, .withFractionalSeconds]
        let date = try XCTUnwrap(parser.date(from: timestamp))
        let formatter = DateFormatter()
        formatter.calendar = Calendar(identifier: .gregorian)
        formatter.locale = Locale(identifier: "en_US_POSIX")
        formatter.timeZone = .current
        formatter.dateFormat = "yyyy-MM-dd"
        return formatter.string(from: date)
    }
}