USE akrcsist_bd0003_2026;

CREATE TABLE IF NOT EXISTS eval_diagnostica_resultado (
  id_eval_diagnostica BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_anio_lectivo SMALLINT UNSIGNED NOT NULL,
  id_nivel VARCHAR(3) NOT NULL,
  id_grado TINYINT UNSIGNED NOT NULL,
  id_area INT UNSIGNED NOT NULL,
  id_competencia INT UNSIGNED NOT NULL,
  momento ENUM('DIAGNOSTICO','PROCESO','SALIDA') NOT NULL,
  cantidad_pg SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  cantidad_i SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  cantidad_p SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  cantidad_s SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  observacion VARCHAR(250) NULL,
  id_usuario INT UNSIGNED NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  actualizado_en TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id_eval_diagnostica),
  UNIQUE KEY uq_eval_diag_scope (id_anio_lectivo, id_nivel, id_grado, id_area, id_competencia, momento),
  KEY idx_eval_diag_anio_nivel_grado (id_anio_lectivo, id_nivel, id_grado),
  KEY idx_eval_diag_area_competencia (id_area, id_competencia),
  CONSTRAINT fk_eval_diag_anio FOREIGN KEY (id_anio_lectivo) REFERENCES anio_lectivo(id_anio_lectivo),
  CONSTRAINT fk_eval_diag_nivel FOREIGN KEY (id_nivel) REFERENCES nivel_educativo(id_nivel),
  CONSTRAINT fk_eval_diag_grado FOREIGN KEY (id_grado) REFERENCES grado(id_grado),
  CONSTRAINT fk_eval_diag_area FOREIGN KEY (id_area) REFERENCES area_curricular(id_area),
  CONSTRAINT fk_eval_diag_competencia FOREIGN KEY (id_competencia) REFERENCES competencia(id_competencia),
  CONSTRAINT fk_eval_diag_usuario FOREIGN KEY (id_usuario) REFERENCES usuario_sistema(id_usuario)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;