/**************************************************************************
  phoenix.i - master DDL to create tables for phoenix database 
 **************************************************************************/

/**************************************************************************
  catalog tables
 **************************************************************************/
CREATE TABLE category (
  uid                SERIAL PRIMARY KEY,
  parentuid          INTEGER,
  name               VARCHAR NOT NULL,
  description        VARCHAR,
  image              VARCHAR,
  ordering           INTEGER,
  visible            BOOLEAN NOT NULL
);

CREATE TABLE categoryi18n (
  uid                SERIAL PRIMARY KEY,
  categoryuid        INTEGER NOT NULL REFERENCES category(uid),
  lang               VARCHAR NOT NULL,
  name               VARCHAR,
  description        VARCHAR
);

CREATE TABLE styletype (
  uid                SERIAL PRIMARY KEY,
  code               VARCHAR UNIQUE,  -- e.g. mensShoes
  description        VARCHAR,         -- e.g. 'Men''s Shoes' (for CSI)
  hascolor           BOOLEAN,
  haswidth           BOOLEAN,
  hassize            BOOLEAN,
  taxable            BOOLEAN,
  trackinventory     BOOLEAN,
  acsproduct         BOOLEAN
);

CREATE TABLE styletypei18n (
  uid                SERIAL PRIMARY KEY,
  styletypeuid       INTEGER NOT NULL REFERENCES styletype(uid),
  lang               VARCHAR NOT NULL,
  description        VARCHAR
);

CREATE TABLE style (
  uid                SERIAL PRIMARY KEY,
  code               VARCHAR UNIQUE,
  name               VARCHAR NOT NULL,
  shortdesc          VARCHAR DEFAULT '',
  longdesc           VARCHAR DEFAULT '',
  image              VARCHAR,
  techimage1         VARCHAR,
  techimage2         VARCHAR,
  featuredcolorcode  VARCHAR,  -- not uid (dup codes the same except group)
  visible            BOOLEAN NOT NULL,
  deleted            BOOLEAN DEFAULT FALSE,
  styletypeuid       INTEGER NOT NULL REFERENCES styletype(uid)
);   

CREATE TABLE stylei18n (
  uid                SERIAL PRIMARY KEY,
  styleuid           INTEGER NOT NULL REFERENCES style(uid),
  lang               VARCHAR NOT NULL,
  shortdesc          VARCHAR,
  longdesc           VARCHAR
);

