import hashlib
import hmac
import io
import logging
import re
import mimetypes
import os
import random
import secrets
import smtplib
import sqlite3
import time
import threading
from concurrent.futures import ThreadPoolExecutor

# Python's mimetype DB lacks webp on some installs; openpyxl needs it when
# embedding .webp images into Excel exports.
mimetypes.add_type("image/webp", ".webp")
from collections import defaultdict
from datetime import datetime, timedelta
from email.message import EmailMessage
from functools import wraps
from pathlib import Path
from threading import Thread
from urllib.parse import parse_qs, urlparse

from flask import Flask, Response, abort, flash, jsonify, redirect, render_template, request, session, url_for
try:
    from dotenv import load_dotenv
except ImportError:
    def load_dotenv(*args, **kwargs):
        pass
from werkzeug.security import check_password_hash, generate_password_hash


BASE_DIR = Path(__file__).resolve().parent
DB_PATH = BASE_DIR / "nexora.db"
STATIC_DIR = BASE_DIR / "static"
UPLOAD_DIR = STATIC_DIR / "uploads"
# ── Canonical image storage architecture (single source of truth) ──
# All image uploads land under static/Images/<Category>[/Game]/ and the DB
# stores paths RELATIVE to static/, e.g. "Images/Games/TEKKEN 7/poster.webp".
# Legacy rows store bare filenames that historically meant static/uploads/.
IMAGE_ROOT = STATIC_DIR / "Images"
CANONICAL_IMAGE_DIRS = {
    "logo": "Logo",                    # Images/Logo/Nexora Logo.webp
    "partners": "Partners",            # sponsor / partner logos
    "home": "Nexora Home",             # homepage hero / background
    "leadership": "Leadership",
    "gallery": "Gallery",              # gallery / memories
    "bank_qr": "Bank QR",              # admin-uploaded payment QR
    "rewards": "Tournament Rewards",   # prize pool banners & items
    "games": "Games",                  # Games/<Game Name>/ per-game subfolder
    "registrations": "Registrations",  # Registrations/<Game Name>/ — SENSITIVE
    "team": "Our Team",                # existing on-disk folder name (do not rename)
}
GAME_SUBFOLDER_CATEGORIES = {"games", "registrations"}
PROTECTED_IMAGE_PREFIXES = ("Images/Registrations/",)  # never publicly served
DEFAULT_SITE_LOGO = "Nexora Logo.png"
DEFAULT_HOME_BACKGROUND = "NexoraGame.png"
MAX_CONTENT_LENGTH = 8 * 1024 * 1024  # 8 MB transport cap

try:
    load_dotenv(BASE_DIR / ".env")
except Exception:
    pass

APP_START_TS = int(datetime.now().timestamp())

# â"€â"€ Logging â"€â"€
logging.basicConfig(level=logging.INFO, format="%(asctime)s [%(levelname)s] %(message)s")
logger = logging.getLogger("nexora")
logger.info("STATIC_DIR = %s (exists=%s)", STATIC_DIR, STATIC_DIR.is_dir())
logger.info("IMAGE_ROOT = %s (exists=%s)", IMAGE_ROOT, IMAGE_ROOT.is_dir())

app = Flask(__name__)

# ── Secret key: never use a hardcoded fallback in production ──
_secret = os.environ.get("SECRET_KEY", "")
if not _secret:
    _secret_file = BASE_DIR / ".secret_key"
    if _secret_file.exists():
        _secret = _secret_file.read_text().strip()
    if not _secret:
        _secret = secrets.token_hex(32)
        try:
            _secret_file.write_text(_secret)
        except OSError:
            pass
app.config["SECRET_KEY"] = _secret

app.config["UPLOAD_FOLDER"] = str(UPLOAD_DIR)
app.config["MAX_CONTENT_LENGTH"] = MAX_CONTENT_LENGTH

# ── Session cookie hardening ──
app.config["SESSION_COOKIE_HTTPONLY"] = True
app.config["SESSION_COOKIE_SAMESITE"] = "Lax"
app.config["PERMANENT_SESSION_LIFETIME"] = timedelta(hours=8)
# Environment-aware Secure flag: the site serves both plain-HTTP (:5000) and an
# HTTPS mirror (:5443) on the LAN, so a static Secure=True would break every
# HTTP login. Instead the cookie stays usable over HTTP and the `Secure`
# attribute is appended per-response whenever the request actually arrived via
# HTTPS (see _secure_cookie_flag below). FORCE_SECURE_COOKIES=1 overrides this
# for pure-HTTPS production deployments.
app.config["SESSION_COOKIE_SECURE"] = os.environ.get("FORCE_SECURE_COOKIES", "") == "1"

ADMIN_USERNAME = os.environ.get("ADMIN_USERNAME", "admin")
ADMIN_PASSWORD = os.environ.get("ADMIN_PASSWORD", "Nexora@2026Admin")

# SMTP credentials come ONLY from server-side environment variables (.env).
# Never hardcode them here. Preferred names: SMTP_EMAIL / SMTP_PASSWORD;
# legacy EMAIL_* names still work as a fallback.
EMAIL_HOST = os.environ.get("SMTP_HOST") or os.environ.get("EMAIL_HOST", "mail.nexoraesport.com")
EMAIL_PORT = int(os.environ.get("SMTP_PORT") or os.environ.get("EMAIL_PORT", "465") or 465)
EMAIL_USER = os.environ.get("SMTP_EMAIL") or os.environ.get("EMAIL_USER", "info@nexoraesport.com")
EMAIL_PASSWORD = os.environ.get("SMTP_PASSWORD") or os.environ.get("EMAIL_PASSWORD", "")
EMAIL_FROM = os.environ.get("SMTP_FROM_EMAIL") or os.environ.get("EMAIL_FROM", EMAIL_USER or "info@nexoraesport.com")
ADMIN_EMAIL = os.environ.get("ADMIN_EMAIL", "nexora.esport.support@gmail.com")
SMTP_TIMEOUT = float(os.environ.get("SMTP_TIMEOUT", "15") or 15)
EMAIL_WORKERS = max(1, int(os.environ.get("EMAIL_WORKERS", "8") or 8))
EMAIL_QUEUE_LIMIT = max(EMAIL_WORKERS, int(os.environ.get("EMAIL_QUEUE_LIMIT", "25000") or 25000))
_email_executor = ThreadPoolExecutor(max_workers=EMAIL_WORKERS, thread_name_prefix="nexora-email")
_email_pending = 0
_email_pending_lock = threading.Lock()
PUBLIC_CACHE_SECONDS = max(0, int(os.environ.get("PUBLIC_CACHE_SECONDS", "20") or 20))
STATIC_CACHE_SECONDS = max(0, int(os.environ.get("STATIC_CACHE_SECONDS", "31536000") or 31536000))
_ttl_cache = {}
_ttl_cache_lock = threading.Lock()
_public_response_cache = {}
_public_response_cache_lock = threading.Lock()
_db_wal_initialized = False
_db_wal_lock = threading.Lock()

# ── HTTP email API (Brevo) ──────────────────────────────────────────
# Many shared hosts silently redirect outbound SMTP (port 25/587) to their
# own mail server, which breaks Gmail SMTP. Setting BREVO_API_KEY makes the
# app send over HTTPS (port 443) instead, which is never blocked.
EMAIL_API_KEY = os.environ.get("BREVO_API_KEY", "").strip()
EMAIL_API_URL = os.environ.get("BREVO_API_URL", "https://api.brevo.com/v3/smtp/email").strip()

# Address that is ALWAYS added to the CC of every player-facing email
# (registration confirmation, paid/unpaid status, ticket). Auto-dropped when
# it equals the To address, so admin notices are never duplicated.
EMAIL_ALWAYS_CC = os.environ.get("EMAIL_ALWAYS_CC", "nexora.esport.support@gmail.com").strip()


# ────────────────────────────────────────────
# Brute-force protection (in-memory rate limiter)
# ────────────────────────────────────────────
class _RateLimiter:
    """Simple in-memory rate limiter with progressive lockout."""

    def __init__(self):
        self._attempts = defaultdict(list)
        self._lockouts = {}
        self._lock = threading.Lock()

    def is_locked(self, key, lockout_secs=900):
        """Returns True if key is currently in lockout."""
        with self._lock:
            if key in self._lockouts:
                if time.time() < self._lockouts[key]:
                    return True
                del self._lockouts[key]
            return False

    def record_failure(self, key, max_attempts=5, lockout_secs=900):
        """Record a failed attempt. Returns seconds until lockout clears (0 if not locked)."""
        now = time.time()
        with self._lock:
            self._attempts[key] = [t for t in self._attempts[key] if now - t < lockout_secs]
            self._attempts[key].append(now)
            if len(self._attempts[key]) >= max_attempts:
                self._lockouts[key] = now + lockout_secs
                self._attempts[key] = []
                return lockout_secs
        return 0

    def clear(self, key):
        with self._lock:
            self._attempts.pop(key, None)
            self._lockouts.pop(key, None)


_login_limiter = _RateLimiter()


# ────────────────────────────────────────────
# CSRF protection (double-submit cookie pattern)
# ────────────────────────────────────────────
def generate_csrf_token():
    """Generate and store a CSRF token in the session."""
    if "_csrf_token" not in session:
        session["_csrf_token"] = secrets.token_hex(32)
    return session["_csrf_token"]


def validate_csrf_token():
    """Validate the CSRF token from form data or header against the session."""
    token = request.form.get("csrf_token") or request.headers.get("X-CSRF-Token", "")
    expected = session.get("_csrf_token", "")
    if not token or not expected:
        return False
    return hmac.compare_digest(token, expected)


def csrf_protect():
    """Abort with 403 if CSRF token is missing/invalid on state-changing requests."""
    if app.config.get("TESTING"):
        return
    if request.method in ("POST", "PUT", "DELETE", "PATCH"):
        if not validate_csrf_token():
            logger.warning("CSRF token missing/invalid from %s %s", request.remote_addr, request.path)
            abort(403)


# Make csrf_token available in all templates
app.jinja_env.globals["csrf_token"] = generate_csrf_token


def _public_cache_key():
    if PUBLIC_CACHE_SECONDS <= 0 or request.method != "GET":
        return None
    if session.get("admin") or request.path.startswith(("/admin", "/static", "/media")):
        return None
    if request.args.get("_") or request.headers.get("Cache-Control") == "no-cache":
        return None
    if request.path in ("/", "/gallery", "/about", "/register") or request.endpoint in ("index", "gallery", "about", "register", "our_team"):
        return (request.path, tuple(sorted(request.args.items())))
    return None


@app.before_request
def _security_before_request():
    """Route-level guards + CSRF enforcement."""
    # Payment screenshots are sensitive: never serve them without an
    # authorized admin session, even though they live under static/.
    if request.path.lower().startswith(("/static/images/registrations/", "/media/images/registrations/")):
        if not session.get("admin") or not has_permission("view_payment_details"):
            abort(404)
    if request.method in ("POST", "PUT", "DELETE", "PATCH"):
        # Only exempt the login endpoint itself
        exempt_paths = {"/admin"}
        if request.path not in exempt_paths and not request.path.startswith("/static"):
            csrf_protect()
    elif request.method == "GET" and (request.path.startswith("/admin") or session.get("admin")):
        generate_csrf_token()
    # Enforce session lifetime
    if session.get("admin") and session.get("_login_time"):
        try:
            login_ts = float(session["_login_time"])
            if time.time() - login_ts > app.config["PERMANENT_SESSION_LIFETIME"].total_seconds():
                session.clear()
                flash("Session expired. Please log in again.", "warning")
                return redirect(url_for("admin_login"))
        except (ValueError, TypeError):
            pass
    cache_key = _public_cache_key()
    if cache_key:
        now_ts = time.time()
        with _public_response_cache_lock:
            cached = _public_response_cache.get(cache_key)
        if cached and cached[0] > now_ts:
            body, status, headers, mimetype = cached[1]
            resp = Response(body, status=status, mimetype=mimetype)
            for key, value in headers.items():
                resp.headers[key] = value
            resp.headers["X-Nexora-Cache"] = "HIT"
            return resp


@app.after_request
def _secure_cookie_flag(response):
    """Mark the session cookie Secure only when the connection is HTTPS, so
    LAN HTTP testing keeps working while the HTTPS mirror gets hardened cookies."""
    if not app.config["SESSION_COOKIE_SECURE"] and request.is_secure:
        cookie_name = app.config.get("SESSION_COOKIE_NAME", "session")
        values = response.headers.getlist("Set-Cookie")
        updated = []
        changed = False
        for value in values:
            if value.startswith(cookie_name + "=") and "secure" not in value.lower():
                value += "; Secure"
                changed = True
            updated.append(value)
        if changed:
            del response.headers["Set-Cookie"]
            for value in updated:
                response.headers.add("Set-Cookie", value)
    if request.path.startswith(("/static/", "/media/")) and response.status_code == 200:
        response.cache_control.no_cache = None
        response.cache_control.public = True
        response.cache_control.max_age = STATIC_CACHE_SECONDS
        response.cache_control.immutable = True
    cache_key = _public_cache_key()
    if response.headers.get("X-Nexora-Cache") == "HIT":
        return response
    if cache_key and response.status_code == 200 and response.mimetype == "text/html" and "Set-Cookie" not in response.headers:
        body = response.get_data()
        headers = {
            "Cache-Control": f"public, max-age={PUBLIC_CACHE_SECONDS}",
        }
        with _public_response_cache_lock:
            _public_response_cache[cache_key] = (
                time.time() + PUBLIC_CACHE_SECONDS,
                (body, response.status_code, headers, response.mimetype),
            )
        response.headers["Cache-Control"] = headers["Cache-Control"]
        response.headers["X-Nexora-Cache"] = "MISS"
    return response


# ────────────────────────────────────────────
# Database helpers
# ────────────────────────────────────────────
def db():
    global _db_wal_initialized
    conn = sqlite3.connect(DB_PATH, timeout=30)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA busy_timeout = 30000")
    conn.execute("PRAGMA synchronous = NORMAL")
    if not _db_wal_initialized:
        with _db_wal_lock:
            if not _db_wal_initialized:
                conn.execute("PRAGMA journal_mode = WAL")
                _db_wal_initialized = True
    return conn


def query(sql, args=(), one=False):
    with db() as conn:
        rows = conn.execute(sql, args).fetchall()
    return (rows[0] if rows else None) if one else rows


def execute(sql, args=()):
    with db() as conn:
        cur = conn.execute(sql, args)
        conn.commit()
        clear_perf_caches()
        return cur.lastrowid


def clear_perf_caches():
    with _ttl_cache_lock:
        _ttl_cache.clear()
    with _public_response_cache_lock:
        _public_response_cache.clear()


def cached_query(cache_key, sql, args=(), one=False, ttl=20):
    if ttl <= 0:
        return query(sql, args, one=one)
    now_ts = time.time()
    key = (cache_key, sql, tuple(args), one)
    with _ttl_cache_lock:
        cached = _ttl_cache.get(key)
        if cached and cached[0] > now_ts:
            return cached[1]
    value = query(sql, args, one=one)
    with _ttl_cache_lock:
        _ttl_cache[key] = (now_ts + ttl, value)
    return value


# ────────────────────────────────────────────
# Centralized image upload processing
# Every image upload on the site goes through process_image_upload():
#   1. extension allowlist (untrusted filename never trusted for storage)
#   2. real byte-size + content validation via Pillow (rejects executables,
#      corrupt payloads, decompression bombs)
#   3. automatic WebP conversion with progressive quality/scale reduction
#   4. hard server-side rule: final stored file must be ≤ MAX_STORED_IMAGE_BYTES
#   5. safe, server-generated filename; original upload is never kept
# Returns (filename, error): (None, "") = no file provided (not an error),
# ("x.webp", "") = success, (None, "message") = rejected.
# ────────────────────────────────────────────
MAX_STORED_IMAGE_BYTES = 20 * 1024        # final stored WebP must be ≤ 20 KB
MAX_IMAGE_DIMENSION = 2400                # cap resolution before encoding
# The 20 KB target never needs more than ~1200 px. Searching for the right
# scale/quality at full resolution with method=6 is what made a single 2 MB
# upload take minutes on a busy worker. Work from a moderate size, use a
# coarser grid and a faster encoder method.
_WEBP_WORK_DIMENSION = 1200
_WEBP_QUALITIES = (82, 68, 54, 42, 32)
_WEBP_SCALES = (1.0, 0.75, 0.55, 0.4, 0.28)
_WEBP_METHOD = 4


def _jpeg_under_limit(img, limit=MAX_STORED_IMAGE_BYTES):
    """Encode img as JPEG at descending scales/qualities until it fits the
    size limit. Returns encoded bytes or None if it cannot fit."""
    from PIL import Image as _PILImage

    if max(img.width, img.height) > _WEBP_WORK_DIMENSION:
        r = _WEBP_WORK_DIMENSION / max(img.width, img.height)
        img = img.resize(
            (max(48, int(img.width * r)), max(48, int(img.height * r))),
            _PILImage.LANCZOS,
        )

    # Convert to RGB as JPEG does not support transparency (RGBA)
    if img.mode != "RGB":
        if img.mode == "RGBA":
            bg = _PILImage.new("RGB", img.size, (255, 255, 255))
            bg.paste(img, mask=img.split()[3])
            img = bg
        else:
            img = img.convert("RGB")

    best_buf = None

    for scale in _WEBP_SCALES:
        candidate = img
        if scale < 1.0:
            width = max(48, int(img.width * scale))
            height = max(48, int(img.height * scale))
            candidate = img.resize((width, height), _PILImage.LANCZOS)
        for quality in _WEBP_QUALITIES:
            buf = io.BytesIO()
            candidate.save(buf, "JPEG", quality=quality, optimize=True)
            best_buf = buf
            if buf.tell() <= limit:
                buf.seek(0)
                return buf.read()

    if best_buf:
        best_buf.seek(0)
        return best_buf.read()
    return None


# ────────────────────────────────────────────
# Canonical path helpers
# ────────────────────────────────────────────
def sanitize_folder_name(title):
    """Game title -> safe folder name. Spaces are preserved so new folders
    match the existing canonical ones (e.g. 'TEKKEN 7', 'PUBG Mobile')."""
    name = re.sub(r'[<>:"/\\|?*\x00-\x1f]', "", (title or ""))
    name = re.sub(r"\s+", " ", name).strip().rstrip(". ")
    return name[:60]


def resolve_game_dir(base_dir, game_title):
    """Return base_dir/<Game Name>/ reusing an existing folder when only the
    letter case differs, so titles like 'Tekken 7' reuse 'TEKKEN 7'."""
    folder = sanitize_folder_name(game_title) or "Unsorted"
    try:
        for child in base_dir.iterdir():
            if child.is_dir() and child.name.lower() == folder.lower():
                return child
    except OSError:
        pass
    return base_dir / folder


_IMAGE_EXTENSIONS = (".webp", ".png", ".jpg", ".jpeg", ".gif", ".jfif", ".avif", ".bmp", ".tiff", ".tif", ".svg", ".heic", ".heif", ".pdf", ".ico")
_IMG_CACHE = {}
_STATIC_CASE_INDEX = None


def _clean_static_reference(reference):
    value = (reference or "").strip().replace("\\", "/")
    while value.startswith("/"):
        value = value[1:]
    while value.lower().startswith("static/"):
        value = value[7:].lstrip("/")
    value = re.sub(r"/+", "/", value)
    if not value or "\x00" in value:
        return ""
    parts = [part for part in value.split("/") if part not in ("", ".")]
    if any(part == ".." for part in parts):
        return ""
    value = "/".join(parts)
    if value and not (value.lower().startswith("images/") or value.lower().startswith("uploads/")):
        value = "uploads/" + value
    return value


def _inside_static(path):
    try:
        rel = os.path.relpath(str(path.resolve()), str(STATIC_DIR.resolve()))
        return rel == "." or not rel.startswith("..")
    except (ValueError, OSError):
        return False


def _static_relative(path):
    return os.path.relpath(str(path.resolve()), str(STATIC_DIR.resolve())).replace("\\", "/")


def _static_case_index():
    global _STATIC_CASE_INDEX
    if _STATIC_CASE_INDEX is None:
        index = {}
        for root in (IMAGE_ROOT, UPLOAD_DIR):
            if not root.is_dir():
                continue
            try:
                for found in root.rglob("*"):
                    if found.is_file():
                        rel = found.relative_to(STATIC_DIR).as_posix()
                        index.setdefault(rel.lower(), rel)
            except OSError:
                continue
        _STATIC_CASE_INDEX = index
    return _STATIC_CASE_INDEX


def invalidate_image_cache():
    global _STATIC_CASE_INDEX
    _IMG_CACHE.clear()
    _STATIC_CASE_INDEX = None
    with _ttl_cache_lock:
        _ttl_cache.clear()


def _resolve_ext_fallback(base_dir, value):
    """Try *value* as-is first, then swap its extension through every
    supported image type.  Returns the first existing file, or the original
    path if nothing else exists (preserving previous behaviour)."""
    value = _clean_static_reference(value)
    if not value:
        return None
    p = (base_dir / value).resolve()
    if _inside_static(p) and p.is_file():
        return p
    indexed = _static_case_index().get(value.lower())
    if indexed:
        p = (base_dir / indexed).resolve()
        if _inside_static(p) and p.is_file():
            return p
    stem = p.stem
    parent_rel = Path(value).parent.as_posix()
    for ext in _IMAGE_EXTENSIONS:
        rel = f"{parent_rel}/{stem}{ext}" if parent_rel != "." else f"{stem}{ext}"
        indexed = _static_case_index().get(rel.lower())
        candidate = (base_dir / (indexed or rel)).resolve()
        if _inside_static(candidate) and candidate.is_file():
            return candidate
    return p


def static_file_abs(reference):
    """Resolve a stored image reference to an absolute path inside static/.
    Accepts legacy slashes/prefixes and resolves Linux filename case safely."""
    value = _clean_static_reference(reference)
    if not value:
        return None
    try:
        target = _resolve_ext_fallback(STATIC_DIR, value)
        if target and target.is_file():
            return target
        basename = os.path.splitext(os.path.basename(value))[0]
        for ext in _IMAGE_EXTENSIONS:
            rel = f"uploads/{basename}{ext}"
            indexed = _static_case_index().get(rel.lower())
            candidate = (STATIC_DIR / (indexed or rel)).resolve()
            if _inside_static(candidate) and candidate.is_file():
                return candidate
    except (ValueError, OSError):
        pass
    return None


def img_static_url(value):
    """Jinja helper: map any stored image reference to its static/-relative
    URL path without exposing protected registration proofs.
    Returns '' when the file cannot be found on disk so the template
    never produces a broken <img> that 404s."""
    v = _clean_static_reference(value)
    if not v:
        return ""
    if v in _IMG_CACHE:
        return _IMG_CACHE[v]
    target = static_file_abs(v)
    if target and target.is_file():
        result = _static_relative(target)
        _IMG_CACHE[v] = result
        return result
    # ── Fallback: recursive glob search for the filename ──
    # On cPanel with Passenger/symlinks, resolve() may not find files
    # that exist on disk.  Search by basename instead.
    basename = os.path.basename(v)
    if basename:
        for search_root in (IMAGE_ROOT, UPLOAD_DIR):
            if not search_root.is_dir():
                continue
            try:
                for found in search_root.rglob(basename):
                    if found.is_file() and _inside_static(found):
                        result = _static_relative(found)
                        _IMG_CACHE[v] = result
                        logger.info("img fallback found: %s -> %s", v, result)
                        return result
            except OSError:
                continue
    # File not found — return empty so the <img> src is "" instead of a
    # guaranteed 404.  The template should guard with {% if img(...) %}.
    _IMG_CACHE[v] = ""
    return ""


app.jinja_env.globals["img"] = img_static_url

from flask import send_file

# 1x1 transparent PNG — returned when an image path is empty or missing
# so templates never produce a 404 on broken <img> tags.
_TRANSPARENT_PX = (
    b'\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x00\x01'
    b'\x00\x00\x00\x01\x08\x06\x00\x00\x00\x1f\x15\xc4\x89'
    b'\x00\x00\x00\nIDATx\x9cc\x00\x01\x00\x00\x05\x00\x01'
    b'\r\n\xb4\x00\x00\x00\x00IEND\xaeB`\x82'
)


@app.route("/media/<path:filename>")
def serve_media(filename):
    """Bypasses cPanel Apache static handlers to ensure images load via Flask.
    Returns a 1x1 transparent pixel for empty/missing paths so the page
    never shows a broken image or triggers a 404."""
    if not filename or not filename.strip():
        return Response(_TRANSPARENT_PX, mimetype="image/png",
                        headers={"Cache-Control": "public, max-age=300"})
    try:
        safe_name = _clean_static_reference(filename)
        if not safe_name or safe_name.lower().startswith("images/registrations/"):
            return abort(404)
        target = static_file_abs(safe_name)
        if not target or not _inside_static(target):
            # ── Fallback: recursive glob search for the filename ──
            basename = os.path.basename(safe_name)
            if basename:
                for search_root in (IMAGE_ROOT, UPLOAD_DIR):
                    if not search_root.is_dir():
                        continue
                    try:
                        for found in search_root.rglob(basename):
                            if found.is_file() and _inside_static(found):
                                return send_file(str(found))
                    except OSError:
                        continue
            return abort(404)
        if target.is_file():
            return send_file(str(target))
    except Exception:
        pass
    return abort(404)


def ensure_canonical_image_dirs():
    """Create every canonical directory once at startup (idempotent)."""
    for sub in CANONICAL_IMAGE_DIRS.values():
        (IMAGE_ROOT / sub).mkdir(parents=True, exist_ok=True)
    # Per-game subfolders for games already registered in the DB
    try:
        for row in query("SELECT title FROM games"):
            title = sanitize_folder_name(row["title"])
            if not title:
                continue
            (IMAGE_ROOT / "Games" / title).mkdir(parents=True, exist_ok=True)
            (IMAGE_ROOT / "Registrations" / title).mkdir(parents=True, exist_ok=True)
    except Exception:
        pass  # DB may not exist yet on very first boot


def repair_broken_settings_images():
    """If branding settings point at files that no longer exist, search both
    Images/ and uploads/ for any matching filename (any extension), then fall
    back to the bare-name defaults that resolve via uploads/."""
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    if not settings:
        return

    def find_any(ref):
        """Return the best matching path for *ref*, or None."""
        p = static_file_abs(ref)
        if p and p.is_file():
            return p
        bare = os.path.basename((ref or "").strip())
        if not bare:
            return None
        stem = os.path.splitext(bare)[0]
        for folder in ("uploads", "Images"):
            parent = STATIC_DIR / folder
            if not parent.is_dir():
                continue
            for ext in _IMAGE_EXTENSIONS:
                c = parent / (stem + ext)
                if c.is_file():
                    return c
            for d in parent.rglob("*"):
                if d.is_dir() and d.name.lower() == stem.lower():
                    for ext in _IMAGE_EXTENSIONS:
                        c = d / (stem + ext)
                        if c.is_file():
                            return c
        return None

    updates = {}
    if not find_any(settings["site_logo"]):
        updates["site_logo"] = "Nexora Logo.png"
    if not find_any(settings["home_background"]):
        updates["home_background"] = "NexoraGame.png"
    if updates:
        assignments = ", ".join(f"{name} = ?" for name in updates)
        execute(f"UPDATE settings SET {assignments} WHERE id = 1", list(updates.values()))
        logger.info("Repaired broken branding references: %s", ", ".join(updates))


def process_image_upload(file, category="uploads", game_title=None, override_filename=None):
    """Accept ANY uploaded file and save it directly to the canonical
    static/Images/<Category>[/Game]/ folder — no re-encoding, no Pillow,
    no format restrictions.  The raw browser bytes are written as-is so
    cPanel cannot corrupt them through libjpeg/libwebp mismatches.
    """
    if file is None or not getattr(file, "filename", ""):
        return None, ""

    # ── 1. Read the raw bytes ──
    data = file.read()
    if not data:
        return None, "The selected file is empty."
    if len(data) > app.config["MAX_CONTENT_LENGTH"]:
        return None, "File is too large. Maximum upload size is 8 MB."

    # ── 2. Preserve the original extension (any extension is accepted) ──
    ext = ""
    if "." in (file.filename or ""):
        ext = file.filename.rsplit(".", 1)[1].lower()
    if not ext:
        ext = "bin"

    # ── 3. Resolve the canonical destination folder ──
    rel_subdir = CANONICAL_IMAGE_DIRS.get(category)
    dest_dir = UPLOAD_DIR
    rel_prefix = "uploads"
    if rel_subdir:
        try:
            candidate_dir = IMAGE_ROOT / rel_subdir
            if category in GAME_SUBFOLDER_CATEGORIES and game_title:
                candidate_dir = resolve_game_dir(candidate_dir, game_title)
            candidate_dir.mkdir(parents=True, exist_ok=True)
            test_file = candidate_dir / ".write_test"
            test_file.write_bytes(b"ok")
            test_file.unlink()
            dest_dir = candidate_dir
            rel_prefix = f"Images/{rel_subdir}"
            if category in GAME_SUBFOLDER_CATEGORIES and game_title:
                rel_prefix = f"{rel_prefix}/{dest_dir.name}"
        except OSError as exc:
            logger.exception("Image upload save failed for category %s", category)
            return None, f"cPanel Permission Error: Could not save image to folder. (Check static/ folder permissions on cPanel). Details: {exc}"

    try:
        dest_dir.mkdir(parents=True, exist_ok=True)
    except OSError as exc:
        return None, f"cPanel Permission Error (Storage): Could not create the upload folder. Details: {exc}"

    # ── 4. Generate filename ──
    if override_filename:
        safe_name = re.sub(r'[<>:"/\\|?*\x00-\x1f]', '_', override_filename.strip())
        filename = f"{safe_name}.{ext}"
        existing = dest_dir / filename
        if existing.is_file():
            existing.unlink()
    else:
        stamp = datetime.now().strftime("%Y%m%d%H%M%S%f")
        filename = f"{stamp}_{secrets.token_hex(4)}.{ext}"

    # ── 5. Write raw bytes directly — no Pillow, no conversion ──
    try:
        dest_file = dest_dir / filename
        dest_file.write_bytes(data)
        # Verify the file was actually written (catches silent cPanel failures)
        if not dest_file.is_file() or dest_file.stat().st_size != len(data):
            logger.error("Post-write verification failed: expected %d bytes, got %s",
                         len(data), str(dest_file.stat().st_size) if dest_file.is_file() else "file missing")
            return None, "File could not be saved. The server may have disk permission issues."
    except OSError as exc:
        logger.exception("Image upload write failed for %s", dest_dir)
        return None, f"Could not write file on the server: {exc}"

    relative = f"{rel_prefix}/{filename}"
    invalidate_image_cache()
    logger.info("Upload saved: %d bytes -> %s (exists=%s)", len(data), relative, dest_file.is_file())
    return relative, ""


