(* The SQL schema migrations, as data. This module holds no logic. It holds only
   the ordered list of migrations and the ledger table that records which ones a
   database file already carries. {!Migrations} reads this and applies it.

   Keeping the SQL here, apart from the apply logic, means a schema change edits
   one place and never touches OCaml control flow.

   Seven incremental migrations first grew this schema. A reset of the deployed
   database consolidated them into a single initial migration. No file was left
   to migrate, so the drop-and-recreate churn of the old chain served no
   purpose. Add the next schema change as version 2. *)

type t = { version : int; name : string; statements : string list }
(** One migration: a version, a human name, and the ordered statements it runs.
*)

(* The ledger of applied migrations. A row per version, so a reconnect knows
   what the file already carries and skips it. *)
let ledger =
  {|CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY,
      name TEXT NOT NULL,
      applied_at REAL NOT NULL
    )|}

let migrations =
  [
    {
      version = 1;
      name = "initial schema";
      statements =
        [
          {|CREATE TABLE IF NOT EXISTS trainee (
              id TEXT PRIMARY KEY,
              username TEXT NOT NULL UNIQUE,
              credential TEXT NOT NULL
            )|};
          {|CREATE TABLE IF NOT EXISTS active_routine (
              trainee_id TEXT PRIMARY KEY,
              routine_id TEXT NOT NULL
            )|};
          {|CREATE TABLE IF NOT EXISTS in_progress (
              trainee_id TEXT PRIMARY KEY,
              encoded TEXT NOT NULL
            )|};
          {|CREATE TABLE IF NOT EXISTS workout (
              id TEXT PRIMARY KEY,
              trainee_id TEXT NOT NULL,
              seq INTEGER NOT NULL,
              encoded TEXT NOT NULL
            )|};
          {|CREATE INDEX IF NOT EXISTS workout_by_trainee
              ON workout (trainee_id, seq DESC)|};
          (* Retained from when Dream's sql_sessions back end stored sessions
             here. Sessions now live in a signed cookie. Kept so an existing
             file keeps its rows; otherwise unused. *)
          {|CREATE TABLE IF NOT EXISTS dream_session (
              id TEXT PRIMARY KEY,
              label TEXT NOT NULL,
              expires_at REAL NOT NULL,
              payload TEXT NOT NULL
            )|};
          {|CREATE TABLE IF NOT EXISTS feedback (
              trainee_id TEXT NOT NULL,
              seq INTEGER NOT NULL,
              encoded TEXT NOT NULL,
              PRIMARY KEY (trainee_id, seq)
            )|};
          {|CREATE INDEX IF NOT EXISTS feedback_by_trainee
              ON feedback (trainee_id, seq DESC)|};
          {|CREATE TABLE IF NOT EXISTS app_feedback (
              trainee_id TEXT NOT NULL,
              seq INTEGER NOT NULL,
              submitted_at TEXT NOT NULL,
              message TEXT NOT NULL,
              PRIMARY KEY (trainee_id, seq)
            )|};
          {|CREATE INDEX IF NOT EXISTS app_feedback_by_trainee
              ON app_feedback (trainee_id, seq DESC)|};
          {|CREATE TABLE IF NOT EXISTS app_feedback_vote (
              feedback_trainee_id TEXT NOT NULL,
              feedback_seq INTEGER NOT NULL,
              voter_id TEXT NOT NULL,
              PRIMARY KEY (feedback_trainee_id, feedback_seq, voter_id)
            )|};
          {|CREATE INDEX IF NOT EXISTS app_feedback_vote_by_voter
              ON app_feedback_vote (voter_id)|};
          (* An import batch is provisional: it holds the raw export source and
             the cleaning done so far, encoded as one snapshot blob. A promoted
             record is canonical evidence with an independent lifecycle, so it
             carries no foreign key to its batch. Deleting the batch must never
             cascade to the record. The batch's [imported_at] is a column, so
             listing can order without decoding every blob. A record is keyed by
             (trainee, workout) so a re-promotion replaces it. *)
          {|CREATE TABLE IF NOT EXISTS import_batch (
              trainee_id TEXT NOT NULL,
              batch_id TEXT NOT NULL,
              imported_at TEXT NOT NULL,
              source TEXT NOT NULL,
              fingerprint TEXT NOT NULL,
              snapshot TEXT NOT NULL,
              PRIMARY KEY (trainee_id, batch_id)
            )|};
          {|CREATE INDEX IF NOT EXISTS import_batch_by_trainee
              ON import_batch (trainee_id, imported_at DESC, batch_id DESC)|};
          {|CREATE TABLE IF NOT EXISTS imported_record (
              trainee_id TEXT NOT NULL,
              workout_id TEXT NOT NULL,
              batch_id TEXT NOT NULL,
              revision INTEGER NOT NULL,
              title TEXT NOT NULL,
              started_at TEXT NOT NULL,
              ended_at TEXT NOT NULL,
              snapshot TEXT NOT NULL,
              PRIMARY KEY (trainee_id, workout_id)
            )|};
          {|CREATE INDEX IF NOT EXISTS imported_record_by_trainee
              ON imported_record (trainee_id, ended_at DESC)|};
        ];
    };
  ]