DROP TABLE IF EXISTS NATION;
DROP TABLE IF EXISTS REGION;
DROP TABLE IF EXISTS PART;
DROP TABLE IF EXISTS SUPPLIER;
DROP TABLE IF EXISTS PARTSUPP;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS ORDERS;
DROP TABLE IF EXISTS LINEITEM;

CREATE TABLE NATION(
N_NATIONKEY  Int32 NOT NULL,
N_NAME       FixedString(25) NOT NULL,
N_REGIONKEY  Int32 NOT NULL,
N_COMMENT    String)
ENGINE = MergeTree() ORDER BY (N_NATIONKEY) PRIMARY KEY (N_NATIONKEY);

CREATE TABLE REGION(
R_REGIONKEY  Int32 NOT NULL,
R_NAME       FixedString(25) NOT NULL,
R_COMMENT    String)
ENGINE = MergeTree() ORDER BY (R_REGIONKEY) PRIMARY KEY (R_REGIONKEY);

CREATE TABLE PART(
P_PARTKEY     Int32 NOT NULL,
P_NAME        String NOT NULL,
P_MFGR        FixedString(25) NOT NULL,
P_BRAND       FixedString(10) NOT NULL,
P_TYPE        String NOT NULL,
P_SIZE        Int32 NOT NULL,
P_CONTAINER   FixedString(10) NOT NULL,
P_RETAILPRICE String NOT NULL,
P_COMMENT     String NOT NULL )
ENGINE = MergeTree() ORDER BY (P_PARTKEY) PRIMARY KEY (P_PARTKEY);

CREATE TABLE SUPPLIER(
S_SUPPKEY     Int32 NOT NULL,
S_NAME        FixedString(25) NOT NULL,
S_ADDRESS     String NOT NULL,
S_NATIONKEY   Int32 NOT NULL,
S_PHONE       FixedString(15) NOT NULL,
S_ACCTBAL     DECIMAL(15,2) NOT NULL,
S_COMMENT     String NOT NULL)
ENGINE = MergeTree() ORDER BY (S_SUPPKEY) PRIMARY KEY (S_SUPPKEY);

CREATE TABLE PARTSUPP(
PS_PARTKEY     Int32 NOT NULL,
PS_SUPPKEY     Int32 NOT NULL,
PS_AVAILQTY    Int32 NOT NULL,
PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
PS_COMMENT     String NOT NULL )
ENGINE = MergeTree() ORDER BY (PS_PARTKEY) PRIMARY KEY (PS_PARTKEY);

CREATE TABLE CUSTOMER (
C_CUSTKEY     Int32 NOT NULL,
C_NAME        String NOT NULL,
C_ADDRESS     String NOT NULL,
C_NATIONKEY   Int32 NOT NULL,
C_PHONE       FixedString(15) NOT NULL,
C_ACCTBAL     String   NOT NULL,
C_MKTSEGMENT  FixedString(10) NOT NULL,
C_COMMENT     String NOT NULL)
ENGINE = MergeTree() ORDER BY (C_CUSTKEY) PRIMARY KEY (C_CUSTKEY);

CREATE TABLE ORDERS(
O_ORDERKEY       Int32 NOT NULL,
O_CUSTKEY        Int32 NOT NULL,
O_ORDERSTATUS    FixedString(1) NOT NULL,
O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
O_ORDERDATE      DATE NOT NULL,
O_ORDERPRIORITY  FixedString(15) NOT NULL,  
O_CLERK          FixedString(15) NOT NULL, 
O_SHIPPRIORITY   Int32 NOT NULL,
O_COMMENT        String NOT NULL)
ENGINE = MergeTree() ORDER BY (O_ORDERKEY) PRIMARY KEY (O_ORDERKEY);

CREATE TABLE LINEITEM(
L_ORDERKEY    Int32 NOT NULL,
L_PARTKEY     Int32 NOT NULL,
L_SUPPKEY     Int32 NOT NULL,
L_LINENUMBER  Int32 NOT NULL,
L_QUANTITY    DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
L_DISCOUNT    DECIMAL(15,2) NOT NULL,
L_TAX         DECIMAL(15,2) NOT NULL,
L_RETURNFLAG  FixedString(1) NOT NULL,
L_LINESTATUS  FixedString(1) NOT NULL,
L_SHIPDATE    DATE NOT NULL,
L_COMMITDATE  DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT FixedString(25) NOT NULL,
L_SHIPMODE     FixedString(10) NOT NULL,
L_COMMENT      String NOT NULL)
ENGINE = MergeTree() ORDER BY (L_ORDERKEY) PRIMARY KEY (L_ORDERKEY);