-- 吉数彩研院 - 用户操作日志表（超级详细版）
-- 执行方式：Supabase 项目 → SQL Editor → New query → 粘贴 → Run

CREATE TABLE IF NOT EXISTS user_logs (
    id SERIAL PRIMARY KEY,
    username TEXT,
    action TEXT,
    detail TEXT,
    game TEXT,
    ip TEXT,
    session_id TEXT,
    user_agent TEXT,
    browser TEXT,
    os TEXT,
    device TEXT,
    screen_resolution TEXT,
    language TEXT,
    referrer TEXT,
    page_url TEXT,
    country TEXT,
    city TEXT,
    region TEXT,
    isp TEXT,
    fingerprint TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_user_logs_created_at ON user_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_user_logs_username ON user_logs(username);
CREATE INDEX IF NOT EXISTS idx_user_logs_action ON user_logs(action);
CREATE INDEX IF NOT EXISTS idx_user_logs_game ON user_logs(game);
CREATE INDEX IF NOT EXISTS idx_user_logs_session_id ON user_logs(session_id);

-- ============================================
-- 已有旧表升级迁移（如果 user_logs 已存在）
-- 取消下面注释并执行即可添加缺失字段：
-- ============================================
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS session_id TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS user_agent TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS browser TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS os TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS device TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS screen_resolution TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS language TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS referrer TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS page_url TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS country TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS city TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS region TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS isp TEXT;
-- ALTER TABLE user_logs ADD COLUMN IF NOT EXISTS fingerprint TEXT;
