import json
from datetime import datetime, timedelta
from decimal import Decimal, ROUND_HALF_UP
from flask import Flask, render_template, request, redirect, url_for, session, jsonify, make_response, abort, flash
import pyodbc
import firebase_admin
from firebase_admin import credentials, auth
from functools import wraps
from pathlib import Path
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.exceptions import RequestEntityTooLarge
from PIL import Image
import os
import uuid
import re
import html
import secrets
import hmac

BASE_DIR = Path(__file__).resolve().parent
RUNTIME_ENV_FILE = BASE_DIR / ".env.runtime"

def _load_runtime_env_file(path: Path):
    if not path.exists():
        return
    try:
        for raw_line in path.read_text(encoding="utf-8").splitlines():
            line = raw_line.strip()
            if not line or line.startswith("#") or "=" not in line:
                continue
            key, value = line.split("=", 1)
            key = key.strip()
            value = value.strip().strip('"').strip("'")
            if key:
                os.environ[key] = value
    except Exception:
        pass

def _env(name: str, default=None):
    value = os.environ.get(name)
    if value is None:
        return default
    value = value.strip()
    return value if value else default

def _env_bool(name: str, default: bool = False) -> bool:
    value = os.environ.get(name)
    if value is None:
        return default
    return value.strip().lower() in {"1", "true", "yes", "on"}

def _env_int(name: str, default: int) -> int:
    value = os.environ.get(name)
    if value is None:
        return default
    try:
        return int(value.strip())
    except Exception:
        return default

_load_runtime_env_file(RUNTIME_ENV_FILE)

app = Flask(__name__)
app.secret_key = _env("SHOPPING_SECRET_KEY", "REPLACE_WITH_A_LONG_RANDOM_SECRET")
app.config["MAX_CONTENT_LENGTH"] = 2 * 1024 * 1024  # 2MB
app.config["SESSION_COOKIE_HTTPONLY"] = True
app.config["SESSION_COOKIE_SAMESITE"] = _env("SHOPPING_SESSION_SAMESITE", "Lax")
app.config["SESSION_COOKIE_SECURE"] = _env_bool("SHOPPING_SESSION_COOKIE_SECURE", True)
app.config["PERMANENT_SESSION_LIFETIME"] = timedelta(days=5)

ALLOWED_AVATAR_EXTENSIONS = {"png", "jpg", "jpeg"}
AVATAR_UPLOAD_DIR = "/var/www/shopping/uploads/avatars"
DEFAULT_AVATAR_URL = "/uploads/avatars/default-avatar.png"
AVATAR_MAX_BYTES = app.config["MAX_CONTENT_LENGTH"]
AVATAR_MAX_SIDE = 1024

DB_CONFIG = {
    "server": _env("SHOPPING_DB_SERVER", "127.0.0.1,1433"),
    "database": _env("SHOPPING_DB_NAME", "shopping_db"),
    "username": _env("SHOPPING_DB_USER", "sa"),
    "password": _env("SHOPPING_DB_PASSWORD", ""),
    "driver": _env("SHOPPING_DB_DRIVER", "ODBC Driver 18 for SQL Server")
}


# ==================================================
# 1. 站点品牌与基础配置
# ==================================================
# ==== site branding start ====
SITE_BRANDING_FILE = os.path.join(os.path.dirname(__file__), "site_branding.json")
SITE_BRANDING_UPLOAD_DIR = "/var/www/shopping/uploads/site"

DEFAULT_SITE_BRANDING = {
    "site_name": "顶真百货",
    "header_name": "顶真百货",
    "browser_title": "顶真百货",
    "favicon_url": "/uploads/avatars/default-avatar.png",
}

def load_site_branding():
    data = dict(DEFAULT_SITE_BRANDING)
    try:
        if os.path.exists(SITE_BRANDING_FILE):
            with open(SITE_BRANDING_FILE, "r", encoding="utf-8") as f:
                raw = json.load(f)
            if isinstance(raw, dict):
                for k in DEFAULT_SITE_BRANDING.keys():
                    v = raw.get(k)
                    if isinstance(v, str) and v.strip():
                        data[k] = v.strip()
    except:
        pass
    return data

def save_site_branding(data):
    merged = dict(DEFAULT_SITE_BRANDING)
    for k in DEFAULT_SITE_BRANDING.keys():
        v = data.get(k)
        if isinstance(v, str) and v.strip():
            merged[k] = v.strip()
    with open(SITE_BRANDING_FILE, "w", encoding="utf-8") as f:
        json.dump(merged, f, ensure_ascii=False, indent=2)


def is_managed_site_icon_url(file_url: str) -> bool:
    value = (file_url or "").strip()
    if not value.startswith("/uploads/site/"):
        return False
    name = os.path.basename(value).lower()
    return name.startswith("site-icon-") and name.endswith(".png")

def allowed_site_icon_file(filename):
    ext = (filename.rsplit(".", 1)[-1].lower() if "." in filename else "")
    return ext in {"png", "jpg", "jpeg", "webp"}

def save_site_icon_image(file_storage):
    if not file_storage or not file_storage.filename:
        raise ValueError("未选择网站图标文件")
    if not allowed_site_icon_file(file_storage.filename):
        raise ValueError("网站图标仅支持 png / jpg / jpeg / webp")

    os.makedirs(SITE_BRANDING_UPLOAD_DIR, exist_ok=True)

    try:
        img = Image.open(file_storage.stream)
        img.verify()
        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream).convert("RGBA")
    except Exception:
        raise ValueError("上传的站点图标不是有效图片")

    if img.width > 512 or img.height > 512:
        img.thumbnail((512, 512))

    side = max(img.width, img.height)
    canvas = Image.new("RGBA", (side, side), (255, 255, 255, 0))
    offset = ((side - img.width) // 2, (side - img.height) // 2)
    canvas.paste(img, offset)

    out_name = f"site-icon-{uuid.uuid4().hex}.png"
    out_path = os.path.join(SITE_BRANDING_UPLOAD_DIR, out_name)
    canvas.save(out_path, format="PNG")

    return f"/uploads/site/{out_name}"

@app.context_processor
def inject_site_branding():
    return {"site_branding": load_site_branding()}
# ==== site branding end ====



# ==================================================
# 2. 数据库与基础上传工具
# ==================================================
def get_conn():
    conn_str = (
        f"DRIVER={{{DB_CONFIG['driver']}}};"
        f"SERVER={DB_CONFIG['server']};"
        f"DATABASE={DB_CONFIG['database']};"
        f"UID={DB_CONFIG['username']};"
        f"PWD={DB_CONFIG['password']};"
        f"Encrypt=yes;"
        f"TrustServerCertificate=yes;"
    )
    return pyodbc.connect(conn_str)


def allowed_avatar_file(filename: str) -> bool:
    if "." not in filename:
        return False
    ext = filename.rsplit(".", 1)[1].lower()
    return ext in ALLOWED_AVATAR_EXTENSIONS


def sanitize_nickname(raw: str) -> str:
    """
    规则：
    - 去首尾空格
    - 长度 2~20
    - 只允许：中文、英文、数字、下划线
    """
    value = (raw or "").strip()
    if not value:
        return ""

    if len(value) < 2 or len(value) > 20:
        return ""

    if not re.fullmatch(r'[\u4e00-\u9fffA-Za-z0-9_]+', value):
        return ""

    return value


def is_password_hashed(value: str) -> bool:
    """
    粗略判断是否是 werkzeug 生成的 hash
    """
    if not value:
        return False
    return value.startswith("pbkdf2:") or value.startswith("scrypt:")


def verify_and_upgrade_password(cur, user_id: int, stored_password: str, input_password: str):
    """
    返回: (ok, upgraded)
    ok=True  表示密码正确
    upgraded=True 表示旧明文密码已自动升级为 hash
    """
    stored_password = stored_password or ""

    # 新版 hash
    if is_password_hashed(stored_password):
        try:
            return check_password_hash(stored_password, input_password), False
        except Exception:
            return False, False

    # 兼容旧版明文密码
    if stored_password == input_password:
        new_hash = generate_password_hash(input_password)
        cur.execute("""
            UPDATE users
            SET password_hash = ?
            WHERE user_id = ?
        """, new_hash, user_id)
        return True, True

    return False, False




def is_default_avatar_url(file_url: str) -> bool:
    value = (file_url or "").strip()
    if not value:
        return True
    if value == DEFAULT_AVATAR_URL:
        return True
    name = os.path.basename(value).lower()
    return name.startswith("default-avatar")

def delete_local_upload_file(file_url: str, upload_dir: str) -> bool:
    value = (file_url or "").strip()
    if not value or not value.startswith("/uploads/"):
        return False

    filename = os.path.basename(value)
    if not filename:
        return False

    upload_dir_real = os.path.realpath(upload_dir)
    full_path = os.path.realpath(os.path.join(upload_dir, filename))

    if not full_path.startswith(upload_dir_real + os.sep):
        return False
    if not os.path.isfile(full_path):
        return False

    try:
        os.remove(full_path)
        return True
    except Exception:
        return False

def save_avatar_file(file_storage):
    """
    返回: (ok, result)
    ok=True  -> result 是 avatar_url
    ok=False -> result 是错误消息
    """
    if not file_storage or not file_storage.filename:
        return False, "未选择头像文件"

    if not allowed_avatar_file(file_storage.filename):
        return False, "头像只允许 png、jpg、jpeg 格式"

    os.makedirs(AVATAR_UPLOAD_DIR, exist_ok=True)

    try:
        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream)
        img.verify()

        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream)

        has_alpha = "A" in img.getbands()
        img = img.convert("RGBA" if has_alpha else "RGB")

        if max(img.size) > AVATAR_MAX_SIDE:
            img.thumbnail((AVATAR_MAX_SIDE, AVATAR_MAX_SIDE))

        if has_alpha:
            ext = "png"
            new_name = f"{uuid.uuid4().hex}.{ext}"
            save_path = os.path.join(AVATAR_UPLOAD_DIR, new_name)
            img.save(save_path, format="PNG", optimize=True)
        else:
            ext = "jpg"
            new_name = f"{uuid.uuid4().hex}.{ext}"
            save_path = os.path.join(AVATAR_UPLOAD_DIR, new_name)
            img = img.convert("RGB")
            img.save(save_path, format="JPEG", quality=88, optimize=True)

    except Exception:
        return False, "头像文件无效或已损坏"

    avatar_url = f"/uploads/avatars/{new_name}"
    return True, avatar_url


# ==================================================
# 3. Firebase / 会话 / CSRF / 安全头 / 限流
# ==================================================
if not firebase_admin._apps:
    cred = credentials.Certificate(_env("SHOPPING_FIREBASE_SERVICE_ACCOUNT", "/opt/webapps/shopping_app/firebase-service-account.json"))
    firebase_admin.initialize_app(cred)

FIREBASE_WEB_CONFIG = {
    "apiKey": _env("SHOPPING_FIREBASE_API_KEY", "AIzaSyA4xaY82IbIGSHuqNjYaTvDUhoDvGd66cw"),
    "authDomain": _env("SHOPPING_FIREBASE_AUTH_DOMAIN", "shoppingvertex-c6690.firebaseapp.com"),
    "projectId": _env("SHOPPING_FIREBASE_PROJECT_ID", "shoppingvertex-c6690"),
    "appId": _env("SHOPPING_FIREBASE_APP_ID", "1:523952953149:web:b6826e654053917d051754")
}

SESSION_COOKIE_NAME = "firebase_session"
SESSION_EXPIRES_IN = 60 * 60 * 24 * 5


CSRF_FIELD_NAME = "csrf_token"
CSRF_COOKIE_NAME = "csrf_token"
CSRF_HEADER_NAMES = ("X-CSRF-Token", "X-CSRFToken")
CSRF_EXEMPT_ENDPOINTS = {
    "local_login",
    "session_login",
    "merchant_editor_upload_image",
}

_POST_FORM_RE = re.compile(
    r'(<form\b[^>]*\bmethod\s*=\s*(?:"post"|\'post\'|post)[^>]*>)',
    flags=re.IGNORECASE
)

def get_csrf_token():
    token = (request.cookies.get(CSRF_COOKIE_NAME) or "").strip()
    if token:
        return token

    token = (session.get("_csrf_token") or "").strip()
    if token:
        return token

    token = secrets.token_urlsafe(32)
    session["_csrf_token"] = token
    return token

def validate_csrf_token(submitted_token, cookie_token):
    submitted_token = (submitted_token or "").strip()
    cookie_token = (cookie_token or "").strip()
    if not submitted_token or not cookie_token:
        return False
    try:
        return hmac.compare_digest(submitted_token, cookie_token)
    except Exception:
        return False

@app.context_processor
def inject_csrf_helpers():
    def csrf_input():
        token = html.escape(get_csrf_token(), quote=True)
        return f'<input type="hidden" name="{CSRF_FIELD_NAME}" value="{token}">'
    return {
        "csrf_token": get_csrf_token(),
        "csrf_input": csrf_input,
    }

@app.before_request
def csrf_protect():
    if request.method not in {"POST", "PUT", "PATCH", "DELETE"}:
        return None

    endpoint = (request.endpoint or "").strip()
    if endpoint in CSRF_EXEMPT_ENDPOINTS:
        return None

    submitted = request.form.get(CSRF_FIELD_NAME)
    if not submitted:
        for header_name in CSRF_HEADER_NAMES:
            header_value = request.headers.get(header_name)
            if header_value:
                submitted = header_value
                break

    cookie_token = request.cookies.get(CSRF_COOKIE_NAME)
    if validate_csrf_token(submitted, cookie_token):
        return None

    abort(400, description="CSRF token 校验失败，请刷新页面后重试")

@app.after_request
def apply_security_headers(response):
    response.headers["X-Frame-Options"] = "DENY"
    response.headers["X-Content-Type-Options"] = "nosniff"
    response.headers["Referrer-Policy"] = "strict-origin-when-cross-origin"
    response.headers["Permissions-Policy"] = "geolocation=(), microphone=(), camera=(), payment=(), usb=()"
    response.headers["Cross-Origin-Resource-Policy"] = "same-origin"

    # 仅对 HTML 页面加更严格的缓存控制，避免敏感页面被浏览器长期缓存
    content_type = (response.headers.get("Content-Type") or "").lower()
    if "text/html" in content_type:
        response.headers["Cache-Control"] = "no-store, no-cache, must-revalidate, max-age=0"
        response.headers["Pragma"] = "no-cache"
        response.headers["Expires"] = "0"

    return response


@app.after_request
def csrf_set_cookie_and_inject_forms(response):
    try:
        token = get_csrf_token()

        response.set_cookie(
            CSRF_COOKIE_NAME,
            token,
            secure=_env_bool("SHOPPING_SESSION_COOKIE_SECURE", True),
            httponly=False,
            samesite=_env("SHOPPING_SESSION_SAMESITE", "Lax"),
            path="/",
        )

        content_type = (response.headers.get("Content-Type") or "").lower()
        if response.status_code >= 400 or "text/html" not in content_type:
            return response

        body = response.get_data(as_text=True)
        if "<form" not in body.lower():
            return response

        token_html = f'<input type="hidden" name="{CSRF_FIELD_NAME}" value="{html.escape(token, quote=True)}">'

        def repl(match):
            start = match.end()
            end = body.find("</form", start)
            if end == -1:
                end = min(len(body), start + 1200)
            segment = body[start:end]

            if f'name="{CSRF_FIELD_NAME}"' in segment or f"name='{CSRF_FIELD_NAME}'" in segment:
                return match.group(1)

            return match.group(1) + "\n  " + token_html

        new_body = _POST_FORM_RE.sub(repl, body)
        if new_body != body:
            response.set_data(new_body)

    except Exception:
        return response

    return response


