#!/bin/bash

# Input CSV file (assumes script and file are in the same directory)
input_file="/mnt/intexm_media/www/production/dmp.intexmmedia.com/data/score_data/$1"

# Database configuration
DB_HOST="localhost"
DB_USER="intexm-dmp"  # Using root user
DB_PASS="DMP1access2!" # Empty password
DB_NAME="intexmmedia_dmp"

# Check if the file exists
if [ ! -f "$input_file" ]; then
  echo "The input file $input_file does not exist. Please check the file path."
  exit 1
fi

# Define the table name based on the CSV file name (without extension)
table_name=$(basename "$input_file" .csv)

# Generate CREATE TABLE SQL statement for MySQL
first_row=$(/usr/bin/head -n 1 "$input_file")  # Use full path to avoid conflicts
create_table_sql="CREATE TABLE IF NOT EXISTS $table_name ("

# Loop through CSV columns to generate table schema
IFS='|' read -r -a headers <<< "$first_row"
for column in "${headers[@]}"; do
    # Sanitize column name by removing invalid characters (non-alphanumeric, non-underscore)
    column=$(echo "$column" | sed 's/[^a-zA-Z0-9_]/_/g')
    create_table_sql="$create_table_sql \`$column\` VARCHAR(255),"
done

# Remove trailing comma and close the CREATE TABLE statement
create_table_sql="${create_table_sql%,});"

# Execute CREATE TABLE query in MySQL
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" --local-infile=1 -e "USE $DB_NAME; $create_table_sql"
echo "Created table $table_name"

# Load the CSV data into the MySQL table
load_sql="LOAD DATA LOCAL INFILE '$input_file' INTO TABLE $table_name FIELDS TERMINATED BY '|' ENCLOSED BY '' LINES TERMINATED BY '\n' IGNORE 1 ROWS;"
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" --local-infile=1 -e "USE $DB_NAME; $load_sql"
echo "Loaded $input_file into table $table_name"

# If the CSV file was successfully loaded, delete the CSV file
if [ $? -eq 0 ]; then
    rm -f "$input_file"
    echo "Deleted CSV file $input_file after successful import."
else
    echo "Error occurred while loading $input_file into table. Skipping deletion."
fi

echo "Import completed."
