#!/bin/bash

# -----------------------------------------------------
# Set ORACLE_HOME Directory
# -----------------------------------------------------
while true; do
    read -p "Enter ORACLE_HOME directory (e.g., /u01/app/oracle/product/19.0.0/client_1): " ORACLE_HOME

    if [ -z "$ORACLE_HOME" ]; then
        ORACLE_HOME="/u01/app/oracle/product/19.0.0/client_1"
        echo "[INFO] No input provided. Using default: $ORACLE_HOME"
    fi

    if [ -d "$ORACLE_HOME" ]; then
        echo "ORACLE_HOME is set to: $ORACLE_HOME"
        break
    else
        echo "[ERROR] The directory '$ORACLE_HOME' does not exist. Please check the path and try again."
    fi
done
export PATH=$ORACLE_HOME/bin:$PATH
echo "Using ORACLE_HOME: $ORACLE_HOME"

# -----------------------------------------------------
# Set directories based on the script's location
# -----------------------------------------------------
# Set the base directory (parent of the current script's directory)
MAIN_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"

JSM_DIR="$MAIN_DIR/database_objects/abssolute_jmsuser_user"
ABS_DIR="$MAIN_DIR/database_objects/abssolute_user"
DATABASE_DIR="$SCRIPT_DIR/database"

# Create 'completed' directories inside each SQL directory
COMPLETED_DIR_JSM="$JSM_DIR/completed"
COMPLETED_DIR_ABS="$ABS_DIR/completed"

mkdir -p "$COMPLETED_DIR_JSM"
mkdir -p "$COMPLETED_DIR_ABS"

if [ ! -d "$JSM_DIR" ]; then
    echo "[ERROR] Directory $JSM_DIR does not exist."
    exit 1
fi

if [ ! -d "$ABS_DIR" ]; then
    echo "[ERROR] Directory $ABS_DIR does not exist."
    exit 1
fi

# -----------------------------------------------------
# Prompt for the database SID
# -----------------------------------------------------
read -p "Enter the Database SID (e.g., ABS1): " DB_SID
if [ -z "$DB_SID" ]; then
    DB_SID="ABS1"
fi
echo "Using DB_SID: $DB_SID"

# -----------------------------------------------------
# Prompt for SYS password
# -----------------------------------------------------
while true; do
read -s -p "Enter SYS Password: " SYS_PASS
    if [ -z "$SYS_PASS" ]; then
        echo "[ERROR] SYS password cannot be empty. Please try again."
    else
        echo "[INFO] SYS password entered."
        break
    fi
done

# -----------------------------------------------------
# Get the new ConnSolace DB user, password, and existing DB user as input parameters
# -----------------------------------------------------
read -p "Enter the new ConnSolace DB user (e.g. ABSSOLUTE_JSM): " NEW_DB_USER
if [ -z "$NEW_DB_USER" ]; then
    NEW_DB_USER="ABSSOLUTE_JSM"
    echo "[INFO] No input provided. Using default: $NEW_DB_USER"
fi
echo "The new ConnSolace DB user is: $NEW_DB_USER"

while true; do
read -s -p "Enter the new ConnSolace user password (e.g. ABSSOLUTE_JSM): " NEW_DB_PASS
    if [ -z "$NEW_DB_PASS" ]; then
        echo "[ERROR] $NEW_DB_USER password cannot be empty. Please try again."
    else
        echo "[INFO] $NEW_DB_USER password entered."
        break
    fi
done

read -p "Enter the existing ABSSOLUTE DB user (e.g., ABSSOLUTE): " EXISTING_DB_USER
if [ -z "$EXISTING_DB_USER" ]; then
    EXISTING_DB_USER="ABSSOLUTE"
    echo "[INFO] No input provided. Using default: $EXISTING_DB_USER"
fi
echo "The new ConnSolace DB user is: $EXISTING_DB_USER"

