#!/bin/bash

# ────────────────────────────────────────────────────────────────────────
# Author: CVI ABSLBS Version: 1.0
# ORDS Installer - Automated Setup Script used with Tomcat as webservice
# This Script will Download Java Tomcat and ORDS based on defined version
# JAVA will get always the latest version upon instalaltion
# Tomcat to get latest one alter the tomcat_version parameterized
# ORDS get the latest version by altering ords_version
# InstantClient Is installed from Zip inside tools
# ────────────────────────────────────────────────────────────────────────

# ========= CONFIGURATION ==========
BASE_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
TOOLS_DIR="$BASE_DIR/tools"
BACKUP_DIR="$BASE_DIR/backup"
STATIC_ZIPS="$TOOLS_DIR/static_zips"

#################
# JRE setup
#################
jre_version="zulu21.42.19-ca-jre21.0.7"
jre_tar_static="${jre_version}-linux_x64.tar.gz"
jre_root_url="https://cdn.azul.com/zulu/bin/${jre_tar_static}"
jre_url=$(curl -s "https://api.azul.com/zulu/download/community/v1.0/bundles/?jre_version=21&os=linux&arch=x86_64&ext=tar.gz" | jq -r '
    [
      .[]
      | select(
          (
            if (.java_version | type) == "array"
            then .java_version[0] == 21
            elif (.java_version | type) == "object"
            then .java_version.major == 21
            else .java_version == 21
            end
          )
          and (.name | test("jre"))
          and (.name | test("fx") | not)
          and (.name | test("crac") | not)
      )
    ]
    | sort_by(.zulu_version)
    | last.url
  ')
if [[ -n "$jre_url" && "$jre_url" != "null" ]]; then
    jre_tar_latest=$(basename "$jre_url")
    jre_version_latest="${jre_tar_latest%%-linux_x64.tar.gz}"
fi

#################
# Tomcat setup
#################
tomcat_version="9.0.105"
tomcat_zip="apache-tomcat-${tomcat_version}.zip"
tomcat_url="https://archive.apache.org/dist/tomcat/tomcat-9/v${tomcat_version}/bin/${tomcat_zip}"
tomcat_main="9"
tomcat_latest=$(curl -s "https://downloads.apache.org/tomcat/tomcat-${tomcat_main}/" | \
grep -oE 'v[0-9]+\.[0-9]+\.[0-9]+/' | sed 's|[v/]||g' | sort -V | tail -1)
tomcat_latest_version="$tomcat_latest"
tomcat_latest_zip="apache-tomcat-${tomcat_latest_version}.zip"
tomcat_lastes_url="https://downloads.apache.org/tomcat/tomcat-${tomcat_main}/v${tomcat_latest_version}/bin/${tomcat_latest_zip}"

#################
# ORDS setup
#################
ords_version="25.1.0.100.1652"
ords_zip="ords-${ords_version}.zip"
ords_url="https://download.oracle.com/otn_software/java/ords/${ords_zip}"
ords_url_latest="https://download.oracle.com/otn_software/java/ords/ords-latest.zip"
ordsmain_url="https://www.oracle.com/database/sqldeveloper/technologies/db-actions/download/"
ords_latest="$(curl -s "$ordsmain_url" | grep -oP 'Version\s+\K[0-9]+(\.[0-9]+){3,4}' | head -1)"
ords_latest_zip="ords-${ords_latest}.zip"

#################
# Oracle Instant Client Configuration
#################
oracle_major_version="19"
oracle_patch_version="27"
oracle_full_version="${oracle_major_version}.${oracle_patch_version}.0.0.0dbru"
instantclient_folder="instantclient_${oracle_major_version}_${oracle_patch_version}"

INSTANT_CLIENT_ZIP_BASIC="instantclient-basic-linux.x64-${oracle_full_version}.zip"
INSTANT_CLIENT_ZIP_SQLPLUS="instantclient-sqlplus-linux.x64-${oracle_full_version}.zip"
INSTANT_CLIENT_BASIC_LOCAL="$STATIC_ZIPS/$INSTANT_CLIENT_ZIP_BASIC"
INSTANT_CLIENT_SQLPLUS_LOCAL="$STATIC_ZIPS/$INSTANT_CLIENT_ZIP_SQLPLUS"

# Check if ORACLE_HOME is present in bash_profile
USER_BASH_PROFILE="$HOME/.bash_profile"
ORACLE_HOME_IN_PROFILE=""
proceed_with_instant_client_install="yes" 

if [[ -f "$USER_BASH_PROFILE" ]]; then
  # Extract all ORACLE_HOME lines and check them all
  while IFS= read -r line; do
    # Extract the value
    extracted_home=$(echo "$line" | awk -F '=' '{print $2}' | tr -d ' ')
    if [[ "$extracted_home" == *instantclient* ]]; then
      echo -e "\033[1;33m⚠ Found ORACLE_HOME (Instant Client): $extracted_home\033[0m"
      # Keep looking for a better one
    else
      echo -e "\033[1;32m✅ Found full Oracle Client/DB ORACLE_HOME: $extracted_home\033[0m"
      ORACLE_HOME="$extracted_home"
      proceed_with_instant_client_install="no"
      break  # Stop at first full client found
    fi
  done < <(grep -E '^(export )?ORACLE_HOME=' "$USER_BASH_PROFILE")

  # After going through all, if only instantclient was found or nothing at all
  if [[ "$proceed_with_instant_client_install" == "yes" ]]; then
    echo -e "\033[1;33m⚠ No full Oracle installation detected, will install Instant Client.\033[0m"
    ORACLE_HOME="$BASE_DIR/$instantclient_folder"
  fi
else
  echo "ℹ .bash_profile not found. Proceeding with Instant Client installation..."
  ORACLE_HOME="$BASE_DIR/$instantclient_folder"
  proceed_with_instant_client_install="yes"
fi

# Use ORACLE_HOME later in the script

if [[ "$proceed_with_instant_client_install" == "yes" ]]; then
  unpack_instant_client
fi

# ========= ENV PATH SETUP ==========
BASE_DIR="$BASE_DIR"
JAVA_HOME="$BASE_DIR/jre"
CATALINA_HOME="$BASE_DIR/tomcat9"
ORDS_INSTALLER="$BASE_DIR/installer"
ORDS_CONFIG="$BASE_DIR/config"
ORDS_LOGS="$BASE_DIR/logs"
ORACLE_HOME="$ORACLE_HOME"
JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms2048M -Xmx2048M"

PATH="$ORACLE_HOME:$ORACLE_HOME/lib:$ORACLE_HOME/bin:$HOME/bin:$PATH:$ORDS_INSTALLER/bin:$PATH:$JAVA_HOME/bin:$PATH"
LD_LIBRARY_PATH="$ORACLE_HOME/lib"

export PATH ORACLE_HOME LD_LIBRARY_PATH JAVA_HOME CATALINA_HOME ORDS_CONFIG JAVA_OPTS
export TNS_ADMIN="$ORACLE_HOME/network/admin"

# ========= GENERIC DOWNLOAD FUNCTION ==========
download_or_use_local() {
  local url="$1"
  local local_file="$2"
  local target="$3"

  echo "🔽 Attempting to download: $url"
  if curl -L --fail -o "$target" "$url"; then
    echo "✔ Downloaded to $target"
  elif [[ -f "$local_file" ]]; then
    echo "⚠ Download failed. Using local copy from $local_file"
    cp "$local_file" "$target"
  else
    echo "❌ ERROR: Could not download or find local copy of $(basename "$target")"
    exit 1
  fi
}

# --- Instant Client Installation ---
if [[ "$proceed_with_instant_client_install" == "yes" ]]; then
  unpack_instant_client
fi

# ========= COMPONENT DOWNLOADS ==========

unpack_instant_client() {
echo "📦 Preparing Oracle Instant Client version ${oracle_full_version}"

  if [[ ! -f "$INSTANT_CLIENT_BASIC_LOCAL" || ! -f "$INSTANT_CLIENT_SQLPLUS_LOCAL" ]]; then
    echo "❌ Missing Oracle Instant Client ZIPs in tools/static_zips:"
    echo "   - $INSTANT_CLIENT_BASIC_LOCAL"
    echo "   - $INSTANT_CLIENT_SQLPLUS_LOCAL"
    echo
    echo "🔗 Please download them manually from:"
    echo "   https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html"
    echo "→ Save them into: $STATIC_ZIPS/"
    exit 1
  fi
  
  echo "🧹 Cleaning previous Oracle Instant Client installation..."
  rm -rf "$BASE_DIR"/instantclient_*
  
  echo "📂 Unzipping Oracle Instant Client..."
  unzip -o -q "$INSTANT_CLIENT_BASIC_LOCAL" -d "$BASE_DIR"
  rm -rf "$BASE_DIR/META-INF"
  unzip -o -q "$INSTANT_CLIENT_SQLPLUS_LOCAL" -d "$BASE_DIR"
  rm -rf "$BASE_DIR/META-INF"

  mkdir -p "$ORACLE_HOME/network/admin"
  if [[ -f "$TOOLS_DIR/tns_repo/tnsnames.ora" ]]; then
    cp "$TOOLS_DIR/tns_repo/tnsnames.ora" "$ORACLE_HOME/network/admin/"
  fi

  echo "📁 Organizing library files..."
  mkdir -p "$ORACLE_HOME/lib"  
  find "$ORACLE_HOME" -maxdepth 1 \( -type f -o -type l \) -name "lib*" -exec mv -f {} "$ORACLE_HOME/lib/" \;

  echo "📁 Organizing library files..."
  mkdir -p "$ORACLE_HOME/lib"
  find "$ORACLE_HOME" -maxdepth 1 \( -type f -o -type l \) -name "lib*" -exec mv -f {} "$ORACLE_HOME/lib/" \;
  
  echo "🔍 Testing sqlplus for missing library dependencies..."
  if ! "sqlplus" /nolog <<EOF
exit;
EOF
  then
    echo -e "\033[1;31m⚠️  WARNING: 'libnsl.so.1' is missing.\nPlease run as root or sudo: \033[1m'dnf install libnsl'\033[0m"
  fi

  echo "✅ Oracle Instant Client ready in $ORACLE_HOME"
}

download_jre() {
  echo "❓ Do you want to update to the latest JRE version ($jre_version_latest)? (y/n)"
  read -r -p "👉 Enter your choice: " answer

  case "$answer" in
    [Yy]* )
      echo "🔄 Switching to latest JRE update process: $jre_version_latest"
      download_jre_update
      return
      ;;
    [Nn]* )
      echo "📦 Proceeding with root JRE version: $jre_version"
      ;;
    * )
      echo "⚠️ Invalid input. Returning to main menu..."
      sleep 1
      show_menu
      return
      ;;
  esac

  echo "🌐 Fetching Zulu root JRE: $jre_version"

  local fallback="$STATIC_ZIPS/$jre_tar_static"
  local jre_tar="$jre_tar_static"
  local target="$TOOLS_DIR/$jre_tar"

  # Attempt to download from jre_root_url
  if [[ -n "$jre_root_url" && "$jre_root_url" != "null" ]]; then
    echo "🔽 Attempting to download from: $jre_root_url"
    if curl -L --fail -o "$target" "$jre_root_url"; then
      echo "✔ Downloaded root JRE to $target"
    else
      echo "⚠️ Failed to download JRE from URL. Checking fallback..."
      if [[ -f "$fallback" ]]; then
        echo "🧯 Using fallback archive: $fallback"
        cp "$fallback" "$target"
      else
        echo "❌ ERROR: No fallback available at $fallback"
        return 1
      fi
    fi
  else
    echo "⚠️ Root URL not set. Checking fallback..."
    if [[ -f "$fallback" ]]; then
      echo "🧯 Using fallback archive: $fallback"
      cp "$fallback" "$target"
    else
      echo "❌ ERROR: No fallback available at $fallback"
      return 1
    fi
  fi

  # Verify the file exists
  if [[ ! -f "$target" ]]; then
    echo "❌ ERROR: Expected tar file not found: $target"
    return 1
  fi

  echo "📦 Unpacking: $target to $JAVA_HOME"
  rm -rf "$JAVA_HOME"
  mkdir -p "$JAVA_HOME"
  tar -xzf "$target" --strip-components=1 -C "$JAVA_HOME"

  echo "✅ JRE installed at $JAVA_HOME"
}

