# web/api.py
from quart import Quart, jsonify, render_template, redirect, request, session, current_app, make_response
import httpx
import os
import sys
import json
import math
import time
import asyncio
import logging
from datetime import datetime, timedelta

sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from config import DB_DSN
from database.db import db  
from utils.image_prep import prepare_database_image
from services.gacha_service import GachaService
import services.importer_service as imp_srv

# ==========================================
# 📡 THE LIVE CONSOLE INTERCEPTOR
# ==========================================
CONSOLE_LOGS = asyncio.Queue()
LOG_HISTORY = []

class DashboardLogHandler(logging.Handler):
    def emit(self, record):
        timestamp = self.formatter.formatTime(record, "%H:%M:%S")
        
        # ⚡ THE FIX: .replace('\n', '<br>') ensures tracebacks don't break the SSE stream
        msg = self.format(record).replace('<', '&lt;').replace('>', '&gt;').replace('\n', '<br>')
        
        if record.levelno >= logging.ERROR:
            color = "text-red-500 font-bold"
        elif record.levelno == logging.WARNING:
            color = "text-yellow-400"
        elif record.levelno == logging.INFO:
            color = "text-gray-300"
        else:
            color = "text-gray-500"

        log_entry = f'<span class="text-gray-600">[{timestamp}]</span> <span class="text-purple-400">[{record.name}]</span> <span class="{color}">{msg}</span>'
        
        LOG_HISTORY.append(log_entry)
        if len(LOG_HISTORY) > 300:
            LOG_HISTORY.pop(0)
            
        try:
            loop = asyncio.get_running_loop()
            loop.call_soon_threadsafe(CONSOLE_LOGS.put_nowait, log_entry)
        except RuntimeError:
            pass

dash_handler = DashboardLogHandler()
dash_handler.setFormatter(logging.Formatter('[%(asctime)s] [%(name)s] %(levelname)s: %(message)s'))
logging.getLogger().addHandler(dash_handler)
# ==========================================

CLIENT_ID = os.environ.get("DISCORD_CLIENT_ID")
CLIENT_SECRET = os.environ.get("DISCORD_CLIENT_SECRET")
ADMIN_ID = os.environ.get("ADMIN_DISCORD_ID")
REDIRECT_URI = os.environ.get("REDIRECT_URI", "https://amit9011-gacha-bot.hf.space/callback")

SUPABASE_URL = os.environ.get("SUPABASE_URL")
SUPABASE_KEY = os.environ.get("SUPABASE_KEY")
BUCKET_NAME = os.environ.get("SUPABASE_BUCKET", "gacha-images")

app = Quart(__name__)
app.secret_key = os.environ.get("QUART_SECRET_KEY", os.urandom(24))

@app.route("/login")
async def login():
    discord_auth_url = (f"https://discord.com/api/oauth2/authorize?client_id={CLIENT_ID}&redirect_uri={REDIRECT_URI}&response_type=code&scope=identify")
    return redirect(discord_auth_url)

@app.route("/callback")
async def callback():
    code = request.args.get("code")
    if not code: return "Error", 400
    data = {"client_id": CLIENT_ID, "client_secret": CLIENT_SECRET, "grant_type": "authorization_code", "code": code, "redirect_uri": REDIRECT_URI}
    async with httpx.AsyncClient() as client:
        token_resp = await client.post("https://discord.com/api/oauth2/token", data=data)
        access_token = token_resp.json().get("access_token")
        if not access_token: return "Failed to get token", 400
        user_resp = await client.get("https://discord.com/api/users/@me", headers={"Authorization": f"Bearer {access_token}"})
        user_json = user_resp.json()
        if str(user_json.get("id")) == ADMIN_ID:
            session["admin_logged_in"] = True
            session["username"] = user_json.get("username")
            return redirect("/")
        return "❌ ACCESS DENIED.", 403

@app.route("/logout")
async def logout():
    session.clear()
    return redirect("/")

