app.py

# =============================================================================
# RETRODB - Main Flask Application
# =============================================================================
# A retro gaming ROM library manager with web interface
# Features: ROM scanning, metadata scraping, beautiful cyberpunk UI
# =============================================================================

from werkzeug.exceptions import HTTPException
from flask import Flask, render_template, request, redirect, url_for, jsonify, session, g, send_from_directory, abort
import gzip
import os
import sys
import logging
import json
import re
import secrets
import time
from datetime import timedelta
from zoneinfo import available_timezones

# Decompression-bomb cap (Pillow MAX_IMAGE_PIXELS) is set canonically from
# config.IMAGE_MAX_PIXELS at import time in services/image_utils.py and
# services/game_media_service.py — the single source of truth. A hardcoded
# value here was silently overridden by those modules (import order), so it's
# intentionally not set in app.py.

# ---------------------------------------------------------------------------
# ROCm GPU environment — MUST be set before any library loads ROCm/MIOpen.
# Overrides gfx1032 (RX 6600-series) to use gfx1030 pre-compiled kernels,
# forces MIOpen algorithm benchmarking, and cleans stale GPU cache.
# ---------------------------------------------------------------------------
if os.path.exists('/dev/kfd'):
    _miopen_cache = os.path.join(os.path.expanduser('~'), '.cache', 'realesrgan', 'miopen')
    os.makedirs(_miopen_cache, exist_ok=True)
    os.environ['HSA_OVERRIDE_GFX_VERSION'] = '10.3.0'
    os.environ['MIOPEN_FIND_MODE'] = '3'
    os.environ['MIOPEN_USER_DB_PATH'] = _miopen_cache
    os.environ['MIOPEN_CUSTOM_CACHE_DIR'] = _miopen_cache
    # Clean stale gfx1032 cache that conflicts with gfx1030 override
    import glob as _glob
    _stale = None
    for _stale in _glob.glob(os.path.join(_miopen_cache, 'gfx1032*')):
        try:
            os.remove(_stale)
        except OSError:
            pass
    del _miopen_cache, _glob, _stale

# Import configuration
import config

# Import settings manager for user-editable settings
import settings_manager

# Import log manager for file-based logging
import log_manager

# Import services layer
from services.atomic_io import atomic_write_json
from services.database import query
from services.auth import (
    hash_password, get_current_user, get_user_settings,
    has_permission, login_required
)
from services.game_utils import (
    get_rating_image_map_js, get_rating_system_names_js,
    get_rating_crossmap_js, RATING_SYSTEM_KEYS
)
from services.template_filters import register_filters as _register_template_filters
from services.database_init import init_database, ensure_user_tables
from services.assets import asset_url
from services.settings_validators import ALLOWED_THEMES
from services.api_helpers import handle_api_errors, success as api_success, error as api_error
from services.analytics import (
    build_analytics_context,
)

# Pre-compute static rating data (derived from constants, never changes)
_RATING_IMG_MAP_JS = get_rating_image_map_js()
_RATING_SYS_NAMES_JS = get_rating_system_names_js()
_RATING_TO_TIER_JS, _TIER_TO_RATING_JS = get_rating_crossmap_js()

# =============================================================================
# FLASK APP INITIALIZATION
# =============================================================================

app = Flask(__name__)
_register_template_filters(app)
# Register asset_url as a Jinja global so it is available in every template
# (including partials rendered without the context processor, e.g. bare
# `jinja_env.get_template(...).render(...)` in tests / tools).
app.jinja_env.globals['asset_url'] = asset_url


def _get_secret_key():
    """Get or generate a persistent secret key for Flask sessions.

    Priority: RETRODB_SECRET_KEY env var > data/.secret_key file > auto-generated.
    """
    # Check environment variable first (useful for Docker deployments)
    env_key = os.environ.get('RETRODB_SECRET_KEY')
    if env_key:
        return env_key

    key_path = os.path.join(config.BASE_DIR, 'data', '.secret_key')
    os.makedirs(os.path.dirname(key_path), exist_ok=True)
    try:
        if os.path.exists(key_path):
            with open(key_path, 'r') as f:
                key = f.read().strip()
                if key:
                    return key
    except OSError:
        pass
    # Generate new key
    key = secrets.token_hex(32)
    # Pass 45.5 — atomic_write_text chmods the tmpfile to 0o600 *before*
    # the os.replace, so the final path never exists at the umask default
    # (typically 0o644). The previous form was open→write→chmod, which
    # left a brief world-readable window holding the secret key.
    try:
        from services.atomic_io import atomic_write_text
        atomic_write_text(key_path, key, mode=0o600)
    except OSError:
        pass
    return key


app.secret_key = _get_secret_key()

# Session cookie hardening
app.config['SESSION_COOKIE_HTTPONLY'] = True
app.config['SESSION_COOKIE_SAMESITE'] = 'Lax'
# Operators fronting RetroDB with a TLS reverse proxy should set
# RETRODB_SECURE_COOKIES=true so the browser drops the session cookie on
# any plain-HTTP request. Default off: on a localhost HTTP deploy the flag
# would silently break login (browser refuses to send the cookie).
app.config['SESSION_COOKIE_SECURE'] = (
    os.environ.get('RETRODB_SECURE_COOKIES', '').lower() in ('true', '1', 'yes')
)
app.config['MAX_CONTENT_LENGTH'] = config.MAX_UPLOAD_BYTES
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(days=7)

# Pass 33.1 — reverse-proxy deploys (nginx / Caddy in front of waitress).
# Behind a proxy, `request.remote_addr` is always the proxy's loopback
# address, so the IP-based login rate limiter (`services.security.
# rate_limit_login`) and flask-limiter both collapse everyone into the
# same bucket — a self-DoS + trivial bypass. Wiring werkzeug's ProxyFix
# middleware reads X-Forwarded-For (and friends) and rewrites remote_addr
# accordingly. Gated on RETRODB_TRUST_PROXY so a localhost deploy without
# a proxy won't start honouring attacker-controlled forwarded headers.
# See docs/PROXY-DEPLOY.md (added in this pass) for the trust contract.
if os.environ.get('RETRODB_TRUST_PROXY', '').lower() in ('true', '1', 'yes'):
    from werkzeug.middleware.proxy_fix import ProxyFix
    # Trust exactly one hop of X-Forwarded-For / X-Forwarded-Proto /
    # X-Forwarded-Host — the single reverse proxy directly in front. If
    # your deployment chains proxies, increase these and audit every hop.
    app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1, x_host=1, x_prefix=0)
    logger_startup = logging.getLogger(__name__)
    logger_startup.info(
        "RETRODB_TRUST_PROXY=1 — ProxyFix installed; trusting one hop of "
        "X-Forwarded-For / Proto / Host"
    )

# =============================================================================
# INTERNATIONALIZATION (Pass 43.1 — i18n foundation)
# =============================================================================
# Flask-Babel with source-text message IDs (the gettext standard): strings are
# wrapped {{ _('Save') }} and the English source IS the catalog key. Per-locale
# .po/.mo catalogs live under translations/ (committed; bundled in retrodb.spec).
from flask_babel import Babel
from services.i18n import available_locales, TRANSLATIONS_DIR

app.config['BABEL_DEFAULT_LOCALE'] = 'en'
app.config['BABEL_TRANSLATION_DIRECTORIES'] = TRANSLATIONS_DIR


def select_locale():
    """Locale-selection chain: user pref -> session -> Accept-Language -> 'en'.

    Every branch is guarded by membership in available_locales(), so a
    stale/removed locale never raises — it degrades to 'en'.
    """
    locales = available_locales()
    # 1. logged-in user's saved preference (g.user_settings is a plain dict;
    #    .get() returns None for a legacy row predating the column).
    if g.get('user_settings'):
        pref = g.user_settings.get('locale_preference')
        if pref and pref in locales:
            return pref
    # 2. transient per-session switch — intentionally retained but unreachable
    #    in v1 (no UI writes session['locale']); kept for a future
    #    anon-switcher. Not dead code.
    sess = session.get('locale')
    if sess and sess in locales:
        return sess
    # 3. browser Accept-Language
    best = request.accept_languages.best_match(locales)
    if best:
        return best
    # 4. default
    return 'en'


babel = Babel(app, locale_selector=select_locale)

# Settings "Language" dropdown reads these (services/i18n.py is the single
# source of truth shared with the selector chain + the route validator).
from services.i18n import locale_display_name
app.jinja_env.globals['available_locales'] = available_locales
app.jinja_env.globals['locale_display_name'] = locale_display_name

# i18n display helpers (Pass 43.2/43.3). All three are Jinja globals called once
# per full-page render in base.html — NOT routed through inject_config (which
# fires per-partial). flask_babel.gettext is the request-aware translator; using
# the active locale, js_i18n_map() builds window.I18N and field_labels_map()
# builds window.FIELD_LABELS. See docs/specs/i18n.md §6/§7.
from flask_babel import gettext as _flask_gettext
from services.i18n_labels import display_field_value, field_labels_map
from services.js_i18n_strings import JS_I18N_KEYS


def js_i18n_map():
    """{ msgid: translation } for the JS-used msgids, active locale (window.I18N)."""
    return {msgid: _flask_gettext(msgid) for msgid in JS_I18N_KEYS}


app.jinja_env.globals['display_field_value'] = display_field_value
app.jinja_env.globals['field_labels_map'] = field_labels_map
app.jinja_env.globals['js_i18n_map'] = js_i18n_map


def current_locale_tag():
    """Active locale as an HTML lang tag (underscore -> hyphen, e.g. pt_BR ->
    pt-BR) for the <html lang> attribute. Defaults to 'en' outside a request."""
    from flask_babel import get_locale
    locale = get_locale()
    return str(locale).replace('_', '-') if locale else 'en'


app.jinja_env.globals['current_locale_tag'] = current_locale_tag


