import csv
import io
import json
import re
import uuid
from datetime import datetime, timedelta
from pathlib import Path
from typing import List

import markdown as md
from fastapi import Depends, FastAPI, Form, HTTPException, Request
from fastapi.concurrency import run_in_threadpool
from fastapi.responses import FileResponse, HTMLResponse, PlainTextResponse, RedirectResponse, Response
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates
from markupsafe import Markup

import auth
import captcha
import config
import db
import dllimit
import docimport

app = FastAPI(title=config.SITE_NAME)
app.mount("/static", StaticFiles(directory=str(Path(__file__).parent / "static")), name="static")
templates = Jinja2Templates(directory=str(Path(__file__).parent / "templates"))
templates.env.globals["site_name"] = config.SITE_NAME
templates.env.globals["site_settings"] = db.get_settings
SESSION_COOKIE = "site_session"

CATEGORY_ICONS = {
    "box": '<path d="M21 8l-9-5-9 5 9 5 9-5zM3 8v8l9 5 9-5V8M12 13v8" fill="none" stroke="currentColor" stroke-width="2" stroke-linejoin="round"/>',
    "tool": '<path d="M14.7 6.3a4 4 0 00-5.4 5.4L3 18v3h3l6.3-6.3a4 4 0 005.4-5.4l-2.9 2.9-2.1-2.1 2.9-2.9z" fill="none" stroke="currentColor" stroke-width="2" stroke-linejoin="round"/>',
    "image": '<rect x="3" y="4" width="18" height="16" rx="2" fill="none" stroke="currentColor" stroke-width="2"/><circle cx="9" cy="10" r="2" fill="currentColor"/><path d="M3 17l5-4 4 3 4-4 5 5" fill="none" stroke="currentColor" stroke-width="2"/>',
    "network": '<circle cx="12" cy="5" r="2.5" fill="none" stroke="currentColor" stroke-width="2"/><circle cx="5" cy="19" r="2.5" fill="none" stroke="currentColor" stroke-width="2"/><circle cx="19" cy="19" r="2.5" fill="none" stroke="currentColor" stroke-width="2"/><path d="M12 7.5v4m0 0l-5.5 5.5M12 11.5l5.5 5.5" stroke="currentColor" stroke-width="2"/>',
    "doc": '<path d="M6 2h9l5 5v15H6zM14 2v6h6M9 13h8M9 17h8" fill="none" stroke="currentColor" stroke-width="2" stroke-linejoin="round"/>',
    "other": '<circle cx="12" cy="12" r="9" fill="none" stroke="currentColor" stroke-width="2"/><path d="M9.5 9a2.5 2.5 0 015 0c0 1.7-2.5 2-2.5 4M12 17h.01" stroke="currentColor" stroke-width="2" stroke-linecap="round"/>',
}
templates.env.globals["cat_icon"] = lambda name: Markup(CATEGORY_ICONS.get(name, CATEGORY_ICONS["box"]))


@app.on_event("startup")
def startup():
    db.init_db()


# ---------- helpers ----------

def human_size(n) -> str:
    n = int(n or 0)
    for unit in ("B", "KB", "MB", "GB", "TB"):
        if n < 1024 or unit == "TB":
            return f"{n:.1f} {unit}" if unit != "B" else f"{n} B"
        n /= 1024
    return f"{n} TB"


templates.env.filters["human_size"] = human_size


def render_md(text: str) -> str:
    return md.markdown(text or "", extensions=["fenced_code", "tables", "nl2br"])


def plain_text(text: str) -> str:
    text = re.sub(r"```[\s\S]*?```", " ", text or "")
    text = re.sub(r"`([^`]*)`", r"\1", text)
    text = re.sub(r"!\[[^\]]*\]\([^)]*\)", " ", text)
    text = re.sub(r"\[([^\]]*)\]\([^)]*\)", r"\1", text)
    text = re.sub(r"^\s{0,3}#{1,6}\s+", "", text, flags=re.M)
    text = re.sub(r"^\s*[-*+]\s+", "", text, flags=re.M)
    text = re.sub(r"^\s*>\s?", "", text, flags=re.M)
    text = re.sub(r"[*_~]{1,3}", "", text)
    text = re.sub(r"<[^>]+>", " ", text)  # 去掉 <video>/<img> 等内嵌 HTML
    return re.sub(r"\s+", " ", text).strip()


templates.env.filters["plain"] = plain_text


def client_ip(request: Request) -> str:
    return request.headers.get("x-real-ip") or (request.client.host if request.client else "unknown")


def current_admin(request: Request):
    return auth.get_session_admin(request.cookies.get(SESSION_COOKIE))


def require_admin(request: Request):
    """任意已登录账号（管理员或投稿员）"""
    admin = current_admin(request)
    if not admin:
        raise HTTPException(status_code=307, headers={"Location": "/admin/login"})
    return admin


def require_full_admin(request: Request):
    """仅管理员角色；投稿员重定向到自己的投稿页"""
    admin = require_admin(request)
    if admin.get("role") != "admin":
        raise HTTPException(status_code=307, headers={"Location": "/admin/my"})
    return admin


# 漏洞严重程度：值 -> (中文名, CSS 类)
SEVERITIES = {
    "critical": ("严重", "sev-critical"),
    "high": ("高危", "sev-high"),
    "medium": ("中危", "sev-medium"),
    "low": ("低危", "sev-low"),
}
templates.env.globals["severities"] = SEVERITIES


def home_for(admin) -> str:
    return "/admin" if admin.get("role") == "admin" else "/admin/my"


def safe_filename(name: str) -> str:
    name = Path(name).name
    return re.sub(r"[^\w.\-\u4e00-\u9fff]+", "_", name)[:120] or "unnamed"


def attachment_disposition(filename: str) -> str:
    """生成下载文件名响应头。
    HTTP 头按 latin-1 编码，直接写中文会抛 UnicodeEncodeError → 500，
    因此 filename 只保留 ASCII 兜底，真实文件名用 RFC 5987 的 filename* 传递。"""
    from urllib.parse import quote
    name = filename or "download"
    fallback = re.sub(r'["\\\\]', "_", name.encode("ascii", "ignore").decode("ascii")).strip() or "download"
    return f"attachment; filename=\"{fallback}\"; filename*=UTF-8''{quote(name)}"


def normalize_http_link(url: str, max_len: int = 500) -> str:
    """规范化外链：局域网常只填 IP/主机名（如 10.0.0.5:8080/oa），
    无协议头时自动补 http://；显式带协议则只允许 http/https（防 javascript: 等）。"""
    url = (url or "").strip()
    if not url or any(ch.isspace() for ch in url):
        raise ValueError("网址格式不正确（不应包含空格）")
    m = re.match(r"^([a-zA-Z][a-zA-Z0-9+.\-]*):", url)
    if m:
        if m.group(1).lower() not in ("http", "https"):
            raise ValueError("网址仅支持 http:// 或 https:// 开头")
    else:
        url = "http://" + url
    if not re.match(r"^https?://\S+$", url, re.I):
        raise ValueError("网址格式不正确")
    return url[:max_len]


def maintenance_guard(request: Request):
    """维护模式：前台所有路由对所有人（含已登录管理员）展示维护页。
    后台 /admin/* 路由不调用此守卫，管理员仍可直接访问后台整理内容。"""
    if db.get_settings().get("maintenance") == "1":
        return templates.TemplateResponse(
            request, "maintenance.html", {"admin": current_admin(request)}, status_code=503
        )
    return None


def parse_tags(text: str) -> List[str]:
    parts = re.split(r"[,，、;；\s]+", text or "")
    seen, out = set(), []
    for p in parts:
        p = p.strip()[:32]
        if p and p not in seen:
            seen.add(p)
            out.append(p)
    return out


def sync_tags(conn, software_id: int, tags: List[str]):
    conn.execute("DELETE FROM software_tag WHERE software_id = ?", (software_id,))
    for name in tags:
        conn.insert_ignore("INSERT INTO tag (name) VALUES (?)", (name,))
        row = conn.execute("SELECT id FROM tag WHERE name = ?", (name,)).fetchone()
        conn.execute(
            "INSERT INTO software_tag (software_id, tag_id) VALUES (?, ?)",
            (software_id, row["id"]),
        )


def software_tags(conn, software_id: int):
    rows = conn.execute(
        "SELECT t.name FROM tag t JOIN software_tag st ON st.tag_id = t.id WHERE st.software_id = ?",
        (software_id,),
    ).fetchall()
    return [r["name"] for r in rows]


# ---------- 首页布局区块 ----------

# 首页区块类型：值 -> 中文名
BLOCK_TYPES = {
    "hero": "横幅搜索",
    "chips": "分类与标签条",
    "category": "软件板块",
    "vulns": "漏洞补丁专区",
    "announce": "公告条",
    "links": "快捷链接",
    "html": "自定义内容",
}


def _build_home_layout(blocks, cats, items, vuln_rows):
    """把可见区块转成模板可直接渲染的单元序列。
    category 区块的软件从已查出的 items 里按分类过滤；连续两个半宽板块并成一行。"""
    cat_map = {c["id"]: c for c in cats}
    by_cat: dict = {}
    for i in items:
        by_cat.setdefault(i["category_id"] or 0, []).append(i)
    prepared = []
    for b in blocks:
        try:
            cfg = json.loads(b["config"] or "{}")
        except ValueError:
            cfg = {}
        u = {"type": b["block_type"], "title": b["title"], "cfg": cfg}
        if u["type"] == "category":
            cat = cat_map.get(b["ref_id"])
            if not cat:
                continue  # 分类已删除或隐藏，跳过该区块
            sw = by_cat.get(cat["id"], [])
            maxn = cfg.get("max", 0)
            u["cat"] = cat
            u["total"] = len(sw)
            u["items"] = sw[:maxn] if isinstance(maxn, int) and maxn > 0 else sw
            u["half"] = cfg.get("width") == "half"
            if not u["items"]:
                continue  # 空分类不占位
        elif u["type"] == "vulns":
            maxn = cfg.get("max", 6)
            u["vulns"] = vuln_rows[:maxn] if isinstance(maxn, int) and maxn > 0 else vuln_rows
        elif u["type"] == "html":
            u["html_content"] = render_md(cfg.get("content", ""))
        prepared.append(u)
    units = []
    pending = []
    for u in prepared:
        if u["type"] == "category" and u.get("half"):
            pending.append(u)
            if len(pending) == 2:
                units.append({"row": pending})
                pending = []
        else:
            if pending:
                units.append({"row": pending})
                pending = []
            units.append({"one": u})
    if pending:
        units.append({"row": pending})
    return units


# ---------- 访客端 ----------

@app.get("/", response_class=HTMLResponse)
def index(request: Request, category: int = 0, q: str = "", tag: str = ""):
    if resp := maintenance_guard(request):
        return resp
    pure_home = not (q or tag or category)
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM category WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        all_tags = conn.execute(
            "SELECT t.name, COUNT(st.software_id) AS n FROM tag t "
            "JOIN software_tag st ON st.tag_id = t.id GROUP BY t.name ORDER BY n DESC LIMIT 20"
        ).fetchall()
        base_sql = """SELECT s.*, c.name AS category_name, c.icon AS category_icon, c.color AS category_color
                      FROM software s LEFT JOIN category c ON c.id = s.category_id WHERE s.status = 1"""
        params: list = []
        if category:
            base_sql += " AND s.category_id = ?"
            params.append(category)
        if q:
            base_sql += " AND (s.name LIKE ? OR s.description LIKE ?)"
            params += [f"%{q}%", f"%{q}%"]
            conn.execute(
                "INSERT INTO search_log (keyword, client_ip, created_at) VALUES (?,?,?)",
                (q[:64], client_ip(request), db.now_str()),
            )
        if tag:
            base_sql += " AND s.id IN (SELECT st.software_id FROM software_tag st JOIN tag t ON t.id = st.tag_id WHERE t.name = ?)"
            params.append(tag)
        base_sql += " ORDER BY s.pin_weight DESC, s.created_at DESC"
        items = conn.execute(base_sql, params).fetchall()
        if items:
            ids = ",".join(str(i["id"]) for i in items)
            tag_rows = conn.execute(
                f"""SELECT st.software_id, t.name FROM software_tag st JOIN tag t ON t.id = st.tag_id
                    WHERE st.software_id IN ({ids})"""
            ).fetchall()
        else:
            tag_rows = []
        blocks, vuln_rows = [], []
        if pure_home:
            blocks = conn.execute(
                "SELECT * FROM home_block WHERE visible = 1 ORDER BY sort_order, id"
            ).fetchall()
            if blocks and any(b["block_type"] == "vulns" for b in blocks):
                vuln_rows = conn.execute(
                    """SELECT v.*, (SELECT COUNT(*) FROM vulnerability_file f WHERE f.vulnerability_id = v.id) AS file_count
                       FROM vulnerability v WHERE v.status = 1 ORDER BY v.created_at DESC, v.id DESC LIMIT 24"""
                ).fetchall()
    tags_by_sw = {}
    for r in tag_rows:
        tags_by_sw.setdefault(r["software_id"], []).append(r["name"])
    sections = []
    layout = None
    if pure_home:
        if blocks:
            layout = _build_home_layout(blocks, cats, items, vuln_rows)
        else:
            # 未配置区块时回退默认布局：横幅 + 分类条 + 各分类板块
            for c in cats:
                group = [i for i in items if i["category_id"] == c["id"]]
                if group:
                    sections.append({"cat": c, "items": group})
            unc = [i for i in items if not i["category_id"]]
            if unc:
                sections.append({"cat": {"name": "未分类", "icon": "other", "color": "#64748b"}, "items": unc})
    return templates.TemplateResponse(request, "index.html", {
        "cats": cats, "items": items, "sections": sections, "all_tags": all_tags,
        "tags_by_sw": tags_by_sw, "cur_cat": category, "q": q, "cur_tag": tag,
        "layout": layout,
    })


@app.get("/software/{sid:int}", response_class=HTMLResponse)
def detail(request: Request, sid: int):
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        row = conn.execute(
            """SELECT s.*, c.name AS category_name FROM software s
               LEFT JOIN category c ON c.id = s.category_id
               WHERE s.id = ? AND s.status = 1""", (sid,),
        ).fetchone()
        if not row:
            raise HTTPException(404, "软件不存在或已下架")
        conn.execute("UPDATE software SET view_count = view_count + 1 WHERE id = ?", (sid,))
        files = conn.execute(
            "SELECT * FROM software_file WHERE software_id = ? ORDER BY sort_order, id", (sid,)
        ).fetchall()
        comments = conn.execute(
            """SELECT id, parent_id, nickname, content, is_admin, created_at FROM comment
               WHERE software_id = ? AND status = 'approved' ORDER BY created_at ASC, id ASC""",
            (sid,),
        ).fetchall()
        tags = software_tags(conn, sid)
    # 组两层楼中楼：顶层评论 + 各自的回复；回复的父级若已不可见（被驳回/删除）则降级为顶层
    by_id = {c["id"]: dict(c) for c in comments}
    roots, replies_of = [], {}
    for c in by_id.values():
        pid = c["parent_id"]
        if pid and pid in by_id:
            root_id = by_id[pid]["parent_id"] or pid  # 回复的回复统一挂到顶层
            root_id = root_id if root_id in by_id else pid
            replies_of.setdefault(root_id, []).append(c)
        else:
            roots.append(c)
    comment_tree = [{"c": c, "replies": replies_of.get(c["id"], [])} for c in roots]
    return templates.TemplateResponse(request, "detail.html", {
        "item": row, "files": files, "comment_tree": comment_tree,
        "comment_count": len(comments), "tags": tags,
        "desc_html": render_md(row["description"]),
        "allow_comment": db.get_settings().get("allow_comment") == "1",
        "msg": request.query_params.get("msg", ""),
    })


@app.get("/captcha/new")
def captcha_new():
    svg, cookie = captcha.generate()
    resp = Response(svg, media_type="image/svg+xml")
    resp.headers["Cache-Control"] = "no-store"
    resp.set_cookie(captcha.COOKIE_NAME, cookie, httponly=True, samesite="lax", max_age=captcha.TTL_SECONDS)
    return resp


