78 lines
2.5 KiB
SQL
78 lines
2.5 KiB
SQL
-- +goose Up
|
|
-- create initial schema
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY ,
|
|
name TEXT NOT NULL,
|
|
email TEXT NOT NULL,
|
|
bio TEXT NOT NULL default '',
|
|
profile_photo TEXT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TYPE auth_type AS ENUM ('discord', 'google', 'password');
|
|
CREATE TABLE IF NOT EXISTS auth_method (
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
user_id UUID REFERENCES users(id),
|
|
type auth_type NOT NULL,
|
|
value TEXT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- BRACKET MANAGEMENT
|
|
CREATE TABLE IF NOT EXISTS event(
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
name TEXT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TYPE entrant_type AS ENUM ('single', 'team');
|
|
CREATE TABLE IF NOT EXISTS entrant(
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
name TEXT NOT NULL,
|
|
event_id UUID REFERENCES event(id),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS team(
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
name TEXT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS team_entrant(
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
team_id UUID REFERENCES team(id),
|
|
entrant_id UUID REFERENCES entrant(id),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS bracket(
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
name TEXT NOT NULL,
|
|
event_id UUID REFERENCES event(id),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE TABLE IF NOT EXISTS bracket_entrant(
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
seed_order INTEGER NOT NULL,
|
|
entrant_id UUID REFERENCES entrant(id),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS match(
|
|
id UUID PRIMARY KEY default uuidv7(),
|
|
player1 UUID REFERENCES entrant(id),
|
|
player2 UUID REFERENCES entrant(id),
|
|
player1_from UUID REFERENCES match(id) NULL,
|
|
player2_from UUID references match(id) NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- +goose Down
|
|
-- Nothing this is the first migration
|