import { prisma } from "@/lib/prisma";

const PAGE_SIZE_MAX = 100;
const PROTECTED_TABLES = new Set([
  "auth_users",
  "auth_user_permissions",
  "auth_user_sectors",
]);

type RawColumn = {
  TABLE_NAME: string;
  COLUMN_NAME: string;
  DATA_TYPE: string;
  COLUMN_TYPE: string;
  IS_NULLABLE: "YES" | "NO";
  COLUMN_DEFAULT: unknown;
  COLUMN_KEY: string;
  EXTRA: string;
  ORDINAL_POSITION: number;
};

export type AdminTableColumn = {
  name: string;
  dataType: string;
  columnType: string;
  nullable: boolean;
  defaultValue: unknown;
  primary: boolean;
  generated: boolean;
  autoIncrement: boolean;
  editable: boolean;
};

export type AdminTableDefinition = {
  name: string;
  category: "referentiel" | "metier" | "systeme";
  protected: boolean;
  exportable: boolean;
  columns: AdminTableColumn[];
  primaryKey: string;
  hasActiveColumn: boolean;
};

const REFERENCE_TABLES = new Set([
  "arh",
  "can_convinser",
  "can_metiers",
  "can_ruptbudg",
  "can_typcontr",
  "classif_salairesbase",
  "classifications",
  "code_insee_emploi",
  "cpam",
  "documents_auto",
  "etablissement",
  "grilles_salaire",
  "jours_feries",
  "medecine_travail",
  "motifs_derogation_mutuelle",
  "motifs_sortie",
  "mutuelles",
  "objet_visite_medicale",
  "postes",
  "ref_signataires",
  "secteurs",
  "secteurs_travail",
  "signataires",
  "types_absence",
  "types_avenants",
  "types_contrat_salarie",
  "types_cs",
  "types_diplome",
]);

function quoteIdentifier(value: string) {
  if (!/^[A-Za-z_][A-Za-z0-9_]*$/.test(value)) {
    throw new Error("INVALID_IDENTIFIER");
  }
  return `\`${value}\``;
}

function categoryFor(table: string): AdminTableDefinition["category"] {
  if (REFERENCE_TABLES.has(table)) return "referentiel";
  if (table.startsWith("auth_") || table.startsWith("prov_")) return "systeme";
  return "metier";
}

function serialize(value: unknown): unknown {
  if (typeof value === "bigint") return value.toString();
  if (value instanceof Date) return value.toISOString();
  if (Buffer.isBuffer(value)) return value.toString("base64");
  if (value && typeof value === "object") {
    if (Array.isArray(value)) return value.map(serialize);
    return Object.fromEntries(
      Object.entries(value as Record<string, unknown>).map(([key, item]) => [
        key,
        serialize(item),
      ]),
    );
  }
  return value;
}

async function readDefinitions(): Promise<AdminTableDefinition[]> {
  const rows = await prisma.$queryRaw<RawColumn[]>`
    SELECT
      c.TABLE_NAME,
      c.COLUMN_NAME,
      c.DATA_TYPE,
      c.COLUMN_TYPE,
      c.IS_NULLABLE,
      c.COLUMN_DEFAULT,
      c.COLUMN_KEY,
      c.EXTRA,
      c.ORDINAL_POSITION
    FROM information_schema.COLUMNS c
    JOIN information_schema.TABLES t
      ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
     AND t.TABLE_NAME = c.TABLE_NAME
    WHERE c.TABLE_SCHEMA = DATABASE()
      AND t.TABLE_TYPE = 'BASE TABLE'
      AND c.TABLE_NAME <> '_prisma_migrations'
    ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
  `;

  const grouped = new Map<string, RawColumn[]>();
  for (const row of rows) {
    const current = grouped.get(row.TABLE_NAME) ?? [];
    current.push(row);
    grouped.set(row.TABLE_NAME, current);
  }

  return [...grouped.entries()].map(([name, columns]) => {
    const protectedTable = PROTECTED_TABLES.has(name);
    const mapped = columns.map((column): AdminTableColumn => {
      const generated = column.EXTRA.toLowerCase().includes("generated");
      const autoIncrement =
        column.EXTRA.toLowerCase().includes("auto_increment");
      return {
        name: column.COLUMN_NAME,
        dataType: column.DATA_TYPE,
        columnType: column.COLUMN_TYPE,
        nullable: column.IS_NULLABLE === "YES",
        defaultValue: serialize(column.COLUMN_DEFAULT),
        primary: column.COLUMN_KEY === "PRI",
        generated,
        autoIncrement,
        editable: !protectedTable && !generated && !autoIncrement,
      };
    });
    const primaryKey = mapped.find((column) => column.primary)?.name ?? "id";
    return {
      name,
      category: categoryFor(name),
      protected: protectedTable,
      exportable: !name.startsWith("auth_"),
      columns: mapped,
      primaryKey,
      hasActiveColumn: mapped.some(
        (column) => column.name.toLowerCase() === "actif",
      ),
    };
  });
}

