#!/bin/bash 

###################################################################

#Script Name	: Aiops database
#Description	: aiopsdb
#Args           : 0-CreateDB, 1-Verbose, 2-UpdateDB
#Author       	: Vaibhav Sharma

###################################################################

workDir=$(dirname $(dirname $(dirname $(realpath $0))))
sysConfigDir=$workDir/config/sysConfig
hostFile=$sysConfigDir/hostRCAMon.conf
CONFIG=$sysConfigDir/hostDetails.conf
backupDir=$workDir/${dbName}_bak
knowledgeBaseDir=$workDir/utility/KnowledgeBase

table_auto_smc=auto_smc
table_manual_smc=manual_smc
table_metricinfo=metricinfo
table_iprcaconfig=iprcaconfig
table_incident=incident
table_incident_col_incidentdata=incidentdata
table_alert=alert
table_alert_col_gTag=gtag
table_incident_col_gTag=gtag
table_alert_col_forceRCA=forcerca
table_incident_col_rcapath=rcapath
table_incident_col_probablegraph=probablegraph
table_incident_col_source=source
table_activeincident=activeincident

path_auto_smc=/home/cavisson/work/aiops/mlAutoRca/utility/KnowledgeBase/db-auto_smc-2021.csv
path_manual_smc=/home/cavisson/work/aiops/mlAutoRca/utility/KnowledgeBase/db-manual_smc-2022.csv
path_metricinfo=/home/cavisson/work/aiops/mlAutoRca/utility/KnowledgeBase/db-metricinfo-2022.csv
path_iprcaconfig=/home/cavisson/work/aiops/mlAutoRca/utility/KnowledgeBase/db-iprcaconfig-2022.csv

STD='\033[0;0;39m'
NONE='\033[00m'
RED='\033[01;31m'
GREEN='\033[01;32m'
YELLOW='\033[01;33m'
PURPLE='\033[01;35m'
CYAN='\033[01;36m'
WHITE='\033[01;37m'
BOLD='\033[1m'
UNDERLINE='\033[4m'

pause(){
	read -ep "Press [Enter] key to continue..." fackEnterKey
}

drop_db(){
	if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
		#echo "$dbName Database Found!..."
		echo -e "${RED}Deleting [$dbName] Database!${NONE}"
		psql -U postgres -c "DROP DATABASE $dbName;"
	else
		echo "[$dbName] Database Not Found !"
	fi
}

