;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; player properties database (including cave stats)
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(define props-db #nil)

define open-props-db()
  if {null? props-db}
    then
      define db sq3:db:open/create(make-main-path("props.sq3"))
      try-finally-ex
        lambda ()
          db[execute: #"
            /* various properties set by the player */
            CREATE TABLE IF NOT EXISTS player_props (
                prop_id INTEGER PRIMARY KEY
              , name TEXT NOT NULL UNIQUE
              , value ANY
            );
            /*
            known props:
            'player-name' (TEXT)
            'selected-game' (INTEGER) -- game_id
            */

            /* caves played, caves finished */
            CREATE TABLE IF NOT EXISTS cave_stats (
                stat_id INTEGER PRIMARY KEY
              , game_id INTEGER
              , cave_id INTEGER
              , cave_index INTEGER NOT NULL
              , level_index INTEGER NOT NULL
              , play_date INTEGER NOT NULL /*DEFAULT unixepoch()*/
              , outcome INTEGER NOT NULL /* -2: skipped; -1: died; 0: aborted; 1: finished */
              , score INTEGER NOT NULL
              , cave_time INTEGER NOT NULL /* seconds */
              , diamonds INTEGER NOT NULL /* diamonds collected */
            );
            CREATE INDEX IF NOT EXISTS cave_stats_game_id ON cave_stats(game_id);
            CREATE INDEX IF NOT EXISTS cave_stats_cave_id ON cave_stats(cave_id);
            CREATE INDEX IF NOT EXISTS cave_stats_game_id_cave_id ON cave_stats(game_id, cave_id);
            CREATE INDEX IF NOT EXISTS cave_stats_game_id_cave_index_stat_id ON cave_stats(game_id, cave_index, stat_id);

            /* cave replays */
            CREATE TABLE IF NOT EXISTS cave_replays (
                replay_id INTEGER PRIMARY KEY
              , stat_id INTEGER /* in "cave_stats" */
              , replay BLOB /* byte vector */
            );
            CREATE INDEX IF NOT EXISTS cave_replays_stat_id ON cave_replays(stat_id);

            /* replay otype translations */
            CREATE TABLE IF NOT EXISTS replay_otypes (
                otype_id INTEGER PRIMARY KEY
              , replay_id INTEGER NOT NULL
              , otype INTEGER NOT NULL
              , enum_name TEXT NOT NULL
            );
            CREATE INDEX IF NOT EXISTS replay_otypes_replay_id ON replay_otypes(replay_id);

            CREATE TABLE IF NOT EXISTS player_remarks_game (
                remark_id INTEGER PRIMARY KEY
              , game_id INTEGER NOT NULL UNIQUE
              , text TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS player_remarks_cave (
                remark_id INTEGER PRIMARY KEY
              , cave_id INTEGER NOT NULL UNIQUE
              , text TEXT NOT NULL
            );
            "#]
          db[execute: #"
            PRAGMA foreign_keys = OFF;
            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! props-db db
        lambda (was-err)
          if was-err
            db[close:]

define close-props-db()
  if {not-null? props-db}
    then
      props-db[execute: "ANALYZE;"]
      props-db[close:]
  gset! props-db #nil


define player-prop-set!(name value)
  assert {{string? name} or {symbol? name}} "invalid player property name"
  define stmt props-db[statement: #"
    INSERT INTO player_props( name, value)
                      VALUES(:name,:value)
    ON CONFLICT(name) DO UPDATE SET value=excluded.value
    "#]
  {stmt[":name"] := name}
  {stmt[":value"] := value}
  stmt[execute:]
  stmt[close:]


define player-prop-ref
  case-lambda
    (name)
      assert {{string? name} or {symbol? name}} "invalid player property name"
      define stmt props-db[statement: #"
        SELECT value AS value
        FROM player_props
        WHERE name = :name
        LIMIT 1
        "#]
      define res
      define gotit #f
      {stmt[":name"] := name}
      while stmt[step:]
        {gotit := #t}
        {res := stmt["value"]}
      stmt[close:]
      if {not gotit}
        then
          if {symbol? name} {name := symbol->string(name)}
          error string-append("cannot find player property \"" name "\"")
      res
    (name default)
      assert {{string? name} or {symbol? name}} "invalid player property name"
      define stmt props-db[statement: #"
        SELECT value AS value
        FROM player_props
        WHERE name = :name
        LIMIT 1
        "#]
      define res default
      {stmt[":name"] := name}
      while stmt[step:]
        {res := stmt["value"]}
      stmt[close:]
      res

define player-prop-ref-bool
  case-lambda
    (name)
      {player-prop-ref(name) <> 0}
    (name default)
      if {boolean? default}
        {default := (if default 1 0)}
      {player-prop-ref(name default) <> 0}


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; cave stats and replay recording
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
(define recording-cave-game-id #f)
(define recording-cave-cave-id #f)
(define recording-cave-stat-id #f)

define update-cave-credits(game-id cave-index level-index)
  if {fixnum? game-id}
    then
      define stmt cave-db[statement: #"
        SELECT title AS title
        FROM caves
        WHERE game_id=:game_id
          AND cave_index=:cave_index
          AND level_index=:level_index
        LIMIT 1
        "#]
      {stmt[":game_id"] := game-id}
      {stmt[":cave_index"] := cave-index}
      {stmt[":level_index"] := level-index}
      while stmt[step:]
        gset! game:cave-name stmt["title"]
      stmt[close:]


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; analyze and save replay
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; vector with frame changes.
;; encoding: idx new-value
;; special commands (idx):
;;  -1: end of frame; next 2 values: C64 PPRNG state at the end of the frame
;;  -2: end of cave (finished)
;;  -3: end of cave (died)
;;  -4: end of cave (aborted)
;;  -5: end of cave (skipped)
;;  -8: player coords (x y) -- used for camera panning
;; -16: magic wall active on this frame
;; -17: amoeba active on this frame
;; -32: diamond collected
;; -33: score change (arg: delta)
;; -42: exit opened
;; -43: was explosion
;; -44: slime pprng was inited (no args)
;; -46: play sound (arg: sndid)
;;      sndid:
;;       1: GD_S_EXPLOSION
;;       2: GD_S_GHOST_EXPLOSION
;;       3: GD_S_BOMB_EXPLOSION
;;       4: GD_S_VOODOO_EXPLOSION
;; -60: play object sound (arg: otype)
;; -61: play object walk sound (arg: otype)
;; -62: play object push sound (arg: otype)
;; -63: play object fall sound (arg: otype)
;; -69: player hatched
;; first frame contains the whole initial map
;;  -666: initial frame(always first)
;;     args: width, height, field
define save-replay(replay)
  define replay-pos 0
  define replay-used-otypes make-vector()
  ;;
  define replay-convert-otype(otype)
    define idx abs(otype)
    if {idx >= vector-length(replay-used-otypes)}
        vector-resize! replay-used-otypes {idx + 1}
    ;printf "len=%o\n" vector-length(replay-used-otypes)
    if {null? replay-used-otypes[idx]}
      {replay-used-otypes[idx] := idx}
    otype
  ;;
  define replay-finished?()
    {{bytevector? replay} and {replay-pos >= bytevector-length(replay)}}
  ;;
  define get-replay-word()
    if {replay-pos = bytevector-length(replay)}
      -4
      else
        define n {replay[replay-pos] + {replay[{replay-pos + 1}] * 256}}
        inc! replay-pos 2
        if {n > 32767}
          dec! n 65536
        n
  ;;
  define process-replay-step()
    if {not replay-finished?()}
      then
        define re get-replay-word()
        define vv
        cond
          {re = -1} ; end of frame; next 2 values: C64 PPRNG state at the end of the frame
            define v0 get-replay-word()
            define v1 get-replay-word()
            ;printf "END-OF-FRAME: pprng: %02X %02X\n" v0 v1
            process-replay-step()
          {re = -2} ; end of cave (finished)
            ;printf "CAVE FINISHED\n"
            process-replay-step()
          {re = -3} ; end of cave (died)
            ;printf "PLAYER DEAD\n"
            process-replay-step()
          {re = -4} ; end of cave (aborted)
            ;printf "CAVE ABORTED\n"
            process-replay-step()
          {re = -5} ; end of cave (skipped)
            ;printf "CAVE SKIPPED\n"
            process-replay-step()
          {re = -8} ; player coords (x y) -- used for camera panning
            define px get-replay-word()
            define py get-replay-word()
            ;printf "PLAYER POS: %o, %o\n" px py
            process-replay-step()
          {re = -16} ; magic wall active on this frame
            ;printf "MAGIC WALL ACTIVE\n"
            process-replay-step()
          {re = -17} ; amoeba active on this frame
            ;printf "AMOEBA WALL ACTIVE\n"
            process-replay-step()
          {re = -32} ; diamond collected
            ;printf "DIAMOND COLLECTED\n"
            process-replay-step()
          {re = -33} ; score change (arg: delta)
            {vv := get-replay-word()}
            ;printf "ADD SCORE: %o\n" vv
            process-replay-step()
          {re = -42} ; exit opened
            ;printf "GATE OPENED\n"
            process-replay-step()
          {re = -43} ; was explosion
            ;printf "WAS EXPLOSION\n"
            process-replay-step()
          {re = -44} ; slime pprng was inited (no args)
            ;printf "SLIME PPRNG INITED\n"
            process-replay-step()
          {re = -46} ; play sound (arg: sndid)
            {vv := get-replay-word()}
            ;printf "PLAY SOUND WITH ID: %o\n" vv
            process-replay-step()
          {re = -60} ; play object sound (arg: otype)
            {vv := replay-convert-otype(get-replay-word())}
            ;printf "PLAY OBJECT SOUND: %o\n" vv
            process-replay-step()
          {re = -61} ; play object walk sound (arg: otype)
            {vv := replay-convert-otype(get-replay-word())}
            ;printf "PLAY OBJECT WALK SOUND: %o\n" vv
            process-replay-step()
          {re = -62} ; play object push sound (arg: otype)
            {vv := replay-convert-otype(get-replay-word())}
            ;printf "PLAY OBJECT PUSH SOUND: %o\n" vv
            process-replay-step()
          {re = -63} ; play object fall sound (arg: otype)
            {vv := replay-convert-otype(get-replay-word())}
            ;printf "PLAY OBJECT FALL SOUND: %o\n" vv
            process-replay-step()
          {re = -69} ; player hatched
            ;printf "PLAYER HATCHED\n"
            process-replay-step()
          else ; idx otype
            ;assert between?(re 0 {{field-width * field-height} - 1})
            define idx re
            define otype replay-convert-otype(get-replay-word())
            process-replay-step()
  ;;
  define analyze-replay-data()
    {replay-used-otypes := make-vector()}
    {replay-pos := 0}
    ;; setup field
    assert {get-replay-word() = -666} "invalid replay start"
    define field-width get-replay-word()
    define field-height get-replay-word()
    assert between?(field-width 2 128)
    assert between?(field-height 2 128)
    define x
    define y
    define otype
    iterate
      init {y := 0}
      repeat {y <> field-height}
        iterate
          init {x := 0}
          repeat {x <> field-width}
            {otype := replay-convert-otype(get-replay-word())}
            assert between?(otype 0 {::etype:_MAX_VALUE_ - 1})
            ;{field[field-index-at(x y)] := otype}
            inc! x
          else #void
        inc! y
      else #void
    ;;
    define rr get-replay-word()
    if {rr = -8}
      then
        get-replay-word()
        get-replay-word()
        {rr := get-replay-word()}
    assert {rr = -1} "invalid replay start"
    get-replay-word() ; car-set! game-pprng get-replay-word()
    get-replay-word() ; cdr-set! game-pprng get-replay-word()
    process-replay-step()
  ;;
  define write-otypes(replay-id)
    analyze-replay-data()
    define stmt props-db[statement: #"
      INSERT INTO replay_otypes ( replay_id, otype, enum_name)
                          VALUES(:replay_id,:otype,:enum_name)
      "#]
    define idx 0
    while {idx <> vector-length(replay-used-otypes)}
      if {not-null? replay-used-otypes[idx]}
        then
          {stmt[":replay_id"] := replay-id}
          {stmt[":otype"] := replay-used-otypes[idx]}
          {stmt[":enum_name"] := otype-name(replay-used-otypes[idx])}
          stmt[execute:]
      inc! idx
    stmt[close:]
  ;;
  printf "saving replay...\n"
  props-db[execute: "BEGIN TRANSACTION"]
  ;; it is ok to save several replays for one stat-id.
  ;; it means that the player used quickload.
  define stmt props-db[statement: #"
    INSERT INTO cave_replays ( stat_id, replay)
                       VALUES(:stat_id,:replay)
    RETURNING replay_id AS replay_id
    "#]
  define rid #nil
  {stmt[":stat_id"] := recording-cave-stat-id}
  {stmt[":replay"] := replay}
  while stmt[step:]
    {rid := stmt["replay_id"]}
  assert {{fixnum? rid} and {positive? rid}}
  stmt[close:]
  write-otypes rid
  props-db[execute: "COMMIT TRANSACTION"]


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; main stats saving API
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define reset-cave-record()
  gset! recording-cave-game-id #f
  gset! recording-cave-cave-id #f
  gset! recording-cave-stat-id #f


define record-cave-start(game-id cave-index level-index)
  reset-cave-record()
  if {fixnum? game-id}
    then
      define stmt cave-db[statement: #"
        SELECT cave_id AS cave_id
        FROM caves
        WHERE game_id=:game_id
          AND cave_index=:cave_index
          AND level_index=:level_index
        LIMIT 1
        "#]
      define cave-id #f
      {stmt[":game_id"] := game-id}
      {stmt[":cave_index"] := cave-index}
      {stmt[":level_index"] := level-index}
      while stmt[step:]
        {cave-id := stmt["cave_id"]}
      stmt[close:]
      if {fixnum? cave-id}
        then
          ;; create the stats record
          define stmt-ins props-db[statement: #"
            INSERT INTO cave_stats
                   ( game_id, cave_id, cave_index, level_index,  play_date, outcome, score, cave_time, diamonds)
             VALUES(:game_id,:cave_id,:cave_index,:level_index,unixepoch(),:outcome,:score,:cave_time,:diamonds)
            RETURNING stat_id AS stat_id
          "#]
          {stmt-ins[":game_id"] := game-id}
          {stmt-ins[":cave_id"] := cave-id}
          {stmt-ins[":cave_index"] := cave-index}
          {stmt-ins[":level_index"] := level-index}
          {stmt-ins[":outcome"] := -2} ; skipped
          {stmt-ins[":score"] := 0}
          {stmt-ins[":cave_time"] := 0}
          {stmt-ins[":diamonds"] := 0}
          define stat-id #f
          while stmt-ins[step:]
            {stat-id := stmt-ins["stat_id"]}
          stmt-ins[close:]
          if {fixnum? stat-id}
            then
              gset! recording-cave-game-id game-id
              gset! recording-cave-cave-id cave-id
              gset! recording-cave-stat-id stat-id


;; internal
define create-new-cave-stats()
  if {fixnum? recording-cave-game-id}
    then
      assert {fixnum? recording-cave-cave-id}
      assert {fixnum? recording-cave-stat-id}
      define stmt-cl cave-db[statement: #"
        SELECT
            cave_index AS cave_index
          , level_index AS level_index
        FROM cave_stats
        WHERE stat_id=:stat_id
        LIMIT 1
        "#]
      define cave-index #f
      define level-index #f
      {stmt-cl[":stat_id"] := recording-cave-stat-id}
      while stmt-cl[step:]
        {cave-index := stmt-cl["cave_index"]}
        {level-index := stmt-cl["level_index"]}
      stmt-cl[close:]
      assert {fixnum? cave-index}
      assert {fixnum? level-index}
      record-cave-start recording-cave-game-id cave-index level-index


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define do-remove-reset-cave-stats()
  if {fixnum? recording-cave-stat-id}
    then
      printf "removed cave stats.\n"
      define stmt props-db[statement: #"
        DELETE FROM cave_stats
        WHERE stat_id=:stat_id
      "#]
      {stmt[":stat_id"] := recording-cave-stat-id}
      stmt[execute:]
      stmt[close:]
      reset-cave-record()
      (game:wipe-replay)

define do-record-cave-stats(outcome)
  if {fixnum? recording-cave-stat-id}
    then
      define record game:get-recorded-data()
      if {{outcome = 1} and {bytevector? record} and
          {positive? bytevector-length(record)}}
        then
          printf "replay size: %,d bytes.\n" bytevector-length(record)
          save-replay(record)
        else
          game:report-replay-size()
      define stmt props-db[statement: #"
        UPDATE cave_stats
        SET   outcome=:outcome
            , score=:score
            , cave_time=:cave_time
            , diamonds=:diamonds
        WHERE stat_id=:stat_id
      "#]
      {stmt[":stat_id"] := recording-cave-stat-id}
      {stmt[":outcome"] := outcome}
      {stmt[":score"] := game:cave-score}
      {stmt[":cave_time"] := game:cave-total-time}
      {stmt[":diamonds"] := game:diamond-count}
      stmt[execute:]
      stmt[close:]
      ;; on quickload, create new stat
      if {outcome = -5}
        then
          create-new-cave-stats()
          printf "saved cave quickload (id=%o).\n" recording-cave-stat-id
        else
          printf "saved cave stats (id=%o).\n" recording-cave-stat-id
      ;; do not reset cave record state, for quickloads
      ;if {{outcome = 1} or {outcome = 0} or {outcome = -2}}
      ;  reset-cave-record()


;; do not reset cave record state, for quickloads
(define (record-cave-skipped) (game:replay-put-word -5) (do-record-cave-stats -2))
(define (record-cave-finished) (game:replay-put-word -2) (do-record-cave-stats 1))
(define (record-cave-cancelled) (game:replay-put-word -4) (do-record-cave-stats 0))
(define (record-cave-cheated) (game:replay-put-word -5) (do-record-cave-stats -2))
(define (record-cave-dead) (game:replay-put-word -3) (do-record-cave-stats -1))
(define (record-cave-quickload) (do-record-cave-stats -5))


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; database utilities
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define get-game-cave-list(game-id)
  define stmt cave-db[statement: #"
    SELECT caves AS cave_count
    FROM games
    WHERE game_id=:game_id
    LIMIT 1
    "#]
  define cave-count 0
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    {cave-count := stmt["cave_count"]}
  stmt[close:]
  assert {positive? cave-count} "wtf?!"
  ;;
  ;define cave-list make-vector(cave-count #nil)
  define cave-list make-vector()
  set! stmt cave-db[statement: #"
    SELECT
        cave_id AS cave_id
      , cave_index AS cave_index
      , title AS title
      , story AS story
      , remark AS remark
    FROM caves
    WHERE game_id=:game_id
      AND level_index=0
    ORDER BY cave_index
    "#]
  define index -1
  define title
  define info
  ;printf "game-id=%o\n" game-id
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    ;printf "num=%o\n" stmt["cave_index"]
    inc! index
    assert {index = stmt["cave_index"]} string-append("missing cave #" number->string(index))
    {info := cons(cons('cave-id stmt["cave_id"]) #nil)}
    {title := stmt["title"]}
    if {empty-string? title}
      {title := string-append("Cave #" number->string({index + 1}))}
    {info := cons(cons('title stmt["title"]) info)}
    if {not empty-string?(stmt["story"])}
      {info := cons(cons('story stmt["story"]) info)}
    if {not empty-string?(stmt["remark"])}
      {info := cons(cons('remark stmt["remark"]) info)}
    vector-push! cave-list info
  stmt[close:]
  ;;
  cave-list


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; loading replay from the database
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define get-cave-replay-id(stat-id)
  ;; there could be several replays; take the last one
  define stmt props-db[statement: #"
    SELECT replay_id AS replay_id
    FROM cave_replays
    WHERE stat_id=:stat_id
    ORDER BY replay_id
    "#]
  define res -1
  {stmt[":stat_id"] := stat-id}
  while stmt[step:]
    {res := stmt["replay_id"]}
  stmt[close:]
  res

define get-cave-replay-data(replay-id)
  define stmt props-db[statement: #"
    SELECT replay AS replay
    FROM cave_replays
    WHERE replay_id=:replay_id
    LIMIT 1
    "#]
  define res #nil
  {stmt[":replay_id"] := replay-id}
  while stmt[step:]
    {res := stmt["replay"]}
  stmt[close:]
  res

define get-cave-replay-otypes(replay-id)
  define otrans make-vector()
  define stmt props-db[statement: #"
    SELECT
        otype AS otype
      , enum_name AS enum_name
    FROM replay_otypes
    WHERE replay_id=:replay_id
    "#]
  define ot-old
  define ot-new
  {stmt[":replay_id"] := replay-id}
  while stmt[step:]
    {ot-old := stmt["otype"]}
    {ot-new := enum:find-name(::etype string->symbol(stmt["enum_name"]))}
    if {false? ot-new}
      error string-append("unknown replay otype: \"" stmt["enum_name"] "\"")
    {ot-new := cdr(ot-new)}
    assert {{fixnum? ot-new} and between?(ot-new 0 etype:_MAX_VALUE_)}
    if {ot-old >= vector-length(otrans)}
      vector-resize! otrans {ot-old + 1}
    {otrans[ot-old] := ot-new}
  stmt[close:]
  otrans


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; update loaded BDCFF with database properties
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define update-bdcff-credits(game-id)
  define stmt cave-db[statement: #"
    SELECT
        title AS title
      , author AS author
      , date AS date
      , description AS description
      , story AS story
      , difficulty AS difficulty
      , remark AS remark
    FROM games
    WHERE games.game_id = :game_id
    LIMIT 1
    "#]
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    bdcff-loader:game-title-set! stmt["title"]
    bdcff-loader:game-author-set! stmt["author"]
    bdcff-loader:game-date-set! stmt["date"]
    bdcff-loader:game-description-set! stmt["description"]
    bdcff-loader:game-story-set! stmt["story"]
    bdcff-loader:game-remark-set! stmt["remark"]
  stmt[close:]


;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
;; game remark utilities
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
define get-game-remarks(game-id)
  define stmt props-db[statement: #"
    SELECT text AS text
    FROM player_remarks_game
    WHERE game_id=:game_id
    LIMIT 1
  "#]
  define res ""
  {stmt[":game_id"] := game-id}
  while stmt[step:]
    {res := stmt["text"]}
  stmt[close:]
  res


define set-game-remarks(game-id text)
  assert {{null? text} or {string? text}} "invalid game remark text"
  define stmt
  if {{null? text} or empty-string?(text)}
    then
      {stmt := props-db[statement: #"
        DELETE FROM player_remarks_game
        WHERE game_id=:game_id
        "#]}
    else
      {stmt := props-db[statement: #"
        INSERT INTO player_remarks_game( game_id, text)
                                 VALUES(:game_id,:text)
        ON CONFLICT(game_id) DO UPDATE SET text=excluded.text
        "#]}
      {stmt[":text"] := text}
  {stmt[":game_id"] := game-id}
  stmt[execute:]
  stmt[close:]
