Spaces:
Sleeping
Sleeping
Download sql/create_tables.sql from Anil1515/chess-baackend: direct link, hf CLI and curl.
- Browser
- Download file 5.63 kB
-
https://huggingface.co/spaces/Anil1515/chess-baackend/resolve/main/sql/create_tables.sql
- Command line
-
hf download hf://spaces/Anil1515/chess-baackend/sql/create_tables.sql
-
curl -L -o create_tables.sql https://huggingface.co/spaces/Anil1515/chess-baackend/resolve/main/sql/create_tables.sql
5.63 kB
| -- 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 | |