# =============================================================================
# STATIC IMAGES — frozen-aware fallback route
# =============================================================================
# Pass 46.3 part 2 — `/static/images/<path>` paths span two roots in a
# PyInstaller bundle: scraped media + uploaded avatars live under
# config.IMAGE_PATH (writable, next to the launcher), while bundled defaults
# (placeholder.png, hardware/, ratings/, systems/, controllers/, default
# avatars/) are baked into config.STATIC_PATH/images (read-only MEIPASS).
# Try the user dir first, fall back to the bundle.  In a non-frozen install
# both roots resolve to the same directory and the fallback is a no-op.
_BUNDLE_IMAGE_DIR = os.path.join(config.STATIC_PATH, 'images')


@app.route('/static/images/<path:filepath>')
def serve_static_image(filepath):
    user_full = os.path.join(config.IMAGE_PATH, filepath)
    if os.path.isfile(user_full):
        return send_from_directory(config.IMAGE_PATH, filepath)
    bundle_full = os.path.join(_BUNDLE_IMAGE_DIR, filepath)
    if os.path.isfile(bundle_full):
        return send_from_directory(_BUNDLE_IMAGE_DIR, filepath)
    abort(404)


# =============================================================================
# RATE LIMITING
# =============================================================================

try:
    from flask_limiter import Limiter
    from flask_limiter.util import get_remote_address

    limiter = Limiter(
        get_remote_address,
        app=app,
        default_limits=[],  # No global default — only apply per-route
        storage_uri="memory://",
    )
except ImportError:
    limiter = None

# =============================================================================
# REGISTER BLUEPRINTS
# =============================================================================

# Original blueprints
from routes.auth import bp as auth_bp
from routes.trophies import bp as trophies_bp
from routes.achievements import bp as achievements_bp
from routes.tools import tools_bp
from routes.reports import reports_bp
from routes.scraper import scraper_bp

app.register_blueprint(auth_bp)
app.register_blueprint(trophies_bp)
app.register_blueprint(achievements_bp)
app.register_blueprint(tools_bp)
app.register_blueprint(reports_bp)
app.register_blueprint(scraper_bp)

# New extracted blueprints
from routes.clz_import import bp as clz_import_bp
from routes.controllers import bp as controllers_bp
from routes.bonus_discs import bp as bonus_discs_bp
from routes.settings import bp as settings_bp
from routes.systems import bp as systems_bp
from routes.games import bp as games_bp
from routes.games_hltb import bp as games_hltb_bp
from routes.games_ai import bp as games_ai_bp
from routes.games_search import bp as games_search_bp
from routes.games_media import bp as games_media_bp
from routes.maintenance import bp as maintenance_bp
from routes.bulk_scrape import bp as bulk_scrape_bp
from routes.ra_sync import bp as ra_sync_bp
from routes.scrape_logs import logs_bp
from routes.collections import bp as collections_bp
from routes.collector_trophies import bp as collector_trophies_bp
from routes.museum import bp as museum_bp
from routes.platform_import import bp as platform_import_bp
from routes.steam_achievements import bp as steam_achievements_bp
from routes.xbox_achievements import bp as xbox_achievements_bp
from routes.game_imports import bp as game_imports_bp
from routes.launch import bp as launch_bp
from routes.emulators import bp as emulators_bp
from routes.launch_settings import bp as launch_settings_bp

app.register_blueprint(clz_import_bp)
app.register_blueprint(controllers_bp)
app.register_blueprint(bonus_discs_bp)
app.register_blueprint(settings_bp)
app.register_blueprint(systems_bp)
app.register_blueprint(games_bp)
app.register_blueprint(games_hltb_bp)
app.register_blueprint(games_ai_bp)
app.register_blueprint(games_search_bp)
app.register_blueprint(games_media_bp)
app.register_blueprint(maintenance_bp)
app.register_blueprint(bulk_scrape_bp)
app.register_blueprint(ra_sync_bp)
app.register_blueprint(logs_bp)
app.register_blueprint(collections_bp)
app.register_blueprint(collector_trophies_bp)
app.register_blueprint(museum_bp)
app.register_blueprint(platform_import_bp)
app.register_blueprint(steam_achievements_bp)
app.register_blueprint(xbox_achievements_bp)
app.register_blueprint(game_imports_bp)
app.register_blueprint(launch_bp)
app.register_blueprint(emulators_bp)
app.register_blueprint(launch_settings_bp)

# Pass 44 — expose has_permission to templates as has_perm() so the ▶ Play
# button can be permission-gated in game_detail.html without leaking
# launch URLs to viewers.
from services.auth import has_permission as _has_permission_for_template
app.jinja_env.globals['has_perm'] = _has_permission_for_template

# =============================================================================
# PER-ROUTE RATE LIMITS (applied after blueprint registration)
# =============================================================================

def _rate_limit(endpoint, spec):
    """Pass 34.4 — apply a rate-limit rule to a view function by endpoint,
    hard-failing at import time if the endpoint was renamed / removed.

    Previously every call used ``app.view_functions.get(endpoint, lambda: None)``
    which silently swapped in a no-op decorator — a renamed route would
    break its rate-limiter coverage with no log line, and the next audit
    pass would miss it. Raising here surfaces the regression before the
    process ever serves a request.
    """
    if not limiter:
        return
    view = app.view_functions.get(endpoint)
    if view is None:
        raise RuntimeError(
            f"Pass 34.4: rate-limiter endpoint '{endpoint}' is not registered "
            f"(did the route get renamed?)"
        )
    # Install the wrapper. Decorating `view` and discarding the result
    # registers the limit under a name the request-time lookup never matches,
    # so nothing was enforced (Pass 59.73).
    app.view_functions[endpoint] = limiter.limit(spec)(view)


if limiter:
    # Expensive AI/scraping endpoints
    _rate_limit('games_ai.api_game_ai_fill', "10 per minute")
    _rate_limit('bulk_scrape.api_bulk_scrape_job_start', "5 per minute")
    # Login brute force protection (supplements existing IP-based rate limiting)
    _rate_limit('auth.api_login', "10 per minute")
    # Heavy admin endpoints — on a localhost deploy the realistic risk is
    # the operator double-clicking, not a malicious DoS, but matching the
    # existing login/bulk-scrape pattern costs nothing.
    _rate_limit('maintenance.api_restart', "2 per minute")
    _rate_limit('maintenance.api_scan', "3 per minute")
    _rate_limit('maintenance.api_database_optimize', "3 per minute")
    _rate_limit('maintenance.api_image_resize_start', "3 per minute")
    _rate_limit('settings.api_backup', "3 per minute")

    # Pass 25.9 — rate-limit additional expensive endpoints. Numbers match
    # the roadmap targets: AI-fill 30/hr, HLTB 60/hr, museum generate
    # 20/hr, trophy refresh 10/hr. The per-minute ceilings below are the
    # equivalents that Flask-Limiter's syntax expresses naturally; the
    # 30-per-hour target for games_ai.api_game_ai_fill is already covered
    # by the 10-per-minute rule above (60/hr effective).
    _rate_limit('games_hltb.api_hltb_lookup', "60 per hour")
    _rate_limit('games_hltb.api_hltb_search', "60 per hour")
    _rate_limit('games_hltb.api_hltb_bulk_start', "5 per hour")
    _rate_limit('museum.generate_system', "20 per hour")
    _rate_limit('museum.generate_all', "2 per hour")
    _rate_limit('collector_trophies.refresh_trophies', "10 per hour")

    # Pass 41.10.D — heavy filesystem walks; cap at 5/min so a misclick in
    # the UI or a runaway script can't hammer the disk on a multi-user host.
    _rate_limit('tools.api_archive_scanner_scan', "5 per minute")
    _rate_limit('tools.api_chd_converter_scan', "5 per minute")
    _rate_limit('tools.api_chd_verify_scan', "5 per minute")
    _rate_limit('tools.api_duplicate_finder_scan', "5 per minute")
    _rate_limit('tools.api_screenshot_dedup_scan', "5 per minute")
    # Pass 39.7 — same family as Pass 41.10.D: walks the filesystem under
    # @login_required only (non-editor users can hit it), one cap per minute
    # since a single scan can take seconds on large libraries.
    _rate_limit('reports.api_reports_multidisc_scan', "5 per minute")

    # Pass 45.8 — third-party-API fan-out endpoints. Each call below issues
    # tens-to-hundreds of remote requests against Steam / Xbox Live / PSN
    # / IGDB / RAWG / TGDB. A misclick or stuck XHR loop can burn API
    # quota or trigger account bans (PSN especially has aggressive
    # per-account rate-limits). Caps mirror the Pass 41.10.D pattern:
    #   5/min for "fetch the whole library" actions (rare admin-driven);
    #   2/hour for "bulk refresh / sync everything" actions
    #   (cron-like, never legitimate to run every minute).
    _rate_limit('platform_import.api_steam_fetch_library', "5 per minute")
    _rate_limit('platform_import.api_steam_import', "5 per minute")
    _rate_limit('platform_import.api_steam_sync_achievements', "2 per hour")
    _rate_limit('platform_import.api_xbox_fetch_library', "5 per minute")
    _rate_limit('platform_import.api_xbox_import', "5 per minute")
    _rate_limit('platform_import.api_xbox_sync_achievements', "2 per hour")
    _rate_limit('platform_import.api_psn_fetch_library', "5 per minute")
    _rate_limit('platform_import.api_psn_import', "5 per minute")
    _rate_limit('steam_achievements.api_steam_sync_all', "2 per hour")
    _rate_limit('xbox_achievements.api_xbox_sync_all', "2 per hour")
    _rate_limit('trophies.api_psn_sync_all', "2 per hour")
    _rate_limit('trophies.api_psn_bulk_refresh_start', "2 per hour")
    _rate_limit('collections.api_scrape_all_wishlist', "2 per hour")
    # Per-source scraper credit-check probes: a stuck UI poll loop can
    # otherwise issue 60 calls/minute against TGDB / IGDB / etc.
    _rate_limit('scraper.api_check_scraper', "30 per minute")
    _rate_limit('scraper.api_scraper_allowance', "30 per minute")

# =============================================================================
# REQUEST-SCOPED DB CONNECTION CLEANUP
# =============================================================================

@app.teardown_appcontext
def close_request_db(exception):
    """Close the request-scoped database connection if one was opened."""
    db = g.pop('db', None)
    if db is not None:
        try:
            db.execute("PRAGMA optimize")
        except Exception:
            pass
        db.close()


