-- The column ordering here and in the loader (LoadSupplier.java) must match, 
-- so that flat file generation properly maps to columns

DROP TABLE S_COMPONENT;
CREATE TABLE S_COMPONENT
(
   COMP_ID        char(20) not null,
   COMP_SITE_ID   integer not null,
   COMP_NAME      char(10),
   COMP_DESC      varchar(100),
   COMP_UNIT      char(10),
   COMP_COST      numeric(12,2),
   QTY_ON_ORDER   integer,
   QTY_DEMANDED   integer,
   LEAD_TIME      integer,
   CONTAINER_SIZE integer,
   COMP_VERSION   integer default 0
);

CREATE UNIQUE INDEX S_COMP_IDX ON S_COMPONENT (COMP_SITE_ID, COMP_ID);

DROP TABLE S_SUPP_COMPONENT;
CREATE TABLE S_SUPP_COMPONENT
(
   SC_P_ID        char(20) not null,
   SC_SUPP_ID     integer not null,
   SC_PRICE       numeric(12,2),
   SC_QTY         integer,
   SC_DISCOUNT    numeric(6,4),
   SC_DEL_DATE    integer,
   SC_VERSION     integer default 0
);

CREATE UNIQUE INDEX S_SC_IDX ON S_SUPP_COMPONENT (SC_P_ID, SC_SUPP_ID);

DROP TABLE S_SUPPLIER;
CREATE TABLE S_SUPPLIER
(
   SUPP_ID        integer not null,
   SUPP_NAME      char(16),
   SUPP_STREET1   char(20),
   SUPP_STREET2   char(20),
   SUPP_CITY      char(20),
   SUPP_STATE     char(2),
   SUPP_COUNTRY   char(10),
   SUPP_ZIP       char(9),
   SUPP_PHONE     char(16),
   SUPP_CONTACT   char(25),
   SUPP_WS_URL    varchar(128),
   SUPP_REPLY_URL varchar(128),
   SUPP_VERSION   integer default 0
);

CREATE UNIQUE INDEX S_SUPP_IDX ON S_SUPPLIER (SUPP_ID);

DROP TABLE S_PURCH_ORDER;
CREATE TABLE S_PURCH_ORDER
(
   PO_NUMBER      integer not null,
   PO_SITE_ID     integer not null,
   PO_SUPP_ID     integer,
   PO_POPULAR_SUPP integer,
   PO_START_DATE  timestamp,
   PO_SENT_DATE   date,
   PO_VERSION     integer default 0
);

CREATE UNIQUE INDEX S_PO_IDX ON S_PURCH_ORDER (PO_SITE_ID, PO_NUMBER);
CREATE INDEX S_PODATE_IDX ON S_PURCH_ORDER (PO_SITE_ID, PO_START_DATE);

DROP TABLE S_PURCH_ORDERLINE;
CREATE TABLE S_PURCH_ORDERLINE
(
   POL_NUMBER     integer not null,
   POL_PO_ID      integer not null,
   POL_LOCATION   integer not null,
   POL_P_ID       char(20),
   POL_QTY        integer,
   POL_BALANCE    numeric(12,2),
   POL_LEADTIME   integer default 0,
   POL_DELDATE    date,
   POL_MESSAGE    varchar(100),
   POL_VERSION    integer default 0
);

CREATE UNIQUE INDEX S_POL_IDX ON S_PURCH_ORDERLINE (POL_LOCATION, POL_PO_ID, POL_NUMBER);


DROP TABLE S_RUN_CONFIG;
CREATE TABLE S_RUN_CONFIG
(
   name        	varchar(20) not null,
   value       	integer not null
);
