-- sys_user 增加 MFA 相关字段
-- 用途：支持账号级多因子登录授权、绑定和校验
-- 适用数据库：MySQL

SET @current_db := DATABASE();

SET @mfa_allow_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @current_db
    AND TABLE_NAME = 'sys_user'
    AND COLUMN_NAME = 'mfa_allow'
);

SET @add_mfa_allow_sql := IF(
  @mfa_allow_exists = 0,
  'ALTER TABLE sys_user ADD COLUMN mfa_allow INT NOT NULL DEFAULT 0 COMMENT ''是否允许使用MFA'' AFTER password',
  'SELECT ''column mfa_allow already exists'' '
);
PREPARE stmt_mfa_allow FROM @add_mfa_allow_sql;
EXECUTE stmt_mfa_allow;
DEALLOCATE PREPARE stmt_mfa_allow;

SET @mfa_enabled_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @current_db
    AND TABLE_NAME = 'sys_user'
    AND COLUMN_NAME = 'mfa_enabled'
);

SET @add_mfa_enabled_sql := IF(
  @mfa_enabled_exists = 0,
  'ALTER TABLE sys_user ADD COLUMN mfa_enabled INT NOT NULL DEFAULT 0 COMMENT ''是否已启用MFA'' AFTER mfa_allow',
  'SELECT ''column mfa_enabled already exists'' '
);
PREPARE stmt_mfa_enabled FROM @add_mfa_enabled_sql;
EXECUTE stmt_mfa_enabled;
DEALLOCATE PREPARE stmt_mfa_enabled;

SET @mfa_secret_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @current_db
    AND TABLE_NAME = 'sys_user'
    AND COLUMN_NAME = 'mfa_secret'
);

SET @add_mfa_secret_sql := IF(
  @mfa_secret_exists = 0,
  'ALTER TABLE sys_user ADD COLUMN mfa_secret VARCHAR(128) NULL COMMENT ''MFA密钥'' AFTER mfa_enabled',
  'SELECT ''column mfa_secret already exists'' '
);
PREPARE stmt_mfa_secret FROM @add_mfa_secret_sql;
EXECUTE stmt_mfa_secret;
DEALLOCATE PREPARE stmt_mfa_secret;

SET @mfa_bound_time_exists := (
  SELECT COUNT(1)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @current_db
    AND TABLE_NAME = 'sys_user'
    AND COLUMN_NAME = 'mfa_bound_time'
);

SET @add_mfa_bound_time_sql := IF(
  @mfa_bound_time_exists = 0,
  'ALTER TABLE sys_user ADD COLUMN mfa_bound_time DATETIME NULL COMMENT ''MFA绑定时间'' AFTER mfa_secret',
  'SELECT ''column mfa_bound_time already exists'' '
);
PREPARE stmt_mfa_bound_time FROM @add_mfa_bound_time_sql;
EXECUTE stmt_mfa_bound_time;
DEALLOCATE PREPARE stmt_mfa_bound_time;

SHOW COLUMNS FROM sys_user LIKE 'mfa_allow';
SHOW COLUMNS FROM sys_user LIKE 'mfa_enabled';
SHOW COLUMNS FROM sys_user LIKE 'mfa_secret';
SHOW COLUMNS FROM sys_user LIKE 'mfa_bound_time';