//! The todo store: one SQLite file in WAL mode, one connection shared by
//! every request behind a mutex. A todo app's queries take microseconds,
//! so a single serialised connection is simpler than a pool and plenty fast.

const std = @import("std");
const c = @import("sqlite");
const Io = std.Io;
const Allocator = std.mem.Allocator;

pub const Error = error{Sqlite};

pub const Todo = struct {
    id: i64,
    title: []const u8,
    completed: bool,
};

/// Migrations only ever add. `PRAGMA user_version` records how many have run.
const migrations = [_][:0]const u8{
    \\CREATE TABLE lists (
    \\  id INTEGER PRIMARY KEY,
    \\  token_hash 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
    \\);
    \\CREATE INDEX todos_by_list ON todos(list_id, id);
    ,
};

pub const Store = struct {
    db: *c.sqlite3,
    mutex: Io.Mutex = .init,
    io: Io,

    pub fn open(io: Io, path: [:0]const u8) Error!Store {
        var db: ?*c.sqlite3 = null;
        const flags = c.SQLITE_OPEN_READWRITE | c.SQLITE_OPEN_CREATE | c.SQLITE_OPEN_FULLMUTEX;
        if (c.sqlite3_open_v2(path.ptr, &db, flags, null) != c.SQLITE_OK) {
            if (db) |d| {
                std.log.err("sqlite open {s}: {s}", .{ path, c.sqlite3_errmsg(d) });
                _ = c.sqlite3_close(d);
            }
            return error.Sqlite;
        }
        var store: Store = .{ .db = db.?, .io = io };
        errdefer store.close();
        _ = c.sqlite3_busy_timeout(store.db, 5000);
        try store.exec("PRAGMA journal_mode = WAL");
        try store.exec("PRAGMA synchronous = NORMAL");
        try store.exec("PRAGMA foreign_keys = ON");
        try store.migrate();
        return store;
    }

    pub fn close(s: *Store) void {
        _ = c.sqlite3_close(s.db);
    }

    fn migrate(s: *Store) Error!void {
        var st = try s.prepare("PRAGMA user_version");
        const current: usize = if (try st.step()) @intCast(st.int(0)) else 0;
        st.finalize();
        for (migrations[@min(current, migrations.len)..], current..) |sql, i| {
            try s.exec("BEGIN");
            errdefer s.exec("ROLLBACK") catch {};
            try s.exec(sql);
            var buf: [64]u8 = undefined;
            const set = std.fmt.bufPrintSentinel(&buf, "PRAGMA user_version = {d}", .{i + 1}, 0) catch unreachable;
            try s.exec(set);
            try s.exec("COMMIT");
        }
    }

    fn exec(s: *Store, sql: [:0]const u8) Error!void {
        var err: [*c]u8 = null;
        if (c.sqlite3_exec(s.db, sql.ptr, null, null, &err) != c.SQLITE_OK) {
            std.log.err("sqlite: {s}", .{err});
            c.sqlite3_free(err);
            return error.Sqlite;
        }
    }

    fn prepare(s: *Store, sql: [:0]const u8) Error!Stmt {
        var stmt: ?*c.sqlite3_stmt = null;
        if (c.sqlite3_prepare_v2(s.db, sql.ptr, @intCast(sql.len + 1), &stmt, null) != c.SQLITE_OK) {
            std.log.err("sqlite prepare: {s}", .{c.sqlite3_errmsg(s.db)});
            return error.Sqlite;
        }
        return .{ .stmt = stmt.?, .db = s.db };
    }

    /// Runs one statement that returns no rows; answers how many rows it changed.
    fn run(s: *Store, sql: [:0]const u8, args: anytype) Error!usize {
        var st = try s.prepare(sql);
        defer st.finalize();
        try st.bindAll(args);
        while (try st.step()) {}
        return @intCast(c.sqlite3_changes(s.db));
    }

    fn lock(s: *Store) void {
        s.mutex.lockUncancelable(s.io);
    }

    fn unlock(s: *Store) void {
        s.mutex.unlock(s.io);
    }

    // The queries the app runs. Every one that touches a todo is scoped
    // by list, so one visitor can never reach another's items.

    pub fn findList(s: *Store, token_hash: []const u8) Error!?i64 {
        s.lock();
        defer s.unlock();
        var st = try s.prepare("SELECT id FROM lists WHERE token_hash = ?");
        defer st.finalize();
        try st.bindAll(.{token_hash});
        return if (try st.step()) st.int(0) else null;
    }

    pub fn createList(s: *Store, token_hash: []const u8) Error!i64 {
        s.lock();
        defer s.unlock();
        _ = try s.run("INSERT INTO lists (token_hash) VALUES (?)", .{token_hash});
        return c.sqlite3_last_insert_rowid(s.db);
    }

    /// Every todo in the list, oldest first, copied into `arena`.
    pub fn todos(s: *Store, arena: Allocator, list: i64) (Error || Allocator.Error)![]Todo {
        s.lock();
        defer s.unlock();
        var st = try s.prepare("SELECT id, title, completed FROM todos WHERE list_id = ? ORDER BY id");
        defer st.finalize();
        try st.bindAll(.{list});
        var out: std.ArrayList(Todo) = .empty;
        while (try st.step()) {
            try out.append(arena, .{
                .id = st.int(0),
                .title = try arena.dupe(u8, st.text(1)),
                .completed = st.int(2) != 0,
            });
        }
        return out.items;
    }

    pub fn add(s: *Store, list: i64, title: []const u8) Error!void {
        s.lock();
        defer s.unlock();
        _ = try s.run("INSERT INTO todos (list_id, title) VALUES (?, ?)", .{ list, title });
    }

    /// Each of these answers false when `id` isn't in `list`.
    pub fn toggle(s: *Store, list: i64, id: i64) Error!bool {
        s.lock();
        defer s.unlock();
        return try s.run("UPDATE todos SET completed = 1 - completed WHERE id = ? AND list_id = ?", .{ id, list }) > 0;
    }

    pub fn rename(s: *Store, list: i64, id: i64, title: []const u8) Error!bool {
        s.lock();
        defer s.unlock();
        return try s.run("UPDATE todos SET title = ? WHERE id = ? AND list_id = ?", .{ title, id, list }) > 0;
    }

    pub fn destroy(s: *Store, list: i64, id: i64) Error!bool {
        s.lock();
        defer s.unlock();
        return try s.run("DELETE FROM todos WHERE id = ? AND list_id = ?", .{ id, list }) > 0;
    }

    /// Completes every item, or, if all already were, makes them all active.
    pub fn toggleAll(s: *Store, list: i64) Error!void {
        s.lock();
        defer s.unlock();
        _ = try s.run(
            \\UPDATE todos SET completed =
            \\  EXISTS (SELECT 1 FROM todos WHERE list_id = ?1 AND completed = 0)
            \\WHERE list_id = ?1
        , .{list});
    }

    pub fn clearCompleted(s: *Store, list: i64) Error!void {
        s.lock();
        defer s.unlock();
        _ = try s.run("DELETE FROM todos WHERE list_id = ? AND completed = 1", .{list});
    }
};

