/**
 * 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,");
  });
});