@app.route("/")
async def analytics():
    if not session.get("admin_logged_in"): return redirect("/login")
    if not db.pool: return "Database not connected", 500

    def format_huge(num):
        if num >= 1_000_000_000_000_000: return f"{num / 1_000_000_000_000_000:.2f}Q"
        if num >= 1_000_000_000_000: return f"{num / 1_000_000_000_000:.2f}T"
        if num >= 1_000_000_000: return f"{num / 1_000_000_000:.2f}B"
        if num >= 1_000_000: return f"{num / 1_000_000:.2f}M"
        return f"{num:,}"

    async with db.pool.acquire() as conn:
        await conn.execute("""
            CREATE TABLE IF NOT EXISTS economy_logs (
                id SERIAL PRIMARY KEY,
                user_id BIGINT,
                amount INT NOT NULL,
                currency_type VARCHAR(10) NOT NULL,
                transaction_type VARCHAR(10) NOT NULL,
                source VARCHAR(50) NOT NULL,
                created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
            )
        """)

        velocity_rows = await conn.fetch("""
            SELECT 
                TO_CHAR(created_at, 'YYYY-MM-DD') as log_date, 
                SUM(CASE WHEN transaction_type = 'mint' THEN amount ELSE 0 END) as minted,
                SUM(CASE WHEN transaction_type = 'burn' THEN amount ELSE 0 END) as burned
            FROM economy_logs
            WHERE created_at >= NOW() - INTERVAL '7 days' AND currency_type = 'coins'
            GROUP BY TO_CHAR(created_at, 'YYYY-MM-DD')
            ORDER BY log_date ASC
        """)

        dates = [(datetime.utcnow() - timedelta(days=i)).strftime('%Y-%m-%d') for i in range(6, -1, -1)]
        mint_data = {d: 0 for d in dates}
        burn_data = {d: 0 for d in dates}
        
        for r in velocity_rows:
            d = r['log_date']
            if d in mint_data:
                mint_data[d] = r['minted']
                burn_data[d] = r['burned']

        economy = await conn.fetchrow("SELECT SUM(coins) as t_coins, SUM(dust) as t_dust FROM users")
        total_coins = economy['t_coins'] or 0
        total_dust = economy['t_dust'] or 0
        total_players = await conn.fetchval("SELECT COUNT(*) FROM users")
        cards_in_wild = await conn.fetchval("SELECT COUNT(*) FROM user_characters")
        
        top_players = await conn.fetch("SELECT user_id, username, coins, dust FROM users ORDER BY coins DESC NULLS LAST LIMIT 5")
        top_players_list = [{"username": p["username"], "formatted_coins": format_huge(p["coins"] or 0), "formatted_dust": format_huge(p["dust"] or 0), "coins": p["coins"], "dust": p["dust"]} for p in top_players]
        
        elements = await conn.fetch("SELECT COALESCE(element, 'None') as elem, COUNT(*) as cnt FROM characters GROUP BY elem")
        popular_cards = await conn.fetch("SELECT c.name, COUNT(uc.instance_id) as count FROM user_characters uc JOIN characters c ON uc.char_id = c.char_id GROUP BY c.char_id, c.name ORDER BY count DESC LIMIT 5")
        
        market_stats = await conn.fetch("SELECT currency_type, COUNT(*) as cnt FROM market_listings GROUP BY currency_type")
        market_dict = {row['currency_type']: row['cnt'] for row in market_stats}
        total_market = market_dict.get('coins', 0) + market_dict.get('dust', 0)

        user_ids = await conn.fetch("SELECT user_id FROM users")
        user_id_set = {r['user_id'] for r in user_ids}

    bot = current_app.bot
    server_count = len(bot.guilds) if bot else 0
    guild_stats = []
    
    if bot:
        for guild in bot.guilds:
            players_in_guild = sum(1 for m in guild.members if m.id in user_id_set)
            if players_in_guild > 0:
                icon_url = guild.icon.url if guild.icon else "https://cdn.discordapp.com/embed/avatars/0.png"
                guild_stats.append({"name": guild.name, "players": players_in_guild, "icon": icon_url, "id": str(guild.id)})
    
    guild_stats.sort(key=lambda x: x['players'], reverse=True)
    top_guilds = guild_stats[:10]
        
    return await render_template(
        "analytics.html", username=session.get("username"), raw_coins=total_coins, raw_dust=total_dust, 
        total_coins=format_huge(total_coins), total_dust=format_huge(total_dust), total_players=total_players, 
        cards_in_wild=cards_in_wild, top_players=top_players_list, chart_labels=json.dumps([r['elem'] for r in elements]), 
        chart_data=json.dumps([r['cnt'] for r in elements]), pop_labels=json.dumps([r['name'] for r in popular_cards]),
        pop_data=json.dumps([r['count'] for r in popular_cards]), market_coins=market_dict.get('coins', 0),
        market_dust=market_dict.get('dust', 0), total_market=total_market, server_count=server_count, top_guilds=top_guilds,
        vel_labels=json.dumps(dates), vel_mint=json.dumps(list(mint_data.values())), vel_burn=json.dumps(list(burn_data.values()))
    )

