def new_key(key, value):
    """
    Create a new key/value pair in the database.

    :param key: Name of the key. Will be hashed
    :param value: The value to be stored. 
    """

    if not key:
        return NoKey(key)

    key = utility.digest(key)

    try:
        sql = 'select value from keyvalue where keyhash = %(key)s;'
        result = doquery(sql, {'key': key})[0][0]
        return
    except IndexError:
        pass

    try:
        sql = "insert into keyvalue (keyhash, value) values (%(key)s, %(value)s);"
        doquery(sql, {'key': key, 'value': base64.b64encode(value)})

        sql = "select last_insert_id();"
        uid = doquery(sql)[0][0]
        if uid is None:
            raise NoKey(key)
    except (MySQLdb.OperationalError, MySQLdb.DataError, Warning):
        raise NoKey(key)

    def __init__(self, name, lang=DEFAULTLANG):
        if not name:
            raise NoString(name, lang)

        sql = 'select id, string from strings where name = %(name)s and lang = %(lang)s;'
        result = doquery(sql, { 'name': name, 'lang': lang })

        try: 
            self.uid = result[0][0]
            self.name = name
            self.string = base64.b64decode(result[0][1])
            self.lang = lang
        except IndexError:
            sql = 'select id, string from strings where name = %(name)s and lang = %(lang)s;'
            result = doquery(sql, { 'name': name, 'lang': SiteString.DEFAULTLANG })

            try: 
                self.uid = result[0][0]
                self.name = name
                self.string = base64.b64decode(result[0][1])
                self.lang = SiteString.DEFAULTLANG
            except IndexError:
                uid = new_string(name, name)
                if not uid:
                    raise NoString(name)

                self.uid = uid
                self.name = name
                self.string = name
                self.lang = lang

def new_string(name, string, lang=SiteString.DEFAULTLANG):
    """
    Create a new string in the database.

    :param name: Name of the string. Strings configurable by the user should have all-lowercase descriptive names so as not to conflict with translations. When used for translation the name should be the DEFAULTLANG version of the string itself.
    :param string: The string to be stored. 
    :param lang: Optional parameter, defaults to DEFAULTLANG
    :return: UID of the new string, or UID of the existing string if it already exists
    """

    if not name or not string:
        return NoString(name, string)

    try:
        sql = 'select id from strings where name = %(name)s and lang = %(lang)s;'
        result = doquery(sql, { 'name': name, 'lang': lang })
        return result[0][0]
    except IndexError:
        pass

    try:
        sql = "insert into strings (name, string, lang) values (%(name)s, %(string)s, %(lang)s);"
        doquery(sql, {'name': name, 'string': base64.b64encode(string), 'lang': lang})

        sql = "select last_insert_id();"
        uid = doquery(sql)[0][0]
        if uid is None:
            raise NoString(name, lang)
    except (MySQLdb.OperationalError, MySQLdb.DataError, Warning):
        raise NoString(name, lang)

    return uid

    def delete(self):
        """
        Delete an item. Might be dangerous.
        """

        self.deleted = True

        for image in self.images():
            image.delete()

        sql = 'delete from itemedits where itemid = %(uid)s;'
        result = doquery(sql, {"uid": self.uid}) 
     
        sql = 'delete from ownwant where itemid = %(itemid)s;'
        result = doquery(sql, {"itemid": self.uid}) 

        sql = 'delete from tradelist where itemid = %(itemid)s;'
        result = doquery(sql, {"itemid": self.uid}) 

        sql = 'delete from itemtags where itemid = %(uid)s;'
        result = doquery(sql, {"uid": self.uid}) 

        sql = 'delete from items where uid = %(uid)s;'
        result = doquery(sql, {"uid": self.uid}) 

        logger.info('deleted item id {}: {}'.format(self.uid, self.name))

        self.uid = None 
        self.name = None
        self.added = None
        self.modified = None

    def remove_tag(self, tag):
        logger.info('tag {} removed from {}: {} '.format(tag, self.uid, self.name))
        try:
            self.tree.retrieve(tag)
        except IndexError:
            return

        sql = "delete from itemtags where itemid=%(itemid)s and tag=%(tag)s;"
        doquery(sql, { 'itemid': self.uid, 'tag': tag })

    def delete(self):
        logger.info('deleted image id {}: {}'.format(self.uid, self.tag))
        siteimage_cache = dict()
        #TODO image purgatory
        sql = 'delete from imgmods where imgid = %(uid)s;'
        result = doquery(sql, { 'uid': self.uid })

        sql = 'delete from images where uid = %(uid)s;'
        result = doquery(sql, { 'uid': self.uid })

def ip_uid(ip, r=False):
    try:
        sql = "select uid from ip where ip = %(ip)s;"
        result = doquery(sql, { 'ip': ip })
        return result[0][0]
    except IndexError:
        if r:
            return None
        sql = "insert into ip (ip) values ( %(ip)s );"
        result = doquery(sql, { 'ip': ip })
        return ip_uid(ip, True)

