382 lines
14 KiB
Python
382 lines
14 KiB
Python
|
"""
|
|||
|
|
授权管理后台（/admin）
|
|||
|
|
|
|||
|
|
单页、只读、服务端渲染：概览统计 + 用户与当前授权 + 最近使用记录 + 会话与扫码场景。
|
|||
|
|
|
|||
|
|
鉴权：HTTP Basic Auth，凭据来自 .env 的 ADMIN_USER / ADMIN_PASSWORD。
|
|||
|
|
ADMIN_PASSWORD 为空时整体返回 503（而不是放行）——避免「忘了配密码 = 谁都能进」。
|
|||
|
|
只提供 GET，不提供任何写操作。
|
|||
|
|
|
|||
|
|
刻意不引入 Jinja2 / Bootstrap / 任何 CDN 资源：
|
|||
|
|
- 服务器出网不可靠，页面不能依赖外部资源；
|
|||
|
|
- 少一个依赖就少一份部署风险。
|
|||
|
|
所有来自数据库的文本都经 html.escape()，避免昵称之类的字段注入 HTML。
|
|||
|
|
|
|||
|
|
注意：本模块被 wechat.py import 并挂载，反向 import wechat 会造成循环依赖。
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
import html
|
|||
|
|
import logging
|
|||
|
|
import secrets
|
|||
|
|
from datetime import datetime
|
|||
|
|
|
|||
|
|
import aiomysql
|
|||
|
|
from fastapi import APIRouter, Depends, HTTPException, status
|
|||
|
|
from fastapi.responses import HTMLResponse
|
|||
|
|
from fastapi.security import HTTPBasic, HTTPBasicCredentials
|
|||
|
|
|
|||
|
|
import db
|
|||
|
|
from config import ADMIN_PASSWORD, ADMIN_USER, MYSQL_DB
|
|||
|
|
|
|||
|
|
logger = logging.getLogger(__name__)
|
|||
|
|
|
|||
|
|
router = APIRouter(prefix="/admin", tags=["admin"])
|
|||
|
|
|
|||
|
|
# realm 必须是 ASCII：Starlette 按 latin-1 编码响应头，中文会直接抛异常
|
|||
|
|
_basic = HTTPBasic(realm="CadOcr Admin")
|
|||
|
|
|
|||
|
|
|
|||
|
|
def require_admin(credentials: HTTPBasicCredentials = Depends(_basic)) -> None:
|
|||
|
|
"""校验 Basic Auth；不匹配一律 401 并带 WWW-Authenticate（浏览器才会弹密码框）"""
|
|||
|
|
if not ADMIN_PASSWORD:
|
|||
|
|
raise HTTPException(
|
|||
|
|
status_code=status.HTTP_503_SERVICE_UNAVAILABLE,
|
|||
|
|
detail="管理后台未启用：请在 .env 里配置 ADMIN_PASSWORD",
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
# 比字节而不是比字符串：compare_digest 对含非 ASCII 的 str 会直接抛 TypeError
|
|||
|
|
user_ok = secrets.compare_digest(
|
|||
|
|
credentials.username.encode("utf-8"), ADMIN_USER.encode("utf-8")
|
|||
|
|
)
|
|||
|
|
pass_ok = secrets.compare_digest(
|
|||
|
|
credentials.password.encode("utf-8"), ADMIN_PASSWORD.encode("utf-8")
|
|||
|
|
)
|
|||
|
|
if not (user_ok and pass_ok):
|
|||
|
|
# 只记用户名，绝不记密码
|
|||
|
|
logger.warning("管理后台鉴权失败 user=%s", credentials.username)
|
|||
|
|
raise HTTPException(
|
|||
|
|
status_code=status.HTTP_401_UNAUTHORIZED,
|
|||
|
|
detail="Unauthorized",
|
|||
|
|
headers={"WWW-Authenticate": 'Basic realm="CadOcr Admin"'},
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
# ---------------------------------------------------------------------------
|
|||
|
|
# 查询
|
|||
|
|
# ---------------------------------------------------------------------------
|
|||
|
|
|
|||
|
|
OVERVIEW_SQL = """
|
|||
|
|
SELECT
|
|||
|
|
(SELECT COUNT(*) FROM users) AS users_total,
|
|||
|
|
(SELECT COUNT(*) FROM users WHERE has_claimed_free = 1) AS users_free,
|
|||
|
|
(SELECT COUNT(*) FROM users WHERE created_at >= CURDATE()) AS users_today,
|
|||
|
|
(SELECT COUNT(*) FROM authorizations WHERE status = 'active') AS auth_active,
|
|||
|
|
(SELECT COUNT(*) FROM authorizations WHERE status = 'pending') AS auth_pending,
|
|||
|
|
(SELECT COUNT(*) FROM authorizations WHERE status = 'expired') AS auth_expired,
|
|||
|
|
(SELECT COUNT(*) FROM authorizations WHERE status = 'exhausted') AS auth_exhausted,
|
|||
|
|
(SELECT COUNT(*) FROM authorizations WHERE type = 'time') AS auth_time,
|
|||
|
|
(SELECT COUNT(*) FROM authorizations WHERE type = 'points') AS auth_points,
|
|||
|
|
(SELECT COUNT(*) FROM usage_logs) AS usage_total,
|
|||
|
|
(SELECT COUNT(*) FROM usage_logs WHERE used_at >= CURDATE()) AS usage_today,
|
|||
|
|
(SELECT COUNT(*) FROM sessions WHERE expires_at > NOW()) AS sessions_valid
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
# 互斥原则保证同一用户最多一条 active，所以这个 LEFT JOIN 不会让用户行翻倍
|
|||
|
|
USERS_SQL = """
|
|||
|
|
SELECT
|
|||
|
|
u.id, u.openid, u.nickname, u.created_at, u.last_seen_at, u.has_claimed_free,
|
|||
|
|
a.type, a.source, a.end_at, a.remaining_points, a.total_points,
|
|||
|
|
TIMESTAMPDIFF(SECOND, NOW(), a.end_at) AS remain_seconds,
|
|||
|
|
(SELECT COUNT(*) FROM authorizations x WHERE x.user_id = u.id) AS auth_count
|
|||
|
|
FROM users u
|
|||
|
|
LEFT JOIN authorizations a ON a.user_id = u.id AND a.status = 'active'
|
|||
|
|
ORDER BY u.id DESC
|
|||
|
|
LIMIT 200
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
USAGE_SQL = """
|
|||
|
|
SELECT l.id, l.user_id, u.openid, l.device_id, l.cost_type, l.cost_points, l.used_at
|
|||
|
|
FROM usage_logs l
|
|||
|
|
LEFT JOIN users u ON u.id = l.user_id
|
|||
|
|
ORDER BY l.id DESC
|
|||
|
|
LIMIT 50
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
SESSIONS_SQL = """
|
|||
|
|
SELECT s.id, s.user_id, s.token, u.openid, s.device_id, s.created_at, s.expires_at,
|
|||
|
|
(s.expires_at > NOW()) AS still_valid
|
|||
|
|
FROM sessions s
|
|||
|
|
LEFT JOIN users u ON u.id = s.user_id
|
|||
|
|
ORDER BY s.id DESC
|
|||
|
|
LIMIT 50
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
SCENES_SQL = """
|
|||
|
|
SELECT id, scene_str, device_id, status, user_id, created_at, expires_at, authorized_at
|
|||
|
|
FROM auth_scenes
|
|||
|
|
ORDER BY id DESC
|
|||
|
|
LIMIT 50
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
|
|||
|
|
# ---------------------------------------------------------------------------
|
|||
|
|
# 展示辅助
|
|||
|
|
# ---------------------------------------------------------------------------
|
|||
|
|
|
|||
|
|
def _e(value) -> str:
|
|||
|
|
"""转义后用于 HTML 文本位置；None 显示为占位符"""
|
|||
|
|
if value is None:
|
|||
|
|
return "—"
|
|||
|
|
return html.escape(str(value))
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _dt(value) -> str:
|
|||
|
|
"""DATETIME → 字符串（aiomysql 返回的是 naive datetime，按原样显示即可）"""
|
|||
|
|
if value is None:
|
|||
|
|
return "—"
|
|||
|
|
if isinstance(value, datetime):
|
|||
|
|
return value.strftime("%Y-%m-%d %H:%M:%S")
|
|||
|
|
return html.escape(str(value))
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _token(value) -> str:
|
|||
|
|
"""会话令牌是活的凭据，页面只显示首尾便于比对，不给全量"""
|
|||
|
|
if not value:
|
|||
|
|
return "—"
|
|||
|
|
s = str(value)
|
|||
|
|
if len(s) <= 16:
|
|||
|
|
return html.escape(s)
|
|||
|
|
return html.escape(f"{s[:10]}…{s[-4:]}")
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _human_duration(seconds) -> str:
|
|||
|
|
if seconds is None:
|
|||
|
|
return "—"
|
|||
|
|
seconds = int(seconds)
|
|||
|
|
if seconds <= 0:
|
|||
|
|
return "已过期"
|
|||
|
|
days, rem = divmod(seconds, 86400)
|
|||
|
|
hours, rem = divmod(rem, 3600)
|
|||
|
|
minutes = rem // 60
|
|||
|
|
if days:
|
|||
|
|
return f"{days} 天 {hours} 小时"
|
|||
|
|
if hours:
|
|||
|
|
return f"{hours} 小时 {minutes} 分"
|
|||
|
|
return f"{minutes} 分"
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _tag(text: str, kind: str) -> str:
|
|||
|
|
return f'<span class="tag {kind}">{_e(text)}</span>'
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _card(number, label: str) -> str:
|
|||
|
|
return f'<div class="card"><div class="n">{_e(number)}</div><div class="l">{_e(label)}</div></div>'
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _table(headers: list[str], rows: list[list[str]], empty_text: str) -> str:
|
|||
|
|
if not rows:
|
|||
|
|
return f'<div class="empty">{_e(empty_text)}</div>'
|
|||
|
|
head = "".join(f"<th>{_e(h)}</th>" for h in headers)
|
|||
|
|
body = "".join("<tr>" + "".join(f"<td>{c}</td>" for c in r) + "</tr>" for r in rows)
|
|||
|
|
return f"<table><thead><tr>{head}</tr></thead><tbody>{body}</tbody></table>"
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _render_overview(ov: dict) -> str:
|
|||
|
|
cards = [
|
|||
|
|
_card(ov["users_total"], "用户总数"),
|
|||
|
|
_card(ov["users_today"], "今日新增用户"),
|
|||
|
|
_card(ov["users_free"], "已领免费授权"),
|
|||
|
|
_card(ov["auth_active"], "生效中授权"),
|
|||
|
|
_card(ov["auth_pending"], "待生效授权"),
|
|||
|
|
_card(ov["auth_expired"], "已过期授权"),
|
|||
|
|
_card(ov["auth_exhausted"], "已耗尽授权"),
|
|||
|
|
_card(ov["auth_time"], "时间授权"),
|
|||
|
|
_card(ov["auth_points"], "积分授权"),
|
|||
|
|
_card(ov["usage_total"], "使用总次数"),
|
|||
|
|
_card(ov["usage_today"], "今日使用次数"),
|
|||
|
|
_card(ov["sessions_valid"], "有效会话"),
|
|||
|
|
]
|
|||
|
|
return '<div class="cards">' + "".join(cards) + "</div>"
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _render_users(rows: list[dict]) -> str:
|
|||
|
|
out = []
|
|||
|
|
for r in rows:
|
|||
|
|
if r["type"] is None:
|
|||
|
|
current = _tag("无可用授权", "none")
|
|||
|
|
expire = "—"
|
|||
|
|
else:
|
|||
|
|
kind = "时间" if r["type"] == "time" else "积分"
|
|||
|
|
source = {"free": "免费", "purchase": "购买", "admin": "后台"}.get(r["source"], r["source"])
|
|||
|
|
current = f"{_e(kind)} · {_e(source)} " + _tag("生效中", "active")
|
|||
|
|
if r["type"] == "time":
|
|||
|
|
expire = f"{_dt(r['end_at'])}<br><span class=\"dim\">剩 {_e(_human_duration(r['remain_seconds']))}</span>"
|
|||
|
|
else:
|
|||
|
|
expire = f"{_e(r['remaining_points'])} / {_e(r['total_points'])} 次"
|
|||
|
|
|
|||
|
|
out.append([
|
|||
|
|
_e(r["id"]),
|
|||
|
|
f'<code>{_e(r["openid"])}</code>',
|
|||
|
|
_e(r["nickname"]),
|
|||
|
|
_dt(r["created_at"]),
|
|||
|
|
_dt(r["last_seen_at"]),
|
|||
|
|
_tag("是", "active") if r["has_claimed_free"] else _tag("否", "expired"),
|
|||
|
|
current,
|
|||
|
|
expire,
|
|||
|
|
_e(r["auth_count"]),
|
|||
|
|
])
|
|||
|
|
return _table(
|
|||
|
|
["ID", "openid", "昵称", "首次关注", "最近活跃", "已领免费", "当前授权", "到期 / 余量", "授权条数"],
|
|||
|
|
out,
|
|||
|
|
"还没有任何用户",
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _render_usage(rows: list[dict]) -> str:
|
|||
|
|
out = []
|
|||
|
|
for r in rows:
|
|||
|
|
kind = "时间" if r["cost_type"] == "time" else "积分"
|
|||
|
|
out.append([
|
|||
|
|
_e(r["id"]),
|
|||
|
|
_e(r["user_id"]),
|
|||
|
|
f'<code>{_e(r["openid"])}</code>',
|
|||
|
|
f'<code>{_e(r["device_id"])}</code>',
|
|||
|
|
_e(kind),
|
|||
|
|
_e(r["cost_points"]),
|
|||
|
|
_dt(r["used_at"]),
|
|||
|
|
])
|
|||
|
|
return _table(
|
|||
|
|
["ID", "用户ID", "openid", "设备", "计费类型", "扣减", "时间"],
|
|||
|
|
out,
|
|||
|
|
"还没有使用记录",
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _render_sessions(rows: list[dict]) -> str:
|
|||
|
|
out = []
|
|||
|
|
for r in rows:
|
|||
|
|
out.append([
|
|||
|
|
_e(r["id"]),
|
|||
|
|
_e(r["user_id"]),
|
|||
|
|
f'<code>{_e(r["openid"])}</code>',
|
|||
|
|
f'<code>{_e(r["device_id"])}</code>',
|
|||
|
|
f'<code>{_token(r["token"])}</code>',
|
|||
|
|
_dt(r["created_at"]),
|
|||
|
|
_dt(r["expires_at"]),
|
|||
|
|
_tag("有效", "active") if r["still_valid"] else _tag("已过期", "expired"),
|
|||
|
|
])
|
|||
|
|
return _table(
|
|||
|
|
["ID", "用户ID", "openid", "设备", "令牌", "签发时间", "过期时间", "状态"],
|
|||
|
|
out,
|
|||
|
|
"还没有会话",
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
def _render_scenes(rows: list[dict]) -> str:
|
|||
|
|
kind_map = {"pending": "pending", "scanned": "pending", "authorized": "active", "expired": "expired"}
|
|||
|
|
out = []
|
|||
|
|
for r in rows:
|
|||
|
|
out.append([
|
|||
|
|
_e(r["id"]),
|
|||
|
|
f'<code>{_e(r["scene_str"])}</code>',
|
|||
|
|
f'<code>{_e(r["device_id"])}</code>',
|
|||
|
|
_tag(r["status"], kind_map.get(r["status"], "expired")),
|
|||
|
|
_e(r["user_id"]),
|
|||
|
|
_dt(r["created_at"]),
|
|||
|
|
_dt(r["expires_at"]),
|
|||
|
|
_dt(r["authorized_at"]),
|
|||
|
|
])
|
|||
|
|
return _table(
|
|||
|
|
["ID", "scene_str", "设备", "状态", "用户ID", "创建时间", "过期时间", "授权完成时间"],
|
|||
|
|
out,
|
|||
|
|
"还没有扫码场景",
|
|||
|
|
)
|
|||
|
|
|
|||
|
|
|
|||
|
|
_CSS = """
|
|||
|
|
* { box-sizing: border-box; }
|
|||
|
|
body {
|
|||
|
|
font-family: -apple-system, "Segoe UI", "Microsoft YaHei", sans-serif;
|
|||
|
|
margin: 0; padding: 24px 28px 48px; background: #f5f6f8; color: #1f2328;
|
|||
|
|
font-size: 14px; line-height: 1.5;
|
|||
|
|
}
|
|||
|
|
h1 { font-size: 20px; margin: 0 0 2px; }
|
|||
|
|
h2 { font-size: 15px; margin: 30px 0 8px; padding-bottom: 6px; border-bottom: 1px solid #e3e5e8; }
|
|||
|
|
.meta { color: #6b7280; font-size: 12px; margin-bottom: 4px; }
|
|||
|
|
.meta a { color: #2563eb; text-decoration: none; }
|
|||
|
|
.meta a:hover { text-decoration: underline; }
|
|||
|
|
.cards { display: flex; flex-wrap: wrap; gap: 10px; }
|
|||
|
|
.card { background: #fff; border: 1px solid #e3e5e8; border-radius: 6px; padding: 10px 16px; min-width: 104px; }
|
|||
|
|
.card .n { font-size: 22px; font-weight: 600; font-variant-numeric: tabular-nums; }
|
|||
|
|
.card .l { font-size: 12px; color: #6b7280; margin-top: 2px; white-space: nowrap; }
|
|||
|
|
table { border-collapse: collapse; width: 100%; background: #fff; font-size: 12.5px; }
|
|||
|
|
th, td { border: 1px solid #e3e5e8; padding: 5px 9px; text-align: left; vertical-align: top; }
|
|||
|
|
th { background: #f0f1f3; font-weight: 600; white-space: nowrap; }
|
|||
|
|
code { font-family: Consolas, "Courier New", monospace; font-size: 12px; word-break: break-all; }
|
|||
|
|
.dim { color: #9ca3af; }
|
|||
|
|
.tag { display: inline-block; padding: 1px 7px; border-radius: 3px; font-size: 11px; white-space: nowrap; }
|
|||
|
|
.tag.active { background: #dcfce7; color: #166534; }
|
|||
|
|
.tag.pending { background: #fef9c3; color: #854d0e; }
|
|||
|
|
.tag.expired, .tag.exhausted, .tag.cancelled { background: #f3f4f6; color: #6b7280; }
|
|||
|
|
.tag.none { background: #fee2e2; color: #991b1b; }
|
|||
|
|
.empty { color: #9ca3af; font-size: 13px; padding: 8px 2px; }
|
|||
|
|
footer { margin-top: 36px; color: #9ca3af; font-size: 12px; }
|
|||
|
|
"""
|
|||
|
|
|
|||
|
|
|
|||
|
|
# ---------------------------------------------------------------------------
|
|||
|
|
# 路由
|
|||
|
|
# ---------------------------------------------------------------------------
|
|||
|
|
|
|||
|
|
@router.get("", response_class=HTMLResponse)
|
|||
|
|
async def admin_page(_: None = Depends(require_admin)) -> HTMLResponse:
|
|||
|
|
"""管理页：一次拉齐四个区块的数据后整页渲染"""
|
|||
|
|
async with db.acquire() as conn:
|
|||
|
|
async with conn.cursor(aiomysql.DictCursor) as cur:
|
|||
|
|
await cur.execute(OVERVIEW_SQL)
|
|||
|
|
overview = await cur.fetchone()
|
|||
|
|
|
|||
|
|
await cur.execute(USERS_SQL)
|
|||
|
|
users = await cur.fetchall()
|
|||
|
|
|
|||
|
|
await cur.execute(USAGE_SQL)
|
|||
|
|
usage = await cur.fetchall()
|
|||
|
|
|
|||
|
|
await cur.execute(SESSIONS_SQL)
|
|||
|
|
sessions = await cur.fetchall()
|
|||
|
|
|
|||
|
|
await cur.execute(SCENES_SQL)
|
|||
|
|
scenes = await cur.fetchall()
|
|||
|
|
|
|||
|
|
now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
|
|||
|
|
page = f"""<!doctype html>
|
|||
|
|
<html lang="zh-CN">
|
|||
|
|
<head>
|
|||
|
|
<meta charset="utf-8">
|
|||
|
|
<meta name="viewport" content="width=device-width, initial-scale=1">
|
|||
|
|
<title>CadOcr 授权管理</title>
|
|||
|
|
<style>{_CSS}</style>
|
|||
|
|
</head>
|
|||
|
|
<body>
|
|||
|
|
<h1>CadOcr 微信授权管理</h1>
|
|||
|
|
<div class="meta">数据生成时间 {_e(now)} · <a href="">刷新</a></div>
|
|||
|
|
|
|||
|
|
<h2>概览</h2>
|
|||
|
|
{_render_overview(overview)}
|
|||
|
|
|
|||
|
|
<h2>用户与当前授权（最多 200 条，按 ID 倒序）</h2>
|
|||
|
|
{_render_users(users)}
|
|||
|
|
|
|||
|
|
<h2>最近使用记录（最多 50 条）</h2>
|
|||
|
|
{_render_usage(usage)}
|
|||
|
|
|
|||
|
|
<h2>会话令牌（最多 50 条）</h2>
|
|||
|
|
{_render_sessions(sessions)}
|
|||
|
|
|
|||
|
|
<h2>扫码场景（最多 50 条）</h2>
|
|||
|
|
{_render_scenes(scenes)}
|
|||
|
|
|
|||
|
|
<footer>只读页面，不提供任何修改操作 · 数据库 {_e(MYSQL_DB)}</footer>
|
|||
|
|
</body>
|
|||
|
|
</html>"""
|
|||
|
|
return HTMLResponse(content=page)
|