import fs from "node:fs/promises";
import path from "node:path";
import type { CommandContext } from "../types.js";
import { CommandModule } from "../types.js";

const DEFAULT_SCHEMA_RELATIVE = path.join("lib", "db", "schema.ts");
const DEFAULT_REGISTRY_RELATIVE = path.join("lib", "db", "table-registry.ts");
const CATEGORY_ORDER = [
  "Auth & User Management",
  "Business Tables",
  "Companies",
  "Resource Management",
  "Cases & Activity",
  "Communication",
  "Audit & Logging",
  "Invoice Management",
  "Documents",
  "Assets",
  "Jurisdictions & Integrations",
  "Feature Flags",
  "Other",
];

type TableInfo = {
  varName: string;
  tableName: string;
  snakeName: string;
  category: string;
};

type TableRegistryOptions = {
  schemaPath?: string;
  registryPath?: string;
};

const snakeToCamel = (str: string): string =>
  str.replace(/_([a-z])/g, (_, letter) => letter.toUpperCase());

const camelToSnake = (str: string): string =>
  str.replace(/[A-Z]/g, (letter) => `_${letter.toLowerCase()}`);

const categorizeTable = (varName: string, tableName: string): string => {
  if (
    [
      "user",
      "session",
      "account",
      "verification",
      "organization",
      "member",
      "invitation",
      "twoFactor",
      "ssoProvider",
      "apikey",
      "apiKey",
    ].includes(varName)
  ) {
    return "Auth & User Management";
  }
  if (
    ["customers", "contacts", "products", "prices", "suppliers", "vendors"]
      .includes(varName)
  ) {
    return "Business Tables";
  }
  if (varName.startsWith("resource") || tableName.startsWith("resource_")) {
    return "Resource Management";
  }
  if (varName.startsWith("case") || tableName.startsWith("case")) {
    return "Cases & Activity";
  }
  if (
    [
      "emails",
      "notifications",
      "notificationSettings",
      "customerMessages",
      "messages",
    ].includes(varName) ||
    tableName.includes("notification") ||
    tableName.includes("message") ||
    tableName.includes("email")
  ) {
    return "Communication";
  }
  if (
    varName.includes("audit") ||
    varName.includes("event") ||
    varName.includes("log") ||
    tableName.includes("audit") ||
    tableName.includes("event") ||
    tableName.includes("log")
  ) {
    return "Audit & Logging";
  }
  if (
    varName.includes("invoice") ||
    varName.includes("Invoice") ||
    tableName.includes("invoice") ||
    varName.includes("lineItem")
  ) {
    return "Invoice Management";
  }
  if (varName === "companies") {
    return "Companies";
  }
  if (
    varName.includes("jurisdiction") ||
    varName.includes("integration") ||
    tableName.includes("jurisdiction") ||
    tableName.includes("integration")
  ) {
    return "Jurisdictions & Integrations";
  }
  if (
    varName.includes("flag") ||
    varName.includes("Flag") ||
    tableName.includes("flag")
  ) {
    return "Feature Flags";
  }
  if (varName.includes("document") || tableName.includes("document")) {
    return "Documents";
  }
  if (varName.includes("asset") || tableName.includes("asset")) {
    return "Assets";
  }
  return "Other";
};

