# Logs URLs this script is just a hack. hack it to suit you
# if you want to.
#
# It also performs an a&o[tm] check to see if an URL has already been posted
# somewhere.
#
# table format;
#+-----------+---------------+------+-----+---------+-------+
#| Field     | Type          | Null | Key | Default | Extra |
#+-----------+---------------+------+-----+---------+-------+
#| inserttime| timestamp(14) | YES  |     | NULL    |       |
#| nick      | char(10)      | YES  |     | NULL    |       |
#| target    | char(255)     | YES  |     | NULL    |       |
#| line      | char(255)     | YES  |     | NULL    |       |
#| url       | varchar(255)  | YES  |     | NULL    |       |
#+-----------+---------------+------+-----+---------+-------+



use DBI;
use Irssi;
use Irssi::Irc;

use vars qw($VERSION %IRSSI);

$VERSION = "1.1";
%IRSSI = (
        authors     => "Riku Voipio, lite, Frank \"yogan\" Blendinger",
        contact     => "riku.voipio\@iki.fi",
        name        => "myssqlurllogger",
        description => "logs url's to mysql database",
        license     => "GPLv2",
        url         => "http://nchip.ukkosenjyly.mine.nu/irssiscripts/",
    );

$dsn = 'DBI:mysql:localhost';
$db_user_name = 'username';
$db_password = '*****';

sub cmd_logurl {
	my ($server, $line, $nick, $mask, $target) = @_;
        if (($line =~ /((http[s]?|ftp):\/\/[^>\s]+)/)
                or ($line =~ /(www\.[^>\s]+)/)) {
            $url = $1;
            ao_check($url, $nick, $target);
            #Irssi:print("calling ao_check($url, $nick, $target)"); 
            db_insert($nick, $target, $line, $url);
        }
	return 1;
}

sub ao_check {
    my ($url, $nick, $channel) = @_;
    my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

    my $posts = $dbh->selectrow_array("SELECT COUNT(*) FROM urlevent"
        . " WHERE url=" . $dbh->quote($url) . ";");

    if ($posts > 0) {
        $posts_chan = $dbh->selectrow_array("SELECT COUNT(*) FROM urlevent"
            . " WHERE url=" . $dbh->quote($url)
            . " AND target=" . $dbh->quote($channel) . ";");
        if ($posts_chan > 0) {
            # posted on the same channel
            my $sql_chan="SELECT inserttime, nick FROM urlevent WHERE url="
                . $dbh->quote($url) . " AND target=" . $dbh->quote($channel)
                . " ORDER BY inserttime DESC;";
            my $sth_chan = $dbh->prepare($sql_chan);
            $sth_chan->execute();
            my @row = $sth_chan->fetchrow_array();
            Irssi::print("URL $url (by $nick on $channel) is a&o! It was last"
                . " posted on ". $row[0] . " by " . $row[1] . " in the VERY"
                . " SAME channel ($posts_chan in $channel / $posts total)");
            $sth_chan->finish;
        } else {
            # posted on a different channel
            my $sql = "SELECT inserttime, nick, target FROM urlevent WHERE url="
                . $dbh->quote($url) . " ORDER BY inserttime DESC;";
            my $sth = $dbh->prepare($sql);
            $sth->execute();
            my @row = $sth->fetchrow_array();
            Irssi::print("URL $url (by $nick on $channel) is a&o! It was last"
                . " posted on " . $row[0] . " by " . $row[1] . " in " . $row[2]
                . " ($posts_chan in $channel / $posts total)");
            $sth->finish;
        }
    }
    $dbh->disconnect();
}

sub cmd_own {
	my ($server, $data, $target) = @_;
	return cmd_logurl($server, $data, $server->{nick}, "", $target);
}
sub cmd_topic {
	my ($server, $target, $data, $nick, $mask) = @_;
	return cmd_logurl($server, $data, $nick, $mask, $target);
}

sub db_insert {
	my ($nick, $target, $line, $url)=@_;
	my $dbh = DBI->connect($dsn, $db_user_name, $db_password);
	my $sql="insert into urlevent (inserttime, nick, target, line, url)"
            . " values (NOW()".",". $dbh->quote($nick) . ","
            . $dbh->quote($target) . "," . $dbh->quote($line) . ","
            . $dbh->quote($url) . ")";
	my $sth = $dbh->do($sql);
	$dbh->disconnect();
}

Irssi::signal_add_last('message public', 'cmd_logurl');
Irssi::signal_add_last('message own_public', 'cmd_own');
Irssi::signal_add_last('message topic', 'cmd_topic');

Irssi::print("URL logger by lite/nchip/yogan loaded.");