-- =============================================
-- MIGRACIÓN: Sistema de Pagos y Portal del Paciente
-- Fecha: 2024-02-17
-- =============================================

USE cibertronia_salud;

-- =============================================
-- TABLA: pagos_examenes
-- Sistema de pago para exámenes médicos
-- =============================================
CREATE TABLE IF NOT EXISTS pagos_examenes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    examen_id INT NOT NULL,
    paciente_id INT NOT NULL,
    
    -- Información del Pago
    monto DECIMAL(10,2) NOT NULL,
    metodo_pago ENUM('efectivo', 'tarjeta', 'transferencia', 'seguro', 'otro') NOT NULL,
    estado_pago ENUM('pendiente', 'pagado', 'parcial', 'cancelado', 'reembolsado') DEFAULT 'pendiente',
    fecha_pago DATETIME NULL,
    
    -- Comprobantes
    numero_comprobante VARCHAR(100) NULL,
    archivo_comprobante VARCHAR(255) NULL,
    
    -- Referencias
    procesado_por INT NULL COMMENT 'ID del usuario que procesó el pago',
    observaciones TEXT NULL,
    
    -- Auditoría
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_examen (examen_id),
    INDEX idx_paciente (paciente_id),
    INDEX idx_estado_pago (estado_pago),
    INDEX idx_fecha_pago (fecha_pago),
    
    -- Relaciones
    FOREIGN KEY (examen_id) REFERENCES examenes_medicos(id) ON DELETE CASCADE,
    FOREIGN KEY (paciente_id) REFERENCES pacientes(id) ON DELETE CASCADE,
    FOREIGN KEY (procesado_por) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TABLA: pacientes_usuario
