import SQLiteNIO

/// One todo item as the page shows it.
public struct Todo: Sendable, Equatable {
    public var id: Int
    public var title: String
    public var completed: Bool
}

/// The app's SQLite database: one file, WAL mode, one connection.
///
/// SQLiteNIO runs every call on its thread pool, and the one connection
/// serialises them. Each write below is a single SQL statement, so two
/// requests can never interleave half a change, and no transaction has to
/// span an `await`.
public final class Store: Sendable {
    let db: SQLiteConnection

    /// Schema changes, in order. Only ever append to this list; never edit
    /// or remove an entry, and never drop or rewrite data.
    /// `PRAGMA user_version` records how many have run.
    static let migrations: [String] = [
        """
        CREATE TABLE lists (
            id         INTEGER PRIMARY KEY,
            token      TEXT    NOT NULL UNIQUE,
            created_at INTEGER NOT NULL DEFAULT (unixepoch())
        );
        CREATE TABLE todos (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            list_id    INTEGER NOT NULL REFERENCES lists(id),
            title      TEXT    NOT NULL,
            completed  INTEGER NOT NULL DEFAULT 0,
            created_at INTEGER NOT NULL DEFAULT (unixepoch())
        );
        CREATE INDEX todos_by_list ON todos(list_id, id);
        """,
    ]

    init(db: SQLiteConnection) {
        self.db = db
    }

    /// Opens (creating if needed) the database at `path` and brings its
    /// schema up to date.
    public static func open(path: String) async throws -> Store {
        let db = try await SQLiteConnection.open(storage: .file(path: path))
        let store = Store(db: db)
        do {
            try await store.prepare()
        } catch {
            try? await db.close()
            throw error
        }
        return store
    }

    func prepare() async throws {
        _ = try await db.query("PRAGMA journal_mode = WAL")
        _ = try await db.query("PRAGMA synchronous = NORMAL")
        _ = try await db.query("PRAGMA busy_timeout = 5000")
        _ = try await db.query("PRAGMA foreign_keys = ON")
        let version = try await db.query("PRAGMA user_version").first?.column("user_version")?.integer ?? 0
        for (index, sql) in Self.migrations.enumerated() where index >= version {
            // The migration and its version bump commit together, or not at all.
            try await db.withConnection { conn in
                _ = try await conn.query("BEGIN IMMEDIATE")
                do {
                    for statement in sql.split(separator: ";").map({ $0.trimmingWhitespace() }) where !statement.isEmpty {
                        _ = try await conn.query(String(statement))
                    }
                    _ = try await conn.query("PRAGMA user_version = \(index + 1)")
                    _ = try await conn.query("COMMIT")
                } catch {
                    _ = try? await conn.query("ROLLBACK")
                    throw error
                }
            }
        }
    }

    public func close() async throws {
        try await db.close()
    }

    // MARK: Lists

    /// The list a cookie token names, if the app ever issued it.
    public func list(token: String) async throws -> Int? {
        try await db.query("SELECT id FROM lists WHERE token = ?", [.text(token)])
            .first?.column("id")?.integer
    }

    /// Records a new, empty list under `token` and returns its id.
    public func createList(token: String) async throws -> Int {
        let rows = try await db.query("INSERT INTO lists (token) VALUES (?) RETURNING id", [.text(token)])
        guard let id = rows.first?.column("id")?.integer else { throw StoreError.noRowReturned }
        return id
    }

    // MARK: Todos

    public func todos(list: Int) async throws -> [Todo] {
        try await db.query(
            "SELECT id, title, completed FROM todos WHERE list_id = ? ORDER BY id",
            [.integer(list)]
        ).map { row in
            Todo(
                id: row.column("id")?.integer ?? 0,
                title: row.column("title")?.string ?? "",
                completed: row.column("completed")?.bool ?? false
            )
        }
    }

    public func add(list: Int, title: String) async throws {
        _ = try await db.query("INSERT INTO todos (list_id, title) VALUES (?, ?)", [.integer(list), .text(title)])
    }

    /// Flips one item. False if the list has no such item.
    public func toggle(list: Int, id: Int) async throws -> Bool {
        try await changed(
            "UPDATE todos SET completed = 1 - completed WHERE id = ? AND list_id = ? RETURNING id",
            [.integer(id), .integer(list)]
        )
    }

    /// Renames one item. False if the list has no such item.
    public func rename(list: Int, id: Int, title: String) async throws -> Bool {
        try await changed(
            "UPDATE todos SET title = ? WHERE id = ? AND list_id = ? RETURNING id",
            [.text(title), .integer(id), .integer(list)]
        )
    }

    /// Deletes one item. False if the list has no such item.
    public func destroy(list: Int, id: Int) async throws -> Bool {
        try await changed(
            "DELETE FROM todos WHERE id = ? AND list_id = ? RETURNING id",
            [.integer(id), .integer(list)]
        )
    }

    /// Completes every item, or, if every item already is, reopens them all.
    public func toggleAll(list: Int) async throws {
        _ = try await db.query(
            """
            UPDATE todos
               SET completed = EXISTS (SELECT 1 FROM todos WHERE list_id = ?1 AND completed = 0)
             WHERE list_id = ?1
            """,
            [.integer(list)]
        )
    }

    public func clearCompleted(list: Int) async throws {
        _ = try await db.query("DELETE FROM todos WHERE list_id = ? AND completed = 1", [.integer(list)])
    }

    private func changed(_ sql: String, _ binds: [SQLiteData]) async throws -> Bool {
        try await !db.query(sql, binds).isEmpty
    }
}

enum StoreError: Error {
    case noRowReturned
}

extension Substring {
    func trimmingWhitespace() -> Substring {
        var s = self
        while let c = s.first, c.isWhitespace { s.removeFirst() }
        while let c = s.last, c.isWhitespace { s.removeLast() }
        return s
    }
}