'use strict';
const { db } = require('../utils/dbName');

const express  = require('express');
const router   = express.Router();
const { getPool, sql } = require('../db');
const { sanitizeInt, sanitizeDate } = require('../utils/validate');
const { localDateStr } = require('../utils/localDate');

function ventasUnionSql(alias = 'm') {
  return `(
    SELECT 'C' AS origen, folio, fecha, idturno, idmesero, cancelado, pagado, total,
      nopersonas, totaldescuentos, propina
    FROM ${db()}cheques
    WHERE cancelado = 0 AND pagado = 1 AND total > 0
    UNION ALL
    SELECT 'T' AS origen, folio, fecha, idturno, idmesero, cancelado, pagado, total,
      nopersonas, totaldescuentos, propina
    FROM ${db()}tempcheques ${alias}tc
    WHERE ISNULL(${alias}tc.cancelado, 0) = 0
      AND ISNULL(${alias}tc.pagado, 0) = 1
      AND ISNULL(${alias}tc.total, 0) > 0
      AND NOT EXISTS (
        SELECT 1 FROM ${db()}cheques ${alias}ch
        WHERE ${alias}ch.folio = ${alias}tc.folio
          AND CAST(${alias}ch.fecha AS DATE) = CAST(${alias}tc.fecha AS DATE)
          AND ${alias}ch.cancelado = 0
          AND ${alias}ch.pagado = 1
      )
  )`;
}

router.get('/rendimiento', async (req, res, next) => {
  try {
    const pool    = await getPool();
    const turno   = sanitizeInt(req.query.turno);
    const fecha   = sanitizeDate(req.query.fecha);
    const desde   = sanitizeDate(req.query.fechaDesde || req.query.desde);
    const hasta   = sanitizeDate(req.query.fechaHasta || req.query.hasta);
    const request = pool.request();

    let where;
    if (turno) {
      request.input('mes_turno', sql.Int, turno);
      where = 'c.idturno = @mes_turno AND c.cancelado = 0 AND c.pagado = 1 AND c.total > 0';
    } else if (desde && hasta) {
      request.input('mes_desde', sql.VarChar(10), desde);
      request.input('mes_hasta', sql.VarChar(10), hasta);
      where = 'CAST(c.fecha AS DATE) BETWEEN CONVERT(date, @mes_desde, 23) AND CONVERT(date, @mes_hasta, 23) AND c.cancelado = 0 AND c.pagado = 1 AND c.total > 0';
    } else if (fecha) {
      request.input('mes_fecha', sql.VarChar(10), fecha);
      where = 'CAST(c.fecha AS DATE) = CONVERT(date, @mes_fecha, 23) AND c.cancelado = 0 AND c.pagado = 1 AND c.total > 0';
    } else {
      request.input('mes_hoy', sql.VarChar(10), localDateStr());
      where = 'CAST(c.fecha AS DATE) = CONVERT(date, @mes_hoy, 23) AND c.cancelado = 0 AND c.pagado = 1 AND c.total > 0';
    }

    const result = await request.query(`
      SELECT
        ISNULL(mes.nombre, 'Sin nombre')                    AS mesero,
        COUNT(DISTINCT c.folio)                             AS tickets,
        ISNULL(SUM(c.nopersonas), 0)                        AS comensales,
        ROUND(ISNULL(SUM(c.total), 0), 2)                   AS venta_total,
        ROUND(
          ISNULL(SUM(c.total), 0) /
          NULLIF(COUNT(DISTINCT c.folio), 0)
        , 2)                                                AS ticket_promedio,
        ROUND(ISNULL(SUM(c.totaldescuentos), 0), 2)         AS descuentos,
        ROUND(ISNULL(SUM(c.propina), 0), 2)                 AS propinas
      FROM ${ventasUnionSql('mes_')} c
      LEFT JOIN ${db()}meseros mes
          ON CAST(c.idmesero AS VARCHAR) = CAST(mes.idmesero AS VARCHAR)
      WHERE ${where}
      GROUP BY c.idmesero, mes.nombre
      ORDER BY venta_total DESC
    `);

    res.json({ ok: true, turno: turno || 'rango', data: result.recordset });
  } catch (err) {
    next(err);
  }
});

module.exports = router;
