# $Id: mylog.pl 58 2007-07-17 07:51:24Z immute $
use strict;
use vars qw($VERSION %IRSSI);

$VERSION = do { q$Rev: 58 $ =~ /(\d+)/; "0.$1"; };
%IRSSI = (
authors     => 'Matt "imMute" Sickler',
contact     => 'imMute {at} mail.msk3.ath.cx',
name        => 'MyLog',
description => 'Logs channel/privmsg activity to a MySQL database',
license     => 'GPLv2',
url         => '',
changed     => '$Date: 2007-07-17 02:51:24 -0500 (Tue, 17 Jul 2007) $',
);

=pod Description
Logs channel/privmsg activity to a MySQL database
=cut

=pod Useage
=cut

use Irssi::Irc;
use Irssi qw/settings_add_str settings_get_str printformat/;
use Data::Dumper; $Data::Dumper::Indent=0; $Data::Dumper::Sortkeys=1;
use DBI;

sub DEBUG (@) { 1 and printformat(MSGLEVEL_CRAP, 'mylog_debug', @_); }

=pod ToDo
=cut

Irssi::theme_register([
    'mylog_debug',         '%P>>%n %_MyLog(DEBUG):%_ $0-',
    'mylog_db_connected',  '%P>>%n %_MyLog:%_ Connected to db ($0:$1 - $2)',
    'mylog_db_no_connect', '%P>>%n %_MyLog:%_ Could not connect to database: $0',
    'mylog_no_setting',    '%P>>%n %_MyLog:%_ You did not specify the $0 setting (mylog_$1)',
    'mylog_loaded',        '%P>>%n %_MyLog:%_ MySQL-Log v$0 loaded',
    'mylog_error',         '%P>>%n %_MyLog:%_ %R%_Error%_ in operation $0:%n $1-',
]);

settings_add_str "mylog", "mylog_db_host",  "localhost";
settings_add_str "mylog", "mylog_db_port",  "3306";
settings_add_str "mylog", "mylog_db_user",  "irssi";
settings_add_str "mylog", "mylog_db_pass",  "irssi";
settings_add_str "mylog", "mylog_db_base",  "irssi";
settings_add_str "mylog", "mylog_levels",   "message chan notice ctcp";
Irssi::settings_add_bool "mylog","mylog_logging",  1;

my $sth_prepare = {
  get_nick_id => q!SELECT `id` FROM `nicks` WHERE `nick` = ?!,
  set_nick_id => q!INSERT INTO `nicks` VALUES(NULL, ?)!,
  
  get_ident_id => q!SELECT `id` FROM `idents` WHERE `ident_name` = ?!,
  set_ident_id => q!INSERT INTO `idents` VALUES(NULL, ?)!,
  
  get_host_id => q!SELECT `id` FROM `hosts` WHERE `hostname` = ?!,
  set_host_id => q!INSERT INTO `hosts` VALUES(NULL, ?)!,
  
  get_user_id => q!SELECT `id` FROM `user_id` WHERE `nick_id` = ? AND `ident_id` = ? AND `host_id` = ?!,
  set_user_id => q!INSERT INTO `user_id` VALUES(NULL, ?, ?, ?)!,
  
  get_chan_id => q!SELECT `id` FROM `channels` WHERE `channel` = ? AND `network` = ?!,
  set_chan_id => q!INSERT INTO `channels` VALUES(NULL, ?, ?)!,
  
  get_quitmsg_id => q!SELECT `id` FROM `quit_messages` WHERE `message` = ?!,
  set_quitmsg_id => q!INSERT INTO `quit_messages` VALUES(NULL, ?)!,
  
  insert_msg => q!INSERT INTO `messages` VALUES(NULL, NOW(), ?, ?, ?)!,
  insert_join => q!INSERT INTO `joins` VALUES(NULL, NOW(), ?, ?)!,
  insert_quit => q!INSERT INTO `quits` VALUES(NULL, NOW(), ?, ?)!,
  insert_part => q!INSERT INTO `parts` VALUES(NULL, ?, ?, ?, NOW())!,
  insert_topic => q!INSERT INTO `topics` VALUES(NULL, NOW(), ?, ?, ?)!,
};
my $dbh;
my $sth = { };

sub dbi_connect {
    my $host = settings_get_str 'mylog_db_host';
    my $port = settings_get_str 'mylog_db_port';
    my $user = settings_get_str 'mylog_db_user';
    my $pass = settings_get_str 'mylog_db_pass';
    my $base = settings_get_str 'mylog_db_base';
    eval {
      $dbh = DBI->connect("DBI:mysql:database=$base;host=$host;port=$port", $user, $pass,
        { PrintError => 0, RaiseError => 1, }) or die;
    };
    if ($@) {
        printformat(MSGLEVEL_CRAP,'mylog_error', 'connect', $DBI::errstr);
        $dbh = $sth = undef;
        return;
    } else {
        printformat(MSGLEVEL_CRAP,'mylog_db_connected', $host, $port, $base);
        setup_sth();
    }
}
sub setup_sth {
    return unless defined $dbh;
    while( my ($name, $sql) = each %$sth_prepare ){
      eval { $sth->{$name} = $dbh->prepare( $sql ) or die; };
      if ( $@ ){  printformat(MSGLEVEL_CRAP, 'mylog_error', 'prepare', $!); $sth->{$name} = undef; }
    }
}

