"""Routes: public"""
import random
from datetime import datetime
from flask import flash, redirect, render_template, request, url_for

from nexora.config import PUBLIC_CACHE_SECONDS
from nexora.core import app
from nexora.database.connection import cached_query, execute, query
from nexora.utils.validators import sanitize_field, validate_email_format


# ────────────────────────────────────────────
# Public routes
# ────────────────────────────────────────────
# How many game cards the homepage shows before the "View All" button.
INDEX_GAME_LIMIT = 3


@app.route("/")
def index():
    # Newest game cards first, older ones still available behind "View All".
    all_games = cached_query("home_games", "SELECT * FROM games WHERE active = 1 AND deleted_at = '' ORDER BY id DESC", ttl=PUBLIC_CACHE_SECONDS)
    games = all_games[:INDEX_GAME_LIMIT]
    games_total = len(all_games)
    hero_slides = cached_query("home_hero", "SELECT * FROM media WHERE kind = 'hero' ORDER BY id DESC", ttl=PUBLIC_CACHE_SECONDS)
    gallery = cached_query("home_gallery", "SELECT * FROM media WHERE kind = 'gallery' ORDER BY id DESC LIMIT 10", ttl=PUBLIC_CACHE_SECONDS)
    leaders = cached_query("home_leaders", "SELECT * FROM leaders WHERE active = 1 ORDER BY sort_order, id", ttl=PUBLIC_CACHE_SECONDS)
    sponsors = cached_query("home_sponsors", "SELECT * FROM sponsors WHERE active = 1 ORDER BY sort_order, id", ttl=PUBLIC_CACHE_SECONDS)
    sponsors_row1 = sponsors[:]
    sponsors_row2 = sponsors[:]
    random.shuffle(sponsors_row1)
    random.shuffle(sponsors_row2)
    stats = {
        "games": cached_query("stats_games", "SELECT COUNT(*) AS c FROM games WHERE active = 1 AND deleted_at = ''", one=True, ttl=PUBLIC_CACHE_SECONDS)["c"],
        "players": cached_query("stats_players", "SELECT COUNT(*) AS c FROM registrations", one=True, ttl=PUBLIC_CACHE_SECONDS)["c"],
        "teams": cached_query(
            "stats_teams",
            "SELECT COUNT(*) AS c FROM registrations WHERE entry_type = 'group'", one=True
        )["c"],
    }
    return render_template(
        "index.html",
        games=games,
        games_total=games_total,
        hero_slides=hero_slides,
        gallery=gallery,
        leaders=leaders,
        sponsors=sponsors,
        sponsors_row1=sponsors_row1,
        sponsors_row2=sponsors_row2,
        stats=stats,
    )


@app.route("/gallery")
def gallery():
    images = cached_query("gallery_images", "SELECT * FROM media WHERE kind = 'gallery' ORDER BY id DESC", ttl=PUBLIC_CACHE_SECONDS)
    return render_template("gallery.html", images=images)


@app.route("/about", methods=["GET", "POST"])
def about():
    if request.method == "POST":
        name = sanitize_field(request.form.get("name", ""), 120)
        email = sanitize_field(request.form.get("email", ""), 254)
        phone = sanitize_field(request.form.get("phone", ""), 20)
        message = sanitize_field(request.form.get("message", ""), 5000)
        if not name or not email or not message:
            flash("Name, email, and message are required.", "danger")
            return redirect(url_for("about") + "#contact")
        if not validate_email_format(email):
            flash("Please enter a valid email address.", "danger")
            return redirect(url_for("about") + "#contact")
        execute(
            "INSERT INTO contacts (name, email, phone, message, created_at) VALUES (?, ?, ?, ?, ?)",
            (name, email, phone, message, datetime.now().isoformat(timespec="seconds")),
        )
        flash("Message received. Nexora team will contact you soon.", "success")
        return redirect(url_for("about") + "#contact")

    contacts = cached_query("about_contacts", "SELECT * FROM contacts ORDER BY id DESC LIMIT 6", ttl=PUBLIC_CACHE_SECONDS)
    leaders = cached_query("about_leaders", "SELECT * FROM leaders WHERE active = 1 ORDER BY sort_order, id", ttl=PUBLIC_CACHE_SECONDS)
    sponsors = cached_query("about_sponsors", "SELECT * FROM sponsors WHERE active = 1 ORDER BY sort_order, id", ttl=PUBLIC_CACHE_SECONDS)
    games_count = cached_query("about_games_count", "SELECT COUNT(*) AS c FROM games WHERE active = 1 AND deleted_at = ''", one=True, ttl=PUBLIC_CACHE_SECONDS)["c"]
    registrations_count = cached_query("about_reg_count", "SELECT COUNT(*) AS c FROM registrations", one=True, ttl=PUBLIC_CACHE_SECONDS)["c"]
    teams_count = cached_query(
        "about_team_count",
        "SELECT COUNT(*) AS c FROM registrations WHERE entry_type = 'group'", one=True
    )["c"]
    return render_template(
        "about.html",
        contacts=contacts,
        leaders=leaders,
        sponsors=sponsors,
        games_count=games_count,
        registrations_count=registrations_count,
        teams_count=teams_count,
    )


@app.route("/privacy-policy")
def privacy_policy():
    return render_template("privacy_policy.html")


@app.route("/terms")
def terms():
    return render_template("terms.html")


# ── Our Team ──────────────────────────────────────────────────────────────────

@app.route("/our-team")
def our_team():
    sections = query("SELECT * FROM team_sections WHERE active = 1 ORDER BY sort_order, id")
    data = []
    for sec in sections:
        members = query(
            "SELECT * FROM team_members WHERE section_id = ? AND active = 1 ORDER BY sort_order, id",
            (sec["id"],),
        )
        rows = query(
            "SELECT * FROM team_rows WHERE section_id = ? ORDER BY row_index",
            (sec["id"],),
        )
        data.append({"section": sec, "members": members, "rows": rows})
    return render_template("our_team.html", team_data=data)