import { Database } from "bun:sqlite";
import { readdirSync, readFileSync } from "node:fs";
import { join } from "node:path";
import { ACCOUNT_SCHEMA, REPO_SCHEMA } from "./scoped-db/schema.gen";
import { ACCOUNT_SCHEMA_EXTRA, REPO_SCHEMA_EXTRA } from "./scoped-db/schema-extra";

const MIGRATIONS = join(import.meta.dir, "../../migrations");
/** The migration that dropped the moved tables from D1; before it, one schema held every table. */
const DROP_MIGRATION = "0057_drop_moved_tables.sql";

function migrationFiles(): string[] {
  return readdirSync(MIGRATIONS)
    .filter((name) => name.endsWith(".sql"))
    .sort();
}

function triggerNames(db: Database): string[] {
  return (db.query("SELECT name FROM sqlite_master WHERE type = 'trigger'").all() as { name: string }[]).map(
    (row) => row.name,
  );
}

/**
 * Triggers that still run in production: those D1 kept after the drop migration, and those the scoped stores
 * create. Every other trigger died with its table, so a test must not lean on it.
 */
function liveTriggers(): Set<string> {
  const d1 = new Database(":memory:");
  for (const file of migrationFiles()) d1.exec(readFileSync(join(MIGRATIONS, file), "utf8"));
  const live = new Set(triggerNames(d1));
  d1.close();
  for (const statement of [...REPO_SCHEMA, ...ACCOUNT_SCHEMA, ...REPO_SCHEMA_EXTRA, ...ACCOUNT_SCHEMA_EXTRA]) {
    const match = /^CREATE TRIGGER IF NOT EXISTS (\w+)/.exec(statement);
    if (match) live.add(match[1]);
  }
  return live;
}

/**
 * One sqlite that stands in for every store in a test: the D1 directory plus the tables that now live in
 * RepoState / AccountState, with the foreign keys they had when they shared a database (so seeds can rely on
 * cascades). Triggers that no longer exist anywhere in production are dropped, so a cascade the code must now
 * perform itself is never silently done by the test schema. Pair it with `sharedScopes(db)` so scoped queries
 * land in the same database.
 */
export function applyCombinedSchema(db: Database): void {
  for (const file of migrationFiles().filter((name) => name < DROP_MIGRATION)) {
    db.exec(readFileSync(join(MIGRATIONS, file), "utf8"));
  }
  for (const statement of [...REPO_SCHEMA_EXTRA, ...ACCOUNT_SCHEMA_EXTRA]) db.exec(statement);
  const live = liveTriggers();
  for (const name of triggerNames(db)) if (!live.has(name)) db.exec(`DROP TRIGGER IF EXISTS ${name}`);
}