-- +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