-- TELSPAY EDUCATION SCHOOL SUBSCRIPTIONS v2026.9.1
-- Additive migration. Run after TELSPAY_EDUCATION_V2026_9_0.sql.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- Keep subscription collection documents inside the existing QR verification registry.
ALTER TABLE edu_document_verifications
  MODIFY 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') NOT NULL;

CREATE TABLE IF NOT EXISTS edu_subscription_features (
  feature_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  feature_code VARCHAR(80) NOT NULL,
  feature_name VARCHAR(160) NOT NULL,
  feature_type ENUM('TOGGLE','LIMIT') NOT NULL DEFAULT 'TOGGLE',
  module_code VARCHAR(60) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  PRIMARY KEY (feature_id),
  UNIQUE KEY uq_edu_sub_feature_code (feature_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_subscription_plans (
  plan_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  plan_ref VARCHAR(40) NOT NULL,
  plan_name VARCHAR(140) NOT NULL,
  description VARCHAR(1000) DEFAULT NULL,
  price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  billing_period_months INT UNSIGNED NOT NULL DEFAULT 1,
  upgrade_rank INT UNSIGNED NOT NULL DEFAULT 1,
  status ENUM('DRAFT','ACTIVE','INACTIVE','ARCHIVED') NOT NULL DEFAULT 'DRAFT',
  is_public TINYINT(1) NOT NULL DEFAULT 1,
  created_by_admin INT DEFAULT NULL,
  updated_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 (plan_id),
  UNIQUE KEY uq_edu_sub_plan_ref (plan_ref),
  UNIQUE KEY uq_edu_sub_plan_name (plan_name),
  KEY idx_edu_sub_plan_status (status,upgrade_rank)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_subscription_plan_features (
  plan_id BIGINT UNSIGNED NOT NULL,
  feature_id INT UNSIGNED NOT NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 0,
  limit_value 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 (plan_id,feature_id),
  CONSTRAINT fk_edu_spf_plan FOREIGN KEY (plan_id) REFERENCES edu_subscription_plans(plan_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_spf_feature FOREIGN KEY (feature_id) REFERENCES edu_subscription_features(feature_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_subscriptions (
  subscription_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subscription_ref VARCHAR(50) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  replaces_subscription_id BIGINT UNSIGNED DEFAULT NULL,
  subscription_type ENUM('NEW','RENEWAL','UPGRADE','ADMIN_GRANT') NOT NULL DEFAULT 'NEW',
  status ENUM('PENDING_PAYMENT','PAID_PENDING_ACTIVATION','SCHEDULED','ACTIVE','GRACE','EXPIRED','SUPERSEDED','CANCELLED','SUSPENDED') NOT NULL DEFAULT 'PENDING_PAYMENT',
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME DEFAULT NULL,
  starts_at DATETIME DEFAULT NULL,
  ends_at DATETIME DEFAULT NULL,
  grace_ends_at DATETIME DEFAULT NULL,
  activated_by_type ENUM('SYSTEM','SACCO_ADMIN') DEFAULT NULL,
  activated_by_id INT DEFAULT NULL,
  cancelled_at DATETIME DEFAULT NULL,
  cancellation_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,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (subscription_id),
  UNIQUE KEY uq_edu_school_subscription_ref (subscription_ref),
  KEY idx_edu_school_sub_school (school_id,status,ends_at),
  KEY idx_edu_school_sub_plan (plan_id,status),
  CONSTRAINT fk_edu_school_sub_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_school_sub_plan FOREIGN KEY (plan_id) REFERENCES edu_subscription_plans(plan_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_school_sub_replaces FOREIGN KEY (replaces_subscription_id) REFERENCES edu_school_subscriptions(subscription_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_school_subscription_payments (
  payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_ref VARCHAR(60) NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  payment_channel ENUM('FLUTTERWAVE','MANUAL_EXCEPTION') NOT NULL DEFAULT 'FLUTTERWAVE',
  provider VARCHAR(40) NOT NULL DEFAULT 'FLUTTERWAVE',
  provider_transaction_id VARCHAR(120) DEFAULT NULL,
  provider_reference VARCHAR(180) DEFAULT NULL,
  provider_status VARCHAR(60) DEFAULT NULL,
  hosted_payment_link TEXT DEFAULT NULL,
  link_expires_at DATETIME DEFAULT NULL,
  status ENUM('CREATED','LINK_READY','PENDING','SUCCESSFUL','DUPLICATE_SUCCESS','FAILED','CANCELLED','EXPIRED','PENDING_MANUAL_APPROVAL','MANUAL_APPROVED','MANUAL_REJECTED') NOT NULL DEFAULT 'CREATED',
  initiated_by_type ENUM('SACCO_ADMIN','SCHOOL_USER','SYSTEM') NOT NULL DEFAULT 'SYSTEM',
  initiated_by_id BIGINT DEFAULT NULL,
  manual_reference VARCHAR(180) DEFAULT NULL,
  manual_reason VARCHAR(1000) DEFAULT NULL,
  manual_requested_by INT DEFAULT NULL,
  manual_approved_by INT DEFAULT NULL,
  manual_approved_at DATETIME DEFAULT NULL,
  verified_at DATETIME DEFAULT NULL,
  raw_response_hash CHAR(64) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (payment_id),
  UNIQUE KEY uq_edu_sub_payment_ref (payment_ref),
  UNIQUE KEY uq_edu_subpay_manual_reference (manual_reference),
  KEY idx_edu_sub_payment_school (school_id,status,created_at),
  KEY idx_edu_sub_payment_subscription (subscription_id,status),
  CONSTRAINT fk_edu_subpay_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_subpay_subscription FOREIGN KEY (subscription_id) REFERENCES edu_school_subscriptions(subscription_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_subpay_plan FOREIGN KEY (plan_id) REFERENCES edu_subscription_plans(plan_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_subscription_income_ledger (
  income_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  income_ref VARCHAR(60) NOT NULL,
  payment_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(15,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'UGX',
  income_category VARCHAR(80) NOT NULL DEFAULT 'SCHOOL_SUBSCRIPTION_INCOME',
  core_gl_account_code VARCHAR(80) DEFAULT NULL,
  core_gl_posting_status ENUM('NOT_CONFIGURED','PENDING','POSTED','FAILED') NOT NULL DEFAULT 'NOT_CONFIGURED',
  core_gl_reference VARCHAR(120) DEFAULT NULL,
  recognized_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (income_id),
  UNIQUE KEY uq_edu_sub_income_ref (income_ref),
  UNIQUE KEY uq_edu_sub_income_payment (payment_id),
  KEY idx_edu_sub_income_school (school_id,recognized_at),
  KEY idx_edu_sub_income_date (recognized_at),
  CONSTRAINT fk_edu_subincome_payment FOREIGN KEY (payment_id) REFERENCES edu_school_subscription_payments(payment_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_subincome_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_subincome_subscription FOREIGN KEY (subscription_id) REFERENCES edu_school_subscriptions(subscription_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_subincome_plan FOREIGN KEY (plan_id) REFERENCES edu_subscription_plans(plan_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_subscription_reminders (
  reminder_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  subscription_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  reminder_code VARCHAR(40) NOT NULL,
  channel ENUM('EMAIL','SYSTEM') NOT NULL DEFAULT 'EMAIL',
  status ENUM('PENDING','SENT','FAILED','SKIPPED') NOT NULL DEFAULT 'PENDING',
  recipient_masked VARCHAR(180) DEFAULT NULL,
  sent_at DATETIME DEFAULT NULL,
  error_message VARCHAR(500) DEFAULT NULL,
  attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
  last_attempt_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (reminder_id),
  UNIQUE KEY uq_edu_sub_reminder_once (subscription_id,reminder_code,channel),
  KEY idx_edu_sub_reminder_school (school_id,status,created_at),
  CONSTRAINT fk_edu_subrem_subscription FOREIGN KEY (subscription_id) REFERENCES edu_school_subscriptions(subscription_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_subrem_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO edu_subscription_features(feature_code,feature_name,feature_type,module_code,description,sort_order) VALUES
('ADMISSIONS','Online Admissions','TOGGLE','admissions','Receive and process online student admission applications.',10),
('CUSTOM_ADMISSION_FORMS','Custom Admission Forms','TOGGLE','admissions','Create school-specific forms, fields, registration fees and requirements.',20),
('STUDENTS','Student Management','TOGGLE','students','View and manage accepted/enrolled students.',30),
('FEES','Fee Structures & Student Ledgers','TOGGLE','fees','Publish school fee structures and manage student fee information.',40),
('PAYMENTS','School Fee Payments','TOGGLE','payments','View and confirm student school-fee payments.',50),
('SACCO_LOANS','School Fees Loan Visibility','TOGGLE','loans','View SACCO education loans linked to the school.',60),
('QR_DOCUMENTS','QR Verified Documents','TOGGLE','documents','Issue and verify education documents with QR references.',70),
('STAFF','School Staff Portal','TOGGLE','staff','Use role-controlled school staff portal access.',80),
('ACADEMIC_SETUP','Academic Setup','TOGGLE','settings','Configure academic years, terms, classes and requirements.',90),
('REPORTS','Reports','TOGGLE','reports','Access school operational and finance reports.',100),
('RECONCILIATION','Settlement Reconciliation','TOGGLE','reconciliation','Access settlement reconciliation and exception controls.',110),
('EMAIL_NOTIFICATIONS','Education Email Notifications','TOGGLE','notifications','Receive automated admissions, payment and subscription email notices.',120),
('STUDENT_LIMIT','Maximum Active Students','LIMIT','students','Maximum distinct accepted/enrolled students under the package.',200),
('ADMISSIONS_MONTHLY_LIMIT','Monthly Admission Applications','LIMIT','admissions','Maximum admission applications received in a calendar month.',210),
('STAFF_LIMIT','Maximum School Portal Users','LIMIT','staff','Maximum active school portal users.',220),
('DOCUMENTS_MONTHLY_LIMIT','Monthly QR Documents','LIMIT','documents','Maximum QR-verifiable documents issued in a calendar month.',230);

INSERT IGNORE INTO edu_school_permissions(permission_code,permission_name,module_code,description) VALUES
('school.subscription.view','View school subscription','subscription','View current package, expiry, usage and payment history.'),
('school.subscription.apply','Apply, renew or upgrade subscription','subscription','Request a package and create a Flutterwave subscription payment link.');

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.subscription.view','school.subscription.apply')
WHERE r.role_code IN ('SCHOOL_SUPER_ADMIN','SCHOOL_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='school.subscription.view'
WHERE r.role_code IN ('SCHOOL_HEAD_TEACHER','SCHOOL_BURSAR','SCHOOL_ACCOUNTANT','SCHOOL_AUDITOR_READ_ONLY');

INSERT IGNORE INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.subscriptions.view','View school subscriptions','education','HIGH','View packages, school subscriptions, payments and expiry.',UTC_TIMESTAMP()),
('education.subscriptions.manage','Manage school subscription packages','education','CRITICAL','Create, update, publish and archive school packages and entitlements.',UTC_TIMESTAMP()),
('education.subscriptions.payment_link','Generate school subscription payment links','education','HIGH','Create Flutterwave hosted links for school subscription collection.',UTC_TIMESTAMP()),
('education.subscriptions.manual_approve','Manually approve subscription payment exception','education','CRITICAL','Approve evidenced non-automatic subscription payments under maker/checker control.',UTC_TIMESTAMP()),
('education.subscriptions.income.view','View subscription income','education','HIGH','View verified school subscription income and GL posting state.',UTC_TIMESTAMP());

INSERT IGNORE INTO admin_cc_roles(role_code,role_name,description,is_system,is_active,created_at,updated_at) VALUES
('EDUCATION_SUBSCRIPTION_MANAGER','Education Subscription Manager','Manages school packages, renewals, payment links and subscription income.',1,1,UTC_TIMESTAMP(),UTC_TIMESTAMP());

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.subscriptions.%';
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.subscriptions.view','education.subscriptions.manage','education.subscriptions.payment_link','education.subscriptions.income.view')
WHERE r.role_code IN ('EDUCATION_PARTNER_MANAGER','EDUCATION_SUBSCRIPTION_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.subscriptions.view','education.subscriptions.payment_link','education.subscriptions.manual_approve','education.subscriptions.income.view')
WHERE r.role_code='EDUCATION_PAYMENTS_OFFICER';

INSERT IGNORE INTO admin_cc_schema_versions(version,description,installed_at)
VALUES('2026.9.1','School subscription packages, feature quotas, Flutterwave links, renewals, manual approval and subscription income',UTC_TIMESTAMP());