import type { Db } from './driver.js';

interface Migration {
  version: number;
  name: string;
  up: string;
}

/**
 * Forward-only migrations for one workspace's database. Each runs once, inside
 * a transaction, recorded in `_wellformed_migration`. Never edit a migration that
 * has shipped — add another.
 *
 * That rule is why version 1 still creates `_wellformed_user` and `_wellformed_session`
 * only for version 2 to drop them. Accounts and sessions moved to the control
 * database when Wellformed became multi-tenant, and an install that predates the
 * move needs its users read out of the old tables before they go — see
 * `db/adopt.ts`. A fresh workspace pays two redundant CREATEs for that.
 */
const MIGRATIONS: Migration[] = [
  {
    version: 1,
    name: 'core metadata',
    up: `
      CREATE TABLE _wellformed_table (
        name            TEXT PRIMARY KEY,
        label           TEXT NOT NULL,
        display_column  TEXT NOT NULL,
        position        INTEGER NOT NULL DEFAULT 0,
        created_at      TEXT NOT NULL DEFAULT (datetime('now'))
      );

      CREATE TABLE _wellformed_column (
        table_name   TEXT NOT NULL REFERENCES _wellformed_table(name) ON DELETE CASCADE,
        name         TEXT NOT NULL,
        type         TEXT NOT NULL,
        position     INTEGER NOT NULL DEFAULT 0,
        options      TEXT,
        target_table TEXT,
        expr         TEXT,
        format       TEXT,
        required     INTEGER NOT NULL DEFAULT 0,
        PRIMARY KEY (table_name, name)
      );

      CREATE TABLE _wellformed_view (
        id         TEXT PRIMARY KEY,
        table_name TEXT NOT NULL REFERENCES _wellformed_table(name) ON DELETE CASCADE,
        name       TEXT NOT NULL,
        filters    TEXT NOT NULL DEFAULT '[]',
        sort       TEXT,
        position   INTEGER NOT NULL DEFAULT 0,
        system     INTEGER NOT NULL DEFAULT 0
      );
      CREATE INDEX idx_view_table ON _wellformed_view(table_name, position);

      CREATE TABLE _wellformed_rule (
        id           TEXT PRIMARY KEY,
        table_name   TEXT NOT NULL REFERENCES _wellformed_table(name) ON DELETE CASCADE,
        column_name  TEXT NOT NULL,
        equals       TEXT NOT NULL,
        label        TEXT NOT NULL,
        actions      TEXT NOT NULL DEFAULT '[]',
        enabled      INTEGER NOT NULL DEFAULT 1
      );
      CREATE INDEX idx_rule_table ON _wellformed_rule(table_name, column_name);

      CREATE TABLE _wellformed_user (
        id            TEXT PRIMARY KEY,
        email         TEXT NOT NULL UNIQUE,
        name          TEXT NOT NULL,
        role          TEXT NOT NULL DEFAULT 'viewer',
        password_hash TEXT NOT NULL,
        disabled      INTEGER NOT NULL DEFAULT 0,
        created_at    TEXT NOT NULL DEFAULT (datetime('now')),
        last_seen_at  TEXT
      );

      CREATE TABLE _wellformed_session (
        token_hash TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL REFERENCES _wellformed_user(id) ON DELETE CASCADE,
        created_at TEXT NOT NULL DEFAULT (datetime('now')),
        expires_at TEXT NOT NULL,
        user_agent TEXT
      );
      CREATE INDEX idx_session_user ON _wellformed_session(user_id);

      CREATE TABLE _wellformed_audit (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        ts          TEXT NOT NULL DEFAULT (datetime('now')),
        user_id     TEXT,
        who         TEXT NOT NULL,
        kind        TEXT NOT NULL,
        table_name  TEXT,
        row_id      TEXT,
        row_label   TEXT,
        column_name TEXT,
        old_value   TEXT,
        new_value   TEXT
      );
      CREATE INDEX idx_audit_recent ON _wellformed_audit(id DESC);
      CREATE INDEX idx_audit_table ON _wellformed_audit(table_name, id DESC);
      CREATE INDEX idx_audit_row ON _wellformed_audit(table_name, row_id, id DESC);
    `,
  },
  {
    version: 2,
    name: 'identity moves to the control plane',
    up: `
      DROP TABLE IF EXISTS _wellformed_session;
      DROP TABLE IF EXISTS _wellformed_user;
    `,
  },
  {
    version: 3,
    name: 'audit records which agent a change came through',
    up: `
      /*
       * The label of the agent key a change arrived on, or null for a person in
       * the browser. It sits beside 'who' rather than replacing it: an
       * assistant acts as its holder, with their role, on a key they made — so
       * the account stays accountable and this is the disclosure.
       *
       * Null on every row written before this, which is the honest reading of
       * "there were no agents yet".
       */
      ALTER TABLE _wellformed_audit ADD COLUMN via TEXT;
    `,
  },
  {
    version: 4,
    name: 'views carry an aggregate row',
    up: `
      /*
       * Column name → aggregate function, for the row pinned under the grid.
       * It belongs to the view for the same reason the filters do: the total
       * of "Unpaid" is a different number from the total of everything, and
       * whoever saved the view meant the one they were looking at.
       *
       * '{}' on every view that predates this, which is "no summaries" — the
       * footer stays empty until somebody asks for something.
       */
      ALTER TABLE _wellformed_view ADD COLUMN summaries TEXT NOT NULL DEFAULT '{}';
    `,
  },
  {
    version: 5,
    name: 'columns can be unique',
    up: `
      /*
       * Whether a column refuses a value another row already holds.
       *
       * Only the catalogue's record of it lives here; the constraint itself is
       * a real UNIQUE INDEX on the user table, named "_uq_<table>_<column>",
       * so it is SQLite refusing the duplicate rather than the server
       * remembering to look. Spelled 'uniq' because UNIQUE is a keyword.
       *
       * 0 everywhere it lands, which is the truth about every column that
       * existed before there was a way to say otherwise.
       */
      ALTER TABLE _wellformed_column ADD COLUMN uniq INTEGER NOT NULL DEFAULT 0;
    `,
  },
  {
    version: 6,
    name: 'clear formats that format nothing',
    up: `
      /*
       * The display format is now only accepted where the column's type leaves
       * the rendering open — see \`formatChoices\` in shared. This clears the
       * ones already stored that it would not accept, because a column holding
       * a format the server refuses is a column that cannot be saved.
       *
       * Both cases are known and both are invisible: 'date' has no case in the
       * formatter at all, so a rollup carrying it (the CRM template seeded one)
       * always rendered exactly as it will now; and a number format on a text,
       * select, checkbox or link column had nothing to apply to, since those
       * four render one way whatever is set here.
       */
      UPDATE _wellformed_column
         SET format = NULL
       WHERE (format = 'date' AND type <> 'date')
          OR (type IN ('text', 'select', 'checkbox', 'link')
              AND format IN ('currency', 'number', 'percent'));
    `,
  },
  {
    version: 7,
    name: 'text columns can number themselves',
    up: `
      /*
       * The numbering pattern a text column fills itself with when a row
       * arrives with the cell empty — 'INV-{yyyy}-{nnn}' — or NULL for the
       * columns that just hold text. Only the pattern lives here: the counter
       * is read off the rows already in the column, scoped to the pattern's
       * rendered date parts, so there is no stored counter to drift out of
       * step with the data and a pattern carrying {yyyy} restarts each year
       * by itself. See sequence.ts in shared.
       */
      ALTER TABLE _wellformed_column ADD COLUMN sequence TEXT;
    `,
  },
  {
    version: 8,
    name: 'views can leave columns out',
    up: `
      /*
       * The columns a view does not show, as a JSON array of names. Part of
       * the view for the same reason its filters are: the view's SELECT list
       * really narrows — the grid, the SQL echo and the wire all carry only
       * what the view shows — and whoever saved the view meant the columns
       * they were looking at.
       *
       * '[]' on every view that predates this, which is "show everything".
       */
      ALTER TABLE _wellformed_view ADD COLUMN hidden TEXT NOT NULL DEFAULT '[]';
    `,
  },
  {
    version: 9,
    name: 'tables can be shelved under a heading',
    up: `
      /*
       * The heading a table is listed under in the sidebar, or NULL for none.
       * A way of shelving the list and nothing more: tables keep their flat
       * SQL names, formulas and links cross groups freely — which is what
       * separates this from a Postgres schema. Spelled group_name because
       * GROUP is a keyword.
       */
      ALTER TABLE _wellformed_table ADD COLUMN group_name TEXT;
    `,
  },
  {
    version: 10,
    name: 'columns wear a label',
    up: `
      /*
       * What people call the column, alongside the SQL identifier they never
       * chose. Tables have carried this split since they existed; columns
       * catch up here. From now on the label is the only thing anyone types —
       * the identifier is derived from it at creation and fixed after.
       *
       * Existing columns take their identifier as their label, underscores
       * read as the spaces they stood in for: "Unit_price" becomes
       * "Unit price". Nothing else is rewritten — the identifiers stay
       * exactly what every stored formula, filter and view says they are.
       */
      ALTER TABLE _wellformed_column ADD COLUMN label TEXT;
      UPDATE _wellformed_column SET label = replace(name, '_', ' ');
    `,
  },
];

export function migrate(db: Db): void {
  db.sqlite.exec(`
    CREATE TABLE IF NOT EXISTS _wellformed_migration (
      version    INTEGER PRIMARY KEY,
      name       TEXT NOT NULL,
      applied_at TEXT NOT NULL DEFAULT (datetime('now'))
    );
  `);

  const applied = new Set(
    db.sqlite
      .prepare<[], { version: number }>('SELECT version FROM _wellformed_migration')
      .all()
      .map((r) => r.version),
  );

  const record = db.sqlite.prepare('INSERT INTO _wellformed_migration (version, name) VALUES (?, ?)');

  for (const m of MIGRATIONS) {
    if (applied.has(m.version)) continue;
    const run = db.sqlite.transaction(() => {
      db.sqlite.exec(m.up);
      record.run(m.version, m.name);
    });
    run();
  }
}

export const LATEST_MIGRATION = MIGRATIONS[MIGRATIONS.length - 1]!.version;