Allen
128 lines
3.9 KiB
SQL
128 lines
3.9 KiB
SQL
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
username VARCHAR(30) UNIQUE NOT NULL,
|
|
email VARCHAR(255) UNIQUE,
|
|
password_hash VARCHAR(255),
|
|
display_name VARCHAR(50),
|
|
is_guest BOOLEAN DEFAULT false,
|
|
device_fingerprint VARCHAR(255),
|
|
tokens_balance INTEGER DEFAULT 5,
|
|
total_tokens_earned INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
last_login TIMESTAMP,
|
|
is_banned BOOLEAN DEFAULT false,
|
|
role VARCHAR(20) DEFAULT 'player'
|
|
);
|
|
|
|
-- Games table
|
|
CREATE TABLE IF NOT EXISTS games (
|
|
id SERIAL PRIMARY KEY,
|
|
creator_id INTEGER REFERENCES users(id),
|
|
title VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
game_code TEXT NOT NULL,
|
|
thumbnail_url VARCHAR(500),
|
|
status VARCHAR(20) DEFAULT 'draft',
|
|
play_count INTEGER DEFAULT 0,
|
|
total_ratings INTEGER DEFAULT 0,
|
|
rating_sum INTEGER DEFAULT 0,
|
|
tokens_earned INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
published_at TIMESTAMP,
|
|
last_played TIMESTAMP,
|
|
is_featured BOOLEAN DEFAULT false,
|
|
rejection_reason TEXT
|
|
);
|
|
|
|
-- Play sessions
|
|
CREATE TABLE IF NOT EXISTS plays (
|
|
id SERIAL PRIMARY KEY,
|
|
player_id INTEGER REFERENCES users(id),
|
|
game_id INTEGER REFERENCES games(id),
|
|
started_at TIMESTAMP DEFAULT NOW(),
|
|
ended_at TIMESTAMP,
|
|
score INTEGER,
|
|
duration_seconds INTEGER,
|
|
token_spent BOOLEAN DEFAULT false,
|
|
device_fingerprint VARCHAR(255),
|
|
ip_address INET
|
|
);
|
|
|
|
-- High scores
|
|
CREATE TABLE IF NOT EXISTS high_scores (
|
|
id SERIAL PRIMARY KEY,
|
|
game_id INTEGER REFERENCES games(id),
|
|
player_id INTEGER REFERENCES users(id),
|
|
score INTEGER NOT NULL,
|
|
achieved_at TIMESTAMP DEFAULT NOW(),
|
|
UNIQUE(game_id, player_id)
|
|
);
|
|
|
|
-- Developer earnings
|
|
CREATE TABLE IF NOT EXISTS developer_earnings (
|
|
id SERIAL PRIMARY KEY,
|
|
developer_id INTEGER REFERENCES users(id),
|
|
game_id INTEGER REFERENCES games(id),
|
|
tokens_earned INTEGER NOT NULL,
|
|
play_id INTEGER REFERENCES plays(id),
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Guest sessions (for fingerprint tracking)
|
|
CREATE TABLE IF NOT EXISTS guest_sessions (
|
|
id SERIAL PRIMARY KEY,
|
|
device_fingerprint VARCHAR(255) NOT NULL,
|
|
user_agent TEXT,
|
|
ip_address INET,
|
|
tokens_granted_today INTEGER DEFAULT 5,
|
|
last_token_grant DATE DEFAULT CURRENT_DATE,
|
|
total_plays INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
last_seen TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Game reports (moderation)
|
|
CREATE TABLE IF NOT EXISTS game_reports (
|
|
id SERIAL PRIMARY KEY,
|
|
game_id INTEGER REFERENCES games(id),
|
|
reporter_id INTEGER REFERENCES users(id),
|
|
reason VARCHAR(50),
|
|
description TEXT,
|
|
status VARCHAR(20) DEFAULT 'pending',
|
|
reviewed_by INTEGER REFERENCES users(id),
|
|
reviewed_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Suspicious activity log
|
|
CREATE TABLE IF NOT EXISTS suspicious_activity_log (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id),
|
|
activity_type VARCHAR(50),
|
|
details JSONB,
|
|
ip_address INET,
|
|
device_fingerprint VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Ratings table (to track one rating per user per game)
|
|
CREATE TABLE IF NOT EXISTS ratings (
|
|
id SERIAL PRIMARY KEY,
|
|
game_id INTEGER REFERENCES games(id),
|
|
user_id INTEGER REFERENCES users(id),
|
|
rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
UNIQUE(game_id, user_id)
|
|
);
|
|
|
|
-- Create indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_games_status ON games(status);
|
|
CREATE INDEX IF NOT EXISTS idx_games_creator ON games(creator_id);
|
|
CREATE INDEX IF NOT EXISTS idx_plays_player ON plays(player_id);
|
|
CREATE INDEX IF NOT EXISTS idx_plays_game ON plays(game_id);
|
|
CREATE INDEX IF NOT EXISTS idx_high_scores_game ON high_scores(game_id);
|
|
CREATE INDEX IF NOT EXISTS idx_guest_fingerprint ON guest_sessions(device_fingerprint);
|
|
CREATE INDEX IF NOT EXISTS idx_games_published ON games(published_at) WHERE status = 'published';
|
|
CREATE INDEX IF NOT EXISTS idx_games_play_count ON games(play_count DESC) WHERE status = 'published';
|