#!/bin/bash 

upgradeDataBase() {
        COUNTER=1
        echo "Upgrading $1 database"
        
        psql -U postgres -h 127.0.0.1 -d $1 -f /etc/coraltele/database/dropfunction.sql
        psql -U postgres -h 127.0.0.1 -d $1 -c "select * from dropdatabaseobjects()"
        
        if [ $1 = "switch" ]; then
        	echo "Updating commands"
        	
			psql -U postgres -h 127.0.0.1 -d switch -c "drop table pbx.m_commands cascade"
		        psql -U postgres -h 127.0.0.1 -d switch -c "drop table pbx.m_rsucommands cascade"	
		        psql -U postgres -h 127.0.0.1 -d switch -c "drop table pbx.m_gatewaysiptrunk cascade"	
		        psql -U postgres -h 127.0.0.1 -d switch -c "drop table pbx.codemaster cascade"	
		        psql -U postgres -h 127.0.0.1 -d switch -c "drop table pbx.m_actioncodelist cascade"	
		        psql -U postgres -h 127.0.0.1 -d switch -c "drop table version cascade"	
		        psql -U postgres -h 127.0.0.1 -d switch -c "drop table pbx.m_modeldetail cascade"	
		        psql -U postgres -h 127.0.0.1 -d switch -c "drop view v_oc_activecalls cascade"	
        	if [ $psqlVersion == "12.5" ]; then
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_commands.data
                                pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_rsucommands.data
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_gatewaysiptrunk.data
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.codemaster.data
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_actioncodelist.data
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.version.data
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_modeldetail.data
			else
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_commands.data.v8
                                pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_rsucommands.data.v8
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_gatewaysiptrunk.data.v8
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.codemaster.data.v8
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_actioncodelist.data.v8
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.version.data.v8
				pg_restore -U postgres -h 127.0.0.1 -d switch /etc/coraltele/database/switch.pbx.m_modeldetail.data.v8
			fi
			        	
    	fi


        if [ $1 = "coralapps" ]; then
        	echo "Updating Data"
        	
		        psql -U postgres -h 127.0.0.1 -d coralapps -c "drop table callcenter.m_customfieldsmaster cascade"	
        	if [ $psqlVersion == "12.5" ]; then
				pg_restore -U postgres -h 127.0.0.1 -d coralapps /etc/coraltele/database/coralapps.callcenter.m_customfieldsmaster.data
			else
				pg_restore -U postgres -h 127.0.0.1 -d coralapps /etc/coraltele/database/coralapps.callcenter.m_customfieldsmaster.data.v8
			fi
			        	
    	fi
        
        if [ $2 = 1 ]; then
        	psql -U postgres -h 127.0.0.1 -d $1 -f /etc/coraltele/database/$3.sql
    	fi
        
        while [  $COUNTER -lt 11 ]; do
	        echo "$1 : Iteration no $COUNTER"
    	    pg_dump -s -O -U postgres -d $1 -h 127.0.0.1 > /tmp/$1.schema
        	upgradeFile="/tmp/$1.upgrade.sql"
        	rm $upgradeFile
        	
        	if [ $psqlVersion == "12.5" ]; then
	        	java -jar /etc/coraltele/apgdiff-2.4.jar /tmp/$1.schema /etc/coraltele/database/$1.schema > $upgradeFile
	    	else
	        	java -jar /etc/coraltele/apgdiff-2.4.jar /tmp/$1.schema /etc/coraltele/database/$1.schema.v8 > $upgradeFile	    		
        	fi
        	
	        if [ -s "$upgradeFile" ]; then
                psql -U postgres -h 127.0.0.1 -d $1 -f $upgradeFile > /tmp/upgradeoutput
    	    else
                echo "$1 : Upgraded"
                break
        	fi
            COUNTER=$(expr $COUNTER + 1)
        done
        
        if [ $COUNTER = 11 ]; then
        	echo "*********************************************************************************************"
        	echo "!!!!! Unable to upgrade $1 , please send $upgradeFile !!!!!"
        	echo "*********************************************************************************************"
        	sleep 5
    	fi
}

