#!/bin/bash

# Input .xlsv 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="dev_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

# Convert .xlsv to CSV and insert into separate SQL tables
sheet_index=1

# Define total number of sheets to process. Update it to the number of sheets in your file.
total_sheets=4  # Change this to the actual number of sheets in your XLSX file

# Loop through each sheet by index
for ((sheet_index=1; sheet_index<=total_sheets; sheet_index++)); do
    # Output CSV file name for each sheet (e.g., sheet1.csv, sheet2.csv, etc.)
    output_file="/mnt/intexm_media/www/production/dmp.intexmmedia.com/data/score_data/sheet${sheet_index}.csv"

    # Convert the current sheet to CSV (no need for --list)
    xlsx2csv -s "$sheet_index" "$input_file" "$output_file"
    echo "Converted sheet $sheet_index to $output_file"

    # Clean up sheet name for MySQL table naming (lowercase, remove spaces, etc.)
    table_name="sheet${sheet_index}"  # You can adjust this naming if you prefer

    # Generate CREATE TABLE SQL statement for MySQL
    first_row=$(/usr/bin/head -n 1 "$output_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 '$output_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 $output_file into table $table_name"

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

done

echo "Conversion and import completed."

