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

-- Table: happy_hour
CREATE TABLE "spider__coffee_shop"."happy_hour" (
    "HH_ID" BIGINT NOT NULL,
    "Shop_ID" BIGINT NOT NULL,
    "Month" TEXT NOT NULL,
    "Num_of_shaff_in_charge" BIGINT,
    CONSTRAINT "happy_hour_pkey" PRIMARY KEY ("HH_ID", "Shop_ID", "Month")
);

-- Table: happy_hour_member
CREATE TABLE "spider__coffee_shop"."happy_hour_member" (
    "HH_ID" BIGINT NOT NULL,
    "Member_ID" BIGINT NOT NULL,
    "Total_amount" DOUBLE PRECISION,
    CONSTRAINT "happy_hour_member_pkey" PRIMARY KEY ("HH_ID", "Member_ID")
);

-- Table: member
CREATE TABLE "spider__coffee_shop"."member" (
    "Member_ID" BIGINT NOT NULL,
    "Name" TEXT,
    "Membership_card" TEXT,
    "Age" BIGINT,
    "Time_of_purchase" BIGINT,
    "Level_of_membership" BIGINT,
    "Address" TEXT,
    CONSTRAINT "member_pkey" PRIMARY KEY ("Member_ID")
);

-- Table: shop
CREATE TABLE "spider__coffee_shop"."shop" (
    "Shop_ID" BIGINT NOT NULL,
    "Address" TEXT,
    "Num_of_staff" TEXT,
    "Score" DOUBLE PRECISION,
    "Open_Year" TEXT,
    CONSTRAINT "shop_pkey" PRIMARY KEY ("Shop_ID")
);

-- Foreign Keys
ALTER TABLE "spider__coffee_shop"."happy_hour" ADD CONSTRAINT "fk_happy_hour_0" FOREIGN KEY ("Shop_ID") REFERENCES "spider__coffee_shop"."shop" ("Shop_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__coffee_shop"."happy_hour_member" ADD CONSTRAINT "fk_happy_hour_member_0" FOREIGN KEY ("Member_ID") REFERENCES "spider__coffee_shop"."member" ("Member_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
