app.py

import csv
import io
import os
import re
import math
import hashlib
import click

try:
    from dotenv import load_dotenv
    load_dotenv()
except ImportError:
    pass

from flask import Flask, render_template, request, redirect, session, url_for, make_response, flash, jsonify, abort
from datetime import date, datetime, timedelta, timezone
from calendar import monthrange
from zoneinfo import ZoneInfo, ZoneInfoNotFoundError
from db import get_db, close_db, IntegrityError, USE_PG
from extensions import csrf, limiter, bcrypt
from authlib.integrations.flask_client import OAuth
from utils.dates import (
    get_today, get_current_day_range, get_current_week_range,
    get_current_month_range, get_previous_month_range,
)

# ---------------------------------------------------------------------------
# App initialisation
# ---------------------------------------------------------------------------

app = Flask(__name__)
app.teardown_appcontext(close_db)

# Keep users logged in for a full year — no surprise sign-outs on mobile PWA
app.config['PERMANENT_SESSION_LIFETIME'] = timedelta(days=365)
app.config['SESSION_COOKIE_SAMESITE'] = 'Lax'
app.config['SESSION_COOKIE_HTTPONLY'] = True

@app.after_request
def add_security_headers(response):
    """Add security headers to every response to mitigate XSS, Clickjacking, and sniffing."""
    response.headers['X-Content-Type-Options'] = 'nosniff'
    response.headers['X-Frame-Options'] = 'SAMEORIGIN'
    response.headers['X-XSS-Protection'] = '1; mode=block'
    response.headers['Referrer-Policy'] = 'strict-origin-when-cross-origin'
    response.headers['Permissions-Policy'] = 'geolocation=(), microphone=(self), camera=()'
    
    # Only enforce HSTS in production/secure contexts so we don't break local HTTP dev
    if os.environ.get('FLASK_ENV') == 'production' or request.is_secure:
        response.headers['Strict-Transport-Security'] = 'max-age=31536000; includeSubDomains'
        
    # Allow local development and common CDNs for scripts/styles
    csp = (
        "default-src 'self'; "
        "connect-src 'self' https: ws: wss:; "
        "script-src 'self' 'unsafe-inline' 'unsafe-eval' https://cdn.jsdelivr.net https://code.jquery.com https://kit.fontawesome.com https://cdnjs.cloudflare.com; "
        "style-src 'self' 'unsafe-inline' https://cdn.jsdelivr.net https://fonts.googleapis.com https://cdnjs.cloudflare.com; "
        "font-src 'self' https://fonts.gstatic.com https://kit-free.fontawesome.com https://cdn.jsdelivr.net https://cdnjs.cloudflare.com data:; "
        "img-src 'self' data: https: blob:;"
    )
    response.headers['Content-Security-Policy'] = csp
    return response

@app.before_request
def check_session_timeout():
    """Sessions are permanent (365 days). No inactivity timeout — mobile PWA users
    should never be surprised by a sign-out. The cookie expiry handles cleanup."""
    if "user_id" in session:
        # Ensure every session is flagged as permanent so the browser keeps the
        # cookie across restarts (especially important for Android/iOS PWA installs).
        session.permanent = True


# SECRET_KEY must come from the environment in production.
# Fail loudly at startup if it is missing or still set to the known placeholder.
_KNOWN_INSECURE_KEYS = {
    "super-secret-key-change-this",
    "dev-secret-key-change-in-production",
    "changeme",
    "",
}
_secret = os.environ.get("SECRET_KEY", "")
if not _secret or _secret in _KNOWN_INSECURE_KEYS:
    # Allow insecure key only in explicit testing/dev modes
    if os.environ.get("FLASK_ENV") in ("production", None) and not os.environ.get("TESTING"):
        raise RuntimeError(
            "[RunRush] SECRET_KEY is missing or is a known insecure placeholder. "
            "Set a strong SECRET_KEY environment variable before starting the app."
        )
    # Fallback for local dev / test runs
    _secret = _secret or "dev-secret-key-change-in-production"
app.secret_key = _secret

app.config['DEV_LOGIN_ENABLED'] = (
    os.environ.get("FLASK_ENV") == "development" and 
    os.environ.get("DEV_LOGIN_ENABLED", "false").lower() == "true"
)

# Wire up Flask extensions
csrf.init_app(app)
limiter.init_app(app)

from flask_wtf.csrf import CSRFError
from flask import jsonify

@app.errorhandler(CSRFError)
def handle_csrf_error(e):
    return jsonify({"error": e.description}), 400
bcrypt.init_app(app)

oauth = OAuth(app)
if os.environ.get("GOOGLE_CLIENT_ID") and os.environ.get("GOOGLE_CLIENT_SECRET"):
    oauth.register(
        name='google',
        server_metadata_url='https://accounts.google.com/.well-known/openid-configuration',
        client_id=os.environ.get("GOOGLE_CLIENT_ID"),
        client_secret=os.environ.get("GOOGLE_CLIENT_SECRET"),
        client_kwargs={
            'scope': 'openid email profile'
        }
    )

DEFAULT_WEIGHT = 0.0   # used if user hasn't set weight yet

# Temporary announcement banner — auto-expires server-side after this date.
# Remove this constant (and the template logic) once the date has passed.
ANNOUNCEMENT_EXPIRES = date(2026, 9, 20)