upgradeRamDatabase(){
  count=1
    
  echo "Updating $1 database at Ip: $ipAddr and Port: $4"
        psql -U postgres -h $ipAddr -p $4 -d $1 -f /etc/coraltele/database/dropfunction.sql
        psql -U postgres -h $ipAddr -p $4 -d $1 -c "select * from dropdatabaseobjects()"

       if [ $1 = "switch" ]; then
               echo "Updating commands"

                        psql -U postgres -h $ipAddr -p $4 -d switch -c "drop table pbx.m_commands cascade"
                        psql -U postgres -h $ipAddr -p $4 -d switch -c "drop table pbx.m_rsucommands cascade"
                        psql -U postgres -h $ipAddr -p $4 -d switch -c "drop table pbx.m_gatewaysiptrunk cascade"
                        psql -U postgres -h $ipAddr -p $4 -d switch -c "drop table pbx.codemaster cascade"
                        psql -U postgres -h $ipAddr -p $4 -d switch -c "drop table pbx.m_actioncodelist cascade"
                        psql -U postgres -h $ipAddr -p $4 -d switch -c "drop table version cascade"
            if [ $psqlVersion == "12.5" ]; then
                         pg_restore -U postgres -h $ipAddr -p $4 -d switch /etc/coraltele/database/switch.pbx.m_commands.data
                         pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.pbx.m_rsucommands.data
                         pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.pbx.m_gatewaysiptrunk.data
                         pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.pbx.codemaster.data
                         pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.pbx.m_actioncodelist.data
                         pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.version.data
                  else
                         pg_restore -U postgres -h $ipAddr -p $4 -d switch /etc/coraltele/database/switch.pbx.m_commands.data.v8
                         pg_restore -U postgres -h $ipAddr -p $4 -d switch /etc/coraltele/database/switch.pbx.m_rsucommands.data.v8
			 pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.pbx.m_gatewaysiptrunk.data.v8
			 pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.pbx.codemaster.data.v8
			 pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.pbx.m_actioncodelist.data.v8
			 pg_restore -U postgres -h $ipAddr-p $4 -d switch /etc/coraltele/database/switch.version.data.v8
                        fi
       fi
       
       if [ $1 = "coralapps" ]; then
               echo "Updating Data"

                        psql -U postgres -h $ipAddr -p $4 -d coralapps -c "drop table callcenter.m_customfieldsmaster cascade"
            if [ $psqlVersion == "12.5" ]; then
                         pg_restore -U postgres -h $ipAddr-p $4 -d coralapps /etc/coraltele/database/coralapps.callcenter.m_customfieldsmaster.data
                  else
			 pg_restore -U postgres -h $ipAddr-p $4 -d coralapps /etc/coraltele/database/coralapps.callcenter.m_customfieldsmaster.data.v8
                        fi
       fi

  if [ $2 = 1 ]; then
                psql -U postgres -h $ipAddr -p $4 -d $1 -f /etc/coraltele/database/$3.sql
        fi

  while [  $count -lt 11 ]; do
    echo "$1 : Iteration no $count at Ip: $ipAddr and Port: $4"
    pg_dump -s -O -U postgres -d $1 -h $ipAddr -p $4 > /tmp/$1.RamDb.schema
    upgradeFilePath="/tmp/$1.RamDbUpgrade.sql"
    rm $upgradeFilePath

          if [ $psqlVersion == "12.5" ]; then
                 java -jar /etc/coraltele/apgdiff-2.4.jar /tmp/$1.RamDb.schema /etc/coraltele/database/$1.schema > $upgradeFilePath
           else
                 java -jar /etc/coraltele/apgdiff-2.4.jar /tmp/$1.RamDb.schema /etc/coraltele/database/$1.schema.v8 > $upgradeFilePath   
          fi

      if [ -s "$upgradeFilePath" ]; then
              psql -U postgres -h $ipAddr -p $4 -d $1 -f $upgradeFilePath > /tmp/RamDbUpgradeoutput
       else
              echo "$1 : Upgraded at Ip: $ipAddr and Port: $4"
              break
              fi
   count=$(expr $count + 1)
        done

  if [ $count = 11 ]; then
                echo "*********************************************************************************************"
                echo "!!!!! Unable to upgrade $1 at Ip: $ipAddr and Port: $4, please send $upgradeFile !!!!!"
                echo "*********************************************************************************************"
                sleep 5
        fi


}

callForRamDb(){
port=( $(pg_lsclusters | awk '{print $3}'|sed -n '1!p') )

for i in "${port[@]}"
do
    if [[ $i != "5432" ]]
     then
          
          ipAddr=$(netstat -plunt |grep $i |awk '{print $4}'| sed 's/:.*//')

          if [ -z "$ipAddr" ] || [ "$ipAddr" = "127.0.0.1" ]
             then
                   echo "Postgres Not running at proper Ip Addres"

               else
                   dbConnect=$(psql -h 127.0.0.1 -U postgres -c "select 1" | grep ?column? | wc -l)
                   psqlVersion=$(dpkg-query -l | grep postgresql-client-12 | awk '{print $3}' | sed 's/-.*//')

                   if [ $dbConnect -eq 0 ]; then
                           echo `date` "Database not available"
                     else
                           echo `date` "Database available upgrading the schema"
                           upgradeRamDatabase switch 0 "" $i
                           upgradeRamDatabase coralapps 1 "materializedviews" $i
                           upgradeRamDatabase users 0 "" $i
                  fi
           fi
fi
done
}


dbConnect=$(psql -h 127.0.0.1 -U postgres -c "select 1" | grep ?column? | wc -l)
psqlVersion=$(dpkg-query -l | grep postgresql-client-12 | awk '{print $3}' | sed 's/-.*//')

if [ $dbConnect -eq 0 ]; then
    echo `date` "Database not available"
else
    echo `date` "Database available upgrading the schema"
	upgradeDataBase switch 0 ""
	upgradeDataBase coralapps 1 "materializedviews"
	upgradeDataBase users 0 ""
fi
       callForRamDb
