-- TPC-H 8张表建表语句
DROP TABLE IF EXISTS LINEITEM;
DROP TABLE IF EXISTS PARTSUPP;
DROP TABLE IF EXISTS ORDERS;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS SUPPLIER;
DROP TABLE IF EXISTS PART;
DROP TABLE IF EXISTS NATION;
DROP TABLE IF EXISTS REGION;

CREATE TABLE REGION (
    R_REGIONKEY INTEGER PRIMARY KEY,
    R_NAME VARCHAR(25)
);

CREATE TABLE NATION (
    N_NATIONKEY INTEGER PRIMARY KEY,
    N_NAME VARCHAR(25),
    N_REGIONKEY INTEGER REFERENCES REGION(R_REGIONKEY)
);

CREATE TABLE PART (
    P_PARTKEY INTEGER PRIMARY KEY,
    P_NAME VARCHAR(55),
    P_MFGR VARCHAR(25),
    P_BRAND VARCHAR(10),
    P_TYPE VARCHAR(25),
    P_SIZE INTEGER,
    P_CONTAINER VARCHAR(10),
    P_RETAILPRICE NUMERIC
);

CREATE TABLE SUPPLIER (
    S_SUPPKEY INTEGER PRIMARY KEY,
    S_NAME VARCHAR(25),
    S_ADDRESS VARCHAR(40),
    S_NATIONKEY INTEGER REFERENCES NATION(N_NATIONKEY),
    S_PHONE VARCHAR(15),
    S_ACCTBAL NUMERIC
);

CREATE TABLE CUSTOMER (
    C_CUSTKEY INTEGER PRIMARY KEY,
    C_NAME VARCHAR(25),
    C_ADDRESS VARCHAR(40),
    C_NATIONKEY INTEGER REFERENCES NATION(N_NATIONKEY),
    C_PHONE VARCHAR(15),
    C_ACCTBAL NUMERIC,
    C_MKTSEGMENT VARCHAR(10)
);

CREATE TABLE ORDERS (
    O_ORDERKEY INTEGER PRIMARY KEY,
    O_CUSTKEY INTEGER REFERENCES CUSTOMER(C_CUSTKEY),
    O_ORDERSTATUS VARCHAR(1),
    O_TOTALPRICE NUMERIC,
    O_ORDERDATE DATE,
    O_ORDERPRIORITY VARCHAR(15),
    O_CLERK VARCHAR(15),
    O_SHIPPRIORITY INTEGER
);

CREATE TABLE PARTSUPP (
    PS_PARTKEY INTEGER REFERENCES PART(P_PARTKEY),
    PS_SUPPKEY INTEGER REFERENCES SUPPLIER(S_SUPPKEY),
    PS_AVAILQTY INTEGER,
    PS_SUPPLYCOST NUMERIC,
    PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY)
);

CREATE TABLE LINEITEM (
    L_ORDERKEY INTEGER REFERENCES ORDERS(O_ORDERKEY),
    L_PARTKEY INTEGER,
    L_SUPPKEY INTEGER,
    L_LINENUMBER INTEGER,
    L_QUANTITY NUMERIC,
    L_EXTENDEDPRICE NUMERIC,
    L_DISCOUNT NUMERIC,
    L_TAX NUMERIC,
    L_RETURNFLAG VARCHAR(1),
    L_LINESTATUS VARCHAR(1),
    L_SHIPDATE DATE,
    L_COMMITDATE DATE,
    L_RECEIPTDATE DATE,
    L_SHIPINSTRUCT VARCHAR(25),
    L_SHIPMODE VARCHAR(10),
    PRIMARY KEY (L_ORDERKEY, L_LINENUMBER)
);

-- 用 COPY 批量导入（CSV带表头，用HEADER选项）
-- 注意：请将以下路径替换为你实际放置 CSV 文件的目录路径
\copy REGION FROM './TPCH_REGION_5.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
\copy NATION FROM './TPCH_NATION_25.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
\copy PART FROM './TPCH_PART_10.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
\copy SUPPLIER FROM './TPCH_supplier.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
\copy CUSTOMER FROM './TPCH_customer.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
\copy ORDERS FROM './TPCH_ORDERS_12380.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
\copy PARTSUPP FROM './TPCH_PARTSUPP_159750.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
\copy LINEITEM FROM './TPCH_LINEITEM_59206.csv' WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');