@app.route("/wipe_inventory/<int:user_id>", methods=["POST"])
async def wipe_inventory(user_id):
    if not session.get("admin_logged_in"): return redirect("/login")
    async with db.pool.acquire() as conn:
        async with conn.transaction():
            await conn.execute("UPDATE users SET profile_slot_1=NULL, profile_slot_2=NULL, profile_slot_3=NULL, profile_slot_4=NULL, profile_slot_5=NULL, profile_slot_6=NULL, profile_slot_7=NULL, profile_slot_8=NULL, showcase_id=NULL WHERE user_id=$1", user_id)
            await conn.execute("DELETE FROM user_teams WHERE user_id=$1", user_id)
            await conn.execute("DELETE FROM expeditions WHERE user_id=$1", user_id)
            await conn.execute("DELETE FROM active_auctions WHERE seller_id=$1", user_id)
            await conn.execute("DELETE FROM market_listings WHERE seller_id=$1", user_id)
            await conn.execute("DELETE FROM user_characters WHERE user_id=$1", user_id)
    return redirect("/players")

@app.route("/global_wipe_cards", methods=["POST"])
async def global_wipe_cards():
    if not session.get("admin_logged_in"): return redirect("/login")
    async with db.pool.acquire() as conn:
        async with conn.transaction():
            await conn.execute("UPDATE users SET profile_slot_1=NULL, profile_slot_2=NULL, profile_slot_3=NULL, profile_slot_4=NULL, profile_slot_5=NULL, profile_slot_6=NULL, profile_slot_7=NULL, profile_slot_8=NULL, showcase_id=NULL")
            await conn.execute("TRUNCATE TABLE user_characters CASCADE")
    return redirect("/")

@app.route("/characters")
async def characters_tab():
    if not session.get("admin_logged_in"): return redirect("/login")
    page = int(request.args.get("page", 1))
    
    search_query = request.args.get("q", "").strip()
    rarity_filter = request.args.get("rarity", "").strip()
    series_filter = request.args.get("series", "").strip()
    
    per_page = 50
    offset = (page - 1) * per_page
    
    async with db.pool.acquire() as conn:
        series_rows = await conn.fetch("SELECT DISTINCT series FROM characters WHERE series IS NOT NULL AND series != '' ORDER BY series")
        all_series = [r['series'] for r in series_rows]

        base_query = "FROM characters WHERE 1=1"
        args = []
        arg_idx = 1
        
        if search_query:
            base_query += f" AND (name ILIKE ${arg_idx} OR series ILIKE ${arg_idx})"
            args.append(f"%{search_query}%")
            arg_idx += 1
            
        if rarity_filter:
            base_query += f" AND rarity = ${arg_idx}"
            args.append(rarity_filter)
            arg_idx += 1
            
        if series_filter:
            base_query += f" AND series ILIKE ${arg_idx}"
            args.append(f"%{series_filter}%")
            arg_idx += 1
            
        total_chars = await conn.fetchval(f"SELECT COUNT(*) {base_query}", *args)
        total_pages = math.ceil(total_chars / per_page) if total_chars else 1
        
        args.extend([per_page, offset])
        rows = await conn.fetch(
            f"SELECT char_id, name, series, rarity, element, atk, def, image_url, c6_gif_url, custom_frame_url {base_query} ORDER BY char_id DESC LIMIT ${arg_idx} OFFSET ${arg_idx+1}", 
            *args
        )
            
    return await render_template(
        "characters.html", characters=[dict(row) for row in rows], username=session.get("username"), 
        current_page=page, total_pages=total_pages, total_chars=total_chars, 
        search_query=search_query, rarity_filter=rarity_filter, series_filter=series_filter, all_series=all_series
    )

@app.route("/add_character_web", methods=["POST"])
async def add_character_web():
    if not session.get("admin_logged_in"): return redirect("/login")
    form_data = await request.form
    files = await request.files
    image = files.get("image")
    if not image or image.filename == '': return "❌ Error: You must upload an image file.", 400
    name = form_data.get("name").strip()
    series = form_data.get("series", "Unknown").strip()
    rarity = form_data.get("rarity", "C")
    element = form_data.get("element", "None").strip()
    atk = int(form_data.get("atk", 50))
    defense = int(form_data.get("def", 50))
    try:
        raw_bytes = image.read()
        optimized_bytes = await asyncio.to_thread(prepare_database_image, raw_bytes)
        safe_filename = f"web_{int(time.time())}_{name.replace(' ', '_').lower()}.webp"
        upload_url = f"{SUPABASE_URL}/storage/v1/object/{BUCKET_NAME}/{safe_filename}"
        headers = {"Authorization": f"Bearer {SUPABASE_KEY}", "apikey": SUPABASE_KEY, "Content-Type": "image/webp"}
        async with httpx.AsyncClient() as client:
            resp = await client.post(upload_url, headers=headers, content=optimized_bytes)
            if resp.status_code not in [200, 201]: return f"❌ Supabase API Error: {resp.text}", 500
        public_url = f"{SUPABASE_URL}/storage/v1/object/public/{BUCKET_NAME}/{safe_filename}"
        async with db.pool.acquire() as conn:
            await conn.execute("INSERT INTO characters (name, series, rarity, element, atk, def, image_url) VALUES ($1, $2, $3, $4, $5, $6, $7)", name, series, rarity, element, atk, defense, public_url)
        await GachaService.rebuild_pools()
        return redirect("/characters")
    except Exception as e: return f"❌ Internal Error: {e}", 500