export async function listAdminTables() {
  return readDefinitions();
}

async function requireTable(tableName: string) {
  const table = (await readDefinitions()).find(
    (item) => item.name === tableName,
  );
  if (!table) throw new Error("TABLE_NOT_FOUND");
  return table;
}

function normalizeValue(column: AdminTableColumn, input: unknown) {
  if (input === null || input === "") {
    if (column.nullable) return null;
    throw new Error(`REQUIRED_VALUE:${column.name}`);
  }

  if (column.columnType.toLowerCase() === "tinyint(1)") {
    return input === true || input === 1 || input === "1" ? 1 : 0;
  }

  if (
    ["tinyint", "smallint", "mediumint", "int", "bigint"].includes(
      column.dataType,
    )
  ) {
    const value = Number(input);
    if (!Number.isInteger(value))
      throw new Error(`INVALID_VALUE:${column.name}`);
    return value;
  }

  if (["decimal", "float", "double"].includes(column.dataType)) {
    const value = Number(input);
    if (!Number.isFinite(value))
      throw new Error(`INVALID_VALUE:${column.name}`);
    return value;
  }

  if (
    ["date", "datetime", "timestamp", "time", "year"].includes(column.dataType)
  ) {
    return String(input).replace("T", " ");
  }

  if (column.dataType === "json" && typeof input !== "string") {
    return JSON.stringify(input);
  }

  return String(input);
}

export async function listAdminTableRows(input: {
  tableName: string;
  limit: number;
  offset: number;
  search?: string;
}) {
  const table = await requireTable(input.tableName);
  const limit = Math.min(Math.max(input.limit, 1), PAGE_SIZE_MAX);
  const offset = Math.max(input.offset, 0);
  const tableSql = quoteIdentifier(table.name);
  const searchable = table.columns.filter((column) =>
    ["char", "varchar", "text", "tinytext", "mediumtext", "longtext"].includes(
      column.dataType,
    ),
  );
  const params: unknown[] = [];
  let where = "";

  if (input.search?.trim() && searchable.length) {
    const needle = `%${input.search.trim()}%`;
    where = ` WHERE ${searchable
      .map((column) => `${quoteIdentifier(column.name)} LIKE ?`)
      .join(" OR ")}`;
    params.push(...searchable.map(() => needle));
  }

  const order = quoteIdentifier(table.primaryKey);
  const rows = await prisma.$queryRawUnsafe<Record<string, unknown>[]>(
    `SELECT * FROM ${tableSql}${where} ORDER BY ${order} ASC LIMIT ? OFFSET ?`,
    ...params,
    limit,
    offset,
  );
  const countRows = await prisma.$queryRawUnsafe<
    Array<{ total: bigint | number }>
  >(`SELECT COUNT(*) AS total FROM ${tableSql}${where}`, ...params);

  return {
    table,
    rows: serialize(rows),
    total: Number(countRows[0]?.total ?? 0),
    limit,
    offset,
  };
}

