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

-- Table: Apartment_Bookings
CREATE TABLE "spider__apartment_rentals"."Apartment_Bookings" (
    "apt_booking_id" BIGINT NOT NULL,
    "apt_id" BIGINT,
    "guest_id" BIGINT NOT NULL,
    "booking_status_code" TEXT NOT NULL,
    "booking_start_date" TIMESTAMP,
    "booking_end_date" TIMESTAMP,
    CONSTRAINT "Apartment_Bookings_pkey" PRIMARY KEY ("apt_booking_id")
);

-- Table: Apartment_Buildings
CREATE TABLE "spider__apartment_rentals"."Apartment_Buildings" (
    "building_id" BIGINT NOT NULL,
    "building_short_name" TEXT,
    "building_full_name" TEXT,
    "building_description" TEXT,
    "building_address" TEXT,
    "building_manager" TEXT,
    "building_phone" TEXT,
    CONSTRAINT "Apartment_Buildings_pkey" PRIMARY KEY ("building_id")
);

-- Table: Apartment_Facilities
CREATE TABLE "spider__apartment_rentals"."Apartment_Facilities" (
    "apt_id" BIGINT NOT NULL,
    "facility_code" TEXT NOT NULL,
    CONSTRAINT "Apartment_Facilities_pkey" PRIMARY KEY ("apt_id", "facility_code")
);

-- Table: Apartments
CREATE TABLE "spider__apartment_rentals"."Apartments" (
    "apt_id" BIGINT NOT NULL,
    "building_id" BIGINT NOT NULL,
    "apt_type_code" TEXT,
    "apt_number" TEXT,
    "bathroom_count" BIGINT,
    "bedroom_count" BIGINT,
    "room_count" TEXT,
    CONSTRAINT "Apartments_pkey" PRIMARY KEY ("apt_id")
);

-- Table: Guests
CREATE TABLE "spider__apartment_rentals"."Guests" (
    "guest_id" BIGINT NOT NULL,
    "gender_code" TEXT,
    "guest_first_name" TEXT,
    "guest_last_name" TEXT,
    "date_of_birth" TIMESTAMP,
    CONSTRAINT "Guests_pkey" PRIMARY KEY ("guest_id")
);

-- Table: View_Unit_Status
CREATE TABLE "spider__apartment_rentals"."View_Unit_Status" (
    "apt_id" BIGINT,
    "apt_booking_id" BIGINT,
    "status_date" TIMESTAMP NOT NULL,
    "available_yn" TEXT,
    CONSTRAINT "View_Unit_Status_pkey" PRIMARY KEY ("status_date")
);

-- Foreign Keys
ALTER TABLE "spider__apartment_rentals"."Apartment_Bookings" ADD CONSTRAINT "fk_Apartment_Bookings_0" FOREIGN KEY ("guest_id") REFERENCES "spider__apartment_rentals"."Guests" ("guest_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__apartment_rentals"."Apartment_Bookings" ADD CONSTRAINT "fk_Apartment_Bookings_1" FOREIGN KEY ("apt_id") REFERENCES "spider__apartment_rentals"."Apartments" ("apt_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__apartment_rentals"."Apartment_Facilities" ADD CONSTRAINT "fk_Apartment_Facilities_0" FOREIGN KEY ("apt_id") REFERENCES "spider__apartment_rentals"."Apartments" ("apt_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__apartment_rentals"."Apartments" ADD CONSTRAINT "fk_Apartments_0" FOREIGN KEY ("building_id") REFERENCES "spider__apartment_rentals"."Apartment_Buildings" ("building_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__apartment_rentals"."View_Unit_Status" ADD CONSTRAINT "fk_View_Unit_Status_0" FOREIGN KEY ("apt_booking_id") REFERENCES "spider__apartment_rentals"."Apartment_Bookings" ("apt_booking_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__apartment_rentals"."View_Unit_Status" ADD CONSTRAINT "fk_View_Unit_Status_1" FOREIGN KEY ("apt_id") REFERENCES "spider__apartment_rentals"."Apartments" ("apt_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
