#!/bin/bash

GLOBAL_ERRORS="0"

if [ -z "$1" ];then
   echo "USAGE: $0 <mysql-options>

Examples: 
 $0 --user=user_name --password=your_password db_name
 MYSQL=/foo/bar/baz/bin/mysql RESPONSE=yestmaster $0 -user=zabbix18 --password=54321 zabbix18
"
fi

echo "WARNING: backup your database before performing upgrade

 This is an UNSUPPORTED Zabbix upgrade script from 1.8 to 2.0 for MySQL
 It does the following things:
  1. Updates indexes that might require changes;
  2. Patches the database from 1.8 schema to 2.0 schema;
  3. Adds 'Disabled' and 'Debug' usergroup if any missing;
  4. Checks for hosts not belonging to any group and adds them to one if any found.

 Usage: pass required MySQL parameters to this script (like database, user, password etc).

 Environment variables:
 - Set environment variable RESPONSE to skip confirmation.
 - Set environment variable MYSQL to point to a custom 'mysql' binary
"

if [ -z "$1" ];then
   exit 1
fi 

if [ -z "$RESPONSE" ];then
   read -n 1 -p "Continue ? (y/n) " RESPONSE

   [ "$RESPONSE" == "y" ] || {
      echo
      exit
   }
fi

# groupname to use for hosts that do not belong to any group, if any. literar string.
# if missing, will be added, if existing, hosts will be added to it
GROUPFORHOSTS="none"

MYSQL="${MYSQL:-$(which mysql)}"
MYSQLPARAMS="$@"
echo

######################################################################################################################################
#### HELPERS

do_sql(){
   local SQL="$1"

   if [ -z "$SQL" ];then
      $MYSQL $MYSQLPARAMS
      RET="$?"
   else
      $MYSQL -e "${SQL}" $MYSQLPARAMS -N
      RET="$?"
   fi

   if [ "$RET" != "0" ];then
      GLOBAL_ERRORS="$(( $GLOBAL_ERRORS + 1 ))"
      echo "ERROR-${RET}: something went wrong while executing the following sql-statement: '$SQL' with options '$MYSQLPARAMS'" >&2
      return 1
   else
      return 0
   fi

}

fail() {
	echo "$1"
	exit 1
}

[[ "$MYSQL" ]] || fail "No mysql binary in path."

timer() {
	TIMER=$(echo "$@" | cut -d" " -f 2- | tr " " _)
	case "$1" in
	start)
		let START_$TIMER=$(date +%s)
		;;
	stop)
		let TOTALTIME=$(date +%s)-START_$TIMER
		let TOTALHOURS=TOTALTIME/3600
		let TOTALMINUTES=(TOTALTIME-TOTALHOURS*3600)/60
		let TOTALSECONDS=TOTALTIME%60
	echo "$(echo $TIMER | tr _ " ") took $TOTALHOURS:$(printf "%02d" $TOTALMINUTES):$(printf "%02d" $TOTALSECONDS)"
	esac
}


increaseid() {
# accepts table name and field as parameters
# increases corresponding id if found, searches the table for max id and inserts one if not
# returns freshly inserted id to be used with new entry

	CURRENTID=$(do_sql "select nextid from ids where table_name='$1' and field_name='$2' and nodeid='$3';")

   if (! [[ "$CURRENTID" =~ ^[\-0-9]+$ ]] );then
      echo "query 'select nextid from ids where table_name='$1' and field_name='$2' and nodeid='$3';' does not result a single integer" >&2
      GLOBAL_ERRORS="$(( $GLOBAL_ERRORS + 1 ))"
      return 1
   fi

   CURRENTID_NEW="$(( $CURRENTID + 1 ))"

   if [[ "$CURRENTID" ]];then
		do_sql "update ids set nextid='${CURRENTID_NEW}' where table_name='$1' and field_name='$2' and nodeid='$3';"
      RET="$?"
   else
		CURRENTID=$(do_sql "select $2 from $1 order by $2 desc limit 1;")
      CURRENTID_NEW="$(( $CURRENTID + 1 ))"
		do_sql "insert into ids values (0,'$1','$2',${CURRENTID_NEW});" 
      RET="$?"
   fi
	echo -n "${CURRENTID_NEW}"
   return $RET
}


drop_index() {
	do_sql "alter table $1 drop index $2;" 2>&1 | grep -v "check that column/key exists"
}

create_index() {
	# 1- index name
	# 2- table
	# 3- columns
	do_sql "create index $1 on $2 ($3);" 2>&1 | grep -v "Duplicate key name"
}


######################################################################################################################################
### MAIN

NODE_IDS="$(do_sql 'select nodeid from nodes;'|tr '\n' ' ')"

# ********************   1

#echo "Dropping indexes that might need re-creation..."

