@echo off
setlocal enabledelayedexpansion

:: 获取当前日期时间作为时间戳（年月日_时分秒）
for /f %%i in ('wmic os get localdatetime ^| find "."') do set DT=%%i
set DATE=%DT:~0,8%
set TIME=%DT:~8,6%
set DATETIME=%DATE%_%TIME%

:: 设置变量
set BACKUP_DIR=C:\Program Files\MySQL\database_backups
set MYSQL_BIN=C:\Program Files\MySQL\MySQL Server 8.0\bin
set MYSQL_USER=root
set MYSQL_PASSWORD=Root_ltkj123

:: 创建备份目录
if not exist "%BACKUP_DIR%" mkdir "%BACKUP_DIR%"

:: 遍历数据库（排除包含 test 的）
for /F "skip=1 delims=" %%D in ('""%MYSQL_BIN%\mysql.exe" -u%MYSQL_USER% -p%MYSQL_PASSWORD% -e "SHOW DATABASES LIKE 'ltkjpeis%%';" -s -N"') do (
    echo %%D | find /I "test" >nul
    if errorlevel 1 (
        set DB_NAME=%%D
        echo Backing up !DB_NAME!...

        :: 生成 SQL 文件
        "%MYSQL_BIN%\mysqldump.exe" -u%MYSQL_USER% -p%MYSQL_PASSWORD% --routines --triggers --events !DB_NAME! > "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql"

        :: 压缩为 ZIP（需要 PowerShell 5.0+）
        powershell -NoProfile -ExecutionPolicy Bypass -Command ^
        "Compress-Archive -Path '%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql' -DestinationPath '%BACKUP_DIR%\!DB_NAME!_%DATETIME%.zip'"

        :: 删除原始 SQL 文件
        del /F /Q "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql"
    ) else (
        echo Skipping database: %%D (contains "test")
    )
)

:: 删除超过 3 天的 ZIP 文件
powershell -NoProfile -ExecutionPolicy Bypass -Command ^
"Get-ChildItem -Path \"%BACKUP_DIR%\" -Filter *.zip ^| Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-3) } ^| Remove-Item -Force"

echo Backup complete. ZIP files saved in %BACKUP_DIR%.