#!/bin/bash


# MySQL helper script by 3PO
# Update 19.03.2017


PASSWORD=""
HOST=""
MY_CNF="/etc/mysql/my.cnf"
MY_DB_SV="mysqld"
TMPFILE="/tmp/.epgtool"
EPGD_CONF="/etc/epgd/epgd.conf"


DISTRI="$(grep "^NAME=" /etc/os-release |cut -d "=" -f2 | sed -e "s/\"//g")"
RELEASE="$(grep "^VERSION_ID=" /etc/os-release |cut -d "=" -f2 | sed -e "s/\"//g")"

if [ "$DISTRI" == Fedora ] ; then
  MY_CNF="/etc/my.cnf"
  if [ "$RELEASE" -gt 21 ] ; then
    MY_DB_SV="mariadb"
  fi
fi 

[ "$HOST" != "" ] && HOST="-h $HOST"

rc_start ()
{
  MYSQL_START="/etc/init.d/mysql start"
  MYSQL_RESTART="/etc/init.d/mysql restart"
  EPGD_START="/etc/init.d/epgd start"
  EPGD_STOP="/etc/init.d/epgd stop"
  EPGD_RESTART="/etc/init.d/epgd restart"
}

systemd_start ()
{
  MYSQL_START="systemctl start "$MY_DB_SV""
  MYSQL_RESTART="systemctl restart "$MY_DB_SV""
  EPGD_START="systemctl epgd start"
  EPGD_STOP="systemctl epgd stop"
  EPGD_RESTART="systemctl epgd restart"
}

upstart_start ()
{
  MYSQL_START="/sbin/start mysql"
  MYSQL_RESTART="/sbin/restart mysql"
  EPGD_START="/sbin/start epgd"
  EPGD_STOP="/sbin/stop epgd"
  EPGD_RESTART="/sbin/restart epgd"
}

chkRootPwd ()
{
[ -f ~/.ssh/mysqlpasswd ] && . ~/.ssh/mysqlpasswd 
if [ ! $PASSWORD ] ; then
  echo "root Password for MySQL Database is not set!"
  echo "Exit"
  exit
else
  . ~/.ssh/mysqlpasswd
  SQL_ROOT="mysql -u root -e "
fi

if [ "$PASSWORD" != "" ] ; then
  export MYSQL_PWD="$PASSWORD"
  SQL_ROOT="mysql -u root -e "
fi
}

if [ "$(ps -p 1 -o comm=)" == 'systemd' ] ; then
   systemd_start
 elif [ -d /usr/lib/upstart ] ; then
   upstart_start
elif which rc > /dev/null 2>&1 ; then
   rc_start
 fi

PID_DB="$(pidof mysqld | wc -l)"
PID_EPGD="$(pidof epgd | wc -l)"
[ "$(pidof epgd)" != "" ] && EPGD_RUN="1"
[ "$(pidof mysqld)" != "" ] && MYSQL_RUN="1"
SQL_EPG2VDR="mysql $HOST -u epg2vdr -D epg2vdr -e "

EPGD_EXIST="$(which epgd |wc -l)"
[ "$EPGD_EXIST" -eq 0 ] && PID_EPGD="0" && EPGD_RUN="0"



DEL_DB ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
chkRootPwd
$SQL_ROOT "DROP DATABASE epg2vdr;"
}

DEL_U ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
chkRootPwd 
$SQL_ROOT "DROP USER epg2vdr;"
} 

DROP_PICS ()
{
export MYSQL_PWD=epg 
$SQL_EPG2VDR "DROP TABLE images;"
}

DEL_ALL ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
chkRootPwd 
$SQL_ROOT "DROP DATABASE epg2vdr;"
$SQL_ROOT "DROP USER epg2vdr;"
}

DROPVIEW ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP
export MYSQL_PWD=epg 
$SQL_EPG2VDR "DROP VIEW eventsview;"
[ "$EPGD_RUN" == 1 ] && $EPGD_START
}
   
