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

-- Table: Features
CREATE TABLE "spider__cre_Theme_park"."Features" (
    "Feature_ID" BIGINT NOT NULL,
    "Feature_Details" TEXT,
    CONSTRAINT "Features_pkey" PRIMARY KEY ("Feature_ID")
);

-- Table: Hotels
CREATE TABLE "spider__cre_Theme_park"."Hotels" (
    "hotel_id" BIGINT NOT NULL,
    "star_rating_code" TEXT NOT NULL,
    "pets_allowed_yn" TEXT,
    "price_range" DOUBLE PRECISION,
    "other_hotel_details" TEXT,
    CONSTRAINT "Hotels_pkey" PRIMARY KEY ("hotel_id")
);

-- Table: Locations
CREATE TABLE "spider__cre_Theme_park"."Locations" (
    "Location_ID" BIGINT NOT NULL,
    "Location_Name" TEXT,
    "Address" TEXT,
    "Other_Details" TEXT,
    CONSTRAINT "Locations_pkey" PRIMARY KEY ("Location_ID")
);

-- Table: Museums
CREATE TABLE "spider__cre_Theme_park"."Museums" (
    "Museum_ID" BIGINT NOT NULL,
    "Museum_Details" TEXT,
    CONSTRAINT "Museums_pkey" PRIMARY KEY ("Museum_ID")
);

-- Table: Photos
CREATE TABLE "spider__cre_Theme_park"."Photos" (
    "Photo_ID" BIGINT NOT NULL,
    "Tourist_Attraction_ID" BIGINT NOT NULL,
    "Name" TEXT,
    "Description" TEXT,
    "Filename" TEXT,
    "Other_Details" TEXT,
    CONSTRAINT "Photos_pkey" PRIMARY KEY ("Photo_ID")
);

-- Table: Ref_Attraction_Types
CREATE TABLE "spider__cre_Theme_park"."Ref_Attraction_Types" (
    "Attraction_Type_Code" TEXT NOT NULL,
    "Attraction_Type_Description" TEXT,
    CONSTRAINT "Ref_Attraction_Types_pkey" PRIMARY KEY ("Attraction_Type_Code")
);

-- Table: Ref_Hotel_Star_Ratings
CREATE TABLE "spider__cre_Theme_park"."Ref_Hotel_Star_Ratings" (
    "star_rating_code" TEXT NOT NULL,
    "star_rating_description" TEXT,
    CONSTRAINT "Ref_Hotel_Star_Ratings_pkey" PRIMARY KEY ("star_rating_code")
);

-- Table: Royal_Family
CREATE TABLE "spider__cre_Theme_park"."Royal_Family" (
    "Royal_Family_ID" BIGINT NOT NULL,
    "Royal_Family_Details" TEXT,
    CONSTRAINT "Royal_Family_pkey" PRIMARY KEY ("Royal_Family_ID")
);

-- Table: Shops
CREATE TABLE "spider__cre_Theme_park"."Shops" (
    "Shop_ID" BIGINT NOT NULL,
    "Shop_Details" TEXT,
    CONSTRAINT "Shops_pkey" PRIMARY KEY ("Shop_ID")
);

-- Table: Staff
CREATE TABLE "spider__cre_Theme_park"."Staff" (
    "Staff_ID" BIGINT NOT NULL,
    "Tourist_Attraction_ID" BIGINT NOT NULL,
    "Name" TEXT,
    "Other_Details" TEXT,
    CONSTRAINT "Staff_pkey" PRIMARY KEY ("Staff_ID")
);

-- Table: Street_Markets
CREATE TABLE "spider__cre_Theme_park"."Street_Markets" (
    "Market_ID" BIGINT NOT NULL,
    "Market_Details" TEXT,
    CONSTRAINT "Street_Markets_pkey" PRIMARY KEY ("Market_ID")
);

-- Table: Theme_Parks
CREATE TABLE "spider__cre_Theme_park"."Theme_Parks" (
    "Theme_Park_ID" BIGINT NOT NULL,
    "Theme_Park_Details" TEXT,
    CONSTRAINT "Theme_Parks_pkey" PRIMARY KEY ("Theme_Park_ID")
);

