'use strict';
const logger = require('../utils/logger');

const { getPool } = require('../db');
const sql         = require('mssql');
const { db }      = require('../utils/dbName');

// ─── Helpers ────────────────────────────────────────────────────────────────

function mkReq(pool, ini, fin, idturno) {
  const r = pool.request();
  if (idturno) {
    r.input('tid', sql.BigInt, parseInt(idturno));
  } else {
    r.input('ini', sql.Date, ini);
    r.input('fin', sql.Date, fin);
  }
  return r;
}

// JOIN de cheques + tempcheques para cubrir cuentas abiertas y cerradas
function unionCheques(idturno) {
  if (idturno) {
    return `(
      SELECT folio, fecha, mesa, idmesero, total, pagado, cancelado, idturno
      FROM ${db()}cheques    WHERE idturno=@tid AND cancelado=0
      UNION ALL
      SELECT folio, fecha, mesa, idmesero, total, pagado, cancelado, idturno
      FROM ${db()}tempcheques WHERE idturno=@tid AND cancelado=0
    ) AS ch`;
  }
  return `(
    SELECT folio, fecha, mesa, idmesero, total, pagado, cancelado, idturno
    FROM ${db()}cheques    WHERE cancelado=0 AND CAST(fecha AS DATE) BETWEEN @ini AND @fin
    UNION ALL
    SELECT folio, fecha, mesa, idmesero, total, pagado, cancelado, idturno
    FROM ${db()}tempcheques WHERE cancelado=0 AND CAST(fecha AS DATE) BETWEEN @ini AND @fin
  ) AS ch`;
}

// JOIN de pagos: chequespagos (cerradas) + tempchequespagos (abiertas)
function unionPagos(idturno) {
  if (idturno) {
    return `(
      SELECT cp.folio, cp.idformadepago, cp.importe, cp.propina, cp.cardBrand
      FROM ${db()}chequespagos cp
      INNER JOIN ${db()}cheques c ON cp.folio = c.folio
      WHERE c.idturno = @tid AND c.cancelado = 0
      UNION ALL
      SELECT tp.folio, tp.idformadepago, tp.importe, tp.propina, tp.cardBrand
      FROM ${db()}tempchequespagos tp
      INNER JOIN ${db()}tempcheques tc ON tp.folio = tc.folio
      WHERE tc.idturno = @tid AND tc.cancelado = 0
    ) AS p`;
  }
  return `(
    SELECT cp.folio, cp.idformadepago, cp.importe, cp.propina, cp.cardBrand
    FROM ${db()}chequespagos cp
    INNER JOIN ${db()}cheques c ON cp.folio = c.folio
    WHERE c.cancelado = 0 AND CAST(c.fecha AS DATE) BETWEEN @ini AND @fin
    UNION ALL
    SELECT tp.folio, tp.idformadepago, tp.importe, tp.propina, tp.cardBrand
    FROM ${db()}tempchequespagos tp
    INNER JOIN ${db()}tempcheques tc ON tp.folio = tc.folio
    WHERE tc.cancelado = 0 AND CAST(tc.fecha AS DATE) BETWEEN @ini AND @fin
  ) AS p`;
}

// ─── Consulta principal ─────────────────────────────────────────────────────

async function getFormasPago({ ini, fin, idturno } = {}) {
  const pool = await getPool();
  const CH   = unionCheques(idturno);
  const PG   = unionPagos(idturno);

  // 1. Resumen por forma de pago
  const rResumen = await mkReq(pool, ini, fin, idturno).query(`
    SELECT
      p.idformadepago,
      ISNULL(fp.descripcion, p.idformadepago)   AS descripcion,
      COUNT(DISTINCT p.folio)                    AS num_folios,
      ROUND(SUM(p.importe), 2)                   AS importe_total,
      ROUND(SUM(p.propina), 2)                   AS propina_total,
      ROUND(SUM(p.importe + p.propina), 2)       AS total_con_propina
    FROM ${PG}
    LEFT JOIN ${db()}formasdepago fp ON p.idformadepago = fp.idformadepago
    GROUP BY p.idformadepago, fp.descripcion
    ORDER BY importe_total DESC
  `);

  // 2. Tendencia por hora del día
  const rHoras = await mkReq(pool, ini, fin, idturno).query(`
    SELECT
      DATEPART(HOUR, ch.fecha) AS hora,
      p.idformadepago,
      ISNULL(fp.descripcion, p.idformadepago)               AS descripcion,
      ROUND(SUM(p.importe), 2)                              AS importe
    FROM ${PG}
    INNER JOIN ${CH} ON p.folio = ch.folio
    LEFT JOIN ${db()}formasdepago fp ON p.idformadepago = fp.idformadepago
    GROUP BY DATEPART(HOUR, ch.fecha),
             p.idformadepago, fp.descripcion
    ORDER BY hora, importe DESC
  `);

  // 3. Detalle folio por folio
  const rDetalle = await mkReq(pool, ini, fin, idturno).query(`
    SELECT TOP 500
      ch.folio,
      ch.fecha     AS fecha,
      ISNULL(ch.mesa, '—')                       AS mesa,
      ISNULL(ch.idmesero, '—')                   AS mesero,
      ROUND(ch.total, 2)                         AS total_cuenta,
      p.idformadepago,
      ISNULL(fp.descripcion, p.idformadepago)    AS forma_pago,
      ROUND(p.importe, 2)                        AS importe,
      ROUND(p.propina, 2)                        AS propina,
      ISNULL(p.cardBrand, '')                    AS marca_tarjeta,
      CASE WHEN ch.pagado = 1 THEN 'Pagada' ELSE 'Abierta' END AS estado
    FROM ${PG}
    INNER JOIN ${CH} ON p.folio = ch.folio
    LEFT JOIN ${db()}formasdepago fp ON p.idformadepago = fp.idformadepago
    ORDER BY ch.fecha DESC
  `);

  // 4. Totales generales
  const rTotales = await mkReq(pool, ini, fin, idturno).query(`
    SELECT
      COUNT(DISTINCT ch.folio)             AS total_folios,
      ROUND(SUM(ch.total), 2)              AS venta_total,
      ROUND(SUM(p.propina), 2)             AS propina_total,
      COUNT(DISTINCT p.idformadepago)      AS formas_usadas
    FROM ${CH}
    INNER JOIN ${PG} ON p.folio = ch.folio
  `);

  return {
    resumen:  rResumen.recordset,
    horas:    rHoras.recordset,
    detalle:  rDetalle.recordset,
    totales:  rTotales.recordset[0] || {},
    periodo:  idturno ? { tipo: 'turno', idturno } : { tipo: 'fechas', ini, fin },
    generadoEn: new Date().toISOString(),
  };
}

// ─── Catálogo de formas de pago ─────────────────────────────────────────────

async function getCatalogoFormasPago() {
  const pool = await getPool();
  try {
    const r = await pool.request().query(`
      SELECT idformadepago, descripcion, tipo, visible
      FROM ${db()}formasdepago
      WHERE visible = 1
      ORDER BY prioridadboton
    `);
    return r.recordset;
  } catch(_) { return []; }
}

module.exports = { getFormasPago, getCatalogoFormasPago };