# =============================================================================
# SECURITY HEADERS
# =============================================================================

@app.after_request
def set_security_headers(response):
    """Add security headers to every response.

    X-XSS-Protection is intentionally omitted — the XSS Auditor was removed
    from Chromium/Edge and retained only in Safari, where leaving the header
    unset matches modern OWASP guidance.
    """
    response.headers['X-Content-Type-Options'] = 'nosniff'
    response.headers['X-Frame-Options'] = 'SAMEORIGIN'
    response.headers['Referrer-Policy'] = 'strict-origin-when-cross-origin'
    response.headers['Permissions-Policy'] = (
        'browsing-topics=(), camera=(), microphone=(), geolocation=(), '
        'payment=(), usb=(), interest-cohort=(), accelerometer=(), '
        'gyroscope=(), magnetometer=(), midi=()'
    )
    # HSTS only when the operator has flagged TLS is in front. On plain HTTP
    # localhost the header is ignored anyway, but sending it unconditionally
    # would be misleading.
    if app.config.get('SESSION_COOKIE_SECURE'):
        response.headers['Strict-Transport-Security'] = (
            'max-age=31536000; includeSubDomains'
        )
    # CSP ships in Report-Only until inline event handlers across templates
    # have been migrated to delegated listeners. The policy below is what we
    # intend to enforce — currently it surfaces violations in the browser
    # console so operators / devs can spot regressions before flipping to
    # enforcing mode. Nonce is per-request and exposed via {{ csp_nonce }}.
    nonce = g.get('csp_nonce', '')
    if nonce:
        response.headers['Content-Security-Policy-Report-Only'] = (
            "default-src 'self'; "
            f"script-src 'self' 'nonce-{nonce}' https://cdn.jsdelivr.net; "
            "style-src 'self' 'unsafe-inline' https://fonts.googleapis.com; "
            "img-src 'self' data: blob:; "
            "font-src 'self' https://fonts.gstatic.com; "
            "connect-src 'self'; "
            "media-src 'self'; "
            "object-src 'none'; "
            "frame-ancestors 'self'; "
            "base-uri 'self'; "
            "form-action 'self'"
        )
    return response


# Pass 21.2 — opt-out gzip compression for JSON responses. Waitress ships no
# compression so large card/filter payloads go over the wire uncompressed.
# Reverse-proxied deploys (Caddy, nginx) already compress at the edge; set
# RETRODB_DISABLE_GZIP=1 there to skip the double work.
_GZIP_ENABLED = os.environ.get('RETRODB_DISABLE_GZIP', '').lower() not in ('1', 'true', 'yes')
_GZIP_MIN_BYTES = 1024


@app.after_request
def compress_response(response):
    """gzip JSON responses >1 KB when the client advertises Accept-Encoding: gzip.

    Skips streamed/direct-passthrough responses, already-encoded bodies, and
    non-compressible status codes (204, 304). Adds Vary: Accept-Encoding so
    shared caches never serve a gzipped body to a client that did not ask for
    it.
    """
    if not _GZIP_ENABLED:
        return response
    # `direct_passthrough` bodies (send_file, StreamResponse) must not be
    # materialised — compressing them would break the streaming contract.
    if response.direct_passthrough:
        return response
    if response.status_code < 200 or response.status_code in (204, 304):
        return response
    if response.headers.get('Content-Encoding'):
        return response
    ctype = response.content_type or ''
    if 'json' not in ctype and 'javascript' not in ctype:
        return response
    if 'gzip' not in (request.headers.get('Accept-Encoding') or ''):
        # Even when we don't compress, advertise that the response varies by
        # Accept-Encoding so the browser cache keys on it correctly.
        response.headers.setdefault('Vary', 'Accept-Encoding')
        return response
    data = response.get_data()
    if len(data) < _GZIP_MIN_BYTES:
        response.headers.setdefault('Vary', 'Accept-Encoding')
        return response
    compressed = gzip.compress(data, compresslevel=6)
    response.set_data(compressed)
    response.headers['Content-Encoding'] = 'gzip'
    response.headers['Content-Length'] = str(len(compressed))
    existing_vary = response.headers.get('Vary', '')
    if existing_vary:
        if 'accept-encoding' not in existing_vary.lower():
            response.headers['Vary'] = f"{existing_vary}, Accept-Encoding"
    else:
        response.headers['Vary'] = 'Accept-Encoding'
    return response


@app.after_request
def log_slow_request(response):
    """Log a WARNING for handlers that exceed config.SLOW_REQUEST_MS.

    Pairs with SLOW_QUERY_MS — DB slowness vs. whole-handler slowness are
    distinct signals. Probe endpoints are exempted so /health polling noise
    never lands in the log. Disabled entirely when SLOW_REQUEST_MS == 0.
    """
    threshold = getattr(config, 'SLOW_REQUEST_MS', 0)
    if threshold <= 0 or request.endpoint in _PROBE_ENDPOINTS:
        return response
    start = g.get('request_start_time')
    if start is None:
        return response
    elapsed_ms = (time.perf_counter() - start) * 1000
    if elapsed_ms > threshold:
        logger.warning(
            f"slow_request {request.method} {request.path} "
            f"{elapsed_ms:.0f}ms status={response.status_code}"
        )
    return response


# =============================================================================
# HEALTH & READINESS PROBES
# =============================================================================
# Cheap unauthenticated endpoints for systemd / Docker / reverse-proxy probes.
# /health is process-alive (no DB hit); /ready exercises the DB so it catches
# lock-held / corrupt-schema states. Both are exempted from first-time-setup
# redirect and slow-request logging so they stay silent in the common case.

@app.route('/health')
def health_probe():
    return jsonify({'status': 'alive', 'version': config.APP_VERSION}), 200


@app.route('/ready')
def ready_probe():
    try:
        from services.database import get_request_db
        db = get_request_db()
        db.execute("SELECT 1").fetchone()
        return jsonify({'status': 'ready'}), 200
    except Exception as e:
        logger.warning(f"readiness probe failed: {e}")
        return jsonify({'status': 'not_ready', 'error': str(e)}), 503


# Endpoints exempted from first-time-setup redirects and slow-request log noise.
_PROBE_ENDPOINTS = frozenset({'health_probe', 'ready_probe'})


# =============================================================================
# GLOBAL ERROR HANDLERS
# =============================================================================

@app.errorhandler(404)
def handle_not_found(e):
    # Pass 34.1: API paths return the standard envelope.
    if request.path.startswith('/api/'):
        return api_error('Not found', 404)
    return render_template('base.html'), 404

@app.errorhandler(500)
def handle_internal_error(e):
    # Include the traceback — without it, the white-page 500 leaves the
    # operator with only `str(e)` to debug from. Pass 34.1: API body via
    # shared error() helper; non-API path stays HTML.
    logger.error(f"Internal server error: {e}", exc_info=True)
    if request.path.startswith('/api/'):
        return api_error('Internal server error', 500)
    return render_template('base.html'), 500

@app.errorhandler(413)
def handle_request_too_large(e):
    """Werkzeug raises this from MAX_CONTENT_LENGTH when the view first reads
    the request body. @handle_api_errors re-raises HTTPException so it lands
    here (Pass 59.45). Default page is bare HTML — give the user a hint about
    the per-file cap and where the global limit comes from.
    """
    mb = config.MAX_UPLOAD_BYTES // (1024 * 1024)
    msg = (
        f"Upload exceeds the {mb} MB request limit. "
        "Each image is also capped at 10 MB; try resizing oversized files "
        "or upload them one at a time. "
        "Operators can raise the global cap via RETRODB_MAX_UPLOAD_MB."
    )
    if request.path.startswith('/api/'):
        return api_error(msg, 413)
    return msg, 413


@app.errorhandler(HTTPException)
def handle_http_exception(e):
    """Any other HTTP error on /api/* answers with the envelope and its real
    status. @handle_api_errors lets HTTPException through (Pass 59.45), so a
    malformed JSON body's 400 or an abort(403) arrives here. The handlers for
    specific codes above take precedence; page requests keep Werkzeug's page.
    """
    if request.path.startswith('/api/'):
        return api_error(e.description or e.name, e.code or 500)
    return e


@app.errorhandler(429)
def handle_rate_limited(e):
    """Flask-Limiter's default 429 is a plain page; /api/* callers read
    JSON (Pass 59.73)."""
    if request.path.startswith('/api/'):
        from flask_babel import gettext as _
        return api_error(_('Too many requests. Please wait and try again.'), 429)
    return e


# =============================================================================
# USER SESSION SETUP
# =============================================================================

@app.before_request
def assign_request_id():
    """Assign a short correlation ID to every request.

    Stamped onto every log record via log_manager.install_request_id_factory()
    so a single grep across logs/*.log reconstructs a full request trace.
    8-char hex (~32 bits) is enough entropy for per-request uniqueness
    within a single operator's install and reads cleanly in logs.
    """
    g.request_id = secrets.token_hex(4)
    g.request_start_time = time.perf_counter()
    # Per-request CSP nonce — 128 bits, base64-url. Templates that embed
    # inline <script> blocks may opt into the enforced CSP by adding
    # nonce="{{ csp_nonce }}"; while the policy ships in report-only mode
    # that migration can happen template-by-template without breaking pages.
    g.csp_nonce = secrets.token_urlsafe(16)


@app.before_request
def load_user():
    """Load current user before each request"""
    g.user = get_current_user()
    if g.user:
        g.user_settings = get_user_settings(g.user['id'])
    else:
        g.user_settings = None

    # Ensure a CSRF token exists in the session
    if '_csrf_token' not in session:
        session['_csrf_token'] = secrets.token_hex(32)


