-- TELSPAY EDUCATION v2026.9.5 — EDUCATION FINANCE, DOCUMENT CONTROL, AI & SECURITY
-- Additive migration. Run AFTER v2026.9.0 → v2026.9.4 migrations.
-- No core customer/share balances are rewritten by this migration.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- -----------------------------------------------------------------------------
-- SCHOOL / MEMBER / STUDENT EDUCATION CONTROL
-- -----------------------------------------------------------------------------
ALTER TABLE edu_students
  ADD COLUMN IF NOT EXISTS education_status ENUM('ACTIVE','SUSPENDED','REVOKED','TERMINATED') NOT NULL DEFAULT 'ACTIVE' AFTER kyc_status,
  ADD COLUMN IF NOT EXISTS education_status_reason VARCHAR(500) DEFAULT NULL AFTER education_status,
  ADD COLUMN IF NOT EXISTS education_status_changed_by INT DEFAULT NULL AFTER education_status_reason,
  ADD COLUMN IF NOT EXISTS education_status_changed_at DATETIME DEFAULT NULL AFTER education_status_changed_by;

CREATE TABLE IF NOT EXISTS edu_member_education_controls (
  member_id INT NOT NULL,
  status ENUM('ACTIVE','SUSPENDED','REVOKED','TERMINATED') NOT NULL DEFAULT 'ACTIVE',
  reason VARCHAR(500) DEFAULT NULL,
  changed_by_admin_id INT DEFAULT NULL,
  changed_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(member_id),
  KEY idx_edu_member_control_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- SCHOOL DOCUMENT ASSIGNMENT / CHANGE REQUEST CONTROL
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS edu_school_documents (
  school_document_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  document_ref VARCHAR(70) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  document_category ENUM('KYB','COMPLIANCE','FINANCE','AGREEMENT','ACADEMIC','MARKETING','OTHER') NOT NULL DEFAULT 'OTHER',
  document_name VARCHAR(200) NOT NULL,
  requirement_status ENUM('REQUIRED','OPTIONAL') NOT NULL DEFAULT 'OPTIONAL',
  document_status ENUM('ASSIGNED','SUBMITTED','UNDER_REVIEW','APPROVED','REJECTED','REVOKED') NOT NULL DEFAULT 'ASSIGNED',
  original_name VARCHAR(255) DEFAULT NULL,
  storage_path VARCHAR(500) DEFAULT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  file_size BIGINT UNSIGNED DEFAULT NULL,
  sha256_hash CHAR(64) DEFAULT NULL,
  admin_note VARCHAR(1000) DEFAULT NULL,
  assigned_by_admin_id INT DEFAULT NULL,
  uploaded_by_school_user BIGINT UNSIGNED DEFAULT NULL,
  submitted_at DATETIME DEFAULT NULL,
  reviewed_by_admin_id INT DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  revoked_by_admin_id INT DEFAULT NULL,
  revoked_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(school_document_id),
  UNIQUE KEY uq_edu_school_document_ref(document_ref),
  KEY idx_edu_school_documents_school(school_id,document_status,document_category),
  CONSTRAINT fk_edu_school_documents_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_school_documents_user FOREIGN KEY(uploaded_by_school_user) REFERENCES edu_school_users(school_user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_change_requests (
  change_request_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  request_ref VARCHAR(70) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  section_code VARCHAR(80) NOT NULL,
  request_text VARCHAR(1500) NOT NULL,
  severity ENUM('INFO','LOW','MEDIUM','HIGH','CRITICAL') NOT NULL DEFAULT 'MEDIUM',
  status ENUM('OPEN','RESPONDED','RESOLVED','CANCELLED') NOT NULL DEFAULT 'OPEN',
  requested_by_admin_id INT NOT NULL,
  responded_by_school_user BIGINT UNSIGNED DEFAULT NULL,
  response_text VARCHAR(1500) DEFAULT NULL,
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  responded_at DATETIME DEFAULT NULL,
  resolved_by_admin_id INT DEFAULT NULL,
  resolved_at DATETIME DEFAULT NULL,
  PRIMARY KEY(change_request_id),
  UNIQUE KEY uq_edu_school_change_request_ref(request_ref),
  KEY idx_edu_school_change_school(school_id,status,requested_at),
  CONSTRAINT fk_edu_school_change_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_school_change_user FOREIGN KEY(responded_by_school_user) REFERENCES edu_school_users(school_user_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- EDUCATION FINANCE POLICY (VALUES ARE ADMIN-CONTROLLED; DEFAULTS DO NOT INVENT POLICY)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS edu_finance_settings (
  setting_key VARCHAR(90) NOT NULL,
  setting_value VARCHAR(255) NOT NULL,
  value_type ENUM('STRING','INTEGER','DECIMAL','BOOLEAN','PERCENT','ENUM') NOT NULL DEFAULT 'STRING',
  description VARCHAR(500) DEFAULT NULL,
  updated_by_admin_id INT DEFAULT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO edu_finance_settings(setting_key,setting_value,value_type,description) VALUES
('school_fee_commission_type','PERCENT','ENUM','Commission mode applied to successful school-fees payments: PERCENT or FIXED.'),
('school_fee_commission_value','0','DECIMAL','SACCO commission value. Configure before production use.'),
('loan_min_savings_ratio_percent','0','PERCENT','Minimum Savings balance as a percentage of requested school-fees loan.'),
('loan_min_shares_ratio_percent','0','PERCENT','Minimum Shares balance as a percentage of requested school-fees loan. Shares are eligibility collateral only and are never debited for school-fees payments.'),
('loan_min_invest_ratio_percent','0','PERCENT','Minimum Education Invest balance as a percentage of requested school-fees loan.'),
('loan_min_total_cover_percent','0','PERCENT','Minimum combined Savings + Shares + Education Invest cover as a percentage of requested school-fees loan.'),
('school_fee_max_payment','0','DECIMAL','Maximum single school-fees payment. Zero means no extra Education-module cap.'),
('school_fee_daily_member_limit','0','DECIMAL','Maximum school-fees payments per member per UTC day. Zero means no extra Education-module cap.'),
('guest_mobile_money_enabled','1','BOOLEAN','Allow valid student payment numbers to be paid by Mobile Money without a SACCO member session.')
ON DUPLICATE KEY UPDATE description=VALUES(description);

-- -----------------------------------------------------------------------------
-- MEMBER EDUCATION INVEST ACCOUNT — LEDGER IS AUTHORITATIVE
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS edu_member_invest_accounts (
  invest_account_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id INT NOT NULL,
  account_ref VARCHAR(70) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  cached_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  account_status ENUM('ACTIVE','LOCKED','SUSPENDED','CLOSED') NOT NULL DEFAULT 'ACTIVE',
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(invest_account_id),
  UNIQUE KEY uq_edu_invest_member(member_id),
  UNIQUE KEY uq_edu_invest_ref(account_ref),
  KEY idx_edu_invest_status(account_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_invest_ledger (
  invest_ledger_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  invest_account_id BIGINT UNSIGNED NOT NULL,
  transaction_ref VARCHAR(90) NOT NULL,
  transaction_type ENUM('SAVINGS_TRANSFER','MOBILE_MONEY_TOPUP','SCHOOL_FEE_PAYMENT','REVERSAL','ADMIN_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,
  source_type VARCHAR(80) DEFAULT NULL,
  source_id VARCHAR(100) DEFAULT NULL,
  status ENUM('POSTED','REVERSED') NOT NULL DEFAULT 'POSTED',
  description VARCHAR(500) NOT NULL,
  actor_type ENUM('MEMBER','SACCO_ADMIN','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  actor_id BIGINT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(invest_ledger_id),
  UNIQUE KEY uq_edu_invest_ledger_ref(transaction_ref),
  KEY idx_edu_invest_ledger_account(invest_account_id,created_at),
  CONSTRAINT fk_edu_invest_ledger_account FOREIGN KEY(invest_account_id) REFERENCES edu_member_invest_accounts(invest_account_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- -----------------------------------------------------------------------------
-- MEMBER EDUCATION INVEST MOBILE MONEY TOPUPS
-- -----------------------------------------------------------------------------
INSERT INTO edu_finance_settings(setting_key,setting_value,value_type,description) VALUES
('education_invest_topup_enabled','1','BOOLEAN','Allow authenticated SACCO members to top up Education Invest from the Member App using verified Mobile Money.'),
('education_invest_min_topup','500','DECIMAL','Minimum Education Invest Mobile Money topup in UGX.'),
('education_invest_max_topup','0','DECIMAL','Maximum Education Invest Mobile Money topup. Zero means no extra Education-module maximum.')
ON DUPLICATE KEY UPDATE description=VALUES(description);

CREATE TABLE IF NOT EXISTS edu_invest_topups (
  invest_topup_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  topup_ref VARCHAR(80) NOT NULL,
  tx_ref VARCHAR(100) NOT NULL,
  invest_account_id BIGINT UNSIGNED NOT NULL,
  member_id INT NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  payer_phone VARCHAR(60) NOT NULL,
  payer_email VARCHAR(190) DEFAULT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'FLUTTERWAVE',
  provider_transaction_id VARCHAR(120) DEFAULT NULL,
  provider_reference VARCHAR(180) DEFAULT NULL,
  checkout_url TEXT DEFAULT NULL,
  topup_status ENUM('CREATED','CHECKOUT_READY','PENDING','VERIFIED','POSTED','FAILED','EXPIRED','CANCELLED','REVERSED') NOT NULL DEFAULT 'CREATED',
  idempotency_key CHAR(64) NOT NULL,
  failure_reason VARCHAR(1000) DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  posted_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(invest_topup_id),
  UNIQUE KEY uq_edu_invest_topup_ref(topup_ref),
  UNIQUE KEY uq_edu_invest_topup_tx_ref(tx_ref),
  UNIQUE KEY uq_edu_invest_topup_idem(idempotency_key),
  KEY idx_edu_invest_topup_member(member_id,topup_status,created_at),
  KEY idx_edu_invest_topup_account(invest_account_id,topup_status,created_at),
  CONSTRAINT fk_edu_invest_topup_account FOREIGN KEY(invest_account_id) REFERENCES edu_member_invest_accounts(invest_account_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- STUDENT PAYMENT NUMBER / SECURE FEE PAYMENT INTENTS
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS edu_student_payment_numbers (
  student_payment_number_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_number VARCHAR(40) NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  member_id INT DEFAULT NULL,
  application_id BIGINT UNSIGNED DEFAULT NULL,
  academic_year_id BIGINT UNSIGNED DEFAULT NULL,
  term_id BIGINT UNSIGNED DEFAULT NULL,
  class_id BIGINT UNSIGNED DEFAULT NULL,
  status ENUM('ACTIVE','SUSPENDED','REVOKED') NOT NULL DEFAULT 'ACTIVE',
  status_reason VARCHAR(500) DEFAULT NULL,
  created_by_type ENUM('SACCO_ADMIN','SCHOOL_USER','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  created_by_id BIGINT DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_used_at DATETIME DEFAULT NULL,
  PRIMARY KEY(student_payment_number_id),
  UNIQUE KEY uq_edu_student_payment_number(payment_number),
  KEY idx_edu_payment_number_student(student_id,school_id,status),
  KEY idx_edu_payment_number_member(member_id,status),
  CONSTRAINT fk_edu_payment_number_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_payment_number_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_payment_number_app FOREIGN KEY(application_id) REFERENCES edu_admission_applications(application_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_payment_number_year FOREIGN KEY(academic_year_id) REFERENCES edu_academic_years(academic_year_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_payment_number_term FOREIGN KEY(term_id) REFERENCES edu_terms(term_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_payment_number_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_payment_intents (
  intent_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  intent_ref VARCHAR(80) NOT NULL,
  tx_ref VARCHAR(100) NOT NULL,
  payment_number VARCHAR(40) NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  member_id INT DEFAULT NULL,
  payer_name VARCHAR(180) NOT NULL,
  payer_phone VARCHAR(60) NOT NULL,
  payer_email VARCHAR(190) DEFAULT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  payment_method ENUM('MOBILE_MONEY','SACCO_SAVINGS','EDUCATION_INVEST') NOT NULL,
  idempotency_key CHAR(64) NOT NULL,
  provider_transaction_id VARCHAR(100) DEFAULT NULL,
  provider_reference VARCHAR(190) DEFAULT NULL,
  checkout_url TEXT DEFAULT NULL,
  intent_status ENUM('CREATED','CHECKOUT_READY','PENDING','VERIFIED','POSTED','FAILED','EXPIRED','CANCELLED','REVERSED') NOT NULL DEFAULT 'CREATED',
  ip_hash CHAR(64) DEFAULT NULL,
  user_agent_hash CHAR(64) DEFAULT NULL,
  failure_reason VARCHAR(1000) DEFAULT NULL,
  expires_at DATETIME DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  posted_payment_id 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(intent_id),
  UNIQUE KEY uq_edu_fee_intent_ref(intent_ref),
  UNIQUE KEY uq_edu_fee_intent_tx_ref(tx_ref),
  UNIQUE KEY uq_edu_fee_intent_idempotency(idempotency_key),
  KEY idx_edu_fee_intent_status(intent_status,created_at),
  KEY idx_edu_fee_intent_student(student_id,created_at),
  CONSTRAINT fk_edu_fee_intent_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_fee_intent_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE edu_school_fee_payments
  MODIFY COLUMN payment_method ENUM('SACCO_SAVINGS','EDUCATION_INVEST','MOBILE_MONEY','BANK','SCHOOL_FEES_LOAN','MANUAL_RECONCILIATION') NOT NULL,
  ADD COLUMN IF NOT EXISTS payment_number VARCHAR(40) DEFAULT NULL AFTER student_account_id,
  ADD COLUMN IF NOT EXISTS academic_year_id BIGINT UNSIGNED DEFAULT NULL AFTER payment_number,
  ADD COLUMN IF NOT EXISTS term_id BIGINT UNSIGNED DEFAULT NULL AFTER academic_year_id,
  ADD COLUMN IF NOT EXISTS class_id BIGINT UNSIGNED DEFAULT NULL AFTER term_id,
  ADD COLUMN IF NOT EXISTS gross_amount DECIMAL(15,2) DEFAULT NULL AFTER amount,
  ADD COLUMN IF NOT EXISTS commission_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00 AFTER gross_amount,
  ADD COLUMN IF NOT EXISTS settlement_amount DECIMAL(15,2) DEFAULT NULL AFTER commission_amount,
  ADD COLUMN IF NOT EXISTS payer_phone_masked VARCHAR(60) DEFAULT NULL AFTER school_reference,
  ADD COLUMN IF NOT EXISTS receipt_public_ref CHAR(32) DEFAULT NULL AFTER payer_phone_masked,
  ADD COLUMN IF NOT EXISTS idempotency_key CHAR(64) DEFAULT NULL AFTER receipt_public_ref,
  ADD UNIQUE KEY IF NOT EXISTS uq_edu_fee_payment_idempotency(idempotency_key),
  ADD KEY IF NOT EXISTS idx_edu_fee_payment_number(payment_number,initiated_at);

CREATE TABLE IF NOT EXISTS edu_school_fee_commission_ledger (
  commission_ledger_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_id BIGINT UNSIGNED NOT NULL,
  payment_ref VARCHAR(80) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  gross_amount DECIMAL(15,2) NOT NULL,
  commission_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  school_net_amount DECIMAL(15,2) NOT NULL,
  commission_type ENUM('PERCENT','FIXED') NOT NULL,
  commission_value DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
  gl_posting_status ENUM('NOT_CONFIGURED','PENDING','POSTED','FAILED') NOT NULL DEFAULT 'NOT_CONFIGURED',
  gl_reference VARCHAR(120) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(commission_ledger_id),
  UNIQUE KEY uq_edu_commission_payment(payment_id),
  KEY idx_edu_commission_school(school_id,created_at),
  CONSTRAINT fk_edu_commission_payment FOREIGN KEY(payment_id) REFERENCES edu_school_fee_payments(payment_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_commission_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- AI SCHOOL MARKETING — PAID FEATURE GATE, PRICE MUST BE SET BY SACCO ADMIN
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS edu_ai_marketing_generations (
  generation_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  generation_ref VARCHAR(70) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  generated_by_type ENUM('SACCO_ADMIN','SCHOOL_USER','SYSTEM') NOT NULL,
  generated_by_id BIGINT DEFAULT NULL,
  content_type ENUM('WEBSITE','SOCIAL_POST','ADMISSION_CAMPAIGN','EMAIL','SMS','PROFILE','EVENT','GENERAL') NOT NULL DEFAULT 'GENERAL',
  objective VARCHAR(300) DEFAULT NULL,
  input_hash CHAR(64) NOT NULL,
  output_text MEDIUMTEXT DEFAULT NULL,
  provider VARCHAR(40) DEFAULT NULL,
  provider_model VARCHAR(100) DEFAULT NULL,
  generation_status ENUM('QUEUED','SUCCESS','FAILED','BLOCKED') NOT NULL DEFAULT 'QUEUED',
  provider_request_id VARCHAR(120) DEFAULT NULL,
  error_message VARCHAR(1000) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(generation_id),
  UNIQUE KEY uq_edu_ai_generation_ref(generation_ref),
  KEY idx_edu_ai_school(school_id,created_at),
  CONSTRAINT fk_edu_ai_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO edu_subscription_features(feature_code,feature_name,feature_type,module_code,description,is_active,sort_order) VALUES
('AI_MARKETING','School Growth AI','TOGGLE','marketing','AI-assisted school marketing content. Requires an active paid entitlement; generated content must be reviewed by the school before publishing.',1,240),
('AI_MARKETING_MONTHLY_LIMIT','AI Marketing Generations / Month','LIMIT','marketing','Maximum AI marketing generations per school per UTC month.',1,241)
ON DUPLICATE KEY UPDATE feature_name=VALUES(feature_name),description=VALUES(description),is_active=1;

INSERT INTO edu_subscription_plans(plan_ref,plan_name,description,price,currency,billing_period_months,upgrade_rank,status,is_public,is_free,is_perpetual,created_at,updated_at)
VALUES('AI-MARKETING','School Growth AI','Paid AI marketing add-on template for Uganda education marketing. SACCO Admin must set a non-zero approved price and publish the plan before schools can subscribe.',0,'UGX',1,50,'DRAFT',0,0,0,UTC_TIMESTAMP(),UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE description=VALUES(description),is_free=0,is_perpetual=0,updated_at=UTC_TIMESTAMP();

SET @ai_plan=(SELECT plan_id FROM edu_subscription_plans WHERE plan_ref='AI-MARKETING' LIMIT 1);
INSERT INTO edu_subscription_plan_features(plan_id,feature_id,is_enabled,limit_value)
SELECT @ai_plan,feature_id,1,CASE WHEN feature_code='AI_MARKETING_MONTHLY_LIMIT' THEN 30 ELSE NULL END
FROM edu_subscription_features WHERE feature_code IN('AI_MARKETING','AI_MARKETING_MONTHLY_LIMIT')
ON DUPLICATE KEY UPDATE is_enabled=VALUES(is_enabled),limit_value=VALUES(limit_value),updated_at=UTC_TIMESTAMP();

INSERT IGNORE INTO edu_school_permissions(permission_code,permission_name,module_code,description) VALUES
('school.ai_marketing.use','Use School Growth AI','marketing','Generate subscription-controlled school marketing drafts for review.'),
('school.finance.invest.view','View Education finance receipts','finance','View school-fees receipts and member-originated Education payments linked to the school.');

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
WHERE r.role_code IN('SCHOOL_SUPER_ADMIN','SCHOOL_HEAD_TEACHER','SCHOOL_FINANCE_MANAGER')
AND p.permission_code='school.ai_marketing.use';

-- -----------------------------------------------------------------------------
-- ADMIN CONTROL CENTER PERMISSIONS
-- -----------------------------------------------------------------------------
INSERT INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.analytics.view','View Education analytics','education','MEDIUM','View school, student, admission and fee-payment demographics/charts.',UTC_TIMESTAMP()),
('education.documents.view','View Education school documents','education','HIGH','View school KYB, compliance, media, receipts and verification documents.',UTC_TIMESTAMP()),
('education.documents.manage','Manage Education school documents','education','HIGH','Assign, review, reject, revoke and request corrections to school documents.',UTC_TIMESTAMP()),
('education.school_users.manage','Manage School Portal users & roles','education','HIGH','Change School Portal user status and role assignments.',UTC_TIMESTAMP()),
('education.finance.settings','Manage Education finance settings','education','CRITICAL','Set school-fee commission and education-loan balance policy.',UTC_TIMESTAMP()),
('education.transactions.search','Search Education transactions','education','HIGH','Search Education transactions and receipts across finance modules.',UTC_TIMESTAMP()),
('education.member_controls.manage','Manage member Education access','education','CRITICAL','Suspend, revoke or restore a member Education service profile without changing core SACCO membership.',UTC_TIMESTAMP()),
('education.student_controls.manage','Manage student Education access','education','HIGH','Suspend, revoke or restore student Education records.',UTC_TIMESTAMP()),
('education.ai.manage','Manage Education AI marketing','education','HIGH','Configure paid AI marketing plans and review school AI usage.',UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE permission_name=VALUES(permission_name),description=VALUES(description),risk_level=VALUES(risk_level);

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 IN('SUPER_ADMIN','EDUCATION_ADMIN') AND p.permission_code LIKE 'education.%';

-- Expand QR registry for Invest statements/correction notices while retaining every prior value.
ALTER TABLE edu_document_verifications
  MODIFY COLUMN 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',
    'SCHOOL_SUBSCRIPTION_RECEIPT','DEPENDANTS_REPORT','MEMBER_EDUCATION_PROFILE_REPORT',
    'STUDENT_LEDGER_REPORT','SCHOOL_PROFILE_REPORT','SCHOOL_ADMISSION_REPORT','SCHOOL_PAYMENT_REPORT',
    'EDUCATION_INVEST_STATEMENT','SCHOOL_CORRECTION_NOTICE'
  ) NOT NULL;

INSERT IGNORE INTO admin_cc_schema_versions(version,description,installed_at)
VALUES('2026.9.5','Education analytics, document control, School Portal role management, Education Invest account, secure school-fees payments, commission policy, AI marketing and stronger Education controls',UTC_TIMESTAMP());

-- =============================================================================
-- v2026.9.5 FINAL EXTENSION — MANUAL SUBSCRIPTIONS & STUDENT WALLET PORTAL
-- =============================================================================

-- Subscription payments: provider collection plus evidence-controlled Bank/Cash.
ALTER TABLE edu_school_subscription_payments
  MODIFY COLUMN payment_channel ENUM('FLUTTERWAVE','BANK_TRANSFER','CASH','ADMIN_GRANT','MANUAL_EXCEPTION') NOT NULL DEFAULT 'FLUTTERWAVE',
  ADD COLUMN IF NOT EXISTS payment_method ENUM('MOBILE_MONEY','CARD','BANK_TRANSFER','CASH','ADMIN_GRANT','UNKNOWN') NOT NULL DEFAULT 'UNKNOWN' AFTER payment_channel,
  ADD COLUMN IF NOT EXISTS evidence_review_status ENUM('NOT_REQUIRED','PENDING','APPROVED','REJECTED','REVOKED') NOT NULL DEFAULT 'NOT_REQUIRED' AFTER manual_reason,
  ADD COLUMN IF NOT EXISTS evidence_review_note VARCHAR(1000) DEFAULT NULL AFTER evidence_review_status,
  ADD COLUMN IF NOT EXISTS evidence_reviewed_by INT DEFAULT NULL AFTER evidence_review_note,
  ADD COLUMN IF NOT EXISTS evidence_reviewed_at DATETIME DEFAULT NULL AFTER evidence_reviewed_by,
  ADD COLUMN IF NOT EXISTS revoked_by INT DEFAULT NULL AFTER evidence_reviewed_at,
  ADD COLUMN IF NOT EXISTS revoked_at DATETIME DEFAULT NULL AFTER revoked_by;

CREATE TABLE IF NOT EXISTS edu_subscription_payment_evidence (
  evidence_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_id BIGINT UNSIGNED NOT NULL,
  evidence_ref VARCHAR(70) NOT NULL,
  evidence_type ENUM('BANK_RECEIPT','CASH_RECEIPT','BANK_STATEMENT','OTHER') NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  storage_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  sha256_hash CHAR(64) NOT NULL,
  submitted_by_type ENUM('SCHOOL_USER','SACCO_ADMIN') NOT NULL,
  submitted_by_id BIGINT NOT NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  review_status ENUM('PENDING','APPROVED','REJECTED','REVOKED') NOT NULL DEFAULT 'PENDING',
  reviewed_by_admin INT DEFAULT NULL,
  review_note VARCHAR(1000) DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  PRIMARY KEY(evidence_id),
  UNIQUE KEY uq_edu_sub_evidence_ref(evidence_ref),
  KEY idx_edu_sub_evidence_payment(payment_id,review_status,submitted_at),
  CONSTRAINT fk_edu_sub_evidence_payment FOREIGN KEY(payment_id) REFERENCES edu_school_subscription_payments(payment_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_subscription_admin_overrides (
  override_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  override_ref VARCHAR(70) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  old_subscription_id BIGINT UNSIGNED DEFAULT NULL,
  new_subscription_id BIGINT UNSIGNED DEFAULT NULL,
  old_plan_id BIGINT UNSIGNED DEFAULT NULL,
  new_plan_id BIGINT UNSIGNED DEFAULT NULL,
  override_action ENUM('GRANT','UPGRADE','DOWNGRADE','EXTEND','SUSPEND','REVOKE','REACTIVATE') NOT NULL,
  effective_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ends_at DATETIME DEFAULT NULL,
  reason VARCHAR(1500) NOT NULL,
  admin_id INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(override_id),
  UNIQUE KEY uq_edu_sub_override_ref(override_ref),
  KEY idx_edu_sub_override_school(school_id,created_at),
  CONSTRAINT fk_edu_sub_override_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_sub_override_old FOREIGN KEY(old_subscription_id) REFERENCES edu_school_subscriptions(subscription_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_sub_override_new FOREIGN KEY(new_subscription_id) REFERENCES edu_school_subscriptions(subscription_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student Wallet configuration. Defaults are safe/closed until Admin enables products/payouts.
INSERT INTO edu_finance_settings(setting_key,setting_value,value_type,description) VALUES
('student_wallet_withdrawal_enabled','0','BOOLEAN','Allow Student Wallet Mobile Money withdrawals after SACCO approval/provider setup.'),
('student_wallet_withdrawal_fee_type','FIXED','ENUM','Student Wallet withdrawal fee mode: FIXED or PERCENT.'),
('student_wallet_withdrawal_fee_value','0','DECIMAL','Withdrawal fee value configured by SACCO Admin.'),
('student_wallet_min_withdrawal','0','DECIMAL','Minimum Student Wallet withdrawal. Zero means no extra Education-module minimum.'),
('student_wallet_max_withdrawal','0','DECIMAL','Maximum single Student Wallet withdrawal. Zero means no extra Education-module maximum.'),
('student_wallet_daily_withdrawal_limit','0','DECIMAL','Maximum Student Wallet withdrawals per UTC day. Zero means no extra Education-module cap.'),
('student_wallet_internal_transfer_enabled','1','BOOLEAN','Allow transfers between active student wallets at the same school.'),
('student_wallet_daily_transfer_limit','0','DECIMAL','Maximum same-school student transfers per UTC day. Zero means no extra Education-module cap.'),
('student_wallet_topup_enabled','1','BOOLEAN','Allow Mobile Money topups to active student wallets.'),
('student_wallet_pin_attempts','3','INTEGER','Maximum wrong 4-digit transaction PIN attempts before wallet locks.'),
('student_wallet_loan_enabled','0','BOOLEAN','Allow Student Wallet short-loan applications after SACCO creates an active student loan product.'),
('student_wallet_loan_max_open','1','INTEGER','Maximum concurrent active/disbursed Student Wallet loans.'),
('student_wallet_loan_min_age','18','INTEGER','Minimum student age for direct Student Wallet borrowing unless SACCO policy/legal approval provides otherwise.')
ON DUPLICATE KEY UPDATE description=VALUES(description);

CREATE TABLE IF NOT EXISTS edu_student_wallet_accounts (
  wallet_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  wallet_ref VARCHAR(70) NOT NULL,
  wallet_number VARCHAR(40) NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  login_email VARCHAR(190) DEFAULT NULL,
  login_phone VARCHAR(60) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  transaction_pin_hash VARCHAR(255) DEFAULT NULL,
  pin_failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  pin_locked_at DATETIME DEFAULT NULL,
  must_change_password TINYINT(1) NOT NULL DEFAULT 1,
  wallet_status ENUM('PENDING','ACTIVE','LOCKED','SUSPENDED','BANNED','DEACTIVATED','CLOSED') NOT NULL DEFAULT 'PENDING',
  status_reason VARCHAR(1000) DEFAULT NULL,
  cached_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  last_login_at DATETIME DEFAULT NULL,
  last_login_ip_hash CHAR(64) DEFAULT NULL,
  created_by_type ENUM('SACCO_ADMIN','SCHOOL_USER','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  created_by_id BIGINT DEFAULT NULL,
  status_changed_by_type ENUM('SACCO_ADMIN','SCHOOL_USER','SYSTEM') DEFAULT NULL,
  status_changed_by_id BIGINT DEFAULT NULL,
  status_changed_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(wallet_id),
  UNIQUE KEY uq_edu_student_wallet_ref(wallet_ref),
  UNIQUE KEY uq_edu_student_wallet_number(wallet_number),
  UNIQUE KEY uq_edu_student_wallet_student_school(student_id,school_id),
  KEY idx_edu_student_wallet_school(school_id,wallet_status),
  KEY idx_edu_student_wallet_email(login_email),
  KEY idx_edu_student_wallet_phone(login_phone),
  CONSTRAINT fk_edu_student_wallet_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_wallet_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_wallet_ledger (
  wallet_ledger_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  wallet_id BIGINT UNSIGNED NOT NULL,
  transaction_ref VARCHAR(90) NOT NULL,
  transaction_type ENUM('MOBILE_MONEY_TOPUP','TRANSFER_IN','TRANSFER_OUT','NOTE_PURCHASE','STUDY_TOUR_PAYMENT','REQUIREMENT_PURCHASE','LOAN_DISBURSEMENT','LOAN_REPAYMENT','WITHDRAWAL','WITHDRAWAL_FEE','REVERSAL','ADMIN_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,
  source_type VARCHAR(80) DEFAULT NULL,
  source_id VARCHAR(120) DEFAULT NULL,
  description VARCHAR(500) NOT NULL,
  status ENUM('POSTED','REVERSED') NOT NULL DEFAULT 'POSTED',
  actor_type ENUM('STUDENT','SACCO_ADMIN','SCHOOL_USER','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  actor_id BIGINT DEFAULT NULL,
  ip_hash CHAR(64) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(wallet_ledger_id),
  UNIQUE KEY uq_edu_student_wallet_tx(transaction_ref),
  KEY idx_edu_student_wallet_ledger(wallet_id,created_at),
  KEY idx_edu_student_wallet_ledger_source(source_type,source_id),
  CONSTRAINT fk_edu_student_wallet_ledger_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_wallet_topups (
  topup_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  topup_ref VARCHAR(80) NOT NULL,
  tx_ref VARCHAR(100) NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  payer_phone VARCHAR(60) NOT NULL,
  payer_email VARCHAR(190) DEFAULT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'FLUTTERWAVE',
  provider_transaction_id VARCHAR(120) DEFAULT NULL,
  provider_reference VARCHAR(180) DEFAULT NULL,
  checkout_url TEXT DEFAULT NULL,
  topup_status ENUM('CREATED','CHECKOUT_READY','PENDING','VERIFIED','POSTED','FAILED','EXPIRED','CANCELLED','REVERSED') NOT NULL DEFAULT 'CREATED',
  idempotency_key CHAR(64) NOT NULL,
  verified_at DATETIME DEFAULT NULL,
  posted_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(topup_id),
  UNIQUE KEY uq_edu_student_topup_ref(topup_ref),
  UNIQUE KEY uq_edu_student_topup_tx_ref(tx_ref),
  UNIQUE KEY uq_edu_student_topup_idem(idempotency_key),
  KEY idx_edu_student_topup_wallet(wallet_id,topup_status,created_at),
  CONSTRAINT fk_edu_student_topup_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_topup_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_topup_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_wallet_transfers (
  transfer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  transfer_ref VARCHAR(80) NOT NULL,
  from_wallet_id BIGINT UNSIGNED NOT NULL,
  to_wallet_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  transfer_note VARCHAR(300) DEFAULT NULL,
  transfer_status ENUM('POSTED','REVERSED') NOT NULL DEFAULT 'POSTED',
  idempotency_key CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reversed_at DATETIME DEFAULT NULL,
  PRIMARY KEY(transfer_id),
  UNIQUE KEY uq_edu_student_transfer_ref(transfer_ref),
  UNIQUE KEY uq_edu_student_transfer_idem(idempotency_key),
  KEY idx_edu_student_transfer_from(from_wallet_id,created_at),
  KEY idx_edu_student_transfer_to(to_wallet_id,created_at),
  CONSTRAINT fk_edu_student_transfer_from FOREIGN KEY(from_wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_transfer_to FOREIGN KEY(to_wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_transfer_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_store_items (
  item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  item_ref VARCHAR(70) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  item_type ENUM('EDUCATION_NOTE','STUDY_TOUR','SCHOOL_REQUIREMENT','OTHER') NOT NULL,
  title VARCHAR(220) NOT NULL,
  description TEXT DEFAULT NULL,
  price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  class_id BIGINT UNSIGNED DEFAULT NULL,
  academic_year_id BIGINT UNSIGNED DEFAULT NULL,
  term_id BIGINT UNSIGNED DEFAULT NULL,
  content_storage_path VARCHAR(500) DEFAULT NULL,
  content_original_name VARCHAR(255) DEFAULT NULL,
  content_mime_type VARCHAR(100) DEFAULT NULL,
  content_sha256 CHAR(64) DEFAULT NULL,
  event_date DATE DEFAULT NULL,
  sales_start_at DATETIME DEFAULT NULL,
  sales_end_at DATETIME DEFAULT NULL,
  item_status ENUM('DRAFT','ACTIVE','SUSPENDED','ARCHIVED') NOT NULL DEFAULT 'DRAFT',
  created_by_school_user BIGINT UNSIGNED DEFAULT NULL,
  approved_by_admin INT DEFAULT NULL,
  approved_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(item_id),
  UNIQUE KEY uq_edu_student_store_item_ref(item_ref),
  KEY idx_edu_student_store_school(school_id,item_type,item_status),
  CONSTRAINT fk_edu_student_store_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_store_class FOREIGN KEY(class_id) REFERENCES edu_classes(class_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_student_store_year FOREIGN KEY(academic_year_id) REFERENCES edu_academic_years(academic_year_id) ON DELETE SET NULL,
  CONSTRAINT fk_edu_student_store_term FOREIGN KEY(term_id) REFERENCES edu_terms(term_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_store_purchases (
  purchase_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  purchase_ref VARCHAR(80) NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  purchase_status ENUM('POSTED','REVERSED') NOT NULL DEFAULT 'POSTED',
  idempotency_key CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(purchase_id),
  UNIQUE KEY uq_edu_student_purchase_ref(purchase_ref),
  UNIQUE KEY uq_edu_student_purchase_idem(idempotency_key),
  KEY idx_edu_student_purchase_wallet(wallet_id,created_at),
  KEY idx_edu_student_purchase_item(item_id,created_at),
  CONSTRAINT fk_edu_student_purchase_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_purchase_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_purchase_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_purchase_item FOREIGN KEY(item_id) REFERENCES edu_student_store_items(item_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_wallet_withdrawals (
  withdrawal_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  withdrawal_ref VARCHAR(80) NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  gross_amount DECIMAL(15,2) NOT NULL,
  fee_amount DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  payout_amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  mobile_network ENUM('MTN','AIRTEL') NOT NULL,
  mobile_number VARCHAR(60) NOT NULL,
  beneficiary_name VARCHAR(180) NOT NULL,
  withdrawal_status ENUM('PENDING_ADMIN','APPROVED','PAYOUT_SUBMITTED','PAYOUT_PENDING','SUCCESSFUL','FAILED','REJECTED','REVOKED','REVERSED') NOT NULL DEFAULT 'PENDING_ADMIN',
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_by_admin INT DEFAULT NULL,
  review_note VARCHAR(1000) DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'FLUTTERWAVE',
  provider_transfer_id VARCHAR(120) DEFAULT NULL,
  provider_reference VARCHAR(180) DEFAULT NULL,
  provider_status VARCHAR(60) DEFAULT NULL,
  submitted_at DATETIME DEFAULT NULL,
  completed_at DATETIME DEFAULT NULL,
  idempotency_key CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(withdrawal_id),
  UNIQUE KEY uq_edu_student_withdrawal_ref(withdrawal_ref),
  UNIQUE KEY uq_edu_student_withdrawal_idem(idempotency_key),
  KEY idx_edu_student_withdrawal_wallet(wallet_id,withdrawal_status,requested_at),
  KEY idx_edu_student_withdrawal_school(school_id,withdrawal_status,requested_at),
  CONSTRAINT fk_edu_student_withdrawal_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_withdrawal_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_withdrawal_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Student loan products are SACCO-controlled and may map to the existing core loan_products row.
CREATE TABLE IF NOT EXISTS edu_student_loan_products (
  student_loan_product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_ref VARCHAR(70) NOT NULL,
  core_loan_product_id INT DEFAULT NULL,
  product_name VARCHAR(180) NOT NULL,
  description VARCHAR(1000) DEFAULT NULL,
  min_principal DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  max_principal DECIMAL(15,2) NOT NULL,
  min_term_months INT UNSIGNED NOT NULL DEFAULT 1,
  max_term_months INT UNSIGNED NOT NULL,
  interest_rate_percent DECIMAL(10,4) NOT NULL,
  interest_method ENUM('FLAT','REDUCING_BALANCE') NOT NULL DEFAULT 'FLAT',
  minimum_wallet_history_days INT UNSIGNED NOT NULL DEFAULT 0,
  minimum_wallet_turnover DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  require_school_approval TINYINT(1) NOT NULL DEFAULT 1,
  product_status ENUM('DRAFT','ACTIVE','SUSPENDED','ARCHIVED') NOT NULL DEFAULT 'DRAFT',
  created_by_admin INT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by_admin INT DEFAULT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(student_loan_product_id),
  UNIQUE KEY uq_edu_student_loan_product_ref(product_ref),
  KEY idx_edu_student_loan_product_status(product_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_loans (
  student_loan_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  loan_ref VARCHAR(80) NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  student_loan_product_id BIGINT UNSIGNED NOT NULL,
  core_loan_product_id INT DEFAULT NULL,
  principal_requested DECIMAL(15,2) NOT NULL,
  principal_approved DECIMAL(15,2) DEFAULT NULL,
  interest_rate_percent DECIMAL(10,4) NOT NULL,
  interest_method ENUM('FLAT','REDUCING_BALANCE') NOT NULL,
  term_months INT UNSIGNED NOT NULL,
  interest_expected DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total_due DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  principal_outstanding DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  interest_outstanding DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  amount_repaid DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  loan_status ENUM('PENDING_SCHOOL','PENDING_SACCO','APPROVED','REJECTED','DISBURSED','ACTIVE','OVERDUE','CLOSED','SUSPENDED','REVOKED') NOT NULL DEFAULT 'PENDING_SCHOOL',
  application_reason VARCHAR(1000) NOT NULL,
  school_reviewed_by BIGINT UNSIGNED DEFAULT NULL,
  school_review_note VARCHAR(1000) DEFAULT NULL,
  school_reviewed_at DATETIME DEFAULT NULL,
  sacco_reviewed_by INT DEFAULT NULL,
  sacco_review_note VARCHAR(1000) DEFAULT NULL,
  sacco_reviewed_at DATETIME DEFAULT NULL,
  approved_at DATETIME DEFAULT NULL,
  disbursed_at DATETIME DEFAULT NULL,
  due_date DATE DEFAULT NULL,
  closed_at DATETIME DEFAULT NULL,
  idempotency_key CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(student_loan_id),
  UNIQUE KEY uq_edu_student_loan_ref(loan_ref),
  UNIQUE KEY uq_edu_student_loan_idem(idempotency_key),
  KEY idx_edu_student_loan_wallet(wallet_id,loan_status,created_at),
  KEY idx_edu_student_loan_school(school_id,loan_status,created_at),
  CONSTRAINT fk_edu_student_loan_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_loan_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_loan_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_loan_product FOREIGN KEY(student_loan_product_id) REFERENCES edu_student_loan_products(student_loan_product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_loan_repayments (
  repayment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  repayment_ref VARCHAR(80) NOT NULL,
  student_loan_id BIGINT UNSIGNED NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  interest_component DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  principal_component DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  payment_method ENUM('STUDENT_WALLET') NOT NULL DEFAULT 'STUDENT_WALLET',
  repayment_status ENUM('POSTED','REVERSED') NOT NULL DEFAULT 'POSTED',
  idempotency_key CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(repayment_id),
  UNIQUE KEY uq_edu_student_repayment_ref(repayment_ref),
  UNIQUE KEY uq_edu_student_repayment_idem(idempotency_key),
  KEY idx_edu_student_repayment_loan(student_loan_id,created_at),
  CONSTRAINT fk_edu_student_repayment_loan FOREIGN KEY(student_loan_id) REFERENCES edu_student_loans(student_loan_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_student_repayment_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_student_wallet_security_events (
  security_event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  event_uuid CHAR(36) NOT NULL,
  wallet_id BIGINT UNSIGNED DEFAULT NULL,
  student_id BIGINT UNSIGNED DEFAULT NULL,
  school_id BIGINT UNSIGNED DEFAULT NULL,
  event_code VARCHAR(80) NOT NULL,
  result_code VARCHAR(50) NOT NULL,
  severity ENUM('INFO','LOW','MEDIUM','HIGH','CRITICAL') NOT NULL DEFAULT 'INFO',
  ip_hash CHAR(64) DEFAULT NULL,
  user_agent_hash CHAR(64) DEFAULT NULL,
  detail_json LONGTEXT DEFAULT NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(security_event_id),
  UNIQUE KEY uq_edu_student_security_uuid(event_uuid),
  KEY idx_edu_student_security_wallet(wallet_id,occurred_at),
  KEY idx_edu_student_security_event(event_code,severity,occurred_at),
  CONSTRAINT fk_edu_student_security_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- School Portal permissions for Student Wallet operational visibility/content.
INSERT IGNORE INTO edu_school_permissions(permission_code,permission_name,module_code,description) VALUES
('school.student_wallet.view','View Student Wallets','student_wallet','View student wallet balances/transactions for students enrolled at this school.'),
('school.student_wallet.content.manage','Manage Student Wallet Content','student_wallet','Create education notes, study tours and school requirement items.'),
('school.student_loans.view','View Student Wallet Loans','student_loans','View school-linked Student Wallet loan applications and repayments.'),
('school.student_loans.review','Review Student Wallet Loans','student_loans','Provide the school review required before SACCO credit approval.');

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
WHERE r.role_code IN('SCHOOL_SUPER_ADMIN','SCHOOL_HEAD_TEACHER','SCHOOL_FINANCE_MANAGER','SCHOOL_BURSAR')
AND p.permission_code IN('school.student_wallet.view','school.student_loans.view');
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
WHERE r.role_code IN('SCHOOL_SUPER_ADMIN','SCHOOL_HEAD_TEACHER','SCHOOL_FINANCE_MANAGER')
AND p.permission_code IN('school.student_wallet.content.manage','school.student_loans.review');

-- SACCO Admin permissions for Student Wallet/loans/payouts.
INSERT INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.student_wallet.view','View Student Wallets','education','HIGH','View student wallet accounts, balances and transactions.',UTC_TIMESTAMP()),
('education.student_wallet.manage','Manage Student Wallets','education','CRITICAL','Activate, lock, suspend, ban, deactivate or restore Student Wallet accounts.',UTC_TIMESTAMP()),
('education.student_wallet.withdrawals','Manage Student Wallet Withdrawals','education','CRITICAL','Approve/reject and reconcile Student Wallet Mobile Money withdrawals.',UTC_TIMESTAMP()),
('education.student_wallet.products','Manage Student Education Store','education','HIGH','Review school education notes, study tours and requirement items.',UTC_TIMESTAMP()),
('education.student_loans.manage','Manage Student Wallet Loans','education','CRITICAL','Create student loan products and approve/reject/disburse Student Wallet loans.',UTC_TIMESTAMP()),
('education.subscription.override','Override School Subscription','education','CRITICAL','Manually grant, upgrade, downgrade, extend, suspend or revoke school subscriptions with an audit reason.',UTC_TIMESTAMP()),
('education.subscription.evidence','Review Subscription Evidence','education','CRITICAL','Review Bank Transfer/Cash subscription payment evidence and approve/revoke collections.',UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE permission_name=VALUES(permission_name),description=VALUES(description),risk_level=VALUES(risk_level);

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 IN('SUPER_ADMIN','EDUCATION_ADMIN') AND p.permission_code LIKE 'education.%';

-- QR registry expands for Student Wallet / loan receipts.
ALTER TABLE edu_document_verifications
  MODIFY COLUMN 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',
    'SCHOOL_SUBSCRIPTION_RECEIPT','DEPENDANTS_REPORT','MEMBER_EDUCATION_PROFILE_REPORT',
    'STUDENT_LEDGER_REPORT','SCHOOL_PROFILE_REPORT','SCHOOL_ADMISSION_REPORT','SCHOOL_PAYMENT_REPORT',
    'EDUCATION_INVEST_STATEMENT','SCHOOL_CORRECTION_NOTICE',
    'STUDENT_WALLET_RECEIPT','STUDENT_WALLET_STATEMENT','STUDENT_LOAN_APPLICATION','STUDENT_LOAN_AGREEMENT','STUDENT_LOAN_RECEIPT',
    'EDUCATION_INVEST_TOPUP_RECEIPT'
  ) NOT NULL;

INSERT IGNORE INTO admin_cc_schema_versions(version,description,installed_at)
VALUES('2026.9.5-student-wallet','Student Wallet Portal, same-school internal transfers, store purchases, controlled withdrawals, Student Wallet loans and evidence-based subscription collection',UTC_TIMESTAMP());