(* The SQLite store: one file, WAL mode, every todo scoped to a list. *)

type todo = { id : int; title : string; completed : bool }
type t = { db : Sqlite3.db; stmts : (string, Sqlite3.stmt) Hashtbl.t }

(* Each entry runs once, in order, and is recorded in user_version.
   Migrations only ever add: never edit or remove an entry. *)
let migrations =
  [
    {|CREATE TABLE lists (
        id TEXT PRIMARY KEY,
        created_at INTEGER NOT NULL DEFAULT (unixepoch())
      ) STRICT;
      CREATE TABLE todos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        list_id TEXT NOT NULL REFERENCES lists (id),
        title TEXT NOT NULL,
        completed INTEGER NOT NULL DEFAULT 0,
        created_at INTEGER NOT NULL DEFAULT (unixepoch())
      ) STRICT;
      CREATE INDEX todos_by_list ON todos (list_id, id);|};
  ]

let exec db sql =
  match Sqlite3.exec db sql with
  | Sqlite3.Rc.OK -> ()
  | rc -> failwith (Printf.sprintf "sqlite: %s: %s" (Sqlite3.Rc.to_string rc) (Sqlite3.errmsg db))

let user_version db =
  let v = ref 0 in
  ignore (Sqlite3.exec_no_headers db "PRAGMA user_version" ~cb:(fun row ->
      v := Option.fold ~none:0 ~some:int_of_string row.(0)));
  !v

let migrate db =
  List.iteri
    (fun i sql ->
      if i >= user_version db then begin
        exec db "BEGIN IMMEDIATE";
        exec db sql;
        exec db (Printf.sprintf "PRAGMA user_version = %d" (i + 1));
        exec db "COMMIT"
      end)
    migrations

let open_ path =
  let db = Sqlite3.db_open path in
  exec db "PRAGMA journal_mode = WAL";
  exec db "PRAGMA synchronous = NORMAL";
  exec db "PRAGMA busy_timeout = 5000";
  exec db "PRAGMA foreign_keys = ON";
  migrate db;
  { db; stmts = Hashtbl.create 16 }

let close t =
  Hashtbl.iter (fun _ s -> ignore (Sqlite3.finalize s)) t.stmts;
  Hashtbl.reset t.stmts;
  ignore (Sqlite3.db_close t.db)

(* Prepares [sql] once, binds [params], and calls [row] for each result. *)
let query t sql params ~row =
  let stmt =
    match Hashtbl.find_opt t.stmts sql with
    | Some s -> s
    | None ->
        let s = Sqlite3.prepare t.db sql in
        Hashtbl.add t.stmts sql s;
        s
  in
  Fun.protect ~finally:(fun () -> ignore (Sqlite3.reset stmt)) @@ fun () ->
  Sqlite3.Rc.check (Sqlite3.bind_values stmt params);
  let rec loop acc =
    match Sqlite3.step stmt with
    | Sqlite3.Rc.ROW -> loop (row stmt :: acc)
    | Sqlite3.Rc.DONE -> List.rev acc
    | rc -> failwith (Printf.sprintf "sqlite: %s: %s" (Sqlite3.Rc.to_string rc) (Sqlite3.errmsg t.db))
  in
  loop []

(* Runs a statement and answers whether it changed any row. *)
let change t sql params =
  ignore (query t sql params ~row:ignore);
  Sqlite3.changes t.db > 0

let text s = Sqlite3.Data.TEXT s
let int i = Sqlite3.Data.INT (Int64.of_int i)

let list_exists t id = query t "SELECT 1 FROM lists WHERE id = ?" [ text id ] ~row:ignore <> []
let create_list t id = ignore (change t "INSERT INTO lists (id) VALUES (?)" [ text id ])

let todos t ~list =
  query t "SELECT id, title, completed FROM todos WHERE list_id = ? ORDER BY id" [ text list ]
    ~row:(fun s ->
      { id = Sqlite3.column_int s 0; title = Sqlite3.column_text s 1; completed = Sqlite3.column_bool s 2 })

let add t ~list title =
  ignore (change t "INSERT INTO todos (list_id, title) VALUES (?, ?)" [ text list; text title ])

let toggle t ~list id =
  change t "UPDATE todos SET completed = NOT completed WHERE id = ? AND list_id = ?" [ int id; text list ]

let rename t ~list id title =
  change t "UPDATE todos SET title = ? WHERE id = ? AND list_id = ?" [ text title; int id; text list ]

let delete t ~list id = change t "DELETE FROM todos WHERE id = ? AND list_id = ?" [ int id; text list ]

(* Completes every item, or reopens them all if every one already was. *)
let toggle_all t ~list =
  ignore
    (change t
       {|UPDATE todos SET completed =
           EXISTS (SELECT 1 FROM todos WHERE list_id = ?1 AND NOT completed)
         WHERE list_id = ?1|}
       [ text list ])

let clear_completed t ~list =
  ignore (change t "DELETE FROM todos WHERE list_id = ? AND completed" [ text list ])