@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
set SEVENZIP="C:\Program Files\7-Zip\7z.exe"

:: 创建备份目录
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"

        :: 使用 7-Zip 压缩 SQL 文件为 ZIP
        %SEVENZIP% a -tzip "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.zip" "%BACKUP_DIR%\!DB_NAME!_%DATETIME%.sql"

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

:: 删除超过 3 天的 ZIP 文件
forfiles /p "%BACKUP_DIR%" /m *.zip /d -3 /c "cmd /c del /F /Q @path"

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