#!/bin/sh
#
# This file is released under the terms of the Artistic License.
# Please see the file LICENSE, included in this package, for details.
#
# Copyright The DBT-2 Authors
#

if [ -z ${DBT2DBNAME} ]; then
    echo "DBT2DBNAME not defined."
    exit 1
fi

USE_TABLESPACES=0
while getopts "l:" OPT; do
	case ${OPT} in
	l)
		PORT=${OPTARG}
		;;
	esac
done

if [ ! "x${PORT}" = "x" ]; then
	PORTARG="-p ${PORT}"
fi
PSQL="psql -X ${PORTARG} -e -d ${DBT2DBNAME}"

${PSQL} -c "
CREATE TABLE warehouse (
    w_id INTEGER 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 REAL,
    w_ytd NUMERIC(24, 12));
" || exit 1

${PSQL} -c "
CREATE TABLE district (
    d_id INTEGER NOT NULL,
    d_w_id INTEGER 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 REAL,
    d_ytd NUMERIC(24, 12),
    d_next_o_id INTEGER);
" || exit 1

${PSQL} -c "
CREATE TABLE customer (
    c_id INTEGER NOT NULL,
    c_d_id INTEGER NOT NULL,
    c_w_id INTEGER 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 TIMESTAMP,
    c_credit char(2),
    c_credit_lim NUMERIC(24, 12),
    c_discount REAL,
    c_balance NUMERIC(24, 12),
    c_ytd_payment NUMERIC(24, 12),
    c_payment_cnt REAL,
    c_delivery_cnt REAL,
    c_data VARCHAR(500));
" || exit 1

${PSQL} -c "
CREATE TABLE history (
    h_c_id INTEGER,
    h_c_d_id INTEGER,
    h_c_w_id INTEGER,
    h_d_id INTEGER,
    h_w_id INTEGER,
    h_date TIMESTAMP,
    h_amount REAL,
    h_data VARCHAR(24));
" || exit 1

${PSQL} -c "
CREATE TABLE new_order (
    no_o_id INTEGER NOT NULL,
    no_d_id INTEGER NOT NULL,
    no_w_id INTEGER NOT NULL);
" || exit 1

${PSQL} -c "
CREATE TABLE orders (
    o_id INTEGER NOT NULL,
    o_d_id INTEGER NOT NULL,
    o_w_id INTEGER NOT NULL,
    o_c_id INTEGER,
    o_entry_d TIMESTAMP,
    o_carrier_id INTEGER,
    o_ol_cnt INTEGER,
    o_all_local REAL);
" || exit 1

${PSQL} -c "
CREATE TABLE order_line (
    ol_o_id INTEGER NOT NULL,
    ol_d_id INTEGER NOT NULL,
    ol_w_id INTEGER NOT NULL,
    ol_number INTEGER NOT NULL,
    ol_i_id INTEGER,
    ol_supply_w_id INTEGER,
    ol_delivery_d TIMESTAMP,
    ol_quantity REAL,
    ol_amount REAL,
    ol_dist_info VARCHAR(24));
" || exit 1

${PSQL} -c "
CREATE TABLE item (
    i_id INTEGER NOT NULL,
    i_im_id INTEGER,
    i_name VARCHAR(24),
    i_price REAL,
    i_data VARCHAR(50));
" || exit 1

${PSQL} -c "
CREATE TABLE stock (
    s_i_id INTEGER NOT NULL,
    s_w_id INTEGER NOT NULL,
    s_quantity REAL NOT NULL,
    s_dist_01 VARCHAR(24),
    s_dist_02 VARCHAR(24),
    s_dist_03 VARCHAR(24),
    s_dist_04 VARCHAR(24),
    s_dist_05 VARCHAR(24),
    s_dist_06 VARCHAR(24),
    s_dist_07 VARCHAR(24),
    s_dist_08 VARCHAR(24),
    s_dist_09 VARCHAR(24),
    s_dist_10 VARCHAR(24),
    s_ytd NUMERIC(16, 8),
    s_order_cnt REAL,
    s_remote_cnt REAL,
    s_data VARCHAR(50));
" || exit 1

exit 0
