import random
import re
import time

from cloudbot import hook
from cloudbot.util import botvars
from sqlalchemy import Table, Column, String, PrimaryKeyConstraint
from sqlalchemy.types import REAL
from sqlalchemy import select

qtable = Table(
    'quote',
    botvars.metadata,
    Column('chan', String),
    Column('nick', String),
    Column('add_nick', String),
    Column('msg', String),
    Column('time', REAL),
    Column('deleted', String, default=0),
    PrimaryKeyConstraint('chan', 'nick', 'msg')
)


def format_quote(q, num, n_quotes):
    """Returns a formatted string of a quote"""
    ctime, nick, msg = q
    return "[{}/{}] <{}> {}".format(num, n_quotes,
                                    nick, msg)


def add_quote(db, chan, target, sender, message):
    """Adds a quote to a nick, returns message string"""
    try:
        query = qtable.insert().values(
            chan=chan,
            nick=target.lower(),
            add_nick=sender.lower(),
            msg=message,
            time=time.time()
        )
        db.execute(query)
        db.commit()
    except:
        return "Message already stored, doing nothing."
    return "Quote added."


def del_quote(db, chan, nick, add_nick, msg):
    """Deletes a quote from a nick"""
    query = qtable.update() \
        .where(qtable.c.chan == 1) \
        .where(qtable.c.nick == nick.lower()) \
        .where(qtable.c.msg == msg) \
        .values(deleted=1)
    db.execute(query)
    db.commit()


def get_quote_num(num, count, name):
    """Returns the quote number to fetch from the DB"""
    if num:  # Make sure num is a number if it isn't false
        num = int(num)
    if count == 0:  # Error on no quotes
        raise Exception("No quotes found for {}.".format(name))
    if num and num < 0:  # Count back if possible
        num = count + num + 1 if num + count > -1 else count + 1
    if num and num > count:  # If there are not enough quotes, raise an error
        raise Exception("I only have {} quote{} for {}.".format(count, ('s', '')[count == 1], name))
    if num and num == 0:  # If the number is zero, set it to one
        num = 1
    if not num:  # If a number is not given, select a random one
        num = random.randint(1, count)
    return num


def get_quote_by_nick(db, nick, num=False):
    """Returns a formatted quote from a nick, random or selected by number"""

    count_query = select([qtable]) \
        .where(qtable.c.deleted != 1) \
        .where(qtable.c.nick == nick.lower()) \
        .count()
    count = db.execute(count_query).fetchall()[0][0]

    try:
        num = get_quote_num(num, count, nick)
    except Exception as error_message:
        return error_message

    query = select([qtable.c.time, qtable.c.nick, qtable.c.msg]) \
        .where(qtable.c.deleted != 1) \
        .where(qtable.c.nick == nick.lower()) \
        .order_by(qtable.c.time)\
        .limit(1) \
        .offset((num - 1))
    data = db.execute(query).fetchall()[0]
    return format_quote(data, num, count)


def get_quote_by_nick_chan(db, chan, nick, num=False):
    """Returns a formatted quote from a nick in a channel, random or selected by number"""
    count_query = select([qtable]) \
        .where(qtable.c.deleted != 1) \
        .where(qtable.c.chan == chan) \
        .where(qtable.c.nick == nick.lower()) \
        .count()
    count = db.execute(count_query).fetchall()[0][0]

    try:
        num = get_quote_num(num, count, nick)
    except Exception as error_message:
        return error_message

    query = select([qtable.c.time, qtable.c.nick, qtable.c.msg]) \
        .where(qtable.c.deleted != 1) \
        .where(qtable.c.chan == chan) \
        .where(qtable.c.nick == nick.lower()) \
        .order_by(qtable.c.time) \
        .limit(1) \
        .offset((num - 1))
    data = db.execute(query).fetchall()[0]
    return format_quote(data, num, count)


def get_quote_by_chan(db, chan, num=False):
    """Returns a formatted quote from a channel, random or selected by number"""
    count_query = select([qtable]) \
        .where(qtable.c.deleted != 1) \
        .where(qtable.c.chan == chan) \
        .count()
    count = db.execute(count_query).fetchall()[0][0]

    try:
        num = get_quote_num(num, count, chan)
    except Exception as error_message:
        return error_message

    query = select([qtable.c.time, qtable.c.nick, qtable.c.msg]) \
        .where(qtable.c.deleted != 1) \
        .where(qtable.c.chan == chan) \
        .order_by(qtable.c.time)\
        .limit(1) \
        .offset((num - 1))
    data = db.execute(query).fetchall()[0]
    return format_quote(data, num, count)


@hook.command('q', 'quote')
def quote(text, nick, chan, db, notice):
    """[#chan] [nick] [#n] OR add <nick> <message> - gets the [#n]th quote by <nick> (defaulting to random)
    OR adds <message> as a quote for <nick> in the caller's channel"""

    add = re.match(r"add[^\w@]+(\S+?)>?\s+(.*)", text, re.I)
    retrieve = re.match(r"(\S+)(?:\s+#?(-?\d+))?$", text)
    retrieve_chan = re.match(r"(#\S+)\s+(\S+)(?:\s+#?(-?\d+))?$", text)

    if add:
        quoted_nick, msg = add.groups()
        notice(add_quote(db, chan, quoted_nick, nick, msg))
        return
    elif retrieve:
        select, num = retrieve.groups()
        by_chan = True if select.startswith('#') else False
        if by_chan:
            return get_quote_by_chan(db, select, num)
        else:
            return get_quote_by_nick(db, select, num)
    elif retrieve_chan:
        chan, nick, num = retrieve_chan.groups()
        return get_quote_by_nick_chan(db, chan, nick, num)

    notice(quote.__doc__)