download_jre_update() {
  echo "🌐 Fetching latest Zulu JRE 21 $jre_version_latest metadata..."
  latest_jdk_version=$(echo "$jre_version_latest" | grep -oP 'jre\K[0-9]+\.[0-9]+\.[0-9]+')

  # Check if a JRE is already installed
  if [[ -f "$JAVA_HOME/release" ]]; then
    installed_jdk_version=$(grep '^JAVA_VERSION=' "$JAVA_HOME/release" | cut -d'"' -f2)
    echo "📄 Installed JRE version: $installed_jdk_version"
    echo "🌐 Latest available JRE version: $latest_jdk_version"

    if [[ "$installed_jdk_version" == "$latest_jdk_version" ]]; then
      echo "✅ JRE is already up to date (version $installed_jdk_version). Skipping update."
      return 0
    else
      echo "🔄 JRE version mismatch. Updating..."
    fi
  else
    echo "ℹ️ No JRE found in $JAVA_HOME. Proceeding with installation..."
  fi

  if ! command -v jq >/dev/null 2>&1; then
    echo -e "\033[1;31m⚠️  WARNING: 'jq' is missing.\nPlease run as root or sudo: \033[1m'dnf install jq'\033[0m"
    return 1
  fi

  local fallback="$STATIC_ZIPS/$jre_tar_static"

  if [[ -n "$jre_url" && "$jre_url" != "null" ]]; then
    local target="$TOOLS_DIR/$jre_tar_latest"
    echo "📦 Resolved latest Zulu JRE 21: $jre_tar_latest"

    echo "🔽 Attempting to download from: $jre_url"
    if curl -L --fail -o "$target" "$jre_url"; then
      echo "✔ Downloaded latest JRE to $target"
    else
      echo "⚠️ Failed to download JRE from URL. Using local fallback: $jre_tar_static..."
      if [[ -f "$fallback" ]]; then
        jre_tar_latest="$jre_tar_static"
        target="$TOOLS_DIR/$jre_tar_latest"
        cp "$fallback" "$target"
      else
        echo "❌ ERROR: No fallback available at $fallback"
        return 1
      fi
    fi
  else
    echo "⚠️ Could not resolve JRE metadata. Attempting fallback..."
    if [[ -f "$fallback" ]]; then
      jre_tar_latest="$jre_tar_static"
      target="$TOOLS_DIR/$jre_tar_latest"
      cp "$fallback" "$target"
    else
      echo "❌ ERROR: No fallback available at $fallback"
      return 1
    fi
  fi

  if [[ ! -f "$TOOLS_DIR/$jre_tar_latest" ]]; then
    echo "❌ ERROR: Expected tar file not found: $TOOLS_DIR/$jre_tar_latest"
    return 1
  fi

  echo "📦 Unpacking: $TOOLS_DIR/$jre_tar_latest to $JAVA_HOME"
  rm -rf "$JAVA_HOME"
  mkdir -p "$JAVA_HOME"
  tar -xzf "$TOOLS_DIR/$jre_tar_latest" --strip-components=1 -C "$JAVA_HOME"

  echo "✅ JRE installed at $JAVA_HOME"
}

