import discord
from discord.ext import commands
from discord import app_commands
import sqlite3

class Profile(commands.Cog):
    def __init__(self, bot):
        self.bot = bot

    @app_commands.command(name="profile", description="View your Rule34dle stats")
    async def profile(self, interaction: discord.Interaction, user: discord.User = None):
        if user is None:
            user = interaction.user

        user_id = user.id

        conn = sqlite3.connect("Goonerdle.db")
        cursor = conn.cursor()

        try:
            cursor.execute("""
                SELECT COUNT(*), COALESCE(SUM(Result), 0)
                FROM Result
                WHERE User_ID = ?
            """, (user_id,))

            row = cursor.fetchone()

            times_played = row[0]
            total_correct = row[1]

            cursor.execute("""SELECT COUNT(*)
                              FROM Result
                              WHERE User_ID = ? AND Result = 10
                           """, (user_id,))
            
            row = cursor.fetchone()
            perfect_games = row[0]

            if times_played == 0:
                await interaction.response.send_message(
                    f"{user.mention} has not played yet!"
                )
                return

            total_possible = times_played * 10
            total_incorrect = total_possible - total_correct

            percentage = (total_correct / total_possible) * 100

            msg = (
                f"**{user.name}'s Profile**\n\n"
                f"Games Played: **{times_played}**\n"
                f"Perfect Games Played: **{perfect_games}**\n"
                f"Correct: **{total_correct}**\n"
                f"Incorrect: **{total_incorrect}**\n"
                f"Accuracy: **{percentage:.2f}%**"
            )

            await interaction.response.send_message(msg)

        except Exception as e:
            print("Profile error:", e)
            await interaction.response.send_message("Error fetching profile.")

        finally:
            conn.close()


async def setup(bot):
    await bot.add_cog(Profile(bot))