path: root/src/db/schema.sql

\set database_name teamdraft
\set schema :database_name
\set owner :database_name
\set rw_name :database_name '_website'
\set ro_name :database_name '_ro'


DROP ROLE IF EXISTS ddl;

CREATE ROLE ddl WITH NOLOGIN;

DROP ROLE IF EXISTS dml;

CREATE ROLE dml WITH NOLOGIN;

DROP ROLE IF EXISTS READ_ONLY;

CREATE ROLE READ_ONLY WITH NOLOGIN;

DROP SCHEMA IF EXISTS :schema CASCADE;

DROP DATABASE IF EXISTS :database_name;

DROP ROLE IF EXISTS :owner;

CREATE ROLE :owner WITH LOGIN PASSWORD :'owner';

GRANT ddl TO :owner;

DROP ROLE IF EXISTS :rw_name;

CREATE ROLE :rw_name WITH LOGIN PASSWORD :'rw_name';

GRANT dml TO :rw_name;

DROP ROLE IF EXISTS :ro_name;

CREATE ROLE :ro_name WITH LOGIN PASSWORD :'ro_name';

GRANT READ_ONLY TO :ro_name;

ALTER ROLE :owner SET se arch_path TO :schema, pgsodium, public;

ALTER ROLE :rw_name SET search_path TO :schema, pgsodium, public;

ALTER ROLE :ro_name SET search_path TO :schema, pgsodium, public;

CREATE DATABASE :database_name OWNER :owner;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON DATABASE :database_name FROM PUBLIC;

GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, READ_ONLY;

\c :database_name
CREATE EXTENSION pgsodium;
GRANT EXECUTE ON ROUTINE pgsodium.randombytes_random TO :owner, :rw_name, :ro_name;
SET ROLE :owner;

CREATE SCHEMA :schema AUTHORIZATION :schema;

GRANT USAGE, CREATE ON SCHEMA :schema TO ddl;

GRANT USAGE ON SCHEMA :schema TO dml, READ_ONLY;

GRANT ALL ON SCHEMA pgsodium TO dml;

REVOKE ALL ON DATABASE :database_name FROM PUBLIC;

GRANT CONNECT, TEMPORARY ON DATABASE :database_name TO ddl, dml, READ_ONLY;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT
SELECT
, INSERT, UPDATE, DELETE ON TABLES TO dml;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT
SELECT
    ON TABLES TO READ_ONLY;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT USAGE,
SELECT
    ON SEQUENCES TO dml, READ_ONLY;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT UPDATE ON SEQUENCES TO dml;

ALTER DEFAULT PRIVILEGES IN SCHEMA :schema GRANT EXECUTE ON ROUTINES TO dml, READ_ONLY;


CREATE TABLE :schema.users(
    user_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    username text,
    PASSWORD text,
    name text,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT users_pk PRIMARY KEY (user_id)
);