def sync_business_user(firebase_uid: str, email: str):
    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT TOP 1 user_id, username, email, nickname, avatar_url
        FROM users
        WHERE email = ?
    """, email)
    row = cur.fetchone()

    if row:
        conn.close()
        return {
            "user_id": row.user_id,
            "username": row.username,
            "email": row.email,
            "nickname": getattr(row, "nickname", None),
            "avatar_url": getattr(row, "avatar_url", None)
        }

    base_username = email.split("@")[0]
    username = base_username
    idx = 1

    while True:
        cur.execute("""
            SELECT TOP 1 user_id
            FROM users
            WHERE username = ?
        """, username)
        exists = cur.fetchone()
        if not exists:
            break
        idx += 1
        username = f"{base_username}_{idx}"

    cur.execute("""
        INSERT INTO users (username, email, password_hash, status)
        VALUES (?, ?, ?, ?)
    """, username, email, "", 1)
    conn.commit()

    cur.execute("""
        SELECT TOP 1 user_id
        FROM users
        WHERE email = ?
        ORDER BY user_id DESC
    """, email)
    new_user = cur.fetchone()

    cur.execute("""
        SELECT TOP 1 role_id
        FROM roles
        WHERE role_name = 'customer'
    """)
    role = cur.fetchone()

    if role and new_user:
        cur.execute("""
            INSERT INTO user_roles (user_id, role_id)
            VALUES (?, ?)
        """, new_user.user_id, role.role_id)
        conn.commit()

    conn.close()
    return {
        "user_id": new_user.user_id,
        "username": username,
        "email": email,
        "nickname": None,
        "avatar_url": None
    }

def apply_firebase_login_to_session():
    session_cookie = request.cookies.get(SESSION_COOKIE_NAME)
    if not session_cookie:
        return False

    try:
        decoded_claims = auth.verify_session_cookie(session_cookie, check_revoked=True)
        firebase_uid = decoded_claims["uid"]
        user_record = auth.get_user(firebase_uid)
        if not user_record.email_verified:
            return False
        biz_user = sync_business_user(firebase_uid, user_record.email)

        conn = get_conn()
        cur = conn.cursor()
        role_flags = get_role_flags(cur, biz_user["user_id"])
        conn.close()

        session["firebase_uid"] = firebase_uid
        session["biz_user_id"] = biz_user["user_id"]
        session["biz_username"] = biz_user["username"]
        session["biz_nickname"] = biz_user.get("nickname")
        session["biz_display_name"] = biz_user.get("nickname") or biz_user["username"]
        session["biz_avatar_url"] = biz_user.get("avatar_url")
        session["biz_email"] = biz_user["email"]
        session["biz_is_admin"] = role_flags["is_admin"]
        session["biz_is_merchant"] = role_flags["is_merchant"]
        return True
    except Exception:
        return False

def get_current_business_user():
    return {
        "user_id": session.get("biz_user_id"),
        "username": session.get("biz_username"),
        "nickname": session.get("biz_nickname"),
        "display_name": session.get("biz_display_name"),
        "email": session.get("biz_email"),
        "avatar_url": session.get("biz_avatar_url"),
        "firebase_uid": session.get("firebase_uid")
    }


def get_request_client_ip():
    forwarded = (request.headers.get("X-Forwarded-For") or "").split(",")[0].strip()
    if forwarded:
        return forwarded
    real_ip = (request.headers.get("X-Real-IP") or "").strip()
    if real_ip:
        return real_ip
    return (request.remote_addr or "unknown").strip() or "unknown"

def get_rate_limit_identity():
    user_id = session.get("biz_user_id")
    if user_id:
        return f"user:{user_id}"
    return f"ip:{get_request_client_ip()}"

def consume_rate_limit(scope: str, limit_count: int, window_seconds: int):
    now = datetime.now()
    identity = get_rate_limit_identity()
    scope_key = f"{scope}|{identity}"

    conn = get_conn()
    cur = conn.cursor()

    try:
        cur.execute("""
            SELECT scope_key, hit_count, window_started_at
            FROM request_rate_limits WITH (UPDLOCK, HOLDLOCK)
            WHERE scope_key = ?
        """, scope_key)
        row = cur.fetchone()

        if not row:
            cur.execute("""
                INSERT INTO request_rate_limits
                    (scope_key, hit_count, limit_count, window_seconds, window_started_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?)
            """, scope_key, 1, limit_count, window_seconds, now, now)
            conn.commit()
            conn.close()
            return True

        started_at = row.window_started_at
        hit_count = int(row.hit_count or 0)

        if not started_at or (now - started_at).total_seconds() >= window_seconds:
            cur.execute("""
                UPDATE request_rate_limits
                SET hit_count = ?, limit_count = ?, window_seconds = ?,
                    window_started_at = ?, updated_at = ?
                WHERE scope_key = ?
            """, 1, limit_count, window_seconds, now, now, scope_key)
            conn.commit()
            conn.close()
            return True

        if hit_count >= limit_count:
            conn.close()
            return False

        cur.execute("""
            UPDATE request_rate_limits
            SET hit_count = ?, limit_count = ?, window_seconds = ?, updated_at = ?
            WHERE scope_key = ?
        """, hit_count + 1, limit_count, window_seconds, now, scope_key)
        conn.commit()
        conn.close()
        return True

    except Exception:
        conn.rollback()
        conn.close()
        return True

def rate_limit_guard(scope: str, limit_count: int, window_seconds: int, response_mode: str = "html"):
    def decorator(view_func):
        @wraps(view_func)
        def wrapper(*args, **kwargs):
            if request.method == "POST":
                ok = consume_rate_limit(scope, limit_count, window_seconds)
                if not ok:
                    message = "操作过于频繁，请稍后再试。"
                    if response_mode == "json":
                        return jsonify({"ok": False, "error": message}), 429
                    flash(message, "error")
                    return redirect(request.referrer or url_for("home"))
            return view_func(*args, **kwargs)
        return wrapper
    return decorator

def consume_custom_rate_limit(scope_key: str, limit_count: int, window_seconds: int):
    now = datetime.now()

    conn = get_conn()
    cur = conn.cursor()

    try:
        cur.execute("""
            SELECT scope_key, hit_count, window_started_at
            FROM request_rate_limits WITH (UPDLOCK, HOLDLOCK)
            WHERE scope_key = ?
        """, scope_key)
        row = cur.fetchone()

        if not row:
            cur.execute("""
                INSERT INTO request_rate_limits
                    (scope_key, hit_count, limit_count, window_seconds, window_started_at, updated_at)
                VALUES (?, ?, ?, ?, ?, ?)
            """, scope_key, 1, limit_count, window_seconds, now, now)
            conn.commit()
            conn.close()
            return True

        started_at = row.window_started_at
        hit_count = int(row.hit_count or 0)

        if not started_at or (now - started_at).total_seconds() >= window_seconds:
            cur.execute("""
                UPDATE request_rate_limits
                SET hit_count = ?, limit_count = ?, window_seconds = ?,
                    window_started_at = ?, updated_at = ?
                WHERE scope_key = ?
            """, 1, limit_count, window_seconds, now, now, scope_key)
            conn.commit()
            conn.close()
            return True

        if hit_count >= limit_count:
            conn.close()
            return False

        cur.execute("""
            UPDATE request_rate_limits
            SET hit_count = ?, limit_count = ?, window_seconds = ?, updated_at = ?
            WHERE scope_key = ?
        """, hit_count + 1, limit_count, window_seconds, now, scope_key)
        conn.commit()
        conn.close()
        return True

    except Exception:
        conn.rollback()
        conn.close()
        return True

def normalize_login_account(value, limit=120):
    return (value or "").strip().lower()[:limit]

def auth_rate_limit_key(scope: str, account: str = "", include_user: bool = False):
    parts = [scope, f"ip:{get_request_client_ip()}"]
    normalized = normalize_login_account(account)
    if normalized:
        parts.append(f"acct:{normalized}")
    if include_user and session.get("biz_user_id"):
        parts.append(f"user:{session.get('biz_user_id')}")
    return "|".join(parts)

def auth_limit_json(scope: str, limit_count: int, window_seconds: int, account: str = ""):
    ok = consume_custom_rate_limit(
        auth_rate_limit_key(scope, account=account, include_user=False),
        limit_count,
        window_seconds
    )
    if ok:
        return None
    return jsonify({"ok": False, "error": "登录尝试过于频繁，请稍后再试。"}), 429

def auth_limit_html(scope: str, limit_count: int, window_seconds: int, account: str = "", include_user: bool = False):
    ok = consume_custom_rate_limit(
        auth_rate_limit_key(scope, account=account, include_user=include_user),
        limit_count,
        window_seconds
    )
    if ok:
        return None
    flash("操作过于频繁，请稍后再试。", "error")
    return redirect(request.referrer or request.path or url_for("home"))

def login_required(view_func):
    @wraps(view_func)
    def wrapper(*args, **kwargs):
        # 先看是否已有本地业务登录态
        if session.get("biz_user_id"):
            return view_func(*args, **kwargs)

        # 再尝试 Firebase cookie
        ok = apply_firebase_login_to_session()
        if not ok:
            session.clear()
            resp = make_response(redirect(url_for("firebase_login")))
            resp.delete_cookie(SESSION_COOKIE_NAME)
            return resp
        return view_func(*args, **kwargs)
    return wrapper

def get_or_create_cart(user_id):
    conn = get_conn()
    cur = conn.cursor()

    cur.execute("SELECT cart_id FROM carts WHERE user_id = ?", user_id)
    row = cur.fetchone()

    if row:
        cart_id = row.cart_id
        conn.close()
        return cart_id

    try:
        cur.execute("INSERT INTO carts (user_id) VALUES (?)", user_id)
        conn.commit()
    except Exception:
        conn.rollback()

    cur.execute("SELECT cart_id FROM carts WHERE user_id = ?", user_id)
    row = cur.fetchone()
    conn.close()

    if not row:
        raise RuntimeError("创建购物车失败")
    return row.cart_id



def validate_cart_items_for_checkout(cur, cart_id):
    """
    校验购物车内商品是否可下单
    返回: (ok, error_message, items)
    """
    cur.execute("""
        SELECT
            ci.cart_item_id,
            ci.product_id,
            ci.quantity,
            p.product_name,
            p.price,
            p.stock,
            p.status,
            p.merchant_id,
            p.shop_id,
            CAST(p.price * ci.quantity AS DECIMAL(18,2)) AS subtotal,
            ISNULL(pm.file_url, '/uploads/products/default.png') AS media_url
        FROM cart_items ci
        JOIN products p ON ci.product_id = p.product_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media pm
            WHERE pm.product_id = p.product_id
            ORDER BY pm.sort_order, pm.media_id
        ) pm
        WHERE ci.cart_id = ?
        ORDER BY ci.cart_item_id DESC
    """, cart_id)
    items = cur.fetchall()

    if not items:
        return False, "购物车为空", []

    for item in items:
        if int(item.status) != 1:
            return False, f"商品 {item.product_name} 已下架，无法下单", items
        if int(item.quantity) <= 0:
            return False, f"商品 {item.product_name} 数量异常", items
        if int(item.stock) < int(item.quantity):
            return False, f"商品 {item.product_name} 库存不足，当前仅剩 {item.stock}", items
        if item.merchant_id is None:
            return False, f"商品 {item.product_name} 缺少商家信息，无法下单", items

    return True, None, items

def get_or_create_wallet(user_id):
    conn = get_conn()
    cur = conn.cursor()

    cur.execute("SELECT wallet_id, balance FROM wallets WHERE user_id = ?", user_id)
    row = cur.fetchone()

    if row:
        wallet_id = row.wallet_id
        conn.close()
        return wallet_id

    try:
        cur.execute("INSERT INTO wallets (user_id, balance) VALUES (?, 0)", user_id)
        conn.commit()
    except Exception:
        conn.rollback()

    cur.execute("SELECT wallet_id, balance FROM wallets WHERE user_id = ?", user_id)
    row = cur.fetchone()
    conn.close()

    if not row:
        raise RuntimeError("创建钱包失败")
    return row.wallet_id






# ==================================================
# 4. 认证与登录路由
# ==================================================
@app.route("/local-reset-password", methods=["GET", "POST"])
def local_reset_password():
    apply_firebase_login_to_session()
    user_id = session.get("biz_user_id")
    if not user_id:
        abort(404)

    conn = get_conn()
    cur = conn.cursor()
    role_flags = get_role_flags(cur, user_id)
    conn.close()

    if not role_flags["is_admin"]:
        abort(404)

    message = None
    error = None

    if request.method == "POST":
        account = (request.form.get("account") or "").strip()
        limit_resp = auth_limit_html("local_reset_password_post", 5, 300, account=account, include_user=True)
        if limit_resp:
            return limit_resp

        new_password = (request.form.get("new_password") or "").strip()
        confirm_password = (request.form.get("confirm_password") or "").strip()

        if not account or not new_password or not confirm_password:
            error = "请填写完整信息"
        elif new_password != confirm_password:
            error = "两次输入的新密码不一致"
        elif len(new_password) < 6:
            error = "新密码长度不能少于 6 位"
        else:
            conn = get_conn()
            cur = conn.cursor()

            cur.execute("""
                SELECT TOP 1 user_id, username, email
                FROM users
                WHERE username = ? OR email = ?
            """, account, account)
            row = cur.fetchone()

            if not row:
                conn.close()
                error = "账号不存在"
            else:
                pwd_hash = generate_password_hash(new_password)
                cur.execute("""
                    UPDATE users
                    SET password_hash = ?
                    WHERE user_id = ?
                """, pwd_hash, row.user_id)
                conn.commit()
                conn.close()
                message = f"本地账号 {row.username} 密码已重置成功，请返回登录页重新登录"

    return render_template("local_reset_password.html", message=message, error=error)

@app.route("/reset-password")
def reset_password():
    return render_template("reset_password.html", firebase_config=FIREBASE_WEB_CONFIG)


@app.route("/firebase-register")
def firebase_register():
    return render_template("firebase_register.html", firebase_config=FIREBASE_WEB_CONFIG)

@app.route("/firebase-login")
def firebase_login():
    return render_template("firebase_login.html", firebase_config=FIREBASE_WEB_CONFIG)

@app.route("/login")
def login_redirect():
    return redirect(url_for("firebase_login"))

@app.route("/register")
def register_redirect():
    return redirect(url_for("firebase_register"))



@app.route("/localLogin", methods=["POST"])
def local_login():
    payload = request.get_json(silent=True) or {}
    account = (payload.get("account") or "").strip()
    password = (payload.get("password") or "").strip()

    if not account or not password:
        return jsonify({"ok": False, "error": "请输入用户名/邮箱和密码"}), 400

    limit_resp = auth_limit_json("local_login_post", 8, 300, account=account)
    if limit_resp:
        return limit_resp

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT TOP 1 user_id, username, email, password_hash, nickname, avatar_url, status
        FROM users
        WHERE username = ? OR email = ?
    """, account, account)
    row = cur.fetchone()

    generic_login_error = "账号或密码错误"

    if not row:
        conn.close()
        return jsonify({"ok": False, "error": generic_login_error}), 400

    if int(row.status) != 1:
        conn.close()
        return jsonify({"ok": False, "error": generic_login_error}), 400

    ok, upgraded = verify_and_upgrade_password(cur, row.user_id, row.password_hash, password)
    if not ok:
        conn.close()
        return jsonify({"ok": False, "error": generic_login_error}), 400

    if upgraded:
        conn.commit()

    role_flags = get_role_flags(cur, row.user_id)

    session.clear()
    session["firebase_uid"] = None
    session["biz_user_id"] = row.user_id
    session["biz_username"] = row.username
    session["biz_nickname"] = getattr(row, "nickname", None)
    session["biz_display_name"] = getattr(row, "nickname", None) or row.username
    session["biz_avatar_url"] = getattr(row, "avatar_url", None)
    session["biz_email"] = row.email
    session["biz_is_admin"] = role_flags["is_admin"]
    session["biz_is_merchant"] = role_flags["is_merchant"]

    conn.close()
    return jsonify({"ok": True, "redirect": "/"})


@app.route("/sessionLogin", methods=["POST"])
def session_login():
    payload = request.get_json(silent=True) or {}
    id_token = (payload.get("idToken") or "").strip()

    limit_resp = auth_limit_json("session_login_post", 12, 300, account="")
    if limit_resp:
        return limit_resp

    if not id_token:
        return jsonify({"ok": False, "error": "登录失败，请稍后重试"}), 400

    try:
        decoded = auth.verify_id_token(id_token)
        uid = decoded["uid"]

        user_record = auth.get_user(uid)
        if not user_record.email_verified:
            return jsonify({"ok": False, "error": "邮箱尚未验证，请先去邮箱点击验证链接"}), 400

        session_cookie = auth.create_session_cookie(
            id_token,
            expires_in=SESSION_EXPIRES_IN
        )

        biz_user = sync_business_user(uid, user_record.email)

        conn = get_conn()
        cur = conn.cursor()
        role_flags = get_role_flags(cur, biz_user["user_id"])
        conn.close()

        session["firebase_uid"] = uid
        session["biz_user_id"] = biz_user["user_id"]
        session["biz_username"] = biz_user["username"]
        session["biz_nickname"] = biz_user.get("nickname")
        session["biz_display_name"] = biz_user.get("nickname") or biz_user["username"]
        session["biz_avatar_url"] = biz_user.get("avatar_url")
        session["biz_email"] = biz_user["email"]
        session["biz_is_admin"] = role_flags["is_admin"]
        session["biz_is_merchant"] = role_flags["is_merchant"]

        resp = jsonify({"ok": True, "redirect": "/"})
        resp.set_cookie(
            SESSION_COOKIE_NAME,
            session_cookie,
            max_age=SESSION_EXPIRES_IN,
            httponly=True,
            secure=True,
            samesite="Lax"
        )
        return resp

    except Exception:
        return jsonify({"ok": False, "error": "登录失败，请稍后重试"}), 400

@app.route("/logout")
def logout():
    session.clear()
    resp = make_response(redirect(url_for("firebase_login")))
    resp.delete_cookie(SESSION_COOKIE_NAME)
    return resp

@app.route("/")
def home():
    apply_firebase_login_to_session()

    conn = get_conn()
    cur = conn.cursor()

    # 热门商品：只统计已完成订单(order_status = 3)的销量，按销量降序取前 8 个
    cur.execute("""
        SELECT TOP 8
            p.product_id,
            p.product_name,
            p.price,
            p.stock,
            c.category_name,
            ISNULL(pm.file_url, '/uploads/products/default.png') AS media_url,
            ISNULL(SUM(CASE WHEN o.order_id IS NOT NULL THEN oi.quantity ELSE 0 END), 0) AS sold_count
        FROM products p
        LEFT JOIN categories c
            ON p.category_id = c.category_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media pm
            WHERE pm.product_id = p.product_id
            ORDER BY pm.sort_order, pm.media_id
        ) pm
        LEFT JOIN order_items oi
            ON oi.product_id = p.product_id
        LEFT JOIN orders o
            ON o.order_id = oi.order_id
           AND o.order_status = 3
        WHERE p.status = 1
        GROUP BY
            p.product_id,
            p.product_name,
            p.price,
            p.stock,
            c.category_name,
            pm.file_url
        ORDER BY sold_count DESC, p.product_id DESC
    """)
    featured_products = cur.fetchall()

    cur.execute("""
        SELECT TOP 6 category_id, category_name
        FROM categories
        ORDER BY category_id
    """)
    categories = cur.fetchall()

    cur.execute("""
        SELECT TOP 4
            s.shop_id,
            s.shop_name,
            s.shop_slug,
            s.logo_url,
            s.intro,
            ISNULL(SUM(CASE WHEN o.order_status = 3 THEN oi.subtotal ELSE 0 END), 0) AS gross_revenue
        FROM shops s
        LEFT JOIN merchants m
            ON m.merchant_id = s.merchant_id
        LEFT JOIN order_items oi
            ON oi.shop_id = s.shop_id
        LEFT JOIN orders o
            ON o.order_id = oi.order_id
        WHERE s.status = 1 AND ISNULL(m.status, 1) = 1
        GROUP BY
            s.shop_id,
            s.shop_name,
            s.shop_slug,
            s.logo_url,
            s.intro
        ORDER BY gross_revenue DESC, s.shop_id DESC
    """)
    recommended_shops = cur.fetchall()

    cur.execute("SELECT COUNT(*) FROM products WHERE status = 1")
    product_count = cur.fetchone()[0]

    cur.execute("SELECT COUNT(*) FROM shops")
    shop_count = cur.fetchone()[0]

    conn.close()

    return render_template(
        "home.html",
        featured_products=featured_products,
        categories=categories,
        recommended_shops=recommended_shops,
        product_count=product_count,
        shop_count=shop_count
    )



