#!/local/bin/perl

use strict;
no strict "vars";
use warnings;

use Getopt::Std;
use Term::ReadKey;
use DBI;
use DBD::mysql;

# Use if DBI can't find socket
# $ENV{MYSQL_UNIX_PORT} = "/var/lib/mysql/mysql.sock";

if (!getopts('u:h:s:f:H', \%opts))
{
    &usage;
    exit;
}

sub usage {
    print "warmath-export.pl -u <db-user> -h <db-host> -s <system(s)> -f <output file>\n";
    print "  -u <db-user>     : MySQL username.\n";
    print "  -h <db-host>     : MySQL hostname, typically 'localhost'. Use ':<port>' if needed.\n";
    print "  -s <system(s)>   : IDs of the Warmath systems you want to extract.\n";
    print "                     Format: '(X[,Y]*)', examples: '(1)' or '(1,2)'\n";
    print "  -f <output file> : File you want to write to.\n";
    print "\n";
    print "If DBI can't find the socket to use for localhost, export the ENV variable\n";
    print "'MYSQL_UNIX_PORT' to point to it's location:\n\n";
    print "     export MYSQL_UNIX_PORT /var/lib/mysql/mysql.sock\n\n";
    print "Little or no hardening has been done to this script. Use at own risk.\n";
    print "Will correctly prompt for database password.\n";
}

if (exists $opts{ 'H' }) {
    &usage;
    exit;
}

# len is constant at this point
if (keys( %opts ) != 4) {
    print "ERROR: Missing parameters!\n\n";
    &usage;
    exit;
}

# Safetly read DB password
print "Enter your password: ";
ReadMode 'noecho';
$password = ReadLine 0;
chomp $password;
ReadMode 'normal';
print "\n";

# Construct parameters
my $dsn = "dbi:mysql:warmath:$opts{'h'}";
my $db_user_name = "$opts{'u'}";
my $my_system = "$opts{'s'}"; 
my $dbh = DBI->connect($dsn, $db_user_name, $password);

open(O, ">$opts{'f'}");

# wm_attendees
my $sth = $dbh->prepare(qq{
    select attendee_character, attendee_credit, attendee_satout,
           attendee_system_id
    from wm_attendees
    where attendee_system_id in $my_system
    });
$sth->execute();
print O "wm_attendees {\n";
while (my ($char, $cred, $sat, $system) =
  $sth->fetchrow_array())
{
    print O "$char:;:$cred:;:$sat:;:$system\n";
}
print O "}\n\n";


# wm_bosses
$sth = $dbh->prepare(qq{
    select boss_id, boss_name, boss_name_short, 
           boss_instance, boss_trash
    from wm_bosses        
    });
$sth->execute();
print O "wm_bosses {\n";
while (my ($id, $name, $short, $instance, $trash) = 
  $sth->fetchrow_array())
{
    print O "$id:;:$name:;:$short:;:$instance:;:$trash\n";
}
print O "}\n\n";


# wm_boss_values
$sth = $dbh->prepare(qq{
    select boss_value_boss, boss_value_value, 
           boss_value_system_id
    from wm_boss_values
    where boss_value_system_id in $my_system
   });
$sth->execute();
print O "wm_boss_values {\n";
while (my ($boss, $value, $system) =
  $sth->fetchrow_array())
{
    print O "$boss:;:$value:;:$system\n";
}
print O "}\n\n";


# wm_characters
$sth = $dbh->prepare(qq{
    select character_id, character_name, character_class,
           character_system_id, character_dkp_earned,
           character_dkp_satout, character_dkp_spent,
           character_dkp, character_approved, character_admin,
           character_forum_id, character_active, character_hidden
    from wm_characters
    where character_system_id in $my_system
   });
$sth->execute();
print O "wm_characters {\n";
while (my ($id, $name, $class, $system, $earned, $sat, $spent,
           $dkp, $app, $admin, $forum, $act, $hidden) =
  $sth->fetchrow_array())
{
    print O "$id:;:$name:;:$class:;:$system:;:$earned:;:$sat:;:$spent:;:$dkp:;:$app:;:$admin:;:$forum:;:$act:;:$hidden\n";
}
print O "}\n\n";

# wm_credits
$sth = $dbh->prepare(qq{
    select credit_id, credit_boss, credit_date, credit_raid,
           credit_system_id
    from wm_credits
    where credit_system_id in $my_system
   });
$sth->execute();
print O "wm_credits {\n";
while (my ($id, $boss, $date, $raid, $system) =
       $sth->fetchrow_array()) {
    print O "$id:;:$boss:;:$date:;:$raid:;:$system\n";
    }
print O "}\n\n";


# wm_instances
$sth = $dbh->prepare(qq{
    select instance_id, instance_name, instance_tier
    from wm_instances
});
$sth->execute();
print O "wm_instances {\n";
while (my ($id, $name, $tier) =
  $sth->fetchrow_array()) {
    print O "$id:;:$name:;:$tier\n";
    }
