#!/bin/bash

# -----------------------------------------------------
# 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)"

ABS_DIR="$MAIN_DIR/database_objects"
SRC_DIR="$ABS_DIR/solace_topic"

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

mkdir -p "$COMPLETED_DIR_ABS"

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

# -----------------------------------------------------
# Set Environment Name
# -----------------------------------------------------
while true; do
    read -rp "Enter ENVIRONMENT NAME (prod, uat, stg): " ENVIRONMENT
    ENVIRONMENT=$(echo "$ENVIRONMENT" | tr '[:upper:]' '[:lower:]')  # Convert to lowercase
    case "$ENVIRONMENT" in
        prod|uat|stg) break ;;
        *) echo "ERROR: Invalid environment! Please enter 'prod', 'uat', or 'stg'." ;;
    esac
done

# -----------------------------------------------------
# Copy the correct SQL file based on the selected environment
# -----------------------------------------------------
SRC_FILE="$SRC_DIR/06.INSERT_SOLACE_TOPIC_TABLE_${ENVIRONMENT}.sql"
DEST_FILE="$ABS_DIR/06.INSERT_SOLACE_TOPIC_TABLE_${ENVIRONMENT}.sql"

if [ -f "$SRC_FILE" ]; then
    cp -f "$SRC_FILE" "$DEST_FILE"
    echo "Successfully copied $SRC_FILE to $DEST_FILE"
else
    echo "ERROR: File $SRC_FILE does not exist."
    exit 1
fi

# -----------------------------------------------------
# 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"

# -----------------------------------------------------
# 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"

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

# -----------------------------------------------------
# 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
    SCRIPT_NAME=$(basename "$SCRIPT")

    # Skip execution if script is already in 'completed'
    if [ -f "$COMPLETED_DIR/$SCRIPT_NAME" ]; then
        echo "[INFO] Script $SCRIPT_NAME already completed. Skipping execution."
        continue
    fi

    echo "Running script: $SCRIPT"

    # Create a log file in the 'log' directory
    SCRIPT_LOG_FILE="$LOG_DIR/${SCRIPT_NAME%.sql}.log"

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

    # Check for execution errors
    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 script **only after successful execution**
    mv "$SCRIPT" "$COMPLETED_DIR"
done

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

# -----------------------------------------------------
# Execute scripts in both directories
# -----------------------------------------------------

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."
exit 0
