#!/bin/bash
#===============================================================================
# convert_to_innodb.sh
#===============================================================================
# Description:
#   Converts all non-InnoDB tables in a MySQL database to the InnoDB engine.
#   This script requires a properly configured ~/.my.cnf file for authentication.
#   It automatically detects all tables with engines other than InnoDB and
#   converts them one by one, providing progress updates.
#
# Usage:
#   ./convert_to_innodb.sh <database_name>
#
# Example:
#   ./convert_to_innodb.sh my_wordpress_db
#
# Author: Generated on $(date +%Y-%m-%d)
#===============================================================================

if [ "$#" -ne 1 ]; then
  echo "Usage: $0 <database_name>"
  echo "Example: $0 my_database"
  exit 1
fi

DB_NAME="$1"

# Welcome message
echo "Converting all non-InnoDB tables to InnoDB engine..."
echo "Database: $DB_NAME"

# Get a list of all non-InnoDB tables
TABLES=$(mysql -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE != 'InnoDB';" -s)

# Check if there are any non-InnoDB tables
if [ -z "$TABLES" ]; then
    echo "No non-InnoDB tables found in $DB_NAME. Nothing to convert."
    exit 0
fi

# Count how many tables need conversion
TABLE_COUNT=$(echo "$TABLES" | wc -l)
echo "Found $TABLE_COUNT tables to convert..."

# Convert each table
COUNTER=0
for TABLE in $TABLES; do
    COUNTER=$((COUNTER+1))
    echo "[$COUNTER/$TABLE_COUNT] Converting table: $TABLE"
    
    # Get current engine for logging
    CURRENT_ENGINE=$(mysql -e "SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_NAME='$TABLE';" -s)
    echo "    Current engine: $CURRENT_ENGINE"
    
    # Execute the ALTER TABLE statement
    mysql $DB_NAME -e "ALTER TABLE \`$TABLE\` ENGINE=InnoDB;"
    
    if [ $? -eq 0 ]; then
        echo "    Conversion successful!"
    else
        echo "    Conversion failed! Please check the table structure."
    fi
done

# Verify all tables have been converted
REMAINING=$(mysql -e "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND ENGINE != 'InnoDB';" -s)

echo ""
echo "Conversion completed!"
echo "Non-InnoDB tables remaining: $REMAINING"

if [ "$REMAINING" -eq "0" ]; then
    echo "All tables successfully converted to InnoDB!"
else
    echo "Some tables could not be converted. Manual intervention may be required."
fi