USE akrcsist_bd0003_2026;

CREATE TABLE IF NOT EXISTS nota_estudiante (
    id_nota INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    id_matricula INT UNSIGNED NOT NULL,
    id_periodo_academico SMALLINT UNSIGNED NOT NULL,
    id_area INT UNSIGNED NOT NULL,
    id_competencia INT UNSIGNED NULL,
    nota_literal VARCHAR(5) NOT NULL,
    nota_numerica DECIMAL(5,2) NULL,
    observacion VARCHAR(255) NULL,
    fecha_registro TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_nota_estudiante (
        id_matricula,
        id_periodo_academico,
        id_area,
        id_competencia
    ),
    KEY idx_nota_periodo_area (id_periodo_academico, id_area),
    CONSTRAINT fk_nota_matricula
        FOREIGN KEY (id_matricula) REFERENCES matricula (id_matricula),
    CONSTRAINT fk_nota_periodo
        FOREIGN KEY (id_periodo_academico) REFERENCES periodo_academico (id_periodo_academico),
    CONSTRAINT fk_nota_area
        FOREIGN KEY (id_area) REFERENCES area_curricular (id_area),
    CONSTRAINT fk_nota_competencia
        FOREIGN KEY (id_competencia) REFERENCES competencia (id_competencia)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;