import crypto from "node:crypto";
import fs from "node:fs/promises";
import path from "node:path";
import { query } from "../../db/pool.js";
import { receiptsStorageDir } from "../../lib/storage.js";
import { resolveCatalogEntry } from "../catalog/catalog.service.js";
import { createProduct, getProduct, type ProductRow } from "../products/products.service.js";
import { createIncident } from "../repairs/repairs.service.js";

export type PurchaseDocumentStatus = "pending" | "processed" | "needs_review" | "failed";
export type PurchaseLineStatus = "detected" | "confirmed" | "ignored" | "converted_to_product";

export interface PurchaseDocumentLineRow {
  id: number;
  document_id: number;
  user_id: number;
  line_number: number;
  raw_text: string;
  name_raw: string;
  brand_raw: string;
  model_raw: string;
  serial_number: string;
  imei: string;
  sku: string;
  category: string;
  quantity: number;
  unit_price: string | null;
  total_price: string | null;
  canonical_brand_id: number | null;
  canonical_product_id: number | null;
  linked_product_id: number | null;
  line_status: PurchaseLineStatus;
  confidence: string;
  metadata: Record<string, unknown>;
  created_at: string;
  updated_at: string;
}

export interface PurchaseDocumentRow {
  id: number;
  user_id: number;
  canonical_store_id: number | null;
  group_id: number | null;
  vendor_raw: string;
  vendor_canonical: string;
  document_number: string;
  purchase_date: string | null;
  total_amount: string | null;
  currency: string;
  file_url: string;
  file_name: string;
  mime_type: string;
  ocr_text: string;
  extraction_status: PurchaseDocumentStatus;
  extraction_confidence: string;
  document_fingerprint: string;
  extraction_started_at: string | null;
  metadata: Record<string, unknown>;
  created_at: string;
  updated_at: string;
}

export interface PurchaseDocumentGroupRow {
  id: number;
  user_id: number;
  name: string;
  slug: string;
  notes: string;
  created_at: string;
  updated_at: string;
}

export interface PurchaseDocumentGroupSummary extends PurchaseDocumentGroupRow {
  document_count: number;
  total_amount: string | null;
}

export interface PurchaseDocumentListItem extends PurchaseDocumentRow {
  line_count: number;
  confirmed_line_count: number;
  duplicate_count: number;
  purchase_summary: string;
  group_name: string;
  group_slug: string;
}

export interface PurchaseDocumentDetail extends PurchaseDocumentRow {
  lines: PurchaseDocumentLineRow[];
  line_count?: number;
  confirmed_line_count?: number;
  duplicate_count?: number;
  purchase_summary?: string;
  group_name?: string;
  group_slug?: string;
}

export interface PurchaseProductCandidate {
  document_id: number;
  document_number: string;
  vendor_raw: string;
  vendor_canonical: string;
  purchase_date: string | null;
  total_amount: string | null;
  currency: string;
  file_url: string;
  file_name: string;
  mime_type: string;
  line_id: number;
  line_number: number;
  raw_text: string;
  name_raw: string;
  brand_raw: string;
  model_raw: string;
  serial_number: string;
  imei: string;
  sku: string;
  category: string;
  quantity: number;
  unit_price: string | null;
  total_price: string | null;
  linked_product_id: number | null;
  line_status: PurchaseLineStatus;
  confidence: string;
}

export interface CreatePurchaseDocumentInput {
  vendorRaw: string;
  documentNumber?: string;
  purchaseDate?: string | null;
  totalAmount?: string | number | null;
  currency?: string;
  groupId?: number | null;
  fileUrl: string;
  fileName: string;
  mimeType: string;
  ocrText: string;
  metadata?: Record<string, unknown>;
}

export interface UpdatePurchaseDocumentInput {
  vendorRaw?: string;
  documentNumber?: string;
  purchaseDate?: string | null;
  totalAmount?: string | number | null;
  currency?: string;
  groupId?: number | null;
  fileUrl?: string;
  fileName?: string;
  mimeType?: string;
  ocrText?: string;
  metadata?: Record<string, unknown>;
}

export interface CreatePurchaseDocumentGroupInput {
  name: string;
  notes?: string;
}

export interface UpdatePurchaseLineInput {
  lineStatus?: PurchaseLineStatus;
  linkedProductId?: number | null;
  canonicalBrandId?: number | null;
  canonicalProductId?: number | null;
  nameRaw?: string;
  brandRaw?: string;
  modelRaw?: string;
  serialNumber?: string;
  imei?: string;
  sku?: string;
  category?: string;
  quantity?: number;
  unitPrice?: string | number | null;
  totalPrice?: string | number | null;
  rawText?: string;
  confidence?: string | number | null;
  metadata?: Record<string, unknown>;
}

export interface CreatePurchaseLineInput extends UpdatePurchaseLineInput {
  nameRaw: string;
}

export interface CreateProductFromPurchaseLineResult {
  product: ProductRow;
  line: PurchaseDocumentLineRow;
}

export interface CreateIncidentFromPurchaseLineResult {
  incident: Awaited<ReturnType<typeof createIncident>>;
  product: ProductRow;
  line: PurchaseDocumentLineRow;
}

interface ParsedLineCandidate {
  lineNumber: number;
  rawText: string;
  nameRaw: string;
  brandRaw: string;
  modelRaw: string;
  serialNumber: string;
  imei: string;
  sku: string;
  category: string;
  quantity: number;
  unitPrice: string | null;
  totalPrice: string | null;
  confidence: number;
  metadata: Record<string, unknown>;
}

function toNumber(value: string | number | null | undefined): number {
  if (value === null || value === undefined) return 0;
  return Number(value);
}

function normalizeTextLine(value: string): string {
  return value.replace(/\s+/g, " ").trim();
}

function normalizeFingerprintValue(value: string | number | null | undefined): string {
  if (value === null || value === undefined) return "";
  return normalizeTextLine(String(value)).toLowerCase();
}

