// A deliberately small online-migration vocabulary. Unknown SQL needs an explicit safety design, not an opt-out.
// Production, 2026-10-02: a strong lock, a whole-table backfill and a plain index build inside one migration
// transaction blocked public reads and writes for two minutes.
export const FIRST_ONLINE_MIGRATION = 55;
export type MigrationPlan = { kind: "transaction" | "validation" | "statistics" } | { kind: "index"; index: string; table: string };

// Keep quoted values opaque, including their semicolons and comment markers. SQL bodies are not in
// the accepted vocabulary; rejecting them also prevents hiding writes in DO or function definitions.
function statements(text: string): string[][] {
  const result: string[][] = [];
  let tokens: string[] = [];
  for (let i = 0; i < text.length;) {
    const rest = text.slice(i);
    const whitespace = /^\s+/.exec(rest);
    if (whitespace) { i += whitespace[0].length; continue; }
    if (rest.startsWith("--")) { const end = text.indexOf("\n", i); i = end < 0 ? text.length : end; continue; }
    if (rest.startsWith("/*")) {
      let depth = 1;
      i += 2;
      while (depth && i < text.length) {
        if (text.startsWith("/*", i)) { depth++; i += 2; }
        else if (text.startsWith("*/", i)) { depth--; i += 2; }
        else i++;
      }
      if (depth) throw new Error("unterminated SQL comment");
      continue;
    }
    const quote = text[i];
    if (quote === "'" || quote === '"') {
      const start = i++;
      let closed = false;
      while (i < text.length) {
        if (text[i++] !== quote) continue;
        if (text[i] === quote) { i++; continue; }
        closed = true;
        break;
      }
      if (!closed) throw new Error("unterminated SQL quote");
      tokens.push(text.slice(start, i));
      continue;
    }
    if (/^\$(?:[a-z_][a-z0-9_]*)?\$/i.test(rest)) throw new Error("SQL bodies are not online migrations");
    if (quote === ";") { if (tokens.length) result.push(tokens); tokens = []; i++; continue; }
    const token = /^[a-z_][a-z0-9_$]*|^-?\d+(?:\.\d+)?|^::/i.exec(rest)?.[0] ?? quote;
    tokens.push(token);
    i += token.length;
  }
  if (tokens.length) result.push(tokens);
  return result;
}

const IDENT = '(?:[a-z_][a-z0-9_$]*|"(?:[^"]|"")+")';
const RELATION = `${IDENT}(?: \\. ${IDENT})?`;
const TYPE = '(?:text|boolean|smallint|int|integer|bigint|real|double precision|numeric(?: \\( \\d+(?: , \\d+)? \\))?|timestamp(?: \\( \\d+ \\))?(?: with(?:out)? time zone)?|timestamptz|date|uuid|jsonb?|bytea)(?: \\[ \\])*';
const LITERAL = `(?:'(?:[^']|'')*'|-?\\d+(?:\\.\\d+)?|true|false|null)(?: :: ${TYPE})?`;
const addColumn = new RegExp(`^alter table ${RELATION} add column (?:if not exists )?${IDENT} ${TYPE}(?: not null)?(?: default ${LITERAL})?(?: not null)?$`, "i");
const alterDefault = new RegExp(`^alter table ${RELATION} alter column ${IDENT} (?:set default ${LITERAL}|drop default)$`, "i");
const indexStart = new RegExp(`^create (unique )?index concurrently if not exists (${IDENT}) on (${RELATION}) `, "i");

function singleAction(tokens: string[]) {
  let depth = 0;
  for (const token of tokens) {
    if (token === "(") depth++;
    if (token === ")") depth--;
    if (token === "," && depth === 0) return false;
  }
  return depth === 0;
}