CREATE TABLE sessions(
    session_id text PRIMARY KEY DEFAULT to_hex(pgsodium.randombytes_random()),
    user_id integer NOT NULL,
    expires timestamptz NOT NULL DEFAULT now() + interval '1 year',
    created timestamptz NOT NULL DEFAULT now(),
    modified timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT sessions_users_fk FOREIGN KEY (user_id) REFERENCES users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TYPE :schema.divisions AS ENUM(
    'nfcn',
    'nfce',
    'nfcs',
    'nfcw',
    'afcn',
    'afce',
    'afcs',
    'afcw'
);

CREATE TABLE :schema.teams(
    team_id smallint NOT NULL GENERATED ALWAYS AS IDENTITY,
    name text NOT NULL,
    division :schema.divisions NOT NULL,
    external_id text,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT teams_pk PRIMARY KEY (team_id),
    CONSTRAINT teams_name_ck CHECK (char_length(name) <= 255)
);

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Buffalo', 'afce', '134918');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Miami', 'afce', '134919');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('New England', 'afce', '134920');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('NYJ', 'afce', '134921');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Baltimore', 'afcn', '134922');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Cincinnati', 'afcn', '134923');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Cleveland', 'afcn', '134924');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Pittsburgh', 'afcn', '134925');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Houston', 'afcs', '134926');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Indianapolis', 'afcs', '134927');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Jacksonville', 'afcs', '134928');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Tennessee', 'afcs', '134929');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Denver', 'afcw', '134930');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Kansas City', 'afcw', '134931');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Las Vegas', 'afcw', '134932');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('LAC', 'afcw', '135908');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Dallas', 'nfce', '134934');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('NYG', 'nfce', '134935');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Philadelphia', 'nfce', '134936');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Washington', 'nfce', '134937');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Chicago', 'nfcn', '134938');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Detroit', 'nfcn', '134939');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Green Bay', 'nfcn', '134940');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Minnesota', 'nfcn', '134941');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Atlanta', 'nfcs', '134942');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Carolina', 'nfcs', '134943');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('New Orleans', 'nfcs', '134944');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Tampa Bay', 'nfcs', '134945');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Seattle', 'nfcw', '134949');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('San Francisco', 'nfcw', '134948');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('Arizona', 'nfcw', '134946');

INSERT INTO :schema.teams(name, division, external_id)
    VALUES ('LAR', 'nfcw', '135907');

CREATE TABLE :schema.seasons(
    season_id smallint NOT NULL GENERATED ALWAYS AS IDENTITY,
    started_at timestamptz NOT NULL,
    ended_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT seasons_pk PRIMARY KEY (season_id)
);

