-- Schema: bird__hockey
CREATE SCHEMA IF NOT EXISTS "bird__hockey";

-- Table: AwardsCoaches
CREATE TABLE "bird__hockey"."AwardsCoaches" (
    "coachID" TEXT,
    "award" TEXT,
    "year" BIGINT,
    "lgID" TEXT,
    "note" TEXT
);

-- Table: AwardsMisc
CREATE TABLE "bird__hockey"."AwardsMisc" (
    "name" TEXT NOT NULL,
    "ID" TEXT,
    "award" TEXT,
    "year" BIGINT,
    "lgID" TEXT,
    "note" TEXT,
    CONSTRAINT "AwardsMisc_pkey" PRIMARY KEY ("name")
);

-- Table: AwardsPlayers
CREATE TABLE "bird__hockey"."AwardsPlayers" (
    "playerID" TEXT NOT NULL,
    "award" TEXT NOT NULL,
    "year" BIGINT NOT NULL,
    "lgID" TEXT,
    "note" TEXT,
    "pos" TEXT,
    CONSTRAINT "AwardsPlayers_pkey" PRIMARY KEY ("playerID", "award", "year")
);

-- Table: Coaches
CREATE TABLE "bird__hockey"."Coaches" (
    "coachID" TEXT NOT NULL,
    "year" BIGINT NOT NULL,
    "tmID" TEXT NOT NULL,
    "lgID" TEXT,
    "stint" BIGINT NOT NULL,
    "notes" TEXT,
    "g" BIGINT,
    "w" BIGINT,
    "l" BIGINT,
    "t" BIGINT,
    "postg" TEXT,
    "postw" TEXT,
    "postl" TEXT,
    "postt" TEXT,
    CONSTRAINT "Coaches_pkey" PRIMARY KEY ("coachID", "year", "tmID", "stint")
);

-- Table: CombinedShutouts
CREATE TABLE "bird__hockey"."CombinedShutouts" (
    "year" BIGINT,
    "month" BIGINT,
    "date" BIGINT,
    "tmID" TEXT,
    "oppID" TEXT,
    "R/P" TEXT,
    "IDgoalie1" TEXT,
    "IDgoalie2" TEXT
);

-- Table: Goalies
CREATE TABLE "bird__hockey"."Goalies" (
    "playerID" TEXT NOT NULL,
    "year" BIGINT NOT NULL,
    "stint" BIGINT NOT NULL,
    "tmID" TEXT,
    "lgID" TEXT,
    "GP" TEXT,
    "Min" TEXT,
    "W" TEXT,
    "L" TEXT,
    "T/OL" TEXT,
    "ENG" TEXT,
    "SHO" TEXT,
    "GA" TEXT,
    "SA" TEXT,
    "PostGP" TEXT,
    "PostMin" TEXT,
    "PostW" TEXT,
    "PostL" TEXT,
    "PostT" TEXT,
    "PostENG" TEXT,
    "PostSHO" TEXT,
    "PostGA" TEXT,
    "PostSA" TEXT,
    CONSTRAINT "Goalies_pkey" PRIMARY KEY ("playerID", "year", "stint")
);

-- Table: GoaliesSC
CREATE TABLE "bird__hockey"."GoaliesSC" (
    "playerID" TEXT NOT NULL,
    "year" BIGINT NOT NULL,
    "tmID" TEXT,
    "lgID" TEXT,
    "GP" BIGINT,
    "Min" BIGINT,
    "W" BIGINT,
    "L" BIGINT,
    "T" BIGINT,
    "SHO" BIGINT,
    "GA" BIGINT,
    CONSTRAINT "GoaliesSC_pkey" PRIMARY KEY ("playerID", "year")
);

-- Table: GoaliesShootout
CREATE TABLE "bird__hockey"."GoaliesShootout" (
    "playerID" TEXT,
    "year" BIGINT,
    "stint" BIGINT,
    "tmID" TEXT,
    "W" BIGINT,
    "L" BIGINT,
    "SA" BIGINT,
    "GA" BIGINT
);

-- Table: HOF
CREATE TABLE "bird__hockey"."HOF" (
    "year" BIGINT,
    "hofID" TEXT NOT NULL,
    "name" TEXT,
    "category" TEXT,
    CONSTRAINT "HOF_pkey" PRIMARY KEY ("hofID")
);

-- Table: Master
CREATE TABLE "bird__hockey"."Master" (
    "playerID" TEXT,
    "coachID" TEXT,
    "hofID" TEXT,
    "firstName" TEXT,
    "lastName" TEXT NOT NULL,
    "nameNote" TEXT,
    "nameGiven" TEXT,
    "nameNick" TEXT,
    "height" TEXT,
    "weight" TEXT,
    "shootCatch" TEXT,
    "legendsID" TEXT,
    "ihdbID" TEXT,
    "hrefID" TEXT,
    "firstNHL" TEXT,
    "lastNHL" TEXT,
    "firstWHA" TEXT,
    "lastWHA" TEXT,
    "pos" TEXT,
    "birthYear" TEXT,
    "birthMon" TEXT,
    "birthDay" TEXT,
    "birthCountry" TEXT,
    "birthState" TEXT,
    "birthCity" TEXT,
    "deathYear" TEXT,
    "deathMon" TEXT,
    "deathDay" TEXT,
    "deathCountry" TEXT,
    "deathState" TEXT,
    "deathCity" TEXT
);

