-- 管理员后台 / TOTP / 日活 / AI 用量 / 发版 / stepup
-- 可重复执行（列/表不存在才添加）

USE code_count;

-- users 扩展列
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='totp_secret')=0,
    'ALTER TABLE users ADD COLUMN totp_secret VARCHAR(64) NOT NULL DEFAULT '''' COMMENT ''TOTP Base32 密钥（仅管理员）'' AFTER password_hash', 'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='totp_enabled')=0,
    'ALTER TABLE users ADD COLUMN totp_enabled TINYINT NOT NULL DEFAULT 0 COMMENT ''是否已启用 TOTP'' AFTER totp_secret', 'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='last_login_ip')=0,
    'ALTER TABLE users ADD COLUMN last_login_ip VARCHAR(64) NOT NULL DEFAULT '''' COMMENT ''最近登录 IP'' AFTER totp_enabled', 'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='last_seen_at')=0,
    'ALTER TABLE users ADD COLUMN last_seen_at VARCHAR(32) NOT NULL DEFAULT '''' COMMENT ''最近活跃时间 RFC3339'' AFTER last_login_ip', 'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='ai_banned')=0,
    'ALTER TABLE users ADD COLUMN ai_banned TINYINT NOT NULL DEFAULT 0 COMMENT ''禁止使用 AI'' AFTER last_seen_at', 'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='users' AND COLUMN_NAME='disabled')=0,
    'ALTER TABLE users ADD COLUMN disabled TINYINT NOT NULL DEFAULT 0 COMMENT ''禁用账号'' AFTER ai_banned', 'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

-- teams.ai_banned
SET @sql = IF((SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA='code_count' AND TABLE_NAME='teams' AND COLUMN_NAME='ai_banned')=0,
    'ALTER TABLE teams ADD COLUMN ai_banned TINYINT NOT NULL DEFAULT 0 COMMENT ''禁止团队使用 AI'' AFTER digest_time', 'SELECT 1');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

CREATE TABLE IF NOT EXISTS user_daily_active(
    user_id BIGINT NOT NULL COMMENT '用户ID',
    active_date CHAR(10) NOT NULL COMMENT '活跃日期 YYYY-MM-DD',
    last_ip VARCHAR(64) NOT NULL DEFAULT '' COMMENT '当日最近 IP',
    last_seen_at VARCHAR(32) NOT NULL DEFAULT '' COMMENT '当日最近活跃时间',
    PRIMARY KEY(user_id, active_date),
    KEY idx_uda_date(active_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户日活';

CREATE TABLE IF NOT EXISTS ai_usage_daily(
    user_id BIGINT NOT NULL COMMENT '用户ID',
    team_id BIGINT NOT NULL DEFAULT 0 COMMENT '团队ID，0=个人',
    usage_date CHAR(10) NOT NULL COMMENT '用量日期 YYYY-MM-DD',
    provider VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'AI 提供商',
    prompt_tokens BIGINT NOT NULL DEFAULT 0 COMMENT '提示 token',
    completion_tokens BIGINT NOT NULL DEFAULT 0 COMMENT '补全 token',
    calls INT NOT NULL DEFAULT 0 COMMENT '调用次数',
    estimated TINYINT NOT NULL DEFAULT 0 COMMENT '是否含估算数据',
    PRIMARY KEY(user_id, team_id, usage_date, provider),
    KEY idx_aud_date(usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI Token 日汇总';

CREATE TABLE IF NOT EXISTS app_releases(
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '发版ID',
    version VARCHAR(32) NOT NULL COMMENT '版本号 semver',
    channel VARCHAR(16) NOT NULL DEFAULT 'stable' COMMENT '渠道',
    filename VARCHAR(255) NOT NULL COMMENT '存储相对路径',
    sha256 CHAR(64) NOT NULL DEFAULT '' COMMENT '文件 SHA256',
    size_bytes BIGINT NOT NULL DEFAULT 0 COMMENT '字节大小',
    changelog MEDIUMTEXT NOT NULL COMMENT '更新说明',
    created_at VARCHAR(32) NOT NULL COMMENT '上传时间',
    is_latest TINYINT NOT NULL DEFAULT 0 COMMENT '是否当前最新',
    UNIQUE KEY idx_app_releases_ver_ch(version, channel),
    KEY idx_app_releases_latest(channel, is_latest)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户端发版';

CREATE TABLE IF NOT EXISTS admin_stepup(
    user_id BIGINT PRIMARY KEY COMMENT '管理员用户ID',
    token_hash CHAR(64) NOT NULL COMMENT 'stepup token SHA256',
    client_ip VARCHAR(64) NOT NULL DEFAULT '' COMMENT '签发时 IP',
    expires_at VARCHAR(32) NOT NULL COMMENT '过期时间 RFC3339'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员敏感操作二次验证';