CREATE TABLE :schema.leagues(
    league_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    name text,
    user_id integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT leagues_pk PRIMARY KEY (league_id),
    CONSTRAINT leagues_name_ck CHECK (char_length(name) <= 255),
    CONSTRAINT leagues_users_fk FOREIGN KEY (user_id) REFERENCES :schema.users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE :schema.league_seasons(
    league_season_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    season_id smallint NOT NULL,
    league_id integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    UNIQUE (season_id, league_id),
    CONSTRAINT league_seasons_pk PRIMARY KEY (league_season_id),
    CONSTRAINT league_seasons_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT league_seasons_leagues_fk FOREIGN KEY (league_id) REFERENCES :schema.leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE :schema.league_users(
    league_user_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    league_season_id integer NOT NULL,
    user_id integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT league_users_pk PRIMARY KEY (league_user_id),
    CONSTRAINT league_users_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES :schema.league_seasons(league_season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT league_users_users_fk FOREIGN KEY (user_id) REFERENCES :schema.users(user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE :schema.rosters(
    roster_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    league_user_id bigint NOT NULL,
    team_id smallint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT rosters_pk PRIMARY KEY (roster_id),
    CONSTRAINT rosters_league_users_fk FOREIGN KEY (league_user_id) REFERENCES :schema.league_users(league_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT rosters_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TYPE :schema.draft_status AS ENUM(
    'created',
    'running',
    'complete'
);

CREATE TABLE :schema.drafts(
    draft_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    league_season_id integer NOT NULL,
    status :schema.draft_status NOT NULL DEFAULT 'created',
    started_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT drafts_pk PRIMARY KEY (draft_id),
    CONSTRAINT drafts_league_seasons_fk FOREIGN KEY (league_season_id) REFERENCES :schema.league_seasons(league_season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE :schema.picks(
    pick_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    draft_id integer NOT NULL,
    league_user_id integer NOT NULL,
    team_id smallint NOT NULL,
    auto boolean NOT NULL DEFAULT FALSE,
    picked_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (draft_id, team_id),
    CONSTRAINT picks_pk PRIMARY KEY (pick_id),
    CONSTRAINT picks_drafts_fk FOREIGN KEY (draft_id) REFERENCES :schema.drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT picks_league_users_fk FOREIGN KEY (league_user_id) REFERENCES :schema.league_users(league_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT picks_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TABLE :schema.rankings(
    ranking_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    season_id integer NOT NULL,
    team_id integer NOT NULL,
    rank smallint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT rankings_pk PRIMARY KEY (ranking_id),
    CONSTRAINT rankings_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

CREATE TYPE :schema.score_category AS ENUM(
    'win',
    'playoffs',
    'divisional',
    'conference',
    'superbowl',
    'champion'
);

CREATE TABLE :schema.scores(
    score_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    season_id integer NOT NULL,
    team_id integer NOT NULL,
    week smallint NOT NULL,
    category :schema.score_category NOT NULL DEFAULT 'win',
    scored_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (season_id, team_id, week, category),
    CONSTRAINT scores_pk PRIMARY KEY (score_id),
    CONSTRAINT scores_seasons_fk FOREIGN KEY (season_id) REFERENCES :schema.seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT scores_teams_fk FOREIGN KEY (team_id) REFERENCES :schema.teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
);

-- functions

CREATE OR REPLACE FUNCTION :schema.current_season()
    RETURNS int
    AS $$
    SELECT
        season_id
    FROM
        seasons
    WHERE
        started_at <= now()
        AND ended_at > now()
    LIMIT 1;
$$
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION season_id_by_year(year text)
    RETURNS int
    AS $$
    SELECT
        season_id
    FROM
        seasons
    WHERE
        year::integer >= date_part('year', started_at)
    LIMIT 1;
$$
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION category_score(category score_category)
    RETURNS smallint
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
BEGIN
    RETURN(
        SELECT
            CASE WHEN category = 'win' THEN
                1
            WHEN category IN('superbowl', 'conference') THEN
                10
            ELSE
                5
            END);
END;
$$;

CREATE OR REPLACE FUNCTION :schema.current_week()
    RETURNS smallint
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    current_week smallint;
BEGIN
    SELECT
        TRUNC(DATE_PART('day', now() - started_at) / 7)::smallint + 1 AS current_week
    FROM
        seasons
    WHERE
        season_id = current_season() INTO current_week;
    RETURN (IFcurrent_week;
END;
$$;

CREATE OR REPLACE PROCEDURE new_league_season(league_id integer)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
BEGIN
    CALL new_league_season(league_id, NULL);
END;
$$;

CREATE OR REPLACE PROCEDURE new_league_season(league_id integer, draft_time timestamptz)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
    new_league_season_id integer;
    new_draft_id integer;
BEGIN
    INSERT INTO league_seasons(season_id, league_id)
        VALUES (current_season(), league_id)
    RETURNING
        league_season_id INTO new_league_season_id;

    IF draft_time IS NOT NULL THEN
        INSERT INTO drafts(league_season_id, started_at)
            VALUES (new_league_season_id, draft_time)
        RETURNING
            draft_id INTO new_draft_id;
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION pick(_draft_id integer, team_id integer)
    RETURNS boolean
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    pick_count smallint;
    player_1 integer;
    player_2 integer;
BEGIN
    INSERT INTO picks (draft_id, league_user_id, team_id) VALUES (_draft_id, current_picker(_draft_id), team_id);
END;
$$;

CREATE OR REPLACE FUNCTION current_picker(_draft_id integer)
    RETURNS integer
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    pick_count smallint;
    player_1 integer;
    player_2 integer;
BEGIN
    SELECT
        count(1)
    FROM
        picks
    WHERE
        draft_id = _draft_id INTO pick_count;
    SELECT
        league_user_id
    FROM
        league_users
    WHERE
        league_season_id =(
            SELECT
                league_season_id
            FROM
                drafts
            WHERE
                draft_id = _draft_id)
    ORDER BY
        league_user_id
    LIMIT 1 INTO player_1;
    SELECT
        league_user_id
    FROM
        league_users
    WHERE
        league_season_id =(
            SELECT
                league_season_id
            FROM
                drafts
            WHERE
                draft_id = _draft_id)
    ORDER BY
        league_user_id DESC
    LIMIT 1 INTO player_2;
    IF pick_count % 2 = 0 THEN
        RETURN player_1;
    ELSE
        RETURN player_2;
    END IF;
END;
$$;

-- create missing draft picks if any
CREATE OR REPLACE PROCEDURE auto_draft(_draft_id integer)
LANGUAGE plpgsql
SECURITY INVOKER
AS $$
DECLARE
    latest_pick_time timestamptz;
    latest_pick picks%ROWTYPE;
    pick_count integer;
    next_auto_draft_team integer;
    draft drafts%ROWTYPE;
BEGIN
    SELECT * FROM drafts WHERE draft_id = _draft_id INTO draft;
    /* IF now() >= draft.started_at THEN */
    /*     UPDATE drafts SET status = 'running', modified_at = now() WHERE draft_id = _draft_id; */
    /* END IF; */
    latest_pick_time = draft.started_at;
    
    /* SELECT */
    /*     started_at */
    /* FROM */
    /*     drafts */
    /* WHERE */
    /*     draft_id = _draft_id INTO latest_pick_time; */

    IF now() >= latest_pick_time AND draft.status = 'created' THEN
        UPDATE drafts SET status = 'running', modified_at = now() WHERE draft_id = _draft_id;
    END IF;

    LOOP
        SELECT
            count(1)
        FROM
            picks
        WHERE
            draft_id = _draft_id INTO pick_count;

        IF pick_count = 32 THEN
            UPDATE drafts SET status = 'complete', modified_at = now() WHERE draft_id = _draft_id;
            RETURN;
        END IF;

        SELECT
            *
        FROM
            picks
        WHERE
            draft_id = _draft_id
        ORDER BY
            picked_at DESC
        LIMIT 1 INTO latest_pick;
        IF latest_pick IS NOT NULL THEN
            latest_pick_time = latest_pick.picked_at;
        END IF;
        IF (date_part('epoch', now())::integer - date_part('epoch', latest_pick_time)::integer) / 30 >= 1 THEN
            SELECT
                current_rankings.team_id
            FROM
                current_rankings
            LEFT JOIN picks ON picks.team_id = current_rankings.team_id
        WHERE
            pick_id IS NULL
        ORDER BY
            rank ASC
        LIMIT 1 INTO next_auto_draft_team;
            INSERT INTO picks(draft_id, league_user_id, team_id, auto, picked_at)
                VALUES (_draft_id, current_picker(_draft_id),
(
                        SELECT
                            current_rankings.team_id
                        FROM
                            current_rankings
                        LEFT JOIN picks ON picks.team_id = current_rankings.team_id
                        AND picks.draft_id = _draft_id
                    WHERE
                        pick_id IS NULL
                    ORDER BY
                        rank ASC
                    LIMIT 1),
                TRUE,
                latest_pick_time + interval '30 seconds');
        ELSE
            EXIT;
        END IF;
    END LOOP;
END;
$$;

CREATE OR REPLACE FUNCTION current_round(_draft_id integer)
    RETURNS smallint
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    time_now timestamptz;
    round smallint;
    latest_pick picks%ROWTYPE;
    pick_count integer;
BEGIN
    time_now = now();
    SELECT
        count(1)
    FROM
        picks
    WHERE
        draft_id = _draft_id INTO pick_count;
    SELECT
        *
    FROM
        picks
    WHERE
        draft_id = _draft_id
    ORDER BY
        created_at DESC
    LIMIT 1 INTO latest_pick;
    IF latest_pick IS NOT NULL THEN
        time_now = latest_pick.created_at;
    END IF;
    SELECT
        (date_part('epoch', time_now)::integer - date_part('epoch', started_at)::integer) / 30
    FROM
        drafts
    WHERE
        draft_id = _draft_id INTO round;
    IF round >= 32 THEN
        RETURN 32;
    ELSE
        RETURN (round);
    END IF;
END;
$$;

CREATE OR REPLACE FUNCTION record_external_score_by_year(_external_id text, year int, week smallint, category score_category, scored_at timestamptz)
    RETURNS int
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    team_id integer;
    season_id integer;
BEGIN
    SELECT seasons.season_id FROM seasons WHERE date_part('year', started_at) = year ORDER BY started_at DESC LIMIT 1 INTO season_id;
    SELECT teams.team_id FROM teams WHERE external_id = _external_id INTO team_id;
    RETURN (SELECT record_score(team_id, week, season_id, category, scored_at));
END;
$$;

CREATE OR REPLACE FUNCTION record_external_score(_external_id text, season_id int, week smallint, category score_category, scored_at timestamptz)
    RETURNS int
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    team_id integer;
BEGIN
    SELECT teams.team_id FROM teams WHERE external_id = _external_id INTO team_id;
    RETURN (SELECT record_score(team_id, week, season_id, category, scored_at));
END;
$$;


CREATE OR REPLACE FUNCTION record_score(team_id int, week smallint, season_id int, category score_category, scored_at timestamptz)
    RETURNS int
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    new_score_id integer;
BEGIN
    INSERT INTO scores(season_id, team_id, week, category, scored_at)
        VALUES (season_id, team_id, week, category, scored_at)
    ON CONFLICT ON CONSTRAINT scores_season_id_team_id_week_category_key
        DO UPDATE SET modified_at = NOW(), scored_at = EXCLUDED.scored_at
    RETURNING
        score_id INTO new_score_id;
    RETURN new_score_id;
END;
$$;

CREATE OR REPLACE FUNCTION current_draft_picks(_draft_id int)
    RETURNS TABLE (team_id      smallint,
                   rank         smallint,
                   name         text,
                   division     divisions,
                   pick_user    text)
    LANGUAGE plpgsql
    SECURITY INVOKER
    AS $$
DECLARE
    draft_season_id integer;
BEGIN
    SELECT league_seasons.season_id FROM drafts JOIN league_seasons USING (league_season_id) WHERE draft_id = _draft_id INTO draft_season_id;
RETURN QUERY 
    WITH season_rankings AS (
        SELECT
            teams.team_id,
            rankings.rank,
            teams.name,
            teams.division
        FROM
            teams
            LEFT JOIN rankings ON rankings.team_id = teams.team_id
                AND rankings.season_id = draft_season_id
            ORDER BY
                rank
    )
    SELECT r.*, users.name AS pick_user 
    FROM season_rankings r
    LEFT JOIN picks ON r.team_id = picks.team_id AND picks.draft_id = _draft_id 
    LEFT JOIN league_users ON league_users.league_user_id = picks.league_user_id
    LEFT JOIN users ON users.user_id = league_users.user_id;
END;
$$;

-- views
CREATE OR REPLACE VIEW draft_info AS
 WITH latest_draft_pick AS (
    SELECT draft_id, MAX(picked_at) AS picked_at FROM picks GROUP BY draft_id
 ) 
 select draft_id, current_picker(draft_id), status,
 leagues.name as league,
 CASE WHEN status = 'complete' OR status = 'created' THEN 0
ELSE date_part('epoch', (coalesce(latest_draft_pick.picked_at, started_at) + interval '30 seconds') - now())::integer 
    END AS round_time_remaining,
 CASE
   WHEN status = 'complete' THEN 'The draft is over, good luck!'
   WHEN status = 'created' THEN 'The draft will start in ' || to_char(started_at - now(), 'MI:SS')
   WHEN status = 'running' THEN 'It''s ' ||
     (SELECT u.name FROM league_users lu JOIN users u ON u.user_id = lu.user_id
      WHERE lu.league_user_id = current_picker(draft_id)) || '''s turn!'
 END as message,
 status = 'running' as can_pick
 from drafts
 JOIN league_seasons USING(league_season_id)
 JOIN leagues USING(league_id)
 LEFT JOIN latest_draft_pick USING(draft_id);

CREATE OR REPLACE VIEW league_picks AS
SELECT
    league_id,
    teams.team_id,
    teams.name AS team,
    users.name AS player
FROM
    picks
    JOIN teams USING (team_id)
    JOIN league_users USING (league_user_id)
    JOIN users USING (user_id)
    JOIN league_seasons USING (league_season_id)
ORDER BY
    picks.created_at;

CREATE OR REPLACE VIEW season_player_scores AS
WITH season_scores AS (
    SELECT
        season_id,
        team_id,
        sum(category_score(category))::integer AS points
    FROM
        scores
    GROUP BY
        season_id,
        team_id
)
SELECT
    season_id,
    league_id,
    player,
    sum(coalesce(points, 0))::integer AS score
FROM
    league_picks
    LEFT OUTER JOIN season_scores USING (team_id)
GROUP BY
    season_id,
    league_id,
    player
ORDER BY
    score DESC;

CREATE OR REPLACE VIEW current_player_scores AS
WITH current_scores AS (
    SELECT
        team_id,
        sum(category_score(category))::integer AS points
    FROM
        scores
    WHERE
        season_id = current_season()
    GROUP BY
        team_id
)
SELECT
    league_id,
    player,
    sum(coalesce(points, 0))::integer AS score
FROM
    league_picks
    LEFT OUTER JOIN current_scores USING (team_id)
GROUP BY
    league_id,
    player
ORDER BY
    score DESC;

CREATE OR REPLACE VIEW league_scores AS
WITH season_scores AS (
    SELECT
        season_id,
        team_id,
        SUM(CASE WHEN category = 'win' THEN
                    category_score('win')
                END)::smallint AS win,
        SUM(CASE WHEN category = 'playoffs' THEN
                    category_score('playoffs')
                END)::smallint AS playoffs,
        SUM(CASE WHEN category = 'divisional' THEN
                    category_score('divisional')
                END)::smallint AS divisional,
        SUM(CASE WHEN category = 'conference' THEN
                    category_score('conference')
                END)::smallint AS conference,
        SUM(CASE WHEN category = 'superbowl' THEN
                    category_score('superbowl')
                END)::smallint AS superbowl,
        SUM(CASE WHEN category = 'champion' THEN
                    category_score('champion')
                END)::smallint AS champion,
        coalesce(SUM(category_score(category)), 0)::smallint AS total
    FROM
        scores
    GROUP BY
        season_id,
        team_id
)
SELECT
    season_id,
    league_id,
    team,
    player,
    coalesce(win, 0) AS win,
    playoffs,
    divisional,
    conference,
    superbowl,
    champion,
    coalesce(total, 0) AS total
FROM
    league_picks
    LEFT OUTER JOIN season_scores USING (team_id);


CREATE OR REPLACE VIEW :schema.current_rankings AS
SELECT
    teams.team_id,
    rank,
    name,
    division
FROM
    teams
    LEFT JOIN rankings ON rankings.team_id = teams.team_id
        AND rankings.season_id = current_season()
    ORDER BY
        rank;

CREATE OR REPLACE FUNCTION random_invite()
    RETURNS  varchar
    LANGUAGE plpgsql
AS $$
DECLARE
    base10 bigint;
    base36 varchar := '';
    intval bigint;
    char0z char[]  := regexp_split_to_array('0123456789abcdefghijklmnopqrstuvwxyz', '');
BEGIN
    SELECT floor(random() * 78_364_164_095 + 2_176_782_336) INTO base10;
    intval := abs(base10);
    WHILE intval != 0 LOOP
        base36 := char0z[(intval % 36)+1] || base36;
        intval := intval / 36;
    END LOOP;

    IF base10 = 0 THEN base36 := '0'; END IF;
    RETURN base36;
END;
$$;