@app.before_request
def validate_csrf():
    """Validate CSRF token on state-changing requests.

    Checks the X-CSRF-Token header against the session token for POST/PUT/DELETE.
    Exempt: login, setup, static files, and GET/HEAD/OPTIONS requests.
    """
    if request.method in ('GET', 'HEAD', 'OPTIONS'):
        return
    if not request.endpoint:
        return

    # Exempt endpoints that must work without a CSRF token
    csrf_exempt = {
        'static', 'serve_static_image',
        'auth.api_login', 'auth.login', 'setup_api',
        'setup_browse_folders',
    }
    if request.endpoint in csrf_exempt:
        return

    # Check X-CSRF-Token header or _csrf_token form field
    session_token = session.get('_csrf_token', '')
    if not session_token:
        return jsonify({'success': False, 'error': 'Invalid or missing CSRF token'}), 403
    token = request.headers.get('X-CSRF-Token', '') or request.form.get('_csrf_token', '')
    if not secrets.compare_digest(token, session_token):
        return jsonify({'success': False, 'error': 'Invalid or missing CSRF token'}), 403


@app.before_request
def check_first_time_setup():
    """Redirect to setup wizard on first launch"""
    # Skip for static files, the setup route itself, and API endpoints used by setup
    if not request.endpoint or request.endpoint in ('static', 'serve_static_image',
                                                     'setup_page', 'setup_api',
                                                     'setup_browse_folders',
                                                     'api_timezones',
                                                     'auth.api_login', 'auth.login', 'auth.logout'):
        return
    if request.endpoint in _PROBE_ENDPOINTS:
        return
    # Check if setup has been completed
    user_settings = settings_manager.load_settings()
    has_rom_path = bool(user_settings.get('rom_path') or getattr(config, 'ROM_PATH', ''))
    setup_done = user_settings.get('setup_completed', False)
    if not setup_done and not has_rom_path:
        return redirect(url_for('setup_page'))


@app.before_request
def check_force_password_change():
    """Redirect admin users who must change their default password"""
    if not g.get('user'):
        return
    if not g.user.get('force_password_change'):
        return
    # Allow access to password change endpoint, logout, static, and setup
    allowed_endpoints = ('auth.api_change_password', 'auth.api_force_change_password',
                         'auth.logout', 'static', 'serve_static_image',
                         'setup_page', 'setup_api')
    if request.endpoint in allowed_endpoints:
        return
    # Pass 59.77: an /api/* caller reads JSON. The HTML page with a 200 read
    # as success to every fetch() caller.
    if request.path.startswith('/api/'):
        from flask_babel import gettext as _
        return api_error(_('Password change required'), 403)
    # Return the force change password page
    return render_template('force_change_password.html'), 200



# Get effective paths (settings.json overrides config.py if set)
def get_rom_path():
    return settings_manager.get_effective_path('rom_path', config.ROM_PATH)


def get_esde_gamelists_path():
    return settings_manager.get_effective_path('esde_gamelists_path', getattr(config, 'ESDE_GAMELISTS_PATH', ''))


def get_esde_media_path():
    return settings_manager.get_effective_path('esde_downloaded_media_path', getattr(config, 'ESDE_DOWNLOADED_MEDIA_PATH', ''))


def get_rpcs3_trophy_path():
    return settings_manager.get_effective_path('rpcs3_trophy_path', getattr(config, 'RPCS3_TROPHY_PATH', ''))


# Make config available to templates
app.config['APP_VERSION'] = config.APP_VERSION
app.config['APP_DESCRIPTION'] = config.APP_DESCRIPTION
app.config['ROM_PATH'] = get_rom_path()
app.config['DB_PATH'] = config.DB_PATH
app.config['RPCS3_TROPHY_PATH'] = get_rpcs3_trophy_path()

# Set up logging — install the request-id factory FIRST so basicConfig's
# handler already has access to %(request_id)s on the very first record.
log_manager.install_request_id_factory()
# Pass 41.3.A — install the redactor before basicConfig, then again after it
# (below) to attach it to the StreamHandler basicConfig creates.
# install_global_redactor is idempotent. The handler filters are what redact
# records propagating from child loggers; the root-logger filter only sees
# records created on root itself (Pass 59.41).
log_manager.install_global_redactor()
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(request_id)s - %(name)s - %(levelname)s - %(message)s'
)
# Re-run to attach the redactor filter to the StreamHandler basicConfig just
# created. CategoryFileHandler already attaches its own redactor; this adds
# the console-stream safety net that the CategoryFileHandler path cannot cover.
log_manager.install_global_redactor()
logger = logging.getLogger(__name__)

# Waitress logs a WARNING every time a request queues — with a 16-thread pool
# and 20+ parallel XHRs per page load, this fires dozens of times per
# navigation and drowns the actual log. Queueing at depth 1–2 is normal and
# not actionable. Bump the threshold so only sustained back-pressure (ERROR)
# surfaces. See waitress/task.py — "Task queue depth is N" is the message.
logging.getLogger('waitress.queue').setLevel(logging.ERROR)

# Pass 34.3 — removed log_to_category + system_log. Neither had any
# importers (routes/ra_sync.py and routes/achievements.py each define
# their own local system_log). Categorised logs flow through
# log_manager.CategoryFileHandler, installed via setup_all_logging().

# =============================================================================
# JINJA2 FILTERS — moved to services/template_filters.py
# Registered below after app creation.
# =============================================================================


def get_avatar_url(user, user_settings_row):
    """Get the avatar URL for a user, or None to fall back to role emoji"""
    if not user_settings_row:
        return None
    avatar = user_settings_row.get('avatar', '') if hasattr(user_settings_row, 'get') else (user_settings_row['avatar'] if 'avatar' in user_settings_row.keys() else '')
    if not avatar:
        return None
    if avatar.startswith('default_'):
        return f'/static/images/avatars/{avatar}.svg'
    return f'/static/images/avatars/{avatar}'


# Pass 34.2 — mtime-keyed cache for the ai-scraper-enabled read. Previous
# implementation re-parsed scraper_settings.json on every Jinja render.
_AI_SCRAPER_CACHE = {'mtime': None, 'enabled': False}
_AI_SCRAPER_KEY_MAP = {
    'gemini': 'ai_gemini_api_key',
    'openai': 'ai_openai_api_key',
    'claude': 'ai_claude_api_key',
}


def _ai_scraper_enabled_cached():
    try:
        from routes.scraper import SCRAPER_SETTINGS_FILE as _ssf
        if not os.path.exists(_ssf):
            _AI_SCRAPER_CACHE['mtime'] = None
            _AI_SCRAPER_CACHE['enabled'] = False
            return False
        mtime = os.path.getmtime(_ssf)
        if _AI_SCRAPER_CACHE['mtime'] == mtime:
            return _AI_SCRAPER_CACHE['enabled']
        with open(_ssf, 'r') as _f:
            _ss = json.load(_f)
        _ak = _ss.get('api_keys', {})
        _prov = _ak.get('ai_provider', '')
        _key = _AI_SCRAPER_KEY_MAP.get(_prov, '')
        enabled = bool(_prov and _ak.get(_key, '') and _ss.get('enabled', {}).get('ai', False))
        _AI_SCRAPER_CACHE['mtime'] = mtime
        _AI_SCRAPER_CACHE['enabled'] = enabled
        return enabled
    except Exception:
        # non-fatal — missing/invalid scraper_settings.json just hides the
        # AI Fill button in the UI.
        return False


def _user_theme(user_settings_obj):
    """The logged-in user's saved theme, or None (Pass 59.38).

    Emitted into base.html's FOUC block as the fallback when this browser has
    no theme in localStorage, so the theme follows the user across devices.
    Re-validated here as well as on write: a row written before the write
    validation existed may hold any string.
    """
    if not user_settings_obj:
        return None
    theme = dict(user_settings_obj).get('theme_preference')
    return theme if theme in ALLOWED_THEMES else None


@app.context_processor
def inject_config():
    """Make config, user settings, and current user available to all templates"""
    user_settings = settings_manager.load_settings()

    # Get per-user timezone for JS and template use
    user_tz = 'UTC'
    user_settings_obj = g.get('user_settings')
    if user_settings_obj:
        if hasattr(user_settings_obj, 'get'):
            user_tz = user_settings_obj.get('timezone', 'UTC') or 'UTC'
        elif hasattr(user_settings_obj, 'keys') and 'timezone' in user_settings_obj.keys():
            user_tz = user_settings_obj['timezone'] or 'UTC'

    # Check if AI scraper is configured (for showing AI Fill button).
    # Pass 34.2 — mtime-cache the scraper_settings.json read. inject_config
    # fires on every Jinja render (dashboard, library, modal partials,
    # cards); the pre-34.2 implementation parsed the JSON file on every
    # one of those, which at ~30-50 renders per page-navigation is a
    # noticeable fixed cost. Matches the pattern settings_manager already
    # uses for its own JSON.
    ai_scraper_enabled = _ai_scraper_enabled_cached()

    return {
        'config': config,
        'user_settings': user_settings,
        'current_user': g.get('user'),
        'current_user_settings': g.get('user_settings'),
        'user_theme': _user_theme(g.get('user_settings')),
        'has_permission': has_permission,
        'get_avatar_url': get_avatar_url,
        # Pass 34.6 — asset_url is already registered as a Jinja global
        # at line 89, so it's available in every template including those
        # rendered outside a request context (tests). Removed from
        # inject_config to avoid double registration; the global wins.
        'user_timezone': user_tz,
        'csrf_token': session.get('_csrf_token', ''),
        'ai_scraper_enabled': ai_scraper_enabled,
        'rating_image_map_js': _RATING_IMG_MAP_JS,
        'rating_system_names_js': _RATING_SYS_NAMES_JS,
        'rating_system_keys': RATING_SYSTEM_KEYS,
        'rating_to_tier_js': _RATING_TO_TIER_JS,
        'tier_to_rating_js': _TIER_TO_RATING_JS,
        'collector_rank': _get_collector_rank_safe(),
        'csp_nonce': g.get('csp_nonce', ''),
    }


def _get_collector_rank_safe():
    """Return the current collector rank, or a zeroed placeholder if the
    trophies module / table isn't ready yet (e.g. first boot before the
    table has been populated). The sidebar badge reads this on every
    render, so it must never raise."""
    try:
        from routes.collector_trophies import get_current_rank
        return get_current_rank()
    except Exception:
        return None


# =============================================================================
# HELPER FUNCTIONS FOR MAIN PAGES
# =============================================================================