-- Credenciales de acceso para el portal del paciente
-- =============================================
CREATE TABLE IF NOT EXISTS pacientes_usuario (
    id INT AUTO_INCREMENT PRIMARY KEY,
    paciente_id INT NOT NULL UNIQUE,
    
    -- Credenciales
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    
    -- Control de Acceso
    activo BOOLEAN DEFAULT TRUE,
    email_verificado BOOLEAN DEFAULT FALSE,
    token_verificacion VARCHAR(100) NULL,
    
    -- Seguridad
    ultimo_acceso TIMESTAMP NULL,
    intentos_fallidos INT DEFAULT 0,
    bloqueado_hasta TIMESTAMP NULL,
    
    -- Auditoría
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_paciente (paciente_id),
    INDEX idx_email (email),
    INDEX idx_activo (activo),
    
    -- Relaciones
    FOREIGN KEY (paciente_id) REFERENCES pacientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- TABLA: pacientes_sesiones
-- Control de sesiones del portal del paciente
-- =============================================
CREATE TABLE IF NOT EXISTS pacientes_sesiones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    paciente_usuario_id INT NOT NULL,
    token VARCHAR(255) UNIQUE NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    expira_en TIMESTAMP NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_token (token),
    INDEX idx_paciente_usuario (paciente_usuario_id),
    INDEX idx_expira_en (expira_en),
    
    FOREIGN KEY (paciente_usuario_id) REFERENCES pacientes_usuario(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================
-- VISTA: Exámenes con Estado de Pago
-- =============================================
CREATE OR REPLACE VIEW vista_examenes_con_pago AS
SELECT 
    e.id,
    e.paciente_id,
    CONCAT(p.nombre, ' ', p.apellido) AS paciente_nombre,
    p.numero_historia,
    e.tipo_examen,
    e.descripcion,
    e.fecha_solicitud,
    e.fecha_realizacion,
    e.laboratorio_centro,
    e.archivo_resultado,
    e.estado AS estado_examen,
    COALESCE(pg.monto, 0) AS precio,
    COALESCE(pg.estado_pago, 'pendiente') AS estado_pago,
    pg.metodo_pago,
    pg.fecha_pago,
    CONCAT(u.nombre, ' ', u.apellido) AS medico_solicitante
FROM examenes_medicos e
INNER JOIN pacientes p ON e.paciente_id = p.id
INNER JOIN usuarios u ON e.medico_solicitante_id = u.id
LEFT JOIN pagos_examenes pg ON e.id = pg.examen_id
ORDER BY e.fecha_solicitud DESC;

-- =============================================
-- PROCEDIMIENTO: Registrar Pago de Examen
-- =============================================
DELIMITER //

DROP PROCEDURE IF EXISTS registrar_pago_examen//

CREATE PROCEDURE registrar_pago_examen(
    IN p_examen_id INT,
    IN p_paciente_id INT,
    IN p_monto DECIMAL(10,2),
    IN p_metodo_pago VARCHAR(50),
    IN p_procesado_por INT,
    IN p_numero_comprobante VARCHAR(100)
)
BEGIN
    DECLARE pago_existente INT DEFAULT 0;
    
    -- Verificar si ya existe un pago para este examen
    SELECT COUNT(*) INTO pago_existente
    FROM pagos_examenes
    WHERE examen_id = p_examen_id;
    
    IF pago_existente > 0 THEN
        -- Actualizar pago existente
        UPDATE pagos_examenes
        SET estado_pago = 'pagado',
            fecha_pago = NOW(),
            monto = p_monto,
            metodo_pago = p_metodo_pago,
            procesado_por = p_procesado_por,
            numero_comprobante = p_numero_comprobante
        WHERE examen_id = p_examen_id;
        
        SELECT 'Pago actualizado exitosamente' AS mensaje, LAST_INSERT_ID() AS pago_id;
    ELSE
        -- Insertar nuevo pago
        INSERT INTO pagos_examenes (
            examen_id,
            paciente_id,
            monto,
            metodo_pago,
            estado_pago,
            fecha_pago,
            procesado_por,
            numero_comprobante
        ) VALUES (
            p_examen_id,
            p_paciente_id,
            p_monto,
            p_metodo_pago,
            'pagado',
            NOW(),
            p_procesado_por,
            p_numero_comprobante
        );
        
        SELECT 'Pago registrado exitosamente' AS mensaje, LAST_INSERT_ID() AS pago_id;
    END IF;
    
    -- Actualizar estado del examen
    UPDATE examenes_medicos
    SET estado = 'en_proceso'
    WHERE id = p_examen_id AND estado = 'solicitado';
END//

DELIMITER ;

-- =============================================
-- PROCEDIMIENTO: Crear Usuario para Paciente
-- =============================================
DELIMITER //

DROP PROCEDURE IF EXISTS crear_usuario_paciente//

CREATE PROCEDURE crear_usuario_paciente(
    IN p_paciente_id INT,
    IN p_email VARCHAR(150),
    IN p_password VARCHAR(255)
)
BEGIN
    DECLARE usuario_existe INT DEFAULT 0;
    
    -- Verificar si ya existe un usuario para este paciente
    SELECT COUNT(*) INTO usuario_existe
    FROM pacientes_usuario
    WHERE paciente_id = p_paciente_id;
    
    IF usuario_existe > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'El paciente ya tiene un usuario registrado';
    ELSE
        INSERT INTO pacientes_usuario (
            paciente_id,
            email,
            password,
            activo,
            email_verificado
        ) VALUES (
            p_paciente_id,
            p_email,
            p_password,
            TRUE,
            FALSE
        );
        
        SELECT 'Usuario creado exitosamente' AS mensaje, LAST_INSERT_ID() AS usuario_id;
    END IF;
END//

DELIMITER ;

-- =============================================
-- DATOS DE EJEMPLO
-- =============================================

-- Crear usuarios para pacientes existentes
-- NOTA: La contraseña es: 'paciente123' (hasheada con bcrypt)
INSERT INTO pacientes_usuario (paciente_id, email, password, activo, email_verificado)
SELECT id, email, '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', TRUE, TRUE
FROM pacientes
WHERE email IS NOT NULL AND email != ''
ON DUPLICATE KEY UPDATE paciente_id = paciente_id;

-- Crear pagos de ejemplo para exámenes
INSERT INTO pagos_examenes (examen_id, paciente_id, monto, metodo_pago, estado_pago, fecha_pago, procesado_por)
SELECT 
    e.id,
    e.paciente_id,
    CASE 
        WHEN e.tipo_examen LIKE '%sangre%' OR e.tipo_examen LIKE '%hemograma%' THEN 45.00
        WHEN e.tipo_examen LIKE '%orina%' THEN 25.00
        WHEN e.tipo_examen LIKE '%rayos%' THEN 60.00
        ELSE 50.00
    END AS monto,
    'efectivo' AS metodo_pago,
    CASE 
        WHEN e.estado = 'completado' THEN 'pagado'
        ELSE 'pendiente'
    END AS estado_pago,
    CASE 
        WHEN e.estado = 'completado' THEN e.fecha_realizacion
        ELSE NULL
    END AS fecha_pago,
    1 AS procesado_por
FROM examenes_medicos e
WHERE NOT EXISTS (
    SELECT 1 FROM pagos_examenes WHERE examen_id = e.id
);

-- =============================================
-- VERIFICACIÓN
-- =============================================
SELECT 'Migración completada exitosamente' AS mensaje;
SELECT COUNT(*) AS total_pagos FROM pagos_examenes;
SELECT COUNT(*) AS total_usuarios_pacientes FROM pacientes_usuario;
SELECT COUNT(*) AS total_sesiones FROM pacientes_sesiones;
