USE peluqueria;

CREATE TABLE IF NOT EXISTS atenciones (
  atn_id INT AUTO_INCREMENT PRIMARY KEY,
  suc_id INT NOT NULL,
  cli_id INT NULL,
  ven_id INT NULL,
  atn_codigo VARCHAR(30) NULL,
  atn_fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  atn_estado ENUM('REGISTRADO','EN_PROGRESO','FINALIZADO','PAGADO','ANULADO') DEFAULT 'REGISTRADO',
  atn_observacion TEXT,
  CONSTRAINT fk_atencion_sucursal FOREIGN KEY (suc_id) REFERENCES sucursales(suc_id),
  CONSTRAINT fk_atencion_cliente FOREIGN KEY (cli_id) REFERENCES clientes(cli_id),
  CONSTRAINT fk_atencion_venta FOREIGN KEY (ven_id) REFERENCES ventas(ven_id),
  INDEX idx_atencion_fecha_estado (suc_id, atn_fecha, atn_estado)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS atencion_detalle_servicios (
  ads_id INT AUTO_INCREMENT PRIMARY KEY,
  atn_id INT NOT NULL,
  ser_id INT NOT NULL,
  tra_id INT NOT NULL,
  ads_cantidad DECIMAL(12,2) DEFAULT 1,
  ads_precio DECIMAL(12,2) DEFAULT 0,
  ads_total DECIMAL(12,2) DEFAULT 0,
  ads_comision_pct DECIMAL(5,2) DEFAULT 0,
  ads_estado ENUM('REGISTRADO','EN_PROGRESO','FINALIZADO','ANULADO') DEFAULT 'REGISTRADO',
  ads_feccre TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ads_fecini TIMESTAMP NULL,
  ads_fecfin TIMESTAMP NULL,
  CONSTRAINT fk_ads_atencion FOREIGN KEY (atn_id) REFERENCES atenciones(atn_id),
  CONSTRAINT fk_ads_servicio FOREIGN KEY (ser_id) REFERENCES servicios(ser_id),
  CONSTRAINT fk_ads_trabajador FOREIGN KEY (tra_id) REFERENCES trabajadores(tra_id),
  INDEX idx_ads_atencion_estado (atn_id, ads_estado),
  INDEX idx_ads_trabajador (tra_id)
) ENGINE=InnoDB;

DELIMITER $$

DROP PROCEDURE IF EXISTS spu_dashboard_sel $$
CREATE PROCEDURE spu_dashboard_sel(IN p_suc_id INT, IN p_fecha DATE)
BEGIN
  SELECT
    COALESCE(SUM(v.ven_total), 0) AS ventas_hoy,
    COALESCE(SUM(CASE WHEN ti.tip_nombre = 'SERVICIO' THEN vd.vde_total ELSE 0 END), 0) AS servicios_hoy,
    COALESCE(SUM(CASE WHEN ti.tip_nombre = 'PRODUCTO' THEN vd.vde_total ELSE 0 END), 0) AS productos_hoy,
    COUNT(DISTINCT v.cli_id) AS clientes_hoy,
    CASE WHEN COUNT(DISTINCT v.ven_id) = 0 THEN 0 ELSE ROUND(SUM(v.ven_total) / COUNT(DISTINCT v.ven_id), 2) END AS ticket_promedio,
    (
      SELECT COUNT(*)
      FROM atenciones a
      WHERE a.suc_id = p_suc_id
        AND DATE(a.atn_fecha) = p_fecha
        AND a.atn_estado IN ('REGISTRADO','EN_PROGRESO','FINALIZADO')
    ) AS atenciones_pendientes,
    (
      SELECT COALESCE(SUM(x.total), 0)
      FROM (
        SELECT a.atn_id, SUM(d.ads_total) AS total
        FROM atenciones a
        INNER JOIN atencion_detalle_servicios d ON d.atn_id = a.atn_id
        WHERE a.suc_id = p_suc_id
          AND DATE(a.atn_fecha) = p_fecha
          AND a.atn_estado = 'FINALIZADO'
          AND d.ads_estado <> 'ANULADO'
        GROUP BY a.atn_id
      ) x
    ) AS monto_finalizado_sin_pagar
  FROM ventas v
  LEFT JOIN venta_detalle vd ON vd.ven_id = v.ven_id
  LEFT JOIN tipo_item ti ON ti.tip_id = vd.tip_id
  WHERE v.suc_id = p_suc_id
    AND DATE(v.ven_fecha) = p_fecha
    AND v.ven_estado = 'REGISTRADO';

  SELECT p.pro_id, p.pro_nombre, p.pro_stock_actual, p.pro_stock_min
  FROM productos p
  WHERE p.pro_activo = 1 AND p.pro_stock_actual <= p.pro_stock_min
  ORDER BY p.pro_stock_actual ASC;
END $$

DROP PROCEDURE IF EXISTS spu_atencion_reg $$
CREATE PROCEDURE spu_atencion_reg(
  IN p_suc_id INT,
  IN p_cli_id INT,
  IN p_observacion TEXT,
  IN p_servicios JSON
)
BEGIN
  DECLARE v_atn_id INT;
  DECLARE v_idx INT DEFAULT 0;
  DECLARE v_len INT DEFAULT 0;
  DECLARE v_ser_id INT;
  DECLARE v_tra_id INT;
  DECLARE v_cantidad DECIMAL(12,2);
  DECLARE v_precio DECIMAL(12,2);
  DECLARE v_comision DECIMAL(5,2);

  INSERT INTO atenciones(suc_id, cli_id, atn_observacion)
  VALUES(p_suc_id, NULLIF(p_cli_id, 0), p_observacion);

  SET v_atn_id = LAST_INSERT_ID();
  UPDATE atenciones SET atn_codigo = CONCAT('AT-', DATE_FORMAT(NOW(), '%Y%m%d'), '-', LPAD(v_atn_id, 5, '0'))
  WHERE atn_id = v_atn_id;

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

  WHILE v_idx < v_len DO
    SET v_ser_id = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_servicios, CONCAT('$[', v_idx, '].ser_id'))) AS UNSIGNED);
    SET v_tra_id = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_servicios, CONCAT('$[', v_idx, '].tra_id'))) AS UNSIGNED);
    SET v_cantidad = COALESCE(CAST(JSON_UNQUOTE(JSON_EXTRACT(p_servicios, CONCAT('$[', v_idx, '].cantidad'))) AS DECIMAL(12,2)), 1);

    SELECT ser_precio, ser_comision_pct
      INTO v_precio, v_comision
      FROM servicios
     WHERE ser_id = v_ser_id;

    INSERT INTO atencion_detalle_servicios(atn_id, ser_id, tra_id, ads_cantidad, ads_precio, ads_total, ads_comision_pct)
    VALUES(v_atn_id, v_ser_id, v_tra_id, v_cantidad, v_precio, v_cantidad * v_precio, v_comision);

    SET v_idx = v_idx + 1;
  END WHILE;

  SELECT * FROM atenciones WHERE atn_id = v_atn_id;
