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

-- Table: customers
CREATE TABLE "bird__car_retails"."customers" (
    "customerNumber" BIGINT NOT NULL,
    "customerName" TEXT NOT NULL,
    "contactLastName" TEXT NOT NULL,
    "contactFirstName" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "addressLine1" TEXT NOT NULL,
    "addressLine2" TEXT,
    "city" TEXT NOT NULL,
    "state" TEXT,
    "postalCode" TEXT,
    "country" TEXT NOT NULL,
    "salesRepEmployeeNumber" BIGINT,
    "creditLimit" DOUBLE PRECISION,
    CONSTRAINT "customers_pkey" PRIMARY KEY ("customerNumber")
);

-- Table: employees
CREATE TABLE "bird__car_retails"."employees" (
    "employeeNumber" BIGINT NOT NULL,
    "lastName" TEXT NOT NULL,
    "firstName" TEXT NOT NULL,
    "extension" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "officeCode" TEXT NOT NULL,
    "reportsTo" BIGINT,
    "jobTitle" TEXT NOT NULL,
    CONSTRAINT "employees_pkey" PRIMARY KEY ("employeeNumber")
);

-- Table: offices
CREATE TABLE "bird__car_retails"."offices" (
    "officeCode" TEXT NOT NULL,
    "city" TEXT NOT NULL,
    "phone" TEXT NOT NULL,
    "addressLine1" TEXT NOT NULL,
    "addressLine2" TEXT,
    "state" TEXT,
    "country" TEXT NOT NULL,
    "postalCode" TEXT NOT NULL,
    "territory" TEXT NOT NULL,
    CONSTRAINT "offices_pkey" PRIMARY KEY ("officeCode")
);

-- Table: orderdetails
CREATE TABLE "bird__car_retails"."orderdetails" (
    "orderNumber" BIGINT NOT NULL,
    "productCode" TEXT NOT NULL,
    "quantityOrdered" BIGINT NOT NULL,
    "priceEach" DOUBLE PRECISION NOT NULL,
    "orderLineNumber" BIGINT NOT NULL,
    CONSTRAINT "orderdetails_pkey" PRIMARY KEY ("orderNumber", "productCode")
);

-- Table: orders
CREATE TABLE "bird__car_retails"."orders" (
    "orderNumber" BIGINT NOT NULL,
    "orderDate" DATE NOT NULL,
    "requiredDate" DATE NOT NULL,
    "shippedDate" DATE,
    "status" TEXT NOT NULL,
    "comments" TEXT,
    "customerNumber" BIGINT NOT NULL,
    CONSTRAINT "orders_pkey" PRIMARY KEY ("orderNumber")
);

-- Table: payments
CREATE TABLE "bird__car_retails"."payments" (
    "customerNumber" BIGINT NOT NULL,
    "checkNumber" TEXT NOT NULL,
    "paymentDate" DATE NOT NULL,
    "amount" DOUBLE PRECISION NOT NULL,
    CONSTRAINT "payments_pkey" PRIMARY KEY ("customerNumber", "checkNumber")
);

-- Table: productlines
CREATE TABLE "bird__car_retails"."productlines" (
    "productLine" TEXT NOT NULL,
    "textDescription" TEXT,
    "htmlDescription" TEXT,
    "image" BYTEA,
    CONSTRAINT "productlines_pkey" PRIMARY KEY ("productLine")
);

-- Table: products
CREATE TABLE "bird__car_retails"."products" (
    "productCode" TEXT NOT NULL,
    "productName" TEXT NOT NULL,
    "productLine" TEXT NOT NULL,
    "productScale" TEXT NOT NULL,
    "productVendor" TEXT NOT NULL,
    "productDescription" TEXT NOT NULL,
    "quantityInStock" BIGINT NOT NULL,
    "buyPrice" DOUBLE PRECISION NOT NULL,
    "MSRP" DOUBLE PRECISION NOT NULL,
    CONSTRAINT "products_pkey" PRIMARY KEY ("productCode")
);

-- Foreign Keys
ALTER TABLE "bird__car_retails"."customers" ADD CONSTRAINT "fk_customers_0" FOREIGN KEY ("salesRepEmployeeNumber") REFERENCES "bird__car_retails"."employees" ("employeeNumber") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__car_retails"."employees" ADD CONSTRAINT "fk_employees_0" FOREIGN KEY ("reportsTo") REFERENCES "bird__car_retails"."employees" ("employeeNumber") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__car_retails"."employees" ADD CONSTRAINT "fk_employees_1" FOREIGN KEY ("officeCode") REFERENCES "bird__car_retails"."offices" ("officeCode") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__car_retails"."orders" ADD CONSTRAINT "fk_orders_0" FOREIGN KEY ("customerNumber") REFERENCES "bird__car_retails"."customers" ("customerNumber") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__car_retails"."payments" ADD CONSTRAINT "fk_payments_0" FOREIGN KEY ("customerNumber") REFERENCES "bird__car_retails"."customers" ("customerNumber") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "bird__car_retails"."products" ADD CONSTRAINT "fk_products_0" FOREIGN KEY ("productLine") REFERENCES "bird__car_retails"."productlines" ("productLine") ON DELETE NO ACTION ON UPDATE NO ACTION;
