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

-- Table: Actual_Order_Products
CREATE TABLE "spider__customer_deliveries"."Actual_Order_Products" (
    "actual_order_id" BIGINT NOT NULL,
    "product_id" BIGINT NOT NULL
);

-- Table: Actual_Orders
CREATE TABLE "spider__customer_deliveries"."Actual_Orders" (
    "actual_order_id" BIGINT NOT NULL,
    "order_status_code" TEXT NOT NULL,
    "regular_order_id" BIGINT NOT NULL,
    "actual_order_date" TIMESTAMP,
    CONSTRAINT "Actual_Orders_pkey" PRIMARY KEY ("actual_order_id")
);

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

-- Table: Customer_Addresses
CREATE TABLE "spider__customer_deliveries"."Customer_Addresses" (
    "customer_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "date_from" TIMESTAMP NOT NULL,
    "address_type" TEXT NOT NULL,
    "date_to" TIMESTAMP
);

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

-- Table: Delivery_Route_Locations
CREATE TABLE "spider__customer_deliveries"."Delivery_Route_Locations" (
    "location_code" TEXT NOT NULL,
    "route_id" BIGINT NOT NULL,
    "location_address_id" BIGINT NOT NULL,
    "location_name" TEXT,
    CONSTRAINT "Delivery_Route_Locations_pkey" PRIMARY KEY ("location_code")
);

-- Table: Delivery_Routes
CREATE TABLE "spider__customer_deliveries"."Delivery_Routes" (
    "route_id" BIGINT NOT NULL,
    "route_name" TEXT,
    "other_route_details" TEXT,
    CONSTRAINT "Delivery_Routes_pkey" PRIMARY KEY ("route_id")
);

-- Table: Employees
CREATE TABLE "spider__customer_deliveries"."Employees" (
    "employee_id" BIGINT NOT NULL,
    "employee_address_id" BIGINT NOT NULL,
    "employee_name" TEXT,
    "employee_phone" TEXT,
    CONSTRAINT "Employees_pkey" PRIMARY KEY ("employee_id")
);

-- Table: Order_Deliveries
CREATE TABLE "spider__customer_deliveries"."Order_Deliveries" (
    "location_code" TEXT NOT NULL,
    "actual_order_id" BIGINT NOT NULL,
    "delivery_status_code" TEXT NOT NULL,
    "driver_employee_id" BIGINT NOT NULL,
    "truck_id" BIGINT NOT NULL,
    "delivery_date" TIMESTAMP
);

-- Table: Products
CREATE TABLE "spider__customer_deliveries"."Products" (
    "product_id" BIGINT NOT NULL,
    "product_name" TEXT,
    "product_price" NUMERIC(19,4),
    "product_description" TEXT,
    CONSTRAINT "Products_pkey" PRIMARY KEY ("product_id")
);

-- Table: Regular_Order_Products
CREATE TABLE "spider__customer_deliveries"."Regular_Order_Products" (
    "regular_order_id" BIGINT NOT NULL,
    "product_id" BIGINT NOT NULL
);

-- Table: Regular_Orders
CREATE TABLE "spider__customer_deliveries"."Regular_Orders" (
    "regular_order_id" BIGINT NOT NULL,
    "distributer_id" BIGINT NOT NULL,
    CONSTRAINT "Regular_Orders_pkey" PRIMARY KEY ("regular_order_id")
);

-- Table: Trucks
CREATE TABLE "spider__customer_deliveries"."Trucks" (
    "truck_id" BIGINT NOT NULL,
    "truck_licence_number" TEXT,
    "truck_details" TEXT,
    CONSTRAINT "Trucks_pkey" PRIMARY KEY ("truck_id")
);

-- Foreign Keys
ALTER TABLE "spider__customer_deliveries"."Actual_Order_Products" ADD CONSTRAINT "fk_Actual_Order_Products_0" FOREIGN KEY ("actual_order_id") REFERENCES "spider__customer_deliveries"."Actual_Orders" ("actual_order_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Actual_Order_Products" ADD CONSTRAINT "fk_Actual_Order_Products_1" FOREIGN KEY ("product_id") REFERENCES "spider__customer_deliveries"."Products" ("product_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Actual_Orders" ADD CONSTRAINT "fk_Actual_Orders_0" FOREIGN KEY ("regular_order_id") REFERENCES "spider__customer_deliveries"."Regular_Orders" ("regular_order_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_0" FOREIGN KEY ("address_id") REFERENCES "spider__customer_deliveries"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_1" FOREIGN KEY ("customer_id") REFERENCES "spider__customer_deliveries"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Delivery_Route_Locations" ADD CONSTRAINT "fk_Delivery_Route_Locations_0" FOREIGN KEY ("route_id") REFERENCES "spider__customer_deliveries"."Delivery_Routes" ("route_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Delivery_Route_Locations" ADD CONSTRAINT "fk_Delivery_Route_Locations_1" FOREIGN KEY ("location_address_id") REFERENCES "spider__customer_deliveries"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Employees" ADD CONSTRAINT "fk_Employees_0" FOREIGN KEY ("employee_address_id") REFERENCES "spider__customer_deliveries"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Order_Deliveries" ADD CONSTRAINT "fk_Order_Deliveries_0" FOREIGN KEY ("driver_employee_id") REFERENCES "spider__customer_deliveries"."Employees" ("employee_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Order_Deliveries" ADD CONSTRAINT "fk_Order_Deliveries_1" FOREIGN KEY ("location_code") REFERENCES "spider__customer_deliveries"."Delivery_Route_Locations" ("location_code") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Order_Deliveries" ADD CONSTRAINT "fk_Order_Deliveries_2" FOREIGN KEY ("actual_order_id") REFERENCES "spider__customer_deliveries"."Actual_Orders" ("actual_order_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Order_Deliveries" ADD CONSTRAINT "fk_Order_Deliveries_3" FOREIGN KEY ("truck_id") REFERENCES "spider__customer_deliveries"."Trucks" ("truck_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Regular_Order_Products" ADD CONSTRAINT "fk_Regular_Order_Products_0" FOREIGN KEY ("regular_order_id") REFERENCES "spider__customer_deliveries"."Regular_Orders" ("regular_order_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Regular_Order_Products" ADD CONSTRAINT "fk_Regular_Order_Products_1" FOREIGN KEY ("product_id") REFERENCES "spider__customer_deliveries"."Products" ("product_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_deliveries"."Regular_Orders" ADD CONSTRAINT "fk_Regular_Orders_0" FOREIGN KEY ("distributer_id") REFERENCES "spider__customer_deliveries"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
