SET NAMES utf8mb4;
SET time_zone = '+01:00';

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NULL,
 legacy_md5 CHAR(32) NULL, first_name VARCHAR(100) NOT NULL, middle_name VARCHAR(100) NULL, last_name VARCHAR(100) NOT NULL,
 phone VARCHAR(30) NULL, role ENUM('applicant','student','staff','super_admin') NOT NULL DEFAULT 'applicant',
 status ENUM('active','suspended','disabled') NOT NULL DEFAULT 'active', must_change_password BOOLEAN NOT NULL DEFAULT FALSE,
 email_verified_at DATETIME NULL, last_login_at DATETIME NULL, legacy_id VARCHAR(100) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(role,status), INDEX(legacy_id)
) ENGINE=InnoDB;

CREATE TABLE campuses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, code VARCHAR(20) NOT NULL UNIQUE, address TEXT NULL, is_active BOOLEAN DEFAULT TRUE);
CREATE TABLE departments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(190) NOT NULL, code VARCHAR(30) NOT NULL UNIQUE, is_active BOOLEAN DEFAULT TRUE);
CREATE TABLE programmes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, department_id BIGINT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, code VARCHAR(30) NOT NULL UNIQUE, duration_years TINYINT UNSIGNED NOT NULL, is_active BOOLEAN DEFAULT TRUE, FOREIGN KEY(department_id) REFERENCES departments(id));
CREATE TABLE academic_sessions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE, starts_on DATE NOT NULL, ends_on DATE NOT NULL, is_active BOOLEAN DEFAULT FALSE);
CREATE TABLE semesters (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, academic_session_id BIGINT UNSIGNED NOT NULL, name ENUM('First','Second','Summer') NOT NULL, starts_on DATE NOT NULL, ends_on DATE NOT NULL, registration_opens_at DATETIME NULL, registration_closes_at DATETIME NULL, is_active BOOLEAN DEFAULT FALSE, UNIQUE(academic_session_id,name), FOREIGN KEY(academic_session_id) REFERENCES academic_sessions(id));

CREATE TABLE applications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, application_no VARCHAR(40) NOT NULL UNIQUE,
 programme_id BIGINT UNSIGNED NOT NULL, campus_id BIGINT UNSIGNED NOT NULL, entry_session_id BIGINT UNSIGNED NOT NULL,
 status ENUM('draft','submitted','in_review','correction_required','admitted','rejected','accepted') NOT NULL DEFAULT 'draft',
 date_of_birth DATE NULL, gender VARCHAR(30) NULL, nationality VARCHAR(100) NULL, state VARCHAR(100) NULL, lga VARCHAR(100) NULL, address TEXT NULL,
 submitted_at DATETIME NULL, decided_at DATETIME NULL, decision_note TEXT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL,
 FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(programme_id) REFERENCES programmes(id), FOREIGN KEY(campus_id) REFERENCES campuses(id), FOREIGN KEY(entry_session_id) REFERENCES academic_sessions(id), INDEX(status,created_at)
);
CREATE TABLE application_qualifications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, application_id BIGINT UNSIGNED NOT NULL, institution VARCHAR(190) NOT NULL, qualification VARCHAR(100) NOT NULL, year_from SMALLINT NOT NULL, year_to SMALLINT NOT NULL, details JSON NULL, FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE);
CREATE TABLE documents (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, application_id BIGINT UNSIGNED NULL, category VARCHAR(50) NOT NULL, original_name VARCHAR(255) NOT NULL, storage_name VARCHAR(255) NOT NULL UNIQUE, mime_type VARCHAR(100) NOT NULL, size_bytes INT UNSIGNED NOT NULL, verified_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(application_id) REFERENCES applications(id) ON DELETE CASCADE);

CREATE TABLE students (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL UNIQUE, application_id BIGINT UNSIGNED NULL UNIQUE, matric_no VARCHAR(50) NOT NULL UNIQUE, programme_id BIGINT UNSIGNED NOT NULL, campus_id BIGINT UNSIGNED NOT NULL, admission_session_id BIGINT UNSIGNED NOT NULL, current_level SMALLINT UNSIGNED NOT NULL DEFAULT 100, category VARCHAR(50) NOT NULL DEFAULT 'regular', status ENUM('active','graduated','withdrawn','deferred','suspended') DEFAULT 'active', legacy_id VARCHAR(100) NULL, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(application_id) REFERENCES applications(id), FOREIGN KEY(programme_id) REFERENCES programmes(id), FOREIGN KEY(campus_id) REFERENCES campuses(id), FOREIGN KEY(admission_session_id) REFERENCES academic_sessions(id));
CREATE TABLE staff_profiles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL UNIQUE, staff_no VARCHAR(50) NOT NULL UNIQUE, department_id BIGINT UNSIGNED NULL, title VARCHAR(80) NULL, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(department_id) REFERENCES departments(id));

