;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(define cave-db #nil)

define open-cave-db()
  define db sq3:db:open(make-main-path("miho-dash-games.sq3"))
  try-finally-ex
    lambda ()
      db[execute: #"
        PRAGMA foreign_keys=ON;
        PRAGMA secure_delete=OFF;
        PRAGMA trusted_schema=ON;
        PRAGMA writable_schema=OFF;
        PRAGMA auto_vacuum=NONE;
        PRAGMA encoding='UTF-8';
        PRAGMA synchronous=OFF;
        PRAGMA journal_mode=WAL;
        "#]
      gset! cave-db db
    lambda (was-err)
      if was-err
        db[close:]

define close-cave-db()
  if {not-null? cave-db}
    cave-db[close:]
  gset! cave-db #nil


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define get-game-bdcff(game-id)
  define stmt cave-db[statement: #"
    SELECT content AS content
    FROM bdcff_files
    WHERE game_id = :game_id
    LIMIT 1
    "#]
  define res ""
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    {res := stmt["content"]}
  stmt[close:]
  res


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define get-game-title-author(game-id)
  define stmt cave-db[statement: #"
    SELECT
        title AS title
      , author AS author
    FROM games
    WHERE game_id = :game_id
    LIMIT 1
    "#]
  define title ""
  define author ""
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    {title := stmt["title"]}
    {author := stmt["author"]}
  stmt[close:]
  cons(title author)


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define get-game-file-name(game-id)
  define stmt cave-db[statement: #"
    SELECT
        games.game_id AS game_id
      , bdcff_files.file_name AS file_name
    FROM games
    LEFT JOIN bdcff_files ON bdcff_files.game_id = games.game_id
    WHERE games.game_id = :game_id
    LIMIT 1
    "#]
  define res ""
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    {res := stmt["file_name"]}
  stmt[close:]
  res


define get-game-story(game-id)
  define stmt cave-db[statement: #"
    SELECT story AS story
    FROM games
    WHERE game_id=:game_id
    LIMIT 1
    "#]
  define story ""
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    {story := stmt["story"]}
  stmt[close:]
  story


define get-game-remark(game-id)
  define stmt cave-db[statement: #"
    SELECT remark AS remark
    FROM games
    WHERE game_id=:game_id
    LIMIT 1
    "#]
  define remark ""
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    {remark := stmt["remark"]}
  stmt[close:]
  remark
