-- TELSPAY Education v2026.9.5.2 — last-mile school/student/member finance wiring
-- Additive migration. No DROP/TRUNCATE/bulk DELETE operations.

CREATE TABLE IF NOT EXISTS edu_member_education_otps (
  otp_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id INT NOT NULL,
  purpose ENUM('SAVINGS_TO_STUDENT_WALLET','SAVINGS_SCHOOL_FEES') NOT NULL,
  otp_hash CHAR(64) NOT NULL,
  otp_nonce CHAR(32) NOT NULL,
  attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  expires_at DATETIME NOT NULL,
  consumed_at DATETIME DEFAULT NULL,
  invalidated_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(otp_id),
  KEY idx_edu_member_otp(member_id,purpose,created_at),
  KEY idx_edu_member_otp_expiry(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO edu_finance_settings(setting_key,setting_value,value_type,description) VALUES
('member_savings_schoolfees_fee_type','FIXED','ENUM','Member service fee mode for paying school fees from SACCO Savings.'),
('member_savings_schoolfees_fee_value','0','DECIMAL','Member service fee charged when school fees are paid from SACCO Savings.'),
('member_savings_student_wallet_fee_type','FIXED','ENUM','Member service fee mode for Savings to linked Student Wallet transfers.'),
('member_savings_student_wallet_fee_value','0','DECIMAL','Member service fee charged for Savings to linked Student Wallet transfers.')
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);

CREATE TABLE IF NOT EXISTS edu_member_education_fee_ledger (
  fee_ledger_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  member_id INT NOT NULL,
  transaction_ref VARCHAR(90) NOT NULL,
  fee_type ENUM('SCHOOL_FEES_FROM_SAVINGS','STUDENT_WALLET_FROM_SAVINGS') NOT NULL,
  base_amount DECIMAL(15,2) NOT NULL,
  fee_amount DECIMAL(15,2) NOT NULL,
  fee_mode ENUM('PERCENT','FIXED') NOT NULL,
  fee_value DECIMAL(15,4) NOT NULL DEFAULT 0,
  source_type VARCHAR(50) NOT NULL,
  source_id VARCHAR(100) DEFAULT NULL,
  status ENUM('POSTED','REVERSED') NOT NULL DEFAULT 'POSTED',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(fee_ledger_id),
  UNIQUE KEY uq_edu_member_fee_ref(transaction_ref),
  KEY idx_edu_member_fee_member(member_id,created_at)
) 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','CASH','SCHOOL_FEES_LOAN','MANUAL_RECONCILIATION') NOT NULL,
  ADD COLUMN IF NOT EXISTS member_service_fee DECIMAL(15,2) NOT NULL DEFAULT 0.00 AFTER commission_amount;

CREATE TABLE IF NOT EXISTS edu_school_fee_manual_evidence (
  manual_evidence_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payment_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  payment_method ENUM('BANK','CASH') NOT NULL,
  external_reference VARCHAR(180) NOT NULL,
  evidence_path VARCHAR(500) DEFAULT NULL,
  evidence_sha256 CHAR(64) DEFAULT NULL,
  evidence_mime VARCHAR(100) DEFAULT NULL,
  review_status ENUM('PENDING','APPROVED','REJECTED','REVOKED') NOT NULL DEFAULT 'PENDING',
  submitted_by_school_user BIGINT UNSIGNED NOT NULL,
  reviewed_by_admin_id INT DEFAULT NULL,
  review_note VARCHAR(1000) DEFAULT NULL,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  reviewed_at DATETIME DEFAULT NULL,
  PRIMARY KEY(manual_evidence_id),
  UNIQUE KEY uq_edu_manual_fee_payment(payment_id),
  KEY idx_edu_manual_fee_school(school_id,review_status,submitted_at),
  CONSTRAINT fk_edu_manual_fee_payment FOREIGN KEY(payment_id) REFERENCES edu_school_fee_payments(payment_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_manual_fee_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE RESTRICT,
  CONSTRAINT fk_edu_manual_fee_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO admin_cc_permissions(permission_code,permission_name,module_code,risk_level,description,created_at) VALUES
('education.schoolfees.reconcile','Reconcile Manual School Fees','education','CRITICAL','Approve or reject school-submitted Bank/Cash school-fee evidence.',UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE permission_name=VALUES(permission_name),risk_level=VALUES(risk_level),description=VALUES(description);