import os
import sys
import uuid
from flask import Flask, current_app, make_response, request, jsonify, send_from_directory, send_file, flash, render_template, json, redirect, url_for, abort, session, g
from flask_cors import CORS
from werkzeug.middleware.proxy_fix import ProxyFix
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from werkzeug.utils import secure_filename
from urllib.parse import urlparse as url_parse
from werkzeug.security import generate_password_hash, check_password_hash
import requests
import json
import pyotp
import qrcode
import io
import base64

try:
    from rq import Queue
    from rq.job import Job
    from worker import conn as _redis_conn
except Exception:
    Queue = None
    Job = None
    _redis_conn = None
    conn = None
import boto3
import time
from datetime import datetime, timedelta
from urllib.parse import urlparse
import sqlite3
from slugify import slugify

from dotenv import load_dotenv

load_dotenv()

# Always read website_url from .env — this is the single source of truth for the domain
website_url = os.getenv("website_url", "localhost")

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

from apscheduler.schedulers.background import BackgroundScheduler
from schedular import expire_files, downgrade_expired_subscriptions
from flask_babel import Babel, gettext as _original_gettext
import flask_babel as _flask_babel
from flask_minify import minify
from flask_compress import Compress

def _expire_overdue_jobs():
    """
    Safety-net scheduler (runs every 60 s):
    Find conversion jobs stuck in 'started' state that have exceeded their
    plan's max_processing_seconds (falls back to 2 h hard limit when 0/unset),
    kill their child processes, then mark them failed in DB.
    """
    try:
        import sqlite3 as _sl, json as _j, time as _t, calendar as _cal
        from datetime import datetime as _dt
        _db_path = os.path.join(os.path.dirname(__file__), "storage", "sqlite.db")
        _FALLBACK_SECS = 7200  # 2-hour hard limit if plan has no timeout

        with _sl.connect(_db_path) as _conn:
            _conn.row_factory = _sl.Row
            # Join through conversions → users → plans to get the plan timeout.
            # Anonymous jobs have no user, so fall back to plan 1 (free plan).
            _rows = _conn.execute("""
                SELECT j.id,
                       j.started_at,
                       COALESCE(p.max_processing_seconds, 0) AS plan_max_secs
                FROM local_jobs j
                LEFT JOIN conversions c ON c.job_id = j.id
                LEFT JOIN users u ON u.id = c.user_id
                LEFT JOIN plans p ON p.id = COALESCE(u.plan_id, 1)
                WHERE j.status = 'started'
                  AND j.started_at IS NOT NULL
                GROUP BY j.id
            """).fetchall()

            _now = _t.time()
            _overdue = []
            for _row in _rows:
                _max_secs = int(_row['plan_max_secs'] or 0)
                if _max_secs <= 0:
                    _max_secs = _FALLBACK_SECS
                try:
                    _started = _dt.strptime(_row['started_at'], '%Y-%m-%d %H:%M:%S')
                    _elapsed = _now - _cal.timegm(_started.timetuple())
                    if _elapsed > _max_secs:
                        _overdue.append(_row['id'])
                except Exception:
                    pass

            if _overdue:
                # Kill child processes before marking as failed
                from local_jobs import _kill_job_procs
                for _jid in _overdue:
                    try:
                        _kill_job_procs(_jid)
                    except Exception:
                        pass

                _msg = _j.dumps({
                    'error': True,
                    'message': 'Job expired: exceeded maximum processing time.',
                    'results': []
                })
                _ph = ','.join('?' * len(_overdue))
                _conn.execute(
                    f"UPDATE local_jobs SET status='failed', result_json=?, "
                    f"completed_at=datetime('now') WHERE id IN ({_ph})",
                    [_msg] + _overdue
                )
                try:
                    _conn.execute(
                        f"UPDATE conversions SET status='failed', "
                        f"error_message='Job expired: exceeded maximum processing time', "
                        f"completed_at=COALESCE(completed_at, datetime('now')) "
                        f"WHERE job_id IN ({_ph}) AND status IN ('pending','started')",
                        _overdue
                    )
                except Exception:
                    pass
                _conn.commit()
    except Exception:
        pass


# sched = BackgroundScheduler(daemon=True)
# sched.add_job(expire_files, 'interval', minutes=60)
# sched.add_job(_expire_overdue_jobs, 'interval', seconds=60)
# sched.start()


if os.getpid() == 1:
    sched = BackgroundScheduler(daemon=True)
    sched.add_job(expire_files, 'interval', minutes=60)
    sched.add_job(_expire_overdue_jobs, 'interval', seconds=60)
    sched.add_job(downgrade_expired_subscriptions, 'interval', minutes=5)
    sched.start()
    # Run once immediately to handle any expirations that occurred while the app was offline
    try:
        downgrade_expired_subscriptions()
    except Exception:
        pass
    print(f"Scheduler started in main process (PID: {os.getpid()})")
else:
    print(f"Scheduler NOT started in worker process (PID: {os.getpid()})")

app = Flask(__name__, subdomain_matching=True)

# Gzip / Brotli compression for all text responses
app.config['COMPRESS_MIMETYPES'] = [
    'text/html', 'text/css', 'text/javascript',
    'application/javascript', 'application/json',
    'image/svg+xml',
]
app.config['COMPRESS_LEVEL'] = 6
app.config['COMPRESS_MIN_SIZE'] = 500
Compress(app)

# SESSION CONFIG
if not os.environ.get('REPLIT_DEV_DOMAIN'):
    app.config['SESSION_COOKIE_DOMAIN'] = '.' + website_url
app.config['SESSION_COOKIE_SECURE'] = not bool(os.environ.get('REPLIT_DEV_DOMAIN'))
app.config['SESSION_COOKIE_HTTPONLY'] = True
app.config['SESSION_COOKIE_SAMESITE'] = 'Lax'
app.secret_key = 'super secret key'  # Move this up too

if not os.environ.get('REPLIT_DEV_DOMAIN'):
    app.config['SERVER_NAME'] = website_url

# CORS configuration
CORS(app, 
     resources={
         r"/auth/*": {
             "origins": [f"https://{website_url}", f"https://*.{website_url}"],
             "supports_credentials": True,
             "allow_headers": ["Content-Type"],
             "methods": ["POST", "OPTIONS"]
         }
     })

app.wsgi_app = ProxyFix(app.wsgi_app, x_proto=1, x_host=1)

app.url_map.strict_slashes = False


import logging
logging.basicConfig(level=logging.WARNING)
app.logger.setLevel(logging.WARNING)



@app.after_request
def add_cors_headers(response):
    """Add CORS headers to all responses for subdomain access"""
    origin = request.headers.get('Origin')
    
    # Allow all onlineconvert.cc subdomains
    if origin and (origin == f"https://{website_url}" or origin.endswith(f".{website_url}")):
        response.headers['Access-Control-Allow-Origin'] = origin
        response.headers['Access-Control-Allow-Credentials'] = 'true'
        response.headers['Access-Control-Allow-Methods'] = 'POST, OPTIONS'
        response.headers['Access-Control-Allow-Headers'] = 'Content-Type'
    
    return response

@app.before_request
def handle_preflight():
    """Handle OPTIONS requests for CORS preflight"""
    if request.method == 'OPTIONS':
        response = make_response()
        origin = request.headers.get('Origin')
        if origin and (origin == f"https://{website_url}" or origin.endswith(f".{website_url}")):
            response.headers['Access-Control-Allow-Origin'] = origin
            response.headers['Access-Control-Allow-Credentials'] = 'true'
            response.headers['Access-Control-Allow-Methods'] = 'POST, OPTIONS'
            response.headers['Access-Control-Allow-Headers'] = 'Content-Type'
            return response
    return None


@app.before_request
def catch_custom_admin_login():
    """Catch custom admin login URLs - only from root domain or with language code"""
    try:
        from helpers import get_site_settings
        from configs.languages import supported_languages

        host = request.host.split(':')[0].lower()
        server_name = (app.config.get('SERVER_NAME') or '').lower()
        is_subdomain = server_name and host != server_name and host.endswith('.' + server_name)

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

        if not custom_slug:
            return

        path = request.path.strip('/')

        # Case 1: Root level match (e.g., /baby12)
        if path == custom_slug:
            if is_subdomain:
                abort(404)
            else:
                from routes.admin import _admin_login_handler
                return _admin_login_handler()

        # Case 2: Language-prefixed match (e.g., /zh/baby12)
        if '/' in path:
            parts = path.split('/')
            if len(parts) == 2 and parts[0] in supported_languages and parts[1] == custom_slug:
                if is_subdomain:
                    abort(404)
                else:
                    g.lang_code = parts[0]
                    from routes.admin import _admin_login_handler
                    return _admin_login_handler()

        # Block default admin login when custom slug is set
        if path == 'admin/login' or path.endswith('/admin/login'):
            abort(404)

    except Exception:
        pass

    return None


