#!/usr/bin/perl

use strict;
use Getopt::Long;
use POSIX;
use File::Basename;
use File::Path;
use DateTime;

our $version = "3.0";

our $log_file = "/var/log/opdb-dump.log";
our $log_file_error = "/var/log/opdb-dump-errors.log";
our $backup_dir = "/var/tmp/opmondb";
our $mysql = "/usr/bin/mysql --max_allowed_packet=512M";
our $mysqldump = "/usr/bin/mysqldump -q -f --max_allowed_packet=512M --skip-add-locks --skip-comments --skip-disable-keys --single-transaction -c --insert-ignore --quick";
our $mysqladmin = "/usr/bin/mysqladmin";
our ($filename, $newfilename);
our $conf_file = "/usr/local/opmon/etc/db.php";
our $clean_old_backup = "/usr/bin/find $backup_dir -mtime +30 -delete >/dev/null 2>/dev/null";
our %date = _gettime();

# ----- Change Log Process ----- #
our $nohup = "/usr/bin/nohup";
our $php = "/usr/bin/php";
our $clean_db = "/usr/local/opmon/utils/clean_db_opmon4.php > /var/log/opmon-cleandb-optimize.log";
our $summarize_db = "/usr/local/opmon/utils/summarize_perfparse.php";
# ------------------------------ #

our ( $dbuser, $dbpass, $dbhost, @dblist, @host_ids, @tablelist,$opt_recover,$opt_help,$opt_version, $opt_export );
our ( $opt_full_recover, $opt_full_export, $table_name_tmp, $opt_disaster_recovery, $opt_export_disaster_recovery);
our ( $opt_export_disaster_recovery_date, $opt_disaster_recovery_date, $opt_list_disaster_backups, $datedisaster);
our @ignoredDBs = ( "information_schema", "backup", "test", "traffic");
our @tablesWithDrop = ( "host_state_change", "service_state_change", "dashboards", "dashboards_resources", "scat_activities", "scat_cf_definitions", "scat_cf_values", "scat_contacts", "scat_itens", "scat_log", "scat_sazdays", "scat_slas", "scats", "interface_shortcut_items", "interface_shortcut_names", "interface_user_favorites", "eventguard_rules", "timecuts", "shared_resources", "host_comment", "service_comment", "custom_reports", "reports", "scheduled_reports", "scheduled_reports_logs", "opdocs_list", "opdocs_contacts", "opdocs_comunication_links", "opdocs_resources" );
our @disasterRecoveryDBs = ( "opcfg", "opmon4", "grafana");
our $error_count=0;

our $script_name = basename($0);

our $usage = '
Usage: '. $0 .' [OPTION]...
Export/Import OpMon database

General Options:
	-r <dbname> - Import  database from /var/tmp/opmondb to mysql
	-e <dbname> - Export  database from mysql to /var/tmp/opmondb
	-R - Recover all databases from /var/tmp/opmondb to mysql
	-E - Export  all databases from mysql to /var/tmp/opmondb
	-D - Disaster recovery - Import needed system tables for quick opmon start
	-C - Export Disaster recovery databases and tables -Export needed system tables for quick opmon start

	-H - Export Disaster recovery databases and tables with datetime on directory, uses current datetime
		Export in the directory /var/tmp/opmondb/disaster_recovery/<datetime>

	-d <datetime> - Disaster recovery a specific disaster recovery dump
		Ex.: opmon-base.pl -d 2018-09-19_09_30 -> the date time listed on -l option

	-l - List diaster recovery databases available
		Ex.: opmon-base.pl -l  -> bellow the list of available disaster recovery dumps

		2018-09-19_09_30	2018-09-19_09_05	2018-09-19_08_05
		2018-09-19_07_05	2018-09-19_06_05	2018-09-19_05_05
		2018-09-19_04_05	2018-09-19_03_05	2018-09-19_02_05

';

