-- Import this file into the database selected in cPanel phpMyAdmin. SET NAMES utf8mb4; SET time_zone = '+03:00'; SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; SET FOREIGN_KEY_CHECKS = 0; CREATE TABLE IF NOT EXISTS roles ( id INT AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(60) NOT NULL UNIQUE, permissions TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS departments ( id INT AUTO_INCREMENT PRIMARY KEY, department_code VARCHAR(30) NOT NULL UNIQUE, department_name VARCHAR(120) NOT NULL UNIQUE, hod_name VARCHAR(120) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS academic_years ( id INT AUTO_INCREMENT PRIMARY KEY, academic_year_name VARCHAR(100) NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, status VARCHAR(20) DEFAULT 'Closed', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_academic_year_dates (start_date, end_date), KEY idx_academic_year_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS sessions ( id INT AUTO_INCREMENT PRIMARY KEY, academic_year_id INT NULL, session_name VARCHAR(100) NOT NULL UNIQUE, academic_year_name VARCHAR(100) NULL, session_number TINYINT NULL, start_date DATE NULL, end_date DATE NULL, status VARCHAR(20) DEFAULT 'Open', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_sessions_academic_year_id (academic_year_id), KEY idx_sessions_year_dates (academic_year_name, start_date, end_date), UNIQUE KEY uq_sessions_year_slot (academic_year_name, session_number) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS semesters ( id INT AUTO_INCREMENT PRIMARY KEY, session_id INT NULL, semester_name VARCHAR(100) NOT NULL, session_name VARCHAR(100) NULL, status VARCHAR(20) DEFAULT 'Open', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_semesters_session_id (session_id), UNIQUE KEY uq_semesters_session_name (session_id, semester_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS users ( id INT AUTO_INCREMENT PRIMARY KEY, full_name VARCHAR(150) NOT NULL, username VARCHAR(80) NOT NULL UNIQUE, email VARCHAR(150) NULL, password VARCHAR(255) NOT NULL, role VARCHAR(60) NOT NULL, department VARCHAR(120) NULL, department_id INT NULL, phone VARCHAR(30) NULL, profile_picture VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, must_change_password TINYINT(1) NOT NULL DEFAULT 0, password_changed_at DATETIME NULL, last_password_reset_at DATETIME NULL, last_login_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_users_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS courses ( id INT AUTO_INCREMENT PRIMARY KEY, course_code VARCHAR(40) NULL UNIQUE, course_name VARCHAR(150) NOT NULL, department_id INT NULL, department VARCHAR(100) NULL, duration VARCHAR(50) NULL, duration_years TINYINT UNSIGNED NULL, modules_per_year TINYINT UNSIGNED NOT NULL DEFAULT 3, course_level VARCHAR(50) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS course_modules ( id INT AUTO_INCREMENT PRIMARY KEY, course_id INT NOT NULL, year_no TINYINT UNSIGNED NOT NULL, module_no TINYINT UNSIGNED NOT NULL, module_name VARCHAR(100) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_course_year_module_no (course_id, year_no, module_no), UNIQUE KEY uq_course_year_module_name (course_id, year_no, module_name), KEY idx_course_modules_lookup (course_id, year_no, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS classes ( id INT AUTO_INCREMENT PRIMARY KEY, class_name VARCHAR(100) NOT NULL, class_code VARCHAR(60) NULL, department_id INT NULL, course_id INT NULL, academic_year_id INT NULL, session_id INT NULL, semester_id INT NULL, course_module_id INT NULL, department VARCHAR(100) NULL, course VARCHAR(120) NULL, session_name VARCHAR(120) NULL, semester_name VARCHAR(100) NULL, year_level VARCHAR(20) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_classes_lookup (class_name, session_name, semester_name, year_level), KEY idx_classes_course_module (course_id, course_module_id, year_level) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS students ( id INT AUTO_INCREMENT PRIMARY KEY, admission_no VARCHAR(50) NOT NULL UNIQUE, full_name VARCHAR(150) NOT NULL, id_number VARCHAR(50) NULL, kcpe_no VARCHAR(50) NULL, kcse_no VARCHAR(50) NULL, gender VARCHAR(20) NULL, department VARCHAR(100) NULL, course VARCHAR(120) NULL, class_name VARCHAR(100) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, year_level VARCHAR(20) NULL, phone VARCHAR(30) NULL, email VARCHAR(120) NULL, county VARCHAR(100) NULL, sub_county VARCHAR(100) NULL, village VARCHAR(100) NULL, country VARCHAR(100) NULL, guardian_name VARCHAR(150) NULL, guardian_phone VARCHAR(30) NULL, year_of_birth VARCHAR(10) NULL, fee_total DECIMAL(12,2) DEFAULT 0, fee_balance DECIMAL(12,2) DEFAULT 0, fee_credit DECIMAL(12,2) DEFAULT 0, status VARCHAR(50) DEFAULT 'Admitted', graduation_date DATE NULL, admission_status VARCHAR(40) DEFAULT 'Approved', current_intake VARCHAR(100) NULL, reporting_status VARCHAR(40) DEFAULT 'Not Reported', reporting_deadline DATE NULL, reporting_date DATE NULL, registration_status VARCHAR(40) DEFAULT 'Pending', registration_date DATE NULL, status_reason TEXT NULL, last_status_change_at DATETIME NULL, active_for_academics TINYINT(1) DEFAULT 0, student_photo VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_students_email (email), UNIQUE KEY uq_students_id_number (id_number), KEY idx_students_lookup (class_name, course, semester_name, year_level) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS units ( id INT AUTO_INCREMENT PRIMARY KEY, unit_code VARCHAR(30) NULL UNIQUE, unit_name VARCHAR(150) NOT NULL, department_id INT NULL, department VARCHAR(100) NULL, course VARCHAR(120) NULL, lecturer VARCHAR(120) NULL, semester_name VARCHAR(100) NULL, year_level VARCHAR(20) NULL, unit_type VARCHAR(20) NOT NULL DEFAULT 'Core', is_industrial_attachment TINYINT(1) NOT NULL DEFAULT 0, attachment_duration_weeks SMALLINT UNSIGNED NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_units_lookup (course, semester_name, year_level) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS course_units ( id INT AUTO_INCREMENT PRIMARY KEY, course_id INT NULL, unit_id INT NULL, course_module_id INT NULL, semester_id INT NULL, year_no TINYINT UNSIGNED NULL, course_code VARCHAR(40) NULL, unit_code VARCHAR(30) NULL, year_level VARCHAR(20) NULL, semester_name VARCHAR(100) NULL, unit_type VARCHAR(20) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_course_unit_period (course_code, unit_code, year_level, semester_name), UNIQUE KEY uq_course_module_unit (course_module_id, unit_id), KEY idx_course_units_course_module (course_id, course_module_id, year_no, unit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS class_unit_assignments ( id INT AUTO_INCREMENT PRIMARY KEY, class_id INT NULL, course_unit_id INT NULL, unit_id INT NULL, class_name VARCHAR(100) NOT NULL, unit_code VARCHAR(30) NOT NULL, course VARCHAR(120) NULL, semester_name VARCHAR(100) NULL, year_level VARCHAR(20) NULL, unit_type VARCHAR(20) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_class_unit_course (class_name, course, unit_code, semester_name, year_level), UNIQUE KEY uq_class_course_unit (class_id, course_unit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS lecturer_unit_assignments ( id INT AUTO_INCREMENT PRIMARY KEY, lecturer_user_id INT NULL, class_id INT NULL, course_unit_id INT NULL, lecturer_name VARCHAR(150) NOT NULL, unit_code VARCHAR(30) NOT NULL, course VARCHAR(120) NULL, semester_name VARCHAR(100) NULL, year_level VARCHAR(20) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_lecturer_class_unit (class_id, course_unit_id), KEY idx_lecturer_unit_legacy (lecturer_name, unit_code, semester_name, year_level) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS student_unit_registrations ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NULL, class_id INT NULL, course_id INT NULL, course_module_id INT NULL, course_unit_id INT NULL, unit_id INT NULL, academic_year_id INT NULL, session_id INT NULL, student_admission_no VARCHAR(50) NOT NULL, unit_code VARCHAR(30) NOT NULL, class_name VARCHAR(100) NULL, course VARCHAR(120) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(40) NULL, year_level VARCHAR(20) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_student_unit_period (student_admission_no, unit_code, class_name, session_name, semester_name, academic_year, year_level), KEY idx_unit_registration_lookup (student_admission_no, class_name, session_name, semester_name, academic_year, year_level, unit_code), KEY idx_sur_academic_ids (student_id, class_id, course_module_id, course_unit_id, unit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS semester_enrollments ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NULL, class_id INT NULL, course_id INT NULL, course_module_id INT NULL, academic_year_id INT NULL, session_id INT NULL, student_admission_no VARCHAR(50) NOT NULL, class_name VARCHAR(100) NULL, course VARCHAR(120) NULL, session_name VARCHAR(100) NOT NULL, semester_name VARCHAR(100) NOT NULL, academic_year VARCHAR(40) NOT NULL, year_level VARCHAR(20) NULL, status VARCHAR(30) DEFAULT 'Registered', enrollment_scope VARCHAR(20) DEFAULT 'Student', enrolled_by VARCHAR(100) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_student_semester_period (student_admission_no, session_name, semester_name, academic_year, year_level), KEY idx_semester_enrollments_class (class_name, session_name, semester_name, academic_year, year_level), KEY idx_semester_enrollment_ids (student_id, class_id, course_module_id, session_id, academic_year_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS grading_scales ( id INT AUTO_INCREMENT PRIMARY KEY, grade_name VARCHAR(50) NULL, letter_grade VARCHAR(5) NULL, min_score DECIMAL(5,2) NULL, max_score DECIMAL(5,2) NULL, grade_point DECIMAL(4,2) NULL, remark VARCHAR(80) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS marks ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NULL, class_id INT NULL, course_id INT NULL, course_module_id INT NULL, course_unit_id INT NULL, unit_id INT NULL, academic_year_id INT NULL, session_id INT NULL, student_admission_no VARCHAR(50) NOT NULL, class_name VARCHAR(100) NULL, course VARCHAR(120) NULL, year_level VARCHAR(20) NULL, session_name VARCHAR(100) NULL, unit_code VARCHAR(30) NOT NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(40) NULL, score DECIMAL(5,2) NULL, grade VARCHAR(5) NULL, uploaded_by VARCHAR(80) NULL, cat_score DECIMAL(5,2) NULL, test_score DECIMAL(5,2) NULL, exam_score DECIMAL(5,2) NULL, theory_score DECIMAL(5,2) NULL, practical_score DECIMAL(5,2) NULL, t1_score DECIMAL(5,2) NULL, t2_score DECIMAL(5,2) NULL, t3_score DECIMAL(5,2) NULL, p1_score DECIMAL(5,2) NULL, p2_score DECIMAL(5,2) NULL, p3_score DECIMAL(5,2) NULL, unit_type VARCHAR(20) NULL, theory_weight DECIMAL(5,2) NULL, practical_weight DECIMAL(5,2) NULL, course_level_label VARCHAR(50) NULL, comment VARCHAR(60) NULL, is_locked TINYINT(1) DEFAULT 0, locked_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_marks_student_unit_period (student_admission_no, unit_code, semester_name, session_name, year_level), KEY idx_marks_class_unit (class_name, course, unit_code, semester_name, year_level), KEY idx_marks_academic_ids (student_id, class_id, course_module_id, course_unit_id, unit_id), KEY idx_marks_period_ids (class_id, session_id, academic_year_id, course_unit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS exam_result_batches ( id INT AUTO_INCREMENT PRIMARY KEY, class_id INT NULL, course_id INT NULL, course_module_id INT NULL, course_unit_id INT NULL, unit_id INT NULL, academic_year_id INT NULL, session_id INT NULL, class_name VARCHAR(100) NOT NULL, course VARCHAR(150) NOT NULL, session_name VARCHAR(100) NOT NULL, semester_name VARCHAR(100) NOT NULL, academic_year VARCHAR(40) NOT NULL, year_level VARCHAR(50) NOT NULL, unit_code VARCHAR(30) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'Draft', approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, locked_by VARCHAR(150) NULL, locked_at DATETIME NULL, released_by VARCHAR(150) NULL, released_at DATETIME NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_exam_batch (class_name, course, session_name, semester_name, academic_year, year_level, unit_code), KEY idx_exam_batch_status (status, session_name, semester_name), KEY idx_exam_batch_academic_ids (class_id, session_id, academic_year_id, course_unit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS fee_structures ( id INT AUTO_INCREMENT PRIMARY KEY, course VARCHAR(120) NOT NULL, class_name VARCHAR(100) NULL, billing_scope VARCHAR(30) NOT NULL DEFAULT 'Academic Year', academic_year VARCHAR(40) NULL, semester_name VARCHAR(100) NULL, amount DECIMAL(12,2) DEFAULT 0, notes VARCHAR(255) NULL, updated_by VARCHAR(150) NULL, change_reason VARCHAR(255) NULL, updated_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_fee_structures_scope (course, class_name, billing_scope, academic_year, semester_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_billings ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, charge_name VARCHAR(150) NOT NULL, amount DECIMAL(12,2) DEFAULT 0, notes VARCHAR(255) NULL, billing_type VARCHAR(40) NOT NULL DEFAULT 'Manual', course VARCHAR(120) NULL, class_name VARCHAR(100) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(40) NULL, year_level VARCHAR(20) NULL, source_module VARCHAR(50) NULL, source_ref VARCHAR(120) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_finance_billings_student (student_admission_no), KEY idx_finance_billings_period (student_admission_no, session_name, semester_name, academic_year, year_level) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS fee_payments ( id INT AUTO_INCREMENT PRIMARY KEY, receipt_no VARCHAR(50) NULL UNIQUE, student_admission_no VARCHAR(50) NOT NULL, amount_paid DECIMAL(12,2) DEFAULT 0, payment_method VARCHAR(50) NULL, mpesa_code VARCHAR(80) NULL, payment_date DATE NULL, academic_year VARCHAR(40) NULL, semester_name VARCHAR(100) NULL, session_name VARCHAR(100) NULL, created_by VARCHAR(150) NULL, payer_type VARCHAR(40) NOT NULL DEFAULT 'Student / Parent', payment_reference VARCHAR(150) NULL, receipt_origin VARCHAR(50) NOT NULL DEFAULT 'Direct Student Payment', is_cash_receipt TINYINT(1) NOT NULL DEFAULT 1, payment_status VARCHAR(30) NOT NULL DEFAULT 'Posted', corrected_from_payment_id INT NULL, corrected_to_payment_id INT NULL, correction_reason TEXT NULL, corrected_by VARCHAR(150) NULL, corrected_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_fee_payments_student (student_admission_no), KEY idx_fee_payments_period (student_admission_no, academic_year, semester_name, session_name), KEY idx_fee_payments_reference (payment_reference), KEY idx_fee_payments_status (payment_status, corrected_from_payment_id, corrected_to_payment_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS fee_payment_corrections ( id INT AUTO_INCREMENT PRIMARY KEY, original_payment_id INT NOT NULL, replacement_payment_id INT NOT NULL, original_student_admission_no VARCHAR(50) NOT NULL, replacement_student_admission_no VARCHAR(50) NOT NULL, original_amount DECIMAL(12,2) NOT NULL DEFAULT 0, replacement_amount DECIMAL(12,2) NOT NULL DEFAULT 0, reason TEXT NOT NULL, reversed_journal_id INT NULL, replacement_journal_id INT NULL, corrected_by VARCHAR(150) NULL, corrected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_fee_payment_correction_original (original_payment_id), UNIQUE KEY uq_fee_payment_correction_replacement (replacement_payment_id), KEY idx_fee_payment_correction_student (original_student_admission_no, replacement_student_admission_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_receipt_reference_registry ( id INT AUTO_INCREMENT PRIMARY KEY, reference_hash CHAR(64) NOT NULL, reference_display VARCHAR(190) NOT NULL, transaction_type VARCHAR(50) NOT NULL, source_table VARCHAR(80) NOT NULL, source_id INT NOT NULL, receipt_no VARCHAR(80) NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'Reserved', created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_receipt_reference_hash (reference_hash), KEY idx_finance_receipt_reference_source (source_table, source_id), KEY idx_finance_receipt_reference_status (transaction_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS fee_payment_reconciliations ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, source_academic_year VARCHAR(40) NOT NULL, source_semester_name VARCHAR(100) NULL, target_academic_year VARCHAR(40) NOT NULL, target_semester_name VARCHAR(100) NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, notes VARCHAR(255) NULL, reconciled_by VARCHAR(120) NULL, reconciled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_fee_reconciliation_student (student_admission_no), KEY idx_fee_reconciliation_source (student_admission_no, source_academic_year, source_semester_name), KEY idx_fee_reconciliation_target (student_admission_no, target_academic_year, target_semester_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS attendance ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, unit_code VARCHAR(30) NOT NULL, class_name VARCHAR(100) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, attendance_date DATE NULL, status VARCHAR(20) NULL, lecturer_name VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_attendance_lookup (class_name, unit_code, attendance_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS timetables ( id INT AUTO_INCREMENT PRIMARY KEY, class_id INT NULL, course_unit_id INT NULL, unit_id INT NULL, lecturer_user_id INT NULL, class_name VARCHAR(100) NOT NULL, department VARCHAR(100) NULL, course VARCHAR(120) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, unit_code VARCHAR(30) NULL, day_name VARCHAR(20) NULL, time_slot VARCHAR(50) NULL, venue VARCHAR(100) NULL, lecturer_name VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_timetable_lookup (class_name, semester_name, day_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS semester_reports ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, semester_name VARCHAR(100) NOT NULL, report_date DATE NULL, status VARCHAR(30) DEFAULT 'Reported', notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS notifications ( id INT AUTO_INCREMENT PRIMARY KEY, recipient_type VARCHAR(100) NOT NULL, recipient_reference VARCHAR(150) DEFAULT '', channel_name VARCHAR(50) NOT NULL DEFAULT 'System', message_body TEXT NOT NULL, status_name VARCHAR(50) NOT NULL DEFAULT 'Queued', created_by VARCHAR(150) DEFAULT '', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS communication_batches ( id INT AUTO_INCREMENT PRIMARY KEY, channel_name VARCHAR(30) NOT NULL, target_group VARCHAR(120) NOT NULL, subject_line VARCHAR(190) DEFAULT '', message_body TEXT NOT NULL, recipient_count INT NOT NULL DEFAULT 0, status_name VARCHAR(60) NOT NULL DEFAULT 'Queued', created_by VARCHAR(180) DEFAULT '', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_communication_batches_channel (channel_name), KEY idx_communication_batches_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS student_documents ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, document_type VARCHAR(120) NOT NULL, file_name VARCHAR(255) DEFAULT '', notes TEXT NULL, uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_student_documents_student (student_admission_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS shared_resources ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, category VARCHAR(80) DEFAULT 'General', file_name VARCHAR(255) DEFAULT '', audience VARCHAR(80) DEFAULT 'Students', uploaded_by VARCHAR(150) DEFAULT '', notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS portfolio_setup_state ( setting_key VARCHAR(100) PRIMARY KEY, setting_value VARCHAR(255) NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS portfolio_evidence ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NULL, student_unit_registration_id INT NULL, mark_id INT NULL, student_admission_no VARCHAR(50) NOT NULL, unit_id INT NULL, unit_code VARCHAR(30) NOT NULL, evidence_type VARCHAR(60) NOT NULL, assessment_component VARCHAR(30) NULL, evidence_title VARCHAR(190) NOT NULL, evidence_description TEXT NULL, assessment_date DATE NULL, class_name VARCHAR(100) NULL, course VARCHAR(150) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(40) NULL, year_level VARCHAR(30) NULL, status_name VARCHAR(40) NOT NULL DEFAULT 'Pending Review', submitted_by_user_id INT NULL, submitted_by VARCHAR(150) NULL, submitted_role VARCHAR(60) NULL, reviewed_by_user_id INT NULL, reviewed_by VARCHAR(150) NULL, review_notes TEXT NULL, reviewed_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_poe_student (student_admission_no, academic_year, semester_name), KEY idx_poe_unit (student_unit_registration_id, unit_code), KEY idx_poe_status (status_name, evidence_type), KEY idx_poe_submitter (submitted_by_user_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS portfolio_evidence_files ( id INT AUTO_INCREMENT PRIMARY KEY, evidence_id INT NOT NULL, original_name VARCHAR(255) NOT NULL, stored_name VARCHAR(255) NOT NULL, storage_path VARCHAR(500) NOT NULL, mime_type VARCHAR(120) NOT NULL, file_extension VARCHAR(20) NULL, file_size BIGINT UNSIGNED NOT NULL DEFAULT 0, sha256_checksum CHAR(64) NULL, sort_order INT NOT NULL DEFAULT 0, uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_poe_files_evidence (evidence_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS audit_logs ( id INT AUTO_INCREMENT PRIMARY KEY, user_name VARCHAR(150) NOT NULL, action_name VARCHAR(255) NOT NULL, module_name VARCHAR(120) NOT NULL, ip_address VARCHAR(60) DEFAULT '127.0.0.1', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS backup_logs ( id INT AUTO_INCREMENT PRIMARY KEY, backup_name VARCHAR(200) NOT NULL, backup_type VARCHAR(80) NOT NULL DEFAULT 'Manual', created_by VARCHAR(150) DEFAULT '', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS password_reset_requests ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(100) NULL, phone VARCHAR(30) NULL, email VARCHAR(120) NULL, notes TEXT NULL, status VARCHAR(30) DEFAULT 'Pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS user_login_logs ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, full_name VARCHAR(180) DEFAULT '', login_identifier VARCHAR(180) DEFAULT '', role_name VARCHAR(120) DEFAULT '', status_name VARCHAR(60) DEFAULT 'Success', ip_address VARCHAR(60) DEFAULT '', user_agent VARCHAR(255) DEFAULT '', login_at DATETIME DEFAULT CURRENT_TIMESTAMP, logout_at DATETIME NULL, logout_reason VARCHAR(120) DEFAULT '', KEY idx_user_login_logs_user (user_id), KEY idx_user_login_logs_status (status_name), KEY idx_user_login_logs_login_at (login_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS user_activity_logs ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, full_name VARCHAR(180) DEFAULT '', role_name VARCHAR(120) DEFAULT '', module_name VARCHAR(120) DEFAULT '', action_name VARCHAR(180) DEFAULT '', request_path VARCHAR(255) DEFAULT '', request_method VARCHAR(10) DEFAULT 'GET', ip_address VARCHAR(60) DEFAULT '', details TEXT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_user_activity_logs_user (user_id), KEY idx_user_activity_logs_module (module_name), KEY idx_user_activity_logs_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS applications ( id INT AUTO_INCREMENT PRIMARY KEY, application_no VARCHAR(50) NULL UNIQUE, applicant_name VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, email VARCHAR(120) NULL, department VARCHAR(150) NULL, course_level VARCHAR(50) NULL, course_applied VARCHAR(150) NULL, class_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, intake VARCHAR(80) NULL, gender VARCHAR(20) NULL, dob DATE NULL, national_id VARCHAR(30) NULL, kcse_index VARCHAR(50) NULL, kcse_grade VARCHAR(10) NULL, postal_address TEXT NULL, guardian_name VARCHAR(150) NULL, guardian_phone VARCHAR(30) NULL, status VARCHAR(40) DEFAULT 'Pending', reporting_deadline DATE NULL, decision_date DATE NULL, reporting_notes TEXT NULL, notes TEXT NULL, remarks TEXT NULL, admission_no_created VARCHAR(50) NULL, admission_letter_token CHAR(64) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_applications_status (status), KEY idx_applications_email (email), UNIQUE KEY uq_applications_admission_letter_token (admission_letter_token) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS student_status_history ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NULL, admission_no VARCHAR(50) NOT NULL, old_status VARCHAR(40) NULL, new_status VARCHAR(40) NOT NULL, remarks TEXT NULL, changed_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS exam_routines ( id INT AUTO_INCREMENT PRIMARY KEY, class_id INT NULL, course_id INT NULL, course_module_id INT NULL, course_unit_id INT NULL, unit_id INT NULL, academic_year_id INT NULL, session_id INT NULL, class_name VARCHAR(100) NOT NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, unit_code VARCHAR(30) NOT NULL, exam_date DATE NULL, time_slot VARCHAR(50) NULL, venue VARCHAR(120) NULL, invigilator VARCHAR(150) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Published', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_exam_routines_class (class_name, session_name, semester_name, exam_date), KEY idx_exam_routine_academic_ids (class_id, session_id, course_unit_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS academic_assignments ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, description TEXT NULL, unit_code VARCHAR(30) NOT NULL, class_name VARCHAR(100) NULL, course VARCHAR(120) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(40) NULL, year_level VARCHAR(20) NULL, due_at DATETIME NULL, attachment_path VARCHAR(255) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Published', published_by VARCHAR(150) NULL, published_by_user_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_assignment_student_scope (unit_code, class_name, session_name, semester_name, academic_year, year_level, status), KEY idx_assignment_publisher (published_by_user_id, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS admit_cards ( id INT AUTO_INCREMENT PRIMARY KEY, student_id INT NULL, class_id INT NULL, course_id INT NULL, course_module_id INT NULL, academic_year_id INT NULL, session_id INT NULL, student_admission_no VARCHAR(50) NOT NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(100) NULL, exam_type VARCHAR(80) DEFAULT 'End Session', status VARCHAR(40) DEFAULT 'Eligible', issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_admit_cards_student (student_admission_no, semester_name), KEY idx_admit_cards_period (student_admission_no, session_name, semester_name, exam_type), KEY idx_admit_card_academic_ids (student_id, class_id, course_module_id, session_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS certificates ( id INT AUTO_INCREMENT PRIMARY KEY, certificate_no VARCHAR(50) NULL UNIQUE, student_admission_no VARCHAR(50) NOT NULL, certificate_type VARCHAR(100) NOT NULL, award_title VARCHAR(180) NULL, issue_date DATE NULL, classification VARCHAR(80) NULL, remarks TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS alumni ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, graduation_year VARCHAR(10) NULL, graduation_date DATE NULL, award_title VARCHAR(180) NULL, employer VARCHAR(180) NULL, phone VARCHAR(30) NULL, email VARCHAR(120) NULL, status VARCHAR(40) DEFAULT 'Active Alumni', previous_student_status VARCHAR(40) NULL, previous_registration_status VARCHAR(40) NULL, previous_active_for_academics TINYINT(1) NULL, declaration_notes TEXT NULL, declared_by VARCHAR(150) NULL, declaration_date DATE NULL, is_current TINYINT(1) NOT NULL DEFAULT 1, restored_by VARCHAR(150) NULL, restored_at DATETIME NULL, restoration_reason TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_alumni_student (student_admission_no), KEY idx_alumni_current (is_current, graduation_year) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS library_books ( id INT AUTO_INCREMENT PRIMARY KEY, accession_no VARCHAR(50) NULL UNIQUE, isbn_no VARCHAR(60) NULL, title VARCHAR(180) NOT NULL, author_name VARCHAR(150) NULL, category VARCHAR(100) NULL, quantity INT DEFAULT 1, available_quantity INT DEFAULT 1, shelf_code VARCHAR(50) NULL, status VARCHAR(20) DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS library_book_requests ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, isbn_no VARCHAR(60) NULL, category VARCHAR(100) NULL, author_name VARCHAR(150) NULL, request_by VARCHAR(150) NULL, status VARCHAR(20) DEFAULT 'Pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS library_issues ( id INT AUTO_INCREMENT PRIMARY KEY, accession_no VARCHAR(50) NOT NULL, borrower_type VARCHAR(20) DEFAULT 'Student', borrower_ref VARCHAR(80) NOT NULL, issue_date DATE NULL, due_date DATE NULL, return_date DATE NULL, status VARCHAR(20) DEFAULT 'Issued', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hostel_rooms ( id INT AUTO_INCREMENT PRIMARY KEY, hostel_name VARCHAR(120) NOT NULL, room_no VARCHAR(50) NOT NULL, room_type VARCHAR(50) DEFAULT 'Standard', capacity INT DEFAULT 1, occupied INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_room (hostel_name, room_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hostel_allocations ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, hostel_name VARCHAR(120) NOT NULL, room_no VARCHAR(50) NOT NULL, semester_name VARCHAR(100) NULL, allocation_status VARCHAR(40) DEFAULT 'Allocated', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS transport_routes ( id INT AUTO_INCREMENT PRIMARY KEY, route_name VARCHAR(120) NOT NULL, pickup_point VARCHAR(150) NULL, fare_amount DECIMAL(12,2) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS transport_allocations ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(50) NOT NULL, route_name VARCHAR(120) NOT NULL, pickup_point VARCHAR(150) NULL, status VARCHAR(40) DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS visitor_log ( id INT AUTO_INCREMENT PRIMARY KEY, visitor_name VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, purpose VARCHAR(150) NULL, person_to_see VARCHAR(150) NULL, visit_date DATE NULL, status VARCHAR(40) DEFAULT 'In', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS enquiries ( id INT AUTO_INCREMENT PRIMARY KEY, enquirer_name VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, source_name VARCHAR(80) NULL, subject_line VARCHAR(150) NULL, status VARCHAR(40) DEFAULT 'Open', notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS payroll ( id INT AUTO_INCREMENT PRIMARY KEY, staff_name VARCHAR(150) NULL, staff_no VARCHAR(50) NULL, designation VARCHAR(100) NULL, basic_salary DECIMAL(12,2) DEFAULT 0, allowance DECIMAL(12,2) DEFAULT 0, deduction DECIMAL(12,2) DEFAULT 0, net_salary DECIMAL(12,2) DEFAULT 0, pay_month VARCHAR(50) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_payroll_staff (staff_no, pay_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_employees ( id INT AUTO_INCREMENT PRIMARY KEY, employee_no VARCHAR(40) NOT NULL UNIQUE, payroll_no VARCHAR(40) NULL, full_name VARCHAR(180) NOT NULL, id_number VARCHAR(30) NULL, kra_pin VARCHAR(30) NULL, nssf_no VARCHAR(40) NULL, shif_no VARCHAR(40) NULL, phone VARCHAR(40) NULL, email VARCHAR(120) NULL, gender VARCHAR(20) NULL, date_of_birth DATE NULL, hire_date DATE NULL, employment_type VARCHAR(60) NULL, department VARCHAR(120) NULL, designation VARCHAR(120) NULL, system_role VARCHAR(120) NULL, basic_salary DECIMAL(12,2) DEFAULT 0, house_allowance DECIMAL(12,2) DEFAULT 0, transport_allowance DECIMAL(12,2) DEFAULT 0, other_allowance DECIMAL(12,2) DEFAULT 0, default_nssf DECIMAL(12,2) DEFAULT 0, default_shif DECIMAL(12,2) DEFAULT 0, default_housing_levy DECIMAL(12,2) DEFAULT 0, default_paye DECIMAL(12,2) DEFAULT 0, leave_days_entitlement INT DEFAULT 21, bank_name VARCHAR(120) NULL, bank_account_no VARCHAR(60) NULL, postal_address VARCHAR(255) NULL, staff_photo VARCHAR(255) NULL, emergency_contact VARCHAR(150) NULL, status VARCHAR(30) DEFAULT 'Active', remarks TEXT NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hr_employees_department (department), KEY idx_hr_employees_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_leave_applications ( id INT AUTO_INCREMENT PRIMARY KEY, leave_no VARCHAR(40) NOT NULL UNIQUE, employee_id INT NULL, employee_no VARCHAR(40) NULL, employee_name VARCHAR(180) NOT NULL, department VARCHAR(120) NULL, designation VARCHAR(120) NULL, leave_type VARCHAR(60) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, days_applied INT DEFAULT 0, days_approved INT DEFAULT 0, reason TEXT NULL, reliever_name VARCHAR(180) NULL, status VARCHAR(30) DEFAULT 'Pending', approval_remarks TEXT NULL, applied_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hr_leave_employee (employee_id), KEY idx_hr_leave_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_appraisals ( id INT AUTO_INCREMENT PRIMARY KEY, appraisal_no VARCHAR(40) NOT NULL UNIQUE, employee_id INT NULL, employee_no VARCHAR(40) NULL, employee_name VARCHAR(180) NOT NULL, department VARCHAR(120) NULL, designation VARCHAR(120) NULL, appraisal_period VARCHAR(100) NOT NULL, appraisal_date DATE NOT NULL, score DECIMAL(5,2) DEFAULT 0, rating VARCHAR(60) NULL, achievements TEXT NULL, strengths TEXT NULL, improvement_areas TEXT NULL, supervisor_comments TEXT NULL, action_plan TEXT NULL, status VARCHAR(30) DEFAULT 'Completed', appraised_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hr_appraisals_employee (employee_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_payroll ( id INT AUTO_INCREMENT PRIMARY KEY, payroll_entry_no VARCHAR(40) NOT NULL UNIQUE, employee_id INT NULL, employee_no VARCHAR(40) NULL, employee_name VARCHAR(180) NOT NULL, payroll_no VARCHAR(40) NULL, kra_pin VARCHAR(30) NULL, nssf_no VARCHAR(40) NULL, shif_no VARCHAR(40) NULL, department VARCHAR(120) NULL, designation VARCHAR(120) NULL, pay_month DATE NOT NULL, basic_salary DECIMAL(12,2) DEFAULT 0, house_allowance DECIMAL(12,2) DEFAULT 0, transport_allowance DECIMAL(12,2) DEFAULT 0, other_allowance DECIMAL(12,2) DEFAULT 0, non_cash_benefit DECIMAL(12,2) DEFAULT 0, value_of_quarters DECIMAL(12,2) DEFAULT 0, gross_pay DECIMAL(12,2) DEFAULT 0, nssf_employee DECIMAL(12,2) DEFAULT 0, shif DECIMAL(12,2) DEFAULT 0, housing_levy DECIMAL(12,2) DEFAULT 0, other_deductions DECIMAL(12,2) DEFAULT 0, insurance_deduction DECIMAL(12,2) DEFAULT 0, welfare_deduction DECIMAL(12,2) DEFAULT 0, owner_occupied_interest DECIMAL(12,2) DEFAULT 0, taxable_pay DECIMAL(12,2) DEFAULT 0, tax_charged DECIMAL(12,2) DEFAULT 0, personal_relief DECIMAL(12,2) DEFAULT 0, insurance_relief DECIMAL(12,2) DEFAULT 0, paye DECIMAL(12,2) DEFAULT 0, total_deductions DECIMAL(12,2) DEFAULT 0, net_pay DECIMAL(12,2) DEFAULT 0, status VARCHAR(30) DEFAULT 'Processed', created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hr_payroll_employee_month (employee_id, pay_month), KEY idx_hr_payroll_month (pay_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_deduction_settings ( id INT AUTO_INCREMENT PRIMARY KEY, deduction_code VARCHAR(40) NOT NULL UNIQUE, deduction_name VARCHAR(150) NOT NULL, calculation_mode VARCHAR(20) NOT NULL DEFAULT 'percentage', based_on VARCHAR(20) NOT NULL DEFAULT 'gross', rate_percent DECIMAL(8,2) NOT NULL DEFAULT 0.00, fixed_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, relief_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, is_active TINYINT(1) NOT NULL DEFAULT 1, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_contract_letters ( id INT AUTO_INCREMENT PRIMARY KEY, letter_no VARCHAR(40) NOT NULL UNIQUE, employee_id INT NULL, employee_no VARCHAR(40) NULL, employee_name VARCHAR(180) NOT NULL, department VARCHAR(120) NULL, designation VARCHAR(120) NULL, letter_type VARCHAR(60) NOT NULL DEFAULT 'Appointment Letter', subject VARCHAR(255) NULL, letter_date DATE NOT NULL, reporting_date DATE NULL, effective_date DATE NULL, contract_start_date DATE NULL, contract_end_date DATE NULL, probation_months INT NOT NULL DEFAULT 0, salary DECIMAL(12,2) NOT NULL DEFAULT 0.00, terms TEXT NULL, body_text LONGTEXT NULL, roles_responsibilities LONGTEXT NULL, signatory_name VARCHAR(180) NULL, signatory_title VARCHAR(180) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Active', created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hr_contract_letters_employee (employee_id), KEY idx_hr_contract_letters_type (letter_type), KEY idx_hr_contract_letters_date (letter_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_disciplinary_records ( id INT AUTO_INCREMENT PRIMARY KEY, case_no VARCHAR(40) NOT NULL UNIQUE, employee_id INT NULL, employee_no VARCHAR(40) NULL, employee_name VARCHAR(180) NOT NULL, department VARCHAR(120) NULL, designation VARCHAR(120) NULL, incident_date DATE NOT NULL, hearing_date DATE NULL, case_subject VARCHAR(255) NOT NULL, incident_details LONGTEXT NULL, action_taken LONGTEXT NULL, outcome TEXT NULL, next_review_date DATE NULL, status VARCHAR(30) NOT NULL DEFAULT 'Open', recorded_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hr_disciplinary_employee (employee_id), KEY idx_hr_disciplinary_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_staff_documents ( id INT AUTO_INCREMENT PRIMARY KEY, document_no VARCHAR(40) NOT NULL UNIQUE, employee_id INT NULL, employee_no VARCHAR(40) NULL, employee_name VARCHAR(180) NOT NULL, category VARCHAR(80) NOT NULL DEFAULT 'General', title VARCHAR(180) NOT NULL, issue_date DATE NULL, expiry_date DATE NULL, file_path VARCHAR(255) NULL, remarks TEXT NULL, uploaded_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_hr_staff_documents_employee (employee_id), KEY idx_hr_staff_documents_category (category) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS imprests ( id INT AUTO_INCREMENT PRIMARY KEY, imprest_no VARCHAR(30) NOT NULL UNIQUE, requester_user_id INT NULL, requester_name VARCHAR(150) NOT NULL, requester_role VARCHAR(60) NULL, department VARCHAR(120) NULL, purpose TEXT NOT NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, request_date DATE NOT NULL, expected_clearance_date DATE NULL, status VARCHAR(30) NOT NULL DEFAULT 'Pending', remarks TEXT NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, disbursed_by VARCHAR(150) NULL, disbursed_at DATETIME NULL, rejected_reason TEXT NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_imprests_status (status), KEY idx_imprests_request_date (request_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS imprest_clearances ( id INT AUTO_INCREMENT PRIMARY KEY, clearance_no VARCHAR(30) NOT NULL UNIQUE, imprest_id INT NOT NULL, clearance_date DATE NOT NULL, amount_spent DECIMAL(12,2) NOT NULL DEFAULT 0.00, amount_returned DECIMAL(12,2) NOT NULL DEFAULT 0.00, remarks TEXT NULL, status VARCHAR(30) NOT NULL DEFAULT 'Verified', verified_by VARCHAR(150) NULL, verified_at DATETIME NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_imprest_clearances_imprest (imprest_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS imprest_clearance_items ( id INT AUTO_INCREMENT PRIMARY KEY, clearance_id INT NOT NULL, expense_date DATE NOT NULL, description VARCHAR(255) NOT NULL, receipt_no VARCHAR(100) NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_imprest_clearance_items_clearance (clearance_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS invoices ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_no VARCHAR(30) NOT NULL UNIQUE, student_admission_no VARCHAR(50) NULL, customer_name VARCHAR(200) NULL, customer_phone VARCHAR(50) NULL, customer_email VARCHAR(120) NULL, invoice_date DATE NOT NULL, due_date DATE NULL, description TEXT NULL, subtotal DECIMAL(12,2) NOT NULL DEFAULT 0.00, discount DECIMAL(12,2) NOT NULL DEFAULT 0.00, tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, amount_paid DECIMAL(12,2) NOT NULL DEFAULT 0.00, status VARCHAR(30) NOT NULL DEFAULT 'Unpaid', created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_invoices_status (status), KEY idx_invoices_date (invoice_date), KEY idx_invoices_student (student_admission_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS invoice_items ( id INT AUTO_INCREMENT PRIMARY KEY, invoice_id INT NOT NULL, item_name VARCHAR(255) NOT NULL, qty DECIMAL(10,2) NOT NULL DEFAULT 1.00, unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00, amount DECIMAL(12,2) NOT NULL DEFAULT 0.00, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_invoice_items_invoice (invoice_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS procurement_suppliers ( id INT AUTO_INCREMENT PRIMARY KEY, supplier_name VARCHAR(150) NOT NULL, contact_person VARCHAR(150) NULL, phone VARCHAR(30) NULL, email VARCHAR(150) NULL, address VARCHAR(255) NULL, supplier_code VARCHAR(60) NULL, category VARCHAR(120) NULL, tax_pin VARCHAR(60) NULL, payment_terms VARCHAR(80) NULL, status VARCHAR(40) DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_procurement_suppliers_name (supplier_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS procurement_requisitions ( id INT AUTO_INCREMENT PRIMARY KEY, requisition_no VARCHAR(60) NULL, line_no INT DEFAULT 1, request_date DATE NULL, from_office VARCHAR(150) NULL, department VARCHAR(120) NULL, requested_by VARCHAR(150) NULL, requester_title VARCHAR(120) NULL, requester_user_id INT NULL, to_office VARCHAR(180) NULL, item_name VARCHAR(180) NOT NULL, quantity DECIMAL(12,2) DEFAULT 1, unit_name VARCHAR(60) NULL, unit_cost DECIMAL(12,2) DEFAULT 0, remarks VARCHAR(255) NULL, status VARCHAR(50) DEFAULT 'Pending', supplier_id INT NULL, notes TEXT NULL, passed_by VARCHAR(150) NULL, verified_by VARCHAR(150) NULL, recommended_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, approval_comment VARCHAR(255) NULL, approved_at DATETIME NULL, ordered_at DATETIME NULL, delivered_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_procurement_requisition_no (requisition_no, line_no), KEY idx_procurement_requisitions_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS purchase_orders ( id INT AUTO_INCREMENT PRIMARY KEY, po_number VARCHAR(60) NOT NULL UNIQUE, requisition_id INT NULL, requisition_no VARCHAR(60) NULL, supplier_id INT NULL, order_date DATE NULL, expected_date DATE NULL, amount DECIMAL(12,2) DEFAULT 0, status VARCHAR(50) DEFAULT 'Draft', notes TEXT NULL, prepared_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, invoice_no VARCHAR(80) NULL, delivery_status VARCHAR(50) DEFAULT 'Pending', payment_status VARCHAR(50) DEFAULT 'Unpaid', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_purchase_orders_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS procurement_creditors ( id INT AUTO_INCREMENT PRIMARY KEY, supplier_id INT NULL, invoice_no VARCHAR(80) NOT NULL UNIQUE, invoice_date DATE NULL, due_date DATE NULL, description VARCHAR(255) NOT NULL, amount DECIMAL(12,2) DEFAULT 0, amount_paid DECIMAL(12,2) DEFAULT 0, status VARCHAR(40) DEFAULT 'Open', notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS procurement_creditor_payments ( id INT AUTO_INCREMENT PRIMARY KEY, creditor_id INT NOT NULL, payment_date DATE NULL, amount_paid DECIMAL(12,2) DEFAULT 0, reference_no VARCHAR(80) NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_procurement_creditor_payments_creditor (creditor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS procurement_debtors ( id INT AUTO_INCREMENT PRIMARY KEY, debtor_name VARCHAR(150) NOT NULL, phone VARCHAR(30) NULL, email VARCHAR(150) NULL, invoice_no VARCHAR(80) NOT NULL UNIQUE, invoice_date DATE NULL, due_date DATE NULL, description VARCHAR(255) NOT NULL, amount DECIMAL(12,2) DEFAULT 0, amount_paid DECIMAL(12,2) DEFAULT 0, status VARCHAR(40) DEFAULT 'Open', notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS procurement_debtor_receipts ( id INT AUTO_INCREMENT PRIMARY KEY, debtor_id INT NOT NULL, receipt_date DATE NULL, amount_received DECIMAL(12,2) DEFAULT 0, reference_no VARCHAR(80) NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_procurement_debtor_receipts_debtor (debtor_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS chart_of_accounts ( id INT AUTO_INCREMENT PRIMARY KEY, account_code VARCHAR(30) NOT NULL, account_name VARCHAR(150) NOT NULL, account_type VARCHAR(30) NOT NULL, category VARCHAR(100) NULL, parent_id INT NULL, is_active TINYINT(1) DEFAULT 1, is_system TINYINT(1) DEFAULT 0, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_chart_of_accounts_code (account_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS accounting_periods ( id INT AUTO_INCREMENT PRIMARY KEY, period_name VARCHAR(120) NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, status VARCHAR(20) DEFAULT 'Open', closed_at DATETIME NULL, closed_by VARCHAR(120) NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_accounting_period_dates (start_date, end_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS accounting_journals ( id INT AUTO_INCREMENT PRIMARY KEY, entry_no VARCHAR(60) NOT NULL, entry_date DATE NOT NULL, memo VARCHAR(255) NULL, reference_no VARCHAR(100) NULL, source_module VARCHAR(60) NULL, source_ref VARCHAR(100) NULL, period_id INT NULL, status VARCHAR(20) DEFAULT 'Posted', posted_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_accounting_entry_no (entry_no), KEY idx_accounting_source (source_module, source_ref), KEY idx_accounting_entry_date (entry_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS accounting_journal_lines ( id INT AUTO_INCREMENT PRIMARY KEY, journal_id INT NOT NULL, account_id INT NOT NULL, description VARCHAR(255) NULL, debit DECIMAL(14,2) DEFAULT 0, credit DECIMAL(14,2) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_accounting_journal_lines_journal (journal_id), KEY idx_accounting_journal_lines_account (account_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS accounting_period_closures ( id INT AUTO_INCREMENT PRIMARY KEY, period_id INT NOT NULL, closing_journal_id INT NULL, revenue_total DECIMAL(14,2) DEFAULT 0, expense_total DECIMAL(14,2) DEFAULT 0, net_result DECIMAL(14,2) DEFAULT 0, notes TEXT NULL, closed_at DATETIME NULL, closed_by VARCHAR(120) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_accounting_period_close (period_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS settings ( id INT PRIMARY KEY, college_name VARCHAR(150) NULL, institution_code VARCHAR(50) NULL, address_line VARCHAR(255) NULL, phone VARCHAR(30) NULL, email VARCHAR(120) NULL, website VARCHAR(150) NULL, registrar_name VARCHAR(150) NULL, institution_motto VARCHAR(255) NULL, institution_logo VARCHAR(255) NULL, dashboard_logo VARCHAR(255) NULL, letterhead_right_logo VARCHAR(255) NULL, exam_card_logo VARCHAR(255) NULL, receipt_footer TEXT NULL, statement_footer TEXT NULL, exam_card_footer TEXT NULL, id_card_logo VARCHAR(255) NULL, id_card_college_name VARCHAR(180) NULL, id_card_return_to TEXT NULL, id_card_contacts TEXT NULL, current_session VARCHAR(100) NULL, semester_status VARCHAR(20) NULL, transcript_title VARCHAR(180) NULL, transcript_subtitle TEXT NULL, transcript_logo VARCHAR(255) NULL, transcript_contacts TEXT NULL, transcript_footer TEXT NULL, transcript_signature_label VARCHAR(150) NULL, transcript_signature_name VARCHAR(150) NULL, transcript_signature_title VARCHAR(150) NULL, grading_mode VARCHAR(50) DEFAULT 'Percentage', mpesa_shortcode VARCHAR(30) NULL, level4_theory_weight DECIMAL(5,2) DEFAULT 30, level4_practical_weight DECIMAL(5,2) DEFAULT 70, level5_theory_weight DECIMAL(5,2) DEFAULT 60, level5_practical_weight DECIMAL(5,2) DEFAULT 40, level6_theory_weight DECIMAL(5,2) DEFAULT 50, level6_practical_weight DECIMAL(5,2) DEFAULT 50, theory_exam_count INT DEFAULT 3, practical_exam_count INT DEFAULT 3, id_card_title VARCHAR(150) NULL, id_card_subtitle VARCHAR(150) NULL, id_card_footer TEXT NULL, id_card_signature_label VARCHAR(150) NULL, id_card_header_align VARCHAR(20) DEFAULT 'center', id_card_show_contacts TINYINT(1) DEFAULT 1, id_card_show_status TINYINT(1) DEFAULT 1, id_card_back_title VARCHAR(150) NULL, id_card_return_label VARCHAR(180) NULL, id_card_back_footer TEXT NULL, id_card_show_back TINYINT(1) DEFAULT 1, id_card_show_barcode TINYINT(1) DEFAULT 1, theme_preset_name VARCHAR(80) NULL, theme_primary_color VARCHAR(7) NULL, theme_secondary_color VARCHAR(7) NULL, theme_accent_color VARCHAR(7) NULL, theme_sidebar_bg VARCHAR(7) NULL, theme_dashboard_bg VARCHAR(7) NULL, theme_app_navbar_bg VARCHAR(7) NULL, theme_app_hero_color VARCHAR(7) NULL, theme_app_button_color VARCHAR(7) NULL, theme_app_surface_tint VARCHAR(7) NULL, login_page_image VARCHAR(255) NULL, login_page_logo VARCHAR(255) NULL, login_page_hover_text VARCHAR(255) NULL, login_heading_text VARCHAR(150) NULL, login_subheading_text VARCHAR(255) NULL, login_username_label VARCHAR(80) NULL, login_password_label VARCHAR(80) NULL, login_remember_text VARCHAR(80) NULL, login_forgot_text VARCHAR(120) NULL, login_button_text VARCHAR(80) NULL, login_signup_prompt VARCHAR(160) NULL, login_signup_text VARCHAR(80) NULL, login_support_label VARCHAR(80) NULL, login_footer_text TEXT NULL, college_page_image VARCHAR(255) NULL, college_page_hover_text VARCHAR(255) NULL, landing_theme_primary_color VARCHAR(7) NULL, landing_theme_panel_bg VARCHAR(7) NULL, landing_theme_card_bg VARCHAR(7) NULL, landing_theme_input_bg VARCHAR(7) NULL, landing_theme_button_color VARCHAR(7) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO roles (role_name, permissions) VALUES ('System Admin','all'), ('Admin','dashboard,student_portal,lecturer_portal,admissions_applications,admissions_statistics,students_manage,admissions_reporting,student_profile,student_id_cards,student_id_settings,student_clearance,student_files,alumni,academic_departments,academic_courses,academic_classes,academic_units,academic_course_units,academic_class_units,academic_lecturer_units,academic_registration,academic_timetable,academic_attendance,reports_attendance,learning_resources,exams_marks_entry,exams_routine,exams_admit_cards,exams_results,exams_marksheet,transcripts_center,transcripts_template,transcripts_certificates,finance_fees,finance_sponsors,finance_statements,finance_imprests,finance_imprest_clearance,finance_invoices,finance_balances,finance_reports,finance_chart_accounts,finance_journal,finance_ledger,finance_period_close,finance_mpesa,hr_staff,hr_leave,hr_appraisals,hr_payroll,hr_contracts,hr_disciplinary,hr_documents,hr_settings,library_desk,procurement_requisitions,procurement_orders,procurement_ledgers,communications_bulk,admin_analytics,admin_reports_export,admin_exam_settings,admin_users,admin_institution_settings,admin_grading,admin_backups,admin_logs'), ('Finance','dashboard,finance_fees,finance_sponsors,finance_statements,finance_imprests,finance_imprest_clearance,finance_invoices,finance_balances,finance_reports,finance_chart_accounts,finance_journal,finance_ledger,finance_period_close,finance_mpesa,procurement_requisitions,procurement_orders,procurement_ledgers,communications_bulk'), ('Lecturer','dashboard,lecturer_portal,academic_units,academic_timetable,academic_attendance,reports_attendance,learning_resources,exams_marks_entry,exams_results,exams_marksheet'), ('Admissions','dashboard,admissions_applications,admissions_statistics,students_manage,admissions_reporting,student_profile,student_id_cards,student_id_settings,student_clearance,student_files,alumni,academic_departments,academic_courses,academic_classes,academic_units,academic_registration,communications_bulk,transcripts_center,transcripts_template,transcripts_certificates'), ('Student','student_portal,finance_statements,student_files,transcripts_center'), ('Librarian','dashboard,library_desk'), ('Procurement Officer','dashboard,procurement_requisitions,procurement_orders,procurement_ledgers,finance_journal,finance_imprests,finance_imprest_clearance,communications_bulk'), ('Human Resource','dashboard,hr_staff,hr_leave,hr_appraisals,hr_payroll,hr_contracts,hr_disciplinary,hr_documents,hr_settings,communications_bulk'), ('Examination Officer','dashboard,exams_marks_entry,exams_routine,exams_admit_cards,exams_results,exams_marksheet,transcripts_center,transcripts_template,transcripts_certificates,reports_attendance,admin_exam_settings,communications_bulk'); INSERT INTO departments (department_code, department_name, hod_name) VALUES ('ICT','ICT','Mr. Kibet'), ('BUS','Business','Ms. Achieng'), ('ADM','Administration','Mrs. Jepchirchir'), ('ACC','Accounts','Mr. Kiprono'), ('EXM','Examinations','Mr. Mutai'), ('PRC','Procurement','Ms. Chepngeno'); INSERT INTO academic_years (academic_year_name, start_date, end_date, status) VALUES ('2025/2026','2025-09-01','2026-08-31','Open'), ('2026/2027','2026-09-01','2027-08-31','Closed'); INSERT INTO sessions (session_name, academic_year_name, session_number, start_date, end_date, status) VALUES ('2025/2026 - Session 1','2025/2026',1,'2025-09-01','2025-12-31','Closed'), ('2025/2026 - Session 2','2025/2026',2,'2026-01-01','2026-04-30','Closed'), ('2025/2026 - Session 3','2025/2026',3,'2026-05-01','2026-08-31','Open'), ('2026/2027 - Session 1','2026/2027',1,'2026-09-01','2026-12-31','Closed'); INSERT INTO semesters (semester_name, session_name, status) VALUES ('Module 1','2025/2026 - Session 3','Open'), ('Module 2','2025/2026 - Session 3','Closed'), ('Module 3','2025/2026 - Session 3','Closed'), ('Module 4','2025/2026 - Session 3','Closed'), ('Module 5','2025/2026 - Session 3','Closed'), ('Module 6','2026/2027 - Session 1','Closed'), ('Module 7','2026/2027 - Session 1','Closed'); -- No default login accounts are installed. Run: php bin/create_admin.php INSERT INTO courses (course_code, course_name, department, duration, course_level) VALUES ('DICT','Diploma in ICT','ICT','3 Years','Level 6 / Diploma'), ('DBUS','Diploma in Business Management','Business','2 Years','Level 6 / Diploma'); INSERT INTO classes (class_name, department, course, session_name, semester_name, year_level) VALUES ('ICT Y1', 'ICT', 'Diploma in ICT', '2025/2026 - Session 3', 'Module 1', 'Year 1'), ('BUS Y1', 'Business', 'Diploma in Business Management', '2025/2026 - Session 3', 'Module 1', 'Year 1'); INSERT INTO students (admission_no, full_name, id_number, gender, department, course, class_name, session_name, semester_name, year_level, phone, email, county, sub_county, village, country, guardian_name, guardian_phone, year_of_birth, fee_total, fee_balance, status, admission_status, reporting_status, registration_status, active_for_academics) VALUES ('ADM001', 'Jane Chebet', '34567890', 'Female', 'ICT', 'Diploma in ICT', 'ICT Y1', '2025/2026 - Session 3', 'Module 1', 'Year 1', '0712345678', 'jane@example.com', 'Uasin Gishu', 'Turbo', 'Kamagut', 'Kenya', 'Peter Chebet', '0700111222', '2004', 45000, 15000, 'Active', 'Approved', 'Reported', 'Registered', 1), ('ADM002', 'Mark Kiptoo', '29876543', 'Male', 'Business', 'Diploma in Business Management', 'BUS Y1', '2025/2026 - Session 3', 'Module 1', 'Year 1', '0799999999', 'mark@example.com', 'Nandi', 'Aldai', 'Kabiyet', 'Kenya', 'Janet Kiptoo', '0700222333', '2003', 30000, 5000, 'Deferred', 'Approved', 'Reported', 'Registered', 1), ('ADM003', 'Mercy Jelimo', '31234567', 'Female', 'ICT', 'Diploma in ICT', 'ICT Y1', '2025/2026 - Session 3', 'Module 1', 'Year 1', '0709990000', 'mercy@example.com', 'Elgeyo Marakwet', 'Keiyo North', 'Tambach', 'Kenya', 'Samuel Jelimo', '0700333444', '2005', 45000, 45000, 'Active', 'Approved', 'Not Reported', 'Pending', 0); INSERT INTO units (unit_code, unit_name, department, course, lecturer, semester_name, year_level) VALUES ('ICT101', 'Computer Applications', 'ICT', 'Diploma in ICT', 'Lecturer Demo', 'Module 1', 'Year 1'), ('ICT102', 'Programming Fundamentals', 'ICT', 'Diploma in ICT', 'Lecturer Demo', 'Module 1', 'Year 1'), ('BUS101', 'Principles of Business', 'Business', 'Diploma in Business Management', 'Ms. Achieng', 'Module 1', 'Year 1'); INSERT INTO course_units (course_code, unit_code, year_level, semester_name) VALUES ('DICT','ICT101','Year 1','Module 1'), ('DICT','ICT102','Year 1','Module 1'), ('DBUS','BUS101','Year 1','Module 1'); INSERT INTO class_unit_assignments (class_name, unit_code, course, semester_name, year_level) VALUES ('ICT Y1','ICT101','Diploma in ICT','Module 1','Year 1'), ('ICT Y1','ICT102','Diploma in ICT','Module 1','Year 1'), ('BUS Y1','BUS101','Diploma in Business Management','Module 1','Year 1'); INSERT INTO lecturer_unit_assignments (lecturer_name, unit_code, course, semester_name, year_level) VALUES ('Lecturer Demo','ICT101','Diploma in ICT','Module 1','Year 1'), ('Lecturer Demo','ICT102','Diploma in ICT','Module 1','Year 1'); INSERT INTO student_unit_registrations (student_admission_no, unit_code, class_name, course, session_name, semester_name, academic_year, year_level) VALUES ('ADM001', 'ICT101', 'ICT Y1', 'Diploma in ICT', '2025/2026 - Session 3', 'Module 1', '2025/2026', 'Year 1'), ('ADM001', 'ICT102', 'ICT Y1', 'Diploma in ICT', '2025/2026 - Session 3', 'Module 1', '2025/2026', 'Year 1'), ('ADM002', 'BUS101', 'BUS Y1', 'Diploma in Business Management', '2025/2026 - Session 3', 'Module 1', '2025/2026', 'Year 1'); INSERT INTO semester_enrollments (student_admission_no, class_name, course, session_name, semester_name, academic_year, year_level, status, enrollment_scope, enrolled_by) VALUES ('ADM001','ICT Y1','Diploma in ICT','2025/2026 - Session 3','Module 1','2025/2026','Year 1','Registered','Student','System Administrator'), ('ADM002','BUS Y1','Diploma in Business Management','2025/2026 - Session 3','Module 1','2025/2026','Year 1','Registered','Student','System Administrator'); INSERT INTO grading_scales (grade_name, letter_grade, min_score, max_score, grade_point, remark) VALUES ('Excellent','A',70,100,4.0,'Pass'), ('Very Good','B',60,69.99,3.0,'Pass'), ('Good','C',50,59.99,2.0,'Pass'), ('Average','D',40,49.99,1.0,'Pass'), ('Fail','E',0,39.99,0.0,'Fail'); INSERT INTO marks (student_admission_no, class_name, course, year_level, session_name, unit_code, semester_name, academic_year, score, grade, uploaded_by, cat_score, test_score, exam_score, theory_score, practical_score, theory_weight, practical_weight, course_level_label, comment, is_locked) VALUES ('ADM001', 'ICT Y1', 'Diploma in ICT', 'Year 1', '2025/2026 - Session 3', 'ICT101', 'Module 1', '2025/2026', 78, 'A', 'admin', 20, 18, 40, 68, 82, 50, 50, 'Level 6 / Diploma', 'Mastery', 1), ('ADM001', 'ICT Y1', 'Diploma in ICT', 'Year 1', '2025/2026 - Session 3', 'ICT102', 'Module 1', '2025/2026', 66, 'B', 'admin', 15, 16, 35, 62, 70, 50, 50, 'Level 6 / Diploma', 'Proficient', 1), ('ADM002', 'BUS Y1', 'Diploma in Business Management', 'Year 1', '2025/2026 - Session 3', 'BUS101', 'Module 1', '2025/2026', 61, 'B', 'admin', 14, 15, 32, 61, 60, 50, 50, 'Level 6 / Diploma', 'Proficient', 1); INSERT INTO fee_structures (course, class_name, billing_scope, academic_year, semester_name, amount, notes) VALUES ('Diploma in ICT','ICT Y1','Academic Year','2025/2026','',135000,'Academic year fee'), ('Diploma in ICT','ICT Y1','Module','2025/2026','Module 1',45000,'Optional per module fee'), ('Diploma in Business Management','BUS Y1','Academic Year','2025/2026','',90000,'Academic year fee'); INSERT INTO fee_payments (receipt_no, student_admission_no, amount_paid, payment_method, mpesa_code, payment_date, academic_year, semester_name, session_name) VALUES ('RCPT1001', 'ADM001', 30000, 'M-Pesa', 'QWE123XYZ', CURDATE(), '2025/2026', 'Module 1', '2025/2026 - Session 3'), ('RCPT1002', 'ADM002', 25000, 'Cash', '', CURDATE(), '2025/2026', 'Module 1', '2025/2026 - Session 3'); INSERT INTO attendance (student_admission_no, unit_code, class_name, session_name, semester_name, attendance_date, status, lecturer_name) VALUES ('ADM001','ICT101','ICT Y1','2025/2026 - Session 3','Module 1',CURDATE(),'Present','Lecturer Demo'), ('ADM003','ICT101','ICT Y1','2025/2026 - Session 3','Module 1',CURDATE(),'Absent','Lecturer Demo'); INSERT INTO timetables (class_name, department, course, session_name, semester_name, unit_code, day_name, time_slot, venue, lecturer_name) VALUES ('ICT Y1','ICT','Diploma in ICT','2025/2026 - Session 3','Module 1','ICT101','Monday','8:00 AM - 10:00 AM','Lab 1','Lecturer Demo'), ('ICT Y1','ICT','Diploma in ICT','2025/2026 - Session 3','Module 1','ICT102','Wednesday','10:00 AM - 12:00 PM','Lab 2','Lecturer Demo'), ('BUS Y1','Business','Diploma in Business Management','2025/2026 - Session 3','Module 1','BUS101','Tuesday','9:00 AM - 11:00 AM','Room B1','Ms. Achieng'); INSERT INTO semester_reports (student_admission_no, semester_name, report_date, status, notes) VALUES ('ADM001','Module 1',CURDATE(),'Reported','Reported and cleared for classes'); INSERT INTO notifications (recipient_type, recipient_reference, channel_name, message_body, status_name, created_by) VALUES ('Student', 'ADM001', 'SMS', 'Dear student, your fee balance is pending. Kindly clear before exams.', 'Queued', 'System Administrator'); INSERT INTO student_documents (student_admission_no, document_type, file_name, notes) VALUES ('ADM001', 'KCSE Certificate', 'uploads/student_docs/adm001_kcse.pdf', 'Verified during admission'); INSERT INTO shared_resources (title, category, file_name, audience, uploaded_by, notes) VALUES ('Session Registration Guide','Guide','uploads/resources/registration_guide.pdf','Students','System Administrator','How to register units online'); INSERT INTO audit_logs (user_name, action_name, module_name, ip_address) VALUES ('System Administrator', 'Created transcript settings', 'Settings', '127.0.0.1'), ('System Administrator', 'Posted fee payment', 'Finance', '127.0.0.1'), ('System Administrator', 'Entered CAT marks', 'Academics', '127.0.0.1'); INSERT INTO backup_logs (backup_name, backup_type, created_by) VALUES ('college_erp_schema_backup.sql', 'Manual', 'System Administrator'); INSERT INTO applications (application_no, applicant_name, phone, email, department, course_applied, intake, gender, national_id, kcse_index, kcse_grade, guardian_name, guardian_phone, status, notes) VALUES ('APP2026001','Jane Applicant','0712345678','jane.applicant@example.com','ICT','Diploma in ICT','May 2026','Female','41234567','12345678901','B-','Peter Applicant','0700111222','Pending','Web application sample'); INSERT INTO exam_routines (class_name, session_name, semester_name, unit_code, exam_date, time_slot, venue, invigilator) VALUES ('ICT Y1','2025/2026 - Session 3','Module 1','ICT101',CURDATE(),'9:00 AM - 11:00 AM','Hall A','Mr. Rotich'); INSERT INTO admit_cards (student_admission_no, semester_name, exam_type, status) VALUES ('ADM001','Module 1','End Module','Eligible'); INSERT INTO certificates (certificate_no, student_admission_no, certificate_type, award_title, issue_date, classification, remarks) VALUES ('CERT/2026/0001','ADM001','Academic Certificate','Diploma in ICT',CURDATE(),'Credit','Sample certificate record'); INSERT INTO alumni (student_admission_no, graduation_year, award_title, employer, phone, email, status) VALUES ('ADM001','2026','Diploma in ICT','ABC Technologies','0712345678','jane@example.com','Active Alumni'); INSERT INTO library_books (accession_no, title, author_name, category, quantity, available_quantity, shelf_code) VALUES ('BK001','Introduction to Database Systems','C. J. Date','ICT',5,5,'SHELF-A1'); INSERT INTO hostel_rooms (hostel_name, room_no, room_type, capacity, occupied) VALUES ('North Hostel','A1','Standard',4,0); INSERT INTO transport_routes (route_name, pickup_point, fare_amount) VALUES ('Town Campus Route','Town Stage',500.00); INSERT INTO visitor_log (visitor_name, phone, purpose, person_to_see, visit_date, status) VALUES ('John Visitor','0715000000','Enquiry','Admissions Officer',CURDATE(),'In'); INSERT INTO enquiries (enquirer_name, phone, source_name, subject_line, status, notes) VALUES ('Mary Caller','0716000000','Phone','Course availability','Open','Interested in May intake'); INSERT INTO payroll (staff_name, staff_no, designation, basic_salary, allowance, deduction, net_salary, pay_month) VALUES ('John Korir', 'EMP001', 'Lecturer', 45000, 5000, 2500, 47500, 'March 2026'); INSERT INTO hr_employees (employee_no, payroll_no, full_name, id_number, kra_pin, nssf_no, shif_no, phone, email, gender, hire_date, employment_type, department, designation, basic_salary, house_allowance, transport_allowance, other_allowance, default_nssf, default_shif, default_housing_levy, default_paye, leave_days_entitlement, bank_name, bank_account_no, postal_address, emergency_contact, status, created_by) VALUES ('EMP001','PR001','John Korir','22334455','A012345678Z','NSSF001','SHIF001','0717000000','john.korir@example.com','Male','2024-01-15','Permanent','ICT','Lecturer',45000,5000,3000,2000,1080,1200,675,4200,21,'Equity Bank','1234567890','P.O. Box 123 Eldoret','Jane Korir - 0700123456','Active','System Administrator'); INSERT INTO hr_leave_applications (leave_no, employee_id, employee_no, employee_name, department, designation, leave_type, start_date, end_date, days_applied, days_approved, reason, reliever_name, status, applied_by) VALUES ('LEV/2026/0001',1,'EMP001','John Korir','ICT','Lecturer','Annual Leave','2026-04-01','2026-04-10',8,8,'Annual rest leave','Lecturer Demo','Approved','HR Officer'); INSERT INTO hr_appraisals (appraisal_no, employee_id, employee_no, employee_name, department, designation, appraisal_period, appraisal_date, score, rating, achievements, strengths, improvement_areas, supervisor_comments, action_plan, status, appraised_by) VALUES ('APR/2026/0001',1,'EMP001','John Korir','ICT','Lecturer','2025 Annual Review','2026-01-20',82,'Excellent','Improved pass rate','Reliable and punctual','More research output','Strong performance','Support postgraduate training','Completed','HR Officer'); INSERT INTO hr_payroll (payroll_entry_no, employee_id, employee_no, employee_name, payroll_no, kra_pin, nssf_no, shif_no, department, designation, pay_month, basic_salary, house_allowance, transport_allowance, other_allowance, gross_pay, nssf_employee, shif, housing_levy, other_deductions, insurance_deduction, welfare_deduction, taxable_pay, tax_charged, personal_relief, insurance_relief, paye, total_deductions, net_pay, status, created_by) VALUES ('PAY/2026/03/0001',1,'EMP001','John Korir','PR001','A012345678Z','NSSF001','SHIF001','ICT','Lecturer','2026-03-01',45000,5000,3000,2000,55000,1080,1512.50,825,500,0,0,53920,10959.35,2400,0,8559.35,12476.85,42523.15,'Processed','HR Officer'); INSERT INTO hr_deduction_settings (deduction_code, deduction_name, calculation_mode, based_on, rate_percent, fixed_amount, relief_amount, is_active, notes) VALUES ('SHIF','SHIF Deduction','percentage','gross',2.75,0,0,1,'Calculated from gross pay.'), ('HOUSING','Housing Levy Deduction','percentage','gross',1.50,0,0,1,'Calculated from gross pay.'), ('PAYE','PAYE / Personal Relief','fixed','taxable',0,0,2400,1,'Personal relief used in PAYE computation.'), ('OTHER','Other Deductions Default','fixed','manual',0,0,0,1,'Default value loaded into payroll form.'), ('INSURANCE','Insurance Deduction','fixed','manual',0,0,0,1,'Extra insurance deduction loaded into payroll form.'), ('WELFARE','Welfare Deduction','fixed','manual',0,0,0,1,'Extra welfare deduction loaded into payroll form.'); INSERT INTO hr_contract_letters (letter_no, employee_id, employee_no, employee_name, department, designation, letter_type, subject, letter_date, reporting_date, effective_date, contract_start_date, contract_end_date, probation_months, salary, terms, body_text, roles_responsibilities, signatory_name, signatory_title, status, created_by) VALUES ('LTR/2026/0001',1,'EMP001','John Korir','ICT','Lecturer','Appointment Letter','APPOINTMENT TO THE ROLE OF LECTURER','2026-01-10','2026-01-15','2026-01-15','2026-01-15','2028-01-14',6,55000,'Standard institution terms apply.','We are pleased to appoint you to the role of Lecturer in the ICT Department.','1. Prepare and deliver competency-based training in assigned units.\n2. Assess learners and maintain accurate assessment records.\n3. Participate in quality assurance and departmental activities.','Principal Name','Principal','Active','HR Officer'); INSERT INTO hr_disciplinary_records (case_no, employee_id, employee_no, employee_name, department, designation, incident_date, hearing_date, case_subject, incident_details, action_taken, outcome, next_review_date, status, recorded_by) VALUES ('DISC/2026/0001',1,'EMP001','John Korir','ICT','Lecturer','2026-02-10','2026-02-15','Late Submission of Marks','Marks were submitted after deadline.','Written warning issued.','Resolved after counselling.','2026-05-15','Closed','HR Officer'); INSERT INTO hr_staff_documents (document_no, employee_id, employee_no, employee_name, category, title, issue_date, expiry_date, file_path, remarks, uploaded_by) VALUES ('DOC/2026/0001',1,'EMP001','John Korir','Contract','Signed Contract','2026-01-15','2028-01-14','uploads/hr/contract_emp001.pdf','Initial contract copy','HR Officer'); INSERT INTO imprests (imprest_no, requester_user_id, requester_name, requester_role, department, purpose, amount, request_date, expected_clearance_date, status, remarks, approved_by, approved_at, disbursed_by, disbursed_at, created_by) VALUES ('IMP/2026/0001',3,'Finance Officer','Finance','Accounts','Office stationery purchase',15000,'2026-03-01','2026-03-07','Disbursed','Urgent procurement','System Administrator',NOW(),'Finance Officer',NOW(),'Finance Officer'); INSERT INTO imprest_clearances (clearance_no, imprest_id, clearance_date, amount_spent, amount_returned, remarks, status, verified_by, verified_at, created_by) VALUES ('CLR/2026/0001',1,'2026-03-08',14000,1000,'Stationery receipts attached','Verified','Finance Officer',NOW(),'Finance Officer'); INSERT INTO imprest_clearance_items (clearance_id, expense_date, description, receipt_no, amount) VALUES (1,'2026-03-05','Printer paper','R001',5000), (1,'2026-03-05','Pens and files','R002',9000); INSERT INTO invoices (invoice_no, student_admission_no, customer_name, customer_phone, customer_email, invoice_date, due_date, description, subtotal, discount, tax_amount, total_amount, amount_paid, status, created_by) VALUES ('INV/2026/0001','ADM001','Jane Chebet','0712345678','jane@example.com','2026-03-10','2026-03-20','ICT Practical Fee',5000,0,0,5000,3000,'Part Paid','Finance Officer'); INSERT INTO invoice_items (invoice_id, item_name, qty, unit_price, amount) VALUES (1,'ICT Practical Fee',1,5000,5000); INSERT INTO procurement_suppliers (supplier_name, contact_person, phone, email, address) VALUES ('Stationers Kenya','Alice','0718000000','suppliers@example.com','Eldoret'); INSERT INTO procurement_requisitions (requisition_no, line_no, request_date, from_office, department, requested_by, requester_title, to_office, item_name, quantity, unit_name, unit_cost, remarks, status, supplier_id, notes, passed_by, verified_by, recommended_by, approved_by) VALUES ('REQ/2026/0001',1,'2026-03-01','HOD ICT','ICT','John Korir','User Staff','HEAD OF SUPPLIES CHAIN MANAGEMENT','Printer Paper',10,'reams',500,'Urgent lab need','Approved',1,'For computer lab','HOD ICT','','',''); INSERT INTO purchase_orders (po_number, requisition_id, supplier_id, order_date, expected_date, amount, status, notes) VALUES ('PO/2026/0001',1,1,'2026-03-02','2026-03-05',5000,'Ordered','Urgent supply'); INSERT INTO procurement_creditors (supplier_id, invoice_no, invoice_date, due_date, description, amount, amount_paid, status, notes) VALUES (1,'CRINV/2026/0001','2026-03-05','2026-03-20','Printer Paper Supply',5000,3000,'Part Paid','Balance pending'); INSERT INTO procurement_creditor_payments (creditor_id, payment_date, amount_paid, reference_no, notes) VALUES (1,'2026-03-10',3000,'PAYREF001','Part payment'); INSERT INTO procurement_debtors (debtor_name, phone, email, invoice_no, invoice_date, due_date, description, amount, amount_paid, status, notes) VALUES ('Community Hall Hire','0719000000','client@example.com','DBINV/2026/0001','2026-03-06','2026-03-15','Hall hire charges',8000,4000,'Part Paid','Pending balance'); INSERT INTO procurement_debtor_receipts (debtor_id, receipt_date, amount_received, reference_no, notes) VALUES (1,'2026-03-08',4000,'DREC001','Initial payment'); INSERT INTO chart_of_accounts (account_code, account_name, account_type, category, is_system) VALUES ('1000', 'Cash at Bank', 'Asset', 'Cash', 1), ('1100', 'Student Receivables', 'Asset', 'Receivables', 1), ('1200', 'Trade Debtors', 'Asset', 'Receivables', 1), ('1300', 'Inventory / Stores', 'Asset', 'Inventory', 0), ('2000', 'Trade Creditors', 'Liability', 'Payables', 1), ('2100', 'Accrued Liabilities', 'Liability', 'Payables', 0), ('3000', 'Accumulated Fund', 'Equity', 'Equity', 1), ('3100', 'Current Year Earnings', 'Equity', 'Equity', 1), ('4000', 'Student Fee Revenue', 'Revenue', 'Income', 1), ('4010', 'Other Income', 'Revenue', 'Income', 1), ('5000', 'Procurement / Operating Expense', 'Expense', 'Expenses', 1), ('5100', 'Administrative Expense', 'Expense', 'Expenses', 0); INSERT INTO accounting_periods (period_name, start_date, end_date, status, notes) VALUES ('March 2026','2026-03-01','2026-03-31','Open','Default accounting period'); INSERT INTO settings (id, college_name, institution_code, address_line, phone, email, website, registrar_name, institution_motto, institution_logo, dashboard_logo, letterhead_right_logo, current_session, semester_status, transcript_title, transcript_subtitle, transcript_logo, transcript_contacts, transcript_footer, transcript_signature_label, transcript_signature_name, transcript_signature_title, grading_mode, mpesa_shortcode, level4_theory_weight, level4_practical_weight, level5_theory_weight, level5_practical_weight, level6_theory_weight, level6_practical_weight, id_card_logo, id_card_college_name, id_card_return_to, id_card_contacts, id_card_title, id_card_subtitle, id_card_footer, id_card_signature_label, id_card_header_align, id_card_show_contacts, id_card_show_status, id_card_back_title, id_card_return_label, id_card_back_footer, id_card_show_back, id_card_show_barcode, login_page_image, login_page_logo, login_page_hover_text, login_heading_text, login_subheading_text, login_username_label, login_password_label, login_remember_text, login_forgot_text, login_button_text, login_signup_prompt, login_signup_text, login_support_label, login_footer_text, college_page_image, college_page_hover_text, landing_theme_primary_color, landing_theme_panel_bg, landing_theme_card_bg, landing_theme_input_bg, landing_theme_button_color) VALUES (1, 'Skulitech Resources', 'SKULITECH', 'P.O Box 60, Moi University, Eldoret Kenya', '0728495194', '', 'skulitech.ac.ke', 'Registrar', 'Smart Digital Resources for Institutions', NULL, NULL, NULL, '2025/2026 - Session 3 / Module 1', 'Open', 'OFFICIAL ACADEMIC TRANSCRIPT', 'This transcript is valid only when signed and stamped by the Registrar.', NULL, 'P.O Box 60, Moi University, Eldoret Kenya | Tel: 0728495194 | Website: skulitech.ac.ke', 'This transcript is valid only when signed and stamped by the Registrar.', 'Authorized By', 'Registrar', 'Registrar', 'Percentage', '174379', 30, 70, 60, 40, 50, 50, NULL, 'Skulitech Resources', 'Registrar Office, Skulitech Resources, P.O Box 60, Moi University, Eldoret Kenya', 'Tel: 0728495194 Website: skulitech.ac.ke', 'STUDENT ID CARD', 'Official Student Identity Card', 'Property of Skulitech Resources. Return if found.', 'Authorized Signatory', 'center', 1, 1, 'CARD BACK / RETURN INFORMATION', 'If found, please return to:', 'This card remains the property of the institution. It must be surrendered on request or upon completion / withdrawal.', 1, 1, 'assets/img/login-campus-reference.jpg', 'assets/img/login-reference-logo.png', 'Smart Digital Resources for Institutions', 'Hi, welcome back', 'Please fill in your details to log in', 'Username', 'Password', 'Remember me', 'Forgot Password?', 'Sign In', 'Don’t have an account ?', 'Sign Up', 'Support', 'Copyright © 2026 Skulitech ERP', 'assets/img/login-campus-reference.jpg', 'Welcome to Skulitech Resources online application portal.', '#003F86', '#F4F5FB', '#FFFFFF', '#E5EDF9', '#003F86'); UPDATE settings SET institution_logo='uploads/logos/cort.png', dashboard_logo='uploads/logos/skulitechlogo.png', letterhead_right_logo='uploads/logos/skulitechlogo.png', login_page_logo='uploads/logos/skulitechlogo.png', login_page_image='uploads/logos/skulitech.png', college_page_image='uploads/logos/skulitech.png', exam_card_logo='uploads/logos/skulitechlogo.png', statement_footer='This statement is system-generated and reflects transactions recorded as of the date shown. All payments are subject to verification against official receipts and institutional records.', receipt_footer='This reciept is system-generated and reflects transactions recorded as of the date shown.' WHERE id=1; UPDATE students s SET s.fee_balance = GREATEST(s.fee_total - COALESCE((SELECT SUM(fp.amount_paid) FROM fee_payments fp WHERE fp.student_admission_no = s.admission_no),0),0); -- -------------------------------------------------------- -- Skulitech Resources institution settings seed/update -- -------------------------------------------------------- INSERT INTO settings (id, college_name, institution_code, address_line, phone, email, website, registrar_name, institution_motto, transcript_contacts, id_card_college_name, id_card_return_to, id_card_contacts, id_card_footer, login_page_hover_text, login_footer_text, college_page_hover_text) VALUES (1, 'Skulitech Resources', 'SKULITECH', 'P.O Box 60, Moi University, Eldoret Kenya', '0728495194', '', 'skulitech.ac.ke', 'Registrar', 'Smart Digital Resources for Institutions', 'P.O Box 60, Moi University, Eldoret Kenya | Tel: 0728495194 | Website: skulitech.ac.ke', 'Skulitech Resources', 'Registrar Office, Skulitech Resources, P.O Box 60, Moi University, Eldoret Kenya', 'Tel: 0728495194\nWebsite: skulitech.ac.ke', 'Property of Skulitech Resources. Return if found.', 'Smart Digital Resources for Institutions', 'Copyright © 2026 Skulitech ERP', 'Welcome to Skulitech Resources online application portal.') ON DUPLICATE KEY UPDATE college_name=VALUES(college_name), institution_code=VALUES(institution_code), address_line=VALUES(address_line), phone=VALUES(phone), website=VALUES(website), registrar_name=VALUES(registrar_name), institution_motto=VALUES(institution_motto), transcript_contacts=VALUES(transcript_contacts), id_card_college_name=VALUES(id_card_college_name), id_card_return_to=VALUES(id_card_return_to), id_card_contacts=VALUES(id_card_contacts), id_card_footer=VALUES(id_card_footer), login_page_hover_text=VALUES(login_page_hover_text), login_footer_text=VALUES(login_footer_text), college_page_hover_text=VALUES(college_page_hover_text); SET FOREIGN_KEY_CHECKS = 1; -- -------------------------------------------------------- -- Timetable auto-generation support -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS timetable_generation_rules ( id INT AUTO_INCREMENT PRIMARY KEY, class_id INT NULL, course_unit_id INT NULL, unit_id INT NULL, lecturer_user_id INT NULL, class_name VARCHAR(100) NOT NULL, course VARCHAR(120) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, year_level VARCHAR(20) NULL, unit_code VARCHAR(30) NOT NULL, lecturer_name VARCHAR(150) NULL, hours_per_week INT NOT NULL DEFAULT 2, lesson_pattern VARCHAR(20) NOT NULL DEFAULT 'single', preferred_venue VARCHAR(120) NULL, shared_group_key VARCHAR(120) NULL, is_shared TINYINT(1) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, notes VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_timetable_rule (class_name, course, session_name, semester_name, year_level, unit_code), KEY idx_timetable_rule_lookup (semester_name, class_name, lecturer_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS timetable_venues ( id INT AUTO_INCREMENT PRIMARY KEY, venue_name VARCHAR(120) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, priority_no INT NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_timetable_venue (venue_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS timetable_slots ( id INT AUTO_INCREMENT PRIMARY KEY, day_name VARCHAR(20) NOT NULL, slot_label VARCHAR(50) NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL, slot_order INT NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_day_slot_order (day_name, slot_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -------------------------------------------------------- -- Student session clearance workflow -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS student_clearances ( id INT AUTO_INCREMENT PRIMARY KEY, clearance_no VARCHAR(60) NOT NULL, student_admission_no VARCHAR(50) NOT NULL, student_name VARCHAR(160) NOT NULL, department VARCHAR(120) NULL, hod_name VARCHAR(120) NULL, course VARCHAR(150) NULL, class_name VARCHAR(120) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(100) NULL, status VARCHAR(40) NOT NULL DEFAULT 'Pending', clearance_type VARCHAR(60) NULL, student_notes TEXT NULL, registrar_final_notes TEXT NULL, accumulated_arrears DECIMAL(12,2) NOT NULL DEFAULT 0, arrears_comment TEXT NULL, arrears_checked_at DATETIME NULL, generated_by VARCHAR(120) NULL, initiated_by VARCHAR(120) NULL, initiated_at DATETIME NULL, finalized_by VARCHAR(120) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_student_clearance_no (clearance_no), UNIQUE KEY uq_student_semester_clearance (student_admission_no, session_name, semester_name, academic_year), KEY idx_student_clearance_status (status, semester_name, class_name), KEY idx_student_clearance_lookup (student_admission_no, student_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS student_clearance_steps ( id INT AUTO_INCREMENT PRIMARY KEY, clearance_id INT NOT NULL, office_key VARCHAR(40) NOT NULL, office_label VARCHAR(160) NOT NULL, officer_name VARCHAR(150) NULL, clearance_status VARCHAR(30) NOT NULL DEFAULT 'Pending', remarks TEXT NULL, signed_at DATETIME NULL, signed_by VARCHAR(120) NULL, sort_order INT NOT NULL DEFAULT 0, is_final_approver TINYINT(1) NOT NULL DEFAULT 0, is_finance_auto TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_student_clearance_step (clearance_id, office_key), KEY idx_student_clearance_steps_lookup (clearance_id, sort_order, clearance_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS student_clearance_offices ( id INT AUTO_INCREMENT PRIMARY KEY, office_key VARCHAR(60) NOT NULL, office_label VARCHAR(160) NOT NULL, officer_name VARCHAR(150) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, is_final_approver TINYINT(1) NOT NULL DEFAULT 0, is_finance_auto TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_student_clearance_office_key (office_key), KEY idx_student_clearance_offices_active (is_active, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT IGNORE INTO student_clearance_offices (office_key, office_label, officer_name, sort_order, is_active, is_final_approver, is_finance_auto) VALUES ('hod','Head of Department','',10,1,0,0), ('librarian','Library / Librarian','',20,1,0,0), ('dean','Dean of Students','',30,1,0,0), ('deputy','Deputy Principal','',40,1,0,0), ('examinations','Examinations Office','',50,1,0,0), ('ilo','Industrial Liaison Office (ILO)','',60,1,0,0), ('finance','Finance Office','',70,1,0,1), ('registrar','Registrar (Final Session Clearance)','',80,1,1,0); -- Keep exam marks editable by default UPDATE marks SET is_locked=0, locked_at=NULL; -- Procurement workflow enhancements: requisitions, approvals, suppliers, LPOs, invoices and reports -- Procurement enhancement columns are included in the base table definitions above. -- ======================================================== -- Current hardening schema additions -- ======================================================== -- Skulitech ERP audit hardening migration -- Version: 20260713_001 -- Apply with: php bin/migrate.php CREATE TABLE IF NOT EXISTS schema_migrations ( id INT AUTO_INCREMENT PRIMARY KEY, migration_name VARCHAR(190) NOT NULL UNIQUE, checksum_sha256 CHAR(64) NOT NULL, applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS public_request_attempts ( id BIGINT AUTO_INCREMENT PRIMARY KEY, scope_name VARCHAR(80) NOT NULL, identifier_hash CHAR(64) NOT NULL, ip_address VARCHAR(45) NULL, was_successful TINYINT(1) NOT NULL DEFAULT 0, attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_public_attempt_scope (scope_name, identifier_hash, attempted_at), KEY idx_public_attempt_ip (ip_address, attempted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS accounting_reversals ( id INT AUTO_INCREMENT PRIMARY KEY, original_journal_id INT NOT NULL, reversal_journal_id INT NOT NULL, reason TEXT NULL, reversed_by VARCHAR(150) NULL, reversed_at DATETIME DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_accounting_reversal_original (original_journal_id), KEY idx_accounting_reversal_reversal (reversal_journal_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS backup_files ( id INT AUTO_INCREMENT PRIMARY KEY, file_name VARCHAR(255) NOT NULL, file_path VARCHAR(255) NOT NULL, file_size BIGINT NOT NULL DEFAULT 0, created_by VARCHAR(150) DEFAULT '', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_backup_files_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS entity_audit_logs ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, full_name VARCHAR(180) DEFAULT '', role_name VARCHAR(120) DEFAULT '', module_name VARCHAR(120) DEFAULT '', entity_type VARCHAR(120) DEFAULT '', entity_id VARCHAR(120) DEFAULT '', action_name VARCHAR(120) DEFAULT '', summary_text VARCHAR(255) DEFAULT '', before_state LONGTEXT NULL, after_state LONGTEXT NULL, ip_address VARCHAR(60) DEFAULT '', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_entity_audit_lookup (module_name, entity_type, entity_id), KEY idx_entity_audit_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_approval_settings ( id INT AUTO_INCREMENT PRIMARY KEY, transaction_type VARCHAR(80) NOT NULL, threshold_amount DECIMAL(14,2) NOT NULL DEFAULT 0, approver_role VARCHAR(80) NOT NULL DEFAULT 'Admin', is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_approval_txn (transaction_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_approvals ( id INT AUTO_INCREMENT PRIMARY KEY, transaction_type VARCHAR(80) NOT NULL, transaction_ref VARCHAR(120) NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, summary VARCHAR(255) NULL, payload LONGTEXT NULL, requested_by VARCHAR(150) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Pending', approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, executed_ref VARCHAR(120) NULL, rejection_reason TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_finance_approvals_status (status, transaction_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_bank_statement_lines ( id INT AUTO_INCREMENT PRIMARY KEY, statement_date DATE NOT NULL, reference_no VARCHAR(120) NULL, description VARCHAR(255) NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, entry_type VARCHAR(20) NOT NULL DEFAULT 'Credit', match_status VARCHAR(20) NOT NULL DEFAULT 'Open', matched_source VARCHAR(80) NULL, matched_ref VARCHAR(120) NULL, imported_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_bank_statement_match (match_status, statement_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_budgets ( id INT AUTO_INCREMENT PRIMARY KEY, budget_year INT NOT NULL, budget_month TINYINT NOT NULL DEFAULT 0, account_code VARCHAR(30) NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, notes VARCHAR(255) NULL, updated_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_budget (budget_year, budget_month, account_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_expense_categories ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_expenses ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, category_id INT NULL, invoice_id VARCHAR(80) NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, expense_date DATE NOT NULL, method VARCHAR(60) NULL, notes TEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_expense_date (expense_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_income_categories ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_incomes ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, category_id INT NULL, invoice_id VARCHAR(80) NULL, amount DECIMAL(12,2) NOT NULL DEFAULT 0, income_date DATE NOT NULL, method VARCHAR(60) NULL, notes TEXT NULL, created_by INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_income_date (income_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS library_categories ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(120) NOT NULL, status VARCHAR(20) DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_library_category (title) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS library_members ( id INT AUTO_INCREMENT PRIMARY KEY, member_type VARCHAR(30) NOT NULL, library_id VARCHAR(50) NOT NULL, ref_code VARCHAR(80) NULL, full_name VARCHAR(150) NOT NULL, occupation VARCHAR(150) NULL, phone VARCHAR(50) NULL, status VARCHAR(20) DEFAULT 'Pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_library_member (library_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS library_penalties ( id INT AUTO_INCREMENT PRIMARY KEY, issue_id INT NOT NULL, accession_no VARCHAR(50) NOT NULL, borrower_type VARCHAR(20) DEFAULT 'Student', borrower_ref VARCHAR(80) NOT NULL, borrower_name VARCHAR(150) NULL, due_date DATE NULL, return_date DATE NULL, overdue_days INT DEFAULT 0, daily_rate DECIMAL(12,2) DEFAULT 0, amount DECIMAL(12,2) DEFAULT 0, penalty_status VARCHAR(30) DEFAULT 'Pending', notes VARCHAR(255) NULL, finance_billing_id INT NULL, settled_at DATETIME NULL, settled_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_library_penalty_issue (issue_id), KEY idx_library_penalties_ref (borrower_type, borrower_ref, penalty_status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS login_attempts ( id INT AUTO_INCREMENT PRIMARY KEY, login_identifier VARCHAR(190) NOT NULL, ip_address VARCHAR(45) NOT NULL, was_successful TINYINT(1) NOT NULL DEFAULT 0, attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_login_attempt_identifier (login_identifier, attempted_at), KEY idx_login_attempt_ip (ip_address, attempted_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS staff_clearance_steps ( id INT AUTO_INCREMENT PRIMARY KEY, clearance_id INT NOT NULL, office_key VARCHAR(60) NOT NULL, office_label VARCHAR(150) NOT NULL, officer_name VARCHAR(150) NULL, clearance_status VARCHAR(30) NOT NULL DEFAULT 'Pending', remarks TEXT NULL, signed_at DATETIME NULL, signed_by VARCHAR(120) NULL, sort_order INT NOT NULL DEFAULT 0, is_final_approver TINYINT(1) NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY idx_staff_clearance_step (clearance_id, sort_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS staff_clearances ( id INT AUTO_INCREMENT PRIMARY KEY, clearance_no VARCHAR(60) NOT NULL, employee_no VARCHAR(50) NOT NULL, staff_name VARCHAR(160) NOT NULL, department VARCHAR(120) NULL, designation VARCHAR(150) NULL, session_name VARCHAR(100) NULL, academic_year VARCHAR(100) NULL, exit_reason VARCHAR(160) NULL, staff_notes TEXT NULL, status VARCHAR(40) NOT NULL DEFAULT 'Pending', generated_by VARCHAR(120) NULL, finalized_by VARCHAR(120) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_staff_clearance_no (clearance_no), UNIQUE KEY uq_staff_clearance_period (employee_no, session_name, academic_year), KEY idx_staff_clearance_status (status, department) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS student_progressions ( id INT AUTO_INCREMENT PRIMARY KEY, student_admission_no VARCHAR(60) NOT NULL, class_name VARCHAR(120) NULL, course VARCHAR(150) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, academic_year VARCHAR(100) NULL, year_level VARCHAR(40) NULL, units_attempted INT NOT NULL DEFAULT 0, units_passed INT NOT NULL DEFAULT 0, average_score DECIMAL(6,2) NOT NULL DEFAULT 0, fee_balance DECIMAL(12,2) NOT NULL DEFAULT 0, clearance_status VARCHAR(40) NOT NULL DEFAULT 'Pending', decision_status VARCHAR(40) NOT NULL DEFAULT 'Pending Review', decision_reason TEXT NULL, recommended_next_year_level VARCHAR(40) NULL, generated_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_student_progression_period (student_admission_no, session_name, semester_name, academic_year, year_level), KEY idx_student_progression_status (decision_status, academic_year, semester_name), KEY idx_student_progression_lookup (student_admission_no, class_name, course) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Columns formerly added during normal page requests. ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_picture VARCHAR(255) NULL AFTER phone, ADD COLUMN IF NOT EXISTS is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER profile_picture; ALTER TABLE settings ADD COLUMN IF NOT EXISTS api_token_hash VARCHAR(255) NULL, ADD COLUMN IF NOT EXISTS api_token_last_rotated_at DATETIME NULL, ADD COLUMN IF NOT EXISTS max_allowed_balance DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS backup_retention_days INT NOT NULL DEFAULT 30, ADD COLUMN IF NOT EXISTS exam_card_max_balance DECIMAL(12,2) NOT NULL DEFAULT 0, ADD COLUMN IF NOT EXISTS competency_pass_mark DECIMAL(5,2) NOT NULL DEFAULT 50, ADD COLUMN IF NOT EXISTS transcript_header VARCHAR(180) NULL, ADD COLUMN IF NOT EXISTS transcript_recommendation_label VARCHAR(180) NULL, ADD COLUMN IF NOT EXISTS statement_title VARCHAR(180) NULL, ADD COLUMN IF NOT EXISTS statement_subtitle VARCHAR(255) NULL; ALTER TABLE timetables ADD COLUMN IF NOT EXISTS year_level VARCHAR(20) NULL AFTER semester_name, ADD COLUMN IF NOT EXISTS class_names TEXT NULL AFTER lecturer_name, ADD COLUMN IF NOT EXISTS slot_start_order INT NULL AFTER class_names, ADD COLUMN IF NOT EXISTS slot_length INT NOT NULL DEFAULT 1 AFTER slot_start_order, ADD COLUMN IF NOT EXISTS lesson_pattern VARCHAR(20) NULL AFTER slot_length, ADD COLUMN IF NOT EXISTS shared_group_key VARCHAR(120) NULL AFTER lesson_pattern, ADD COLUMN IF NOT EXISTS generation_source VARCHAR(20) NULL DEFAULT 'manual' AFTER shared_group_key, ADD COLUMN IF NOT EXISTS generated_batch_id VARCHAR(40) NULL AFTER generation_source; ALTER TABLE library_issues ADD COLUMN IF NOT EXISTS borrower_name VARCHAR(150) NULL AFTER borrower_ref, ADD COLUMN IF NOT EXISTS phone VARCHAR(50) NULL AFTER borrower_name, ADD COLUMN IF NOT EXISTS penalty_per_day DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER return_date, ADD COLUMN IF NOT EXISTS overdue_days INT NOT NULL DEFAULT 0 AFTER penalty_per_day, ADD COLUMN IF NOT EXISTS penalty_amount DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER overdue_days, ADD COLUMN IF NOT EXISTS penalty_status VARCHAR(30) NOT NULL DEFAULT 'None' AFTER penalty_amount, ADD COLUMN IF NOT EXISTS penalty_billing_id INT NULL AFTER penalty_status, ADD COLUMN IF NOT EXISTS returned_by VARCHAR(150) NULL AFTER penalty_billing_id; -- Canonical academic-period identifiers and relational ID backfill. UPDATE sessions s JOIN academic_years ay ON ay.academic_year_name=s.academic_year_name SET s.academic_year_id=ay.id WHERE s.academic_year_id IS NULL OR s.academic_year_id<>ay.id; UPDATE semesters sm JOIN sessions s ON s.session_name=sm.session_name SET sm.session_id=s.id WHERE sm.session_id IS NULL OR sm.session_id<>s.id; UPDATE courses c JOIN departments d ON d.department_name=c.department SET c.department_id=d.id WHERE c.department_id IS NULL OR c.department_id<>d.id; UPDATE units u JOIN departments d ON d.department_name=u.department SET u.department_id=d.id WHERE u.department_id IS NULL OR u.department_id<>d.id; UPDATE classes c LEFT JOIN departments d ON d.department_name=c.department LEFT JOIN courses co ON co.course_name=c.course LEFT JOIN sessions s ON s.session_name=c.session_name LEFT JOIN semesters sm ON sm.session_name=c.session_name AND sm.semester_name=c.semester_name LEFT JOIN academic_years ay ON ay.id=s.academic_year_id SET c.department_id=d.id, c.course_id=co.id, c.session_id=s.id, c.semester_id=sm.id, c.academic_year_id=ay.id; UPDATE course_units cu LEFT JOIN courses c ON c.course_code=cu.course_code LEFT JOIN units u ON u.unit_code=cu.unit_code LEFT JOIN semesters sm ON sm.semester_name=cu.semester_name SET cu.course_id=c.id, cu.unit_id=u.id, cu.semester_id=sm.id WHERE cu.course_id IS NULL OR cu.unit_id IS NULL OR cu.semester_id IS NULL; UPDATE class_unit_assignments cua LEFT JOIN classes c ON c.class_name=cua.class_name AND (cua.course IS NULL OR c.course=cua.course) LEFT JOIN units u ON u.unit_code=cua.unit_code LEFT JOIN course_units cu ON cu.unit_id=u.id AND (c.course_id IS NULL OR cu.course_id=c.course_id) SET cua.class_id=c.id, cua.unit_id=u.id, cua.course_unit_id=cu.id WHERE cua.class_id IS NULL OR cua.unit_id IS NULL OR cua.course_unit_id IS NULL; UPDATE lecturer_unit_assignments lua LEFT JOIN users u ON u.full_name=lua.lecturer_name LEFT JOIN units un ON un.unit_code=lua.unit_code LEFT JOIN classes c ON c.course=lua.course AND c.semester_name=lua.semester_name AND c.year_level=lua.year_level LEFT JOIN course_units cu ON cu.unit_id=un.id AND (c.course_id IS NULL OR cu.course_id=c.course_id) SET lua.lecturer_user_id=u.id, lua.class_id=c.id, lua.course_unit_id=cu.id WHERE lua.lecturer_user_id IS NULL OR lua.class_id IS NULL OR lua.course_unit_id IS NULL; UPDATE student_unit_registrations sur LEFT JOIN students st ON st.admission_no=sur.student_admission_no LEFT JOIN classes c ON c.class_name=sur.class_name AND c.session_name=sur.session_name AND c.semester_name=sur.semester_name LEFT JOIN courses co ON co.course_name=sur.course LEFT JOIN units u ON u.unit_code=sur.unit_code LEFT JOIN course_units cu ON cu.unit_id=u.id AND (co.id IS NULL OR cu.course_id=co.id) LEFT JOIN sessions se ON se.session_name=sur.session_name LEFT JOIN academic_years ay ON ay.academic_year_name=sur.academic_year SET sur.student_id=st.id, sur.class_id=c.id, sur.course_id=co.id, sur.course_unit_id=cu.id, sur.unit_id=u.id, sur.session_id=se.id, sur.academic_year_id=ay.id; UPDATE semester_enrollments e LEFT JOIN students st ON st.admission_no=e.student_admission_no LEFT JOIN classes c ON c.class_name=e.class_name AND c.session_name=e.session_name AND c.semester_name=e.semester_name LEFT JOIN courses co ON co.course_name=e.course LEFT JOIN sessions se ON se.session_name=e.session_name LEFT JOIN academic_years ay ON ay.academic_year_name=e.academic_year SET e.student_id=st.id, e.class_id=c.id, e.course_id=co.id, e.session_id=se.id, e.academic_year_id=ay.id; UPDATE marks m LEFT JOIN students st ON st.admission_no=m.student_admission_no LEFT JOIN classes c ON c.class_name=m.class_name AND c.session_name=m.session_name AND c.semester_name=m.semester_name LEFT JOIN courses co ON co.course_name=m.course LEFT JOIN units u ON u.unit_code=m.unit_code LEFT JOIN course_units cu ON cu.unit_id=u.id AND (co.id IS NULL OR cu.course_id=co.id) LEFT JOIN sessions se ON se.session_name=m.session_name LEFT JOIN academic_years ay ON ay.academic_year_name=m.academic_year SET m.student_id=st.id, m.class_id=c.id, m.course_id=co.id, m.course_unit_id=cu.id, m.unit_id=u.id, m.session_id=se.id, m.academic_year_id=ay.id; UPDATE timetables t LEFT JOIN classes c ON c.class_name=t.class_name AND c.session_name=t.session_name AND c.semester_name=t.semester_name LEFT JOIN units u ON u.unit_code=t.unit_code LEFT JOIN course_units cu ON cu.unit_id=u.id AND (c.course_id IS NULL OR cu.course_id=c.course_id) LEFT JOIN users lu ON lu.full_name=t.lecturer_name SET t.class_id=c.id, t.unit_id=u.id, t.course_unit_id=cu.id, t.lecturer_user_id=lu.id; -- Remove invalid IDs before relational constraints are enabled. UPDATE users u LEFT JOIN departments d ON d.id=u.department_id SET u.department_id=NULL WHERE u.department_id IS NOT NULL AND d.id IS NULL; UPDATE courses c LEFT JOIN departments d ON d.id=c.department_id SET c.department_id=NULL WHERE c.department_id IS NOT NULL AND d.id IS NULL; UPDATE units u LEFT JOIN departments d ON d.id=u.department_id SET u.department_id=NULL WHERE u.department_id IS NOT NULL AND d.id IS NULL; UPDATE entity_audit_logs a LEFT JOIN users u ON u.id=a.user_id SET a.user_id=NULL WHERE a.user_id IS NOT NULL AND u.id IS NULL; -- Database-enforced integrity for the highest-value academic, audit, finance and workflow relationships. ALTER TABLE sessions ADD CONSTRAINT fk_sessions_academic_year FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE RESTRICT; ALTER TABLE semesters ADD CONSTRAINT fk_semesters_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE RESTRICT; ALTER TABLE users ADD CONSTRAINT fk_users_department FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE courses ADD CONSTRAINT fk_courses_department FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE units ADD CONSTRAINT fk_units_department FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE course_modules ADD CONSTRAINT fk_course_modules_course FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE classes ADD CONSTRAINT fk_classes_department FOREIGN KEY (department_id) REFERENCES departments(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE classes ADD CONSTRAINT fk_classes_course FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE classes ADD CONSTRAINT fk_classes_academic_year FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE classes ADD CONSTRAINT fk_classes_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE classes ADD CONSTRAINT fk_classes_semester FOREIGN KEY (semester_id) REFERENCES semesters(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE classes ADD CONSTRAINT fk_classes_course_module FOREIGN KEY (course_module_id) REFERENCES course_modules(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE course_units ADD CONSTRAINT fk_course_units_course FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE course_units ADD CONSTRAINT fk_course_units_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE course_units ADD CONSTRAINT fk_course_units_module FOREIGN KEY (course_module_id) REFERENCES course_modules(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE course_units ADD CONSTRAINT fk_course_units_semester FOREIGN KEY (semester_id) REFERENCES semesters(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE class_unit_assignments ADD CONSTRAINT fk_class_unit_class FOREIGN KEY (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE class_unit_assignments ADD CONSTRAINT fk_class_unit_course_unit FOREIGN KEY (course_unit_id) REFERENCES course_units(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE class_unit_assignments ADD CONSTRAINT fk_class_unit_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE lecturer_unit_assignments ADD CONSTRAINT fk_lecturer_unit_user FOREIGN KEY (lecturer_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE lecturer_unit_assignments ADD CONSTRAINT fk_lecturer_unit_class FOREIGN KEY (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE lecturer_unit_assignments ADD CONSTRAINT fk_lecturer_unit_course_unit FOREIGN KEY (course_unit_id) REFERENCES course_units(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE student_unit_registrations ADD CONSTRAINT fk_sur_student FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE student_unit_registrations ADD CONSTRAINT fk_sur_class FOREIGN KEY (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE student_unit_registrations ADD CONSTRAINT fk_sur_course FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE student_unit_registrations ADD CONSTRAINT fk_sur_course_unit FOREIGN KEY (course_unit_id) REFERENCES course_units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE student_unit_registrations ADD CONSTRAINT fk_sur_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE student_unit_registrations ADD CONSTRAINT fk_sur_academic_year FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE student_unit_registrations ADD CONSTRAINT fk_sur_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE semester_enrollments ADD CONSTRAINT fk_enrol_student FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE semester_enrollments ADD CONSTRAINT fk_enrol_class FOREIGN KEY (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE semester_enrollments ADD CONSTRAINT fk_enrol_course FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE semester_enrollments ADD CONSTRAINT fk_enrol_academic_year FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE semester_enrollments ADD CONSTRAINT fk_enrol_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE marks ADD CONSTRAINT fk_marks_student FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE marks ADD CONSTRAINT fk_marks_class FOREIGN KEY (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE marks ADD CONSTRAINT fk_marks_course FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE marks ADD CONSTRAINT fk_marks_course_unit FOREIGN KEY (course_unit_id) REFERENCES course_units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE marks ADD CONSTRAINT fk_marks_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE marks ADD CONSTRAINT fk_marks_academic_year FOREIGN KEY (academic_year_id) REFERENCES academic_years(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE marks ADD CONSTRAINT fk_marks_session FOREIGN KEY (session_id) REFERENCES sessions(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE timetables ADD CONSTRAINT fk_timetable_class FOREIGN KEY (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE timetables ADD CONSTRAINT fk_timetable_course_unit FOREIGN KEY (course_unit_id) REFERENCES course_units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE timetables ADD CONSTRAINT fk_timetable_unit FOREIGN KEY (unit_id) REFERENCES units(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE timetables ADD CONSTRAINT fk_timetable_lecturer FOREIGN KEY (lecturer_user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE student_clearance_steps ADD CONSTRAINT fk_student_clearance_step_header FOREIGN KEY (clearance_id) REFERENCES student_clearances(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE staff_clearance_steps ADD CONSTRAINT fk_staff_clearance_step_header FOREIGN KEY (clearance_id) REFERENCES staff_clearances(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE library_penalties ADD CONSTRAINT fk_library_penalty_issue FOREIGN KEY (issue_id) REFERENCES library_issues(id) ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE accounting_reversals ADD CONSTRAINT fk_accounting_reversal_original FOREIGN KEY (original_journal_id) REFERENCES accounting_journals(id) ON UPDATE CASCADE ON DELETE RESTRICT; ALTER TABLE accounting_reversals ADD CONSTRAINT fk_accounting_reversal_entry FOREIGN KEY (reversal_journal_id) REFERENCES accounting_journals(id) ON UPDATE CASCADE ON DELETE RESTRICT; ALTER TABLE finance_incomes ADD CONSTRAINT fk_income_category FOREIGN KEY (category_id) REFERENCES finance_income_categories(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE finance_incomes ADD CONSTRAINT fk_income_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE finance_expenses ADD CONSTRAINT fk_expense_category FOREIGN KEY (category_id) REFERENCES finance_expense_categories(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE finance_expenses ADD CONSTRAINT fk_expense_creator FOREIGN KEY (created_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE entity_audit_logs ADD CONSTRAINT fk_entity_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL; UPDATE settings SET competency_pass_mark=COALESCE(NULLIF(competency_pass_mark,0),50), current_session=CASE WHEN current_session IS NULL OR current_session='' OR current_session='Module 1 2026' THEN '2025/2026 - Session 3 / Module 1' ELSE current_session END WHERE id=1; -- Register bundled migration for clean installations. INSERT IGNORE INTO schema_migrations(migration_name, checksum_sha256) VALUES ('20260713_001_audit_hardening.sql', 'f6f837a3518a83813b380e830c67ec490587ed1ee0afd90080393c2c3b2507dd'); -- ======================================================== -- Sponsor funds, bulk allocations and accrual accounting -- ======================================================== -- Skulitech ERP sponsor funds and accrual accounting migration -- Version: 20260713_002 -- Supports HELB, County bursaries, NG-CDF bursaries, scholarships and other sponsors. CREATE TABLE IF NOT EXISTS funding_sources ( id INT AUTO_INCREMENT PRIMARY KEY, source_code VARCHAR(40) NOT NULL, source_name VARCHAR(180) NOT NULL, source_type VARCHAR(50) NOT NULL DEFAULT 'Other Sponsor', programme_name VARCHAR(180) NULL, jurisdiction_name VARCHAR(180) NULL, sponsor_identifier VARCHAR(100) NULL, contact_person VARCHAR(150) NULL, phone VARCHAR(50) NULL, email VARCHAR(150) NULL, status VARCHAR(20) NOT NULL DEFAULT 'Active', created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_funding_source_code (source_code), KEY idx_funding_source_type_status (source_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS sponsor_awards ( id INT AUTO_INCREMENT PRIMARY KEY, award_no VARCHAR(60) NOT NULL, source_id INT NOT NULL, award_date DATE NOT NULL, expected_receipt_date DATE NULL, amount_awarded DECIMAL(14,2) NOT NULL DEFAULT 0, amount_received DECIMAL(14,2) NOT NULL DEFAULT 0, academic_year VARCHAR(40) NULL, semester_name VARCHAR(100) NULL, reference_no VARCHAR(120) NULL, recognition_basis VARCHAR(20) NOT NULL DEFAULT 'Accrual', status VARCHAR(30) NOT NULL DEFAULT 'Draft', journal_id INT NULL, notes TEXT NULL, created_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_sponsor_award_no (award_no), KEY idx_sponsor_award_source_status (source_id, status), KEY idx_sponsor_award_period (academic_year, semester_name), KEY idx_sponsor_award_journal (journal_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS sponsor_receipts ( id INT AUTO_INCREMENT PRIMARY KEY, receipt_no VARCHAR(60) NOT NULL, source_id INT NOT NULL, award_id INT NULL, receipt_date DATE NOT NULL, amount_received DECIMAL(14,2) NOT NULL DEFAULT 0, payment_method VARCHAR(40) NOT NULL DEFAULT 'Bank Transfer', bank_account_name VARCHAR(150) NULL, bank_reference VARCHAR(150) NOT NULL, cheque_no VARCHAR(100) NULL, remittance_reference VARCHAR(150) NULL, clearance_status VARCHAR(30) NOT NULL DEFAULT 'Cleared', cleared_date DATE NULL, academic_year VARCHAR(40) NULL, semester_name VARCHAR(100) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Draft', journal_id INT NULL, notes TEXT NULL, created_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, reversed_by VARCHAR(150) NULL, reversed_at DATETIME NULL, reversal_reason TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_sponsor_receipt_no (receipt_no), UNIQUE KEY uq_sponsor_bank_reference (bank_reference), KEY idx_sponsor_receipt_source_status (source_id, status), KEY idx_sponsor_receipt_period (academic_year, semester_name), KEY idx_sponsor_receipt_award (award_id), KEY idx_sponsor_receipt_journal (journal_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS sponsor_allocation_batches ( id INT AUTO_INCREMENT PRIMARY KEY, batch_no VARCHAR(60) NOT NULL, source_id INT NOT NULL, receipt_id INT NOT NULL, batch_date DATE NOT NULL, academic_year VARCHAR(40) NULL, semester_name VARCHAR(100) NULL, batch_reference VARCHAR(150) NULL, expected_amount DECIMAL(14,2) NOT NULL DEFAULT 0, total_allocated DECIMAL(14,2) NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'Draft', journal_id INT NULL, notes TEXT NULL, created_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, reversed_by VARCHAR(150) NULL, reversed_at DATETIME NULL, reversal_reason TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_sponsor_batch_no (batch_no), KEY idx_sponsor_batch_receipt_status (receipt_id, status), KEY idx_sponsor_batch_period (academic_year, semester_name), KEY idx_sponsor_batch_source (source_id), KEY idx_sponsor_batch_journal (journal_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS sponsor_allocations ( id INT AUTO_INCREMENT PRIMARY KEY, batch_id INT NOT NULL, student_admission_no VARCHAR(50) NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, line_reference VARCHAR(150) NOT NULL DEFAULT '', beneficiary_reference VARCHAR(150) NULL, remarks VARCHAR(255) NULL, allocation_date DATE NOT NULL, academic_year VARCHAR(40) NULL, semester_name VARCHAR(100) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Pending', created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_sponsor_allocation_line (batch_id, student_admission_no, line_reference), KEY idx_sponsor_allocation_student_status (student_admission_no, status), KEY idx_sponsor_allocation_batch_status (batch_id, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO chart_of_accounts (account_code, account_name, account_type, category, is_system) VALUES ('1110', 'Sponsor Bursary and Scholarship Receivables', 'Asset', 'Receivables', 1), ('1160', 'Prepayments', 'Asset', 'Prepayments', 0), ('1400', 'Property, Plant and Equipment', 'Asset', 'Non-current Assets', 0), ('1490', 'Accumulated Depreciation', 'Asset', 'Contra Asset', 0), ('2110', 'Employee Benefits Payable', 'Liability', 'Employee Benefits', 0), ('2200', 'Sponsor Funds Unallocated Control', 'Liability', 'Student Sponsor Funds', 1), ('2300', 'Deferred Income', 'Liability', 'Deferred Revenue', 0), ('4020', 'Grants and Transfers Revenue', 'Revenue', 'Non-exchange Revenue', 0), ('5200', 'Depreciation and Amortisation Expense', 'Expense', 'Expenses', 0) ON DUPLICATE KEY UPDATE account_name=VALUES(account_name), account_type=VALUES(account_type), category=VALUES(category), is_system=VALUES(is_system); INSERT INTO funding_sources(source_code,source_name,source_type,status,created_by) VALUES ('HELB','Higher Education Loans Board (HELB)','HELB','Active','System'), ('NGCDF','National Government Constituencies Development Fund (NG-CDF)','NG-CDF Bursary','Active','System'), ('COUNTY','County Government Bursary','County Bursary','Active','System'), ('SCHOLARSHIP','Scholarship Sponsor','Scholarship','Active','System'), ('OTHER','Other Sponsor / Donor','Other Sponsor','Active','System') ON DUPLICATE KEY UPDATE source_name=VALUES(source_name),source_type=VALUES(source_type),status=VALUES(status); UPDATE roles SET permissions = CONCAT_WS(',', NULLIF(TRIM(BOTH ',' FROM permissions), ''), 'finance_sponsors') WHERE permissions <> 'all' AND FIND_IN_SET('finance_sponsors', permissions) = 0 AND (role_name IN ('Admin','Finance') OR FIND_IN_SET('finance_fees', permissions) > 0); ALTER TABLE sponsor_awards ADD CONSTRAINT fk_sponsor_award_source FOREIGN KEY (source_id) REFERENCES funding_sources(id) ON UPDATE CASCADE ON DELETE RESTRICT, ADD CONSTRAINT fk_sponsor_award_journal FOREIGN KEY (journal_id) REFERENCES accounting_journals(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE sponsor_receipts ADD CONSTRAINT fk_sponsor_receipt_source FOREIGN KEY (source_id) REFERENCES funding_sources(id) ON UPDATE CASCADE ON DELETE RESTRICT, ADD CONSTRAINT fk_sponsor_receipt_award FOREIGN KEY (award_id) REFERENCES sponsor_awards(id) ON UPDATE CASCADE ON DELETE SET NULL, ADD CONSTRAINT fk_sponsor_receipt_journal FOREIGN KEY (journal_id) REFERENCES accounting_journals(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE sponsor_allocation_batches ADD CONSTRAINT fk_sponsor_batch_source FOREIGN KEY (source_id) REFERENCES funding_sources(id) ON UPDATE CASCADE ON DELETE RESTRICT, ADD CONSTRAINT fk_sponsor_batch_receipt FOREIGN KEY (receipt_id) REFERENCES sponsor_receipts(id) ON UPDATE CASCADE ON DELETE RESTRICT, ADD CONSTRAINT fk_sponsor_batch_journal FOREIGN KEY (journal_id) REFERENCES accounting_journals(id) ON UPDATE CASCADE ON DELETE SET NULL; ALTER TABLE sponsor_allocations ADD CONSTRAINT fk_sponsor_allocation_batch FOREIGN KEY (batch_id) REFERENCES sponsor_allocation_batches(id) ON UPDATE CASCADE ON DELETE CASCADE, ADD CONSTRAINT fk_sponsor_allocation_student FOREIGN KEY (student_admission_no) REFERENCES students(admission_no) ON UPDATE CASCADE ON DELETE RESTRICT; INSERT IGNORE INTO schema_migrations(migration_name, checksum_sha256) VALUES ('20260713_002_sponsor_funds_accrual.sql', 'd7cd8344961a84004b23a5fb7d0bf4b36cf957112f2426023bd9ee67f3478609'); -- Version: 20260714_003 INSERT IGNORE INTO schema_migrations(migration_name, checksum_sha256) VALUES ('20260714_003_bulk_sponsor_receipting.sql', '3cacaf867aff96aee4f338265192a825b4ce154da073dfe47516e9c1a47371f5'); -- The cPanel package intentionally does not create a predictable administrator. -- Create the first administrator with cpanel_setup.php or php bin/create_admin.php. INSERT IGNORE INTO schema_migrations(migration_name, checksum_sha256) VALUES ('20260714_005_default_admin_login.sql', '91d092ad99282e34ea6336752f50fa33c7d2e8ab80caa2fd04e89e87bb33bc34'); -- Version: 20260724_011 payments, expenses and payroll controls -- Skulitech ERP payments, expenses and payroll control upgrade -- Version: 20260724_011 -- Adds only controls that were not already available: staff expense claims, -- recurring expenses, accrual/prepayment schedules, payroll batches, -- effective-dated payroll rules, approval/locking and payroll GL settlement. CREATE TABLE IF NOT EXISTS finance_expense_claims ( id INT AUTO_INCREMENT PRIMARY KEY, claim_no VARCHAR(60) NOT NULL, claimant_user_id INT NULL, claimant_name VARCHAR(180) NOT NULL, employee_id INT NULL, department VARCHAR(150) NULL, cost_centre_id INT NULL, project_code VARCHAR(80) NULL, claim_date DATE NOT NULL, expense_account_code VARCHAR(30) NOT NULL DEFAULT '5000', payment_method VARCHAR(40) NULL, bank_reference VARCHAR(120) NULL, purpose TEXT NOT NULL, total_amount DECIMAL(14,2) NOT NULL DEFAULT 0, attachment_path VARCHAR(255) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Draft', submitted_by VARCHAR(150) NULL, submitted_at DATETIME NULL, reviewed_by VARCHAR(150) NULL, reviewed_at DATETIME NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, paid_by VARCHAR(150) NULL, paid_at DATETIME NULL, rejected_by VARCHAR(150) NULL, rejected_at DATETIME NULL, rejection_reason TEXT NULL, journal_id INT NULL, payment_journal_id INT NULL, reversed_by VARCHAR(150) NULL, reversed_at DATETIME NULL, reversal_reason TEXT NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_expense_claim_no (claim_no), UNIQUE KEY uq_finance_expense_claim_bank_reference (bank_reference), KEY idx_finance_expense_claim_status (status, claim_date), KEY idx_finance_expense_claimant (employee_id, claimant_user_id), KEY idx_finance_expense_claim_dimension (cost_centre_id, project_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_expense_claim_items ( id INT AUTO_INCREMENT PRIMARY KEY, claim_id INT NOT NULL, expense_date DATE NOT NULL, description VARCHAR(255) NOT NULL, receipt_no VARCHAR(100) NULL, expense_account_code VARCHAR(30) NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, attachment_path VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_finance_expense_claim_item (claim_id, expense_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_recurring_expenses ( id INT AUTO_INCREMENT PRIMARY KEY, template_no VARCHAR(60) NOT NULL, title VARCHAR(180) NOT NULL, payee_name VARCHAR(180) NULL, description TEXT NULL, expense_account_code VARCHAR(30) NOT NULL DEFAULT '5000', cost_centre_id INT NULL, project_code VARCHAR(80) NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, frequency VARCHAR(30) NOT NULL DEFAULT 'Monthly', start_date DATE NOT NULL, end_date DATE NULL, next_due_date DATE NOT NULL, payment_method VARCHAR(40) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Active', last_generated_claim_id INT NULL, last_generated_at DATETIME NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_recurring_expense_no (template_no), KEY idx_finance_recurring_expense_due (status, next_due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_adjustment_schedules ( id INT AUTO_INCREMENT PRIMARY KEY, schedule_no VARCHAR(60) NOT NULL, adjustment_type VARCHAR(30) NOT NULL, title VARCHAR(180) NOT NULL, counterparty_name VARCHAR(180) NULL, description TEXT NULL, expense_account_code VARCHAR(30) NOT NULL DEFAULT '5000', control_account_code VARCHAR(30) NOT NULL, cost_centre_id INT NULL, project_code VARCHAR(80) NULL, start_date DATE NOT NULL, end_date DATE NULL, number_of_periods INT NOT NULL DEFAULT 1, total_amount DECIMAL(14,2) NOT NULL DEFAULT 0, released_amount DECIMAL(14,2) NOT NULL DEFAULT 0, settled_amount DECIMAL(14,2) NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'Draft', initial_journal_id INT NULL, created_by VARCHAR(150) NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, closed_by VARCHAR(150) NULL, closed_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_adjustment_schedule_no (schedule_no), KEY idx_finance_adjustment_status (adjustment_type, status, start_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_adjustment_transactions ( id INT AUTO_INCREMENT PRIMARY KEY, schedule_id INT NOT NULL, transaction_no VARCHAR(60) NOT NULL, transaction_date DATE NOT NULL, transaction_type VARCHAR(30) NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, bank_reference VARCHAR(120) NULL, journal_id INT NULL, notes VARCHAR(255) NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_adjustment_transaction_no (transaction_no), UNIQUE KEY uq_finance_adjustment_bank_reference (bank_reference), KEY idx_finance_adjustment_transaction_schedule (schedule_id, transaction_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_petty_cash_funds ( id INT AUTO_INCREMENT PRIMARY KEY, fund_code VARCHAR(40) NOT NULL, fund_name VARCHAR(150) NOT NULL, custodian_name VARCHAR(180) NULL, account_code VARCHAR(30) NOT NULL DEFAULT '1010', maximum_float DECIMAL(14,2) NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'Active', notes TEXT NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_petty_cash_fund_code (fund_code), KEY idx_finance_petty_cash_fund_status (status, fund_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_petty_cash_vouchers ( id INT AUTO_INCREMENT PRIMARY KEY, voucher_no VARCHAR(60) NOT NULL, fund_id INT NOT NULL, transaction_date DATE NOT NULL, transaction_type VARCHAR(30) NOT NULL, payee_name VARCHAR(180) NULL, purpose TEXT NOT NULL, expense_account_code VARCHAR(30) NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, payment_reference VARCHAR(120) NULL, attachment_path VARCHAR(255) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Draft', submitted_by VARCHAR(150) NULL, submitted_at DATETIME NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, journal_id INT NULL, reversed_by VARCHAR(150) NULL, reversed_at DATETIME NULL, reversal_reason TEXT NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_petty_cash_voucher_no (voucher_no), UNIQUE KEY uq_finance_petty_cash_reference (payment_reference), KEY idx_finance_petty_cash_voucher_fund (fund_id, transaction_date), KEY idx_finance_petty_cash_voucher_status (status, transaction_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_payroll_batches ( id INT AUTO_INCREMENT PRIMARY KEY, batch_no VARCHAR(60) NOT NULL, pay_month DATE NOT NULL, run_type VARCHAR(30) NOT NULL DEFAULT 'Regular', description VARCHAR(255) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Draft', employee_count INT NOT NULL DEFAULT 0, gross_total DECIMAL(14,2) NOT NULL DEFAULT 0, deduction_total DECIMAL(14,2) NOT NULL DEFAULT 0, net_total DECIMAL(14,2) NOT NULL DEFAULT 0, employer_cost_total DECIMAL(14,2) NOT NULL DEFAULT 0, prepared_by VARCHAR(150) NULL, prepared_at DATETIME NULL, checked_by VARCHAR(150) NULL, checked_at DATETIME NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, posted_by VARCHAR(150) NULL, posted_at DATETIME NULL, paid_by VARCHAR(150) NULL, paid_at DATETIME NULL, bank_reference VARCHAR(120) NULL, journal_id INT NULL, payment_journal_id INT NULL, locked_at DATETIME NULL, reversed_by VARCHAR(150) NULL, reversed_at DATETIME NULL, reversal_reason TEXT NULL, notes TEXT NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hr_payroll_batch_no (batch_no), UNIQUE KEY uq_hr_payroll_batch_month_type (pay_month, run_type), UNIQUE KEY uq_hr_payroll_batch_bank_reference (bank_reference), KEY idx_hr_payroll_batch_status (status, pay_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE hr_payroll ADD COLUMN batch_id INT NULL AFTER id; ALTER TABLE hr_payroll ADD COLUMN run_type VARCHAR(30) NOT NULL DEFAULT 'Regular' AFTER pay_month; ALTER TABLE hr_payroll ADD COLUMN cost_centre_id INT NULL AFTER designation; ALTER TABLE hr_payroll ADD COLUMN overtime_pay DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER other_allowance; ALTER TABLE hr_payroll ADD COLUMN arrears_pay DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER overtime_pay; ALTER TABLE hr_payroll ADD COLUMN final_dues DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER arrears_pay; ALTER TABLE hr_payroll ADD COLUMN nssf_employer DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER nssf_employee; ALTER TABLE hr_payroll ADD COLUMN housing_levy_employer DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER housing_levy; ALTER TABLE hr_payroll ADD COLUMN unpaid_leave_deduction DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER housing_levy_employer; ALTER TABLE hr_payroll ADD COLUMN loan_deduction DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER unpaid_leave_deduction; ALTER TABLE hr_payroll ADD COLUMN salary_advance_deduction DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER loan_deduction; ALTER TABLE hr_payroll ADD COLUMN pension_deduction DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER salary_advance_deduction; ALTER TABLE hr_payroll ADD COLUMN sacco_deduction DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER pension_deduction; ALTER TABLE hr_payroll ADD COLUMN court_order_deduction DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER sacco_deduction; ALTER TABLE hr_payroll ADD COLUMN attendance_reference VARCHAR(120) NULL AFTER owner_occupied_interest; ALTER TABLE hr_payroll ADD COLUMN adjustment_notes TEXT NULL AFTER attendance_reference; ALTER TABLE hr_payroll ADD COLUMN employer_cost DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER net_pay; ALTER TABLE hr_payroll ADD COLUMN payment_status VARCHAR(30) NOT NULL DEFAULT 'Unpaid' AFTER status; ALTER TABLE hr_payroll ADD COLUMN payment_reference VARCHAR(120) NULL AFTER payment_status; ALTER TABLE hr_payroll ADD COLUMN locked_at DATETIME NULL AFTER payment_reference; ALTER TABLE hr_payroll ADD COLUMN reversed_by VARCHAR(150) NULL AFTER locked_at; ALTER TABLE hr_payroll ADD COLUMN reversed_at DATETIME NULL AFTER reversed_by; ALTER TABLE hr_payroll ADD COLUMN reversal_reason TEXT NULL AFTER reversed_at; ALTER TABLE hr_payroll DROP INDEX uq_hr_payroll_employee_month; ALTER TABLE hr_payroll ADD UNIQUE KEY uq_hr_payroll_employee_month_run (employee_id, pay_month, run_type); ALTER TABLE hr_payroll ADD KEY idx_hr_payroll_batch (batch_id, status); ALTER TABLE hr_payroll ADD KEY idx_hr_payroll_payment_status (payment_status, pay_month); CREATE TABLE IF NOT EXISTS hr_statutory_rules ( id INT AUTO_INCREMENT PRIMARY KEY, rule_code VARCHAR(50) NOT NULL, rule_name VARCHAR(180) NOT NULL, effective_from DATE NOT NULL, effective_to DATE NULL, calculation_mode VARCHAR(30) NOT NULL DEFAULT 'percentage', based_on VARCHAR(30) NOT NULL DEFAULT 'gross', rate_percent DECIMAL(10,4) NOT NULL DEFAULT 0, fixed_amount DECIMAL(14,2) NOT NULL DEFAULT 0, minimum_amount DECIMAL(14,2) NOT NULL DEFAULT 0, maximum_amount DECIMAL(14,2) NULL, relief_amount DECIMAL(14,2) NOT NULL DEFAULT 0, employer_rate_percent DECIMAL(10,4) NOT NULL DEFAULT 0, employer_fixed_amount DECIMAL(14,2) NOT NULL DEFAULT 0, liability_account_code VARCHAR(30) NULL, employer_expense_account_code VARCHAR(30) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, notes TEXT NULL, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hr_statutory_rule_version (rule_code, effective_from), KEY idx_hr_statutory_rule_effective (rule_code, is_active, effective_from, effective_to) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_tax_bands ( id INT AUTO_INCREMENT PRIMARY KEY, tax_code VARCHAR(30) NOT NULL DEFAULT 'PAYE', effective_from DATE NOT NULL, effective_to DATE NULL, band_order INT NOT NULL, band_amount DECIMAL(14,2) NULL, rate_percent DECIMAL(10,4) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hr_tax_band_version_order (tax_code, effective_from, band_order), KEY idx_hr_tax_band_effective (tax_code, is_active, effective_from, effective_to, band_order) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS hr_payroll_remittances ( id INT AUTO_INCREMENT PRIMARY KEY, remittance_no VARCHAR(60) NOT NULL, batch_id INT NOT NULL, deduction_code VARCHAR(50) NOT NULL, agency_name VARCHAR(180) NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, due_date DATE NULL, payment_date DATE NULL, bank_reference VARCHAR(120) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Pending', journal_id INT NULL, notes TEXT NULL, prepared_by VARCHAR(150) NULL, paid_by VARCHAR(150) NULL, paid_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_hr_payroll_remittance_no (remittance_no), UNIQUE KEY uq_hr_payroll_remittance_batch_code (batch_id, deduction_code), UNIQUE KEY uq_hr_payroll_remittance_bank_reference (bank_reference), KEY idx_hr_payroll_remittance_status (status, due_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO chart_of_accounts (account_code,account_name,account_type,category,is_system) VALUES ('1010','Petty Cash','Asset','Cash and Cash Equivalents',1) ON DUPLICATE KEY UPDATE account_name=VALUES(account_name),account_type=VALUES(account_type),category=VALUES(category),is_system=VALUES(is_system); INSERT INTO hr_statutory_rules(rule_code,rule_name,effective_from,calculation_mode,based_on,rate_percent,fixed_amount,relief_amount,employer_rate_percent,liability_account_code,employer_expense_account_code,is_active,notes) VALUES ('SHIF','Social Health Insurance Fund','2025-01-01','percentage','gross',2.7500,0,0,0,'2150',NULL,1,'Configurable effective-dated employee contribution.'), ('HOUSING','Affordable Housing Levy','2025-01-01','percentage','gross',1.5000,0,0,1.5000,'2160','5020',1,'Employee and employer rates are independently configurable.'), ('PAYE','Pay As You Earn','2025-01-01','fixed','manual',0,0,2400,0,'2130',NULL,1,'PAYE bands remain in payroll computation; this rule stores relief and liability mapping.'), ('NSSF','National Social Security Fund','2025-01-01','manual','manual',0,0,0,0,'2140','5020',1,'Employee amount is captured per employee; employer amount may be configured.'), ('OTHER','Other Payroll Deductions','2025-01-01','manual','manual',0,0,0,0,'2170',NULL,1,'Combined other deductions control.'), ('INSURANCE','Insurance Deductions','2025-01-01','manual','manual',0,0,0,0,'2170',NULL,1,'Employee insurance deductions control.'), ('WELFARE','Staff Welfare Deductions','2025-01-01','manual','manual',0,0,0,0,'2170',NULL,1,'Staff welfare deductions control.') ON DUPLICATE KEY UPDATE rule_name=VALUES(rule_name),liability_account_code=VALUES(liability_account_code),employer_expense_account_code=VALUES(employer_expense_account_code); INSERT INTO hr_tax_bands(tax_code,effective_from,band_order,band_amount,rate_percent,is_active,created_by) VALUES ('PAYE','2025-01-01',1,24000,10.0000,1,'Migration'), ('PAYE','2025-01-01',2,8333,25.0000,1,'Migration'), ('PAYE','2025-01-01',3,467667,30.0000,1,'Migration'), ('PAYE','2025-01-01',4,300000,32.5000,1,'Migration'), ('PAYE','2025-01-01',5,NULL,35.0000,1,'Migration') ON DUPLICATE KEY UPDATE band_amount=VALUES(band_amount),rate_percent=VALUES(rate_percent),is_active=VALUES(is_active); INSERT INTO finance_approval_settings(transaction_type,threshold_amount,approver_role,is_active) VALUES ('expense_claim',1,'Finance,Admin',1), ('expense_claim_payment',1,'Finance,Admin',1), ('payroll_batch',1,'Human Resource,Finance,Admin',1), ('payroll_payment',1,'Finance,Admin',1), ('finance_adjustment',1,'Finance,Admin',1), ('petty_cash_voucher',1,'Finance,Admin',1) ON DUPLICATE KEY UPDATE approver_role=VALUES(approver_role),is_active=VALUES(is_active); INSERT IGNORE INTO schema_migrations(migration_name, checksum_sha256) VALUES ('20260724_011_payments_expenses_payroll_controls.sql', 'ea5599fc9f14266d30b86fa4400f1151426d557c9218b7cce51565289157c8e7'); -- 20260726_013_cashbook_finance_procurement_hardening.sql CREATE TABLE IF NOT EXISTS finance_cashbook_accounts ( id INT AUTO_INCREMENT PRIMARY KEY, chart_account_id INT NOT NULL, account_label VARCHAR(180) NOT NULL, account_type VARCHAR(30) NOT NULL DEFAULT 'Bank', institution_name VARCHAR(180) NULL, account_reference VARCHAR(120) NULL, opening_notes TEXT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_by VARCHAR(150) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_cashbook_chart_account (chart_account_id), KEY idx_finance_cashbook_active (is_active, account_type) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS finance_cashbook_vouchers ( id INT AUTO_INCREMENT PRIMARY KEY, voucher_no VARCHAR(60) NOT NULL, transaction_date DATE NOT NULL, transaction_type VARCHAR(20) NOT NULL, cashbook_account_id INT NOT NULL, contra_account_code VARCHAR(30) NULL, transfer_account_id INT NULL, payer_payee VARCHAR(180) NOT NULL, reference_no VARCHAR(120) NOT NULL, payment_method VARCHAR(40) NULL, description TEXT NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, attachment_path VARCHAR(255) NULL, status VARCHAR(30) NOT NULL DEFAULT 'Draft', transaction_hash CHAR(64) NULL, prepared_by VARCHAR(150) NULL, submitted_by VARCHAR(150) NULL, submitted_at DATETIME NULL, approved_by VARCHAR(150) NULL, approved_at DATETIME NULL, journal_id INT NULL, reversed_by VARCHAR(150) NULL, reversed_at DATETIME NULL, reversal_reason TEXT NULL, reversal_journal_id INT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_finance_cashbook_voucher_no (voucher_no), UNIQUE KEY uq_finance_cashbook_reference (reference_no), KEY idx_finance_cashbook_voucher_date (transaction_date, status), KEY idx_finance_cashbook_voucher_account (cashbook_account_id, transaction_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO chart_of_accounts(account_code,account_name,account_type,category,is_system) VALUES ('1010','Petty Cash','Asset','Cash and Cash Equivalents',1), ('1020','Mobile Money / Collection Wallet','Asset','Cash and Cash Equivalents',1) ON DUPLICATE KEY UPDATE account_name=VALUES(account_name),account_type=VALUES(account_type),category=VALUES(category),is_system=VALUES(is_system); INSERT INTO finance_cashbook_accounts(chart_account_id,account_label,account_type,institution_name,account_reference,is_active,created_by) SELECT id,account_name,CASE account_code WHEN '1010' THEN 'Cash' WHEN '1020' THEN 'Mobile Money' ELSE 'Bank' END,NULL,NULL,1,'Schema' FROM chart_of_accounts WHERE account_code IN ('1000','1010','1020') ON DUPLICATE KEY UPDATE account_label=VALUES(account_label); INSERT INTO finance_approval_settings(transaction_type,threshold_amount,approver_role,is_active) VALUES ('cashbook_voucher',1,'Finance,Admin',1) ON DUPLICATE KEY UPDATE approver_role=VALUES(approver_role),is_active=VALUES(is_active); -- Cashbook finance/procurement hardening role permissions (2026-07-26) UPDATE roles SET permissions=CASE WHEN permissions IS NULL OR TRIM(permissions)='' THEN 'finance_cashbook' ELSE CONCAT(TRIM(TRAILING ',' FROM permissions),',finance_cashbook') END WHERE role_name IN ('Finance','Admin','System Admin') AND FIND_IN_SET('finance_cashbook',REPLACE(COALESCE(permissions,''),' ',''))=0; -- 20260726_016_payment_voucher_workflow_pdf.sql -- Payment voucher workflow separation and printable controlled document support. -- Apply with: php bin/migrate.php ALTER TABLE procurement_payment_vouchers ADD COLUMN prepared_user_id INT NULL AFTER prepared_by, ADD COLUMN checked_user_id INT NULL AFTER checked_by, ADD COLUMN checked_at DATETIME NULL AFTER checked_user_id, ADD COLUMN check_comments TEXT NULL AFTER checked_at, ADD COLUMN approved_user_id INT NULL AFTER approved_by, ADD COLUMN approval_comments TEXT NULL AFTER approved_at, ADD COLUMN payment_date DATE NULL AFTER bank_reference, ADD COLUMN paid_user_id INT NULL AFTER paid_by, ADD COLUMN rejected_by VARCHAR(150) NULL AFTER paid_at, ADD COLUMN rejected_user_id INT NULL AFTER rejected_by, ADD COLUMN rejected_at DATETIME NULL AFTER rejected_by, ADD COLUMN rejection_reason TEXT NULL AFTER rejected_at, ADD COLUMN print_count INT NOT NULL DEFAULT 0 AFTER rejection_reason, ADD COLUMN last_printed_by VARCHAR(150) NULL AFTER print_count, ADD COLUMN last_printed_user_id INT NULL AFTER last_printed_by, ADD COLUMN last_printed_at DATETIME NULL AFTER last_printed_by; UPDATE procurement_payment_vouchers SET checked_at=COALESCE(checked_at,approved_at), payment_date=COALESCE(payment_date,DATE(paid_at)), check_comments=CASE WHEN checked_by IS NOT NULL AND approved_by IS NOT NULL AND LOWER(TRIM(checked_by))=LOWER(TRIM(approved_by)) THEN 'Legacy voucher: checking and approval were recorded in one action before workflow separation.' ELSE check_comments END WHERE status IN ('Approved','Paid'); INSERT INTO finance_approval_settings(transaction_type,threshold_amount,approver_role,is_active) VALUES ('procurement_payment_voucher_check',1,'Finance,Admin',1), ('procurement_payment_voucher_approval',1,'Finance,Admin',1), ('procurement_payment_voucher_release',1,'Finance,Admin',1) ON DUPLICATE KEY UPDATE approver_role=VALUES(approver_role),is_active=VALUES(is_active); -- Industrial attachment unit roster and placement period tables are created by migration 20260728_019. CREATE TABLE IF NOT EXISTS ilo_attachment_periods ( id BIGINT AUTO_INCREMENT PRIMARY KEY, period_code VARCHAR(60) NOT NULL, period_name VARCHAR(180) NOT NULL, academic_year VARCHAR(40) NOT NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, registration_deadline DATE NULL, status ENUM('Draft','Open','Closed','Archived') NOT NULL DEFAULT 'Draft', notes TEXT NULL, created_by_user_id INT NULL, created_by_name VARCHAR(150) NULL, approved_by_user_id INT NULL, approved_by_name VARCHAR(150) NULL, approved_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ilo_attachment_period_code (period_code), KEY idx_ilo_attachment_period_match (academic_year,session_name,semester_name,status,start_date,end_date) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ilo_attachment_roster ( id BIGINT AUTO_INCREMENT PRIMARY KEY, period_id BIGINT NULL, student_unit_registration_id INT NULL, student_id INT NULL, student_admission_no VARCHAR(50) NOT NULL, student_name VARCHAR(180) NOT NULL, department VARCHAR(120) NULL, course VARCHAR(180) NULL, class_name VARCHAR(120) NULL, unit_id INT NULL, unit_code VARCHAR(60) NOT NULL, unit_name VARCHAR(180) NULL, academic_year VARCHAR(40) NULL, session_name VARCHAR(100) NULL, semester_name VARCHAR(100) NULL, year_level VARCHAR(30) NULL, placement_id BIGINT NULL, placement_status ENUM('Not Placed','Placed','Attached','Deferred','Completed','Cleared') NOT NULL DEFAULT 'Not Placed', deferral_reason TEXT NULL, deferred_to_period_id BIGINT NULL, source ENUM('Unit Registration','Bulk Upload','Manual') NOT NULL DEFAULT 'Unit Registration', import_batch_id BIGINT NULL, mapped_by_user_id INT NULL, mapped_by_name VARCHAR(150) NULL, mapped_at DATETIME NULL, placed_at DATETIME NULL, attached_at DATETIME NULL, deferred_at DATETIME NULL, notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_ilo_roster_registration (student_unit_registration_id), UNIQUE KEY uq_ilo_roster_period_student_unit (period_id,student_admission_no,unit_code), KEY idx_ilo_roster_status (period_id,placement_status,class_name,course), KEY idx_ilo_roster_student (student_admission_no,academic_year,semester_name), KEY idx_ilo_roster_placement (placement_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ilo_roster_import_batches ( id BIGINT AUTO_INCREMENT PRIMARY KEY, batch_no VARCHAR(60) NOT NULL, period_id BIGINT NOT NULL, unit_id INT NULL, unit_code VARCHAR(60) NULL, original_filename VARCHAR(255) NOT NULL, register_missing_unit TINYINT(1) NOT NULL DEFAULT 0, total_rows INT NOT NULL DEFAULT 0, imported_rows INT NOT NULL DEFAULT 0, skipped_rows INT NOT NULL DEFAULT 0, failed_rows INT NOT NULL DEFAULT 0, status ENUM('Processing','Completed','Completed with Errors','Failed') NOT NULL DEFAULT 'Processing', summary TEXT NULL, uploaded_by_user_id INT NULL, uploaded_by_name VARCHAR(150) NULL, uploaded_at DATETIME NOT NULL, completed_at DATETIME NULL, UNIQUE KEY uq_ilo_roster_batch_no (batch_no), KEY idx_ilo_roster_batch_period (period_id,uploaded_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE IF NOT EXISTS ilo_roster_import_rows ( id BIGINT AUTO_INCREMENT PRIMARY KEY, batch_id BIGINT NOT NULL, row_no INT NOT NULL, admission_no VARCHAR(50) NULL, student_name VARCHAR(180) NULL, unit_code VARCHAR(60) NULL, requested_status VARCHAR(40) NULL, result_status ENUM('Imported','Skipped','Failed') NOT NULL, roster_id BIGINT NULL, message TEXT NULL, raw_row LONGTEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, KEY idx_ilo_roster_import_row (batch_id,result_status,row_no) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Version: 20260730_020 cPanel security hardening -- Version: 20260730_020 -- Disable only the legacy predictable administrator account when its password -- still matches the historical bundled hash. Custom administrator accounts and -- changed passwords are not affected. UPDATE users SET is_active=0, must_change_password=1, last_password_reset_at=NOW() WHERE username='admin' AND email='admin@skulitech.local' AND password='$2y$12$xOMHKJfh/KNCO93AK7blue/IO1PbqrPAycHtKETVF7O3rRrpegVLe'; INSERT IGNORE INTO schema_migrations(migration_name, checksum_sha256) VALUES ('20260730_020_cpanel_security_hardening.sql', '02932d5cb0785b94b2f79813fe4002357a91249c5d508bd6402692684e8a735b');