update_Table(){
	if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
                echo "[$dbName] Database Found!"
                table_auto_smc_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = '$table_auto_smc');")
                table_manual_smc_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = '$table_manual_smc');")
                table_metricinfo_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = '$table_metricinfo');")
		table_iprcaconfig_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = '$table_iprcaconfig');")
		 table_alert_col_forceRCA_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (SELECT * FROM information_schema.columns WHERE table_name = '$table_alert' and column_name= '$table_alert_col_forceRCA');")
		table_incident_col_incidentdata_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (Select * from information_schema.columns WHERE table_name = '$table_incident' and column_name = '$table_incident_col_incidentdata');")
		table_alert_col_gTag_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (Select * from information_schema.columns WHERE table_name = '$table_alert' and column_name = '$table_alert_col_gTag');")
		table_incident_col_gTag_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (Select * from information_schema.columns WHERE table_name = '$table_incident' and column_name = '$table_incident_col_gTag');")
		table_incident_col_rcapath_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (Select * from information_schema.columns WHERE table_name = '$table_incident' and column_name = '$table_incident_col_rcapath');")
                table_incident_col_probablegraph_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (Select * from information_schema.columns WHERE table_name = '$table_incident' and column_name = '$table_incident_col_probablegraph');")
		table_incident_col_source_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (Select * from information_schema.columns WHERE table_name = '$table_incident' and column_name = '$table_incident_col_source');")
		 table_activeincident_col_source_exists=$(psql -U postgres -d $dbName -tAc "SELECT EXISTS (Select * from information_schema.columns WHERE table_name = '$table_activeincident' and column_name = '$table_incident_col_source');")
                if [ "$table_auto_smc_exists" = "t" ]; then
                        psql -U postgres -d $dbName -c "TRUNCATE TABLE $table_auto_smc;"
                        psql -U postgres -d $dbName -c "COPY $table_auto_smc FROM '$path_auto_smc' DELIMITER ',' CSV HEADER;"
                        echo "COPIED [$table_auto_smc] table to db"
                fi
                if [ "$table_manual_smc_exists" = "t" ]; then
                        psql -U postgres -d $dbName -c "TRUNCATE TABLE $table_manual_smc;"
                        psql -U postgres -d $dbName -c "COPY $table_manual_smc FROM '$path_manual_smc' DELIMITER ',' CSV HEADER;"
                        echo "COPIED [$table_manual_smc] table to db"
                fi
                if [ "$table_metricinfo_exists" = "t" ]; then
                        psql -U postgres -d $dbName -c "TRUNCATE TABLE $table_metricinfo;"
                        psql -U postgres -d $dbName -c "COPY $table_metricinfo FROM '$path_metricinfo' DELIMITER ',' CSV HEADER;"
                        echo "COPIED [$table_metricinfo] table to db"
                fi
		if [ "$table_iprcaconfig_exists" = "t" ]; then
                        psql -U postgres -d $dbName -c "TRUNCATE TABLE $table_iprcaconfig;"
                        psql -U postgres -d $dbName -c "COPY $table_iprcaconfig FROM '$path_iprcaconfig' DELIMITER ',' CSV HEADER;"
                        echo "COPIED [$table_iprcaconfig] table to db"
                fi
		if [ "$table_incident_col_incidentdata_exists" = "f" ]; then
			psql -U postgres -d $dbName -c "ALTER TABLE $table_incident ADD COLUMN $table_incident_col_incidentdata VARCHAR;"
			echo "Added column named [$table_incident_col_incidentdata] in table [$table_incident]"
		fi
		if [ "$table_alert_col_forceRCA_exists" = "f" ]; then
                        psql -U postgres -d $dbName -c "ALTER TABLE $table_alert ADD COLUMN $table_alert_col_forceRCA INT;"
                        echo "Added column named [$table_alert_col_forceRCA] in table [$table_alert]"
                fi
		if [ "$table_alert_col_gTag_exists" = "f" ]; then
                        psql -U postgres -d $dbName -c "ALTER TABLE $table_alert ADD COLUMN $table_alert_col_gTag VARCHAR;"
                        echo "Added column named [$table_alert_col_gTag] in table [$table_alert]"
                fi
		if [ "$table_incident_col_gTag_exists" = "f" ]; then
                        psql -U postgres -d $dbName -c "ALTER TABLE $table_incident ADD COLUMN $table_incident_col_gTag VARCHAR;"
                        echo "Added column named [$table_incident_col_gTag] in table [$table_incident]"
                fi
		if [ "$table_incident_col_rcapath_exists" = "f" ]; then
                        psql -U postgres -d $dbName -c "ALTER TABLE $table_incident ADD COLUMN $table_incident_col_rcapath VARCHAR;"
                        echo "Added column named [$table_incident_col_rcapath] in table [$table_incident]"
                fi
                if [ "$table_incident_col_probablegraph_exists" = "f" ]; then
                        psql -U postgres -d $dbName -c "ALTER TABLE $table_incident ADD COLUMN $table_incident_col_probablegraph VARCHAR;"
                        echo "Added column named [$table_incident_col_probablegraph] in table [$table_incident]"
                fi
		if [ "$table_incident_col_source_exists" = "f" ]; then
                        psql -U postgres -d $dbName -c "ALTER TABLE $table_incident ADD COLUMN $table_incident_col_source VARCHAR;"
                        echo "Added column named [$table_incident_col_source] in table [$table_incident]"
                fi
		if [ "$table_activeincident_col_source_exists" = "f" ]; then                                                                                                              psql -U postgres -d $dbName -c "ALTER TABLE $table_activeincident ADD COLUMN $table_incident_col_source VARCHAR;"
                        echo "Added column named [$table_incident_col_source] in table [$table_activeincident]"
                fi

	fi
}

