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

-- Table: Ingredient
CREATE TABLE "bird__cookbook"."Ingredient" (
    "ingredient_id" BIGINT NOT NULL,
    "category" TEXT,
    "name" TEXT,
    "plural" TEXT,
    CONSTRAINT "Ingredient_pkey" PRIMARY KEY ("ingredient_id")
);

-- Table: Nutrition
CREATE TABLE "bird__cookbook"."Nutrition" (
    "recipe_id" BIGINT NOT NULL,
    "protein" DOUBLE PRECISION,
    "carbo" DOUBLE PRECISION,
    "alcohol" DOUBLE PRECISION,
    "total_fat" DOUBLE PRECISION,
    "sat_fat" DOUBLE PRECISION,
    "cholestrl" DOUBLE PRECISION,
    "sodium" DOUBLE PRECISION,
    "iron" DOUBLE PRECISION,
    "vitamin_c" DOUBLE PRECISION,
    "vitamin_a" DOUBLE PRECISION,
    "fiber" DOUBLE PRECISION,
    "pcnt_cal_carb" DOUBLE PRECISION,
    "pcnt_cal_fat" DOUBLE PRECISION,
    "pcnt_cal_prot" DOUBLE PRECISION,
    "calories" DOUBLE PRECISION,
    CONSTRAINT "Nutrition_pkey" PRIMARY KEY ("recipe_id")
);

-- Table: Quantity
CREATE TABLE "bird__cookbook"."Quantity" (
    "quantity_id" BIGINT NOT NULL,
    "recipe_id" BIGINT,
    "ingredient_id" BIGINT,
    "max_qty" DOUBLE PRECISION,
    "min_qty" DOUBLE PRECISION,
    "unit" TEXT,
    "preparation" TEXT,
    "optional" TEXT,
    CONSTRAINT "Quantity_pkey" PRIMARY KEY ("quantity_id")
);

-- Table: Recipe
CREATE TABLE "bird__cookbook"."Recipe" (
    "recipe_id" BIGINT NOT NULL,
    "title" TEXT,
    "subtitle" TEXT,
    "servings" BIGINT,
    "yield_unit" TEXT,
    "prep_min" BIGINT,
    "cook_min" BIGINT,
    "stnd_min" BIGINT,
    "source" TEXT,
    "intro" TEXT,
    "directions" TEXT,
    CONSTRAINT "Recipe_pkey" PRIMARY KEY ("recipe_id")
);

-- Foreign Keys
ALTER TABLE "bird__cookbook"."Nutrition" ADD CONSTRAINT "fk_Nutrition_0" FOREIGN KEY ("recipe_id") REFERENCES "bird__cookbook"."Recipe" ("recipe_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__cookbook"."Quantity" ADD CONSTRAINT "fk_Quantity_0" FOREIGN KEY ("recipe_id") REFERENCES "bird__cookbook"."Nutrition" ("recipe_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__cookbook"."Quantity" ADD CONSTRAINT "fk_Quantity_1" FOREIGN KEY ("ingredient_id") REFERENCES "bird__cookbook"."Ingredient" ("ingredient_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__cookbook"."Quantity" ADD CONSTRAINT "fk_Quantity_2" FOREIGN KEY ("recipe_id") REFERENCES "bird__cookbook"."Recipe" ("recipe_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
