CREATE TABLE IF NOT EXISTS practical_exams (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(160) NOT NULL,
 academic_period VARCHAR(100) NOT NULL,
 course_id BIGINT UNSIGNED NOT NULL,
 department_id BIGINT UNSIGNED NOT NULL,
 campus_id BIGINT UNSIGNED NOT NULL,
 year_of_study TINYINT UNSIGNED NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(course_id) REFERENCES courses(id),
 FOREIGN KEY(department_id) REFERENCES departments(id),
 FOREIGN KEY(campus_id) REFERENCES campuses(id),
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS practical_exam_results (
 exam_id BIGINT UNSIGNED NOT NULL,
 student_user_id BIGINT UNSIGNED NOT NULL,
 student_number VARCHAR(20) NOT NULL,
 level_one DECIMAL(5,2) NULL,
 level_two DECIMAL(5,2) NULL,
 final_exam DECIMAL(5,2) NULL,
 feedback VARCHAR(2000) NULL,
 revision INT UNSIGNED NOT NULL DEFAULT 0,
 published_at DATETIME NULL,
 graded_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(exam_id,student_user_id),
 FOREIGN KEY(exam_id) REFERENCES practical_exams(id) ON DELETE CASCADE,
 FOREIGN KEY(student_user_id) REFERENCES users(id),
 FOREIGN KEY(graded_by) REFERENCES users(id),
 CHECK(level_one IS NULL OR level_one BETWEEN 0 AND 25),
 CHECK(level_two IS NULL OR level_two BETWEEN 0 AND 25),
 CHECK(final_exam IS NULL OR final_exam BETWEEN 0 AND 50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