create_db(){

	if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
		update_Table
	else
		echo -e "${BOLD}Creating [$dbName] Database!${NONE}"
		psql -U postgres << END_OF_DBSCRIPT

		-- Drop the DB
		-- DROP DATABASE $dbName;

		CREATE DATABASE $dbName WITH OWNER = postgres;

		-- GRANT ALL ON DATABASE $dbName TO cavisson;
		\c $dbName

		-- Create tables on the $dbName DB

		DO \$$ BEGIN RAISE INFO 'Table Name [Alert]'; END \$$ ;
create table Alert(Id varchar,AlertType varchar,Status varchar,Tier varchar,Server varchar,GroupId varchar,GraphId varchar,Severity varchar,PrevSeverity varchar,AlertTime timestamp,IncidentId varchar,AlertValue varchar,Threshold varchar,alertmessage varchar,anomalous int,tierincidentid varchar,rname varchar,ip varchar,version varchar,rcaflag int,report varchar,forceRCA int,gTag varchar,primary key(id,status,server,alerttime,incidentid,ip));

		DO \$$ BEGIN RAISE INFO 'Table Name [Incident]'; END \$$ ;
		create table Incident(Id varchar primary key,Status varchar,IncidentType varchar,StartTime timestamp,EndTime timestamp,RootCause varchar,ProbableRootCause varchar,server varchar,tier varchar,allprobablerootcauses varchar,alertcount int,anomalous int,tags text[] default array[]::text[],graph varchar,userspecifiedroots text[] default array[]::text[],ip varchar,version varchar,systemgeneratedroots text[] default array[]::text[],incidentdata varchar, rcapath varchar, probablegraph varchar, gTag varchar, source int);

		DO \$$ BEGIN RAISE INFO 'Table Name [ActiveIncident]'; END \$$ ;
		create table activeincident(Id varchar primary key,Status varchar,IncidentType varchar,StartTime timestamp,EndTime timestamp,RootCause varchar,ProbableRootCause varchar,server varchar,tier varchar,allprobablerootcauses varchar,alertcount int,anomalous int,tags text[] default array[]::text[],graph varchar,userspecifiedroots text[] default array[]::text[],ip varchar,version varchar,systemgeneratedroots text[] default array[]::text[],source varchar);
		-- create table activeincident(Id varchar primary key,Status varchar,IncidentType varchar,StartTime timestamp,EndTime timestamp,RootCause varchar,ProbableRootCause varchar,server varchar,tier varchar,alertcount int,anomalous int);

		DO \$$ BEGIN RAISE INFO 'Table Name [RootCause]'; END \$$ ;
		create table rootcause(IncidentType varchar primary key,TypeCount int,PrimaryCause varchar,IncidentGraph varchar);

		DO \$$ BEGIN RAISE INFO 'Table Name [AutoSMC]'; END \$$ ;
		create table auto_smc(metricAid varchar,metricBid varchar,metricAname varchar,metricBname varchar,forWeightCount int,backWeightCount int,ACount int,BCount int,ABCount int,prevACount int,prevBCount int,prevABCount int,primary key(metricAid,metricBid));

		DO \$$ BEGIN RAISE INFO 'Table Name [ManualSMC]'; END \$$ ;
		create table manual_smc(metricAid varchar,metricBid varchar,metricAname varchar,metricBname varchar,forsurety int,backsurety int,primary key(metricAid,metricBid));

		DO \$$ BEGIN RAISE INFO 'Table Name [SuggestedSMC]'; END \$$ ;
		create table suggested_smc(metricAid varchar,metricBid varchar,metricAname varchar,metricBname varchar,forWeightCount int,backWeightCount int,ACount int,BCount int,ABCount int,prevACount int,prevBCount int,prevABCount int,created date,lastUpdated timestamp default now(),visible int default 1,primary key(metricAid,metricBid,created));

		DO \$$ BEGIN RAISE INFO 'Table Name [TxnFlowGraph]'; END \$$ ;
		create table txnflowgraph(tier varchar,txn varchar,graph varchar,tierip varchar,primary key (tier,txn));

		DO \$$ BEGIN RAISE INFO 'Table Name [GraphData]'; END \$$ ;
		create table graphdata(groupid_graphid varchar primary key,groupname varchar,graphname varchar);

		DO \$$ BEGIN RAISE INFO 'Table Name [Thresholds]'; END \$$ ;
		create table thresholds(tier varchar,grpid varchar,graphid varchar,threshold varchar,date_of_eval timestamp default now(),primary key(tier,grpid,graphid));

		DO \$$ BEGIN RAISE INFO 'Table Name [AnomalyCount]'; END \$$ ;
		CREATE TABLE AnomalyCount(Btname VARCHAR NOT NULL,Count INTEGER NOT NULL,Timestamp TIMESTAMP NOT NULL,Total INTEGER NOT NULL,Percentage FLOAT NOT NULL,Bucket Text DEFAULT 'Null',PRIMARY KEY(Btname,Timestamp));

		DO \$$ BEGIN RAISE INFO 'Table Name [TxnList]'; END \$$ ;
		CREATE TABLE Txnlist(Btname VARCHAR NOT NULL,Tier VARCHAR NOT NULL,Fromtime TIMESTAMP NOT NULL,LatestFile TIMESTAMP,CheckBox BOOLEAN DEFAULT 'false' NOT NULL,State VARCHAR DEFAULT 'Idle',PRIMARY KEY (Btname,tier));

		DO \$$ BEGIN RAISE INFO 'Table Name [AnomalousTxnTable'; END \$$ ;
		CREATE TABLE AnomalousTxnTable(Btname VARCHAR NOT NULL,Timestamp TIMESTAMP NOT NULL,FPID VARCHAR NOT NULL,N_Tiers INTEGER NOT NULL,responseTime NUMERIC NOT NULL,N_callouts INTEGER NOT NULL,N_DBCallouts INTEGER NOT NULL,backendDuration INTEGER,N_httpcalls INTEGER NOT NULL,N_errorcount INTEGER NOT NULL,Normalized_N_Tiers NUMERIC NOT NULL,Normalized_responseTime NUMERIC NOT NULL,Normalized_N_callouts NUMERIC NOT NULL,Normalized_N_DBCallouts NUMERIC NOT NULL,Normalized_backendDuration NUMERIC,Normalized_N_httpcalls NUMERIC NOT NULL,Normalized_N_errorcount NUMERIC NOT NULL,Abnormality TEXT NOT NULL,PRIMARY KEY (FPID,Btname,Timestamp));

		DO \$$ BEGIN RAISE INFO 'Table Name [PriorityIncidentIds]'; END \$$ ;
		create table priorityIncidentIds(incidentId varchar,time timestamp default now(),machineId varchar,processingStatus int default 0,primary key(incidentId,time));

		DO \$$ BEGIN RAISE INFO 'Table Name [Comments]'; END \$$ ;
		create table comments(incident_type varchar,comment varchar,time timestamp);

		DO \$$ BEGIN RAISE INFO 'Table Name [Filter]'; END \$$ ;
		create table filter(pattern varchar,type varchar,time timestamp,flag varchar,primary key(pattern,type));

		DO \$$ BEGIN RAISE INFO 'Table Name [custometric]'; END \$$ ;
		create table custometric(groupid varchar,graphid varchar,groupname varchar,graphname varchar,description varchar,primary key(groupid,graphid));

		Do \$$ BEGIN RAISE INFO 'Table Name [metricthreshold]'; END \$$ ;
		create table metricthreshold(groupname varchar,graphname varchar,vector varchar,mean varchar,xfactorval varchar,threshold varchar,variance varchar,range varchar,primary key(groupname,graphname,vector));

		DO \$$ BEGIN RAISE INFO 'Table Name [email_filter]'; END \$$ ;
		create table email_filter(pattern varchar,severity integer,flag varchar,to_mail_list text[],cc_mail_list text[],primary key(pattern,severity,flag));

		DO \$$ BEGIN RAISE INFO 'Table Name [iprca]'; END \$$ ;
		create table iprca(iprcakey varchar,tier varchar,anomalous_serverids varchar[],nonanomalous_serverids varchar[]);

		DO \$$ BEGIN RAISE INFO 'Table Name [iprcaconfig]'; END \$$ ;
		create table iprcaconfig(currenttier varchar,transactionpattern varchar,roottier varchar);

		DO \$$ BEGIN RAISE INFO 'Table Name [iprcametric]'; END \$$ ;
		create table iprcametric(transactionpattern varchar,metric varchar);

		DO \$$ BEGIN RAISE INFO 'Table Name [correlationWeights]'; END \$$ ;
		create table correlationWeights(metricAid varchar,metricAname varchar,metricBid varchar,metricBname varchar,hitCount int,missCount int, primary key(metricAid,metricBid));

		-- list created tables
		-- \d ;\d alert;\d incident;\d activeincident;\d rootcause;\d auto_smc;\d manual_smc;\d txnflowgraph;\d graphdata;\d thresholds;

END_OF_DBSCRIPT

		: '
		echo "Restoring Database..."
		while true; do
			read -ep "Do you wish to Restore Database from Dir[$backupDir] ? : " yn
			case $yn in
				[Yy]* ) echo "Entered:"$yn
					restore_db
					break
					;;
				[Nn]* ) echo "Entered:"$yn
					break
					;;
				* )     echo "Please answer yes or no ! : "
					;;
			esac
		done
		'
	fi
