let's goHEADmain

1 files changed, 385 insertions, 0 deletions

diff --git a/src/db/drafts.sql b/src/db/drafts.sql
new file mode 100644
index 0000000..4fff19f
--- /dev/null
+++ b/src/db/drafts.sql
@@ -0,0 +1,385 @@
+CREATE TABLE draft_statuses(
+    draft_status_id smallint NOT NULL,
+    name text NOT NULL,
+    UNIQUE (name),
+    CONSTRAINT draft_statuses_pk PRIMARY KEY (draft_status_id)
+);
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (1, 'Scheduled');
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (2, 'Running');
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (3, 'Complete');
+
+INSERT INTO draft_statuses(draft_status_id, name)
+    VALUES (4, 'Manual');
+
+CREATE TABLE drafts (
+    draft_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    season_id bigint NOT NULL,
+    draft_status_id smallint NOT NULL DEFAULT 1,
+    name text,
+    account_id bigint NOT NULL,
+    started_at timestamptz,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT drafts_pk PRIMARY KEY (draft_id),
+    CONSTRAINT drafts_seasons_fk FOREIGN KEY (season_id) REFERENCES seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT drafts_name_ck CHECK (char_length(name) <= 255),
+    CONSTRAINT drafts_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE TABLE draft_users (
+    draft_user_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    draft_id bigint NOT NULL,
+    name text NOT NULL,
+    account_id bigint,
+    created_at timestamptz NOT NULL DEFAULT now(),
+    modified_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT draft_users_pk PRIMARY KEY (draft_user_id),
+    CONSTRAINT draft_users_drafts_fk FOREIGN KEY (draft_id) REFERENCES drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT draft_users_accounts_fk FOREIGN KEY (account_id) REFERENCES accounts(account_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE FUNCTION gen_invite_code()
+    RETURNS  varchar
+    LANGUAGE plpgsql
+AS $$
+DECLARE
+    base10 bigint;
+    base36 varchar := '';
+    intval bigint;
+    char0z char[]  := regexp_split_to_array('0123456789abcdefghijklmnopqrstuvwxyz', '');
+BEGIN
+    SELECT floor(random() * 78364164095 + 2176782336) 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;
+$$;
+
+CREATE TABLE draft_user_invites (
+    draft_user_invite_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    draft_user_id bigint NOT NULL,
+    name text NOT NULL,
+    code text NOT NULL DEFAULT gen_invite_code(),
+    created_at timestamptz NOT NULL DEFAULT now(),
+
+    CONSTRAINT draft_user_invites_pk PRIMARY KEY (draft_user_id),
+    CONSTRAINT draft_user_invites_draft_users_fk FOREIGN KEY (draft_user_id) REFERENCES draft_users(draft_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT draft_user_invites_name_ck CHECK (char_length(name) <= 255)
+);
+
+CREATE TABLE picks (
+    pick_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
+    draft_id integer NOT NULL,
+    draft_user_id integer NOT NULL,
+    team_id int 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 drafts(draft_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT picks_draft_users_fk FOREIGN KEY (draft_user_id) REFERENCES draft_users(draft_user_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT picks_teams_fk FOREIGN KEY (team_id) REFERENCES teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+-- TODO: support more than 2 players by persisting draft order and current_picker
+CREATE OR REPLACE FUNCTION current_picker_name(_draft_id bigint)
+    RETURNS text
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+BEGIN
+    RETURN (SELECT name FROM draft_users WHERE draft_user_id = current_picker_id(_draft_id));
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION current_picker_account_id(_draft_id bigint)
+    RETURNS bigint
+    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 account_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_user_id 
+    LIMIT 1 INTO player_1;
+
+    SELECT account_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_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 OR REPLACE FUNCTION current_picker_id(_draft_id bigint)
+    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 draft_user_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_user_id 
+    LIMIT 1 INTO player_1;
+
+    SELECT draft_user_id
+    FROM draft_users
+    WHERE draft_id = _draft_id
+    ORDER BY draft_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 OR REPLACE FUNCTION pick(_draft_id integer, _team_id integer)
+    RETURNS boolean
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+BEGIN
+    INSERT INTO picks (draft_id, league_user_id, team_id) VALUES (_draft_id, current_picker_id(_draft_id), _team_id);
+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;
+    draft_league_id integer;
+BEGIN
+    SELECT * FROM drafts WHERE draft_id = _draft_id INTO draft;
+    SELECT league_id FROM seasons WHERE season_id = draft.season_id INTO draft_league_id;
+
+    latest_pick_time = draft.started_at;
+    
+    IF now() >= latest_pick_time AND draft.draft_status_id = 1 THEN
+        UPDATE drafts SET draft_status_id = 2, 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 = (SELECT count(1) FROM teams JOIN league_divisions USING(league_division_id) WHERE league_id = draft_league_id) THEN
+            UPDATE drafts SET draft_status_id = 3, 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
+                AND league_id = draft_league_id
+            ORDER BY
+                rank ASC
+            LIMIT 1 INTO next_auto_draft_team;
+
+            INSERT INTO picks(draft_id, draft_user_id, team_id, auto, picked_at)
+                VALUES (_draft_id, current_picker_id(_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 VIEW draft_picks AS
+SELECT
+    picks.draft_id,
+    teams.team_id,
+    teams.name AS team,
+    accounts.name AS player
+FROM
+    picks
+    JOIN teams USING (team_id)
+    JOIN draft_users USING (draft_user_id)
+    JOIN accounts USING (account_id)
+ORDER BY
+    picks.created_at;
+
+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_account_id(draft_id) as current_player_id,
+    current_picker_name(draft_id) as current_player_name,
+    draft_status_id,
+    CASE WHEN draft_status_id IN (1, 3) 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 draft_status_id = 1 THEN
+        'The draft will start in ' || to_char(started_at - now(), 'MI:SS')
+    WHEN draft_status_id = 2 THEN
+        'It''s ' || current_picker_name(draft_id) || '''s turn!'
+    WHEN draft_status_id = 3 THEN
+        'The draft is over, good luck!'
+    END AS message,
+    draft_status_id = 2 AS can_pick
+FROM
+    drafts
+    LEFT JOIN latest_draft_pick USING (draft_id);
+
+CREATE OR REPLACE FUNCTION accept_invite(_code text)
+    RETURNS table (session_id text, invite_draft_id bigint)
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_account_id bigint;
+    invite_draft_user_id bigint;
+    invite_draft_id bigint;
+    new_session_id text;
+BEGIN
+    SELECT draft_user_id FROM draft_user_invites WHERE code = _code INTO invite_draft_user_id;
+    SELECT draft_id FROM draft_users WHERE draft_user_id = invite_draft_user_id INTO invite_draft_id;
+    
+    INSERT INTO accounts DEFAULT VALUES RETURNING account_id INTO new_account_id;
+
+    UPDATE draft_users SET account_id = new_account_id WHERE draft_user_id = invite_draft_user_id;
+    SELECT create_session(new_account_id) INTO new_session_id;
+
+    RETURN QUERY SELECT new_session_id, invite_draft_id;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION create_draft(league_id int, player_1 text, player_2 text, started_at timestamptz)
+    RETURNS table (session_id text, new_draft_id bigint)
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_account_id bigint;
+    new_draft_id bigint;
+    current_season_id int := current_season_id(league_id);
+    new_session_id text;
+    player_2_id bigint;
+BEGIN
+    INSERT INTO accounts DEFAULT VALUES RETURNING account_id INTO new_account_id;
+    SELECT create_session(new_account_id) INTO new_session_id;
+
+    IF started_at IS NULL THEN
+        INSERT INTO drafts (season_id, account_id, draft_status_id) VALUES (current_season_id, new_account_id, 4) RETURNING draft_id INTO new_draft_id;
+    ELSE
+        INSERT INTO drafts (season_id, account_id, started_at) VALUES (current_season_id, new_account_id, started_at) RETURNING draft_id INTO new_draft_id;
+    END IF;
+
+    INSERT INTO draft_users (draft_id, name, account_id) VALUES (new_draft_id, player_1, new_account_id);
+    INSERT INTO draft_users (draft_id, name) VALUES (new_draft_id, CASE WHEN player_2 = '' THEN 'Player 2' ELSE player_2 END) RETURNING draft_user_id INTO player_2_id;
+    INSERT INTO draft_user_invites (draft_user_id, name) VALUES (player_2_id, player_2);
+
+    RETURN QUERY SELECT new_session_id, new_draft_id;
+END;
+$$;
+
+CREATE OR REPLACE FUNCTION current_draft_picks(_draft_id int)
+    RETURNS TABLE (team_id      int,
+                   rank         smallint,
+                   name         text,
+                   picked       boolean,
+                   pick_user    text)
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    draft_season_id integer;
+BEGIN
+    SELECT season_id FROM drafts WHERE draft_id = _draft_id INTO draft_season_id;
+RETURN QUERY
+    WITH season_rankings AS (
+        SELECT
+            teams.team_id,
+            rankings.rank,
+            teams.name
+        FROM
+            teams
+            LEFT JOIN rankings ON rankings.team_id = teams.team_id
+                AND rankings.season_id = draft_season_id
+            ORDER BY
+                rank
+    )
+    SELECT r.*, draft_users.name IS NOT NULL AS picked, draft_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 draft_users ON draft_users.draft_user_id = picks.draft_user_id;
+END;
+$$;