def delete_upload(reference):
    """Delete a stored image by its DB reference. Accepts canonical
    'Images/...' paths and legacy bare uploads/ filenames. Path-traversal
    safe: the resolved target must remain inside static/."""
    if not reference:
        return
    # Canonical assets referenced as defaults are never deleted implicitly
    if reference in (DEFAULT_SITE_LOGO, DEFAULT_HOME_BACKGROUND):
        return
    target = static_file_abs(reference)
    try:
        if target and target.is_file():
            target.unlink()
    except OSError:
        pass


# ────────────────────────────────────────────
# Registration validation helpers
# ────────────────────────────────────────────
def normalize_email(email):
    """Trim whitespace and lowercase for case-insensitive comparison."""
    return (email or "").strip().lower()


def normalize_cnic(cnic):
    """Strip spaces, hyphens, dots from CNIC so formatting cannot bypass checks."""
    return re.sub(r"[\s\-\.]", "", (cnic or "").strip())


# ────────────────────────────────────────────
# Input validation / sanitization helpers
# ────────────────────────────────────────────
_MAX_FIELD_LENGTHS = {
    "full_name": 120, "email": 254, "phone": 20, "cnic": 20,
    "team_name": 80, "university": 200, "transaction_reference": 120,
    "leader_name": 120, "member_name": 120, "member_email": 254,
    "member_phone": 20, "reason": 500, "gate": 80, "override_reason": 200,
    "username": 60, "password": 128, "title": 200, "name": 120,
    "message": 5000, "event_title": 200, "event_tagline": 300,
    "about_intro": 2000, "about_body": 10000,
}


def sanitize_field(value, max_len=None):
    """Strip control characters and enforce max length."""
    v = (value or "").strip()
    # Remove control chars except newline/tab
    v = re.sub(r"[\x00-\x08\x0b\x0c\x0e-\x1f\x7f]", "", v)
    if max_len and len(v) > max_len:
        v = v[:max_len]
    return v


def validate_email_format(email):
    """Basic email format check."""
    email = (email or "").strip()
    if not email or len(email) > 254:
        return False
    return bool(re.match(r"^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$", email))


def validate_cnic_format(cnic):
    """CNIC should be 5-20 digits after normalization."""
    c = normalize_cnic(cnic)
    return c and c.isdigit() and 5 <= len(c) <= 20


def validate_phone_format(phone):
    """Phone should be 7-20 digits/dashes/plus."""
    p = re.sub(r"[\s\-\+]", "", (phone or "").strip())
    return p and p.isdigit() and 7 <= len(p) <= 20


def validate_username_format(username):
    """Username: 3-60 chars, alphanumeric plus underscore/hyphen/dot."""
    u = (username or "").strip()
    return bool(u) and 3 <= len(u) <= 60 and bool(re.match(r"^[a-zA-Z0-9._\-]+$", u))


# Only these table names may be used in f-string SQL (prevents SQL injection via table names)
_ALLOWED_TABLES = frozenset({"registrations", "member_tickets"})


def safe_table_name(name):
    """Return the table name only if it's in the allowlist, else raise ValueError."""
    if name not in _ALLOWED_TABLES:
        raise ValueError(f"Invalid table name: {name}")
    return name


def _emails_in_members(members_text):
    """Return the set of normalised emails found in a members text block."""
    return {
        normalize_email(m["email"])
        for m in parse_team_members(members_text)
        if m.get("email")
    }


def validate_registration_constraints(
    conn, email, cnic, game_id, team_name, entry_type,
    members_text=None, exclude_id=None,
):
    """Check every uniqueness rule inside *conn* (caller owns the transaction).

    Returns a list of user-facing error strings.  An empty list means valid.

    Rules (all game-scoped):
    1. Nickname / team name must be unique within the same game.
    2. CNIC must be unique within the same game.
    3. A player (email or CNIC) cannot already be registered for the same game
       (as primary registrant or as a team member).
    """
    errors = []

    try:
        norm_email = normalize_email(email)
        norm_cnic = normalize_cnic(cnic)
        norm_team = (team_name or "").strip()
        game_id = int(game_id)
    except (ValueError, TypeError):
        errors.append("Invalid registration data. Please check your inputs.")
        return errors

    exclude = " AND id != ?" if exclude_id else ""
    exclude_args = (exclude_id,) if exclude_id else ()

    # ── 1. Nickname / team name unique within the same game ──
    if norm_team:
        try:
            hit = conn.execute(
                f"""SELECT id FROM registrations
                    WHERE LOWER(TRIM(team_name)) = LOWER(?)
                      AND game_id = ? {exclude}""",
                (norm_team, game_id, *exclude_args),
            ).fetchone()
            if hit:
                errors.append("This Nickname is already registered for this game.")
        except Exception:
            pass

    # ── 2. CNIC unique within the same game ──
    if norm_cnic:
        try:
            hit = conn.execute(
                f"""SELECT id FROM registrations
                    WHERE REPLACE(REPLACE(REPLACE(TRIM(cnic), '-', ''), ' ', ''), '.', '') = ?
                      AND game_id = ? {exclude}""",
                (norm_cnic, game_id, *exclude_args),
            ).fetchone()
            if hit:
                errors.append("This CNIC is already registered for this game.")
        except Exception:
            pass

    # ── 3 & 4. Player already registered in this game ──
    existing = []
    try:
        existing = conn.execute(
            f"SELECT id, email, cnic, members FROM registrations WHERE game_id = ? {exclude}",
            (game_id, *exclude_args),
        ).fetchall()
    except Exception:
        pass

    existing_norm_cnic_set = set()
    for row in existing:
        existing_norm_cnic_set.add(normalize_cnic(row["cnic"]))

    # 3. Is the new primary registrant already registered for this game?
    if not errors:
        for row in existing:
            existing_email = normalize_email(row["email"])
            existing_cnic = normalize_cnic(row["cnic"])
            is_primary = (existing_email == norm_email)
            is_in_members = (norm_email in _emails_in_members(row["members"]))
            if is_primary or is_in_members:
                errors.append(
                    "This player is already registered in this game with another team."
                )
                break

    # 4. Is any of the new team's members already registered for this game?
    if not errors and (entry_type or "").lower() == "group" and members_text:
        new_member_emails = _emails_in_members(members_text)
        if norm_email in new_member_emails:
            new_member_emails.discard(norm_email)
        if new_member_emails:
            for row in existing:
                existing_email = normalize_email(row["email"])
                existing_member_emails = _emails_in_members(row["members"])
                if existing_email in new_member_emails:
                    errors.append(
                        "This player is already registered in this game with another team."
                    )
                    break
                overlap = new_member_emails & existing_member_emails
                if overlap:
                    errors.append(
                        "This player is already registered in this game with another team."
                    )
                    break

    return errors


def split_upload_list(value):
    return [item.strip() for item in (value or "").splitlines() if item.strip()]


def make_ticket_token():
    return "nxt_" + secrets.token_urlsafe(24)


def game_ticket_code(title):
    clean = re.sub(r"[^A-Za-z0-9]+", " ", str(title or "")).upper().strip()
    if "FIFA" in clean or clean.startswith("EA FC"):
        return "FIFA"
    if "PUBG" in clean:
        return "PUBG"
    if "TEKKEN" in clean:
        return "TEKKEN"
    return ("".join(clean.split())[:8] or "GAME")


def make_ticket_serial(registration_id, game_title="", event_date=""):
    year_match = re.search(r"\b(20\d{2})\b", str(event_date or ""))
    event_year = year_match.group(1) if year_match else str(datetime.now().year)
    return f"NX-{game_ticket_code(game_title)}-{event_year}-{int(registration_id):06d}"


def ensure_ticket_tokens(conn=None):
    owns_conn = conn is None
    conn = conn or db()
    try:
        rows = conn.execute(
            "SELECT id FROM registrations WHERE ticket_token IS NULL OR ticket_token = ''"
        ).fetchall()
        for row in rows:
            token = make_ticket_token()
            while conn.execute(
                "SELECT id FROM registrations WHERE ticket_token = ?", (token,)
            ).fetchone():
                token = make_ticket_token()
            conn.execute("UPDATE registrations SET ticket_token = ? WHERE id = ?", (token, row["id"]))
        if rows:
            conn.commit()
    finally:
        if owns_conn:
            conn.close()


def ensure_ticket_serials(conn=None):
    owns_conn = conn is None
    conn = conn or db()
    try:
        event_date = conn.execute("SELECT event_date FROM settings WHERE id = 1").fetchone()
        rows = conn.execute(
            """
            SELECT registrations.id, COALESCE(games.title, 'GAME') AS game_title
            FROM registrations LEFT JOIN games ON games.id = registrations.game_id
            WHERE registrations.ticket_serial IS NULL
               OR registrations.ticket_serial = ''
               OR registrations.ticket_serial LIKE 'NX-TICKET-%'
            """
        ).fetchall()
        for row in rows:
            conn.execute(
                "UPDATE registrations SET ticket_serial = ? WHERE id = ?",
                (make_ticket_serial(row["id"], row["game_title"], event_date["event_date"] if event_date else ""), row["id"]),
            )
        if rows:
            conn.commit()
    finally:
        if owns_conn:
            conn.close()


def ensure_indexes(conn):
    conn.executescript(
        """
        CREATE UNIQUE INDEX IF NOT EXISTS idx_registrations_ticket_token
        ON registrations(ticket_token);

        CREATE INDEX IF NOT EXISTS idx_registrations_payment_status
        ON registrations(payment_status);

        CREATE INDEX IF NOT EXISTS idx_registrations_game_id
        ON registrations(game_id);

        CREATE INDEX IF NOT EXISTS idx_registrations_created_at
        ON registrations(created_at);

        CREATE INDEX IF NOT EXISTS idx_registrations_entry_type
        ON registrations(entry_type);

        CREATE INDEX IF NOT EXISTS idx_registrations_entry_status
        ON registrations(entry_status);

        CREATE INDEX IF NOT EXISTS idx_registrations_payment_method
        ON registrations(payment_method);

        CREATE INDEX IF NOT EXISTS idx_games_public
        ON games(active, deleted_at, id);

        CREATE INDEX IF NOT EXISTS idx_media_kind_id
        ON media(kind, id);

        CREATE INDEX IF NOT EXISTS idx_leaders_active_order
        ON leaders(active, sort_order, id);

        CREATE INDEX IF NOT EXISTS idx_sponsors_active_order
        ON sponsors(active, sort_order, id);

        CREATE INDEX IF NOT EXISTS idx_prize_items_active_order
        ON prize_items(active, sort_order, id);

        CREATE INDEX IF NOT EXISTS idx_contacts_created_at
        ON contacts(created_at);

        CREATE INDEX IF NOT EXISTS idx_member_tickets_registration
        ON member_tickets(registration_id, member_number);

        CREATE INDEX IF NOT EXISTS idx_ticket_audit_registration
        ON ticket_audit_log(registration_id, id);

        DROP INDEX IF EXISTS idx_registrations_ticket_serial;

        CREATE UNIQUE INDEX IF NOT EXISTS idx_registrations_ticket_serial
        ON registrations(ticket_serial);

        """
    )
    # ── Registration uniqueness constraints ──
    # Team name / nickname must be unique per game (case-insensitive, blank-safe).
    # Drop old global index if it exists, then create per-game partial index.
    try:
        conn.execute("DROP INDEX IF EXISTS idx_reg_unique_team_name")
    except Exception:
        pass
    try:
        conn.execute(
            """CREATE UNIQUE INDEX IF NOT EXISTS idx_reg_unique_team_name
               ON registrations(LOWER(TRIM(team_name)), game_id)
               WHERE team_name IS NOT NULL AND TRIM(team_name) != ''"""
        )
    except Exception:
        pass
    # CNIC must be unique per game (one registration per person per game).
    # CNIC is stored normalized: hyphens, spaces and dots stripped.
    try:
        conn.execute(
            """CREATE UNIQUE INDEX IF NOT EXISTS idx_reg_unique_email_cnic_game
               ON registrations(
                   LOWER(TRIM(email)),
                   REPLACE(REPLACE(REPLACE(TRIM(cnic), '-', ''), ' ', ''), '.', ''),
                   game_id
               )"""
        )
    except Exception:
        pass

    # Existing installations can briefly be migrated before the new table is
    # present. Never let an optional index stop the application from starting.
    has_member_tickets = conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'member_tickets'"
    ).fetchone()
    if has_member_tickets:
        conn.executescript(
            """
            CREATE UNIQUE INDEX IF NOT EXISTS idx_member_tickets_serial
            ON member_tickets(ticket_serial);

            CREATE UNIQUE INDEX IF NOT EXISTS idx_member_tickets_token
            ON member_tickets(ticket_token);
            """
        )


MEMBER_TICKET_EXTRA_COLUMNS = {
    "ticket_token": "",
    "entry_status": "pending",
    "entry_marked_at": "",
    "entry_note": "",
    "checkin_gate": "",
    "checked_in_by": "",
    "rejection_reason": "",
    "rejected_by": "",
    "rejected_at": "",
    "override_by": "",
    "override_at": "",
    "override_reason": "",
}


def ensure_member_ticket_tokens(conn):
    """Give historical member tickets their own opaque QR token."""
    rows = conn.execute(
        "SELECT id FROM member_tickets WHERE ticket_token IS NULL OR ticket_token = ''"
    ).fetchall()
    for row in rows:
        token = make_ticket_token()
        while conn.execute("SELECT id FROM member_tickets WHERE ticket_token = ?", (token,)).fetchone():
            token = make_ticket_token()
        conn.execute("UPDATE member_tickets SET ticket_token = ? WHERE id = ?", (token, row["id"]))
    if rows:
        conn.commit()


def ensure_group_member_tickets(conn):
    """Create a unique ticket record for every non-leader group member."""
    rows = conn.execute(
        "SELECT id, members FROM registrations WHERE LOWER(COALESCE(entry_type, '')) = 'group'"
    ).fetchall()
    for row in rows:
        for member_number, member in enumerate(parse_team_members(row["members"]), start=2):
            if not any(member.values()):
                continue
            existing = conn.execute(
                "SELECT id FROM member_tickets WHERE registration_id = ? AND member_number = ?",
                (row["id"], member_number),
            ).fetchone()
            if existing:
                continue
            serial = f"NX-MEMBER-{row['id']:06d}-M{member_number:02d}"
            # Keep the public serial aligned with its group leader's serial when available.
            parent = conn.execute("SELECT ticket_serial FROM registrations WHERE id = ?", (row["id"],)).fetchone()
            if parent and parent["ticket_serial"]:
                serial = f"{parent['ticket_serial']}-M{member_number:02d}"
            conn.execute(
                """INSERT INTO member_tickets
                   (registration_id, member_number, member_name, member_email, ticket_serial, ticket_token, created_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?)""",
                (row["id"], member_number, member["name"], member["email"], serial,
                 make_ticket_token(), datetime.now().isoformat(timespec="seconds")),
            )
    conn.commit()


# ────────────────────────────────────────────
# Email
# ────────────────────────────────────────────
def email_config():
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    host = (settings["smtp_host"] if settings and "smtp_host" in settings.keys() else "") or EMAIL_HOST
    port_raw = (settings["smtp_port"] if settings and "smtp_port" in settings.keys() else "") or str(EMAIL_PORT)
    user = (settings["smtp_user"] if settings and "smtp_user" in settings.keys() else "") or EMAIL_USER
    password = (settings["smtp_password"] if settings and "smtp_password" in settings.keys() else "") or EMAIL_PASSWORD
    from_email = (settings["smtp_from_email"] if settings and "smtp_from_email" in settings.keys() else "") or EMAIL_FROM
    admin_email = (settings["smtp_admin_email"] if settings and "smtp_admin_email" in settings.keys() else "") or ADMIN_EMAIL
    try:
        port = int(port_raw or 465)
    except ValueError:
        port = 465
    sender = from_email.strip() or user.strip()
    smtp_ready = bool(host.strip() and user.strip() and password.strip())
    api_ready = bool(EMAIL_API_KEY and sender)
    return {
        "host": host.strip(),
        "port": port,
        "user": user.strip(),
        "password": password.strip(),
        "from_email": sender,
        "admin_email": admin_email.strip() or sender,
        "api_key": EMAIL_API_KEY,
        "api_url": EMAIL_API_URL,
        "enabled": bool(api_ready or smtp_ready),
    }


def admin_notification_email():
    return email_config()["admin_email"]


def _send_email_via_api(config, to_email, subject, html_body, text_body, attachments, cc_emails):
    """Send through Brevo's HTTP API (port 443). Works even when the host
    blocks or redirects outbound SMTP. Enabled only when BREVO_API_KEY is set."""
    import base64
    import json
    import urllib.request
    import urllib.error
    from email.utils import parseaddr

    from_name, from_addr = parseaddr(config["from_email"] or config["user"])
    payload = {
        "sender": {"email": from_addr or config["from_email"], "name": from_name or "Nexora Esports"},
        "to": [{"email": to_email}],
        "subject": subject,
        "htmlContent": html_body,
        "textContent": text_body or " ",
    }
    if config["admin_email"]:
        payload["replyTo"] = {"email": config["admin_email"]}
    if cc_emails:
        payload["cc"] = [{"email": e} for e in dict.fromkeys(cc_emails)]
    api_attachments = []
    for attachment in attachments or []:
        if not attachment or "data" not in attachment or "filename" not in attachment:
            continue
        api_attachments.append({
            "content": base64.b64encode(attachment["data"]).decode("ascii"),
            "name": attachment["filename"],
        })
    if api_attachments:
        payload["attachment"] = api_attachments

    req = urllib.request.Request(
        config["api_url"],
        data=json.dumps(payload).encode("utf-8"),
        headers={
            "api-key": config["api_key"],
            "content-type": "application/json",
            "accept": "application/json",
        },
        method="POST",
    )
    try:
        with urllib.request.urlopen(req, timeout=15) as resp:
            resp.read()
        return True
    except urllib.error.HTTPError as e:
        body = e.read().decode("utf-8", "replace")[:500]
        logging.getLogger("nexora").error("Email API error %s: %s", e.code, body)
        return False
    except Exception as e:
        logging.getLogger("nexora").error("Email API error: %s", e)
        return False


def send_email(to_email, subject, html_body, text_body, attachments=None, cc_emails=None):
    config = email_config()
    if not to_email or not config["enabled"]:
        print(f"Email skipped for {to_email or 'unknown'}: email not configured.")
        return False

    cc_source = list(cc_emails or [])
    if EMAIL_ALWAYS_CC:
        cc_source.append(EMAIL_ALWAYS_CC)
    cc_list = list(dict.fromkeys(
        email.strip()
        for email in cc_source
        if email and email.strip() and email.strip().lower() != to_email.strip().lower()
    ))

    if config["api_key"]:
        return _send_email_via_api(config, to_email, subject, html_body, text_body, attachments, cc_list)

    if not config["host"] or not config["user"] or not config["password"]:
        print(f"Email skipped for {to_email}: SMTP not configured.")
        return False

    message = EmailMessage()
    message["Subject"] = subject
    message["From"] = config["from_email"]
    message["To"] = to_email
    message["Reply-To"] = config["admin_email"]
    message["X-Mailer"] = "NexoraMail"
    if cc_list:
        message["Cc"] = ", ".join(dict.fromkeys(cc_list))
    message.set_content(text_body)
    message.add_alternative(html_body, subtype="html")
    for attachment in attachments or []:
        if not attachment or "data" not in attachment or "filename" not in attachment:
            continue
        message.add_attachment(
            attachment["data"],
            maintype=attachment.get("maintype", "application"),
            subtype=attachment.get("subtype", "octet-stream"),
            filename=attachment["filename"],
        )

    try:
        if str(config["port"]) == "465":
            with smtplib.SMTP_SSL(config["host"], config["port"], timeout=SMTP_TIMEOUT) as smtp:
                smtp.login(config["user"], config["password"])
                smtp.send_message(message)
        else:
            with smtplib.SMTP(config["host"], config["port"], timeout=SMTP_TIMEOUT) as smtp:
                smtp.ehlo()
                smtp.starttls()
                smtp.sock.settimeout(SMTP_TIMEOUT)
                smtp.ehlo()
                smtp.login(config["user"], config["password"])
                smtp.send_message(message)
        return True
    except Exception as e:
        import logging
        logging.getLogger("nexora").error(f"SMTP Error: {e}")
        return False


def queue_background_email(task_name, callback, *args):
    """Run email/PDF work on a bounded shared pool instead of one thread per request."""
    global _email_pending
    with _email_pending_lock:
        if _email_pending >= EMAIL_QUEUE_LIMIT:
            logger.error("Email queue full; dropping %s task with args=%s", task_name, args)
            return False
        _email_pending += 1

    def runner():
        global _email_pending
        try:
            with app.app_context():
                callback(*args)
        except Exception:
            logger.exception("Background email task failed: %s args=%s", task_name, args)
        finally:
            with _email_pending_lock:
                _email_pending = max(0, _email_pending - 1)

    _email_executor.submit(runner)
    return True


def registration_details(registration_id):
    row = query(
        """
        SELECT registrations.*,
               COALESCE(games.title, 'Selected game') AS game_title,
               COALESCE(games.platform, '-') AS platform,
               COALESCE(games.individual_price, '') AS individual_price,
               COALESCE(games.group_price, '') AS group_price,
               COALESCE(games.price_for_2, '') AS price_for_2,
               COALESCE(games.price_for_3, '') AS price_for_3,
               COALESCE(games.price_for_4, '') AS price_for_4,
               COALESCE(games.discount_pct, '') AS discount_pct,
               COALESCE(games.discount_from, '') AS discount_from,
               COALESCE(games.discount_to, '') AS discount_to
        FROM registrations LEFT JOIN games ON games.id = registrations.game_id
        WHERE registrations.id = ?
        """,
        (registration_id,),
        one=True,
    )
    if not row:
        return None

    entry_type = (row["entry_type"] or "individual").lower()
    display_member_count = team_total_count(row["members"], entry_type)
    ticket_number = row["ticket_serial"] or make_ticket_serial(row["id"], row["game_title"])
    base_price = registration_effective_price(row, entry_type, display_member_count)
    payable_amount = base_price["discounted"]
    amount_paid = (row["amount_paid"] or "").strip() if "amount_paid" in row.keys() else ""
    payment_status = (row["payment_status"] or "unpaid").lower()
    if not amount_paid:
        amount_paid = payable_amount if payment_status == "paid" else "Rs. 0"

    payable_value = amount_value(payable_amount)
    paid_value = amount_value(amount_paid)
    remaining_value = max(0, payable_value - paid_value)
    if payment_status == "paid":
        remaining_amount = "Rs. 0"
    elif payment_status == "under_verification":
        remaining_amount = "Pending"
    else:
        remaining_amount = f"Rs. {remaining_value:,.0f}" if payable_value else "Not recorded"
    members = parse_team_members(row["members"])
    return {
        "id": row["id"],
        "ref": f"NX-{row['id']:04d}",
        "name": row["full_name"],
        "email": row["email"],
        "phone": row["phone"],
        "cnic": row["cnic"],
        "institution_type": row["institution_type"] or "University",
        "university": row["university"],
        "roll_no": row["roll_no"] or "Not provided",
        "entry_type": entry_type.title(),
        "game": row["game_title"],
        "platform": row["platform"],
        "price": base_price["discounted"],
        "original_price": base_price["original"],
        "has_discount": base_price["active"],
        "team_name": row["team_name"] or "Individual entry",
        "leader_name": row["leader_name"] or row["full_name"],
        "members": row["members"] or "No team members added",
        "member_count": display_member_count,
        "team_roster": team_roster_text(row),
        "team_member_names": [member["name"] for member in members if member["name"]],
        "payment_method": row["payment_method"],
        "payment_status": payment_status.replace("_", " ").title(),
        "total_payable": payable_amount,
        "amount_paid": amount_paid,
        "remaining_amount": remaining_amount,
        "transaction_reference": (row["transaction_reference"] or "Not provided") if "transaction_reference" in row.keys() else "Not provided",
        "created_at": pretty_dt(row["created_at"]),
        "ticket_issued_at": pretty_dt(row["ticket_issued_at"] or row["verified_at"] or row["created_at"]) if "ticket_issued_at" in row.keys() else pretty_dt(row["verified_at"] or row["created_at"]),
        "ticket_number": ticket_number,
        "ticket_id": f"NX-{row['id']:04d}",
        "team_id": f"TEAM-{row['id']:06d}" if entry_type == "group" else "Individual",
        "ticket_token": row["ticket_token"],
        "entry_status": (row["entry_status"] or "pending").lower() if "entry_status" in row.keys() else "pending",
        "verified_at": pretty_dt(row["verified_at"]) if row["verified_at"] else "",
    }


def parse_team_members(members_text):
    parsed = []
    for line in (members_text or "").splitlines():
        clean = line.strip()
        if not clean:
            continue
        if ":" in clean:
            clean = clean.split(":", 1)[1].strip()
        parts = [part.strip() for part in clean.split("|")]
        parsed.append(
            {
                "name": parts[0] if len(parts) > 0 else "",
                "email": parts[1] if len(parts) > 1 else "",
                "phone": parts[2] if len(parts) > 2 else "",
            }
        )
    return parsed


def team_member_count(members_text):
    return sum(
        1
        for member in parse_team_members(members_text)
        if member["name"] or member["email"] or member["phone"]
    )


def team_total_count(members_text, entry_type):
    return team_member_count(members_text) + 1 if (entry_type or "").lower() == "group" else 1


def team_roster_text(row):
    if (row["entry_type"] or "").lower() != "group":
        return "Individual player"
    roster = [f"Team Leader: {row['leader_name'] or row['full_name']}"]
    for member in parse_team_members(row["members"]):
        parts = [part for part in (member["name"], member["email"], member["phone"]) if part]
        if parts:
            roster.append("Member: " + " | ".join(parts))
    return " ; ".join(roster)


def registration_email_recipients(details, settings=None):
    recipients = []
    seen = set()

    def add(email, name, role, member_number=0):
        email = (email or "").strip()
        if not email or email.lower() in seen:
            return
        seen.add(email.lower())
        recipients.append({"email": email, "name": name or email, "role": role, "member_number": member_number})

    add(details["email"], details["leader_name"] or details["name"], "leader", 1)
    if details["entry_type"].lower() == "group":
        for member_number, member in enumerate(parse_team_members(details["members"]), start=2):
            add(member["email"], member["name"], "member", member_number)
    return recipients


def member_ticket_record(details, recipient):
    """Return the persisted serial and opaque token for a group member ticket."""
    if recipient.get("role") != "member":
        return {"ticket_serial": details["ticket_number"], "ticket_token": details["ticket_token"]}

    member_number = int(recipient["member_number"])
    stored = query(
        "SELECT ticket_serial, ticket_token FROM member_tickets WHERE registration_id = ? AND member_number = ?",
        (details["id"], member_number),
        one=True,
    )
    if stored:
        return dict(stored)

    serial = f"{details['ticket_number']}-M{member_number:02d}"
    execute(
        """
        INSERT OR IGNORE INTO member_tickets
        (registration_id, member_number, member_name, member_email, ticket_serial, ticket_token, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        """,
        (
            details["id"], member_number, recipient.get("name", ""), recipient.get("email", ""),
            serial, make_ticket_token(), datetime.now().isoformat(timespec="seconds"),
        ),
    )
    stored = query(
        "SELECT ticket_serial, ticket_token FROM member_tickets WHERE registration_id = ? AND member_number = ?",
        (details["id"], member_number),
        one=True,
    )
    return dict(stored) if stored else {"ticket_serial": serial, "ticket_token": ""}


def member_ticket_serial(details, recipient):
    return member_ticket_record(details, recipient)["ticket_serial"]


def ticket_details_for_recipient(details, recipient):
    recipient_details = dict(details)
    record = member_ticket_record(details, recipient)
    recipient_details["ticket_number"] = record["ticket_serial"]
    recipient_details["ticket_token"] = record["ticket_token"]
    return recipient_details


def ticket_member_roster(details):
    """Every ticket holder on ONE registration, straight from the DB.

    Returns [{"number", "name", "serial", "token"}] — entry 1 is always the
    leader/main registrant using the registration's own token; entries 2..n
    are that registration's stored team members with their persisted
    member_tickets tokens (created on demand). Members of other registrations
    are never mixed in.
    """
    roster = [{
        "number": 1,
        "name": details.get("leader_name") or details.get("name") or "",
        "email": details.get("email") or "",
        "phone": details.get("phone") or "",
        "serial": details["ticket_number"],
        "ticket_id": f"NX-{int(details['id']):04d}",
        "token": details["ticket_token"],
    }]
    if (details.get("entry_type") or "").lower() != "group":
        return roster
    for member in parse_team_members(details.get("members")):
        number = len(roster) + 1
        if not (member["name"] or member["email"] or member["phone"]):
            continue
        record = member_ticket_record(details, {"role": "member", "member_number": number, "name": member["name"], "email": member["email"]})
        roster.append({
            "number": number,
            "name": member["name"] or f"Member {number}",
            "email": member.get("email") or "",
            "phone": member.get("phone") or "",
            "serial": record["ticket_serial"],
            "ticket_id": f"NX-{int(details['id']):04d}-M{number}",
            "token": record["ticket_token"],
        })
    return roster


