import os, sqlite3, json, secrets as _secrets, csv, io
from urllib.parse import urlencode, urljoin
from dotenv import load_dotenv
from flask import request, render_template, redirect, url_for, jsonify, session, flash, abort, g, send_file
from werkzeug.utils import secure_filename
from werkzeug.security import generate_password_hash, check_password_hash
from slugify import slugify
import stripe
import requests as _requests
from helpers import get_today_usage, get_plan_tool_limits
from helpers import (_db, url, get_current_user, login_required_user, user_has_paid_plan, get_user_by_email,
                     get_user_by_username, create_user, create_or_get_google_user,
                     create_or_get_microsoft_user, create_or_get_apple_user, get_user_by_id,
                     _check_password, _hash_password,
                     get_all_plans, get_plan_by_id, get_plan_periods, get_period_by_id, calc_period_price,
                     _seed_billing_periods_for_plan, _downgrade_user_to_free, get_user_conversions,
                     get_user_notif_prefs, _NOTIF_LABELS, send_email, send_user_notification, log_event,
                     log_deletion,
                     get_site_settings, get_payment_setting, ensure_referral_code, get_user_by_referral_code,
                     get_user_referrals, get_user_rewards, grant_referral_reward, _user_avatar_color,
                     _notify_admin_pending_blog, dir_path)

load_dotenv()
# Read website_url directly from .env — single source of truth
website_url = os.getenv("website_url", "localhost")


def _validate_password_strength(password):
    import re as _re_pw
    errors = []
    if len(password) < 8:
        errors.append("Password must be at least 8 characters.")
    if not _re_pw.search(r'[A-Z]', password):
        errors.append("Password must contain at least one uppercase letter.")
    if not _re_pw.search(r'[a-z]', password):
        errors.append("Password must contain at least one lowercase letter.")
    if not _re_pw.search(r'[0-9]', password):
        errors.append("Password must contain at least one number.")
    if not _re_pw.search(r'[^A-Za-z0-9]', password):
        errors.append("Password must contain at least one special character.")
    return errors


