#!/bin/bash

# Prompt for MySQL root password securely
read -sp "Enter MySQL root password: " MYSQL_PASSWORD
echo # Newline after password input

DEFAULT_INSTANCENAME="cihsr"
echo "Enter instance name (default: $DEFAULT_INSTANCENAME):"
read instancename
INSTANCENAME=${instancename:-$DEFAULT_INSTANCENAME}

# Prompt for the database name with a default value
read -p "Enter the database name (default: cihsr): " DB_NAME
DB_NAME=${DB_NAME:-$INSTANCENAME}

# Default the SQL file to 'create.mysql.db.sql' in the current directory
SQL_FILE="elxr/create.mysql.db.sql"

# Confirm if the SQL file exists
if [[ ! -f "$SQL_FILE" ]]; then
    echo "SQL file '$SQL_FILE' does not exist in the current directory. Please check the file path and try again."
    exit 1
fi

# MySQL user credentials
MYSQL_USER="root"

# Checking if the database exists
echo "Checking if the database exists..."
DB_EXISTS=$(mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES LIKE '$DB_NAME';" -s -N)

if [[ -z "$DB_EXISTS" ]]; then
    # Database does not exist, create it
    echo "Database '$DB_NAME' does not exist. Creating database..."
    mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "CREATE DATABASE IF NOT EXISTS $DB_NAME;"
fi

# Replace the placeholder {{DB_NAME}} in the SQL file with the database name
TEMP_SQL_FILE=$(mktemp)
sed "s/{{DB_NAME}}/$DB_NAME/g" "$SQL_FILE" > "$TEMP_SQL_FILE"

# Run the SQL script with the provided database name
echo "Running the SQL script to create the schema..."
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD $DB_NAME < "$TEMP_SQL_FILE"

# Clean up the temporary file
rm "$TEMP_SQL_FILE"

# Run additional SQL files if any
echo "Loading data into the database..."
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD $DB_NAME < "$INSTANCENAME-data/$DB_NAME.sql"

echo "SQL script executed successfully on database $INSTANCENAME-data/$DB_NAME"