-- Select the SAME database that SLPS connects to before importing. Safe to rerun; retains existing projects, requirements, files and reviews.
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;

-- Run after 20261017_way_to_graduate.sql on existing installations.
ALTER TABLE graduation_requirements MODIFY course_id BIGINT UNSIGNED NULL;
ALTER TABLE student_projects MODIFY course_id BIGINT UNSIGNED NULL;
SET @graduation_add_delta = IF((SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='graduation_requirements' AND COLUMN_NAME='requirements_delta')=0,'ALTER TABLE graduation_requirements ADD COLUMN requirements_delta MEDIUMTEXT NULL AFTER requirements','SELECT 1');
PREPARE graduation_editor_upgrade FROM @graduation_add_delta;
EXECUTE graduation_editor_upgrade;
DEALLOCATE PREPARE graduation_editor_upgrade;

CREATE TABLE IF NOT EXISTS graduation_deleted_items (
 entity_type VARCHAR(20) NOT NULL,
 entity_id BIGINT UNSIGNED NOT NULL,
 deleted_by BIGINT UNSIGNED NOT NULL,
 reason VARCHAR(1000) NOT NULL,
 deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(entity_type,entity_id),
 FOREIGN KEY(deleted_by) REFERENCES users(id),
 CHECK(entity_type IN ('requirement','submission'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
