@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 MAIN_DIR=%~dp0..
for %%F in ("%MAIN_DIR%") do set MAIN_DIR=%%~fF

set JSM_DIR=%MAIN_DIR%\database_objects\abssolute_jmsuser_user
set ABS_DIR=%MAIN_DIR%\database_objects\abssolute_user
REM Verify the directories exist
if not exist "%JSM_DIR%" (
    echo ERROR: Directory %JSM_DIR% does not exist.
    exit /b 1
)
if not exist "%ABS_DIR%" (
    echo ERROR: Directory %ABS_DIR% does not exist.
    exit /b 1
)

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"
set "COMPLETED_DIR=%~1\completed"
if not exist "%LOG_DIR%" mkdir "%LOG_DIR%"
if not exist "%COMPLETED_DIR%" mkdir "%COMPLETED_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 Check if there are any scripts in the directory
set scriptFound=false
for /f "tokens=*" %%F in ('dir /b /on *.sql') do (
    set scriptFound=true
    REM Check if the script has already been run (exists in the completed folder)
    if exist "%COMPLETED_DIR%\%%F" (
        echo [INFO] Script %%F already run. Skipping execution.
        echo [INFO] Script %%F already moved to completed folder.
        REM Move the script to completed folder (if not already moved)
        move /Y "%%F" "%COMPLETED_DIR%" >nul
        echo.
        goto :RunScripts
    )

    REM Run the script
    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 "%LOG_DIR%\%%~nF.log".
    )
    
    REM Move the script to the completed folder after successful execution
    if exist "%%F" (
        move /Y "%%F" "%COMPLETED_DIR%\" >nul
        echo [INFO] Script %%F moved to completed folder.
    )
    
    REM Move the log file to the log directory
    if exist "%%~nF.log" (
        move /Y "%%~nF.log" "%LOG_DIR%\"
    )
    
    REM Check the log file for ORA- errors
    findstr /m /c:"ORA-" "%LOG_DIR%\%%~nF.log" >nul
    if not errorlevel 1 (
        echo [WARNING] ORA- error found in script %%F. Check log for details.
    )

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

REM If no scripts were found in the folder, notify the user
if not %scriptFound%==true (
    echo [INFO] No scripts present in directory %~1. Skipping execution.
)

REM -----------------------------------------------------
REM Cleanup temporary files (optional)
REM -----------------------------------------------------

echo Scripts from directory %~1 completed successfully.
cd %~dp0
exit /b 0
