import random
import string
import re
import discord
import mysql.connector
from mysql.connector import errorcode

client = discord.Client()

try:
    cnx = mysql.connector.connect(user='', password='',
                                  host='',
                                  database='')
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)


@client.event
async def on_ready():
    print('Logged in as')
    print(client.user.name)
    print(client.user.id)
    print('Online with ' + str(len(client.servers)) + ' servers.')
    await client.change_presence(game=discord.Game(name='DM me .generate'))
    print('------')


# @client.event
# async def direct_message_task():
def add_user(userid, token):
    curA = cnx.cursor()
    query = ("INSERT INTO users "
             "(discord_id, token)"
             "VALUES (%s, %s)")
    curA.execute(query, (userid, token))
    cnx.commit()


def add_server(server):
    serverID = server.id
    serverName = server.name
    owner = server.owner.id
    members = server.member_count
    curA = cnx.cursor()
    query = ("INSERT INTO servers "
             "(discord_id, members, server_name, owner_discord_id)"
             "VALUES (%s, %s, %s, %s)")
    curA.execute(query, (serverID, members, serverName, owner))
    cnx.commit()


# def get_user_voted(userid, server):
#     curA = cnx.cursor(buffered=True)
#     query = "SELECT %s_voted FROM users WHERE discord_id = %s"
#     curA.execute(query, (int(server.id), userid))
#     result = curA.fetchall()
#     for r in result:
#         return r[0]


def get_user_votes(userid, server):
    curA = cnx.cursor(buffered=True)
    query = "SELECT votes FROM votes WHERE server_id = %s AND user_id = %s"
    curA.execute(query, (int(server.id), userid))
    result = curA.fetchall()
    for r in result:
        return r[0]


def generate_token():
    return ''.join(random.SystemRandom().choice(string.ascii_uppercase + string.digits) for _ in range(12))


def get_token(userid):
    curA = cnx.cursor(buffered=True)
    query = "SELECT token FROM users WHERE discord_id = %s"
    curA.execute(query, (userid,))
    result = curA.fetchall()
    for r in result:
        return r[0]


def server_exists(serverid):
    curA = cnx.cursor(buffered=True)
    query = "SELECT * FROM servers WHERE discord_id = %s"
    curA.execute(query, (serverid,))
    result = curA.fetchall()
    cnx.commit()
    return len(result) > 0


def user_exists(userid):
    curA = cnx.cursor(buffered=True)
    query = "SELECT * FROM users WHERE discord_id = %s"
    curA.execute(query, (userid,))
    result = curA.fetchall()
    cnx.commit()
    return len(result) > 0


def set_invite(invType, server, invite):
    curA = cnx.cursor()
    # type = true - discord
    # type = false - telegram
    if invType:
        query = "UPDATE `cryptodb`.`servers` SET `discord_link`=%s WHERE `discord_id`=%s"
    else:
        query = "UPDATE `cryptodb`.`servers` SET `telegram_link`=%s WHERE `discord_id`=%s"
    curA.execute(query, (invite, int(server.id)))
    cnx.commit()


def set_visible(boole, server):
    curA = cnx.cursor()
    query = "UPDATE `cryptodb`.`servers` SET `visibility`=%s WHERE `discord_id`=%s"
    curA.execute(query, (int(boole), int(server.id)))
    cnx.commit()


@client.event
async def on_server_remove(server):
    set_visible(False, server)


@client.event
async def on_server_join(server):
    # Sends server info to SQL
    if not server_exists(server.id):
        add_server(server)
    set_visible(True, server)

    # Creates a #votes channel, sends an informational message, and pins it.
    votesChannel = await client.create_channel(server, 'votes')
    votesEmbed = discord.Embed(
        description='Use .votes to view your votes for this server. Only usable in the #votes channel.', color=0xf7c9c9)
    votesMsg = await client.send_message(votesChannel, embed=votesEmbed)
    await client.pin_message(votesMsg)

    # Creates a #input channel, sends an informational message, and pins it.
    everyone_perms = discord.PermissionOverwrite(read_messages=False)
    everyone = discord.ChannelPermissions(target=server.default_role, overwrite=everyone_perms)

    inputChannel = await client.create_channel(server, 'input', everyone)
    inputEmbed = discord.Embed(
        description='Use .telegram (**URL**) to update your telegram link.\nUse .discord (**URL**) to update your discord invite link.\nThese commands are only usable by **Administrators** in the #input channel.',
        color=0xf7c9c9)
    inputMsg = await client.send_message(inputChannel, embed=inputEmbed)
    await client.pin_message(inputMsg)


@client.event
async def on_message(message):
    auth = message.author.id

    # #input channel handler
    if message.channel.name == 'input':
        if message.content.startswith('.telegram'):
            try:
                invite = re.search("(?P<url>https?://[^\s]+)", message.content).group("url")
                if len(invite) > 0 and 't.me/' in invite:
                    set_invite(False, message.server, invite)
                    await client.send_message(message.channel, 'Telegram link changed to `' + invite + '`')
                else:
                    await client.send_message(message.channel,
                                              'No valid link. Make sure you\'re using `https://t.me/INVITE` format.')
            except AttributeError:
                await client.send_message(message.channel,
                                          'No valid link. Make sure you\'re using `https://t.me/INVITE` format.')
        elif message.content.startswith('.discord'):
            try:
                invite = re.search("(?P<url>https?://[^\s]+)", message.content).group("url")
                if len(invite) > 0 and 'discord.gg/' in invite:
                    set_invite(True, message.server, invite)
                    await client.send_message(message.channel, 'Discord link changed to `' + invite + '`')
                else:
                    await client.send_message(message.channel,
                                              'No valid link. Make sure you\'re using `https://discord.gg/INVITE` '
                                              'format.')
            except AttributeError:
                await client.send_message(message.channel,
                                          'No valid link. Make sure you\'re using `https://discord.gg/INVITE` format.')

    # .generate handler
    elif message.server is None and message.content.startswith('.generate'):
        token = generate_token()
        if user_exists(auth):  # check to see if their id is already registered
            await client.send_message(message.channel, 'Token already exists: ' + get_token(auth))
        else:
            add_user(auth, token)
            await client.send_message(message.channel, 'Use this token to register on http://crypto-pumps.com. Do '
                                                       'not share it with anyone. It is used to recover your account and register. \n\n' + token)

    # .token handler
    elif message.server is None and message.content.startswith('.token'):
        token = get_token(auth)
        await client.send_message(message.channel, 'Use this token to register on http://crypto-pumps.com. Do '
                                                   'not share it with anyone. It is used to recover your account and register. \n\n' + token)

    # .votes handler
    elif message.channel.name == 'votes' and message.content.startswith('.votes'):
        if user_exists(auth):
            mention = message.author.mention
            embed = discord.Embed(description='**Stats for ' + mention + '**', color=0xf7c9c9)
            embed.add_field(name="Server: " + message.server.name,
                            value="Votes :thumbsup🇬🇮 " + str(get_user_votes(auth, message.server)))
            await client.send_message(message.channel, embed=embed)
        else:
            await client.send_message(message.channel, 'Cannot find any information about that user.')


client.run('NDAyMTU5MTEyMTQ3OTU5ODA4.DT2QGw._LZ2vtf7Obw6p8EbVDYElXdzsS0')