def get_stats():
    """Get library statistics - optimized single query"""
    try:
        stats = query("""
            SELECT
                (SELECT COUNT(*) FROM systems) as total_systems,
                (SELECT COUNT(*) FROM games) as total_games,
                (SELECT COUNT(*) FROM games WHERE scraped = 1) as scraped_games,
                (SELECT SUM(COALESCE(file_size, 0)) FROM games) as total_storage,
                (SELECT COUNT(*) FROM games WHERE completion_status IN ('completed', '100_percent')) as completed_games,
                (SELECT SUM(earned_achievements) FROM game_achievement_progress) as total_earned_achievements,
                (SELECT SUM(total_achievements) FROM game_achievement_progress) as total_available_achievements
        """, one=True)

        total_games = stats['total_games'] or 0
        completed = stats['completed_games'] or 0
        completion_rate = round((completed / total_games * 100) if total_games > 0 else 0, 1)

        # Parse HLTB playtime estimates to get total hours
        total_hltb_hours = 0
        try:
            hltb_rows = query("SELECT playtime_estimate FROM games WHERE playtime_estimate IS NOT NULL AND playtime_estimate != ''")
            for row in hltb_rows:
                est = row.get('playtime_estimate', '')
                if est:
                    # Parse "Main: 25h" or "Main Story: 25½ Hours" patterns
                    match = re.search(r'Main[^:]*:\s*([\d.½]+)', est)
                    if match:
                        val = match.group(1).replace('½', '.5')
                        try:
                            total_hltb_hours += float(val)
                        except ValueError:
                            pass
        except Exception:
            pass

        # Collection health score: weighted average of field completeness
        health_fields = query("""
            SELECT
                COUNT(*) as total,
                SUM(CASE WHEN description IS NOT NULL AND description != '' THEN 1 ELSE 0 END) as has_description,
                SUM(CASE WHEN boxart IS NOT NULL AND boxart != '' THEN 1 ELSE 0 END) as has_boxart,
                SUM(CASE WHEN genre IS NOT NULL AND genre != '' THEN 1 ELSE 0 END) as has_genre,
                SUM(CASE WHEN developer IS NOT NULL AND developer != '' THEN 1 ELSE 0 END) as has_developer,
                SUM(CASE WHEN publisher IS NOT NULL AND publisher != '' THEN 1 ELSE 0 END) as has_publisher,
                SUM(CASE WHEN release_date IS NOT NULL AND release_date != '' THEN 1 ELSE 0 END) as has_release_date,
                SUM(CASE WHEN screenshots IS NOT NULL AND screenshots != '' THEN 1 ELSE 0 END) as has_screenshots,
                SUM(CASE WHEN has_retroachievements = 1 THEN 1 ELSE 0 END) as has_ra
            FROM games
        """, one=True)

        health_score = 0
        if health_fields and health_fields['total'] > 0:
            t = health_fields['total']
            # Weighted: boxart & description more important
            health_score = round((
                (health_fields['has_boxart'] or 0) / t * 25 +
                (health_fields['has_description'] or 0) / t * 25 +
                (health_fields['has_genre'] or 0) / t * 15 +
                (health_fields['has_developer'] or 0) / t * 10 +
                (health_fields['has_publisher'] or 0) / t * 10 +
                (health_fields['has_release_date'] or 0) / t * 10 +
                (health_fields['has_screenshots'] or 0) / t * 5
            ), 1)

        # Per-field health percentages for mini rings
        health_boxart_pct = round((health_fields['has_boxart'] or 0) / t * 100, 1) if health_fields and health_fields['total'] > 0 else 0
        health_desc_pct = round((health_fields['has_description'] or 0) / t * 100, 1) if health_fields and health_fields['total'] > 0 else 0
        health_ra_pct = round((health_fields['has_ra'] or 0) / t * 100, 1) if health_fields and health_fields['total'] > 0 else 0

        # Quick-fixes count: games with no box-art (Pass 52.3). health_fields.total
        # is COUNT(*) FROM games, so this reuses the same scan as the health score.
        missing_boxart = (total_games - (health_fields['has_boxart'] or 0)) if health_fields else 0

        return {
            'total_systems': stats['total_systems'],
            'total_games': total_games,
            'scraped_games': stats['scraped_games'],
            'missing_metadata': total_games - (stats['scraped_games'] or 0),
            'total_storage': stats['total_storage'] or 0,
            'completion_rate': completion_rate,
            'completed_games': completed,
            'total_earned_achievements': stats['total_earned_achievements'] or 0,
            'total_available_achievements': stats['total_available_achievements'] or 0,
            'total_hltb_hours': round(total_hltb_hours),
            'health_score': health_score,
            'health_boxart_pct': health_boxart_pct,
            'health_desc_pct': health_desc_pct,
            'health_ra_pct': health_ra_pct,
            'missing_boxart': missing_boxart,
        }
    except Exception as e:
        logger.error(f"Error getting stats: {e}")
        return {
            'total_systems': 0,
            'total_games': 0,
            'scraped_games': 0,
            'missing_metadata': 0,
            'total_storage': 0,
            'completion_rate': 0,
            'completed_games': 0,
            'total_earned_achievements': 0,
            'total_available_achievements': 0,
            'total_hltb_hours': 0,
            'health_score': 0,
            'health_boxart_pct': 0,
            'health_desc_pct': 0,
            'health_ra_pct': 0,
            'missing_boxart': 0,
        }


def get_api_status():
    """
    Get API status based on configuration only (no network calls).
    Actual online checks are done asynchronously via /api/check-scraper/<scraper> endpoints.
    This makes Dashboard and Settings pages load instantly.
    """
    status = {
        'database': True,  # Always online since we got here
        'esde': True,  # Local filesystem - always available
        'rawg': False,
        'igdb': False,
        'retroachievements': False,
        'screenscraper': False,
        'tgdb': False,
    }

    # Load scraper settings for API keys
    scraper_settings = {}
    settings_path = os.path.join(config.BASE_DIR, 'data', 'scraper_settings.json')
    if os.path.exists(settings_path):
        try:
            with open(settings_path, 'r') as f:
                scraper_settings = json.load(f)
        except (json.JSONDecodeError, OSError):
            pass

    api_keys = scraper_settings.get('api_keys', {})
    enabled = scraper_settings.get('enabled', {})

    # Check if credentials are configured AND scraper is enabled
    # Disabled scrapers are excluded so they don't trigger offline warnings
    # This is instant - no network calls
    tgdb_key = api_keys.get('tgdb') or getattr(config, 'THEGAMESDB_API_KEY', '')
    tgdb_public = api_keys.get('tgdb_public') or getattr(config, 'THEGAMESDB_PUBLIC_API_KEY', '')
    status['tgdb'] = bool((tgdb_key or tgdb_public) and enabled.get('tgdb', True))

    igdb_client = api_keys.get('igdb_client_id') or getattr(config, 'IGDB_CLIENT_ID', '')
    igdb_secret = api_keys.get('igdb_client_secret') or getattr(config, 'IGDB_CLIENT_SECRET', '')
    status['igdb'] = bool(igdb_client and igdb_secret and enabled.get('igdb', True))

    ss_user = api_keys.get('screenscraper_username') or getattr(config, 'SCREENSCRAPER_USERNAME', '')
    ss_pass = api_keys.get('screenscraper_password') or getattr(config, 'SCREENSCRAPER_PASSWORD', '')
    status['screenscraper'] = bool(ss_user and ss_pass and enabled.get('screenscraper', True))

    rawg_key = api_keys.get('rawg') or api_keys.get('rawg_api_key') or getattr(config, 'RAWG_API_KEY', '')
    status['rawg'] = bool(rawg_key and enabled.get('rawg', True))

    ra_key = api_keys.get('ra_apikey') or getattr(config, 'RETROACHIEVEMENTS_API_KEY', '')
    ra_user = api_keys.get('ra_username') or getattr(config, 'RETROACHIEVEMENTS_USERNAME', '')
    status['retroachievements'] = bool(ra_key and ra_user)

    # Track which scrapers are disabled (have keys but user turned them off)
    status['_disabled'] = {
        'tgdb': bool((tgdb_key or tgdb_public) and not enabled.get('tgdb', True)),
        'igdb': bool((igdb_client and igdb_secret) and not enabled.get('igdb', True)),
        'screenscraper': bool((ss_user and ss_pass) and not enabled.get('screenscraper', True)),
        'rawg': bool(rawg_key and not enabled.get('rawg', True)),
    }

    return status


# Manufacturer mapping and size formatting moved to services.formatters
# (see import near top of file)


# =============================================================================
# MAIN PAGE ROUTES
# =============================================================================

@app.route('/')
def index():
    """Redirect to dashboard (or login if not authenticated)"""
    if not g.user:
        return redirect(url_for('auth.login'))
    return redirect(url_for('dashboard'))


# Job types that have a `resume_from_params` handler wired in `resume_job`.
# Kept alongside the dashboard loop so we never offer a Resume button that
# would 400 at the endpoint — jobs here skip Resume and only offer Dismiss.
_RESUMABLE_JOB_TYPES = {
    'bulk_scrape', 'ra_refresh', 'ra_sync', 'psn_refresh',
    'steam_sync', 'xbox_sync', 'museum_generate',
}


