import csv
import hashlib
import io
import json
import re
import uuid
from datetime import datetime, timedelta
from functools import lru_cache
from pathlib import Path
from typing import List
from urllib.parse import quote

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, JSONResponse, PlainTextResponse,
                              RedirectResponse, Response)
from fastapi.staticfiles import StaticFiles
from fastapi.templating import Jinja2Templates
from markupsafe import Markup, escape

import auth
import captcha
import config
import db
import dllimit
import docimport
import epub_reader
import idcard

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["app_version"] = config.APP_VERSION
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"]))


def _admin_categories():
    """后台侧栏“软件分类筛选”用，所有后台页面按需调用"""
    with db.get_db() as conn:
        return conn.execute("SELECT id, name FROM category ORDER BY sort_order, id").fetchall()


templates.env.globals["admin_categories"] = _admin_categories


@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


def require_approved(request: Request):
    """已通过审核的账号才能上传/投稿；待审核或已驳回的投稿员查看自己的状态页。
    投稿员状态不影响管理员账号（role=admin 始终放行）。"""
    admin = require_admin(request)
    if admin.get("role") != "admin" and admin.get("approval_status", "approved") != "approved":
        raise HTTPException(status_code=303, headers={"Location": "/admin/my?msg=pending"})
    return admin


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

# 软件授权方式：值 -> 中文名
LICENSE_TYPES = {
    "open_free": "开源免费软件",
    "free_activation": "免费授权激活软件",
    "no_activation_closed": "免激活使用闭源软件",
    "paid": "付费授权激活软件",
    "cracked": "破解逆向软件",
}


def license_label(value: str) -> str:
    return LICENSE_TYPES.get(value or "", value or "")


templates.env.filters["license_label"] = license_label
templates.env.globals["license_types"] = LICENSE_TYPES


def _int_setting(key: str, default: int) -> int:
    try:
        return int(str(db.get_settings().get(key, default)).strip())
    except (TypeError, ValueError):
        return default


def allowed_license_values() -> list:
    """投稿员可选授权方式。管理员在站点设置里勾选；全部不勾选视为不限制。"""
    raw = str(db.get_settings().get("submitter_license_allowed") or "")
    allowed = [v for v in raw.split(",") if v in LICENSE_TYPES]
    return allowed or list(LICENSE_TYPES)


def license_options(for_submitter: bool) -> list:
    values = allowed_license_values() if for_submitter else list(LICENSE_TYPES)
    return [{"value": v, "label": LICENSE_TYPES[v]} for v in values]


def validate_software_meta(developer: str, license_type: str, for_submitter: bool):
    """校验开发者与授权方式，返回 (developer, license_type)。"""
    developer = (developer or "").strip()[:128]
    if not developer:
        raise ValueError("请填写软件开发者 / 软件上传分享者")
    license_type = (license_type or "").strip()
    if license_type not in LICENSE_TYPES:
        raise ValueError("请选择软件授权方式")
    if for_submitter and license_type not in allowed_license_values():
        raise ValueError("该授权方式当前未开放投稿，请选择其他授权方式")
    return developer, license_type


def submitter_quota(conn, admin, software_id=None, exclude_file_ids=None):
    """投稿员配额：单个文件上限、单个软件项目的总容量上限、该项目已用字节数。

    总容量按“一个软件项目下所有上传文件合计”计算，不是账号累计。"""
    single_mb = max(1, _int_setting("submitter_max_file_mb", 100))
    total_mb = max(1, _int_setting("submitter_total_mb", 500))
    used = 0
    if software_id:
        sql = """SELECT COALESCE(SUM(file_size), 0) AS n FROM software_file
                 WHERE software_id = ? AND source_type = 'upload'"""
        params = [software_id]
        exclude = [int(i) for i in (exclude_file_ids or [])]
        if exclude:
            sql += " AND id NOT IN (" + ",".join("?" * len(exclude)) + ")"
            params.extend(exclude)
        used = conn.execute(sql, params).fetchone()["n"]
    return single_mb, total_mb, int(used or 0)


def rating_summary(conn, software_id: int):
    """按维度聚合平均星级与评分人数。"""
    dims = conn.execute(
        "SELECT * FROM rating_dimension WHERE visible = 1 ORDER BY sort_order, id"
    ).fetchall()
    rows = conn.execute(
        """SELECT dimension_id, COUNT(*) AS n, AVG(score) AS avg_score
           FROM software_rating WHERE software_id = ? GROUP BY dimension_id""",
        (software_id,),
    ).fetchall()
    agg = {r["dimension_id"]: r for r in rows}
    out = []
    for d in dims:
        a = agg.get(d["id"])
        out.append({
            "id": d["id"], "name": d["name"],
            "avg": round(float(a["avg_score"]), 1) if a and a["n"] else 0.0,
            "count": int(a["n"]) if a else 0,
        })
    return out


def _rater_key(request: Request, visitor_id: str = "") -> str:
    """同一访客（cookie）+ IP + UA 的哈希，用于限制重复评分。"""
    vid = visitor_id or request.cookies.get("visitor_id", "")
    ip = client_ip(request)
    ua = request.headers.get("user-agent", "")[:200]
    return hashlib.sha256(f"{vid}|{ip}|{ua}".encode("utf-8")).hexdigest()[:40]


_COMMENT_IMG_RE = re.compile(r"!\[[^\]]*\]\((/media/comments/[A-Za-z0-9_.-]+)\)")


def render_comment(text: str) -> Markup:
    """评论内容默认纯文本转义，仅把本站评论图片的 Markdown 语法转成 <img>。"""
    esc = str(escape(text or ""))
    esc = _COMMENT_IMG_RE.sub(
        lambda m: f'<img src="{m.group(1)}" alt="评论图片" class="comment-img">', esc
    )
    return Markup(esc.replace("\r\n", "\n").replace("\n", "<br>"))


templates.env.filters["comment_html"] = render_comment


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"
    suffix = Path(name).suffix
    fallback = re.sub(r'["\\\\]', "_", name.encode("ascii", "ignore").decode("ascii")).strip()
    if not fallback or fallback == suffix:
        fallback = "download" + suffix
    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


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

# 可设为默认主页的前台页面（key, 显示名, 访问路径）
HOME_PAGES = [
    ("software", "软件下载", "/software"),
    ("announcements", "公告栏", "/announcements"),
    ("articles", "技术文章", "/articles"),
    ("nav", "网址导航", "/nav"),
    ("vulns", "漏洞管理", "/vulns"),
    ("videos", "教学视频", "/videos"),
    ("photos", "图片素材", "/photos"),
    ("music", "音乐", "/music"),
    ("ebooks", "电子书", "/ebooks"),
    ("board", "留言板", "/board"),
    ("stats", "软件统计总览", "/stats"),
]
HOME_PAGE_ROUTES = {key: url for key, _, url in HOME_PAGES}


@app.get("/", response_class=HTMLResponse)
def index(request: Request, category: int = 0, q: str = "", tag: str = ""):
    """站点根路径：按后台“默认主页”设置跳转；带搜索/筛选参数时始终展示软件列表。"""
    if resp := maintenance_guard(request):
        return resp
    if not (q or tag or category):
        target = HOME_PAGE_ROUTES.get(db.get_settings().get("default_home", "software"), "/software")
        if target != "/software":
            return RedirectResponse(target, status_code=302)
    return software_page(request, category, q, tag)


@app.get("/software", response_class=HTMLResponse)
def software_page(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)
        ratings = rating_summary(conn, sid)
        vid = request.cookies.get("visitor_id") or uuid.uuid4().hex
        mine = {
            r["dimension_id"]: r["score"] for r in conn.execute(
                "SELECT dimension_id, score FROM software_rating WHERE software_id = ? AND rater_key = ?",
                (sid, _rater_key(request, vid)),
            ).fetchall()
        }
    # 组两层楼中楼：顶层评论 + 各自的回复；回复的父级若已不可见（被驳回/删除）则降级为顶层
    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]
    resp = 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", ""),
        "ratings": ratings, "mine": mine,
        "rating_total": sum(d["count"] for d in ratings),
    })
    if not request.cookies.get("visitor_id"):
        resp.set_cookie("visitor_id", vid, max_age=86400 * 365, httponly=True, samesite="lax")
    return resp


@app.post("/software/{sid:int}/rate")
def software_rate(request: Request, sid: int,
                  dimension_id: int = Form(...), score: int = Form(...)):
    score = max(1, min(5, int(score)))
    vid = request.cookies.get("visitor_id", "")
    key = _rater_key(request, vid)
    with db.get_db() as conn:
        sw = conn.execute("SELECT id FROM software WHERE id = ? AND status = 1", (sid,)).fetchone()
        if not sw:
            raise HTTPException(404, "软件不存在或已下架")
        dim = conn.execute(
            "SELECT id FROM rating_dimension WHERE id = ? AND visible = 1", (dimension_id,)
        ).fetchone()
        if not dim:
            raise HTTPException(400, "评价维度不存在")
        row = conn.execute(
            "SELECT id FROM software_rating WHERE software_id = ? AND dimension_id = ? AND rater_key = ?",
            (sid, dimension_id, key),
        ).fetchone()
        now = db.now_str()
        if row:
            conn.execute("UPDATE software_rating SET score = ?, created_at = ? WHERE id = ?",
                         (score, now, row["id"]))
        else:
            conn.execute(
                """INSERT INTO software_rating (software_id, dimension_id, rater_key, score, created_at)
                   VALUES (?,?,?,?,?)""",
                (sid, dimension_id, key, score, now),
            )
        ratings = rating_summary(conn, sid)
        for d in ratings:
            d["mine"] = mine_score(conn, sid, d["id"], key)
    resp = JSONResponse({"ok": True, "dimensions": ratings, "score": score})
    if not vid:
        resp.set_cookie("visitor_id", uuid.uuid4().hex, max_age=86400 * 365, httponly=True, samesite="lax")
    return resp


def mine_score(conn, software_id: int, dimension_id: int, rater_key: str) -> int:
    row = conn.execute(
        "SELECT score FROM software_rating WHERE software_id = ? AND dimension_id = ? AND rater_key = ?",
        (software_id, dimension_id, rater_key),
    ).fetchone()
    return int(row["score"]) if row else 0


@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


MAX_COMMENT_IMAGE_SIZE = 5 * 1024 * 1024
COMMENT_IMAGE_DIR = config.UPLOAD_DIR / "media" / "comments"


@app.post("/comment/image")
async def comment_image(request: Request):
    """评论配图上传：公开接口，限图片类型 5MB，按 IP 每小时 20 张。"""
    if db.get_settings().get("allow_comment") != "1":
        raise HTTPException(403, "评论功能已关闭")
    ip = client_ip(request)
    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_image WHERE client_ip = ? AND created_at >= ?",
            (ip, since),
        ).fetchone()["n"]
        if n >= 20:
            raise HTTPException(429, "上传图片过于频繁，请稍后再试")
    form = await request.form(max_part_size=MAX_FORM_PART)
    up = form.get("file") or form.get("image")
    if not up or not getattr(up, "filename", ""):
        raise HTTPException(400, "没有收到图片")
    ext = Path(safe_filename(up.filename)).suffix.lower()
    if ext not in {".jpg", ".jpeg", ".png", ".gif", ".webp"}:
        raise HTTPException(400, "仅支持 jpg / png / gif / webp 图片")
    COMMENT_IMAGE_DIR.mkdir(parents=True, exist_ok=True)
    dest = COMMENT_IMAGE_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 > MAX_COMMENT_IMAGE_SIZE:
                f.close()
                dest.unlink(missing_ok=True)
                raise HTTPException(400, "图片不能超过 5MB")
            f.write(chunk)
    with db.get_db() as conn:
        conn.execute("INSERT INTO comment_image (client_ip, created_at) VALUES (?,?)",
                     (ip, db.now_str()))
    return JSONResponse({"ok": True, "url": f"/media/comments/{dest.name}"})


@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",
}
AUDIO_EXTS = {".mp3", ".wav", ".ogg", ".m4a", ".aac", ".flac"}
MEDIA_EXTS = IMAGE_EXTS | VIDEO_EXTS | APP_EXTS | ATTACH_EXTS | AUDIO_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",
    ".mp3": "audio/mpeg", ".wav": "audio/wav", ".ogg": "audio/ogg", ".m4a": "audio/mp4",
    ".aac": "audio/aac", ".flac": "audio/flac",
    ".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"
VIDEO_DIR = MEDIA_DIR / "videos"
VIDEO_EXTS = {".mp4", ".webm", ".m4v", ".mov"}


def _store_teach_video(up):
    """保存后台上传的教学视频，返回 (原始文件名, 媒体相对路径, 字节数)。"""
    if not up or not getattr(up, "filename", ""):
        raise ValueError("请选择视频文件")
    original = safe_filename(up.filename)
    ext = Path(original).suffix.lower()
    if ext not in VIDEO_EXTS:
        raise ValueError("仅支持 mp4 / webm / m4v / mov 格式视频")
    VIDEO_DIR.mkdir(parents=True, exist_ok=True)
    dest = VIDEO_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 > config.MAX_UPLOAD_SIZE:
                f.close()
                dest.unlink(missing_ok=True)
                raise ValueError(f"视频超过上传大小限制（{human_size(config.MAX_UPLOAD_SIZE)}）")
            f.write(chunk)
    return original, f"videos/{dest.name}", size


