#!/usr/local/cpanel/3rdparty/bin/perl
use strict;
use warnings;
use Filesys::Df;
use DBI;
use Getopt::Long;
use File::Temp;
use Try::Tiny;
use Log::Log4perl qw(:easy);

use Cpanel::MysqlUtils::MyCnf::Basic;

our $VERSION = '1.0.0';

Log::Log4perl->easy_init($WARN);

my $logger = Log::Log4perl->get_logger();

sub disk_free_percent {
	my $part = shift;
	print "$part\n";
	my $percent = Filesys::Df::df($part);
	return $percent->{per};
}

sub disk_free_mb {
	my $part = shift;
	my $free_mb = Filesys::Df::df($part, 1048576);
	my $free = sprintf "%0.f", $free_mb->{bavail};
	return $free;
}

sub get_table_size {
	my $dbh = shift;
	my $db_name = shift;
	my $table_name = shift;
	$db_name = $dbh->quote($db_name);
	$table_name = $dbh->quote($table_name);
	my $SQL_QUERY = "select table_name,
round(data_length/1024/1024) as data_length_mb,
round(data_free/1024/1024) as data_free_mb
 from information_schema.tables
 where table_name = $table_name and table_schema = $db_name";
	my $sth = $dbh->prepare($SQL_QUERY);
	$sth->execute() or warn DBI::errstr;
	my $data_length = 0; 
	my $data_free = 0;
	while (my @row = $sth->fetchrow_array()){
		($table_name, $data_length, $data_free) = @row;
	}
	$sth->finish() or warn DBI::errstr;
	$data_length = 0 if not defined $data_length;
	$data_free = 0 if not defined $data_free;
	return $data_length, $data_free;
}

sub get_tables_list {
	my $dbh = shift;
	my $db_name = shift;
	$db_name = $dbh->quote($db_name);
	my $QUERY = "select table_name from information_schema.tables where table_schema = $db_name";
	my $sth = $dbh->prepare($QUERY) or warn DBI::err;
	my @table_list;
	$sth->execute() or warn DBI::errstr;
	while (my @row = $sth->fetchrow_array()) {
		my ($tb_name) = @row;
		push @table_list, $tb_name;
	}
	return @table_list;

}

sub get_db_list {
	my $dbh = shift;
	my $sth = $dbh->prepare("SHOW DATABASES;");
	$sth->execute() or die DBI::errstr;
	my @db_list;
	while (my @row = $sth->fetchrow_array()){
		if ( grep {$row[0] eq $_} ("performance_schema", "information_schema")){
		next;
		}
		push @db_list, $row[0];
	}
	return @db_list;
}

sub sorted_tables_list {
	my $dbh = shift;
	my @db_list = get_db_list($dbh);
	my %table_size_hash;
	my @sorted_tables;
	my @table_list;
	my $data_length;
	my $data_free;
	foreach my $db (@db_list){
		try {
			@table_list = get_tables_list($dbh, $db);
		}
		catch {
			$logger->warn("[!] Couldn't get list of tables. skipping database $db : $@");
			next;
		};
		foreach my $table (@table_list){
			
			try {
				( $data_length, $data_free ) = get_table_size($dbh, $db, $table);
			}
			catch{
				$logger->warn("[!] Couldn't get size of table $db.$table: $@");
				next;
			};
			
			if($data_free > 0) {
				my @tmp_array = ($data_length, $data_free);
				$table_size_hash{"$db.$table"} = \@tmp_array;
			}
		}
	}
	foreach my $tb (sort {$table_size_hash{$a}[0] <=> $table_size_hash{$b}[0]} keys %table_size_hash) {
		if($table_size_hash{$tb}[1] > 25){
			push @sorted_tables, $tb;
		}
	}
	return \%table_size_hash, \@sorted_tables;


}
sub get_db_sizes {
	my $dbh = shift;
	my $db_name = shift;
	my @table_list = get_tables_list($dbh, $db_name);
	my %table_sizes;
	foreach my $tb_name (@table_list) {
		my ($data_length, $data_free) = get_table_size($dbh, $db_name, $tb_name);
		$table_sizes{$tb_name} = [$data_length, $data_free];

	}
	my $total_db_size = 0;
	my $total_db_free = 0;
	foreach my $tb (keys %table_sizes){
		$total_db_size = $total_db_size + $table_sizes{$tb}[0];
		$total_db_free = $total_db_free + $table_sizes{$tb}[1];
	}
	return $total_db_size, $total_db_free;
}

