let's goHEADmain

1 files changed, 106 insertions, 0 deletions

diff --git a/src/db/scores.sql b/src/db/scores.sql
new file mode 100644
index 0000000..8572a66
--- /dev/null
+++ b/src/db/scores.sql
@@ -0,0 +1,106 @@
+CREATE TABLE score_categories (
+    score_category_id smallint NOT NULL,
+    league_id int NOT NULL,
+    name text NOT NULL,
+    value int NOT NULL,
+
+    UNIQUE (league_id, name),
+    CONSTRAINT score_categories_pk PRIMARY KEY (score_category_id)
+);
+SELECT league_id AS nfl_league_id FROM leagues where name = 'NFL' LIMIT 1 \gset
+
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (1, :nfl_league_id, 'Win', 1);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (2, :nfl_league_id, 'Playoffs', 5);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (3, :nfl_league_id, 'Divisional', 5);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (4, :nfl_league_id, 'Conference', 10);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (5, :nfl_league_id, 'Superbowl', 10);
+INSERT INTO score_categories (score_category_id, league_id, name, value) VALUES (6, :nfl_league_id, 'Champion', 5);
+
+CREATE TABLE scores (
+    score_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    season_id bigint NOT NULL,
+    team_id integer NOT NULL,
+    game int NOT NULL,
+    score_category_id smallint NOT NULL DEFAULT 1,
+    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, game, score_category_id),
+    CONSTRAINT scores_pk PRIMARY KEY (score_id),
+    CONSTRAINT scores_seasons_fk FOREIGN KEY (season_id) REFERENCES seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT scores_teams_fk FOREIGN KEY (team_id) REFERENCES teams(team_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
+    CONSTRAINT scores_score_categories_fk FOREIGN KEY (score_category_id) REFERENCES score_categories(score_category_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE FUNCTION record_score(team_id int, game int, season_id int, score_category_id int, scored_at timestamptz)
+    RETURNS int
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_score_id integer;
+BEGIN
+    INSERT INTO scores(season_id, team_id, game, score_category_id, scored_at)
+        VALUES (season_id, team_id, game, score_category_id, scored_at)
+    ON CONFLICT ON CONSTRAINT scores_season_id_team_id_game_score_category_id_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 VIEW season_scores AS
+WITH score_totals AS (
+    SELECT
+        scores.season_id,
+        scores.team_id,
+        sc.name,
+        sum(value) AS total
+    FROM
+        scores
+        JOIN score_categories sc USING(score_category_id)
+    GROUP BY
+        scores.season_id,
+        scores.team_id,
+        sc.name
+)
+SELECT
+    st.season_id,
+    teams.name,
+    array_agg(st.name || ': ' || st.total ORDER BY st.total DESC) AS scores
+FROM
+    teams
+    LEFT JOIN score_totals st USING(team_id)
+GROUP BY
+    st.season_id, teams.name;
+
+-- CREATE OR REPLACE VIEW draft_scores AS
+-- WITH season_scores AS (
+--     SELECT
+--         season_id,
+--         team_id,
+--         coalesce(SUM(value), 0)::smallint AS total
+--     FROM
+--         scores
+--     JOIN score_categories ON category_id = score_categories.score_category_id
+--     GROUP BY
+--         season_id,
+--         team_id
+-- )
+-- SELECT
+--     season_id,
+--     draft_id,
+--     team,
+--     player,
+--     coalesce(win, 0) AS win,
+--     playoffs,
+--     divisional,
+--     conference,
+--     superbowl,
+--     champion,
+--     coalesce(total, 0) AS total
+-- FROM
+--     draft_picks
+--     LEFT OUTER JOIN season_scores USING (team_id);