import os, sqlite3, json, time, re
from datetime import datetime
from dotenv import load_dotenv
from flask import request, render_template, redirect, url_for, jsonify, session, flash, abort, g, send_from_directory, send_file, Response, make_response

# ── Module-level broadcast helpers (accessible from app.py for APScheduler) ──

def _make_tracking_token_ml(broadcast_id, user_id):
    """Token = '<user_id_hex>.<8-char HMAC>'.

    Secret resolution order:
      1. SECRET_KEY env var
      2. broadcast_secret_key in site_settings (Admin → General Settings)
      3. Flask app.secret_key (always set)
    """
    import hmac, hashlib
    secret = os.getenv('SECRET_KEY', '').strip()
    if not secret:
        try:
            from helpers import get_site_settings as _gss
            secret = (_gss().get('broadcast_secret_key') or '').strip()
        except Exception:
            pass
    if not secret:
        try:
            from flask import current_app
            secret = current_app.secret_key or ''
            if isinstance(secret, bytes):
                secret = secret.decode('utf-8', errors='replace')
        except Exception:
            pass
    if not secret:
        raise RuntimeError("Set a Broadcast Secret Key in Admin → General Settings → Broadcast")
    uid_hex = format(user_id, 'x')
    msg = f"{broadcast_id}:{user_id}".encode()
    sig = hmac.new(secret.encode(), msg, hashlib.sha256).hexdigest()[:8]
    return f"{uid_hex}.{sig}"


def _substitute_vars_ml(template_str, user, site_name):
    """Substitute per-recipient template variables."""
    replacements = {
        '{{username}}': user.get('username', ''),
        '{{email}}': user.get('email', ''),
        '{{plan_name}}': user.get('plan_name', ''),
        '{{country}}': user.get('country', ''),
        '{{joined_date}}': (user.get('joined_date') or '')[:10],
        '{{subscription_status}}': user.get('subscription_status', ''),
        '{{site_name}}': site_name,
    }
    result = template_str
    for k, v in replacements.items():
        result = result.replace(k, v or '')
    return result


def _execute_broadcast(broadcast_id):
    """Execute a broadcast (sends emails/push) given an existing broadcast_sends row.

    Called both immediately (from POST handler) and by APScheduler for scheduled sends.
    Reads all broadcast data from broadcast_sends + broadcast_recipients rows.
    Updates status → 'sent' and populates actual sent counts.
    """
    from helpers import _db, send_email, get_site_settings, log_event
    _allowed_domain = os.getenv('website_url', 'localhost')
    _track_base = f"https://{_allowed_domain}"

    site_settings = get_site_settings()
    site_name = site_settings.get('site_name', 'OnlineConvert')

    with _db() as c:
        send_row = c.execute(
            "SELECT * FROM broadcast_sends WHERE id=?", (broadcast_id,)
        ).fetchone()
        if not send_row:
            return
        send_row = dict(send_row)

        if send_row['status'] not in ('scheduled', 'pending'):
            return

        recipient_rows_db = c.execute(
            "SELECT user_id FROM broadcast_recipients WHERE broadcast_id=?",
            (broadcast_id,)
        ).fetchall()
        user_ids = [r['user_id'] for r in recipient_rows_db]

    if not user_ids:
        with _db() as c:
            c.execute("UPDATE broadcast_sends SET status='sent' WHERE id=?", (broadcast_id,))
            c.commit()
        return

    channel_label = send_row['channel']
    do_email = 'email' in channel_label
    do_push = 'push' in channel_label
    email_subject = send_row['email_subject']
    email_body = send_row['email_body']
    push_title = send_row['push_title']
    push_body_tpl = send_row['push_body']
    push_icon = (send_row.get('push_icon') or '').strip()
    push_image = (send_row.get('push_image') or '').strip()
    push_url_custom = (send_row.get('push_url') or '').strip()

    with _db() as c:
        placeholders = ','.join('?' * len(user_ids))
        users_rows = c.execute(
            f"""SELECT u.id, u.username, u.email, u.country,
                       u.subscription_status, u.created_at AS joined_date,
                       p.name AS plan_name
                FROM users u LEFT JOIN plans p ON p.id=u.plan_id
                WHERE u.id IN ({placeholders})""",
            user_ids
        ).fetchall()
        users_map = {r['id']: dict(r) for r in users_rows}

        push_subs_rows = c.execute(
            f"""SELECT user_id, endpoint, p256dh, auth
                FROM user_push_subscriptions
                WHERE user_id IN ({placeholders}) AND user_id IS NOT NULL AND active=1""",
            user_ids
        ).fetchall()
    from collections import defaultdict
    push_subs_map = defaultdict(list)
    for ps in push_subs_rows:
        push_subs_map[ps['user_id']].append(dict(ps))

    emails_sent_count = 0
    push_sent_count = 0
    recipient_results = []

    for uid in user_ids:
        user = users_map.get(uid)
        if not user:
            recipient_results.append((uid, 0, 0))
            continue
        e_sent = 0
        p_sent = 0
        _token = _make_tracking_token_ml(broadcast_id, uid)

        if do_email and email_subject and email_body:
            subj = _substitute_vars_ml(email_subject, user, site_name)
            body_html = _substitute_vars_ml(email_body, user, site_name)
            pixel_url = f"{_track_base}/t/eo/{broadcast_id}/{_token}"
            body_html += f'<img src="{pixel_url}" width="1" height="1" style="display:none;" alt="">'
            try:
                ok, _err_msg = send_email(user['email'], subj, body_html, None)
                if ok:
                    e_sent = 1
                    emails_sent_count += 1
                else:
                    import logging as _logging
                    _logging.getLogger(__name__).warning(
                        "Broadcast %s email not sent for user %s: %s", broadcast_id, uid, _err_msg
                    )
            except Exception as _email_err:
                import logging as _logging
                _logging.getLogger(__name__).error(
                    "Broadcast %s email failed for user %s: %s", broadcast_id, uid, _email_err
                )

        if do_push and push_title and push_body_tpl:
            p_title = _substitute_vars_ml(push_title, user, site_name)
            p_body = _substitute_vars_ml(push_body_tpl, user, site_name)
            _site_url = _allowed_domain if _allowed_domain.startswith('http') else f"https://{_allowed_domain}"
            # Tracking beacon (returns 204) fired silently by the service worker
            _click_beacon = f"{_track_base}/t/pc/{broadcast_id}/{_token}"
            # Destination opened in the browser when the user taps the notification
            _dest_url = push_url_custom or _site_url
            from helpers import send_push_notification as _send_push
            _expired_endpoints = []
            for sub in push_subs_map.get(uid, []):
                _result = _send_push(
                    endpoint=sub['endpoint'],
                    p256dh=sub['p256dh'],
                    auth=sub['auth'],
                    title=p_title,
                    body=p_body,
                    url=_dest_url,
                    icon=push_icon,
                    image=push_image,
                    click_beacon=_click_beacon,
                )
                if _result is True:
                    p_sent = 1
                elif _result == 'gone':
                    _expired_endpoints.append(sub['endpoint'])
            if _expired_endpoints:
                try:
                    with _db() as _c2:
                        for _ep in _expired_endpoints:
                            _c2.execute(
                                "UPDATE user_push_subscriptions SET active=0 WHERE endpoint=?",
                                (_ep,)
                            )
                        _c2.commit()
                except Exception:
                    pass
            if p_sent:
                push_sent_count += 1

        recipient_results.append((uid, e_sent, p_sent))

    # ── Also send to anonymous (not logged-in) push subscribers ──────────────
    anon_push_sent_count = 0
    if do_push and push_title and push_body_tpl:
        from helpers import send_push_notification as _send_push
        _site_url = _allowed_domain if _allowed_domain.startswith('http') else f"https://{_allowed_domain}"
        _dest_url = push_url_custom or _site_url
        _anon_expired = []
        try:
            with _db() as _ca:
                anon_subs = _ca.execute(
                    "SELECT endpoint, p256dh, auth FROM user_push_subscriptions WHERE user_id IS NULL AND active=1"
                ).fetchall()
        except Exception:
            anon_subs = []
        for _asub in anon_subs:
            try:
                _r = _send_push(
                    endpoint=_asub['endpoint'],
                    p256dh=_asub['p256dh'],
                    auth=_asub['auth'],
                    title=push_title,
                    body=push_body_tpl,
                    url=_dest_url,
                    icon=push_icon,
                    image=push_image,
                )
                if _r is True:
                    anon_push_sent_count += 1
                elif _r == 'gone':
                    _anon_expired.append(_asub['endpoint'])
            except Exception:
                pass
        if _anon_expired:
            try:
                with _db() as _c3:
                    for _ep in _anon_expired:
                        _c3.execute("UPDATE user_push_subscriptions SET active=0 WHERE endpoint=?", (_ep,))
                    _c3.commit()
            except Exception:
                pass

    _sent_at = datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%S')
    with _db() as c:
        c.execute(
            """UPDATE broadcast_sends
               SET status='sent', emails_sent=?, push_sent=?, anon_push_sent=?,
                   total_recipients=?, sent_at=? WHERE id=?""",
            (emails_sent_count, push_sent_count, anon_push_sent_count,
             len(recipient_results), _sent_at, broadcast_id)
        )
        for uid, e_sent, p_sent in recipient_results:
            c.execute(
                "UPDATE broadcast_recipients SET email_sent=?, push_sent=? WHERE broadcast_id=? AND user_id=?",
                (e_sent, p_sent, broadcast_id, uid)
            )
        c.commit()

    log_event('admin_broadcast', f"Broadcast {broadcast_id} sent: {channel_label}, {len(recipient_results)} recipients, {emails_sent_count} emails, {push_sent_count} push")

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

from werkzeug.utils import secure_filename
from werkzeug.security import generate_password_hash, check_password_hash
from helpers import (authenticate, is_admin, is_editor, get_editor_info, get_editor_langs,
                     url, _db, get_site_settings, set_site_setting, get_admin_profile, update_admin_profile,
                     get_text_overrides, clear_text_overrides_cache, get_page_content, get_page_all_fields, set_page_content,
                     get_page_faqs, save_page_faqs, normalize_page_id, STATIC_PAGES, PAGE_FIELDS, _collect_translatable_strings,
                     get_all_plans, get_plan_by_id, get_plan_periods, get_period_by_id, calc_period_price, _seed_billing_periods_for_plan,
                     get_all_users_admin, get_all_file_uploads_admin, admin_delete_file_upload, admin_delete_all_file_uploads,
                     get_all_conversions_admin, get_reviews, get_review_summary, add_review, delete_review, toggle_review_approval,
                     get_all_reviews, get_reviews_count, send_email, send_user_notification, log_event, log_deletion, _get_storage_stats, _fmt_bytes,
                     get_payment_setting, get_user_by_referral_code, ensure_referral_code, get_user_referrals, get_user_rewards,
                     grant_referral_reward, _page_content_cache_time, _text_overrides_cache_time, dir_path,
                     get_all_languages_with_status, get_active_language_codes, save_active_languages, _invalidate_active_lang_cache,
                     get_all_editors, get_editor_by_id, create_editor, update_editor, delete_editor, verify_editor_credentials,
                     _KNOWN_LANGUAGES, load_server_settings)

import helpers as _helpers_module

ALLOWED_DOMAIN = website_url

def _is_allowed_origin(origin):
    if not origin:
        return False
    try:
        from urllib.parse import urlparse
        parsed = urlparse(origin)
        host = parsed.hostname or ''
        return host == ALLOWED_DOMAIN or host.endswith('.' + ALLOWED_DOMAIN)
    except Exception:
        return False


def _admin_login_handler():
    from flask_babel import gettext as _
    if request.method == "GET":
        error = session.pop('error', None)
        return render_template("admin/login.html", error=error)
    else:
        username = request.form.get("username", "").strip()
        password = request.form.get("password", "")

        # ── 1. Try super-admin credentials first ──────────────────────────
        profile = get_admin_profile()
        password_valid = False
        if username == profile['username']:
            if profile['password_hash']:
                password_valid = check_password_hash(profile['password_hash'], password)
            else:
                password_valid = (password == os.getenv("admin_password", "admin123"))
        elif username == os.getenv("admin_username") and password == os.getenv("admin_password"):
            password_valid = True

        if password_valid:
            bypass_2fa = os.getenv("ADMIN_BYPASS_2FA", "0").strip() == "1"
            if not bypass_2fa and profile['totp_enabled'] and profile['totp_secret']:
                # Clear any stale sub-admin pending state before entering main-admin 2FA flow
                session.pop('pending_sub_admin_id', None)
                session['pending_2fa'] = True
                session['pending_user'] = username
                return redirect(url("/admin/verify-2fa"))
            session.clear()
            session['login'] = True
            return redirect(url("/admin"))

        # ── 2. Try sub-admin credentials (admin_accounts table) ───────────
        with _db() as _c:
            sub_admin = _c.execute(
                "SELECT * FROM admin_accounts WHERE username=? AND is_active=1",
                (username,)
            ).fetchone()
        if sub_admin and check_password_hash(sub_admin['password_hash'], password):
            sub_admin = dict(sub_admin)
            if sub_admin.get('totp_enabled') and sub_admin.get('totp_secret'):
                # Clear any stale main-admin pending state before sub-admin 2FA flow
                session.pop('pending_user', None)
                session['pending_2fa'] = True
                session['pending_user'] = username
                session['pending_sub_admin_id'] = sub_admin['id']
                return redirect(url("/admin/verify-2fa"))
            session.clear()
            session['login'] = True
            session['is_sub_admin'] = True
            session['sub_admin_id'] = sub_admin['id']
            return redirect(url("/admin"))

        # ── 3. Try editor credentials ─────────────────────────────────────
        editor = verify_editor_credentials(username, password)
        if editor:
            session.clear()
            session['editor_login'] = True
            session['editor_id'] = editor['id']
            session['editor_langs'] = json.dumps(editor['allowed_languages'])
            return redirect(url("/admin/pages"))

        # ── 4. Bad credentials ─────────────────────────────────────────────
        session['error'] = _("username or password is wrong")
        settings = get_site_settings()
        custom_slug = (settings.get('admin_login_slug', '').strip().strip('/') if settings.get('admin_login_slug', '').strip().strip('/') != 'admin/blog' else '')
        if custom_slug:
            return redirect('/' + custom_slug)
        return redirect(url("/admin/login"))


