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

const express    = require('express');
const router     = express.Router();
const { getPool, sql } = require('../db');

// GET /api/mesas  — mesas abiertas (excluye domicilios/para llevar)
router.get('/', async (req, res, next) => {
  try {
    const pool = await getPool();
    const result = await pool.request().query(`
      SELECT
        LTRIM(RTRIM(ISNULL(tc.mesa, '')))     AS mesa,
        tc.folio                              AS foliocuenta,
        ISNULL(mes.nombre, 'Sin asignar')     AS nombre_mesero,
        tc.fecha                              AS apertura,
        ROUND(ISNULL(tc.total, 0), 2)         AS total,
        ROUND(ISNULL(tc.total, 0), 2)         AS subtotal,
        CAST(ISNULL(tc.impreso, 0) AS INT)    AS impreso,
        CAST(ISNULL(tc.pagado, 0) AS INT)     AS pagado,
        DATEDIFF(MINUTE, tc.fecha, GETDATE()) AS minutos_abierta,
        (SELECT COUNT(DISTINCT td2.comanda)
         FROM ${db()}tempcheqdet td2
         WHERE td2.foliodet = tc.folio
           AND td2.modificador = 0
           AND ISNULL(td2.comanda,'') <> '') AS comandas,
        tc.nopersonas AS comensales
      FROM ${db()}tempcheques tc
      LEFT JOIN ${db()}meseros mes
        ON CAST(tc.idmesero AS VARCHAR) = CAST(mes.idmesero AS VARCHAR)
      WHERE tc.pagado = 0 AND tc.cancelado = 0
        AND ${sqlExcluirDomicilios('tc')}
      ORDER BY tc.fecha
    `);
    res.json({ ok: true, data: result.recordset });
  } catch (err) { next(err); }   
});