#timer start dropping of indexes
#for i in\
# "node_cksum_1 node_cksum_cksum_1"; do
#	drop_index $i
#done
#echo -n " ... "
#timer stop dropping of indexes

#echo "Creating possibly missing indexes..."

#timer start creating of indexes
# create_index index_name table_name columns
#timer stop creating of indexes

# ********************   2

echo "** Patching the database"

timer start patching of the database
do_sql < patch.sql || fail "Failed to patch Zabbix database. Restore from backup"

echo -n " ... "
timer stop patching of the database


# ********************   3
echo "** Adding groups"
for NODE_ID in $NODE_IDS;
do
echo "=> NODE $NODE_ID"
NODE_RANGE="and usrgrp.usrgrpid >= $(( $NODE_ID * 100000000000000 )) and usrgrp.usrgrpid < $(( ( $NODE_ID + 1 ) * 100000000000000 ))"

 	for group in "Disabled,1,0" "Debug,0,1"; do
 		groupname=$(echo $group | cut -d, -f1)
      users_status=$(echo $group | cut -d, -f2)
      debug_mode=$(echo $group | cut -d, -f3)

 		column=$(echo $group | cut -d, -f2)
 		echo -n "Checking for '$groupname' user group... "

      if [ -n "$(do_sql "select name from usrgrp where name='${groupname}' ${NODE_RANGE};")" ];then
 			echo "group '${groupname}' found, not doing anything."
      else
 			echo "group '${groupname}' not found, adding to node $NODE_ID."
 			usrgrouptoadd="$(increaseid usrgrp usrgrpid $NODE_ID)"
         if [ "$?" == "0" ];then
 			  do_sql "insert into usrgrp (usrgrpid,name,gui_access,users_status,debug_mode) values ('$usrgrouptoadd','$groupname','0','$users_status','$debug_mode');" 
         else
           echo "skippping, get no id - it seems that this is not the current node"
           GLOBAL_ERRORS="$(( $GLOBAL_ERRORS + 1 ))"
         fi
 		fi
 	done

done

# ********************   4
echo "** Checking for hosts not belonging to any group... "
timer start checking for hosts not belonging to any group

for NODE_ID in $NODE_IDS;
do
echo "=> NODE $NODE_ID"
NODE_RANGE="and hosts.hostid >= $(( $NODE_ID * 100000000000000 )) and  hosts.hostid < $(( ( $NODE_ID + 1 ) * 100000000000000 ))"
HOSTSWOGROUP="$(do_sql "select hosts.hostid from hosts left join hosts_groups on hosts.hostid=hosts_groups.hostid where hosts_groups.hostid is null $NODE_RANGE;")"

if [ -z "$HOSTSWOGROUP" ];then
	echo "found."
	GROUPFORHOSTSID=$(do_sql "select groupid from groups where name='$GROUPFORHOSTS'")

   if [ -z "$GROUPFORHOSTSID" ];then
		echo "Group '$GROUPFORHOSTS' already exists, adding all orphaned hosts to it."
	else
		echo "Group '$GROUPFORHOSTS' does not exist, creating it and adding all orphaned hosts to it."
		GROUPFORHOSTSID=$(increaseid groups groupid $NODE_ID)

      if [ "$?" == "0" ];then
		   do_sql "insert into groups (groupid,name,internal) values ('$GROUPFORHOSTSID','$GROUPFORHOSTS','0');" 
      else
        echo "skippping, get no id - it seems that this is not the current node"
        GLOBAL_ERRORS="$(( $GLOBAL_ERRORS + 1 ))"
      fi
	fi
	for ORPHANHOST in $HOSTSWOGROUP; do
      echo "orphaned host $ORPHANHOST"
		MAPPINGID=$(increaseid hosts_groups hostgroupid $NODE_ID)
      if [ "$?" == "0" ];then
		   do_sql "insert into hosts_groups (hostgroupid,hostid,groupid) values ('$MAPPINGID','$ORPHANHOST','$GROUPFORHOSTSID');" 
	   	((HOSTSADDED++))
      else
        echo "skippping, get no id - it seems that this is not the current node"
        GLOBAL_ERRORS="$(( $GLOBAL_ERRORS + 1 ))"
      fi
	done
	echo "Added $HOSTSADDED hosts to group '$GROUPFORHOSTS'. Move them to correct groups manually."
else
	echo "not found any hosts"
fi
done
echo -n " ... "
timer stop checking for hosts not belonging to any group

if [ "$GLOBAL_ERRORS" -gt 0 ];then
   echo "ERROR: $GLOBAL_ERRORS problems occurred, migration procedure failed"
   exit 1
else
   echo "SUCCESS: $GLOBAL_ERRORS problems occurred, migration procedure was successful"
   exit 0 
fi