eric / piclead-client

import Foundation

struct SqliteCrmContactDao: CrmContactDao {
    let db: SqliteDatabase

    private static let columns = """
    id, fullName, firstName, lastName, company, jobTitle, phones, emails, \
    website, address, notes, cardImagePath, profileImagePath, createdAt, updatedAt, \
    phonesText, emailsText, serverId, statut, etape, tags, entrepriseServerId
    """

    private static func map(_ row: SqliteRow) -> CrmContactEntity {
        let converters = Converters()
        var e = CrmContactEntity()
        e.id = row.int64(0)
        e.fullName = row.textOrNil(1)
        e.firstName = row.textOrNil(2)
        e.lastName = row.textOrNil(3)
        e.company = row.textOrNil(4)
        e.jobTitle = row.textOrNil(5)
        e.phones = converters.toStringList(row.textOrNil(6))
        e.emails = converters.toStringList(row.textOrNil(7))
        e.website = row.textOrNil(8)
        e.address = row.textOrNil(9)
        e.notes = row.textOrNil(10)
        e.cardImagePath = row.textOrNil(11)
        e.profileImagePath = row.textOrNil(12)
        e.createdAt = row.int64(13)
        e.updatedAt = row.int64(14)
        e.phonesText = row.text(15)
        e.emailsText = row.text(16)
        e.serverId = row.textOrNil(17)
        e.statut = row.textOrNil(18)
        e.etape = row.textOrNil(19)
        e.tags = converters.toStringList(row.textOrNil(20))
        e.entrepriseServerId = row.textOrNil(21)
        return e
    }

    /// Bindings for every column except `id`.
    private static func bindings(_ e: CrmContactEntity) -> [SqliteValue] {
        let converters = Converters()
        return [
            .optText(e.fullName),
            .optText(e.firstName),
            .optText(e.lastName),
            .optText(e.company),
            .optText(e.jobTitle),
            .text(converters.fromStringList(e.phones)),
            .text(converters.fromStringList(e.emails)),
            .optText(e.website),
            .optText(e.address),
            .optText(e.notes),
            .optText(e.cardImagePath),
            .optText(e.profileImagePath),
            .int(e.createdAt),
            .int(e.updatedAt),
            .text(e.phonesText),
            .text(e.emailsText),
            .optText(e.serverId),
            .optText(e.statut),
            .optText(e.etape),
            .text(converters.fromStringList(e.tags)),
            .optText(e.entrepriseServerId),
        ]
    }

    @discardableResult
    func insert(_ entity: CrmContactEntity) async throws -> Int64 {
        try await db.insert(
            """
            INSERT OR REPLACE INTO crm_contacts (\(Self.columns))
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            [.rowId(entity.id)] + Self.bindings(entity)
        )
    }

    func update(_ entity: CrmContactEntity) async throws {
        try await db.write(
            """
            UPDATE crm_contacts SET
                fullName = ?, firstName = ?, lastName = ?, company = ?, jobTitle = ?,
                phones = ?, emails = ?, website = ?, address = ?, notes = ?,
                cardImagePath = ?, profileImagePath = ?, createdAt = ?, updatedAt = ?,
                phonesText = ?, emailsText = ?, serverId = ?, statut = ?, etape = ?,
                tags = ?, entrepriseServerId = ?
            WHERE id = ?
            """,
            Self.bindings(entity) + [.int(entity.id)]
        )
    }

    func deleteById(_ id: Int64) async throws {
        try await db.write("DELETE FROM crm_contacts WHERE id = ?", [.int(id)])
    }

    func getById(_ id: Int64) async throws -> CrmContactEntity? {
        try await db.query(
            "SELECT \(Self.columns) FROM crm_contacts WHERE id = ?",
            [.int(id)],
            map: Self.map
        ).first
    }

    func getByServerId(_ serverId: String) async throws -> CrmContactEntity? {
        try await db.query(
            "SELECT \(Self.columns) FROM crm_contacts WHERE serverId = ?",
            [.text(serverId)],
            map: Self.map
        ).first
    }

    func deleteByServerId(_ serverId: String) async throws {
        try await db.write("DELETE FROM crm_contacts WHERE serverId = ?", [.text(serverId)])
    }

    func fetchAll() async throws -> [CrmContactEntity] {
        try await db.query(
            "SELECT \(Self.columns) FROM crm_contacts ORDER BY createdAt DESC",
            map: Self.map
        )
    }

    func getAll() async throws -> [CrmContactEntity] {
        try await fetchAll()
    }

    func searchIds(_ query: String) async throws -> [Int64] {
        try await db.query(
            "SELECT rowid FROM crm_contacts_fts WHERE crm_contacts_fts MATCH ?",
            [.text(query)]
        ) { $0.int64(0) }
    }

    @discardableResult
    func insertExclusion(_ ex: DuplicateExclusionEntity) async throws -> Int64 {
        try await db.insertIgnoring(
            "INSERT OR IGNORE INTO duplicate_exclusions (id, idA, idB) VALUES (?, ?, ?)",
            [.rowId(ex.id), .int(ex.idA), .int(ex.idB)]
        )
    }

    func getExclusions() async throws -> [DuplicateExclusionEntity] {
        try await db.query("SELECT id, idA, idB FROM duplicate_exclusions") { row in
            var e = DuplicateExclusionEntity(idA: row.int64(1), idB: row.int64(2))
            e.id = row.int64(0)
            return e
        }
    }

    func deleteExclusionsFor(_ id: Int64) async throws {
        try await db.write(
            "DELETE FROM duplicate_exclusions WHERE idA = ? OR idB = ?",
            [.int(id), .int(id)]
        )
    }
}