修复：补全反馈设备与系统版本

反馈列表按同一用户、同一设备和提交时间补全可读机型及厂商系统版本，并更新 mock 数据与回归测试。

app/admin/repositories/queries.py

@@ -9,7 +9,7 @@ from datetime import date, datetime, time, timedelta, timezone
 from decimal import ROUND_HALF_UP, Decimal
 from zoneinfo import ZoneInfo
 
-from sqlalchemy import Select, asc, case, desc, func, or_, select
+from sqlalchemy import Select, and_, asc, case, desc, func, or_, select
 from sqlalchemy.orm import Session
 
 from app.core import rewards
@@ -45,6 +45,72 @@ _FEED_SCENE_LABEL = {
 }
 
 
+_DEVICE_MARKETING_NAMES = {
+    "23078RKD5C": "Redmi K60 至尊版",
+    "M2012K11AC": "Redmi K40",
+    "PJA110": "一加 Ace 2 Pro",
+    "PPG-AN00": "荣耀 GT Pro",
+    "V2166BA": "vivo Y77e",
+    "V2309A": "vivo X100",
+}
+
+
+def _device_marketing_name(model: str | None) -> str | None:
+    """把线上已知 Build.MODEL 编码转成用户可识别的商品名。"""
+    if not model:
+        return None
+    return _DEVICE_MARKETING_NAMES.get(model.strip().upper())
+
+
+def _attach_feedback_device_details(db: Session, feedbacks: list[Feedback]) -> None:
+    """按同一用户、同一设备编码及提交时间补齐厂商和 ROM 大版本。"""
+    candidates = [
+        item for item in feedbacks if item.device_model and item.device_model.strip()
+    ]
+    for item in candidates:
+        item.device_model_name = _device_marketing_name(item.device_model)
+        item.device_manufacturer = None
+        item.rom_version = None
+    if not candidates:
+        return
+
+    ranked = (
+        select(
+            Feedback.id.label("feedback_id"),
+            ComparisonRecord.device_manufacturer.label("device_manufacturer"),
+            ComparisonRecord.rom_version.label("rom_version"),
+            func.row_number()
+            .over(
+                partition_by=Feedback.id,
+                order_by=(
+                    ComparisonRecord.created_at.desc(),
+                    ComparisonRecord.id.desc(),
+                ),
+            )
+            .label("row_num"),
+        )
+        .join(
+            ComparisonRecord,
+            and_(
+                ComparisonRecord.user_id == Feedback.user_id,
+                ComparisonRecord.device_model == Feedback.device_model,
+                ComparisonRecord.created_at <= Feedback.created_at,
+            ),
+        )
+        .where(Feedback.id.in_([item.id for item in candidates]))
+        .subquery()
+    )
+    details = {
+        row.feedback_id: row
+        for row in db.execute(select(ranked).where(ranked.c.row_num == 1)).all()
+    }
+
+    for item in candidates:
+        detail = details.get(item.id)
+        item.device_manufacturer = detail.device_manufacturer if detail else None
+        item.rom_version = detail.rom_version if detail else None
+
+
 def cursor_paginate(
     db: Session, stmt: Select, id_col, *, limit: int, cursor: int | None
 ) -> tuple[list, int | None]:
@@ -817,6 +883,7 @@ def list_feedbacks(
         db, stmt, (order_fn(sort_col), id_order), limit=limit, cursor=cursor
     )
     _attach_user_info(db, items)  # 列表展示完整手机号(点手机号查该用户全部反馈)
+    _attach_feedback_device_details(db, items)
     return items, next_cursor, total
 
 

app/admin/schemas/feedback.py

@@ -32,7 +32,10 @@ class FeedbackOut(BaseModel):
     # 提交端环境快照(feedback 表列):提交版本号 / 机型OS版本;改版前的历史反馈为 None
     app_version: str | None = None
     device_model: str | None = None
+    device_model_name: str | None = None
+    device_manufacturer: str | None = None
     rom_name: str | None = None
+    rom_version: int | None = None
     android_version: str | None = None
     # 联表瞬态字段(queries._attach_user_info 挂):列表展示完整手机号,点手机号查该用户全部反馈
     phone: str | None = None

scripts/seed_mock_feedback.py

