19 lines
981 B
SQL
19 lines
981 B
SQL
-- Spectator predictions and tournament pick'ems.
|
|
CREATE TABLE IF NOT EXISTS prediction (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id text NOT NULL,
|
|
match_id uuid REFERENCES match(id) ON DELETE CASCADE,
|
|
tournament_match_id uuid REFERENCES tournament_match(id) ON DELETE CASCADE,
|
|
winner_team_id uuid NOT NULL REFERENCES team(id) ON DELETE CASCADE,
|
|
winner_score integer,
|
|
loser_score integer,
|
|
points integer,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT prediction_one_subject CHECK ((match_id IS NULL) <> (tournament_match_id IS NULL))
|
|
);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_prediction_user_match ON prediction (user_id, match_id);
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_prediction_user_tmatch ON prediction (user_id, tournament_match_id);
|
|
CREATE INDEX IF NOT EXISTS idx_prediction_match ON prediction (match_id);
|
|
CREATE INDEX IF NOT EXISTS idx_prediction_tmatch ON prediction (tournament_match_id);
|