#!/usr/bin/perl 

#*
#* GnuDialer - Complete, free predictive dialer
#*
#* Complete, free predictive dialer for contact centers.
#*
#* Copyright (C) 2008, GnuDialer Project
#*
#* Richard Lyman <richard@dynx.net>
#*
#* This program is free software, distributed under the terms of
#* the GNU General Public License.
#*

use DBI;
$drh = DBI->install_driver('mysql') or die("Unable to install driver");
$dbh = DBI->connect("DBI:mysql:dialer",'dialer','1234') or die("Unable to connect");

&what_dbs;

$dbh->disconnect;
####################################################################################################
sub what_dbs {
	my $ret = 0;
	my $sth = $dbh->prepare("SHOW TABLES");

	$sth->execute or print "query: what_dbs failed";
	while(@table = $sth->fetchrow_array) {
		print "$table[0]: ";
		if ($table[0] ne 'cdr' && $table[0] ne 'CDR' &&$table[0] ne 'dnc' && $table[0] ne 'DNC') {
			print "processing\n";
			drop_indexes($table[0]);
			rebuild_indexes($table[0]);
		} else {
			print "skipping\n";
		}
	}
	$sth->finish;
	return $ret;
}
###################################################################################################
sub rebuild_indexes {
	local($thetable) = @_;

	my $sth4 = "";
	
#        $sth4 = $dbh->prepare("ALTER TABLE `" . $thetable . "` CHANGE `id` `id` int(8)");
#        $sth4->execute or print "query: what_dbs failed";

#        $sth4 = $dbh->prepare("ALTER IGNORE TABLE `" . $thetable . "` DROP INDEX `PRIMARY`");
#        $sth4->execute or print "query: what_dbs failed";

#        $sth4 = $dbh->prepare("ALTER TABLE `" . $thetable . "` ADD PRIMARY KEY auto_increment (`id`)");
#        $sth4->execute or print "query: what_dbs failed";

        $sth4 = $dbh->prepare("ALTER TABLE `" . $thetable . "` CHANGE `id` `id` INT( 8 ) NOT NULL AUTO_INCREMENT");
	print "changing index: id not null auto_increment\n";
	$sth4->execute or die("Unable to excute query");

	$sth4 = $dbh->prepare("ALTER IGNORE TABLE `" . $thetable . "` ADD INDEX (`attempts`), ADD INDEX (`pickups`), ADD INDEX (`abandons`), ADD INDEX (`disposition`), ADD INDEX (`phone`), ADD INDEX (`lastupdated`), ADD INDEX (`tzl` DESC)");
	print "adding indexes: attempts,pickups,abandons,disposition,phone,lastupdated,tzl\n";
	$sth4->execute or die("Unable to excute query");

	$sth4 = $dbh->prepare("ALTER IGNORE TABLE `" . $thetable . "` MODIFY COLUMN `lastupdated` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP");
	print "modifing column: lastupdated\n";
	$sth4->execute or die("Unable to excute query");

	$sth4->finish;

}

####################################################################################################
sub drop_indexes {
	local($thetable) = @_;
	my $sth2 = $dbh->prepare("SHOW INDEXES FROM `" . $thetable . "`");

	$sth2->execute or print "query: what_dbs failed";
	while(@theindex = $sth2->fetchrow_array) {
	        print "checking key name $theindex[2] ";
		if ($theindex[2] ne 'PRIMARY') {
			drop_index($thetable,$theindex[2]);
			print "dropped ";
		} else {
			print "skipped ";
		}
		
		print "\n";
	}
	$sth2->finish;

}

####################################################################################################
sub drop_index {
	local($thetablename,$theindexname) = @_;
	my $sth3 = $dbh->prepare("ALTER IGNORE TABLE `" . $thetablename . "` DROP INDEX `" . $theindexname . "`");
	$sth3->execute or die("Unable to excute query");
	$sth3->finish;

}