SET @OLD_UNIQUE_CHECKS = @@UNIQUE_CHECKS, UNIQUE_CHECKS = 0;
SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS = 0;

DROP DATABASE IF EXISTS TPCCH;
CREATE DATABASE TPCCH;

CREATE TABLE TPCCH.WAREHOUSE (
  W_ID       SMALLINT NOT NULL,
  W_NAME     VARCHAR(10),
  W_STREET_1 VARCHAR(20),
  W_STREET_2 VARCHAR(20),
  W_CITY     VARCHAR(20),
  W_STATE    CHAR(2),
  W_ZIP      CHAR(9),
  W_TAX      DECIMAL(4, 2),
  W_YTD      DECIMAL(12, 2),
  PRIMARY KEY (W_ID)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.DISTRICT (
  D_ID        TINYINT  NOT NULL,
  D_W_ID      SMALLINT NOT NULL,
  D_NAME      VARCHAR(10),
  D_STREET_1  VARCHAR(20),
  D_STREET_2  VARCHAR(20),
  D_CITY      VARCHAR(20),
  D_STATE     CHAR(2),
  D_ZIP       CHAR(9),
  D_TAX       DECIMAL(4, 2),
  D_YTD       DECIMAL(12, 2),
  D_NEXT_O_ID INT,
  PRIMARY KEY (D_W_ID, D_ID)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.CUSTOMER (
  C_ID           INT      NOT NULL,
  C_D_ID         TINYINT  NOT NULL,
  C_W_ID         SMALLINT NOT NULL,
  C_FIRST        VARCHAR(16),
  C_MIDDLE       CHAR(2),
  C_LAST         VARCHAR(16),
  C_STREET_1     VARCHAR(20),
  C_STREET_2     VARCHAR(20),
  C_CITY         VARCHAR(20),
  C_STATE        CHAR(2),
  C_ZIP          CHAR(9),
  C_PHONE        CHAR(16),
  C_SINCE        DATETIME,
  C_CREDIT       CHAR(2),
  C_CREDIT_LIM   BIGINT,
  C_DISCOUNT     DECIMAL(4, 2),
  C_BALANCE      DECIMAL(12, 2),
  C_YTD_PAYMENT  DECIMAL(12, 2),
  C_PAYMENT_CNT  SMALLINT,
  C_DELIVERY_CNT SMALLINT,
  C_DATA         TEXT,
  PRIMARY KEY (C_W_ID, C_D_ID, C_ID)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.HISTORY (
  H_C_ID   INT,
  H_C_D_ID TINYINT,
  H_C_W_ID SMALLINT,
  H_D_ID   TINYINT,
  H_W_ID   SMALLINT,
  H_DATE   DATETIME,
  H_AMOUNT DECIMAL(6, 2),
  H_DATA   VARCHAR(24)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.NEW_ORDERS (
  NO_O_ID INT      NOT NULL,
  NO_D_ID TINYINT  NOT NULL,
  NO_W_ID SMALLINT NOT NULL,
  PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.ORDERS (
  O_ID         INT      NOT NULL,
  O_D_ID       TINYINT  NOT NULL,
  O_W_ID       SMALLINT NOT NULL,
  O_C_ID       INT,
  O_ENTRY_D    DATETIME,
  O_CARRIER_ID TINYINT,
  O_OL_CNT     TINYINT,
  O_ALL_LOCAL  TINYINT,
  PRIMARY KEY (O_W_ID, O_D_ID, O_ID)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.ORDER_LINE (
  OL_O_ID        INT      NOT NULL,
  OL_D_ID        TINYINT  NOT NULL,
  OL_W_ID        SMALLINT NOT NULL,
  OL_NUMBER      TINYINT  NOT NULL,
  OL_I_ID        INT,
  OL_SUPPLY_W_ID SMALLINT,
  OL_DELIVERY_D  DATETIME,
  OL_QUANTITY    TINYINT,
  OL_AMOUNT      DECIMAL(6, 2),
  OL_DIST_INFO   CHAR(24),
  PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.ITEM (
  I_ID    INT NOT NULL,
  I_IM_ID INT,
  I_NAME  VARCHAR(24),
  I_PRICE DECIMAL(5, 2),
  I_DATA  VARCHAR(50),
  PRIMARY KEY (I_ID)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.STOCK (
  S_I_ID       INT      NOT NULL,
  S_W_ID       SMALLINT NOT NULL,
  S_QUANTITY   SMALLINT,
  S_DIST_01    CHAR(24),
  S_DIST_02    CHAR(24),
  S_DIST_03    CHAR(24),
  S_DIST_04    CHAR(24),
  S_DIST_05    CHAR(24),
  S_DIST_06    CHAR(24),
  S_DIST_07    CHAR(24),
  S_DIST_08    CHAR(24),
  S_DIST_09    CHAR(24),
  S_DIST_10    CHAR(24),
  S_YTD        DECIMAL(8, 0),
  S_ORDER_CNT  SMALLINT,
  S_REMOTE_CNT SMALLINT,
  S_DATA       VARCHAR(50),
  PRIMARY KEY (S_W_ID, S_I_ID)
)
  ENGINE = INNODB;


CREATE TABLE TPCCH.NATION (
  N_NATIONKEY TINYINT      NOT NULL,
  N_NAME      CHAR(25)     NOT NULL,
  N_REGIONKEY TINYINT      NOT NULL,
  N_COMMENT   VARCHAR(152) NOT NULL,
  PRIMARY KEY (N_NATIONKEY)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.SUPPLIER (
  SU_SUPPKEY   SMALLINT     NOT NULL,
  SU_NAME      CHAR(25)     NOT NULL,
  SU_ADDRESS   VARCHAR(40)  NOT NULL,
  SU_NATIONKEY TINYINT      NOT NULL,
  SU_PHONE     CHAR(15)     NOT NULL,
  SU_ACCTBAL   DECIMAL      NOT NULL,
  SU_COMMENT   VARCHAR(101) NOT NULL,
  PRIMARY KEY (SU_SUPPKEY)
)
  ENGINE = INNODB;

CREATE TABLE TPCCH.REGION (
  R_REGIONKEY TINYINT      NOT NULL,
  R_NAME      CHAR(25)     NOT NULL,
  R_COMMENT   VARCHAR(152) NOT NULL,
  PRIMARY KEY (R_REGIONKEY)
)
  ENGINE = INNODB;

SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS = @OLD_UNIQUE_CHECKS;