-- SIRGILA analítico: modelo 3FN no destructivo.
-- Ejecutar primero en una copia de la base tenant y conciliar resultados.

CREATE TABLE IF NOT EXISTS sirgila_nivel_logro (
    codigo VARCHAR(2) NOT NULL,
    nombre VARCHAR(80) NOT NULL,
    orden TINYINT UNSIGNED NOT NULL,
    es_logro TINYINT(1) NOT NULL DEFAULT 0,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (codigo),
    UNIQUE KEY uq_sirgila_nivel_logro_orden (orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO sirgila_nivel_logro (codigo, nombre, orden, es_logro, activo) VALUES
('AD', 'Logro destacado', 1, 1, 1),
('A', 'Logro esperado', 2, 1, 1),
('B', 'En proceso', 3, 0, 1),
('C', 'En inicio', 4, 0, 1)
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre), orden = VALUES(orden), es_logro = VALUES(es_logro), activo = 1;

CREATE TABLE IF NOT EXISTS sirgila_tipo_evaluacion (
    id_tipo_evaluacion SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    codigo VARCHAR(30) NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    orden TINYINT UNSIGNED NOT NULL DEFAULT 0,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id_tipo_evaluacion),
    UNIQUE KEY uq_sirgila_tipo_evaluacion_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO sirgila_tipo_evaluacion (codigo, nombre, orden, activo) VALUES
('ANUAL', 'Resultado anual', 1, 1),
('BIMESTRE', 'Resultado por bimestre', 2, 1),
('DIAGNOSTICA', 'Evaluación diagnóstica', 3, 1),
('PROCESO', 'Evaluación de proceso', 4, 1),
('SALIDA', 'Evaluación de salida', 5, 1)
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre), orden = VALUES(orden), activo = 1;

