#!/usr/bin/env perl

use strict;
use Getopt::Long;
use Data::Dumper;
use DBI;

#------------------------------------------------------------------------------------
# Default values for connection params
my $db_host = "localhost";
my $db_port = 3306;
my $db_user = "root";
my $db_password = "";
my $db_name = '';

# Test mode flag
my $test_mode = 0;
my $db_all = 0;
my $help = 0;

my $pk_type = 'key';

# Parse command line
GetOptions("host=s" => \$db_host,
           "port=i" => \$db_port,
           "user=s" => \$db_user,
           "password=s" => \$db_password,
           "db=s" => \$db_name,
           "all" => \$db_all,
           "test-mode" => \$test_mode,
           "help" => \$help,
           "pk-type=s", \$pk_type);

# Show help if requested
PrintUsage() if ($help);

# Clear db name in case of --all option specified
$db_name = '' if ($db_all);

# Check pk_type
if ($pk_type ne 'none' && $pk_type ne 'primary' && $pk_type ne 'key') {
    print "Invalid primary_key_type: '$pk_type'. Allowed values are: none, primary or key\n";
    exit(1);
}

#------------------------------------------------------------------------------------
# Connect to database
our $dbh = ConnectToDb($db_host, $db_port, $db_user, $db_password, $db_name);

# Compose databases list
my @databases = $db_all ? GetDatabasesList() : ($db_name);

# Process all databases in list
foreach my $db_name (@databases) {
    next if ($db_name eq '' || $db_name eq 'information_schema');
    print "-----------------------------------------------------------------\n";
    print "DATABASE: $db_name\n";
    ProcessDB($db_name);
}

exit(0);

#------------------------------------------------------------------------------------
sub PrintUsage() {
    print "Usage: $0 [options]\n";
    print "Where options are:\n";
    print "  --host <host> - specifies host to connect.\n";
    print "  --port <port> - specifies port to connect.\n";
    print "  --user <user> - user name for MySQL server.\n";
    print "  --password <pass> - password for MySQL server.\n";
    print "  --db <name> - database to process.\n";
    print "  -A | --all - process all databases.\n";
    print "  -t | --test-mode - do not modify tables, just print SQL statements.\n";
    print "  -pk | --pk-type <none|primary|key> - What keys to create for 'id' fields. Default: key.\n";
    print "  --help - this help.\n\n";
    exit(0);
}

#------------------------------------------------------------------------------------
sub ConnectToDb($$$$$) {
    my ($db_host, $db_port, $db_user, $db_password, $db_name) = @_;

    # Construct DSN
    my $dsn = "DBI:mysql:";
    $dsn .= "database=$db_name;" if ($db_name);
    $dsn .= "host=$db_host;port=$db_port";

    # Perform connection
    return DBI->connect($dsn, $db_user, $db_password, { RaiseError => 1 });
}

#------------------------------------------------------------------------------------
sub GetDatabasesList() {
    return GetSingleColResult("SHOW DATABASES", 1);
}

#------------------------------------------------------------------------------------
sub ExecuteQuery($) {
    my $query = shift;
    eval {
      $dbh->do($query)
    };
    print $@ if ( $@ );
}

#------------------------------------------------------------------------------------
sub GetListResult($) {
    my $query = shift;

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my @res = ();
    while (my $row = $sth->fetchrow_hashref()) {
        push @res, $row;
    }

    return @res;
}

#------------------------------------------------------------------------------------
sub GetSingleColResult {
    my $query = shift;

    my $sth = $dbh->prepare($query);
    $sth->execute();

    my @res = ();
    while (my $row = $sth->fetchrow_arrayref()) {
        push @res, $row->[0];
    }

    return @res;
}

#------------------------------------------------------------------------------------
sub GetTableIdFields($) {
    my $table = shift;
    my @fields = GetListResult("DESCRIBE $table");

    my @field_names = ();
    foreach my $field (@fields) {
        push(@field_names, $field->{Field}) if ($field->{Field} =~ /_id$/ || $field->{Field} eq 'id');
    }

    return @field_names;
}

#------------------------------------------------------------------------------------
sub GetTableIdIndexes($) {
    my $table = shift;
    my @indexes = GetListResult("SHOW INDEXES FROM $table");

    my @indexed_fields = ();
    foreach my $index (@indexes) {
        if ($index->{Seq_in_index} == 1 && ($index->{Column_name} =~ /_id$/ || $index->{Column_name} eq 'id')) {
            push(@indexed_fields, $index->{Column_name});
        }
    }

    return @indexed_fields;
}

#------------------------------------------------------------------------------------
sub CheckFieldIndex($$$) {
    my ($table, $field, $id_indexes) = @_;

    foreach my $indexed_field (@$id_indexes) {
        return if ($field eq $indexed_field);
    }

    print "Field '$field' in table '$table' needs index!\n";
    my $type = '';
    if ($field eq 'id') {
        if ($pk_type eq 'none') {
            print "Skipping primary key by request\n";
            return;
        }
        $type = 'PRIMARY' if ($pk_type eq 'primary');
    }

    my $sql = "ALTER TABLE $table ADD $type KEY ey_${field}_key($field)";
    if ($test_mode) {
        print "TEST MODE: $sql\n";
    } else {
        print "Adding index 'ey_${field}_key($field)' to the table $table...";
        ExecuteQuery($sql);
        print "Ok!\n";
	sleep 5;
    }
}

#------------------------------------------------------------------------------------
sub ProcessDB($) {
    my $db_name = shift;

    ExecuteQuery("USE $db_name");

    my @tables = GetSingleColResult("SHOW TABLES");

    foreach my $table (@tables) {        
        print "Processing table: '$table'...\n";
        my @fields = GetTableIdFields($table);
        my @id_indexes = GetTableIdIndexes($table);

        foreach my $field (@fields) {
            CheckFieldIndex($table, $field, \@id_indexes);
        }
        print "\n";
    }
}