'use strict';
// ─── REPORTE SEMANAL FINANCIERO ───────────────────────────────────────────────
// Genera el Excel "Reporte Financiero Semanal" con el mismo layout que el archivo
// manual (REPORTE_FINANCIERO_DE_EPICO.xlsx):
//   SECCIÓN 1 — Ventas por producto (días lun–dom): venta del día, costo compra,
//               costo total, precio venta, venta total, utilidad
//   SECCIÓN 2 — Entretenimiento (bandas/artistas): fecha, costo, venta, utilidad
//   SECCIÓN 3 — Gastos misceláneos e insumos (compras por proveedor)
//   FILA RESUMEN — totales semana
// Requiere: npm install exceljs  (se agrega en package.json automáticamente)
// ─────────────────────────────────────────────────────────────────────────────

// ExcelJS se carga bajo demanda (lazy) para no consumir ~15-20MB de heap
// al arrancar el servidor. Solo se necesita cuando el usuario genera un reporte.
let ExcelJS = null;
function getExcelJS() {
  if (!ExcelJS) ExcelJS = require('exceljs');
  return ExcelJS;
}
const { db }   = require('../utils/dbName');
const { getPool } = require('../db');
const sql      = require('mssql');

// ── Colores corporativos ──────────────────────────────────────────────────────
const C = {
  NARANJA_HEADER : 'FFE07B28',   // naranja ARKHON/EPICO
  GRIS_SUBHEADER : 'FF4B4B4B',   // gris oscuro para sub-encabezados de día
  GRIS_CLARO     : 'FFF2F2F2',   // fondo filas alternas
  VERDE_UTIL     : 'FF21A366',   // utilidad positiva
  ROJO_NEG       : 'FFD93025',   // utilidad negativa
  AMARILLO_TOTAL : 'FFFFF0C4',   // fondo filas de total
  AZUL_SECCION   : 'FF1F4E79',   // título de sección
  BLANCO         : 'FFFFFFFF',
  NEGRO          : 'FF000000',
  GRIS_BORDE     : 'FFB0B0B0',
};

const DIAS_ES = ['DOMINGO','LUNES','MARTES','MIÉRCOLES','JUEVES','VIERNES','SÁBADO'];

// ── Helpers ───────────────────────────────────────────────────────────────────
function addDays(dateStr, n) {
  const d = new Date(dateStr + 'T12:00:00'); // mediodía evita problemas de timezone
  d.setDate(d.getDate() + n);
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, '0');
  const day = String(d.getDate()).padStart(2, '0');
  return `${y}-${m}-${day}`;
}

function weekLabel(inicio, fin) {
  const meses = ['ENERO','FEBRERO','MARZO','ABRIL','MAYO','JUNIO',
                 'JULIO','AGOSTO','SEPTIEMBRE','OCTUBRE','NOVIEMBRE','DICIEMBRE'];
  const d1 = new Date(inicio + 'T12:00:00');
  const d2 = new Date(fin    + 'T12:00:00');
  if (d1.getMonth() === d2.getMonth()) {
    return `SEMANA DEL ${d1.getDate()} AL ${d2.getDate()} DE ${meses[d1.getMonth()]}`;
  }
  return `SEMANA DEL ${d1.getDate()} DE ${meses[d1.getMonth()]} AL ${d2.getDate()} DE ${meses[d2.getMonth()]}`;
}

function border(style = 'thin') {
  const s = { style };
  return { top: s, left: s, bottom: s, right: s };
}

function applyBorder(cell, style = 'thin') {
  cell.border = border(style);
}

function setFill(cell, argb) {
  cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb } };
}

function setFont(cell, opts = {}) {
  cell.font = { name: 'Arial', size: opts.size || 9, bold: !!opts.bold,
                color: { argb: opts.color || C.NEGRO } };
}

function money(cell, val) {
  cell.value = typeof val === 'number' ? val : (parseFloat(val) || 0);
  cell.numFmt = '$#,##0.00;[Red]($#,##0.00)';
}

function num(cell, val) {
  cell.value = typeof val === 'number' ? val : (parseFloat(val) || 0);
  cell.numFmt = '#,##0.##';
}

