# jtdict.py
#
# Version 0.1 - 2007-01-17
# Niels Roosen - niels at okkernoot dot net
#
# Syntax:
# !learn <word> #= <definition>
# !alias <word> = <word>
# !forget <word> [#]
# ?? <word>
#
# Necessary extra database tables:
#
# CREATE TABLE dict_defs (
#   id int(11) NOT NULL auto_increment,
#   key_id int(11) NOT NULL default '0',
#   list_id int(11) NOT NULL default '1',
#   `string` text NOT NULL,
#   date_added varchar(32) NOT NULL default '',
#   `user` varchar(32) NOT NULL default '',
#   hostmask varchar(128) NOT NULL default '',
#   PRIMARY KEY  (id)
# );
# 
# CREATE TABLE dict_keys (
#   id int(11) NOT NULL auto_increment,
#   `string` varchar(64) NOT NULL default '',
#   alias int(11) default NULL,
#   PRIMARY KEY  (id),
#   UNIQUE KEY `string` (`string`)
# );

""" dictionary """

__copyright__ = 'this file is in the public domain'

from gozerbot.commands import cmnds
from gozerbot.examples import examples
from gozerbot.redispatcher import rebefore, reafter
from gozerbot.aliases import aliases
from gozerbot.plughelp import plughelp
from gozerbot.generic import rlog
from gozerbot.db import db
import time, MySQLdb

plughelp.add("jtdict", "infoitems/factoids dictionary, ported from dictionary.tcl");

class JTDict(object):
    """ information items """
    def __init__(self):
        self.lastmsg = "";

    def add(self, key, pos, desc, userhost, ttime):
        """ add item at pos # """

        if pos == "": pos = 0;
        else:         pos = int(pos);

        # First retrieve current occurrences of current keyphrase
        keyfound = db.execute(""" SELECT * FROM dict_keys WHERE string=%s LIMIT 1 """, key);
        if not keyfound: # phrase doesn't exist
            res = db.execute(""" INSERT INTO dict_keys SET string=%s """, key);
            if not res:
                self.lastmsg = "unable to insert new key into database"; return false;
            keyfound = db.execute(""" SELECT * FROM dict_keys WHERE string=%s LIMIT 1 """, key);

        (_id, _key, _alias) = keyfound[0];
        
        # Check if key isn't an aliase
        if _alias != None:
            alias = db.execute(""" SELECT string FROM dict_keys WHERE id=%s LIMIT 1 """, _alias);
            if not alias:
                self.lastmsg = "key %s is an alias but corresponding key not found";
            else:
                self.lastmsg = "key %s is an alias for %s, can't add definitions to aliases";

            return False;

        # Fetch the highest position number
        last_def_id = db.execute(""" SELECT MAX(list_id) FROM dict_defs WHERE key_id=%s """, _id);
        if not last_def_id or last_def_id[0][0] == None: last_def_id = 0;
        else:                                            last_def_id = int(last_def_id[0][0]);

        if pos > last_def_id or pos == 0: pos = last_def_id + 1;

        # From here it gets tricky
        # If we insert somewhere in the list, update the ordering
        if pos <= last_def_id:
            res = db.execute(""" UPDATE dict_defs SET list_id=list_id+1 WHERE key_id=%s AND """ \
                             """ list_id >= %s """, (_id, str(pos)));
            if not res:
                self.lastmsg = "error updating ordering"; return False;

        # Insert the new entry
        res = db.execute("""INSERT INTO dict_defs SET """ \
                         """key_id=%s, """ \
                         """list_id=%s, """ \
                         """string=%s, """ \
                         """date_added=%s, """ \
                         """hostmask=%s """, \
                         (str(_id), str(pos), desc, str(int(ttime)), userhost));

        if not res:
            self.lastmsg = "error inserting new item (NOTE: consistency warning!)";
            return False;

        return True;
    

    def delete(self, key, pos):
        """ del item at pos # """
        if pos == "": pos = 0;

        # Look up the item first
        res = db.execute("""SELECT * FROM dict_keys WHERE string=%s LIMIT 1""", key);
        if not res:
            self.lastmsg = "error: key %s not found" % key; return False;
        _id, _string, _alias = res[0];
        _id = int(_id);

        # If we have to delete an alias, delete it
        if _alias != None:
            res = db.execute("""DELETE FROM dict_keys WHERE string=%s""", key);
            if not res:
                self.lastmsg = "error: deleting of alias %s failed" % key; return False;
            else:
                self.lastmsg = "alias %s deleted" % key; return True;

        # Do we have to delete only one definition?
        if pos > 0:
            # Check itemcount
            count = db.execute("""SELECT MAX(list_id) FROM dict_defs WHERE key_id=%s""", res[0][0]);

            if not count:
                self.lastmsg = "error: unable to retrieve definition count for %s" % key; 
                return False;
            count = int(count[0][0]);

            if pos > count:
                self.lastmsg = "error: key hass only %d definitions" % count; return False;
            if pos > 1 or count > 1:
                # Delete selected item
                res = db.execute("""DELETE FROM dict_defs WHERE key_id=%s AND list_id=%s""",
                                 (_id, str(pos)));
                if not res:
                    self.lastmsg = "error: unable to delete definition at pos %d for %s" \
                                   % (pos, key); 
                    return False;
                
                # Update ordering
                res = db.execute("""UPDATE dict_defs SET list_id=list_id-1 WHERE key_id=%s """
                                 """AND list_id > %s""", (_id, str(pos)));
                if not res:
                    self.lastmsg = "error: definition %s at pos %d deleted but unable to update " \
                                   "ordering" % (key, pos); return False;
                
                self.lastmsg = "definition for %s at position %d deleted" % (key, pos); return True;

        # Otherwise we have to remove the whole definition
        res = db.execute("""DELETE FROM dict_defs WHERE key_id=%s""", _id);
        if not res: 
            self.lastmsg = "error: unable to delete definitions for %s" % key;
            return False;
        res = db.execute("""DELETE FROM dict_keys WHERE id=%s OR alias=%s""", (_id, _id));
        if not res: 
            self.lastmsg = "error: unable to delete key for %s, definitions deleted" % key;
            return False;

        self.lastmsg = "all definitions (and aliases) for %s deleted" % key;
        return True;


    def alias(self, alias, key):
        """ add alias """

        # Check if alias doesn't already exist
        res = db.execute("""SELECT id FROM dict_keys WHERE string=%s LIMIT 1""", alias);
        if res:
            self.lastmsg = "error adding alias: key %s already exists" % alias;
            return False;

        # Check if key exists and isn't an alias as well
        res = db.execute("""SELECT * FROM dict_keys WHERE string=%s LIMIT 1""", key);
        if not res:
            self.lastmsg = "error: key %s doesn't exist" % key; return False;
        elif res[0][2] != None:
            self.lastmsg = "error: key %s is an alias itself" % key; return False;

        # Add the alias
        res = db.execute("""INSERT INTO dict_keys (string, alias) VALUES (%s, %s)""",
                         (alias, res[0][0]));
        if not res:
            self.lastmsg = "error adding alias %s for %s to database" % (alias, key);
            return False;

        return True;

    def get(self, key):
        """ retrieve a definition """
        # SELECT d1.string, d2.string, d3.list_id, d3.string, d3.date_added, d3.hostmask
        # FROM dict_keys AS d1 
        #   LEFT JOIN  dict_keys AS d2 ON d1.id=d2.alias AND d2.string='two'
        #   RIGHT JOIN dict_defs AS d3 ON d1.id=d3.key_id 
        # WHERE (d2.string='two' OR d1.string='two')
        # ORDER BY d3.list_id
        res = db.execute("""SELECT d1.string, d2.string, d3.list_id, d3.string, """ \
                                """d3.date_added, d3.hostmask """ \
                         """FROM dict_keys AS d1 """ \
                           """LEFT JOIN  dict_keys AS d2 ON d1.id=d2.alias AND d2.string=%s """ \
                           """RIGHT JOIN dict_defs AS d3 ON d1.id=d3.key_id """ \
                         """WHERE (d2.string=%s OR d1.string=%s) """ \
                         """ORDER BY d3.list_id""", (key, key, key));
        return res;

    def GetLastMsg(self):
        return self.lastmsg;

