#!/bin/bash


#This script searches for a content ID when it's difficult to determine which content type within the database that the content item belongs to. 
#At times, the content type within the CMS and the database can have different names.

# Color variables
red='\033[0;31m'
green='\033[0;32m'
clear='\033[0m'


printf "
********************************************************************************************
********************************************************************************************

This script searches for a content ID when it's difficult to determine which content type 
within the database that the content item belongs to. At times, the content type within 
the CMS and the database can have different names.    

********************************************************************************************                       
********************************************************************************************"

echo ""
echo ""

# Detect the Tomcat config directory automatically
path="/usr/share"
if [[ -e "$path/tomcat9/conf" ]]
then
        path="$path/tomcat9/conf"
elif [[ -e "$path/tomcat8/conf" ]]
then
        path="$path/tomcat8/conf"
elif [[ -e "$path/tomcat7/conf" ]]
then
        path="$path/tomcat7/conf"
else
        echo -e "${red}No tomcat dirs exist / Something went wrong${clear}"
        exit 1
fi

echo "Using config: $path/server.xml"
echo ""

# MySQL connection information
host="$(cat $path/server.xml | grep url= | sed -e 's/url\=\"jdbc\:mysql\:\/\///g' | sed -e 's/\/terminalfour.*//g' | tr -d '[:space:]')"
db_user="$(cat $path/server.xml | grep username= | sed -e 's/username\=\"//g' | sed -e 's/\"//g' | tr -d '[:space:]')"
db_password="$(cat $path/server.xml | grep password= | sed -e 's/password\=\"//g' | sed -e 's/\"//g' | tr -d '[:space:]')"
db_name="terminalfour"


#The content id of the content item you are attempting to find the content type for. 
read -p "Please enter content item ID number: " -r id

# Output file to save the list of table names
table_list_file="table_list.txt"

# Use MySQL to get a list of table names starting with "t4_"
mysql -u "$db_user" -h"$host" -p"$db_password" --ssl -N -B -e "SHOW TABLES LIKE 't4%';" "$db_name" > "$table_list_file"

# Removes the t4_sub_type table name from the table list. Leaving the table name in this file will generate an error. 
sed -i '/t4_sub_type/d;/^$/d' "$table_list_file"

# Results file to store tables where the record is found
results_file="found.txt"

# Create or clear the results file
> "$results_file"

# Loop through the list of table names
while IFS= read -r table_name
do
  echo "Checking $table_name:"
  # Run a MySQL query to check for the record
  result=$(mysql -u "$db_user" -h"$host" -p"$db_password" --ssl "$db_name" -s -N -e "SELECT COUNT(*) FROM $table_name WHERE id = $id")

  # If the record is found, add the table name to the results file
  if [ "$result" -gt 0 ]; then
    echo "Found in table named: $table_name" >> "$results_file"
  fi
done < "$table_list_file"

echo ""
echo ""
echo "Search Finished."
echo ""
echo ""
# Check if the results file is empty
if [ -s "$results_file" ]; then
    # Display lines found in the file
    echo -e "${green}Results found for Content id $id ${clear}"
    echo ""
    cat "$results_file"
    echo ""

else
    # Display message when no results are found
    echo -e "${red}No results found for Content id $id.${clear}"
    echo ""
fi

# Removes the table list and results file
rm -rf $table_list_file
rm -rf $results_file