#!/bin/bash

# Script to optimize all InnoDB tables in a specified MySQL database
# Usage: ./optimize_innodb_tables.sh <database_name> [options]

# Display help information
function show_help {
  echo "Usage: $0 <database_name> [options]"
  echo ""
  echo "Options:"
  echo "  -h, --help             Display this help message"
  echo "  -u, --user USERNAME    MySQL username (optional, will use .my.cnf if not provided)"
  echo "  -p, --password PASS    MySQL password (optional, will use .my.cnf if not provided)"
  echo ""
  echo "Example: $0 mydatabase -u root -p mypassword"
  echo "Example: $0 mydatabase (uses credentials from .my.cnf)"
  exit 1
}

# Check if we have at least one argument
if [ $# -lt 1 ]; then
  show_help
fi

# Initialize variables
DB_NAME=""
MYSQL_USER=""
MYSQL_PASS=""
MYSQL_OPTS=""

# Parse command line arguments
while [ $# -gt 0 ]; do
  case "$1" in
    -h|--help)
      show_help
      ;;
    -u|--user)
      MYSQL_USER="$2"
      shift 2
      ;;
    -p|--password)
      MYSQL_PASS="$2"
      shift 2
      ;;
    *)
      # First non-option argument is the database name
      if [ -z "$DB_NAME" ]; then
        DB_NAME="$1"
        shift
      else
        echo "Error: Unknown option $1"
        show_help
      fi
      ;;
  esac
done

# Verify database name was provided
if [ -z "$DB_NAME" ]; then
  echo "Error: Database name is required."
  show_help
fi

# Set MySQL command options
if [ -n "$MYSQL_USER" ]; then
  MYSQL_OPTS="$MYSQL_OPTS -u $MYSQL_USER"
fi

if [ -n "$MYSQL_PASS" ]; then
  MYSQL_OPTS="$MYSQL_OPTS -p$MYSQL_PASS"
fi

# Get all InnoDB tables
echo "Fetching InnoDB tables from database $DB_NAME..."
INNODB_TABLES=$(mysql $MYSQL_OPTS -B -N -e "
  SELECT TABLE_NAME 
  FROM INFORMATION_SCHEMA.TABLES 
  WHERE TABLE_SCHEMA='$DB_NAME' 
  AND ENGINE='InnoDB'")

# Check if any InnoDB tables were found
if [ -z "$INNODB_TABLES" ]; then
  echo "No InnoDB tables found in the database $DB_NAME."
  exit 0
fi

# Count the tables
TABLE_COUNT=$(echo "$INNODB_TABLES" | wc -l)
echo "Found $TABLE_COUNT InnoDB tables in database $DB_NAME."

# Initialize counter for tracking progress
COUNTER=0

# Optimize each table
for TABLE in $INNODB_TABLES; do
  COUNTER=$((COUNTER + 1))
  echo "[$COUNTER/$TABLE_COUNT] Optimizing table: $TABLE"
  
  # Run OPTIMIZE TABLE command
  RESULT=$(mysql $MYSQL_OPTS -B -N -e "
    OPTIMIZE TABLE $DB_NAME.$TABLE" | tr '\t' ' ')
  
  echo "  Result: $RESULT"
done

echo "Optimization completed for all InnoDB tables in database $DB_NAME."