CREATE TABLE leagues (
    league_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    name text,

    CONSTRAINT leagues_pk PRIMARY KEY (league_id),
    CONSTRAINT leagues_name_ck CHECK (char_length(name) <= 64)
);

CREATE TABLE league_divisions (
    league_division_id integer NOT NULL GENERATED ALWAYS AS IDENTITY,
    league_id bigint NOT NULL,
    name text,

    CONSTRAINT league_divisions_pk PRIMARY KEY (league_division_id),
    CONSTRAINT league_divisions_leagues_fk FOREIGN KEY (league_id) REFERENCES leagues(league_id) MATCH SIMPLE ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT league_divisions_name_ck CHECK (char_length(name) <= 64)
);

INSERT INTO leagues (name) VALUES ('NFL') RETURNING league_id nfl_league_id \gset

INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC East');
INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC North');
INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC South');
INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'AFC West');

INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC East');
INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC North');
INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC South');
INSERT INTO league_divisions (league_id, name) VALUES (:nfl_league_id, 'NFC West');