def _delete_teach_video(file_path: str):
    """仅删除 media 目录内的教学视频文件，防止路径穿越。"""
    if not file_path:
        return
    media_root = MEDIA_DIR.resolve()
    target = (MEDIA_DIR / file_path).resolve()
    if media_root not in target.parents:
        return
    try:
        target.unlink(missing_ok=True)
    except OSError:
        pass


PHOTO_DIR = MEDIA_DIR / "photos"
PHOTO_EXTS = {".jpg", ".jpeg", ".png", ".gif", ".webp"}


def _store_teach_photo(up):
    """保存后台上传的图片素材，返回 (原始文件名, 媒体相对路径, 字节数)。"""
    if not up or not getattr(up, "filename", ""):
        raise ValueError("请选择图片文件")
    original = safe_filename(up.filename)
    ext = Path(original).suffix.lower()
    if ext not in PHOTO_EXTS:
        raise ValueError("仅支持 jpg / jpeg / png / gif / webp 格式图片")
    PHOTO_DIR.mkdir(parents=True, exist_ok=True)
    dest = PHOTO_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 > MAX_IMAGE_SIZE:
                f.close()
                dest.unlink(missing_ok=True)
                raise ValueError(f"图片超过上传大小限制（{human_size(MAX_IMAGE_SIZE)}）")
            f.write(chunk)
    return original, f"photos/{dest.name}", size


def _delete_teach_photo(file_path: str):
    """仅删除 media 目录内的图片素材，防止路径穿越。"""
    if not file_path:
        return
    media_root = MEDIA_DIR.resolve()
    target = (MEDIA_DIR / file_path).resolve()
    if media_root not in target.parents:
        return
    try:
        target.unlink(missing_ok=True)
    except OSError:
        pass


BGM_DIR = MEDIA_DIR / "bgm"
MUSIC_DIR = MEDIA_DIR / "music"
EBOOK_DIR = MEDIA_DIR / "ebooks"
MAX_AUDIO_SIZE = 100 * 1024 * 1024
MAX_EBOOK_SIZE = 500 * 1024 * 1024
EBOOK_EXTS = {".pdf", ".epub"}

ANNOUNCEMENT_DOC_EXTS = {".pdf", ".docx"}
MAX_ANNOUNCEMENT_DOC_SIZE = 100 * 1024 * 1024


def _announcement_doc_html(rel_path: str) -> str:
    """把已保存的 docx 转换成网页可直接查看的 HTML，返回相对路径；转换失败返回空串。"""
    if not rel_path:
        return ""
    src = MEDIA_DIR / rel_path
    try:
        import mammoth
        with open(src, "rb") as f:
            body = mammoth.convert_to_html(f).value
        dest = src.with_suffix(".html")
        dest.write_text(
            '<!DOCTYPE html><html lang="zh-CN"><head><meta charset="utf-8">'
            '<meta name="viewport" content="width=device-width,initial-scale=1">'
            '<style>body{margin:0;padding:26px 30px;font-family:"PingFang SC",'
            '"Microsoft YaHei","Noto Sans CJK SC",sans-serif;line-height:1.75;'
            'color:#1f2430;font-size:15px;}img{max-width:100%;height:auto;}'
            'table{border-collapse:collapse;width:100%;margin:10px 0;}'
            'td,th{border:1px solid #d7dce8;padding:6px 10px;text-align:left;}'
            'h1,h2,h3{line-height:1.4;margin:18px 0 10px;}p{margin:0 0 10px;}'
            'blockquote{border-left:4px solid #4f6ef7;margin:0 0 10px;padding-left:12px;color:#7a8299;}'
            '</style></head><body>' + body + '</body></html>',
            encoding="utf-8",
        )
        return f"announcements/{dest.name}"
    except Exception as e:  # 转换失败时保留原文档下载，不影响公告保存
        print(f"[warn] 公告 docx 转换失败：{e}")
        return ""


def _store_media_upload(up, subdir: str, allowed_exts: set, max_size: int, kind_label: str):
    """保存后台上传的媒体文件，返回 (原始文件名, 媒体相对路径, 字节数)。"""
    if not up or not getattr(up, "filename", ""):
        raise ValueError(f"请选择{kind_label}文件")
    original = safe_filename(up.filename)
    ext = Path(original).suffix.lower()
    if ext not in allowed_exts:
        raise ValueError(f"{kind_label}格式不支持，仅支持 {' / '.join(sorted(e.lstrip('.') for e in allowed_exts))}")
    folder = MEDIA_DIR / subdir
    folder.mkdir(parents=True, exist_ok=True)
    dest = folder / 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 > max_size:
                f.close()
                dest.unlink(missing_ok=True)
                raise ValueError(f"{kind_label}超过上传大小限制（{human_size(max_size)}）")
            f.write(chunk)
    return original, f"{subdir}/{dest.name}", size


def _delete_media_file(file_path: str):
    """仅删除 media 目录内的文件，防止路径穿越。"""
    if not file_path:
        return
    media_root = MEDIA_DIR.resolve()
    target = (MEDIA_DIR / file_path).resolve()
    if media_root not in target.parents:
        return
    try:
        target.unlink(missing_ok=True)
    except OSError:
        pass


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"
    # 允许调用方通过 name 指定下载文件名（如音乐标题），并补回原文件扩展名
    filename = (request.query_params.get("name") or "").strip()
    if filename:
        filename = re.sub(r'[\x00-\x1f\x7f/\\]', "_", filename).strip(" .")[:150]
        if filename and path.suffix and not filename.lower().endswith(path.suffix.lower()):
            filename += path.suffix
    # 附件目录下的文件一律下载，不渲染（HTML/MHTML 附件防止 XSS）
    is_attachment = "attachments" in path.parts
    attach = download or is_attachment
    # 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_disposition(filename) if (attach and filename)
            else ("attachment" if attach else "inline"))
        return resp
    disposition_type = "attachment" if attach else "inline"
    resp = FileResponse(path, media_type=mime, content_disposition_type=disposition_type,
                        filename=filename if (attach and filename) else None)
    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, approval_status 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, "未登录或上传令牌无效")
    if admin.get("role") != "admin" and admin.get("approval_status", "approved") != "approved":
        raise HTTPException(403, "账号尚未通过管理员审核，暂不能上传内容")
    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_approved)):
    """文章附件上传：白名单类型，保存到 /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_approved)):
    """技术文章编辑框「导入 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_approved)):
    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_approved)):
    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_approved)):
    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_approved)):
    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("/videos", response_class=HTMLResponse)