#Return True if free space in /var partition is less than 2 times size of table.
sub check_var_size {
	my $table_size = shift;
	$table_size = 2 * $table_size;
	my $var_free_size = disk_free_mb("/var/lib/mysql/");
	return ($var_free_size > $table_size) ? 0 : 1;
}

sub optimize_table {
	my $dbh = shift;
	my $table_name = shift;
	my ($db, $table) = split /\./, $table_name, 2;
	$table_name = $dbh->quote_identifier($db, $table);
	my $optimize_query = "OPTIMIZE TABLE $table_name;";
	my $sth = $dbh->prepare($optimize_query);
	$sth->execute() or warn DBI::errstr;
	while (my @row = $sth->fetchrow_array()) {
		print "@row\n";
}
	$sth->finish();
}

my $show;
GetOptions(
	'show' => \$show,
);

my $tmpdir = File::Temp->newdir;
#Create custom my.cnf
my $mycnfdata = <<"END_MSG";
[mysql]
key_buffer_size=128M
myisam_sort_buffer_size=256M
read_buffer_size=64M
write_buffer_size=64M
tmpdir=${tmpdir}
END_MSG

my $mycnffile = $tmpdir . "/mysqlcheck.cnf";
open my $fh, '>', $mycnffile or die "Can't write to '$mycnffile': $!\n";
print $fh $mycnfdata;
close $fh;

my $user = Cpanel::MysqlUtils::MyCnf::Basic::getmydbuser();
my $passwd = Cpanel::MysqlUtils::MyCnf::Basic::getmydbpass();
my $dbh = DBI->connect("DBI:mysql:mysql:localhost:mysql_read_default_file=$mycnffile", $user, $passwd, {RaiseError => 1}) or die DBI::errstr;

if ($show) {
	my @db_list = get_db_list($dbh);
	my %db_size_hash;
	my $db_size;
	my $db_free;
		foreach my $db (@db_list) {
		try{
			($db_size, $db_free) = get_db_sizes($dbh, $db);
		}
		catch{
			$logger->warn("[!] Couldn't get size of $db : $@");

		};
		
		if ($db_free > 0) {
			$db_size_hash{$db} = [$db_size, $db_free];
		}
	}
	if (keys %db_size_hash) {
		printf "%-34s %-18s %-18s\n", "DATABASE", "DATA LENGTH", "FREE SIZE";
		printf "%-34s %-18s %-18s\n", "-"x8, "-"x11, "-"x9;
		foreach my $db_name (sort {$db_size_hash{$b}[1] <=> $db_size_hash{$a}[1]} keys %db_size_hash) {
			printf "%-34s %-18s %-18s\n", $db_name, $db_size_hash{$db_name}[0]." MB", $db_size_hash{$db_name}[1]." MB";
		}
	} else {
		print "[!] There are no tables with free space to display\n";
	}
	exit(0);
}

my ($table_size, $tables) = sorted_tables_list($dbh);
if (not scalar @$tables) {
	print "[!] There are no tables with free space to optimize\n";
	exit(0);
}

my @optimized_tables;
foreach my $tb (@$tables){
	my $tb_size = $table_size->{$tb}[0];
	if (check_var_size($tb_size)) {
		print "[-] Not enough free space in /var partition to optimize the table $tb\n";
		last;
	}
	print "[+] Optimizing table $tb : Size=$tb_size MB\n";
	
	try{
		optimize_table($dbh, $tb);
	}
	catch {
		$logger->warn("[!] Error optimizing table: $@");
		next;
	};
	

	push @optimized_tables, $tb;

}

print "\n[+] Optimized tables:\n";
foreach my $table (@optimized_tables){
	print "$table\n";
}

if (scalar @$tables != scalar @optimized_tables) {
	print "\n[-] Non Optimized tables:\n";
	foreach my $table (@$tables){
		if (!(grep {$_ eq $table} @optimized_tables)){
        		print "$table\n";
		}
	}
}
exit(0);
