120 lines
4.6 KiB
SQL
120 lines
4.6 KiB
SQL
-- VRBattles Initial Schema Migration
|
|
-- Converted from MySQL to PostgreSQL
|
|
-- Compatible with existing vr_battles_tables database
|
|
|
|
-- Users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
date_registered TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
firstname VARCHAR(100) NOT NULL,
|
|
lastname VARCHAR(100) NOT NULL,
|
|
username VARCHAR(50) NOT NULL,
|
|
email VARCHAR(100) NOT NULL,
|
|
password VARCHAR(255) NOT NULL, -- Supports both MD5 (legacy) and Argon2
|
|
is_admin BOOLEAN NOT NULL DEFAULT FALSE,
|
|
profile VARCHAR(255),
|
|
password_reset_token VARCHAR(255)
|
|
);
|
|
|
|
-- Index for faster lookups (unique constraints added separately for migration flexibility)
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
|
|
-- Teams table
|
|
CREATE TABLE IF NOT EXISTS teams (
|
|
id SERIAL PRIMARY KEY,
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
name VARCHAR(255) NOT NULL,
|
|
logo TEXT NOT NULL DEFAULT '',
|
|
bio TEXT,
|
|
rank INTEGER NOT NULL DEFAULT 0,
|
|
mmr REAL NOT NULL DEFAULT 1000.0,
|
|
is_delete BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_teams_name ON teams(name);
|
|
CREATE INDEX IF NOT EXISTS idx_teams_rank ON teams(rank);
|
|
|
|
-- Team players (membership) table
|
|
CREATE TABLE IF NOT EXISTS team_players (
|
|
id SERIAL PRIMARY KEY,
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
player_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
position VARCHAR(20) NOT NULL DEFAULT 'member',
|
|
status INTEGER NOT NULL DEFAULT 0,
|
|
UNIQUE(team_id, player_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_team_players_team ON team_players(team_id);
|
|
CREATE INDEX IF NOT EXISTS idx_team_players_player ON team_players(player_id);
|
|
|
|
-- Ladders table
|
|
CREATE TABLE IF NOT EXISTS ladders (
|
|
id SERIAL PRIMARY KEY,
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
date_expiration VARCHAR(255) NOT NULL DEFAULT '',
|
|
date_start VARCHAR(255) NOT NULL DEFAULT '',
|
|
created_by_id INTEGER NOT NULL DEFAULT 0,
|
|
name VARCHAR(255) NOT NULL,
|
|
status INTEGER NOT NULL DEFAULT 0,
|
|
logo VARCHAR(255)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ladders_name ON ladders(name);
|
|
CREATE INDEX IF NOT EXISTS idx_ladders_status ON ladders(status);
|
|
|
|
-- Ladder teams (teams enrolled in ladders)
|
|
CREATE TABLE IF NOT EXISTS ladder_teams (
|
|
id SERIAL PRIMARY KEY,
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
ladder_id INTEGER NOT NULL REFERENCES ladders(id) ON DELETE CASCADE,
|
|
team_id INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
UNIQUE(ladder_id, team_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ladder_teams_ladder ON ladder_teams(ladder_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ladder_teams_team ON ladder_teams(team_id);
|
|
|
|
-- Matches table
|
|
CREATE TABLE IF NOT EXISTS matches (
|
|
id SERIAL PRIMARY KEY,
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
date_start TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
created_by_id INTEGER NOT NULL DEFAULT 0,
|
|
challenge_date TIMESTAMPTZ,
|
|
team_id_1 INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
team_id_2 INTEGER NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
team_1_status INTEGER NOT NULL DEFAULT 0, -- 0=challenging, 1=pending, 2=challenged, 3=rejected, 4=accepted
|
|
team_2_status INTEGER NOT NULL DEFAULT 0,
|
|
matche_status INTEGER NOT NULL DEFAULT 0 -- 0=pending, 1=scheduled, 2=in-progress, 3=done, 4=cancelled
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_matches_team1 ON matches(team_id_1);
|
|
CREATE INDEX IF NOT EXISTS idx_matches_team2 ON matches(team_id_2);
|
|
CREATE INDEX IF NOT EXISTS idx_matches_status ON matches(matche_status);
|
|
|
|
-- Match rounds table
|
|
CREATE TABLE IF NOT EXISTS match_rounds (
|
|
id SERIAL PRIMARY KEY,
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
match_id INTEGER NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
|
|
team_1_score INTEGER NOT NULL DEFAULT 0,
|
|
team_2_score INTEGER NOT NULL DEFAULT 0,
|
|
score_posted_by_team_id INTEGER NOT NULL,
|
|
score_get_by_teamid INTEGER,
|
|
score_acceptance_status INTEGER NOT NULL DEFAULT 0 -- 0=pending, 1=accepted, 2=rejected
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_match_rounds_match ON match_rounds(match_id);
|
|
|
|
-- Featured players table
|
|
CREATE TABLE IF NOT EXISTS featured_players (
|
|
id SERIAL PRIMARY KEY,
|
|
date_created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
player_id INTEGER NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
rank INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_featured_players_rank ON featured_players(rank);
|