@app.route("/edit/<int:char_id>", methods=["POST"])
async def edit_character(char_id):
    if not session.get("admin_logged_in"): return redirect("/login")
    f = await request.form
    gif_url = f.get("c6_gif_url")
    gif_url = gif_url if gif_url and gif_url.strip() else None
    frame_url = f.get("custom_frame_url")
    frame_url = frame_url if frame_url and frame_url.strip() else None
    async with db.pool.acquire() as conn:
        await conn.execute("UPDATE characters SET name=$1, series=$2, rarity=$3, element=$4, atk=$5, def=$6, c6_gif_url=$7, custom_frame_url=$8 WHERE char_id=$9", f.get("name"), f.get("series"), f.get("rarity"), f.get("element"), int(f.get("atk", 0)), int(f.get("def", 0)), gif_url, frame_url, char_id)
    await GachaService.rebuild_pools()
    return redirect("/characters")

@app.route("/delete/<int:char_id>", methods=["POST"])
async def delete_character(char_id):
    if not session.get("admin_logged_in"): return redirect("/login")
    async with db.pool.acquire() as conn:
        ownership_count = await conn.fetchval("SELECT COUNT(*) FROM user_characters WHERE char_id = $1", char_id)
        if ownership_count > 5: return (f"<h1 style='color:red; text-align:center; margin-top:50px;'>🚨 WIPE PROTECTION TRIGGERED 🚨<br><br>Cannot delete! <b>{ownership_count} players</b> currently own this card.</h1>"), 403
        char_data = await conn.fetchrow("SELECT image_url FROM characters WHERE char_id = $1", char_id)
        if char_data and char_data['image_url'] and SUPABASE_URL and SUPABASE_KEY:
            try:
                filename = char_data['image_url'].split('/')[-1]
                delete_url = f"{SUPABASE_URL}/storage/v1/object/{BUCKET_NAME}/{filename}"
                headers = {"Authorization": f"Bearer {SUPABASE_KEY}", "apikey": SUPABASE_KEY}
                async with httpx.AsyncClient() as client:
                    await client.delete(delete_url, headers=headers)
            except Exception as e: print(f"Failed to delete image: {e}")
        async with conn.transaction():
            await conn.execute("UPDATE users SET showcase_id = NULL WHERE showcase_id IN (SELECT instance_id FROM user_characters WHERE char_id = $1)", char_id)
            await conn.execute("UPDATE user_teams SET slot_1 = NULL WHERE slot_1 IN (SELECT instance_id FROM user_characters WHERE char_id = $1)", char_id)
            await conn.execute("UPDATE user_teams SET slot_2 = NULL WHERE slot_2 IN (SELECT instance_id FROM user_characters WHERE char_id = $1)", char_id)
            await conn.execute("UPDATE user_teams SET slot_3 = NULL WHERE slot_3 IN (SELECT instance_id FROM user_characters WHERE char_id = $1)", char_id)
            await conn.execute("DELETE FROM expeditions WHERE instance_id IN (SELECT instance_id FROM user_characters WHERE char_id = $1)", char_id)
            await conn.execute("DELETE FROM user_characters WHERE char_id = $1", char_id)
            await conn.execute("DELETE FROM characters WHERE char_id = $1", char_id)
    await GachaService.rebuild_pools()
    return redirect("/characters")

@app.route("/players")
async def players_tab():
    if not session.get("admin_logged_in"): return redirect("/login")
    page = int(request.args.get("page", 1))
    per_page = 50
    offset = (page - 1) * per_page
    async with db.pool.acquire() as conn:
        total_users = await conn.fetchval("SELECT COUNT(*) FROM users")
        total_pages = math.ceil(total_users / per_page) if total_users else 1
        rows = await conn.fetch(
            "SELECT user_id, username, coins, dust, is_banned, is_locked, "
            "(SELECT COUNT(*) FROM user_characters WHERE user_characters.user_id = users.user_id) as card_count "
            "FROM users ORDER BY coins DESC NULLS LAST LIMIT $1 OFFSET $2", 
            per_page, offset
        )
    return await render_template("players.html", players=[dict(r) for r in rows], username=session.get("username"), current_page=page, total_pages=total_pages, total_users=total_users)

