import type {
  Column,
  Constraint,
  Extension,
  Table,
} from "./schema.js";

const PG_TO_SQL_TYPE: Record<string, string> = {
  bigint: "BIGINT",
  integer: "INTEGER",
  smallint: "SMALLINT",
  text: "TEXT",
  uuid: "UUID",
  boolean: "BOOLEAN",
  "timestamp with time zone": "TIMESTAMPTZ",
  "timestamp without time zone": "TIMESTAMP",
  numeric: "NUMERIC",
  json: "JSON",
  jsonb: "JSONB",
  "character varying": "VARCHAR",
  bytea: "BYTEA",
  "text[]": "TEXT[]",
  "uuid[]": "UUID[]",
};

const PG_TO_CQL_TYPE: Record<string, string> = {
  bigint: "bigint",
  integer: "int",
  smallint: "smallint",
  text: "text",
  uuid: "uuid",
  boolean: "boolean",
  "timestamp with time zone": "timestamp",
  "timestamp without time zone": "timestamp",
  numeric: "decimal",
  json: "text",
  jsonb: "text",
  "character varying": "text",
  bytea: "blob",
  "text[]": "list<text>",
  "uuid[]": "list<uuid>",
};

const CQL_RESERVED_KEYWORDS = new Set([
  "token",
  "schema",
  "table",
  "key",
  "create",
  "from",
  "primary",
  "boolean",
  "timestamp",
  "user",
  "uuid",
  "to",
  "update",
  "index",
  "text",
  "int",
  "decimal",
  "blob",
  "delete",
  "order",
]);

const SQL_RESERVED_KEYWORDS = new Set([
  "user",
  "table",
  "authorization",
  "default",
  "group",
  "primary",
  "to",
  "offset",
  "current_date",
  "current_time",
  "from",
  "order",
  "collate",
  "create",
]);

const mapPgTypeToSql = (pgType: string) => {
  if (pgType.startsWith("character varying")) {
    return pgType.toUpperCase();
  }
  return PG_TO_SQL_TYPE[pgType] ?? pgType.toUpperCase();
};

const mapPgTypeToCql = (pgType: string) => {
  if (pgType.startsWith("character varying")) {
    return "text";
  }
  if (pgType.endsWith("[]")) {
    const base = pgType.slice(0, -2);
    return `list<${PG_TO_CQL_TYPE[base] ?? "text"}>`;
  }
  return PG_TO_CQL_TYPE[pgType] ?? "text";
};

const quoteIdentifier = (name: string, keywords: Set<string>) => {
  if (!name) {
    return name;
  }
  const plain = name.replace(/_/g, "");
  const needsQuote =
    keywords.has(name.toLowerCase()) ||
    !plain ||
    !/^[a-z0-9]+$/i.test(plain) ||
    /^\d/.test(name) ||
    name.toLowerCase() === "user";
  return needsQuote ? `"${name}"` : name;
};

const quoteCqlIdentifier = (name: string) =>
  quoteIdentifier(name, CQL_RESERVED_KEYWORDS);
const quoteSqlIdentifier = (name: string) =>
  quoteIdentifier(name, SQL_RESERVED_KEYWORDS);

const cqlTableName = (name: string) =>
  quoteCqlIdentifier(name.replace(/^_+/, ""));
const sqlTableName = (name: string) =>
  quoteSqlIdentifier(name.replace(/^_+/, ""));

export const renderSqlCreateTable = (
  table: Table,
  schema: string,
  ifNotExists: boolean,
): string => {
  const lines: string[] = [];
  for (const column of table.columns) {
    const colName = quoteSqlIdentifier(column.column_name);
    const colType = mapPgTypeToSql(column.data_type);
    const defaultValue = column.default;
    if (column.column_name === "id" && column.data_type === "bigint") {
      lines.push(
        `    ${colName} ${colType} GENERATED BY DEFAULT AS IDENTITY NOT NULL`,
      );
      continue;
    }
    let line = `    ${colName} ${colType}`;
    if (!column.is_nullable) {
      line += " NOT NULL";
    }
    if (defaultValue) {
      line += ` DEFAULT ${defaultValue}`;
    }
    lines.push(line);
  }
  const tableName = sqlTableName(table.table_name);
  const clause = ifNotExists ? "CREATE TABLE IF NOT EXISTS" : "CREATE TABLE";
  return `${clause} ${schema}.${tableName} (\n${lines.join(",\n")}\n);\n`;
};