END $$

DROP PROCEDURE IF EXISTS spu_atencion_lis $$
CREATE PROCEDURE spu_atencion_lis(IN p_suc_id INT, IN p_fecha DATE, IN p_estado VARCHAR(20))
BEGIN
  SELECT
    a.atn_id,
    a.atn_codigo,
    a.atn_fecha,
    a.atn_estado,
    a.ven_id,
    COALESCE(CONCAT_WS(' ', pc.per_nombres, pc.per_apepat, pc.per_apemat), 'Cliente directo') AS cliente,
    GROUP_CONCAT(DISTINCT s.ser_nombre ORDER BY s.ser_nombre SEPARATOR ', ') AS servicios,
    GROUP_CONCAT(DISTINCT CONCAT_WS(' ', pt.per_nombres, pt.per_apepat) ORDER BY pt.per_nombres SEPARATOR ', ') AS trabajadores,
    COALESCE(SUM(CASE WHEN d.ads_estado <> 'ANULADO' THEN d.ads_total ELSE 0 END), 0) AS total
  FROM atenciones a
  LEFT JOIN clientes c ON c.cli_id = a.cli_id
  LEFT JOIN personas pc ON pc.per_id = c.per_id
  LEFT JOIN atencion_detalle_servicios d ON d.atn_id = a.atn_id
  LEFT JOIN servicios s ON s.ser_id = d.ser_id
  LEFT JOIN trabajadores t ON t.tra_id = d.tra_id
  LEFT JOIN personas pt ON pt.per_id = t.per_id
  WHERE a.suc_id = p_suc_id
    AND DATE(a.atn_fecha) = p_fecha
    AND (p_estado IS NULL OR p_estado = '' OR a.atn_estado = p_estado)
  GROUP BY a.atn_id, a.atn_codigo, a.atn_fecha, a.atn_estado, a.ven_id, cliente
  ORDER BY FIELD(a.atn_estado, 'EN_PROGRESO','FINALIZADO','REGISTRADO','PAGADO','ANULADO'), a.atn_id DESC;
