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

CREATE TABLE IF NOT EXISTS nota_transversal_docente (
  id_nota_transversal BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  id_periodo_academico SMALLINT UNSIGNED NOT NULL,
  id_aula INT UNSIGNED NOT NULL,
  id_docente INT UNSIGNED NOT NULL,
  id_area_fuente INT UNSIGNED NOT NULL,
  id_matricula INT UNSIGNED NOT NULL,
  id_area_transversal INT UNSIGNED NOT NULL,
  id_competencia_transversal INT UNSIGNED NOT NULL,
  nota_literal VARCHAR(5) NOT NULL,
  conclusion_descriptiva TEXT NULL,
  observacion TEXT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_nota_transversal),
  UNIQUE KEY uq_nota_transversal_docente (
    id_periodo_academico,
    id_aula,
    id_docente,
    id_area_fuente,
    id_matricula,
    id_competencia_transversal
  ),
  KEY idx_nota_transversal_docente_aula (id_anio_lectivo, id_aula, id_periodo_academico),
  KEY idx_nota_transversal_docente_docente (id_docente, id_anio_lectivo),
  CONSTRAINT fk_ntd_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo (id_anio_lectivo),
  CONSTRAINT fk_ntd_periodo FOREIGN KEY (id_periodo_academico) REFERENCES periodo_academico (id_periodo_academico),
  CONSTRAINT fk_ntd_aula FOREIGN KEY (id_aula) REFERENCES aula (id_aula),
  CONSTRAINT fk_ntd_docente FOREIGN KEY (id_docente) REFERENCES docente (id_docente),
  CONSTRAINT fk_ntd_area_fuente FOREIGN KEY (id_area_fuente) REFERENCES area_curricular (id_area),
  CONSTRAINT fk_ntd_matricula FOREIGN KEY (id_matricula) REFERENCES matricula (id_matricula),
  CONSTRAINT fk_ntd_area_transversal FOREIGN KEY (id_area_transversal) REFERENCES area_curricular (id_area),
  CONSTRAINT fk_ntd_competencia_transversal FOREIGN KEY (id_competencia_transversal) REFERENCES competencia (id_competencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;