function headerCell(cell, text, opts = {}) {
  cell.value = text;
  cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
  setFill(cell, opts.bg || C.NARANJA_HEADER);
  setFont(cell, { bold: true, color: C.BLANCO, size: opts.size || 9 });
  applyBorder(cell);
}

// ── QUERY: ventas por producto por día ────────────────────────────────────────
async function getVentasPorProductoPorDia(pool, dias) {
  const logger = (() => { try { return require('../utils/logger'); } catch(_) { return console; } })();

  if (!dias || !dias.length) return [];
  const fechaDesde = dias[0];               // '2026-03-01'
  const fechaHasta = dias[dias.length - 1]; // '2026-03-07'
  logger.info(`[ExcelSemanal] Consultando ventas del ${fechaDesde} al ${fechaHasta} (${dias.length} días)`);

  // FIX DEFINITIVO: usar interpolación directa de fechas en vez de parámetros sql.VarChar/sql.Date.
  // Las fechas son generadas por el código (addDays), NO por input del usuario → no hay riesgo de inyección.
  // Los parámetros @desde/@hasta con sql.VarChar(10) no funcionan correctamente en esta versión del driver mssql.
  try {
    // Nivel 1: cheqdet + productosdetalle + grupos
    try {
      const r = await pool.request().query(`
        SELECT
          LTRIM(RTRIM(p.descripcion))       AS producto,
          ISNULL(g.descripcion,'Sin grupo') AS grupo,
          CAST(c.fecha AS DATE)             AS fecha,
          ROUND(SUM(cd.cantidad), 2)        AS cantidad,
          0                                 AS costo_compra,
          ISNULL(MAX(pd.precio), 0)         AS precio_venta,
          ROUND(SUM(cd.cantidad * cd.precio), 2) AS venta_total
        FROM ${db()}cheqdet cd
        INNER JOIN ${db()}cheques          c  ON cd.foliodet   = c.folio
        INNER JOIN ${db()}productos        p  ON cd.idproducto = p.idproducto
        LEFT  JOIN ${db()}productosdetalle pd ON pd.idproducto = p.idproducto
        LEFT  JOIN ${db()}grupos           g  ON p.idgrupo     = g.idgrupo
        WHERE CAST(c.fecha AS DATE) BETWEEN '${fechaDesde}' AND '${fechaHasta}'
          AND c.cancelado = 0 AND c.pagado = 1 AND cd.modificador = 0
        GROUP BY LTRIM(RTRIM(p.descripcion)), g.descripcion, CAST(c.fecha AS DATE)
        ORDER BY g.descripcion, p.descripcion, fecha
      `);
      if (r.recordset.length > 0) {
        logger.info(`[ExcelSemanal] Nivel 1: ${r.recordset.length} filas`);
        return r.recordset;
      }
      logger.info('[ExcelSemanal] Nivel 1: 0 filas — probando nivel 2');
    } catch(e) {
      logger.warn(`[ExcelSemanal] Nivel 1 falló: ${e.message} — probando nivel 2`);
    }

    // Nivel 2: sin productosdetalle
    try {
      const r2 = await pool.request().query(`
        SELECT
          LTRIM(RTRIM(p.descripcion))       AS producto,
          ISNULL(g.descripcion,'Sin grupo') AS grupo,
          CAST(c.fecha AS DATE)             AS fecha,
          ROUND(SUM(cd.cantidad), 2)        AS cantidad,
          0                                 AS costo_compra,
          ROUND(MAX(cd.precio), 2)          AS precio_venta,
          ROUND(SUM(cd.cantidad * cd.precio), 2) AS venta_total
        FROM ${db()}cheqdet cd
        INNER JOIN ${db()}cheques   c ON cd.foliodet   = c.folio
        INNER JOIN ${db()}productos p ON cd.idproducto = p.idproducto
        LEFT  JOIN ${db()}grupos    g ON p.idgrupo     = g.idgrupo
        WHERE CAST(c.fecha AS DATE) BETWEEN '${fechaDesde}' AND '${fechaHasta}'
          AND c.cancelado = 0 AND c.pagado = 1 AND cd.modificador = 0
        GROUP BY LTRIM(RTRIM(p.descripcion)), g.descripcion, CAST(c.fecha AS DATE)
        ORDER BY g.descripcion, p.descripcion, fecha
      `);
      if (r2.recordset.length > 0) {
        logger.info(`[ExcelSemanal] Nivel 2: ${r2.recordset.length} filas`);
        return r2.recordset;
      }
      logger.info('[ExcelSemanal] Nivel 2: 0 filas — probando nivel 3');
    } catch(e) {
      logger.warn(`[ExcelSemanal] Nivel 2 falló: ${e.message} — probando nivel 3`);
    }

    // Nivel 3: mínimo sin grupos
    try {
      const r3 = await pool.request().query(`
        SELECT
          LTRIM(RTRIM(p.descripcion)) AS producto,
          'Sin grupo'                 AS grupo,
          CAST(c.fecha AS DATE)       AS fecha,
          ROUND(SUM(cd.cantidad), 2)  AS cantidad,
          0 AS costo_compra, 0 AS precio_venta,
          ROUND(SUM(cd.cantidad * cd.precio), 2) AS venta_total
        FROM ${db()}cheqdet cd
        INNER JOIN ${db()}cheques   c ON cd.foliodet   = c.folio
        INNER JOIN ${db()}productos p ON cd.idproducto = p.idproducto
        WHERE CAST(c.fecha AS DATE) BETWEEN '${fechaDesde}' AND '${fechaHasta}'
          AND c.cancelado = 0 AND c.pagado = 1 AND cd.modificador = 0
        GROUP BY LTRIM(RTRIM(p.descripcion)), CAST(c.fecha AS DATE)
        ORDER BY p.descripcion, fecha
      `);
      if (r3.recordset.length > 0) {
        logger.info(`[ExcelSemanal] Nivel 3: ${r3.recordset.length} filas`);
        return r3.recordset;
      }
    } catch(e) {
      logger.warn(`[ExcelSemanal] Nivel 3 falló: ${e.message}`);
    }

    logger.error(`[ExcelSemanal] Todos los niveles agotados — 0 filas`);
    return [];
  } catch(excelErr) {
    logger.error('[getVentasPorProductoPorDia] Error:', excelErr?.message || excelErr);
    return [];
  }
}

