import disnake
import sqlite3
from disnake.ext import commands


bot = commands.Bot(command_prefix="!", help_command=None, intents=disnake.Intents.all())

conn = sqlite3.connect('main.db')
c = conn.cursor()


@bot.event
async def on_ready():
    print(f"Bot {bot.user} is ready to work!")

    c.execute("""CREATE TABLE IF NOT EXISTS users (
        name TEXT,
        score BIGINT,
        wins INTEGER,
        loses INTEGER,
        winrate TEXT
    )""")

    conn.commit()

    for guild in bot.guilds:
        for member in guild.members:
            if c.execute(f"SELECT name FROM users WHERE name = '{member}'").fetchone() is None:
                c.execute(f"INSERT INTO users VALUES ('{member}', 0, 0, 0, '0')")
            else:
                pass
    c.execute

    conn.commit()
    print('client connected')


@bot.slash_command(name='counter', description='Add win\lose')
async def addwinlose(ctx, user: disnake.User, score: int, winlose: str = commands.Param(choices=['Win', 'Lose'])):
    allowed_role = ctx.guild.get_role("1224836480531169474")
    if allowed_role in ctx.author.roles:
        c.execute("SELECT wins, loses, score FROM users WHERE name=?", (f'{user}',))
        wins, loses, score = c.fetchone()
        if winlose == "Win":
            modifier = 1 + score / (wins + 1)
            winrate = (((wins + 1) * modifier) / ((wins + 1) + loses))
            c.execute("UPDATE users SET wins=wins+1, score=score+?, winrate=? WHERE name=?", (score, f'{winrate}', f'{user}'))
        else:
            modifier = 1 + score / wins
            winrate = ((wins * modifier) / (wins + (loses + 1)))
            c.execute("UPDATE users SET loses=loses+1, score=score+?, winrate=? WHERE name=?", (score, f'{winrate}', f'{user}'))
        c.execute("SELECT score FROM users WHERE name=?", (f'{user}',))
        new_score = c.fetchone[0]
        await ctx.send(f'User {user} got {score} score points and now have {new_score}')
    else:
        await ctx.send("You do not have permissions to use this command.")
    conn.commit()


@bot.slash_command(name='info', description='Show user info')
async def info(ctx, user: disnake.User):
    allowed_role = ctx.guild.get_role("1224836480531169474")
    if allowed_role in ctx.author.roles:
        c.execute("SELECT wins, loses, score, winrate FROM users WHERE name=?", (f'{user}',))
        wins, loses, score, winrate = c.fetchone()

        embed = disnake.Embed(
            title=f"Info about {user}",
            description=f"Score: {score}\Wins: {wins}\Loses: {loses}\nWinrate: {winrate}",
            color=0x00ff00
        )

        await ctx.send(embed=embed)
    else:
        await ctx.send("You do not have permissions to use this command.")
    conn.commit()


@bot.slash_command(name='top10', description='Show top 10 users')
async def showtop10(ctx):
    allowed_role = ctx.guild.get_role("1224836480531169474")
    if allowed_role in ctx.author.roles:
        top10 = {}
        c.execute("SELECT name FROM users")
        names = c.fetchall()
        for name in names:
            c.execute("SELECT winrate FROM users WHERE name=?", (f'{name}',))
            top10[name] = c.fetchone()[0]
        sort_top10 = dict(sorted(top10.items(), key=lambda x: x[1], reverse=True))
        for item in sort_top10:
            top1 = item
            break
        embed = disnake.Embed(
            title=f"Top 10 users",
            description=f"1) {top1}: {sort_top10[top1]}",
            color=0x00ff00
        )
        del sort_top10[top1]
        count = 2
        for item in sort_top10:
            if count != 10:
                embed.add_field(value=f"{count}) {item}: {sort_top10[item]}", inline=False)
        await ctx.send(embed=embed)
    else:
        await ctx.send("You do not have permissions to use this command.")
    conn.commit()


bot.run('TOKEN')