import discord
from discord.ext import commands
import logging
import sqlite3

# Set up custom logging with date/time and error tags
logging.basicConfig(
    format="%(asctime)s %(levelname)-8s r6.bot %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
    level=logging.INFO
)

# Create intents
intents = discord.Intents.default()
intents.message_content = True  # Enable message content intent
intents.members = True 

# Initialize the bot with application_id
bot = commands.Bot(command_prefix='!', intents=intents, application_id='1341898739349983272')

# Database connection
def db_connect():
    conn = sqlite3.connect('r6bot.db', timeout=10, check_same_thread=False)
    cursor = conn.cursor()
    cursor.execute("PRAGMA journal_mode=WAL;")  # WAL mode for concurrent writes
    cursor.execute("PRAGMA synchronous=NORMAL;")  # Prevent excessive disk writes
    conn.commit()
    return conn

def update_user(user_id, username, guild_id, win=False, lose=False, nickname=None):
    conn = None
    try:
        conn = db_connect()
        cursor = conn.cursor()

        # Ensure table exists
        cursor.execute(""" 
            CREATE TABLE IF NOT EXISTS r6bot (
                user_id INTEGER NOT NULL,
                username TEXT NOT NULL,
                wins INTEGER NOT NULL DEFAULT 0,
                losses INTEGER NOT NULL DEFAULT 0,
                nickname TEXT,
                guild_id INTEGER NOT NULL,
                PRIMARY KEY (user_id, guild_id)
            )
        """)

        # Check if user already exists in the specific guild
        cursor.execute("SELECT wins, losses, username, nickname FROM r6bot WHERE user_id = ? AND guild_id = ?", (user_id, guild_id))
        user = cursor.fetchone()

        if user:
            # Extract existing data
            wins, losses, current_username, current_nickname = user

            # Increment win/loss count
            if win:
                wins += 1
            if lose:
                losses += 1

            # Update nickname if provided
            if nickname is not None:
                current_nickname = nickname

            # Update the existing record
            cursor.execute(""" 
                UPDATE r6bot 
                SET wins = ?, losses = ?, username = ?, nickname = ? 
                WHERE user_id = ? AND guild_id = ?
            """, (wins, losses, username, current_nickname, user_id, guild_id))

        else:
            # Insert new user entry for this guild
            cursor.execute(""" 
                INSERT INTO r6bot (user_id, username, wins, losses, nickname, guild_id) 
                VALUES (?, ?, ?, ?, ?, ?)
            """, (user_id, username, 1 if win else 0, 1 if lose else 0, nickname, guild_id))

        conn.commit()  # Save changes
        logging.info(f"Updated DB for {username} in guild {guild_id} (win={win}, lose={lose}, nickname={nickname})")

    except sqlite3.DatabaseError as e:
        logging.error(f"Database error: {e}")
    except Exception as e:
        logging.error(f"Unexpected error: {e}")
    finally:
        if conn:
            conn.close()

async def create_admin_role(guild):
    role_name = "Tracker Admin"
    # Check if the role already exists
    role = discord.utils.get(guild.roles, name=role_name)
    if not role:
        # Create the role with appropriate permissions
        permissions = discord.Permissions(manage_guild=True, administrator=True)
        role = await guild.create_role(name=role_name, permissions=permissions)
        logging.info(f"Created 'Tracker Admin' role in {guild.name}")

    return role

@bot.event
async def on_guild_join(guild):
    # Create 'Tracker Admin' role if it doesn't exist
    await create_admin_role(guild)

# Register a slash command for /hello
@bot.tree.command(name="hello")
async def hello(interaction: discord.Interaction):
    logging.info("Received /hello command")
    await interaction.response.send_message("Hello, world!")
    logging.info("Sent hello message")

# /win Command with defined members
@bot.tree.command(name="win", description="Increase wins for mentioned user(s)")
async def win(interaction: discord.Interaction, member1: discord.Member, member2: discord.Member = None, member3: discord.Member = None, member4: discord.Member = None, member5: discord.Member = None):
    # Check if the user has the 'Tracker Admin' role
    role = discord.utils.get(interaction.user.roles, name="Tracker Admin")
    if not role:
        await interaction.response.send_message("You do not have permission to use this command.", ephemeral=True)
        logging.info("Unauthorized user tried a command")
        return

    members = [m for m in [member1, member2, member3, member4, member5] if m]  # Remove None values
    if not members:
        await interaction.response.send_message("No valid members found.", ephemeral=True)
        logging.info("No valid members found for win command.")
        return

    updated_members = []
    guild_id = interaction.guild.id  # Capture the guild ID
    for member in members:
        update_user(member.id, member.name, guild_id, win=True)  # No nickname passed, only updating wins
        updated_members.append(member.name)

    await interaction.response.send_message(f"Updated wins for {', '.join(updated_members)}.")
    logging.info(f"Updated wins for {', '.join(updated_members)}")