-- Table: Tourist_Attraction_Features
CREATE TABLE "spider__cre_Theme_park"."Tourist_Attraction_Features" (
    "Tourist_Attraction_ID" BIGINT NOT NULL,
    "Feature_ID" BIGINT NOT NULL,
    CONSTRAINT "Tourist_Attraction_Features_pkey" PRIMARY KEY ("Tourist_Attraction_ID", "Feature_ID")
);

-- Table: Tourist_Attractions
CREATE TABLE "spider__cre_Theme_park"."Tourist_Attractions" (
    "Tourist_Attraction_ID" BIGINT NOT NULL,
    "Attraction_Type_Code" TEXT NOT NULL,
    "Location_ID" BIGINT NOT NULL,
    "How_to_Get_There" TEXT,
    "Name" TEXT,
    "Description" TEXT,
    "Opening_Hours" TEXT,
    "Other_Details" TEXT,
    CONSTRAINT "Tourist_Attractions_pkey" PRIMARY KEY ("Tourist_Attraction_ID")
);

-- Table: Visitors
CREATE TABLE "spider__cre_Theme_park"."Visitors" (
    "Tourist_ID" BIGINT NOT NULL,
    "Tourist_Details" TEXT,
    CONSTRAINT "Visitors_pkey" PRIMARY KEY ("Tourist_ID")
);

-- Table: Visits
CREATE TABLE "spider__cre_Theme_park"."Visits" (
    "Visit_ID" BIGINT NOT NULL,
    "Tourist_Attraction_ID" BIGINT NOT NULL,
    "Tourist_ID" BIGINT NOT NULL,
    "Visit_Date" TIMESTAMP NOT NULL,
    "Visit_Details" TEXT NOT NULL,
    CONSTRAINT "Visits_pkey" PRIMARY KEY ("Visit_ID")
);

-- Foreign Keys
ALTER TABLE "spider__cre_Theme_park"."Hotels" ADD CONSTRAINT "fk_Hotels_0" FOREIGN KEY ("star_rating_code") REFERENCES "spider__cre_Theme_park"."Ref_Hotel_Star_Ratings" ("star_rating_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Museums" ADD CONSTRAINT "fk_Museums_0" FOREIGN KEY ("Museum_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Photos" ADD CONSTRAINT "fk_Photos_0" FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Royal_Family" ADD CONSTRAINT "fk_Royal_Family_0" FOREIGN KEY ("Royal_Family_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Shops" ADD CONSTRAINT "fk_Shops_0" FOREIGN KEY ("Shop_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Staff" ADD CONSTRAINT "fk_Staff_0" FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Street_Markets" ADD CONSTRAINT "fk_Street_Markets_0" FOREIGN KEY ("Market_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Theme_Parks" ADD CONSTRAINT "fk_Theme_Parks_0" FOREIGN KEY ("Theme_Park_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Tourist_Attraction_Features" ADD CONSTRAINT "fk_Tourist_Attraction_Features_0" FOREIGN KEY ("Feature_ID") REFERENCES "spider__cre_Theme_park"."Features" ("Feature_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Tourist_Attraction_Features" ADD CONSTRAINT "fk_Tourist_Attraction_Features_1" FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Tourist_Attractions" ADD CONSTRAINT "fk_Tourist_Attractions_0" FOREIGN KEY ("Attraction_Type_Code") REFERENCES "spider__cre_Theme_park"."Ref_Attraction_Types" ("Attraction_Type_Code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Tourist_Attractions" ADD CONSTRAINT "fk_Tourist_Attractions_1" FOREIGN KEY ("Location_ID") REFERENCES "spider__cre_Theme_park"."Locations" ("Location_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Visits" ADD CONSTRAINT "fk_Visits_0" FOREIGN KEY ("Tourist_ID") REFERENCES "spider__cre_Theme_park"."Visitors" ("Tourist_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__cre_Theme_park"."Visits" ADD CONSTRAINT "fk_Visits_1" FOREIGN KEY ("Tourist_Attraction_ID") REFERENCES "spider__cre_Theme_park"."Tourist_Attractions" ("Tourist_Attraction_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