-- Table: Scoring
CREATE TABLE "bird__hockey"."Scoring" (
    "playerID" TEXT,
    "year" BIGINT,
    "stint" BIGINT,
    "tmID" TEXT,
    "lgID" TEXT,
    "pos" TEXT,
    "GP" BIGINT,
    "G" BIGINT,
    "A" BIGINT,
    "Pts" BIGINT,
    "PIM" BIGINT,
    "+/-" TEXT,
    "PPG" TEXT,
    "PPA" TEXT,
    "SHG" TEXT,
    "SHA" TEXT,
    "GWG" TEXT,
    "GTG" TEXT,
    "SOG" TEXT,
    "PostGP" TEXT,
    "PostG" TEXT,
    "PostA" TEXT,
    "PostPts" TEXT,
    "PostPIM" TEXT,
    "Post+/-" TEXT,
    "PostPPG" TEXT,
    "PostPPA" TEXT,
    "PostSHG" TEXT,
    "PostSHA" TEXT,
    "PostGWG" TEXT,
    "PostSOG" TEXT
);

-- Table: ScoringSC
CREATE TABLE "bird__hockey"."ScoringSC" (
    "playerID" TEXT,
    "year" BIGINT,
    "tmID" TEXT,
    "lgID" TEXT,
    "pos" TEXT,
    "GP" BIGINT,
    "G" BIGINT,
    "A" BIGINT,
    "Pts" BIGINT,
    "PIM" BIGINT
);

-- Table: ScoringShootout
CREATE TABLE "bird__hockey"."ScoringShootout" (
    "playerID" TEXT,
    "year" BIGINT,
    "stint" BIGINT,
    "tmID" TEXT,
    "S" BIGINT,
    "G" BIGINT,
    "GDG" BIGINT
);

-- Table: ScoringSup
CREATE TABLE "bird__hockey"."ScoringSup" (
    "playerID" TEXT,
    "year" BIGINT,
    "PPA" TEXT,
    "SHA" TEXT
);

-- Table: SeriesPost
CREATE TABLE "bird__hockey"."SeriesPost" (
    "year" BIGINT,
    "round" TEXT,
    "series" TEXT,
    "tmIDWinner" TEXT,
    "lgIDWinner" TEXT,
    "tmIDLoser" TEXT,
    "lgIDLoser" TEXT,
    "W" BIGINT,
    "L" BIGINT,
    "T" BIGINT,
    "GoalsWinner" BIGINT,
    "GoalsLoser" BIGINT,
    "note" TEXT
);

-- Table: TeamSplits
CREATE TABLE "bird__hockey"."TeamSplits" (
    "year" BIGINT NOT NULL,
    "lgID" TEXT,
    "tmID" TEXT NOT NULL,
    "hW" BIGINT,
    "hL" BIGINT,
    "hT" BIGINT,
    "hOTL" TEXT,
    "rW" BIGINT,
    "rL" BIGINT,
    "rT" BIGINT,
    "rOTL" TEXT,
    "SepW" TEXT,
    "SepL" TEXT,
    "SepT" TEXT,
    "SepOL" TEXT,
    "OctW" TEXT,
    "OctL" TEXT,
    "OctT" TEXT,
    "OctOL" TEXT,
    "NovW" TEXT,
    "NovL" TEXT,
    "NovT" TEXT,
    "NovOL" TEXT,
    "DecW" TEXT,
    "DecL" TEXT,
    "DecT" TEXT,
    "DecOL" TEXT,
    "JanW" BIGINT,
    "JanL" BIGINT,
    "JanT" BIGINT,
    "JanOL" TEXT,
    "FebW" BIGINT,
    "FebL" BIGINT,
    "FebT" BIGINT,
    "FebOL" TEXT,
    "MarW" TEXT,
    "MarL" TEXT,
    "MarT" TEXT,
    "MarOL" TEXT,
    "AprW" TEXT,
    "AprL" TEXT,
    "AprT" TEXT,
    "AprOL" TEXT,
    CONSTRAINT "TeamSplits_pkey" PRIMARY KEY ("year", "tmID")
);

-- Table: TeamVsTeam
CREATE TABLE "bird__hockey"."TeamVsTeam" (
    "year" BIGINT NOT NULL,
    "lgID" TEXT,
    "tmID" TEXT NOT NULL,
    "oppID" TEXT NOT NULL,
    "W" BIGINT,
    "L" BIGINT,
    "T" BIGINT,
    "OTL" TEXT,
    CONSTRAINT "TeamVsTeam_pkey" PRIMARY KEY ("year", "tmID", "oppID")
);

