-- Sistema interno para peluqueria - MySQL
-- Base objetivo: peluqueria

CREATE DATABASE IF NOT EXISTS peluqueria
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE peluqueria;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS auditoria;
DROP TABLE IF EXISTS gastos;
DROP TABLE IF EXISTS caja_movimientos;
DROP TABLE IF EXISTS caja;
DROP TABLE IF EXISTS comisiones;
DROP TABLE IF EXISTS venta_detalle;
DROP TABLE IF EXISTS tipo_item;
DROP TABLE IF EXISTS ventas;
DROP TABLE IF EXISTS metodos_pago;
DROP TABLE IF EXISTS citas;
DROP TABLE IF EXISTS movimiento_inventario;
DROP TABLE IF EXISTS productos;
DROP TABLE IF EXISTS categoria_producto;
DROP TABLE IF EXISTS servicios;
DROP TABLE IF EXISTS categoria_servicio;
DROP TABLE IF EXISTS trabajadores;
DROP TABLE IF EXISTS especialidades;
DROP TABLE IF EXISTS clientes;
DROP TABLE IF EXISTS usuarios;
DROP TABLE IF EXISTS personas;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS sucursales;
DROP TABLE IF EXISTS empresas;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE empresas (
  emp_id INT AUTO_INCREMENT PRIMARY KEY,
  emp_ruc VARCHAR(11),
  emp_razsoc VARCHAR(250) NOT NULL,
  emp_nombrecom VARCHAR(250),
  emp_direccion TEXT,
  emp_telefono VARCHAR(50),
  emp_correo VARCHAR(150),
  emp_activo TINYINT DEFAULT 1,
  emp_feccre TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE sucursales (
  suc_id INT AUTO_INCREMENT PRIMARY KEY,
  emp_id INT NOT NULL,
  suc_nombre VARCHAR(200) NOT NULL,
  suc_direccion TEXT,
  suc_telefono VARCHAR(50),
  suc_activo TINYINT DEFAULT 1,
  CONSTRAINT fk_sucursal_empresa FOREIGN KEY (emp_id) REFERENCES empresas(emp_id)
) ENGINE=InnoDB;

CREATE TABLE roles (
  rol_id INT AUTO_INCREMENT PRIMARY KEY,
  rol_nombre VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE personas (
  per_id INT AUTO_INCREMENT PRIMARY KEY,
  per_tipdoc VARCHAR(20),
  per_numdoc VARCHAR(20),
  per_apepat VARCHAR(100),
  per_apemat VARCHAR(100),
  per_nombres VARCHAR(150),
  per_celular VARCHAR(30),
  per_correo VARCHAR(150),
  per_direccion TEXT,
  per_fecnac DATE,
  per_activo TINYINT DEFAULT 1,
  per_feccre TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_persona_documento (per_numdoc)
) ENGINE=InnoDB;

CREATE TABLE usuarios (
  usu_id INT AUTO_INCREMENT PRIMARY KEY,
  per_id INT NOT NULL,
  rol_id INT NOT NULL,
  usu_login VARCHAR(100) UNIQUE,
  usu_password TEXT,
  usu_activo TINYINT DEFAULT 1,
  CONSTRAINT fk_usuario_persona FOREIGN KEY (per_id) REFERENCES personas(per_id),
  CONSTRAINT fk_usuario_rol FOREIGN KEY (rol_id) REFERENCES roles(rol_id)
) ENGINE=InnoDB;

CREATE TABLE clientes (
  cli_id INT AUTO_INCREMENT PRIMARY KEY,
  per_id INT NOT NULL,
  cli_puntos DECIMAL(10,2) DEFAULT 0,
  CONSTRAINT fk_cliente_persona FOREIGN KEY (per_id) REFERENCES personas(per_id)
) ENGINE=InnoDB;

CREATE TABLE especialidades (
  esp_id INT AUTO_INCREMENT PRIMARY KEY,
  esp_nombre VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE trabajadores (
  tra_id INT AUTO_INCREMENT PRIMARY KEY,
  suc_id INT NOT NULL,
  per_id INT NOT NULL,
  esp_id INT,
  tra_fecing DATE,
  tra_comision_base DECIMAL(5,2) DEFAULT 0,
  tra_activo TINYINT DEFAULT 1,
  CONSTRAINT fk_tra_sucursal FOREIGN KEY (suc_id) REFERENCES sucursales(suc_id),
  CONSTRAINT fk_tra_persona FOREIGN KEY (per_id) REFERENCES personas(per_id),
  CONSTRAINT fk_tra_especialidad FOREIGN KEY (esp_id) REFERENCES especialidades(esp_id)
) ENGINE=InnoDB;

CREATE TABLE categoria_servicio (
  csv_id INT AUTO_INCREMENT PRIMARY KEY,
  csv_nombre VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE servicios (
  ser_id INT AUTO_INCREMENT PRIMARY KEY,
  csv_id INT NOT NULL,
  ser_nombre VARCHAR(200) NOT NULL,
  ser_precio DECIMAL(12,2) NOT NULL,
  ser_comision_pct DECIMAL(5,2) DEFAULT 0,
  ser_duracion_min INT,
  ser_activo TINYINT DEFAULT 1,
  CONSTRAINT fk_servicio_categoria FOREIGN KEY (csv_id) REFERENCES categoria_servicio(csv_id)
) ENGINE=InnoDB;

CREATE TABLE categoria_producto (
  cat_id INT AUTO_INCREMENT PRIMARY KEY,
  cat_nombre VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE productos (
  pro_id INT AUTO_INCREMENT PRIMARY KEY,
  cat_id INT NOT NULL,
  pro_nombre VARCHAR(250) NOT NULL,
  pro_precio_compra DECIMAL(12,2) DEFAULT 0,
  pro_precio_venta DECIMAL(12,2) DEFAULT 0,
  pro_stock_actual DECIMAL(12,2) DEFAULT 0,
  pro_stock_min DECIMAL(12,2) DEFAULT 0,
  pro_activo TINYINT DEFAULT 1,
  CONSTRAINT fk_producto_categoria FOREIGN KEY (cat_id) REFERENCES categoria_producto(cat_id)
) ENGINE=InnoDB;

CREATE TABLE movimiento_inventario (
  mov_id INT AUTO_INCREMENT PRIMARY KEY,
  pro_id INT NOT NULL,
  mov_tipo ENUM('ENTRADA','SALIDA','AJUSTE') NOT NULL,
  mov_cantidad DECIMAL(12,2) NOT NULL,
  mov_fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  mov_observacion TEXT,
  CONSTRAINT fk_mov_producto FOREIGN KEY (pro_id) REFERENCES productos(pro_id)
) ENGINE=InnoDB;

CREATE TABLE citas (
  cit_id INT AUTO_INCREMENT PRIMARY KEY,
  suc_id INT NOT NULL,
  cli_id INT NOT NULL,
  tra_id INT,
  cit_fecha DATETIME NOT NULL,
  cit_estado ENUM('RESERVADO','CONFIRMADO','EN_PROCESO','FINALIZADO','CANCELADO') DEFAULT 'RESERVADO',
  cit_observacion TEXT,
  CONSTRAINT fk_cita_sucursal FOREIGN KEY (suc_id) REFERENCES sucursales(suc_id),
  CONSTRAINT fk_cita_cliente FOREIGN KEY (cli_id) REFERENCES clientes(cli_id),
  CONSTRAINT fk_cita_trabajador FOREIGN KEY (tra_id) REFERENCES trabajadores(tra_id)
) ENGINE=InnoDB;

CREATE TABLE metodos_pago (
  mpa_id INT AUTO_INCREMENT PRIMARY KEY,
  mpa_nombre VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE ventas (
  ven_id INT AUTO_INCREMENT PRIMARY KEY,
  suc_id INT NOT NULL,
  cli_id INT,
  mpa_id INT,
  ven_fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ven_subtotal DECIMAL(12,2) DEFAULT 0,
  ven_total DECIMAL(12,2) DEFAULT 0,
  ven_estado ENUM('REGISTRADO','ANULADO') DEFAULT 'REGISTRADO',
  CONSTRAINT fk_venta_sucursal FOREIGN KEY (suc_id) REFERENCES sucursales(suc_id),
  CONSTRAINT fk_venta_cliente FOREIGN KEY (cli_id) REFERENCES clientes(cli_id),
  CONSTRAINT fk_venta_metodo FOREIGN KEY (mpa_id) REFERENCES metodos_pago(mpa_id)
) ENGINE=InnoDB;

CREATE TABLE tipo_item (
  tip_id INT AUTO_INCREMENT PRIMARY KEY,
  tip_nombre VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE venta_detalle (
  vde_id INT AUTO_INCREMENT PRIMARY KEY,
  ven_id INT NOT NULL,
  tip_id INT NOT NULL,
  ser_id INT,
  pro_id INT,
  tra_id INT,
  vde_cantidad DECIMAL(12,2) DEFAULT 1,
  vde_precio DECIMAL(12,2) DEFAULT 0,
  vde_total DECIMAL(12,2) DEFAULT 0,
  CONSTRAINT fk_vde_venta FOREIGN KEY (ven_id) REFERENCES ventas(ven_id),
  CONSTRAINT fk_vde_tipo FOREIGN KEY (tip_id) REFERENCES tipo_item(tip_id),
  CONSTRAINT fk_vde_servicio FOREIGN KEY (ser_id) REFERENCES servicios(ser_id),
  CONSTRAINT fk_vde_producto FOREIGN KEY (pro_id) REFERENCES productos(pro_id),
  CONSTRAINT fk_vde_trabajador FOREIGN KEY (tra_id) REFERENCES trabajadores(tra_id)
) ENGINE=InnoDB;

CREATE TABLE comisiones (
  com_id INT AUTO_INCREMENT PRIMARY KEY,
  vde_id INT NOT NULL,
  tra_id INT NOT NULL,
  com_porcentaje DECIMAL(5,2) DEFAULT 0,
  com_monto DECIMAL(12,2) DEFAULT 0,
  com_fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_com_detalle FOREIGN KEY (vde_id) REFERENCES venta_detalle(vde_id),
  CONSTRAINT fk_com_trabajador FOREIGN KEY (tra_id) REFERENCES trabajadores(tra_id)
) ENGINE=InnoDB;

CREATE TABLE caja (
  caj_id INT AUTO_INCREMENT PRIMARY KEY,
  suc_id INT NOT NULL,
  caj_fecha DATE NOT NULL,
  caj_apertura DECIMAL(12,2) DEFAULT 0,
  caj_cierre DECIMAL(12,2),
  caj_estado ENUM('ABIERTA','CERRADA') DEFAULT 'ABIERTA',
  UNIQUE KEY uk_caja_sucursal_fecha (suc_id, caj_fecha),
  CONSTRAINT fk_caja_sucursal FOREIGN KEY (suc_id) REFERENCES sucursales(suc_id)
) ENGINE=InnoDB;

CREATE TABLE caja_movimientos (
  cam_id INT AUTO_INCREMENT PRIMARY KEY,
  caj_id INT NOT NULL,
  ven_id INT,
  cam_tipo ENUM('INGRESO','EGRESO','APERTURA','CIERRE') NOT NULL,
  cam_monto DECIMAL(12,2) DEFAULT 0,
  cam_observacion TEXT,
  cam_fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_cam_caja FOREIGN KEY (caj_id) REFERENCES caja(caj_id),
  CONSTRAINT fk_cam_venta FOREIGN KEY (ven_id) REFERENCES ventas(ven_id)
) ENGINE=InnoDB;

CREATE TABLE gastos (
  gas_id INT AUTO_INCREMENT PRIMARY KEY,
  suc_id INT NOT NULL,
  gas_fecha DATE NOT NULL,
  gas_descripcion TEXT,
  gas_monto DECIMAL(12,2) DEFAULT 0,
  CONSTRAINT fk_gasto_sucursal FOREIGN KEY (suc_id) REFERENCES sucursales(suc_id)
) ENGINE=InnoDB;

CREATE TABLE auditoria (
  aud_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  usu_id INT,
  aud_tabla VARCHAR(100),
  aud_accion VARCHAR(50),
  aud_registro VARCHAR(100),
  aud_fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO roles (rol_nombre) VALUES
('ADMINISTRADOR'), ('RECEPCIONISTA'), ('ESTILISTA'), ('MANICURISTA'), ('CAJERO');

INSERT INTO especialidades (esp_nombre) VALUES
('ESTILISTA'), ('COLORISTA'), ('MANICURISTA'), ('PEDICURISTA'), ('BARBERO');

INSERT INTO categoria_servicio (csv_nombre) VALUES
('CABELLO'), ('TINTES'), ('BALAYAGE'), ('MANICURE'), ('PEDICURE'), ('TRATAMIENTOS');

INSERT INTO categoria_producto (cat_nombre) VALUES
('SHAMPOO'), ('ACONDICIONADOR'), ('MASCARILLA'), ('TINTE'), ('TRATAMIENTO'), ('OTROS');

INSERT INTO metodos_pago (mpa_nombre) VALUES
('EFECTIVO'), ('YAPE'), ('PLIN'), ('TARJETA'), ('TRANSFERENCIA');

INSERT INTO tipo_item (tip_nombre) VALUES
('SERVICIO'), ('PRODUCTO');

INSERT INTO empresas (emp_ruc, emp_razsoc, emp_nombrecom, emp_direccion, emp_telefono, emp_correo)
VALUES ('00000000000', 'PELUQUERIA', 'Salon de Belleza', 'Local principal', '', '');

INSERT INTO sucursales (emp_id, suc_nombre, suc_direccion, suc_telefono)
VALUES (1, 'PRINCIPAL', 'Local principal', '');

INSERT INTO servicios (csv_id, ser_nombre, ser_precio, ser_comision_pct, ser_duracion_min) VALUES
(1, 'Corte mujer', 40.00, 30.00, 45),
(1, 'Corte hombre', 25.00, 30.00, 30),
(2, 'Tinte', 180.00, 30.00, 120),
(3, 'Balayage', 350.00, 30.00, 180),
(1, 'Peinado', 90.00, 30.00, 60),
(6, 'Keratina', 250.00, 30.00, 150),
(4, 'Manicure', 40.00, 60.00, 45),
(5, 'Pedicure', 50.00, 60.00, 60);

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
  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_cliente_gra $$
CREATE PROCEDURE spu_cliente_gra(
  IN p_cli_id INT,
  IN p_per_tipdoc VARCHAR(20),
  IN p_per_numdoc VARCHAR(20),
  IN p_per_apepat VARCHAR(100),
  IN p_per_apemat VARCHAR(100),
  IN p_per_nombres VARCHAR(150),
  IN p_per_celular VARCHAR(30),
  IN p_per_correo VARCHAR(150),
  IN p_per_direccion TEXT,
  IN p_per_fecnac DATE
)
BEGIN
  DECLARE v_per_id INT;
  IF p_cli_id IS NULL OR p_cli_id = 0 THEN
    INSERT INTO personas(per_tipdoc, per_numdoc, per_apepat, per_apemat, per_nombres, per_celular, per_correo, per_direccion, per_fecnac)
    VALUES(p_per_tipdoc, p_per_numdoc, p_per_apepat, p_per_apemat, p_per_nombres, p_per_celular, p_per_correo, p_per_direccion, p_per_fecnac);
    SET v_per_id = LAST_INSERT_ID();
    INSERT INTO clientes(per_id, cli_puntos) VALUES(v_per_id, 0);
    SET p_cli_id = LAST_INSERT_ID();
  ELSE
    SELECT per_id INTO v_per_id FROM clientes WHERE cli_id = p_cli_id;
    UPDATE personas
       SET per_tipdoc = p_per_tipdoc,
           per_numdoc = p_per_numdoc,
           per_apepat = p_per_apepat,
           per_apemat = p_per_apemat,
           per_nombres = p_per_nombres,
           per_celular = p_per_celular,
           per_correo = p_per_correo,
           per_direccion = p_per_direccion,
           per_fecnac = p_per_fecnac
     WHERE per_id = v_per_id;
  END IF;
  SELECT p_cli_id AS cli_id;
END $$

DROP PROCEDURE IF EXISTS spu_cliente_lis $$
CREATE PROCEDURE spu_cliente_lis(IN p_buscar VARCHAR(150), IN p_activo TINYINT)
BEGIN
  SELECT c.cli_id, c.cli_puntos, p.*
  FROM clientes c
  INNER JOIN personas p ON p.per_id = c.per_id
  WHERE (p_activo IS NULL OR p.per_activo = p_activo)
    AND (
      p_buscar IS NULL OR p_buscar = ''
      OR p.per_numdoc LIKE CONCAT('%', p_buscar, '%')
      OR p.per_nombres LIKE CONCAT('%', p_buscar, '%')
      OR p.per_apepat LIKE CONCAT('%', p_buscar, '%')
      OR p.per_apemat LIKE CONCAT('%', p_buscar, '%')
    )
  ORDER BY p.per_nombres, p.per_apepat;
END $$

DROP PROCEDURE IF EXISTS spu_trabajador_gra $$
CREATE PROCEDURE spu_trabajador_gra(
  IN p_tra_id INT,
  IN p_suc_id INT,
  IN p_esp_id INT,
  IN p_per_tipdoc VARCHAR(20),
  IN p_per_numdoc VARCHAR(20),
  IN p_per_apepat VARCHAR(100),
  IN p_per_apemat VARCHAR(100),
  IN p_per_nombres VARCHAR(150),
  IN p_per_celular VARCHAR(30),
  IN p_per_correo VARCHAR(150),
  IN p_tra_fecing DATE,
  IN p_tra_comision_base DECIMAL(5,2)
)
BEGIN
  DECLARE v_per_id INT;
  IF p_tra_id IS NULL OR p_tra_id = 0 THEN
    INSERT INTO personas(per_tipdoc, per_numdoc, per_apepat, per_apemat, per_nombres, per_celular, per_correo)
    VALUES(p_per_tipdoc, p_per_numdoc, p_per_apepat, p_per_apemat, p_per_nombres, p_per_celular, p_per_correo);
    SET v_per_id = LAST_INSERT_ID();
    INSERT INTO trabajadores(suc_id, per_id, esp_id, tra_fecing, tra_comision_base)
    VALUES(p_suc_id, v_per_id, p_esp_id, p_tra_fecing, p_tra_comision_base);
    SET p_tra_id = LAST_INSERT_ID();
  ELSE
    SELECT per_id INTO v_per_id FROM trabajadores WHERE tra_id = p_tra_id;
    UPDATE personas
       SET per_tipdoc = p_per_tipdoc,
           per_numdoc = p_per_numdoc,
           per_apepat = p_per_apepat,
           per_apemat = p_per_apemat,
           per_nombres = p_per_nombres,
           per_celular = p_per_celular,
           per_correo = p_per_correo
     WHERE per_id = v_per_id;
    UPDATE trabajadores
       SET suc_id = p_suc_id,
           esp_id = p_esp_id,
           tra_fecing = p_tra_fecing,
           tra_comision_base = p_tra_comision_base
     WHERE tra_id = p_tra_id;
  END IF;
  SELECT p_tra_id AS tra_id;
END $$

DROP PROCEDURE IF EXISTS spu_trabajador_lis $$
CREATE PROCEDURE spu_trabajador_lis(IN p_suc_id INT, IN p_activo TINYINT)
BEGIN
  SELECT t.tra_id, t.suc_id, t.esp_id, e.esp_nombre, t.tra_fecing, t.tra_comision_base, t.tra_activo, p.*
  FROM trabajadores t
  INNER JOIN personas p ON p.per_id = t.per_id
  LEFT JOIN especialidades e ON e.esp_id = t.esp_id
  WHERE (p_suc_id IS NULL OR p_suc_id = 0 OR t.suc_id = p_suc_id)
    AND (p_activo IS NULL OR t.tra_activo = p_activo)
  ORDER BY p.per_nombres, p.per_apepat;
END $$

DROP PROCEDURE IF EXISTS spu_servicio_gra $$
CREATE PROCEDURE spu_servicio_gra(
  IN p_ser_id INT,
  IN p_csv_id INT,
  IN p_ser_nombre VARCHAR(200),
  IN p_ser_precio DECIMAL(12,2),
  IN p_ser_comision_pct DECIMAL(5,2),
  IN p_ser_duracion_min INT,
  IN p_ser_activo TINYINT
)
BEGIN
  IF p_ser_id IS NULL OR p_ser_id = 0 THEN
    INSERT INTO servicios(csv_id, ser_nombre, ser_precio, ser_comision_pct, ser_duracion_min, ser_activo)
    VALUES(p_csv_id, p_ser_nombre, p_ser_precio, p_ser_comision_pct, p_ser_duracion_min, COALESCE(p_ser_activo, 1));
    SET p_ser_id = LAST_INSERT_ID();
  ELSE
    UPDATE servicios
       SET csv_id = p_csv_id,
           ser_nombre = p_ser_nombre,
           ser_precio = p_ser_precio,
           ser_comision_pct = p_ser_comision_pct,
           ser_duracion_min = p_ser_duracion_min,
           ser_activo = COALESCE(p_ser_activo, ser_activo)
     WHERE ser_id = p_ser_id;
  END IF;
  SELECT p_ser_id AS ser_id;
END $$

DROP PROCEDURE IF EXISTS spu_servicio_lis $$
CREATE PROCEDURE spu_servicio_lis(IN p_csv_id INT, IN p_activo TINYINT)
BEGIN
  SELECT s.*, c.csv_nombre
  FROM servicios s
  INNER JOIN categoria_servicio c ON c.csv_id = s.csv_id
  WHERE (p_csv_id IS NULL OR p_csv_id = 0 OR s.csv_id = p_csv_id)
    AND (p_activo IS NULL OR s.ser_activo = p_activo)
  ORDER BY c.csv_nombre, s.ser_nombre;
END $$

DROP PROCEDURE IF EXISTS spu_producto_gra $$
CREATE PROCEDURE spu_producto_gra(
  IN p_pro_id INT,
  IN p_cat_id INT,
  IN p_pro_nombre VARCHAR(250),
  IN p_pro_precio_compra DECIMAL(12,2),
  IN p_pro_precio_venta DECIMAL(12,2),
  IN p_pro_stock_actual DECIMAL(12,2),
  IN p_pro_stock_min DECIMAL(12,2),
  IN p_pro_activo TINYINT
)
BEGIN
  IF p_pro_id IS NULL OR p_pro_id = 0 THEN
    INSERT INTO productos(cat_id, pro_nombre, pro_precio_compra, pro_precio_venta, pro_stock_actual, pro_stock_min, pro_activo)
    VALUES(p_cat_id, p_pro_nombre, p_pro_precio_compra, p_pro_precio_venta, p_pro_stock_actual, p_pro_stock_min, COALESCE(p_pro_activo, 1));
    SET p_pro_id = LAST_INSERT_ID();
    IF COALESCE(p_pro_stock_actual, 0) > 0 THEN
      INSERT INTO movimiento_inventario(pro_id, mov_tipo, mov_cantidad, mov_observacion)
      VALUES(p_pro_id, 'ENTRADA', p_pro_stock_actual, 'Stock inicial');
    END IF;
  ELSE
    UPDATE productos
       SET cat_id = p_cat_id,
           pro_nombre = p_pro_nombre,
           pro_precio_compra = p_pro_precio_compra,
           pro_precio_venta = p_pro_precio_venta,
           pro_stock_min = p_pro_stock_min,
           pro_activo = COALESCE(p_pro_activo, pro_activo)
     WHERE pro_id = p_pro_id;
  END IF;
  SELECT p_pro_id AS pro_id;
END $$

DROP PROCEDURE IF EXISTS spu_producto_lis $$
CREATE PROCEDURE spu_producto_lis(IN p_cat_id INT, IN p_activo TINYINT)
BEGIN
  SELECT p.*, c.cat_nombre
  FROM productos p
  INNER JOIN categoria_producto c ON c.cat_id = p.cat_id
  WHERE (p_cat_id IS NULL OR p_cat_id = 0 OR p.cat_id = p_cat_id)
    AND (p_activo IS NULL OR p.pro_activo = p_activo)
  ORDER BY c.cat_nombre, p.pro_nombre;
END $$

DROP PROCEDURE IF EXISTS spu_catalogo_sel $$
CREATE PROCEDURE spu_catalogo_sel(IN p_catalogo VARCHAR(50))
BEGIN
  IF UPPER(p_catalogo) = 'ESPECIALIDADES' THEN
    SELECT esp_id AS id, esp_nombre AS nombre FROM especialidades ORDER BY esp_nombre;
  ELSEIF UPPER(p_catalogo) = 'CATEGORIA_SERVICIO' THEN
    SELECT csv_id AS id, csv_nombre AS nombre FROM categoria_servicio ORDER BY csv_nombre;
  ELSEIF UPPER(p_catalogo) = 'CATEGORIA_PRODUCTO' THEN
    SELECT cat_id AS id, cat_nombre AS nombre FROM categoria_producto ORDER BY cat_nombre;
  ELSEIF UPPER(p_catalogo) = 'METODOS_PAGO' THEN
    SELECT mpa_id AS id, mpa_nombre AS nombre FROM metodos_pago ORDER BY mpa_nombre;
  ELSEIF UPPER(p_catalogo) = 'SUCURSALES' THEN
    SELECT suc_id AS id, suc_nombre AS nombre FROM sucursales WHERE suc_activo = 1 ORDER BY suc_nombre;
  ELSE
    SELECT NULL AS id, 'CATALOGO_NO_VALIDO' AS nombre;
  END IF;
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
  INSERT INTO caja(suc_id, caj_fecha, caj_apertura, caj_estado)
  VALUES(p_suc_id, p_fecha, p_monto, 'ABIERTA')
  ON DUPLICATE KEY UPDATE caj_apertura = VALUES(caj_apertura), caj_estado = 'ABIERTA', caj_cierre = NULL;

  INSERT INTO caja_movimientos(caj_id, cam_tipo, cam_monto, cam_observacion)
  SELECT caj_id, 'APERTURA', p_monto, 'Apertura de caja'
  FROM caja
  WHERE suc_id = p_suc_id AND caj_fecha = p_fecha;

  SELECT * FROM caja WHERE suc_id = p_suc_id AND caj_fecha = p_fecha;
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);

  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 caj_apertura
       + COALESCE(SUM(CASE WHEN cam_tipo = 'INGRESO' THEN cam_monto WHEN cam_tipo = 'EGRESO' THEN -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' 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');

  SELECT * FROM caja WHERE caj_id = v_caj_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));

    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,
      NULLIF(v_tra_id, 0),
      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, t.tra_comision_base, 0),
         ROUND(vd.vde_total * COALESCE(s.ser_comision_pct, t.tra_comision_base, 0) / 100, 2)
  FROM venta_detalle vd
  INNER JOIN servicios s ON s.ser_id = vd.ser_id
  INNER JOIN trabajadores t ON t.tra_id = vd.tra_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_nombres, p.per_apepat, p.per_apemat) AS trabajador,
    COUNT(DISTINCT vd.vde_id) AS servicios,
    COALESCE(SUM(vd.vde_total), 0) AS venta,
    COALESCE(SUM(c.com_monto), 0) AS comision,
    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'
  LEFT JOIN comisiones c ON c.vde_id = vd.vde_id
  WHERE t.suc_id = p_suc_id
    AND (v.ven_id IS NULL OR DATE(v.ven_fecha) BETWEEN p_fecini AND p_fecfin)
  GROUP BY t.tra_id, trabajador
  ORDER BY venta 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
  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, COALESCE(SUM(vd.vde_total), 0) AS venta
  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'
  WHERE v.ven_id IS NULL OR DATE(v.ven_fecha) BETWEEN p_fecini AND p_fecfin
  GROUP BY s.ser_id, s.ser_nombre
  ORDER BY venta DESC;
END $$

DELIMITER ;