import re
import socket

import MySQLdb as mdb

global run, dbhost, dbuser, dbpass, dbname, table, db, dbc

server = "irc.freenode.net"
channel = "#reddit-r4r-Florida"
botnick = "nb1"
run = 1
dbhost = 'localhost'
dbuser = 'root'
dbpass = 'Ntk5927654'
dbname = 'nbot'
dbtable = 'users'

def ping():
    ircsock.send("PONG :pingis\n")

def sendmsg(chan, msg):
    ircsock.send("PRIVMSG " + chan + " :" + msg + "\n")

def joinchan(chan):
    ircsock.send("JOIN " + chan + "\n")

def stoprun():
    global run
    run = 0
    return run

def cities(nick, chan):
    query = buildquery('SELECT', 'cities', '', '', '`name` ASC', '')
    c = gdbc(query)
    data = c.fetchall()
    rc = c.rowcount
    counter = 1
    if rc > 0:
        s = 'We have users registered in: '
        for row in data:
            city = row[1]
            num = row[2]
            if counter == rc:
                s += ' and '
            elif counter > 1:
                s += ', '
            s += '%s (%s)' % (city, num)
            counter += 1
    else:
        s = 'No registered users have specified their location'
    sendmsg(chan, s)

def gdbc(query):
    global db, dbc, channel
    try:
        db = mdb.connect(host=dbhost, user=dbuser, passwd=dbpass, db=dbname)
        dbc = db.cursor()
        dbc.execute(query)
    except mdb.Error, e:
        sendmsg(channel, "Error %d: %s" % (e.args[0], e.args[1]))
    finally:
        if db:
            db.close()
    return dbc

def rowcount(qstring):
    c = gdbc(qstring)
    rc = c.rowcount
    c.close
    return rc

def getuser(nstring):
    c = gdbc(buildquery('SELECT', 'users', '', 'name=%s' % nstring, '', '1'))
    data = c.fetchall()
    c.close
    return data[0]

def register(ps):
    global dbc
    dbc.execute(buildquery('INSERT', '', 'users', ps, '', '', ''))

def unregister():
    return ''

def info(ps):
    global channel
    if len(ps) == 1:
        nstring = ps[0]
    else:
        nstring = ps[1]
    user = getuser(nstring)
    sendmsg(channel, '%s' % user[6])

def set(ps):
    global dbc
    dbc.execute(buildquery('UPDATE', '', 'users', ps, '', '', ''))

def askball(str):
    return "You asked: %s" % (str)

def who(nick):
    return 'Who is %s' % (nick)

def locals(nick, chan, ps):
    pstring = buildpstring(ps)
    if pstring.find("location=") == -1:
        dl = getuser(nick)[5]
        if pstring != '':
            pstring += ';'
        pstring += "location=%s" % dl
        location = dl
    else:
        location = pstring.split("location=")[-1].split(";")[0]
    query = buildquery('SELECT', 'users', '', pstring, '`id` ASC', '')
    c = gdbc(query)
    data = c.fetchall()
    rc = c.rowcount
    c.close()
    if rc != 0:
        if rc == 1:
            s = '%s is the only user in %s' % (data[0][6], location)
        else:
            s = '%s users in %s: ' % (rc, location)
            for i in range(len(data)):
                if i > 0:
                    s += ', '
                s += data[i][6]
    else:
        rc = rowcount(buildquery('SELECT' , 'users', '', 'location=%s' % location, '`id` ASC', ''))
        if rc != 0:
            s = 'No users in %s match criteria.' % location
        else:
            s = 'No registered user in %s.' % location
    sendmsg(chan, s)

def buildpstring(ps):
    counter = 0
    age = ''
    ages = ''
    gender = ''
    seeking = ''
    location = ''
    s = ''
    for param in ps:
        if re.match("^[0-9]+$", param):
            age = param
        elif re.match("^m|f$", param):
            gender = param
        elif re.match("^[\S]+:[\S]+$", param):
            if counter > 0:
                s += ';'
            kvp = param.split(':')
            key = kvp[0]
            value = kvp[1]
            s += '%s=%s' % (key, value)
            counter += 1
        elif re.match("^4m|4f|4g|4a|4t|4n$", param):
            seeking = param
        elif re.match("^[0-9]*-[0-9]*$", param):
            ages = param
        else:
            if location == '':
                location = param
            else:
                location += ' %s' % param
    if age != '':
        if s != '':
            s += ';'
        s += 'age=%s' % age
    if ages != '':
        if s != '':
            s += ';'
        s += 'ages=%s' % ages
    if gender != '':
        if s != '':
            s += ';'
        s += 'gender=%s' % gender
    if seeking != '':
        if s != '':
            s += ';'
        s += 'seeking=%s' % seeking
    if location != '':
        if s != '':
            s += ';'
        s += 'location=%s' % location
    return s