export async function exportAdminTableRows(tableName: string) {
  const table = await requireTable(tableName);
  if (!table.exportable) throw new Error("PROTECTED_TABLE");

  const tableSql = quoteIdentifier(table.name);
  const order = quoteIdentifier(table.primaryKey);
  const rows = await prisma.$queryRawUnsafe<Record<string, unknown>[]>(
    `SELECT * FROM ${tableSql} ORDER BY ${order} ASC`,
  );

  return {
    columns: table.columns.map((column) => column.name),
    rows: serialize(rows) as Record<string, unknown>[],
  };
}

export async function exportActiveEmployeesWithContracts() {
  const definitions = await readDefinitions();
  const employees = definitions.find((table) => table.name === "employes");
  const contracts = definitions.find((table) => table.name === "contrats");

  if (!employees || !contracts) throw new Error("TABLE_NOT_FOUND");

  const employeeColumns = employees.columns.map((column) => ({
    source: `e.${quoteIdentifier(column.name)}`,
    alias: `employe_${column.name}`,
  }));
  const contractColumns = contracts.columns.map((column) => ({
    source: `c.${quoteIdentifier(column.name)}`,
    alias: `contrat_${column.name}`,
  }));
  const columns = [...employeeColumns, ...contractColumns];
  const select = columns
    .map(
      (column) =>
        `${column.source} AS ${quoteIdentifier(column.alias)}`,
    )
    .join(",\n      ");

  const rows = await prisma.$queryRawUnsafe<Record<string, unknown>[]>(`
    WITH ranked_contracts AS (
      SELECT
        contrats.*,
        ROW_NUMBER() OVER (
          PARTITION BY contrats.Id_Salarie
          ORDER BY contrats.DAE DESC, contrats.id DESC
        ) AS export_row_number
      FROM contrats
      WHERE contrats.DAE IS NULL OR DATE(contrats.DAE) <= CURRENT_DATE()
    )
    SELECT
      ${select}
    FROM employes e
    INNER JOIN ranked_contracts c
      ON c.Id_Salarie = e.COS
     AND c.export_row_number = 1
    WHERE c.DSR IS NULL
      AND (c.DSP IS NULL OR DATE(c.DSP) >= CURRENT_DATE())
    ORDER BY e.NSA ASC, e.PRE ASC, e.COS ASC
  `);

  return {
    columns: columns.map((column) => column.alias),
    rows: serialize(rows) as Record<string, unknown>[],
  };
}

export async function createAdminTableRow(
  tableName: string,
  payload: Record<string, unknown>,
) {
  const table = await requireTable(tableName);
  if (table.protected) throw new Error("PROTECTED_TABLE");

  const columns = table.columns.filter(
    (column) => column.editable && Object.hasOwn(payload, column.name),
  );
  if (!columns.length) throw new Error("EMPTY_PAYLOAD");

  const values = columns.map((column) =>
    normalizeValue(column, payload[column.name]),
  );
  const sql = `INSERT INTO ${quoteIdentifier(table.name)} (${columns
    .map((column) => quoteIdentifier(column.name))
    .join(", ")}) VALUES (${columns.map(() => "?").join(", ")})`;

  await prisma.$executeRawUnsafe(sql, ...values);
  return { created: true };
}

export async function updateAdminTableRow(
  tableName: string,
  id: string,
  payload: Record<string, unknown>,
) {
  const table = await requireTable(tableName);
  if (table.protected) throw new Error("PROTECTED_TABLE");

  const columns = table.columns.filter(
    (column) =>
      column.editable && !column.primary && Object.hasOwn(payload, column.name),
  );
  if (!columns.length) throw new Error("EMPTY_PAYLOAD");

  const primaryColumn = table.columns.find(
    (column) => column.name === table.primaryKey,
  );
  if (!primaryColumn) throw new Error("PRIMARY_KEY_NOT_FOUND");

  const values = columns.map((column) =>
    normalizeValue(column, payload[column.name]),
  );
  values.push(normalizeValue(primaryColumn, id));
  const result = await prisma.$executeRawUnsafe(
    `UPDATE ${quoteIdentifier(table.name)} SET ${columns
      .map((column) => `${quoteIdentifier(column.name)} = ?`)
      .join(", ")} WHERE ${quoteIdentifier(table.primaryKey)} = ? LIMIT 1`,
    ...values,
  );

  return { updated: Number(result) };
}