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

-- Table: Customer_Addresses
CREATE TABLE "spider__customers_campaigns_ecommerce"."Customer_Addresses" (
    "customer_id" BIGINT NOT NULL,
    "premise_id" BIGINT NOT NULL,
    "date_address_from" TIMESTAMP NOT NULL,
    "address_type_code" TEXT NOT NULL,
    "date_address_to" TIMESTAMP
);

-- Table: Customer_Orders
CREATE TABLE "spider__customers_campaigns_ecommerce"."Customer_Orders" (
    "order_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "order_status_code" TEXT NOT NULL,
    "shipping_method_code" TEXT NOT NULL,
    "order_placed_datetime" TIMESTAMP NOT NULL,
    "order_delivered_datetime" TIMESTAMP,
    "order_shipping_charges" TEXT,
    CONSTRAINT "Customer_Orders_pkey" PRIMARY KEY ("order_id")
);

-- Table: Customers
CREATE TABLE "spider__customers_campaigns_ecommerce"."Customers" (
    "customer_id" BIGINT NOT NULL,
    "payment_method" TEXT NOT NULL,
    "customer_name" TEXT,
    "customer_phone" TEXT,
    "customer_email" TEXT,
    "customer_address" TEXT,
    "customer_login" TEXT,
    "customer_password" TEXT,
    CONSTRAINT "Customers_pkey" PRIMARY KEY ("customer_id")
);

-- Table: Mailshot_Campaigns
CREATE TABLE "spider__customers_campaigns_ecommerce"."Mailshot_Campaigns" (
    "mailshot_id" BIGINT NOT NULL,
    "product_category" TEXT,
    "mailshot_name" TEXT,
    "mailshot_start_date" TIMESTAMP,
    "mailshot_end_date" TIMESTAMP,
    CONSTRAINT "Mailshot_Campaigns_pkey" PRIMARY KEY ("mailshot_id")
);

-- Table: Mailshot_Customers
CREATE TABLE "spider__customers_campaigns_ecommerce"."Mailshot_Customers" (
    "mailshot_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "outcome_code" TEXT NOT NULL,
    "mailshot_customer_date" TIMESTAMP
);

-- Table: Order_Items
CREATE TABLE "spider__customers_campaigns_ecommerce"."Order_Items" (
    "item_id" BIGINT NOT NULL,
    "order_item_status_code" TEXT NOT NULL,
    "order_id" BIGINT NOT NULL,
    "product_id" BIGINT NOT NULL,
    "item_status_code" TEXT,
    "item_delivered_datetime" TIMESTAMP,
    "item_order_quantity" TEXT
);

-- Table: Premises
CREATE TABLE "spider__customers_campaigns_ecommerce"."Premises" (
    "premise_id" BIGINT NOT NULL,
    "premises_type" TEXT NOT NULL,
    "premise_details" TEXT,
    CONSTRAINT "Premises_pkey" PRIMARY KEY ("premise_id")
);

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

-- Foreign Keys
ALTER TABLE "spider__customers_campaigns_ecommerce"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_0" FOREIGN KEY ("customer_id") REFERENCES "spider__customers_campaigns_ecommerce"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_campaigns_ecommerce"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_1" FOREIGN KEY ("premise_id") REFERENCES "spider__customers_campaigns_ecommerce"."Premises" ("premise_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_campaigns_ecommerce"."Customer_Orders" ADD CONSTRAINT "fk_Customer_Orders_0" FOREIGN KEY ("customer_id") REFERENCES "spider__customers_campaigns_ecommerce"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_campaigns_ecommerce"."Mailshot_Customers" ADD CONSTRAINT "fk_Mailshot_Customers_0" FOREIGN KEY ("mailshot_id") REFERENCES "spider__customers_campaigns_ecommerce"."Mailshot_Campaigns" ("mailshot_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_campaigns_ecommerce"."Mailshot_Customers" ADD CONSTRAINT "fk_Mailshot_Customers_1" FOREIGN KEY ("customer_id") REFERENCES "spider__customers_campaigns_ecommerce"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_campaigns_ecommerce"."Order_Items" ADD CONSTRAINT "fk_Order_Items_0" FOREIGN KEY ("order_id") REFERENCES "spider__customers_campaigns_ecommerce"."Customer_Orders" ("order_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customers_campaigns_ecommerce"."Order_Items" ADD CONSTRAINT "fk_Order_Items_1" FOREIGN KEY ("product_id") REFERENCES "spider__customers_campaigns_ecommerce"."Products" ("product_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