update_db
}

import_data(){
	tableName=$1
	fileDir=$2
	if [ -z "$tableName" -a -z "$fileDir" ]
	then
		echo "Import: Configurations are missing!"
	else
		corr_stat=`psql -X -A -U postgres -d $dbName -t -c "select count(*) from $tableName"`
		sfcount=`ls $fileDir/ | grep "\-$tableName\-" | wc -l`
		if [ "${corr_stat}" -eq "0" -a "$sfcount" -eq "1" ]; then
			echo "Import: [$tableName]"
			sourceFile=$(ls $fileDir/ | grep "\-$tableName\-")
			psql -U postgres -d $dbName -c "\COPY $tableName from '$fileDir/$sourceFile' DELIMITER ',' CSV HEADER;"
		else
			echo "Import: [$sfcount] source file(s) & [$corr_stat] available rows in [$tableName]!"
		fi
	fi
}

update_db(){
if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
echo -e "${BOLD}Updating [$dbName] Database!${NONE}"
psql -U postgres << END_OF_DBSCRIPT

\c $dbName

DO \$$ BEGIN RAISE INFO 'Table Name [Alert] update gtag'; END \$$ ;
DO \$$
    BEGIN
        BEGIN
            ALTER TABLE alert ADD COLUMN gtag VARCHAR;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column gtag already exists in [alert]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [Incident] update gtag'; END \$$ ;
DO \$$
    BEGIN
        BEGIN
            ALTER TABLE incident ADD COLUMN gtag varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column gtag already exists in [incident]';
        END;
    END;
\$$;


DO \$$ BEGIN RAISE INFO 'Table Name [Incident] update probablegraph'; END \$$ ;
DO \$$
    BEGIN
        BEGIN
            ALTER TABLE incident ADD COLUMN probablegraph varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column probablegraph already exists in [incident]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [Incident] update rcapath'; END \$$ ;
DO \$$
    BEGIN
        BEGIN
            ALTER TABLE incident ADD COLUMN rcapath varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column rcapath already exists in [incident]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [Alert] update forceRCA'; END \$$ ;
DO \$$
    BEGIN
        BEGIN
            ALTER TABLE alert ADD COLUMN forceRCA INT;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column forceRCA already exists in [alert]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [Alert] update report'; END \$$ ;
DO \$$ 
    BEGIN
        BEGIN
            ALTER TABLE alert ADD COLUMN report varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column report already exists in [alert]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [Incident] update rcastatus'; END \$$ ;
DO \$$ 
    BEGIN
        BEGIN
            ALTER TABLE incident ADD COLUMN source INT ,ADD COLUMN rcastatus varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column rcastatus already exists in [incident]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [ActiveIncident] update rcastatus'; END \$$ ;
DO \$$ 
    BEGIN
        BEGIN
            ALTER TABLE activeincident ADD COLUMN source INT, ADD COLUMN rcastatus varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column rcastatus already exists in [activeincident]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [IPRCA] update pendingIncidentids'; END \$$ ;
-- alter table iprca add column IF NOT EXISTS pendingIncidentids varchar[];
DO \$$ 
    BEGIN
        BEGIN
            ALTER TABLE IPRCA ADD COLUMN pendingIncidentids varchar[];
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column pendingIncidentids already exists in [IPRCA]';
        END;
    END;
\$$;
update iprca set pendingincidentids='{}' where pendingincidentids is null;

DO \$$ BEGIN RAISE INFO 'Table Name [IPRCACONFIG] update metric and transaction'; END \$$ ;
-- alter table iprcaconfig add column if not exists metric varchar,add column if not exists transaction varchar;
DO \$$ 
    BEGIN
        BEGIN
            alter table iprcaconfig add column metric varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column metric already exists in [IPRCACONFIG]';
        END;
        BEGIN
            alter table iprcaconfig add column transaction varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column transaction already exists in [IPRCACONFIG]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [IPRCAMETRIC] update rootcauseMetric'; END \$$ ;
-- alter table iprcametric add column IF NOT EXISTS rootcauseMetric varchar;
DO \$$ 
    BEGIN
        BEGIN
            ALTER TABLE iprcametric ADD COLUMN rootcauseMetric varchar;
        EXCEPTION
            WHEN duplicate_column THEN RAISE NOTICE 'column rootcauseMetric already exists in [IPRCAMETRIC]';
        END;
    END;
\$$;

DO \$$ BEGIN RAISE INFO 'Table Name [AnalysisSummary]'; END \$$ ;
create table IF NOT EXISTS AnalysisSummary(incidentid varchar, startTime timestamp,autoRcaDuration bigint, analyzedMetricCount int, primary key(incidentid));


DO \$$ BEGIN RAISE INFO 'Table Name [Manual_Threshold]'; END \$$ ;
create table IF NOT EXISTS manual_threshold(topology varchar,grpid varchar,grpname varchar,graphid varchar,graphname varchar,threshold varchar,primary key(topology,grpid,graphid));

DO \$$ BEGIN RAISE INFO 'Table Name [metricinfo]'; END \$$ ;
create table IF NOT EXISTS metricinfo(groupid varchar,graphid varchar,groupname varchar,graphname varchar,serverip varchar,lastupdated timestamp default now(),primary key(groupid,graphid,serverip));

DO \$$ BEGIN RAISE INFO 'Table Name [nvdetails]'; END \$$ ;
create table IF NOT EXISTS nvdetails(incidentid varchar, metadata varchar, requestjson varchar,status int, sessiondata varchar, exphotsptFlag varchar,requestTime timestamp);

DO \$$ BEGIN RAISE INFO 'Table Name [exceptionandhotspotstats]'; END \$$ ;
create table IF NOT EXISTS exceptionandhotspotStats (incidentid varchar, tier varchar, expresponsejson varchar,requestJson varchar,threadresponsejson varchar,flag int, status int);

DO \$$ BEGIN RAISE INFO 'Table Name [exceptionstatssummary]'; END \$$ ;
create table  IF NOT EXISTS exceptionstatssummary(incidentid  varchar, tier varchar, server varchar, exceptionname varchar,excpdetail varchar,status int);

DO \$$ BEGIN RAISE INFO 'Table Name [hotspotstatssummary]'; END \$$ ;
create table  IF NOT EXISTS hotspotstatssummary(incidentid varchar, tier varchar, server varchar,threadname varchar,hotspotdetail varchar,status int);

DO \$$ BEGIN RAISE INFO 'Indexing [Alert]'; END \$$ ;
create index IF NOT EXISTS idx_alert_incidentid on alert(incidentid);
create index IF NOT EXISTS idx_alert_ip on alert(ip);
create index IF NOT EXISTS idx_alert_version on alert(version);
create index IF NOT EXISTS idx_alert_tierincidentid on alert(tierincidentid);
create index IF NOT EXISTS idx_alert_alerttime on alert(alerttime);
create index IF NOT EXISTS idx_alert_rname on alert(rname);
create index IF NOT EXISTS idx_alert_tier on alert(tier);
create index IF NOT EXISTS idx_alert_id on alert(id);

DO \$$ BEGIN RAISE INFO 'Indexing [Incident]'; END \$$ ;
create index IF NOT EXISTS idx_incident_tags on incident(tags);
create index IF NOT EXISTS idx_incident_ip on incident(ip);
create index IF NOT EXISTS idx_incident_endtime on incident(endtime);
create index IF NOT EXISTS idx_incident_starttime on incident(starttime);
create index IF NOT EXISTS idx_incident_status on incident(status);
create index IF NOT EXISTS idx_incident_tier on incident(tier);

DO \$$ BEGIN RAISE INFO 'Indexing [nvdetails]'; END \$$ ;
create index IF NOT EXISTS idx_nvdetails_status on nvdetails(status);

DO \$$ BEGIN RAISE INFO 'alertinging [manual_smc]'; END \$$ ;
alter table manual_smc add polarity int default 0;

DO \$$ BEGIN RAISE INFO 'alertining [manual_smc]'; END \$$ ;
alter table manual_smc add resource int default 0;

Alter table public.activeincident owner to cavisson;
Alter table public.alert owner to cavisson;
Alter table public.analysissummary owner to cavisson;
Alter table public.anomaloustxntable owner to cavisson;
Alter table public.anomalycount owner to cavisson;
Alter table public.auto_smc owner to cavisson;
Alter table public.comments owner to cavisson;
Alter table public.correlationweights owner to cavisson;
Alter table public.custometric owner to cavisson;
Alter table public.email_filter owner to cavisson;
Alter table public.exceptionandhotspotstats owner to cavisson;
Alter table public.exceptionstatssummary owner to cavisson;
Alter table public.filter owner to cavisson;
Alter table public.graphdata owner to cavisson;
Alter table public.hotspotstatssummary owner to cavisson;
Alter table public.incident owner to cavisson;
Alter table public.iprca owner to cavisson;
Alter table public.iprcaconfig owner to cavisson;
Alter table public.iprcametric owner to cavisson;
Alter table public.manual_smc owner to cavisson;
Alter table public.manual_threshold owner to cavisson;
Alter table public.metricinfo owner to cavisson;
Alter table public.metricthreshold owner to cavisson;
Alter table public.nvdetails owner to cavisson;
Alter table public.priorityincidentids owner to cavisson;
Alter table public.rootcause owner to cavisson;
Alter table public.suggested_smc owner to cavisson;
Alter table public.thresholds owner to cavisson;
Alter table public.txnflowgraph owner to cavisson;
Alter table public.txnlist owner to cavisson;

END_OF_DBSCRIPT

echo "Checking & Restoring Correlations!"
import_data "manual_smc" "$knowledgeBaseDir"
import_data "auto_smc" "$knowledgeBaseDir"
import_data "metricinfo" "$knowledgeBaseDir"

else
    echo "[$dbName] database not found for update!" 
fi
}

