From 8d018d996c1eddb882dc64ebbd228bb0135944f3 Mon Sep 17 00:00:00 2001 From: sadbeast Date: Sun, 23 Jun 2024 15:36:59 -0700 Subject: wtf --- src/db/schema.sql | 902 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 902 insertions(+) create mode 100644 src/db/schema.sql (limited to 'src/db/schema.sql') diff --git a/src/db/schema.sql b/src/db/schema.sql new file mode 100644 index 0000000..ed5e72c --- /dev/null +++ b/src/db/schema.sql @@ -0,0 +1,902 @@ +\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; +$$; -- cgit v1.2.3