Spaces:
Sleeping
Sleeping
File size: 5,632 Bytes
f0c0e92 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 | -- Create tables for chess backend
-- Run this against the `chess_db` database
-- users
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
email TEXT,
first_name TEXT,
last_name TEXT,
password_hash TEXT,
profile_image TEXT,
bio TEXT,
fcm_token TEXT,
friends JSONB,
friend_requests JSONB,
profile JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_image TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS fcm_token TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS friends JSONB DEFAULT '[]'::jsonb;
ALTER TABLE users ADD COLUMN IF NOT EXISTS friend_requests JSONB DEFAULT '[]'::jsonb;
ALTER TABLE users ADD COLUMN IF NOT EXISTS profile JSONB;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_seen TIMESTAMPTZ;
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT now();
-- messages
CREATE TABLE IF NOT EXISTS messages (
id TEXT PRIMARY KEY,
sender TEXT NOT NULL,
receiver TEXT NOT NULL,
message_type TEXT,
text TEXT,
media_url TEXT,
thumbnail_url TEXT,
reply_to_id TEXT,
status TEXT DEFAULT 'sent',
is_read BOOLEAN DEFAULT FALSE,
reactions JSONB,
metadata JSONB,
timestamp TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE messages ALTER COLUMN id TYPE TEXT USING id::text;
ALTER TABLE messages ALTER COLUMN id DROP DEFAULT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS media_url TEXT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS thumbnail_url TEXT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS reply_to_id TEXT;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'sent';
ALTER TABLE messages ADD COLUMN IF NOT EXISTS is_read BOOLEAN DEFAULT FALSE;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS reactions JSONB DEFAULT '{}'::jsonb;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS metadata JSONB;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS timestamp TIMESTAMPTZ;
DROP TABLE IF EXISTS group_call_history;
DROP TABLE IF EXISTS group_messages;
DROP TABLE IF EXISTS groups;
-- groups
CREATE TABLE IF NOT EXISTS groups (
id TEXT PRIMARY KEY,
name TEXT,
avatar TEXT,
created_by TEXT,
admins JSONB,
members JSONB,
created_at TIMESTAMPTZ,
updated_at TIMESTAMPTZ,
last_message TEXT,
last_message_time TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS group_messages (
id TEXT PRIMARY KEY,
group_id TEXT NOT NULL,
sender TEXT NOT NULL,
text TEXT,
message_type TEXT,
media_url TEXT,
thumbnail_url TEXT,
timestamp TIMESTAMPTZ,
seen_by JSONB,
FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS group_call_history (
id TEXT PRIMARY KEY,
group_id TEXT NOT NULL,
started_by TEXT,
call_type TEXT,
participants JSONB,
status TEXT,
started_at TIMESTAMPTZ,
ended_at TIMESTAMPTZ,
FOREIGN KEY (group_id) REFERENCES groups (id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS stories (
id TEXT PRIMARY KEY,
username TEXT NOT NULL,
media_url TEXT,
thumbnail_url TEXT,
media_type TEXT,
timestamp TIMESTAMPTZ,
viewers JSONB,
reactions JSONB,
reaction_details JSONB
);
CREATE TABLE IF NOT EXISTS notes (
id TEXT PRIMARY KEY,
username TEXT NOT NULL,
text_content TEXT,
media_url TEXT,
thumbnail_url TEXT,
media_type TEXT,
timestamp TIMESTAMPTZ,
viewers JSONB
);
CREATE TABLE IF NOT EXISTS auth_token_blocklist (
jti TEXT PRIMARY KEY,
token_type TEXT,
revoked_at TIMESTAMPTZ,
expires_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS password_reset_tokens (
token TEXT PRIMARY KEY,
username TEXT NOT NULL,
expires_at TIMESTAMPTZ
);
CREATE TABLE IF NOT EXISTS game_rooms (
room_id TEXT PRIMARY KEY,
room_data JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS audit_events (
id BIGSERIAL PRIMARY KEY,
event_type TEXT NOT NULL,
payload JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS media_files (
id TEXT PRIMARY KEY,
category TEXT NOT NULL,
filename TEXT NOT NULL,
content_type TEXT,
data BYTEA NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- payments table for tournament/in-app purchases
CREATE TABLE IF NOT EXISTS payments (
id BIGSERIAL PRIMARY KEY,
pid TEXT UNIQUE NOT NULL,
user_id TEXT,
tournament_id TEXT,
amount NUMERIC(12,2) NOT NULL,
currency TEXT DEFAULT 'NPR',
status TEXT DEFAULT 'pending', -- pending, paid, failed
esewa_ref_id TEXT,
raw_response JSONB,
created_at TIMESTAMPTZ DEFAULT now(),
verified_at TIMESTAMPTZ
);
-- tournaments
CREATE TABLE IF NOT EXISTS tournaments (
id TEXT PRIMARY KEY,
title TEXT NOT NULL,
game_type TEXT NOT NULL, -- 'chess' or 'ludo'
entry_fee NUMERIC(12,2) DEFAULT 0,
max_players INT DEFAULT 2,
owner TEXT,
status TEXT DEFAULT 'open', -- open, closed, running, finished
metadata JSONB,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE IF NOT EXISTS tournament_participants (
id BIGSERIAL PRIMARY KEY,
tournament_id TEXT NOT NULL REFERENCES tournaments(id) ON DELETE CASCADE,
user_id TEXT NOT NULL,
status TEXT DEFAULT 'pending', -- pending, paid, joined
payment_pid TEXT,
joined_at TIMESTAMPTZ
);
-- Performance Indexes for Messaging Optimization
CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(sender, receiver, timestamp);
CREATE INDEX IF NOT EXISTS idx_messages_sync ON messages(receiver, timestamp);
CREATE INDEX IF NOT EXISTS idx_group_messages_query ON group_messages(group_id, timestamp);
-- removed non-immutable partial index
|