Kirubel-Kibru-Yaltopia
89440985f1
Some checks failed
Deploy to Cloudflare Workers / deploy (push) Has been cancelled
269 lines
9.6 KiB
PL/PgSQL
269 lines
9.6 KiB
PL/PgSQL
-- Yaltopia FIFA Tournament System - Initial Schema
|
|
|
|
-- Extensions
|
|
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
|
|
|
-- Enums
|
|
CREATE TYPE player_status AS ENUM ('active', 'inactive');
|
|
CREATE TYPE team_member_role AS ENUM ('manager', 'captain');
|
|
CREATE TYPE tournament_mode AS ENUM ('league', 'cup');
|
|
CREATE TYPE competition_status AS ENUM ('draft', 'active', 'playoffs', 'completed');
|
|
CREATE TYPE match_status AS ENUM (
|
|
'scheduled',
|
|
'schedule_pending',
|
|
'schedule_confirmed',
|
|
'awaiting_results',
|
|
'result_submitted',
|
|
'pending_approval',
|
|
'disputed',
|
|
'completed',
|
|
'cancelled'
|
|
);
|
|
CREATE TYPE result_status AS ENUM (
|
|
'awaiting_results',
|
|
'result_submitted',
|
|
'pending_approval',
|
|
'disputed',
|
|
'completed'
|
|
);
|
|
CREATE TYPE roster_event_type AS ENUM ('add', 'remove');
|
|
CREATE TYPE transfer_status AS ENUM ('pending', 'completed', 'voided');
|
|
CREATE TYPE signature_type AS ENUM ('schedule');
|
|
|
|
-- Profiles
|
|
CREATE TABLE profiles (
|
|
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
display_name TEXT,
|
|
avatar_url TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Leagues
|
|
CREATE TABLE leagues (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name TEXT NOT NULL,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
description TEXT,
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Versioned league rules
|
|
CREATE TABLE league_rules (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
league_id UUID NOT NULL REFERENCES leagues(id) ON DELETE CASCADE,
|
|
version INT NOT NULL,
|
|
rules JSONB NOT NULL DEFAULT '{}',
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (league_id, version)
|
|
);
|
|
|
|
-- Competitions (seasons / cups)
|
|
CREATE TABLE competitions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
league_id UUID NOT NULL REFERENCES leagues(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
tournament_mode tournament_mode NOT NULL DEFAULT 'league',
|
|
status competition_status NOT NULL DEFAULT 'draft',
|
|
timezone TEXT NOT NULL DEFAULT 'UTC',
|
|
rules_snapshot JSONB,
|
|
rule_set_version INT,
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Global player registry
|
|
CREATE TABLE players (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
display_name TEXT NOT NULL,
|
|
external_id TEXT,
|
|
status player_status NOT NULL DEFAULT 'active',
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Teams
|
|
CREATE TABLE teams (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
competition_id UUID NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
logo_path TEXT,
|
|
home_stadium_name TEXT,
|
|
cup_seed INT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Team members (manager / captain)
|
|
CREATE TABLE team_members (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
role team_member_role NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (team_id, user_id)
|
|
);
|
|
|
|
-- Team availability windows
|
|
CREATE TABLE team_availability (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
day_of_week INT NOT NULL CHECK (day_of_week >= 0 AND day_of_week <= 6),
|
|
start_time TIME,
|
|
end_time TIME,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Roster
|
|
CREATE TABLE team_roster (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE RESTRICT,
|
|
competition_id UUID NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
|
|
joined_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
left_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- Roster events
|
|
CREATE TABLE roster_events (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE RESTRICT,
|
|
competition_id UUID NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
|
|
event_type roster_event_type NOT NULL,
|
|
registered_by UUID NOT NULL REFERENCES auth.users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Transfers
|
|
CREATE TABLE transfers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
competition_id UUID NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE RESTRICT,
|
|
from_team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
to_team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
status transfer_status NOT NULL DEFAULT 'completed',
|
|
effective_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
registered_by UUID NOT NULL REFERENCES auth.users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Competition league managers
|
|
CREATE TABLE competition_league_managers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
competition_id UUID NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (competition_id, user_id)
|
|
);
|
|
|
|
-- Matches
|
|
CREATE TABLE matches (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
competition_id UUID NOT NULL REFERENCES competitions(id) ON DELETE CASCADE,
|
|
home_team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
away_team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
round INT,
|
|
matchday INT,
|
|
leg INT DEFAULT 1,
|
|
bracket_round INT,
|
|
bracket_slot INT,
|
|
next_match_id UUID REFERENCES matches(id) ON DELETE SET NULL,
|
|
status match_status NOT NULL DEFAULT 'scheduled',
|
|
result_status result_status DEFAULT 'awaiting_results',
|
|
scheduled_at TIMESTAMPTZ,
|
|
proposed_scheduled_at TIMESTAMPTZ,
|
|
venue TEXT,
|
|
home_score INT,
|
|
away_score INT,
|
|
approved_by UUID REFERENCES auth.users(id),
|
|
approved_at TIMESTAMPTZ,
|
|
resolution_note TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Schedule signatures
|
|
CREATE TABLE match_signatures (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
match_id UUID NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
signature_type signature_type NOT NULL DEFAULT 'schedule',
|
|
signed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (match_id, team_id, signature_type)
|
|
);
|
|
|
|
-- Result submissions (per team)
|
|
CREATE TABLE match_result_submissions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
match_id UUID NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
home_score INT NOT NULL,
|
|
away_score INT NOT NULL,
|
|
submitted_by UUID NOT NULL REFERENCES auth.users(id),
|
|
submitted_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (match_id, team_id)
|
|
);
|
|
|
|
-- Player match stats
|
|
CREATE TABLE match_player_stats (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
match_id UUID NOT NULL REFERENCES matches(id) ON DELETE CASCADE,
|
|
player_id UUID NOT NULL REFERENCES players(id) ON DELETE RESTRICT,
|
|
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
|
|
goals INT NOT NULL DEFAULT 0 CHECK (goals >= 0),
|
|
assists INT NOT NULL DEFAULT 0 CHECK (assists >= 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (match_id, player_id)
|
|
);
|
|
|
|
-- Indexes
|
|
CREATE INDEX idx_competitions_league ON competitions(league_id);
|
|
CREATE INDEX idx_teams_competition ON teams(competition_id);
|
|
CREATE INDEX idx_matches_competition ON matches(competition_id);
|
|
CREATE INDEX idx_matches_status ON matches(competition_id, status);
|
|
CREATE INDEX idx_team_roster_active ON team_roster(competition_id, player_id) WHERE left_at IS NULL;
|
|
CREATE INDEX idx_team_members_user ON team_members(user_id);
|
|
|
|
-- One manager per team
|
|
CREATE UNIQUE INDEX idx_one_manager_per_team ON team_members(team_id) WHERE role = 'manager';
|
|
|
|
-- One active roster slot per player per competition
|
|
CREATE UNIQUE INDEX idx_active_roster_per_player ON team_roster(competition_id, player_id) WHERE left_at IS NULL;
|
|
|
|
-- Updated_at trigger
|
|
CREATE OR REPLACE FUNCTION update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER profiles_updated_at BEFORE UPDATE ON profiles FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
CREATE TRIGGER leagues_updated_at BEFORE UPDATE ON leagues FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
CREATE TRIGGER competitions_updated_at BEFORE UPDATE ON competitions FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
CREATE TRIGGER teams_updated_at BEFORE UPDATE ON teams FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
CREATE TRIGGER players_updated_at BEFORE UPDATE ON players FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
CREATE TRIGGER matches_updated_at BEFORE UPDATE ON matches FOR EACH ROW EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- Auto-create profile on signup
|
|
CREATE OR REPLACE FUNCTION handle_new_user()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
INSERT INTO public.profiles (id, display_name)
|
|
VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'display_name', NEW.email));
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
CREATE TRIGGER on_auth_user_created
|
|
AFTER INSERT ON auth.users
|
|
FOR EACH ROW EXECUTE FUNCTION handle_new_user();
|