USE scholarcloud;
CREATE TABLE IF NOT EXISTS curriculum_versions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,programme_id BIGINT UNSIGNED,name VARCHAR(100),academic_session_id BIGINT UNSIGNED,status ENUM('draft','active','archived') DEFAULT 'draft',created_by BIGINT UNSIGNED,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,UNIQUE(programme_id,name));
CREATE TABLE IF NOT EXISTS curriculum_courses(curriculum_id BIGINT UNSIGNED,course_id BIGINT UNSIGNED,level_id BIGINT UNSIGNED,semester ENUM('first','second','summer'),course_type ENUM('core','required','elective') DEFAULT 'core',minimum_grade VARCHAR(3),PRIMARY KEY(curriculum_id,course_id,level_id));
CREATE TABLE IF NOT EXISTS course_prerequisites(course_id BIGINT UNSIGNED,prerequisite_course_id BIGINT UNSIGNED,minimum_grade VARCHAR(3) DEFAULT 'E',PRIMARY KEY(course_id,prerequisite_course_id));
CREATE TABLE IF NOT EXISTS registration_periods(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,semester_id BIGINT UNSIGNED,programme_id BIGINT UNSIGNED NULL,level_id BIGINT UNSIGNED NULL,title VARCHAR(150),opens_at DATETIME,closes_at DATETIME,add_drop_closes_at DATETIME,min_units SMALLINT DEFAULT 0,max_units SMALLINT DEFAULT 24,status ENUM('draft','open','closed','locked') DEFAULT 'draft');
CREATE TABLE IF NOT EXISTS teaching_allocations(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,course_id BIGINT UNSIGNED,semester_id BIGINT UNSIGNED,lecturer_id BIGINT UNSIGNED,role ENUM('lead','co_lecturer','assistant') DEFAULT 'lead',weekly_hours DECIMAL(4,1) DEFAULT 0,status ENUM('active','removed') DEFAULT 'active',UNIQUE(course_id,semester_id,lecturer_id));
CREATE TABLE IF NOT EXISTS venues(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100),campus VARCHAR(100),capacity INT,venue_type ENUM('classroom','laboratory','hall','online') DEFAULT 'classroom',status ENUM('active','inactive') DEFAULT 'active',UNIQUE(name,campus));
CREATE TABLE IF NOT EXISTS timetable_entries(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,semester_id BIGINT UNSIGNED,course_id BIGINT UNSIGNED,lecturer_id BIGINT UNSIGNED NULL,venue_id BIGINT UNSIGNED NULL,entry_type ENUM('lecture','tutorial','practical','examination') DEFAULT 'lecture',day_of_week TINYINT NULL,starts_at TIME NULL,ends_at TIME NULL,specific_date DATE NULL,status ENUM('draft','published','cancelled') DEFAULT 'draft',INDEX(semester_id),INDEX(course_id));
CREATE TABLE IF NOT EXISTS lecture_sessions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,timetable_entry_id BIGINT UNSIGNED NULL,course_id BIGINT UNSIGNED,lecturer_id BIGINT UNSIGNED,topic VARCHAR(180),session_date DATE,starts_at TIME,ends_at TIME,attendance_method ENUM('manual','code','qr') DEFAULT 'manual',attendance_code VARCHAR(20),status ENUM('scheduled','open','closed','cancelled') DEFAULT 'scheduled');
CREATE TABLE IF NOT EXISTS student_attendance(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,lecture_session_id BIGINT UNSIGNED,student_id BIGINT UNSIGNED,status ENUM('present','absent','late','excused') DEFAULT 'present',checked_in_at DATETIME,recorded_by BIGINT UNSIGNED,source ENUM('manual','code','qr') DEFAULT 'manual',UNIQUE(lecture_session_id,student_id));
CREATE TABLE IF NOT EXISTS attendance_corrections(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,attendance_id BIGINT UNSIGNED,requested_by BIGINT UNSIGNED,requested_status ENUM('present','absent','late','excused'),reason TEXT,status ENUM('pending','approved','rejected') DEFAULT 'pending',reviewed_by BIGINT UNSIGNED,reviewed_at DATETIME,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
INSERT IGNORE INTO permissions(name,slug,module,description) VALUES ('Manage curriculum','curriculum.manage','Academics','Manage curriculum versions and prerequisites'),('Manage registration','registration.manage','Academics','Manage course registration periods and approvals'),('Manage teaching allocations','lecturers.manage','Academics','Allocate teaching duties'),('Manage timetables','timetables.manage','Academics','Manage timetable entries and conflicts');
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.slug IN('curriculum.manage','registration.manage','lecturers.manage','timetables.manage') WHERE r.slug='super-admin';
INSERT IGNORE INTO venues(name,campus,capacity,venue_type,status) VALUES('Main Lecture Hall','Main Campus',300,'hall','active'),('Theology Room 1','Main Campus',80,'classroom','active'),('ICT Laboratory','Main Campus',60,'laboratory','active'),('Online Classroom','Virtual Campus',500,'online','active');