-- Complemento CNEB EBR para evaluación formativa y conclusiones descriptivas.
-- Adaptado de cneb_ebr_base_datos_mysql.sql sin eliminar ni recrear tablas productivas.
-- Usa las tablas cneb_* existentes del sistema COMANO.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS cneb_logro_calificacion (
  codigo VARCHAR(5) NOT NULL,
  nombre VARCHAR(80) NOT NULL,
  descripcion TEXT NULL,
  orden TINYINT UNSIGNED NOT NULL,
  puntaje_min DECIMAL(5,2) NULL,
  puntaje_max DECIMAL(5,2) NULL,
  activo TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (codigo),
  UNIQUE KEY uq_cneb_logro_orden (orden)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cneb_logro_calificacion
(codigo, nombre, descripcion, orden, puntaje_min, puntaje_max, activo)
VALUES
('AD','Logro destacado','El estudiante evidencia un nivel superior a lo esperado respecto a la competencia.',1,18.00,20.00,1),
('A','Logro esperado','El estudiante evidencia el nivel esperado respecto a la competencia.',2,14.00,17.99,1),
('B','En proceso','El estudiante está próximo o cerca al nivel esperado y requiere acompañamiento durante un tiempo razonable.',3,11.00,13.99,1),
('C','En inicio','El estudiante muestra un progreso mínimo y requiere mayor tiempo de acompañamiento e intervención docente.',4,0.00,10.99,1)
ON DUPLICATE KEY UPDATE
  nombre = VALUES(nombre),
  descripcion = VALUES(descripcion),
  orden = VALUES(orden),
  puntaje_min = VALUES(puntaje_min),
  puntaje_max = VALUES(puntaje_max),
  activo = VALUES(activo);

ALTER TABLE cneb_competencia
  ADD COLUMN IF NOT EXISTS numero_cneb TINYINT UNSIGNED NULL AFTER codigo,
  ADD COLUMN IF NOT EXISTS codigo_script VARCHAR(40) NULL AFTER numero_cneb;

UPDATE cneb_competencia
SET
  numero_cneb = CASE codigo
    WHEN 'CONSTRUYE_IDENTIDAD' THEN 1
    WHEN 'MOTRICIDAD' THEN 2
    WHEN 'VIDA_SALUDABLE' THEN 3
    WHEN 'HABILIDADES_SOCIOMOTRICES' THEN 4
    WHEN 'APRECIA_ARTE' THEN 5
    WHEN 'CREA_ARTE' THEN 6
    WHEN 'COM_ORAL_LM' THEN 7
    WHEN 'LEE_LM' THEN 8
    WHEN 'ESCRIBE_LM' THEN 9
    WHEN 'COM_ORAL_CAST2' THEN 10
    WHEN 'LEE_CAST2' THEN 11
    WHEN 'ESCRIBE_CAST2' THEN 12
    WHEN 'COM_ORAL_INGLES' THEN 13
    WHEN 'LEE_INGLES' THEN 14
    WHEN 'ESCRIBE_INGLES' THEN 15
    WHEN 'CONVIVE_PARTICIPA' THEN 16
    WHEN 'INTERPRETACIONES_HISTORICAS' THEN 17
    WHEN 'GESTIONA_ESPACIO_AMBIENTE' THEN 18
    WHEN 'GESTIONA_RECURSOS_ECONOMICOS' THEN 19
    WHEN 'INDAGA' THEN 20
    WHEN 'EXPLICA_MUNDO' THEN 21
    WHEN 'SOLUCIONES_TEC' THEN 22
    WHEN 'MAT_CANTIDAD' THEN 23
    WHEN 'MAT_REGULARIDAD' THEN 24
    WHEN 'MAT_FORMA' THEN 25
    WHEN 'MAT_DATOS' THEN 26
    WHEN 'EMPRENDIMIENTO' THEN 27
    WHEN 'TIC' THEN 28
    WHEN 'AUTOAPRENDIZAJE' THEN 29
    WHEN 'RELIG_IDENTIDAD' THEN 30
    WHEN 'REL_EXPERIENCIA' THEN 31
    ELSE numero_cneb
  END,
  codigo_script = CASE codigo
    WHEN 'CONSTRUYE_IDENTIDAD' THEN 'C01_IDENTIDAD'
    WHEN 'MOTRICIDAD' THEN 'C02_MOTRICIDAD'
    WHEN 'VIDA_SALUDABLE' THEN 'C03_VIDA_SALUDABLE'
    WHEN 'HABILIDADES_SOCIOMOTRICES' THEN 'C04_SOCIOMOTRIZ'
    WHEN 'APRECIA_ARTE' THEN 'C05_APRECIA_ARTE'
    WHEN 'CREA_ARTE' THEN 'C06_CREA_ARTE'
    WHEN 'COM_ORAL_LM' THEN 'C07_ORAL_LM'
    WHEN 'LEE_LM' THEN 'C08_LEE_LM'
    WHEN 'ESCRIBE_LM' THEN 'C09_ESCRIBE_LM'
    WHEN 'COM_ORAL_CAST2' THEN 'C10_ORAL_CASTELLANO_SL'
    WHEN 'LEE_CAST2' THEN 'C11_LEE_CASTELLANO_SL'
    WHEN 'ESCRIBE_CAST2' THEN 'C12_ESCRIBE_CASTELLANO_SL'
    WHEN 'COM_ORAL_INGLES' THEN 'C13_ORAL_INGLES'
    WHEN 'LEE_INGLES' THEN 'C14_LEE_INGLES'
    WHEN 'ESCRIBE_INGLES' THEN 'C15_ESCRIBE_INGLES'
    WHEN 'CONVIVE_PARTICIPA' THEN 'C16_CONVIVE_DEMOCRATICAMENTE'
    WHEN 'INTERPRETACIONES_HISTORICAS' THEN 'C17_INTERPRETACIONES_HISTORICAS'
    WHEN 'GESTIONA_ESPACIO_AMBIENTE' THEN 'C18_GESTIONA_ESPACIO_AMBIENTE'
    WHEN 'GESTIONA_RECURSOS_ECONOMICOS' THEN 'C19_GESTIONA_RECURSOS_ECONOMICOS'
    WHEN 'INDAGA' THEN 'C20_INDAGA'
    WHEN 'EXPLICA_MUNDO' THEN 'C21_EXPLICA_MUNDO_FISICO'
    WHEN 'SOLUCIONES_TEC' THEN 'C22_SOLUCIONES_TECNOLOGICAS'
    WHEN 'MAT_CANTIDAD' THEN 'C23_CANTIDAD'
    WHEN 'MAT_REGULARIDAD' THEN 'C24_REGULARIDAD_EQUIVALENCIA_CAMBIO'
    WHEN 'MAT_FORMA' THEN 'C25_FORMA_MOVIMIENTO_LOCALIZACION'
    WHEN 'MAT_DATOS' THEN 'C26_DATOS_INCERTIDUMBRE'
    WHEN 'EMPRENDIMIENTO' THEN 'C27_EMPRENDIMIENTO'
    WHEN 'TIC' THEN 'C28_TIC'
    WHEN 'AUTOAPRENDIZAJE' THEN 'C29_APRENDIZAJE_AUTONOMO'
    WHEN 'RELIG_IDENTIDAD' THEN 'C30_IDENTIDAD_RELIGIOSA'
    WHEN 'REL_EXPERIENCIA' THEN 'C31_ENCUENTRO_DIOS'
    ELSE codigo_script
  END
WHERE codigo IN (
  'CONSTRUYE_IDENTIDAD','MOTRICIDAD','VIDA_SALUDABLE','HABILIDADES_SOCIOMOTRICES',
  'APRECIA_ARTE','CREA_ARTE','COM_ORAL_LM','LEE_LM','ESCRIBE_LM',
  'COM_ORAL_CAST2','LEE_CAST2','ESCRIBE_CAST2','COM_ORAL_INGLES','LEE_INGLES',
  'ESCRIBE_INGLES','CONVIVE_PARTICIPA','INTERPRETACIONES_HISTORICAS',
  'GESTIONA_ESPACIO_AMBIENTE','GESTIONA_RECURSOS_ECONOMICOS','INDAGA',
  'EXPLICA_MUNDO','SOLUCIONES_TEC','MAT_CANTIDAD','MAT_REGULARIDAD',
  'MAT_FORMA','MAT_DATOS','EMPRENDIMIENTO','TIC','AUTOAPRENDIZAJE',
  'RELIG_IDENTIDAD','REL_EXPERIENCIA'
);

CREATE TABLE IF NOT EXISTS cneb_regla_conclusion (
  id_cneb_regla_conclusion INT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_cneb_nivel INT UNSIGNED NOT NULL,
  logro_codigo VARCHAR(5) NOT NULL,
  requiere_conclusion TINYINT(1) NOT NULL DEFAULT 0,
  descripcion VARCHAR(300) NOT NULL,
  fuente VARCHAR(180) NOT NULL DEFAULT 'CNEB EBR - Evaluación formativa',
  activo TINYINT(1) NOT NULL DEFAULT 1,
  actualizado_en TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_cneb_regla_conclusion),
  UNIQUE KEY uq_cneb_regla_nivel_logro (id_cneb_nivel, logro_codigo),
  KEY idx_cneb_regla_logro (logro_codigo),
  CONSTRAINT fk_cneb_regla_nivel FOREIGN KEY (id_cneb_nivel) REFERENCES cneb_nivel(id_cneb_nivel),
  CONSTRAINT fk_cneb_regla_logro FOREIGN KEY (logro_codigo) REFERENCES cneb_logro_calificacion(codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cneb_regla_conclusion
(id_cneb_nivel, logro_codigo, requiere_conclusion, descripcion, fuente, activo)
SELECT
  n.id_cneb_nivel,
  l.codigo,
  CASE
    WHEN n.codigo IN ('INI','PRI') AND l.codigo IN ('B','C') THEN 1
    WHEN n.codigo = 'SEC' AND l.codigo = 'C' THEN 1
    ELSE 0
  END,
  CASE
    WHEN n.codigo IN ('INI','PRI') AND l.codigo IN ('B','C') THEN 'Generar conclusión descriptiva obligatoria para logros B y C en Inicial y Primaria.'
    WHEN n.codigo = 'SEC' AND l.codigo = 'C' THEN 'Generar conclusión descriptiva obligatoria para logro C en Secundaria.'
    ELSE 'No requiere conclusión descriptiva obligatoria; puede registrarse como retroalimentación complementaria.'
  END,
  'CNEB EBR - Evaluación formativa',
  1
FROM cneb_nivel n
CROSS JOIN cneb_logro_calificacion l
WHERE 1 = 1
ON DUPLICATE KEY UPDATE
  requiere_conclusion = VALUES(requiere_conclusion),
  descripcion = VALUES(descripcion),
  fuente = VALUES(fuente),
  activo = VALUES(activo);

CREATE TABLE IF NOT EXISTS cneb_plantilla_conclusion (
  id_cneb_plantilla_conclusion INT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_cneb_nivel INT UNSIGNED NOT NULL,
  logro_codigo VARCHAR(5) NOT NULL,
  tipo ENUM('GENERAL','AREA','COMPETENCIA') NOT NULL DEFAULT 'GENERAL',
  scope_key VARCHAR(120) NOT NULL DEFAULT 'GENERAL',
  id_cneb_area INT UNSIGNED NULL,
  id_cneb_competencia INT UNSIGNED NULL,
  texto_inicio TEXT NOT NULL,
  texto_recomendacion TEXT NOT NULL,
  fuente VARCHAR(180) NOT NULL DEFAULT 'CNEB EBR - Evaluación formativa',
  activo TINYINT(1) NOT NULL DEFAULT 1,
  actualizado_en TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_cneb_plantilla_conclusion),
  UNIQUE KEY uq_cneb_plantilla_scope (id_cneb_nivel, logro_codigo, tipo, scope_key),
  KEY idx_cneb_plantilla_logro (logro_codigo),
  KEY idx_cneb_plantilla_area (id_cneb_area),
  KEY idx_cneb_plantilla_competencia (id_cneb_competencia),
  CONSTRAINT fk_cneb_plantilla_nivel FOREIGN KEY (id_cneb_nivel) REFERENCES cneb_nivel(id_cneb_nivel),
  CONSTRAINT fk_cneb_plantilla_logro FOREIGN KEY (logro_codigo) REFERENCES cneb_logro_calificacion(codigo),
  CONSTRAINT fk_cneb_plantilla_area FOREIGN KEY (id_cneb_area) REFERENCES cneb_area(id_cneb_area),
  CONSTRAINT fk_cneb_plantilla_competencia FOREIGN KEY (id_cneb_competencia) REFERENCES cneb_competencia(id_cneb_competencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO cneb_plantilla_conclusion
(id_cneb_nivel, logro_codigo, tipo, scope_key, texto_inicio, texto_recomendacion, fuente, activo)
SELECT
  n.id_cneb_nivel,
  l.codigo,
  'GENERAL',
  'GENERAL',
  CASE
    WHEN n.codigo IN ('INI','PRI') AND l.codigo = 'B' THEN 'se encuentra en proceso de desarrollar la competencia'
    WHEN n.codigo IN ('INI','PRI') AND l.codigo = 'C' THEN 'presenta dificultades para iniciar el desarrollo esperado de la competencia'
    WHEN n.codigo = 'SEC' AND l.codigo = 'C' THEN 'evidencia dificultades significativas para alcanzar el estándar esperado de la competencia'
    ELSE 'presenta avance en la competencia'
  END,
  CASE
    WHEN l.codigo = 'B' THEN 'Se recomienda brindar acompañamiento oportuno, actividades graduadas, retroalimentación específica y oportunidades de práctica vinculadas a situaciones significativas.'
    WHEN l.codigo = 'C' THEN 'Se recomienda aplicar refuerzo personalizado, mediación permanente, actividades diferenciadas, participación familiar y seguimiento continuo hasta evidenciar progreso.'
    ELSE 'Continuar fortaleciendo sus aprendizajes mediante retos adecuados al nivel esperado.'
  END,
  'CNEB EBR - Evaluación formativa',
  1
FROM cneb_nivel n
JOIN cneb_logro_calificacion l
WHERE (n.codigo IN ('INI','PRI') AND l.codigo IN ('B','C'))
   OR (n.codigo = 'SEC' AND l.codigo = 'C')
ON DUPLICATE KEY UPDATE
  texto_inicio = VALUES(texto_inicio),
  texto_recomendacion = VALUES(texto_recomendacion),
  fuente = VALUES(fuente),
  activo = VALUES(activo);

CREATE TABLE IF NOT EXISTS cneb_conclusion_generada (
  id_cneb_conclusion_generada BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_nota INT UNSIGNED NULL,
  id_auxiliar_sesion_estudiante BIGINT UNSIGNED NULL,
  conclusion_sugerida TEXT NOT NULL,
  conclusion_final TEXT NULL,
  estado ENUM('SUGERIDA','EDITADA','APROBADA') NOT NULL DEFAULT 'SUGERIDA',
  usuario_generacion VARCHAR(120) NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_cneb_conclusion_generada),
  KEY idx_cneb_conclusion_nota (id_nota),
  KEY idx_cneb_conclusion_auxiliar (id_auxiliar_sesion_estudiante),
  CONSTRAINT fk_cneb_conclusion_nota FOREIGN KEY (id_nota) REFERENCES nota_estudiante(id_nota) ON DELETE SET NULL,
  CONSTRAINT fk_cneb_conclusion_auxiliar FOREIGN KEY (id_auxiliar_sesion_estudiante) REFERENCES eval_auxiliar_sesion_estudiante(id_auxiliar_sesion_estudiante) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE OR REPLACE VIEW vw_cneb_reglas_conclusion AS
SELECT
  r.id_cneb_regla_conclusion,
  n.codigo AS nivel_codigo,
  n.nombre AS nivel,
  l.codigo AS logro_codigo,
  l.nombre AS logro,
  r.requiere_conclusion,
  r.descripcion,
  r.fuente,
  r.activo
FROM cneb_regla_conclusion r
INNER JOIN cneb_nivel n ON n.id_cneb_nivel = r.id_cneb_nivel
INNER JOIN cneb_logro_calificacion l ON l.codigo = r.logro_codigo;

CREATE OR REPLACE VIEW vw_malla_curricular AS
SELECT
  n.codigo AS nivel_codigo,
  n.nombre AS nivel,
  ci.codigo AS ciclo_codigo,
  ci.nombre AS ciclo,
  a.codigo AS area_codigo,
  a.nombre AS area,
  co.numero_cneb,
  co.codigo_script,
  co.codigo AS competencia_codigo,
  co.nombre AS competencia,
  cap.codigo AS capacidad_codigo,
  cap.nombre AS capacidad,
  cap.orden AS capacidad_orden,
  es.nivel_estandar,
  es.descripcion AS estandar,
  f.documento AS fuente
FROM cneb_area_competencia ac
INNER JOIN cneb_nivel n ON n.id_cneb_nivel = ac.id_cneb_nivel
LEFT JOIN cneb_ciclo ci ON ci.id_cneb_ciclo = ac.id_cneb_ciclo
INNER JOIN cneb_area a ON a.id_cneb_area = ac.id_cneb_area
INNER JOIN cneb_competencia co ON co.id_cneb_competencia = ac.id_cneb_competencia
LEFT JOIN cneb_capacidad cap ON cap.id_cneb_competencia = co.id_cneb_competencia
LEFT JOIN cneb_estandar es
  ON es.id_cneb_competencia = co.id_cneb_competencia
 AND (es.id_cneb_ciclo <=> ac.id_cneb_ciclo)
LEFT JOIN cneb_fuente f ON f.id_cneb_fuente = COALESCE(es.id_cneb_fuente, ac.id_cneb_fuente)
ORDER BY n.id_cneb_nivel, ci.id_cneb_ciclo, ac.orden, cap.orden;

CREATE OR REPLACE VIEW vw_evaluaciones_conclusion_obligatoria AS
SELECT
  ne.id_nota AS id_evaluacion,
  ne.id_nota,
  m.id_matricula,
  COALESCE(NULLIF(p.nombre_completo, ''), TRIM(CONCAT_WS(' ', p.apellido_paterno, p.apellido_materno, p.nombres))) AS estudiante,
  m.id_anio_lectivo AS anio,
  g.descripcion AS grado,
  s.descripcion AS seccion,
  pa.descripcion AS periodo,
  cn.codigo AS nivel_codigo,
  cn.nombre AS nivel,
  ci.codigo AS ciclo_codigo,
  ar.nombre AS area,
  c.descripcion AS competencia,
  ne.nota_literal AS calificacion,
  l.nombre AS logro,
  rc.requiere_conclusion,
  ne.conclusion_descriptiva,
  ne.observacion,
  CASE
    WHEN COALESCE(rc.requiere_conclusion, 0) = 0 THEN
      CONCAT('La calificación ', ne.nota_literal, ' no requiere conclusión descriptiva obligatoria para ', cn.nombre, '.')
    ELSE
      CONCAT(
        COALESCE(NULLIF(p.nombres, ''), COALESCE(NULLIF(p.nombre_completo, ''), 'El/la estudiante')), ' ',
        COALESCE(pc.texto_inicio, 'requiere acompañamiento para fortalecer la competencia'), ' "',
        COALESCE(cc.nombre, c.descripcion), '" en el área de ', ar.nombre, '. ',
        'Según el estándar del ciclo ', COALESCE(ci.codigo, '-'), ': ', COALESCE(ce.descripcion, 'estándar CNEB no vinculado'), '. ',
        'Capacidades a fortalecer: ',
        COALESCE((
          SELECT GROUP_CONCAT(cap.nombre ORDER BY cap.orden SEPARATOR '; ')
          FROM cneb_capacidad cap
          WHERE cap.id_cneb_competencia = cc.id_cneb_competencia
        ), 'capacidades no vinculadas'), '. ',
        COALESCE(pc.texto_recomendacion, 'Brindar retroalimentación específica y seguimiento oportuno.')
      )
  END AS conclusion_sugerida
FROM nota_estudiante ne
INNER JOIN matricula m ON m.id_matricula = ne.id_matricula
INNER JOIN aula au ON au.id_aula = m.id_aula
INNER JOIN grado g ON g.id_grado = au.id_grado
INNER JOIN seccion s ON s.id_seccion = au.id_seccion
INNER JOIN estudiante e ON e.id_estudiante = m.id_estudiante
INNER JOIN persona p ON p.id_persona = e.id_persona
INNER JOIN periodo_academico pa ON pa.id_periodo_academico = ne.id_periodo_academico
INNER JOIN area_curricular ar ON ar.id_area = ne.id_area
LEFT JOIN competencia c ON c.id_competencia = ne.id_competencia
LEFT JOIN cneb_competencia cc ON cc.id_cneb_competencia = c.id_cneb_competencia
LEFT JOIN cneb_nivel cn ON cn.codigo = au.id_nivel
LEFT JOIN cneb_ciclo ci ON ci.codigo = CASE
  WHEN au.id_nivel = 'INI' THEN 'II'
  WHEN au.id_nivel = 'PRI' AND au.id_grado IN (1,2) THEN 'III'
  WHEN au.id_nivel = 'PRI' AND au.id_grado IN (3,4) THEN 'IV'
  WHEN au.id_nivel = 'PRI' AND au.id_grado IN (5,6) THEN 'V'
  WHEN au.id_nivel = 'SEC' AND au.id_grado IN (7,8) THEN 'VI'
  WHEN au.id_nivel = 'SEC' AND au.id_grado IN (9,10,11) THEN 'VII'
  ELSE NULL
END
LEFT JOIN cneb_logro_calificacion l ON l.codigo = ne.nota_literal
LEFT JOIN cneb_regla_conclusion rc ON rc.id_cneb_nivel = cn.id_cneb_nivel AND rc.logro_codigo = ne.nota_literal AND rc.activo = 1
LEFT JOIN cneb_plantilla_conclusion pc
  ON pc.id_cneb_nivel = cn.id_cneb_nivel
 AND pc.logro_codigo = ne.nota_literal
 AND pc.tipo = 'GENERAL'
 AND pc.scope_key = 'GENERAL'
 AND pc.activo = 1
LEFT JOIN cneb_estandar ce ON ce.id_cneb_competencia = cc.id_cneb_competencia AND ce.id_cneb_ciclo = ci.id_cneb_ciclo
WHERE COALESCE(rc.requiere_conclusion, 0) = 1;

DROP PROCEDURE IF EXISTS sp_sugerir_conclusion_manual;
DROP PROCEDURE IF EXISTS sp_cneb_sugerir_conclusion_nota;

DELIMITER $$

CREATE PROCEDURE sp_sugerir_conclusion_manual(
  IN p_nivel_codigo VARCHAR(20),
  IN p_ciclo_codigo VARCHAR(10),
  IN p_competencia_codigo VARCHAR(100),
  IN p_logro_codigo VARCHAR(5),
  IN p_nombre_estudiante VARCHAR(250)
)
BEGIN
  SELECT
    p_nombre_estudiante AS estudiante,
    n.nombre AS nivel,
    ci.codigo AS ciclo,
    co.nombre AS competencia,
    l.codigo AS calificacion,
    COALESCE(rc.requiere_conclusion, 0) AS requiere_conclusion,
    CASE
      WHEN COALESCE(rc.requiere_conclusion, 0) = 0 THEN
        CONCAT('Según la regla configurada, la calificación ', l.codigo, ' en ', n.nombre, ' no requiere conclusión descriptiva obligatoria.')
      ELSE
        CONCAT(
          p_nombre_estudiante, ' ',
          COALESCE(pc.texto_inicio, 'requiere acompañamiento para fortalecer la competencia'), ' "',
          co.nombre, '". ',
          'De acuerdo con el estándar del ciclo ', ci.codigo, ': ', COALESCE(ce.descripcion, 'estándar CNEB no vinculado'), '. ',
          'Capacidades asociadas: ',
          COALESCE((
            SELECT GROUP_CONCAT(cap.nombre ORDER BY cap.orden SEPARATOR '; ')
            FROM cneb_capacidad cap
            WHERE cap.id_cneb_competencia = co.id_cneb_competencia
          ), 'capacidades no vinculadas'), '. ',
          COALESCE(pc.texto_recomendacion, 'Brindar retroalimentación específica y seguimiento oportuno.')
        )
    END AS conclusion_sugerida
  FROM cneb_nivel n
  INNER JOIN cneb_ciclo ci ON ci.codigo = p_ciclo_codigo
  INNER JOIN cneb_competencia co ON co.codigo = p_competencia_codigo OR co.codigo_script = p_competencia_codigo
  INNER JOIN cneb_logro_calificacion l ON l.codigo = p_logro_codigo
  LEFT JOIN cneb_regla_conclusion rc ON rc.id_cneb_nivel = n.id_cneb_nivel AND rc.logro_codigo = l.codigo AND rc.activo = 1
  LEFT JOIN cneb_plantilla_conclusion pc
    ON pc.id_cneb_nivel = n.id_cneb_nivel
   AND pc.logro_codigo = l.codigo
   AND pc.tipo = 'GENERAL'
   AND pc.scope_key = 'GENERAL'
   AND pc.activo = 1
  LEFT JOIN cneb_estandar ce ON ce.id_cneb_competencia = co.id_cneb_competencia AND ce.id_cneb_ciclo = ci.id_cneb_ciclo
  WHERE n.codigo = CASE UPPER(p_nivel_codigo)
    WHEN 'INICIAL' THEN 'INI'
    WHEN 'PRIMARIA' THEN 'PRI'
    WHEN 'SECUNDARIA' THEN 'SEC'
    ELSE UPPER(p_nivel_codigo)
  END
  LIMIT 1;
END$$

CREATE PROCEDURE sp_cneb_sugerir_conclusion_nota(IN p_id_nota INT UNSIGNED)
BEGIN
  SELECT
    v.id_nota,
    v.estudiante,
    v.nivel,
    v.ciclo_codigo AS ciclo,
    v.area,
    v.competencia,
    v.calificacion,
    v.requiere_conclusion,
    v.conclusion_descriptiva AS conclusion_actual,
    v.conclusion_sugerida
  FROM vw_evaluaciones_conclusion_obligatoria v
  WHERE v.id_nota = p_id_nota
  LIMIT 1;
END$$

DELIMITER ;