// GET /api/mesas/:folio/detalle  — detalle de productos de una mesa abierta
// Conciliacion de cuentas abiertas, impresas y pagadas del dia calendario SQL.
// Se mantiene GET /api/mesas como arreglo para no romper clientes existentes.
router.get('/estado-hoy', async (_req, res, next) => {
  try {
    const pool = await getPool();
    const [abiertasResult, pagadasResult, contextoResult] = await Promise.all([
      pool.request().query(`
        SELECT
          'T' AS origen,
          tc.folio,
          tc.numcheque,
          LTRIM(RTRIM(ISNULL(tc.mesa, ''))) AS mesa,
          ISNULL(mes.nombre, 'Sin asignar') AS nombre_mesero,
          tc.fecha,
          tc.idturno,
          ROUND(ISNULL(tc.total, 0), 2) AS total,
          CAST(ISNULL(tc.impreso, 0) AS INT) AS impreso,
          CAST(ISNULL(tc.pagado, 0) AS INT) AS pagado,
          CAST(ISNULL(tc.cancelado, 0) AS INT) AS cancelado,
          DATEDIFF(MINUTE, tc.fecha, GETDATE()) AS minutos_abierta
        FROM ${db()}tempcheques tc
        LEFT JOIN ${db()}meseros mes
          ON CAST(tc.idmesero AS VARCHAR) = CAST(mes.idmesero AS VARCHAR)
        WHERE ISNULL(tc.pagado, 0) = 0
          AND ISNULL(tc.cancelado, 0) = 0
          AND ${sqlExcluirDomicilios('tc')}
          AND CAST(tc.fecha AS DATE) = CAST(GETDATE() AS DATE)
        ORDER BY tc.fecha
      `),
      pool.request().query(`
        SELECT TOP 100 p.*
        FROM (
          SELECT
            'C' AS origen,
            c.folio,
            c.numcheque,
            LTRIM(RTRIM(ISNULL(c.mesa, ''))) AS mesa,
            ISNULL(mes.nombre, 'Sin asignar') AS nombre_mesero,
            c.fecha,
            c.idturno,
            ROUND(ISNULL(c.total, 0), 2) AS total,
            CAST(ISNULL(c.impreso, 0) AS INT) AS impreso,
            CAST(ISNULL(c.pagado, 0) AS INT) AS pagado,
            CAST(ISNULL(c.cancelado, 0) AS INT) AS cancelado
          FROM ${db()}cheques c
          LEFT JOIN ${db()}meseros mes
            ON CAST(c.idmesero AS VARCHAR) = CAST(mes.idmesero AS VARCHAR)
          WHERE c.pagado = 1 AND c.cancelado = 0 AND c.total > 0
            AND CAST(c.fecha AS DATE) = CAST(GETDATE() AS DATE)

          UNION ALL

          SELECT
            'T' AS origen,
            tc.folio,
            tc.numcheque,
            LTRIM(RTRIM(ISNULL(tc.mesa, ''))) AS mesa,
            ISNULL(mes.nombre, 'Sin asignar') AS nombre_mesero,
            tc.fecha,
            tc.idturno,
            ROUND(ISNULL(tc.total, 0), 2) AS total,
            CAST(ISNULL(tc.impreso, 0) AS INT) AS impreso,
            CAST(ISNULL(tc.pagado, 0) AS INT) AS pagado,
            CAST(ISNULL(tc.cancelado, 0) AS INT) AS cancelado
          FROM ${db()}tempcheques tc
          LEFT JOIN ${db()}meseros mes
            ON CAST(tc.idmesero AS VARCHAR) = CAST(mes.idmesero AS VARCHAR)
          WHERE tc.pagado = 1 AND tc.cancelado = 0 AND tc.total > 0
            AND CAST(tc.fecha AS DATE) = CAST(GETDATE() AS DATE)
            AND NOT EXISTS (
              SELECT 1
              FROM ${db()}cheques c
              WHERE c.folio = tc.folio
                AND CAST(c.fecha AS DATE) = CAST(tc.fecha AS DATE)
                AND c.pagado = 1 AND c.cancelado = 0
            )
        ) p
        ORDER BY p.fecha DESC
      `),
      pool.request().query(`
        SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS fecha,
          GETDATE() AS hora_sql, @@SERVERNAME AS servidor, DB_NAME() AS base
      `),
    ]);

    const abiertas = abiertasResult.recordset || [];
    const pagadas = pagadasResult.recordset || [];
    const resumen = {
      abiertas: abiertas.length,
      abiertas_sin_imprimir: abiertas.filter(c => Number(c.impreso) !== 1).length,
      impresas_pendientes: abiertas.filter(c => Number(c.impreso) === 1).length,
      pagadas: pagadas.length,
      pagadas_impresas: pagadas.filter(c => Number(c.impreso) === 1).length,
      total_en_curso: Math.round(abiertas.reduce((s, c) => s + Number(c.total || 0), 0) * 100) / 100,
      total_pagado: Math.round(pagadas.reduce((s, c) => s + Number(c.total || 0), 0) * 100) / 100,
    };

    res.json({ ok: true, data: {
      contexto: contextoResult.recordset[0] || {},
      resumen,
      abiertas,
      pagadas,
    } });
  } catch (err) { next(err); }
});

router.get('/:folio/detalle', async (req, res, next) => {
  try {
    const pool  = await getPool();
    
    const result = await pool.request()
      .input('folio', sql.Int, parseInt(req.params.folio) || 0)
      .query(`
        SELECT
          td.idproducto,
          ISNULL(p.descripcion, CAST(td.idproducto AS VARCHAR)) AS producto,
          ISNULL(g.descripcion, 'Sin grupo')                    AS grupo,
          ROUND(td.cantidad, 0)                                 AS cantidad,
          ROUND(td.precio, 2)                                   AS precio_unitario,
          ROUND(td.cantidad * td.precio, 2)                     AS subtotal,
          ROUND(td.descuento, 2)                                AS descuento,
          td.hora,
          td.comentario,
          td.modificador
        FROM ${db()}tempcheqdet td
        LEFT JOIN ${db()}productos p
          ON CAST(td.idproducto AS VARCHAR) = CAST(p.idproducto AS VARCHAR)
        LEFT JOIN ${db()}grupos g ON p.idgrupo = g.idgrupo
        WHERE td.foliodet = @folio AND td.modificador = 0
        ORDER BY td.hora
      `);
    res.json({ ok: true, data: result.recordset });
  } catch (err) { next(err); }   
});

module.exports = router;
