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

-- Table: Addresses
CREATE TABLE "spider__customers_and_addresses"."Addresses" (
    "address_id" BIGINT NOT NULL,
    "address_content" TEXT,
    "city" TEXT,
    "zip_postcode" TEXT,
    "state_province_county" TEXT,
    "country" TEXT,
    "other_address_details" TEXT,
    CONSTRAINT "Addresses_pkey" PRIMARY KEY ("address_id")
);

-- Table: Customer_Addresses
CREATE TABLE "spider__customers_and_addresses"."Customer_Addresses" (
    "customer_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "date_address_from" TIMESTAMP NOT NULL,
    "address_type" TEXT NOT NULL,
    "date_address_to" TIMESTAMP
);

-- Table: Customer_Contact_Channels
CREATE TABLE "spider__customers_and_addresses"."Customer_Contact_Channels" (
    "customer_id" BIGINT NOT NULL,
    "channel_code" TEXT NOT NULL,
    "active_from_date" TIMESTAMP NOT NULL,
    "active_to_date" TIMESTAMP,
    "contact_number" TEXT NOT NULL
);

-- Table: Customer_Orders
CREATE TABLE "spider__customers_and_addresses"."Customer_Orders" (
    "order_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "order_status" TEXT NOT NULL,
    "order_date" TIMESTAMP,
    "order_details" TEXT,
    CONSTRAINT "Customer_Orders_pkey" PRIMARY KEY ("order_id")
);

-- Table: Customers
CREATE TABLE "spider__customers_and_addresses"."Customers" (
    "customer_id" BIGINT NOT NULL,
    "payment_method" TEXT NOT NULL,
    "customer_name" TEXT,
    "date_became_customer" TIMESTAMP,
    "other_customer_details" TEXT,
    CONSTRAINT "Customers_pkey" PRIMARY KEY ("customer_id")
);

-- Table: Order_Items
CREATE TABLE "spider__customers_and_addresses"."Order_Items" (
    "order_id" BIGINT NOT NULL,
    "product_id" BIGINT NOT NULL,
    "order_quantity" TEXT
);

-- Table: Products
CREATE TABLE "spider__customers_and_addresses"."Products" (
    "product_id" BIGINT NOT NULL,
    "product_details" TEXT,
    CONSTRAINT "Products_pkey" PRIMARY KEY ("product_id")
);

-- Foreign Keys
ALTER TABLE "spider__customers_and_addresses"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_0" FOREIGN KEY ("customer_id") REFERENCES "spider__customers_and_addresses"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_and_addresses"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_1" FOREIGN KEY ("address_id") REFERENCES "spider__customers_and_addresses"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_and_addresses"."Customer_Contact_Channels" ADD CONSTRAINT "fk_Customer_Contact_Channels_0" FOREIGN KEY ("customer_id") REFERENCES "spider__customers_and_addresses"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_and_addresses"."Customer_Orders" ADD CONSTRAINT "fk_Customer_Orders_0" FOREIGN KEY ("customer_id") REFERENCES "spider__customers_and_addresses"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_and_addresses"."Order_Items" ADD CONSTRAINT "fk_Order_Items_0" FOREIGN KEY ("order_id") REFERENCES "spider__customers_and_addresses"."Customer_Orders" ("order_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_and_addresses"."Order_Items" ADD CONSTRAINT "fk_Order_Items_1" FOREIGN KEY ("product_id") REFERENCES "spider__customers_and_addresses"."Products" ("product_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
