## SQLite storage: one file, WAL mode, every query scoped to one list.
##
## The binding is `db_connector/sqlite3`; the SQLite amalgamation is compiled
## into the binary here, and the build passes `--dynlibOverride:sqlite3` so
## the binding links against it instead of loading `libsqlite3.so`.

import std/[sysrand, strutils, times]
import db_connector/sqlite3

const
  sqliteSource {.strdefine.} = "../.cache/sqlite/sqlite3.c"
  sqliteFlags = "-O2 -DSQLITE_THREADSAFE=0 -DSQLITE_DQS=0 " &
    "-DSQLITE_DEFAULT_MEMSTATUS=0 -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 " &
    "-DSQLITE_OMIT_LOAD_EXTENSION -DSQLITE_OMIT_DEPRECATED " &
    "-DSQLITE_OMIT_SHARED_CACHE -DSQLITE_LIKE_DOESNT_MATCH_BLOBS"

{.compile(sqliteSource, sqliteFlags).}

type
  StoreError* = object of CatchableError

  Todo* = object
    id*: int64
    title*: string
    completed*: bool

  Store* = ref object
    db: PSqlite3
    stmts: seq[PStmt]

  Stmt = enum
    sListExists, sCreateList, sTodos, sAdd, sToggle, sRename, sDestroy,
    sCountAll, sCountCompleted, sSetAll, sClearCompleted

const
  # Migrations only ever add. Append; never edit or remove a shipped entry.
  migrations = [
    """
    CREATE TABLE lists (
      id         TEXT PRIMARY KEY,
      created_at INTEGER NOT NULL
    ) WITHOUT ROWID;
    CREATE TABLE todos (
      id         INTEGER PRIMARY KEY,
      list_id    TEXT NOT NULL REFERENCES lists(id),
      title      TEXT NOT NULL,
      completed  INTEGER NOT NULL DEFAULT 0,
      created_at INTEGER NOT NULL
    );
    CREATE INDEX todos_by_list ON todos(list_id, id);
    """,
  ]

  sql: array[Stmt, string] = [
    sListExists: "SELECT 1 FROM lists WHERE id = ?1",
    sCreateList: "INSERT INTO lists (id, created_at) VALUES (?1, ?2)",
    sTodos: "SELECT id, title, completed FROM todos WHERE list_id = ?1 " &
      "AND (?2 = 0 OR completed = ?3) ORDER BY id",
    sAdd: "INSERT INTO todos (list_id, title, created_at) VALUES (?1, ?2, ?3)",
    sToggle: "UPDATE todos SET completed = 1 - completed " &
      "WHERE list_id = ?1 AND id = ?2",
    sRename: "UPDATE todos SET title = ?3 WHERE list_id = ?1 AND id = ?2",
    sDestroy: "DELETE FROM todos WHERE list_id = ?1 AND id = ?2",
    sCountAll: "SELECT count(*) FROM todos WHERE list_id = ?1",
    sCountCompleted: "SELECT count(*) FROM todos WHERE list_id = ?1 AND completed = 1",
    sSetAll: "UPDATE todos SET completed = ?2 WHERE list_id = ?1",
    sClearCompleted: "DELETE FROM todos WHERE list_id = ?1 AND completed = 1",
  ]

proc check(s: Store, rc: int32): int32 {.discardable.} =
  if rc notin [SQLITE_OK, SQLITE_ROW, SQLITE_DONE]:
    raise newException(StoreError, $errmsg(s.db))
  rc

proc execScript(s: Store, script: string) =
  var err: cstring
  if exec(s.db, script.cstring, nil, nil, err) != SQLITE_OK:
    let msg = $err
    free(err)
    raise newException(StoreError, msg)

proc scalar(s: Store, query: string): int64 =
  var st: PStmt
  s.check prepare_v2(s.db, query.cstring, -1, st, nil)
  try:
    if step(st) == SQLITE_ROW: result = column_int64(st, 0)
  finally:
    discard finalize(st)

proc schemaVersion*(s: Store): int = s.scalar("PRAGMA user_version").int

proc migrate(s: Store) =
  for i in s.schemaVersion ..< migrations.len:
    s.execScript("BEGIN;\n" & migrations[i] & "\nPRAGMA user_version = " &
      $(i + 1) & ";\nCOMMIT;")