def register_user_routes(app):

    @app.before_request
    def _check_session_not_revoked():
        user_id = session.get('user_id')
        if not user_id:
            return
        token = session.get('_sec_token')
        if not token:
            return
        with _db() as c:
            row = c.execute(
                "SELECT revoked FROM user_sessions WHERE session_token=? AND user_id=?",
                (token, user_id)
            ).fetchone()
        if row and dict(row)['revoked']:
            session.clear()
            return redirect('/login')

    @app.route("/<lang_code>/register", methods=['GET', 'POST'])
    @app.route('/register', methods=['GET', 'POST'])
    def user_register():
        if session.get('user_id'):
            return redirect(url_for('dashboard'))
        
        # Fetch site settings once at the beginning
        site_settings = get_site_settings()
        
        all_plans = get_all_plans()
        plan_periods_map = {}
        for pl in all_plans:
            periods = get_plan_periods(pl['id'])
            if not periods:
                _seed_billing_periods_for_plan(pl['id'])
                periods = get_plan_periods(pl['id'])
            plan_periods_map[pl['id']] = [dict(p) for p in periods]
        
        if request.method == 'POST':
            username = request.form.get('username', '').strip()
            email = request.form.get('email', '').strip().lower()
            password = request.form.get('password', '')
            confirm = request.form.get('confirm_password', '')
            chosen_plan_id = request.form.get('plan_id', type=int) or 1
            preferred_months = request.form.get('preferred_months', type=int) or 1
            errors = []
            import re as _re_val
            if not username or len(username) < 3:
                errors.append("Username must be at least 3 characters.")
            if not email or not _re_val.match(r'^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$', email):
                errors.append("Please enter a valid email address.")
            _pw_errors = _validate_password_strength(password)
            if _pw_errors:
                errors.extend(_pw_errors)
            if password != confirm:
                errors.append("Passwords do not match.")
            if get_user_by_username(username):
                errors.append("That username is already taken.")
            if get_user_by_email(email):
                errors.append("An account with that email already exists.")
            chosen_plan = get_plan_by_id(chosen_plan_id) or (all_plans[0] if all_plans else None)
            if not chosen_plan:
                errors.append("Please select a valid plan.")
            if errors:
                return render_template('auth/register.html', 
                                    errors=errors,
                                    username=username, 
                                    email=email,
                                    plans=all_plans, 
                                    selected_plan_id=chosen_plan_id,
                                    plan_periods_map=plan_periods_map,
                                    preferred_months=preferred_months,
                                    site_settings=site_settings)  # ADDED
            
            ref_code = request.form.get('ref_code', '').strip()
            referrer = get_user_by_referral_code(ref_code) if ref_code else None
            # Validate promo code if provided
            promo_code_str = request.form.get('promo_code', '').strip().upper()
            promo_row = None
            if promo_code_str:
                from datetime import datetime as _dt
                with _db() as c:
                    _promo = c.execute(
                        "SELECT * FROM promo_codes WHERE code=? COLLATE NOCASE AND is_active=1",
                        (promo_code_str,)
                    ).fetchone()
                if _promo:
                    _expired = _promo['expires_at'] and _dt.utcnow() > _dt.fromisoformat(_promo['expires_at'])
                    _maxed = _promo['max_uses'] > 0 and _promo['uses_count'] >= _promo['max_uses']
                    if not _expired and not _maxed:
                        promo_row = dict(_promo)
                    else:
                        errors.append("Promo code is expired or has reached its usage limit.")
                else:
                    errors.append("Invalid or inactive promo code.")
                if errors:
                    return render_template('auth/register.html', 
                                        errors=errors,
                                        username=username, 
                                        email=email,
                                        plans=all_plans, 
                                        selected_plan_id=chosen_plan_id,
                                        plan_periods_map=plan_periods_map,
                                        preferred_months=preferred_months,
                                        promo_code=promo_code_str,
                                        site_settings=site_settings)  # ADDED
            
            user_id = create_user(username, email, password)
            if not user_id:
                return render_template('auth/register.html',
                                    errors=["Registration failed. Please try again."],
                                    plans=all_plans, 
                                    selected_plan_id=chosen_plan_id,
                                    plan_periods_map=plan_periods_map,
                                    preferred_months=preferred_months, 
                                    ref_code=ref_code,
                                    site_settings=site_settings)  # ADDED
            
            import secrets as _sec
            _verify_token = _sec.token_urlsafe(32)
            country = request.form.get('country', '').strip()
            with _db() as _pc:
                _pc.execute("UPDATE users SET plan_id=?, email_verification_token=?, email_verified=0 WHERE id=?",
                            (chosen_plan_id, _verify_token, user_id))
                if country:
                    _pc.execute("UPDATE users SET country=? WHERE id=?", (country, user_id))
                if referrer and referrer['id'] != user_id:
                    _pc.execute("UPDATE users SET referred_by_user_id=? WHERE id=?", (referrer['id'], user_id))
                if promo_row:
                    _pc.execute("UPDATE users SET promo_code_id=? WHERE id=?", (promo_row['id'], user_id))
                    _pc.execute("UPDATE promo_codes SET uses_count=uses_count+1 WHERE id=?", (promo_row['id'],))
                _pc.commit()
            ensure_referral_code(user_id)
            session['user_id'] = user_id
            log_event('user_registered', f"New user registered: {username} ({email})", user_id=user_id, username=username)
            try:
                _settings = get_site_settings()
                _site_name = _settings.get('site_name', 'OnlineConvert')
                _verify_url = f"https://{ALLOWED_DOMAIN}/verify-email/{_verify_token}"
                send_email(email, f"Verify your email – {_site_name}",
                    f"""<div style="font-family:Arial,sans-serif;max-width:520px;margin:0 auto;">
                    <h2 style="color:#4f46e5;">Verify Your Email Address</h2>
                    <p>Hi {username}, thanks for registering! Please verify your email to unlock all features.</p>
                    <p style="margin:24px 0;">
                        <a href="{_verify_url}" style="background:#4f46e5;color:#fff;padding:12px 28px;border-radius:8px;text-decoration:none;font-weight:bold;">Verify Email Address</a>
                    </p>
                    <p style="color:#6b7280;font-size:12px;">If you didn't register, ignore this email. Link expires in 7 days.</p>
                    </div>""",
                    f"Verify your email: {_verify_url}"
                )
            except Exception:
                pass
            if chosen_plan and chosen_plan['price_monthly'] > 0:
                plan_periods = plan_periods_map.get(chosen_plan_id, [])
                preferred_period = next(
                    (p for p in plan_periods if p['months'] == preferred_months), None
                ) or (plan_periods[0] if plan_periods else None)
                checkout_url = url_for('checkout_select', plan_id=chosen_plan_id)
                if preferred_period:
                    checkout_url += f"?period={preferred_period['id']}"
                return redirect(checkout_url)
            flash("Welcome! Your account has been created. Please check your email to verify your address.", "success")
            return redirect(url_for('dashboard'))
        
        # GET request
        ref_code = request.args.get('ref', '').strip()
        selected_plan_id = request.args.get('plan', type=int) or 1
        preferred_months = request.args.get('months', type=int) or 1
        return render_template('auth/register.html', 
                            plans=all_plans, 
                            selected_plan_id=selected_plan_id,
                            plan_periods_map=plan_periods_map,
                            preferred_months=preferred_months, 
                            ref_code=ref_code,
                            site_settings=site_settings)  # ADDED


    # ─── Google OAuth ────────────────────────────────────────────────────────────

    def _google_client_id():
        return (os.environ.get('GOOGLE_CLIENT_ID') or
                get_payment_setting('google_client_id', '') or '')

    def _google_client_secret():
        return (os.environ.get('GOOGLE_CLIENT_SECRET') or
                get_payment_setting('google_client_secret', '') or '')

    def _google_redirect_uri():
        replit_domain = os.environ.get('REPLIT_DEV_DOMAIN')
        if replit_domain:
            base = f'https://{replit_domain}'
        else:
            base = request.host_url.rstrip('/')
        return base + '/auth/google/callback'

    @app.route("/<lang_code>/auth/google")
    @app.route('/auth/google')
    def google_login():
        client_id = _google_client_id()
        if not client_id:
            flash("Google login is not configured yet.", "error")
            return redirect(url_for('user_login'))
        # Support ?action=link to link Google to an existing logged-in account
        if request.args.get('action') == 'link' and session.get('user_id'):
            session['google_link_action'] = True
        state = _secrets.token_urlsafe(16)
        session['google_oauth_state'] = state
        params = {
            'client_id': client_id,
            'redirect_uri': _google_redirect_uri(),
            'response_type': 'code',
            'scope': 'openid email profile',
            'state': state,
            'access_type': 'offline',
            'prompt': 'select_account',
        }
        return redirect('https://accounts.google.com/o/oauth2/v2/auth?' + urlencode(params))

    @app.route("/<lang_code>/auth/google/callback")
    @app.route('/auth/google/callback')
    def google_callback():
        error = request.args.get('error')
        if error:
            flash(f"Google login cancelled: {error}", "error")
            return redirect(url_for('user_login'))
        code = request.args.get('code')
        state = request.args.get('state')
        if not code or state != session.pop('google_oauth_state', None):
            flash("Invalid Google OAuth response. Please try again.", "error")
            return redirect(url_for('user_login'))
        client_id = _google_client_id()
        client_secret = _google_client_secret()
        if not client_id or not client_secret:
            flash("Google login is not configured yet.", "error")
            return redirect(url_for('user_login'))
        try:
            token_resp = _requests.post('https://oauth2.googleapis.com/token', data={
                'code': code,
                'client_id': client_id,
                'client_secret': client_secret,
                'redirect_uri': _google_redirect_uri(),
                'grant_type': 'authorization_code',
            }, timeout=10)
            token_data = token_resp.json()
            if 'error' in token_data:
                flash(f"Google token error: {token_data.get('error_description', token_data['error'])}", "error")
                return redirect(url_for('user_login'))
            user_info_resp = _requests.get(
                'https://www.googleapis.com/oauth2/v3/userinfo',
                headers={'Authorization': f"Bearer {token_data['access_token']}"},
                timeout=10
            )
            user_info = user_info_resp.json()
            google_id = user_info.get('sub')
            email = user_info.get('email', '')
            display_name = user_info.get('name', '')
            picture_url = user_info.get('picture', '')
            if not google_id or not email:
                flash("Could not retrieve your Google account info. Please try again.", "error")
                return redirect(url_for('user_login'))
            # If a logged-in user is linking Google to their account
            link_action = session.pop('google_link_action', False)
            if link_action and session.get('user_id'):
                logged_user_id = session['user_id']
                # Check if this google account is already linked to another user
                with _db() as c:
                    existing = c.execute(
                        "SELECT id FROM users WHERE google_id=? AND id!=?", (google_id, logged_user_id)
                    ).fetchone()
                    if existing:
                        flash("This Google account is already linked to another account.", "error")
                        return redirect(url_for('dashboard_profile') + '?tab=social')
                    c.execute(
                        "UPDATE users SET google_id=?, google_email=? WHERE id=?",
                        (google_id, email, logged_user_id)
                    )
                    c.commit()
                flash("Google account linked successfully!", "success")
                log_event('google_linked', f"Google account linked: {email}", user_id=logged_user_id)
                return redirect(url_for('dashboard_profile') + '?tab=social')

            user = create_or_get_google_user(google_id, email, display_name, picture_url=picture_url)
            if not user:
                flash("Could not create account. Please try again.", "error")
                return redirect(url_for('user_login'))
            session['user_id'] = user['id']
            ensure_referral_code(user['id'])
            log_event('user_login', f"Google login: {user['username']} ({email})", user_id=user['id'])
            next_url = session.pop('next', None) or url_for('dashboard')
            return redirect(next_url)
        except Exception as e:
            flash(f"Google login failed: {str(e)}", "error")
            return redirect(url_for('user_login'))


    # ─── Google One Tap ──────────────────────────────────────────────────────────

    @app.route("/<lang_code>/auth/google-one-tap", methods=['POST'])
    @app.route('/auth/google-one-tap', methods=['POST'])
    def google_one_tap():
        """Verify a Google One Tap credential (ID token JWT) and log the user in."""
        try:
            data = request.get_json(force=True, silent=True) or {}
            credential = (data.get('credential') or '').strip()
            if not credential:
                return jsonify({'success': False, 'error': 'No credential provided'})

            client_id = _google_client_id()
            if not client_id:
                return jsonify({'success': False, 'error': 'Google login is not configured'})

            # Verify the ID token by calling Google's tokeninfo endpoint
            resp = _requests.get(
                'https://oauth2.googleapis.com/tokeninfo',
                params={'id_token': credential},
                timeout=10
            )
            if resp.status_code != 200:
                return jsonify({'success': False, 'error': 'Token verification failed'})

            token_info = resp.json()

            # Security: ensure the token was issued for our app
            aud = token_info.get('aud', '')
            if aud != client_id:
                return jsonify({'success': False, 'error': 'Token audience mismatch'})

            # Ensure email is verified
            if token_info.get('email_verified') not in (True, 'true'):
                return jsonify({'success': False, 'error': 'Email not verified by Google'})

            google_id = token_info.get('sub', '')
            email = token_info.get('email', '')
            display_name = token_info.get('name', '') or token_info.get('given_name', '')
            picture_url = token_info.get('picture', '')

            if not google_id or not email:
                return jsonify({'success': False, 'error': 'Could not retrieve account info'})

            user = create_or_get_google_user(google_id, email, display_name, picture_url=picture_url)
            if not user:
                return jsonify({'success': False, 'error': 'Could not create account'})

            session['user_id'] = user['id']
            ensure_referral_code(user['id'])
            log_event('user_login', f"Google One Tap login: {user['username']} ({email})",
                      user_id=user['id'])
            return jsonify({'success': True})

        except Exception as exc:
            app.logger.error("Google One Tap login error: %s", exc, exc_info=True)
            return jsonify({'success': False, 'error': 'Login failed — please try again'})

    # ─── Social: Unlink Google ────────────────────────────────────────────────────

    @app.route("/<lang_code>/account/social/unlink-google", methods=['POST'])
    @app.route('/account/social/unlink-google', methods=['POST'])
    @login_required_user
    def user_unlink_google():
        user = get_current_user()
        with _db() as c:
            row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
        pw_hash = row['password_hash'] if row else ''
        if not pw_hash:
            flash("Cannot unlink Google — it is your only login method. Set a password first.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        provided = request.form.get('current_password', '').strip()
        if not provided or not _check_password(pw_hash, provided):
            flash("Incorrect password. Google account was not disconnected.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        with _db() as c:
            c.execute("UPDATE users SET google_id='', google_email='' WHERE id=?", (user['id'],))
            c.commit()
        flash("Google account disconnected successfully.", "success")
        log_event('google_unlinked', "Google account unlinked", user_id=user['id'])
        return redirect(url_for('dashboard_profile') + '?tab=social')

    @app.route("/<lang_code>/account/social/verify-for-google-link", methods=['POST'])
    @app.route('/account/social/verify-for-google-link', methods=['POST'])
    @login_required_user
    def user_verify_for_google_link():
        user = get_current_user()
        with _db() as c:
            row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
        pw_hash = row['password_hash'] if row else ''
        provided = request.form.get('current_password', '').strip()
        if not provided or (pw_hash and not _check_password(pw_hash, provided)):
            flash("Incorrect password. Please try again.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        session['google_link_action'] = True
        return redirect(url_for('google_login') + '?action=link')

    # ─── Microsoft OAuth ─────────────────────────────────────────────────────────

    def _ms_client_id():
        return (os.environ.get('MICROSOFT_CLIENT_ID') or
                get_payment_setting('microsoft_client_id', '') or '')

    def _ms_client_secret():
        return (os.environ.get('MICROSOFT_CLIENT_SECRET') or
                get_payment_setting('microsoft_client_secret', '') or '')

    def _ms_redirect_uri():
        replit_domain = os.environ.get('REPLIT_DEV_DOMAIN')
        base = f'https://{replit_domain}' if replit_domain else request.host_url.rstrip('/')
        return base + '/auth/microsoft/callback'

    @app.route("/<lang_code>/auth/microsoft")
    @app.route('/auth/microsoft')
    def microsoft_login():
        client_id = _ms_client_id()
        if not client_id:
            flash("Microsoft login is not configured yet.", "error")
            return redirect(url_for('user_login'))
        if request.args.get('action') == 'link' and session.get('user_id'):
            session['microsoft_link_action'] = True
        state = _secrets.token_urlsafe(16)
        session['microsoft_oauth_state'] = state
        params = {
            'client_id': client_id,
            'redirect_uri': _ms_redirect_uri(),
            'response_type': 'code',
            'scope': 'openid email profile User.Read',
            'state': state,
            'prompt': 'select_account',
        }
        return redirect('https://login.microsoftonline.com/common/oauth2/v2.0/authorize?' + urlencode(params))

    @app.route("/<lang_code>/auth/microsoft/callback")
    @app.route('/auth/microsoft/callback')
    def microsoft_callback():
        error = request.args.get('error')
        if error:
            flash(f"Microsoft login cancelled: {error}", "error")
            return redirect(url_for('user_login'))
        code = request.args.get('code')
        state = request.args.get('state')
        if not code or state != session.pop('microsoft_oauth_state', None):
            flash("Invalid Microsoft OAuth response. Please try again.", "error")
            return redirect(url_for('user_login'))
        client_id = _ms_client_id()
        client_secret = _ms_client_secret()
        if not client_id or not client_secret:
            flash("Microsoft login is not configured yet.", "error")
            return redirect(url_for('user_login'))
        try:
            token_resp = _requests.post(
                'https://login.microsoftonline.com/common/oauth2/v2.0/token',
                data={
                    'code': code,
                    'client_id': client_id,
                    'client_secret': client_secret,
                    'redirect_uri': _ms_redirect_uri(),
                    'grant_type': 'authorization_code',
                }, timeout=10)
            token_data = token_resp.json()
            if 'error' in token_data:
                flash(f"Microsoft token error: {token_data.get('error_description', token_data['error'])}", "error")
                return redirect(url_for('user_login'))
            user_info_resp = _requests.get(
                'https://graph.microsoft.com/v1.0/me',
                headers={'Authorization': f"Bearer {token_data['access_token']}"},
                timeout=10)
            user_info = user_info_resp.json()
            microsoft_id = user_info.get('id')
            email = user_info.get('mail') or user_info.get('userPrincipalName', '')
            display_name = user_info.get('displayName', '')
            if not microsoft_id or not email:
                flash("Could not retrieve your Microsoft account info. Please try again.", "error")
                return redirect(url_for('user_login'))
            # Try to fetch the Microsoft profile photo (96×96 is the most reliable size)
            ms_pic_bytes = None
            ms_pic_ext = '.jpg'
            try:
                _photo_resp = _requests.get(
                    'https://graph.microsoft.com/v1.0/me/photos/96x96/$value',
                    headers={'Authorization': f"Bearer {token_data['access_token']}"},
                    timeout=8
                )
                if _photo_resp.status_code == 200:
                    ms_pic_bytes = _photo_resp.content
                    ct = _photo_resp.headers.get('Content-Type', 'image/jpeg')
                    ms_pic_ext = '.png' if 'png' in ct else '.jpg'
            except Exception:
                pass
            link_action = session.pop('microsoft_link_action', False)
            if link_action and session.get('user_id'):
                logged_user_id = session['user_id']
                with _db() as c:
                    existing = c.execute(
                        "SELECT id FROM users WHERE microsoft_id=? AND id!=?", (microsoft_id, logged_user_id)
                    ).fetchone()
                    if existing:
                        flash("This Microsoft account is already linked to another account.", "error")
                        return redirect(url_for('dashboard_profile') + '?tab=social')
                    c.execute(
                        "UPDATE users SET microsoft_id=?, microsoft_email=? WHERE id=?",
                        (microsoft_id, email, logged_user_id))
                    c.commit()
                flash("Microsoft account linked successfully!", "success")
                log_event('microsoft_linked', f"Microsoft account linked: {email}", user_id=logged_user_id)
                return redirect(url_for('dashboard_profile') + '?tab=social')
            user = create_or_get_microsoft_user(microsoft_id, email, display_name,
                                                picture_bytes=ms_pic_bytes, picture_ext=ms_pic_ext)
            if not user:
                flash("Could not create account. Please try again.", "error")
                return redirect(url_for('user_login'))
            session['user_id'] = user['id']
            ensure_referral_code(user['id'])
            log_event('user_login', f"Microsoft login: {user['username']} ({email})", user_id=user['id'])
            next_url = session.pop('next', None) or url_for('dashboard')
            return redirect(next_url)
        except Exception as e:
            flash(f"Microsoft login failed: {str(e)}", "error")
            return redirect(url_for('user_login'))

    @app.route("/<lang_code>/account/social/unlink-microsoft", methods=['POST'])
    @app.route('/account/social/unlink-microsoft', methods=['POST'])
    @login_required_user
    def user_unlink_microsoft():
        user = get_current_user()
        with _db() as c:
            row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
        pw_hash = row['password_hash'] if row else ''
        if not pw_hash:
            flash("Cannot unlink Microsoft — it is your only login method. Set a password first.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        provided = request.form.get('current_password', '').strip()
        if not provided or not _check_password(pw_hash, provided):
            flash("Incorrect password. Microsoft account was not disconnected.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        with _db() as c:
            c.execute("UPDATE users SET microsoft_id='', microsoft_email='' WHERE id=?", (user['id'],))
            c.commit()
        flash("Microsoft account disconnected successfully.", "success")
        log_event('microsoft_unlinked', "Microsoft account unlinked", user_id=user['id'])
        return redirect(url_for('dashboard_profile') + '?tab=social')

    @app.route("/<lang_code>/account/social/verify-for-microsoft-link", methods=['POST'])
    @app.route('/account/social/verify-for-microsoft-link', methods=['POST'])
    @login_required_user
    def user_verify_for_microsoft_link():
        user = get_current_user()
        with _db() as c:
            row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
        pw_hash = row['password_hash'] if row else ''
        provided = request.form.get('current_password', '').strip()
        if not provided or (pw_hash and not _check_password(pw_hash, provided)):
            flash("Incorrect password. Please try again.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        session['microsoft_link_action'] = True
        return redirect(url_for('microsoft_login') + '?action=link')

    # ─── Apple Sign In ───────────────────────────────────────────────────────────

    def _apple_client_id():
        return (os.environ.get('APPLE_CLIENT_ID') or
                get_payment_setting('apple_client_id', '') or '')

    def _apple_team_id():
        return (os.environ.get('APPLE_TEAM_ID') or
                get_payment_setting('apple_team_id', '') or '')

    def _apple_key_id():
        return (os.environ.get('APPLE_KEY_ID') or
                get_payment_setting('apple_key_id', '') or '')

    def _apple_private_key():
        return (os.environ.get('APPLE_PRIVATE_KEY') or
                get_payment_setting('apple_private_key', '') or '')

    def _apple_redirect_uri():
        replit_domain = os.environ.get('REPLIT_DEV_DOMAIN')
        base = f'https://{replit_domain}' if replit_domain else request.host_url.rstrip('/')
        return base + '/auth/apple/callback'

    def _generate_apple_client_secret():
        import jwt as _jwt
        import time as _time
        private_key = _apple_private_key().replace('\\n', '\n')
        return _jwt.encode(
            {
                'iss': _apple_team_id(),
                'iat': int(_time.time()),
                'exp': int(_time.time()) + 86400 * 180,
                'aud': 'https://appleid.apple.com',
                'sub': _apple_client_id(),
            },
            private_key,
            algorithm='ES256',
            headers={'kid': _apple_key_id()},
        )

    @app.route("/<lang_code>/auth/apple")
    @app.route('/auth/apple')
    def apple_login():
        client_id = _apple_client_id()
        if not client_id or not _apple_team_id() or not _apple_key_id() or not _apple_private_key():
            flash("Apple login is not configured yet.", "error")
            return redirect(url_for('user_login'))
        if request.args.get('action') == 'link' and session.get('user_id'):
            session['apple_link_action'] = True
        state = _secrets.token_urlsafe(16)
        session['apple_oauth_state'] = state
        params = {
            'client_id': client_id,
            'redirect_uri': _apple_redirect_uri(),
            'response_type': 'code id_token',
            'scope': 'name email',
            'state': state,
            'response_mode': 'form_post',
        }
        return redirect('https://appleid.apple.com/auth/authorize?' + urlencode(params))

    @app.route("/<lang_code>/auth/apple/callback", methods=['GET', 'POST'])
    @app.route('/auth/apple/callback', methods=['GET', 'POST'])
    def apple_callback():
        error = request.form.get('error') or request.args.get('error')
        if error:
            flash(f"Apple login cancelled: {error}", "error")
            return redirect(url_for('user_login'))
        code = request.form.get('code') or request.args.get('code')
        state = request.form.get('state') or request.args.get('state')
        if not code or state != session.pop('apple_oauth_state', None):
            flash("Invalid Apple Sign In response. Please try again.", "error")
            return redirect(url_for('user_login'))
        client_id = _apple_client_id()
        if not client_id or not _apple_team_id() or not _apple_key_id() or not _apple_private_key():
            flash("Apple login is not configured yet.", "error")
            return redirect(url_for('user_login'))
        # Parse user info from Apple's first-login POST (name only sent once)
        user_json_str = request.form.get('user', '{}')
        try:
            user_json = json.loads(user_json_str)
        except Exception:
            user_json = {}
        name_obj = user_json.get('name', {})
        first = name_obj.get('firstName', '')
        last = name_obj.get('lastName', '')
        display_name = f"{first} {last}".strip() or ''
        try:
            client_secret = _generate_apple_client_secret()
            token_resp = _requests.post(
                'https://appleid.apple.com/auth/token',
                data={
                    'code': code,
                    'client_id': client_id,
                    'client_secret': client_secret,
                    'redirect_uri': _apple_redirect_uri(),
                    'grant_type': 'authorization_code',
                }, timeout=10)
            token_data = token_resp.json()
            if 'error' in token_data:
                flash(f"Apple token error: {token_data.get('error_description', token_data['error'])}", "error")
                return redirect(url_for('user_login'))
            import jwt as _jwt
            id_token = token_data.get('id_token', '')
            claims = _jwt.decode(id_token, options={"verify_signature": False})
            apple_id = claims.get('sub')
            email = claims.get('email', '') or user_json.get('email', '')
            if not apple_id:
                flash("Could not retrieve your Apple account info. Please try again.", "error")
                return redirect(url_for('user_login'))
            link_action = session.pop('apple_link_action', False)
            if link_action and session.get('user_id'):
                logged_user_id = session['user_id']
                with _db() as c:
                    existing = c.execute(
                        "SELECT id FROM users WHERE apple_id=? AND id!=?", (apple_id, logged_user_id)
                    ).fetchone()
                    if existing:
                        flash("This Apple account is already linked to another account.", "error")
                        return redirect(url_for('dashboard_profile') + '?tab=social')
                    c.execute(
                        "UPDATE users SET apple_id=?, apple_email=? WHERE id=?",
                        (apple_id, email, logged_user_id))
                    c.commit()
                flash("Apple account linked successfully!", "success")
                log_event('apple_linked', f"Apple account linked: {email or apple_id}", user_id=logged_user_id)
                return redirect(url_for('dashboard_profile') + '?tab=social')
            user = create_or_get_apple_user(apple_id, email, display_name)
            if not user:
                flash("Could not create account. Please try again.", "error")
                return redirect(url_for('user_login'))
            session['user_id'] = user['id']
            ensure_referral_code(user['id'])
            log_event('user_login', f"Apple login: {user['username']}", user_id=user['id'])
            next_url = session.pop('next', None) or url_for('dashboard')
            return redirect(next_url)
        except Exception as e:
            flash(f"Apple login failed: {str(e)}", "error")
            return redirect(url_for('user_login'))

    @app.route("/<lang_code>/account/social/unlink-apple", methods=['POST'])
    @app.route('/account/social/unlink-apple', methods=['POST'])
    @login_required_user
    def user_unlink_apple():
        user = get_current_user()
        with _db() as c:
            row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
        pw_hash = row['password_hash'] if row else ''
        if not pw_hash:
            flash("Cannot unlink Apple — it is your only login method. Set a password first.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        provided = request.form.get('current_password', '').strip()
        if not provided or not _check_password(pw_hash, provided):
            flash("Incorrect password. Apple account was not disconnected.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        with _db() as c:
            c.execute("UPDATE users SET apple_id='', apple_email='' WHERE id=?", (user['id'],))
            c.commit()
        flash("Apple account disconnected successfully.", "success")
        log_event('apple_unlinked', "Apple account unlinked", user_id=user['id'])
        return redirect(url_for('dashboard_profile') + '?tab=social')

    @app.route("/<lang_code>/account/social/verify-for-apple-link", methods=['POST'])
    @app.route('/account/social/verify-for-apple-link', methods=['POST'])
    @login_required_user
    def user_verify_for_apple_link():
        user = get_current_user()
        with _db() as c:
            row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
        pw_hash = row['password_hash'] if row else ''
        provided = request.form.get('current_password', '').strip()
        if not provided or (pw_hash and not _check_password(pw_hash, provided)):
            flash("Incorrect password. Please try again.", "error")
            return redirect(url_for('dashboard_profile') + '?tab=social')
        session['apple_link_action'] = True
        return redirect(url_for('apple_login') + '?action=link')

    # ─── Skip checkout — downgrade to free ───────────────────────────────────────

    @app.route("/<lang_code>/checkout/skip")
    @app.route('/checkout/skip')
    def checkout_skip():
        user = get_current_user()
        if user:
            free_plan = next((p for p in get_all_plans() if p.get('price_monthly', 1) == 0), None)
            free_id = free_plan['id'] if free_plan else 1
            with _db() as c:
                c.execute(
                    "UPDATE users SET plan_id=?, subscription_status='free', stripe_subscription_id='', paypal_subscription_id='', billing_period_months=1, subscription_expires_at=NULL WHERE id=?",
                    (free_id, user['id'])
                )
                c.commit()
            flash("You're on the Free plan. You can upgrade anytime from your dashboard.", "info")
        return redirect(url_for('dashboard'))


    @app.route("/<lang_code>/verify-email/<token>")
    @app.route('/verify-email/<token>')
    def verify_email(token):
        if not token:
            flash("Invalid verification link.", "error")
            return redirect(url_for('dashboard'))
        with _db() as c:
            user = c.execute(
                "SELECT id, email_verified FROM users WHERE email_verification_token=?", (token,)
            ).fetchone()
            if not user:
                flash("Invalid or expired verification link.", "error")
                return redirect(url_for('dashboard'))
            if user['email_verified']:
                flash("Your email is already verified.", "success")
                return redirect(url_for('dashboard'))
            c.execute(
                "UPDATE users SET email_verified=1, email_verification_token='' WHERE id=?",
                (user['id'],)
            )
            c.commit()
        flash("Your email has been verified successfully!", "success")
        return redirect(url_for('dashboard'))


    @app.route("/<lang_code>/resend-verification", methods=['POST'])
    @app.route('/resend-verification', methods=['POST'])
    @login_required_user
    def resend_verification():
        user = get_current_user()
        if user.get('email_verified'):
            flash("Your email is already verified.", "info")
            return redirect(url_for('dashboard'))
        import secrets as _sec
        token = _sec.token_urlsafe(32)
        with _db() as c:
            c.execute("UPDATE users SET email_verification_token=? WHERE id=?", (token, user['id']))
            c.commit()
        try:
            _settings = get_site_settings()
            _site_name = _settings.get('site_name', 'OnlineConvert')
            _verify_url = f"https://{website_url}/verify-email/{token}"
            send_email(user['email'], f"Verify your email – {_site_name}",
                f"""<div style="font-family:Arial,sans-serif;max-width:520px;margin:0 auto;">
                <h2 style="color:#4f46e5;">Verify Your Email Address</h2>
                <p>Hi {user['username']}, click below to verify your email address.</p>
                <p style="margin:24px 0;">
                    <a href="{_verify_url}" style="background:#4f46e5;color:#fff;padding:12px 28px;border-radius:8px;text-decoration:none;font-weight:bold;">Verify Email Address</a>
                </p>
                <p style="color:#6b7280;font-size:12px;">Link expires in 7 days.</p>
                </div>""",
                f"Verify your email: {_verify_url}"
            )
            flash("Verification email sent! Please check your inbox.", "success")
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "ok", "message": "Verification email sent."})
        except Exception as e:
            flash(f"Could not send email: {e}", "error")
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "error", "message": str(e)}), 500
        return redirect(url_for('dashboard'))


    @app.route("/<lang_code>/verify-email-pending", methods=['GET'])
    @app.route('/verify-email-pending', methods=['GET'])
    def verify_email_pending():
        user_id = session.get('unverified_user_id')
        if not user_id:
            return redirect(url_for('user_login'))
        with _db() as c:
            user = c.execute("SELECT id, email, username, email_verified FROM users WHERE id=?", (user_id,)).fetchone()
        if not user:
            session.pop('unverified_user_id', None)
            return redirect(url_for('user_login'))
        if user['email_verified']:
            session.pop('unverified_user_id', None)
            session['user_id'] = user_id
            return redirect(url_for('dashboard'))
        return render_template('auth/verify_email_pending.html', email=user['email'], username=user['username'])

    @app.route("/<lang_code>/resend-verification-pending", methods=['POST'])
    @app.route('/resend-verification-pending', methods=['POST'])
    def resend_verification_pending():
        user_id = session.get('unverified_user_id')
        if not user_id:
            return redirect(url_for('user_login'))
        with _db() as c:
            user = c.execute("SELECT id, email, username FROM users WHERE id=?", (user_id,)).fetchone()
        if not user:
            return redirect(url_for('user_login'))
        import secrets as _sec2
        token = _sec2.token_urlsafe(32)
        with _db() as c:
            c.execute("UPDATE users SET email_verification_token=? WHERE id=?", (token, user_id))
            c.commit()
        try:
            _settings = get_site_settings()
            _site_name = _settings.get('site_name', 'OnlineConvert')
            _verify_url = f"https://{website_url}/verify-email/{token}"
            send_email(user['email'], f"Verify your email – {_site_name}",
                f"""<div style="font-family:Arial,sans-serif;max-width:520px;margin:0 auto;">
                <h2 style="color:#4f46e5;">Verify Your Email Address</h2>
                <p>Hi {user['username']}, click below to verify your email address.</p>
                <p style="margin:24px 0;">
                    <a href="{_verify_url}" style="background:#4f46e5;color:#fff;padding:12px 28px;border-radius:8px;text-decoration:none;font-weight:bold;">Verify Email Address</a>
                </p>
                <p style="color:#6b7280;font-size:12px;">Link expires in 7 days.</p>
                </div>""",
                f"Verify your email: {_verify_url}"
            )
            flash("Verification email sent! Please check your inbox.", "success")
        except Exception as e:
            flash(f"Could not send email: {e}", "error")
        return redirect(url_for('verify_email_pending'))

    @app.route("/<lang_code>/checkout/select/<int:plan_id>", methods=['GET'])
    @app.route('/checkout/select/<int:plan_id>', methods=['GET'])
    def checkout_select(plan_id):
        user = get_current_user()
        plan = get_plan_by_id(plan_id)
        if not plan or plan['price_monthly'] <= 0:
            return redirect(url_for('dashboard'))
        periods = get_plan_periods(plan_id)
        if not periods:
            _seed_billing_periods_for_plan(plan_id)
            periods = get_plan_periods(plan_id)
        period_id = request.args.get('period', type=int)
        selected_period = next((p for p in periods if p['id'] == period_id), None)
        if not selected_period and periods:
            selected_period = periods[0]
        stripe_key = get_payment_setting('stripe_publishable_key', 'STRIPE_PUBLISHABLE_KEY')
        paypal_client = get_payment_setting('paypal_client_id', 'PAYPAL_CLIENT_ID')
        stripe_enabled = get_payment_setting('stripe_enabled', '') != '0'
        paypal_enabled = get_payment_setting('paypal_enabled', '') != '0'
        periods_data = []
        for p in periods:
            pr = calc_period_price(plan['price_monthly'], p['months'], p['discount_percent'])
            has_s = bool(stripe_enabled and stripe_key)
            has_pp = bool(paypal_enabled and paypal_client and (p.get('paypal_plan_id') or plan.get('paypal_plan_id')))
            periods_data.append({
                'id': p['id'], 'months': p['months'], 'label': p['label'],
                'discount_percent': p['discount_percent'],
                'stripe_price_id': p.get('stripe_price_id', ''),
                'paypal_plan_id': p.get('paypal_plan_id', ''),
                'pricing': pr, 'has_stripe': has_s, 'has_paypal': has_pp
            })
        pricing = calc_period_price(
            plan['price_monthly'],
            selected_period['months'],
            selected_period['discount_percent']
        ) if selected_period else None
        has_stripe = bool(stripe_enabled and stripe_key)
        has_paypal = bool(paypal_enabled and paypal_client and (
            (selected_period and selected_period.get('paypal_plan_id')) or plan.get('paypal_plan_id')
        ))
        return render_template('auth/checkout_select.html',
                               plan=plan, user=user, periods=periods,
                               periods_data=periods_data,
                               selected_period=selected_period, pricing=pricing,
                               has_stripe=has_stripe, has_paypal=has_paypal)


    @app.route("/<lang_code>/login", methods=['GET', 'POST'])
    @app.route('/login', methods=['GET', 'POST'])
    def user_login():
        if session.get('user_id'):
            return redirect(url_for('dashboard'))
        
        # Fetch site settings from your database/config
        site_settings = get_site_settings()  # You need to implement this function
        
        if request.method == 'POST':
            login_val = request.form.get('login', '').strip()
            password = request.form.get('password', '')
            user = get_user_by_email(login_val) or get_user_by_username(login_val)
            if user and _check_password(user['password_hash'], password):
                if not user.get('email_verified'):
                    session['unverified_user_id'] = user['id']
                    return redirect(url_for('verify_email_pending'))
                if user.get('user_totp_enabled'):
                    session['pending_2fa_user_id'] = user['id']
                    session['pending_2fa_next'] = request.form.get('next') or url_for('dashboard')
                    return redirect(url_for('user_verify_2fa'))
                session['user_id'] = user['id']
                log_event('user_login', f"User logged in: {user.get('username','')}", user_id=user['id'], username=user.get('username',''))
                _ip = request.headers.get('X-Forwarded-For', request.remote_addr or '').split(',')[0].strip()
                _ua = request.headers.get('User-Agent', '')
                import secrets as _lsec
                _login_token = _lsec.token_hex(24)
                session['_sec_token'] = _login_token
                _login_device = 'mobile' if 'Mobile' in _ua else ('tablet' if 'Tablet' in _ua or 'iPad' in _ua else 'desktop')
                with _db() as _sec_c:
                    _sec_c.execute("UPDATE user_sessions SET is_current=0 WHERE user_id=?", (user['id'],))
                    _sec_c.execute(
                        "INSERT INTO user_sessions (user_id, session_token, ip_address, user_agent, device_type, is_current) VALUES (?,?,?,?,?,1)",
                        (user['id'], _login_token, _ip, _ua[:512], _login_device)
                    )
                    _sec_c.execute(
                        "INSERT INTO security_events (user_id, event_type, description, ip_address, user_agent) VALUES (?,?,?,?,?)",
                        (user['id'], 'login', f"Logged in from {_ip}", _ip, _ua[:512])
                    )
                    _sec_c.commit()
                next_url = request.form.get('next') or url_for('dashboard')
                return redirect(next_url)
            return render_template('auth/login.html',
                                error="Invalid credentials. Please try again.",
                                login_val=login_val,
                                site_settings=site_settings)  # Added here
        return render_template('auth/login.html', site_settings=site_settings)  # Added here


    @app.route("/<lang_code>/account/2fa/verify", methods=['GET', 'POST'])
    @app.route('/account/2fa/verify', methods=['GET', 'POST'])
    def user_verify_2fa():
        pending_id = session.get('pending_2fa_user_id')
        if not pending_id:
            return redirect(url_for('user_login'))
        if request.method == 'POST':
            code = request.form.get('code', '').strip().replace(' ', '')
            with _db() as c:
                row = c.execute("SELECT user_totp_secret FROM users WHERE id=?", (pending_id,)).fetchone()
            if row and row['user_totp_secret']:
                import pyotp as _pyotp
                totp = _pyotp.TOTP(row['user_totp_secret'])
                if totp.verify(code, valid_window=1):
                    session.pop('pending_2fa_user_id', None)
                    next_url = session.pop('pending_2fa_next', url_for('dashboard'))
                    session['user_id'] = pending_id
                    _ip2 = request.headers.get('X-Forwarded-For', request.remote_addr or '').split(',')[0].strip()
                    _ua2 = request.headers.get('User-Agent', '')
                    import secrets as _2fa_sec
                    _2fa_token = _2fa_sec.token_hex(24)
                    session['_sec_token'] = _2fa_token
                    _2fa_device = 'mobile' if 'Mobile' in _ua2 else ('tablet' if 'Tablet' in _ua2 or 'iPad' in _ua2 else 'desktop')
                    with _db() as _2fa_c:
                        _2fa_c.execute("UPDATE user_sessions SET is_current=0 WHERE user_id=?", (pending_id,))
                        _2fa_c.execute(
                            "INSERT INTO user_sessions (user_id, session_token, ip_address, user_agent, device_type, is_current) VALUES (?,?,?,?,?,1)",
                            (pending_id, _2fa_token, _ip2, _ua2[:512], _2fa_device)
                        )
                        _2fa_c.execute(
                            "INSERT INTO security_events (user_id, event_type, description, ip_address, user_agent) VALUES (?,?,?,?,?)",
                            (pending_id, '2fa_login', f"Logged in via 2FA from {_ip2}", _ip2, _ua2[:512])
                        )
                        _2fa_c.commit()
                    return redirect(next_url)
            return render_template('account/verify_2fa.html', error="Invalid code. Please try again.")
        return render_template('account/verify_2fa.html')


    @app.route("/<lang_code>/account/2fa", methods=['GET'])
    @app.route('/account/2fa', methods=['GET'])
    @login_required_user
    def user_2fa_settings():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        is_paid = user_has_paid_plan(user)
        qr_data_url = None
        if not user.get('user_totp_enabled') and not user.get('user_totp_secret'):
            import pyotp as _pyotp
            secret = _pyotp.random_base32()
            with _db() as c:
                c.execute("UPDATE users SET user_totp_secret=? WHERE id=?", (secret, user['id']))
                c.commit()
            user['user_totp_secret'] = secret
        if user.get('user_totp_secret') and not user.get('user_totp_enabled'):
            import pyotp as _pyotp, qrcode as _qr, io, base64
            uri = _pyotp.totp.TOTP(user['user_totp_secret']).provisioning_uri(
                name=user['email'], issuer_name='OnlineConvert'
            )
            qr = _qr.QRCode(version=1, box_size=6, border=2)
            qr.add_data(uri)
            qr.make(fit=True)
            img = qr.make_image(fill_color='black', back_color='white')
            buf = io.BytesIO()
            img.save(buf, format='PNG')
            qr_data_url = 'data:image/png;base64,' + base64.b64encode(buf.getvalue()).decode()
        return render_template('account/2fa.html', user=user, plan=plan,
                               is_paid=is_paid, qr_data_url=qr_data_url)


    @app.route("/<lang_code>/account/2fa/enable", methods=['POST'])
    @app.route('/account/2fa/enable', methods=['POST'])
    @login_required_user
    def user_2fa_enable():
        user = get_current_user()
        code = request.form.get('code', '').strip().replace(' ', '')
        with _db() as c:
            row = c.execute("SELECT user_totp_secret FROM users WHERE id=?", (user['id'],)).fetchone()
        secret = row['user_totp_secret'] if row else ''
        if not secret:
            flash("No 2FA secret found. Please refresh and try again.", "error")
            return redirect(url_for('user_2fa_settings'))
        import pyotp as _pyotp
        totp = _pyotp.TOTP(secret)
        if totp.verify(code, valid_window=1):
            with _db() as c:
                c.execute("UPDATE users SET user_totp_enabled=1 WHERE id=?", (user['id'],))
                c.commit()
            flash("Two-factor authentication has been enabled successfully.", "success")
        else:
            flash("Invalid code. Please try again.", "error")
        return redirect(url_for('user_2fa_settings'))


    @app.route("/<lang_code>/account/2fa/disable", methods=['POST'])
    @app.route('/account/2fa/disable', methods=['POST'])
    @login_required_user
    def user_2fa_disable():
        user = get_current_user()
        code = request.form.get('code', '').strip().replace(' ', '')
        with _db() as c:
            row = c.execute("SELECT user_totp_secret FROM users WHERE id=?", (user['id'],)).fetchone()
        secret = row['user_totp_secret'] if row else ''
        import pyotp as _pyotp
        totp = _pyotp.TOTP(secret)
        if totp.verify(code, valid_window=1):
            with _db() as c:
                c.execute("UPDATE users SET user_totp_enabled=0, user_totp_secret='' WHERE id=?", (user['id'],))
                c.commit()
            flash("Two-factor authentication has been disabled.", "success")
        else:
            flash("Invalid code. 2FA not disabled.", "error")
        return redirect(url_for('user_2fa_settings'))


    @app.route("/<lang_code>/account/change-password", methods=['GET', 'POST'])
    @app.route('/account/change-password', methods=['GET', 'POST'])
    @login_required_user
    def user_change_password():
        user = get_current_user()
        if request.method == 'POST':
            current = request.form.get('current_password', '')
            new_pw = request.form.get('new_password', '')
            confirm = request.form.get('confirm_password', '')
            if not _check_password(user['password_hash'], current):
                return render_template('account/change_password.html', user=user,
                                       error="Current password is incorrect.")
            if len(new_pw) < 6:
                return render_template('account/change_password.html', user=user,
                                       error="New password must be at least 6 characters.")
            if new_pw != confirm:
                return render_template('account/change_password.html', user=user,
                                       error="Passwords do not match.")
            new_hash = generate_password_hash(new_pw)
            with _db() as c:
                c.execute("UPDATE users SET password_hash=? WHERE id=?", (new_hash, user['id']))
                c.commit()
            flash("Password changed successfully.", "success")
            return redirect(url_for('dashboard'))
        return render_template('account/change_password.html', user=user)


    @app.route("/<lang_code>/forgot-password", methods=['GET', 'POST'])
    @app.route('/forgot-password', methods=['GET', 'POST'])
    def user_forgot_password():
        if request.method == 'POST':
            email = request.form.get('email', '').strip().lower()
            user = get_user_by_email(email)
            email_sent = False
            smtp_error = None
            if user:
                import secrets as _sec, datetime as _dt
                token = _sec.token_urlsafe(32)
                expires = (_dt.datetime.utcnow() + _dt.timedelta(hours=2)).isoformat()
                with _db() as c:
                    c.execute("INSERT OR REPLACE INTO password_reset_tokens (user_id, token, expires_at) VALUES (?,?,?)",
                              (user['id'], token, expires))
                    c.commit()
                reset_link = request.host_url.rstrip('/') + f'/reset-password/{token}'
                settings = get_site_settings()
                site_name = settings.get('site_name', 'OnlineConvert')
                html_body = f"""
<!DOCTYPE html>
<html>
<body style="font-family:Arial,sans-serif;background:#f4f4f4;margin:0;padding:0;">
  <div style="max-width:520px;margin:40px auto;background:#fff;border-radius:12px;overflow:hidden;box-shadow:0 2px 12px rgba(0,0,0,0.08);">
    <div style="background:linear-gradient(135deg,#6366f1,#8b5cf6);padding:32px 40px 24px;text-align:center;">
      <h1 style="color:#fff;margin:0;font-size:24px;font-weight:700;">{site_name}</h1>
    </div>
    <div style="padding:36px 40px;">
      <h2 style="margin:0 0 8px;color:#111827;font-size:20px;">Reset Your Password</h2>
      <p style="color:#6b7280;margin:0 0 24px;line-height:1.6;">
        Hi <strong>{user['username']}</strong>, we received a request to reset your password.
        Click the button below to choose a new one. This link expires in <strong>2 hours</strong>.
      </p>
      <div style="text-align:center;margin:28px 0;">
        <a href="{reset_link}" style="display:inline-block;background:#6366f1;color:#fff;text-decoration:none;padding:14px 36px;border-radius:10px;font-size:15px;font-weight:700;">
          Reset My Password
        </a>
      </div>
      <p style="color:#9ca3af;font-size:13px;margin:24px 0 0;line-height:1.6;">
        If you didn't request this, you can safely ignore this email — your account will not be affected.<br><br>
        Or copy this link into your browser:<br>
        <a href="{reset_link}" style="color:#6366f1;word-break:break-all;">{reset_link}</a>
      </p>
    </div>
    <div style="background:#f9fafb;padding:16px 40px;border-top:1px solid #e5e7eb;text-align:center;">
      <p style="color:#9ca3af;font-size:12px;margin:0;">&copy; {site_name}. All rights reserved.</p>
    </div>
  </div>
</body>
</html>"""
                text_body = f"Hi {user['username']},\n\nReset your password here (valid 2 hours):\n{reset_link}\n\nIf you didn't request this, ignore this email."
                ok, err = send_email(email, f"Reset your {site_name} password", html_body, text_body)
                if ok:
                    email_sent = True
                else:
                    smtp_error = err
            return render_template('account/forgot_password.html',
                                   submitted=True, email=email,
                                   email_sent=email_sent, smtp_error=smtp_error,
                                   user_found=bool(user))
        return render_template('account/forgot_password.html', submitted=False)


    @app.route("/<lang_code>/reset-password/<token>", methods=['GET', 'POST'])
    @app.route('/reset-password/<token>', methods=['GET', 'POST'])
    def user_reset_password(token):
        import datetime as _dt
        with _db() as c:
            row = c.execute(
                "SELECT * FROM password_reset_tokens WHERE token=? AND used=0", (token,)
            ).fetchone()
        if not row:
            flash("This reset link is invalid or has already been used.", "error")
            return redirect(url_for('user_forgot_password'))
        tok = dict(row)
        try:
            if _dt.datetime.utcnow() > _dt.datetime.fromisoformat(tok['expires_at']):
                flash("This reset link has expired. Please request a new one.", "error")
                return redirect(url_for('user_forgot_password'))
        except Exception:
            pass
        if request.method == 'POST':
            new_pw = request.form.get('new_password', '')
            confirm = request.form.get('confirm_password', '')
            _pw_errs = _validate_password_strength(new_pw)
            if _pw_errs:
                return render_template('account/reset_password.html', token=token, error=" ".join(_pw_errs))
            if new_pw != confirm:
                return render_template('account/reset_password.html', token=token, error="Passwords do not match.")
            new_hash = generate_password_hash(new_pw)
            with _db() as c:
                c.execute("UPDATE users SET password_hash=? WHERE id=?", (new_hash, tok['user_id']))
                c.execute("UPDATE password_reset_tokens SET used=1 WHERE id=?", (tok['id'],))
                c.commit()
            flash("Your password has been reset. Please sign in.", "success")
            return redirect(url_for('user_login'))
        return render_template('account/reset_password.html', token=token)


    @app.route("/<lang_code>/logout")
    @app.route('/logout')
    def user_logout():
        session.pop('user_id', None)
        flash("You have been logged out.", "info")
        return redirect(url_for('user_login'))


    @app.route("/<lang_code>/dashboard")
    @app.route('/dashboard')
    @login_required_user
    def dashboard():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        page = request.args.get('page', 1, type=int)
        conversions, total = get_user_conversions(user['id'], page=page)
        per_page = 20
        total_pages = max(1, (total + per_page - 1) // per_page)
        ref_code = ensure_referral_code(user['id'])
        ref_url = request.host_url.rstrip('/') + '/register?ref=' + ref_code
        referrals = get_user_referrals(user['id'])
        rewards = get_user_rewards(user['id'])
        granted_days = sum(r['reward_days'] for r in rewards if r['status'] == 'granted')
        pending_rewards = sum(1 for r in rewards if r['status'] == 'pending')
        referral_count = len(referrals)
        with _db() as c:
            blog_count = c.execute("SELECT COUNT(*) FROM blogs WHERE author_user_id=?", (user['id'],)).fetchone()[0]
        today_usage = get_today_usage(user_id=user['id'])
        plan_limits = get_plan_tool_limits(user.get('plan_id') or 1)
        return render_template('dashboard/index.html',
                               user=user, plan=plan,
                               conversions=conversions, total=total,
                               page=page, total_pages=total_pages,
                               plans=get_all_plans(),
                               ref_code=ref_code, ref_url=ref_url,
                               referrals=referrals, rewards=rewards,
                               granted_days=granted_days, pending_rewards=pending_rewards,
                               referral_count=referral_count, blog_count=blog_count,
                               today_usage=today_usage, plan_limits=plan_limits)


    @app.route("/<lang_code>/dashboard/profile", methods=['GET'])
    @app.route('/dashboard/profile', methods=['GET'])
    @login_required_user
    def dashboard_profile():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        prefs = get_user_notif_prefs(user['id'])
        return render_template('dashboard/profile.html',
                               user=user, plan=plan,
                               prefs=prefs, active_page='profile')


    @app.route("/<lang_code>/dashboard/profile/save", methods=['POST'])
    @app.route('/dashboard/profile/save', methods=['POST'])
    @login_required_user
    def dashboard_profile_save():
        try:
            return _dashboard_profile_save_inner()
        except Exception as _exc:
            import traceback as _tb
            return jsonify({'ok': False, 'errors': [str(_exc)], 'trace': _tb.format_exc()}), 500


    def _dashboard_profile_save_inner():
        user = get_current_user()
        action = request.form.get('action', 'profile')

        if action == 'profile':
            new_username = request.form.get('username', '').strip()
            new_email = request.form.get('email', '').strip().lower()
            display_name = request.form.get('display_name', '').strip()
            bio = request.form.get('bio', '').strip()
            country = request.form.get('country', '').strip()
            errors = []
            if not new_username or len(new_username) < 3:
                errors.append("Username must be at least 3 characters.")
            if not new_email or '@' not in new_email:
                errors.append("A valid email address is required.")
            if not errors:
                with _db() as c:
                    if c.execute("SELECT id FROM users WHERE username=? AND id!=?", (new_username, user['id'])).fetchone():
                        errors.append("That username is already taken.")
                    if c.execute("SELECT id FROM users WHERE email=? AND id!=?", (new_email, user['id'])).fetchone():
                        errors.append("That email is already registered to another account.")
            if errors:
                return jsonify({'ok': False, 'errors': errors})
            pic_ref = user.get('profile_picture', '') or ''
            pic_file = request.files.get('profile_picture')
            new_avatar_url = None
            # Normalise stored value: may be full path "/static/avatars/f.jpg" or bare "f.jpg"
            pic_filename = pic_ref  # keep original ref for DB; updated below if new file uploaded
            if pic_file and pic_file.filename:
                import uuid as _uuid
                ext = os.path.splitext(pic_file.filename)[1].lower()
                if ext in ('.jpg', '.jpeg', '.png', '.gif', '.webp'):
                    pic_dir = os.path.join(dir_path, 'static', 'avatars')
                    os.makedirs(pic_dir, exist_ok=True)
                    # Delete old avatar – handle both full-URL and bare filename formats
                    if pic_ref:
                        old_basename = os.path.basename(pic_ref)
                        old_path = os.path.join(pic_dir, old_basename)
                        try:
                            if os.path.isfile(old_path):
                                os.remove(old_path)
                        except OSError:
                            pass
                    new_fname = f"avatar_{user['id']}_{_uuid.uuid4().hex[:8]}{ext}"
                    pic_file.save(os.path.join(pic_dir, new_fname))
                    # Always store as full URL path for consistency across all upload methods
                    pic_filename = f"/static/avatars/{new_fname}"
                    new_avatar_url = pic_filename
            email_changed = new_email != user['email']
            with _db() as c:
                if email_changed:
                    import secrets as _sec2
                    new_token = _sec2.token_urlsafe(32)
                    c.execute(
                        "UPDATE users SET username=?, email=?, display_name=?, bio=?, country=?, profile_picture=?, email_verified=0, email_verification_token=? WHERE id=?",
                        (new_username, new_email, display_name, bio, country, pic_filename, new_token, user['id'])
                    )
                    c.commit()
                    try:
                        _st = get_site_settings()
                        _vurl = f"https://{ALLOWED_DOMAIN}/verify-email/{new_token}"
                        send_email(new_email, f"Verify your new email – {_st.get('site_name','OnlineConvert')}",
                            f'<p>Click <a href="{_vurl}">here</a> to verify your new email address.</p>',
                            f"Verify your email: {_vurl}"
                        )
                    except Exception:
                        pass
                    msg = "Profile updated. A verification email has been sent to your new address."
                else:
                    c.execute(
                        "UPDATE users SET username=?, display_name=?, bio=?, country=?, profile_picture=? WHERE id=?",
                        (new_username, display_name, bio, country, pic_filename, user['id'])
                    )
                    c.commit()
                    msg = "Profile updated successfully."
            session['username'] = new_username
            return jsonify({'ok': True, 'msg': msg, 'avatar_url': new_avatar_url})

        elif action == 'password':
            current_password = request.form.get('current_password', '').strip()
            new_password = request.form.get('new_password', '').strip()
            confirm_password = request.form.get('confirm_password', '').strip()
            errors = []
            if not current_password:
                errors.append("Current password is required.")
            if not new_password or len(new_password) < 8:
                errors.append("New password must be at least 8 characters.")
            if new_password != confirm_password:
                errors.append("New passwords do not match.")
            if not errors:
                with _db() as c:
                    row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
                    if not check_password_hash(row['password_hash'], current_password):
                        errors.append("Current password is incorrect.")
            if errors:
                return jsonify({'ok': False, 'errors': errors})
            with _db() as c:
                c.execute("UPDATE users SET password_hash=? WHERE id=?",
                          (generate_password_hash(new_password), user['id']))
                c.commit()
            return jsonify({'ok': True, 'msg': "Password changed successfully."})

        elif action == 'notifications':
            _NOTIF_EVENTS = ['login', 'file_uploaded', 'blog_submitted', 'blog_reviewed',
                             'blog_published', 'plan_changed', 'payment_received', 'referral_signup', 'password_changed']
            updates = {ev: (1 if request.form.get(f'notif_{ev}') else 0) for ev in _NOTIF_EVENTS}
            with _db() as c:
                existing = c.execute("SELECT 1 FROM user_notification_prefs WHERE user_id=?", (user['id'],)).fetchone()
                cols = ', '.join(updates.keys())
                placeholders = ', '.join(['?'] * len(updates))
                if existing:
                    set_clause = ', '.join([f"{k}=?" for k in updates.keys()])
                    c.execute(f"UPDATE user_notification_prefs SET {set_clause} WHERE user_id=?",
                              list(updates.values()) + [user['id']])
                else:
                    c.execute(f"INSERT INTO user_notification_prefs (user_id, {cols}) VALUES (?, {placeholders})",
                              [user['id']] + list(updates.values()))
                c.commit()
            return jsonify({'ok': True, 'msg': "Notification preferences saved."})

        return jsonify({'ok': False, 'errors': ['Unknown action.']})


    @app.route("/<lang_code>/dashboard/conversion/<int:conv_id>")
    @app.route('/dashboard/conversion/<int:conv_id>')
    @login_required_user
    def dashboard_conversion(conv_id):
        user = get_current_user()
        with _db() as c:
            row = c.execute(
                "SELECT * FROM conversions WHERE id=? AND user_id=?", (conv_id, user['id'])
            ).fetchone()
        if not row:
            abort(404)
        conv = dict(row)
        return render_template('dashboard/conversion.html', conv=conv, user=user)


    @app.route("/<lang_code>/dashboard/download/<int:conv_id>")
    @app.route('/dashboard/download/<int:conv_id>')
    @login_required_user
    def dashboard_download(conv_id):
        user = get_current_user()
        with _db() as c:
            row = c.execute(
                "SELECT * FROM conversions WHERE id=? AND user_id=?", (conv_id, user['id'])
            ).fetchone()
        if not row:
            abort(404)
        conv = dict(row)
        output_path = conv.get('output_path', '')
        if not output_path:
            job_id = conv.get('job_id', '')
            if job_id:
                return redirect(f"/output/{job_id}")
            flash("Converted file is no longer available.", "error")
            return redirect(url_for('dashboard_files'))
        full_path = os.path.join(app.config['UPLOAD_DIR'], output_path)
        if not os.path.exists(full_path):
            full_path = output_path
        if not os.path.exists(full_path):
            job_id = conv.get('job_id', '')
            if job_id:
                return redirect(f"/output/{job_id}")
            flash("Converted file is no longer available on disk.", "error")
            return redirect(url_for('dashboard_files'))
        fname = conv.get('file_name') or os.path.basename(output_path)
        out_fmt = conv.get('output_format', '')
        if out_fmt and fname and not fname.lower().endswith('.' + out_fmt.lower()):
            base = os.path.splitext(fname)[0]
            fname = f"{base}.{out_fmt.lower()}"
        return send_file(full_path, as_attachment=True, download_name=fname)


    @app.route("/<lang_code>/dashboard/download-original/<int:conv_id>")
    @app.route('/dashboard/download-original/<int:conv_id>')
    @login_required_user
    def dashboard_download_original(conv_id):
        user = get_current_user()
        with _db() as c:
            row = c.execute(
                """SELECT fu.file_path, fu.file_name FROM conversions conv
                   JOIN file_uploads fu ON fu.id = conv.upload_id
                   WHERE conv.id=? AND conv.user_id=?""",
                (conv_id, user['id'])
            ).fetchone()
        if not row:
            abort(404)
        upload_dir = app.config.get('UPLOAD_DIR', 'storage/uploads')
        full_path = os.path.join(upload_dir, row['file_path'] or '')
        if not os.path.exists(full_path):
            full_path = row['file_path'] or ''
        if not os.path.exists(full_path):
            flash("Original file not available.", "error")
            return redirect(url_for('dashboard_files'))
        return send_file(full_path, as_attachment=True, download_name=row['file_name'] or os.path.basename(full_path))


    @app.route("/<lang_code>/dashboard/inbox")
    @app.route('/dashboard/inbox')
    @login_required_user
    def dashboard_inbox():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        page = request.args.get('page', 1, type=int)
        per_page = 20
        offset = (page - 1) * per_page
        with _db() as c:
            total = c.execute("SELECT COUNT(*) FROM user_inbox WHERE user_id=?", (user['id'],)).fetchone()[0]
            msgs = c.execute(
                "SELECT * FROM user_inbox WHERE user_id=? ORDER BY id DESC LIMIT ? OFFSET ?",
                (user['id'], per_page, offset)
            ).fetchall()
            c.execute("UPDATE user_inbox SET is_read=1 WHERE user_id=? AND is_read=0", (user['id'],))
            c.commit()
        msgs = [dict(m) for m in msgs]
        total_pages = max(1, (total + per_page - 1) // per_page)
        return render_template('dashboard/inbox.html', user=user, plan=plan,
                               msgs=msgs, total=total, page=page, total_pages=total_pages,
                               active_page='inbox')


    @app.route("/<lang_code>/pricing")
    @app.route('/pricing')
    def pricing_page():
        plans = get_all_plans()
        user = get_current_user()
        plan_periods_map = {}
        plans_js = []
        yearly_disc_pct = 0

        for pl in plans:
            periods = get_plan_periods(pl['id'])
            if not periods:
                _seed_billing_periods_for_plan(pl['id'])
                periods = get_plan_periods(pl['id'])
            plan_periods_map[pl['id']] = [dict(p) for p in periods]

            monthly_price = float(pl.get('price_monthly') or 0)
            period_objs = []
            for p in plan_periods_map[pl['id']]:
                months = int(p.get('months') or 1)
                disc   = float(p.get('discount_percent') or 0)
                total  = round(monthly_price * months * (1 - disc / 100), 2)
                per_mo = round(total / months, 2) if months > 0 else monthly_price
                period_objs.append({
                    'months': months,
                    'label': p.get('label') or '',
                    'discount_percent': int(disc),
                    'per_month': per_mo,
                    'total': total,
                })
                if months == 12 and int(disc) > yearly_disc_pct:
                    yearly_disc_pct = int(disc)

            if not period_objs:
                period_objs.append({
                    'months': 1, 'label': 'Monthly',
                    'discount_percent': 0,
                    'per_month': monthly_price, 'total': monthly_price,
                })

            yearly_p = next((p for p in period_objs if p['months'] == 12), None)
            pl['_yearly_per_mo'] = yearly_p['per_month'] if yearly_p else monthly_price

            plans_js.append({
                'id': pl['id'],
                'name': pl.get('name') or '',
                'price_monthly': monthly_price,
                'periods': period_objs,
            })

        return render_template(
            'pricing.html',
            plans=plans,
            current_user=user,
            plan_periods_map=plan_periods_map,
            plans_js=plans_js,
            yearly_disc_pct=yearly_disc_pct,
        )


    @app.route("/<lang_code>/subscribe")
    @app.route('/subscribe')
    def subscribe_page():
        plans = [p for p in get_all_plans() if p['price_monthly'] > 0]
        user = get_current_user()
        plan_periods_map = {}
        for pl in plans:
            periods = get_plan_periods(pl['id'])
            if not periods:
                _seed_billing_periods_for_plan(pl['id'])
                periods = get_plan_periods(pl['id'])
            plan_periods_map[pl['id']] = [dict(p) for p in periods]
        return render_template('auth/subscribe.html', plans=plans, user=user,
                               plan_periods_map=plan_periods_map)


    @app.route("/<lang_code>/subscribe/stripe/<int:plan_id>", methods=['POST'])
    @app.route('/subscribe/stripe/<int:plan_id>', methods=['POST'])
    def stripe_checkout(plan_id):
        stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
        if not stripe_secret:
            flash("Stripe payments are not configured yet.", "error")
            return redirect(url_for('subscribe_page'))
        try:
            import stripe
            stripe.api_key = stripe_secret
            plan = get_plan_by_id(plan_id)
            if not plan:
                flash("Plan not found.", "error")
                return redirect(url_for('subscribe_page'))
            period_id = request.form.get('period_id', type=int)
            period = get_period_by_id(period_id) if period_id else None
            stripe_price_id = (period.get('stripe_price_id') if period else None) or plan.get('stripe_price_id', '')

            current_mode = 'test' if stripe_secret.startswith('sk_test_') else 'live'
            stored_mode = plan.get('stripe_key_mode', '')
            if stripe_price_id and stored_mode != current_mode:
                stripe_price_id = ''
                with _db() as _c:
                    _c.execute("UPDATE plans SET stripe_price_id='', stripe_product_id='', stripe_key_mode='' WHERE id=?", (plan_id,))
                    _c.execute("UPDATE plan_billing_periods SET stripe_price_id='' WHERE plan_id=?", (plan_id,))
                    _c.commit()
                plan = get_plan_by_id(plan_id)

            # Auto-create Stripe Product + Price if not configured yet
            if not stripe_price_id:
                months = period['months'] if period else 1
                discount = float(period.get('discount_percent', 0)) if period else 0
                base_monthly = float(plan['price_monthly'])
                if months > 1 and discount > 0:
                    monthly_after_discount = base_monthly * (1 - discount / 100)
                else:
                    monthly_after_discount = base_monthly
                total_amount = monthly_after_discount * months
                unit_amount = max(1, round(total_amount * 100))

                stripe_product_id = plan.get('stripe_product_id', '')
                if not stripe_product_id:
                    _stripe_settings = get_site_settings()
                    _stripe_site_name = (_stripe_settings.get('logo_text') or _stripe_settings.get('site_name') or 'Premium').strip()
                    product = stripe.Product.create(
                        name=plan['name'],
                        description=f"{_stripe_site_name} {plan['name']} plan"
                    )
                    stripe_product_id = product['id']
                    with _db() as _c:
                        _c.execute("UPDATE plans SET stripe_product_id=?, stripe_key_mode=? WHERE id=?", (stripe_product_id, current_mode, plan_id))
                        _c.commit()

                interval = 'month' if months == 1 else 'year' if months == 12 else 'month'
                interval_count = 1 if months in (1, 12) else months
                price = stripe.Price.create(
                    product=stripe_product_id,
                    unit_amount=unit_amount,
                    currency='usd',
                    recurring={'interval': interval, 'interval_count': interval_count},
                    nickname=f"{plan['name']} – {period['label'] if period else 'Monthly'}"
                )
                stripe_price_id = price['id']
                with _db() as _c:
                    if period_id:
                        _c.execute("UPDATE plan_billing_periods SET stripe_price_id=? WHERE id=?",
                                   (stripe_price_id, period_id))
                    else:
                        _c.execute("UPDATE plans SET stripe_price_id=? WHERE id=?",
                                   (stripe_price_id, plan_id))
                    _c.execute("UPDATE plans SET stripe_key_mode=? WHERE id=?", (current_mode, plan_id))
                    _c.commit()
            user = get_current_user()
            customer_kwargs = {}
            if user and user.get('stripe_customer_id'):
                customer_kwargs['customer'] = user['stripe_customer_id']
            elif user:
                customer_kwargs['customer_email'] = user['email']
            months = period['months'] if period else 1
            metadata = {'plan_id': plan_id, 'period_months': months, 'period_id': period_id or ''}
            _co_settings = get_site_settings()
            _co_site_name = (_co_settings.get('logo_text') or _co_settings.get('site_name') or request.host).strip()
            checkout = stripe.checkout.Session.create(
                mode='subscription',
                line_items=[{'price': stripe_price_id, 'quantity': 1}],
                success_url=request.host_url + 'dashboard?stripe=success',
                cancel_url=request.host_url + f'checkout/select/{plan_id}',
                metadata=metadata,
                subscription_data={
                    'metadata': metadata,
                    'description': f"{_co_site_name} – {plan['name']} subscription",
                },
                custom_text={
                    'submit': {'message': f"You're subscribing to {_co_site_name} {plan['name']} plan. You can cancel anytime from your dashboard."}
                },
                **customer_kwargs
            )
            return redirect(checkout.url, code=303)
        except Exception as e:
            flash(f"Stripe error: {str(e)}", "error")
            return redirect(url_for('subscribe_page'))


    @app.route("/<lang_code>/webhook/stripe", methods=['POST'])
    @app.route('/webhook/stripe', methods=['POST'])
    def stripe_webhook():
        stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
        webhook_secret = get_payment_setting('stripe_webhook_secret', 'STRIPE_WEBHOOK_SECRET')
        if not stripe_secret:
            return jsonify(error="Not configured"), 400
        try:
            import stripe
            stripe.api_key = stripe_secret
            payload = request.get_data()
            sig_header = request.headers.get('Stripe-Signature', '')
            if webhook_secret:
                event = stripe.Webhook.construct_event(payload, sig_header, webhook_secret)
            else:
                event = stripe.Event.construct_from(
                    json.loads(payload), stripe.api_key
                )
            import datetime as _dt

            def _resolve_plan_from_stripe_price(price_id):
                if not price_id:
                    return None
                with _db() as cc:
                    row = cc.execute("SELECT * FROM plans WHERE stripe_price_id=?", (price_id,)).fetchone()
                    if row:
                        return dict(row)
                    row2 = cc.execute(
                        "SELECT p.* FROM plan_billing_periods bp JOIN plans p ON p.id=bp.plan_id WHERE bp.stripe_price_id=?",
                        (price_id,)
                    ).fetchone()
                    return dict(row2) if row2 else None

            def _resolve_period_months_from_price(price_id):
                if not price_id:
                    return 1
                with _db() as cc:
                    row = cc.execute("SELECT months FROM plan_billing_periods WHERE stripe_price_id=?", (price_id,)).fetchone()
                    return row['months'] if row else 1

            if event['type'] == 'checkout.session.completed':
                sess_obj = event['data']['object']
                customer_id = sess_obj.get('customer')
                sub_id = sess_obj.get('subscription')
                meta = sess_obj.get('metadata') or {}
                email = sess_obj.get('customer_email') or (sess_obj.get('customer_details') or {}).get('email')
                if meta.get('type') == 'blog_payment' and meta.get('blog_id'):
                    blog_id_pay = int(meta['blog_id'])
                    user_id_pay = int(meta.get('user_id', 0))
                    with _db() as c:
                        c.execute(
                            "UPDATE blogs SET status='published', payment_status='paid', updated_at=datetime('now') WHERE rowid=? AND author_user_id=?",
                            (blog_id_pay, user_id_pay)
                        )
                        c.commit()
                    return jsonify(received=True)
                if meta.get('type') == 'team_plan' and meta.get('team_id') and meta.get('plan_id'):
                    team_id_pay = int(meta['team_id'])
                    plan_id_pay = int(meta['plan_id'])
                    with _db() as c:
                        c.execute(
                            "UPDATE teams SET plan_id=?, stripe_subscription_id=? WHERE id=?",
                            (plan_id_pay, sub_id, team_id_pay)
                        )
                        c.commit()
                    return jsonify(received=True)
                user = get_user_by_email(email) if email else None
                if user:
                    sub = stripe.Subscription.retrieve(sub_id) if sub_id else None
                    price_id = None
                    if sub and sub.get('items', {}).get('data'):
                        price_id = sub['items']['data'][0]['price']['id']
                    db_plan = _resolve_plan_from_stripe_price(price_id)
                    period_months = int(meta.get('period_months') or _resolve_period_months_from_price(price_id) or 1)
                    plan_id_to_set = db_plan['id'] if db_plan else int(meta.get('plan_id') or user['plan_id'])
                    expires_at = None
                    if sub and sub.get('current_period_end'):
                        expires_at = _dt.datetime.utcfromtimestamp(sub['current_period_end']).isoformat()
                    with _db() as c:
                        c.execute("""
                            UPDATE users SET stripe_customer_id=?, stripe_subscription_id=?,
                            subscription_status='active', plan_id=?, subscription_expires_at=?,
                            billing_period_months=?, auto_renew=1
                            WHERE id=?
                        """, (customer_id, sub_id, plan_id_to_set, expires_at, period_months, user['id']))
                        c.commit()
                    referred_user = get_current_user() or user
                    referrer_id = user.get('referred_by_user_id')
                    if referrer_id:
                        plan_obj = get_plan_by_id(plan_id_to_set)
                        grant_referral_reward(referrer_id, user['id'], plan_obj['name'] if plan_obj else '')

            elif event['type'] in ('customer.subscription.deleted', 'customer.subscription.paused'):
                sub_obj = event['data']['object']
                customer_id = sub_obj.get('customer')
                with _db() as c:
                    user_row = c.execute("SELECT id FROM users WHERE stripe_customer_id=?", (customer_id,)).fetchone()
                    if user_row:
                        _downgrade_user_to_free(user_row['id'])

            elif event['type'] == 'invoice.payment_failed':
                inv_obj = event['data']['object']
                customer_id = inv_obj.get('customer')
                attempt_count = inv_obj.get('attempt_count', 1)
                with _db() as c:
                    user_row = c.execute("SELECT id FROM users WHERE stripe_customer_id=?", (customer_id,)).fetchone()
                    if user_row:
                        c.execute(
                            "UPDATE users SET subscription_status='payment_failed' WHERE id=?",
                            (user_row['id'],)
                        )
                        c.commit()
                if attempt_count >= 3:
                    with _db() as c:
                        user_row = c.execute("SELECT id FROM users WHERE stripe_customer_id=?", (customer_id,)).fetchone()
                        if user_row:
                            _downgrade_user_to_free(user_row['id'])

            elif event['type'] == 'customer.subscription.updated':
                sub_obj = event['data']['object']
                customer_id = sub_obj.get('customer')
                status = sub_obj.get('status', '')
                period_end = sub_obj.get('current_period_end')
                expires_at = _dt.datetime.utcfromtimestamp(period_end).isoformat() if period_end else None
                db_status = 'active' if status == 'active' else ('payment_failed' if status == 'past_due' else status)
                # auto_renew stays as-is; Stripe cancel_at_period_end controls actual renewal
                with _db() as c:
                    c.execute(
                        "UPDATE users SET subscription_status=?, subscription_expires_at=? WHERE stripe_customer_id=?",
                        (db_status, expires_at, customer_id)
                    )
                    c.commit()

            elif event['type'] in ('invoice.payment_succeeded', 'invoice.paid'):
                inv_obj = event['data']['object']
                customer_id = inv_obj.get('customer')
                billing_reason = inv_obj.get('billing_reason', '')
                # Skip the first invoice — handled by checkout.session.completed
                if billing_reason in ('subscription_cycle', 'subscription_update', 'manual'):
                    amount_paid = (inv_obj.get('amount_paid') or 0) / 100.0
                    currency = inv_obj.get('currency', 'usd')
                    invoice_id = inv_obj.get('id', '')
                    payment_intent = inv_obj.get('payment_intent', '') or ''
                    invoice_pdf = inv_obj.get('invoice_pdf', '') or ''
                    period_start_ts = (inv_obj.get('period_start') or 0)
                    period_end_ts = (inv_obj.get('period_end') or 0)
                    period_start_str = _dt.datetime.utcfromtimestamp(period_start_ts).isoformat() if period_start_ts else None
                    period_end_str = _dt.datetime.utcfromtimestamp(period_end_ts).isoformat() if period_end_ts else None
                    # Extend subscription_expires_at on renewal
                    sub_id_inv = inv_obj.get('subscription')
                    if sub_id_inv:
                        try:
                            sub_renewed = stripe.Subscription.retrieve(sub_id_inv)
                            new_period_end = sub_renewed.get('current_period_end')
                            new_expires = _dt.datetime.utcfromtimestamp(new_period_end).isoformat() if new_period_end else None
                        except Exception:
                            new_expires = period_end_str
                    else:
                        new_expires = period_end_str
                    with _db() as c:
                        user_row = c.execute(
                            "SELECT id FROM users WHERE stripe_customer_id=?", (customer_id,)
                        ).fetchone()
                        if user_row:
                            user_id_inv = user_row['id']
                            if new_expires:
                                c.execute(
                                    "UPDATE users SET subscription_status='active', subscription_expires_at=? WHERE id=?",
                                    (new_expires, user_id_inv)
                                )
                            # Record in payment_history (deduplicate on stripe_invoice_id)
                            existing = c.execute(
                                "SELECT id FROM payment_history WHERE stripe_invoice_id=?", (invoice_id,)
                            ).fetchone()
                            if not existing and amount_paid > 0:
                                plan_row = c.execute("SELECT name FROM plans p JOIN users u ON u.plan_id=p.id WHERE u.id=?", (user_id_inv,)).fetchone()
                                desc = f"Subscription renewal – {plan_row['name'] if plan_row else 'Plan'}"
                                c.execute(
                                    """INSERT INTO payment_history
                                       (user_id, stripe_invoice_id, stripe_payment_intent, amount, currency, status, description, invoice_pdf, period_start, period_end)
                                       VALUES (?,?,?,?,?,?,?,?,?,?)""",
                                    (user_id_inv, invoice_id, payment_intent, amount_paid, currency,
                                     'paid', desc, invoice_pdf, period_start_str, period_end_str)
                                )
                            c.commit()

            return jsonify(received=True)
        except Exception as e:
            return jsonify(error=str(e)), 400


    @app.route("/<lang_code>/subscribe/paypal/<int:plan_id>", methods=['POST'])
    @app.route('/subscribe/paypal/<int:plan_id>', methods=['POST'])
    def paypal_checkout(plan_id):
        paypal_client = get_payment_setting('paypal_client_id', 'PAYPAL_CLIENT_ID')
        paypal_secret = get_payment_setting('paypal_client_secret', 'PAYPAL_CLIENT_SECRET')
        if not paypal_client or not paypal_secret:
            flash("PayPal payments are not configured yet.", "error")
            return redirect(url_for('subscribe_page'))
        try:
            import requests as _req
            plan = get_plan_by_id(plan_id)
            if not plan:
                flash("Plan not found.", "error")
                return redirect(url_for('subscribe_page'))
            period_id = request.form.get('period_id', type=int)
            period = get_period_by_id(period_id) if period_id else None
            months = period['months'] if period else 1
            paypal_plan_id = (period.get('paypal_plan_id') if period and period.get('paypal_plan_id') else None) \
                             or plan.get('paypal_plan_id', '')
            if not paypal_plan_id:
                flash("This plan/billing period is not configured for PayPal checkout.", "error")
                return redirect(url_for('checkout_select', plan_id=plan_id))
            token_resp = _req.post(
                'https://api-m.paypal.com/v1/oauth2/token',
                auth=(paypal_client, paypal_secret),
                data={'grant_type': 'client_credentials'}
            )
            access_token = token_resp.json().get('access_token', '')
            sub_resp = _req.post(
                'https://api-m.paypal.com/v1/billing/subscriptions',
                headers={'Authorization': f'Bearer {access_token}', 'Content-Type': 'application/json'},
                json={
                    'plan_id': paypal_plan_id,
                    'custom_id': f'period_id={period_id or ""}&period_months={months}&plan_id={plan_id}',
                    'application_context': {
                        'return_url': request.host_url + 'paypal/success',
                        'cancel_url': request.host_url + f'checkout/select/{plan_id}'
                    }
                }
            )
            sub_data = sub_resp.json()
            approve_link = next(
                (l['href'] for l in sub_data.get('links', []) if l['rel'] == 'approve'),
                None
            )
            if approve_link:
                return redirect(approve_link, code=303)
            flash("Could not initiate PayPal subscription. Please try again.", "error")
            return redirect(url_for('subscribe_page'))
        except Exception as e:
            flash(f"PayPal error: {str(e)}", "error")
            return redirect(url_for('subscribe_page'))


    @app.route("/<lang_code>/paypal/success")
    @app.route('/paypal/success')
    def paypal_success():
        import datetime as _dt
        subscription_id = request.args.get('subscription_id', '')
        user = get_current_user()
        if user and subscription_id:
            paypal_client = get_payment_setting('paypal_client_id', 'PAYPAL_CLIENT_ID')
            paypal_secret = get_payment_setting('paypal_client_secret', 'PAYPAL_CLIENT_SECRET')
            if paypal_client and paypal_secret:
                try:
                    import requests as _req
                    token_resp = _req.post(
                        'https://api-m.paypal.com/v1/oauth2/token',
                        auth=(paypal_client, paypal_secret),
                        data={'grant_type': 'client_credentials'}
                    )
                    access_token = token_resp.json().get('access_token', '')
                    sub_resp = _req.get(
                        f'https://api-m.paypal.com/v1/billing/subscriptions/{subscription_id}',
                        headers={'Authorization': f'Bearer {access_token}'}
                    )
                    sub_data = sub_resp.json()
                    plan_paypal_id = sub_data.get('plan_id', '')
                    custom_id = sub_data.get('custom_id', '')
                    custom_params = {}
                    for part in custom_id.split('&'):
                        if '=' in part:
                            k, v = part.split('=', 1)
                            custom_params[k] = v
                    period_months = int(custom_params.get('period_months', 1) or 1)
                    if custom_params.get('type') == 'team_plan' and custom_params.get('team_id'):
                        team_id_pay = int(custom_params['team_id'])
                        plan_id_pay = int(custom_params.get('plan_id', 0) or 0)
                        if plan_id_pay:
                            with _db() as c:
                                c.execute(
                                    "UPDATE teams SET plan_id=?, paypal_subscription_id=? WHERE id=?",
                                    (plan_id_pay, subscription_id, team_id_pay)
                                )
                                c.commit()
                            flash("Team PayPal subscription activated!", "success")
                            return redirect(url_for('dashboard_team_detail', team_id=team_id_pay))
                    with _db() as c:
                        db_plan = c.execute(
                            "SELECT p.* FROM plan_billing_periods bp JOIN plans p ON p.id=bp.plan_id WHERE bp.paypal_plan_id=?",
                            (plan_paypal_id,)
                        ).fetchone()
                        if not db_plan:
                            db_plan = c.execute(
                                "SELECT * FROM plans WHERE paypal_plan_id=?", (plan_paypal_id,)
                            ).fetchone()
                        plan_id_to_set = db_plan['id'] if db_plan else user['plan_id']
                        expires_at = None
                        next_billing = (sub_data.get('billing_info') or {}).get('next_billing_time')
                        if next_billing:
                            try:
                                expires_at = next_billing[:19]
                            except Exception:
                                pass
                        if not expires_at:
                            expires_dt = _dt.datetime.utcnow() + _dt.timedelta(days=30 * period_months)
                            expires_at = expires_dt.isoformat()
                        c.execute("""
                            UPDATE users SET paypal_subscription_id=?,
                            subscription_status='active', plan_id=?,
                            billing_period_months=?, subscription_expires_at=?,
                            auto_renew=1
                            WHERE id=?
                        """, (subscription_id, plan_id_to_set, period_months, expires_at, user['id']))
                        c.commit()
                    referrer_id = user.get('referred_by_user_id')
                    if referrer_id:
                        plan_obj = get_plan_by_id(plan_id_to_set)
                        grant_referral_reward(referrer_id, user['id'], plan_obj['name'] if plan_obj else '')
                except Exception:
                    pass
        flash("Your PayPal subscription has been activated!", "success")
        return redirect(url_for('dashboard'))


    @app.route("/<lang_code>/dashboard/blogs")
    @app.route('/dashboard/blogs')
    def dashboard_blogs():
        user = get_current_user()
        if not user:
            return redirect('/login')
        if not user_has_paid_plan(user):
            flash("Blog writing is available on paid plans only.", "error")
            return redirect('/dashboard')
        with _db() as c:
            blogs = c.execute(
                "SELECT rowid,* FROM blogs WHERE author_user_id=? ORDER BY rowid DESC",
                (user['id'],)
            ).fetchall()
        return render_template('dashboard/blogs.html', user=user, blogs=[dict(b) for b in blogs])


    @app.route("/<lang_code>/dashboard/blogs/new", methods=['GET', 'POST'])
    @app.route('/dashboard/blogs/new', methods=['GET', 'POST'])
    def dashboard_blog_new():
        user = get_current_user()
        if not user:
            return redirect('/login')
        if not user_has_paid_plan(user):
            flash("Blog writing is available on paid plans only.", "error")
            return redirect('/dashboard')
        if request.method == 'POST':
            title = request.form.get('title', '').strip()
            summary = request.form.get('summary', '').strip()
            content = request.form.get('content', '').strip()
            image = request.files.get('image')
            if not title or not content:
                return render_template('dashboard/blog_write.html', user=user, blog=None, error="Title and content are required.")
            filename = ''
            if image and image.filename:
                filename = secure_filename(image.filename)
                filepath = os.path.join(app.config['BLOGS_ASSETS_DIR'], filename)
                image.save(filepath)
            slug = slugify(title).strip()
            with _db() as c:
                existing = c.execute("SELECT rowid FROM blogs WHERE slug=?", (slug,)).fetchone()
                if existing:
                    slug = slug + '-' + str(user['id'])
                c.execute(
                    "INSERT INTO blogs (title, summary, content, image, slug, author_user_id, status, created_at, updated_at) VALUES (?,?,?,?,?,?,'pending_review',datetime('now'),datetime('now'))",
                    (title, summary, content, filename, slug, user['id'])
                )
                c.commit()
            try:
                _notify_admin_pending_blog(title, user.get('username', ''))
            except Exception:
                pass
            flash("Your blog post has been submitted for review. We'll notify you once it's reviewed.", "success")
            return redirect(url_for('dashboard_blogs'))
        return render_template('dashboard/blog_write.html', user=user, blog=None)


    @app.route("/<lang_code>/dashboard/blogs/<int:blog_id>/edit", methods=['GET', 'POST'])
    @app.route('/dashboard/blogs/<int:blog_id>/edit', methods=['GET', 'POST'])
    def dashboard_blog_edit(blog_id):
        user = get_current_user()
        if not user:
            return redirect('/login')
        with _db() as c:
            blog = c.execute("SELECT rowid,* FROM blogs WHERE rowid=? AND author_user_id=?", (blog_id, user['id'])).fetchone()
        if not blog:
            flash("Blog post not found.", "error")
            return redirect(url_for('dashboard_blogs'))
        blog = dict(blog)
        if blog['status'] not in ('draft', 'rejected', 'pending_review'):
            flash("You can only edit draft or rejected posts.", "error")
            return redirect(url_for('dashboard_blogs'))
        if request.method == 'POST':
            title = request.form.get('title', '').strip()
            summary = request.form.get('summary', '').strip()
            content = request.form.get('content', '').strip()
            image = request.files.get('image')
            if not title or not content:
                return render_template('dashboard/blog_write.html', user=user, blog=blog, error="Title and content are required.")
            filename = blog.get('image', '')
            if image and image.filename:
                filename = secure_filename(image.filename)
                filepath = os.path.join(app.config['BLOGS_ASSETS_DIR'], filename)
                image.save(filepath)
            slug = slugify(title).strip()
            with _db() as c:
                c.execute(
                    "UPDATE blogs SET title=?, summary=?, content=?, image=?, slug=?, status='pending_review', admin_note='', updated_at=datetime('now') WHERE rowid=?",
                    (title, summary, content, filename, slug, blog_id)
                )
                c.commit()
            flash("Your blog post has been resubmitted for review.", "success")
            return redirect(url_for('dashboard_blogs'))
        return render_template('dashboard/blog_write.html', user=user, blog=blog)


    @app.route("/<lang_code>/dashboard/blogs/<int:blog_id>/delete", methods=['POST'])
    @app.route('/dashboard/blogs/<int:blog_id>/delete', methods=['POST'])
    def dashboard_blog_delete(blog_id):
        user = get_current_user()
        if not user:
            return redirect('/login')
        with _db() as c:
            blog = c.execute("SELECT rowid,* FROM blogs WHERE rowid=? AND author_user_id=?", (blog_id, user['id'])).fetchone()
            if blog and dict(blog)['status'] not in ('published',):
                c.execute("DELETE FROM blogs WHERE rowid=?", (blog_id,))
                c.commit()
                flash("Blog post deleted.", "success")
            else:
                flash("Cannot delete a published post.", "error")
        return redirect(url_for('dashboard_blogs'))


    @app.route("/<lang_code>/dashboard/blogs/<int:blog_id>/request-manual-payment", methods=['POST'])
    @app.route('/dashboard/blogs/<int:blog_id>/request-manual-payment', methods=['POST'])
    def dashboard_blog_request_manual(blog_id):
        user = get_current_user()
        if not user:
            return redirect('/login')
        with _db() as c:
            blog = c.execute("SELECT rowid,* FROM blogs WHERE rowid=? AND author_user_id=?", (blog_id, user['id'])).fetchone()
            if blog and dict(blog)['status'] == 'pending_payment':
                c.execute(
                    "UPDATE blogs SET payment_status='pending_manual', updated_at=datetime('now') WHERE rowid=?",
                    (blog_id,)
                )
                c.commit()
        flash("Manual payment request submitted. Admin will confirm your payment and publish your post.", "success")
        return redirect(url_for('dashboard_blogs'))


    @app.route("/<lang_code>/dashboard/blogs/<int:blog_id>/pay", methods=['GET'])
    @app.route('/dashboard/blogs/<int:blog_id>/pay', methods=['GET'])
    def dashboard_blog_pay(blog_id):
        user = get_current_user()
        if not user:
            return redirect('/login')
        with _db() as c:
            blog = c.execute("SELECT rowid,* FROM blogs WHERE rowid=? AND author_user_id=?", (blog_id, user['id'])).fetchone()
        if not blog:
            flash("Blog post not found.", "error")
            return redirect(url_for('dashboard_blogs'))
        blog = dict(blog)
        if blog['status'] not in ('pending_payment',):
            return redirect(url_for('dashboard_blogs'))
        stripe_key = get_payment_setting('stripe_publishable_key', 'STRIPE_PUBLISHABLE_KEY')
        paypal_client = get_payment_setting('paypal_client_id', 'PAYPAL_CLIENT_ID')
        settings = get_site_settings()
        manual_enabled = settings.get('manual_payment_enabled', '1') == '1'
        manual_instructions = settings.get('manual_payment_instructions', '')
        return render_template('dashboard/blog_pay.html', user=user, blog=blog,
                               has_stripe=bool(stripe_key), has_paypal=bool(paypal_client),
                               manual_enabled=manual_enabled,
                               manual_instructions=manual_instructions)


    @app.route('/blog-payment/stripe/<int:blog_id>', methods=['POST'])
    def blog_stripe_checkout(blog_id):
        user = get_current_user()
        if not user:
            return redirect('/login')
        stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
        if not stripe_secret:
            flash("Stripe is not configured.", "error")
            return redirect(url_for('dashboard_blog_pay', blog_id=blog_id))
        try:
            import stripe
            stripe.api_key = stripe_secret
            with _db() as c:
                blog = c.execute("SELECT rowid,* FROM blogs WHERE rowid=? AND author_user_id=?", (blog_id, user['id'])).fetchone()
            if not blog or dict(blog)['status'] != 'pending_payment':
                flash("This blog post is not awaiting payment.", "error")
                return redirect(url_for('dashboard_blogs'))
            blog = dict(blog)
            fee = float(blog.get('publish_fee') or 0)
            if fee <= 0:
                flash("No payment required for this post.", "error")
                return redirect(url_for('dashboard_blogs'))
            checkout = stripe.checkout.Session.create(
                payment_method_types=['card'],
                mode='payment',
                line_items=[{
                    'price_data': {
                        'currency': 'usd',
                        'unit_amount': int(fee * 100),
                        'product_data': {'name': f'Blog Publication Fee: {blog["title"]}'},
                    },
                    'quantity': 1,
                }],
                customer_email=user['email'],
                metadata={'blog_id': str(blog_id), 'user_id': str(user['id']), 'type': 'blog_payment'},
                success_url=request.host_url + 'dashboard/blogs?blog_payment=success',
                cancel_url=request.host_url + f'dashboard/blogs/{blog_id}/pay',
            )
            with _db() as c:
                c.execute("UPDATE blogs SET blog_payment_session=? WHERE rowid=?", (checkout.id, blog_id))
                c.commit()
            return redirect(checkout.url, code=303)
        except Exception as e:
            flash(f"Payment error: {str(e)}", "error")
            return redirect(url_for('dashboard_blog_pay', blog_id=blog_id))


    @app.route('/blog-payment/paypal/<int:blog_id>', methods=['POST'])
    def blog_paypal_checkout(blog_id):
        user = get_current_user()
        if not user:
            return redirect('/login')
        paypal_client = get_payment_setting('paypal_client_id', 'PAYPAL_CLIENT_ID')
        paypal_secret = get_payment_setting('paypal_client_secret', 'PAYPAL_CLIENT_SECRET')
        if not paypal_client or not paypal_secret:
            flash("PayPal is not configured.", "error")
            return redirect(url_for('dashboard_blog_pay', blog_id=blog_id))
        try:
            import requests as _req
            with _db() as c:
                blog = c.execute("SELECT rowid,* FROM blogs WHERE rowid=? AND author_user_id=?", (blog_id, user['id'])).fetchone()
            if not blog or dict(blog)['status'] != 'pending_payment':
                flash("This blog post is not awaiting payment.", "error")
                return redirect(url_for('dashboard_blogs'))
            blog = dict(blog)
            fee = float(blog.get('publish_fee') or 0)
            token_resp = _req.post(
                'https://api-m.paypal.com/v1/oauth2/token',
                auth=(paypal_client, paypal_secret),
                data={'grant_type': 'client_credentials'}
            )
            access_token = token_resp.json().get('access_token', '')
            order_resp = _req.post(
                'https://api-m.paypal.com/v2/checkout/orders',
                headers={'Authorization': f'Bearer {access_token}', 'Content-Type': 'application/json'},
                json={
                    'intent': 'CAPTURE',
                    'purchase_units': [{
                        'amount': {'currency_code': 'USD', 'value': f'{fee:.2f}'},
                        'description': f'Blog Publication Fee: {blog["title"]}'
                    }],
                    'application_context': {
                        'return_url': request.host_url + f'blog-payment/paypal/success?blog_id={blog_id}',
                        'cancel_url': request.host_url + f'dashboard/blogs/{blog_id}/pay'
                    }
                }
            )
            order_data = order_resp.json()
            approve_link = next((l['href'] for l in order_data.get('links', []) if l['rel'] == 'approve'), None)
            if approve_link:
                with _db() as c:
                    c.execute("UPDATE blogs SET blog_payment_session=? WHERE rowid=?", (order_data.get('id', ''), blog_id))
                    c.commit()
                return redirect(approve_link, code=303)
            flash("Could not initiate PayPal payment.", "error")
            return redirect(url_for('dashboard_blog_pay', blog_id=blog_id))
        except Exception as e:
            flash(f"PayPal error: {str(e)}", "error")
            return redirect(url_for('dashboard_blog_pay', blog_id=blog_id))


    @app.route('/blog-payment/paypal/success')
    def blog_paypal_success():
        blog_id = request.args.get('blog_id', type=int)
        order_id = request.args.get('token', '')
        user = get_current_user()
        if not user or not blog_id:
            return redirect(url_for('dashboard_blogs'))
        paypal_client = get_payment_setting('paypal_client_id', 'PAYPAL_CLIENT_ID')
        paypal_secret = get_payment_setting('paypal_client_secret', 'PAYPAL_CLIENT_SECRET')
        try:
            import requests as _req
            token_resp = _req.post(
                'https://api-m.paypal.com/v1/oauth2/token',
                auth=(paypal_client, paypal_secret),
                data={'grant_type': 'client_credentials'}
            )
            access_token = token_resp.json().get('access_token', '')
            capture_resp = _req.post(
                f'https://api-m.paypal.com/v2/checkout/orders/{order_id}/capture',
                headers={'Authorization': f'Bearer {access_token}', 'Content-Type': 'application/json'}
            )
            capture_data = capture_resp.json()
            if capture_data.get('status') == 'COMPLETED':
                with _db() as c:
                    c.execute(
                        "UPDATE blogs SET status='published', payment_status='paid', updated_at=datetime('now') WHERE rowid=? AND author_user_id=?",
                        (blog_id, user['id'])
                    )
                    c.commit()
                flash("Payment successful! Your blog post is now published.", "success")
            else:
                flash("Payment could not be confirmed. Please try again.", "error")
        except Exception as e:
            flash(f"Error confirming payment: {str(e)}", "error")
        return redirect(url_for('dashboard_blogs'))


    @app.route("/<lang_code>/dashboard/files")
    @app.route('/dashboard/files')
    @login_required_user
    def dashboard_files():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        page = request.args.get('page', 1, type=int)
        per_page = 30
        offset = (page - 1) * per_page
        with _db() as c:
            total = c.execute("SELECT COUNT(*) FROM conversions WHERE user_id=? AND status != 'deleted'", (user['id'],)).fetchone()[0]
            rows = c.execute(
                """SELECT conv.*, fu.file_path AS orig_file_path, fu.file_name AS orig_file_name, fu.file_size AS orig_file_size
                   FROM conversions conv
                   LEFT JOIN file_uploads fu ON fu.id = conv.upload_id
                   WHERE conv.user_id=? AND conv.status != 'deleted' ORDER BY conv.id DESC LIMIT ? OFFSET ?""",
                (user['id'], per_page, offset)
            ).fetchall()
        files = [dict(r) for r in rows]
        # Build a set of job_ids that have share grants or shared links
        job_ids = [f['job_id'] for f in files if f.get('job_id')]
        _share_counts = {}
        if job_ids:
            placeholders = ','.join('?' * len(job_ids))
            with _db() as c:
                grant_rows = c.execute(
                    f"SELECT job_id, COUNT(*) AS cnt FROM job_access_grants "
                    f"WHERE job_id IN ({placeholders}) GROUP BY job_id",
                    job_ids
                ).fetchall()
                link_rows = c.execute(
                    f"SELECT job_id, COUNT(*) AS cnt FROM shared_links "
                    f"WHERE job_id IN ({placeholders}) AND user_id=? GROUP BY job_id",
                    job_ids + [user['id']]
                ).fetchall()
            for r in grant_rows:
                _share_counts[r['job_id']] = _share_counts.get(r['job_id'], 0) + r['cnt']
            for r in link_rows:
                _share_counts[r['job_id']] = _share_counts.get(r['job_id'], 0) + r['cnt']
        for f in files:
            f['share_count'] = _share_counts.get(f.get('job_id', ''), 0)
        total_pages = max(1, (total + per_page - 1) // per_page)

        # Files shared WITH the current user by others
        user_email = (user.get('email') or '').strip().lower()
        shared_with_me = []
        try:
            # Reconciliation: if grants were created for this user's email before
            # they registered (granted_to_user_id was NULL at creation time),
            # backfill the user_id now so future lookups match by either column.
            if user_email:
                try:
                    with _db() as c:
                        c.execute(
                            "UPDATE job_access_grants SET granted_to_user_id=? "
                            "WHERE LOWER(granted_to_email)=? AND granted_to_user_id IS NULL",
                            (user['id'], user_email)
                        )
                        c.commit()
                except Exception:
                    pass

            with _db() as c:
                sw_rows = c.execute(
                    """SELECT jag.id AS grant_id, jag.job_id, jag.granted_at,
                              conv.input_format, conv.output_format, conv.status,
                              conv.file_name, conv.created_at AS conv_created_at,
                              owner.username AS shared_by_name, owner.email AS shared_by_email
                       FROM job_access_grants jag
                       JOIN conversions conv ON conv.job_id = jag.job_id
                       JOIN users owner ON owner.id = conv.user_id
                       WHERE (jag.granted_to_user_id = ? OR LOWER(jag.granted_to_email) = ?)
                         AND conv.user_id != ?
                         AND conv.status != 'deleted'
                       ORDER BY jag.granted_at DESC LIMIT 50""",
                    (user['id'], user_email, user['id'])
                ).fetchall()
            shared_with_me = [dict(r) for r in sw_rows]
        except Exception:
            shared_with_me = []

        return render_template('dashboard/files.html', user=user, plan=plan, files=files,
                               total=total, page=page, total_pages=total_pages,
                               shared_with_me=shared_with_me)


    @app.route('/dashboard/files/delete/<int:conv_id>', methods=['POST'])
    @app.route('/<lang_code>/dashboard/files/delete/<int:conv_id>', methods=['POST'])
    @login_required_user
    def dashboard_file_delete(conv_id):
        import shutil as _sh
        user = get_current_user()
        _udir = app.config.get('UPLOAD_DIR', os.path.join(dir_path, 'storage', 'uploads'))
        def _res(p):
            if not p: return None
            return p if os.path.isabs(p) else os.path.join(_udir, p)
        with _db() as c:
            row = c.execute(
                "SELECT conv.output_path, conv.upload_id, conv.job_id, conv.file_name,"
                " fu.file_path AS upload_file_path, fu.file_size AS file_size"
                " FROM conversions conv LEFT JOIN file_uploads fu ON fu.id = conv.upload_id"
                " WHERE conv.id=? AND conv.user_id=?", (conv_id, user['id'])
            ).fetchone()
            if row:
                _fname = row['file_name'] or os.path.basename(row['upload_file_path'] or '') or str(conv_id)
                _fsize = row['file_size']
                c.execute(
                    "DELETE FROM conversions WHERE id=? AND user_id=?",
                    (conv_id, user['id']))
                if row['upload_id']:
                    try:
                        c.execute(
                            "DELETE FROM file_uploads WHERE id=?",
                            (row['upload_id'],))
                    except Exception:
                        app.logger.exception("dashboard_file_delete: file_uploads delete failed")
                _jid = row['job_id']
                if _jid:
                    try:
                        c.execute("DELETE FROM shared_links WHERE job_id=?", (_jid,))
                        c.execute("DELETE FROM job_access_grants WHERE job_id=?", (_jid,))
                    except Exception:
                        app.logger.exception("dashboard_file_delete: shared_links cleanup failed")
                c.commit()
                log_deletion('file', entity_id=conv_id, entity_name=_fname,
                             actor_user_id=user['id'], actor_username=user.get('username', ''),
                             actor_role='user', file_size_bytes=_fsize,
                             extra_meta={'job_id': row['job_id'], 'upload_id': row['upload_id']},
                             entity_owner_user_id=user['id'])
                for fpath in (row['output_path'] or '').split('\n'):
                    fpath = (_res(fpath.strip()) or '').strip()
                    if not fpath: continue
                    try:
                        if os.path.isfile(fpath): os.remove(fpath)
                        parent = os.path.dirname(fpath)
                        if os.path.isdir(parent) and not os.listdir(parent):
                            _sh.rmtree(parent, ignore_errors=True)
                    except Exception:
                        app.logger.exception("dashboard_file_delete: output file removal failed")
                if row['upload_id']:
                    try:
                        up_file = _res(row['upload_file_path'] or '')
                        if up_file:
                            if os.path.isfile(up_file): os.remove(up_file)
                            up_folder = os.path.dirname(up_file)
                            if os.path.isdir(up_folder) and not os.listdir(up_folder):
                                _sh.rmtree(up_folder, ignore_errors=True)
                    except Exception:
                        app.logger.exception("dashboard_file_delete: upload file removal failed")
        flash("File deleted.", "success")
        return redirect(url_for('dashboard_files'))


    @app.route('/dashboard/files/delete-all', methods=['POST'])
    @app.route('/<lang_code>/dashboard/files/delete-all', methods=['POST'])
    @login_required_user
    def dashboard_files_delete_all():
        import shutil as _sh
        user = get_current_user()
        _udir = app.config.get('UPLOAD_DIR', os.path.join(dir_path, 'storage', 'uploads'))
        def _res(p):
            if not p: return None
            return p if os.path.isabs(p) else os.path.join(_udir, p)
        with _db() as c:
            rows = c.execute(
                "SELECT conv.id, conv.job_id, conv.output_path, conv.upload_id, conv.file_name,"
                " fu.file_path AS upload_file_path, fu.file_size AS file_size"
                " FROM conversions conv LEFT JOIN file_uploads fu ON fu.id = conv.upload_id"
                " WHERE conv.user_id=? AND conv.status != 'deleted'",
                (user['id'],)
            ).fetchall()
            rows = [dict(r) for r in rows]
            c.execute("DELETE FROM conversions WHERE user_id=?", (user['id'],))
            for row in rows:
                if row['upload_id']:
                    try:
                        c.execute("DELETE FROM file_uploads WHERE id=?", (row['upload_id'],))
                    except Exception:
                        app.logger.exception("dashboard_files_delete_all: file_uploads delete failed")
            _jids = [row['job_id'] for row in rows if row['job_id']]
            for _jid in _jids:
                try:
                    c.execute("DELETE FROM shared_links WHERE job_id=?", (_jid,))
                    c.execute("DELETE FROM job_access_grants WHERE job_id=?", (_jid,))
                except Exception:
                    app.logger.exception("dashboard_files_delete_all: shared_links cleanup failed")
            c.commit()
        for row in rows:
            _fname = row.get('file_name') or os.path.basename(row.get('upload_file_path') or '') or str(row['id'])
            log_deletion('file', entity_id=row['id'], entity_name=_fname,
                         actor_user_id=user['id'], actor_username=user.get('username', ''),
                         actor_role='user', file_size_bytes=row.get('file_size'),
                         extra_meta={'job_id': row['job_id'], 'upload_id': row['upload_id'], 'bulk': True},
                         entity_owner_user_id=user['id'])
        for row in rows:
            for fpath in (row['output_path'] or '').split('\n'):
                fpath = (_res(fpath.strip()) or '').strip()
                if not fpath: continue
                try:
                    if os.path.isfile(fpath): os.remove(fpath)
                    parent = os.path.dirname(fpath)
                    if os.path.isdir(parent) and not os.listdir(parent):
                        _sh.rmtree(parent, ignore_errors=True)
                except Exception:
                    app.logger.exception("dashboard_files_delete_all: output file removal failed")
            if row['upload_id']:
                try:
                    up_file = _res(row['upload_file_path'] or '')
                    if up_file:
                        if os.path.isfile(up_file): os.remove(up_file)
                        up_folder = os.path.dirname(up_file)
                        if os.path.isdir(up_folder) and not os.listdir(up_folder):
                            _sh.rmtree(up_folder, ignore_errors=True)
                except Exception:
                    app.logger.exception("dashboard_files_delete_all: upload file removal failed")
        flash("All files deleted.", "success")
        return redirect(url_for('dashboard_files'))


    @app.route('/dashboard/files/delete-selected', methods=['POST'])
    @app.route('/<lang_code>/dashboard/files/delete-selected', methods=['POST'])
    @login_required_user
    def dashboard_files_delete_selected():
        import shutil as _sh
        user = get_current_user()
        ids = request.form.getlist('ids')
        if not ids:
            flash("No files selected.", "warning")
            return redirect(url_for('dashboard_files'))
        _udir = app.config.get('UPLOAD_DIR', os.path.join(dir_path, 'storage', 'uploads'))
        def _res(p):
            if not p: return None
            return p if os.path.isabs(p) else os.path.join(_udir, p)
        deleted = 0
        rows_to_delete = []
        with _db() as c:
            for raw_id in ids:
                try:
                    conv_id = int(raw_id)
                except (ValueError, TypeError):
                    continue
                row = c.execute(
                    "SELECT conv.id, conv.output_path, conv.upload_id, conv.job_id, conv.file_name,"
                    " fu.file_path AS upload_file_path, fu.file_size AS file_size"
                    " FROM conversions conv LEFT JOIN file_uploads fu ON fu.id = conv.upload_id"
                    " WHERE conv.id=? AND conv.user_id=?",
                    (conv_id, user['id'])
                ).fetchone()
                if row:
                    rows_to_delete.append(dict(row))
                    c.execute(
                        "DELETE FROM conversions WHERE id=? AND user_id=?",
                        (conv_id, user['id']))
                    if row['upload_id']:
                        try:
                            c.execute("DELETE FROM file_uploads WHERE id=?", (row['upload_id'],))
                        except Exception:
                            app.logger.exception("dashboard_files_delete_selected: file_uploads delete failed")
                    _jid = row['job_id']
                    if _jid:
                        try:
                            c.execute("DELETE FROM shared_links WHERE job_id=?", (_jid,))
                            c.execute("DELETE FROM job_access_grants WHERE job_id=?", (_jid,))
                        except Exception:
                            app.logger.exception("dashboard_files_delete_selected: shared_links cleanup failed")
                    deleted += 1
            c.commit()
        for row in rows_to_delete:
            _fname = row.get('file_name') or os.path.basename(row.get('upload_file_path') or '') or str(row['id'])
            log_deletion('file', entity_id=row['id'], entity_name=_fname,
                         actor_user_id=user['id'], actor_username=user.get('username', ''),
                         actor_role='user', file_size_bytes=row.get('file_size'),
                         extra_meta={'job_id': row['job_id'], 'upload_id': row['upload_id'], 'bulk': True},
                         entity_owner_user_id=user['id'])
        for row in rows_to_delete:
            for fpath in (row['output_path'] or '').split('\n'):
                fpath = (_res(fpath.strip()) or '').strip()
                if not fpath: continue
                try:
                    if os.path.isfile(fpath): os.remove(fpath)
                    parent = os.path.dirname(fpath)
                    if os.path.isdir(parent) and not os.listdir(parent):
                        _sh.rmtree(parent, ignore_errors=True)
                except Exception:
                    app.logger.exception("dashboard_files_delete_selected: output file removal failed")
            if row['upload_id']:
                try:
                    up_file = _res(row['upload_file_path'] or '')
                    if up_file:
                        if os.path.isfile(up_file): os.remove(up_file)
                        up_folder = os.path.dirname(up_file)
                        if os.path.isdir(up_folder) and not os.listdir(up_folder):
                            _sh.rmtree(up_folder, ignore_errors=True)
                except Exception:
                    app.logger.exception("dashboard_files_delete_selected: upload file removal failed")
        flash(f"{deleted} file(s) deleted.", "success")
        return redirect(url_for('dashboard_files'))


    @app.route('/dashboard/deletion-history')
    @app.route('/<lang_code>/dashboard/deletion-history')
    @login_required_user
    def dashboard_deletion_history():
        user = get_current_user()
        page = max(1, int(request.args.get('page', 1)))
        per_page = 25
        offset = (page - 1) * per_page
        uid = user['id']
        with _db() as c:
            total = c.execute(
                "SELECT COUNT(*) FROM deletion_log WHERE (actor_user_id=? OR entity_owner_user_id=?)",
                (uid, uid)
            ).fetchone()[0]
            rows = c.execute(
                "SELECT * FROM deletion_log WHERE (actor_user_id=? OR entity_owner_user_id=?) "
                "ORDER BY deleted_at DESC LIMIT ? OFFSET ?",
                (uid, uid, per_page, offset)
            ).fetchall()
        rows = [dict(r) for r in rows]
        total_pages = max(1, (total + per_page - 1) // per_page)
        return render_template(
            'dashboard/deletion-history.html',
            entries=rows, total=total, page=page,
            total_pages=total_pages, per_page=per_page
        )

    @app.route("/<lang_code>/dashboard/notifications", methods=['GET', 'POST'])
    @app.route('/dashboard/notifications', methods=['GET', 'POST'])
    @login_required_user
    def dashboard_notifications():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        _NOTIF_EVENTS = ['login', 'file_uploaded', 'blog_submitted', 'blog_reviewed',
                         'blog_published', 'plan_changed', 'payment_received', 'referral_signup', 'password_changed']
        if request.method == 'POST':
            with _db() as c:
                existing = c.execute("SELECT 1 FROM user_notification_prefs WHERE user_id=?", (user['id'],)).fetchone()
                vals = {ev: 1 if request.form.get(ev) else 0 for ev in _NOTIF_EVENTS}
                if existing:
                    set_clause = ', '.join(f"{k}=?" for k in vals)
                    c.execute(f"UPDATE user_notification_prefs SET {set_clause} WHERE user_id=?",
                              list(vals.values()) + [user['id']])
                else:
                    cols = 'user_id, ' + ', '.join(vals.keys())
                    placeholders = ', '.join(['?'] * (len(vals) + 1))
                    c.execute(f"INSERT INTO user_notification_prefs ({cols}) VALUES ({placeholders})",
                              [user['id']] + list(vals.values()))
                c.commit()
            flash("Notification preferences saved.", "success")
            return redirect(url_for('dashboard_notifications'))
        prefs = get_user_notif_prefs(user['id'])
        prefs_dict = {ev: prefs.get(ev, 1) if prefs else 1 for ev in _NOTIF_EVENTS}
        return render_template('dashboard/notifications.html', user=user, plan=plan, prefs=prefs_dict)


    @app.route("/<lang_code>/dashboard/conversion/<int:conv_id>/delete", methods=['POST'])
    @app.route('/dashboard/conversion/<int:conv_id>/delete', methods=['POST'])
    @login_required_user
    def dashboard_delete_conversion(conv_id):
        import shutil as _sh
        user = get_current_user()
        _udir = app.config.get('UPLOAD_DIR', os.path.join(dir_path, 'storage', 'uploads'))
        def _res(p):
            if not p: return None
            return p if os.path.isabs(p) else os.path.join(_udir, p)
        with _db() as c:
            row = c.execute(
                "SELECT conv.job_id, conv.output_path, conv.upload_id, conv.file_name,"
                " fu.file_path AS upload_file_path, fu.file_size AS file_size"
                " FROM conversions conv LEFT JOIN file_uploads fu ON fu.id = conv.upload_id"
                " WHERE conv.id=? AND conv.user_id=?",
                (conv_id, user['id'])
            ).fetchone()
            if row:
                row = dict(row)
            # Hard-delete conversion and upload rows
            c.execute("DELETE FROM conversions WHERE id=? AND user_id=?", (conv_id, user['id']))
            if row and row['upload_id']:
                c.execute("DELETE FROM file_uploads WHERE id=?", (row['upload_id'],))
            # Remove shared links and access grants
            if row and row['job_id']:
                c.execute("DELETE FROM shared_links WHERE job_id=?", (row['job_id'],))
                c.execute("DELETE FROM job_access_grants WHERE job_id=?", (row['job_id'],))
            c.commit()
        if row:
            _fname = row.get('file_name') or os.path.basename(row.get('upload_file_path') or '') or str(conv_id)
            log_deletion('conversion', entity_id=conv_id, entity_name=_fname,
                         actor_user_id=user['id'], actor_username=user.get('username', ''),
                         actor_role='user', file_size_bytes=row.get('file_size'),
                         extra_meta={'job_id': row.get('job_id'), 'upload_id': row.get('upload_id')},
                         entity_owner_user_id=user['id'])
        # Delete physical files after commit
        if row:
            for fpath in (row.get('output_path') or '').split('\n'):
                fpath = (_res(fpath.strip()) or '').strip()
                if not fpath: continue
                try:
                    if os.path.isfile(fpath): os.remove(fpath)
                    parent = os.path.dirname(fpath)
                    if os.path.isdir(parent) and not os.listdir(parent):
                        _sh.rmtree(parent, ignore_errors=True)
                except Exception:
                    app.logger.exception("dashboard_delete_conversion: output file removal failed")
            if row.get('upload_id'):
                try:
                    up_file = _res(row.get('upload_file_path') or '')
                    if up_file:
                        if os.path.isfile(up_file): os.remove(up_file)
                        up_folder = os.path.dirname(up_file)
                        if os.path.isdir(up_folder) and not os.listdir(up_folder):
                            _sh.rmtree(up_folder, ignore_errors=True)
                except Exception:
                    app.logger.exception("dashboard_delete_conversion: upload file removal failed")
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify(success=True)
        flash("Conversion removed from history.", "success")
        return redirect(url_for('dashboard'))


    @app.route("/<lang_code>/dashboard/referrals")
    @app.route('/dashboard/referrals')
    @login_required_user
    def dashboard_referrals():
        user = get_current_user()
        ref_code = ensure_referral_code(user['id'])
        referrals = get_user_referrals(user['id'])
        rewards = get_user_rewards(user['id'])
        ref_url = request.host_url.rstrip('/') + '/register?ref=' + ref_code
        granted_days = sum(r['reward_days'] for r in rewards if r['status'] == 'granted')
        pending_count = sum(1 for r in rewards if r['status'] == 'pending')
        return render_template('dashboard/referrals.html',
                               user=user, ref_code=ref_code, ref_url=ref_url,
                               referrals=referrals, rewards=rewards,
                               granted_days=granted_days, pending_count=pending_count)

    # ─────────────────────────────────────────────────────────────────────────
    # BILLING PAGE
    # ─────────────────────────────────────────────────────────────────────────
    def _get_billing_invoices(user_id):
        """Return merged invoice list from payment_history and manual_payments."""
        with _db() as c:
            # Stripe / online payments
            stripe_rows = c.execute(
                "SELECT id, 'stripe' AS source, stripe_invoice_id, amount, currency, status, "
                "description, invoice_pdf, created_at FROM payment_history "
                "WHERE user_id=? ORDER BY created_at DESC LIMIT 100",
                (user_id,)
            ).fetchall()
            stripe_invoices = []
            for r in stripe_rows:
                d = dict(r)
                d['method'] = 'Online (Stripe)'
                d['source'] = 'stripe'
                stripe_invoices.append(d)

            # Manual payments recorded by admin
            manual_rows = c.execute(
                "SELECT mp.id, 'manual' AS source, mp.amount, mp.currency, mp.method, "
                "mp.notes, mp.created_at, p.name AS plan_name "
                "FROM manual_payments mp "
                "LEFT JOIN plans p ON p.id = mp.plan_id "
                "WHERE mp.user_id=? ORDER BY mp.created_at DESC LIMIT 100",
                (user_id,)
            ).fetchall()
            manual_invoices = []
            for r in manual_rows:
                d = dict(r)
                d['status'] = 'paid'
                d['description'] = (d.get('plan_name') or 'Plan upgrade') + (f' – {d["notes"]}' if d.get('notes') else '')
                d['invoice_pdf'] = None
                d['stripe_invoice_id'] = None
                d['source'] = 'manual'
                manual_invoices.append(d)

        # Merge and sort by created_at descending
        all_invoices = stripe_invoices + manual_invoices
        all_invoices.sort(key=lambda x: x.get('created_at') or '', reverse=True)
        return all_invoices[:100]

    @app.route("/<lang_code>/dashboard/billing")
    @app.route('/dashboard/billing')
    @login_required_user
    def dashboard_billing():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        invoices = _get_billing_invoices(user['id'])
        return render_template('dashboard/billing.html',
                               user=user, plan=plan, invoices=invoices,
                               active_page='billing')

    @app.route("/<lang_code>/dashboard/billing/download/csv")
    @app.route('/dashboard/billing/download/csv')
    @login_required_user
    def dashboard_billing_csv():
        """Download all invoices as a CSV file."""
        user = get_current_user()
        invoices = _get_billing_invoices(user['id'])
        output = io.StringIO()
        writer = csv.writer(output)
        writer.writerow(['Date', 'Description', 'Amount', 'Currency', 'Method', 'Status'])
        for inv in invoices:
            amount_val = inv.get('amount') or 0
            writer.writerow([
                inv.get('created_at', '')[:10],
                inv.get('description', ''),
                f"{amount_val / 100:.2f}" if inv.get('source') == 'stripe' else f"{amount_val:.2f}",
                (inv.get('currency') or 'USD').upper(),
                inv.get('method', inv.get('source', '')).title(),
                (inv.get('status') or '').title(),
            ])
        output.seek(0)
        buf = io.BytesIO(output.getvalue().encode('utf-8-sig'))
        buf.seek(0)
        return send_file(buf, as_attachment=True,
                         download_name='invoices.csv',
                         mimetype='text/csv')

    def _fetch_invoice(user_id, source, inv_id):
        """Fetch a single invoice dict for the given user, source, and id."""
        with _db() as c:
            if source == 'stripe':
                row = c.execute(
                    "SELECT * FROM payment_history WHERE id=? AND user_id=?",
                    (inv_id, user_id)
                ).fetchone()
                if row:
                    inv = dict(row)
                    inv['source'] = 'stripe'
                    inv['method'] = 'Online (Stripe)'
                    inv['display_amount'] = (inv.get('amount') or 0) / 100
                    return inv
            elif source == 'manual':
                row = c.execute(
                    "SELECT mp.*, p.name AS plan_name FROM manual_payments mp "
                    "LEFT JOIN plans p ON p.id = mp.plan_id "
                    "WHERE mp.id=? AND mp.user_id=?",
                    (inv_id, user_id)
                ).fetchone()
                if row:
                    inv = dict(row)
                    inv['source'] = 'manual'
                    inv['status'] = 'paid'
                    inv['description'] = (inv.get('plan_name') or 'Plan upgrade') + (f' – {inv["notes"]}' if inv.get('notes') else '')
                    inv['invoice_pdf'] = None
                    inv['stripe_invoice_id'] = None
                    inv['display_amount'] = inv.get('amount') or 0
                    return inv
        return None

    @app.route("/<lang_code>/dashboard/billing/invoice/<source>/<int:inv_id>")
    @app.route('/dashboard/billing/invoice/<source>/<int:inv_id>')
    @login_required_user
    def dashboard_billing_invoice(source, inv_id):
        """Render a viewable invoice page."""
        user = get_current_user()
        inv = _fetch_invoice(user['id'], source, inv_id)
        if not inv:
            abort(404)
        inv_num = f"INV-{source[0].upper()}{inv_id:06d}"
        pdf_url = f"/dashboard/billing/invoice/{source}/{inv_id}/pdf"
        return render_template('dashboard/invoice_print.html',
                               user=user, inv=inv, inv_num=inv_num, pdf_url=pdf_url)

    @app.route("/<lang_code>/dashboard/billing/invoice/<source>/<int:inv_id>/pdf")
    @app.route('/dashboard/billing/invoice/<source>/<int:inv_id>/pdf')
    @login_required_user
    def dashboard_billing_invoice_pdf(source, inv_id):
        """Generate and stream a PDF invoice for download."""
        user = get_current_user()
        inv = _fetch_invoice(user['id'], source, inv_id)
        if not inv:
            abort(404)

        try:
            from reportlab.lib.pagesizes import A4
            from reportlab.lib import colors
            from reportlab.lib.units import cm
            from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, HRFlowable
            from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
            from reportlab.lib.enums import TA_RIGHT, TA_CENTER, TA_LEFT

            _pdf_settings = get_site_settings()
            _pdf_site_name = (_pdf_settings.get('logo_text') or _pdf_settings.get('site_name') or 'Company').strip()
            _pdf_site_domain = request.host

            buf = io.BytesIO()
            doc = SimpleDocTemplate(buf, pagesize=A4,
                                    rightMargin=2*cm, leftMargin=2*cm,
                                    topMargin=2*cm, bottomMargin=2*cm)

            styles = getSampleStyleSheet()
            accent = colors.HexColor('#00796B')
            dark = colors.HexColor('#111827')
            muted = colors.HexColor('#6b7280')

            title_style = ParagraphStyle('Title', parent=styles['Normal'],
                                         fontSize=28, textColor=accent, fontName='Helvetica-Bold',
                                         spaceAfter=2)
            subtitle_style = ParagraphStyle('Sub', parent=styles['Normal'],
                                            fontSize=10, textColor=muted)
            heading_style = ParagraphStyle('Heading', parent=styles['Normal'],
                                           fontSize=10, textColor=muted, fontName='Helvetica-Bold',
                                           spaceBefore=12, spaceAfter=4)
            body_style = ParagraphStyle('Body', parent=styles['Normal'],
                                        fontSize=10, textColor=dark)
            right_style = ParagraphStyle('Right', parent=styles['Normal'],
                                         fontSize=10, textColor=dark, alignment=TA_RIGHT)
            big_amount_style = ParagraphStyle('BigAmt', parent=styles['Normal'],
                                              fontSize=22, textColor=accent, fontName='Helvetica-Bold',
                                              alignment=TA_RIGHT)

            inv_date = (inv.get('created_at') or '')[:10] or 'N/A'
            inv_num = f"INV-{source.upper()[:1]}{inv_id:06d}"
            amount = inv.get('display_amount', 0)
            currency = (inv.get('currency') or 'USD').upper()
            description = inv.get('description') or 'Subscription payment'
            status = (inv.get('status') or 'paid').upper()
            method = inv.get('method', 'Online (Stripe)' if source == 'stripe' else 'Manual')

            story = []

            # Header row: company name left, INVOICE right
            header_data = [
                [Paragraph(f'<b>{_pdf_site_name}</b>', ParagraphStyle('Co', parent=styles['Normal'],
                           fontSize=18, textColor=dark, fontName='Helvetica-Bold')),
                 Paragraph('INVOICE', ParagraphStyle('Inv', parent=styles['Normal'],
                           fontSize=28, textColor=accent, fontName='Helvetica-Bold', alignment=TA_RIGHT))],
            ]
            header_table = Table(header_data, colWidths=['60%', '40%'])
            header_table.setStyle(TableStyle([('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
                                              ('BOTTOMPADDING', (0, 0), (-1, -1), 0)]))
            story.append(header_table)
            story.append(Spacer(1, 0.3*cm))
            story.append(HRFlowable(width='100%', thickness=2, color=accent, spaceAfter=0.4*cm))

            # Invoice meta: number + date
            meta_data = [
                [Paragraph(f'<b>Invoice #</b> {inv_num}', body_style),
                 Paragraph(f'<b>Date:</b> {inv_date}', right_style)],
                [Paragraph(f'<b>Status:</b> <font color="#16a34a">{status}</font>' if status == 'PAID'
                           else f'<b>Status:</b> {status}', body_style),
                 Paragraph(f'<b>Method:</b> {method}', right_style)],
            ]
            meta_table = Table(meta_data, colWidths=['50%', '50%'])
            meta_table.setStyle(TableStyle([('BOTTOMPADDING', (0, 0), (-1, -1), 4),
                                            ('TOPPADDING', (0, 0), (-1, -1), 2)]))
            story.append(meta_table)
            story.append(Spacer(1, 0.4*cm))
            story.append(HRFlowable(width='100%', thickness=0.5, color=colors.HexColor('#e5e7eb'), spaceAfter=0.4*cm))

            # Billed to
            story.append(Paragraph('BILLED TO', heading_style))
            story.append(Paragraph(f"<b>{user.get('display_name') or user.get('username', '')}</b>", body_style))
            story.append(Paragraph(user.get('email', ''), body_style))
            story.append(Spacer(1, 0.5*cm))

            # Line items table
            story.append(Paragraph('ITEMS', heading_style))
            items_data = [
                [Paragraph('<b>Description</b>', ParagraphStyle('Th', parent=styles['Normal'],
                           fontSize=9, textColor=muted, fontName='Helvetica-Bold')),
                 Paragraph('<b>Amount</b>', ParagraphStyle('ThR', parent=styles['Normal'],
                           fontSize=9, textColor=muted, fontName='Helvetica-Bold', alignment=TA_RIGHT))],
                [Paragraph(description, body_style),
                 Paragraph(f'<b>${amount:.2f} {currency}</b>',
                           ParagraphStyle('AmtR', parent=styles['Normal'],
                                          fontSize=10, textColor=dark, fontName='Helvetica-Bold', alignment=TA_RIGHT))],
            ]
            items_table = Table(items_data, colWidths=['75%', '25%'])
            items_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#f9fafb')),
                ('ROWBACKGROUND', (0, 1), (-1, -1), [colors.white]),
                ('GRID', (0, 0), (-1, -1), 0.5, colors.HexColor('#e5e7eb')),
                ('TOPPADDING', (0, 0), (-1, -1), 8),
                ('BOTTOMPADDING', (0, 0), (-1, -1), 8),
                ('LEFTPADDING', (0, 0), (-1, -1), 10),
                ('RIGHTPADDING', (0, 0), (-1, -1), 10),
            ]))
            story.append(items_table)
            story.append(Spacer(1, 0.3*cm))

            # Total
            total_data = [
                [Paragraph('<b>TOTAL</b>', ParagraphStyle('TotLabel', parent=styles['Normal'],
                           fontSize=11, textColor=dark, fontName='Helvetica-Bold')),
                 Paragraph(f'<b>${amount:.2f} {currency}</b>',
                           ParagraphStyle('TotAmt', parent=styles['Normal'],
                                          fontSize=14, textColor=accent, fontName='Helvetica-Bold', alignment=TA_RIGHT))],
            ]
            total_table = Table(total_data, colWidths=['75%', '25%'])
            total_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, -1), colors.HexColor('#f5f3ff')),
                ('TOPPADDING', (0, 0), (-1, -1), 10),
                ('BOTTOMPADDING', (0, 0), (-1, -1), 10),
                ('LEFTPADDING', (0, 0), (-1, -1), 10),
                ('RIGHTPADDING', (0, 0), (-1, -1), 10),
                ('BOX', (0, 0), (-1, -1), 1, accent),
            ]))
            story.append(total_table)

            # Footer
            story.append(Spacer(1, 1*cm))
            story.append(HRFlowable(width='100%', thickness=0.5, color=colors.HexColor('#e5e7eb'), spaceAfter=0.3*cm))
            story.append(Paragraph('Thank you for your business!',
                                   ParagraphStyle('Footer', parent=styles['Normal'],
                                                  fontSize=9, textColor=muted, alignment=TA_CENTER)))
            story.append(Paragraph(_pdf_site_domain,
                                   ParagraphStyle('FooterDomain', parent=styles['Normal'],
                                                  fontSize=8, textColor=colors.HexColor('#9ca3af'), alignment=TA_CENTER)))

            doc.build(story)
            buf.seek(0)
            fname = f"invoice-{inv_num}.pdf"
            return send_file(buf, as_attachment=True, download_name=fname, mimetype='application/pdf')

        except ImportError:
            abort(503, description="PDF generation library not available.")

    @app.route('/dashboard/billing/cancel', methods=['POST'])
    @login_required_user
    def dashboard_billing_cancel():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        if not plan or plan.get('price_monthly', 0) == 0:
            flash("No active paid subscription to cancel.", "error")
            return redirect(url_for('dashboard_billing'))
        # Cancel Stripe subscription at period end (not immediately)
        stripe_sub_id = user.get('stripe_subscription_id', '')
        if stripe_sub_id:
            try:
                import stripe as _stripe
                stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
                if stripe_secret:
                    _stripe.api_key = stripe_secret
                    _stripe.Subscription.modify(stripe_sub_id, cancel_at_period_end=True)
            except Exception:
                pass
        with _db() as c:
            c.execute(
                "UPDATE users SET subscription_status='cancelled', auto_renew=0 WHERE id=?",
                (user['id'],)
            )
            c.commit()
        log_event('subscription_cancelled', f"User cancelled subscription: {user.get('username','')}", user_id=user['id'])
        flash("Your subscription has been cancelled. You'll keep access until the end of your billing period.", "success")
        return redirect(url_for('dashboard_billing'))

    @app.route('/dashboard/billing/toggle-auto-renew', methods=['POST'])
    @login_required_user
    def dashboard_billing_toggle_auto_renew():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        if not plan or plan.get('price_monthly', 0) == 0:
            flash("Auto-renew is only available for paid subscriptions.", "error")
            return redirect(url_for('dashboard_billing'))
        new_auto_renew = 1 if request.form.get('auto_renew') == '1' else 0
        stripe_sub_id = user.get('stripe_subscription_id', '')
        if stripe_sub_id:
            try:
                import stripe as _stripe
                stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
                if stripe_secret:
                    _stripe.api_key = stripe_secret
                    # cancel_at_period_end=True means no auto-renew, False means auto-renew
                    _stripe.Subscription.modify(stripe_sub_id, cancel_at_period_end=(new_auto_renew == 0))
            except Exception:
                pass
        new_status = user.get('subscription_status', 'active')
        if new_auto_renew == 0 and new_status == 'active':
            new_status = 'cancelled'
        elif new_auto_renew == 1 and new_status in ('cancelled', 'expired'):
            new_status = 'active'
        with _db() as c:
            c.execute(
                "UPDATE users SET auto_renew=?, subscription_status=? WHERE id=?",
                (new_auto_renew, new_status, user['id'])
            )
            c.commit()
        log_event('auto_renew_changed', f"User {'enabled' if new_auto_renew else 'disabled'} auto-renew", user_id=user['id'])
        if new_auto_renew:
            flash("Auto-renew has been enabled. Your subscription will renew automatically.", "success")
        else:
            flash("Auto-renew disabled. Your subscription will not renew after the current period.", "info")
        return redirect(url_for('dashboard_billing'))

    # ─────────────────────────────────────────────────────────────────────────
    # API KEYS DASHBOARD
    # ─────────────────────────────────────────────────────────────────────────
    @app.route("/<lang_code>/dashboard/api-keys")
    @app.route('/dashboard/api-keys')
    @login_required_user
    def dashboard_api_keys():
        import json as _json
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        new_key = session.pop('_new_api_key', None)
        with _db() as c:
            keys = c.execute(
                "SELECT * FROM user_api_keys WHERE user_id=? ORDER BY created_at DESC",
                (user['id'],)
            ).fetchall()
            keys = [dict(k) for k in keys]
            today_usage = c.execute(
                "SELECT COUNT(*) FROM api_usage_log WHERE user_id=? AND date(created_at)=date('now')",
                (user['id'],)
            ).fetchone()[0]
        for k in keys:
            try:
                k['allowed_ips'] = _json.loads(k.get('allowed_ips') or '[]')
            except Exception:
                k['allowed_ips'] = []
            try:
                k['allowed_domains'] = _json.loads(k.get('allowed_domains') or '[]')
            except Exception:
                k['allowed_domains'] = []
            k['display_prefix'] = k.get('key_prefix') or (k.get('api_key') or '')[:16]
        api_enabled = bool(plan and plan.get('api_enabled'))
        daily_limit = int(plan.get('api_calls_per_day', 0)) if plan else 0
        return render_template('dashboard/api_keys.html',
                               user=user, plan=plan, keys=keys,
                               api_enabled=api_enabled,
                               today_usage=today_usage,
                               daily_limit=daily_limit,
                               new_key=new_key,
                               active_page='api_keys')

    @app.route('/dashboard/api-keys/create', methods=['POST'])
    @login_required_user
    def dashboard_api_keys_create():
        import secrets as _sec
        import json as _json
        import hashlib as _hashlib
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        if not plan or not plan.get('api_enabled'):
            flash("API access is not available on your plan.", "error")
            return redirect(url_for('dashboard_api_keys'))
        name = request.form.get('name', '').strip() or 'My API Key'
        restriction_type = request.form.get('restriction_type', 'all')
        allowed_ips_raw = request.form.get('allowed_ips', '').strip()
        allowed_domains_raw = request.form.get('allowed_domains', '').strip()
        allowed_ips = _json.dumps([ip.strip() for ip in allowed_ips_raw.splitlines() if ip.strip()])
        allowed_domains = _json.dumps([d.strip() for d in allowed_domains_raw.splitlines() if d.strip()])
        full_key = 'sk_live_' + _sec.token_urlsafe(32)
        key_hash = _hashlib.sha256(full_key.encode()).hexdigest()
        key_prefix = full_key[:16]
        with _db() as c:
            existing = c.execute("SELECT COUNT(*) FROM user_api_keys WHERE user_id=?", (user['id'],)).fetchone()[0]
            if existing >= 10:
                flash("You can have at most 10 API keys.", "error")
                return redirect(url_for('dashboard_api_keys'))
            c.execute(
                "INSERT INTO user_api_keys (user_id, name, api_key, key_hash, key_prefix, restriction_type, allowed_ips, allowed_domains) VALUES (?,?,?,?,?,?,?,?)",
                (user['id'], name, key_prefix, key_hash, key_prefix, restriction_type, allowed_ips, allowed_domains)
            )
            c.commit()
        session['_new_api_key'] = full_key
        return redirect(url_for('dashboard_api_keys'))

    @app.route('/dashboard/api-keys/<int:key_id>/revoke', methods=['POST'])
    @login_required_user
    def dashboard_api_keys_revoke(key_id):
        user = get_current_user()
        with _db() as c:
            c.execute(
                "UPDATE user_api_keys SET is_active=0 WHERE id=? AND user_id=?",
                (key_id, user['id'])
            )
            c.commit()
        flash("API key revoked.", "success")
        return redirect(url_for('dashboard_api_keys'))

    @app.route('/dashboard/api-keys/<int:key_id>/delete', methods=['POST'])
    @login_required_user
    def dashboard_api_keys_delete(key_id):
        user = get_current_user()
        with _db() as c:
            c.execute("DELETE FROM user_api_keys WHERE id=? AND user_id=?", (key_id, user['id']))
            c.commit()
        flash("API key deleted.", "success")
        return redirect(url_for('dashboard_api_keys'))

    # ─────────────────────────────────────────────────────────────────────────
    # SECURITY & SESSIONS PAGE
    # ─────────────────────────────────────────────────────────────────────────
    def _get_device_type(ua_string):
        ua = ua_string.lower()
        if any(x in ua for x in ['mobile', 'android', 'iphone', 'ipad']):
            return 'mobile'
        if 'tablet' in ua:
            return 'tablet'
        return 'desktop'

    def _ensure_session_record(user_id):
        import secrets as _sec
        token = session.get('_sec_token')
        if not token:
            token = _sec.token_hex(24)
            session['_sec_token'] = token
        ip = request.headers.get('X-Forwarded-For', request.remote_addr or '').split(',')[0].strip()
        ua = request.headers.get('User-Agent', '')
        device = _get_device_type(ua)
        with _db() as c:
            existing = c.execute("SELECT id FROM user_sessions WHERE session_token=?", (token,)).fetchone()
            if existing:
                c.execute(
                    "UPDATE user_sessions SET last_seen_at=datetime('now'), is_current=1 WHERE session_token=?",
                    (token,)
                )
            else:
                c.execute(
                    "UPDATE user_sessions SET is_current=0 WHERE user_id=?",
                    (user_id,)
                )
                c.execute(
                    "INSERT INTO user_sessions (user_id, session_token, ip_address, user_agent, device_type, is_current) VALUES (?,?,?,?,?,1)",
                    (user_id, token, ip, ua[:512], device)
                )
            c.commit()
        return token

    @app.route("/<lang_code>/dashboard/security")
    @app.route('/dashboard/security')
    @login_required_user
    def dashboard_security():
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        _ensure_session_record(user['id'])
        current_token = session.get('_sec_token')
        with _db() as c:
            sessions = c.execute(
                "SELECT * FROM user_sessions WHERE user_id=? AND revoked=0 ORDER BY last_seen_at DESC LIMIT 20",
                (user['id'],)
            ).fetchall()
            sessions = [dict(s) for s in sessions]
            events = c.execute(
                "SELECT * FROM security_events WHERE user_id=? ORDER BY created_at DESC LIMIT 30",
                (user['id'],)
            ).fetchall()
            events = [dict(e) for e in events]
        for s in sessions:
            s['is_current'] = (s.get('session_token') == current_token)
        return render_template('dashboard/security.html',
                               user=user, plan=plan,
                               sessions=sessions, events=events,
                               active_page='security')

    @app.route('/dashboard/security/sessions/<int:session_id>/revoke', methods=['POST'])
    @app.route('/dashboard/security/revoke-session/<int:session_id>', methods=['POST'])
    @login_required_user
    def dashboard_security_revoke_session(session_id):
        user = get_current_user()
        with _db() as c:
            row = c.execute(
                "SELECT * FROM user_sessions WHERE id=? AND user_id=?",
                (session_id, user['id'])
            ).fetchone()
            if row and not dict(row).get('is_current'):
                c.execute("UPDATE user_sessions SET revoked=1 WHERE id=? AND user_id=?", (session_id, user['id']))
                c.commit()
        flash("Session revoked.", "success")
        return redirect(url_for('dashboard_security'))

    @app.route('/dashboard/security/revoke-all', methods=['POST'])
    @login_required_user
    def dashboard_security_revoke_all():
        user = get_current_user()
        current_token = session.get('_sec_token')
        with _db() as c:
            if current_token:
                c.execute(
                    "UPDATE user_sessions SET revoked=1 WHERE user_id=? AND session_token!=?",
                    (user['id'], current_token)
                )
            else:
                c.execute("UPDATE user_sessions SET revoked=1 WHERE user_id=?", (user['id'],))
            c.commit()
        flash("All other sessions have been revoked.", "success")
        return redirect(url_for('dashboard_security'))

    @app.route('/dashboard/security/change-password', methods=['POST'])
    @login_required_user
    def dashboard_security_change_password():
        from werkzeug.security import check_password_hash as _chk, generate_password_hash as _gph
        user = get_current_user()
        plan = get_plan_by_id(user['plan_id'])
        current_pw = request.form.get('current_password', '')
        new_pw = request.form.get('new_password', '')
        confirm_pw = request.form.get('confirm_password', '')
        with _db() as c:
            row = c.execute("SELECT password_hash FROM users WHERE id=?", (user['id'],)).fetchone()
        pw_hash = dict(row)['password_hash'] if row else ''
        if not _chk(pw_hash, current_pw):
            flash("Current password is incorrect.", "error")
            return redirect(url_for('dashboard_security'))
        if len(new_pw) < 8:
            flash("New password must be at least 8 characters.", "error")
            return redirect(url_for('dashboard_security'))
        if new_pw != confirm_pw:
            flash("New passwords do not match.", "error")
            return redirect(url_for('dashboard_security'))
        new_hash = _gph(new_pw)
        with _db() as c:
            c.execute("UPDATE users SET password_hash=? WHERE id=?", (new_hash, user['id']))
            ip = request.headers.get('X-Forwarded-For', request.remote_addr or '').split(',')[0].strip()
            ua = request.headers.get('User-Agent', '')
            c.execute(
                "INSERT INTO security_events (user_id, event_type, description, ip_address, user_agent) VALUES (?,?,?,?,?)",
                (user['id'], 'password_changed', 'Password changed successfully', ip, ua[:512])
            )
            c.commit()
        flash("Password changed successfully.", "success")
        return redirect(url_for('dashboard_security'))