@app.route("/shops")
def shops():
    apply_firebase_login_to_session()

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT
            s.shop_id,
            s.shop_name,
            s.shop_slug,
            s.logo_url,
            s.intro,
            ISNULL(SUM(CASE WHEN o.order_status = 3 THEN oi.subtotal ELSE 0 END), 0) AS gross_revenue,
            ISNULL(SUM(CASE WHEN o.order_status = 3 THEN oi.quantity ELSE 0 END), 0) AS sold_count,
            COUNT(DISTINCT CASE WHEN o.order_status = 3 THEN o.order_id END) AS settled_order_count
        FROM shops s
        LEFT JOIN merchants m
            ON m.merchant_id = s.merchant_id
        LEFT JOIN order_items oi
            ON oi.shop_id = s.shop_id
        LEFT JOIN orders o
            ON o.order_id = oi.order_id
        WHERE s.status = 1 AND ISNULL(m.status, 1) = 1
        GROUP BY
            s.shop_id,
            s.shop_name,
            s.shop_slug,
            s.logo_url,
            s.intro
        ORDER BY gross_revenue DESC, sold_count DESC, s.shop_id DESC
    """)
    rows = cur.fetchall()
    conn.close()

    shops = []
    for r in rows:
        shops.append({
            "shop_id": r.shop_id,
            "shop_name": r.shop_name,
            "shop_slug": r.shop_slug,
            "logo_url": r.logo_url,
            "intro": _trim_search_text(r.intro or "这个店铺暂时还没有填写简介。", 100),
            "gross_revenue": float(r.gross_revenue or 0),
            "sold_count": int(r.sold_count or 0),
            "settled_order_count": int(r.settled_order_count or 0),
        })

    return render_template("shops.html", shops=shops)


# ==== search feature start ====
def _coerce_search_type(raw):
    value = (raw or "products").strip().lower()
    return value if value in {"products", "shops", "users"} else "products"


def _trim_search_text(value, limit=120):
    text = (value or "").strip()
    if len(text) <= limit:
        return text
    return text[:limit] + "..."


@app.route("/search")
def search():
    apply_firebase_login_to_session()

    q = (request.args.get("q") or "").strip()
    search_type = _coerce_search_type(request.args.get("type"))

    products = []
    shops = []
    users = []

    if q:
        like = f"%{q}%"
        prefix = f"{q}%"

        conn = get_conn()
        cur = conn.cursor()

        # 商品搜索
        cur.execute("""
            SELECT TOP 40
                p.product_id,
                p.product_name,
                p.description,
                p.price,
                p.stock,
                c.category_name,
                s.shop_name,
                s.shop_slug,
                ISNULL(pm.file_url, '/uploads/products/default.png') AS media_url
            FROM products p
            LEFT JOIN categories c
                ON p.category_id = c.category_id
            LEFT JOIN shops s
                ON p.shop_id = s.shop_id
            OUTER APPLY (
                SELECT TOP 1 file_url
                FROM product_media pm
                WHERE pm.product_id = p.product_id
                ORDER BY pm.sort_order, pm.media_id
            ) pm
            WHERE p.status = 1
              AND (
                    p.product_name LIKE ?
                 OR ISNULL(p.description, '') LIKE ?
                 OR ISNULL(c.category_name, '') LIKE ?
                 OR ISNULL(s.shop_name, '') LIKE ?
              )
            ORDER BY
                CASE WHEN p.product_name LIKE ? THEN 0 ELSE 1 END,
                p.product_id DESC
        """, like, like, like, like, prefix)
        product_rows = cur.fetchall()
        for r in product_rows:
            products.append({
                "product_id": r.product_id,
                "product_name": r.product_name,
                "intro": make_product_intro(r.description, 88),
                "price": float(r.price),
                "stock": int(r.stock),
                "category_name": r.category_name,
                "shop_name": r.shop_name,
                "shop_slug": r.shop_slug,
                "media_url": r.media_url,
            })

        # 店铺搜索
        cur.execute("""
            SELECT TOP 40
                s.shop_id,
                s.shop_name,
                s.shop_slug,
                s.logo_url,
                s.intro,
                ISNULL(SUM(CASE WHEN o.order_status = 3 THEN oi.subtotal ELSE 0 END), 0) AS gross_revenue,
                ISNULL(SUM(CASE WHEN o.order_status = 3 THEN oi.quantity ELSE 0 END), 0) AS sold_count
            FROM shops s
            LEFT JOIN merchants m
                ON m.merchant_id = s.merchant_id
            LEFT JOIN order_items oi
                ON oi.shop_id = s.shop_id
            LEFT JOIN orders o
                ON o.order_id = oi.order_id
            WHERE s.status = 1
              AND ISNULL(m.status, 1) = 1
              AND (
                    s.shop_name LIKE ?
                 OR ISNULL(s.intro, '') LIKE ?
                 OR ISNULL(s.announcement, '') LIKE ?
              )
            GROUP BY
                s.shop_id,
                s.shop_name,
                s.shop_slug,
                s.logo_url,
                s.intro
            ORDER BY
                CASE WHEN s.shop_name LIKE ? THEN 0 ELSE 1 END,
                gross_revenue DESC,
                sold_count DESC,
                s.shop_id DESC
        """, like, like, like, prefix)
        shop_rows = cur.fetchall()
        for r in shop_rows:
            shops.append({
                "shop_id": r.shop_id,
                "shop_name": r.shop_name,
                "shop_slug": r.shop_slug,
                "logo_url": r.logo_url,
                "intro": _trim_search_text(r.intro or "这个店铺暂时还没有填写简介。", 100),
                "gross_revenue": float(r.gross_revenue or 0),
                "sold_count": int(r.sold_count or 0),
            })

        # 用户搜索（不包含管理员）
        cur.execute("""
            SELECT TOP 40
                u.user_id,
                COALESCE(NULLIF(u.nickname, ''), NULLIF(u.username, ''), CONCAT(N'用户', CONVERT(nvarchar(20), u.user_id))) AS display_name,
                u.nickname,
                u.username,
                u.avatar_url,
                CASE WHEN merchant_role.user_id IS NOT NULL THEN 1 ELSE 0 END AS is_merchant,
                s.shop_slug,
                s.shop_name
            FROM users u
            LEFT JOIN (
                SELECT DISTINCT ur.user_id
                FROM user_roles ur
                JOIN roles r
                    ON r.role_id = ur.role_id
                WHERE r.role_name = 'merchant'
            ) merchant_role
                ON merchant_role.user_id = u.user_id
            LEFT JOIN merchants m
                ON m.user_id = u.user_id
               AND ISNULL(m.status, 1) = 1
            LEFT JOIN shops s
                ON s.merchant_id = m.merchant_id
               AND s.status = 1
            WHERE ISNULL(u.status, 1) = 1
              AND NOT EXISTS (
                    SELECT 1
                    FROM user_roles urx
                    JOIN roles rx
                        ON rx.role_id = urx.role_id
                    WHERE urx.user_id = u.user_id
                      AND rx.role_name = 'admin'
              )
              AND (
                    ISNULL(u.nickname, '') LIKE ?
                 OR ISNULL(u.username, '') LIKE ?
                 OR ISNULL(m.shop_name, '') LIKE ?
              )
            ORDER BY
                CASE
                    WHEN COALESCE(NULLIF(u.nickname, ''), NULLIF(u.username, '')) LIKE ? THEN 0
                    ELSE 1
                END,
                u.user_id DESC
        """, like, like, like, prefix)
        user_rows = cur.fetchall()
        for r in user_rows:
            users.append({
                "user_id": r.user_id,
                "display_name": r.display_name,
                "avatar_url": r.avatar_url,
                "is_merchant": bool(r.is_merchant),
                "shop_slug": r.shop_slug,
                "shop_name": r.shop_name,
            })

        conn.close()

    counts = {
        "products": len(products),
        "shops": len(shops),
        "users": len(users),
    }

    return render_template(
        "search.html",
        q=q,
        search_type=search_type,
        products=products,
        shops=shops,
        users=users,
        counts=counts,
    )
# ==== search feature end ====




def make_product_intro(raw_html, limit=88):
    plain = rich_html_to_plain_text(raw_html)
    if len(plain) <= limit:
        return plain
    return plain[:limit] + "..."



# ==================================================
# 5. 前台商城、订单状态机与结算核心
# ==================================================
ORDER_STATUS_PENDING_PAYMENT = 0
ORDER_STATUS_PAID_PENDING_SHIPMENT = 1
ORDER_STATUS_SHIPPED = 2
ORDER_STATUS_COMPLETED = 3
ORDER_STATUS_CANCELLED = 4

SHIPMENT_STATUS_PENDING = 0
SHIPMENT_STATUS_SHIPPED = 1
SHIPMENT_STATUS_RECEIVED = 2

PAY_TIMEOUT_MINUTES = 10
SHIP_TIMEOUT_HOURS = 24
AUTO_DELIVER_MINUTES = 10
AUTO_RECEIVE_DAYS = 7


MONEY_QUANT = Decimal("0.01")

def money(value):
    if isinstance(value, Decimal):
        d = value
    else:
        d = Decimal(str(value if value is not None else 0))
    return d.quantize(MONEY_QUANT, rounding=ROUND_HALF_UP)



def format_countdown(target_dt):
    if not target_dt:
        return None
    now = datetime.now()
    seconds = int((target_dt - now).total_seconds())
    if seconds < 0:
        seconds = 0
    days, rem = divmod(seconds, 86400)
    hours, rem = divmod(rem, 3600)
    minutes, sec = divmod(rem, 60)
    if days > 0:
        return f"{days}天 {hours:02d}:{minutes:02d}:{sec:02d}"
    return f"{hours:02d}:{minutes:02d}:{sec:02d}"


def get_order_status_text(order_status, shipment_status=None, shipped_at=None):
    now = datetime.now()

    if int(order_status) == ORDER_STATUS_PENDING_PAYMENT:
        return "待支付"

    if int(order_status) == ORDER_STATUS_PAID_PENDING_SHIPMENT:
        return "已支付，待发货"

    if int(order_status) == ORDER_STATUS_SHIPPED:
        if shipped_at:
            delivery_time = shipped_at + timedelta(minutes=AUTO_DELIVER_MINUTES)
            if now < delivery_time:
                return "已发货，运输中"
            return "已送达，待收货"
        return "已发货"

    if int(order_status) == ORDER_STATUS_COMPLETED:
        return "已完成"

    if int(order_status) == ORDER_STATUS_CANCELLED:
        return "已取消"

    return f"状态 {order_status}"


def ensure_wallet(cur, user_id):
    cur.execute("""
        SELECT wallet_id, balance
        FROM wallets
        WHERE user_id = ?
    """, user_id)
    row = cur.fetchone()

    if row:
        return row

    try:
        cur.execute("""
            INSERT INTO wallets (user_id, balance)
            VALUES (?, 0)
        """, user_id)
    except Exception:
        pass

    cur.execute("""
        SELECT wallet_id, balance
        FROM wallets
        WHERE user_id = ?
    """, user_id)
    row = cur.fetchone()

    if not row:
        raise RuntimeError("创建钱包失败")

    return row


def settle_order_to_merchants(cur, order_id, operator_user_id, remark):
    cur.execute("""
        SELECT order_id, order_status
        FROM orders
        WHERE order_id = ?
    """, order_id)
    order = cur.fetchone()
    if not order or int(order.order_status) != ORDER_STATUS_SHIPPED:
        return False

    cur.execute("""
        SELECT TOP 1 shipment_id, status
        FROM shipments
        WHERE order_id = ?
        ORDER BY shipment_id DESC
    """, order_id)
    shipment = cur.fetchone()
    if not shipment or int(shipment.status) != SHIPMENT_STATUS_SHIPPED:
        return False

    cur.execute("""
        SELECT merchant_id, SUM(subtotal) AS merchant_amount
        FROM order_items
        WHERE order_id = ?
        GROUP BY merchant_id
    """, order_id)
    merchant_rows = cur.fetchall()

    for row in merchant_rows:
        cur.execute("""
            SELECT TOP 1 user_id
            FROM merchants
            WHERE merchant_id = ?
        """, row.merchant_id)
        merchant_user = cur.fetchone()
        if not merchant_user:
            continue

        wallet = ensure_wallet(cur, merchant_user.user_id)
        new_balance = money(wallet.balance) + money(row.merchant_amount)

        cur.execute("""
            UPDATE wallets
            SET balance = ?, updated_at = SYSDATETIME()
            WHERE wallet_id = ?
        """, new_balance, wallet.wallet_id)

        cur.execute("""
            INSERT INTO wallet_transactions
                (wallet_id, tx_type, amount, balance_after, related_order_id, remark)
            VALUES (?, ?, ?, ?, ?, ?)
        """, wallet.wallet_id, 3, row.merchant_amount, new_balance, order_id, remark)

    cur.execute("""
        UPDATE shipments
        SET status = ?, received_at = SYSDATETIME()
        WHERE order_id = ?
    """, SHIPMENT_STATUS_RECEIVED, order_id)

    cur.execute("""
        UPDATE orders
        SET order_status = ?
        WHERE order_id = ?
    """, ORDER_STATUS_COMPLETED, order_id)

    try:
        cur.execute("""
            INSERT INTO order_status_logs
                (order_id, old_status, new_status, operator_user_id, remark)
            VALUES (?, ?, ?, ?, ?)
        """, order_id, ORDER_STATUS_SHIPPED, ORDER_STATUS_COMPLETED, operator_user_id, remark)
    except:
        pass

    return True


def cancel_order(cur, order_id, user_id, old_status, refund_user=False, restore_stock=False, remark='订单已取消'):
    cur.execute("""
        SELECT order_id, user_id, total_amount, order_status
        FROM orders
        WHERE order_id = ? AND user_id = ?
    """, order_id, user_id)
    order = cur.fetchone()

    if not order or int(order.order_status) != int(old_status):
        return False

    if restore_stock:
        cur.execute("""
            SELECT product_id, quantity
            FROM order_items
            WHERE order_id = ?
        """, order_id)
        items = cur.fetchall()
        for item in items:
            cur.execute("""
                UPDATE products
                SET stock = stock + ?
                WHERE product_id = ?
            """, item.quantity, item.product_id)

    if refund_user:
        wallet = ensure_wallet(cur, user_id)
        new_balance = money(wallet.balance) + money(order.total_amount)

        cur.execute("""
            UPDATE wallets
            SET balance = ?, updated_at = SYSDATETIME()
            WHERE wallet_id = ?
        """, new_balance, wallet.wallet_id)

        cur.execute("""
            INSERT INTO wallet_transactions
                (wallet_id, tx_type, amount, balance_after, related_order_id, remark)
            VALUES (?, ?, ?, ?, ?, ?)
        """, wallet.wallet_id, 1, order.total_amount, new_balance, order_id, remark)

    cur.execute("""
        UPDATE orders
        SET order_status = ?
        WHERE order_id = ?
    """, ORDER_STATUS_CANCELLED, order_id)

    try:
        cur.execute("""
            INSERT INTO order_status_logs
                (order_id, old_status, new_status, operator_user_id, remark)
            VALUES (?, ?, ?, ?, ?)
        """, order_id, old_status, ORDER_STATUS_CANCELLED, user_id, remark)
    except:
        pass

    return True


def apply_order_timeouts(cur, user_id=None):
    sql = """
        SELECT
            o.order_id,
            o.user_id,
            o.total_amount,
            o.order_status,
            o.created_at,
            o.paid_at,
            s.shipment_id,
            s.status AS shipment_status,
            s.shipped_at,
            s.received_at
        FROM orders o
        LEFT JOIN shipments s ON s.order_id = o.order_id
        WHERE o.order_status IN (?, ?, ?)
    """
    params = [ORDER_STATUS_PENDING_PAYMENT, ORDER_STATUS_PAID_PENDING_SHIPMENT, ORDER_STATUS_SHIPPED]

    if user_id is not None:
        sql += " AND o.user_id = ?"
        params.append(user_id)

    sql += " ORDER BY o.order_id ASC"

    cur.execute(sql, params)
    rows = cur.fetchall()

    changed = False
    now = datetime.now()

    for row in rows:
        order_id = row.order_id
        uid = row.user_id
        order_status = int(row.order_status)
        created_at = row.created_at
        paid_at = row.paid_at
        shipment_status = getattr(row, "shipment_status", None)
        shipped_at = getattr(row, "shipped_at", None)

        if order_status == ORDER_STATUS_PENDING_PAYMENT and created_at:
            deadline = created_at + timedelta(minutes=PAY_TIMEOUT_MINUTES)
            if now >= deadline:
                ok = cancel_order(
                    cur,
                    order_id,
                    uid,
                    ORDER_STATUS_PENDING_PAYMENT,
                    refund_user=False,
                    restore_stock=False,
                    remark='订单超时未支付，系统自动取消'
                )
                changed = changed or ok
                continue

        if order_status == ORDER_STATUS_PAID_PENDING_SHIPMENT and paid_at:
            deadline = paid_at + timedelta(hours=SHIP_TIMEOUT_HOURS)
            if now >= deadline and (shipment_status is None or int(shipment_status) == SHIPMENT_STATUS_PENDING or shipped_at is None):
                ok = cancel_order(
                    cur,
                    order_id,
                    uid,
                    ORDER_STATUS_PAID_PENDING_SHIPMENT,
                    refund_user=True,
                    restore_stock=True,
                    remark='商家超时未发货，系统自动取消并退款'
                )
                changed = changed or ok
                continue

        if order_status == ORDER_STATUS_SHIPPED and shipped_at:
            delivery_time = shipped_at + timedelta(minutes=AUTO_DELIVER_MINUTES)
            auto_receive_time = delivery_time + timedelta(days=AUTO_RECEIVE_DAYS)
            if now >= auto_receive_time:
                ok = settle_order_to_merchants(
                    cur,
                    order_id,
                    uid,
                    '系统自动签收，订单完成结算'
                )
                changed = changed or ok

    if changed:
        cur.connection.commit()


def build_order_view(row):
    now = datetime.now()
    order_status = int(row.order_status)
    created_at = row.created_at
    paid_at = getattr(row, "paid_at", None)
    shipment_status = getattr(row, "shipment_status", None)
    shipped_at = getattr(row, "shipped_at", None)
    received_at = getattr(row, "received_at", None)

    view = {
        "order_id": row.order_id,
        "total_amount": float(row.total_amount),
        "order_status": order_status,
        "created_at": created_at,
        "paid_at": paid_at,
        "item_count": getattr(row, "item_count", None),
        "shipment_status": shipment_status,
        "shipped_at": shipped_at,
        "received_at": received_at,
        "status_text": get_order_status_text(order_status, shipment_status, shipped_at),
        "countdown_label": None,
        "countdown_text": None,
        "countdown_deadline_iso": None,
        "delivery_time": None,
        "auto_receive_time": None,
        "can_confirm_receipt": False,
    }

    if order_status == ORDER_STATUS_PENDING_PAYMENT and created_at:
        deadline = created_at + timedelta(minutes=PAY_TIMEOUT_MINUTES)
        view["countdown_label"] = "支付剩余时间"
        view["countdown_text"] = format_countdown(deadline)
        view["countdown_deadline_iso"] = deadline.isoformat()

    elif order_status == ORDER_STATUS_PAID_PENDING_SHIPMENT and paid_at:
        deadline = paid_at + timedelta(hours=SHIP_TIMEOUT_HOURS)
        view["countdown_label"] = "发货剩余时间"
        view["countdown_text"] = format_countdown(deadline)
        view["countdown_deadline_iso"] = deadline.isoformat()

    elif order_status == ORDER_STATUS_SHIPPED and shipped_at:
        delivery_time = shipped_at + timedelta(minutes=AUTO_DELIVER_MINUTES)
        view["delivery_time"] = delivery_time
        if now >= delivery_time:
            auto_receive_time = delivery_time + timedelta(days=AUTO_RECEIVE_DAYS)
            view["auto_receive_time"] = auto_receive_time
            view["countdown_label"] = "自动签收剩余时间"
            view["countdown_text"] = format_countdown(auto_receive_time)
            view["countdown_deadline_iso"] = auto_receive_time.isoformat()
            view["can_confirm_receipt"] = True

    return view

@app.route("/products")
def products():
    apply_firebase_login_to_session()

    selected_category_id = request.args.get("category_id", type=int)

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT category_id, category_name
        FROM categories
        ORDER BY category_id
    """)
    category_rows = cur.fetchall()

    sql = """
        SELECT
            p.product_id,
            p.product_name,
            p.description,
            p.price,
            p.stock,
            p.category_id,
            c.category_name,
            s.shop_name,
            s.shop_slug,
            ISNULL(pm.file_url, '/uploads/products/default.png') AS media_url
        FROM products p
        LEFT JOIN categories c
            ON p.category_id = c.category_id
        LEFT JOIN shops s
            ON p.shop_id = s.shop_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media pm
            WHERE pm.product_id = p.product_id
            ORDER BY pm.sort_order, pm.media_id
        ) pm
        WHERE p.status = 1
    """
    params = []

    if selected_category_id:
        sql += " AND p.category_id = ?"
        params.append(selected_category_id)

    sql += " ORDER BY p.product_id DESC"

    cur.execute(sql, params)
    rows = cur.fetchall()
    conn.close()

    categories = []
    for c in category_rows:
        categories.append({
            "category_id": c.category_id,
            "category_name": c.category_name,
        })

    products = []
    for r in rows:
        products.append({
            "product_id": r.product_id,
            "product_name": r.product_name,
            "description": r.description,
            "intro": make_product_intro(r.description, 88),
            "price": float(r.price),
            "stock": int(r.stock),
            "category_id": r.category_id,
            "category_name": r.category_name,
            "shop_name": r.shop_name,
            "shop_slug": r.shop_slug,
            "media_url": r.media_url,
        })

    return render_template(
        "products.html",
        products=products,
        categories=categories,
        selected_category_id=selected_category_id
    )

