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

-- Table: Complaints
CREATE TABLE "spider__customer_complaints"."Complaints" (
    "complaint_id" BIGINT NOT NULL,
    "product_id" BIGINT NOT NULL,
    "customer_id" BIGINT NOT NULL,
    "complaint_outcome_code" TEXT NOT NULL,
    "complaint_status_code" TEXT NOT NULL,
    "complaint_type_code" TEXT NOT NULL,
    "date_complaint_raised" TIMESTAMP,
    "date_complaint_closed" TIMESTAMP,
    "staff_id" BIGINT NOT NULL
);

-- Table: Customers
CREATE TABLE "spider__customer_complaints"."Customers" (
    "customer_id" BIGINT NOT NULL,
    "customer_type_code" TEXT NOT NULL,
    "address_line_1" TEXT,
    "address_line_2" TEXT,
    "town_city" TEXT,
    "state" TEXT,
    "email_address" TEXT,
    "phone_number" TEXT,
    CONSTRAINT "Customers_pkey" PRIMARY KEY ("customer_id")
);

-- Table: Products
CREATE TABLE "spider__customer_complaints"."Products" (
    "product_id" BIGINT NOT NULL,
    "parent_product_id" BIGINT,
    "product_category_code" TEXT NOT NULL,
    "date_product_first_available" TIMESTAMP,
    "date_product_discontinued" TIMESTAMP,
    "product_name" TEXT,
    "product_description" TEXT,
    "product_price" NUMERIC(19,4),
    CONSTRAINT "Products_pkey" PRIMARY KEY ("product_id")
);

-- Table: Staff
CREATE TABLE "spider__customer_complaints"."Staff" (
    "staff_id" BIGINT NOT NULL,
    "gender" TEXT,
    "first_name" TEXT,
    "last_name" TEXT,
    "email_address" TEXT,
    "phone_number" TEXT,
    CONSTRAINT "Staff_pkey" PRIMARY KEY ("staff_id")
);

-- Foreign Keys
ALTER TABLE "spider__customer_complaints"."Complaints" ADD CONSTRAINT "fk_Complaints_0" FOREIGN KEY ("customer_id") REFERENCES "spider__customer_complaints"."Customers" ("customer_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_complaints"."Complaints" ADD CONSTRAINT "fk_Complaints_1" FOREIGN KEY ("product_id") REFERENCES "spider__customer_complaints"."Products" ("product_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "spider__customer_complaints"."Complaints" ADD CONSTRAINT "fk_Complaints_2" FOREIGN KEY ("staff_id") REFERENCES "spider__customer_complaints"."Staff" ("staff_id") ON DELETE NO ACTION ON UPDATE NO ACTION;