def videos_page(request: Request):
    """教学视频播放页：按板块分组，循环/随机/点击播放只在当前板块内生效。"""
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM teach_video_category WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        rows = conn.execute(
            "SELECT * FROM teach_video WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()

    grouped = {}
    for r in rows:
        grouped.setdefault(int(r["category_id"] or 0), []).append({
            "id": r["id"], "title": r["title"], "description": r["description"],
            "url": f"/media/{r['file_path']}", "size_text": human_size(r["file_size"]),
        })

    sections = []
    for c in cats:
        items = grouped.get(int(c["id"]), [])
        if items:
            sections.append({"id": int(c["id"]), "name": c["name"], "videos": items})
    if grouped.get(0):
        sections.append({"id": 0, "name": "未分类", "videos": grouped[0]})

    return templates.TemplateResponse(request, "videos.html", {
        "sections": sections, "video_count": sum(len(s["videos"]) for s in sections),
    })


@app.get("/photos", response_class=HTMLResponse)
def photos_page(request: Request):
    """图片素材页：按板块分组，循环/随机/单张/全屏播放都只作用于当前板块。"""
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM photo_category WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        rows = conn.execute(
            "SELECT * FROM photo WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()

    grouped = {}
    for r in rows:
        grouped.setdefault(int(r["category_id"] or 0), []).append({
            "id": r["id"], "title": r["title"], "description": r["description"],
            "url": f"/media/{r['file_path']}", "size_text": human_size(r["file_size"]),
        })

    sections = []
    for c in cats:
        items = grouped.get(int(c["id"]), [])
        if items:
            sections.append({
                "id": int(c["id"]), "name": c["name"], "photos": items,
                "bgm": f"/media/{c['bgm_path']}" if c["bgm_path"] else "",
            })
    if grouped.get(0):
        sections.append({"id": 0, "name": "未分类", "photos": grouped[0], "bgm": ""})

    return templates.TemplateResponse(request, "photos.html", {
        "sections": sections, "photo_count": sum(len(s["photos"]) for s in sections),
    })


WEEKDAY_NAMES = ("周一", "周二", "周三", "周四", "周五", "周六", "周日")
PLAY_MODE_TEXT = {"loop": "列表循环", "random": "随机播放", "single": "单曲循环"}


def _weekdays_text(raw: str) -> str:
    """把 '1,2,3,4,5' 转成“工作日 / 周末 / 每天 / 周一、周三”这样的说明。"""
    days = sorted({int(d) for d in str(raw or "").split(",") if d.strip().isdigit()})
    if not days:
        return "未设置"
    if days == [1, 2, 3, 4, 5]:
        return "工作日"
    if days == [1, 2, 3, 4, 5, 6, 7]:
        return "每天"
    if days == [6, 7]:
        return "周末"
    return "、".join(WEEKDAY_NAMES[d - 1] for d in days if 1 <= d <= 7)


def _schedule_tracks(conn, category_id: int, album_id: int) -> list:
    """定时广播的曲目：按分类（album_id=0）或分类下某个专辑取已上架音乐。"""
    sql = "SELECT * FROM music WHERE visible = 1 AND category_id = ?"
    params = [category_id]
    if album_id:
        sql += " AND album_id = ?"
        params.append(album_id)
    sql += " ORDER BY sort_order, id"
    return [{
        "title": r["title"], "description": r["description"] or "",
        "url": f"/media/{r['file_path']}",
        "download": f"/media/{r['file_path']}?download=1&name={quote(r['title'] or '', safe='')}",
    } for r in conn.execute(sql, tuple(params)).fetchall()]


@app.get("/music", response_class=HTMLResponse)
def music_page(request: Request, cat: int = -1, album: int = 0):
    """音乐页：分类下再按专辑筛选，支持列表循环 / 随机 / 单曲播放与下载。"""
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM music_category WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        albums = conn.execute(
            "SELECT * FROM music_album WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        rows = conn.execute(
            "SELECT * FROM music WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        schedules = conn.execute(
            """SELECT s.*, c.name AS category_name, a.name AS album_name
               FROM music_schedule s
               LEFT JOIN music_category c ON c.id = s.category_id
               LEFT JOIN music_album a ON a.id = s.album_id
               WHERE s.enabled = 1
               ORDER BY s.start_time, s.id"""
        ).fetchall()

    grouped = {}
    for r in rows:
        grouped.setdefault(int(r["category_id"] or 0), []).append({
            "id": r["id"], "title": r["title"], "description": r["description"],
            "album_id": int(r["album_id"] or 0),
            "url": f"/media/{r['file_path']}",
            "download_url": f"/media/{r['file_path']}?download=1&name={quote(r['title'] or '', safe='')}",
            "size_text": human_size(r["file_size"]),
        })

    def albums_of(cid: int, items: list) -> list:
        used = {i["album_id"] for i in items}
        return [{"id": int(a["id"]), "name": a["name"]}
                for a in albums
                if int(a["category_id"] or 0) == cid and int(a["id"]) in used]

    sections = []
    for c in cats:
        cid = int(c["id"])
        items = grouped.get(cid, [])
        if items:
            sections.append({"id": cid, "name": c["name"], "items": items,
                             "albums": albums_of(cid, items)})
    if grouped.get(0):
        sections.append({"id": 0, "name": "未分类", "items": grouped[0],
                         "albums": albums_of(0, grouped[0])})

    current = next((s for s in sections if s["id"] == cat), None)
    if not current and album:
        current = next(
            (s for s in sections if any(a["id"] == album for a in s["albums"])), None)
    current = current or (sections[0] if sections else None)
    selected_album = 0
    selected_album_name = ""
    if current and any(a["id"] == album for a in current["albums"]):
        selected_album = album
        selected_album_name = next(a["name"] for a in current["albums"] if a["id"] == album)
    items = current["items"] if current else []
    if selected_album:
        items = [i for i in items if i["album_id"] == selected_album]

    return templates.TemplateResponse(request, "music.html", {
        "sections": sections, "current": current, "items": items,
        "selected_album": selected_album, "selected_album_name": selected_album_name,
        "music_count": sum(len(s["items"]) for s in sections),
        "schedules": [{
            "title": s["title"], "start_time": s["start_time"], "end_time": s["end_time"],
            "weekday_text": _weekdays_text(s["weekdays"]),
            "target": s["album_name"] or s["category_name"] or "未分类",
            "mode_text": PLAY_MODE_TEXT.get(s["play_mode"], "列表循环"),
        } for s in schedules],
    })


@app.get("/api/music/schedule")
def api_music_schedule():
    """前台定时广播用：返回启用的计划及其实时曲目（仅已上架音乐）。"""
    with db.get_db() as conn:
        rows = conn.execute(
            """SELECT s.*, c.name AS category_name, a.name AS album_name
               FROM music_schedule s
               LEFT JOIN music_category c ON c.id = s.category_id
               LEFT JOIN music_album a ON a.id = s.album_id
               WHERE s.enabled = 1
               ORDER BY s.start_time, s.id"""
        ).fetchall()
        schedules = []
        for s in rows:
            tracks = _schedule_tracks(conn, int(s["category_id"] or 0), int(s["album_id"] or 0))
            schedules.append({
                "id": int(s["id"]),
                "title": s["title"],
                "category_id": int(s["category_id"] or 0),
                "album_id": int(s["album_id"] or 0),
                "category_name": s["category_name"] or "",
                "album_name": s["album_name"] or "",
                "weekdays": [int(d) for d in str(s["weekdays"]).split(",") if d.strip().isdigit()],
                "start": s["start_time"],
                "end": s["end_time"],
                "mode": s["play_mode"] if s["play_mode"] in PLAY_MODE_TEXT else "loop",
                "tracks": tracks,
            })
    return JSONResponse({"schedules": schedules})



@app.get("/ebooks", response_class=HTMLResponse)
def ebooks_page(request: Request):
    """电子书页：按分类分组，PDF / EPUB 均支持在线阅读与下载。"""
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM ebook_category WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        rows = conn.execute(
            "SELECT * FROM ebook WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()

    grouped = {}
    for r in rows:
        grouped.setdefault(int(r["category_id"] or 0), []).append({
            "id": r["id"], "title": r["title"], "description": r["description"],
            "format": (r["format"] or "").upper(),
            "size_text": human_size(r["file_size"]),
            "read_url": f"/ebooks/{r['id']}/read",
            "download_url": f"/media/{r['file_path']}?download=1",
        })

    sections = []
    for c in cats:
        items = grouped.get(int(c["id"]), [])
        if items:
            sections.append({"id": int(c["id"]), "name": c["name"], "items": items})
    if grouped.get(0):
        sections.append({"id": 0, "name": "未分类", "items": grouped[0]})

    return templates.TemplateResponse(request, "ebooks.html", {
        "sections": sections, "ebook_count": sum(len(s["items"]) for s in sections),
    })


def _get_visible_ebook(eid: int):
    with db.get_db() as conn:
        row = conn.execute(
            "SELECT * FROM ebook WHERE id = ? AND visible = 1", (eid,)
        ).fetchone()
    if not row:
        raise HTTPException(404, "电子书不存在或已下架")
    return row


@lru_cache(maxsize=32)
def _load_epub(path_str: str, mtime_ns: int, ebook_id: int):
    return epub_reader.EpubBook(path_str, ebook_id)


def _ebook_book(row):
    path = (MEDIA_DIR / row["file_path"]).resolve()
    if not path.is_file():
        raise epub_reader.EpubError("电子书文件不存在")
    return _load_epub(str(path), path.stat().st_mtime_ns, int(row["id"]))


@app.get("/ebooks/{eid:int}/read", response_class=HTMLResponse)
def ebook_read(request: Request, eid: int):
    """阅读页：PDF 用浏览器内置阅读器，EPUB 用服务端解析出的章节阅读器。"""
    if resp := maintenance_guard(request):
        return resp
    row = _get_visible_ebook(eid)
    pdf_url, chapters, ebook_title = "", [], row["title"]
    if (row["format"] or "").lower() == "pdf":
        pdf_url = f"/media/{row['file_path']}"
    else:
        try:
            book = _ebook_book(row)
        except epub_reader.EpubError as exc:
            raise HTTPException(404, str(exc))
        ebook_title = book.title or row["title"]
        chapters = [{"index": i, "title": c["title"]} for i, c in enumerate(book.chapters)]
    return templates.TemplateResponse(request, "ebook_reader.html", {
        "item": row, "pdf_url": pdf_url, "chapters": chapters,
        "ebook_title": ebook_title,
        "download_url": f"/media/{row['file_path']}?download=1",
    })


@app.get("/ebooks/{eid:int}/chapter/{idx:int}", response_class=HTMLResponse)
def ebook_chapter(request: Request, eid: int, idx: int):
    if resp := maintenance_guard(request):
        return resp
    row = _get_visible_ebook(eid)
    if (row["format"] or "").lower() != "epub":
        raise HTTPException(404)
    try:
        book = _ebook_book(row)
        _title, html = book.read_chapter(idx)
    except (epub_reader.EpubError, IndexError):
        raise HTTPException(404, "章节不存在")
    resp = HTMLResponse(html)
    resp.headers["Content-Security-Policy"] = "script-src 'none'; object-src 'none'; base-uri 'none'"
    resp.headers["X-Content-Type-Options"] = "nosniff"
    resp.headers["Cache-Control"] = "private, max-age=60"
    return resp


@app.get("/ebooks/{eid:int}/asset/{rel:path}")
def ebook_asset(request: Request, eid: int, rel: str):
    if resp := maintenance_guard(request):
        return resp
    row = _get_visible_ebook(eid)
    if (row["format"] or "").lower() != "epub":
        raise HTTPException(404)
    try:
        book = _ebook_book(row)
        data, mime = book.read_asset(rel)
    except (epub_reader.EpubError, KeyError):
        raise HTTPException(404)
    resp = Response(data, media_type=mime)
    resp.headers["X-Content-Type-Options"] = "nosniff"
    resp.headers["Cache-Control"] = "public, max-age=3600"
    if mime.startswith("text/html") or mime == "image/svg+xml":
        resp.headers["Content-Security-Policy"] = "script-src 'none'; object-src 'none'; base-uri 'none'"
    return resp


BOARD_KINDS = {"message": "留言", "requirement": "软件需求"}
BOARD_MSG_TEXT = {
    "pending": "留言已提交，管理员审核通过后会公开展示。",
    "captcha": "验证码错误或已过期，请重新输入。",
    "empty": "留言内容不能为空。",
    "limit": "提交过于频繁，请稍后再试。",
    "blocked": "当前 IP 已被限制，无法留言。",
}


@app.get("/board", response_class=HTMLResponse)
def board_page(request: Request):
    """留言板：公开已审核通过的留言与管理员回复。"""
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        rows = conn.execute(
            """SELECT * FROM board_message WHERE status = 'approved'
               ORDER BY created_at DESC, id DESC LIMIT 200"""
        ).fetchall()
        announcements = conn.execute(
            """SELECT id, title, created_at, doc_type FROM announcement
               WHERE visible = 1 ORDER BY created_at DESC, id DESC LIMIT 5"""
        ).fetchall()
    return templates.TemplateResponse(request, "board.html", {
        "rows": rows, "kinds": BOARD_KINDS, "announcements": announcements,
        "msg": BOARD_MSG_TEXT.get(request.query_params.get("msg", ""), ""),
    })


@app.post("/board")
def board_submit(request: Request, nickname: str = Form(""), contact: str = Form(""),
                kind: str = Form("message"), content: str = Form(""),
                captcha_code: str = Form("")):
    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("/board?msg=captcha", status_code=302)
    content = content.strip()[:2000]
    if not content:
        return RedirectResponse("/board?msg=empty", status_code=302)
    if kind not in BOARD_KINDS:
        kind = "message"
    with db.get_db() as conn:
        if db.is_ip_blocked(conn, ip):
            return RedirectResponse("/board?msg=blocked", status_code=302)
        since = (datetime.now() - timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
        n = conn.execute(
            "SELECT COUNT(*) AS n FROM board_message WHERE client_ip = ? AND created_at >= ?",
            (ip, since),
        ).fetchone()["n"]
        if n >= 5:
            return RedirectResponse("/board?msg=limit", status_code=302)
        conn.execute(
            """INSERT INTO board_message (nickname, contact, kind, content, status,
               client_ip, created_at) VALUES (?,?,?,?, 'pending', ?, ?)""",
            ((nickname.strip() or "匿名网友")[:32], contact.strip()[:64], kind,
             content, ip, db.now_str()),
        )
    resp = RedirectResponse("/board?msg=pending", status_code=302)
    resp.delete_cookie(captcha.COOKIE_NAME)
    return resp


@app.get("/announcements", response_class=HTMLResponse)
def announcements_page(request: Request):
    """公告栏：展示管理员已发布的公告，最新在前。"""
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        rows = conn.execute(
            """SELECT id, title, content, doc_name, doc_type, created_at, updated_at
               FROM announcement WHERE visible = 1
               ORDER BY created_at DESC, id DESC LIMIT 100"""
        ).fetchall()
    items = [{"row": r, "summary": plain_text(r["content"])[:120]} for r in rows]
    return templates.TemplateResponse(request, "announcements.html", {"items": items})


@app.get("/announcements/{aid:int}", response_class=HTMLResponse)
def announcement_detail(request: Request, aid: int):
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        row = conn.execute(
            "SELECT * FROM announcement WHERE id = ? AND visible = 1", (aid,)
        ).fetchone()
    if not row:
        raise HTTPException(404)
    return templates.TemplateResponse(request, "announcement_detail.html", {
        "item": row, "body_html": render_md(row["content"] or ""),
    })


@app.get("/stats", response_class=HTMLResponse)
def stats_page(request: Request):
    """按下载量与多维度评分生成统计图（服务端渲染，不依赖外部图表库）。"""
    if resp := maintenance_guard(request):
        return resp
    with db.get_db() as conn:
        dims = conn.execute(
            "SELECT * FROM rating_dimension WHERE visible = 1 ORDER BY sort_order, id"
        ).fetchall()
        sw_rows = conn.execute(
            "SELECT id, name, download_count FROM software WHERE status = 1"
        ).fetchall()
        rating_rows = conn.execute(
            """SELECT software_id, dimension_id, COUNT(*) AS n, AVG(score) AS avg_score
               FROM software_rating GROUP BY software_id, dimension_id"""
        ).fetchall()
        dim_agg = conn.execute(
            """SELECT dimension_id, COUNT(*) AS n, AVG(score) AS avg_score
               FROM software_rating GROUP BY dimension_id"""
        ).fetchall()
    rating_map = {(r["software_id"], r["dimension_id"]): r for r in rating_rows}
    dim_map = {r["dimension_id"]: r for r in dim_agg}
    items = []
    for s in sw_rows:
        per, votes, weighted = [], 0, 0.0
        for d in dims:
            a = rating_map.get((s["id"], d["id"]))
            avg = round(float(a["avg_score"]), 1) if a and a["n"] else 0.0
            n = int(a["n"]) if a else 0
            per.append({"name": d["name"], "avg": avg, "count": n})
            votes += n
            weighted += avg * n
        items.append({
            "id": s["id"], "name": s["name"],
            "downloads": int(s["download_count"] or 0), "per": per,
            "rating_count": votes,
            "overall": round(weighted / votes, 1) if votes else 0.0,
        })
    items.sort(key=lambda x: x["downloads"], reverse=True)
    max_dl = max([i["downloads"] for i in items] or [0]) or 1
    top_dl = items[:10]
    top_rated = sorted(
        [i for i in items if i["rating_count"] > 0],
        key=lambda x: (-x["overall"], -x["rating_count"]),
    )[:10]
    dim_summary = []
    for d in dims:
        a = dim_map.get(d["id"])
        dim_summary.append({
            "name": d["name"],
            "avg": round(float(a["avg_score"]), 1) if a and a["n"] else 0.0,
            "count": int(a["n"]) if a else 0,
        })
    return templates.TemplateResponse(request, "stats.html", {
        "items": items, "top_dl": top_dl, "top_rated": top_rated,
        "dim_summary": dim_summary, "max_dl": max_dl,
        "total_downloads": sum(i["downloads"] for i in items),
        "total_ratings": sum(d["count"] for d in dim_summary),
    })


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

@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


# ---------- 投稿员自助注册 ----------

REGISTER_LIMIT_PER_HOUR = 10


def _register_ctx(request: Request, error: str = "", form: dict = None, ok: bool = False, status_code: int = 200):
    return templates.TemplateResponse(
        request, "register.html",
        {"error": error, "form": form or {}, "ok": ok},
        status_code=status_code,
    )


@app.get("/admin/register", response_class=HTMLResponse)
def register_page(request: Request):
    return _register_ctx(request)


@app.post("/admin/register")
def register(request: Request, username: str = Form(...), password: str = Form(...),
             id_card: str = Form(...), unit_dept: str = Form(...)):
    ip = client_ip(request)
    now_dt = datetime.now()
    now = db.now_str()
    hour_ago = (now_dt - timedelta(hours=1)).strftime("%Y-%m-%d %H:%M:%S")
    with db.get_db() as conn:
        conn.execute("INSERT INTO register_attempt (ip, created_at) VALUES (?, ?)", (ip, now))
        attempts = conn.execute(
            "SELECT COUNT(*) AS n FROM register_attempt WHERE ip = ? AND created_at >= ?",
            (ip, hour_ago),
        ).fetchone()["n"]
    if attempts > REGISTER_LIMIT_PER_HOUR:
        form = {"username": username.strip(), "id_card": id_card.strip(), "unit_dept": unit_dept.strip()}
        return _register_ctx(request, "注册请求过于频繁，请 1 小时后再试", form, status_code=429)

    username = username.strip()
    unit_dept = unit_dept.strip()
    form = {"username": username, "id_card": id_card.strip().upper(), "unit_dept": unit_dept}
    if not 2 <= len(username) <= 64 or any(ch.isspace() for ch in username):
        return _register_ctx(request, "账号需为 2-64 个字符，且不能包含空格", form, status_code=400)
    if not 6 <= len(password) <= 128:
        return _register_ctx(request, "密码长度需为 6-128 位", form, status_code=400)
    if not 2 <= len(unit_dept) <= 128:
        return _register_ctx(request, "请填写单位/部门（2-128 个字符）", form, status_code=400)
    err = idcard.validate(id_card)
    if err:
        return _register_ctx(request, err, form, status_code=400)

    card = idcard.normalize(id_card)
    card_hash = idcard.digest(card)
    with db.get_db() as conn:
        if conn.execute("SELECT 1 AS x FROM admin WHERE username = ?", (username,)).fetchone():
            return _register_ctx(request, "该账号已被注册，请更换账号", form, status_code=409)
        if conn.execute("SELECT 1 AS x FROM admin WHERE id_card_hash = ?", (card_hash,)).fetchone():
            return _register_ctx(request, "该身份证号已注册过投稿员账号", form, status_code=409)
        cur = conn.insert_ignore(
            """INSERT INTO admin (username, password_hash, role, disabled, upload_token,
               id_card_hash, id_card_mask, unit_dept, approval_status, approval_note, reviewed_at, created_at)
               VALUES (?, ?, 'uploader', 0, '', ?, ?, ?, 'pending', '', '', ?)""",
            (username, auth.hash_password(password), card_hash, idcard.mask(card), unit_dept, now),
        )
        if cur.rowcount == 0:
            return _register_ctx(request, "该账号或身份证号已被注册", form, status_code=409)
        db.log_action(conn, username, "注册投稿员", f"单位={unit_dept}，待审核", ip)
    return _register_ctx(request, ok=True)


@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 = "", category: int = 0):
    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 = []
        conds: list = []
        if q:
            conds.append("s.name LIKE ?")
            params.append(f"%{q}%")
        if category:
            conds.append("s.category_id = ?")
            params.append(category)
        if conds:
            sql += " WHERE " + " AND ".join(conds)
        sql += " ORDER BY s.pin_weight DESC, s.created_at DESC"
        rows = conn.execute(sql, params).fetchall()
        cats = conn.execute("SELECT id, name FROM category ORDER BY sort_order, id").fetchall()
    cur_cat_name = next((c["name"] for c in cats if c["id"] == category), "")
    return templates.TemplateResponse(request, "admin_software.html", {
        "admin": admin, "items": rows, "q": q,
        "cats": cats, "cur_cat": category, "cur_cat_name": cur_cat_name,
    })


@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), category: int = Form(0)):
    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))
    target = f"/admin/software?category={category}" if category else "/admin/software"
    return RedirectResponse(target, status_code=302)


