/**
 * Targeted SQL parser for drizzle-kit-generated migration statements.
 *
 * Input: one statement extracted from a `.sql` migration file.
 * Output: a classification + the object name, used by the reconciler to decide
 *   whether the statement's effect is already present in the DB.
 *
 * Regex-based intentionally. Our only input is drizzle-kit output, whose
 * surface syntax is narrow and stable. Anything unrecognized falls through
 * as `unknown` and is executed unconditionally by the reconciler (safe for
 * PRAGMA / comments / drizzle internals).
 */

export type StatementClass =
  | { kind: "table"; name: string }
  | { kind: "virtual"; name: string }
  | { kind: "index"; name: string }
  | { kind: "trigger"; name: string }
  | { kind: "column"; table: string; name: string }
  | { kind: "unknown" };

// Identifier: optionally backtick-quoted, dot-free.
const IDENT = String.raw`\`?([A-Za-z_][A-Za-z0-9_]*)\`?`;

// CREATE TABLE [IF NOT EXISTS] <name> ...
const CREATE_TABLE_RE = new RegExp(
  String.raw`^\s*CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?${IDENT}`,
  "i",
);

// CREATE VIRTUAL TABLE [IF NOT EXISTS] <name> USING ...
const CREATE_VIRTUAL_RE = new RegExp(
  String.raw`^\s*CREATE\s+VIRTUAL\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?${IDENT}`,
  "i",
);

// CREATE [UNIQUE] INDEX [IF NOT EXISTS] <name> ON <table> ...
const CREATE_INDEX_RE = new RegExp(
  String.raw`^\s*CREATE\s+(?:UNIQUE\s+)?INDEX\s+(?:IF\s+NOT\s+EXISTS\s+)?${IDENT}\s+ON\s+`,
  "i",
);

// CREATE [TEMP|TEMPORARY] TRIGGER [IF NOT EXISTS] <name> ...
const CREATE_TRIGGER_RE = new RegExp(
  String.raw`^\s*CREATE\s+(?:TEMP\s+|TEMPORARY\s+)?TRIGGER\s+(?:IF\s+NOT\s+EXISTS\s+)?${IDENT}`,
  "i",
);

// ALTER TABLE <table> ADD [COLUMN] <column> ...
const ALTER_ADD_COLUMN_RE = new RegExp(
  String.raw`^\s*ALTER\s+TABLE\s+${IDENT}\s+ADD\s+(?:COLUMN\s+)?${IDENT}`,
  "i",
);

// ALTER TABLE <from> RENAME TO <to>
const ALTER_RENAME_TABLE_RE = new RegExp(
  String.raw`^\s*ALTER\s+TABLE\s+${IDENT}\s+RENAME\s+TO\s+${IDENT}`,
  "i",
);

// CREATE [UNIQUE] INDEX [IF NOT EXISTS] <name> ON <table> ... (captures <table>)
const CREATE_INDEX_TABLE_RE = new RegExp(
  String.raw`^\s*CREATE\s+(?:UNIQUE\s+)?INDEX\s+(?:IF\s+NOT\s+EXISTS\s+)?${IDENT}\s+ON\s+${IDENT}`,
  "i",
);

export function classifyStatement(sql: string): StatementClass {
  // Virtual tables must be checked before generic CREATE TABLE.
  let m = CREATE_VIRTUAL_RE.exec(sql);
  if (m?.[1]) return { kind: "virtual", name: m[1] };

  m = CREATE_TABLE_RE.exec(sql);
  if (m?.[1]) return { kind: "table", name: m[1] };

  m = CREATE_INDEX_RE.exec(sql);
  if (m?.[1]) return { kind: "index", name: m[1] };

  m = CREATE_TRIGGER_RE.exec(sql);
  if (m?.[1]) return { kind: "trigger", name: m[1] };

  m = ALTER_ADD_COLUMN_RE.exec(sql);
  if (m?.[1] && m[2]) return { kind: "column", table: m[1], name: m[2] };

  return { kind: "unknown" };
}

/**
 * The target table of a `CREATE INDEX ... ON <table> (...)` statement, or null
 * if the statement isn't a CREATE INDEX. Used by the reconciler to tell which
 * table an index belongs to (needed to detect indexes on a table that a later
 * migration recreates).
 */
export function parseIndexTargetTable(sql: string): string | null {
  const m = CREATE_INDEX_TABLE_RE.exec(sql);
  return m?.[2] ?? null;
}

// CREATE UNIQUE INDEX [IF NOT EXISTS] <name> ON <table> ( <cols> )
const CREATE_UNIQUE_INDEX_RE = new RegExp(
  String.raw`^\s*CREATE\s+UNIQUE\s+INDEX\s+(?:IF\s+NOT\s+EXISTS\s+)?${IDENT}\s+ON\s+${IDENT}\s*\(([^)]*)\)`,
  "i",
);

/**
 * For a `CREATE UNIQUE INDEX ... ON <table> (<cols>)` statement, return the
 * target table and the bare column names. Null for anything that isn't a
 * unique index. Used by the reconciler to dedup a drifted DB whose data
 * violates a unique index that schema.ts added after the DB was created
 * (e.g. the desktop's evidence_messages had a non-unique index and
 * accumulated duplicates before schema.ts made it unique).
 */
export function parseUniqueIndex(
  sql: string,
): { table: string; columns: string[] } | null {
  const m = CREATE_UNIQUE_INDEX_RE.exec(sql);
  if (!m?.[2]) return null;
  const columns = m[3]
    .split(",")
    .map((c) =>
      c
        .trim()
        .replace(/^`|`$/g, "")
        .replace(/\s+(?:ASC|DESC|COLLATE\s+\w+)\s*$/i, "")
        .trim(),
    )
    .filter((c) => c.length > 0 && /^[A-Za-z_][A-Za-z0-9_]*$/.test(c));
  if (columns.length === 0) return null;
  return { table: m[2], columns };
}

