-- Hotfix: blindaje de mesas para evitar pedidos activos duplicados
-- Base esperada: MySQL/MariaDB con tablas tm_mesa, tm_pedido, tm_pedido_mesa
-- Recomendado: ejecutar primero en staging y luego en produccion.

/* ============================================================
   0) PRE-CHECKS (SOLO LECTURA)
   ============================================================ */

-- A) Mesas fisicas duplicadas por numero (debe retornar 0 filas)
SELECT id_salon, nro_mesa, COUNT(*) AS c
FROM tm_mesa
GROUP BY id_salon, nro_mesa
HAVING c > 1;

-- B) Mas de un pedido activo por misma mesa (debe retornar 0 filas)
SELECT pm.id_mesa, m.nro_mesa, COUNT(*) AS c
FROM tm_pedido p
INNER JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
INNER JOIN tm_mesa m ON m.id_mesa = pm.id_mesa
WHERE p.estado = 'a'
GROUP BY pm.id_mesa, m.nro_mesa
HAVING c > 1;

-- C) Pedidos activos de mesa sin detalle (diagnostico de "mesa colgada")
SELECT
    p.id_pedido,
    pm.id_mesa,
    m.nro_mesa,
    p.fecha_pedido,
    TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) AS horas_activo,
    IFNULL(d.cnt, 0) AS items
FROM tm_pedido p
INNER JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
INNER JOIN tm_mesa m ON m.id_mesa = pm.id_mesa
LEFT JOIN (
    SELECT id_pedido, COUNT(*) AS cnt
    FROM tm_detalle_pedido
    WHERE estado <> 'z'
    GROUP BY id_pedido
) d ON d.id_pedido = p.id_pedido
WHERE p.estado = 'a'
  AND IFNULL(d.cnt, 0) = 0
ORDER BY p.fecha_pedido ASC;

/* ============================================================
   1) LIMPIEZA OPCIONAL (DESCOMENTAR SOLO SI YA REVISASTE)
   Cierra pedidos activos antiguos y deja solo el mas reciente por mesa.
   ============================================================ */
/*
START TRANSACTION;

UPDATE tm_pedido p
INNER JOIN (
    SELECT pm.id_mesa, p.id_pedido
    FROM tm_pedido_mesa pm
    INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
    INNER JOIN (
        SELECT pm2.id_mesa, MAX(p2.id_pedido) AS id_pedido_keep
        FROM tm_pedido_mesa pm2
        INNER JOIN tm_pedido p2 ON p2.id_pedido = pm2.id_pedido
        WHERE p2.estado = 'a'
        GROUP BY pm2.id_mesa
        HAVING COUNT(*) > 1
    ) k ON k.id_mesa = pm.id_mesa
    WHERE p.estado = 'a'
      AND p.id_pedido <> k.id_pedido_keep
) old ON old.id_pedido = p.id_pedido
SET p.estado = 'z';

UPDATE tm_mesa m
LEFT JOIN (
    SELECT pm.id_mesa
    FROM tm_pedido_mesa pm
    INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
    WHERE p.estado = 'a'
    GROUP BY pm.id_mesa
) a ON a.id_mesa = m.id_mesa
SET m.estado = CASE WHEN a.id_mesa IS NULL THEN 'a' ELSE 'i' END
WHERE m.estado <> 'm';

COMMIT;
*/

/* ============================================================
   1B) LIMPIEZA OPCIONAL DE PEDIDOS ACTIVOS SIN DETALLE
   Cierra solo pedidos de mesa activos sin items y muy antiguos.
   ============================================================ */
/*
-- Ajusta el umbral (en horas) antes de ejecutar:
SET @horas_huerfano := 6;

START TRANSACTION;

UPDATE tm_pedido p
INNER JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
LEFT JOIN (
    SELECT id_pedido, COUNT(*) AS cnt
    FROM tm_detalle_pedido
    WHERE estado <> 'z'
    GROUP BY id_pedido
) d ON d.id_pedido = p.id_pedido
SET p.estado = 'z'
WHERE p.estado = 'a'
  AND IFNULL(d.cnt, 0) = 0
  AND TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) >= @horas_huerfano;

UPDATE tm_mesa m
LEFT JOIN (
    SELECT pm.id_mesa
    FROM tm_pedido p
    INNER JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
    WHERE p.estado = 'a'
    GROUP BY pm.id_mesa
) a ON a.id_mesa = m.id_mesa
SET m.estado = CASE WHEN a.id_mesa IS NULL THEN 'a' ELSE 'i' END
WHERE m.estado <> 'm';

COMMIT;
*/

/* ============================================================
   2) VIEW BLINDADA: v_listar_mesas
   Devuelve maximo 1 pedido activo por mesa (el mas reciente).
   ============================================================ */