@app.post("/admin/software/{sid:int}/recommend")
def admin_recommend(sid: int, request: Request, admin=Depends(require_full_admin), category: int = Form(0)):
    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))
    target = f"/admin/software?category={category}" if category else "/admin/software"
    return RedirectResponse(target, 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,
                   max_file_bytes=None, remaining_bytes=None):
    """按行收集软件包：每行三选一——服务器路径 / 下载链接（外链跳转）/ 上传文件。
    浏览器对留空文本框也会提交空值，labels/paths/links 与行号一一对应；
    文件框未选择时整体缺名，靠 files_<行号> 对齐。
    max_file_bytes / remaining_bytes 用于投稿员配额校验（仅对上传文件生效）。"""
    out = []
    total = 0
    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("文件超过上传大小限制")
                    if max_file_bytes and size > max_file_bytes:
                        f.close()
                        dest.unlink(missing_ok=True)
                        raise ValueError(f"单个文件不能超过 {human_size(max_file_bytes)}")
                    if remaining_bytes is not None and total + size > remaining_bytes:
                        f.close()
                        dest.unlink(missing_ok=True)
                        raise ValueError("本软件的上传文件总容量已超出限额，请删除部分文件或联系管理员调整限额")
                    f.write(chunk)
            total += size
            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(""),
                    developer: str = Form(""), license_type: 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:
        developer, license_type = validate_software_meta(developer, license_type, False)
        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, developer, license_type, category_id, description,
                   remark, recommended, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?)""",
                (name.strip(), version.strip(), developer, license_type, 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, "developer": developer,
                            "license_type": license_type,
                            "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(""),
                     developer: str = Form(""), license_type: 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:
        developer, license_type = validate_software_meta(developer, license_type, False)
        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=?, developer=?, license_type=?, category_id=?,
                   description=?, remark=?, recommended=?, status=?, updated_at=? WHERE id=?""",
                (name.strip(), version.strip(), developer, license_type, 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), category: int = Form(0)):
    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))
    target = f"/admin/software?category={category}" if category else "/admin/software"
    return RedirectResponse(target, 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(0), 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(0), 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(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("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 _announcement_error(message) -> RedirectResponse:
    from urllib.parse import quote
    return RedirectResponse(f"/admin/announcements?error={quote(str(message))}", status_code=302)


@app.get("/admin/announcements", response_class=HTMLResponse)
def admin_announcements(request: Request, error: str = "", saved: int = 0,
                        admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        rows = conn.execute(
            "SELECT * FROM announcement ORDER BY created_at DESC, id DESC"
        ).fetchall()
    return templates.TemplateResponse(request, "admin_announcements.html", {
        "admin": admin, "rows": rows, "error": error, "saved": saved,
        "max_size": human_size(MAX_ANNOUNCEMENT_DOC_SIZE),
    })


@app.post("/admin/announcements/add")
async def admin_announcement_add(request: Request, admin=Depends(require_full_admin),
                                 title: str = Form(...), content: str = Form(""),
                                 visible: int = Form(0)):
    try:
        title = title.strip()[:200]
        if not title:
            raise ValueError("公告标题不能为空")
        form = await request.form(max_part_size=MAX_FORM_PART)
        up = form.get("file")
        original = rel_path = doc_html = doc_type = ""
        size = 0
        if up and getattr(up, "filename", ""):
            original, rel_path, size = _store_media_upload(
                up, "announcements", ANNOUNCEMENT_DOC_EXTS,
                MAX_ANNOUNCEMENT_DOC_SIZE, "公告附件")
            doc_type = Path(original).suffix.lower().lstrip(".")
            if doc_type == "docx":
                doc_html = _announcement_doc_html(rel_path)
        now = db.now_str()
        with db.get_db() as conn:
            conn.execute(
                """INSERT INTO announcement (title, content, doc_path, doc_name, doc_size,
                   doc_type, doc_html, visible, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?)""",
                (title, content.strip(), rel_path, original, size, doc_type, doc_html,
                 1 if visible else 0, now, now),
            )
            db.log_action(conn, admin["username"], "发布公告", title, client_ip(request))
    except ValueError as e:
        return _announcement_error(e)
    return RedirectResponse("/admin/announcements?saved=1", status_code=302)


@app.post("/admin/announcements/{aid:int}/update")
async def admin_announcement_update(request: Request, aid: int,
                                    admin=Depends(require_full_admin),
                                    title: str = Form(...), content: str = Form(""),
                                    visible: int = Form(0), remove_doc: int = Form(0)):
    try:
        title = title.strip()[:200]
        if not title:
            raise ValueError("公告标题不能为空")
        with db.get_db() as conn:
            item = conn.execute("SELECT * FROM announcement WHERE id = ?", (aid,)).fetchone()
        if not item:
            raise ValueError("公告不存在")
        form = await request.form(max_part_size=MAX_FORM_PART)
        up = form.get("file")
        new_original = new_rel = new_html = new_type = ""
        new_size = 0
        if up and getattr(up, "filename", ""):
            new_original, new_rel, new_size = _store_media_upload(
                up, "announcements", ANNOUNCEMENT_DOC_EXTS,
                MAX_ANNOUNCEMENT_DOC_SIZE, "公告附件")
            new_type = Path(new_original).suffix.lower().lstrip(".")
            if new_type == "docx":
                new_html = _announcement_doc_html(new_rel)
        now = db.now_str()
        if new_rel:
            doc_path, doc_name, doc_size, doc_type, doc_html = (
                new_rel, new_original, new_size, new_type, new_html)
        elif remove_doc:
            doc_path = doc_name = doc_html = doc_type = ""
            doc_size = 0
        else:
            doc_path, doc_name, doc_size, doc_type, doc_html = (
                item["doc_path"], item["doc_name"], item["doc_size"],
                item["doc_type"], item["doc_html"])
        with db.get_db() as conn:
            conn.execute(
                """UPDATE announcement SET title=?, content=?, doc_path=?, doc_name=?,
                   doc_size=?, doc_type=?, doc_html=?, visible=?, updated_at=? WHERE id=?""",
                (title, content.strip(), doc_path, doc_name, doc_size, doc_type,
                 doc_html, 1 if visible else 0, now, aid),
            )
            db.log_action(conn, admin["username"], "编辑公告", title, client_ip(request))
        if (new_rel or remove_doc) and item["doc_path"]:
            _delete_media_file(item["doc_path"])
            _delete_media_file(item["doc_html"])
    except ValueError as e:
        return _announcement_error(e)
    return RedirectResponse("/admin/announcements?saved=1", status_code=302)


@app.post("/admin/announcements/{aid:int}/delete")
def admin_announcement_delete(request: Request, aid: int,
                              admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute(
            "SELECT title, doc_path, doc_html FROM announcement WHERE id = ?", (aid,)
        ).fetchone()
        if item:
            conn.execute("DELETE FROM announcement WHERE id = ?", (aid,))
            db.log_action(conn, admin["username"], "删除公告", item["title"],
                          client_ip(request))
    if item:
        _delete_media_file(item["doc_path"])
        _delete_media_file(item["doc_html"])
    return RedirectResponse("/admin/announcements?saved=1", 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,
        "home_pages": HOME_PAGES,
    })


@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),
                        submitter_max_file_mb: int = Form(100),
                        submitter_total_mb: int = Form(500),
                        front_view_mode: str = Form("card"),
                        default_home: str = Form("software"),
                        license_allowed: List[str] = Form([])):
    doc_max_mb = max(1, min(doc_max_mb, 2048))
    if default_home not in HOME_PAGE_ROUTES:
        default_home = "software"
    submitter_max_file_mb = max(1, min(submitter_max_file_mb, 2048))
    submitter_total_mb = max(1, min(submitter_total_mb, 10240))
    if front_view_mode not in ("card", "list"):
        front_view_mode = "card"
    # 勾选项过滤为合法值；一个都不勾选视为“不限制”，保存全部授权方式
    chosen = [v for v in license_allowed if v in LICENSE_TYPES] or list(LICENSE_TYPES)
    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),
        "submitter_max_file_mb": str(submitter_max_file_mb),
        "submitter_total_mb": str(submitter_total_mb),
        "front_view_mode": front_view_mode,
        "default_home": default_home,
        "submitter_license_allowed": ",".join(chosen),
    }
    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]} 默认主页={default_home} "
                      f"维护模式={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/approvals", response_class=HTMLResponse)
def admin_approvals(request: Request, admin=Depends(require_full_admin), error: str = ""):
    with db.get_db() as conn:
        pending = conn.execute(
            """SELECT id, username, id_card_mask, unit_dept, created_at
               FROM admin WHERE role = 'uploader' AND approval_status = 'pending'
               ORDER BY created_at ASC, id ASC"""
        ).fetchall()
        processed = conn.execute(
            """SELECT id, username, id_card_mask, unit_dept, approval_status, approval_note, reviewed_at
               FROM admin WHERE role = 'uploader' AND id_card_hash IS NOT NULL
                 AND approval_status IN ('approved', 'rejected')
               ORDER BY reviewed_at DESC, id DESC LIMIT 30"""
        ).fetchall()
    return templates.TemplateResponse(
        request, "admin_approvals.html",
        {"admin": admin, "active": "approvals", "pending": pending, "processed": processed, "error": error},
    )


