import nextcord
from nextcord.ext import application_checks, commands
from nextcord import SlashOption
from dotenv import load_dotenv
from random import randint
import aiohttp
import aiosqlite as sqlite3
import traceback
from urllib.parse import urlparse
import os
import logging
import sys

firstrun = False

class Bot(commands.Bot):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)

    async def on_guild_remove(self, guild):
        await cur.execute("DELETE from teamsubscriptions WHERE serverid = ?", (guild.id,))
        await cur.execute("DELETE from liveupdate WHERE serverid = ?", (guild.id,))
        await cur.execute("DELETE from spotlightsubscriptions WHERE serverid = ?", (guild.id,))
        await db.commit()

load_dotenv()
bot = Bot()



@bot.event
async def on_ready():
    global firstrun
    if not firstrun:
        firstrun = True
        global db
        global cur
        db = await sqlite3.connect(os.getenv("DB_PATH"),timeout=30)
        cur = await db.cursor()
        res = await cur.execute("SELECT name FROM sqlite_master WHERE name='liveupdate'")
        if await res.fetchone() is None:
            await cur.execute("CREATE TABLE liveupdate(serverid INTEGER, userid INTEGER, channelid INTEGER NOT NULL, messageid INTEGER, gameid TEXT, offset INTEGER, classic INTEGER NOT NULL)")
            await db.commit()
        res = await cur.execute("SELECT name FROM sqlite_master WHERE name='spotlightsubscriptions'")
        if await res.fetchone() is None:
            await cur.execute("CREATE TABLE spotlightsubscriptions(serverid INTEGER, channelid INTEGER NOT NULL, classic INTEGER NOT NULL)")
            await db.commit()
        res = await cur.execute("SELECT name FROM sqlite_master WHERE name='teamsubscriptions'")
        if await res.fetchone() is None:
            await cur.execute("CREATE TABLE teamsubscriptions(serverid INTEGER, channelid INTEGER NOT NULL, teamid TEXT, classic INTEGER NOT NULL)")
            await db.commit()
        #await cur.execute("ALTER TABLE liveupdate ADD classic INTEGER NOT NULL DEFAULT 0")
        #await cur.execute("ALTER TABLE spotlightsubscriptions ADD classic INTEGER NOT NULL DEFAULT 0")
        #await cur.execute("ALTER TABLE teamsubscriptions ADD classic INTEGER NOT NULL DEFAULT 0")
        #await db.execute(f"""
         #               DELETE from liveupdate WHERE classic = 0
          #              """)
        await db.commit()
        bot.db = db
        bot.cur = cur
        guild_list = []
        async for guild in bot.fetch_guilds():
            guild_list.append(guild.id)
        res = await cur.execute("SELECT serverid FROM liveupdate UNION SELECT serverid FROM spotlightsubscriptions UNION SELECT serverid FROM teamsubscriptions")
        res = await res.fetchall()
        for [serverid] in res:
            if serverid not in guild_list:
                await cur.execute("DELETE from teamsubscriptions WHERE serverid = ?", (serverid,))
                await cur.execute("DELETE from liveupdate WHERE serverid = ?", (serverid,))
                await cur.execute("DELETE from spotlightsubscriptions WHERE serverid = ?", (serverid,))
        await db.commit()
        bot.load_extension('cogs.league') #Must load first as it contains autofill code referenced in team
        bot.load_extension('cogs.team') #Must load first as it contains autofill code
        bot.load_extension('cogs.liveupdate')
        bot.load_extension('cogs.error')
        bot.add_all_application_commands()
        await bot.sync_all_application_commands()

@bot.slash_command(
    name="managecog",
    description="manage cogs",
    guild_ids=[int(os.getenv("OWNER_GUILD"))],
)
@application_checks.is_owner()
async def managecog(interaction: nextcord.Interaction,
action: str = SlashOption(choices=["load", "unload", "reload","sync"]),
cog: str = SlashOption(required=False)
):
    errors = ""
    try:
        if action == "load":
            bot.load_extension(cog)
        elif action == "unload":
            bot.unload_extension(cog)
        elif action == "reload":
            bot.reload_extension(cog)
        elif action == "sync":
            bot.add_all_application_commands()
            await bot.sync_all_application_commands()
        await interaction.response.send_message(f"Done!" + (f"\n{errors}" if errors != "" else ""), ephemeral=True)

    except Exception as e:
        await interaction.response.send_message(e, ephemeral=True)
        

@bot.slash_command(
    name="clearliveupdates",
    description="Delete all live updates",
    guild_ids=[int(os.getenv("OWNER_GUILD"))],
)
@application_checks.is_owner()
async def delliveupdates(interaction: nextcord.Interaction,
confirm: str = SlashOption(required=False)
):
    if confirm == "yes, do as I say!":
        await db.execute(f"""DELETE from liveupdate""")
        await db.commit()
        await interaction.response.send_message("all gone...")
    else:
        res = await db.execute("SELECT COUNT (*) from liveupdate")
        res, = await res.fetchone()
        await interaction.response.send_message(f"There are currently {res} live updates")


bot.run(os.getenv("TOKEN"))