-- TELSPAY EDUCATION & SCHOOL SERVICES MODULE v2026.9.0
-- Additive migration for NFA Staff SACCO / Telspay.
-- PHP 7.4 compatible application layer. Existing SACCO tables are not dropped or renamed.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS edu_schools (
  school_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_ref VARCHAR(40) NOT NULL,
  school_name VARCHAR(180) NOT NULL,
  trading_name VARCHAR(180) DEFAULT NULL,
  school_type VARCHAR(60) DEFAULT NULL,
  ownership_type VARCHAR(60) DEFAULT NULL,
  ministry_registration_no VARCHAR(100) DEFAULT NULL,
  tin VARCHAR(60) DEFAULT NULL,
  email VARCHAR(160) DEFAULT NULL,
  phone VARCHAR(60) DEFAULT NULL,
  alternate_phone VARCHAR(60) DEFAULT NULL,
  website VARCHAR(255) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  district VARCHAR(100) DEFAULT NULL,
  country VARCHAR(80) NOT NULL DEFAULT 'Uganda',
  head_teacher_name VARCHAR(180) DEFAULT NULL,
  logo_path VARCHAR(500) DEFAULT NULL,
  profile_summary TEXT DEFAULT NULL,
  admission_status ENUM('OPEN','CLOSED','PAUSED') NOT NULL DEFAULT 'OPEN',
  finance_enabled TINYINT(1) NOT NULL DEFAULT 1,
  online_admission_enabled TINYINT(1) NOT NULL DEFAULT 1,
  kyb_status ENUM('DRAFT','KYC_PENDING','UNDER_REVIEW','APPROVED','SUSPENDED','TERMINATED') NOT NULL DEFAULT 'DRAFT',
  school_status ENUM('ACTIVE','INACTIVE','SUSPENDED','TERMINATED') NOT NULL DEFAULT 'INACTIVE',
  approved_by INT DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  created_by INT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (school_id),
  UNIQUE KEY uq_edu_school_ref (school_ref),
  KEY idx_edu_school_name (school_name),
  KEY idx_edu_school_status (school_status,kyb_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_bank_accounts (
  bank_account_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  bank_name VARCHAR(150) NOT NULL,
  branch_name VARCHAR(150) DEFAULT NULL,
  account_name VARCHAR(180) NOT NULL,
  account_number_masked VARCHAR(80) NOT NULL,
  account_number_ciphertext TEXT DEFAULT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  verification_status ENUM('PENDING','VERIFIED','REJECTED','DISABLED') NOT NULL DEFAULT 'PENDING',
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  created_by_admin INT DEFAULT NULL,
  verified_by INT DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (bank_account_id),
  KEY idx_edu_school_bank_school (school_id,verification_status),
  CONSTRAINT fk_edu_school_bank_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_permissions (
  permission_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  permission_code VARCHAR(90) NOT NULL,
  permission_name VARCHAR(160) NOT NULL,
  module_code VARCHAR(60) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (permission_id),
  UNIQUE KEY uq_edu_school_permission_code (permission_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_roles (
  role_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_code VARCHAR(60) NOT NULL,
  role_name VARCHAR(150) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id),
  UNIQUE KEY uq_edu_school_role_code (role_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_role_permissions (
  role_id INT UNSIGNED NOT NULL,
  permission_id INT UNSIGNED NOT NULL,
  granted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id,permission_id),
  CONSTRAINT fk_edu_srp_role FOREIGN KEY (role_id) REFERENCES edu_school_roles(role_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_srp_permission FOREIGN KEY (permission_id) REFERENCES edu_school_permissions(permission_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_users (
  school_user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  full_name VARCHAR(180) NOT NULL,
  email VARCHAR(160) NOT NULL,
  phone VARCHAR(60) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('INVITED','ACTIVE','LOCKED','SUSPENDED','DISABLED') NOT NULL DEFAULT 'INVITED',
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  failed_attempts INT UNSIGNED NOT NULL DEFAULT 0,
  locked_until DATETIME DEFAULT NULL,
  last_login_at DATETIME DEFAULT NULL,
  last_login_ip_hash CHAR(64) DEFAULT NULL,
  created_by_admin INT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (school_user_id),
  UNIQUE KEY uq_edu_school_user_email (email),
  KEY idx_edu_school_user_status (school_id,status),
  CONSTRAINT fk_edu_school_user_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_user_roles (
  school_user_id BIGINT UNSIGNED NOT NULL,
  role_id INT UNSIGNED NOT NULL,
  assigned_by_admin INT DEFAULT NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (school_user_id,role_id),
  CONSTRAINT fk_edu_sur_user FOREIGN KEY (school_user_id) REFERENCES edu_school_users(school_user_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_sur_role FOREIGN KEY (role_id) REFERENCES edu_school_roles(role_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_academic_years (
  academic_year_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  year_name VARCHAR(60) NOT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  admission_open_at DATETIME DEFAULT NULL,
  admission_close_at DATETIME DEFAULT NULL,
  status ENUM('DRAFT','OPEN','ACTIVE','CLOSED') NOT NULL DEFAULT 'DRAFT',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (academic_year_id),
  UNIQUE KEY uq_edu_school_year (school_id,year_name),
  CONSTRAINT fk_edu_year_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_terms (
  term_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  academic_year_id BIGINT UNSIGNED NOT NULL,
  term_name VARCHAR(60) NOT NULL,
  start_date DATE DEFAULT NULL,
  end_date DATE DEFAULT NULL,
  status ENUM('UPCOMING','ACTIVE','CLOSED') NOT NULL DEFAULT 'UPCOMING',
  PRIMARY KEY (term_id),
  UNIQUE KEY uq_edu_year_term (academic_year_id,term_name),
  CONSTRAINT fk_edu_term_year FOREIGN KEY (academic_year_id) REFERENCES edu_academic_years(academic_year_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_classes (
  class_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  class_name VARCHAR(100) NOT NULL,
  class_code VARCHAR(40) DEFAULT NULL,
  level_name VARCHAR(80) DEFAULT NULL,
  capacity INT UNSIGNED DEFAULT NULL,
  available_places INT UNSIGNED DEFAULT NULL,
  status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (class_id),
  UNIQUE KEY uq_edu_school_class (school_id,class_name),
  CONSTRAINT fk_edu_class_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_requirements (
  requirement_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  class_id BIGINT UNSIGNED DEFAULT NULL,
  requirement_name VARCHAR(180) NOT NULL,
  requirement_type ENUM('DOCUMENT','ITEM','MEDICAL','ACADEMIC','OTHER') NOT NULL DEFAULT 'OTHER',
  requirement_level ENUM('REQUIRED','OPTIONAL','INFORMATIONAL') NOT NULL DEFAULT 'OPTIONAL',
  description TEXT DEFAULT NULL,
  status ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (requirement_id),
  KEY idx_edu_req_school (school_id,class_id,status),
  CONSTRAINT fk_edu_req_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_req_class FOREIGN KEY (class_id) REFERENCES edu_classes(class_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_admission_forms (
  admission_form_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  academic_year_id BIGINT UNSIGNED DEFAULT NULL,
  form_name VARCHAR(180) NOT NULL,
  instructions TEXT DEFAULT NULL,
  application_fee DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  interview_fee DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  registration_fee DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  medical_fee DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  status ENUM('DRAFT','PUBLISHED','CLOSED') NOT NULL DEFAULT 'DRAFT',
  created_by_school_user BIGINT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (admission_form_id),
  KEY idx_edu_adm_form_school (school_id,status),
  CONSTRAINT fk_edu_adm_form_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_adm_form_year FOREIGN KEY (academic_year_id) REFERENCES edu_academic_years(academic_year_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_admission_form_fields (
  field_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admission_form_id BIGINT UNSIGNED NOT NULL,
  field_key VARCHAR(80) NOT NULL,
  field_label VARCHAR(180) NOT NULL,
  field_type ENUM('TEXT','TEXTAREA','DATE','NUMBER','EMAIL','PHONE','SELECT','RADIO','CHECKBOX','FILE') NOT NULL DEFAULT 'TEXT',
  options_json LONGTEXT DEFAULT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  validation_json LONGTEXT DEFAULT NULL,
  PRIMARY KEY (field_id),
  UNIQUE KEY uq_edu_adm_field_key (admission_form_id,field_key),
  CONSTRAINT fk_edu_adm_field_form FOREIGN KEY (admission_form_id) REFERENCES edu_admission_forms(admission_form_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_students (
  student_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_ref VARCHAR(40) NOT NULL,
  owner_member_id INT DEFAULT NULL,
  first_name VARCHAR(100) NOT NULL,
  middle_name VARCHAR(100) DEFAULT NULL,
  last_name VARCHAR(100) NOT NULL,
  date_of_birth DATE DEFAULT NULL,
  gender VARCHAR(30) DEFAULT NULL,
  nationality VARCHAR(80) DEFAULT NULL,
  birth_certificate_no VARCHAR(100) DEFAULT NULL,
  nin VARCHAR(30) DEFAULT NULL,
  passport_no VARCHAR(50) DEFAULT NULL,
  passport_photo_path VARCHAR(500) DEFAULT NULL,
  medical_notes TEXT DEFAULT NULL,
  kyc_status ENUM('INCOMPLETE','PENDING','VERIFIED','REJECTED') NOT NULL DEFAULT 'INCOMPLETE',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id),
  UNIQUE KEY uq_edu_student_ref (student_ref),
  KEY idx_edu_student_member (owner_member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_guardians (
  guardian_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT UNSIGNED NOT NULL,
  member_id INT DEFAULT NULL,
  relationship VARCHAR(60) NOT NULL,
  full_name VARCHAR(180) NOT NULL,
  phone VARCHAR(60) DEFAULT NULL,
  email VARCHAR(160) DEFAULT NULL,
  nin VARCHAR(30) DEFAULT NULL,
  is_primary TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (guardian_id),
  KEY idx_edu_guardian_student (student_id,is_primary),
  CONSTRAINT fk_edu_guardian_student FOREIGN KEY (student_id) REFERENCES edu_students(student_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_admission_applications (
  application_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  application_ref VARCHAR(50) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  admission_form_id BIGINT UNSIGNED NOT NULL,
  academic_year_id BIGINT UNSIGNED DEFAULT NULL,
  class_id BIGINT UNSIGNED DEFAULT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  member_id INT DEFAULT NULL,
  application_status ENUM('DRAFT','SUBMITTED','APPLICATION_FEE_PENDING','UNDER_REVIEW','DOCUMENTS_REQUIRED','INTERVIEW_REQUIRED','INTERVIEW_COMPLETED','WAITLISTED','ACCEPTED','REJECTED','WITHDRAWN','ENROLLED') NOT NULL DEFAULT 'DRAFT',
  application_fee_status ENUM('NOT_REQUIRED','PENDING','PAID','WAIVED','FAILED') NOT NULL DEFAULT 'PENDING',
  school_student_no VARCHAR(100) DEFAULT NULL,
  admission_no VARCHAR(100) DEFAULT NULL,
  review_note TEXT DEFAULT NULL,
  submitted_at DATETIME DEFAULT NULL,
  reviewed_by BIGINT UNSIGNED DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  accepted_at DATETIME DEFAULT NULL,
  enrolled_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (application_id),
  UNIQUE KEY uq_edu_application_ref (application_ref),
  KEY idx_edu_application_school (school_id,application_status,created_at),
  KEY idx_edu_application_member (member_id,created_at),
  CONSTRAINT fk_edu_application_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_application_form FOREIGN KEY (admission_form_id) REFERENCES edu_admission_forms(admission_form_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_application_year FOREIGN KEY (academic_year_id) REFERENCES edu_academic_years(academic_year_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_application_class FOREIGN KEY (class_id) REFERENCES edu_classes(class_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_application_student FOREIGN KEY (student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_admission_answers (
  answer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  application_id BIGINT UNSIGNED NOT NULL,
  field_id BIGINT UNSIGNED NOT NULL,
  answer_text LONGTEXT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (answer_id),
  UNIQUE KEY uq_edu_answer_field (application_id,field_id),
  CONSTRAINT fk_edu_answer_application FOREIGN KEY (application_id) REFERENCES edu_admission_applications(application_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_answer_field FOREIGN KEY (field_id) REFERENCES edu_admission_form_fields(field_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_admission_documents (
  document_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  application_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(80) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  file_sha256 CHAR(64) NOT NULL,
  verification_status ENUM('PENDING','VERIFIED','REJECTED') NOT NULL DEFAULT 'PENDING',
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  verified_by BIGINT UNSIGNED DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  PRIMARY KEY (document_id),
  KEY idx_edu_adm_doc_app (application_id,verification_status),
  CONSTRAINT fk_edu_adm_doc_app FOREIGN KEY (application_id) REFERENCES edu_admission_applications(application_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_fee_structures (
  fee_structure_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  academic_year_id BIGINT UNSIGNED NOT NULL,
  term_id BIGINT UNSIGNED DEFAULT NULL,
  class_id BIGINT UNSIGNED DEFAULT NULL,
  structure_name VARCHAR(180) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  status ENUM('DRAFT','PUBLISHED','ARCHIVED') NOT NULL DEFAULT 'DRAFT',
  created_by BIGINT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (fee_structure_id),
  KEY idx_edu_fee_structure (school_id,academic_year_id,term_id,class_id,status),
  CONSTRAINT fk_edu_fee_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_fee_year FOREIGN KEY (academic_year_id) REFERENCES edu_academic_years(academic_year_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_fee_term FOREIGN KEY (term_id) REFERENCES edu_terms(term_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_fee_class FOREIGN KEY (class_id) REFERENCES edu_classes(class_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_fee_items (
  fee_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  fee_structure_id BIGINT UNSIGNED NOT NULL,
  fee_name VARCHAR(180) NOT NULL,
  amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  fee_requirement ENUM('MANDATORY','OPTIONAL') NOT NULL DEFAULT 'MANDATORY',
  billing_frequency ENUM('ONE_TIME','PER_TERM','ANNUAL') NOT NULL DEFAULT 'PER_TERM',
  refundable TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  PRIMARY KEY (fee_item_id),
  CONSTRAINT fk_edu_fee_item_structure FOREIGN KEY (fee_structure_id) REFERENCES edu_fee_structures(fee_structure_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_accounts (
  student_account_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  academic_year_id BIGINT UNSIGNED DEFAULT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  opening_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  current_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  status ENUM('ACTIVE','CLOSED') NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (student_account_id),
  UNIQUE KEY uq_edu_student_account (student_id,school_id,academic_year_id),
  CONSTRAINT fk_edu_student_account_student FOREIGN KEY (student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_account_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_ledger (
  ledger_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  student_account_id BIGINT UNSIGNED NOT NULL,
  transaction_ref VARCHAR(80) NOT NULL,
  transaction_type ENUM('CHARGE','PAYMENT','LOAN_PAYMENT','REVERSAL','WAIVER','ADJUSTMENT') NOT NULL,
  debit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  credit DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  running_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  description VARCHAR(500) NOT NULL,
  source_type VARCHAR(60) DEFAULT NULL,
  source_id BIGINT DEFAULT NULL,
  posted_by_type ENUM('SACCO_ADMIN','SCHOOL_USER','MEMBER','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  posted_by_id BIGINT DEFAULT NULL,
  posted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (ledger_id),
  UNIQUE KEY uq_edu_student_ledger_ref (transaction_ref),
  KEY idx_edu_student_ledger_account (student_account_id,posted_at),
  CONSTRAINT fk_edu_student_ledger_account FOREIGN KEY (student_account_id) REFERENCES edu_student_accounts(student_account_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_fee_loans (
  edu_loan_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  education_loan_ref VARCHAR(60) NOT NULL,
  loan_id INT DEFAULT NULL,
  member_id INT NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  application_id BIGINT UNSIGNED DEFAULT NULL,
  academic_year_id BIGINT UNSIGNED DEFAULT NULL,
  term_id BIGINT UNSIGNED DEFAULT NULL,
  fee_structure_id BIGINT UNSIGNED DEFAULT NULL,
  requested_amount DECIMAL(15,2) NOT NULL,
  member_contribution DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  approved_amount DECIMAL(15,2) DEFAULT NULL,
  approval_maker_admin_id INT DEFAULT NULL,
  approval_checker_admin_id INT DEFAULT NULL,
  approval_requested_at DATETIME DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  disbursement_mode ENUM('SCHOOL_ONLY') NOT NULL DEFAULT 'SCHOOL_ONLY',
  loan_status ENUM('DRAFT','SUBMITTED','UNDER_REVIEW','APPROVED','REJECTED','DISBURSEMENT_PENDING','DISBURSED','SCHOOL_CONFIRMED','RECONCILED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
  school_bank_account_id BIGINT UNSIGNED DEFAULT NULL,
  disbursement_reference VARCHAR(120) DEFAULT NULL,
  disbursed_at DATETIME DEFAULT NULL,
  school_confirmed_at DATETIME DEFAULT NULL,
  reconciled_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (edu_loan_id),
  UNIQUE KEY uq_edu_loan_ref (education_loan_ref),
  UNIQUE KEY uq_edu_linked_loan (loan_id),
  KEY idx_edu_loan_member (member_id,loan_status),
  KEY idx_edu_loan_school (school_id,loan_status),
  CONSTRAINT fk_edu_loan_student FOREIGN KEY (student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_loan_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_loan_application FOREIGN KEY (application_id) REFERENCES edu_admission_applications(application_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_loan_school_bank FOREIGN KEY (school_bank_account_id) REFERENCES edu_school_bank_accounts(bank_account_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_fee_payments (
  payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_ref VARCHAR(80) NOT NULL,
  member_id INT DEFAULT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  edu_loan_id BIGINT UNSIGNED DEFAULT NULL,
  student_account_id BIGINT UNSIGNED DEFAULT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  payment_method ENUM('SACCO_SAVINGS','MOBILE_MONEY','BANK','SCHOOL_FEES_LOAN','MANUAL_RECONCILIATION') NOT NULL,
  provider_reference VARCHAR(180) DEFAULT NULL,
  school_reference VARCHAR(180) DEFAULT NULL,
  payment_status ENUM('CREATED','PENDING','SUCCESSFUL','FAILED','REVERSED','SCHOOL_CONFIRMED','RECONCILED') NOT NULL DEFAULT 'CREATED',
  initiated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME DEFAULT NULL,
  school_confirmed_at DATETIME DEFAULT NULL,
  reconciled_at DATETIME DEFAULT NULL,
  created_by_type ENUM('MEMBER','SACCO_ADMIN','SCHOOL_USER','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  created_by_id BIGINT DEFAULT NULL,
  PRIMARY KEY (payment_id),
  UNIQUE KEY uq_edu_payment_ref (payment_ref),
  KEY idx_edu_payment_school (school_id,payment_status,initiated_at),
  KEY idx_edu_payment_student (student_id,initiated_at),
  CONSTRAINT fk_edu_payment_student FOREIGN KEY (student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_payment_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_payment_loan FOREIGN KEY (edu_loan_id) REFERENCES edu_school_fee_loans(edu_loan_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_payment_account FOREIGN KEY (student_account_id) REFERENCES edu_student_accounts(student_account_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_settlements (
  settlement_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  settlement_ref VARCHAR(80) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  bank_account_id BIGINT UNSIGNED NOT NULL,
  period_start DATETIME NOT NULL,
  period_end DATETIME NOT NULL,
  expected_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  sent_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  confirmed_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  unmatched_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  status ENUM('DRAFT','PENDING_APPROVAL','SENT','PARTIALLY_CONFIRMED','CONFIRMED','RECONCILED','EXCEPTION') NOT NULL DEFAULT 'DRAFT',
  maker_admin_id INT DEFAULT NULL,
  checker_admin_id INT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (settlement_id),
  UNIQUE KEY uq_edu_settlement_ref (settlement_ref),
  KEY idx_edu_settlement_school (school_id,status,period_start),
  CONSTRAINT fk_edu_settlement_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_settlement_bank FOREIGN KEY (bank_account_id) REFERENCES edu_school_bank_accounts(bank_account_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_document_verifications (
  verification_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_ref CHAR(32) NOT NULL,
  document_type ENUM('ADMISSION_APPLICATION','ADMISSION_LETTER','REGISTRATION_RECEIPT','SCHOOL_FEES_RECEIPT','SCHOOL_FEES_LOAN_APPLICATION','SCHOOL_FEES_LOAN_AGREEMENT','SCHOOL_PAYMENT_CONFIRMATION','STUDENT_STATEMENT','SETTLEMENT_REPORT') NOT NULL,
  entity_type VARCHAR(60) NOT NULL,
  entity_id BIGINT NOT NULL,
  school_id BIGINT UNSIGNED DEFAULT NULL,
  member_id INT DEFAULT NULL,
  student_id BIGINT UNSIGNED DEFAULT NULL,
  canonical_hash CHAR(64) NOT NULL,
  snapshot_json LONGTEXT NOT NULL,
  stored_name VARCHAR(255) DEFAULT NULL,
  file_sha256 CHAR(64) DEFAULT NULL,
  verification_status ENUM('VALID','REVOKED','EXPIRED') NOT NULL DEFAULT 'VALID',
  issued_by_type ENUM('SACCO_ADMIN','SCHOOL_USER','MEMBER','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  issued_by_id BIGINT DEFAULT NULL,
  issued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME DEFAULT NULL,
  revoked_at DATETIME DEFAULT NULL,
  revocation_reason VARCHAR(500) DEFAULT NULL,
  last_verified_at DATETIME DEFAULT NULL,
  verification_count INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (verification_id),
  UNIQUE KEY uq_edu_doc_public_ref (public_ref),
  KEY idx_edu_doc_entity (entity_type,entity_id),
  KEY idx_edu_doc_student (student_id,issued_at),
  KEY idx_edu_doc_school (school_id,issued_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_audit_events (
  event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_uuid CHAR(36) NOT NULL,
  actor_type ENUM('SACCO_ADMIN','SCHOOL_USER','MEMBER','PUBLIC','SYSTEM') NOT NULL,
  actor_id VARCHAR(80) DEFAULT NULL,
  school_id BIGINT UNSIGNED DEFAULT NULL,
  action_code VARCHAR(120) NOT NULL,
  target_type VARCHAR(80) DEFAULT NULL,
  target_id VARCHAR(100) DEFAULT NULL,
  result_code VARCHAR(40) NOT NULL DEFAULT 'SUCCESS',
  severity ENUM('INFO','LOW','MEDIUM','HIGH','CRITICAL') NOT NULL DEFAULT 'INFO',
  ip_hash CHAR(64) DEFAULT NULL,
  user_agent_hash CHAR(64) DEFAULT NULL,
  payload_json LONGTEXT DEFAULT NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (event_id),
  UNIQUE KEY uq_edu_audit_uuid (event_uuid),
  KEY idx_edu_audit_school (school_id,occurred_at),
  KEY idx_edu_audit_action (action_code,occurred_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default school permissions
INSERT IGNORE INTO edu_school_permissions(permission_code,permission_name,module_code,description) VALUES
('school.dashboard.view','View school dashboard','dashboard','View school operational summary.'),
('school.admissions.view','View admissions','admissions','View applications and admission evidence.'),
('school.admissions.manage','Manage admissions','admissions','Request documents, schedule reviews and update workflow.'),
('school.admissions.decide','Accept or reject admissions','admissions','Make final admission decisions.'),
('school.students.view','View students','students','View enrolled student profiles.'),
('school.students.manage','Manage students','students','Update school-side student records.'),
('school.fees.view','View fee structures','fees','View school fee structures and student ledgers.'),
('school.fees.manage','Manage fee structures','fees','Create and publish fee structures and items.'),
('school.payments.view','View school payments','payments','View payments received for students.'),
('school.payments.confirm','Confirm school receipt','payments','Confirm receipt of SACCO-originated school payments.'),
('school.loans.view','View school fees loans','loans','View loans linked to the school and its students.'),
('school.documents.view','View documents','documents','View education document metadata.'),
('school.documents.issue','Issue school documents','documents','Issue QR-verifiable admission and payment documents.'),
('school.staff.view','View school staff','staff','View school portal users and role assignments.'),
('school.settings.view','View school settings','settings','View school configuration.'),
('school.settings.manage','Manage school settings','settings','Manage admissions settings excluding verified settlement accounts.'),
('school.reports.view','View reports','reports','View education operational and finance reports.'),
('school.reconciliation.view','View reconciliation','reconciliation','View school settlement reconciliation.'),
('school.reconciliation.manage','Manage reconciliation','reconciliation','Confirm exceptions and reconciliation notes.');

INSERT IGNORE INTO edu_school_roles(role_code,role_name,description,is_system,is_active) VALUES
('SCHOOL_SUPER_ADMIN','School Super Administrator','Full school portal access except SACCO-only controls.',1,1),
('SCHOOL_REGISTRAR','School Registrar','Student records, admissions and documents.',1,1),
('SCHOOL_ADMISSIONS_OFFICER','Admissions Officer','Admission applications and supporting documents.',1,1),
('SCHOOL_BURSAR','School Bursar','Fee structures, student ledgers and payment confirmations.',1,1),
('SCHOOL_ACCOUNTANT','School Accountant','Payments, settlements and reconciliation.',1,1),
('SCHOOL_HEAD_TEACHER','Head Teacher','Admissions decisions and read access across core school operations.',1,1),
('SCHOOL_FINANCE_MANAGER','Finance Manager','School finance, payments, loans and reconciliation.',1,1),
('SCHOOL_AUDITOR_READ_ONLY','School Auditor Read Only','Read-only school operations and finance evidence.',1,1);

-- Full permissions for School Super Admin
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r CROSS JOIN edu_school_permissions p WHERE r.role_code='SCHOOL_SUPER_ADMIN';

-- Registrar
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN
('school.dashboard.view','school.admissions.view','school.admissions.manage','school.students.view','school.students.manage','school.documents.view','school.documents.issue','school.reports.view')
WHERE r.role_code='SCHOOL_REGISTRAR';
-- Admissions Officer
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN
('school.dashboard.view','school.admissions.view','school.admissions.manage','school.students.view','school.documents.view')
WHERE r.role_code='SCHOOL_ADMISSIONS_OFFICER';
-- Bursar
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN
('school.dashboard.view','school.students.view','school.fees.view','school.fees.manage','school.payments.view','school.payments.confirm','school.loans.view','school.documents.view','school.documents.issue','school.reports.view','school.reconciliation.view')
WHERE r.role_code='SCHOOL_BURSAR';
-- Accountant
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN
('school.dashboard.view','school.students.view','school.fees.view','school.payments.view','school.payments.confirm','school.loans.view','school.documents.view','school.reports.view','school.reconciliation.view','school.reconciliation.manage')
WHERE r.role_code='SCHOOL_ACCOUNTANT';
-- Head Teacher
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN
('school.dashboard.view','school.admissions.view','school.admissions.manage','school.admissions.decide','school.students.view','school.fees.view','school.payments.view','school.loans.view','school.documents.view','school.documents.issue','school.staff.view','school.settings.view','school.reports.view')
WHERE r.role_code='SCHOOL_HEAD_TEACHER';
-- Finance Manager
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN
('school.dashboard.view','school.students.view','school.fees.view','school.fees.manage','school.payments.view','school.payments.confirm','school.loans.view','school.documents.view','school.documents.issue','school.reports.view','school.reconciliation.view','school.reconciliation.manage')
WHERE r.role_code='SCHOOL_FINANCE_MANAGER';
-- Auditor
INSERT IGNORE INTO edu_school_role_permissions(role_id,permission_id)
SELECT r.role_id,p.permission_id FROM edu_school_roles r JOIN edu_school_permissions p ON p.permission_code IN
('school.dashboard.view','school.admissions.view','school.students.view','school.fees.view','school.payments.view','school.loans.view','school.documents.view','school.reports.view','school.reconciliation.view')
WHERE r.role_code='SCHOOL_AUDITOR_READ_ONLY';

-- Extend the existing SACCO Admin Control Center only when those tables are present.
INSERT IGNORE INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.view','View Education Control Center','education','HIGH','View schools, students, admissions, payments and education loans.',UTC_TIMESTAMP()),
('education.schools.manage','Manage partner schools','education','CRITICAL','Onboard, approve, suspend and manage partner schools.',UTC_TIMESTAMP()),
('education.school_staff.manage','Manage school portal staff','education','CRITICAL','Create school users and assign school roles from SACCO Admin.',UTC_TIMESTAMP()),
('education.admissions.view','View school admissions','education','HIGH','View admission applications and evidence.',UTC_TIMESTAMP()),
('education.admissions.manage','Manage school admissions oversight','education','HIGH','Manage SACCO-side admission exceptions and school escalation.',UTC_TIMESTAMP()),
('education.fees.view','View school fee structures','education','HIGH','View fee structures and student school accounts.',UTC_TIMESTAMP()),
('education.fees.manage','Manage school fee controls','education','CRITICAL','Approve or restrict fee structures used for financing.',UTC_TIMESTAMP()),
('education.loans.view','View school fees loans','education','HIGH','View education loans and beneficiary schools.',UTC_TIMESTAMP()),
('education.loans.manage','Manage school fees loans','education','CRITICAL','Review education loan linkage and beneficiary controls.',UTC_TIMESTAMP()),
('education.loans.disburse','Disburse school fees loans','education','CRITICAL','Direct school-only disbursement; member cash disbursement is prohibited.',UTC_TIMESTAMP()),
('education.payments.view','View education payments','education','HIGH','View school-fee payments and settlement status.',UTC_TIMESTAMP()),
('education.payments.manage','Manage education payments','education','CRITICAL','Manage reconciliation and payment exceptions.',UTC_TIMESTAMP()),
('education.documents.view','View education documents','education','HIGH','View QR-verifiable education document metadata.',UTC_TIMESTAMP()),
('education.documents.manage','Manage education documents','education','HIGH','Issue, revoke or reissue QR-verifiable education documents.',UTC_TIMESTAMP()),
('education.audit.view','View education audit','education','HIGH','View school and education audit events.',UTC_TIMESTAMP()),
('education.settings.manage','Manage education configuration','education','CRITICAL','Manage education platform settings and school policy.',UTC_TIMESTAMP());

INSERT IGNORE INTO admin_cc_roles(role_code,role_name,description,is_system,is_active,created_at,updated_at) VALUES
('EDUCATION_ADMIN','Education Administrator','Full education module administration under SACCO controls.',1,1,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('EDUCATION_CREDIT_OFFICER','Education Credit Officer','School fees loan review, beneficiary validation and education credit workflow.',1,1,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('EDUCATION_PARTNER_MANAGER','Education Partner Manager','Partner-school onboarding, school staff and admissions oversight.',1,1,UTC_TIMESTAMP(),UTC_TIMESTAMP()),
('EDUCATION_PAYMENTS_OFFICER','Education Payments Officer','Verified school-beneficiary disbursement and education payment reconciliation.',1,1,UTC_TIMESTAMP(),UTC_TIMESTAMP());

-- Super Administrator receives every education permission.
INSERT IGNORE INTO admin_cc_role_permissions(role_id,permission_id,granted_at)
SELECT r.role_id,p.permission_id,UTC_TIMESTAMP() FROM admin_cc_roles r CROSS JOIN admin_cc_permissions p
WHERE r.role_code='SUPER_ADMIN' AND p.permission_code LIKE 'education.%';
-- Education Administrator receives every education permission.
INSERT IGNORE INTO admin_cc_role_permissions(role_id,permission_id,granted_at)
SELECT r.role_id,p.permission_id,UTC_TIMESTAMP() FROM admin_cc_roles r CROSS JOIN admin_cc_permissions p
WHERE r.role_code='EDUCATION_ADMIN' AND p.permission_code LIKE 'education.%';
-- Education Credit Officer
INSERT IGNORE INTO admin_cc_role_permissions(role_id,permission_id,granted_at)
SELECT r.role_id,p.permission_id,UTC_TIMESTAMP() FROM admin_cc_roles r JOIN admin_cc_permissions p ON p.permission_code IN
('education.view','education.admissions.view','education.fees.view','education.loans.view','education.loans.manage','education.payments.view','education.documents.view','education.audit.view')
WHERE r.role_code='EDUCATION_CREDIT_OFFICER';
-- Education Partner Manager
INSERT IGNORE INTO admin_cc_role_permissions(role_id,permission_id,granted_at)
SELECT r.role_id,p.permission_id,UTC_TIMESTAMP() FROM admin_cc_roles r JOIN admin_cc_permissions p ON p.permission_code IN
('education.view','education.schools.manage','education.school_staff.manage','education.admissions.view','education.admissions.manage','education.fees.view','education.documents.view','education.audit.view')
WHERE r.role_code='EDUCATION_PARTNER_MANAGER';

-- Education Payments Officer
INSERT IGNORE INTO admin_cc_role_permissions(role_id,permission_id,granted_at)
SELECT r.role_id,p.permission_id,UTC_TIMESTAMP() FROM admin_cc_roles r JOIN admin_cc_permissions p ON p.permission_code IN
('education.view','education.loans.view','education.loans.disburse','education.payments.view','education.payments.manage','education.documents.view','education.audit.view')
WHERE r.role_code='EDUCATION_PAYMENTS_OFFICER';

INSERT IGNORE INTO admin_cc_schema_versions(version,description,installed_at)
VALUES('2026.9.0','Education, school portal, admissions, school fees finance, QR verification and school RBAC',UTC_TIMESTAMP());