DELIMITER $$

DROP PROCEDURE IF EXISTS spu_atencion_pagar $$
CREATE PROCEDURE spu_atencion_pagar(IN p_atn_id INT, IN p_pagos JSON)
BEGIN
  DECLARE v_suc_id INT;
  DECLARE v_cli_id INT;
  DECLARE v_caj_id INT;
  DECLARE v_ven_id INT;
  DECLARE v_total DECIMAL(12,2);
  DECLARE v_total_pagos DECIMAL(12,2);
  DECLARE v_estado VARCHAR(20);
  DECLARE v_tip_servicio INT;
  DECLARE v_mpa_principal INT;

  SELECT suc_id, cli_id, atn_estado
    INTO v_suc_id, v_cli_id, v_estado
  FROM atenciones
  WHERE atn_id = p_atn_id
  LIMIT 1;

  IF v_estado IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Atencion no encontrada';
  END IF;

  IF v_estado = 'PAGADO' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La atencion ya fue pagada';
  END IF;

  IF v_estado = 'ANULADO' THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No se puede pagar una atencion anulada';
  END IF;

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

  IF v_caj_id IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Debe abrir caja antes de cobrar';
  END IF;

  SELECT COALESCE(SUM(ads_total), 0)
    INTO v_total
  FROM atencion_detalle_servicios
  WHERE atn_id = p_atn_id
    AND ads_estado <> 'ANULADO';

  SELECT COALESCE(SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(p_pagos, CONCAT('$[', n.n, '].monto'))) AS DECIMAL(12,2))), 0)
    INTO v_total_pagos
  FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) n
  WHERE n.n < JSON_LENGTH(p_pagos);

  IF ROUND(v_total, 2) <> ROUND(v_total_pagos, 2) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La suma de pagos debe ser igual al total de la atencion';
  END IF;

  SET v_mpa_principal = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_pagos, '$[0].mpa_id')) AS UNSIGNED);
  SELECT tip_id INTO v_tip_servicio FROM tipo_item WHERE tip_nombre = 'SERVICIO' LIMIT 1;

  INSERT INTO ventas(suc_id, cli_id, mpa_id, ven_subtotal, ven_total, ven_estado)
  VALUES(v_suc_id, v_cli_id, v_mpa_principal, v_total, v_total, 'REGISTRADO');
  SET v_ven_id = LAST_INSERT_ID();

  INSERT INTO venta_detalle(ven_id, tip_id, ser_id, tra_id, vde_cantidad, vde_precio, vde_total)
  SELECT v_ven_id, v_tip_servicio, ser_id, tra_id, ads_cantidad, ads_precio, ads_total
  FROM atencion_detalle_servicios
  WHERE atn_id = p_atn_id
    AND ads_estado <> 'ANULADO';

  INSERT INTO comisiones(vde_id, tra_id, com_porcentaje, com_monto)
  SELECT vd.vde_id,
         vd.tra_id,
         d.ads_comision_pct,
         ROUND(vd.vde_total * d.ads_comision_pct / 100, 2)
  FROM venta_detalle vd
  INNER JOIN atencion_detalle_servicios d
    ON d.ser_id = vd.ser_id
   AND d.tra_id = vd.tra_id
   AND d.ads_total = vd.vde_total
  WHERE vd.ven_id = v_ven_id
    AND d.atn_id = p_atn_id;

  CALL spu_venta_pagos_reg(v_ven_id, v_caj_id, p_pagos);

  UPDATE atenciones
     SET atn_estado = 'PAGADO',
         ven_id = v_ven_id
   WHERE atn_id = p_atn_id;

  IF v_cli_id IS NOT NULL THEN
    UPDATE clientes
       SET cli_puntos = cli_puntos + FLOOR(v_total / 100) * 10
     WHERE cli_id = v_cli_id;
  END IF;

  SELECT * FROM ventas WHERE ven_id = v_ven_id;
END $$