END $$

DROP PROCEDURE IF EXISTS spu_atencion_detalle_lis $$
CREATE PROCEDURE spu_atencion_detalle_lis(IN p_atn_id INT)
BEGIN
  SELECT
    d.*,
    s.ser_nombre,
    s.ser_duracion_min,
    CONCAT_WS(' ', p.per_nombres, p.per_apepat, p.per_apemat) AS trabajador
  FROM atencion_detalle_servicios d
  INNER JOIN servicios s ON s.ser_id = d.ser_id
  INNER JOIN trabajadores t ON t.tra_id = d.tra_id
  INNER JOIN personas p ON p.per_id = t.per_id
  WHERE d.atn_id = p_atn_id
  ORDER BY d.ads_id;
END $$

DROP PROCEDURE IF EXISTS spu_atencion_estado_upd $$
CREATE PROCEDURE spu_atencion_estado_upd(IN p_atn_id INT, IN p_estado VARCHAR(20))
BEGIN
  IF p_estado NOT IN ('REGISTRADO','EN_PROGRESO','FINALIZADO','ANULADO') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Estado no valido para atencion';
  END IF;

  UPDATE atenciones
     SET atn_estado = p_estado
   WHERE atn_id = p_atn_id
     AND atn_estado <> 'PAGADO';

  UPDATE atencion_detalle_servicios
     SET ads_estado = CASE WHEN p_estado = 'ANULADO' THEN 'ANULADO' ELSE p_estado END,
         ads_fecini = CASE WHEN p_estado = 'EN_PROGRESO' AND ads_fecini IS NULL THEN NOW() ELSE ads_fecini END,
         ads_fecfin = CASE WHEN p_estado = 'FINALIZADO' THEN NOW() ELSE ads_fecfin END
   WHERE atn_id = p_atn_id
     AND ads_estado <> 'ANULADO';

  SELECT * FROM atenciones WHERE atn_id = p_atn_id;
END $$

DROP PROCEDURE IF EXISTS spu_atencion_pagar $$
CREATE PROCEDURE spu_atencion_pagar(IN p_atn_id INT, IN p_mpa_id INT)
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_estado VARCHAR(20);
  DECLARE v_tip_servicio 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_fecha = CURRENT_DATE() AND caj_estado = 'ABIERTA'
  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';

  IF v_total <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La atencion no tiene servicios para cobrar';
  END IF;

  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, p_mpa_id, 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;

  INSERT INTO caja_movimientos(caj_id, ven_id, cam_tipo, cam_monto, cam_observacion)
  VALUES(v_caj_id, v_ven_id, 'INGRESO', v_total, CONCAT('Cobro atencion ', p_atn_id));

  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_mpa_id INT,
  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_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;

  SELECT caj_id INTO v_caj_id
  FROM caja
  WHERE suc_id = p_suc_id AND caj_fecha = CURRENT_DATE() AND caj_estado = 'ABIERTA'
  LIMIT 1;

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

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

  SET v_len = JSON_LENGTH(p_items);

  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_idx = v_idx + 1;
  END WHILE;

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

  UPDATE productos p
  INNER JOIN venta_detalle vd ON vd.pro_id = p.pro_id
  SET p.pro_stock_actual = p.pro_stock_actual - vd.vde_cantidad
  WHERE vd.ven_id = v_ven_id;

  INSERT INTO movimiento_inventario(pro_id, mov_tipo, mov_cantidad, mov_observacion)
  SELECT pro_id, 'SALIDA', vde_cantidad, CONCAT('Venta ', v_ven_id)
  FROM venta_detalle
  WHERE ven_id = v_ven_id AND pro_id IS NOT NULL;

  SELECT COALESCE(SUM(vde_total), 0) INTO v_total
  FROM venta_detalle
  WHERE ven_id = v_ven_id;

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

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

  INSERT INTO caja_movimientos(caj_id, ven_id, cam_tipo, cam_monto, cam_observacion)
  VALUES(v_caj_id, v_ven_id, 'INGRESO', v_total, 'Venta registrada');

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

