/usr/bin/env perl

package foobot;
use base qw( Bot::BasicBot );

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=foo.sqlt",
    "", "")
  or die "Couldn't connect to database!\n$!\n";

sub said {
    my ($self, $msg) = @_;

    if ($msg->{body} =~ /^!add\s+\S+/i) {
        my ($quote) = $msg->{body} =~ /^!add\s+(.+)$/;
        my $nick = $msg->{who};
        my $sth = $dbh->prepare("INSERT INTO quotes VALUES (?, ?)");
        $sth->execute($nick, $quote);
        $msg->{body} = undef;
    }
    elsif ($msg->{body} =~ /^!(?:query|search)\s+\S+/i) {
        my ($query) = $msg->{body} =~ /^!(?:query|search)\s+(.+)$/;
        $query = "%$query%";
        my $sth
          = $dbh->prepare(
            "SELECT ROWID,who,quote FROM quotes WHERE quote LIKE ? OR who LIKE ?"
          );
        $sth->execute($query, $query);
        my @row;
        while (@row = $sth->fetchrow_array()) {
            my ($rowID, $who, $quote) = @row;
            $msg->{body} = "$quote (ID=$rowID, added by $who)\n";
            $self->say($msg);
        }
        $msg->{body} = undef;
    }
    elsif ($msg->{body} =~ /^!delete\s+\d+$/i) {
        my ($rowID) = $msg->{body} =~ /^!delete\s+(\d+)$/;
        my $sth    = $dbh->prepare("DELETE FROM quotes WHERE ROWID = ?");
        my $retval = $sth->execute($rowID);
        $msg->{body}
          = ($retval ne '0E0') ? "Deleted quote with ID $rowID\n" : undef;
    }
    elsif ($msg->{body} =~ /^!cya/i) {
        $dbh->disconnect();
        exit 0;
    }
    else {
        $msg->{body} = undef;
    }

    if ($msg->{body}) {
        $self->say($msg);
    }

    return;
}

1;

foobot->new(
    server   => "irc.euirc.net",
    port     => "6667",
    channels => ['#fooooo'],

    nick      => "foo",
    alt_nicks => ["barbot"],
    username  => "asdfjkloe",
    name      => "foobar",

    ignore_list => [],

#    charset => "latin-1",    # charset the bot assumes the channel is using
)->run();

use DBI;
my $dbh=DBI->connect("dbi:SQLite:dbname=foo.sqlt", "", "");
$dbh->do("CREATE TABLE quotes (who, quote)");
$dbh->commit();
$dbh->disconnect();

use Encode qw(encode_utf8 decode_utf8);
...
$sth->execute($nick, encode_utf($quote));