@app.route("/edit_wallet/<int:user_id>", methods=["POST"])
async def edit_wallet(user_id):
    if not session.get("admin_logged_in"): return redirect("/login")
    f = await request.form
    async with db.pool.acquire() as conn:
        await conn.execute("UPDATE users SET coins=$1, dust=$2 WHERE user_id=$3", int(f.get("coins", 0)), int(f.get("dust", 0)), user_id)
    return redirect("/players")

@app.route("/toggle_ban/<int:user_id>", methods=["POST"])
async def toggle_ban(user_id):
    if not session.get("admin_logged_in"): return redirect("/login")
    async with db.pool.acquire() as conn:
        current = await conn.fetchval("SELECT is_banned FROM users WHERE user_id = $1", user_id)
        await conn.execute("UPDATE users SET is_banned = $1 WHERE user_id = $2", not current, user_id)
    return redirect("/players")

@app.route("/toggle_lock/<int:user_id>", methods=["POST"])
async def toggle_lock(user_id):
    if not session.get("admin_logged_in"): return redirect("/login")
    async with db.pool.acquire() as conn:
        current = await conn.fetchval("SELECT is_locked FROM users WHERE user_id = $1", user_id)
        await conn.execute("UPDATE users SET is_locked = $1 WHERE user_id = $2", not current, user_id)
    return redirect("/players")

@app.route("/api/inventory/<int:user_id>")
async def get_inventory(user_id):
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    async with db.pool.acquire() as conn:
        rows = await conn.fetch("SELECT uc.instance_id, c.name, c.rarity, c.element, c.image_url FROM user_characters uc JOIN characters c ON uc.char_id = c.char_id WHERE uc.user_id = $1 ORDER BY c.rarity DESC, c.name ASC", user_id)
    return jsonify([dict(r) for r in rows])

@app.route("/banner")
async def banner_tab():
    if not session.get("admin_logged_in"): return redirect("/login")
    async with db.pool.acquire() as conn:
        await conn.execute("CREATE TABLE IF NOT EXISTS banner_rates (rarity VARCHAR(50) PRIMARY KEY, drop_rate FLOAT)")
        count = await conn.fetchval("SELECT COUNT(*) FROM banner_rates")
        if count == 0:
            for r, d in [("C", 50.0), ("R", 30.0), ("SR", 15.0), ("SSR", 4.0), ("UR", 1.0)]:
                await conn.execute("INSERT INTO banner_rates (rarity, drop_rate) VALUES ($1, $2) ON CONFLICT (rarity) DO NOTHING", r, d)
        rows = await conn.fetch("SELECT rarity, drop_rate FROM banner_rates")
        rates = {row['rarity']: row['drop_rate'] for row in rows}

        await conn.execute("""
            CREATE TABLE IF NOT EXISTS current_event (
                id INT PRIMARY KEY DEFAULT 1,
                event_type VARCHAR(50),
                event_value VARCHAR(255),
                is_active BOOLEAN DEFAULT FALSE,
                end_time TIMESTAMP WITH TIME ZONE
            )
        """)
        try: await conn.execute("ALTER TABLE current_event ADD COLUMN IF NOT EXISTS end_time TIMESTAMP WITH TIME ZONE")
        except: pass
        
        await conn.execute("INSERT INTO current_event (id, event_type, event_value, is_active) VALUES (1, 'series', '', FALSE) ON CONFLICT DO NOTHING")
        current_event = await conn.fetchrow("SELECT *, (end_time > NOW()) as is_valid FROM current_event WHERE id = 1")

    return await render_template("banner.html", rates=rates, current_event=dict(current_event) if current_event else {}, username=session.get("username"))

@app.route("/edit_banner", methods=["POST"])
async def edit_banner_rates():
    if not session.get("admin_logged_in"): return redirect("/login")
    f = await request.form
    async with db.pool.acquire() as conn:
        for rarity in ["C", "R", "SR", "SSR", "UR"]:
            await conn.execute("UPDATE banner_rates SET drop_rate=$1 WHERE rarity=$2", float(f.get(rarity, 0)), rarity)
    from services.gacha_service import GachaService
    await GachaService.clear_rates_cache()
    return redirect("/banner")

@app.route("/toggle_event", methods=["POST"])
async def toggle_event():
    if not session.get("admin_logged_in"): return redirect("/login")
    f = await request.form
    action = f.get("action")
    
    async with db.pool.acquire() as conn:
        if action == "start":
            event_type = f.get("event_type")
            event_value = f.get("event_value").strip()
            days = int(f.get("duration_days", 7))
            await conn.execute(
                "UPDATE current_event SET event_type=$1, event_value=$2, is_active=TRUE, end_time=NOW() + INTERVAL '1 day' * $3 WHERE id=1", 
                event_type, event_value, days
            )
        elif action == "stop":
            await conn.execute("UPDATE current_event SET is_active=FALSE WHERE id=1")
            
    return redirect("/banner")