@@ -175,22 +175,22 @@ def seed(db) -> list[Feedback]:
         # 普通反馈 · 新端(带环境快照)· 无图
         fb("13255550001", "签到金币到账有时候会延迟一两分钟,能不能做成实时到账?",
            source="profile",
-           app_version="2.3.1", device_model="PJA110", rom_name="ColorOS", android_version="14",
+           app_version="2.3.1", device_model="PJA110", rom_name="ColorOS 14", android_version="14",
            created=ago(minutes=6)),
         # 比价反馈 · scene=优惠不对 · 新端 · 2 图
         fb("13255550002", "这家店京东外卖的到手价比你们算出来的最低价还低,截图为证,麻烦核实。",
            source="comparison", scene="优惠不对", images=[imgs[0], imgs[1]],
-           app_version="2.3.1", device_model="M2012K11AC", rom_name="MIUI", android_version="13",
+           app_version="2.3.1", device_model="M2012K11AC", rom_name="MIUI 14", android_version="13",
            created=ago(minutes=22)),
         # 比价反馈 · scene=找错商品 · 新端 · 无图
         fb("13255550002", "比价结果里的商品跟我搜的不是同一个规格,数量对不上。",
            source="comparison", scene="找错商品",
-           app_version="2.3.0", device_model="V2309A", rom_name="OriginOS", android_version="14",
+           app_version="2.3.0", device_model="V2309A", rom_name="OriginOS 4", android_version="14",
            created=ago(hours=1)),
         # 普通反馈 · 新端 · 1 图(表扬 + 小问题)
         fb("13255550003", "提现秒到账,好评!顺手反馈个小 bug:金币记录页偶尔白屏,要退出去重进。",
            source="profile", images=[imgs[2]],
-           app_version="2.3.1", device_model="23078RKD5C", rom_name="MIUI", android_version="14",
+           app_version="2.3.1", device_model="23078RKD5C", rom_name="MIUI 14", android_version="14",
            created=ago(hours=3)),
         # 比价反馈 · scene=比价太慢 · 历史数据(env 全 NULL、contact 有值)
         fb("13255550004", "比价转圈太久了,经常要等十几秒才出结果,体验不太好。",
@@ -206,7 +206,7 @@ def seed(db) -> list[Feedback]:
            source="profile", images=[imgs[3]],
            status="adopted", reward_coins=2000,
            review_note="有效产品建议,已排期到 2.4.0", admin_reply="感谢反馈!该功能已在规划中,金币奖励已发放~",
-           app_version="2.2.8", device_model="PJA110", rom_name="ColorOS", android_version="13",
+           app_version="2.2.8", device_model="PJA110", rom_name="ColorOS 13", android_version="13",
            created=ago(days=2)),
 
         # ===== 未采纳 rejected(带原因 + 回复)=====
@@ -214,7 +214,7 @@ def seed(db) -> list[Feedback]:
            source="comparison", scene="价格不准",
            status="rejected", reject_reason="截图价格为限时活动价且已过期,不满足「长期可复现更低价」条件,暂不采纳。",
            admin_reply="感谢参与,本次未通过,欢迎继续上报有效更低价~",
-           app_version="2.3.0", device_model="M2012K11AC", rom_name="MIUI", android_version="13",
+           app_version="2.3.0", device_model="M2012K11AC", rom_name="MIUI 14", android_version="13",
            created=ago(days=3)),
     ]
     db.add_all(feedbacks)

tests/test_admin_feedback_device_details.py

@@ -0,0 +1,71 @@
+from datetime import datetime, timedelta, timezone
+
+from app.admin.repositories import queries
+from app.db.session import SessionLocal
+from app.models.comparison import ComparisonRecord
+from app.models.feedback import Feedback
+from app.repositories import user as user_repo
+
+
+def test_feedback_device_details_use_same_user_and_model() -> None:
+    with SessionLocal() as db:
+        submitted_at = datetime.now(timezone.utc)
+        user = user_repo.upsert_user_for_login(
+            db,
+            phone="13800009876",
+            register_channel="sms",
+        )
+        db.add_all(
+            [
+                ComparisonRecord(
+                    user_id=user.id,
+                    trace_id="feedback-device-v2166ba",
+                    device_model="V2166BA",
+                    device_manufacturer="vivo",
+                    rom_name="OriginOS",
+                    rom_version=13,
+                    created_at=submitted_at - timedelta(minutes=5),
+                ),
+                ComparisonRecord(
+                    user_id=user.id,
+                    trace_id="feedback-device-other",
+                    device_model="OTHER-CODE",
+                    device_manufacturer="Other",
+                    rom_name="OtherOS",
+                    rom_version=99,
+                    created_at=submitted_at - timedelta(minutes=5),
+                ),
+                ComparisonRecord(
+                    user_id=user.id,
+                    trace_id="feedback-device-future-upgrade",
+                    device_model="V2166BA",
+                    device_manufacturer="vivo-new",
+                    rom_name="OriginOS",
+                    rom_version=99,
+                    created_at=submitted_at + timedelta(minutes=5),
+                ),
+                Feedback(
+                    user_id=user.id,
+                    content="设备信息补全测试",
+                    contact="",
+                    status="pending",
+                    device_model="V2166BA",
+                    rom_name="OriginOS",
+                    android_version="13",
+                    created_at=submitted_at,
+                ),
+            ]
+        )
+        db.commit()
+
+        items, _next_cursor, total = queries.list_feedbacks(
+            db,
+            user_id=user.id,
+            limit=20,
+        )
+
+        assert total == 1
+        assert items[0].device_model == "V2166BA"
+        assert items[0].device_model_name == "vivo Y77e"
+        assert items[0].device_manufacturer == "vivo"
+        assert items[0].rom_version == 13