cogs/mail.py

# cogs/mail.py
import discord
from discord import app_commands
from discord.ext import commands
from database.db import db
import logging

# Import code encoder to show them their new card code
from utils.code_converter import encode_id

logger = logging.getLogger(__name__)

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

    @app_commands.command(name="mail", description="📬 Check your inbox for system messages and free rewards!")
    async def check_mail(self, interaction: discord.Interaction):
        await interaction.response.defer(ephemeral=True)

        async with db.pool.acquire() as conn:
            await conn.execute(
                "INSERT INTO users (user_id, username) VALUES ($1, $2) ON CONFLICT DO NOTHING", 
                interaction.user.id, str(interaction.user)
            )

            # 🔥 THE FIX: Use a LEFT JOIN to get the character name/rarity in ONE single query!
            mails = await conn.fetch("""
                SELECT m.mail_id, m.title, m.message, m.coins, m.dust, m.char_id,
                       c.name as char_name, c.rarity as char_rarity
                FROM user_mail m
                LEFT JOIN characters c ON m.char_id = c.char_id
                WHERE m.user_id = $1 AND m.is_claimed = FALSE
                ORDER BY m.created_at ASC
            """, interaction.user.id)

            if not mails:
                await interaction.followup.send("📭 **Your inbox is empty.** No new mail right now!")
                return

            total_coins = 0
            total_dust = 0
            granted_cards = []
            
            embed = discord.Embed(
                title="📬 You've Got Mail!",
                color=discord.Color.green()
            )

            # Process the messages
            for i, m in enumerate(mails):
                total_coins += m['coins']
                total_dust += m['dust']
                
                # Save the character blueprint ID if one was attached
                if m['char_id']:
                    granted_cards.append(m['char_id'])
                
                if i < 5:
                    rewards_text = ""
                    if m['coins'] > 0: rewards_text += f"🪙 **{m['coins']:,} Coins** "
                    if m['dust'] > 0: rewards_text += f"🎇 **{m['dust']:,} Dust** "
                    
                    # 🔥 THE FIX: Read directly from the joined SQL row, no extra queries needed!
                    if m['char_id'] and m['char_name']:
                        rewards_text += f"🎴 **{m['char_name']}** ({m['char_rarity']})"
                    
                    if not rewards_text: rewards_text = "No attached rewards."
                    
                    embed.add_field(
                        name=f"✉️ {m['title']}",
                        value=f"{m['message']}\n└ *Attached: {rewards_text}*",
                        inline=False
                    )

            if len(mails) > 5:
                embed.add_field(
                    name=f"...and {len(mails) - 5} more!", 
                    value="The rewards for older messages have also been claimed.", 
                    inline=False
                )

            # Database Transaction (Give rewards & mark as read)
            async with conn.transaction():
                if total_coins > 0 or total_dust > 0:
                    await conn.execute("""
                        UPDATE users 
                        SET coins = coins + $1, dust = dust + $2 
                        WHERE user_id = $3
                    """, total_coins, total_dust, interaction.user.id)
                
                for char_id in granted_cards:
                    await conn.execute("""
                        WITH update_char AS (
                            UPDATE characters 
                            SET acquired_count = acquired_count + 1 
                            WHERE char_id = $2 
                            RETURNING acquired_count
                        )
                        INSERT INTO user_characters (user_id, char_id, print_num) 
                        SELECT $1, $2, acquired_count FROM update_char;
                    """, interaction.user.id, char_id)
                
                await conn.execute("""
                    UPDATE user_mail 
                    SET is_claimed = TRUE 
                    WHERE user_id = $1 AND is_claimed = FALSE
                """, interaction.user.id)

            if total_coins > 0 or total_dust > 0 or granted_cards:
                embed.set_footer(text=f"Total Claimed: {total_coins:,} Coins | {total_dust:,} Dust | {len(granted_cards)} Cards")
            else:
                embed.set_footer(text="All mail read. No items attached.")

            await interaction.followup.send(embed=embed)

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