Irssi::command_bind("mylog_reconnect", 'dbi_connect');
Irssi::command_bind("mylog_qst", 'setup_sth');

dbi_connect() if Irssi::settings_get_bool("mylog_logging");
printformat(MSGLEVEL_CRAP, 'mylog_loaded', $VERSION);

sub signal_add {
  my ($type, $name, $sub) = @_;
  Irssi::signal_add( $name, sub {
    return unless Irssi::settings_get_bool("mylog_logging");
    return unless settings_get_str("mylog_levels") =~ $type;
    $sub->(@_);
  });
}

sub sql {
  my ($name, @vars) = @_;
  my $X = $sth_prepare->{$name} =~ /^INSERT/;
  my $S = $sth->{$name};
  my $R;
  eval { $S->execute( @vars ); return if $X; $R = $S->fetchall_arrayref(); };
  if ($@){ printformat(MSGLEVEL_CRAP, 'mylog_error', 'exec', $@, $DBI::errstr); }
  else { return $R; }
}
sub LID () { return $dbh->last_insert_id(undef, undef, undef, undef); }

sub AUTOCREATE () { 1 }

{ #signals
# --- Message

#  "message public",      SERVER_REC, char *msg, char *nick,   char *address, char *target
signal_add "message", "message public", sub {
  my ($server, $msg, $nick, $address, $channel) = @_;
  my ($ident, $host) = split /@/, $server->{userhost}, 2;
  
  my $nick_id = m_nick( $nick, AUTOCREATE );
  my $ident_id = m_ident( $ident, AUTOCREATE );
  my $host_id = m_host( $host, AUTOCREATE );
  my $user_id = m_user( $nick_id, $ident_id, $host_id, AUTOCREATE );
  my $channel_id = m_chan( $channel, $server->{chatnet}, AUTOCREATE );
  
  sql('insert_msg', $user_id, $channel_id, $msg);
};

#  "message own_public",  SERVER_REC, char *msg, char *target
signal_add "message", "message own_public", sub {
  my ($server, $message, $channel) = @_;
  my $nick = $server->{nick};
  my ($ident, $host) = split /@/, $server->{userhost}, 2;
  
  my $nick_id = m_nick( $nick, AUTOCREATE );
  my $ident_id = m_ident( $ident, AUTOCREATE );
  my $host_id = m_host( $host, AUTOCREATE );
  my $user_id = m_user( $nick_id, $ident_id, $host_id, AUTOCREATE );
  my $channel_id = m_chan( $channel, $server->{chatnet}, AUTOCREATE );
  
  sql('insert_msg', $user_id, $channel_id, $message);
};

#  "message private",     SERVER_REC, char *msg, char *nick,   char *address
#  "message own_private", SERVER_REC, char *msg, char *target, char *orig_target

#  "message irc action", SERVER_REC, char *msg, char *nick, char *address, char *target
#  "message irc own_action", SERVER_REC, char *msg, char *target

# --- Chan
#  "message join",     SERVER_REC, char *channel, char *nick,    char *address
signal_add "chan", "message join", sub {
  my ($server, $channel, $nick, $address) = @_;
  my ($ident, $host) = split /@/, $address, 2;
  
  my $nick_id = m_nick( $nick, AUTOCREATE );
  my $ident_id = m_ident( $ident, AUTOCREATE );
  my $host_id = m_host( $host, AUTOCREATE );
  my $user_id = m_user( $nick_id, $ident_id, $host_id, AUTOCREATE );
  my $channel_id = m_chan( $channel, $server->{chatnet}, AUTOCREATE );
  
  sql('insert_join', $user_id, $channel_id);
};

#  "message part",     SERVER_REC, char *channel, char *nick,    char *address, char *reason
signal_add "chan", "message part", sub {
  my ($server, $channel, $nick, $address, $reason) = @_;
  my ($ident, $host) = split /@/, $address, 2;
  
  my $nick_id = m_nick( $nick, AUTOCREATE );
  my $ident_id = m_ident( $ident, AUTOCREATE );
  my $host_id = m_host( $host, AUTOCREATE );
  my $user_id = m_user( $nick_id, $ident_id, $host_id, AUTOCREATE );
  my $channel_id = m_chan( $channel, $server->{chatnet}, AUTOCREATE );
  my $quitmsg_id = m_quitmsg( $reason, AUTOCREATE );
  
  sql('insert_part', $user_id, $channel, $quitmsg_id);
};

#  "message quit",     SERVER_REC, char *nick,    char *address, char *reason
signal_add "chan", "message quit", sub {
  my ($server, $nick, $address, $reason) = @_;
  my ($ident, $host) = split /@/, $address, 2;
  
  my $nick_id = m_nick( $nick, AUTOCREATE );
  my $ident_id = m_ident( $ident, AUTOCREATE );
  my $host_id = m_host( $host, AUTOCREATE );
  my $user_id = m_user( $nick_id, $ident_id, $host_id, AUTOCREATE );
  my $quitmsg_id = m_quitmsg( $reason, AUTOCREATE );
  
  sql('insert_quit', $user_id, $quitmsg_id);
};

#  "message kick",     SERVER_REC, char *channel, char *nick,    char *kicker,  char *address, char *reason
signal_add "chan", "message kick", sub {  # TODO: fix/finish
  my ($server, $channel, $nick, $kicker, $address, $reason) = @_;
  my ($ident, $host) = split /@/, $address, 2;
  
  my $nick_id = m_nick( $nick, AUTOCREATE );
  my $ident_id = m_ident( $ident, AUTOCREATE );
  my $host_id = m_host( $host, AUTOCREATE );
  my $user_id = m_user( $nick_id, $ident_id, $host_id, AUTOCREATE );
  my $channel_id = m_chan( $channel, $server->{chatnet}, AUTOCREATE );
  
};

#  "message nick",     SERVER_REC, char *newnick, char *oldnick, char *address
signal_add "chan", "message nick", sub {  #TODO: finish
  my ($server, $new_nick, $old_nick, $address) = @_;
};

#  "message own_nick", SERVER_REC, char *newnick, char *oldnick, char *address
signal_add "chan", "message own_nick", sub {  #TODO: finish
  my ($server, $newnick, $oldnick, $address) = @_;
};

#  "message irc mode", SERVER_REC, char *channel, char *nick, char *addr, char *mode
signal_add "chan", "message irc mode", sub {  #TODO: finish
  my ($server, $channel, $nick, $address, $mode) = @_;
};

#  "message topic", SERVER_REC, char *channel, char *topic, char *nick, char *address
signal_add "chan", "message topic", sub {
  my ($server, $channel, $topic, $nick, $address) = @_;
  my ($ident, $host) = split /@/, $address, 2;
  
  my $nick_id = m_nick( $nick, AUTOCREATE );
  my $ident_id = m_ident( $ident, AUTOCREATE );
  my $host_id = m_host( $host, AUTOCREATE );
  my $user_id = m_user( $nick_id, $ident_id, $host_id, AUTOCREATE );
  my $channel_id = m_chan( $channel, $server->{chatnet}, AUTOCREATE );
  
  sql('insert_topic', $channel_id, $user_id, $topic);
}

# --- Notice
#  "message irc notice", SERVER_REC, char *msg, char *nick, char *address, char *target

#  "message irc own_notice", SERVER_REC, char *msg, char *target

# --- CTCP
#  "message irc ctcp", SERVER_REC, char *cmd, char *data, char *nick, char *address, char *target

#  "message irc own_ctcp", SERVER_REC, char *cmd, char *data, char *target

#  "ctcp reply", SERVER_REC, char *args, char *nick, char *addr, char *target


}