// ── QUERY: compras por proveedor (gastos misceláneos) ────────────────────────
async function getComprasSemana(pool, inicio, fin) {
  const logger = (() => { try { return require('../utils/logger'); } catch(_) { return console; } })();
  try {
    const r = await pool.request().query(`
      SELECT
        CAST(c.fechaaplicacion AS DATE)        AS fecha,
        ISNULL(p.nombre, c.idproveedor)        AS proveedor,
        ISNULL(c.total, 0)                     AS total
      FROM ${db()}compras c
      LEFT JOIN ${db()}proveedores p ON p.idproveedor = c.idproveedor
      WHERE CAST(c.fechaaplicacion AS DATE) BETWEEN '${inicio}' AND '${fin}'
      ORDER BY fecha, proveedor
    `);
    logger.info(`[ExcelSemanal] Compras: ${r.recordset.length} registros`);
    return r.recordset;
  } catch(e) {
    logger.warn(`[ExcelSemanal] Compras falló: ${e.message}`);
    return [];
  }
}

// ── WRITER: construye la hoja Excel ──────────────────────────────────────────
async function buildSheet(ws, inicio, fin, ventas, compras) {
  const dias = [];
  for (let i = 0; i < 7; i++) dias.push(addDays(inicio, i));

  // índice: producto → { [fecha]: { cantidad, costo_compra, precio_venta, venta_total } }
  const prodMap = {};
  const grupos  = {};  // producto → grupo
  for (const r of ventas) {
    const key  = r.producto;
    // FIX: NO usar toISOString() — convierte a UTC y puede cambiar el día en zonas horarias negativas
    let fecha;
    if (r.fecha instanceof Date) {
      const y = r.fecha.getFullYear();
      const m = String(r.fecha.getMonth() + 1).padStart(2, '0');
      const d = String(r.fecha.getDate()).padStart(2, '0');
      fecha = `${y}-${m}-${d}`;
    } else {
      fecha = String(r.fecha).split('T')[0];
    }
    if (!prodMap[key]) { prodMap[key] = {}; grupos[key] = r.grupo || 'Sin grupo'; }
    prodMap[key][fecha] = {
      cantidad    : parseFloat(r.cantidad)    || 0,
      costo_compra: parseFloat(r.costo_compra)|| 0,
      precio_venta: parseFloat(r.precio_venta)|| 0,
      venta_total : parseFloat(r.venta_total) || 0,
    };
  }

  // Ordenar productos por grupo luego nombre
  const productos = Object.keys(prodMap).sort((a, b) => {
    const ga = grupos[a], gb = grupos[b];
    if (ga !== gb) return ga.localeCompare(gb);
    return a.localeCompare(b);
  });

  // ── Columnas ──────────────────────────────────────────────────────────────
  // Col B = descripción (ancho 32)
  // Por cada día (7): VENTA DÍA | COSTO COMPRA | COSTO TOTAL | PV | VENTA TOTAL | UTILIDAD (6 cols)
  // Col final = TOTAL SEMANA (VENTA) | UTILIDAD TOTAL
  const COL_DESC = 2; // B
  const COLS_POR_DIA = 6;
  const COL_DIA_START = (d) => COL_DESC + 1 + d * COLS_POR_DIA; // C = 3

  ws.getColumn(1).width = 3;   // A vacía (margen)
  ws.getColumn(2).width = 34;  // Descripción

  for (let d = 0; d < 7; d++) {
    const base = COL_DIA_START(d);
    ws.getColumn(base).width     = 9;  // Venta día
    ws.getColumn(base+1).width   = 10; // Costo compra
    ws.getColumn(base+2).width   = 11; // Costo total
    ws.getColumn(base+3).width   = 10; // Precio venta
    ws.getColumn(base+4).width   = 11; // Venta total
    ws.getColumn(base+5).width   = 11; // Utilidad
  }
  const COL_TOT_VENTA = COL_DIA_START(7);    // columna totales
  const COL_TOT_UTIL  = COL_TOT_VENTA + 1;
  ws.getColumn(COL_TOT_VENTA).width = 12;
  ws.getColumn(COL_TOT_UTIL).width  = 12;

  const LAST_COL = COL_TOT_UTIL;

  let row = 1;

  // ── FILA 1: título principal ──────────────────────────────────────────────
  ws.mergeCells(row, 2, row, LAST_COL);
  const titleCell = ws.getCell(row, 2);
  titleCell.value = `REPORTE FINANCIERO SEMANAL — ${weekLabel(inicio, fin)}`;
  titleCell.alignment = { horizontal: 'center', vertical: 'middle' };
  setFont(titleCell, { bold: true, size: 13, color: C.BLANCO });
  setFill(titleCell, C.AZUL_SECCION);
  ws.getRow(row).height = 24;
  row++;

  // ── FILA 2: encabezado "DESCRIPCION" + días ───────────────────────────────
  ws.mergeCells(row, 2, row+1, 2);  // descripcion ocupa 2 filas
  headerCell(ws.getCell(row, 2), 'DESCRIPCIÓN', { bg: C.NARANJA_HEADER });

  for (let d = 0; d < 7; d++) {
    const base  = COL_DIA_START(d);
    const fecha = new Date(dias[d] + 'T12:00:00');
    const label = `${DIAS_ES[fecha.getDay()]} ${fecha.getDate()}`;
    ws.mergeCells(row, base, row, base + COLS_POR_DIA - 1);
    headerCell(ws.getCell(row, base), label, { bg: C.GRIS_SUBHEADER });
  }
  ws.mergeCells(row, COL_TOT_VENTA, row, COL_TOT_UTIL);
  headerCell(ws.getCell(row, COL_TOT_VENTA), 'TOTAL SEMANA', { bg: C.AZUL_SECCION });
  ws.getRow(row).height = 18;
  row++;

  // ── FILA 3: sub-encabezados de columna ────────────────────────────────────
  const subHeaders = ['VENTA\nDÍA', 'COSTO\nCOMPRA', 'COSTO\nTOTAL', 'PRECIO\nVENTA', 'VENTA\nTOTAL', 'UTILIDAD'];
  for (let d = 0; d < 7; d++) {
    const base = COL_DIA_START(d);
    subHeaders.forEach((h, i) => {
      headerCell(ws.getCell(row, base + i), h, { bg: C.GRIS_SUBHEADER, size: 8 });
    });
  }
  headerCell(ws.getCell(row, COL_TOT_VENTA), 'VENTA TOTAL',    { bg: C.AZUL_SECCION, size: 8 });
  headerCell(ws.getCell(row, COL_TOT_UTIL),  'UTILIDAD TOTAL', { bg: C.AZUL_SECCION, size: 8 });
  ws.getRow(row).height = 30;
  row++;

  const DATA_START_ROW = row;

  // ── FILAS DE DATOS ────────────────────────────────────────────────────────
  let grupoActual = null;
  for (const prod of productos) {
    const grp = grupos[prod];

    // Separador de grupo
    if (grp !== grupoActual) {
      grupoActual = grp;
      ws.mergeCells(row, 2, row, LAST_COL);
      const gc = ws.getCell(row, 2);
      gc.value = grp.toUpperCase();
      gc.alignment = { horizontal: 'left', vertical: 'middle' };
      setFont(gc, { bold: true, size: 9, color: C.BLANCO });
      setFill(gc, C.NARANJA_HEADER);
      applyBorder(gc);
      ws.getRow(row).height = 14;
      row++;
    }

    const isAlt = (row % 2 === 0);
    const rowBg = isAlt ? C.GRIS_CLARO : C.BLANCO;

    // Descripción
    const dc = ws.getCell(row, COL_DESC);
    dc.value = prod;
    dc.alignment = { horizontal: 'left', vertical: 'middle' };
    setFont(dc, { size: 9 });
    setFill(dc, rowBg);
    applyBorder(dc);

    let semanaVenta = 0, semanaUtil = 0;

    for (let d = 0; d < 7; d++) {
      const fecha   = dias[d];
      const base    = COL_DIA_START(d);
      const dayData = prodMap[prod][fecha];
      const vd   = dayData?.cantidad     || 0;
      const cc   = dayData?.costo_compra || 0;
      const ct   = vd * cc;
      const pv   = dayData?.precio_venta || 0;
      const vt   = dayData?.venta_total  || 0;
      const util = vt - ct;

      semanaVenta += vt;
      semanaUtil  += util;

      [vd, cc, ct, pv, vt, util].forEach((val, i) => {
        const cell = ws.getCell(row, base + i);
        if (i === 0) num(cell, val);    // cantidad
        else money(cell, val);
        cell.alignment = { horizontal: 'right', vertical: 'middle' };
        setFill(cell, rowBg);
        applyBorder(cell);
        if (i === 5 && val < 0) setFont(cell, { size: 9, color: C.ROJO_NEG });
        else setFont(cell, { size: 9 });
      });
    }

    // Total semana
    const tcv = ws.getCell(row, COL_TOT_VENTA);
    money(tcv, semanaVenta);
    tcv.alignment = { horizontal: 'right', vertical: 'middle' };
    setFill(tcv, rowBg); applyBorder(tcv);
    setFont(tcv, { bold: true, size: 9 });

    const tcu = ws.getCell(row, COL_TOT_UTIL);
    money(tcu, semanaUtil);
    tcu.alignment = { horizontal: 'right', vertical: 'middle' };
    setFill(tcu, rowBg); applyBorder(tcu);
    setFont(tcu, { bold: true, size: 9, color: semanaUtil < 0 ? C.ROJO_NEG : C.VERDE_UTIL });

    ws.getRow(row).height = 13;
    row++;
  }

  // ── FILA TOTAL GENERAL ────────────────────────────────────────────────────
  // FIX: si no hay productos (DATA_START_ROW == row), las fórmulas SUM serían
  // circulares (SUM(C4:C3)). En ese caso escribir 0 en lugar de fórmula.
  const hayDatos = row > DATA_START_ROW;

  ws.mergeCells(row, 2, row, 2);
  const totLabel = ws.getCell(row, 2);
  totLabel.value = 'TOTAL GENERAL';
  totLabel.alignment = { horizontal: 'center', vertical: 'middle' };
  setFont(totLabel, { bold: true, size: 9, color: C.BLANCO });
  setFill(totLabel, C.AZUL_SECCION); applyBorder(totLabel);

  for (let d = 0; d < 7; d++) {
    const base = COL_DIA_START(d);
    for (let i = 0; i < COLS_POR_DIA; i++) {
      const cell = ws.getCell(row, base + i);
      const colLetter = cell._column.letter;
      cell.value = hayDatos
        ? { formula: `SUM(${colLetter}${DATA_START_ROW}:${colLetter}${row - 1})` }
        : 0;
      if (i === 0) cell.numFmt = '#,##0.##';
      else         cell.numFmt = '$#,##0.00;[Red]($#,##0.00)';
      cell.alignment = { horizontal: 'right', vertical: 'middle' };
      setFill(cell, C.AMARILLO_TOTAL); applyBorder(cell);
      setFont(cell, { bold: true, size: 9 });
    }
  }

  for (const col of [COL_TOT_VENTA, COL_TOT_UTIL]) {
    const cell = ws.getCell(row, col);
    const colLetter = cell._column.letter;
    cell.value = hayDatos
      ? { formula: `SUM(${colLetter}${DATA_START_ROW}:${colLetter}${row - 1})` }
      : 0;
    cell.numFmt = '$#,##0.00;[Red]($#,##0.00)';
    cell.alignment = { horizontal: 'right', vertical: 'middle' };
    setFill(cell, C.AMARILLO_TOTAL); applyBorder(cell);
    setFont(cell, { bold: true, size: 9 });
  }

  ws.getRow(row).height = 16;
  const TOTAL_GENERAL_ROW = row; // guardar referencia para fórmulas del resumen
  row += 3;

  // ═══════════════════════════════════════════════════════════════════════════
  // SECCIÓN 2 — GASTOS MISCELÁNEOS E INSUMOS (COMPRAS)
  // ═══════════════════════════════════════════════════════════════════════════
  ws.mergeCells(row, 2, row, LAST_COL);
  const gastoTitle = ws.getCell(row, 2);
  gastoTitle.value = 'GASTOS MISCELÁNEOS E INSUMOS';
  gastoTitle.alignment = { horizontal: 'center', vertical: 'middle' };
  setFont(gastoTitle, { bold: true, size: 11, color: C.BLANCO });
  setFill(gastoTitle, C.AZUL_SECCION); applyBorder(gastoTitle);
  ws.getRow(row).height = 20;
  row++;

  // Encabezados gastos
  const GCOLS = ['PROVEEDOR / DESCRIPCIÓN', 'FECHA', 'TOTAL'];
  const GCOL_WIDTHS = [2, 10, 2]; // col indices offset from B
  const GCOL_LETTERS = ['B', 'C', 'D'];

  ws.mergeCells(row, 2, row, 9);
  headerCell(ws.getCell(row, 2), 'PROVEEDOR / DESCRIPCIÓN', { bg: C.NARANJA_HEADER });
  ws.mergeCells(row, 10, row, 11);
  headerCell(ws.getCell(row, 10), 'FECHA', { bg: C.NARANJA_HEADER });
  ws.mergeCells(row, 12, row, 14);
  headerCell(ws.getCell(row, 12), 'TOTAL', { bg: C.NARANJA_HEADER });
  ws.getRow(row).height = 14;
  row++;

  const GASTOS_START = row;
  let totalGastos = 0;

  if (compras.length === 0) {
    ws.mergeCells(row, 2, row, LAST_COL);
    const emptyCell = ws.getCell(row, 2);
    emptyCell.value = 'Sin compras registradas en el período';
    emptyCell.alignment = { horizontal: 'center', vertical: 'middle' };
    setFont(emptyCell, { size: 9, color: 'FF888888' });
    row++;
  }

  for (const c of compras) {
    const isAlt = (row % 2 === 0);
    const rowBg = isAlt ? C.GRIS_CLARO : C.BLANCO;
    const total = parseFloat(c.total) || 0;
    totalGastos += total;

    ws.mergeCells(row, 2, row, 9);
    const prov = ws.getCell(row, 2);
    prov.value = c.proveedor || '';
    prov.alignment = { horizontal: 'left', vertical: 'middle' };
    setFont(prov, { size: 9 }); setFill(prov, rowBg); applyBorder(prov);

    ws.mergeCells(row, 10, row, 11);
    const fecha = ws.getCell(row, 10);
    const fd = c.fecha instanceof Date ? c.fecha : new Date(c.fecha);
    fecha.value = `${fd.getDate().toString().padStart(2,'0')}/${(fd.getMonth()+1).toString().padStart(2,'0')}/${fd.getFullYear()}`;
    fecha.alignment = { horizontal: 'center', vertical: 'middle' };
    setFont(fecha, { size: 9 }); setFill(fecha, rowBg); applyBorder(fecha);

    ws.mergeCells(row, 12, row, 14);
    const mont = ws.getCell(row, 12);
    money(mont, total);
    mont.alignment = { horizontal: 'right', vertical: 'middle' };
    setFont(mont, { size: 9 }); setFill(mont, rowBg); applyBorder(mont);

    ws.getRow(row).height = 13;
    row++;
  }

  // Total gastos
  ws.mergeCells(row, 2, row, 11);
  const gtotLabel = ws.getCell(row, 2);
  gtotLabel.value = 'TOTAL GASTOS';
  gtotLabel.alignment = { horizontal: 'right', vertical: 'middle' };
  setFont(gtotLabel, { bold: true, size: 9, color: C.BLANCO });
  setFill(gtotLabel, C.AZUL_SECCION); applyBorder(gtotLabel);

  ws.mergeCells(row, 12, row, 14);
  const gtotMonto = ws.getCell(row, 12);
  gtotMonto.value = { formula: `SUM(N${GASTOS_START}:N${row-1})` };
  gtotMonto.numFmt = '$#,##0.00;[Red]($#,##0.00)';
  gtotMonto.alignment = { horizontal: 'right', vertical: 'middle' };
  setFont(gtotMonto, { bold: true, size: 9 });
  setFill(gtotMonto, C.AMARILLO_TOTAL); applyBorder(gtotMonto);
  ws.getRow(row).height = 16;
  const TOTAL_GASTOS_ROW = row; // guardar referencia para fórmulas del resumen
  row += 3;

  // ═══════════════════════════════════════════════════════════════════════════
  // SECCIÓN 3 — RESUMEN FINANCIERO FINAL
  // ═══════════════════════════════════════════════════════════════════════════
  ws.mergeCells(row, 2, row, LAST_COL);
  const resTitle = ws.getCell(row, 2);
  resTitle.value = 'RESUMEN FINANCIERO DE LA SEMANA';
  resTitle.alignment = { horizontal: 'center', vertical: 'middle' };
  setFont(resTitle, { bold: true, size: 11, color: C.BLANCO });
  setFill(resTitle, C.AZUL_SECCION); applyBorder(resTitle);
  ws.getRow(row).height = 20;
  row++;

  // Obtener letras de columna del total general (ventas y utilidad)
  const colLetterVenta = ws.getCell(TOTAL_GENERAL_ROW, COL_TOT_VENTA)._column.letter;
  const colLetterUtil  = ws.getCell(TOTAL_GENERAL_ROW, COL_TOT_UTIL)._column.letter;
  // Letra de columna donde está el monto de total gastos (col 12 = N en merge 12-14)
  const colLetterGastos = 'N';

  // BUG-FIX: Las filas de resumen antes tenían valor=0 (placeholder).
  // Ahora enlazan fórmulas a las celdas de totales de la sección de productos
  // y de gastos para que el resumen se calcule automáticamente.
  const resRows = [
    { label: 'TOTAL VENTAS SEMANA',      formula: `${colLetterVenta}${TOTAL_GENERAL_ROW}` },
    { label: 'TOTAL COSTO PRODUCTOS',    formula: null },  // no hay datos de costo en SR — queda 0
    { label: 'UTILIDAD BRUTA',           formula: null },  // se calcula abajo
    { label: 'TOTAL GASTOS MISCELÁNEOS', formula: `${colLetterGastos}${TOTAL_GASTOS_ROW}` },
    { label: 'UTILIDAD NETA',            formula: null },  // se calcula abajo
  ];

  const resStartRow = row;
  for (let ri = 0; ri < resRows.length; ri++) {
    const { label, formula } = resRows[ri];
    const isAlt = (row % 2 === 0);
    ws.mergeCells(row, 2, row, 14);
    const lc = ws.getCell(row, 2);
    lc.value = label;
    lc.alignment = { horizontal: 'left', vertical: 'middle' };
    setFont(lc, { bold: true, size: 9 });
    setFill(lc, isAlt ? C.GRIS_CLARO : C.BLANCO);
    applyBorder(lc);

    ws.mergeCells(row, 15, row, LAST_COL);
    const vc = ws.getCell(row, 15);
    if (formula) {
      vc.value = { formula };
    } else if (ri === 1) {
      // TOTAL COSTO PRODUCTOS: no hay costo de compra desde SR → 0
      vc.value = 0;
    } else if (ri === 2) {
      // UTILIDAD BRUTA = VENTAS - COSTO PRODUCTOS
      const vRow = resStartRow;      // fila de TOTAL VENTAS
      const cRow = resStartRow + 1;  // fila de TOTAL COSTO
      vc.value = { formula: `O${vRow}-O${cRow}` };
    } else if (ri === 4) {
      // UTILIDAD NETA = UTILIDAD BRUTA - GASTOS
      const ubRow = resStartRow + 2;  // fila de UTILIDAD BRUTA
      const gRow  = resStartRow + 3;  // fila de TOTAL GASTOS
      vc.value = { formula: `O${ubRow}-O${gRow}` };
    } else {
      vc.value = 0;
    }
    vc.numFmt = '$#,##0.00;[Red]($#,##0.00)';
    vc.alignment = { horizontal: 'right', vertical: 'middle' };
    setFont(vc, { bold: true, size: 9 });
    setFill(vc, C.AMARILLO_TOTAL); applyBorder(vc);
    ws.getRow(row).height = 14;
    row++;
  }

  // ── Freeze panes ──────────────────────────────────────────────────────────
  ws.views = [{ state: 'frozen', xSplit: 2, ySplit: 3, activeCell: 'C4' }];

  // ── Print settings ────────────────────────────────────────────────────────
  ws.pageSetup = {
    orientation: 'landscape',
    paperSize: 9,    // A4
    fitToPage: true,
    fitToWidth: 1,
    fitToHeight: 0,
  };
}