# -----------------------------------------------------
# Log file for centralized errors
# -----------------------------------------------------
ERROR_LOG_FILE="$SCRIPT_DIR/script_execution_errors.log"
[ -f "$ERROR_LOG_FILE" ] && rm "$ERROR_LOG_FILE"

# -----------------------------------------------------
# Function to execute scripts from a specific directory
# -----------------------------------------------------
run_scripts() {
    local DIR=$1
    local COMPLETED_DIR=$2
    local LOG_DIR="$DIR/log"
    [ ! -d "$LOG_DIR" ] && mkdir -p "$LOG_DIR"  # Ensure log directory exists

    cd "$DIR" || {
        echo "[ERROR] Unable to access directory: $DIR" >> "$ERROR_LOG_FILE"
        return 1
    }

    # Check if there are any SQL files to run
    shopt -s nullglob  # Handle glob expansion safely
    SQL_FILES=("$DIR"/*.sql)
    shopt -u nullglob  # Reset nullglob to default

    if [ ${#SQL_FILES[@]} -eq 0 ]; then
        echo "[INFO] No SQL files found in $DIR. Skipping directory."
        return 0
    fi

    for SCRIPT in "${SQL_FILES[@]}"; do
        # Check if the SQL file exists in both 'completed' and 'database' directories
        if [ -f "$COMPLETED_DIR/$(basename "$SCRIPT")" ] || [ -f "$DATABASE_DIR/$(basename "$SCRIPT")" ]; then
            echo "[INFO] Script $(basename "$SCRIPT") already processed. Moving to $COMPLETED_DIR."
            mv "$SCRIPT" "$COMPLETED_DIR"
            continue
        fi

        echo "Running script: $SCRIPT"

        # Create a log file in the 'log' subdirectory relative to the current script directory
        SCRIPT_LOG_FILE="$LOG_DIR/$(basename "${SCRIPT%.sql}.log")"

        # Run SQL script
        sqlplus -s "sys/$SYS_PASS@$DB_SID as sysdba" @"$SCRIPT" "$NEW_DB_USER" "$NEW_DB_PASS" "$EXISTING_DB_USER" > "$SCRIPT_LOG_FILE" 2>&1

        # Check for errors in execution
        if [ $? -ne 0 ]; then
            echo "[ERROR] Script $SCRIPT failed in directory $DIR." >> "$ERROR_LOG_FILE"
            echo "Error details:" >> "$ERROR_LOG_FILE"
            cat "$SCRIPT_LOG_FILE" >> "$ERROR_LOG_FILE"
            echo >> "$ERROR_LOG_FILE"
            echo "[ERROR] Script $SCRIPT failed. Check $ERROR_LOG_FILE or $SCRIPT_LOG_FILE for details."
            return 1
        fi

        # Check for ORA errors in the log
        if grep -q "ORA-" "$SCRIPT_LOG_FILE"; then
            echo "[WARNING] ORA- error found in script $SCRIPT. Check $SCRIPT_LOG_FILE for details."
        fi

        echo "[INFO] Script $SCRIPT executed successfully. Log saved to $SCRIPT_LOG_FILE."
        echo

        # Move the successfully executed script to the 'completed' directory
        mv "$SCRIPT" "$COMPLETED_DIR"
    done

    echo "Scripts from directory $DIR completed successfully."
    return 0
}


# -----------------------------------------------------
# Execute scripts in both directories
# -----------------------------------------------------
run_scripts "$JSM_DIR" "$COMPLETED_DIR_JSM"
if [ $? -ne 0 ]; then
    echo "[ERROR] Failed to complete scripts in directory: $JSM_DIR. Exiting..."
    exit 1
fi

run_scripts "$ABS_DIR" "$COMPLETED_DIR_ABS"
if [ $? -ne 0 ]; then
    echo "[ERROR] Failed to complete scripts in directory: $ABS_DIR. Exiting..."
    exit 1
fi

echo "All scripts executed successfully from both directories."
exit 0