download_tomcat() {
  echo "❓ Do you want to update to the latest Tomcat version ($tomcat_latest_version)? (y/n)"
  read -r -p "👉 Enter your choice: " answer

  case "$answer" in
    [Yy]* )
      echo "🔄 Switching to latest Tomcat update process: $tomcat_latest_version"
      download_tomcat_update
      return
      ;;
    [Nn]* )
      echo "📦 Proceeding with root Tomcat version: $tomcat_version"
      ;;
    * )
      echo "⚠️ Invalid input. Returning to main menu..."
      sleep 1
      show_menu
      return
      ;;
  esac
  
  local target="$TOOLS_DIR/$tomcat_zip"
  download_or_use_local "$tomcat_url" "$STATIC_ZIPS/$tomcat_zip" "$target" 
  
  # Clean old Tomcat installation
  rm -rf "$CATALINA_HOME"
  mkdir -p "$CATALINA_HOME" 
  
  echo "📦 Unpacking Tomcat..."
  # Extract Tomcat in a temp location (inside TOOLS_DIR)
  tmp_tomcat_dir="$TOOLS_DIR/tmp_tomcat"
  rm -rf "$tmp_tomcat_dir"
  mkdir -p "$tmp_tomcat_dir"  
  unzip -q "$TOOLS_DIR/$tomcat_zip" -d "$tmp_tomcat_dir"  
  cp -r "$tmp_tomcat_dir/apache-tomcat-$tomcat_version/"* "$CATALINA_HOME" 

  # Cleanup temp folder
  rm -rf "$tmp_tomcat_dir"
  
  echo "✅ Tomcat unpacked to $CATALINA_HOME"
  
  # Apply Tomcat custom config
  TOMCAT_CONFIG_DIR="$TOOLS_DIR/tomcat_config"

  if [[ -f "$TOMCAT_CONFIG_DIR/server.xml" ]]; then
    echo "🔧 Updating Tomcat server.xml"
    mv "$CATALINA_HOME/conf/server.xml" "$CATALINA_HOME/conf/server.xml_old"
    cp "$TOMCAT_CONFIG_DIR/server.xml" "$CATALINA_HOME/conf/server.xml"
  fi

  if [[ -f "$TOMCAT_CONFIG_DIR/context.xml" ]]; then
    echo "🔧 Updating Tomcat context.xml"
    mv "$CATALINA_HOME/webapps/manager/META-INF/context.xml" "$CATALINA_HOME/webapps/manager/META-INF/context.xml_old"
    cp "$TOMCAT_CONFIG_DIR/context.xml" "$CATALINA_HOME/webapps/manager/META-INF/context.xml"
  fi

  if [[ -f "$TOMCAT_CONFIG_DIR/tomcat-users.xml" ]]; then
    echo "🔧 Updating Tomcat tomcat-users.xml"
    mv "$CATALINA_HOME/conf/tomcat-users.xml" "$CATALINA_HOME/conf/tomcat-users.xml_old"
    cp "$TOMCAT_CONFIG_DIR/tomcat-users.xml" "$CATALINA_HOME/conf/tomcat-users.xml"
  fi

  if [[ -f "$TOMCAT_CONFIG_DIR/localhost.p12" ]]; then
    echo "🔐 Copying localhost.p12 to Tomcat conf"
    cp "$TOMCAT_CONFIG_DIR/localhost.p12" "$CATALINA_HOME/conf/"
  fi

  if [[ -d "$TOMCAT_CONFIG_DIR/ROOT" ]]; then
    echo "📁 Copy Tomcat ROOT webapp..."
    rm -rf "$CATALINA_HOME/webapps/ROOT"
    cp -r "$TOMCAT_CONFIG_DIR/ROOT" "$CATALINA_HOME/webapps/ROOT"
  fi

  if [[ -d "$TOMCAT_CONFIG_DIR/org" ]]; then
    echo "📁 Hide Apache Tomcat version..."
    rm -rf "$CATALINA_HOME/lib/org"
    cp -r "$TOMCAT_CONFIG_DIR/org" "$CATALINA_HOME/lib/org"
  fi

  # 📌 Add Tomcat to start upon reboot in crontab
echo "🛠️ Ensuring Tomcat starts on reboot..."
cd "$TOOLS_DIR" 
chmod 775 start_ords.sh
dos2unix start_ords.sh

# Dump existing crontab into a temp file, or create a new one if empty
crontab -l 2>/dev/null > "$TOOLS_DIR/mycron.tmp" || touch "$TOOLS_DIR/mycron.tmp"

# Prepare the line to add
reboot_cmd="@reboot sleep 30 && $TOOLS_DIR/start_ords.sh"

# Add the line only if it's not already there
if ! grep -Fxq "$reboot_cmd" "$TOOLS_DIR/mycron.tmp"; then
  echo "$reboot_cmd" >> "$TOOLS_DIR/mycron.tmp"
  crontab "$TOOLS_DIR/mycron.tmp"
  echo "✅ Tomcat startup added to crontab."
else
  echo "ℹ️ Tomcat startup already present in crontab."
fi

# Clean up
rm -f "$TOOLS_DIR/mycron.tmp"
}

download_tomcat_update() {
  # Check existing Tomcat version from RELEASE
  local current_version=""
  if [[ -f "$CATALINA_HOME/RELEASE-NOTES" ]]; then
    current_version=$(grep -oP 'Apache Tomcat Version \K[0-9]+\.[0-9]+\.[0-9]+' "$CATALINA_HOME/RELEASE-NOTES")
    echo "📄 Existing Tomcat version detected: $current_version"
  fi

  # If current version matches latest version, skip update
  if [[ "$current_version" == "$tomcat_latest_version" ]]; then
    echo "✅ Tomcat is already up-to-date (version $current_version). Skipping update."
    return 0
  fi
  
  echo "⬇️ Proceeding to update Tomcat to version $tomcat_latest_version..."
  local target="$TOOLS_DIR/$tomcat_latest_zip"
  download_or_use_local "$tomcat_lastes_url" "$STATIC_ZIPS/$tomcat_latest_zip" "$target" 
  
  # Clean old Tomcat installation
  rm -rf "$CATALINA_HOME"
  mkdir -p "$CATALINA_HOME" 
  
  echo "📦 Unpacking Tomcat..."
  # Extract Tomcat in a temp location (inside TOOLS_DIR)
  tmp_tomcat_dir="$TOOLS_DIR/tmp_tomcat"
  rm -rf "$tmp_tomcat_dir"
  mkdir -p "$tmp_tomcat_dir"  
  unzip -q "$TOOLS_DIR/$tomcat_latest_zip" -d "$tmp_tomcat_dir"  
  cp -r "$tmp_tomcat_dir/apache-tomcat-$tomcat_latest_version/"* "$CATALINA_HOME" 

  # Cleanup temp folder
  rm -rf "$tmp_tomcat_dir"
  
  echo "✅ Tomcat unpacked to $CATALINA_HOME"
  
  # Apply Tomcat custom config
  TOMCAT_CONFIG_DIR="$TOOLS_DIR/tomcat_config"

  if [[ -f "$TOMCAT_CONFIG_DIR/server.xml" ]]; then
    echo "🔧 Updating Tomcat server.xml"
    mv "$CATALINA_HOME/conf/server.xml" "$CATALINA_HOME/conf/server.xml_old"
    cp "$TOMCAT_CONFIG_DIR/server.xml" "$CATALINA_HOME/conf/server.xml"
  fi

  if [[ -f "$TOMCAT_CONFIG_DIR/context.xml" ]]; then
    echo "🔧 Updating Tomcat context.xml"
    mv "$CATALINA_HOME/webapps/manager/META-INF/context.xml" "$CATALINA_HOME/webapps/manager/META-INF/context.xml_old"
    cp "$TOMCAT_CONFIG_DIR/context.xml" "$CATALINA_HOME/webapps/manager/META-INF/context.xml"
  fi

  if [[ -f "$TOMCAT_CONFIG_DIR/tomcat-users.xml" ]]; then
    echo "🔧 Updating Tomcat tomcat-users.xml"
    mv "$CATALINA_HOME/conf/tomcat-users.xml" "$CATALINA_HOME/conf/tomcat-users.xml_old"
    cp "$TOMCAT_CONFIG_DIR/tomcat-users.xml" "$CATALINA_HOME/conf/tomcat-users.xml"
  fi

  if [[ -f "$TOMCAT_CONFIG_DIR/localhost.p12" ]]; then
    echo "🔐 Copying localhost.p12 to Tomcat conf"
    cp "$TOMCAT_CONFIG_DIR/localhost.p12" "$CATALINA_HOME/conf/"
  fi

  if [[ -d "$TOMCAT_CONFIG_DIR/ROOT" ]]; then
    echo "📁 Copy Tomcat ROOT webapp..."
    rm -rf "$CATALINA_HOME/webapps/ROOT"
    cp -r "$TOMCAT_CONFIG_DIR/ROOT" "$CATALINA_HOME/webapps/ROOT"
  fi

  if [[ -d "$TOMCAT_CONFIG_DIR/org" ]]; then
    echo "📁 Hide Apache Tomcat version..."
    rm -rf "$CATALINA_HOME/lib/org"
    cp -r "$TOMCAT_CONFIG_DIR/org" "$CATALINA_HOME/lib/org"
  fi

  # 📌 Add Tomcat to start upon reboot in crontab
echo "🛠️ Ensuring Tomcat starts on reboot..."
cd "$TOOLS_DIR" 
chmod 775 start_ords.sh
dos2unix start_ords.sh

# Dump existing crontab into a temp file, or create a new one if empty
crontab -l 2>/dev/null > "$TOOLS_DIR/mycron.tmp" || touch "$TOOLS_DIR/mycron.tmp"

# Prepare the line to add
reboot_cmd="@reboot sleep 30 && $TOOLS_DIR/start_ords.sh"

# Add the line only if it's not already there
if ! grep -Fxq "$reboot_cmd" "$TOOLS_DIR/mycron.tmp"; then
  echo "$reboot_cmd" >> "$TOOLS_DIR/mycron.tmp"
  crontab "$TOOLS_DIR/mycron.tmp"
  echo "✅ Tomcat startup added to crontab."
else
  echo "ℹ️ Tomcat startup already present in crontab."
fi

# Clean up
rm -f "$TOOLS_DIR/mycron.tmp"
}