{ # relational helpers

sub m_nick {
  my ($nick, $C) = @_;
  my $R = sql('get_nick_id', $nick)->[0]; # dangerous if auto is off and it returns undef!
  if (ref $R) { return $R->[0];}
  elsif ($C)  { sql('set_nick_id', $nick); return LID(); }
  else        { return undef; }
}

sub m_ident {
  my ($ident, $C) = @_;
  my $R = sql('get_ident_id', $ident)->[0]; # dangerous if auto is off and it returns undef!
  if (ref $R) { return $R->[0]; }
  elsif ($C)  { sql('set_ident_id', $ident); return LID(); }
  else { return undef; }
}

sub m_host {
  my ($host, $C) = @_;
  my $R = sql('get_host_id', $host)->[0]; # dangerous if auto is off and it returns undef!
  if (ref $R) { return $R->[0]; }
  elsif ($C)  { sql('set_host_id', $host); return LID(); }
  else { return undef; }
}

sub m_user {
  my ($nick, $ident, $host, $C) = @_;
  my $R = sql('get_user_id', $nick, $ident, $host)->[0]; # dangerous if auto is off and it returns undef!
  if (ref $R) { return $R->[0]; }
  elsif ($C)  { sql('set_user_id', $nick, $ident, $host); return LID(); }
  else { return undef; }
}

sub m_chan {
  my ($chan, $net, $C) = @_;
  my $R = sql('get_chan_id', $chan, $net)->[0]; # dangerous if auto is off and it returns undef!
  if (ref $R) { return $R->[0]; }
  elsif ($C)  { sql('set_chan_id', $chan, $net); return LID(); }
  else { return undef; }
}

sub m_quitmsg {
  my ($reason, $C) = @_;
  my $R = sql('get_quitmsg_id', $reason)->[0]; # dangerous if auto is off and it returns undef!
  if (ref $R) { return $R->[0]; }
  elsif ($C)  { sql('set_quitmsg_id', $reason); return LID(); }
  else { return undef; }
}

}