import discord
import string
import asyncio
import aiohttp
import pymysql
import pymysql.cursors

client = discord.Client()


#
@client.event
async def on_message(message):
    if message.content.startswith('ginsert'):
        await client.send_message(message.channel, "kay!")
        connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='datbot',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
        with connection.cursor() as cursor:
            # Create a new record
            sql = "INSERT INTO `datbot` (`serverid`, `whitelist`,`blacklist`) VALUES (%s, %s, %s)"
            cursor.execute(sql, (message.server.id, '0', '0'))
        # connection is not autocommit by default. So you must commit to save
        # your changes.
        connection.commit()
        connection.close()

    if message.content.startswith('gdbbl'):
        connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='datbot',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `blacklist` FROM `datbot` WHERE `serverid`=%s"
            cursor.execute(sql, (message.server.id,))
            result = cursor.fetchone()
            await client.send_message(message.channel, "kay database returned fer blecklist" + str(result))
            connection.close()

    if message.content.startswith('gdbwl'):
        connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='datbot',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `whitelist` FROM `datbot` WHERE `serverid`=%s"
            cursor.execute(sql, (message.server.id,))
            result = cursor.fetchone()
            await client.send_message(message.channel, "kay database returned fer witlist" + str(result))
            connection.close()

    if message.content == "gwhitelist":
        connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='datbot',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
        with connection.cursor() as cursor:
            sql = "SELECT `whitelist` FROM `datbot` WHERE `serverid`=%s"
            cursor.execute(sql, (message.server.id,))
            result = cursor.fetchone()
            if result["whitelist"] == "1":
                await client.send_message(message.channel, "it woz on turning it off")
                sql = "UPDATE datbot SET whitelist='0' WHERE serverid=%s"
                cursor.execute(sql, (message.server.id,))
                connection.commit()
                connection.close()
            else:
                await client.send_message(message.channel, "it wos off turning it on")
                sql = "UPDATE datbot SET whitelist='1' WHERE serverid=%s"
                cursor.execute(sql, (message.server.id,))
                connection.commit()
                connection.close()

    if message.content.startswith('gwhitelistadd'):
                rolename = message.content.replace("gwhitelistadd ","",1)
                role = discord.utils.get(message.server.roles, name=str(rolename))
                roleid = role.id
        with connection.cursor() as cursor:
            # Read a single record
            sql = "SELECT `whitelist` FROM `datbot` WHERE `serverid`=%s"
            cursor.execute(sql, (message.server.id,))
            result = cursor.fetchone()
            await client.send_message(message.channel, "kay database returned fer witlist" + str(result))
            connection.close()


                rolelist = list(dataout)


@client.event
async def on_ready():
    print('Logged in as')
    print(client.user.name)
    print(client.user.id)
    print('------')


#client.run('#########################################')
#client.run('##############','##########')