download_ords_update() {
  local ords_latest_major=$(echo "$ords_latest" | cut -d. -f1-3)  # e.g., 25.2.1

  if [[ -f "$ORDS_INSTALLER/NOTICE.txt" ]]; then
    local installed_version=$(grep -oP 'REST Data Services \K[0-9]+\.[0-9]+\.[0-9]+' "$ORDS_INSTALLER/NOTICE.txt")

    echo "📄 Installed ORDS version: $installed_version"
    echo "🌐 Latest ORDS version (from Oracle site):   $ords_latest_major"

    if [[ "$installed_version" == "$ords_latest_major" ]]; then
      echo "✅ ORDS $installed_version already installed. Skipping update."
      return 0
    else
      echo "🔁 ORDS version mismatch. Proceeding with update..."
    fi
  else
    echo "⚠️ No Installation not found in $ORDS_INSTALLER. Proceeding with update..."
  fi
  
  local target="$TOOLS_DIR/$ords_latest_zip"
  download_or_use_local "$ords_url_latest" "$STATIC_ZIPS/$ords_latest_zip" "$target"
  
  #  🔧 ORDS Parameters configuration
  export ORDS_HOME="$ORDS_INSTALLER"
    # Backup ORDS
  mkdir -p "$BACKUP_DIR"  
  if [[ -d "$ORDS_HOME" ]]; then
    echo "📦 Backing up ORDS from $ORDS_HOME"
    zip -r -q -o "$BACKUP_DIR/ords_backup.zip" "$ORDS_HOME"
  else
    echo "⚠ ORDS folder not found at $ORDS_HOME"
  fi
  
  echo "🧹 Cleaning previous ORDS installation..."
  rm -rf "$ORDS_INSTALLER"
  
  echo "📦 Unpacking ORDS..."
  mkdir -p "$ORDS_INSTALLER"
  unzip -q "$TOOLS_DIR/$ords_latest_zip" -d "$ORDS_INSTALLER"
  
  # 📁 Ensure global config folder exists
  mkdir -p "$ORDS_CONFIG/global"
  mkdir -p "$ORDS_LOGS"
  
  # 📄 Copy settings.xml to global config folder
  if [[ -f "$TOOLS_DIR/ords_conf/settings.xml" ]]; then
    cp "$TOOLS_DIR/ords_conf/settings.xml" "$ORDS_CONFIG/global/"
    echo "✅ settings.xml copied to $ORDS_CONFIG/global/"
  else
    echo "⚠️ settings.xml not found in $TOOLS_DIR/ords_conf/"
  fi
}

download_ords() {
echo "❓ Do you want to update to the latest ORDS version ($ords_latest)? (y/n)"
  read -r -p "👉 Enter your choice: " answer

  case "$answer" in
    [Yy]* )
      echo "🔄 Switching to latest ORDS update process: $ords_latest"
      download_ords_update
      return
      ;;
    [Nn]* )
      echo "📦 Proceeding with root ORDS version: $ords_version"
      ;;
    * )
      echo "⚠️ Invalid input. Returning to main menu..."
      sleep 1
      show_menu
      return
      ;;
  esac
  
  local target="$TOOLS_DIR/$ords_zip"
  download_or_use_local "$ords_url" "$STATIC_ZIPS/$ords_zip" "$target"
  
  #  🔧 ORDS Parameters configuration
  export ORDS_HOME="$ORDS_INSTALLER"
    # Backup ORDS
  mkdir -p "$BACKUP_DIR"  
  if [[ -d "$ORDS_HOME" ]]; then
    echo "📦 Backing up ORDS from $ORDS_HOME"
    zip -r -q -o "$BACKUP_DIR/ords_backup.zip" "$ORDS_HOME"
  else
    echo "⚠ ORDS folder not found at $ORDS_HOME"
  fi
  
  echo "🧹 Cleaning previous ORDS installation..."
  rm -rf "$ORDS_INSTALLER"
  
  echo "📦 Unpacking ORDS..."
  mkdir -p "$ORDS_INSTALLER"
  unzip -q "$TOOLS_DIR/$ords_zip" -d "$ORDS_INSTALLER"
  
  # 📁 Ensure global config folder exists
  mkdir -p "$ORDS_CONFIG/global"
  mkdir -p "$ORDS_LOGS"
  
  # 📄 Copy settings.xml to global config folder
  if [[ -f "$TOOLS_DIR/ords_conf/settings.xml" ]]; then
    cp "$TOOLS_DIR/ords_conf/settings.xml" "$ORDS_CONFIG/global/"
    echo "✅ settings.xml copied to $ORDS_CONFIG/global/"
  else
    echo "⚠️ settings.xml not found in $TOOLS_DIR/ords_conf/"
  fi
}

# ========= UNPACK COMPONENTS ==========

clean_all() {
  # Cleanup 
  echo "🧹 Cleaning downloaded zips..."
  cd "$TOOLS_DIR"
  rm -rf "$tomcat_zip" "$jre_tar" "$jre_tar_latest" "$ords_zip" "$ords_latest_zip" "$tomcat_latest_zip"
}

unpack_all() {
  echo "🔧 Preparing components..."
  command -v jq >/dev/null 2>&1 || {
    echo -e "\033[1;31m⚠️  WARNING: 'jq' is missing.\nPlease run as root or sudo: \033[1m'dnf install jq'\033[0m"
  }
  # Only unpack if Instant Client is needed
  if [[ "$proceed_with_instant_client_install" == "yes" ]]; then
    unpack_instant_client
  else
    echo "✅ Using existing ORACLE_HOME: $ORACLE_HOME"
  fi
  
  download_jre
  download_tomcat
  download_ords
  clean_all
  
  BASH_PROFILE="$HOME/.bash_profile"
  ENV_MARKER="# ORDS ORACLE_HOME JAVA and TOMCAT Variables"

  # Check if marker already exists
  if grep -qF "$ENV_MARKER" "$BASH_PROFILE"; then
    echo "✅ ORDS environment variables already set in $BASH_PROFILE."
  else
    echo "🔧 Adding ORDS environment variables to $BASH_PROFILE..."
    {
      echo ""
      echo "$ENV_MARKER"
      echo "export ORACLE_HOME=$ORACLE_HOME"
      echo "export JAVA_HOME=$JAVA_HOME"
      echo "export CATALINA_HOME=$CATALINA_HOME"
      echo "export ORDS_CONFIG=$ORDS_CONFIG"
      echo "export JAVA_OPTS=\"-Dconfig.url=\${ORDS_CONFIG} -Xms1024M -Xmx1024M\""
      echo "export PATH=\$ORACLE_HOME:\$ORACLE_HOME/lib:\$ORACLE_HOME/bin:\$HOME/bin:\$PATH:$ORDS_INSTALLER/bin:\$JAVA_HOME/bin:\$PATH"
      echo "export LD_LIBRARY_PATH=\$ORACLE_HOME/lib"
      echo "export PATH ORACLE_HOME LD_LIBRARY_PATH CATALINA_HOME ORDS_CONFIG JAVA_OPTS"
    } >> "$BASH_PROFILE"
    echo "✅ ORDS environment variables appended to $BASH_PROFILE."
  fi
    
  echo "✔ All components ready."
  sleep 2
}

