import type { Criteria } from "@repo/rdx-criteria/server";
import { Op, OrderItem, WhereOptions, literal } from "sequelize";

// Campos físicos (DB) que permitimos filtrar/ordenar
const ALLOWED_FILTERS = {
  id: "id",
  customerId: "customer_id",
  invoiceSeries: "invoice_series",
  invoiceNumber: "invoice_number",
  invoiceDate: "invoice_date",
  status: "status",
  currencyCode: "currency_code",
  // Rango por total (en unidades menores)
  totalAmountValue: "total_amount_value",
} as const;

const ALLOWED_SORT: Record<string, string | string[]> = {
  // Sort "invoiceDate" realmente ordena por (invoice_date DESC, invoice_series ASC, invoice_number DESC, id DESC)
  invoiceDate: ["invoice_date"],
  invoiceNumber: ["invoice_number"],
  invoiceSeries: ["invoice_series"],
  status: ["status"],
  createdAt: ["created_at"],
  updatedAt: ["updated_at"],
};

// Proyección mínima para el listado (evita N+1 y payloads grandes)
export const DEFAULT_LIST_ATTRIBUTES = [
  "id",
  "company_id",
  "customer_id",
  "invoice_series",
  "invoice_number",
  "invoice_date",
  "status",
  "total_amount_value",
  "total_amount_scale",
  "currency_code",
  // Agregamos itemsCount por subconsulta (no es columna real)
] as const;

type Sanitized = {
  where: WhereOptions;
  order: OrderItem[];
  limit: number;
  offset: number;
  attributes: (string | any)[];
  // keyset opcional
  keyset?: {
    after?: { invoiceDate: string; invoiceSeries: string; invoiceNumber: number; id: string };
  };
};

const MAX_LIMIT = 100;
const DEFAULT_LIMIT = 25;

export function sanitizeListCriteria(criteria: Criteria): Sanitized {
  const { filters = {}, sort = [], pagination = {}, search } = (criteria ?? {}) as any;

  // LIMIT/OFFSET
  const rawLimit = Number(pagination?.limit ?? DEFAULT_LIMIT);
  const rawOffset = Number(pagination?.offset ?? 0);
  const limit = Number.isFinite(rawLimit)
    ? Math.max(1, Math.min(MAX_LIMIT, rawLimit))
    : DEFAULT_LIMIT;
  const offset = Number.isFinite(rawOffset) && rawOffset >= 0 ? rawOffset : 0;

  // WHERE base siempre por seguridad a nivel superior (company en repo)
  const where: WhereOptions = {};

  // Búsqueda libre (q) → aplicar sobre campos concretos (series/number/status)
  if (typeof search?.q === "string" && search.q.trim() !== "") {
    const q = `%${search.q.trim()}%`;
    Object.assign(where, {
      [Op.or]: [
        { invoice_series: { [Op.like]: q } },
        { status: { [Op.like]: q } },
        // Buscar por número como texto
        literal(`CAST(invoice_number AS CHAR) LIKE ${escapeLikeLiteral(q)}`),
      ],
    });
  }

  // Filtros permitidos
  for (const [key, value] of Object.entries(filters)) {
    const column = (ALLOWED_FILTERS as any)[key];
    if (!column) {
      throw forbiddenFilter(key);
    }
    if (value == null) continue;

    // Operadores sencillos permitidos
    if (typeof value === "object" && !Array.isArray(value)) {
      const v = value as any;
      const ops: any = {};
      if (v.eq !== undefined) ops[Op.eq] = v.eq;
      if (v.ne !== undefined) ops[Op.ne] = v.ne;
      if (v.in !== undefined && Array.isArray(v.in)) ops[Op.in] = v.in;
      if (v.like !== undefined) ops[Op.like] = `%${String(v.like)}%`;
      if (v.gte !== undefined) ops[Op.gte] = v.gte;
      if (v.lte !== undefined) ops[Op.lte] = v.lte;
      if (Object.keys(ops).length === 0) continue;
      (where as any)[column] = ops;
    } else {
      (where as any)[column] = value;
    }
  }

  // ORDER (determinista)
  const order: OrderItem[] = [];
  const sortArray = Array.isArray(sort)
    ? sort
    : String(sort || "")
        .split(",")
        .filter(Boolean);
  if (sortArray.length === 0) {
    // orden por defecto: invoice_date desc, invoice_series asc, invoice_number desc, id desc
    order.push(
      ["invoice_date", "DESC"],
      ["invoice_series", "ASC"],
      ["invoice_number", "DESC"],
      ["id", "DESC"]
    );
  } else {
    for (const part of sortArray) {
      const desc = String(part).startsWith("-");
      const key = desc ? String(part).slice(1) : String(part);
      const allowed = ALLOWED_SORT[key];
      if (!allowed) throw forbiddenSort(key);
      const cols = Array.isArray(allowed) ? allowed : [allowed];
      for (const c of cols) {
        order.push([c, desc ? "DESC" : "ASC"]);
      }
    }
    // tiebreaker final
    order.push(["id", "DESC"]);
  }

  // Atributos (proyección)
  const attributes: any[] = [...DEFAULT_LIST_ATTRIBUTES];

  // itemsCount por subconsulta (evitamos include)
  attributes.push([
    literal(
      "(SELECT COUNT(1) FROM customer_invoice_items it WHERE it.invoice_id = customer_invoices.id AND it.deleted_at IS NULL)"
    ),
    "itemsCount",
  ]);

  return { where, order, limit, offset, attributes };
}

// Helpers
function forbiddenFilter(field: string) {
  const e = new Error(`Filter "${field}" is not allowed`);
  (e as any).code = "FORBIDDEN_FILTER";
  return e;
}
function forbiddenSort(field: string) {
  const e = new Error(`Sort "${field}" is not allowed`);
  (e as any).code = "FORBIDDEN_SORT";
  return e;
}
function escapeLikeLiteral(v: string) {
  // Simple escapado para usar en literal; asume conexión confiable. Para máxima seguridad usa replacements.
  return `'${v.replace(/'/g, "''")}'`;
}