const TABLE_REGEX =
  /export\s+const\s+(\w+)\s*=\s*pgTable\(\s*["']([^"']+)["']/gs;

const parseSchema = (schemaContent: string): TableInfo[] => {
  const tables: TableInfo[] = [];
  let match: RegExpExecArray | null;
  while ((match = TABLE_REGEX.exec(schemaContent)) !== null) {
    const varName = match[1];
    const tableName = match[2];
    const snakeName = camelToSnake(tableName).replace(/^_/, "");
    const category = categorizeTable(varName, tableName);
    tables.push({ varName, tableName, snakeName, category });
  }
  return tables;
};

const buildRegistryEntries = (tables: TableInfo[]): string => {
  const grouped = tables.reduce((acc, table) => {
    const bucket = acc[table.category];
    if (bucket) {
      bucket.push(table);
    } else {
      acc[table.category] = [table];
    }
    return acc;
  }, {} as Record<string, TableInfo[]>);
  const lines: string[] = [];
  for (const category of CATEGORY_ORDER) {
    const group = grouped[category];
    if (!group?.length) {
      continue;
    }
    lines.push(`\t// ${category}`);
    group.sort((a, b) => a.varName.localeCompare(b.varName));
    for (const table of group) {
      const { varName, tableName, snakeName } = table;
      const camelName = snakeToCamel(tableName);
      const aliasSet = new Set<string>();
      const addAlias = (alias?: string) => {
        if (!alias || aliasSet.has(alias)) {
          return;
        }
        aliasSet.add(alias);
        lines.push(`\t${alias}: schema.${varName},`);
      };
      addAlias(varName);
      addAlias(tableName);
      addAlias(snakeName);
      if (
        camelName &&
        camelName !== varName &&
        camelName !== tableName &&
        camelName !== snakeName
      ) {
        addAlias(camelName);
      }
    }
    lines.push("");
  }
  while (lines.length && lines[lines.length - 1] === "") {
    lines.pop();
  }
  return lines.join("\n");
};

const generateRegistryContent = (tables: TableInfo[]): string => {
  const entries = buildRegistryEntries(tables);
  const timestamp = new Date().toISOString();
  const header = `/**
 * Table Registry for Dynamic Access
 * Maps table names to their Drizzle schema definitions
 * 
 *   AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Generated by: CLI table registry generator
 * Last generated: ${timestamp}
 */
`;
  const footer = `
export function getTable(tableName: string): PgTable | undefined {
  return tableRegistry[tableName];
}

export function hasTable(tableName: string): boolean {
  return tableName in tableRegistry;
}

export function getTableNames(): string[] {
  return Object.keys(tableRegistry);
}

export function getUniqueTables(): PgTable[] {
  const seen = new Set<PgTable>();
  const unique: PgTable[] = [];
  for (const table of Object.values(tableRegistry)) {
    if (!seen.has(table)) {
      seen.add(table);
      unique.push(table);
    }
  }
  return unique;
}
`;
  return `${header}
import * as schema from "./schema";
import type { PgTable } from "drizzle-orm/pg-core";

export const tableRegistry: Record<string, PgTable> = {
${entries}
};

${footer}`;
};

export const generateTableRegistry = async (
  options: TableRegistryOptions = {},
): Promise<void> => {
  const schemaPath = path.resolve(
    process.cwd(),
    options.schemaPath ?? DEFAULT_SCHEMA_RELATIVE,
  );
  const registryPath = path.resolve(
    process.cwd(),
    options.registryPath ?? DEFAULT_REGISTRY_RELATIVE,
  );
  console.log(`Reading schema file ${schemaPath}`);
  try {
    await fs.access(schemaPath);
  } catch (error) {
    throw new Error(`Schema file missing at ${schemaPath}: ${error}`);
  }
  let schemaContent: string;
  try {
    schemaContent = await fs.readFile(schemaPath, "utf-8");
  } catch (error) {
    throw new Error(`Unable to read schema file ${schemaPath}: ${error}`);
  }
  console.log("Parsing table definitions");
  const tables = parseSchema(schemaContent);
  if (!tables.length) {
    throw new Error("No pgTable definitions found in schema");
  }
  console.log(`Found ${tables.length} tables`);
  const categoryCounts = tables.reduce((acc, table) => {
    acc[table.category] = (acc[table.category] ?? 0) + 1;
    return acc;
  }, {} as Record<string, number>);
  console.log("Generating registry file");
  const registryContent = generateRegistryContent(tables);
  try {
    await fs.mkdir(path.dirname(registryPath), { recursive: true });
    await fs.writeFile(registryPath, registryContent, "utf-8");
  } catch (error) {
    throw new Error(`Unable to write table registry ${registryPath}: ${error}`);
  }
  const entryCount = registryContent
    .split("\n")
    .filter((line) => line.includes("schema.")).length;
  console.log("Tables by category:");
  Object.entries(categoryCounts)
    .sort(([, a], [, b]) => b - a)
    .forEach(([category, count]) => {
      console.log(`  ${category}: ${count}`);
    });
  console.log("Table registry generated");
  console.log(`  tables: ${tables.length}`);
  console.log(`  categories: ${Object.keys(categoryCounts).length}`);
  console.log(`  entries: ${entryCount}`);
  console.log(`  output: ${registryPath}`);
};

const registryFlags = [
  {
    flag: "--registry-schema-path=PATH",
    description: "Override the schema.ts source path.",
  },
  {
    flag: "--registry-path=PATH",
    description: "Destination path for the generated table registry.",
  },
];

export const tableRegistryCommand: CommandModule = {
  name: "table-registry",
  description: "Generate a table registry from the Drizzle schema.",
  flags: registryFlags,
  handler: async ({ args }: CommandContext) => {
    await generateTableRegistry({
      schemaPath: args.registrySchemaPath,
      registryPath: args.registryPath,
    });
  },
};
