55 lines
2.0 KiB
MySQL
55 lines
2.0 KiB
MySQL
|
-- 添加用户封禁字段
|
|||
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_banned BOOLEAN NOT NULL DEFAULT FALSE;
|
|||
|
|
|
|||
|
|
-- 添加房间邀请码
|
|||
|
|
ALTER TABLE rooms ADD COLUMN IF NOT EXISTS invite_code VARCHAR(8);
|
|||
|
|
|
|||
|
|
-- 添加用户最后在线 IP（用于安全审计）
|
|||
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_ip VARCHAR(45);
|
|||
|
|
|
|||
|
|
-- 创建房间邀请表
|
|||
|
|
CREATE TABLE IF NOT EXISTS room_invites (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|||
|
|
room_id UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
|
|||
|
|
inviter_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
invitee_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|||
|
|
status VARCHAR(16) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'accepted', 'declined', 'expired')),
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|||
|
|
expires_at TIMESTAMPTZ NOT NULL DEFAULT NOW() + INTERVAL '24 hours'
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 创建服务器配置表（持久化配置）
|
|||
|
|
CREATE TABLE IF NOT EXISTS server_config (
|
|||
|
|
key VARCHAR(64) PRIMARY KEY,
|
|||
|
|
value TEXT NOT NULL,
|
|||
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 创建操作日志表（管理审计）
|
|||
|
|
CREATE TABLE IF NOT EXISTS admin_logs (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|||
|
|
admin_user VARCHAR(64) NOT NULL,
|
|||
|
|
action VARCHAR(64) NOT NULL,
|
|||
|
|
target_type VARCHAR(32),
|
|||
|
|
target_id UUID,
|
|||
|
|
details JSONB,
|
|||
|
|
ip_address VARCHAR(45),
|
|||
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 创建下载统计表
|
|||
|
|
CREATE TABLE IF NOT EXISTS download_stats (
|
|||
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|||
|
|
filename VARCHAR(255) NOT NULL,
|
|||
|
|
platform VARCHAR(32) NOT NULL,
|
|||
|
|
ip_address VARCHAR(45),
|
|||
|
|
user_agent TEXT,
|
|||
|
|
downloaded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|||
|
|
);
|
|||
|
|
|
|||
|
|
-- 索引
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_room_invites_invitee ON room_invites(invitee_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_room_invites_room ON room_invites(room_id);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_admin_logs_created ON admin_logs(created_at DESC);
|
|||
|
|
CREATE INDEX IF NOT EXISTS idx_download_stats_filename ON download_stats(filename);
|