-- BI UBE V2 - Tablas adicionales
-- Ejecutar una sola vez en la base de datos woprescom_bi_ube

CREATE TABLE IF NOT EXISTS reglas_riesgo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(50) NOT NULL UNIQUE,
    nombre VARCHAR(150) NOT NULL,
    descripcion VARCHAR(255) NULL,
    puntaje INT NOT NULL DEFAULT 1,
    estado ENUM('ACTIVA','INACTIVA') NOT NULL DEFAULT 'ACTIVA',
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO reglas_riesgo (codigo,nombre,descripcion,puntaje,estado) VALUES
('ABANDONO','Riesgo de abandono','Manifestó haber pensado abandonar sus estudios.',4,'ACTIVA'),
('ESTRES','Estrés o ansiedad','Reportó estrés, ansiedad o preocupación con impacto académico.',3,'ACTIVA'),
('DESAPROBADOS','Unidades desaprobadas','Tiene tres o más unidades didácticas desaprobadas.',3,'ACTIVA'),
('SEGURO','Seguro de salud','No cuenta con seguro o el seguro no se encuentra activo.',2,'ACTIVA'),
('INGRESO','Ingreso familiar bajo','El ingreso familiar declarado se encuentra en un rango bajo.',2,'ACTIVA'),
('SALUD','Problema de salud','Reportó enfermedad o condición de salud diagnosticada.',2,'ACTIVA'),
('DISCAPACIDAD','Discapacidad','Reportó alguna discapacidad.',2,'ACTIVA')
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre), descripcion=VALUES(descripcion);

CREATE TABLE IF NOT EXISTS seguimientos_ube (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_estudiante INT NOT NULL,
    id_campana INT NULL,
    fecha DATE NOT NULL,
    tipo VARCHAR(80) NOT NULL,
    descripcion TEXT NOT NULL,
    acuerdos TEXT NULL,
    responsable VARCHAR(150) NULL,
    estado ENUM('PENDIENTE','EN PROCESO','CERRADO') NOT NULL DEFAULT 'PENDIENTE',
    proxima_fecha DATE NULL,
    id_usuario INT NULL,
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_seguimiento_estudiante (id_estudiante),
    INDEX idx_seguimiento_campana (id_campana),
    INDEX idx_seguimiento_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS historial_cambios (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tabla_afectada VARCHAR(80) NOT NULL,
    id_registro INT NULL,
    accion VARCHAR(40) NOT NULL,
    detalle TEXT NULL,
    id_usuario INT NULL,
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_historial_tabla (tabla_afectada),
    INDEX idx_historial_fecha (fecha_registro)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS normalizacion_ubicacion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipo ENUM('departamento','provincia','distrito','centro_poblado') NOT NULL,
    valor_original VARCHAR(200) NOT NULL,
    valor_normalizado VARCHAR(200) NOT NULL,
    estado ENUM('ACTIVA','INACTIVA') NOT NULL DEFAULT 'ACTIVA',
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_normalizacion (tipo, valor_original)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;