# ========= UPDATE ORDS ==========
update_ords() {
  echo "🔐 Preparing to deploy ORDS to the database..."

  local TNS_FILE="$ORACLE_HOME/network/admin/tnsnames.ora"
  
  if [[ ! -f "$TNS_FILE" ]]; then
    echo "❌ ERROR: $TNS_FILE not found. Cannot continue."
    return 1
  fi

  echo "📡 Available TNS entries:"
  grep -E '^[A-Za-z0-9_.-]+[[:space:]]*=' "$TNS_FILE" | cut -d '=' -f1 | awk '{$1=$1};1' | nl
  echo

  read -p "Enter the number of the TNS entry to use: " tns_choice
  TNS_ENTRY=$(grep -E '^[A-Za-z0-9_.-]+[[:space:]]*=' "$TNS_FILE" | cut -d '=' -f1 | awk '{$1=$1};1' | sed -n "${tns_choice}p")

  if [[ -z "$TNS_ENTRY" ]]; then
    echo "❌ Invalid selection."
    return 1
  fi
  echo -e "\033[1;31m🔧 Use these to update the DB SID to latest version Apply these to all SID listed in $TNS_FILE\033[0m"
  echo -e "\033[1;31m🔧 [S] Specify the database connection to install/upgrade ORDS - this will update $TNS_ENTRY\033[0m"
  
#  🔧 ORDS Parameters configuration
  export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"
  export ORDS_HOME="$ORDS_INSTALLER"
  export ORDS_CONFIG="$ORDS_CONFIG"
  export ORDS_LOGS="$ORDS_LOGS"
  mkdir -p "$ORDS_LOGS"
  
  # 🚀 Install ORDS using parameterized file
  "$ORDS_HOME/bin/ords" --config "$ORDS_CONFIG" install --interactive --db-only --log-folder "$ORDS_LOGS"
}

# ========= INSTALL ORDS ==========
install_ords() {
  echo "🔐 Preparing to deploy ORDS to the database..."

  local TNS_FILE="$ORACLE_HOME/network/admin/tnsnames.ora"
  local SQL_SCRIPTS="$TOOLS_DIR/TI_Scripts"
  local LOG_DIR="$SQL_SCRIPTS/logs"
  mkdir -p "$LOG_DIR"

  if [[ ! -f "$TNS_FILE" ]]; then
    echo "❌ ERROR: $TNS_FILE not found. Cannot continue."
    return 1
  fi
  echo "📡 Available TNS entries:"
  grep -E '^[A-Za-z0-9_.-]+[[:space:]]*=' "$TNS_FILE" | cut -d '=' -f1 | awk '{$1=$1};1' | nl
  echo

  read -p "Enter the number of the TNS entry to use: " tns_choice
  TNS_ENTRY=$(grep -E '^[A-Za-z0-9_.-]+[[:space:]]*=' "$TNS_FILE" | cut -d '=' -f1 | awk '{$1=$1};1' | sed -n "${tns_choice}p")

  if [[ -z "$TNS_ENTRY" ]]; then
    echo "❌ Invalid selection."
    return 1
  fi

  echo -n "🔑 Enter SYS password (input hidden): "
  read -s sys_pass
  echo

  # Create ORDS user
  echo -n "🔐 Enter ORDS user password: "
  read -s ords_pass
  echo
  
  echo "📥 AS SYS USER - Creating ORDS user..."
  local ords_user_sql="$SQL_SCRIPTS/create_ords_user_assys.sql"
  "sqlplus" -s "sys/${sys_pass}@${TNS_ENTRY} as sysdba" @"$ords_user_sql" "$ords_pass" > "$LOG_DIR/ords_user_creation.log"

  # Prompt to create REST user
  read -p "Do you want to create a REST user? (y/n): " create_rest_user
  rest_user_created="no"
  
  if [[ "$create_rest_user" =~ ^[Yy]$ ]]; then
    rest_user_created="yes"
  
    while true; do
      read -p "👤 Enter ABSSOLUTE username (e.g. ABSSOLUTE): " abssolute_user
      if [[ -n "$abssolute_user" && "$abssolute_user" =~ ^[A-Z0-9_]+$ ]]; then
        break
      else
        echo -e "\e[31m❌ Username must be non-empty and uppercase letters/numbers/underscores only.\e[0m"
      fi
    done
  
    rest_user="${abssolute_user}_REST"
    echo -e "🧩 REST user will be: \e[32m${rest_user}\e[0m"
  
    mkdir -p "$TOOLS_DIR/DB_Scripts/$abssolute_user"
    echo -e "📁 Created directory: \e[34m$TOOLS_DIR/DB_Scripts/$abssolute_user\e[0m"
  
    echo -n "🔐 Enter password for ${rest_user}: "
    read -s rest_pass
    echo

    # Normalize REST user to lowercase
    rest_low_caps=$(echo "$rest_user" | tr '[:upper:]' '[:lower:]')
    echo "🔠 REST user in lowercase: $rest_low_caps"
  
    local rest_user_sql="$SQL_SCRIPTS/create_rest_user_assys.sql"
    echo "⚙ AS SYS USER - Creating REST user..."
    sqlplus -s "sys/${sys_pass}@${TNS_ENTRY} as sysdba" @"$rest_user_sql" "$rest_user" "$rest_pass" > "$LOG_DIR/rest_user_creation.log"
  fi
  
    # ORDS Install
  echo "✅ ORDS user ready. Proceeding to install ORDS..."
  list_dbpools
  unset pool_name
  while [[ -z "$pool_name" ]]; do
    echo -en "\e[1;33m⚙ Enter the name for the new or existing database pool - one per database e.g., live, test): \e[0m"
    read -e pool_name
  done

  
#  🔧 ORDS Parameters configuration
  export JAVA_TOOL_OPTIONS="--enable-native-access=ALL-UNNAMED"
  export ORDS_HOME="$ORDS_INSTALLER"
  export ORDS_CONFIG="$ORDS_CONFIG"
  export ORDS_LOGS="$ORDS_LOGS"
  mkdir -p "$ORDS_LOGS"
  
  # 🚀 Install ORDS using parameterized file
  "$ORDS_HOME/bin/ords" --config "$ORDS_CONFIG" install --interactive --db-pool "$pool_name" --log-folder "$ORDS_LOGS"
  
  # ✅ Check result
  if [[ $? -ne 0 ]]; then
    echo "❌ ORDS installation failed."
    return 1
  fi
  
  echo "✅ ORDS installation completed successfully."


  echo "📜 Running post-install scripts as ORDS user..."
  echo "🎯 Granting ORDS administrator role to ORDS user..."

  echo "grant ords_administrator_role to ORDS;" | "sqlplus" -s "sys/${sys_pass}@${TNS_ENTRY} as sysdba" > "$LOG_DIR/grant_ords_admin_role.log"

  if grep -q "ORA-" "$LOG_DIR/grant_ords_admin_role.log"; then
  echo "❌ Failed to grant ORDS administrator role. Check log: $LOG_DIR/grant_ords_admin_role.log"
     else
  echo "✅ ORDS administrator role granted to ORDS user."
  fi
  
  local access_script="$SQL_SCRIPTS/access_ords_to_rest_asords.sql"
  local client_script="$SQL_SCRIPTS/create_client_access_asrest.sql"
  local acl_sql="$SQL_SCRIPTS/create_ords_acl_assys.sql"
  
  if [[ "$rest_user_created" == "yes" ]]; then
    echo "⚙ AS ORDS USER - Running Access ORDS to REST script..."
    "sqlplus" -s "ORDS/${ords_pass}@${TNS_ENTRY}" @"$access_script" "$rest_user" "$rest_low_caps"  > "$LOG_DIR/access_ords_to_rest.log"
  
    echo "⚙ AS REST USER - Running Create Client Access script..."
    "sqlplus" -s "${rest_user}/${rest_pass}@${TNS_ENTRY}" @"$client_script" "$rest_user" > "$LOG_DIR/create_client_access.log"
  else
    echo "ℹ Skipping REST user scripts because no REST user was created."
  fi
  
  echo "⚙ AS SYS USER - Running ORDS ACL script..."
  "sqlplus" -s "sys/${sys_pass}@${TNS_ENTRY} as sysdba" @"$acl_sql" > "$LOG_DIR/ords_acl.log"
  
  echo "✅ Post-install scripts executed."
  
   # Run SQL via sqlplus show client
  result=$(sqlplus -s "${rest_user}/${rest_pass}@$TNS_ENTRY" <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 200
SELECT 'Name of the Client: ' || name || CHR(10) ||
       'Client ID is: ' || client_id || CHR(10) ||
       'Client Secret is: ' || client_secret || CHR(10) ||
       'Created by: ' || created_by || CHR(10) ||
       'Created on: ' || TO_CHAR(created_on, 'YYYY-MM-DD HH24:MI:SS') || CHR(10) ||
       '----------------------------------------'
FROM User_Ords_Clients;
EXIT;
EOF
)
  # Display results nicely
  echo -e "\n\e[1;32m📋 ORDS Clients for user ${rest_user}:\e[0m\n"
  if [[ -n "$result" ]]; then
    echo -e "$result"
  else
    echo -e "\e[33m⚠️ No clients found or query returned no data.\e[0m"
  fi
  
    echo "✅ ORDS installation and setup completed successfully."
}

