#! /bin/bash

#variable used to repeat the script
endscript=false


echo ""
printf "
********************************************************************************************************************
********************************************************************************************************************
This script will run a database report.csv with a user provide script. The report.csv will be clean and formated
and a link will be provided to download the report.csv. The report.csv will then be erased.
********************************************************************************************************************"


echo ""
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 "No tomcat dirs exist / Something went wrong"
        exit 1
fi

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

#DB Credentials
DBHost="$(cat $path/server.xml | grep url= | sed -e 's/url\=\"jdbc\:mysql\:\/\///g' | sed -e 's/\/terminalfour.*//g' | tr -d '[:space:]')"
DBUser="$(cat $path/server.xml | grep username= | sed -e 's/username\=\"//g' | sed -e 's/\"//g' | tr -d '[:space:]')"
DBPass="$(cat $path/server.xml | grep password= | sed -e 's/password\=\"//g' | sed -e 's/\"//g' | tr -d '[:space:]')"

# loop to run a report
    while [ $endscript = false ]; do


echo ""
echo "Enter in your query (it can span multiple lines; end it with a semicolon ;):"

# Read lines until one ends with a semicolon, so multi-line queries can be pasted in
query=""
while IFS= read -r line; do
    query+="$line"$'\n'
    if [[ "$line" =~ \;[[:space:]]*$ ]]; then
        break
    fi
done

echo "$query" > /home/john.sims/query.sql


echo ""
read -p "Save report as: " report


echo "Running the report..."
mysql -h"$DBHost" -u"$DBUser" -p"$DBPass" --ssl terminalfour < /home/john.sims/query.sql | tr '\t' '#' > /home/john.sims/$report.csv

sed -i 's/,/-/g'  /home/john.sims/$report.csv

sed -i 's/#/,/g'  /home/john.sims/$report.csv

echo ""
echo "Report Completed."
echo ""
echo "File saved as: /home/john.sims/$report.csv"

#remove old query file
rm -rf /home/john.sims/query.sql

name=$HOSTNAME.terminalfour.net


cp /home/john.sims/$report.csv /web/terminalfour/htdocs/

echo ""
echo ""

echo "************************************************************************************************************************"
echo "$report.csv can be downloaded at: $name/$report.csv"
echo "************************************************************************************************************************"

echo ""
echo ""

read -p  "Have you downloaded the file. If yes , the file will now be deleted. (yes/no): " answer




if [ "$answer" = "yes" ]; then
        rm -rf /web/terminalfour/htdocs/$report.csv
        echo "Successfully removed /web/terminalfour/htdocs/$report.csv"
        echo ""
        echo ""
fi


validinput=false
# loop
    while [ $validinput = false ]; do

read -p  "Run another report? (yes/no): " repeat
# condition used to run another report
if [ "$repeat" = "yes" ]; then
        endscript=false
        validinput=true
elif [ "$repeat" = "no" ]; then
        endscript=true
        validinput=true
echo ""
echo "Ending Script."

else
    echo "Invalid input. Please enter 'yes' or 'no'"
fi
        done

done