USE peluqueria;

ALTER TABLE caja
  ADD COLUMN IF NOT EXISTS caj_conforme TINYINT DEFAULT 0,
  ADD COLUMN IF NOT EXISTS caj_fecconformidad TIMESTAMP NULL,
  ADD COLUMN IF NOT EXISTS caj_conformidad_observ TEXT NULL;

ALTER TABLE caja_movimientos
  ADD COLUMN IF NOT EXISTS mpa_id INT NULL;

CREATE TABLE IF NOT EXISTS venta_pagos (
  vpa_id INT AUTO_INCREMENT PRIMARY KEY,
  ven_id INT NOT NULL,
  mpa_id INT NOT NULL,
  vpa_monto DECIMAL(12,2) NOT NULL,
  vpa_referencia VARCHAR(120) NULL,
  vpa_fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_vpa_venta FOREIGN KEY (ven_id) REFERENCES ventas(ven_id),
  CONSTRAINT fk_vpa_metodo FOREIGN KEY (mpa_id) REFERENCES metodos_pago(mpa_id),
  INDEX idx_vpa_venta (ven_id),
  INDEX idx_vpa_metodo_fecha (mpa_id, vpa_fecha)
) 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((SELECT SUM(v.ven_total)
      FROM ventas v
      WHERE v.suc_id = p_suc_id AND DATE(v.ven_fecha) = p_fecha AND v.ven_estado = 'REGISTRADO'), 0) AS ventas_hoy,
    COALESCE((SELECT SUM(vd.vde_total)
      FROM ventas v
      INNER JOIN venta_detalle vd ON vd.ven_id = v.ven_id
      INNER 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' AND ti.tip_nombre = 'SERVICIO'), 0) AS servicios_hoy,
    COALESCE((SELECT SUM(vd.vde_total)
      FROM ventas v
      INNER JOIN venta_detalle vd ON vd.ven_id = v.ven_id
      INNER 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' AND ti.tip_nombre = 'PRODUCTO'), 0) AS productos_hoy,
    COALESCE((SELECT COUNT(DISTINCT v.cli_id)
      FROM ventas v
      WHERE v.suc_id = p_suc_id AND DATE(v.ven_fecha) = p_fecha AND v.ven_estado = 'REGISTRADO' AND v.cli_id IS NOT NULL), 0) AS clientes_hoy,
    COALESCE((SELECT ROUND(AVG(v.ven_total), 2)
      FROM ventas v
      WHERE v.suc_id = p_suc_id AND DATE(v.ven_fecha) = p_fecha AND v.ven_estado = 'REGISTRADO'), 0) AS ticket_promedio,
    COALESCE((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')), 0) AS atenciones_pendientes,
    COALESCE((SELECT SUM(x.total)
      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), 0) AS monto_finalizado_sin_pagar;

  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_caja_estado_sel $$
CREATE PROCEDURE spu_caja_estado_sel(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 caja cx
      WHERE cx.suc_id = p_suc_id AND cx.caj_estado = 'CERRADA' AND cx.caj_conforme = 0
    ) AS cajas_cerradas_sin_conformidad
  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;
END $$

DROP PROCEDURE IF EXISTS spu_caja_abrir $$
CREATE PROCEDURE spu_caja_abrir(IN p_suc_id INT, IN p_fecha DATE, IN p_monto DECIMAL(12,2))
BEGIN
  IF EXISTS (SELECT 1 FROM caja WHERE suc_id = p_suc_id AND caj_estado = 'ABIERTA') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Ya existe una caja abierta. Debe cerrarla antes de abrir otra.';
  END IF;

  IF EXISTS (SELECT 1 FROM caja WHERE suc_id = p_suc_id AND caj_estado = 'CERRADA' AND caj_conforme = 0) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Hay una caja cerrada pendiente de conformidad. Debe conformarla antes de abrir otra.';
  END IF;

  IF EXISTS (SELECT 1 FROM caja WHERE suc_id = p_suc_id AND caj_fecha = p_fecha) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'La caja de esta fecha ya existe.';
  END IF;

  INSERT INTO caja(suc_id, caj_fecha, caj_apertura, caj_estado, caj_conforme)
  VALUES(p_suc_id, p_fecha, p_monto, 'ABIERTA', 0);

  INSERT INTO caja_movimientos(caj_id, cam_tipo, cam_monto, cam_observacion)
  VALUES(LAST_INSERT_ID(), 'APERTURA', p_monto, 'Apertura de caja');

  SELECT * FROM caja WHERE caj_id = LAST_INSERT_ID();
END $$

DROP PROCEDURE IF EXISTS spu_caja_cerrar $$
CREATE PROCEDURE spu_caja_cerrar(IN p_suc_id INT, IN p_fecha DATE)
BEGIN
  DECLARE v_caj_id INT;
  DECLARE v_cierre DECIMAL(12,2);

  IF EXISTS (
    SELECT 1
    FROM atenciones
    WHERE suc_id = p_suc_id
      AND DATE(atn_fecha) = p_fecha
      AND atn_estado IN ('REGISTRADO','EN_PROGRESO','FINALIZADO')
  ) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No puede cerrar caja con atenciones pendientes o finalizadas sin cobrar.';
  END IF;

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

  IF v_caj_id IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'No existe caja abierta para la fecha indicada';
  END IF;

  SELECT 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)
  INTO v_cierre
  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, c.caj_apertura;

  UPDATE caja SET caj_cierre = v_cierre, caj_estado = 'CERRADA', caj_conforme = 0 WHERE caj_id = v_caj_id;
  INSERT INTO caja_movimientos(caj_id, cam_tipo, cam_monto, cam_observacion)
  VALUES(v_caj_id, 'CIERRE', v_cierre, 'Cierre de caja pendiente de conformidad');

  SELECT * FROM caja WHERE caj_id = v_caj_id;
END $$

DROP PROCEDURE IF EXISTS spu_caja_conformidad $$
CREATE PROCEDURE spu_caja_conformidad(IN p_caj_id INT, IN p_observacion TEXT)
BEGIN
  UPDATE caja
     SET caj_conforme = 1,
         caj_fecconformidad = NOW(),
         caj_conformidad_observ = p_observacion
   WHERE caj_id = p_caj_id
     AND caj_estado = 'CERRADA';

  SELECT * FROM caja WHERE caj_id = p_caj_id;
END $$

DROP PROCEDURE IF EXISTS spu_venta_pagos_reg $$
CREATE PROCEDURE spu_venta_pagos_reg(IN p_ven_id INT, IN p_caj_id INT, IN p_pagos JSON)
BEGIN
  DECLARE v_idx INT DEFAULT 0;
  DECLARE v_len INT DEFAULT 0;
  DECLARE v_mpa_id INT;
  DECLARE v_monto DECIMAL(12,2);
  DECLARE v_ref VARCHAR(120);

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

  WHILE v_idx < v_len DO
    SET v_mpa_id = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_pagos, CONCAT('$[', v_idx, '].mpa_id'))) AS UNSIGNED);
    SET v_monto = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_pagos, CONCAT('$[', v_idx, '].monto'))) AS DECIMAL(12,2));
    SET v_ref = JSON_UNQUOTE(JSON_EXTRACT(p_pagos, CONCAT('$[', v_idx, '].referencia')));

    INSERT INTO venta_pagos(ven_id, mpa_id, vpa_monto, vpa_referencia)
    VALUES(p_ven_id, v_mpa_id, v_monto, NULLIF(v_ref, 'null'));

    INSERT INTO caja_movimientos(caj_id, ven_id, mpa_id, cam_tipo, cam_monto, cam_observacion)
    VALUES(p_caj_id, p_ven_id, v_mpa_id, 'INGRESO', v_monto, 'Pago de venta');

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

  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 = 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;
  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;
  CALL spu_venta_pagos_reg(v_ven_id, v_caj_id, p_pagos);

  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;

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

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_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';

  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_caja_cierre_ticket $$
