/*
 * This file is used to create public table for vector engine tests
 */
create schema vector_engine;
set current_schema=vector_engine; 
 
CREATE TABLE lineitem_vec
(
    L_ORDERKEY    BIGINT NOT NULL
  , L_PARTKEY     BIGINT NOT NULL
  , L_SUPPKEY     BIGINT NOT NULL
  , L_LINENUMBER  BIGINT 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  CHAR(1) NOT NULL
  , L_LINESTATUS  CHAR(1) NOT NULL
  , L_SHIPDATE    DATE NOT NULL
  , L_COMMITDATE  DATE NOT NULL
  , L_RECEIPTDATE DATE NOT NULL
  , L_SHIPINSTRUCT CHAR(25) NOT NULL
  , L_SHIPMODE     CHAR(10) NOT NULL
  , L_COMMENT      VARCHAR(44) NOT NULL
)
with (orientation = column)
 ;
COPY lineitem_vec FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/lineitem.data' DELIMITER '|';

CREATE TABLE LINEITEM_partition
(
    L_ORDERKEY    BIGINT NOT NULL
  , L_PARTKEY     BIGINT NOT NULL
  , L_SUPPKEY     BIGINT NOT NULL
  , L_LINENUMBER  BIGINT 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  CHAR(1) NOT NULL
  , L_LINESTATUS  CHAR(1) NOT NULL
  , L_SHIPDATE    DATE NOT NULL
  , L_COMMITDATE  DATE NOT NULL
  , L_RECEIPTDATE DATE NOT NULL
  , L_SHIPINSTRUCT CHAR(25) NOT NULL
  , L_SHIPMODE     CHAR(10) NOT NULL
  , L_COMMENT      VARCHAR(44) NOT NULL
)
with (orientation = column)
 
partition by range (L_ORDERKEY)
(
     partition CstorePartition1 values less than (1000),
     partition CstorePartition2 values less than (2000),
	 partition CstorePartition3 values less than (3000),
	 partition CstorePartition4 values less than (4000),
	 partition CstorePartition5 values less than (5000),
	 partition CstorePartition6 values less than (6000),
	 partition CstorePartition7 values less than (100000)
);
COPY LINEITEM_partition FROM '@abs_srcdir@/tmp_check/datanode1/pg_copydir/lineitem.data' DELIMITER '|';

---- 
--- tables used for tpch
----
drop table customer;
drop table lineitem;
drop table nation;
drop table orders;
drop table part;
drop table partsupp;
drop table region;
drop table supplier;
CREATE TABLE customer (
    c_custkey integer NOT NULL,
    c_name character varying(25) NOT NULL,
    c_address character varying(40) NOT NULL,
    c_nationkey integer NOT NULL,
    c_phone character(15) NOT NULL,
    c_acctbal numeric(15,2) NOT NULL,
    c_mktsegment character(10) NOT NULL,
    c_comment character varying(117) NOT NULL
)
with (orientation = column)
 ;

CREATE TABLE lineitem (
    l_orderkey integer NOT NULL,
    l_partkey integer NOT NULL,
    l_suppkey integer NOT NULL,
    l_linenumber integer NOT NULL,
    l_quantity numeric(15,2) NOT NULL,
    l_extendedprice numeric(15,2) NOT NULL,
    l_discount numeric(15,2) NOT NULL,
    l_tax numeric(15,2) NOT NULL,
    l_returnflag character(1) NOT NULL,
    l_linestatus character(1) NOT NULL,
    l_shipdate date NOT NULL,
    l_commitdate date NOT NULL,
    l_receiptdate date NOT NULL,
    l_shipinstruct character(25) NOT NULL,
    l_shipmode character(10) NOT NULL,
    l_comment character varying(44) NOT NULL
)
with (orientation = column)
 ;

CREATE TABLE nation (
    n_nationkey integer NOT NULL,
    n_name character(25) NOT NULL,
    n_regionkey integer NOT NULL,
    n_comment character varying(152)
)
with (orientation = column)
 ;

CREATE TABLE orders (
    o_orderkey integer NOT NULL,
    o_custkey integer NOT NULL,
    o_orderstatus character(1) NOT NULL,
    o_totalprice numeric(15,2) NOT NULL,
    o_orderdate date NOT NULL,
    o_orderpriority character(15) NOT NULL,
    o_clerk character(15) NOT NULL,
    o_shippriority integer NOT NULL,
    o_comment character varying(79) NOT NULL
)
with (orientation = column)
 ;

CREATE TABLE part (
    p_partkey integer NOT NULL,
    p_name character varying(55) NOT NULL,
    p_mfgr character(25) NOT NULL,
    p_brand character(10) NOT NULL,
    p_type character varying(25) NOT NULL,
    p_size integer NOT NULL,
    p_container character(10) NOT NULL,
    p_retailprice numeric(15,2) NOT NULL,
    p_comment character varying(23) NOT NULL
)
with (orientation = column)
 ;

