/**
 * One Drizzle definition per physical table.
 *
 * `organizations` was declared TWICE — in schema.ts (slug + created_by_id
 * NOT NULL, matching migration 0003 and therefore the real database) and in
 * schema-extensions.ts (display_name/website/location/is_verified, neither
 * NOT NULL column). Both mapped to the same physical table with irreconcilable
 * shapes.
 *
 * drizzle.config.ts includes only schema.ts and deploys run `db:migrate`,
 * never `db:push`, so the schema-extensions version was never migrated: it
 * described a table that has never existed. routes/orgs.tsx imported it, so
 * POST /orgs/new wrote columns Postgres does not have while omitting two that
 * are NOT NULL. Organization creation could never have succeeded, and because
 * each file typechecked against its own definition, tsc could not see it.
 *
 * The duplicate-table check below is the one that matters: it fails the build
 * for ANY table declared in more than one place, so this class cannot recur.
 */

import { describe, expect, it } from "bun:test";
import { readFileSync, readdirSync } from "fs";
import { join } from "path";

const DB_DIR = "src/db";

/**
 * Strip comments before scanning. Without this the check matches its own
 * documentation: these files explain the duplicate-table bug in prose that
 * necessarily contains `pgTable("organizations"`, and a naive scan then
 * reports the very thing the comment says was fixed.
 */
