-- TELSPAY EDUCATION v2026.9.4 — ONBOARDING, FREE STARTER, EMAIL/SMS OUTBOX
-- Additive migration. Run AFTER v2026.9.0, v2026.9.1, v2026.9.2 and v2026.9.3.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

ALTER TABLE edu_schools
  ADD COLUMN IF NOT EXISTS onboarding_status ENUM('ACCOUNT_PENDING','ACCOUNT_APPROVED','PROFILE_REQUIRED','KYB_SUBMITTED','ACTIVE','REJECTED','SUSPENDED') NOT NULL DEFAULT 'ACCOUNT_PENDING' AFTER school_status,
  ADD COLUMN IF NOT EXISTS onboarding_note VARCHAR(1000) DEFAULT NULL AFTER onboarding_status,
  ADD COLUMN IF NOT EXISTS account_approved_by INT DEFAULT NULL AFTER onboarding_note,
  ADD COLUMN IF NOT EXISTS account_approved_at DATETIME DEFAULT NULL AFTER account_approved_by,
  ADD COLUMN IF NOT EXISTS kyb_submitted_at DATETIME DEFAULT NULL AFTER account_approved_at,
  ADD COLUMN IF NOT EXISTS onboarding_source ENUM('SACCO_ADMIN','PUBLIC_WEBSITE','MIGRATED') NOT NULL DEFAULT 'MIGRATED' AFTER kyb_submitted_at;

UPDATE edu_schools SET onboarding_status='ACTIVE', onboarding_source='MIGRATED'
WHERE kyb_status='APPROVED' AND school_status='ACTIVE' AND onboarding_status='ACCOUNT_PENDING';

ALTER TABLE edu_school_users
  ADD COLUMN IF NOT EXISTS is_onboarding_owner TINYINT(1) NOT NULL DEFAULT 0 AFTER must_change_password;