/**
 * Extract `ALTER TABLE <from> RENAME TO <to>` mappings from a migration file.
 *
 * drizzle-kit's SQLite table-recreation pattern (used whenever a column can't
 * be altered in place — a drop, a NOT-NULL change, etc.) builds a
 * `__new_<table>` temp, copies rows over, drops the original, then renames the
 * temp back to `<table>`. The reconciler uses this map to know that a
 * `CREATE TABLE __new_<table>` in an already-applied migration must be verified
 * against the live final table, not the (now-renamed-away) temp name.
 */
export function parseTableRenames(fileContents: string): Map<string, string> {
  const map = new Map<string, string>();
  for (const raw of splitStatements(fileContents)) {
    const m = ALTER_RENAME_TABLE_RE.exec(raw.trim());
    if (m?.[1] && m[2]) map.set(m[1], m[2]);
  }
  return map;
}

/**
 * Split a migration file on drizzle-kit's exact separator.
 * Match drizzle-orm's `readMigrationFiles` behavior bit-for-bit: plain `split`
 * on the literal string, no whitespace normalization.
 */
export function splitStatements(fileContents: string): string[] {
  return fileContents.split("--> statement-breakpoint");
}

/**
 * Count semicolons that terminate top-level statements, ignoring:
 *   - semicolons inside quoted strings ('...', "...", `...`)
 *   - semicolons inside balanced parentheses
 *
 * Used by the reconciler as a safety check: a classified chunk should contain
 * at most one top-level semicolon. A chunk with multiple top-level statements
 * is refused (not skipped, not executed) because the classifier only inspects
 * the prefix — an untrusted trailing statement could ride through.
 */
export function countTopLevelSemicolons(sql: string): number {
  let depth = 0;
  let inString = false;
  let stringChar = "";
  let count = 0;

  for (let i = 0; i < sql.length; i++) {
    const c = sql[i];
    if (inString) {
      if (c === stringChar) {
        // SQL-standard escape: doubling the quote character
        if (sql[i + 1] === stringChar) {
          i++;
          continue;
        }
        inString = false;
      }
      continue;
    }
    if (c === "'" || c === '"' || c === "`") {
      inString = true;
      stringChar = c;
      continue;
    }
    if (c === "(") {
      depth++;
    } else if (c === ")") {
      depth--;
    } else if (c === ";" && depth === 0) {
      count++;
    }
  }

  return count;
}

/**
 * Parse column definitions from a CREATE TABLE statement body.
 *
 * Returns each column's bare name and its full definition (everything between
 * commas at paren-depth 1, trimmed). Skips table-level constraints such as
 * FOREIGN KEY, PRIMARY KEY, UNIQUE, CHECK, and CONSTRAINT that appear at the
 * end of the column list.
 *
 * Returns `[]` for non-CREATE-TABLE input or malformed SQL.
 */
export function parseCreateTableColumns(
  sql: string,
): Array<{ name: string; def: string }> {
  if (!CREATE_TABLE_RE.test(sql)) return [];

  // Find the outer column-list parens by scanning for the first '(' at depth 0
  // (before any paren has opened), then matching its closing ')'.
  let firstOpen = -1;
  let depth = 0;
  let inString = false;
  let stringChar = "";
  let lastClose = -1;

  for (let i = 0; i < sql.length; i++) {
    const c = sql[i];
    if (inString) {
      if (c === stringChar) {
        if (sql[i + 1] === stringChar) {
          i++;
          continue;
        }
        inString = false;
      }
      continue;
    }
    if (c === "'" || c === '"' || c === "`") {
      inString = true;
      stringChar = c;
      continue;
    }
    if (c === "(") {
      if (firstOpen === -1) firstOpen = i;
      depth++;
    } else if (c === ")") {
      depth--;
      if (depth === 0) {
        lastClose = i;
        break;
      }
    }
  }

  if (firstOpen === -1 || lastClose === -1) return [];

  const body = sql.slice(firstOpen + 1, lastClose);

  // Split on commas at depth 0 of the body (respecting nested parens and strings).
  const chunks: string[] = [];
  {
    let start = 0;
    let bodyDepth = 0;
    let bodyInString = false;
    let bodyStringChar = "";
    for (let i = 0; i < body.length; i++) {
      const c = body[i];
      if (bodyInString) {
        if (c === bodyStringChar) {
          if (body[i + 1] === bodyStringChar) {
            i++;
            continue;
          }
          bodyInString = false;
        }
        continue;
      }
      if (c === "'" || c === '"' || c === "`") {
        bodyInString = true;
        bodyStringChar = c;
        continue;
      }
      if (c === "(") {
        bodyDepth++;
      } else if (c === ")") {
        bodyDepth--;
      } else if (c === "," && bodyDepth === 0) {
        chunks.push(body.slice(start, i));
        start = i + 1;
      }
    }
    chunks.push(body.slice(start));
  }

  const CONSTRAINT_RE =
    /^\s*(FOREIGN\s+KEY|PRIMARY\s+KEY|UNIQUE|CHECK|CONSTRAINT)\b/i;
  const COLUMN_NAME_RE = /^\s*`?([A-Za-z_][A-Za-z0-9_]*)`?\s/;

  const columns: Array<{ name: string; def: string }> = [];
  for (const chunk of chunks) {
    if (chunk.trim().length === 0) continue;
    if (CONSTRAINT_RE.test(chunk)) continue;
    const m = COLUMN_NAME_RE.exec(chunk);
    if (!m?.[1]) continue;
    columns.push({ name: m[1], def: chunk });
  }

  return columns;
}