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

class CustomCommands(commands.Cog):
    def __init__(self, bot: commands.Bot):
        self.bot = bot
        self.db_connection = sqlite3.connect("xalos_data.db")
        self.db_cursor = self.db_connection.cursor()
        self.db_cursor.execute("""
            CREATE TABLE IF NOT EXISTS custom_commands (
                guild_id INTEGER,
                command_name TEXT,
                response_text TEXT,
                PRIMARY KEY (guild_id, command_name)
            )
        """)
        self.db_connection.commit()
        # We define a static prefix for custom commands, as they are text-based.
        self.prefix = "!"

    @commands.Cog.listener()
    async def on_message(self, message: discord.Message):
        # Ignore bots, DMs, and messages that don't start with the prefix
        if message.author.bot or not message.guild or not message.content.startswith(self.prefix):
            return

        # Extract command name (e.g., "!hello" -> "hello")
        command_name = message.content[len(self.prefix):].split(' ')[0].lower()
        if not command_name:
            return

        # Look up the command in the database
        self.db_cursor.execute(
            "SELECT response_text FROM custom_commands WHERE guild_id = ? AND command_name = ?",
            (message.guild.id, command_name)
        )
        result = self.db_cursor.fetchone()

        if result:
            response_text = result[0]
            await message.channel.send(response_text)

    # --- Management Commands ---

    customcmd_group = app_commands.Group(name="customcommand", description="Verwaltet benutzerdefinierte Befehle.", default_permissions=discord.Permissions(manage_guild=True))

    @customcmd_group.command(name="add", description="Erstellt einen neuen benutzerdefinierten Befehl.")
    @app_commands.describe(name="Der Name des Befehls (ohne '!').", response="Die Antwort, die der Bot senden soll.")
    async def cc_add(self, interaction: discord.Interaction, name: str, response: str):
        command_name = name.lower().split(' ')[0] # Ensure single word and lowercase
        try:
            self.db_cursor.execute("INSERT INTO custom_commands (guild_id, command_name, response_text) VALUES (?, ?, ?)", (interaction.guild.id, command_name, response))
            self.db_connection.commit()
            await interaction.response.send_message(f"✅ Der Befehl `{self.prefix}{command_name}` wurde erstellt.", ephemeral=True)
        except sqlite3.IntegrityError:
            await interaction.response.send_message(f"⚠️ Ein Befehl mit dem Namen `{command_name}` existiert bereits. Bitte entferne ihn zuerst.", ephemeral=True)

    @customcmd_group.command(name="remove", description="Löscht einen benutzerdefinierten Befehl.")
    @app_commands.describe(name="Der Name des Befehls, der gelöscht werden soll (ohne '!').")
    async def cc_remove(self, interaction: discord.Interaction, name: str):
        command_name = name.lower()
        self.db_cursor.execute("DELETE FROM custom_commands WHERE guild_id = ? AND command_name = ?", (interaction.guild.id, command_name))
        if self.db_cursor.rowcount > 0:
            self.db_connection.commit()
            await interaction.response.send_message(f"✅ Der Befehl `{self.prefix}{command_name}` wurde gelöscht.", ephemeral=True)
        else:
            await interaction.response.send_message(f"❌ Kein Befehl mit dem Namen `{command_name}` gefunden.", ephemeral=True)

    @customcmd_group.command(name="list", description="Zeigt alle benutzerdefinierten Befehle an.")
    async def cc_list(self, interaction: discord.Interaction):
        self.db_cursor.execute("SELECT command_name FROM custom_commands WHERE guild_id = ?", (interaction.guild.id,))
        commands_list = [row[0] for row in self.db_cursor.fetchall()]

        if not commands_list:
            await interaction.response.send_message("Es gibt keine benutzerdefinierten Befehle auf diesem Server.", ephemeral=True)
            return

        embed = discord.Embed(
            title="Benutzerdefinierte Befehle",
            description=", ".join(f"`{self.prefix}{cmd}`" for cmd in commands_list),
            color=discord.Color.green()
        )
        await interaction.response.send_message(embed=embed, ephemeral=True)

async def setup(bot: commands.Bot):
    cog = CustomCommands(bot)
    bot.tree.add_command(cog.customcmd_group)
    await bot.add_cog(cog)