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

-- Table: Categories
CREATE TABLE "ecommerce__northwind"."Categories" (
    "CategoryID" BIGINT NOT NULL,
    "CategoryName" TEXT,
    "Description" TEXT,
    "Picture" BYTEA,
    CONSTRAINT "Categories_pkey" PRIMARY KEY ("CategoryID")
);

-- Table: CustomerCustomerDemo
CREATE TABLE "ecommerce__northwind"."CustomerCustomerDemo" (
    "CustomerID" TEXT NOT NULL,
    "CustomerTypeID" TEXT NOT NULL,
    CONSTRAINT "CustomerCustomerDemo_pkey" PRIMARY KEY ("CustomerID", "CustomerTypeID")
);

-- Table: CustomerDemographics
CREATE TABLE "ecommerce__northwind"."CustomerDemographics" (
    "CustomerTypeID" TEXT NOT NULL,
    "CustomerDesc" TEXT,
    CONSTRAINT "CustomerDemographics_pkey" PRIMARY KEY ("CustomerTypeID")
);

-- Table: Customers
CREATE TABLE "ecommerce__northwind"."Customers" (
    "CustomerID" TEXT NOT NULL,
    "CompanyName" TEXT,
    "ContactName" TEXT,
    "ContactTitle" TEXT,
    "Address" TEXT,
    "City" TEXT,
    "Region" TEXT,
    "PostalCode" TEXT,
    "Country" TEXT,
    "Phone" TEXT,
    "Fax" TEXT,
    CONSTRAINT "Customers_pkey" PRIMARY KEY ("CustomerID")
);

-- Table: EmployeeTerritories
CREATE TABLE "ecommerce__northwind"."EmployeeTerritories" (
    "EmployeeID" BIGINT NOT NULL,
    "TerritoryID" TEXT NOT NULL,
    CONSTRAINT "EmployeeTerritories_pkey" PRIMARY KEY ("EmployeeID", "TerritoryID")
);

-- Table: Employees
CREATE TABLE "ecommerce__northwind"."Employees" (
    "EmployeeID" BIGINT NOT NULL,
    "LastName" TEXT,
    "FirstName" TEXT,
    "Title" TEXT,
    "TitleOfCourtesy" TEXT,
    "BirthDate" DATE,
    "HireDate" DATE,
    "Address" TEXT,
    "City" TEXT,
    "Region" TEXT,
    "PostalCode" TEXT,
    "Country" TEXT,
    "HomePhone" TEXT,
    "Extension" TEXT,
    "Photo" BYTEA,
    "Notes" TEXT,
    "ReportsTo" BIGINT,
    "PhotoPath" TEXT,
    CONSTRAINT "Employees_pkey" PRIMARY KEY ("EmployeeID")
);

-- Table: Order Details
CREATE TABLE "ecommerce__northwind"."Order Details" (
    "OrderID" BIGINT NOT NULL,
    "ProductID" BIGINT NOT NULL,
    "UnitPrice" NUMERIC NOT NULL DEFAULT 0,
    "Quantity" BIGINT NOT NULL DEFAULT 1,
    "Discount" DOUBLE PRECISION NOT NULL DEFAULT 0,
    CONSTRAINT "Order Details_pkey" PRIMARY KEY ("OrderID", "ProductID")
);

-- Table: Orders
CREATE TABLE "ecommerce__northwind"."Orders" (
    "OrderID" BIGINT NOT NULL,
    "CustomerID" TEXT,
    "EmployeeID" BIGINT,
    "OrderDate" TIMESTAMP,
    "RequiredDate" TIMESTAMP,
    "ShippedDate" TIMESTAMP,
    "ShipVia" BIGINT,
    "Freight" NUMERIC DEFAULT 0,
    "ShipName" TEXT,
    "ShipAddress" TEXT,
    "ShipCity" TEXT,
    "ShipRegion" TEXT,
    "ShipPostalCode" TEXT,
    "ShipCountry" TEXT,
    CONSTRAINT "Orders_pkey" PRIMARY KEY ("OrderID")
);

-- Table: Products
CREATE TABLE "ecommerce__northwind"."Products" (
    "ProductID" BIGINT NOT NULL,
    "ProductName" TEXT NOT NULL,
    "SupplierID" BIGINT,
    "CategoryID" BIGINT,
    "QuantityPerUnit" TEXT,
    "UnitPrice" NUMERIC DEFAULT 0,
    "UnitsInStock" BIGINT DEFAULT 0,
    "UnitsOnOrder" BIGINT DEFAULT 0,
    "ReorderLevel" BIGINT DEFAULT 0,
    "Discontinued" TEXT NOT NULL DEFAULT '0'::text,
    CONSTRAINT "Products_pkey" PRIMARY KEY ("ProductID")
);

