#!/bin/bash

# CSV file structure:
# DB_NAME DB_USER DB_PASSWORD_DST

# CSV field explanation
# DB_NAME: Name of the database.
# DB_USER: Name of the database user.
# DB_PASSWORD_DST: Password for the database user.

# General notes on CSV
# All fields in a row must be filled
# Delimiter is a single space, since all other characters might be used in the passwords.
# Upper case letters may only be used in passwords

# Usage
# Clone the he_migration_cwh_fms_plesk repo from code.wartung.xyz to /tmp/
# run /tmp/he_migration_cwh_fms_plesk/update_server_plesk_migration_cwh.sh to make sure the server is setup correctly
# Save the csv, see above, to /tmp/migration_csv
# Create a botf of all databaes on the source server and move it to /tmp/migration_db on the destination server
# run /tmp/he_migration_cwh_fms_plesk/import_mysql_content.sh -f /tmp/migration_csv/MY_DB_IMPORT.csv -i /tmp/migration_db/MY_DB_BOTF.tgz

# Ensure the log directory exists
mkdir -p /tmp/migration_logs/

# Get the filename of current script and strip .sh from the end
SCRIPT_FILE_NAME=$(basename "$0" .sh)

# Create the log and error filenames
LOG_FILE_NAME="/tmp/migration_logs/$SCRIPT_FILE_NAME.log"
ERR_FILE_NAME="/tmp/migration_logs/$SCRIPT_FILE_NAME.err"

# Print info about logging
echo "STDOUT redirected to $LOG_FILE_NAME"
echo "STDERR redirected to $ERR_FILE_NAME"

# Function to print usage
function usage() {
  echo "Usage: $0 -f <path_to_csv_file> -i <path_to_db_backups_file.tgz>"
  exit 1
}

# Parse command line arguments
while getopts "f:i:" opt; do
    case $opt in
        f)
            CSV_FILE="$OPTARG" ;;
        i)
            DB_BU_TGZ="$OPTARG" ;;
        *)
            usage ;;
    esac
done

# Check if both files are provided
if [[ -z "$CSV_FILE" || -z "$DB_BU_TGZ" ]]; then
    usage
fi

# Constants
SUCCESSFUL=()
FAILED=()

# Extract $DB_BU_TGZ to $TEMP_DIR 
TEMP_DIR=$(mktemp -d)
tar -xvzf "$DB_BU_TGZ" -C "$TEMP_DIR"

# Read the CSV file line by line
HEADER_PROCESSED=false
while IFS=" " read -r DB_NAME DB_USER DB_PASSWORD_DST; do
    # Skip empty lines
    if [[ -z "DB_NAME" || -z "DB_USER" || -z "DB_PASSWORD_DST" ]]; then
        continue
    fi

    # Skip the header line if it exists
    if [[ "$HEADER_PROCESSED" == false ]]; then
        if [[ "$DB_NAME" == "DB_NAME" && "$DB_USER" == "DB_USER" && "$DB_PASSWORD_DST" == "DB_PASSWORD_DST" ]]; then
            HEADER_PROCESSED=true
            continue
        fi
        HEADER_PROCESSED=true
    fi

    # Find the SQL file that contains the database name as part of its name
    SQL_FILE=$(find "$TEMP_DIR" -type f -name "*$DB_NAME*.sql" | head -n 1)

    if [[ -z "$SQL_FILE" ]]; then
        echo "SQL dump for $DB_NAME not found, skipping."
        FAILED+=("$DB_NAME - SQL dump not found")
        continue
    fi

    # Replace collation utf8mb4_0900_ai_ci with utf8mb4_unicode_520_ci. Required for compatibillity with MariaDB
    sed -i -e 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_520_ci/g' $SQL_FILE

    # Extract wpID from db user
    WPID=$(echo "$DB_USER" | awk -F'-' '{gsub("db", "", $1); print $1}')

    # Check if the SQL dump contains the old docroot or generic domain
    echo "Checking $SQL_FILE before import"
    if grep -q -E "/is/htdocs/wp${WPID}_|/is/htdocs/user_tmp/wp${WPID}_|wp${WPID}\.server-he.de|\.webpack\.hosteurope\.de" $SQL_FILE; then
        grep -n -E "/is/htdocs/wp${WPID}_|/is/htdocs/user_tmp/wp${WPID}_|wp${WPID}\.server-he.de|\.webpack\.hosteurope\.de" $SQL_FILE | while read -r line; do
            LINENUMBER=$(echo "$line" | cut -d: -f1)
            mkdir -p /tmp/migration_logs
            echo "$DB_NAME $LINENUMBER $SQL_FILE" >> /tmp/migration_logs/db_import_"$WPID".log
        done
        FAILED+=("$DB_NAME - Not imported, old docroot or generic domain found in database. Check /tmp/migration_logs/db_import_"$WPID".log. Dump copied to /tmp/migration_db/db"$WPID"_require_mod")
        mkdir -p /tmp/migration_db/db"$WPID"_require_mod
        cp $SQL_FILE /tmp/migration_db/db"$WPID"_require_mod
    else
        # Import the SQL file into the database
        echo "Importing $SQL_FILE into $DB_NAME" 
        mysql -u "$DB_USER" -p"$DB_PASSWORD_DST" "$DB_NAME" < "$SQL_FILE"  >> "$LOG_FILE_NAME" 2>> "$ERR_FILE_NAME"

        # Check if the import was successful
        if [[ $? -eq 0 ]]; then
            SUCCESSFUL+=("$DB_NAME - Import completed")
        else
            FAILED+=("$DB_NAME - Import failed")
        fi
    fi
done < "$CSV_FILE"

# Print summary
echo "Summary:"
echo "---------"
echo "Successfully created entries:"
for ITEM in "${SUCCESSFUL[@]}"; do
  echo "  - $ITEM"
done
echo "---------"
echo "Failed to create entries:"
for ITEM in "${FAILED[@]}"; do
  echo "  - $ITEM"
done
echo "---------"
