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

-- Table: Addresses
CREATE TABLE "spider__department_store"."Addresses" (
    "address_id" BIGINT NOT NULL,
    "address_details" TEXT,
    CONSTRAINT "Addresses_pkey" PRIMARY KEY ("address_id")
);

-- Table: Customer_Addresses
CREATE TABLE "spider__department_store"."Customer_Addresses" (
    "customer_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "date_from" TIMESTAMP NOT NULL,
    "date_to" TIMESTAMP,
    CONSTRAINT "Customer_Addresses_pkey" PRIMARY KEY ("customer_id", "address_id")
);

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

-- Table: Customers
CREATE TABLE "spider__department_store"."Customers" (
    "customer_id" BIGINT NOT NULL,
    "payment_method_code" TEXT NOT NULL,
    "customer_code" TEXT,
    "customer_name" TEXT,
    "customer_address" TEXT,
    "customer_phone" TEXT,
    "customer_email" TEXT,
    CONSTRAINT "Customers_pkey" PRIMARY KEY ("customer_id")
);

-- Table: Department_Store_Chain
CREATE TABLE "spider__department_store"."Department_Store_Chain" (
    "dept_store_chain_id" BIGINT NOT NULL,
    "dept_store_chain_name" TEXT,
    CONSTRAINT "Department_Store_Chain_pkey" PRIMARY KEY ("dept_store_chain_id")
);

-- Table: Department_Stores
CREATE TABLE "spider__department_store"."Department_Stores" (
    "dept_store_id" BIGINT NOT NULL,
    "dept_store_chain_id" BIGINT,
    "store_name" TEXT,
    "store_address" TEXT,
    "store_phone" TEXT,
    "store_email" TEXT,
    CONSTRAINT "Department_Stores_pkey" PRIMARY KEY ("dept_store_id")
);

-- Table: Departments
CREATE TABLE "spider__department_store"."Departments" (
    "department_id" BIGINT NOT NULL,
    "dept_store_id" BIGINT NOT NULL,
    "department_name" TEXT,
    CONSTRAINT "Departments_pkey" PRIMARY KEY ("department_id")
);

-- Table: Order_Items
CREATE TABLE "spider__department_store"."Order_Items" (
    "order_item_id" BIGINT NOT NULL,
    "order_id" BIGINT NOT NULL,
    "product_id" BIGINT NOT NULL,
    CONSTRAINT "Order_Items_pkey" PRIMARY KEY ("order_item_id")
);

-- Table: Product_Suppliers
CREATE TABLE "spider__department_store"."Product_Suppliers" (
    "product_id" BIGINT NOT NULL,
    "supplier_id" BIGINT NOT NULL,
    "date_supplied_from" TIMESTAMP NOT NULL,
    "date_supplied_to" TIMESTAMP,
    "total_amount_purchased" TEXT,
    "total_value_purchased" NUMERIC(19,4),
    CONSTRAINT "Product_Suppliers_pkey" PRIMARY KEY ("product_id", "supplier_id")
);

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

-- Table: Staff
CREATE TABLE "spider__department_store"."Staff" (
    "staff_id" BIGINT NOT NULL,
    "staff_gender" TEXT,
    "staff_name" TEXT,
    CONSTRAINT "Staff_pkey" PRIMARY KEY ("staff_id")
);

-- Table: Staff_Department_Assignments
CREATE TABLE "spider__department_store"."Staff_Department_Assignments" (
    "staff_id" BIGINT NOT NULL,
    "department_id" BIGINT NOT NULL,
    "date_assigned_from" TIMESTAMP NOT NULL,
    "job_title_code" TEXT NOT NULL,
    "date_assigned_to" TIMESTAMP,
    CONSTRAINT "Staff_Department_Assignments_pkey" PRIMARY KEY ("staff_id", "department_id")
);

-- Table: Supplier_Addresses
CREATE TABLE "spider__department_store"."Supplier_Addresses" (
    "supplier_id" BIGINT NOT NULL,
    "address_id" BIGINT NOT NULL,
    "date_from" TIMESTAMP NOT NULL,
    "date_to" TIMESTAMP,
    CONSTRAINT "Supplier_Addresses_pkey" PRIMARY KEY ("supplier_id", "address_id")
);

-- Table: Suppliers
CREATE TABLE "spider__department_store"."Suppliers" (
    "supplier_id" BIGINT NOT NULL,
    "supplier_name" TEXT,
    "supplier_phone" TEXT,
    CONSTRAINT "Suppliers_pkey" PRIMARY KEY ("supplier_id")
);

-- Foreign Keys
ALTER TABLE "spider__department_store"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_0" FOREIGN KEY ("customer_id") REFERENCES "spider__department_store"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Customer_Addresses" ADD CONSTRAINT "fk_Customer_Addresses_1" FOREIGN KEY ("address_id") REFERENCES "spider__department_store"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Customer_Orders" ADD CONSTRAINT "fk_Customer_Orders_0" FOREIGN KEY ("customer_id") REFERENCES "spider__department_store"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Department_Stores" ADD CONSTRAINT "fk_Department_Stores_0" FOREIGN KEY ("dept_store_chain_id") REFERENCES "spider__department_store"."Department_Store_Chain" ("dept_store_chain_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Departments" ADD CONSTRAINT "fk_Departments_0" FOREIGN KEY ("dept_store_id") REFERENCES "spider__department_store"."Department_Stores" ("dept_store_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Order_Items" ADD CONSTRAINT "fk_Order_Items_0" FOREIGN KEY ("product_id") REFERENCES "spider__department_store"."Products" ("product_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Order_Items" ADD CONSTRAINT "fk_Order_Items_1" FOREIGN KEY ("order_id") REFERENCES "spider__department_store"."Customer_Orders" ("order_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Product_Suppliers" ADD CONSTRAINT "fk_Product_Suppliers_0" FOREIGN KEY ("product_id") REFERENCES "spider__department_store"."Products" ("product_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Product_Suppliers" ADD CONSTRAINT "fk_Product_Suppliers_1" FOREIGN KEY ("supplier_id") REFERENCES "spider__department_store"."Suppliers" ("supplier_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Staff_Department_Assignments" ADD CONSTRAINT "fk_Staff_Department_Assignments_0" FOREIGN KEY ("staff_id") REFERENCES "spider__department_store"."Staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Staff_Department_Assignments" ADD CONSTRAINT "fk_Staff_Department_Assignments_1" FOREIGN KEY ("department_id") REFERENCES "spider__department_store"."Departments" ("department_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Supplier_Addresses" ADD CONSTRAINT "fk_Supplier_Addresses_0" FOREIGN KEY ("supplier_id") REFERENCES "spider__department_store"."Suppliers" ("supplier_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__department_store"."Supplier_Addresses" ADD CONSTRAINT "fk_Supplier_Addresses_1" FOREIGN KEY ("address_id") REFERENCES "spider__department_store"."Addresses" ("address_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