@app.route("/products/<int:product_id>")
def product_detail(product_id):
    apply_firebase_login_to_session()

    conn = get_conn()
    cur = conn.cursor()
    cur.execute("""
        SELECT TOP 1
            p.product_id,
            p.product_name,
            p.description,
            p.price,
            p.stock,
            c.category_name,
            s.shop_name,
            s.shop_slug,
            ISNULL(pm.file_url, '/uploads/products/default.png') AS media_url
        FROM products p
        LEFT JOIN categories c ON p.category_id = c.category_id
        LEFT JOIN shops s ON p.shop_id = s.shop_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media pm
            WHERE pm.product_id = p.product_id
            ORDER BY pm.sort_order, pm.media_id
        ) pm
        WHERE p.product_id = ? AND p.status = 1
    """, product_id)
    product = cur.fetchone()
    conn.close()

    if not product:
        abort(404)

    description_html = sanitize_rich_html(getattr(product, "description", None))
    intro_plain = make_product_intro(description_html, 110)

    return render_template(
        "product_detail.html",
        product=product,
        description_html=description_html,
        intro_plain=intro_plain,
        cart_msg=request.args.get("cart_msg")
    )

@app.route("/cart")
@login_required
def cart():
    user_id = session["biz_user_id"]
    cart_id = get_or_create_cart(user_id)

    conn = get_conn()
    cur = conn.cursor()
    cur.execute("""
        SELECT
            ci.cart_item_id,
            ci.quantity,
            p.product_id,
            p.product_name,
            p.price,
            p.stock,
            ISNULL(pm.file_url, '/uploads/products/default.png') AS media_url,
            CAST(p.price * ci.quantity AS DECIMAL(18,2)) AS subtotal
        FROM cart_items ci
        JOIN products p ON ci.product_id = p.product_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media pm
            WHERE pm.product_id = p.product_id
            ORDER BY pm.sort_order, pm.media_id
        ) pm
        WHERE ci.cart_id = ?
        ORDER BY ci.cart_item_id DESC
    """, cart_id)
    items = cur.fetchall()

    total_amount = 0
    total_quantity = 0
    for item in items:
        total_amount += float(item.subtotal)
        total_quantity += int(item.quantity)

    conn.close()

    return render_template(
        "cart.html",
        items=items,
        total_amount=total_amount,
        total_quantity=total_quantity
    )

@app.route("/cart/add/<int:product_id>", methods=["POST"])
@login_required
def cart_add(product_id):
    user_id = session["biz_user_id"]
    next_url = request.form.get("next") or request.referrer or url_for("products")
    cart_id = get_or_create_cart(user_id)

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT product_id, stock, status
        FROM products
        WHERE product_id = ?
    """, product_id)
    product = cur.fetchone()

    if not product or product.status != 1:
        conn.close()
        sep = "&" if "?" in next_url else "?"
        return redirect(f"{next_url}{sep}cart_msg=商品不可加入购物车")

    cur.execute("""
        SELECT cart_item_id, quantity
        FROM cart_items
        WHERE cart_id = ? AND product_id = ?
    """, cart_id, product_id)
    row = cur.fetchone()

    if row:
        new_qty = row.quantity + 1
        if new_qty > product.stock:
            conn.close()
            sep = "&" if "?" in next_url else "?"
            return redirect(f"{next_url}{sep}cart_msg=库存不足，无法继续增加")
        cur.execute("""
            UPDATE cart_items
            SET quantity = ?
            WHERE cart_item_id = ?
        """, new_qty, row.cart_item_id)
    else:
        if product.stock >= 1:
            cur.execute("""
                INSERT INTO cart_items (cart_id, product_id, quantity)
                VALUES (?, ?, 1)
            """, cart_id, product_id)
        else:
            conn.close()
            sep = "&" if "?" in next_url else "?"
            return redirect(f"{next_url}{sep}cart_msg=商品库存不足")

    conn.commit()
    conn.close()

    sep = "&" if "?" in next_url else "?"
    return redirect(f"{next_url}{sep}cart_msg=已加入购物车")

@app.route("/cart/update/<int:cart_item_id>", methods=["POST"])
@login_required
def cart_update(cart_item_id):
    user_id = session["biz_user_id"]
    qty = request.form.get("quantity", "1").strip()
    try:
        qty = int(qty)
    except:
        qty = 1
    if qty < 1:
        qty = 1

    conn = get_conn()
    cur = conn.cursor()
    cur.execute("""
        SELECT ci.cart_item_id, p.stock
        FROM cart_items ci
        JOIN carts c ON ci.cart_id = c.cart_id
        JOIN products p ON ci.product_id = p.product_id
        WHERE ci.cart_item_id = ? AND c.user_id = ?
    """, cart_item_id, user_id)
    row = cur.fetchone()

    if row:
        if qty > row.stock:
            qty = row.stock
        cur.execute("""
            UPDATE cart_items
            SET quantity = ?
            WHERE cart_item_id = ?
        """, qty, cart_item_id)
        conn.commit()

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

@app.route("/cart/remove/<int:cart_item_id>", methods=["POST"])
@login_required
def cart_remove(cart_item_id):
    user_id = session["biz_user_id"]
    conn = get_conn()
    cur = conn.cursor()
    cur.execute("""
        DELETE ci
        FROM cart_items ci
        JOIN carts c ON ci.cart_id = c.cart_id
        WHERE ci.cart_item_id = ? AND c.user_id = ?
    """, cart_item_id, user_id)
    conn.commit()
    conn.close()
    return redirect(url_for("cart"))

def _split_checkout_items_by_shop(items):
    grouped = {}
    for item in items:
        key = (int(item.merchant_id), int(item.shop_id))
        grouped.setdefault(key, []).append(item)
    return list(grouped.values())


def _create_split_orders(cur, user_id, addr, grouped_items, shipping_fee):
    created_order_ids = []

    for idx, group in enumerate(grouped_items):
        group_goods_total = sum((money(item.subtotal) for item in group), Decimal("0.00"))
        group_shipping_fee = money(shipping_fee) if idx == 0 else Decimal("0.00")
        group_payable_amount = group_goods_total + group_shipping_fee

        cur.execute("""
            INSERT INTO orders (user_id, total_amount, order_status)
            OUTPUT INSERTED.order_id
            VALUES (?, ?, ?)
        """, user_id, group_payable_amount, ORDER_STATUS_PENDING_PAYMENT)
        order_id = cur.fetchone()[0]

        for item in group:
            cur.execute("""
                INSERT INTO order_items
                    (order_id, product_id, merchant_id, quantity, price, subtotal, shop_id)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            """, order_id, item.product_id, item.merchant_id, item.quantity, item.price, item.subtotal, item.shop_id)

        cur.execute("""
            INSERT INTO order_addresses
                (order_id, receiver_name, receiver_phone, province, city, district, detail_address, postal_code)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        """, order_id, addr.receiver_name, addr.receiver_phone, addr.province, addr.city,
             addr.district, addr.detail_address, addr.postal_code)

        try:
            cur.execute("""
                INSERT INTO order_status_logs
                    (order_id, old_status, new_status, operator_user_id, remark)
                VALUES (?, ?, ?, ?, ?)
            """, order_id, None, ORDER_STATUS_PENDING_PAYMENT, user_id, '用户提交订单，等待支付')
        except:
            pass

        created_order_ids.append(order_id)

    return created_order_ids


@app.route("/checkout", methods=["GET", "POST"])
@login_required
@rate_limit_guard("checkout_post", 6, 60, "html")
def checkout():
    user_id = session["biz_user_id"]
    cart_id = get_or_create_cart(user_id)

    conn = get_conn()
    cur = conn.cursor()

    ok, stock_error, items = validate_cart_items_for_checkout(cur, cart_id)

    if not items:
        conn.close()
        return redirect(url_for("cart"))

    if not ok and request.method == "GET":
        conn.close()
        return redirect(url_for("cart", cart_msg=stock_error))

    # 全部地址
    cur.execute("""
        SELECT
            address_id, receiver_name, receiver_phone,
            province, city, district, detail_address, postal_code,
            is_default
        FROM user_addresses
        WHERE user_id = ?
        ORDER BY is_default DESC, address_id DESC
    """, user_id)
    addresses = cur.fetchall()

    default_address = addresses[0] if addresses else None

    total_amount = 0
    total_quantity = 0
    for item in items:
        total_amount += float(item.subtotal)
        total_quantity += int(item.quantity)

    # 运费逻辑：先简单做成满99包邮，否则8元
    shipping_fee = 0 if total_amount >= 99 else 8
    payable_amount = total_amount + shipping_fee

    if request.method == "POST":
        address_id = request.form.get("address_id", "").strip()

        if not address_id:
            conn.close()
            return render_template(
                "checkout.html",
                items=items,
                addresses=addresses,
                default_address=default_address,
                total_amount=total_amount,
                total_quantity=total_quantity,
                shipping_fee=shipping_fee,
                payable_amount=payable_amount,
                error="请先选择收货地址"
            )

        # 取地址快照
        cur.execute("""
            SELECT TOP 1
                address_id, receiver_name, receiver_phone,
                province, city, district, detail_address, postal_code
            FROM user_addresses
            WHERE address_id = ? AND user_id = ?
        """, address_id, user_id)
        addr = cur.fetchone()

        if not addr:
            conn.close()
            return render_template(
                "checkout.html",
                items=items,
                addresses=addresses,
                default_address=default_address,
                total_amount=total_amount,
                total_quantity=total_quantity,
                shipping_fee=shipping_fee,
                payable_amount=payable_amount,
                error="收货地址不存在"
            )

        # 提交订单前再次校验库存与商品状态
        ok, stock_error, items = validate_cart_items_for_checkout(cur, cart_id)
        if not ok:
            conn.close()
            return render_template(
                "checkout.html",
                items=items,
                addresses=addresses,
                default_address=default_address,
                total_amount=total_amount,
                total_quantity=total_quantity,
                shipping_fee=shipping_fee,
                payable_amount=payable_amount,
                error=stock_error
            )

        # 再检查库存
        for item in items:
            if item.quantity > item.stock:
                conn.close()
                return render_template(
                    "checkout.html",
                    items=items,
                    addresses=addresses,
                    default_address=default_address,
                    total_amount=total_amount,
                    total_quantity=total_quantity,
                    shipping_fee=shipping_fee,
                    payable_amount=payable_amount,
                    error=f"商品 {item.product_name} 库存不足"
                )

        try:
            grouped_items = _split_checkout_items_by_shop(items)
            created_order_ids = _create_split_orders(cur, user_id, addr, grouped_items, shipping_fee)

            # 清空购物车
            cur.execute("DELETE FROM cart_items WHERE cart_id = ?", cart_id)

            conn.commit()
        except Exception as e:
            conn.rollback()
            conn.close()
            return render_template(
                "checkout.html",
                items=items,
                addresses=addresses,
                default_address=default_address,
                total_amount=total_amount,
                total_quantity=total_quantity,
                shipping_fee=shipping_fee,
                payable_amount=payable_amount,
                error=f"创建订单失败：{e}"
            )

        conn.close()

        if len(created_order_ids) == 1:
            return redirect(url_for("order_detail", order_id=created_order_ids[0]))

        return redirect(url_for("batch_pay_orders", order_ids=",".join(str(x) for x in created_order_ids)))

    conn.close()
    return render_template(
        "checkout.html",
        items=items,
        addresses=addresses,
        default_address=default_address,
        total_amount=total_amount,
        total_quantity=total_quantity,
        shipping_fee=shipping_fee,
        payable_amount=payable_amount,
        error=None
    )


@app.route("/orders/pay-batch", methods=["GET", "POST"])
@login_required
@rate_limit_guard("batch_pay_post", 4, 60, "html")
def batch_pay_orders():
    user_id = session["biz_user_id"]

    raw_ids = (request.values.get("order_ids") or "").strip()
    order_ids = []
    for x in raw_ids.split(","):
        x = x.strip()
        if x.isdigit():
            order_ids.append(int(x))
    order_ids = list(dict.fromkeys(order_ids))

    if not order_ids:
        return redirect(url_for("orders"))

    conn = get_conn()
    cur = conn.cursor()

    apply_order_timeouts(cur, user_id)

    placeholders = ",".join("?" for _ in order_ids)
    cur.execute(f"""
        SELECT order_id, user_id, total_amount, order_status, created_at, paid_at
        FROM orders
        WHERE user_id = ? AND order_id IN ({placeholders})
        ORDER BY order_id DESC
    """, [user_id] + order_ids)
    all_orders = cur.fetchall()

    pending_orders = [o for o in all_orders if int(o.order_status) == ORDER_STATUS_PENDING_PAYMENT]
    if not pending_orders:
        conn.close()
        return redirect(url_for("orders"))

    pending_ids = [o.order_id for o in pending_orders]
    placeholders2 = ",".join("?" for _ in pending_ids)

    cur.execute(f"""
        SELECT
            oi.order_id,
            oi.product_id,
            oi.quantity,
            oi.price,
            oi.subtotal,
            oi.shop_id,
            p.product_name,
            p.stock,
            p.status
        FROM order_items oi
        JOIN products p
            ON oi.product_id = p.product_id
        WHERE oi.order_id IN ({placeholders2})
        ORDER BY oi.order_id DESC, oi.order_item_id ASC
    """, pending_ids)
    item_rows = cur.fetchall()

    items_by_order = {}
    total_amount = Decimal("0.00")
    for order in pending_orders:
        items_by_order[order.order_id] = []
        total_amount += money(order.total_amount)

    for row in item_rows:
        items_by_order.setdefault(row.order_id, []).append(row)

    if request.method == "POST":
        cur.execute("""
            SELECT wallet_id, balance
            FROM wallets
            WHERE user_id = ?
        """, user_id)
        wallet = cur.fetchone()

        if not wallet:
            conn.close()
            return render_template(
                "batch_pay.html",
                orders=pending_orders,
                items_by_order=items_by_order,
                total_amount=total_amount,
                error="钱包不存在"
            )

        if float(wallet.balance) < total_amount:
            conn.close()
            return render_template(
                "batch_pay.html",
                orders=pending_orders,
                items_by_order=items_by_order,
                total_amount=total_amount,
                error="余额不足，无法一键付款"
            )

        for order in pending_orders:
            for item in items_by_order.get(order.order_id, []):
                if int(item.status) != 1:
                    conn.close()
                    return render_template(
                        "batch_pay.html",
                        orders=pending_orders,
                        items_by_order=items_by_order,
                        total_amount=total_amount,
                        error=f"商品 {item.product_name} 已下架，无法支付"
                    )
                if int(item.stock) < int(item.quantity):
                    conn.close()
                    return render_template(
                        "batch_pay.html",
                        orders=pending_orders,
                        items_by_order=items_by_order,
                        total_amount=total_amount,
                        error=f"商品 {item.product_name} 库存不足，无法支付"
                    )

        running_balance = money(wallet.balance)

        try:
            for order in pending_orders:
                running_balance = money(running_balance - money(order.total_amount))

                cur.execute("""
                    INSERT INTO wallet_transactions
                        (wallet_id, tx_type, amount, balance_after, related_order_id, remark)
                    VALUES (?, ?, ?, ?, ?, ?)
                """, wallet.wallet_id, 2, order.total_amount, running_balance, order.order_id, '用户批量支付订单，资金冻结待确认收货')

                for item in items_by_order.get(order.order_id, []):
                    cur.execute("""
                        UPDATE products
                        SET stock = stock - ?
                        WHERE product_id = ?
                          AND stock >= ?
                    """, item.quantity, item.product_id, item.quantity)

                    if cur.rowcount != 1:
                        conn.rollback()
                        conn.close()
                        return render_template(
                            "batch_pay.html",
                            orders=pending_orders,
                            items_by_order=items_by_order,
                            total_amount=total_amount,
                            error=f"商品 {item.product_name} 库存扣减失败，请重试"
                        )

                cur.execute("""
                    UPDATE orders
                    SET order_status = ?,
                        paid_at = SYSDATETIME()
                    WHERE order_id = ?
                """, ORDER_STATUS_PAID_PENDING_SHIPMENT, order.order_id)

                cur.execute("""
                    SELECT TOP 1 shipment_id
                    FROM shipments
                    WHERE order_id = ?
                    ORDER BY shipment_id DESC
                """, order.order_id)
                shipment = cur.fetchone()

                if not shipment:
                    cur.execute("""
                        SELECT TOP 1 shop_id
                        FROM order_items
                        WHERE order_id = ?
                    """, order.order_id)
                    row = cur.fetchone()
                    shop_id = row.shop_id if row else None

                    cur.execute("""
                        INSERT INTO shipments (order_id, shop_id, status)
                        VALUES (?, ?, ?)
                    """, order.order_id, shop_id, SHIPMENT_STATUS_PENDING)

                try:
                    cur.execute("""
                        INSERT INTO order_status_logs
                            (order_id, old_status, new_status, operator_user_id, remark)
                        VALUES (?, ?, ?, ?, ?)
                    """, order.order_id, ORDER_STATUS_PENDING_PAYMENT, ORDER_STATUS_PAID_PENDING_SHIPMENT, user_id, '用户完成批量支付，等待商家发货')
                except:
                    pass

            cur.execute("""
                UPDATE wallets
                SET balance = ?, updated_at = SYSDATETIME()
                WHERE wallet_id = ?
            """, running_balance, wallet.wallet_id)

            conn.commit()
        except Exception:
            conn.rollback()
            conn.close()
            return render_template(
                "batch_pay.html",
                orders=pending_orders,
                items_by_order=items_by_order,
                total_amount=total_amount,
                error="批量支付失败，请稍后重试"
            )

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

    conn.close()
    return render_template(
        "batch_pay.html",
        orders=pending_orders,
        items_by_order=items_by_order,
        total_amount=total_amount,
        error=None
    )

@app.route("/orders")
@login_required
def orders():
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    apply_order_timeouts(cur, user_id)

    cur.execute("""
        SELECT
            o.order_id,
            o.total_amount,
            o.order_status,
            o.created_at,
            o.paid_at,
            COUNT(oi.order_item_id) AS item_count,
            s.status AS shipment_status,
            s.shipped_at,
            s.received_at
        FROM orders o
        LEFT JOIN order_items oi ON o.order_id = oi.order_id
        OUTER APPLY (
            SELECT TOP 1 status, shipped_at, received_at
            FROM shipments s
            WHERE s.order_id = o.order_id
            ORDER BY s.shipment_id DESC
        ) s
        WHERE o.user_id = ?
        GROUP BY
            o.order_id, o.total_amount, o.order_status, o.created_at, o.paid_at,
            s.status, s.shipped_at, s.received_at
        ORDER BY o.order_id DESC
    """, user_id)
    rows = cur.fetchall()
    conn.close()

    return render_template("orders.html", orders=[build_order_view(r) for r in rows])


@app.route("/orders/<int:order_id>/pay", methods=["GET", "POST"])
@login_required
@rate_limit_guard("order_pay_post", 6, 60, "html")
def order_pay(order_id):
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    apply_order_timeouts(cur, user_id)

    cur.execute("""
        SELECT order_id, user_id, total_amount, order_status, paid_at
        FROM orders
        WHERE order_id = ? AND user_id = ?
    """, order_id, user_id)
    order = cur.fetchone()

    if not order:
        conn.close()
        abort(404)

    if int(order.order_status) != ORDER_STATUS_PENDING_PAYMENT:
        conn.close()
        return redirect(url_for("order_detail", order_id=order_id, pay_msg="该订单不是待支付状态"))

    cur.execute("""
        SELECT wallet_id, balance
        FROM wallets
        WHERE user_id = ?
    """, user_id)
    wallet = cur.fetchone()

    if not wallet:
        conn.close()
        return redirect(url_for("order_detail", order_id=order_id, pay_msg="钱包不存在"))

    if money(wallet.balance) < money(order.total_amount):
        conn.close()
        return redirect(url_for("order_detail", order_id=order_id, pay_msg="余额不足，支付失败"))

    cur.execute("""
        SELECT oi.product_id, oi.quantity, p.product_name, p.stock, p.status
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        WHERE oi.order_id = ?
    """, order_id)
    items = cur.fetchall()

    for item in items:
        if int(item.status) != 1:
            conn.close()
            return redirect(url_for("order_detail", order_id=order_id, pay_msg=f"商品 {item.product_name} 已下架，支付失败"))
        if int(item.stock) < int(item.quantity):
            conn.close()
            return redirect(url_for("order_detail", order_id=order_id, pay_msg=f"商品 {item.product_name} 库存不足，支付失败"))

    new_balance = money(wallet.balance) - money(order.total_amount)

    try:
        cur.execute("""
            UPDATE wallets
            SET balance = ?, updated_at = SYSDATETIME()
            WHERE wallet_id = ?
        """, new_balance, wallet.wallet_id)

        cur.execute("""
            INSERT INTO wallet_transactions
                (wallet_id, tx_type, amount, balance_after, related_order_id, remark)
            VALUES (?, ?, ?, ?, ?, ?)
        """, wallet.wallet_id, 2, order.total_amount, new_balance, order_id, '用户支付订单，资金冻结待确认收货')

        for item in items:
            cur.execute("""
                UPDATE products
                SET stock = stock - ?
                WHERE product_id = ?
                  AND stock >= ?
            """, item.quantity, item.product_id, item.quantity)

            if cur.rowcount != 1:
                conn.rollback()
                conn.close()
                return redirect(url_for("order_detail", order_id=order_id, pay_msg=f"商品 {item.product_name} 库存扣减失败，请重试"))

        cur.execute("""
            UPDATE orders
            SET order_status = ?,
                paid_at = SYSDATETIME()
            WHERE order_id = ?
        """, ORDER_STATUS_PAID_PENDING_SHIPMENT, order_id)

        cur.execute("""
            SELECT TOP 1 shipment_id
            FROM shipments
            WHERE order_id = ?
            ORDER BY shipment_id DESC
        """, order_id)
        shipment = cur.fetchone()

        if not shipment:
            cur.execute("""
                SELECT TOP 1 shop_id
                FROM order_items
                WHERE order_id = ?
            """, order_id)
            row = cur.fetchone()
            shop_id = row.shop_id if row else None

            cur.execute("""
                INSERT INTO shipments (order_id, shop_id, status)
                VALUES (?, ?, ?)
            """, order_id, shop_id, SHIPMENT_STATUS_PENDING)

        try:
            cur.execute("""
                INSERT INTO order_status_logs
                    (order_id, old_status, new_status, operator_user_id, remark)
                VALUES (?, ?, ?, ?, ?)
            """, order_id, ORDER_STATUS_PENDING_PAYMENT, ORDER_STATUS_PAID_PENDING_SHIPMENT, user_id, '用户完成支付，等待商家发货')
        except:
            pass

        conn.commit()
    except Exception:
        conn.rollback()
        conn.close()
        return redirect(url_for("order_detail", order_id=order_id, pay_msg="支付处理失败，请稍后重试"))

    conn.close()
    return redirect(url_for("order_detail", order_id=order_id, pay_msg="支付成功，等待商家发货"))





@app.route("/orders/<int:order_id>")
@login_required
def order_detail(order_id):
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    apply_order_timeouts(cur, user_id)

    cur.execute("""
        SELECT
            o.order_id,
            o.user_id,
            o.total_amount,
            o.order_status,
            o.created_at,
            o.paid_at,
            s.status AS shipment_status,
            s.shipped_at,
            s.received_at
        FROM orders o
        OUTER APPLY (
            SELECT TOP 1 status, shipped_at, received_at
            FROM shipments s
            WHERE s.order_id = o.order_id
            ORDER BY s.shipment_id DESC
        ) s
        WHERE o.order_id = ? AND o.user_id = ?
    """, order_id, user_id)
    order = cur.fetchone()
    if not order:
        conn.close()
        abort(404)

    cur.execute("""
        SELECT
            oi.order_item_id,
            oi.product_id,
            oi.quantity,
            oi.price,
            oi.subtotal,
            p.product_name,
            ISNULL(pm.file_url, '/uploads/products/default.png') AS media_url
        FROM order_items oi
        JOIN products p ON oi.product_id = p.product_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media pm
            WHERE pm.product_id = p.product_id
            ORDER BY pm.sort_order, pm.media_id
        ) pm
        WHERE oi.order_id = ?
        ORDER BY oi.order_item_id
    """, order_id)
    items = cur.fetchall()

    try:
        cur.execute("""
            SELECT receiver_name, receiver_phone, province, city, district, detail_address, postal_code
            FROM order_addresses
            WHERE order_id = ?
        """, order_id)
        address = cur.fetchone()
    except:
        address = None

    try:
        cur.execute("""
            SELECT TOP 1 shipment_id, status, shipped_at, received_at, carrier_name, tracking_no
            FROM shipments
            WHERE order_id = ?
            ORDER BY shipment_id DESC
        """, order_id)
        shipment = cur.fetchone()
    except:
        shipment = None

    try:
        cur.execute("""
            SELECT log_id, old_status, new_status, remark, created_at
            FROM order_status_logs
            WHERE order_id = ?
            ORDER BY log_id DESC
        """, order_id)
        logs = cur.fetchall()
    except:
        logs = []

    conn.close()

    return render_template(
        "order_detail.html",
        order=build_order_view(order),
        items=items,
        logs=logs,
        address=address,
        shipment=shipment,
        pay_msg=request.args.get("pay_msg")
    )


@app.route("/merchant/orders/<int:order_id>/ship", methods=["POST"])
@login_required
@rate_limit_guard("merchant_ship_post", 20, 60, "html")
def merchant_ship_order(order_id):
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx:
        return redirect(url_for("merchant_dashboard"))

    carrier_name = (request.form.get("carrier_name") or "默认物流").strip()
    tracking_no = (request.form.get("tracking_no") or "").strip() or None

    conn = get_conn()
    cur = conn.cursor()

    apply_order_timeouts(cur)

    try:
        cur.execute("""
            SELECT COUNT(*)
            FROM order_items
            WHERE order_id = ? AND merchant_id = ?
        """, order_id, merchant_ctx["merchant_id"])
        owned_count = cur.fetchone()[0]

        if owned_count <= 0:
            conn.close()
            abort(403)

        cur.execute("""
            SELECT order_status
            FROM orders
            WHERE order_id = ?
        """, order_id)
        order = cur.fetchone()
        if not order or int(order.order_status) != ORDER_STATUS_PAID_PENDING_SHIPMENT:
            conn.close()
            return redirect(url_for("merchant_orders", ship_msg="当前订单不可发货"))

        cur.execute("""
            SELECT TOP 1 shipment_id, status
            FROM shipments
            WHERE order_id = ?
            ORDER BY shipment_id DESC
        """, order_id)
        shipment = cur.fetchone()

        if not shipment:
            cur.execute("""
                SELECT TOP 1 shop_id
                FROM order_items
                WHERE order_id = ?
            """, order_id)
            row = cur.fetchone()
            shop_id = row.shop_id if row else None

            cur.execute("""
                INSERT INTO shipments (order_id, shop_id, status)
                VALUES (?, ?, ?)
            """, order_id, shop_id, SHIPMENT_STATUS_PENDING)

            cur.execute("""
                SELECT TOP 1 shipment_id, status
                FROM shipments
                WHERE order_id = ?
                ORDER BY shipment_id DESC
            """, order_id)
            shipment = cur.fetchone()

        if not shipment:
            raise RuntimeError("发货记录创建失败")

        cur.execute("""
            UPDATE shipments
            SET status = ?,
                shipped_at = SYSDATETIME(),
                carrier_name = ?,
                tracking_no = ?
            WHERE shipment_id = ?
              AND status = ?
        """, SHIPMENT_STATUS_SHIPPED, carrier_name, tracking_no, shipment.shipment_id, SHIPMENT_STATUS_PENDING)

        if cur.rowcount != 1:
            conn.rollback()
            conn.close()
            return redirect(url_for("merchant_orders", ship_msg="发货失败：发货记录状态异常，请刷新后重试"))

        cur.execute("""
            UPDATE orders
            SET order_status = ?
            WHERE order_id = ? AND order_status = ?
        """, ORDER_STATUS_SHIPPED, order_id, ORDER_STATUS_PAID_PENDING_SHIPMENT)

        if cur.rowcount != 1:
            conn.rollback()
            conn.close()
            return redirect(url_for("merchant_orders", ship_msg="发货失败：订单状态异常，请刷新后重试"))

        try:
            cur.execute("""
                INSERT INTO order_status_logs
                    (order_id, old_status, new_status, operator_user_id, remark)
                VALUES (?, ?, ?, ?, ?)
            """, order_id, ORDER_STATUS_PAID_PENDING_SHIPMENT, ORDER_STATUS_SHIPPED, session["biz_user_id"], '商家已发货，系统将在 10 分钟后视为送达')
        except Exception:
            pass

        conn.commit()
    except Exception as e:
        conn.rollback()
        conn.close()
        return redirect(url_for("merchant_orders", ship_msg=f"发货失败：{e}"))

    conn.close()
    return redirect(url_for("merchant_orders", ship_msg="订单已发货"))


@app.route("/orders/<int:order_id>/confirm-receipt", methods=["POST"])
@login_required
@rate_limit_guard("confirm_receipt_post", 10, 60, "html")
def order_confirm_receipt(order_id):
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    apply_order_timeouts(cur, user_id)

    cur.execute("""
        SELECT order_id, user_id, total_amount, order_status
        FROM orders
        WHERE order_id = ? AND user_id = ?
    """, order_id, user_id)
    order = cur.fetchone()

    if not order:
        conn.close()
        abort(404)

    cur.execute("""
        SELECT TOP 1 shipment_id, status, shipped_at
        FROM shipments
        WHERE order_id = ?
        ORDER BY shipment_id DESC
    """, order_id)
    shipment = cur.fetchone()

    if not shipment or int(shipment.status) != SHIPMENT_STATUS_SHIPPED:
        conn.close()
        return redirect(url_for("order_detail", order_id=order_id, pay_msg="当前订单还不能确认收货"))

    if shipment.shipped_at:
        delivery_time = shipment.shipped_at + timedelta(minutes=AUTO_DELIVER_MINUTES)
        if datetime.now() < delivery_time:
            conn.close()
            return redirect(url_for("order_detail", order_id=order_id, pay_msg="商品尚未送达，暂不能确认收货"))

    try:
        ok = settle_order_to_merchants(cur, order_id, user_id, '用户确认收货，订单完成结算')
        if not ok:
            conn.rollback()
            conn.close()
            return redirect(url_for("order_detail", order_id=order_id, pay_msg="确认收货失败，请稍后重试"))

        conn.commit()
    except Exception:
        conn.rollback()
        conn.close()
        return redirect(url_for("order_detail", order_id=order_id, pay_msg="确认收货失败，请稍后重试"))

    conn.close()
    return redirect(url_for("order_detail", order_id=order_id, pay_msg="确认收货成功"))


@app.route("/wallet")
@login_required
def wallet():
    user_id = session["biz_user_id"]
    wallet_id = get_or_create_wallet(user_id)

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT wallet_id, user_id, balance, updated_at
        FROM wallets
        WHERE wallet_id = ?
    """, wallet_id)
    wallet = cur.fetchone()

    cur.execute("""
        SELECT transaction_id, tx_type, amount, balance_after, related_order_id, remark, created_at
        FROM wallet_transactions
        WHERE wallet_id = ?
        ORDER BY transaction_id DESC
    """, wallet_id)
    txs = cur.fetchall()

    conn.close()
    return render_template("wallet.html", wallet=wallet, txs=txs)

