use Irssi;
use DBI;
use Date::Format;

use vars qw($VERSION %IRSSI);

$VERSION = "2.0";
%IRSSI = (
	  authors     => "Pasi Lallinaho",
	  contact     => "open@knome.fi",
	  name        => "MySQL Database backuper",
	  description => "Backups your MySQL-databases with irssi command.",
	  license     => "GPL v2"
	);

reset;

Irssi::settings_add_str( 'scripts', 'db_server', 'localhost' );
Irssi::settings_add_str( 'scripts', 'db_username', '' );
Irssi::settings_add_str( 'scripts', 'db_password', '' );
Irssi::settings_add_str( 'scripts', 'db_database_name', '' );

Irssi::settings_add_str( 'scripts', 'db_backup_fileprefix', 'db_' );
Irssi::settings_add_str( 'scripts', 'db_backup_directory', '' );
Irssi::settings_add_str( 'scripts', 'db_backup_dateformat', '%Y-%m-%d' );
Irssi::settings_add_str( 'scripts', 'db_backup_fileextension', '.db' );

sub backup {

    my( $db_serv, $db_user, $db_pass, $db_name );
    if( Irssi::settings_get_str('db_server') && Irssi::settings_get_str('db_username') && Irssi::settings_get_str('db_database_name') && ( Irssi::settings_get_str('db_password') || @_ ) ) {

	$dbs = Irssi::settings_get_str('db_database_name');
	@databases = split( /,/, $dbs );

	foreach( @databases ) {
	    $error = &backupdatabase( $_, @_ );
	    if( $error ) {
		Irssi::print $error;
	    }
	}
    }
    else {
	Irssi::print "Check your settings! (/set db_)";
      }
}

sub backupdatabase {

    my( $db_pass );
    $dsn = 'DBI:mysql:' . $_[0] . ':' . Irssi::settings_get_str('db_server');

    # check is password is set from parameter
    if( $_[1] ) { $db_pass = $_[1]; }
    else { $db_pass = Irssi::settings_get_str('db_password'); }

    # open the file for writing
    $backupfile = Irssi::settings_get_str('db_backup_directory') . Irssi::settings_get_str('db_backup_fileprefix') . $_ . "_" . time2str( Irssi::settings_get_str('db_backup_dateformat'), time() ) . Irssi::settings_get_str('db_backup_fileextension');
    open( BACKUP, ">$backupfile" );

    # get the names of the tables from database
    my $dbh = DBI->connect( $dsn, Irssi::settings_get_str('db_username'), $db_pass, { PrintError => 0 } ) || return "MySQL Error: $DBI::errstr - check your settings (/set db_)";
    my $sql = "SHOW TABLES";
    my $sth = $dbh->prepare( $sql );
    $sth->execute();

    my( $table );
    $sth->bind_columns( undef, \$table );

    my( @tables );

    while( $sth->fetch() ) {
        push( @tables, $table );
    }

    $sth->finish();
    $dbh->disconnect();

    foreach $table( @tables ) {

        # get the 'creating parameters' for all tables
        my $dbh = DBI->connect( $dsn, Irssi::settings_get_str('db_username'), $db_pass );
        my $sql = "SHOW CREATE TABLE " . $table;
        my $sth = $dbh->prepare( $sql );
        $sth->execute();

        my( $table, $table_create );
        $sth->bind_columns( undef, \$table, \$table_create );

        while( $sth->fetch() ) {
            print BACKUP $table_create . "\n\n";
        }

        $sth->finish();
        $dbh->disconnect();

        # get the data !
        my $dbh = DBI->connect( $dsn, Irssi::settings_get_str('db_username'), $db_pass );
        my $sql = "SELECT * FROM " . $table;
        my $sth = $dbh->prepare( $sql );
        $sth->execute();

        my $rows = $sth->fetchall_arrayref;

        my( $i, $j );
        for $i ( 0 .. $#{$rows} ) {
            $rows->[$i][0] =~ s/'/\\'/gi;
            print BACKUP "INSERT INTO `" . $table . "` VALUES( '" . $rows->[$i][0];

            for $j ( 1 .. $#{$rows->[$i]} ) {
                $rows->[$i][$j] =~ s/'/\\'/gi;
                print BACKUP "', '" . $rows->[$i][$j];
            }

            print BACKUP "');\n";
        }

        print BACKUP "\n\n";

        $sth->finish();
        $dbh->disconnect();
    }

    close( BACKUP );

    Irssi::print "Your database [" . $_[0] . "] is now backuped.";
}

Irssi::command_bind('backup_db', \&backup );