def register_admin_routes(app):
    import pyotp
    import qrcode
    import io
    import base64
    import requests
    import subprocess
    import threading
    import sys
    import re as _re_module

    from configs.filetypes import available_filetypes, available_hashtypes
    from configs.definition import definitions
    from configs.languages import supported_languages

    dir_path = os.path.dirname(os.path.dirname(os.path.realpath(__file__)))

    def url(path):
        """Return a redirect-friendly path string (drop-in for url_for when a literal path is needed)."""
        return path

    # ── Editor access control ──────────────────────────────────────────────────
    # Editors may only access pages/edit, text-overrides, login, and logout.
    # All other admin routes are restricted to the super-admin.
    _EDITOR_ALLOWED_PREFIXES = (
        '/admin/pages',
        '/admin/text-overrides',
        '/admin/logout',
        '/admin/login',
    )

    @app.before_request
    def _editor_access_guard():
        if not session.get('editor_login') or session.get('login'):
            return  # not an editor, or super-admin — allow through normally
        path = request.path
        # Strip optional language prefix (/en/, /fr/, etc.)
        path_norm = _re_module.sub(r'^/[a-z]{2}(?=/|$)', '', path) or path
        if not any(path_norm.startswith(p) or path.startswith(p)
                   for p in _EDITOR_ALLOWED_PREFIXES):
            return redirect(url('/admin/pages'))

    # ===== HELPER FUNCTION FOR SITEMAP REGENERATION =====
    def regenerate_sitemaps_async():
        """Trigger sitemap regeneration in background"""
        try:
            def regenerate_sitemaps():
                """Run sitemap generator in background"""
                script_path = os.path.join(dir_path, 'scripts', 'generate_sitemaps.py')
                try:
                    subprocess.Popen([sys.executable, script_path])
                    app.logger.info("Sitemap regeneration triggered successfully")
                except Exception as e:
                    app.logger.error(f"Failed to regenerate sitemaps: {e}")
            
            # Start regeneration in background thread
            threading.Thread(target=regenerate_sitemaps).start()
            
        except Exception as e:
            app.logger.error(f"Error setting up sitemap regeneration: {e}")

    @app.route("/<lang_code>/admin")
    @app.route('/admin')
    def admin_home():
        return redirect(url("/admin/dashboard"))

    @app.route('/admin/dashboard')
    def admin_dashboard():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            total_users = c.execute("SELECT COUNT(*) FROM users").fetchone()[0]
            paid_users = c.execute(
                "SELECT COUNT(*) FROM users u JOIN plans p ON p.id=u.plan_id WHERE p.price_monthly > 0"
            ).fetchone()[0]
            free_users = total_users - paid_users
            new_today = c.execute(
                "SELECT COUNT(*) FROM users WHERE date(created_at)=date('now')"
            ).fetchone()[0]
            new_7d = c.execute(
                "SELECT COUNT(*) FROM users WHERE created_at >= datetime('now','-7 days')"
            ).fetchone()[0]
            new_30d = c.execute(
                "SELECT COUNT(*) FROM users WHERE created_at >= datetime('now','-30 days')"
            ).fetchone()[0]
            total_conversions = c.execute("SELECT COUNT(*) FROM conversions").fetchone()[0]
            conversions_today = c.execute(
                "SELECT COUNT(*) FROM conversions WHERE date(created_at)=date('now')"
            ).fetchone()[0]
            conversions_7d = c.execute(
                "SELECT COUNT(*) FROM conversions WHERE created_at >= datetime('now','-7 days')"
            ).fetchone()[0]
            try:
                total_revenue = c.execute(
                    "SELECT COALESCE(SUM(amount),0) FROM payment_history WHERE status='paid'"
                ).fetchone()[0]
                revenue_30d = c.execute(
                    "SELECT COALESCE(SUM(amount),0) FROM payment_history WHERE status='paid' AND created_at >= datetime('now','-30 days')"
                ).fetchone()[0]
            except Exception:
                total_revenue = 0
                revenue_30d = 0
            recent_users = c.execute(
                "SELECT u.id, u.username, u.email, u.created_at, p.name AS plan_name "
                "FROM users u LEFT JOIN plans p ON p.id=u.plan_id "
                "ORDER BY u.id DESC LIMIT 10"
            ).fetchall()
            recent_users = [dict(r) for r in recent_users]
            # Daily signup chart for last 30 days
            signup_chart = []
            for i in range(29, -1, -1):
                row_d = c.execute(
                    "SELECT COUNT(*) FROM users WHERE date(created_at)=date('now', ? || ' days')",
                    (f"-{i}",)
                ).fetchone()
                signup_chart.append(row_d[0] if row_d else 0)
            # Daily conversion chart for last 30 days
            conversion_chart = []
            for i in range(29, -1, -1):
                row_c = c.execute(
                    "SELECT COUNT(*) FROM conversions WHERE date(created_at)=date('now', ? || ' days')",
                    (f"-{i}",)
                ).fetchone()
                conversion_chart.append(row_c[0] if row_c else 0)
            # Top formats
            try:
                top_formats = c.execute(
                    "SELECT output_format, COUNT(*) AS cnt FROM conversions "
                    "GROUP BY output_format ORDER BY cnt DESC LIMIT 5"
                ).fetchall()
                top_formats = [dict(r) for r in top_formats]
            except Exception:
                top_formats = []
        return render_template('admin/dashboard.html',
                               total_users=total_users,
                               paid_users=paid_users,
                               free_users=free_users,
                               new_today=new_today,
                               new_7d=new_7d,
                               new_30d=new_30d,
                               total_conversions=total_conversions,
                               conversions_today=conversions_today,
                               conversions_7d=conversions_7d,
                               total_revenue=total_revenue,
                               revenue_30d=revenue_30d,
                               recent_users=recent_users,
                               signup_chart=signup_chart,
                               conversion_chart=conversion_chart,
                               top_formats=top_formats)

    
    
    
    
   
        
    @app.route('/admin/routes')
    def list_routes():
        """List all registered routes"""
        routes = []
        for rule in app.url_map.iter_rules():
            routes.append({
                'endpoint': rule.endpoint,
                'methods': list(rule.methods),
                'path': str(rule)
            })
        return jsonify(routes)  
    
    
    
      

    @app.route("/<lang_code>/admin/login", methods=["GET", "POST"])
    @app.route('/admin/login', methods=["GET", "POST"])
    def login():
            from configs.languages import supported_languages

            settings = get_site_settings()
            custom_slug = settings.get('admin_login_slug', '').strip().strip('/')

            # Check if this is a language-specific route
            lang_code = request.view_args.get('lang_code') if request.view_args else None

            # If it's a language route but not a valid language, show 404
            if lang_code and lang_code not in supported_languages:
                abort(404)

            # If custom slug is set and this is the default admin login, block it
            if custom_slug and not lang_code:
                abort(404)

            # Otherwise show login page
            return _admin_login_handler()

    @app.route('/admin/forgot-password', methods=['GET', 'POST'])
    def admin_forgot_password():
        import secrets as _secrets
        from datetime import datetime, timedelta
        if request.method == 'GET':
            return render_template('admin/forgot_password.html')
        email = request.form.get('email', '').strip().lower()
        profile = get_admin_profile()
        admin_email = (profile.get('email') or '').strip().lower()
        if email and admin_email and email == admin_email:
            token = _secrets.token_urlsafe(32)
            expires = (datetime.utcnow() + timedelta(hours=2)).strftime('%Y-%m-%d %H:%M:%S')
            with _db() as c:
                c.execute(
                    "INSERT INTO admin_password_reset_tokens (token, expires_at) VALUES (?, ?)",
                    (token, expires)
                )
                c.commit()
            settings = get_site_settings()
            site_name = settings.get('site_name', 'OnlineConvert')
            reset_link = f"https://{os.getenv('website_url', 'localhost')}/admin/reset-password/{token}"
            html_body = f"""
            <p>Hi,</p>
            <p>You requested a password reset for the <strong>{site_name}</strong> admin panel.</p>
            <p><a href="{reset_link}" style="color:#6366f1;font-weight:bold;">Click here to reset your password</a></p>
            <p>This link will expire in 2 hours.</p>
            <p>If you didn't request this, please ignore this email.</p>
            """
            try:
                send_email(admin_email, f"Admin Password Reset – {site_name}", html_body, None)
            except Exception:
                pass
        return render_template('admin/forgot_password.html', sent=True)

    @app.route('/admin/reset-password/<token>', methods=['GET', 'POST'])
    def admin_reset_password(token):
        from datetime import datetime
        with _db() as c:
            row = c.execute(
                "SELECT * FROM admin_password_reset_tokens WHERE token=? AND used=0",
                (token,)
            ).fetchone()
        if not row:
            return render_template('admin/reset_password.html', error='This reset link is invalid or has already been used.')
        row = dict(row)
        expires_at = row.get('expires_at', '')
        try:
            if datetime.utcnow() > datetime.strptime(expires_at, '%Y-%m-%d %H:%M:%S'):
                return render_template('admin/reset_password.html', error='This reset link has expired. Please request a new one.')
        except Exception:
            pass
        if request.method == 'GET':
            return render_template('admin/reset_password.html', token=token)
        new_password = request.form.get('new_password', '').strip()
        confirm_password = request.form.get('confirm_password', '').strip()
        if len(new_password) < 8:
            return render_template('admin/reset_password.html', token=token, error='Password must be at least 8 characters.')
        if new_password != confirm_password:
            return render_template('admin/reset_password.html', token=token, error='Passwords do not match.')
        new_hash = generate_password_hash(new_password)
        update_admin_profile({'password_hash': new_hash})
        with _db() as c:
            c.execute("UPDATE admin_password_reset_tokens SET used=1 WHERE token=?", (token,))
            c.commit()
        session['error'] = 'Password reset successfully. Please log in with your new password.'
        return redirect(url('/admin/login'))

    @app.route('/admin/users/new', methods=['GET', 'POST'])
    def admin_create_user():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        plans = get_all_plans()
        if request.method == 'GET':
            return render_template('admin/create_user.html', plans=plans)
        account_type = request.form.get('account_type', 'user')
        username = request.form.get('username', '').strip()
        email = request.form.get('email', '').strip().lower()
        password = request.form.get('password', '').strip()
        plan_id = request.form.get('plan_id', 1, type=int)
        errors = []
        if not username or len(username) < 3:
            errors.append('Username must be at least 3 characters.')
        if not password or len(password) < 6:
            errors.append('Password must be at least 6 characters.')
        if account_type == 'user' and (not email or '@' not in email):
            errors.append('A valid email is required for regular user accounts.')
        if not errors:
            with _db() as c:
                if account_type == 'editor':
                    if c.execute("SELECT id FROM editors WHERE username=?", (username,)).fetchone():
                        errors.append('That username is already taken by an editor.')
                else:
                    if c.execute("SELECT id FROM users WHERE username=?", (username,)).fetchone():
                        errors.append('That username is already taken.')
                    elif c.execute("SELECT id FROM users WHERE email=?", (email,)).fetchone():
                        errors.append('That email is already registered.')
        if errors:
            return render_template('admin/create_user.html', plans=plans, errors=errors,
                                   form={'username': username, 'email': email, 'plan_id': plan_id,
                                         'account_type': account_type})
        from werkzeug.security import generate_password_hash as _gph
        pw_hash = _gph(password)
        if account_type == 'editor':
            with _db() as c:
                cursor = c.execute(
                    "INSERT INTO editors (username, password_hash, allowed_languages, is_active) VALUES (?,?,?,1)",
                    (username, pw_hash, '["en"]')
                )
                c.commit()
                new_id = cursor.lastrowid
            log_event('admin_create_editor', f"Admin created new content editor: {username}", meta=f"editor_id={new_id}")
            flash(f"Content editor '{username}' created successfully.", "success")
            return redirect(url('/admin/editors'))
        else:
            email_verified = 1 if request.form.get('email_verified') else 0
            with _db() as c:
                cursor = c.execute(
                    "INSERT INTO users (username, email, password_hash, plan_id, email_verified) VALUES (?,?,?,?,?)",
                    (username, email, pw_hash, plan_id, email_verified)
                )
                c.commit()
                new_id = cursor.lastrowid
            log_event('admin_create_user', f"Admin created new user: {username} ({email})", meta=f"user_id={new_id}")
            flash(f"User '{username}' created successfully.", "success")
            return redirect(url('/admin/users'))

    @app.route("/<lang_code>/admin/blogs")
    @app.route('/admin/blogs')
    def admin_blogs():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        with sqlite3.connect("storage/sqlite.db") as connection:
            connection.row_factory = sqlite3.Row
            cursor = connection.cursor()
            cursor.execute("""
                SELECT b.rowid, b.*, u.username AS author_username, u.email AS author_email
                FROM blogs b
                LEFT JOIN users u ON u.id = b.author_user_id
                ORDER BY b.rowid DESC
            """)
            blogs = [dict(r) for r in cursor.fetchall()]
            cursor.close()
        return render_template("admin/blogs.html", blogs=blogs)

    @app.route("/<lang_code>/admin/messages")
    @app.route('/admin/messages')
    def admin_messages():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        with sqlite3.connect("storage/sqlite.db") as connection:
            cursor = connection.cursor()
            cursor.execute("select rowid, * from messages")

            messages = cursor.fetchall()

            cursor.close()
        return render_template("admin/messages.html", messages=messages)

    @app.route("/<lang_code>/admin/messages/remove/<id>", methods=['GET'])
    @app.route('/admin/messages/remove/<id>', methods=['GET'])
    def admin_messages_remove(id):
        logged_in, r = authenticate()
        if not logged_in:
            return r

        with sqlite3.connect(os.path.join(os.path.dirname(os.path.realpath(__file__)), "..", "storage", "sqlite.db")) as connection:
            cursor = connection.cursor()

            cursor.execute("select * from messages where rowid = ?", (id, ))

            if not cursor.fetchone():
                cursor.close()
                return redirect(url_for('admin_messages'))
            else:
                cursor.execute("delete from messages where rowid = ?", (id, ))
                cursor.close()
                connection.commit()
                _admin = session.get('admin_username', 'admin')
                log_deletion('message', entity_id=id, entity_name=str(id),
                             actor_username=_admin, actor_role='admin',
                             extra_meta={'message_rowid': id})

            return redirect(url_for('admin_messages'))

    @app.route("/<lang_code>/admin/blogs/<id>", methods=['GET', 'POST'])
    @app.route('/admin/blogs/<id>', methods=['GET', 'POST'])
    def admin_blogs_new(id):
        logged_in, r = authenticate()
        if not logged_in:
            return r

        if request.method == "GET":
            if id != "new":
                with sqlite3.connect("storage/sqlite.db") as connection:
                    connection.row_factory = sqlite3.Row
                    cursor = connection.cursor()
                    cursor.execute("""
                        SELECT b.rowid, b.*, u.username AS author_username, u.email AS author_email
                        FROM blogs b LEFT JOIN users u ON u.id = b.author_user_id
                        WHERE b.rowid = ?
                    """, (id,))
                    row = cursor.fetchone()
                    cursor.close()
                    if not row:
                        flash("Blog post not found.", "error")
                        return redirect(url_for('admin_blogs'))
                    blog = dict(row)
                    return render_template("admin/blog.html", blog=blog)
            else:
                return render_template("admin/blog.html", blog=None)
        else:
            from slugify import slugify
            title = (request.form.get("title") or "").strip()
            summary = (request.form.get("summary") or "").strip()
            content = (request.form.get("content") or "").strip()
            status = request.form.get("status", "draft").strip()
            image = request.files.get("image")
            filename = request.form.get("featured", "").strip()
            
            # Track if this is a publish action (status changing to published)
            was_published = False
            if id != "new":
                # Check current status before update
                with sqlite3.connect("storage/sqlite.db") as conn_check:
                    cur_check = conn_check.cursor()
                    cur_check.execute("SELECT status FROM blogs WHERE rowid=?", (id,))
                    current = cur_check.fetchone()
                    if current and current[0] != 'published' and status == 'published':
                        was_published = True
            
            if title:
                slug = slugify(title).strip()
            else:
                slug = ""

            if image and image.filename:
                filename = secure_filename(image.filename)
                filepath = os.path.join(app.config['BLOGS_ASSETS_DIR'], filename)
                image.save(filepath)

            with sqlite3.connect("storage/sqlite.db") as connection:
                cursor = connection.cursor()
                if id == "new":
                    cursor.execute(
                        "INSERT INTO blogs(title, summary, content, image, slug, status) VALUES (?, ?, ?, ?, ?, ?)",
                        (title, summary, content, filename, slug, status))
                    # If this is a new post published immediately
                    if status == 'published':
                        was_published = True
                else:
                    cursor.execute(
                        "UPDATE blogs SET title=?, summary=?, content=?, image=?, slug=?, status=? WHERE rowid=?",
                        (title, summary, content, filename, slug, status, id))
                cursor.close()
                connection.commit()
            
            # ===== TRIGGER SITEMAP REGENERATION IF BLOG WAS PUBLISHED =====
            if was_published:
                regenerate_sitemaps_async()
                app.logger.info(f"Sitemap regeneration triggered for published blog: {title}")
            
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest' or request.content_type and 'multipart' in request.content_type:
                return jsonify({"status": "OK"}), 200
            
            flash("Blog post saved.", "success")
            return redirect(url_for('admin_blogs'))

    @app.route("/<lang_code>/admin/blogs/remove/<id>", methods=['GET'])
    @app.route('/admin/blogs/remove/<id>', methods=['GET'])
    def admin_blogs_remove(id):
        logged_in, r = authenticate()
        if not logged_in:
            return r

        # Check if the blog was published before deletion
        was_published = False
        
        with sqlite3.connect("storage/sqlite.db") as connection:
            cursor = connection.cursor()

            # First check if blog exists and get its status
            cursor.execute("SELECT status FROM blogs WHERE rowid = ?", (id,))
            blog = cursor.fetchone()
            
            if not blog:
                cursor.close()
                return redirect(url("/admin/blogs"))
            else:
                # Check if it was published
                was_published = (blog[0] == 'published')
                
                # Delete the blog
                cursor.execute("DELETE FROM blogs WHERE rowid = ?", (id,))
                cursor.close()
                connection.commit()
                _admin = session.get('admin_username', 'admin')
                log_deletion('blog', entity_id=id, entity_name=str(id),
                             actor_username=_admin, actor_role='admin',
                             extra_meta={'blog_rowid': id, 'was_published': was_published})

        # ===== TRIGGER SITEMAP REGENERATION IF A PUBLISHED BLOG WAS DELETED =====
        if was_published:
            regenerate_sitemaps_async()
            app.logger.info(f"Sitemap regeneration triggered for deleted blog ID: {id}")

        return redirect("/admin/blogs")

    @app.route("/<lang_code>/admin/payment-gateways")
    @app.route('/admin/payment-gateways')
    def admin_payment_gateways():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        settings = get_site_settings()
        plans = [p for p in get_all_plans() if p['price_monthly'] > 0]
        plan_periods = {}
        for p in plans:
            with _db() as c:
                rows = c.execute("SELECT * FROM plan_billing_periods WHERE plan_id=? ORDER BY months", (p['id'],)).fetchall()
                plan_periods[p['id']] = [dict(r) for r in rows]
        return render_template('admin/payment_gateways.html', settings=settings, plans=plans, plan_periods=plan_periods)

    @app.route("/<lang_code>/admin/payment-gateways/save", methods=['POST'])
    @app.route('/admin/payment-gateways/save', methods=['POST'])
    def admin_payment_gateways_save():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        fields = [
            'stripe_enabled', 'stripe_publishable_key', 'stripe_secret_key', 'stripe_webhook_secret',
            'paypal_enabled', 'paypal_client_id', 'paypal_client_secret',
            'manual_payment_enabled', 'manual_payment_instructions',
        ]
        with _db() as c:
            for key in fields:
                if key in ('stripe_enabled', 'paypal_enabled', 'manual_payment_enabled'):
                    val = '1' if request.form.get(key) else '0'
                else:
                    val = request.form.get(key, '').strip()
                if c.execute("SELECT 1 FROM site_settings WHERE key=?", (key,)).fetchone():
                    c.execute("UPDATE site_settings SET value=? WHERE key=?", (val, key))
                else:
                    c.execute("INSERT INTO site_settings (key, value) VALUES (?,?)", (key, val))
            c.commit()
        flash("Payment gateway settings saved.", "success")
        return redirect(url_for('admin_payment_gateways'))

    @app.route("/<lang_code>/admin/payment-gateways/plan-discount", methods=['POST'])
    @app.route('/admin/payment-gateways/plan-discount', methods=['POST'])
    def admin_plan_discount_save():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        plan_id = request.form.get('plan_id', type=int)
        yearly_discount = request.form.get('yearly_discount', type=float) or 0
        if plan_id:
            with _db() as c:
                c.execute(
                    "UPDATE plan_billing_periods SET discount_percent=? WHERE plan_id=? AND months=12",
                    (min(max(yearly_discount, 0), 80), plan_id)
                )
                c.commit()
        flash("Yearly discount updated.", "success")
        return redirect(url_for('admin_payment_gateways'))

    @app.route("/<lang_code>/admin/payments")
    @app.route('/admin/payments')
    def admin_payments():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        filter_year   = request.args.get('year', '')
        filter_month  = request.args.get('month', '')
        filter_source = request.args.get('source', 'all')   # all | stripe | manual
        page          = request.args.get('page', 1, type=int)
        per_page      = 30

        import datetime as _dt2

        with _db() as c:
            # ── Stripe payments ──────────────────────────────────────────
            stripe_rows = []
            if filter_source in ('all', 'stripe'):
                stripe_rows = [dict(r) for r in c.execute("""
                    SELECT ph.id, ph.user_id, ph.amount, ph.currency, ph.status,
                           ph.description, ph.created_at,
                           'stripe' AS source, 'Online (Stripe)' AS method,
                           u.username, u.email,
                           '' AS plan_name, '' AS notes
                    FROM payment_history ph
                    LEFT JOIN users u ON u.id = ph.user_id
                    ORDER BY ph.created_at DESC
                """).fetchall()]
                for r in stripe_rows:
                    r['display_amount'] = (r['amount'] or 0) / 100

            # ── Manual payments ──────────────────────────────────────────
            manual_rows = []
            if filter_source in ('all', 'manual'):
                manual_rows = [dict(r) for r in c.execute("""
                    SELECT mp.id, mp.user_id, mp.amount, mp.currency, 'paid' AS status,
                           p.name AS description, mp.created_at,
                           'manual' AS source, mp.method,
                           u.username, u.email,
                           p.name AS plan_name, mp.notes
                    FROM manual_payments mp
                    LEFT JOIN users u ON u.id = mp.user_id
                    LEFT JOIN plans p ON p.id = mp.plan_id
                    ORDER BY mp.created_at DESC
                """).fetchall()]
                for r in manual_rows:
                    r['display_amount'] = r['amount'] or 0

            # ── Merge + filter ───────────────────────────────────────────
            all_rows = stripe_rows + manual_rows
            all_rows.sort(key=lambda x: x.get('created_at') or '', reverse=True)

            if filter_year:
                all_rows = [r for r in all_rows if (r.get('created_at') or '').startswith(filter_year)]
            if filter_month and filter_year:
                prefix = f"{filter_year}-{filter_month.zfill(2)}"
                all_rows = [r for r in all_rows if (r.get('created_at') or '').startswith(prefix)]

            # ── Summary stats ────────────────────────────────────────────
            now = _dt2.datetime.utcnow()
            this_month_prefix = now.strftime('%Y-%m')
            this_year_prefix  = now.strftime('%Y')

            def _sum(rows):
                return sum(r.get('display_amount', 0) for r in rows)

            total_revenue      = _sum(all_rows)
            revenue_this_month = _sum([r for r in all_rows if (r.get('created_at') or '').startswith(this_month_prefix)])
            revenue_this_year  = _sum([r for r in all_rows if (r.get('created_at') or '').startswith(this_year_prefix)])

            # ── Paginate ─────────────────────────────────────────────────
            total        = len(all_rows)
            total_pages  = max(1, (total + per_page - 1) // per_page)
            page         = min(page, total_pages)
            offset       = (page - 1) * per_page
            payments     = all_rows[offset: offset + per_page]

            # ── Year list for filter dropdown ────────────────────────────
            years = sorted(set(
                (r.get('created_at') or '')[:4]
                for r in stripe_rows + manual_rows
                if (r.get('created_at') or '')[:4].isdigit()
            ), reverse=True)

        plans = get_all_plans()
        return render_template('admin/payments_dashboard.html',
                               payments=payments,
                               total=total, page=page, total_pages=total_pages,
                               plans=plans,
                               filter_year=filter_year,
                               filter_month=filter_month,
                               filter_source=filter_source,
                               years=years,
                               total_revenue=total_revenue,
                               revenue_this_month=revenue_this_month,
                               revenue_this_year=revenue_this_year)

    @app.route("/<lang_code>/admin/payments/export-csv")
    @app.route('/admin/payments/export-csv')
    def admin_payments_export_csv():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import csv as _csv, io as _io

        filter_year   = request.args.get('year', '')
        filter_month  = request.args.get('month', '')
        filter_source = request.args.get('source', 'all')

        with _db() as c:
            stripe_rows = []
            if filter_source in ('all', 'stripe'):
                stripe_rows = [dict(r) for r in c.execute("""
                    SELECT ph.id, ph.amount, ph.currency, ph.status, ph.description,
                           ph.created_at, 'stripe' AS source, 'Online (Stripe)' AS method,
                           u.username, u.email
                    FROM payment_history ph LEFT JOIN users u ON u.id = ph.user_id
                    ORDER BY ph.created_at DESC
                """).fetchall()]
                for r in stripe_rows:
                    r['display_amount'] = (r['amount'] or 0) / 100
                    r['plan_name'] = r.get('description', '')

            manual_rows = []
            if filter_source in ('all', 'manual'):
                manual_rows = [dict(r) for r in c.execute("""
                    SELECT mp.id, mp.amount, mp.currency, 'paid' AS status, mp.method,
                           mp.created_at, 'manual' AS source,
                           u.username, u.email, p.name AS plan_name
                    FROM manual_payments mp
                    LEFT JOIN users u ON u.id = mp.user_id
                    LEFT JOIN plans p ON p.id = mp.plan_id
                    ORDER BY mp.created_at DESC
                """).fetchall()]
                for r in manual_rows:
                    r['display_amount'] = r['amount'] or 0

        all_rows = stripe_rows + manual_rows
        all_rows.sort(key=lambda x: x.get('created_at') or '', reverse=True)
        if filter_year:
            all_rows = [r for r in all_rows if (r.get('created_at') or '').startswith(filter_year)]
        if filter_month and filter_year:
            prefix = f"{filter_year}-{filter_month.zfill(2)}"
            all_rows = [r for r in all_rows if (r.get('created_at') or '').startswith(prefix)]

        output = _io.StringIO()
        writer = _csv.writer(output)
        writer.writerow(['Date', 'User', 'Email', 'Plan/Description', 'Amount', 'Currency', 'Method', 'Source', 'Status'])
        for row in all_rows:
            writer.writerow([
                (row.get('created_at') or '')[:10],
                row.get('username', ''),
                row.get('email', ''),
                row.get('plan_name') or row.get('description', ''),
                f"{row.get('display_amount', 0):.2f}",
                (row.get('currency') or 'USD').upper(),
                row.get('method', ''),
                row.get('source', ''),
                (row.get('status') or '').title(),
            ])
        output.seek(0)
        from flask import make_response
        resp = make_response(output.getvalue())
        resp.headers['Content-Type'] = 'text/csv; charset=utf-8'
        resp.headers['Content-Disposition'] = 'attachment; filename=payments.csv'
        return resp

    @app.route('/admin/blogs/<int:blog_id>/approve', methods=['POST'])
    def admin_blog_approve(blog_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        fee = float(request.form.get('publish_fee', 0) or 0)
        note = request.form.get('admin_note', '').strip()
        
        was_published = False
        
        with _db() as c:
            _row = c.execute("SELECT rowid,status,title,author_user_id,slug FROM blogs WHERE rowid=?", (blog_id,)).fetchone()
            if not _row:
                flash("Blog post not found.", "error")
                return redirect(url_for('admin_blogs'))
            blog = dict(_row)
            
            if fee > 0:
                c.execute(
                    "UPDATE blogs SET status='pending_payment', publish_fee=?, payment_status='pending', admin_note=?, updated_at=datetime('now') WHERE rowid=?",
                    (fee, note, blog_id)
                )
                c.commit()
                flash(f"Blog pending payment of ${fee:.2f}. User has been notified.", "success")
                if blog.get('author_user_id'):
                    send_user_notification(
                        blog['author_user_id'], 'blog_reviewed',
                        "Your blog post requires payment to publish",
                        f"<p>Hi there!</p><p>Your blog post <strong>\"{blog['title']}\"</strong> has been reviewed and approved.</p>"
                        f"<p>To publish it, a fee of <strong>${fee:.2f}</strong> is required. "
                        f"Please <a href='/dashboard/blogs' style='color:#6366f1;'>visit your dashboard</a> to complete payment.</p>"
                        f"{'<p><em>Admin note: ' + note + '</em></p>' if note else ''}",
                        f"Your blog post has been reviewed. A fee of ${fee:.2f} is required to publish."
                    )
            else:
                c.execute(
                    "UPDATE blogs SET status='published', publish_fee=0, payment_status='free', admin_note=?, updated_at=datetime('now') WHERE rowid=?",
                    (note, blog_id)
                )
                c.commit()
                was_published = True
                flash("Blog post approved and published.", "success")
                if blog.get('author_user_id'):
                    send_user_notification(
                        blog['author_user_id'], 'blog_published',
                        "Your blog post is now live!",
                        f"<p>Hi there!</p><p>Great news — your blog post <strong>\"{blog['title']}\"</strong> has been approved and is now published on the site.</p>"
                        f"<p><a href='/blogs/{blog.get('slug', '')}' style='color:#6366f1;'>View your post →</a></p>",
                        f"Your blog post \"{blog['title']}\" is now published."
                    )
        
        # ===== TRIGGER SITEMAP REGENERATION IF BLOG WAS PUBLISHED =====
        if was_published:
            regenerate_sitemaps_async()
            app.logger.info(f"Sitemap regeneration triggered for approved blog ID: {blog_id}")
        
        return redirect(url_for('admin_blogs'))

    @app.route('/admin/blogs/<int:blog_id>/mark-paid', methods=['POST'])
    def admin_blog_mark_paid(blog_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        method = request.form.get('method', 'manual').strip()
        notes = request.form.get('notes', '').strip()
        
        was_published = False
        
        with _db() as c:
            blog = c.execute("SELECT rowid,status,title,author_user_id,slug FROM blogs WHERE rowid=?", (blog_id,)).fetchone()
            if not blog:
                flash("Blog not found.", "error")
                return redirect(url_for('admin_blogs'))
            blog = dict(blog)
            if blog.get('publish_fee', 0) > 0 and blog.get('author_user_id'):
                c.execute(
                    "INSERT INTO manual_payments (user_id, plan_id, amount, method, notes, recorded_by) VALUES (?,?,?,?,?,?)",
                    (blog['author_user_id'], 1, blog['publish_fee'], method, f"Blog post fee: {blog['title']}. {notes}", 'admin')
                )
            c.execute(
                "UPDATE blogs SET status='published', payment_status='paid_manual', updated_at=datetime('now') WHERE rowid=?",
                (blog_id,)
            )
            c.commit()
            was_published = True
        
        if blog.get('author_user_id'):
            send_user_notification(
                blog['author_user_id'], 'blog_published',
                "Your blog post is now live!",
                f"<p>Your blog post <strong>\"{blog['title']}\"</strong> has been confirmed as paid and is now published.</p>"
                f"<p><a href='/blogs/{blog.get('slug', '')}' style='color:#6366f1;'>View your post →</a></p>",
                f"Your blog post \"{blog['title']}\" is now published."
            )
        
        # ===== TRIGGER SITEMAP REGENERATION IF BLOG WAS PUBLISHED =====
        if was_published:
            regenerate_sitemaps_async()
            app.logger.info(f"Sitemap regeneration triggered for paid blog ID: {blog_id}")
        
        flash("Blog post marked as paid and published.", "success")
        return redirect(url_for('admin_blogs'))

    @app.route('/admin/blogs/<int:blog_id>/reject', methods=['POST'])
    def admin_blog_reject(blog_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        note = request.form.get('admin_note', '').strip()
        
        was_published = False
        
        with _db() as c:
            blog = c.execute("SELECT rowid,status,title,author_user_id FROM blogs WHERE rowid=?", (blog_id,)).fetchone()
            if blog:
                was_published = (blog['status'] == 'published')
            c.execute(
                "UPDATE blogs SET status='rejected', admin_note=?, updated_at=datetime('now') WHERE rowid=?",
                (note, blog_id)
            )
            c.commit()
        
        # ===== TRIGGER SITEMAP REGENERATION IF A PUBLISHED BLOG WAS REJECTED =====
        if was_published:
            regenerate_sitemaps_async()
            app.logger.info(f"Sitemap regeneration triggered for rejected blog ID: {blog_id}")
        
        if blog and dict(blog).get('author_user_id'):
            b = dict(blog)
            send_user_notification(
                b['author_user_id'], 'blog_reviewed',
                "Your blog post was not approved",
                f"<p>Your blog post <strong>\"{b['title']}\"</strong> was reviewed but not approved for publication at this time.</p>"
                f"{'<p><strong>Admin feedback:</strong> ' + note + '</p>' if note else ''}"
                f"<p>You can edit and resubmit your post from your <a href='/dashboard/blogs' style='color:#6366f1;'>dashboard</a>.</p>",
                f"Your blog post \"{b['title']}\" was not approved. Reason: {note or 'No reason given.'}"
            )
        flash("Blog post rejected.", "success")
        return redirect(url_for('admin_blogs'))

    @app.route("/<lang_code>/admin/settings", methods=['GET', 'POST'])
    @app.route('/admin/settings', methods=['GET', 'POST'])
    def admin_settings():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        SETTINGS_FIELDS = [
            {
                "section":
                "Hero Section",
                "fields": [
                    {
                        "key": "hero_badge",
                        "label": "Badge Text",
                        "default": "Free & Unlimited Conversions",
                        "is_textarea": False
                    },
                    {
                        "key": "hero_heading",
                        "label": "Main Heading",
                        "default": "THE ULTIMATE FREE ONLINE FILE CONVERTER",
                        "is_textarea": False
                    },
                    {
                        "key": "hero_description",
                        "label": "Description",
                        "default":
                        "Unlimited All in One File Converter with Audio, Video, Images, Documents, PDFs, Ebooks, Softwares, Compressions and Much More.",
                        "is_textarea": True
                    },
                ]
            },
            {
                "section":
                "Converter Section",
                "fields": [
                    {
                        "key": "converter_heading",
                        "label": "Section Heading",
                        "default": "Choose Your Converter",
                        "is_textarea": False
                    },
                    {
                        "key": "converter_description",
                        "label": "Section Description",
                        "default":
                        "Select a file type below to get started with your conversion",
                        "is_textarea": True
                    },
                ]
            },
            {
                "section":
                "Tools Section",
                "fields": [
                    {
                        "key": "tools_converter_label",
                        "label": "Converter Label",
                        "default": "Converter",
                        "is_textarea": False
                    },
                    {
                        "key": "tools_more_label",
                        "label": "More Converters Label",
                        "default": "More Converters",
                        "is_textarea": False
                    },
                    {
                        "key": "tools_compress_doc_label",
                        "label": "Compress Documents Label",
                        "default": "Compress document files",
                        "is_textarea": False
                    },
                    {
                        "key": "tools_compress_img_label",
                        "label": "Compress Images Label",
                        "default": "Compress image files",
                        "is_textarea": False
                    },
                    {
                        "key": "tools_compress_vid_label",
                        "label": "Compress Videos Label",
                        "default": "Compress video files",
                        "is_textarea": False
                    },
                    {
                        "key": "tools_hash_label",
                        "label": "Hash Generators Label",
                        "default": "Hash Generators",
                        "is_textarea": False
                    },
                    {
                        "key": "tools_additional_label",
                        "label": "Additional Tools Label",
                        "default": "Additional Tools",
                        "is_textarea": False
                    },
                    {
                        "key": "tools_currency_text",
                        "label": "Currency Converter Link Text",
                        "default": "Currency Converter",
                        "is_textarea": False
                    },
                ]
            },
            {
                "section":
                "Content Section",
                "fields": [
                    {
                        "key": "content_heading",
                        "label": "Section Heading",
                        "default": "EDIT & CONVERT PDF FILES ONLINE",
                        "is_textarea": False
                    },
                    {
                        "key": "content_subheading",
                        "label": "Section Subheading",
                        "default": "FOR FREE AND WHEREVER YOU WANT",
                        "is_textarea": False
                    },
                    {
                        "key": "card1_title",
                        "label": "Card 1 Title",
                        "default": "How To Use Converter",
                        "is_textarea": False
                    },
                    {
                        "key": "card1_text",
                        "label": "Card 1 Text",
                        "default":
                        "If you've landed on Onlineconvert.cc, it's safe to assume that you're in search of a reliable online file converter. Simply select the file editing function that you require, and you'll be redirected to a page where all available options will be presented to you.",
                        "is_textarea": True
                    },
                    {
                        "key": "card2_title",
                        "label": "Card 2 Title",
                        "default": "Convert Your Files with Ease",
                        "is_textarea": False
                    },
                    {
                        "key": "card2_text",
                        "label": "Card 2 Text",
                        "default":
                        "With Onlineconvert.cc, you have access to over 500 file converters, making it easy to convert, edit, or compress your files online. No matter what format you need your files to be in, we've got you covered with hundreds of file formats.",
                        "is_textarea": True
                    },
                    {
                        "key": "card3_title",
                        "label": "Card 3 Title",
                        "default": "Effortlessly Customize Your Files",
                        "is_textarea": False
                    },
                    {
                        "key": "card3_text",
                        "label": "Card 3 Text",
                        "default":
                        "We've created a free online converter tool that allows you to customize your images, documents, and all other important files with ease. Crop, resize, add filters to images, or edit text, font, layout of your documents.",
                        "is_textarea": True
                    },
                    {
                        "key": "card4_title",
                        "label": "Card 4 Title",
                        "default": "Your Files Are Safe and Secure",
                        "is_textarea": False
                    },
                    {
                        "key": "card4_text",
                        "label": "Card 4 Text",
                        "default":
                        "We take your privacy and security seriously. We automatically delete all files that you upload after 24 hours, and we don't make any backup copies of them.",
                        "is_textarea": True
                    },
                    {
                        "key": "card5_title",
                        "label": "Card 5 Title",
                        "default": "Supports a Wide Range of File Formats",
                        "is_textarea": False
                    },
                    {
                        "key": "card5_text",
                        "label": "Card 5 Text",
                        "default":
                        "Documents: PDF, Microsoft Word, OpenOffice, TXT, RTF, ePub and more. Images: JPG, PNG, BMP, TIFF, GIF, SVG and more. Presentations: PPT, PPTX, ODP, and more.",
                        "is_textarea": True
                    },
                    {
                        "key": "card6_title",
                        "label": "Card 6 Title",
                        "default": "Convert Files Anywhere, Anytime",
                        "is_textarea": False
                    },
                    {
                        "key": "card6_text",
                        "label": "Card 6 Text",
                        "default":
                        "Our platform works seamlessly on any device, with any browser. Whether you're using a desktop, laptop, smartphone, or tablet, you can easily convert your files on the go. No installation or download required.",
                        "is_textarea": True
                    },
                ]
            },
            {
                "section": "Cloud Upload Tabs",
                "fields": [
                    {
                        "key": "google_drive_client_id",
                        "label": "Google Drive OAuth Client ID",
                        "default": "",
                        "is_textarea": False
                    },
                    {
                        "key": "google_drive_api_key",
                        "label": "Google Drive API Key (for Picker)",
                        "default": "",
                        "is_textarea": False
                    },
                    {
                        "key": "dropbox_app_key",
                        "label": "Dropbox App Key",
                        "default": "",
                        "is_textarea": False
                    },
                    {
                        "key": "dropbox_app_secret",
                        "label": "Dropbox App Secret",
                        "default": "",
                        "is_textarea": False
                    },
                    {
                        "key": "onedrive_client_id",
                        "label": "OneDrive OAuth Client ID",
                        "default": "",
                        "is_textarea": False
                    },
                ]
            },
        ]

        if request.method == "POST":
            for section in SETTINGS_FIELDS:
                for field in section["fields"]:
                    value = request.form.get(field["key"], "").strip()
                    if value:
                        set_site_setting(field["key"], value)
                    else:
                        set_site_setting(field["key"], field["default"])
            is_ajax = request.headers.get('X-Requested-With') == 'XMLHttpRequest'
            if is_ajax:
                return jsonify({"status": "ok"}), 200
            return redirect(url("/admin/settings"))

        settings = get_site_settings()
        return render_template("admin/settings.html",
                               sections=SETTINGS_FIELDS,
                               settings=settings)

    @app.route("/<lang_code>/admin/general", methods=['GET', 'POST'])
    @app.route('/admin/general', methods=['GET', 'POST'])
    def admin_general():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        if request.method == 'POST':
            # Check if subdomains_enabled setting is changing
            old_value = get_site_settings().get('subdomains_enabled', '0')
            new_value = request.form.get('subdomains_enabled', '0')
            subdomain_changed = (old_value != new_value)

            # Update all settings including the new logo text fields
            settings_keys = [
                # Existing fields
                'site_logo', 'site_favicon', 'global_head_html', 'global_body_html', 'footer_text',
                'social_buttons_enabled', 'social_bookmark_enabled', 'social_like_enabled', 
                'social_share_enabled', 'social_tweet_enabled',
                'social_like_count', 'social_share_count', 'social_bookmark_url', 'social_tweet_text',
                'recaptcha_enabled', 'recaptcha_site_key', 'recaptcha_secret_key',
                'smtp_host', 'smtp_port', 'smtp_username', 'smtp_password',
                'smtp_from_email', 'smtp_from_name', 'smtp_use_tls',
                'subdomains_enabled',

                # NEW: Logo type and text settings
                'logo_type',
                'logo_text',
                'logo_text_font',
                'logo_text_custom_font',
                'logo_text_font_url',
                'logo_text_size',
                'logo_text_weight',
                'logo_text_color_light',
                'logo_text_color_dark',
                'logo_text_italic',
                'logo_text_underline',
                'logo_text_uppercase',
                'logo_text_letter_spacing',

                # Google Social Login
                'google_client_id', 'google_client_secret',

                # Microsoft Social Login
                'microsoft_client_id', 'microsoft_client_secret',

                # Apple Sign In
                'apple_client_id', 'apple_team_id', 'apple_key_id', 'apple_private_key',

                # Cloud Upload Tabs
                'google_drive_client_id', 'google_drive_api_key',
                'dropbox_app_key', 'dropbox_app_secret', 'onedrive_client_id',

                # Job Notifications
                'job_notify_browser_enabled', 'job_notify_email_enabled',
                'job_notify_push_enabled',
                'job_notify_from_email', 'job_notify_from_name',
                'job_notify_smtp_host', 'job_notify_smtp_port',
                'job_notify_smtp_username', 'job_notify_smtp_password',
                'job_notify_smtp_use_tls',

                # Broadcast
                'broadcast_secret_key',

                # Brand replacement
                'brand_replacement',
                'brand_domain',

                # Brand / Primary color
                'primary_color',
                'primary_dark_color',
                'primary_light_color',

                # Scrollbar
                'scrollbar_thumb',
                'scrollbar_track_dark',
                'scrollbar_track_light',

                # Hero Appearance
                'hero_bg_type',
                'hero_grad_from_light',
                'hero_grad_to_light',
                'hero_grad_from_dark',
                'hero_grad_to_dark',
                'hero_grad_dir',
                'hero_solid_light',
                'hero_solid_dark',
                'hero_bg_image',
                'hero_overlay_opacity',
                'hero_text_color_light',
                'hero_text_color_dark',
                'hero_img_left_color_light',
                'hero_img_left_color_dark',

                # Theme / Appearance
                'theme_allow_dark',
                'theme_allow_light',
                'theme_default',
            ]

            for key in settings_keys:
                # Handle checkbox fields that might be unchecked
                if key in ['social_buttons_enabled', 'social_bookmark_enabled', 'social_like_enabled', 
                          'social_share_enabled', 'social_tweet_enabled', 'recaptcha_enabled', 
                          'smtp_use_tls', 'subdomains_enabled',
                          'job_notify_browser_enabled', 'job_notify_email_enabled',
                          'job_notify_push_enabled', 'job_notify_smtp_use_tls',
                          'theme_allow_dark', 'theme_allow_light']:
                    # Use getlist so that when both hidden=0 and checkbox=1 are submitted,
                    # we correctly detect '1' (checked) vs only '0' (unchecked/hidden-only)
                    value = '1' if '1' in request.form.getlist(key) else '0'
                else:
                    value = request.form.get(key, '')

                # Sanitize brand / scrollbar color values
                if key in ('primary_color', 'primary_dark_color', 'primary_light_color',
                           'scrollbar_thumb', 'scrollbar_track_dark', 'scrollbar_track_light'):
                    import re as _re
                    if not _re.match(r'^#[0-9A-Fa-f]{6}$', value):
                        value = ''

                # Sanitize hero appearance values before saving
                elif key == 'hero_bg_type':
                    value = value if value in ('gradient', 'solid', 'image') else 'gradient'
                elif key in ('hero_grad_from_light', 'hero_grad_to_light',
                             'hero_grad_from_dark', 'hero_grad_to_dark',
                             'hero_solid_light', 'hero_solid_dark',
                             'hero_text_color_light', 'hero_text_color_dark',
                             'hero_img_left_color_light', 'hero_img_left_color_dark'):
                    import re as _re
                    if not _re.match(r'^#[0-9A-Fa-f]{6}$', value):
                        value = ''
                elif key == 'hero_grad_dir':
                    _allowed_dirs = ('135deg', 'to right', 'to bottom',
                                     'to bottom right', 'to bottom left', '45deg')
                    value = value if value in _allowed_dirs else '135deg'
                elif key == 'hero_overlay_opacity':
                    try:
                        _op = int(value)
                        value = str(max(0, min(100, _op)))
                    except (ValueError, TypeError):
                        value = '40'

                set_site_setting(key, value)

            # Handle logo text color separately if coming from color picker or hex input
            # (both inputs have the same name, so we're already covered above)

            # ===== TRIGGER SITEMAP REGENERATION IF SUBDOMAIN SETTING CHANGED =====
            if subdomain_changed:
                regenerate_sitemaps_async()
                app.logger.info(f"Sitemap regeneration triggered by subdomain setting change: {old_value} -> {new_value}")

            # Update reCAPTCHA config
            app.config['RECAPTCHA_ENABLED'] = (request.form.get('recaptcha_enabled', '') == '1')
            app.config['RECAPTCHA_SITE_KEY'] = request.form.get('recaptcha_site_key', '') or app.config.get('RECAPTCHA_SITE_KEY', '')
            app.config['RECAPTCHA_SECRET_KEY'] = request.form.get('recaptcha_secret_key', '') or app.config.get('RECAPTCHA_SECRET_KEY', '')

            # ===== DOMAIN REPLACEMENT IN TEMPLATES =====
            # Simple two-value replace: old_domain → new_domain, old_name → new_name.
            # Runs automatically on every settings save — no separate "apply" step.
            import os as _bos, re as _bre

            _cur_site    = get_site_settings()
            _old_domain = (request.form.get('brand_original_domain', '').strip()
                           or _cur_site.get('brand_original_domain', '')
                           or 'onlineconvert.cc')
            _new_domain  = request.form.get('brand_domain', '').strip()
            _new_name    = request.form.get('brand_replacement', '').strip()
            _old_name    = (_cur_site.get('brand_original_name') or 'onlineconvert')

            if not _new_name and _new_domain:
                _new_name = _new_domain.split('.')[0].capitalize()

            if _new_domain:
                def _dmirror(src, tgt):
                    if not src or not tgt: return tgt
                    if src.isupper(): return tgt.upper()
                    if src.islower(): return tgt.lower()
                    if src[0].isupper(): return tgt[0].upper() + tgt[1:]
                    return tgt

                _dpairs = sorted([(_old_domain, _new_domain), (_old_name, _new_name)],
                                 key=lambda x: -len(x[0]))
                _drx  = _bre.compile('|'.join(_bre.escape(o) for o, r in _dpairs), _bre.IGNORECASE)
                _dlke = {o: r for o, r in _dpairs}
                _dlkl = {o.lower(): r for o, r in _dpairs}

                def _doreplace(m):
                    s = m.group(0)
                    if s in _dlke: return _dlke[s]
                    return _dmirror(s, _dlkl.get(s.lower(), s))

                def _apply_domain_to_dir(base_dir):
                    _tpl = _bos.path.join(base_dir, 'templates')
                    if not _bos.path.isdir(_tpl): return
                    for _root, _dirs, _files in _bos.walk(_tpl):
                        _dirs[:] = [d for d in _dirs if d not in ['__pycache__', '.git']]
                        for _fn in _files:
                            if not _fn.endswith(('.html', '.htm')): continue
                            _fp = _bos.path.join(_root, _fn)
                            try:
                                with open(_fp, encoding='utf-8', errors='ignore') as _f:
                                    _orig = _f.read()
                                _cur = _orig
                                for _ in range(5):
                                    _nxt = _drx.sub(_doreplace, _cur)
                                    if _nxt == _cur: break
                                    _cur = _nxt
                                if _cur != _orig:
                                    with open(_fp, 'w', encoding='utf-8') as _f:
                                        _f.write(_cur)
                            except Exception:
                                pass

                _apply_domain_to_dir(dir_path)
                _host_app_dir = (_bos.environ.get('HOST_APP_DIR') or '').strip()
                if _host_app_dir and _bos.path.isdir(_host_app_dir):
                    _apply_domain_to_dir(_host_app_dir)

                # Update stored "from" for next replacement cycle
                set_site_setting('brand_original_domain', _new_domain)
                set_site_setting('brand_original_name', _new_name)

            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "ok"})
            return redirect(url("/admin/general"))

        # GET request - get current settings
        site = get_site_settings()

        # Ensure default values exist for new settings if not already set
        if 'logo_type' not in site:
            site['logo_type'] = 'image'
        if 'logo_text' not in site:
            site['logo_text'] = 'OnlineConvert'
        if 'logo_text_size' not in site:
            site['logo_text_size'] = 'text-2xl'
        if 'logo_text_weight' not in site:
            site['logo_text_weight'] = 'font-bold'
        if 'logo_text_color' not in site:
            site['logo_text_color'] = '#000000'

        # Brand / primary color defaults
        site.setdefault('primary_color', '#3B82F6')
        site.setdefault('primary_dark_color', '#2563EB')
        site.setdefault('primary_light_color', '#60A5FA')

        # Scrollbar defaults
        site.setdefault('scrollbar_thumb', '#3B82F6')
        site.setdefault('scrollbar_track_dark', '#0f172a')
        site.setdefault('scrollbar_track_light', '#f1f5f9')

        # Hero appearance defaults
        site.setdefault('hero_bg_type', 'gradient')
        site.setdefault('hero_grad_from_light', '#1e3a5f')
        site.setdefault('hero_grad_to_light', '#8B5CF6')
        site.setdefault('hero_grad_from_dark', '#0f172a')
        site.setdefault('hero_grad_to_dark', '#5b21b6')
        site.setdefault('hero_grad_dir', '135deg')
        site.setdefault('hero_solid_light', '#1e3a5f')
        site.setdefault('hero_solid_dark', '#0f172a')
        site.setdefault('hero_bg_image', '')
        site.setdefault('hero_overlay_opacity', '40')
        site.setdefault('hero_text_color_light', '#ffffff')
        site.setdefault('hero_text_color_dark', '#ffffff')

        return render_template('admin/general.html', site=site)



    @app.route("/<lang_code>/admin/security", methods=['GET', 'POST'])
    @app.route('/admin/security', methods=['GET', 'POST'])
    def admin_security():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if request.method == 'POST':
            current = request.form.get('current_password', '')
            new_pw = request.form.get('new_password', '')
            profile = get_admin_profile()
            if profile['password_hash']:
                valid = check_password_hash(profile['password_hash'], current)
            else:
                valid = (current == os.getenv('admin_password', 'admin123'))
            if not valid:
                if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                    return jsonify({"status": "error", "message": "Current password is incorrect."}), 400
                session['error'] = "Current password is incorrect."
                return redirect(url("/admin/security"))
            update_admin_profile({'password_hash': generate_password_hash(new_pw)})
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "ok"})
            return redirect(url("/admin/security"))
        current_settings = get_site_settings()
        return render_template('admin/security.html', current_settings=current_settings)

    @app.route("/<lang_code>/admin/security/login-url", methods=['POST'])
    @app.route('/admin/security/login-url', methods=['POST'])
    def admin_save_login_url():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        
        is_ajax = request.headers.get('X-Requested-With') == 'XMLHttpRequest'
        
        try:
            # Get form data
            new_slug = request.form.get('new_login_url', '').strip().strip('/')
            confirm_password = request.form.get('confirm_admin_password', '').strip()
            disable = request.form.get('disable_custom_login') == '1'
            
            # Validate password
            if not confirm_password:
                if is_ajax:
                    return jsonify({'status': 'error', 'message': 'Password is required.'}), 400
                flash("Password is required.", "error")
                return redirect(url("/admin/security"))
            
            # Verify admin password
            profile = get_admin_profile()
            if profile['password_hash']:
                valid = check_password_hash(profile['password_hash'], confirm_password)
            else:
                valid = (confirm_password == os.getenv('admin_password', 'admin123'))
            
            if not valid:
                if is_ajax:
                    return jsonify({'status': 'error', 'message': 'Current admin password is incorrect.'}), 400
                flash("Current admin password is incorrect.", "error")
                return redirect(url("/admin/security"))
            
            # Handle disable option
            if disable:
                # Clear the custom slug
                new_slug = ''
                message = 'Custom login URL disabled. Using default /admin/login'
            else:
                # Validate URL for non-disable case
                if not new_slug:
                    if is_ajax:
                        return jsonify({'status': 'error', 'message': 'URL is required.'}), 400
                    flash("URL is required.", "error")
                    return redirect(url("/admin/security"))
                
                # Validate URL format
                import re
                if not re.match(r'^[a-zA-Z0-9\-]{6,}$', new_slug):
                    if is_ajax:
                        return jsonify({'status': 'error', 'message': 'URL must be at least 6 characters, letters/numbers/hyphens only.'}), 400
                    flash("URL must be at least 6 characters, letters/numbers/hyphens only.", "error")
                    return redirect(url("/admin/security"))
                
                # Check reserved URLs
                reserved = ['admin', 'admin/login', 'static', 'api', 'register', 'login', 'logout', 'dashboard', 'upload', 'output']
                if new_slug in reserved or any(new_slug.startswith(r + '/') for r in reserved):
                    if is_ajax:
                        return jsonify({'status': 'error', 'message': 'That URL is reserved and cannot be used.'}), 400
                    flash("That URL is reserved and cannot be used.", "error")
                    return redirect(url("/admin/security"))
                
                message = f'Login URL updated to /{new_slug}'
            
            # Save to database
            with sqlite3.connect(os.path.join(dir_path, "storage", "sqlite.db")) as _sc:
                _sc.execute(
                    "INSERT OR REPLACE INTO site_settings (key, value) VALUES ('admin_login_slug', ?)",
                    (new_slug,)
                )
                _sc.commit()
            
            # Return JSON response with the new URL
            if is_ajax:
                response_data = {
                    'status': 'ok',
                    'message': message
                }
                # Only include new_url if it's not empty
                if new_slug:
                    response_data['new_url'] = new_slug
                
                return jsonify(response_data)
            else:
                flash(message, "success")
                return redirect(url("/admin/security"))
                
        except Exception as e:
            app.logger.error(f"Error saving login URL: {str(e)}")
            if is_ajax:
                return jsonify({'status': 'error', 'message': 'An error occurred.'}), 500
            flash("An error occurred.", "error")
            return redirect(url("/admin/security"))

    # ── Server Settings ─────────────────────────────────────────────────────
    @app.route("/<lang_code>/admin/server-settings", methods=['GET', 'POST'])
    @app.route('/admin/server-settings', methods=['GET', 'POST'])
    def admin_server_settings(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return abort(403)

        is_ajax = request.headers.get('X-Requested-With') == 'XMLHttpRequest'

        SERVER_KEYS = [
            'server_max_upload_mb',
            'session_lifetime_days',
            'compress_level',
            'compress_min_size',
            'whisper_model',
        ]

        WHISPER_MODELS = ['tiny', 'base', 'small', 'medium', 'large']

        if request.method == 'POST':
            for key in SERVER_KEYS:
                val = request.form.get(key, '').strip()
                if key == 'whisper_model':
                    if val not in WHISPER_MODELS:
                        val = 'tiny'
                    set_site_setting(key, val)
                elif val != '':
                    set_site_setting(key, val)

            # Apply immediately to running app
            load_server_settings(app)

            if is_ajax:
                return jsonify({'status': 'ok', 'message': 'Server settings saved.'})
            flash('Server settings saved successfully.', 'success')
            return redirect(url('/admin/server-settings'))

        site = get_site_settings()
        # Compute actual effective MB for each plan so admin can see comparison
        plans = get_all_plans()
        server_max_mb = int(site.get('server_max_upload_mb', '500'))
        for plan in plans:
            plan_mb = int(plan.get('max_file_size_mb', 0) or 0)
            if plan_mb <= 0:
                plan['effective_max_mb'] = server_max_mb
            else:
                plan['effective_max_mb'] = min(server_max_mb, plan_mb)

        return render_template('admin/server-settings.html',
                               site=site,
                               plans=plans,
                               server_max_mb=server_max_mb,
                               whisper_models=WHISPER_MODELS)

    @app.route('/admin/cache/clear', methods=['POST'])
    def admin_cache_clear():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return abort(403)

        cleared = []

        # 1. Reload site settings from DB (flushes any in-memory caches)
        try:
            get_site_settings()
            cleared.append('site_settings')
        except Exception:
            pass

        # 2. Clear text-overrides cache
        try:
            clear_text_overrides_cache()
            cleared.append('text_overrides')
        except Exception:
            pass

        # 3. Clear active-language cache
        try:
            _invalidate_active_lang_cache()
            cleared.append('active_languages')
        except Exception:
            pass

        # 4. Bump css_ver so browsers re-fetch CSS
        try:
            import os as _os
            _css = _os.path.join(dir_path, 'static', 'css', 'tailwind.css')
            if _os.path.exists(_css):
                app.jinja_env.globals['css_ver'] = str(int(_os.path.getmtime(_css)))
                cleared.append('css_ver')
        except Exception:
            pass

        # 5. Re-apply server config from DB
        try:
            load_server_settings(app)
            cleared.append('server_config')
        except Exception:
            pass

        return jsonify({'status': 'ok', 'cleared': cleared,
                        'message': f'Cache cleared: {", ".join(cleared)}'})
    # ────────────────────────────────────────────────────────────────────────

    @app.route("/<lang_code>/admin/text-overrides", methods=['GET', 'POST'])
    @app.route('/admin/text-overrides', methods=['GET', 'POST'])
    def admin_text_overrides():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        is_ajax = request.headers.get('X-Requested-With') == 'XMLHttpRequest'

        # ── Determine working language ──────────────────────────────────────
        # For GET: from query string; for POST: from form field (hidden input).
        if request.method == 'POST':
            lang = request.form.get('lang', 'en').strip() or 'en'
        else:
            lang = request.args.get('lang', 'en').strip() or 'en'

        # ── Editor language restriction ─────────────────────────────────────
        # Editors may only read/write overrides for their assigned languages.
        if is_editor():
            allowed = get_editor_langs()
            if lang not in allowed:
                if request.method == 'POST':
                    if is_ajax:
                        return jsonify({"status": "error",
                                        "message": "Language not permitted"}), 403
                    return abort(403)
                # On GET: silently fall back to first allowed language
                lang = allowed[0] if allowed else 'en'

        # ── Handle POST actions ─────────────────────────────────────────────
        if request.method == "POST":
            action = request.form.get("action", "save")
            with sqlite3.connect("storage/sqlite.db") as conn:
                if action == "delete":
                    original = request.form.get("original_text", "").strip()
                    if original:
                        conn.execute(
                            "DELETE FROM text_overrides WHERE lang = ? AND original_text = ?",
                            (lang, original)
                        )
                        conn.commit()
                elif action == "save_single":
                    original = request.form.get("original_text", "").strip()
                    override = request.form.get("override_text", "").strip()
                    if original and override:
                        conn.execute(
                            "INSERT INTO text_overrides (lang, original_text, override_text) VALUES (?, ?, ?) "
                            "ON CONFLICT(lang, original_text) DO UPDATE SET override_text = ?",
                            (lang, original, override, override)
                        )
                        conn.commit()
                elif action == "save_all":
                    keys = request.form.getlist("orig[]")
                    vals = request.form.getlist("over[]")
                    for orig, ovr in zip(keys, vals):
                        orig = orig.strip()
                        ovr = ovr.strip()
                        if ovr:
                            conn.execute(
                                "INSERT INTO text_overrides (lang, original_text, override_text) VALUES (?, ?, ?) "
                                "ON CONFLICT(lang, original_text) DO UPDATE SET override_text = ?",
                                (lang, orig, ovr, ovr)
                            )
                        else:
                            conn.execute(
                                "DELETE FROM text_overrides WHERE lang = ? AND original_text = ?",
                                (lang, orig)
                            )
                    conn.commit()
                elif action == "delete_all":
                    if is_admin():
                        # Admin: wipe all overrides for the selected language
                        conn.execute("DELETE FROM text_overrides WHERE lang = ?", (lang,))
                    else:
                        # Editor: wipe only their allowed languages' overrides
                        for lc in get_editor_langs():
                            conn.execute("DELETE FROM text_overrides WHERE lang = ?", (lc,))
                    conn.commit()
            clear_text_overrides_cache()
            if is_ajax:
                return jsonify({"status": "ok"}), 200
            return redirect(url("/admin/text-overrides") + "?lang=" + lang)

        # ── GET request: fetch existing overrides for selected language ──────
        with sqlite3.connect("storage/sqlite.db") as conn:
            cursor = conn.execute(
                "SELECT original_text, override_text FROM text_overrides "
                "WHERE lang = ? AND override_text != ''",
                (lang,)
            )
            overrides = cursor.fetchall()

        overrides_dict = {orig: ovr for orig, ovr in overrides}

        # Collect translatable strings with file + line info
        all_texts_raw = _collect_translatable_strings()

        # Strings that contain HTML tags or Jinja expressions cannot be
        # overridden as plain text — skip them from the override UI.
        _html_jinja_pat = re.compile(r'<[a-zA-Z/]|{{|{%')

        # Merge overrides with translatable strings
        all_texts = []
        for t in all_texts_raw:
            text_only = t["text"]
            if _html_jinja_pat.search(text_only):
                continue
            all_texts.append({
                "text": text_only,
                "file": t.get("file", ""),
                "line": t.get("line", 0),
                "override": overrides_dict.get(text_only, "")
            })

        # Build overrides_list for the "Active Overrides" section in the template
        overrides_list = [item for item in all_texts if item["override"]]

        # Determine languages available to the current user for the selector
        if is_editor():
            available_langs = [(lc, _KNOWN_LANGUAGES.get(lc, {}).get("name", lc))
                               for lc in get_editor_langs()]
        else:
            available_langs = [(lc, meta["name"])
                               for lc, meta in _KNOWN_LANGUAGES.items()]

        return render_template(
            "admin/text-overrides.html",
            all_texts=all_texts,
            overrides_list=overrides_list,
            current_lang=lang,
            available_langs=available_langs,
        )
    
    
    @app.route("/admin/text-overrides/empty-database", methods=['POST'])
    @app.route("/<lang_code>/admin/text-overrides/empty-database", methods=['POST'])
    def admin_text_overrides_empty_database(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return abort(403)
        with sqlite3.connect("storage/sqlite.db") as conn:
            conn.execute("DELETE FROM text_overrides")
            conn.commit()
        clear_text_overrides_cache()
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify({"status": "ok", "message": "All overrides deleted"}), 200
        return redirect("/admin/text-overrides")

    @app.route("/admin/text-overrides/export-po")
    @app.route("/<lang_code>/admin/text-overrides/export-po")
    def export_text_overrides_po(lang_code=None):
        # ADD AUTHENTICATION CHECK HERE
        logged_in, r = authenticate()
        if not logged_in:
            return r
        
        try:
            # Get language code from URL parameter or query string
            target_lang = lang_code or request.args.get('lang', 'en')
            
            # Get all translatable strings from templates with file and line info
            all_texts_raw = _collect_translatable_strings()
            
            # Fetch existing overrides from database
            with sqlite3.connect("storage/sqlite.db") as conn:
                cursor = conn.execute(
                    "SELECT original_text, override_text FROM text_overrides WHERE override_text != ''"
                )
                overrides = dict(cursor.fetchall())

            # Remove duplicates from translatable strings but keep first occurrence
            seen = set()
            unique_texts = []
            file_info = {}
            
            for item in all_texts_raw:
                text = item["text"]
                if text not in seen:
                    seen.add(text)
                    unique_texts.append(text)
                    # Store file and line info for this text (first occurrence)
                    file_info[text] = {
                        "file": item.get("file", ""),
                        "line": item.get("line", 0)
                    }

            app.logger.info(f"Found {len(unique_texts)} unique translatable strings")
            app.logger.info(f"Found {len(overrides)} existing overrides")

            # Build PO content
            po_lines = [
                'msgid ""',
                'msgstr ""',
                f'"Project-Id-Version: OnlineConvert\\n"',
                f'"POT-Creation-Date: {datetime.utcnow().strftime("%Y-%m-%d %H:%M+0000")}\\n"',
                '"PO-Revision-Date: \\n"',
                '"Last-Translator: \\n"',
                '"Language-Team: \\n"',
                f'"Language: {target_lang}\\n"',
                '"MIME-Version: 1.0\\n"',
                '"Content-Type: text/plain; charset=UTF-8\\n"',
                '"Content-Transfer-Encoding: 8bit\\n"',
                '"Plural-Forms: nplurals=2; plural=(n != 1);\\n"',
                '"X-Generator: OnlineConvert Admin\\n"',
                ''
            ]

            # Add all translatable strings
            for original_text in unique_texts:
                # Skip empty strings
                if not original_text.strip():
                    continue
                
                # Escape quotes for msgid
                orig_esc = original_text.replace('"', '\\"')
                
                # Get override if exists, otherwise use empty string
                if original_text in overrides:
                    msgstr_text = overrides[original_text]
                    # Add translation status comment
                    po_lines.append('# (translated)')
                else:
                    # No override - msgstr should be EMPTY (not language code)
                    msgstr_text = ''
                    # Add translation status comment
                    po_lines.append('# (not translated)')
                
                # Escape quotes for msgstr
                msgstr_esc = msgstr_text.replace('"', '\\"')
                
                # Add file/line reference as comment if available
                if original_text in file_info:
                    info = file_info[original_text]
                    if info["file"]:
                        # Clean up file path for display
                        display_file = info["file"].replace('templates/', '')
                        po_lines.append(f'#: {display_file}:{info["line"]}')
                
                # Add the translation entry
                po_lines.append(f'msgid "{orig_esc}"')
                po_lines.append(f'msgstr "{msgstr_esc}"')
                po_lines.append('')  # blank line between entries

            po_content = "\n".join(po_lines)

            # Create response with proper headers
            filename = f"translations-{target_lang}.po"
            response = make_response(po_content)
            response.mimetype = "text/plain"
            response.headers["Content-Disposition"] = f'attachment; filename="{filename}"'
            response.headers["Content-Length"] = len(po_content)
            response.headers["Content-Type"] = "text/plain; charset=utf-8"
            
            return response
            
        except Exception as e:
            app.logger.error(f"Error exporting PO: {str(e)}")
            import traceback
            traceback.print_exc()
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "error", "message": str(e)}), 500
            flash(f"Error exporting PO file: {str(e)}", "error")
            return redirect(url_for('admin_text_overrides'))
    
    # ── Language Management ───────────────────────────────────────────────────

    @app.route("/<lang_code>/admin/languages", methods=['GET', 'POST'])
    @app.route('/admin/languages', methods=['GET', 'POST'])
    def admin_languages():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            flash("Access denied: only admins can manage languages.", "error")
            return redirect(url("/admin/pages"))

        if request.method == 'POST':
            active_codes = request.form.getlist('active_langs')
            save_active_languages(active_codes)
            _invalidate_active_lang_cache()
            # Idempotently create PO skeleton files for newly enabled languages
            # so Flask-Babel can pick them up without a manual pybabel step.
            for _lc in active_codes:
                _po_dir = os.path.join(dir_path, 'translations', _lc, 'LC_MESSAGES')
                _po_path = os.path.join(_po_dir, 'messages.po')
                if not os.path.exists(_po_path):
                    try:
                        os.makedirs(_po_dir, exist_ok=True)
                        with open(_po_path, 'w', encoding='utf-8') as _pf:
                            _pf.write(
                                '# Translation: {lc}\n'
                                'msgid ""\n'
                                'msgstr ""\n'
                                '"Content-Type: text/plain; charset=UTF-8\\n"\n'
                                '"Content-Transfer-Encoding: 8bit\\n"\n'
                                '"Language: {lc}\\n"\n'.format(lc=_lc)
                            )
                    except Exception:
                        pass
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "ok"})
            flash("Language settings saved.", "success")
            return redirect(url("/admin/languages"))

        all_langs = get_all_languages_with_status()
        return render_template("admin/languages.html", all_langs=all_langs)

    # ── Editor Management ─────────────────────────────────────────────────────

    @app.route("/<lang_code>/admin/editors", methods=['GET'])
    @app.route('/admin/editors', methods=['GET'])
    def admin_editors():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            flash("Access denied.", "error")
            return redirect(url("/admin/pages"))
        editors = get_all_editors()
        all_langs = get_all_languages_with_status()
        return render_template("admin/editors.html", editors=editors, all_langs=all_langs)

    @app.route("/<lang_code>/admin/editors/new", methods=['GET', 'POST'])
    @app.route('/admin/editors/new', methods=['GET', 'POST'])
    def admin_editors_new():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return redirect(url("/admin/pages"))

        all_langs = get_all_languages_with_status()
        if request.method == 'POST':
            username   = request.form.get('username', '').strip()
            password   = request.form.get('password', '').strip()
            allowed    = request.form.getlist('allowed_languages')
            is_active  = int(request.form.get('is_active', 1))
            if not username or not password:
                flash("Username and password are required.", "error")
                return render_template("admin/editor-form.html", editor=None, all_langs=all_langs)
            if not allowed:
                allowed = ['en']
            ok, err = create_editor(username, password, allowed)
            if ok:
                flash("Editor created successfully.", "success")
                return redirect(url("/admin/editors"))
            flash(f"Error: {err}", "error")
            return render_template("admin/editor-form.html", editor=None, all_langs=all_langs)
        return render_template("admin/editor-form.html", editor=None, all_langs=all_langs)

    @app.route("/<lang_code>/admin/editors/<int:editor_id>/edit", methods=['GET', 'POST'])
    @app.route('/admin/editors/<int:editor_id>/edit', methods=['GET', 'POST'])
    def admin_editors_edit(editor_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return redirect(url("/admin/pages"))
        editor = get_editor_by_id(editor_id)
        if not editor:
            flash("Editor not found.", "error")
            return redirect(url("/admin/editors"))
        all_langs = get_all_languages_with_status()
        if request.method == 'POST':
            data = {
                'username':          request.form.get('username', '').strip(),
                'allowed_languages': request.form.getlist('allowed_languages') or ['en'],
                'is_active':         int(request.form.get('is_active', 1)),
            }
            pwd = request.form.get('password', '').strip()
            if pwd:
                data['password'] = pwd
            ok, err = update_editor(editor_id, data)
            if ok:
                flash("Editor updated.", "success")
                return redirect(url("/admin/editors"))
            flash(f"Error: {err}", "error")
        return render_template("admin/editor-form.html", editor=editor, all_langs=all_langs)

    @app.route("/<lang_code>/admin/editors/<int:editor_id>/delete", methods=['POST'])
    @app.route('/admin/editors/<int:editor_id>/delete', methods=['POST'])
    def admin_editors_delete(editor_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return redirect(url("/admin/pages"))
        delete_editor(editor_id)
        _admin = session.get('admin_username', 'admin')
        log_deletion('editor', entity_id=editor_id, entity_name=str(editor_id),
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'editor_id': editor_id})
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify({"status": "ok"})
        flash("Editor deleted.", "success")
        return redirect(url("/admin/editors"))

    # ── Sub-Admin (Admin Accounts) Management ────────────────────────────────

    def _is_main_admin():
        """True only for the main env-var / admin_profiles admin (not sub-admins)."""
        return bool(session.get('login')) and not session.get('is_sub_admin')

    @app.route('/<lang_code>/admin/admins', methods=['GET'])
    @app.route('/admin/admins', methods=['GET'])
    def admin_admins():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not _is_main_admin():
            flash("Access denied. Only the main admin can manage sub-admin accounts.", "error")
            return redirect(url("/admin"))
        with _db() as c:
            admins = c.execute("SELECT * FROM admin_accounts ORDER BY created_at DESC").fetchall()
        return render_template("admin/admins.html", admins=[dict(a) for a in admins])

    @app.route('/<lang_code>/admin/admins/new', methods=['POST'])
    @app.route('/admin/admins/new', methods=['POST'])
    def admin_admins_new():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not _is_main_admin():
            flash("Access denied.", "error")
            return redirect(url("/admin"))
        username = request.form.get('username', '').strip()
        email = request.form.get('email', '').strip()
        password = request.form.get('password', '').strip()
        confirm = request.form.get('confirm_password', '').strip()
        if not username or not password:
            flash("Username and password are required.", "error")
            return redirect(url("/admin/admins"))
        if password != confirm:
            flash("Passwords do not match.", "error")
            return redirect(url("/admin/admins"))
        if len(password) < 6:
            flash("Password must be at least 6 characters.", "error")
            return redirect(url("/admin/admins"))
        pw_hash = generate_password_hash(password)
        try:
            with _db() as c:
                c.execute(
                    "INSERT INTO admin_accounts (username, email, password_hash) VALUES (?, ?, ?)",
                    (username, email, pw_hash)
                )
                c.commit()
            log_event('admin_create_subadmin', f"Sub-admin '{username}' created")
            flash(f"Sub-admin '{username}' created successfully.", "success")
        except Exception as e:
            if 'UNIQUE' in str(e).upper():
                flash(f"Username '{username}' already exists.", "error")
            else:
                log_event('admin_create_subadmin_error', f"Failed to create sub-admin '{username}': {e}")
                flash("Could not create sub-admin. Please try again.", "error")
        return redirect(url("/admin/admins"))

    @app.route('/<lang_code>/admin/admins/<int:admin_id>/delete', methods=['POST'])
    @app.route('/admin/admins/<int:admin_id>/delete', methods=['POST'])
    def admin_admins_delete(admin_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not _is_main_admin():
            flash("Access denied.", "error")
            return redirect(url("/admin"))
        with _db() as c:
            row = c.execute("SELECT username FROM admin_accounts WHERE id=?", (admin_id,)).fetchone()
            if not row:
                flash("Sub-admin not found.", "error")
                return redirect(url("/admin/admins"))
            c.execute("DELETE FROM admin_accounts WHERE id=?", (admin_id,))
            c.commit()
        log_event('admin_delete_subadmin', f"Sub-admin '{row['username']}' deleted")
        _admin = session.get('admin_username', 'admin')
        log_deletion('admin_account', entity_id=admin_id, entity_name=row['username'],
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'admin_id': admin_id})
        flash(f"Sub-admin '{row['username']}' deleted.", "success")
        return redirect(url("/admin/admins"))

    # ── Page Content Manager ──────────────────────────────────────────────────

    @app.route("/<lang_code>/admin/pages")
    @app.route('/admin/pages')
    def admin_pages():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        converter_category_pages = []
        converter_pages = []
        # converter_pair_pages: grouped by filetype {key, label, icon, pairs:[{id, label}]}
        converter_pair_groups = []
        try:
            ft = {**available_filetypes, **available_hashtypes}
            for key, item in ft.items():
                converter_category_pages.append({
                    "id":    "converter:" + key,
                    "label": key.capitalize() + " Converter",
                    "icon":  item.get('icon-class', 'fa-folder')
                })
                exts    = [e.lower() for e in item.get('ext', [])]
                allowed = [a.lower() for a in item.get('allowed', [])]
                for ext in exts:
                    converter_pages.append({
                        "id":    "converter:" + key + ":" + ext,
                        "label": key.capitalize() + " → " + ext.upper(),
                        "icon":  item.get('icon-class', 'fa-file')
                    })
                # Build source→target pairs
                pairs = []
                sources = allowed if allowed else exts
                for src in sources:
                    for tgt in exts:
                        if src != tgt:
                            pairs.append({
                                "id":    f"converter:{key}:{src}:{tgt}",
                                "label": f"{src.upper()} → {tgt.upper()}",
                                "slug":  f"{src.lower()}-to-{tgt.lower()}",
                            })
                if pairs:
                    converter_pair_groups.append({
                        "key":   key,
                        "label": key.capitalize() + " Converter",
                        "icon":  item.get('icon-class', 'fa-exchange-alt'),
                        "pairs": pairs,
                    })
        except Exception:
            pass

        generator_category_pages = []
        hash_pages = []
        try:
            ht = available_hashtypes
            for key, item in ht.items():
                generator_category_pages.append({
                    "id":    "generator:" + key,
                    "label": key.capitalize() + " Generator",
                    "icon":  item.get('icon-class', 'fa-folder')
                })
                for ext in item.get('ext', []):
                    hash_pages.append({
                        "id":    "hash:" + ext,
                        "label": ext.upper() + " Generator",
                        "icon":  item.get('icon-class', 'fa-hashtag')
                    })
        except Exception:
            pass

        format_definition_pages = []
        seen_formats = set()
        try:
            for key, item in ft.items():
                for ext in list(item.get('ext', [])) + list(item.get('allowed', [])):
                    ext_lower = ext.lower()
                    if ext_lower not in seen_formats:
                        seen_formats.add(ext_lower)
                        format_definition_pages.append({
                            "id":    "format:" + ext_lower,
                            "label": ext.upper() + " Format Definition",
                            "icon":  "fa-file-lines"
                        })
        except Exception:
            pass
        format_definition_pages.sort(key=lambda x: x["label"])

        currency_pages = [
            {"id": "page:currencies",     "label": "All Currencies",                       "icon": "fa-coins"},
            {"id": "page:currency-rates", "label": "Currency Exchange Rates (base)",        "icon": "fa-chart-line"},
            {"id": "page:currency-single","label": "Currency Converter (base-to-target)",   "icon": "fa-exchange-alt"},
        ]

        # Per-base currency pages  (currency:<code>)
        _currencies_data = {}
        currency_base_pages = []
        try:
            _currencies_path = os.path.join(dir_path, "configs", "currencies.json")
            with open(_currencies_path) as _f:
                _currencies_data = json.load(_f)
            for _code, _info in sorted(_currencies_data.items()):
                _name = _info.get("name") or _code.upper()
                currency_base_pages.append({
                    "id":    "currency:" + _code.lower(),
                    "label": _name + " (" + _code.upper() + ")",
                    "icon":  "fa-money-bill-wave",
                    "code":  _code.upper(),
                })
        except Exception:
            pass

        with sqlite3.connect("storage/sqlite.db") as conn:
            cursor = conn.execute(
                "SELECT DISTINCT page_id FROM page_content UNION SELECT DISTINCT page_id FROM page_faqs"
            )
            edited_pages = set(row[0] for row in cursor.fetchall())

        # Per-pair currency pages: show pairs that already exist in DB
        # Plus pass currency metadata so the template can offer a search-to-edit form
        currency_pair_pages = []
        try:
            for _pid in sorted(edited_pages):
                if _pid.startswith("currency:") and _pid.count(":") == 2:
                    _, _base, _target = _pid.split(":", 2)
                    _b_name = (_currencies_data.get(_base.lower()) or {}).get("name") or _base.upper()
                    _t_name = (_currencies_data.get(_target.lower()) or {}).get("name") or _target.upper()
                    currency_pair_pages.append({
                        "id":     _pid,
                        "label":  "{} ({}) → {} ({})".format(_b_name, _base.upper(), _t_name, _target.upper()),
                        "base":   _base.upper(),
                        "target": _target.upper(),
                    })
        except Exception:
            pass

        # Build a lightweight list of all currency codes for the pair-search UI
        currency_code_list = []
        try:
            currency_code_list = sorted(_currencies_data.keys(), key=lambda c: c.upper())
        except Exception:
            pass

        active_langs = get_active_language_codes()

        return render_template("admin/pages.html",
                               static_pages=STATIC_PAGES,
                               converter_category_pages=converter_category_pages,
                               converter_pages=converter_pages,
                               converter_pair_groups=converter_pair_groups,
                               generator_category_pages=generator_category_pages,
                               hash_pages=hash_pages,
                               format_definition_pages=format_definition_pages,
                               currency_pages=currency_pages,
                               currency_base_pages=currency_base_pages,
                               currency_pair_pages=currency_pair_pages,
                               currency_code_list=currency_code_list,
                               edited_pages=edited_pages,
                               active_langs=active_langs)

    @app.route("/<lang_code>/admin/pages/edit", methods=['GET', 'POST'])
    @app.route('/admin/pages/edit', methods=['GET', 'POST'])
    def admin_page_edit():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        page_id = request.args.get('page_id', '') or request.form.get('page_id', '')
        # Read lang from the appropriate source for each HTTP method
        if request.method == 'POST':
            lang = (request.form.get('lang') or request.args.get('lang') or 'en')
        else:
            lang = (request.args.get('lang') or request.form.get('lang') or 'en')

        # ── Editor language enforcement ────────────────────────────────────────
        # Editors may only view/save content for their assigned languages.
        if is_editor():
            allowed = get_editor_langs()
            if lang not in allowed:
                if request.method == 'POST':
                    is_ajax = request.headers.get('X-Requested-With') == 'XMLHttpRequest'
                    if is_ajax:
                        return jsonify({"status": "error", "message": "Language not permitted"}), 403
                    return abort(403)
                # On GET: silently fall back to the first allowed language
                lang = allowed[0] if allowed else 'en'

        if not page_id:
            return redirect(url("/admin/pages"))

        if request.method == "POST":
            action = request.form.get("action", "save")
            is_ajax = request.headers.get('X-Requested-With') == 'XMLHttpRequest'

            if action == "save_fields":
                # Save regular fields
                for f in PAGE_FIELDS:
                    val = request.form.get("field_" + f["key"], "")
                    set_page_content(page_id, f["key"], val, lang)

                # Save format definition
                parts = page_id.split(":")
                fmt_ext = None
                if parts[0] == "converter" and len(parts) >= 3:
                    fmt_ext = parts[2].lower()
                elif parts[0] == "generator" and len(parts) >= 3:
                    fmt_ext = parts[2].lower()
                elif parts[0] == "hash" and len(parts) >= 2:
                    fmt_ext = parts[1].lower()

                if fmt_ext:
                    fmt_def_val = request.form.get("field_format_definition", "")
                    set_page_content("format:" + fmt_ext, "format_definition", fmt_def_val, lang)

                # Save extension-specific fields for converter pages
                if parts[0] == "converter" and len(parts) == 2:  # Category page (e.g., converter:video)
                    filetype_key = parts[1]
                    extensions = get_extensions_for_filetype(filetype_key)
                    for ext in extensions:
                        # Get form values (may be empty strings)
                        ext_title = request.form.get(f"ext_{ext}_title", "")
                        ext_box_text = request.form.get(f"ext_{ext}_box_text", "")
                        ext_icon_type = request.form.get(f"ext_{ext}_icon_type", "none")
                        
                        # Get icon value based on type
                        ext_icon_value = ""
                        if ext_icon_type == "fa":
                            ext_icon_value = request.form.get(f"ext_{ext}_fa_icon", "")
                        elif ext_icon_type == "svg":
                            ext_icon_value = request.form.get(f"ext_{ext}_svg_code", "")
                        elif ext_icon_type == "image":
                            ext_icon_value = request.form.get(f"ext_{ext}_image_url", "")
                        
                        ext_page_id = f"converter:{filetype_key}:{ext}"
                        
                        # Always save these fields (even empty strings)
                        set_page_content(ext_page_id, "extension_title", ext_title, lang)
                        set_page_content(ext_page_id, "extension_box_text", ext_box_text, lang)
                        set_page_content(ext_page_id, "extension_icon_type", ext_icon_type, lang)
                        set_page_content(ext_page_id, "extension_icon_value", ext_icon_value, lang)

                # Save converter option overrides (title + description per property key)
                if parts[0] == "converter":
                    _ft_key_for_opts = parts[1] if len(parts) >= 2 else None
                    if _ft_key_for_opts:
                        _ft_opts = available_filetypes.get(_ft_key_for_opts, {}).get('options', {})
                        for _prop_key in (_ft_opts.get('properties') or {}):
                            _opt_title = request.form.get(f"option_{_prop_key}_title", "")
                            _opt_desc = request.form.get(f"option_{_prop_key}_description", "")
                            set_page_content(page_id, f"option:{_prop_key}:title", _opt_title, lang)
                            set_page_content(page_id, f"option:{_prop_key}:description", _opt_desc, lang)

                if is_ajax:
                    return jsonify({"status": "ok"}), 200
                return redirect(url("/admin/pages/edit") + "?page_id=" + page_id + "&lang=" + lang)

            elif action == "save_faqs":
                questions = request.form.getlist("faq_question[]")
                answers = request.form.getlist("faq_answer[]")
                save_page_faqs(page_id, questions, answers, lang)
                if is_ajax:
                    return jsonify({"status": "ok"}), 200
                return redirect(url("/admin/pages/edit") + "?page_id=" + page_id + "&lang=" + lang)

        # GET request - load existing data
        fields = get_page_all_fields(page_id, lang)
        faqs = get_page_faqs(page_id, lang)

        parts_check = page_id.split(":")
        page_type = parts_check[0]
        filetype_key = parts_check[1] if len(parts_check) >= 2 else None

        # Get extensions for converter category pages
        extensions = []
        if page_type == "converter" and len(parts_check) == 2 and filetype_key:
            extensions = get_extensions_for_filetype(filetype_key)
            # Load existing translations for each extension
            for ext in extensions:
                ext_page_id = f"converter:{filetype_key}:{ext}"
                ext_title_key = f"ext_{ext}_title"
                ext_box_key = f"ext_{ext}_box_text"
                ext_icon_type_key = f"ext_{ext}_icon_type"
                ext_icon_value_key = f"ext_{ext}_icon_value"
                
                # Store in fields with special keys
                fields[ext_title_key] = get_page_content(ext_page_id, "extension_title", lang)
                fields[ext_box_key] = get_page_content(ext_page_id, "extension_box_text", lang)
                fields[ext_icon_type_key] = get_page_content(ext_page_id, "extension_icon_type", lang)
                fields[ext_icon_value_key] = get_page_content(ext_page_id, "extension_icon_value", lang)

        fmt_ext_check = None
        if page_type == "converter" and len(parts_check) >= 3:
            fmt_ext_check = parts_check[2].lower()
        elif page_type == "generator" and len(parts_check) >= 3:
            fmt_ext_check = parts_check[2].lower()
        elif page_type == "hash" and len(parts_check) >= 2:
            fmt_ext_check = parts_check[1].lower()

        if fmt_ext_check:
            if 'format_definition' not in fields or not fields['format_definition']:
                fmt_def_from_format_page = get_page_content("format:" + fmt_ext_check, "format_definition", lang)
                if fmt_def_from_format_page:
                    fields['format_definition'] = fmt_def_from_format_page

        defaults = {}
        if page_type == "converter" and filetype_key:
            ft_config = available_filetypes.get(filetype_key, {})
            defaults['filetype_title'] = ft_config.get('title', '')
            defaults['filetype_description'] = ft_config.get('description', '')
            defaults['filetype_long_description'] = ft_config.get('long-description', '')
        elif page_type == "generator" and filetype_key:
            ht_config = available_hashtypes.get(filetype_key, {})
            defaults['filetype_title'] = ht_config.get('title', '')
            defaults['filetype_description'] = ht_config.get('description', '')
            defaults['filetype_long_description'] = ht_config.get('long-description', '')

        if fmt_ext_check and ('format_definition' not in fields or not fields['format_definition']):
            def_text = definitions.get(fmt_ext_check, definitions.get(fmt_ext_check.upper(), ''))
            if def_text:
                defaults['format_definition'] = def_text

        if page_type == "format" and filetype_key:
            ext_key = filetype_key.lower()
            if 'format_definition' not in fields or not fields['format_definition']:
                def_text = definitions.get(ext_key, definitions.get(ext_key.upper(), ''))
                if def_text:
                    defaults['format_definition'] = def_text

        show_desc_fields = page_type in ('converter', 'generator', 'hash', 'currency')
        show_format_def = (page_type == 'format'
                        or (page_type == 'converter' and len(parts_check) >= 3)
                        or (page_type == 'generator' and len(parts_check) >= 3)
                        or page_type == 'hash')

        # Extension fields only for converter category pages (converter:type, 2 parts)
        show_extension_fields = page_type == "converter" and len(parts_check) == 2 and len(extensions) > 0

        # Build option override fields for converter pages
        option_overrides = []
        if page_type == "converter" and filetype_key:
            _ft_opts = available_filetypes.get(filetype_key, {}).get('options', {})
            for _prop_key, _prop_def in (_ft_opts.get('properties') or {}).items():
                _default_title = _prop_def.get('title', _prop_key)
                _default_desc = _prop_def.get('description', '')
                option_overrides.append({
                    'key': _prop_key,
                    'default_title': _default_title,
                    'default_description': _default_desc,
                    'value_title': get_page_content(page_id, f"option:{_prop_key}:title", lang),
                    'value_description': get_page_content(page_id, f"option:{_prop_key}:description", lang),
                })

        # ── Language list: use active languages from DB ───────────────────
        try:
            langs = get_active_language_codes()
        except Exception:
            langs = ['en']

        # If editor, restrict to their assigned languages only
        if is_editor():
            editor_langs = get_editor_langs()
            langs = [l for l in langs if l in editor_langs]
            if not langs:
                langs = ['en']
            # Enforce lang param is within allowed list
            if lang not in langs:
                lang = langs[0]

        # ── Human-readable page label ─────────────────────────────────────
        page_label = page_id
        for sp in STATIC_PAGES:
            if sp["id"] == page_id:
                page_label = sp["label"]
                break

        if page_label == page_id:
            if page_type == "converter" and len(parts_check) == 4:
                # converter:type:src:tgt  (pair page e.g. converter:video:mp4:mp3)
                src = parts_check[2].upper()
                tgt = parts_check[3].upper()
                ft_title = available_filetypes.get(filetype_key, {}).get('title', filetype_key.capitalize())
                page_label = f"{ft_title}: {src} → {tgt}"
            elif page_type == "converter" and len(parts_check) == 3:
                ft_title = available_filetypes.get(filetype_key, {}).get('title', filetype_key.capitalize())
                page_label = ft_title + " → " + parts_check[2].upper()
            elif page_type == "converter" and len(parts_check) == 2:
                ft_title = available_filetypes.get(filetype_key, {}).get('title', filetype_key.capitalize() + " Converter")
                page_label = ft_title + " (Category)"
            elif page_type == "generator" and len(parts_check) >= 3:
                page_label = parts_check[1].capitalize() + " → " + parts_check[2].upper() + " Generator"
            elif page_type == "generator" and len(parts_check) == 2:
                page_label = parts_check[1].capitalize() + " Generator (Category)"
            elif page_type == "hash":
                page_label = parts_check[1].upper() + " Hash Generator"
            elif page_type == "currency" and len(parts_check) == 3:
                page_label = parts_check[1].upper() + " → " + parts_check[2].upper() + " Currency"
            elif page_type == "currency":
                page_label = parts_check[1].upper() + " Currency Page"
            elif page_type == "format":
                page_label = parts_check[1].upper() + " Format Definition"

        live_url_parts = {}
        if page_type == "converter":
            if len(parts_check) == 4:
                live_url_parts = {"path": f"/convert/{parts_check[2].lower()}-to-{parts_check[3].lower()}", "filetype": filetype_key}
            elif len(parts_check) == 3:
                live_url_parts = {"filetype": filetype_key, "fileformat": parts_check[2].lower()}
            elif len(parts_check) == 2:
                live_url_parts = {"path": f"/converter/{filetype_key}"}
        elif page_type == "currency":
            if len(parts_check) == 3:
                live_url_parts = {"path": f"/currency/{parts_check[1].lower()}-to-{parts_check[2].lower()}"}
            elif len(parts_check) == 2:
                live_url_parts = {"path": f"/currency/{parts_check[1].lower()}"}
        elif page_type == "hash":
            live_url_parts = {"path": f"/hash/{parts_check[1].lower()}-generator"}
        elif page_type == "generator":
            if len(parts_check) >= 3:
                live_url_parts = {"path": f"/hash/{parts_check[2].lower()}-generator"}
            elif len(parts_check) == 2:
                live_url_parts = {"path": "/hash"}
        elif page_type == "page":
            pg = parts_check[1] if len(parts_check) >= 2 else "index"
            live_url_parts = {"path": "/" if pg == "index" else f"/{pg}"}

        return render_template("admin/page-edit.html",
                            page_id=page_id,
                            page_label=page_label,
                            lang=lang,
                            langs=langs,
                            fields=fields,
                            faqs=faqs,
                            page_fields=PAGE_FIELDS,
                            show_desc_fields=show_desc_fields,
                            show_format_def=show_format_def,
                            show_extension_fields=show_extension_fields,
                            extensions=extensions,
                            defaults=defaults,
                            live_url_parts=live_url_parts,
                            option_overrides=option_overrides)
                               
    def get_extensions_for_filetype(filetype_key):
        """Get list of extensions for a given filetype"""
        filetypes_config = available_filetypes.get(filetype_key, {})
        return filetypes_config.get('ext', [])

    @app.route("/<lang_code>/admin/pages/content-csv", methods=['GET'])
    @app.route('/admin/pages/content-csv', methods=['GET'])
    def admin_page_csv_download():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import csv, io
        page_id = request.args.get("page_id", "")
        lang = request.args.get("lang", "en")
        if not page_id:
            return "page_id required", 400
        if is_editor():
            allowed = get_editor_langs()
            if lang not in allowed:
                lang = allowed[0] if allowed else 'en'

        parts = page_id.split(":")
        page_type = parts[0]
        filetype_key = parts[1] if len(parts) >= 2 else None
        is_converter_category = (page_type == "converter" and len(parts) == 2)

        # ── Same visibility flags as the page-edit template ─────────────────
        show_desc_fields  = page_type in ('converter', 'generator', 'hash', 'currency')
        show_format_def   = (page_type == 'format'
                             or (page_type == 'converter' and len(parts) >= 3)
                             or (page_type == 'generator' and len(parts) >= 3)
                             or page_type == 'hash')
        show_ext_fields   = is_converter_category

        # Keys visible per section (mirrors template exactly)
        SEO_KEYS          = {'title', 'description', 'head_html', 'og_image'}
        PAGE_CONTENT_KEYS = {'page_heading', 'page_description', 'before_faq_title', 'before_faq_content'}
        DESC_KEYS         = {'filetype_title', 'filetype_description', 'filetype_long_description', 'extension_box_text'}
        FORMAT_KEYS       = {'format_definition'}

        visible_keys = SEO_KEYS | PAGE_CONTENT_KEYS
        if show_desc_fields:
            visible_keys |= DESC_KEYS
        if show_format_def:
            visible_keys |= FORMAT_KEYS

        # Build ordered list of visible fields keeping PAGE_FIELDS order
        visible_fields = [f for f in PAGE_FIELDS if f['key'] in visible_keys]

        # ── Config-based defaults (same logic as page-edit view) ────────────
        defaults = {}
        if page_type == "converter" and filetype_key:
            ft_config = available_filetypes.get(filetype_key, {})
            defaults['filetype_title']            = ft_config.get('title', '')
            defaults['filetype_description']      = ft_config.get('description', '')
            defaults['filetype_long_description'] = ft_config.get('long-description', '')
        elif page_type == "generator" and filetype_key:
            ht_config = available_hashtypes.get(filetype_key, {})
            defaults['filetype_title']            = ht_config.get('title', '')
            defaults['filetype_description']      = ht_config.get('description', '')
            defaults['filetype_long_description'] = ht_config.get('long-description', '')

        fmt_ext_check = None
        if page_type == "converter" and len(parts) >= 3:
            fmt_ext_check = parts[2].lower()
        elif page_type == "generator" and len(parts) >= 3:
            fmt_ext_check = parts[2].lower()
        elif page_type == "hash" and len(parts) >= 2:
            fmt_ext_check = parts[1].lower()
        elif page_type == "format" and filetype_key:
            fmt_ext_check = filetype_key.lower()

        if fmt_ext_check:
            def_text = definitions.get(fmt_ext_check, definitions.get(fmt_ext_check.upper(), ''))
            if def_text:
                defaults['format_definition'] = def_text

        # ── Compute page-ID hierarchy for fallback lookups ─────────────────
        # Mirrors what converter.html does: pair → target-only → category
        fallback_page_ids = []  # ordered: most-specific first
        if page_type == "converter":
            if len(parts) == 4:   # converter:filetype:source:target
                fallback_page_ids = [
                    f"converter:{parts[1]}:{parts[3]}",   # target-only
                    f"converter:{parts[1]}",               # category
                ]
            elif len(parts) == 3:  # converter:filetype:target
                fallback_page_ids = [f"converter:{parts[1]}"]
        elif page_type == "generator":
            if len(parts) == 3:
                fallback_page_ids = [f"generator:{parts[1]}"]

        with sqlite3.connect(os.path.join(dir_path, "storage", "sqlite.db")) as conn:
            # ── Page content values ─────────────────────────────────────────
            en_rows = conn.execute(
                "SELECT field, value FROM page_content WHERE page_id = ? AND lang = 'en'",
                (page_id,)).fetchall()
            en_map = {r[0]: r[1] for r in en_rows}

            if lang != 'en':
                lang_rows = conn.execute(
                    "SELECT field, value FROM page_content WHERE page_id = ? AND lang = ?",
                    (page_id, lang)).fetchall()
                lang_map = {r[0]: r[1] for r in lang_rows}
            else:
                lang_map = dict(en_map)

            # ── Build parent-page en_maps for fallback ─────────────────────
            fallback_en_maps = []
            for fb_pid in fallback_page_ids:
                fb_rows = conn.execute(
                    "SELECT field, value FROM page_content WHERE page_id = ? AND lang = 'en'",
                    (fb_pid,)).fetchall()
                fallback_en_maps.append({r[0]: r[1] for r in fb_rows})

            # ── FAQs ────────────────────────────────────────────────────────
            en_faqs = conn.execute(
                "SELECT question, answer FROM page_faqs WHERE page_id = ? AND lang = 'en' ORDER BY position",
                (page_id,)).fetchall()
            if lang != 'en':
                lang_faqs = conn.execute(
                    "SELECT question, answer FROM page_faqs WHERE page_id = ? AND lang = ? ORDER BY position",
                    (page_id, lang)).fetchall()
            else:
                lang_faqs = list(en_faqs)

            # ── format_definition fallback from format: page ────────────────
            if show_format_def and fmt_ext_check and 'format_definition' not in defaults:
                fmt_def = conn.execute(
                    "SELECT value FROM page_content WHERE page_id = ? AND field = 'format_definition' AND lang = 'en'",
                    ("format:" + fmt_ext_check,)).fetchone()
                if fmt_def and fmt_def[0]:
                    defaults['format_definition'] = fmt_def[0]

            # ── Extension-specific settings (converter category pages only) ──
            ext_data = {}
            if show_ext_fields:
                extensions = get_extensions_for_filetype(filetype_key)
                for ext in extensions:
                    ext_page_id = f"converter:{filetype_key}:{ext}"
                    ext_en = {r[0]: r[1] for r in conn.execute(
                        "SELECT field, value FROM page_content WHERE page_id = ? AND lang = 'en'",
                        (ext_page_id,)).fetchall()}
                    if lang != 'en':
                        ext_lang = {r[0]: r[1] for r in conn.execute(
                            "SELECT field, value FROM page_content WHERE page_id = ? AND lang = ?",
                            (ext_page_id, lang)).fetchall()}
                    else:
                        ext_lang = dict(ext_en)
                    ext_data[ext] = {"en": ext_en, "lang": ext_lang}

        # ── Helpers: resolve filetype config for the page type ────────────
        if page_type == "converter" and filetype_key:
            ft_config = available_filetypes.get(filetype_key, {})
        elif page_type in ("generator", "hash"):
            ft_config = available_hashtypes.get(filetype_key or "hash", {})
        else:
            ft_config = {}

        is_tool   = ft_config.get("category") == "tool"
        prefix_v  = ft_config.get("_prefix", "")

        def _splitpart(val):
            """Equivalent of Jinja2 splitpart(0, '_')."""
            return val.split("_")[0] if val else val

        def _def(ext):
            """Lookup definitions entry for an extension key."""
            if not ext:
                return ""
            return definitions.get(ext.lower(), definitions.get(ext.upper(), definitions.get(ext, "")))

        # ── Template-mirroring default for every CMS field ──────────────
        def _compute_field_default(field_key, page_id_override=None, parts_override=None, ft_config_override=None):
            """Return the text that the frontend template would render
            when no CMS value has been saved for *field_key*.

            Optional overrides let extension-specific rows reuse this
            helper with a different page_id / parts / config context."""
            _parts  = parts_override  or parts
            _ft     = ft_config_override or ft_config
            _ptype  = _parts[0] if _parts else page_type
            _ftkey  = _parts[1] if len(_parts) >= 2 else filetype_key
            _is_tool = _ft.get("category") == "tool"
            _prefix  = _ft.get("_prefix", "")

            # ── converter 3/4-part pages (converter.html) ────────────
            if _ptype == "converter" and len(_parts) >= 3:
                source_fmt = _parts[2] if len(_parts) == 4 else None
                target_fmt = _parts[3] if len(_parts) == 4 else _parts[2]

                if field_key == "title":
                    if _is_tool and target_fmt:
                        return _splitpart(_prefix.replace("{}", target_fmt))
                    if _is_tool:
                        return _ft.get("type", "")
                    if source_fmt:
                        return f"Free Online {source_fmt.upper()} to {target_fmt.upper()} Converter"
                    if not _prefix:
                        t = f"Free Online {_ftkey.capitalize()} Converter"
                        if target_fmt:
                            t += f" to {target_fmt.upper()}"
                        return t
                    return _splitpart(_prefix.replace("{}", target_fmt).replace("-", " "))

                if field_key == "page_heading":
                    if _is_tool and target_fmt:
                        return _splitpart(_prefix.replace("{}", target_fmt))
                    if _is_tool:
                        return _ft.get("title", "")
                    if source_fmt:
                        return f"Free Online {source_fmt.upper()} to {target_fmt.upper()} Converter"
                    if not _prefix:
                        t = f"Free Online {_ftkey.capitalize()} Converter"
                        if target_fmt:
                            t += f" to {target_fmt.upper()}"
                        return t
                    return _splitpart(_prefix.replace("{}", target_fmt).replace("-", " "))

                if field_key == "description":
                    if source_fmt:
                        return " ".join(filter(None, [_def(source_fmt), _def(target_fmt)]))
                    return _def(target_fmt)

                if field_key == "page_description":
                    if source_fmt:
                        return " ".join(filter(None, [_def(source_fmt), _def(target_fmt)]))
                    return _def(target_fmt)

                if field_key == "extension_box_text":
                    return _def(target_fmt)

                if field_key == "extension_title":
                    if _ftkey in ("device", "webservice"):
                        return f"Convert for {target_fmt}"
                    if _is_tool and _prefix:
                        return _splitpart(_prefix.replace("{}", target_fmt))
                    if not _prefix:
                        return f"{target_fmt} file converter"
                    return _splitpart(_prefix.replace("{}", target_fmt).replace("-", " "))

                if field_key == "format_definition":
                    return defaults.get("format_definition", _def(source_fmt or target_fmt))

                if field_key in ("filetype_title", "filetype_description", "filetype_long_description"):
                    return defaults.get(field_key, "")

            # ── converter category (2-part) → list.html ──────────────
            elif _ptype == "converter" and len(_parts) == 2:
                if field_key == "title":
                    if _is_tool:
                        return _ft.get("type", _ft.get("title", ""))
                    if not _prefix:
                        return f"Free Online {_ftkey.capitalize()} Converter"
                    return _splitpart(_prefix.replace("{}", "").replace("-", " "))

                if field_key == "page_heading":
                    return _ft.get("title", "")

                if field_key == "description":
                    return _ft.get("description", "")

                if field_key == "page_description":
                    return _ft.get("long-description", "")

                if field_key == "filetype_title":
                    return _ft.get("title", "")
                if field_key == "filetype_description":
                    return _ft.get("description", "")
                if field_key == "filetype_long_description":
                    return _ft.get("long-description", "")

            # ── hash pages (2-part) → hash.html ──────────────────────
            elif _ptype == "hash" and len(_parts) >= 2:
                algo_name = _parts[1].replace("-", " ").title()

                if field_key == "title":
                    return f"{algo_name} Hash Generator"
                if field_key == "page_heading":
                    return f"{algo_name} Hash Generator"
                if field_key == "description":
                    return f"Generate {algo_name} hashes online from text or file upload. Free, fast and secure."
                if field_key == "page_description":
                    return f"Generate {algo_name} hashes from text or uploaded files. Optionally provide a shared HMAC key or salt."
                if field_key == "format_definition":
                    return defaults.get("format_definition", "")

            # ── generator list pages (2-part) → list2.html ───────────
            elif _ptype == "generator" and len(_parts) == 2:
                ht_config = available_hashtypes.get(_ftkey or "hash", {})
                gen_tool  = ht_config.get("category") == "tool"

                if field_key == "title":
                    if gen_tool:
                        return ht_config.get("type", ht_config.get("typeee", ""))
                    return f"Free online {_ftkey} converter"
                if field_key == "page_heading":
                    if gen_tool:
                        return ht_config.get("title", "")
                    return f"Online {(_ftkey or '').capitalize()} Converter"
                if field_key == "description":
                    return ht_config.get("description", "")
                if field_key == "page_description":
                    return ht_config.get("long-description", "")
                if field_key == "filetype_title":
                    return ht_config.get("title", "")
                if field_key == "filetype_description":
                    return ht_config.get("description", "")
                if field_key == "filetype_long_description":
                    return ht_config.get("long-description", "")

            return defaults.get(field_key, "")

        def _en_text(field_key):
            """Return best available English text for a field."""
            v = en_map.get(field_key, "")
            if v:
                return v
            for fb_map in fallback_en_maps:
                v = fb_map.get(field_key, "")
                if v:
                    return v
            return _compute_field_default(field_key)

        buf = io.StringIO()
        writer = csv.writer(buf)
        writer.writerow(["field_key", "label", "english_text", "value"])

        # ── SEO section ─────────────────────────────────────────────────────
        seo_fields = [f for f in visible_fields if f['key'] in SEO_KEYS]
        if seo_fields:
            writer.writerow(["## SEO ##", "", "", ""])
            for f in seo_fields:
                writer.writerow([f['key'], f['label'], _en_text(f['key']), lang_map.get(f['key'], "")])

        # ── Page Content section ─────────────────────────────────────────────
        content_fields = [f for f in visible_fields if f['key'] in PAGE_CONTENT_KEYS]
        if content_fields:
            writer.writerow(["## PAGE CONTENT ##", "", "", ""])
            for f in content_fields:
                writer.writerow([f['key'], f['label'], _en_text(f['key']), lang_map.get(f['key'], "")])

        # ── Filetype & Category Descriptions section ─────────────────────────
        if show_desc_fields:
            desc_fields_list = [f for f in visible_fields if f['key'] in DESC_KEYS]
            if desc_fields_list:
                writer.writerow(["## FILETYPE & CATEGORY DESCRIPTIONS ##", "", "", ""])
                for f in desc_fields_list:
                    writer.writerow([f['key'], f['label'], _en_text(f['key']), lang_map.get(f['key'], "")])

        # ── Format Definition section ────────────────────────────────────────
        if show_format_def:
            fmt_fields_list = [f for f in visible_fields if f['key'] in FORMAT_KEYS]
            if fmt_fields_list:
                writer.writerow(["## FORMAT DEFINITION ##", "", "", ""])
                for f in fmt_fields_list:
                    writer.writerow([f['key'], f['label'], _en_text(f['key']), lang_map.get(f['key'], "")])

        # ── Section: FAQs ──────────────────────────────────────────────────
        max_faqs = max(len(en_faqs), len(lang_faqs))
        if max_faqs > 0:
            writer.writerow(["## FAQS ##", "", "", ""])
            for i in range(max_faqs):
                en_q, en_a = en_faqs[i] if i < len(en_faqs) else ("", "")
                lq, la = lang_faqs[i] if i < len(lang_faqs) else ("", "")
                writer.writerow([f"faq_{i+1}_question", f"FAQ {i+1} Question", en_q, lq])
                writer.writerow([f"faq_{i+1}_answer", f"FAQ {i+1} Answer", en_a, la])

        # ── Section: Extension-Specific Settings ───────────────────────────
        if ext_data:
            writer.writerow(["## EXTENSION SETTINGS ##", "", "", ""])
            ext_field_map = [
                ("extension_title",      "Title"),
                ("extension_box_text",   "Box Text"),
                ("extension_icon_type",  "Icon Type"),
                ("extension_icon_value", "Icon Value"),
            ]
            for ext, maps in ext_data.items():
                ext_parts = ["converter", filetype_key, ext]
                for db_field, label_suffix in ext_field_map:
                    csv_key = f"ext_{ext}_{db_field}"
                    label = f"Extension {ext.upper()} {label_suffix}"
                    en_val = maps["en"].get(db_field, "") or _compute_field_default(db_field, parts_override=ext_parts)
                    writer.writerow([csv_key, label, en_val, maps["lang"].get(db_field, "")])

        output = buf.getvalue()
        safe_name = page_id.replace(":", "_") + "_" + lang + ".csv"
        return app.response_class(
            output,
            mimetype="text/csv",
            headers={"Content-Disposition": f"attachment; filename={safe_name}"}
        )

    @app.route("/<lang_code>/admin/pages/content-csv", methods=['POST'])
    @app.route('/admin/pages/content-csv', methods=['POST'])
    def admin_page_csv_upload():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import csv, io, re as _re
        page_id = request.form.get("page_id", "")
        lang = request.form.get("lang", "en")
        if not page_id:
            return jsonify({"status": "error", "message": "page_id required"}), 400
        if is_editor():
            allowed = get_editor_langs()
            if lang not in allowed:
                return jsonify({"status": "error", "message": "Language not permitted"}), 403

        csv_file = request.files.get("csv_file")
        if not csv_file:
            return jsonify({"status": "error", "message": "No CSV file uploaded"}), 400

        parts = page_id.split(":")
        is_converter_category = (parts[0] == "converter" and len(parts) == 2)
        filetype_key = parts[1] if is_converter_category else None

        try:
            content = csv_file.read().decode("utf-8-sig")
            reader = csv.DictReader(io.StringIO(content))
            saved = 0

            # Accumulate FAQs keyed by index so we can bulk-save in order
            faq_questions = {}
            faq_answers = {}

            for row in reader:
                field_key = row.get("field_key", "").strip()
                value = row.get("value", "").strip()
                if not field_key or field_key.startswith("##"):
                    continue

                # ── FAQ rows ───────────────────────────────────────────────
                m = _re.match(r'^faq_(\d+)_(question|answer)$', field_key)
                if m:
                    idx = int(m.group(1))
                    kind = m.group(2)
                    # Only record non-empty values; skip empty to preserve DB
                    if value:
                        if kind == "question":
                            faq_questions[idx] = value
                        else:
                            faq_answers[idx] = value
                        saved += 1
                    continue

                # ── Extension-specific rows ────────────────────────────────
                m_ext = _re.match(r'^ext_([^_]+)_(.+)$', field_key)
                if m_ext and is_converter_category:
                    ext = m_ext.group(1)
                    sub_field = m_ext.group(2)
                    ext_page_id = f"converter:{filetype_key}:{ext}"
                    # Skip empty values — do not wipe existing DB content
                    if value:
                        set_page_content(ext_page_id, sub_field, value, lang)
                        saved += 1
                    continue

                # ── Regular page_content fields ────────────────────────────
                # Skip empty values — do not wipe existing DB content
                if value:
                    set_page_content(page_id, field_key, value, lang)
                    saved += 1

            # Bulk-save FAQs only when at least one value is non-empty
            if faq_questions or faq_answers:
                max_idx = max(list(faq_questions.keys()) + list(faq_answers.keys()))
                questions_list = [faq_questions.get(i, "") for i in range(1, max_idx + 1)]
                answers_list = [faq_answers.get(i, "") for i in range(1, max_idx + 1)]
                save_page_faqs(page_id, questions_list, answers_list, lang)

            is_ajax = request.headers.get('X-Requested-With') == 'XMLHttpRequest'
            if is_ajax:
                return jsonify({"status": "ok", "saved": saved})
            flash(f"CSV imported: {saved} fields saved.", "success")
            return redirect(url("/admin/pages/edit") + "?page_id=" + page_id + "&lang=" + lang)
        except Exception as e:
            return jsonify({"status": "error", "message": str(e)}), 400

    @app.route("/<lang_code>/admin/translations/export")
    @app.route('/admin/translations/export')
    def admin_translations_export():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import csv, io
        lang_filter = request.args.get("lang", "all").strip()

        field_label_map = {f["key"]: f["label"] for f in PAGE_FIELDS}

        with sqlite3.connect(os.path.join(dir_path, "storage", "sqlite.db")) as conn:
            # ── page_content rows ───────────────────────────────────────────
            if lang_filter and lang_filter != "all":
                content_rows = conn.execute(
                    "SELECT page_id, lang, field, value FROM page_content WHERE lang = ? ORDER BY page_id, field",
                    (lang_filter,)).fetchall()
            else:
                content_rows = conn.execute(
                    "SELECT page_id, lang, field, value FROM page_content ORDER BY page_id, lang, field"
                ).fetchall()

            # ── page_faqs rows ─────────────────────────────────────────────
            if lang_filter and lang_filter != "all":
                faq_rows = conn.execute(
                    "SELECT page_id, lang, position, question, answer FROM page_faqs WHERE lang = ? ORDER BY page_id, position",
                    (lang_filter,)).fetchall()
            else:
                faq_rows = conn.execute(
                    "SELECT page_id, lang, position, question, answer FROM page_faqs ORDER BY page_id, lang, position"
                ).fetchall()

        buf = io.StringIO()
        writer = csv.writer(buf)
        writer.writerow(["page_id", "lang", "field_key", "label", "value"])

        # Write page_content rows
        for pid, lng, field_key, value in content_rows:
            label = field_label_map.get(field_key, field_key)
            writer.writerow([pid, lng, field_key, label, value])

        # Write FAQ rows — group by (page_id, lang) to assign sequential numbering
        faq_by_page_lang = {}
        for pid, lng, pos, question, answer in faq_rows:
            faq_by_page_lang.setdefault((pid, lng), []).append((question, answer))
        for (pid, lng), qa_list in sorted(faq_by_page_lang.items()):
            for i, (q, a) in enumerate(qa_list, start=1):
                writer.writerow([pid, lng, f"faq_{i}_question", f"FAQ {i} Question", q])
                writer.writerow([pid, lng, f"faq_{i}_answer",   f"FAQ {i} Answer",   a])

        output = buf.getvalue()
        date_str = datetime.now().strftime("%Y-%m-%d")
        safe_lang = lang_filter if lang_filter and lang_filter != "all" else "all"
        filename = f"translations_{safe_lang}_{date_str}.csv"
        return app.response_class(
            output,
            mimetype="text/csv",
            headers={"Content-Disposition": f"attachment; filename={filename}"}
        )

    @app.route("/<lang_code>/admin/translations/import", methods=['POST'])
    @app.route('/admin/translations/import', methods=['POST'])
    def admin_translations_import():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import csv, io, re as _re

        csv_file = request.files.get("csv_file")
        if not csv_file:
            flash("No CSV file uploaded.", "error")
            return redirect(url("/admin/pages"))

        force_lang = request.form.get("force_lang", "").strip()

        try:
            content = csv_file.read().decode("utf-8-sig")
            reader = csv.DictReader(io.StringIO(content))
            saved = 0

            # Accumulate FAQs: (page_id, lang) -> {idx: {question/answer: value}}
            faq_accumulator = {}

            for row in reader:
                pid = row.get("page_id", "").strip()
                lng = force_lang if force_lang else row.get("lang", "").strip()
                field_key = row.get("field_key", "").strip()
                value = row.get("value", "").strip()

                if not pid or not lng or not field_key or field_key.startswith("##"):
                    continue

                # ── FAQ rows ───────────────────────────────────────────────
                m = _re.match(r'^faq_(\d+)_(question|answer)$', field_key)
                if m:
                    idx = int(m.group(1))
                    kind = m.group(2)
                    if value:
                        key = (pid, lng)
                        faq_accumulator.setdefault(key, {}).setdefault(idx, {})[kind] = value
                        saved += 1
                    continue

                # ── Regular page_content rows ──────────────────────────────
                if value:
                    set_page_content(pid, field_key, value, lng)
                    saved += 1

            # Bulk-save accumulated FAQs
            for (pid, lng), idx_map in faq_accumulator.items():
                max_idx = max(idx_map.keys())
                questions_list = [idx_map.get(i, {}).get("question", "") for i in range(1, max_idx + 1)]
                answers_list   = [idx_map.get(i, {}).get("answer", "")   for i in range(1, max_idx + 1)]
                save_page_faqs(pid, questions_list, answers_list, lng)

            flash(f"Translations imported: {saved} fields saved.", "success")
        except Exception as e:
            flash(f"Import failed: {str(e)}", "error")

        return redirect(url("/admin/pages"))

    @app.route("/<lang_code>/admin/profile", methods=['GET', 'POST'])
    @app.route('/admin/profile', methods=['GET', 'POST'])
    def admin_profile():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        sub_admin_id = session.get('sub_admin_id') if session.get('is_sub_admin') else None

        def _delete_local_avatar(url_or_path):
            """Remove a local avatar file from disk if it lives in static/avatars/."""
            if not url_or_path:
                return
            if url_or_path.startswith('/static/avatars/'):
                avatar_dir = os.path.join(app.root_path, 'static', 'avatars')
                basename = os.path.basename(url_or_path)
                path = os.path.join(avatar_dir, basename)
                try:
                    if os.path.isfile(path):
                        os.remove(path)
                except OSError:
                    pass

        if request.method == 'POST':
            if sub_admin_id:
                username = request.form.get('username', '').strip()
                email = request.form.get('email', '').strip()
                first_name = request.form.get('first_name', '').strip()
                last_name = request.form.get('last_name', '').strip()
                profile_description = request.form.get('profile_description', '').strip()
                profile_picture = request.form.get('profile_picture', '').strip()
                if username:
                    try:
                        with _db() as _c:
                            old_row = _c.execute(
                                "SELECT profile_picture FROM admin_accounts WHERE id=?", (sub_admin_id,)
                            ).fetchone()
                            old_pic = (old_row['profile_picture'] if old_row else '') or ''
                            if old_pic and old_pic != profile_picture:
                                _delete_local_avatar(old_pic)
                            _c.execute(
                                "UPDATE admin_accounts SET username=?, email=?, first_name=?, last_name=?, profile_description=?, profile_picture=? WHERE id=?",
                                (username, email, first_name, last_name, profile_description, profile_picture, sub_admin_id)
                            )
                            _c.commit()
                        session['sub_admin_username'] = username
                    except Exception as _e:
                        if 'UNIQUE' in str(_e).upper():
                            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                                return jsonify({"status": "error", "message": "That username is already taken."}), 409
                            flash("That username is already taken.", "error")
                            return redirect(url("/admin/profile"))
                        raise
            else:
                data = {
                    'username': request.form.get('username', '').strip(),
                    'first_name': request.form.get('first_name', '').strip(),
                    'last_name': request.form.get('last_name', '').strip(),
                    'email': request.form.get('email', '').strip(),
                    'profile_description': request.form.get('profile_description', '').strip(),
                }
                pic = request.form.get('profile_picture', '').strip()
                if pic is not None:
                    old_profile = get_admin_profile()
                    old_pic = (old_profile.get('profile_picture') if old_profile else '') or ''
                    if old_pic and old_pic != pic:
                        _delete_local_avatar(old_pic)
                    data['profile_picture'] = pic
                update_admin_profile(data)
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "ok"})
            return redirect(url("/admin/profile"))
        profile = get_admin_profile()
        sub_admin_profile = None
        if sub_admin_id:
            with _db() as _c:
                row = _c.execute("SELECT * FROM admin_accounts WHERE id=?", (sub_admin_id,)).fetchone()
                sub_admin_profile = dict(row) if row else None
        return render_template('admin/profile.html', profile=profile,
                               sub_admin_profile=sub_admin_profile)

    @app.route("/<lang_code>/admin/profile/password", methods=['POST'])
    @app.route('/admin/profile/password', methods=['POST'])
    def admin_profile_password():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        current = request.form.get('current_password', '')
        new_pw = request.form.get('new_password', '')
        if len(new_pw) < 6:
            return jsonify({"status": "error", "message": "New password must be at least 6 characters."}), 400
        sub_admin_id = session.get('sub_admin_id') if session.get('is_sub_admin') else None
        if sub_admin_id:
            with _db() as _c:
                sub = _c.execute("SELECT * FROM admin_accounts WHERE id=?", (sub_admin_id,)).fetchone()
            if not sub or not check_password_hash(sub['password_hash'], current):
                return jsonify({"status": "error", "message": "Current password is incorrect."}), 400
            with _db() as _c:
                _c.execute("UPDATE admin_accounts SET password_hash=? WHERE id=?",
                           (generate_password_hash(new_pw), sub_admin_id))
                _c.commit()
        else:
            profile = get_admin_profile()
            if profile['password_hash']:
                valid = check_password_hash(profile['password_hash'], current)
            else:
                valid = (current == os.getenv('admin_password', 'admin123'))
            if not valid:
                return jsonify({"status": "error", "message": "Current password is incorrect."}), 400
            update_admin_profile({'password_hash': generate_password_hash(new_pw)})
        return jsonify({"status": "ok"})

    @app.route("/<lang_code>/admin/profile/2fa/setup", methods=['GET', 'POST'])
    @app.route('/admin/profile/2fa/setup', methods=['GET', 'POST'])
    def admin_2fa_setup():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        # Only the main (env-var) admin may touch the main-admin TOTP secret.
        # Sub-admins use /admin/profile/sub-2fa/setup instead.
        if not _is_main_admin():
            return jsonify({"status": "error", "message": "Access denied."}), 403
        profile = get_admin_profile()
        if request.method == 'GET':
            secret = pyotp.random_base32()
            provisioning_uri = pyotp.totp.TOTP(secret).provisioning_uri(
                name=profile.get('email') or profile['username'],
                issuer_name='OnlineConvert Admin'
            )
            qr = qrcode.QRCode(version=1, box_size=6, border=2)
            qr.add_data(provisioning_uri)
            qr.make(fit=True)
            img = qr.make_image(fill_color="black", back_color="white")
            buffer = io.BytesIO()
            img.save(buffer, format='PNG')
            qr_base64 = base64.b64encode(buffer.getvalue()).decode('utf-8')
            return jsonify({
                "status": "ok",
                "secret": secret,
                "qr_code": f"data:image/png;base64,{qr_base64}"
            })
        else:
            code = request.form.get('code', '').strip()
            secret = request.form.get('secret', '').strip()
            if not code or not secret:
                return jsonify({"status": "error", "message": "Code and secret are required."}), 400
            totp = pyotp.TOTP(secret)
            if totp.verify(code, valid_window=1):
                update_admin_profile({'totp_secret': secret, 'totp_enabled': 1})
                return jsonify({"status": "ok"})
            else:
                return jsonify({"status": "error", "message": "Invalid verification code. Please try again."}), 400

    @app.route("/<lang_code>/admin/profile/2fa/disable", methods=['POST'])
    @app.route('/admin/profile/2fa/disable', methods=['POST'])
    def admin_2fa_disable():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        # Only the main (env-var) admin may disable the main-admin TOTP.
        # Sub-admins use /admin/profile/sub-2fa/disable instead.
        if not _is_main_admin():
            return jsonify({"status": "error", "message": "Access denied."}), 403
        password = request.form.get('password', '')
        profile = get_admin_profile()
        if profile['password_hash']:
            valid = check_password_hash(profile['password_hash'], password)
        else:
            valid = (password == os.getenv('admin_password', 'admin123'))
        if not valid:
            return jsonify({"status": "error", "message": "Incorrect password."}), 400
        update_admin_profile({'totp_secret': '', 'totp_enabled': 0})
        return jsonify({"status": "ok"})

    @app.route('/<lang_code>/admin/profile/2fa/emergency-disable', methods=['POST'])
    @app.route('/admin/profile/2fa/emergency-disable', methods=['POST'])
    def admin_2fa_emergency_disable():
        """No password required — lets the MAIN admin unlock themselves after a device loss.
        Sub-admins and editors are explicitly denied; they have their own endpoints."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        # Must be the env-var (main) admin — sub-admins and editors are not allowed.
        if not _is_main_admin():
            return jsonify({"status": "error", "message": "Access denied."}), 403
        update_admin_profile({'totp_secret': '', 'totp_enabled': 0})
        log_event('admin_emergency_disable_2fa', "Main admin used emergency 2FA disable")
        return jsonify({"status": "ok"})

    # ── Sub-admin 2FA setup / disable ─────────────────────────────────────────

    @app.route('/<lang_code>/admin/profile/sub-2fa/setup', methods=['GET', 'POST'])
    @app.route('/admin/profile/sub-2fa/setup', methods=['GET', 'POST'])
    def admin_sub_2fa_setup():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not session.get('is_sub_admin'):
            return jsonify({"status": "error", "message": "Only sub-admins use this endpoint."}), 403
        sub_id = session.get('sub_admin_id')
        if request.method == 'GET':
            secret = pyotp.random_base32()
            with _db() as _c:
                sub = _c.execute("SELECT username, email FROM admin_accounts WHERE id=?", (sub_id,)).fetchone()
            name = (sub['email'] or sub['username']) if sub else 'sub-admin'
            provisioning_uri = pyotp.totp.TOTP(secret).provisioning_uri(
                name=name, issuer_name='OnlineConvert Admin'
            )
            qr = qrcode.QRCode(version=1, box_size=6, border=2)
            qr.add_data(provisioning_uri)
            qr.make(fit=True)
            img = qr.make_image(fill_color="black", back_color="white")
            buffer = io.BytesIO()
            img.save(buffer, format='PNG')
            qr_base64 = base64.b64encode(buffer.getvalue()).decode('utf-8')
            return jsonify({"status": "ok", "secret": secret, "qr_code": f"data:image/png;base64,{qr_base64}"})
        else:
            code = request.form.get('code', '').strip()
            secret = request.form.get('secret', '').strip()
            if not code or not secret:
                return jsonify({"status": "error", "message": "Code and secret are required."}), 400
            totp = pyotp.TOTP(secret)
            if totp.verify(code, valid_window=1):
                with _db() as _c:
                    _c.execute("UPDATE admin_accounts SET totp_secret=?, totp_enabled=1 WHERE id=?", (secret, sub_id))
                    _c.commit()
                return jsonify({"status": "ok"})
            return jsonify({"status": "error", "message": "Invalid verification code. Please try again."}), 400

    @app.route('/<lang_code>/admin/profile/sub-2fa/disable', methods=['POST'])
    @app.route('/admin/profile/sub-2fa/disable', methods=['POST'])
    def admin_sub_2fa_disable():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not session.get('is_sub_admin'):
            return jsonify({"status": "error", "message": "Only sub-admins use this endpoint."}), 403
        sub_id = session.get('sub_admin_id')
        password = request.form.get('password', '')
        with _db() as _c:
            sub = _c.execute("SELECT * FROM admin_accounts WHERE id=?", (sub_id,)).fetchone()
        if not sub or not check_password_hash(sub['password_hash'], password):
            return jsonify({"status": "error", "message": "Incorrect password."}), 400
        with _db() as _c:
            _c.execute("UPDATE admin_accounts SET totp_secret='', totp_enabled=0 WHERE id=?", (sub_id,))
            _c.commit()
        return jsonify({"status": "ok"})

    # ── Main admin: secretly reset any sub-admin's 2FA ────────────────────────

    @app.route('/<lang_code>/admin/admins/<int:admin_id>/reset-2fa', methods=['POST'])
    @app.route('/admin/admins/<int:admin_id>/reset-2fa', methods=['POST'])
    def admin_admins_reset_2fa(admin_id):
        """Only the main (env-var) admin can call this — silently wipes a sub-admin's TOTP."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not _is_main_admin():
            if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                return jsonify({"status": "error", "message": "Access denied."}), 403
            flash("Access denied.", "error")
            return redirect(url("/admin"))
        with _db() as _c:
            row = _c.execute("SELECT username FROM admin_accounts WHERE id=?", (admin_id,)).fetchone()
            if not row:
                if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
                    return jsonify({"status": "error", "message": "Sub-admin not found."}), 404
                flash("Sub-admin not found.", "error")
                return redirect(url("/admin/admins"))
            _c.execute("UPDATE admin_accounts SET totp_secret='', totp_enabled=0 WHERE id=?", (admin_id,))
            _c.commit()
        log_event('admin_reset_subadmin_2fa', f"Main admin reset 2FA for sub-admin '{row['username']}'")
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify({"status": "ok"})
        flash(f"2FA reset for sub-admin '{row['username']}'.", "success")
        return redirect(url("/admin/admins"))

    # ── User 2FA reset ─────────────────────────────────────────────────────────

    @app.route('/<lang_code>/admin/users/<int:user_id>/reset-2fa', methods=['POST'])
    @app.route('/admin/users/<int:user_id>/reset-2fa', methods=['POST'])
    def admin_reset_user_2fa(user_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        # Only full admins (main or sub-admin) may reset a user's 2FA — editors are denied.
        if not session.get('login'):
            flash("Access denied.", "error")
            return redirect(url("/admin/users"))
        with _db() as _c:
            row = _c.execute("SELECT username FROM users WHERE id=?", (user_id,)).fetchone()
            if not row:
                flash("User not found.", "error")
                return redirect(url("/admin/users"))
            _c.execute(
                "UPDATE users SET user_totp_secret='', user_totp_enabled=0 WHERE id=?",
                (user_id,)
            )
            _c.commit()
        log_event('admin_reset_user_2fa', f"Admin reset 2FA for user #{user_id}", user_id=user_id)
        flash(f"2FA has been reset for {row['username']}.", "success")
        return redirect(url_for('admin_edit_user', user_id=user_id))

    @app.route("/<lang_code>/admin/verify-2fa", methods=['GET', 'POST'])
    @app.route('/admin/verify-2fa', methods=['GET', 'POST'])
    def admin_verify_2fa():
        if not session.get('pending_2fa'):
            return redirect(url("/admin/login"))
        if request.method == 'GET':
            error = session.pop('error', None)
            return render_template("admin/verify-2fa.html", error=error)
        else:
            code = request.form.get('code', '').strip()
            sub_admin_id = session.get('pending_sub_admin_id')
            if sub_admin_id:
                with _db() as _c:
                    sub = _c.execute("SELECT * FROM admin_accounts WHERE id=?", (sub_admin_id,)).fetchone()
                if sub and sub['totp_secret']:
                    totp = pyotp.TOTP(sub['totp_secret'])
                    if totp.verify(code, valid_window=1):
                        _id = sub_admin_id
                        session.pop('pending_2fa', None)
                        session.pop('pending_user', None)
                        session.pop('pending_sub_admin_id', None)
                        session['login'] = True
                        session['is_sub_admin'] = True
                        session['sub_admin_id'] = _id
                        return redirect(url("/admin"))
            else:
                profile = get_admin_profile()
                if profile['totp_secret']:
                    totp = pyotp.TOTP(profile['totp_secret'])
                    if totp.verify(code, valid_window=1):
                        session.pop('pending_2fa', None)
                        session.pop('pending_user', None)
                        session['login'] = True
                        return redirect(url("/admin"))
            session['error'] = "Invalid verification code."
            return redirect(url("/admin/verify-2fa"))

    @app.route("/<lang_code>/admin/upload-image", methods=['POST'])
    @app.route('/admin/upload-image', methods=['POST'])
    def admin_upload_image():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        f = request.files.get('file')
        if not f:
            return jsonify({"status": "error", "message": "No file provided."}), 400
        upload_dir = os.path.join(app.static_folder or 'static', 'site-media')
        os.makedirs(upload_dir, exist_ok=True)
        filename = f"{int(time.time())}_{secure_filename(f.filename)}"
        f.save(os.path.join(upload_dir, filename))
        return jsonify({"url": f"/static/site-media/{filename}"})

    @app.route("/<lang_code>/admin/upload-avatar", methods=['POST'])
    @app.route('/admin/upload-avatar', methods=['POST'])
    def admin_upload_avatar():
        """Dedicated avatar upload for admin / sub-admin profile pictures.
        Saves to static/avatars/, deletes old avatar if supplied, and returns the public URL."""
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error", "message": "Not authenticated."}), 403
        f = request.files.get('file')
        if not f or not f.filename:
            return jsonify({"status": "error", "message": "No file provided."}), 400
        safe_name = secure_filename(f.filename)
        if not safe_name:
            return jsonify({"status": "error", "message": "Invalid filename."}), 400
        ext = os.path.splitext(safe_name)[1].lower()
        if ext not in ('.jpg', '.jpeg', '.png', '.gif', '.webp'):
            return jsonify({"status": "error", "message": f"Unsupported format '{ext}'. Use JPG, PNG, GIF or WebP."}), 400
        # Use app.root_path for a reliable absolute path regardless of working directory
        avatar_dir = os.path.join(app.root_path, 'static', 'avatars')
        try:
            os.makedirs(avatar_dir, exist_ok=True)
        except OSError as _e:
            return jsonify({"status": "error", "message": f"Cannot create avatar directory: {_e}"}), 500
        # Delete old avatar file if the caller supplied its URL
        old_url = request.form.get('old_url', '').strip()
        if old_url and old_url.startswith('/static/avatars/'):
            old_basename = os.path.basename(old_url)
            old_path = os.path.join(avatar_dir, old_basename)
            try:
                if os.path.isfile(old_path):
                    os.remove(old_path)
            except OSError:
                pass
        filename = f"admin_{int(time.time())}_{os.urandom(4).hex()}{ext}"
        save_path = os.path.join(avatar_dir, filename)
        try:
            f.save(save_path)
        except Exception as _e:
            return jsonify({"status": "error", "message": f"Failed to save file: {_e}"}), 500
        return jsonify({"status": "ok", "url": f"/static/avatars/{filename}"})

    @app.route("/<lang_code>/api/reviews", methods=['POST', 'OPTIONS'], subdomain="<subdomain>")
    @app.route('/api/reviews', methods=['POST', 'OPTIONS'], subdomain="<subdomain>")
    @app.route("/<lang_code>/api/reviews", methods=['POST', 'OPTIONS'])
    @app.route('/api/reviews', methods=['POST', 'OPTIONS'])
    def api_submit_review(lang_code=None, subdomain=None):
        if request.method == 'OPTIONS':
            resp = app.make_default_options_response()
            return resp
        origin = request.headers.get('Origin', '')
        referer = request.headers.get('Referer', '')
        if origin and not _is_allowed_origin(origin):
            return jsonify({"status": "error", "message": "Forbidden."}), 403
        if referer and not _is_allowed_origin(referer):
            return jsonify({"status": "error", "message": "Forbidden."}), 403
        if not origin and not referer:
            xhr = request.headers.get('X-Requested-With', '')
            if xhr != 'XMLHttpRequest':
                return jsonify({"status": "error", "message": "Forbidden."}), 403
        page_id = request.form.get('page_id', '').strip()
        reviewer_name = request.form.get('reviewer_name', '').strip()
        rating = request.form.get('rating', '')
        review_text = request.form.get('review_text', '').strip()
        if not page_id:
            return jsonify({"status": "error", "message": "Page ID is required."}), 400
        if not reviewer_name:
            return jsonify({"status": "error", "message": "Name is required."}), 400
        try:
            rating = int(rating)
            if rating < 1 or rating > 5:
                raise ValueError
        except (ValueError, TypeError):
            return jsonify({"status": "error", "message": "Rating must be between 1 and 5."}), 400
        if len(review_text) > 1000:
            return jsonify({"status": "error", "message": "Review text must be under 1000 characters."}), 400
        add_review(page_id, reviewer_name, rating, review_text)
        return jsonify({"status": "ok", "message": "Thank you for your rating!"})

    @app.route("/<lang_code>/api/reviews/<path:page_id>", methods=['GET', 'OPTIONS'], subdomain="<subdomain>")
    @app.route('/api/reviews/<path:page_id>', methods=['GET', 'OPTIONS'], subdomain="<subdomain>")
    @app.route("/<lang_code>/api/reviews/<path:page_id>", methods=['GET', 'OPTIONS'])
    @app.route('/api/reviews/<path:page_id>', methods=['GET', 'OPTIONS'])
    def api_get_reviews(page_id, lang_code=None, subdomain=None):
        offset = request.args.get('offset', 0, type=int)
        limit = request.args.get('limit', 10, type=int)
        reviews = get_reviews(page_id, approved_only=True, limit=limit, offset=offset)
        summary = get_review_summary(page_id)
        return jsonify({"reviews": reviews, "summary": summary})

    @app.route("/<lang_code>/admin/reviews")
    @app.route('/admin/reviews')
    def admin_reviews():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        status = request.args.get('status', '')
        search = request.args.get('search', '')
        page = request.args.get('page', 1, type=int)
        per_page = 25
        offset = (page - 1) * per_page
        reviews = get_all_reviews(status=status or None, search=search or None, limit=per_page, offset=offset)
        total = get_reviews_count(status=status or None, search=search or None)
        total_pages = (total + per_page - 1) // per_page
        return render_template("admin/reviews.html",
                               reviews=reviews, status=status, search=search,
                               page=page, total_pages=total_pages, total=total)

    @app.route("/<lang_code>/api/admin/reviews/add", methods=['POST'])
    @app.route('/api/admin/reviews/add', methods=['POST'])
    def api_admin_add_review(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error", "message": "Unauthorized"}), 401
        page_id = request.form.get('page_id', '').strip()
        reviewer_name = request.form.get('reviewer_name', '').strip()
        rating = request.form.get('rating', '5')
        review_text = request.form.get('review_text', '').strip()
        featured = 1 if request.form.get('featured') else 0
        if not page_id:
            return jsonify({"status": "error", "message": "Page ID is required."}), 400
        if not reviewer_name:
            return jsonify({"status": "error", "message": "Reviewer name is required."}), 400
        try:
            rating = int(rating)
            if rating < 1 or rating > 5:
                raise ValueError
        except (ValueError, TypeError):
            return jsonify({"status": "error", "message": "Rating must be between 1 and 5."}), 400
        if not review_text:
            return jsonify({"status": "error", "message": "Review text is required."}), 400
        add_review(page_id, reviewer_name, rating, review_text, featured=featured)
        return jsonify({"status": "ok", "message": "Review added successfully."})

    @app.route("/<lang_code>/admin/reviews/<int:review_id>/toggle", methods=['POST'])
    @app.route('/admin/reviews/<int:review_id>/toggle', methods=['POST'])
    def admin_toggle_review(review_id, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error"}), 401
        toggle_review_approval(review_id)
        return jsonify({"status": "ok"})

    @app.route("/<lang_code>/admin/reviews/<int:review_id>/delete", methods=['POST'])
    @app.route('/admin/reviews/<int:review_id>/delete', methods=['POST'])
    def admin_delete_review(review_id, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error"}), 401
        delete_review(review_id)
        _admin = session.get('admin_username', 'admin')
        log_deletion('review', entity_id=review_id, entity_name=str(review_id),
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'review_id': review_id})
        return jsonify({"status": "ok"})

    @app.route("/<lang_code>/admin/backups")
    @app.route('/admin/backups')
    def admin_backups():
        logged_in, r = authenticate()
        if not logged_in:
            return r

        # Get the root directory (where app.py is)
        current_dir = os.path.dirname(os.path.abspath(__file__))  # /app/routes/
        root_dir = os.path.dirname(current_dir)  # /app/

        backup_dir = os.path.join(root_dir, 'backups')
        os.makedirs(backup_dir, exist_ok=True)

        backups = []
        for f in sorted(os.listdir(backup_dir), reverse=True):
            if f.endswith('.zip'):
                path = os.path.join(backup_dir, f)
                size = os.path.getsize(path)
                if size < 1024:
                    size_str = f"{size} B"
                elif size < 1024 * 1024:
                    size_str = f"{size / 1024:.1f} KB"
                else:
                    size_str = f"{size / (1024 * 1024):.1f} MB"
                backups.append({"name": f, "size": size_str, "date": f.replace("backup_", "").replace(".zip", "").replace("_", " ", 1).replace("-", "/", 2)})

        from helpers import get_site_settings
        site = get_site_settings()
        sync_settings = {
            'local_sync_enabled':       site.get('local_sync_enabled', '0'),
            'local_sync_path':          site.get('local_sync_path', ''),
            'local_sync_interval_secs': site.get('local_sync_interval_secs', '60'),
            'ftp_sync_enabled':         site.get('ftp_sync_enabled', '0'),
            'ftp_host':                 site.get('ftp_host', ''),
            'ftp_port':                 site.get('ftp_port', '21'),
            'ftp_user':                 site.get('ftp_user', ''),
            'ftp_remote_path':          site.get('ftp_remote_path', '/'),
            'ftp_interval_mins':        site.get('ftp_interval_mins', '60'),
        }
        return render_template("admin/backups.html", backups=backups, sync_settings=sync_settings)

    @app.route("/<lang_code>/admin/backups/create", methods=['POST'])
    @app.route('/admin/backups/create', methods=['POST'])
    def admin_create_backup(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error"}), 401
        import zipfile
        import shutil

        # Get the root directory (where app.py is)
        current_dir = os.path.dirname(os.path.abspath(__file__))  # /app/routes/
        root_dir = os.path.dirname(current_dir)  # /app/

        backup_dir = os.path.join(root_dir, 'backups')
        os.makedirs(backup_dir, exist_ok=True)

        timestamp = datetime.now().strftime("%Y-%m-%d_%H-%M-%S")
        backup_name = f"backup_{timestamp}.zip"
        backup_path = os.path.join(backup_dir, backup_name)

        # Database is in root/storage/sqlite.db
        db_path = os.path.join(root_dir, 'storage', 'sqlite.db')

        # Folders to include in backup
        uploads_dir     = os.path.join(root_dir, 'static', 'uploads')
        blogs_dir       = os.path.join(root_dir, 'static', 'blogs')
        avatars_dir     = os.path.join(root_dir, 'static', 'avatars')
        site_media_dir  = os.path.join(root_dir, 'static', 'site-media')
        screenshots_dir = os.path.join(root_dir, 'static', 'screenshots')
        bg_removed_dir  = os.path.join(root_dir, 'static', 'bg-removed')

        backup_folders = [
            uploads_dir,
            blogs_dir,
            avatars_dir,     # user + admin profile pictures
            site_media_dir,  # site logo, favicon, and other branding images
            screenshots_dir, # generated screenshots
            bg_removed_dir,  # background-removed images
        ]

        with zipfile.ZipFile(backup_path, 'w', zipfile.ZIP_DEFLATED) as zf:
            if os.path.exists(db_path):
                # Create temp file in backup directory
                temp_db = os.path.join(backup_dir, f"temp_{timestamp}.db")

                try:
                    # Backup SQLite database to temp file
                    with sqlite3.connect(db_path) as src:
                        with sqlite3.connect(temp_db) as dst:
                            src.backup(dst)

                    # Add to ZIP with path storage/sqlite.db
                    zf.write(temp_db, 'storage/sqlite.db')
                finally:
                    # Clean up temp file
                    if os.path.exists(temp_db):
                        os.remove(temp_db)

            # Add each folder (uploads, blogs, avatars, site-media)
            for folder in backup_folders:
                if os.path.isdir(folder):
                    for root, dirs, files in os.walk(folder):
                        for file in files:
                            file_path = os.path.join(root, file)
                            arcname = os.path.relpath(file_path, root_dir)
                            zf.write(file_path, arcname)

        size = os.path.getsize(backup_path)
        if size < 1024:
            size_str = f"{size} B"
        elif size < 1024 * 1024:
            size_str = f"{size / 1024:.1f} KB"
        else:
            size_str = f"{size / (1024 * 1024):.1f} MB"

        return jsonify({"status": "ok", "name": backup_name, "size": size_str})

    @app.route("/<lang_code>/admin/backups/upload", methods=['POST'])
    @app.route('/admin/backups/upload', methods=['POST'])
    def admin_upload_backup(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error"}), 401
        if 'file' not in request.files:
            return jsonify({"status": "error", "message": "No file uploaded"}), 400
        f = request.files['file']
        if not f.filename or not f.filename.endswith('.zip'):
            return jsonify({"status": "error", "message": "Only ZIP files are allowed"}), 400

        # Get the root directory
        current_dir = os.path.dirname(os.path.abspath(__file__))
        root_dir = os.path.dirname(current_dir)

        backup_dir = os.path.join(root_dir, 'backups')
        os.makedirs(backup_dir, exist_ok=True)

        filename = secure_filename(f.filename)
        if not filename.endswith('.zip'):
            filename += '.zip'
        backup_path = os.path.join(backup_dir, filename)
        f.save(backup_path)

        size = os.path.getsize(backup_path)
        if size < 1024:
            size_str = f"{size} B"
        elif size < 1024 * 1024:
            size_str = f"{size / 1024:.1f} KB"
        else:
            size_str = f"{size / (1024 * 1024):.1f} MB"

        return jsonify({"status": "ok", "name": filename, "size": size_str})

    @app.route("/<lang_code>/admin/backups/download/<filename>")
    @app.route('/admin/backups/download/<filename>')
    def admin_download_backup(filename, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r

        filename = secure_filename(filename)

        # Get the root directory
        current_dir = os.path.dirname(os.path.abspath(__file__))
        root_dir = os.path.dirname(current_dir)

        backup_dir = os.path.join(root_dir, 'backups')
        backup_path = os.path.join(backup_dir, filename)

        if not os.path.exists(backup_path):
            return jsonify({"status": "error", "message": "Backup not found"}), 404

        return send_from_directory(backup_dir, filename, as_attachment=True)

    @app.route("/<lang_code>/admin/backups/delete/<filename>", methods=['POST'])
    @app.route('/admin/backups/delete/<filename>', methods=['POST'])
    def admin_delete_backup(filename, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error"}), 401

        filename = secure_filename(filename)

        # Get the root directory
        current_dir = os.path.dirname(os.path.abspath(__file__))
        root_dir = os.path.dirname(current_dir)

        backup_dir = os.path.join(root_dir, 'backups')
        backup_path = os.path.join(backup_dir, filename)

        if os.path.exists(backup_path):
            _fsize = os.path.getsize(backup_path)
            os.remove(backup_path)
            _admin = session.get('admin_username', 'admin')
            log_deletion('backup', entity_id=filename, entity_name=filename,
                         actor_username=_admin, actor_role='admin',
                         file_size_bytes=_fsize,
                         extra_meta={'filename': filename})

        return jsonify({"status": "ok"})

    @app.route("/<lang_code>/admin/backups/restore/<filename>", methods=['POST'])
    @app.route('/admin/backups/restore/<filename>', methods=['POST'])
    def admin_restore_backup(filename, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({"status": "error"}), 401
        import zipfile

        filename = secure_filename(filename)

        # Get the root directory
        current_dir = os.path.dirname(os.path.abspath(__file__))
        root_dir = os.path.dirname(current_dir)

        backup_dir = os.path.join(root_dir, 'backups')
        backup_path = os.path.join(backup_dir, filename)

        if not os.path.exists(backup_path):
            return jsonify({"status": "error", "message": "Backup not found"}), 404

        live_db = os.path.join(root_dir, 'storage', 'sqlite.db')
        tmp_db = live_db + '.restore_tmp'

        with zipfile.ZipFile(backup_path, 'r') as zf:
            for member in zf.namelist():
                if not (member.startswith('storage/') or member.startswith('static/')):
                    continue
                if member == 'storage/sqlite.db':
                    # Extract to a temp file first; use sqlite3.backup() to
                    # safely overwrite the live database without corrupting
                    # active connections.
                    try:
                        with zf.open(member) as src:
                            with open(tmp_db, 'wb') as dst:
                                dst.write(src.read())
                        with sqlite3.connect(tmp_db) as src_conn:
                            with sqlite3.connect(live_db) as dst_conn:
                                src_conn.backup(dst_conn)
                    finally:
                        try:
                            os.remove(tmp_db)
                        except OSError:
                            pass
                else:
                    target = os.path.join(root_dir, member)
                    os.makedirs(os.path.dirname(target), exist_ok=True)
                    with zf.open(member) as src, open(target, 'wb') as dst:
                        dst.write(src.read())

        # Invalidate all in-process caches so restored data is picked up
        # immediately without restarting the server.
        _helpers_module._page_content_cache_time = 0
        _helpers_module._page_content_cache = {}
        _helpers_module._text_overrides_cache_time = {}
        _helpers_module._text_overrides_cache = {}
        try:
            _helpers_module._invalidate_active_lang_cache()
        except Exception:
            pass

        return jsonify({"status": "ok", "message": "Backup restored successfully. Please refresh the page."})


    # =========================================================================
    # Backup Sync & FTP Export/Restore routes
    # =========================================================================

    @app.route("/<lang_code>/admin/backups/sync-settings", methods=['POST'])
    @app.route('/admin/backups/sync-settings', methods=['POST'])
    def admin_sync_settings_save(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        from helpers import set_site_setting
        keys = [
            'local_sync_enabled', 'local_sync_path', 'local_sync_interval_secs',
            'ftp_sync_enabled', 'ftp_host', 'ftp_port', 'ftp_user',
            'ftp_password', 'ftp_remote_path', 'ftp_interval_mins',
        ]
        data = request.get_json(silent=True) or {}
        errors = []

        # Validate numeric fields before persisting
        try:
            interval_secs = int(data.get('local_sync_interval_secs', 60))
            if interval_secs < 10:
                interval_secs = 10
            if interval_secs > 86400:
                interval_secs = 86400
            data['local_sync_interval_secs'] = str(interval_secs)
        except (ValueError, TypeError):
            data['local_sync_interval_secs'] = '60'
            errors.append('local_sync_interval_secs must be a number; reset to 60')

        try:
            ftp_port = int(data.get('ftp_port', 21))
            if ftp_port < 1 or ftp_port > 65535:
                ftp_port = 21
            data['ftp_port'] = str(ftp_port)
        except (ValueError, TypeError):
            data['ftp_port'] = '21'
            errors.append('ftp_port must be a number; reset to 21')

        try:
            ftp_interval = int(data.get('ftp_interval_mins', 60))
            if ftp_interval < 1:
                ftp_interval = 1
            if ftp_interval > 1440:
                ftp_interval = 1440
            data['ftp_interval_mins'] = str(ftp_interval)
        except (ValueError, TypeError):
            data['ftp_interval_mins'] = '60'
            errors.append('ftp_interval_mins must be a number; reset to 60')

        for k in keys:
            if k in data:
                set_site_setting(k, str(data[k]))
        return jsonify({'status': 'ok', 'warnings': errors})

    @app.route("/<lang_code>/admin/backups/sync-now", methods=['POST'])
    @app.route('/admin/backups/sync-now', methods=['POST'])
    def admin_sync_now(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        from helpers import get_site_settings
        from backup_sync import run_combined_sync
        settings = get_site_settings()
        result = run_combined_sync(settings)
        if not result:
            return jsonify({
                'status': 'nothing',
                'results': {},
                'message': 'No sync is enabled. Enable Local Path Sync or FTP Sync and click Save Settings first.',
            })
        any_ok = any(v.get('status') in ('ok', 'partial') for v in result.values())
        all_errors = []
        for label, res in result.items():
            for e in (res.get('errors') or []):
                all_errors.append(f'{label.upper()}: {e}')
        return jsonify({
            'status': 'ok' if any_ok else 'error',
            'results': result,
            'errors': all_errors,
        })

    @app.route("/<lang_code>/admin/backups/test-local", methods=['POST'])
    @app.route('/admin/backups/test-local', methods=['POST'])
    def admin_test_local(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        from helpers import get_site_settings
        from backup_sync import LocalSyncer
        data = request.get_json(silent=True) or {}
        path = (data.get('local_sync_path') or '').strip()
        if not path:
            settings = get_site_settings()
            path = (settings.get('local_sync_path') or '').strip()
        if not path:
            return jsonify({'status': 'error', 'message': 'Local sync path is not configured.'})
        result = LocalSyncer(path).test_connection()
        return jsonify(result)

    @app.route("/<lang_code>/admin/backups/force-sync", methods=['POST'])
    @app.route('/admin/backups/force-sync', methods=['POST'])
    def admin_force_sync(lang_code=None):
        """Clear local incremental state then run a full sync immediately."""
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        from helpers import get_site_settings
        from backup_sync import run_combined_sync, clear_local_state
        settings = get_site_settings()
        path = (settings.get('local_sync_path') or '').strip()
        if path:
            clear_local_state(path)
        result = run_combined_sync(settings)
        if not result:
            return jsonify({
                'status': 'nothing',
                'results': {},
                'message': 'No sync is enabled. Enable Local Path Sync and click Save Settings first.',
            })
        any_ok = any(v.get('status') in ('ok', 'partial') for v in result.values())
        all_errors = []
        for label, res in result.items():
            for e in (res.get('errors') or []):
                all_errors.append(f'{label.upper()}: {e}')
        return jsonify({
            'status': 'ok' if any_ok else 'error',
            'results': result,
            'errors': all_errors,
        })

    @app.route("/<lang_code>/admin/backups/test-ftp", methods=['POST'])
    @app.route('/admin/backups/test-ftp', methods=['POST'])
    def admin_test_ftp(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        data = request.get_json(silent=True) or {}
        host = (data.get('ftp_host') or '').strip()
        if not host:
            return jsonify({'status': 'error', 'message': 'FTP host is required'})
        from backup_sync import FTPSyncer
        syncer = FTPSyncer(
            host=host,
            port=int(data.get('ftp_port') or 21),
            user=(data.get('ftp_user') or '').strip(),
            password=(data.get('ftp_password') or '').strip(),
            remote_path=(data.get('ftp_remote_path') or '/').strip(),
        )
        result = syncer.test_connection()
        return jsonify(result)

    @app.route("/<lang_code>/admin/backups/restore-local", methods=['POST'])
    @app.route('/admin/backups/restore-local', methods=['POST'])
    def admin_restore_local(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        from helpers import get_site_settings
        from backup_sync import LocalSyncer
        settings = get_site_settings()
        path = (settings.get('local_sync_path') or '').strip()
        if not path:
            return jsonify({'status': 'error', 'message': 'Local sync path is not configured.'})
        result = LocalSyncer(path).restore_from_source()
        try:
            import helpers as _helpers_module
            _helpers_module._page_content_cache_time = 0
            _helpers_module._page_content_cache = {}
            _helpers_module._text_overrides_cache_time = {}
            _helpers_module._text_overrides_cache = {}
            try:
                _helpers_module._invalidate_active_lang_cache()
            except Exception:
                pass
        except Exception:
            pass
        msg = 'Restore from local path completed.'
        if result.get('errors'):
            msg += ' Errors: ' + '; '.join(result['errors'])
        return jsonify({'status': result['status'], 'message': msg, 'details': result})

    @app.route("/<lang_code>/admin/backups/restore-ftp", methods=['POST'])
    @app.route('/admin/backups/restore-ftp', methods=['POST'])
    def admin_restore_ftp(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        from helpers import get_site_settings
        from backup_sync import FTPSyncer
        settings = get_site_settings()
        host = (settings.get('ftp_host') or '').strip()
        if not host:
            return jsonify({'status': 'error', 'message': 'FTP host is not configured.'})
        syncer = FTPSyncer(
            host=host,
            port=int(settings.get('ftp_port') or 21),
            user=(settings.get('ftp_user') or '').strip(),
            password=(settings.get('ftp_password') or '').strip(),
            remote_path=(settings.get('ftp_remote_path') or '/').strip(),
        )
        result = syncer.restore_from_source()
        try:
            import helpers as _helpers_module
            _helpers_module._page_content_cache_time = 0
            _helpers_module._page_content_cache = {}
            _helpers_module._text_overrides_cache_time = {}
            _helpers_module._text_overrides_cache = {}
            try:
                _helpers_module._invalidate_active_lang_cache()
            except Exception:
                pass
        except Exception:
            pass
        msg = 'Restore from FTP completed.'
        if result.get('errors'):
            msg += ' Errors: ' + '; '.join(result['errors'])
        return jsonify({'status': result['status'], 'message': msg, 'details': result})

    @app.route("/<lang_code>/admin/backups/sync-status", methods=['GET'])
    @app.route('/admin/backups/sync-status', methods=['GET'])
    def admin_sync_status(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'status': 'error'}), 401
        from backup_sync import _load_state
        state = _load_state()
        return jsonify({
            'status': 'ok',
            'local_last_sync': state.get('local_last_sync'),
            'local_last_error': state.get('local_last_error'),
            'ftp_last_sync': state.get('ftp_last_sync'),
            'ftp_last_error': state.get('ftp_last_error'),
            'restore_log': state.get('restore_log', [])[:5],
        })

    @app.route('/admin/test-email', methods=['POST'])
    def admin_test_email():
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify(success=False, error="Not authenticated"), 401
        to_email = request.json.get('email', '').strip()
        if not to_email:
            return jsonify(success=False, error="No email address provided.")
        ok, err = send_email(
            to_email,
            "Test Email from OnlineConvert Admin",
            "<h2>Test Email</h2><p>SMTP is configured correctly. Password reset emails will work.</p>",
            "Test Email\n\nSMTP is configured correctly."
        )
        if ok:
            return jsonify(success=True, message=f"Test email sent to {to_email}")
        return jsonify(success=False, error=err)

    @app.route("/<lang_code>/admin/logout")
    @app.route('/admin/logout')
    def admin_logout():
        session.clear()
        return redirect(url("/admin/login"))

    @app.route("/<lang_code>/admin/files")
    @app.route('/admin/files')
    def admin_files():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        tab = request.args.get('tab', 'uploads')

        # ── Uploads tab params ─────────────────────────────────────────
        page     = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 50, type=int)
        if per_page not in (10, 25, 50, 100):
            per_page = 50
        search   = request.args.get('search', '').strip()
        sort_by  = request.args.get('sort_by', 'uploaded_at')
        sort_dir = request.args.get('sort_dir', 'desc')
        include_deleted = request.args.get('deleted', '0') == '1'

        files, total = get_all_file_uploads_admin(
            page=page, per_page=per_page, include_deleted=include_deleted,
            search=search, sort_by=sort_by, sort_dir=sort_dir
        )
        total_pages = max(1, (total + per_page - 1) // per_page)

        # ── Conversions tab params ─────────────────────────────────────
        conv_page     = request.args.get('conv_page', 1, type=int)
        conv_per_page = request.args.get('conv_per_page', 50, type=int)
        if conv_per_page not in (10, 25, 50, 100):
            conv_per_page = 50
        conv_search   = request.args.get('conv_search', '').strip()
        conv_sort_by  = request.args.get('conv_sort_by', 'created_at')
        conv_sort_dir = request.args.get('conv_sort_dir', 'desc')
        include_deleted_convs = request.args.get('conv_deleted', '0') == '1'

        _conv_sort_cols = {
            'created_at': 'conv.created_at', 'id': 'conv.id',
            'status': 'conv.status', 'input_format': 'conv.input_format',
            'output_format': 'conv.output_format', 'username': 'u.username',
        }
        _conv_sd   = 'ASC' if conv_sort_dir.lower() == 'asc' else 'DESC'
        _conv_sort = _conv_sort_cols.get(conv_sort_by, 'conv.created_at')

        conv_where_parts, conv_params = [], []
        if not include_deleted_convs:
            conv_where_parts.append("conv.status != 'deleted'")
        if conv_search:
            conv_where_parts.append("(conv.input_format LIKE ? OR conv.output_format LIKE ? OR u.username LIKE ? OR u.email LIKE ?)")
            conv_params += [f'%{conv_search}%'] * 4
        conv_where = ("WHERE " + " AND ".join(conv_where_parts)) if conv_where_parts else ""
        conv_offset = (conv_page - 1) * conv_per_page

        with _db() as c:
            conv_total = c.execute(
                f"SELECT COUNT(*) FROM conversions conv LEFT JOIN users u ON u.id=conv.user_id {conv_where}",
                conv_params
            ).fetchone()[0]
            conv_rows = c.execute(
                f"""SELECT conv.*, u.username, u.email
                    FROM conversions conv LEFT JOIN users u ON u.id = conv.user_id
                    {conv_where}
                    ORDER BY {_conv_sort} {_conv_sd} LIMIT ? OFFSET ?""",
                conv_params + [conv_per_page, conv_offset]
            ).fetchall()
            pending_blogs = c.execute("SELECT COUNT(*) FROM blogs WHERE status='pending_review'").fetchone()[0]

        conversions = [dict(r) for r in conv_rows]
        conv_total_pages = max(1, (conv_total + conv_per_page - 1) // conv_per_page)
        storage = _get_storage_stats()
        return render_template('admin/files.html',
                               files=files, total=total, page=page,
                               total_pages=total_pages, per_page=per_page,
                               search=search, sort_by=sort_by, sort_dir=sort_dir,
                               include_deleted=include_deleted,
                               include_deleted_convs=include_deleted_convs,
                               conversions=conversions, conv_total=conv_total,
                               conv_page=conv_page, conv_total_pages=conv_total_pages,
                               conv_per_page=conv_per_page, conv_search=conv_search,
                               conv_sort_by=conv_sort_by, conv_sort_dir=conv_sort_dir,
                               storage=storage, fmt_bytes=_fmt_bytes,
                               pending_blogs=pending_blogs, active_tab=tab)

    @app.route('/admin/files/detail/<int:upload_id>')
    def admin_file_detail(upload_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute(
                """SELECT fu.*, u.username, u.email, u.plan_id,
                          conv.id AS conv_id, conv.input_format, conv.output_format,
                          conv.status AS conv_status, conv.output_path AS conv_output_path,
                          conv.created_at AS conv_created_at, conv.completed_at AS conv_completed_at
                   FROM file_uploads fu
                   LEFT JOIN users u ON u.id = fu.user_id
                   LEFT JOIN conversions conv ON conv.upload_id = fu.id
                   WHERE fu.id=?""", (upload_id,)
            ).fetchone()
        if not row:
            return jsonify(error="Not found"), 404
        return jsonify(dict(row))

    @app.route('/admin/conversions/detail/<int:conv_id>')
    def admin_conversion_detail(conv_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute(
                """SELECT conv.*, u.username, u.email, u.plan_id,
                          fu.file_name AS orig_file_name, fu.file_path AS orig_file_path,
                          fu.file_size AS orig_file_size, fu.uploaded_at AS orig_uploaded_at
                   FROM conversions conv
                   LEFT JOIN users u ON u.id = conv.user_id
                   LEFT JOIN file_uploads fu ON fu.id = conv.upload_id
                   WHERE conv.id=?""", (conv_id,)
            ).fetchone()
        if not row:
            return jsonify(error="Not found"), 404
        return jsonify(dict(row))

    @app.route('/admin/files/delete/<int:upload_id>', methods=['POST'])
    def admin_delete_file(upload_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            _fu_row = c.execute(
                "SELECT fu.file_path, fu.file_size, fu.user_id AS owner_id, u.username"
                " FROM file_uploads fu LEFT JOIN users u ON u.id = fu.user_id"
                " WHERE fu.id=?", (upload_id,)
            ).fetchone()
        _fname = os.path.basename((_fu_row['file_path'] or '') if _fu_row else '') or str(upload_id)
        _fsize = _fu_row['file_size'] if _fu_row else None
        _owner = _fu_row['username'] if _fu_row else ''
        _owner_id = _fu_row['owner_id'] if _fu_row else None
        success = admin_delete_file_upload(upload_id, also_delete_conversions=True)
        if success:
            _admin = session.get('admin_username', 'admin')
            log_deletion('file', entity_id=upload_id, entity_name=_fname,
                         actor_username=_admin, actor_role='admin',
                         file_size_bytes=_fsize,
                         extra_meta={'upload_id': upload_id, 'file_owner': _owner},
                         entity_owner_user_id=_owner_id)
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify(success=success)
        flash("File deleted." if success else "File not found.", "success" if success else "error")
        return redirect(url_for('admin_files'))

    @app.route('/admin/files/purge/<int:upload_id>', methods=['POST'])
    def admin_purge_file(upload_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        upload_dir = app.config.get('UPLOAD_DIR', 'storage/uploads')
        with _db() as c:
            row = c.execute("SELECT file_path FROM file_uploads WHERE id=?", (upload_id,)).fetchone()
            if row:
                try:
                    import shutil as _sh
                    full_folder = os.path.join(upload_dir, os.path.dirname(row['file_path']))
                    if os.path.isdir(full_folder):
                        _sh.rmtree(full_folder)
                    elif os.path.exists(os.path.join(upload_dir, row['file_path'])):
                        os.remove(os.path.join(upload_dir, row['file_path']))
                except Exception:
                    pass
                c.execute("DELETE FROM conversions WHERE upload_id=?", (upload_id,))
                c.execute("DELETE FROM file_uploads WHERE id=?", (upload_id,))
                c.commit()
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify(success=row is not None)
        flash("Record permanently deleted." if row else "File not found.", "success" if row else "error")
        return redirect(url_for('admin_files', deleted=1))

    @app.route('/admin/files/delete-all', methods=['POST'])
    def admin_delete_all_files():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        also_conv = request.form.get('also_delete_conversions') == '1'
        with _db() as c:
            _all_fu = c.execute(
                "SELECT fu.id, fu.file_path, fu.file_size, fu.user_id AS owner_id FROM file_uploads fu WHERE fu.deleted_at IS NULL"
            ).fetchall()
        _admin = session.get('admin_username', 'admin')
        count = admin_delete_all_file_uploads(also_delete_conversions=also_conv)
        for _fu in _all_fu:
            _fn = os.path.basename(_fu['file_path'] or '') or str(_fu['id'])
            log_deletion('file', entity_id=_fu['id'], entity_name=_fn,
                         actor_username=_admin, actor_role='admin',
                         file_size_bytes=_fu['file_size'],
                         extra_meta={'upload_id': _fu['id'], 'bulk': True},
                         entity_owner_user_id=_fu['owner_id'])
        msg = f"{count} file(s) deleted."
        if also_conv:
            msg += " Conversion history also deleted."
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify(success=True, deleted=count, also_conv=also_conv)
        flash(msg, "success")
        return redirect(url_for('admin_files'))

    @app.route('/admin/files/empty-all', methods=['POST'])
    def admin_empty_all_files():
        """Wipe all file_uploads rows, all conversions rows, and all physical upload files."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import shutil as _sh
        upload_dir = app.config.get('UPLOAD_DIR', os.path.join(dir_path, 'storage', 'uploads'))
        deleted_files = 0
        try:
            if os.path.isdir(upload_dir):
                for entry in os.listdir(upload_dir):
                    entry_path = os.path.join(upload_dir, entry)
                    try:
                        if os.path.isdir(entry_path):
                            _sh.rmtree(entry_path, ignore_errors=True)
                        else:
                            os.remove(entry_path)
                        deleted_files += 1
                    except Exception:
                        pass
        except Exception:
            pass
        with _db() as c:
            conv_count = c.execute("SELECT COUNT(*) FROM conversions").fetchone()[0]
            up_count = c.execute("SELECT COUNT(*) FROM file_uploads").fetchone()[0]
            c.execute("DELETE FROM conversions")
            c.execute("DELETE FROM file_uploads")
            c.commit()
        flash(
            f"Done: {conv_count} conversion record(s) and {up_count} upload record(s) "
            f"removed from database; {deleted_files} storage item(s) deleted from disk.",
            "success"
        )
        return redirect(url_for('admin_files'))

    @app.route('/admin/files/delete-selected', methods=['POST'])
    def admin_delete_selected_files():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        also_conv = request.form.get('also_delete_conversions') == '1'
        ids = request.form.getlist('ids')
        _admin = session.get('admin_username', 'admin')
        deleted = 0
        for fid in ids:
            try:
                fid = int(fid)
            except (ValueError, TypeError):
                continue
            with _db() as c:
                _fu = c.execute(
                    "SELECT file_path, file_size, user_id AS owner_id FROM file_uploads WHERE id=?", (fid,)
                ).fetchone()
            _fname = os.path.basename((_fu['file_path'] or '') if _fu else '') or str(fid)
            _fsize = _fu['file_size'] if _fu else None
            _owner_id = _fu['owner_id'] if _fu else None
            if admin_delete_file_upload(fid, also_delete_conversions=also_conv):
                deleted += 1
                log_deletion('file', entity_id=fid, entity_name=_fname,
                             actor_username=_admin, actor_role='admin',
                             file_size_bytes=_fsize,
                             extra_meta={'upload_id': fid, 'bulk': True},
                             entity_owner_user_id=_owner_id)
        msg = f"{deleted} file(s) deleted."
        if also_conv:
            msg += " Conversion history also deleted."
        flash(msg, "success")
        keep_deleted = request.form.get('keep_deleted') == '1'
        return redirect(url_for('admin_files', deleted=1) if keep_deleted else url_for('admin_files'))

    @app.route('/admin/files/purge-selected', methods=['POST'])
    def admin_purge_selected_files():
        """Hard-delete selected file records (used from the ?deleted=1 view)."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        also_conv = request.form.get('also_delete_conversions') == '1'
        ids = request.form.getlist('ids')
        upload_dir = app.config.get('UPLOAD_DIR', 'storage/uploads')
        purged = 0
        with _db() as c:
            for fid_raw in ids:
                try:
                    fid = int(fid_raw)
                except (ValueError, TypeError):
                    continue
                row = c.execute("SELECT file_path FROM file_uploads WHERE id=?", (fid,)).fetchone()
                if row:
                    try:
                        import shutil as _sh2
                        full_folder = os.path.join(upload_dir, os.path.dirname(row['file_path']))
                        if os.path.isdir(full_folder):
                            _sh2.rmtree(full_folder)
                        elif os.path.exists(os.path.join(upload_dir, row['file_path'])):
                            os.remove(os.path.join(upload_dir, row['file_path']))
                    except Exception:
                        pass
                    if also_conv:
                        c.execute("DELETE FROM conversions WHERE upload_id=?", (fid,))
                    c.execute("DELETE FROM file_uploads WHERE id=?", (fid,))
                    purged += 1
            c.commit()
        msg = f"{purged} record(s) permanently deleted."
        if also_conv:
            msg += " Conversion history also deleted."
        flash(msg, "success")
        return redirect(url_for('admin_files', deleted=1))

    @app.route('/admin/files/purge-all', methods=['POST'])
    def admin_purge_all_files():
        """Hard-delete ALL soft-deleted file records (used from the ?deleted=1 view)."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        also_conv = request.form.get('also_delete_conversions') == '1'
        upload_dir = app.config.get('UPLOAD_DIR', 'storage/uploads')
        with _db() as c:
            rows = c.execute("SELECT id, file_path FROM file_uploads WHERE deleted_at IS NOT NULL").fetchall()
            for row in rows:
                try:
                    import shutil as _sh3
                    full_folder = os.path.join(upload_dir, os.path.dirname(row['file_path'] or ''))
                    if os.path.isdir(full_folder):
                        _sh3.rmtree(full_folder)
                except Exception:
                    pass
            if rows:
                ids = [r['id'] for r in rows]
                if also_conv:
                    c.execute(
                        "DELETE FROM conversions WHERE upload_id IN ({})".format(','.join('?' * len(ids))),
                        ids
                    )
                c.execute(
                    "DELETE FROM file_uploads WHERE id IN ({})".format(','.join('?' * len(ids))),
                    ids
                )
            c.commit()
        msg = f"{len(rows)} record(s) permanently deleted."
        if also_conv:
            msg += " Conversion history also deleted."
        flash(msg, "success")
        return redirect(url_for('admin_files', deleted=1))

    @app.route('/admin/files/download/<int:upload_id>')
    def admin_download_file(upload_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute("SELECT * FROM file_uploads WHERE id=?", (upload_id,)).fetchone()
        if not row:
            abort(404)
        row = dict(row)
        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):
            flash("File not found on disk.", "error")
            return redirect(url_for('admin_files'))
        return send_file(full_path, as_attachment=True, download_name=row.get('file_name') or os.path.basename(full_path))

    @app.route('/admin/files/conversions')
    @app.route('/admin/conversions')
    def admin_conversions():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        page = request.args.get('page', 1, type=int)
        per_page = 50
        q = request.args.get('q', '').strip()
        status_filter = request.args.get('status', '').strip()
        offset = (page - 1) * per_page
        conditions = []
        params = []
        if q:
            conditions.append("(conv.id LIKE ? OR u.username LIKE ? OR conv.input_format LIKE ? OR conv.output_format LIKE ?)")
            params += [f"%{q}%", f"%{q}%", f"%{q}%", f"%{q}%"]
        if status_filter:
            conditions.append("conv.status = ?")
            params.append(status_filter)
        else:
            # Exclude soft-deleted conversions from the default view
            conditions.append("conv.status != 'deleted'")
        where_clause = ("WHERE " + " AND ".join(conditions)) if conditions else ""
        import datetime as _dt
        with _db() as c:
            total = c.execute(
                f"SELECT COUNT(*) FROM conversions conv LEFT JOIN users u ON u.id = conv.user_id {where_clause}",
                params
            ).fetchone()[0]
            rows = c.execute(
                f"""SELECT conv.*, u.username,
                       CAST(ROUND(
                           (JULIANDAY(COALESCE(conv.completed_at, datetime('now'))) -
                            JULIANDAY(conv.created_at)) * 86400
                       ) AS INTEGER) AS duration_seconds
                    FROM conversions conv
                    LEFT JOIN users u ON u.id = conv.user_id
                    {where_clause}
                    ORDER BY conv.id DESC LIMIT ? OFFSET ?""",
                params + [per_page, offset]
            ).fetchall()
        files = [dict(r) for r in rows]
        total_pages = max(1, (total + per_page - 1) // per_page)
        storage = _get_storage_stats()
        pending_blogs = 0
        try:
            with _db() as c:
                pending_blogs = c.execute("SELECT COUNT(*) FROM blogs WHERE status='pending_review'").fetchone()[0]
        except Exception:
            pass
        return render_template('admin/conversions.html',
                               files=files, total=total, page=page,
                               total_pages=total_pages, storage=storage,
                               fmt_bytes=_fmt_bytes, pending_blogs=pending_blogs,
                               q=q, status_filter=status_filter)

    @app.route('/admin/conversions/download/<int:conv_id>')
    def admin_download_conversion(conv_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute("SELECT * FROM conversions WHERE id=?", (conv_id,)).fetchone()
        if not row:
            abort(404)
        conv = dict(row)
        output_path = conv.get('output_path', '')
        upload_dir = app.config.get('UPLOAD_DIR', 'storage/uploads')
        if not output_path:
            job_id = conv.get('job_id', '')
            if job_id:
                return redirect(f"/output/{job_id}")
            flash("Converted file path not stored for this conversion.", "error")
            return redirect(url_for('admin_files', tab='conversions'))
        full_path = os.path.join(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 not found on disk.", "error")
            return redirect(url_for('admin_files', tab='conversions'))
        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('/admin/conversions/delete/<int:conv_id>', methods=['POST'])
    def admin_delete_conversion(conv_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import shutil as _sh
        _udir = app.config.get('UPLOAD_DIR', '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 c.job_id, c.output_path, c.upload_id, c.file_name, c.user_id AS owner_id, fu.file_path AS upload_file_path, fu.file_size AS file_size "
                "FROM conversions c LEFT JOIN file_uploads fu ON fu.id = c.upload_id "
                "WHERE c.id=?", (conv_id,)
            ).fetchone()
            if row:
                row = dict(row)
                # Delete all output files (output_path may be absolute or relative; may be newline-separated)
                output_path_raw = row['output_path'] or ''
                for fpath in output_path_raw.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:
                        pass
                # Delete upload record and remove its physical file
                if row['upload_id']:
                    try:
                        up_file = _res(row['upload_file_path'] or '')
                        if up_file:
                            up_folder = os.path.dirname(up_file)
                            if os.path.isfile(up_file):
                                os.remove(up_file)
                            if os.path.isdir(up_folder) and not os.listdir(up_folder):
                                _sh.rmtree(up_folder, ignore_errors=True)
                    except Exception:
                        pass
                    c.execute("DELETE FROM file_uploads WHERE id=?", (row['upload_id'],))
                # Hard-delete the conversion record
                c.execute("DELETE FROM conversions WHERE id=?", (conv_id,))
                # Remove shared links and access grants so the job URL stops working
                _jid = row['job_id']
                if _jid:
                    c.execute("DELETE FROM shared_links WHERE job_id=?", (_jid,))
                    c.execute("DELETE FROM job_access_grants WHERE job_id=?", (_jid,))
                c.commit()
                _fname = row.get('file_name') or os.path.basename(row.get('upload_file_path') or '') or str(conv_id)
                _admin = session.get('admin_username', 'admin')
                log_deletion('conversion', entity_id=conv_id, entity_name=_fname,
                             actor_username=_admin, actor_role='admin',
                             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=row.get('owner_id'))
        flash("Conversion deleted.", "success")
        return redirect(url_for('admin_conversions'))

    @app.route('/admin/conversions/retry/<int:conv_id>', methods=['POST'])
    def admin_retry_conversion(conv_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        db_path = os.path.join(dir_path, "storage", "sqlite.db")
        with sqlite3.connect(db_path) as _rc:
            _rc.row_factory = sqlite3.Row
            conv = _rc.execute(
                "SELECT c.*, fu.file_path AS upload_file_path "
                "FROM conversions c "
                "LEFT JOIN file_uploads fu ON fu.id = c.upload_id "
                "WHERE c.id=?",
                (conv_id,)
            ).fetchone()
        if not conv:
            flash("Conversion not found.", "error")
            return redirect(url_for('admin_files', tab='conversions'))
        conv = dict(conv)

        if conv.get('status') != 'failed':
            flash("Only failed conversions can be retried.", "error")
            return redirect(url_for('admin_files', tab='conversions'))

        upload_file_path = conv.get('upload_file_path')
        if not upload_file_path:
            flash("No original file linked to this conversion — cannot retry.", "error")
            return redirect(url_for('admin_files', tab='conversions'))

        upload_dir = app.config.get('UPLOAD_DIR', 'storage/uploads')
        full_disk_path = os.path.join(upload_dir, upload_file_path)
        if not os.path.exists(full_disk_path):
            flash("Original file is no longer on disk — cannot retry.", "error")
            return redirect(url_for('admin_files', tab='conversions'))

        try:
            stored = json.loads(conv.get('options_json') or '{}')
            _filetype = stored.get('filetype') or conv.get('tool_type') or 'converter'
            _options = stored.get('options') or {}
        except Exception:
            _filetype = conv.get('tool_type') or 'converter'
            _options = {}

        _out_fmt = conv.get('output_format') or ''
        if not _out_fmt:
            flash("Could not determine output format for retry.", "error")
            return redirect(url_for('admin_files', tab='conversions'))

        try:
            from rq import Queue as _RQ
            from worker import conn as _redis_conn_admin, HIGH_QUEUE, MEDIUM_QUEUE, LOW_QUEUE
        except Exception:
            _RQ = None
            _redis_conn_admin = None

        try:
            from local_jobs import LocalQueue, _local_queue as _admin_local_queue
            from converters import (image_converter, hash_generator, audio_converter, video_converter,
                                    document_converter, archive_converter, ebook_converter, device_converter,
                                    webservice_converter, pdf_converter, documents_compressor,
                                    image_compressor, video_compressor)
            _convert_list = {
                "image": image_converter, "bmp": image_converter, "hash": hash_generator,
                "audio": audio_converter, "video": video_converter, "document": document_converter,
                "archive": archive_converter, "ebook": ebook_converter, "device": device_converter,
                "webservice": webservice_converter, "pdf": pdf_converter,
                "document-compressor": documents_compressor, "image-compressor": image_compressor,
                "video-compressor": video_compressor
            }
        except Exception as _imp_err:
            flash(f"Could not load converters: {_imp_err}", "error")
            return redirect(url_for('admin_files', tab='conversions'))

        if _filetype not in _convert_list:
            flash(f"Cannot retry: converter type '{_filetype}' is unknown or unavailable.", "error")
            return redirect(url_for('admin_files', tab='conversions'))

        _queue_name = conv.get('queue') or 'low'
        if _queue_name not in ('high', 'medium', 'low'):
            _queue_name = 'low'
        _retry_base, _retry_ext = os.path.splitext(os.path.basename(upload_file_path))
        _url_entry = json.dumps({"path": upload_file_path, "upload_id": conv.get('upload_id'),
                                  "name": _retry_base, "ext": _retry_ext})

        _q_map_admin = {}
        if _RQ and _redis_conn_admin:
            _QUEUE_NAMES = ('high', 'medium', 'low')
            _q_map_admin = {n: _RQ(n, connection=_redis_conn_admin) for n in _QUEUE_NAMES}

        _conv_args = ([_url_entry], _out_fmt, _options, {
            "UPLOAD_DIR": upload_dir,
            "BUCKET": app.config.get('BUCKET', ''),
            "LOCAL": app.config.get('LOCAL', True)
        })

        new_job = None
        if _q_map_admin:
            try:
                new_job = _q_map_admin[_queue_name].enqueue_call(
                    func=_convert_list[_filetype].convert,
                    args=_conv_args,
                    result_ttl=5000,
                    timeout=500
                )
            except Exception:
                new_job = None
        if new_job is None:
            new_job = _admin_local_queue.enqueue_call(
                func=_convert_list[_filetype].convert,
                args=_conv_args,
                result_ttl=5000,
                timeout=500,
                queue_name=_queue_name
            )

        _options_json = json.dumps({"filetype": _filetype, "options": _options})
        _conv_ip = request.remote_addr or ''
        _new_job_id = new_job.id
        try:
            with sqlite3.connect(db_path) as _cc:
                _cc.execute(
                    "INSERT INTO conversions (user_id, upload_id, input_format, output_format, job_id, status, file_name, ip_address, tool_type, queue, options_json) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                    (conv.get('user_id'), conv.get('upload_id'), conv.get('input_format', ''),
                     _out_fmt, _new_job_id, 'pending', conv.get('file_name', ''),
                     _conv_ip, conv.get('tool_type', 'converter'), _queue_name, _options_json)
                )
                _cc.commit()
        except Exception:
            pass

        def _admin_retry_sync(job_obj, job_id_str, db_path_str):
            """Poll the LocalJob in background until done, then sync conversions table."""
            import time as _time
            for _ in range(600):
                try:
                    if job_obj.is_finished or job_obj.is_failed:
                        new_status = 'finished' if job_obj.is_finished else 'failed'
                        _out_path = ''
                        if job_obj.is_finished and job_obj.result:
                            r = job_obj.result
                            if isinstance(r, dict):
                                results = r.get('results') or []
                                _out_path = (r.get('output_path') or
                                             (results[0] if results else '') or '')
                        with sqlite3.connect(db_path_str) as _sc:
                            _sc.execute(
                                "UPDATE conversions SET status=?, output_path=COALESCE(NULLIF(?,''),output_path), completed_at=COALESCE(completed_at,datetime('now')) WHERE job_id=? AND status='pending'",
                                (new_status, _out_path, job_id_str)
                            )
                            _sc.commit()
                        return
                except Exception:
                    pass
                _time.sleep(1)

        import threading as _threading
        _threading.Thread(
            target=_admin_retry_sync,
            args=(new_job, _new_job_id, db_path),
            daemon=True
        ).start()

        flash(f"Conversion re-queued. New job: {_new_job_id[:8]}…", "success")
        return redirect(url_for('admin_files', tab='conversions'))

    @app.route('/admin/conversions/delete-selected', methods=['POST'])
    def admin_delete_selected_conversions():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        import shutil as _sh
        ids = request.form.getlist('ids')
        _udir = app.config.get('UPLOAD_DIR', '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
        with _db() as c:
            for cid in ids:
                try:
                    cid = int(cid)
                except (ValueError, TypeError):
                    continue
                row = c.execute(
                    "SELECT c.job_id, c.output_path, c.upload_id, c.file_name, c.user_id AS owner_id, fu.file_path AS upload_file_path, fu.file_size AS file_size "
                    "FROM conversions c LEFT JOIN file_uploads fu ON fu.id = c.upload_id "
                    "WHERE c.id=?", (cid,)
                ).fetchone()
                if row:
                    row = dict(row)
                    # Delete all output files (may be newline-separated; may be relative or absolute)
                    output_path_raw = row['output_path'] or ''
                    for fpath in output_path_raw.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:
                            pass
                    if row['upload_id']:
                        try:
                            up_file = _res(row['upload_file_path'] or '')
                            if up_file:
                                up_folder = os.path.dirname(up_file)
                                if os.path.isfile(up_file):
                                    os.remove(up_file)
                                if os.path.isdir(up_folder) and not os.listdir(up_folder):
                                    _sh.rmtree(up_folder, ignore_errors=True)
                        except Exception:
                            pass
                        c.execute("DELETE FROM file_uploads WHERE id=?", (row['upload_id'],))
                    # Hard-delete the conversion record
                    c.execute("DELETE FROM conversions WHERE id=?", (cid,))
                    # Remove shared links and access grants so the job URL stops working
                    _jid = row['job_id']
                    if _jid:
                        c.execute("DELETE FROM shared_links WHERE job_id=?", (_jid,))
                        c.execute("DELETE FROM job_access_grants WHERE job_id=?", (_jid,))
                    _fname = row.get('file_name') or os.path.basename(row.get('upload_file_path') or '') or str(cid)
                    log_deletion('conversion', entity_id=cid, entity_name=_fname,
                                 actor_username=session.get('admin_username', 'admin'), actor_role='admin',
                                 file_size_bytes=row.get('file_size'),
                                 extra_meta={'job_id': row.get('job_id'), 'upload_id': row.get('upload_id'), 'bulk': True},
                                 entity_owner_user_id=row.get('owner_id'))
                    deleted += 1
            c.commit()
        flash(f"{deleted} conversion(s) deleted.", "success")
        return redirect(url_for('admin_conversions'))

    @app.route('/admin/jobs/kill-stuck', methods=['POST'])
    def admin_kill_stuck_jobs():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        from local_jobs import kill_all_heavy_procs
        killed = kill_all_heavy_procs()
        # Mark any still-pending/started jobs as failed in DB
        stuck_count = 0
        import json as _json
        _restart_msg = _json.dumps({
            'error': True,
            'message': 'Job was killed by admin.',
            'results': []
        })
        with _db() as c:
            cur = c.execute("SELECT id FROM local_jobs WHERE status IN ('queued', 'started')")
            stuck_ids = [row[0] for row in cur.fetchall()]
            if stuck_ids:
                ph = ','.join('?' * len(stuck_ids))
                c.execute(
                    f"UPDATE local_jobs SET status='failed', result_json=?, completed_at=datetime('now') WHERE id IN ({ph})",
                    [_restart_msg] + stuck_ids
                )
                try:
                    c.execute(
                        f"UPDATE conversions SET status='failed', error_message='Killed by admin', "
                        f"completed_at=COALESCE(completed_at, datetime('now')) "
                        f"WHERE job_id IN ({ph}) AND status IN ('pending', 'started')",
                        stuck_ids
                    )
                except Exception:
                    pass
                stuck_count = len(stuck_ids)
                c.commit()
        flash(f"Killed {killed} system process(es) and marked {stuck_count} stuck job(s) as failed.", "success")
        return redirect(url_for('admin_conversions'))

    # ── System Health Panel ────────────────────────────────────────────────────

    @app.route('/<lang_code>/admin/system/health-panel')
    @app.route('/admin/system/health-panel')
    def admin_system_health_panel():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return abort(403)
        return render_template('admin/system-health.html')

    @app.route('/<lang_code>/admin/system/health')
    @app.route('/admin/system/health')
    def admin_system_health_api():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return jsonify({'ok': False, 'error': 'Forbidden'}), 403

        try:
            import psutil as _psutil
        except ImportError:
            return jsonify({'ok': False, 'error': 'psutil not installed'}), 500

        import calendar as _cal
        from local_jobs import _HEAVY_PROC_NAMES, _job_child_procs, _job_child_procs_lock

        # RAM
        mem = _psutil.virtual_memory()
        ram = {
            'total_mb': round(mem.total / 1024 / 1024, 1),
            'used_mb': round(mem.used / 1024 / 1024, 1),
            'available_mb': round(mem.available / 1024 / 1024, 1),
            'percent': round(mem.percent, 1),
        }

        # CPU
        cpu_percent = round(_psutil.cpu_percent(interval=0.3), 1)
        cpu_count = _psutil.cpu_count(logical=True) or 1

        # Disk usage for storage dir
        storage_dir = os.path.join(dir_path, 'storage')
        try:
            disk = _psutil.disk_usage(storage_dir)
            disk_info = {
                'total_gb': round(disk.total / 1024 ** 3, 2),
                'used_gb': round(disk.used / 1024 ** 3, 2),
                'free_gb': round(disk.free / 1024 ** 3, 2),
                'percent': round(disk.percent, 1),
            }
        except Exception:
            disk_info = {'total_gb': 0, 'used_gb': 0, 'free_gb': 0, 'percent': 0}

        # Build pid → job_id mapping from tracked child procs
        pid_to_job = {}
        with _job_child_procs_lock:
            for _jid, _procs in _job_child_procs.items():
                for _p in _procs:
                    try:
                        pid_to_job[_p.pid] = _jid
                    except Exception:
                        pass

        # Enumerate child processes — separate heavy ones from others
        heavy_procs = []
        child_procs = []
        try:
            _current = _psutil.Process()
            for child in _current.children(recursive=True):
                try:
                    if not child.is_running():
                        continue
                    info = child.as_dict(attrs=['pid', 'name', 'status', 'cpu_percent', 'memory_info', 'create_time'])
                    memory_mb = 0
                    try:
                        memory_mb = round(info['memory_info'].rss / 1024 / 1024, 1)
                    except Exception:
                        pass
                    elapsed_seconds = 0
                    try:
                        elapsed_seconds = int(time.time() - (info.get('create_time') or time.time()))
                    except Exception:
                        pass
                    _name_base = (info.get('name') or '').lower().split('.')[0]
                    _is_heavy = _name_base in _HEAVY_PROC_NAMES
                    _entry = {
                        'pid': info['pid'],
                        'name': info.get('name', ''),
                        'status': info.get('status', ''),
                        'cpu_percent': round(info.get('cpu_percent') or 0, 1),
                        'memory_mb': memory_mb,
                        'elapsed_seconds': elapsed_seconds,
                        'is_heavy': _is_heavy,
                        'job_id': pid_to_job.get(info['pid']),
                    }
                    if _is_heavy:
                        heavy_procs.append(_entry)
                    else:
                        child_procs.append(_entry)
                except Exception:
                    pass
        except Exception:
            pass

        # Active jobs from DB
        active_jobs = []
        try:
            with _db() as c:
                rows = c.execute(
                    "SELECT j.id, j.status, j.created_at, "
                    "       c.input_format, c.output_format "
                    "FROM local_jobs j "
                    "LEFT JOIN conversions c ON c.job_id = j.id "
                    "WHERE j.status IN ('queued', 'started') "
                    "GROUP BY j.id "
                    "ORDER BY j.created_at ASC"
                ).fetchall()
                for row in rows:
                    d = dict(row)
                    try:
                        created = datetime.strptime(d['created_at'], '%Y-%m-%d %H:%M:%S')
                        d['elapsed_seconds'] = int(time.time() - _cal.timegm(created.timetuple()))
                    except Exception:
                        d['elapsed_seconds'] = 0
                    active_jobs.append(d)
        except Exception:
            pass

        return jsonify({
            'ok': True,
            'ram': ram,
            'cpu_percent': cpu_percent,
            'cpu_count': cpu_count,
            'disk': disk_info,
            'heavy_procs': heavy_procs,
            'heavy_proc_count': len(heavy_procs),
            'child_procs': child_procs,
            'active_jobs': active_jobs,
        })

    @app.route('/<lang_code>/admin/system/kill-pid', methods=['POST'])
    @app.route('/admin/system/kill-pid', methods=['POST'])
    def admin_system_kill_pid():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return jsonify({'ok': False, 'error': 'Forbidden'}), 403

        pid = request.form.get('pid', type=int)
        if not pid:
            return jsonify({'ok': False, 'error': 'No PID provided'}), 400

        # Find which job_id this PID belongs to (if any)
        from local_jobs import _job_child_procs, _job_child_procs_lock
        associated_job_id = None
        with _job_child_procs_lock:
            for _jid, _procs in _job_child_procs.items():
                for _p in _procs:
                    try:
                        if _p.pid == pid:
                            associated_job_id = _jid
                            break
                    except Exception:
                        pass
                if associated_job_id:
                    break

        try:
            import psutil as _psutil
            proc = _psutil.Process(pid)
            name = proc.name()
            proc.kill()
        except Exception as _e:
            return jsonify({'ok': False, 'error': str(_e)}), 500

        # Kill any remaining child processes for the associated job (full cleanup)
        if associated_job_id:
            try:
                from local_jobs import _kill_job_procs
                _kill_job_procs(associated_job_id)
            except Exception:
                pass

        # Mark associated job + conversions as failed in DB
        if associated_job_id:
            try:
                _kill_msg = json.dumps({'error': True, 'message': f'Job killed by admin (PID {pid} terminated).', 'results': []})
                with _db() as c:
                    c.execute(
                        "UPDATE local_jobs SET status='failed', result_json=?, completed_at=datetime('now') WHERE id=?",
                        (_kill_msg, associated_job_id)
                    )
                    try:
                        c.execute(
                            "UPDATE conversions SET status='failed', error_message='Killed by admin', "
                            "completed_at=COALESCE(completed_at, datetime('now')) "
                            "WHERE job_id=? AND status IN ('pending', 'started')",
                            (associated_job_id,)
                        )
                    except Exception:
                        pass
                    c.commit()
            except Exception:
                pass

        return jsonify({
            'ok': True,
            'message': f'Killed PID {pid} ({name})',
            'job_id': associated_job_id,
        })

    @app.route('/<lang_code>/admin/system/clear-ram', methods=['POST'])
    @app.route('/admin/system/clear-ram', methods=['POST'])
    def admin_system_clear_ram():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return jsonify({'ok': False, 'error': 'Forbidden'}), 403

        steps = []

        # 1. Kill all heavy converter processes
        try:
            from local_jobs import kill_all_heavy_procs
            killed = kill_all_heavy_procs()
            steps.append(f'Killed {killed} heavy process(es)')
        except Exception as _e:
            steps.append(f'Process kill skipped: {_e}')

        # 2. Mark all stuck jobs as failed in DB
        try:
            _kill_msg = json.dumps({'error': True, 'message': 'Cleared by admin (RAM clear).', 'results': []})
            with _db() as c:
                _cur = c.execute("SELECT id FROM local_jobs WHERE status IN ('queued', 'started')")
                _stuck = [row[0] for row in _cur.fetchall()]
                if _stuck:
                    _ph = ','.join('?' * len(_stuck))
                    c.execute(
                        f"UPDATE local_jobs SET status='failed', result_json=?, completed_at=datetime('now') WHERE id IN ({_ph})",
                        [_kill_msg] + _stuck
                    )
                    try:
                        c.execute(
                            f"UPDATE conversions SET status='failed', error_message='Cleared by admin', "
                            f"completed_at=COALESCE(completed_at, datetime('now')) "
                            f"WHERE job_id IN ({_ph}) AND status IN ('pending','started')",
                            _stuck
                        )
                    except Exception:
                        pass
                    c.commit()
                steps.append(f'Marked {len(_stuck)} stuck job(s) as failed')
        except Exception as _e:
            steps.append(f'Job cleanup skipped: {_e}')

        # 3. Python GC
        try:
            import gc as _gc
            collected = _gc.collect()
            steps.append(f'Python GC: reclaimed {collected} object(s)')
        except Exception as _e:
            steps.append(f'GC skipped: {_e}')

        # 4. Clear in-memory caches (text overrides, language cache)
        try:
            from helpers import clear_text_overrides_cache, _invalidate_active_lang_cache
            clear_text_overrides_cache()
            _invalidate_active_lang_cache()
            steps.append('Cleared in-memory caches')
        except Exception as _e:
            steps.append(f'Cache clear skipped: {_e}')

        # 5. Attempt OS page cache drop (Linux only — needs root, may silently fail)
        try:
            import subprocess as _sp
            _sp.run(['sh', '-c', 'sync && echo 1 > /proc/sys/vm/drop_caches'],
                    capture_output=True, timeout=3)
            steps.append('OS page cache drop attempted')
        except Exception:
            steps.append('OS page cache drop skipped (no permission or unsupported)')

        return jsonify({'ok': True, 'steps': steps})

    @app.route('/admin/users/<int:user_id>')
    def admin_user_detail(user_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        return redirect(url_for('admin_edit_user', user_id=user_id))

    @app.route('/admin/deletion-log')
    def admin_deletion_log():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not session.get('login'):
            flash("Access denied. Deletion log is restricted to admin accounts.", "error")
            return redirect(url_for('admin_dashboard'))
        page = request.args.get('page', 1, type=int)
        per_page = 50
        offset = (page - 1) * per_page
        entity_type = request.args.get('entity_type', '')
        actor_role = request.args.get('actor_role', '')
        search = request.args.get('q', '').strip()
        from_date = request.args.get('from_date', '').strip()
        to_date = request.args.get('to_date', '').strip()
        with _db() as c:
            filters = []
            params = []
            if entity_type:
                filters.append("entity_type=?")
                params.append(entity_type)
            if actor_role:
                filters.append("actor_role=?")
                params.append(actor_role)
            if search:
                filters.append("(entity_name LIKE ? OR actor_username LIKE ?)")
                params.extend([f'%{search}%', f'%{search}%'])
            if from_date:
                filters.append("deleted_at >= ?")
                params.append(from_date)
            if to_date:
                filters.append("deleted_at <= ?")
                params.append(to_date + ' 23:59:59')
            where = ("WHERE " + " AND ".join(filters)) if filters else ""
            total = c.execute(
                f"SELECT COUNT(*) FROM deletion_log {where}", params
            ).fetchone()[0]
            rows = c.execute(
                f"SELECT * FROM deletion_log {where} ORDER BY deleted_at DESC LIMIT ? OFFSET ?",
                params + [per_page, offset]
            ).fetchall()
            entity_types = [r['entity_type'] for r in c.execute(
                "SELECT DISTINCT entity_type FROM deletion_log ORDER BY entity_type"
            ).fetchall()]
            actor_roles = [r['actor_role'] for r in c.execute(
                "SELECT DISTINCT actor_role FROM deletion_log ORDER BY actor_role"
            ).fetchall()]
        rows = [dict(r) for r in rows]
        total_pages = max(1, (total + per_page - 1) // per_page)
        return render_template(
            'admin/deletion-log.html',
            entries=rows, total=total, page=page,
            total_pages=total_pages, per_page=per_page,
            entity_type=entity_type, actor_role=actor_role, search=search,
            entity_types=entity_types, actor_roles=actor_roles,
            from_date=from_date, to_date=to_date
        )

    @app.route('/admin/users')
    def admin_users():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        page     = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 50, type=int)
        if per_page not in (10, 25, 50, 100):
            per_page = 50
        search   = request.args.get('search', '').strip()
        plan_id  = request.args.get('plan_id', None, type=int)
        status   = request.args.get('status', '')
        sort_by  = request.args.get('sort_by', 'created_at')
        sort_dir = request.args.get('sort_dir', 'desc')
        users, total = get_all_users_admin(
            page=page, per_page=per_page, search=search,
            plan_id=plan_id, status=status, sort_by=sort_by, sort_dir=sort_dir
        )
        total_pages = max(1, (total + per_page - 1) // per_page)
        plans = get_all_plans()
        return render_template('admin/users.html',
                               users=users, total=total, page=page,
                               total_pages=total_pages, per_page=per_page,
                               search=search, plan_id=plan_id, status=status,
                               sort_by=sort_by, sort_dir=sort_dir, plans=plans)

    @app.route('/admin/users/<int:user_id>/change-plan', methods=['POST'])
    def admin_change_user_plan(user_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        plan_id = request.form.get('plan_id', type=int)
        status = request.form.get('subscription_status', 'active')
        expires_at = request.form.get('subscription_expires_at', '').strip() or None
        if plan_id:
            with _db() as c:
                plan = c.execute("SELECT * FROM plans WHERE id=?", (plan_id,)).fetchone()
                if plan and dict(plan).get('price_monthly', 0) == 0:
                    status = 'free'
                    expires_at = None
                c.execute(
                    "UPDATE users SET plan_id=?, subscription_status=?, subscription_expires_at=? WHERE id=?",
                    (plan_id, status, expires_at, user_id)
                )
                c.commit()
                plan_name = dict(plan).get('name','') if plan else ''
                log_event('plan_change', f"Admin changed user #{user_id} plan to {plan_name}", user_id=user_id, meta=f"plan_id={plan_id}")
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify(success=True)
        flash("User plan updated.", "success")
        return redirect(url_for('admin_users'))

    @app.route('/admin/users/<int:user_id>/manual-payment', methods=['POST'])
    def admin_user_manual_payment(user_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        plan_id = request.form.get('plan_id', type=int)
        amount = float(request.form.get('amount', 0) or 0)
        method = request.form.get('method', 'cash').strip()
        notes = request.form.get('notes', '').strip()
        currency = request.form.get('currency', 'USD').strip() or 'USD'
        expires_at = request.form.get('subscription_expires_at', '').strip() or None
        if not plan_id:
            flash("Please select a plan.", "error")
            return redirect(url_for('admin_users'))
        with _db() as c:
            plan = c.execute("SELECT * FROM plans WHERE id=?", (plan_id,)).fetchone()
            if not plan:
                flash("Plan not found.", "error")
                return redirect(url_for('admin_users'))
            plan = dict(plan)
            sub_status = 'active' if plan.get('price_monthly', 0) > 0 else 'free'
            c.execute(
                "UPDATE users SET plan_id=?, subscription_status=?, subscription_expires_at=? WHERE id=?",
                (plan_id, sub_status, expires_at, user_id)
            )
            c.execute(
                "INSERT INTO manual_payments (user_id, plan_id, amount, currency, method, notes, subscription_expires_at, recorded_by) VALUES (?,?,?,?,?,?,?,?)",
                (user_id, plan_id, amount, currency, method, notes, expires_at, 'admin')
            )
            c.commit()
        log_event('manual_payment', f"Manual payment recorded for user #{user_id}: {currency} {amount:.2f} via {method}, plan={plan['name']}", user_id=user_id, meta=f"amount={amount} currency={currency} method={method}")
        flash(f"Manual payment recorded and plan set to {plan['name']}.", "success")
        return redirect(url_for('admin_users'))

    @app.route('/admin/users/<int:user_id>/delete', methods=['POST'])
    def admin_delete_user(user_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            # ── Get user email for email-keyed grant revocation ──────────────────
            user_row = c.execute("SELECT email, username FROM users WHERE id=?", (user_id,)).fetchone()
            user_email = user_row['email'] if user_row else ''
            _deleted_username = (user_row['username'] if user_row else '') or str(user_id)

            # ── Collect this user's job_ids for notify-table cleanup ────────────
            job_id_rows = c.execute(
                "SELECT job_id FROM conversions WHERE user_id=? AND job_id IS NOT NULL",
                (user_id,)
            ).fetchall()
            user_job_ids = [r['job_id'] for r in job_id_rows]

            # ── Hard-delete all conversions and file_uploads for this user ────────
            c.execute("DELETE FROM file_uploads WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM conversions WHERE user_id=?", (user_id,))

            # ── Remove shared links and access grants for this user's jobs ───────
            c.execute("DELETE FROM shared_links WHERE user_id=?", (user_id,))
            for _jid in user_job_ids:
                c.execute("DELETE FROM shared_links WHERE job_id=?", (_jid,))
                c.execute("DELETE FROM job_access_grants WHERE job_id=?", (_jid,))

            # ── Remove this user's grants on other users' jobs (by id and email) ─
            c.execute("DELETE FROM job_access_grants WHERE granted_to_user_id=?", (user_id,))
            if user_email:
                c.execute("DELETE FROM job_access_grants WHERE granted_to_email=?", (user_email,))

            # ── Cascade-delete each team they own ────────────────────────────────
            owned_teams = c.execute(
                "SELECT id FROM teams WHERE owner_user_id=?", (user_id,)
            ).fetchall()
            for ot in owned_teams:
                ot_id = ot['id']
                subteam_rows = c.execute(
                    "SELECT id FROM teams WHERE parent_team_id=?", (ot_id,)
                ).fetchall()
                # Revoke shared links/grants for all jobs belonging to team members
                all_ot_team_ids = [ot_id] + [st['id'] for st in subteam_rows]
                ot_member_uid_set = {user_id}
                for _tid in all_ot_team_ids:
                    tm_rows = c.execute(
                        "SELECT user_id FROM team_members WHERE team_id=? AND status='active' AND user_id IS NOT NULL",
                        (_tid,)
                    ).fetchall()
                    for tmr in tm_rows:
                        ot_member_uid_set.add(tmr['user_id'])
                if ot_member_uid_set:
                    _ph2 = ','.join('?' * len(ot_member_uid_set))
                    ot_job_rows = c.execute(
                        f"SELECT job_id FROM conversions WHERE user_id IN ({_ph2}) AND job_id IS NOT NULL",
                        list(ot_member_uid_set)
                    ).fetchall()
                    for ojr in ot_job_rows:
                        c.execute("DELETE FROM shared_links WHERE job_id=?", (ojr['job_id'],))
                        c.execute("DELETE FROM job_access_grants WHERE job_id=?", (ojr['job_id'],))
                for st in subteam_rows:
                    c.execute("DELETE FROM team_members WHERE team_id=?", (st['id'],))
                    c.execute("DELETE FROM team_messages WHERE team_id=?", (st['id'],))
                    c.execute("DELETE FROM team_presence WHERE team_id=?", (st['id'],))
                c.execute("DELETE FROM teams WHERE parent_team_id=?", (ot_id,))
                c.execute("DELETE FROM team_members WHERE team_id=?", (ot_id,))
                c.execute("DELETE FROM team_messages WHERE team_id=?", (ot_id,))
                c.execute("DELETE FROM team_presence WHERE team_id=?", (ot_id,))
                c.execute("DELETE FROM teams WHERE id=?", (ot_id,))

            # ── Remove from any other teams they belong to ───────────────────────
            c.execute("DELETE FROM team_members WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM team_presence WHERE user_id=?", (user_id,))

            # ── Delete session / auth / push data ────────────────────────────────
            c.execute("DELETE FROM user_sessions WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM user_push_subscriptions WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM user_api_keys WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM password_reset_tokens WHERE user_id=?", (user_id,))

            # ── Delete notify records keyed by job_id ────────────────────────────
            for _jid in user_job_ids:
                c.execute("DELETE FROM job_notify_emails WHERE job_id=?", (_jid,))
                c.execute("DELETE FROM job_notify_push WHERE job_id=?", (_jid,))

            # ── Delete remaining user-keyed records ──────────────────────────────
            # Note: plan_billing_periods has no user_id column (it is plan-level config
            # data, not per-user data), so there is nothing to delete per user there.
            c.execute("DELETE FROM user_notification_prefs WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM user_inbox WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM payment_history WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM manual_payments WHERE user_id=?", (user_id,))
            c.execute(
                "DELETE FROM referral_rewards WHERE referrer_user_id=? OR referred_user_id=?",
                (user_id, user_id)
            )
            c.execute("DELETE FROM broadcast_recipients WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM security_events WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM activity_log WHERE user_id=?", (user_id,))

            # ── Finally delete the user row ───────────────────────────────────────
            c.execute("DELETE FROM users WHERE id=?", (user_id,))
            c.commit()
        _admin = session.get('admin_username', 'admin')
        log_deletion('user', entity_id=user_id, entity_name=_deleted_username,
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'email': user_email},
                     entity_owner_user_id=user_id)
        if request.headers.get('X-Requested-With') == 'XMLHttpRequest':
            return jsonify(success=True)
        flash("User deleted.", "success")
        return redirect(url_for('admin_users'))

    @app.route('/admin/users/<int:user_id>/verify-email', methods=['POST'])
    def admin_verify_user_email(user_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute("SELECT id, email_verified FROM users WHERE id=?", (user_id,)).fetchone()
            if not row:
                flash("User not found.", "error")
                return redirect(url_for('admin_users'))
            c.execute(
                "UPDATE users SET email_verified=1, email_verification_token='' WHERE id=?",
                (user_id,)
            )
            c.commit()
        log_event('admin_verify_email', f"Admin bypassed email verification for user #{user_id}", user_id=user_id)
        flash("Email marked as verified.", "success")
        return redirect(url_for('admin_edit_user', user_id=user_id))

    @app.route('/admin/users/<int:user_id>/edit', methods=['GET', 'POST'])
    def admin_edit_user(user_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        plans = get_all_plans()
        if request.method == 'POST':
            username = (request.form.get('username') or '').strip()
            email = (request.form.get('email') or '').strip()
            new_password = (request.form.get('new_password') or '').strip()
            plan_id = request.form.get('plan_id', type=int)
            sub_status = (request.form.get('subscription_status') or 'free').strip()
            sub_expires = (request.form.get('subscription_expires_at') or '').strip() or None
            totp_enabled = 1 if '1' in request.form.getlist('totp_enabled') else 0
            is_active = 1 if '1' in request.form.getlist('is_active') else 0
            country = (request.form.get('country') or '').strip()
            if not username or not email:
                flash("Username and email are required.", "error")
                return redirect(url_for('admin_edit_user', user_id=user_id))
            with _db() as c:
                if new_password:
                    pw_hash = generate_password_hash(new_password)
                    c.execute(
                        "UPDATE users SET username=?, email=?, password_hash=?, plan_id=?, subscription_status=?, subscription_expires_at=?, user_totp_enabled=?, is_active=?, country=? WHERE id=?",
                        (username, email, pw_hash, plan_id, sub_status, sub_expires, totp_enabled, is_active, country, user_id)
                    )
                else:
                    c.execute(
                        "UPDATE users SET username=?, email=?, plan_id=?, subscription_status=?, subscription_expires_at=?, user_totp_enabled=?, is_active=?, country=? WHERE id=?",
                        (username, email, plan_id, sub_status, sub_expires, totp_enabled, is_active, country, user_id)
                    )
                if not totp_enabled:
                    c.execute("UPDATE users SET user_totp_secret='' WHERE id=?", (user_id,))
                c.commit()
            log_event('admin_edit_user', f"Admin edited user #{user_id} ({email})", user_id=user_id, meta=f"plan_id={plan_id} status={sub_status}")
            flash("User profile updated successfully.", "success")
            return redirect(url_for('admin_edit_user', user_id=user_id))

        with _db() as c:
            user = c.execute(
                "SELECT u.*, p.name AS plan_name FROM users u LEFT JOIN plans p ON p.id=u.plan_id WHERE u.id=?",
                (user_id,)
            ).fetchone()
            if not user:
                flash("User not found.", "error")
                return redirect(url_for('admin_users'))
            user = dict(user)
            file_count = c.execute("SELECT COUNT(*) FROM file_uploads WHERE user_id=?", (user_id,)).fetchone()[0]
            conv_count = c.execute("SELECT COUNT(*) FROM conversions WHERE user_id=?", (user_id,)).fetchone()[0]
            recent_convs = c.execute(
                "SELECT * FROM conversions WHERE user_id=? ORDER BY id DESC LIMIT 10", (user_id,)
            ).fetchall()
            recent_convs = [dict(r) for r in recent_convs]
            payments = 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.user_id=? ORDER BY mp.id DESC LIMIT 10",
                (user_id,)
            ).fetchall()
            payments = [dict(r) for r in payments]
        return render_template('admin/user_edit.html', user=user, plans=plans,
                               file_count=file_count, conv_count=conv_count,
                               recent_convs=recent_convs, payments=payments)

    @app.route('/admin/plans')
    def admin_plans():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        plans = get_all_plans()
        stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
        stripe_mode = ''
        if stripe_secret:
            stripe_mode = 'test' if stripe_secret.startswith('sk_test_') else 'live'
        return render_template('admin/plans.html', plans=plans, stripe_mode=stripe_mode)

    @app.route('/admin/plans/create', methods=['POST'])
    def admin_create_plan():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        name = request.form.get('name', '').strip()
        slug = request.form.get('slug', '').strip().lower().replace(' ', '-')
        price = float(request.form.get('price_monthly', 0))
        max_files = int(request.form.get('max_files_per_day', 5))
        max_size = int(request.form.get('max_file_size_mb', 50))
        auto_delete = int(request.form.get('auto_delete_hours', 24))
        max_conv = int(request.form.get('max_conversions_stored', 10))
        max_pdf = int(request.form.get('max_pdf_per_day', 3))
        max_hash = int(request.form.get('max_hash_per_day', 10))
        max_shot = int(request.form.get('max_screenshot_per_day', 5))
        max_bgr = int(request.form.get('max_bg_remove_per_day', 3))
        max_proc_secs = int(request.form.get('max_processing_seconds', 120))
        max_batch = int(request.form.get('max_batch_files', 20))
        job_notify_email = 1 if request.form.get('job_notify_email') else 0
        batch_archive_download = 1 if request.form.get('batch_archive_download') else 0
        queue_priority = request.form.get('queue', 'low').strip().lower()
        if queue_priority not in ('high', 'medium', 'low'):
            queue_priority = 'low'
        api_enabled = 1 if request.form.get('api_enabled') else 0
        api_calls_per_day = int(request.form.get('api_calls_per_day', 100))
        api_calls_per_minute = int(request.form.get('api_calls_per_minute', 10))
        cloud_export_google_drive = 1 if request.form.get('cloud_export_google_drive') else 0
        cloud_export_dropbox = 1 if request.form.get('cloud_export_dropbox') else 0
        cloud_export_onedrive = 1 if request.form.get('cloud_export_onedrive') else 0
        share_link_enabled = 1 if request.form.get('share_link_enabled') else 0
        share_link_password = 1 if request.form.get('share_link_password') else 0
        share_link_onetime = 1 if request.form.get('share_link_onetime') else 0
        share_link_expiry = 1 if request.form.get('share_link_expiry') else 0
        share_link_max_expiry_hours = int(request.form.get('share_link_max_expiry_hours', 0))
        share_link_max_per_job = int(request.form.get('share_link_max_per_job', 0))
        with _db() as c:
            c.execute("""
                INSERT INTO plans (name, slug, price_monthly, max_files_per_day, max_file_size_mb,
                auto_delete_hours, max_conversions_stored, stripe_price_id, paypal_plan_id,
                max_pdf_per_day, max_hash_per_day, max_screenshot_per_day, max_bg_remove_per_day,
                job_notify_email, queue, batch_archive_download, max_processing_seconds, max_batch_files,
                api_enabled, api_calls_per_day, api_calls_per_minute,
                cloud_export_google_drive, cloud_export_dropbox, cloud_export_onedrive,
                share_link_enabled, share_link_password, share_link_onetime, share_link_expiry,
                share_link_max_expiry_hours, share_link_max_per_job)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            """, (name, slug, price, max_files, max_size, auto_delete, max_conv, '', '',
                  max_pdf, max_hash, max_shot, max_bgr, job_notify_email, queue_priority,
                  batch_archive_download, max_proc_secs, max_batch,
                  api_enabled, api_calls_per_day, api_calls_per_minute,
                  cloud_export_google_drive, cloud_export_dropbox, cloud_export_onedrive,
                  share_link_enabled, share_link_password, share_link_onetime, share_link_expiry,
                  share_link_max_expiry_hours, share_link_max_per_job))
            c.commit()
            new_plan_id = c.execute("SELECT last_insert_rowid()").fetchone()[0]

        if price > 0:
            stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
            if stripe_secret:
                try:
                    import stripe
                    stripe.api_key = stripe_secret
                    current_mode = 'test' if stripe_secret.startswith('sk_test_') else 'live'
                    product = stripe.Product.create(
                        name=name,
                        description=f"OnlineConvert {name} plan"
                    )
                    stripe_price = stripe.Price.create(
                        product=product['id'],
                        unit_amount=max(1, round(price * 100)),
                        currency='usd',
                        recurring={'interval': 'month', 'interval_count': 1},
                        nickname=f"{name} – Monthly"
                    )
                    with _db() as c:
                        c.execute("UPDATE plans SET stripe_product_id=?, stripe_price_id=?, stripe_key_mode=? WHERE id=?",
                                  (product['id'], stripe_price['id'], current_mode, new_plan_id))
                        c.commit()
                    flash("Plan created and synced to Stripe automatically.", "success")
                except Exception as e:
                    flash(f"Plan created but Stripe sync failed: {str(e)}", "error")
                return redirect(url_for('admin_plans'))

        flash("Plan created.", "success")
        return redirect(url_for('admin_plans'))

    @app.route('/admin/plans/<int:plan_id>/update', methods=['POST'])
    def admin_update_plan(plan_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        name = request.form.get('name', '').strip()
        price = float(request.form.get('price_monthly', 0))
        max_files = int(request.form.get('max_files_per_day', 5))
        max_size = int(request.form.get('max_file_size_mb', 50))
        auto_delete = int(request.form.get('auto_delete_hours', 24))
        max_conv = int(request.form.get('max_conversions_stored', 10))
        max_pdf = int(request.form.get('max_pdf_per_day', 3))
        max_hash = int(request.form.get('max_hash_per_day', 10))
        max_shot = int(request.form.get('max_screenshot_per_day', 5))
        max_bgr = int(request.form.get('max_bg_remove_per_day', 3))
        is_active = 1 if request.form.get('is_active') else 0
        job_notify_email = 1 if request.form.get('job_notify_email') else 0
        batch_archive_download = 1 if request.form.get('batch_archive_download') else 0
        max_proc_secs = int(request.form.get('max_processing_seconds', 0))
        max_batch = int(request.form.get('max_batch_files', 20))
        queue_priority = request.form.get('queue', 'low').strip().lower()
        if queue_priority not in ('high', 'medium', 'low'):
            queue_priority = 'low'
        api_enabled = 1 if request.form.get('api_enabled') else 0
        api_calls_per_day = int(request.form.get('api_calls_per_day', 100))
        api_calls_per_minute = int(request.form.get('api_calls_per_minute', 10))
        cloud_export_google_drive = 1 if request.form.get('cloud_export_google_drive') else 0
        cloud_export_dropbox = 1 if request.form.get('cloud_export_dropbox') else 0
        cloud_export_onedrive = 1 if request.form.get('cloud_export_onedrive') else 0
        share_link_enabled = 1 if request.form.get('share_link_enabled') else 0
        share_link_password_chk = 1 if request.form.get('share_link_password') else 0
        share_link_onetime = 1 if request.form.get('share_link_onetime') else 0
        share_link_expiry = 1 if request.form.get('share_link_expiry') else 0
        share_link_max_expiry_hours = int(request.form.get('share_link_max_expiry_hours', 0))
        share_link_max_per_job = int(request.form.get('share_link_max_per_job', 0))
        max_teams = int(request.form.get('max_teams', 0))
        max_sub_teams = int(request.form.get('max_sub_teams', 0))
        max_members_per_team = int(request.form.get('max_members_per_team', 0))

        old_plan = get_plan_by_id(plan_id)
        old_price = old_plan['price_monthly'] if old_plan else 0

        with _db() as c:
            c.execute("""
                UPDATE plans SET name=?, price_monthly=?, max_files_per_day=?,
                max_file_size_mb=?, auto_delete_hours=?, max_conversions_stored=?,
                is_active=?,
                max_pdf_per_day=?, max_hash_per_day=?, max_screenshot_per_day=?, max_bg_remove_per_day=?,
                job_notify_email=?, queue=?, batch_archive_download=?, max_processing_seconds=?,
                max_batch_files=?, api_enabled=?, api_calls_per_day=?, api_calls_per_minute=?,
                cloud_export_google_drive=?, cloud_export_dropbox=?, cloud_export_onedrive=?,
                share_link_enabled=?, share_link_password=?, share_link_onetime=?, share_link_expiry=?,
                share_link_max_expiry_hours=?, share_link_max_per_job=?,
                max_teams=?, max_sub_teams=?, max_members_per_team=?
                WHERE id=?
            """, (name, price, max_files, max_size, auto_delete, max_conv,
                  is_active,
                  max_pdf, max_hash, max_shot, max_bgr, job_notify_email, queue_priority,
                  batch_archive_download, max_proc_secs, max_batch,
                  api_enabled, api_calls_per_day, api_calls_per_minute,
                  cloud_export_google_drive, cloud_export_dropbox, cloud_export_onedrive,
                  share_link_enabled, share_link_password_chk, share_link_onetime, share_link_expiry,
                  share_link_max_expiry_hours, share_link_max_per_job,
                  max_teams, max_sub_teams, max_members_per_team, plan_id))
            c.commit()

        if price > 0:
            stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
            if stripe_secret:
                price_changed = abs(float(price) - float(old_price)) > 0.001
                old_name = old_plan.get('name', '') if old_plan else ''
                name_changed = name != old_name
                stored_mode = old_plan.get('stripe_key_mode', '') if old_plan else ''
                current_mode = 'test' if stripe_secret.startswith('sk_test_') else 'live'
                mode_mismatch = stored_mode != current_mode
                needs_new_price = price_changed or mode_mismatch or not old_plan.get('stripe_price_id', '')

                try:
                    import stripe
                    stripe.api_key = stripe_secret
                    existing_product_id = old_plan.get('stripe_product_id', '') if old_plan else ''
                    if not existing_product_id or mode_mismatch:
                        product = stripe.Product.create(
                            name=name,
                            description=f"OnlineConvert {name} plan"
                        )
                        existing_product_id = product['id']
                    elif name_changed:
                        try:
                            stripe.Product.modify(existing_product_id, name=name)
                        except Exception:
                            product = stripe.Product.create(
                                name=name,
                                description=f"OnlineConvert {name} plan"
                            )
                            existing_product_id = product['id']

                    if needs_new_price:
                        stripe_price = stripe.Price.create(
                            product=existing_product_id,
                            unit_amount=max(1, round(price * 100)),
                            currency='usd',
                            recurring={'interval': 'month', 'interval_count': 1},
                            nickname=f"{name} – Monthly"
                        )
                        new_price_id = stripe_price['id']
                    else:
                        new_price_id = old_plan.get('stripe_price_id', '')

                    with _db() as c:
                        c.execute("UPDATE plans SET stripe_product_id=?, stripe_price_id=?, stripe_key_mode=? WHERE id=?",
                                  (existing_product_id, new_price_id, current_mode, plan_id))
                        if needs_new_price:
                            c.execute("UPDATE plan_billing_periods SET stripe_price_id='' WHERE plan_id=?", (plan_id,))
                        c.commit()
                    flash("Plan updated and synced to Stripe.", "success")
                except Exception as e:
                    flash(f"Plan updated but Stripe sync failed: {str(e)}", "error")
                return redirect(url_for('admin_plans'))

        flash("Plan updated.", "success")
        return redirect(url_for('admin_plans'))

    @app.route('/admin/plans/<int:plan_id>/delete', methods=['POST'])
    def admin_delete_plan(plan_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if plan_id == 1:
            flash("Cannot delete the default Free plan.", "error")
            return redirect(url_for('admin_plans'))
        with _db() as c:
            _plan_row = c.execute("SELECT name FROM plans WHERE id=?", (plan_id,)).fetchone()
            _plan_name = _plan_row['name'] if _plan_row else str(plan_id)
            c.execute("UPDATE users SET plan_id=1 WHERE plan_id=?", (plan_id,))
            c.execute("DELETE FROM plans WHERE id=?", (plan_id,))
            c.commit()
        _admin = session.get('admin_username', 'admin')
        log_deletion('plan', entity_id=plan_id, entity_name=_plan_name,
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'plan_id': plan_id})
        flash("Plan deleted.", "success")
        return redirect(url_for('admin_plans'))

    @app.route('/admin/plans/<int:plan_id>/reset-stripe', methods=['POST'])
    def admin_reset_stripe(plan_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        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()
        flash("Stripe IDs cleared. They will be re-created automatically on the next purchase.", "success")
        return redirect(url_for('admin_plans'))

    @app.route('/admin/plans/sync-all-stripe', methods=['POST'])
    def admin_sync_all_stripe():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        stripe_secret = get_payment_setting('stripe_secret_key', 'STRIPE_SECRET_KEY')
        if not stripe_secret:
            flash("Stripe secret key is not configured.", "error")
            return redirect(url_for('admin_plans'))
        import stripe
        stripe.api_key = stripe_secret
        current_mode = 'test' if stripe_secret.startswith('sk_test_') else 'live'
        plans = get_all_plans()
        synced = 0
        errors = 0
        for plan in plans:
            if plan['price_monthly'] <= 0:
                continue
            stored_mode = plan.get('stripe_key_mode', '')
            if stored_mode == current_mode and plan.get('stripe_price_id', ''):
                continue
            try:
                product = stripe.Product.create(
                    name=plan['name'],
                    description=f"OnlineConvert {plan['name']} plan"
                )
                stripe_price = stripe.Price.create(
                    product=product['id'],
                    unit_amount=max(1, round(float(plan['price_monthly']) * 100)),
                    currency='usd',
                    recurring={'interval': 'month', 'interval_count': 1},
                    nickname=f"{plan['name']} – Monthly"
                )
                with _db() as c:
                    c.execute("UPDATE plans SET stripe_product_id=?, stripe_price_id=?, stripe_key_mode=? WHERE id=?",
                              (product['id'], stripe_price['id'], current_mode, plan['id']))
                    c.execute("UPDATE plan_billing_periods SET stripe_price_id='' WHERE plan_id=?", (plan['id'],))
                    c.commit()
                synced += 1
            except Exception:
                errors += 1
        msg = f"Synced {synced} plan(s) to Stripe ({current_mode} mode)."
        if errors:
            msg += f" {errors} plan(s) failed."
        flash(msg, "success" if errors == 0 else "error")
        return redirect(url_for('admin_plans'))

    @app.route('/admin/auto-delete/save', methods=['POST'])
    def admin_save_auto_delete():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        hours = request.form.get('anon_auto_delete_hours', '24').strip()
        try:
            hours = str(max(0, int(hours)))
        except ValueError:
            hours = '24'
        with _db() as c:
            c.execute(
                "INSERT OR REPLACE INTO site_settings (key, value) VALUES ('anon_auto_delete_hours', ?)",
                (hours,)
            )
            c.commit()
        flash(f"Anonymous file auto-delete set to {hours} hours.", "success")
        return redirect(url_for('admin_files'))

    @app.route("/<lang_code>/admin/referrals")
    @app.route('/admin/referrals')
    def admin_referrals():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            rows = c.execute("""
                SELECT rr.*,
                       ru.username as referrer_username, ru.email as referrer_email,
                       ref_u.username as referred_username, ref_u.email as referred_email,
                       p.name as referred_plan
                FROM referral_rewards rr
                JOIN users ru ON ru.id = rr.referrer_user_id
                JOIN users ref_u ON ref_u.id = rr.referred_user_id
                LEFT JOIN plans p ON p.id = ref_u.plan_id
                ORDER BY rr.created_at DESC
            """).fetchall()
            rewards = [dict(r) for r in rows]
            total_users = c.execute("SELECT COUNT(*) FROM users WHERE referred_by_user_id IS NOT NULL").fetchone()[0]
            total_granted = c.execute("SELECT COUNT(*) FROM referral_rewards WHERE status='granted'").fetchone()[0]
            total_pending = c.execute("SELECT COUNT(*) FROM referral_rewards WHERE status='pending'").fetchone()[0]
            site = {r['key']: r['value'] for r in c.execute(
                "SELECT key, value FROM site_settings WHERE key IN ('referral_enabled','referral_reward_days')"
            ).fetchall()}
        return render_template('admin/referrals.html',
                               rewards=rewards, total_users=total_users,
                               total_granted=total_granted, total_pending=total_pending,
                               site=site)

    @app.route("/<lang_code>/admin/referrals/settings", methods=['POST'])
    @app.route('/admin/referrals/settings', methods=['POST'])
    def admin_referral_settings():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        enabled = '1' if request.form.get('referral_enabled') else '0'
        days = request.form.get('referral_reward_days', '30').strip()
        try:
            days = str(max(1, int(days)))
        except ValueError:
            days = '30'
        with _db() as c:
            c.execute("INSERT OR REPLACE INTO site_settings (key, value) VALUES ('referral_enabled',?)", (enabled,))
            c.execute("INSERT OR REPLACE INTO site_settings (key, value) VALUES ('referral_reward_days',?)", (days,))
            c.commit()
        flash("Referral settings saved.", "success")
        return redirect(url_for('admin_referrals'))

    @app.route("/<lang_code>/admin/referrals/<int:reward_id>/grant", methods=['POST'])
    @app.route('/admin/referrals/<int:reward_id>/grant', methods=['POST'])
    def admin_grant_reward(reward_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            reward = c.execute("SELECT * FROM referral_rewards WHERE id=?", (reward_id,)).fetchone()
            if reward and reward['status'] == 'pending':
                grant_referral_reward(reward['referrer_user_id'], reward['referred_user_id'], reward['plan_name'])
        flash("Reward granted.", "success")
        return redirect(url_for('admin_referrals'))

    # ─── Admin Promo Codes ────────────────────────────────────────────────────

    @app.route("/<lang_code>/admin/promo-codes")
    @app.route('/admin/promo-codes')
    def admin_promo_codes():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            codes = c.execute("SELECT * FROM promo_codes ORDER BY created_at DESC").fetchall()
        return render_template('admin/promo_codes.html', codes=codes)

    @app.route("/<lang_code>/admin/promo-codes/create", methods=['POST'])
    @app.route('/admin/promo-codes/create', methods=['POST'])
    def admin_create_promo_code():
        logged_in, r = authenticate()
        if not logged_in:
            return r
        code = request.form.get('code', '').strip().upper()
        discount_percent = float(request.form.get('discount_percent', 0) or 0)
        discount_type = request.form.get('discount_type', 'first_time')
        max_uses = int(request.form.get('max_uses', 0) or 0)
        expires_at = request.form.get('expires_at', '').strip() or None
        is_active = 1 if request.form.get('is_active') else 0
        if not code:
            flash("Code is required.", "error")
            return redirect(url_for('admin_promo_codes'))
        try:
            with _db() as c:
                c.execute("""
                    INSERT INTO promo_codes (code, discount_percent, discount_type, max_uses, expires_at, is_active)
                    VALUES (?,?,?,?,?,?)
                """, (code, discount_percent, discount_type, max_uses, expires_at, is_active))
                c.commit()
            flash(f"Promo code '{code}' created.", "success")
        except Exception as e:
            flash(f"Error creating promo code: {e}", "error")
        return redirect(url_for('admin_promo_codes'))

    @app.route("/<lang_code>/admin/promo-codes/<int:code_id>/update", methods=['POST'])
    @app.route('/admin/promo-codes/<int:code_id>/update', methods=['POST'])
    def admin_update_promo_code(code_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        discount_percent = float(request.form.get('discount_percent', 0) or 0)
        discount_type = request.form.get('discount_type', 'first_time')
        max_uses = int(request.form.get('max_uses', 0) or 0)
        expires_at = request.form.get('expires_at', '').strip() or None
        is_active = 1 if request.form.get('is_active') else 0
        with _db() as c:
            c.execute("""
                UPDATE promo_codes SET discount_percent=?, discount_type=?, max_uses=?,
                expires_at=?, is_active=? WHERE id=?
            """, (discount_percent, discount_type, max_uses, expires_at, is_active, code_id))
            c.commit()
        flash("Promo code updated.", "success")
        return redirect(url_for('admin_promo_codes'))

    @app.route("/<lang_code>/admin/promo-codes/<int:code_id>/delete", methods=['POST'])
    @app.route('/admin/promo-codes/<int:code_id>/delete', methods=['POST'])
    def admin_delete_promo_code(code_id):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            _pc = c.execute("SELECT code FROM promo_codes WHERE id=?", (code_id,)).fetchone()
            _pc_name = _pc['code'] if _pc else str(code_id)
            c.execute("DELETE FROM promo_codes WHERE id=?", (code_id,))
            c.commit()
        _admin = session.get('admin_username', 'admin')
        log_deletion('promo_code', entity_id=code_id, entity_name=_pc_name,
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'code_id': code_id})
        flash("Promo code deleted.", "success")
        return redirect(url_for('admin_promo_codes'))

    @app.route("/<lang_code>/admin/promo-codes/near-expiry")
    @app.route('/admin/promo-codes/near-expiry')
    def admin_promo_near_expiry():
        """Return list of users expiring within N days (AJAX)."""
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'error': 'Unauthorized'}), 401
        import datetime as _dt2
        days = request.args.get('days', 14, type=int)
        days = max(1, min(days, 90))
        now  = _dt2.datetime.utcnow()
        cutoff = (now + _dt2.timedelta(days=days)).strftime('%Y-%m-%d')
        today  = now.strftime('%Y-%m-%d')
        with _db() as c:
            rows = c.execute("""
                SELECT u.id, u.username, u.email, u.subscription_expires_at,
                       p.name AS plan_name
                FROM users u
                LEFT JOIN plans p ON p.id = u.plan_id
                WHERE u.subscription_status = 'active'
                  AND u.subscription_expires_at IS NOT NULL
                  AND substr(u.subscription_expires_at, 1, 10) >= ?
                  AND substr(u.subscription_expires_at, 1, 10) <= ?
                ORDER BY u.subscription_expires_at ASC
                LIMIT 200
            """, (today, cutoff)).fetchall()
        return jsonify({'users': [dict(r) for r in rows]})

    @app.route("/<lang_code>/admin/promo-codes/send-expiry", methods=['POST'])
    @app.route('/admin/promo-codes/send-expiry', methods=['POST'])
    def admin_send_promo_expiry():
        """Send a promo code to one or all near-expiry users via email + in-app notification (JSON API)."""
        logged_in, r = authenticate()
        if not logged_in:
            return jsonify({'error': 'Unauthorized'}), 401

        data         = request.get_json(silent=True) or {}
        promo_code   = (data.get('promo_code') or '').strip().upper()
        user_ids_raw = data.get('user_ids', [])          # list of int ids; empty = all
        send_all     = bool(data.get('send_all', False))
        days         = int(data.get('days', 14))

        if not promo_code:
            return jsonify({'ok': False, 'error': 'No promo code specified'}), 400

        import datetime as _dt2
        now    = _dt2.datetime.utcnow()
        cutoff = (now + _dt2.timedelta(days=days)).strftime('%Y-%m-%d')
        today  = now.strftime('%Y-%m-%d')

        with _db() as c:
            # Fetch promo code by code string
            promo = c.execute("SELECT * FROM promo_codes WHERE code=? AND is_active=1",
                              (promo_code,)).fetchone()
            if not promo:
                return jsonify({'ok': False, 'error': f'Promo code "{promo_code}" not found or inactive'}), 404
            promo = dict(promo)

            # Fetch target users
            if not send_all and user_ids_raw:
                placeholders = ','.join('?' * len(user_ids_raw))
                users = c.execute(f"""
                    SELECT u.id, u.username, u.email, u.subscription_expires_at, p.name AS plan_name
                    FROM users u LEFT JOIN plans p ON p.id = u.plan_id
                    WHERE u.id IN ({placeholders})
                """, [int(x) for x in user_ids_raw]).fetchall()
            else:
                users = c.execute("""
                    SELECT u.id, u.username, u.email, u.subscription_expires_at, p.name AS plan_name
                    FROM users u LEFT JOIN plans p ON p.id = u.plan_id
                    WHERE u.subscription_status = 'active'
                      AND u.subscription_expires_at IS NOT NULL
                      AND substr(u.subscription_expires_at, 1, 10) >= ?
                      AND substr(u.subscription_expires_at, 1, 10) <= ?
                """, (today, cutoff)).fetchall()

            site = get_site_settings()
            site_name = site.get('site_name', 'FileConvert')
            sent_count = 0
            for u in users:
                u = dict(u)
                expiry_date = (u.get('subscription_expires_at') or '')[:10]
                discount    = promo['discount_percent']
                promo_str   = promo['code']
                subject     = f"Your {site_name} subscription expires soon – get {discount:.0f}% off renewal!"
                extra_msg   = f"<p>{custom_message}</p>" if custom_message else ""
                html_body   = f"""
<div style="font-family:sans-serif;max-width:560px;margin:0 auto;padding:24px;color:#1f2937">
  <div style="background:linear-gradient(135deg,#6366f1,#8b5cf6);border-radius:12px;padding:24px;margin-bottom:24px;text-align:center">
    <h2 style="color:white;margin:0 0 4px">Renew &amp; Save {discount:.0f}%!</h2>
    <p style="color:#e0e7ff;margin:0;font-size:14px">Your subscription expires on {expiry_date}</p>
  </div>
  <p>Hi <strong>{u['username']}</strong>,</p>
  <p>Your <strong>{u.get('plan_name','') or 'paid'}</strong> plan expires on <strong>{expiry_date}</strong>.
     Renew now and save <strong>{discount:.0f}%</strong> with the exclusive promo code below:</p>
  {extra_msg}
  <div style="background:#f5f3ff;border:2px dashed #6366f1;border-radius:12px;padding:20px;text-align:center;margin:20px 0">
    <div style="font-size:11px;color:#6366f1;font-weight:600;letter-spacing:0.1em;text-transform:uppercase;margin-bottom:8px">Your Promo Code</div>
    <div style="font-family:monospace;font-size:28px;font-weight:900;color:#4f46e5;letter-spacing:0.15em">{promo_str}</div>
    <div style="font-size:12px;color:#7c3aed;margin-top:6px">{discount:.0f}% off · {promo['discount_type'].replace('_',' ').title()}</div>
  </div>
  <p style="font-size:13px;color:#6b7280">Apply this code at checkout when renewing your plan.
  {f'Expires: {promo["expires_at"][:10]}.' if promo.get("expires_at") else 'No expiry date.'}</p>
  <div style="margin:24px 0;text-align:center">
    <a href="{site.get('site_url','')}/pricing"
       style="background:#6366f1;color:white;padding:12px 28px;border-radius:8px;text-decoration:none;font-weight:600;font-size:15px">
      Renew My Plan
    </a>
  </div>
  <hr style="border:none;border-top:1px solid #e5e7eb;margin:24px 0">
  <p style="font-size:12px;color:#9ca3af;text-align:center">{site_name} · This is an automated message.</p>
</div>"""
                # Send email
                send_email(u['email'], subject, html_body, None)
                # In-app notification
                send_user_notification(
                    u['id'], 'promo_expiry',
                    subject,
                    html_body,
                    f"Your subscription expires {expiry_date}. Use code {promo_str} for {discount:.0f}% off!"
                )
                sent_count += 1

        return jsonify({'ok': True, 'sent': sent_count})

    @app.route('/admin/promo-codes/validate', methods=['POST'])
    def admin_validate_promo():
        """AJAX endpoint to validate a promo code (used at checkout/registration)."""
        code = (request.json or {}).get('code', '').strip().upper()
        if not code:
            return jsonify({'valid': False, 'error': 'No code provided'})
        with _db() as c:
            row = c.execute(
                "SELECT * FROM promo_codes WHERE code=? COLLATE NOCASE AND is_active=1", (code,)
            ).fetchone()
        if not row:
            return jsonify({'valid': False, 'error': 'Invalid or inactive promo code'})
        if row['max_uses'] > 0 and row['uses_count'] >= row['max_uses']:
            return jsonify({'valid': False, 'error': 'This promo code has reached its usage limit'})
        if row['expires_at']:
            from datetime import datetime as _dt
            try:
                exp = _dt.fromisoformat(row['expires_at'])
                if _dt.utcnow() > exp:
                    return jsonify({'valid': False, 'error': 'This promo code has expired'})
            except Exception:
                pass
        return jsonify({
            'valid': True,
            'discount_percent': row['discount_percent'],
            'discount_type': row['discount_type'],
            'code': row['code'],
        })

    # ===== AT THE VERY END - Catch-all route for custom login =====

    # ===== BROADCAST =====

    def _get_broadcast_recipients(plan_ids, countries):
        """Return list of matching users as dicts with has_push bool."""
        with _db() as c:
            query = """
                SELECT u.id,
                       COALESCE(u.username, '') AS username,
                       COALESCE(u.email, '') AS email,
                       COALESCE(u.country, '') AS country,
                       COALESCE(u.subscription_status, '') AS subscription_status,
                       u.created_at AS joined_date,
                       COALESCE(p.name, '') AS plan_name,
                       CASE WHEN ups.user_id IS NOT NULL THEN 1 ELSE 0 END AS has_push
                FROM users u
                LEFT JOIN plans p ON p.id = u.plan_id
                LEFT JOIN (
                    SELECT DISTINCT user_id
                    FROM user_push_subscriptions
                    WHERE active=1 AND user_id IS NOT NULL
                ) ups ON ups.user_id = u.id
                WHERE u.is_active = 1
            """
            args = []
            clauses = []
            if plan_ids:
                placeholders = ','.join('?' * len(plan_ids))
                clauses.append(f"u.plan_id IN ({placeholders})")
                args.extend(plan_ids)
            if countries:
                placeholders = ','.join('?' * len(countries))
                clauses.append(f"u.country IN ({placeholders})")
                args.extend(countries)
            if clauses:
                query += ' AND ' + ' AND '.join(clauses)
            query += ' ORDER BY has_push DESC, u.id ASC'
            rows = c.execute(query, args).fetchall()
        return [dict(r) for r in rows]

    _make_tracking_token = _make_tracking_token_ml
    _substitute_vars = _substitute_vars_ml

    # ── VAPID diagnostic endpoints ────────────────────────────────────────────
    @app.route('/admin/push/vapid-info', methods=['GET'])
    def admin_vapid_info():
        if not session.get('login'):
            return jsonify({'error': 'Unauthorized'}), 401
        if not is_admin():
            return jsonify({'error': 'Forbidden'}), 403
        result = {'push_enabled': False, 'py_vapid_ok': False, 'pywebpush_ok': False,
                  'public_key': None, 'subscriber_count': 0, 'errors': []}
        ss = get_site_settings()
        result['push_enabled'] = ss.get('job_notify_push_enabled', '0') == '1'
        try:
            import pywebpush as _pwp
            result['pywebpush_ok'] = True
            result['pywebpush_version'] = getattr(_pwp, '__version__', '?')
        except ImportError as _e:
            result['errors'].append(f'pywebpush not installed: {_e}')
        try:
            from py_vapid import Vapid  # noqa: F401
            result['py_vapid_ok'] = True
        except ImportError as _e:
            result['errors'].append(f'py_vapid not installed: {_e}')
        if result['py_vapid_ok']:
            try:
                from helpers import get_vapid_keys as _gvk
                _, pub = _gvk()
                result['public_key'] = pub
            except Exception as _e:
                result['errors'].append(f'Key generation failed: {_e}')
        try:
            with _db() as _c:
                row = _c.execute("SELECT COUNT(*) FROM user_push_subscriptions WHERE active=1").fetchone()
                result['subscriber_count'] = row[0] if row else 0
        except Exception:
            pass
        return jsonify(result)

    @app.route('/admin/push/vapid-regenerate', methods=['POST'])
    def admin_vapid_regenerate():
        if not session.get('login'):
            return jsonify({'error': 'Unauthorized'}), 401
        if not is_admin():
            return jsonify({'error': 'Forbidden'}), 403
        try:
            with _db() as _c:
                _c.execute("DELETE FROM site_settings WHERE key IN ('vapid_private_key','vapid_public_key')")
                _c.commit()
            from helpers import get_vapid_keys as _gvk
            _, pub = _gvk()
            return jsonify({'ok': True, 'public_key': pub,
                            'warning': 'All existing push subscriptions are now invalid. Users must re-subscribe.'})
        except Exception as _e:
            return jsonify({'ok': False, 'error': str(_e)}), 500

    @app.route('/admin/push/test', methods=['POST'])
    def admin_push_test():
        if not session.get('login'):
            return jsonify({'error': 'Unauthorized'}), 401
        if not is_admin():
            return jsonify({'error': 'Forbidden'}), 403
        data = request.get_json(silent=True) or {}
        endpoint = (data.get('endpoint') or '').strip()
        p256dh   = (data.get('p256dh')   or '').strip()
        auth_key = (data.get('auth')      or '').strip()
        if not endpoint or not p256dh or not auth_key:
            return jsonify({'ok': False, 'error': 'Provide endpoint, p256dh and auth from your browser subscription.'}), 400
        # Call webpush directly so we can surface the real error to the admin UI
        try:
            import json as _j, base64 as _b64
            from pywebpush import webpush as _wp, WebPushException as _WPE
            from cryptography.hazmat.primitives.serialization import (
                load_pem_private_key as _lpk, Encoding as _Enc,
                PrivateFormat as _PF, NoEncryption as _NE,
            )
            from helpers import get_vapid_keys as _gvk
            _priv_pem, _ = _gvk()
            # py_vapid 1.9.x from_string() expects base64url-encoded DER, not PEM.
            # Convert: PEM → key object → DER bytes → base64url string.
            _key_obj    = _lpk(_priv_pem.encode(), password=None)
            _der        = _key_obj.private_bytes(_Enc.DER, _PF.TraditionalOpenSSL, _NE())
            _b64url_key = _b64.urlsafe_b64encode(_der).rstrip(b'=').decode()
            _claim_domain = os.getenv('website_url', 'localhost').replace('https://', '').replace('http://', '').split('/')[0]
            _wp(
                subscription_info={"endpoint": endpoint, "keys": {"p256dh": p256dh, "auth": auth_key}},
                data=_j.dumps({"title": "Admin Push Test", "body": "If you see this, push notifications are working!", "url": "/"}),
                vapid_private_key=_b64url_key,
                vapid_claims={"sub": f"mailto:admin@{_claim_domain}"},
                ttl=86400,
            )
            return jsonify({'ok': True, 'msg': 'Test push sent successfully!'})
        except Exception as _e:
            import traceback as _tb
            _trace = _tb.format_exc()
            # Surface HTTP status if the push server returned one
            _resp = getattr(_e, 'response', None)
            _status = getattr(_resp, 'status_code', None)
            _resp_body = ''
            try:
                _resp_body = _resp.text[:300] if _resp and hasattr(_resp, 'text') else ''
            except Exception:
                pass
            if _status in (404, 410):
                return jsonify({'ok': False, 'error': f'Subscription expired/invalid (HTTP {_status}). Re-subscribe in the browser.'})
            _detail = str(_e)
            if _status:
                _detail = f'HTTP {_status}: {_detail}'
            if _resp_body:
                _detail += f' | Server response: {_resp_body}'
            app.logger.error("Admin push test failed:\n%s", _trace)
            return jsonify({'ok': False, 'error': _detail, 'traceback': _trace})

    # ── End VAPID diagnostic endpoints ───────────────────────────────────────

    @app.route('/admin/broadcast', methods=['GET', 'POST'])
    @app.route('/<lang_code>/admin/broadcast', methods=['GET', 'POST'])
    def admin_broadcast(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            abort(403)
        plans = get_all_plans()
        site_settings = get_site_settings()
        site_name = site_settings.get('site_name', 'OnlineConvert')

        if request.method == 'POST':
            # parse recipient list
            user_ids_raw = request.form.getlist('user_ids')
            try:
                final_user_ids = [int(x) for x in user_ids_raw if x.strip().isdigit()]
            except Exception:
                final_user_ids = []

            channels = request.form.getlist('channels')
            do_email = 'email' in channels
            do_push = 'push' in channels

            email_subject = request.form.get('email_subject', '').strip()
            email_body = request.form.get('email_body', '').strip()
            push_title = request.form.get('push_title', '').strip()
            push_body_tpl = request.form.get('push_body', '').strip()
            push_icon = request.form.get('push_icon', '').strip()
            push_image = request.form.get('push_image', '').strip()
            push_url = request.form.get('push_url', '').strip()

            filter_plans_raw = request.form.getlist('filter_plan_ids')
            filter_countries_raw = request.form.get('filter_countries', '').strip()
            filter_countries = [c.strip() for c in filter_countries_raw.split(',') if c.strip()] if filter_countries_raw else []

            # ── Schedule mode ─────────────────────────────────────────────────
            send_mode = request.form.get('send_mode', 'now')
            scheduled_at_raw = request.form.get('scheduled_at', '').strip()
            scheduled_at = None
            if send_mode == 'schedule':
                if not scheduled_at_raw:
                    flash("Please provide a scheduled date and time.", "error")
                    return redirect(url_for('admin_broadcast'))
                try:
                    from datetime import datetime as _dt, timezone as _tz, timedelta as _td
                    scheduled_at = _dt.fromisoformat(scheduled_at_raw)
                    min_future = _dt.utcnow() + _td(minutes=5)
                    if scheduled_at <= min_future:
                        flash("Scheduled time must be at least 5 minutes in the future.", "error")
                        return redirect(url_for('admin_broadcast'))
                except ValueError:
                    flash("Invalid scheduled date/time format.", "error")
                    return redirect(url_for('admin_broadcast'))

            if not final_user_ids:
                flash("No recipients selected.", "error")
                return redirect(url_for('admin_broadcast'))

            if not do_email and not do_push:
                flash("Select at least one channel (email or push).", "error")
                return redirect(url_for('admin_broadcast'))

            if do_email and (not email_subject or not email_body):
                flash("Email channel requires both subject and body.", "error")
                return redirect(url_for('admin_broadcast'))

            if do_push and (not push_title or not push_body_tpl):
                flash("Push channel requires both title and body.", "error")
                return redirect(url_for('admin_broadcast'))

            channel_label = '+'.join(channels)
            # Look up valid user IDs
            with _db() as c:
                placeholders = ','.join('?' * len(final_user_ids))
                users_rows = c.execute(
                    f"SELECT id FROM users WHERE id IN ({placeholders})",
                    final_user_ids
                ).fetchall()
                valid_uids = [r['id'] for r in users_rows]

            if not valid_uids:
                flash("None of the selected recipients could be found.", "error")
                return redirect(url_for('admin_broadcast'))

            if scheduled_at:
                # ── Save as scheduled, register APScheduler job ─────────────
                with _db() as c:
                    cur = c.execute(
                        """INSERT INTO broadcast_sends
                           (channel, filter_plans_json, filter_countries_json,
                            email_subject, email_body, push_title, push_body,
                            push_icon, push_image, push_url,
                            total_recipients, emails_sent, push_sent, anon_push_sent, status, scheduled_at)
                           VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,'scheduled',?)""",
                        (channel_label,
                         json.dumps([int(x) for x in filter_plans_raw if str(x).isdigit()]),
                         json.dumps(filter_countries),
                         email_subject, email_body, push_title, push_body_tpl,
                         push_icon, push_image, push_url,
                         len(valid_uids), 0, 0, 0,
                         scheduled_at.strftime('%Y-%m-%dT%H:%M:%S'))
                    )
                    broadcast_id = cur.lastrowid
                    c.executemany(
                        "INSERT INTO broadcast_recipients (broadcast_id, user_id, email_sent, push_sent) VALUES (?,?,?,?)",
                        [(broadcast_id, uid, 0, 0) for uid in valid_uids]
                    )
                    c.commit()

                from app import sched as _sched
                _sched.add_job(
                    _execute_broadcast, trigger='date', run_date=scheduled_at,
                    id=f'broadcast_{broadcast_id}', args=[broadcast_id], replace_existing=True
                )
                log_event('admin_broadcast', f"Broadcast {broadcast_id} scheduled for {scheduled_at.isoformat()}, {len(valid_uids)} recipients")
                flash(f"Broadcast scheduled for {scheduled_at.strftime('%Y-%m-%d %H:%M')} UTC with {len(valid_uids)} recipients.", "success")
                return redirect(url_for('admin_broadcast'))

            # ── Send now path ─────────────────────────────────────────────────
            with _db() as c:
                cur = c.execute(
                    """INSERT INTO broadcast_sends
                       (channel, filter_plans_json, filter_countries_json,
                        email_subject, email_body, push_title, push_body,
                        push_icon, push_image, push_url,
                        total_recipients, emails_sent, push_sent, anon_push_sent, status)
                       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,'pending')""",
                    (channel_label,
                     json.dumps([int(x) for x in filter_plans_raw if str(x).isdigit()]),
                     json.dumps(filter_countries),
                     email_subject, email_body, push_title, push_body_tpl,
                     push_icon, push_image, push_url,
                     len(valid_uids), 0, 0, 0)
                )
                broadcast_id = cur.lastrowid
                c.executemany(
                    "INSERT INTO broadcast_recipients (broadcast_id, user_id, email_sent, push_sent) VALUES (?,?,?,?)",
                    [(broadcast_id, uid, 0, 0) for uid in valid_uids]
                )
                c.commit()

            try:
                _execute_broadcast(broadcast_id)
            except Exception as _exec_err:
                import traceback as _tb
                app.logger.error("_execute_broadcast(%s) crashed:\n%s", broadcast_id, _tb.format_exc())
                flash(f"Broadcast failed: {_exec_err}", "error")
                return redirect(url_for('admin_broadcast'))

            with _db() as c:
                send_row = dict(c.execute(
                    "SELECT * FROM broadcast_sends WHERE id=?", (broadcast_id,)
                ).fetchone())
                history = c.execute(
                    "SELECT * FROM broadcast_sends ORDER BY created_at DESC LIMIT 50"
                ).fetchall()

            do_email_f = 'email' in channel_label
            do_push_f = 'push' in channel_label
            _total = len(valid_uids)
            _emails_sent = send_row['emails_sent']
            _push_sent = send_row['push_sent']
            summary = {
                'total': _total,
                'emails_sent': _emails_sent,
                'push_sent': _push_sent,
                'email_failed': max(0, _total - _emails_sent) if do_email_f else 0,
                'push_failed': max(0, _total - _push_sent) if do_push_f else 0,
                'failures': max(0, (_total if do_email_f else 0) + (_total if do_push_f else 0) - _emails_sent - _push_sent),
            }
            return render_template('admin/broadcast.html',
                                   plans=plans, summary=summary,
                                   history=[dict(h) for h in history])

        # GET
        with _db() as c:
            history = c.execute(
                "SELECT * FROM broadcast_sends ORDER BY created_at DESC LIMIT 50"
            ).fetchall()
        return render_template('admin/broadcast.html',
                               plans=plans, summary=None,
                               history=[dict(h) for h in history])

    @app.route('/admin/broadcast/preview', methods=['GET', 'POST'])
    @app.route('/<lang_code>/admin/broadcast/preview', methods=['GET', 'POST'])
    def admin_broadcast_preview(lang_code=None):
        # Always return JSON — never HTML — so AJAX callers get a structured response.
        try:
            if not session.get('login'):
                return jsonify({'error': True, 'message': 'Not authenticated. Please log in again.'}), 401
            if not is_admin():
                return jsonify({'error': True, 'message': 'Admin access required.'}), 403
            if request.method == 'GET':
                return redirect(url_for('admin_broadcast'))
            data = request.get_json(force=True, silent=True) or {}
            plan_ids_raw = data.get('plan_ids', [])
            countries_raw = data.get('countries', [])
            page = max(1, int(data.get('page', 1)))
            per_page = max(1, min(200, int(data.get('per_page', 50))))
            try:
                plan_ids = [int(x) for x in plan_ids_raw if str(x).isdigit()]
            except Exception:
                plan_ids = []
            countries = [str(c).strip() for c in countries_raw if str(c).strip()]
            try:
                all_users = _get_broadcast_recipients(plan_ids, countries)
            except Exception as exc:
                app.logger.exception("admin_broadcast_preview error: %s", exc)
                return jsonify({'error': True, 'message': f'Failed to load recipients: {exc}',
                                'users': [], 'total': 0, 'page': 1, 'per_page': per_page,
                                'total_pages': 1}), 200
            total = len(all_users)
            push_total = sum(1 for u in all_users if u.get('has_push'))
            email_only_total = total - push_total
            # Count anonymous (not-logged-in) push subscribers separately
            try:
                with _db() as _ca:
                    _row = _ca.execute(
                        "SELECT COUNT(*) FROM user_push_subscriptions WHERE user_id IS NULL AND active=1"
                    ).fetchone()
                    anon_push_total = _row[0] if _row else 0
            except Exception:
                anon_push_total = 0
            offset = (page - 1) * per_page
            paged = all_users[offset:offset + per_page]
            for u in paged:
                u['has_push'] = bool(u.get('has_push'))
            return jsonify({
                'users': paged,
                'total': total,
                'push_total': push_total,
                'email_only_total': email_only_total,
                'anon_push_total': anon_push_total,
                'page': page,
                'per_page': per_page,
                'total_pages': max(1, (total + per_page - 1) // per_page),
            })
        except Exception as exc:
            app.logger.exception("admin_broadcast_preview unhandled error: %s", exc)
            return jsonify({'error': True, 'message': f'Server error: {exc}',
                            'users': [], 'total': 0, 'page': 1, 'per_page': 50,
                            'total_pages': 1}), 200

    @app.route('/admin/broadcast/user-search', methods=['GET'])
    @app.route('/<lang_code>/admin/broadcast/user-search', methods=['GET'])
    def admin_broadcast_user_search(lang_code=None):
        """Search all active users by username/email for manual add."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        if not is_admin():
            return jsonify({'users': [], 'error': 'Admin access required.'}), 403
        q = (request.args.get('q') or '').strip()
        if not q:
            return jsonify({'users': []})
        pattern = f'%{q}%'
        with _db() as c:
            rows = c.execute(
                """SELECT u.id, u.username, u.email, u.country,
                          u.subscription_status, u.created_at AS joined_date,
                          p.name AS plan_name,
                          CASE WHEN ups.user_id IS NOT NULL THEN 1 ELSE 0 END AS has_push
                   FROM users u
                   LEFT JOIN plans p ON p.id=u.plan_id
                   LEFT JOIN (
                       SELECT DISTINCT user_id FROM user_push_subscriptions
                       WHERE active=1 AND user_id IS NOT NULL
                   ) ups ON ups.user_id = u.id
                   WHERE u.is_active=1 AND (u.username LIKE ? OR u.email LIKE ?)
                   ORDER BY u.username ASC LIMIT 20""",
                (pattern, pattern)
            ).fetchall()
        return jsonify({'users': [dict(r) for r in rows]})

    @app.route('/admin/broadcast/history', methods=['GET'])
    @app.route('/<lang_code>/admin/broadcast/history', methods=['GET'])
    def admin_broadcast_history(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        page = max(1, request.args.get('page', 1, type=int))
        per_page = max(1, min(100, request.args.get('per_page', 20, type=int)))
        offset = (page - 1) * per_page
        with _db() as c:
            total = c.execute("SELECT COUNT(*) FROM broadcast_sends").fetchone()[0]
            rows = c.execute(
                "SELECT * FROM broadcast_sends ORDER BY created_at DESC LIMIT ? OFFSET ?",
                (per_page, offset)
            ).fetchall()
        return jsonify({
            'history': [dict(r) for r in rows],
            'total': total,
            'page': page,
            'per_page': per_page,
            'total_pages': max(1, (total + per_page - 1) // per_page),
        })

    @app.route('/admin/broadcast/<int:broadcast_id>/report')
    @app.route('/<lang_code>/admin/broadcast/<int:broadcast_id>/report')
    def admin_broadcast_report(broadcast_id, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            send = c.execute("SELECT * FROM broadcast_sends WHERE id=?", (broadcast_id,)).fetchone()
            if not send:
                flash("Broadcast not found.", "error")
                return redirect(url_for('admin_broadcast'))
            send = dict(send)
            recipients = c.execute(
                """SELECT br.*, u.username, u.email, u.country,
                          p.name AS plan_name, u.subscription_status
                   FROM broadcast_recipients br
                   JOIN users u ON u.id = br.user_id
                   LEFT JOIN plans p ON p.id = u.plan_id
                   WHERE br.broadcast_id=?
                   ORDER BY br.id ASC""",
                (broadcast_id,)
            ).fetchall()
            recipients = [dict(r) for r in recipients]

        # ── Aggregate daily unique first-open / first-click counts ────────────
        # Each recipient's email_opened_at / push_clicked_at records the FIRST event.
        # Charts show "unique new openers/clickers per day" which is accurate with
        # the current schema (no separate event log for repeated events).
        from collections import defaultdict as _dd
        email_opens_by_day = _dd(int)
        push_clicks_by_day = _dd(int)
        for r in recipients:
            if r.get('email_opened_at') and r.get('email_open_count', 0) > 0:
                day = r['email_opened_at'][:10]
                email_opens_by_day[day] += 1  # count unique openers per day
            if r.get('push_clicked_at') and r.get('push_click_count', 0) > 0:
                day = r['push_clicked_at'][:10]
                push_clicks_by_day[day] += 1  # count unique clickers per day

        all_days = sorted(set(list(email_opens_by_day.keys()) + list(push_clicks_by_day.keys())))
        chart_labels = all_days or [send['created_at'][:10]]
        chart_opens = [email_opens_by_day.get(d, 0) for d in chart_labels]
        chart_clicks = [push_clicks_by_day.get(d, 0) for d in chart_labels]

        # ── Summary stats ───────────────────────────────────────────────────
        total_recipients = len(recipients)
        emails_sent = sum(1 for r in recipients if r['email_sent'])
        emails_opened_unique = sum(1 for r in recipients if r['email_open_count'] > 0)
        emails_opened_total = sum(r['email_open_count'] for r in recipients)
        emails_not_opened = emails_sent - emails_opened_unique
        push_sent = sum(1 for r in recipients if r['push_sent'])
        push_clicked_unique = sum(1 for r in recipients if r['push_click_count'] > 0)

        return render_template('admin/broadcast_report.html',
            send=send,
            recipients=recipients,
            chart_labels=json.dumps(chart_labels),
            chart_opens=json.dumps(chart_opens),
            chart_clicks=json.dumps(chart_clicks),
            total_recipients=total_recipients,
            emails_sent=emails_sent,
            emails_opened_unique=emails_opened_unique,
            emails_opened_total=emails_opened_total,
            emails_not_opened=max(0, emails_not_opened),
            push_sent=push_sent,
            push_clicked_unique=push_clicked_unique,
        )

    @app.route('/admin/broadcast/<int:broadcast_id>/clear-analytics', methods=['POST'])
    @app.route('/<lang_code>/admin/broadcast/<int:broadcast_id>/clear-analytics', methods=['POST'])
    def admin_broadcast_clear_analytics(broadcast_id, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            c.execute(
                """UPDATE broadcast_recipients
                   SET email_open_count=0, email_opened_at=NULL,
                       push_click_count=0, push_clicked_at=NULL
                   WHERE broadcast_id=?""",
                (broadcast_id,)
            )
            c.commit()
        return jsonify({'ok': True})

    @app.route('/admin/broadcast/<int:broadcast_id>/cancel', methods=['POST'])
    @app.route('/<lang_code>/admin/broadcast/<int:broadcast_id>/cancel', methods=['POST'])
    def admin_broadcast_cancel(broadcast_id, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute("SELECT status FROM broadcast_sends WHERE id=?", (broadcast_id,)).fetchone()
            if not row or row['status'] != 'scheduled':
                return jsonify({'ok': False, 'error': 'Broadcast not found or not in scheduled state'}), 400
            c.execute("UPDATE broadcast_sends SET status='cancelled' WHERE id=?", (broadcast_id,))
            c.commit()
        try:
            from app import sched as _sched
            _sched.remove_job(f'broadcast_{broadcast_id}')
        except Exception:
            pass
        log_event('admin_broadcast', f"Broadcast {broadcast_id} cancelled by admin")
        return jsonify({'ok': True})

    @app.route('/admin/broadcast/delete/<int:broadcast_id>', methods=['POST'])
    @app.route('/<lang_code>/admin/broadcast/delete/<int:broadcast_id>', methods=['POST'])
    def admin_broadcast_delete(broadcast_id, lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            _bc = c.execute("SELECT subject FROM broadcast_sends WHERE id=?", (broadcast_id,)).fetchone()
            _bc_name = _bc['subject'] if _bc else str(broadcast_id)
            c.execute("DELETE FROM broadcast_recipients WHERE broadcast_id=?", (broadcast_id,))
            c.execute("DELETE FROM broadcast_sends WHERE id=?", (broadcast_id,))
            c.commit()
        _admin = session.get('admin_username', 'admin')
        log_deletion('broadcast', entity_id=broadcast_id, entity_name=_bc_name,
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'broadcast_id': broadcast_id})
        return jsonify({'ok': True})

    @app.route('/admin/broadcast/clear', methods=['POST'])
    @app.route('/<lang_code>/admin/broadcast/clear', methods=['POST'])
    def admin_broadcast_clear(lang_code=None):
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            c.execute("DELETE FROM broadcast_recipients")
            c.execute("DELETE FROM broadcast_sends")
            c.commit()
        return jsonify({'ok': True})

    # ===== Admin User Files Manager =====

    @app.route('/admin/users/files')
    @app.route('/<lang_code>/admin/users/files')
    def admin_user_files(lang_code=None):
        """Admin view: all users' conversions, with share link/grant detail panels."""
        logged_in, r = authenticate()
        if not logged_in:
            return r

        page = request.args.get('page', 1, type=int)
        per_page = 30
        search = (request.args.get('q') or '').strip()
        offset = (page - 1) * per_page

        with _db() as c:
            pending_blogs = 0
            try:
                pending_blogs = c.execute("SELECT COUNT(*) FROM blogs WHERE status='pending_review'").fetchone()[0]
            except Exception:
                pass

            if search:
                pat = '%' + search + '%'
                total_row = c.execute(
                    """SELECT COUNT(DISTINCT conv.id) FROM conversions conv
                       LEFT JOIN users u ON u.id = conv.user_id
                       WHERE u.email LIKE ? OR u.username LIKE ?""",
                    (pat, pat)
                ).fetchone()
                total = total_row[0] if total_row else 0
                rows = c.execute(
                    """SELECT conv.id, conv.job_id, conv.file_name, conv.orig_file_name,
                              conv.input_format, conv.output_format, conv.status,
                              conv.orig_file_size, conv.created_at,
                              u.id AS user_id, u.email AS user_email, u.username AS user_username,
                              (SELECT COUNT(*) FROM shared_links sl WHERE sl.job_id = conv.job_id) AS share_link_count,
                              (SELECT COUNT(*) FROM job_access_grants g WHERE g.job_id = conv.job_id) AS grant_count
                       FROM conversions conv
                       LEFT JOIN users u ON u.id = conv.user_id
                       WHERE u.email LIKE ? OR u.username LIKE ?
                       ORDER BY conv.id DESC LIMIT ? OFFSET ?""",
                    (pat, pat, per_page, offset)
                ).fetchall()
            else:
                total = c.execute("SELECT COUNT(*) FROM conversions").fetchone()[0]
                rows = c.execute(
                    """SELECT conv.id, conv.job_id, conv.file_name, conv.orig_file_name,
                              conv.input_format, conv.output_format, conv.status,
                              conv.orig_file_size, conv.created_at,
                              u.id AS user_id, u.email AS user_email, u.username AS user_username,
                              (SELECT COUNT(*) FROM shared_links sl WHERE sl.job_id = conv.job_id) AS share_link_count,
                              (SELECT COUNT(*) FROM job_access_grants g WHERE g.job_id = conv.job_id) AS grant_count
                       FROM conversions conv
                       LEFT JOIN users u ON u.id = conv.user_id
                       ORDER BY conv.id DESC LIMIT ? OFFSET ?""",
                    (per_page, offset)
                ).fetchall()

        files = [dict(r) for r in rows]
        total_pages = max(1, (total + per_page - 1) // per_page)

        site_settings = get_site_settings()
        site_url = (site_settings.get('site_url', '') or '').rstrip('/')
        if not site_url:
            site_url = request.host_url.rstrip('/')

        return render_template('admin/user_files.html',
                               files=files, total=total, page=page,
                               total_pages=total_pages, per_page=per_page,
                               search=search, site_url=site_url,
                               pending_blogs_count=pending_blogs)

    # ===== Admin Share Manager =====

    @app.route('/admin/shares')
    @app.route('/<lang_code>/admin/shares')
    def admin_share_manager(lang_code=None):
        """Admin view: all users' share links and grants, with revoke capability."""
        logged_in, r = authenticate()
        if not logged_in:
            return r

        page = request.args.get('page', 1, type=int)
        per_page = 30
        search = (request.args.get('q') or '').strip()
        tab = request.args.get('tab', 'links')
        offset = (page - 1) * per_page

        with _db() as c:
            pending_blogs = 0
            try:
                pending_blogs = c.execute("SELECT COUNT(*) FROM blogs WHERE status='pending_review'").fetchone()[0]
            except Exception:
                pass

            if tab == 'grants':
                # Email-based access grants
                base_q = """
                    SELECT g.id, g.job_id, g.granted_to_email, g.granted_to_user_id, g.granted_at,
                           conv.file_name, conv.input_format, conv.output_format,
                           u.email AS owner_email, u.username AS owner_username, g.granted_by_user_id
                    FROM job_access_grants g
                    LEFT JOIN conversions conv ON conv.job_id = g.job_id
                    LEFT JOIN users u ON u.id = conv.user_id
                """
                if search:
                    pat = '%' + search + '%'
                    count_row = c.execute(
                        """SELECT COUNT(DISTINCT g.id) FROM job_access_grants g
                           LEFT JOIN conversions conv ON conv.job_id = g.job_id
                           LEFT JOIN users u ON u.id = conv.user_id
                           WHERE u.email LIKE ? OR g.granted_to_email LIKE ?""",
                        (pat, pat)
                    ).fetchone()
                    total = count_row[0] if count_row else 0
                    rows = c.execute(
                        base_q + " WHERE (u.email LIKE ? OR g.granted_to_email LIKE ?) GROUP BY g.id ORDER BY g.id DESC LIMIT ? OFFSET ?",
                        (pat, pat, per_page, offset)
                    ).fetchall()
                else:
                    total = c.execute("SELECT COUNT(*) FROM job_access_grants").fetchone()[0]
                    rows = c.execute(base_q + " GROUP BY g.id ORDER BY g.id DESC LIMIT ? OFFSET ?", (per_page, offset)).fetchall()
                items = [dict(r) for r in rows]
            else:
                # Share links
                base_q = """
                    SELECT sl.id, sl.token, sl.job_id, sl.created_at, sl.expires_at,
                           sl.is_one_time, sl.is_accessed,
                           CASE WHEN sl.password_hash IS NOT NULL AND sl.password_hash != '' THEN 1 ELSE 0 END AS has_password,
                           conv.file_name, conv.input_format, conv.output_format,
                           u.email AS owner_email, u.username AS owner_username
                    FROM shared_links sl
                    LEFT JOIN conversions conv ON conv.job_id = sl.job_id
                    LEFT JOIN users u ON u.id = sl.user_id
                """
                if search:
                    pat = '%' + search + '%'
                    count_row = c.execute(
                        """SELECT COUNT(DISTINCT sl.id) FROM shared_links sl
                           LEFT JOIN users u ON u.id = sl.user_id
                           WHERE u.email LIKE ?""",
                        (pat,)
                    ).fetchone()
                    total = count_row[0] if count_row else 0
                    rows = c.execute(
                        base_q + " WHERE u.email LIKE ? GROUP BY sl.id ORDER BY sl.id DESC LIMIT ? OFFSET ?",
                        (pat, per_page, offset)
                    ).fetchall()
                else:
                    total = c.execute("SELECT COUNT(*) FROM shared_links").fetchone()[0]
                    rows = c.execute(base_q + " GROUP BY sl.id ORDER BY sl.id DESC LIMIT ? OFFSET ?", (per_page, offset)).fetchall()
                items = [dict(r) for r in rows]

        total_pages = max(1, (total + per_page - 1) // per_page)
        site_settings = get_site_settings()
        site_url = (site_settings.get('site_url', '') or '').rstrip('/')
        if not site_url:
            site_url = request.host_url.rstrip('/')

        return render_template('admin/user_shares.html',
                               items=items, total=total, page=page,
                               total_pages=total_pages, per_page=per_page,
                               search=search, tab=tab,
                               site_url=site_url,
                               pending_blogs_count=pending_blogs)

    @app.route('/admin/shares/revoke-link/<token>', methods=['POST'])
    def admin_revoke_share_link(token):
        """Admin: forcibly revoke a share link regardless of owner."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute("SELECT user_id, job_id FROM shared_links WHERE token=?", (token,)).fetchone()
            if not row:
                return jsonify({'ok': False, 'error': 'Not found'}), 404
            c.execute("DELETE FROM shared_links WHERE token=?", (token,))
            c.commit()
        _admin = session.get('admin_username', 'admin')
        log_deletion('share', entity_id=token, entity_name=token,
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'token': token, 'job_id': row['job_id']},
                     entity_owner_user_id=row['user_id'])
        return jsonify({'ok': True})

    @app.route('/admin/shares/revoke-grant/<int:grant_id>', methods=['POST'])
    def admin_revoke_share_grant(grant_id):
        """Admin: forcibly revoke an email-based grant regardless of owner."""
        logged_in, r = authenticate()
        if not logged_in:
            return r
        with _db() as c:
            row = c.execute(
                "SELECT job_id, granted_to_email, granted_to_user_id, "
                "c.user_id AS owner_id "
                "FROM job_access_grants g "
                "LEFT JOIN conversions c ON c.job_id = g.job_id "
                "WHERE g.id=? LIMIT 1", (grant_id,)
            ).fetchone()
            if not row:
                return jsonify({'ok': False, 'error': 'Not found'}), 404
            c.execute("DELETE FROM job_access_grants WHERE id=?", (grant_id,))
            c.commit()
        _admin = session.get('admin_username', 'admin')
        _grantee = row['granted_to_email'] or str(grant_id)
        log_deletion('share_grant', entity_id=grant_id, entity_name=_grantee,
                     actor_username=_admin, actor_role='admin',
                     extra_meta={'grant_id': grant_id, 'job_id': row['job_id'],
                                 'granted_to_user_id': row['granted_to_user_id']},
                     entity_owner_user_id=row['owner_id'])
        return jsonify({'ok': True})

    # ===== 404 Handler - MUST be last =====
    @app.errorhandler(404)
    def not_found(error):
        return render_template('404.html'), 404