CREATE TABLE IF NOT EXISTS edu_school_kyb_documents (
  kyb_document_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  school_id BIGINT UNSIGNED NOT NULL,
  document_type ENUM('REGISTRATION_CERTIFICATE','SCHOOL_LICENCE','TIN_CERTIFICATE','BANK_PROOF','DIRECTOR_ID','OTHER') NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  storage_path VARCHAR(500) NOT NULL,
  mime_type VARCHAR(100) DEFAULT NULL,
  file_size BIGINT UNSIGNED DEFAULT NULL,
  sha256_hash CHAR(64) NOT NULL,
  review_status ENUM('PENDING','APPROVED','REJECTED') NOT NULL DEFAULT 'PENDING',
  review_note VARCHAR(500) DEFAULT NULL,
  reviewed_by INT DEFAULT NULL,
  reviewed_at DATETIME DEFAULT NULL,
  uploaded_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 (kyb_document_id),
  KEY idx_edu_kyb_school (school_id,review_status),
  CONSTRAINT fk_edu_kyb_school FOREIGN KEY (school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_kyb_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_notification_outbox (
  notification_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  notification_ref VARCHAR(60) NOT NULL,
  channel ENUM('EMAIL','SMS') NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  subject VARCHAR(255) DEFAULT NULL,
  body_html MEDIUMTEXT DEFAULT NULL,
  body_text TEXT DEFAULT NULL,
  school_id BIGINT UNSIGNED DEFAULT NULL,
  related_type VARCHAR(80) DEFAULT NULL,
  related_id VARCHAR(100) DEFAULT NULL,
  provider VARCHAR(50) DEFAULT NULL,
  provider_message_id VARCHAR(190) DEFAULT NULL,
  status ENUM('QUEUED','PROCESSING','SENT','FAILED','CANCELLED') NOT NULL DEFAULT 'QUEUED',
  attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
  next_attempt_at DATETIME DEFAULT NULL,
  last_attempt_at DATETIME DEFAULT NULL,
  sent_at DATETIME DEFAULT NULL,
  last_error VARCHAR(1000) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY(notification_id),
  UNIQUE KEY uq_edu_notification_ref(notification_ref),
  KEY idx_edu_notification_queue(status,next_attempt_at,created_at),
  KEY idx_edu_notification_school(school_id,created_at),
  CONSTRAINT fk_edu_notification_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS edu_sms_delivery_events (
  delivery_event_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  notification_id BIGINT UNSIGNED DEFAULT NULL,
  provider_message_id VARCHAR(190) DEFAULT NULL,
  phone_masked VARCHAR(40) DEFAULT NULL,
  delivery_status VARCHAR(80) NOT NULL,
  failure_reason VARCHAR(500) DEFAULT NULL,
  payload_hash CHAR(64) DEFAULT NULL,
  occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(delivery_event_id),
  KEY idx_edu_sms_event_message(provider_message_id,occurred_at),
  CONSTRAINT fk_edu_sms_event_notification FOREIGN KEY(notification_id) REFERENCES edu_notification_outbox(notification_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE edu_subscription_plans
  ADD COLUMN IF NOT EXISTS is_free TINYINT(1) NOT NULL DEFAULT 0 AFTER is_public,
  ADD COLUMN IF NOT EXISTS is_perpetual TINYINT(1) NOT NULL DEFAULT 0 AFTER is_free;

INSERT INTO edu_subscription_features(feature_code,feature_name,feature_type,module_code,description,is_active,sort_order) VALUES
('PUBLIC_PROFILE','Public Partner School Profile','TOGGLE','marketing','Publish a verified basic school profile in the TELSPAY Education directory.',1,5),
('ABOUT_PROFILE','About School Information','TOGGLE','marketing','Publish school overview and contact information.',1,6)
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('FREE-STARTER','Free Starter','A no-cost onboarding package for approved partner schools. Limited students, admissions, staff and QR documents; paid financial and advanced marketing services remain restricted.',0,'UGX',1,0,'ACTIVE',1,1,1,UTC_TIMESTAMP(),UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE description=VALUES(description),price=0,status='ACTIVE',is_public=1,is_free=1,is_perpetual=1,updated_at=UTC_TIMESTAMP();

SET @free_plan=(SELECT plan_id FROM edu_subscription_plans WHERE plan_ref='FREE-STARTER' LIMIT 1);
INSERT INTO edu_subscription_plan_features(plan_id,feature_id,is_enabled,limit_value)
SELECT @free_plan,feature_id,
  CASE WHEN feature_code IN ('ADMISSIONS','STUDENTS','QR_DOCUMENTS','STAFF','ACADEMIC_SETUP','PUBLIC_PROFILE','ABOUT_PROFILE') THEN 1
       WHEN feature_code IN ('STUDENT_LIMIT','ADMISSIONS_MONTHLY_LIMIT','STAFF_LIMIT','DOCUMENTS_MONTHLY_LIMIT') THEN 1 ELSE 0 END,
  CASE feature_code WHEN 'STUDENT_LIMIT' THEN 50 WHEN 'ADMISSIONS_MONTHLY_LIMIT' THEN 15 WHEN 'STAFF_LIMIT' THEN 2 WHEN 'DOCUMENTS_MONTHLY_LIMIT' THEN 10 ELSE NULL END
FROM edu_subscription_features
ON DUPLICATE KEY UPDATE is_enabled=VALUES(is_enabled),limit_value=VALUES(limit_value),updated_at=UTC_TIMESTAMP();

INSERT INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.notifications.view','View Education Email & SMS Operations','education','MEDIUM','View Education notification queue and delivery health.',UTC_TIMESTAMP()),
('education.notifications.manage','Manage Education Email & SMS Operations','education','HIGH','Retry/dispatch queued Education notifications and controlled test messages.',UTC_TIMESTAMP()),
('education.onboarding.approve','Approve Education school onboarding','education','HIGH','Approve school accounts, review KYB and activate partner-school access.',UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE permission_name=VALUES(permission_name),description=VALUES(description);

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
WHERE r.role_code IN ('SUPER_ADMIN','OPERATIONS_MAKER','REGISTRATION_OFFICER','NOTIFICATIONS_OFFICER')
AND p.permission_code IN ('education.notifications.view','education.notifications.manage','education.onboarding.approve');

INSERT INTO edu_school_permissions(permission_code,permission_name,module_code,description) VALUES
('school.onboarding.manage','Complete School Onboarding','onboarding','Complete SACCO-requested school profile and KYB information after account approval.')
ON DUPLICATE KEY UPDATE permission_name=VALUES(permission_name),description=VALUES(description);

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='SCHOOL_SUPER_ADMIN' AND p.permission_code='school.onboarding.manage';