# name: test/sql/tpch/tpch_sf1_struct.test_slow
# description: Test TPC-H SF1
# group: [tpch]

require tpch

load __TEST_DIR__/tpch_sf1_struct.db

statement ok
CALL dbgen(sf=1, suffix='_normal');

statement ok
CREATE TABLE lineitem_struct AS SELECT {
'l_orderkey': l_orderkey,
'l_partkey': l_partkey,
'l_suppkey': l_suppkey,
'l_linenumber': l_linenumber,
'l_quantity': l_quantity,
'l_extendedprice': l_extendedprice,
'l_discount': l_discount,
'l_tax': l_tax,
'l_returnflag': l_returnflag,
'l_linestatus': l_linestatus,
'l_shipdate': l_shipdate,
'l_commitdate': l_commitdate,
'l_receiptdate': l_receiptdate,
'l_shipinstruct': l_shipinstruct,
'l_shipmode': l_shipmode,
'l_comment': l_comment
} c FROM lineitem_normal;

statement ok
CREATE VIEW lineitem AS SELECT
c['l_orderkey'] AS l_orderkey,
c['l_partkey'] AS l_partkey,
c['l_suppkey'] AS l_suppkey,
c['l_linenumber'] AS l_linenumber,
c['l_quantity'] AS l_quantity,
c['l_extendedprice'] AS l_extendedprice,
c['l_discount'] AS l_discount,
c['l_tax'] AS l_tax,
c['l_returnflag'] AS l_returnflag,
c['l_linestatus'] AS l_linestatus,
c['l_shipdate'] AS l_shipdate,
c['l_commitdate'] AS l_commitdate,
c['l_receiptdate'] AS l_receiptdate,
c['l_shipinstruct'] AS l_shipinstruct,
c['l_shipmode'] AS l_shipmode,
c['l_comment'] AS l_comment
FROM lineitem_struct

statement ok
CREATE TABLE orders_struct AS SELECT {
'o_orderkey': o_orderkey,
'o_custkey': o_custkey,
'o_orderstatus': o_orderstatus,
'o_totalprice': o_totalprice,
'o_orderdate': o_orderdate,
'o_orderpriority': o_orderpriority,
'o_clerk': o_clerk,
'o_shippriority': o_shippriority,
'o_comment': o_comment
} c FROM orders_normal;

statement ok
CREATE VIEW orders AS SELECT
c['o_orderkey'] AS o_orderkey,
c['o_custkey'] AS o_custkey,
c['o_orderstatus'] AS o_orderstatus,
c['o_totalprice'] AS o_totalprice,
c['o_orderdate'] AS o_orderdate,
c['o_orderpriority'] AS o_orderpriority,
c['o_clerk'] AS o_clerk,
c['o_shippriority'] AS o_shippriority,
c['o_comment'] AS o_comment
FROM orders_struct

statement ok
CREATE TABLE part_struct AS SELECT {
'p_partkey': p_partkey,
'p_name': p_name,
'p_mfgr': p_mfgr,
'p_brand': p_brand,
'p_type': p_type,
'p_size': p_size,
'p_container': p_container,
'p_retailprice': p_retailprice,
'p_comment': p_comment
} c FROM part_normal;

statement ok
CREATE VIEW part AS SELECT
c['p_partkey'] AS p_partkey,
c['p_name'] AS p_name,
c['p_mfgr'] AS p_mfgr,
c['p_brand'] AS p_brand,
c['p_type'] AS p_type,
c['p_size'] AS p_size,
c['p_container'] AS p_container,
c['p_retailprice'] AS p_retailprice,
c['p_comment'] AS p_comment
FROM part_struct

statement ok
CREATE TABLE partsupp_struct AS SELECT {
'ps_partkey': ps_partkey,
'ps_suppkey': ps_suppkey,
'ps_availqty': ps_availqty,
'ps_supplycost': ps_supplycost,
'ps_comment': ps_comment
} c FROM partsupp_normal;

statement ok
CREATE VIEW partsupp AS SELECT
c['ps_partkey'] AS ps_partkey,
c['ps_suppkey'] AS ps_suppkey,
c['ps_availqty'] AS ps_availqty,
c['ps_supplycost'] AS ps_supplycost,
c['ps_comment'] AS ps_comment
FROM partsupp_struct

statement ok
CREATE TABLE customer_struct AS SELECT {
'c_custkey': c_custkey,
'c_name': c_name,
'c_address': c_address,
'c_nationkey': c_nationkey,
'c_phone': c_phone,
'c_acctbal': c_acctbal,
'c_mktsegment': c_mktsegment,
'c_comment': c_comment
} c FROM customer_normal;

statement ok
CREATE VIEW customer AS SELECT
c['c_custkey'] AS c_custkey,
c['c_name'] AS c_name,
c['c_address'] AS c_address,
c['c_nationkey'] AS c_nationkey,
c['c_phone'] AS c_phone,
c['c_acctbal'] AS c_acctbal,
c['c_mktsegment'] AS c_mktsegment,
c['c_comment'] AS c_comment
FROM customer_struct

statement ok
CREATE TABLE region_struct AS SELECT {
'r_regionkey': r_regionkey,
'r_name': r_name,
'r_comment': r_comment
} c FROM region_normal;

statement ok
CREATE VIEW region AS SELECT
c['r_regionkey'] AS r_regionkey,
c['r_name'] AS r_name,
c['r_comment'] AS r_comment
FROM region_struct;

statement ok
CREATE TABLE supplier_struct AS SELECT {
's_suppkey': s_suppkey,
's_name': s_name,
's_address': s_address,
's_nationkey': s_nationkey,
's_phone': s_phone,
's_acctbal': s_acctbal,
's_comment': s_comment
} c FROM supplier_normal;

statement ok
CREATE VIEW supplier AS SELECT
c['s_suppkey'] AS s_suppkey,
c['s_name'] AS s_name,
c['s_address'] AS s_address,
c['s_nationkey'] AS s_nationkey,
c['s_phone'] AS s_phone,
c['s_acctbal'] AS s_acctbal,
c['s_comment'] AS s_comment
FROM supplier_struct

statement ok
CREATE TABLE nation_struct AS SELECT {
'n_nationkey': n_nationkey,
'n_name': n_name,
'n_regionkey': n_regionkey,
'n_comment': n_comment
} c FROM nation_normal;

statement ok
CREATE VIEW nation AS SELECT
c['n_nationkey'] AS n_nationkey,
c['n_name'] AS n_name,
c['n_regionkey'] AS n_regionkey,
c['n_comment'] AS n_comment
FROM nation_struct

loop i 1 9

query I
PRAGMA tpch(${i})
----
<FILE>:extension/tpch/dbgen/answers/sf1/q0${i}.csv

endloop

loop i 10 23

query I
PRAGMA tpch(${i})
----
<FILE>:extension/tpch/dbgen/answers/sf1/q${i}.csv

endloop