#!/usr/bin/python


from twisted.words.protocols import irc
from twisted.internet import reactor, protocol
from twisted.python import log
import time, sys, random, MySQLdb, re, datetime

class dB():

    def __init__(self, mysqlhost, mysqluser, mysqlpass, db):
        self.db = MySQLdb.connect(host=mysqlhost, user=mysqluser, passwd=mysqlpass, db=db)
        self.cursor = self.db.cursor()

    def add(self, quote, date, addedby):
        self.cursor.execute("INSERT INTO quotes (quote,date_added,added_by) VALUES ('%s', '%s', '%s')" %(quote, date, addedby))

    def randquoteid(self):
        self.cursor.execute("SELECT id FROM quotes")
        rows = self.cursor.rowcount
        if rows:
            id = random.randrange(1, rows + 1)
            return id
        else:
            return False

    def randquote(self):
        if self.randquoteid():
            self.cursor.execute("SELECT quote,date_added FROM quotes WHERE id='%s'" %self.randquoteid())
            result = self.cursor.fetchone()
            return result
        else:
            return False

    def url(self, url, date, addedby):
        existQuery = self.cursor.execute("SELECT url,date_added,added_by FROM urls WHERE url LIKE %s", ("%"+url+"%"))
        exist = self.cursor.fetchone()
        if not exist:
            self.cursor.execute("INSERT INTO urls (url,date_added,added_by) VALUES ('%s', '%s', '%s')" %(url, date, addedby))
            return False
        else:
            url = exist[0]
            date = exist[1]
            by = exist[2]
            msg = "Url: %s first seen from %s at (%s)" %(url, by, date.split(" ", 1)[0])
            return msg

    def close(self):
        self.db.close()

class quoteBot(irc.IRCClient):

    nickname = ""
    channelkey = ""
    realname = ""
    username = ""
    version = ""
    channel = ""
    mysqlhost = ""
    mysqluser = ""
    mysqlpass = ""
    mysqldb = ""

    def connectionMade(self):
        irc.IRCClient.connectionMade(self)
        #self.addquote = addQuote(self.quotelog)
        #self.showquote = showQuote(self.quotelog)
        self.quotedb = dB(self.mysqlhost, self.mysqluser, self.mysqlpass, self.mysqldb)

    def connectionLost(self, reason):
        irc.IRCClient.connectionLost(self, reason)
        #self.addquote.close()
        #self.showquote.close()
        self.quotedb.close()

    def signedOn(self):
        self.join(self.channel, self.channelkey)

    def privmsg(self, user, channel, msg):
        user = user.split("!", 1)[0]
        url = re.findall('http[s]?://(?:[a-zA-Z]|[0-9]|[$-_@.&+]|[!*\(\),]|(?:%[0-9a-fA-F][0-9a-fA-F]))+', msg)

        if url:
            date = datetime.datetime.now()
            exist = self.quotedb.url(url[0], date, user)

            if exist:
                self.msg(channel, exist)

        if msg.startswith("!addquote"):
            #self.addquote.add(msg.split(" ", 1)[1])
            #date = time.strftime("%Y%m%d%H%M", time.localtime())
            date = datetime.datetime.now()
            msg = msg.split(" ", 1)[1]
            self.quotedb.add(msg, date, user)
            self.msg(channel, "Quote added to database!")

        if msg.startswith("!quote"):
            quote = self.quotedb.randquote()
            if quote:
                msg = "Quote (%s) - %s" %(quote[1].split(" ", 1)[0], quote[0])
                self.msg(channel, msg)
            else:
                msg = "Quote database empty."
                self.msg(channel, msg)

    def ctcpQuery_VERSION(self, user, channel, data):
        user = user.split("!", 1)[0]
        self.ctcpMakeReply(user, [("VERSION", "%s" %self.version)])

class quoteBotFactory(protocol.ClientFactory):

    protocol = quoteBot

    def clientConnectionLost(self, connector, reason):
        connector.connect()

    def clientConnectionFailed(self, connector, reason):
        reactor.stop()

if __name__ == '__main__':
    #log.startLogging(sys.stdout)
    f = quoteBotFactory()
    reactor.connectTCP("irc.inet.tele.dk", 6667, f)
    reactor.run()