aboutsummaryrefslogtreecommitdiffstats
path: root/src/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/db')
-rw-r--r--src/db/schema.sql902
-rw-r--r--src/db/seed.sql68
2 files changed, 970 insertions, 0 deletions
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;
+$$;
diff --git a/src/db/seed.sql b/src/db/seed.sql
new file mode 100644
index 0000000..37bb2f9
--- /dev/null
+++ b/src/db/seed.sql
@@ -0,0 +1,68 @@
+\set database_name teamdraft
+\set schema :database_name
+\set rw_name :database_name '_website'
+\c :database_name
+SET ROLE :rw_name;
+SET search_path TO :schema, public;
+
+begin;
+do
+$$
+declare
+ player_1_user_id int;
+ player_2_user_id int;
+ new_league_id int;
+ new_draft_id int;
+ new_league_season_id int;
+begin
+
+INSERT INTO seasons (started_at, ended_at) VALUES (NOW(), NOW() + interval '3 months');
+
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 1, 1);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 2, 2);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 3, 3);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 4, 4);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 5, 5);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 6, 6);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 7, 7);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 8, 8);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 9, 9);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 10, 10);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 11, 11);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 12, 12);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 13, 13);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 14, 14);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 15, 15);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 16, 16);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 17, 17);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 18, 18);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 19, 19);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 20, 20);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 21, 21);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 22, 22);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 23, 23);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 24, 24);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 25, 25);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 26, 26);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 27, 27);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 28, 28);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 29, 29);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 30, 30);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 31, 31);
+INSERT INTO rankings (season_id, team_id, rank) VALUES (current_season(), 32, 32);
+
+INSERT INTO users (username, name) VALUES ('kent', 'Kent') RETURNING user_id INTO player_1_user_id;
+INSERT INTO users (username, name) VALUES ('miranda', 'Miranda') RETURNING user_id INTO player_2_user_id;
+
+INSERT INTO leagues (name, user_id) VALUES ('my league', player_1_user_id) RETURNING league_id INTO new_league_id;
+INSERT INTO league_seasons (season_id, league_id) VALUES (current_season(), new_league_id) RETURNING league_season_id INTO new_league_season_id;
+
+INSERT INTO league_users (league_season_id, user_id) VALUES (new_league_season_id, player_1_user_id);
+INSERT INTO league_users (league_season_id, user_id) VALUES (new_league_season_id, player_2_user_id);
+
+INSERT INTO drafts (league_season_id, started_at) VALUES (new_league_season_id, NOW()) RETURNING draft_id INTO new_draft_id;
+
+end;
+
+$$;
+commit;