import sqlite3
import numpy as np
import re
import time
import datetime
import asyncio
import discord
from discord.ext import commands

Token = "NDQ2OTU3NTE0MzUzODAzMjY0.DeW17w.zUoKvoNKBPYsOeMj4UhXWLv2D_g"
client = commands.Bot(command_prefix="")
client.remove_command("help")
conn = sqlite3.connect("PointsBot.db")
c = conn.cursor()
unix = time.time()
IsUpdating = False
date = str(datetime.datetime.fromtimestamp(unix).strftime("%Y-%m-%d %H:%M:%S"))
c.execute("CREATE TABLE IF NOT EXISTS users (date TEXT, name TEXT, score NUM, write NUM)")
conn.commit()

@client.event
async def on_ready():
    print("Logged in")
    IsUpdating = True
    for server in client.servers:
        for channel in server.channels:
            if str(channel.type) == "text":
                async for message in client.logs_from(channel, limit=9999999):
                    author = message.author
                    try:
                        c.execute("SELECT score FROM users WHERE name IS '"+str(author)+"'")
                        for row in c.fetchone():
                            c.execute("UPDATE users SET score = score+1 WHERE name is '"+str(author)+"' AND write = 0")
                            conn.commit()

                    except TypeError:
                        c.execute("INSERT INTO users VALUES('"+str(date)+"', '"+str(author)+"', 1, 0)")
                        conn.commit()

            else:
                continue

    IsUpdating == False

    c.execute("UPDATE users SET write = 1")
    conn.commit()

@client.event
async def on_message(message):
    author = message.author
    channel = message.channel
    try:
        c.execute("SELECT name FROM users WHERE name IS '"+str(author)+"'")
        for row in c.fetchone():
            c.execute("UPDATE users SET score = score+1 WHERE name IS '"+str(author)+"'")
            c.execute("UPDATE users SET write = 1 WHERE name is '"+str(author)+"'")
            conn.commit()

    except TypeError:
        c.execute("INSERT INTO users VALUES('"+str(date)+"', '"+str(author)+"', 1, 0)")
        conn.commit()

    if message.content.startswith("!rank") and IsUpdating == False:
        c.execute("SELECT name, score FROM users WHERE name IS '"+str(author)+"'")
        for row in c.fetchmany():
            print(str(row))

    elif message.content.startswith("!points") and IsUpdating == True:
        await client.send_message("Sorry im updating")

    if message.content.startswith("!lboard") and IsUpdating == False:
        c.execute("SELECT name, score FROM users ORDER BY score DESC")
        for row in c.fetchmany(size=5):
            await client.send_message(channel, str(row))

    elif message.content.startswith("!lboard") and IsUpdating == True:
        await client.send_message(channel, "Sorry im updateding")

    if message.content.startswith("!help"):
        embed = discord.Embed(
        colour = discord.Color.blue()
        )

        embed.set_author(name="Help")
        embed.add_field(name = "!points", value = "Displays your amount of points", inline=False)
        embed.add_field(name = "!lboard", value = "Shows the top five users with the highest score", inline=False)
        embed.add_field(name = "!help", value = "displays this message", inline=False)

        await client.send_message(message.channel, embed=embed)

@client.event
async def on_message_delete(message):
    author = message.author
    c.execute("UPDATE users SET score = score-1 WHERE name is '"+str(author)+"'")
    conn.commit()

client.run(Token)