with sqlite3.connect("storage/sqlite.db") as _db_init:
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS site_settings (
            key TEXT PRIMARY KEY,
            value TEXT NOT NULL DEFAULT ''
        )
    """)
    # text_overrides: language-aware CMS string overrides.
    # Schema v2 adds `lang` so editors can only override their assigned language.
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS text_overrides (
            lang TEXT NOT NULL DEFAULT 'en',
            original_text TEXT NOT NULL,
            override_text TEXT NOT NULL DEFAULT '',
            PRIMARY KEY (lang, original_text)
        )
    """)
    # Migrate v1 schema (original_text TEXT PRIMARY KEY) → v2 (lang, original_text)
    _v1_cols = [r[1] for r in _db_init.execute("PRAGMA table_info(text_overrides)").fetchall()]
    if 'lang' not in _v1_cols:
        _db_init.executescript("""
            CREATE TABLE IF NOT EXISTS text_overrides_v2 (
                lang TEXT NOT NULL DEFAULT 'en',
                original_text TEXT NOT NULL,
                override_text TEXT NOT NULL DEFAULT '',
                PRIMARY KEY (lang, original_text)
            );
            INSERT OR IGNORE INTO text_overrides_v2 (lang, original_text, override_text)
                SELECT 'en', original_text, override_text FROM text_overrides;
            DROP TABLE text_overrides;
            ALTER TABLE text_overrides_v2 RENAME TO text_overrides;
        """)
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS page_content (
            page_id TEXT NOT NULL,
            field TEXT NOT NULL,
            lang TEXT NOT NULL DEFAULT 'en',
            value TEXT NOT NULL DEFAULT '',
            PRIMARY KEY (page_id, field, lang)
        )
    """)
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS page_faqs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            page_id TEXT NOT NULL,
            lang TEXT NOT NULL DEFAULT 'en',
            position INTEGER NOT NULL DEFAULT 0,
            question TEXT NOT NULL DEFAULT '',
            answer TEXT NOT NULL DEFAULT ''
        )
    """)
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS admin_profiles (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL DEFAULT 'admin',
            first_name TEXT NOT NULL DEFAULT '',
            last_name TEXT NOT NULL DEFAULT '',
            email TEXT NOT NULL DEFAULT '',
            password_hash TEXT NOT NULL DEFAULT '',
            profile_picture TEXT NOT NULL DEFAULT '',
            profile_description TEXT NOT NULL DEFAULT '',
            totp_secret TEXT NOT NULL DEFAULT '',
            totp_enabled INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    try:
        _db_init.execute("ALTER TABLE admin_profiles ADD COLUMN first_name TEXT NOT NULL DEFAULT ''")
    except:
        pass
    try:
        _db_init.execute("ALTER TABLE admin_profiles ADD COLUMN last_name TEXT NOT NULL DEFAULT ''")
    except:
        pass
    for _social_col in [
        ('google_id',       "TEXT NOT NULL DEFAULT ''"),
        ('google_email',    "TEXT NOT NULL DEFAULT ''"),
        ('apple_id',        "TEXT NOT NULL DEFAULT ''"),
        ('apple_email',     "TEXT NOT NULL DEFAULT ''"),
        ('microsoft_id',    "TEXT NOT NULL DEFAULT ''"),
        ('microsoft_email', "TEXT NOT NULL DEFAULT ''"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE users ADD COLUMN {_social_col[0]} {_social_col[1]}")
        except Exception:
            pass

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS admin_accounts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL DEFAULT '',
            password_hash TEXT NOT NULL,
            is_active INTEGER NOT NULL DEFAULT 1,
            totp_secret TEXT NOT NULL DEFAULT '',
            totp_enabled INTEGER NOT NULL DEFAULT 0,
            first_name TEXT NOT NULL DEFAULT '',
            last_name TEXT NOT NULL DEFAULT '',
            profile_picture TEXT NOT NULL DEFAULT '',
            profile_description TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    for _col in [
        ('totp_secret', "TEXT NOT NULL DEFAULT ''"),
        ('totp_enabled', "INTEGER NOT NULL DEFAULT 0"),
        ('first_name', "TEXT NOT NULL DEFAULT ''"),
        ('last_name', "TEXT NOT NULL DEFAULT ''"),
        ('profile_picture', "TEXT NOT NULL DEFAULT ''"),
        ('profile_description', "TEXT NOT NULL DEFAULT ''"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE admin_accounts ADD COLUMN {_col[0]} {_col[1]}")
        except Exception:
            pass

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS reviews (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            page_id TEXT NOT NULL,
            reviewer_name TEXT NOT NULL DEFAULT '',
            rating INTEGER NOT NULL CHECK(rating >= 1 AND rating <= 5),
            review_text TEXT NOT NULL DEFAULT '',
            approved INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_reviews_page_id ON reviews(page_id)")

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS plans (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            slug TEXT NOT NULL UNIQUE,
            price_monthly REAL NOT NULL DEFAULT 0,
            max_files_per_day INTEGER NOT NULL DEFAULT 5,
            max_file_size_mb INTEGER NOT NULL DEFAULT 50,
            auto_delete_hours INTEGER NOT NULL DEFAULT 24,
            max_conversions_stored INTEGER NOT NULL DEFAULT 10,
            stripe_price_id TEXT NOT NULL DEFAULT '',
            paypal_plan_id TEXT NOT NULL DEFAULT '',
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    try:
        _db_init.execute("ALTER TABLE plans ADD COLUMN stripe_product_id TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE plans ADD COLUMN stripe_key_mode TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE plans ADD COLUMN batch_archive_download INTEGER NOT NULL DEFAULT 0")
    except Exception:
        pass
    for _col, _def in [
        ("cloud_export_google_drive", "INTEGER NOT NULL DEFAULT 0"),
        ("cloud_export_dropbox", "INTEGER NOT NULL DEFAULT 0"),
        ("cloud_export_onedrive", "INTEGER NOT NULL DEFAULT 0"),
        ("share_link_enabled", "INTEGER NOT NULL DEFAULT 0"),
        ("share_link_password", "INTEGER NOT NULL DEFAULT 0"),
        ("share_link_onetime", "INTEGER NOT NULL DEFAULT 0"),
        ("share_link_expiry", "INTEGER NOT NULL DEFAULT 0"),
        ("share_link_max_expiry_hours", "INTEGER NOT NULL DEFAULT 0"),
        ("share_link_max_per_job", "INTEGER NOT NULL DEFAULT 0"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE plans ADD COLUMN {_col} {_def}")
        except Exception:
            pass

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS shared_links (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            token TEXT NOT NULL UNIQUE,
            job_id TEXT NOT NULL,
            user_id INTEGER,
            password_hash TEXT,
            is_one_time INTEGER NOT NULL DEFAULT 0,
            is_accessed INTEGER NOT NULL DEFAULT 0,
            expires_at TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            file_urls TEXT NOT NULL DEFAULT '[]'
        )
    """)

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS job_access_grants (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            job_id TEXT NOT NULL,
            granted_to_email TEXT NOT NULL DEFAULT '',
            granted_to_user_id INTEGER,
            granted_by_user_id INTEGER,
            granted_at TEXT NOT NULL DEFAULT (datetime('now')),
            UNIQUE(job_id, granted_to_email)
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_job_access_grants_job ON job_access_grants(job_id)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_job_access_grants_email ON job_access_grants(granted_to_email)")
    try:
        _db_init.execute("ALTER TABLE job_access_grants ADD COLUMN granted_by_user_id INTEGER")
    except Exception:
        pass

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS job_feedback (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            job_id TEXT NOT NULL,
            rating INTEGER,
            comment TEXT NOT NULL DEFAULT '',
            ip_address TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            plan_id INTEGER NOT NULL DEFAULT 1,
            stripe_customer_id TEXT NOT NULL DEFAULT '',
            stripe_subscription_id TEXT NOT NULL DEFAULT '',
            paypal_subscription_id TEXT NOT NULL DEFAULT '',
            subscription_status TEXT NOT NULL DEFAULT 'free',
            subscription_expires_at TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS file_uploads (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            session_id TEXT NOT NULL DEFAULT '',
            file_path TEXT NOT NULL,
            file_name TEXT NOT NULL,
            file_size INTEGER NOT NULL DEFAULT 0,
            original_format TEXT NOT NULL DEFAULT '',
            uploaded_at TEXT NOT NULL DEFAULT (datetime('now')),
            deleted_at TEXT
        )
    """)

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS conversions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            upload_id INTEGER,
            input_format TEXT NOT NULL DEFAULT '',
            output_format TEXT NOT NULL DEFAULT '',
            job_id TEXT NOT NULL DEFAULT '',
            status TEXT NOT NULL DEFAULT 'pending',
            output_path TEXT NOT NULL DEFAULT '',
            file_name TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            completed_at TEXT
        )
    """)

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS teams (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            owner_user_id INTEGER NOT NULL,
            parent_team_id INTEGER,
            plan_id INTEGER,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_teams_owner ON teams(owner_user_id)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_teams_parent ON teams(parent_team_id)")

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS team_members (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            team_id INTEGER NOT NULL,
            user_id INTEGER,
            invited_email TEXT NOT NULL DEFAULT '',
            role TEXT NOT NULL DEFAULT 'member',
            permissions TEXT NOT NULL DEFAULT '{"view_files":true,"share":false,"download":true,"delete":false}',
            status TEXT NOT NULL DEFAULT 'pending',
            invite_token TEXT,
            invited_by INTEGER,
            invited_at TEXT NOT NULL DEFAULT (datetime('now')),
            joined_at TEXT,
            UNIQUE(team_id, invited_email)
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_team_members_team ON team_members(team_id)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_team_members_user ON team_members(user_id)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_team_members_token ON team_members(invite_token)")

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS team_messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            team_id INTEGER NOT NULL,
            user_id INTEGER,
            message TEXT NOT NULL DEFAULT '',
            message_type TEXT NOT NULL DEFAULT 'chat',
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_team_messages_team ON team_messages(team_id)")

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS team_presence (
            team_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            last_seen TEXT NOT NULL DEFAULT (datetime('now')),
            PRIMARY KEY (team_id, user_id)
        )
    """)

    for _tcol, _tdef in [
        ("max_teams", "INTEGER NOT NULL DEFAULT 0"),
        ("max_sub_teams", "INTEGER NOT NULL DEFAULT 0"),
        ("max_members_per_team", "INTEGER NOT NULL DEFAULT 0"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE plans ADD COLUMN {_tcol} {_tdef}")
        except Exception:
            pass

    try:
        _db_init.execute("ALTER TABLE teams ADD COLUMN stripe_subscription_id TEXT")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE teams ADD COLUMN paypal_subscription_id TEXT")
    except Exception:
        pass

    _team_limits_seeded = _db_init.execute(
        "SELECT 1 FROM site_settings WHERE key='team_limits_seeded_v1'"
    ).fetchone()
    if not _team_limits_seeded:
        _db_init.execute("UPDATE plans SET max_teams=0, max_sub_teams=0, max_members_per_team=0 WHERE slug='free'")
        _db_init.execute("UPDATE plans SET max_teams=1, max_sub_teams=2, max_members_per_team=5 WHERE slug='basic'")
        _db_init.execute("UPDATE plans SET max_teams=5, max_sub_teams=10, max_members_per_team=20 WHERE slug='pro'")
        _db_init.execute("INSERT INTO site_settings (key, value) VALUES ('team_limits_seeded_v1', '1')")

    anon_delete_exists = _db_init.execute(
        "SELECT 1 FROM site_settings WHERE key='anon_auto_delete_hours'"
    ).fetchone()
    if not anon_delete_exists:
        _db_init.execute(
            "INSERT INTO site_settings (key, value) VALUES ('anon_auto_delete_hours', '24')"
        )

    slug_exists = _db_init.execute(
        "SELECT 1 FROM site_settings WHERE key='admin_login_slug'"
    ).fetchone()
    if not slug_exists:
        _db_init.execute(
            "INSERT INTO site_settings (key, value) VALUES ('admin_login_slug', '')"
        )

    for _ad_key in ('ad_banner_code', 'ad_sidebar_code', 'ad_inline_code', 'ad_dashboard_code'):
        if not _db_init.execute(
            "SELECT 1 FROM site_settings WHERE key=?", (_ad_key,)
        ).fetchone():
            _db_init.execute(
                "INSERT INTO site_settings (key, value) VALUES (?, '')", (_ad_key,)
            )

    existing_plans = _db_init.execute("SELECT COUNT(*) FROM plans").fetchone()[0]
    if existing_plans == 0:
        _db_init.executemany(
            "INSERT INTO plans (name, slug, price_monthly, max_files_per_day, max_file_size_mb, auto_delete_hours, max_conversions_stored) VALUES (?,?,?,?,?,?,?)",
            [
                ("Free", "free", 0.0, 5, 50, 24, 10),
                ("Basic", "basic", 9.99, 50, 500, 72, 100),
                ("Pro", "pro", 24.99, -1, 2048, 0, -1),
            ]
        )

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS plan_billing_periods (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            plan_id INTEGER NOT NULL,
            months INTEGER NOT NULL,
            label TEXT NOT NULL,
            discount_percent REAL NOT NULL DEFAULT 0,
            stripe_price_id TEXT NOT NULL DEFAULT '',
            paypal_plan_id TEXT NOT NULL DEFAULT '',
            UNIQUE(plan_id, months),
            FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE CASCADE
        )
    """)

    _db_init.execute("DELETE FROM plan_billing_periods WHERE months IN (3, 6)")
    for _pp in _db_init.execute("SELECT id FROM plans WHERE price_monthly > 0").fetchall():
        for _pm, _pl, _pd in [(1, 'Monthly', 0), (12, 'Yearly', 20)]:
            _db_init.execute(
                "INSERT OR IGNORE INTO plan_billing_periods (plan_id, months, label, discount_percent) VALUES (?,?,?,?)",
                (_pp[0], _pm, _pl, _pd)
            )

    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN billing_period_months INTEGER NOT NULL DEFAULT 1")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN billing_amount_paid REAL NOT NULL DEFAULT 0")
    except Exception:
        pass

    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN user_totp_secret TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN user_totp_enabled INTEGER NOT NULL DEFAULT 0")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN avatar_color TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS password_reset_tokens (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            token TEXT NOT NULL UNIQUE,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            expires_at TEXT NOT NULL,
            used INTEGER NOT NULL DEFAULT 0
        )
    """)

    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN referral_code TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN referred_by_user_id INTEGER DEFAULT NULL")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN country TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_recipients ADD COLUMN email_opened_at TEXT")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_recipients ADD COLUMN push_clicked_at TEXT")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN scheduled_at TEXT")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN sent_at TEXT")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN push_title TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN push_body TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN filter_plans_json TEXT NOT NULL DEFAULT '[]'")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN filter_countries_json TEXT NOT NULL DEFAULT '[]'")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN created_by_admin_id INTEGER DEFAULT NULL")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN push_icon TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN push_image TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN push_url TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_sends ADD COLUMN anon_push_sent INTEGER NOT NULL DEFAULT 0")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_recipients ADD COLUMN email_open_count INTEGER NOT NULL DEFAULT 0")
    except Exception:
        pass
    try:
        _db_init.execute("ALTER TABLE broadcast_recipients ADD COLUMN push_click_count INTEGER NOT NULL DEFAULT 0")
    except Exception:
        pass
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS referral_rewards (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            referrer_user_id INTEGER NOT NULL,
            referred_user_id INTEGER NOT NULL,
            reward_days INTEGER NOT NULL DEFAULT 30,
            plan_name TEXT NOT NULL DEFAULT '',
            status TEXT NOT NULL DEFAULT 'pending',
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            granted_at TEXT,
            UNIQUE(referrer_user_id, referred_user_id)
        )
    """)
    for _rk, _rv in [
        ('referral_enabled', '1'),
        ('referral_reward_days', '30'),
        ('manual_payment_enabled', '1'),
        ('manual_payment_instructions', 'Please contact us to arrange manual payment via bank transfer, cash, or other methods. We will activate your account once payment is confirmed.'),
    ]:
        if not _db_init.execute("SELECT 1 FROM site_settings WHERE key=?", (_rk,)).fetchone():
            _db_init.execute("INSERT INTO site_settings (key, value) VALUES (?,?)", (_rk, _rv))

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS manual_payments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            plan_id INTEGER NOT NULL,
            amount REAL NOT NULL DEFAULT 0,
            currency TEXT NOT NULL DEFAULT 'USD',
            method TEXT NOT NULL DEFAULT 'cash',
            notes TEXT NOT NULL DEFAULT '',
            subscription_expires_at TEXT,
            recorded_by TEXT NOT NULL DEFAULT 'admin',
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)

    for _col, _def in [
        ("author_user_id", "INTEGER DEFAULT NULL"),
        ("status", "TEXT NOT NULL DEFAULT 'published'"),
        ("created_at", "TEXT DEFAULT (datetime('now'))"),
        ("updated_at", "TEXT DEFAULT (datetime('now'))"),
        ("publish_fee", "REAL NOT NULL DEFAULT 0"),
        ("payment_status", "TEXT NOT NULL DEFAULT 'free'"),
        ("admin_note", "TEXT NOT NULL DEFAULT ''"),
        ("blog_payment_session", "TEXT NOT NULL DEFAULT ''"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE blogs ADD COLUMN {_col} {_def}")
        except Exception:
            pass

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS user_notification_prefs (
            user_id INTEGER PRIMARY KEY,
            login INTEGER NOT NULL DEFAULT 1,
            file_uploaded INTEGER NOT NULL DEFAULT 0,
            blog_submitted INTEGER NOT NULL DEFAULT 1,
            blog_reviewed INTEGER NOT NULL DEFAULT 1,
            blog_published INTEGER NOT NULL DEFAULT 1,
            plan_changed INTEGER NOT NULL DEFAULT 1,
            payment_received INTEGER NOT NULL DEFAULT 1,
            referral_signup INTEGER NOT NULL DEFAULT 1,
            password_changed INTEGER NOT NULL DEFAULT 1,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS job_notify_emails (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            job_id TEXT NOT NULL,
            email TEXT NOT NULL,
            sent INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_job_notify_job ON job_notify_emails(job_id, sent)")

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS job_notify_push (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            job_id TEXT NOT NULL,
            endpoint TEXT NOT NULL,
            p256dh TEXT NOT NULL,
            auth TEXT NOT NULL,
            sent INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_job_notify_push_job ON job_notify_push(job_id, sent)")

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS broadcast_sends (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            channel TEXT NOT NULL DEFAULT '',
            filter_plans_json TEXT NOT NULL DEFAULT '[]',
            filter_countries_json TEXT NOT NULL DEFAULT '[]',
            email_subject TEXT NOT NULL DEFAULT '',
            email_body TEXT NOT NULL DEFAULT '',
            push_title TEXT NOT NULL DEFAULT '',
            push_body TEXT NOT NULL DEFAULT '',
            push_icon TEXT NOT NULL DEFAULT '',
            push_image TEXT NOT NULL DEFAULT '',
            push_url TEXT NOT NULL DEFAULT '',
            total_recipients INTEGER NOT NULL DEFAULT 0,
            emails_sent INTEGER NOT NULL DEFAULT 0,
            push_sent INTEGER NOT NULL DEFAULT 0,
            anon_push_sent INTEGER NOT NULL DEFAULT 0,
            status TEXT NOT NULL DEFAULT 'sent',
            scheduled_at TEXT,
            sent_at TEXT,
            created_by_admin_id INTEGER DEFAULT NULL
        )
    """)
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS broadcast_recipients (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            broadcast_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            email_sent INTEGER NOT NULL DEFAULT 0,
            push_sent INTEGER NOT NULL DEFAULT 0,
            email_open_count INTEGER NOT NULL DEFAULT 0,
            push_click_count INTEGER NOT NULL DEFAULT 0,
            email_opened_at TEXT,
            push_clicked_at TEXT,
            FOREIGN KEY (broadcast_id) REFERENCES broadcast_sends(id) ON DELETE CASCADE
        )
    """)

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS user_push_subscriptions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER DEFAULT NULL,
            anon_id TEXT NOT NULL DEFAULT '',
            endpoint TEXT NOT NULL,
            p256dh TEXT NOT NULL,
            auth TEXT NOT NULL,
            active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_user_push_endpoint ON user_push_subscriptions(endpoint)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_user_push_user ON user_push_subscriptions(user_id, active)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_user_push_anon ON user_push_subscriptions(anon_id, active)")

    # anon_id on conversions — links anonymous jobs to a push subscription
    try:
        _db_init.execute("ALTER TABLE conversions ADD COLUMN anon_id TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass

    try:
        _db_init.execute("ALTER TABLE plans ADD COLUMN job_notify_email INTEGER NOT NULL DEFAULT 0")
    except Exception:
        pass

    _plans_cols = [r[1] for r in _db_init.execute("PRAGMA table_info(plans)").fetchall()]
    if 'queue' not in _plans_cols:
        _db_init.execute("ALTER TABLE plans ADD COLUMN queue TEXT NOT NULL DEFAULT 'low'")

    # Ensure each default plan has the correct priority queue value.
    # The ALTER TABLE above defaults everything to 'low', so paid plans must be
    # corrected on every startup (idempotent UPDATE, no-op when already correct).
    # Match by slug (immutable identifier) instead of name (user-editable).
    for _plan_slug, _plan_queue in [("free", "low"), ("basic", "medium"), ("pro", "high")]:
        _db_init.execute(
            "UPDATE plans SET queue=? WHERE slug=?",
            (_plan_queue, _plan_slug)
        )

    _conv_cols = [r[1] for r in _db_init.execute("PRAGMA table_info(conversions)").fetchall()]
    if 'queue' not in _conv_cols:
        _db_init.execute("ALTER TABLE conversions ADD COLUMN queue TEXT NOT NULL DEFAULT 'low'")
    if 'options_json' not in _conv_cols:
        _db_init.execute("ALTER TABLE conversions ADD COLUMN options_json TEXT NOT NULL DEFAULT ''")

    for _sk, _sv in [
        ('smtp_host', ''), ('smtp_port', '587'), ('smtp_username', ''),
        ('smtp_password', ''), ('smtp_from_email', ''), ('smtp_from_name', 'OnlineConvert'),
        ('smtp_use_tls', '1'), ('subdomains_enabled', '0'),
        ('job_notify_browser_enabled', '1'),
        ('job_notify_email_enabled', '0'),
        ('job_notify_push_enabled', '0'),
        ('job_notify_from_email', ''), ('job_notify_from_name', ''),
        ('job_notify_smtp_host', ''), ('job_notify_smtp_port', '587'),
        ('job_notify_smtp_username', ''), ('job_notify_smtp_password', ''),
        ('job_notify_smtp_use_tls', '1'),
        ('vapid_private_key', ''), ('vapid_public_key', ''),
    ]:
        if not _db_init.execute("SELECT 1 FROM site_settings WHERE key=?", (_sk,)).fetchone():
            _db_init.execute("INSERT INTO site_settings (key, value) VALUES (?,?)", (_sk, _sv))

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS activity_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            event_type TEXT NOT NULL DEFAULT '',
            user_id INTEGER,
            username TEXT NOT NULL DEFAULT '',
            description TEXT NOT NULL DEFAULT '',
            meta TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_activity_log_created ON activity_log(created_at DESC)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_activity_log_type ON activity_log(event_type)")

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS deletion_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            deleted_at TEXT NOT NULL DEFAULT (datetime('now')),
            actor_user_id INTEGER,
            actor_username TEXT NOT NULL DEFAULT '',
            actor_role TEXT NOT NULL DEFAULT 'user',
            entity_type TEXT NOT NULL DEFAULT '',
            entity_id TEXT NOT NULL DEFAULT '',
            entity_name TEXT NOT NULL DEFAULT '',
            file_size_bytes INTEGER,
            team_id INTEGER,
            team_name TEXT NOT NULL DEFAULT '',
            extra_meta TEXT NOT NULL DEFAULT '{}',
            entity_owner_user_id INTEGER
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_deletion_log_deleted_at ON deletion_log(deleted_at DESC)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_deletion_log_actor ON deletion_log(actor_user_id)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_deletion_log_entity_type ON deletion_log(entity_type)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_deletion_log_owner ON deletion_log(entity_owner_user_id)")

    for _col, _def in [
        ("email_verified", "INTEGER NOT NULL DEFAULT 0"),
        ("email_verification_token", "TEXT NOT NULL DEFAULT ''"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE users ADD COLUMN {_col} {_def}")
        except Exception:
            pass

    for _col, _def in [
        ("display_name", "TEXT NOT NULL DEFAULT ''"),
        ("bio", "TEXT NOT NULL DEFAULT ''"),
        ("profile_picture", "TEXT NOT NULL DEFAULT ''"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE users ADD COLUMN {_col} {_def}")
        except Exception:
            pass

    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS user_inbox (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            event_type TEXT NOT NULL DEFAULT 'notification',
            subject TEXT NOT NULL DEFAULT '',
            body_html TEXT NOT NULL DEFAULT '',
            is_read INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_user_inbox_user ON user_inbox(user_id, is_read)")

    # ip_address tracking on conversions (for guest rate-limiting)
    try:
        _db_init.execute("ALTER TABLE conversions ADD COLUMN ip_address TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_conversions_ip_date ON conversions(ip_address, created_at)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_conversions_user_date ON conversions(user_id, created_at)")

    # stripe_product_id on plans (auto-created by stripe_checkout)
    try:
        _db_init.execute("ALTER TABLE plans ADD COLUMN stripe_product_id TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass

    # Per-tool daily limits on plans
    # ── Editors table (restricted admin accounts) ──────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS editors (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL DEFAULT '',
            allowed_languages TEXT NOT NULL DEFAULT '["en"]',
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)

    # ── Active languages table ──────────────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS active_languages (
            lang_code TEXT PRIMARY KEY,
            is_active INTEGER NOT NULL DEFAULT 1
        )
    """)
    # Seed all 13 known languages (enabled by default)
    _known_langs = ['en','zh','ar','de','es','fr','id','it','pt','ru','tr','ur','vi']
    for _lc in _known_langs:
        _db_init.execute(
            "INSERT OR IGNORE INTO active_languages (lang_code, is_active) VALUES (?, 1)",
            (_lc,)
        )

    # ── Blogs table ────────────────────────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS blogs (
            title varchar(255),
            summary VARCHAR(255),
            content TEXT,
            image varchar(255),
            slug varchar(255),
            author_user_id INTEGER DEFAULT NULL,
            status TEXT NOT NULL DEFAULT 'published',
            created_at TEXT DEFAULT (datetime('now')),
            updated_at TEXT DEFAULT (datetime('now')),
            publish_fee REAL NOT NULL DEFAULT 0,
            payment_status TEXT NOT NULL DEFAULT 'free',
            admin_note TEXT NOT NULL DEFAULT '',
            blog_payment_session TEXT NOT NULL DEFAULT ''
        )
    """)

    # ── Messages (contact form) table ───────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS messages (
            contact_type TEXT,
            name varchar(255),
            email varchar(255),
            message TEXT
        )
    """)

    for _col, _def in [
        ('max_pdf_per_day',       'INTEGER NOT NULL DEFAULT 3'),
        ('max_hash_per_day',      'INTEGER NOT NULL DEFAULT 10'),
        ('max_screenshot_per_day','INTEGER NOT NULL DEFAULT 5'),
        ('max_bg_remove_per_day', 'INTEGER NOT NULL DEFAULT 3'),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE plans ADD COLUMN {_col} {_def}")
        except Exception:
            pass

    # tool_type on conversions (converter | pdf | hash)
    try:
        _db_init.execute("ALTER TABLE conversions ADD COLUMN tool_type TEXT NOT NULL DEFAULT 'converter'")
    except Exception:
        pass

    # error_message on conversions — stores failure reason for admin display
    try:
        _db_init.execute("ALTER TABLE conversions ADD COLUMN error_message TEXT NOT NULL DEFAULT ''")
    except Exception:
        pass

    # max_processing_seconds on plans — 0 = unlimited
    try:
        _db_init.execute("ALTER TABLE plans ADD COLUMN max_processing_seconds INTEGER NOT NULL DEFAULT 0")
    except Exception:
        pass

    # max_batch_files on plans — max files per single batch job (0 = unlimited)
    try:
        _db_init.execute("ALTER TABLE plans ADD COLUMN max_batch_files INTEGER NOT NULL DEFAULT 20")
    except Exception:
        pass
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_conversions_tool ON conversions(user_id, tool_type, created_at)")

    # ─── Promo Codes ─────────────────────────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS promo_codes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code TEXT NOT NULL UNIQUE COLLATE NOCASE,
            discount_percent REAL NOT NULL DEFAULT 0,
            discount_type TEXT NOT NULL DEFAULT 'first_time',
            max_uses INTEGER NOT NULL DEFAULT 0,
            uses_count INTEGER NOT NULL DEFAULT 0,
            expires_at TEXT,
            is_active INTEGER NOT NULL DEFAULT 1,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_promo_codes_code ON promo_codes(code)")
    # Track which user used which promo code
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS user_promo_uses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            promo_code_id INTEGER NOT NULL,
            used_at TEXT NOT NULL DEFAULT (datetime('now')),
            UNIQUE(user_id, promo_code_id)
        )
    """)
    # Add promo_code_id to users
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN promo_code_id INTEGER")
    except Exception:
        pass

    # ── Auto-renewal toggle for subscriptions ───────────────────────────────
    try:
        _db_init.execute("ALTER TABLE users ADD COLUMN auto_renew INTEGER NOT NULL DEFAULT 1")
    except Exception:
        pass

    # ── Payment history (invoices) ──────────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS payment_history (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            stripe_invoice_id TEXT NOT NULL DEFAULT '',
            stripe_payment_intent TEXT NOT NULL DEFAULT '',
            amount REAL NOT NULL DEFAULT 0,
            currency TEXT NOT NULL DEFAULT 'usd',
            status TEXT NOT NULL DEFAULT 'paid',
            description TEXT NOT NULL DEFAULT '',
            invoice_pdf TEXT NOT NULL DEFAULT '',
            period_start TEXT,
            period_end TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_payment_history_user ON payment_history(user_id, created_at DESC)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_payment_history_invoice ON payment_history(stripe_invoice_id)")

    # ── Admin password reset tokens ─────────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS admin_password_reset_tokens (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            token TEXT NOT NULL UNIQUE,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            expires_at TEXT NOT NULL,
            used INTEGER NOT NULL DEFAULT 0
        )
    """)

    # ── API rate-limit + access columns on plans ────────────────────────────
    for _col, _def in [
        ("api_enabled",          "INTEGER NOT NULL DEFAULT 0"),
        ("api_calls_per_day",    "INTEGER NOT NULL DEFAULT 100"),
        ("api_calls_per_minute", "INTEGER NOT NULL DEFAULT 10"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE plans ADD COLUMN {_col} {_def}")
        except Exception:
            pass

    # Seed sensible defaults for existing plans
    # Free: API disabled; Basic: enabled 2000/day; Pro: enabled unlimited
    _db_init.execute(
        "UPDATE plans SET api_enabled=0, api_calls_per_day=100,  api_calls_per_minute=10  WHERE slug='free'"
    )
    _db_init.execute(
        "UPDATE plans SET api_enabled=1, api_calls_per_day=2000, api_calls_per_minute=60  WHERE slug='basic'"
    )
    _db_init.execute(
        "UPDATE plans SET api_enabled=1, api_calls_per_day=-1,   api_calls_per_minute=-1  WHERE slug='pro'"
    )

    # ── API usage log (for daily + per-minute rate limiting) ────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS api_usage_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            api_key_id INTEGER NOT NULL,
            endpoint TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_api_usage_user_day ON api_usage_log(user_id, created_at)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_api_usage_key_min ON api_usage_log(api_key_id, created_at)")

    # ── User API keys ───────────────────────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS user_api_keys (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            name TEXT NOT NULL DEFAULT 'My API Key',
            api_key TEXT NOT NULL UNIQUE,
            restriction_type TEXT NOT NULL DEFAULT 'all',
            allowed_ips TEXT NOT NULL DEFAULT '[]',
            allowed_domains TEXT NOT NULL DEFAULT '[]',
            is_active INTEGER NOT NULL DEFAULT 1,
            usage_count INTEGER NOT NULL DEFAULT 0,
            last_used_at TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_api_keys_key ON user_api_keys(api_key)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_api_keys_user ON user_api_keys(user_id)")

    # ── API key hash/prefix columns (added after initial schema) ─────────────
    for _col, _def in [
        ("key_hash",   "TEXT NOT NULL DEFAULT ''"),
        ("key_prefix", "TEXT NOT NULL DEFAULT ''"),
    ]:
        try:
            _db_init.execute(f"ALTER TABLE user_api_keys ADD COLUMN {_col} {_def}")
        except Exception:
            pass
    try:
        _db_init.execute("CREATE INDEX IF NOT EXISTS idx_api_keys_hash ON user_api_keys(key_hash)")
    except Exception:
        pass

    # ── User sessions (security / active sessions) ──────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS user_sessions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            session_token TEXT NOT NULL UNIQUE,
            ip_address TEXT NOT NULL DEFAULT '',
            user_agent TEXT NOT NULL DEFAULT '',
            device_type TEXT NOT NULL DEFAULT 'unknown',
            location TEXT NOT NULL DEFAULT '',
            is_current INTEGER NOT NULL DEFAULT 0,
            revoked INTEGER NOT NULL DEFAULT 0,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            last_seen_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_user_sessions_user ON user_sessions(user_id, revoked)")
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_user_sessions_token ON user_sessions(session_token)")

    # ── Security event log ──────────────────────────────────────────────────
    _db_init.execute("""
        CREATE TABLE IF NOT EXISTS security_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            event_type TEXT NOT NULL DEFAULT '',
            description TEXT NOT NULL DEFAULT '',
            ip_address TEXT NOT NULL DEFAULT '',
            user_agent TEXT NOT NULL DEFAULT '',
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )
    """)
    _db_init.execute("CREATE INDEX IF NOT EXISTS idx_security_events_user ON security_events(user_id, created_at DESC)")

    _db_init.commit()


from helpers import (
    _db, url, static_url, get_file_size, authenticate, get_text_overrides,
    get_site_settings, get_admin_profile, get_page_content, get_page_faqs,
    normalize_page_id, get_reviews, get_review_summary, get_current_user,
    get_plan_by_id, user_has_paid_plan, _user_avatar_color, _NOTIF_LABELS,
    get_today_usage, get_plan_tool_limits, TOOL_LABELS,
    get_active_language_codes, _KNOWN_LANGUAGES,
    load_server_settings
)


def _(string, **variables):
    try:
        lang = getattr(g, 'lang_code', None) or 'en'
    except RuntimeError:
        # Called outside of request context (e.g., at module import time)
        lang = 'en'
    overrides = get_text_overrides(lang)
    if string in overrides and overrides[string]:
        s = overrides[string]
        return s % variables if variables else s
    return _original_gettext(string, **variables)


_flask_babel.gettext = _


from routes.subdomain import register_subdomain_hooks
from routes.sitemap import register_sitemap_routes

# Register subdomain hooks FIRST
register_subdomain_hooks(app)

# Then register sitemap routes
register_sitemap_routes(app)



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

CONFIG_FOLDER = os.path.join(dir_path, "configs")

app.config['UPLOAD_DIR'] = os.path.join(dir_path, "static", "uploads")
app.config['BLOGS_ASSETS_DIR'] = os.path.join(dir_path, "static", "blogs")
app.config['BUCKET'] = os.getenv("aws_bucket")
app.config['LOCAL'] = False

_site = get_site_settings()
app.config['RECAPTCHA_ENABLED'] = (_site.get('recaptcha_enabled', '1') == '1')
app.config['RECAPTCHA_SITE_KEY'] = _site.get('recaptcha_site_key', '6LfbfIYlAAAAAN9SKS8JffEtG6UKFgb0nmYHa1Pd')
app.config['RECAPTCHA_SECRET_KEY'] = _site.get('recaptcha_secret_key', '6LfbfIYlAAAAAEV8rYpYBAU7coQSVtrnHO_IMJjz')

if app.config['BUCKET'] == "" or app.config['BUCKET'] is None:
    app.config['LOCAL'] = True

# Load all server-level settings from DB (MAX_CONTENT_LENGTH, SEND_FILE_MAX_AGE_DEFAULT, etc.)
load_server_settings(app)

from werkzeug.exceptions import RequestEntityTooLarge

@app.errorhandler(RequestEntityTooLarge)
def handle_413(e):
    max_bytes = app.config.get('MAX_CONTENT_LENGTH', 500 * 1024 * 1024)
    max_mb = max_bytes // (1024 * 1024)
    return jsonify(success=False, message=f"File exceeds the {max_mb} MB server limit. Please reduce the file size or contact your administrator."), 413

app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True

_css_path = os.path.join(os.path.dirname(__file__), 'static', 'css', 'tailwind.css')
app.jinja_env.globals['css_ver'] = str(int(os.path.getmtime(_css_path))) if os.path.exists(_css_path) else '1'

def get_locale():
    try:
        return "en" if not g.get('lang_code') else g.lang_code
    except Exception:
        return "en"

try:
    babel = Babel(app, locale_selector=get_locale)
except TypeError:
    babel = Babel(app)
    babel.localeselector(get_locale)

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

@app.after_request
def add_cors_for_reviews(response):
    if request.path.startswith('/api/reviews') or (len(request.path.split('/')) > 3 and '/api/reviews' in request.path):
        origin = request.headers.get('Origin', '')
        if _is_allowed_origin(origin):
            response.headers['Access-Control-Allow-Origin'] = origin
            response.headers['Access-Control-Allow-Methods'] = 'GET, POST, OPTIONS'
            response.headers['Access-Control-Allow-Headers'] = 'Content-Type, X-Requested-With'
            response.headers['Access-Control-Allow-Credentials'] = 'true'
    return response


@app.after_request
def add_static_cache_headers(response):
    if request.path.startswith('/static/'):
        response.headers['Cache-Control'] = 'no-store'
    return response


if not os.environ.get('REPLIT_DEV_DOMAIN') and app.config.get('SERVER_NAME'):
    @app.route('/static/<path:filename>', subdomain='<subdomain>')
    def subdomain_static(subdomain, filename):
        return send_from_directory(app.static_folder, filename)


def _jinja_gettext(s):
    try:
        lang = getattr(g, 'lang_code', None) or 'en'
    except RuntimeError:
        lang = 'en'
    overrides = get_text_overrides(lang)
    if s in overrides and overrides[s]:
        return overrides[s]
    return _original_gettext(s)


def _jinja_ngettext(s, p, n):
    from flask_babel import get_translations
    t = get_translations()
    return t.ungettext(s, p, n)


app.jinja_env.install_gettext_callables(_jinja_gettext,
                                        _jinja_ngettext,
                                        newstyle=True)


# Keys whose values should NOT be passed through text translation
# (technical settings, API keys, URLs, HTML blobs, etc.)
_SITE_SETTINGS_NO_TRANSLATE = {
    'site_logo', 'site_favicon', 'global_head_html', 'global_body_html',
    'recaptcha_site_key', 'recaptcha_secret_key',
    'smtp_host', 'smtp_port', 'smtp_username', 'smtp_password',
    'smtp_from_email', 'stripe_publishable_key', 'stripe_secret_key',
    'stripe_webhook_secret', 'paypal_client_id', 'paypal_client_secret',
    'head_html', 'og_image', 'logo_text_font_url', 'admin_login_slug',
    'social_bookmark_url',
    # Brand / primary color — raw CSS hex values
    'primary_color', 'primary_dark_color', 'primary_light_color',
    # Scrollbar — raw CSS values
    'scrollbar_thumb', 'scrollbar_track_dark', 'scrollbar_track_light',
    # Hero appearance — raw CSS values, never translated
    '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',
}


class _TranslatedSiteSettings(dict):
    """
    A dict wrapper for site settings that automatically passes every
    retrieved string value through the text-override / translation
    system (_jinja_gettext).  This ensures that text displayed via
    ``{{ site.get('key', 'Default') }}`` is translated exactly like
    text wrapped in ``{% trans %}`` or ``_('...')``.
    """

    def _translate(self, key, value):
        if key in _SITE_SETTINGS_NO_TRANSLATE:
            return value
        if isinstance(value, str) and value.strip():
            return _jinja_gettext(value)
        return value

    def get(self, key, default=None):
        value = super().get(key, default)
        return self._translate(key, value)

    def __getitem__(self, key):
        value = super().__getitem__(key)
        return self._translate(key, value)

minify(app=app, html=True, js=True, cssless=True)

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


from routes.general import replace_url_lang
import collections as _collections


def _get_active_supported_languages():
    """Return an OrderedDict of active languages for the template context.

    Only languages toggled ON in the active_languages table are returned.
    English ('en') is always included. The dict values match the legacy
    (display_name, active_flag) tuple format used by supported_languages.
    """
    try:
        active_codes = set(get_active_language_codes())
    except Exception:
        active_codes = set(supported_languages.keys())
    result = _collections.OrderedDict()
    for code, meta in _KNOWN_LANGUAGES.items():
        if code in active_codes:
            display = meta.get("native") or meta.get("name", code)
            result[code] = (display, True)
    # Always ensure English is present
    if "en" not in result:
        result["en"] = ("English", True)
    return result


@app.url_defaults
def add_language_code(endpoint, values):
    if 'lang_code' in values or not g.lang_code:
        return
    if app.url_map.is_endpoint_expecting(endpoint, 'lang_code'):
        values['lang_code'] = g.lang_code


@app.url_value_preprocessor
def pull_lang_code(endpoint, values):
    if values is not None and 'lang_code' in values:
        lang_code = values.pop('lang_code', None)
        
        # First, check if this is an ACTIVE (enabled) language.
        # Using get_active_language_codes() (60-second TTL cache) ensures disabled
        # languages are rejected at the URL routing level, not just in the switcher.
        try:
            from helpers import get_active_language_codes
            _active_codes = set(get_active_language_codes())
        except Exception:
            _active_codes = set(supported_languages.keys())
        if lang_code in _active_codes:
            g.lang_code = lang_code
            return
        
        # If not a language, check if it's the custom admin slug
        from helpers import get_site_settings
        settings = get_site_settings()
        custom_slug = settings.get('admin_login_slug', '').strip().strip('/')
        
        # Check if we're on a subdomain
        host = request.host.split(':')[0].lower()
        server_name = (app.config.get('SERVER_NAME') or '').lower()
        is_subdomain = server_name and host != server_name and host.endswith('.' + server_name)
        
        # If this matches the custom slug
        if custom_slug and lang_code == custom_slug:
            if is_subdomain:
                abort(404)
            else:
                # On main domain with custom slug → let before_request handle it
                g.lang_code = "en"
                return
        
        # Not a language and not the custom slug → 404
        g.lang_code = "en"
        abort(404)
    else:
        g.lang_code = "en"


@app.template_filter('hex_to_rgb')
def hex_to_rgb_filter(hex_color):
    """Convert #RRGGBB → 'R G B' for use in CSS rgb() with opacity, e.g. rgb(var(--x) / 0.5)."""
    h = (hex_color or '#3B82F6').strip().lstrip('#')
    if len(h) == 3:
        h = ''.join(c * 2 for c in h)
    if len(h) != 6:
        return '59 130 246'
    try:
        r, g, b = int(h[0:2], 16), int(h[2:4], 16), int(h[4:6], 16)
        return f'{r} {g} {b}'
    except ValueError:
        return '59 130 246'


@app.template_filter('basename')
def basename(s):
    return os.path.basename(s)


@app.template_filter('splitpart')
def splitpart(value, index, char=','):
    return value.split(char)[index]


def _get_sub_admin_profile_ctx():
    """Fetch the logged-in sub-admin row from admin_accounts for the context processor."""
    try:
        if not session.get('is_sub_admin'):
            return None
        sub_id = session.get('sub_admin_id')
        if not sub_id:
            return None
        with _db() as c:
            row = c.execute(
                "SELECT id, username, email, first_name, last_name, "
                "profile_picture, profile_description "
                "FROM admin_accounts WHERE id=? AND is_active=1",
                (sub_id,)
            ).fetchone()
        return dict(row) if row else None
    except Exception:
        return None


@app.context_processor
def functions():

    def get_definitions():
        for key in list(definitions.keys()):
            definitions[key.upper()] = definitions[key]
            definitions[key.lower()] = definitions[key]
        return definitions

    def get_definition(fmt, lang=None):
        if not fmt:
            return ''
        if lang is None:
            try:
                lang = g.lang_code
            except:
                lang = 'en'
        cms_val = get_page_content("format:" + fmt.lower(),
                                   "format_definition", lang)
        if cms_val:
            return cms_val
        defs = get_definitions()
        return defs.get(fmt, defs.get(fmt.lower(), defs.get(fmt.upper(), '')))

    def main_domain():
        # In Replit dev tunnel, use the public tunnel domain so that all links
        # in the page resolve correctly in the browser.  In production, always
        # use the domain from .env.
        replit_domain = os.environ.get('REPLIT_DEV_DOMAIN')
        if replit_domain:
            return replit_domain
        return website_url

    def get_types():
        merged = {**available_filetypes, **available_hashtypes}
        return merged

    def get_types2():
        return available_hashtypes

    def get_converters():
        return [{
            **t, "key": _
        } for _, t in available_filetypes.items() if 'category' not in t]

    def get_hash_generator():
        return [{
            **t, "key": _
        } for _, t in available_hashtypes.items() if 'category' in t]

    def get_tools(type):
        return [{
            **t, "key": _
        } for _, t in available_filetypes.items()
                if 'category' in t and _ == type]

    def ext_supported(key):
        return key in [
            ext for k, i in available_filetypes.items() for ext in i['allowed']
        ]

    def ext_color(counter):
        if counter % 5 == 0:
            return "blue"
        elif counter % 3 == 0:
            return "green"
        elif counter % 2 == 0:
            return "red"
        else:
            return "yellow"

    def lang_prefix():
        lc = getattr(g, 'lang_code', 'en')
        return '' if lc == 'en' else '/' + lc

    def full_url(path='', filetype=None, fileformat=None, input_format=None, output_format=None):

            base = main_domain()
            # Always HTTPS for Replit tunnel; HTTP only for true localhost
            scheme = 'https' if os.environ.get('REPLIT_DEV_DOMAIN') else (
                'http' if 'localhost' in request.host else 'https'
            )
            lang = lang_prefix()
            _site_settings = get_site_settings()
            # Subdomains can't work in Replit dev tunnel — always use path-based URLs
            subdomains_on = (
                False if os.environ.get('REPLIT_DEV_DOMAIN')
                else _site_settings.get('subdomains_enabled', '0') == '1'
            )

            # 1️⃣ Handle explicit filetype + fileformat (convert-to, compress, etc.)
            if filetype and fileformat:
                if subdomains_on:
                    if filetype in ("device", "webservice"):
                        path = f"/convert-for-{fileformat}"
                    elif filetype == "pdf":
                        path = f"/do/{fileformat}"
                    elif filetype == "hash":
                        path = f"/{fileformat}-generator"
                    elif filetype in ["image-compressor", "video-compressor"]:
                        path = f"/compress-{fileformat}"
                    else:
                        path = f"/convert-to-{fileformat}"
                    return f"{scheme}://{filetype}.{base}{lang}{path}"
                else:
                    if filetype == "pdf":
                        return f"{scheme}://{base}{lang}/do/{fileformat}"
                    return f"{scheme}://{base}{lang}/converter/{filetype}/{fileformat}"

            # 2️⃣ Handle format-to-format URLs (like ICO → WBMP)
            if input_format and output_format:
                if subdomains_on and filetype:
                    # e.g. image.domain.com/convert/ico-to-wbmp
                    return f"{scheme}://{filetype}.{base}{lang}/convert/{input_format}-to-{output_format}"
                else:
                    # e.g. domain.com/converter/ico-to-wbmp
                    return f"{scheme}://{base}{lang}/converter/{input_format}-to-{output_format}"

            # 3️⃣ Fallback for raw paths
            if path:
                if not path.startswith('/'):
                    path = '/' + path

                if subdomains_on:
                    # a) Special converter paths
                    if path.startswith('/converter/'):
                        parts = path.strip('/').split('/')
                        if len(parts) >= 3:
                            ft, ff = parts[1], parts[2]
                            if ft == "device":
                                path = f"/convert-for-{ff}"
                            elif ft == "pdf":
                                path = f"/do/{ff}"    
                            elif ft == "hash":
                                path = f"/{ff}-generator"
                            elif ft in ["image-compressor", "video-compressor"]:
                                path = f"/compress-{ff}"
                            else:
                                path = f"/convert-to-{ff}"
                            return f"{scheme}://{ft}.{base}{lang}{path}"

                    # b) /convert/<X>-to-<Y> — look up real filetype and route to subdomain
                    import re as _re
                    _m = _re.match(r'^/convert/([^/]+)-to-([^/]+)$', path)
                    if _m:
                        _src, _tgt = _m.group(1).lower(), _m.group(2).lower()
                        _ft = None
                        for _k, _cfg in available_filetypes.items():
                            _allowed = [f.lower() for f in _cfg.get('allowed', [])]
                            _ext = [f.lower() for f in _cfg.get('ext', [])]
                            if _src in _allowed and _tgt in _ext:
                                _ft = _k
                                break
                        if _ft:
                            return f"{scheme}://{_ft}.{base}{lang}{path}"

                    # c) Explicit filetype as subdomain
                    if filetype:
                        return f"{scheme}://{filetype}.{base}{lang}{path}"

                # Subdomain OFF → rewrite subdomain-specific patterns when filetype known
                if filetype:
                    import re as _re_ns
                    _m_ns = _re_ns.match(r'^/(convert-to-|convert-for-)(.+)$', path)
                    if _m_ns:
                        return f"{scheme}://{base}{lang}/converter/{filetype}/{_m_ns.group(2)}"
                    _m_ns2 = _re_ns.match(r'^/compress-(.+)$', path)
                    if _m_ns2:
                        return f"{scheme}://{base}{lang}/converter/{filetype}/{_m_ns2.group(1)}"
                return f"{scheme}://{base}{lang}{path}"

            # 4️⃣ Default → main domain root
            return f"{scheme}://{base}{lang}/"


    _site_settings = get_site_settings()
    _subdomains_on = _site_settings.get('subdomains_enabled', '0') == '1'

    def sub_url(sub, path=''):
        _use_subdomains = (not os.environ.get('REPLIT_DEV_DOMAIN')) and _subdomains_on
        if _use_subdomains:
            return 'https://' + sub + '.' + main_domain() + lang_prefix() + path
        return 'https://' + main_domain() + lang_prefix() + '/converter/' + sub + path

    return {
        "definitions": get_definitions,
        "main_domain": main_domain,
        "lang_prefix": lang_prefix,
        "full_url": full_url,
        "sub_url": sub_url,
        "subdomains_enabled": _subdomains_on,
        "use_subdomains": (not os.environ.get('REPLIT_DEV_DOMAIN')) and _subdomains_on,
        "available_types": get_types,
        "available_types2": get_types2,
        "selector_types": lambda: {
            k: {**v, 'ext': [f for f in v.get('ext', [])
                             if f.lower() not in {t.lower() for t in v.get('client_side_tools', [])}]}
            for k, v in get_types().items()
            if k not in ('hash', 'document-compressor', 'image-compressor', 'video-compressor')
        },
        "config": app.config,
        "url_for": url_for,
        "url": url,
        "join": os.path.join,
        "supported_languages": _get_active_supported_languages(),
        "replace_url_lang": replace_url_lang,
        "current_url": (
            'https://' + main_domain() + request.full_path.rstrip('?')
            if os.environ.get('REPLIT_DEV_DOMAIN')
            else request.url
        ),
        "dir_path": dir_path,
        "static_url": static_url,
        "get_file_size": get_file_size,
        "ext_supported": ext_supported,
        "ext_color": ext_color,
        "get_converters": get_converters,
        "get_hash_generator": get_hash_generator,
        "get_tools": get_tools,
        "seo": seo,
        "otpages": otpages,
        "content": content,
        "site": _TranslatedSiteSettings(get_site_settings()),
        "admin_profile": get_admin_profile() if (session.get('login') or session.get('editor_login')) else None,
        "sub_admin_profile": _get_sub_admin_profile_ctx(),
        "is_admin_user": bool(session.get('login')),
        "is_editor_user": bool(session.get('editor_login')),
        "editor_allowed_langs": json.loads(session.get('editor_langs', '[]')) if session.get('editor_login') else [],
        "get_page_content": get_page_content,
        "get_page_faqs": get_page_faqs,
        "normalize_page_id": normalize_page_id,
        "get_definition": get_definition,
        "get_reviews": get_reviews,
        "get_review_summary": get_review_summary
    }


@app.context_processor
def inject_current_user():
    user = get_current_user()
    if user:
        plan = get_plan_by_id(user.get('plan_id', 1))
        if plan:
            user['plan_name'] = plan['name']
    show_ads = not user_has_paid_plan(user)
    avatar_color = _user_avatar_color(user['username']) if user else '#3B82F6'
    user_initial = (user['username'][0].upper()) if user else 'U'
    is_paid = user_has_paid_plan(user)
    try:
        with _db() as c:
            pending_blogs_count = c.execute(
                "SELECT COUNT(*) FROM blogs WHERE status='pending_review'"
            ).fetchone()[0]
    except Exception:
        pending_blogs_count = 0
    inbox_unread = 0
    if user:
        try:
            with _db() as _uc:
                inbox_unread = _uc.execute(
                    "SELECT COUNT(*) FROM user_inbox WHERE user_id=? AND is_read=0", (user['id'],)
                ).fetchone()[0]
        except Exception:
            inbox_unread = 0
    # ── Nav usage summary ────────────────────────────────────────────────────
    nav_today_usage = {}
    nav_plan_limits = {}
    if user:
        try:
            from helpers import get_client_ip as _gci
            _nav_ip = _gci()
        except Exception:
            _nav_ip = ''
        try:
            nav_today_usage = get_today_usage(user_id=user.get('id'), ip_address=_nav_ip)
        except Exception:
            nav_today_usage = {}
        try:
            nav_plan_limits = get_plan_tool_limits(user.get('plan_id', 1))
        except Exception:
            nav_plan_limits = {}
    # ─────────────────────────────────────────────────────────────────────────
    _site_settings = {}
    try:
        _site_settings = get_site_settings()
    except Exception:
        pass
    return {"current_user": user, "show_ads": show_ads,
            "user_avatar_color": avatar_color, "user_initial": user_initial,
            "user_is_paid": is_paid, "pending_blogs_count": pending_blogs_count,
            "inbox_unread": inbox_unread,
            "nav_today_usage": nav_today_usage,
            "nav_plan_limits": nav_plan_limits,
            "nav_tool_labels": TOOL_LABELS,
            "site_settings": _site_settings}




from routes.admin import register_admin_routes
from routes.user import register_user_routes
from routes.converter import register_converter_routes
from routes.general import register_general_routes
from routes.currency import register_currency_routes
from routes.api import register_api_routes
from routes.developers import register_developers_routes

register_admin_routes(app)
register_user_routes(app)
register_converter_routes(app)
register_general_routes(app)
register_currency_routes(app)
register_api_routes(app)
register_developers_routes(app)
from routes.sharing import register_sharing_routes
from routes.teams import register_team_routes
register_team_routes(app)
register_sharing_routes(app)


@app.route('/sw.js')
def service_worker_js():
    resp = send_from_directory(app.static_folder, 'sw.js')
    resp.headers['Service-Worker-Allowed'] = '/'
    resp.headers['Cache-Control'] = 'no-cache'
    return resp


if not os.environ.get('REPLIT_DEV_DOMAIN') and app.config.get('SERVER_NAME'):
    @app.route('/sw.js', subdomain='<subdomain>')
    def subdomain_service_worker_js(subdomain):
        resp = send_from_directory(app.static_folder, 'sw.js')
        resp.headers['Service-Worker-Allowed'] = '/'
        resp.headers['Cache-Control'] = 'no-cache'
        return resp


def _reschedule_pending_broadcasts():
    """On startup: reschedule pending broadcast jobs or mark missed ones."""
    try:
        from helpers import _db
        from datetime import datetime, timezone
        now_utc = datetime.utcnow()
        with _db() as c:
            rows = c.execute(
                "SELECT id, scheduled_at FROM broadcast_sends WHERE status='scheduled'"
            ).fetchall()
            for row in rows:
                bid = row['id']
                sa = row['scheduled_at']
                if not sa:
                    c.execute("UPDATE broadcast_sends SET status='missed' WHERE id=?", (bid,))
                    continue
                try:
                    run_dt = datetime.fromisoformat(sa)
                except Exception:
                    c.execute("UPDATE broadcast_sends SET status='missed' WHERE id=?", (bid,))
                    continue
                if run_dt <= now_utc:
                    c.execute("UPDATE broadcast_sends SET status='missed' WHERE id=?", (bid,))
                else:
                    job_id = f'broadcast_{bid}'
                    try:
                        sched.remove_job(job_id)
                    except Exception:
                        pass
                    from routes.admin import _execute_broadcast
                    sched.add_job(
                        _execute_broadcast, trigger='date', run_date=run_dt,
                        id=job_id, args=[bid], replace_existing=True
                    )
            c.commit()
    except Exception as _exc:
        import traceback
        traceback.print_exc()


_reschedule_pending_broadcasts()




@app.after_request
def apply_brand_replacement(response):
    """Replace hardcoded brand name/domain in HTML responses using admin-configured values."""
    ct = response.content_type or ''
    if 'text/html' not in ct:
        return response
    try:
        _settings = get_site_settings()
        brand_rep = _settings.get('brand_replacement', '').strip()
        brand_domain = _settings.get('brand_domain', '').strip()
    except Exception:
        brand_rep = ''
        brand_domain = ''
    if not brand_rep:
        return response

    html = response.get_data(as_text=True)

    rep_lower = brand_rep.lower()
    rep_title = brand_rep
    rep_upper = brand_rep.upper()

    rep_domain = brand_domain if brand_domain else rep_lower + '.cc'
    rep_domain_upper = rep_domain.upper()
    rep_domain_title = '.'.join(p.capitalize() for p in rep_domain.split('.'))

    orig_domain       = 'onlineconvert.cc'
    orig_domain_title = 'OnlineConvert.cc'
    orig_domain_upper = 'ONLINECONVERT.CC'
    orig_domain_odd   = 'Onlineconvert.cc'

    orig_lower = 'onlineconvert'
    orig_title = 'OnlineConvert'
    orig_upper = 'ONLINECONVERT'

    html = html.replace(orig_domain_upper, rep_domain_upper)
    html = html.replace(orig_domain_title, rep_domain_title)
    html = html.replace(orig_domain_odd,   rep_domain_title)
    html = html.replace(orig_domain,       rep_domain)

    html = html.replace(orig_upper, rep_upper)
    html = html.replace(orig_title, rep_title)
    html = html.replace(orig_lower, rep_lower)

    response.set_data(html.encode('utf-8'))
    return response


if __name__ == "__main__":
    app.run(host='0.0.0.0', port=int(os.environ.get('PORT', 5000)), debug=True)