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

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

function tipoLabel(tipo, pagodepropina = 0) {
  if (Number(pagodepropina) === 1 || Number(tipo) === 3) return 'propina';
  if (Number(tipo) === 2) return 'deposito';
  if (Number(tipo) === 1) return 'retiro';
  return 'otro';
}

function whereByTurno(req, idturno) {
  req.input('tid', sql.Int, parseInt(idturno));
  return 'm.idturno = @tid AND ISNULL(m.cancelado, 0) = 0';
}
function whereByFecha(req, inicio, fin) {
  // No usar sql.Date con useUTC=false: el driver desplaza YYYY-MM-DD al dia
  // anterior. Conservar la fecha como texto ISO y convertirla dentro de SQL.
  req.input('ini', sql.VarChar(10), inicio);
  req.input('fin', sql.VarChar(10), fin);
  return "CAST(m.fecha AS DATE) BETWEEN CONVERT(date,@ini,23) AND CONVERT(date,@fin,23) AND ISNULL(m.cancelado, 0) = 0";
}

async function getResumen(params) {
  try {

  const pool = await getPool();
  const req  = pool.request();
  const w    = params.idturno
    ? whereByTurno(req, params.idturno)
    : whereByFecha(req, params.inicio, params.fin);

  
  const r = await req.query(`
    SELECT
      COUNT(*)                                                          AS total_movimientos,
      ISNULL(SUM(CASE WHEN m.tipo=1 AND ISNULL(m.pagodepropina,0)=0 THEN m.importe ELSE 0 END), 0) AS total_retiros,
      ISNULL(SUM(CASE WHEN m.tipo=2 AND ISNULL(m.pagodepropina,0)=0 THEN m.importe ELSE 0 END), 0) AS total_depositos,
      ISNULL(SUM(CASE WHEN m.tipo=3 OR ISNULL(m.pagodepropina,0)=1 THEN m.importe ELSE 0 END), 0) AS total_propinas
    FROM ${db()}movtoscaja m
    WHERE ${w}
  `);
  return r.recordset[0];
  } catch(_) { return {}; }
}

async function getPorConcepto(params) {
  try {

  const pool = await getPool();
  const req  = pool.request();
  const w    = params.idturno
    ? whereByTurno(req, params.idturno)
    : whereByFecha(req, params.inicio, params.fin);

  const r = await req.query(`
    SELECT TOP 40
      LTRIM(RTRIM(ISNULL(m.concepto, 'Sin concepto'))) AS concepto,
      m.tipo,
      MAX(CAST(ISNULL(m.pagodepropina,0) AS INT)) AS pagodepropina,
      COUNT(*)             AS cantidad,
      SUM(m.importe)       AS importe_total
    FROM ${db()}movtoscaja m
    WHERE ${w}
    GROUP BY LTRIM(RTRIM(ISNULL(m.concepto, 'Sin concepto'))), m.tipo
    ORDER BY importe_total DESC
  `);
  return r.recordset.map(row => ({
    ...row,
    tipo_label: tipoLabel(row.tipo, row.pagodepropina),
  }));
  } catch(_) { return []; }
}

async function getPorUsuario(params) {
  try {

  const pool = await getPool();
  const req  = pool.request();
  const w    = params.idturno
    ? whereByTurno(req, params.idturno)
    : whereByFecha(req, params.inicio, params.fin);

  const r = await req.query(`
    SELECT
      ISNULL(NULLIF(LTRIM(RTRIM(m.usuarioautoriza)), ''), 'Sin registro') AS usuario,
      COUNT(*)       AS cantidad,
      SUM(m.importe) AS importe_total
    FROM ${db()}movtoscaja m
    WHERE ${w}
    GROUP BY ISNULL(NULLIF(LTRIM(RTRIM(m.usuarioautoriza)), ''), 'Sin registro')
    ORDER BY importe_total DESC
  `);
  return r.recordset;
  } catch(_) { return []; }
}

async function getMovimientos(params) {
  try {

  const pool = await getPool();
  const req  = pool.request();
  const w    = params.idturno
    ? whereByTurno(req, params.idturno)
    : whereByFecha(req, params.inicio, params.fin);

  const r = await req.query(`
    SELECT TOP 500
      m.folio, m.foliomovto, m.idturno,
      LTRIM(RTRIM(ISNULL(m.concepto,   'Sin concepto'))) AS concepto,
      LTRIM(RTRIM(ISNULL(m.referencia, '')))             AS referencia,
      m.importe,
      m.fecha AS fecha,
      m.tipo,
      m.pagodepropina,
      m.IdCashMovementType,
      ISNULL(NULLIF(LTRIM(RTRIM(m.usuarioautoriza)), ''), 'Sin registro') AS usuario
    FROM ${db()}movtoscaja m
    WHERE ${w}
    ORDER BY m.fecha DESC
  `);

  const movimientos = r.recordset.map(row => ({
    ...row,
    tipo_label: tipoLabel(row.tipo, row.pagodepropina),
  }));

  return { movimientos, paginacion: { total: movimientos.length } };
  } catch(_) { return []; }
}

async function getEnVivo() {
  const pool = await getPool();

  const [rMovs, rRes] = await Promise.all([
    pool.request().query(`
      SELECT TOP 200
        m.folio, m.foliomovto, m.idturno,
        LTRIM(RTRIM(ISNULL(m.concepto,   'Sin concepto'))) AS concepto,
        LTRIM(RTRIM(ISNULL(m.referencia, '')))             AS referencia,
        m.importe,
        m.fecha AS fecha,
        m.tipo,
        m.pagodepropina,
        m.IdCashMovementType,
        ISNULL(NULLIF(LTRIM(RTRIM(m.usuarioautoriza)), ''), 'Sin registro') AS usuario
      FROM ${db()}movtoscaja m
      WHERE ISNULL(m.cancelado,0)=0
        AND CAST(m.fecha AS DATE) = CAST(GETDATE() AS DATE)
      ORDER BY m.fecha DESC
    `),
    pool.request().query(`
      SELECT
        COUNT(*) AS total_hoy,
        ISNULL(SUM(CASE WHEN m.tipo=1 AND ISNULL(m.pagodepropina,0)=0 THEN m.importe ELSE 0 END),0) AS total_retiros_hoy,
        ISNULL(SUM(CASE WHEN m.tipo=2 AND ISNULL(m.pagodepropina,0)=0 THEN m.importe ELSE 0 END),0) AS total_depositos_hoy,
        ISNULL(SUM(CASE WHEN m.tipo=3 OR ISNULL(m.pagodepropina,0)=1 THEN m.importe ELSE 0 END),0) AS total_propinas_hoy,
        GETDATE() AS hora_sql
      FROM ${db()}movtoscaja m
      WHERE ISNULL(m.cancelado,0)=0
        AND CAST(m.fecha AS DATE) = CAST(GETDATE() AS DATE)
    `),
  ]);

  const movimientos = rMovs.recordset.map(row => ({
    ...row,
    tipo_label: tipoLabel(row.tipo, row.pagodepropina),
    movement_key: `${row.idturno || 0}:${row.foliomovto || row.folio}`,
  }));

  return {
    movimientos,
    resumen_hoy: rRes.recordset[0],
    generadoEn:  new Date().toISOString(),
    sourceNow: rRes.recordset[0]?.hora_sql || null,
  };
}

module.exports = { getResumen, getPorConcepto, getPorUsuario, getMovimientos, getEnVivo };
