import os
import time
import re
from slackclient import SlackClient
import MySQLdb

# instantiate Slack client
slack_client = SlackClient('xoxb-XXXXXXXXXXXXXXXXXXX')
# starterbot's user ID in Slack: value is assigned after the bot starts up
starterbot_id = None

# database
db_host = '..'
db_name = '..'
db_user = '..'
db_password = '..'

# constants
RTM_READ_DELAY = 2 # 2 second delay between reading from RTM
EXAMPLE_COMMAND = "Passoword: || Module: || SQL: "
MENTION_REGEX = "^<@(|[WU].+?)>(.*)"

help = "To get password for a user (Password: email_id) \n To activate company module (Module: all company_id) \n Can write your own SQL queries (SQL: query) \n Happy Coding. :-)"

///
SQL Commands
///

def parse_bot_commands(slack_events):
    """
        Parses a list of events coming from the Slack RTM API to find bot commands.
        If a bot command is found, this function returns a tuple of command and channel.
        If its not found, then this function returns None, None.
    """
    for event in slack_events:
        if event["type"] == "message" and not "subtype" in event:
            user_id, message = parse_direct_mention(event["text"])
            if user_id == starterbot_id:
                return message.lower(), event["channel"]
    return None, None


def parse_direct_mention(message_text):
    """
        Finds a direct mention (a mention that is at the beginning) in message text
        and returns the user ID which was mentioned. If there is no direct mention, returns None
    """
    matches = re.search(MENTION_REGEX, message_text)
    # the first group contains the username, the second group contains the remaining message
    return (matches.group(1), matches.group(2).strip()) if matches else (None, None)


def handle_command(command, channel):
    """
        Executes bot command if the command is known
    """

    # Open database connection
    db = MySQLdb.connect(db_host, db_user, db_password, db_name)

    # Default response is help text for the user
    default_response = "Not sure what you mean. Try *{}* ".format(EXAMPLE_COMMAND)

    # Finds and executes the given command, filling in response
    response = None
    if command in ['hi', 'hey', 'hello']:
        response = 'Hey.. How may I help you.'
    if command == 'help':
        response = help
    else:
        try:
            command = command.split(':')
            if command[0] == 'password':
                response = get_password(command[2], db)

            if command[0] == 'module':
                response = module_activation(command[1], db)

            if command[0] == 'sql':
                response = raw_sql(command[1], db)
        except Exception as e:
            print e

    db.close()

    # Sends the response back to the channel
    slack_client.api_call(
        "chat.postMessage",
        channel=channel,
        text=response or default_response
    )


def get_password(command, db):
    response = ''
    cursor = db.cursor()
    try:
        email = command.split('|')
        sql = password.format(email[0])
        cursor.execute(sql)
        data = cursor.fetchall()
        if data:
            for d in data:
                response += 'Name: ' + str(d[0]) + ' ' + str(d[1]) + ', Email: ' + str(d[2]) + ', Password: ' + str(d[3]) + ', Company Id: ' + str(d[4])
        else:
            response = 'No data found.'
    except Exception as e:
        print(e)
        pass

    return response


def module_activation(cmd, db):
    cursor = db.cursor()
    try:
        all_modules = [inventory, reporting, integration, access, tagging, multiple_unit, gst, payment, bi, production, approval]
        module, company = cmd.split(' ')
        if module == 'all':
            for module in all_modules:
                for script in module:
                    cursor.execute(script.format(company))

        response = 'Done'
    except:
        response = 'Not Done.'

    return response


def raw_sql(sql, db):
    response = ''
    if sql.startswith('happycoding'):
        sql = sql.replace('happycoding', '')
        cursor = db.cursor()
        cursor.execute(sql)
        response = cursor.fetchall()
    return response


if __name__ == "__main__":
    if slack_client.rtm_connect(with_team_state=False):
        print("Starter Bot connected and running!")
        # Read bot's user ID by calling Web API method `auth.test`
        starterbot_id = slack_client.api_call("auth.test")["user_id"]
        while True:
            command, channel = parse_bot_commands(slack_client.rtm_read())
            if command == 'exit':
                slack_client.api_call(
                    "chat.postMessage",
                    channel=channel,
                    text = 'Bye..'
                )
                break
            if command:
                handle_command(command, channel)
            time.sleep(RTM_READ_DELAY)
    else:
        print("Connection failed. Exception traceback printed above.")