sub _main {
	_logger("::: OpMon Database Export/Import tool Starting :::");

	print "Logging on file $log_file\n";
	# get command line options
	getOptions();

	if (!$opt_export_disaster_recovery_date and !$opt_list_disaster_backups) {
	    if (already_is_running()) {
	        _logger("$script_name already is running. Good bye...");
	        print "$script_name already is running. Good bye...\n";
	        exit(0);
	    }
	}


	if ($opt_list_disaster_backups) {
		listDisasterRecoveryDumps();
		exit(0);	
	}

	cleanDisasterRecoveryDumps($backup_dir, 100);

	if ($opt_export_disaster_recovery_date) {
		$opt_export_disaster_recovery = 1;
		my $dt = DateTime->now(time_zone=>'local');
		$datedisaster = $dt->strftime('%Y-%m-%d_%H_%M');
		$backup_dir = $backup_dir.'/disaster_recovery/'.$datedisaster;
	}
	if ($opt_disaster_recovery_date) {
		$opt_disaster_recovery = 1;
		$backup_dir = $backup_dir.'/disaster_recovery/'.$opt_disaster_recovery_date;
		unless (-e $backup_dir) {
			_logger("Disaster recovery backup dir $backup_dir does not exists",1);
			exit(1);
		}
	}

	# If disaser recovery mode set, recover opcfg and special opmon4 tables
	if ($opt_disaster_recovery) {
		$opt_recover="opcfg";
		if ($opt_disaster_recovery_date) {
			_logger(" -> Starting database disaster recovery with date $opt_disaster_recovery_date",1);
		}
		else {
			_logger(" -> Starting database disaster recovery",1);
		}
	}

	# Creates backup dir if it doest exist 
	unless (-e $backup_dir) {
		# Create backup dir
		mkpath($backup_dir);
	}

	# Read database config
	_read_conf();

	if ($opt_recover) {
		_logger(" -> Starting database $opt_recover recover",1);
		my $db_dir_recover=$backup_dir."/".$opt_recover;
		unless (-e $db_dir_recover) {
			_logger(" -> Dump for $opt_recover does not exist, cant recover it",1);
			exit(0);
		}
		_importfull($opt_recover, $opt_disaster_recovery);
		if ($error_count>0) {
			_logger(" -> Database $opt_recover recover with errors, check log $log_file",1);
		}
		else {
			_logger(" -> Database $opt_recover recover done",1);
		}
		exit(0);
	}
	if ($opt_full_recover) {
		_logger(" -> Starting full database recover",1);
		_importfull();
		if ($error_count>0) {
			_logger(" -> Database recover with errors, check log $log_file",1);
		}
		else {
			_logger(" -> Database recover done",1);
		}
		exit(0);
	}
	if ($opt_export || $opt_export_disaster_recovery) {
		if ($opt_export_disaster_recovery) {
			$opt_export = "";
			if ($opt_export_disaster_recovery_date) {
				_logger(" -> Starting database disaster recovery export by date $datedisaster",1);
			}
			else {
				_logger(" -> Starting database disaster recovery export",1);
			}
		}
		else {
			_logger(" -> Starting database $opt_export export",1);
			$opt_export_disaster_recovery = "";
		}
		_backupfull($opt_export, $opt_export_disaster_recovery);
		if ($error_count>0) {
			_logger(" -> Database $opt_export export with errors, check log $log_file",1);
		}
		else {
			_logger(" -> Database $opt_export export done",1);
		}
		exit(0);
	}
	if ($opt_full_export) {
		_logger("Starting all databases export",1);
		
		# Clean and optimize database before dumping on the first sunday of the month
		my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
		if ($mday>=1 and $mday<=7) {
			if ($wday==0) {
				_logger(" -> First of the month, running Summarization Process");
				system("$nohup $php -q $summarize_db 2>/dev/null");
				_logger(" -> First of the month, running CleanDB Process");
				system("$nohup $php -q $clean_db 2>/dev/null");
			}
		}
		
		# Perform a full backup
		#_slave("stop");	
		_backupfull();
		#_slave("start");
		
		# Clean old SQL dumps
		system($clean_old_backup);
		
		if ($error_count>0) {
			_logger(" -> OpMon Database Backup Finished with errors, check log $log_file",1);
		}
		else {
			_logger("::: OpMon Database Backup Finished :::");
		}
	}
	else {
		_logger("-> No options selceted, nothing done");
		print $usage;
	}
	exit(0);
}

sub already_is_running() {
    my $num_procs = `ps -ef | grep -v grep | grep '$script_name' | wc -l`;

    # The first process is the shell opened by cron.
    # The second process is the perl running this script;
    return ($num_procs > 2);
}

sub get_opmondb_dirs {
	my @array_tmp=`/bin/ls -p $backup_dir | /bin/grep "/" | /usr/bin/tr -d "/"`;
	@dblist=();
	push @dblist, "opcfg";
	foreach (@array_tmp) {
		my $tmp=$_;
		chomp($tmp);
		if ($tmp ne "opcfg" and $tmp ne "disaster_recovery") {
			push @dblist, $tmp if (!_in_array($tmp));
		}
	}
	@array_tmp=();
}