@app.route('/dashboard')
@login_required
def dashboard():
    """Main dashboard page"""
    try:
        stats = get_stats()
        api_status = get_api_status()

        # Get systems with ROM counts, scraped counts, and system_type (limit to 8 for dashboard)
        systems = query("""
            SELECT s.*, COUNT(g.id) AS rom_count,
                   SUM(CASE WHEN g.scraped = 1 THEN 1 ELSE 0 END) AS scraped_count
            FROM systems s
            LEFT JOIN games g ON s.id = g.system_id
            GROUP BY s.id
            HAVING rom_count > 0
            ORDER BY rom_count DESC
            LIMIT 8
        """)

        # Get recent games (recently added) with genre and release date
        recent_games = query("""
            SELECT g.id, g.title, g.boxart, g.boxart_3d, g.scraped,
                   g.genre, g.release_date, g.system_id, s.name AS system_name
            FROM games g
            JOIN systems s ON g.system_id = s.id
            ORDER BY g.id DESC
            LIMIT 5
        """)

        # Pass 41.9 — recently-viewed and continue-playing now key on the
        # per-user `user_game_views` table (migration 010). The previous
        # shape sorted on the global `games.last_viewed` column, which
        # leaked viewing history across users.
        user_id = g.user['id']

        recently_viewed = query("""
            SELECT g.id, g.title, g.boxart, g.boxart_3d, g.system_id,
                   g.completion_status, s.name AS system_name
            FROM user_game_views v
            JOIN games g ON g.id = v.game_id
            JOIN systems s ON g.system_id = s.id
            WHERE v.user_id = ?
            ORDER BY v.last_viewed DESC
            LIMIT 5
        """, (user_id,))

        # Get continue playing games (in-progress) — sorted by per-user
        # last_viewed so the panel reflects this user's recent in-progress
        # navigation, not whoever viewed last globally.
        continue_playing = query("""
            SELECT g.id, g.title, g.boxart, g.boxart_3d, g.system_id,
                   g.completion_status, g.playtime_estimate,
                   s.name AS system_name,
                   v.last_viewed
            FROM games g
            JOIN systems s ON g.system_id = s.id
            LEFT JOIN user_game_views v ON v.game_id = g.id AND v.user_id = ?
            WHERE g.completion_status = 'in_progress'
            ORDER BY v.last_viewed DESC NULLS LAST, g.title ASC
            LIMIT 5
        """, (user_id,))

        # Get recently scraped games (from scrape_history JSON)
        recently_scraped = query("""
            SELECT g.id, g.title, g.boxart, g.boxart_3d, g.scraped,
                   g.scrape_history, g.system_id, s.name AS system_name
            FROM games g
            JOIN systems s ON g.system_id = s.id
            WHERE g.scrape_history IS NOT NULL AND g.scrape_history != '' AND g.scrape_history != '[]'
            ORDER BY g.id DESC
            LIMIT 20
        """)
        # Sort by most recent scrape timestamp from history JSON
        scraped_with_dates = []
        for game in recently_scraped:
            try:
                history = json.loads(game.get('scrape_history', '[]'))
                if history:
                    last_entry = history[-1] if isinstance(history, list) else history
                    ts = last_entry.get('timestamp', last_entry.get('date', ''))
                    scraped_with_dates.append((ts, game))
            except (json.JSONDecodeError, AttributeError, TypeError):
                pass
        scraped_with_dates.sort(key=lambda x: x[0], reverse=True)
        recently_scraped = [g for _, g in scraped_with_dates[:5]]

        # Check for offline configured APIs (for warning banner)
        api_warning = False
        for key in ['rawg', 'igdb', 'screenscraper', 'tgdb']:
            if api_status.get(key):
                api_warning = True  # At least one configured; JS will check online status
                break

        # Check for recoverable (interrupted/queued) jobs
        from services.jobs.base import get_recoverable_jobs
        raw_recoverable = get_recoverable_jobs()
        recoverable_jobs = []
        for rj in raw_recoverable:
            params = rj.get('params', {})
            progress = rj.get('progress', {})
            jtype = rj['job_type']
            status = rj['status']

            # Determine if this job had real progress (interrupted mid-run)
            # vs was just queued (no work done yet)
            has_progress = progress.get('current', 0) > 0

            # Build human-readable description
            if jtype == 'bulk_scrape':
                sys_name = params.get('system_name', 'Unknown')
                if has_progress:
                    current = progress.get('current', 0)
                    total = progress.get('total', params.get('game_count', 0))
                    desc = f"{sys_name} bulk scrape was interrupted ({current}/{total} completed)"
                else:
                    count = params.get('game_count', len(params.get('game_ids', [])))
                    desc = f"{sys_name} bulk scrape was queued ({count} games)"
            elif jtype == 'ra_refresh':
                desc = "RetroAchievements refresh was interrupted" if has_progress else "RetroAchievements refresh was queued"
            elif jtype == 'ra_sync':
                desc = "RetroAchievements sync was interrupted" if has_progress else "RetroAchievements sync was queued"
            elif jtype == 'psn_refresh':
                desc = "PSN trophy refresh was interrupted" if has_progress else "PSN trophy refresh was queued"
            elif jtype == 'steam_sync':
                desc = "Steam achievement sync was interrupted" if has_progress else "Steam achievement sync was queued"
            elif jtype == 'xbox_sync':
                desc = "Xbox achievement sync was interrupted" if has_progress else "Xbox achievement sync was queued"
            elif jtype == 'hltb_bulk':
                desc = "HLTB bulk lookup was interrupted" if has_progress else "HLTB bulk lookup was queued"
            elif jtype == 'alt_titles_backfill':
                desc = "Alternate titles backfill was interrupted" if has_progress else "Alternate titles backfill was queued"
            else:
                desc = f"{jtype} job was {status}"

            # Mirror resume_job's handler_map so the UI never offers "Resume"
            # on a job type that would 400 at the endpoint.
            can_resume = jtype in _RESUMABLE_JOB_TYPES

            recoverable_jobs.append({
                'id': rj['id'],
                'job_type': jtype,
                'status': status,
                'description': desc,
                'can_resume': can_resume,
                'action_label': 'Resume' if has_progress else 'Start'
            })

        return render_template('dashboard.html',
                             stats=stats,
                             api_status=api_status,
                             systems=systems,
                             recent_games=recent_games,
                             recently_viewed=recently_viewed,
                             continue_playing=continue_playing,
                             recently_scraped=recently_scraped,
                             api_warning=api_warning,
                             recoverable_jobs=recoverable_jobs)
    except Exception as e:
        logger.error(f"Dashboard error: {e}", exc_info=True)
        return "An error occurred loading the dashboard. Check logs for details.", 500


@app.route('/api/jobs/resume/<int:job_id>', methods=['POST'])
@login_required
@handle_api_errors
def resume_job(job_id):
    """Resume an interrupted or queued job from the dashboard recovery banner.

    Pass 34.1 — routes via @handle_api_errors + success()/error(). The old
    implementation hand-rolled `jsonify` envelopes and returned `str(e)` in
    error bodies (SQL text / column names leak to the client). The decorator
    emits a stack trace to the log and a redacted 500 envelope.
    """
    from services.jobs.base import get_recoverable_jobs, persist_job_complete
    from services.jobs import bulk_scrape_job, ra_sync_job, ra_refresh_job, psn_refresh_job
    from services.jobs import steam_sync_job, xbox_sync_job, museum_generate_job

    # Find the job
    recoverable = get_recoverable_jobs()
    job = None
    for rj in recoverable:
        if rj['id'] == job_id:
            job = rj
            break

    if not job:
        return api_error('Job not found or already handled', 404)

    handler_map = {
        'bulk_scrape': bulk_scrape_job,
        'ra_refresh': ra_refresh_job,
        'ra_sync': ra_sync_job,
        'psn_refresh': psn_refresh_job,
        'steam_sync': steam_sync_job,
        'xbox_sync': xbox_sync_job,
        'museum_generate': museum_generate_job,
    }
    handler = handler_map.get(job['job_type'])
    if not handler or not hasattr(handler, 'resume_from_params'):
        return api_error(f"No handler for job type '{job['job_type']}'", 400)

    resumed = handler.resume_from_params(job['params'], progress=job.get('progress'))
    if not resumed:
        return api_error('Could not resume job — handler returned false', 500)
    # Mark the old interrupted/queued row as completed so it doesn't reappear
    persist_job_complete(job_id, status='completed')
    logger.info(f"Resumed {job['job_type']} job (DB id={job_id}) from dashboard")
    return api_success()


@app.route('/api/jobs/dismiss/<int:job_id>', methods=['POST'])
@login_required
@handle_api_errors
def dismiss_job_route(job_id):
    """Dismiss a recoverable job so it no longer appears on the dashboard."""
    from services.jobs.base import dismiss_job
    dismiss_job(job_id)
    return api_success()


@app.route('/api/random-game')
@login_required
@handle_api_errors
def random_game():
    """Get a random game from the library"""
    game = query("SELECT id FROM games ORDER BY RANDOM() LIMIT 1", one=True)
    if game:
        return api_success(game_id=game['id'])
    return api_error('No games in library', 404)



@app.route('/analytics')
@login_required
def analytics():
    """Collection analytics page with charts"""
    try:
        ctx = build_analytics_context()
        return render_template('analytics.html', **ctx)
    except Exception as e:
        logger.error(f"Analytics error: {e}", exc_info=True)
        return "An error occurred loading analytics. Check logs for details.", 500


CHANGELOG_PAGE_SIZE = 20  # entries rendered per "Load More" slice (Pass 49.1)


@app.route('/changelog')
def changelog():
    """Changelog page showing version history (paginated — Pass 49.1)"""
    import yaml
    from flask_babel import get_locale
    data_dir = os.path.join(config.BUNDLE_DIR, 'data')
    with open(os.path.join(data_dir, 'changelog.yaml'), 'r') as f:
        entries = yaml.safe_load(f)
    # Per-locale changelog (Pass 43.6): recent entries are translated per
    # language in data/changelog.<locale>.yaml; older history stays English.
    # We swap in any translated entry by version, keeping the full timeline —
    # long-form content is translated as whole files, not via the gettext
    # catalog (see docs/specs/i18n.md §9).
    locale = str(get_locale())
    loc_path = os.path.join(data_dir, f'changelog.{locale}.yaml')
    if os.path.exists(loc_path):
        with open(loc_path, 'r') as f:
            translated = {e['version']: e for e in (yaml.safe_load(f) or [])}
        if translated:
            entries = [translated.get(e['version'], e) for e in entries]
    # Pagination (Pass 49.1): the changelog has 788+ entries; rendering them all
    # server-side every visit cost ~550 ms. Slice AFTER the version-merge so the
    # translated recent entries still win, then render one page; a "Load More"
    # button fetches subsequent slices as HTML partials (?partial=1&offset=).
    try:
        offset = max(0, int(request.args.get('offset', 0)))
    except (TypeError, ValueError):
        offset = 0
    page = entries[offset:offset + CHANGELOG_PAGE_SIZE]
    has_more = offset + CHANGELOG_PAGE_SIZE < len(entries)
    next_offset = offset + CHANGELOG_PAGE_SIZE
    # Pass literal .html template names (not a variable) so Flask's autoescape
    # and the semgrep unescaped-template-extension rule both see the safe
    # extension statically — keep the kwargs DRY in one dict.
    ctx = dict(changelog=page, has_more=has_more, next_offset=next_offset)
    if request.args.get('partial'):
        return render_template('_changelog_entries.html', **ctx)
    return render_template('changelog.html', **ctx)


