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

DROP TABLE O_CUSTINVENTORY;
CREATE TABLE O_CUSTINVENTORY
(
   CI_ID          integer not null,
   CI_CUSTOMERID  integer not null,
   CI_ITEMID      char(20),
   CI_QUANTITY    integer,
   CI_VALUE       numeric(12,2),
   CI_VERSION     integer default 0
);

CREATE UNIQUE INDEX O_CI_IDX ON O_CUSTINVENTORY(CI_CUSTOMERID, CI_ID);

DROP TABLE O_CUSTOMER;
CREATE TABLE O_CUSTOMER
(
   C_ID           integer not null,
   C_FIRST        char(16),
   C_LAST         char(16),
   C_STREET1      char(20),
   C_STREET2      char(20),
   C_CITY         char(20),
   C_STATE        char(2),
   C_COUNTRY      char(10),
   C_ZIP          char(9),
   C_PHONE        char(16),
   C_CONTACT      char(25),
   C_SINCE        date,
   C_BALANCE      numeric(12,2),
   C_CREDIT       char(2),
   C_CREDIT_LIMIT numeric(12,2),
   C_YTD_PAYMENT  numeric(12,2),
   C_VERSION      integer default 0
);

CREATE UNIQUE INDEX O_C_IDX ON O_CUSTOMER (C_ID);

DROP TABLE O_ORDERS;
CREATE TABLE O_ORDERS
(
   O_ID           integer not null,
   O_C_ID         integer,
   O_OL_CNT       integer,
   O_DISCOUNT     numeric(4,2),
   O_TOTAL        numeric(12,2),
   O_STATUS       integer,
   O_ENTRY_DATE   timestamp,
   O_SHIP_DATE    date,
   O_VERSION      integer default 0
);

CREATE UNIQUE INDEX O_ORDS_IDX ON O_ORDERS (O_ID);

CREATE INDEX O_OC_IDX ON O_ORDERS (O_C_ID);

DROP TABLE O_ORDERLINE;
CREATE TABLE O_ORDERLINE
(
   OL_ID          integer not null,
   OL_O_ID        integer not null,
   OL_I_ID        char(20),
   OL_QTY         integer,
   OL_TOTAL_VALUE numeric(12,2),
   OL_MSRP        numeric(12,2),
   OL_STATUS      integer,
   OL_SHIP_DATE   timestamp,
   OL_VERSION     integer default 0
);

CREATE UNIQUE INDEX O_ORDL_IDX ON O_ORDERLINE (OL_O_ID, OL_ID);

DROP TABLE O_ITEM;
CREATE TABLE O_ITEM
(
   I_ID           char(20) not null,
   I_NAME         char(35),
   I_DESC         varchar(100),
   I_PRICE        numeric(12,2),
   I_DISCOUNT     numeric(6,4),
   I_CATEGORY     integer not null,
   I_VERSION      integer default 0
);

CREATE UNIQUE INDEX O_I_IDX ON O_ITEM (I_ID);

CREATE INDEX O_ICAT_IDX ON O_ITEM (I_CATEGORY);