sub _gettime {
   my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
   $mon++;
   $year+=1900;

   my %self = (
      'wday'	=>	$wday,
      'year'	=>	$year,
      'mon'	=>	$mon,
      'mday'	=>	$mday,
      'hour'	=>	$hour,
      'min'	=>	$min,
      'sec'	=>	$sec
   );
   return %self;
}

sub _logger {
	my ($msg, $show) = @_;
	
	my %date = _gettime();
	open(_LOG_, ">>$log_file");
	if ($show==1) {
		print "$date{year}/$date{mon}/$date{mday} $date{hour}:$date{min}:$date{sec} - $msg\n";
	}
	print _LOG_ "$date{year}/$date{mon}/$date{mday} $date{hour}:$date{min}:$date{sec} - $msg\n";
	close(_LOG_);
	
	# Remove old log file if is bigger then 10MB
	my @log_stat = stat($log_file);
	if ($#log_stat>=7) {
		unlink($log_file) if ($log_stat[7] >= 10485760);
	}
}

sub _logger_dump_errors {
	my ($msg, $show) = @_;
	
	my %date = _gettime();
	open(_LOG_, ">>$log_file_error");
	if ($show==1) {
		print "$date{year}/$date{mon}/$date{mday} $date{hour}:$date{min}:$date{sec} - $msg\n";

	}
	print _LOG_ "$date{year}/$date{mon}/$date{mday} $date{hour}:$date{min}:$date{sec} - $msg\n";
	close(_LOG_);
	
	# Remove old log file if is bigger then 10MB
	my @log_stat = stat($log_file_error);
	if ($#log_stat>=7) {
		my @log_stat = stat($log_file_error);
		unlink($log_file_error) if ($log_stat[7] >= 10485760);
	}
}

sub _importfull() {
	my ($dbname_to_recover, $disater_recovery)=@_;
	my $ok=0;
	if ($dbname_to_recover) {
		$#dblist=-1;
		if ($disater_recovery) {
			@dblist = @disasterRecoveryDBs;
		}
		else {
			push @dblist, $dbname_to_recover;
		}
	}
	else {
		get_opmondb_dirs();
	}

	foreach(@dblist) {
		my $dbname=$_;
		# opmon4 databases has a special recovery bellow for diaster recovery
		if ($disater_recovery and $dbname eq "opmon4") {
			next;
		}

		system("$mysqladmin -u $dbuser -h $dbhost --password=$dbpass create $dbname > /dev/null 2>/dev/null");
		# Create directory for tables dump
		my $backup_dir_tmp = $backup_dir."/".$dbname."/*.sql";
		my $backup_dir_tmp_gz = $backup_dir."/".$dbname."/*.sql.gz";
		my @sql_files=();
		@sql_files=`ls -1 $backup_dir_tmp 2>/dev/null | ls -1 $backup_dir_tmp_gz 2>/dev/null`;
		foreach(sort(@sql_files)) {
			my $sql_file=$_;
			chomp($sql_file);
			#_logger(" $dbname -->\t$sql_file");
			if ($sql_file =~ m/\.gz/) {
				uncompressFile($sql_file);
				$sql_file =~ s/.gz//g;
			}
			$ok=system("$mysql -f -u $dbuser -h $dbhost --password=$dbpass $dbname < $sql_file 2>/dev/null");
			if ($ok==0) {
				_logger(" $dbname <--\t$sql_file OK", 1);
			}
			else {
				$error_count=$error_count+1;
				_logger(" $dbname <--\t$sql_file ERROR",1);
			}

			if ( -e $sql_file ) {
				compressFile($sql_file);
			}
		}
	}
	# Recovery disaster recovery tables from opmon4
	if ($disater_recovery) {
		foreach(sort(@tablesWithDrop)) {
			my $sql_file=$_;
			chomp($sql_file);
			my $sql_file_gz = $backup_dir."/opmon4/".$sql_file.".sql.gz";
			my $sql_file = $backup_dir."/opmon4/".$sql_file.".sql";
			if ( -e $sql_file_gz ) {
				uncompressFile($sql_file);
			}
			$ok=system("$mysql -f -u $dbuser -h $dbhost --password=$dbpass opmon4 < $sql_file 2>/dev/null");
			if ($ok==0) {
				_logger(" opmon4 <--\t$sql_file OK", 1);
			}
			else {
				$error_count=$error_count+1;
				_logger(" opmon4 <--\t$sql_file ERROR", 1);
			}
			if ( -e $sql_file ) {
				compressFile($sql_file);
			}
		}
		# Create host_state_change and service_state_change tables
		_get_host_ids();
		foreach (@host_ids) {
			my $tmp = "host_state_change_".$_;
			_logger(" opmon4 <--\tcreating table $tmp OK");
			my $host_table="CREATE TABLE IF NOT EXISTS opmon4.host_state_change_".$_." LIKE opmon4.host_state_change";
			_run_query($host_table);
			
			my $tmp = "service_state_change_".$_;
			_logger(" opmon4 <--\tcreating table $tmp OK");
			my $service_table="CREATE TABLE IF NOT EXISTS opmon4.service_state_change_".$_." LIKE opmon4.service_state_change";
			_run_query($service_table);
		}	
	}
}