@app.post("/admin/approvals/{uid:int}/approve")
def admin_approval_approve(uid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute(
            "SELECT username, approval_status FROM admin WHERE id = ? AND role = 'uploader'", (uid,)
        ).fetchone()
        if not row:
            return RedirectResponse("/admin/approvals?error=账号不存在", status_code=302)
        if row["approval_status"] != "pending":
            return RedirectResponse("/admin/approvals?error=该申请已处理，请勿重复审核", status_code=302)
        conn.execute(
            "UPDATE admin SET approval_status = 'approved', approval_note = '', reviewed_at = ? WHERE id = ?",
            (db.now_str(), uid),
        )
        db.log_action(conn, admin["username"], "审核通过投稿员", row["username"], client_ip(request))
    return RedirectResponse("/admin/approvals", status_code=302)


@app.post("/admin/approvals/{uid:int}/reject")
def admin_approval_reject(uid: int, request: Request, note: str = Form(""),
                          admin=Depends(require_full_admin)):
    from urllib.parse import quote
    note = (note or "").strip()[:255]
    if not note:
        return RedirectResponse(
            "/admin/approvals?error=" + quote("驳回时必须填写理由"), status_code=302
        )
    with db.get_db() as conn:
        row = conn.execute(
            "SELECT username, approval_status FROM admin WHERE id = ? AND role = 'uploader'", (uid,)
        ).fetchone()
        if not row:
            return RedirectResponse("/admin/approvals?error=账号不存在", status_code=302)
        if row["approval_status"] != "pending":
            return RedirectResponse("/admin/approvals?error=该申请已处理，请勿重复审核", status_code=302)
        conn.execute(
            "UPDATE admin SET approval_status = 'rejected', approval_note = ?, reviewed_at = ? WHERE id = ?",
            (note, db.now_str(), uid),
        )
        db.log_action(conn, admin["username"], "驳回投稿员申请", f"{row['username']}：{note}", client_ip(request))
    return RedirectResponse("/admin/approvals", status_code=302)


@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/videos", response_class=HTMLResponse)
def admin_videos(request: Request, error: str = "", admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM teach_video_category ORDER BY sort_order, id"
        ).fetchall()
        rows = conn.execute(
            """SELECT v.*, c.name AS category_name FROM teach_video v
               LEFT JOIN teach_video_category c ON c.id = v.category_id
               ORDER BY COALESCE(c.sort_order, 999999), COALESCE(c.id, 999999),
                        v.sort_order, v.id"""
        ).fetchall()
        counts = {
            r["category_id"]: r["n"] for r in conn.execute(
                "SELECT category_id, COUNT(*) AS n FROM teach_video GROUP BY category_id"
            ).fetchall()
        }
    return templates.TemplateResponse(request, "admin_videos.html", {
        "admin": admin, "rows": rows, "cats": cats, "counts": counts, "error": error,
        "max_size": human_size(config.MAX_UPLOAD_SIZE),
    })


def _validate_video_category(conn, category_id: int) -> int:
    """板块必须存在；0 表示未分类。"""
    if category_id and not conn.execute(
        "SELECT 1 FROM teach_video_category WHERE id = ?", (category_id,)
    ).fetchone():
        raise ValueError("所属板块不存在")
    return category_id


@app.post("/admin/videos/category/add")
def admin_video_cat_add(request: Request, admin=Depends(require_full_admin),
                        name: str = Form(...), sort_order: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _video_error("板块名称不能为空")
    now = db.now_str()
    with db.get_db() as conn:
        conn.execute(
            """INSERT INTO teach_video_category (name, sort_order, visible, created_at, updated_at)
               VALUES (?,?,?,?,?)""",
            (name, sort_order, 1, now, now),
        )
        db.log_action(conn, admin["username"], "新增教学视频板块", name, client_ip(request))
    return RedirectResponse("/admin/videos", status_code=302)


@app.post("/admin/videos/category/{cid:int}/update")
def admin_video_cat_update(cid: int, request: Request, admin=Depends(require_full_admin),
                           name: str = Form(...), visible: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _video_error("板块名称不能为空")
    with db.get_db() as conn:
        if not conn.execute("SELECT 1 FROM teach_video_category WHERE id = ?", (cid,)).fetchone():
            return _video_error("板块不存在")
        conn.execute(
            "UPDATE teach_video_category SET name=?, visible=?, updated_at=? WHERE id=?",
            (name, 1 if visible else 0, db.now_str(), cid),
        )
        db.log_action(conn, admin["username"], "编辑教学视频板块", name, client_ip(request))
    return RedirectResponse("/admin/videos", status_code=302)


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


@app.post("/admin/videos/category/{cid:int}/delete")
def admin_video_cat_delete(cid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT name FROM teach_video_category WHERE id = ?", (cid,)).fetchone()
        n = conn.execute("SELECT COUNT(*) AS n FROM teach_video WHERE category_id = ?", (cid,)).fetchone()["n"]
        if n:
            return _video_error(f"板块【{row['name'] if row else cid}】下还有 {n} 个视频，请先移动或删除后再删除板块")
        conn.execute("DELETE FROM teach_video_category WHERE id = ?", (cid,))
        if row:
            db.log_action(conn, admin["username"], "删除教学视频板块", row["name"], client_ip(request))
    return RedirectResponse("/admin/videos", status_code=302)


@app.post("/admin/videos/add")
async def admin_video_add(request: Request, admin=Depends(require_full_admin),
                          title: str = Form(...), description: str = Form(""),
                          category_id: int = Form(0),
                          sort_order: int = Form(0), visible: int = Form(0)):
    try:
        title = title.strip()[:200]
        if not title:
            raise ValueError("视频标题不能为空")
        with db.get_db() as conn:
            category_id = _validate_video_category(conn, category_id)
        form = await request.form(max_part_size=MAX_FORM_PART)
        original, rel_path, size = _store_teach_video(form.get("file"))
        now = db.now_str()
        with db.get_db() as conn:
            conn.execute(
                """INSERT INTO teach_video (title, description, original_name, file_path,
                   file_size, sort_order, visible, category_id, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?)""",
                (title, description.strip()[:2000], original, rel_path, size,
                 sort_order, 1 if visible else 0, category_id, now, now),
            )
            db.log_action(conn, admin["username"], "上传教学视频", title, client_ip(request))
    except ValueError as e:
        return _video_error(e)
    return RedirectResponse("/admin/videos", status_code=302)


@app.post("/admin/videos/{vid:int}/update")
async def admin_video_update(request: Request, vid: int, admin=Depends(require_full_admin),
                             title: str = Form(...), description: str = Form(""),
                             category_id: int = Form(0),
                             sort_order: int = Form(0), visible: int = Form(0)):
    try:
        with db.get_db() as conn:
            item = conn.execute("SELECT * FROM teach_video WHERE id = ?", (vid,)).fetchone()
            if not item:
                raise ValueError("视频不存在")
            category_id = _validate_video_category(conn, category_id)
        title = title.strip()[:200]
        if not title:
            raise ValueError("视频标题不能为空")
        form = await request.form(max_part_size=MAX_FORM_PART)
        up = form.get("file")
        new_original = new_rel = ""
        new_size = 0
        if up and getattr(up, "filename", ""):
            new_original, new_rel, new_size = _store_teach_video(up)
        now = db.now_str()
        with db.get_db() as conn:
            if new_rel:
                conn.execute(
                    """UPDATE teach_video SET title=?, description=?, original_name=?, file_path=?,
                       file_size=?, sort_order=?, visible=?, category_id=?, updated_at=? WHERE id=?""",
                    (title, description.strip()[:2000], new_original, new_rel, new_size,
                     sort_order, 1 if visible else 0, category_id, now, vid),
                )
            else:
                conn.execute(
                    """UPDATE teach_video SET title=?, description=?, sort_order=?, visible=?,
                       category_id=?, updated_at=? WHERE id=?""",
                    (title, description.strip()[:2000], sort_order,
                     1 if visible else 0, category_id, now, vid),
                )
            db.log_action(conn, admin["username"], "编辑教学视频", title, client_ip(request))
        if new_rel and new_rel != item["file_path"]:
            _delete_teach_video(item["file_path"])
    except ValueError as e:
        return _video_error(e)
    return RedirectResponse("/admin/videos", status_code=302)


@app.post("/admin/videos/{vid:int}/move")
def admin_video_move(request: Request, vid: int, direction: str = Form(...),
                     admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT category_id FROM teach_video WHERE id = ?", (vid,)).fetchone()
        if not item:
            return _video_error("视频不存在")
        _move_rows(conn, "teach_video", vid, direction,
                   "WHERE category_id = ?", (item["category_id"],))
        db.log_action(conn, admin["username"], "调整教学视频排序", str(vid), client_ip(request))
    return RedirectResponse("/admin/videos", status_code=302)


@app.post("/admin/videos/{vid:int}/delete")
def admin_video_delete(request: Request, vid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT title, file_path FROM teach_video WHERE id = ?", (vid,)).fetchone()
        if item:
            conn.execute("DELETE FROM teach_video WHERE id = ?", (vid,))
            db.log_action(conn, admin["username"], "删除教学视频", item["title"], client_ip(request))
    if item:
        _delete_teach_video(item["file_path"])
    return RedirectResponse("/admin/videos", status_code=302)


def _video_error(message) -> RedirectResponse:
    from urllib.parse import quote
    return RedirectResponse(f"/admin/videos?error={quote(str(message))}", status_code=302)


@app.get("/admin/photos", response_class=HTMLResponse)
def admin_photos(request: Request, error: str = "", admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = conn.execute(
            "SELECT * FROM photo_category ORDER BY sort_order, id"
        ).fetchall()
        rows = conn.execute(
            """SELECT p.*, c.name AS category_name FROM photo p
               LEFT JOIN photo_category c ON c.id = p.category_id
               ORDER BY COALESCE(c.sort_order, 999999), COALESCE(c.id, 999999),
                        p.sort_order, p.id"""
        ).fetchall()
        counts = {
            r["category_id"]: r["n"] for r in conn.execute(
                "SELECT category_id, COUNT(*) AS n FROM photo GROUP BY category_id"
            ).fetchall()
        }
    return templates.TemplateResponse(request, "admin_photos.html", {
        "admin": admin, "rows": rows, "cats": cats, "counts": counts, "error": error,
        "max_size": human_size(MAX_IMAGE_SIZE),
    })


def _validate_photo_category(conn, category_id: int) -> int:
    """板块必须存在；0 表示未分类。"""
    if category_id and not conn.execute(
        "SELECT 1 FROM photo_category WHERE id = ?", (category_id,)
    ).fetchone():
        raise ValueError("所属板块不存在")
    return category_id


@app.post("/admin/photos/category/add")
def admin_photo_cat_add(request: Request, admin=Depends(require_full_admin),
                        name: str = Form(...), sort_order: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _photo_error("板块名称不能为空")
    now = db.now_str()
    with db.get_db() as conn:
        conn.execute(
            """INSERT INTO photo_category (name, sort_order, visible, created_at, updated_at)
               VALUES (?,?,?,?,?)""",
            (name, sort_order, 1, now, now),
        )
        db.log_action(conn, admin["username"], "新增图片素材板块", name, client_ip(request))
    return RedirectResponse("/admin/photos", status_code=302)


@app.post("/admin/photos/category/{cid:int}/update")
def admin_photo_cat_update(cid: int, request: Request, admin=Depends(require_full_admin),
                           name: str = Form(...), visible: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _photo_error("板块名称不能为空")
    with db.get_db() as conn:
        if not conn.execute("SELECT 1 FROM photo_category WHERE id = ?", (cid,)).fetchone():
            return _photo_error("板块不存在")
        conn.execute(
            "UPDATE photo_category SET name=?, visible=?, updated_at=? WHERE id=?",
            (name, 1 if visible else 0, db.now_str(), cid),
        )
        db.log_action(conn, admin["username"], "编辑图片素材板块", name, client_ip(request))
    return RedirectResponse("/admin/photos", status_code=302)


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


@app.post("/admin/photos/category/{cid:int}/delete")
def admin_photo_cat_delete(cid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT * FROM photo_category WHERE id = ?", (cid,)).fetchone()
        n = conn.execute("SELECT COUNT(*) AS n FROM photo WHERE category_id = ?", (cid,)).fetchone()["n"]
        if n:
            return _photo_error(f"板块【{row['name'] if row else cid}】下还有 {n} 张图片，请先移动或删除后再删除板块")
        conn.execute("DELETE FROM photo_category WHERE id = ?", (cid,))
        if row:
            db.log_action(conn, admin["username"], "删除图片素材板块", row["name"], client_ip(request))
    if row:
        _delete_media_file(row["bgm_path"])
    return RedirectResponse("/admin/photos", status_code=302)


@app.post("/admin/photos/add")
async def admin_photo_add(request: Request, admin=Depends(require_full_admin),
                          title: str = Form(...), description: str = Form(""),
                          category_id: int = Form(0),
                          sort_order: int = Form(0), visible: int = Form(0)):
    try:
        title = title.strip()[:200]
        if not title:
            raise ValueError("图片标题不能为空")
        with db.get_db() as conn:
            category_id = _validate_photo_category(conn, category_id)
        form = await request.form(max_part_size=MAX_FORM_PART)
        original, rel_path, size = _store_teach_photo(form.get("file"))
        now = db.now_str()
        with db.get_db() as conn:
            conn.execute(
                """INSERT INTO photo (title, description, original_name, file_path,
                   file_size, sort_order, visible, category_id, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?)""",
                (title, description.strip()[:2000], original, rel_path, size,
                 sort_order, 1 if visible else 0, category_id, now, now),
            )
            db.log_action(conn, admin["username"], "上传图片素材", title, client_ip(request))
    except ValueError as e:
        return _photo_error(e)
    return RedirectResponse("/admin/photos", status_code=302)


@app.post("/admin/photos/{pid:int}/update")
async def admin_photo_update(request: Request, pid: int, admin=Depends(require_full_admin),
                             title: str = Form(...), description: str = Form(""),
                             category_id: int = Form(0),
                             sort_order: int = Form(0), visible: int = Form(0)):
    try:
        with db.get_db() as conn:
            item = conn.execute("SELECT * FROM photo WHERE id = ?", (pid,)).fetchone()
            if not item:
                raise ValueError("图片不存在")
            category_id = _validate_photo_category(conn, category_id)
        title = title.strip()[:200]
        if not title:
            raise ValueError("图片标题不能为空")
        form = await request.form(max_part_size=MAX_FORM_PART)
        up = form.get("file")
        new_original = new_rel = ""
        new_size = 0
        if up and getattr(up, "filename", ""):
            new_original, new_rel, new_size = _store_teach_photo(up)
        now = db.now_str()
        with db.get_db() as conn:
            if new_rel:
                conn.execute(
                    """UPDATE photo SET title=?, description=?, original_name=?, file_path=?,
                       file_size=?, sort_order=?, visible=?, category_id=?, updated_at=? WHERE id=?""",
                    (title, description.strip()[:2000], new_original, new_rel, new_size,
                     sort_order, 1 if visible else 0, category_id, now, pid),
                )
            else:
                conn.execute(
                    """UPDATE photo SET title=?, description=?, sort_order=?, visible=?,
                       category_id=?, updated_at=? WHERE id=?""",
                    (title, description.strip()[:2000], sort_order,
                     1 if visible else 0, category_id, now, pid),
                )
            db.log_action(conn, admin["username"], "编辑图片素材", title, client_ip(request))
        if new_rel and new_rel != item["file_path"]:
            _delete_teach_photo(item["file_path"])
    except ValueError as e:
        return _photo_error(e)
    return RedirectResponse("/admin/photos", status_code=302)


@app.post("/admin/photos/{pid:int}/move")
def admin_photo_move(request: Request, pid: int, direction: str = Form(...),
                     admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT category_id FROM photo WHERE id = ?", (pid,)).fetchone()
        if not item:
            return _photo_error("图片不存在")
        _move_rows(conn, "photo", pid, direction,
                   "WHERE category_id = ?", (item["category_id"],))
        db.log_action(conn, admin["username"], "调整图片素材排序", str(pid), client_ip(request))
    return RedirectResponse("/admin/photos", status_code=302)


@app.post("/admin/photos/{pid:int}/delete")
def admin_photo_delete(request: Request, pid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT title, file_path FROM photo WHERE id = ?", (pid,)).fetchone()
        if item:
            conn.execute("DELETE FROM photo WHERE id = ?", (pid,))
            db.log_action(conn, admin["username"], "删除图片素材", item["title"], client_ip(request))
    if item:
        _delete_teach_photo(item["file_path"])
    return RedirectResponse("/admin/photos", status_code=302)


def _photo_error(message) -> RedirectResponse:
    from urllib.parse import quote
    return RedirectResponse(f"/admin/photos?error={quote(str(message))}", status_code=302)


# ---------- 图片素材板块背景音乐 ----------

@app.post("/admin/photos/category/{cid:int}/bgm")
async def admin_photo_cat_bgm(cid: int, request: Request,
                              admin=Depends(require_full_admin)):
    try:
        with db.get_db() as conn:
            cat = conn.execute("SELECT * FROM photo_category WHERE id = ?", (cid,)).fetchone()
            if not cat:
                raise ValueError("板块不存在")
        form = await request.form(max_part_size=MAX_FORM_PART)
        original, rel_path, size = _store_media_upload(
            form.get("file"), "bgm", AUDIO_EXTS, MAX_AUDIO_SIZE, "背景音乐")
        with db.get_db() as conn:
            conn.execute(
                """UPDATE photo_category SET bgm_path=?, bgm_original_name=?,
                   bgm_file_size=?, updated_at=? WHERE id=?""",
                (rel_path, original, size, db.now_str(), cid),
            )
            db.log_action(conn, admin["username"], "上传图片板块背景音乐", cat["name"], client_ip(request))
        if cat["bgm_path"] and cat["bgm_path"] != rel_path:
            _delete_media_file(cat["bgm_path"])
    except ValueError as e:
        return _photo_error(e)
    return RedirectResponse("/admin/photos", status_code=302)


@app.post("/admin/photos/category/{cid:int}/bgm/delete")
def admin_photo_cat_bgm_delete(cid: int, request: Request,
                               admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cat = conn.execute("SELECT * FROM photo_category WHERE id = ?", (cid,)).fetchone()
        if not cat:
            return _photo_error("板块不存在")
        conn.execute(
            "UPDATE photo_category SET bgm_path='', bgm_original_name='', bgm_file_size=0, updated_at=? WHERE id=?",
            (db.now_str(), cid),
        )
        db.log_action(conn, admin["username"], "删除图片板块背景音乐", cat["name"], client_ip(request))
    _delete_media_file(cat["bgm_path"])
    return RedirectResponse("/admin/photos", status_code=302)


# ---------- 音乐管理 ----------

@app.get("/admin/music", response_class=HTMLResponse)
def admin_music(request: Request, error: str = "", uploaded: int = 0, skipped: int = 0,
                admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = conn.execute("SELECT * FROM music_category ORDER BY sort_order, id").fetchall()
        albums = conn.execute(
            """SELECT a.*, c.name AS category_name FROM music_album a
               LEFT JOIN music_category c ON c.id = a.category_id
               ORDER BY COALESCE(c.sort_order, 999999), COALESCE(c.id, 999999), a.sort_order, a.id"""
        ).fetchall()
        rows = conn.execute(
            """SELECT m.*, c.name AS category_name, a.name AS album_name FROM music m
               LEFT JOIN music_category c ON c.id = m.category_id
               LEFT JOIN music_album a ON a.id = m.album_id
               ORDER BY COALESCE(c.sort_order, 999999), COALESCE(c.id, 999999),
                        m.sort_order, m.id"""
        ).fetchall()
        counts = {
            r["category_id"]: r["n"] for r in conn.execute(
                "SELECT category_id, COUNT(*) AS n FROM music GROUP BY category_id"
            ).fetchall()
        }
        album_counts = {
            r["album_id"]: r["n"] for r in conn.execute(
                "SELECT album_id, COUNT(*) AS n FROM music WHERE album_id > 0 GROUP BY album_id"
            ).fetchall()
        }
        schedules = []
        for s in conn.execute(
            """SELECT s.*, c.name AS category_name, a.name AS album_name
               FROM music_schedule s
               LEFT JOIN music_category c ON c.id = s.category_id
               LEFT JOIN music_album a ON a.id = s.album_id
               ORDER BY s.start_time, s.id"""
        ).fetchall():
            item = dict(s)
            item["track_count"] = len(
                _schedule_tracks(conn, int(s["category_id"] or 0), int(s["album_id"] or 0)))
            schedules.append(item)
    albums_by_cat = {}
    for a in albums:
        albums_by_cat.setdefault(int(a["category_id"] or 0), []).append(a)
    for s in schedules:
        s["days"] = [int(d) for d in str(s["weekdays"]).split(",") if d.strip().isdigit()]
        s["weekday_text"] = _weekdays_text(s["weekdays"])
        s["target"] = s["album_name"] or s["category_name"] or "未分类"
    return templates.TemplateResponse(request, "admin_music.html", {
        "admin": admin, "rows": rows, "cats": cats, "albums": albums,
        "albums_by_cat": albums_by_cat, "counts": counts, "album_counts": album_counts,
        "schedules": schedules,
        "error": error, "uploaded": uploaded, "skipped": skipped,
        "max_size": human_size(MAX_AUDIO_SIZE),
    })


def _validate_music_category(conn, category_id: int) -> int:
    if category_id and not conn.execute(
        "SELECT 1 FROM music_category WHERE id = ?", (category_id,)
    ).fetchone():
        raise ValueError("所属分类不存在")
    return category_id


def _validate_music_album(conn, category_id: int, album_id: int) -> int:
    """校验专辑存在且属于所选分类；album_id=0 表示不归入专辑。"""
    if not album_id:
        return 0
    row = conn.execute("SELECT category_id FROM music_album WHERE id = ?", (album_id,)).fetchone()
    if not row:
        raise ValueError("所属专辑不存在")
    if int(row["category_id"] or 0) != int(category_id or 0):
        raise ValueError("所选专辑不属于该分类")
    return album_id


def _music_error(message) -> RedirectResponse:
    from urllib.parse import quote
    return RedirectResponse(f"/admin/music?error={quote(str(message))}", status_code=302)


_TIME_RE = re.compile(r"^([01]\d|2[0-3]):[0-5]\d$")


def _parse_weekdays(raw: str) -> str:
    """把表单里多选的星期（1=周一 … 7=周日）规整成 '1,2,3,4,5'。"""
    days = []
    for part in str(raw or "").split(","):
        part = part.strip()
        if not part:
            continue
        if not part.isdigit() or not 1 <= int(part) <= 7:
            raise ValueError("星期设置有误，只能选择周一至周日")
        if int(part) not in days:
            days.append(int(part))
    if not days:
        raise ValueError("请至少选择一个星期")
    return ",".join(str(d) for d in sorted(days))


def _schedule_params(conn, category_id: int, album_id: int, weekdays: str,
                     start_time: str, end_time: str, play_mode: str):
    """校验定时广播的目标与时段，返回可直接入库的字段元组。"""
    category_id = _validate_music_category(conn, category_id)
    album_id = _validate_music_album(conn, category_id, album_id)
    weekdays = _parse_weekdays(weekdays)
    start_time, end_time = (start_time or "").strip(), (end_time or "").strip()
    for value in (start_time, end_time):
        if not _TIME_RE.match(value):
            raise ValueError("时间格式应为 HH:MM，例如 08:00")
    if end_time <= start_time:
        raise ValueError("结束时间必须晚于开始时间（暂不支持跨天时段）")
    if play_mode not in PLAY_MODE_TEXT:
        play_mode = "loop"
    return category_id, album_id, weekdays, start_time, end_time, play_mode


@app.post("/admin/music/schedule/add")
def admin_music_schedule_add(request: Request, admin=Depends(require_full_admin),
                             title: str = Form(...), category_id: int = Form(0),
                             album_id: int = Form(0), weekdays: List[int] = Form([]),
                             start_time: str = Form(...), end_time: str = Form(...),
                             play_mode: str = Form("loop"), enabled: int = Form(0),
                             sort_order: int = Form(0)):
    title = title.strip()[:80]
    if not title:
        return _music_error("广播名称不能为空")
    now = db.now_str()
    with db.get_db() as conn:
        try:
            params = _schedule_params(conn, category_id, album_id,
                                      ",".join(str(d) for d in weekdays),
                                      start_time, end_time, play_mode)
        except ValueError as e:
            return _music_error(e)
        conn.execute(
            """INSERT INTO music_schedule (title, category_id, album_id, weekdays, start_time,
                                           end_time, play_mode, enabled, sort_order, created_at, updated_at)
               VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
            (title, *params, 1 if enabled else 0, sort_order, now, now),
        )
        db.log_action(conn, admin["username"], "新增定时广播",
                      f"{title} {start_time}-{end_time}", client_ip(request))
    return RedirectResponse("/admin/music#schedule", status_code=302)


@app.post("/admin/music/schedule/{sid:int}/update")
def admin_music_schedule_update(sid: int, request: Request, admin=Depends(require_full_admin),
                                title: str = Form(...), category_id: int = Form(0),
                                album_id: int = Form(0), weekdays: List[int] = Form([]),
                                start_time: str = Form(...), end_time: str = Form(...),
                                play_mode: str = Form("loop"), enabled: int = Form(0),
                                sort_order: int = Form(0)):
    title = title.strip()[:80]
    if not title:
        return _music_error("广播名称不能为空")
    with db.get_db() as conn:
        if not conn.execute("SELECT 1 FROM music_schedule WHERE id = ?", (sid,)).fetchone():
            return _music_error("定时广播计划不存在")
        try:
            params = _schedule_params(conn, category_id, album_id,
                                      ",".join(str(d) for d in weekdays),
                                      start_time, end_time, play_mode)
        except ValueError as e:
            return _music_error(e)
        conn.execute(
            """UPDATE music_schedule SET title=?, category_id=?, album_id=?, weekdays=?,
                                        start_time=?, end_time=?, play_mode=?, enabled=?,
                                        sort_order=?, updated_at=?
               WHERE id=?""",
            (title, *params, 1 if enabled else 0, sort_order, db.now_str(), sid),
        )
        db.log_action(conn, admin["username"], "编辑定时广播",
                      f"{title} {start_time}-{end_time}", client_ip(request))
    return RedirectResponse("/admin/music#schedule", status_code=302)


@app.post("/admin/music/schedule/{sid:int}/delete")
def admin_music_schedule_delete(sid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT title FROM music_schedule WHERE id = ?", (sid,)).fetchone()
        conn.execute("DELETE FROM music_schedule WHERE id = ?", (sid,))
        if row:
            db.log_action(conn, admin["username"], "删除定时广播", row["title"], client_ip(request))
    return RedirectResponse("/admin/music#schedule", status_code=302)


@app.post("/admin/music/category/add")
def admin_music_cat_add(request: Request, admin=Depends(require_full_admin),
                        name: str = Form(...), sort_order: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _music_error("分类名称不能为空")
    now = db.now_str()
    with db.get_db() as conn:
        conn.execute(
            "INSERT INTO music_category (name, sort_order, visible, created_at, updated_at) VALUES (?,?,?,?,?)",
            (name, sort_order, 1, now, now),
        )
        db.log_action(conn, admin["username"], "新增音乐分类", name, client_ip(request))
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/category/{cid:int}/update")
def admin_music_cat_update(cid: int, request: Request, admin=Depends(require_full_admin),
                           name: str = Form(...), visible: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _music_error("分类名称不能为空")
    with db.get_db() as conn:
        if not conn.execute("SELECT 1 FROM music_category WHERE id = ?", (cid,)).fetchone():
            return _music_error("分类不存在")
        conn.execute(
            "UPDATE music_category SET name=?, visible=?, updated_at=? WHERE id=?",
            (name, 1 if visible else 0, db.now_str(), cid),
        )
        db.log_action(conn, admin["username"], "编辑音乐分类", name, client_ip(request))
    return RedirectResponse("/admin/music", status_code=302)


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


@app.post("/admin/music/category/{cid:int}/delete")
def admin_music_cat_delete(cid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT name FROM music_category WHERE id = ?", (cid,)).fetchone()
        n = conn.execute("SELECT COUNT(*) AS n FROM music WHERE category_id = ?", (cid,)).fetchone()["n"]
        na = conn.execute("SELECT COUNT(*) AS n FROM music_album WHERE category_id = ?", (cid,)).fetchone()["n"]
        ns = conn.execute("SELECT COUNT(*) AS n FROM music_schedule WHERE category_id = ?", (cid,)).fetchone()["n"]
        if n:
            return _music_error(f"分类【{row['name'] if row else cid}】下还有 {n} 首音乐，请先移动或删除后再删除分类")
        if na:
            return _music_error(f"分类【{row['name'] if row else cid}】下还有 {na} 个专辑，请先删除专辑后再删除分类")
        if ns:
            return _music_error(f"分类【{row['name'] if row else cid}】正被 {ns} 个定时广播计划使用，请先修改或删除这些计划")
        conn.execute("DELETE FROM music_category WHERE id = ?", (cid,))
        if row:
            db.log_action(conn, admin["username"], "删除音乐分类", row["name"], client_ip(request))
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/album/add")
def admin_music_album_add(request: Request, admin=Depends(require_full_admin),
                          category_id: int = Form(0), name: str = Form(...),
                          sort_order: int = Form(0)):
    name = name.strip()[:80]
    if not name:
        return _music_error("专辑名称不能为空")
    now = db.now_str()
    with db.get_db() as conn:
        cat = conn.execute("SELECT name FROM music_category WHERE id = ?", (category_id,)).fetchone()
        if not cat:
            return _music_error("所属分类不存在，请先选择分类")
        if conn.execute(
            "SELECT 1 FROM music_album WHERE category_id = ? AND name = ?", (category_id, name)
        ).fetchone():
            return _music_error(f"分类【{cat['name']}】下已存在专辑【{name}】")
        conn.execute(
            """INSERT INTO music_album (category_id, name, sort_order, visible, created_at, updated_at)
               VALUES (?,?,?,?,?,?)""",
            (category_id, name, sort_order, 1, now, now),
        )
        db.log_action(conn, admin["username"], "新增音乐专辑",
                      f"{cat['name']} / {name}", client_ip(request))
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/album/{aid:int}/update")
def admin_music_album_update(aid: int, request: Request, admin=Depends(require_full_admin),
                             name: str = Form(...), visible: int = Form(0)):
    name = name.strip()[:80]
    if not name:
        return _music_error("专辑名称不能为空")
    with db.get_db() as conn:
        album = conn.execute("SELECT * FROM music_album WHERE id = ?", (aid,)).fetchone()
        if not album:
            return _music_error("专辑不存在")
        if conn.execute(
            "SELECT 1 FROM music_album WHERE category_id = ? AND name = ? AND id <> ?",
            (album["category_id"], name, aid),
        ).fetchone():
            return _music_error(f"该分类下已存在专辑【{name}】")
        conn.execute(
            "UPDATE music_album SET name=?, visible=?, updated_at=? WHERE id=?",
            (name, 1 if visible else 0, db.now_str(), aid),
        )
        db.log_action(conn, admin["username"], "编辑音乐专辑", name, client_ip(request))
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/album/{aid:int}/move")
def admin_music_album_move(aid: int, direction: str = Form(...),
                           admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        album = conn.execute("SELECT category_id FROM music_album WHERE id = ?", (aid,)).fetchone()
        if not album:
            return _music_error("专辑不存在")
        _move_rows(conn, "music_album", aid, direction,
                   "WHERE category_id = ?", (album["category_id"],))
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/album/{aid:int}/delete")
def admin_music_album_delete(aid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        album = conn.execute("SELECT * FROM music_album WHERE id = ?", (aid,)).fetchone()
        if not album:
            return _music_error("专辑不存在")
        conn.execute(
            "UPDATE music SET album_id=0, updated_at=? WHERE album_id=?", (db.now_str(), aid))
        conn.execute(
            "UPDATE music_schedule SET album_id=0, updated_at=? WHERE album_id=?",
            (db.now_str(), aid))
        conn.execute("DELETE FROM music_album WHERE id = ?", (aid,))
        db.log_action(conn, admin["username"], "删除音乐专辑", album["name"], client_ip(request))
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/add")
async def admin_music_add(request: Request, admin=Depends(require_full_admin),
                          title: str = Form(...), description: str = Form(""),
                          category_id: int = Form(0), album_id: int = Form(0),
                          sort_order: int = Form(0), visible: int = Form(0)):
    try:
        title = title.strip()[:200]
        if not title:
            raise ValueError("音乐标题不能为空")
        with db.get_db() as conn:
            category_id = _validate_music_category(conn, category_id)
            album_id = _validate_music_album(conn, category_id, album_id)
        form = await request.form(max_part_size=MAX_FORM_PART)
        original, rel_path, size = _store_media_upload(
            form.get("file"), "music", AUDIO_EXTS, MAX_AUDIO_SIZE, "音乐")
        now = db.now_str()
        with db.get_db() as conn:
            conn.execute(
                """INSERT INTO music (title, description, original_name, file_path, file_size,
                   sort_order, visible, category_id, album_id, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
                (title, description.strip()[:2000], original, rel_path, size,
                 sort_order, 1 if visible else 0, category_id, album_id, now, now),
            )
            db.log_action(conn, admin["username"], "上传音乐", title, client_ip(request))
    except ValueError as e:
        return _music_error(e)
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/batch-upload")
async def admin_music_batch_upload(request: Request, admin=Depends(require_full_admin),
                                   category_id: int = Form(0), album_id: int = Form(0),
                                   visible: int = Form(0)):
    """按分类批量上传音乐：标题自动取文件名（不含扩展名）。"""
    form = await request.form(max_part_size=MAX_FORM_PART)
    uploads = [u for u in form.getlist("files") if getattr(u, "filename", "")]
    if not uploads:
        return _music_error("请选择要批量上传的音乐文件")
    with db.get_db() as conn:
        try:
            category_id = _validate_music_category(conn, category_id)
            album_id = _validate_music_album(conn, category_id, album_id)
        except ValueError as e:
            return _music_error(e)
        cat = conn.execute("SELECT name FROM music_category WHERE id = ?", (category_id,)).fetchone()
        category_name = cat["name"] if cat else "未分类"
        album = conn.execute("SELECT name FROM music_album WHERE id = ?", (album_id,)).fetchone()
        album_name = album["name"] if album else ""
        if album_name:
            category_name = f"{category_name} / {album_name}"
        start = conn.execute(
            "SELECT COALESCE(MAX(sort_order), 0) AS n FROM music WHERE category_id = ?",
            (category_id,),
        ).fetchone()["n"]
        saved, skipped = 0, []
        now = db.now_str()
        for idx, up in enumerate(uploads, start=1):
            try:
                original, rel_path, size = _store_media_upload(
                    up, "music", AUDIO_EXTS, MAX_AUDIO_SIZE, "音乐")
            except ValueError as e:
                skipped.append(f"{safe_filename(up.filename)}：{e}")
                continue
            title = Path(str(up.filename).replace("\\", "/")).stem.strip()[:200] or "未命名音乐"
            conn.execute(
                """INSERT INTO music (title, description, original_name, file_path, file_size,
                   sort_order, visible, category_id, album_id, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
                (title, "", original, rel_path, size, start + idx,
                 1 if visible else 0, category_id, album_id, now, now),
            )
            saved += 1
        db.log_action(conn, admin["username"], "批量上传音乐",
                      f"{category_name}：成功 {saved} 首，跳过 {len(skipped)} 个", client_ip(request))
    if not saved:
        return _music_error("批量上传失败：" + "；".join(skipped[:3]))
    return RedirectResponse(f"/admin/music?uploaded={saved}&skipped={len(skipped)}",
                            status_code=302)


@app.post("/admin/music/{mid:int}/update")
async def admin_music_update(request: Request, mid: int, admin=Depends(require_full_admin),
                             title: str = Form(...), description: str = Form(""),
                             category_id: int = Form(0), album_id: int = Form(0),
                             sort_order: int = Form(0), visible: int = Form(0)):
    try:
        with db.get_db() as conn:
            item = conn.execute("SELECT * FROM music WHERE id = ?", (mid,)).fetchone()
            if not item:
                raise ValueError("音乐不存在")
            category_id = _validate_music_category(conn, category_id)
            album_id = _validate_music_album(conn, category_id, album_id)
        title = title.strip()[:200]
        if not title:
            raise ValueError("音乐标题不能为空")
        form = await request.form(max_part_size=MAX_FORM_PART)
        up = form.get("file")
        new_original = new_rel = ""
        new_size = 0
        if up and getattr(up, "filename", ""):
            new_original, new_rel, new_size = _store_media_upload(
                up, "music", AUDIO_EXTS, MAX_AUDIO_SIZE, "音乐")
        now = db.now_str()
        with db.get_db() as conn:
            if new_rel:
                conn.execute(
                    """UPDATE music SET title=?, description=?, original_name=?, file_path=?,
                       file_size=?, sort_order=?, visible=?, category_id=?, album_id=?, updated_at=?
                       WHERE id=?""",
                    (title, description.strip()[:2000], new_original, new_rel, new_size,
                     sort_order, 1 if visible else 0, category_id, album_id, now, mid),
                )
            else:
                conn.execute(
                    """UPDATE music SET title=?, description=?, sort_order=?, visible=?,
                       category_id=?, album_id=?, updated_at=? WHERE id=?""",
                    (title, description.strip()[:2000], sort_order,
                     1 if visible else 0, category_id, album_id, now, mid),
                )
            db.log_action(conn, admin["username"], "编辑音乐", title, client_ip(request))
        if new_rel and new_rel != item["file_path"]:
            _delete_media_file(item["file_path"])
    except ValueError as e:
        return _music_error(e)
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/{mid:int}/move")
def admin_music_move(request: Request, mid: int, direction: str = Form(...),
                     admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT category_id FROM music WHERE id = ?", (mid,)).fetchone()
        if not item:
            return _music_error("音乐不存在")
        _move_rows(conn, "music", mid, direction,
                   "WHERE category_id = ?", (item["category_id"],))
        db.log_action(conn, admin["username"], "调整音乐排序", str(mid), client_ip(request))
    return RedirectResponse("/admin/music", status_code=302)


@app.post("/admin/music/{mid:int}/delete")
def admin_music_delete(request: Request, mid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT title, file_path FROM music WHERE id = ?", (mid,)).fetchone()
        if item:
            conn.execute("DELETE FROM music WHERE id = ?", (mid,))
            db.log_action(conn, admin["username"], "删除音乐", item["title"], client_ip(request))
    if item:
        _delete_media_file(item["file_path"])
    return RedirectResponse("/admin/music", status_code=302)


# ---------- 电子书管理 ----------

@app.get("/admin/ebooks", response_class=HTMLResponse)
def admin_ebooks(request: Request, error: str = "", admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        cats = conn.execute("SELECT * FROM ebook_category ORDER BY sort_order, id").fetchall()
        rows = conn.execute(
            """SELECT e.*, c.name AS category_name FROM ebook e
               LEFT JOIN ebook_category c ON c.id = e.category_id
               ORDER BY COALESCE(c.sort_order, 999999), COALESCE(c.id, 999999),
                        e.sort_order, e.id"""
        ).fetchall()
        counts = {
            r["category_id"]: r["n"] for r in conn.execute(
                "SELECT category_id, COUNT(*) AS n FROM ebook GROUP BY category_id"
            ).fetchall()
        }
    return templates.TemplateResponse(request, "admin_ebooks.html", {
        "admin": admin, "rows": rows, "cats": cats, "counts": counts, "error": error,
        "max_size": human_size(MAX_EBOOK_SIZE),
    })


def _validate_ebook_category(conn, category_id: int) -> int:
    if category_id and not conn.execute(
        "SELECT 1 FROM ebook_category WHERE id = ?", (category_id,)
    ).fetchone():
        raise ValueError("所属分类不存在")
    return category_id


def _ebook_error(message) -> RedirectResponse:
    from urllib.parse import quote
    return RedirectResponse(f"/admin/ebooks?error={quote(str(message))}", status_code=302)


@app.post("/admin/ebooks/category/add")
def admin_ebook_cat_add(request: Request, admin=Depends(require_full_admin),
                        name: str = Form(...), sort_order: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _ebook_error("分类名称不能为空")
    now = db.now_str()
    with db.get_db() as conn:
        conn.execute(
            "INSERT INTO ebook_category (name, sort_order, visible, created_at, updated_at) VALUES (?,?,?,?,?)",
            (name, sort_order, 1, now, now),
        )
        db.log_action(conn, admin["username"], "新增电子书分类", name, client_ip(request))
    return RedirectResponse("/admin/ebooks", status_code=302)


@app.post("/admin/ebooks/category/{cid:int}/update")
def admin_ebook_cat_update(cid: int, request: Request, admin=Depends(require_full_admin),
                           name: str = Form(...), visible: int = Form(0)):
    name = name.strip()[:60]
    if not name:
        return _ebook_error("分类名称不能为空")
    with db.get_db() as conn:
        if not conn.execute("SELECT 1 FROM ebook_category WHERE id = ?", (cid,)).fetchone():
            return _ebook_error("分类不存在")
        conn.execute(
            "UPDATE ebook_category SET name=?, visible=?, updated_at=? WHERE id=?",
            (name, 1 if visible else 0, db.now_str(), cid),
        )
        db.log_action(conn, admin["username"], "编辑电子书分类", name, client_ip(request))
    return RedirectResponse("/admin/ebooks", status_code=302)


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


@app.post("/admin/ebooks/category/{cid:int}/delete")
def admin_ebook_cat_delete(cid: int, request: Request, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        row = conn.execute("SELECT name FROM ebook_category WHERE id = ?", (cid,)).fetchone()
        n = conn.execute("SELECT COUNT(*) AS n FROM ebook WHERE category_id = ?", (cid,)).fetchone()["n"]
        if n:
            return _ebook_error(f"分类【{row['name'] if row else cid}】下还有 {n} 本电子书，请先移动或删除后再删除分类")
        conn.execute("DELETE FROM ebook_category WHERE id = ?", (cid,))
        if row:
            db.log_action(conn, admin["username"], "删除电子书分类", row["name"], client_ip(request))
    return RedirectResponse("/admin/ebooks", status_code=302)


@app.post("/admin/ebooks/add")
async def admin_ebook_add(request: Request, admin=Depends(require_full_admin),
                          title: str = Form(...), description: str = Form(""),
                          category_id: int = Form(0), sort_order: int = Form(0),
                          visible: int = Form(0)):
    try:
        title = title.strip()[:200]
        if not title:
            raise ValueError("电子书标题不能为空")
        with db.get_db() as conn:
            category_id = _validate_ebook_category(conn, category_id)
        form = await request.form(max_part_size=MAX_FORM_PART)
        original, rel_path, size = _store_media_upload(
            form.get("file"), "ebooks", EBOOK_EXTS, MAX_EBOOK_SIZE, "电子书")
        fmt = Path(rel_path).suffix.lower().lstrip(".")
        now = db.now_str()
        with db.get_db() as conn:
            conn.execute(
                """INSERT INTO ebook (title, description, original_name, file_path, file_size,
                   format, sort_order, visible, category_id, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?)""",
                (title, description.strip()[:2000], original, rel_path, size,
                 fmt, sort_order, 1 if visible else 0, category_id, now, now),
            )
            db.log_action(conn, admin["username"], "上传电子书", title, client_ip(request))
    except ValueError as e:
        return _ebook_error(e)
    return RedirectResponse("/admin/ebooks", status_code=302)


@app.post("/admin/ebooks/{bid:int}/update")
async def admin_ebook_update(request: Request, bid: int, admin=Depends(require_full_admin),
                             title: str = Form(...), description: str = Form(""),
                             category_id: int = Form(0), sort_order: int = Form(0),
                             visible: int = Form(0)):
    try:
        with db.get_db() as conn:
            item = conn.execute("SELECT * FROM ebook WHERE id = ?", (bid,)).fetchone()
            if not item:
                raise ValueError("电子书不存在")
            category_id = _validate_ebook_category(conn, category_id)
        title = title.strip()[:200]
        if not title:
            raise ValueError("电子书标题不能为空")
        form = await request.form(max_part_size=MAX_FORM_PART)
        up = form.get("file")
        new_original = new_rel = new_fmt = ""
        new_size = 0
        if up and getattr(up, "filename", ""):
            new_original, new_rel, new_size = _store_media_upload(
                up, "ebooks", EBOOK_EXTS, MAX_EBOOK_SIZE, "电子书")
            new_fmt = Path(new_rel).suffix.lower().lstrip(".")
        now = db.now_str()
        with db.get_db() as conn:
            if new_rel:
                conn.execute(
                    """UPDATE ebook SET title=?, description=?, original_name=?, file_path=?,
                       file_size=?, format=?, sort_order=?, visible=?, category_id=?, updated_at=?
                       WHERE id=?""",
                    (title, description.strip()[:2000], new_original, new_rel, new_size,
                     new_fmt, sort_order, 1 if visible else 0, category_id, now, bid),
                )
            else:
                conn.execute(
                    """UPDATE ebook SET title=?, description=?, sort_order=?, visible=?,
                       category_id=?, updated_at=? WHERE id=?""",
                    (title, description.strip()[:2000], sort_order,
                     1 if visible else 0, category_id, now, bid),
                )
            db.log_action(conn, admin["username"], "编辑电子书", title, client_ip(request))
        if new_rel and new_rel != item["file_path"]:
            _delete_media_file(item["file_path"])
    except ValueError as e:
        return _ebook_error(e)
    return RedirectResponse("/admin/ebooks", status_code=302)


@app.post("/admin/ebooks/{bid:int}/move")
def admin_ebook_move(request: Request, bid: int, direction: str = Form(...),
                     admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT category_id FROM ebook WHERE id = ?", (bid,)).fetchone()
        if not item:
            return _ebook_error("电子书不存在")
        _move_rows(conn, "ebook", bid, direction,
                   "WHERE category_id = ?", (item["category_id"],))
        db.log_action(conn, admin["username"], "调整电子书排序", str(bid), client_ip(request))
    return RedirectResponse("/admin/ebooks", status_code=302)


@app.post("/admin/ebooks/{bid:int}/delete")
def admin_ebook_delete(request: Request, bid: int, admin=Depends(require_full_admin)):
    with db.get_db() as conn:
        item = conn.execute("SELECT title, file_path FROM ebook WHERE id = ?", (bid,)).fetchone()
        if item:
            conn.execute("DELETE FROM ebook WHERE id = ?", (bid,))
            db.log_action(conn, admin["username"], "删除电子书", item["title"], client_ip(request))
    if item:
        _delete_media_file(item["file_path"])
    return RedirectResponse("/admin/ebooks", status_code=302)


# ---------- 留言板管理 ----------

BOARD_STATUS_TEXT = {"pending": "待审核", "approved": "已通过", "rejected": "已驳回"}


@app.get("/admin/board", response_class=HTMLResponse)
def admin_board(request: Request, status: str = "pending", error: str = "",
                admin=Depends(require_full_admin)):
    if status not in BOARD_STATUS_TEXT:
        status = "pending"
    with db.get_db() as conn:
        rows = conn.execute(
            "SELECT * FROM board_message WHERE status = ? ORDER BY created_at DESC, id DESC",
            (status,),
        ).fetchall()
        counts = {
            r["status"]: r["n"] for r in conn.execute(
                "SELECT status, COUNT(*) AS n FROM board_message GROUP BY status"
            ).fetchall()
        }
    return templates.TemplateResponse(request, "admin_board.html", {
        "admin": admin, "rows": rows, "counts": counts, "status": status,
        "status_text": BOARD_STATUS_TEXT, "kinds": BOARD_KINDS, "error": error,
    })


def _board_admin_redirect(status: str) -> RedirectResponse:
    return RedirectResponse(f"/admin/board?status={status}", status_code=302)


@app.post("/admin/board/{mid:int}/reply")
def admin_board_reply(mid: int, request: Request, reply: str = Form(""),
                      status: str = Form("pending"), admin=Depends(require_full_admin)):
    reply = reply.strip()[:2000]
    if not reply:
        return _board_admin_redirect(status)
    with db.get_db() as conn:
        row = conn.execute("SELECT id, status FROM board_message WHERE id = ?", (mid,)).fetchone()
        if not row:
            return _board_admin_redirect(status)
        conn.execute(
            "UPDATE board_message SET reply=?, replied_at=?, replied_by=? WHERE id=?",
            (reply, db.now_str(), admin["username"], mid),
        )
        db.log_action(conn, admin["username"], "回复留言板留言", str(mid), client_ip(request))
    return _board_admin_redirect(status)


@app.post("/admin/board/{mid:int}/{action}")
def admin_board_action(mid: int, action: str, request: Request,
                       status: str = Form("pending"), admin=Depends(require_full_admin)):
    if action not in ("approve", "reject", "delete"):
        return _board_admin_redirect(status)
    with db.get_db() as conn:
        row = conn.execute("SELECT nickname FROM board_message WHERE id = ?", (mid,)).fetchone()
        if not row:
            return _board_admin_redirect(status)
        if action == "delete":
            conn.execute("DELETE FROM board_message WHERE id = ?", (mid,))
        else:
            conn.execute(
                "UPDATE board_message SET status=? WHERE id=?",
                ("approved" if action == "approve" else "rejected", mid),
            )
        db.log_action(conn, admin["username"],
                      {"approve": "通过留言板留言", "reject": "驳回留言板留言",
                       "delete": "删除留言板留言"}[action],
                      row["nickname"], client_ip(request))
    return _board_admin_redirect(status)


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

@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 "提交软件"
        with db.get_db() as conn:
            single_mb, total_mb, used = submitter_quota(conn, admin, item["id"] if item else None)
        quota = {
            "single_mb": single_mb, "total_mb": total_mb,
            "used_mb": round(used / 1048576, 1),
            "remaining_mb": round(max(0, total_mb * 1048576 - used) / 1048576, 1),
        }
    else:
        action = f"/admin/software/{item['id']}/edit" if item else "/admin/software/add"
        cancel = "/admin/software"
        title = f"编辑软件：{item['name']}" if item else "添加软件"
        quota = None
    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,
        "license_options": license_options(submitter_mode), "quota": quota,
    })


@app.get("/admin/submit/software", response_class=HTMLResponse)
def submit_sw_page(request: Request, admin=Depends(require_approved)):
    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_approved),
                    name: str = Form(...), version: str = Form(""),
                    developer: str = Form(""), license_type: 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([]),
                    file_links: List[str] = Form([])):
    with db.get_db() as conn:
        cats = conn.execute("SELECT * FROM category ORDER BY sort_order, id").fetchall()
        single_mb, total_mb, used = submitter_quota(conn, admin)
    try:
        developer, license_type = validate_software_meta(developer, license_type, True)
        if admin.get("role") == "admin":
            max_file_bytes = None
            remaining_bytes = None
        else:
            max_file_bytes = single_mb * 1024 * 1024
            remaining_bytes = max(0, total_mb * 1024 * 1024 - used)
        collected = _collect_files(
            file_labels, file_paths, file_links,
            _form_uploads(await request.form(max_part_size=MAX_FORM_PART)),
            max_file_bytes=max_file_bytes, remaining_bytes=remaining_bytes,
        )
        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, developer, license_type, category_id,
                   description, remark, status, submitter_id, submitter_name, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,2,?,?,?,?)""",
                (name.strip(), version.strip(), developer, license_type, 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, "developer": developer,
                             "license_type": license_type, "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_approved)):
    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_approved),
                     name: str = Form(...), version: str = Form(""),
                     developer: str = Form(""), license_type: 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([]),
                     file_links: 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()
        single_mb, total_mb, used = submitter_quota(conn, admin, sid, delete_ids)
    try:
        developer, license_type = validate_software_meta(developer, license_type, True)
        if admin.get("role") == "admin":
            max_file_bytes = None
            remaining_bytes = None
        else:
            max_file_bytes = single_mb * 1024 * 1024
            remaining_bytes = max(0, total_mb * 1024 * 1024 - used)
        collected = _collect_files(
            file_labels, file_paths, file_links,
            _form_uploads(await request.form(max_part_size=MAX_FORM_PART)),
            max_file_bytes=max_file_bytes, remaining_bytes=remaining_bytes,
        )
        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=?, developer=?, license_type=?, category_id=?,
                   description=?, remark=?, status=2, review_note='', updated_at=? WHERE id=?""",
                (name.strip(), version.strip(), developer, license_type, 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_approved)):
    return _vuln_form_ctx(request, admin, None, [], submitter_mode=True)


@app.post("/admin/submit/vuln")
async def submit_vuln(request: Request, admin=Depends(require_approved),
                      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_approved)):
    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_approved),
                       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_approved)):
    return _article_form_ctx(request, admin, None, submitter_mode=True)


@app.post("/admin/submit/article")
async def submit_article(request: Request, admin=Depends(require_approved)):
    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_approved)):
    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_approved)):
    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)