@app.route("/api/search_characters")
async def search_characters():
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    q = request.args.get("q", "")
    async with db.pool.acquire() as conn:
        if q: rows = await conn.fetch("SELECT char_id, name, rarity, image_url FROM characters WHERE name ILIKE $1 LIMIT 12", f"%{q}%")
        else: rows = await conn.fetch("SELECT char_id, name, rarity, image_url FROM characters ORDER BY char_id DESC LIMIT 12")
    return jsonify([dict(r) for r in rows])

@app.route("/send_player_mail", methods=["POST"])
async def send_player_mail():
    if not session.get("admin_logged_in"): return redirect("/login")
    f = await request.form
    char_id = int(f.get("char_id")) if f.get("char_id") else None
    async with db.pool.acquire() as conn:
        await conn.execute("ALTER TABLE user_mail ADD COLUMN IF NOT EXISTS char_id INT DEFAULT NULL;")
        await conn.execute("INSERT INTO user_mail (user_id, title, message, char_id) VALUES ($1, $2, $3, $4)", int(f.get("user_id")), f.get("title", "A Special Gift!"), f.get("message", ""), char_id)
    return redirect("/players")

@app.route("/mail")
async def mail_tab():
    if not session.get("admin_logged_in"): return redirect("/login")
    success = request.args.get("success")
    async with db.pool.acquire() as conn:
        await conn.execute("CREATE TABLE IF NOT EXISTS user_mail (mail_id SERIAL PRIMARY KEY, user_id BIGINT REFERENCES users(user_id) ON DELETE CASCADE, title VARCHAR(255), message TEXT, coins INT DEFAULT 0, dust INT DEFAULT 0, char_id INT DEFAULT NULL, is_claimed BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)")
        player_count = await conn.fetchval("SELECT COUNT(*) FROM users")
    return await render_template("mail.html", username=session.get("username"), player_count=player_count, success=success)

@app.route("/send_mail", methods=["POST"])
async def send_global_mail():
    if not session.get("admin_logged_in"): return redirect("/login")
    f = await request.form
    async with db.pool.acquire() as conn:
        await conn.execute("INSERT INTO user_mail (user_id, title, message, coins, dust) SELECT user_id, $1, $2, $3, $4 FROM users", f.get("title"), f.get("message"), int(f.get("coins", 0)), int(f.get("dust", 0)))
    return redirect("/mail?success=1")

@app.route("/assets", methods=["GET", "POST"])
async def assets_tab():
    if not session.get("admin_logged_in"): return redirect("/login")
    message = ""
    os.makedirs("assets", exist_ok=True)
    if request.method == "POST":
        files = await request.files
        frame_file = files.get("frame_image")
        if frame_file and frame_file.filename != '':
            await frame_file.save(os.path.join("assets", "frame.png"))
            message += "✅ Card Frame updated successfully! "
        font_file = files.get("font_file")
        if font_file and font_file.filename != '':
            await font_file.save(os.path.join("assets", "font.ttf"))
            message += "✅ Font updated successfully! "
    return await render_template("assets.html", username=session.get("username"), message=message)

@app.route("/health", methods=["GET"])
async def health_check():
    return "OK", 200

# --- IMPORTER ROUTES ---
@app.route("/importer")
async def importer_tab():
    if not session.get("admin_logged_in"): return redirect("/login")
    return await render_template("importer.html", username=session.get("username"))

@app.route("/api/run_importer", methods=["POST"])
async def run_importer():
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    data = await request.json
    anime_name = data.get("anime_name", "").strip()
    pages = int(data.get("pages", 1))
    
    if imp_srv.CURRENT_IMPORT_TASK and not imp_srv.CURRENT_IMPORT_TASK.done():
        return jsonify({"status": "already_running"})
        
    imp_srv.CURRENT_IMPORT_TASK = asyncio.create_task(imp_srv.ImporterService.run_import_task(anime_name, pages))
    return jsonify({"status": "started"})

@app.route("/api/stop_importer", methods=["POST"])
async def stop_importer():
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    if imp_srv.CURRENT_IMPORT_TASK and not imp_srv.CURRENT_IMPORT_TASK.done():
        imp_srv.CURRENT_IMPORT_TASK.cancel()
        return jsonify({"status": "stopped"})
    return jsonify({"status": "not_running"})