@app.route("/addresses", methods=["GET", "POST"])
@login_required
def addresses():
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    if request.method == "POST":
        receiver_name = request.form.get("receiver_name", "").strip()
        receiver_phone = request.form.get("receiver_phone", "").strip()
        province = request.form.get("province", "").strip()
        city = request.form.get("city", "").strip()
        district = request.form.get("district", "").strip()
        detail_address = request.form.get("detail_address", "").strip()
        postal_code = request.form.get("postal_code", "").strip()
        is_default = 1 if request.form.get("is_default") == "1" else 0

        if receiver_name and receiver_phone and province and city and detail_address:
            if is_default:
                cur.execute("""
                    UPDATE user_addresses
                    SET is_default = 0, updated_at = SYSDATETIME()
                    WHERE user_id = ?
                """, user_id)

            cur.execute("""
                INSERT INTO user_addresses
                    (user_id, receiver_name, receiver_phone, province, city, district,
                     detail_address, postal_code, is_default)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, user_id, receiver_name, receiver_phone, province, city, district,
                 detail_address, postal_code if postal_code else None, is_default)
            conn.commit()

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

    cur.execute("""
        SELECT address_id, receiver_name, receiver_phone, province, city, district,
               detail_address, postal_code, is_default, created_at, updated_at
        FROM user_addresses
        WHERE user_id = ?
        ORDER BY is_default DESC, address_id DESC
    """, user_id)
    rows = cur.fetchall()
    conn.close()

    return render_template("addresses.html", addresses=rows)




# ==================================================
# 6. 权限判断与管理员模块
# ==================================================

@app.route("/addresses/<int:address_id>/set-default", methods=["POST"])
@login_required
def address_set_default(address_id):
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT TOP 1 address_id
        FROM user_addresses
        WHERE address_id = ? AND user_id = ?
    """, address_id, user_id)
    row = cur.fetchone()

    if not row:
        conn.close()
        abort(404)

    try:
        cur.execute("""
            UPDATE user_addresses
            SET is_default = 0
            WHERE user_id = ?
        """, user_id)

        cur.execute("""
            UPDATE user_addresses
            SET is_default = 1
            WHERE address_id = ? AND user_id = ?
        """, address_id, user_id)

        conn.commit()
        flash("默认地址已更新。", "success")
    except Exception:
        conn.rollback()
        flash("默认地址更新失败，请稍后重试。", "error")
    finally:
        conn.close()

    return redirect(url_for("addresses"))


