ALTER TABLE nota_estudiante
  MODIFY origen_registro ENUM('MANUAL','SIAGIE','MIGRACION','CONSOLIDADO','AUXILIAR') NOT NULL DEFAULT 'MANUAL';

CREATE TABLE IF NOT EXISTS eval_auxiliar_sesion (
  id_auxiliar_sesion BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  id_periodo_academico SMALLINT UNSIGNED NOT NULL,
  id_docente INT UNSIGNED NULL,
  id_aula INT UNSIGNED NOT NULL,
  id_area INT UNSIGNED NOT NULL,
  id_competencia INT UNSIGNED NOT NULL,
  unidad_numero TINYINT UNSIGNED NOT NULL DEFAULT 1,
  unidad_titulo VARCHAR(180) NULL,
  sesion_numero SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  sesion_titulo VARCHAR(220) NOT NULL,
  fecha DATE NOT NULL,
  capacidades TEXT NULL,
  proposito TEXT NULL,
  evidencia TEXT NULL,
  instrumento VARCHAR(140) NULL,
  estado ENUM('BORRADOR','CERRADO') NOT NULL DEFAULT 'CERRADO',
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_auxiliar_sesion),
  KEY idx_eval_aux_sesion_scope (id_periodo_academico, id_aula, id_area, id_competencia),
  KEY idx_eval_aux_sesion_docente (id_docente, id_anio_lectivo, fecha),
  KEY idx_eval_aux_sesion_fecha (fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS eval_auxiliar_sesion_estudiante (
  id_auxiliar_sesion_estudiante BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_auxiliar_sesion BIGINT UNSIGNED NOT NULL,
  id_matricula INT UNSIGNED NOT NULL,
  nivel_logro ENUM('AD','A','B','C') NULL,
  puntaje DECIMAL(5,2) NULL,
  evidencia TEXT NULL,
  retroalimentacion TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_auxiliar_sesion_estudiante),
  UNIQUE KEY uq_eval_aux_sesion_estudiante (id_auxiliar_sesion, id_matricula),
  KEY idx_eval_aux_det_matricula (id_matricula),
  CONSTRAINT fk_eval_aux_det_sesion
    FOREIGN KEY (id_auxiliar_sesion)
    REFERENCES eval_auxiliar_sesion (id_auxiliar_sesion)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;