def buildquery(qt, tn, st, ps, ob, ls):
    params = ps.split(";")
    counter = 0
    if qt == 'SELECT':
        s = 'SELECT * FROM `' + tn + '`'
        if ps != '':
            s += ' WHERE'
    elif qt == 'UPDATE':
        s = "UPDATE `" + tn + "` SET"
    else:
        s = 'INSERT INTO `' + tn + '` ('
    if ps != '':
        for param in params:
            if counter > 0:
                if qt == 'UPDATE' or qt == 'INSERT':
                    s += ', '
                else:
                    s += " && "
            else:
                s += " "
            vals = param.split("=")
            key = vals[0]
            value = vals[-1]
            if qt == 'SELECT' and key == 'ages':
                ages = value.split('-')
                minage = ages[0]
                maxage = ages[1]
                if minage != '':
                    s += "`age` >= '" + minage + "'"
                if maxage != '':
                    if minage != '':
                        s += " && "
                    s += "`age` <= '" + maxage + "'"
            elif qt == 'SELECT' and key == 'seeking':
                s += "(`seeking` = '%s' || `seeking` = '4a')" % value
            else:
                s += "`%s`" % key
                if qt != 'INSERT':
                    s += " = '%s'" % value
            counter += 1
    if qt == 'INSERT':
        s += ') VALUES ('
        vals = param.split("=")
        if counter != 0:
            s += ', '
        value = vals[-1]
        s += "'$s'"
    if qt == 'SELECT' and ob != '':
            s += ' ORDER BY ' + ob
    if ls != '' and qt != 'INSERT':
        s += ' LIMIT ' + ls
    return s

def help(ps):
    return "This will be the help"

def handle(nick, chan, msg):
    global botnick
    parts = msg.split()
    cmd = parts[0]
    if chan == botnick:
        chan = nick
    if re.match('^dismiss$', cmd) or re.match('^bye$', cmd):
        if nick == "N___":
            sendmsg(chan, '%s: I am dismissed!' % (nick))
            stoprun()
        else:
            sendmsg(chan, '%s: You do not have permission to dismiss me.' % (nick))
    elif re.match('^locals$', cmd):
        locals(nick, chan, parts[1:])
    elif re.match('^cities$', cmd):
        cities(nick, chan)
    elif re.match('^info$', cmd):
        parts[0] = nick
        info(parts)
    elif re.match('^register$', cmd):
        parts[0] = "name:" + nick
        register(parts)
    elif re.match('^set$', cmd):
        parts[0] = "name:" + nick
        sendmsg(chan, set(parts))
    elif re.match('^help$', cmd):
        sendmsg(chan, help(ps))
    elif re.match('[\S\s]+?$', msg):
        sendmsg(chan, askball(msg))
    elif re.match('^ask$', cmd):
        sendmsg(chan, askball(msg))
    else:
        sendmsg(chan, '%s: I do not understand the command "%s"' % (nick, cmd))

ircsock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
ircsock.connect((server, 6667))
ircsock.send("USER " + botnick + " " + botnick + " " + botnick + " :This is nBot___\n")
ircsock.send("NICK " + botnick + "\n")

joinchan(channel)

while run == 1:
    ircmsg = ircsock.recv(2048)
    ircmsg = ircmsg.strip('\n\r')
    print(ircmsg)

    if ircmsg.find(' PRIVMSG ') != -1 or ircmsg.find(' JOIN ') != -1 or ircmsg.find(' PART ') != -1:
        mtype = ' PRIVMSG '
        nick = ircmsg.split('!')[0][1:]
        host = ircmsg.split('!')[-1].split(' ')[0]
        msg = ''
        if ircmsg.find(' JOIN ') != -1:
            mtype = ' JOIN '
        elif ircmsg.find(' PART ') != -1:
            mtype = ' PART '
        if mtype == ' JOIN ':
            chan = ircmsg.split(' JOIN ')[-1]
            if nick != botnick:
                sendmsg(channel, 'Welcome %s!' % nick)
        elif mtype == ' PART ':
            chan = ircmsg.split(' PART ')[-1]
        elif mtype == ' PRIVMSG ' and ircmsg.find(':' + botnick + ':') != -1:
            chan = ircmsg.split(mtype)[-1].split(' :')[0]
            msg = ircmsg.split(' PRIVMSG ' + chan + ' :' + botnick + ':')[-1]
            handle(nick, chan, msg)

    if ircmsg.find("PING :") != -1:
        ping()