import discord
from discord import app_commands
from discord.ext import commands
import os
from dotenv import load_dotenv
from datetime import datetime
import json
import mysql.connector
import aiohttp
from typing import List

# Load environment variables
load_dotenv()
TOKEN = os.getenv('DISCORD_TOKEN')

# Bot setup
intents = discord.Intents.default()
intents.message_content = True
intents.guilds = True

bot = commands.Bot(command_prefix='!', intents=intents)

# MySQL connection setup
MYSQL_HOST = os.getenv('MYSQL_HOST', 'localhost')
MYSQL_PORT = int(os.getenv('MYSQL_PORT', '3306'))
MYSQL_USER = os.getenv('MYSQL_USER', 'root')
MYSQL_PASSWORD = os.getenv('MYSQL_PASSWORD', '')
MYSQL_DATABASE = os.getenv('MYSQL_DATABASE', 'tickets')

def get_db_connection():
    return mysql.connector.connect(
        host=MYSQL_HOST,
        port=MYSQL_PORT,
        user=MYSQL_USER,
        password=MYSQL_PASSWORD,
        database=MYSQL_DATABASE,
        charset='utf8mb4',
        collation='utf8mb4_unicode_ci'
    )

def init_db():
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Create tables with utf8mb4 charset
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS tickets (
            ticket_id VARCHAR(32) PRIMARY KEY,
            message_id BIGINT,
            channel_id BIGINT,
            title VARCHAR(255),
            project VARCHAR(255),
            status VARCHAR(100),
            creator BIGINT,
            created_at VARCHAR(32),
            reference_message_id VARCHAR(32),
            original_text TEXT,
            download_link VARCHAR(512),
            archived BOOLEAN DEFAULT FALSE
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS server_settings (
            guild_id BIGINT PRIMARY KEY,
            active_channel_id BIGINT,
            archive_channel_id BIGINT
        ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
    ''')
    
    # Convert existing tables to utf8mb4 if needed
    try:
        cursor.execute('ALTER TABLE tickets CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
        cursor.execute('ALTER TABLE server_settings CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci')
        print("Database tables converted to utf8mb4")
    except Exception as e:
        # Tables might already be utf8mb4 or might not exist yet
        print(f"Table conversion info: {e}")
    
    # Add new columns if they don't exist
    try:
        cursor.execute('ALTER TABLE tickets ADD COLUMN original_text TEXT')
        print("Added original_text column")
    except Exception as e:
        print(f"original_text column info: {e}")
    
    try:
        cursor.execute('ALTER TABLE tickets ADD COLUMN download_link VARCHAR(512)')
        print("Added download_link column")
    except Exception as e:
        print(f"download_link column info: {e}")
    
    conn.commit()
    cursor.close()
    conn.close()

# Server settings DB functions

def save_server_settings(guild_id, active_channel_id, archive_channel_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('''
        REPLACE INTO server_settings (guild_id, active_channel_id, archive_channel_id)
        VALUES (%s, %s, %s)
    ''', (guild_id, active_channel_id, archive_channel_id))
    conn.commit()
    cursor.close()
    conn.close()

def load_server_settings(guild_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute('SELECT * FROM server_settings WHERE guild_id = %s', (guild_id,))
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result

def get_next_ticket_id():
    """Generate the next ticket ID based on existing tickets in database"""
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT COUNT(*) as count FROM tickets')
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    count = result[0] if result else 0
    return f"TICKET-{count + 1:04d}"

def save_ticket(ticket_id, ticket):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('''
        REPLACE INTO tickets (ticket_id, message_id, channel_id, title, project, status, creator, created_at, reference_message_id, original_text, download_link, archived)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    ''', (
        ticket_id,
        ticket['message_id'],
        ticket['channel_id'],
        ticket['title'],
        ticket['project'],
        ticket['status'],
        ticket['creator'],
        ticket['created_at'],
        ticket.get('reference_message_id'),
        ticket.get('original_text'),
        ticket.get('download_link'),
        ticket.get('archived', False)
    ))
    conn.commit()
    cursor.close()
    conn.close()

def load_ticket(ticket_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    cursor.execute('SELECT * FROM tickets WHERE ticket_id = %s', (ticket_id,))
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result

def load_all_tickets(archived=None):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)
    if archived is None:
        cursor.execute('SELECT * FROM tickets')
    else:
        cursor.execute('SELECT * FROM tickets WHERE archived = %s', (archived,))
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    return results

# Project cache for autocomplete
project_cache = []
last_project_fetch = None

# Ticket statuses
class TicketStatus:
    PENDING = "⏳ Pending"
    IN_PROGRESS = "🔄 In Progress"
    COMPLETED = "✅ Completed"
    CANCELLED = "❌ Cancelled"

async def fetch_projects() -> List[str]:
    """Fetch available projects from devanturas.net/versions"""
    global project_cache, last_project_fetch
    
    # Cache for 1 hour
    from datetime import timedelta
    if last_project_fetch and (datetime.utcnow() - last_project_fetch) < timedelta(hours=1):
        return project_cache
    
    try:
        async with aiohttp.ClientSession() as session:
            async with session.get('https://devanturas.net/versions') as response:
                if response.status == 200:
                    data = await response.json()
                    project_cache = list(data.keys())
                    last_project_fetch = datetime.utcnow()
                    return project_cache
    except Exception as e:
        print(f"Error fetching projects: {e}")
    
    # Fallback to cached data or empty list
    return project_cache if project_cache else []

async def project_autocomplete(
    interaction: discord.Interaction,
    current: str,
) -> List[app_commands.Choice[str]]:
    """Autocomplete for project names"""
    projects = await fetch_projects()
    
    # Filter projects based on current input
    filtered = [p for p in projects if current.lower() in p.lower()]
    
    # Return up to 25 choices (Discord limit)
    return [
        app_commands.Choice(name=project, value=project)
        for project in filtered[:25]
    ]

@bot.event
async def on_ready():
    print(f'{bot.user} has connected to Discord!')
    init_db()
    try:
        synced = await bot.tree.sync()
        print(f"Synced {len(synced)} command(s)")
    except Exception as e:
        print(f"Error syncing commands: {e}")

@bot.tree.command(name="setup", description="Setup the bot channels")
@app_commands.describe(
    active_channel="The channel for active tickets",
    archive_channel="The channel for archived tickets"
)
async def setup(
    interaction: discord.Interaction,
    active_channel: discord.TextChannel,
    archive_channel: discord.TextChannel
):
    guild_id = interaction.guild.id if interaction.guild else None
    if not guild_id:
        await interaction.response.send_message("❌ This command must be used in a server.", ephemeral=True)
        return
    save_server_settings(guild_id, active_channel.id, archive_channel.id)
    await interaction.response.send_message(
        f"✅ Configuration saved!\n"
        f"Active tickets: {active_channel.mention}\n"
        f"Archive: {archive_channel.mention}",
        ephemeral=True
    )

@bot.tree.command(name="ticket", description="Create a new project update ticket")
@app_commands.describe(
    project_name="The project name from devanturas.net/projects",
    title="Brief title for this update",
    message_id="(Optional) Discord message ID to reference"
)
@app_commands.autocomplete(project_name=project_autocomplete)
async def create_ticket(
    interaction: discord.Interaction,
    project_name: str,
    title: str,
    message_id: str = None
):
    guild_id = interaction.guild.id if interaction.guild else None
    settings = load_server_settings(guild_id)
    if not settings or not settings.get("active_channel_id"):
        await interaction.response.send_message(
            "❌ Bot not configured! Please use `/setup` first.",
            ephemeral=True
        )
        return
    active_channel = bot.get_channel(settings["active_channel_id"])
    if not active_channel:
        await interaction.response.send_message(
            "❌ Active channel not found! Please reconfigure with `/setup`.",
            ephemeral=True
        )
        return
    
    # Create ticket ID from database
    ticket_id = get_next_ticket_id()
    
    # Fetch original message if message_id provided
    original_text = None
    original_author = None
    if message_id:
        try:
            ref_message = await interaction.channel.fetch_message(int(message_id))
            original_text = ref_message.content[:500]  # Limit to 500 chars
            original_author = ref_message.author
        except:
            pass
    
    # Create embed for the ticket with improved design
    embed = discord.Embed(
        title=f"{title}",
        description=f"**Project:** {project_name}\n**Ticket ID:** `{ticket_id}`",
        color=0x5865F2,  # Discord Blurple
        timestamp=datetime.utcnow()
    )
    
    # Add original request if available
    if original_text and original_author:
        embed.add_field(
            name="Original Request",
            value=f"> {original_text}\n\n— {original_author.mention}",
            inline=False
        )
    
    # Status and creator info
    embed.add_field(name="Status", value=TicketStatus.PENDING, inline=True)
    embed.add_field(name="Created By", value=interaction.user.mention, inline=True)
    embed.add_field(name="Created", value=f"<t:{int(datetime.utcnow().timestamp())}:R>", inline=True)
    
    # Project links
    embed.add_field(
        name="Links",
        value=f"[All Projects](https://devanturas.net/projects) • [{project_name}](https://devanturas.net/projects/{project_name})",
        inline=False
    )
    
    embed.set_footer(text=f"Update Ticket System • {ticket_id}")
    
    # Send to active channel
    ticket_message = await active_channel.send(embed=embed)
    
    # Store ticket data
    ticket = {
        "message_id": ticket_message.id,
        "channel_id": active_channel.id,
        "title": title,
        "project": project_name,
        "status": TicketStatus.PENDING,
        "creator": interaction.user.id,
        "created_at": datetime.utcnow().isoformat(),
        "reference_message_id": message_id,
        "original_text": original_text,
        "download_link": None
    }
    save_ticket(ticket_id, ticket)
    
    # Confirm to user
    await interaction.response.send_message(
        f"✅ Ticket created successfully!\n"
        f"**Ticket ID:** `{ticket_id}`\n"
        f"**Channel:** {active_channel.mention}\n"
        f"Use `/status {ticket_id}` to update the status.",
        ephemeral=True
    )

@bot.tree.command(name="status", description="Update ticket status")
@app_commands.describe(
    ticket_id="The ticket ID (e.g., TICKET-0001)",
    new_status="The new status for the ticket",
    download_link="(Optional) Download link for update - use when marking as completed"
)
@app_commands.choices(new_status=[
    app_commands.Choice(name="⏳ Pending", value="pending"),
    app_commands.Choice(name="🔄 In Progress", value="in_progress"),
    app_commands.Choice(name="✅ Completed", value="completed"),
    app_commands.Choice(name="❌ Cancelled", value="cancelled"),
])
async def update_status(
    interaction: discord.Interaction,
    ticket_id: str,
    new_status: str,
    download_link: str = None
):
    """Update the status of a ticket"""
    
    # Convert to uppercase for consistency
    ticket_id = ticket_id.upper()
    
    ticket = load_ticket(ticket_id)
    if not ticket:
        await interaction.response.send_message(
            f"❌ Ticket `{ticket_id}` not found!",
            ephemeral=True
        )
        return
    
    # Map status choice to display text
    status_map = {
        "pending": TicketStatus.PENDING,
        "in_progress": TicketStatus.IN_PROGRESS,
        "completed": TicketStatus.COMPLETED,
        "cancelled": TicketStatus.CANCELLED
    }
    
    old_status = ticket["status"]
    new_status_text = status_map[new_status]
    ticket["status"] = new_status_text
    
    # Add download link if provided and status is completed
    if download_link and new_status == "completed":
        ticket["download_link"] = download_link
    
    save_ticket(ticket_id, ticket)
    
    # Get the ticket message
    channel = bot.get_channel(ticket["channel_id"])
    if not channel:
        await interaction.response.send_message(
            "❌ Channel not found!",
            ephemeral=True
        )
        return
    
    try:
        message = await channel.fetch_message(ticket["message_id"])
        embed = message.embeds[0]
        
        # Update the status field
        for i, field in enumerate(embed.fields):
            if "Status" in field.name:
                embed.set_field_at(i, name="Status", value=new_status_text, inline=True)
                break
        
        # Change color based on status
        if new_status == "completed":
            embed.color = 0x57F287  # Green
        elif new_status == "in_progress":
            embed.color = 0xFEE75C  # Yellow
        elif new_status == "cancelled":
            embed.color = 0xED4245  # Red
        else:
            embed.color = 0x5865F2  # Blurple
        
        # Add download link if provided
        if download_link and new_status == "completed":
            # Update or add download link field
            download_field_exists = False
            for i, field in enumerate(embed.fields):
                if "Download" in field.name:
                    embed.set_field_at(i, name="Download", value=f"[Download Update]({download_link})", inline=False)
                    download_field_exists = True
                    break
            
            if not download_field_exists:
                embed.add_field(
                    name="Download",
                    value=f"[Download Update]({download_link})",
                    inline=False
                )
        
        # Add status update log
        timestamp_now = int(datetime.utcnow().timestamp())
        update_text = f"{interaction.user.mention} • {old_status} → {new_status_text} • <t:{timestamp_now}:R>"
        
        # Check if update history field exists
        history_exists = False
        for i, field in enumerate(embed.fields):
            if "Update History" in field.name:
                current_history = field.value
                embed.set_field_at(i, name="Update History", value=f"{current_history}\n{update_text}", inline=False)
                history_exists = True
                break
        
        if not history_exists:
            embed.add_field(
                name="Update History",
                value=update_text,
                inline=False
            )
        
        await message.edit(embed=embed)
        
        # If completed, archive the ticket
        if new_status == "completed":
            await archive_ticket(interaction, ticket_id, message, channel)
        else:
            await interaction.response.send_message(
                f"✅ Ticket `{ticket_id}` status updated to {new_status_text}",
                ephemeral=True
            )
    
    except discord.NotFound:
        await interaction.response.send_message(
            "❌ Ticket message not found!",
            ephemeral=True
        )
    except Exception as e:
        await interaction.response.send_message(
            f"❌ Error updating ticket: {str(e)}",
            ephemeral=True
        )

async def archive_ticket(interaction, ticket_id, message, current_channel):
    guild_id = interaction.guild.id if interaction.guild else None
    settings = load_server_settings(guild_id)
    archive_channel_id = settings.get("archive_channel_id") if settings else None
    if not archive_channel_id:
        await interaction.response.send_message(
            "⚠️ Status updated, but no archive channel configured. Use `/setup` to configure.",
            ephemeral=True
        )
        return
    archive_channel = bot.get_channel(archive_channel_id)
    if not archive_channel:
        await interaction.response.send_message(
            "⚠️ Archive channel not found!",
            ephemeral=True
        )
        return
    
    # Copy message to archive
    embed = message.embeds[0]
    embed.add_field(
        name="📦 Archived",
        value=f"Moved to archive on {datetime.utcnow().strftime('%Y-%m-%d %H:%M UTC')}",
        inline=False
    )
    
    await archive_channel.send(embed=embed)
    
    # Delete from active channel
    await message.delete()
    
    # Update ticket storage
    ticket = load_ticket(ticket_id)
    ticket["channel_id"] = archive_channel.id
    ticket["archived"] = True
    save_ticket(ticket_id, ticket)
    
    await interaction.response.send_message(
        f"✅ Ticket `{ticket_id}` marked as completed and archived to {archive_channel.mention}",
        ephemeral=True
    )

@bot.tree.command(name="list", description="List all active tickets")
async def list_tickets(interaction: discord.Interaction):
    """List all active tickets"""
    
    active_tickets = [
        (t["ticket_id"], t) for t in load_all_tickets(archived=False)
    ]
    
    if not active_tickets:
        await interaction.response.send_message(
            "📭 No active tickets found.",
            ephemeral=True
        )
        return
    
    embed = discord.Embed(
        title="🎫 Active Tickets",
        color=discord.Color.blue(),
        timestamp=datetime.utcnow()
    )
    
    for ticket_id, ticket in active_tickets[:25]:  # Discord limit
        embed.add_field(
            name=f"{ticket_id}: {ticket['title']}",
            value=f"Project: {ticket['project']}\nStatus: {ticket['status']}",
            inline=False
        )
    
    if len(active_tickets) > 25:
        embed.set_footer(text=f"Showing 25 of {len(active_tickets)} tickets")
    
    await interaction.response.send_message(embed=embed, ephemeral=True)

@bot.tree.command(name="info", description="Get detailed information about a ticket")
@app_commands.describe(ticket_id="The ticket ID (e.g., TICKET-0001)")
async def ticket_info(interaction: discord.Interaction, ticket_id: str):
    """Get detailed information about a specific ticket"""
    
    ticket_id = ticket_id.upper()
    
    ticket = load_ticket(ticket_id)
    if not ticket:
        await interaction.response.send_message(
            f"❌ Ticket `{ticket_id}` not found!",
            ephemeral=True
        )
        return
    
    creator = await bot.fetch_user(ticket["creator"])
    
    embed = discord.Embed(
        title=f"🎫 {ticket_id} Details",
        description=ticket["title"],
        color=discord.Color.blue()
    )
    
    embed.add_field(name="📋 Project", value=ticket["project"], inline=True)
    embed.add_field(name="📊 Status", value=ticket["status"], inline=True)
    embed.add_field(name="👤 Creator", value=creator.mention, inline=True)
    embed.add_field(name="📅 Created", value=ticket["created_at"][:10], inline=True)
    embed.add_field(name="🔗 Reference Message", value=f"`{ticket['reference_message_id']}`", inline=False)
    embed.add_field(
        name="📦 Archived",
        value="Yes" if ticket.get("archived") else "No",
        inline=True
    )
    
    await interaction.response.send_message(embed=embed, ephemeral=True)

# Run the bot
if __name__ == "__main__":
    if not TOKEN:
        print("ERROR: DISCORD_TOKEN not found in .env file!")
    else:
        bot.run(TOKEN)