/**
 * sql-objects.ts — Carry non-EF-tracked SQL objects (functions, views, stored
 * procedures, triggers) from a project's `**\/SqlObjects\/**\/*.sql` files INTO
 * the generated EF Core migrations as FROZEN literals.
 *
 * WHY frozen literals — and NOT a runtime `SqlObjectHelper.ApplyAll(migrationBuilder)`
 * call inside the migration:
 *   - A migration that reads the embedded .sql at run time is NON-DETERMINISTIC:
 *     its effect depends on the assembly built later, breaking EF's "a migration
 *     is an immutable historical record" guarantee.
 *   - It breaks ordering: an early migration would apply TODAY's SQL, which may
 *     reference a table created by a LATER migration → `database update` on a
 *     fresh DB fails with "Invalid object name".
 *   A literal frozen at creation time is deterministic, self-contained, and
 *   correctly ordered (it only ever references tables that already exist at the
 *   migration that carries it).
 *
 * The .sql files stay the single source of truth; each migration carries a
 * snapshot; the app's startup re-apply (idempotent CREATE OR ALTER) remains the
 * runtime safety net. `database update` alone now deploys the objects, and a
 * squash can no longer silently drop them.
 *
 * Detection is by glob (`**\/SqlObjects\/**\/*.sql`) under the assembly's own
 * project dir — NO hardcoded project/assembly name, so it survives a client
 * renaming the Infrastructure project.
 *
 * Everything here is PURE text manipulation (no dotnet, no DB) except the
 * clearly-marked I/O helpers at the bottom — so the logic is unit-tested
 * without a toolchain.
 */
import path from 'node:path';
import { findFiles, readText, writeText } from './fs.js';

export type SqlObjectKind = 'FUNCTION' | 'VIEW' | 'PROCEDURE' | 'TRIGGER';

export interface SqlObjectHeader {
  kind: SqlObjectKind;
  schema: string | null;
  name: string;
}

export interface SqlObject {
  /** Stable identity for change detection: lowercased "schema.name". */
  key: string;
  header: SqlObjectHeader;
  /** Raw SQL as authored (single source of truth). */
  sql: string;
  /** Path relative to the project dir — deterministic ordering + messages. */
  relPath: string;
}

/** Up marker carries the key so change-detection can find the last inlined copy. */
const MARKER = '[smartstack:sqlobject]';
const MARKER_DROP = '[smartstack:sqlobject:drop]';
/** 12-space indent = inside an EF migration method body (extra indent is harmless). */
const IND = '            ';

// ---------------------------------------------------------------------------
// Pure helpers
// ---------------------------------------------------------------------------

/**
 * Extract `{kind, schema, name}` from the first `CREATE [OR ALTER] <KIND> …`
 * statement. Brackets/quotes around identifiers are optional. Returns null when
 * the file has no recognizable programmable-object header.
 */
export function parseSqlObjectHeader(sqlText: string): SqlObjectHeader | null {
  const m = sqlText.match(
    /CREATE\s+(?:OR\s+ALTER\s+)?(FUNCTION|VIEW|PROCEDURE|PROC|TRIGGER)\s+(?:(\[?[A-Za-z0-9_]+\]?)\s*\.\s*)?(\[?[A-Za-z0-9_]+\]?)/i,
  );
  if (!m) return null;
  const kindRaw = m[1].toUpperCase();
  const kind = (kindRaw === 'PROC' ? 'PROCEDURE' : kindRaw) as SqlObjectKind;
  const strip = (s: string | undefined): string | null =>
    s ? s.replace(/^\[/, '').replace(/\]$/, '') : null;
  return { kind, schema: strip(m[2]), name: strip(m[3]) as string };
}

export function objectKey(h: SqlObjectHeader): string {
  return `${(h.schema ?? '').toLowerCase()}.${h.name.toLowerCase()}`;
}

function bracket(id: string): string {
  return `[${id}]`;
}

export function qualifiedName(h: SqlObjectHeader): string {
  return h.schema ? `${bracket(h.schema)}.${bracket(h.name)}` : bracket(h.name);
}