@app.route("/addresses/<int:address_id>/delete", methods=["POST"])
@login_required
def address_delete(address_id):
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT TOP 1 address_id, is_default
        FROM user_addresses
        WHERE address_id = ? AND user_id = ?
    """, address_id, user_id)
    row = cur.fetchone()

    if not row:
        conn.close()
        abort(404)

    was_default = bool(getattr(row, "is_default", 0))

    try:
        cur.execute("""
            DELETE FROM user_addresses
            WHERE address_id = ? AND user_id = ?
        """, address_id, user_id)

        if was_default:
            cur.execute("""
                SELECT TOP 1 address_id
                FROM user_addresses
                WHERE user_id = ?
                ORDER BY address_id DESC
            """, user_id)
            next_row = cur.fetchone()
            if next_row:
                cur.execute("""
                    UPDATE user_addresses
                    SET is_default = 1
                    WHERE address_id = ?
                """, next_row.address_id)

        conn.commit()
        flash("地址已删除。", "success")
    except Exception:
        conn.rollback()
        flash("删除地址失败，请稍后重试。", "error")
    finally:
        conn.close()

    return redirect(url_for("addresses"))

def user_has_role(cur, user_id, role_name):
    cur.execute("""
        SELECT TOP 1 ur.user_id
        FROM user_roles ur
        JOIN roles r ON ur.role_id = r.role_id
        WHERE ur.user_id = ? AND r.role_name = ?
    """, user_id, role_name)
    return cur.fetchone() is not None


def get_role_flags(cur, user_id):
    return {
        "is_admin": user_has_role(cur, user_id, "admin"),
        "is_merchant": user_has_role(cur, user_id, "merchant"),
    }


def admin_required(view_func):
    @wraps(view_func)
    def wrapper(*args, **kwargs):
        if not session.get("biz_user_id"):
            ok = apply_firebase_login_to_session()
            if not ok and not session.get("biz_user_id"):
                return redirect(url_for("firebase_login"))

        conn = get_conn()
        cur = conn.cursor()
        ok_admin = user_has_role(cur, session["biz_user_id"], "admin")
        conn.close()

        if not ok_admin:
            abort(403)

        return view_func(*args, **kwargs)
    return wrapper


def merchant_required(view_func):
    @wraps(view_func)
    def wrapper(*args, **kwargs):
        if not session.get("biz_user_id"):
            ok = apply_firebase_login_to_session()
            if not ok and not session.get("biz_user_id"):
                return redirect(url_for("firebase_login"))

        conn = get_conn()
        cur = conn.cursor()
        ok_merchant = user_has_role(cur, session["biz_user_id"], "merchant")
        conn.close()

        if not ok_merchant:
            return redirect(url_for("merchant_apply"))

        return view_func(*args, **kwargs)
    return wrapper


# ==== admin site branding route start ====
@app.route("/admin/site-branding", methods=["GET", "POST"])
@admin_required
def admin_site_branding():
    form_data = load_site_branding()
    message = None
    error = None

    if request.method == "POST":
        old_favicon_url = load_site_branding().get("favicon_url", DEFAULT_SITE_BRANDING["favicon_url"])
        new_favicon_url = None

        site_name = (request.form.get("site_name") or "").strip()
        header_name = (request.form.get("header_name") or "").strip()
        browser_title = (request.form.get("browser_title") or "").strip()

        form_data = {
            "site_name": site_name or DEFAULT_SITE_BRANDING["site_name"],
            "header_name": header_name or DEFAULT_SITE_BRANDING["header_name"],
            "browser_title": browser_title or DEFAULT_SITE_BRANDING["browser_title"],
            "favicon_url": old_favicon_url,
        }

        if len(form_data["site_name"]) > 40:
            error = "网站名称不能超过 40 个字"
        elif len(form_data["header_name"]) > 40:
            error = "头顶名称不能超过 40 个字"
        elif len(form_data["browser_title"]) > 60:
            error = "浏览器标题不能超过 60 个字"
        else:
            icon_file = request.files.get("favicon_image")
            try:
                if icon_file and icon_file.filename:
                    new_favicon_url = save_site_icon_image(icon_file)
                    form_data["favicon_url"] = new_favicon_url

                save_site_branding(form_data)

                if new_favicon_url and old_favicon_url and old_favicon_url != new_favicon_url and is_managed_site_icon_url(old_favicon_url):
                    delete_local_upload_file(old_favicon_url, SITE_BRANDING_UPLOAD_DIR)

                message = "站点设置已保存"
            except Exception as e:
                if new_favicon_url and new_favicon_url != old_favicon_url and is_managed_site_icon_url(new_favicon_url):
                    delete_local_upload_file(new_favicon_url, SITE_BRANDING_UPLOAD_DIR)
                error = str(e)

    return render_template(
        "admin_site_branding.html",
        form_data=form_data,
        message=message,
        error=error
    )
# ==== admin site branding route end ====


# ==== admin index start ====
@app.route("/admin/")
@admin_required
def admin_index():
    conn = get_conn()
    cur = conn.cursor()

    try:
        cur.execute("SELECT COUNT(*) AS cnt FROM merchant_applications")
        merchant_application_count = int(cur.fetchone().cnt)
    except:
        merchant_application_count = 0

    try:
        cur.execute("SELECT COUNT(*) AS cnt FROM users")
        user_count = int(cur.fetchone().cnt)
    except:
        user_count = 0

    try:
        cur.execute("SELECT COUNT(*) AS cnt FROM orders")
        order_count = int(cur.fetchone().cnt)
    except:
        order_count = 0

    conn.close()

    return render_template(
        "admin_index.html",
        merchant_application_count=merchant_application_count,
        user_count=user_count,
        order_count=order_count
    )
# ==== admin index end ====


@app.route("/merchant/apply", methods=["GET", "POST"])
@login_required
def merchant_apply():
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    # 已经是商家则直接去商家后台
    if user_has_role(cur, user_id, "merchant"):
        conn.close()
        return redirect(url_for("merchant_dashboard"))

    message = None
    error = None

    cur.execute("""
        SELECT TOP 1
            application_id, shop_name, contact_name, contact_phone,
            business_scope, application_reason, status, review_remark,
            created_at, reviewed_at
        FROM merchant_applications
        WHERE user_id = ?
        ORDER BY application_id DESC
    """, user_id)
    app_row = cur.fetchone()

    if request.method == "POST":
        if app_row and int(app_row.status) == 0:
            conn.close()
            return render_template("merchant_apply.html", app_row=app_row, error="你已有待审核申请，请勿重复提交", message=None)

        shop_name = (request.form.get("shop_name") or "").strip()
        contact_name = (request.form.get("contact_name") or "").strip()
        contact_phone = (request.form.get("contact_phone") or "").strip()
        business_scope = (request.form.get("business_scope") or "").strip()
        application_reason = (request.form.get("application_reason") or "").strip()

        if not shop_name or not contact_name or not contact_phone:
            error = "店铺名称、联系人、联系电话不能为空"
        elif len(shop_name) > 100 or len(contact_name) > 50 or len(contact_phone) > 30:
            error = "输入内容过长"
        else:
            cur.execute("""
                INSERT INTO merchant_applications
                    (user_id, shop_name, contact_name, contact_phone, business_scope, application_reason, status)
                VALUES (?, ?, ?, ?, ?, ?, 0)
            """, user_id, shop_name, contact_name, contact_phone, business_scope or None, application_reason or None)
            conn.commit()

            cur.execute("""
                SELECT TOP 1
                    application_id, shop_name, contact_name, contact_phone,
                    business_scope, application_reason, status, review_remark,
                    created_at, reviewed_at
                FROM merchant_applications
                WHERE user_id = ?
                ORDER BY application_id DESC
            """, user_id)
            app_row = cur.fetchone()
            message = "商家入驻申请已提交，请等待管理员审核"

    conn.close()
    return render_template("merchant_apply.html", app_row=app_row, error=error, message=message)


@app.route("/admin/merchant-applications")
@admin_required
def admin_merchant_applications():
    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT
            ma.application_id,
            ma.user_id,
            u.username,
            u.email,
            ma.shop_name,
            ma.contact_name,
            ma.contact_phone,
            ma.business_scope,
            ma.application_reason,
            ma.status,
            ma.review_remark,
            ma.created_at,
            ma.reviewed_at
        FROM merchant_applications ma
        JOIN users u ON ma.user_id = u.user_id
        ORDER BY
            CASE WHEN ma.status = 0 THEN 0 ELSE 1 END,
            ma.application_id DESC
    """)
    rows = cur.fetchall()

    conn.close()
    return render_template("admin_merchant_applications.html", applications=rows)


@app.route("/admin/merchant-applications/<int:application_id>/approve", methods=["POST"])
@admin_required
def approve_merchant_application(application_id):
    admin_user_id = session["biz_user_id"]
    review_remark = (request.form.get("review_remark") or "").strip() or "审核通过"

    conn = get_conn()
    cur = conn.cursor()

    try:
        cur.execute("""
            SELECT TOP 1 application_id, user_id, shop_name, status
            FROM merchant_applications
            WHERE application_id = ?
        """, application_id)
        app_row = cur.fetchone()

        if not app_row or int(app_row.status) != 0:
            flash("申请不存在或已处理。", "error")
            conn.close()
            return redirect(url_for("admin_merchant_applications"))

        user_id = app_row.user_id
        shop_name = (app_row.shop_name or "").strip() or f"店铺{user_id}"

        cur.execute("""
            SELECT TOP 1 role_id
            FROM roles
            WHERE role_name = 'merchant'
        """)
        role_row = cur.fetchone()
        if not role_row:
            raise RuntimeError("缺少 merchant 角色，请先检查 roles 表。")

        merchant_role_id = role_row.role_id

        cur.execute("""
            SELECT TOP 1 1
            FROM user_roles
            WHERE user_id = ? AND role_id = ?
        """, user_id, merchant_role_id)
        if not cur.fetchone():
            cur.execute("""
                INSERT INTO user_roles (user_id, role_id)
                VALUES (?, ?)
            """, user_id, merchant_role_id)

        cur.execute("""
            SELECT TOP 1 merchant_id
            FROM merchants
            WHERE user_id = ?
            ORDER BY merchant_id DESC
        """, user_id)
        merchant_row = cur.fetchone()

        if merchant_row:
            merchant_id = merchant_row.merchant_id
            cur.execute("""
                UPDATE merchants
                SET shop_name = ?, status = 1
                WHERE merchant_id = ?
            """, shop_name, merchant_id)
        else:
            cur.execute("""
                INSERT INTO merchants (user_id, shop_name, status)
                VALUES (?, ?, 1)
            """, user_id, shop_name)

            cur.execute("""
                SELECT TOP 1 merchant_id
                FROM merchants
                WHERE user_id = ?
                ORDER BY merchant_id DESC
            """, user_id)
            merchant_row = cur.fetchone()
            if not merchant_row:
                raise RuntimeError("创建 merchants 记录失败。")
            merchant_id = merchant_row.merchant_id

        cur.execute("""
            SELECT TOP 1 shop_id
            FROM shops
            WHERE merchant_id = ?
        """, merchant_id)
        shop_row = cur.fetchone()

        if shop_row:
            cur.execute("""
                UPDATE shops
                SET shop_name = ?, status = 1
                WHERE shop_id = ?
            """, shop_name, shop_row.shop_id)
        else:
            slug = f"shop-{merchant_id}"
            cur.execute("""
                INSERT INTO shops (merchant_id, shop_name, shop_slug, status)
                VALUES (?, ?, ?, 1)
            """, merchant_id, shop_name, slug)

        cur.execute("""
            UPDATE merchant_applications
            SET status = 1,
                review_remark = ?,
                reviewed_by = ?,
                reviewed_at = SYSDATETIME()
            WHERE application_id = ? AND status = 0
        """, review_remark, admin_user_id, application_id)

        if cur.rowcount != 1:
            raise RuntimeError("申请状态更新失败。")

        conn.commit()
        flash("商家申请已审核通过。", "success")
    except Exception as e:
        conn.rollback()
        flash(f"审核通过失败：{e}", "error")
    finally:
        conn.close()

    return redirect(url_for("admin_merchant_applications"))

@app.route("/admin/merchant-applications/<int:application_id>/reject", methods=["POST"])
@admin_required
def reject_merchant_application(application_id):
    admin_user_id = session["biz_user_id"]
    review_remark = (request.form.get("review_remark") or "").strip() or "审核未通过"

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        UPDATE merchant_applications
        SET status = 2,
            review_remark = ?,
            reviewed_by = ?,
            reviewed_at = SYSDATETIME()
        WHERE application_id = ? AND status = 0
    """, review_remark, admin_user_id, application_id)

    conn.commit()
    conn.close()

    return redirect(url_for("admin_merchant_applications"))


PRODUCT_IMAGE_EXTENSIONS = {"png", "jpg", "jpeg", "webp"}
PRODUCT_IMAGE_UPLOAD_DIR = "/var/www/shopping/uploads/products"
DEFAULT_PRODUCT_IMAGE_URL = "/uploads/products/default.png"
PRODUCT_IMAGE_MAX_SIDE = 1200



# ==================================================
# 7. 商家商品、富文本、店铺主页模块
# ==================================================
def allowed_product_image_file(filename):
    if not filename or "." not in filename:
        return False
    ext = filename.rsplit(".", 1)[1].lower()
    return ext in PRODUCT_IMAGE_EXTENSIONS




def is_default_product_cover_url(file_url: str) -> bool:
    value = (file_url or "").strip()
    if not value:
        return True
    if value == DEFAULT_PRODUCT_IMAGE_URL:
        return True
    name = os.path.basename(value).lower()
    return name == "default.png"

def save_product_image(file_storage, product_id):
    if not file_storage or not file_storage.filename:
        return None

    if not allowed_product_image_file(file_storage.filename):
        raise ValueError("商品图片只允许 png、jpg、jpeg、webp 格式")

    os.makedirs(PRODUCT_IMAGE_UPLOAD_DIR, exist_ok=True)

    try:
        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream)
        img.verify()

        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream).convert("RGB")
    except Exception:
        raise ValueError("上传文件不是有效图片")

    w, h = img.size
    side = min(w, h)
    left = (w - side) // 2
    top = (h - side) // 2
    img = img.crop((left, top, left + side, top + side))

    if side > PRODUCT_IMAGE_MAX_SIDE:
        img = img.resize((PRODUCT_IMAGE_MAX_SIDE, PRODUCT_IMAGE_MAX_SIDE))

    new_name = f"product_{product_id}_{uuid.uuid4().hex[:16]}.jpg"
    save_path = os.path.join(PRODUCT_IMAGE_UPLOAD_DIR, new_name)
    img.save(save_path, format="JPEG", quality=88, optimize=True)

    return f"/uploads/products/{new_name}"


def _safe_richtext_url(raw_url):
    raw_url = html.unescape((raw_url or "").strip())
    if not raw_url:
        return None
    if raw_url.startswith(("http://", "https://", "/uploads/", "/static/", "/")):
        return html.escape(raw_url, quote=True)
    return None


DEFAULT_PRODUCT_DESCRIPTION_HTML = """
<div class="bb-empty">
  这里后续可以继续扩展更多商品详情内容，例如商品规格、商品属性、自定义字段、用户评价、发货与退款说明等。当前先保留为课程项目的详情展示区。
</div>
""".strip()


def render_product_description(raw_text):
    source = (raw_text or "").strip()
    if not source:
        return DEFAULT_PRODUCT_DESCRIPTION_HTML

    text = html.escape(source).replace("\r\n", "\n").replace("\r", "\n")
    blocks = []

    def stash(block_html):
        key = f"__BB_BLOCK_{len(blocks)}__"
        blocks.append(block_html)
        return key

    def repl_code(match):
        inner = match.group(1)
        return stash(f'<pre class="bb-code">{inner}</pre>')

    def repl_quote(match):
        inner = match.group(1)
        return stash(f'<blockquote class="bb-quote">{inner}</blockquote>')

    def repl_img(match):
        url = _safe_richtext_url(match.group(1))
        if not url:
            return stash('<div class="bb-warn">[图片链接无效]</div>')
        return stash(
            f'<div class="bb-image-wrap"><img class="bb-image" src="{url}" alt="商品说明图片" loading="lazy"></div>'
        )

    def repl_url_label(match):
        url = _safe_richtext_url(match.group(1))
        label = match.group(2)
        if not url:
            return label
        return f'<a class="bb-link" href="{url}" target="_blank" rel="nofollow noopener noreferrer">{label}</a>'

    def repl_url_plain(match):
        url = _safe_richtext_url(match.group(1))
        label = html.escape(html.unescape(match.group(1)).strip())
        if not url:
            return label
        return f'<a class="bb-link" href="{url}" target="_blank" rel="nofollow noopener noreferrer">{label}</a>'

    def repl_list(match):
        inner = match.group(1)
        parts = [p.strip() for p in re.split(r'\[\*\]', inner, flags=re.I) if p.strip()]
        if not parts:
            return ''
        return stash('<ul class="bb-list">' + ''.join(f'<li>{p}</li>' for p in parts) + '</ul>')

    text = re.sub(r'\[code\](.*?)\[/code\]', repl_code, text, flags=re.I | re.S)
    text = re.sub(r'\[quote\](.*?)\[/quote\]', repl_quote, text, flags=re.I | re.S)
    text = re.sub(r'\[img\](.*?)\[/img\]', repl_img, text, flags=re.I | re.S)
    text = re.sub(r'\[list\](.*?)\[/list\]', repl_list, text, flags=re.I | re.S)
    text = re.sub(r'\[url=(.*?)\](.*?)\[/url\]', repl_url_label, text, flags=re.I | re.S)
    text = re.sub(r'\[url\](.*?)\[/url\]', repl_url_plain, text, flags=re.I | re.S)

    simple_tags = {
        'b': 'strong',
        'i': 'em',
        'u': 'u',
        's': 's',
    }

    for _ in range(4):
        old = text
        for bb, tag in simple_tags.items():
            text = re.sub(
                rf'\[{bb}\](.*?)\[/{bb}\]',
                rf'<{tag}>\1</{tag}>',
                text,
                flags=re.I | re.S
            )
        text = re.sub(r'\[h1\](.*?)\[/h1\]', r'<div class="bb-h1">\1</div>', text, flags=re.I | re.S)
        text = re.sub(r'\[h2\](.*?)\[/h2\]', r'<div class="bb-h2">\1</div>', text, flags=re.I | re.S)
        text = re.sub(r'\[h3\](.*?)\[/h3\]', r'<div class="bb-h3">\1</div>', text, flags=re.I | re.S)
        text = re.sub(r'\[hr\]', r'<hr class="bb-hr">', text, flags=re.I)
        if text == old:
            break

    text = text.replace('\n', '<br>\n')

    for idx, block in enumerate(blocks):
        text = text.replace(f'__BB_BLOCK_{idx}__', block)

    return text



PRODUCT_CONTENT_IMAGE_EXTENSIONS = {"png", "jpg", "jpeg", "webp"}
PRODUCT_CONTENT_IMAGE_UPLOAD_DIR = "/var/www/shopping/uploads/products/content"
PRODUCT_CONTENT_IMAGE_MAX_SIDE = 1600
PRODUCT_CONTENT_IMAGE_MAX_PIXELS = 20000000


def save_product_content_image(file_storage):
    if not file_storage or not file_storage.filename:
        raise ValueError("未选择说明图片")

    if "." not in file_storage.filename:
        raise ValueError("说明图片格式不正确")

    ext = file_storage.filename.rsplit(".", 1)[1].lower()
    if ext not in PRODUCT_CONTENT_IMAGE_EXTENSIONS:
        raise ValueError("说明图片只允许 png、jpg、jpeg、webp")

    os.makedirs(PRODUCT_CONTENT_IMAGE_UPLOAD_DIR, exist_ok=True)

    try:
        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream)
        img.verify()

        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream)

        w, h = img.size
        if w <= 0 or h <= 0:
            raise ValueError("说明图片尺寸无效")
        if w * h > PRODUCT_CONTENT_IMAGE_MAX_PIXELS:
            raise ValueError("说明图片像素过大，请压缩后再上传")

        img = img.convert("RGB")
    except ValueError:
        raise
    except Exception:
        raise ValueError("上传文件不是有效图片")

    w, h = img.size
    if max(w, h) > PRODUCT_CONTENT_IMAGE_MAX_SIDE:
        if w >= h:
            new_w = PRODUCT_CONTENT_IMAGE_MAX_SIDE
            new_h = int(h * PRODUCT_CONTENT_IMAGE_MAX_SIDE / w)
        else:
            new_h = PRODUCT_CONTENT_IMAGE_MAX_SIDE
            new_w = int(w * PRODUCT_CONTENT_IMAGE_MAX_SIDE / h)
        img = img.resize((new_w, new_h))

    filename = f"desc_{uuid.uuid4().hex[:16]}.jpg"
    save_path = os.path.join(PRODUCT_CONTENT_IMAGE_UPLOAD_DIR, filename)
    img.save(save_path, format="JPEG", quality=88, optimize=True)

    return f"/uploads/products/content/{filename}"


def sanitize_rich_html(raw_html):
    value = (raw_html or "").strip()
    if not value:
        return ""

    value = re.sub(r'(?is)<(script|style).*?>.*?</\\1>', '', value)
    value = re.sub(r'(?i)\\son\\w+\\s*=\\s*"[^"]*"', '', value)
    value = re.sub(r"(?i)\\son\\w+\\s*=\\s*'[^']*'", '', value)
    value = re.sub(r'(?i)\\son\\w+\\s*=\\s*[^\\s>]+', '', value)
    value = re.sub(r'(?i)(href|src)\\s*=\\s*"javascript:[^"]*"', r'\\1="#"', value)
    value = re.sub(r"(?i)(href|src)\\s*=\\s*'javascript:[^']*'", r"\\1='#'", value)
    return value


def rich_html_to_plain_text(raw_html):
    value = raw_html or ""
    value = re.sub(r'(?is)<br\\s*/?>', '\\n', value)
    value = re.sub(r'(?is)</(p|div|li|h1|h2|h3|blockquote|pre)>', '\\n', value)
    value = re.sub(r'(?is)<[^>]+>', '', value)
    value = html.unescape(value)
    value = re.sub(r'\\s+', ' ', value).strip()
    return value


def get_current_merchant_context():
    user_id = session.get("biz_user_id")
    if not user_id:
        return None

    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        SELECT TOP 1
            m.merchant_id,
            m.user_id,
            m.shop_name AS merchant_shop_name,
            m.status AS merchant_status,
            s.shop_id,
            s.shop_name,
            s.shop_slug,
            s.status AS shop_status
        FROM merchants m
        LEFT JOIN shops s
            ON s.merchant_id = m.merchant_id
        WHERE m.user_id = ? AND m.status = 1
        ORDER BY s.shop_id ASC, m.merchant_id ASC
        """,
        (user_id,),
    )
    row = cur.fetchone()
    conn.close()

    if not row:
        return None

    return {
        "merchant_id": row[0],
        "user_id": row[1],
        "merchant_shop_name": row[2],
        "merchant_status": row[3],
        "shop_id": row[4],
        "shop_name": row[5],
        "shop_slug": row[6],
        "shop_status": row[7],
    }