feature_db(){
	if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
		#echo "$dbName Database Found!..."
		echo -e "${BOLD}Restoring [$dbName] Database!${NONE}"

		echo "This Feature is Not Yet Supported."
		echo "Try this command : psql -U postgres -d $dbName -c \"\COPY Table_name FROM '/home/cavisson/work/ml$dbName/$backupDir/filename' DELIMITER ',' CSV HEADER;\""
		#psql -U postgres -d $dbName -c "\COPY auto_smc FROM '/home/cavisson/work/ml$dbName/backupDB/autosmc6-3-19.csv' DELIMITER ',' CSV HEADER;"

		#psql -U postgres -d $dbName -c "\COPY manual_smc FROM '/home/cavisson/work/ml$dbName/backupDB/manualsmc6-3-19.csv' DELIMITER ',' CSV HEADER;"
	else
		echo "[$dbName] Database Not Found!"
	fi
}

restore_db(){

	if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
		#echo "$dbName Database Found!..."
		echo -e "${BOLD}Restoring [$dbName] Database!${NONE}"
		today=`date '+%Y_%m_%d__%H_%M_%S'`;
		filepath=$backupDir
		if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
			echo "[$dbName] Database Found!..."
			for i in $(psql -t -U postgres -d $dbName -c "\d"|cut -d \| -f 2)
			do
				echo "Table Name: "$i
				sfcount=`ls $filepath/ | grep "\-$i\-" | wc -l`
				if [ $sfcount -eq 1 ]
				then
					sourceFile=$(ls $filepath/ | grep "\-$i\-")
					echo "Source File: "$sourceFile
					psql -U postgres -d $dbName -c "\COPY $i from '$filepath/$sourceFile' DELIMITER ',' CSV HEADER;"
				else
					echo "Error: $sfcount Source File(s) Found !"
				fi
			done
		else
			echo "[$dbName] Database Not Found!"
			# ruh-roh
			# $? is 1
		fi
	else
		echo "[$dbName] Database Not Found!"
	fi
}