DROP PROCEDURE IF EXISTS spu_reporte_trabajador $$
CREATE PROCEDURE spu_reporte_trabajador(IN p_suc_id INT, IN p_fecini DATE, IN p_fecfin DATE)
BEGIN
  SELECT
    t.tra_id,
    CONCAT_WS(' ', p.per_apepat, p.per_apemat, p.per_nombres) AS trabajador,
    COALESCE(SUM(CASE WHEN ti.tip_nombre = 'SERVICIO' THEN vd.vde_total ELSE 0 END), 0) AS venta_servicios,
    COALESCE(SUM(CASE WHEN ti.tip_nombre = 'PRODUCTO' THEN vd.vde_total ELSE 0 END), 0) AS venta_productos,
    COALESCE(SUM(vd.vde_total), 0) AS venta_total_pagada,
    COALESCE(SUM(c.com_monto), 0) AS comision,
    COUNT(DISTINCT CASE WHEN ti.tip_nombre = 'SERVICIO' THEN vd.vde_id END) AS servicios_pagados,
    COUNT(DISTINCT v.cli_id) AS clientes_atendidos
  FROM trabajadores t
  INNER JOIN personas p ON p.per_id = t.per_id
  LEFT JOIN venta_detalle vd ON vd.tra_id = t.tra_id
  LEFT JOIN ventas v ON v.ven_id = vd.ven_id
    AND v.ven_estado = 'REGISTRADO'
    AND DATE(v.ven_fecha) BETWEEN p_fecini AND p_fecfin
  LEFT JOIN tipo_item ti ON ti.tip_id = vd.tip_id
  LEFT JOIN comisiones c ON c.vde_id = vd.vde_id
  WHERE t.suc_id = p_suc_id
    AND (v.ven_id IS NOT NULL OR vd.vde_id IS NULL)
  GROUP BY t.tra_id, trabajador
  ORDER BY venta_total_pagada DESC;
END $$

DROP PROCEDURE IF EXISTS spu_reporte_caja $$
CREATE PROCEDURE spu_reporte_caja(IN p_suc_id INT, IN p_fecha DATE)
BEGIN
  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,
         (
           SELECT COUNT(*)
           FROM atenciones a
           WHERE a.suc_id = p_suc_id
             AND DATE(a.atn_fecha) = p_fecha
             AND a.atn_estado IN ('REGISTRADO','EN_PROGRESO','FINALIZADO')
         ) AS atenciones_sin_cobrar
  FROM caja c
  LEFT JOIN caja_movimientos cm ON cm.caj_id = c.caj_id
  WHERE c.suc_id = p_suc_id AND c.caj_fecha = p_fecha
  GROUP BY c.caj_id;

  SELECT cm.*
  FROM caja_movimientos cm
  INNER JOIN caja c ON c.caj_id = cm.caj_id
  WHERE c.suc_id = p_suc_id AND c.caj_fecha = p_fecha
  ORDER BY cm.cam_id;
END $$

DROP PROCEDURE IF EXISTS spu_reporte_rentabilidad $$
CREATE PROCEDURE spu_reporte_rentabilidad(IN p_fecini DATE, IN p_fecfin DATE)
BEGIN
  SELECT s.ser_id, s.ser_nombre, COUNT(vd.vde_id) AS cantidad_pagada, COALESCE(SUM(vd.vde_total), 0) AS venta_pagada
  FROM servicios s
  LEFT JOIN venta_detalle vd ON vd.ser_id = s.ser_id
  LEFT JOIN ventas v ON v.ven_id = vd.ven_id
    AND v.ven_estado = 'REGISTRADO'
    AND DATE(v.ven_fecha) BETWEEN p_fecini AND p_fecfin
  WHERE v.ven_id IS NOT NULL OR vd.vde_id IS NULL
  GROUP BY s.ser_id, s.ser_nombre
  ORDER BY venta_pagada DESC;
END $$

DELIMITER ;