def get_product_categories():
    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        SELECT category_id, category_name
        FROM categories
        ORDER BY category_id ASC
        """
    )
    rows = cur.fetchall()
    conn.close()
    return [{"category_id": r[0], "category_name": r[1]} for r in rows]


def get_owned_product(cur, merchant_id, shop_id, product_id):
    cur.execute(
        """
        SELECT TOP 1
            p.product_id,
            p.merchant_id,
            p.category_id,
            p.product_name,
            p.description,
            p.price,
            p.stock,
            p.status,
            p.created_at,
            p.updated_at,
            p.shop_id,
            pm.file_url AS cover_url
        FROM products p
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media
            WHERE product_id = p.product_id AND media_type = 1
            ORDER BY sort_order ASC, media_id ASC
        ) pm
        WHERE p.product_id = ? AND p.merchant_id = ? AND p.shop_id = ?
        """,
        (product_id, merchant_id, shop_id),
    )
    row = cur.fetchone()
    if not row:
        return None
    return {
        "product_id": row[0],
        "merchant_id": row[1],
        "category_id": row[2],
        "product_name": row[3],
        "description": row[4],
        "price": row[5],
        "stock": row[6],
        "status": row[7],
        "created_at": row[8],
        "updated_at": row[9],
        "shop_id": row[10],
        "cover_url": row[11],
    }


def upsert_product_cover_media(cur, product_id, file_url):
    now = datetime.now()
    cur.execute(
        """
        SELECT TOP 1 media_id
        FROM product_media
        WHERE product_id = ? AND media_type = 1
        ORDER BY sort_order ASC, media_id ASC
        """,
        (product_id,),
    )
    row = cur.fetchone()

    if row:
        cur.execute(
            """
            UPDATE product_media
            SET file_url = ?, sort_order = 1
            WHERE media_id = ?
            """,
            (file_url, row[0]),
        )
    else:
        cur.execute(
            """
            INSERT INTO product_media
                (product_id, media_type, file_url, sort_order, created_at)
            VALUES (?, ?, ?, ?, ?)
            """,
            (product_id, 1, file_url, 1, now),
        )




def parse_product_form():
    product_name = (request.form.get("product_name") or "").strip()
    description = sanitize_rich_html(request.form.get("description") or "")
    category_raw = (request.form.get("category_id") or "").strip()
    price_raw = (request.form.get("price") or "").strip()
    stock_raw = (request.form.get("stock") or "").strip()

    errors = []

    if not product_name:
        errors.append("商品名称不能为空。")

    try:
        price = Decimal(price_raw)
        if price < 0:
            errors.append("商品价格不能小于 0。")
    except Exception:
        price = None
        errors.append("商品价格格式不正确。")

    try:
        stock = int(stock_raw)
        if stock < 0:
            errors.append("库存不能小于 0。")
    except Exception:
        stock = None
        errors.append("库存必须是整数。")

    category_id = None
    if category_raw:
        try:
            category_id = int(category_raw)
        except Exception:
            errors.append("分类选择无效。")

    return {
        "product_name": product_name,
        "description": description or None,
        "category_id": category_id,
        "price": price,
        "stock": stock,
        "errors": errors,
    }





# ==== admin wallet adjustments start ====
def _admin_wallet_display_name(row):
    nickname = getattr(row, "nickname", None)
    username = getattr(row, "username", None)
    if nickname and str(nickname).strip():
        return str(nickname).strip()
    if username and str(username).strip():
        return str(username).strip()
    return f"用户{row.user_id}"


def _get_non_admin_wallet_users(cur):
    cur.execute("""
        SELECT
            u.user_id,
            u.username,
            u.nickname,
            u.avatar_url,
            ISNULL(w.balance, 0) AS balance
        FROM users u
        LEFT JOIN wallets w
            ON w.user_id = u.user_id
        WHERE ISNULL(u.status, 1) = 1
          AND NOT EXISTS (
                SELECT 1
                FROM user_roles ur
                JOIN roles r
                    ON r.role_id = ur.role_id
                WHERE ur.user_id = u.user_id
                  AND r.role_name = 'admin'
          )
        ORDER BY u.user_id DESC
    """)
    rows = cur.fetchall()

    users = []
    for row in rows:
        users.append({
            "user_id": row.user_id,
            "display_name": _admin_wallet_display_name(row),
            "avatar_url": getattr(row, "avatar_url", None),
            "balance": float(row.balance or 0),
        })
    return users


@app.route("/admin/wallet-adjustments", methods=["GET", "POST"])
@admin_required
def admin_wallet_adjustments():
    admin_user_id = session["biz_user_id"]
    admin_name = session.get("biz_display_name") or session.get("biz_username") or f"管理员{admin_user_id}"
    admin_label = f"管理员{admin_name}"

    conn = get_conn()
    cur = conn.cursor()

    message = None
    error = None

    form_data = {
        "target_mode": "single",
        "target_user_id": "",
        "action_type": "credit",
        "amount": "",
        "custom_text": "",
    }

    if request.method == "POST":
        target_mode = (request.form.get("target_mode") or "single").strip().lower()
        target_user_id = (request.form.get("target_user_id") or "").strip()
        action_type = (request.form.get("action_type") or "credit").strip().lower()
        amount_raw = (request.form.get("amount") or "").strip()
        custom_text = (request.form.get("custom_text") or "").strip()

        form_data = {
            "target_mode": target_mode,
            "target_user_id": target_user_id,
            "action_type": action_type,
            "amount": amount_raw,
            "custom_text": custom_text,
        }

        if target_mode not in {"single", "all"}:
            error = "操作目标不合法。"
        elif action_type not in {"credit", "debit"}:
            error = "操作类型不合法。"
        else:
            try:
                amount = money(amount_raw)
            except:
                amount = Decimal("-1.00")

            if amount <= 0:
                error = "金额必须大于 0。"
            elif amount > Decimal("1000000.00"):
                error = "单次金额不能超过 1000000。"
            elif len(custom_text) > 200:
                error = "自定义说明不能超过 200 字。"

        targets = []
        if not error:
            if target_mode == "single":
                if not target_user_id.isdigit():
                    error = "请选择有效用户。"
                else:
                    cur.execute("""
                        SELECT
                            u.user_id,
                            u.username,
                            u.nickname
                        FROM users u
                        WHERE u.user_id = ?
                          AND ISNULL(u.status, 1) = 1
                          AND NOT EXISTS (
                                SELECT 1
                                FROM user_roles ur
                                JOIN roles r
                                    ON r.role_id = ur.role_id
                                WHERE ur.user_id = u.user_id
                                  AND r.role_name = 'admin'
                          )
                    """, int(target_user_id))
                    row = cur.fetchone()
                    if not row:
                        error = "目标用户不存在或不可操作。"
                    else:
                        targets = [row]
            else:
                cur.execute("""
                    SELECT
                        u.user_id,
                        u.username,
                        u.nickname
                    FROM users u
                    WHERE ISNULL(u.status, 1) = 1
                      AND NOT EXISTS (
                            SELECT 1
                            FROM user_roles ur
                            JOIN roles r
                                ON r.role_id = ur.role_id
                            WHERE ur.user_id = u.user_id
                              AND r.role_name = 'admin'
                      )
                    ORDER BY u.user_id DESC
                """)
                targets = cur.fetchall()
                if not targets:
                    error = "当前没有可操作的用户。"

        if not error:
            success_count = 0
            skipped = []

            try:
                for row in targets:
                    wallet = ensure_wallet(cur, row.user_id)
                    current_balance = money(wallet.balance or 0)

                    if action_type == "debit" and current_balance < amount:
                        skipped.append(_admin_wallet_display_name(row))
                        continue

                    if action_type == "credit":
                        new_balance = money(current_balance + amount)
                        remark = f"{admin_label}向您钱包转账"
                        tx_type = 4
                    else:
                        new_balance = money(current_balance - amount)
                        remark = f"{admin_label}从您的钱包扣款"
                        tx_type = 5

                    if custom_text:
                        remark = f"{remark}：{custom_text}"

                    cur.execute("""
                        UPDATE wallets
                        SET balance = ?, updated_at = SYSDATETIME()
                        WHERE wallet_id = ?
                    """, new_balance, wallet.wallet_id)

                    cur.execute("""
                        INSERT INTO wallet_transactions
                            (wallet_id, tx_type, amount, balance_after, related_order_id, remark)
                        VALUES (?, ?, ?, ?, ?, ?)
                    """, wallet.wallet_id, tx_type, amount, new_balance, None, remark)

                    success_count += 1

                conn.commit()

                if skipped:
                    message = f"已成功处理 {success_count} 个用户；以下用户余额不足，已跳过：{'、'.join(skipped[:8])}" + (" 等" if len(skipped) > 8 else "")
                else:
                    message = f"已成功处理 {success_count} 个用户。"

                form_data = {
                    "target_mode": "single",
                    "target_user_id": "",
                    "action_type": "credit",
                    "amount": "",
                    "custom_text": "",
                }
            except Exception:
                conn.rollback()
                error = "钱包调账失败，请稍后重试。"

    users = _get_non_admin_wallet_users(cur)
    conn.close()

    return render_template(
        "admin_wallet_adjustments.html",
        users=users,
        message=message,
        error=error,
        form_data=form_data,
        admin_name=admin_name,
    )
# ==== admin wallet adjustments end ====


@app.route("/merchant/editor/upload-image", methods=["POST"])
@login_required
@rate_limit_guard("merchant_editor_upload_post", 40, 300, "json")
def merchant_editor_upload_image():
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx:
        return jsonify({
            "ok": False,
            "errno": 1,
            "message": "无商家权限",
            "error": "无商家权限"
        }), 403

    file = request.files.get("image") or request.files.get("file")

    try:
        url = save_product_content_image(file)
        return jsonify({
            "ok": True,
            "errno": 0,
            "message": "上传成功",
            "url": url,
            "data": {"url": url}
        })
    except Exception as e:
        msg = str(e) or "图片上传失败"
        return jsonify({
            "ok": False,
            "errno": 1,
            "message": msg,
            "error": msg
        }), 400



def merchant_format_countdown(target_dt):
    if not target_dt:
        return None
    now = datetime.now()
    seconds = int((target_dt - now).total_seconds())
    if seconds < 0:
        seconds = 0
    days, rem = divmod(seconds, 86400)
    hours, rem = divmod(rem, 3600)
    minutes, sec = divmod(rem, 60)
    if days > 0:
        return f"{days}天 {hours:02d}:{minutes:02d}:{sec:02d}"
    return f"{hours:02d}:{minutes:02d}:{sec:02d}"


def merchant_order_status_text(order_status, shipped_at=None, received_at=None):
    now = datetime.now()
    status = int(order_status)

    if status == 0:
        return "待支付"
    if status == 1:
        return "已支付，待发货"
    if status == 2:
        if shipped_at:
            delivery_time = shipped_at + timedelta(minutes=10)
            if now < delivery_time:
                return "已发货，运输中"
            if received_at:
                return "已签收"
            return "已送达，待收货"
        return "已发货"
    if status == 3:
        return "已完成"
    if status == 4:
        return "已取消"
    return f"状态 {status}"


def build_merchant_order_view(row):
    now = datetime.now()
    order_status = int(row.order_status)
    created_at = row.created_at
    paid_at = getattr(row, "paid_at", None)
    shipped_at = getattr(row, "shipped_at", None)
    received_at = getattr(row, "received_at", None)

    result = {
        "order_id": row.order_id,
        "total_amount": float(row.total_amount),
        "order_status": order_status,
        "created_at": created_at,
        "paid_at": paid_at,
        "item_count": row.item_count,
        "shipment_status": getattr(row, "shipment_status", None),
        "shipped_at": shipped_at,
        "received_at": received_at,
        "carrier_name": getattr(row, "carrier_name", None),
        "tracking_no": getattr(row, "tracking_no", None),
        "status_text": merchant_order_status_text(order_status, shipped_at, received_at),
        "countdown_label": None,
        "countdown_text": None,
        "countdown_deadline_iso": None,
        "delivery_time": None,
        "can_ship": False,
    }

    if order_status == 1 and paid_at:
        deadline = paid_at + timedelta(days=1)
        result["countdown_label"] = "发货剩余时间"
        result["countdown_text"] = merchant_format_countdown(deadline)
        result["countdown_deadline_iso"] = deadline.isoformat()
        result["can_ship"] = True

    elif order_status == 2 and shipped_at:
        delivery_time = shipped_at + timedelta(minutes=10)
        result["delivery_time"] = delivery_time

        if now >= delivery_time and not received_at:
            auto_receive_time = delivery_time + timedelta(days=7)
            result["countdown_label"] = "自动签收剩余时间"
            result["countdown_text"] = merchant_format_countdown(auto_receive_time)
            result["countdown_deadline_iso"] = auto_receive_time.isoformat()

    return result



@app.route("/merchant/orders")
@merchant_required
def merchant_orders():
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx or not merchant_ctx.get("shop_id"):
        flash("未找到可用的商家或店铺信息。", "error")
        return redirect(url_for("merchant_dashboard"))

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT
            o.order_id,
            o.total_amount,
            o.order_status,
            o.created_at,
            o.paid_at,
            COUNT(oi.order_item_id) AS item_count,
            s.status AS shipment_status,
            s.shipped_at,
            s.received_at,
            s.carrier_name,
            s.tracking_no
        FROM orders o
        JOIN order_items oi
            ON o.order_id = oi.order_id
        OUTER APPLY (
            SELECT TOP 1
                status, shipped_at, received_at, carrier_name, tracking_no
            FROM shipments s
            WHERE s.order_id = o.order_id
            ORDER BY s.shipment_id DESC
        ) s
        WHERE oi.merchant_id = ?
        GROUP BY
            o.order_id, o.total_amount, o.order_status, o.created_at, o.paid_at,
            s.status, s.shipped_at, s.received_at, s.carrier_name, s.tracking_no
        ORDER BY o.order_id DESC
    """, merchant_ctx["merchant_id"])
    rows = cur.fetchall()
    conn.close()

    orders = [build_merchant_order_view(row) for row in rows]

    return render_template(
        "merchant_orders.html",
        merchant_ctx=merchant_ctx,
        orders=orders,
        ship_msg=request.args.get("ship_msg")
    )


@app.route("/merchant/products")
@merchant_required
def merchant_products():
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx or not merchant_ctx.get("shop_id"):
        flash("未找到可用的商家或店铺信息。", "error")
        return redirect(url_for("merchant_dashboard"))

    conn = get_conn()
    cur = conn.cursor()
    cur.execute(
        """
        SELECT
            p.product_id,
            p.product_name,
            p.price,
            p.stock,
            p.status,
            p.created_at,
            p.updated_at,
            c.category_name,
            pm.file_url AS cover_url
        FROM products p
        LEFT JOIN categories c
            ON c.category_id = p.category_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media
            WHERE product_id = p.product_id AND media_type = 1
            ORDER BY sort_order ASC, media_id ASC
        ) pm
        WHERE p.merchant_id = ? AND p.shop_id = ?
        ORDER BY p.product_id DESC
        """,
        (merchant_ctx["merchant_id"], merchant_ctx["shop_id"]),
    )
    rows = cur.fetchall()
    conn.close()

    products = []
    for r in rows:
        products.append({
            "product_id": r[0],
            "product_name": r[1],
            "price": r[2],
            "stock": r[3],
            "status": r[4],
            "created_at": r[5],
            "updated_at": r[6],
            "category_name": r[7],
            "cover_url": r[8],
        })

    return render_template(
        "merchant_products.html",
        merchant_ctx=merchant_ctx,
        products=products,
    )


