#!/bin/bash

# CSV file structure:
# DOMAIN DB_NAME DB_USER

# CSV field explanation
# Domain: Must be an existing domain of type 'domain' in Plesk. No aliases or subdomains
# DB_NAME: Name of the database.
# DB_USER: Name of the database user. If DB has no user leave empty or use DB_NAME as DB_USER. If Empty, the script will take care of setting DB_USER to DB_NAME

# General notes on CSV
# All fields in a row must be filled
# Delimiter is "€", 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
# run /tmp/he_migration_cwh_fms_plesk/create_mysql_plesk_databases.sh -f /tmp/migration_csv/MY_DATABASE_CREATE.csv

# 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"

# Check if the necessary argument is provided
if [[ "$#" -lt 1 ]]; then
    echo "Usage: $0 -f <path_to_csv_file>"
    exit 1
fi

# Parse arguments
while getopts "f:" opt; do
    case $opt in
        f) CSV_FILE="$OPTARG" ;;
        *)
            echo "Invalid option: -$OPTARG"
            exit 1
            ;;
    esac
done

# Ensure the CSV file exists
if [[ ! -f "$CSV_FILE" ]]; then
    echo "Error: CSV file not found at $CSV_FILE"
    exit 1
fi

# Making sure CSV files formatting is correct
echo "Checking $CSV_FILE for BOM, CRLF, White Spaces and Empty Lines"
  # Remove BOM (Byte Order Mark) from $CSV_FILE
  sed -i '1s/^\xEF\xBB\xBF//' "$CSV_FILE"
  
  # Remove CRLF (Windows Line Endings) from $CSV_FILE
  sed -i 's/\r$//' "$CSV_FILE"

  # Remove all spaces and tabs
  sed -i 's/ //g' "$CSV_FILE"

  # Remove empty lines
  sed -i '/^$/d' "$CSV_FILE"
echo "Done converting $CSV_FILE"

# Function to generate random password
GENERATE_PASSWORD() {
  tr -dc A-Za-z0-9 </dev/urandom | head -c 12
}

# Arrays for summary
SUCCESSFUL=()
FAILED=()

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

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

  # Check if the database already exists
  if plesk db -Ne "SHOW DATABASES;"  | grep -qw "$DB_NAME"; then
    FAILED+=("DB $DB_NAME already exists")
    continue
  fi

  # Check if $DB_USER was provided. If not, set $DB_USER=$DB_NAME
  if [ -z "$DB_USER" ]; then
    DB_USER="$DB_NAME"
  fi

  # Check if the database user already exists
  if plesk db "SELECT User FROM mysql.user;" | grep -qw "$DB_USER"; then
    FAILED+=("DBUser $DB_USER already exists")
    continue
  fi

  # Create the database
  echo "Creating database $DB_NAME under $DOMAIN"
  if ! plesk bin database --create "$DB_NAME" -domain "$DOMAIN" -type mysql >> "$LOG_FILE_NAME" 2>> "$ERR_FILE_NAME"; then
    FAILED+=("Failed to create database $DB_NAME")
    continue
  fi

  # Generate a random password
  DB_PASSWORD_DST=$(GENERATE_PASSWORD)

  # Create the database user
  echo "Creating database user $DB_USER for $DB_NAME under $DOMAIN"
  if plesk bin database --create-dbuser "$DB_USER" -passwd "$DB_PASSWORD_DST" -domain "$DOMAIN" -database "$DB_NAME" -server localhost:3306 >> "$LOG_FILE_NAME" 2>> "$ERR_FILE_NAME"; then
    SUCCESSFUL+=("Successfully created database '$DB_NAME' and user '$DB_USER'.")
    
    # Write DB_NAME DB_USER DB_PASSWORD_DST to new file for db-import
    # create dir, if not exist 
    mkdir -p /tmp/migration_csv

    # Extract wpID from db user
    WPID=$(echo "$DB_USER" | awk -F'-' '{gsub("db", "", $1); print $1}')
    
    # Generate file name
    DB_IMPORT=/tmp/migration_csv/database_import_wp"$WPID".csv

    # Create file with header, if not exist. Separator is space
    if [[ ! -f "$DB_IMPORT" ]]; then
      echo "DB_NAME DB_USER DB_PASSWORD_DST" >> "$DB_IMPORT"
    fi

    # Append current line to the file
    echo "$DB_NAME $DB_USER $DB_PASSWORD_DST" >> "$DB_IMPORT"
  else
    echo "Failed to create user '$DB_USER'."
    FAILED+=("Failed to create user '$DB_USER'.")
  
    # Rollback: Drop the database and its user, if user creation fails
    plesk bin database --remove "$DB_NAME" && \
    plesk bin database --remove-dbuser "$DB_USER" -server localhost:3306
  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 "---------"