#!/bin/bash
#
# Detect and optionally convert MyISAM tables to InnoDB for cPanel users.
# Interactive and non-interactive modes.
#
# Suggested location:
# /scripts2/detect_and_convert_myisam_to_innodb_v2.sh
# Version : 2.0
# Date    : 2026-08-03
#
# Changes:
# - Fixed detection of databases without the cPanel username prefix.
# - Databases are now obtained from mysql.db instead of assuming "user_*".
# - Improved compatibility with migrated and restored databases.
#
# Examples:
#
# Dry-run all server:
# /scripts2/detect_and_convert_myisam_to_innodb.sh --dry-run
#
# Interactive mode:
# /scripts2/detect_and_convert_myisam_to_innodb.sh
#
# Convert all, non-interactive:
# /scripts2/detect_and_convert_myisam_to_innodb.sh --convert --pause 30 --max-load 8
#
# Convert one cPanel user:
# /scripts2/detect_and_convert_myisam_to_innodb.sh --user gesinco --convert --pause 30
#
# Convert one database:
# /scripts2/detect_and_convert_myisam_to_innodb.sh --database gesinco_site --convert --pause 30
#

set -Eeuo pipefail

export PATH="/usr/local/cpanel/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin"

MYSQL_BIN="$(command -v mysql || true)"

DRY_RUN=0
AUTO_CONVERT=0
FILTER_USER=""
FILTER_DATABASE=""
PAUSE_SECONDS=""
MAX_LOAD=""

show_help() {
    cat <<'EOF'
Usage:
  detect_and_convert_myisam_to_innodb.sh [options]

Options:
  --dry-run
      Detect and show MyISAM tables only. Do not ask for conversion.

  --convert
      Convert without interactive confirmation. Required for cron/Ansible mode.

  --user USERNAME
      Process only one cPanel user.

  --database DATABASE
      Process only one database.

  --pause SECONDS
      Pause between each database conversion.
      Recommended values: 15, 30, 60.

  --max-load LOAD
      If system load average is above this value, wait before processing.
      Example: --max-load 8

  --help
      Show this help.

Examples:
  ./detect_and_convert_myisam_to_innodb.sh --dry-run

  ./detect_and_convert_myisam_to_innodb.sh

  ./detect_and_convert_myisam_to_innodb.sh --convert --pause 30 --max-load 8

  ./detect_and_convert_myisam_to_innodb.sh --user gesinco --convert --pause 30

  ./detect_and_convert_myisam_to_innodb.sh --database gesinco_site --convert --pause 30
EOF
}

while [[ $# -gt 0 ]]; do
    case "$1" in
        --dry-run)
            DRY_RUN=1
            shift
            ;;
        --convert)
            AUTO_CONVERT=1
            shift
            ;;
        --user)
            FILTER_USER="${2:-}"
            shift 2
            ;;
        --database)
            FILTER_DATABASE="${2:-}"
            shift 2
            ;;
        --pause)
            PAUSE_SECONDS="${2:-}"
            shift 2
            ;;
        --max-load)
            MAX_LOAD="${2:-}"
            shift 2
            ;;
        --help|-h)
            show_help
            exit 0
            ;;
        *)
            echo "ERROR: Unknown option: $1"
            echo
            show_help
            exit 1
            ;;
    esac
done

if [[ $EUID -ne 0 ]]; then
    echo "ERROR: This script must be run as root."
    exit 1
fi

if [[ -z "$MYSQL_BIN" ]]; then
    echo "ERROR: mysql command not found."
    exit 1
fi

if [[ ! -d /var/cpanel/users ]]; then
    echo "ERROR: /var/cpanel/users not found. Is this a cPanel server?"
    exit 1
fi

if [[ ! -f /etc/userdomains ]]; then
    echo "ERROR: /etc/userdomains not found."
    echo "Try rebuilding it with:"
    echo "/scripts/updateuserdomains"
    exit 1
fi