@app.route("/merchant/products/new", methods=["GET", "POST"])
@merchant_required
def merchant_product_create():
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx or not merchant_ctx.get("shop_id"):
        flash("未找到可用的商家或店铺信息。", "error")
        return redirect(url_for("merchant_dashboard"))

    categories = get_product_categories()

    if request.method == "POST":
        data = parse_product_form()
        if data["errors"]:
            for msg in data["errors"]:
                flash(msg, "error")
            return render_template(
                "merchant_product_form.html",
                merchant_ctx=merchant_ctx,
                categories=categories,
                product=None,
                form_data=request.form,
                page_title="添加商品",
            )

        conn = get_conn()
        cur = conn.cursor()
        now = datetime.now()

        cur.execute(
            """
            INSERT INTO products
                (merchant_id, category_id, product_name, description, price, stock, status, created_at, updated_at, shop_id)
            OUTPUT INSERTED.product_id
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                merchant_ctx["merchant_id"],
                data["category_id"],
                data["product_name"],
                data["description"],
                data["price"],
                data["stock"],
                1,
                now,
                now,
                merchant_ctx["shop_id"],
            ),
        )
        product_id = cur.fetchone()[0]

        cover_file = request.files.get("cover_image")
        try:
            if cover_file and cover_file.filename:
                file_url = save_product_image(cover_file, product_id)
                upsert_product_cover_media(cur, product_id, file_url)
            else:
                upsert_product_cover_media(cur, product_id, DEFAULT_PRODUCT_IMAGE_URL)
        except Exception as e:
            conn.rollback()
            conn.close()
            flash(str(e), "error")
            return render_template(
                "merchant_product_form.html",
                merchant_ctx=merchant_ctx,
                categories=categories,
                product=None,
                form_data=request.form,
                page_title="添加商品",
            )

        conn.commit()
        conn.close()

        flash("商品添加成功。", "success")
        return redirect(url_for("merchant_products"))

    return render_template(
        "merchant_product_form.html",
        merchant_ctx=merchant_ctx,
        categories=categories,
        product=None,
        form_data=None,
        page_title="添加商品",
    )


@app.route("/merchant/products/<int:product_id>/edit", methods=["GET", "POST"])
@merchant_required
def merchant_product_edit(product_id):
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx or not merchant_ctx.get("shop_id"):
        flash("未找到可用的商家或店铺信息。", "error")
        return redirect(url_for("merchant_dashboard"))

    categories = get_product_categories()
    conn = get_conn()
    cur = conn.cursor()

    product = get_owned_product(cur, merchant_ctx["merchant_id"], merchant_ctx["shop_id"], product_id)
    if not product:
        conn.close()
        abort(404)

    if request.method == "POST":
        data = parse_product_form()
        if data["errors"]:
            for msg in data["errors"]:
                flash(msg, "error")
            conn.close()
            return render_template(
                "merchant_product_form.html",
                merchant_ctx=merchant_ctx,
                categories=categories,
                product=product,
                form_data=request.form,
                page_title="编辑商品",
            )

        now = datetime.now()
        cur.execute(
            """
            UPDATE products
            SET category_id = ?,
                product_name = ?,
                description = ?,
                price = ?,
                stock = ?,
                updated_at = ?
            WHERE product_id = ? AND merchant_id = ? AND shop_id = ?
            """,
            (
                data["category_id"],
                data["product_name"],
                data["description"],
                data["price"],
                data["stock"],
                now,
                product_id,
                merchant_ctx["merchant_id"],
                merchant_ctx["shop_id"],
            ),
        )

        old_cover_url = product.get("cover_url")
        new_cover_url = None

        cover_file = request.files.get("cover_image")
        if cover_file and cover_file.filename:
            try:
                new_cover_url = save_product_image(cover_file, product_id)
                upsert_product_cover_media(cur, product_id, new_cover_url)
            except Exception as e:
                if new_cover_url and new_cover_url != old_cover_url and not is_default_product_cover_url(new_cover_url):
                    delete_local_upload_file(new_cover_url, PRODUCT_IMAGE_UPLOAD_DIR)
                conn.rollback()
                conn.close()
                flash(str(e), "error")
                return render_template(
                    "merchant_product_form.html",
                    merchant_ctx=merchant_ctx,
                    categories=categories,
                    product=product,
                    form_data=request.form,
                    page_title="编辑商品",
                )

        conn.commit()
        conn.close()

        if new_cover_url and old_cover_url and old_cover_url != new_cover_url and not is_default_product_cover_url(old_cover_url):
            delete_local_upload_file(old_cover_url, PRODUCT_IMAGE_UPLOAD_DIR)

        flash("商品修改成功。", "success")
        return redirect(url_for("merchant_products"))

    conn.close()
    return render_template(
        "merchant_product_form.html",
        merchant_ctx=merchant_ctx,
        categories=categories,
        product=product,
        form_data=None,
        page_title="编辑商品",
    )


@app.route("/merchant/products/<int:product_id>/toggle-status", methods=["POST"])
@merchant_required
def merchant_product_toggle_status(product_id):
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx or not merchant_ctx.get("shop_id"):
        flash("未找到可用的商家或店铺信息。", "error")
        return redirect(url_for("merchant_dashboard"))

    conn = get_conn()
    cur = conn.cursor()

    product = get_owned_product(cur, merchant_ctx["merchant_id"], merchant_ctx["shop_id"], product_id)
    if not product:
        conn.close()
        abort(404)

    cur.execute(
        """
        UPDATE products
        SET status = CASE WHEN status = 1 THEN 0 ELSE 1 END,
            updated_at = ?
        WHERE product_id = ? AND merchant_id = ? AND shop_id = ?
        """,
        (
            datetime.now(),
            product_id,
            merchant_ctx["merchant_id"],
            merchant_ctx["shop_id"],
        ),
    )

    conn.commit()
    conn.close()

    flash("商品状态已切换。", "success")
    return redirect(url_for("merchant_products"))

@app.route("/merchant/dashboard")
@merchant_required
def merchant_dashboard():
    user_id = session["biz_user_id"]

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT TOP 1 m.merchant_id, m.shop_name, s.shop_id, s.shop_name, s.shop_slug
        FROM merchants m
        LEFT JOIN shops s ON m.merchant_id = s.merchant_id
        WHERE m.user_id = ?
    """, user_id)
    merchant = cur.fetchone()

    if not merchant:
        conn.close()
        return redirect(url_for("merchant_apply"))

    cur.execute("""
        SELECT COUNT(*)
        FROM products
        WHERE merchant_id = ?
    """, merchant.merchant_id)
    product_count = cur.fetchone()[0]

    cur.execute("""
        SELECT COUNT(DISTINCT order_id)
        FROM order_items
        WHERE merchant_id = ?
    """, merchant.merchant_id)
    order_count = cur.fetchone()[0]

    cur.execute("""
        SELECT COUNT(DISTINCT order_id)
        FROM order_items
        WHERE merchant_id = ? AND order_id IN (
            SELECT order_id FROM orders WHERE order_status = 1
        )
    """, merchant.merchant_id)
    paid_order_count = cur.fetchone()[0]

    conn.close()

    return render_template(
        "merchant_dashboard.html",
        merchant=merchant,
        product_count=product_count,
        order_count=order_count,
        paid_order_count=paid_order_count
    )



@app.route("/account", methods=["GET", "POST"])
@login_required
def account():
    user = get_current_business_user()
    user_id = user["user_id"]

    conn = get_conn()
    cur = conn.cursor()

    message = None
    error = None

    if request.method == "POST":
        action = request.form.get("action", "").strip()

        if action == "update_nickname":
            nickname = sanitize_nickname(request.form.get("nickname", ""))

            if not nickname:
                error = "昵称只能为 2-20 位中文、英文、数字或下划线"
            else:
                cur.execute("""
                    UPDATE users
                    SET nickname = ?
                    WHERE user_id = ?
                """, nickname, user_id)
                conn.commit()
                message = "昵称修改成功"
                session["biz_nickname"] = nickname
                session["biz_display_name"] = nickname

        elif action == "upload_avatar":
            file = request.files.get("avatar")

            cur.execute("""
                SELECT avatar_url
                FROM users
                WHERE user_id = ?
            """, user_id)
            old_avatar_row = cur.fetchone()
            old_avatar_url = getattr(old_avatar_row, "avatar_url", None) if old_avatar_row else None

            ok, result = save_avatar_file(file)
            if not ok:
                error = result
            else:
                cur.execute("""
                    UPDATE users
                    SET avatar_url = ?
                    WHERE user_id = ?
                """, result, user_id)
                conn.commit()
                session["biz_avatar_url"] = result

                if old_avatar_url and old_avatar_url != result and not is_default_avatar_url(old_avatar_url):
                    delete_local_upload_file(old_avatar_url, AVATAR_UPLOAD_DIR)

                message = "头像上传成功"

    cur.execute("""
        SELECT
            user_id,
            username,
            email,
            nickname,
            avatar_url,
            status,
            created_at
        FROM users
        WHERE user_id = ?
    """, user_id)
    profile = cur.fetchone()

    role_flags = get_role_flags(cur, user_id)
    session["biz_is_admin"] = role_flags["is_admin"]
    session["biz_is_merchant"] = role_flags["is_merchant"]

    conn.close()

    return render_template(
        "account.html",
        user=user,
        profile=profile,
        message=message,
        error=error,
        is_admin=role_flags["is_admin"],
        is_merchant=role_flags["is_merchant"]
    )



# ==== shop homepage feature start ====
SHOP_LOGO_EXTENSIONS = {"png", "jpg", "jpeg", "webp"}


def allowed_shop_logo_file(filename):
    if not filename or "." not in filename:
        return False
    ext = filename.rsplit(".", 1)[1].lower()
    return ext in SHOP_LOGO_EXTENSIONS


def save_shop_logo_image(file_storage, shop_id):
    if not file_storage or not file_storage.filename:
        return None

    if not allowed_shop_logo_file(file_storage.filename):
        raise ValueError("店铺头像只允许 png、jpg、jpeg、webp 格式")

    upload_dir = "/var/www/shopping/uploads/shops"
    os.makedirs(upload_dir, exist_ok=True)

    try:
        img = Image.open(file_storage.stream)
        img.verify()
        file_storage.stream.seek(0)
        img = Image.open(file_storage.stream).convert("RGB")
    except Exception:
        raise ValueError("上传的店铺头像不是有效图片")

    w, h = img.size
    side = min(w, h)
    left = (w - side) // 2
    top = (h - side) // 2
    img = img.crop((left, top, left + side, top + side))

    if side > 800:
        img = img.resize((800, 800))

    new_name = f"shop_logo_{shop_id}_{uuid.uuid4().hex[:16]}.jpg"
    save_path = os.path.join(upload_dir, new_name)
    img.save(save_path, format="JPEG", quality=88, optimize=True)

    return f"/uploads/shops/{new_name}"


def get_shop_home_payload(shop_slug):
    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT TOP 1
            s.shop_id,
            s.merchant_id,
            s.shop_name,
            s.shop_slug,
            s.logo_url,
            s.banner_url,
            s.intro,
            s.announcement,
            s.contact_phone,
            s.status,
            m.user_id AS merchant_user_id
        FROM shops s
        JOIN merchants m
            ON m.merchant_id = s.merchant_id
        WHERE s.shop_slug = ? AND s.status = 1 AND m.status = 1
    """, shop_slug)
    shop = cur.fetchone()

    if not shop:
        conn.close()
        return None

    cur.execute("""
        SELECT
            ISNULL(SUM(CASE WHEN o.order_status = 3 THEN oi.quantity ELSE 0 END), 0) AS sold_count,
            ISNULL(SUM(CASE WHEN o.order_status = 3 THEN oi.subtotal ELSE 0 END), 0) AS gross_revenue,
            COUNT(DISTINCT CASE WHEN o.order_status = 3 THEN o.order_id END) AS settled_order_count
        FROM order_items oi
        LEFT JOIN orders o
            ON o.order_id = oi.order_id
        WHERE oi.shop_id = ?
    """, shop.shop_id)
    stats = cur.fetchone()

    cur.execute("""
        SELECT
            p.product_id,
            p.product_name,
            p.price,
            p.stock,
            p.status,
            p.updated_at,
            c.category_name,
            ISNULL(pm.file_url, '/uploads/products/default.png') AS cover_url
        FROM products p
        LEFT JOIN categories c
            ON c.category_id = p.category_id
        OUTER APPLY (
            SELECT TOP 1 file_url
            FROM product_media
            WHERE product_id = p.product_id AND media_type = 1
            ORDER BY sort_order ASC, media_id ASC
        ) pm
        WHERE p.shop_id = ? AND p.status = 1
        ORDER BY p.product_id DESC
    """, shop.shop_id)
    products = cur.fetchall()

    conn.close()

    return {
        "shop": shop,
        "stats": {
            "sold_count": int(stats.sold_count or 0),
            "gross_revenue": float(stats.gross_revenue or 0),
            "settled_order_count": int(stats.settled_order_count or 0),
            "product_count": len(products),
        },
        "products": products,
        "homepage_html": sanitize_rich_html(getattr(shop, "announcement", None)),
    }


@app.route("/shop/<shop_slug>")
def shop_home(shop_slug):
    apply_firebase_login_to_session()

    payload = get_shop_home_payload(shop_slug)
    if not payload:
        abort(404)

    return render_template(
        "shop_home.html",
        shop=payload["shop"],
        stats=payload["stats"],
        products=payload["products"],
        homepage_html=payload["homepage_html"],
    )


@app.route("/merchant/shop/edit", methods=["GET", "POST"])
@merchant_required
def merchant_shop_edit():
    merchant_ctx = get_current_merchant_context()
    if not merchant_ctx or not merchant_ctx.get("shop_id"):
        flash("未找到可用的店铺信息。", "error")
        return redirect(url_for("merchant_dashboard"))

    conn = get_conn()
    cur = conn.cursor()

    cur.execute("""
        SELECT TOP 1
            s.shop_id,
            s.merchant_id,
            s.shop_name,
            s.shop_slug,
            s.logo_url,
            s.banner_url,
            s.intro,
            s.announcement,
            s.contact_phone,
            s.status
        FROM shops s
        WHERE s.shop_id = ? AND s.merchant_id = ?
    """, merchant_ctx["shop_id"], merchant_ctx["merchant_id"])
    shop = cur.fetchone()

    if not shop:
        conn.close()
        abort(404)

    if request.method == "POST":
        shop_name = (request.form.get("shop_name") or "").strip()
        intro = (request.form.get("intro") or "").strip()
        contact_phone = (request.form.get("contact_phone") or "").strip()
        announcement = sanitize_rich_html(request.form.get("announcement") or "")

        errors = []

        if not shop_name:
            errors.append("店铺名称不能为空。")
        if len(shop_name) > 100:
            errors.append("店铺名称过长。")
        if len(intro) > 300:
            errors.append("店铺简略描述不能超过 300 字。")
        if len(contact_phone) > 30:
            errors.append("联系电话长度不合法。")

        logo_url = getattr(shop, "logo_url", None)
        logo_file = request.files.get("logo_image")
        if logo_file and logo_file.filename:
            try:
                logo_url = save_shop_logo_image(logo_file, shop.shop_id)
            except Exception as e:
                errors.append(str(e))

        if errors:
            for msg in errors:
                flash(msg, "error")
            conn.close()
            return render_template(
                "merchant_shop_edit.html",
                merchant_ctx=merchant_ctx,
                shop=shop,
                form_data=request.form,
            )

        try:
            cur.execute("""
                UPDATE shops
                SET shop_name = ?,
                    logo_url = ?,
                    intro = ?,
                    announcement = ?,
                    contact_phone = ?,
                    updated_at = SYSDATETIME()
                WHERE shop_id = ? AND merchant_id = ?
            """, shop_name, logo_url, intro or None, announcement or None, contact_phone or None, shop.shop_id, merchant_ctx["merchant_id"])

            try:
                cur.execute("""
                    UPDATE merchants
                    SET shop_name = ?,
                        description = ?
                    WHERE merchant_id = ?
                """, shop_name, intro or None, merchant_ctx["merchant_id"])
            except Exception:
                pass

            conn.commit()
        except pyodbc.Error as e:
            conn.rollback()
            err = str(e)

            if "would be truncated" in err.lower() or "2628" in err:
                flash("店铺主页内容过长，当前无法保存，请适当缩短内容。", "error")
            else:
                flash(f"保存店铺主页失败：{err}", "error")

            conn.close()
            return render_template(
                "merchant_shop_edit.html",
                merchant_ctx=merchant_ctx,
                shop=shop,
                form_data=request.form,
            )

        conn.close()
        flash("店铺主页信息已更新。", "success")
        return redirect(url_for("merchant_shop_edit"))

    conn.close()
    return render_template(
        "merchant_shop_edit.html",
        merchant_ctx=merchant_ctx,
        shop=shop,
        form_data=None,
    )
# ==== shop homepage feature end ====






# ==================================================
# 8. 统一错误响应模块
# ==================================================
def wants_json_error_response():
    accept = (request.headers.get("Accept") or "").lower()
    xrw = (request.headers.get("X-Requested-With") or "").lower()
    content_type = (request.headers.get("Content-Type") or "").lower()

    if request.path.startswith("/localLogin") or request.path.startswith("/sessionLogin"):
        return True
    if request.path.startswith("/merchant/editor/upload-image"):
        return True
    if request.is_json:
        return True
    if "application/json" in accept:
        return True
    if "application/json" in content_type:
        return True
    if xrw == "xmlhttprequest":
        return True
    return False

def build_error_page_payload(status_code, err):
    branding = load_site_branding()
    titles = {
        400: "请求无效",
        403: "无权访问",
        404: "页面不存在",
        413: "上传文件过大",
        429: "操作过于频繁",
        500: "服务器开小差了",
    }
    messages = {
        400: "当前请求无法被服务器正确处理，请返回上一页检查表单内容后再试。",
        403: "你当前没有权限访问这个页面或执行这个操作。",
        404: "你访问的页面不存在，可能已经被移动、删除，或链接地址填写有误。",
        413: "本次上传的文件超过了服务器允许的大小限制，请压缩后再试。",
        429: "你的操作过于频繁，请稍等片刻后再试。",
        500: "服务器处理请求时出现异常。你可以稍后重试，或返回首页继续浏览。",
    }

    title = titles.get(status_code, "请求处理失败")
    message = messages.get(status_code, "请求暂时无法完成，请稍后重试。")

    description = getattr(err, "description", None)
    if status_code in {400, 403, 404, 413, 429} and description and str(description).strip():
        message = str(description).strip()

    return {
        "status_code": status_code,
        "title": title,
        "message": message,
        "site_branding": branding,
    }

def render_error_response(status_code, err):
    payload = build_error_page_payload(status_code, err)

    if wants_json_error_response():
        return jsonify({
            "ok": False,
            "status": payload["status_code"],
            "title": payload["title"],
            "error": payload["message"],
        }), status_code

    return render_template(
        "error_page.html",
        status_code=payload["status_code"],
        title=payload["title"],
        message=payload["message"],
        site_branding=payload["site_branding"],
    ), status_code

@app.errorhandler(400)
def handle_bad_request(err):
    return render_error_response(400, err)

@app.errorhandler(403)
def handle_forbidden(err):
    return render_error_response(403, err)

@app.errorhandler(404)
def handle_not_found(err):
    return render_error_response(404, err)

@app.errorhandler(429)
def handle_too_many_requests(err):
    return render_error_response(429, err)

@app.errorhandler(RequestEntityTooLarge)
def handle_request_entity_too_large(err):
    return render_error_response(413, err)

@app.errorhandler(500)
def handle_internal_server_error(err):
    return render_error_response(500, err)


if __name__ == "__main__":
    app.run(
        host=_env("SHOPPING_APP_HOST", "127.0.0.1"),
        port=_env_int("SHOPPING_APP_PORT", 5000),
        debug=_env_bool("SHOPPING_APP_DEBUG", False),
    )
