-- Student Learning Practical System (SLPS)
-- MySQL 8.0 schema for the requirements in docs/SLPS_PROPOSAL.md.
-- Run against a database named slps with InnoDB and utf8mb4 enabled.
-- Passwords are stored only as password hashes. Never store plain-text passwords.

CREATE TABLE campuses (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    campus_code VARCHAR(20) NOT NULL,
    campus_name VARCHAR(120) NOT NULL,
    location VARCHAR(150) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_campuses_code (campus_code),
    KEY idx_campuses_active_name (is_active, campus_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE departments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_code VARCHAR(30) NULL,
    department_name VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_departments_code (department_code),
    UNIQUE KEY uq_departments_name (department_name),
    KEY idx_departments_active_name (is_active, department_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(150) NOT NULL,
    full_name VARCHAR(150) NULL,
    email VARCHAR(254) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email),
    CONSTRAINT chk_users_role CHECK (role IN ('student','hod','staff','admin'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_profile_pictures (
    user_id BIGINT UNSIGNED NOT NULL,
    image_data LONGBLOB NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_profile_picture_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE students (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    student_number VARCHAR(20) NOT NULL,
    identity_type VARCHAR(10) NOT NULL,
    identity_number VARCHAR(40) NOT NULL,
    department_id BIGINT UNSIGNED NOT NULL,
    campus_id BIGINT UNSIGNED NOT NULL,
    year_of_study TINYINT UNSIGNED NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'inactive',
    enrollment_date DATE NOT NULL,
    email_verified_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_students_user (user_id),
    UNIQUE KEY uq_students_number (student_number),
    UNIQUE KEY uq_students_identity (identity_number),
    KEY idx_students_scope_year (department_id, campus_id, year_of_study),
    CONSTRAINT chk_students_identity_type CHECK (identity_type IN ('nrc','passport')),
    CONSTRAINT chk_students_year CHECK (year_of_study BETWEEN 1 AND 4),
    CONSTRAINT chk_students_status CHECK (status IN ('inactive','active','deferred')),
    CONSTRAINT fk_students_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_students_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_students_campus FOREIGN KEY (campus_id) REFERENCES campuses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE staff (
    user_id BIGINT UNSIGNED NOT NULL,
    staff_number VARCHAR(40) NOT NULL,
    department VARCHAR(150) NULL,
    designation VARCHAR(150) NULL,
    campus_id BIGINT UNSIGNED NULL,
    identity_number VARCHAR(40) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE KEY uq_staff_number (staff_number),
    UNIQUE KEY uq_staff_identity (identity_number),
    CONSTRAINT fk_staff_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_staff_campus FOREIGN KEY (campus_id) REFERENCES campuses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admins (
    user_id BIGINT UNSIGNED NOT NULL,
    campus_id BIGINT UNSIGNED NULL,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_admin_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_admin_campus FOREIGN KEY (campus_id) REFERENCES campuses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE hod_assignments (
    user_id BIGINT UNSIGNED NOT NULL,
    department_id BIGINT UNSIGNED NOT NULL,
    campus_id BIGINT UNSIGNED NOT NULL,
    assigned_by BIGINT UNSIGNED NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE KEY uq_hod_department_campus (department_id, campus_id),
    CONSTRAINT fk_hod_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_hod_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_hod_campus FOREIGN KEY (campus_id) REFERENCES campuses(id),
    CONSTRAINT fk_hod_assigned_by FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE student_email_verifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_email_verification_token (token_hash),
    KEY idx_email_verification_user_expiry (user_id, expires_at),
    CONSTRAINT fk_email_verification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE auth_rate_limits (
    bucket_key VARCHAR(190) NOT NULL,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    window_start DATETIME NOT NULL,
    PRIMARY KEY (bucket_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_login_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    logged_in_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(500) NULL,
    PRIMARY KEY (id),
    KEY idx_login_history_user_time (user_id, logged_in_at),
    CONSTRAINT fk_login_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    actor_id BIGINT UNSIGNED NULL,
    actor_role VARCHAR(20) NOT NULL,
    campus_id BIGINT UNSIGNED NULL,
    entity VARCHAR(80) NOT NULL,
    record_id BIGINT UNSIGNED NULL,
    action VARCHAR(40) NOT NULL,
    summary VARCHAR(500) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_actor_time (actor_id, created_at),
    KEY idx_audit_campus_time (campus_id, created_at),
    CONSTRAINT fk_audit_actor FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_audit_campus FOREIGN KEY (campus_id) REFERENCES campuses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE courses (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_id BIGINT UNSIGNED NOT NULL,
    course_code VARCHAR(40) NOT NULL,
    course_name VARCHAR(180) NOT NULL,
    year_of_study TINYINT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE KEY uq_course_department_code (department_id, course_code),
    CONSTRAINT fk_course_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT chk_course_year CHECK (year_of_study IS NULL OR year_of_study BETWEEN 1 AND 4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE practical_groups (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_name VARCHAR(100) NOT NULL,
    department_id BIGINT UNSIGNED NOT NULL,
    campus_id BIGINT UNSIGNED NOT NULL,
    year_of_study TINYINT UNSIGNED NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_group_name_scope (group_name, department_id, campus_id, year_of_study),
    CONSTRAINT fk_group_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_group_campus FOREIGN KEY (campus_id) REFERENCES campuses(id),
    CONSTRAINT fk_group_creator FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT chk_group_year CHECK (year_of_study BETWEEN 1 AND 4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE group_members (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    left_at DATETIME NULL,
    active_student_id BIGINT UNSIGNED GENERATED ALWAYS AS (IF(left_at IS NULL, student_id, NULL)) STORED,
    PRIMARY KEY (id),
    UNIQUE KEY uq_one_active_group_per_student (active_student_id),
    KEY idx_group_members_group (group_id),
    CONSTRAINT fk_group_member_group FOREIGN KEY (group_id) REFERENCES practical_groups(id) ON DELETE CASCADE,
    CONSTRAINT fk_group_member_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE practical_projects (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    department_id BIGINT UNSIGNED NOT NULL,
    campus_id BIGINT UNSIGNED NOT NULL,
    course_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_projects_scope (department_id, campus_id),
    CONSTRAINT fk_project_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_project_campus FOREIGN KEY (campus_id) REFERENCES campuses(id),
    CONSTRAINT fk_project_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    project_id BIGINT UNSIGNED NOT NULL,
    group_id BIGINT UNSIGNED NULL,
    student_id BIGINT UNSIGNED NULL,
    assigned_by BIGINT UNSIGNED NOT NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    due_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_assignments_due (due_at),
    CONSTRAINT chk_assignment_target CHECK ((group_id IS NOT NULL) <> (student_id IS NOT NULL)),
    CONSTRAINT fk_assignment_project FOREIGN KEY (project_id) REFERENCES practical_projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_assignment_group FOREIGN KEY (group_id) REFERENCES practical_groups(id) ON DELETE CASCADE,
    CONSTRAINT fk_assignment_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_assignment_hod FOREIGN KEY (assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE file_assets (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    file_name VARCHAR(255) NOT NULL,
    storage_key VARCHAR(512) NOT NULL,
    media_type VARCHAR(150) NOT NULL,
    file_size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    file_category VARCHAR(30) NOT NULL DEFAULT 'department_file',
    department_id BIGINT UNSIGNED NULL,
    campus_id BIGINT UNSIGNED NULL,
    uploaded_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_file_storage_key (storage_key),
    KEY idx_files_scope_category (department_id, campus_id, file_category),
    CONSTRAINT chk_file_category CHECK (file_category IN ('submission','past_paper','solution','guide','department_file','profile_picture')),
    CONSTRAINT fk_file_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    CONSTRAINT fk_file_campus FOREIGN KEY (campus_id) REFERENCES campuses(id) ON DELETE SET NULL,
    CONSTRAINT fk_file_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_submissions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    assignment_id BIGINT UNSIGNED NOT NULL,
    submitted_by BIGINT UNSIGNED NOT NULL,
    zip_file_id BIGINT UNSIGNED NOT NULL,
    submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    validation_state VARCHAR(20) NOT NULL DEFAULT 'pending',
    validation_message TEXT NULL,
    PRIMARY KEY (id),
    KEY idx_submissions_assignment_date (assignment_id, submitted_at),
    CONSTRAINT chk_submission_validation CHECK (validation_state IN ('pending','valid','invalid')),
    CONSTRAINT fk_submission_assignment FOREIGN KEY (assignment_id) REFERENCES project_assignments(id) ON DELETE CASCADE,
    CONSTRAINT fk_submission_student FOREIGN KEY (submitted_by) REFERENCES students(id),
    CONSTRAINT fk_submission_zip FOREIGN KEY (zip_file_id) REFERENCES file_assets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE extracted_submission_files (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    submission_id BIGINT UNSIGNED NOT NULL,
    relative_path VARCHAR(1024) NOT NULL,
    media_type VARCHAR(150) NULL,
    storage_key VARCHAR(1024) NULL,
    file_size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    UNIQUE KEY uq_submission_relative_path (submission_id, relative_path(255)),
    CONSTRAINT fk_extracted_submission FOREIGN KEY (submission_id) REFERENCES project_submissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE practical_guides (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_id BIGINT UNSIGNED NOT NULL,
    campus_id BIGINT UNSIGNED NOT NULL,
    guide_type VARCHAR(30) NOT NULL,
    title VARCHAR(200) NOT NULL,
    software_name VARCHAR(150) NULL,
    download_link TEXT NULL,
    installation_steps TEXT NULL,
    installation_check TEXT NULL,
    development_stage VARCHAR(30) NULL,
    explanation TEXT NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_guides_scope (department_id, campus_id, guide_type),
    CONSTRAINT chk_guide_type CHECK (guide_type IN ('software_installation','system_development')),
    CONSTRAINT chk_development_stage CHECK (development_stage IS NULL OR development_stage IN ('requirements','analysis','design','coding','testing','deployment')),
    CONSTRAINT fk_guide_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_guide_campus FOREIGN KEY (campus_id) REFERENCES campuses(id),
    CONSTRAINT fk_guide_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE past_papers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_id BIGINT UNSIGNED NOT NULL,
    course_id BIGINT UNSIGNED NOT NULL,
    course_code VARCHAR(40) NOT NULL,
    exam_year SMALLINT UNSIGNED NOT NULL,
    semester VARCHAR(40) NOT NULL,
    paper_file_id BIGINT UNSIGNED NOT NULL,
    solution_file_id BIGINT UNSIGNED NULL,
    uploaded_by BIGINT UNSIGNED NOT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_past_paper_lookup (department_id, course_code, exam_year, semester),
    CONSTRAINT fk_paper_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_paper_course FOREIGN KEY (course_id) REFERENCES courses(id),
    CONSTRAINT fk_paper_file FOREIGN KEY (paper_file_id) REFERENCES file_assets(id),
    CONSTRAINT fk_paper_solution FOREIGN KEY (solution_file_id) REFERENCES file_assets(id) ON DELETE SET NULL,
    CONSTRAINT fk_paper_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE grades_feedback (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    assignment_id BIGINT UNSIGNED NOT NULL,
    grade DECIMAL(6,2) NOT NULL,
    comments TEXT NULL,
    graded_by BIGINT UNSIGNED NOT NULL,
    graded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_grade_assignment (assignment_id),
    CONSTRAINT chk_grade_nonnegative CHECK (grade >= 0),
    CONSTRAINT fk_grade_assignment FOREIGN KEY (assignment_id) REFERENCES project_assignments(id) ON DELETE CASCADE,
    CONSTRAINT fk_grade_hod FOREIGN KEY (graded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE logbook_entries (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    student_id BIGINT UNSIGNED NOT NULL,
    activity_date DATE NOT NULL,
    practical_activity TEXT NOT NULL,
    verified_by BIGINT UNSIGNED NULL,
    verified_at DATETIME NULL,
    verification_comment TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_logbook_student_date (student_id, activity_date),
    CONSTRAINT fk_logbook_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_logbook_verifier FOREIGN KEY (verified_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE practical_attendance_sessions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    department_id BIGINT UNSIGNED NOT NULL,
    campus_id BIGINT UNSIGNED NOT NULL,
    session_date DATE NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    qr_token VARCHAR(128) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_attendance_qr_token (qr_token),
    KEY idx_attendance_session_scope (department_id, campus_id, session_date),
    CONSTRAINT fk_attendance_session_department FOREIGN KEY (department_id) REFERENCES departments(id),
    CONSTRAINT fk_attendance_session_campus FOREIGN KEY (campus_id) REFERENCES campuses(id),
    CONSTRAINT fk_attendance_session_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE practical_attendance (
    session_id BIGINT UNSIGNED NOT NULL,
    student_id BIGINT UNSIGNED NOT NULL,
    attendance_status VARCHAR(15) NOT NULL,
    marked_by BIGINT UNSIGNED NULL,
    marked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    mark_method VARCHAR(10) NOT NULL,
    PRIMARY KEY (session_id, student_id),
    CONSTRAINT chk_attendance_status CHECK (attendance_status IN ('present','absent','deferred')),
    CONSTRAINT chk_attendance_method CHECK (mark_method IN ('qr','manual')),
    CONSTRAINT fk_attendance_session FOREIGN KEY (session_id) REFERENCES practical_attendance_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_attendance_marker FOREIGN KEY (marked_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    student_id BIGINT UNSIGNED NULL,
    recipient_user_id BIGINT UNSIGNED NULL,
    department_id BIGINT UNSIGNED NULL,
    campus_id BIGINT UNSIGNED NULL,
    kind VARCHAR(40) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    link VARCHAR(1024) NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_at DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_notifications_student_date (student_id, created_at),
    KEY idx_notifications_scope_date (department_id, campus_id, created_at),
    CONSTRAINT fk_notification_student FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_recipient FOREIGN KEY (recipient_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_campus FOREIGN KEY (campus_id) REFERENCES campuses(id) ON DELETE CASCADE,
    CONSTRAINT fk_notification_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE conversations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    conversation_type VARCHAR(15) NOT NULL,
    group_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT chk_conversation_type CHECK (conversation_type IN ('direct','group')),
    CONSTRAINT fk_conversation_group FOREIGN KEY (group_id) REFERENCES practical_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE conversation_members (
    conversation_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (conversation_id, user_id),
    CONSTRAINT fk_conversation_member_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_conversation_member_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    conversation_id BIGINT UNSIGNED NOT NULL,
    sender_user_id BIGINT UNSIGNED NOT NULL,
    message_text TEXT NOT NULL,
    sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_messages_conversation_time (conversation_id, sent_at),
    CONSTRAINT fk_message_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_message_sender FOREIGN KEY (sender_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_requests (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    requested_by BIGINT UNSIGNED NOT NULL,
    department_id BIGINT UNSIGNED NULL,
    campus_id BIGINT UNSIGNED NULL,
    request_type VARCHAR(100) NOT NULL,
    request_details TEXT NOT NULL,
    request_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    review_comment TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_requests_status_date (request_status, created_at),
    CONSTRAINT chk_request_status CHECK (request_status IN ('pending','approved','rejected')),
    CONSTRAINT fk_requester FOREIGN KEY (requested_by) REFERENCES users(id),
    CONSTRAINT fk_request_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    CONSTRAINT fk_request_campus FOREIGN KEY (campus_id) REFERENCES campuses(id) ON DELETE SET NULL,
    CONSTRAINT fk_request_reviewer FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_settings (
    setting_key VARCHAR(120) NOT NULL,
    setting_value JSON NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (setting_key),
    CONSTRAINT fk_setting_updater FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_backups (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    backup_storage_key VARCHAR(512) NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    backup_status VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT chk_backup_status CHECK (backup_status IN ('pending','complete','failed')),
    CONSTRAINT fk_backup_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Practical Guidelines course editor and publication storage.
-- Run once against the existing SLPS database before opening Practical Guidelines.
CREATE TABLE IF NOT EXISTS course_guidelines (
 course_id BIGINT UNSIGNED NOT NULL,
 draft_delta MEDIUMTEXT NOT NULL,
 published_delta MEDIUMTEXT NULL,
 draft_attachment_ids TEXT NOT NULL,
 published_attachment_ids TEXT NULL,
 revision INT UNSIGNED NOT NULL DEFAULT 1,
 published_revision INT UNSIGNED NULL,
 updated_by BIGINT UNSIGNED NOT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 published_at DATETIME NULL,
 PRIMARY KEY(course_id),
 CONSTRAINT fk_cg_course FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE,
 CONSTRAINT fk_cg_editor FOREIGN KEY(updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS guideline_assets (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 course_id BIGINT UNSIGNED NOT NULL,
 file_name VARCHAR(255) NOT NULL,
 storage_key VARCHAR(80) NOT NULL,
 media_type VARCHAR(100) NOT NULL,
 file_size_bytes BIGINT UNSIGNED NOT NULL,
 asset_kind VARCHAR(12) NOT NULL,
 is_published TINYINT(1) NOT NULL DEFAULT 0,
 uploaded_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_ga_storage(storage_key), KEY idx_ga_course(course_id,is_published),
 CONSTRAINT fk_ga_course FOREIGN KEY(course_id) REFERENCES courses(id) ON DELETE CASCADE,
 CONSTRAINT fk_ga_uploader FOREIGN KEY(uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS student_projects (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 student_user_id BIGINT UNSIGNED NOT NULL,
 course_id BIGINT UNSIGNED NULL,
 department_id BIGINT UNSIGNED NOT NULL,
 campus_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(160) NOT NULL,
 description TEXT NOT NULL,
 technologies TEXT NOT NULL,
 archive_name VARCHAR(255) NOT NULL,
 archive_type VARCHAR(3) NOT NULL,
 storage_key VARCHAR(64) NOT NULL UNIQUE,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_sp_student(student_user_id), KEY idx_sp_scope(department_id,campus_id),
 FOREIGN KEY(student_user_id) REFERENCES users(id),
 FOREIGN KEY(course_id) REFERENCES courses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Apply once to the existing SLPS database before opening Project Collaborations.
CREATE TABLE IF NOT EXISTS collaboration_projects (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(160) NOT NULL,
 description TEXT NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 storage_key CHAR(48) NOT NULL UNIQUE,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS collaboration_members (
 project_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 added_by BIGINT UNSIGNED NOT NULL,
 added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (project_id,user_id),
 KEY idx_collaboration_member (user_id,project_id),
 FOREIGN KEY (project_id) REFERENCES collaboration_projects(id) ON DELETE CASCADE,
 FOREIGN KEY (user_id) REFERENCES users(id),
 FOREIGN KEY (added_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS collaboration_admin_departments (
 user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 department_id BIGINT UNSIGNED NOT NULL,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (department_id) REFERENCES departments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS collaboration_deleted_projects (
 project_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 deleted_by BIGINT UNSIGNED NOT NULL,
 deleted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (project_id) REFERENCES collaboration_projects(id) ON DELETE CASCADE,
 FOREIGN KEY (deleted_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Reuse an existing admin department column when the deployed installation has one.
SET @has_admin_department=(SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='admins' AND column_name='department_id');
SET @seed_admin_scope=IF(@has_admin_department>0,'INSERT IGNORE INTO collaboration_admin_departments(user_id,department_id) SELECT a.user_id,a.department_id FROM admins a JOIN departments d ON d.id=a.department_id','SELECT 1');
PREPARE seed_admin_scope FROM @seed_admin_scope;
EXECUTE seed_admin_scope;
DEALLOCATE PREPARE seed_admin_scope;

SET @has_user_department=(SELECT COUNT(*) FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name='users' AND column_name='department_id');
SET @seed_user_scope=IF(@has_user_department>0,'INSERT IGNORE INTO collaboration_admin_departments(user_id,department_id) SELECT u.id,u.department_id FROM users u JOIN departments d ON d.id=u.department_id WHERE u.role="admin"','SELECT 1');
PREPARE seed_user_scope FROM @seed_user_scope;
EXECUTE seed_user_scope;
DEALLOCATE PREPARE seed_user_scope;

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;

CREATE TABLE IF NOT EXISTS notice_publications (
 notification_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 audience VARCHAR(16) NOT NULL,
 priority VARCHAR(12) NOT NULL DEFAULT 'normal',
 software_name VARCHAR(160) NULL,
 software_version VARCHAR(80) NULL,
 FOREIGN KEY(notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
 CONSTRAINT chk_notice_audience CHECK(audience IN ('global','campuses')),
 CONSTRAINT chk_notice_priority CHECK(priority IN ('normal','important'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS notice_campuses (
 notification_id BIGINT UNSIGNED NOT NULL,
 campus_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(notification_id,campus_id),
 KEY idx_notice_campus(campus_id,notification_id),
 FOREIGN KEY(notification_id) REFERENCES notice_publications(notification_id) ON DELETE CASCADE,
 FOREIGN KEY(campus_id) REFERENCES campuses(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS notification_reads (
 notification_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(notification_id,user_id),
 KEY idx_notice_reader(user_id,notification_id),
 FOREIGN KEY(notification_id) REFERENCES notifications(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Preserve known personal reads; never copy a broadcast's shared read flag to all students.
INSERT IGNORE INTO notification_reads(notification_id,user_id,read_at)
 SELECT id,recipient_user_id,read_at FROM notifications WHERE read_at IS NOT NULL AND recipient_user_id IS NOT NULL;
INSERT IGNORE INTO notification_reads(notification_id,user_id,read_at)
 SELECT n.id,s.user_id,n.read_at FROM notifications n JOIN students s ON s.id=n.student_id WHERE n.read_at IS NOT NULL;

CREATE TABLE IF NOT EXISTS suggestions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(160) NOT NULL,
 message TEXT NOT NULL,
 category VARCHAR(24) NOT NULL DEFAULT 'other',
 likes_count INT UNSIGNED NOT NULL DEFAULT 0,
 comments_count INT UNSIGNED NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at DATETIME NULL,
 KEY idx_suggestion_feed(deleted_at,id),
 KEY idx_suggestion_author(user_id,deleted_at,id),
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS suggestion_likes (
 suggestion_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(suggestion_id,user_id),
 FOREIGN KEY(suggestion_id) REFERENCES suggestions(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS suggestion_comments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 suggestion_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 message TEXT NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 deleted_at DATETIME NULL,
 KEY idx_suggestion_comments(suggestion_id,deleted_at,id),
 FOREIGN KEY(suggestion_id) REFERENCES suggestions(id) ON DELETE CASCADE,
 FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_profile_details (
 user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 preferred_name VARCHAR(100) NOT NULL DEFAULT '',
 phone VARCHAR(40) NOT NULL DEFAULT '',
 country VARCHAR(100) NOT NULL DEFAULT '',
 city VARCHAR(100) NOT NULL DEFAULT '',
 bio VARCHAR(1000) NOT NULL DEFAULT '',
 revision INT UNSIGNED NOT NULL DEFAULT 1,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attendance_settings (
 id TINYINT UNSIGNED NOT NULL PRIMARY KEY,
 started_on DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO login_attendance_settings(id,started_on) VALUES(1,DATE(UTC_TIMESTAMP()+INTERVAL 2 HOUR));
CREATE TABLE IF NOT EXISTS student_login_days (
 user_id BIGINT UNSIGNED NOT NULL,
 attendance_date DATE NOT NULL,
 first_seen_at DATETIME NOT NULL,
 PRIMARY KEY(user_id,attendance_date),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS student_login_receipts (
 user_id BIGINT UNSIGNED NOT NULL,
 receipt_month CHAR(7) NOT NULL,
 closed_at DATETIME NOT NULL,
 receipt_json LONGTEXT NOT NULL,
 PRIMARY KEY(user_id,receipt_month),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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;

CREATE TABLE IF NOT EXISTS practical_exam_components (
 exam_id BIGINT UNSIGNED NOT NULL,
 student_user_id BIGINT UNSIGNED NOT NULL,
 component VARCHAR(20) NOT NULL,
 feedback VARCHAR(2000) NULL,
 graded_by BIGINT UNSIGNED NOT NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(exam_id,student_user_id,component),
 FOREIGN KEY(exam_id,student_user_id) REFERENCES practical_exam_results(exam_id,student_user_id) ON DELETE CASCADE,
 FOREIGN KEY(graded_by) REFERENCES users(id),
 CHECK(component IN ('level_one','level_two','final_exam'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS practical_transcripts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 token_hash CHAR(64) NOT NULL UNIQUE,
 student_user_id BIGINT UNSIGNED NOT NULL,
 issued_by BIGINT UNSIGNED NOT NULL,
 issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 snapshot_json LONGTEXT NOT NULL,
 FOREIGN KEY(student_user_id) REFERENCES users(id),
 FOREIGN KEY(issued_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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;

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;