CREATE TABLE courses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, department_id BIGINT UNSIGNED NOT NULL, code VARCHAR(30) NOT NULL UNIQUE, title VARCHAR(190) NOT NULL, credit_units TINYINT UNSIGNED NOT NULL, level SMALLINT UNSIGNED NOT NULL, description TEXT NULL, is_active BOOLEAN DEFAULT TRUE, FOREIGN KEY(department_id) REFERENCES departments(id));
CREATE TABLE course_prerequisites (course_id BIGINT UNSIGNED NOT NULL, prerequisite_course_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY(course_id,prerequisite_course_id), FOREIGN KEY(course_id) REFERENCES courses(id), FOREIGN KEY(prerequisite_course_id) REFERENCES courses(id));
CREATE TABLE course_offerings (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, course_id BIGINT UNSIGNED NOT NULL, programme_id BIGINT UNSIGNED NOT NULL, semester_id BIGINT UNSIGNED NOT NULL, lecturer_user_id BIGINT UNSIGNED NULL, status ENUM('compulsory','elective') DEFAULT 'compulsory', max_students INT UNSIGNED NULL, UNIQUE(course_id,programme_id,semester_id), FOREIGN KEY(course_id) REFERENCES courses(id), FOREIGN KEY(programme_id) REFERENCES programmes(id), FOREIGN KEY(semester_id) REFERENCES semesters(id), FOREIGN KEY(lecturer_user_id) REFERENCES users(id));
CREATE TABLE course_registrations (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id BIGINT UNSIGNED NOT NULL, semester_id BIGINT UNSIGNED NOT NULL, status ENUM('draft','registered','approved','cancelled') DEFAULT 'registered', registered_at DATETIME NOT NULL, approved_at DATETIME NULL, UNIQUE(student_id,semester_id), FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(semester_id) REFERENCES semesters(id));
CREATE TABLE course_registration_items (registration_id BIGINT UNSIGNED NOT NULL, course_offering_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY(registration_id,course_offering_id), FOREIGN KEY(registration_id) REFERENCES course_registrations(id) ON DELETE CASCADE, FOREIGN KEY(course_offering_id) REFERENCES course_offerings(id));
CREATE TABLE grade_bands (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, letter_grade VARCHAR(3) NOT NULL, min_score DECIMAL(5,2) NOT NULL, max_score DECIMAL(5,2) NOT NULL, grade_point DECIMAL(3,2) NOT NULL, UNIQUE(letter_grade));
CREATE TABLE results (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, student_id BIGINT UNSIGNED NOT NULL, course_offering_id BIGINT UNSIGNED NOT NULL, continuous_assessment DECIMAL(5,2) NOT NULL DEFAULT 0, exam_score DECIMAL(5,2) NOT NULL DEFAULT 0, total_score DECIMAL(5,2) NOT NULL, letter_grade VARCHAR(3) NOT NULL, grade_point DECIMAL(3,2) NOT NULL, status ENUM('draft','published') DEFAULT 'draft', published_at DATETIME NULL, updated_by BIGINT UNSIGNED NOT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE(student_id,course_offering_id), FOREIGN KEY(student_id) REFERENCES students(id), FOREIGN KEY(course_offering_id) REFERENCES course_offerings(id), FOREIGN KEY(updated_by) REFERENCES users(id));

CREATE TABLE fee_schedules (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, academic_session_id BIGINT UNSIGNED NOT NULL, programme_id BIGINT UNSIGNED NULL, level SMALLINT UNSIGNED NULL, category VARCHAR(50) NULL, fee_type VARCHAR(100) NOT NULL, amount DECIMAL(12,2) NOT NULL, is_active BOOLEAN DEFAULT TRUE, FOREIGN KEY(academic_session_id) REFERENCES academic_sessions(id), FOREIGN KEY(programme_id) REFERENCES programmes(id));
CREATE TABLE invoices (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, reference VARCHAR(50) NOT NULL UNIQUE, academic_session_id BIGINT UNSIGNED NULL, description VARCHAR(190) NOT NULL, total_amount DECIMAL(12,2) NOT NULL, amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0, balance_due DECIMAL(12,2) NOT NULL, status ENUM('unpaid','partial','paid','cancelled') DEFAULT 'unpaid', due_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(academic_session_id) REFERENCES academic_sessions(id), INDEX(user_id,status));
CREATE TABLE invoice_items (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, description VARCHAR(190) NOT NULL, amount DECIMAL(12,2) NOT NULL, FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE);
CREATE TABLE payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, invoice_id BIGINT UNSIGNED NOT NULL, reference VARCHAR(100) NOT NULL UNIQUE, amount DECIMAL(12,2) NOT NULL, method ENUM('paystack','bank_transfer','cash') NOT NULL, status ENUM('pending','successful','failed','reversed') DEFAULT 'pending', gateway_payload JSON NULL, recorded_by BIGINT UNSIGNED NULL, confirmed_by BIGINT UNSIGNED NULL, paid_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(invoice_id) REFERENCES invoices(id), FOREIGN KEY(recorded_by) REFERENCES users(id), FOREIGN KEY(confirmed_by) REFERENCES users(id));

CREATE TABLE announcements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(190) NOT NULL, body TEXT NOT NULL, audience ENUM('all','applicants','students','staff') DEFAULT 'all', author_id BIGINT UNSIGNED NOT NULL, published_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(author_id) REFERENCES users(id));
CREATE TABLE password_reset_tokens (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL UNIQUE, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(100) NULL, entity_id VARCHAR(100) NULL, old_values JSON NULL, new_values JSON NULL, ip_address VARCHAR(45) NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id), INDEX(action,created_at));
CREATE TABLE settings (setting_key VARCHAR(100) PRIMARY KEY, setting_value TEXT NOT NULL, is_public BOOLEAN DEFAULT FALSE, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

INSERT INTO grade_bands(letter_grade,min_score,max_score,grade_point) VALUES ('A',70,100,5.00),('B',60,69.99,4.00),('C',50,59.99,3.00),('D',45,49.99,2.00),('E',40,44.99,1.00),('F',0,39.99,0.00);
INSERT INTO settings(setting_key,setting_value,is_public) VALUES ('institution_name','Unicohstech',TRUE),('institution_full_name','Universal College of Health Science and Technology',TRUE),('currency','NGN',TRUE);