@app.route('/help')
def help_page():
    """Help and documentation page"""
    from flask_babel import get_locale
    # Per-locale manual (Pass 43.6): render templates/help.<locale>.html when it
    # exists, else fall back to the English help.html. The manual is translated
    # as a whole file per language, not via the gettext catalog (docs/specs/i18n.md §9).
    locale = str(get_locale())
    return render_template([f'help.{locale}.html', 'help.html'])


@app.route('/setup')
def setup_page():
    """First-time setup wizard"""
    from platform_utils import PLATFORM_NAME
    # Redirect away if setup is already complete
    user_settings = settings_manager.load_settings()
    has_rom_path = bool(user_settings.get('rom_path') or getattr(config, 'ROM_PATH', ''))
    setup_done = user_settings.get('setup_completed', False)
    if setup_done or has_rom_path:
        return redirect(url_for('dashboard'))
    return render_template('setup.html', setup_mode=True, platform_name=PLATFORM_NAME)


@app.route('/api/setup/browse-folders', methods=['POST'])
@handle_api_errors
def setup_browse_folders():
    """Browse filesystem folders during setup (no auth required, only works before setup is complete).

    Pass 34.1: routed through success()/error(); @handle_api_errors owns
    the unexpected-exception 500 path. No behavioural change.
    """
    user_settings_check = settings_manager.load_settings()
    has_rom_path = bool(user_settings_check.get('rom_path') or getattr(config, 'ROM_PATH', ''))
    setup_done = user_settings_check.get('setup_completed', False)
    if setup_done or has_rom_path:
        return api_error('Setup has already been completed', 403)

    data = request.get_json() or {}
    requested_path = data.get('path', '')

    # Default to user's home directory
    home_dir = os.path.expanduser('~')
    current_path = requested_path if requested_path else home_dir

    # Resolve and validate
    try:
        current_path = os.path.realpath(current_path)
    except (OSError, ValueError):
        current_path = home_dir

    if not os.path.exists(current_path) or not os.path.isdir(current_path):
        current_path = home_dir

    # Parent path (allow navigating up to filesystem root)
    parent_path = None
    parent = os.path.dirname(current_path)
    if parent != current_path:  # Not at filesystem root
        parent_path = parent

    folders = []
    try:
        for item in sorted(os.listdir(current_path)):
            if item.startswith('.'):
                continue
            item_path = os.path.join(current_path, item)
            if not os.path.isdir(item_path):
                continue
            try:
                contents = os.listdir(item_path)
                file_count = sum(1 for f in contents if os.path.isfile(os.path.join(item_path, f)))
                subfolder_count = sum(1 for f in contents if os.path.isdir(os.path.join(item_path, f)))
            except PermissionError:
                file_count = 0
                subfolder_count = 0
            folders.append({
                'name': item,
                'path': item_path,
                'file_count': file_count,
                'subfolder_count': subfolder_count
            })
    except PermissionError:
        return api_error('Permission denied', 403)

    return api_success(
        base_path=home_dir,
        current_path=current_path,
        parent_path=parent_path,
        folders=folders,
        is_root=parent == current_path,
    )


@app.route('/api/setup', methods=['POST'])
@handle_api_errors
def setup_api():
    """Process setup wizard form submission.

    Pass 34.1: routed through success()/error(); @handle_api_errors wraps
    unexpected-exception logging.
    """
    user_settings_check = settings_manager.load_settings()
    has_rom_path = bool(user_settings_check.get('rom_path') or getattr(config, 'ROM_PATH', ''))
    setup_done = user_settings_check.get('setup_completed', False)
    if setup_done or has_rom_path:
        return api_error('Setup has already been completed', 403)

    data = request.get_json()
    if not data:
        return api_error('No data provided', 400)

    # Require license acceptance
    if not data.get('license_accepted'):
        return api_error('You must accept the license and terms before completing setup', 400)

    # 1. Create admin account if username/password provided
    admin_username = data.get('admin_username', '').strip()
    admin_password = data.get('admin_password', '').strip()
    if admin_username and admin_password:
        import sqlite3 as _sqlite3
        conn = _sqlite3.connect(config.DB_PATH)
        cursor = conn.cursor()
        # Update existing admin or create new one
        cursor.execute("SELECT id FROM users WHERE role = 'admin' LIMIT 1")
        admin = cursor.fetchone()
        new_hash = hash_password(admin_password)
        if admin:
            cursor.execute("""
                UPDATE users SET username = ?, password_hash = ?, force_password_change = 0
                WHERE id = ?
            """, (admin_username, new_hash, admin[0]))
        else:
            cursor.execute("""
                INSERT INTO users (username, display_name, password_hash, role, force_password_change)
                VALUES (?, ?, ?, 'admin', 0)
            """, (admin_username, admin_username, new_hash))
            admin_id = cursor.lastrowid
            cursor.execute("INSERT INTO user_settings (user_id) VALUES (?)", (admin_id,))
        conn.commit()
        conn.close()

    # 2. Save paths to settings
    user_settings = settings_manager.load_settings()
    if data.get('rom_path'):
        user_settings['rom_path'] = data['rom_path']
    if data.get('esde_gamelists_path'):
        user_settings['esde_gamelists_path'] = data['esde_gamelists_path']
    if data.get('esde_media_path'):
        user_settings['esde_downloaded_media_path'] = data['esde_media_path']

    # 3. Save API keys to scraper_settings.json
    scraper_path = os.path.join(config.BASE_DIR, 'data', 'scraper_settings.json')
    scraper_settings = {}
    if os.path.exists(scraper_path):
        try:
            with open(scraper_path, 'r') as f:
                scraper_settings = json.load(f)
        except (json.JSONDecodeError, OSError):
            pass

    api_keys = scraper_settings.get('api_keys', {})
    key_mapping = {
        'tgdb_key': 'tgdb',
        'tgdb_public_key': 'tgdb_public',
        'igdb_client_id': 'igdb_client_id',
        'igdb_client_secret': 'igdb_client_secret',
        'rawg_key': 'rawg',
        'ss_username': 'screenscraper_username',
        'ss_password': 'screenscraper_password',
        'ss_devid': 'screenscraper_devid',
        'ss_devpassword': 'screenscraper_devpassword',
        'ra_username': 'ra_username',
        'ra_apikey': 'ra_apikey',
    }
    for form_key, settings_key in key_mapping.items():
        val = data.get(form_key, '').strip()
        if val:
            api_keys[settings_key] = val

    scraper_settings['api_keys'] = api_keys
    if 'priority' not in scraper_settings:
        scraper_settings['priority'] = ['esde', 'screenscraper', 'rawg', 'tgdb', 'igdb']
    if 'enabled' not in scraper_settings:
        scraper_settings['enabled'] = {'esde': True, 'tgdb': True, 'igdb': True, 'rawg': True, 'screenscraper': True}

    try:
        atomic_write_json(scraper_path, scraper_settings)
    except Exception as e:
        logger.error(f"Error saving scraper settings during setup: {e}")

    # 4. Save timezone to admin's user_settings if provided
    if data.get('timezone'):
        import sqlite3 as _sqlite3_tz
        tz_conn = _sqlite3_tz.connect(config.DB_PATH)
        tz_cursor = tz_conn.cursor()
        tz_cursor.execute("SELECT id FROM users WHERE role = 'admin' LIMIT 1")
        tz_admin = tz_cursor.fetchone()
        if tz_admin:
            tz_cursor.execute("UPDATE user_settings SET timezone = ? WHERE user_id = ?",
                              (data['timezone'], tz_admin[0]))
        tz_conn.commit()
        tz_conn.close()

    # 5. Mark setup as completed
    user_settings['setup_completed'] = True
    # Pass 59.85: a failed (or, since 59.42, refused) save is not "complete".
    if not settings_manager.save_settings(user_settings):
        return api_error('Could not save your settings. Check that the data folder is writable, then try again.', 500)

    # Update runtime config paths
    app.config['ROM_PATH'] = get_rom_path()

    return api_success(message='Setup complete!')


@app.route('/api/timezones')
def api_timezones():
    """Return all IANA timezones grouped by region for timezone picker"""
    grouped = {}
    for tz_name in sorted(available_timezones()):
        # Skip non-region timezones (e.g., 'EST', 'UTC') except UTC itself
        if '/' not in tz_name:
            if tz_name == 'UTC':
                grouped.setdefault('UTC', []).append(tz_name)
            continue
        region = tz_name.split('/')[0]
        grouped.setdefault(region, []).append(tz_name)

    # Sort each group
    for region in grouped:
        grouped[region].sort()

    return jsonify({'timezones': grouped})


# =============================================================================
# DATABASE INITIALIZATION
# =============================================================================


# =============================================================================
# ENSURE DATABASE IS INITIALIZED (runs on import)
# =============================================================================
# This ensures database migrations run even when Flask auto-reloads.
# Order matters: ensure_user_tables() creates the `users` table and seeds the
# default admin; init_database() then runs migrations that depend on those
# rows for backfill (e.g. 005_collections_owner_id, 006_per_user_platform_tokens).
# Reversing this order silently stamps user_version past those migrations on
# a fresh install, leaving owner_id columns NULL and rows invisible to the
# owner-scoped queries introduced in Pass 27.
ensure_user_tables()
init_database()

# Pass 44 — seed emulator registry on startup (idempotent).  Runs after
# init_database() so migration 010's tables exist; INSERT OR IGNORE on
# emulators.name and (system_id, emulator_id) makes re-runs no-ops.
try:
    import sqlite3 as _sqlite
    from services.emulator_seeder import seed_emulators_from_file
    _seed_path = os.path.join(os.path.dirname(__file__), 'data', 'emulator_seeds.json')
    if os.path.exists(_seed_path):
        _seed_conn = _sqlite.connect(config.DB_PATH)
        try:
            seed_emulators_from_file(_seed_conn, _seed_path)
        finally:
            _seed_conn.close()
