ALTER TABLE student_projects MODIFY course_id BIGINT UNSIGNED NULL;
CREATE TABLE IF NOT EXISTS graduation_requirements (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 stage TINYINT UNSIGNED NOT NULL,
 title VARCHAR(160) NOT NULL,
 academic_period VARCHAR(100) NOT NULL,
 course_id BIGINT UNSIGNED NULL,
 department_id BIGINT UNSIGNED NOT NULL,
 requirements TEXT NOT NULL,
 requirements_delta MEDIUMTEXT NULL,
 deadline DATETIME NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CHECK(stage BETWEEN 1 AND 3),
 FOREIGN KEY(course_id) REFERENCES courses(id), FOREIGN KEY(department_id) REFERENCES departments(id), FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS graduation_requirement_years (
 requirement_id BIGINT UNSIGNED NOT NULL,
 year_of_study TINYINT UNSIGNED NOT NULL,
 PRIMARY KEY(requirement_id,year_of_study),
 FOREIGN KEY(requirement_id) REFERENCES graduation_requirements(id) ON DELETE CASCADE,
 CHECK(year_of_study BETWEEN 1 AND 4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS graduation_submissions (
 project_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 requirement_id BIGINT UNSIGNED NOT NULL,
 student_user_id BIGINT UNSIGNED NOT NULL,
 attempt INT UNSIGNED NOT NULL,
 status VARCHAR(25) NOT NULL DEFAULT 'submitted',
 reviewed_revision INT UNSIGNED NULL,
 version INT UNSIGNED NOT NULL DEFAULT 0,
 UNIQUE KEY uq_graduation_attempt(requirement_id,student_user_id,attempt),
 FOREIGN KEY(project_id) REFERENCES student_projects(id), FOREIGN KEY(requirement_id) REFERENCES graduation_requirements(id), FOREIGN KEY(student_user_id) REFERENCES users(id),
 CHECK(status IN ('submitted','verified','defended_pass','defended_fail','approved','rejected'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS graduation_reviews (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 project_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(30) NOT NULL,
 reason TEXT NOT NULL,
 defence_at DATETIME NULL,
 reviewed_by BIGINT UNSIGNED NOT NULL,
 reviewed_revision INT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(project_id) REFERENCES graduation_submissions(project_id), FOREIGN KEY(reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
