-- Notificaciones Telegram a representantes — colegio jesistem_juanxxiii (id_colegio 11)
--
-- Aplicar manualmente contra la BD del colegio:
--   mysql -u root jesistem_juanxxiii < db/colegios/colegiojuanxxiii-telegram.sql
--
-- Ver documentacion/telegram-notificaciones.md para la arquitectura completa,
-- el mantenimiento que hay que hacer cada año escolar y cómo onboardear otro colegio.

-- 1. Vínculo Telegram por representante (un representante puede tener varios alumnos;
--    el vínculo se hace una sola vez por cédula de representante, no por alumno).
ALTER TABLE represe
  ADD COLUMN telegram_chat_id VARCHAR(32) NULL AFTER etnia_rep,
  ADD COLUMN telegram_linked_at DATETIME NULL AFTER telegram_chat_id;

-- 2. Cola de notificaciones pendientes de envío (drenada por telegram-enviar-notificaciones.php)
CREATE TABLE notif_telegram_queue (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ced_rep VARCHAR(11) NOT NULL,
  tipo VARCHAR(20) NOT NULL,
  mensaje TEXT NOT NULL,
  origen_id INT NULL,
  creado DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  enviado TINYINT NOT NULL DEFAULT 0,
  enviado_en DATETIME NULL,
  intentos INT NOT NULL DEFAULT 0,
  error VARCHAR(255) NULL,
  INDEX idx_ced_rep (ced_rep),
  INDEX idx_pendientes (enviado, id)
);

-- 3. Seguimiento de boletines ya notificados (el chequeo es por fecha, no por trigger;
--    ver telegram-enviar-notificaciones.php). Evita re-notificar el mismo lapso cada día.
ALTER TABLE preinscripcion
  ADD COLUMN notif_boleta_valor DATE NULL,
  ADD COLUMN notif_primaria_valor DATE NULL;

-- 4. Trigger: evaluación cargada por un docente.
--    evalua_calendario NO cambia de nombre entre años escolares → este trigger se crea
--    una sola vez y no requiere mantenimiento anual.
DELIMITER $$
CREATE TRIGGER trg_evalua_calendario_notif
AFTER INSERT ON evalua_calendario
FOR EACH ROW
BEGIN
  INSERT INTO notif_telegram_queue (ced_rep, tipo, mensaje, origen_id)
  SELECT DISTINCT R.cedula, 'evaluacion',
    CONCAT('📝 Nueva evaluación para ', A.nombre, ' ', A.apellido, ': "', NEW.titulo, '" el ', DATE_FORMAT(NEW.fecha, '%d/%m/%Y'), '.'),
    NEW.id
  FROM alumcer A
  JOIN represe R ON R.cedula = A.ced_rep
  WHERE A.grado = NEW.grado AND A.seccion = NEW.seccion AND R.telegram_chat_id IS NOT NULL;
END$$
DELIMITER ;

-- 5. Trigger: nota cargada/actualizada en un corte.
--    ¡ATENCIÓN! cortes2627/materiass2627 son tablas del período 2026-2027. Cada año escolar
--    hay que DROP + CREATE de este trigger apuntando a cortes<nuevoPeriodo>/materiass<nuevoPeriodo>.
--    Ver el checklist anual en documentacion/telegram-notificaciones.md.
DELIMITER $$
CREATE TRIGGER trg_cortes2627_notif
AFTER UPDATE ON cortes2627
FOR EACH ROW
BEGIN
  IF CONCAT_WS('|', NEW.nota11,NEW.nota21,NEW.nota31,NEW.nota41,NEW.nota51,NEW.nota61,
                    NEW.nota12,NEW.nota22,NEW.nota32,NEW.nota42,NEW.nota52,NEW.nota62,
                    NEW.nota13,NEW.nota23,NEW.nota33,NEW.nota43,NEW.nota53,NEW.nota63)
     <> CONCAT_WS('|', OLD.nota11,OLD.nota21,OLD.nota31,OLD.nota41,OLD.nota51,OLD.nota61,
                       OLD.nota12,OLD.nota22,OLD.nota32,OLD.nota42,OLD.nota52,OLD.nota62,
                       OLD.nota13,OLD.nota23,OLD.nota33,OLD.nota43,OLD.nota53,OLD.nota63)
  THEN
    INSERT INTO notif_telegram_queue (ced_rep, tipo, mensaje, origen_id)
    SELECT R.cedula, 'nota',
      CONCAT('📊 Se actualizaron notas de ', A.nombre, ' ', A.apellido, ' en ', M.nombremate, '.'),
      NEW.id
    FROM alumcer A
    JOIN represe R ON R.cedula = A.ced_rep
    JOIN materiass2627 M ON M.codigo = NEW.cod_materia
    WHERE A.cedula = NEW.ced_alu AND R.telegram_chat_id IS NOT NULL;
  END IF;
END$$
DELIMITER ;