@app.post("/software/{sid:int}/comment")
def comment_add(request: Request, sid: int, nickname: str = Form(""), content: str = Form(""),
                parent_id: int = Form(0), captcha_code: str = Form("")):
    if db.get_settings().get("allow_comment") != "1":
        raise HTTPException(403, "评论功能已关闭")
    if resp := maintenance_guard(request):
        return resp
    ip = client_ip(request)
    if not captcha.verify(request.cookies.get(captcha.COOKIE_NAME), captcha_code):
        return RedirectResponse(f"/software/{sid}?msg=captcha", status_code=302)
    content = content.strip()[:1000]
    if not content:
        return RedirectResponse(f"/software/{sid}?msg=empty", status_code=302)
    with db.get_db() as conn:
        if db.is_ip_blocked(conn, ip):
            raise HTTPException(403, "禁止操作")
        since = (datetime.now() - timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
        n = conn.execute(
            "SELECT COUNT(*) AS n FROM comment WHERE client_ip = ? AND created_at >= ?", (ip, since)
        ).fetchone()["n"]
        if n >= 10:
            return RedirectResponse(f"/software/{sid}?msg=limit", status_code=302)
        sw = conn.execute("SELECT id FROM software WHERE id = ? AND status = 1", (sid,)).fetchone()
        if not sw:
            raise HTTPException(404)
        parent = None
        if parent_id:
            parent = conn.execute(
                "SELECT id, parent_id FROM comment WHERE id = ? AND software_id = ? AND status = 'approved'",
                (parent_id, sid),
            ).fetchone()
            if not parent:
                return RedirectResponse(f"/software/{sid}?msg=empty", status_code=302)
        # 回复的回复一律挂到顶层评论下，保持两层结构
        root_id = (parent["parent_id"] or parent["id"]) if parent else None
        conn.execute(
            """INSERT INTO comment (software_id, parent_id, nickname, content, client_ip, status, created_at)
               VALUES (?,?,?,?,?, 'pending', ?)""",
            (sid, root_id, (nickname.strip() or "匿名网友")[:32], content, ip, db.now_str()),
        )
    resp = RedirectResponse(f"/software/{sid}?msg=pending", status_code=302)
    resp.delete_cookie(captcha.COOKIE_NAME)
    return resp


@app.get("/download-first/{sid:int}")
def download_first(request: Request, sid: int):
    with db.get_db() as conn:
        row = conn.execute(
            "SELECT id FROM software_file WHERE software_id = ? ORDER BY sort_order, id LIMIT 1", (sid,)
        ).fetchone()
    if not row:
        raise HTTPException(404, "该软件暂无可下载文件")
    return download(request, row["id"])


@app.get("/download/{fid:int}")
def download(request: Request, fid: int):
    if resp := maintenance_guard(request):
        return resp
    ip = client_ip(request)
    with db.get_db() as conn:
        if db.is_ip_blocked(conn, ip):
            raise HTTPException(403, "您的 IP 已被限制下载，请联系管理员")
        row = conn.execute(
            """SELECT f.*, s.name AS sw_name, s.status FROM software_file f
               JOIN software s ON s.id = f.software_id WHERE f.id = ?""", (fid,),
        ).fetchone()
        if not row or not row["status"]:
            raise HTTPException(404, "软件不存在或已下架")
        conn.execute("UPDATE software_file SET download_count = download_count + 1 WHERE id = ?", (fid,))
        conn.execute("UPDATE software SET download_count = download_count + 1 WHERE id = ?", (row["software_id"],))
        conn.execute(
            """INSERT INTO download_log (software_id, software_name, file_id, file_label, client_ip, user_agent, downloaded_at)
               VALUES (?,?,?,?,?,?,?)""",
            (row["software_id"], row["sw_name"], fid, row["label"], ip,
             request.headers.get("user-agent", "")[:300], db.now_str()),
        )
        if row["source_type"] == "link":
            # 外链软件包：计入统计后跳转到目标地址
            return RedirectResponse(row["file_path"], status_code=302)
        path = Path(row["file_path"])
        if not path.is_file():
            raise HTTPException(404, "文件已丢失，请联系管理员")
        # 按 IP 并发限速（租约估算）：超限的连接加 X-Accel-Limit-Rate，由 nginx 压速
        throttle = 0
        s = db.get_settings()
        if s.get("dl_limit_enabled") == "1":
            rule = conn.execute("SELECT * FROM dl_ip_rule WHERE ip = ?", (ip,)).fetchone()
            max_conns = (rule["max_conns"] if rule and rule["max_conns"] else 0) or int(s["dl_limit_conns"])
            rate = (rule["rate_limit"] if rule and rule["rate_limit"] else 0) or int(s["dl_limit_rate"])
            if dllimit.admit(conn, ip, row["label"] or row["original_name"]) > max_conns and rate > 0:
                throttle = rate
    if request.headers.get("x-accel-enabled"):
        from urllib.parse import quote
        resp = Response()
        resp.headers["X-Accel-Redirect"] = f"/files-internal/{quote(str(path))}"
        resp.headers["Content-Disposition"] = attachment_disposition(row["original_name"])
        if throttle:
            resp.headers["X-Accel-Limit-Rate"] = str(throttle)
        return resp
    # 直出模式（无 nginx）无法对单个响应限速，限速仅 docker 部署生效
    return FileResponse(path, filename=row["original_name"])


# ---------- 漏洞管理（前台） ----------

@app.get("/vulns", response_class=HTMLResponse)
def vuln_list(request: Request, severity: str = "", q: str = "", page: int = 1):
    if resp := maintenance_guard(request):
        return resp
    where, params = ["v.status = 1"], []
    if severity in SEVERITIES:
        where.append("v.severity = ?")
        params.append(severity)
    if q:
        where.append("(v.title LIKE ? OR v.cve_no LIKE ? OR v.affected_product LIKE ?)")
        params += [f"%{q}%", f"%{q}%", f"%{q}%"]
    clause = " WHERE " + " AND ".join(where)
    per_page = 15
    page = max(1, page)
    with db.get_db() as conn:
        total = conn.execute(f"SELECT COUNT(*) AS n FROM vulnerability v{clause}", params).fetchone()["n"]
        rows = conn.execute(
            f"""SELECT v.*, (SELECT COUNT(*) FROM vulnerability_file f WHERE f.vulnerability_id = v.id) AS file_count
                FROM vulnerability v{clause}
                ORDER BY v.created_at DESC, v.id DESC LIMIT {per_page} OFFSET {(page - 1) * per_page}""",
            params,
        ).fetchall()
        sev_counts = {r["severity"]: r["n"] for r in conn.execute(
            "SELECT severity, COUNT(*) AS n FROM vulnerability WHERE status = 1 GROUP BY severity"
        ).fetchall()}
    pages = max(1, (total + per_page - 1) // per_page)
    return templates.TemplateResponse(request, "vulns.html", {
        "rows": rows, "total": total, "page": page, "pages": pages,
        "cur_sev": severity, "q": q, "sev_counts": sev_counts,
    })


@app.get("/vulns/{vid:int}", response_class=HTMLResponse)
def vuln_detail(request: Request, vid: int):
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        row = conn.execute("SELECT * FROM vulnerability WHERE id = ? AND status = 1", (vid,)).fetchone()
        if not row:
            raise HTTPException(404, "漏洞信息不存在或已下线")
        conn.execute("UPDATE vulnerability SET view_count = view_count + 1 WHERE id = ?", (vid,))
        files = conn.execute(
            "SELECT * FROM vulnerability_file WHERE vulnerability_id = ? ORDER BY sort_order, id", (vid,)
        ).fetchall()
    return templates.TemplateResponse(request, "vuln_detail.html", {
        "item": row, "files": files, "desc_html": render_md(row["description"]),
    })


@app.get("/vuln-download/{fid:int}")
def vuln_download(request: Request, fid: int):
    if resp := maintenance_guard(request):
        return resp
    ip = client_ip(request)
    with db.get_db() as conn:
        if db.is_ip_blocked(conn, ip):
            raise HTTPException(403, "您的 IP 已被限制下载，请联系管理员")
        row = conn.execute(
            """SELECT f.*, v.title AS vuln_title, v.status FROM vulnerability_file f
               JOIN vulnerability v ON v.id = f.vulnerability_id WHERE f.id = ?""", (fid,),
        ).fetchone()
        if not row or row["status"] != 1:
            raise HTTPException(404, "补丁不存在或已下线")
        path = Path(row["file_path"])
        if not path.is_file():
            raise HTTPException(404, "文件已丢失，请联系管理员")
        conn.execute("UPDATE vulnerability_file SET download_count = download_count + 1 WHERE id = ?", (fid,))
        # software_id=0 表示漏洞补丁下载，便于在下载日志里统一审计
        conn.execute(
            """INSERT INTO download_log (software_id, software_name, file_id, file_label, client_ip, user_agent, downloaded_at)
               VALUES (0, ?, ?, ?, ?, ?, ?)""",
            ("[漏洞补丁] " + row["vuln_title"], fid, row["label"], ip,
             request.headers.get("user-agent", "")[:300], db.now_str()),
        )
    if request.headers.get("x-accel-enabled"):
        from urllib.parse import quote
        resp = Response()
        resp.headers["X-Accel-Redirect"] = f"/files-internal/{quote(str(path))}"
        resp.headers["Content-Disposition"] = attachment_disposition(row["original_name"])
        return resp
    return FileResponse(path, filename=row["original_name"])


# ---------- 媒体服务（文章图文 / 视频，统一 URL 契约：/media/年月/uuid.ext） ----------

IMAGE_EXTS = {".jpg", ".jpeg", ".png", ".gif", ".webp", ".bmp", ".svg"}
VIDEO_EXTS = {".mp4", ".webm", ".m4v", ".mov"}
APP_EXTS = {".html", ".htm"}
# 文章附件白名单：常见文档/压缩/电子书，禁止可执行文件
ATTACH_EXTS = {
    ".pdf", ".mhtml", ".mht", ".html", ".htm",
    ".zip", ".rar", ".7z", ".gz", ".tar", ".bz2", ".xz",
    ".doc", ".docx", ".xls", ".xlsx", ".ppt", ".pptx",
    ".txt", ".md", ".csv", ".rtf", ".odt", ".ods", ".odp",
    ".epub", ".azw3", ".mobi", ".chm", ".djvu",
    ".iso", ".dmg",
}
MEDIA_EXTS = IMAGE_EXTS | VIDEO_EXTS | APP_EXTS | ATTACH_EXTS
MEDIA_MIME = {
    ".jpg": "image/jpeg", ".jpeg": "image/jpeg", ".png": "image/png",
    ".gif": "image/gif", ".webp": "image/webp", ".bmp": "image/bmp",
    ".svg": "image/svg+xml",
    ".mp4": "video/mp4", ".m4v": "video/mp4", ".webm": "video/webm", ".mov": "video/quicktime",
    ".html": "text/html; charset=utf-8", ".htm": "text/html; charset=utf-8",
    ".pdf": "application/pdf",
    ".zip": "application/zip", ".rar": "application/vnd.rar", ".7z": "application/x-7z-compressed",
    ".gz": "application/gzip", ".tar": "application/x-tar", ".bz2": "application/x-bzip2",
    ".xz": "application/x-xz",
    ".doc": "application/msword", ".docx": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
    ".xls": "application/vnd.ms-excel", ".xlsx": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    ".ppt": "application/vnd.ms-powerpoint", ".pptx": "application/vnd.openxmlformats-officedocument.presentationml.presentation",
    ".txt": "text/plain; charset=utf-8", ".md": "text/markdown", ".csv": "text/csv",
    ".rtf": "application/rtf", ".odt": "application/vnd.oasis.opendocument.text",
    ".ods": "application/vnd.oasis.opendocument.spreadsheet", ".odp": "application/vnd.oasis.opendocument.presentation",
    ".epub": "application/epub+zip", ".azw3": "application/vnd.amazon.ebook",
    ".mobi": "application/x-mobipocket-ebook", ".chm": "application/vnd.ms-htmlhelp",
    ".djvu": "image/vnd.djvu", ".iso": "application/x-iso9660-image", ".dmg": "application/x-apple-diskimage",
    ".mhtml": "multipart/related", ".mht": "multipart/related",
}
MAX_IMAGE_SIZE = 20 * 1024 * 1024
# Starlette 1.7 起对单个 multipart part 默认限 1MB，会卡死大段 HTML 正文和大视频上传；
# 显式放宽到 2.1GB（与 nginx client_max_body_size 对齐）。文件 part 走磁盘假脱机，不占内存。
MAX_FORM_PART = 2 * 1024 * 1024 * 1024 + 1024
MEDIA_DIR = config.UPLOAD_DIR / "media"


def _media_response(request, path: Path):
    ext = path.suffix.lower()
    mime = MEDIA_MIME.get(ext, "application/octet-stream")
    download = request.query_params.get("download") == "1"
    # 附件目录下的文件一律下载，不渲染（HTML/MHTML 附件防止 XSS）
    is_attachment = "attachments" in path.parts
    # HTML 应用与 SVG 可能内嵌脚本：强制沙箱响应头（不给 allow-same-origin），
    # 即使有人直接打开资源 URL 也无法访问主站 Cookie / DOM
    # 注意：nginx X-Accel 内部直出会丢弃 CSP，这类文件必须由应用直接输出
    sandbox = (ext in APP_EXTS or ext == ".svg") and not is_attachment
    if request.headers.get("x-accel-enabled") and not sandbox:
        from urllib.parse import quote
        resp = Response(media_type=mime)
        # 与下载路由共用 nginx 内部跳转：nginx 直出并原生支持 Range（视频拖进度）
        resp.headers["X-Accel-Redirect"] = f"/files-internal/{quote(str(path))}"
        resp.headers["Content-Disposition"] = "attachment" if is_attachment else "inline"
        return resp
    disposition_type = "attachment" if (download or is_attachment) else "inline"
    resp = FileResponse(path, media_type=mime, content_disposition_type=disposition_type)
    if sandbox:
        resp.headers["Content-Security-Policy"] = (
            "sandbox allow-scripts allow-modals allow-forms allow-popups allow-downloads; "
            "default-src * data: blob: 'unsafe-inline' 'unsafe-eval'")
        resp.headers["X-Content-Type-Options"] = "nosniff"
    return resp


@app.get("/media/{rel:path}")
def media_file(request: Request, rel: str):
    # 媒体资源不走维护拦截（文章页本身已隐藏，单独资源 URL 不暴露内容列表）
    MEDIA_DIR.mkdir(parents=True, exist_ok=True)
    target = (MEDIA_DIR / rel).resolve()
    media_root = MEDIA_DIR.resolve()
    if media_root not in target.parents or not target.is_file():
        raise HTTPException(404)
    if target.suffix.lower() not in MEDIA_EXTS:
        raise HTTPException(404)
    return _media_response(request, target)


def _media_auth(request: Request, token: str = ""):
    """网页端凭 session、Typora 脚本凭上传令牌，二选一"""
    admin = current_admin(request)
    if admin:
        return admin
    token = (token or "").strip()
    if token:
        with db.get_db() as conn:
            row = conn.execute(
                "SELECT id, username, role FROM admin WHERE upload_token = ? AND disabled = 0", (token,),
            ).fetchone()
            if row:
                return dict(row)
    return None


@app.post("/admin/upload/media")
async def upload_media(request: Request):
    form = await request.form(max_part_size=MAX_FORM_PART)
    admin = _media_auth(request, form.get("token", ""))
    if not admin:
        raise HTTPException(401, "未登录或上传令牌无效")
    files = [v for k, v in form.multi_items()
             if k in ("files", "file") and hasattr(v, "filename") and v.filename]
    if not files:
        raise HTTPException(400, "没有收到文件")
    config.ensure_dirs()
    month = datetime.now().strftime("%Y%m")
    sub_dir = MEDIA_DIR / month
    sub_dir.mkdir(parents=True, exist_ok=True)
    urls = []
    for up in files:
        original = safe_filename(up.filename)
        ext = Path(original).suffix.lower()
        if ext not in MEDIA_EXTS:
            raise HTTPException(400, f"不支持的媒体类型：{ext}（仅支持 jpg/png/gif/webp 图片与 mp4/webm 视频）")
        is_image = ext in IMAGE_EXTS
        limit = MAX_IMAGE_SIZE if is_image else config.MAX_UPLOAD_SIZE
        dest = sub_dir / f"{uuid.uuid4().hex}{ext}"
        size = 0
        with open(dest, "wb") as f:
            while chunk := up.file.read(1024 * 1024):
                size += len(chunk)
                if size > limit:
                    f.close()
                    dest.unlink(missing_ok=True)
                    raise HTTPException(400, "图片不能超过 20MB" if is_image else "视频超过上传大小限制")
                f.write(chunk)
        urls.append(f"/media/{month}/{dest.name}")
    with db.get_db() as conn:
        db.log_action(conn, admin["username"], "上传媒体", f"{len(urls)} 个文件", client_ip(request))
    # Typora 自定义命令只读取 stdout 最后 N 行（N=图片数），每行列一个 URL
    if request.query_params.get("format") == "text":
        return PlainTextResponse("\n".join(urls))
    return {"ok": True, "urls": urls}


DOC_EXTS = {".html", ".htm", ".mhtml", ".mht"}


@app.post("/admin/upload/attachment")
async def upload_attachment(request: Request, admin=Depends(require_admin)):
    """文章附件上传：白名单类型，保存到 /media/attachments/，返回 {url, name, size}"""
    form = await request.form(max_part_size=MAX_FORM_PART)
    ups = form.getlist("files")
    if not ups:
        single = form.get("file")
        ups = [single] if single is not None else []
    ups = [u for u in ups if hasattr(u, "filename") and u.filename]
    if not ups:
        raise HTTPException(400, "没有收到文件")
    urls = []
    month = db.now_str()[:7].replace("-", "")
    dest_dir = MEDIA_DIR / "attachments" / month
    dest_dir.mkdir(parents=True, exist_ok=True)
    for up in ups:
        original = safe_filename(up.filename)
        ext = Path(original).suffix.lower()
        if ext not in ATTACH_EXTS:
            raise HTTPException(400, f"附件类型 {ext or '未知'} 不在允许范围内")
        dest = dest_dir / f"{uuid.uuid4().hex}{ext}"
        size = 0
        with open(dest, "wb") as f:
            while chunk := up.file.read(1024 * 1024):
                size += len(chunk)
                f.write(chunk)
                if size > 2 * 1024 * 1024 * 1024:
                    f.close()
                    dest.unlink(missing_ok=True)
                    raise HTTPException(400, "附件不能超过 2GB")
        url = f"/media/attachments/{month}/{dest.name}"
        urls.append({"url": url, "name": original, "size": size})
    with db.get_db() as conn:
        db.log_action(conn, admin["username"], "上传文章附件", f"{len(urls)} 个", client_ip(request))
    return {"ok": True, "files": urls}


@app.post("/admin/import/article-doc")
async def import_article_doc(request: Request, admin=Depends(require_admin)):
    """技术文章编辑框「导入 HTML/MHTML」。
    mode=doc 解析为清洗后的静态图文片段；mode=app 原样保存为沙箱网页应用。
    支持多文件：用 files= 字段一次传多个，返回 results 数组，可一次性建立合集。"""
    form = await request.form(max_part_size=MAX_FORM_PART)
    mode = form.get("mode", "doc")
    if mode not in ("doc", "app"):
        raise HTTPException(400, "mode 参数无效")
    # 兼容单文件(file=)和多文件(files=)
    ups = form.getlist("files")
    if not ups:
        single = form.get("file")
        ups = [single] if single is not None else []
    ups = [u for u in ups if hasattr(u, "filename") and u.filename]
    if not ups:
        raise HTTPException(400, "没有收到文件")
    config.ensure_dirs()
    # 文档导入上限可在「站点设置」调整，默认 100MB
    doc_max_mb = int(db.get_settings().get("doc_max_mb") or 100)
    doc_max_bytes = doc_max_mb * 1024 * 1024
    results = []
    for up in ups:
        original = safe_filename(up.filename)
        ext = Path(original).suffix.lower()
        if ext not in DOC_EXTS:
            results.append({"ok": False, "filename": up.filename, "error": "仅支持 .html/.htm/.mhtml/.mht"})
            continue
        if mode == "app" and ext not in APP_EXTS:
            results.append({"ok": False, "filename": up.filename, "error": "网页应用模式仅支持单个 .html"})
            continue
        data = b""
        while chunk := up.file.read(1024 * 1024):
            data += chunk
            if len(data) > doc_max_bytes:
                break
        if len(data) > doc_max_bytes:
            results.append({"ok": False, "filename": up.filename, "error": f"文档不能超过 {doc_max_mb}MB"})
            continue
        try:
            if mode == "doc":
                # 解析可能含外链图片兜底下载（每张最多 10s），放线程池避免阻塞事件循环
                r = await run_in_threadpool(docimport.import_document, original, data)
            else:
                r = await run_in_threadpool(
                    lambda: {"title": docimport.html_title(data),
                             "app_file_url": docimport.store_app_file(original, data)})
            r["ok"] = True
            r["filename"] = up.filename
            results.append(r)
        except ValueError as e:
            results.append({"ok": False, "filename": up.filename, "error": str(e)})
        except Exception as e:
            results.append({"ok": False, "filename": up.filename, "error": f"解析失败：{e}"})
    with db.get_db() as conn:
        db.log_action(conn, admin["username"], "导入文章文档",
                      f"{len(results)} 个文件（{'网页应用' if mode == 'app' else '文档解析'}）", client_ip(request))
    return {"ok": True, "mode": mode, "results": results}


TYPORA_BAT = '''@echo off
chcp 65001 >nul
setlocal enabledelayedexpansion
REM ===== 软件站 Typora 图片上传脚本（自动生成，请勿外传）=====
set "SITE_URL=__SITE_URL__"
set "TOKEN=__TOKEN__"
set "ARGS="
:loop
if "%~1"=="" goto run
set "ARGS=!ARGS! -F "files=@%~1""
shift
goto loop
:run
REM Typora 要求返回的地址以 http(s):// 开头，故把每行相对地址补上站点前缀
curl.exe -s !ARGS! -F "token=!TOKEN!" "!SITE_URL!/admin/upload/media?format=text" > "%TEMP%\\typora_up.txt"
for /f "usebackq delims=" %%u in ("%TEMP%\\typora_up.txt") do @echo !SITE_URL!%%u
del "%TEMP%\\typora_up.txt" >nul 2>&1
endlocal
'''

# Linux / macOS 版（Typora 把图片路径作为位置参数追加，stdout 每行一个完整 http(s) URL）
TYPORA_SH = '''#!/usr/bin/env bash
# ===== 软件站 Typora 图片上传脚本（自动生成，请勿外传）=====
set -euo pipefail
SITE_URL="__SITE_URL__"
TOKEN="__TOKEN__"
args=()
for f in "$@"; do
  args+=(-F "files=@$f")
done
# Typora 要求返回的地址以 http(s):// 开头，故把每行相对地址补上站点前缀
# awk 的 print 保证每行（含最后一行）都有换行符，避免末行被吞
curl -fsS "${args[@]}" -F "token=$TOKEN" "$SITE_URL/admin/upload/media?format=text" \\
  | awk -v base="$SITE_URL" '{ print base $0 }'
'''


def _site_base_url(request: Request) -> str:
    """生成对外可访问的站点根地址：优先用反代透传的主机名（含端口），
    保证局域网其他电脑下载的 Typora 脚本里地址可用。"""
    host = request.headers.get("x-forwarded-host") or request.headers.get("host")
    if host:
        proto = request.headers.get("x-forwarded-proto") or ("https" if request.url.scheme == "https" else "http")
        return f"{proto}://{host}".rstrip("/")
    return str(request.base_url).rstrip("/")


def _ensure_upload_token(admin) -> str:
    """取该账号的上传令牌；首次访问时自动签发（投稿员同样可用）"""
    import secrets
    with db.get_db() as conn:
        row = conn.execute("SELECT upload_token FROM admin WHERE id = ?", (admin["id"],)).fetchone()
        token = (row["upload_token"] if row else "") or ""
        if not token:
            token = secrets.token_hex(20)
            conn.execute("UPDATE admin SET upload_token = ? WHERE id = ?", (token, admin["id"]))
        return token


@app.get("/admin/upload-token", response_class=HTMLResponse)
def upload_token_page(request: Request, admin=Depends(require_admin)):
    token = _ensure_upload_token(admin)
    site_url = _site_base_url(request)
    bat = TYPORA_BAT.replace("__SITE_URL__", site_url).replace("__TOKEN__", token or "")
    sh = TYPORA_SH.replace("__SITE_URL__", site_url).replace("__TOKEN__", token or "")
    return templates.TemplateResponse(request, "admin_upload_token.html", {
        "admin": admin, "token": token, "site_url": site_url, "bat": bat, "sh": sh,
    })


@app.get("/admin/upload-token/typora.bat")
def upload_token_bat(request: Request, admin=Depends(require_admin)):
    token = _ensure_upload_token(admin)
    site_url = _site_base_url(request)
    bat = TYPORA_BAT.replace("__SITE_URL__", site_url).replace("__TOKEN__", token or "")
    return PlainTextResponse(bat, media_type="application/octet-stream",
                             headers={"Content-Disposition": 'attachment; filename="typora_upload.bat"'})


@app.get("/admin/upload-token/typora.sh")
def upload_token_sh(request: Request, admin=Depends(require_admin)):
    token = _ensure_upload_token(admin)
    site_url = _site_base_url(request)
    sh = TYPORA_SH.replace("__SITE_URL__", site_url).replace("__TOKEN__", token or "")
    return PlainTextResponse(sh, media_type="text/x-shellscript; charset=utf-8",
                             headers={"Content-Disposition": 'attachment; filename="typora_upload.sh"'})


@app.post("/admin/upload-token/rotate")
def upload_token_rotate(request: Request, admin=Depends(require_admin)):
    import secrets
    with db.get_db() as conn:
        conn.execute("UPDATE admin SET upload_token = ? WHERE id = ?", (secrets.token_hex(20), admin["id"]))
        db.log_action(conn, admin["username"], "重置上传令牌", "Typora/媒体上传", client_ip(request))
    return RedirectResponse("/admin/upload-token", status_code=302)


# ---------- 网址导航（前台） ----------

@app.get("/nav", response_class=HTMLResponse)
def nav_page(request: Request):
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM nav_category WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        links = conn.execute(
            "SELECT * FROM nav_link WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
    # 按分类分组，空分类不在前台展示
    groups = [(c, [l for l in links if l["category_id"] == c["id"]]) for c in cats]
    groups = [(c, ls) for c, ls in groups if ls]
    return templates.TemplateResponse(request, "nav.html", {"groups": groups})


# ---------- 技术文章（前台） ----------

@app.get("/articles", response_class=HTMLResponse)
def article_list(request: Request, q: str = "", page: int = 1):
    if resp := maintenance_guard(request):
        return resp
    where, params = ["status = 1"], []
    if q:
        where.append("(title LIKE ? OR summary LIKE ?)")
        params += [f"%{q}%", f"%{q}%"]
    clause = " WHERE " + " AND ".join(where)
    per_page = 12
    page = max(1, page)
    with db.get_db() as conn:
        total = conn.execute(f"SELECT COUNT(*) AS n FROM article{clause}", params).fetchone()["n"]
        rows = conn.execute(
            f"SELECT * FROM article{clause} ORDER BY created_at DESC, id DESC "
            f"LIMIT {per_page} OFFSET {(page - 1) * per_page}",
            params,
        ).fetchall()
    pages = max(1, (total + per_page - 1) // per_page)
    return templates.TemplateResponse(request, "articles.html", {
        "rows": rows, "total": total, "page": page, "pages": pages, "q": q,
    })


@app.get("/articles/{aid:int}", response_class=HTMLResponse)
def article_detail(request: Request, aid: int):
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        row = conn.execute("SELECT * FROM article WHERE id = ? AND status = 1", (aid,)).fetchone()
        if not row:
            raise HTTPException(404, "文章不存在或已下线")
        conn.execute("UPDATE article SET view_count = view_count + 1 WHERE id = ?", (aid,))
        parts = conn.execute(
            "SELECT * FROM article_part WHERE article_id = ? ORDER BY sort_order", (aid,)
        ).fetchall()
        attachments = conn.execute(
            "SELECT * FROM article_attachment WHERE article_id = ? ORDER BY sort_order", (aid,)
        ).fetchall()
        more = conn.execute(
            "SELECT id, title, cover_url, created_at FROM article WHERE status = 1 AND id <> ? "
            "ORDER BY created_at DESC, id DESC LIMIT 8",
            (aid,),
        ).fetchall()
    ctype = row["content_type"] if "content_type" in row.keys() else "md"
    if ctype == "html":
        body_html = Markup(row["content"])
    elif ctype == "collection":
        # 合集：正文（前言）+ 各子篇章，子篇章 HTML 已在导入时白名单清洗
        body_html = Markup(row["content"]) if row["content"] else Markup("")
    elif ctype == "app":
        body_html = render_md(row["content"])
    else:
        body_html = render_md(row["content"])
    return templates.TemplateResponse(request, "article_detail.html", {
        "item": row, "more": more, "content_html": body_html, "content_type": ctype,
        "parts": parts, "attachments": attachments,
    })


# ---------- 管理端 ----------

@app.get("/admin/login", response_class=HTMLResponse)
def login_page(request: Request):
    admin = current_admin(request)
    if admin:
        return RedirectResponse(home_for(admin), status_code=302)
    return templates.TemplateResponse(request, "login.html", {"error": ""})


@app.post("/admin/login")
def login(request: Request, username: str = Form(...), password: str = Form(...)):
    admin = auth.authenticate(username, password)
    if not admin:
        return templates.TemplateResponse(request, "login.html", {"error": "用户名或密码错误（或账号已停用）"}, status_code=401)
    sid = auth.create_session(admin["id"])
    with db.get_db() as conn:
        db.log_action(conn, admin["username"], "登录", "登录管理后台", client_ip(request))
    resp = RedirectResponse(home_for(admin), status_code=302)
    resp.set_cookie(SESSION_COOKIE, sid, httponly=True, samesite="lax", max_age=86400)
    return resp


@app.get("/admin/logout")
def logout(request: Request):
    sid = request.cookies.get(SESSION_COOKIE)
    if sid:
        auth.destroy_session(sid)
    resp = RedirectResponse("/", status_code=302)
    resp.delete_cookie(SESSION_COOKIE)
    return resp


@app.get("/admin", response_class=HTMLResponse)
def admin_dashboard(request: Request, admin=Depends(require_full_admin)):
    today = db.now_str()[:10]
    with db.get_db() as conn:
        stats = conn.execute("""
            SELECT (SELECT COUNT(*) FROM software) AS total_sw,
                   (SELECT COUNT(*) FROM software WHERE status = 1) AS online_sw,
                   (SELECT COALESCE(SUM(download_count),0) FROM software) AS total_dl,
                   (SELECT COUNT(*) FROM download_log WHERE downloaded_at LIKE ?) AS today_dl,
                   (SELECT COUNT(DISTINCT client_ip) FROM download_log) AS total_ips,
                   (SELECT COUNT(*) FROM comment WHERE status = 'pending') AS pending_comments,
                   (SELECT COUNT(*) FROM software WHERE status = 2) AS pending_sw,
                   (SELECT COUNT(*) FROM vulnerability WHERE status = 2) AS pending_vulns,
                   (SELECT COUNT(*) FROM article WHERE status = 2) AS pending_arts
        """, (today + "%",)).fetchone()
        top = conn.execute(
            "SELECT id, name, download_count, view_count FROM software ORDER BY download_count DESC LIMIT 10"
        ).fetchall()
        recent = conn.execute(
            """SELECT software_name, file_label, client_ip, downloaded_at FROM download_log
               ORDER BY downloaded_at DESC, id DESC LIMIT 15"""
        ).fetchall()
        trend = []
        for i in range(6, -1, -1):
            day = (datetime.now() - timedelta(days=i)).strftime("%Y-%m-%d")
            n = conn.execute(
                "SELECT COUNT(*) AS n FROM download_log WHERE downloaded_at LIKE ?", (day + "%",)
            ).fetchone()["n"]
            trend.append({"day": day[5:], "n": n})
        hot = conn.execute(
            """SELECT keyword, COUNT(*) AS c FROM search_log GROUP BY keyword
               ORDER BY c DESC LIMIT 8"""
        ).fetchall()
    return templates.TemplateResponse(request, "admin_dashboard.html", {
        "admin": admin, "stats": stats, "top": top, "recent": recent,
        "trend": trend, "hot": hot, "max_trend": max([t["n"] for t in trend] + [1]),
    })


@app.get("/admin/software", response_class=HTMLResponse)
def admin_software_list(request: Request, admin=Depends(require_full_admin), q: str = ""):
    with db.get_db() as conn:
        sql = """SELECT s.*, c.name AS category_name,
                        (SELECT COUNT(*) FROM software_file f WHERE f.software_id = s.id) AS file_count
                 FROM software s LEFT JOIN category c ON c.id = s.category_id"""
        params: list = []
        if q:
            sql += " WHERE s.name LIKE ?"
            params.append(f"%{q}%")
        sql += " ORDER BY s.pin_weight DESC, s.created_at DESC"
        rows = conn.execute(sql, params).fetchall()
    return templates.TemplateResponse(request, "admin_software.html", {
        "admin": admin, "items": rows, "q": q,
    })


@app.get("/admin/software/export")
def admin_software_export(admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        rows = conn.execute(
            """SELECT s.*, c.name AS category_name,
               (SELECT COUNT(*) FROM software_file f WHERE f.software_id = s.id) AS file_count
               FROM software s LEFT JOIN category c ON c.id = s.category_id ORDER BY s.id"""
        ).fetchall()
        tags = {r["id"]: software_tags(conn, r["id"]) for r in rows}
    buf = io.StringIO()
    buf.write("\ufeff")
    w = csv.writer(buf)
    w.writerow(["ID", "名称", "版本", "分类", "标签", "备注", "文件数", "下载量", "浏览量", "状态", "添加时间"])
    for r in rows:
        w.writerow([r["id"], r["name"], r["version"], r["category_name"] or "",
                    " ".join(tags[r["id"]]), r["remark"], r["file_count"], r["download_count"],
                    r["view_count"], "上架" if r["status"] else "下架", r["created_at"]])
    return Response(buf.getvalue().encode("utf-8"), media_type="text/csv",
                    headers={"Content-Disposition": "attachment; filename=software_list.csv"})


@app.post("/admin/software/{sid:int}/pin")
def admin_pin(sid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT pin_weight, name FROM software WHERE id = ?", (sid,)).fetchone()
        if not row:
            raise HTTPException(404)
        if row["pin_weight"]:
            new = 0
            action = "取消置顶"
        else:
            new = (conn.execute("SELECT COALESCE(MAX(pin_weight),0) AS m FROM software").fetchone()["m"]) + 1
            action = "置顶"
        conn.execute("UPDATE software SET pin_weight = ? WHERE id = ?", (new, sid))
        db.log_action(conn, admin["username"], action, row["name"], client_ip(request))
    return RedirectResponse("/admin/software", status_code=302)


@app.post("/admin/software/{sid:int}/recommend")
def admin_recommend(sid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT recommended, name FROM software WHERE id = ?", (sid,)).fetchone()
        if not row:
            raise HTTPException(404)
        conn.execute("UPDATE software SET recommended = ? WHERE id = ?", (0 if row["recommended"] else 1, sid))
        db.log_action(conn, admin["username"], "取消推荐" if row["recommended"] else "设为推荐",
                      row["name"], client_ip(request))
    return RedirectResponse("/admin/software", status_code=302)


def _form_uploads(form):
    """浏览器会整体跳过未选择文件的 input，只能靠 files_<行号> 显式对齐每一行"""
    out = {}
    for key, val in form.multi_items():
        if key.startswith("files_") and hasattr(val, "filename") and val.filename:
            try:
                out[int(key[len("files_"):])] = val
            except ValueError:
                pass
    return out


def _collect_files(file_labels, file_paths, file_links, uploads):
    """按行收集软件包：每行三选一——服务器路径 / 下载链接（外链跳转）/ 上传文件。
    浏览器对留空文本框也会提交空值，labels/paths/links 与行号一一对应；
    文件框未选择时整体缺名，靠 files_<行号> 对齐。"""
    out = []
    labels = file_labels or []
    paths = file_paths or []
    links = file_links or []
    uploads = uploads or {}
    row_count = max(len(labels), len(paths), len(links),
                    (max(uploads) + 1) if uploads else 0)
    for i in range(row_count):
        label = labels[i].strip() if i < len(labels) else ""
        path_str = paths[i].strip() if i < len(paths) else ""
        link = links[i].strip() if i < len(links) else ""
        up = uploads.get(i)
        if path_str:
            p = Path(path_str).expanduser()
            if not p.is_file():
                raise ValueError(f"第 {i + 1} 个文件的服务器路径不存在：{path_str}")
            out.append((label or "安装包", p.name, str(p.resolve()), p.stat().st_size, "path"))
        elif link:
            try:
                link = normalize_http_link(link, 255)
            except ValueError as e:
                raise ValueError(f"第 {i + 1} 行的下载链接{e}")
            out.append((label or "外链", link[:255], link, 0, "link"))
        elif up is not None:
            original = safe_filename(up.filename)
            if Path(original).suffix.lower() not in config.ALLOWED_EXTENSIONS:
                raise ValueError(f"不允许上传该类型文件（{Path(original).suffix}）")
            config.ensure_dirs()
            dest = config.UPLOAD_DIR / f"{uuid.uuid4().hex}{Path(original).suffix}"
            size = 0
            with open(dest, "wb") as f:
                while chunk := up.file.read(1024 * 1024):
                    size += len(chunk)
                    if size > config.MAX_UPLOAD_SIZE:
                        f.close()
                        dest.unlink(missing_ok=True)
                        raise ValueError("文件超过上传大小限制")
                    f.write(chunk)
            out.append((label or "安装包", original, str(dest), size, "upload"))
    return out


@app.get("/admin/software/add", response_class=HTMLResponse)
def admin_add_page(request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
    return _sw_form_ctx(request, admin, None, [], cats)


@app.post("/admin/software/add")
async def admin_add(request: Request, admin=Depends(require_full_admin),
                    name: str = Form(...), version: str = Form(""),
                    category_id: int = Form(0), description: str = Form(""),
                    remark: str = Form(""), tags: str = Form(""),
                    recommended: int = Form(0),
                    file_labels: List[str] = Form([]), file_paths: List[str] = Form([]),
                    file_links: List[str] = Form([])):
    with db.get_db() as conn:
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
    try:
        form = await request.form(max_part_size=MAX_FORM_PART)
        collected = _collect_files(file_labels, file_paths, file_links, _form_uploads(form))
        if not collected:
            raise ValueError("请至少添加一个软件包（上传或填写服务器路径）")
        now = db.now_str()
        with db.get_db() as conn:
            sw_id = conn.insert_returning_id(
                """INSERT INTO software (name, version, category_id, description, remark,
                   recommended, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?)""",
                (name.strip(), version.strip(), category_id or None, description,
                 remark.strip()[:255], 1 if recommended else 0, now, now),
            )
            for i, (label, original, stored, size, stype) in enumerate(collected):
                conn.execute(
                    """INSERT INTO software_file (software_id, label, original_name, file_path,
                       file_size, source_type, sort_order, created_at) VALUES (?,?,?,?,?,?,?,?)""",
                    (sw_id, label, original, stored, size, stype, i, now),
                )
            sync_tags(conn, sw_id, parse_tags(tags))
            db.log_action(conn, admin["username"], "添加软件", f"{name}（{len(collected)} 个文件）", client_ip(request))
        return RedirectResponse("/admin/software", status_code=302)
    except ValueError as e:
        ctx = _sw_form_ctx(request, admin, None, [], cats, str(e), tags, False,
                           {"name": name, "version": version, "category_id": category_id,
                            "description": description, "remark": remark, "recommended": recommended})
        ctx.status_code = 400
        return ctx


@app.get("/admin/software/{sid:int}/edit", response_class=HTMLResponse)
def admin_edit_page(request: Request, sid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM software WHERE id = ?", (sid,)).fetchone()
        if not item:
            raise HTTPException(404)
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
        files = conn.execute("SELECT * FROM software_file WHERE software_id = ? ORDER BY sort_order, id", (sid,)).fetchall()
        tags_text = "，".join(software_tags(conn, sid))
    return _sw_form_ctx(request, admin, item, files, cats, "", tags_text)


@app.post("/admin/software/{sid:int}/edit")
async def admin_edit(request: Request, sid: int, admin=Depends(require_full_admin),
                     name: str = Form(...), version: str = Form(""),
                     category_id: int = Form(0), description: str = Form(""),
                     remark: str = Form(""), tags: str = Form(""),
                     recommended: int = Form(0), status: int = Form(0),
                     existing_ids: List[int] = Form([]), existing_labels: List[str] = Form([]),
                     delete_ids: List[int] = Form([]),
                     file_labels: List[str] = Form([]), file_paths: List[str] = Form([]),
                     file_links: List[str] = Form([])):
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM software WHERE id = ?", (sid,)).fetchone()
        if not item:
            raise HTTPException(404)
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
        files = conn.execute("SELECT * FROM software_file WHERE software_id = ? ORDER BY sort_order, id", (sid,)).fetchall()
    try:
        form = await request.form(max_part_size=MAX_FORM_PART)
        collected = _collect_files(file_labels, file_paths, file_links, _form_uploads(form))
        now = db.now_str()
        with db.get_db() as conn:
            for del_id in delete_ids:
                row = conn.execute("SELECT * FROM software_file WHERE id = ? AND software_id = ?", (del_id, sid)).fetchone()
                if row:
                    conn.execute("DELETE FROM software_file WHERE id = ?", (del_id,))
                    if row["source_type"] == "upload":
                        p = Path(row["file_path"])
                        if p.is_file() and p.parent == config.UPLOAD_DIR:
                            p.unlink()
            label_map = dict(zip(existing_ids, existing_labels))
            for eid in existing_ids:
                if eid not in delete_ids:
                    conn.execute("UPDATE software_file SET label = ? WHERE id = ? AND software_id = ?",
                                 (label_map.get(eid, "安装包").strip() or "安装包", eid, sid))
            remain = conn.execute("SELECT COALESCE(MAX(sort_order),-1) AS m FROM software_file WHERE software_id = ?", (sid,)).fetchone()["m"]
            if not collected and conn.execute("SELECT COUNT(*) AS n FROM software_file WHERE software_id = ?", (sid,)).fetchone()["n"] == 0:
                raise ValueError("软件至少需要保留一个文件")
            for i, (label, original, stored, size, stype) in enumerate(collected):
                conn.execute(
                    """INSERT INTO software_file (software_id, label, original_name, file_path,
                       file_size, source_type, sort_order, created_at) VALUES (?,?,?,?,?,?,?,?)""",
                    (sid, label, original, stored, size, stype, remain + 1 + i, now),
                )
            conn.execute(
                """UPDATE software SET name=?, version=?, category_id=?, description=?, remark=?,
                   recommended=?, status=?, updated_at=? WHERE id=?""",
                (name.strip(), version.strip(), category_id or None, description,
                 remark.strip()[:255], 1 if recommended else 0, 1 if status else 0, now, sid),
            )
            sync_tags(conn, sid, parse_tags(tags))
            db.log_action(conn, admin["username"], "编辑软件", name, client_ip(request))
        return RedirectResponse("/admin/software", status_code=302)
    except ValueError as e:
        ctx = _sw_form_ctx(request, admin, item, files, cats, str(e), tags)
        ctx.status_code = 400
        return ctx


@app.post("/admin/software/{sid:int}/delete")
def admin_delete(sid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM software WHERE id = ?", (sid,)).fetchone()
        if item:
            up_files = conn.execute(
                "SELECT file_path FROM software_file WHERE software_id = ? AND source_type = 'upload'", (sid,)
            ).fetchall()
            conn.execute("DELETE FROM software WHERE id = ?", (sid,))
            for r in up_files:
                p = Path(r["file_path"])
                if p.is_file() and p.parent == config.UPLOAD_DIR:
                    p.unlink()
            db.log_action(conn, admin["username"], "删除软件", item["name"], client_ip(request))
    return RedirectResponse("/admin/software", status_code=302)


@app.get("/admin/category", response_class=HTMLResponse)
def admin_category(request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = conn.execute(
            """SELECT c.*, (SELECT COUNT(*) FROM software s WHERE s.category_id = c.id) AS sw_count
               FROM category c ORDER BY c.sort_order, c.id"""
        ).fetchall()
    return templates.TemplateResponse(request, "admin_category.html", {
        "admin": admin, "cats": cats,
    })


@app.post("/admin/category/add")
def admin_category_add(request: Request, name: str = Form(...), sort_order: int = Form(0),
                       icon: str = Form("box"), color: str = Form("#4f6ef7"),
                       admin=Depends(require_full_admin)):
    if name.strip():
        with db.get_db() as conn:
            conn.insert_ignore(
                "INSERT INTO category (name, sort_order, visible, icon, color, created_at) VALUES (?,?,1,?,?,?)",
                (name.strip(), sort_order, icon if icon in CATEGORY_ICONS else "box", color, db.now_str()),
            )
            db.log_action(conn, admin["username"], "新增分类", name, client_ip(request))
    return RedirectResponse("/admin/category", status_code=302)


@app.post("/admin/category/{cid:int}/update")
def admin_category_update(request: Request, cid: int, name: str = Form(...),
                          icon: str = Form("box"), color: str = Form("#4f6ef7"),
                          visible: int = Form(1), admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        conn.execute(
            "UPDATE category SET name=?, icon=?, color=?, visible=? WHERE id=?",
            (name.strip(), icon if icon in CATEGORY_ICONS else "box", color, 1 if visible else 0, cid),
        )
        db.log_action(conn, admin["username"], "编辑分类", name, client_ip(request))
    return RedirectResponse("/admin/category", status_code=302)


@app.post("/admin/category/{cid:int}/move")
def admin_category_move(request: Request, cid: int, direction: str = Form(...),
                        admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = conn.execute("SELECT id, sort_order FROM category ORDER BY sort_order, id").fetchall()
        idx = next((i for i, c in enumerate(cats) if c["id"] == cid), None)
        if idx is None:
            raise HTTPException(404)
        swap = idx + (-1 if direction == "up" else 1)
        if 0 <= swap < len(cats):
            a, b = cats[idx], cats[swap]
            conn.execute("UPDATE category SET sort_order=? WHERE id=?", (b["sort_order"], a["id"]))
            conn.execute("UPDATE category SET sort_order=? WHERE id=?", (a["sort_order"], b["id"]))
    return RedirectResponse("/admin/category", status_code=302)


@app.post("/admin/category/{cid:int}/delete")
def admin_category_delete(cid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        n = conn.execute("SELECT COUNT(*) AS n FROM software WHERE category_id = ?", (cid,)).fetchone()["n"]
        if n:
            raise HTTPException(400, "分类下仍有软件，不能删除")
        conn.execute("DELETE FROM category WHERE id = ?", (cid,))
        db.log_action(conn, admin["username"], "删除分类", f"id={cid}", client_ip(request))
    return RedirectResponse("/admin/category", status_code=302)


# ---------- 网址导航管理 ----------

def _nav_error_redirect(e):
    from urllib.parse import quote
    return RedirectResponse("/admin/nav?error=" + quote(str(e)), status_code=302)


def _nav_categories(conn):
    return conn.execute("SELECT * FROM nav_category ORDER BY sort_order, id").fetchall()


@app.get("/admin/nav", response_class=HTMLResponse)
def admin_nav(request: Request, error: str = "", admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = _nav_categories(conn)
        links = conn.execute(
            "SELECT * FROM nav_link ORDER BY category_id, sort_order, id"
        ).fetchall()
        counts = {c["id"]: 0 for c in cats}
        for l in links:
            if l["category_id"] in counts:
                counts[l["category_id"]] += 1
    return templates.TemplateResponse(request, "admin_nav.html", {
        "admin": admin, "cats": cats, "links": links, "counts": counts, "error": error,
    })


def _valid_nav_url(url: str) -> str:
    return normalize_http_link(url, 500)


def _nav_cat_exists(conn, cid: int):
    return conn.execute("SELECT 1 FROM nav_category WHERE id=?", (cid,)).fetchone() is not None


# ===== 分类（板块） =====

@app.post("/admin/nav/category/add")
def admin_nav_cat_add(request: Request, name: str = Form(...),
                      sort_order: int = Form(0), admin=Depends(require_full_admin)):
    try:
        name = name.strip()[:60]
        if not name:
            raise ValueError("分类名称不能为空")
        with db.get_db() as conn:
            conn.execute(
                "INSERT INTO nav_category (name, sort_order, visible, created_at) VALUES (?,?,1,?)",
                (name, sort_order, db.now_str()),
            )
            db.log_action(conn, admin["username"], "新增导航分类", name, client_ip(request))
    except ValueError as e:
        return _nav_error_redirect(e)
    return RedirectResponse("/admin/nav", status_code=302)


@app.post("/admin/nav/category/{cid:int}/update")
def admin_nav_cat_update(request: Request, cid: int, name: str = Form(...),
                         visible: int = Form(1), admin=Depends(require_full_admin)):
    try:
        name = name.strip()[:60]
        if not name:
            raise ValueError("分类名称不能为空")
        with db.get_db() as conn:
            conn.execute("UPDATE nav_category SET name=?, visible=? WHERE id=?",
                         (name, 1 if visible else 0, cid))
            db.log_action(conn, admin["username"], "编辑导航分类", name, client_ip(request))
    except ValueError as e:
        return _nav_error_redirect(e)
    return RedirectResponse("/admin/nav", status_code=302)


def _move_rows(conn, table, id_value, direction, where_sql="", params=()):
    """在一个有序集合内上移/下移一项。
    sort_order 可能全部相同（默认 0），仅交换两个值会无效，
    所以交换位置后按新顺序把 sort_order 整体重写为 10 的倍数。"""
    rows = conn.execute(
        f"SELECT id FROM {table} {where_sql} ORDER BY sort_order, id", params
    ).fetchall()
    ids = [r["id"] for r in rows]
    idx = next((i for i, v in enumerate(ids) if v == id_value), None)
    if idx is None:
        return
    swap = idx + (-1 if direction == "up" else 1)
    if not (0 <= swap < len(ids)):
        return
    ids[idx], ids[swap] = ids[swap], ids[idx]
    for i, rid in enumerate(ids):
        conn.execute(f"UPDATE {table} SET sort_order=? WHERE id=?", (i * 10, rid))


@app.post("/admin/nav/category/{cid:int}/move")
def admin_nav_cat_move(cid: int, direction: str = Form(...), admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        _move_rows(conn, "nav_category", cid, direction)
    return RedirectResponse("/admin/nav", status_code=302)


@app.post("/admin/nav/category/{cid:int}/delete")
def admin_nav_cat_delete(cid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT name FROM nav_category WHERE id=?", (cid,)).fetchone()
        n = conn.execute("SELECT COUNT(*) AS n FROM nav_link WHERE category_id=?", (cid,)).fetchone()["n"]
        if n:
            return _nav_error_redirect(f"分类【{row['name'] if row else cid}】下还有 {n} 个网址，请先移动或删除后再删除分类")
        conn.execute("DELETE FROM nav_category WHERE id=?", (cid,))
        if row:
            db.log_action(conn, admin["username"], "删除导航分类", row["name"], client_ip(request))
    return RedirectResponse("/admin/nav", status_code=302)


# ===== 网址 =====

@app.post("/admin/nav/add")
def admin_nav_add(request: Request, name: str = Form(...), url: str = Form(...),
                  category_id: int = Form(...), sort_order: int = Form(0),
                  admin=Depends(require_full_admin)):
    try:
        name, url = name.strip()[:120], _valid_nav_url(url)
        if not name:
            raise ValueError("网站名称不能为空")
        with db.get_db() as conn:
            if not _nav_cat_exists(conn, category_id):
                raise ValueError("请选择有效的分类板块")
            conn.execute(
                "INSERT INTO nav_link (category_id, name, url, sort_order, visible, created_at) "
                "VALUES (?,?,?,?,1,?)",
                (category_id, name, url, sort_order, db.now_str()),
            )
            db.log_action(conn, admin["username"], "新增网址", name, client_ip(request))
    except ValueError as e:
        return _nav_error_redirect(e)
    return RedirectResponse("/admin/nav", status_code=302)


@app.post("/admin/nav/{nid:int}/update")
def admin_nav_update(request: Request, nid: int, name: str = Form(...), url: str = Form(...),
                     category_id: int = Form(...), visible: int = Form(1),
                     admin=Depends(require_full_admin)):
    try:
        name, url = name.strip()[:120], _valid_nav_url(url)
        if not name:
            raise ValueError("网站名称不能为空")
        with db.get_db() as conn:
            if not _nav_cat_exists(conn, category_id):
                raise ValueError("请选择有效的分类板块")
            conn.execute("UPDATE nav_link SET category_id=?, name=?, url=?, visible=? WHERE id=?",
                         (category_id, name, url, 1 if visible else 0, nid))
            db.log_action(conn, admin["username"], "编辑网址", name, client_ip(request))
    except ValueError as e:
        return _nav_error_redirect(e)
    return RedirectResponse("/admin/nav", status_code=302)


@app.post("/admin/nav/{nid:int}/move")
def admin_nav_move(nid: int, direction: str = Form(...), admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cur = conn.execute("SELECT category_id FROM nav_link WHERE id=?", (nid,)).fetchone()
        if cur:
            _move_rows(conn, "nav_link", nid, direction,
                       where_sql="WHERE category_id=?", params=(cur["category_id"],))
    return RedirectResponse("/admin/nav", status_code=302)


@app.post("/admin/nav/{nid:int}/delete")
def admin_nav_delete(nid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT name FROM nav_link WHERE id=?", (nid,)).fetchone()
        conn.execute("DELETE FROM nav_link WHERE id=?", (nid,))
        if row:
            db.log_action(conn, admin["username"], "删除网址", row["name"], client_ip(request))
    return RedirectResponse("/admin/nav", status_code=302)


# ---------- 首页布局管理 ----------

def _block_cats(conn):
    return conn.execute(
        "SELECT id, name FROM category WHERE visible = 1 ORDER BY sort_order, id"
    ).fetchall()


@app.get("/admin/blocks", response_class=HTMLResponse)
def admin_blocks(request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        rows = conn.execute("SELECT * FROM home_block ORDER BY sort_order, id").fetchall()
        cats = _block_cats(conn)
    cat_names = {c["id"]: c["name"] for c in cats}
    return templates.TemplateResponse(request, "admin_blocks.html", {
        "admin": admin, "rows": rows, "block_types": BLOCK_TYPES, "cat_names": cat_names,
    })


def _block_form_ctx(request, admin, item, cats, error=""):
    cfg = {}
    if item and item.get("config"):
        try:
            cfg = json.loads(item["config"])
        except ValueError:
            cfg = {}
    return templates.TemplateResponse(request, "admin_block_form.html", {
        "admin": admin, "item": item, "cfg": cfg, "cats": cats,
        "block_types": BLOCK_TYPES, "error": error,
    })


@app.get("/admin/blocks/add", response_class=HTMLResponse)
def admin_block_add_page(request: Request, type: str = "category", admin=Depends(require_full_admin)):
    if type not in BLOCK_TYPES:
        type = "category"
    with db.get_db() as conn:
        cats = _block_cats(conn)
    return _block_form_ctx(request, admin, {"block_type": type, "title": ""}, cats)


def _parse_block(block_type: str, form):
    """从表单解析 (ref_id, config)，始终成功（结构完整，值可能为空）"""
    ref_id, cfg = 0, {}
    if block_type == "category":
        try:
            ref_id = int(form.get("ref_id") or 0)
        except ValueError:
            ref_id = 0
        cfg["width"] = "half" if form.get("width") == "half" else "full"
        try:
            cfg["max"] = max(0, int(form.get("max") or 0))
        except ValueError:
            cfg["max"] = 0
    elif block_type == "vulns":
        try:
            cfg["max"] = min(24, max(1, int(form.get("max") or 6)))
        except ValueError:
            cfg["max"] = 6
    elif block_type == "announce":
        cfg["text"] = (form.get("text") or "").strip()[:200]
        cfg["level"] = form.get("level") if form.get("level") in ("info", "warn") else "info"
    elif block_type == "links":
        names = form.getlist("link_names")
        urls = form.getlist("link_urls")
        cfg["links"] = [
            {"name": n.strip()[:32], "url": u.strip()[:300]}
            for n, u in zip(names, urls) if n.strip() and u.strip()
        ][:20]
    elif block_type == "html":
        cfg["content"] = (form.get("content") or "").strip()[:10000]
    return ref_id, cfg


def _validate_block(block_type: str, ref_id: int, cfg: dict) -> str:
    """返回错误文案，合法返回空串"""
    if block_type == "category" and not ref_id:
        return "请选择要展示的分类"
    if block_type == "announce" and not cfg.get("text"):
        return "公告内容不能为空"
    if block_type == "links" and not cfg.get("links"):
        return "请至少填写一条有效链接"
    if block_type == "html" and not cfg.get("content"):
        return "内容不能为空"
    return ""


@app.post("/admin/blocks/add")
async def admin_block_add(request: Request, admin=Depends(require_full_admin)):
    form = await request.form(max_part_size=MAX_FORM_PART)
    block_type = form.get("block_type") or "category"
    if block_type not in BLOCK_TYPES:
        raise HTTPException(400, "未知区块类型")
    title = (form.get("title") or "").strip()[:64]
    with db.get_db() as conn:
        cats = _block_cats(conn)
        ref_id, cfg = _parse_block(block_type, form)
        error = _validate_block(block_type, ref_id, cfg)
        if not error and block_type == "category" and ref_id not in {c["id"] for c in cats}:
            error = "所选分类不存在或已隐藏"
        if error:
            return _block_form_ctx(request, admin,
                                   {"block_type": block_type, "title": title,
                                    "config": json.dumps(cfg, ensure_ascii=False)},
                                   cats, error)
        now = db.now_str()
        top = conn.execute("SELECT COALESCE(MAX(sort_order),-1) AS m FROM home_block").fetchone()["m"]
        conn.execute(
            "INSERT INTO home_block (block_type, ref_id, title, config, sort_order, visible, created_at, updated_at)"
            " VALUES (?,?,?,?,?,1,?,?)",
            (block_type, ref_id or None, title, json.dumps(cfg, ensure_ascii=False), top + 1, now, now),
        )
        db.log_action(conn, admin["username"], "新增首页区块", BLOCK_TYPES[block_type], client_ip(request))
    return RedirectResponse("/admin/blocks", status_code=302)


@app.get("/admin/blocks/{bid:int}/edit", response_class=HTMLResponse)
def admin_block_edit_page(request: Request, bid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM home_block WHERE id = ?", (bid,)).fetchone()
        if not item:
            raise HTTPException(404)
        cats = _block_cats(conn)
    return _block_form_ctx(request, admin, item, cats)


@app.post("/admin/blocks/{bid:int}/edit")
async def admin_block_edit(request: Request, bid: int, admin=Depends(require_full_admin)):
    form = await request.form(max_part_size=MAX_FORM_PART)
    title = (form.get("title") or "").strip()[:64]
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM home_block WHERE id = ?", (bid,)).fetchone()
        if not item:
            raise HTTPException(404)
        cats = _block_cats(conn)
        ref_id, cfg = _parse_block(item["block_type"], form)
        error = _validate_block(item["block_type"], ref_id, cfg)
        if not error and item["block_type"] == "category" and ref_id not in {c["id"] for c in cats}:
            error = "所选分类不存在或已隐藏"
        if error:
            return _block_form_ctx(request, admin,
                                   dict(item, title=title, config=json.dumps(cfg, ensure_ascii=False)),
                                   cats, error)
        conn.execute(
            "UPDATE home_block SET ref_id=?, title=?, config=?, updated_at=? WHERE id=?",
            (ref_id or None, title, json.dumps(cfg, ensure_ascii=False), db.now_str(), bid),
        )
        db.log_action(conn, admin["username"], "编辑首页区块", BLOCK_TYPES[item["block_type"]], client_ip(request))
    return RedirectResponse("/admin/blocks", status_code=302)


@app.post("/admin/blocks/{bid:int}/toggle")
def admin_block_toggle(bid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        conn.execute(
            "UPDATE home_block SET visible = 1 - visible, updated_at = ? WHERE id = ?",
            (db.now_str(), bid),
        )
    return RedirectResponse("/admin/blocks", status_code=302)


@app.post("/admin/blocks/{bid:int}/delete")
def admin_block_delete(bid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT block_type FROM home_block WHERE id = ?", (bid,)).fetchone()
        if row:
            conn.execute("DELETE FROM home_block WHERE id = ?", (bid,))
            db.log_action(conn, admin["username"], "删除首页区块",
                          BLOCK_TYPES.get(row["block_type"], row["block_type"]), client_ip(request))
    return RedirectResponse("/admin/blocks", status_code=302)


@app.post("/admin/blocks/order")
async def admin_blocks_order(request: Request, admin=Depends(require_full_admin)):
    """拖拽排序：ids 为逗号分隔的区块 id 序列"""
    form = await request.form(max_part_size=MAX_FORM_PART)
    ids = [int(x) for x in str(form.get("ids") or "").split(",") if x.strip().isdigit()]
    with db.get_db() as conn:
        for i, bid in enumerate(ids):
            conn.execute("UPDATE home_block SET sort_order = ? WHERE id = ?", (i, bid))
    return {"ok": True}


@app.get("/admin/comments", response_class=HTMLResponse)
def admin_comments(request: Request, admin=Depends(require_full_admin), status: str = "pending"):
    if status not in ("pending", "approved", "rejected"):
        status = "pending"
    with db.get_db() as conn:
        rows = conn.execute(
            """SELECT c.*, s.name AS software_name, p.nickname AS parent_nickname, p.content AS parent_content
               FROM comment c JOIN software s ON s.id = c.software_id
               LEFT JOIN comment p ON p.id = c.parent_id
               WHERE c.status = ? ORDER BY c.created_at DESC, c.id DESC LIMIT 300""", (status,),
        ).fetchall()
        counts = {s: conn.execute("SELECT COUNT(*) AS n FROM comment WHERE status = ?", (s,)).fetchone()["n"]
                  for s in ("pending", "approved", "rejected")}
    return templates.TemplateResponse(request, "admin_comments.html", {
        "admin": admin, "rows": rows, "status": status, "counts": counts,
    })


@app.post("/admin/comments/{cid:int}/reply")
def admin_comment_reply(cid: int, request: Request, content: str = Form(""),
                        admin=Depends(require_full_admin)):
    """管理员回复评论：落库即通过，前端带"管理员"徽章"""
    content = content.strip()[:1000]
    if not content:
        return RedirectResponse("/admin/comments?status=approved", status_code=302)
    with db.get_db() as conn:
        row = conn.execute("SELECT * FROM comment WHERE id = ?", (cid,)).fetchone()
        if not row:
            raise HTTPException(404)
        root_id = row["parent_id"] or row["id"]  # 保持两层结构
        now = db.now_str()
        conn.execute(
            """INSERT INTO comment (software_id, parent_id, nickname, content, client_ip, is_admin,
               status, created_at, approved_at) VALUES (?,?,?,?,?,1,'approved',?,?)""",
            (row["software_id"], root_id, admin["username"][:32], content, "", now, now),
        )
        db.log_action(conn, admin["username"], "回复评论",
                      f"评论#{cid}：{content[:60]}", client_ip(request))
    return RedirectResponse("/admin/comments?status=approved", status_code=302)


@app.post("/admin/comments/{cid:int}/{action}")
def admin_comment_action(cid: int, action: str, request: Request, admin=Depends(require_full_admin)):
    if action not in ("approve", "reject", "delete"):
        raise HTTPException(404)
    with db.get_db() as conn:
        row = conn.execute("SELECT c.*, s.name AS sw FROM comment c JOIN software s ON s.id=c.software_id WHERE c.id = ?", (cid,)).fetchone()
        if not row:
            raise HTTPException(404)
        if action == "approve":
            conn.execute("UPDATE comment SET status='approved', approved_at=? WHERE id=?", (db.now_str(), cid))
        elif action == "reject":
            conn.execute("UPDATE comment SET status='rejected' WHERE id=?", (cid,))
        else:
            # 顶层评论被删时其回复一并删除（老库 parent_id 无外键级联，显式兜底）
            conn.execute("DELETE FROM comment WHERE id = ? OR parent_id = ?", (cid, cid))
        db.log_action(conn, admin["username"],
                      {"approve": "通过评论", "reject": "驳回评论", "delete": "删除评论"}[action],
                      f"{row['sw']}：{row['content'][:60]}", client_ip(request))
    return RedirectResponse("/admin/comments?status=" + ("approved" if action == "approve" else "pending"), status_code=302)


@app.get("/admin/settings", response_class=HTMLResponse)
def admin_settings(request: Request, admin=Depends(require_full_admin), saved: int = 0):
    return templates.TemplateResponse(request, "admin_settings.html", {
        "admin": admin, "settings": db.get_settings(), "saved": saved,
    })


@app.post("/admin/settings")
def admin_settings_save(request: Request, admin=Depends(require_full_admin),
                        hero_title: str = Form(""), hero_subtitle: str = Form(""),
                        announcement: str = Form(""), footer_text: str = Form(""),
                        contact: str = Form(""), allow_comment: int = Form(0),
                        maintenance: int = Form(0), doc_max_mb: int = Form(100)):
    doc_max_mb = max(1, min(doc_max_mb, 2048))
    values = {
        "hero_title": hero_title.strip(), "hero_subtitle": hero_subtitle.strip(),
        "announcement": announcement.strip()[:200], "footer_text": footer_text.strip(),
        "contact": contact.strip(), "allow_comment": "1" if allow_comment else "0",
        "maintenance": "1" if maintenance else "0",
        "doc_max_mb": str(doc_max_mb),
    }
    for k, v in values.items():
        db.set_setting(k, v)
    with db.get_db() as conn:
        db.log_action(conn, admin["username"], "修改站点设置",
                      f"公告={values['announcement'][:30]} 维护模式={values['maintenance']}", client_ip(request))
    return RedirectResponse("/admin/settings?saved=1", status_code=302)


@app.get("/admin/blocklist", response_class=HTMLResponse)
def admin_blocklist(request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        rows = conn.execute("SELECT * FROM ip_blocklist ORDER BY created_at DESC").fetchall()
    return templates.TemplateResponse(request, "admin_blocklist.html", {
        "admin": admin, "rows": rows,
    })


@app.post("/admin/blocklist/add")
def admin_blocklist_add(request: Request, ip: str = Form(...), reason: str = Form(""),
                        admin=Depends(require_full_admin)):
    ip = ip.strip()
    if ip:
        with db.get_db() as conn:
            conn.insert_ignore("INSERT INTO ip_blocklist (ip, reason, created_at) VALUES (?,?,?)",
                               (ip, reason.strip()[:255], db.now_str()))
            db.log_action(conn, admin["username"], "封禁IP", ip, client_ip(request))
    return RedirectResponse("/admin/blocklist", status_code=302)


@app.post("/admin/blocklist/{bid:int}/delete")
def admin_blocklist_delete(bid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT ip FROM ip_blocklist WHERE id = ?", (bid,)).fetchone()
        conn.execute("DELETE FROM ip_blocklist WHERE id = ?", (bid,))
        if row:
            db.log_action(conn, admin["username"], "移除IP黑名单", row["ip"], client_ip(request))
    return RedirectResponse("/admin/blocklist", status_code=302)


# ---------- 下载控制（按 IP 并发限速） ----------

def _dl_ctx(request, admin, saved=0):
    with db.get_db() as conn:
        rules = conn.execute("SELECT * FROM dl_ip_rule ORDER BY created_at DESC").fetchall()
        blocked = {r["ip"] for r in conn.execute("SELECT ip FROM ip_blocklist").fetchall()}
        active_downloads = dllimit.snapshot(conn)
    return templates.TemplateResponse(request, "admin_download_control.html", {
        "admin": admin, "active": "dlcontrol", "rules": rules,
        "active_downloads": active_downloads, "blocked": blocked,
        "s": db.get_settings(), "saved": saved,
    })


@app.get("/admin/download-control", response_class=HTMLResponse)
def admin_dl_control(request: Request, admin=Depends(require_full_admin), saved: int = 0):
    return _dl_ctx(request, admin, saved)


@app.post("/admin/download-control/settings")
def admin_dl_settings(request: Request, admin=Depends(require_full_admin),
                      dl_limit_conns: int = Form(8), dl_limit_rate_kbs: int = Form(512),
                      dl_limit_enabled: str = Form("")):
    dl_limit_conns = max(1, min(dl_limit_conns, 100))
    dl_limit_rate_kbs = max(16, min(dl_limit_rate_kbs, 1024 * 1024))
    db.set_setting("dl_limit_enabled", "1" if dl_limit_enabled else "0")
    db.set_setting("dl_limit_conns", str(dl_limit_conns))
    db.set_setting("dl_limit_rate", str(dl_limit_rate_kbs * 1024))
    with db.get_db() as conn:
        db.log_action(conn, admin["username"], "下载控制设置",
                      f"启用={bool(dl_limit_enabled)} 并发={dl_limit_conns} 限速={dl_limit_rate_kbs}KB/s",
                      client_ip(request))
    return RedirectResponse("/admin/download-control?saved=1", status_code=302)


def _valid_ip(ip: str) -> bool:
    parts = ip.split(".")
    return len(parts) == 4 and all(p.isdigit() and 0 <= int(p) <= 255 for p in parts)


@app.post("/admin/download-control/rule/add")
def admin_dl_rule_add(request: Request, ip: str = Form(...), max_conns: int = Form(0),
                      rate_kbs: int = Form(0), note: str = Form(""),
                      admin=Depends(require_full_admin)):
    ip = ip.strip()
    if not _valid_ip(ip):
        return RedirectResponse("/admin/download-control", status_code=302)
    max_conns = max(0, min(max_conns, 100))
    rate = max(0, min(rate_kbs, 1024 * 1024)) * 1024
    now = db.now_str()
    with db.get_db() as conn:
        conn.insert_ignore(
            "INSERT INTO dl_ip_rule (ip, max_conns, rate_limit, note, created_at) VALUES (?,?,?,?,?)",
            (ip, max_conns, rate, note.strip()[:255], now))
        conn.execute(
            "UPDATE dl_ip_rule SET max_conns=?, rate_limit=?, note=? WHERE ip=?",
            (max_conns, rate, note.strip()[:255], ip))
        db.log_action(conn, admin["username"], "下载限速规则",
                      f"{ip} 并发上限={max_conns or '全局'} 限速={rate_kbs or '全局'}KB/s", client_ip(request))
    return RedirectResponse("/admin/download-control", status_code=302)


@app.post("/admin/download-control/rule/{rid:int}/delete")
def admin_dl_rule_delete(rid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT ip FROM dl_ip_rule WHERE id = ?", (rid,)).fetchone()
        conn.execute("DELETE FROM dl_ip_rule WHERE id = ?", (rid,))
        if row:
            db.log_action(conn, admin["username"], "删除限速规则", row["ip"], client_ip(request))
    return RedirectResponse("/admin/download-control", status_code=302)


@app.post("/admin/download-control/quick-throttle")
def admin_dl_quick_throttle(request: Request, ip: str = Form(...), admin=Depends(require_full_admin)):
    """活跃面板一键限速：该 IP 只允许 1 个并发，超出的连接压到 256KB/s"""
    ip = ip.strip()
    if _valid_ip(ip):
        with db.get_db() as conn:
            conn.insert_ignore(
                "INSERT INTO dl_ip_rule (ip, max_conns, rate_limit, note, created_at) VALUES (?,?,?,?,?)",
                (ip, 1, 262144, "面板一键限速", db.now_str()))
            conn.execute(
                "UPDATE dl_ip_rule SET max_conns=1, rate_limit=262144, note='面板一键限速' WHERE ip=?",
                (ip,))
            db.log_action(conn, admin["username"], "一键限速", ip, client_ip(request))
    return RedirectResponse("/admin/download-control", status_code=302)


@app.post("/admin/download-control/block")
def admin_dl_block(request: Request, ip: str = Form(...), admin=Depends(require_full_admin)):
    """活跃面板一键拉黑（复用现有 IP 黑名单，下载直接 403）"""
    ip = ip.strip()
    if _valid_ip(ip):
        with db.get_db() as conn:
            conn.insert_ignore("INSERT INTO ip_blocklist (ip, reason, created_at) VALUES (?,?,?)",
                               (ip, "下载控制面板拉黑", db.now_str()))
            db.log_action(conn, admin["username"], "封禁IP", ip, client_ip(request))
    return RedirectResponse("/admin/download-control", status_code=302)


@app.get("/admin/audit", response_class=HTMLResponse)
def admin_audit(request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        rows = conn.execute("SELECT * FROM admin_log ORDER BY created_at DESC, id DESC LIMIT 300").fetchall()
    return templates.TemplateResponse(request, "admin_audit.html", {"admin": admin, "rows": rows})


@app.get("/admin/logs", response_class=HTMLResponse)
def admin_logs(request: Request, admin=Depends(require_full_admin),
               q: str = "", ip: str = "", date: str = "", page: int = 1):
    per_page = 30
    where, params = [], []
    if q:
        where.append("software_name LIKE ?")
        params.append(f"%{q}%")
    if ip:
        where.append("client_ip LIKE ?")
        params.append(f"%{ip}%")
    if date:
        where.append("downloaded_at LIKE ?")
        params.append(date + "%")
    clause = (" WHERE " + " AND ".join(where)) if where else ""
    with db.get_db() as conn:
        total = conn.execute(f"SELECT COUNT(*) AS n FROM download_log{clause}", params).fetchone()["n"]
        per_page_i, offset_i = int(per_page), (max(1, int(page)) - 1) * per_page
        rows = conn.execute(
            f"""SELECT * FROM download_log{clause}
                ORDER BY downloaded_at DESC, id DESC LIMIT {per_page_i} OFFSET {offset_i}""",
            params,
        ).fetchall()
    pages = max(1, (total + per_page - 1) // per_page)
    return templates.TemplateResponse(request, "admin_logs.html", {
        "admin": admin, "logs": rows, "total": total, "page": page, "pages": pages,
        "q": q, "ip": ip, "date": date,
    })


@app.get("/admin/logs/export")
def admin_logs_export(q: str = "", ip: str = "", date: str = "", admin=Depends(require_full_admin)):
    where, params = [], []
    if q:
        where.append("software_name LIKE ?")
        params.append(f"%{q}%")
    if ip:
        where.append("client_ip LIKE ?")
        params.append(f"%{ip}%")
    if date:
        where.append("downloaded_at LIKE ?")
        params.append(date + "%")
    clause = (" WHERE " + " AND ".join(where)) if where else ""
    with db.get_db() as conn:
        rows = conn.execute(
            f"SELECT software_name, file_label, client_ip, user_agent, downloaded_at FROM download_log{clause} ORDER BY downloaded_at DESC",
            params,
        ).fetchall()
    buf = io.StringIO()
    buf.write("\ufeff")
    w = csv.writer(buf)
    w.writerow(["软件名称", "文件", "下载IP", "User-Agent", "下载时间"])
    for r in rows:
        w.writerow([r["software_name"], r["file_label"], r["client_ip"], r["user_agent"], r["downloaded_at"]])
    return Response(buf.getvalue().encode("utf-8"), media_type="text/csv",
                    headers={"Content-Disposition": "attachment; filename=download_logs.csv"})


@app.get("/admin/password", response_class=HTMLResponse)
def admin_password_page(request: Request, admin=Depends(require_admin)):
    return templates.TemplateResponse(request, "admin_password.html", {
        "admin": admin, "error": "", "ok": False,
    })


@app.post("/admin/password")
def admin_password(request: Request, old_password: str = Form(...),
                   new_password: str = Form(...), confirm: str = Form(...),
                   admin=Depends(require_admin)):
    error = ""
    with db.get_db() as conn:
        row = conn.execute("SELECT password_hash FROM admin WHERE id = ?", (admin["id"],)).fetchone()
    if not row or not auth.verify_password(old_password, row["password_hash"]):
        error = "原密码错误"
    elif len(new_password) < 6:
        error = "新密码至少 6 位"
    elif new_password != confirm:
        error = "两次输入的新密码不一致"
    if error:
        return templates.TemplateResponse(request, "admin_password.html", {
            "admin": admin, "error": error, "ok": False,
        }, status_code=400)
    auth.change_password(admin["id"], new_password)
    resp = RedirectResponse("/admin/login", status_code=302)
    resp.delete_cookie(SESSION_COOKIE)
    return resp


# ---------- 用户管理（仅管理员） ----------

@app.get("/admin/users", response_class=HTMLResponse)
def admin_users(request: Request, admin=Depends(require_full_admin), error: str = ""):
    with db.get_db() as conn:
        rows = conn.execute(
            """SELECT a.*, (SELECT COUNT(*) FROM software s WHERE s.submitter_id = a.id) AS sw_count,
                      (SELECT COUNT(*) FROM vulnerability v WHERE v.submitter_id = a.id) AS vuln_count
               FROM admin a ORDER BY a.id"""
        ).fetchall()
    return templates.TemplateResponse(request, "admin_users.html", {
        "admin": admin, "rows": rows, "error": request.query_params.get("error", ""),
    })


@app.post("/admin/users/add")
def admin_users_add(request: Request, username: str = Form(...), password: str = Form(...),
                    role: str = Form("uploader"), admin=Depends(require_full_admin)):
    username = username.strip()[:64]
    if role not in ("admin", "uploader"):
        role = "uploader"
    if not username or len(password) < 6:
        return RedirectResponse("/admin/users?error=用户名不能为空，密码至少 6 位", status_code=302)
    with db.get_db() as conn:
        exists = conn.execute("SELECT 1 AS x FROM admin WHERE username = ?", (username,)).fetchone()
        if exists:
            return RedirectResponse("/admin/users?error=用户名已存在", status_code=302)
        conn.execute(
            "INSERT INTO admin (username, password_hash, role, created_at) VALUES (?,?,?,?)",
            (username, auth.hash_password(password), role, db.now_str()),
        )
        db.log_action(conn, admin["username"], "新增用户", f"{username}（{role}）", client_ip(request))
    return RedirectResponse("/admin/users", status_code=302)


@app.post("/admin/users/{uid:int}/password")
def admin_users_password(uid: int, request: Request, new_password: str = Form(...),
                         admin=Depends(require_full_admin)):
    if len(new_password) < 6:
        return RedirectResponse("/admin/users?error=密码至少 6 位", status_code=302)
    with db.get_db() as conn:
        row = conn.execute("SELECT username FROM admin WHERE id = ?", (uid,)).fetchone()
        if not row:
            raise HTTPException(404)
        conn.execute("UPDATE admin SET password_hash = ? WHERE id = ?", (auth.hash_password(new_password), uid))
        conn.execute("DELETE FROM session WHERE admin_id = ?", (uid,))
        db.log_action(conn, admin["username"], "重置密码", row["username"], client_ip(request))
    return RedirectResponse("/admin/users", status_code=302)


@app.post("/admin/users/{uid:int}/toggle")
def admin_users_toggle(uid: int, request: Request, admin=Depends(require_full_admin)):
    if uid == admin["id"]:
        return RedirectResponse("/admin/users?error=不能停用自己的账号", status_code=302)
    with db.get_db() as conn:
        row = conn.execute("SELECT username, role, disabled FROM admin WHERE id = ?", (uid,)).fetchone()
        if not row:
            raise HTTPException(404)
        if row["role"] == "admin" and not row["disabled"]:
            n = conn.execute(
                "SELECT COUNT(*) AS n FROM admin WHERE role = 'admin' AND disabled = 0"
            ).fetchone()["n"]
            if n <= 1:
                return RedirectResponse("/admin/users?error=至少保留一个可用管理员", status_code=302)
        conn.execute("UPDATE admin SET disabled = ? WHERE id = ?", (0 if row["disabled"] else 1, uid))
        if not row["disabled"]:
            conn.execute("DELETE FROM session WHERE admin_id = ?", (uid,))
        db.log_action(conn, admin["username"], "启用账号" if row["disabled"] else "停用账号",
                      row["username"], client_ip(request))
    return RedirectResponse("/admin/users", status_code=302)


@app.post("/admin/users/{uid:int}/delete")
def admin_users_delete(uid: int, request: Request, admin=Depends(require_full_admin)):
    if uid == admin["id"]:
        return RedirectResponse("/admin/users?error=不能删除自己的账号", status_code=302)
    with db.get_db() as conn:
        row = conn.execute("SELECT username, role FROM admin WHERE id = ?", (uid,)).fetchone()
        if not row:
            raise HTTPException(404)
        if row["role"] == "admin":
            n = conn.execute("SELECT COUNT(*) AS n FROM admin WHERE role = 'admin'").fetchone()["n"]
            if n <= 1:
                return RedirectResponse("/admin/users?error=至少保留一个管理员", status_code=302)
        # 投稿记录保留（software/vulnerability 冗余了 submitter_name，无需级联清理）
        conn.execute("DELETE FROM admin WHERE id = ?", (uid,))
        conn.execute("DELETE FROM session WHERE admin_id = ?", (uid,))
        db.log_action(conn, admin["username"], "删除用户", row["username"], client_ip(request))
    return RedirectResponse("/admin/users", status_code=302)


# ---------- 投稿审核（仅管理员） ----------

@app.get("/admin/review", response_class=HTMLResponse)
def admin_review(request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        sw = conn.execute(
            """SELECT s.*, c.name AS category_name,
                      (SELECT COUNT(*) FROM software_file f WHERE f.software_id = s.id) AS file_count
               FROM software s LEFT JOIN category c ON c.id = s.category_id
               WHERE s.status = 2 ORDER BY s.created_at ASC"""
        ).fetchall()
        vulns = conn.execute(
            """SELECT v.*, (SELECT COUNT(*) FROM vulnerability_file f WHERE f.vulnerability_id = v.id) AS file_count
               FROM vulnerability v WHERE v.status = 2 ORDER BY v.created_at ASC"""
        ).fetchall()
        arts = conn.execute(
            "SELECT * FROM article WHERE status = 2 ORDER BY created_at ASC"
        ).fetchall()
    return templates.TemplateResponse(request, "admin_review.html", {
        "admin": admin, "sw": sw, "vulns": vulns, "arts": arts,
    })


def _review(conn, table, rid, action, note):
    now = db.now_str()
    if action == "approve":
        conn.execute(
            f"UPDATE {table} SET status = 1, reviewed_at = ?, review_note = '' WHERE id = ?",
            (now, rid),
        )
    else:
        conn.execute(
            f"UPDATE {table} SET status = 0, reviewed_at = ?, review_note = ? WHERE id = ?",
            (now, note[:255], rid),
        )


@app.post("/admin/review/software/{sid:int}")
def admin_review_software(sid: int, request: Request, action: str = Form(...),
                          note: str = Form(""), admin=Depends(require_full_admin)):
    if action not in ("approve", "reject"):
        raise HTTPException(404)
    with db.get_db() as conn:
        row = conn.execute("SELECT name FROM software WHERE id = ? AND status = 2", (sid,)).fetchone()
        if not row:
            raise HTTPException(404, "该投稿不存在或已处理")
        _review(conn, "software", sid, action, note)
        db.log_action(conn, admin["username"], "通过投稿(软件)" if action == "approve" else "驳回投稿(软件)",
                      row["name"], client_ip(request))
    return RedirectResponse("/admin/review", status_code=302)


@app.post("/admin/review/vuln/{vid:int}")
def admin_review_vuln(vid: int, request: Request, action: str = Form(...),
                      note: str = Form(""), admin=Depends(require_full_admin)):
    if action not in ("approve", "reject"):
        raise HTTPException(404)
    with db.get_db() as conn:
        row = conn.execute("SELECT title FROM vulnerability WHERE id = ? AND status = 2", (vid,)).fetchone()
        if not row:
            raise HTTPException(404, "该投稿不存在或已处理")
        _review(conn, "vulnerability", vid, action, note)
        db.log_action(conn, admin["username"], "通过投稿(漏洞)" if action == "approve" else "驳回投稿(漏洞)",
                      row["title"], client_ip(request))
    return RedirectResponse("/admin/review", status_code=302)


@app.post("/admin/review/article/{aid:int}")
def admin_review_article(aid: int, request: Request, action: str = Form(...),
                         note: str = Form(""), admin=Depends(require_full_admin)):
    if action not in ("approve", "reject"):
        raise HTTPException(404)
    with db.get_db() as conn:
        row = conn.execute("SELECT title FROM article WHERE id = ? AND status = 2", (aid,)).fetchone()
        if not row:
            raise HTTPException(404, "该投稿不存在或已处理")
        _review(conn, "article", aid, action, note)
        db.log_action(conn, admin["username"], "通过投稿(文章)" if action == "approve" else "驳回投稿(文章)",
                      row["title"], client_ip(request))
    return RedirectResponse("/admin/review", status_code=302)


# ---------- 漏洞管理（仅管理员，直接发布） ----------

def _vuln_form_ctx(request, admin, item, files, error="", submitter_mode=False, form=None):
    if submitter_mode:
        action = f"/admin/my/vuln/{item['id']}/edit" if item else "/admin/submit/vuln"
        cancel = "/admin/my"
        title = f"编辑投稿：{item['title']}" if item else "提交漏洞补丁"
    else:
        action = f"/admin/vulns/{item['id']}/edit" if item else "/admin/vulns/add"
        cancel = "/admin/vulns"
        title = f"编辑漏洞：{item['title']}" if item else "发布漏洞补丁"
    return templates.TemplateResponse(request, "admin_vuln_form.html", {
        "admin": admin, "item": item, "files": files, "error": error,
        "submitter_mode": submitter_mode, "form": form,
        "form_action": action, "cancel_url": cancel, "page_title": title,
    })


def _vuln_files(conn, vid):
    return conn.execute(
        "SELECT * FROM vulnerability_file WHERE vulnerability_id = ? ORDER BY sort_order, id", (vid,)
    ).fetchall()


def _save_vuln_files(conn, vid, collected, now, start_order=0):
    for i, (label, original, stored, size, stype) in enumerate(collected):
        conn.execute(
            """INSERT INTO vulnerability_file (vulnerability_id, label, original_name, file_path,
               file_size, source_type, sort_order, created_at) VALUES (?,?,?,?,?,?,?,?)""",
            (vid, label, original, stored, size, stype, start_order + i, now),
        )


def _update_vuln_files(conn, vid, existing_ids, existing_labels, delete_ids):
    for del_id in delete_ids:
        row = conn.execute(
            "SELECT * FROM vulnerability_file WHERE id = ? AND vulnerability_id = ?", (del_id, vid)
        ).fetchone()
        if row:
            conn.execute("DELETE FROM vulnerability_file WHERE id = ?", (del_id,))
            if row["source_type"] == "upload":
                p = Path(row["file_path"])
                if p.is_file() and p.parent == config.UPLOAD_DIR:
                    p.unlink()
    label_map = dict(zip(existing_ids, existing_labels))
    for eid in existing_ids:
        if eid not in delete_ids:
            conn.execute(
                "UPDATE vulnerability_file SET label = ? WHERE id = ? AND vulnerability_id = ?",
                (label_map.get(eid, "补丁包").strip() or "补丁包", eid, vid),
            )
    return conn.execute(
        "SELECT COALESCE(MAX(sort_order),-1) AS m FROM vulnerability_file WHERE vulnerability_id = ?", (vid,)
    ).fetchone()["m"] + 1


@app.get("/admin/vulns", response_class=HTMLResponse)
def admin_vulns(request: Request, admin=Depends(require_full_admin), q: str = ""):
    sql = """SELECT v.*, (SELECT COUNT(*) FROM vulnerability_file f WHERE f.vulnerability_id = v.id) AS file_count
             FROM vulnerability v"""
    params: list = []
    if q:
        sql += " WHERE v.title LIKE ? OR v.cve_no LIKE ?"
        params += [f"%{q}%", f"%{q}%"]
    sql += " ORDER BY v.created_at DESC, v.id DESC"
    with db.get_db() as conn:
        rows = conn.execute(sql, params).fetchall()
    return templates.TemplateResponse(request, "admin_vulns.html", {
        "admin": admin, "rows": rows, "q": q,
    })


@app.get("/admin/vulns/add", response_class=HTMLResponse)
def admin_vuln_add_page(request: Request, admin=Depends(require_full_admin)):
    return _vuln_form_ctx(request, admin, None, [])


@app.post("/admin/vulns/add")
async def admin_vuln_add(request: Request, admin=Depends(require_full_admin),
                         title: str = Form(...), cve_no: str = Form(""),
                         severity: str = Form("medium"), affected_product: str = Form(""),
                         affected_version: str = Form(""), official_url: str = Form(""),
                         description: str = Form(""),
                         file_labels: List[str] = Form([]), file_paths: List[str] = Form([])):
    try:
        collected = _collect_files(file_labels, file_paths, _form_uploads(await request.form(max_part_size=MAX_FORM_PART)))
        now = db.now_str()
        with db.get_db() as conn:
            vid = conn.insert_returning_id(
                """INSERT INTO vulnerability (title, cve_no, severity, affected_product, affected_version,
                   description, official_url, status, submitter_id, submitter_name, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,1,?,?,?,?)""",
                (title.strip()[:128], cve_no.strip()[:32], severity if severity in SEVERITIES else "medium",
                 affected_product.strip()[:128], affected_version.strip()[:128], description,
                 official_url.strip()[:500], admin["id"], admin["username"], now, now),
            )
            _save_vuln_files(conn, vid, collected, now)
            db.log_action(conn, admin["username"], "发布漏洞", title, client_ip(request))
        return RedirectResponse("/admin/vulns", status_code=302)
    except ValueError as e:
        return _vuln_form_ctx(request, admin, None, [], str(e), form={
            "title": title, "cve_no": cve_no, "severity": severity,
            "affected_product": affected_product, "affected_version": affected_version,
            "official_url": official_url, "description": description,
        })


@app.get("/admin/vulns/{vid:int}/edit", response_class=HTMLResponse)
def admin_vuln_edit_page(request: Request, vid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM vulnerability WHERE id = ?", (vid,)).fetchone()
        if not item:
            raise HTTPException(404)
        files = _vuln_files(conn, vid)
    return _vuln_form_ctx(request, admin, item, files)


@app.post("/admin/vulns/{vid:int}/edit")
async def admin_vuln_edit(request: Request, vid: int, admin=Depends(require_full_admin),
                          title: str = Form(...), cve_no: str = Form(""),
                          severity: str = Form("medium"), affected_product: str = Form(""),
                          affected_version: str = Form(""), official_url: str = Form(""),
                          description: str = Form(""), status: int = Form(1),
                          existing_ids: List[int] = Form([]), existing_labels: List[str] = Form([]),
                          delete_ids: List[int] = Form([]),
                          file_labels: List[str] = Form([]), file_paths: List[str] = Form([])):
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM vulnerability WHERE id = ?", (vid,)).fetchone()
        if not item:
            raise HTTPException(404)
        files = _vuln_files(conn, vid)
    try:
        collected = _collect_files(file_labels, file_paths, _form_uploads(await request.form(max_part_size=MAX_FORM_PART)))
        now = db.now_str()
        with db.get_db() as conn:
            next_order = _update_vuln_files(conn, vid, existing_ids, existing_labels, delete_ids)
            _save_vuln_files(conn, vid, collected, now, next_order)
            conn.execute(
                """UPDATE vulnerability SET title=?, cve_no=?, severity=?, affected_product=?,
                   affected_version=?, description=?, official_url=?, status=?, updated_at=? WHERE id=?""",
                (title.strip()[:128], cve_no.strip()[:32], severity if severity in SEVERITIES else "medium",
                 affected_product.strip()[:128], affected_version.strip()[:128], description,
                 official_url.strip()[:500], 1 if status else 0, now, vid),
            )
            db.log_action(conn, admin["username"], "编辑漏洞", title, client_ip(request))
        return RedirectResponse("/admin/vulns", status_code=302)
    except ValueError as e:
        return _vuln_form_ctx(request, admin, item, files, str(e))


@app.post("/admin/vulns/{vid:int}/delete")
def admin_vuln_delete(vid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT title FROM vulnerability WHERE id = ?", (vid,)).fetchone()
        if item:
            up_files = conn.execute(
                "SELECT file_path FROM vulnerability_file WHERE vulnerability_id = ? AND source_type = 'upload'",
                (vid,),
            ).fetchall()
            conn.execute("DELETE FROM vulnerability WHERE id = ?", (vid,))
            for r in up_files:
                p = Path(r["file_path"])
                if p.is_file() and p.parent == config.UPLOAD_DIR:
                    p.unlink()
            db.log_action(conn, admin["username"], "删除漏洞", item["title"], client_ip(request))
    return RedirectResponse("/admin/vulns", status_code=302)


# ---------- 技术文章管理（仅管理员，直接发布） ----------

def _article_form_ctx(request, admin, item, error="", submitter_mode=False):
    if submitter_mode:
        action = f"/admin/my/article/{item['id']}/edit" if item else "/admin/submit/article"
        cancel, title = "/admin/my", (f"编辑投稿：{item['title']}" if item else "提交技术文章")
    else:
        action = f"/admin/articles/{item['id']}/edit" if item else "/admin/articles/add"
        cancel, title = "/admin/articles", (f"编辑文章：{item['title']}" if item else "发布技术文章")
    parts, attachments = [], []
    if item and item.get("id"):
        with db.get_db() as conn:
            parts = conn.execute(
                "SELECT * FROM article_part WHERE article_id = ? ORDER BY sort_order", (item["id"],)
            ).fetchall()
            attachments = conn.execute(
                "SELECT * FROM article_attachment WHERE article_id = ? ORDER BY sort_order", (item["id"],)
            ).fetchall()
    return templates.TemplateResponse(request, "admin_article_form.html", {
        "admin": admin, "item": item, "error": error, "submitter_mode": submitter_mode,
        "form_action": action, "cancel_url": cancel, "page_title": title,
        "parts": parts, "attachments": attachments,
    })


def _article_fields(form):
    """解析并校验文章表单，返回 (title, cover, summary, content, content_type, app_url)"""
    title = (form.get("title") or "").strip()
    cover = (form.get("cover_url") or "").strip()[:1024]
    summary = (form.get("summary") or "").strip()[:500]
    content = form.get("content") or ""
    content_type = (form.get("content_type") or "md").lower()
    if content_type not in ("md", "html", "app", "collection"):
        content_type = "md"
    app_url = (form.get("app_file_url") or "").strip()[:1024]
    if content_type == "app":
        if not app_url.startswith("/media/apps/"):
            raise ValueError("网页应用文件缺失，请重新点「导入网页应用」")
        target = (MEDIA_DIR / app_url[len("/media/"):]).resolve()
        if MEDIA_DIR.resolve() not in target.parents or not target.is_file():
            raise ValueError("网页应用文件不存在，请重新点「导入网页应用」")
    else:
        app_url = ""
    return title[:200], cover, summary, content, content_type, app_url


def _save_article_parts(conn, aid, form):
    """保存合集子篇章（content_type=collection 时），表单字段 part_titles[] / part_contents[]"""
    conn.execute("DELETE FROM article_part WHERE article_id = ?", (aid,))
    titles = form.getlist("part_titles")
    contents = form.getlist("part_contents")
    for i, (t, c) in enumerate(zip(titles, contents)):
        t = (t or "").strip()[:200]
        if not (t or (c or "").strip()):
            continue
        conn.execute(
            "INSERT INTO article_part (article_id, sort_order, title, content, content_type) VALUES (?,?,?,?,?)",
            (aid, i, t, c, "html"),
        )


def _save_article_attachments(conn, aid, form):
    """保存文章附件：文件(kind=file)和链接(kind=link)"""
    conn.execute("DELETE FROM article_attachment WHERE article_id = ?", (aid,))
    kinds = form.getlist("att_kinds")
    names = form.getlist("att_names")
    urls = form.getlist("att_urls")
    sizes = form.getlist("att_sizes")
    for i, (k, n, u) in enumerate(zip(kinds, names, urls)):
        k = (k or "file").strip()
        if k not in ("file", "link"):
            continue
        u = (u or "").strip()[:1024]
        if k == "file" and not u.startswith("/media/"):
            continue
        if k == "link" and not u.startswith(("http://", "https://")):
            continue
        sz = 0
        try:
            sz = int(sizes[i]) if i < len(sizes) else 0
        except ValueError:
            sz = 0
        conn.execute(
            "INSERT INTO article_attachment (article_id, sort_order, kind, name, url, file_size) "
            "VALUES (?,?,?,?,?,?)",
            (aid, i, k, (n or "").strip()[:255], u, sz),
        )


@app.get("/admin/articles", response_class=HTMLResponse)
def admin_articles(request: Request, q: str = "", admin=Depends(require_full_admin)):
    where, params = [], []
    if q:
        where.append("(title LIKE ? OR submitter_name LIKE ?)")
        params += [f"%{q}%", f"%{q}%"]
    clause = (" WHERE " + " AND ".join(where)) if where else ""
    with db.get_db() as conn:
        rows = conn.execute(
            f"SELECT * FROM article{clause} ORDER BY updated_at DESC, id DESC LIMIT 200", params
        ).fetchall()
    return templates.TemplateResponse(request, "admin_articles.html", {
        "admin": admin, "rows": rows, "q": q,
    })


@app.get("/admin/articles/add", response_class=HTMLResponse)
def admin_article_add_page(request: Request, admin=Depends(require_full_admin)):
    return _article_form_ctx(request, admin, None)


@app.post("/admin/articles/add")
async def admin_article_add(request: Request, admin=Depends(require_full_admin)):
    form = await request.form(max_part_size=MAX_FORM_PART)
    try:
        title, cover, summary, content, ctype, app_url = _article_fields(form)
    except ValueError as e:
        return _article_form_ctx(request, admin, {"title": form.get("title") or ""}, str(e))
    if not title:
        return _article_form_ctx(request, admin, {"title": title}, "标题不能为空")
    now = db.now_str()
    with db.get_db() as conn:
        aid = conn.insert_returning_id(
            """INSERT INTO article (title, cover_url, summary, content, content_type, app_file_url,
               status, submitter_id, submitter_name, reviewed_at, created_at, updated_at)
               VALUES (?,?,?,?,?,?,1,?,?,?,?,?)""",
            (title, cover, summary, content, ctype, app_url,
             admin["id"], admin["username"], now, now, now),
        )
        _save_article_parts(conn, aid, form)
        _save_article_attachments(conn, aid, form)
        db.log_action(conn, admin["username"], "发布文章", title, client_ip(request))
    return RedirectResponse("/admin/articles", status_code=302)


@app.get("/admin/articles/{aid:int}/edit", response_class=HTMLResponse)
def admin_article_edit_page(request: Request, aid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM article WHERE id = ?", (aid,)).fetchone()
    if not item:
        raise HTTPException(404)
    return _article_form_ctx(request, admin, item)


@app.post("/admin/articles/{aid:int}/edit")
async def admin_article_edit(request: Request, aid: int, admin=Depends(require_full_admin)):
    form = await request.form(max_part_size=MAX_FORM_PART)
    with db.get_db() as conn:
        item = conn.execute("SELECT * FROM article WHERE id = ?", (aid,)).fetchone()
        if not item:
            raise HTTPException(404)
    try:
        title, cover, summary, content, ctype, app_url = _article_fields(form)
    except ValueError as e:
        return _article_form_ctx(request, admin, dict(item), str(e))
    if not title:
        return _article_form_ctx(request, admin, dict(item), "标题不能为空")
    status = 1 if form.get("status") else 0
    with db.get_db() as conn:
        conn.execute(
            """UPDATE article SET title=?, cover_url=?, summary=?, content=?, content_type=?,
               app_file_url=?, status=?, updated_at=? WHERE id=?""",
            (title, cover, summary, content, ctype, app_url,
             status, db.now_str(), aid),
        )
        _save_article_parts(conn, aid, form)
        _save_article_attachments(conn, aid, form)
        db.log_action(conn, admin["username"], "编辑文章", title, client_ip(request))
    return RedirectResponse("/admin/articles", status_code=302)


@app.post("/admin/articles/{aid:int}/delete")
def admin_article_delete(aid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT title, cover_url, app_file_url FROM article WHERE id = ?", (aid,)).fetchone()
        if item:
            # 清理附件文件
            atts = conn.execute("SELECT url FROM article_attachment WHERE article_id = ?", (aid,)).fetchall()
            media_root = MEDIA_DIR.resolve()
            for a in atts:
                url = a["url"]
                if url.startswith("/media/"):
                    p = (MEDIA_DIR / url[len("/media/"):]).resolve()
                    if media_root in p.parents and p.is_file():
                        p.unlink()
            conn.execute("DELETE FROM article_part WHERE article_id = ?", (aid,))
            conn.execute("DELETE FROM article_attachment WHERE article_id = ?", (aid,))
            conn.execute("DELETE FROM article WHERE id = ?", (aid,))
            # 仅清理本站媒体封面与网页应用文件；正文图片可能被复用，不做删除
            for url in (item["cover_url"], item["app_file_url"]):
                if not url.startswith("/media/"):
                    continue
                p = (MEDIA_DIR / url[len("/media/"):]).resolve()
                if media_root in p.parents and p.is_file():
                    p.unlink()
            db.log_action(conn, admin["username"], "删除文章", item["title"], client_ip(request))
    return RedirectResponse("/admin/articles", status_code=302)


# ---------- 投稿员（上传软件 / 提交漏洞，查看自己的投稿） ----------

@app.get("/admin/my", response_class=HTMLResponse)
def my_home(request: Request, admin=Depends(require_admin)):
    with db.get_db() as conn:
        sw = conn.execute(
            """SELECT s.*, c.name AS category_name,
                      (SELECT COUNT(*) FROM software_file f WHERE f.software_id = s.id) AS file_count
               FROM software s LEFT JOIN category c ON c.id = s.category_id
               WHERE s.submitter_id = ? ORDER BY s.created_at DESC, s.id DESC""",
            (admin["id"],),
        ).fetchall()
        vulns = conn.execute(
            """SELECT v.*, (SELECT COUNT(*) FROM vulnerability_file f WHERE f.vulnerability_id = v.id) AS file_count
               FROM vulnerability v WHERE v.submitter_id = ? ORDER BY v.created_at DESC, v.id DESC""",
            (admin["id"],),
        ).fetchall()
        arts = conn.execute(
            "SELECT * FROM article WHERE submitter_id = ? ORDER BY created_at DESC, id DESC",
            (admin["id"],),
        ).fetchall()
    return templates.TemplateResponse(request, "admin_my.html", {
        "admin": admin, "sw": sw, "vulns": vulns, "arts": arts,
        "msg": request.query_params.get("msg", ""),
    })


def _sw_form_ctx(request, admin, item, files, cats, error="", tags_text="", submitter_mode=False, form=None):
    if submitter_mode:
        action = f"/admin/my/software/{item['id']}/edit" if item else "/admin/submit/software"
        cancel = "/admin/my"
        title = f"编辑投稿：{item['name']}" if item else "提交软件"
    else:
        action = f"/admin/software/{item['id']}/edit" if item else "/admin/software/add"
        cancel = "/admin/software"
        title = f"编辑软件：{item['name']}" if item else "添加软件"
    return templates.TemplateResponse(request, "admin_form.html", {
        "admin": admin, "item": item, "files": files, "cats": cats, "error": error,
        "tags_text": tags_text, "submitter_mode": submitter_mode, "form": form,
        "form_action": action, "cancel_url": cancel, "page_title": title,
    })


@app.get("/admin/submit/software", response_class=HTMLResponse)
def submit_sw_page(request: Request, admin=Depends(require_admin)):
    with db.get_db() as conn:
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
    return _sw_form_ctx(request, admin, None, [], cats, submitter_mode=True)


@app.post("/admin/submit/software")
async def submit_sw(request: Request, admin=Depends(require_admin),
                    name: str = Form(...), version: str = Form(""),
                    category_id: int = Form(0), description: str = Form(""),
                    remark: str = Form(""), tags: str = Form(""),
                    file_labels: List[str] = Form([]), file_paths: List[str] = Form([])):
    with db.get_db() as conn:
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
    try:
        collected = _collect_files(file_labels, file_paths, _form_uploads(await request.form(max_part_size=MAX_FORM_PART)))
        if not collected:
            raise ValueError("请至少添加一个软件包（上传或填写服务器路径）")
        now = db.now_str()
        with db.get_db() as conn:
            sw_id = conn.insert_returning_id(
                """INSERT INTO software (name, version, category_id, description, remark,
                   status, submitter_id, submitter_name, created_at, updated_at)
                   VALUES (?,?,?,?,?,2,?,?,?,?)""",
                (name.strip(), version.strip(), category_id or None, description,
                 remark.strip()[:255], admin["id"], admin["username"], now, now),
            )
            for i, (label, original, stored, size, stype) in enumerate(collected):
                conn.execute(
                    """INSERT INTO software_file (software_id, label, original_name, file_path,
                       file_size, source_type, sort_order, created_at) VALUES (?,?,?,?,?,?,?,?)""",
                    (sw_id, label, original, stored, size, stype, i, now),
                )
            sync_tags(conn, sw_id, parse_tags(tags))
            db.log_action(conn, admin["username"], "投稿软件", f"{name}（{len(collected)} 个文件）", client_ip(request))
        return RedirectResponse("/admin/my?msg=submitted", status_code=302)
    except ValueError as e:
        return _sw_form_ctx(request, admin, None, [], cats, str(e), tags, True,
                            {"name": name, "version": version, "category_id": category_id,
                             "description": description, "remark": remark})


def _load_own_draft(conn, table, rid, admin):
    """投稿员只能编辑自己的、且未上架的投稿（待审核或已驳回）"""
    row = conn.execute(f"SELECT * FROM {table} WHERE id = ?", (rid,)).fetchone()
    if not row or row["submitter_id"] != admin["id"]:
        raise HTTPException(404, "投稿不存在")
    if row["status"] == 1:
        raise HTTPException(400, "已发布的投稿不能编辑，请联系管理员")
    return row


@app.get("/admin/my/software/{sid:int}/edit", response_class=HTMLResponse)
def my_sw_edit_page(request: Request, sid: int, admin=Depends(require_admin)):
    with db.get_db() as conn:
        item = _load_own_draft(conn, "software", sid, admin)
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
        files = conn.execute(
            "SELECT * FROM software_file WHERE software_id = ? ORDER BY sort_order, id", (sid,)
        ).fetchall()
        tags_text = "，".join(software_tags(conn, sid))
    return _sw_form_ctx(request, admin, item, files, cats, "", tags_text, True)


@app.post("/admin/my/software/{sid:int}/edit")
async def my_sw_edit(request: Request, sid: int, admin=Depends(require_admin),
                     name: str = Form(...), version: str = Form(""),
                     category_id: int = Form(0), description: str = Form(""),
                     remark: str = Form(""), tags: str = Form(""),
                     existing_ids: List[int] = Form([]), existing_labels: List[str] = Form([]),
                     delete_ids: List[int] = Form([]),
                     file_labels: List[str] = Form([]), file_paths: List[str] = Form([])):
    with db.get_db() as conn:
        item = _load_own_draft(conn, "software", sid, admin)
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
        files = conn.execute(
            "SELECT * FROM software_file WHERE software_id = ? ORDER BY sort_order, id", (sid,)
        ).fetchall()
    try:
        collected = _collect_files(file_labels, file_paths, _form_uploads(await request.form(max_part_size=MAX_FORM_PART)))
        now = db.now_str()
        with db.get_db() as conn:
            for del_id in delete_ids:
                row = conn.execute(
                    "SELECT * FROM software_file WHERE id = ? AND software_id = ?", (del_id, sid)
                ).fetchone()
                if row:
                    conn.execute("DELETE FROM software_file WHERE id = ?", (del_id,))
                    if row["source_type"] == "upload":
                        p = Path(row["file_path"])
                        if p.is_file() and p.parent == config.UPLOAD_DIR:
                            p.unlink()
            label_map = dict(zip(existing_ids, existing_labels))
            for eid in existing_ids:
                if eid not in delete_ids:
                    conn.execute("UPDATE software_file SET label = ? WHERE id = ? AND software_id = ?",
                                 (label_map.get(eid, "安装包").strip() or "安装包", eid, sid))
            remain = conn.execute(
                "SELECT COALESCE(MAX(sort_order),-1) AS m FROM software_file WHERE software_id = ?", (sid,)
            ).fetchone()["m"]
            if not collected and conn.execute(
                    "SELECT COUNT(*) AS n FROM software_file WHERE software_id = ?", (sid,)).fetchone()["n"] == 0:
                raise ValueError("软件至少需要保留一个文件")
            for i, (label, original, stored, size, stype) in enumerate(collected):
                conn.execute(
                    """INSERT INTO software_file (software_id, label, original_name, file_path,
                       file_size, source_type, sort_order, created_at) VALUES (?,?,?,?,?,?,?,?)""",
                    (sid, label, original, stored, size, stype, remain + 1 + i, now),
                )
            # 保存后重新进入待审核
            conn.execute(
                """UPDATE software SET name=?, version=?, category_id=?, description=?, remark=?,
                   status=2, review_note='', updated_at=? WHERE id=?""",
                (name.strip(), version.strip(), category_id or None, description,
                 remark.strip()[:255], now, sid),
            )
            sync_tags(conn, sid, parse_tags(tags))
            db.log_action(conn, admin["username"], "编辑投稿(软件)", name, client_ip(request))
        return RedirectResponse("/admin/my?msg=submitted", status_code=302)
    except ValueError as e:
        return _sw_form_ctx(request, admin, item, files, cats, str(e), tags, True)


@app.get("/admin/submit/vuln", response_class=HTMLResponse)
def submit_vuln_page(request: Request, admin=Depends(require_admin)):
    return _vuln_form_ctx(request, admin, None, [], submitter_mode=True)


@app.post("/admin/submit/vuln")
async def submit_vuln(request: Request, admin=Depends(require_admin),
                      title: str = Form(...), cve_no: str = Form(""),
                      severity: str = Form("medium"), affected_product: str = Form(""),
                      affected_version: str = Form(""), official_url: str = Form(""),
                      description: str = Form(""),
                      file_labels: List[str] = Form([]), file_paths: List[str] = Form([])):
    try:
        collected = _collect_files(file_labels, file_paths, _form_uploads(await request.form(max_part_size=MAX_FORM_PART)))
        now = db.now_str()
        with db.get_db() as conn:
            vid = conn.insert_returning_id(
                """INSERT INTO vulnerability (title, cve_no, severity, affected_product, affected_version,
                   description, official_url, status, submitter_id, submitter_name, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,2,?,?,?,?)""",
                (title.strip()[:128], cve_no.strip()[:32], severity if severity in SEVERITIES else "medium",
                 affected_product.strip()[:128], affected_version.strip()[:128], description,
                 official_url.strip()[:500], admin["id"], admin["username"], now, now),
            )
            _save_vuln_files(conn, vid, collected, now)
            db.log_action(conn, admin["username"], "投稿漏洞", title, client_ip(request))
        return RedirectResponse("/admin/my?msg=submitted", status_code=302)
    except ValueError as e:
        return _vuln_form_ctx(request, admin, None, [], str(e), True, {
            "title": title, "cve_no": cve_no, "severity": severity,
            "affected_product": affected_product, "affected_version": affected_version,
            "official_url": official_url, "description": description,
        })


@app.get("/admin/my/vuln/{vid:int}/edit", response_class=HTMLResponse)
def my_vuln_edit_page(request: Request, vid: int, admin=Depends(require_admin)):
    with db.get_db() as conn:
        item = _load_own_draft(conn, "vulnerability", vid, admin)
        files = _vuln_files(conn, vid)
    return _vuln_form_ctx(request, admin, item, files, submitter_mode=True)


@app.post("/admin/my/vuln/{vid:int}/edit")
async def my_vuln_edit(request: Request, vid: int, admin=Depends(require_admin),
                       title: str = Form(...), cve_no: str = Form(""),
                       severity: str = Form("medium"), affected_product: str = Form(""),
                       affected_version: str = Form(""), official_url: str = Form(""),
                       description: str = Form(""),
                       existing_ids: List[int] = Form([]), existing_labels: List[str] = Form([]),
                       delete_ids: List[int] = Form([]),
                       file_labels: List[str] = Form([]), file_paths: List[str] = Form([])):
    with db.get_db() as conn:
        item = _load_own_draft(conn, "vulnerability", vid, admin)
        files = _vuln_files(conn, vid)
    try:
        collected = _collect_files(file_labels, file_paths, _form_uploads(await request.form(max_part_size=MAX_FORM_PART)))
        now = db.now_str()
        with db.get_db() as conn:
            next_order = _update_vuln_files(conn, vid, existing_ids, existing_labels, delete_ids)
            _save_vuln_files(conn, vid, collected, now, next_order)
            # 保存后重新进入待审核
            conn.execute(
                """UPDATE vulnerability SET title=?, cve_no=?, severity=?, affected_product=?,
                   affected_version=?, description=?, official_url=?, status=2, review_note='', updated_at=?
                   WHERE id=?""",
                (title.strip()[:128], cve_no.strip()[:32], severity if severity in SEVERITIES else "medium",
                 affected_product.strip()[:128], affected_version.strip()[:128], description,
                 official_url.strip()[:500], now, vid),
            )
            db.log_action(conn, admin["username"], "编辑投稿(漏洞)", title, client_ip(request))
        return RedirectResponse("/admin/my?msg=submitted", status_code=302)
    except ValueError as e:
        return _vuln_form_ctx(request, admin, item, files, str(e), True)


# ---------- 投稿员：技术文章 ----------

@app.get("/admin/submit/article", response_class=HTMLResponse)
def submit_article_page(request: Request, admin=Depends(require_admin)):
    return _article_form_ctx(request, admin, None, submitter_mode=True)


@app.post("/admin/submit/article")
async def submit_article(request: Request, admin=Depends(require_admin)):
    form = await request.form(max_part_size=MAX_FORM_PART)
    try:
        title, cover, summary, content, ctype, app_url = _article_fields(form)
    except ValueError as e:
        return _article_form_ctx(request, admin, {"title": form.get("title") or ""},
                                 str(e), submitter_mode=True)
    if not title:
        return _article_form_ctx(request, admin, {"title": title}, "标题不能为空", submitter_mode=True)
    now = db.now_str()
    with db.get_db() as conn:
        aid = conn.insert_returning_id(
            """INSERT INTO article (title, cover_url, summary, content, content_type, app_file_url,
               status, submitter_id, submitter_name, created_at, updated_at)
               VALUES (?,?,?,?,?,?,2,?,?,?,?)""",
            (title, cover, summary, content, ctype, app_url,
             admin["id"], admin["username"], now, now),
        )
        _save_article_parts(conn, aid, form)
        _save_article_attachments(conn, aid, form)
        db.log_action(conn, admin["username"], "提交文章", title, client_ip(request))
    return RedirectResponse("/admin/my?msg=submitted", status_code=302)


@app.get("/admin/my/article/{aid:int}/edit", response_class=HTMLResponse)
def my_article_edit_page(request: Request, aid: int, admin=Depends(require_admin)):
    with db.get_db() as conn:
        item = _load_own_draft(conn, "article", aid, admin)
    return _article_form_ctx(request, admin, item, submitter_mode=True)


@app.post("/admin/my/article/{aid:int}/edit")
async def my_article_edit(request: Request, aid: int, admin=Depends(require_admin)):
    form = await request.form(max_part_size=MAX_FORM_PART)
    with db.get_db() as conn:
        item = _load_own_draft(conn, "article", aid, admin)
    try:
        title, cover, summary, content, ctype, app_url = _article_fields(form)
    except ValueError as e:
        return _article_form_ctx(request, admin, dict(item), str(e), submitter_mode=True)
    if not title:
        return _article_form_ctx(request, admin, dict(item), "标题不能为空", submitter_mode=True)
    # 保存后重新进入待审核
    with db.get_db() as conn:
        conn.execute(
            """UPDATE article SET title=?, cover_url=?, summary=?, content=?, content_type=?,
               app_file_url=?, status=2, review_note='', updated_at=? WHERE id=?""",
            (title, cover, summary, content, ctype, app_url,
             db.now_str(), aid),
        )
        _save_article_parts(conn, aid, form)
        _save_article_attachments(conn, aid, form)
        db.log_action(conn, admin["username"], "编辑投稿(文章)", title, client_ip(request))
    return RedirectResponse("/admin/my?msg=submitted", status_code=302)