export function buildDropStatement(h: SqlObjectHeader): string {
  return `DROP ${h.kind} IF EXISTS ${qualifiedName(h)};`;
}

/** C# verbatim string literal: only `"` needs escaping (doubled). CRLF→LF. */
export function toVerbatimLiteral(sql: string): string {
  const body = sql.replace(/\r\n/g, '\n').replace(/"/g, '""').replace(/\s+$/g, '');
  return `@"${body}"`;
}

export function normalizeSql(sql: string): string {
  return sql
    .replace(/\r\n/g, '\n')
    .split('\n')
    .map((l) => l.replace(/\s+$/g, ''))
    .join('\n')
    .trim();
}

function labelFor(h: SqlObjectHeader): string {
  return `${h.schema ?? ''}.${h.name}`;
}

export function renderUpBlock(obj: SqlObject, builder: string): string {
  return (
    `${IND}// ${MARKER} ${obj.key} (${obj.relPath})\n` +
    `${IND}${builder}.Sql(${toVerbatimLiteral(obj.sql)});`
  );
}

export function renderDownBlock(obj: SqlObject, builder: string): string {
  return (
    `${IND}// ${MARKER_DROP} ${obj.key}\n` +
    `${IND}${builder}.Sql("${buildDropStatement(obj.header)}");`
  );
}

/**
 * Index of the `}` matching the `{` at `openIndex`, skipping C# strings,
 * verbatim strings, char literals and comments — so a `"{}"` default value or a
 * brace inside a `@"…"` SQL literal never throws the depth count off.
 */
export function matchBrace(source: string, openIndex: number): number {
  let depth = 0;
  let i = openIndex;
  const n = source.length;
  while (i < n) {
    const c = source[i];
    const c2 = source[i + 1];

    if (c === '/' && c2 === '/') {
      const nl = source.indexOf('\n', i);
      if (nl === -1) return -1;
      i = nl + 1;
      continue;
    }
    if (c === '/' && c2 === '*') {
      const end = source.indexOf('*/', i + 2);
      if (end === -1) return -1;
      i = end + 2;
      continue;
    }
    if (c === '@' && c2 === '"') {
      i += 2;
      while (i < n) {
        if (source[i] === '"') {
          if (source[i + 1] === '"') {
            i += 2;
            continue;
          }
          i += 1;
          break;
        }
        i += 1;
      }
      continue;
    }
    if (c === '"') {
      i += 1;
      while (i < n) {
        if (source[i] === '\\') {
          i += 2;
          continue;
        }
        if (source[i] === '"') {
          i += 1;
          break;
        }
        i += 1;
      }
      continue;
    }
    if (c === "'") {
      i += 1;
      while (i < n) {
        if (source[i] === '\\') {
          i += 2;
          continue;
        }
        if (source[i] === "'") {
          i += 1;
          break;
        }
        i += 1;
      }
      continue;
    }
    if (c === '{') depth += 1;
    else if (c === '}') {
      depth -= 1;
      if (depth === 0) return i;
    }
    i += 1;
  }
  return -1;
}

function findMethodOpenBrace(
  source: string,
  methodName: 'Up' | 'Down',
): { open: number; builder: string } | null {
  const re = new RegExp(`void\\s+${methodName}\\s*\\(\\s*MigrationBuilder\\s+(\\w+)\\s*\\)`);
  const m = re.exec(source);
  if (!m) return null;
  const open = source.indexOf('{', m.index + m[0].length);
  if (open === -1) return null;
  return { open, builder: m[1] };
}

/**
 * Insert the frozen SQL-object calls at the END of `Up()` (after the tables they
 * depend on are created) and the matching DROPs at the START of `Down()` (before
 * EF drops those tables). Returns the source unchanged when `Up()` can't be
 * located or `objs` is empty.
 */
export function injectSqlObjects(source: string, objs: SqlObject[]): string {
  if (objs.length === 0) return source;
  const up = findMethodOpenBrace(source, 'Up');
  if (!up) return source;
  const upClose = matchBrace(source, up.open);
  if (upClose === -1) return source;

  const edits: Array<{ at: number; text: string }> = [];

  const upBlock =
    `\n\n${IND}// --- SmartStack SQL objects (frozen literal — not tracked by the EF model) ---\n` +
    objs.map((o) => renderUpBlock(o, up.builder)).join('\n') +
    `\n`;
  edits.push({ at: upClose, text: upBlock });

  const down = findMethodOpenBrace(source, 'Down');
  if (down) {
    const downBlock =
      `\n${IND}// --- SmartStack SQL objects — drop before EF drops their tables ---\n` +
      objs.map((o) => renderDownBlock(o, down.builder)).join('\n') +
      `\n`;
    edits.push({ at: down.open + 1, text: downBlock });
  }

  // Apply from the highest index downward so earlier offsets stay valid.
  edits.sort((a, b) => b.at - a.at);
  let result = source;
  for (const e of edits) {
    result = result.slice(0, e.at) + e.text + result.slice(e.at);
  }
  return result;
}

/** Map of key → normalized SQL for every SQL object inlined in a migration. */
export function extractInlinedSql(migrationText: string): Map<string, string> {
  const map = new Map<string, string>();
  const re = /\/\/\s*\[smartstack:sqlobject\]\s+(\S+)[^\n]*\n\s*\w+\.Sql\s*\(/g;
  let m: RegExpExecArray | null;
  while ((m = re.exec(migrationText))) {
    const key = m[1].toLowerCase();
    const parenIdx = m.index + m[0].length - 1;
    const at = migrationText.indexOf('@"', parenIdx);
    if (at === -1) continue;
    const lit = readVerbatim(migrationText, at);
    if (lit) map.set(key, normalizeSql(lit.value));
  }
  return map;
}

function readVerbatim(s: string, atQuote: number): { value: string; end: number } | null {
  let i = atQuote + 2;
  let out = '';
  const n = s.length;
  while (i < n) {
    if (s[i] === '"') {
      if (s[i + 1] === '"') {
        out += '"';
        i += 2;
        continue;
      }
      return { value: out, end: i + 1 };
    }
    out += s[i];
    i += 1;
  }
  return null;
}

/**
 * The SQL objects whose CURRENT definition differs from (or is absent in) the
 * most recent inlined copy across the migration history — i.e. what the new
 * migration must carry. `existingMigrationTextsOldestFirst` MUST be ordered
 * oldest→newest so the latest copy wins.
 */
export function computeChangedObjects(
  current: SqlObject[],
  existingMigrationTextsOldestFirst: string[],
): SqlObject[] {
  const lastInlined = new Map<string, string>();
  for (const text of existingMigrationTextsOldestFirst) {
    for (const [k, v] of extractInlinedSql(text)) lastInlined.set(k, v);
  }
  return current.filter((o) => {
    const prev = lastInlined.get(o.key);
    return prev === undefined || prev !== normalizeSql(o.sql);
  });
}

// ---------------------------------------------------------------------------
// I/O helpers (thin wrappers around fs — everything above is pure)
// ---------------------------------------------------------------------------

/** Read every SQL object under `<projectDir>\/**\/SqlObjects\/**\/*.sql`. */
export async function collectSqlObjects(
  projectDir: string,
): Promise<{ objects: SqlObject[]; unparsable: string[] }> {
  const files = (await findFiles('**/SqlObjects/**/*.sql', { cwd: projectDir })).sort((a, b) =>
    a.localeCompare(b),
  );
  const objects: SqlObject[] = [];
  const unparsable: string[] = [];
  for (const abs of files) {
    const sql = await readText(abs);
    const header = parseSqlObjectHeader(sql);
    const relPath = path.relative(projectDir, abs).replace(/\\/g, '/');
    if (!header) {
      unparsable.push(relPath);
      continue;
    }
    objects.push({ key: objectKey(header), header, sql, relPath });
  }
  return { objects, unparsable };
}

/** Locate the `{timestamp}_{migrationName}.cs` file EF just generated. */
export async function findGeneratedMigrationFile(
  migrationsDir: string,
  migrationName: string,
): Promise<string | null> {
  const files = (await findFiles('*.cs', { cwd: migrationsDir })).filter(
    (f) =>
      path.dirname(f) === migrationsDir &&
      !f.endsWith('.Designer.cs') &&
      path.basename(f).endsWith(`${migrationName}.cs`),
  );
  files.sort((a, b) => b.localeCompare(a));
  return files[0] ?? null;
}

async function readMigrationHistory(
  migrationsDir: string,
  exclude: string | null,
): Promise<string[]> {
  const files = (await findFiles('*.cs', { cwd: migrationsDir }))
    .filter(
      (f) =>
        path.dirname(f) === migrationsDir &&
        !f.endsWith('.Designer.cs') &&
        !f.endsWith('ModelSnapshot.cs') &&
        f !== exclude,
    )
    .sort((a, b) => a.localeCompare(b));
  return Promise.all(files.map((f) => readText(f)));
}

export interface EnsureSqlObjectsResult {
  /** Labels (schema.name) inlined into the new migration. */
  inlined: string[];
  /** .sql files whose header could not be parsed (skipped). */
  unparsable: string[];
  hadSqlObjects: boolean;
  injected: boolean;
  note?: string;
}

/**
 * After `dotnet ef migrations add`, inline into the freshly generated migration
 * the frozen literal of every SQL object that is new or changed vs the migration
 * history. Used by BOTH `create` and `squash`.
 */
export async function ensureSqlObjectsInMigration(opts: {
  projectDir: string;
  migrationsDir: string;
  migrationName: string;
}): Promise<EnsureSqlObjectsResult> {
  const { objects, unparsable } = await collectSqlObjects(opts.projectDir);
  if (objects.length === 0) {
    return { inlined: [], unparsable, hadSqlObjects: false, injected: false };
  }

  const newFile = await findGeneratedMigrationFile(opts.migrationsDir, opts.migrationName);
  const history = await readMigrationHistory(opts.migrationsDir, newFile);
  const changed = computeChangedObjects(objects, history);

  if (changed.length === 0) {
    return {
      inlined: [],
      unparsable,
      hadSqlObjects: true,
      injected: false,
      note: 'all SQL objects already current in migration history',
    };
  }
  if (!newFile) {
    return {
      inlined: [],
      unparsable,
      hadSqlObjects: true,
      injected: false,
      note: `generated migration file for "${opts.migrationName}" not found`,
    };
  }

  const src = await readText(newFile);
  // Idempotency: never append a second copy of an object the generated migration
  // already carries (guards against an accidental re-run on the same file).
  const alreadyInNew = extractInlinedSql(src);
  const toInject = changed.filter((o) => !alreadyInNew.has(o.key));
  if (toInject.length === 0) {
    return {
      inlined: [],
      unparsable,
      hadSqlObjects: true,
      injected: false,
      note: 'SQL objects already inlined in the generated migration',
    };
  }

  const next = injectSqlObjects(src, toInject);
  if (next === src) {
    return {
      inlined: [],
      unparsable,
      hadSqlObjects: true,
      injected: false,
      note: 'could not locate Up() in the generated migration',
    };
  }
  await writeText(newFile, next);
  return {
    inlined: toInject.map((o) => labelFor(o.header)),
    unparsable,
    hadSqlObjects: true,
    injected: true,
  };
}

/** Dry-run preview: which SQL objects WOULD be inlined into the next migration. */
export async function previewSqlObjectsForMigration(opts: {
  projectDir: string;
  migrationsDir: string;
}): Promise<string[]> {
  const { objects } = await collectSqlObjects(opts.projectDir);
  if (objects.length === 0) return [];
  const history = await readMigrationHistory(opts.migrationsDir, null);
  return computeChangedObjects(objects, history).map((o) => labelFor(o.header));
}