CREATE TABLE stylecategory (
  uid                SERIAL PRIMARY KEY,
  styleuid           INTEGER NOT NULL REFERENCES style(uid),
  categoryuid        INTEGER NOT NULL REFERENCES category(uid),
  ordering           INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE skucolor (
  uid                SERIAL PRIMARY KEY,
  code               VARCHAR NOT NULL, -- note: not unique!
  description        VARCHAR NOT NULL,
  colorgroup         VARCHAR           -- for finder
);

CREATE TABLE skucolori18n (
  uid                SERIAL PRIMARY KEY,
  skucoloruid        INTEGER NOT NULL REFERENCES skucolor(uid),
  lang               VARCHAR NOT NULL,
  description        VARCHAR,
  colorgroup         VARCHAR           -- for finder
);

CREATE TABLE skuwidth (
  uid                SERIAL PRIMARY KEY,
  code               VARCHAR NOT NULL, -- note: not unique!
  description        VARCHAR NOT NULL,
  widthgroup         VARCHAR,          -- for finder
  ordering           INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE skuwidthi18n (
  uid                SERIAL PRIMARY KEY,
  skuwidthuid        INTEGER NOT NULL REFERENCES skuwidth(uid),
  lang               VARCHAR NOT NULL,
  description        VARCHAR,
  widthgroup         VARCHAR           -- for finder
);

CREATE TABLE skusize (
  uid                SERIAL PRIMARY KEY,
  acssize            VARCHAR,
  websize            VARCHAR NOT NULL,  -- e.g. 'S'
  description        VARCHAR NOT NULL,  -- e.g. 'S (fits 6W-8M)'
  styletypeuid       INTEGER REFERENCES styletype(uid),
  ordering           INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE skusizei18n (
  uid                SERIAL PRIMARY KEY,
  skusizeuid         INTEGER NOT NULL REFERENCES skusize(uid),
  lang               VARCHAR NOT NULL,
  description        VARCHAR
);

CREATE TABLE sku (
  uid                SERIAL PRIMARY KEY,
  styleuid           INTEGER REFERENCES style(uid),
  skucoloruid        INTEGER REFERENCES skucolor(uid),
  skuwidthuid        INTEGER REFERENCES skuwidth(uid),
  skusizeuid         INTEGER REFERENCES skusize(uid), -- e.g. '10.5'
  name               VARCHAR NOT NULL, -- e.g. '00012-01 10.5 D'
  code               VARCHAR,          -- note: UPC
  image              VARCHAR NOT NULL,
  price              NUMERIC(8, 2) NOT NULL,
  saleprice          NUMERIC(8, 2),
  clearance          BOOLEAN DEFAULT FALSE,
  oddsends           BOOLEAN DEFAULT FALSE,
  qtyavailable       INTEGER DEFAULT 0,
  visible            BOOLEAN NOT NULL,
  deleted            BOOLEAN DEFAULT FALSE
);
  
CREATE TABLE xselling (
  uid                SERIAL PRIMARY KEY,
  fromstyleuid       INTEGER NOT NULL REFERENCES style(uid),
  fromskucoloruid    INTEGER REFERENCES skucolor(uid),
  tostyleuid         INTEGER NOT NULL REFERENCES style(uid),
  toskucoloruid      INTEGER REFERENCES skucolor(uid),
  ordering           INTEGER DEFAULT 0
);

/**************************************************************************
  csiuser table
 **************************************************************************/
CREATE TABLE csiuser (
  uid         SERIAL PRIMARY KEY,
  userid      VARCHAR UNIQUE NOT NULL,
  name        VARCHAR NOT NULL,
  password    VARCHAR NOT NULL,
  accesslevel INTEGER
);

/**************************************************************************
  tax and freight tables
 **************************************************************************/
CREATE TABLE state (
  uid                SERIAL PRIMARY KEY,
  code               CHAR(2) UNIQUE,
  description        VARCHAR NOT NULL,
  taxrate            NUMERIC(8,2) NOT NULL
);

CREATE TABLE statei18n (
  uid                SERIAL PRIMARY KEY,
  stateuid           INTEGER NOT NULL REFERENCES state(uid),
  lang               VARCHAR NOT NULL,
  description        VARCHAR NOT NULL
);

CREATE TABLE shippingservice (
  uid                SERIAL PRIMARY KEY,
  code               VARCHAR UNIQUE,   -- e.g. 'ExpeditedLower48Ups'
  description        VARCHAR NOT NULL, 
  carriername        VARCHAR NOT NULL, 
  image              VARCHAR,
  acsshipvia         CHAR(3) NOT NULL, -- e.g. '2H '
  deleted            BOOLEAN DEFAULT FALSE
);

CREATE TABLE shippingservicei18n (
  uid                SERIAL PRIMARY KEY,
  shippingserviceuid     INTEGER NOT NULL REFERENCES shippingservice(uid),
  lang               VARCHAR NOT NULL,
  description        VARCHAR NOT NULL,
  carriername        VARCHAR NOT NULL      
);

CREATE TABLE stateshippingservice (
  uid                SERIAL PRIMARY KEY,
  shippinbserviceuid INTEGER NOT NULL REFERENCES shippingservice(uid),
  stateuid           INTEGER NOT NULL REFERENCES state(uid),
  amount             NUMERIC(8,2)
);

/**************************************************************************
  customer tables
 **************************************************************************/
CREATE TABLE signup (
  uid                SERIAL PRIMARY KEY, 
  email              VARCHAR NOT NULL,
  created            TIMESTAMP NOT NULL,
  signuptype         VARCHAR NOT NULL CHECK (signuptype in (
                             'Email', 'Opt-in' ))
);
  
CREATE TABLE customer ( 
  uid                SERIAL PRIMARY KEY,
  email              VARCHAR UNIQUE NOT NULL, 
  password           VARCHAR,
  optin              BOOLEAN,
  firstname          VARCHAR,
  lastname           VARCHAR,
  lastbilladdruid    INTEGER,
  lastshipaddruid    INTEGER,
  lastpaymethoduid   INTEGER, -- null if never ordered
  created            TIMESTAMP NOT NULL,
  cookie             VARCHAR,
  deleted            BOOLEAN DEFAULT FALSE
);

CREATE TABLE address (
  uid                SERIAL PRIMARY KEY,
  customeruid        INTEGER NOT NULL REFERENCES customer(uid),
  alias              VARCHAR, -- e.g. "home", "work", etc.
  firstname          VARCHAR NOT NULL,
  lastname           VARCHAR NOT NULL,
  street1            VARCHAR NOT NULL,
  street2            VARCHAR,
  city               VARCHAR NOT NULL,
  state              CHAR(2) NOT NULL,
  zipcode            VARCHAR NOT NULL,
  country            CHAR(2) NOT NULL, -- for now always 'US'
  phone              VARCHAR NOT NULL,
  commercial         BOOLEAN,
  deleted            BOOLEAN DEFAULT FALSE
);

CREATE TABLE paymethod (
  uid                SERIAL PRIMARY KEY,
  customeruid        INTEGER NOT NULL REFERENCES customer(uid), 
  accountid          VARCHAR NOT NULL, -- credit card # or PayPal signin
  type               VARCHAR NOT NULL CHECK (type in (
                             'Visa',
                             'Master Card', 
                             'American Express',
                             'Discover',
                             'PayPal',
                             'Gift Card' )),
  accountholder      VARCHAR,  
  expmonth           INTEGER,
  expyear            INTEGER
);

/**************************************************************************
  history tables
 **************************************************************************/
CREATE TABLE optinlog (
  uid                SERIAL PRIMARY KEY,
  email              VARCHAR UNIQUE NOT NULL,
  optedin            BOOLEAN,
  atcreation         BOOLEAN,
  logged             TIMESTAMP NOT NULL
);

CREATE TABLE searchlog ( 
  uid                SERIAL PRIMARY KEY,
  keywords           VARCHAR,  
  typed              BOOLEAN,
  findercolor        VARCHAR,
  finderwidth        VARCHAR,
  findersize         VARCHAR,
  matches            INTEGER NOT NULL,
  deadmatch          BOOLEAN,  
  suggestedterms     INTEGER NOT NULL, 
  logged             TIMESTAMP NOT NULL
);

/**************************************************************************
  shopping cart tables
 **************************************************************************/
CREATE TABLE cart ( 
  uid                SERIAL PRIMARY KEY,
  modified           TIMESTAMP NOT NULL,
  cookie             VARCHAR
);

CREATE TABLE cartline (
  uid                SERIAL PRIMARY KEY,
  cartuid            INTEGER NOT NULL,
  skuuid             INTEGER NOT NULL,
  quantity           INTEGER NOT NULL,
  wishlistitem       BOOLEAN
);

CREATE TABLE giftcard (
  uid                SERIAL PRIMARY KEY,
  accountid          CHAR(16) NOT NULL,
  pin                char(4),
  created            TIMESTAMP,
  ipaddress          VARCHAR,
  amountavailable    NUMERIC(8, 2),
  amountused         NUMERIC(8, 2),
  referencenumber    INTEGER, 
  transactiontype    VARCHAR CHECK (transactiontype in (
                             'Authorization',
                             'Sale' )),
  validated          BOOLEAN, 
  completespurchase  BOOLEAN, 
  orderheaderuid     INTEGER
);
/* Non-data record used to hold NOVA last record no.  
   This number is unique by NOVA terminal ID.
*/
INSERT INTO giftcard
       (uid, accountid, referencenumber, validated, completespurchase, 
        orderheaderuid)
       VALUES(1, '0000000000000000', 0, false, false, 0);

/**************************************************************************
  order tables
 **************************************************************************/
CREATE TABLE orderheader (
  uid                SERIAL PRIMARY KEY, -- also used as order number
  ipaddress          VARCHAR NOT NULL,
  customeruid        INTEGER NOT NULL REFERENCES customer(uid), 
  created            TIMESTAMP NOT NULL,
  status             VARCHAR CHECK (status in (
                             'Placed', 
                             'Uploaded to ACS', 
                             'Partially Shipped', 
                             'Shipped', 
                             'Canceled', 
                             'Returned' )),
  billaddruid        INTEGER,       -- may be null (e.g. PayPal)
  shipaddruid        INTEGER NOT NULL,
  ttlprice           NUMERIC(8, 2), -- sum (saleprice * qty)
  ttldiscount        NUMERIC(8, 2), -- sum linediscount + order disc
  promocode          VARCHAR,
  shippingamount     NUMERIC(8, 2),
  taxamount          NUMERIC(8, 2),
  shippingserviceuid INTEGER NOT NULL REFERENCES shippingservice(uid),
  tracking           VARCHAR,
  imported           BOOLEAN DEFAULT false -- imported from EP?
);

CREATE TABLE orderline (
  uid                SERIAL PRIMARY KEY,
  orderheaderuid     INTEGER NOT NULL REFERENCES orderheader(uid),
  styleuid           INTEGER NOT NULL,
  skuuid             INTEGER NOT NULL,
  quantity           INTEGER, 
  linediscount       NUMERIC(8, 2), -- item discount$ for this line
  saleprice          NUMERIC(8, 2), -- prices at time of purchase
  listprice          NUMERIC(8, 2)  
);

CREATE TABLE ordernote (
  uid                SERIAL PRIMARY KEY,
  orderheaderuid     INTEGER NOT NULL references orderheader(uid),
  csiuseruid         INTEGER NOT NULL references csiuser(uid),           
  note               VARCHAR,
  modified           TIMESTAMP NOT NULL
);

CREATE TABLE acsorder (
  uid                SERIAL PRIMARY KEY, 
  orderheaderuid     INTEGER NOT NULL REFERENCES orderheader(uid),
  division           CHAR(2),
  acsordernumber     INTEGER,
  acsconfnumber      INTEGER
);

CREATE TABLE payment (
  uid                SERIAL PRIMARY KEY,
  orderheaderuid     INTEGER NOT NULL REFERENCES orderheader(uid),           
  created            TIMESTAMP NOT NULL,
  accountid          VARCHAR NOT NULL, -- credit card # or PayPal signon
  type               VARCHAR NOT NULL CHECK (type in (
                             'Visa',
                             'Master Card', 
                             'American Express',
                             'Discover',
                             'PayPal',
                             'Gift Card' )),
  accountholder      VARCHAR,        
  expmonth           INTEGER,
  expyear            INTEGER,
  gateway            VARCHAR, -- name of payment gateway module 
  amount             NUMERIC(8,2),
  referenceid        VARCHAR,
  transactiontype    VARCHAR CHECK (transactiontype in (
                             'Authorization',
                             'Sale' )),
  authorizationcode  VARCHAR
);

/**************************************************************************
  return tables
 **************************************************************************/
CREATE TABLE returnheader (
  uid                 SERIAL PRIMARY KEY,
  orderheaderuid      INTEGER NOT NULL REFERENCES orderheader(uid),
  reason              VARCHAR NOT NULL CHECK (reason in (
                              'Arrived Late',
                              'Changed Mind',
                              'Defective or Missing Parts',
                              'Did not Order Anything',
                              'Damaged in Transit',
                              'Poor Fit',
                              'Returning Gift',
                              'Shipment Incomplete',
                              'Unhappy with Color or Quality',
                              'Wrong Item',
                              'Unspecified',
                              'Other' )),
  created             TIMESTAMP NOT NULL,
  rmacode             VARCHAR,
  beforetaxamount     NUMERIC(8,2), -- refunded amt from mdse + shipping
  taxamount           NUMERIC(8,2)  -- refunded tax (Weyco does not pay)
);

CREATE TABLE returnline (
  uid                 SERIAL PRIMARY KEY,
  returnuid           INTEGER NOT NULL REFERENCES returnheader(uid),
  orderlineuid        INTEGER NOT NULL REFERENCES orderline(uid),
  quantity            INTEGER NOT NULL,
  amount              NUMERIC(8,2)
);

/**************************************************************************
  store locator tables
 **************************************************************************/
CREATE TABLE soldto(
  uid                 SERIAL PRIMARY KEY,
  name                VARCHAR NOT NULL,
  address1            VARCHAR NOT NULL,
  address2            VARCHAR,
  city                VARCHAR NOT NULL,
  state               CHAR(2) NOT NULL,
  zipcode             VARCHAR NOT NULL,
  country             CHAR(2), -- for now always 'US'
  phone               VARCHAR UNIQUE,
  changedate          TIMESTAMP,
  changeuser          VARCHAR NOT NULL,
  shoes               BOOLEAN,
  belts               BOOLEAN,
  hats                BOOLEAN,
  jewelry             BOOLEAN,
  leather             BOOLEAN,
  shirts              BOOLEAN,
  socks               BOOLEAN,
  sportswear          BOOLEAN,
  suits               BOOLEAN,
  ties                BOOLEAN
);

CREATE TABLE sales(
  uid                 SERIAL PRIMARY KEY,
  soldtouid           INTEGER NOT NULL REFERENCES soldto(uid),
  year                INTEGER,
  quarter             INTEGER,
  amount              NUMERIC(11, 2),
  changedate          TIMESTAMP,
  changeuser          VARCHAR,
  category            INTEGER
);

CREATE TABLE shipto(
  uid                 SERIAL PRIMARY KEY,
  name                VARCHAR NOT NULL,
  address1            VARCHAR NOT NULL,
  address2            VARCHAR,
  city                VARCHAR NOT NULL,
  state               CHAR(2) NOT NULL,
  zipcode             VARCHAR NOT NULL,
  country             CHAR(2), -- for now always 'US'
  phone               VARCHAR NOT NULL,
  soldtophone         VARCHAR,
  latitude            DOUBLE PRECISION,
  longitude           DOUBLE PRECISION,
  ours                BOOLEAN,
  changedate          TIMESTAMP,
  changeuser          VARCHAR NOT NULL,
  shoes               BOOLEAN,
  belts               BOOLEAN,
  hats                BOOLEAN,
  jewelry             BOOLEAN,
  leather             BOOLEAN,
  shirts              BOOLEAN,
  socks               BOOLEAN,
  sportswear          BOOLEAN,
  suits               BOOLEAN,
  ties                BOOLEAN
);

CREATE TABLE ziploc(
  zipcode             CHAR(5) UNIQUE PRIMARY KEY,
  state               CHAR(2),
  latitude            DOUBLE PRECISION,
  longitude           DOUBLE PRECISION
);

/**************************************************************************
  linkshare tables
 **************************************************************************/
CREATE TABLE linksharetracking (
  uid                 SERIAL PRIMARY KEY,
  sessionid           VARCHAR NOT NULL,
  siteid              VARCHAR NOT NULL,
  created             TIMESTAMP
);

CREATE TABLE linkshareorder (
  uid                 SERIAL PRIMARY KEY,
  trackinguid         INTEGER NOT NULL REFERENCES linksharetracking(uid),
  orderheaderuid      INTEGER NOT NULL REFERENCES orderheader(uid),
  ordered             TIMESTAMP,
  shipped             TIMESTAMP,
  returned            TIMESTAMP,
  shipmentwritten     TIMESTAMP,
  returnwritten       TIMESTAMP
);

CREATE TABLE linkshareskureturn (
  uid     SERIAL PRIMARY KEY,
  skuuid  INTEGER NOT NULL
);

/**************************************************************************
  promotion tables
 **************************************************************************/
CREATE TABLE promo (
  uid             SERIAL PRIMARY KEY,
  code            VARCHAR,
  name            VARCHAR,
  description     VARCHAR, 
  start           TIMESTAMP,
  stop            TIMESTAMP, 
  combinable      BOOLEAN
);

CREATE TABLE promoi18n (
  uid             SERIAL PRIMARY KEY,
  promouid        INTEGER NOT NULL REFERENCES promo(uid),
  lang            VARCHAR NOT NULL,
  name            VARCHAR,
  description     VARCHAR
);

CREATE TABLE promoqualifier (
  uid             SERIAL PRIMARY KEY,
  promouid        INTEGER NOT NULL REFERENCES promo(uid),
  function        VARCHAR, --enum.
  parameter1      VARCHAR, 
  parameter2      VARCHAR 
);

CREATE TABLE promoaction (
  uid             SERIAL PRIMARY KEY,
  promouid        INTEGER REFERENCES promo(uid),
  function        VARCHAR, --enum.
  parameter1      VARCHAR,
  parameter2      VARCHAR,
  parameter3      VARCHAR
);

/**************************************************************************
  transient tables (to be eventually deleted)
 **************************************************************************/
-- which EP address did a Phoenix address come from? for loader (no JPA)
CREATE TABLE epaddress (
  addressuid      INTEGER PRIMARY KEY,
  epaddressuid    INTEGER
);