-- Auditoria + limpieza segura de pedidos huerfanos historicos
-- Objetivo:
-- 1) Detectar pedidos activos (a,b,c) sin detalle cobrable.
-- 2) Cerrar pedidos huerfanos en masa sin tocar pedidos validos.
-- 3) Liberar mesas solo si ya no queda otro pedido activo en esa mesa.
--
-- Recomendacion:
-- - Ejecutar primero con @modo_aplicar = 0 (solo auditoria).
-- - Revisar salida.
-- - Ejecutar luego con @modo_aplicar = 1 (aplica cambios).
--
-- Compatibilidad: MariaDB 10.6+ / MySQL 5.7+

/* ============================================================
   0) PARAMETROS
   ============================================================ */

-- 0 = solo auditoria (no cambia estados)
-- 1 = aplicar cierre masivo + liberar mesas
SET @modo_aplicar := 0;

-- Cierra solo pedidos con al menos estas horas de antiguedad.
-- Para barrido completo historico usa 0.
SET @min_horas_huerfano := 0;

-- Etiqueta de corrida para auditoria
SET @run_id := DATE_FORMAT(NOW(), '%Y%m%d%H%i%s');

/* ============================================================
   1) PRE-CHECKS
   ============================================================ */

-- A) Pedidos activos por estado
SELECT estado, COUNT(*) AS filas
FROM tm_pedido
GROUP BY estado
ORDER BY estado;

-- B) Pedidos activos de mesa sin detalle cobrable (diagnostico)
SELECT
    p.id_pedido,
    p.id_tipo_pedido,
    pm.id_mesa,
    p.estado AS estado_pedido,
    p.fecha_pedido,
    TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) AS horas_activo,
    EXISTS(
        SELECT 1
        FROM tm_venta v
        WHERE v.id_pedido = p.id_pedido
          AND v.estado = 'a'
    ) AS tiene_venta_activa
FROM tm_pedido p
LEFT JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
WHERE p.estado IN ('a','b','c')
  AND TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) >= @min_horas_huerfano
  AND NOT EXISTS (
      SELECT 1
      FROM tm_detalle_pedido dp
      WHERE dp.id_pedido = p.id_pedido
        AND dp.estado IN ('a','b','c','y')
        AND (IFNULL(dp.cantidad,0) > 0 OR IFNULL(dp.cant,0) > 0)
  )
ORDER BY p.fecha_pedido ASC;

/* ============================================================
   2) STAGING DE CANDIDATOS + RESPALDO
   ============================================================ */

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_pedidos_huerfanos_cierre;
CREATE TEMPORARY TABLE tmp_pedidos_huerfanos_cierre AS
SELECT
    p.id_pedido,
    p.id_tipo_pedido,
    pm.id_mesa,
    p.estado AS estado_anterior,
    CASE
        WHEN EXISTS(
            SELECT 1
            FROM tm_venta v
            WHERE v.id_pedido = p.id_pedido
              AND v.estado = 'a'
        ) THEN 'd'
        ELSE 'z'
    END AS estado_nuevo,
    p.fecha_pedido,
    TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) AS horas_activo,
    CASE
        WHEN EXISTS(
            SELECT 1
            FROM tm_venta v
            WHERE v.id_pedido = p.id_pedido
              AND v.estado = 'a'
        ) THEN 1
        ELSE 0
    END AS tiene_venta_activa
FROM tm_pedido p
LEFT JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
WHERE p.estado IN ('a','b','c')
  AND TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) >= @min_horas_huerfano
  AND NOT EXISTS (
      SELECT 1
      FROM tm_detalle_pedido dp
      WHERE dp.id_pedido = p.id_pedido
        AND dp.estado IN ('a','b','c','y')
        AND (IFNULL(dp.cantidad,0) > 0 OR IFNULL(dp.cant,0) > 0)
  );

-- Preview de candidatos de esta corrida
SELECT * FROM tmp_pedidos_huerfanos_cierre ORDER BY fecha_pedido ASC;