sub _backupfull {
	my ($dbname_to_export, $export_disater_recovery)=@_;
	my $ok=0;
	
	# Get database backup list
	_get_databases_list(); 
	
	if ($dbname_to_export) {
		unless (grep {$_ eq $dbname_to_export} @dblist) {
			_logger(" -> Database $dbname_to_export does not exist to be exported",1);
			exit(0);
		}
		$#dblist=-1;
		push @dblist, $dbname_to_export;
		# Clean old .sql from old export format
		my $old_sql_files_tmp="/bin/rm -f ".$backup_dir."/*.sql";
		system($old_sql_files_tmp);
	}
	elsif ($export_disater_recovery) {
		$#dblist=-1;
		push @dblist, "opcfg";
		push @dblist, "opmon4";
		push @dblist, "grafana";
	}
	else {
		_logger(" -> Dumping all databases",1);
		# Clean old .sql from old export format
		my $old_sql_files_tmp="/bin/rm -f ".$backup_dir."/*.sql";
		system($old_sql_files_tmp);
	}

	# Dump each database in a diffrent file  
  foreach(sort(@dblist)) {
  	my $dbname=$_;
    # Create directory for tables dump
    my $backup_dir_tmp=$backup_dir."/".$dbname;
    mkdir($backup_dir_tmp);
    if ($export_disater_recovery and $dbname eq "opmon4") {
		@tablelist = @tablesWithDrop;
    }
    else {
		_get_tables_list($dbname);
    }
    foreach(sort(@tablelist)) {
    	my $table_name=$_;
		$table_name_tmp=$table_name.".sql-tmp";
		$ok = 0;
		if ($dbname eq "opcfg" or $dbname eq "mysql" or $dbname eq "grafana") {
      		$ok=system("$mysqldump -u $dbuser -h $dbhost --password=$dbpass --add-drop-table $dbname $table_name > $backup_dir_tmp/$table_name_tmp 2>/dev/null");
      	}
		else {
			if ( grep( /^$table_name/, @tablesWithDrop ) ) {
      			$ok=system("$mysqldump -u $dbuser -h $dbhost --password=$dbpass --add-drop-table $dbname $table_name > $backup_dir_tmp/$table_name_tmp 2>/dev/null");
      		}
      		else {
      			$ok=system("$mysqldump -u $dbuser -h $dbhost --password=$dbpass --skip-add-drop-table $dbname $table_name > $backup_dir_tmp/$table_name_tmp 2>/dev/null");
      		}
    	}
			if ($ok==0) {
				_logger(" $dbname -->\t$table_name OK");
			}
			else {
				$error_count=$error_count+1;
				_logger(" $dbname -->\t$table_name ERROR");
			}
    	# Check if file has 0 bytes, if so, do not delete old one
    	$filename=$backup_dir_tmp."/".$table_name_tmp;
		$newfilename=$backup_dir_tmp."/".$table_name.".sql";
		my $newfilenamegz=$backup_dir_tmp."/".$table_name.".sql.gz";
    	our ($dev,$ino,$mode,$nlink,$uid,$gid,$rdev,$size,$atime,$mtime,$ctime,$blksize,$blocks) = stat($filename);
    	if ($size eq "") {
    		# File does not exits
    		_logger_dump_errors(" $dbname -->\t$table_name - did not export");
			}
			else {
				if ($size > 0) {
					# File exported OK, remove old and rename tmp file
					unlink($newfilename);
					unlink($newfilenamegz);
					rename($filename,$newfilename);
					compressFile($newfilename);
				}
				else {
					# print "File is zero sized\n";
					_logger_dump_errors(" $dbname -->\t$table_name - 0 size export file, probably corrupted table");
					unlink($filename);
				}
			}
		}
	}
}

sub compressFile {
	my ($file)=@_;
	my $run = "/usr/bin/gzip --fast ".$file;
	system($run);
}

sub uncompressFile {
	my ($file)=@_;
	my $run = "/usr/bin/gzip -d ".$file;
	system($run);
}

