CREATE TABLE IF NOT EXISTS student_project_grades (
 project_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 score DECIMAL(5,2) NOT NULL,
 feedback TEXT NOT NULL,
 graded_by BIGINT UNSIGNED NOT NULL,
 graded_revision INT UNSIGNED NOT NULL DEFAULT 0,
 version INT UNSIGNED NOT NULL DEFAULT 1,
 graded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(project_id) REFERENCES student_projects(id) ON DELETE CASCADE,
 FOREIGN KEY(graded_by) REFERENCES users(id),
 CONSTRAINT chk_student_project_score CHECK(score>=0 AND score<=100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS student_project_grade_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 project_id BIGINT UNSIGNED NOT NULL,
 score DECIMAL(5,2) NOT NULL,
 feedback TEXT NOT NULL,
 graded_by BIGINT UNSIGNED NOT NULL,
 graded_revision INT UNSIGNED NOT NULL,
 version INT UNSIGNED NOT NULL,
 graded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_student_project_grade_version(project_id,version),
 FOREIGN KEY(project_id) REFERENCES student_projects(id) ON DELETE CASCADE,
 FOREIGN KEY(graded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