# ========= REPAIR ORDS ==========
repair_ords() {
  echo "🔐 Repairing ORDS installation..."
  
  read -p "❓ Are you sure you want to repair ORDS $ords_version installation? [y/N]: " confirm_repair
  if [[ ! "$confirm_repair" =~ ^[Yy]$ ]]; then
    echo "❌ Aborting ORDS Repair."
    return 1
  fi

  # 🔧 ORDS Parameters configuration
  export ORDS_HOME="$ORDS_INSTALLER"
  export ORDS_CONFIG="$ORDS_CONFIG"
  export ORDS_LOGS="$ORDS_LOGS"
  mkdir -p "$ORDS_LOGS"
  
  # 🚀 Repair ORDS using parameterized file
  "$ORDS_HOME/bin/ords" --config "$ORDS_CONFIG" install repair --interactive --log-folder "$ORDS_LOGS"
  
  # ✅ Check result
  if [[ $? -ne 0 ]]; then
    echo "❌ ORDS repair failed."
    return 1
  else
    echo "✅ ORDS repaired successfully."
  fi
}

# ========= Uninstall ORDS ==========
uninstall_ords() {
  echo "🔐 This will uninstall only the ORDS from the selected database interactively..."
  
  read -p "❓ Are you sure you want to Uninstall ORDS $ords_version from the Database? [y/N]: " confirm_uninstall
  if [[ ! "$confirm_uninstall" =~ ^[Yy]$ ]]; then
    echo "❌ Aborting ORDS Uninstall."
    return 1
  fi

  # 🔧 ORDS Parameters configuration
  export ORDS_HOME="$ORDS_INSTALLER"
  export ORDS_CONFIG="$ORDS_CONFIG"
  export ORDS_LOGS="$ORDS_LOGS"
  mkdir -p "$ORDS_LOGS"
  
  # 🚀 Uninstall ORDS using parameterized file
  "$ORDS_HOME/bin/ords" --config "$ORDS_CONFIG" uninstall --interactive --log-folder "$ORDS_LOGS"
  
  # ✅ Check result
  if [[ $? -ne 0 ]]; then
    echo "❌ ORDS uninstall failed."
    return 1
  else
    echo "✅ ORDS uninstalled successfully."
  fi
}

# ========= DEPLOY WAR ==========
deploy_ords_to_tomcat() {
  echo "🚀 Deploying ORDS WAR to Tomcat..."

  # Go to Tomcat bin folder and ensure scripts are executable
  cd "$CATALINA_HOME/bin/"
  chmod 775 *.sh

  # Clean existing ORDS deployment (WAR and exploded folder)
  if [[ -f "$CATALINA_HOME/webapps/ords.war" ]]; then
    echo "🗑 Removing existing ords.war..."
    rm -f "$CATALINA_HOME/webapps/ords.war"
  fi

  if [[ -d "$CATALINA_HOME/webapps/ords" ]]; then
    echo "🗑 Removing existing exploded ords folder..."
    rm -rf "$CATALINA_HOME/webapps/ords"
  fi

  # Deploy new ORDS WAR
  if [[ -f "$ORDS_INSTALLER/ords.war" ]]; then
    cp "$ORDS_INSTALLER/ords.war" "$CATALINA_HOME/webapps/"
    echo "✔ ORDS deployed to Tomcat webapps folder."
  else
    echo "❌ ORDS WAR not found at: $ORDS_INSTALLER/ords.war"
  fi
}