const Stmt = struct {
    stmt: *c.sqlite3_stmt,
    db: *c.sqlite3,

    fn finalize(st: *Stmt) void {
        _ = c.sqlite3_finalize(st.stmt);
    }

    /// Binds `args` in order. Text is bound without copying, so it must
    /// outlive the statement, which every caller here guarantees.
    fn bindAll(st: *Stmt, args: anytype) Error!void {
        inline for (args, 1..) |arg, i| {
            const rc = switch (@TypeOf(arg)) {
                i64 => c.sqlite3_bind_int64(st.stmt, i, arg),
                []const u8, []u8 => c.sqlite3_bind_text(st.stmt, i, arg.ptr, @intCast(arg.len), null),
                else => @compileError("can't bind " ++ @typeName(@TypeOf(arg))),
            };
            if (rc != c.SQLITE_OK) return error.Sqlite;
        }
    }

    fn step(st: *Stmt) Error!bool {
        return switch (c.sqlite3_step(st.stmt)) {
            c.SQLITE_ROW => true,
            c.SQLITE_DONE => false,
            else => {
                std.log.err("sqlite step: {s}", .{c.sqlite3_errmsg(st.db)});
                return error.Sqlite;
            },
        };
    }

    fn int(st: *Stmt, col: c_int) i64 {
        return c.sqlite3_column_int64(st.stmt, col);
    }

    fn text(st: *Stmt, col: c_int) []const u8 {
        const ptr = c.sqlite3_column_text(st.stmt, col) orelse return "";
        return ptr[0..@intCast(c.sqlite3_column_bytes(st.stmt, col))];
    }
};