#!/usr/bin/perl

use strict;

use Sys::Hostname;
use Getopt::Std;

use lib '/app/tools/common/lib';
use Common::Consts;
use Common::RSDB;
use Common::DB::Item::Client;
use Common::RSApp;

my %opt;
getopts( 'as:f:h:', \%opt );
my $hostName = $opt{h} || hostname();
$hostName = lc $hostName;
$hostName =~ s/^(.+?)\..*$/$1/;

my $SQL;

if ( defined $opt{s} ) {
    $SQL = $opt{s};
    chomp($SQL);
} else {
    _usage() unless $opt{f};
    open( SQL, $opt{f} ) || die "can't read file '$opt{f}'\n";
    $SQL = join( '', <SQL> );
    close(SQL);
}

die "$0 is meant only for SELECT queries\n" if ( $SQL =~ m/.*?\b(insert|update|delete|drop|truncate)\b/si );

print STDERR "\nYou are about to run the following query against EVERY client db:\n\n$SQL\n\nARE YOU SURE THIS IS OK? (y/n):";
unless ( <STDIN> =~ m/^y/i ) {
    print STDERR "cancelled\n";
    exit();
}

print "\n$SQL\n\n";

foreach my $clientID ( sort { $a <=> $b } keys %Common::RSDB::CLIENT_DB ) {
    my $app = Common::RSApp->new( clientID => $clientID );

    if ( !$opt{a} ) {
        next unless ( $Common::RSDB::CLIENT_DB{$clientID}{host_id} eq $hostName );
    }

    my $dbName   = $Common::RSDB::CLIENT_DB{$clientID}{db_name};
    my $server   = $Common::RSDB::CLIENT_DB{$clientID}{server};
    my $userName = $Common::RSDB::CLIENT_DB{$clientID}{username};
    my $password = $Common::RSDB::CLIENT_DB{$clientID}{password};
    next unless ( $dbName =~ m/^C_/ );

    my $client = Common::DB::Item::Client->Lookup( client_id => $clientID );
    my $clientNameClean = $client->client_name_clean();

    my $result = $opt{s} ? `mysql -u$userName -p$password -h$server $dbName -e '$opt{s}' 2>&1` : `mysql -uroot $dbName < $opt{f} 2>&1`;

    next unless length $result;

    printf( "%s / %d / %s\n%s\n", $dbName, $clientID, $clientNameClean, $result );
}

sub _usage {
    die <<EOD;
usage: $0 -f <path/to/sql/file> or $0 -s 'single line sql query' [-h <hostname> (when running on non-prod boxes)] [-a (to run on _all_ databases)]

client_db_query will read your sql query command file and run it for each client specified in %Common::RSDB::CLIENT_DB
EOD
}