create_backup(){
	today=`date '+%Y_%m_%d__%H_%M_%S'`;
	filename=$backupDir/db-$today.csv
	filepath=$backupDir
	if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
		echo "[$dbName] Database Found! Taking Backup..."
		rm -rf $backupDir
		mkdir -p $backupDir
		for i in $(psql -t -U postgres -d $dbName -c "\d"|cut -d \| -f 2)
		do
			echo "Table Name: "$i
			psql -U postgres -d $dbName -c "\COPY $i TO '$filepath/db-$i-$today.csv' DELIMITER ',' CSV HEADER;"
		done
		#tar -cvzf $backupDir-$today.tar.gz $backupDir/
		#echo "Created tar named as $backupDir-"$today".tar.gz"
	else
		echo "[$dbName] Database Not Found!"
		# ruh-roh
		# $? is 1
	fi
}

delete_db(){
	if psql -U postgres -lqt | cut -d \| -f 1 | grep -qw $dbName; then
		echo "[$dbName] Database Found! Deleting Data..."
		for i in $(psql -t -U postgres -d $dbName -c "\d"|cut -d \| -f 2)
		do
			echo "Table Name: "$i
			psql -U postgres -d $dbName -c "delete from $i"
		done
	else
		echo "[$dbName] Database Not Found!"
		# ruh-roh
		# $? is 1
	fi
}

