-- Sistema Médico - Actualización para Facturación
-- Nuevas tablas para productos, servicios, facturas y roles

USE centrobd;

-- Actualizar tabla de usuarios para incluir rol de colaborador
ALTER TABLE usuarios 
MODIFY COLUMN rol ENUM('admin', 'medico', 'colaborador') DEFAULT 'medico';

-- Tabla de categorías de productos/servicios
CREATE TABLE IF NOT EXISTS categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    tipo ENUM('producto', 'servicio') NOT NULL,
    descripcion TEXT,
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tipo (tipo),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de productos
CREATE TABLE IF NOT EXISTS productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT,
    codigo VARCHAR(50) UNIQUE,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    precio DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    stock_minimo INT DEFAULT 0,
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE SET NULL,
    INDEX idx_codigo (codigo),
    INDEX idx_nombre (nombre),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de servicios
CREATE TABLE IF NOT EXISTS servicios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    categoria_id INT,
    codigo VARCHAR(50) UNIQUE,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    precio DECIMAL(10,2) NOT NULL,
    duracion_estimada INT, -- en minutos
    activo BOOLEAN DEFAULT TRUE,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id) ON DELETE SET NULL,
    INDEX idx_codigo (codigo),
    INDEX idx_nombre (nombre),
    INDEX idx_activo (activo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de facturas
CREATE TABLE IF NOT EXISTS facturas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_factura VARCHAR(50) UNIQUE NOT NULL,
    paciente_id INT NOT NULL,
    usuario_id INT NOT NULL, -- quien emitió la factura
    consulta_id INT, -- opcional, si está asociada a una consulta
    fecha_emision DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
    descuento DECIMAL(10,2) DEFAULT 0,
    impuesto DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) NOT NULL,
    estado ENUM('pendiente', 'pagada', 'cancelada', 'anulada') DEFAULT 'pendiente',
    metodo_pago ENUM('efectivo', 'tarjeta', 'transferencia', 'cheque', 'otro'),
    observaciones TEXT,
    fecha_pago DATETIME,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (paciente_id) REFERENCES pacientes(id) ON DELETE RESTRICT,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    FOREIGN KEY (consulta_id) REFERENCES consultas(id) ON DELETE SET NULL,
    INDEX idx_numero_factura (numero_factura),
    INDEX idx_paciente (paciente_id),
    INDEX idx_fecha_emision (fecha_emision),
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de detalle de facturas (productos)
CREATE TABLE IF NOT EXISTS factura_productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    factura_id INT NOT NULL,
    producto_id INT NOT NULL,
    cantidad INT NOT NULL DEFAULT 1,
    precio_unitario DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (factura_id) REFERENCES facturas(id) ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE RESTRICT,
    INDEX idx_factura (factura_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de detalle de facturas (servicios)
CREATE TABLE IF NOT EXISTS factura_servicios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    factura_id INT NOT NULL,
    servicio_id INT NOT NULL,
    cantidad INT NOT NULL DEFAULT 1,
    precio_unitario DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (factura_id) REFERENCES facturas(id) ON DELETE CASCADE,
    FOREIGN KEY (servicio_id) REFERENCES servicios(id) ON DELETE RESTRICT,
    INDEX idx_factura (factura_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de historial de cambios de precios
CREATE TABLE IF NOT EXISTS historial_precios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('producto', 'servicio') NOT NULL,
    item_id INT NOT NULL,
    precio_anterior DECIMAL(10,2) NOT NULL,
    precio_nuevo DECIMAL(10,2) NOT NULL,
    usuario_id INT NOT NULL,
    motivo TEXT,
    fecha_cambio TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    INDEX idx_tipo_item (tipo, item_id),
    INDEX idx_fecha (fecha_cambio)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar categorías por defecto
INSERT INTO categorias (nombre, tipo, descripcion) VALUES
('Medicamentos', 'producto', 'Medicamentos y fármacos'),
('Material Médico', 'producto', 'Material y equipamiento médico'),
('Insumos', 'producto', 'Insumos médicos generales'),
('Consultas', 'servicio', 'Servicios de consulta médica'),
('Procedimientos', 'servicio', 'Procedimientos médicos'),
('Análisis', 'servicio', 'Análisis y estudios clínicos'),
('Terapias', 'servicio', 'Servicios de terapia y rehabilitación');

-- Insertar productos de ejemplo
INSERT INTO productos (categoria_id, codigo, nombre, descripcion, precio, stock, stock_minimo) VALUES
(1, 'MED-001', 'Paracetamol 500mg', 'Analgésico y antipirético', 5000.00, 100, 20),
(1, 'MED-002', 'Ibuprofeno 400mg', 'Antiinflamatorio', 7500.00, 80, 15),
(1, 'MED-003', 'Amoxicilina 500mg', 'Antibiótico', 15000.00, 50, 10),
(2, 'MAT-001', 'Jeringa 5ml', 'Jeringa desechable', 2000.00, 200, 50),
(2, 'MAT-002', 'Guantes látex (caja)', 'Caja de 100 unidades', 25000.00, 30, 10),
(3, 'INS-001', 'Gasas estériles', 'Paquete de 10 unidades', 3000.00, 150, 30);

-- Insertar servicios de ejemplo
INSERT INTO servicios (categoria_id, codigo, nombre, descripcion, precio, duracion_estimada) VALUES
(4, 'SRV-001', 'Consulta General', 'Consulta médica general', 50000.00, 30),
(4, 'SRV-002', 'Consulta Especializada', 'Consulta con especialista', 80000.00, 45),
(5, 'SRV-003', 'Curaciones', 'Servicio de curaciones', 30000.00, 20),
(5, 'SRV-004', 'Inyección', 'Aplicación de inyección', 15000.00, 10),
(6, 'SRV-005', 'Análisis de Sangre', 'Análisis de sangre completo', 60000.00, 15),
(6, 'SRV-006', 'Electrocardiograma', 'ECG completo', 45000.00, 20),
(7, 'SRV-007', 'Fisioterapia (sesión)', 'Sesión de fisioterapia', 40000.00, 60);

-- Insertar usuario colaborador de ejemplo
INSERT INTO usuarios (usuario, contrasena, nombre_completo, rol) VALUES 
('colaborador', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Colaborador de Facturación', 'colaborador');

-- Nota: La contraseña por defecto es 'medico' (mismo hash que el usuario 'centro')
-- Se recomienda cambiarla después de la instalación

-- Crear vista para reportes de recaudación diaria
CREATE OR REPLACE VIEW reporte_recaudacion_diaria AS
SELECT 
    DATE(f.fecha_emision) as fecha,
    COUNT(f.id) as total_facturas,
    SUM(CASE WHEN f.estado = 'pagada' THEN 1 ELSE 0 END) as facturas_pagadas,
    SUM(CASE WHEN f.estado = 'pendiente' THEN 1 ELSE 0 END) as facturas_pendientes,
    SUM(CASE WHEN f.estado = 'pagada' THEN f.total ELSE 0 END) as total_recaudado,
    SUM(CASE WHEN f.estado = 'pendiente' THEN f.total ELSE 0 END) as total_pendiente,
    SUM(f.total) as total_general
FROM facturas f
GROUP BY DATE(f.fecha_emision)
ORDER BY fecha DESC;

-- Crear vista para reportes de recaudación mensual
CREATE OR REPLACE VIEW reporte_recaudacion_mensual AS
SELECT 
    YEAR(f.fecha_emision) as anio,
    MONTH(f.fecha_emision) as mes,
    COUNT(f.id) as total_facturas,
    SUM(CASE WHEN f.estado = 'pagada' THEN 1 ELSE 0 END) as facturas_pagadas,
    SUM(CASE WHEN f.estado = 'pagada' THEN f.total ELSE 0 END) as total_recaudado,
    SUM(CASE WHEN f.estado = 'pendiente' THEN f.total ELSE 0 END) as total_pendiente
FROM facturas f
GROUP BY YEAR(f.fecha_emision), MONTH(f.fecha_emision)
ORDER BY anio DESC, mes DESC;

-- Crear vista para productos más vendidos
CREATE OR REPLACE VIEW productos_mas_vendidos AS
SELECT 
    p.id,
    p.codigo,
    p.nombre,
    SUM(fp.cantidad) as total_vendido,
    SUM(fp.subtotal) as total_ingresos
FROM productos p
INNER JOIN factura_productos fp ON p.id = fp.producto_id
INNER JOIN facturas f ON fp.factura_id = f.id
WHERE f.estado = 'pagada'
GROUP BY p.id, p.codigo, p.nombre
ORDER BY total_vendido DESC;

-- Crear vista para servicios más solicitados
CREATE OR REPLACE VIEW servicios_mas_solicitados AS
SELECT 
    s.id,
    s.codigo,
    s.nombre,
    SUM(fs.cantidad) as total_solicitado,
    SUM(fs.subtotal) as total_ingresos
FROM servicios s
INNER JOIN factura_servicios fs ON s.id = fs.servicio_id
INNER JOIN facturas f ON fs.factura_id = f.id
WHERE f.estado = 'pagada'
GROUP BY s.id, s.codigo, s.nombre
ORDER BY total_solicitado DESC;