CREATE PROCEDURE spu_caja_cierre_ticket(IN p_caj_id INT)
BEGIN
  SELECT c.*, s.suc_nombre
  FROM caja c
  INNER JOIN sucursales s ON s.suc_id = c.suc_id
  WHERE c.caj_id = p_caj_id;

  SELECT m.mpa_nombre, COALESCE(SUM(vp.vpa_monto), 0) AS total
  FROM venta_pagos vp
  INNER JOIN metodos_pago m ON m.mpa_id = vp.mpa_id
  INNER JOIN ventas v ON v.ven_id = vp.ven_id
  INNER JOIN caja_movimientos cm ON cm.ven_id = v.ven_id AND cm.mpa_id = vp.mpa_id
  WHERE cm.caj_id = p_caj_id
  GROUP BY m.mpa_id, m.mpa_nombre
  ORDER BY m.mpa_nombre;

  SELECT
    v.ven_id,
    v.ven_fecha,
    ti.tip_nombre,
    COALESCE(s.ser_nombre, p.pro_nombre) AS item,
    CONCAT_WS(' ', pe.per_apepat, pe.per_apemat, pe.per_nombres) AS trabajador,
    vd.vde_cantidad,
    vd.vde_total
  FROM caja_movimientos cm
  INNER JOIN ventas v ON v.ven_id = cm.ven_id
  INNER JOIN venta_detalle vd ON vd.ven_id = v.ven_id
  INNER JOIN tipo_item ti ON ti.tip_id = vd.tip_id
  LEFT JOIN servicios s ON s.ser_id = vd.ser_id
  LEFT JOIN productos p ON p.pro_id = vd.pro_id
  LEFT JOIN trabajadores t ON t.tra_id = vd.tra_id
  LEFT JOIN personas pe ON pe.per_id = t.per_id
  WHERE cm.caj_id = p_caj_id
  GROUP BY v.ven_id, v.ven_fecha, ti.tip_nombre, item, trabajador, vd.vde_cantidad, vd.vde_total
  ORDER BY v.ven_fecha, v.ven_id;