export const renderSqlCreateView = (view: Table, schema: string) => {
  const viewName = sqlTableName(view.table_name);
  return `-- View: ${schema}.${viewName}\nCREATE VIEW ${schema}.${viewName} AS /* definition omitted */;\n`;
};

export const renderSqlExtensions = (extensions: Extension[] = []) =>
  `${extensions
    .map(
      (ext) =>
        `CREATE EXTENSION IF NOT EXISTS ${ext.name} WITH VERSION '${ext.version}';`,
    )
    .join("\n")}\n`;

export const renderCqlCreateTable = (table: Table, cqlKeyspace = "public") => {
  const lines: string[] = [];
  const quotedTable = cqlTableName(table.table_name);
  let pk: string | undefined;
  for (const column of table.columns) {
    const colName = quoteCqlIdentifier(column.column_name);
    const colType = mapPgTypeToCql(column.data_type);
    if (!pk && (column.column_name === "id" || column.column_name === "uuid")) {
      pk = colName;
    }
    lines.push(`    ${colName} ${colType}`);
  }
  if (!pk && table.columns.length) {
    pk = quoteCqlIdentifier(table.columns[0].column_name);
  }
  const tableDef = lines.join(",\n");
  const primaryKey = pk ? `,\n    PRIMARY KEY (${pk})` : "";
  return `CREATE TABLE IF NOT EXISTS ${cqlKeyspace}.${quotedTable} (\n${tableDef}${primaryKey}\n);\n`;
};

const extractConstraints = (table: Table): Constraint[] => {
  const raw =
    table.constraints ??
    (table as any).table_constraints ??
    (table as any).constraints_info ??
    [];
  return Array.isArray(raw) ? raw : [];
};

const formatFkRule = (rule?: string) => {
  if (!rule) {
    return "";
  }
  const normalized = rule.trim().toUpperCase();
  const allowed = new Set([
    "NO ACTION",
    "RESTRICT",
    "CASCADE",
    "SET NULL",
    "SET DEFAULT",
  ]);
  return allowed.has(normalized) ? normalized : "";
};

export const renderTableConstraintsSql = (table: Table, schema: string) => {
  const tableName = sqlTableName(table.table_name);
  const constraints = extractConstraints(table);
  const nonFk: string[] = [];
  const fk: string[] = [];
  for (const constraint of constraints) {
    const type = (constraint.constraint_type ?? constraint.type ?? "").toUpperCase();
    const name = constraint.constraint_name ?? constraint.name;
    const columns =
      constraint.columns ??
      constraint.column_names ??
      constraint.constrained_columns ??
      [];
    if (!columns.length) {
      continue;
    }
    const columnList = columns.map(quoteSqlIdentifier).join(", ");
    const constraintNameSql = name ? ` ${quoteSqlIdentifier(name)}` : "";
    if (type === "PRIMARY KEY") {
      nonFk.push(
        `ALTER TABLE ${schema}.${tableName} ADD CONSTRAINT${constraintNameSql} PRIMARY KEY (${columnList});`,
      );
      continue;
    }
    if (type === "UNIQUE") {
      nonFk.push(
        `ALTER TABLE ${schema}.${tableName} ADD CONSTRAINT${constraintNameSql} UNIQUE (${columnList});`,
      );
      continue;
    }
    if (type === "FOREIGN KEY") {
      const refTable =
        constraint.foreign_table ??
        constraint.referenced_table ??
        constraint.foreign_table_name;
      if (!refTable) {
        continue;
      }
      const refSchema =
        constraint.foreign_table_schema ??
        constraint.referenced_schema ??
        constraint.schema ??
        "public";
      const refColumns =
        constraint.foreign_columns ?? constraint.referenced_columns ?? [];
      if (!refColumns.length) {
        continue;
      }
      const refColSql = refColumns.map(quoteSqlIdentifier).join(", ");
      let stmt = `ALTER TABLE ${schema}.${tableName} ADD CONSTRAINT${constraintNameSql} FOREIGN KEY (${columnList}) REFERENCES ${refSchema}.${sqlTableName(
        refTable,
      )} (${refColSql})`;
      const onDelete = formatFkRule(
        constraint.on_delete ?? constraint.delete_rule ?? constraint.delete,
      );
      const onUpdate = formatFkRule(
        constraint.on_update ?? constraint.update_rule ?? constraint.update,
      );
      if (onDelete) {
        stmt += ` ON DELETE ${onDelete}`;
      }
      if (onUpdate) {
        stmt += ` ON UPDATE ${onUpdate}`;
      }
      stmt += ";";
      fk.push(stmt);
    }
  }
  return { nonFk, fk };
};
