Intermediate Schema for PostgreSQL

-- Salescast Push DB schema v1 for PostgreSQL 7.3 or later
-- Create database and execute queries in it.
-- Make sure to read documentation of how to customize meta data field list
-- Important: names of the tables and required fields are CASE-Sensitive 

BEGIN TRANSACTION;

CREATE TABLE "Lokad_Items"
(
  "Id" BIGSERIAL,

  -- "Meta" data field samples
  -- "LabelProduct" varchar(256) DEFAULT NULL,
  -- "LabelSupplier" varchar(256) DEFAULT NULL,
  -- "TagColor" varchar(50) DEFAULT NULL,
  -- end of metadata field samples

  -- Inventory fields
  -- "StockOnHand" real DEFAULT NULL,
  -- "StockOnOrder" real DEFAULT NULL,
  -- "LeadTime" real DEFAULT NULL,
  -- "ServiceLevel" real DEFAULT NULL,
  -- "LotMultiplier" real DEFAULT NULL,
  -- end of inventory fields

  -- Custom
  -- "IsForecasted" boolean NULL,

  PRIMARY KEY ("Id")
);

CREATE TABLE "Lokad_Orders"
(
  "Id" BIGSERIAL,
  "Item_Id" bigint NOT NULL,
  "OrderDate" date NOT NULL,
  "Quantity" real NOT NULL,
  PRIMARY KEY ("Id")
);

ALTER TABLE "Lokad_Orders"
  ADD CONSTRAINT "FK_Lokad_Orders_Items"
  FOREIGN KEY ("Item_Id")
  REFERENCES "Lokad_Items" ("Id");

CREATE INDEX "FK_Lokad_Orders_Items" ON "Lokad_Orders" ("Item_Id" ASC);

-- This table is typically omitted in the early setup
-- (event info is usually harder to gather)
CREATE TABLE "Lokad_Events"
(
  "Id" BIGSERIAL,
  "Item_Id" bigint NOT NULL,
  "StartDate" date NOT NULL,

  -- Custom tags to describe the event
  -- "TagPromotionMechanism" varchar (50),
  -- "TagPromotionMarketing" varchar (50),
  -- "TagPromotionPackaging" varchar (50),
  -- "TagPromotionFacing" varchar (50)
  -- end of custom tags

  PRIMARY KEY ("Id")
);

ALTER TABLE "Lokad_Events"
  ADD CONSTRAINT "FK_Lokad_Events_Items"
  FOREIGN KEY ("Item_Id")
  REFERENCES "Lokad_Items" ("Id");

CREATE INDEX "FK_Lokad_Events_Items" ON "Lokad_Events" ("Item_Id" ASC);

CREATE INDEX "IX_Lokad_Orders_Query" ON "Lokad_Orders"
(
  "Item_Id" ASC,
  "OrderDate" ASC
);

--CREATE INDEX IX_Lokad_Orders_Date ON Lokad_Orders (OrderDate);

-- Include those tables only if you want forecasts to be pushed
-- into the intermediate table.
CREATE TABLE "Lokad_ForecastSync"
(
  "Id" BIGSERIAL,
  "SyncDate" date NOT NULL,
  "CompletedDate" date DEFAULT NULL,
  "SyncReference" varchar(36) NOT NULL,
  PRIMARY KEY ("Id")
);

CREATE TABLE "Lokad_Forecasts"
(
  "Id" BIGSERIAL,
  "Sync_Id" bigint NOT NULL,
  "Item_Id" bigint NOT NULL,
  "ForecastDate" date NOT NULL,
  "Quantity" real NOT NULL,
  "Accuracy" real NOT NULL,
  PRIMARY KEY ("Id")
);

ALTER TABLE "Lokad_Forecasts"
  ADD CONSTRAINT "FK_Lokad_Forecasts_ForecastSync"
  FOREIGN KEY ("Sync_Id")
  REFERENCES "Lokad_ForecastSync" ("Id");

CREATE INDEX "FK_Lokad_Forecasts_ForecastSync" ON "Lokad_Forecasts" ("Sync_Id" ASC);


-- Configure this table with the desired output format
-- for the optimization information
CREATE TABLE "Lokad_OptimizedItems"
(
  -- Required columns that SHOULD be included exactly as specified
  "Id" BIGSERIAL,
  "Sync_Id" bigint NOT NULL,
  "Item_Id" bigint NOT NULL,

  -- Optional product-specific columns
  -- if provided, they should be NULLable and match original fields from Lokad_Items
  -- "LabelSKU" varchar(256) DEFAULT NULL,
  -- "LabelSupplier" varchar(256) DEFAULT NULL,
  -- "TagColor" varchar(50) DEFAULT NULL,
  -- "StockOnHand" real DEFAULT NULL,
  -- "StockOnOrder" real DEFAULT NULL,
  -- "LeadTime" real DEFAULT NULL,
  -- "ServiceLevel" real DEFAULT NULL,
  -- end of optional data field samples

  -- optional calculated fields
  -- "ReorderPoint" real DEFAULT NULL,
  -- "OrderQuantity" real DEFAULT NULL,
  -- "StockCover" real DEFAULT NULL,
  -- "LeadDemand" real DEFAULT NULL,
  -- end of optional calculated fields

  PRIMARY KEY ("Id")
);

ALTER TABLE "Lokad_OptimizedItems"
  ADD CONSTRAINT "FK_Lokad_OptimizedItems_ForecastSync"
  FOREIGN KEY ("Sync_Id")
  REFERENCES "Lokad_ForecastSync" ("Id");

CREATE INDEX "FK_Lokad_OptimizedItems_ForecastSync" ON "Lokad_OptimizedItems" ("Sync_Id" ASC);

COMMIT TRANSACTION;