DROP VIEW IF EXISTS `v_listar_mesas`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `v_listar_mesas` AS
SELECT
    vm.id_mesa AS id_mesa,
    vm.forma AS forma,
    vm.id_salon AS id_salon,
    vm.nro_mesa AS nro_mesa,
    vm.estado AS estado,
    vm.desc_salon AS desc_salon,
    pa.id_pedido AS id_pedido,
    pa.fecha_pedido AS fecha_pedido,
    pa.nro_personas AS nro_personas
FROM v_mesas vm
LEFT JOIN (
    SELECT
        pm.id_mesa AS id_mesa,
        p.id_pedido AS id_pedido,
        p.fecha_pedido AS fecha_pedido,
        pm.nro_personas AS nro_personas
    FROM tm_pedido_mesa pm
    INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
    INNER JOIN (
        SELECT
            pm2.id_mesa AS id_mesa,
            MAX(p2.id_pedido) AS id_pedido
        FROM tm_pedido_mesa pm2
        INNER JOIN tm_pedido p2 ON p2.id_pedido = pm2.id_pedido
        WHERE p2.estado = 'a'
        GROUP BY pm2.id_mesa
    ) x ON x.id_mesa = pm.id_mesa
       AND x.id_pedido = p.id_pedido
) pa ON pa.id_mesa = vm.id_mesa
ORDER BY vm.nro_mesa;

/* ============================================================
   3) PROCEDURE BLINDADA: usp_restRegMesa
   Evita abrir pedido si ya existe pedido activo para la misma mesa.
   Usa transaccion para evitar condicion de carrera.
   ============================================================ */

DROP PROCEDURE IF EXISTS `usp_restRegMesa`;
DELIMITER //
CREATE PROCEDURE `usp_restRegMesa`(
    IN `_flag` INT(11),
    IN `_id_tipo_pedido` INT(11),
    IN `_id_apc` INT(11),
    IN `_id_usu` INT(11),
    IN `_fecha_pedido` DATETIME,
    IN `_id_mesa` INT(11),
    IN `_id_mozo` INT(11),
    IN `_nomb_cliente` VARCHAR(45),
    IN `_nro_personas` INT(11),
    IN `_id_cliente` INT(11)
)
BEGIN
    DECLARE _mesa_disponible INT DEFAULT 0;
    DECLARE _pedido_activo INT DEFAULT 0;
    DECLARE _id_nuevo INT DEFAULT NULL;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 0 AS fil, NULL AS id_pedido;
    END;

    IF _flag = 1 THEN
        START TRANSACTION;

        SELECT COUNT(*) INTO _mesa_disponible
        FROM tm_mesa
        WHERE id_mesa = _id_mesa
          AND estado = 'a'
        FOR UPDATE;

        SELECT COUNT(*) INTO _pedido_activo
        FROM tm_pedido_mesa pm
        INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
        WHERE pm.id_mesa = _id_mesa
          AND p.estado = 'a'
        FOR UPDATE;

        IF _mesa_disponible = 1 AND _pedido_activo = 0 THEN
            INSERT INTO tm_pedido (id_tipo_pedido, id_apc, id_usu, fecha_pedido, estado)
            VALUES (_id_tipo_pedido, _id_apc, _id_usu, _fecha_pedido, 'a');

            SET _id_nuevo = LAST_INSERT_ID();

            INSERT INTO tm_pedido_mesa (id_pedido, id_mesa, id_mozo, nomb_cliente, id_cliente, nro_personas)
            VALUES (_id_nuevo, _id_mesa, _id_mozo, _nomb_cliente, _id_cliente, _nro_personas);

            UPDATE tm_mesa
            SET estado = 'i'
            WHERE id_mesa = _id_mesa;

            COMMIT;
            SELECT 1 AS fil, _id_nuevo AS id_pedido;
        ELSE
            ROLLBACK;
            SELECT 0 AS fil, NULL AS id_pedido;
        END IF;
    END IF;
END//
DELIMITER ;

/* ============================================================
   4) PROCEDURE BLINDADA: usp_restOpcionesMesa
   No toma id_pedido ambiguo desde v_listar_mesas.
   Selecciona explicitamente el pedido activo mas reciente por mesa.
   ============================================================ */