def ticket_pdf_attachment(details, settings, recipient=None):
    pdf_details = ticket_details_for_recipient(details, recipient or {})
    ticket_number = pdf_details["ticket_number"]
    return {
        "filename": f"{ticket_number}.pdf",
        "data": build_ticket_pdf(pdf_details, settings, ticket_number_override=ticket_number, ticket_holder=recipient),
        "maintype": "application",
        "subtype": "pdf",
    }


def safe_ticket_pdf_attachment(details, settings, recipient=None):
    try:
        return ticket_pdf_attachment(details, settings, recipient)
    except Exception as exc:
        print(f"Ticket PDF generation failed for {details.get('ref', 'unknown')}: {exc}")
        return None


def ticket_qr_payload(details):
    """The QR holds ONLY the opaque token — never a URL.

    Earlier the QR encoded a link to the scanner page, so anyone who opened a
    ticket PDF on a phone could follow it. Now the code is meaningless outside
    the admin scanner, which is behind the login."""
    return f"NXT:{details['ticket_token']}"


def team_ticket_qr_items(details):
    """Return one opaque, persisted QR payload for every player on a team ticket."""
    leader = {
        "email": details["email"],
        "name": details["leader_name"] or details["name"],
        "role": "leader",
        "member_number": 1,
    }
    leader_ticket = member_ticket_record(details, leader)
    items = [{
        "name": leader["name"], "email": details["email"], "phone": details["phone"],
        "ticket_serial": leader_ticket["ticket_serial"],
        "ticket_id": f"NX-{int(details['id']):04d}",
        "token": leader_ticket["ticket_token"],
    }]
    if details["entry_type"].lower() == "group":
        for member_number, member in enumerate(parse_team_members(details["members"]), start=2):
            if not member["name"]:
                continue
            recipient = {"name": member["name"], "email": member["email"], "role": "member", "member_number": member_number}
            member_ticket = member_ticket_record(details, recipient)
            items.append({
                "name": member["name"], "email": member["email"], "phone": member["phone"],
                "ticket_serial": member_ticket["ticket_serial"],
                "ticket_id": f"NX-{int(details['id']):04d}-M{member_number}",
                "token": member_ticket["ticket_token"],
            })
    return [item for item in items if item["token"]]


def group_email_delivery(details):
    """A team receives one shared ticket: leader in To, all listed players in CC."""
    leader = {
        "email": details["email"],
        "name": details["leader_name"] or details["name"],
        "role": "leader",
        "member_number": 1,
    }
    cc_emails = []
    for member in parse_team_members(details["members"]):
        email = (member["email"] or "").strip()
        if email and email.lower() != leader["email"].strip().lower():
            cc_emails.append(email)
    return leader, list(dict.fromkeys(cc_emails))


def normalize_qr_token(raw):
    """Accept an opaque token, an old-style scanner URL, a ticket serial,
    or an NX-#### reference. Returns the raw text for the lookup layer."""
    value = (raw or "").strip()
    if not value:
        return ""
    if value.upper().startswith("NXT:"):
        return value[4:].strip()
    parsed = urlparse(value)
    if parsed.scheme and parsed.query:
        token = parse_qs(parsed.query).get("token", [""])[0].strip()
        if token:
            return token
    return value


def find_registration_by_code(code):
    """Look a ticket up by QR token, ticket serial, or NX-#### reference,
    so the admin can type a ticket number when the camera is unavailable."""
    code = (code or "").strip()
    if not code:
        return None

    ticket = find_ticket_by_code(code)
    return ticket["registration"] if ticket else None


def find_ticket_by_code(code):
    """Resolve a QR/manual value to one ticket, not merely its registration.

    Group members have their own serial, QR token and entry state. The ticket
    token is opaque, so no personal data is embedded in a printable QR code.
    """
    code = (code or "").strip()
    if not code:
        return None

    member = query(
        "SELECT * FROM member_tickets WHERE ticket_token = ? OR UPPER(ticket_serial) = ?",
        (code, code.upper()), one=True,
    )
    if member:
        row = query(REGISTRATION_SELECT + " WHERE registrations.id = ?", (member["registration_id"],), one=True)
        if row:
            return {"registration": row, "ticket": member, "kind": "member"}

    row = query(REGISTRATION_SELECT + " WHERE registrations.ticket_token = ?", (code,), one=True)
    if row:
        return {"registration": row, "ticket": row, "kind": "registration"}

    row = query(
        REGISTRATION_SELECT + " WHERE UPPER(registrations.ticket_serial) = ?",
        (code.upper(),),
        one=True,
    )
    if row:
        return {"registration": row, "ticket": row, "kind": "registration"}

    # NX-0007 / nx0007 / plain 7 all resolve to registration id 7
    digits = re.sub(r"\D", "", code)
    if digits:
        row = query(
            REGISTRATION_SELECT + " WHERE registrations.id = ?", (int(digits),), one=True
        )
        if row:
            return {"registration": row, "ticket": row, "kind": "registration"}

    return None


def atomic_checkin(table, ticket_id, staff, gate, override=False, override_reason=""):
    """Atomically check in a ticket using UPDATE ... WHERE to prevent race conditions.
    Returns True if this call performed the check-in, False if already checked in."""
    safe_table_name(table)
    now = datetime.now().isoformat(timespec="seconds")
    with db() as conn:
        if override:
            cur = conn.execute(
                f"UPDATE {table} SET entry_status = 'approved', entry_marked_at = ?, checkin_gate = ?, checked_in_by = ?, override_by = ?, override_at = ?, override_reason = ? WHERE id = ? AND (entry_status IS NULL OR entry_status = '' OR entry_status = 'pending')",
                (now, gate, staff, staff, now, override_reason, ticket_id),
            )
        else:
            cur = conn.execute(
                f"UPDATE {table} SET entry_status = 'approved', entry_marked_at = ?, checkin_gate = ?, checked_in_by = ? WHERE id = ? AND (entry_status IS NULL OR entry_status = '' OR entry_status = 'pending')",
                (now, gate, staff, ticket_id),
            )
        conn.commit()
        return cur.rowcount > 0


def atomic_reject(table, ticket_id, staff, reason, gate=""):
    """Atomically reject a ticket using UPDATE ... WHERE to prevent race conditions."""
    safe_table_name(table)
    now = datetime.now().isoformat(timespec="seconds")
    with db() as conn:
        cur = conn.execute(
            f"UPDATE {table} SET entry_status = 'rejected', entry_marked_at = ?, entry_note = ?, rejection_reason = ?, rejected_by = ?, rejected_at = ? WHERE id = ? AND (entry_status IS NULL OR entry_status = '' OR entry_status = 'pending')",
            (now, reason, reason, staff, now, ticket_id),
        )
        conn.commit()
        return cur.rowcount > 0


def build_qr_png(value):
    try:
        import qrcode

        stream = io.BytesIO()
        image = qrcode.make(value)
        image.save(stream, format="PNG")
        return stream.getvalue()
    except ModuleNotFoundError:
        # The PDF stack already provides a QR implementation. Use it when the
        # optional Pillow/qrcode package is not installed on a deployment.
        from reportlab.graphics import renderPM
        from reportlab.graphics.barcode import qr
        from reportlab.graphics.shapes import Drawing

        code = qr.QrCodeWidget(value)
        bounds = code.getBounds()
        width, height = bounds[2] - bounds[0], bounds[3] - bounds[1]
        size = 180
        drawing = Drawing(size, size, transform=[size / width, 0, 0, size / height, 0, 0])
        drawing.add(code)
        return renderPM.drawToString(drawing, fmt="PNG")


def build_ticket_pdf(details, settings, ticket_number_override=None, ticket_holder=None):
    """One-page Nexora ticket.

    Solo -> game, nick name, player info, main QR.
    Team -> same header info plus a compact grid where EVERY member gets
            their own unique scannable QR with their name printed under it.
    The ticket_holder/ticket_number_override arguments are kept for email-flow
    compatibility; the generated document is always the complete one-page
    ticket for the whole registration.
    """
    details = dict(details) if details else {}
    settings = dict(settings) if settings else {}
    from reportlab.lib import colors
    from reportlab.lib.pagesizes import A4
    from reportlab.lib.styles import getSampleStyleSheet
    from reportlab.lib.units import mm
    from reportlab.graphics import renderPDF
    from reportlab.graphics.barcode import qr
    from reportlab.graphics.shapes import Drawing
    from reportlab.pdfbase import pdfmetrics
    from reportlab.pdfbase.ttfonts import TTFont
    from reportlab.platypus import Flowable, Image, Paragraph, SimpleDocTemplate, Spacer, Table, TableStyle
    from xml.sax.saxutils import escape

    regular_font = "NexoraTicketRegular"
    bold_font = "NexoraTicketBold"
    if regular_font not in pdfmetrics.getRegisteredFontNames():
        _font_regular = None
        _font_bold = None
        for _dir, _r, _b in [
            (r"C:\Windows\Fonts", "arial.ttf", "arialbd.ttf"),
            (os.path.join(BASE_DIR, "static", "fonts"), "arial.ttf", "arialbd.ttf"),
            (os.path.join(BASE_DIR, "static", "fonts"), "DejaVuSans.ttf", "DejaVuSans-Bold.ttf"),
            ("/usr/share/fonts/truetype/dejavu", "DejaVuSans.ttf", "DejaVuSans-Bold.ttf"),
            ("/usr/share/fonts/truetype/liberation", "LiberationSans-Regular.ttf", "LiberationSans-Bold.ttf"),
            ("/usr/share/fonts", "DejaVuSans.ttf", "DejaVuSans-Bold.ttf"),
        ]:
            _rpath = os.path.join(_dir, _r)
            _bpath = os.path.join(_dir, _b)
            if os.path.isfile(_rpath) and os.path.isfile(_bpath):
                _font_regular = _rpath
                _font_bold = _bpath
                break
        try:
            pdfmetrics.registerFont(TTFont(regular_font, _font_regular))
        except Exception:
            regular_font = "Helvetica"
        try:
            pdfmetrics.registerFont(TTFont(bold_font, _font_bold))
        except Exception:
            bold_font = "Helvetica-Bold"

    def pdf_text(value):
        value = str(value or "").replace("\r", " ").replace("\n", " ")
        value = re.sub(r"[\x00-\x08\x0b\x0c\x0e-\x1f]", "", value)
        return escape(re.sub(r"\s+", " ", value).strip())

    # -- Logo: canonical static asset first (webp flattened via Pillow so the
    # white mark stays intact), uploads copy fallback. Aspect preserved. --
    logo_info = None
    logo_candidates = [
        static_file_abs(settings.get("site_logo", "")),
        static_file_abs("Images/Logo/Nexora Logo.png"),
        static_file_abs("Images/Logo/Nexora Logo.webp"),
        Path(BASE_DIR) / "static" / "uploads" / "Nexora Logo.png",
    ]
    for candidate in logo_candidates:
        try:
            if candidate and os.path.isfile(str(candidate)):
                from PIL import Image as PILImage
                with PILImage.open(str(candidate)) as pil:
                    pil.load()
                    buf = io.BytesIO()
                    pil.convert("RGBA").save(buf, format="PNG")
                    buf.seek(0)
                logo_info = {"buf": buf, "w": pil.width, "h": pil.height}
                break
        except Exception:
            continue

    ticket_number = re.sub(r"[^A-Za-z0-9-]", "", str(ticket_number_override or details.get("ticket_number", ""))).upper()
    if not ticket_number:
        ticket_number = f"NX-TICKET-{int(details['id']):04d}"

    is_group = (details["entry_type"] or "").lower() == "group"
    roster = ticket_member_roster(details)
    entry_status = (details.get("entry_status") or "pending").lower()
    # registration_details returns "Under Verification"; normalise back to the raw key
    payment_status = (details["payment_status"] or "unpaid").lower().replace(" ", "_")

    if payment_status == "paid":
        status_text, status_bg = "PAID - APPROVED", colors.HexColor("#13734d")
    elif payment_status == "under_verification":
        status_text, status_bg = "UNDER VERIFICATION - NOT VALID FOR ENTRY", colors.HexColor("#b45309")
    elif payment_status == "partial":
        status_text, status_bg = "PARTIAL PAYMENT - NOT CONFIRMED", colors.HexColor("#b45309")
    elif payment_status == "refunded":
        status_text, status_bg = "REFUNDED - NOT VALID FOR ENTRY", colors.HexColor("#a32132")
    else:
        status_text, status_bg = "UNPAID - NOT VALID FOR ENTRY", colors.HexColor("#a32132")

    class QRFlowable(Flowable):
        def __init__(self, value, size):
            super().__init__()
            self.value = value
            self.width = size
            self.height = size

        def draw(self):
            code = qr.QrCodeWidget(self.value)
            bounds = code.getBounds()
            width = bounds[2] - bounds[0]
            height = bounds[3] - bounds[1]
            drawing = Drawing(self.width, self.height, transform=[self.width / width, 0, 0, self.height / height, 0, 0])
            drawing.add(code)
            renderPDF.draw(drawing, self.canv, 0, 0)

    stream = io.BytesIO()
    doc = SimpleDocTemplate(
        stream,
        pagesize=A4,
        rightMargin=12 * mm,
        leftMargin=12 * mm,
        topMargin=10 * mm,
        bottomMargin=10 * mm,
        title=f"{ticket_number} - {str(details.get('name', '')).strip()}",
    )
    styles = getSampleStyleSheet()
    story = []

    brand_style = styles["Title"].clone("brand")
    brand_style.fontName = bold_font
    brand_style.fontSize = 17
    brand_style.leading = 20
    brand_style.textColor = colors.white
    brand_sub = styles["BodyText"].clone("brand-sub")
    brand_sub.fontName = regular_font
    brand_sub.fontSize = 9
    brand_sub.leading = 11
    brand_sub.textColor = colors.HexColor("#e5e7eb")

    label_style = styles["BodyText"].clone("lbl")
    label_style.fontName = bold_font
    label_style.fontSize = 8.5
    label_style.leading = 11.5
    label_style.textColor = colors.HexColor("#64748b")
    value_style = styles["BodyText"].clone("val")
    value_style.fontName = regular_font
    value_style.fontSize = 9.5
    value_style.leading = 11.5
    value_style.wordWrap = "CJK"
    value_style.textColor = colors.HexColor("#111827")

    big_value = styles["BodyText"].clone("big-val")
    big_value.fontName = bold_font
    big_value.fontSize = 13
    big_value.leading = 16
    big_value.alignment = 1
    big_value.wordWrap = "CJK"
    big_value.textColor = colors.HexColor("#111827")

    name_style = styles["BodyText"].clone("qr-name")
    name_style.fontName = bold_font
    name_style.fontSize = 7.6
    name_style.leading = 9.2
    name_style.alignment = 1
    name_style.wordWrap = "CJK"

    section_style = styles["BodyText"].clone("section")
    section_style.fontName = bold_font
    section_style.fontSize = 9.5
    section_style.leading = 12
    section_style.textColor = colors.white

    status_line = styles["BodyText"].clone("status-line")
    status_line.fontName = bold_font
    status_line.fontSize = 10.5
    status_line.leading = 13
    status_line.alignment = 1
    status_line.textColor = colors.white

    # -- Dark brand band: centered logo on black background.
    if logo_info is not None:
        ratio = logo_info["w"] / float(logo_info["h"] or 1)
        box_w, box_h = 140 * mm, 28 * mm
        draw_w = min(box_w, box_h * ratio)
        draw_h = draw_w / ratio if ratio else box_h
        img = Image(logo_info["buf"], draw_w, min(draw_h, box_h))
        brand_band = Table([[img]], colWidths=[154 * mm])
        brand_band.setStyle(TableStyle([
            ("ALIGN", (0, 0), (-1, -1), "CENTER"), ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
            ("BACKGROUND", (0, 0), (-1, -1), colors.HexColor("#0b0d14")),
            ("TOPPADDING", (0, 0), (-1, -1), 10), ("BOTTOMPADDING", (0, 0), (-1, -1), 10),
            ("LEFTPADDING", (0, 0), (-1, -1), 0), ("RIGHTPADDING", (0, 0), (-1, -1), 0),
        ]))
    else:
        brand_band = Table([[""]], colWidths=[154 * mm], rowHeights=[28 * mm])
        brand_band.setStyle(TableStyle([
            ("BACKGROUND", (0, 0), (-1, -1), colors.HexColor("#0b0d14")),
        ]))
    story.append(brand_band)
    story.append(Spacer(1, 6))

    # -- Status strip (payment state + check-in warning) --
    if entry_status == "approved":
        entry_chip = "  |  ALREADY ENTERED"
        strip_color = colors.HexColor("#4b5563")
    elif entry_status == "rejected":
        entry_chip = "  |  ENTRY REJECTED"
        strip_color = colors.HexColor("#7f1d1d")
    else:
        entry_chip = ""
        strip_color = status_bg
    status_strip = Table(
        [[Paragraph(pdf_text(status_text + entry_chip).replace("|", "|"), status_line)]],
        colWidths=[154 * mm],
    )
    status_strip.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (-1, -1), strip_color),
        ("TOPPADDING", (0, 0), (-1, -1), 5), ("BOTTOMPADDING", (0, 0), (-1, -1), 5),
        ("LEFTPADDING", (0, 0), (-1, -1), 6), ("RIGHTPADDING", (0, 0), (-1, -1), 6),
    ]))
    story.append(status_strip)
    story.append(Spacer(1, 8))

    def kv_section(title, rows):
        data = [[Paragraph("<b>" + pdf_text(title) + "</b>", section_style), ""]]
        for label, value in rows:
            data.append([
                Paragraph(pdf_text(label), label_style),
                Paragraph(pdf_text(value), value_style),
            ])
        t = Table(data, colWidths=[42 * mm, 112 * mm])
        t.setStyle(TableStyle([
            ("SPAN", (0, 0), (-1, 0)),
            ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#7f1d1d")),
            ("GRID", (0, 0), (-1, -1), 0.4, colors.HexColor("#dbe2ec")),
            ("BACKGROUND", (0, 1), (0, -1), colors.HexColor("#f4f6fb")),
            ("ROWBACKGROUNDS", (1, 1), (1, -1), [colors.white, colors.HexColor("#fbfdff")]),
            ("VALIGN", (0, 0), (-1, -1), "TOP"),
            ("LEFTPADDING", (0, 0), (-1, -1), 6), ("RIGHTPADDING", (0, 0), (-1, -1), 6),
            ("TOPPADDING", (0, 0), (-1, -1), 3), ("BOTTOMPADDING", (0, 0), (-1, -1), 3),
        ]))
        return t

    nick = details.get("team_name") or ""
    leader_name = details.get("leader_name") or details.get("name") or ""
    info_rows = [
        ("Event Name", settings["event_title"] if "event_title" in settings.keys() else "Nexora Esports Championship"),
        ("Ticket Number", ticket_number),
        ("Registration ID", f"NX-{int(details['id']):04d}"),
        ("Game Name", details["game"]),
        ("Platform", details.get("platform", "-")),
        ("Registration Type", details["entry_type"]),
    ]
    if nick:
        info_rows.append(("Nick Name", nick))
    if is_group:
        info_rows.append(("Team Name", details.get("team_name") or nick))
        info_rows.append(("Group Leader", leader_name))
    info_rows.append(("Player Name", details.get("name") or ""))
    if not is_group:
        info_rows.append(("Email", details.get("email")))
        info_rows.append(("Phone Number", details.get("phone")))
        info_rows.append(("CNIC", details.get("cnic")))
    info_rows.append(("Payment Status", "PAID" if payment_status == "paid" else ("UNDER VERIFICATION" if payment_status == "under_verification" else "UNPAID")))
    info_rows.append(("Amount Paid", details.get("amount_paid") or "Rs. 0"))
    info_rows.append(("Amount Due", details.get("remaining_amount") or "Rs. 0"))
    story.append(kv_section("TICKET INFORMATION", info_rows))
    story.append(Spacer(1, 8))

    # -- Main QR (leader on team tickets; the solo player otherwise) --
    leader_entry = roster[0]

    if is_group:
        # -- Group: one column per member (leader + members) with Name, Email, Phone, QR --
        all_members = roster
        story.append(Spacer(1, 6))
        members_header = Table(
            [[Paragraph("<b>TEAM ROSTER - EACH MEMBER SCANS THEIR OWN QR</b>", section_style)]],
            colWidths=[154 * mm],
        )
        members_header.setStyle(TableStyle([
            ("BACKGROUND", (0, 0), (-1, -1), colors.HexColor("#263b67")),
            ("TOPPADDING", (0, 0), (-1, -1), 4), ("BOTTOMPADDING", (0, 0), (-1, -1), 4),
            ("LEFTPADDING", (0, 0), (-1, -1), 6),
        ]))
        story.append(members_header)
        col_count = len(all_members)
        columns = 5 if col_count > 9 else (4 if col_count > 6 else 3)
        member_qr_size = {3: 25 * mm, 4: 22 * mm, 5: 20 * mm}[columns]
        cell_w = 154 * mm / columns

        label_s = styles["BodyText"].clone("col-lbl")
        label_s.fontName = bold_font
        label_s.fontSize = 7
        label_s.leading = 9
        label_s.alignment = 1
        label_s.textColor = colors.HexColor("#64748b")

        val_s = styles["BodyText"].clone("col-val")
        val_s.fontName = regular_font
        val_s.fontSize = 8
        val_s.leading = 10
        val_s.alignment = 1
        val_s.wordWrap = "CJK"
        val_s.textColor = colors.HexColor("#111827")

        role_s = styles["BodyText"].clone("col-role")
        role_s.fontName = bold_font
        role_s.fontSize = 6.5
        role_s.leading = 8
        role_s.alignment = 1
        role_s.textColor = colors.HexColor("#7f1d1d")

        grid_rows = []
        for start in range(0, col_count, columns):
            chunk = all_members[start:start + columns]
            qr_row, role_row, name_row, email_row, phone_row, tid_row, tnum_row = [], [], [], [], [], [], []
            for m in chunk:
                qr_row.append(QRFlowable(f"NXT:{m['token']}", member_qr_size))
                role_label = "LEADER" if m["number"] == 1 else f"MEMBER {m['number']}"
                role_row.append(Paragraph(pdf_text(role_label), role_s))
                name_row.append(Paragraph(pdf_text(m["name"]), val_s))
                email_row.append(Paragraph(pdf_text(m.get("email") or ""), val_s))
                phone_row.append(Paragraph(pdf_text(m.get("phone") or ""), val_s))
                tid_row.append(Paragraph(pdf_text(m.get("ticket_id") or ""), val_s))
                tnum_row.append(Paragraph(pdf_text(m.get("serial") or ""), val_s))
            while len(qr_row) < columns:
                qr_row.append(""); role_row.append(""); name_row.append("")
                email_row.append(""); phone_row.append(""); tid_row.append(""); tnum_row.append("")
            grid_rows.append(qr_row)
            grid_rows.append(role_row)
            grid_rows.append(name_row)
            grid_rows.append(email_row)
            grid_rows.append(phone_row)
            grid_rows.append(tid_row)
            grid_rows.append(tnum_row)

        member_grid = Table(grid_rows, colWidths=[cell_w] * columns)
        gs = [
            ("ALIGN", (0, 0), (-1, -1), "CENTER"),
            ("VALIGN", (0, 0), (-1, 0), "MIDDLE"),
            ("LEFTPADDING", (0, 0), (-1, -1), 2), ("RIGHTPADDING", (0, 0), (-1, -1), 2),
            ("TOPPADDING", (0, 0), (-1, -1), 2),
            ("BOTTOMPADDING", (0, 0), (-1, 0), 1),
            ("BOTTOMPADDING", (0, 1), (-1, -1), 3),
        ]
        for r in range(0, len(grid_rows) - 6, 7):
            gs.append(("LINEBELOW", (0, r + 6), (-1, r + 6), 0.4, colors.HexColor("#e2e8f0")))
        member_grid.setStyle(TableStyle(gs))
        story.append(member_grid)
    else:
        # -- Solo: QR + name --
        main_qr_size = 42 * mm
        qr_block = Table([[QRFlowable(f"NXT:{leader_entry['token']}", main_qr_size)]], colWidths=[main_qr_size + 4 * mm])
        qr_block.setStyle(TableStyle([
            ("ALIGN", (0, 0), (-1, -1), "CENTER"),
            ("BOX", (0, 0), (-1, -1), 0.6, colors.HexColor("#cbd5e1")),
            ("TOPPADDING", (0, 0), (-1, -1), 3), ("BOTTOMPADDING", (0, 0), (-1, -1), 3),
        ]))
        holder_para = Paragraph(pdf_text(leader_entry["name"]), big_value)
        holder_line = Table([[holder_para]], colWidths=[154 * mm])
        holder_line.setStyle(TableStyle([
            ("ALIGN", (0, 0), (-1, -1), "CENTER"), ("TOPPADDING", (0, 0), (-1, -1), 3),
        ]))
        cap_style = styles["BodyText"].clone("cap")
        cap_style.fontName = regular_font
        cap_style.fontSize = 7.5
        cap_style.alignment = 1
        cap_style.textColor = colors.HexColor("#64748b")
        caption = Paragraph(pdf_text("Entry QR - scan at the gate"), cap_style)
        story.append(qr_block)
        story.append(holder_line)
        story.append(caption)

    story.append(Spacer(1, 7))
    footer_style = styles["BodyText"].clone("footer")
    footer_style.fontName = regular_font
    footer_style.fontSize = 7.8
    footer_style.leading = 10
    footer_style.alignment = 1
    footer_style.textColor = colors.HexColor("#64748b")
    contact_email = settings["contact_email"] if "contact_email" in settings.keys() and settings["contact_email"] else ""
    footer_lines = (
        "Carry your original CNIC along with this ticket for verification at the venue. "
        "Each team member must present their own QR code at the gate."
        if is_group else
        "Carry your original CNIC along with this ticket for verification at the venue."
    )
    footer_lines += "  Email: nexora.esport.support@gmail.com  |  WhatsApp: +92 331 0314962"
    story.append(Paragraph(pdf_text(footer_lines), footer_style))

    doc.build(story)
    return stream.getvalue()


def registration_roster_lines(details):
    """Numbered '1. Name' roster lines for confirmation emails (leader first)."""
    names = [details.get("leader_name") or details.get("name") or ""]
    names.extend(member["name"] for member in parse_team_members(details.get("members")) if member["name"])
    return [f"{i}. {name}" for i, name in enumerate(names, 1) if name]


def send_registration_emails(registration_id):
    """Post-submit confirmation notice: tells the player(s) the registration
    was received and is UNDER VERIFICATION.

    Deliberately does NOT attach the official ticket PDF — tickets are only
    emailed after admin/payment verification via send_approval_email.

    Recipients come strictly from the DB record: solo -> registrant email;
    team -> leader (To) + every stored member email (CC). Addresses are
    trimmed, lowercased for dedup, format-checked, and empty ones skipped.

    Returns {"sent": [emails], "failed": [(email, reason)], "configured": bool}
    so the caller can tell the user whether the email went out — the
    registration itself is never rolled back because of SMTP problems.
    """
    result = {"sent": [], "failed": [], "configured": True}
    try:
        details = registration_details(registration_id)
        if not details:
            result["failed"].append(("", "Registration record not found."))
            return result
        settings = query("SELECT * FROM settings WHERE id = 1", one=True)
        config = email_config()
        if not config["enabled"]:
            logger.error("Registration confirmation skipped for %s: email is not configured.", details["ref"])
            result["configured"] = False
            result["failed"] = [
                (r["email"], "Email is not configured on the server")
                for r in registration_email_recipients(details, settings)
            ]
            return result

        event_title = (settings["event_title"] if settings and "event_title" in settings.keys() else "") or "Nexora Esports Championship"
        subject = f"{event_title} - Registration Received ({details['ref']})"

        def _valid(email):
            return bool(email) and validate_email_format(email)

        if details["entry_type"].lower() == "group":
            roster = registration_roster_lines(details)
            all_members = [{"role": "leader", "name": details["leader_name"] or details["name"], "email": details["email"], "member_number": 1}]
            for i, member in enumerate(parse_team_members(details["members"]), start=2):
                email = (member.get("email") or "").strip()
                if email and validate_email_format(email):
                    all_members.append({"role": "member", "name": member.get("name", ""), "email": email, "member_number": i})

            leader = all_members[0]
            cc_emails = [m["email"] for m in all_members[1:]]
            try:
                greeting = f"Dear {leader['name']},"
                body = (
                    f"{greeting}\n\n"
                    f"Your team registration has been successfully received and is currently UNDER VERIFICATION.\n\n"
                    f"Game: {details['game']}\n"
                    f"Team: {details['team_name']}\n"
                    f"Group Leader: {details['leader_name']}\n"
                    f"Team Members:\n" + "\n".join(roster) + "\n"
                    f"Registration Reference: {details['ref']}\n\n"
                    f"Status: UNDER VERIFICATION\n\n"
                    f"Your registration/payment is currently under verification. Once it is verified, "
                    f"the official ticket(s) will be generated and sent to this email address. "
                    f"You do not need to register again."
                )
                user_html = render_template(
                    "emails/registration_confirmation.html",
                    details=details, settings=settings, recipient=leader,
                    roster=roster, is_team=True,
                )
                send_email(leader["email"], subject, user_html, body, cc_emails=cc_emails)
                result["sent"].append(leader["email"])
                result["sent"].extend(cc_emails)
            except Exception as exc:
                logger.exception("Registration confirmation email failed for %s", details["ref"])
                for m in all_members:
                    result["failed"].append((m["email"], str(exc)))
        else:
            recipient = {"role": "leader", "name": details["name"], "email": details["email"], "member_number": 1}
            user_html = render_template(
                "emails/registration_confirmation.html",
                details=details, settings=settings, recipient=recipient,
                roster=[], is_team=False,
            )
            user_text = (
                f"Dear {details['name']},\n\n"
                f"Your registration has been successfully received and is currently UNDER VERIFICATION.\n\n"
                f"Game: {details['game']}\n"
                f"Nick Name: {details['team_name']}\n"
                f"Registration Reference: {details['ref']}\n\n"
                f"Status: UNDER VERIFICATION\n\n"
                f"Your registration/payment is currently under verification. Once it is verified, "
                f"your official ticket will be generated and sent to you by email. "
                f"You do not need to register again."
            )
            send_email(details["email"], subject, user_html, user_text)
            result["sent"].append(details["email"])

        # Admin notice (best effort — never blocks the player's confirmation)
        try:
            admin_html = render_template("emails/registration_admin.html", details=details, settings=settings)
            admin_text = (
                f"New registration {details['ref']}: {details['name']} for {details['game']} "
                f"({details['entry_type']}). Status: UNDER VERIFICATION. "
                f"Verify the payment proof in the admin panel."
            )
            send_email(
                admin_notification_email(),
                f"New Registration - Verification Needed ({details['ref']})",
                admin_html,
                admin_text,
            )
        except Exception:
            logger.exception("Admin registration notice failed for %s", details["ref"])
    except Exception as exc:
        # Log the technical cause server-side only; never expose SMTP details.
        logger.exception("Registration confirmation email failed for registration #%s", registration_id)
        result["failed"].append(("", str(exc)))
    return result


