let's goHEADmain

1 files changed, 69 insertions, 0 deletions

diff --git a/src/db/seasons.sql b/src/db/seasons.sql
new file mode 100644
index 0000000..ce492f0
--- /dev/null
+++ b/src/db/seasons.sql
@@ -0,0 +1,69 @@
+CREATE TABLE seasons (
+    season_id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
+    league_id integer NOT NULL,
+    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),
+    CONSTRAINT seasons_leagues_fk FOREIGN KEY (league_id) REFERENCES leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE TABLE 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(),
+
+    UNIQUE(season_id, team_id, rank),
+    CONSTRAINT rankings_pk PRIMARY KEY (ranking_id),
+    CONSTRAINT rankings_seasons_fk FOREIGN KEY (season_id) REFERENCES seasons(season_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION
+);
+
+CREATE OR REPLACE VIEW current_rankings AS
+SELECT teams.team_id, rankings.season_id, seasons.league_id, rank, name
+FROM teams
+JOIN rankings ON rankings.team_id = teams.team_id
+JOIN seasons ON rankings.season_id = seasons.season_id
+ORDER BY rank;
+
+CREATE OR REPLACE FUNCTION current_season_id(_league_id int)
+    RETURNS int
+    AS $$
+    SELECT
+        season_id
+    FROM
+        seasons
+    WHERE
+        started_at <= now()
+        AND ended_at > now()
+        AND league_id = _league_id
+    ORDER BY started_at DESC
+    LIMIT 1;
+$$
+LANGUAGE SQL;
+
+CREATE OR REPLACE FUNCTION create_season(_league_id int, started_at timestamptz, ended_at timestamptz)
+    RETURNS int
+    LANGUAGE plpgsql
+    SECURITY INVOKER
+    AS $$
+DECLARE
+    new_season_id int;
+    team record;
+    rank smallint := 1;
+BEGIN
+    INSERT INTO seasons (league_id, started_at, ended_at) VALUES (_league_id, started_at, ended_at) RETURNING season_id INTO new_season_id;
+
+    FOR team in SELECT team_id FROM teams JOIN league_divisions USING (league_division_id) WHERE league_id = _league_id
+    LOOP
+        INSERT INTO rankings (season_id, team_id, rank) VALUES (new_season_id, team.team_id, rank);
+        rank := rank + 1;
+    END LOOP;
+
+    RETURN new_season_id;
+END;
+$$;