DROP_ALL ()
{
[ "$PID_DB" -lt 1 ] && $MYSQL_START
[ "$PID_EPGD" -ne 0 ] && $EPGD_STOP

export MYSQL_PWD=epg 
$SQL_EPG2VDR "SHOW TABLES" |grep -v images > $TMPFILE

while read TABLE; do
    $SQL_EPG2VDR "DROP TABLE IF EXISTS $TABLE;"
done < "$TMPFILE"

rm -f $TMPFILE

[ "$EPGD_RUN" == 1 ] && $EPGD_START
CASE="DROP_PICS"
echo -e "\nDelete all EPG Pictures from epg2vdr Database?\n"
YES_NO
}

YES_NO ()
{
LOOP=0 
while [ $LOOP -eq 0 ] 
do 
  echo -en 'Are You sure? [yes/no]: ' 
  read CHOICE 
  echo -en "\n" 
 case $CHOICE in 

   [yY][eE][sS]|[yY])  
   $CASE 
   LOOP=1 ;; 
  
   [nN][oO]|[nN])
   echo -e "\nScript aborted by User\n" 
   exit  ;; 
 
   *) echo "Please enter \"yes\" or \"no\"." 
   LOOP=0;; 
 
 esac 
done 
}


case $1 in

   -new-db|new-db)
   [ "$PID_DB" -lt 1 ] && $MYSQL_START 
   chkRootPwd 
   $SQL_ROOT "CREATE DATABASE epg2vdr charset utf8;"
   $SQL_ROOT "DROP FUNCTION IF EXISTS epglv;"
   $SQL_ROOT "DROP FUNCTION IF EXISTS epglvr;"
   $SQL_ROOT "CREATE FUNCTION epglv RETURNS INT SONAME 'mysqlepglv.so';"
   $SQL_ROOT "CREATE FUNCTION epglvr RETURNS INT SONAME 'mysqlepglv.so';"
   exit
   ;;

   -new-u|new-u)
   [ "$PID_DB" -lt 1 ] && $MYSQL_START 
   chkRootPwd
   $SQL_ROOT "CREATE USER 'epg2vdr'@'%' IDENTIFIED BY 'epg';"
   $SQL_ROOT "GRANT ALL PRIVILEGES ON epg2vdr.* TO 'epg2vdr'@'%' IDENTIFIED BY 'epg';"
   $SQL_ROOT "GRANT ALL PRIVILEGES ON epg2vdr.* TO 'epg2vdr'@'localhost' IDENTIFIED BY 'epg';"
   $SQL_ROOT "FLUSH PRIVILEGES;"
   exit
   ;;

   -del-db|del-db)
   echo -e "\nDelete the complete epg2vdr Database?\n"
   CASE="DEL_DB"
   YES_NO
   ;;

   -del-u|del-u)
   echo -e "\nDelete the User epg2vdr?\n"
   CASE="DEL_U"
   YES_NO
   ;; 

   -del-all|del-all)
   echo -e "\nDelete the complete epg2vdr Database and User epg2vdr?\n"
   CASE="DEL_ALL"
   YES_NO
   ;;

   -show|show)
   [ "$PID_DB" -lt 1 ] && $MYSQL_START
   chkRootPwd
   $SQL_ROOT "SELECT User,Host,Db FROM mysql.db;"
   $SQL_ROOT "SHOW GRANTS FOR epg2vdr;"
   $SQL_ROOT "SHOW DATABASES;"
   export MYSQL_PWD=epg 
   $SQL_EPG2VDR "SHOW TABLES;"
   exit
   ;;

   -fix-cnf|fix-cnf)
   if [ ! -f $MY_CNF ] ; then 
     echo "$MY_CNF does not exist"
     echo "Exit"
     exit
   else
     chkRootPwd
     $SQL_ROOT "Flush LOGS;"
     $SQL_ROOT "RESET MASTER;"
     sed -i $MY_CNF -e "s/^bind-address/# bind-address/"
     sed -i $MY_CNF -e "s/^log-bin/# log-bin/"
     [ "$MYSQL_RUN" == "1" ] && $MYSQL_RESTART
     [ "$EPGD_RUN" == "1" ] && $EPGD_RESTART 
   fi
   exit
   ;;
   
   -dropview|dropview)
   echo -e "\nDrop all Views form database?\n"
   CASE="DROPVIEW"
   YES_NO
   ;;
   
   -drop-all|drop-all)
   echo -e "\nClean the complete epg2vdr Database?\n"
   CASE="DROP_ALL"
   YES_NO
   ;;

   -check-events|check-events)
   export MYSQL_PWD=epg 
   $SQL_EPG2VDR "SELECT count(1) AS Events, source FROM events group by source;"
   exit
   ;;

   -check-pic|check-pic)
   export MYSQL_PWD=epg 
   $SQL_EPG2VDR "SELECT count(1) AS Pictures FROM images WHERE image IS NOT NULL;" 
   exit
   ;;
   
   -show-stats|show-stats)
   export MYSQL_PWD=epg 
   $SQL_EPG2VDR "SELECT version, master, ip, state, FROM_UNIXTIME(updsp,'%D %M %Y% %H:%i:%s') AS 'last touch', FROM_UNIXTIME(lastupd, '%D %M %Y% %H:%i:%s') AS 'last download', FROM_UNIXTIME(nextupd,'%D %M %Y% %H:%i:%s') AS 'next download' FROM vdrs ;"
   exit
   ;;

   -show-size|show-size)
   export MYSQL_PWD=epg 
   $SQL_EPG2VDR "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size in MB' FROM information_schema.TABLES  GROUP BY table_schema;"
   echo ""
   $SQL_EPG2VDR "SELECT table_name AS 'Tables', ROUND(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB' FROM information_schema.TABLES WHERE table_schema = \"epg2vdr\" ORDER BY (data_length + index_length) DESC;"
   exit
   ;;

   -drop-pics|drop-pics)
   echo -e "\nDelete all EPG Pictures from epg2vdr Database?\n"
   CASE="DROP_PICS"
   YES_NO
   ;;

   -apikey|apikey)
   if [ ! -f "$EPGD_CONF" ] ; then
     echo -e "\n $EPGD_CONF -> File not found!\n"
     exit
   fi
   APIKEY="$(grep ScrapMovieDbApiKey "$EPGD_CONF" |sed -e 's/ //g' |cut -d "=" -f2)"
   TMDBAPIURL="https://api.themoviedb.org/3/movie/550?"

   if [ "$(curl -s "$TMDBAPIURL"api_key="$APIKEY" | grep '\"status_code\":7')" ] ; then
     echo -e "\n Invalid API key: You must be granted a valid key\n"
   else
     echo -e "\n Your API key is valid: -> '$APIKEY'\n"
   fi
   ;;


   *)
   echo ""
   echo " MySQL helper script by 3PO"
   echo ""
   echo " usage: [-new-db] [-new-u] [-del-db] [-del-u] [ -del-all] [-show] [-fix-cnf] [-dropview] [-drop-all] [-drop-pics] [-check-events] [-check-pic] [-show-stats] [-show-size] [-apikey]"
   echo ""
   echo "	-new-db         Create new Database for epg2vdr" 
   echo "	-new-u          Create new User for epg2vdr" 
   echo "	-del-db         Delete epg2vdr Database"
   echo "	-del-u          Delete User epg2vdr"
   echo "	-del-all        Delete epg2vdr Database and User epg2vdr"
   echo "	-show           Shows GRANTS for epg2vdr and list Databases and Users"
   echo "	-fix-cnf        Disable \"bind-address\" in $MY_CNF"
   echo "	-dropview       Drop Table \"eventsview\" in Database"
   echo "	-drop-all       Drop all Tables in epg2vdr Database"
   echo "	-drop-pics      Delete all Pictures from epg2vdr Database"
   echo "	-check-events   Shows quantity of Events in Database imported per source"
   echo "	-check-pic      Shows quantity of EPG Pictures stored in Database"
   echo "	-show-stats     Shows the Stats and Versions of EPGd and the connected VDRs"
   echo "	-show-size      Shows the Size of the Tables in the epg2vdr Database"
   echo "	-apikey         Checks the validity of the API key for https://www.themoviedb.org"
   echo ""
   exit
   ;;

esac