-- Tabla de auditoria persistente
CREATE TABLE IF NOT EXISTS tm_pedido_huerfano_audit (
    id_audit BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    run_id VARCHAR(14) NOT NULL,
    audit_ts DATETIME NOT NULL,
    id_pedido INT NOT NULL,
    id_tipo_pedido INT NULL,
    id_mesa INT NULL,
    estado_anterior VARCHAR(1) NOT NULL,
    estado_nuevo VARCHAR(1) NOT NULL,
    fecha_pedido DATETIME NULL,
    horas_activo INT NULL,
    tiene_venta_activa TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (id_audit),
    KEY idx_run_id (run_id),
    KEY idx_id_pedido (id_pedido)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO tm_pedido_huerfano_audit (
    run_id,
    audit_ts,
    id_pedido,
    id_tipo_pedido,
    id_mesa,
    estado_anterior,
    estado_nuevo,
    fecha_pedido,
    horas_activo,
    tiene_venta_activa
)
SELECT
    @run_id,
    NOW(),
    t.id_pedido,
    t.id_tipo_pedido,
    t.id_mesa,
    t.estado_anterior,
    t.estado_nuevo,
    t.fecha_pedido,
    t.horas_activo,
    t.tiene_venta_activa
FROM tmp_pedidos_huerfanos_cierre t;

/* ============================================================
   3) APLICACION CONTROLADA (solo si @modo_aplicar = 1)
   ============================================================ */

-- 3.1) Cerrar pedidos huerfanos:
--      - d si ya hay venta activa para ese pedido
--      - z si no hay venta activa
UPDATE tm_pedido p
INNER JOIN tmp_pedidos_huerfanos_cierre t ON t.id_pedido = p.id_pedido
SET p.estado = t.estado_nuevo
WHERE @modo_aplicar = 1
  AND p.estado IN ('a','b','c');
SET @rows_pedidos_cerrados := ROW_COUNT();

-- 3.2) Liberar mesas involucradas solo si no queda otro pedido activo en la mesa
UPDATE tm_mesa m
INNER JOIN (
    SELECT DISTINCT id_mesa
    FROM tmp_pedidos_huerfanos_cierre
    WHERE id_tipo_pedido = 1
      AND id_mesa IS NOT NULL
) x ON x.id_mesa = m.id_mesa
SET m.estado = 'a'
WHERE @modo_aplicar = 1
  AND m.estado <> 'm'
  AND NOT EXISTS (
      SELECT 1
      FROM tm_pedido p2
      INNER JOIN tm_pedido_mesa pm2 ON pm2.id_pedido = p2.id_pedido
      WHERE pm2.id_mesa = m.id_mesa
        AND p2.estado IN ('a','b','c')
  );
SET @rows_mesas_liberadas := ROW_COUNT();

-- Resumen de corrida
SELECT
    @run_id AS run_id,
    @modo_aplicar AS modo_aplicar,
    COUNT(*) AS candidatos,
    SUM(CASE WHEN estado_nuevo = 'd' THEN 1 ELSE 0 END) AS candidatos_a_d,
    SUM(CASE WHEN estado_nuevo = 'z' THEN 1 ELSE 0 END) AS candidatos_a_z,
    @rows_pedidos_cerrados AS pedidos_cerrados,
    @rows_mesas_liberadas AS mesas_liberadas
FROM tmp_pedidos_huerfanos_cierre;

-- Confirmacion transaccional
-- Si @modo_aplicar = 1, se persisten cambios.
-- Si @modo_aplicar = 0, se revierte para dejar solo auditoria externa (SELECT).
-- Cambia ROLLBACK por COMMIT si quieres guardar la auditoria aun en modo preview.
SET @sql_tx := IF(@modo_aplicar = 1, 'COMMIT', 'ROLLBACK');
PREPARE stmt_tx FROM @sql_tx;
EXECUTE stmt_tx;
DEALLOCATE PREPARE stmt_tx;

/* ============================================================
   4) POST-CHECKS
   ============================================================ */

-- A) Debe tender a cero si aplicaste el cierre masivo
SELECT
    p.id_pedido,
    p.id_tipo_pedido,
    p.estado,
    p.fecha_pedido
FROM tm_pedido p
WHERE p.estado IN ('a','b','c')
  AND TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) >= @min_horas_huerfano
  AND NOT EXISTS (
      SELECT 1
      FROM tm_detalle_pedido dp
      WHERE dp.id_pedido = p.id_pedido
        AND dp.estado IN ('a','b','c','y')
        AND (IFNULL(dp.cantidad,0) > 0 OR IFNULL(dp.cant,0) > 0)
  )
ORDER BY p.fecha_pedido ASC;

-- B) Mesas con mas de 1 pedido activo (debe ser 0 filas)
SELECT pm.id_mesa, COUNT(*) AS activos
FROM tm_pedido p
INNER JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
WHERE p.estado IN ('a','b','c')
GROUP BY pm.id_mesa
HAVING COUNT(*) > 1;

-- C) Ultima corrida auditada (si se ejecuto en modo aplicar)
SELECT *
FROM tm_pedido_huerfano_audit
WHERE run_id = @run_id
ORDER BY id_audit;

/* ============================================================
   5) RECUPERACION (manual, solo referencia)
   ============================================================
   -- Para revertir una corrida concreta:
   START TRANSACTION;
   UPDATE tm_pedido p
   INNER JOIN tm_pedido_huerfano_audit a
       ON a.id_pedido = p.id_pedido
      AND a.run_id = 'YYYYMMDDHHMMSS'
   SET p.estado = a.estado_anterior;
   COMMIT;
*/
