package kvstore

const sqlSchema = `
BEGIN IMMEDIATE;

CREATE TABLE IF NOT EXISTS data(
  SeqNum  INTEGER NOT NULL PRIMARY KEY,
  Deleted INTEGER NOT NULL DEFAULT 0,
  Data    BLOB NOT NULL
) WITHOUT ROWID;

CREATE INDEX IF NOT EXISTS data_deleted_index ON data(Deleted,SeqNum);

CREATE TABLE IF NOT EXISTS log(
  SeqNum     INTEGER NOT NULL PRIMARY KEY,
  CreatedAt  INTEGER NOT NULL,
  Collection TEXT    NOT NULL,
  ID         INTEGER NOT NULL,
  Store      INTEGER NOT NULL
) WITHOUT ROWID;

CREATE INDEX IF NOT EXISTS log_created_at_index ON log(CreatedAt);

CREATE TABLE IF NOT EXISTS kv(
  Collection TEXT    NOT NULL,
  ID         INTEGER NOT NULL,
  SeqNum     INTEGER NOT NULL,
  PRIMARY KEY (Collection, ID)
) WITHOUT ROWID;

CREATE VIEW IF NOT EXISTS kvdata AS
SELECT
  kv.Collection,
  kv.ID,
  data.Data
FROM kv
JOIN data ON kv.SeqNum=data.SeqNum;

CREATE VIEW IF NOT EXISTS logdata AS
SELECT
  log.SeqNum,
  log.Collection,
  log.ID,
  log.Store,
  data.data
FROM log
LEFT JOIN data on log.SeqNum=data.SeqNum;

COMMIT;`

// ----------------------------------------------------------------------------

const sqlInsertData = `INSERT INTO data(SeqNum,Data) VALUES(?,?)`

const sqlInsertKV = `INSERT INTO kv(Collection,ID,SeqNum) VALUES (?,?,?)
ON CONFLICT(Collection,ID) DO UPDATE SET SeqNum=excluded.SeqNum
WHERE ID=excluded.ID`

// ----------------------------------------------------------------------------

const sqlDeleteKV = `DELETE FROM kv WHERE Collection=? AND ID=?`

const sqlDeleteData = `UPDATE data SET Deleted=1
WHERE SeqNum=(
  SELECT SeqNum FROM kv WHERE Collection=? AND ID=?)`

// ----------------------------------------------------------------------------

const sqlInsertLog = `INSERT INTO log(SeqNum,CreatedAt,Collection,ID,Store)
  VALUES(?,?,?,?,?)`

// ----------------------------------------------------------------------------

const sqlKVIterate = `SELECT ID,Data FROM kvdata WHERE Collection=?`

const sqlLogIterate = `
SELECT SeqNum,Collection,ID,Store,Data
FROM logdata
WHERE SeqNum > ?
ORDER BY SeqNum ASC`

const sqlMaxSeqNumGet = `SELECT COALESCE(MAX(SeqNum),0) FROM log`

const sqlCleanQuery = `
DELETE FROM
  log
WHERE
  CreatedAt < ? AND
  SeqNum < (SELECT MAX(SeqNum) FROM log;

DELETE FROM
  data
WHERE
  Deleted != 0 AND
  SeqNum < (SELECT MIN(SeqNum) FROM log;`