#!/bin/sh

# config
db_hostname=127.0.0.1
db_port=7654
db_admin_user="opengauss"
db_admin_password="opengauss_password_is_123456!"
db_name="buptlab_database"
db_user="bupt_dba"
db_password="i_am_from_bupt666"
sql_dir="./lab1-creation/sql"
data_dir=$(readlink -f "./data")
tables="orders region nation supplier part partsupp customer lineitem"

# check if the data directory exists
if [ ! -d "${data_dir}" ]; then
    echo "no data directory"
    exit 1
fi

init_commands=$(
    cat <<EOL
drop database if exists ${db_name};                       -- drop the database if it already exists
drop user if exists ${db_user};                           -- delete the user if it already exists
create database ${db_name};                               -- create the database
create user ${db_user} with password '${db_password}';    -- create the user
grant all privileges to ${db_user};                       -- grant privileges
EOL
)

echo "initializing database..."
gsql \
    --host="${db_hostname}" \
    --port="${db_port}" \
    -d "postgres" \
    --username="${db_admin_user}" \
    --password="${db_admin_password}" \
    -echo-all \
    -c "${init_commands}"

# create the tables
echo "create tables..."
for table in $tables; do
    gsql \
        --host="${db_hostname}" \
        --port="${db_port}" \
        -d "${db_name}" \
        --username="${db_user}" \
        --password="${db_password}" \
        -echo-all \
        -f "${sql_dir}/${table}.sql"
done

# import the data
echo "importing data..."
for table in $tables; do
    gsql \
        --host="${db_hostname}" \
        --port="${db_port}" \
        -d "${db_name}" \
        --username="${db_admin_user}" \
        --password="${db_admin_password}" \
        -echo-all \
        -c "copy ${table} from '${data_dir}/${table}.txt' delimiter '|';"
done

# add the constraints
echo "adding constraints..."
gsql \
    --host="${db_hostname}" \
    --port="${db_port}" \
    -d "${db_name}" \
    --username="${db_user}" \
    --password="${db_password}" \
    -echo-all \
    -f "${sql_dir}/constraints.sql"