CREATE TABLE partsupp (
    ps_partkey integer NOT NULL,
    ps_suppkey integer NOT NULL,
    ps_availqty integer NOT NULL,
    ps_supplycost numeric(15,2) NOT NULL,
    ps_comment character varying(199) NOT NULL
)
with (orientation = column)
 ;

CREATE TABLE region (
    r_regionkey integer NOT NULL,
    r_name character(25) NOT NULL,
    r_comment character varying(152)
)
with (orientation = column)
 ;

CREATE TABLE supplier (
    s_suppkey integer NOT NULL,
    s_name character(25) NOT NULL,
    s_address character varying(40) NOT NULL,
    s_nationkey integer NOT NULL,
    s_phone character(15) NOT NULL,
    s_acctbal numeric(15,2) NOT NULL,
    s_comment character varying(101) NOT NULL
)
with (orientation = column)
 ;
\i @abs_srcdir@/tmp_check/datanode1/pg_copydir/tpch.data

analyze customer;
analyze lineitem;
analyze nation;
analyze orders;
analyze part;
analyze partsupp;
analyze region;
analyze supplier;

----
--- tables for ICBC
----
create schema dwSumData_act;
CREATE TABLE dwSumData_act.C03_SEMI_CRDT_CARD_ACCT
(
Data_Dt date,
Agt_Num VARCHAR(60) ,
Agt_Modif_Num VARCHAR(60) ,
Party_Id VARCHAR(30) ,
Proc_Org_Num VARCHAR(30) ,
Int_Org_Num VARCHAR(30) ,
Curr_Cd CHAR(4) ,
Open_Dt DATE ,
Year_Dpsit_Accum DECIMAL(18,2) ,
Party_Class_Cd CHAR(4) 
)with (orientation = column);

copy dwSumData_act.C03_SEMI_CRDT_CARD_ACCT from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/vec_casewhen_1.data' delimiter ',' NULL '';
copy dwSumData_act.C03_SEMI_CRDT_CARD_ACCT from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/vec_casewhen_2.data' delimiter ',' NULL '';

--test for semi join
create table ETL_JOB_DEPENDENCY
(
 etl_system         character varying(20)      ,
 etl_job            character varying(50)      ,
 dependency_system  character varying(20)      ,
 dependency_job     character varying(50)      ,
 description        character varying(200)     ,
 enable             character varying(1) 
) 
with (orientation=column) 
  ;                                           
                      
create index idx_etl_dependency_job2 on etl_job_dependency(dependency_job);
  
create table ETL_JOB_PRIORITY
(
 etl_system  character varying(20),
 etl_job     character varying(50),
 priority    numeric(5,0)  
) with (orientation=column)
 ;


create table ETL_JOB
(
 etl_system      character varying(20)         ,
 etl_job         character varying(50)         ,
 batch_no        numeric(5,0)                  ,
 batch_sum       numeric(5,0)                  ,
 etl_server      character varying(50)         ,
 description     character varying(200)        ,
 frequency       character varying(30)         ,
 jobtype         character varying(2)          ,
 batch_type      character varying(1)          ,
 enable          character varying(1)          ,
 last_starttime  character varying(19)         ,
 last_endtime    character varying(19)         ,
 last_jobstatus  character varying(20)         ,
 last_txdate     timestamp without time zone   ,
 last_filecnt    numeric(5,0)                  ,
 last_cubestatus character varying(20)         ,
 cubeflag        character varying(1)          ,
 checkflag       character varying(1)          ,
 autooff         character varying(1)          ,
 checkcalendar   character varying(1)          ,
 calendarbu      character varying(15)         ,
 runningscript   character varying(200)        ,
 jobsessionid    numeric(8,0)                  ,
 expectedrecord  numeric(5,0)                  ,
 checklaststatus character varying(1)          ,
 timetrigger     character varying(1)          ,
 jobresweight    numeric(5,0)                
) with (orientation=column)
 ;

copy etl_job from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/ETL_JOB.data' delimiter '|';

copy etl_job_dependency from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/ETL_JOB_DEPENDENCY.data' delimiter '|';

copy etl_job_priority from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/ETL_JOB_PRIORITY.data' delimiter '|';
copy etl_job_priority from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/ETL_JOB_PRIORITY.data' delimiter '|';
copy etl_job_priority from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/ETL_JOB_PRIORITY.data' delimiter '|';
copy etl_job_priority from '@abs_srcdir@/tmp_check/datanode1/pg_copydir/ETL_JOB_PRIORITY.data' delimiter '|';

analyze etl_job_priority;

--test explain_dna_file
show explain_dna_file;
set explain_dna_file = 'data';
set explain_dna_file = '@abs_srcdir@/tmp_check/datanode1/pg_copydir';
set explain_dna_file = '@abs_srcdir@/tmp_check/datanode1/pg_copydir/test.csv';
reset explain_dna_file;