// ── FUNCIÓN PRINCIPAL exportada ───────────────────────────────────────────────
async function generarReporteSemanal(inicio, fin) {
  const pool = await getPool();
  const logger = (() => { try { return require('../utils/logger'); } catch(_) { return console; } })();

  // Normalizar: siempre 7 días lun–dom o el rango dado
  const diasArr = [];
  let cur = inicio;
  while (cur <= fin) { diasArr.push(cur); cur = addDays(cur, 1); }
  const diaUso = diasArr.slice(0, 7);  // máximo 7

  logger.info(`[ExcelSemanal] ═══ GENERANDO REPORTE ═══`);
  logger.info(`[ExcelSemanal] Rango: ${inicio} → ${fin}`);
  logger.info(`[ExcelSemanal] Días generados: ${diaUso.join(', ')}`);

  const [ventas, compras] = await Promise.all([
    getVentasPorProductoPorDia(pool, diaUso),
    getComprasSemana(pool, inicio, fin),
  ]);

  logger.info(`[ExcelSemanal] Ventas obtenidas: ${ventas.length} filas`);
  logger.info(`[ExcelSemanal] Compras obtenidas: ${compras.length} filas`);
  if (ventas.length > 0) {
    logger.info(`[ExcelSemanal] Ejemplo: ${ventas[0].producto} | ${ventas[0].fecha} | qty=${ventas[0].cantidad} | $${ventas[0].venta_total}`);
  } else {
    logger.error(`[ExcelSemanal] ⚠️ SIN DATOS DE VENTAS — el Excel saldrá vacío`);
  }

  const wb = new (getExcelJS()).Workbook();
  wb.creator  = 'ARKHON Monitor';
  wb.created  = new Date();
  wb.modified = new Date();

  const label = weekLabel(inicio, fin).replace(/ /g, '_').replace(/Á/g,'A').replace(/É/g,'E')
                                       .replace(/Í/g,'I').replace(/Ó/g,'O').replace(/Ú/g,'U');
  const ws = wb.addWorksheet(label.substring(0, 31), {
    properties: { tabColor: { argb: C.NARANJA_HEADER } },
  });

  await buildSheet(ws, inicio, fin, ventas, compras);

  return wb;
}

module.exports = { generarReporteSemanal };
