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

-- Table: customers
CREATE TABLE "bird__beer_factory"."customers" (
    "CustomerID" BIGINT NOT NULL,
    "First" TEXT,
    "Last" TEXT,
    "StreetAddress" TEXT,
    "City" TEXT,
    "State" TEXT,
    "ZipCode" BIGINT,
    "Email" TEXT,
    "PhoneNumber" TEXT,
    "FirstPurchaseDate" DATE,
    "SubscribedToEmailList" TEXT,
    "Gender" TEXT,
    CONSTRAINT "customers_pkey" PRIMARY KEY ("CustomerID")
);

-- Table: geolocation
CREATE TABLE "bird__beer_factory"."geolocation" (
    "LocationID" BIGINT NOT NULL,
    "Latitude" DOUBLE PRECISION,
    "Longitude" DOUBLE PRECISION,
    CONSTRAINT "geolocation_pkey" PRIMARY KEY ("LocationID")
);

-- Table: location
CREATE TABLE "bird__beer_factory"."location" (
    "LocationID" BIGINT NOT NULL,
    "LocationName" TEXT,
    "StreetAddress" TEXT,
    "City" TEXT,
    "State" TEXT,
    "ZipCode" BIGINT,
    CONSTRAINT "location_pkey" PRIMARY KEY ("LocationID")
);

-- Table: rootbeer
CREATE TABLE "bird__beer_factory"."rootbeer" (
    "RootBeerID" BIGINT NOT NULL,
    "BrandID" BIGINT,
    "ContainerType" TEXT,
    "LocationID" BIGINT,
    "PurchaseDate" DATE,
    CONSTRAINT "rootbeer_pkey" PRIMARY KEY ("RootBeerID")
);

-- Table: rootbeerbrand
CREATE TABLE "bird__beer_factory"."rootbeerbrand" (
    "BrandID" BIGINT NOT NULL,
    "BrandName" TEXT,
    "FirstBrewedYear" BIGINT,
    "BreweryName" TEXT,
    "City" TEXT,
    "State" TEXT,
    "Country" TEXT,
    "Description" TEXT,
    "CaneSugar" TEXT,
    "CornSyrup" TEXT,
    "Honey" TEXT,
    "ArtificialSweetener" TEXT,
    "Caffeinated" TEXT,
    "Alcoholic" TEXT,
    "AvailableInCans" TEXT,
    "AvailableInBottles" TEXT,
    "AvailableInKegs" TEXT,
    "Website" TEXT,
    "FacebookPage" TEXT,
    "Twitter" TEXT,
    "WholesaleCost" DOUBLE PRECISION,
    "CurrentRetailPrice" DOUBLE PRECISION,
    CONSTRAINT "rootbeerbrand_pkey" PRIMARY KEY ("BrandID")
);

-- Table: rootbeerreview
CREATE TABLE "bird__beer_factory"."rootbeerreview" (
    "CustomerID" BIGINT NOT NULL,
    "BrandID" BIGINT NOT NULL,
    "StarRating" BIGINT,
    "ReviewDate" DATE,
    "Review" TEXT,
    CONSTRAINT "rootbeerreview_pkey" PRIMARY KEY ("CustomerID", "BrandID")
);

-- Table: transaction
CREATE TABLE "bird__beer_factory"."transaction" (
    "TransactionID" BIGINT NOT NULL,
    "CreditCardNumber" BIGINT,
    "CustomerID" BIGINT,
    "TransactionDate" DATE,
    "CreditCardType" TEXT,
    "LocationID" BIGINT,
    "RootBeerID" BIGINT,
    "PurchasePrice" DOUBLE PRECISION,
    CONSTRAINT "transaction_pkey" PRIMARY KEY ("TransactionID")
);

-- Foreign Keys
ALTER TABLE "bird__beer_factory"."geolocation" ADD CONSTRAINT "fk_geolocation_0" FOREIGN KEY ("LocationID") REFERENCES "bird__beer_factory"."location" ("LocationID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."location" ADD CONSTRAINT "fk_location_0" FOREIGN KEY ("LocationID") REFERENCES "bird__beer_factory"."geolocation" ("LocationID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."rootbeer" ADD CONSTRAINT "fk_rootbeer_0" FOREIGN KEY ("BrandID") REFERENCES "bird__beer_factory"."rootbeerbrand" ("BrandID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."rootbeer" ADD CONSTRAINT "fk_rootbeer_1" FOREIGN KEY ("LocationID") REFERENCES "bird__beer_factory"."location" ("LocationID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."rootbeer" ADD CONSTRAINT "fk_rootbeer_2" FOREIGN KEY ("LocationID") REFERENCES "bird__beer_factory"."geolocation" ("LocationID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."rootbeerreview" ADD CONSTRAINT "fk_rootbeerreview_0" FOREIGN KEY ("BrandID") REFERENCES "bird__beer_factory"."rootbeerbrand" ("BrandID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."rootbeerreview" ADD CONSTRAINT "fk_rootbeerreview_1" FOREIGN KEY ("CustomerID") REFERENCES "bird__beer_factory"."customers" ("CustomerID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."transaction" ADD CONSTRAINT "fk_transaction_0" FOREIGN KEY ("RootBeerID") REFERENCES "bird__beer_factory"."rootbeer" ("RootBeerID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."transaction" ADD CONSTRAINT "fk_transaction_1" FOREIGN KEY ("LocationID") REFERENCES "bird__beer_factory"."location" ("LocationID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__beer_factory"."transaction" ADD CONSTRAINT "fk_transaction_2" FOREIGN KEY ("CustomerID") REFERENCES "bird__beer_factory"."customers" ("CustomerID") ON DELETE NO ACTION ON UPDATE NO ACTION;
