eric / piclead-client

import Foundation
import SQLite3

extension Notification.Name {
    /// Posted (on the main queue) after every successful database write.
    static let card2vcfDatabaseDidChange = Notification.Name("fr.ebii.card2vcf.databaseDidChange")
}

struct DatabaseError: Error, CustomStringConvertible {
    let message: String

    var description: String { "DatabaseError: \(message)" }

    static func last(_ db: OpaquePointer, while context: String) -> DatabaseError {
        DatabaseError(message: "\(context): \(String(cString: sqlite3_errmsg(db)))")
    }
}

/// Owner of the sqlite3 connection. Mirrors the Room schema of the Android
/// `CrmDatabase` (version 3) with destructive migration semantics.
actor SqliteDatabase {
    static let schemaVersion: Int32 = 7
    static let fileName = "card2vcf.sqlite"

    private let inMemory: Bool
    private let customPath: String?
    private var handle: OpaquePointer?

    init(inMemory: Bool = false) {
        self.inMemory = inMemory
        self.customPath = nil
    }

    /// Initializer for tests that need a specific file path (e.g. migration tests).
    init(filePath: String) {
        self.inMemory = false
        self.customPath = filePath
    }

    deinit {
        if let handle { sqlite3_close_v2(handle) }
    }

    // MARK: - Statement API

    func query<T>(
        _ sql: String,
        _ bindings: [SqliteValue] = [],
        map: (SqliteRow) throws -> T
    ) throws -> [T] {
        let db = try database()
        let stmt = try SqliteStatement(db: db, sql: sql)
        try stmt.bind(bindings)
        var rows: [T] = []
        while try stmt.step() {
            rows.append(try map(stmt.row))
        }
        return rows
    }

    /// Runs a write statement (UPDATE/DELETE/INSERT without id) and notifies.
    func write(_ sql: String, _ bindings: [SqliteValue] = []) throws {
        try run(sql, bindings)
        notifyChange()
    }

    /// Runs an INSERT and returns `sqlite3_last_insert_rowid`.
    func insert(_ sql: String, _ bindings: [SqliteValue] = []) throws -> Int64 {
        let db = try database()
        try run(sql, bindings)
        let id = sqlite3_last_insert_rowid(db)
        notifyChange()
        return id
    }

    /// Runs an INSERT OR IGNORE; returns the new row id, or -1 when the
    /// insert was ignored (Room `OnConflictStrategy.IGNORE` semantics).
    func insertIgnoring(_ sql: String, _ bindings: [SqliteValue] = []) throws -> Int64 {
        let db = try database()
        try run(sql, bindings)
        let id: Int64 = sqlite3_changes(db) > 0 ? sqlite3_last_insert_rowid(db) : -1
        notifyChange()
        return id
    }

    private func run(_ sql: String, _ bindings: [SqliteValue]) throws {
        let db = try database()
        let stmt = try SqliteStatement(db: db, sql: sql)
        try stmt.bind(bindings)
        while try stmt.step() {}
    }

    private nonisolated func notifyChange() {
        DispatchQueue.main.async {
            NotificationCenter.default.post(name: .card2vcfDatabaseDidChange, object: nil)
        }
    }

    // MARK: - Connection

    private func database() throws -> OpaquePointer {
        if let handle { return handle }
        let path: String
        if inMemory {
            path = ":memory:"
        } else if let customPath {
            path = customPath
        } else {
            path = try Self.defaultPath()
        }
        var opened: OpaquePointer?
        guard sqlite3_open(path, &opened) == SQLITE_OK, let db = opened else {
            let message = opened.map { String(cString: sqlite3_errmsg($0)) } ?? "cannot open \(path)"
            sqlite3_close_v2(opened)
            throw DatabaseError(message: message)
        }
        do {
            try Self.exec(db, "PRAGMA journal_mode=WAL;")
            try Self.exec(db, "PRAGMA foreign_keys=ON;")
            // REPLACE conflict resolution must fire the FTS delete trigger.
            try Self.exec(db, "PRAGMA recursive_triggers=ON;")
            try Self.migrateIfNeeded(db)
        } catch {
            sqlite3_close_v2(db)
            throw error
        }
        handle = db
        return db
    }

    private static func defaultPath() throws -> String {
        let dir = try FileManager.default.url(
            for: .applicationSupportDirectory,
            in: .userDomainMask,
            appropriateFor: nil,
            create: true
        )
        return dir.appendingPathComponent(fileName).path
    }

    private static func exec(_ db: OpaquePointer, _ sql: String) throws {
        var errorMessage: UnsafeMutablePointer<CChar>?
        guard sqlite3_exec(db, sql, nil, nil, &errorMessage) == SQLITE_OK else {
            let message = errorMessage.map { String(cString: $0) } ?? "sqlite3_exec failed"
            sqlite3_free(errorMessage)
            throw DatabaseError(message: message)
        }
    }

    /// Mirrors Android: additive migrations from v3 on (Room `MIGRATION_3_4`, `MIGRATION_4_5`),
    /// destructive fallback (`fallbackToDestructiveMigration()`) for anything older.
    private static func migrateIfNeeded(_ db: OpaquePointer) throws {
        let stmt = try SqliteStatement(db: db, sql: "PRAGMA user_version")
        var version: Int32 = 0
        if try stmt.step() {
            version = Int32(stmt.row.int64(0))
        }
        guard version != schemaVersion else { return }
        // v3→v4: `lastError` column on `sync_ops` (per-op push failure state).
        if version == 3 {
            try exec(db, "ALTER TABLE sync_ops ADD COLUMN lastError TEXT;")
            version = 4
        }
        // v4→v5: `notes_projet` table.
        if version == 4 {
            try exec(db, notesProjetCreateSql)
            version = 5
        }
        // v5→v6: audio + transcription columns on `interactions`.
        // Guard on table existence: some legacy test databases (v4→v5 path) may lack `interactions`.
        if version == 5 {
            if try tableExists(db, "interactions") {
                try exec(db, "ALTER TABLE interactions ADD COLUMN audioPath TEXT;")
                try exec(db, "ALTER TABLE interactions ADD COLUMN transcriptionStatut TEXT;")
                try exec(db, "ALTER TABLE interactions ADD COLUMN transcriptionErreur TEXT;")
            }
            version = 6
        }
        // v6→v7: task planning (start, working-day duration, label, grouping,
        // dependencies, subtasks) and project planning settings. Mirror of Room
        // `MIGRATION_7_8`; the server already sent these fields in the pull.
        // Same table-existence guard as above: partial test databases exist.
        if version == 6 {
            if try tableExists(db, "taches") {
                try exec(db, tachesPlanificationAlterSql)
            }
            if try tableExists(db, "projets") {
                try exec(db, projetsPlanificationAlterSql)
            }
            version = 7
        }
        if version == schemaVersion {
            try exec(db, "PRAGMA user_version = \(schemaVersion);")
            return
        }
        try exec(db, dropAllSql)
        try exec(db, schemaSql)
        try exec(db, "PRAGMA user_version = \(schemaVersion);")
    }

    private static func tableExists(_ db: OpaquePointer, _ name: String) throws -> Bool {
        let stmt = try SqliteStatement(
            db: db,
            sql: "SELECT name FROM sqlite_master WHERE type='table' AND name='\(name)'"
        )
        return try stmt.step()
    }

    private static let tachesPlanificationAlterSql = """
        ALTER TABLE taches ADD COLUMN debut TEXT;
        ALTER TABLE taches ADD COLUMN dureeJours INTEGER;
        ALTER TABLE taches ADD COLUMN etiquette TEXT;
        ALTER TABLE taches ADD COLUMN parentId TEXT;
        ALTER TABLE taches ADD COLUMN dependDeJson TEXT NOT NULL DEFAULT '[]';
        ALTER TABLE taches ADD COLUMN sousTachesJson TEXT NOT NULL DEFAULT '[]';
        """

    private static let projetsPlanificationAlterSql = """
        ALTER TABLE projets ADD COLUMN planification INTEGER NOT NULL DEFAULT 0;
        ALTER TABLE projets ADD COLUMN echeance TEXT;
        ALTER TABLE projets ADD COLUMN joursOuvres INTEGER NOT NULL DEFAULT 7;
        """

    private static let notesProjetCreateSql = """
        CREATE TABLE notes_projet (
            localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            serverId TEXT,
            projetServerId TEXT NOT NULL,
            titre TEXT NOT NULL,
            texte TEXT NOT NULL,
            audioPath TEXT,
            transcriptionStatut TEXT,
            transcriptionErreur TEXT,
            auteur TEXT NOT NULL,
            createdAt INTEGER NOT NULL,
            updatedAt INTEGER
        );
        """

    // MARK: - Schema (Room v4 mirror)

    private static let dropAllSql = """
    DROP TRIGGER IF EXISTS trg_crm_contacts_ai;
    DROP TRIGGER IF EXISTS trg_crm_contacts_ad;
    DROP TRIGGER IF EXISTS trg_crm_contacts_au;
    DROP TABLE IF EXISTS crm_contacts_fts;
    DROP TABLE IF EXISTS crm_contacts;
    DROP TABLE IF EXISTS duplicate_exclusions;
    DROP TABLE IF EXISTS entreprises;
    DROP TABLE IF EXISTS projets;
    DROP TABLE IF EXISTS taches;
    DROP TABLE IF EXISTS interactions;
    DROP TABLE IF EXISTS notes_projet;
    DROP TABLE IF EXISTS workflows;
    DROP TABLE IF EXISTS sync_ops;
    DROP TABLE IF EXISTS sync_meta;
    DROP TABLE IF EXISTS rdv;
    DROP TABLE IF EXISTS reservations;
    DROP TABLE IF EXISTS indisponibilites;
    """

    private static let schemaSql = """
    CREATE TABLE crm_contacts (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        fullName TEXT,
        firstName TEXT,
        lastName TEXT,
        company TEXT,
        jobTitle TEXT,
        phones TEXT NOT NULL,
        emails TEXT NOT NULL,
        website TEXT,
        address TEXT,
        notes TEXT,
        cardImagePath TEXT,
        profileImagePath TEXT,
        createdAt INTEGER NOT NULL,
        updatedAt INTEGER NOT NULL,
        phonesText TEXT NOT NULL,
        emailsText TEXT NOT NULL,
        serverId TEXT,
        statut TEXT,
        etape TEXT,
        tags TEXT NOT NULL,
        entrepriseServerId TEXT
    );

    CREATE VIRTUAL TABLE crm_contacts_fts USING fts5(
        fullName, firstName, lastName, company, jobTitle,
        website, address, notes, phonesText, emailsText,
        content='crm_contacts', content_rowid='id'
    );

    CREATE TRIGGER trg_crm_contacts_ai AFTER INSERT ON crm_contacts BEGIN
        INSERT INTO crm_contacts_fts(
            rowid, fullName, firstName, lastName, company, jobTitle,
            website, address, notes, phonesText, emailsText)
        VALUES (
            new.id, new.fullName, new.firstName, new.lastName, new.company, new.jobTitle,
            new.website, new.address, new.notes, new.phonesText, new.emailsText);
    END;

    CREATE TRIGGER trg_crm_contacts_ad AFTER DELETE ON crm_contacts BEGIN
        INSERT INTO crm_contacts_fts(
            crm_contacts_fts, rowid, fullName, firstName, lastName, company, jobTitle,
            website, address, notes, phonesText, emailsText)
        VALUES (
            'delete', old.id, old.fullName, old.firstName, old.lastName, old.company, old.jobTitle,
            old.website, old.address, old.notes, old.phonesText, old.emailsText);
    END;

    CREATE TRIGGER trg_crm_contacts_au AFTER UPDATE ON crm_contacts BEGIN
        INSERT INTO crm_contacts_fts(
            crm_contacts_fts, rowid, fullName, firstName, lastName, company, jobTitle,
            website, address, notes, phonesText, emailsText)
        VALUES (
            'delete', old.id, old.fullName, old.firstName, old.lastName, old.company, old.jobTitle,
            old.website, old.address, old.notes, old.phonesText, old.emailsText);
        INSERT INTO crm_contacts_fts(
            rowid, fullName, firstName, lastName, company, jobTitle,
            website, address, notes, phonesText, emailsText)
        VALUES (
            new.id, new.fullName, new.firstName, new.lastName, new.company, new.jobTitle,
            new.website, new.address, new.notes, new.phonesText, new.emailsText);
    END;

    CREATE TABLE duplicate_exclusions (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        idA INTEGER NOT NULL,
        idB INTEGER NOT NULL
    );
    CREATE UNIQUE INDEX index_duplicate_exclusions_idA_idB
        ON duplicate_exclusions(idA, idB);

    CREATE TABLE entreprises (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        nom TEXT NOT NULL,
        secteur TEXT NOT NULL,
        creePar TEXT NOT NULL,
        createdAt INTEGER NOT NULL,
        updatedAt INTEGER NOT NULL
    );

    CREATE TABLE projets (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        nom TEXT NOT NULL,
        description TEXT NOT NULL,
        workflowServerId TEXT NOT NULL,
        membresJson TEXT NOT NULL,
        creePar TEXT NOT NULL,
        planification INTEGER NOT NULL DEFAULT 0,
        echeance TEXT,
        joursOuvres INTEGER NOT NULL DEFAULT 7,
        createdAt INTEGER NOT NULL,
        updatedAt INTEGER NOT NULL
    );

    CREATE TABLE taches (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        projetLocalId INTEGER,
        projetServerId TEXT NOT NULL,
        titre TEXT NOT NULL,
        description TEXT NOT NULL,
        colonneId TEXT NOT NULL,
        ordre INTEGER NOT NULL,
        assigneA TEXT,
        debut TEXT,
        dureeJours INTEGER,
        etiquette TEXT,
        parentId TEXT,
        dependDeJson TEXT NOT NULL DEFAULT '[]',
        sousTachesJson TEXT NOT NULL DEFAULT '[]',
        auteur TEXT NOT NULL,
        createdAt INTEGER NOT NULL,
        updatedAt INTEGER NOT NULL
    );

    CREATE TABLE interactions (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        contactServerId TEXT NOT NULL,
        type TEXT NOT NULL,
        sujet TEXT NOT NULL,
        description TEXT NOT NULL,
        creePar TEXT NOT NULL,
        createdAt INTEGER NOT NULL,
        updatedAt INTEGER,
        audioPath TEXT,
        transcriptionStatut TEXT,
        transcriptionErreur TEXT
    );

    CREATE TABLE notes_projet (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        projetServerId TEXT NOT NULL,
        titre TEXT NOT NULL,
        texte TEXT NOT NULL,
        audioPath TEXT,
        transcriptionStatut TEXT,
        transcriptionErreur TEXT,
        auteur TEXT NOT NULL,
        createdAt INTEGER NOT NULL,
        updatedAt INTEGER
    );

    CREATE TABLE workflows (
        serverId TEXT PRIMARY KEY NOT NULL,
        nom TEXT NOT NULL,
        description TEXT NOT NULL,
        colonnesJson TEXT NOT NULL,
        creePar TEXT NOT NULL,
        createdAt INTEGER NOT NULL
    );

    CREATE TABLE sync_ops (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        entityType TEXT NOT NULL,
        op TEXT NOT NULL,
        payloadJson TEXT NOT NULL,
        localId INTEGER,
        serverId TEXT,
        createdAt INTEGER NOT NULL,
        attempts INTEGER NOT NULL,
        lastError TEXT
    );

    CREATE TABLE sync_meta (
        "key" TEXT PRIMARY KEY NOT NULL,
        "value" TEXT NOT NULL
    );

    CREATE TABLE rdv (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        titre TEXT NOT NULL,
        description TEXT NOT NULL,
        lieu TEXT NOT NULL,
        debutMs INTEGER NOT NULL,
        finMs INTEGER NOT NULL,
        contactIdsJson TEXT NOT NULL,
        projetId TEXT,
        updatedAt INTEGER NOT NULL,
        calendarEventId INTEGER,
        dirtyLocal INTEGER NOT NULL
    );

    CREATE TABLE reservations (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        cibleType TEXT NOT NULL,
        cibleId TEXT NOT NULL,
        debutMs INTEGER NOT NULL,
        finMs INTEGER NOT NULL,
        motif TEXT NOT NULL,
        statut TEXT NOT NULL,
        updatedAt INTEGER NOT NULL,
        calendarEventId INTEGER,
        dirtyLocal INTEGER NOT NULL,
        conflictPending INTEGER NOT NULL
    );

    CREATE TABLE indisponibilites (
        localId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        serverId TEXT,
        cibleType TEXT NOT NULL,
        cibleId TEXT NOT NULL,
        debutMs INTEGER NOT NULL,
        finMs INTEGER NOT NULL,
        nature TEXT,
        commentaire TEXT,
        updatedAt INTEGER NOT NULL,
        calendarEventId INTEGER
    );
    """
}