show_menus() {
	#clear
	echo "~~~~~~~~~~~~~~~~~~~~~~~~~~"	
	echo "     Database Options     "
	echo "~~~~~~~~~~~~~~~~~~~~~~~~~~"
	echo ""
	echo "1. Create Database"
	echo "2. Create Backup"
	echo "3. Restore Database"
	echo "4. Remove Database"
	echo "5. Clean Database"
	echo "6. Update Schema"
	echo "7. Back"
	echo ""
}

back_option(){
        sh $workDir/utility/aiops_installer.sh admin
}

read_options(){
	local choice
	read -ep "Enter choice [ 1 - 6 ]: " choice
	case $choice in
		1)  create_db
			pause;;
		2)  create_backup 
			pause;;
		3)  restore_db
			pause;;
		4)  drop_db
			pause;;
		5)  delete_db
			pause;;
		6)	update_db
			pause;;
		7) #back_option
                        exit 0;;
		*) echo -e "${RED}Wrong Choice! Please Wait...${STD}" && sleep 1
	esac
}

# Trap CTRL+C, CTRL+Z and quit singles
#trap '' SIGINT SIGQUIT SIGTSTP

if [ -f "$CONFIG" ]
then
	source $CONFIG
    backupDir=$workDir/${dbName}_bak
else
	echo "[Aiops Database] configurations not found!"
	exit
fi

if [ "$1" = 0 ]
then
	create_db
elif [ "$1" = 1 ] 
then
	while true
	do
		clear 
		show_menus
		read_options
	done
elif [ "$1" = 2 ]
then
    update_db
elif [ "$1" = 3 ]
then
	update_Table
else
echo "Args Required! [0-CreateDB,1-Verbose,2-UpdateDB,3-UpdateTable]"
fi