if [[ -n "$FILTER_USER" ]] && ! [[ "$FILTER_USER" =~ ^[a-zA-Z0-9_]+$ ]]; then
    echo "ERROR: Invalid cPanel username: $FILTER_USER"
    exit 1
fi

if [[ -n "$FILTER_DATABASE" ]] && ! [[ "$FILTER_DATABASE" =~ ^[a-zA-Z0-9_\$.-]+$ ]]; then
    echo "ERROR: Invalid database name: $FILTER_DATABASE"
    exit 1
fi

if [[ -n "$PAUSE_SECONDS" ]] && ! [[ "$PAUSE_SECONDS" =~ ^[0-9]+$ ]]; then
    echo "ERROR: --pause must be a number."
    exit 1
fi

if [[ -n "$MAX_LOAD" ]] && ! [[ "$MAX_LOAD" =~ ^[0-9]+([.][0-9]+)?$ ]]; then
    echo "ERROR: --max-load must be a number."
    exit 1
fi

mysql_query() {
    "$MYSQL_BIN" --batch --raw --skip-column-names -e "$1"
}

mysql_execute_legacy_compatible() {
    local sql_command="$1"

    "$MYSQL_BIN" -e "
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_DATE', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_IN_DATE', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_ALL_TABLES', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, ',,', ',');
SET SESSION sql_mode = TRIM(BOTH ',' FROM @@SESSION.sql_mode);
SET SESSION innodb_strict_mode = OFF;
${sql_command}
"
}

