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