DROP PROCEDURE IF EXISTS `usp_restOpcionesMesa`;
DELIMITER //
CREATE PROCEDURE `usp_restOpcionesMesa`(
    IN `_flag` INT(11),
    IN `_cod_mesa_origen` INT(11),
    IN `_cod_mesa_destino` INT(11)
)
BEGIN
    DECLARE _filtro INT DEFAULT 0;
    DECLARE _destino_libre INT DEFAULT 0;
    DECLARE _destino_ocupado INT DEFAULT 0;
    DECLARE _cod_1 INT DEFAULT NULL;
    DECLARE _cod_2 INT DEFAULT NULL;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 0 AS cod;
    END;

    IF _flag = 1 THEN
        START TRANSACTION;

        SELECT COUNT(*) INTO _filtro
        FROM tm_mesa
        WHERE id_mesa = _cod_mesa_origen
          AND estado = 'i'
        FOR UPDATE;

        SELECT COUNT(*) INTO _destino_libre
        FROM tm_mesa
        WHERE id_mesa = _cod_mesa_destino
          AND estado = 'a'
        FOR UPDATE;

        SELECT COUNT(*) INTO _destino_ocupado
        FROM tm_pedido_mesa pm
        INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
        WHERE pm.id_mesa = _cod_mesa_destino
          AND p.estado = 'a'
        FOR UPDATE;

        SET _cod_1 = NULL;
        SELECT pm.id_pedido INTO _cod_1
        FROM tm_pedido_mesa pm
        INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
        WHERE pm.id_mesa = _cod_mesa_origen
          AND p.estado = 'a'
        ORDER BY p.id_pedido DESC
        LIMIT 1;

        IF _filtro = 1 AND _destino_libre = 1 AND _destino_ocupado = 0 AND _cod_1 IS NOT NULL THEN
            UPDATE tm_pedido_mesa
            SET id_mesa = _cod_mesa_destino
            WHERE id_pedido = _cod_1;

            UPDATE tm_mesa
            SET estado = 'a'
            WHERE id_mesa = _cod_mesa_origen;

            UPDATE tm_mesa
            SET estado = 'i'
            WHERE id_mesa = _cod_mesa_destino;

            COMMIT;
            SELECT 1 AS cod;
        ELSE
            ROLLBACK;
            SELECT 0 AS cod;
        END IF;
    END IF;

    IF _flag = 2 THEN
        START TRANSACTION;

        SELECT COUNT(*) INTO _filtro
        FROM tm_mesa
        WHERE id_mesa = _cod_mesa_origen
          AND estado = 'i'
        FOR UPDATE;

        SET _cod_1 = NULL;
        SET _cod_2 = NULL;

        SELECT pm.id_pedido INTO _cod_1
        FROM tm_pedido_mesa pm
        INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
        WHERE pm.id_mesa = _cod_mesa_origen
          AND p.estado = 'a'
        ORDER BY p.id_pedido DESC
        LIMIT 1;

        SELECT pm.id_pedido INTO _cod_2
        FROM tm_pedido_mesa pm
        INNER JOIN tm_pedido p ON p.id_pedido = pm.id_pedido
        WHERE pm.id_mesa = _cod_mesa_destino
          AND p.estado = 'a'
        ORDER BY p.id_pedido DESC
        LIMIT 1;

        IF _filtro = 1 AND _cod_1 IS NOT NULL AND _cod_2 IS NOT NULL THEN
            UPDATE tm_detalle_pedido
            SET id_pedido = _cod_2
            WHERE id_pedido = _cod_1;

            IF _cod_mesa_origen = _cod_mesa_destino THEN
                UPDATE tm_mesa
                SET estado = 'i'
                WHERE id_mesa = _cod_mesa_origen;
            ELSE
                UPDATE tm_mesa
                SET estado = 'a'
                WHERE id_mesa = _cod_mesa_origen;

                UPDATE tm_pedido
                SET estado = 'z'
                WHERE id_pedido = _cod_1;
            END IF;

            COMMIT;
            SELECT 1 AS cod;
        ELSE
            ROLLBACK;
            SELECT 0 AS cod;
        END IF;
    END IF;
END//
DELIMITER ;

/* ============================================================
   5) POST-CHECKS
   ============================================================ */

-- Debe devolver 0 filas
SELECT pm.id_mesa, m.nro_mesa, COUNT(*) AS c
FROM tm_pedido p
INNER JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
INNER JOIN tm_mesa m ON m.id_mesa = pm.id_mesa
WHERE p.estado = 'a'
GROUP BY pm.id_mesa, m.nro_mesa
HAVING c > 1;

-- Debe devolver una sola fila por mesa en la vista
SELECT id_mesa, nro_mesa, COUNT(*) AS c
FROM v_listar_mesas
GROUP BY id_mesa, nro_mesa
HAVING c > 1;

-- Si aun aparecen filas aqui, son mesas activas sin detalle (revisar bloque 1B)
SELECT
    p.id_pedido,
    pm.id_mesa,
    m.nro_mesa,
    p.fecha_pedido,
    TIMESTAMPDIFF(HOUR, p.fecha_pedido, NOW()) AS horas_activo
FROM tm_pedido p
INNER JOIN tm_pedido_mesa pm ON pm.id_pedido = p.id_pedido
INNER JOIN tm_mesa m ON m.id_mesa = pm.id_mesa
LEFT JOIN (
    SELECT id_pedido, COUNT(*) AS cnt
    FROM tm_detalle_pedido
    WHERE estado <> 'z'
    GROUP BY id_pedido
) d ON d.id_pedido = p.id_pedido
WHERE p.estado = 'a'
  AND IFNULL(d.cnt, 0) = 0
ORDER BY p.fecha_pedido ASC;