@bot.tree.command(name="lose", description="Increase losses for mentioned user(s)")
async def lose(interaction: discord.Interaction, member1: discord.Member, member2: discord.Member = None, member3: discord.Member = None, member4: discord.Member = None, member5: discord.Member = None):
    # Check if the user has the 'Tracker Admin' role
    role = discord.utils.get(interaction.user.roles, name="Tracker Admin")
    if not role:
        await interaction.response.send_message("You do not have permission to use this command.", ephemeral=True)
        logging.info("Unauthorized user tried a command")
        return

    members = [m for m in [member1, member2, member3, member4, member5] if m]  # Remove None values
    if not members:
        await interaction.response.send_message("No valid members found.", ephemeral=True)
        logging.info("No valid members found for lose command.")
        return

    updated_members = []
    guild_id = interaction.guild.id  # Capture the guild ID
    for member in members:
        update_user(member.id, member.name, guild_id, lose=True)  # No nickname passed, only updating losses
        updated_members.append(member.name)

    await interaction.response.send_message(f"Updated losses for {', '.join(updated_members)}.")  # Fixed line
    logging.info(f"Updated losses for {', '.join(updated_members)}")  # Corrected placement


# /nickname Command
@bot.tree.command(name="nickname", description="Set yourself a new Nickname for this Server")
async def nickname(interaction: discord.Interaction, new_username: str = None):
    if new_username is None:
        await interaction.response.send_message("Please provide a new nickname.")
        logging.info("No username was provided")
    elif new_username == "":
        await interaction.response.send_message("Nicknames cannot be blank!")
        logging.info("Empty username was entered")
    else:
        # Check if the user is the server owner
        if interaction.user.id == interaction.guild.owner_id:
            await interaction.response.send_message("I cannot change the nickname of the server owner.")
            logging.info(f"Server owner {interaction.user.name} attempted to change nickname.")
        else:
            guild_id = interaction.guild.id  # Capture the guild ID
            # Update the nickname in the database
            update_user(interaction.user.id, interaction.user.name, guild_id, nickname=new_username)  

            # Update the nickname on the server
            try:
                await interaction.user.edit(nick=new_username)  # Change the user's nickname
                await interaction.response.send_message(f"Your nickname has been updated to {new_username}.")
                logging.info(f"Updated nickname for {interaction.user.name} to {new_username}")
            except discord.Forbidden:
                await interaction.response.send_message("I don't have permission to change your nickname.")
                logging.error(f"Permission denied when updating nickname for {interaction.user.name}")
            except discord.HTTPException as e:
                await interaction.response.send_message("An error occurred while updating your nickname.")
                logging.error(f"Error updating nickname for {interaction.user.name}: {e}")

# /WLR Command
@bot.tree.command(name="wlr", description="Display Win-Loss Ratio for a user")
async def wlr(interaction: discord.Interaction, user: discord.Member = None):
    # If no user is provided, use the user issuing the command
    if user is None:
        user = interaction.user

    guild_id = interaction.guild.id  # Capture the guild ID
    # Fetch user data from the database
    conn = None
    try:
        conn = db_connect()
        cursor = conn.cursor()

        # Retrieve user data from the database for the specific guild
        cursor.execute("SELECT wins, losses FROM r6bot WHERE user_id = ? AND guild_id = ?", (user.id, guild_id))
        user_data = cursor.fetchone()

        if user_data:
            wins, losses = user_data
            # Avoid division by zero and handle the case where either wins or losses are non-numeric
            if wins is None or losses is None or losses == 0:
                wlr = str(wins) + " Win to 0 Losses :sunglasses:"  # If no losses or invalid data, WLR is infinite
            else:
                wlr = wins / losses  # Calculate Win-Loss Ratio
            
            # Format WLR only if it's a number
            if isinstance(wlr, (int, float)):
                wlr = f"{wlr:.2f}"

            await interaction.response.send_message(f"{user.mention}'s Win-Loss Ratio (WLR): {wlr}")
            logging.info(f"Sent WLR for {user.name}: {wlr}")
        else:
            await interaction.response.send_message(f"No data found for {user.mention}.")
            logging.warning(f"No data found for {user.name} in the database.")
    except sqlite3.DatabaseError as e:
        logging.error(f"Database error: {e}")
        await interaction.response.send_message("An error occurred while fetching data.")
    except Exception as e:
        logging.error(f"Unexpected error: {e}")
        await interaction.response.send_message("An unexpected error occurred.")
    finally:
        if conn:
            conn.close()

bot.run("MTM0MTg5ODczOTM0OTk4MzI3Mg.GYFp_M.37g1sqY878-AIyhMuKV2YG8Bx5Wxazo76qytv4")