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);