@app.route("/api/importer_stream")
async def importer_stream():
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    async def log_generator():
        for msg in list(imp_srv.LOG_HISTORY): 
            yield f"data: {msg}\n\n"
            
        while True:
            try:
                # ⚡ FAST 5s TIMEOUT
                msg = await asyncio.wait_for(imp_srv.IMPORTER_LOGS.get(), timeout=5.0)
                yield f"data: {msg}\n\n"
            except asyncio.TimeoutError:
                # ⚡ W3C STRICT SSE COMMENT (Browser ignores this, but it keeps the TCP socket alive)
                yield ": keepalive\n\n"
                
    response = await make_response(log_generator(), {
        "Content-Type": "text/event-stream", 
        "Cache-Control": "no-cache", 
        "Connection": "keep-alive",
        "X-Accel-Buffering": "no"
    })
    response.timeout = None
    return response

# --- TERMINAL CONSOLE ROUTES ---
@app.route("/console")
async def console_tab():
    if not session.get("admin_logged_in"): return redirect("/login")
    return await render_template("console.html", username=session.get("username"))

@app.route("/api/console_stream")
async def console_stream():
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    async def log_generator():
        for msg in list(LOG_HISTORY): 
            yield f"data: {msg}\n\n"
            
        while True:
            try:
                msg = await asyncio.wait_for(CONSOLE_LOGS.get(), timeout=5.0)
                yield f"data: {msg}\n\n"
            except asyncio.TimeoutError:
                # ⚡ W3C STRICT SSE COMMENT
                yield ": keepalive\n\n"
                
    response = await make_response(log_generator(), {
        "Content-Type": "text/event-stream", 
        "Cache-Control": "no-cache", 
        "Connection": "keep-alive",
        "X-Accel-Buffering": "no"
    })
    response.timeout = None
    return response

# 🚨 INSTANT CARD CONFISCATION ROUTE
@app.route("/api/confiscate/<int:instance_id>", methods=["POST"])
async def api_confiscate(instance_id):
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    async with db.pool.acquire() as conn:
        async with conn.transaction():
            exists = await conn.fetchval("SELECT 1 FROM user_characters WHERE instance_id = $1", instance_id)
            if not exists:
                return jsonify({"status": "error", "message": "Card not found"})
            
            # Wipe it from any visual profile slots before deleting
            await conn.execute("UPDATE users SET showcase_id = NULL WHERE showcase_id = $1", instance_id)
            for i in range(1, 9):
                await conn.execute(f"UPDATE users SET profile_slot_{i} = NULL WHERE profile_slot_{i} = $1", instance_id)
            
            # The ON DELETE CASCADE in your schema automatically handles user_teams, expeditions, and the market
            await conn.execute("DELETE FROM user_characters WHERE instance_id = $1", instance_id)
            
    return jsonify({"status": "success"})

@app.route("/recover_db")
async def recover_db():
    if not session.get("admin_logged_in"): return redirect("/login")
    
    CLEAN_KEY = str(SUPABASE_KEY).strip(' \n\r"\'')
    if CLEAN_KEY.startswith("sb_"):
        CLEAN_KEY = CLEAN_KEY[3:]
    
    recovered_count = 0
    offset = 0
    limit = 1000
    
    async with httpx.AsyncClient() as client:
        async with db.pool.acquire() as conn:
            while True:
                url = f"{SUPABASE_URL}/storage/v1/object/list/{BUCKET_NAME}"
                
                headers = {
                    "Authorization": f"Bearer {CLEAN_KEY}",
                    "apikey": CLEAN_KEY,
                    "Content-Type": "application/json"
                }
                
                payload = {"prefix": "", "limit": limit, "offset": offset, "sortBy": {"column": "name", "order": "asc"}}
                
                resp = await client.post(url, headers=headers, json=payload)
                
                if resp.status_code != 200:
                    safe_key_preview = CLEAN_KEY[:15] + "..." if len(CLEAN_KEY) > 15 else "TOO_SHORT"
                    error_html = f"""
                    <body style="background:#111; color:white; font-family:sans-serif; text-align:center; padding-top:50px;">
                        <h1 style="color:#ef4444;">🛑 STILL BLOCKED!</h1>
                        <p style="font-size: 20px;"><b>Status Code:</b> {resp.status_code}</p>
                        <div style="background:#222; padding:20px; border-radius:10px; display:inline-block; margin-top:20px; text-align:left; color:#ffb86c; font-family:monospace;">
                            {resp.text}
                        </div>
                        <p style="margin-top:30px; color:#9ca3af;">Key Preview (first 15 chars): {safe_key_preview}</p>
                    </body>
                    """
                    return error_html
                
                files = resp.json()
                if not isinstance(files, list) or len(files) == 0: 
                    break
                
                for file in files:
                    filename = file.get("name")
                    if not filename or filename.startswith(".emptyFolder"): 
                        continue
                    
                    public_url = f"{SUPABASE_URL}/storage/v1/object/public/{BUCKET_NAME}/{filename}"
                    exists = await conn.fetchval("SELECT 1 FROM characters WHERE image_url = $1", public_url)
                    if exists: 
                        continue
                    
                    clean_filename = filename.replace(".webp", "").replace(".png", "").replace(".jpg", "")
                    parts = clean_filename.split("_")
                    
                    if parts[0] == "web": name_parts = parts[2:]
                    elif parts[0].isdigit() and len(parts) > 2: name_parts = parts[2:]
                    else: name_parts = parts
                    
                    char_name = " ".join(name_parts).title()
                    if not char_name: char_name = "Unknown"
                    
                    await conn.execute(
                        "INSERT INTO characters (name, rarity, element, atk, def, image_url, series, source) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)",
                        char_name, "C", "None", 50, 50, public_url, "Recovered Series", "RecoveryScript"
                    )
                    recovered_count += 1
                    
                if len(files) < limit: 
                    break
                offset += limit
                
    from services.gacha_service import GachaService
    await GachaService.rebuild_pools()
    
    html = f"""
    <body style="background:#111; color:white; font-family:sans-serif; text-align:center; padding-top:100px;">
        <h1 style="color:#22c55e;">✅ Resurrection Complete!</h1>
        <p>Successfully scanned the storage bucket and linked <b>{recovered_count}</b> images back into the database.</p>
        <br><br>
        <a href="/characters" style="background:#3b82f6; color:white; padding:10px 20px; text-decoration:none; border-radius:5px; font-weight:bold;">Return to Characters</a>
    </body>
    """
    return html

