import asyncio
from disnake.ext import commands, tasks
import disnake
import sqlite3
import time


class Bookclub(commands.Cog):
    booktypes = [
        "1-Abjuration-Visitor Center",
        "2-Enchantment-Balcony",
        "3-Illusion-Violet Hold",
        "4-Necromancy-Inn 2nd Floor",
        "5-Divination-Citadel Top",
        "6-Introduction-Violet Gate",
        "7-Transmutation-Inn 1st Floor",
        "8-Conjuration-Citadel Bottom",
    ]
    def __init__(self,bot:commands.InteractionBot):
        self.bot = bot
        self.advert_updater.start()

    async def update_advert(self,guild_id:int):
        connection = sqlite3.connect("store.db")
        cursor = connection.cursor()

        advert_info = cursor.execute("SELECT * FROM adverts WHERE server_id = ?",(guild_id,)).fetchone()
        advert_data = cursor.execute("SELECT * FROM advert_data WHERE reported_at > unixepoch('now','-4 hours') ORDER BY layer, reported_at").fetchall()
        book_types = cursor.execute("SELECT * FROM books").fetchall()
        cursor.close()
        connection.close()

        timers = {}

        for entry in advert_data:
            if entry[1] not in timers.keys():
                timers[entry[1]] = {}
            timers[entry[1]][entry[2]] = entry[3]
            timers[entry[1]] = dict(sorted(timers[entry[1]].items(), key=lambda item:item[1]))

        command_id = self.bot.get_global_command_named(name="book_report")

        embed = disnake.Embed()
        embed.set_thumbnail("https://i.imgur.com/Jaa3CgV.png")
        embed.title = "🌟 -  Dalaran Bookclub  - 🌟"
        embed.description = f"Use the command </book_report:{command_id.id}>  **<-Click it!** to mark what book and what layer you found it at.\n Timers are for when the spawn window opens.\n Window is open for 1 hour.\nOld timers get purged when not updated"
        for layer,times in timers.items():
            tempstring = ""
            for book,time in times.items():
                tempstring += f"{book_types[book-1][2]} - <t:{time+10800}:R> - <t:{time+10800}:t>\n"
            embed.add_field(name=f"Layer {layer}:",value=tempstring,inline=False)
        embed.set_footer(text= "Use reactions below to select which books you need, and the bot will ping you when its seen - Hover to see name")
        message:disnake.Message = await self.bot.get_channel(advert_info[2]).fetch_message(advert_info[3])
        await message.edit(content="",embed=embed)

    @commands.slash_command(description="[Admin]Create Bookclub advert",default_member_permissions=disnake.Permissions(manage_guild=True))
    async def setup_bookclub(self,
    inter:disnake.ApplicationCommandInteraction):
        connection = sqlite3.connect("store.db")
        cursor = connection.cursor()
        await inter.response.defer(ephemeral="Setting things up")
        message = await inter.channel.send("Advert coming soon")
        cursor.execute(f"INSERT INTO adverts(server_id,channel_id,message_id) VALUES(?,?,?) ON CONFLICT DO UPDATE SET channel_id = excluded.channel_id, message_id = excluded.message_id",(inter.guild_id,inter.channel_id,message.id))
        connection.commit()
        cursor.close()
        connection.close()
        await self.update_advert(inter.guild_id)

    @commands.slash_command(description="Report a book sighting")
    async def book_report(self,
    inter:disnake.ApplicationCommandInteraction,
    layer:int = commands.Param(choices=[x for x in range(1,7)],description="Which layer was this?"),
    book:str = commands.Param(choices=booktypes,description="What book was it?"),
    real_book:str = commands.Param(choices=["True","False"],default="False",description="Is this book real? Defaults to False if not added. Pings people if True")):
        connection = sqlite3.connect("store.db")
        cursor = connection.cursor()
        cursor.execute(f"INSERT INTO advert_data(layer,book_id,reported_at) VALUES(?,?,?)",(layer,book[0],int(time.time())))
        connection.commit()
        if real_book == "True":
            role_id = cursor.execute("SELECT role_id FROM books WHERE id = ?",(book[0],)).fetchone()[0]
            advert_info = cursor.execute("SELECT * FROM adverts WHERE server_id = ?",(inter.guild_id,)).fetchone()
            await self.bot.get_channel(advert_info[2]).send(content=f"Look alive! <@&{role_id}> spawned on Layer {layer} \n{book}",delete_after=300)
        cursor.close()
        connection.close()
        await self.update_advert(inter.guild_id)
        await inter.response.send_message(ephemeral=True,content="Added to list")

    @commands.Cog.listener()
    async def on_message(self,message:disnake.Message):
        if message.channel.id != 1034201792352886784: return
        if message.author == self.bot.user: return
        await message.delete()

    @tasks.loop(hours=1)
    async def advert_updater(self):
        await self.bot.wait_until_ready()
        for guild in self.bot.guilds:
            await self.update_advert(guild.id)
        print("Updated Adverts")