-- TELSPAY EDUCATION / MEMBER 360 / GLOBAL DOCUMENT AUTHENTICITY v2026.9.3
-- Additive migration. Run after v2026.9.0, v2026.9.1 and v2026.9.2.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS telspay_document_verifications (
  verification_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  public_ref CHAR(32) NOT NULL,
  document_scope VARCHAR(40) NOT NULL DEFAULT 'GENERAL',
  document_type VARCHAR(100) NOT NULL,
  document_title VARCHAR(220) NOT NULL,
  entity_type VARCHAR(80) DEFAULT NULL,
  entity_id BIGINT DEFAULT NULL,
  member_id INT DEFAULT NULL,
  school_id BIGINT UNSIGNED 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 VARCHAR(40) 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_telspay_doc_public_ref (public_ref),
  KEY idx_telspay_doc_member (member_id,issued_at),
  KEY idx_telspay_doc_school (school_id,issued_at),
  KEY idx_telspay_doc_student (student_id,issued_at),
  KEY idx_telspay_doc_entity (entity_type,entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE edu_schools
  ADD COLUMN IF NOT EXISTS document_header_note VARCHAR(300) DEFAULT NULL AFTER profile_summary,
  ADD COLUMN IF NOT EXISTS document_footer_note VARCHAR(500) DEFAULT NULL AFTER document_header_note;

-- Expand education-document registry for the new profile/report documents.
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'
  ) NOT NULL;

INSERT IGNORE INTO edu_school_permissions(permission_code,permission_name,module_code,description) VALUES
('school.reports.view','View school reports','reports','View student, admission, payment and document reports linked to the school profile.'),
('school.reports.issue','Issue QR-verifiable school reports','reports','Issue school-branded QR-verifiable reports where the subscription allows documents.');

INSERT IGNORE INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.member360.view','View member education 360 profile','education','HIGH','View dependants, admissions, education transactions, loans and document reports attached to a SACCO member.',UTC_TIMESTAMP());




-- Allow selected school roles to issue the new branded QR-verifiable reports.
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.reports.issue'
WHERE r.role_code IN ('SCHOOL_SUPER_ADMIN','SCHOOL_REGISTRAR','SCHOOL_BURSAR','SCHOOL_HEAD_TEACHER','SCHOOL_FINANCE_MANAGER');

-- Grant Member 360 visibility to controlled education and audit roles.
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='education.member360.view'
WHERE r.role_code IN ('SUPER_ADMIN','EDUCATION_ADMIN','EDUCATION_CREDIT_OFFICER','AUDITOR_READ_ONLY');

CREATE TABLE IF NOT EXISTS telspay_module_versions (
  module_code VARCHAR(80) NOT NULL,
  version VARCHAR(40) NOT NULL,
  description VARCHAR(500) DEFAULT NULL,
  installed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(module_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO telspay_module_versions(module_code,version,description,installed_at)
VALUES('TELSPAY_EDUCATION_PROFILE_DOCUMENTS','2026.9.3','System-wide QR document authenticity, school document branding, member 360 profile, dependants and education reports',UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE version=VALUES(version),description=VALUES(description),installed_at=VALUES(installed_at);