@echo off
REM -----------------------------------------------------
REM Set ORACLE_HOME Directory
REM -----------------------------------------------------
SET /P ORACLE_HOME=Enter ORACLE_HOME directory (e.g., C:\Oracle\product\19.0.0\client_1): 
set PATH=%ORACLE_HOME%\bin;%PATH%

REM -----------------------------------------------------
REM Set directories based on the script's location
REM -----------------------------------------------------
set JSM_DIR=%~dp0abssolute_jmsuser_user
set ABS_DIR=%~dp0abssolute_user

REM -----------------------------------------------------
REM Prompt for the database SID
REM -----------------------------------------------------
set /p DB_SID="Enter the Database SID (e.g., ABS1): "

REM -----------------------------------------------------
REM Prompt for SYS password
REM -----------------------------------------------------
set /p SYS_PASS="Enter SYS Password: "
REM -----------------------------------------------------
REM Get the new ConnSolace  DB user, password, and existing DB user as input parameters
REM -----------------------------------------------------
set /p NEW_DB_USER="Enter the new ConnSolace DB user: "
set /p NEW_DB_PASS="Enter the new ConnSolace user password: "
set /p EXISTING_DB_USER="Enter the existing ABSSOLUTE DB user: "

REM Log file for centralized errors
set ERROR_LOG_FILE=%~dp0script_execution_errors.log
if exist %ERROR_LOG_FILE% del %ERROR_LOG_FILE%

REM -----------------------------------------------------
REM Function to execute scripts from a specific directory
REM -----------------------------------------------------
call :RunScripts %JSM_DIR%
if errorlevel 1 (
    echo [ERROR] Failed to complete scripts in directory: %JSM_DIR%. Exiting...
    exit /b 1
)

call :RunScripts %ABS_DIR%
if errorlevel 1 (
    echo [ERROR] Failed to complete scripts in directory: %ABS_DIR%. Exiting...
    exit /b 1
)

echo All scripts executed successfully from both directories.
exit /b 0

REM -----------------------------------------------------
REM Run Script Function
REM -----------------------------------------------------
:RunScripts
REM Create a logs directory within the given script directory
set LOG_DIR=%1\log
if not exist %LOG_DIR% mkdir %LOG_DIR%

REM Change directory to the specified folder
cd /d %1
if errorlevel 1 (
    echo [ERROR] Unable to access directory: %1
    exit /b 1
)

REM Loop through all SQL scripts in numeric order
for /f "tokens=*" %%F in ('dir /b /on *.sql') do (
    echo Running script: %%F
    set SCRIPT_LOG_FILE=%LOG_DIR%\%%~nF.log
    sqlplus -s "sys/%SYS_PASS%@%DB_SID% as sysdba" @%%F %NEW_DB_USER% %NEW_DB_PASS% %EXISTING_DB_USER% > "%%~nF.log" 2>&1
    if errorlevel 1 (
        echo [ERROR] Script %%F failed in directory %1. >> %ERROR_LOG_FILE%
        echo Error details: >> %ERROR_LOG_FILE%
        type "%SCRIPT_LOG_FILE%" >> %ERROR_LOG_FILE%
        echo. >> %ERROR_LOG_FILE%
        echo [ERROR] Script %%F failed in directory %1. Check %ERROR_LOG_FILE% or %SCRIPT_LOG_FILE% for details.
        exit /b 1
    ) else (
        echo Script %%F executed successfully. Log saved to %1\log\%%~nF.log.
    )
	
		REM Move the log file to the log directory
    move /Y %%~nF.log %1\log\	
	
	    REM Check the log file for ORA- errors
    findstr /m /c:"ORA-" %1\log\%%~nF.log >nul
    if not errorlevel 1 (
        echo [WARNING] ORA- error found in script %%F. Check log for details for details.
    )

    REM Display the content of the log file (optional, can be suppressed if needed)
    echo [INFO] Script %%F executed successfully. Log location: %1\log\%%~nF.log
    echo.

)
REM -----------------------------------------------------
REM Cleanup temporary files (optional)
REM -----------------------------------------------------
echo Scripts from directory %1 completed successfully.
cd %~dp0
Pause
exit /b 0