function lineSignature(value: {
  name_raw?: string | null;
  brand_raw?: string | null;
  model_raw?: string | null;
  total_price?: string | number | null;
  raw_text?: string | null;
}): string {
  return [
    normalizeFingerprintValue(value.name_raw || value.raw_text || ""),
    normalizeFingerprintValue(value.brand_raw || ""),
    normalizeFingerprintValue(value.model_raw || ""),
    normalizeFingerprintValue(value.total_price ?? ""),
  ].join("|");
}

function computeDocumentFingerprint(input: CreatePurchaseDocumentInput, vendorCanonical: string): string {
  const payload = [
    normalizeFingerprintValue(vendorCanonical || input.vendorRaw),
    normalizeFingerprintValue(input.documentNumber),
    normalizeFingerprintValue(input.purchaseDate),
    normalizeFingerprintValue(input.totalAmount),
    normalizeFingerprintValue(input.currency ?? "EUR"),
    normalizeFingerprintValue(input.fileName),
    normalizeFingerprintValue(input.mimeType),
    normalizeFingerprintValue(input.ocrText),
  ].join("|");
  return crypto.createHash("sha256").update(payload).digest("hex");
}

function normalizeTrackingMetadata(metadata: Record<string, unknown> | undefined, ocrText: string): Record<string, unknown> {
  const next = { ...(metadata ?? {}) } as Record<string, unknown>;
  const hasTrackingData = ["trackingUrl", "trackingLabel", "trackingText", "qrUrl", "qrLabel", "qrText"].some((key) => {
    const value = next[key];
    return typeof value === "string" && value.trim().length > 0;
  });
  if (hasTrackingData) {
    if (typeof next.qrUrl === "string" && !next.trackingUrl) next.trackingUrl = next.qrUrl;
    if (typeof next.qrLabel === "string" && !next.trackingLabel) next.trackingLabel = next.qrLabel;
    if (typeof next.qrText === "string" && !next.trackingText) next.trackingText = next.qrText;
    return next;
  }

  const urlMatch = ocrText.match(/https?:\/\/[^\s<>"')\]]+/i);
  if (urlMatch?.[0]) {
    const url = urlMatch[0];
    next.trackingUrl = url;
    next.trackingText = next.trackingText ?? url;
    try {
      const parsed = new URL(url);
      const label = `${parsed.hostname}${parsed.pathname !== "/" ? parsed.pathname : ""}`.replace(/\/$/, "");
      next.trackingLabel = label || parsed.hostname || url;
    } catch {
      next.trackingLabel = next.trackingLabel ?? url;
    }
    return next;
  }

  const trackingLine = ocrText
    .replace(/\r/g, "\n")
    .split("\n")
    .map((line) => normalizeTextLine(line))
    .find((line) => /(seguim|tracking|rastreo|consulta|estado del pedido|localiza)/i.test(line));

  if (trackingLine) {
    next.trackingText = trackingLine;
  }

  return next;
}

function buildTrackingNote(metadata: Record<string, unknown> | undefined): string {
  if (!metadata) return "";
  const label = typeof metadata.trackingLabel === "string" ? metadata.trackingLabel.trim() : "";
  const url = typeof metadata.trackingUrl === "string" ? metadata.trackingUrl.trim() : "";
  const text = typeof metadata.trackingText === "string" ? metadata.trackingText.trim() : "";
  const parts = [label, url, text].filter((part, index, array) => part && array.indexOf(part) === index);
  return parts.length > 0 ? `Seguimiento: ${parts.join(" · ")}` : "";
}

function parseMoney(raw: string): string | null {
  const cleaned = raw.replace(/\s+/g, "").replace(/[^\d.,]/g, "");
  if (!cleaned) return null;
  const hasComma = cleaned.includes(",");
  const hasDot = cleaned.includes(".");
  if (!hasComma && !hasDot) return `${Number.parseInt(cleaned, 10) || 0}.00`;

  const decimalSep = hasComma && hasDot ? (cleaned.lastIndexOf(",") > cleaned.lastIndexOf(".") ? "," : ".") : (hasComma ? "," : ".");
  const parts = cleaned.split(decimalSep);
  const decimals = parts.pop() ?? "00";
  const integerPart = parts.join("").replace(/[.,]/g, "") || "0";
  const cents = decimals.padEnd(2, "0").slice(0, 2);
  const amount = Number(`${Number.parseInt(integerPart, 10)}.${cents}`);
  return Number.isFinite(amount) ? `${Number.parseInt(integerPart, 10)}.${cents}` : null;
}

function extractModel(value: string): string {
  const match = value.match(/\b([A-Z0-9]{2,8}(?:[-/][A-Z0-9]{1,8}){0,2})\b/);
  return match?.[1] ?? "";
}

function extractSerial(value: string): string {
  const match = value.match(/\b(?:serie|serial|sn|s\/n|imei)\s*[:#]?\s*([A-Z0-9\-]{6,})\b/i);
  return match?.[1] ?? "";
}

function extractSku(value: string): string {
  const match = value.match(/\b(?:sku|ref|referencia|art\.|articulo)\s*[:#]?\s*([A-Z0-9\-]{4,})\b/i);
  return match?.[1] ?? "";
}

function extractQuantity(value: string): number {
  const match = value.match(/\b(\d+)\s*x\b/i) || value.match(/\bx\s*(\d+)\b/i) || value.match(/\bqty\.?\s*(\d+)\b/i);
  return match?.[1] ? Math.max(1, Number.parseInt(match[1], 10)) : 1;
}

function likelyItemLine(line: string): boolean {
  return /\d[.,]\d{2}/.test(line) || /€/.test(line) || /\b(?:qty|cantidad|precio|total|importe)\b/i.test(line);
}

function isNonProductText(value: string): boolean {
  return /\b(?:subtotal|total|iva|impuesto|tasas?|reciclaje|ecoval|reee|canon|descuento|entrega|transporte|env[ií]o|pagado|pago|restante|cambio|vuelto|base imponible|forma de pago|cuenta de facturaci[oó]n|factura|pedido|fecha de|art[ií]culo\s+descripci[oó]n)\b/i.test(value);
}

function normalizedEvidence(value: string): string {
  return normalizeTextLine(value)
    .toLocaleLowerCase("es")
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .replace(/[^a-z0-9]+/g, " ")
    .trim();
}

function isPlausibleProductText(value: string): boolean {
  const text = normalizeTextLine(value);
  const letters = (text.match(/[A-Za-zÁÉÍÓÚÑáéíóúñ]/g) ?? []).length;
  return text.length >= 6 && letters >= 3 && !isNonProductText(text);
}

function extractLines(rawText: string): ParsedLineCandidate[] {
  const lines = rawText
    .replace(/\r/g, "\n")
    .split("\n")
    .map(normalizeTextLine)
    .filter(Boolean);

  const candidates: ParsedLineCandidate[] = [];
  for (let index = 0; index < lines.length; index += 1) {
    const line = lines[index]!;
    if (!likelyItemLine(line)) continue;
    if (/^(?:total|subtotal|iva|cif|nif|factura|ticket|pedido|pagado|cambio|vuelto)/i.test(line) || !isPlausibleProductText(line)) continue;

    const amountMatch = line.match(/(?:€\s*)?(\d[\d.,]*)\s*€?|€\s*(\d[\d.,]*)/);
    const amount = parseMoney(amountMatch?.[1] ?? amountMatch?.[2] ?? "");
    const quantity = extractQuantity(line);
    const modelRaw = extractModel(line);
    const serialNumber = extractSerial(line);
    const sku = extractSku(line);

    const brandMatch = line.match(/\b([A-ZÁÉÍÓÚÑ][A-Za-zÁÉÍÓÚÑ0-9'&.-]{1,30})\b/);
    const brandRaw = brandMatch?.[1] ?? "";

    const cleanedName = line
      .replace(/(?:€\s*)?\d[\d.,]*\s*€?/g, "")
      .replace(/\b(?:qty|cantidad|precio|total|importe|iva|unidad|unidades|ref|sku|serie|serial|imei)\b.*$/i, "")
      .trim();

    const nameRaw = normalizeTextLine(cleanedName || line);
    const confidence = Math.min(0.95, 0.35 + (amount ? 0.25 : 0) + (nameRaw.length > 12 ? 0.15 : 0) + (modelRaw ? 0.1 : 0) + (brandRaw ? 0.1 : 0));

    candidates.push({
      lineNumber: index + 1,
      rawText: line,
      nameRaw,
      brandRaw,
      modelRaw,
      serialNumber,
      imei: serialNumber,
      sku,
      category: "",
      quantity,
      unitPrice: amount,
      totalPrice: amount,
      confidence,
      metadata: {
        source_line_index: index,
        heuristic: true,
      },
    });
  }

  return candidates;
}

function extractAiLines(metadata: Record<string, unknown> | undefined, ocrText: string): ParsedLineCandidate[] {
  const products = Array.isArray(metadata?.aiProducts) ? metadata.aiProducts : [];
  const allowedCategories = new Set(["electronics", "mobile", "appliance", "computer", "clothing", "vehicle", "furniture", "tool", "toy", "digital", "secondhand", "other"]);
  const normalizedOcr = normalizedEvidence(ocrText);

  return products.flatMap((value, index) => {
    if (!value || typeof value !== "object") return [];
    const product = value as Record<string, unknown>;
    const nameRaw = normalizeTextLine(typeof product.name === "string" ? product.name : "");
    const brandRaw = normalizeTextLine(typeof product.brand === "string" ? product.brand : "");
    const modelRaw = normalizeTextLine(typeof product.model === "string" ? product.model : "");
    if (!nameRaw && !brandRaw && !modelRaw) return [];
    const sourceText = normalizeTextLine(typeof product.sourceText === "string" ? product.sourceText : "");
    const productText = [nameRaw, brandRaw, modelRaw].filter(Boolean).join(" ");
    if (!isPlausibleProductText(productText) || !sourceText || !normalizedOcr.includes(normalizedEvidence(sourceText))) return [];
    const serialNumber = normalizeTextLine(typeof product.serialNumber === "string" ? product.serialNumber : "");
    const quantity = Number(product.quantity);
    const unitPrice = product.unitPrice === null || product.unitPrice === undefined ? null : parseMoney(String(product.unitPrice));
    const totalPrice = product.totalPrice === null || product.totalPrice === undefined ? unitPrice : parseMoney(String(product.totalPrice));
    const confidence = Number(product.confidence);
    const suggestedWarrantyMonths = Number(product.warrantyMonths);
    const warrantyEvidence = normalizeTextLine(typeof product.warrantyEvidence === "string" ? product.warrantyEvidence : "");
    const proposedCategory = typeof product.category === "string" ? product.category.trim().toLowerCase() : "other";

    return [{
      lineNumber: index + 1,
      rawText: [nameRaw, brandRaw, modelRaw, serialNumber].filter(Boolean).join(" "),
      nameRaw: nameRaw || [brandRaw, modelRaw].filter(Boolean).join(" "),
      brandRaw,
      modelRaw,
      serialNumber,
      imei: serialNumber,
      sku: "",
      category: allowedCategories.has(proposedCategory) ? proposedCategory : "other",
      quantity: Number.isInteger(quantity) && quantity > 0 ? Math.min(quantity, 999) : 1,
      unitPrice,
      totalPrice,
      confidence: Number.isFinite(confidence) ? Math.min(1, Math.max(0, confidence)) : 0.5,
      metadata: {
        source: "openai",
        aiDetected: true,
        aiProductIndex: index,
        sourceText,
        ...(Number.isInteger(suggestedWarrantyMonths) && suggestedWarrantyMonths > 0 && suggestedWarrantyMonths <= 600
          ? { aiSuggestedWarrantyMonths: suggestedWarrantyMonths, aiWarrantyEvidence: warrantyEvidence }
          : {}),
      },
    }];
  });
}

function sanitizeVendor(value: string): string {
  return normalizeTextLine(value);
}

function normalizeGroupSlug(value: string): string {
  return normalizeTextLine(value)
    .toLowerCase()
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/^-+|-+$/g, "")
    .slice(0, 120) || "grupo";
}

async function resolveCanonicalStore(vendorRaw: string): Promise<{ id: number | null; displayName: string }> {
  const resolved = await resolveCatalogEntry("store", vendorRaw);
  if (resolved) {
    return { id: resolved.id, displayName: resolved.display_name };
  }
  return { id: null, displayName: sanitizeVendor(vendorRaw) };
}

function addMonthsToDate(dateIso: string, months: number): string {
  const date = new Date(`${dateIso}T00:00:00.000Z`);
  if (Number.isNaN(date.getTime())) return dateIso;
  const month = date.getUTCMonth() + months;
  date.setUTCMonth(month);
  return date.toISOString().slice(0, 10);
}

function todayIso(): string {
  return new Date().toISOString().slice(0, 10);
}

function buildProductName(line: PurchaseDocumentLineRow, document: PurchaseDocumentRow): string {
  const parts = [line.name_raw, line.brand_raw, line.model_raw].map((part) => normalizeTextLine(part)).filter(Boolean);
  if (parts.length > 0) return parts.join(" ");
  if (document.vendor_canonical) return document.vendor_canonical;
  if (document.vendor_raw) return document.vendor_raw;
  return `Producto factura ${line.line_number}`;
}

function buildProductBrand(line: PurchaseDocumentLineRow): string {
  return normalizeTextLine(line.brand_raw || "");
}

function buildProductModel(line: PurchaseDocumentLineRow): string {
  return normalizeTextLine(line.model_raw || line.sku || "");
}

async function loadPurchaseLine(userId: number, documentId: number, lineId: number): Promise<{ document: PurchaseDocumentRow; line: PurchaseDocumentLineRow } | null> {
  const documentRows = await query<PurchaseDocumentRow>(`
    SELECT *
    FROM purchase_documents
    WHERE id = $1 AND user_id = $2
    LIMIT 1
  `, [documentId, userId]);
  const document = documentRows[0];
  if (!document) return null;

  const lineRows = await query<PurchaseDocumentLineRow>(`
    SELECT *
    FROM purchase_document_lines
    WHERE id = $1 AND user_id = $2 AND document_id = $3
    LIMIT 1
  `, [lineId, userId, documentId]);
  const line = lineRows[0];
  if (!line) return null;

  return { document, line };
}

async function persistPurchaseDocumentLines(userId: number, document: PurchaseDocumentRow): Promise<PurchaseDocumentLineRow[]> {
  const preservedLines = await query<PurchaseDocumentLineRow>(
    `SELECT *
     FROM purchase_document_lines
     WHERE user_id = $1
       AND document_id = $2
       AND (
        linked_product_id IS NOT NULL
        OR line_status IN ('confirmed', 'ignored', 'converted_to_product')
        OR metadata ? 'productLinkedFromManualForm'
        OR metadata ? 'manuallyAddedFromLineId'
        OR metadata ? 'manuallyAdded'
       )
     ORDER BY line_number ASC, created_at ASC`,
    [userId, document.id]
  );
  const preservedSignatures = new Set(preservedLines.map(lineSignature));

  await query(
    `DELETE FROM purchase_document_lines
     WHERE user_id = $1
       AND document_id = $2
       AND linked_product_id IS NULL
       AND line_status = 'detected'
       AND NOT (metadata ? 'productLinkedFromManualForm')
       AND NOT (metadata ? 'manuallyAddedFromLineId')
       AND NOT (metadata ? 'manuallyAdded')`,
    [userId, document.id]
  );

  const aiCandidates = extractAiLines(document.metadata, document.ocr_text ?? "");
  const rawCandidates = aiCandidates.length > 0 ? aiCandidates : extractLines(document.ocr_text ?? "");
  const insertedLines: PurchaseDocumentLineRow[] = [];
  const nextLineRows = await query<{ next_line_number: number }>(
    `SELECT COALESCE(MAX(line_number), 0) + 1 AS next_line_number
     FROM purchase_document_lines
     WHERE user_id = $1 AND document_id = $2`,
    [userId, document.id]
  );
  let nextLineNumber = nextLineRows[0]?.next_line_number ?? 1;
  for (const candidate of rawCandidates) {
    const signature = lineSignature({
      name_raw: candidate.nameRaw,
      brand_raw: candidate.brandRaw,
      model_raw: candidate.modelRaw,
      total_price: candidate.totalPrice,
      raw_text: candidate.rawText,
    });
    if (preservedSignatures.has(signature)) continue;

    const brand = candidate.brandRaw || "";
    const canonicalBrand = brand ? await resolveCatalogEntry("brand", brand) : null;
    const rows = await query<PurchaseDocumentLineRow>(`
      INSERT INTO purchase_document_lines (
        document_id, user_id, line_number, raw_text, name_raw, brand_raw, model_raw, serial_number,
        imei, sku, category, quantity, unit_price, total_price, canonical_brand_id, canonical_product_id,
        linked_product_id, line_status, confidence, metadata
      ) VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,NULL,NULL,'detected',$16,$17
      )
      RETURNING *
    `, [
      document.id,
      userId,
      nextLineNumber++,
      candidate.rawText,
      candidate.nameRaw,
      candidate.brandRaw,
      candidate.modelRaw,
      candidate.serialNumber,
      candidate.imei,
      candidate.sku,
      candidate.category,
      candidate.quantity,
      candidate.unitPrice,
      candidate.totalPrice,
      canonicalBrand?.id ?? null,
      candidate.confidence,
      candidate.metadata,
    ]);
    insertedLines.push(rows[0]!);
  }

  const finalStatus: PurchaseDocumentStatus = insertedLines.length > 0 ? "processed" : "needs_review";
  const finalConfidence = insertedLines.length > 0 ? 0.75 : 0.25;
  await query(
    "UPDATE purchase_documents SET extraction_status = $3, extraction_confidence = $4, extraction_started_at = NOW() WHERE id = $1 AND user_id = $2",
    [document.id, userId, finalStatus, finalConfidence]
  );

  return insertedLines;
}

function mapDocument(row: PurchaseDocumentRow & { line_count?: number; confirmed_line_count?: number; duplicate_count?: number; purchase_summary?: string }): PurchaseDocumentListItem {
  return {
    ...row,
    line_count: row.line_count ?? 0,
    confirmed_line_count: row.confirmed_line_count ?? 0,
    duplicate_count: row.duplicate_count ?? 1,
    purchase_summary: "purchase_summary" in row && typeof row.purchase_summary === "string" ? row.purchase_summary : "",
    extraction_confidence: row.extraction_confidence ?? "0",
    document_fingerprint: row.document_fingerprint ?? "",
    group_name: "group_name" in row && typeof (row as { group_name?: string }).group_name === "string" ? (row as { group_name?: string }).group_name ?? "" : "",
    group_slug: "group_slug" in row && typeof (row as { group_slug?: string }).group_slug === "string" ? (row as { group_slug?: string }).group_slug ?? "" : "",
  };
}

function mapGroup(row: PurchaseDocumentGroupRow & { document_count?: number; total_amount?: string | null }): PurchaseDocumentGroupSummary {
  return {
    ...row,
    document_count: row.document_count ?? 0,
    total_amount: row.total_amount ?? null,
  };
}

export async function listPurchaseDocuments(userId: number): Promise<PurchaseDocumentListItem[]> {
  const rows = await query<PurchaseDocumentRow>(`
    SELECT
      d.*,
      g.name AS group_name,
      g.slug AS group_slug,
      COALESCE(line_counts.line_count, 0)::int AS line_count,
      COALESCE(line_counts.confirmed_line_count, 0)::int AS confirmed_line_count,
      COALESCE(line_summaries.purchase_summary, '') AS purchase_summary,
      COALESCE(dup_counts.duplicate_count, 1)::int AS duplicate_count
    FROM purchase_documents d
    LEFT JOIN purchase_document_groups g ON g.id = d.group_id AND g.user_id = d.user_id
    LEFT JOIN (
      SELECT
        document_id,
        COUNT(*)::int AS line_count,
        COUNT(*) FILTER (WHERE line_status = 'confirmed')::int AS confirmed_line_count
      FROM purchase_document_lines
      WHERE user_id = $1
      GROUP BY document_id
    ) line_counts ON line_counts.document_id = d.id
    LEFT JOIN LATERAL (
      SELECT string_agg(summary_item, ', ' ORDER BY line_number) AS purchase_summary
      FROM (
        SELECT
          l.line_number,
          COALESCE(NULLIF(l.name_raw, ''), NULLIF(TRIM(CONCAT_WS(' ', NULLIF(l.brand_raw, ''), NULLIF(l.model_raw, ''))), ''), NULLIF(l.raw_text, '')) AS summary_item
        FROM purchase_document_lines l
        WHERE l.user_id = $1
          AND l.document_id = d.id
          AND l.line_status <> 'ignored'
        ORDER BY l.line_number ASC, l.created_at ASC
        LIMIT 4
      ) summary_lines
      WHERE summary_item IS NOT NULL
    ) line_summaries ON TRUE
    LEFT JOIN (
      SELECT
        document_fingerprint,
        COUNT(*)::int AS duplicate_count
      FROM purchase_documents
      WHERE user_id = $1 AND document_fingerprint <> ''
      GROUP BY document_fingerprint
    ) dup_counts ON dup_counts.document_fingerprint = d.document_fingerprint
    WHERE d.user_id = $1
    ORDER BY d.purchase_date DESC NULLS LAST, d.created_at DESC
  `, [userId]);
  return rows.map(mapDocument);
}

export async function listPurchaseProductCandidates(
  userId: number,
  options?: { documentId?: number | null; includeConverted?: boolean }
): Promise<PurchaseProductCandidate[]> {
  const documentId = options?.documentId ?? null;
  const includeConverted = options?.includeConverted ?? false;

  return query<PurchaseProductCandidate>(
    `
      SELECT
        d.id AS document_id,
        d.document_number,
        d.vendor_raw,
        d.vendor_canonical,
        d.purchase_date,
        d.total_amount,
        d.currency,
        d.file_url,
        d.file_name,
        d.mime_type,
        l.id AS line_id,
        l.line_number,
        l.raw_text,
        l.name_raw,
        l.brand_raw,
        l.model_raw,
        l.serial_number,
        l.imei,
        l.sku,
        l.category,
        l.quantity,
        l.unit_price,
        l.total_price,
        l.linked_product_id,
        l.line_status,
        l.confidence
      FROM purchase_document_lines l
      INNER JOIN purchase_documents d ON d.id = l.document_id AND d.user_id = l.user_id
      WHERE l.user_id = $1
        AND ($2::int IS NULL OR l.document_id = $2)
        AND l.line_status <> 'ignored'
        AND ($3::boolean = TRUE OR l.linked_product_id IS NULL)
      ORDER BY d.purchase_date DESC NULLS LAST, d.created_at DESC, l.line_number ASC, l.created_at ASC
    `,
    [userId, documentId, includeConverted]
  );
}

export async function getPurchaseDocument(userId: number, id: number): Promise<PurchaseDocumentDetail | null> {
  const docRows = await query<PurchaseDocumentRow>(`
    SELECT
      d.*,
      g.name AS group_name,
      g.slug AS group_slug,
      COALESCE(line_counts.line_count, 0)::int AS line_count,
      COALESCE(line_counts.confirmed_line_count, 0)::int AS confirmed_line_count,
      COALESCE(line_summaries.purchase_summary, '') AS purchase_summary,
      COALESCE(dup_counts.duplicate_count, 1)::int AS duplicate_count
    FROM purchase_documents d
    LEFT JOIN purchase_document_groups g ON g.id = d.group_id AND g.user_id = d.user_id
    LEFT JOIN (
      SELECT
        document_id,
        COUNT(*)::int AS line_count,
        COUNT(*) FILTER (WHERE line_status = 'confirmed')::int AS confirmed_line_count
      FROM purchase_document_lines
      WHERE user_id = $1
      GROUP BY document_id
    ) line_counts ON line_counts.document_id = d.id
    LEFT JOIN LATERAL (
      SELECT string_agg(summary_item, ', ' ORDER BY line_number) AS purchase_summary
      FROM (
        SELECT
          l.line_number,
          COALESCE(NULLIF(l.name_raw, ''), NULLIF(TRIM(CONCAT_WS(' ', NULLIF(l.brand_raw, ''), NULLIF(l.model_raw, ''))), ''), NULLIF(l.raw_text, '')) AS summary_item
        FROM purchase_document_lines l
        WHERE l.user_id = $1
          AND l.document_id = d.id
          AND l.line_status <> 'ignored'
        ORDER BY l.line_number ASC, l.created_at ASC
        LIMIT 4
      ) summary_lines
      WHERE summary_item IS NOT NULL
    ) line_summaries ON TRUE
    LEFT JOIN (
      SELECT
        document_fingerprint,
        COUNT(*)::int AS duplicate_count
      FROM purchase_documents
      WHERE user_id = $1 AND document_fingerprint <> ''
      GROUP BY document_fingerprint
    ) dup_counts ON dup_counts.document_fingerprint = d.document_fingerprint
    WHERE d.user_id = $1 AND d.id = $2
    LIMIT 1
  `, [userId, id]);
  const document = docRows[0];
  if (!document) return null;

  const lines = await query<PurchaseDocumentLineRow>(
    "SELECT * FROM purchase_document_lines WHERE user_id = $1 AND document_id = $2 ORDER BY line_number ASC, created_at ASC",
    [userId, id]
  );

  return { ...mapDocument(document), lines };
}

export async function createPurchaseDocument(userId: number, input: CreatePurchaseDocumentInput): Promise<PurchaseDocumentDetail> {
  const store = await resolveCanonicalStore(input.vendorRaw);
  const documentFingerprint = computeDocumentFingerprint(input, store.displayName);
  const metadata = normalizeTrackingMetadata(input.metadata, input.ocrText ?? "");
  let groupId: number | null = null;
  if (input.groupId !== undefined) {
    if (input.groupId === null) {
      groupId = null;
    } else {
      const groupRows = await query<{ id: number }>(
        "SELECT id FROM purchase_document_groups WHERE id = $1 AND user_id = $2 LIMIT 1",
        [input.groupId, userId]
      );
      groupId = groupRows[0]?.id ?? null;
    }
  }
  const insertRows = await query<PurchaseDocumentRow>(`
    INSERT INTO purchase_documents (
      user_id, canonical_store_id, vendor_raw, vendor_canonical, document_number, purchase_date,
      total_amount, currency, group_id, file_url, file_name, mime_type, ocr_text, extraction_status,
      extraction_confidence, document_fingerprint, extraction_started_at, metadata
    ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,'pending',$14,$15,NOW(),$16)
    RETURNING *
  `, [
    userId,
    store.id,
    sanitizeVendor(input.vendorRaw),
    store.displayName,
    input.documentNumber ?? "",
    input.purchaseDate ?? null,
    input.totalAmount == null || input.totalAmount === "" ? null : String(input.totalAmount),
    input.currency ?? "EUR",
    groupId,
    input.fileUrl,
    input.fileName,
    input.mimeType,
    input.ocrText ?? "",
    0,
    documentFingerprint,
    metadata,
  ]);
  const document = insertRows[0]!;
  void persistPurchaseDocumentLines(userId, document).catch((err) => {
    console.error("[purchases] async extraction error:", err);
  });

  return {
    ...mapDocument(document),
    lines: [],
  };
}

export async function listPurchaseDocumentGroups(userId: number): Promise<PurchaseDocumentGroupSummary[]> {
  const rows = await query<PurchaseDocumentGroupRow & { document_count: number; total_amount: string | null }>(`
    SELECT
      g.*,
      COUNT(d.id)::int AS document_count,
      COALESCE(SUM(d.total_amount), 0)::text AS total_amount
    FROM purchase_document_groups g
    LEFT JOIN purchase_documents d ON d.group_id = g.id AND d.user_id = g.user_id
    WHERE g.user_id = $1
    GROUP BY g.id
    ORDER BY g.name ASC, g.created_at ASC
  `, [userId]);
  return rows.map(mapGroup);
}

export async function createPurchaseDocumentGroup(
  userId: number,
  input: CreatePurchaseDocumentGroupInput
): Promise<PurchaseDocumentGroupSummary> {
  const name = normalizeTextLine(input.name);
  if (!name) {
    throw new Error("group_name_required");
  }
  const slug = normalizeGroupSlug(name);
  const rows = await query<PurchaseDocumentGroupRow & { document_count: number; total_amount: string | null }>(`
    INSERT INTO purchase_document_groups (user_id, name, slug, notes)
    VALUES ($1, $2, $3, $4)
    ON CONFLICT (user_id, slug)
    DO UPDATE SET name = EXCLUDED.name, notes = EXCLUDED.notes, updated_at = NOW()
    RETURNING *, 0::int AS document_count, NULL::text AS total_amount
  `, [userId, name, slug, input.notes ?? ""]);
  return mapGroup(rows[0]!);
}

export async function updatePurchaseDocument(userId: number, id: number, input: UpdatePurchaseDocumentInput): Promise<PurchaseDocumentDetail | null> {
  const currentRows = await query<PurchaseDocumentRow>(
    "SELECT * FROM purchase_documents WHERE id = $1 AND user_id = $2 LIMIT 1",
    [id, userId]
  );
  const current = currentRows[0];
  if (!current) return null;

  const vendorRaw = input.vendorRaw !== undefined ? sanitizeVendor(input.vendorRaw) : current.vendor_raw;
  const store = input.vendorRaw !== undefined ? await resolveCanonicalStore(vendorRaw) : null;
  const vendorCanonical = store?.displayName ?? current.vendor_canonical;
  const canonicalStoreId = store?.id ?? current.canonical_store_id;
  let groupId = current.group_id;
  if (input.groupId !== undefined) {
    if (input.groupId === null) {
      groupId = null;
    } else {
      const groupRows = await query<{ id: number }>(
        "SELECT id FROM purchase_document_groups WHERE id = $1 AND user_id = $2 LIMIT 1",
        [input.groupId, userId]
      );
      groupId = groupRows[0]?.id ?? null;
    }
  }
  const nextMetadata = input.metadata ? { ...(current.metadata ?? {}), ...input.metadata } : current.metadata;
  const nextFileUrl = input.fileUrl ?? current.file_url;
  const nextFileName = input.fileName ?? current.file_name;
  const nextMimeType = input.mimeType ?? current.mime_type;
  const nextOcrText = input.ocrText ?? current.ocr_text;
  const fileChanged = input.fileUrl !== undefined && input.fileUrl !== current.file_url;

  if (fileChanged && current.file_url.startsWith("/receipts/")) {
    const previousFile = path.join(receiptsStorageDir, path.basename(current.file_url));
    await fs.unlink(previousFile).catch(() => {});
  }

  await query(
    `UPDATE purchase_documents
     SET
      canonical_store_id = $3,
      vendor_raw = $4,
      vendor_canonical = $5,
      document_number = $6,
      purchase_date = $7,
      total_amount = $8,
      currency = $9,
      group_id = $10,
      file_url = $11,
      file_name = $12,
      mime_type = $13,
      ocr_text = $14,
      extraction_status = $15,
      extraction_confidence = $16,
      metadata = $17,
      updated_at = NOW()
     WHERE id = $1 AND user_id = $2`,
    [
      id,
      userId,
      canonicalStoreId,
      vendorRaw,
      vendorCanonical,
      input.documentNumber ?? current.document_number,
      input.purchaseDate !== undefined ? input.purchaseDate : current.purchase_date,
      input.totalAmount !== undefined
        ? (input.totalAmount == null || input.totalAmount === "" ? null : String(input.totalAmount))
        : current.total_amount,
      input.currency ?? current.currency,
      groupId,
      nextFileUrl,
      nextFileName,
      nextMimeType,
      nextOcrText,
      fileChanged || input.ocrText !== undefined ? "pending" : current.extraction_status,
      fileChanged || input.ocrText !== undefined ? "0" : current.extraction_confidence,
      nextMetadata,
    ]
  );

  return getPurchaseDocument(userId, id);
}

export async function processPendingPurchaseDocuments(limit = 20): Promise<number> {
  const rows = await query<PurchaseDocumentRow>(`
    SELECT *
    FROM purchase_documents
    WHERE extraction_status = 'pending'
      AND ocr_text <> ''
      AND (extraction_started_at IS NULL OR extraction_started_at < NOW() - INTERVAL '10 minutes')
    ORDER BY created_at ASC
    LIMIT $1
  `, [limit]);

  let processed = 0;
  for (const document of rows) {
    const lines = await persistPurchaseDocumentLines(document.user_id, document);
    if (lines.length > 0) processed += 1;
  }

  return processed;
}

export async function reprocessPurchaseDocument(userId: number, id: number): Promise<PurchaseDocumentDetail | null> {
  const docRows = await query<PurchaseDocumentRow>(`
    SELECT *
    FROM purchase_documents
    WHERE id = $1 AND user_id = $2
    LIMIT 1
  `, [id, userId]);
  const document = docRows[0];
  if (!document) return null;

  await query("UPDATE purchase_documents SET extraction_started_at = NOW() WHERE id = $1 AND user_id = $2", [document.id, userId]);
  await persistPurchaseDocumentLines(userId, document);
  return getPurchaseDocument(userId, id);
}

export async function createPurchaseLine(userId: number, documentId: number, input: CreatePurchaseLineInput): Promise<PurchaseDocumentLineRow | null> {
  const documentRows = await query<{ id: number }>(
    "SELECT id FROM purchase_documents WHERE id = $1 AND user_id = $2 LIMIT 1",
    [documentId, userId]
  );
  if (!documentRows[0]) return null;

  const nextLineRows = await query<{ next_line_number: number }>(
    `SELECT COALESCE(MAX(line_number), 0) + 1 AS next_line_number
     FROM purchase_document_lines WHERE document_id = $1 AND user_id = $2`,
    [documentId, userId]
  );
  const brand = normalizeTextLine(input.brandRaw ?? "");
  const canonicalBrand = brand ? await resolveCatalogEntry("brand", brand) : null;
  const rows = await query<PurchaseDocumentLineRow>(`
    INSERT INTO purchase_document_lines (
      document_id, user_id, line_number, raw_text, name_raw, brand_raw, model_raw, serial_number,
      imei, sku, category, quantity, unit_price, total_price, canonical_brand_id, canonical_product_id,
      linked_product_id, line_status, confidence, metadata
    ) VALUES (
      $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,NULL,NULL,'detected',$16,$17
    )
    RETURNING *
  `, [
    documentId,
    userId,
    nextLineRows[0]?.next_line_number ?? 1,
    input.rawText ?? input.nameRaw,
    normalizeTextLine(input.nameRaw),
    brand,
    normalizeTextLine(input.modelRaw ?? ""),
    normalizeTextLine(input.serialNumber ?? ""),
    normalizeTextLine(input.imei ?? ""),
    normalizeTextLine(input.sku ?? ""),
    normalizeTextLine(input.category ?? "other"),
    input.quantity ?? 1,
    input.unitPrice == null || input.unitPrice === "" ? null : String(input.unitPrice),
    input.totalPrice == null || input.totalPrice === "" ? null : String(input.totalPrice),
    canonicalBrand?.id ?? null,
    input.confidence == null || input.confidence === "" ? "1" : String(input.confidence),
    { ...(input.metadata ?? {}), manuallyAdded: true },
  ]);
  return rows[0] ?? null;
}

export async function updatePurchaseLine(userId: number, documentId: number, lineId: number, input: UpdatePurchaseLineInput): Promise<PurchaseDocumentLineRow | null> {
  const current = await query<PurchaseDocumentLineRow>(
    "SELECT * FROM purchase_document_lines WHERE user_id = $1 AND document_id = $2 AND id = $3 LIMIT 1",
    [userId, documentId, lineId]
  );
  const line = current[0];
  if (!line) return null;

  const rows = await query<PurchaseDocumentLineRow>(`
    UPDATE purchase_document_lines
    SET
      line_status = COALESCE($4, line_status),
      linked_product_id = COALESCE($5, linked_product_id),
      canonical_brand_id = COALESCE($6, canonical_brand_id),
      canonical_product_id = COALESCE($7, canonical_product_id),
      name_raw = COALESCE($8, name_raw),
      brand_raw = COALESCE($9, brand_raw),
      model_raw = COALESCE($10, model_raw),
      serial_number = COALESCE($11, serial_number),
      imei = COALESCE($12, imei),
      sku = COALESCE($13, sku),
      category = COALESCE($14, category),
      quantity = COALESCE($15, quantity),
      unit_price = COALESCE($16, unit_price),
      total_price = COALESCE($17, total_price),
      raw_text = COALESCE($18, raw_text),
      confidence = COALESCE($19, confidence),
      metadata = COALESCE($20, metadata)
    WHERE id = $1 AND user_id = $2 AND document_id = $3
    RETURNING *
  `, [
    lineId,
    userId,
    documentId,
    input.lineStatus ?? null,
    input.linkedProductId ?? null,
    input.canonicalBrandId ?? null,
    input.canonicalProductId ?? null,
    input.nameRaw ?? null,
    input.brandRaw ?? null,
    input.modelRaw ?? null,
    input.serialNumber ?? null,
    input.imei ?? null,
    input.sku ?? null,
    input.category ?? null,
    input.quantity ?? null,
    input.unitPrice == null || input.unitPrice === "" ? null : String(input.unitPrice),
    input.totalPrice == null || input.totalPrice === "" ? null : String(input.totalPrice),
    input.rawText ?? null,
    input.confidence == null || input.confidence === "" ? null : String(input.confidence),
    input.metadata ?? null,
  ]);
  return rows[0] ?? null;
}

export async function deletePurchaseLine(userId: number, documentId: number, lineId: number): Promise<boolean> {
  const rows = await query<{ id: number }>(
    `DELETE FROM purchase_document_lines
     WHERE id = $1 AND user_id = $2 AND document_id = $3
     RETURNING id`,
    [lineId, userId, documentId]
  );
  return rows.length > 0;
}

export async function createProductFromPurchaseLine(userId: number, documentId: number, lineId: number): Promise<CreateProductFromPurchaseLineResult | null> {
  const source = await loadPurchaseLine(userId, documentId, lineId);
  if (!source) return null;
  const { document, line } = source;

  if (line.line_status === "ignored") {
    throw new Error("purchase_line_ignored");
  }

  if (line.linked_product_id) {
    const current = await getProduct(userId, line.linked_product_id);
    if (current) {
      return { product: current, line };
    }
  }

  const purchaseDate = document.purchase_date ?? todayIso();
  const requestedWarrantyMonths = Number(line.metadata?.warrantyMonths);
  const legalWarrantyMonths = Number.isInteger(requestedWarrantyMonths) && requestedWarrantyMonths >= 1 && requestedWarrantyMonths <= 600
    ? requestedWarrantyMonths
    : 36;
  const purchasePriceRaw = line.total_price ?? line.unit_price ?? document.total_amount;
  const purchasePrice = purchasePriceRaw == null || purchasePriceRaw === "" ? null : Number(purchasePriceRaw);
  const product = await createProduct({
    userId,
    name: buildProductName(line, document),
    brand: buildProductBrand(line),
    model: buildProductModel(line),
    category: line.category || "other",
    purchaseDate,
    purchasePrice: purchasePrice !== null && Number.isFinite(purchasePrice) ? purchasePrice : null,
    vendor: document.vendor_canonical || document.vendor_raw,
    serialNumber: line.serial_number || line.imei || "",
    receiptImage: document.file_url,
    legalWarrantyMonths,
    warrantyEndDate: addMonthsToDate(purchaseDate, legalWarrantyMonths),
    hasExtendedWarranty: false,
    extendedWarrantyEndDate: null,
    extendedWarrantyProvider: "",
    extendedWarrantyNotes: "",
    hasInsurance: false,
    insuranceEndDate: null,
    insuranceProvider: "",
    insurancePolicyNumber: "",
    notes: [
      `Creado desde factura ${document.file_name}${line.raw_text ? ` · línea ${line.line_number}` : ""}`,
      buildTrackingNote(document.metadata),
      typeof line.metadata?.warrantyBasis === "string" ? `Garantía seleccionada: ${line.metadata.warrantyBasis}` : "",
    ].filter(Boolean).join("\n"),
    isSecondHand: line.category === "secondhand" || line.metadata?.warrantyBasis === "used_professional",
  });

  const linkedLine = await updatePurchaseLine(userId, documentId, lineId, {
    lineStatus: "converted_to_product",
    linkedProductId: product.id,
  });

  return { product, line: linkedLine ?? line };
}

export async function createIncidentFromPurchaseLine(userId: number, documentId: number, lineId: number): Promise<CreateIncidentFromPurchaseLineResult | null> {
  const source = await loadPurchaseLine(userId, documentId, lineId);
  if (!source) return null;
  const { document, line } = source;

  const productResult = await createProductFromPurchaseLine(userId, documentId, lineId);
  if (!productResult) return null;

  const incident = await createIncident({
    userId,
    productId: productResult.product.id,
    purchaseDocumentLineId: line.id,
    title: buildProductName(line, document),
    incidentType: "warranty",
    detectedAt: todayIso(),
    deliveryPlace: document.vendor_canonical || document.vendor_raw,
    receivedBy: "",
    description: line.raw_text || document.ocr_text.slice(0, 500),
    symptom: line.name_raw || buildProductName(line, document),
    accessories: "",
    satName: document.vendor_canonical || document.vendor_raw,
    satReference: line.serial_number || line.imei || line.sku || document.document_number || "",
    diagnosis: "",
    technicianNotes: [
      `Creada desde factura ${document.file_name} · línea ${line.line_number}`,
      buildTrackingNote(document.metadata),
    ].filter(Boolean).join("\n"),
    estimatedCost: line.total_price ? toNumber(line.total_price) : null,
    resolution: "",
    replacedWithNew: false,
    replacedWithRefurbished: false,
    userRating: null,
    userRatingNotes: "",
    sharePublicRating: false,
  });

  const linkedLine = await updatePurchaseLine(userId, documentId, lineId, {
    lineStatus: "converted_to_product",
    linkedProductId: productResult.product.id,
  });

  return {
    incident,
    product: productResult.product,
    line: linkedLine ?? line,
  };
}