except Exception as _e:
    logger.error(f"Emulator seed failed: {_e}", exc_info=True)

# Pass 41.1.C — surface accounts whose password_hash is below the current
# OWASP floor (PBKDF2_ITERATIONS).  needs_rehash() only fires on the next
# successful login, so dormant accounts retain pre-bump 100k-iteration
# hashes indefinitely.  An operator can act on the warning by forcing a
# password change in User Management.
try:
    from services.auth import count_stale_password_hashes
    _stale_hash_count = count_stale_password_hashes()
    if _stale_hash_count:
        logger.warning(
            "%d active user(s) have password hashes below the current "
            "OWASP floor; force a password change for any idle accounts "
            "to upgrade now (User Management → Reset Password).",
            _stale_hash_count,
        )
except Exception as _e:
    logger.error(f"Stale-password-hash sweep failed: {_e}")

# Mark interrupted jobs for dashboard recovery (no silent auto-resume)
# In debug mode, Flask's reloader spawns a child process (worker) that handles
# HTTP requests. Module-level code runs in BOTH processes. We must only mark
# jobs in the worker process so the dashboard reads correct state.
_is_worker = os.environ.get('WERKZEUG_RUN_MAIN') == 'true' or not config.DEBUG_MODE
if _is_worker:
    try:
        from services.jobs.base import mark_jobs_interrupted, sweep_old_job_history
        _interrupted = mark_jobs_interrupted()
        if _interrupted:
            logger.info(f"Marked {len(_interrupted)} interrupted job(s) for dashboard recovery")
        sweep_old_job_history()
    except Exception as _e:
        logger.warning(f"Could not mark interrupted jobs: {_e}")

    # Graceful-shutdown handler: on SIGTERM/SIGINT, ask running jobs to
    # cancel and give them a few seconds to flush persisted state.
    # Without this, jobs killed mid-iteration leave their last few minutes
    # of progress unpersisted, so the dashboard recovery banner would
    # rewind the user further than necessary on next start.
    try:
        import signal as _signal
        from services.jobs.base import request_shutdown as _request_job_shutdown

        def _handle_shutdown_signal(signum, frame):
            try:
                logger.info(f"Received signal {signum}; draining running jobs before exit")
                _request_job_shutdown(timeout=5.0)
            finally:
                # Restore default handler and re-raise so the process exits
                # the same way it would have without our handler.
                _signal.signal(signum, _signal.SIG_DFL)
                os.kill(os.getpid(), signum)

        _signal.signal(_signal.SIGTERM, _handle_shutdown_signal)
        _signal.signal(_signal.SIGINT, _handle_shutdown_signal)
    except (ValueError, OSError) as _e:
        # signal.signal() raises ValueError when called outside the main
        # thread (e.g. under WSGI workers that import the app on a worker
        # thread).  In that case we silently skip — the WSGI host owns
        # signal handling.
        logger.debug(f"Could not install shutdown signal handlers: {_e}")

# =============================================================================
# MAIN ENTRY POINT
# =============================================================================

if __name__ == '__main__':
    import errno
    from platform_utils import get_local_ip, IS_WINDOWS, IS_MACOS
    from server_port import resolve_server_port

    # Resolve the bind port first — before directory creation, logging setup
    # and the GPU warm-up below — so a bad value costs a second, not a minute.
    # use_saved=True adds the Settings-page `server_port` tier, which is safe
    # here and only here: this runs after imports, so reading the settings
    # layer (which imports config) is no longer a cycle.
    try:
        port = resolve_server_port(default=config.SERVER_PORT, use_saved=True)
    except ValueError as _port_err:
        print(f'ERROR: {_port_err}', file=sys.stderr)
        sys.exit(1)

    def _die_port_in_use(port: int) -> None:
        """Print a cross-platform diagnostic for EADDRINUSE and exit."""
        if IS_WINDOWS:
            find_cmd = f'netstat -ano | findstr :{port}'
            kill_cmd = 'taskkill /PID <PID> /F'
        elif IS_MACOS:
            find_cmd = f'lsof -nP -iTCP:{port} -sTCP:LISTEN'
            kill_cmd = 'kill <PID>'
        else:  # Linux / other Unix
            find_cmd = f"ss -ltnp | grep ':{port}'"
            kill_cmd = 'kill <PID>'

        msg = [
            '',
            '=' * 60,
            f'  ERROR: Port {port} is already in use',
            '=' * 60,
            '',
            f'  Another process on this machine is bound to port {port}.',
            '  RetroDB cannot start until that process releases the port',
            '  (or you switch RetroDB to a different port).',
            '',
            '  Find the process:',
            f'    {find_cmd}',
            '',
            '  Stop it (substitute the PID from the line above):',
            f'    {kill_cmd}',
            '',
            '  Or move RetroDB to a free port (e.g. 5001) and restart:',
            '    Settings -> System -> Server Port, or PORT=5001 in the',
            '    environment, which overrides the saved setting.',
            '',
            '=' * 60,
            '',
        ]
        print('\n'.join(msg), file=sys.stderr)
        sys.exit(1)

    # Flask debug mode runs a reloader that spawns a subprocess
    # Only log startup info in the main process (when WERKZEUG_RUN_MAIN is set)
    # or when not in debug mode at all
    is_reloader_process = os.environ.get('WERKZEUG_RUN_MAIN') == 'true'

    if is_reloader_process or not config.DEBUG_MODE:
        logger.info("=" * 60)
        logger.info(f"Starting {config.APP_NAME} v{config.APP_VERSION}")
        logger.info("=" * 60)

    # Create required directories (always, in both processes is fine)
    for subdir in ['boxart', 'boxart_3d', 'screenshots', 'systems', 'ratings', 'fanart', 'videos', 'manuals', 'avatars', 'controllers']:
        os.makedirs(os.path.join(config.IMAGE_PATH, subdir), exist_ok=True)

    # Create data directory for settings (anchored to BASE_DIR — see config.py;
    # writable user-data root, not the bundled-assets root).
    os.makedirs(os.path.join(config.BASE_DIR, 'data'), exist_ok=True)

    # Create logs directory (BASE_DIR for the same reason).
    os.makedirs(os.path.join(config.BASE_DIR, 'logs'), exist_ok=True)

    # Initialize file-based logging (only once)
    if is_reloader_process or not config.DEBUG_MODE:
        log_manager.setup_all_logging()
        logger.info("File-based logging initialized")

    # Eager GPU warm-up on main thread — MIOpen handles are NOT thread-safe,
    # so the find/benchmark phase must complete here before Flask worker
    # threads try to run inference.  Only in the reloader child process
    # (the one that actually serves requests).
    if is_reloader_process or not config.DEBUG_MODE:
        try:
            from services.image_utils import _get_upscaler
            _get_upscaler()  # triggers init + warm-up on main thread
        except Exception as _upscaler_init_err:
            # Pass 46.4 — _init_upscaler logs its own warning on the normal
            # failure path, but if the *logging* subsystem itself is broken
            # the warning never reaches the operator and ESRGAN silently
            # disables. Print to stderr so even a log-subsystem outage is
            # visible at startup.
            import traceback
            print(f"WARN: ESRGAN init failed: {_upscaler_init_err}", file=sys.stderr)
            traceback.print_exc()

    # Pass 50.1 — start the PSN session keep-alive (daily background refresh so
    # the ~2-month refresh-token window never lapses while the app runs). Only in
    # the serving worker, never the reloader parent (and never on a bare import,
    # since this is inside `if __name__ == '__main__'`).
    if is_reloader_process or not config.DEBUG_MODE:
        try:
            from services.jobs.psn_keepalive import start_psn_keepalive_thread
            start_psn_keepalive_thread(app)
        except Exception as _ka_err:
            logger.warning(f"PSN keep-alive thread failed to start: {_ka_err}")

    host = config.SERVER_HOST
    local_ip = get_local_ip()

    if config.DEBUG_MODE:
        # Development: use Flask dev server with auto-reload
        print("\n  * Running in DEBUG mode (Flask dev server)")
        print(f"  * Local:   http://localhost:{port}")
        print(f"  * Network: http://{local_ip}:{port}\n")
        try:
            app.run(debug=True, host=host, port=port)
        except OSError as _bind_err:
            if _bind_err.errno == errno.EADDRINUSE:
                _die_port_in_use(port)
            raise
    else:
        # Production: use Waitress WSGI server
        try:
            from waitress import serve
        except ImportError:
            print("ERROR: Waitress is not installed. Install with: pip install waitress")
            print("       Or set DEBUG_MODE = True in config.py to use the Flask dev server.")
            sys.exit(1)

        print()
        print("=" * 58)
        print(f"  {config.APP_NAME} v{config.APP_VERSION}")
        print("=" * 58)
        print("  Server:  Waitress (production)")
        print(f"  Local:   http://localhost:{port}")
        print(f"  Network: http://{local_ip}:{port}")
        print("=" * 58)
        print("  Press Ctrl+C to stop the server")
        print()

        # A single /games or /dashboard load fires 20+ parallel XHRs (card
        # data, fanart, per-row API lookups). 4 threads meant most of those
        # queued behind each other and every page load flooded the log with
        # "Task queue depth" warnings. 16 gives comfortable headroom for a
        # local single-user / small-household workload without being wasteful.
        # Standalone bundle only: open the browser shortly after the bind.
        # A source install gets this from start.sh / start.command / start.bat
        # or scripts/retrodb_launcher.py, none of which ship in the bundle —
        # and only the server knows the resolved port (Pass 59.10).
        if getattr(sys, 'frozen', False):
            import threading
            import webbrowser
            _opener = threading.Timer(
                2.0, lambda: webbrowser.open(f'http://localhost:{port}')
            )
            _opener.daemon = True  # never hold up Ctrl+C
            _opener.start()

        try:
            serve(app, host=host, port=port, threads=16)
        except OSError as _bind_err:
            if _bind_err.errno == errno.EADDRINUSE:
                _die_port_in_use(port)
            raise