def _apply_startup_schema_updates():
    """Ensure essential tables and defaults exist in production without requiring manual shell access."""
    if os.environ.get("TESTING"):
        return
    try:
        conn = get_db()
        # 1. monthly_report_preferences
        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_report_preferences (
                user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
                popup_enabled INTEGER NOT NULL DEFAULT 1,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        # 2. monthly_summary_deliveries
        if USE_PG:
            conn.execute("""
                CREATE TABLE IF NOT EXISTS monthly_summary_deliveries (
                    id SERIAL PRIMARY KEY,
                    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                    year INTEGER NOT NULL,
                    month INTEGER NOT NULL,
                    status TEXT NOT NULL DEFAULT 'PENDING',
                    error TEXT,
                    sent_at TIMESTAMP,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(user_id, year, month)
                )
            """)
        else:
            conn.execute("""
                CREATE TABLE IF NOT EXISTS monthly_summary_deliveries (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    user_id INTEGER NOT NULL,
                    year INTEGER NOT NULL,
                    month INTEGER NOT NULL,
                    status TEXT NOT NULL DEFAULT 'PENDING',
                    error TEXT,
                    sent_at TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    UNIQUE(user_id, year, month),
                    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
                )
            """)
        # 3. Ensure indexes
        try:
            conn.execute("CREATE INDEX IF NOT EXISTS idx_msd_user ON monthly_summary_deliveries(user_id)")
            conn.execute("CREATE INDEX IF NOT EXISTS idx_msd_status ON monthly_summary_deliveries(status)")
        except Exception:
            pass
        # 4. Auto-enable email summaries for Google users
        try:
            conn.execute("""
                UPDATE users 
                SET email = COALESCE(NULLIF(email, ''), google_email),
                    email_monthly_summary = 1,
                    email_weekly_summary = 1
                WHERE (google_id IS NOT NULL OR google_email IS NOT NULL)
            """)
            conn.commit()
        except Exception:
            pass
        conn.close()
    except Exception as e:
        print(f"[RunRush DB] Startup schema check warning: {e}", flush=True)


with app.app_context():
    _apply_startup_schema_updates()


# ----------------- DB HELPERS -----------------



@app.cli.command("init-db")
def init_db_command():
    """Initialize the database (create tables)."""
    init_db()

def init_db():
    conn = get_db()

    if USE_PG:
        print("[RunRush DB] Connected to PostgreSQL. Persistent storage is ACTIVE.")
        # ---- PostgreSQL DDL (all columns from the start) ----
        conn.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id SERIAL PRIMARY KEY,
                username TEXT UNIQUE NOT NULL,
                pin TEXT NOT NULL,
                display_name TEXT,
                profile_emoji TEXT DEFAULT '🏃🏻',
                weight REAL,
                theme TEXT,
                height REAL,
                last_login TEXT,
                role TEXT DEFAULT 'user',
                status TEXT DEFAULT 'active',
                google_id TEXT UNIQUE,
                google_avatar_url TEXT
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS runs (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id),
                date TEXT NOT NULL,
                distance_km REAL NOT NULL,
                time_min REAL NOT NULL,
                pace REAL NOT NULL,
                calories REAL NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                insight TEXT
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS edit_history (
                id SERIAL PRIMARY KEY,
                run_id INTEGER NOT NULL REFERENCES runs(id),
                user_id INTEGER NOT NULL REFERENCES users(id),
                field_name TEXT NOT NULL,
                old_value TEXT,
                new_value TEXT,
                edited_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS activity_logs (
                id SERIAL PRIMARY KEY,
                user_id INTEGER REFERENCES users(id),
                action TEXT NOT NULL,
                details TEXT,
                timestamp TEXT NOT NULL
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS admin_notes (
                id SERIAL PRIMARY KEY,
                target_user_id INTEGER NOT NULL REFERENCES users(id),
                author_id INTEGER NOT NULL REFERENCES users(id),
                note TEXT NOT NULL,
                timestamp TEXT NOT NULL
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS badges (
                id SERIAL PRIMARY KEY,
                key TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                description TEXT,
                icon_url TEXT,
                criteria_type TEXT NOT NULL,
                criteria_value REAL NOT NULL
            )
        """)

        conn.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS google_id TEXT UNIQUE")
        conn.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS google_avatar_url TEXT")
        conn.execute("ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_emoji TEXT DEFAULT '🏃🏻'")
        conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_users_google_id ON users(google_id)")

        # PIN recovery token table (PG)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS pin_resets (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id),
                code_hash TEXT NOT NULL,
                expires_at TEXT NOT NULL,
                attempts INTEGER DEFAULT 0,
                created_at TEXT NOT NULL,
                used_at TEXT
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_badges (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                badge_id INTEGER NOT NULL REFERENCES badges(id) ON DELETE RESTRICT,
                run_id INTEGER REFERENCES runs(id) ON DELETE SET NULL,
                unlocked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, badge_id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_stats (
                id SERIAL PRIMARY KEY,
                user_id INTEGER UNIQUE NOT NULL REFERENCES users(id),
                total_distance_km REAL DEFAULT 0.0,
                current_streak INTEGER DEFAULT 0,
                best_streak INTEGER DEFAULT 0,
                last_activity_date TEXT,
                updated_at TEXT
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS friends (
                id SERIAL PRIMARY KEY,
                follower_id INTEGER NOT NULL REFERENCES users(id),
                followed_id INTEGER NOT NULL REFERENCES users(id),
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE (follower_id, followed_id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS run_likes (
                id SERIAL PRIMARY KEY,
                run_id INTEGER NOT NULL REFERENCES runs(id) ON DELETE CASCADE,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE (run_id, user_id)
            )
        """)
        conn.execute("CREATE INDEX IF NOT EXISTS idx_run_likes_run_id ON run_likes(run_id)")

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_weekly_goals (
                user_id INTEGER PRIMARY KEY REFERENCES users(id),
                goal_km REAL NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_goals (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                year INTEGER NOT NULL,
                month INTEGER NOT NULL,
                target_km REAL NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, year, month)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS web_push_subscriptions (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                endpoint TEXT NOT NULL UNIQUE,
                p256dh TEXT NOT NULL,
                auth TEXT NOT NULL,
                expiration_time DOUBLE PRECISION,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_web_push_subscriptions_user_id "
            "ON web_push_subscriptions(user_id)"
        )

        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_summary_deliveries (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                year INTEGER NOT NULL,
                month INTEGER NOT NULL,
                status TEXT NOT NULL DEFAULT 'PENDING',
                error TEXT,
                sent_at TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, year, month)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_report_views (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                year INTEGER NOT NULL,
                month INTEGER NOT NULL,
                status TEXT NOT NULL DEFAULT 'DISMISSED_DAY_1',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, year, month)
            )
        """)
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_monthly_report_views_user "
            "ON monthly_report_views(user_id)"
        )
        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_report_preferences (
                user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
                popup_enabled INTEGER NOT NULL DEFAULT 1,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_dashboard_layout (
                user_id INTEGER PRIMARY KEY REFERENCES users(id),
                layout_json TEXT NOT NULL
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS challenges (
                id SERIAL PRIMARY KEY,
                key TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                description TEXT NOT NULL,
                target_value REAL NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_challenge_progress (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                challenge_id INTEGER NOT NULL REFERENCES challenges(id) ON DELETE RESTRICT,
                current_progress REAL DEFAULT 0.0,
                completed_at TIMESTAMP,
                UNIQUE(user_id, challenge_id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_goals (
                id SERIAL PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id),
                goal_type TEXT NOT NULL,
                target_distance_km REAL NOT NULL,
                target_date TEXT NOT NULL,
                days_per_week INTEGER NOT NULL,
                status TEXT NOT NULL DEFAULT 'active',
                created_at TEXT NOT NULL,
                dashboard_pinned BOOLEAN DEFAULT false
            )
        """)

        # Add columns that may not exist (PG migration — uses IF NOT EXISTS, safe to re-run)
        for pg_migration in [
            "ALTER TABLE runs ADD COLUMN IF NOT EXISTS run_type TEXT DEFAULT 'easy'",
            "ALTER TABLE runs ADD COLUMN IF NOT EXISTS notes TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS email TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_weekly_summary INTEGER DEFAULT 1",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS email_monthly_summary INTEGER DEFAULT 1",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS google_id TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS google_email TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS height REAL",
            # Weather / location columns
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS home_city TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS home_latitude REAL",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS home_longitude REAL",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS failed_login_attempts INTEGER DEFAULT 0",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS locked_until TEXT",
            "ALTER TABLE runs ADD COLUMN IF NOT EXISTS weather_temp REAL",
            "ALTER TABLE runs ADD COLUMN IF NOT EXISTS weather_humidity INTEGER",
            "ALTER TABLE runs ADD COLUMN IF NOT EXISTS weather_wind_kph REAL",
            "ALTER TABLE runs ADD COLUMN IF NOT EXISTS weather_condition TEXT",
            "ALTER TABLE runs ADD COLUMN IF NOT EXISTS weather_emoji TEXT",
            # PIN recovery columns
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS recovery_email TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS recovery_email_verified INTEGER DEFAULT 0",
            # Public profile columns
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS next_race TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar_image BYTEA",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS avatar_mime_type TEXT",
            # Onboarding redesign columns
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS experience TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS primary_goal TEXT",
            "ALTER TABLE users ADD COLUMN IF NOT EXISTS frequency TEXT",
        ]:
            try:
                conn.execute(pg_migration)
            except Exception:
                pass




    else:
        print(f"DEBUG: init_db using conn {conn._conn}", flush=True)
        print("[RunRush DB WARNING] Connected to SQLite (runs.db). Data is EPHEMERAL on Render/cloud hosting. To persist profiles across deploys, set DATABASE_URL in Render environment variables.")
        # ---- SQLite DDL (with ALTER TABLE migrations) ----
        conn.execute("""
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT UNIQUE NOT NULL,
                pin TEXT NOT NULL
            )
        """)

        # add new columns if they don't exist (SQLite migration style)
        import sqlite3 as _sqlite3
        _alter_columns = [
            "ALTER TABLE users ADD COLUMN display_name TEXT",
            "ALTER TABLE users ADD COLUMN weight REAL",
            "ALTER TABLE users ADD COLUMN theme TEXT",
            "ALTER TABLE users ADD COLUMN height REAL",
            "ALTER TABLE users ADD COLUMN last_login TEXT",
            "ALTER TABLE users ADD COLUMN role TEXT DEFAULT 'user'",
            "ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active'",
            "ALTER TABLE users ADD COLUMN email TEXT",
            "ALTER TABLE users ADD COLUMN email_weekly_summary INTEGER DEFAULT 1",
            "ALTER TABLE users ADD COLUMN email_monthly_summary INTEGER DEFAULT 1",
            "ALTER TABLE users ADD COLUMN google_id TEXT",
            "ALTER TABLE users ADD COLUMN google_email TEXT",
            # Weather / location columns
            "ALTER TABLE users ADD COLUMN home_city TEXT",
            "ALTER TABLE users ADD COLUMN home_latitude REAL",
            "ALTER TABLE users ADD COLUMN home_longitude REAL",
            "ALTER TABLE users ADD COLUMN failed_login_attempts INTEGER DEFAULT 0",
            "ALTER TABLE users ADD COLUMN locked_until TEXT",
            # PIN recovery columns
            "ALTER TABLE users ADD COLUMN recovery_email TEXT",
            "ALTER TABLE users ADD COLUMN recovery_email_verified INTEGER DEFAULT 0",
            # Onboarding redesign columns
            "ALTER TABLE users ADD COLUMN experience TEXT",
            "ALTER TABLE users ADD COLUMN primary_goal TEXT",
            "ALTER TABLE users ADD COLUMN frequency TEXT",
            "ALTER TABLE users ADD COLUMN profile_emoji TEXT DEFAULT '🏃🏻'",
        ]
        for sql in _alter_columns + [
            "ALTER TABLE users ADD COLUMN bio TEXT",
            "ALTER TABLE users ADD COLUMN next_race TEXT",
            "ALTER TABLE users ADD COLUMN avatar_image BLOB",
            "ALTER TABLE users ADD COLUMN avatar_mime_type TEXT",
        ]:
            try:
                conn.execute(sql)
            except _sqlite3.OperationalError:
                pass

        conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_users_google_id ON users(google_id)")

        # PIN recovery token table (SQLite)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS pin_resets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                code_hash TEXT NOT NULL,
                expires_at TEXT NOT NULL,
                attempts INTEGER DEFAULT 0,
                created_at TEXT NOT NULL,
                used_at TEXT,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        # runs table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS runs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                date TEXT NOT NULL,
                distance_km REAL NOT NULL,
                time_min REAL NOT NULL,
                pace REAL NOT NULL,
                calories REAL NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        # Add columns to runs if missing (SQLite migration — safe to re-run)
        for sql in [
            "ALTER TABLE runs ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP",
            "ALTER TABLE runs ADD COLUMN insight TEXT",
            "ALTER TABLE runs ADD COLUMN run_type TEXT DEFAULT 'easy'",
            "ALTER TABLE runs ADD COLUMN notes TEXT",  # Feature: friend mentions
            # Weather columns
            "ALTER TABLE runs ADD COLUMN weather_temp REAL",
            "ALTER TABLE runs ADD COLUMN weather_humidity INTEGER",
            "ALTER TABLE runs ADD COLUMN weather_wind_kph REAL",
            "ALTER TABLE runs ADD COLUMN weather_condition TEXT",
            "ALTER TABLE runs ADD COLUMN weather_emoji TEXT",
        ]:
            try:
                conn.execute(sql)
            except _sqlite3.OperationalError:
                pass

        # edit_history table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS edit_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                run_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                field_name TEXT NOT NULL,
                old_value TEXT,
                new_value TEXT,
                edited_at TEXT DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (run_id) REFERENCES runs(id),
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        # activity logs
        conn.execute("""
            CREATE TABLE IF NOT EXISTS activity_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER,
                action TEXT NOT NULL,
                details TEXT,
                timestamp TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        # admin notes
        conn.execute("""
            CREATE TABLE IF NOT EXISTS admin_notes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                target_user_id INTEGER NOT NULL,
                author_id INTEGER NOT NULL,
                note TEXT NOT NULL,
                timestamp TEXT NOT NULL,
                FOREIGN KEY (target_user_id) REFERENCES users(id),
                FOREIGN KEY (author_id) REFERENCES users(id)
            )
        """)

        # badges table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS badges (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                key TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                description TEXT,
                icon_url TEXT,
                criteria_type TEXT NOT NULL,
                criteria_value REAL NOT NULL
            )
        """)

        # user_badges table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_badges (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                badge_id INTEGER NOT NULL,
                unlocked_at TEXT DEFAULT CURRENT_TIMESTAMP,
                run_id INTEGER,
                UNIQUE (user_id, badge_id),
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        # user_stats table
        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_stats (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER UNIQUE NOT NULL,
                total_distance_km REAL DEFAULT 0.0,
                current_streak INTEGER DEFAULT 0,
                best_streak INTEGER DEFAULT 0,
                last_activity_date TEXT,
                updated_at TEXT,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        # friends / follow system
        conn.execute("""
            CREATE TABLE IF NOT EXISTS friends (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                follower_id INTEGER NOT NULL,
                followed_id INTEGER NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE (follower_id, followed_id),
                FOREIGN KEY (follower_id) REFERENCES users(id),
                FOREIGN KEY (followed_id) REFERENCES users(id)
            )
        """)

        # likes on runs (activity feed)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS run_likes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                run_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                UNIQUE (run_id, user_id),
                FOREIGN KEY (run_id) REFERENCES runs(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        """)
        conn.execute("CREATE INDEX IF NOT EXISTS idx_run_likes_run_id ON run_likes(run_id)")

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_weekly_goals (
                user_id INTEGER PRIMARY KEY,
                goal_km REAL NOT NULL,
                created_at TEXT NOT NULL,
                updated_at TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_goals (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                year INTEGER NOT NULL,
                month INTEGER NOT NULL,
                target_km REAL NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, year, month)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_summary_deliveries (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                year INTEGER NOT NULL,
                month INTEGER NOT NULL,
                status TEXT NOT NULL DEFAULT 'PENDING',
                error TEXT,
                sent_at TIMESTAMP,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, year, month)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_report_views (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                year INTEGER NOT NULL,
                month INTEGER NOT NULL,
                status TEXT NOT NULL DEFAULT 'DISMISSED_DAY_1',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(user_id, year, month)
            )
        """)
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_monthly_report_views_user "
            "ON monthly_report_views(user_id)"
        )
        conn.execute("""
            CREATE TABLE IF NOT EXISTS monthly_report_preferences (
                user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
                popup_enabled INTEGER NOT NULL DEFAULT 1,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_dashboard_layout (
                user_id INTEGER PRIMARY KEY,
                layout_json TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS challenges (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                key TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                description TEXT NOT NULL,
                target_value REAL NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_challenge_progress (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                challenge_id INTEGER NOT NULL,
                current_progress REAL DEFAULT 0.0,
                completed_at TEXT,
                UNIQUE (user_id, challenge_id),
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_goals (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                goal_type TEXT NOT NULL,
                target_distance_km REAL NOT NULL,
                target_date TEXT NOT NULL,
                days_per_week INTEGER NOT NULL,
                status TEXT NOT NULL DEFAULT 'active',
                created_at TEXT NOT NULL,
                dashboard_pinned BOOLEAN DEFAULT false,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)


        conn.execute("""
            CREATE TABLE IF NOT EXISTS notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                message TEXT NOT NULL,
                type TEXT NOT NULL,
                created_by INTEGER NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                expires_at TEXT,
                audience_type TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'SENT',
                FOREIGN KEY (created_by) REFERENCES users(id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                notification_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                delivered_at TEXT DEFAULT CURRENT_TIMESTAMP,
                read_at TEXT,
                UNIQUE(notification_id, user_id),
                FOREIGN KEY (notification_id) REFERENCES notifications(id),
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS web_push_subscriptions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                endpoint TEXT NOT NULL UNIQUE,
                p256dh TEXT NOT NULL,
                auth TEXT NOT NULL,
                expiration_time REAL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            )
        """)
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_web_push_subscriptions_user_id "
            "ON web_push_subscriptions(user_id)"
        )

        conn.execute("""
            CREATE TABLE IF NOT EXISTS notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                message TEXT NOT NULL,
                type TEXT NOT NULL,
                created_by INTEGER NOT NULL,
                created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                expires_at TEXT,
                audience_type TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'SENT',
                FOREIGN KEY (created_by) REFERENCES users(id)
            )
        """)

        conn.execute("""
            CREATE TABLE IF NOT EXISTS user_notifications (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                notification_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                delivered_at TEXT DEFAULT CURRENT_TIMESTAMP,
                read_at TEXT,
                UNIQUE(notification_id, user_id),
                FOREIGN KEY (notification_id) REFERENCES notifications(id),
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        """)
    conn.commit()
    
    # Seed badges and challenges
    import os
    seed_path = os.path.join(os.path.dirname(__file__), 'migrations', '002_seed_badges_challenges.sql')
    if os.path.exists(seed_path):
        with open(seed_path, 'r', encoding='utf-8') as f:
            seed_sql = f.read()
        try:
            db_url = os.environ.get("DATABASE_URL", "")
            is_postgres = db_url.startswith("postgres")
            if is_postgres:
                conn.execute(seed_sql)
            else:
                conn.executescript(seed_sql)
            conn.commit()
        except Exception as e:
            print(f"Warning: Failed to seed DB (it may already be seeded): {e}")


# ---------------------------------------------------------------------------
# CLI Commands
# ---------------------------------------------------------------------------

@app.cli.command("migrate-pins")
@click.option("--dry-run", is_flag=True, default=False, help="Preview changes without writing to DB.")
def migrate_pins(dry_run):
    """
    Hash all plaintext PINs in the users table using bcrypt.

    Run ONLY against a local copy / backup of the database, never production.
    Idempotent: already-hashed PINs (starting with '$2') are skipped.
    """
    conn = get_db()
    users = conn.execute("SELECT id, username, pin FROM users").fetchall()
    updated = 0
    skipped = 0
    for u in users:
        raw_pin = u["pin"] or ""
        if raw_pin.startswith("$2"):  # already a bcrypt hash
            click.echo(f"  SKIP  user={u['username']} (already hashed)")
            skipped += 1
            continue
        hashed = bcrypt.generate_password_hash(raw_pin)
        if not dry_run:
            conn.execute("UPDATE users SET pin = ? WHERE id = ?", (hashed, u["id"]))
        click.echo(f"  {'DRY ' if dry_run else ''}HASH  user={u['username']}")
        updated += 1
    if not dry_run:
        conn.commit()
    conn.close()
    click.echo(f"\nDone. Updated={updated}, Skipped={skipped}" + (" (dry-run, no changes written)" if dry_run else ""))


@app.cli.command("preview-monthly-summary")
@click.option("--user-id", type=int, required=True, help="User ID to build the summary for.")
@click.option("--year", type=int, required=True, help="Calendar year, e.g. 2026.")
@click.option("--month", type=int, required=True, help="Calendar month, 1-12.")
@click.option("--output", type=str, default=None,
              help="Path to write the rendered HTML. Defaults to "
                   "dev_previews/monthly_summary_<user_id>_<year>_<month>.html")
def preview_monthly_summary(user_id, year, month, output):
    """
    Render a specific user's monthly summary email locally and save it to
    disk. Does NOT send anything — no Resend call is made here at all.

    Example:
        flask preview-monthly-summary --user-id 3 --year 2026 --month 9
    """
    from services.monthly_summary_service import build_monthly_summary
    from services.monthly_summary_renderer import render_html, render_subject

    if not (1 <= month <= 12):
        click.echo(f"Error: --month must be 1-12, got {month}", err=True)
        raise SystemExit(1)

    try:
        summary = build_monthly_summary(user_id, year, month)
    except ValueError as e:
        click.echo(f"Error: {e}", err=True)
        raise SystemExit(1)

    subject = render_subject(summary)
    html_out = render_html(summary)

    if not output:
        output = os.path.join("dev_previews", f"monthly_summary_{user_id}_{year}_{month:02d}.html")
    os.makedirs(os.path.dirname(output) or ".", exist_ok=True)
    with open(output, "w", encoding="utf-8") as f:
        f.write(html_out)

    def _safe_echo(text):
        # Some terminals (notably the default Windows console codepage) can't
        # encode emoji/non-ASCII — never let a print crash the command after
        # the file has already been written successfully. click's own stream
        # wrapper doesn't reliably reflect the real console codepage, so we
        # go straight for the lowest common denominator: ASCII, with
        # unsupported characters replaced rather than raising.
        try:
            click.echo(text)
        except UnicodeEncodeError:
            click.echo(text.encode("ascii", errors="replace").decode("ascii"))

    _safe_echo(f"User:          {summary.display_name} (id={user_id})")
    _safe_echo(f"Month:         {summary.month_label}")
    _safe_echo(f"Has activity:  {summary.has_activity}")
    if summary.has_activity:
        _safe_echo(f"Total km:      {summary.total_distance_km}")
        _safe_echo(f"Total runs:    {summary.total_runs}")
        _safe_echo(f"New PBs:       {[pb.metric for pb in summary.personal_bests]}")
        _safe_echo(f"Achievements:  {[a.badge_key for a in summary.achievements]}")
    _safe_echo(f"Subject:       {subject}")
    _safe_echo(f"Saved to:      {output}")
    _safe_echo("\n(No email was sent — this command never calls the email provider.)")


def parse_date_val(val):
    if not val:
        return None
    if isinstance(val, date) and not isinstance(val, datetime):
        return val
    if isinstance(val, datetime):
        return val.date()
    if isinstance(val, str):
        val_str = val.strip()
        for fmt in ("%Y-%m-%d", "%Y-%m-%d %H:%M:%S", "%Y/%m/%d"):
            try:
                return datetime.strptime(val_str, fmt).date()
            except ValueError:
                pass
    return None


def calc_stats(distance_km, time_min, weight_kg):
    if distance_km <= 0 or time_min <= 0:
        return 0.0, 0.0
    
    pace = time_min / distance_km
    speed_kmh = (distance_km / time_min) * 60
    
    if speed_kmh < 6.0:
        mets = 2.0 + (speed_kmh - 2.0) * 0.75
    elif speed_kmh < 10.0:
        mets = 5.0 + (speed_kmh - 6.0) * (5.5 / 4.0)
    else:
        mets = 0.952 * speed_kmh + 1.0
        
    calories = mets * weight_kg * (time_min / 60.0)
    return round(pace, 2), round(calories, 0)


def format_time_min(minutes):
    total_seconds = int(round(minutes * 60))
    m = total_seconds // 60
    s = total_seconds % 60
    return f"{m}m {s:02d}s"


def require_login():
    return "user_id" in session


# ---------------------------------------------------------------------------
# Public Profile Helpers  (reusable, no auth required)
# ---------------------------------------------------------------------------

def get_personal_bests_for_user(user_id, conn):
    """
    Compute personal bests for any user given an open DB connection.
    Returns a dict with fastest_5k, fastest_10k, longest_distance, best_pace.
    """
    def _row(r):
        if not r:
            return None
        return {
            "distance_km": r["distance_km"],
            "time_min": r["time_min"],
            "pace": r["pace"],
            "date": r["date"],
        }

    fastest_5k = conn.execute(
        "SELECT * FROM runs WHERE user_id = ? AND distance_km >= 4.5 AND distance_km <= 5.5 "
        "ORDER BY pace ASC, date ASC LIMIT 1",
        (user_id,)
    ).fetchone()
    fastest_10k = conn.execute(
        "SELECT * FROM runs WHERE user_id = ? AND distance_km >= 9.5 AND distance_km <= 10.5 "
        "ORDER BY pace ASC, date ASC LIMIT 1",
        (user_id,)
    ).fetchone()
    longest_distance = conn.execute(
        "SELECT * FROM runs WHERE user_id = ? ORDER BY distance_km DESC, date ASC LIMIT 1",
        (user_id,)
    ).fetchone()
    best_pace = conn.execute(
        "SELECT * FROM runs WHERE user_id = ? AND distance_km >= 1.0 ORDER BY pace ASC, date ASC LIMIT 1",
        (user_id,)
    ).fetchone()
    return {
        "fastest_5k": _row(fastest_5k),
        "fastest_10k": _row(fastest_10k),
        "longest_distance": _row(longest_distance),
        "best_pace": _row(best_pace),
    }


def get_badges_status_for_user(user_id):
    """
    Return full badge list with earned/locked status for any user.
    Reuses BADGE_METADATA from badge_service.
    """
    from services.badge_service import BADGE_METADATA, get_user_badges
    unlocked = get_user_badges(user_id)
    unlocked_keys = {b["badge_key"]: b["unlocked_at"] for b in unlocked}
    result = []
    for key, meta in BADGE_METADATA.items():
        result.append({
            "key": key,
            "name": meta["name"],
            "icon": meta["icon"],
            "description": meta["description"],
            "earned": key in unlocked_keys,
            "unlocked_at": unlocked_keys.get(key),
        })
    return result


@app.before_request
def check_pin_setup():
    from flask import request, redirect, url_for, session
    if "user_id" in session:
        allowed_endpoints = [
            'set_pin', 'logout', 'static', 'google_disconnect',
            'google_login', 'google_auth',
            'auth.login', 'auth.register', 'auth.logout',
            # PIN recovery flow (unauthenticated)
            'forgot_pin', 'forgot_pin_methods', 'forgot_pin_send_email',
            'forgot_pin_verify', 'forgot_pin_reset',
        ]
        if request.endpoint and request.endpoint not in allowed_endpoints:
            conn = get_db()
            user = conn.execute("SELECT pin FROM users WHERE id = ?", (session["user_id"],)).fetchone()
            conn.close()
            if user and not user['pin']:
                return redirect(url_for('set_pin'))



def is_onboarding_complete(user):
    """Check if the user has completed mandatory onboarding fields."""
    if not user:
        return False
    return bool(user['display_name'] and user['profile_emoji'] and user['experience'] and user['primary_goal'])

def get_current_user():
    if "user_id" not in session:
        return None
    conn = get_db()
    user = conn.execute(
        "SELECT * FROM users WHERE id = ?",
        (session["user_id"],)
    ).fetchone()
    conn.close()
    return user


def get_user_role(user):
    """
    Returns 'admin', 'moderator', or 'user'.
    Checks ENV variable for Super Admin first.
    """
    if not user:
        return None
    
    # Super Admin Check
    admin_id = os.environ.get("ADMIN_USER_ID")
    if str(user["id"]) == str(admin_id):
        return "admin"
    
    # DB Role Check
    return user["role"] if user["role"] in ["admin", "moderator"] else "user"


def log_activity(user_id, action, details=None):
    try:
        conn = get_db()
        now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        conn.execute(
            "INSERT INTO activity_logs (user_id, action, details, timestamp) VALUES (?, ?, ?, ?)",
            (user_id, action, details, now_str)
        )
        conn.commit()
    except Exception:
        pass
    finally:
        if 'conn' in locals():
            conn.close()


def generate_run_insight(user_id, distance, pace, calories):
    """
    Generate a friendly, motivational 1-2 line insight about the run.
    Analyzes performance vs user's history and provides encouraging feedback.
    """
    import random
    
    conn = get_db()
    
    # Get user's recent stats (last 30 days)
    cutoff_date = (datetime.now() - timedelta(days=30)).strftime("%Y-%m-%d")
    recent_runs = conn.execute("""
        SELECT distance_km, pace, calories 
        FROM runs 
        WHERE user_id = ? 
        AND date >= ?
        ORDER BY date DESC
        LIMIT 10
    """, (user_id, cutoff_date)).fetchall()
    
    conn.close()
    
    # If this is first run or very few runs
    if len(recent_runs) <= 1:
        return random.choice([
            "Great start! Every journey begins with a single step 🏃",
            "Welcome to your running journey! Keep it up 💪",
            "First run logged! This is just the beginning 🔥",
            "Awesome! You've taken the first step toward your goals 🎯"
        ])
    
    # Calculate averages from recent runs (excluding current one)
    avg_pace = sum(r["pace"] for r in recent_runs[1:]) / len(recent_runs[1:])
    avg_distance = sum(r["distance_km"] for r in recent_runs[1:]) / len(recent_runs[1:])
    
    insights = []
    
    # Pace analysis
    if pace < avg_pace * 0.95:  # 5% faster
        insights.append("You beat your average pace! 🔥")
    elif pace < avg_pace:
        insights.append("Solid pace today! 👏")
    elif pace > avg_pace * 1.1:  # 10% slower
        insights.append("Pace was slower today — try starting easier next time 🏃")
    
    # Distance milestones
    if distance >= 5 and distance < 5.5:
        insights.append("You hit 5K! Great milestone 🎉")
    elif distance >= 10 and distance < 10.5:
        insights.append("Double digits! 10K completed 🏆")
    elif distance > avg_distance * 1.2:  # 20% longer
        insights.append("Longest run in a while! Keep pushing 💪")
    elif distance > avg_distance:
        insights.append("You went further than usual today!")
    
    # Consistency praise
    if len(recent_runs) >= 5:
        insights.append("Great consistency this month!")
    
    # Return insight or default
    if insights:
        return " ".join(insights[:2])  # Max 2 insights
    
    # Default encouraging messages
    return random.choice([
        "Another run in the books! Keep it up 🏃",
        "Consistent effort pays off. Well done! 💪",
        "Every run counts. Great work today! 🔥",
        "You showed up and that's what matters! 👏"
    ])



def is_run_locked(run):
    """Check if run is locked (>24h old)"""
    created_at = run["created_at"] if "created_at" in run.keys() else None
    if not created_at:
        return False  # Legacy runs without created_at are not locked
    
    try:
        created = datetime.strptime(str(run['created_at'])[:19], '%Y-%m-%d %H:%M:%S')
        now = datetime.now()
        age_hours = (now - created).total_seconds() / 3600
        return age_hours > 24
    except (ValueError, TypeError):
        return False


def can_edit_run(run, user):
    """Check if user can edit this run"""
    # Admin/moderator override
    if get_user_role(user) in ['admin', 'moderator']:
        return True
    
    # Owner check
    if run['user_id'] != user['id']:
        return False
    
    # Lock check
    return not is_run_locked(run)


def log_edit_history(run_id, user_id, changes):
    """Log all field changes to edit_history table"""
    if not changes:
        return
    
    conn = get_db()
    try:
        for field, (old_val, new_val) in changes.items():
            conn.execute(
                """
                INSERT INTO edit_history (run_id, user_id, field_name, old_value, new_value)
                VALUES (?, ?, ?, ?, ?)
                """,
                (run_id, user_id, field, str(old_val), str(new_val))
            )
        conn.commit()
    finally:
        conn.close()


# ----------------- BADGE SYSTEM -----------------

from services.badge_service import evaluate_badges_for_user, award_badge


def update_user_stats(user_id, run_date_str, distance_km, operation='add'):
    """
    Incrementally update user stats when a run is added or deleted.
    
    Args:
        operation: 'add' or 'delete'
    """
    conn = get_db()
    
    stats = conn.execute(
        "SELECT * FROM user_stats WHERE user_id = ?", 
        (user_id,)
    ).fetchone()
    
    if not stats:
        stats = initialize_user_stats(user_id)
        stats = conn.execute(
            "SELECT * FROM user_stats WHERE user_id = ?", 
            (user_id,)
        ).fetchone()
    
    # Update total distance
    if operation == 'add':
        new_total = stats['total_distance_km'] + distance_km
    else:  # delete
        new_total = max(0, stats['total_distance_km'] - distance_km)
    
    # Recalculate streak (always recalculate to ensure accuracy)
    current_streak, best_streak = calculate_streak_for_user(user_id)
    
    now_str = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
    
    conn.execute(
        """
        UPDATE user_stats
        SET total_distance_km = ?,
            current_streak = ?,
            best_streak = ?,
            last_activity_date = ?,
            updated_at = ?
        WHERE user_id = ?
        """,
        (new_total, current_streak, best_streak, run_date_str, now_str, user_id)
    )
    conn.commit()
    conn.close()


def calculate_streak_for_user(user_id):
    """
    Calculate current and best streak for a user.
    Returns: (current_streak, best_streak)
    """
    conn = get_db()
    
    # Get all unique activity dates, sorted
    runs = conn.execute(
        "SELECT DISTINCT date FROM runs WHERE user_id = ? ORDER BY date ASC",
        (user_id,)
    ).fetchall()
    
    all_dates = []
    for r in runs:
        parsed_d = parse_date_val(r['date'])
        if parsed_d:
            all_dates.append(parsed_d)
            
    if not all_dates:
        conn.close()
        return 0, 0
        
    all_dates = sorted(list(set(all_dates)))
    today = date.today()
    
    # Calculate current streak (backward from today)
    current_streak = 0
    day_pointer = today
    
    while day_pointer in all_dates:
        current_streak += 1
        day_pointer = day_pointer - timedelta(days=1)
    
    # Calculate best streak
    best_streak = 0
    streak = 1
    for i in range(1, len(all_dates)):
        if all_dates[i] == all_dates[i-1] + timedelta(days=1):
            streak += 1
        else:
            best_streak = max(best_streak, streak)
            streak = 1
    best_streak = max(best_streak, streak)
    
    conn.close()
    return current_streak, best_streak


def initialize_user_stats(user_id):
    """Create initial stats record for a user."""
    conn = get_db()
    now_str = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
    
    try:
        conn.execute(
            """
            INSERT INTO user_stats (user_id, total_distance_km, current_streak, best_streak, updated_at)
            VALUES (?, 0.0, 0, 0, ?)
            """,
            (user_id, now_str)
        )
        conn.commit()
    except IntegrityError:
        # Stats already exist
        conn.rollback()
        pass
    
    stats = conn.execute(
        "SELECT * FROM user_stats WHERE user_id = ?",
        (user_id,)
    ).fetchone()
    
    conn.close()
    return stats


def recalculate_user_stats(user_id):
    """
    Resync a user's user_stats row from their runs: total distance, streaks
    and last activity date are all recomputed from scratch, so it is
    idempotent and also creates a missing row. update_user_stats is
    incremental (it adds/subtracts one run's distance), so paths that change
    runs in other ways -- editing a run, clearing all runs -- must use this
    instead; otherwise the cached total drifts and every later add carries
    the drift forward.
    """
    conn = get_db()
    agg = conn.execute(
        "SELECT COALESCE(SUM(distance_km), 0) AS total, MAX(date) AS last_date "
        "FROM runs WHERE user_id = ?",
        (user_id,)
    ).fetchone()
    conn.close()

    initialize_user_stats(user_id)
    current_streak, best_streak = calculate_streak_for_user(user_id)
    last_date = str(agg["last_date"])[:10] if agg["last_date"] else None
    now_str = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")

    conn = get_db()
    conn.execute(
        """
        UPDATE user_stats
        SET total_distance_km = ?,
            current_streak = ?,
            best_streak = ?,
            last_activity_date = ?,
            updated_at = ?
        WHERE user_id = ?
        """,
        (agg["total"], current_streak, best_streak, last_date, now_str, user_id)
    )
    conn.commit()
    conn.close()


@app.cli.command("resync-user-stats")
@click.option("--apply", "apply_changes", is_flag=True, default=False,
              help="Write the repairs. Without it, only report which users are out of sync.")
def resync_user_stats_command(apply_changes):
    """
    Report (and with --apply, repair) users whose cached user_stats disagree
    with their runs. Compares total distance and best streak; current streak
    is not compared because it legitimately goes stale as days pass without a
    run, but --apply refreshes it for every user it repairs.

    Back up first and review the report before using --apply on a real
    database.
    """
    conn = get_db()
    rows = conn.execute(
        """
        SELECT u.id, u.username,
               us.total_distance_km AS cached_total, us.best_streak AS cached_best,
               COALESCE(r.total, 0) AS actual_total
        FROM users u
        LEFT JOIN user_stats us ON us.user_id = u.id
        LEFT JOIN (SELECT user_id, SUM(distance_km) AS total FROM runs GROUP BY user_id) r
               ON r.user_id = u.id
        ORDER BY u.id
        """
    ).fetchall()
    conn.close()

    drifted = []
    for row in rows:
        _, actual_best = calculate_streak_for_user(row["id"])
        missing = row["cached_total"] is None
        total_off = (not missing) and abs(row["cached_total"] - row["actual_total"]) > 0.005
        best_off = (not missing) and (row["cached_best"] or 0) != actual_best
        if (missing and row["actual_total"] > 0) or total_off or best_off:
            drifted.append(row)
            cached = "none" if missing else f"{row['cached_total']:.2f} km / best {row['cached_best']}"
            click.echo(f"  {row['username']} (id {row['id']}): cached {cached} -> actual "
                       f"{row['actual_total']:.2f} km / best {actual_best}")

    click.echo(f"{len(drifted)} user(s) out of sync.")
    if apply_changes:
        for row in drifted:
            recalculate_user_stats(row["id"])
        click.echo(f"Repaired {len(drifted)} user(s).")
    elif drifted:
        click.echo("Dry run -- re-run with --apply to write these repairs.")


# ----------------- ROUTES -----------------


@app.route('/api/admin/clean-spam')
def clean_spam():
    conn = get_db()
    spam_users = ['asdfgvbnm', 'Mihikaaaaa', 'Mike', 'fgbn 22345ty', 'Ateet', 'Palak']
    
    deleted_count = 0
    for u in spam_users:
        # Find user
        cur = conn.execute('SELECT id FROM users WHERE username = ?', (u,))
        row = cur.fetchone()
        if row:
            uid = row['id']
            conn.execute('DELETE FROM runs WHERE user_id = ?', (uid,))
            conn.execute('DELETE FROM users WHERE id = ?', (uid,))
            deleted_count += 1
            
    conn.commit()
    conn.close()
    return f'Success! Deleted {deleted_count} spam users.'


@app.route('/testlogin')
def testlogin():
    from flask import session, redirect, url_for
    session['user_id'] = 3
    session['username'] = 'Himanshu'
    session['display_name'] = 'Himanshu'
    session['google_id'] = 'mocked'
    return redirect(url_for('index'))

@app.route("/")
@app.route("/landing")
def home_redirect():
    if request.path == "/landing" or not require_login():
        return render_template("landing.html")
    return redirect(url_for("index"))


@app.route("/offline")
def offline():
    """PWA offline fallback page — served from Service Worker cache."""
    return render_template("offline.html")


@app.route("/sw.js")
def serve_sw():
    response = make_response(app.send_static_file("sw.js"))
    response.headers["Content-Type"] = "application/javascript"
    response.headers["Service-Worker-Allowed"] = "/"
    return response


@app.route("/dashboard", methods=["GET"])
def index():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    username = user["username"]
    display_name = user["display_name"] or username
    user_weight = user["weight"] if user["weight"] is not None else DEFAULT_WEIGHT

    # ---- BMI CALCULATION (uses actual stored weight + height) ----
    raw_weight = user["weight"]
    raw_height = user["height"] if "height" in user.keys() else None

    bmi_value = None
    bmi_status = None

    try:
        if raw_weight is not None and raw_height is not None:
            w = float(raw_weight)
            h_cm = float(raw_height)
            if h_cm > 0:
                h_m = h_cm / 100.0
                bmi_value = round(w / (h_m * h_m), 1)

                if bmi_value < 18.5:
                    bmi_status = "Underweight"
                elif bmi_value < 25:
                    bmi_status = "Normal"
                elif bmi_value < 30:
                    bmi_status = "Overweight"
                else:
                    bmi_status = "Obese"
    except (TypeError, ValueError):
        bmi_value = None
        bmi_status = None

    # theme from DB (fallback dark)
    theme = user["theme"] or "system"

    # --- read sort + filter from query params ---
    sort_by = request.args.get("sort", "date")      # date | distance | time
    filter_opt = request.args.get("filter", "all")  # all | last7 | month | 5k10k

    conn = get_db()

    # --- base query with sorting ---
    base_query = "SELECT * FROM runs WHERE user_id = ?"
    
    if sort_by == "distance_asc":
        order_clause = " ORDER BY distance_km ASC, date DESC"
    elif sort_by == "distance" or sort_by == "distance_desc":
        order_clause = " ORDER BY distance_km DESC, date DESC"
    elif sort_by == "time_asc":
        order_clause = " ORDER BY time_min ASC, date DESC"
    elif sort_by == "time" or sort_by == "time_desc":
        order_clause = " ORDER BY time_min DESC, date DESC"
    elif sort_by == "pace_asc":
        order_clause = " ORDER BY pace ASC, date DESC"
    elif sort_by == "pace" or sort_by == "pace_desc":
        order_clause = " ORDER BY pace DESC, date DESC"
    elif sort_by == "cal_asc":
        order_clause = " ORDER BY calories ASC, date DESC"
    elif sort_by == "cal" or sort_by == "cal_desc":
        order_clause = " ORDER BY calories DESC, date DESC"
    elif sort_by == "date_asc":
        order_clause = " ORDER BY date ASC, id ASC"
    else:  # default = date or date_desc
        order_clause = " ORDER BY date DESC, id DESC"

    runs = conn.execute(base_query + order_clause, (user["id"],)).fetchall()

    # ---- All-time stats (use ALL runs) ----
    total_km = sum(r["distance_km"] for r in runs) if runs else 0
    total_cal = sum(r["calories"] for r in runs) if runs else 0
    avg_pace = (sum(r["pace"] for r in runs) / len(runs)) if runs else 0

    # ---- This month stats (use ALL runs) ----
    today = get_today()
    current_year = today.year
    current_month = today.month

    month_runs = []
    for r in runs:
        try:
            d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d").date()
            if d.year == current_year and d.month == current_month:
                month_runs.append(r)
        except Exception:
            continue

    month_km = sum(r["distance_km"] for r in month_runs) if month_runs else 0
    month_cal = sum(r["calories"] for r in month_runs) if month_runs else 0
    month_avg_pace = (sum(r["pace"] for r in month_runs) / len(month_runs)) if month_runs else 0

    # ---- WEEKLY DISTANCE + GOAL PROGRESS ----
    week_start, week_end = get_current_week_range(today)

    week_runs = []
    for r in runs:
        try:
            d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d").date()
            if week_start <= d <= week_end:
                week_runs.append(r)
        except Exception:
            continue

    weekly_km = sum(r["distance_km"] for r in week_runs) if week_runs else 0.0

    conn = get_db()
    wg_row = conn.execute("SELECT goal_km FROM user_weekly_goals WHERE user_id = ?", (user['id'],)).fetchone()
    conn.close()
    weekly_goal = wg_row['goal_km'] if wg_row else None

    if weekly_goal and weekly_goal > 0:
        weekly_remaining = max(weekly_goal - weekly_km, 0)
        weekly_progress_percent = min(100.0 * weekly_km / weekly_goal, 100.0)
    else:
        weekly_remaining = None
        weekly_progress_percent = 0.0

    # ---- Personal bests (use ALL runs) ----
    pb_5k_time = None
    pb_5k_pace = None
    pb_10k_time = None
    pb_10k_pace = None

    for r in runs:
        dist = r["distance_km"]
        t = r["time_min"]
        pace = r["pace"]

        # treat 4.5–5.5 km as 5K
        if 4.5 <= dist <= 5.5:
            if pb_5k_time is None or t < pb_5k_time:
                pb_5k_time = t
                pb_5k_pace = pace

        # treat 9–11 km as 10K
        if 9.0 <= dist <= 11.0:
            if pb_10k_time is None or t < pb_10k_time:
                pb_10k_time = t
                pb_10k_pace = pace

    pb_5k_time_str = format_time_min(pb_5k_time) if pb_5k_time is not None else None
    pb_10k_time_str = format_time_min(pb_10k_time) if pb_10k_time is not None else None

    # ---- FILTER for history table (not stats) ----
    filtered_runs = list(runs)

    if filter_opt == "last7":
        cutoff = today - timedelta(days=7)
        temp = []
        for r in runs:
            try:
                d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d").date()
                if d >= cutoff:
                    temp.append(r)
            except Exception:
                continue
        filtered_runs = temp

    elif filter_opt == "month":
        filtered_runs = month_runs

    elif filter_opt == "5k10k":
        temp = []
        for r in runs:
            dist = r["distance_km"]
            if (4.5 <= dist <= 5.5) or (9.0 <= dist <= 11.0):
                temp.append(r)
        filtered_runs = temp

    # ---------- STREAK CALCULATION ----------
    all_dates = []
    for r in runs:
        try:
            d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d").date()
            all_dates.append(d)
        except Exception:
            pass

    if all_dates:
        all_dates = sorted(list(set(all_dates)))  # unique + sorted ASC

        # ---- CURRENT STREAK ----
        current_streak = 0
        day_pointer = today

        while day_pointer in all_dates:
            current_streak += 1
            day_pointer = day_pointer - timedelta(days=1)

        # ---- BEST STREAK ----
        best_streak = 0
        streak = 1
        for i in range(1, len(all_dates)):
            if all_dates[i] == all_dates[i-1] + timedelta(days=1):
                streak += 1
            else:
                best_streak = max(best_streak, streak)
                streak = 1
        best_streak = max(best_streak, streak)

        # ---- WEEKLY STREAK BAR (Mon–Sun) ----
        week_start, _ = get_current_week_range(today)
        week_days = [week_start + timedelta(days=i) for i in range(7)]

        streak_bar = []
        for d in week_days:
            if d in all_dates:
                streak_bar.append("🔥")
            else:
                streak_bar.append("—")
    else:
        current_streak = 0
        best_streak = 0
        streak_bar = ["—"] * 7

    # ---- WEEKLY LEADERBOARD — qualification threshold: >= 1.00 KM ----
    lb_start_str = week_start.strftime("%Y-%m-%d")
    lb_end_str = week_end.strftime("%Y-%m-%d")

    lb_all_query = """
        SELECT
            u.username,
            u.display_name,
            CASE WHEN u.avatar_image IS NOT NULL THEN 1 ELSE 0 END as has_avatar,
            COALESCE(MAX(us.current_streak), 0) as current_streak,
            COALESCE(SUM(r.distance_km), 0) as total_dist
        FROM users u
        LEFT JOIN user_stats us ON u.id = us.user_id
        JOIN runs r ON u.id = r.user_id
        WHERE r.date BETWEEN ? AND ?
          AND COALESCE(u.status, 'active') != 'blocked'
        GROUP BY u.id, u.username, u.display_name, u.avatar_image
        HAVING COALESCE(SUM(r.distance_km), 0) > 0
        ORDER BY total_dist DESC
    """
    lb_all_rows = conn.execute(lb_all_query, (lb_start_str, lb_end_str)).fetchall()

    QUALIFY_KM = 1.0  # minimum KM to appear in ranked leaderboard

    weekly_leaderboard = []       # qualified (>= 1 km) — ranked
    weekly_leaderboard_unranked = []  # unqualified (0 < dist < 1 km)
    for row in lb_all_rows:
        dist = row["total_dist"]
        entry = {
            "username": row["username"],
            "display_name": row["display_name"] or row["username"],
            "has_avatar": bool(row["has_avatar"]),
            "current_streak": row["current_streak"],
            "total_dist": round(dist, 2)
        }
        if dist >= QUALIFY_KM:
            weekly_leaderboard.append(entry)
        else:
            remaining = round(QUALIFY_KM - dist, 2)
            entry["remaining"] = remaining
            entry["motivational"] = ("Run 1 KM to enter the leaderboard"
                                     if dist == 0
                                     else f"{remaining:.2f} KM to qualify")
            weekly_leaderboard_unranked.append(entry)

    # ---- MONTHLY LEADERBOARD — qualification threshold: >= 1.00 KM ----
    from utils.dates import get_current_month_range
    month_start_lb, month_end_lb = get_current_month_range(today)
    month_start_str = month_start_lb.strftime("%Y-%m-%d")
    month_end_str = month_end_lb.strftime("%Y-%m-%d")

    monthly_all_query = """
        SELECT
            u.username,
            u.display_name,
            CASE WHEN u.avatar_image IS NOT NULL THEN 1 ELSE 0 END as has_avatar,
            COALESCE(MAX(us.current_streak), 0) as current_streak,
            COALESCE(SUM(r.distance_km), 0) as total_dist
        FROM users u
        LEFT JOIN user_stats us ON u.id = us.user_id
        JOIN runs r ON u.id = r.user_id
        WHERE r.date BETWEEN ? AND ?
          AND COALESCE(u.status, 'active') != 'blocked'
        GROUP BY u.id, u.username, u.display_name, u.avatar_image
        HAVING COALESCE(SUM(r.distance_km), 0) > 0
        ORDER BY total_dist DESC
    """
    monthly_all_rows = conn.execute(monthly_all_query, (month_start_str, month_end_str)).fetchall()

    monthly_leaderboard = []          # qualified (>= 1 km)
    monthly_leaderboard_unranked = [] # unqualified
    for row in monthly_all_rows:
        dist = row["total_dist"]
        entry = {
            "username": row["username"],
            "display_name": row["display_name"] or row["username"],
            "has_avatar": bool(row["has_avatar"]),
            "current_streak": row["current_streak"],
            "total_dist": round(dist, 2)
        }
        if dist >= QUALIFY_KM:
            monthly_leaderboard.append(entry)
        else:
            remaining = round(QUALIFY_KM - dist, 2)
            entry["remaining"] = remaining
            entry["motivational"] = ("Run 1 KM to enter the leaderboard"
                                     if dist == 0
                                     else f"{remaining:.2f} KM to qualify")
            monthly_leaderboard_unranked.append(entry)

    # ---- Feature: Streak Reminder — detect if user ran today ----
    today_str = today.strftime("%Y-%m-%d")
    ran_today = any(
        str(r["date"])[:10] == today_str for r in runs
    )

    # ---- All-Time Leaderboard (for SPA Leaderboard Tab) — qualification threshold: >= 1.00 KM ----
    all_time_query = """
        SELECT
            u.username,
            u.display_name,
            CASE WHEN u.avatar_image IS NOT NULL THEN 1 ELSE 0 END as has_avatar,
            COALESCE(MAX(us.current_streak), 0) as current_streak,
            COALESCE(SUM(r.distance_km), 0)  AS total_dist,
            COALESCE(SUM(r.time_min), 0)     AS total_time,
            COUNT(r.id)                      AS run_count
        FROM users u
        LEFT JOIN user_stats us ON u.id = us.user_id
        LEFT JOIN runs r ON u.id = r.user_id
        WHERE COALESCE(u.status, 'active') != 'blocked'
        GROUP BY u.id, u.username, u.display_name, u.avatar_image
        ORDER BY total_dist DESC
    """
    all_time_rows = conn.execute(all_time_query).fetchall()
    all_time_leaderboard = []          # qualified (>= 1 km)
    all_time_leaderboard_unranked = [] # unqualified
    for row in all_time_rows:
        total_dist_at = row["total_dist"]
        total_time_at = row["total_time"]
        entry = {
            "username": row["username"],
            "display_name": row["display_name"] or row["username"],
            "has_avatar": bool(row["has_avatar"]),
            "current_streak": row["current_streak"],
            "total_dist": round(total_dist_at, 2),
            "total_time": round(total_time_at, 1),
            "run_count": row["run_count"],
            "avg_pace": round(total_time_at / total_dist_at, 2) if total_dist_at > 0 else 0
        }
        if total_dist_at >= QUALIFY_KM:
            all_time_leaderboard.append(entry)
        else:
            remaining = round(QUALIFY_KM - total_dist_at, 2)
            entry["remaining"] = remaining
            entry["motivational"] = ("Run 1 KM to enter the leaderboard"
                                     if total_dist_at == 0
                                     else f"{remaining:.2f} KM to qualify")
            all_time_leaderboard_unranked.append(entry)

    # ---- Map Personal Bests for Inline Badges ----
    pb_records = get_personal_bests_for_user(user["id"], conn)
    pb_run_ids = {}
    for pb_key, pb_data in pb_records.items():
        if pb_data and pb_data.get("run_id"):
            rid = pb_data["run_id"]
            if rid not in pb_run_ids:
                pb_run_ids[rid] = []
            
            # Convert keys to nice labels
            if pb_key == "fastest_5k": pb_run_ids[rid].append("Fastest 5K")
            elif pb_key == "fastest_10k": pb_run_ids[rid].append("Fastest 10K")
            elif pb_key == "longest_run": pb_run_ids[rid].append("Longest Run")
            elif pb_key == "best_pace": pb_run_ids[rid].append("Best Pace")
            elif pb_key == "highest_calories": pb_run_ids[rid].append("Highest Calories")

    conn.close()

    # Pop new_badges so confetti only fires once per badge earn
    new_badges = session.pop('new_badges', None)
    level_up = session.pop('level_up', None)

    # ---- Recent runs for goals.js (last 8 weeks) ----
    eight_weeks_ago = today - timedelta(days=56)
    recent_runs = []
    for r in runs:
        try:
            d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d").date()
            if d >= eight_weeks_ago:
                recent_runs.append({"date": r["date"], "distance_km": r["distance_km"], "pace": r["pace"]})
        except Exception:
            continue

    total_runs_count = len(filtered_runs)
    # Only pass the first 15 runs to the template to avoid HTML bloat
    filtered_runs = filtered_runs[:15]
    try:
        from services.social_service import get_like_counts
        run_like_counts = get_like_counts([r["id"] for r in filtered_runs])
    except Exception as like_err:
        print(f"Like counts warning: {like_err}")
        run_like_counts = {}

    # Rank chips for every runner on the leaderboards
    try:
        from services.level_service import ranks_for_usernames
        _lb_lists = (weekly_leaderboard, monthly_leaderboard, all_time_leaderboard)
        _lb_ranks = ranks_for_usernames({e["username"] for lst in _lb_lists for e in lst})
        for lst in _lb_lists:
            for e in lst:
                e["rank"] = _lb_ranks.get(e["username"])
    except Exception as rk_err:
        print(f"Leaderboard rank warning: {rk_err}")

    return render_template(
        "index.html",
        theme=theme,
        runs=filtered_runs,              # history table uses filtered list
        run_like_counts=run_like_counts,
        total_runs_count=total_runs_count, # total count of filtered runs
        recent_runs=recent_runs,         # minimal data for goals.js
        total_km=round(total_km, 2),
        total_cal=round(total_cal, 0),
        avg_pace=round(avg_pace, 2),
        month_km=round(month_km, 2),
        month_cal=round(month_cal, 0),
        month_avg_pace=round(month_avg_pace, 2),
        pb_5k_time=pb_5k_time_str,
        pb_5k_pace=round(pb_5k_pace, 2) if pb_5k_pace is not None else None,
        pb_10k_time=pb_10k_time_str,
        pb_10k_pace=round(pb_10k_pace, 2) if pb_10k_pace is not None else None,
        weight=user_weight,
        username=username,
        display_name=display_name,
        has_avatar=bool(user["avatar_image"]) if "avatar_image" in user.keys() else False,
        profile_emoji=user["profile_emoji"] if "profile_emoji" in user.keys() else "🏃🏻",
        active_sort=sort_by,
        active_filter=filter_opt,
        current_streak=current_streak,
        best_streak=best_streak,
        streak_bar=streak_bar,
        weekly_km=round(weekly_km, 1),
        weekly_goal=weekly_goal,
        weekly_remaining=round(weekly_remaining, 1) if weekly_remaining is not None else None,
        weekly_progress_percent=round(weekly_progress_percent, 1),
        bmi_status=bmi_status,
        today=today_str,
        height=raw_height,
        profile_height=raw_height,
        profile_weight=raw_weight,
        profile_bmi=bmi_value,
        weekly_leaderboard=weekly_leaderboard,
        weekly_leaderboard_unranked=weekly_leaderboard_unranked,
        monthly_leaderboard=monthly_leaderboard,
        monthly_leaderboard_unranked=monthly_leaderboard_unranked,
        all_time_leaderboard=all_time_leaderboard,
        all_time_leaderboard_unranked=all_time_leaderboard_unranked,
        new_badges=new_badges or [],
        level_up=level_up,
        ran_today=ran_today,          # Feature: streak reminder
        pb_run_ids=pb_run_ids,
        google_id=user["google_id"] if "google_id" in user.keys() else None,
        google_email=user["google_email"] if "google_email" in user.keys() else None
    )


# ---------- ADD RUN ----------

@app.route("/add", methods=["POST"])
def add_run():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    try:
        date_str = request.form.get("date", "").strip()
        distance_str = request.form.get("distance", "").strip()
        time_str = request.form.get("time", "").strip()
        # Feature: Friend Mentions — truncate notes to 500 chars to prevent abuse
        notes = request.form.get("notes", "").strip()[:500]
        run_type = request.form.get("run_type", "easy").strip()
        valid_run_types = {"easy", "tempo", "long", "interval", "race"}
        if run_type not in valid_run_types:
            run_type = "easy"

        # Validation: Date
        if date_str:
            run_date = parse_date_val(date_str)
            if not run_date:
                flash("Invalid date format.", "danger")
                return redirect(url_for("index"))
            # +1 day: the runner's local "today" (e.g. India, UTC+5:30) can be ahead of the server's
            today_date = datetime.now().date() + timedelta(days=1)
            if run_date > today_date:
                log_activity(user["id"], "VALIDATION_FAIL", f"Attempted future date: {date_str}")
                flash("You cannot log runs for future dates.", "danger")
                return redirect(url_for("index"))
        else:
            date_str = datetime.now().strftime("%Y-%m-%d")

        # Validation: Distance (must be positive)
        try:
            distance = float(distance_str)
            if distance <= 0:
                log_activity(user["id"], "VALIDATION_FAIL", f"Invalid distance: {distance}")
                flash("Distance must be greater than 0 km.", "danger")
                return redirect(url_for("index"))
            if distance > 1000:
                log_activity(user["id"], "VALIDATION_FAIL", f"Invalid distance (over limit): {distance}")
                flash("Distance cannot exceed 1000 km per run.", "danger")
                return redirect(url_for("index"))
        except (ValueError, TypeError):
            flash("Invalid distance value.", "danger")
            return redirect(url_for("index"))

        # Validation: Duration (must be positive)
        try:
            time_min = float(time_str)
            if time_min <= 0:
                log_activity(user["id"], "VALIDATION_FAIL", f"Invalid duration: {time_min}")
                flash("Duration must be greater than 0 minutes.", "danger")
                return redirect(url_for("index"))
        except (ValueError, TypeError):
            flash("Invalid duration value.", "danger")
            return redirect(url_for("index"))

        # Validation: Pace (realistic check)
        pace_check = time_min / distance
        if pace_check > 30:  # Slower than 30 min/km (very slow walking)
            log_activity(user["id"], "VALIDATION_FAIL", f"Unrealistic pace (too slow): {pace_check:.2f} min/km")
            flash("Pace seems unrealistic. Please check your distance and time.", "danger")
            return redirect(url_for("index"))
        if pace_check < 2:  # Faster than 2 min/km (world record territory)
            log_activity(user["id"], "VALIDATION_FAIL", f"Unrealistic pace (too fast): {pace_check:.2f} min/km")
            flash("Pace seems too fast. Please check your distance and time.", "danger")
            return redirect(url_for("index"))

        # Double-tap guard: the same run submitted again within 2 minutes is the
        # same run (slow saves make people press "Log Run" twice) — don't store it twice
        recent_cutoff = (datetime.now() - timedelta(minutes=2)).strftime("%Y-%m-%d %H:%M:%S")
        dup_conn = get_db()
        duplicate = dup_conn.execute(
            """
            SELECT id FROM runs
            WHERE user_id = ? AND substr(CAST(date AS TEXT), 1, 10) = ? AND ABS(distance_km - ?) < 0.001 AND ABS(time_min - ?) < 0.001
              AND created_at >= ?
            ORDER BY id DESC LIMIT 1
            """,
            (user["id"], date_str[:10], distance, time_min, recent_cutoff),
        ).fetchone()
        dup_conn.close()
        if duplicate:
            log_activity(user["id"], "RUN_DUPLICATE_SKIPPED", f"Ignored repeat submit of run {duplicate['id']}")
            flash("Run logged! 🎉", "success")
            return redirect(url_for("index", open=duplicate["id"]))

        user_weight = user["weight"] if "weight" in user.keys() and user["weight"] is not None else DEFAULT_WEIGHT
        pace, calories = calc_stats(distance, time_min, user_weight)
        
        # Generate AI insight for this run
        try:
            insight = generate_run_insight(user["id"], distance, pace, calories)
        except Exception:
            insight = "Great run logged! Keep up the effort! 🏃"

        # ── Auto-fetch weather for this run ─────────────────────────────────────
        weather = None
        if "home_latitude" in user.keys() and "home_longitude" in user.keys() and user["home_latitude"] and user["home_longitude"]:
            try:
                from services.weather_service import fetch_weather
                weather = fetch_weather(
                    run_date_str=date_str,
                    latitude=user["home_latitude"],
                    longitude=user["home_longitude"],
                )
            except Exception:
                weather = None  # Weather is non-critical — never block a run save
        # ─────────────────────────────────────────────────────────────────────────

        conn = get_db()
        # Insert run with created_at timestamp, insight, notes, run_type, and weather
        now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        row = conn.execute(
            """
            INSERT INTO runs (
                user_id, date, distance_km, time_min, pace, calories, created_at,
                insight, run_type, notes,
                weather_temp, weather_humidity, weather_wind_kph, weather_condition, weather_emoji
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            RETURNING id
            """,
            (
                user["id"], date_str, distance, time_min, pace, calories, now_str,
                insight, run_type, notes or None,
                weather["temp_c"]    if weather else None,
                weather["humidity"]  if weather else None,
                weather["wind_kph"]  if weather else None,
                weather["condition"] if weather else None,
                weather["emoji"]     if weather else None,
            ),
        ).fetchone()
        
        run_id = None
        if row:
            if hasattr(row, 'keys') and 'id' in row.keys():
                run_id = row['id']
            elif isinstance(row, dict) and 'id' in row:
                run_id = row['id']
            else:
                run_id = row[0]
                
        conn.commit()
        conn.close()
        
        # ⭐ NEW: Update stats and evaluate badges
        try:
            update_user_stats(user["id"], date_str, distance, operation='add')
        except Exception as st_err:
            print(f"Stats update warning: {st_err}")
            
        try:
            if run_id:
                newly_awarded = evaluate_badges_for_user(user["id"], run_id)
                if newly_awarded:
                    session['new_badges'] = newly_awarded
        except Exception as bdg_err:
            print(f"Badge eval warning: {bdg_err}")

        _check_level_up(user, distance)

        try:
            if run_id:
                from services.challenge_service import evaluate_challenges_for_user as _eval_challenges
                from services.goal_service import evaluate_goals_for_user as _eval_goals
                _eval_challenges(user["id"], run_id)
                _eval_goals(user["id"], run_id)
        except Exception as ch_err:
            print(f"Challenge eval warning: {ch_err}")
            
        _notify_followers_of_run(user, distance, date_str)
        log_activity(user["id"], "RUN_ADDED", f"Added run: {distance}km in {time_min}min")
        flash("Run logged! 🎉", "success")

    except Exception as e:
        import traceback
        print("CRITICAL ERROR IN /add:", traceback.format_exc())
        flash("Something went wrong while saving your run. Please try again.", "danger")

    return redirect(url_for("index", open=run_id)) if 'run_id' in locals() and run_id else redirect(url_for("index"))




# ---------- VOICE QUICK-LOG ----------
@app.route("/api/voice-quick-log", methods=["POST"])
def voice_quick_log():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "No user"}), 401
    try:
        data = request.get_json(silent=True) or {}
        distance = float(data.get("distance", 0))
        time_min = float(data.get("time_minutes", 0))
        if distance <= 0 or time_min <= 0 or distance > 1000:
            return jsonify({"error": "Invalid distance or duration"}), 400
        pace = round(time_min / distance, 2)
        weight = user["weight"] or 70.0
        calories = round(0.7 * weight * distance, 0)
        conn = get_db()
        now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        row = conn.execute(
            """INSERT INTO runs (user_id, distance_km, time_min, pace, calories, date, run_type, notes, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            RETURNING id""",
            (user["id"], distance, time_min, pace, int(calories), datetime.now().strftime("%Y-%m-%d"), "easy", "Voice quick log", now_str)
        ).fetchone()
        conn.commit()
        run_id = None
        if row:
            if hasattr(row, 'keys') and 'id' in row.keys():
                run_id = row['id']
            elif isinstance(row, dict) and 'id' in row:
                run_id = row['id']
            else:
                run_id = row[0]
        conn.close()
        return jsonify({"success": True, "run_id": run_id, "pace": pace, "calories": int(calories)})
    except Exception as e:
        return jsonify({"error": str(e)}), 500


# ---------- SYNC OFFLINE RUN ----------

@app.route("/api/sync-run", methods=["POST"])
@csrf.exempt  # Called by the Service Worker offline background-sync queue; cannot carry a CSRF token
def sync_offline_run():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    
    try:
        data = request.get_json()
        
        # Extract data
        temp_id = data.get('tempId')
        date_str = data.get('date', '').strip()
        distance_str = str(data.get('distance', ''))
        time_str = str(data.get('time', ''))
        notes = data.get('notes', '').strip()
        client_hash = data.get('hash', '')
        
        # Validate required fields
        if not all([temp_id, date_str, distance_str, time_str]):
            return jsonify({"error": "Missing required fields"}), 400
        
        # Hash verification for data integrity
        server_hash_data = f"{date_str}{distance_str}{time_str}"
        server_hash = hashlib.sha256(server_hash_data.encode()).hexdigest()
        
        if client_hash and client_hash != server_hash:
            log_activity(user["id"], "SYNC_FAIL", f"Hash mismatch for {temp_id}")
            return jsonify({"error": "Data integrity check failed"}), 400
        
        # Parse and validate date
        try:
            run_date = datetime.strptime(date_str, "%Y-%m-%d").date()
            today_date = datetime.now().date()
            if run_date > today_date:
                log_activity(user["id"], "SYNC_FAIL", f"Future date in {temp_id}: {date_str}")
                return jsonify({"error": "Cannot sync future-dated runs"}), 400
        except ValueError:
            return jsonify({"error": "Invalid date format"}), 400
        
        # Parse and validate distance
        try:
            distance = float(distance_str)
            if distance <= 0:
                log_activity(user["id"], "SYNC_FAIL", f"Invalid distance in {temp_id}: {distance}")
                return jsonify({"error": "Distance must be greater than 0"}), 400
        except (ValueError, TypeError):
            return jsonify({"error": "Invalid distance value"}), 400
        
        # Parse and validate time
        try:
            time_min = float(time_str)
            if time_min <= 0:
                log_activity(user["id"], "SYNC_FAIL", f"Invalid time in {temp_id}: {time_min}")
                return jsonify({"error": "Duration must be greater than 0"}), 400
        except (ValueError, TypeError):
            return jsonify({"error": "Invalid duration value"}), 400
        
        # Validate pace
        pace_check = time_min / distance
        if pace_check > 30:
            log_activity(user["id"], "SYNC_FAIL", f"Unrealistic pace in {temp_id}: {pace_check:.2f}")
            return jsonify({"error": "Pace too slow (> 30 min/km)"}), 400
        if pace_check < 2:
            log_activity(user["id"], "SYNC_FAIL", f"Unrealistic pace in {temp_id}: {pace_check:.2f}")
            return jsonify({"error": "Pace too fast (< 2 min/km)"}), 400
        
        # Check for duplicate run
        conn = get_db()
        existing = conn.execute(
            "SELECT id FROM runs WHERE user_id = ? AND date = ? AND distance_km = ? AND time_min = ?",
            (user["id"], date_str, distance, time_min)
        ).fetchone()
        
        if existing:
            conn.close()
            log_activity(user["id"], "SYNC_DUPLICATE", f"Duplicate run detected: {temp_id}")
            return jsonify({"error": "Duplicate run detected"}), 409
        
        
        # Calculate stats
        user_weight = user["weight"] if user["weight"] is not None else DEFAULT_WEIGHT
        pace, calories = calc_stats(distance, time_min, user_weight)
        
        # Generate AI insight for this run
        insight = generate_run_insight(user["id"], distance, pace, calories)

        # Feature: Friend Mentions — save notes from offline sync payload too
        notes = data.get('notes', '').strip()[:500] or None
        
        # Insert run with insight and notes
        row = conn.execute(
            """
            INSERT INTO runs (user_id, date, distance_km, time_min, pace, calories, insight, notes, created_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            RETURNING id
            """,
            (user["id"], date_str, distance, time_min, pace, calories, insight, notes,
             datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
        ).fetchone()
        run_id = row["id"] if isinstance(row, dict) else row[0]
        conn.commit()
        conn.close()
        
        # ⭐ Update stats and evaluate badges
        update_user_stats(user["id"], date_str, distance, operation='add')
        newly_awarded = evaluate_badges_for_user(user["id"], run_id)
        level_up = _check_level_up(user, distance)
        try:
            from services.challenge_service import evaluate_challenges_for_user as _eval_challenges
            from services.goal_service import evaluate_goals_for_user as _eval_goals
            _eval_challenges(user["id"], run_id)
            _eval_goals(user["id"], run_id)
        except Exception as ch_err:
            print(f"Challenge eval warning (sync): {ch_err}")
        
        _notify_followers_of_run(user, distance, date_str)

        # Log successful sync
        log_activity(user["id"], "SYNC_SUCCESS", f"Synced offline run: {temp_id} -> {run_id}")
        
        return jsonify({
            "success": True,
            "runId": run_id,
            "insight": insight,
            "newBadges": newly_awarded,  # Include badges in response
            "levelUp": level_up,
            "message": "Run synced successfully"
        }), 200

        
    except Exception as e:
        log_activity(user["id"], "SYNC_ERROR", f"Sync exception: {str(e)}")
        return jsonify({"error": f"Server error: {str(e)}"}), 500


# ---------- WEEKLY GOAL ----------



# ---------- DELETE RUN ----------

@app.route("/delete/<int:run_id>", methods=["POST"])
def delete_run(run_id):
    if not require_login():
        return redirect(url_for("login"))

    conn = get_db()
    
    # Get run details before deletion
    run = conn.execute(
        "SELECT * FROM runs WHERE id = ? AND user_id = ?",
        (run_id, session["user_id"])
    ).fetchone()
    
    if run:
        conn.execute(
            "DELETE FROM runs WHERE id = ? AND user_id = ?",
            (run_id, session["user_id"]),
        )
        conn.commit()

        # ⭐ Update stats (but don't revoke badges - industry standard)
        update_user_stats(
            session["user_id"],
            run['date'],
            run['distance_km'],
            operation='delete'
        )

        # Re-sum challenge progress from scratch (deleted run may lower progress)
        try:
            from services.challenge_service import evaluate_challenges_for_user as _eval_challenges
            from services.goal_service import evaluate_goals_for_user as _eval_goals
            _eval_challenges(session["user_id"])
            _eval_goals(session["user_id"])
        except Exception as ch_err:
            print(f"Challenge eval warning after delete: {ch_err}")

    conn.close()
    return redirect(url_for("index"))



# ---------- SETTINGS ----------

@app.route("/settings", methods=["GET"])
def settings():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()

    # Safely read new columns that may not exist in older DB schemas
    user_email = (user["email"] if "email" in user.keys() else None) or (user["google_email"] if "google_email" in user.keys() else None)
    user_email_pref = user["email_weekly_summary"] if "email_weekly_summary" in user.keys() else 1
    user_monthly_email_pref = user["email_monthly_summary"] if "email_monthly_summary" in user.keys() else 1
    try:
        pref_conn = get_db()
        monthly_report_pref = pref_conn.execute(
            "SELECT popup_enabled FROM monthly_report_preferences WHERE user_id = ?", (user["id"],)
        ).fetchone()
        pref_conn.close()
    except Exception:
        monthly_report_pref = None
    home_city = user["home_city"] if "home_city" in user.keys() else None
    google_id = user["google_id"] if "google_id" in user.keys() else None
    recovery_email = user["recovery_email"] if "recovery_email" in user.keys() else None
    recovery_email_verified = user["recovery_email_verified"] if "recovery_email_verified" in user.keys() else 0

    weight_val = user["weight"] if "weight" in user.keys() else None
    height_val = user["height"] if "height" in user.keys() else None
    bmi = None
    if weight_val and height_val and float(height_val) > 0:
        bmi = round(float(weight_val) / ((float(height_val) / 100) ** 2), 1)

    return render_template(
        "settings.html",
        display_name=user["display_name"] or user["username"],
        username=user["username"],
        profile_emoji=user["profile_emoji"] if "profile_emoji" in user.keys() else "🏃🏻",
        weight=weight_val,
        height=height_val,
        bmi=bmi,
        theme=user["theme"] or "system",
        email=user_email,
        email_weekly_summary=user_email_pref if user_email_pref is not None else 1,
        email_monthly_summary=user_monthly_email_pref if user_monthly_email_pref is not None else 1,
        monthly_report_popup_enabled=monthly_report_pref["popup_enabled"] if monthly_report_pref else 1,
        home_city=home_city,
        google_id=google_id,
        recovery_email=recovery_email,
        recovery_email_verified=recovery_email_verified,
    )


def _monthly_report_user():
    user = get_current_user()
    if not user:
        return None, (jsonify({"error": "Authentication required."}), 401)
    return user, None


def _monthly_report_timezone(value):
    if not value or len(value) > 64:
        return None
    try:
        return ZoneInfo(value)
    except (ZoneInfoNotFoundError, ValueError):
        return None


def _monthly_report_local_today(timezone_name=None, local_date=None):
    if local_date:
        try:
            return date.fromisoformat(local_date)
        except (TypeError, ValueError):
            pass
    zone = _monthly_report_timezone(timezone_name)
    return datetime.now(zone).date() if zone else get_today()


def _monthly_report_window(today=None):
    """Return (year, month, is_live) for the last two days and next two days."""
    today = today or get_today()
    last_day = monthrange(today.year, today.month)[1]
    if today.day >= last_day - 1:
        return today.year, today.month, True
    if today.day in (1, 2):
        previous = today.replace(day=1) - timedelta(days=1)
        return previous.year, previous.month, False
    return None


def _monthly_report_timezone_from_request():
    return request.args.get("timezone") or request.headers.get("X-User-Timezone")


def _monthly_report_today_from_request():
    return _monthly_report_local_today(
        _monthly_report_timezone_from_request(), request.args.get("local_date")
    )


@app.route("/api/monthly-report/prompt", methods=["GET"])
def monthly_report_prompt():
    user, error = _monthly_report_user()
    if error:
        return error
    today = _monthly_report_today_from_request()
    period = _monthly_report_window(today)
    if not period:
        return jsonify({"available": False})

    year, month, is_live = period
    conn = get_db()
    preference = conn.execute(
        "SELECT popup_enabled FROM monthly_report_preferences WHERE user_id = ?", (user["id"],)
    ).fetchone()
    conn.close()
    if preference and not preference["popup_enabled"]:
        return jsonify({"available": False})

    from services.monthly_report_service import build_monthly_report
    report = build_monthly_report(
        user["id"], year, month,
        as_of_date=today if is_live else None,
        is_live=is_live,
    )
    return jsonify({"available": True, "report": report})


@app.route("/api/monthly-report/action", methods=["POST"])
def monthly_report_action():
    """Compatibility endpoint: only persistent opt-out writes a preference."""
    user, error = _monthly_report_user()
    if error:
        return error
    data = request.get_json(silent=True) or {}
    if data.get("action") != "opt_out":
        return jsonify({"error": "Viewing or postponing a report does not change preferences."}), 400
    conn = get_db()
    conn.execute(
        "INSERT INTO monthly_report_preferences (user_id, popup_enabled) VALUES (?, 0) "
        "ON CONFLICT(user_id) DO UPDATE SET popup_enabled = 0, updated_at = CURRENT_TIMESTAMP",
        (user["id"],),
    )
    conn.commit()
    conn.close()
    return jsonify({"saved": True, "popup_enabled": False})


@app.route("/monthly-report", methods=["GET"])
def monthly_report_page():
    if not require_login():
        return redirect(url_for("login"))
    user = get_current_user()
    today = _monthly_report_today_from_request()
    try:
        year = int(request.args.get("year", ""))
        month = int(request.args.get("month", ""))
        date(year, month, 1)
    except (TypeError, ValueError):
        period = _monthly_report_window(today)
        if not period:
            abort(404)
        year, month, _ = period
    period = _monthly_report_window(today)
    is_live = bool(period and period[:2] == (year, month) and period[2])
    from services.monthly_report_service import build_monthly_report
    report = build_monthly_report(user["id"], year, month, as_of_date=today if is_live else None, is_live=is_live)
    return render_template("monthly_report.html", report=report)


def _push_auth_user():
    """Return the current user for JSON push endpoints, or a safe 401."""
    user = get_current_user()
    if not user:
        return None, (jsonify({"error": "Authentication required."}), 401)
    return user, None


def _valid_push_subscription(data):
    if not isinstance(data, dict):
        return None
    endpoint = data.get("endpoint")
    keys = data.get("keys")
    if (
        not isinstance(endpoint, str)
        or not endpoint.startswith(("https://", "http://localhost"))
        or len(endpoint) > 2048
        or not isinstance(keys, dict)
    ):
        return None
    p256dh = keys.get("p256dh")
    auth = keys.get("auth")
    if (
        not isinstance(p256dh, str)
        or not isinstance(auth, str)
        or not 16 <= len(p256dh) <= 256
        or not 8 <= len(auth) <= 256
    ):
        return None
    expiration = data.get("expirationTime")
    if expiration is not None and (
        not isinstance(expiration, (int, float)) or expiration < 0
    ):
        return None
    return endpoint, p256dh, auth, expiration


@app.route("/api/push/config", methods=["GET"])
def push_config():
    public_key = os.environ.get("VAPID_PUBLIC_KEY", "").strip()
    return jsonify({
        "supported": bool(public_key),
        "publicKey": public_key or None,
    })


@app.route("/api/push/status", methods=["GET"])
def push_status():
    user, error = _push_auth_user()
    if error:
        return error
    conn = get_db()
    count = conn.execute(
        "SELECT COUNT(*) AS count FROM web_push_subscriptions WHERE user_id = ?",
        (user["id"],),
    ).fetchone()["count"]
    conn.close()
    return jsonify({"subscribed": count > 0})


@app.route("/api/push/subscribe", methods=["POST"])
def push_subscribe():
    user, error = _push_auth_user()
    if error:
        return error
    subscription = _valid_push_subscription(request.get_json(silent=True))
    if not subscription:
        return jsonify({"error": "Invalid push subscription payload."}), 400

    endpoint, p256dh, auth, expiration = subscription
    conn = get_db()
    existing = conn.execute(
        "SELECT id, user_id FROM web_push_subscriptions WHERE endpoint = ?",
        (endpoint,),
    ).fetchone()
    if existing and existing["user_id"] != user["id"]:
        conn.close()
        return jsonify({"error": "This push subscription belongs to another user."}), 409

    if existing:
        conn.execute(
            """UPDATE web_push_subscriptions
               SET p256dh = ?, auth = ?, expiration_time = ?,
                   updated_at = CURRENT_TIMESTAMP
               WHERE id = ?""",
            (p256dh, auth, expiration, existing["id"]),
        )
    else:
        conn.execute(
            """INSERT INTO web_push_subscriptions
               (user_id, endpoint, p256dh, auth, expiration_time)
               VALUES (?, ?, ?, ?, ?)""",
            (user["id"], endpoint, p256dh, auth, expiration),
        )
    conn.commit()
    conn.close()
    return jsonify({"subscribed": True}), 201 if not existing else 200


@app.route("/api/push/unsubscribe", methods=["POST"])
def push_unsubscribe():
    user, error = _push_auth_user()
    if error:
        return error
    data = request.get_json(silent=True)
    endpoint = data.get("endpoint") if isinstance(data, dict) else None
    if not isinstance(endpoint, str) or not endpoint.startswith(("https://", "http://localhost")):
        return jsonify({"error": "A valid subscription endpoint is required."}), 400

    conn = get_db()
    conn.execute(
        "DELETE FROM web_push_subscriptions WHERE user_id = ? AND endpoint = ?",
        (user["id"], endpoint),
    )
    conn.commit()
    conn.close()
    return jsonify({"subscribed": False})


@app.route("/api/push/test", methods=["POST"])
def push_test():
    user, error = _push_auth_user()
    if error:
        return error

    from services.push_service import PushConfigurationError, send_push

    conn = get_db()
    subscriptions = conn.execute(
        """SELECT endpoint, p256dh, auth, expiration_time
           FROM web_push_subscriptions WHERE user_id = ?""",
        (user["id"],),
    ).fetchall()
    delivered = 0
    removed = 0
    failed = 0
    payload = {
        "title": "RunRush test notification",
        "body": "Push notifications are working.",
        "url": "/dashboard",
        "tag": "runrush-test",
    }
    for row in subscriptions:
        subscription = {
            "endpoint": row["endpoint"],
            "keys": {"p256dh": row["p256dh"], "auth": row["auth"]},
        }
        if row["expiration_time"] is not None:
            subscription["expirationTime"] = row["expiration_time"]
        try:
            send_push(subscription, payload)
            delivered += 1
        except PushConfigurationError as exc:
            conn.close()
            return jsonify({"error": str(exc)}), 503
        except Exception as exc:
            status = getattr(getattr(exc, "response", None), "status_code", None)
            if status in (404, 410):
                conn.execute(
                    "DELETE FROM web_push_subscriptions WHERE endpoint = ?",
                    (row["endpoint"],),
                )
                removed += 1
            else:
                failed += 1
                app.logger.warning("Push delivery failed for current user: %s", type(exc).__name__)
    conn.commit()
    conn.close()
    if not subscriptions:
        return jsonify({"error": "Enable push notifications before sending a test."}), 400
    if not delivered:
        return jsonify({"error": "The test notification could not be delivered.", "removed": removed}), 502
    return jsonify({"sent": delivered, "removed": removed, "failed": failed})


@app.route("/settings/location", methods=["POST"])
def update_location():
    """Save user's home city and resolve it to lat/lng for weather fetching."""
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    city = request.form.get("home_city", "").strip()

    if not city:
        # Clear location
        conn = get_db()
        conn.execute(
            "UPDATE users SET home_city = NULL, home_latitude = NULL, home_longitude = NULL WHERE id = ?",
            (user["id"],)
        )
        conn.commit()
        conn.close()
        flash("Location cleared.", "info")
        return redirect(url_for("settings"))

    # Geocode the city via Open-Meteo (no API key needed)
    from services.weather_service import geocode_city
    lat, lng, resolved_name = geocode_city(city)

    if lat is None:
        flash(f"Could not find '{city}'. Try a larger nearby city or use format 'City, Country'.", "warning")
        return redirect(url_for("settings"))

    conn = get_db()
    conn.execute(
        "UPDATE users SET home_city = ?, home_latitude = ?, home_longitude = ? WHERE id = ?",
        (resolved_name, lat, lng, user["id"])
    )
    conn.commit()
    conn.close()

    flash(f"📍 Location set to {resolved_name} ({lat}, {lng}). Weather will now be auto-logged with your runs!", "success")
    return redirect(url_for("settings"))

@app.route("/profile", methods=["POST"])
def update_profile():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    display_name = request.form.get("display_name")
    weight = request.form.get("weight")

    conn = get_db()
    conn.execute("""
        UPDATE users SET display_name = ?, weight = ?
        WHERE id = ?
    """, (display_name, weight, user["id"]))
    conn.commit()
    conn.close()

    return redirect(url_for("index"))

BODY_METRIC_LIMITS = {"min_cm": 100, "max_cm": 250, "min_kg": 25, "max_kg": 300, "min_bmi": 10, "max_bmi": 70}
BODY_METRICS_ERROR = "These numbers don't look right. Please check your height and weight and enter the correct values."


def _check_body_metrics(height_raw, weight_raw):
    """
    Parse height (cm) / weight (kg). Returns (height, weight, error); blanks become None.
    Mirrors static/js/body-metrics.js so impossible values are rejected even without JS.
    """
    try:
        height = float(height_raw) if str(height_raw or "").strip() else None
        weight = float(weight_raw) if str(weight_raw or "").strip() else None
    except ValueError:
        return None, None, BODY_METRICS_ERROR
    lim = BODY_METRIC_LIMITS
    if height is not None and not (lim["min_cm"] <= height <= lim["max_cm"]):
        return height, weight, BODY_METRICS_ERROR
    if weight is not None and not (lim["min_kg"] <= weight <= lim["max_kg"]):
        return height, weight, BODY_METRICS_ERROR
    if height and weight and not (lim["min_bmi"] <= weight / (height / 100) ** 2 <= lim["max_bmi"]):
        return height, weight, BODY_METRICS_ERROR
    return height, weight, None


@app.route("/settings/update", methods=["POST"])
def update_settings():
    if not require_login():
        return redirect(url_for("login"))

    theme = request.form.get("theme")
    display_name = request.form.get("display_name")
    weight = request.form.get("weight")
    height = request.form.get("height")   # always centimetres (the form converts ft/in)
    profile_emoji = request.form.get("profile_emoji")

    height, weight, metrics_error = _check_body_metrics(height, weight)
    if metrics_error:
        return jsonify({"error": metrics_error}), 400

    user = get_current_user()

    conn = get_db()
    
    if profile_emoji:
        allowed_emojis = ["🏃🏻", "🏃🏻‍♀️", "🏃🏻‍♂️", "👟", "🥇", "🏅", "🎽", "💪", "🔥", "⚡", "🚀", "🤖", "🦾", "🐺", "🦅", "🐉", "🏃🏻♀️", "🏃🏻♂️"]
        if profile_emoji in allowed_emojis:
            conn.execute("""
                UPDATE users
                SET theme = ?, display_name = ?, weight = ?, height = ?, profile_emoji = ?
                WHERE id = ?
            """, (theme, display_name, weight, height, profile_emoji, user["id"]))
        else:
            flash("Invalid Profile Icon selected.", "danger")
            conn.execute("""
                UPDATE users
                SET theme = ?, display_name = ?, weight = ?, height = ?
                WHERE id = ?
            """, (theme, display_name, weight, height, user["id"]))
    else:
        conn.execute("""
            UPDATE users
            SET theme = ?, display_name = ?, weight = ?, height = ?
            WHERE id = ?
        """, (theme, display_name, weight, height, user["id"]))
        
    conn.commit()
    conn.close()

    flash("Settings updated successfully.", "success")
    return redirect(url_for("settings"))


@app.context_processor
def inject_account_theme():
    """
    The logged-in user's saved theme (or None), for partials/_theme_init.html.
    The account setting beats each device's local copy, so choosing Light in
    Settings changes the app on every device, not just the one you used.
    """
    if "user_id" not in session:
        return {"account_theme": None}
    try:
        conn = get_db()
        row = conn.execute("SELECT theme FROM users WHERE id = ?", (session["user_id"],)).fetchone()
        conn.close()
        theme = row["theme"] if row else None
    except Exception:
        theme = None
    return {"account_theme": theme if theme in ("system", "light", "dark") else None}


@app.route("/settings/theme", methods=["POST"])
def update_theme_preference():
    """
    Lightweight endpoint for the unified theme system (static/js/theme.js)
    to persist a user's theme preference (system/light/dark) without
    touching the rest of their profile fields -- deliberately separate
    from /settings/update, which expects display_name/weight/height and
    would overwrite them with blanks if called from this narrower fetch.
    No-ops silently for logged-out visitors (their preference still lives
    in localStorage on this device).
    """
    if not require_login():
        return jsonify({"success": False}), 401

    theme = request.form.get("theme", "")
    if theme not in ("system", "light", "dark"):
        return jsonify({"success": False, "error": "invalid theme"}), 400

    user = get_current_user()
    conn = get_db()
    conn.execute("UPDATE users SET theme = ? WHERE id = ?", (theme, user["id"]))
    conn.commit()
    conn.close()

    return jsonify({"success": True, "theme": theme})


# ---------- MONTHLY PROGRESS ----------


def _with_like_counts(run_dicts):
    """Add like_count to serialized runs (Runs tab "Load More")."""
    try:
        from services.social_service import get_like_counts
        counts = get_like_counts([r["id"] for r in run_dicts])
    except Exception as like_err:
        print(f"Like counts warning: {like_err}")
        counts = {}
    for r in run_dicts:
        r["like_count"] = counts.get(r["id"], 0)
    return run_dicts


@app.route("/api/runs")
def api_runs():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()

    sort_by = request.args.get("sort", "date")
    filter_opt = request.args.get("filter", "all")
    try:
        offset = int(request.args.get("offset", 0))
        limit = int(request.args.get("limit", 15))
        if offset < 0 or limit < 0:
            raise ValueError()
        limit = min(limit, 50) # safe upper bound
    except ValueError:
        return jsonify({"error": "Invalid offset or limit"}), 400

    conn = get_db()

    base_query = "SELECT * FROM runs WHERE user_id = ?"
    
    if sort_by == "distance_asc":
        order_clause = " ORDER BY distance_km ASC, date DESC"
    elif sort_by == "distance" or sort_by == "distance_desc":
        order_clause = " ORDER BY distance_km DESC, date DESC"
    elif sort_by == "time_asc":
        order_clause = " ORDER BY time_min ASC, date DESC"
    elif sort_by == "time" or sort_by == "time_desc":
        order_clause = " ORDER BY time_min DESC, date DESC"
    elif sort_by == "pace_asc":
        order_clause = " ORDER BY pace ASC, date DESC"
    elif sort_by == "pace" or sort_by == "pace_desc":
        order_clause = " ORDER BY pace DESC, date DESC"
    elif sort_by == "cal_asc":
        order_clause = " ORDER BY calories ASC, date DESC"
    elif sort_by == "cal" or sort_by == "cal_desc":
        order_clause = " ORDER BY calories DESC, date DESC"
    elif sort_by == "date_asc":
        order_clause = " ORDER BY date ASC, id ASC"
    else:  # default = date or date_desc
        order_clause = " ORDER BY date DESC, id DESC"

    runs = conn.execute(base_query + order_clause, (user["id"],)).fetchall()
    
    # Filtering (Must match main route logic)
    filtered_runs = list(runs)
    today = get_today()
    
    if filter_opt == "last7":
        cutoff = today - timedelta(days=7)
        temp = []
        for r in runs:
            try:
                d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d").date()
                if d >= cutoff:
                    temp.append(r)
            except Exception:
                continue
        filtered_runs = temp

    elif filter_opt == "month":
        current_year = today.year
        current_month = today.month
        month_runs = []
        for r in runs:
            try:
                d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d").date()
                if d.year == current_year and d.month == current_month:
                    month_runs.append(r)
            except Exception:
                continue
        filtered_runs = month_runs

    elif filter_opt == "5k10k":
        temp = []
        for r in runs:
            dist = r["distance_km"]
            if (4.5 <= dist <= 5.5) or (9.0 <= dist <= 11.0):
                temp.append(r)
        filtered_runs = temp
    
    conn.close()

    # Pagination
    total_count = len(filtered_runs)
    paginated = filtered_runs[offset : offset + limit]

    return jsonify({
        "status": "success",
        "runs": _with_like_counts([dict(r) for r in paginated]),
        "total": total_count,
        "offset": offset,
        "limit": limit
    })

@app.route("/api/monthly-progress", methods=["GET"])
def api_monthly_progress():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    
    try:
        year = int(request.args.get("year", datetime.now().year))
        month = int(request.args.get("month", datetime.now().month))
    except ValueError:
        return jsonify({"error": "Invalid year or month"}), 400

    import calendar
    _, last_day = calendar.monthrange(year, month)
    start_date = f"{year}-{month:02d}-01"
    end_date = f"{year}-{month:02d}-{last_day} 23:59:59"

    conn = get_db()
    goal_row = conn.execute(
        "SELECT target_km FROM monthly_goals WHERE user_id = ? AND year = ? AND month = ?",
        (user["id"], year, month)
    ).fetchone()
    
    target_km = goal_row["target_km"] if goal_row else None
    
    stats = conn.execute("""
        SELECT 
            COUNT(id) as run_count,
            COALESCE(SUM(distance_km), 0) as total_distance,
            COALESCE(SUM(time_min), 0) as total_time
        FROM runs 
        WHERE user_id = ? AND date >= ? AND date <= ?
    """, (user["id"], start_date, end_date)).fetchone()
    conn.close()
    
    run_count = stats["run_count"]
    total_distance = stats["total_distance"]
    total_time = stats["total_time"]
    avg_pace = 0
    if total_distance > 0:
        avg_pace = total_time / total_distance
        
    pace_m = int(avg_pace)
    pace_s = int((avg_pace - pace_m) * 60)
    pace_str = f"{pace_m}:{pace_s:02d}" if avg_pace > 0 else "--:--"
    
    time_h = int(total_time // 60)
    time_m = int(total_time % 60)
    
    return jsonify({
        "year": year,
        "month": month,
        "target_km": target_km,
        "run_count": run_count,
        "total_distance": round(total_distance, 1),
        "time_str": f"{time_h}h {time_m}m",
        "pace_str": pace_str
    })

@app.route("/api/monthly-goals", methods=["POST"])
@csrf.exempt
def api_set_monthly_goal():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    
    data = request.get_json() or {}
    try:
        year = int(data.get("year"))
        month = int(data.get("month"))
        target_km = data.get("target_km")
    except (ValueError, TypeError):
        return jsonify({"error": "Invalid data"}), 400
        
    conn = get_db()
    if target_km is None or str(target_km).strip() == "" or float(target_km) <= 0:
        conn.execute("DELETE FROM monthly_goals WHERE user_id = ? AND year = ? AND month = ?", 
                     (user["id"], year, month))
    else:
        target_km = float(target_km)
        existing = conn.execute("SELECT id FROM monthly_goals WHERE user_id = ? AND year = ? AND month = ?",
                                (user["id"], year, month)).fetchone()
        if existing:
            conn.execute("UPDATE monthly_goals SET target_km = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
                         (target_km, existing["id"]))
        else:
            conn.execute("INSERT INTO monthly_goals (user_id, year, month, target_km) VALUES (?, ?, ?, ?)",
                         (user["id"], year, month, target_km))
    conn.commit()
    conn.close()
    
    return jsonify({"success": True})


# ---------- EMAIL PREFERENCES ----------

@app.route("/settings/email", methods=["POST"])
def update_email_settings():
    """Save user's email address and weekly summary email preference."""
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    import re as _re
    email = request.form.get("email", "").strip()
    email_weekly_summary = 1 if request.form.get("email_weekly_summary") else 0
    email_monthly_summary = 1 if request.form.get("email_monthly_summary") else 0

    if email and not _re.match(r'^[^@\s]+@[^@\s]+\.[^@\s]+$', email):
        flash("Please enter a valid email address.", "danger")
        return redirect(url_for("settings"))

    conn = get_db()
    conn.execute(
        "UPDATE users SET email = ?, email_weekly_summary = ?, email_monthly_summary = ? WHERE id = ?",
        (email or None, email_weekly_summary, email_monthly_summary, user["id"])
    )
    conn.commit()
    conn.close()

    log_activity(user["id"], "UPDATE_EMAIL", "User updated email preferences")
    flash("Email preferences saved! 📧", "success")
    return redirect(url_for("settings"))


@app.route("/settings/monthly-report-preferences", methods=["POST"])
def update_monthly_report_preferences():
    if not require_login():
        return redirect(url_for("login"))
    user = get_current_user()
    enabled = 1 if request.form.get("monthly_report_popup_enabled") else 0
    conn = get_db()
    conn.execute(
        "INSERT INTO monthly_report_preferences (user_id, popup_enabled) VALUES (?, ?) "
        "ON CONFLICT(user_id) DO UPDATE SET popup_enabled = excluded.popup_enabled, updated_at = CURRENT_TIMESTAMP",
        (user["id"], enabled),
    )
    conn.commit()
    conn.close()
    flash("Monthly report popup preference saved.", "success")
    return redirect(url_for("settings"))


# ---------- WEEKLY SUMMARY EMAIL HELPER ----------

def send_weekly_summary(user_id):
    """
    Send a weekly run summary email to a user via the Resend API.
    Uses only stdlib (urllib) — no extra packages required.
    Returns True on success, False on any failure.
    """
    import json as _json
    import urllib.request as _url_req
    import urllib.error as _url_err

    api_key    = os.environ.get("RESEND_API_KEY", "")
    from_email = os.environ.get("RESEND_FROM_EMAIL", "RunRush <noreply@runrush.app>")

    if not api_key:
        return False

    conn = get_db()
    user = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    if not user:
        conn.close()
        return False

    user_email = (user["email"] if "email" in user.keys() else None) or (user["google_email"] if "google_email" in user.keys() else None)
    opt_in     = user["email_weekly_summary"] if "email_weekly_summary" in user.keys() else 1
    if not user_email or not opt_in:
        conn.close()
        return False

    today      = get_today()
    week_start, week_end = get_current_week_range(today)

    week_runs = conn.execute(
        "SELECT distance_km, time_min, pace, calories FROM runs WHERE user_id = ? AND date BETWEEN ? AND ?",
        (user_id, week_start.strftime("%Y-%m-%d"), week_end.strftime("%Y-%m-%d"))
    ).fetchall()

    stats_row = conn.execute(
        "SELECT current_streak FROM user_stats WHERE user_id = ?", (user_id,)
    ).fetchone()
    conn = get_db()
    wg_row = conn.execute("SELECT goal_km FROM user_weekly_goals WHERE user_id = ?", (user_id,)).fetchone()
    weekly_goal = wg_row["goal_km"] if wg_row else None

    name              = user["display_name"] or user["username"]
    total_km          = sum(r["distance_km"] for r in week_runs)
    total_runs_count  = len(week_runs)
    avg_pace          = (sum(r["pace"] for r in week_runs) / total_runs_count) if total_runs_count > 0 else 0
    streak            = stats_row["current_streak"] if stats_row else 0
    
    conn.close()
    goal_html = ""
    if weekly_goal and weekly_goal > 0:
        pct = min(100, round(total_km / weekly_goal * 100))
        goal_html = f"<p style='text-align:center;color:#888;margin-top:0;'>Goal: <b style='color:#F5A623'>{total_km:.1f} / {weekly_goal:.0f} km</b> — {pct}%</p>"

    if total_runs_count == 0:
        motivation = "No runs this week — next week is a fresh start! 💪"
    elif weekly_goal and total_km >= weekly_goal:
        motivation = "🎉 You crushed your weekly goal! Amazing work!"
    elif streak >= 7:
        motivation = f"🔥 {streak}-day streak! You are on fire!"
    else:
        motivation = "Keep running — every step counts! 🏃"

    html_body = f"""<!DOCTYPE html>
<html><head><meta charset="utf-8"></head>
<body style="margin:0;padding:20px;background:#09090f;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;">
<div style="max-width:560px;margin:0 auto;background:#0d0d1a;border-radius:20px;overflow:hidden;">
  <div style="background:linear-gradient(135deg,#F5A623 0%,#ff6b35 100%);padding:32px;text-align:center;">
    <div style="font-size:2.5rem;">🏃</div>
    <h1 style="margin:8px 0 4px;color:#000;font-size:1.5rem;font-weight:800;">RunRush</h1>
    <p style="margin:0;color:rgba(0,0,0,0.65);font-size:0.9rem;">Weekly Summary for {name}</p>
  </div>
  <div style="padding:28px 32px;">
    <p style="color:#ccc;margin-top:0;">{motivation}</p>
    <div style="display:grid;grid-template-columns:1fr 1fr;gap:12px;margin-bottom:20px;">
      <div style="background:#1a1a2e;border-radius:12px;padding:18px;text-align:center;">
        <div style="font-size:1.8rem;font-weight:800;color:#F5A623;">{total_km:.1f}</div>
        <div style="color:#666;font-size:0.78rem;margin-top:4px;">KM THIS WEEK</div>
      </div>
      <div style="background:#1a1a2e;border-radius:12px;padding:18px;text-align:center;">
        <div style="font-size:1.8rem;font-weight:800;color:#4dadff;">{total_runs_count}</div>
        <div style="color:#666;font-size:0.78rem;margin-top:4px;">RUNS LOGGED</div>
      </div>
      <div style="background:#1a1a2e;border-radius:12px;padding:18px;text-align:center;">
        <div style="font-size:1.8rem;font-weight:800;color:#b0ff4f;">{streak}</div>
        <div style="color:#666;font-size:0.78rem;margin-top:4px;">DAY STREAK 🔥</div>
      </div>
      <div style="background:#1a1a2e;border-radius:12px;padding:18px;text-align:center;">
        <div style="font-size:1.8rem;font-weight:800;color:#d988ff;">{avg_pace:.1f}</div>
        <div style="color:#666;font-size:0.78rem;margin-top:4px;">AVG MIN/KM</div>
      </div>
    </div>
    {goal_html}
    <div style="text-align:center;margin-top:24px;">
      <a href="https://runrush.app/dashboard"
         style="background:#F5A623;color:#000;padding:13px 32px;border-radius:50px;text-decoration:none;font-weight:700;font-size:0.95rem;">Open Dashboard →</a>
    </div>
    <p style="color:#333;font-size:0.75rem;text-align:center;margin-top:24px;">You're receiving this because you opted in.
      <a href="https://runrush.app/settings" style="color:#555;">Change preferences</a>
    </p>
  </div>
</div></body></html>"""

    payload = _json.dumps({
        "from":    from_email,
        "to":      [user_email],
        "subject": f"🏃 {name}'s Weekly Running Summary – RunRush",
        "html":    html_body
    }).encode("utf-8")

    req = _url_req.Request(
        "https://api.resend.com/emails",
        data=payload,
        headers={
            "Authorization": f"Bearer {api_key}",
            "Content-Type":  "application/json"
        }
    )
    try:
        with _url_req.urlopen(req, timeout=10) as resp:
            return resp.status == 200
    except (_url_err.URLError, Exception):
        return False


# ---------- CHANGE PIN ----------

@app.route("/settings/change-pin", methods=["POST"])
def change_pin():
    """
    Allows a logged-in user to change their numeric PIN.
    Validates current PIN, enforces 4+ digit numeric requirement,
    and checks new PIN confirmation matches.
    PINs are stored as bcrypt hashes; legacy plaintext PINs are also handled.
    """
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    current_pin = request.form.get("current_pin", "").strip()
    new_pin = request.form.get("new_pin", "").strip()
    confirm_pin = request.form.get("confirm_pin", "").strip()

    # ---- Validate current PIN (bcrypt-aware; constant-time for plaintext fallback) ----
    stored_pin = user["pin"] or ""
    if stored_pin.startswith("$2"):
        pins_match = bcrypt.check_password_hash(stored_pin, current_pin)
    else:
        import hmac as _hmac
        pins_match = _hmac.compare_digest(stored_pin.encode(), current_pin.encode())
    if not pins_match:
        flash("Current PIN is incorrect.", "danger")
        return redirect(url_for("settings"))

    # ---- Validate new PIN format ----
    if not new_pin.isdigit() or len(new_pin) < 4:
        flash("New PIN must be numeric and at least 4 digits.", "danger")
        return redirect(url_for("settings"))

    # ---- Validate confirmation ----
    if new_pin != confirm_pin:
        flash("New PIN and confirmation do not match.", "danger")
        return redirect(url_for("settings"))

    # ---- Prevent reusing the same PIN ----
    if new_pin == current_pin:
        flash("New PIN must be different from the current PIN.", "warning")
        return redirect(url_for("settings"))

    # ---- Update the PIN (stored as bcrypt hash) ----
    hashed_new_pin = bcrypt.generate_password_hash(new_pin)
    conn = get_db()
    conn.execute("UPDATE users SET pin = ? WHERE id = ?", (hashed_new_pin, user["id"]))
    conn.commit()
    conn.close()

    log_activity(user["id"], "CHANGE_PIN", "User changed PIN")
    flash("PIN changed successfully! 🔒", "success")
    return redirect(url_for("settings"))


#-------Clear Data-----#
@app.route("/settings/clear-data", methods=["POST"])
def clear_data():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    conn = get_db()
    # Delete all runs for this user
    conn.execute("DELETE FROM runs WHERE user_id = ?", (user["id"],))
    conn.commit()
    conn.close()

    # Zero the cached total and streaks to match the now-empty run history
    recalculate_user_stats(user["id"])

    # Reset challenge progress to 0 (runs gone, progress must reflect that)
    try:
        from services.challenge_service import reset_challenge_progress
        reset_challenge_progress(user["id"])
    except Exception as ch_err:
        print(f"Challenge reset warning after clear_data: {ch_err}")

    log_activity(user["id"], "CLEAR_DATA", "User cleared all their runs")
    flash("All your logged runs have been cleared! 🗑️", "success")
    return redirect(url_for("settings"))



#-------Delete acc-----#
@app.route("/delete-account", methods=["POST"])
def delete_account():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    conn = get_db()

    # 1) Delete challenge progress (must be before user row deletion)
    conn.execute("DELETE FROM user_challenge_progress WHERE user_id = ?", (user["id"],))

    # 2) Delete all runs for this user
    conn.execute("DELETE FROM runs WHERE user_id = ?", (user["id"],))

    # 3) Delete the user record
    conn.execute("DELETE FROM users WHERE id = ?", (user["id"],))

    conn.commit()
    conn.close()

    # 4) Clear session and send back to login
    session.clear()
    return redirect(url_for("login"))

# ---------- EDIT RUN ---------

@app.route("/edit/<int:run_id>", methods=["GET", "POST"])
def edit_run(run_id):
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    conn = get_db()
    run = conn.execute(
        "SELECT * FROM runs WHERE id = ? AND user_id = ?",
        (run_id, session["user_id"])
    ).fetchone()
    
    if not run:
        conn.close()
        return redirect(url_for("index"))
    
    # Handle POST request (update)
    if request.method == "POST":
        try:
            distance = float(request.form["distance"])
            time_min = float(request.form["time"])
            run_date = request.form.get("date")

            if distance <= 0 or time_min <= 0:
                conn.close()
                flash("Distance and time must be positive.", "danger")
                return redirect(url_for("edit_run", run_id=run_id))


            user_weight = user["weight"] if user["weight"] is not None else DEFAULT_WEIGHT
            pace, calories = calc_stats(distance, time_min, user_weight)
            
            # Regenerate insight with updated data
            insight = generate_run_insight(user["id"], distance, pace, calories)

            conn.execute("""
                UPDATE runs
                SET date = ?, distance_km = ?, time_min = ?, pace = ?, calories = ?, insight = ?
                WHERE id = ? AND user_id = ?
            """, (run_date, distance, time_min, pace, calories, insight, run_id, session["user_id"]))
            conn.commit()
            conn.close()

            # Distance and date may both have changed: resync the cached total and streaks
            recalculate_user_stats(user["id"])

            # Re-evaluate badges (pre-existing gap: editing distance up should trigger badges)
            try:
                evaluate_badges_for_user(user["id"], run_id)
            except Exception as bdg_err:
                print(f"Badge eval warning after edit: {bdg_err}")

            # Re-sum challenge progress from scratch (handles distance edits up AND down)
            try:
                from services.challenge_service import evaluate_challenges_for_user as _eval_challenges
                from services.goal_service import evaluate_goals_for_user as _eval_goals
                _eval_challenges(user["id"], run_id)
                _eval_goals(user["id"], run_id)
            except Exception as ch_err:
                print(f"Challenge eval warning after edit: {ch_err}")

            flash("Run updated successfully!", "success")
            return redirect(url_for("index"))

        except (ValueError, TypeError):
            conn.close()
            flash("Invalid distance or time.", "danger")
            return redirect(url_for("edit_run", run_id=run_id))

    # Handle GET request (show form)
    conn.close()
    display_name = user["display_name"] or user["username"]
    user_weight = user["weight"] if user["weight"] is not None else DEFAULT_WEIGHT

    return render_template(
        "edit.html",
        run=run,
        weight=user_weight,
        username=user["username"],
        display_name=display_name,
        theme=user["theme"] or "system"
    )


# ---------- EXPORT / LEADERBOARD ----------

@app.route("/export")
def export_runs():
    if not require_login():
        return redirect(url_for("login"))
    
    user = get_current_user()
    conn = get_db()
    runs = conn.execute(
        "SELECT date, distance_km, time_min, pace, calories, run_type FROM runs WHERE user_id = ? ORDER BY date DESC",
        (user["id"],)
    ).fetchall()
    conn.close()

    # Create CSV in memory
    si = io.StringIO()
    cw = csv.writer(si)
    cw.writerow(["Date", "Distance (km)", "Time (min)", "Pace (min/km)", "Calories", "Run Type"])
    for r in runs:
        cw.writerow([r["date"], r["distance_km"], r["time_min"], r["pace"], r["calories"], r["run_type"] or "easy"])
    
    output = make_response(si.getvalue())
    output.headers["Content-Disposition"] = "attachment; filename=runrush_history.csv"
    output.headers["Content-type"] = "text/csv"
    return output


# ---------- STRAVA CSV IMPORT API ----------

def parse_duration_str(dur_str):
    if not dur_str:
        return None
    dur_str = str(dur_str).strip()
    if ':' in dur_str:
        parts = dur_str.split(':')
        try:
            if len(parts) == 3:
                h, m, s = float(parts[0]), float(parts[1]), float(parts[2])
                return round(h * 60.0 + m + s / 60.0, 2)
            elif len(parts) == 2:
                m, s = float(parts[0]), float(parts[1])
                return round(m + s / 60.0, 2)
        except ValueError:
            return None
    try:
        val = float(dur_str.replace(',', ''))
        if val <= 0:
            return None
        if val > 300: # given in seconds
            return round(val / 60.0, 2)
        return round(val, 2)
    except ValueError:
        return None


def parse_distance_str(dist_str):
    if not dist_str:
        return None
    dist_str_clean = str(dist_str).strip().lower()
    is_miles = 'mi' in dist_str_clean or 'mile' in dist_str_clean
    num_str = re.sub(r'[^\d\.]', '', dist_str_clean)
    try:
        val = float(num_str)
        if val <= 0:
            return None
        if is_miles:
            return round(val * 1.60934, 2)
        if val > 150: # distance given in meters
            return round(val / 1000.0, 2)
        return round(val, 2)
    except ValueError:
        return None


def parse_strava_date(date_raw):
    if not date_raw:
        return None
    date_raw = str(date_raw).strip()
    d = parse_date_val(date_raw)
    if d:
        return d.strftime("%Y-%m-%d")
    
    strava_formats = [
        "%b %d, %Y, %I:%M:%S %p",
        "%b %d, %Y, %I:%M %p",
        "%b %d, %Y",
        "%d %b %Y, %H:%M:%S",
        "%d %b %Y",
        "%Y-%m-%d %H:%M:%S",
        "%Y-%m-%d",
        "%Y/%m/%d %H:%M:%S",
        "%Y/%m/%d"
    ]
    for fmt in strava_formats:
        try:
            dt = datetime.strptime(date_raw, fmt)
            return dt.strftime("%Y-%m-%d")
        except ValueError:
            pass
    return None


def parse_strava_csv(csv_text, user_id, user_weight):
    si = io.StringIO(csv_text)
    try:
        reader = csv.DictReader(si)
    except Exception:
        raise ValueError("Invalid CSV file structure")

    if not reader.fieldnames:
        raise ValueError("CSV file is empty or has no header row")

    def _norm_col(name):
        # Normalize header names so both "activity date" (spaces) and
        # "activity_date" (underscores, as in real Strava exports) match.
        return name.strip().lower().replace("_", " ")

    headers_lower = {_norm_col(fn): fn for fn in reader.fieldnames if fn}

    def find_col(candidates):
        for c in candidates:
            key = _norm_col(c)
            if key in headers_lower:
                return headers_lower[key]
        return None

    date_col = find_col(["activity date", "date", "start time", "activity date/time"])
    dist_col = find_col(["distance", "distance (km)", "distance (m)", "distance_km"])
    time_col = find_col(["moving time", "elapsed time", "time (min)", "duration", "time", "time_min"])
    type_col = find_col(["activity type", "type", "sport", "run_type"])
    name_col = find_col(["activity name", "title", "name"])
    desc_col = find_col(["activity description", "description", "notes"])

    if not date_col or not dist_col or not time_col:
        cols_summary = ", ".join(reader.fieldnames[:6]) if reader.fieldnames else "none"
        raise ValueError(
            "Missing required columns in CSV. File must contain Date, Distance, and Duration/Time columns. "
            f"Found columns: {cols_summary}"
        )

    valid_runs = []
    duplicates = []
    skipped_non_run = []
    invalids = []
    seen_in_import = set()

    conn = get_db()
    existing_rows = conn.execute(
        "SELECT date, distance_km, time_min FROM runs WHERE user_id = ?",
        (user_id,)
    ).fetchall()
    conn.close()

    existing_runs_list = []
    for er in existing_rows:
        existing_runs_list.append((
            str(er['date']),
            float(er['distance_km']),
            float(er['time_min'])
        ))

    row_index = 0
    running_activities = {"run", "trail run", "virtual run", "treadmill", "race", "running"}

    for raw_row in reader:
        row_index += 1
        
        act_name = (raw_row.get(name_col) or "").strip() if name_col else ""
        act_desc = (raw_row.get(desc_col) or "").strip() if desc_col else ""
        act_type_raw = (raw_row.get(type_col) or "").strip() if type_col else ""
        act_type_clean = act_type_raw.lower()

        notes_parts = [p for p in [act_name, act_desc] if p]
        notes = " - ".join(notes_parts)[:500] or "Imported from Strava"

        is_run = False
        if act_type_clean in running_activities:
            is_run = True
        elif not act_type_raw and ("run" in act_name.lower() or "jog" in act_name.lower()):
            is_run = True
        elif not type_col:
            is_run = True

        if not is_run:
            skipped_non_run.append({
                "row": row_index,
                "name": act_name or f"Row {row_index}",
                "type": act_type_raw or "Non-run",
                "reason": f"Non-running activity ({act_type_raw or 'Other'})"
            })
            continue

        date_str = parse_strava_date(raw_row.get(date_col))
        if not date_str:
            invalids.append({
                "row": row_index,
                "name": act_name or f"Row {row_index}",
                "reason": f"Invalid date: '{raw_row.get(date_col)}'"
            })
            continue

        try:
            r_date = datetime.strptime(date_str, "%Y-%m-%d").date()
            if r_date > date.today():
                invalids.append({
                    "row": row_index,
                    "name": act_name or f"Row {row_index}",
                    "reason": f"Future date not allowed: '{date_str}'"
                })
                continue
        except ValueError:
            invalids.append({
                "row": row_index,
                "name": act_name or f"Row {row_index}",
                "reason": f"Invalid date format: '{date_str}'"
            })
            continue

        distance_km = parse_distance_str(raw_row.get(dist_col))
        if not distance_km or distance_km <= 0:
            invalids.append({
                "row": row_index,
                "name": act_name or f"Row {row_index}",
                "reason": f"Invalid distance: '{raw_row.get(dist_col)}'"
            })
            continue
        if distance_km > 1000:
            invalids.append({
                "row": row_index,
                "name": act_name or f"Row {row_index}",
                "reason": f"Distance cannot exceed 1000 km (Found: {distance_km} km)"
            })
            continue

        time_min = parse_duration_str(raw_row.get(time_col))
        if not time_min or time_min <= 0:
            invalids.append({
                "row": row_index,
                "name": act_name or f"Row {row_index}",
                "reason": f"Invalid duration: '{raw_row.get(time_col)}'"
            })
            continue

        pace_val = time_min / distance_km
        if pace_val < 2.0 or pace_val > 30.0:
            invalids.append({
                "row": row_index,
                "name": act_name or f"Row {row_index}",
                "reason": f"Unrealistic pace ({pace_val:.2f} min/km)"
            })
            continue

        pace, calories = calc_stats(distance_km, time_min, user_weight)

        lower_notes = notes.lower()
        if "race" in lower_notes or "marathon" in lower_notes:
            run_type = "race"
        elif "interval" in lower_notes or "workout" in lower_notes or "reps" in lower_notes:
            run_type = "interval"
        elif "tempo" in lower_notes or "threshold" in lower_notes:
            run_type = "tempo"
        elif "long" in lower_notes or distance_km >= 15.0:
            run_type = "long"
        else:
            run_type = "easy"

        is_dup = False
        for ex_date, ex_dist, ex_time in existing_runs_list:
            if ex_date == date_str and abs(ex_dist - distance_km) < 0.05 and abs(ex_time - time_min) < 0.5:
                is_dup = True
                break

        dup_key = (date_str, round(distance_km, 2), round(time_min, 1))
        if dup_key in seen_in_import:
            is_dup = True

        run_item = {
            "date": date_str,
            "distance": distance_km,
            "time": time_min,
            "pace": pace,
            "calories": calories,
            "run_type": run_type,
            "notes": notes,
            "name": act_name or f"{distance_km} km Run"
        }

        if is_dup:
            duplicates.append(run_item)
        else:
            seen_in_import.add(dup_key)
            valid_runs.append(run_item)

    return {
        "total_rows": row_index,
        "valid_count": len(valid_runs),
        "skipped_non_run_count": len(skipped_non_run),
        "duplicate_count": len(duplicates),
        "invalid_count": len(invalids),
        "valid_runs": valid_runs,
        "duplicates": duplicates,
        "skipped": skipped_non_run,
        "invalids": invalids
    }


@app.route("/api/parse-import", methods=["POST"])
def parse_import():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    if 'file' not in request.files:
        return jsonify({"error": "No file uploaded"}), 400
    
    file = request.files['file']
    if not file or not file.filename:
        return jsonify({"error": "No file selected"}), 400

    if not file.filename.lower().endswith('.csv'):
        return jsonify({"error": "Only CSV files (.csv) are supported"}), 400

    try:
        file.seek(0, os.SEEK_END)
        size_bytes = file.tell()
        file.seek(0)

        if size_bytes > 5 * 1024 * 1024:
            return jsonify({"error": "File size exceeds limit (max 5 MB)"}), 400

        content = file.read().decode('utf-8-sig', errors='replace')
        
        user_weight = user["weight"] if "weight" in user.keys() and user["weight"] is not None else DEFAULT_WEIGHT
        res = parse_strava_csv(content, user["id"], user_weight)
        
        return jsonify({"success": True, "data": res}), 200

    except ValueError as ve:
        return jsonify({"error": str(ve)}), 400
    except Exception as e:
        import traceback
        print("PARSE IMPORT ERROR:", traceback.format_exc())
        return jsonify({"error": f"Failed to parse CSV file: {str(e)}"}), 500


@app.route("/api/confirm-import", methods=["POST"])
def confirm_import():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    payload = request.get_json() or {}
    runs_to_import = payload.get("runs", [])

    if not runs_to_import or not isinstance(runs_to_import, list):
        return jsonify({"error": "No runs provided for import"}), 400

    user_weight = user["weight"] if user["weight"] is not None else DEFAULT_WEIGHT
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    conn = get_db()
    inserted_ids = []
    
    try:
        for item in runs_to_import:
            date_str = str(item.get("date", "")).strip()
            distance = float(item.get("distance", 0))
            time_min = float(item.get("time", 0))
            notes = str(item.get("notes", "")).strip()[:500] or "Imported from Strava"
            run_type = str(item.get("run_type", "easy")).strip()
            if run_type not in {"easy", "tempo", "long", "interval", "race"}:
                run_type = "easy"

            pace, calories = calc_stats(distance, time_min, user_weight)
            insight = f"Imported from Strava 🏃 ({distance} km in {time_min} min)"

            row = conn.execute(
                """
                INSERT INTO runs (
                    user_id, date, distance_km, time_min, pace, calories, created_at,
                    insight, run_type, notes
                )
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                RETURNING id
                """,
                (user["id"], date_str, distance, time_min, pace, calories, now_str, insight, run_type, notes)
            ).fetchone()

            r_id = None
            if row:
                if hasattr(row, 'keys') and 'id' in row.keys():
                    r_id = row['id']
                elif isinstance(row, dict) and 'id' in row:
                    r_id = row['id']
                else:
                    r_id = row[0]
            if r_id:
                inserted_ids.append(r_id)

        conn.commit()
        conn.close()

    except Exception as e:
        conn.rollback()
        conn.close()
        import traceback
        print("BATCH IMPORT ROLLBACK:", traceback.format_exc())
        return jsonify({"error": f"Database import failed. All changes rolled back: {str(e)}"}), 500

    all_awarded = []
    if inserted_ids:
        try:
            latest_date = max(str(r.get("date", "")) for r in runs_to_import)
            total_dist_added = sum(float(r.get("distance", 0)) for r in runs_to_import)
            update_user_stats(user["id"], latest_date, total_dist_added, operation='add')
            _check_level_up(user, total_dist_added)
        except Exception as st_err:
            print(f"Stats update warning after import: {st_err}")

        try:
            for r_id in inserted_ids[:10]:
                awarded = evaluate_badges_for_user(user["id"], r_id)
                all_awarded.extend(awarded)
            all_awarded = list(set(all_awarded))
            if all_awarded:
                session['new_badges'] = all_awarded
        except Exception as bdg_err:
            print(f"Badge eval warning after import: {bdg_err}")

        try:
            from services.challenge_service import evaluate_challenges_for_user as _eval_challenges
            from services.goal_service import evaluate_goals_for_user as _eval_goals
            _eval_challenges(user["id"])
            _eval_goals(user["id"])
        except Exception as ch_err:
            print(f"Challenge eval warning after CSV import: {ch_err}")

        log_activity(user["id"], "STRAVA_IMPORT", f"Imported {len(inserted_ids)} runs from Strava CSV")

    return jsonify({
        "success": True,
        "imported_count": len(inserted_ids),
        "new_badges": all_awarded,
        "message": f"Successfully imported {len(inserted_ids)} runs!"
    }), 200



# ---------- SCREENSHOT IMPORT ----------

_WEEKDAYS = ["monday", "tuesday", "wednesday", "thursday", "friday", "saturday", "sunday"]
_WEEKDAY_ALIASES = {
    "mon": "monday",
    "tue": "tuesday",
    "tues": "tuesday",
    "wed": "wednesday",
    "thu": "thursday",
    "thur": "thursday",
    "thurs": "thursday",
    "fri": "friday",
    "sat": "saturday",
    "sun": "sunday",
}
_MILES_TO_KM = 1.609344
_FEET_TO_M = 0.3048
_MAX_SCREENSHOT_DISTANCE_KM = 500.0
_MAX_SCREENSHOT_DURATION_SECONDS = 24 * 60 * 60
_MIN_SCREENSHOT_DURATION_SECONDS = 10


def _screenshot_reference_date(client_date_str):
    """
    The user's local "today", sent by the browser. Falls back to the server's date
    when missing/invalid or more than a day away from it (timezones differ by < 1 day).
    """
    server_today = date.today()
    try:
        client_today = datetime.strptime(str(client_date_str or "").strip(), "%Y-%m-%d").date()
    except ValueError:
        return server_today
    if abs((client_today - server_today).days) > 1:
        return server_today
    return client_today


def _resolve_screenshot_date(raw_date, relative, today):
    """
    Turn what the model read off the screenshot into a YYYY-MM-DD string.
    Relative labels ("Today", "Yesterday", "Monday") are resolved against `today`;
    an absolute date is kept if valid and not in the future; otherwise default to today.
    """
    rel = _extract_relative_label(relative, raw_date)
    if rel == "today":
        return today.isoformat()
    if rel == "yesterday":
        return (today - timedelta(days=1)).isoformat()
    if rel in _WEEKDAYS:
        # Apps show a weekday name for runs earlier in the past week, never for today
        days_back = (today.weekday() - _WEEKDAYS.index(rel)) % 7 or 7
        return (today - timedelta(days=days_back)).isoformat()

    parsed = _parse_loose_screenshot_date(raw_date, today)
    if parsed:
        return parsed.isoformat()
    return today.isoformat()


def _extract_relative_label(*values):
    for value in values:
        txt = str(value or "").strip().lower()
        if not txt:
            continue
        if "today" in txt:
            return "today"
        if "yesterday" in txt:
            return "yesterday"
        for weekday in _WEEKDAYS:
            if re.search(rf"\b{weekday}\b", txt):
                return weekday
        for short, weekday in _WEEKDAY_ALIASES.items():
            if re.search(rf"\b{short}\b", txt):
                return weekday
    return ""


def _parse_loose_screenshot_date(raw_date, today):
    txt = str(raw_date or "").strip()
    if not txt:
        return None
    for fmt in ("%Y-%m-%d", "%b %d, %Y", "%B %d, %Y", "%d %b %Y", "%d %B %Y", "%m/%d/%Y", "%d/%m/%Y"):
        try:
            parsed = datetime.strptime(txt, fmt).date()
            if parsed <= today:
                return parsed
        except ValueError:
            continue
    for fmt in ("%b %d", "%B %d", "%m/%d", "%d/%m"):
        try:
            parsed = datetime.strptime(txt, fmt).date().replace(year=today.year)
            if parsed > today:
                parsed = parsed.replace(year=parsed.year - 1)
            return parsed
        except ValueError:
            continue
    return None


def _to_finite_float(value):
    if value is None:
        return None
    txt = str(value).strip() if isinstance(value, str) else value
    if txt == "":
        return None
    try:
        parsed = float(txt)
    except (TypeError, ValueError):
        return None
    return parsed if math.isfinite(parsed) else None


def _extract_number_from_text(value):
    txt = str(value or "").replace(",", "")
    match = re.search(r"(-?\d+(?:\.\d+)?)", txt)
    return _to_finite_float(match.group(1)) if match else None


def _normalize_distance_unit(*values):
    txt = " ".join(str(v or "").lower() for v in values)
    if any(token in txt for token in ("mile", "miles", " mi", "mi ", "/mi")):
        return "mi"
    if any(token in txt for token in ("km", "kilometer", "kilometre")):
        return "km"
    return ""


def _normalize_pace_unit(*values):
    txt = " ".join(str(v or "").lower() for v in values)
    if any(token in txt for token in ("/mile", "min/mile", "per mile", "/mi", "mi pace")):
        return "mi"
    if any(token in txt for token in ("/km", "min/km", "per km", "km pace")):
        return "km"
    return ""


def _parse_duration_to_seconds(value):
    if value is None:
        return None

    numeric = _to_finite_float(value)
    if numeric is not None:
        return int(round(numeric))

    txt = str(value).strip().lower()
    if not txt:
        return None

    if ":" in txt:
        parts = txt.split(":")
        try:
            if len(parts) == 3:
                return int(float(parts[0]) * 3600 + float(parts[1]) * 60 + float(parts[2]))
            if len(parts) == 2:
                return int(float(parts[0]) * 60 + float(parts[1]))
        except ValueError:
            return None

    hours = re.search(r"(\d+(?:\.\d+)?)\s*h", txt)
    minutes = re.search(r"(\d+(?:\.\d+)?)\s*m", txt)
    seconds = re.search(r"(\d+(?:\.\d+)?)\s*s", txt)
    if hours or minutes or seconds:
        total = 0.0
        if hours:
            total += float(hours.group(1)) * 3600
        if minutes:
            total += float(minutes.group(1)) * 60
        if seconds:
            total += float(seconds.group(1))
        return int(total) if total > 0 else None

    base = _extract_number_from_text(txt)
    if base is None:
        return None
    if any(token in txt for token in ("hour", "hr", " h")):
        return int(base * 3600)
    if any(token in txt for token in ("minute", "min", " m")):
        return int(base * 60)
    return int(base)


def _parse_pace_to_min_per_km(value, unit_hint=""):
    txt = str(value or "").strip().lower()
    if not txt:
        return None

    unit = _normalize_pace_unit(unit_hint, txt)

    pace_min = None
    time_match = re.search(r"(\d{1,2}:\d{1,2}(?::\d{1,2})?)", txt)
    if time_match:
        parts = [float(p) for p in time_match.group(1).split(":")]
        if len(parts) == 3:
            pace_min = parts[0] * 60 + parts[1] + (parts[2] / 60.0)
        elif len(parts) == 2:
            pace_min = parts[0] + (parts[1] / 60.0)
    else:
        numeric = _extract_number_from_text(txt)
        if numeric is not None:
            pace_min = numeric

    if pace_min is None or not math.isfinite(pace_min) or pace_min <= 0:
        return None
    if unit == "mi":
        pace_min = pace_min / _MILES_TO_KM
    if pace_min < 2 or pace_min > 30:
        return None
    return pace_min


def _format_pace(min_per_km):
    if min_per_km is None or not math.isfinite(min_per_km) or min_per_km <= 0:
        return None
    mins = int(min_per_km)
    secs = int(round((min_per_km - mins) * 60))
    if secs == 60:
        mins += 1
        secs = 0
    return f"{mins}:{secs:02d}"


def _parse_distance_km(parsed):
    candidates = [
        (parsed.get("distance_km"), "km"),
        (parsed.get("distance"), parsed.get("distance_unit")),
        (parsed.get("distance_value"), parsed.get("distance_unit")),
        (parsed.get("distance_raw"), parsed.get("distance_unit")),
    ]
    for value, unit_hint in candidates:
        num = _to_finite_float(value)
        if num is None:
            num = _extract_number_from_text(value)
        if num is None or num <= 0:
            continue
        unit = _normalize_distance_unit(unit_hint, value)
        if unit == "mi":
            num *= _MILES_TO_KM
        if 0 < num <= _MAX_SCREENSHOT_DISTANCE_KM:
            return round(num, 3)
    return None


def _parse_duration_seconds(parsed):
    fields = (
        "duration_seconds",
        "moving_time_seconds",
        "elapsed_time_seconds",
        "duration",
        "moving_time",
        "elapsed_time",
        "duration_text",
    )
    for key in fields:
        seconds = _parse_duration_to_seconds(parsed.get(key))
        if seconds is None:
            continue
        if _MIN_SCREENSHOT_DURATION_SECONDS <= seconds <= _MAX_SCREENSHOT_DURATION_SECONDS:
            return seconds
    return None


def _parse_optional_int(value, min_value=None, max_value=None):
    numeric = _to_finite_float(value)
    if numeric is None:
        numeric = _extract_number_from_text(value)
    if numeric is None:
        return None
    if min_value is not None and numeric < min_value:
        return None
    if max_value is not None and numeric > max_value:
        return None
    return int(round(numeric))


def _parse_elevation_gain_m(parsed):
    raw = parsed.get("elevation_gain_m")
    value = _to_finite_float(raw)
    if value is None:
        value = _extract_number_from_text(raw)
    if value is None:
        value = _to_finite_float(parsed.get("elevation_gain"))
    if value is None:
        value = _to_finite_float(parsed.get("elevation_gain_value"))
    if value is None:
        value = _extract_number_from_text(parsed.get("elevation_gain_raw"))
    if value is None or value < 0:
        return None

    unit_hint = " ".join(
        str(parsed.get(k) or "")
        for k in ("elevation_gain_unit", "elevation_gain_raw", "elevation_gain")
    ).lower()
    if any(token in unit_hint for token in ("ft", "feet", "foot")):
        value *= _FEET_TO_M
    return round(value, 1) if value <= 30000 else None


def _detect_source_app(parsed):
    text_fields = [
        parsed.get("source_app"),
        parsed.get("source_evidence"),
        parsed.get("source_labels"),
        parsed.get("app_name"),
        parsed.get("screenshot_text"),
    ]
    combined = " ".join(str(v or "").lower() for v in text_fields)
    if any(token in combined for token in ("strava", "kudos", "relative effort", "segments")):
        return "Strava"
    if "nike" in combined:
        return "Nike Run Club"
    if "garmin" in combined:
        return "Garmin"
    if "adidas" in combined:
        return "Adidas Running"
    if "apple" in combined:
        return "Apple Fitness"
    src = str(parsed.get("source_app") or "").strip()
    return src[:100] if src else "unknown"


def _normalize_uncertainty_notes(parsed):
    notes = parsed.get("uncertainty_notes")
    if isinstance(notes, str):
        notes = [n.strip() for n in re.split(r"[;\n]+", notes) if n.strip()]
    elif not isinstance(notes, list):
        notes = []
    cleaned = []
    for note in notes:
        txt = str(note).strip()
        if txt:
            cleaned.append(txt[:200])
    return cleaned[:5]


def _coerce_confidence(parsed):
    value = parsed.get("confidence")
    if value is None:
        value = parsed.get("confidence_score")
    conf = _to_finite_float(value)
    if conf is None:
        return None
    if conf > 1 and conf <= 100:
        conf = conf / 100.0
    if conf < 0 or conf > 1:
        return None
    return round(conf, 2)


@app.route("/api/parse-screenshot", methods=["POST"])
@limiter.limit("20 per hour")
def parse_screenshot():
    """
    Accept a screenshot image, send it to the Claude vision API,
    and return extracted run data as a preview JSON (no DB write).
    """
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    if "file" not in request.files:
        return jsonify({"error": "No file uploaded"}), 400
    file = request.files["file"]
    if not file or not file.filename:
        return jsonify({"error": "No file selected"}), 400

    # Validate MIME type / extension (images only)
    allowed_mimes = {"image/jpeg", "image/png", "image/heic", "image/heif", "image/webp"}
    content_type = (file.content_type or "").lower()
    fname = file.filename.lower()
    if content_type not in allowed_mimes:
        if not any(fname.endswith(ext) for ext in (".jpg", ".jpeg", ".png", ".heic", ".heif", ".webp")):
            return jsonify({"error": "Only image files (JPG, PNG, HEIC, WEBP) are supported"}), 400
        # Fallback: infer media type from extension
        ext_map = {".jpg": "image/jpeg", ".jpeg": "image/jpeg", ".png": "image/png",
                   ".heic": "image/heic", ".heif": "image/heif", ".webp": "image/webp"}
        for ext, mime in ext_map.items():
            if fname.endswith(ext):
                content_type = mime
                break

    if content_type not in allowed_mimes:
        content_type = "image/jpeg"  # safe fallback

    # Validate size (<= 10 MB)
    file.seek(0, os.SEEK_END)
    size_bytes = file.tell()
    file.seek(0)
    if size_bytes > 10 * 1024 * 1024:
        return jsonify({"error": "Image size exceeds limit (max 10 MB)"}), 400

    api_key = os.environ.get("GEMINI_API_KEY", "")
    if not api_key:
        print("[parse-screenshot] GEMINI_API_KEY not configured")
        return jsonify({"error": "Screenshot import is not configured on this server. Please enter your run manually."}), 503

    import json as _json
    try:
        from google import genai as _genai
        from google.genai import types as _types
        from google.genai.errors import APIError as _APIError
    except ImportError:
        return jsonify({"error": "Screenshot import is not available (missing dependency). Please enter your run manually."}), 503

    file_bytes = file.read()

    # --- Resize / recompress before sending to Gemini ---
    # Phone screenshots can be 3-5 MB; cap the longest dimension at 1568 px
    # and re-encode as JPEG @ quality=85. This typically reduces 3 MB → 200-400 KB,
    # cutting Render→Gemini upload time significantly.
    _orig_size = len(file_bytes)
    try:
        from PIL import Image as _PILImage
        import io as _io
        _img = _PILImage.open(_io.BytesIO(file_bytes))
        _img = _img.convert("RGB")  # drop alpha, normalise mode
        _max_dim = 1568
        _w, _h = _img.size
        if max(_w, _h) > _max_dim:
            _scale = _max_dim / max(_w, _h)
            _img = _img.resize(
                (int(_w * _scale), int(_h * _scale)),
                _PILImage.LANCZOS,
            )
        _buf = _io.BytesIO()
        _img.save(_buf, format="JPEG", quality=85, optimize=True)
        file_bytes = _buf.getvalue()
        content_type = "image/jpeg"
    except Exception as _resize_err:
        # Non-fatal: log and continue with original bytes
        print(f"[parse-screenshot] Image resize skipped ({_resize_err}); sending original")
    _new_size = len(file_bytes)
    print(
        f"[parse-screenshot] image size: {_orig_size:,} bytes -> {_new_size:,} bytes "
        f"({_orig_size / 1024:.1f} KB -> {_new_size / 1024:.1f} KB)"
    )

    today = _screenshot_reference_date(request.form.get("client_date"))

    PROMPT = (
        "You are a running data extractor for screenshot imports.\n"
        f"The user's date today is {today.isoformat()} ({today.strftime('%A')}).\n"
        "Carefully extract run stats from the screenshot and return ONLY strict JSON.\n"
        "Strava screenshots are especially important: look for labels like Distance, Moving Time, Elapsed Time, Pace, Elev Gain, Calories, Avg HR, and visible Strava branding.\n"
        "If a value is unreadable, return null for that field.\n"
        "{\n"
        '  "distance_km": <float or null>,\n'
        '  "distance_value": <float or null>,\n'
        '  "distance_unit": <"km"|"mi"|null>,\n'
        '  "duration_seconds": <int or null>,\n'
        '  "moving_time": <string or null>,\n'
        '  "elapsed_time": <string or null>,\n'
        '  "pace_per_km": <string like "5:30" or null>,\n'
        '  "pace_raw": <string or null>,\n'
        '  "pace_unit": <"min/km"|"min/mile"|null>,\n'
        '  "date": <ISO date "YYYY-MM-DD" if visible, else null>,\n'
        '  "date_relative": <"today"|"yesterday"|weekday name|null>,\n'
        '  "calories": <int or null>,\n'
        '  "average_heart_rate": <int or null>,\n'
        '  "elevation_gain_m": <float or null>,\n'
        '  "elevation_gain_raw": <string or null>,\n'
        '  "source_app": <string guess, prefer "Strava" if branding/labels indicate Strava>,\n'
        '  "source_evidence": <short string evidence or null>,\n'
        '  "confidence": <0..1 number or null>,\n'
        '  "uncertainty_notes": <array of short strings or []>\n'
        "}\n"
        "Rules:\n"
        "- Convert miles to kilometers.\n"
        "- Convert min/mile pace into min/km pace.\n"
        "- Convert duration values like 45:30 or 1:02:03 into seconds.\n"
        "- For relative dates like Today/Yesterday/weekday names, use date_relative.\n"
        "Return ONLY the JSON object, no markdown or commentary."
    )
    try:
        client = _genai.Client(
            api_key=api_key,
            http_options={
                "timeout": 15000.0,
                # Default is 5 attempts; cap at 2 so a slow request doesn't
                # silently stack into 90-150s of retries before the user sees an error.
                "retry_options": {
                    "attempts": 2,
                    "initial_delay": 1.0,
                    "max_delay": 10.0,
                },
            },
        )
        response = client.models.generate_content(
            model="gemini-3.5-flash-lite",
            contents=[
                _types.Part.from_bytes(data=file_bytes, mime_type=content_type),
                PROMPT
            ],
            config=_types.GenerateContentConfig(
                response_mime_type="application/json"
            )
        )
        # response.text raises if the candidate is blocked/empty; guard it explicitly
        candidates = getattr(response, "candidates", None)
        raw = None
        if candidates:
            raw = getattr(response, "text", None)
        if not raw:
            finish = (
                candidates[0].finish_reason.name
                if candidates and hasattr(candidates[0], "finish_reason")
                else "UNKNOWN"
            )
            print(f"[parse-screenshot] Gemini returned empty/blocked response. finish_reason={finish}")
            return (
                jsonify({"error": "Couldn't read this screenshot clearly — try a clearer photo or enter your run manually."}),
                422,
            )
        raw = raw.strip()
    except Exception as e:
        import traceback
        if getattr(e, "code", None) == 402:
            print("[parse-screenshot] Gemini API credits are depleted")
            return jsonify({
                "error": "Screenshot analysis is temporarily unavailable because the AI service has no credits. Please enter your run manually."
            }), 503
        print("[parse-screenshot] Gemini API error:", traceback.format_exc())
        return jsonify({"error": "Couldn't connect to the AI service. Please try again or enter your run manually."}), 502

    # Defensively parse JSON — strip markdown code fences if model included them
    raw_clean = raw
    if raw_clean.startswith("```"):
        parts = raw_clean.split("```")
        raw_clean = parts[1] if len(parts) > 1 else raw_clean
        if raw_clean.startswith("json"):
            raw_clean = raw_clean[4:]
    raw_clean = raw_clean.strip()

    try:
        parsed = _json.loads(raw_clean)
    except Exception:
        print("[parse-screenshot] JSON parse failed. Raw model output:", raw)
        return jsonify({"error": "Couldn't read this screenshot clearly — try a clearer photo or enter your run manually."}), 422

    if not isinstance(parsed, dict):
        return jsonify({"error": "Couldn't read this screenshot clearly — try a clearer photo or enter your run manually."}), 422

    distance_km = _parse_distance_km(parsed)
    duration_seconds = _parse_duration_seconds(parsed)
    if distance_km is None or duration_seconds is None:
        return jsonify({"error": "Couldn't read this screenshot clearly — try a clearer photo or enter your run manually."}), 422

    if (
        not math.isfinite(distance_km)
        or distance_km <= 0
        or distance_km > _MAX_SCREENSHOT_DISTANCE_KM
        or duration_seconds < _MIN_SCREENSHOT_DURATION_SECONDS
        or duration_seconds > _MAX_SCREENSHOT_DURATION_SECONDS
    ):
        return jsonify({"error": "Couldn't read this screenshot clearly — try a clearer photo or enter your run manually."}), 422

    time_min = round(duration_seconds / 60.0, 2)
    pace_per_km = (
        _format_pace(_parse_pace_to_min_per_km(parsed.get("pace_per_km"), "min/km"))
        or _format_pace(_parse_pace_to_min_per_km(parsed.get("pace_raw"), parsed.get("pace_unit")))
    )
    if not pace_per_km and distance_km > 0:
        pace_per_km = _format_pace((duration_seconds / 60.0) / distance_km)

    confidence = _coerce_confidence(parsed)
    uncertainty_notes = _normalize_uncertainty_notes(parsed)

    return jsonify({
        "success": True,
        "data": {
            "distance_km": distance_km,
            "time_min": time_min,
            "duration_seconds": duration_seconds,
            "pace_per_km": pace_per_km,
            "date": _resolve_screenshot_date(parsed.get("date"), parsed.get("date_relative"), today),
            "calories": _parse_optional_int(parsed.get("calories"), min_value=0, max_value=20000),
            "average_heart_rate": _parse_optional_int(parsed.get("average_heart_rate"), min_value=30, max_value=260),
            "elevation_gain_m": _parse_elevation_gain_m(parsed),
            "source_app": _detect_source_app(parsed),
            "confidence": confidence,
            "uncertainty_notes": uncertainty_notes,
        },
    }), 200


@app.route("/api/confirm-screenshot-import", methods=["POST"])
def confirm_screenshot_import():
    """
    Accept the (possibly user-edited) preview fields from /api/parse-screenshot
    and write the run to the database. Follows the same auth/CSRF/validation
    pattern as /api/confirm-import.
    """
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    payload = request.get_json() or {}

    # Validate & coerce core fields
    try:
        distance = float(payload.get("distance_km", 0))
        time_min = float(payload.get("time_min", 0))
    except (TypeError, ValueError):
        return jsonify({"error": "Invalid distance or duration"}), 400

    if not math.isfinite(distance) or not math.isfinite(time_min):
        return jsonify({"error": "Distance and duration must be finite numbers"}), 400
    if distance <= 0 or distance > 500:
        return jsonify({"error": "Distance must be between 0 and 500 km"}), 400
    if time_min <= 0 or time_min > 1440:
        return jsonify({"error": "Duration must be between 0 and 1440 minutes (24 h)"}), 400

    date_str = str(payload.get("date") or "").strip()
    if not date_str:
        date_str = datetime.now().strftime("%Y-%m-%d")
    try:
        run_date = datetime.strptime(date_str, "%Y-%m-%d").date()
        # +1 day: the user's local "today" can be ahead of the server's date
        if run_date > date.today() + timedelta(days=1):
            return jsonify({"error": "Run date cannot be in the future"}), 400
    except ValueError:
        return jsonify({"error": "Invalid date format — expected YYYY-MM-DD"}), 400

    run_type = str(payload.get("run_type", "easy")).strip()
    if run_type not in {"easy", "tempo", "long", "interval", "race"}:
        run_type = "easy"

    source_app = str(payload.get("source_app") or "unknown")[:100]
    user_notes = str(payload.get("notes") or "").strip()[:400]
    tag = f"[Screenshot Import — {source_app}]"
    notes = f"{tag} {user_notes}".strip() if user_notes else tag

    # Same tolerances as the CSV import's duplicate check; the user can override
    if not payload.get("allow_duplicate"):
        conn = get_db()
        existing = conn.execute(
            """
            SELECT id, date, distance_km, time_min FROM runs
            WHERE user_id = ? AND substr(CAST(date AS TEXT), 1, 10) = ?
              AND ABS(distance_km - ?) < 0.05 AND ABS(time_min - ?) < 0.5
            LIMIT 1
            """,
            (user["id"], date_str, distance, time_min),
        ).fetchone()
        conn.close()
        if existing:
            return jsonify({
                "error": "Looks like you already logged this run.",
                "duplicate": True,
                "existing_run": {
                    "id": existing["id"],
                    "date": str(existing["date"])[:10],
                    "distance_km": float(existing["distance_km"]),
                    "time_min": float(existing["time_min"]),
                },
            }), 409

    user_weight = user["weight"] if "weight" in user.keys() and user["weight"] is not None else DEFAULT_WEIGHT
    pace, calories = calc_stats(distance, time_min, user_weight)
    insight = f"Imported from screenshot ({source_app}) \U0001f4f8 — {distance} km in {time_min} min"
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")

    conn = get_db()
    r_id = None
    try:
        row = conn.execute(
            """
            INSERT INTO runs (
                user_id, date, distance_km, time_min, pace, calories, created_at,
                insight, run_type, notes
            )
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            RETURNING id
            """,
            (user["id"], date_str, distance, time_min, pace, calories, now_str,
             insight, run_type, notes),
        ).fetchone()

        if row:
            if hasattr(row, "keys") and "id" in row.keys():
                r_id = row["id"]
            elif isinstance(row, dict) and "id" in row:
                r_id = row["id"]
            else:
                r_id = row[0]

        conn.commit()
    except Exception as e:
        conn.rollback()
        conn.close()
        import traceback
        print("SCREENSHOT IMPORT ROLLBACK:", traceback.format_exc())
        return jsonify({"error": f"Database error: {str(e)}"}), 500

    conn.close()

    all_awarded = []
    if r_id:
        try:
            update_user_stats(user["id"], date_str, distance, operation="add")
        except Exception as st_err:
            print(f"Stats update warning after screenshot import: {st_err}")
        try:
            awarded = evaluate_badges_for_user(user["id"], r_id)
            all_awarded = list(set(awarded))
            if all_awarded:
                session["new_badges"] = all_awarded
        except Exception as bdg_err:
            print(f"Badge eval warning after screenshot import: {bdg_err}")
        _check_level_up(user, distance)

        try:
            from services.challenge_service import evaluate_challenges_for_user as _eval_challenges
            from services.goal_service import evaluate_goals_for_user as _eval_goals
            _eval_challenges(user["id"], r_id)
            _eval_goals(user["id"], r_id)
        except Exception as ch_err:
            print(f"Challenge eval warning after screenshot import: {ch_err}")
        _notify_followers_of_run(user, distance, date_str)
        log_activity(user["id"], "SCREENSHOT_IMPORT",
                     f"Imported run via screenshot ({source_app}): {distance} km")

    return jsonify({
        "success": True,
        "run_id": r_id,
        "new_badges": all_awarded,
        "message": f"Run imported successfully from {source_app}!",
    }), 200


# ---------- HEATMAP API ----------

@app.route("/api/heatmap-data")
def api_heatmap_data():
    """Returns daily run distances for the past 365 days for the heatmap calendar."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    conn = get_db()

    today = date.today()
    start_date = today - timedelta(days=364)  # 52 weeks

    runs = conn.execute(
        """
        SELECT date, SUM(distance_km) as total
        FROM runs
        WHERE user_id = ? AND date >= ?
        GROUP BY date
        ORDER BY date ASC
        """,
        (user["id"], start_date.strftime("%Y-%m-%d"))
    ).fetchall()
    conn.close()

    run_map = {str(r["date"])[:10]: round(r["total"], 2) for r in runs}

    # Build a list of {date, km} for each of the 365 days
    days = []
    for i in range(365):
        d = start_date + timedelta(days=i)
        d_str = d.strftime("%Y-%m-%d")
        days.append({"date": d_str, "km": run_map.get(d_str, 0)})

    return jsonify({"days": days})


# ---------- RUN TYPE STATS API ----------

@app.route("/api/run-type-stats")
def api_run_type_stats():
    """Returns a breakdown of run types for the pie chart."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    conn = get_db()

    rows = conn.execute(
        """
        SELECT COALESCE(run_type, 'easy') as run_type, COUNT(*) as count, SUM(distance_km) as total_km
        FROM runs
        WHERE user_id = ?
        GROUP BY run_type
        """,
        (user["id"],)
    ).fetchall()
    conn.close()

    result = {r["run_type"]: {"count": r["count"], "total_km": round(r["total_km"], 2)} for r in rows}
    return jsonify(result)


@app.route("/leaderboard")
def leaderboard():
    """Leaderboard: users ranked by total km logged (>= 1 KM to qualify)."""
    if not require_login():
        return redirect(url_for("login"))
    
    user = get_current_user()
    
    tab = request.args.get("tab", "all-time").lower()
    if tab not in ["daily", "weekly", "monthly", "all-time"]:
        tab = "all-time"

    today = get_today()
    if tab == "daily":
        start_date, end_date = get_current_day_range(today)
    elif tab == "weekly":
        start_date, end_date = get_current_week_range(today)
    elif tab == "monthly":
        start_date, end_date = get_current_month_range(today)
    else:
        start_date, end_date = None, None

    conn = get_db()
    param = "%s" if USE_PG else "?"
    
    if start_date and end_date:
        start_str = f"{start_date} 00:00:00"
        end_str = f"{end_date} 23:59:59"
        date_join = f"AND r.date >= {param} AND r.date <= {param}"
        params = (start_str, end_str)
    else:
        date_join = ""
        params = ()

    query = f"""
        SELECT 
            u.username, 
            u.display_name, 
            CASE WHEN u.avatar_image IS NOT NULL THEN 1 ELSE 0 END as has_avatar,
            COALESCE(us.current_streak, 0) as current_streak,
            COALESCE(SUM(r.distance_km), 0)  AS total_dist, 
            COALESCE(SUM(r.time_min), 0)     AS total_time,
            COUNT(r.id)                      AS run_count
        FROM users u
        LEFT JOIN user_stats us ON u.id = us.user_id
        LEFT JOIN runs r ON u.id = r.user_id {date_join}
        WHERE COALESCE(u.status, 'active') != 'blocked'
        GROUP BY u.id
        ORDER BY total_dist DESC
    """
    rows = conn.execute(query, params).fetchall()
    conn.close()

    QUALIFY_KM = 1.0
    leaderboard_qualified = []
    leaderboard_unranked = []
    for row in rows:
        total_dist = row["total_dist"]
        total_time = row["total_time"]
        entry = {
            "username": row["username"],
            "display_name": row["display_name"] or row["username"],
            "has_avatar": bool(row["has_avatar"]),
            "current_streak": row["current_streak"],
            "total_dist": round(total_dist, 2),
            "total_time": round(total_time, 1),
            "run_count": row["run_count"],
            # avg_pace in min/km; 0 if no runs
            "avg_pace": round(total_time / total_dist, 2) if total_dist > 0 else 0
        }
        if total_dist >= QUALIFY_KM:
            leaderboard_qualified.append(entry)
        else:
            remaining = round(QUALIFY_KM - total_dist, 2)
            entry["remaining"] = remaining
            entry["motivational"] = ("Run 1 KM to enter the leaderboard"
                                     if total_dist == 0
                                     else f"{remaining:.2f} KM to qualify")
            leaderboard_unranked.append(entry)

    return render_template(
        "leaderboard.html",
        leaderboard=leaderboard_qualified,
        leaderboard_unranked=leaderboard_unranked,
        username=user["username"],
        display_name=user["display_name"] or user["username"],
        theme=user["theme"] or "system",
        active_tab=tab
    )


# ---------- USER SEARCH API (for @mention autocomplete) ----------

@app.route("/api/users/search")
def api_user_search():
    """
    Returns a list of usernames matching the query string.
    Used by the @mention autocomplete in the run notes field.
    Limits to 8 results. Excludes the current user.
    """
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    q = request.args.get("q", "").strip()
    if not q or len(q) < 1:
        return jsonify({"users": []})

    # Sanitise: only allow alphanumeric + underscore queries
    import re
    if not re.match(r'^[\w]+$', q):
        return jsonify({"users": []})

    user = get_current_user()
    conn = get_db()
    rows = conn.execute(
        """
        SELECT username, display_name
        FROM users
        WHERE username LIKE ?
          AND id != ?
          AND COALESCE(status, 'active') = 'active'
        ORDER BY username ASC
        LIMIT 8
        """,
        (q + "%", user["id"])
    ).fetchall()
    conn.close()

    return jsonify({
        "users": [
            {
                "username": row["username"],
                "display_name": row["display_name"] or row["username"]
            }
            for row in rows
        ]
    })


# ---------- FRIEND / FOLLOW SYSTEM ----------

@app.route("/follow/<username>", methods=["POST"])
def follow_user(username):
    """Follow another runner."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    conn = get_db()
    target = conn.execute(
        "SELECT id, username, display_name FROM users WHERE username = ? AND COALESCE(status, 'active') = 'active'",
        (username,)
    ).fetchone()

    if not target:
        conn.close()
        return jsonify({"error": "User not found"}), 404

    if target["id"] == user["id"]:
        conn.close()
        return jsonify({"error": "Cannot follow yourself"}), 400

    try:
        now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        conn.execute(
            "INSERT INTO friends (follower_id, followed_id, created_at) VALUES (?, ?, ?)",
            (user["id"], target["id"], now_str)
        )
        conn.commit()
        conn.close()
        log_activity(user["id"], "FOLLOW", f"Followed {username}")
        try:
            from services.social_service import notify_new_follower
            notify_new_follower(user, target["id"])
        except Exception as notif_err:
            print(f"Follow notification warning: {notif_err}")
        return jsonify({"success": True, "following": True}), 200
    except IntegrityError:
        conn.close()
        return jsonify({"success": True, "following": True, "note": "Already following"}), 200


@app.route("/unfollow/<username>", methods=["POST"])
def unfollow_user(username):
    """Unfollow a runner."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    conn = get_db()
    target = conn.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone()
    if not target:
        conn.close()
        return jsonify({"error": "User not found"}), 404

    conn.execute(
        "DELETE FROM friends WHERE follower_id = ? AND followed_id = ?",
        (user["id"], target["id"])
    )
    conn.commit()
    conn.close()
    log_activity(user["id"], "UNFOLLOW", f"Unfollowed {username}")
    return jsonify({"success": True, "following": False}), 200


def _check_level_up(user, added_km):
    """
    Detect a rank-up caused by `added_km` that has just been saved. Queues the
    celebration modal for the next Home load and sends a bell notification.
    Returns the new level dict (or None). Never blocks saving a run.
    """
    try:
        from services.level_service import get_total_km, detect_level_up
        after = get_total_km(user["id"])
        level = detect_level_up(after - float(added_km), after)
        if level:
            session['level_up'] = level
            from services.social_service import notify_level_up
            notify_level_up(user, level)
        return level
    except Exception as lv_err:
        print(f"Level-up check warning: {lv_err}")
        return None


def _notify_followers_of_run(user, distance, date_str):
    """Bell notification to the runner's followers. Never blocks saving a run."""
    try:
        from services.social_service import notify_followers_of_run
        notify_followers_of_run(user, distance, date_str)
    except Exception as notif_err:
        print(f"Friend-run notification warning: {notif_err}")


# ---------- ACTIVITY FEED / LIKES / FOLLOW LISTS ----------

@app.route("/api/feed")
def api_activity_feed():
    """Everyone's runs from the last 24 hours, plus the top performances."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    from services.social_service import get_activity_feed
    return jsonify(get_activity_feed(user["id"])), 200


@app.route("/api/runs/<int:run_id>/like", methods=["POST", "DELETE"])
@limiter.limit("60 per minute")
def api_like_run(run_id):
    """POST likes a run, DELETE removes the like. Both are idempotent."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    from services.social_service import set_run_like
    result = set_run_like(user, run_id, liked=request.method == "POST")
    if result is None:
        return jsonify({"error": "Run not found"}), 404
    like_count, liked = result
    return jsonify({"success": True, "liked": liked, "like_count": like_count}), 200


@app.route("/api/runs/<int:run_id>/likers")
def api_run_likers(run_id):
    """Who liked a run (newest first), with follow state for the viewer."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    from services.social_service import get_run_likers
    likers = get_run_likers(run_id, user["id"])
    if likers is None:
        return jsonify({"error": "Run not found"}), 404
    return jsonify({"likers": likers, "like_count": len(likers)}), 200


@app.route("/api/user/<username>/<any(followers, following):which>")
def api_follow_list(username, which):
    """Followers of a runner, or the runners they follow."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    viewer = get_current_user()
    from services.social_service import get_follow_list
    people = get_follow_list(username, which, viewer["id"] if viewer else None)
    if people is None:
        return jsonify({"error": "User not found"}), 404
    return jsonify({"people": people}), 200


@app.route("/api/social/suggested")
def api_suggested_runners():
    """Active runners the current user doesn't follow yet."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    from services.social_service import get_suggested_runners
    return jsonify({"runners": get_suggested_runners(user["id"])}), 200


@app.route("/api/social/runners")
def api_all_runners():
    """Every active runner (except you) with follow state — the "show more" list on Home."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    from services.social_service import get_all_runners
    return jsonify({"runners": get_all_runners(user["id"])}), 200


# ---------- AUTH ROUTES ----------

@app.route("/register", methods=["GET", "POST"])
@limiter.limit("10 per hour")
def register():
    if request.method == "POST":
        username = request.form["username"].strip()
        pin = request.form["pin"].strip()

        if not username or not pin:
            return render_template("register.html", error="Username and PIN are required.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)

        if len(pin) < 4 or not pin.isdigit():
            return render_template("register.html", error="Use a 4+ digit numeric PIN.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)

        hashed_pin = bcrypt.generate_password_hash(pin)
        conn = get_db()
        try:
            conn.execute(
                "INSERT INTO users (username, pin) VALUES (?, ?)",
                (username, hashed_pin)
            )
            conn.commit()
        except IntegrityError:
            conn.close()
            return render_template("register.html", error="Username already taken.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)
        user = conn.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone()
        conn.close()

        session["user_id"] = user["id"]
        session["username"] = username
        return redirect(url_for("onboarding"))

    return render_template("register.html", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)

#Onboarding program 

@app.route("/onboarding", methods=["GET", "POST"])
def onboarding():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    if not user:
        return redirect(url_for("login"))

    if request.method == "GET":
        if is_onboarding_complete(user):
            return redirect(url_for("index"))

        return render_template(
            "onboarding.html",
            username=user["username"],
            profile_emoji=user["profile_emoji"] if "profile_emoji" in user.keys() else "🏃🏻"
        )

    # POST: save onboarding data
    display_name = request.form.get("display_name", "").strip()
    profile_emoji = request.form.get("profile_emoji", "").strip()
    
    # Enforce mandatory fields
    if not display_name or not profile_emoji:
        return render_template(
            "onboarding.html",
            username=user["username"],
            error="Display Name and Profile Icon are required."
        )

    allowed_emojis = ["🏃🏻", "🏃🏻‍♀️", "🏃🏻‍♂️", "👟", "🥇", "🏅", "🎽", "💪", "🔥", "⚡", "🚀", "🤖", "🦾", "🐺", "🦅", "🐉", "🏃🏻♀️", "🏃🏻♂️"]
    if profile_emoji not in allowed_emojis:
        return render_template(
            "onboarding.html",
            username=user["username"],
            error="Invalid Profile Icon selected."
        )

    weight_raw = request.form.get("weight", "").strip()
    height_raw = request.form.get("height", "").strip()
    weekly_goal_raw = request.form.get("weekly_goal", "").strip()
    experience = request.form.get("experience", "").strip() or None
    primary_goal = request.form.get("primary_goal", "").strip() or None
    frequency = request.form.get("frequency", "").strip() or None

    # Optional at onboarding: unrealistic values are dropped rather than saved
    height, weight, metrics_error = _check_body_metrics(height_raw, weight_raw)
    if metrics_error:
        height, weight = None, None

    try:
        weekly_goal = float(weekly_goal_raw) if weekly_goal_raw else None
    except ValueError:
        weekly_goal = None

    conn = get_db()
    
    # Check if user already has a weekly goal in user_weekly_goals
    existing_wg = conn.execute("SELECT goal_km FROM user_weekly_goals WHERE user_id = ?", (user['id'],)).fetchone()
    
    # If user provided a new weekly goal, upsert it.
    if weekly_goal is not None:
        now = datetime.now(timezone.utc).isoformat()
        if existing_wg:
            conn.execute("UPDATE user_weekly_goals SET goal_km = ?, updated_at = ? WHERE user_id = ?", 
                         (weekly_goal, now, user["id"]))
        else:
            conn.execute("INSERT INTO user_weekly_goals (user_id, goal_km, created_at, updated_at) VALUES (?, ?, ?, ?)", 
                         (user["id"], weekly_goal, now, now))
    else:
        # If they skipped it during onboarding, let's keep existing if available.
        if existing_wg:
            weekly_goal = existing_wg["goal_km"]

    conn.execute("""
        UPDATE users
        SET display_name = ?, profile_emoji = ?, weight = ?, height = ?, experience = ?, primary_goal = ?, frequency = ?
        WHERE id = ?
    """, (display_name, profile_emoji, weight, height, experience, primary_goal, frequency, user["id"]))
    
    conn.commit()
    conn.close()

    return redirect(url_for("index", tour="1"))

@app.route("/dev-login", methods=["POST"])
def dev_login():
    if not app.config.get('DEV_LOGIN_ENABLED'):
        return render_template("login.html", error="Dev login is disabled.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES), 403
    
    conn = get_db()
    # Find the developer account "Yajat" or ADMIN_USER_ID fallback
    dev_username = "Yajat"
    user = conn.execute("SELECT * FROM users WHERE username = ?", (dev_username,)).fetchone()
    
    if not user:
        # Fallback to ADMIN_USER_ID if present
        admin_id = os.environ.get("ADMIN_USER_ID")
        if admin_id:
            user = conn.execute("SELECT * FROM users WHERE id = ?", (admin_id,)).fetchone()
    
    conn.close()

    if not user:
        return render_template("login.html", error="Dev account not found in database.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)
    
    session.permanent = True
    session["user_id"] = user["id"]
    session["username"] = user["username"]
    
    if user["status"] == "blocked":
        session.clear()
        return render_template("login.html", error="Account is blocked.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)
        
    return redirect(url_for("admin_dashboard"))

@app.route("/login", methods=["GET", "POST"])
@limiter.limit("20 per hour")
def login():
    if request.method == "POST":
        username = request.form["username"].strip()
        pin = request.form["pin"].strip()

        conn = get_db()
        user = conn.execute(
            "SELECT * FROM users WHERE username = ?",
            (username,)
        ).fetchone()

        if user:
            # Check if locked
            if user["locked_until"]:
                try:
                    locked_until = datetime.strptime(user["locked_until"], "%Y-%m-%d %H:%M:%S")
                    if datetime.now() < locked_until:
                        conn.close()
                        time_left = max(1, (locked_until - datetime.now()).seconds // 60)
                        return render_template("login.html", error=f"Account locked. Try again in {time_left} minutes.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)
                    else:
                        # Lock expired, reset attempts
                        conn.execute("UPDATE users SET failed_login_attempts = 0, locked_until = NULL WHERE id = ?", (user["id"],))
                        conn.commit()
                except ValueError:
                    pass

        # Verify PIN using bcrypt. All PINs have been migrated to bcrypt hashes
        # via `flask migrate-pins` — plaintext fallback has been removed.
        if user:
            stored_pin = user["pin"] or ""
            pin_ok = bcrypt.check_password_hash(stored_pin, pin)
        else:
            pin_ok = False

        if not user or not pin_ok:
            if user:
                # Atomically increment failed attempts to prevent race conditions
                conn.execute("UPDATE users SET failed_login_attempts = COALESCE(failed_login_attempts, 0) + 1 WHERE id = ?", (user["id"],))
                conn.commit()
                # Fetch updated attempts
                updated_user = conn.execute("SELECT failed_login_attempts FROM users WHERE id = ?", (user["id"],)).fetchone()
                attempts = updated_user["failed_login_attempts"]
                
                if attempts >= app.config.get("MAX_LOGIN_ATTEMPTS", 5):
                    lock_time = datetime.now() + timedelta(seconds=app.config.get("LOGIN_ATTEMPT_WINDOW", 300))
                    conn.execute("UPDATE users SET locked_until = ? WHERE id = ?", (lock_time.strftime("%Y-%m-%d %H:%M:%S"), user["id"]))
                    conn.commit()
                    conn.close()
                    return render_template("login.html", error="Too many failed attempts. Account locked for 5 minutes.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)
            conn.close()
            return render_template("login.html", error="Invalid username or PIN.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)
            
        # Reset on successful login
        conn.execute("UPDATE users SET failed_login_attempts = 0, locked_until = NULL WHERE id = ?", (user["id"],))
        conn.commit()
        conn.close()

        session.permanent = True
        session["user_id"] = user["id"]
        session["username"] = user["username"]

        # Check if blocked
        if user["status"] == "blocked":
            session.clear()
            return render_template("login.html", error="Your account has been blocked.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)

        # Update last_login
        try:
            now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            conn = get_db()
            conn.execute("UPDATE users SET last_login = ? WHERE id = ?", (now_str, user["id"]))
            conn.commit()
            conn.close()
            
            log_activity(user["id"], "LOGIN", "User logged in")
        except Exception:
            pass # Non-critical if fails
        
        
        return redirect(url_for("index"))

    return render_template("login.html", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)


@app.route("/auth/google/login")
def google_login():
    if not os.environ.get("GOOGLE_CLIENT_ID"):
        flash("Google Login is not configured on this server.", "danger")
        return redirect(url_for("login"))
    
    redirect_uri = url_for('google_auth', _external=True)
    session['linking'] = request.args.get('link') == 'true'
    session['intent'] = request.args.get('intent', 'login')
    if session['linking']:
        session['oauth_redirect_to'] = request.referrer
    return oauth.google.authorize_redirect(redirect_uri)


@app.route("/auth/google/callback")
@limiter.limit("20 per hour")
def google_auth():
    if not os.environ.get("GOOGLE_CLIENT_ID"):
        flash("Google Login is not configured.", "danger")
        return redirect(url_for("login"))
        
    try:
        token = oauth.google.authorize_access_token()
        userinfo = token.get('userinfo')
        if not userinfo:
            userinfo = oauth.google.userinfo()
    except Exception as e:
        flash(f"Google login failed: {str(e)}", "danger")
        return redirect(url_for("login"))
        
    google_id = userinfo.get('sub')
    email = userinfo.get('email')
    intent = session.pop('intent', 'login')
    
    if not google_id or not email:
        flash("Incomplete information received from Google.", "danger")
        return redirect(url_for("login"))
        
    conn = get_db()
    
    # 1. Check if google_id already exists
    user = conn.execute("SELECT * FROM users WHERE google_id = ?", (google_id,)).fetchone()
    
    if user:
        # ---- GOOGLE RECOVERY FLOW: must check BEFORE any login logic ----
        # intent is consumed from session (server-side) to prevent tampering
        if intent == 'recover':
            recovery_uid = session.get('recovery_user_id')
            session.pop('recovery_user_id', None)  # consume
            if not recovery_uid or user['id'] != recovery_uid:
                # Wrong Google account or stale session
                conn.close()
                flash("Recovery failed. The Google account you used does not match the account's linked Google identity.", "danger")
                return redirect(url_for('forgot_pin'))
            # Valid — grant access to reset
            conn.close()
            session['recovery_verified']    = True
            session['recovery_user_id']     = recovery_uid
            session['recovery_method']      = 'google'
            session['recovery_expires_at']  = (
                datetime.now(timezone.utc) + timedelta(minutes=15)
            ).strftime("%Y-%m-%d %H:%M:%S")
            return redirect(url_for('forgot_pin_reset'))

        if session.get('linking'):
            if user['id'] != session.get('user_id'):
                flash("This Google account is already linked to another user.", "danger")
                conn.close()
                return redirect(session.pop('oauth_redirect_to', url_for("settings")))
            else:
                flash("This Google account is already linked to your account.", "info")
                conn.close()
                return redirect(session.pop('oauth_redirect_to', url_for("settings")))
        
        # Log them in
        session.permanent = True
        session["user_id"] = user["id"]
        session["username"] = user["username"]
        if user["status"] == "blocked":
            session.clear()
            conn.close()
            return render_template("login.html", error="Your account has been blocked.", show_db_notice=date.today() < ANNOUNCEMENT_EXPIRES)
        
        now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        conn.execute(
            "UPDATE users SET last_login = ?, "
            "email = COALESCE(NULLIF(email, ''), ?), "
            "google_email = ?, "
            "email_monthly_summary = 1, "
            "email_weekly_summary = 1 "
            "WHERE id = ?",
            (now_str, email, email, user["id"])
        )
        conn.commit()
        conn.close()
        log_activity(user["id"], "LOGIN", "User logged in via Google")
        
        if not user['pin']:
            return redirect(url_for('set_pin'))
            
        if not is_onboarding_complete(user):
            return redirect(url_for("onboarding"))
            
        return redirect(url_for("index"))
        
    # 2. Check if we are linking to a current session
    if session.get('linking') and session.get('user_id'):
        user_id = session['user_id']
        conn.execute(
            "UPDATE users SET google_id = ?, google_email = ?, "
            "email = COALESCE(NULLIF(email, ''), ?), "
            "email_monthly_summary = 1, "
            "email_weekly_summary = 1 "
            "WHERE id = ?",
            (google_id, email, email, user_id)
        )
        conn.commit()
        conn.close()
        session.pop('linking', None)
        flash("Google account successfully linked!", "success")
        log_activity(user_id, "LINK_GOOGLE", "Linked Google account")
        return redirect(session.pop('oauth_redirect_to', url_for("settings")))
        
    # 3. Check for email collision with existing account
    existing_email_user = conn.execute("SELECT * FROM users WHERE email = ?", (email,)).fetchone()
    if existing_email_user:
        conn.close()
        flash("An account already exists with this email. Please log in with your existing username and PIN, then link your Google account from your Profile.", "warning")
        return redirect(url_for("login"))
        
    # 4. Handle based on intent
    if intent == 'login':
        conn.close()
        flash("No RunRush account found for this Google account. Please create an account first.", "warning")
        return redirect(url_for("login"))

    # Brand new registration (intent == 'register')
    base_username = email.split('@')[0]
    username = base_username
    counter = 2
    while conn.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone():
        username = f"{base_username}{counter}"
        counter += 1
        
    try:
        conn.execute(
            "INSERT INTO users (username, pin, email, google_id, google_email, email_monthly_summary, email_weekly_summary) "
            "VALUES (?, ?, ?, ?, ?, 1, 1)",
            (username, "", email, google_id, email)
        )
        conn.commit()
        new_user = conn.execute("SELECT id FROM users WHERE username = ?", (username,)).fetchone()
        
        session.permanent = True
        session["user_id"] = new_user["id"]
        session["username"] = username
        log_activity(new_user["id"], "REGISTER", "User registered via Google")
        conn.close()
        
        return redirect(url_for("set_pin"))
        
    except IntegrityError:
        conn.close()
        flash("Error creating account.", "danger")
        return redirect(url_for("login"))


@app.route("/auth/google/disconnect", methods=["POST"])
def google_disconnect():
    if not require_login():
        return redirect(url_for("login"))
        
    user = get_current_user()
    if not user:
        return redirect(url_for("login"))
        
    if not user['pin']:
        flash("You cannot disconnect your Google account because you don't have a backup PIN set. Please change your PIN first.", "danger")
        return redirect(request.referrer or url_for("settings"))
        
    conn = get_db()
    conn.execute("UPDATE users SET google_id = NULL, google_email = NULL WHERE id = ?", (user['id'],))
    conn.commit()
    conn.close()
    
    log_activity(user['id'], "UNLINK_GOOGLE", "Disconnected Google account")
    flash("Google account disconnected.", "success")
    return redirect(request.referrer or url_for("settings"))


# =============================================================================
# FORGOT PIN — Recovery flow
# =============================================================================

_RECOVERY_SESSION_LIFETIME = 900  # 15 minutes


def _recovery_session_valid():
    """True if the server-side recovery session is present, verified, and not expired."""
    if not session.get('recovery_verified'):
        return False
    expires_at_str = session.get('recovery_expires_at', '')
    if not expires_at_str:
        return False
    try:
        expires_at = datetime.strptime(expires_at_str, "%Y-%m-%d %H:%M:%S")
    except ValueError:
        return False
    return datetime.now(timezone.utc) < expires_at


def _clear_recovery_session():
    """Wipe all recovery state from the server-side session."""
    for k in ('recovery_user_id', 'recovery_verified', 'recovery_method', 'recovery_expires_at'):
        session.pop(k, None)


@app.route("/forgot-pin", methods=["GET", "POST"])
@limiter.limit("20 per hour")
def forgot_pin():
    """
    Step 1: collect username.
    POST: look up the user (never reveal whether it exists), set
    server-side recovery_user_id in session, redirect to methods page.
    """
    _clear_recovery_session()

    if request.method == "POST":
        username = request.form.get('username', '').strip()
        # Constant-time: always redirect to /forgot-pin/methods
        # We store the user_id only if found — otherwise store None
        conn = get_db()
        user = conn.execute(
            "SELECT id FROM users WHERE username = ?", (username,)
        ).fetchone() if username else None
        conn.close()

        # Store the (possibly None) user_id server-side
        # The methods page will display a generic message if None
        session['recovery_user_id'] = user['id'] if user else None
        session['recovery_verified'] = False
        return redirect(url_for('forgot_pin_methods'))

    return render_template('forgot_pin.html')


@app.route("/forgot-pin/methods", methods=["GET"])
@limiter.limit("20 per hour")
def forgot_pin_methods():
    """
    Step 2: show available recovery options without exposing account details.
    """
    user_id = session.get('recovery_user_id')
    SUPPORT_EMAIL = os.environ.get('SUPPORT_EMAIL', '<!-- CONFIGURE SUPPORT_EMAIL in environment -->')

    if not user_id:
        # Generic — never reveal whether the account exists
        return render_template('forgot_pin_methods.html',
                               has_google=False,
                               has_email=False,
                               no_method=True,
                               support_email=SUPPORT_EMAIL)

    conn = get_db()
    user = conn.execute(
        "SELECT google_id, recovery_email, recovery_email_verified FROM users WHERE id = ?",
        (user_id,)
    ).fetchone()
    conn.close()

    if not user:
        return render_template('forgot_pin_methods.html',
                               has_google=False, has_email=False, no_method=True,
                               support_email=SUPPORT_EMAIL)

    has_google = bool(user['google_id'])
    has_email  = bool(user['recovery_email'] and user['recovery_email_verified'])

    # Mask the email — never expose the full address
    from services.pin_recovery_service import mask_email
    masked_email = mask_email(user['recovery_email']) if has_email else None

    return render_template(
        'forgot_pin_methods.html',
        has_google=has_google,
        has_email=has_email,
        masked_email=masked_email,
        no_method=(not has_google and not has_email),
        support_email=SUPPORT_EMAIL,
    )


@app.route("/forgot-pin/send-email", methods=["POST"])
@limiter.limit("5 per hour")
def forgot_pin_send_email():
    """
    Step 2b: send a 6-digit recovery code to the verified recovery email.
    Always returns a generic response to prevent enumeration.
    """
    user_id = session.get('recovery_user_id')
    GENERIC_MSG = ("If an account with a recovery email exists, "
                   "we've sent a 6-digit code. Check your inbox.")

    if not user_id:
        flash(GENERIC_MSG, 'info')
        return redirect(url_for('forgot_pin_verify'))

    conn = get_db()
    user = conn.execute(
        "SELECT username, recovery_email, recovery_email_verified FROM users WHERE id = ?",
        (user_id,)
    ).fetchone()
    conn.close()

    if not user or not user['recovery_email'] or not user['recovery_email_verified']:
        flash(GENERIC_MSG, 'info')
        return redirect(url_for('forgot_pin_verify'))

    from services.pin_recovery_service import generate_and_send_recovery_code
    ok, reason = generate_and_send_recovery_code(
        user_id, user['recovery_email'], user['username']
    )

    if not ok and reason == 'rate_limited':
        flash("Too many recovery requests. Please wait before trying again.", 'danger')
        return redirect(url_for('forgot_pin_methods'))

    # Always show generic success
    flash(GENERIC_MSG, 'info')
    return redirect(url_for('forgot_pin_verify'))


@app.route("/forgot-pin/google-recover", methods=["POST"])
@limiter.limit("10 per hour")
def forgot_pin_google_recover():
    """
    Step 2c: initiate Google OAuth for PIN recovery.
    We validate server-side that the account has a linked Google ID
    before we even start the OAuth flow.
    """
    user_id = session.get('recovery_user_id')

    if not user_id:
        flash("Recovery session expired. Please start again.", 'danger')
        return redirect(url_for('forgot_pin'))

    conn = get_db()
    user = conn.execute(
        "SELECT google_id FROM users WHERE id = ?", (user_id,)
    ).fetchone()
    conn.close()

    if not user or not user['google_id']:
        flash("This account does not have a linked Google account.", 'danger')
        return redirect(url_for('forgot_pin_methods'))

    if not os.environ.get('GOOGLE_CLIENT_ID'):
        flash("Google is not configured on this server.", 'danger')
        return redirect(url_for('forgot_pin_methods'))

    # Store intent on server side — do NOT pass via URL parameter
    session['intent'] = 'recover'
    # recovery_user_id is already in session; google_auth will consume it
    redirect_uri = url_for('google_auth', _external=True)
    return oauth.google.authorize_redirect(redirect_uri)


@app.route("/forgot-pin/verify", methods=["GET", "POST"])
@limiter.limit("10 per hour")
def forgot_pin_verify():
    """
    Step 3 (email path): verify the 6-digit code.
    """
    user_id = session.get('recovery_user_id')

    if request.method == "POST":
        if not user_id:
            flash("Recovery session expired. Please start again.", 'danger')
            return redirect(url_for('forgot_pin'))

        code = request.form.get('code', '').strip()

        from services.pin_recovery_service import verify_recovery_code
        ok, reason = verify_recovery_code(user_id, code)

        if not ok:
            if reason == 'max_attempts':
                _clear_recovery_session()
                flash("Too many incorrect attempts. Please start the recovery flow again.", 'danger')
                return redirect(url_for('forgot_pin'))
            elif reason in ('no_active_code', 'wrong_code'):
                remaining_label = ''
                flash(f"Invalid or expired code. Please check and try again.", 'danger')
                return render_template('forgot_pin_verify.html')
            else:
                flash("Something went wrong. Please start again.", 'danger')
                return redirect(url_for('forgot_pin'))

        # Code verified — elevate to reset-capable session
        session['recovery_verified']   = True
        session['recovery_method']     = 'email'
        session['recovery_expires_at'] = (
            datetime.now(timezone.utc) + timedelta(minutes=15)
        ).strftime("%Y-%m-%d %H:%M:%S")
        return redirect(url_for('forgot_pin_reset'))

    return render_template('forgot_pin_verify.html')


@app.route("/forgot-pin/reset", methods=["GET", "POST"])
@limiter.limit("10 per hour")
def forgot_pin_reset():
    """
    Step 4: set the new PIN.
    Requires a valid server-side recovery session.
    """
    if not _recovery_session_valid():
        _clear_recovery_session()
        flash("Your recovery session has expired. Please start again.", 'danger')
        return redirect(url_for('forgot_pin'))

    user_id = session.get('recovery_user_id')
    if not user_id:
        _clear_recovery_session()
        return redirect(url_for('forgot_pin'))

    if request.method == "POST":
        new_pin     = request.form.get('new_pin', '').strip()
        confirm_pin = request.form.get('confirm_pin', '').strip()

        if not new_pin.isdigit() or len(new_pin) < 4:
            return render_template('forgot_pin_reset.html',
                                   error="PIN must be at least 4 digits (numbers only).")
        if new_pin != confirm_pin:
            return render_template('forgot_pin_reset.html',
                                   error="PINs do not match. Please try again.")

        hashed = bcrypt.generate_password_hash(new_pin)
        conn = get_db()
        conn.execute("UPDATE users SET pin = ? WHERE id = ?", (hashed, user_id))
        # Invalidate all active recovery tokens for this account
        now_str = datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S")
        conn.execute(
            "UPDATE pin_resets SET used_at = ? WHERE user_id = ? AND used_at IS NULL",
            (now_str, user_id)
        )
        conn.commit()
        conn.close()

        log_activity(user_id, "PIN_RESET", "PIN reset via recovery flow")
        _clear_recovery_session()

        flash("Your PIN has been reset successfully. Please log in with your new PIN.", 'success')
        return redirect(url_for('login'))

    return render_template('forgot_pin_reset.html')


# =============================================================================
# RECOVERY EMAIL — Settings (add & verify)
# =============================================================================

@app.route("/settings/recovery-email", methods=["POST"])
@limiter.limit("10 per hour")
def set_recovery_email():
    """
    Logged-in user sets a new recovery email.
    Sends a verification code; marks recovery_email_verified = 0 until verified.
    """
    if not require_login():
        return redirect(url_for('login'))

    from utils.validators import validate_email, ValidationError
    new_email = request.form.get('recovery_email', '').strip().lower()

    try:
        new_email = validate_email(new_email)
    except ValidationError as e:
        flash(str(e), 'danger')
        return redirect(url_for('settings'))

    if not new_email:
        flash("Please provide a valid email address.", 'danger')
        return redirect(url_for('settings'))

    user = get_current_user()
    conn = get_db()
    # Save the new (unverified) email immediately
    conn.execute(
        "UPDATE users SET recovery_email = ?, recovery_email_verified = ? WHERE id = ?",
        (new_email, False, user['id'])
    )
    conn.commit()

    # Send verification code
    from services.pin_recovery_service import generate_and_send_verification_email
    ok, reason = generate_and_send_verification_email(
        user['id'], new_email, user['username']
    )
    conn.close()

    if not ok:
        if reason == 'rate_limited':
            flash("Too many verification requests. Please wait before trying again.", 'danger')
        else:
            flash("Failed to send verification email. Is RESEND_API_KEY configured?", 'danger')
    else:
        flash("A verification code was sent to your new recovery email. Enter it below to complete verification.", 'success')

    return redirect(url_for('settings'))


@app.route("/settings/verify-recovery-email", methods=["POST"])
@limiter.limit("10 per hour")
def verify_recovery_email():
    """
    Logged-in user submits the 6-digit code to confirm their recovery email.
    """
    if not require_login():
        return redirect(url_for('login'))

    user = get_current_user()
    code = request.form.get('verification_code', '').strip()

    from services.pin_recovery_service import verify_recovery_code
    ok, reason = verify_recovery_code(user['id'], code)

    if not ok:
        if reason == 'max_attempts':
            flash("Too many incorrect attempts. Please request a new code.", 'danger')
        elif reason == 'no_active_code':
            flash("No active verification code found. Please request a new code.", 'danger')
        else:
            flash("Invalid or expired code. Please try again.", 'danger')
        return redirect(url_for('settings'))

    conn = get_db()
    conn.execute(
        "UPDATE users SET recovery_email_verified = ? WHERE id = ?", (True, user['id'])
    )
    conn.commit()
    conn.close()
    flash("Recovery email verified successfully!", 'success')
    return redirect(url_for('settings'))




@app.route("/auth/set-pin", methods=["GET", "POST"])
def set_pin():
    if not require_login():
        return redirect(url_for("login"))
        
    user = get_current_user()
    if user['pin']:
        return redirect(url_for("index"))
        
    if request.method == "POST":
        pin = request.form.get("pin", "").strip()
        if len(pin) < 4 or not pin.isdigit():
            return render_template("set_pin.html", error="Use a 4+ digit numeric PIN.", username=user['username'])
            
        hashed_pin = bcrypt.generate_password_hash(pin)
        conn = get_db()
        conn.execute("UPDATE users SET pin = ? WHERE id = ?", (hashed_pin, user['id']))
        conn.commit()
        conn.close()
        
        flash("PIN successfully set! Welcome to RunRush.", "success")
        if not is_onboarding_complete(user):
            return redirect(url_for("onboarding"))
        return redirect(url_for("index"))
        
    return render_template("set_pin.html", username=user['username'])


@app.route("/dev/design-system")
def dev_design_system():
    """
    Phase 1 design-system visual QA surface (UI redesign project). Not
    linked from any nav, not a production feature — admin/moderator-only,
    same auth pattern as /admin, so it can be reached directly by URL
    without exposing it to regular users.
    """
    if not require_login():
        return redirect(url_for("login"))
    user = get_current_user()
    if get_user_role(user) not in ["admin", "moderator"]:
        return render_template("403.html"), 403
    return render_template("dev_design_system.html", theme=user["theme"] or "system")


@app.route("/admin")
def admin_dashboard():
    if not require_login():
        return redirect(url_for("login"))

    user = get_current_user()
    role = get_user_role(user)
    
    if role not in ["admin", "moderator"]:
         return render_template("403.html"), 403

    conn = get_db()
    
    # Get all users with aggregated statistics
    users_query = """
        WITH run_counts AS (
            SELECT user_id, COUNT(id) as total_runs
            FROM runs
            GROUP BY user_id
        )
        SELECT u.*, 
               COALESCE(rc.total_runs, 0) as total_runs,
               COALESCE(s.total_distance_km, 0.0) as total_km
        FROM users u
        LEFT JOIN run_counts rc ON u.id = rc.user_id
        LEFT JOIN user_stats s ON u.id = s.user_id
        ORDER BY u.last_login DESC
    """
    # Convert Row objects to dicts so they are mutable if needed, though jinja handles dict-like Rows.
    # In sqlite it returns sqlite3.Row, in psycopg it returns dict.
    users = conn.execute(users_query).fetchall()
    
    # Stats: Total Users
    total_users = len(users)
    
    # Stats: Active Users (last 7 days)
    cutoff = datetime.now() - timedelta(days=7)
    active_users = 0
    for u in users:
        if u["last_login"]:
            try:
                # Format match: YYYY-MM-DD HH:MM:SS
                if datetime.strptime(str(u["last_login"])[:19], "%Y-%m-%d %H:%M:%S") >= cutoff:
                    active_users += 1
            except:
                pass

    # Stats: Total KM (platform wide)
    total_km_row = conn.execute("SELECT SUM(distance_km) AS total_km FROM runs").fetchone()
    total_km = round(total_km_row["total_km"] or 0, 1)

    # Activity Logs (Limit 20)
    logs = conn.execute("""
        SELECT a.action, a.details, a.timestamp, u.username 
        FROM activity_logs a 
        LEFT JOIN users u ON a.user_id = u.id 
        ORDER BY a.id DESC LIMIT 20
    """).fetchall()

    conn.close()
    
    return render_template(
        "admin.html",
        users=users,
        role=role,
        total_users=total_users,
        active_users=active_users,
        total_km=total_km,
        logs=logs,
        theme=user["theme"] or "system"
    )


@app.route("/admin/user/<int:user_id>/<action>", methods=["POST"])
def admin_user_action(user_id, action):
    if not require_login():
        return redirect(url_for("login"))
        
    current_user = get_current_user()
    current_role = get_user_role(current_user)
    
    # Permissions
    if current_role not in ["admin", "moderator"]:
         return render_template("403.html"), 403
         
    # Moderators cannot delete
    if action == "delete" and current_role != "admin":
         return render_template("403.html"), 403

    conn = get_db()
    
    # Protect Super Admin from being touched
    target_user = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    if target_user:
        target_role = get_user_role(target_user)
        # Cannot modify admins unless you are an admin
        # Ideally, Super Admin (ENV) shouldn't be touched by DB admins either, but simple logic for now:
        # Don't let moderators touch admins
        if target_role == "admin" and current_role != "admin":
             conn.close()
             return render_template("403.html"), 403
        
        # Super Admin Env Check (Immutable)
        admin_id_env = os.environ.get("ADMIN_USER_ID")
        if str(user_id) == str(admin_id_env):
             conn.close()
             # Flash message ideal here, but simpler to just redirect
             return redirect(url_for("admin_dashboard"))

        if action == "block":
            conn.execute("UPDATE users SET status = 'blocked' WHERE id = ?", (user_id,))
            log_activity(current_user["id"], "BLOCK_USER", f"Blocked user {target_user['username']}")
            flash(f"User {target_user['username']} has been blocked.", "warning")

        elif action == "unblock":
            conn.execute("UPDATE users SET status = 'active' WHERE id = ?", (user_id,))
            log_activity(current_user["id"], "UNBLOCK_USER", f"Unblocked user {target_user['username']}")
            flash(f"User {target_user['username']} has been unblocked.", "success")

        elif action == "promote":
            # Only Admin can promote
            if current_role == "admin":
                conn.execute("UPDATE users SET role = 'admin' WHERE id = ?", (user_id,))
                log_activity(current_user["id"], "PROMOTE_USER", f"Promoted {target_user['username']} to admin")
                flash(f"User {target_user['username']} promoted to Admin.", "success")

        elif action == "demote":
            # Only Admin can demote
            if current_role == "admin":
                conn.execute("UPDATE users SET role = 'user' WHERE id = ?", (user_id,))
                log_activity(current_user["id"], "DEMOTE_USER", f"Demoted {target_user['username']} to user")
                flash(f"User {target_user['username']} demoted to User.", "info")
        
        elif action == "delete":
            # Delete runs then user
            conn.execute("DELETE FROM runs WHERE user_id = ?", (user_id,))
            conn.execute("DELETE FROM users WHERE id = ?", (user_id,))
            log_activity(current_user["id"], "DELETE_USER", f"Deleted user {target_user['username']}")
            flash(f"User {target_user['username']} deleted permanently.", "danger")

        conn.commit()
    
    conn.close()
    return redirect(url_for("admin_dashboard"))


# ---------- ADMIN USER ANALYTICS ----------

@app.route("/admin/user/<int:target_user_id>/analytics")
def admin_user_analytics(target_user_id):
    """Admin-only: returns full stats, charts, badges for a specific user as JSON."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    current_user = get_current_user()
    role = get_user_role(current_user)
    if role not in ["admin", "moderator"]:
        return jsonify({"error": "Forbidden"}), 403

    conn = get_db()
    target = conn.execute("SELECT * FROM users WHERE id = ?", (target_user_id,)).fetchone()
    if not target:
        conn.close()
        return jsonify({"error": "User not found"}), 404

    stats = conn.execute("SELECT * FROM user_stats WHERE user_id = ?", (target_user_id,)).fetchone()
    runs  = conn.execute(
        "SELECT * FROM runs WHERE user_id = ? ORDER BY date DESC", (target_user_id,)
    ).fetchall()

    total_runs = len(runs)
    total_km   = sum(r["distance_km"] for r in runs)
    total_cal  = sum(r["calories"]    for r in runs)
    avg_pace   = (sum(r["pace"] for r in runs) / total_runs) if total_runs > 0 else 0

    # Monthly breakdown – last 6 months (no external deps needed)
    today      = date.today()
    month_names = ["Jan","Feb","Mar","Apr","May","Jun",
                   "Jul","Aug","Sep","Oct","Nov","Dec"]
    monthly_data   = {}
    monthly_ordered = []
    for i in range(5, -1, -1):
        total_months = today.year * 12 + today.month - 1 - i
        y = total_months // 12
        m = total_months % 12 + 1
        key = f"{y}-{m:02d}"
        monthly_data[key]    = 0.0
        monthly_ordered.append((key, f"{month_names[m-1]} '{str(y)[2:]}"))

    for r in runs:
        try:
            key = r["date"][:7]   # "YYYY-MM"
            if key in monthly_data:
                monthly_data[key] += r["distance_km"]
        except Exception:
            pass

    monthly_labels = [lbl  for _, lbl in monthly_ordered]
    monthly_values = [round(monthly_data[key], 2) for key, _ in monthly_ordered]

    # Run-type breakdown
    run_type_breakdown = {}
    for r in runs:
        rt = r["run_type"] or "easy"
        run_type_breakdown[rt] = run_type_breakdown.get(rt, 0) + 1

    # Badges
    badges = conn.execute(
        """
        SELECT b.key as badge_key, ub.unlocked_at 
        FROM user_badges ub 
        JOIN badges b ON ub.badge_id = b.id 
        WHERE ub.user_id = ? 
        ORDER BY ub.unlocked_at ASC
        """,
        (target_user_id,)
    ).fetchall()

    conn.close()

    recent_runs = [
        {
            "date":        r["date"],
            "distance_km": round(r["distance_km"], 2),
            "time_min":    round(r["time_min"], 1),
            "pace":        round(r["pace"], 2),
            "calories":    round(r["calories"], 0),
            "run_type":    r["run_type"] or "easy"
        }
        for r in runs[:10]
    ]

    return jsonify({
        "user": {
            "id":           target["id"],
            "username":     target["username"],
            "display_name": target["display_name"] or target["username"],
            "role":         get_user_role(target),
            "status":       target["status"] or "active",
            "last_login":   target["last_login"],
        },
        "stats": {
            "total_runs":     total_runs,
            "total_km":       round(total_km, 2),
            "total_cal":      round(total_cal, 0),
            "avg_pace":       round(avg_pace, 2),
            "current_streak": stats["current_streak"] if stats else 0,
            "best_streak":    stats["best_streak"]    if stats else 0,
        },
        "monthly_chart": {"labels": monthly_labels, "values": monthly_values},
        "run_type_breakdown": run_type_breakdown,
        "badges":      [{"key": b["badge_key"], "unlocked_at": b["unlocked_at"]} for b in badges],
        "recent_runs": recent_runs
    })


@app.route("/api/trigger-weekly-emails", methods=["POST"])
@csrf.exempt  # Called by external cron scheduler; protected by CRON_SECRET shared-secret header
def trigger_weekly_emails():
    """
    Cron-triggered: send weekly summary emails to all opted-in users.

    Authentication: the caller must supply the CRON_SECRET value in the
    'Authorization: Bearer <secret>' header.  This replaces the previous
    admin-session check which is incompatible with automated cron jobs.
    """
    # Authenticate via either CRON_SECRET header or an admin session
    authenticated = False
    current_user = None

    cron_secret = os.environ.get("CRON_SECRET", "")
    auth_header = request.headers.get("Authorization", "")
    
    # 1. Check CRON_SECRET header
    if auth_header and cron_secret:
        provided = auth_header.removeprefix("Bearer ").strip()
        import hmac as _hmac
        if _hmac.compare_digest(provided, cron_secret):
            authenticated = True
            current_user = {"id": 0}  # Sentinel system user

    # 2. Check admin session (fallback)
    if not authenticated and require_login():
        current_user = get_current_user()
        role = get_user_role(current_user)
        if role in ["admin", "moderator"]:
            # Protect the admin session path from CSRF attacks
            from extensions import csrf
            csrf.protect()
            authenticated = True

    if not authenticated:
        if auth_header:
            return jsonify({"error": "Unauthorized"}), 401
        return jsonify({"error": "Forbidden"}), 403

    conn = get_db()
    users_to_email = conn.execute(
        "SELECT id FROM users WHERE ( (email IS NOT NULL AND email != '') OR (google_email IS NOT NULL AND google_email != '') ) AND COALESCE(email_weekly_summary, 1) = 1"
    ).fetchall()
    conn.close()

    sent = 0
    failed = 0
    for u in users_to_email:
        if send_weekly_summary(u["id"]):
            sent += 1
        else:
            failed += 1

    log_activity(current_user["id"], "WEEKLY_EMAILS", f"Sent: {sent}, Failed: {failed}")
    return jsonify({"success": True, "sent": sent, "failed": failed})


@app.route("/api/trigger-monthly-emails", methods=["POST"])
@csrf.exempt  # Called by external cron scheduler; protected by CRON_SECRET shared-secret header
def trigger_monthly_emails():
    """
    Cron-triggered: send monthly summary emails to all opted-in users for
    a given calendar month (default: the previous calendar month relative
    to now, e.g. run on Oct 1st it summarizes September).

    Same dual-auth pattern as /api/trigger-weekly-emails: CRON_SECRET
    header, or an authenticated admin/moderator session.

    NOTE: this endpoint exists and is testable, but nothing in this
    codebase currently calls it automatically — no Render cron job (or
    equivalent) has been configured to hit it. Activating that is a
    deliberate, separate step, not part of this endpoint's own rollout.
    """
    from services.monthly_summary_delivery import send_monthly_summaries_for_all_eligible_users
    from utils.dates import get_previous_month_range

    authenticated = False
    current_user = None

    cron_secret = os.environ.get("CRON_SECRET", "")
    auth_header = request.headers.get("Authorization", "")

    if auth_header and cron_secret:
        provided = auth_header.removeprefix("Bearer ").strip()
        import hmac as _hmac
        if _hmac.compare_digest(provided, cron_secret):
            authenticated = True
            current_user = {"id": 0}  # Sentinel system user

    if not authenticated and require_login():
        current_user = get_current_user()
        role = get_user_role(current_user)
        if role in ["admin", "moderator"]:
            csrf.protect()
            authenticated = True

    if not authenticated:
        if auth_header:
            return jsonify({"error": "Unauthorized"}), 401
        return jsonify({"error": "Forbidden"}), 403

    payload = request.get_json(silent=True) or {}
    try:
        year = int(payload["year"]) if "year" in payload else None
        month = int(payload["month"]) if "month" in payload else None
    except (TypeError, ValueError):
        return jsonify({"error": "Invalid year or month"}), 400

    if year is None or month is None:
        prev_start, _prev_end = get_previous_month_range()
        year = year or prev_start.year
        month = month or prev_start.month

    if not (1 <= month <= 12):
        return jsonify({"error": "month must be 1-12"}), 400

    result = send_monthly_summaries_for_all_eligible_users(year, month)
    tally = result["tally"]
    details = result.get("details", [])

    log_activity(
        current_user["id"], "MONTHLY_EMAILS",
        f"year={year} month={month} sent={tally.get('sent', 0)} failed={tally.get('failed', 0)} "
        f"skipped={tally.get('skipped', 0)} ineligible={tally.get('ineligible', 0)}"
    )
    return jsonify({
        "success": True,
        "year": year,
        "month": month,
        "tally": tally,
        "details": details,
        "total_checked": len(details)
    })


from ml_predictor import get_predictions_for_user

@app.route("/api/predict-next-run")
def api_predict_next_run():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    try:
        result = get_predictions_for_user(user["id"], get_db)
        # Estimated duration for the Home header, from the runner's recent average pace
        result["predicted_time_min"] = None
        if result.get("prediction_km"):
            conn = get_db()
            recent = conn.execute(
                "SELECT pace FROM runs WHERE user_id = ? AND pace > 0 ORDER BY date DESC, id DESC LIMIT 10",
                (user["id"],)
            ).fetchall()
            conn.close()
            if recent:
                avg_pace = sum(float(r["pace"]) for r in recent) / len(recent)
                result["predicted_time_min"] = round(result["prediction_km"] * avg_pace)
        return jsonify(result)
    except Exception as e:
        app.logger.error(f"Error predicting next run: {e}")
        return jsonify({"error": "Failed to predict next run"}), 500

@app.route("/api/progress-data")
def api_progress_data():
    if not require_login():
        return {"error": "Unauthorized"}, 401
    
    user = get_current_user()
    range_type = request.args.get("range", "week")  # week, month, year
    
    conn = get_db()
    today = get_today()
    
    if range_type == "week":
        # Last 7 days (Mon-Sun) - only up to today
        start_date, _ = get_current_week_range(today)  # Current Monday
        labels = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"]
        data = [0.0] * 7
        
        runs = conn.execute(
            "SELECT date, SUM(distance_km) as total FROM runs WHERE user_id = ? AND date >= ? AND date <= ? GROUP BY date",
            (user["id"], start_date.strftime("%Y-%m-%d"), today.strftime("%Y-%m-%d"))
        ).fetchall()
        
        for run in runs:
            run_date = datetime.strptime(str(run["date"])[:10], "%Y-%m-%d")
            # Only include dates up to today
            if run_date.date() <= today:
                day_index = run_date.weekday()
                if 0 <= day_index < 7:
                    data[day_index] = round(run["total"], 2)
    
    elif range_type == "month":
        # Current month daily - only up to today
        start_date, end_date = get_current_month_range(today)
        num_days = end_date.day
        
        labels = [str(i) for i in range(1, num_days + 1)]
        data = [0.0] * num_days
        
        runs = conn.execute(
            "SELECT date, SUM(distance_km) as total FROM runs WHERE user_id = ? AND date >= ? AND date <= ? GROUP BY date",
            (user["id"], start_date.strftime("%Y-%m-%d"), today.strftime("%Y-%m-%d"))
        ).fetchall()
        
        for run in runs:
            run_date = datetime.strptime(str(run["date"])[:10], "%Y-%m-%d")
            # Only include dates up to today
            if run_date.date() <= today:
                day_index = run_date.day - 1
                if 0 <= day_index < num_days:
                    data[day_index] = round(run["total"], 2)
    
    elif range_type == "year":
        # Current year monthly - only up to current month
        start_date = today.replace(month=1, day=1)
        labels = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
        data = [0.0] * 12
        
        runs = conn.execute(
            "SELECT date, SUM(distance_km) as total FROM runs WHERE user_id = ? AND date >= ? AND date <= ? GROUP BY date",
            (user["id"], start_date.strftime("%Y-%m-%d"), today.strftime("%Y-%m-%d"))
        ).fetchall()
        
        for run in runs:
            run_date = datetime.strptime(str(run["date"])[:10], "%Y-%m-%d")
            # Only include dates up to today
            if run_date.date() <= today:
                month_index = run_date.month - 1
                if 0 <= month_index < 12:
                    data[month_index] += run["total"]
        
        data = [round(d, 2) for d in data]
    
    else:
        conn.close()
        return {"error": "Invalid range"}, 400
    
    # Calculate stats
    total = sum(data)
    active_days = sum(1 for d in data if d > 0)
    average = round(total / active_days, 2) if active_days > 0 else 0
    best = max(data) if data else 0
    
    conn.close()
    
    return {
        "labels": labels,
        "data": data,
        "stats": {
            "total": round(total, 2),
            "average": average,
            "best": best,
            "active_days": active_days
        }
    }


# ---------- WEEKLY GOAL ----------

@app.route("/api/weekly-goal", methods=["POST"])
def set_weekly_goal():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
        
    user = get_current_user()
    data = request.get_json()
    
    if not data or 'goal_km' not in data:
        return jsonify({"error": "Missing goal_km"}), 400
        
    try:
        goal_km = float(data['goal_km'])
    except ValueError:
        return jsonify({"error": "Invalid goal_km"}), 400
        
    if goal_km <= 0 or goal_km > 500:
        return jsonify({"error": "Goal must be between 0.1 and 500 km"}), 400
        
    now = datetime.now(timezone.utc).isoformat()
    conn = get_db()
    
    # Upsert logic (compatible with both SQLite and Postgres)
    existing = conn.execute("SELECT user_id FROM user_weekly_goals WHERE user_id = ?", (user['id'],)).fetchone()
    
    if existing:
        conn.execute("UPDATE user_weekly_goals SET goal_km = ?, updated_at = ? WHERE user_id = ?", 
                     (goal_km, now, user['id']))
    else:
        conn.execute("INSERT INTO user_weekly_goals (user_id, goal_km, created_at, updated_at) VALUES (?, ?, ?, ?)", 
                     (user['id'], goal_km, now, now))
                     
    conn.commit()
    conn.close()
    
    return jsonify({"success": True, "goal_km": goal_km})

@app.route("/api/weekly-goal-progress", methods=["GET"])
def get_weekly_goal_progress():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
        
    user = get_current_user()
    conn = get_db()
    
    goal_row = conn.execute("SELECT goal_km FROM user_weekly_goals WHERE user_id = ?", (user['id'],)).fetchone()
    
    if not goal_row:
        conn.close()
        return jsonify({"goal_km": None})
        
    goal_km = goal_row['goal_km']
    
    # Exactly matching the week boundary calculation from Weekly Leaderboard
    today = get_today()
    week_start, week_end = get_current_week_range(today)
    
    lb_start_str = week_start.strftime("%Y-%m-%d")
    lb_end_str = week_end.strftime("%Y-%m-%d")
    
    res = conn.execute("""
        SELECT COALESCE(SUM(distance_km), 0) as current_km 
        FROM runs 
        WHERE user_id = ? AND date BETWEEN ? AND ?
    """, (user['id'], lb_start_str, lb_end_str)).fetchone()
    
    conn.close()
    
    current_km = res['current_km']
    percent_complete = min(100, (current_km / goal_km) * 100) if goal_km > 0 else 0
    days_remaining = (week_end - today).days
    
    return jsonify({
        "goal_km": goal_km,
        "current_km": round(current_km, 2),
        "percent_complete": round(percent_complete, 1),
        "days_remaining_in_week": days_remaining
    })

# ---------- ANALYTICS: MONTHLY COMPARISON ----------

@app.route("/api/analytics/monthly-comparison")
def api_monthly_comparison():
    """Weekly-bucketed distance for this month vs last month."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    conn = get_db()
    now = datetime.now()

    # --- Date ranges ---
    # This month: 1st of current month → today
    this_month_start = now.replace(day=1)
    this_month_end   = now

    # Last month: 1st of previous month → last day of previous month
    last_month_end   = this_month_start - timedelta(days=1)
    last_month_start = last_month_end.replace(day=1)

    param = "%s" if USE_PG else "?"

    def fetch_runs(start, end):
        return conn.execute(
            f"SELECT date, SUM(distance_km) as total FROM runs "
            f"WHERE user_id = {param} AND date >= {param} AND date <= {param} "
            f"GROUP BY date ORDER BY date",
            (user["id"], start.strftime("%Y-%m-%d"), end.strftime("%Y-%m-%d"))
        ).fetchall()

    def bucket_by_week(runs, month_start, num_days):
        """Aggregate runs into calendar-week buckets (days 1-7, 8-14, 15-21, 22-28, 29+)."""
        weeks = [0.0, 0.0, 0.0, 0.0, 0.0]
        for r in runs:
            d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d")
            week_idx = min((d.day - 1) // 7, 4)
            weeks[week_idx] += r["total"]
        # Only return buckets that fall within the month's days
        used = (num_days - 1) // 7 + 1
        return [round(w, 2) for w in weeks[:used]]

    def make_labels(month_start, month_abbr, num_days):
        """Generate 'Mon D1\u2013D2' labels for each week bucket."""
        import calendar
        labels = []
        starts = [1, 8, 15, 22, 29]
        for s in starts:
            if s > num_days:
                break
            e = min(s + 6, num_days)
            labels.append(f"{month_abbr} {s}\u2013{e}")
        return labels

    import calendar
    days_this = (now - this_month_start).days + 1
    days_last = last_month_end.day

    this_runs = fetch_runs(this_month_start, this_month_end)
    last_runs = fetch_runs(last_month_start, last_month_end)

    this_weeks = bucket_by_week(this_runs, this_month_start, days_this)
    last_weeks = bucket_by_week(last_runs, last_month_start, days_last)

    # Align lengths
    max_len = max(len(this_weeks), len(last_weeks))
    this_weeks += [0.0] * (max_len - len(this_weeks))
    last_weeks += [0.0] * (max_len - len(last_weeks))

    # Use current month's date-range labels (the x-axis represents calendar position)
    this_month_abbr = now.strftime("%b")
    labels = make_labels(this_month_start, this_month_abbr, days_this)
    # Pad labels if last month had more weeks
    while len(labels) < max_len:
        labels.append(f"Wk {len(labels)+1}")

    this_total = round(sum(this_weeks), 2)
    last_total = round(sum(last_weeks), 2)
    delta      = round(this_total - last_total, 2)

    this_month_name = now.strftime("%B")
    last_month_name = last_month_end.strftime("%B")

    conn.close()

    return jsonify({
        "labels":          labels,
        "this_month":      this_weeks,
        "last_month":      last_weeks,
        "this_month_name": this_month_name,
        "last_month_name": last_month_name,
        "this_total":      this_total,
        "last_total":      last_total,
        "delta":           delta,
    })


# ---------- ANALYTICS: PACE TREND ----------

@app.route("/api/analytics/pace-trend")
def api_pace_trend():
    """Average pace per run for the last 90 days, for trend analysis."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    conn = get_db()
    now = datetime.now()
    since = (now - timedelta(days=90)).strftime("%Y-%m-%d")

    param = "%s" if USE_PG else "?"

    runs = conn.execute(
        f"SELECT date, AVG(pace) as avg_pace, SUM(distance_km) as total_km "
        f"FROM runs WHERE user_id = {param} AND date >= {param} "
        f"GROUP BY date ORDER BY date",
        (user["id"], since)
    ).fetchall()

    if not runs:
        conn.close()
        return jsonify({"labels": [], "paces": [], "trend": []})

    labels = []
    paces  = []
    for r in runs:
        d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d")
        labels.append(d.strftime("%d %b"))
        paces.append(round(float(r["avg_pace"]), 2))

    # Linear regression for trend line
    n = len(paces)
    if n >= 2:
        xs = list(range(n))
        mean_x = sum(xs) / n
        mean_y = sum(paces) / n
        num   = sum((x - mean_x) * (y - mean_y) for x, y in zip(xs, paces))
        denom = sum((x - mean_x) ** 2 for x in xs)
        slope     = num / denom if denom else 0
        intercept = mean_y - slope * mean_x
        trend = [round(slope * x + intercept, 2) for x in xs]
    else:
        slope = 0
        trend = paces[:]

    improving = (trend[-1] < trend[0]) if len(trend) >= 2 else True

    # Magnitude of the trend: slope in sec/km across the full date range
    # slope is in pace-units/run-index; convert to sec/km improvement over the span
    slope_sec_per_km = round(slope * 60, 2) if n >= 2 else 0  # negative = improving

    conn.close()

    return jsonify({
        "labels":           labels,
        "paces":            paces,
        "trend":            trend,
        "improving":        improving,
        "n":                n,
        "slope_sec_per_km": slope_sec_per_km,  # neg = faster per run, pos = slower
    })


# ---------- ANALYTICS: RUNRUSH INSIGHTS ----------

@app.route("/api/analytics/insights")
def api_analytics_insights():
    """Generate personalized running insights from the user's existing run data."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    conn = get_db()
    param = "%s" if USE_PG else "?"

    from utils.dates import get_current_month_range, get_previous_month_range, get_today

    today = get_today()
    curr_start, curr_end = get_current_month_range(today)
    prev_start, prev_end = get_previous_month_range(today)

    # Fetch this month's runs
    this_month_runs = conn.execute(
        f"SELECT date, distance_km, pace, time_min FROM runs "
        f"WHERE user_id = {param} AND date >= {param} AND date <= {param}",
        (user["id"], curr_start.strftime("%Y-%m-%d"), curr_end.strftime("%Y-%m-%d"))
    ).fetchall()

    # Fetch previous month's runs
    prev_month_runs = conn.execute(
        f"SELECT date, distance_km, pace, time_min FROM runs "
        f"WHERE user_id = {param} AND date >= {param} AND date <= {param}",
        (user["id"], prev_start.strftime("%Y-%m-%d"), prev_end.strftime("%Y-%m-%d"))
    ).fetchall()

    # Fetch all-time runs
    all_runs = conn.execute(
        f"SELECT date, distance_km, pace, time_min FROM runs "
        f"WHERE user_id = {param}",
        (user["id"],)
    ).fetchall()

    conn.close()

    insights = []

    # --- INSIGHT: Getting Faster ---
    # Threshold: >=2 runs in BOTH current and previous month
    # Reuses: pace column (same as pace-trend endpoint AVG(pace))
    if len(this_month_runs) >= 2 and len(prev_month_runs) >= 2:
        curr_paces = [float(r["pace"]) for r in this_month_runs if r["pace"] and float(r["pace"]) > 0]
        prev_paces = [float(r["pace"]) for r in prev_month_runs if r["pace"] and float(r["pace"]) > 0]
        if len(curr_paces) >= 2 and len(prev_paces) >= 2:
            avg_curr_pace = sum(curr_paces) / len(curr_paces)
            avg_prev_pace = sum(prev_paces) / len(prev_paces)
            if avg_prev_pace > 0:
                pace_change_pct = ((avg_prev_pace - avg_curr_pace) / avg_prev_pace) * 100
                if pace_change_pct > 0:
                    insights.append({
                        "icon": "🔥",
                        "title": "Getting Faster",
                        "description": f"Your average pace has improved by {pace_change_pct:.1f}% compared with last month."
                    })
                elif pace_change_pct < -1:
                    insights.append({
                        "icon": "🐢",
                        "title": "Pace Change",
                        "description": f"Your average pace is {abs(pace_change_pct):.1f}% slower than last month. Recovery weeks are normal!"
                    })

    # --- INSIGHT: Distance Growth ---
    # Threshold: >=2 runs in BOTH current and previous month
    # Reuses: SUM(distance_km) — same aggregation as monthly-comparison endpoint
    if len(this_month_runs) >= 2 and len(prev_month_runs) >= 2:
        curr_total_km = sum(float(r["distance_km"]) for r in this_month_runs)
        prev_total_km = sum(float(r["distance_km"]) for r in prev_month_runs)
        delta_km = curr_total_km - prev_total_km
        if delta_km > 0:
            insights.append({
                "icon": "📈",
                "title": "Distance Growth",
                "description": f"You've run {delta_km:.1f} km more than last month so far."
            })
        elif delta_km < 0 and prev_total_km > 0:
            insights.append({
                "icon": "📉",
                "title": "Distance Change",
                "description": f"You've run {abs(delta_km):.1f} km less than last month so far."
            })

    # --- INSIGHT: Consistency (active days this month) ---
    # Threshold: >=1 run this month
    # Reuses: COUNT(DISTINCT date) — same as heatmap/progress active-days stat
    if len(this_month_runs) >= 1:
        active_days = len(set(r["date"] for r in this_month_runs))
        insights.append({
            "icon": "📅",
            "title": "Consistency",
            "description": f"You've been active on {active_days} day{'s' if active_days != 1 else ''} this month."
        })

    # --- INSIGHT: Typical Run ---
    # Threshold: >=1 run all-time
    # Reuses: AVG(distance_km) — same basis as progress stat-average
    if len(all_runs) >= 1:
        avg_distance = sum(float(r["distance_km"]) for r in all_runs) / len(all_runs)
        insights.append({
            "icon": "🏃",
            "title": "Your Typical Run",
            "description": f"Your average run distance is {avg_distance:.1f} km."
        })

    # --- INSIGHT: Favorite Running Day ---
    # Threshold: >=5 total logged runs
    # Reuses: date column — same as heatmap day-of-week distribution
    if len(all_runs) >= 5:
        from collections import Counter
        day_counts = Counter()
        day_names = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
        for r in all_runs:
            d = datetime.strptime(str(r["date"])[:10], "%Y-%m-%d")
            day_counts[d.weekday()] += 1
        most_common_day = day_counts.most_common(1)[0]
        insights.append({
            "icon": "🗓️",
            "title": "Favorite Running Day",
            "description": f"{day_names[most_common_day[0]]} is your most active running day."
        })

    # --- INSIGHT: Best Performance ---
    # Threshold: >=1 run with a valid pace
    # Reuses: MIN(pace) — same as pace-trend / personal-bests logic
    valid_paces = [float(r["pace"]) for r in all_runs if r["pace"] and float(r["pace"]) > 0]
    if len(valid_paces) >= 1:
        best_pace = min(valid_paces)
        pace_min = int(best_pace)
        pace_sec = int((best_pace - pace_min) * 60)
        insights.append({
            "icon": "⚡",
            "title": "Best Performance",
            "description": f"Your fastest recorded pace is {pace_min}:{pace_sec:02d} min/km."
        })

    return jsonify({"insights": insights})


# ---------- ANALYTICS: TRAINING LOAD (ACWR) ----------

@app.route("/api/training-load", methods=["GET"])
@limiter.limit("20 per hour")
def api_training_load():
    """Return the user's Acute:Chronic Workload Ratio (ACWR) and 8-week series."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401

    user = get_current_user()
    conn = get_db()
    param = "%s" if USE_PG else "?"

    rows = conn.execute(
        f"SELECT date, distance_km, time_min FROM runs WHERE user_id = {param}",
        (user["id"],),
    ).fetchall()
    conn.close()

    runs = [
        {"date": str(r["date"])[:10], "distance_km": r["distance_km"], "time_min": r["time_min"]}
        for r in rows
    ]

    from services.training_load import compute_training_load
    from datetime import date as _date

    result = compute_training_load(runs, _date.today())
    return jsonify(result), 200



@app.route("/api/personal-bests", methods=["GET"])
def api_personal_bests():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    conn = get_db()
    
    try:
        # fastest 5k range (4.5 to 5.5)
        fastest_5k = conn.execute(
            "SELECT * FROM runs WHERE user_id = ? AND distance_km >= 4.5 AND distance_km <= 5.5 ORDER BY pace ASC, date ASC LIMIT 1", 
            (user["id"],)
        ).fetchone()
        
        # fastest 10k range (9.5 to 10.5)
        fastest_10k = conn.execute(
            "SELECT * FROM runs WHERE user_id = ? AND distance_km >= 9.5 AND distance_km <= 10.5 ORDER BY pace ASC, date ASC LIMIT 1", 
            (user["id"],)
        ).fetchone()
        
        # longest distance
        longest_distance = conn.execute(
            "SELECT * FROM runs WHERE user_id = ? ORDER BY distance_km DESC, date ASC LIMIT 1", 
            (user["id"],)
        ).fetchone()
        
        # longest duration
        longest_duration = conn.execute(
            "SELECT * FROM runs WHERE user_id = ? ORDER BY time_min DESC, date ASC LIMIT 1", 
            (user["id"],)
        ).fetchone()
        
        # best pace (distance >= 1.0 km)
        best_pace = conn.execute(
            "SELECT * FROM runs WHERE user_id = ? AND distance_km >= 1.0 ORDER BY pace ASC, date ASC LIMIT 1", 
            (user["id"],)
        ).fetchone()
        
        # highest calorie burn
        highest_calories = conn.execute(
            "SELECT * FROM runs WHERE user_id = ? ORDER BY calories DESC, date ASC LIMIT 1", 
            (user["id"],)
        ).fetchone()
    finally:
        conn.close()
    
    def format_run(run):
        if not run:
            return None
        return dict(run)

    return jsonify({
        "fastest_5k": format_run(fastest_5k),
        "fastest_10k": format_run(fastest_10k),
        "longest_distance": format_run(longest_distance),
        "longest_duration": format_run(longest_duration),
        "best_pace": format_run(best_pace),
        "highest_calories": format_run(highest_calories)
    })

@app.route("/api/challenges", methods=["GET"])
def api_challenges():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    from services.challenge_service import get_user_challenges
    challenges = get_user_challenges(user["id"])
    return jsonify({"status": "success", "challenges": challenges})

@app.route("/api/goals", methods=["GET"])
def api_goals_get():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    
    from services.goal_service import get_user_goals
    goals = get_user_goals(user["id"])
    return jsonify({"status": "success", "goals": goals})

@app.route("/api/goals", methods=["POST"])
def api_goals_post():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
        
    user = get_current_user()
    data = request.json
    
    goal_type = data.get("goal_type")
    target_distance_km = float(data.get("target_distance_km", 0))
    target_date = data.get("target_date")
    days_per_week = int(data.get("days_per_week", 3))
    
    from services.goal_service import check_goal_feasibility
    
    is_feasible, reject_data = check_goal_feasibility(
        user["id"], target_distance_km, target_date, days_per_week
    )
    
    if not is_feasible:
        return jsonify({
            "status": "error", 
            "error": "infeasible",
            "message": reject_data["message"],
            "suggested_date": reject_data["suggested_date"]
        }), 400
        
    conn = get_db()
    from datetime import datetime
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn.execute(
        """
        INSERT INTO user_goals 
        (user_id, goal_type, target_distance_km, target_date, days_per_week, status, created_at, dashboard_pinned)
        VALUES (?, ?, ?, ?, ?, 'active', ?, ?)
        """,
        (user["id"], goal_type, target_distance_km, target_date, days_per_week, now_str, False)
    )
    conn.commit()
    conn.close()
    
    return jsonify({"status": "success", "message": "Goal created"})

@app.route("/api/goals/<int:goal_id>", methods=["DELETE"])
def api_goals_delete(goal_id):
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    
    conn = get_db()
    conn.execute("DELETE FROM user_goals WHERE id = ? AND user_id = ?", (goal_id, user["id"]))
    conn.commit()
    conn.close()
    
    return jsonify({"status": "success"})

@app.route("/api/goals/<int:goal_id>/extend", methods=["POST"])
def api_goals_extend(goal_id):
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    
    data = request.get_json()
    new_date = data.get("new_date")
    if not new_date:
        return jsonify({"status": "error", "message": "Missing new_date"}), 400
        
    # Optional: could do validation that it's in the future
        
    conn = get_db()
    goal = conn.execute("SELECT id FROM user_goals WHERE id = ? AND user_id = ?", (goal_id, user["id"])).fetchone()
    if not goal:
        conn.close()
        return jsonify({"status": "error", "message": "Goal not found"}), 404
        
    conn.execute("UPDATE user_goals SET target_date = ? WHERE id = ?", (new_date, goal_id))
    conn.commit()
    conn.close()
    
    return jsonify({"status": "success"})

@app.route("/api/badges", methods=["GET"])
def get_badges():
    """
    Returns all badges with unlock status for the current user.
    Uses the hardcoded BADGE_METADATA from badge_service.
    """
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    
    from services.badge_service import BADGE_METADATA, get_user_badges
    
    # Get user's unlocked badges
    unlocked = get_user_badges(user["id"])
    unlocked_keys = {b['badge_key']: b['unlocked_at'] for b in unlocked}
    
    result = []
    for key, meta in BADGE_METADATA.items():
        is_unlocked = key in unlocked_keys
        
        result.append({
            'key': key,
            'name': meta['name'],
            'description': meta['description'],
            'icon': meta['icon'],
            'earned': is_unlocked,
            'unlocked_at': unlocked_keys.get(key)
        })
        
    return jsonify({"status": "success", "badges": result})


@app.route("/api/level", methods=["GET"])
def api_level():
    """Current rank, progress to the next rank, and the full ladder."""
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    from services.level_service import get_total_km, level_info, ladder
    info = level_info(get_total_km(user["id"]))
    info["ladder"] = ladder()
    return jsonify(info), 200


@app.route("/api/badges/progress", methods=["GET"])
def get_badge_progress():
    """
    Returns compact progress data for UI progress bars.
    """
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401
    conn = get_db()
    
    stats = conn.execute(
        "SELECT * FROM user_stats WHERE user_id = ?",
        (user["id"],)
    ).fetchone()
    
    conn.close()
    
    if not stats:
        return jsonify({
            'total_distance': 0,
            'current_streak': 0,
            'best_streak': 0
        }), 200
    
    return jsonify({
        'total_distance': stats['total_distance_km'],
        'current_streak': stats['current_streak'],
        'best_streak': stats['best_streak']
    }), 200



# ---------------------------------------------------------------------------
# PUBLIC PROFILE — Avatar, Bio/Next-Race, Public Data, Profile Page
# ---------------------------------------------------------------------------

MAX_AVATAR_SIZE = 5 * 1024 * 1024  # 5 MB
ALLOWED_AVATAR_TYPES = {"image/jpeg", "image/png", "image/webp"}
ALLOWED_AVATAR_EXTENSIONS = {".jpg", ".jpeg", ".png", ".webp"}


@app.route("/api/profile/avatar", methods=["POST", "DELETE"])
@limiter.limit("10 per hour")
def upload_avatar():
    """Upload or remove an avatar for the current user."""
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    if request.method == "DELETE":
        conn = get_db()
        conn.execute(
            "UPDATE users SET avatar_image = NULL, avatar_mime_type = NULL WHERE id = ?",
            (user["id"],)
        )
        conn.commit()
        conn.close()
        return jsonify({"success": True, "message": "Avatar removed"}), 200

    if "avatar" not in request.files:
        return jsonify({"error": "No file uploaded"}), 400

    f = request.files["avatar"]
    if not f or not f.filename:
        return jsonify({"error": "No file selected"}), 400

    import os as _os
    ext = _os.path.splitext(f.filename.lower())[1]
    if ext not in ALLOWED_AVATAR_EXTENSIONS:
        return jsonify({"error": "Only JPG, PNG and WebP images are allowed"}), 400

    # Read into memory and check size before touching Pillow
    raw_bytes = f.read()
    if len(raw_bytes) > MAX_AVATAR_SIZE:
        return jsonify({"error": "File too large (max 5 MB)"}), 400

    # Resize / re-compress with Pillow
    try:
        from PIL import Image
        import io as _io
        img = Image.open(_io.BytesIO(raw_bytes))
        img = img.convert("RGB")          # strip EXIF alpha / CMYK
        img.thumbnail((400, 400), Image.LANCZOS)
        out = _io.BytesIO()
        img.save(out, format="JPEG", quality=85, optimize=True)
        compressed = out.getvalue()
    except Exception as e:
        return jsonify({"error": "Invalid image file. Please upload a valid JPG, PNG, or WebP image."}), 400

    conn = get_db()
    conn.execute(
        "UPDATE users SET avatar_image = ?, avatar_mime_type = ? WHERE id = ?",
        (compressed, "image/jpeg", user["id"])
    )
    conn.commit()
    conn.close()
    return jsonify({"success": True, "message": "Avatar updated"}), 200


@app.route("/avatar/<username>")
def serve_avatar(username):
    """Serve a user's avatar image. Returns 404 if no avatar set."""
    conn = get_db()
    row = conn.execute(
        "SELECT avatar_image, avatar_mime_type FROM users WHERE LOWER(username) = LOWER(?) AND COALESCE(status, 'active') != 'blocked'",
        (username,)
    ).fetchone()
    conn.close()

    if not row or not row["avatar_image"]:
        return jsonify({"error": "No avatar"}), 404

    from flask import Response
    mime = row["avatar_mime_type"] or "image/jpeg"
    return Response(
        bytes(row["avatar_image"]),
        status=200,
        mimetype=mime,
        headers={"Cache-Control": "public, max-age=3600"}
    )


@app.route("/api/profile/details", methods=["POST"])
def update_profile_details():
    """Update bio and next_race for the logged-in user."""
    user = get_current_user()
    if not user:
        return jsonify({"error": "Unauthorized"}), 401

    import bleach
    data = request.get_json() or {}

    bio = data.get("bio", "")
    next_race = data.get("next_race", "")

    # Length validation
    if len(bio) > 200:
        return jsonify({"error": "Bio must be 200 characters or fewer"}), 400
    if len(next_race) > 100:
        return jsonify({"error": "Next race must be 100 characters or fewer"}), 400

    # Strip all HTML/script tags to prevent XSS
    bio = bleach.clean(bio, tags=[], strip=True).strip()
    next_race = bleach.clean(next_race, tags=[], strip=True).strip()

    conn = get_db()
    conn.execute(
        "UPDATE users SET bio = ?, next_race = ? WHERE id = ?",
        (bio, next_race, user["id"])
    )
    conn.commit()
    conn.close()
    return jsonify({"success": True}), 200


@app.route("/api/user/<username>/public-profile")
def api_public_profile(username):
    """
    Return ONLY public-safe data for a user profile page.
    No authentication required — profile URLs are publicly shareable.
    NEVER returns: email, weight, height, google_id, google_email,
                   home_city, home_latitude, home_longitude,
                   recovery_email, pin, bmi, or any internal fields.
    """
    conn = get_db()
    target = conn.execute(
        "SELECT id, username, display_name, profile_emoji, bio, next_race, avatar_image, avatar_mime_type "
        "FROM users WHERE LOWER(username) = LOWER(?) AND COALESCE(status, 'active') != 'blocked'",
        (username,)
    ).fetchone()

    if not target:
        conn.close()
        return jsonify({"error": "User not found"}), 404

    target_id = target["id"]

    # --- Run stats ---
    # All four come from the runs table itself. total_distance_km used to be
    # read from the user_stats cache, which edit_run / clear_data never update
    # and out-of-band inserts never create, so it could disagree with the
    # other stats computed right beside it (e.g. 0 km total, 12 km longest run).
    runs_agg = conn.execute(
        "SELECT COUNT(*) as run_count, "
        "COALESCE(SUM(distance_km), 0) as total_distance_km, "
        "COALESCE(SUM(time_min), 0) as total_time_min, "
        "COALESCE(MAX(distance_km), 0) as longest_run_km "
        "FROM runs WHERE user_id = ?",
        (target_id,)
    ).fetchone()
    run_count         = runs_agg["run_count"] if runs_agg else 0
    total_distance_km = round(runs_agg["total_distance_km"], 2) if runs_agg else 0.0
    total_time_min    = round(runs_agg["total_time_min"], 1) if runs_agg else 0.0
    longest_run_km    = round(runs_agg["longest_run_km"], 2) if runs_agg else 0.0
    avg_pace        = round(total_time_min / total_distance_km, 2) if total_distance_km > 0 else 0.0

    # --- Social counts ---
    follower_count = conn.execute(
        "SELECT COUNT(*) as cnt FROM friends WHERE followed_id = ?", (target_id,)
    ).fetchone()["cnt"]
    following_count = conn.execute(
        "SELECT COUNT(*) as cnt FROM friends WHERE follower_id = ?", (target_id,)
    ).fetchone()["cnt"]

    # --- Challenges completed ---
    challenges_completed = conn.execute(
        "SELECT COUNT(*) as cnt FROM user_challenge_progress "
        "WHERE user_id = ? AND completed_at IS NOT NULL",
        (target_id,)
    ).fetchone()["cnt"]

    # --- Viewer relationship (lets the dashboard popup show Follow / Unfollow) ---
    viewer = get_current_user()
    is_own_profile = viewer is not None and viewer["id"] == target_id
    is_following = False
    if viewer and not is_own_profile:
        is_following = conn.execute(
            "SELECT 1 FROM friends WHERE follower_id = ? AND followed_id = ?",
            (viewer["id"], target_id)
        ).fetchone() is not None

    # --- Personal bests (reuse helper) ---
    personal_bests = get_personal_bests_for_user(target_id, conn)
    conn.close()

    # --- Badges (reuse helper) ---
    badges = get_badges_status_for_user(target_id)

    # --- Distance progress for numeric badges (TOTAL_50KM, TOTAL_100KM) ---
    for badge in badges:
        if badge["key"] == "TOTAL_50KM" and not badge["earned"]:
            badge["progress"] = min(total_distance_km, 50.0)
            badge["progress_target"] = 50.0
        elif badge["key"] == "TOTAL_100KM" and not badge["earned"]:
            badge["progress"] = min(total_distance_km, 100.0)
            badge["progress_target"] = 100.0

    from services.level_service import level_info
    return jsonify({
        "username": target["username"],
        "display_name": target["display_name"] or target["username"],
        "profile_emoji": target["profile_emoji"],
        "bio": target["bio"] or "",
        "next_race": target["next_race"] or "",
        "has_avatar": bool(target["avatar_image"]),
        "run_count": run_count,
        "total_distance_km": total_distance_km,
        "total_time_min": total_time_min,
        "avg_pace_min_per_km": avg_pace,
        "longest_run_km": longest_run_km,
        "follower_count": follower_count,
        "following_count": following_count,
        "challenges_completed": challenges_completed,
        "badges": badges,
        "personal_bests": personal_bests,
        "is_own_profile": is_own_profile,
        "is_following": is_following,
        "rank": level_info(total_distance_km),
    }), 200


@app.route("/api/user/<username>/heatmap")
def api_user_heatmap(username):
    """Public heatmap data for a specific user (past 365 days)."""
    conn = get_db()
    target = conn.execute(
        "SELECT id FROM users WHERE LOWER(username) = LOWER(?) AND COALESCE(status, 'active') != 'blocked'",
        (username,)
    ).fetchone()

    if not target:
        conn.close()
        return jsonify({"error": "User not found"}), 404

    today = date.today()
    start_date = today - timedelta(days=364)

    runs = conn.execute(
        "SELECT date, SUM(distance_km) as total FROM runs "
        "WHERE user_id = ? AND date >= ? GROUP BY date ORDER BY date ASC",
        (target["id"], start_date.strftime("%Y-%m-%d"))
    ).fetchall()
    conn.close()

    run_map = {str(r["date"])[:10]: round(r["total"], 2) for r in runs}
    days = []
    for i in range(365):
        d = start_date + timedelta(days=i)
        d_str = d.strftime("%Y-%m-%d")
        days.append({"date": d_str, "km": run_map.get(d_str, 0)})

    return jsonify({"days": days}), 200


@app.route("/u/<username>")
def public_profile(username):
    """
    Public profile page for any user.
    No login required — profile is publicly viewable.
    """
    conn = get_db()
    target = conn.execute(
        "SELECT id, username, display_name, profile_emoji, height, weight FROM users WHERE LOWER(username) = LOWER(?) AND COALESCE(status, 'active') != 'blocked'",
        (username,)
    ).fetchone()
    conn.close()

    if not target:
        from flask import abort
        abort(404)

    viewer = get_current_user()
    viewer_username = viewer["username"] if viewer else None
    is_own_profile = viewer is not None and viewer["username"] == username

    # Is the viewer already following this profile?
    is_following = False
    if viewer and not is_own_profile:
        conn = get_db()
        row = conn.execute(
            "SELECT 1 FROM friends WHERE follower_id = ? AND followed_id = ?",
            (viewer["id"], target["id"])
        ).fetchone()
        conn.close()
        is_following = row is not None

    theme = viewer["theme"] if viewer and viewer["theme"] else "system"

    weight_val = target["weight"] if "weight" in target.keys() else None
    height_val = target["height"] if "height" in target.keys() else None
    bmi = None
    if weight_val and height_val and float(height_val) > 0:
        bmi = round(float(weight_val) / ((float(height_val) / 100) ** 2), 1)

    return render_template(
        "public_profile.html",
        profile_username=username,
        viewer_username=viewer_username,
        is_own_profile=is_own_profile,
        is_following=is_following,
        theme=theme,
        profile_height=height_val,
        profile_weight=weight_val,
        profile_bmi=bmi,
    )


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




# ---------- NOTIFICATIONS (PHASE 2) ----------
from utils.decorators import login_required, admin_required

@app.route("/admin/notifications")
@login_required
@admin_required
def admin_notifications_page():
    user = get_current_user()
    return render_template("admin_notifications.html", user=user, theme=user["theme"] or "system")

@app.route("/api/admin/notifications", methods=["GET"])
@login_required
@admin_required
def get_admin_notifications():
    conn = get_db()
    rows = conn.execute(
        "SELECT n.*, u.username as creator_username FROM notifications n "
        "LEFT JOIN users u ON n.created_by = u.id ORDER BY n.created_at DESC LIMIT 100"
    ).fetchall()
    conn.close()
    return jsonify({"notifications": [dict(r) for r in rows]})

@app.route("/api/admin/notifications", methods=["POST"])
@login_required
@admin_required
def create_admin_notification():
    user = get_current_user()
    data = request.json
    title = data.get("title", "").strip()
    message = data.get("message", "").strip()
    notif_type = data.get("type", "SYSTEM")
    audience_type = data.get("audience_type", "EVERYONE")
    target_username = data.get("target_username")

    if not title or not message:
        return jsonify({"error": "Title and message are required"}), 400

    conn = get_db()
    from db import USE_PG
    try:
        if USE_PG:
            cur = conn.execute(
                "INSERT INTO notifications (title, message, type, created_by, audience_type) "
                "VALUES (?, ?, ?, ?, ?) RETURNING id",
                (title, message, notif_type, user["id"], audience_type)
            )
            notif_id = cur.fetchone()["id"]
        else:
            cur = conn.execute(
                "INSERT INTO notifications (title, message, type, created_by, audience_type) "
                "VALUES (?, ?, ?, ?, ?)",
                (title, message, notif_type, user["id"], audience_type)
            )
            notif_id = cur._cursor.lastrowid if hasattr(cur, '_cursor') else cur.lastrowid
            
        if audience_type == "SPECIFIC_USER" and target_username:
            conn.execute(
                "INSERT INTO user_notifications (notification_id, user_id) "
                "SELECT ?, id FROM users WHERE username = ?",
                (notif_id, target_username)
            )
        else:
            conn.execute(
                "INSERT INTO user_notifications (notification_id, user_id) "
                "SELECT ?, id FROM users WHERE COALESCE(status, 'active') = 'active'",
                (notif_id,)
            )
        conn.commit()
    except Exception as e:
        conn.rollback()
        return jsonify({"error": str(e)}), 500
    finally:
        conn.close()
        
    return jsonify({"success": True, "message": "Notification sent successfully"})

@app.route("/api/notifications", methods=["GET"])
def get_user_notifications():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
    
    user = get_current_user()
    limit = int(request.args.get("limit", 20))
    
    conn = get_db()
    rows = conn.execute(
        "SELECT n.id, n.title, n.message, n.type, n.created_at, un.read_at "
        "FROM user_notifications un "
        "JOIN notifications n ON un.notification_id = n.id "
        "WHERE un.user_id = ? "
        "ORDER BY n.created_at DESC LIMIT ?",
        (user["id"], limit)
    ).fetchall()
    conn.close()
    
    return jsonify({"notifications": [dict(r) for r in rows]})

@app.route("/api/notifications/unread-count", methods=["GET"])
def get_unread_count():
    if not require_login():
        return jsonify({"count": 0})
        
    user = get_current_user()
    conn = get_db()
    row = conn.execute(
        "SELECT COUNT(*) as count FROM user_notifications "
        "WHERE user_id = ? AND read_at IS NULL",
        (user["id"],)
    ).fetchone()
    conn.close()
    
    return jsonify({"count": row["count"] if row else 0})

@app.route("/api/notifications/<int:notif_id>/read", methods=["POST"])
def mark_notification_read(notif_id):
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
        
    user = get_current_user()
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn = get_db()
    conn.execute(
        "UPDATE user_notifications SET read_at = ? "
        "WHERE notification_id = ? AND user_id = ? AND read_at IS NULL",
        (now_str, notif_id, user["id"])
    )
    conn.commit()
    conn.close()
    return jsonify({"success": True})

@app.route("/api/notifications/read-all", methods=["POST"])
def mark_all_notifications_read():
    if not require_login():
        return jsonify({"error": "Unauthorized"}), 401
        
    user = get_current_user()
    now_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    
    conn = get_db()
    conn.execute(
        "UPDATE user_notifications SET read_at = ? "
        "WHERE user_id = ? AND read_at IS NULL",
        (now_str, user["id"])
    )
    conn.commit()
    conn.close()
    return jsonify({"success": True})


# ---------- RUN APP ----------




if __name__ == "__main__":
    app.run(debug=True, host="0.0.0.0", port=5000)