export function migrationPlan(text: string): MigrationPlan {
  const all = statements(text);
  if (all.length !== 1) throw new Error("use one statement per online migration so strong table locks cannot accumulate");
  const tokens = all[0];
  const sql = tokens.join(" ");
  const words = tokens.filter((token) => /^[a-z_]/i.test(token)).join(" ");
  if (/^create (?:unique )?index\b/i.test(sql)) {
    const match = indexStart.exec(sql);
    if (!match) throw new Error("indexes require CREATE INDEX CONCURRENTLY IF NOT EXISTS");
    return { kind: "index", index: match[2], table: match[3].replace(/ \. /g, ".") };
  }
  if (new RegExp(`^alter table ${RELATION} validate constraint ${IDENT}$`, "i").test(sql)) return { kind: "validation" };
  // These take SHARE UPDATE EXCLUSIVE, which permits ordinary reads and writes. Keep ANALYZE
  // scoped to named columns of one table; SKIP_LOCKED could silently leave its statistics unbuilt.
  if (new RegExp(`^create statistics ${RELATION} \\( mcv \\) on ${IDENT}(?: , ${IDENT}){1,7} from ${RELATION}$`, "i").test(sql)
    || new RegExp(`^analyze ${RELATION} \\( ${IDENT}(?: , ${IDENT})* \\)$`, "i").test(sql)) return { kind: "statistics" };
  if (new RegExp(`^create table (?:if not exists )?${RELATION} \\(.*\\)$`, "i").test(sql)
    && !/\b(as|like|inherits|partition)\b/i.test(words)) return { kind: "transaction" };
  // Dropping a table, column or constraint that no code uses changes the catalog only: no scan or rewrite,
  // and the runner bounds the wait for its lock. One object and no CASCADE, so an object that still depends
  // on it fails the migration instead of disappearing with it. Whether its data may go is settled before
  // the change, and the release running while it applies no longer reads or writes it.
  if (new RegExp(`^drop table if exists ${RELATION}$`, "i").test(sql)) return { kind: "transaction" };
  if (!singleAction(tokens)) throw new Error("use one ALTER TABLE action per statement");
  // Only these per-table vacuum triggers: catalog changes under SHARE UPDATE EXCLUSIVE, without
  // disabling maintenance, changing its resource limits or broadening the storage-parameter syntax.
  const vacuum = new RegExp(`^alter table ${RELATION} set \\( (.*) \\)$`, "i").exec(sql);
  if (vacuum) {
    const parameters = vacuum[1].split(" , ").map((part) => /^(autovacuum_vacuum(?:_insert)?_scale_factor) = (\d+(?:\.\d+)?)$/i.exec(part));
    if (parameters.every((parameter) => parameter && Number(parameter[2]) > 0 && Number(parameter[2]) <= 1)
      && new Set(parameters.map((parameter) => parameter![1].toLowerCase())).size === parameters.length) return { kind: "transaction" };
  }
  if (new RegExp(`^alter table ${RELATION} drop (?:column|constraint) if exists ${IDENT}$`, "i").test(sql)) return { kind: "transaction" };
  if (addColumn.test(sql)) {
    if (/\bnot null\b/i.test(words) && (!/\bdefault\b/i.test(words) || /\bdefault null\b/i.test(words))) throw new Error("NOT NULL on a new column requires a non-null constant default");
    return { kind: "transaction" };
  }
  if (alterDefault.test(sql)) return { kind: "transaction" };
  if (new RegExp(`^alter table ${RELATION} add constraint ${IDENT} (?:check \\(.*\\)|foreign key \\(.*\\) references .*) not valid$`, "i").test(sql)) return { kind: "transaction" };
  throw new Error(`not safe for an online migration: ${sql.slice(0, 180)}. Use constant-default columns, NOT VALID then separate validation, concurrent indexes, column MCV statistics with separate column ANALYZE, bounded per-table vacuum scale factors, or DROP TABLE IF EXISTS / DROP COLUMN IF EXISTS / DROP CONSTRAINT IF EXISTS for one object nothing uses; backfill data in bounded batches outside release migrations.`);
}