proc open*(path: string): Store =
  result = Store()
  if sqlite3.open(path.cstring, result.db) != SQLITE_OK:
    raise newException(StoreError, "cannot open " & path)
  discard busy_timeout(result.db, 5000)
  result.execScript("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; " &
    "PRAGMA foreign_keys = ON;")
  result.migrate()
  result.stmts = newSeq[PStmt](Stmt.high.ord + 1)
  for k in Stmt:
    result.check prepare_v2(result.db, sql[k].cstring, -1, result.stmts[k.ord], nil)

proc close*(s: Store) =
  for st in s.stmts: discard finalize(st)
  s.stmts.setLen(0)
  discard sqlite3.close(s.db)

# Statements are prepared once and reused: bind, step, then reset.

type Arg = object
  case isText: bool
  of true: text: string
  of false: num: int64

proc toArg(v: string): Arg = Arg(isText: true, text: v)
proc toArg(v: int64): Arg = Arg(isText: false, num: v)
proc toArg(v: int): Arg = Arg(isText: false, num: v.int64)
proc toArg(v: bool): Arg = Arg(isText: false, num: ord(v).int64)

proc bound(s: Store, k: Stmt, args: openArray[Arg]): PStmt =
  result = s.stmts[k.ord]
  discard reset(result)
  discard clear_bindings(result)
  for i, a in args:
    let n = int32(i + 1)
    if a.isText:
      s.check bind_text(result, n, a.text.cstring, a.text.len.int32, SQLITE_TRANSIENT)
    else:
      s.check bind_int64(result, n, a.num)

proc exec(s: Store, k: Stmt, args: varargs[Arg, toArg]): int =
  ## Runs a statement that returns no rows; answers the rows it changed.
  let st = s.bound(k, args)
  try:
    s.check step(st)
    result = changes(s.db).int
  finally:
    discard reset(st)

proc count(s: Store, k: Stmt, args: varargs[Arg, toArg]): int =
  let st = s.bound(k, args)
  try:
    if s.check(step(st)) == SQLITE_ROW: result = column_int64(st, 0).int
  finally:
    discard reset(st)

proc now(): int64 = getTime().toUnix()

# Lists

proc newListId*(): string =
  ## 128 random bits from the OS, as 32 hex characters.
  for b in urandom(16): result.add b.toHex(2).toLowerAscii

proc isWellFormedListId*(id: string): bool =
  id.len == 32 and id.allCharsInSet({'0'..'9', 'a'..'f'})

proc listExists*(s: Store, id: string): bool =
  isWellFormedListId(id) and s.count(sListExists, id) == 1

proc createList*(s: Store): string =
  result = newListId()
  discard s.exec(sCreateList, result, now())

# Todos

type Filter* = enum
  fAll = "all", fActive = "active", fCompleted = "completed"

proc todos*(s: Store, list: string, filter = fAll): seq[Todo] =
  let st = s.bound(sTodos, [toArg(list), toArg(filter != fAll),
                            toArg(filter == fCompleted)])
  try:
    while s.check(step(st)) == SQLITE_ROW:
      result.add Todo(id: column_int64(st, 0), title: $column_text(st, 1),
                      completed: column_int64(st, 2) != 0)
  finally:
    discard reset(st)

proc counts*(s: Store, list: string): tuple[total, completed: int] =
  (s.count(sCountAll, list), s.count(sCountCompleted, list))

proc add*(s: Store, list, title: string) =
  discard s.exec(sAdd, list, title, now())

proc toggle*(s: Store, list: string, id: int64): bool =
  ## False when `id` isn't in `list`.
  s.exec(sToggle, list, id) == 1

proc rename*(s: Store, list: string, id: int64, title: string): bool =
  s.exec(sRename, list, id, title) == 1

proc destroy*(s: Store, list: string, id: int64): bool =
  s.exec(sDestroy, list, id) == 1

proc toggleAll*(s: Store, list: string) =
  ## Completes every item, or reopens them all if all were already done.
  let (total, completed) = s.counts(list)
  discard s.exec(sSetAll, list, total == 0 or completed < total)

proc clearCompleted*(s: Store, list: string) =
  discard s.exec(sClearCompleted, list)