def _send_ticket_email_to_recipients(registration_id, template_name, subject_prefix, text_builder, template_vars=None):
    """Shared implementation: build + send the ticket PDF email.
    Solo: one email to the registrant.
    Team: one email to ALL members (leader in To, rest in CC) with the
    complete team ticket PDF attached.
    Returns {"sent": [...], "failed": [...]}.
    Never raises; SMTP problems are logged server-side without credentials."""
    result = {"sent": [], "failed": []}
    details = registration_details(registration_id)
    if not details:
        return result
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    config = email_config()
    if not config["enabled"]:
        logger.error("Ticket email skipped for %s: email is not configured.", details["ref"])
        return {
            "sent": [],
            "failed": [(r["email"], "Email is not configured on the server") for r in registration_email_recipients(details, settings)],
        }

    extra = template_vars or {}
    all_recipients = registration_email_recipients(details, settings)
    is_group = details["entry_type"].lower() == "group"

    if not is_group or len(all_recipients) <= 1:
        # Solo or single-recipient: send one email directly
        for recipient in all_recipients:
            try:
                recipient_details = ticket_details_for_recipient(details, recipient)
                attachment = safe_ticket_pdf_attachment(details, settings, recipient)
                attachments = [attachment] if attachment else []
                html = render_template(
                    template_name,
                    details=recipient_details,
                    settings=settings,
                    recipient=recipient,
                    **extra,
                )
                text = text_builder(details, recipient_details, recipient)
                send_email(
                    recipient["email"],
                    f"{subject_prefix} ({details['ref']})",
                    html, text, attachments=attachments,
                )
                result["sent"].append(recipient["email"])
            except Exception as exc:
                logger.exception("Ticket email to %s failed for %s", recipient["email"], details["ref"])
                result["failed"].append((recipient["email"], str(exc)))
        return result

    # ── Team: ONE email to ALL members, each in To, with the full team ticket PDF ──
    roster = registration_roster_lines(details)
    all_emails = [r["email"] for r in all_recipients if r.get("email")]
    if not all_emails:
        return result

    try:
        attachment = safe_ticket_pdf_attachment(details, settings)
        attachments = [attachment] if attachment else []
        html = render_template(
            template_name,
            details=details,
            settings=settings,
            recipient=all_recipients[0] if all_recipients else {},
            roster=roster,
            is_team=True,
            **extra,
        )
        text = text_builder(details, details, all_recipients[0] if all_recipients else {})
        send_email(
            all_emails[0],
            f"{subject_prefix} ({details['ref']})",
            html, text, attachments=attachments,
            cc_emails=all_emails[1:],
        )
        result["sent"].extend(all_emails)
    except Exception as exc:
        logger.exception("Team ticket email failed for %s", details["ref"])
        for email in all_emails:
            result["failed"].append((email, str(exc)))

    return result


def send_approval_email(registration_id):
    """Sent when the admin flips the status to Paid."""
    details = registration_details(registration_id)
    template_vars = {}
    if details:
        template_vars = {
            "payment_received": details.get("amount_paid", "Rs. 0"),
            "payment_due": details.get("remaining_amount", "Rs. 0"),
            "total_payable": details.get("total_payable", "Rs. 0"),
        }

    def _text(details, recipient_details, recipient):
        return (
            f"Dear {recipient['name']}, payment is received and confirmed for {details['game']} "
            f"({details['entry_type']}). Team: {details['team_name']}. Ticket: {recipient_details['ticket_number']}. "
            f"Amount Received: {details.get('amount_paid', 'Rs. 0')}. "
            f"Amount Due: {details.get('remaining_amount', 'Rs. 0')}. CNIC: {details['cnic']}."
        )

    return _send_ticket_email_to_recipients(
        registration_id, "emails/payment_approved.html", "Payment Received - Slot Confirmed", _text,
        template_vars=template_vars,
    )


def send_waiting_email(registration_id):
    details = registration_details(registration_id)
    template_vars = {}
    if details:
        template_vars = {
            "payment_received": details.get("amount_paid", "Rs. 0"),
            "payment_due": details.get("remaining_amount", "Rs. 0"),
            "total_payable": details.get("total_payable", "Rs. 0"),
        }

    def _text(details, recipient_details, recipient):
        return (
            f"Dear {recipient['name']}, payment status for {details['game']} is waiting for admin approval. "
            f"Ticket: {recipient_details['ticket_number']}. "
            f"Amount Received: {details.get('amount_paid', 'Rs. 0')}. "
            f"Amount Due: {details.get('remaining_amount', 'Rs. 0')}."
        )

    return _send_ticket_email_to_recipients(
        registration_id, "emails/registration_user.html", "Payment Waiting For Approval", _text,
        template_vars={"status_type": "pending", **template_vars},
    )


def send_unpaid_email(registration_id):
    """Sent when admin marks registration as unpaid (payment not confirmed)."""
    details = registration_details(registration_id)
    template_vars = {}
    if details:
        template_vars = {
            "payment_received": details.get("amount_paid", "Rs. 0"),
            "payment_due": details.get("remaining_amount", "Rs. 0"),
            "total_payable": details.get("total_payable", "Rs. 0"),
        }

    def _text(details, recipient_details, recipient):
        return (
            f"Dear {recipient['name']}, your payment for {details['game']} "
            f"({details['entry_type']}) could not be confirmed. "
            f"Team: {details['team_name']}. Ticket: {recipient_details['ticket_number']}. "
            f"Amount Received: {details.get('amount_paid', 'Rs. 0')}. "
            f"Amount Due: {details.get('remaining_amount', 'Rs. 0')}. "
            f"Please share a screenshot of your payment to nexora.esport.support@gmail.com "
            f"or WhatsApp us at +92 331 0314962 "
            f"so we can verify and update your status."
        )

    return _send_ticket_email_to_recipients(
        registration_id, "emails/registration_user.html", "Payment Not Confirmed", _text,
        template_vars={"status_type": "unpaid", **template_vars},
    )


def send_ticket_email_in_background(email_sender, registration_id):
    """Keep admin status changes responsive while ticket email/PDF work runs."""
    queued = queue_background_email("ticket", email_sender, registration_id)
    if not queued:
        logger.error("Ticket email was not queued for registration #%s", registration_id)


def send_contact_email(contact_id):
    """Notify the admin that someone used the Get In Touch form."""
    row = query("SELECT * FROM contacts WHERE id = ?", (contact_id,), one=True)
    if not row:
        return
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    contact = {
        "name": row["name"],
        "email": row["email"],
        "phone": row["phone"],
        "message": row["message"],
        "created_at": pretty_dt(row["created_at"]),
    }
    html = render_template("emails/contact_admin.html", contact=contact, settings=settings)
    text = f"New message from {contact['name']} ({contact['email']} / {contact['phone']}):\n\n{contact['message']}"
    try:
        send_email(admin_notification_email(), f"New Contact Message - {contact['name']}", html, text)
    except Exception as exc:
        print(f"Contact email failed: {exc}")


def admin_required(view):
    @wraps(view)
    def wrapped(*args, **kwargs):
        if not session.get("admin"):
            if request.is_json or request.path.endswith("/verify") or request.path.endswith("/decision") or request.path.endswith("/confirm-payment"):
                return jsonify({"ok": False, "status": "unauthorized", "message": "Admin login required."}), 401
            flash("Admin login required.", "warning")
            return redirect(url_for("admin_login"))
        # Verify session has not been revoked — check user still exists and is active
        username = session.get("admin_username", "")
        if username != ADMIN_USERNAME:
            staff = query("SELECT active FROM admin_users WHERE username = ?", (username,), one=True)
            if not staff or not staff["active"]:
                session.clear()
                flash("Your account has been deactivated.", "danger")
                return redirect(url_for("admin_login"))
        return view(*args, **kwargs)

    return wrapped


PERMISSIONS = [
    ("view_dashboard", "View dashboard"),
    ("games_view", "Games: view"),
    ("games_create", "Games: create"),
    ("games_update", "Games: edit/update"),
    ("games_delete", "Games: delete"),
    ("media_view", "Media: view"),
    ("media_create", "Media: create/upload"),
    ("media_update", "Media: edit/update"),
    ("media_delete", "Media: delete"),
    ("leadership_view", "Leadership: view"),
    ("leadership_create", "Leadership: create"),
    ("leadership_update", "Leadership: edit/update"),
    ("leadership_delete", "Leadership: delete"),
    ("sponsors_view", "Sponsors: view"),
    ("sponsors_create", "Sponsors: create"),
    ("sponsors_update", "Sponsors: edit/update"),
    ("sponsors_delete", "Sponsors: delete"),
    ("prize_pool_view", "Prize Pool: view"),
    ("prize_pool_update", "Prize Pool: update settings/banner"),
    ("prize_items_create", "Prize Items: create"),
    ("prize_items_update", "Prize Items: edit/update"),
    ("prize_items_delete", "Prize Items: delete"),
    ("team_view", "Our Team: view"),
    ("team_create", "Our Team: create"),
    ("team_update", "Our Team: edit/update/reorder"),
    ("team_delete", "Our Team: delete"),
    ("contacts_view", "Messages: view"),
    ("contacts_delete", "Messages: delete"),
    ("settings_view", "Settings: view"),
    ("settings_update", "Settings: update"),
    ("users_view", "Staff Users: view"),
    ("users_create", "Staff Users: create"),
    ("users_update", "Staff Users: edit/update/reset passwords"),
    ("users_delete", "Staff Users: delete"),
    ("roles_view", "Roles: view"),
    ("roles_create", "Roles: create"),
    ("roles_update", "Roles: edit/update permissions"),
    ("roles_delete", "Roles: delete"),
    ("registrations_delete", "Registrations: delete"),
    ("manage_games", "Manage game cards (add, edit, delete)"),
    ("manage_media", "Manage covers and gallery"),
    ("manage_leadership", "Manage leadership quotes"),
    ("manage_sponsors", "Manage sponsors"),
    ("manage_prize_pool", "Manage prize pool and rewards"),
    ("manage_team", "Manage Our Team sections and members"),
    ("manage_contacts", "View and manage messages"),
    ("manage_settings", "Manage website and payment settings"),
    ("view_registrations", "View registrations"),
    ("edit_registrations", "Edit registrations"),
    ("view_payment_details", "View payment details"),
    ("confirm_payments", "Confirm payments"),
    ("process_refunds", "Process refunds"),
    ("download_pdf_tickets", "Download PDF tickets"),
    ("export_excel_reports", "Export Excel reports"),
    ("scan_qr_codes", "Scan QR codes"),
    ("manually_verify_tickets", "Manually verify tickets"),
    ("view_scanned_record", "View scanned ticket record details"),
    ("approve_entry", "Approve entry"),
    ("reject_entry", "Reject entry"),
    ("view_checkin_history", "View check-in history"),
    ("override_duplicate_entry", "Override duplicate entry"),
    ("manage_users", "Manage users"),
    ("manage_roles_permissions", "Manage roles and permissions"),
    ("view_audit_logs", "View audit logs"),
]
PERMISSION_KEYS = {key for key, _label in PERMISSIONS}
PERMISSION_LABELS = dict(PERMISSIONS)
PERMISSION_ALIASES = {
    "manage_games": {"games_view", "games_create", "games_update", "games_delete"},
    "manage_media": {"media_view", "media_create", "media_update", "media_delete"},
    "manage_leadership": {"leadership_view", "leadership_create", "leadership_update", "leadership_delete"},
    "manage_sponsors": {"sponsors_view", "sponsors_create", "sponsors_update", "sponsors_delete"},
    "manage_prize_pool": {"prize_pool_view", "prize_pool_update", "prize_items_create", "prize_items_update", "prize_items_delete"},
    "manage_team": {"team_view", "team_create", "team_update", "team_delete"},
    "manage_contacts": {"contacts_view", "contacts_delete"},
    "manage_settings": {"settings_view", "settings_update"},
    "manage_users": {"users_view", "users_create", "users_update", "users_delete"},
    "manage_roles_permissions": {"roles_view", "roles_create", "roles_update", "roles_delete"},
    "edit_registrations": {"registrations_delete"},
}
PERMISSION_SECTIONS = [
    ("Dashboard", ["view_dashboard"]),
    ("Game Cards", ["games_view", "games_create", "games_update", "games_delete", "manage_games"]),
    ("Covers & Gallery", ["media_view", "media_create", "media_update", "media_delete", "manage_media"]),
    ("Leadership", ["leadership_view", "leadership_create", "leadership_update", "leadership_delete", "manage_leadership"]),
    ("Sponsors", ["sponsors_view", "sponsors_create", "sponsors_update", "sponsors_delete", "manage_sponsors"]),
    ("Prize Pool", ["prize_pool_view", "prize_pool_update", "prize_items_create", "prize_items_update", "prize_items_delete", "manage_prize_pool"]),
    ("Our Team", ["team_view", "team_create", "team_update", "team_delete", "manage_team"]),
    ("Messages & Settings", ["contacts_view", "contacts_delete", "settings_view", "settings_update", "manage_contacts", "manage_settings"]),
    ("Registrations & Payments", ["view_registrations", "edit_registrations", "registrations_delete", "view_payment_details", "confirm_payments", "process_refunds", "download_pdf_tickets", "export_excel_reports"]),
    ("QR Check-In", ["scan_qr_codes", "manually_verify_tickets", "view_scanned_record", "approve_entry", "reject_entry", "view_checkin_history", "override_duplicate_entry"]),
    ("Staff Users", ["users_view", "users_create", "users_update", "users_delete", "manage_users"]),
    ("Roles & Audit", ["roles_view", "roles_create", "roles_update", "roles_delete", "manage_roles_permissions", "view_audit_logs"]),
]


def current_permissions():
    """The configured root admin always retains full access."""
    if session.get("admin_username") == ADMIN_USERNAME:
        return PERMISSION_KEYS
    perms = set()
    role_id = session.get("role_id")
    if role_id:
        perms = {row["permission_key"] for row in query("SELECT permission_key FROM role_permissions WHERE role_id = ?", (role_id,))}
    user_id = session.get("admin_user_id")
    if user_id:
        user_perms = {row["permission_key"] for row in query("SELECT permission_key FROM user_permissions WHERE user_id = ?", (user_id,))}
        perms |= user_perms
    expanded = set(perms)
    for key in tuple(perms):
        expanded |= PERMISSION_ALIASES.get(key, set())
    return expanded


def has_permission(permission):
    return permission in current_permissions()


def forbidden_response():
    if request.is_json or request.path.endswith("/verify") or request.path.endswith("/decision"):
        return jsonify({"ok": False, "status": "forbidden", "message": "403 Forbidden: you do not have permission for this action."}), 403
    abort(403)


def permission_required(*permissions, any_of=False):
    def decorator(view):
        @wraps(view)
        def wrapped(*args, **kwargs):
            if not session.get("admin"):
                return admin_required(view)(*args, **kwargs)
            granted = current_permissions()
            allowed = bool(set(permissions) & granted) if any_of else set(permissions).issubset(granted)
            if not allowed:
                return forbidden_response()
            return view(*args, **kwargs)
        return wrapped
    return decorator


def root_admin_required(view):
    @wraps(view)
    def wrapped(*args, **kwargs):
        if not session.get("admin"):
            return admin_required(view)(*args, **kwargs)
        if session.get("admin_username") != ADMIN_USERNAME:
            return forbidden_response()
        return view(*args, **kwargs)
    return wrapped


@app.context_processor
def inject_permissions():
    try:
        granted = current_permissions() if session.get("admin") else set()
    except RuntimeError:
        granted = set()
    return {
        "can": lambda key: key in granted,
        "can_any": lambda *keys: bool(set(keys) & granted),
        "admin_permissions": granted,
    }


def log_role_audit(action, target_username="", details=""):
    execute(
        "INSERT INTO role_audit_log (actor_username, action, target_username, details, created_at) VALUES (?, ?, ?, ?, ?)",
        (session.get("admin_username", "system"), action, target_username, details, datetime.now().isoformat(timespec="seconds")),
    )


# ────────────────────────────────────────────
# Schema + migrations
# ────────────────────────────────────────────
SETTINGS_EXTRA_COLUMNS = {
    "event_tagline": "Solo and team formats for serious players on one competitive stage.",
    "event_venue": "Main Sports Complex, Campus Ground",
    "event_city": "Karachi, Pakistan",
    "hero_heading": "Nexora Esports Championship",
    "about_intro": (
        "Nexora Esports Championship is a professionally managed gaming event for students, teams, "
        "and competitive players who want a fair stage to prove their skill."
    ),
    "about_body": (
        "Nexora was built to give campus gamers a serious, fair, and well-organized platform. Every "
        "registration is verified, every bracket is managed transparently, and every match follows clear "
        "tournament standards."
    ),
    "contact_email": "nexora.esport.support@gmail.com",
    "contact_phone": "+92 331 0314962",
    "contact_address": "Sports Office, Main Campus",
    "whatsapp_link": "https://wa.me/923310314962",
    "facebook_link": "",
    "instagram_link": "",
    "youtube_link": "",
    "discord_link": "",
    "payment_method_label": "G-Pay / JazzCash / Easypaisa",
    "payment_bank_name": "",
    "payment_account_title": "",
    "payment_iban": "",
    "payment_account_number": "",
    "payment_bank_branch": "",
    "payment_qr_image": "",
    "site_logo": "Nexora Logo.png",
    "home_background": "NexoraGame.png",
    "nav_bg_color": "#d4a017",
    "nav_text_color": "#111827",
    "nav_accent_color": "#e8384f",
    "group_ticket_delivery": "leader_only",
    "countdown_mode": "event",
    "prize_pool_title": "Victory Feels Bigger Here",
    "prize_pool_amount": "Prize Pool Announced Soon",
    "prize_pool_note": "Prize pool, winner perks, and event rewards are updated from the admin panel.",
    "prize_first_label": "Winner Trophy",
    "prize_first_note": "Champion-level recognition",
    "prize_second_label": "Official Certificates",
    "prize_second_note": "For finalists and top teams",
    "prize_third_label": "MVP Spotlight",
    "prize_third_note": "Featured moments and shoutouts",
    "prize_banner_image": "",
    "prize_banner_images": "",
    "smtp_host": "mail.nexoraesport.com",
    "smtp_port": "465",
    "smtp_user": "info@nexoraesport.com",
    "smtp_password": "",
    "smtp_from_email": "info@nexoraesport.com",
    "smtp_admin_email": "nexora.esport.support@gmail.com",
    "admin_password_hash": "",
}


GAME_EXTRA_COLUMNS = {
    "reg_from": "",
    "reg_to": "",
    "discount_label": "",
    "individual_old_price": "",
    "group_old_price": "",
    "min_members": "1",
    "max_members": "1",
    "price_for_2": "",
    "price_for_3": "",
    "price_for_4": "",
    "old_price_2": "",
    "old_price_3": "",
    "old_price_4": "",
    "discount_from": "",
    "discount_to": "",
    "discount_pct": "",
    # Soft-delete marker. Games with historical registrations are archived
    # (deleted_at set) instead of physically removed, so registrations,
    # tickets and payment history keep their game title.
    "deleted_at": "",
}

REGISTRATION_EXTRA_COLUMNS = {
    "institution_type": "University",
    "ticket_token": "",
    "ticket_serial": "",
    "verified_at": "",
    "entry_status": "pending",
    "entry_marked_at": "",
    "entry_note": "",
    "amount_paid": "",
    "transaction_reference": "",
    "ticket_issued_at": "",
    "checkin_gate": "",
    "checked_in_by": "",
    "rejection_reason": "",
    "rejected_by": "",
    "rejected_at": "",
    "override_by": "",
    "override_at": "",
    "override_reason": "",
}


def ensure_columns(conn, table, columns):
    existing = {row["name"] for row in conn.execute(f"PRAGMA table_info({table})")}
    for name, default in columns.items():
        if name not in existing:
            conn.execute(f"ALTER TABLE {table} ADD COLUMN {name} TEXT NOT NULL DEFAULT ''")
            conn.execute(f"UPDATE {table} SET {name} = ? WHERE {name} = ''", (default,))


