"""Routes: admin dashboard"""
from flask import render_template

from nexora.core import app
from nexora.database.connection import query
from nexora.security.permissions import admin_required, permission_required


@app.route("/admin/dashboard")
@admin_required
@permission_required("view_dashboard")
def admin_dashboard():
    stats = {
        "registrations": query("SELECT COUNT(*) AS c FROM registrations", one=True)["c"],
        "paid": query(
            "SELECT COUNT(*) AS c FROM registrations WHERE payment_status='paid'", one=True
        )["c"],
        "unpaid": query(
            "SELECT COUNT(*) AS c FROM registrations WHERE payment_status='unpaid'", one=True
        )["c"],
        "games": query("SELECT COUNT(*) AS c FROM games WHERE deleted_at = ''", one=True)["c"],
        "contacts": query("SELECT COUNT(*) AS c FROM contacts", one=True)["c"],
        "sponsors": query("SELECT COUNT(*) AS c FROM sponsors", one=True)["c"],
        "media": query("SELECT COUNT(*) AS c FROM media", one=True)["c"],
    }
    by_game = query(
        """
        SELECT games.title, COUNT(registrations.id) AS total
        FROM games LEFT JOIN registrations ON registrations.game_id = games.id
        GROUP BY games.id ORDER BY total DESC
        """
    )
    recent = query(
        """
        SELECT registrations.*,
               COALESCE(games.title, 'Deleted game') AS game_title,
               COALESCE(games.platform, '—') AS platform
        FROM registrations LEFT JOIN games ON games.id = registrations.game_id
        ORDER BY registrations.id DESC LIMIT 6
        """
    )
    return render_template("admin_dashboard.html", stats=stats, by_game=by_game, recent=recent)