init-db.sh

#!/bin/bash
set -e

# Wait for Postgres to be ready
until pg_isready -U postgres; do
  echo "Waiting for PostgreSQL to start..."
  sleep 2
done

# Database name from environment or default
DB_NAME=${POSTGRES_DB:-db1}
DATA_DIR="/tmp/sf1"

echo "Initializing database $DB_NAME with TPC-H data from $DATA_DIR..."

# Wait for all TPC-H data files (especially the last ones) to be generated by the app container
echo "Waiting for all TPC-H data files in $DATA_DIR..."
# supplier.csv is the last one in the generation script loop
while [ ! -f "$DATA_DIR/supplier.csv" ]; do
  sleep 2
done
# Small additional buffer to ensure write is complete and visible
sleep 2
echo "All data files found. Proceeding with initialization."

# List of TPC-H tables to load
TABLES=("customer" "lineitem" "nation" "orders" "part" "partsupp" "region" "supplier")

for TABLE in "${TABLES[@]}"; do
  # Check if table exists and has rows
  TABLE_NAME="dbms_$TABLE"
  EXISTS=$(psql -U postgres -d "$DB_NAME" -tAc "SELECT EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = '$TABLE_NAME');")
  
  HAS_DATA="f"
  if [ "$EXISTS" = "t" ]; then
    HAS_DATA=$(psql -U postgres -d "$DB_NAME" -tAc "SELECT EXISTS (SELECT 1 FROM $TABLE_NAME LIMIT 1);")
  fi
  
  if [ "$EXISTS" = "f" ] || [ "$HAS_DATA" = "f" ]; then
    echo "Creating and loading table $TABLE_NAME..."
    
    # If table exists but is empty, we might need to drop it if we want to recreate with correct schema,
    # or just truncate. To be safe and handle schema changes, let's drop if it exists.
    if [ "$EXISTS" = "t" ]; then
       psql -U postgres -d "$DB_NAME" -c "DROP TABLE $TABLE_NAME;"
    fi
    
    case $TABLE in
      nation)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_nation (n_nationkey INT, n_name CHAR(25), n_regionkey INT, n_comment VARCHAR(152));"
        ;;
      region)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_region (r_regionkey INT, r_name CHAR(25), r_comment VARCHAR(152));"
        ;;
      part)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_part (p_partkey INT, p_name VARCHAR(55), p_mfgr CHAR(25), p_brand CHAR(10), p_type VARCHAR(25), p_size INT, p_container CHAR(10), p_retailprice DECIMAL(15,2), p_comment VARCHAR(23));"
        ;;
      supplier)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_supplier (s_suppkey INT, s_name CHAR(25), s_address VARCHAR(40), s_nationkey INT, s_phone CHAR(15), s_acctbal DECIMAL(15,2), s_comment VARCHAR(101));"
        ;;
      partsupp)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_partsupp (ps_partkey INT, ps_suppkey INT, ps_availqty INT, ps_supplycost DECIMAL(15,2), ps_comment VARCHAR(199));"
        ;;
      customer)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_customer (c_custkey INT, c_name VARCHAR(25), c_address VARCHAR(40), c_nationkey INT, c_phone CHAR(15), c_acctbal DECIMAL(15,2), c_mktsegment CHAR(10), c_comment VARCHAR(117));"
        ;;
      orders)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_orders (o_orderkey INT, o_custkey INT, o_orderstatus CHAR(1), o_totalprice DECIMAL(15,2), o_orderdate DATE, o_orderpriority CHAR(15), o_clerk CHAR(15), o_shippriority INT, o_comment VARCHAR(79));"
        ;;
      lineitem)
        psql -U postgres -d "$DB_NAME" -c "CREATE TABLE dbms_lineitem (l_orderkey INT, l_partkey INT, l_suppkey INT, l_linenumber INT, l_quantity DECIMAL(15,2), l_extendedprice DECIMAL(15,2), l_discount DECIMAL(15,2), l_tax DECIMAL(15,2), l_returnflag CHAR(1), l_linestatus CHAR(1), l_shipdate DATE, l_commitdate DATE, l_receiptdate DATE, l_shipinstruct CHAR(25), l_shipmode CHAR(10), l_comment VARCHAR(44));"
        ;;
    esac
    
    psql -U postgres -d "$DB_NAME" -c "\COPY dbms_$TABLE FROM '$DATA_DIR/$TABLE.csv' WITH (FORMAT CSV, HEADER);"
  else
    echo "Table dbms_$TABLE already exists, skipping."
  fi
done

echo "Database initialization complete."