-- Table: Regions
CREATE TABLE "ecommerce__northwind"."Regions" (
    "RegionID" BIGINT NOT NULL,
    "RegionDescription" TEXT NOT NULL,
    CONSTRAINT "Regions_pkey" PRIMARY KEY ("RegionID")
);

-- Table: Shippers
CREATE TABLE "ecommerce__northwind"."Shippers" (
    "ShipperID" BIGINT NOT NULL,
    "CompanyName" TEXT NOT NULL,
    "Phone" TEXT,
    CONSTRAINT "Shippers_pkey" PRIMARY KEY ("ShipperID")
);

-- Table: Suppliers
CREATE TABLE "ecommerce__northwind"."Suppliers" (
    "SupplierID" BIGINT NOT NULL,
    "CompanyName" TEXT NOT NULL,
    "ContactName" TEXT,
    "ContactTitle" TEXT,
    "Address" TEXT,
    "City" TEXT,
    "Region" TEXT,
    "PostalCode" TEXT,
    "Country" TEXT,
    "Phone" TEXT,
    "Fax" TEXT,
    "HomePage" TEXT,
    CONSTRAINT "Suppliers_pkey" PRIMARY KEY ("SupplierID")
);

-- Table: Territories
CREATE TABLE "ecommerce__northwind"."Territories" (
    "TerritoryID" TEXT NOT NULL,
    "TerritoryDescription" TEXT NOT NULL,
    "RegionID" BIGINT NOT NULL,
    CONSTRAINT "Territories_pkey" PRIMARY KEY ("TerritoryID")
);

-- Foreign Keys
ALTER TABLE "ecommerce__northwind"."CustomerCustomerDemo" ADD CONSTRAINT "fk_CustomerCustomerDemo_0" FOREIGN KEY ("CustomerTypeID") REFERENCES "ecommerce__northwind"."CustomerDemographics" ("CustomerTypeID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."CustomerCustomerDemo" ADD CONSTRAINT "fk_CustomerCustomerDemo_1" FOREIGN KEY ("CustomerID") REFERENCES "ecommerce__northwind"."Customers" ("CustomerID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."EmployeeTerritories" ADD CONSTRAINT "fk_EmployeeTerritories_0" FOREIGN KEY ("TerritoryID") REFERENCES "ecommerce__northwind"."Territories" ("TerritoryID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."EmployeeTerritories" ADD CONSTRAINT "fk_EmployeeTerritories_1" FOREIGN KEY ("EmployeeID") REFERENCES "ecommerce__northwind"."Employees" ("EmployeeID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Employees" ADD CONSTRAINT "fk_Employees_0" FOREIGN KEY ("ReportsTo") REFERENCES "ecommerce__northwind"."Employees" ("EmployeeID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Order Details" ADD CONSTRAINT "fk_Order Details_0" FOREIGN KEY ("ProductID") REFERENCES "ecommerce__northwind"."Products" ("ProductID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Order Details" ADD CONSTRAINT "fk_Order Details_1" FOREIGN KEY ("OrderID") REFERENCES "ecommerce__northwind"."Orders" ("OrderID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Orders" ADD CONSTRAINT "fk_Orders_0" FOREIGN KEY ("ShipVia") REFERENCES "ecommerce__northwind"."Shippers" ("ShipperID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Orders" ADD CONSTRAINT "fk_Orders_1" FOREIGN KEY ("CustomerID") REFERENCES "ecommerce__northwind"."Customers" ("CustomerID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Orders" ADD CONSTRAINT "fk_Orders_2" FOREIGN KEY ("EmployeeID") REFERENCES "ecommerce__northwind"."Employees" ("EmployeeID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Products" ADD CONSTRAINT "fk_Products_0" FOREIGN KEY ("SupplierID") REFERENCES "ecommerce__northwind"."Suppliers" ("SupplierID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Products" ADD CONSTRAINT "fk_Products_1" FOREIGN KEY ("CategoryID") REFERENCES "ecommerce__northwind"."Categories" ("CategoryID") ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE "ecommerce__northwind"."Territories" ADD CONSTRAINT "fk_Territories_0" FOREIGN KEY ("RegionID") REFERENCES "ecommerce__northwind"."Regions" ("RegionID") ON DELETE NO ACTION ON UPDATE NO ACTION;