CREATE TABLE IF NOT EXISTS sirgila_configuracion_meta (
    id_configuracion BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_institucion SMALLINT UNSIGNED NOT NULL,
    peso_anio_1 DECIMAL(6,5) NOT NULL DEFAULT 0.20000,
    peso_anio_2 DECIMAL(6,5) NOT NULL DEFAULT 0.30000,
    peso_anio_3 DECIMAL(6,5) NOT NULL DEFAULT 0.50000,
    peso_tendencia_1 DECIMAL(6,5) NOT NULL DEFAULT 0.40000,
    peso_tendencia_2 DECIMAL(6,5) NOT NULL DEFAULT 0.60000,
    factor_brecha DECIMAL(6,5) NOT NULL DEFAULT 0.15000,
    factor_tendencia_positiva DECIMAL(6,5) NOT NULL DEFAULT 0.50000,
    incremento_minimo DECIMAL(5,2) NOT NULL DEFAULT 2.00,
    incremento_maximo DECIMAL(5,2) NOT NULL DEFAULT 8.00,
    reparto_ad DECIMAL(6,5) NOT NULL DEFAULT 0.35000,
    reparto_a DECIMAL(6,5) NOT NULL DEFAULT 0.65000,
    tolerancia_suma DECIMAL(5,2) NOT NULL DEFAULT 0.20,
    activo TINYINT(1) NOT NULL DEFAULT 1,
    actualizado_por INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_configuracion),
    UNIQUE KEY uq_sirgila_configuracion_institucion (id_institucion),
    CONSTRAINT fk_sirgila_configuracion_institucion FOREIGN KEY (id_institucion) REFERENCES institucion_educativa (id_institucion),
    CONSTRAINT fk_sirgila_configuracion_usuario FOREIGN KEY (actualizado_por) REFERENCES usuario_sistema (id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_importacion_lote (
    id_lote BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_institucion SMALLINT UNSIGNED NOT NULL,
    anio SMALLINT UNSIGNED NOT NULL,
    id_nivel VARCHAR(10) NOT NULL,
    revision SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    nombre_archivo VARCHAR(255) NOT NULL,
    hash_archivo CHAR(64) NOT NULL,
    version_plantilla VARCHAR(30) NULL,
    estado ENUM('CARGADO','VALIDADO','RECHAZADO','CONFIRMADO','ANULADO') NOT NULL DEFAULT 'CARGADO',
    filas_leidas INT UNSIGNED NOT NULL DEFAULT 0,
    filas_validas INT UNSIGNED NOT NULL DEFAULT 0,
    filas_rechazadas INT UNSIGNED NOT NULL DEFAULT 0,
    resumen_errores_json LONGTEXT NULL,
    creado_por INT UNSIGNED NULL,
    confirmado_por INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    confirmado_en DATETIME NULL,
    PRIMARY KEY (id_lote),
    UNIQUE KEY uq_sirgila_lote_revision (id_institucion, anio, id_nivel, revision),
    KEY idx_sirgila_lote_estado (id_institucion, estado, anio),
    CONSTRAINT fk_sirgila_lote_institucion FOREIGN KEY (id_institucion) REFERENCES institucion_educativa (id_institucion),
    CONSTRAINT fk_sirgila_lote_creador FOREIGN KEY (creado_por) REFERENCES usuario_sistema (id_usuario),
    CONSTRAINT fk_sirgila_lote_confirmador FOREIGN KEY (confirmado_por) REFERENCES usuario_sistema (id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_corte_evaluacion (
    id_corte BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_lote BIGINT UNSIGNED NULL,
    id_institucion SMALLINT UNSIGNED NOT NULL,
    anio SMALLINT UNSIGNED NOT NULL,
    id_nivel VARCHAR(10) NOT NULL,
    id_grado INT UNSIGNED NOT NULL,
    id_seccion INT UNSIGNED NULL,
    id_tipo_evaluacion SMALLINT UNSIGNED NOT NULL,
    numero_periodo TINYINT UNSIGNED NULL,
    contexto_hash CHAR(64) NOT NULL,
    estudiantes_matriculados INT UNSIGNED NOT NULL DEFAULT 0,
    estudiantes_evaluados INT UNSIGNED NOT NULL DEFAULT 0,
    confirmado TINYINT(1) NOT NULL DEFAULT 0,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_corte),
    UNIQUE KEY uq_sirgila_corte_contexto (contexto_hash),
    KEY idx_sirgila_corte_filtros (id_institucion, anio, id_nivel, id_grado, numero_periodo),
    CONSTRAINT fk_sirgila_corte_lote FOREIGN KEY (id_lote) REFERENCES sirgila_importacion_lote (id_lote),
    CONSTRAINT fk_sirgila_corte_institucion FOREIGN KEY (id_institucion) REFERENCES institucion_educativa (id_institucion),
    CONSTRAINT fk_sirgila_corte_tipo FOREIGN KEY (id_tipo_evaluacion) REFERENCES sirgila_tipo_evaluacion (id_tipo_evaluacion)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_analitica_resultado_competencia (
    id_resultado BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_corte BIGINT UNSIGNED NOT NULL,
    id_area INT UNSIGNED NOT NULL,
    id_competencia INT UNSIGNED NOT NULL,
    evaluados INT UNSIGNED NOT NULL DEFAULT 0,
    sin_calificacion INT UNSIGNED NOT NULL DEFAULT 0,
    cobertura DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_resultado),
    UNIQUE KEY uq_sirgila_resultado_competencia (id_corte, id_competencia),
    KEY idx_sirgila_resultado_area (id_area, id_competencia),
    CONSTRAINT fk_sirgila_analitica_resultado_corte FOREIGN KEY (id_corte) REFERENCES sirgila_corte_evaluacion (id_corte),
    CONSTRAINT fk_sirgila_analitica_resultado_area FOREIGN KEY (id_area) REFERENCES area_curricular (id_area),
    CONSTRAINT fk_sirgila_analitica_resultado_comp FOREIGN KEY (id_competencia) REFERENCES competencia (id_competencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_analitica_resultado_nivel_logro (
    id_resultado BIGINT UNSIGNED NOT NULL,
    codigo_nivel_logro VARCHAR(2) NOT NULL,
    cantidad INT UNSIGNED NOT NULL DEFAULT 0,
    porcentaje DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id_resultado, codigo_nivel_logro),
    CONSTRAINT fk_sirgila_analitica_nivel_resultado FOREIGN KEY (id_resultado) REFERENCES sirgila_analitica_resultado_competencia (id_resultado) ON DELETE CASCADE,
    CONSTRAINT fk_sirgila_analitica_nivel_catalogo FOREIGN KEY (codigo_nivel_logro) REFERENCES sirgila_nivel_logro (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_meta (
    id_meta BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_institucion SMALLINT UNSIGNED NOT NULL,
    anio_objetivo SMALLINT UNSIGNED NOT NULL,
    id_nivel VARCHAR(10) NOT NULL,
    id_grado INT UNSIGNED NOT NULL,
    id_area INT UNSIGNED NOT NULL,
    id_competencia INT UNSIGNED NOT NULL,
    escenario ENUM('CONSERVADOR','RECOMENDADO','DESAFIANTE','MANUAL') NOT NULL,
    version SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    estado ENUM('PROPUESTA','AJUSTADA','APROBADA','RECHAZADA') NOT NULL DEFAULT 'PROPUESTA',
    contexto_hash CHAR(64) NOT NULL,
    es_actual TINYINT(1) NOT NULL DEFAULT 1,
    anio_base SMALLINT UNSIGNED NOT NULL,
    logro_ponderado DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    logro_actual DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    tendencia DECIMAL(7,2) NOT NULL DEFAULT 0.00,
    brecha DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    incremento DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    variabilidad DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    confianza ENUM('ALTA','MEDIA','BAJA') NOT NULL DEFAULT 'BAJA',
    prioridad ENUM('ALTA','MEDIA','BAJA') NOT NULL DEFAULT 'MEDIA',
    parametros_json LONGTEXT NULL,
    justificacion TEXT NULL,
    creado_por INT UNSIGNED NULL,
    aprobado_por INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    aprobado_en DATETIME NULL,
    PRIMARY KEY (id_meta),
    UNIQUE KEY uq_sirgila_meta_version (contexto_hash, version),
    KEY idx_sirgila_meta_consulta (id_institucion, anio_objetivo, id_nivel, id_grado, estado, es_actual),
    CONSTRAINT fk_sirgila_meta_institucion FOREIGN KEY (id_institucion) REFERENCES institucion_educativa (id_institucion),
    CONSTRAINT fk_sirgila_meta_area FOREIGN KEY (id_area) REFERENCES area_curricular (id_area),
    CONSTRAINT fk_sirgila_meta_competencia FOREIGN KEY (id_competencia) REFERENCES competencia (id_competencia),
    CONSTRAINT fk_sirgila_meta_creador FOREIGN KEY (creado_por) REFERENCES usuario_sistema (id_usuario),
    CONSTRAINT fk_sirgila_meta_aprobador FOREIGN KEY (aprobado_por) REFERENCES usuario_sistema (id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_meta_nivel_logro (
    id_meta BIGINT UNSIGNED NOT NULL,
    codigo_nivel_logro VARCHAR(2) NOT NULL,
    porcentaje DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id_meta, codigo_nivel_logro),
    CONSTRAINT fk_sirgila_meta_nivel_meta FOREIGN KEY (id_meta) REFERENCES sirgila_meta (id_meta) ON DELETE CASCADE,
    CONSTRAINT fk_sirgila_meta_nivel_catalogo FOREIGN KEY (codigo_nivel_logro) REFERENCES sirgila_nivel_logro (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_meta_auditoria (
    id_auditoria BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_meta BIGINT UNSIGNED NOT NULL,
    evento ENUM('CALCULADA','EDITADA','APROBADA','RECHAZADA','RESTAURADA') NOT NULL,
    valor_anterior_json LONGTEXT NULL,
    valor_nuevo_json LONGTEXT NULL,
    justificacion TEXT NULL,
    id_usuario INT UNSIGNED NULL,
    ip VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_auditoria),
    KEY idx_sirgila_meta_auditoria (id_meta, creado_en),
    CONSTRAINT fk_sirgila_meta_auditoria_meta FOREIGN KEY (id_meta) REFERENCES sirgila_meta (id_meta),
    CONSTRAINT fk_sirgila_meta_auditoria_usuario FOREIGN KEY (id_usuario) REFERENCES usuario_sistema (id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_plan_mejora (
    id_plan BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_meta BIGINT UNSIGNED NOT NULL,
    titulo VARCHAR(220) NOT NULL,
    diagnostico TEXT NULL,
    objetivo TEXT NULL,
    estado ENUM('BORRADOR','APROBADO','EN_EJECUCION','CERRADO') NOT NULL DEFAULT 'BORRADOR',
    creado_por INT UNSIGNED NULL,
    aprobado_por INT UNSIGNED NULL,
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_plan),
    UNIQUE KEY uq_sirgila_plan_meta (id_meta),
    CONSTRAINT fk_sirgila_plan_meta FOREIGN KEY (id_meta) REFERENCES sirgila_meta (id_meta),
    CONSTRAINT fk_sirgila_plan_creador FOREIGN KEY (creado_por) REFERENCES usuario_sistema (id_usuario),
    CONSTRAINT fk_sirgila_plan_aprobador FOREIGN KEY (aprobado_por) REFERENCES usuario_sistema (id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sirgila_plan_accion (
    id_accion BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    id_plan BIGINT UNSIGNED NOT NULL,
    orden SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    accion TEXT NOT NULL,
    responsable VARCHAR(180) NOT NULL,
    fecha_inicio DATE NULL,
    fecha_fin DATE NULL,
    indicador VARCHAR(300) NULL,
    evidencia VARCHAR(300) NULL,
    avance DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    estado ENUM('PENDIENTE','EN_PROCESO','CULMINADA','CANCELADA') NOT NULL DEFAULT 'PENDIENTE',
    creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id_accion),
    KEY idx_sirgila_plan_accion_estado (id_plan, estado, orden),
    CONSTRAINT fk_sirgila_plan_accion_plan FOREIGN KEY (id_plan) REFERENCES sirgila_plan_mejora (id_plan) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;