print O "}\n\n";


# wm_items
$sth = $dbh->prepare(qq{
    select item_id, item_boss
    from wm_items
});
$sth->execute();
print O "wm_items {\n";
while (my ($id, $boss) =
       $sth->fetchrow_array()) {
    print O "$id:;:$boss\n";
    }
print O "}\n\n";


# wm_item_values
$sth = $dbh->prepare(qq{
    select item_id, item_value, item_system_id
    from wm_item_values
    where item_system_id in $my_system
});
$sth->execute();
print O "wm_item_values {\n";
while (my ($id, $value, $system) =
       $sth->fetchrow_array()) {
    print O "$id:;:$value:;:$system\n";
    }
print O "}\n\n";


# wm_logs
$sth = $dbh->prepare(qq{
    select log_id, log_character, log_type, log_description,
           log_datetime, log_system_id, log_admin
    from wm_logs
    where log_system_id in $my_system
});
$sth->execute();
print O "wm_logs {\n";
while (my ($id, $char, $type, $desc, $date, $system,
	   $admin) =
       $sth->fetchrow_array()) {
    print O "$id:;:$char:;:$type:;:$desc:;:$date:;:$system:;:$admin\n";
    }
print O "}\n\n";


# wm_loots
$sth = $dbh->prepare(qq{
    select loot_id, loot_character, loot_item, loot_credit, loot_bid, loot_level, 
           loot_system_id
    from wm_loots
    where loot_system_id in $my_system
});
$sth->execute();
print O "wm_loots {\n";
while (my ($id, $char, $item, $cred, $bid, $level, $system) =
       $sth->fetchrow_array()) {
    print O "$id:;:$char:;:$item:;:$cred:;:$bid:;:$level:;:$system\n";
    }
print O "}\n\n";


# wm_raids
$sth = $dbh->prepare(qq{
    select raid_id, raid_date, raid_instance, raid_system_id
    from wm_raids
    where raid_system_id in $my_system
});
$sth->execute();
print O "wm_raids {\n";
while (my ($id, $date, $inst, $system) =
       $sth->fetchrow_array()) {
    print O "$id:;:$date:;:$inst:;:$system\n";
    }
print O "}\n\n";


# wm_scheduled_raids
$sth = $dbh->prepare(qq{
    select scheduled_raid_id, scheduled_raid_instance, scheduled_raid_datetime, 
           scheduled_raid_comments, scheduled_raid_system_id
    from wm_scheduled_raids
    where scheduled_raid_system_id in $my_system
});
$sth->execute();
print O "wm_scheduled_raids {\n";
while (my ($id, $inst, $date, $comment, $system) =
       $sth->fetchrow_array()) {
    print O "$id:;:$inst:;:$date:;:$comment:;:$system\n";
    }
print O "}\n\n";


# wm_scheduled_raid_bosses
$sth = $dbh->prepare(qq{
    select scheduled_raid, scheduled_raid_boss, scheduled_raid_system_id
    from wm_scheduled_raid_bosses
    where scheduled_raid_system_id in $my_system
});
$sth->execute();
print O "wm_scheduled_raid_bosses {\n";
while (my ($raid, $boss, $system) =
       $sth->fetchrow_array()) {
    print O "$raid:;:$boss:;:$system\n";
    }
print O "}\n\n";


# wm_scheduled_signups
$sth = $dbh->prepare(qq{
    select scheduled_signup_character, scheduled_signup_raid, 
           scheduled_signup_comments, scheduled_signup_system_id
    from wm_scheduled_signups
    where scheduled_signup_system_id in $my_system
});
$sth->execute();
print O "wm_scheduled_signups {\n";
while (my ($char, $raid, $comment, $system) =
       $sth->fetchrow_array()) {
    print O "$char:;:$raid:;:$comment:;:$system\n";
    }
print O "}\n\n";


# wm_systems
$sth = $dbh->prepare(qq{
    select system_id, system_name, system_server, system_faction, system_config, 
           system_announcement, system_locked, system_tier
    from wm_systems
    where system_id in $my_system
});
$sth->execute();
print O "wm_systems {\n";
while (my ($id, $name, $server, $fact, $config, $ann, $lock, $tier) =
       $sth->fetchrow_array()) {
    print O "$id:;:$name:;:$server:;:$fact:;:$config:;:$ann:;:$lock:;:$tier\n";
    }
print O "}\n\n";


# wm_wishes
$sth = $dbh->prepare(qq{
    select wish_id, wish_item, wish_character, wish_level, wish_system_id
    from wm_wishes
    where wish_system_id in $my_system
});
$sth->execute();
print O "wm_wishes {\n";
while (my ($id, $item, $char, $lvl, $system) =
       $sth->fetchrow_array()) {
    print O "$id:;:$item:;:$char:;:$lvl:;:$system\n";
    }
print O "}\n\n";