DELIMITER $$

DROP PROCEDURE IF EXISTS spu_caja_estado_sel $$
CREATE PROCEDURE spu_caja_estado_sel(IN p_suc_id INT, IN p_fecha DATE)
BEGIN
  DECLARE v_caj_id INT DEFAULT NULL;
  DECLARE v_cerradas_sin_conformidad INT DEFAULT 0;

  SELECT COUNT(*)
    INTO v_cerradas_sin_conformidad
  FROM caja
  WHERE suc_id = p_suc_id
    AND caj_estado = 'CERRADA'
    AND caj_conforme = 0;

  SELECT caj_id
    INTO v_caj_id
  FROM caja
  WHERE suc_id = p_suc_id
    AND caj_estado = 'ABIERTA'
  ORDER BY caj_fecha DESC, caj_id DESC
  LIMIT 1;

  IF v_caj_id IS NULL THEN
    SELECT caj_id
      INTO v_caj_id
    FROM caja
    WHERE suc_id = p_suc_id
      AND caj_fecha = p_fecha
    ORDER BY caj_id DESC
    LIMIT 1;
  END IF;

  IF v_caj_id IS NULL THEN
    SELECT caj_id
      INTO v_caj_id
    FROM caja
    WHERE suc_id = p_suc_id
      AND caj_estado = 'CERRADA'
      AND caj_conforme = 0
    ORDER BY caj_fecha DESC, caj_id DESC
    LIMIT 1;
  END IF;

  IF v_caj_id IS NULL THEN
    SELECT
      NULL AS caj_id,
      NULL AS suc_id,
      NULL AS caj_fecha,
      NULL AS caj_apertura,
      NULL AS caj_cierre,
      NULL AS caj_estado,
      NULL AS caj_conforme,
      NULL AS caj_fecconformidad,
      NULL AS caj_conformidad_observ,
      0.00 AS ingresos,
      0.00 AS egresos,
      0.00 AS saldo_calculado,
      v_cerradas_sin_conformidad AS cajas_cerradas_sin_conformidad,
      0 AS es_caja_abierta_de_otra_fecha,
      0 AS es_caja_cerrada_pendiente_de_otra_fecha;
  ELSE
    SELECT
      c.*,
      COALESCE(SUM(CASE WHEN cm.cam_tipo = 'INGRESO' THEN cm.cam_monto ELSE 0 END), 0) AS ingresos,
      COALESCE(SUM(CASE WHEN cm.cam_tipo = 'EGRESO' THEN cm.cam_monto ELSE 0 END), 0) AS egresos,
      c.caj_apertura + COALESCE(SUM(CASE WHEN cm.cam_tipo = 'INGRESO' THEN cm.cam_monto WHEN cm.cam_tipo = 'EGRESO' THEN -cm.cam_monto ELSE 0 END), 0) AS saldo_calculado,
      v_cerradas_sin_conformidad AS cajas_cerradas_sin_conformidad,
      CASE WHEN c.caj_estado = 'ABIERTA' AND c.caj_fecha <> p_fecha THEN 1 ELSE 0 END AS es_caja_abierta_de_otra_fecha,
      CASE WHEN c.caj_estado = 'CERRADA' AND c.caj_conforme = 0 AND c.caj_fecha <> p_fecha THEN 1 ELSE 0 END AS es_caja_cerrada_pendiente_de_otra_fecha
    FROM caja c
    LEFT JOIN caja_movimientos cm ON cm.caj_id = c.caj_id
    WHERE c.caj_id = v_caj_id
    GROUP BY c.caj_id;
  END IF;
END $$

DELIMITER ;