-- TELSPAY Education v2026.9.5.1
-- Student Login Access / secure credential resend module
-- Additive migration. No existing student password or transaction PIN is exposed or copied.

CREATE TABLE IF NOT EXISTS edu_student_login_access_tokens (
  access_token_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  token_ref VARCHAR(70) NOT NULL,
  wallet_id BIGINT UNSIGNED NOT NULL,
  student_id BIGINT UNSIGNED NOT NULL,
  school_id BIGINT UNSIGNED NOT NULL,
  purpose ENUM('LOGIN_SETUP','PASSWORD_RESET') NOT NULL DEFAULT 'PASSWORD_RESET',
  token_hash CHAR(64) NOT NULL,
  token_status ENUM('ACTIVE','USED','EXPIRED','REVOKED') NOT NULL DEFAULT 'ACTIVE',
  expires_at DATETIME NOT NULL,
  requested_by_admin_id BIGINT DEFAULT NULL,
  delivery_email VARCHAR(190) DEFAULT NULL,
  delivery_phone VARCHAR(60) DEFAULT NULL,
  email_notification_id BIGINT UNSIGNED DEFAULT NULL,
  sms_notification_id BIGINT UNSIGNED DEFAULT NULL,
  request_ip_hash CHAR(64) DEFAULT NULL,
  used_at DATETIME DEFAULT NULL,
  used_ip_hash CHAR(64) 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(access_token_id),
  UNIQUE KEY uq_edu_student_login_token_ref(token_ref),
  UNIQUE KEY uq_edu_student_login_token_hash(token_hash),
  KEY idx_edu_student_login_wallet(wallet_id,token_status,created_at),
  KEY idx_edu_student_login_expiry(token_status,expires_at),
  KEY idx_edu_student_login_admin(requested_by_admin_id,created_at),
  CONSTRAINT fk_edu_student_login_wallet FOREIGN KEY(wallet_id) REFERENCES edu_student_wallet_accounts(wallet_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_student_login_student FOREIGN KEY(student_id) REFERENCES edu_students(student_id) ON DELETE CASCADE,
  CONSTRAINT fk_edu_student_login_school FOREIGN KEY(school_id) REFERENCES edu_schools(school_id) ON DELETE CASCADE
) 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.student_wallet.credentials','Resend Student Login Access','education','HIGH','Issue a single-use Student Portal password setup/reset link and resend the Student Wallet login identifier by approved communication channels.',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='education.student_wallet.credentials';