def new_item(name, description, userid, ip):
    name = name.strip()[:64]

    try:
        sql = "insert into items (name, description, added, modified) values (%(name)s, 0, %(now)s, %(now)s);"
        doquery(sql, { 'now': datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S"), 'name': name })

        sql = "select uid from items where name=%(name)s and description=0;"
        itemid = doquery(sql, { 'name': name })[0][0]
    except MySQLdb.OperationalError, Warning:
        logger.info('Error adding item {} by {} ({})'.format(name, userid, ip))
        raise NoItem(0)

    def save(self, userid, ip, overwrite=False):
        sql_image = base64.b64encode(self.preview().read())

        if overwrite:
            sql = "update images set image = %(image)s where uid = %(uid)s;"
        else:
            sql = "insert into images (tag, parent, userid, image, ip) values (%(tag)s, %(parent)s, %(userid)s, %(image)s, %(ip)s);"

        doquery(sql, { 'tag': self.tag, 'userid': userid, 'ip': utility.ip_uid(ip), 'parent': self.parent, 'image': sql_image})

        sql = "select last_insert_id();"
        return doquery(sql)[0][0]

    def newaccesslevel(self, accesslevel):
        """
        Method to change a user's access level

        :param accesslevel: The new accesslevel
        """

        logger.info('Accesslevel change for user {}, was {} is now {}'.format(self.username, self.accesslevel, accesslevel))
        self.accesslevel = int(accesslevel)

        sql = "update users set accesslevel = %(level)s where uid = %(uid)s;"
        doquery(sql, {"uid": self.uid, "level": self.accesslevel})

def new_user(username, password, email, ip):
    """
    Register a new user

    :param username: Username. Truncated to 200 characters
    :param password: Cleartext password
    :param email: email address. Truncated to 200 characters
    :param ip: IP address of the requester

    :raises NoUser: if an invalid email or username is given, or on general failure in creating the user
    :return: UID of the new user or False if the username is taken
    """

    username = unicode(username).strip()[:200]
    email = email.strip()[:200]
    pwhash = gen_pwhash(password)

    if len(username) == 0:
        raise NoUser(0)

    if len(email) < 3:
        raise NoUser(0)

    joined = datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S")

    try:
        sql = "select uid from users where username = %(username)s;"
        uid = doquery(sql, { 'username': username })[0][0]
        # user exists
        return False
    except (Warning, IndexError):
        # user doesn't exist
        pass

    try:
        sql = "insert into users (username, pwhash, email, joined, accesslevel) values (%(username)s, %(pwhash)s, %(email)s, %(joined)s, '1');"
        result = doquery(sql, { 'username': username, 'pwhash': pwhash, 'email': email, 'joined': joined })

        uid = doquery("select last_insert_id();")[0][0]
        if not uid:
            raise NoUser(username)

        sql = "insert into userstat_lastseen (date, uid) values (%(lastseen)s, %(uid)s);"
        result = doquery(sql, { 'uid': uid, 'lastseen': joined })
    except MySQLdb.Error, e:
        try:
            logger.info('MySQL error adding new user {} - {}: {})'.format(username, e.args[0], e.args[1]))
            raise NoUser(username)
        except IndexError:
            logger.info('MySQL error adding new user {} - {})'.format(username, e))
            raise NoUser(username)

    def history(self):
        """
        Get the edit history for an item. 

        :return: A list of objects with the following attributes:
            * uid       - The edit's uid, zero filled
            * itemid    - The item id
            * date      - Date of the edit
            * userid    - Editing user
            * ip        - IP address of the editor
        """

        sql = """select itemedits.uid, itemedits.itemid, itemedits.date, itemedits.userid, ip.ip
                 from itemedits
                 join ip on itemedits.ip=ip.uid
                 where itemid = %(uid)s
                 order by uid desc;"""
        edits = doquery(sql, { 'uid': self.uid })

        ret = list()
        for edit in edits:
            editobject = ItemHist(edit[0])
            editobject.uid = str(editobject.uid).zfill(8)
            editobject.itemid = edit[1]
            editobject.date = edit[2]
            editobject.userid = edit[3]
            editobject.ip = edit[4]

            editobject.user = users.user_by_uid(editobject.userid)

            ret.append(editobject)

        return ret

    def crop(self, userid, ip, x1, y1, x2, y2):
        image_string = cStringIO.StringIO(base64.b64decode(self.image()))
        im = Image.open(image_string)
        cropped = im.crop((x1, y1, x2, y2))

        output = cStringIO.StringIO()
        cropped.save(output, format="JPEG")
        img_str = output.getvalue()
        output.close()

        sql_image = base64.b64encode(img_str)
        sql = "insert into images (tag, parent, userid, image, ip) values (%(tag)s, %(parent)s, %(userid)s, %(image)s, %(ip)s);"
        doquery(sql, { 'tag': self.tag, 'userid': userid, 'ip': utility.ip_uid(ip), 'parent': self.parent, 'image': sql_image})

        sql = "select last_insert_id();"
        return doquery(sql)[0][0]

 def setstatus(self, status):
     if self.uid > 0:
         self.status = status
         sql = "update messages set status = %(status)s where uid = %(uid)s;"
         result = doquery(sql, {"uid": self.uid, "status": status})
     else:
         return None

    def __init__(self, messageid):
        super(self.__class__, self).__init__(messageid)
        self.tradeitemstatus = tradeitemstatus
        self.tradestatus = tradestatus

        self.items = []

        sql = 'select * from tradelist where messageid = %(uid)s;'
        result = doquery(sql, {"uid": messageid})

        complete = True
        for item in result:
            ti = TradeItem(item[0])
            ti.itemid = item[1]
            ti.messageid = item[2]
            ti.userid = item[3]
            ti.acceptstatus = item[4]
            ti.item = items.SiteItem.create(ti.itemid)
            ti.user = users.SiteUser.create(users.user_by_uid(ti.userid))

            self.items.append(ti)

            if (ti.acceptstatus != tradeitemstatus['accepted']):
                complete = False

        if complete == True and self.status < tradestatus['settled_trade']:
            self.status = tradestatus['complete_trade']

    def __init__(self, username=None, uid=None):
        try:
            if username:
                uid = uid_by_user(username)

            if not uid:
                raise NoUser(None)

            sql = """select json
                     from user_profiles
                     where uid = %(uid)s; """
            result = doquery(sql, { 'uid': uid })

            self.uid = uid
            self.profile = json.loads(result[0][0])

            # Delete this at some point once all data has been moved, shouldn't take long
            if 'avatar' in self.profile:
                logger.info('avatar fixup applied for user id {}'.format(uid))
                self.new_avatar(self.profile['avatar'])
                del self.profile['avatar']
                self.update()
        except (Warning, IndexError):
            # return defaults
            self.profile = dict()
            self.profile['timezone'] = "America/Los_Angeles"

    def messages(self, trash=False):
        """
        Get all trades and private messages for a user

        :param trash: Only show deleted messages. Permanent deletion is not currently implemented.
        :return: list of PrivateMessage and TradeMessage objects
        """

        mwi_cache = dict()
        ret = list()
        sql = """select uid,status from messages
                 where fromuserid = %(fromuid)s or touserid = %(touid)s
                 order by sent desc;"""

        result = doquery(sql, { 'fromuid': self.uid, 'touid': self.uid })

        for item in result:
            if item[1]:
                message = messages.TradeMessage.create(item[0])
            else:
                message = messages.PrivateMessage.create(item[0])

            deleted = message.delete_status(self.username)

            if trash:
                deleted = not deleted
            if not deleted:
                ret.append(message)

        return ret

    def authenticate(self, password):
        """
        Verify a user's password

        :param password: The plaintext password to verify.
        :return: None
        :raises AuthFail: This exception will be raised if the user cannot be logged in for any reason.
        """

        sql = """select users.pwhash
                 from users
                 where users.uid = %(uid)s;"""
        result = doquery(sql, { 'uid': self.uid })

        try:
            pwhash = result[0][0]
        except IndexError:
            logger.info('AuthFail for user {}: unable to find user'.format(self.username))
            raise AuthFail(self.username)
 
        if self.accesslevel == 0:
            logger.info('AuthFail for user {}: account has been banned'.format(self.username))
            raise AuthFail(self.username)

        if not verify_pw(password, pwhash):
            logger.info('AuthFail for user {}: invalid password'.format(self.username))
            raise AuthFail(self.username)

        logger.info('Successful password authentication for user {}'.format(self.username))
        return None

    def collection(self):
        """
        List a user's collection

        :return: list of SiteItem objects
        """
        ret = list()
        sql = """select ownwant.own, ownwant.willtrade, ownwant.want, ownwant.hidden, items.uid
                 from ownwant
                 join items on items.uid=ownwant.itemid
                 where ownwant.userid = %(uid)s
                 order by ownwant.willtrade desc"""

        result = doquery(sql, { 'uid': self.uid })

        for item in result:
            sitem = items.SiteItem.create(item[4])
            sitem.have = item[0]
            sitem.willtrade = item[1]
            sitem.want = item[2]
            sitem.hidden = item[3]

            ret.append(sitem)

        return ret

    def mwi(self):
        """
        Get a count of unread messages and trades for a user

        :return: (unread messages, unread trades)
        """

        num_messages = 0
        num_trades = 0

        sql = """select uid,status from messages
                 where touserid = %(touid)s
                 order by sent desc limit 100;"""

        result = doquery(sql, { 'touid': self.uid })

        for item in result:
            message = messages.TradeMessage.create(item[0])

            if message.delete_status(self.username):
                continue

            read = message.read_status(self.username)

            if not read:
                if item[1]:
                    num_trades = num_trades + 1
                else:
                    num_messages = num_messages + 1

        return (num_messages, num_trades)