-- Parche adaptado a COMANO: conclusiones descriptivas maximo 150 caracteres.
-- Basado en C:\Users\Alvert\Downloads\parche_conclusiones_max150.sql.

DROP PROCEDURE IF EXISTS sp_sugerir_conclusion_manual;
DROP PROCEDURE IF EXISTS sp_sugerir_conclusion_por_evaluacion;
DROP PROCEDURE IF EXISTS sp_cneb_sugerir_conclusion_nota;
DROP VIEW IF EXISTS vw_evaluaciones_conclusion_obligatoria;
DROP FUNCTION IF EXISTS fn_limitar_texto_150;

DELIMITER $$
CREATE FUNCTION fn_limitar_texto_150(p_texto TEXT)
RETURNS VARCHAR(150)
DETERMINISTIC
BEGIN
  DECLARE v_texto TEXT;
  DECLARE v_corte VARCHAR(150);

  SET v_texto = TRIM(REPLACE(REPLACE(REPLACE(IFNULL(p_texto, ''), CHAR(13), ' '), CHAR(10), ' '), '  ', ' '));

  WHILE LOCATE('  ', v_texto) > 0 DO
    SET v_texto = REPLACE(v_texto, '  ', ' ');
  END WHILE;

  IF CHAR_LENGTH(v_texto) <= 150 THEN
    RETURN v_texto;
  END IF;

  SET v_corte = LEFT(v_texto, 150);

  IF LOCATE(' ', REVERSE(v_corte)) > 0 THEN
    SET v_corte = LEFT(v_corte, 150 - LOCATE(' ', REVERSE(v_corte)));
  END IF;

  RETURN TRIM(v_corte);
END$$
DELIMITER ;

UPDATE cneb_conclusion_generada
SET conclusion_sugerida = fn_limitar_texto_150(conclusion_sugerida),
    conclusion_final = CASE
      WHEN conclusion_final IS NULL THEN NULL
      ELSE fn_limitar_texto_150(conclusion_final)
    END;

ALTER TABLE cneb_conclusion_generada
  MODIFY conclusion_sugerida VARCHAR(150) NOT NULL,
  MODIFY conclusion_final VARCHAR(150) NULL;

UPDATE nota_estudiante
SET conclusion_descriptiva = CASE
    WHEN CHAR_LENGTH(fn_limitar_texto_150(conclusion_descriptiva)) = 0 THEN NULL
    ELSE fn_limitar_texto_150(conclusion_descriptiva)
  END
WHERE conclusion_descriptiva IS NOT NULL;

UPDATE nota_transversal_docente
SET conclusion_descriptiva = CASE
    WHEN CHAR_LENGTH(fn_limitar_texto_150(conclusion_descriptiva)) = 0 THEN NULL
    ELSE fn_limitar_texto_150(conclusion_descriptiva)
  END
WHERE conclusion_descriptiva IS NOT NULL;

UPDATE siagie_importacion_detalle
SET conclusion_descriptiva = CASE
    WHEN CHAR_LENGTH(fn_limitar_texto_150(conclusion_descriptiva)) = 0 THEN NULL
    ELSE fn_limitar_texto_150(conclusion_descriptiva)
  END
WHERE conclusion_descriptiva IS NOT NULL;

ALTER TABLE nota_estudiante
  MODIFY conclusion_descriptiva VARCHAR(150) NULL;

ALTER TABLE nota_transversal_docente
  MODIFY conclusion_descriptiva VARCHAR(150) NULL;

ALTER TABLE siagie_importacion_detalle
  MODIFY conclusion_descriptiva VARCHAR(150) NULL;

UPDATE cneb_plantilla_conclusion pc
INNER JOIN cneb_nivel n ON n.id_cneb_nivel = pc.id_cneb_nivel
SET pc.texto_inicio = CASE
      WHEN n.codigo IN ('INI','PRI') AND pc.logro_codigo = 'B' THEN 'esta en proceso en'
      WHEN n.codigo IN ('INI','PRI') AND pc.logro_codigo = 'C' THEN 'requiere apoyo en'
      WHEN n.codigo = 'SEC' AND pc.logro_codigo = 'C' THEN 'requiere refuerzo en'
      ELSE 'presenta avance en'
    END,
    pc.texto_recomendacion = CASE
      WHEN pc.logro_codigo = 'B' THEN 'Practica guiada y retroalimentacion.'
      WHEN pc.logro_codigo = 'C' THEN 'Apoyo personalizado y seguimiento.'
      ELSE 'Continuar fortaleciendo sus aprendizajes.'
    END
WHERE pc.tipo = 'GENERAL'
  AND pc.scope_key = 'GENERAL';

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,
  fn_limitar_texto_150(
    CASE
      WHEN COALESCE(rc.requiere_conclusion, 0) = 0 THEN
        CONCAT('La calificacion ', ne.nota_literal, ' no requiere conclusion descriptiva obligatoria en ', cn.nombre, '.')
      ELSE
        CONCAT(
          COALESCE(NULLIF(p.nombres, ''), COALESCE(NULLIF(p.nombre_completo, ''), 'El/la estudiante')), ' ',
          COALESCE(pc.texto_inicio, 'requiere acompanamiento en'), ' ',
          COALESCE(cc.nombre, c.descripcion), ' segun estandar del ciclo ', COALESCE(ci.codigo, '-'), '. ',
          COALESCE(pc.texto_recomendacion, 'Brindar retroalimentacion especifica.')
        )
    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
WHERE COALESCE(rc.requiere_conclusion, 0) = 1;

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_calificacion_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,
    fn_limitar_texto_150(
      CASE
        WHEN COALESCE(rc.requiere_conclusion, 0) = 0 THEN
          CONCAT('La calificacion ', l.codigo, ' no requiere conclusion descriptiva obligatoria en ', n.nombre, '.')
        ELSE
          CONCAT(
            p_nombre_estudiante, ' ', COALESCE(pc.texto_inicio, 'requiere acompanamiento en'), ' ', co.nombre,
            ' segun estandar del ciclo ', ci.codigo, '. ',
            COALESCE(pc.texto_recomendacion, 'Brindar retroalimentacion especifica.')
          )
      END
    ) AS conclusion_sugerida,
    CHAR_LENGTH(fn_limitar_texto_150(
      CASE
        WHEN COALESCE(rc.requiere_conclusion, 0) = 0 THEN
          CONCAT('La calificacion ', l.codigo, ' no requiere conclusion descriptiva obligatoria en ', n.nombre, '.')
        ELSE
          CONCAT(
            p_nombre_estudiante, ' ', COALESCE(pc.texto_inicio, 'requiere acompanamiento en'), ' ', co.nombre,
            ' segun estandar del ciclo ', ci.codigo, '. ',
            COALESCE(pc.texto_recomendacion, 'Brindar retroalimentacion especifica.')
          )
      END
    )) AS total_caracteres
  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_calificacion_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
  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_sugerir_conclusion_por_evaluacion(IN p_id_evaluacion BIGINT UNSIGNED)
BEGIN
  SELECT
    v.id_nota AS id_evaluacion,
    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,
    CHAR_LENGTH(v.conclusion_sugerida) AS total_caracteres
  FROM vw_evaluaciones_conclusion_obligatoria v
  WHERE v.id_nota = p_id_evaluacion
  LIMIT 1;
END$$

CREATE PROCEDURE sp_cneb_sugerir_conclusion_nota(IN p_id_nota INT UNSIGNED)
BEGIN
  CALL sp_sugerir_conclusion_por_evaluacion(p_id_nota);
END$$
DELIMITER ;