WIP Schema

WIP Schema.sql

-- Start Migrations

-- Migration 20200921191728-mig-1
CREATE TABLE "public"."User" (
"id" SERIAL,
"email" text   NOT NULL ,
"firstName" text   NOT NULL ,
"middleName" text   ,
"lastName" text   NOT NULL ,
PRIMARY KEY ("id")
)
CREATE TABLE "public"."PointOfSales" (
"id" SERIAL,
"name" text   NOT NULL ,
PRIMARY KEY ("id")
)
CREATE TABLE "public"."PointOfSalesAddress" (
"pointOfSalesId" integer   NOT NULL ,
"id" SERIAL,
"street" text   NOT NULL ,
"state" text   NOT NULL ,
"county" text   NOT NULL ,
"locality" text   NOT NULL ,
PRIMARY KEY ("pointOfSalesId","id")
)
CREATE TABLE "public"."Company" (
"id" SERIAL,
"name" text   NOT NULL ,
PRIMARY KEY ("id")
)
CREATE TABLE "public"."CompanyAddress" (
"companyId" integer   NOT NULL ,
"id" SERIAL,
"street" text   NOT NULL ,
"state" text   NOT NULL ,
"county" text   NOT NULL ,
"locality" text   NOT NULL ,
PRIMARY KEY ("companyId","id")
)
CREATE TABLE "public"."ProductSupplierMapping" (
"productId" integer   NOT NULL ,
"companyId" integer   NOT NULL ,
PRIMARY KEY ("productId","companyId")
)
CREATE TABLE "public"."Product" (
"id" SERIAL,
"categoryId" integer   NOT NULL ,
"name" text   NOT NULL ,
PRIMARY KEY ("id")
)
CREATE TABLE "public"."ProductSku" (
"productId" integer   NOT NULL ,
"id" text   NOT NULL ,
"name" text   NOT NULL ,
"fractions" integer   NOT NULL DEFAULT 1,
PRIMARY KEY ("productId","id")
)
CREATE TABLE "public"."ProductCategory" (
"id" SERIAL,
"name" text   NOT NULL ,
PRIMARY KEY ("id")
)
CREATE TABLE "public"."Stock" (
"pointOfSalesId" integer   NOT NULL ,
"productId" integer   NOT NULL ,
"productSkuId" text   NOT NULL ,
"stockMovementId" integer   NOT NULL ,
"stockTypeId" integer   NOT NULL ,
"quantity" integer   NOT NULL ,
"fractionsQuantity" integer   NOT NULL ,
PRIMARY KEY ("pointOfSalesId","productId","productSkuId")
)
CREATE TABLE "public"."StockType" (
"id" SERIAL,
"name" text   NOT NULL ,
PRIMARY KEY ("id")
)
CREATE TABLE "public"."StockMovement" (
"id" SERIAL,
"pointOfSalesId" integer   NOT NULL ,
"timestamp" timestamp(3)   NOT NULL ,
"descriptionInfo" text   ,
PRIMARY KEY ("id")
)
CREATE UNIQUE INDEX "User.email_unique" ON "public"."User"("email")
CREATE UNIQUE INDEX "PointOfSales.name_unique" ON "public"."PointOfSales"("name")
CREATE UNIQUE INDEX "PointOfSalesAddress_pointOfSalesId_unique" ON "public"."PointOfSalesAddress"("pointOfSalesId")
CREATE UNIQUE INDEX "Company.name_unique" ON "public"."Company"("name")
CREATE UNIQUE INDEX "ProductSku.name_unique" ON "public"."ProductSku"("name")
CREATE UNIQUE INDEX "ProductCategory.name_unique" ON "public"."ProductCategory"("name")
CREATE UNIQUE INDEX "StockType.name_unique" ON "public"."StockType"("name")
CREATE UNIQUE INDEX "StockMovement.timestamp_unique" ON "public"."StockMovement"("timestamp")
ALTER TABLE "public"."PointOfSalesAddress" ADD FOREIGN KEY ("pointOfSalesId")REFERENCES "public"."PointOfSales"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."CompanyAddress" ADD FOREIGN KEY ("companyId")REFERENCES "public"."Company"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."ProductSupplierMapping" ADD FOREIGN KEY ("productId")REFERENCES "public"."Product"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."ProductSupplierMapping" ADD FOREIGN KEY ("companyId")REFERENCES "public"."Company"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."Product" ADD FOREIGN KEY ("categoryId")REFERENCES "public"."ProductCategory"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."ProductSku" ADD FOREIGN KEY ("productId")REFERENCES "public"."Product"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."Stock" ADD FOREIGN KEY ("pointOfSalesId")REFERENCES "public"."PointOfSales"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."Stock" ADD FOREIGN KEY ("productId")REFERENCES "public"."Product"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."Stock" ADD FOREIGN KEY ("productId", "productSkuId")REFERENCES "public"."ProductSku"("productId","id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."Stock" ADD FOREIGN KEY ("stockMovementId")REFERENCES "public"."StockMovement"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."Stock" ADD FOREIGN KEY ("stockTypeId")REFERENCES "public"."StockType"("id") ON DELETE CASCADE ON UPDATE CASCADE
ALTER TABLE "public"."StockMovement" ADD FOREIGN KEY ("pointOfSalesId")REFERENCES "public"."PointOfSales"("id") ON DELETE CASCADE ON UPDATE CASCADE