jt = JTDict();


def handle_jtaddinfoitem(bot, ievent):
    """ add an item """
    try:
        (key, pos, desc) = ievent.groups;
    except ValueError:
        ievent.missing("<item> [<position>] <description>");
        return;

    if jt.add(key, pos, desc, ievent.userhost, int(time.time())):
        ievent.reply("%s added" % desc);
    else:
        msg = jt.GetLastMsg();
        if msg != "":
            ievent.reply("%s" % msg);
        ievent.reply("add failed");
    return;

rebefore.add(9, '^learn (.+?)\s+(\d*)=\s+(.+)$', handle_jtaddinfoitem, 'USER');
rebefore.add(10, '^(.+?)\s+(\d*)=\s+(.+)$', handle_jtaddinfoitem, 'USER');


def handle_jtaddinfoalias(bot, ievent):
    """ add an alias for an item """
    try:
        (alias, key) = ievent.groups;
    except ValueError:
        ievent.missing("<alias> <key>");
        return;

    if jt.alias(alias, key):
        ievent.reply("alias %s added" % alias);
    else:
        msg = jt.GetLastMsg();
        if msg != "":
            ievent.reply("%s" % msg);
        ievent.reply("add alias failed");
    return;

rebefore.add(9, '^alias (.+?)\s+=\s+(.+)$', handle_jtaddinfoalias, 'USER');


def handle_jtforget(bot, ievent):
    """ remove an item """
    try:
        (key, position) = ievent.groups;
    except ValueError:
        ievent.missing("<item> [<position>]");
        return;
    if position != None: position = int(position.strip());
    else: position = 0;
    res = jt.delete(key, position);
    ievent.reply(jt.GetLastMsg());

rebefore.add(9, '^forget (.+?)(\s+\d*)?$', handle_jtforget, ['USER', 'FORGET']);
examples.add('forget', 'forget <txt> [#] .. forget the item at position #', 'forget foo');


def handle_jtquestion(bot, ievent):
    """ ?? <item> .. look up information item """
    try:
        key = ievent.groups[0];
    except ValueError:
        ievent.missing("no argument given");
        return;
    key = key.strip();
    result = jt.get(key);
    rlog(10, 'jtdict', result);
    if result:
        # Items per row:
        # string, alias string, list_id, string, date_added, hostmask
        if result[0][1] != None:
            ievent.reply("[dict] %s is an alias for %s" % (result[0][1], result[0][0]));
        res = []
        for i in result:
            res.append(i[3])
        if len(res) < 5: 
            n = 1;
            for i in res:
                ievent.reply("[%d] %s" % (n, i));
                n += 1;
        else:
            ievent.reply("[dict] showing %d matches on %s to %s in private" \
                         % (len(res), key, ievent.nick))
            n = 1;
            for i in res:
                ievent.reply("[%d] %s" % (n, i), nick=ievent.nick)
                n += 1;
    else:
        ievent.reply('[dict] no items known for %s' % key)

reafter.add(10, '^(.+)\?$', handle_jtquestion, ['USER', 'WEB', 'JCOLL'])
reafter.add(10, '^\?(.+)$', handle_jtquestion, ['USER', 'WEB', 'JCOLL'])