def init_db():
    with db() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS settings (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                event_title TEXT NOT NULL,
                event_date TEXT NOT NULL,
                early_deadline TEXT NOT NULL,
                early_discount TEXT NOT NULL,
                bank_account TEXT NOT NULL,
                payment_note TEXT NOT NULL,
                registration_open INTEGER NOT NULL DEFAULT 1
            );

            CREATE TABLE IF NOT EXISTS games (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                platform TEXT NOT NULL,
                individual_price TEXT NOT NULL,
                group_price TEXT NOT NULL,
                image TEXT,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS media (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                kind TEXT NOT NULL,
                title TEXT NOT NULL,
                caption TEXT,
                image TEXT NOT NULL,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS registrations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                full_name TEXT NOT NULL,
                email TEXT NOT NULL,
                phone TEXT NOT NULL,
                cnic TEXT NOT NULL,
                university TEXT NOT NULL,
                roll_no TEXT,
                entry_type TEXT NOT NULL,
                game_id INTEGER NOT NULL,
                team_name TEXT,
                leader_name TEXT,
                members TEXT,
                payment_method TEXT NOT NULL,
                payment_status TEXT NOT NULL,
                proof_image TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (game_id) REFERENCES games(id)
            );

            CREATE TABLE IF NOT EXISTS contacts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT NOT NULL,
                phone TEXT NOT NULL,
                message TEXT NOT NULL,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS leaders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                role TEXT NOT NULL,
                quote TEXT NOT NULL,
                image TEXT,
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS sponsors (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                tier TEXT NOT NULL DEFAULT 'Partner',
                commitment TEXT,
                website TEXT,
                logo TEXT,
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS prize_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                amount TEXT NOT NULL DEFAULT '',
                note TEXT NOT NULL DEFAULT '',
                image TEXT NOT NULL DEFAULT '',
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS member_tickets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                registration_id INTEGER NOT NULL,
                member_number INTEGER NOT NULL,
                member_name TEXT NOT NULL DEFAULT '',
                member_email TEXT NOT NULL DEFAULT '',
                ticket_serial TEXT NOT NULL,
                created_at TEXT NOT NULL,
                UNIQUE(registration_id, member_number),
                UNIQUE(ticket_serial),
                FOREIGN KEY (registration_id) REFERENCES registrations(id)
            );

            CREATE TABLE IF NOT EXISTS ticket_audit_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                registration_id INTEGER NOT NULL,
                ticket_serial TEXT NOT NULL,
                action TEXT NOT NULL,
                previous_status TEXT NOT NULL DEFAULT '',
                new_status TEXT NOT NULL DEFAULT '',
                reason TEXT NOT NULL DEFAULT '',
                gate_location TEXT NOT NULL DEFAULT '',
                staff_username TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL,
                FOREIGN KEY (registration_id) REFERENCES registrations(id)
            );

            CREATE TABLE IF NOT EXISTS roles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                description TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS role_permissions (
                role_id INTEGER NOT NULL,
                permission_key TEXT NOT NULL,
                PRIMARY KEY (role_id, permission_key),
                FOREIGN KEY (role_id) REFERENCES roles(id)
            );

            CREATE TABLE IF NOT EXISTS admin_users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                full_name TEXT NOT NULL DEFAULT '',
                role_id INTEGER,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL,
                FOREIGN KEY (role_id) REFERENCES roles(id)
            );

            CREATE TABLE IF NOT EXISTS role_audit_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                actor_username TEXT NOT NULL,
                action TEXT NOT NULL,
                target_username TEXT NOT NULL DEFAULT '',
                details TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS user_permissions (
                user_id INTEGER NOT NULL,
                permission_key TEXT NOT NULL,
                PRIMARY KEY (user_id, permission_key),
                FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS team_sections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                description TEXT NOT NULL DEFAULT '',
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS team_members (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                section_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                image TEXT NOT NULL DEFAULT '',
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL,
                FOREIGN KEY (section_id) REFERENCES team_sections(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS team_rows (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                section_id INTEGER NOT NULL,
                row_index INTEGER NOT NULL DEFAULT 0,
                direction TEXT NOT NULL DEFAULT 'ltr',
                speed INTEGER NOT NULL DEFAULT 30,
                enabled INTEGER NOT NULL DEFAULT 1,
                FOREIGN KEY (section_id) REFERENCES team_sections(id) ON DELETE CASCADE
            );
            """
        )

        ensure_columns(conn, "settings", SETTINGS_EXTRA_COLUMNS)
        ensure_columns(conn, "games", GAME_EXTRA_COLUMNS)
        ensure_columns(conn, "registrations", REGISTRATION_EXTRA_COLUMNS)
        ensure_columns(conn, "member_tickets", MEMBER_TICKET_EXTRA_COLUMNS)
        ensure_ticket_tokens(conn)
        ensure_ticket_serials(conn)
        ensure_group_member_tickets(conn)
        ensure_member_ticket_tokens(conn)
        ensure_indexes(conn)

        exists = conn.execute("SELECT id FROM settings WHERE id = 1").fetchone()
        fresh_db = not exists
        if not exists:
            conn.execute(
                """
                INSERT INTO settings
                (id, event_title, event_date, early_deadline, early_discount, bank_account,
                 payment_note, registration_open)
                VALUES (1, ?, ?, ?, ?, ?, ?, 1)
                """,
                (
                    "Nexora Esports Championship",
                    "2026-10-15T18:00",
                    "2026-09-30T23:59",
                    "Early Bird: 20% discount before deadline",
                    "Bank / JazzCash / Easypaisa account: 03XX-XXXXXXX",
                    "Upload a payment proof screenshot. The admin will verify it and mark the status as Paid.",
                ),
            )
            for column, value in SETTINGS_EXTRA_COLUMNS.items():
                conn.execute(f"UPDATE settings SET {column} = ? WHERE id = 1", (value,))

        now = datetime.now().isoformat(timespec="seconds")

        if not conn.execute("SELECT id FROM roles WHERE name = 'Check-in Staff'").fetchone():
            cur = conn.execute("INSERT INTO roles (name, description, created_at) VALUES (?, ?, ?)", ("Check-in Staff", "Scans and processes event entry tickets.", now))
            conn.executemany("INSERT INTO role_permissions (role_id, permission_key) VALUES (?, ?)", [(cur.lastrowid, key) for key in ("view_dashboard", "view_registrations", "scan_qr_codes", "manually_verify_tickets", "view_scanned_record", "approve_entry", "reject_entry", "view_checkin_history")])
        checkin_role = conn.execute("SELECT id FROM roles WHERE name = 'Check-in Staff'").fetchone()
        if checkin_role:
            conn.execute("INSERT OR IGNORE INTO role_permissions (role_id, permission_key) VALUES (?, ?)", (checkin_role["id"], "view_dashboard"))
            conn.execute("INSERT OR IGNORE INTO role_permissions (role_id, permission_key) VALUES (?, ?)", (checkin_role["id"], "view_scanned_record"))

        # Demo content is seeded ONLY once, when the database has never been
        # initialized (no settings row). Deleted games/leaders stay deleted.
        if fresh_db:
            if conn.execute("SELECT COUNT(*) AS c FROM games").fetchone()["c"] == 0:
                conn.executemany(
                    """
                    INSERT INTO games (title, platform, individual_price, group_price, image, active, created_at)
                    VALUES (?, ?, ?, ?, ?, 1, ?)
                    """,
                    [
                        ("EA FC / FIFA", "PS5", "Rs. 800", "Rs. 2,800", "", now),
                        ("PUBG Mobile", "Mobile", "Rs. 600", "Rs. 2,000", "", now),
                        ("Tekken 8", "PC / PS5", "Rs. 700", "Rs. 2,400", "", now),
                    ],
                )

            if conn.execute("SELECT COUNT(*) AS c FROM leaders").fetchone()["c"] == 0:
                conn.executemany(
                    """
                    INSERT INTO leaders (name, role, quote, image, sort_order, active, created_at)
                    VALUES (?, ?, ?, ?, ?, 1, ?)
                    """,
                    [
                        (
                            "Full Name Here",
                            "Sports CEO",
                            "Esports is more than a hobby. It is discipline, teamwork, and strategy on a serious stage.",
                            "",
                            1,
                            now,
                        ),
                        (
                            "Full Name Here",
                            "Sports Secretary",
                            "Every registration, match, and result should stay transparent. Fair play defines this championship.",
                            "",
                            2,
                            now,
                        ),
                        (
                            "Full Name Here",
                            "Sports Director",
                            "We want players who can think clearly under pressure. Every Nexora bracket tests that mindset.",
                            "",
                            3,
                            now,
                        ),
                    ],
                )

        conn.commit()


# ────────────────────────────────────────────
# Context
# ────────────────────────────────────────────
def parse_dt(value):
    if not value:
        return None
    text = str(value).strip()
    for fmt in ("%Y-%m-%dT%H:%M", "%Y-%m-%dT%H:%M:%S", "%Y-%m-%d %H:%M:%S", "%Y-%m-%d"):
        try:
            return datetime.strptime(text, fmt)
        except ValueError:
            continue
    return None


def game_status(game, registration_open=None):
    """Return (is_open, label) for a game's registration window."""
    if registration_open is None:
        row = query("SELECT registration_open FROM settings WHERE id = 1", one=True)
        registration_open = row["registration_open"] if row else 1

    if not registration_open:
        return False, "Closed"

    now = datetime.now()
    start = parse_dt(game["reg_from"] if "reg_from" in game.keys() else "")
    end = parse_dt(game["reg_to"] if "reg_to" in game.keys() else "")

    if start and now < start:
        return False, "Opens Soon"
    if end and now > end:
        return False, "Closed"
    return True, "Open"


def amount_value(value):
    """Extract a numeric value from a price string like 'Rs. 960' or 'Rs. 1,200'."""
    m = re.search(r"(\d[\d,]*(?:\.\d+)?)", str(value or ""))
    try:
        return float(m.group(1).replace(",", "")) if m else 0.0
    except (ValueError, AttributeError):
        return 0.0


def discount_active(game):
    """A discount is active when it has a valid percentage and its optional window matches now."""
    if not game:
        return False
    pct = discount_pct_value(game)
    if pct <= 0 or pct > 100:
        return False

    now = datetime.now()
    start = parse_dt(game["discount_from"] if "discount_from" in game.keys() else "")
    end = parse_dt(game["discount_to"] if "discount_to" in game.keys() else "")
    if start and now < start:
        return False
    if end and now > end:
        return False
    return True


def discount_pct_value(game):
    """Return the configured discount percent, safely ignoring game names like 'Tekken 8'."""
    if not game:
        return 0
    
    if "discount_pct" in game.keys():
        val = str(game["discount_pct"] or "").strip()
        if val:
            found = re.search(r"^\d+(?:\.\d+)?", val)
            if found:
                try:
                    return float(found.group(0))
                except ValueError:
                    pass
                
    if "discount_label" in game.keys():
        lbl = str(game["discount_label"] or "").strip()
        if lbl:
            # Look for numbers directly followed by % or "off" to avoid "Tekken 8" false positives
            found = re.search(r"(\d+(?:\.\d+)?)\s*(?:%|% off|percent|off|discount)\b", lbl, re.IGNORECASE)
            if found:
                try:
                    return float(found.group(1))
                except ValueError:
                    pass
            # Fallback for purely numeric labels "20" (legacy data)
            elif re.fullmatch(r"\d+(?:\.\d+)?", lbl):
                try:
                    return float(lbl)
                except ValueError:
                    pass
                
    return 0


def discount_label_text(game):
    pct = discount_pct_value(game)
    label = str(game["discount_label"] if game and "discount_label" in game.keys() else "").strip()
    if pct and (not label or re.fullmatch(r"\d+(?:\.\d+)?%?", label)):
        pct_text = f"{pct:g}"
        return f"{pct_text}% Discount"
    if label and "%" not in label and pct:
        return f"{label} ({pct:g}% Discount)"
    return label


def discounted_price(price_str, pct):
    return disc_price_filter(price_str, pct)


def registration_effective_price(row, entry_type, member_count):
    if entry_type == "group":
        size_key = f"price_for_{member_count}"
        original = row[size_key] if size_key in row.keys() and row[size_key] else row["group_price"]
    else:
        original = row["individual_price"]
    pct = discount_pct_value(row) if discount_active(row) else 0
    return discounted_price(original, pct)


def game_price_for_size(game, size):
    if not game:
        return ""
    if int(size or 1) <= 1:
        return game["individual_price"] if "individual_price" in game.keys() else ""
    key = f"price_for_{int(size)}"
    if key in game.keys() and game[key]:
        return game[key]
    return game["group_price"] if "group_price" in game.keys() else ""


@app.context_processor
def inject_common():
    settings = cached_query("settings", "SELECT * FROM settings WHERE id = 1", one=True, ttl=PUBLIC_CACHE_SECONDS)
    prize_banner_images = []
    if settings:
        prize_banner_images = split_upload_list(settings["prize_banner_images"] if "prize_banner_images" in settings.keys() else "")
        if not prize_banner_images and settings["prize_banner_image"]:
            prize_banner_images = [settings["prize_banner_image"]]
    cache_bust = APP_START_TS
    for asset in (
        BASE_DIR / "static" / "css" / "style.css",
        BASE_DIR / "static" / "css" / "overrides.css",
        BASE_DIR / "static" / "js" / "main.js",
    ):
        try:
            cache_bust = max(cache_bust, int(asset.stat().st_mtime))
        except OSError:
            pass
    return {
        "settings": settings,
        "current_year": datetime.now().year,
        "game_status": game_status,
        "discount_active": discount_active,
        "discount_pct_value": discount_pct_value,
        "discount_label_text": discount_label_text,
        "prize_banner_images": prize_banner_images,
        "prize_items": cached_query("active_prize_items", "SELECT * FROM prize_items WHERE active = 1 ORDER BY sort_order, id", ttl=PUBLIC_CACHE_SECONDS),
        "cache_bust": cache_bust,
    }


@app.template_global("now")
def now_global():
    return datetime.now()


@app.template_filter("pretty_dt")
def pretty_dt(value):
    """Turn 2026-10-15T18:00 into 15 Oct 2026, 06:00 PM."""
    if not value:
        return ""
    text = str(value).strip()
    for fmt in ("%Y-%m-%dT%H:%M", "%Y-%m-%dT%H:%M:%S", "%Y-%m-%d %H:%M:%S", "%Y-%m-%d"):
        try:
            return datetime.strptime(text, fmt).strftime("%d %b %Y, %I:%M %p")
        except ValueError:
            continue
    return text.replace("T", " ")


@app.template_filter("disc_price")
def disc_price_filter(price_str, pct):
    """Apply a discount percentage to a price string.

    Returns {"original", "discounted", "active"} where BOTH values are
    canonical single prices ("Rs. 1,200" / "Rs. 960"). Dirty inputs that
    accidentally contain several numbers ("Rs. 1,200 960") are reduced to
    their first number so concatenated prices can never reach the UI again.
    """
    import re as _re

    def _canonical(text):
        """Normalize any price-ish text to 'Prefix Amount' using its first number."""
        s = str(text or "").strip()
        if not s:
            return ""
        m = _re.search(r"\d[\d,]*(?:\.\d+)?", s)
        if not m:
            return s
        # Keep abbreviation dots ("Rs.") — strip only separator junk.
        prefix = _re.sub(r"[\s;:/\\-]+$", "", s[: m.start()]).strip()
        prefix = _re.sub(r"\s+", " ", prefix)
        amount = f"{float(m.group(0).replace(',', '')):,.0f}"
        return f"{prefix} {amount}" if prefix else amount

    original_text = _canonical(price_str)
    try:
        pct = float(pct or 0)
    except (ValueError, TypeError):
        pct = 0
    m = _re.search(r"\d[\d,]*(?:\.\d+)?", str(price_str or ""))
    if not m or pct <= 0 or pct > 100:
        return {"original": original_text, "discounted": original_text, "active": False}
    val = float(m.group(0).replace(",", ""))
    discounted = val * (1 - pct / 100)
    if discounted < 0:
        return {"original": original_text, "discounted": original_text, "active": False}
    # Reuse the same currency prefix as the original ("Rs." etc.), else none.
    # NOTE: keep a trailing dot on abbreviations like "Rs." — only strip
    # genuine separator junk (spaces/semicolons/slashes).
    pm = _re.match(r"^\D*?(?=\d)", str(price_str).strip())
    prefix = _re.sub(r"[\s;:/\\-]+$", "", pm.group(0)) if pm else ""
    prefix = _re.sub(r"\s+", " ", prefix).strip()
    formatted = f"{prefix} {discounted:,.0f}".strip() if prefix else f"{discounted:,.0f}"
    return {"original": original_text, "discounted": formatted, "active": True}


@app.template_global("price_view")
def price_view(game, size=1):
    """Single source of truth for public price display.

    Returns None when the game has no configured price for this size,
    otherwise {"current", "original", "strike"}:
      - discount ACTIVE  -> current = discounted, original = regular, strike = True
      - discount EXPIRED -> current = regular,    original = "",       strike = False
    Uses the exact same helpers as registration pricing, so the UI price
    always matches what the player is actually charged.
    """
    base = game_price_for_size(game, size)
    if not base:
        return None
    active = bool(game and discount_active(game))
    pct = discount_pct_value(game) if active else 0
    result = disc_price_filter(base, pct)
    if result["active"]:
        return {"current": result["discounted"], "original": result["original"], "strike": True}
    return {"current": result["original"], "original": "", "strike": False}


@app.template_global("price_map")
def price_map(game):
    """Backend-precomputed prices for every supported team size.

    Returns {size_string: {"current", "original", "strike"}} for sizes that
    have a configured price. Templates embed this as JSON on <option> tags so
    the frontend never recalculates discounts itself.
    """
    out = {}
    for size in (1, 2, 3, 4):
        view = price_view(game, size)
        if view:
            out[str(size)] = view
    return out


@app.errorhandler(400)
def bad_request(_error):
    if request.path.startswith("/admin") or request.is_json:
        return jsonify({"ok": False, "status": "bad_request", "message": "Bad request."}), 400
    flash("Bad request.", "danger")
    return redirect(request.referrer or url_for("index")), 302


@app.errorhandler(403)
def forbidden(_error):
    wants_json = request.is_json or request.headers.get("X-Requested-With") == "XMLHttpRequest"
    if request.path.startswith("/admin/api") or (request.path.startswith("/admin") and wants_json):
        return jsonify({"ok": False, "status": "forbidden", "message": "403 Forbidden: you do not have permission."}), 403
    if request.path.startswith("/admin") or request.accept_mimetypes.best == "application/json":
        return render_template("403.html"), 403
    flash("You do not have permission to access this page.", "danger")
    return redirect(request.referrer or url_for("index")), 302


@app.errorhandler(404)
def not_found(_error):
    if request.path.startswith("/admin") or request.is_json:
        return jsonify({"ok": False, "status": "not_found", "message": "Resource not found."}), 404
    return render_template("404.html"), 404


@app.errorhandler(405)
def method_not_allowed(_error):
    if request.path.startswith("/admin") or request.is_json:
        return jsonify({"ok": False, "status": "method_not_allowed", "message": "Method not allowed."}), 405
    flash("This action is not allowed.", "danger")
    return redirect(request.referrer or url_for("index")), 302


@app.errorhandler(429)
def rate_limited(_error):
    if request.path.startswith("/admin") or request.is_json:
        return jsonify({"ok": False, "status": "rate_limited", "message": "Too many requests. Please try again later."}), 429
    flash("Too many requests. Please try again later.", "danger")
    return redirect(request.referrer or url_for("index")), 302


@app.errorhandler(500)
def server_error(_error):
    import traceback
    tb = traceback.format_exc()
    logger.exception("Internal server error on %s", request.path)
    if request.path.startswith("/admin") or request.is_json:
        return jsonify({"ok": False, "status": "server_error", "message": f"An internal error occurred.\n\nTRACEBACK:\n{tb}"}), 500
    return render_template("500.html"), 500


@app.errorhandler(413)
def too_large(_error):
    flash("File too large. Please upload an image under 8 MB.", "danger")
    return redirect(request.referrer or url_for("index")), 302


# ────────────────────────────────────────────
# 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")
        contact_id = execute(
            "INSERT INTO contacts (name, email, phone, message, created_at) VALUES (?, ?, ?, ?, ?)",
            (name, email, phone, message, datetime.now().isoformat(timespec="seconds")),
        )
        send_contact_email(contact_id)
        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("/register", methods=["GET", "POST"])
def register():
    settings = cached_query("settings", "SELECT * FROM settings WHERE id = 1", one=True, ttl=PUBLIC_CACHE_SECONDS)
    games = cached_query("register_games", "SELECT * FROM games WHERE active = 1 AND deleted_at = '' ORDER BY id DESC", ttl=PUBLIC_CACHE_SECONDS)

    if not settings["registration_open"]:
        return render_template("register.html", games=games, closed=True, selected_game=None)

    open_games = [g for g in games if game_status(g, settings["registration_open"])[0]]

    if request.method == "POST":
        t0 = time.perf_counter()
        stage_t = t0

        def log_stage(name):
            nonlocal stage_t
            now = time.perf_counter()
            logger.info("Registration timing stage=%s elapsed_ms=%.1f total_ms=%.1f", name, (now - stage_t) * 1000, (now - t0) * 1000)
            stage_t = now

        dedupe_key = request.form.get("submission_key", "").strip()
        if dedupe_key and session.get("last_registration_submission_key") == dedupe_key:
            flash("Registration already submitted. Duplicate entry was prevented.", "warning")
            return redirect(url_for("register"))

        chosen = next((g for g in games if str(g["id"]) == str(request.form.get("game_id"))), None)
        if not chosen or not game_status(chosen, settings["registration_open"])[0]:
            flash("Registration for that game is currently closed.", "danger")
            return redirect(url_for("register"))
        selected_team_size = request.form.get("team_size", type=int) or 1
        min_m = int(chosen["min_members"] or 1) if "min_members" in chosen.keys() else 1
        max_m = int(chosen["max_members"] or 1) if "max_members" in chosen.keys() else 1
        if selected_team_size < min_m:
            flash(f"Minimum {min_m} player{'s' if min_m != 1 else ''} required for this game.", "danger")
            return redirect(url_for("register", game=chosen["id"]))
        if selected_team_size > max_m:
            flash(f"Maximum {max_m} player{'s' if max_m != 1 else ''} allowed for this game.", "danger")
            return redirect(url_for("register", game=chosen["id"]))
        if not game_price_for_size(chosen, selected_team_size):
            flash(f"Price is not configured for {selected_team_size} player(s).", "danger")
            return redirect(url_for("register", game=chosen["id"]))
        entry_type = "group" if selected_team_size > 1 else "individual"
        if entry_type == "individual" and not chosen["individual_price"]:
            flash("Individual entry is not available for this game.", "danger")
            return redirect(url_for("register", game=chosen["id"]))

        team_name = request.form.get("team_name", "").strip()
        if not team_name:
            flash("Team Name / Nick Name is required.", "danger")
            return redirect(url_for("register", game=chosen["id"]))

        # Proof is only required when discount < 100%
        is_full_discount = discount_pct_value(chosen) >= 100
        proof = ""
        if not is_full_discount:
            proof, proof_error = process_image_upload(
                request.files.get("proof_image"), category="registrations", game_title=chosen["title"],
                override_filename=request.form["email"].strip(),
            )
            log_stage("proof_image")
            if proof_error:
                flash(proof_error, "danger")
                return redirect(url_for("register", game=chosen["id"]))
        else:
            log_stage("proof_image")
        members = []
        member_names = request.form.getlist("member_name[]")
        member_emails = request.form.getlist("member_email[]")
        member_phones = request.form.getlist("member_phone[]")
        if entry_type == "group":
            required_members = max(0, selected_team_size - 1)
            for index in range(required_members):
                name = member_names[index].strip() if index < len(member_names) else ""
                email = member_emails[index].strip() if index < len(member_emails) else ""
                phone = member_phones[index].strip() if index < len(member_phones) else ""
                # Email is optional per member: a missing address only removes
                # that member from the confirmation-email CC list, it must not
                # block the registration itself.
                if not name or not phone:
                    flash(f"Please complete details for team member {index + 1}.", "danger")
                    return redirect(url_for("register", game=chosen["id"]))
                members.append(f"Member {index + 1}: {name} | {email} | {phone}")

        members_text = "\n".join(members)

        # ── Transactional validation + insert ──
        try:
            with db() as conn:
                errors = validate_registration_constraints(
                    conn,
                    email=request.form["email"],
                    cnic=request.form["cnic"],
                    game_id=chosen["id"],
                    team_name=team_name,
                    entry_type=entry_type,
                    members_text=members_text,
                )
                if errors:
                    if dedupe_key:
                        session["last_registration_submission_key"] = dedupe_key
                    for err in errors:
                        flash(err, "danger")
                    return redirect(url_for("register", game=chosen["id"]))

                ticket_token = make_ticket_token()
                now_ts = datetime.now().isoformat(timespec="seconds")
                cur = conn.execute(
                    """
                    INSERT INTO registrations
                    (full_name, email, phone, cnic, institution_type, university, roll_no, entry_type,
                     game_id, team_name, leader_name, members, payment_method, payment_status,
                     proof_image, ticket_token, ticket_serial, created_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (
                        request.form["full_name"].strip(),
                        request.form["email"].strip(),
                        request.form["phone"].strip(),
                        request.form["cnic"].strip(),
                        request.form.get("institution_type", "University").strip() or "University",
                        request.form["university"].strip(),
                        request.form.get("roll_no", "").strip(),
                        entry_type,
                        request.form["game_id"],
                        team_name,
                        request.form.get("leader_name", "").strip(),
                        members_text,
                        settings["payment_method_label"] or "G-Pay",
                        "under_verification",
                        proof,
                        ticket_token,
                        "",
                        now_ts,
                    ),
                )
                registration_id = cur.lastrowid
                conn.execute(
                    "UPDATE registrations SET ticket_serial = ? WHERE id = ?",
                    (make_ticket_serial(registration_id, chosen["title"], settings["event_date"]), registration_id),
                )
                ensure_group_member_tickets(conn)
                conn.commit()
                log_stage("database")

        except sqlite3.IntegrityError:
            if dedupe_key:
                session["last_registration_submission_key"] = dedupe_key
            flash("This registration conflicts with an existing entry. Please check your Team Name, Nickname, CNIC, and try again.", "danger")
            return redirect(url_for("register", game=chosen["id"]))
        except Exception:
            if dedupe_key:
                session["last_registration_submission_key"] = dedupe_key
            flash("An unexpected error occurred. Please try again.", "danger")
            return redirect(url_for("register", game=chosen["id"]))

        if dedupe_key:
            session["last_registration_submission_key"] = dedupe_key
        saved_serial = query("SELECT ticket_serial FROM registrations WHERE id = ?", (registration_id,), one=True)
        serial_text = saved_serial["ticket_serial"] if saved_serial and saved_serial["ticket_serial"] else f"NX-{registration_id:04d}"

        if not queue_background_email("registration_confirmation", send_registration_emails, registration_id):
            logger.error("Registration confirmation email was not queued for registration #%s", registration_id)
        log_stage("response_ready")
        session["registration_success"] = {
            "id": registration_id,
            "ref": serial_text,
            "email_sent": None,
            "email_recipients": [],
            "email_configured": bool(email_config().get("enabled")),
        }
        return redirect(url_for("registration_success"))

    sel = request.args.get("game", type=int)
    selected_game = next((g for g in games if g["id"] == sel), None)
    if selected_game and not game_status(selected_game, settings["registration_open"])[0]:
        flash("Registration for that game is closed. Please choose another title.", "warning")
        selected_game = None
    return render_template(
        "register.html",
        games=games,
        open_games=open_games,
        closed=False,
        selected_game=selected_game,
    )


@app.route("/register/success")
def registration_success():
    """Post-submit confirmation page. The registration id travels through the
    server session only, so a visitor can only ever see their own submission."""
    payload = session.pop("registration_success", None)
    if not payload:
        return redirect(url_for("register"))
    reg_id = payload.get("id")
    details = registration_details(reg_id) if reg_id else None
    if not details:
        return redirect(url_for("register"))
    return render_template(
        "registration_success.html",
        details=details,
        email_sent=payload.get("email_sent"),
        email_recipients=payload.get("email_recipients", []),
        email_configured=payload.get("email_configured", True),
    )


@app.route("/api/validate-registration", methods=["POST"])
def api_validate_registration():
    """AJAX endpoint: check constraints before form submit. Returns JSON."""
    data = request.get_json(silent=True) or {}
    email = data.get("email", "")
    cnic = data.get("cnic", "")
    game_id = data.get("game_id")
    team_name = data.get("team_name", "")
    entry_type = data.get("entry_type", "individual")
    members_text = data.get("members_text", "")

    if not game_id:
        return jsonify({"ok": True, "errors": []})

    with db() as conn:
        errors = validate_registration_constraints(
            conn,
            email=email,
            cnic=cnic,
            game_id=game_id,
            team_name=team_name,
            entry_type=entry_type,
            members_text=members_text,
        )
    return jsonify({"ok": not errors, "errors": errors})


# ────────────────────────────────────────────
# Admin auth
# ────────────────────────────────────────────
@app.route("/admin", methods=["GET", "POST"])
def admin_login():
    if session.get("admin"):
        return redirect(url_for("admin_dashboard"))

    # CSRF check for login POST (exempted from before_request)
    if request.method == "POST" and not validate_csrf_token():
        abort(403)

    if request.method == "POST":
        username = request.form.get("username", "").strip()
        password = request.form.get("password", "")

        # Rate limiting: lock out after 5 failures for 15 minutes
        client_key = f"login:{request.remote_addr}:{username}"
        if _login_limiter.is_locked(client_key):
            flash("Too many failed attempts. Please try again in 15 minutes.", "danger")
            return render_template("admin_login.html"), 429

        authenticated = False
        is_root = False

        # Check root admin: env password
        if username == ADMIN_USERNAME and password == ADMIN_PASSWORD:
            authenticated = True
            is_root = True

        # Check root admin: hashed password in DB
        if not authenticated and username == ADMIN_USERNAME:
            settings_row = query("SELECT admin_password_hash FROM settings WHERE id = 1", one=True)
            if settings_row and settings_row["admin_password_hash"] and check_password_hash(settings_row["admin_password_hash"], password):
                authenticated = True
                is_root = True

        # Check staff users
        if not authenticated:
            staff = query("SELECT * FROM admin_users WHERE username = ? AND active = 1", (username,), one=True)
            if staff and check_password_hash(staff["password_hash"], password):
                authenticated = True

        if not authenticated:
            remaining = _login_limiter.record_failure(client_key)
            if remaining:
                logger.warning("Login lockout for %s (IP %s) — %d seconds", username, request.remote_addr, remaining)
            flash("Invalid username or password.", "danger")
            return render_template("admin_login.html")

        # Success: regenerate session to prevent fixation
        session.clear()
        session["admin"] = True
        session["admin_username"] = username if not is_root else ADMIN_USERNAME
        if not is_root:
            staff = query("SELECT * FROM admin_users WHERE username = ? AND active = 1", (username,), one=True)
            if staff:
                session["role_id"] = staff["role_id"]
                session["admin_user_id"] = staff["id"]
        session["_login_time"] = str(time.time())
        session.permanent = True
        # Generate a fresh CSRF token for the new session
        session["_csrf_token"] = secrets.token_hex(32)

        _login_limiter.clear(client_key)
        logger.info("Admin login: %s from %s", username, request.remote_addr)
        return redirect(url_for("admin_dashboard"))

    return render_template("admin_login.html")


@app.route("/admin/logout", methods=["POST"])
def admin_logout():
    if not validate_csrf_token():
        abort(403)
    username = session.get("admin_username", "unknown")
    session.clear()
    logger.info("Admin logout: %s from %s", username, request.remote_addr)
    flash("Logged out successfully.", "success")
    return redirect(url_for("admin_login"))


@app.route("/admin/users", methods=["GET", "POST"])
@admin_required
@permission_required("users_view")
def admin_users():
    roles = query("SELECT * FROM roles ORDER BY name")
    if request.method == "POST":
        if not has_permission("users_create"):
            return forbidden_response()
        username = sanitize_field(request.form.get("username", ""), 60)
        password = request.form.get("password", "")
        role_id = request.form.get("role_id", type=int)
        full_name = sanitize_field(request.form.get("full_name", ""), 120)
        if not validate_username_format(username):
            flash("Username must be 3-60 characters: letters, numbers, dots, hyphens, or underscores.", "danger")
        elif len(password) < 8:
            flash("Password must be at least 8 characters.", "danger")
        elif not role_id:
            flash("A role must be selected.", "danger")
        elif query("SELECT id FROM admin_users WHERE username = ?", (username,), one=True):
            flash("That username already exists.", "danger")
        else:
            execute("INSERT INTO admin_users (username, password_hash, full_name, role_id, active, created_at) VALUES (?, ?, ?, ?, 1, ?)", (username, generate_password_hash(password), full_name, role_id, datetime.now().isoformat(timespec="seconds")))
            log_role_audit("user_created", username, f"role_id={role_id}")
            flash("Staff user created.", "success")
        return redirect(url_for("admin_users"))
    users = query("SELECT admin_users.*, roles.name AS role_name FROM admin_users LEFT JOIN roles ON roles.id = admin_users.role_id ORDER BY admin_users.id DESC")
    user_permissions = {}
    for u in users:
        user_permissions[u["id"]] = [row["permission_key"] for row in query("SELECT permission_key FROM user_permissions WHERE user_id = ?", (u["id"],))]
    return render_template("admin_users.html", users=users, roles=roles, user_permissions=user_permissions, permission_sections=PERMISSION_SECTIONS, permission_labels=PERMISSION_LABELS)


@app.route("/admin/users/<int:user_id>/toggle", methods=["POST"])
@admin_required
@permission_required("users_update")
def toggle_admin_user(user_id):
    user = query("SELECT username FROM admin_users WHERE id = ?", (user_id,), one=True)
    if not user:
        abort(404)
    execute("UPDATE admin_users SET active = 1 - active WHERE id = ?", (user_id,))
    log_role_audit("user_status_changed", user["username"])
    return redirect(url_for("admin_users"))


@app.route("/admin/users/<int:user_id>/edit", methods=["POST"])
@admin_required
@permission_required("users_update")
def edit_admin_user(user_id):
    user = query("SELECT username FROM admin_users WHERE id = ?", (user_id,), one=True)
    if not user:
        abort(404)
    username = request.form.get("username", "").strip()
    full_name = request.form.get("full_name", "").strip()
    role_id = request.form.get("role_id", type=int)
    active = 1 if request.form.get("active") else 0
    if not username or not role_id:
        flash("Username and role are required.", "danger")
        return redirect(url_for("admin_users"))
    if query("SELECT id FROM admin_users WHERE username = ? AND id != ?", (username, user_id), one=True):
        flash("That username already exists.", "danger")
        return redirect(url_for("admin_users"))
    execute(
        "UPDATE admin_users SET username = ?, full_name = ?, role_id = ?, active = ? WHERE id = ?",
        (username, full_name, role_id, active, user_id),
    )
    log_role_audit("user_updated", username, f"role_id={role_id}")
    flash("Staff user updated.", "success")
    return redirect(url_for("admin_users"))


@app.route("/admin/users/<int:user_id>/delete", methods=["POST"])
@admin_required
@permission_required("users_delete")
def delete_admin_user(user_id):
    user = query("SELECT username FROM admin_users WHERE id = ?", (user_id,), one=True)
    if not user:
        abort(404)
    if session.get("admin_username") == user["username"]:
        flash("You cannot delete the account you are currently logged in with.", "danger")
        return redirect(url_for("admin_users"))
    execute("DELETE FROM admin_users WHERE id = ?", (user_id,))
    log_role_audit("user_deleted", user["username"])
    flash(f'Staff user "{user["username"]}" deleted.', "success")
    return redirect(url_for("admin_users"))


@app.route("/admin/roles", methods=["GET", "POST"])
@admin_required
@permission_required("roles_view")
def admin_roles():
    all_users = query("SELECT id, username, full_name, role_id FROM admin_users ORDER BY username")
    if request.method == "POST":
        if not has_permission("roles_create"):
            return forbidden_response()
        name = request.form.get("name", "").strip()
        selected = [key for key in request.form.getlist("permissions") if key in PERMISSION_KEYS]
        assign_user_ids = [int(uid) for uid in request.form.getlist("assign_users") if uid.isdigit()]
        if not name:
            flash("Role name is required.", "danger")
        elif query("SELECT id FROM roles WHERE name = ?", (name,), one=True):
            flash("That role already exists.", "danger")
        else:
            role_id = execute("INSERT INTO roles (name, description, created_at) VALUES (?, ?, ?)", (name, request.form.get("description", "").strip(), datetime.now().isoformat(timespec="seconds")))
            for key in selected:
                execute("INSERT INTO role_permissions (role_id, permission_key) VALUES (?, ?)", (role_id, key))
            for uid in assign_user_ids:
                execute("UPDATE admin_users SET role_id = ? WHERE id = ?", (role_id, uid))
            log_role_audit("role_created", name, f"perms={len(selected)}, users={len(assign_user_ids)}")
            flash(f"Role created and {len(assign_user_ids)} user(s) assigned." if assign_user_ids else "Role created.", "success")
        return redirect(url_for("admin_roles"))
    roles = query("SELECT * FROM roles ORDER BY name")
    role_permissions = {role["id"]: {item["permission_key"] for item in query("SELECT permission_key FROM role_permissions WHERE role_id = ?", (role["id"],))} for role in roles}
    role_users = {}
    for role in roles:
        role_users[role["id"]] = query("SELECT id, username, full_name FROM admin_users WHERE role_id = ? ORDER BY username", (role["id"],))
    audit = query("SELECT * FROM role_audit_log ORDER BY id DESC LIMIT 30")
    return render_template("admin_roles.html", roles=roles, role_permissions=role_permissions, permission_sections=PERMISSION_SECTIONS, permission_labels=PERMISSION_LABELS, audit=audit, all_users=all_users, role_users=role_users)


@app.route("/admin/roles/<int:role_id>/permissions", methods=["POST"])
@admin_required
@permission_required("roles_update")
def update_role_permissions(role_id):
    role = query("SELECT name FROM roles WHERE id = ?", (role_id,), one=True)
    if not role:
        abort(404)
    selected = [key for key in request.form.getlist("permissions") if key in PERMISSION_KEYS]
    execute("DELETE FROM role_permissions WHERE role_id = ?", (role_id,))
    for key in selected:
        execute("INSERT INTO role_permissions (role_id, permission_key) VALUES (?, ?)", (role_id, key))
    log_role_audit("role_permissions_updated", role["name"], ",".join(selected))
    flash("Role permissions updated.", "success")
    return redirect(url_for("admin_roles"))


@app.route("/admin/roles/<int:role_id>/edit", methods=["POST"])
@admin_required
@permission_required("roles_update")
def edit_role(role_id):
    role = query("SELECT name FROM roles WHERE id = ?", (role_id,), one=True)
    if not role:
        abort(404)
    name = request.form.get("name", "").strip()
    description = request.form.get("description", "").strip()
    if not name:
        flash("Role name is required.", "danger")
        return redirect(url_for("admin_roles"))
    if query("SELECT id FROM roles WHERE name = ? AND id != ?", (name, role_id), one=True):
        flash("That role already exists.", "danger")
        return redirect(url_for("admin_roles"))
    execute("UPDATE roles SET name = ?, description = ? WHERE id = ?", (name, description, role_id))
    log_role_audit("role_updated", name, f"previous_name={role['name']}")
    flash("Role updated.", "success")
    return redirect(url_for("admin_roles"))


@app.route("/admin/roles/<int:role_id>/delete", methods=["POST"])
@admin_required
@permission_required("roles_delete")
def delete_role(role_id):
    role = query("SELECT name FROM roles WHERE id = ?", (role_id,), one=True)
    if not role:
        abort(404)
    if session.get("role_id") == role_id:
        flash("You cannot delete the role you are currently assigned to.", "danger")
        return redirect(url_for("admin_roles"))
    execute("UPDATE admin_users SET role_id = NULL WHERE role_id = ?", (role_id,))
    execute("DELETE FROM role_permissions WHERE role_id = ?", (role_id,))
    execute("DELETE FROM roles WHERE id = ?", (role_id,))
    log_role_audit("role_deleted", role["name"])
    flash(f'Role "{role["name"]}" deleted. Assigned staff now have no role.', "success")
    return redirect(url_for("admin_roles"))


# ────────────────────────────────────────────
# Admin pages
# ────────────────────────────────────────────
@app.route("/admin/heartbeat", methods=["POST"])
@admin_required
def admin_heartbeat():
    """AJAX heartbeat — keeps the session alive and returns remaining seconds."""
    if not validate_csrf_token():
        abort(403)
    try:
        login_ts = float(session.get("_login_time", 0))
        remaining = app.config["PERMANENT_SESSION_LIFETIME"].total_seconds() - (time.time() - login_ts)
    except (ValueError, TypeError):
        remaining = 0
    return jsonify({"ok": True, "expires_in": max(0, int(remaining))})


@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)


@app.route("/admin/games", methods=["GET", "POST"])
@admin_required
@permission_required("games_view")
def admin_games():
    if request.method == "POST":
        if not has_permission("games_create"):
            return forbidden_response()
        individual_price = request.form.get("individual_price", "").strip()
        group_price = request.form.get("group_price", "").strip()
        if not individual_price and not group_price:
            flash("Add at least one price: Individual or Group.", "danger")
            return redirect(url_for("admin_games"))
        image, image_error = process_image_upload(
            request.files.get("image"), category="games", game_title=request.form["title"]
        )
        if image_error:
            flash(image_error, "danger")
            return redirect(url_for("admin_games"))
        # Canonical per-game folders exist as soon as a game is created
        game_folder = sanitize_folder_name(request.form["title"])
        if game_folder:
            try:
                (IMAGE_ROOT / "Games" / game_folder).mkdir(parents=True, exist_ok=True)
                (IMAGE_ROOT / "Registrations" / game_folder).mkdir(parents=True, exist_ok=True)
            except OSError:
                pass
        execute(
            """
            INSERT INTO games
            (title, platform, individual_price, group_price, image, reg_from, reg_to,
             discount_label, individual_old_price, group_old_price,
             min_members, max_members,
             price_for_2, price_for_3, price_for_4,
             old_price_2, old_price_3, old_price_4,
             discount_from, discount_to, discount_pct, active, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                request.form["title"].strip(),
                request.form["platform"].strip(),
                individual_price,
                group_price,
                image or "",
                request.form.get("reg_from", "").strip(),
                request.form.get("reg_to", "").strip(),
                request.form.get("discount_label", "").strip(),
                "",
                "",
                str(max(1, min(4, int(request.form.get("min_members", 1) or 1)))),
                str(max(1, min(4, int(request.form.get("max_members", 1) or 1)))),
                request.form.get("price_for_2", "").strip(),
                request.form.get("price_for_3", "").strip(),
                request.form.get("price_for_4", "").strip(),
                "",
                "",
                "",
                request.form.get("discount_from", "").strip(),
                request.form.get("discount_to", "").strip(),
                request.form.get("discount_pct", "").strip(),
                1 if request.form.get("active") else 0,
                datetime.now().isoformat(timespec="seconds"),
            ),
        )
        flash("Game card created.", "success")
        return redirect(url_for("admin_games"))
    games = query("SELECT * FROM games WHERE deleted_at = '' ORDER BY id DESC")
    return render_template("admin_games.html", games=games)


@app.route("/admin/games/<int:game_id>/toggle", methods=["POST"])
@admin_required
@permission_required("games_update")
def toggle_game(game_id):
    execute("UPDATE games SET active = 1 - active WHERE id = ?", (game_id,))
    flash("Game visibility updated.", "success")
    return redirect(url_for("admin_games"))


@app.route("/admin/games/<int:game_id>/update", methods=["POST"])
@admin_required
@permission_required("games_update")
def update_game(game_id):
    existing = query("SELECT * FROM games WHERE id = ?", (game_id,), one=True)
    if not existing:
        flash("Game not found.", "danger")
        return redirect(url_for("admin_games"))
    individual_price = request.form.get("individual_price", "").strip()
    group_price = request.form.get("group_price", "").strip()
    if not individual_price and not group_price:
        flash("Add at least one price: Individual or Group.", "danger")
        return redirect(url_for("admin_games"))

    new_title = request.form["title"].strip()
    image, image_error = process_image_upload(
        request.files.get("image"), category="games", game_title=new_title
    )
    if image_error:
        flash(image_error, "danger")
        return redirect(url_for("admin_games"))
    if image and existing["image"]:
        delete_upload(existing["image"])
    # Keep per-game folders present when a game is renamed/retitled
    game_folder = sanitize_folder_name(new_title)
    if game_folder:
        try:
            (IMAGE_ROOT / "Games" / game_folder).mkdir(parents=True, exist_ok=True)
            (IMAGE_ROOT / "Registrations" / game_folder).mkdir(parents=True, exist_ok=True)
        except OSError:
            pass

    execute(
        """
        UPDATE games SET title = ?, platform = ?, individual_price = ?, group_price = ?,
        image = ?, reg_from = ?, reg_to = ?, discount_label = ?, individual_old_price = ?,
        group_old_price = ?,
        min_members = ?, max_members = ?,
        price_for_2 = ?, price_for_3 = ?, price_for_4 = ?,
        old_price_2 = ?, old_price_3 = ?, old_price_4 = ?,
        discount_from = ?, discount_to = ?, discount_pct = ?,
        active = ? WHERE id = ?
        """,
        (
            new_title,
            request.form["platform"].strip(),
            individual_price,
            group_price,
            image or existing["image"],
            request.form.get("reg_from", "").strip(),
            request.form.get("reg_to", "").strip(),
            request.form.get("discount_label", "").strip(),
            "",
            "",
            str(max(1, min(4, int(request.form.get("min_members", 1) or 1)))),
            str(max(1, min(4, int(request.form.get("max_members", 1) or 1)))),
            request.form.get("price_for_2", "").strip(),
            request.form.get("price_for_3", "").strip(),
            request.form.get("price_for_4", "").strip(),
            "",
            "",
            "",
            request.form.get("discount_from", "").strip(),
            request.form.get("discount_to", "").strip(),
            request.form.get("discount_pct", "").strip(),
            1 if request.form.get("active") else 0,
            game_id,
        ),
    )
    flash("Game card updated.", "success")
    return redirect(url_for("admin_games"))


@app.route("/admin/games/<int:game_id>/delete", methods=["POST"])
@admin_required
@permission_required("games_delete")
def delete_game(game_id):
    row = query("SELECT * FROM games WHERE id = ?", (game_id,), one=True)
    if not row:
        flash("Game not found.", "danger")
        return redirect(url_for("admin_games"))

    reg_count = query("SELECT COUNT(*) AS c FROM registrations WHERE game_id = ?", (game_id,), one=True)["c"]
    if reg_count > 0:
        # Historical registrations, tickets and payments reference this game.
        # Archive it instead of destroying the shared history.
        execute(
            "UPDATE games SET deleted_at = ?, active = 0 WHERE id = ?",
            (datetime.now().isoformat(timespec="seconds"), game_id),
        )
        flash(
            f'"{row["title"]}" was removed from the site. Its {reg_count} registration(s) '
            "and ticket history are preserved.",
            "success",
        )
        return redirect(url_for("admin_games"))

    if row["image"]:
        delete_upload(row["image"])
    execute("DELETE FROM games WHERE id = ?", (game_id,))
    flash("Game deleted.", "success")
    return redirect(url_for("admin_games"))


@app.route("/admin/media", methods=["GET", "POST"])
@admin_required
@permission_required("media_view")
def admin_media():
    if request.method == "POST":
        if not has_permission("media_create"):
            return forbidden_response()
        media_kind = request.form.get("kind", "gallery")
        media_category = "home" if media_kind == "hero" else "gallery"
        image, image_error = process_image_upload(request.files.get("image"), category=media_category)
        if image_error:
            flash(image_error, "danger")
            return redirect(url_for("admin_media"))
        if image:
            execute(
                "INSERT INTO media (kind, title, caption, image, created_at) VALUES (?, ?, ?, ?, ?)",
                (
                    request.form["kind"],
                    request.form["title"].strip(),
                    request.form.get("caption", "").strip(),
                    image,
                    datetime.now().isoformat(timespec="seconds"),
                ),
            )
            flash("Image uploaded.", "success")
        else:
            flash("Please select a file to upload.", "danger")
        return redirect(url_for("admin_media"))

    hero = query("SELECT * FROM media WHERE kind = 'hero' ORDER BY id DESC")
    gallery_items = query("SELECT * FROM media WHERE kind = 'gallery' ORDER BY id DESC")
    return render_template("admin_media.html", hero=hero, gallery_items=gallery_items)


@app.route("/admin/media/<int:media_id>/edit", methods=["POST"])
@admin_required
@permission_required("media_update")
def edit_media(media_id):
    row = query("SELECT * FROM media WHERE id = ?", (media_id,), one=True)
    if not row:
        flash("Image not found.", "danger")
        return redirect(url_for("admin_media"))
    title = request.form.get("title", "").strip()
    caption = request.form.get("caption", "").strip()
    if not title:
        flash("Title is required.", "danger")
        return redirect(url_for("admin_media"))
    raw_image = request.files.get("image")
    image = row["image"]
    if raw_image and raw_image.filename:
        media_category = "home" if row["kind"] == "hero" else "gallery"
        new_image, image_error = process_image_upload(raw_image, category=media_category)
        if image_error:
            flash(image_error, "danger")
            return redirect(url_for("admin_media"))
        if new_image:
            delete_upload(row["image"])
            image = new_image
    execute("UPDATE media SET title = ?, caption = ?, image = ? WHERE id = ?", (title, caption, image, media_id))
    flash("Image updated.", "success")
    return redirect(url_for("admin_media"))


@app.route("/admin/media/<int:media_id>/delete", methods=["POST"])
@admin_required
@permission_required("media_delete")
def delete_media(media_id):
    row = query("SELECT image FROM media WHERE id = ?", (media_id,), one=True)
    if row:
        delete_upload(row["image"])
    execute("DELETE FROM media WHERE id = ?", (media_id,))
    flash("Image deleted.", "success")
    return redirect(url_for("admin_media"))


@app.route("/admin/leaders", methods=["GET", "POST"])
@admin_required
@permission_required("leadership_view")
def admin_leaders():
    if request.method == "POST":
        if not has_permission("leadership_create"):
            return forbidden_response()
        image, image_error = process_image_upload(request.files.get("image"), category="leadership")
        if image_error:
            flash(image_error, "danger")
            return redirect(url_for("admin_leaders"))
        execute(
            """
            INSERT INTO leaders (name, role, quote, image, sort_order, active, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            """,
            (
                request.form["name"].strip(),
                request.form["role"].strip(),
                request.form["quote"].strip(),
                image or "",
                request.form.get("sort_order", type=int) or 0,
                1 if request.form.get("active") else 0,
                datetime.now().isoformat(timespec="seconds"),
            ),
        )
        flash("Leadership quote added.", "success")
        return redirect(url_for("admin_leaders"))
    leaders = query("SELECT * FROM leaders ORDER BY sort_order, id")
    return render_template("admin_leaders.html", leaders=leaders)


@app.route("/admin/leaders/<int:leader_id>/update", methods=["POST"])
@admin_required
@permission_required("leadership_update")
def update_leader(leader_id):
    existing = query("SELECT * FROM leaders WHERE id = ?", (leader_id,), one=True)
    if not existing:
        flash("Entry not found.", "danger")
        return redirect(url_for("admin_leaders"))

    image, image_error = process_image_upload(request.files.get("image"), category="leadership")
    if image_error:
        flash(image_error, "danger")
        return redirect(url_for("admin_leaders"))
    if image and existing["image"]:
        delete_upload(existing["image"])

    execute(
        """
        UPDATE leaders SET name = ?, role = ?, quote = ?, image = ?, sort_order = ?, active = ?
        WHERE id = ?
        """,
        (
            request.form["name"].strip(),
            request.form["role"].strip(),
            request.form["quote"].strip(),
            image or existing["image"],
            request.form.get("sort_order", type=int) or 0,
            1 if request.form.get("active") else 0,
            leader_id,
        ),
    )
    flash("Leadership entry updated.", "success")
    return redirect(url_for("admin_leaders"))


@app.route("/admin/leaders/<int:leader_id>/delete", methods=["POST"])
@admin_required
@permission_required("leadership_delete")
def delete_leader(leader_id):
    row = query("SELECT image FROM leaders WHERE id = ?", (leader_id,), one=True)
    if row:
        delete_upload(row["image"])
    execute("DELETE FROM leaders WHERE id = ?", (leader_id,))
    flash("Leadership entry deleted.", "success")
    return redirect(url_for("admin_leaders"))


@app.route("/admin/sponsors", methods=["GET", "POST"])
@admin_required
@permission_required("sponsors_view")
def admin_sponsors():
    if request.method == "POST":
        if not has_permission("sponsors_create"):
            return forbidden_response()
        logo, logo_error = process_image_upload(request.files.get("logo"), category="partners")
        if logo_error:
            flash(logo_error, "danger")
            return redirect(url_for("admin_sponsors"))
        execute(
            """
            INSERT INTO sponsors (name, tier, commitment, website, logo, sort_order, active, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                request.form["name"].strip(),
                "",
                "",
                request.form.get("website", "").strip(),
                logo or "",
                0,
                1 if request.form.get("active") else 0,
                datetime.now().isoformat(timespec="seconds"),
            ),
        )
        flash("Sponsor / partner added.", "success")
        return redirect(url_for("admin_sponsors"))
    sponsors = query("SELECT * FROM sponsors ORDER BY sort_order, id")
    return render_template("admin_sponsors.html", sponsors=sponsors)


@app.route("/admin/sponsors/<int:sponsor_id>/delete", methods=["POST"])
@admin_required
@permission_required("sponsors_delete")
def delete_sponsor(sponsor_id):
    row = query("SELECT logo FROM sponsors WHERE id = ?", (sponsor_id,), one=True)
    if row:
        delete_upload(row["logo"])
    execute("DELETE FROM sponsors WHERE id = ?", (sponsor_id,))
    flash("Sponsor deleted.", "success")
    return redirect(url_for("admin_sponsors"))


@app.route("/admin/sponsors/<int:sponsor_id>/toggle", methods=["POST"])
@admin_required
@permission_required("sponsors_update")
def toggle_sponsor(sponsor_id):
    execute("UPDATE sponsors SET active = 1 - active WHERE id = ?", (sponsor_id,))
    flash("Sponsor visibility updated.", "success")
    return redirect(url_for("admin_sponsors"))


@app.route("/admin/sponsors/<int:sponsor_id>/edit", methods=["POST"])
@admin_required
@permission_required("sponsors_update")
def edit_sponsor(sponsor_id):
    row = query("SELECT logo FROM sponsors WHERE id = ?", (sponsor_id,), one=True)
    if not row:
        flash("Sponsor not found.", "danger")
        return redirect(url_for("admin_sponsors"))
    name = request.form.get("name", "").strip()
    website = request.form.get("website", "").strip()
    active = 1 if request.form.get("active") else 0
    if not name:
        flash("Sponsor name is required.", "danger")
        return redirect(url_for("admin_sponsors"))
    new_logo, logo_error = process_image_upload(request.files.get("logo"), category="partners")
    if logo_error:
        flash(logo_error, "danger")
        return redirect(url_for("admin_sponsors"))
    if new_logo:
        delete_upload(row["logo"])
        logo = new_logo
    else:
        logo = row["logo"]
    execute(
        "UPDATE sponsors SET name = ?, website = ?, logo = ?, active = ? WHERE id = ?",
        (name, website, logo, active, sponsor_id),
    )
    flash("Sponsor updated.", "success")
    return redirect(url_for("admin_sponsors"))


# ── 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)


@app.route("/admin/team", methods=["GET", "POST"])
@admin_required
@permission_required("team_view")
def admin_team():
    if request.method == "POST":
        if not has_permission("team_create"):
            return forbidden_response()
        title = request.form.get("title", "").strip()
        if not title:
            flash("Section title is required.", "danger")
            return redirect(url_for("admin_team"))
        desc = request.form.get("description", "").strip()
        max_order = query("SELECT COALESCE(MAX(sort_order),0) AS m FROM team_sections", one=True)["m"]
        sec_id = execute(
            "INSERT INTO team_sections (title, description, sort_order, active, created_at) VALUES (?, ?, ?, 1, ?)",
            (title, desc, max_order + 1, datetime.now().isoformat(timespec="seconds")),
        )
        num_rows = max(1, min(10, int(request.form.get("num_rows", 2))))
        for i in range(num_rows):
            direction = request.form.get(f"row_dir_{i}", "ltr")
            speed = max(10, min(120, int(request.form.get(f"row_speed_{i}", 30))))
            execute(
                "INSERT INTO team_rows (section_id, row_index, direction, speed, enabled) VALUES (?, ?, ?, ?, 1)",
                (sec_id, i, direction, speed),
            )
        flash(f'Section "{title}" created.', "success")
        return redirect(url_for("admin_team"))

    sections = query("SELECT * FROM team_sections ORDER BY sort_order, id")
    full = []
    for sec in sections:
        members = query(
            "SELECT * FROM team_members WHERE section_id = ? ORDER BY sort_order, id",
            (sec["id"],),
        )
        rows = query(
            "SELECT * FROM team_rows WHERE section_id = ? ORDER BY row_index",
            (sec["id"],),
        )
        full.append({"section": sec, "members": members, "rows": rows})
    return render_template("admin_team.html", team_data=full)


@app.route("/admin/team/section/<int:sec_id>/edit", methods=["POST"])
@admin_required
@permission_required("team_update")
def edit_team_section(sec_id):
    sec = query("SELECT * FROM team_sections WHERE id = ?", (sec_id,), one=True)
    if not sec:
        flash("Section not found.", "danger")
        return redirect(url_for("admin_team"))
    title = request.form.get("title", "").strip()
    if not title:
        flash("Title is required.", "danger")
        return redirect(url_for("admin_team"))
    desc = request.form.get("description", "").strip()
    active = 1 if request.form.get("active") else 0
    execute("UPDATE team_sections SET title = ?, description = ?, active = ? WHERE id = ?",
            (title, desc, active, sec_id))
    num_rows = max(1, min(10, int(request.form.get("num_rows", 2))))
    existing = {r["row_index"]: r for r in query("SELECT * FROM team_rows WHERE section_id = ?", (sec_id,))}
    for i in range(num_rows):
        direction = request.form.get(f"row_dir_{i}", "ltr")
        speed = max(10, min(120, int(request.form.get(f"row_speed_{i}", 30))))
        enabled = 1 if request.form.get(f"row_enabled_{i}") is not None else 0
        if i in existing:
            execute("UPDATE team_rows SET direction = ?, speed = ?, enabled = ? WHERE id = ?",
                    (direction, speed, enabled, existing[i]["id"]))
        else:
            execute("INSERT INTO team_rows (section_id, row_index, direction, speed, enabled) VALUES (?, ?, ?, ?, ?)",
                    (sec_id, i, direction, speed, enabled))
    for idx, r in existing.items():
        if idx >= num_rows:
            execute("DELETE FROM team_rows WHERE id = ?", (r["id"],))
    flash("Section updated.", "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/team/section/<int:sec_id>/delete", methods=["POST"])
@admin_required
@permission_required("team_delete")
def delete_team_section(sec_id):
    sec = query("SELECT * FROM team_sections WHERE id = ?", (sec_id,), one=True)
    if not sec:
        flash("Section not found.", "danger")
        return redirect(url_for("admin_team"))
    members = query("SELECT image FROM team_members WHERE section_id = ?", (sec_id,))
    for m in members:
        delete_upload(m["image"])
    execute("DELETE FROM team_members WHERE section_id = ?", (sec_id,))
    execute("DELETE FROM team_rows WHERE section_id = ?", (sec_id,))
    execute("DELETE FROM team_sections WHERE id = ?", (sec_id,))
    flash(f'Section "{sec["title"]}" deleted.', "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/team/section/<int:sec_id>/reorder", methods=["POST"])
@admin_required
@permission_required("team_update")
def reorder_team_section(sec_id):
    direction = request.form.get("direction")
    sec = query("SELECT sort_order FROM team_sections WHERE id = ?", (sec_id,), one=True)
    if not sec:
        return redirect(url_for("admin_team"))
    if direction == "up":
        swap = query("SELECT id, sort_order FROM team_sections WHERE sort_order < ? ORDER BY sort_order DESC LIMIT 1",
                     (sec["sort_order"],), one=True)
    else:
        swap = query("SELECT id, sort_order FROM team_sections WHERE sort_order > ? ORDER BY sort_order ASC LIMIT 1",
                     (sec["sort_order"],), one=True)
    if swap:
        execute("UPDATE team_sections SET sort_order = ? WHERE id = ?", (swap["sort_order"], sec_id))
        execute("UPDATE team_sections SET sort_order = ? WHERE id = ?", (sec["sort_order"], swap["id"]))
    flash("Order updated.", "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/team/section/<int:sec_id>/member/add", methods=["POST"])
@admin_required
@permission_required("team_create")
def add_team_member(sec_id):
    sec = query("SELECT id FROM team_sections WHERE id = ?", (sec_id,), one=True)
    if not sec:
        flash("Section not found.", "danger")
        return redirect(url_for("admin_team"))
    name = request.form.get("name", "").strip()
    if not name:
        flash("Member name is required.", "danger")
        return redirect(url_for("admin_team"))
    image, image_error = process_image_upload(request.files.get("image"), category="team")
    if image_error:
        flash(image_error, "danger")
        return redirect(url_for("admin_team"))
    max_order = query("SELECT COALESCE(MAX(sort_order),0) AS m FROM team_members WHERE section_id = ?",
                      (sec_id,), one=True)["m"]
    execute(
        "INSERT INTO team_members (section_id, name, image, sort_order, active, created_at) VALUES (?, ?, ?, ?, 1, ?)",
        (sec_id, name, image or "", max_order + 1, datetime.now().isoformat(timespec="seconds")),
    )
    flash(f'"{name}" added.', "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/team/member/<int:member_id>/edit", methods=["POST"])
@admin_required
@permission_required("team_update")
def edit_team_member(member_id):
    member = query("SELECT * FROM team_members WHERE id = ?", (member_id,), one=True)
    if not member:
        flash("Member not found.", "danger")
        return redirect(url_for("admin_team"))
    name = request.form.get("name", "").strip()
    if not name:
        flash("Name is required.", "danger")
        return redirect(url_for("admin_team"))
    active = 1 if request.form.get("active") else 0
    new_image, image_error = process_image_upload(request.files.get("image"), category="team")
    if image_error:
        flash(image_error, "danger")
        return redirect(url_for("admin_team"))
    if new_image:
        delete_upload(member["image"])
        image = new_image
    else:
        image = member["image"]
    execute("UPDATE team_members SET name = ?, image = ?, active = ? WHERE id = ?",
            (name, image, active, member_id))
    flash("Member updated.", "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/team/member/<int:member_id>/delete", methods=["POST"])
@admin_required
@permission_required("team_delete")
def delete_team_member(member_id):
    member = query("SELECT * FROM team_members WHERE id = ?", (member_id,), one=True)
    if not member:
        flash("Member not found.", "danger")
        return redirect(url_for("admin_team"))
    delete_upload(member["image"])
    execute("DELETE FROM team_members WHERE id = ?", (member_id,))
    flash("Member deleted.", "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/team/member/<int:member_id>/toggle", methods=["POST"])
@admin_required
@permission_required("team_update")
def toggle_team_member(member_id):
    execute("UPDATE team_members SET active = 1 - active WHERE id = ?", (member_id,))
    flash("Member visibility updated.", "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/team/member/<int:member_id>/reorder", methods=["POST"])
@admin_required
@permission_required("team_update")
def reorder_team_member(member_id):
    member = query("SELECT * FROM team_members WHERE id = ?", (member_id,), one=True)
    if not member:
        return redirect(url_for("admin_team"))
    direction = request.form.get("direction")
    if direction == "up":
        swap = query(
            "SELECT id, sort_order FROM team_members WHERE section_id = ? AND sort_order < ? ORDER BY sort_order DESC LIMIT 1",
            (member["section_id"], member["sort_order"]), one=True)
    else:
        swap = query(
            "SELECT id, sort_order FROM team_members WHERE section_id = ? AND sort_order > ? ORDER BY sort_order ASC LIMIT 1",
            (member["section_id"], member["sort_order"]), one=True)
    if swap:
        execute("UPDATE team_members SET sort_order = ? WHERE id = ?", (swap["sort_order"], member_id))
        execute("UPDATE team_members SET sort_order = ? WHERE id = ?", (member["sort_order"], swap["id"]))
    flash("Order updated.", "success")
    return redirect(url_for("admin_team"))


@app.route("/admin/prize-pool", methods=["GET", "POST"])
@admin_required
@permission_required("prize_pool_view")
def admin_prize_pool():
    fields = [
        "prize_pool_title",
        "prize_pool_amount",
        "prize_pool_note",
    ]
    if request.method == "POST":
        if not has_permission("prize_pool_update"):
            return forbidden_response()
        settings = query("SELECT * FROM settings WHERE id = 1", one=True)
        banner_images = []
        for file in request.files.getlist("prize_banner_images"):
            uploaded, upload_error = process_image_upload(file, category="rewards")
            if upload_error:
                flash(upload_error, "danger")
                return redirect(url_for("admin_prize_pool"))
            if uploaded:
                banner_images.append(uploaded)
        if not banner_images:
            legacy_banner, legacy_error = process_image_upload(
                request.files.get("prize_banner_image"), category="rewards"
            )
            if legacy_error:
                flash(legacy_error, "danger")
                return redirect(url_for("admin_prize_pool"))
            if legacy_banner:
                banner_images.append(legacy_banner)

        old_images = split_upload_list(settings["prize_banner_images"] if "prize_banner_images" in settings.keys() else "")
        if settings["prize_banner_image"] and settings["prize_banner_image"] not in old_images:
            old_images.append(settings["prize_banner_image"])
        if banner_images:
            for old_image in old_images:
                delete_upload(old_image)
        assignments = ", ".join(f"{name} = ?" for name in fields)
        values = [request.form.get(name, "").strip() for name in fields]
        if banner_images:
            assignments += ", prize_banner_image = ?, prize_banner_images = ?"
            values.extend([banner_images[0], "\n".join(banner_images)])
        execute(f"UPDATE settings SET {assignments} WHERE id = 1", values)
        flash("Prize pool banner updated.", "success")
        return redirect(url_for("admin_prize_pool"))
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    prize_banner_images = split_upload_list(settings["prize_banner_images"] if "prize_banner_images" in settings.keys() else "")
    if settings["prize_banner_image"] and settings["prize_banner_image"] not in prize_banner_images:
        prize_banner_images.insert(0, settings["prize_banner_image"])
    return render_template("admin_prize_pool.html", settings=settings, prize_banner_images=prize_banner_images, prize_items=query("SELECT * FROM prize_items ORDER BY sort_order, id"))


@app.route("/admin/prize-items", methods=["POST"])
@admin_required
@permission_required("prize_items_create")
def add_prize_item():
    image, image_error = process_image_upload(request.files.get("image"), category="rewards")
    if image_error:
        flash(image_error, "danger")
        return redirect(url_for("admin_prize_pool"))
    execute(
        "INSERT INTO prize_items (title, amount, note, image, sort_order, active, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
        (
            request.form.get("title", "").strip(),
            request.form.get("amount", "").strip(),
            request.form.get("note", "").strip(),
            image or "",
            0,
            1 if request.form.get("active") else 0,
            datetime.now().isoformat(timespec="seconds"),
        ),
    )
    flash("Prize item added.", "success")
    return redirect(url_for("admin_prize_pool"))


@app.route("/admin/prize-items/<int:item_id>/edit", methods=["POST"])
@admin_required
@permission_required("prize_items_update")
def edit_prize_item(item_id):
    image, image_error = process_image_upload(request.files.get("image"), category="rewards")
    if image_error:
        flash(image_error, "danger")
        return redirect(url_for("admin_prize_pool"))
    if image:
        old = query("SELECT image FROM prize_items WHERE id = ?", (item_id,), one=True)
        if old and old["image"]:
            delete_upload(old["image"])
        execute("UPDATE prize_items SET title = ?, amount = ?, note = ?, image = ? WHERE id = ?",
                (request.form.get("title", "").strip(), request.form.get("amount", "").strip(),
                 request.form.get("note", "").strip(), image, item_id))
    else:
        execute("UPDATE prize_items SET title = ?, amount = ?, note = ? WHERE id = ?",
                (request.form.get("title", "").strip(), request.form.get("amount", "").strip(),
                 request.form.get("note", "").strip(), item_id))
    flash("Prize item updated.", "success")
    return redirect(url_for("admin_prize_pool"))


@app.route("/admin/prize-items/<int:item_id>/delete", methods=["POST"])
@admin_required
@permission_required("prize_items_delete")
def delete_prize_item(item_id):
    row = query("SELECT image FROM prize_items WHERE id = ?", (item_id,), one=True)
    if row and row["image"]:
        delete_upload(row["image"])
    execute("DELETE FROM prize_items WHERE id = ?", (item_id,))
    flash("Prize item deleted.", "success")
    return redirect(url_for("admin_prize_pool"))


@app.route("/admin/prize-items/<int:item_id>/toggle", methods=["POST"])
@admin_required
@permission_required("prize_items_update")
def toggle_prize_item(item_id):
    execute("UPDATE prize_items SET active = 1 - active WHERE id = ?", (item_id,))
    flash("Prize item visibility updated.", "success")
    return redirect(url_for("admin_prize_pool"))


def build_registration_filters():
    """Read filter args and return (where_sql, args, active_filters)."""
    status = request.args.get("status", "").strip()
    search = request.args.get("q", "").strip()
    game_id = request.args.get("game_id", type=int)
    date_from = request.args.get("date_from", "").strip()
    date_to = request.args.get("date_to", "").strip()

    clauses, args = [], []

    entry_type = request.args.get("entry_type", "").strip().lower()
    payment_method = request.args.get("payment_method", "").strip()
    checkin_status = request.args.get("checkin_status", "").strip().lower()

    if status in ("paid", "unpaid", "under_verification", "partial", "refunded"):
        clauses.append("registrations.payment_status = ?")
        args.append(status)

    if entry_type in ("individual", "group"):
        clauses.append("registrations.entry_type = ?")
        args.append(entry_type)

    if payment_method:
        clauses.append("registrations.payment_method = ?")
        args.append(payment_method)

    if checkin_status in ("pending", "approved", "rejected"):
        clauses.append("COALESCE(registrations.entry_status, 'pending') = ?")
        args.append(checkin_status)

    if search:
        clauses.append(
            "(registrations.full_name LIKE ? OR registrations.email LIKE ?"
            " OR registrations.phone LIKE ? OR registrations.team_name LIKE ?"
            " OR registrations.ticket_serial LIKE ? OR registrations.transaction_reference LIKE ?)"
        )
        like = f"%{search}%"
        args.extend([like, like, like, like, like, like])

    if game_id:
        clauses.append("registrations.game_id = ?")
        args.append(game_id)

    if date_from:
        clauses.append("date(registrations.created_at) >= date(?)")
        args.append(date_from)

    if date_to:
        clauses.append("date(registrations.created_at) <= date(?)")
        args.append(date_to)

    where = (" WHERE " + " AND ".join(clauses)) if clauses else ""
    filters = {
        "status": status,
        "q": search,
        "game_id": game_id or "",
        "date_from": date_from,
        "date_to": date_to,
        "entry_type": entry_type,
        "payment_method": payment_method,
        "checkin_status": checkin_status,
    }
    return where, tuple(args), filters


REGISTRATION_SELECT = """
    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
"""


@app.route("/admin/registrations")
@admin_required
@permission_required("view_registrations")
def admin_registrations():
    where, args, filters = build_registration_filters()
    db_rows = query(REGISTRATION_SELECT + where + " ORDER BY registrations.id DESC", args)
    # The list is an operational view: show what was paid and what remains
    # without forcing staff to open every registration.
    rows = []
    for row in db_rows:
        item = dict(row)
        details = registration_details(row["id"])
        item["total_payable"] = details["total_payable"] if details else "Not recorded"
        item["amount_paid"] = details["amount_paid"] if details else "Rs. 0"
        item["remaining_amount"] = details["remaining_amount"] if details else "Not recorded"
        item["team_id"] = registration_team_id(row)
        item["member_names"] = registration_member_names(row)
        rows.append(item)
    games = query("SELECT id, title, max_members FROM games ORDER BY title")
    rows_by_game = {"all": rows}
    rows_by_game.update({game["id"]: [row for row in rows if row["game_id"] == game["id"]] for game in games})
    totals = {
        "shown": len(rows),
        "paid": sum(1 for r in rows if r["payment_status"] == "paid"),
        "unpaid": sum(1 for r in rows if r["payment_status"] == "unpaid"),
        "under_verification": sum(1 for r in rows if r["payment_status"] == "under_verification"),
    }
    payment_methods = query(
        "SELECT DISTINCT payment_method FROM registrations WHERE payment_method IS NOT NULL AND payment_method != '' ORDER BY payment_method"
    )
    return render_template(
        "admin_registrations.html", rows=rows, rows_by_game=rows_by_game, filters=filters, games=games,
        totals=totals, payment_methods=payment_methods,
    )


@app.route("/admin/registrations/<int:reg_id>")
@admin_required
@permission_required("view_registrations")
def registration_view(reg_id):
    row = query(REGISTRATION_SELECT + " WHERE registrations.id = ?", (reg_id,), one=True)
    details = registration_details(reg_id)
    if not row or not details:
        flash("Registration not found.", "danger")
        return redirect(url_for("admin_registrations"))
    members = parse_team_members(row["members"])
    member_tickets = query(
        "SELECT * FROM member_tickets WHERE registration_id = ? ORDER BY member_number", (reg_id,)
    )
    member_tickets = [dict(ticket) for ticket in member_tickets]
    for ticket in member_tickets:
        member_index = int(ticket["member_number"]) - 2
        ticket["member_phone"] = members[member_index]["phone"] if 0 <= member_index < len(members) else ""
    audit_log = query(
        "SELECT * FROM ticket_audit_log WHERE registration_id = ? ORDER BY id DESC", (reg_id,)
    )
    checkin_summary = group_checkin_summary(row)
    return render_template(
        "admin_registration_view.html",
        row=row,
        details=details,
        members=members,
        member_tickets=member_tickets,
        audit_log=audit_log,
        checkin_summary=checkin_summary,
    )


@app.route("/admin/registrations/<int:reg_id>/edit", methods=["GET", "POST"])
@admin_required
@permission_required("edit_registrations")
@permission_required("view_payment_details")
def edit_registration(reg_id):
    row = query(REGISTRATION_SELECT + " WHERE registrations.id = ?", (reg_id,), one=True)
    if not row:
        flash("Registration not found.", "danger")
        return redirect(url_for("admin_registrations"))

    games = query("SELECT id, title, platform FROM games ORDER BY title")
    if request.method == "POST":
        previous_status = row["payment_status"]
        proof = row["proof_image"]
        new_proof, proof_error = process_image_upload(
            request.files.get("proof_image"), category="registrations", game_title=row["game_title"],
            override_filename=request.form.get("email", row["email"]).strip(),
        )
        if proof_error:
            flash(proof_error, "danger")
            return redirect(url_for("edit_registration", reg_id=reg_id))
        if new_proof:
            delete_upload(proof)
            proof = new_proof

        # Serial numbers are system-generated to keep database, PDF, email, and scanner in sync.
        ticket_serial = row["ticket_serial"] or make_ticket_serial(reg_id, row["game_title"])
        ticket_token = row["ticket_token"] or make_ticket_token()
        if request.form.get("regenerate_token"):
            ticket_token = make_ticket_token()

        new_status = request.form.get("payment_status", "under_verification").lower()
        if new_status not in ("under_verification", "unpaid", "paid", "partial", "refunded"):
            new_status = "under_verification"

        new_team_name = request.form.get("team_name", "").strip()
        new_entry_type = request.form.get("entry_type", "individual")
        new_members = request.form.get("members", "").strip()

        form_amount_paid = request.form.get("amount_paid", "").strip()
        if new_status == "paid":
            entry_count = team_total_count(new_members, new_entry_type)
            game_info = query("SELECT * FROM games WHERE id = ?", (request.form["game_id"],), one=True)
            if game_info:
                tmp = dict(row)
                tmp["game_id"] = request.form["game_id"]
                bp = registration_effective_price(tmp, new_entry_type, entry_count)
                full_pay = bp["discounted"]
                if not form_amount_paid or amount_value(form_amount_paid) < amount_value(full_pay):
                    form_amount_paid = full_pay

        # ── Transactional validation + update ──
        try:
            with db() as conn:
                errors = validate_registration_constraints(
                    conn,
                    email=request.form["email"],
                    cnic=request.form["cnic"],
                    game_id=request.form["game_id"],
                    team_name=new_team_name,
                    entry_type=new_entry_type,
                    members_text=new_members,
                    exclude_id=reg_id,
                )
                if errors:
                    for err in errors:
                        flash(err, "danger")
                    return redirect(url_for("edit_registration", reg_id=reg_id))

                conn.execute(
                    """
                    UPDATE registrations
                    SET full_name = ?, email = ?, phone = ?, cnic = ?, institution_type = ?,
                        university = ?, roll_no = ?, entry_type = ?, game_id = ?, team_name = ?,
                        leader_name = ?, members = ?, payment_method = ?, payment_status = ?,
                        amount_paid = ?, transaction_reference = ?, ticket_issued_at = ?,
                        proof_image = ?, ticket_serial = ?, ticket_token = ?
                    WHERE id = ?
                    """,
                    (
                        request.form["full_name"].strip(),
                        request.form["email"].strip(),
                        request.form["phone"].strip(),
                        request.form["cnic"].strip(),
                        request.form.get("institution_type", "University").strip() or "University",
                        request.form["university"].strip(),
                        request.form.get("roll_no", "").strip(),
                        new_entry_type,
                        request.form["game_id"],
                        new_team_name,
                        request.form.get("leader_name", "").strip(),
                        new_members,
                        request.form.get("payment_method", "").strip() or row["payment_method"],
                        new_status,
                        form_amount_paid,
                        request.form.get("transaction_reference", "").strip(),
                        row["ticket_issued_at"] or datetime.now().isoformat(timespec="seconds"),
                        proof,
                        ticket_serial,
                        ticket_token,
                        reg_id,
                    ),
                )
                ensure_group_member_tickets(conn)
                conn.commit()

        except sqlite3.IntegrityError:
            flash("This update conflicts with an existing registration. Please check Team Name, Nickname, CNIC, and try again.", "danger")
            return redirect(url_for("edit_registration", reg_id=reg_id))
        except Exception:
            flash("An unexpected error occurred while updating. Please try again.", "danger")
            return redirect(url_for("edit_registration", reg_id=reg_id))

        if new_status == "paid" and previous_status != "paid":
            send_approval_email(reg_id)
            flash("Registration updated. Paid confirmation email with ticket PDF is being sent.", "success")
        elif new_status == "unpaid" and previous_status != "unpaid":
            send_unpaid_email(reg_id)
            flash("Registration updated. Payment status email is being sent.", "success")
        else:
            flash("Registration updated.", "success")
        return redirect(url_for("registration_view", reg_id=reg_id))

    return render_template("admin_registration_edit.html", row=row, games=games)


@app.route("/admin/qr-scanner")
@admin_required
@permission_required("scan_qr_codes", "manually_verify_tickets", any_of=True)
def admin_qr_scanner():
    return render_template("admin_qr_scanner.html")


def group_checkin_summary(row):
    """Return team arrival counts without conflating one member with the whole team."""
    if (row["entry_type"] or "").lower() != "group":
        state = (row["entry_status"] or "pending").lower()
        return {"total": 1, "checked_in": int(state == "approved"), "rejected": int(state == "rejected"), "remaining": int(state == "pending")}
    states = [row["entry_status"] or "pending"]
    states.extend(ticket["entry_status"] or "pending" for ticket in query(
        "SELECT entry_status FROM member_tickets WHERE registration_id = ? ORDER BY member_number", (row["id"],)
    ))
    states = [state.lower() for state in states]
    checked_in = sum(state == "approved" for state in states)
    rejected = sum(state == "rejected" for state in states)
    return {"total": len(states), "checked_in": checked_in, "rejected": rejected, "remaining": len(states) - checked_in - rejected}


def registration_team_id(row):
    return f"TEAM-{int(row['id']):06d}" if (row["entry_type"] or "").lower() == "group" else ""


def registration_member_names(row):
    names = [row["leader_name"] or row["full_name"]]
    if (row["entry_type"] or "").lower() == "group":
        names.extend(member["name"] for member in parse_team_members(row["members"]) if member["name"])
    return names


def ticket_payload(ticket_record, status, message):
    """Build the JSON payload shown inside the scanner overlay."""
    row = ticket_record["registration"]
    ticket = ticket_record["ticket"]
    is_member = ticket_record["kind"] == "member"
    entry_status = (ticket["entry_status"] or "pending").lower()
    team_summary = group_checkin_summary(row)
    holder_name = ticket["member_name"] if is_member else row["full_name"]
    holder_email = ticket["member_email"] if is_member else row["email"]
    checkin_gate = ticket["checkin_gate"] or ""
    checked_in_by = ticket["checked_in_by"] or ""
    marked_at = ticket["entry_marked_at"] or ""
    details = registration_details(row["id"])
    payable = details["total_payable"] if details else "Rs. 0"
    paid = details["amount_paid"] if details else "Rs. 0"
    due = details["remaining_amount"] if details else "Rs. 0"
    return {
        "ok": True,
        "status": status,
        "message": message,
        "id": row["id"],
        "ticket_number": ticket["ticket_serial"] or make_ticket_serial(row["id"], row["game_title"]),
        "ref": f"NX-{row['id']:04d}",
        "name": holder_name or row["full_name"],
        "player_name": holder_name or row["full_name"],
        "email": holder_email or row["email"],
        "phone": row["phone"],
        "cnic": row["cnic"],
        "institution": row["university"],
        "game": row["game_title"],
        "platform": row["platform"],
        "entry_type": (row["entry_type"] or "").title(),
        "team_name": row["team_name"] or "",
        "team_id": registration_team_id(row),
        "team_members": registration_member_names(row),
        "group_leader": row["leader_name"] or row["full_name"],
        "total_members": team_summary["total"],
        "team_checked_in": team_summary["checked_in"],
        "team_rejected": team_summary["rejected"],
        "team_remaining": team_summary["remaining"],
        "payment_status": (row["payment_status"] or "").title(),
        "amount_paid": paid,
        "payment_due": due,
        "total_payable": payable,
        "applied_at": pretty_dt(row["created_at"]),
        "verified_at": pretty_dt(row["verified_at"]) if row["verified_at"] else "",
        "entry_status": entry_status,
        "entry_marked_at": pretty_dt(marked_at) if marked_at else "",
        "ticket_status": "CHECKED IN" if entry_status == "approved" else ("REJECTED" if entry_status == "rejected" else ("VALID" if (row["payment_status"] or "").lower() == "paid" else "UNPAID")),
        "checkin_status": "Already Used" if entry_status == "approved" else "Unused",
        "checkin_gate": checkin_gate,
        "checked_in_by": checked_in_by,
        "rejection_reason": ticket["rejection_reason"] or ticket["entry_note"] or "",
        "proof_url": url_for("registration_proof", reg_id=row["id"]) if row["proof_image"] else "",
        "view_url": url_for("registration_view", reg_id=row["id"]),
}


def log_ticket_action(ticket_record, action, previous_status, new_status, reason="", gate=""):
    row = ticket_record["registration"]
    ticket = ticket_record["ticket"]
    execute(
        """INSERT INTO ticket_audit_log
           (registration_id, ticket_serial, action, previous_status, new_status, reason, gate_location, staff_username, created_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (row["id"], ticket["ticket_serial"], action, previous_status, new_status,
         reason, gate, session.get("admin_username", ADMIN_USERNAME), datetime.now().isoformat(timespec="seconds")),
    )


@app.route("/admin/qr-scanner/verify", methods=["POST"])
@admin_required
@permission_required("scan_qr_codes", "manually_verify_tickets", any_of=True)
def verify_qr_ticket():
    """Scan lookup only. It never changes the record — the admin decides
    Approve / Reject from the overlay that opens after the scan."""
    payload = request.get_json(silent=True) or {}
    code = normalize_qr_token(payload.get("token"))
    if not code:
        return jsonify({"ok": False, "status": "invalid", "message": "No QR code or ticket number received."}), 400

    ticket_record = find_ticket_by_code(code)
    if not ticket_record:
        return jsonify(
            {"ok": False, "status": "invalid", "message": "No ticket matched this QR code or ticket number."}
        ), 404

    row = ticket_record["registration"]
    ticket = ticket_record["ticket"]
    entry_status = (ticket["entry_status"] or "pending").lower()
    payment_status = (row["payment_status"] or "unpaid").lower()

    if entry_status == "approved":
        status, message = "already", "ALREADY CHECKED IN - Double Entry Detected"
    elif entry_status == "rejected":
        reason_text = ticket.get("rejection_reason") or ticket.get("entry_note") or "No reason provided"
        rejected_at = pretty_dt(ticket.get("rejected_at") or ticket.get("entry_marked_at")) if ticket.get("rejected_at") or ticket.get("entry_marked_at") else ""
        status, message = "rejected", f"ENTRY REJECTED — {reason_text}" + (f" ({rejected_at})" if rejected_at else "")
    elif payment_status in ("unpaid", "under_verification"):
        status, message = "payment_required", "PAYMENT NOT VERIFIED"
    elif payment_status == "paid" and entry_status != "approved":
        status, message = "paid_ready", "PAID — ENTRY READY"
    else:
        status, message = "found", "Ticket Found — Decide Entry"

    data = ticket_payload(ticket_record, status, message)
    data["token"] = ticket["ticket_token"] or code
    data["payment_status_raw"] = payment_status
    data["entry_status_raw"] = entry_status

    if not has_permission("view_scanned_record"):
        data["name"] = data.get("name", "")[:1] + "***"
        data["player_name"] = data["name"]
        data["email"] = "***"
        data["phone"] = "***"
        data["cnic"] = "***"
        data["institution"] = "***"
        data["team_members"] = []
        data["group_leader"] = "***"
        data["proof_url"] = ""
        data["rejection_reason"] = ""
        data["checked_in_by"] = ""
        data["checkin_gate"] = ""

    return jsonify(data)


@app.route("/admin/qr-scanner/confirm-payment", methods=["POST"])
@admin_required
@permission_required("confirm_payments")
def qr_confirm_payment():
    """Confirm payment from within the scanner overlay (same-screen flow).
    After confirming, the overlay refreshes to show the ticket as Paid/Entry Ready."""
    payload = request.get_json(silent=True) or {}
    code = normalize_qr_token(payload.get("token"))
    if not code:
        return jsonify({"ok": False, "message": "No ticket reference received."}), 400

    ticket_record = find_ticket_by_code(code)
    if not ticket_record:
        return jsonify({"ok": False, "message": "Ticket not found."}), 404

    row = ticket_record["registration"]
    payment_status = (row["payment_status"] or "unpaid").lower()
    entry_status = (row["entry_status"] or "pending").lower()

    if entry_status == "approved":
        return jsonify({"ok": False, "status": "already", "message": "Ticket already checked in. Cannot change payment status."}), 409

    if payment_status == "paid":
        return jsonify({"ok": False, "status": "paid_ready", "message": "Payment is already confirmed."}), 409

    now = datetime.now().isoformat(timespec="seconds")
    details = registration_details(row["id"])
    payable_amount = details["total_payable"] if details else "Rs. 0"

    execute(
        "UPDATE registrations SET payment_status = 'paid', amount_paid = ?, verified_at = COALESCE(verified_at, ?) WHERE id = ?",
        (payable_amount, now, row["id"]),
    )

    # Re-fetch and return updated ticket data
    fresh = find_ticket_by_code(ticket_record["ticket"]["ticket_token"])
    if fresh:
        data = ticket_payload(fresh, "paid_ready", "PAID — ENTRY READY")
        data["token"] = ticket_record["ticket"]["ticket_token"] or code
        data["payment_status_raw"] = "paid"
        data["entry_status_raw"] = "pending"
    else:
        data = {"ok": True, "status": "paid_ready", "message": "Payment confirmed. Ticket refreshed.", "token": code}

    log_ticket_action(ticket_record, "payment_confirmed", payment_status, "paid", "", "")
    return jsonify(data)


@app.route("/admin/qr-scanner/decision", methods=["POST"])
@admin_required
def qr_entry_decision():
    """Admin presses Approve (entry allowed) or Reject (entry denied)."""
    payload = request.get_json(silent=True) or {}
    code = normalize_qr_token(payload.get("token"))
    decision = (payload.get("decision") or "").strip().lower()
    reason = (payload.get("reason") or "").strip()
    gate = (payload.get("gate") or "").strip()
    override = bool(payload.get("override"))
    override_reason = (payload.get("override_reason") or "").strip()

    if decision not in {"approved", "rejected", "approved_team"}:
        return jsonify({"ok": False, "status": "invalid", "message": "Unknown decision."}), 400
    if decision in {"approved", "approved_team"} and not has_permission("approve_entry"):
        return forbidden_response()
    if decision == "rejected" and not has_permission("reject_entry"):
        return forbidden_response()
    if override and not has_permission("override_duplicate_entry"):
        return forbidden_response()
    if not code:
        return jsonify({"ok": False, "status": "invalid", "message": "No ticket reference received."}), 400

    ticket_record = find_ticket_by_code(code)
    if not ticket_record:
        return jsonify({"ok": False, "status": "invalid", "message": "This ticket no longer exists."}), 404

    row = ticket_record["registration"]
    ticket = ticket_record["ticket"]
    table = "member_tickets" if ticket_record["kind"] == "member" else "registrations"
    ticket_id = ticket["id"]
    previous_status = (ticket["entry_status"] or "pending").lower()
    payment_status = (row["payment_status"] or "unpaid").lower()
    marked_at = datetime.now().isoformat(timespec="seconds")

    # SAFETY: Block approval of unpaid/under-verification tickets
    if decision in ("approved", "approved_team") and payment_status in ("unpaid", "under_verification") and not override:
        return jsonify({
            "ok": False, "status": "payment_required",
            "message": "Cannot approve entry: payment is " + payment_status.replace("_", " ").title() + ". Confirm payment first."
        }), 403
    if decision == "approved_team":
        if (row["entry_type"] or "").lower() != "group":
            return jsonify({"ok": False, "status": "invalid", "message": "Entire-team check-in is only available for group registrations."}), 400
        if group_checkin_summary(row)["checked_in"] and not override:
            return jsonify(ticket_payload(ticket_record, "already", "Some team members are already checked in. Use each member ticket individually, or provide an admin override.")), 409
        if override and not override_reason:
            return jsonify({"ok": False, "status": "invalid", "message": "An admin override reason is required."}), 400
        staff = session.get("admin_username", ADMIN_USERNAME)
        targets = [("registrations", row["id"], row["ticket_serial"])]
        targets.extend(("member_tickets", item["id"], item["ticket_serial"]) for item in query(
            "SELECT id, ticket_serial FROM member_tickets WHERE registration_id = ?", (row["id"],)
        ))
        all_checked = True
        for target_table, target_id, target_serial in targets:
            ok = atomic_checkin(target_table, target_id, staff, gate or "Main Gate", override, override_reason)
            execute(
                """INSERT INTO ticket_audit_log
                   (registration_id, ticket_serial, action, previous_status, new_status, reason, gate_location, staff_username, created_at)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
                (row["id"], target_serial, "team_override_approved" if override else "team_approved",
                 previous_status, "approved" if ok else previous_status, override_reason, gate or "Main Gate", staff, marked_at),
            )
            if not ok:
                all_checked = False
        if not all_checked:
            return jsonify(ticket_payload(ticket_record, "already", "Some team members are already checked in.")), 409
        execute("UPDATE registrations SET payment_status = 'paid', verified_at = COALESCE(verified_at, ?) WHERE id = ?", (marked_at, row["id"]))
        fresh = find_ticket_by_code(ticket["ticket_token"])
        data = ticket_payload(fresh, "approved", "Entire Team Checked In")
        data["token"] = ticket["ticket_token"] or code
        return jsonify(data)
    if decision == "approved":
        if previous_status == "approved" and not override:
            return jsonify(ticket_payload(ticket_record, "already", "This ticket has already been used.")), 409
        if previous_status == "approved" and override and not override_reason:
            return jsonify({"ok": False, "status": "invalid", "message": "An admin override reason is required."}), 400
        staff = session.get("admin_username", ADMIN_USERNAME)
        ok = atomic_checkin(table, ticket_id, staff, gate or "Main Gate", override, override_reason)
        if not ok:
            return jsonify(ticket_payload(ticket_record, "already", "This ticket has already been used.")), 409
        execute("UPDATE registrations SET payment_status = 'paid', verified_at = COALESCE(verified_at, ?) WHERE id = ?", (marked_at, row["id"]))
        log_ticket_action(ticket_record, "override_approved" if override else "approved", previous_status, "approved", override_reason, gate or "Main Gate")
        message = "Entry Approved" if not override else "Entry Approved by Admin Override"
    else:
        if not reason:
            return jsonify({"ok": False, "status": "invalid", "message": "Select a rejection reason before rejecting this ticket."}), 400
        staff = session.get("admin_username", ADMIN_USERNAME)
        ok = atomic_reject(table, ticket_id, staff, reason, gate)
        if not ok:
            return jsonify(ticket_payload(ticket_record, "already", "This ticket has already been used.")), 409
        log_ticket_action(ticket_record, "rejected", previous_status, "rejected", reason, gate)
        message = "Entry Rejected"

    fresh = find_ticket_by_code(ticket["ticket_token"])
    data = ticket_payload(fresh, decision, message)
    data["token"] = ticket["ticket_token"] or code
    return jsonify(data)


EXPORT_HEADERS = [
    "ID", "Ticket", "Player / Contact", "Game", "Type", "Team / Team ID", "Members",
    "Total", "Paid", "Remaining", "Payment", "Check-In", "Registered",
]


def _filter_summary_text(filters):
    """Human-readable line of the active filters shown on exported PDFs."""
    from xml.sax.saxutils import escape as _esc
    labels = {
        "q": "Search", "game_id": "Game", "status": "Payment Status",
        "entry_type": "Type", "payment_method": "Payment Method",
        "checkin_status": "Check-In", "date_from": "From", "date_to": "To",
    }
    parts = []
    for key, label in labels.items():
        value = str(filters.get(key) or "").strip()
        if not value:
            continue
        pretty = value.replace("_", " ").title() if key in ("status", "checkin_status") else value
        parts.append(f"{label}: {_esc(pretty)}")
    return "Filters — " + (" · ".join(parts) if parts else "None (all registrations)")


def money_value(value):
    try:
        return float(re.sub(r"[^0-9.]", "", str(value or "")) or 0)
    except ValueError:
        return 0.0


def ticket_export_values(row):
    details = registration_details(row["id"])
    total_payable = str(details["total_payable"] if details else "")
    amount_paid = str(details["amount_paid"] if details else "")
    remaining_amount = str(details["remaining_amount"] if details else "")
    roster_text = (
        f"{team_total_count(row['members'], row['entry_type'])} members - {team_roster_text(row)}"
        if row["entry_type"] == "group"
        else "Individual"
    )
    return [
        f"NX-{row['id']:04d}",
        row["ticket_serial"] or make_ticket_serial(row["id"]),
        f"{row['full_name']}\n{row['email']}\n{row['phone']}",
        row["game_title"],
        (row["entry_type"] or "").title(),
        f"{row['team_name'] or 'Individual'}\n{registration_team_id(row)}",
        roster_text,
        total_payable,
        amount_paid,
        remaining_amount,
        (row["payment_status"] or "").replace("_", " ").title(),
        "Checked In" if row["entry_status"] == "approved" else ("Rejected" if row["entry_status"] == "rejected" else "Not Arrived"),
        pretty_dt(row["created_at"]),
    ]


@app.route("/admin/registrations/export")
@admin_required
@permission_required("export_excel_reports")
def export_registrations():
    # Exports MUST honour the exact same active filters as the Registrations
    # page — identical SQL built by the identical helper, so the file can
    # never contain more/different rows than the screen shows.
    where, args, _filters = build_registration_filters()
    rows = query(REGISTRATION_SELECT + where + " ORDER BY games.title, registrations.id DESC", args)
    stamp = datetime.now().strftime("%Y%m%d_%H%M")

    from openpyxl import Workbook
    from openpyxl.drawing.image import Image as ExcelImage
    from openpyxl.formatting.rule import FormulaRule
    from openpyxl.styles import Alignment, Font, PatternFill
    from openpyxl.worksheet.datavalidation import DataValidation
    from openpyxl.utils import get_column_letter
    import io

    wb = Workbook()
    wb.remove(wb.active)
    games = query("SELECT id, title, max_members FROM games ORDER BY title")
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    _logo_ref = (settings["site_logo"] if settings and "site_logo" in settings.keys() else "") or ""
    logo_path = static_file_abs(_logo_ref) or (UPLOAD_DIR / _logo_ref)
    header_fill = PatternFill("solid", fgColor="FF7F1D1D")
    title_fill = PatternFill("solid", fgColor="FF18070B")
    status_fills = {
        "Paid": PatternFill("solid", fgColor="FFC6EFCE"), "Unpaid": PatternFill("solid", fgColor="FFFFC7CE"),
        "Under Verification": PatternFill("solid", fgColor="FFFFEB9C"),
        "Partial": PatternFill("solid", fgColor="FFFCE4D6"), "Refunded": PatternFill("solid", fgColor="FFBDD7EE"),
        "Checked In": PatternFill("solid", fgColor="FFC6EFCE"), "Rejected": PatternFill("solid", fgColor="FFFFC7CE"),
    }
    widths = [12, 26, 34, 22, 14, 28, 54, 16, 14, 16, 18, 18, 22]
    game_rows = {game["id"]: [row for row in rows if row["game_id"] == game["id"]] for game in games}
    if not games:
        games = [{"id": None, "title": "Registrations", "max_members": "1"}]
        game_rows = {None: rows}

    for game in games:
        sheet_headers = EXPORT_HEADERS[:]
        title = re.sub(r"[\\/*?:\[\]]", "", game["title"])[:31] or "Game"
        ws = wb.create_sheet(title=title)
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(sheet_headers))
        heading = ws.cell(1, 1, f"Nexora Esports Championship - {game['title']} Registrations")
        heading.fill = title_fill
        heading.font = Font(color="FFFFFFFF", bold=True, size=15)
        heading.alignment = Alignment(horizontal="center", vertical="center")
        ws.row_dimensions[1].height = 28
        if logo_path.is_file():
            try:
                # Convert through PIL to PNG bytes so any format (webp/jfif)
                # embeds cleanly regardless of extension support.
                from PIL import Image as PILImage
                logo_buf = io.BytesIO()
                with PILImage.open(logo_path) as pil_logo:
                    pil_logo.convert("RGBA").save(logo_buf, format="PNG")
                logo_buf.seek(0)
                logo = ExcelImage(logo_buf)
                logo.width, logo.height = 42, 26
                ws.add_image(logo, "A1")
            except Exception as exc:
                print(f"Excel logo could not be added: {exc}")
        ws.append([])
        ws.append(sheet_headers)
        for cell in ws[3]:
            cell.fill = header_fill
            cell.font = Font(color="FFFFFFFF", bold=True, size=10)
            cell.alignment = Alignment(vertical="center", horizontal="center", wrap_text=True)
        ws.row_dimensions[3].height = 34
        ws.freeze_panes = "A4"
        for row in game_rows.get(game["id"], []):
            ws.append(ticket_export_values(row))
            excel_row = ws.max_row
            money_cols = {sheet_headers.index(name) + 1 for name in ("Total", "Paid", "Remaining")}
            text_cols = {sheet_headers.index(name) + 1 for name in ("ID", "Ticket", "Player / Contact", "Team / Team ID")}
            for col in range(1, len(sheet_headers) + 1):
                cell = ws.cell(excel_row, col)
                cell.alignment = Alignment(vertical="top", wrap_text=True)
                if col in text_cols:
                    cell.number_format = "@"
                elif col in money_cols:
                    cell.number_format = "@"
                if row["payment_status"] == "paid":
                    cell.fill = status_fills["Paid"]
                elif row["payment_status"] == "unpaid":
                    cell.fill = status_fills["Unpaid"]
                elif row["payment_status"] == "under_verification":
                    cell.fill = status_fills["Under Verification"]
            for col in (sheet_headers.index("Payment") + 1, sheet_headers.index("Check-In") + 1):
                value = ws.cell(excel_row, col).value
                if value in status_fills:
                    ws.cell(excel_row, col).fill = status_fills[value]
                    ws.cell(excel_row, col).font = Font(bold=True)
            member_text = str(ws.cell(excel_row, sheet_headers.index("Members") + 1).value or "")
            contact_text = str(ws.cell(excel_row, sheet_headers.index("Player / Contact") + 1).value or "")
            ws.row_dimensions[excel_row].height = max(30, min(120, 15 * (member_text.count(";") + contact_text.count("\n") + 3)))
        for index, width in enumerate(widths, start=1):
            ws.column_dimensions[get_column_letter(index)].width = width
        ws.auto_filter.ref = f"A3:{get_column_letter(len(sheet_headers))}{max(ws.max_row, 3)}"
        payment_column = sheet_headers.index("Payment") + 1
        checkin_column = sheet_headers.index("Check-In") + 1
        payment_letter = get_column_letter(payment_column)
        checkin_letter = get_column_letter(checkin_column)
        validation_end_row = max(ws.max_row, 1000)
        payment_range = f"{payment_letter}4:{payment_letter}{validation_end_row}"
        checkin_range = f"{checkin_letter}4:{checkin_letter}{validation_end_row}"
        validation = DataValidation(type="list", formula1='"Under Verification,Paid,Unpaid"', allow_blank=False)
        ws.add_data_validation(validation)
        validation.add(payment_range)
        checkin_validation = DataValidation(type="list", formula1='"Not Arrived,Checked In,Rejected"', allow_blank=False)
        ws.add_data_validation(checkin_validation)
        checkin_validation.add(checkin_range)
        if ws.max_row >= 4:
            full_range = f"A4:{get_column_letter(len(sheet_headers))}{ws.max_row}"
            ws.conditional_formatting.add(
                full_range,
                FormulaRule(formula=[f'${payment_letter}4="Paid"'], fill=status_fills["Paid"]),
            )
            ws.conditional_formatting.add(
                full_range,
                FormulaRule(formula=[f'${payment_letter}4="Unpaid"'], fill=status_fills["Unpaid"]),
            )
            ws.conditional_formatting.add(
                full_range,
                FormulaRule(formula=[f'${payment_letter}4="Under Verification"'], fill=status_fills["Under Verification"]),
            )

    stream = io.BytesIO()
    wb.save(stream)
    stream.seek(0)
    return Response(
        stream.getvalue(),
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        headers={
            "Content-Disposition": f"attachment; filename=nexora_registrations_{stamp}.xlsx"
        },
    )


@app.route("/admin/registrations/export/pdf")
@admin_required
@permission_required("export_excel_reports")
def export_registrations_pdf():
    from reportlab.lib import colors
    from reportlab.lib.pagesizes import landscape, A4
    from reportlab.lib.styles import getSampleStyleSheet
    from reportlab.lib.units import mm
    from reportlab.platypus import Paragraph, SimpleDocTemplate, Table, TableStyle
    from xml.sax.saxutils import escape

    # Same filter helper as the list page and the Excel export — one logic,
    # three consumers (list, xlsx, pdf) that can never drift apart.
    where, args, active_filters = build_registration_filters()
    rows = query(REGISTRATION_SELECT + where + " ORDER BY games.title, registrations.id DESC", args)
    stamp = datetime.now().strftime("%Y%m%d_%H%M")
    stream = io.BytesIO()
    doc = SimpleDocTemplate(stream, pagesize=landscape(A4), rightMargin=8 * mm, leftMargin=8 * mm, topMargin=10 * mm, bottomMargin=10 * mm)
    styles = getSampleStyleSheet()

    ps = ParagraphStyle = styles["BodyText"].clone("cell")
    ps.fontSize = 7.5
    ps.leading = 10
    ps.wordWrap = "CJK"

    hs = ParagraphStyle = styles["Heading2"].clone("hdr")
    hs.fontSize = 8
    hs.leading = 10
    hs.textColor = colors.white
    hs.fontName = "Helvetica-Bold"

    def cell(text, style=ps):
        t = str(text or "").strip()
        return Paragraph(escape(t).replace("\n", "<br/>"), style)

    headers = ["ID", "Ticket", "Player / Contact", "Game", "Type", "Team / Team ID", "Members", "Total", "Paid", "Remaining", "Payment", "Check-In", "Registered"]
    data = [[cell(h, hs) for h in headers]]
    for row in rows:
        details = registration_details(row["id"])
        roster_text = (
            f"{team_total_count(row['members'], row['entry_type'])} members - {team_roster_text(row)}"
            if row["entry_type"] == "group"
            else "Individual"
        )
        data.append([
            cell(f"NX-{row['id']:04d}"),
            cell(row["ticket_serial"] or make_ticket_serial(row["id"])),
            cell(f"{row['full_name']}\n{row['email']}\n{row['phone']}"),
            cell(row["game_title"]),
            cell((row["entry_type"] or "").title()),
            cell(f"{row['team_name'] or 'Individual'}\n{registration_team_id(row)}"),
            cell(roster_text),
            cell(details["total_payable"] if details else ""),
            cell(details["amount_paid"] if details else ""),
            cell(details["remaining_amount"] if details else ""),
            cell((row["payment_status"] or "").replace("_", " ").title()),
            cell("Checked In" if row["entry_status"] == "approved" else ("Rejected" if row["entry_status"] == "rejected" else "Not Arrived")),
            cell(pretty_dt(row["created_at"])),
        ])

    page_w = landscape(A4)[0] - 16 * mm
    col_pcts = [0.045, 0.095, 0.13, 0.07, 0.05, 0.085, 0.12, 0.055, 0.05, 0.06, 0.06, 0.06, 0.06]
    colWidths = [p * page_w for p in col_pcts]

    table = Table(data, repeatRows=1, colWidths=colWidths)
    table.setStyle(TableStyle([
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#111827")),
        ("TEXTCOLOR", (0, 0), (-1, 0), colors.white),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("GRID", (0, 0), (-1, -1), 0.35, colors.HexColor("#d8e0ec")),
        ("VALIGN", (0, 0), (-1, -1), "TOP"),
        ("TOPPADDING", (0, 0), (-1, -1), 4),
        ("BOTTOMPADDING", (0, 0), (-1, -1), 4),
        ("LEFTPADDING", (0, 0), (-1, -1), 4),
        ("RIGHTPADDING", (0, 0), (-1, -1), 4),
        ("ROWBACKGROUNDS", (0, 1), (-1, -1), [colors.HexColor("#ffffff"), colors.HexColor("#f8fafc")]),
    ]))
    for idx, row in enumerate(rows, start=1):
        fill = {
            "paid": colors.HexColor("#e5f6ef"),
            "unpaid": colors.HexColor("#fde2e2"),
            "under_verification": colors.HexColor("#fff3bf"),
        }.get((row["payment_status"] or "").lower(), colors.HexColor("#ffffff"))
        table.setStyle(TableStyle([("BACKGROUND", (0, idx), (-1, idx), fill)]))

    fs = styles["BodyText"].clone("filter-summary")
    fs.fontSize = 9
    fs.leading = 12
    fs.textColor = colors.HexColor("#475569")
    doc.build([Paragraph("Nexora Registrations", styles["Title"]), Paragraph(_filter_summary_text(active_filters), fs), table])
    stream.seek(0)
    return Response(
        stream.getvalue(),
        mimetype="application/pdf",
        headers={"Content-Disposition": f"attachment; filename=nexora_registrations_{stamp}.pdf"},
    )


@app.route("/admin/registrations/<int:reg_id>/ticket.pdf")
@admin_required
@permission_required("download_pdf_tickets")
def registration_ticket_pdf(reg_id):
    details = registration_details(reg_id)
    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    if not details:
        flash("Registration not found.", "danger")
        return redirect(url_for("admin_registrations"))
    pdf = build_ticket_pdf(details, settings)
    return Response(
        pdf,
        mimetype="application/pdf",
        headers={"Content-Disposition": f"attachment; filename={details['ticket_number']}.pdf"},
    )


@app.route("/admin/registrations/<int:reg_id>/ticket")
@admin_required
@permission_required("download_pdf_tickets")
def registration_ticket_html(reg_id):
    details = registration_details(reg_id)
    if not details:
        flash("Registration not found.", "danger")
        return redirect(url_for("admin_registrations"))
    member_qrs = team_ticket_qr_items(details) if details["entry_type"].lower() == "group" else []
    return render_template("ticket_print.html", details=details, reg_id=reg_id, member_qrs=member_qrs)


@app.route("/admin/registrations/<int:reg_id>/qr.png")
@admin_required
@permission_required("download_pdf_tickets")
def registration_qr_png(reg_id):
    details = registration_details(reg_id)
    if not details:
        return Response(status=404)
    return Response(build_qr_png(ticket_qr_payload(details)), mimetype="image/png")


@app.route("/admin/member-tickets/<int:ticket_id>/qr.png")
@admin_required
@permission_required("download_pdf_tickets")
def member_ticket_qr_png(ticket_id):
    ticket = query("SELECT ticket_token FROM member_tickets WHERE id = ?", (ticket_id,), one=True)
    if not ticket or not ticket["ticket_token"]:
        return Response(status=404)
    return Response(build_qr_png(f"NXT:{ticket['ticket_token']}"), mimetype="image/png")


@app.route("/admin/registrations/<int:reg_id>/proof")
@admin_required
@permission_required("view_payment_details")
def registration_proof(reg_id):
    row = query(REGISTRATION_SELECT + " WHERE registrations.id = ?", (reg_id,), one=True)
    if not row:
        flash("Registration not found.", "danger")
        return redirect(url_for("admin_registrations"))
    return render_template("admin_registration_proof.html", row=row)


from flask import send_file
@app.route("/admin/registrations/<int:reg_id>/proof/image")
@admin_required
@permission_required("view_payment_details")
def serve_proof_image(reg_id):
    row = query("SELECT proof_image FROM registrations WHERE id = ?", (reg_id,), one=True)
    if not row or not row["proof_image"]:
        return abort(404)
    file_path = static_file_abs(row["proof_image"])
    if not file_path or not file_path.is_file():
        return abort(404)
    return send_file(str(file_path))


@app.route("/admin/registrations/<int:reg_id>/status", methods=["POST"])
@admin_required
@permission_required("confirm_payments")
def update_payment(reg_id):
    new_status = request.form.get("payment_status", "under_verification").lower()
    is_ajax = request.headers.get("X-Requested-With") == "XMLHttpRequest"
    if new_status not in ("under_verification", "paid", "unpaid"):
        if is_ajax:
            return jsonify({"ok": False, "message": "Invalid payment status."}), 400
        flash("Invalid payment status.", "danger")
        return redirect(request.referrer or url_for("admin_registrations"))
    previous = query("SELECT payment_status FROM registrations WHERE id = ?", (reg_id,), one=True)
    if not previous:
        if is_ajax:
            return jsonify({"ok": False, "message": "Registration not found."}), 404
        flash("Registration not found.", "danger")
        return redirect(url_for("admin_registrations"))
    details = registration_details(reg_id)
    payable_amount = details["total_payable"] if details else "Rs. 0"
    # A status change must also update the financial summary. Without this,
    # a previously paid amount made an Unpaid row still look paid in the UI.
    confirmed_amount = payable_amount if new_status == "paid" else "Rs. 0"
    execute(
        "UPDATE registrations SET payment_status = ?, amount_paid = ? WHERE id = ?",
        (new_status, confirmed_amount, reg_id),
    )
    updated_details = registration_details(reg_id)

    email_msg = ""
    if new_status == "paid" and (not previous or previous["payment_status"] != "paid"):
        send_ticket_email_in_background(send_approval_email, reg_id)
        message = "Marked as Paid. Email is being sent."
    elif new_status == "unpaid":
        send_ticket_email_in_background(send_unpaid_email, reg_id)
        message = "Marked as Unpaid. Email is being sent."
    elif new_status == "under_verification":
        message = "Marked as Under Verification. Awaiting admin decision."
    else:
        message = "Payment status updated."
    if is_ajax:
        return jsonify({
            "ok": True,
            "status": new_status,
            "label": new_status.replace("_", " ").title(),
            "amount_paid": updated_details["amount_paid"] if updated_details else confirmed_amount,
            "remaining_amount": updated_details["remaining_amount"] if updated_details else payable_amount,
            "message": message,
        })
    flash(message, "success")
    return redirect(request.referrer or url_for("admin_registrations"))


@app.route("/admin/registrations/<int:reg_id>/send-email", methods=["POST"])
@admin_required
@permission_required("confirm_payments")
def send_registration_email(reg_id):
    is_ajax = request.headers.get("X-Requested-With") == "XMLHttpRequest"
    row = query("SELECT id, payment_status FROM registrations WHERE id = ?", (reg_id,), one=True)
    if not row:
        if is_ajax:
            return jsonify({"ok": False, "message": "Registration not found."}), 404
        flash("Registration not found.", "danger")
        return redirect(url_for("admin_registrations"))
    status = (row["payment_status"] or "under_verification").lower()

    # Run synchronously so the admin gets the real outcome, not a guess.
    if status == "paid":
        result = send_approval_email(reg_id)
        action = "Ticket email"
    elif status == "unpaid":
        result = send_unpaid_email(reg_id)
        action = "Payment status email"
    else:
        result = send_waiting_email(reg_id)
        action = "Payment-pending email"

    sent, failed = result.get("sent", []), result.get("failed", [])
    if sent and not failed:
        message = f"{action} sent successfully with the ticket PDF attached to: {', '.join(sent)}."
        ok, category = True, "success"
    elif sent and failed:
        message = (
            f"{action} sent to: {', '.join(sent)}. "
            f"Could not send to: {', '.join(email for email, _ in failed)}."
        )
        ok, category = True, "warning"
    else:
        reason = failed[0][1] if failed else "no recipient with a valid email address was found"
        message = f"Ticket was generated successfully, but the email could not be sent ({reason}). Check SMTP settings."
        ok, category = False, "danger"

    if is_ajax:
        return jsonify({"ok": ok, "message": message}), (200 if ok else 502)
    flash(message, category)
    return redirect(request.referrer or url_for("admin_registrations"))


@app.route("/admin/registrations/<int:reg_id>/delete", methods=["POST"])
@admin_required
@permission_required("registrations_delete")
def delete_registration(reg_id):
    row = query("SELECT proof_image FROM registrations WHERE id = ?", (reg_id,), one=True)
    if row:
        delete_upload(row["proof_image"])
    execute("DELETE FROM registrations WHERE id = ?", (reg_id,))
    flash("Registration deleted.", "success")
    return redirect(request.referrer or url_for("admin_registrations"))


@app.route("/admin/contacts")
@admin_required
@permission_required("contacts_view")
def admin_contacts():
    contacts = query("SELECT * FROM contacts ORDER BY id DESC")
    return render_template("admin_contacts.html", contacts=contacts)


@app.route("/admin/contacts/<int:contact_id>/delete", methods=["POST"])
@admin_required
@permission_required("contacts_delete")
def delete_contact(contact_id):
    execute("DELETE FROM contacts WHERE id = ?", (contact_id,))
    flash("Message deleted.", "success")
    return redirect(url_for("admin_contacts"))


@app.route("/admin/settings", methods=["GET", "POST"])
@admin_required
@permission_required("settings_view")
def admin_settings():
    if request.method == "POST":
        if not has_permission("settings_update"):
            return forbidden_response()
        section = request.form.get("section", "")

        SECTION_FIELDS = {
            "event": [
                "event_title", "event_date", "early_deadline", "early_discount",
                "event_tagline", "event_venue", "event_city", "hero_heading",
                "countdown_mode",
            ],
            "payment": [
                "bank_account", "payment_note", "payment_method_label",
                "payment_bank_name", "payment_account_title", "payment_iban",
                "payment_account_number", "payment_bank_branch",
            ],
            "about": [
                "about_intro", "about_body",
            ],
            "contact": [
                "contact_email", "contact_phone", "contact_address",
                "whatsapp_link", "facebook_link", "instagram_link",
                "youtube_link", "discord_link",
            ],
            "email": [
                "smtp_host", "smtp_port", "smtp_user",
                "smtp_from_email", "smtp_admin_email", "group_ticket_delivery",
            ],
            "theme": ["nav_bg_color", "nav_text_color", "nav_accent_color"],
        }

        if section in SECTION_FIELDS:
            fields = SECTION_FIELDS[section]
            extra = ""
            values = []
            for name in fields:
                value = request.form.get(name, "").strip()
                if section == "theme" and not re.fullmatch(r"#[0-9a-fA-F]{6}", value):
                    flash("Choose valid six-digit colors for the navigation theme.", "danger")
                    return redirect(url_for("admin_settings"))
                values.append(value)
            if section == "payment":
                values.append(1 if request.form.get("registration_open") else 0)
                extra = ", registration_open = ?"
                settings = query("SELECT * FROM settings WHERE id = 1", one=True)
                qr_image, qr_error = process_image_upload(
                    request.files.get("payment_qr_image"), category="bank_qr"
                )
                if qr_error:
                    flash(qr_error, "danger")
                    return redirect(url_for("admin_settings"))
                if qr_image:
                    if settings and settings["payment_qr_image"]:
                        delete_upload(settings["payment_qr_image"])
                    extra += ", payment_qr_image = ?"
                    values.append(qr_image)
            if section == "email":
                smtp_password = request.form.get("smtp_password", "").strip()
                if smtp_password:
                    extra += ", smtp_password = ?"
                    values.append(smtp_password)
            assignments = ", ".join(f"{name} = ?" for name in fields)
            execute(f"UPDATE settings SET {assignments}{extra} WHERE id = 1", values)
            flash(f"{section.title()} settings saved.", "success")
        elif section == "branding":
            settings = query("SELECT * FROM settings WHERE id = 1", one=True)
            logo, logo_error = process_image_upload(request.files.get("site_logo"), category="logo")
            if logo_error:
                flash(logo_error, "danger")
                return redirect(url_for("admin_settings"))
            home_background, bg_error = process_image_upload(
                request.files.get("home_background"), category="home"
            )
            if bg_error:
                flash(bg_error, "danger")
                return redirect(url_for("admin_settings"))
            assignments, values = [], []
            if logo:
                old_logo = settings["site_logo"] if settings else ""
                # Never delete the canonical default assets
                if old_logo and old_logo != DEFAULT_SITE_LOGO:
                    delete_upload(old_logo)
                assignments.append("site_logo = ?")
                values.append(logo)
            if home_background:
                old_background = settings["home_background"] if settings else ""
                if old_background and old_background != DEFAULT_HOME_BACKGROUND:
                    delete_upload(old_background)
                assignments.append("home_background = ?")
                values.append(home_background)
            if assignments:
                execute(f"UPDATE settings SET {', '.join(assignments)} WHERE id = 1", values)
                flash("Branding images saved.", "success")
            else:
                flash("Choose a logo or homepage background image first.", "warning")
        else:
            flash("Unknown section.", "danger")

        return redirect(url_for("admin_settings"))

    settings = query("SELECT * FROM settings WHERE id = 1", one=True)
    return render_template("admin_settings.html", settings=settings)


@app.route("/admin/change-password", methods=["POST"])
@admin_required
def admin_change_password():
    current_password = request.form.get("current_password", "")
    new_password = request.form.get("new_password", "")
    confirm_password = request.form.get("confirm_password", "")

    if not current_password or not new_password or not confirm_password:
        flash("All password fields are required.", "danger")
        return redirect(url_for("admin_settings"))

    if len(new_password) < 8:
        flash("New password must be at least 8 characters.", "danger")
        return redirect(url_for("admin_settings"))

    if new_password != confirm_password:
        flash("New password and confirmation do not match.", "danger")
        return redirect(url_for("admin_settings"))

    is_root = session.get("admin_username") == ADMIN_USERNAME

    if is_root:
        settings_row = query("SELECT admin_password_hash FROM settings WHERE id = 1", one=True)
        stored_hash = settings_row["admin_password_hash"] if settings_row else ""
        # Once a hash is set, the plaintext fallback no longer works
        if stored_hash:
            password_ok = check_password_hash(stored_hash, current_password)
        else:
            password_ok = hmac.compare_digest(current_password, ADMIN_PASSWORD)
            if password_ok:
                # Migrate to hashed: set the hash so plaintext never works again
                execute("UPDATE settings SET admin_password_hash = ? WHERE id = 1", (generate_password_hash(new_password),))
                flash("Password changed. Root admin password is now stored securely.", "success")
                return redirect(url_for("admin_settings"))
        if not password_ok:
            flash("Current password is incorrect.", "danger")
            return redirect(url_for("admin_settings"))
        execute("UPDATE settings SET admin_password_hash = ? WHERE id = 1", (generate_password_hash(new_password),))
    else:
        staff = query("SELECT password_hash FROM admin_users WHERE username = ?", (session.get("admin_username", ""),), one=True)
        if not staff or not check_password_hash(staff["password_hash"], current_password):
            flash("Current password is incorrect.", "danger")
            return redirect(url_for("admin_settings"))
        execute("UPDATE admin_users SET password_hash = ? WHERE username = ?", (generate_password_hash(new_password), session["admin_username"]))

    flash("Password changed successfully.", "success")
    return redirect(url_for("admin_settings"))


@app.route("/admin/users/<int:user_id>/reset-password", methods=["POST"])
@admin_required
@permission_required("users_update")
def reset_user_password(user_id):
    user = query("SELECT username FROM admin_users WHERE id = ?", (user_id,), one=True)
    if not user:
        flash("User not found.", "danger")
        return redirect(url_for("admin_users"))

    new_password = request.form.get("new_password", "")
    if len(new_password) < 8:
        flash("Password must be at least 8 characters.", "danger")
        return redirect(url_for("admin_users"))

    execute("UPDATE admin_users SET password_hash = ? WHERE id = ?", (generate_password_hash(new_password), user_id))
    flash(f'Password reset for "{user["username"]}" done.', "success")
    return redirect(url_for("admin_users"))


@app.route("/admin/users/<int:user_id>/permissions", methods=["POST"])
@admin_required
@permission_required("users_update")
def save_user_permissions(user_id):
    user = query("SELECT username FROM admin_users WHERE id = ?", (user_id,), one=True)
    if not user:
        flash("User not found.", "danger")
        return redirect(url_for("admin_users"))
    selected = [key for key in request.form.getlist("permissions") if key in PERMISSION_KEYS]
    execute("DELETE FROM user_permissions WHERE user_id = ?", (user_id,))
    for key in selected:
        execute("INSERT INTO user_permissions (user_id, permission_key) VALUES (?, ?)", (user_id, key))
    log_role_audit("user_permissions_updated", user["username"], f"direct_perms={','.join(selected)}")
    flash(f'Permissions updated for "{user["username"]}".', "success")
    return redirect(url_for("admin_users"))


init_db()
ensure_canonical_image_dirs()
repair_broken_settings_images()

if __name__ == "__main__":
    import os as _os
    import sys as _sys
    import socket as _socket
    import threading as _threading

    def _lan_ip():
        try:
            s = _socket.socket(_socket.AF_INET, _socket.SOCK_DGRAM)
            s.connect(("8.8.8.8", 80))
            ip = s.getsockname()[0]
            s.close()
            return ip
        except Exception:
            return "127.0.0.1"

    _LAN = _lan_ip()

    # ── TLS context: custom cert pair via QR_CERT/QR_KEY, else self-signed ──
    def _ssl_context():
        cert = _os.environ.get("QR_CERT", "")
        key = _os.environ.get("QR_KEY", "")
        if cert and key and _os.path.exists(cert) and _os.path.exists(key):
            return (cert, key)
        return "adhoc"          # ephemeral self-signed certificate

    _force_https_only = _os.environ.get("QR_HTTPS") == "1" or "--https" in _sys.argv

    # ────────────────────────────────────────────────────────────────
    # MODE B (--https / QR_HTTPS=1): one secure server on :5000 only
    # ────────────────────────────────────────────────────────────────
    if _force_https_only:
        from werkzeug.serving import make_server as _mk
        print("\n" + "=" * 66)
        print("  NEXORA ADMIN — FULL HTTPS")
        print(f"  URL:  https://{_LAN}:5000")
        print("  First visit: Advanced -> Proceed (self-signed certificate).")
        print("=" * 66 + "\n")
        try:
            _mk("0.0.0.0", 5000, app, threaded=True,
                ssl_context=_ssl_context()).serve_forever()
        except ImportError:
            print("[!] Missing pyopenssl — install once:  pip install pyopenssl\n")
            _mk("0.0.0.0", 5000, app, threaded=True).serve_forever()

    # ────────────────────────────────────────────────────────────────
    # DEFAULT MODE: BOTH PROTOCOLS LIVE AT ONCE
    #   HTTP  :5000  → everyone (main site, admin, forms)
    #   HTTPS :5443  → secure mirror (camera / QR scanning on LAN)
    # ────────────────────────────────────────────────────────────────
    else:
        print("\n" + "=" * 66)
        print("  NEXORA ADMIN — DUAL ACCESS")
        print(f"  Website (HTTP):        http://{_LAN}:5000")
        print(f"  Camera scanner (TLS):  https://{_LAN}:5443/admin/qr-scanner")
        print("  First HTTPS visit: Advanced -> Proceed (self-signed cert).")
        print("=" * 66 + "\n")

        # ---- HTTPS mirror (:5443) in a background thread ----
        def _run_https():
            try:
                from werkzeug.serving import make_server as _mk
                _srv = _mk("0.0.0.0", 5443, app, threaded=True,
                           ssl_context=_ssl_context())
                print("  [tls] https://0.0.0.0:5443  ready")
                _srv.serve_forever()
            except ImportError:
                print("[!] HTTPS mirror offline — install once:  pip install pyopenssl")
            except OSError as e:
                print(f"[!] HTTPS mirror could not bind :5443 ({e}). "
                      f"Close the previous instance and restart.")
            except Exception as e:
                print(f"[!] HTTPS mirror stopped: {e}")

        _threading.Thread(target=_run_https, name="nexora-https", daemon=True).start()

        # ---- Main HTTP server (:5000) ----
        try:
            from waitress import serve as _waitress
            print("  [http] waitress serving 0.0.0.0:5000\n")
            _waitress(app, host="0.0.0.0", port=5000, threads=12)
        except ImportError:
            from werkzeug.serving import make_server as _mk
            print("  [http] waitress not installed — using built-in server "
                  "(pip install waitress)\n")
            _mk("0.0.0.0", 5000, app, threaded=True).serve_forever()