DROP PROCEDURE IF EXISTS spu_venta_reg $$
CREATE PROCEDURE spu_venta_reg(
  IN p_suc_id INT,
  IN p_cli_id INT,
  IN p_pagos JSON,
  IN p_items JSON
)
BEGIN
  DECLARE v_ven_id INT;
  DECLARE v_caj_id INT;
  DECLARE v_total DECIMAL(12,2) DEFAULT 0;
  DECLARE v_total_pagos DECIMAL(12,2) DEFAULT 0;
  DECLARE v_idx INT DEFAULT 0;
  DECLARE v_len INT DEFAULT 0;
  DECLARE v_tipo VARCHAR(20);
  DECLARE v_item_id INT;
  DECLARE v_tra_id INT;
  DECLARE v_cantidad DECIMAL(12,2);
  DECLARE v_precio DECIMAL(12,2);
  DECLARE v_tip_id INT;
  DECLARE v_mpa_principal INT;

  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
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Debe abrir caja antes de registrar ventas';
  END IF;

  SET v_mpa_principal = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_pagos, '$[0].mpa_id')) AS UNSIGNED);

  INSERT INTO ventas(suc_id, cli_id, mpa_id, ven_subtotal, ven_total, ven_estado)
  VALUES(p_suc_id, NULLIF(p_cli_id, 0), v_mpa_principal, 0, 0, 'REGISTRADO');
  SET v_ven_id = LAST_INSERT_ID();

  SET v_len = COALESCE(JSON_LENGTH(p_items), 0);

  WHILE v_idx < v_len DO
    SET v_tipo = UPPER(JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', v_idx, '].tipo'))));
    SET v_item_id = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', v_idx, '].item_id'))) AS UNSIGNED);
    SET v_tra_id = COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', v_idx, '].tra_id'))) AS UNSIGNED), 0);
    SET v_cantidad = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', v_idx, '].cantidad'))) AS DECIMAL(12,2));
    SET v_precio = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_items, CONCAT('$[', v_idx, '].precio'))) AS DECIMAL(12,2));

    IF v_tra_id IS NULL OR v_tra_id = 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Toda venta debe tener trabajador responsable';
    END IF;

    SELECT tip_id INTO v_tip_id FROM tipo_item WHERE tip_nombre = v_tipo LIMIT 1;

    INSERT INTO venta_detalle(ven_id, tip_id, ser_id, pro_id, tra_id, vde_cantidad, vde_precio, vde_total)
    VALUES(
      v_ven_id,
      v_tip_id,
      CASE WHEN v_tipo = 'SERVICIO' THEN v_item_id ELSE NULL END,
      CASE WHEN v_tipo = 'PRODUCTO' THEN v_item_id ELSE NULL END,
      v_tra_id,
      v_cantidad,
      v_precio,
      v_cantidad * v_precio
    );

    SET v_total = v_total + (v_cantidad * v_precio);

    IF v_tipo = 'PRODUCTO' THEN
      UPDATE productos SET pro_stock_actual = pro_stock_actual - v_cantidad WHERE pro_id = v_item_id;
      INSERT INTO movimiento_inventario(pro_id, mov_tipo, mov_cantidad, mov_observacion)
      VALUES(v_item_id, 'SALIDA', v_cantidad, CONCAT('Venta ', v_ven_id));
    END IF;

    SET v_idx = v_idx + 1;
  END WHILE;

  SELECT COALESCE(SUM(CAST(JSON_UNQUOTE(JSON_EXTRACT(p_pagos, CONCAT('$[', n.n, '].monto'))) AS DECIMAL(12,2))), 0)
    INTO v_total_pagos
  FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) n
  WHERE n.n < JSON_LENGTH(p_pagos);

  IF ROUND(v_total, 2) <> ROUND(v_total_pagos, 2) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La suma de pagos debe ser igual al total de la venta';
  END IF;

  UPDATE ventas SET ven_subtotal = v_total, ven_total = v_total WHERE ven_id = v_ven_id;

  INSERT INTO comisiones(vde_id, tra_id, com_porcentaje, com_monto)
  SELECT vd.vde_id,
         vd.tra_id,
         CASE WHEN vd.ser_id IS NOT NULL THEN s.ser_comision_pct ELSE 0 END,
         CASE WHEN vd.ser_id IS NOT NULL THEN ROUND(vd.vde_total * s.ser_comision_pct / 100, 2) ELSE 0 END
  FROM venta_detalle vd
  LEFT JOIN servicios s ON s.ser_id = vd.ser_id
  WHERE vd.ven_id = v_ven_id;

  CALL spu_venta_pagos_reg(v_ven_id, v_caj_id, p_pagos);

  SELECT * FROM ventas WHERE ven_id = v_ven_id;
END $$

DELIMITER ;