1
-- 消息映射表
2
CREATE TABLE message_mappings (
3
  id BIGSERIAL PRIMARY KEY,
4
  forwarded_message_id BIGINT UNIQUE NOT NULL,
5
  user_id BIGINT NOT NULL,
6
  username TEXT,
7
  created_at TIMESTAMPTZ DEFAULT NOW()
8
);
9

10
-- 已验证用户表
11
CREATE TABLE verified_users (
12
  id BIGSERIAL PRIMARY KEY,
13
  user_id BIGINT UNIQUE NOT NULL,
14
  username TEXT,
15
  verified_at TIMESTAMPTZ DEFAULT NOW()
16
);
17

18
-- 待验证用户表
19
CREATE TABLE pending_verifications (
20
  id BIGSERIAL PRIMARY KEY,
21
  user_id BIGINT UNIQUE NOT NULL,
22
  code TEXT NOT NULL,
23
  attempts INTEGER DEFAULT 0,
24
  expires_at TIMESTAMPTZ NOT NULL,
25
  created_at TIMESTAMPTZ DEFAULT NOW()
26
);
27

28
-- 拉黑用户表
29
CREATE TABLE blocked_users (
30
  id BIGSERIAL PRIMARY KEY,
31
  user_id BIGINT UNIQUE NOT NULL,
32
  blocked_at TIMESTAMPTZ DEFAULT NOW()
33
);
34

35
-- 创建索引
36
CREATE INDEX idx_message_mappings_forwarded_id ON message_mappings(forwarded_message_id);
37
CREATE INDEX idx_verified_users_user_id ON verified_users(user_id);
38
CREATE INDEX idx_pending_verifications_user_id ON pending_verifications(user_id);
39
CREATE INDEX idx_blocked_users_user_id ON blocked_users(user_id);