mysql_repair_myisam_table() {
    local db_name="$1"
    local table_name="$2"

    "$MYSQL_BIN" -e "
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_DATE', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'NO_ZERO_IN_DATE', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_ALL_TABLES', '');
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, ',,', ',');
SET SESSION sql_mode = TRIM(BOTH ',' FROM @@SESSION.sql_mode);
REPAIR TABLE \`${db_name}\`.\`${table_name}\`;
"
}

sql_escape() {
    local value="$1"
    value="${value//\\/\\\\}"
    value="${value//\'/\\\'}"
    printf "%s" "$value"
}

get_domains_for_user() {
    local user="$1"

    awk -F': ' -v user="$user" '
        $2 == user {
            print $1
        }
    ' /etc/userdomains | sort -u | paste -sd "," -
}

###############################################################################
# Database helper functions
###############################################################################

get_databases_for_user() {
    local user="$1"

    mysql_query "
        SELECT DISTINCT Db
        FROM mysql.db
        WHERE User='$(sql_escape "$user")'
        ORDER BY Db;
    "
}

database_belongs_to_user() {
    local user="$1"
    local db="$2"

    mysql_query "
        SELECT 1
        FROM mysql.db
        WHERE User='$(sql_escape "$user")'
          AND Db='$(sql_escape "$db")'
        LIMIT 1;
    "
}

build_database_list() {
    local user="$1"

    if [[ -n "$FILTER_DATABASE" ]]; then
        if [[ -n "$(database_belongs_to_user "$user" "$FILTER_DATABASE")" ]]; then
            echo "$FILTER_DATABASE"
        fi
        return
    fi

    get_databases_for_user "$user"
}

wait_for_load_if_needed() {
    local context="$1"

    if [[ -z "$MAX_LOAD" ]]; then
        return 0
    fi

    while true; do
        local current_load
        current_load="$(awk '{print $1}' /proc/loadavg)"

        if awk -v current="$current_load" -v max="$MAX_LOAD" 'BEGIN { exit !(current <= max) }'; then
            return 0
        fi

        echo
        echo "Load average is $current_load, above limit $MAX_LOAD."
        echo "Waiting 60 seconds before continuing: $context"
        sleep 60
    done
}

TMP_REPORT="$(mktemp)"
TMP_DATABASES="$(mktemp)"
TMP_DATABASES_SORTED="$(mktemp)"
TMP_USERS="$(mktemp)"
TMP_RESULTS="$(mktemp)"

cleanup() {
    rm -f "$TMP_REPORT" "$TMP_DATABASES" "$TMP_DATABASES_SORTED" "$TMP_USERS" "$TMP_RESULTS"
}

trap cleanup EXIT

echo "============================================================"
echo "MyISAM detector and converter for cPanel users"
echo "Domain source: /etc/userdomains"
echo "Mode: screen output only"
echo "Legacy date defaults: supported through SESSION sql_mode"
echo "Large row format: ENGINE=InnoDB ROW_FORMAT=DYNAMIC"
echo "Crashed MyISAM tables: REPAIR TABLE fallback enabled"
echo "============================================================"
echo

if [[ "$DRY_RUN" -eq 1 ]]; then
    echo "Selected mode: DRY-RUN"
elif [[ "$AUTO_CONVERT" -eq 1 ]]; then
    echo "Selected mode: NON-INTERACTIVE CONVERSION"
else
    echo "Selected mode: INTERACTIVE"
fi

if [[ -n "$FILTER_USER" ]]; then
    echo "User filter:     $FILTER_USER"
fi

if [[ -n "$FILTER_DATABASE" ]]; then
    echo "Database filter: $FILTER_DATABASE"
fi

if [[ -n "$MAX_LOAD" ]]; then
    echo "Max load:        $MAX_LOAD"
fi

echo

echo -e "CPANEL_USER\tDOMAINS\tDATABASE\tTABLE_COUNT\tSIZE_MB" > "$TMP_REPORT"
echo -e "STATUS\tUSER\tDATABASE\tTABLE\tMESSAGE" > "$TMP_RESULTS"

if [[ -n "$FILTER_USER" ]]; then
    if [[ ! -f "/var/cpanel/users/$FILTER_USER" ]]; then
        echo "ERROR: cPanel user not found: $FILTER_USER"
        exit 1
    fi

    echo "$FILTER_USER" > "$TMP_USERS"
else
    find /var/cpanel/users -maxdepth 1 -type f -printf '%f\n' | sort > "$TMP_USERS"
fi

TOTAL_USERS="$(wc -l < "$TMP_USERS")"
CURRENT_USER=0
FOUND_USERS=0
FOUND_DATABASES=0
FOUND_TABLES=0
FOUND_SIZE_MB=0

echo "Scanning cPanel users for MyISAM tables..."
echo

while read -r CPANEL_USER; do
    [[ -z "$CPANEL_USER" ]] && continue

    CURRENT_USER=$((CURRENT_USER + 1))

    printf '[%s/%s] Checking user: %s\n' "$CURRENT_USER" "$TOTAL_USERS" "$CPANEL_USER"

    if ! [[ "$CPANEL_USER" =~ ^[a-zA-Z0-9_]+$ ]]; then
        echo "  Skipping invalid username: $CPANEL_USER"
        continue
    fi

DATABASE_LIST="$(build_database_list "$CPANEL_USER")"

if [[ -z "$DATABASE_LIST" ]]; then
    echo "  No databases found for this user."
    continue
fi

USER_FOUND_MYISAM=0

while read -r DB_NAME; do

    [[ -z "$DB_NAME" ]] && continue

    DB_ESCAPED="$(sql_escape "$DB_NAME")"

    QUERY="
SELECT
    TABLE_SCHEMA,
    COUNT(*),
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND ENGINE = 'MyISAM'
  AND TABLE_SCHEMA = '${DB_ESCAPED}'
GROUP BY TABLE_SCHEMA
ORDER BY TABLE_SCHEMA;
"

    RESULTS="$(mysql_query "$QUERY" || true)"

    [[ -z "$RESULTS" ]] && continue

    if [[ "$USER_FOUND_MYISAM" -eq 0 ]]; then
        FOUND_USERS=$((FOUND_USERS + 1))
        USER_FOUND_MYISAM=1
    fi

    DOMAINS="$(get_domains_for_user "$CPANEL_USER")"
    [[ -z "$DOMAINS" ]] && DOMAINS="-"

    while IFS=$'\t' read -r DB_NAME TABLE_COUNT SIZE_MB; do

        [[ -z "$DB_NAME" || -z "$TABLE_COUNT" ]] && continue
        [[ -z "$SIZE_MB" || "$SIZE_MB" == "NULL" ]] && SIZE_MB=0

        FOUND_DATABASES=$((FOUND_DATABASES + 1))
        FOUND_TABLES=$((FOUND_TABLES + TABLE_COUNT))

        FOUND_SIZE_MB="$(
            awk -v a="$FOUND_SIZE_MB" -v b="$SIZE_MB" \
            'BEGIN { printf "%.2f", a+b }'
        )"

        echo -e "${CPANEL_USER}\t${DOMAINS}\t${DB_NAME}\t${TABLE_COUNT}\t${SIZE_MB}" >> "$TMP_REPORT"

        echo -e "${CPANEL_USER}\t${DB_NAME}" >> "$TMP_DATABASES"

        echo "  FOUND: $DB_NAME has $TABLE_COUNT MyISAM table(s), approx ${SIZE_MB} MB"

    done <<< "$RESULTS"

done <<< "$DATABASE_LIST"

done < "$TMP_USERS"

echo
echo "============================================================"
echo "Scan completed"
echo "============================================================"
echo "Users scanned:             $TOTAL_USERS"
echo "Users with MyISAM tables:  $FOUND_USERS"
echo "Databases with MyISAM:     $FOUND_DATABASES"
echo "Total MyISAM tables:       $FOUND_TABLES"
echo "Approx MyISAM size:        ${FOUND_SIZE_MB} MB"
echo "============================================================"
echo

if [[ "$FOUND_TABLES" -eq 0 ]]; then
    echo "No MyISAM tables found."
    exit 0
fi

echo "============================================================"
echo "Summary"
echo "============================================================"
echo

if command -v column >/dev/null 2>&1; then
    column -t -s $'\t' "$TMP_REPORT"
else
    cat "$TMP_REPORT"
fi

echo
echo "============================================================"
echo "Detailed MyISAM tables"
echo "============================================================"
echo

sort -u "$TMP_DATABASES" | while IFS=$'\t' read -r CPANEL_USER DB_NAME; do
    [[ -z "$CPANEL_USER" || -z "$DB_NAME" ]] && continue

    DOMAINS="$(get_domains_for_user "$CPANEL_USER")"
    [[ -z "$DOMAINS" ]] && DOMAINS="-"

    DB_ESCAPED="$(sql_escape "$DB_NAME")"

    echo
    echo "User:     $CPANEL_USER"
    echo "Domains:  $DOMAINS"
    echo "Database: $DB_NAME"
    echo "Tables:"

    QUERY="
SELECT
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND ENGINE = 'MyISAM'
  AND TABLE_SCHEMA = '${DB_ESCAPED}'
ORDER BY DATA_LENGTH + INDEX_LENGTH ASC, TABLE_NAME;
"

    mysql_query "$QUERY" | awk -F'\t' '{print "  - " $1 " (" $2 " MB)"}'
done

if [[ "$DRY_RUN" -eq 1 ]]; then
    echo
    echo "============================================================"
    echo "Dry-run finished"
    echo "============================================================"
    echo "No changes were made."
    echo "============================================================"
    exit 0
fi

if [[ "$AUTO_CONVERT" -eq 0 && ! -t 0 ]]; then
    echo
    echo "============================================================"
    echo "Non-interactive input detected"
    echo "============================================================"
    echo "No --convert option was provided, so no conversion will be performed."
    echo "Use --convert --pause 30 for cron or Ansible mode."
    echo "============================================================"
    exit 0
fi

echo
echo "============================================================"
echo "IMPORTANT"
echo "============================================================"
echo "- Make sure you have recent backups before converting tables."
echo "- Large tables may lock or impact busy websites during conversion."
echo "- Tables with old date defaults like '0000-00-00 00:00:00' will be handled using SESSION sql_mode."
echo "- Tables will be converted using ENGINE=InnoDB ROW_FORMAT=DYNAMIC."
echo "- This does NOT change the global MySQL/MariaDB sql_mode."
echo "- This does NOT change the global MySQL/MariaDB innodb_strict_mode."
echo "- If a MyISAM table is marked as crashed, the script will try REPAIR TABLE and retry conversion."
echo "- The script will pause between each database conversion."
echo

if [[ "$AUTO_CONVERT" -eq 0 ]]; then
    read -r -p "Do you want to continue with the conversion to InnoDB? Type CONVERT to continue: " CONFIRM

    if [[ "$CONFIRM" != "CONVERT" ]]; then
        echo
        echo "Conversion aborted. No changes were made."
        exit 0
    fi
fi

if [[ -z "$PAUSE_SECONDS" ]]; then
    if [[ "$AUTO_CONVERT" -eq 1 ]]; then
        PAUSE_SECONDS=30
        echo "No --pause provided. Using default pause: ${PAUSE_SECONDS}s"
    else
        echo
        echo "Choose pause between each database conversion:"
        echo "1) 15 seconds"
        echo "2) 30 seconds"
        echo "3) 60 seconds"
        echo

        read -r -p "Enter option [1/2/3]: " PAUSE_OPTION

        case "$PAUSE_OPTION" in
            1) PAUSE_SECONDS=15 ;;
            2) PAUSE_SECONDS=30 ;;
            3) PAUSE_SECONDS=60 ;;
            *)
                echo "Invalid option. Aborting."
                exit 1
                ;;
        esac
    fi
fi

sort -u "$TMP_DATABASES" > "$TMP_DATABASES_SORTED"

TOTAL_DATABASES="$(wc -l < "$TMP_DATABASES_SORTED")"
CURRENT_DATABASE=0
CONVERTED_DATABASES=0
FAILED_DATABASES=0
CONVERTED_TABLES=0
FAILED_TABLES=0
REPAIRED_TABLES=0
SKIPPED_TABLES=0

echo
echo "============================================================"
echo "Starting conversion"
echo "============================================================"
echo "Pause between databases: ${PAUSE_SECONDS}s"
echo "Databases to process:    $TOTAL_DATABASES"
echo "Legacy date mode:        enabled per MySQL session"
echo "InnoDB row format:       DYNAMIC"
echo "Repair fallback:         enabled for crashed MyISAM tables"
if [[ -n "$MAX_LOAD" ]]; then
    echo "Max load:                $MAX_LOAD"
fi
echo "============================================================"
echo

while IFS=$'\t' read -r CPANEL_USER DB_NAME; do
    [[ -z "$CPANEL_USER" || -z "$DB_NAME" ]] && continue

    CURRENT_DATABASE=$((CURRENT_DATABASE + 1))

    wait_for_load_if_needed "database $DB_NAME"

    DOMAINS="$(get_domains_for_user "$CPANEL_USER")"
    [[ -z "$DOMAINS" ]] && DOMAINS="-"

    DB_ESCAPED="$(sql_escape "$DB_NAME")"

    echo
    echo "------------------------------------------------------------"
    echo "[$CURRENT_DATABASE/$TOTAL_DATABASES] Processing database"
    echo "User:     $CPANEL_USER"
    echo "Domains:  $DOMAINS"
    echo "Database: $DB_NAME"
    echo "Started:  $(date)"
    echo "------------------------------------------------------------"

    TMP_SQL="$(mktemp)"

    QUERY="
SELECT CONCAT(
    'ALTER TABLE \`',
    REPLACE(TABLE_SCHEMA, '\`', '\`\`'),
    '\`.\`',
    REPLACE(TABLE_NAME, '\`', '\`\`'),
    '\` ENGINE=InnoDB ROW_FORMAT=DYNAMIC;'
)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND ENGINE = 'MyISAM'
  AND TABLE_SCHEMA = '${DB_ESCAPED}'
ORDER BY DATA_LENGTH + INDEX_LENGTH ASC, TABLE_NAME;
"

    mysql_query "$QUERY" > "$TMP_SQL" || true

    if [[ ! -s "$TMP_SQL" ]]; then
        echo "No MyISAM tables currently found in $DB_NAME. Skipping."
        echo -e "SKIPPED\t${CPANEL_USER}\t${DB_NAME}\t-\tNo MyISAM tables currently found" >> "$TMP_RESULTS"
        rm -f "$TMP_SQL"
        continue
    fi

    TABLES_IN_DB="$(wc -l < "$TMP_SQL")"
    CURRENT_TABLE=0
    DB_FAILED=0

    while read -r SQL_COMMAND; do
        [[ -z "$SQL_COMMAND" ]] && continue

        CURRENT_TABLE=$((CURRENT_TABLE + 1))

        wait_for_load_if_needed "table $CURRENT_TABLE/$TABLES_IN_DB in database $DB_NAME"

        TABLE_NAME="$(echo "$SQL_COMMAND" | sed -E 's/^ALTER TABLE `[^`]+`\.`([^`]+)` ENGINE=InnoDB( ROW_FORMAT=DYNAMIC)?;$/\1/')"

        echo
        echo "  [$CURRENT_TABLE/$TABLES_IN_DB] Converting table: $TABLE_NAME"
        echo "  SQL: $SQL_COMMAND"
        echo "  Using legacy-compatible SESSION sql_mode, innodb_strict_mode=OFF and ROW_FORMAT=DYNAMIC."

        CONVERT_OUTPUT="$(mktemp)"

        if mysql_execute_legacy_compatible "$SQL_COMMAND" > "$CONVERT_OUTPUT" 2>&1; then
            echo "  OK: $TABLE_NAME converted to InnoDB."
            CONVERTED_TABLES=$((CONVERTED_TABLES + 1))
            echo -e "OK\t${CPANEL_USER}\t${DB_NAME}\t${TABLE_NAME}\tConverted to InnoDB" >> "$TMP_RESULTS"
        else
            cat "$CONVERT_OUTPUT"

            if grep -qiE "marked as crashed|should be repaired|is crashed" "$CONVERT_OUTPUT"; then
                echo
                echo "  WARNING: Table appears to be crashed: $TABLE_NAME"
                echo "  Attempting REPAIR TABLE before retrying conversion..."

                REPAIR_OUTPUT="$(mktemp)"

                if mysql_repair_myisam_table "$DB_NAME" "$TABLE_NAME" > "$REPAIR_OUTPUT" 2>&1; then
                    cat "$REPAIR_OUTPUT"
                    echo "  OK: REPAIR TABLE completed for $TABLE_NAME"
                    echo "  Retrying conversion: $TABLE_NAME"

                    if mysql_execute_legacy_compatible "$SQL_COMMAND"; then
                        echo "  OK: $TABLE_NAME converted to InnoDB after repair."
                        CONVERTED_TABLES=$((CONVERTED_TABLES + 1))
                        REPAIRED_TABLES=$((REPAIRED_TABLES + 1))
                        echo -e "REPAIRED\t${CPANEL_USER}\t${DB_NAME}\t${TABLE_NAME}\tRepaired and converted to InnoDB" >> "$TMP_RESULTS"
                    else
                        echo "  ERROR: Failed converting table after repair: $TABLE_NAME"
                        DB_FAILED=1
                        FAILED_TABLES=$((FAILED_TABLES + 1))
                        echo -e "FAILED\t${CPANEL_USER}\t${DB_NAME}\t${TABLE_NAME}\tFailed after REPAIR TABLE" >> "$TMP_RESULTS"
                    fi
                else
                    cat "$REPAIR_OUTPUT"
                    echo "  ERROR: REPAIR TABLE failed for $TABLE_NAME"
                    echo "  Manual repair may be required."
                    DB_FAILED=1
                    FAILED_TABLES=$((FAILED_TABLES + 1))
                    echo -e "FAILED\t${CPANEL_USER}\t${DB_NAME}\t${TABLE_NAME}\tREPAIR TABLE failed" >> "$TMP_RESULTS"
                fi

                rm -f "$REPAIR_OUTPUT"
            else
                echo "  ERROR: Failed converting table: $TABLE_NAME"

                if grep -qiE "Row size too large|ERROR 1118" "$CONVERT_OUTPUT"; then
                    echo "  Hint: This looks like ERROR 1118 / Row size too large."
                    echo "  The script already used ROW_FORMAT=DYNAMIC and innodb_strict_mode=OFF."
                    echo "  Manual review may be needed to convert some large VARCHAR/CHAR columns to TEXT."
                    echo -e "FAILED\t${CPANEL_USER}\t${DB_NAME}\t${TABLE_NAME}\tERROR 1118 Row size too large" >> "$TMP_RESULTS"
                elif grep -qiE "Invalid default value|ERROR 1067" "$CONVERT_OUTPUT"; then
                    echo "  Hint: This looks like ERROR 1067 / Invalid default value."
                    echo "  The script already used a legacy-compatible SESSION sql_mode."
                    echo "  Manual review may be needed if the table still fails."
                    echo -e "FAILED\t${CPANEL_USER}\t${DB_NAME}\t${TABLE_NAME}\tERROR 1067 Invalid default value" >> "$TMP_RESULTS"
                else
                    echo "  Hint: Check the MySQL error above for the exact cause."
                    echo -e "FAILED\t${CPANEL_USER}\t${DB_NAME}\t${TABLE_NAME}\tConversion failed" >> "$TMP_RESULTS"
                fi

                DB_FAILED=1
                FAILED_TABLES=$((FAILED_TABLES + 1))
            fi
        fi

        rm -f "$CONVERT_OUTPUT"

    done < "$TMP_SQL"

    rm -f "$TMP_SQL"

    if [[ "$DB_FAILED" -eq 0 ]]; then
        CONVERTED_DATABASES=$((CONVERTED_DATABASES + 1))
        echo
        echo "OK: Database completed successfully: $DB_NAME"
    else
        FAILED_DATABASES=$((FAILED_DATABASES + 1))
        echo
        echo "WARNING: Database completed with errors: $DB_NAME"
    fi

    echo "Finished: $(date)"

    if [[ "$CURRENT_DATABASE" -lt "$TOTAL_DATABASES" ]]; then
        echo
        echo "Sleeping ${PAUSE_SECONDS}s before next database..."
        sleep "$PAUSE_SECONDS"
    fi

done < "$TMP_DATABASES_SORTED"

echo
echo "============================================================"
echo "Conversion finished"
echo "============================================================"
echo "Databases processed:       $TOTAL_DATABASES"
echo "Tables converted:          $CONVERTED_TABLES"
echo "Tables repaired+converted: $REPAIRED_TABLES"
echo "Tables failed:             $FAILED_TABLES"
echo "Tables skipped:            $SKIPPED_TABLES"
echo "Completed databases:       $CONVERTED_DATABASES"
echo "Databases with errors:     $FAILED_DATABASES"
echo "Finished at:               $(date)"
echo "============================================================"
echo

echo "============================================================"
echo "Final result detail"
echo "============================================================"

if command -v column >/dev/null 2>&1; then
    column -t -s $'\t' "$TMP_RESULTS"
else
    cat "$TMP_RESULTS"
fi

echo
echo "============================================================"
echo "Remaining MyISAM tables after execution"
echo "============================================================"

REMAINING_QUERY="
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
  AND ENGINE = 'MyISAM'
ORDER BY TABLE_SCHEMA, TABLE_NAME;
"

REMAINING_MYISAM="$(mysql_query "$REMAINING_QUERY" || true)"

if [[ -z "$REMAINING_MYISAM" ]]; then
    echo "No MyISAM tables remain on the server."
else
    echo "$REMAINING_MYISAM" | awk -F'\t' '{print $1 "." $2 " (" $3 ")"}'
fi

echo "============================================================"