@echo off
setlocal enabledelayedexpansion

:: Set database configuration
set DB_NAME=soilgd
set DB_USER=postgres

:: Get project root directory
set SCRIPT_DIR=%~dp0
for %%i in ("%SCRIPT_DIR%..\..\") do set PROJECT_ROOT=%%~fi
set BACKUP_FILE=%PROJECT_ROOT%soilgd.dump

echo =====================================
echo   PostgreSQL Database Restore Tool
echo =====================================
echo.

:: Check if backup file exists
if not exist "%BACKUP_FILE%" (
    echo Error: Backup file not found: %BACKUP_FILE%
    echo Please ensure soilgd.dump exists in project root directory.
    echo Project root: %PROJECT_ROOT%
    pause
    exit /b 1
)

echo Will restore database from backup file:
echo Backup file: %BACKUP_FILE%
echo.

set FULL_BACKUP_PATH=%BACKUP_FILE%

echo =====================================
echo WARNING: This will delete existing database and recreate it!
echo Database: %DB_NAME%
echo Backup file: %FULL_BACKUP_PATH%
echo =====================================
echo.
echo Continue? (Y/N)
set /p CONFIRM=
if /i not "%CONFIRM%"=="Y" (
    echo Operation cancelled.
    pause
    exit /b 0
)

echo.
echo Starting restore process...

:: Step 1: Terminate active connections and drop existing database
echo 1. Terminating active connections to database %DB_NAME%...
psql -U %DB_USER% -d postgres -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '%DB_NAME%' AND pid <> pg_backend_pid();" 2>nul

echo 2. Dropping existing database %DB_NAME%...
dropdb -U %DB_USER% %DB_NAME% 2>nul
if %ERRORLEVEL% EQU 0 (
    echo   Database dropped successfully
) else (
    echo   Database may not exist or cannot be dropped (this is usually normal)
)

:: Step 2: Create new empty database
echo 3. Creating new database %DB_NAME%...
createdb -U %DB_USER% %DB_NAME% 2>nul
if %ERRORLEVEL% NEQ 0 (
    echo   Database creation failed, attempting to force drop and recreate...
    
    :: Force drop database with more aggressive approach
    psql -U %DB_USER% -d postgres -c "DROP DATABASE IF EXISTS %DB_NAME%;" 2>nul
    
    :: Try creating again
    createdb -U %DB_USER% %DB_NAME%
    if %ERRORLEVEL% NEQ 0 (
        echo   Database creation still failed!
        echo   Possible causes:
        echo     1. PostgreSQL service not running
        echo     2. Insufficient user permissions
        echo     3. Database is in use by other processes
        echo   Please check and retry.
        pause
        exit /b 1
    )
)
echo   Database created successfully

:: Step 3: Restore data
echo 4. Restoring data from backup file...
pg_restore -U %DB_USER% -d %DB_NAME% "%FULL_BACKUP_PATH%"
if %ERRORLEVEL% EQU 0 (
    echo   Data restored successfully!
) else (
    echo   Data restore failed! Please check:
    echo     1. Is backup file corrupted?
    echo     2. Is PostgreSQL service running?
    echo     3. Are user permissions correct?
    pause
    exit /b 1
)

echo.
echo =====================================
echo Database restore completed successfully!
echo Database: %DB_NAME%
echo Backup source: %FULL_BACKUP_PATH%
echo =====================================
echo.
pause