@app.route("/api/bulk_delete", methods=["POST"])
async def bulk_delete_characters():
    if not session.get("admin_logged_in"): return jsonify({"error": "Unauthorized"}), 403
    
    data = await request.json
    char_ids = data.get("char_ids", [])
    if not char_ids: return jsonify({"status": "error", "message": "No characters selected."})

    try: char_ids = [int(cid) for cid in char_ids]
    except ValueError: return jsonify({"status": "error", "message": "Invalid ID format."})

    async with db.pool.acquire() as conn:
        protected_rows = await conn.fetch("SELECT char_id FROM user_characters WHERE char_id = ANY($1) GROUP BY char_id HAVING COUNT(*) > 5", char_ids)
        protected_ids = {r['char_id'] for r in protected_rows}
        
        to_delete = [cid for cid in char_ids if cid not in protected_ids]
        
        if not to_delete:
            return jsonify({"status": "protected", "message": f"All {len(char_ids)} selected characters are protected (owned by >5 players)."})

        char_data = await conn.fetch("SELECT image_url FROM characters WHERE char_id = ANY($1)", to_delete)
        
        if SUPABASE_URL and SUPABASE_KEY:
            headers = {"Authorization": f"Bearer {SUPABASE_KEY}", "apikey": SUPABASE_KEY}
            async def delete_storage_files():
                async with httpx.AsyncClient() as client:
                    tasks = []
                    for row in char_data:
                        if row['image_url']:
                            filename = row['image_url'].split('/')[-1]
                            delete_url = f"{SUPABASE_URL}/storage/v1/object/{BUCKET_NAME}/{filename}"
                            tasks.append(client.delete(delete_url, headers=headers))
                    if tasks:
                        await asyncio.gather(*tasks, return_exceptions=True)
            
            asyncio.create_task(delete_storage_files())

        async with conn.transaction():
            await conn.execute("UPDATE users SET showcase_id = NULL WHERE showcase_id IN (SELECT instance_id FROM user_characters WHERE char_id = ANY($1))", to_delete)
            await conn.execute("UPDATE user_teams SET slot_1 = NULL WHERE slot_1 IN (SELECT instance_id FROM user_characters WHERE char_id = ANY($1))", to_delete)
            await conn.execute("UPDATE user_teams SET slot_2 = NULL WHERE slot_2 IN (SELECT instance_id FROM user_characters WHERE char_id = ANY($1))", to_delete)
            await conn.execute("UPDATE user_teams SET slot_3 = NULL WHERE slot_3 IN (SELECT instance_id FROM user_characters WHERE char_id = ANY($1))", to_delete)
            await conn.execute("DELETE FROM expeditions WHERE instance_id IN (SELECT instance_id FROM user_characters WHERE char_id = ANY($1))", to_delete)
            await conn.execute("DELETE FROM user_characters WHERE char_id = ANY($1)", to_delete)
            await conn.execute("DELETE FROM characters WHERE char_id = ANY($1)", to_delete)

    from services.gacha_service import GachaService
    await GachaService.rebuild_pools()
    
    return jsonify({
        "status": "success", 
        "deleted": len(to_delete), 
        "protected": len(protected_ids),
        "message": f"Successfully deleted {len(to_delete)} characters."
    })