-- Table: Teams
CREATE TABLE "bird__hockey"."Teams" (
    "year" BIGINT NOT NULL,
    "lgID" TEXT,
    "tmID" TEXT NOT NULL,
    "franchID" TEXT,
    "confID" TEXT,
    "divID" TEXT,
    "rank" BIGINT,
    "playoff" TEXT,
    "G" BIGINT,
    "W" BIGINT,
    "L" BIGINT,
    "T" BIGINT,
    "OTL" TEXT,
    "Pts" BIGINT,
    "SoW" TEXT,
    "SoL" TEXT,
    "GF" BIGINT,
    "GA" BIGINT,
    "name" TEXT,
    "PIM" TEXT,
    "BenchMinor" TEXT,
    "PPG" TEXT,
    "PPC" TEXT,
    "SHA" TEXT,
    "PKG" TEXT,
    "PKC" TEXT,
    "SHF" TEXT,
    CONSTRAINT "Teams_pkey" PRIMARY KEY ("year", "tmID")
);

-- Table: TeamsHalf
CREATE TABLE "bird__hockey"."TeamsHalf" (
    "year" BIGINT NOT NULL,
    "lgID" TEXT,
    "tmID" TEXT NOT NULL,
    "half" BIGINT NOT NULL,
    "rank" BIGINT,
    "G" BIGINT,
    "W" BIGINT,
    "L" BIGINT,
    "T" BIGINT,
    "GF" BIGINT,
    "GA" BIGINT,
    CONSTRAINT "TeamsHalf_pkey" PRIMARY KEY ("year", "tmID", "half")
);

-- Table: TeamsPost
CREATE TABLE "bird__hockey"."TeamsPost" (
    "year" BIGINT NOT NULL,
    "lgID" TEXT,
    "tmID" TEXT NOT NULL,
    "G" BIGINT,
    "W" BIGINT,
    "L" BIGINT,
    "T" BIGINT,
    "GF" BIGINT,
    "GA" BIGINT,
    "PIM" TEXT,
    "BenchMinor" TEXT,
    "PPG" TEXT,
    "PPC" TEXT,
    "SHA" TEXT,
    "PKG" TEXT,
    "PKC" TEXT,
    "SHF" TEXT,
    CONSTRAINT "TeamsPost_pkey" PRIMARY KEY ("year", "tmID")
);

-- Table: TeamsSC
CREATE TABLE "bird__hockey"."TeamsSC" (
    "year" BIGINT NOT NULL,
    "lgID" TEXT,
    "tmID" TEXT NOT NULL,
    "G" BIGINT,
    "W" BIGINT,
    "L" BIGINT,
    "T" BIGINT,
    "GF" BIGINT,
    "GA" BIGINT,
    "PIM" TEXT,
    CONSTRAINT "TeamsSC_pkey" PRIMARY KEY ("year", "tmID")
);

-- Table: abbrev
CREATE TABLE "bird__hockey"."abbrev" (
    "Type" TEXT NOT NULL,
    "Code" TEXT NOT NULL,
    "Fullname" TEXT,
    CONSTRAINT "abbrev_pkey" PRIMARY KEY ("Type", "Code")
);

-- Foreign Keys
ALTER TABLE "bird__hockey"."Coaches" ADD CONSTRAINT "fk_Coaches_0" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."Goalies" ADD CONSTRAINT "fk_Goalies_1" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."GoaliesSC" ADD CONSTRAINT "fk_GoaliesSC_1" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."GoaliesShootout" ADD CONSTRAINT "fk_GoaliesShootout_1" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."Scoring" ADD CONSTRAINT "fk_Scoring_1" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."ScoringSC" ADD CONSTRAINT "fk_ScoringSC_1" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."ScoringShootout" ADD CONSTRAINT "fk_ScoringShootout_1" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."SeriesPost" ADD CONSTRAINT "fk_SeriesPost_0" FOREIGN KEY ("year", "year", "tmIDLoser", "tmIDLoser") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."SeriesPost" ADD CONSTRAINT "fk_SeriesPost_1" FOREIGN KEY ("year", "year", "tmIDWinner", "tmIDWinner") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."TeamSplits" ADD CONSTRAINT "fk_TeamSplits_0" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."TeamVsTeam" ADD CONSTRAINT "fk_TeamVsTeam_0" FOREIGN KEY ("oppID", "oppID", "year", "year") REFERENCES "bird__hockey"."Teams" ("tmID", "tmID", "year", "year") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."TeamVsTeam" ADD CONSTRAINT "fk_TeamVsTeam_1" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."TeamsHalf" ADD CONSTRAINT "fk_TeamsHalf_0" FOREIGN KEY ("tmID", "tmID", "year", "year") REFERENCES "bird__hockey"."Teams" ("tmID", "tmID", "year", "year") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."TeamsPost" ADD CONSTRAINT "fk_TeamsPost_0" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE "bird__hockey"."TeamsSC" ADD CONSTRAINT "fk_TeamsSC_0" FOREIGN KEY ("year", "year", "tmID", "tmID") REFERENCES "bird__hockey"."Teams" ("year", "year", "tmID", "tmID") ON DELETE CASCADE ON UPDATE CASCADE;
