let's goHEADmain

1 files changed, 29 insertions, 0 deletions

diff --git a/src/db/leagues.sql b/src/db/leagues.sql
new file mode 100644
index 0000000..9407de8
--- /dev/null
+++ b/src/db/leagues.sql
@@ -0,0 +1,29 @@
+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');