sub _read_conf {

   _logger(" -> Reading database config");

   open( FILE, "$conf_file" );
   while(<FILE>) {
      $dbuser = $1 if (m/DBUSER\s*=\s*[\'\"]*([^\'\"]*)[\'\"]*;/);
      $dbpass = $1 if (m/DBPASS\s*=\s*[\'\"]*([^\'\"]*)[\'\"]*;/);
      $dbhost = $1 if (m/DBHOST\s*=\s*[\'\"]*([^\'\"]*)[\'\"]*;/);
   }
   close(FILE);

}

sub _get_databases_list {
   my @list = `$mysql -u $dbuser -h $dbhost --password=$dbpass <<EOF | grep -v Database
show databases;
quit
EOF`;

	push @dblist, "opcfg";
	foreach(@list) {
		chomp;
		push @dblist, $_ if (!_in_array($_));
	}
}

sub _run_query {
	my ($sql)=@_;
	my @list = `$mysql -u $dbuser -h $dbhost --password=$dbpass <<EOF | grep -v Database
$sql;
quit
EOF`;

}

sub _get_host_ids {
	my @list = `$mysql -u $dbuser -h $dbhost --password=$dbpass <<EOF | grep -v host_id
select host_id from opcfg.nagios_hosts order by 1;
quit
EOF`;

	foreach(@list) {
		chomp;
		push @host_ids, $_ if (!_in_array($_));
	}
}

sub _get_tables_list {
	my ($dbname)=@_;
	 
	my @list = `$mysql -u $dbuser -h $dbhost --password=$dbpass --database=$dbname <<EOF | grep -v Tables_in_
show tables;
quit
EOF`;

	@tablelist=();
	foreach(@list) {
		chomp;
		push @tablelist, $_ if (!_in_array($_));
	}
}

sub _in_array {
	my $value = shift;
	return 0 if ($value eq $opt_export);
	foreach(@ignoredDBs) {
		return 1 if m/$value/;
	}
	return 0;
}

sub _slave {
	my $param = shift;
	 _logger(" -> Slave $param");
	`$mysql -h $dbhost -u $dbuser --password="$dbpass" <<SQL
slave $param;
exit
SQL`;
}

sub cleanDisasterRecoveryDumps {
	my ($backup_dir, $maxdumps)=@_;
	my $cmd;

	# clean diaster recoveries
	$backup_dir = $backup_dir.'/disaster_recovery';
	my $data_dh;
	if (!opendir($data_dh, $backup_dir)) {
		  _logger("Cannot open $backup_dir", 1);
		  return;
	}
	my $count = 0;
	my @files = sort { $b cmp $a } readdir($data_dh);
	my $lastfile = '';
	foreach my $file (@files) {
		if ($file eq '.' or $file eq '..') {
			next;
		}
		$count++;
		$lastfile = $file;
	}
	if ($count > $maxdumps and length($lastfile)>10) {
		_logger("Max disaster recovery dumps of $count reached, removing $lastfile",1);
		$cmd = "/bin/rm -fR ".$backup_dir.'/'.$lastfile;
		system($cmd);
	}
}

sub listDisasterRecoveryDumps {
	$backup_dir = $backup_dir.'/disaster_recovery/';
	print "Disaster recovery dumps in $backup_dir\n\n";
	opendir(my $data_dh, $backup_dir) or die "Cannot open $backup_dir\n";
	my $count = 0;
	my $internal_count = 0;
	my @files = readdir($data_dh);
	@files = sort {$b cmp $a} @files;
	foreach my $file (@files) {
		if ($file eq '.' or $file eq '..') {
			next;
		}
		$count++;
		$internal_count++;
		if ($internal_count == 3) {
			print $file."\n";
			$internal_count=0;
		}
		else {
			print $file."\t";
		}
	}
	print "\n$count disaster recoveries found in $backup_dir\n";
}

sub getOptions {  #command line options
	Getopt::Long::Configure('bundling');
  GetOptions(
		'r:s'	=> \$opt_recover,
		'e:s'	=> \$opt_export,
		'h|help|?'			=> \$opt_help,
		'v|version'		=> \$opt_version,
		'R' => \$opt_full_recover,
		'E' => \$opt_full_export,
		'D' => \$opt_disaster_recovery,
		'C' => \$opt_export_disaster_recovery,
		'H' => \$opt_export_disaster_recovery_date,
		'd:s' => \$opt_disaster_recovery_date,
		'l' => \$opt_list_disaster_backups,
	)or do {
			exit();
		};

	if ($opt_version) {
  	print_version($version);
	}
	if ($opt_help) {
		print $usage;
  	exit(0);
	}
}

&_main;