END $$

DROP PROCEDURE IF EXISTS spu_usuario_lis $$
CREATE PROCEDURE spu_usuario_lis(IN p_activo TINYINT)
BEGIN
  SELECT u.usu_id, u.usu_login, u.usu_activo, r.rol_id, r.rol_nombre, p.*
  FROM usuarios u
  INNER JOIN personas p ON p.per_id = u.per_id
  INNER JOIN roles r ON r.rol_id = u.rol_id
  WHERE p_activo IS NULL OR u.usu_activo = p_activo
  ORDER BY p.per_apepat, p.per_apemat, p.per_nombres;
END $$

DROP PROCEDURE IF EXISTS spu_usuario_gra $$
CREATE PROCEDURE spu_usuario_gra(
  IN p_usu_id INT,
  IN p_rol_id INT,
  IN p_login VARCHAR(100),
  IN p_password TEXT,
  IN p_tipdoc VARCHAR(20),
  IN p_numdoc VARCHAR(20),
  IN p_apepat VARCHAR(100),
  IN p_apemat VARCHAR(100),
  IN p_nombres VARCHAR(150),
  IN p_celular VARCHAR(30),
  IN p_correo VARCHAR(150)
)
BEGIN
  DECLARE v_per_id INT;

  IF p_usu_id IS NULL OR p_usu_id = 0 THEN
    INSERT INTO personas(per_tipdoc, per_numdoc, per_apepat, per_apemat, per_nombres, per_celular, per_correo)
    VALUES(p_tipdoc, p_numdoc, p_apepat, p_apemat, p_nombres, p_celular, p_correo);
    SET v_per_id = LAST_INSERT_ID();
    INSERT INTO usuarios(per_id, rol_id, usu_login, usu_password, usu_activo)
    VALUES(v_per_id, p_rol_id, p_login, p_password, 1);
    SET p_usu_id = LAST_INSERT_ID();
  ELSE
    SELECT per_id INTO v_per_id FROM usuarios WHERE usu_id = p_usu_id;
    UPDATE personas SET per_tipdoc = p_tipdoc, per_numdoc = p_numdoc, per_apepat = p_apepat, per_apemat = p_apemat,
      per_nombres = p_nombres, per_celular = p_celular, per_correo = p_correo
    WHERE per_id = v_per_id;
    UPDATE usuarios SET rol_id = p_rol_id, usu_login = p_login, usu_password = COALESCE(NULLIF(p_password, ''), usu_password)
    WHERE usu_id = p_usu_id;
  END IF;

  SELECT p_usu_id AS usu_id;
END $$

DROP PROCEDURE IF EXISTS spu_usuario_estado $$
CREATE PROCEDURE spu_usuario_estado(IN p_usu_id INT, IN p_activo TINYINT)
BEGIN
  UPDATE usuarios SET usu_activo = p_activo WHERE usu_id = p_usu_id;
  SELECT * FROM usuarios WHERE usu_id = p_usu_id;
END $$

DROP PROCEDURE IF EXISTS spu_roles_lis $$
CREATE PROCEDURE spu_roles_lis()
BEGIN
  SELECT rol_id AS id, rol_nombre AS nombre FROM roles ORDER BY rol_nombre;
END $$

DELIMITER ;