function code(src: string): string {
  return src
    .replace(/\/\*[\s\S]*?\*\//g, "")
    .replace(/^\s*\/\/.*$/gm, "");
}

function schemaFiles(): string[] {
  return readdirSync(DB_DIR)
    .filter((f) => f.endsWith(".ts") && f.includes("schema"))
    .map((f) => join(DB_DIR, f));
}

describe("no physical table is declared twice", () => {
  it("every pgTable name is unique across all schema files", () => {
    const seen = new Map<string, string[]>();
    for (const f of schemaFiles()) {
      const src = code(readFileSync(f, "utf8"));
      for (const m of src.matchAll(/pgTable\(\s*["'`]([^"'`]+)["'`]/g)) {
        const table = m[1];
        if (!seen.has(table)) seen.set(table, []);
        seen.get(table)!.push(f.replace(/\\/g, "/"));
      }
    }
    const dupes = [...seen.entries()]
      .filter(([, files]) => files.length > 1)
      .map(([t, files]) => `${t} declared in ${files.join(" AND ")}`);
    expect(dupes).toEqual([]);
  });

  it("schema-extensions re-exports the org/team tables rather than redeclaring them", () => {
    const src = code(readFileSync("src/db/schema-extensions.ts", "utf8"));
    // Matched loosely on purpose: asserting an exact import string breaks the
    // moment the list wraps across lines, which says nothing about the bug.
    expect(src).toMatch(/from\s+["']\.\/schema["']/);
    for (const t of ["organizations", "teams", "team_members", "team_repos", "org_members", "branch_protection"]) {
      expect(src).not.toMatch(new RegExp(`pgTable\\(\\s*["'\`]${t}["'\`]`));
    }
  });
});

describe("the organizations table matches its migration", () => {
  const schema = readFileSync("src/db/schema.ts", "utf8");
  const table = schema.slice(
    schema.indexOf('export const organizations = pgTable("organizations"'),
    schema.indexOf('export const orgMembers')
  );

  it("keeps the NOT NULL columns migration 0003 created", () => {
    expect(table).toContain('slug: text("slug").notNull()');
    expect(table).toContain('createdById: uuid("created_by_id")');
    expect(table).toContain(".notNull()");
  });

  it("carries the columns migration 0113 added", () => {
    // Added rather than dropped, so the create form's display name and
    // website are not silently discarded.
    for (const col of ["display_name", "website", "location", "is_verified"]) {
      expect(table).toContain(col);
    }
  });

  it("has a migration that adds those columns", () => {
    const sql = readFileSync("drizzle/0113_orgs_reconcile.sql", "utf8");
    for (const col of ["display_name", "website", "location", "is_verified"]) {
      expect(sql).toContain(`ADD COLUMN IF NOT EXISTS "${col}"`);
    }
    // Additive and idempotent — safe against a populated table and a re-run.
    expect(sql).not.toMatch(/DROP COLUMN|DROP TABLE/i);
  });
});

describe("POST /orgs/new supplies every required column", () => {
  const src = readFileSync("src/routes/orgs.tsx", "utf8");
  const insert = src.slice(
    src.indexOf(".insert(organizations)"),
    src.indexOf(".insert(organizations)") + 700
  );

  it("provides slug and createdById", () => {
    expect(insert).toContain("slug:");
    expect(insert).toContain("createdById:");
  });

  it("derives the slug from the validated name", () => {
    // `name` is already constrained to /^[a-zA-Z0-9._-]+$/ and collision-
    // checked against both users and orgs, so lowercasing it is a safe slug.
    expect(insert).toContain("slug: name.toLowerCase()");
  });
});

describe("teams feature matches the database", () => {
  const schema = readFileSync("src/db/schema.ts", "utf8");

  it("teams carries the permission column the UI renders", () => {
    // orgs.tsx renders `team.permission` and the create form collects it, but
    // the column existed only in the rival definition and was never migrated.
    // Anchored with a regex, not a literal newline — these files are CRLF.
    const t = schema.slice(schema.search(/pgTable\(\s*"teams"/), schema.indexOf("export const teamMembers"));
    expect(t).toContain('permission: text("permission")');
  });

  it("team_repos is declared in schema.ts so it gets a migration", () => {
    // It previously lived ONLY in schema-extensions, which drizzle.config.ts
    // does not include — so the table never existed, while orgs.tsx selected
    // and joined it to render a team's repo list.
    expect(schema).toMatch(/pgTable\(\s*"team_repos"/);
  });

  it("migration 0114 creates team_repos and adds teams.permission", () => {
    const sql = readFileSync("drizzle/0114_teams_reconcile.sql", "utf8");
    expect(sql).toContain('CREATE TABLE IF NOT EXISTS "team_repos"');
    expect(sql).toContain('ALTER TABLE "teams" ADD COLUMN IF NOT EXISTS "permission"');
    expect(sql).not.toMatch(/DROP COLUMN|DROP TABLE/i);
  });

  it("team creation supplies the NOT NULL slug", () => {
    const orgs = readFileSync("src/routes/orgs.tsx", "utf8");
    const insert = orgs.slice(orgs.indexOf(".insert(teams)"), orgs.indexOf(".insert(teams)") + 400);
    expect(insert).toContain("slug,");
  });
});

// ─────────────────────────────────────────────────────────────────────────
// Schema / migration drift
// ─────────────────────────────────────────────────────────────────────────

/**
 * Every column Drizzle declares must exist in the migrated database.
 *
 * This is the class that produced five silent runtime failures: org creation,
 * team creation, notify.ts inserts, the notification read-state split, and
 * `team_repos` — a table declared in code that had no migration at all, while
 * routes/orgs.tsx selected and joined it.
 *
 * The database is built solely by applying drizzle/*.sql in filename order
 * (drizzle.config.ts names only schema.ts; deploys run db:migrate, never
 * db:push), so the migrations ARE the schema of record. Replaying their DDL
 * gives the real shape to compare against.
 *
 * Only one direction is enforced: a column in code but not in the database is
 * a runtime error waiting to happen. The reverse — a column in the database
 * that Drizzle no longer declares — is harmless and common after a cleanup, so
 * it is reported by the scratch analyzer but not failed here.
 */
function declaredTables(): Map<string, Set<string>> {
  const out = new Map<string, Set<string>>();
  for (const f of schemaFiles()) {
    const src = code(readFileSync(f, "utf8"));
    // Split on pgTable( boundaries. Brace-matching is not safe here: a nested
    // `{ onDelete: "cascade" }` or a template literal makes a depth counter
    // run away and swallow the following tables' columns.
    for (const part of src.split(/pgTable\(/).slice(1)) {
      const name = part.match(/^\s*["'`]([a-z0-9_]+)["'`]/);
      if (!name) continue;
      const end = part.search(/\n\);/);
      const body = end > -1 ? part.slice(0, end) : part;
      if (!out.has(name[1])) out.set(name[1], new Set());
      for (const c of body.matchAll(/:\s*\w+\(\s*["'`]([a-z0-9_]+)["'`]/g)) {
        out.get(name[1])!.add(c[1]);
      }
    }
  }
  return out;
}

function migratedTables(): Map<string, Set<string>> {
  const dir = "drizzle";
  const out = new Map<string, Set<string>>();
  for (const f of readdirSync(dir).filter((x) => x.endsWith(".sql")).sort()) {
    const sql = readFileSync(join(dir, f), "utf8").replace(/--[^\n]*/g, "");

    for (const m of sql.matchAll(
      /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?"?([a-z0-9_]+)"?\s*\(([\s\S]*?)\n\s*\);/gi
    )) {
      if (!out.has(m[1])) out.set(m[1], new Set());
      const set = out.get(m[1])!;
      for (const line of m[2].split("\n")) {
        const t = line.trim();
        if (!t || /^(CONSTRAINT|PRIMARY\s+KEY|FOREIGN\s+KEY|UNIQUE|CHECK)\b/i.test(t)) continue;
        const c = t.match(/^"?([a-z0-9_]+)"?\s+/);
        if (c) set.add(c[1]);
      }
    }

    for (const m of sql.matchAll(/ALTER\s+TABLE\s+"?([a-z0-9_]+)"?\s+([\s\S]*?);/gi)) {
      if (!out.has(m[1])) out.set(m[1], new Set());
      const set = out.get(m[1])!;
      for (const a of m[2].matchAll(/ADD\s+COLUMN\s+(?:IF\s+NOT\s+EXISTS\s+)?"?([a-z0-9_]+)"?/gi)) set.add(a[1]);
      for (const d of m[2].matchAll(/DROP\s+COLUMN\s+(?:IF\s+EXISTS\s+)?"?([a-z0-9_]+)"?/gi)) set.delete(d[1]);
      for (const r of m[2].matchAll(/RENAME\s+COLUMN\s+"?([a-z0-9_]+)"?\s+TO\s+"?([a-z0-9_]+)"?/gi)) {
        set.delete(r[1]); set.add(r[2]);
      }
    }
  }
  return out;
}

describe("schema matches the migrations", () => {
  const declared = declaredTables();
  const migrated = migratedTables();

  it("parses a plausible number of tables (guards against the scan breaking)", () => {
    // A broken parser would make the assertions below vacuously pass.
    expect(declared.size).toBeGreaterThan(150);
    expect(migrated.size).toBeGreaterThan(150);
  });

  it("every declared table has a migration that creates it", () => {
    // `team_repos` failed this: declared, joined by orgs.tsx, never created.
    const missing = [...declared.keys()].filter((t) => !migrated.has(t)).sort();
    expect(missing).toEqual([]);
  });

  it("every declared column exists in the migrated schema", () => {
    const gaps: string[] = [];
    for (const [table, cols] of [...declared].sort()) {
      const have = migrated.get(table);
      if (!have) continue; // covered by the previous assertion
      const missing = [...cols].filter((c) => !have.has(c)).sort();
      if (missing.length) gaps.push(`${table}: ${missing.join(", ")}`);
    }
    expect(gaps).toEqual([]);
  });
});