-- iBilik Capital Database v1.4 - Super Admin Security Center
-- Compatibility: MySQL 5.7.44+
-- IMPORTANT: Execute only after v1.3 bilingual migration.
-- 所有新增資料表及欄位均使用中文 COMMENT，方便後續維護。

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. Session 增加「由哪位超級管理員撤銷」追蹤欄位。
ALTER TABLE `auth_sessions`
  ADD COLUMN `revoked_by_super_admin_id` BIGINT(20) UNSIGNED NULL
    COMMENT '若 Session 由超級管理員強制撤銷，記錄執行撤銷的超級管理員主鍵'
    AFTER `revoke_reason`,
  ADD KEY `idx_session_revoked_by_admin` (`revoked_by_super_admin_id`),
  ADD CONSTRAINT `fk_session_revoked_by_admin`
    FOREIGN KEY (`revoked_by_super_admin_id`) REFERENCES `sys_super_admins` (`id`);

-- 2. Credential 敏感操作歷史。此表永遠不保存密碼或 MID 明文。
CREATE TABLE `auth_credential_events` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '登入憑證敏感操作事件主鍵',
  `account_type` VARCHAR(20) NOT NULL COMMENT '被操作帳戶類型 SUPER_ADMIN 或 USER',
  `account_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '被操作帳戶主鍵；依 account_type 對應超管或一般使用者',
  `event_type` VARCHAR(40) NOT NULL COMMENT '憑證事件類型，例如 PASSWORD_RESET、MID_REGENERATED、FAIL_COUNTER_RESET',
  `old_credential_version` INT(10) UNSIGNED NOT NULL COMMENT '操作前憑證版本',
  `new_credential_version` INT(10) UNSIGNED NOT NULL COMMENT '操作後憑證版本',
  `mid_mask_after` VARCHAR(20) DEFAULT NULL COMMENT '操作完成後的 MID 遮罩；禁止保存完整 MID',
  `performed_by_super_admin_id` BIGINT(20) UNSIGNED NOT NULL COMMENT '執行敏感操作的超級管理員主鍵',
  `ip_address` VARCHAR(45) DEFAULT NULL COMMENT '執行操作時的來源 IP 位址',
  `note_zh` VARCHAR(255) DEFAULT NULL COMMENT '事件中文備註，不得寫入密碼或 MID 明文',
  `note_en` VARCHAR(255) DEFAULT NULL COMMENT '事件英文備註，不得寫入密碼或 MID 明文',
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '事件建立時間',
  PRIMARY KEY (`id`),
  KEY `idx_credential_event_account` (`account_type`,`account_id`,`created_at`),
  KEY `idx_credential_event_admin` (`performed_by_super_admin_id`,`created_at`),
  CONSTRAINT `fk_credential_event_admin`
    FOREIGN KEY (`performed_by_super_admin_id`) REFERENCES `sys_super_admins` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='登入憑證敏感操作歷史；追蹤密碼重設、MID 重發及失敗計數清除，永不保存明文憑證';

-- 3. 補充安全管理權限目錄，供未來需要時控制可見範圍。
INSERT INTO `permission_catalog`
(`permission_code`,`module_code`,`permission_type`,`name_zh`,`name_en`,`description_zh`,`description_en`,`active_flag`,`sort_order`)
VALUES
('SECURITY_AUDIT_VIEW','SECURITY','REPORT','查看安全稽核','View Security Audit','允許查看登入、Session、IP Lock 與安全事件稽核資訊','Allows viewing login, session, IP lock and security audit events',1,40),
('SECURITY_SESSION_REVOKE','SECURITY','ACTION','強制撤銷登入 Session','Force Revoke Sessions','允許撤銷指定一般帳戶的所有有效 Session；實際執行仍限超級管理員','Allows revoking all active sessions for a selected user; execution remains Super Admin only',1,50),
('SECURITY_IP_UNLOCK','SECURITY','ACTION','解除 IP 凍結','Release IP Lock','允許人工提前解除被登入風控凍結的來源 IP；實際執行仍限超級管理員','Allows early release of an IP locked by login risk controls; execution remains Super Admin only',1,60),
('SECURITY_CREDENTIAL_RESET','SECURITY','ACTION','重設帳戶登入憑證','Reset Account Credentials','允許重設一般使用者密碼或重新產生 MID；實際執行仍限超級管理員','Allows resetting a user password or regenerating MID; execution remains Super Admin only',1,70)
ON DUPLICATE KEY UPDATE
  `name_zh` = VALUES(`name_zh`),
  `name_en` = VALUES(`name_en`),
  `description_zh` = VALUES(`description_zh`),
  `description_en` = VALUES(`description_en`),
  `active_flag` = 1,
  `sort_order` = VALUES(`sort_order`);

SET FOREIGN_KEY_CHECKS = 1;

-- Verification
SELECT 'auth_credential_events' AS check_item, COUNT(*) AS record_count FROM auth_credential_events;
SHOW COLUMNS FROM auth_sessions LIKE 'revoked_by_super_admin_id';
SELECT permission_code, name_zh, name_en FROM permission_catalog
WHERE permission_code IN ('SECURITY_AUDIT_VIEW','SECURITY_SESSION_REVOKE','SECURITY_IP_UNLOCK','SECURITY_CREDENTIAL_RESET')
ORDER BY sort_order;