# ========= Run Database Scripts ==========
run_db_and_rest_scripts() {
  local TNS_FILE="$ORACLE_HOME/network/admin/tnsnames.ora"

  # --- Validate TNS file ---
  if [[ ! -f "$TNS_FILE" ]]; then
    echo "❌ ERROR: $TNS_FILE not found. Cannot continue."
    return 1
  fi

  echo -e "\e[1;34m📡 Available TNS entries:\e[0m"

  # --- Gather available TNS entries ---
  mapfile -t tns_entries < <(grep -E '^[A-Za-z0-9_.-]+[[:space:]]*=' "$TNS_FILE" | cut -d '=' -f1 | awk '{$1=$1};1')

  # --- Validate TNS entries ---
  if [[ ${#tns_entries[@]} -eq 0 ]]; then
    echo -e "\e[31m❌ No TNS entries found in $TNS_FILE.\e[0m"
    return 1
  fi

  # --- Display TNS entries for selection ---
  for i in "${!tns_entries[@]}"; do
    echo "  $((i+1)). ${tns_entries[$i]}"
  done
  echo

  # --- Force valid TNS selection ---
  local tns_choice
  while true; do
    read -p "👉 Enter the number of the TNS entry to use (1-${#tns_entries[@]}): " tns_choice
    if [[ "$tns_choice" =~ ^[0-9]+$ ]] && (( tns_choice >= 1 && tns_choice <= ${#tns_entries[@]} )); then
      TNS_ENTRY="${tns_entries[$((tns_choice-1))]}"
      break
    else
      echo -e "\e[31m❌ Invalid selection. Please enter a number between 1 and ${#tns_entries[@]}.\e[0m"
    fi
  done

  echo -e "\n\e[1;34m📁 Available ABSSOLUTE user directories:\e[0m"

  # --- Gather ABSSOLUTE user directories ---
  mapfile -t user_dirs < <(find "$TOOLS_DIR/DB_Scripts" -maxdepth 1 -type d ! -path "$TOOLS_DIR/DB_Scripts" -exec basename {} \;)

  # --- Validate DB directories ---
  if [[ ${#user_dirs[@]} -eq 0 ]]; then
    echo -e "\e[31m❌ No ABSSOLUTE user directories found in $TOOLS_DIR/DB_Scripts.\e[0m"
    echo -e "Please create one first using the REST user creation option."
    return 1
  fi

  # --- Display DB directories ---
  for i in "${!user_dirs[@]}"; do
    echo "  $((i+1)). ${user_dirs[$i]}"
  done
  echo

  # --- Force valid directory selection ---
  local choice
  while true; do
    read -p "👉 Select the ABSSOLUTE user to use (1-${#user_dirs[@]}): " choice
    if [[ "$choice" =~ ^[0-9]+$ ]] && (( choice >= 1 && choice <= ${#user_dirs[@]} )); then
      abssolute_user="${user_dirs[$((choice-1))]}"
      break
    else
      echo -e "\e[31m❌ Invalid selection. Please enter a number between 1 and ${#user_dirs[@]}.\e[0m"
    fi
  done

  # --- Derive usernames ---
  db_user="$abssolute_user"
  rest_user="${abssolute_user}_REST"

  # --- Prompt for passwords ---
  echo -e "\n\e[1;34m🔐 Enter passwords for selected users:\e[0m"
  echo -e "👤 ABSSOLUTE (DB) user: \e[32m$db_user\e[0m"
  echo -en "🔑 Password for $db_user: "
  read -s db_pass
  echo

  echo -e "\n👤 REST user: \e[32m$rest_user\e[0m"
  echo -en "🔑 Password for $rest_user: "
  read -s rest_pass
  echo

  # --- Define working directories ---
  local db_dir="$TOOLS_DIR/DB_Scripts/$db_user/dbuser"
  local rest_dir="$TOOLS_DIR/DB_Scripts/$db_user/rest"
  local DBLOG_DIR="$TOOLS_DIR/DB_Scripts/$db_user/logs"
  mkdir -p "$DBLOG_DIR"

  # --- Define script extensions to execute ---
  local ordered_extensions=("*.prc" "*.fnc" "*.pck" "*.sql")

  # --- Run DB user scripts ---
  if [[ -d "$db_dir" ]]; then
    echo -e "\n🚀 Running DB scripts for \e[33m$db_user\e[0m from \e[36m$db_dir\e[0m"
    for ext in "${ordered_extensions[@]}"; do
      for script in "$db_dir"/$ext; do
        [[ -f "$script" ]] || continue
        echo -e "\n📄 Executing: \e[32m$(basename "$script")\e[0m"
        log_file="$DBLOG_DIR/$(basename "$script" .${script##*.})_${db_user}.log"

        {
          cat "$script"
          echo "EXIT;"
        } | sqlplus -s "$db_user/$db_pass@$TNS_ENTRY" > "$log_file" 2>&1

        if grep -q "ORA-" "$log_file"; then
          echo -e "❌ \e[31mErrors detected in script execution!\e[0m"
          grep "ORA-" "$log_file"
        else
          echo -e "✅ \e[33mScript executed successfully (no errors).\e[0m"
        fi

        echo "📝 ABSSoluteLog saved to: $log_file"
      done
    done
  else
    echo -e "\e[31m❌ DB scripts directory not found: $db_dir\e[0m"
  fi

  # --- Run REST user scripts ---
  if [[ -d "$rest_dir" ]]; then
    echo -e "\n🚀 Running REST scripts for \e[33m$rest_user\e[0m from \e[36m$rest_dir\e[0m"
    for ext in "${ordered_extensions[@]}"; do
      for script in "$rest_dir"/$ext; do
        [[ -f "$script" ]] || continue
        echo -e "\n📄 Executing: \e[32m$(basename "$script")\e[0m"
        log_file="$DBLOG_DIR/$(basename "$script" .${script##*.})_${rest_user}.log"

        {
          cat "$script"
          echo "EXIT;"
        } | sqlplus -s "$rest_user/$rest_pass@$TNS_ENTRY" > "$log_file" 2>&1

        if grep -q "ORA-" "$log_file"; then
          echo -e "❌ \e[31mErrors detected in script execution!\e[0m"
          grep "ORA-" "$log_file"
        else
          echo -e "✅ \e[33mScript executed successfully (no errors).\e[0m"
        fi

        echo "📝 RestLog saved to: $log_file"
      done
    done
  else
    echo -e "\e[31m❌ REST scripts directory not found: $rest_dir\e[0m"
  fi
}

# ========= Display DBPools ==========
list_dbpools() {
    local DBPOOL="${ORDS_CONFIG}/databases"

    if [[ ! -d "$DBPOOL" ]]; then
        echo -e "\e[31m❌ DBPool directory not found: $DBPOOL\e[0m"
        return 1
    fi

    echo -e "\e[1;33m⚙ ORDS DatabasePool's installed in $DBPOOL:\e[0m"

    local found_any=false

    # Use find to list only directories, ignoring files or broken symlinks
    while IFS= read -r -d '' dir; do
        folder_name=$(basename "$dir")
        echo -e "  \e[33m✔$folder_name\e[0m"
        found_any=true
    done < <(find "$DBPOOL" -mindepth 1 -maxdepth 1 -type d -print0 | sort -z)

    if ! $found_any; then
        echo -e "\e[33mℹ️ No database pools found.\e[0m"
    fi
}

# ========= Display Client ID's ==========
list_ords_clients() {
  local TNS_FILE="$ORACLE_HOME/network/admin/tnsnames.ora"

  # --- Validate TNS file ---
  if [[ ! -f "$TNS_FILE" ]]; then
    echo "❌ ERROR: $TNS_FILE not found. Cannot continue."
    return 1
  fi

  echo -e "\e[1;34m📡 Available TNS entries:\e[0m"

  # --- Gather available TNS entries ---
  mapfile -t tns_entries < <(grep -E '^[A-Za-z0-9_.-]+[[:space:]]*=' "$TNS_FILE" | cut -d '=' -f1 | awk '{$1=$1};1')

  # --- Validate TNS entries ---
  if [[ ${#tns_entries[@]} -eq 0 ]]; then
    echo -e "\e[31m❌ No TNS entries found in $TNS_FILE.\e[0m"
    return 1
  fi

  # --- Display TNS entries for selection ---
  for i in "${!tns_entries[@]}"; do
    echo "  $((i+1)). ${tns_entries[$i]}"
  done
  echo

  # --- Force valid TNS selection ---
  local tns_choice
  while true; do
    read -p "👉 Enter the number of the TNS entry to use (1-${#tns_entries[@]}): " tns_choice
    if [[ "$tns_choice" =~ ^[0-9]+$ ]] && (( tns_choice >= 1 && tns_choice <= ${#tns_entries[@]} )); then
      TNS_ENTRY="${tns_entries[$((tns_choice-1))]}"
      break
    else
      echo -e "\e[31m❌ Invalid selection. Please enter a number between 1 and ${#tns_entries[@]}.\e[0m"
    fi
  done

  echo -e "\n\e[1;34m📂 Listing available ABSSOLUTE users in DB_Scripts:\e[0m"

  # --- Gather DB user directories ---
  mapfile -t user_dirs < <(find "$TOOLS_DIR/DB_Scripts" -maxdepth 1 -type d ! -path "$TOOLS_DIR/DB_Scripts" -exec basename {} \;)

  # --- Validate DB directories ---
  if [[ ${#user_dirs[@]} -eq 0 ]]; then
    echo -e "\e[31m❌ No ABSSOLUTE user directories found in $TOOLS_DIR/DB_Scripts.\e[0m"
    return 1
  fi

  # --- Display DB directories ---
  for i in "${!user_dirs[@]}"; do
    echo "  $((i+1)). ${user_dirs[$i]}"
  done
  echo

  # --- Force valid directory selection ---
  local choice
  while true; do
    read -p "👉 Select which ABSSOLUTE user to use (1-${#user_dirs[@]}): " choice
    if [[ "$choice" =~ ^[0-9]+$ ]] && (( choice >= 1 && choice <= ${#user_dirs[@]} )); then
      abssolute_user="${user_dirs[$((choice-1))]}"
      break
    else
      echo -e "\e[31m❌ Invalid selection. Please choose a valid number.\e[0m"
    fi
  done

  # --- Derive REST user ---
  rest_user="${abssolute_user}_REST"

  # --- Prompt for password ---
  echo -en "\n🔐 Enter password for REST user \e[32m${rest_user}\e[0m: "
  read -s rest_pass
  echo

  echo -e "\n\e[1;34m🔗 Connecting to database as ${rest_user}@${TNS_ENTRY}...\e[0m"

  # --- Run SQL query ---
  result=$(sqlplus -s "${rest_user}/${rest_pass}@${TNS_ENTRY}" <<EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 200
SELECT 'Name of the Client: ' || name || CHR(10) ||
       'Client ID is: ' || client_id || CHR(10) ||
       'Client Secret is: ' || client_secret || CHR(10) ||
       'Created by: ' || created_by || CHR(10) ||
       'Created on: ' || TO_CHAR(created_on, 'YYYY-MM-DD HH24:MI:SS') || CHR(10) ||
       '----------------------------------------'
FROM User_Ords_Clients;
EXIT;
EOF
)

  # --- Display results nicely ---
  echo -e "\n\e[1;32m📋 ORDS Clients for user ${rest_user}:\e[0m\n"
  if [[ -n "$result" ]]; then
    echo -e "$result"
  else
    echo -e "\e[33m⚠️ No clients found or query returned no data.\e[0m"
  fi
}

# ========= SHOW ENV ==========
show_env() {
  echo "🔧 Environment Configuration:"
  echo "BASE_DIR        = $BASE_DIR"
  echo "TOOLS_DIR       = $TOOLS_DIR"
  echo "JAVA_HOME       = $JAVA_HOME"
  echo "JAVA_OPTS       = $JAVA_OPTS"
  echo "CATALINA_HOME   = $CATALINA_HOME"
  echo "ORDS_INSTALLER  = $ORDS_INSTALLER"
  echo "ORDS_CONFIG     = $ORDS_CONFIG"
  echo "ORACLE_HOME     = $ORACLE_HOME"
  echo "PATH            = $PATH"
  echo "LD_LIBRARY_PATH = $LD_LIBRARY_PATH"
}

# ========= START TOMCAT ==========
start_tomcat() {
export JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms1024M -Xmx1024M"
chmod 775 "$CATALINA_HOME/bin/startup.sh"
"$CATALINA_HOME/bin/startup.sh"
echo "🚀 Tomcat application started... 🚀"
}

# ========= STOP FORCE TOMCAT ==========
force_kill_tomcat() {
  echo "🔍 Checking for Tomcat processes running from: $CATALINA_HOME"

  if ps -ef | grep "$CATALINA_HOME" | grep java | grep -v grep > /dev/null; then
    echo "⚠️ Tomcat process detected. Attempting to force kill..."
    pkill -9 -f "$CATALINA_HOME"
    echo "✅ Tomcat process killed."
  else
    echo "✔ No Tomcat process running from $CATALINA_HOME"
  fi
}


# ========= STOP TOMCAT ==========
stop_tomcat() {
export JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms1024M -Xmx1024M"
chmod 775 "$CATALINA_HOME/bin/shutdown.sh" 
"$CATALINA_HOME/bin/shutdown.sh"
force_kill_tomcat
echo "❌ Tomcat application stoped... ❌"
}

# ========= TOMCAT Certificate change ==========
crt_tomcat() {
  TOMCAT_CERT_DIR="$TOOLS_DIR/tomcat_config/cert"

  # Find a .p12 file in the directory
  p12_file=$(find "$TOMCAT_CERT_DIR" -maxdepth 1 -name '*.p12' | head -n 1)

  if [[ -f "$p12_file" ]]; then
    echo "🔧 Updating Tomcat certificate..."
    
    # Backup the old one if it exists
    if [[ -f "$CATALINA_HOME/conf/localhost.p12" ]]; then
	rm -rf "$CATALINA_HOME/conf/localhost.p12_old"
      mv "$CATALINA_HOME/conf/localhost.p12" "$CATALINA_HOME/conf/localhost.p12_old"
    fi

    # Copy new .p12 to expected location
    cp "$p12_file" "$CATALINA_HOME/conf/localhost.p12"
    echo "✅ Certificate updated: $(basename "$p12_file")"
  else
    echo "❌ No .p12 certificate file found in $TOMCAT_CERT_DIR"
  fi
}

# ========= MENU ==========
show_menu() {
  echo "|--------------------------------------------------------------------------------------|"
  echo -e "| ORDS Installer - Bash Menu Script \e[92mversion 1.0\e[0m                                        |"
  echo -e "| \e[92mJava 21 | Tomcat $tomcat_version | ORDS $ords_version\e[0m                                      |"
  echo "| Script will isntall ORDS based on the TNS name added in the tnsnames.ora             |"
  echo "| 1. Will get all componets isntalled and prepared for ORDS instalaltion               |"
  echo "| 2. Will install ORDS with TNS at the [A] disable the standalone install              |"
  echo "| ORDS DB User is dedicated per DB - create rest DB user and pool DB name interactivly |"
  echo "| 3. Will install the ords.war in tomcat - available on port HTTP:8080/HTTPS:8443       |"
  echo "|--------------------------------------------------------------------------------------|"
  echo
  echo "1. ⚙ Prepare and unpack all components - ORDS JRE Tomcat Oracle Client"
  echo "2. 🚀 Install ORDS interactively"
  echo "3. 📥 Deploy ORDS WAR to Tomcat"
  echo
  echo "4. ✅ Start Tomcat"
  echo "5. ❌ Stop Tomcat"
  echo
  echo "u. ❌ Uninstall ORDS from the Database"
  echo "o. 🔄 ORDS Update to $ords_latest"
  echo "t. 🔄 Tomcat Update to $tomcat_latest_version"
  echo "j. 🔄 Java Update to latest Java 21 $jre_version_latest"
  echo "r. 🛠️ Repair ORDS interactively"
  echo "c. 📜 Update SSL Certificate in p12 format"
  echo "l. 📜️ List ORDS Database Pools Installed"
  echo "i. 📜️ List ORDS REST Clients Installed"
  echo "e. 📜 Show environment configuration"
  echo
  echo "s. 📄 Run Database Scripts (optional)"
  echo
  echo "🎯 Press Enter to Exit"
  echo "-------------------------------"
  read -p "Choose an option [1-5/t,j,r,e,o,u,l,s,c,Enter]: " choice

  case $choice in
    1) stop_tomcat
	   unpack_all ;;
    2) install_ords ;;
    3) stop_tomcat
	   deploy_ords_to_tomcat 
	   start_tomcat ;;
    4) start_tomcat ;;
    5) stop_tomcat ;;
	u|U) stop_tomcat
	     uninstall_ords
         start_tomcat ;;	
	o|O) read -p "Are you sure you want to update ORDS to $ords_latest ❓ - Enter to continue..." 
	     stop_tomcat
		 download_ords_update	
         update_ords
		 deploy_ords_to_tomcat
         start_tomcat 
		 clean_all ;;
	t|T) read -p "Are you sure you want to update Tomcat to $tomcat_latest_version ❓ - Enter to continue..." 
	     stop_tomcat
		 download_tomcat_update	
         deploy_ords_to_tomcat
		 crt_tomcat
         start_tomcat
         clean_all ;;
	j|J) read -p "Are you sure you want to update Java to latest version $jre_version_latest ❓ - Enter to continue..."
	     stop_tomcat
		 download_jre_update	
         start_tomcat
         clean_all ;;
	r|R) stop_tomcat
	     repair_ords
         start_tomcat ;;
    c|C) stop_tomcat
	     crt_tomcat
         start_tomcat ;;
    s|S) run_db_and_rest_scripts ;;
    l|L) list_dbpools ;;
    i|I) list_ords_clients ;;
    e|E) show_env ;;
    "") echo "❌ Exiting..." && exit 0 ;;
    *)
      echo "❓ Invalid option!"
	  sleep 1
      ;;
  esac
}

# ========= LOOP MENU ==========
while true; do
  clear
  show_menu
  read -p "Press [Enter] to continue..." dummy
done