knight-gt/ai-guide-cloud
1
1import asyncio2from collections import Counter3import hashlib4import hmac5import os6import sqlite37import uuid8from pathlib import Path9 10 11DATA_DIR = Path(__file__).resolve().parent / "data"12DB_PATH = DATA_DIR / "user_data.db"13DEFAULT_AVATAR_ID = "guide3"14DEFAULT_VOICE_ID = "zh-CN-XiaoxiaoNeural"15PASSWORD_ITERATIONS = 120_00016 17def _dedupe_terms(*terms: str) -> tuple[str, ...]:18 return tuple(dict.fromkeys(term for term in terms if term))19 20 21POSITIVE_SENTIMENT_KEYWORDS = _dedupe_terms(22 "喜欢", "漂亮", "震撼", "方便", "热情", "贴心", "干净", "满意",23 "值得", "舒服", "清楚", "专业", "不错", "顺利", "惊喜", "周到",24 "亲切", "负责", "细致", "耐心", "友好", "温柔", "温和", "礼貌",25 "高效", "快速", "准确", "清晰", "明确", "醒目", "直观", "实用",26 "合理", "顺畅", "便捷",27 "省心", "安心", "安全", "规范", "有序", "整洁", "清爽", "清新",28 "优美", "壮观", "精彩", "生动", "详细", "丰富", "有趣", "好玩",29 "好看", "出片", "舒适", "愉快", "开心", "轻松", "放松", "便利",30 "齐全", "完善", "充足", "明亮", "宽敞", "好找", "稳妥", "及时", "主动",31 "认真", "用心", "可靠", "满分", "加分", "超值", "实惠",32 "划算", "合适", "可口", "好吃", "美味", "解渴", "凉快", "暖心",33 "惊艳", "治愈", "震憾", "耐看", "省力", "放心", "贴切", "灵活",34 "人性化", "有耐心", "有礼貌", "有温度", "有特色", "有文化", "有氛围",35 "有秩序", "有帮助", "有亮点", "有收获", "有诚意", "可接受", "可放心",36 "可推荐", "可再来", "适合拍照", "适合亲子", "适合休闲", "适合散步",37 "good", "great", "nice", "beautiful", "excellent", "perfect",38)39POSITIVE_SENTIMENT_PHRASES = _dedupe_terms(40 "值得推荐", "很推荐", "非常推荐", "强烈推荐", "值得来", "值得一来",41 "值得打卡", "值得二刷", "还想再来", "下次还来", "还会再来", "推荐朋友来",42 "带家人来", "想带朋友来", "值得专门来", "值得排队", "值得等待", "值得体验",43 "挺直观", "很直观", "导览直观", "地图直观", "标注醒目", "地图醒目",44 "入口好找", "出口好找", "位置好找", "找景点很快", "找景点方便",45 "挺有用", "很有用", "确实有用", "还挺有用", "确实能用", "功能能用",46 "至少能找到", "能找到大概位置", "还算清楚", "还算明白", "说明明白",47 "说明还算明白", "不复杂", "操作不复杂", "不卡", "切换不卡",48 "挺快", "反应挺快", "回复很快", "马上解决", "很实用", "提醒实用",49 "有重点", "内容有重点", "愿意听",50 "体验很好", "体验很棒", "体验很赞", "体验不错", "体验满意", "体验舒适",51 "体验愉快", "体验顺畅", "体验很顺", "体验轻松", "体验省心", "体验感好",52 "整体满意", "整体很满意", "整体不错", "整体很好", "整体很棒", "整体顺利",53 "整体舒服", "整体省心", "整体推荐", "总体满意", "总体不错", "总体很好",54 "玩得开心", "玩得舒服", "玩得尽兴", "玩得轻松", "玩得很值", "玩得很顺",55 "游览顺畅", "游玩顺利", "游玩轻松", "行程顺利", "行程合理", "行程舒服",56 "服务很好", "服务很棒", "服务很赞", "服务到位", "服务贴心", "服务周到",57 "服务热情", "服务专业", "服务细致", "服务耐心", "服务主动", "服务及时",58 "服务满分", "服务加分", "服务有温度", "态度很好", "态度不错", "态度亲切",59 "态度热情", "态度温和", "态度友好", "态度耐心", "态度礼貌", "态度诚恳",60 "工作人员热情", "工作人员负责", "工作人员耐心", "工作人员专业", "工作人员友好",61 "工作人员贴心", "工作人员主动", "工作人员细心", "工作人员靠谱", "工作人员有礼貌",62 "导游专业", "导游热情", "导游耐心", "导游负责", "导游讲得好", "导游很细心",63 "讲解很清楚", "讲解很详细", "讲解很生动", "讲解很专业", "讲解很有趣",64 "讲解有耐心", "讲解有文化", "讲解内容丰富", "讲得明白", "讲得清楚",65 "讲得详细", "讲得生动", "回答及时", "回答清楚", "回答准确", "有问必答",66 "帮助及时", "主动帮忙", "处理及时", "处理很快", "解决问题快", "指引清楚",67 "指引明确", "指引到位", "路线清楚", "路线合理", "路线顺畅", "路线好走",68 "路线安排合理", "路线推荐合理", "路标清晰", "路牌清楚", "标识清楚", "标识明确",69 "指示明确", "导航准确", "动线顺畅", "动线合理", "找路方便", "交通方便",70 "停车方便", "入口好找", "出口好找", "换乘方便", "观光车方便", "上下山方便",71 "入园顺利", "检票顺利", "预约方便", "购票方便", "退票方便", "验票很快",72 "票价合理", "价格合理", "性价比高", "值回票价", "门票值得", "优惠清楚",73 "排队很快", "等待不久", "排队可接受", "队伍有序", "秩序很好", "现场有序",74 "管理规范", "组织有序", "高峰也顺", "环境很好", "环境优美", "环境干净",75 "环境整洁", "环境舒服", "卫生干净", "卫生不错", "厕所干净", "洗手间干净",76 "地面干净", "空气清新", "味道清新", "景色很好", "风景很好", "景色漂亮",77 "风景漂亮", "景色震撼", "风景震撼", "景观壮观", "景观漂亮", "景区很美",78 "景区不错", "景区漂亮", "大佛震撼", "梵宫漂亮", "九龙灌浴精彩", "山顶风景好",79 "很好看", "很好玩", "很出片", "拍照好看", "拍照出片", "表演精彩",80 "演出精彩", "文化氛围好", "故事有意思", "内容丰富", "项目有趣", "好玩有趣",81 "适合拍照", "适合亲子", "适合老人", "适合慢慢逛", "餐饮不错", "小吃好吃",82 "饭菜可口", "饮品不错", "饮水方便", "补水方便", "休息方便", "座位充足",83 "设施完善", "设施齐全", "设施好用", "充电方便", "避雨方便", "防晒提醒贴心",84 "雨天也方便", "天气提醒及时", "安全感强", "很有安全感", "心情很好",85 "心情舒畅", "很有收获", "收获很多", "不虚此行", "超出预期", "超过预期",86 "超级满意", "非常满意", "特别满意", "真的不错", "真不错", "真棒",87 "真好", "太好了", "太赞了", "太喜欢了", "特别好", "非常好",88 "挺好的", "挺不错", "很省心", "特别省心", "方便省心", "没踩坑",89)90NEGATIVE_SENTIMENT_PHRASES = _dedupe_terms(91 "不喜欢", "不太喜欢", "不是很喜欢", "不怎么喜欢", "不满意", "不推荐",92 "不想再来", "不会再来", "再也不来", "不值得来", "不值票价", "不值这个价",93 "不好用", "一点都不好用", "一点不好用", "不方便", "不清楚", "不明白",94 "看不懂", "没看懂", "听不清", "讲不明白", "太敷衍", "介绍敷衍", "说得敷衍",95 "说得太普通", "太普通", "不知道景点有什么意思", "太机械", "很机械",96 "不自然", "很不自然", "像复制粘贴", "复制粘贴", "没什么重点", "没有重点",97 "讲解不清楚", "讲解不详细", "讲解不专业", "讲解没意思", "指引不清楚",98 "路线不清楚", "路线不合理", "路线太绕", "标识不清楚", "标识不明显",99 "路牌不明显", "指示不明确", "找不到路", "找不到入口", "找不到出口",100 "找不到厕所", "找不到洗手间", "找不到地方", "找半天找不到地方", "没有找到", "看不到指示", "没有指示",101 "导航不准", "路线绕远", "绕了好多路", "跟现场不一致", "路线不一致",102 "现场不一致", "发现封路", "动线混乱", "容易迷路", "走错路", "绕来绕去",103 "排队太久", "等太久", "等了很久", "等待太久", "队伍太长", "队伍很长",104 "入园太慢", "检票太慢", "处理太慢", "反应太慢", "响应太慢", "点了半天才出来",105 "老是卡住", "页面卡住", "点什么都没反应", "没反应", "人太多", "太拥挤", "特别拥挤",106 "挤得难受", "挤得不舒服", "现场混乱", "秩序不好", "管理混乱", "安排混乱",107 "服务差", "服务不好", "服务很差", "服务太差", "服务不到位", "服务不贴心",108 "服务不周到", "服务不专业", "服务不及时", "服务让人生气", "态度差",109 "态度不好", "态度很差", "态度太差", "态度冷淡", "态度敷衍", "态度生硬",110 "态度不耐烦", "工作人员不耐烦", "工作人员冷漠", "工作人员敷衍", "工作人员不负责",111 "工作人员态度差", "工作人员不专业", "导游不专业", "导游讲不清", "导游不耐烦",112 "回答不及时", "回答不清楚", "答非所问", "帮助不及时", "没人管", "无人处理",113 "体验差", "体验不好", "体验很差", "体验太差", "体验糟糕", "体验不舒服",114 "体验不顺畅", "体验很失望", "体验没意思", "感觉不好", "感觉有点麻烦", "心情不好", "很失望",115 "特别失望", "有点失望", "太失望", "令人失望", "比较失望", "没意思",116 "很无聊", "太无聊", "内容无聊", "表演无聊", "项目无聊", "景点无聊",117 "不好玩", "不好看", "拍照不好看", "景色失望", "风景失望", "景观一般",118 "有点乱", "信息太乱", "看着费劲", "看着很费劲", "用起来费劲", "用起来不舒服",119 "太累", "走得太累", "爬得太累", "太晒", "太热", "太冷",120 "闷得难受", "淋雨了", "路太滑", "地面太滑", "没地方躲雨", "没地方休息",121 "休息不方便", "座位不够", "座位太少", "设施不足", "设施老旧", "设施损坏",122 "设备故障", "充电不方便", "厕所脏", "洗手间脏", "卫生差", "环境脏",123 "环境不好", "环境很乱", "垃圾很多", "垃圾太多", "味道难闻", "异味很重",124 "地面很脏", "不干净", "餐饮差", "餐饮不好", "小吃难吃", "饮品难喝",125 "饭菜不好吃", "价格太高", "票价太高", "票价偏高", "票价不合理", "票价不值",126 "票太贵", "门票贵", "门票太贵", "门票偏贵", "门票价格高", "门票不值",127 "太贵", "收费太贵", "性价比低",128 "不划算", "不实惠", "感觉被坑", "有点坑人", "乱收费", "标价不清",129 "优惠不清楚", "预约麻烦", "购票麻烦", "退票麻烦", "入园麻烦", "流程繁琐",130 "信息不准", "信息错误", "信息太少", "景点信息太少", "信息过时", "开放时间不对",131 "优惠规则看不明白", "解释含糊", "说明含糊", "答不到点上", "回答很空",132 "回答空泛", "帮助不大", "没什么帮助", "帮不上忙", "基本帮不上忙",133 "休息点推荐太少", "推荐太少", "位置写错", "写错了", "白跑一趟",134 "整体粗糙", "感觉很粗糙", "功能鸡肋", "有点鸡肋", "有用的不多",135 "不如自己看地图", "解释不清", "讲解敷衍", "噪音太大", "太吵",136 "没有安全感", "安全隐患", "有安全隐患", "踩坑了", "坑了", "坑人",137 "糟心", "闹心", "气死了", "气死人", "太气人", "我生气了",138 "不要理你", "不想理你", "不理你", "讨厌你", "差评", "必须差评",139 "非常差", "特别差", "很差劲", "烂透了", "太离谱", "真离谱",140 "真无语", "很崩溃", "受不了", "令人不适", "不舒服", "又累又烦",141 "白来了", "后悔来了", "浪费时间", "浪费钱", "影响心情", "没有达到预期",142 "没达到预期", "一点也不好", "路线推荐不太行", "不太行",143)144NEGATIVE_SENTIMENT_KEYWORDS = _dedupe_terms(145 "拥挤", "脏乱", "迷路", "投诉", "失望", "冷淡", "敷衍", "生硬",146 "不耐烦", "冷漠", "敷衍", "模糊", "机械", "生硬", "糟糕", "无聊",147 "难受", "费劲", "不适", "糟心", "闹心",148 "崩溃", "离谱", "无语", "坑人", "宰客", "乱收费", "难找", "绕远",149 "混乱", "排队久", "队伍长", "等待久", "人太多", "不清晰", "不明确",150 "不专业", "不负责", "不及时", "不到位", "不贴心", "不周到", "不合理",151 "不顺畅", "不方便", "不干净", "异味", "难闻", "垃圾多", "设施旧",152 "设施坏", "故障", "损坏", "太贵", "票贵", "票价贵", "门票贵",153 "昂贵", "价格高", "票价高", "门票高", "性价比低",154 "不划算", "不实惠", "麻烦", "繁琐", "拥堵", "太吵", "噪音大",155 "太晒", "闷热", "太冷", "淋雨", "路滑", "危险", "隐患", "担心",156 "焦虑", "生气", "气愤", "恼火", "讨厌", "厌烦", "反感", "差劲",157 "卡顿", "卡住", "页面卡", "有点卡", "没反应", "过时", "不一致",158 "封路", "写错", "粗糙", "鸡肋", "太普通",159 "烂透", "难吃", "难喝", "不好吃", "不好喝", "口味差", "没座位",160 "座位少", "不省心", "不安心", "不舒服", "没意思", "看不懂",161 "听不清", "不好玩", "不好看", "不满意", "不推荐", "不值当",162 "不值票", "不友好", "不礼貌", "不靠谱", "不准确", "不安全",163 "bad", "expensive", "crowded", "dirty", "awful", "terrible",164)165ABUSIVE_SENTIMENT_KEYWORDS = _dedupe_terms(166 "sb", "傻逼", "垃圾服务", "垃圾态度", "垃圾体验", "笨蛋", "蠢货",167 "闭嘴", "废物", "有病", "神经病", "脑子有病", "滚开", "别烦我",168 "烦死了", "气死我了", "讨厌死了", "恶心人", "真恶心", "太恶心",169 "烂服务", "破服务", "破地方", "破景区", "什么玩意", "一塌糊涂",170)171SENTIMENT_NEGATORS = ("不", "没", "没有", "无", "别", "不要", "不是", "不太", "不怎么")172DEGREE_WEIGHTS = {173 "非常": 1.6,174 "特别": 1.6,175 "太": 1.5,176 "很": 1.3,177 "比较": 1.15,178 "有点": 0.8,179 "稍微": 0.7,180}181CONTRAST_MARKERS = ("但是", "但", "不过", "可是", "然而", "就是", "倒是")182NEUTRAL_FEEDBACK_PHRASES = ("一般", "还行", "还可以", "普通", "凑合", "还好")183NON_FEEDBACK_EXACT = {184 "你好", "您好", "hello", "hi", "嗨", "在吗", "谢谢", "再见",185}186NON_FEEDBACK_HINTS = (187 "吗", "呢", "么", "多少", "几", "哪里", "在哪", "有没有", "有什么",188 "怎么", "如何", "能不能", "可以", "请", "帮我", "给我", "推荐",189 "路线", "门票", "票价", "价格", "多少钱", "厕所", "洗手间", "小吃",190 "餐厅", "名人", "来过", "你是", "男的", "女的", "个人资料", "介绍一下",191)192QUESTION_HINTS = (193 "吗", "呢", "么", "多少", "哪里", "在哪", "有没有", "有什么",194 "怎么", "如何", "能不能", "请问", "?",195)196DIRECT_QUESTION_ENDINGS = ("吗", "呢", "么", "?", "？")197FEEDBACK_TARGET_HINTS = (198 "景区", "景点", "服务", "工作人员", "导游", "讲解", "路线", "门票",199 "票价", "排队", "厕所", "洗手间", "餐饮", "小吃", "环境", "风景",200 "景色", "体验", "梵宫", "大佛", "九龙灌浴", "山顶", "导览", "地图",201 "页面", "功能", "操作", "介绍", "内容", "ai导游", "ai 导游", "语音",202 "反应", "响应", "信息", "标注", "提醒", "回复", "开放时间", "位置",203 "优惠", "说明", "智能导览",204)205SENTIMENT_TOPICS = {206 "服务接待": ("服务", "工作人员", "导游", "态度", "讲解", "客服", "帮助", "问询", "回复"),207 "交通路线": ("路线", "怎么走", "迷路", "路标", "指示", "导航", "地图", "导览", "找景点", "观光车", "停车", "入口", "出口"),208 "门票排队": ("门票", "票", "排队", "入园", "预约", "价格", "贵", "优惠"),209 "环境卫生": ("卫生", "厕所", "洗手间", "垃圾", "脏", "干净", "环境", "味道"),210 "餐饮休息": ("吃", "餐饮", "饭", "咖啡", "水", "休息", "座位", "小吃"),211 "景点体验": ("景点", "表演", "拍照", "大佛", "梵宫", "九龙灌浴", "风景", "景色", "好玩", "无聊", "介绍", "内容", "页面", "功能", "操作", "语音", "反应", "响应", "信息", "标注", "提醒", "开放时间", "说明", "智能导览"),212 "天气舒适": ("天气", "热", "冷", "下雨", "晒", "雨", "风", "防晒"),213}214TOPIC_SUGGESTIONS = {215 "服务接待": "加强入口、问询点和重点景点的服务话术培训，优先处理游客提到的态度、讲解不清或帮助不及时问题。",216 "交通路线": "补强路线指引、路牌和地图热点说明，在热门岔路口增加更明确的下一站提示。",217 "门票排队": "关注票价解释、预约流程和高峰排队提示，提前展示预计等待时间和优惠政策。",218 "环境卫生": "把负面集中区域纳入巡检清单，增加卫生间、垃圾桶和休息区的高峰巡检频次。",219 "餐饮休息": "优化餐饮、饮水和休息点的导览说明，在游客疲劳节点主动推荐附近休息设施。",220 "景点体验": "复盘被频繁吐槽的景点讲解内容和动线安排，增强拍照点、演出时间和文化故事说明。",221 "天气舒适": "根据天气主动推送防晒、避雨、防滑和补水提醒，并推荐更舒适的室内外路线组合。",222 "综合体验": "继续收集游客原话，优先复盘高频负面问题，再把处理结果补进知识库和现场服务流程。",223}224 225 226def _connect() -> sqlite3.Connection:227 conn = sqlite3.connect(DB_PATH)228 conn.row_factory = sqlite3.Row229 return conn230 231 232def _init_db_sync() -> None:233 DATA_DIR.mkdir(parents=True, exist_ok=True)234 with _connect() as conn:235 conn.execute(236 """237 CREATE TABLE IF NOT EXISTS user_queries (238 id INTEGER PRIMARY KEY AUTOINCREMENT,239 user_id TEXT NOT NULL,240 question TEXT NOT NULL,241 answer TEXT NOT NULL,242 mode TEXT NOT NULL DEFAULT 'rag',243 sentiment TEXT NOT NULL DEFAULT 'neutral',244 sentiment_score REAL NOT NULL DEFAULT 0,245 sentiment_reason TEXT NOT NULL DEFAULT '常规咨询',246 sentiment_topic TEXT NOT NULL DEFAULT '综合体验',247 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP248 )249 """250 )251 columns = {252 row["name"]253 for row in conn.execute("PRAGMA table_info(user_queries)").fetchall()254 }255 if "mode" not in columns:256 conn.execute(257 "ALTER TABLE user_queries ADD COLUMN mode TEXT NOT NULL DEFAULT 'rag'"258 )259 sentiment_columns = {260 "sentiment": "TEXT NOT NULL DEFAULT 'neutral'",261 "sentiment_score": "REAL NOT NULL DEFAULT 0",262 "sentiment_reason": "TEXT NOT NULL DEFAULT '常规咨询'",263 "sentiment_topic": "TEXT NOT NULL DEFAULT '综合体验'",264 }265 for column, definition in sentiment_columns.items():266 if column not in columns:267 conn.execute(f"ALTER TABLE user_queries ADD COLUMN {column} {definition}")268 conn.execute(269 """270 CREATE TABLE IF NOT EXISTS guide_settings (271 id INTEGER PRIMARY KEY CHECK (id = 1),272 avatar_id TEXT NOT NULL,273 voice_id TEXT NOT NULL,274 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP275 )276 """277 )278 conn.execute(279 """280 CREATE TABLE IF NOT EXISTS users (281 id TEXT PRIMARY KEY,282 username TEXT NOT NULL UNIQUE,283 password_hash TEXT NOT NULL,284 password_salt TEXT NOT NULL,285 nickname TEXT NOT NULL,286 avatar TEXT NOT NULL DEFAULT '',287 bio TEXT NOT NULL DEFAULT '',288 interests TEXT NOT NULL DEFAULT '',289 traveler_type TEXT NOT NULL DEFAULT '',290 personality TEXT NOT NULL DEFAULT 'friendly',291 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,292 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP293 )294 """295 )296 user_columns = {297 row["name"]298 for row in conn.execute("PRAGMA table_info(users)").fetchall()299 }300 if "personality" not in user_columns:301 conn.execute(302 "ALTER TABLE users ADD COLUMN personality TEXT NOT NULL DEFAULT 'friendly'"303 )304 conn.execute(305 """306 CREATE TABLE IF NOT EXISTS knowledge_gaps (307 id INTEGER PRIMARY KEY AUTOINCREMENT,308 question TEXT NOT NULL UNIQUE,309 mode TEXT NOT NULL DEFAULT 'rag',310 count INTEGER NOT NULL DEFAULT 1,311 status TEXT NOT NULL DEFAULT 'pending',312 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,313 updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP314 )315 """316 )317 conn.execute(318 """319 CREATE TABLE IF NOT EXISTS manual_knowledge (320 id INTEGER PRIMARY KEY AUTOINCREMENT,321 question TEXT NOT NULL,322 answer TEXT NOT NULL,323 category TEXT NOT NULL DEFAULT 'general',324 created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP325 )326 """327 )328 conn.commit()329 330 331async def init_db() -> None:332 await asyncio.to_thread(_init_db_sync)333 334 335def _text_contains_any(text: str, words: tuple[str, ...] | set[str]) -> bool:336 return any(word.lower() in text for word in words)337 338 339def _weighted_hit(text: str, start: int) -> float:340 prefix = text[max(0, start - 4):start]341 for word, weight in DEGREE_WEIGHTS.items():342 if word in prefix:343 return weight344 return 1.0345 346 347def _prefix_has_active_negator(prefix: str) -> bool:348 compact = "".join(prefix.split())349 return any(compact.endswith(negator) for negator in SENTIMENT_NEGATORS)350 351 352def _split_for_contrast(text: str) -> list[tuple[str, float]]:353 for marker in CONTRAST_MARKERS:354 if marker in text:355 before, after = text.split(marker, 1)356 return [(before, 0.65), (after, 1.35)]357 return [(text, 1.0)]358 359 360def _match_weighted_terms(361 text: str,362 terms: tuple[str, ...],363 base_polarity: int,364) -> tuple[float, list[str]]:365 score = 0.0366 hits = []367 for segment, segment_weight in _split_for_contrast(text):368 for term in terms:369 keyword = term.lower()370 start = segment.find(keyword)371 if start < 0:372 continue373 374 prefix = segment[max(0, start - 5):start]375 polarity = base_polarity376 hit_label = term377 if _prefix_has_active_negator(prefix):378 polarity *= -1379 hit_label = f"{prefix}{term}".strip()380 381 score += polarity * _weighted_hit(segment, start) * segment_weight382 if hit_label not in hits:383 hits.append(hit_label)384 return score, hits385 386 387def analyze_visitor_sentiment(question: str, answer: str = "") -> dict:388 text = (question or answer or "").strip().lower()389 compact_text = "".join(text.split())390 if not compact_text:391 return {392 "sentiment": "not_feedback",393 "sentiment_score": 0,394 "sentiment_reason": "空文本未形成评价",395 "sentiment_topic": "非评价",396 "keywords": [],397 }398 399 topic_scores = {400 topic: sum(1 for word in keywords if word.lower() in text)401 for topic, keywords in SENTIMENT_TOPICS.items()402 }403 topic = max(topic_scores, key=topic_scores.get, default="综合体验")404 if topic_scores.get(topic, 0) == 0:405 topic = "综合体验"406 407 score = 0.0408 all_hits = []409 for terms, polarity in (410 (POSITIVE_SENTIMENT_PHRASES, 1),411 (NEGATIVE_SENTIMENT_PHRASES, -1),412 (ABUSIVE_SENTIMENT_KEYWORDS, -1),413 (POSITIVE_SENTIMENT_KEYWORDS, 1),414 (NEGATIVE_SENTIMENT_KEYWORDS, -1),415 ):416 term_score, hits = _match_weighted_terms(text, terms, polarity)417 score += term_score418 all_hits.extend(hit for hit in hits if hit not in all_hits)419 420 has_abusive_or_anger_signal = _text_contains_any(421 text,422 ABUSIVE_SENTIMENT_KEYWORDS + ("生气", "气死", "气人", "不要理你", "不理你", "讨厌你"),423 )424 if has_abusive_or_anger_signal:425 topic = "服务接待"426 427 has_neutral_feedback = _text_contains_any(text, NEUTRAL_FEEDBACK_PHRASES)428 has_feedback_target = _text_contains_any(text, FEEDBACK_TARGET_HINTS)429 has_feedback_signal = bool(all_hits) or has_neutral_feedback430 has_explicit_feedback_phrase = _text_contains_any(431 text,432 POSITIVE_SENTIMENT_PHRASES + NEGATIVE_SENTIMENT_PHRASES + NEUTRAL_FEEDBACK_PHRASES,433 )434 is_question_like = _text_contains_any(text, QUESTION_HINTS)435 is_direct_question = compact_text.endswith(DIRECT_QUESTION_ENDINGS)436 is_plain_non_feedback = (437 compact_text in NON_FEEDBACK_EXACT438 or is_direct_question439 or (is_question_like and not has_explicit_feedback_phrase)440 or (441 not has_feedback_signal442 and _text_contains_any(text, NON_FEEDBACK_HINTS)443 )444 )445 if is_plain_non_feedback or not has_feedback_signal:446 return {447 "sentiment": "not_feedback",448 "sentiment_score": 0,449 "sentiment_reason": "普通咨询或闲聊，不计入景区情感评价",450 "sentiment_topic": "非评价",451 "keywords": [],452 }453 454 normalized_score = max(-1.0, min(1.0, score / 3))455 topic_label = topic if topic.endswith("体验") else f"{topic}体验"456 if normalized_score > 0.18:457 sentiment = "positive"458 reason = f"对{topic_label}认可"459 elif normalized_score < -0.18:460 sentiment = "negative"461 reason = "服务互动中出现负面情绪" if has_abusive_or_anger_signal else f"{topic_label}待改进"462 elif has_neutral_feedback and has_feedback_target:463 sentiment = "neutral"464 reason = f"{topic_label}反馈一般"465 else:466 sentiment = "neutral"467 reason = f"{topic_label}反馈不明显"468 469 return {470 "sentiment": sentiment,471 "sentiment_score": round(normalized_score, 2),472 "sentiment_reason": reason,473 "sentiment_topic": topic,474 "keywords": all_hits,475 }476 477 478def _sentiment_from_row(row: sqlite3.Row) -> dict:479 return analyze_visitor_sentiment(row["question"], row["answer"])480 481 482def _build_sentiment_suggestions(483 sentiment_counts: Counter,484 topic_counts: Counter,485 negative_topic_counts: Counter,486 total: int,487) -> list[dict]:488 if total <= 0:489 return [{490 "priority": "观察",491 "topic": "暂无数据",492 "issue": "游客对景区及服务的情感样本还不够",493 "suggestion": "上线后持续收集游客提问和反馈，建议先观察 1-3 天再做运营调整。",494 "count": 0,495 }]496 497 suggestions = []498 for topic, count in negative_topic_counts.most_common(5):499 ratio = count / total500 suggestions.append({501 "priority": "高" if ratio >= 0.2 or count >= 5 else "中",502 "topic": topic,503 "issue": f"{topic}相关负面反馈 {count} 次，占全部互动 {ratio:.0%}",504 "suggestion": TOPIC_SUGGESTIONS.get(topic, TOPIC_SUGGESTIONS["综合体验"]),505 "count": count,506 })507 508 positive_ratio = sentiment_counts["positive"] / total509 negative_ratio = sentiment_counts["negative"] / total510 if not suggestions and positive_ratio >= 0.5:511 top_topic = topic_counts.most_common(1)[0][0] if topic_counts else "综合体验"512 suggestions.append({513 "priority": "保持",514 "topic": top_topic,515 "issue": f"正向反馈占比 {positive_ratio:.0%}，整体体验较稳定",516 "suggestion": f"继续保持{top_topic}优势，把游客认可的讲解话术、路线推荐和服务动作沉淀为标准流程。",517 "count": sentiment_counts["positive"],518 })519 elif negative_ratio == 0:520 suggestions.append({521 "priority": "观察",522 "topic": "综合体验",523 "issue": "暂未出现明显负面情绪",524 "suggestion": "继续扩大样本量，重点关注高峰期、雨天和热门景点的反馈变化。",525 "count": 0,526 })527 528 return suggestions529 530 531def _add_user_query_sync(532 user_id: str,533 question: str,534 answer: str,535 mode: str = "rag",536) -> None:537 DATA_DIR.mkdir(parents=True, exist_ok=True)538 sentiment = analyze_visitor_sentiment(question, answer)539 with _connect() as conn:540 conn.execute(541 """542 INSERT INTO user_queries (543 user_id, question, answer, mode,544 sentiment, sentiment_score, sentiment_reason, sentiment_topic545 )546 VALUES (?, ?, ?, ?, ?, ?, ?, ?)547 """,548 (549 user_id,550 question,551 answer,552 mode,553 sentiment["sentiment"],554 sentiment["sentiment_score"],555 sentiment["sentiment_reason"],556 sentiment["sentiment_topic"],557 ),558 )559 conn.commit()560 561 562async def add_user_query(563 user_id: str,564 question: str,565 answer: str,566 mode: str = "rag",567) -> None:568 await asyncio.to_thread(_add_user_query_sync, user_id, question, answer, mode)569 570 571def _fetch_user_queries_sync(user_id: str, limit: int) -> list[dict]:572 DATA_DIR.mkdir(parents=True, exist_ok=True)573 with _connect() as conn:574 rows = conn.execute(575 """576 SELECT id, user_id, question, answer, mode,577 sentiment, sentiment_score, sentiment_reason, sentiment_topic,578 created_at579 FROM user_queries580 WHERE user_id = ?581 ORDER BY id DESC582 LIMIT ?583 """,584 (user_id, limit),585 ).fetchall()586 587 return [dict(row) for row in rows]588 589 590async def fetch_user_queries(user_id: str, limit: int = 20) -> list[dict]:591 return await asyncio.to_thread(_fetch_user_queries_sync, user_id, limit)592 593 594def _format_query_row(row: sqlite3.Row) -> dict:595 sentiment = _sentiment_from_row(row)596 return {597 "id": row["id"],598 "user_id": row["user_id"],599 "question": row["question"],600 "answer": row["answer"],601 "mode": row["mode"],602 **sentiment,603 "timestamp": row["created_at"],604 "created_at": row["created_at"],605 }606 607 608def _get_admin_stats_sync() -> dict:609 DATA_DIR.mkdir(parents=True, exist_ok=True)610 with _connect() as conn:611 today_users = conn.execute(612 """613 SELECT COUNT(DISTINCT user_id)614 FROM user_queries615 WHERE date(created_at) = date('now', 'localtime')616 """617 ).fetchone()[0]618 today_questions = conn.execute(619 """620 SELECT COUNT(*)621 FROM user_queries622 WHERE date(created_at) = date('now', 'localtime')623 """624 ).fetchone()[0]625 total_queries = conn.execute("SELECT COUNT(*) FROM user_queries").fetchone()[0]626 total_users = conn.execute(627 "SELECT COUNT(DISTINCT user_id) FROM user_queries"628 ).fetchone()[0]629 top_questions = [630 {"question": row["question"], "count": row["count"]}631 for row in conn.execute(632 """633 SELECT question, COUNT(*) AS count634 FROM user_queries635 GROUP BY question636 ORDER BY count DESC, MAX(created_at) DESC637 LIMIT 10638 """639 ).fetchall()640 ]641 mode_stats = [642 {"mode": row["mode"], "count": row["count"]}643 for row in conn.execute(644 """645 SELECT mode, COUNT(*) AS count646 FROM user_queries647 GROUP BY mode648 ORDER BY count DESC649 """650 ).fetchall()651 ]652 recent_visits = [653 _format_query_row(row)654 for row in conn.execute(655 """656 SELECT id, user_id, question, answer, mode,657 sentiment, sentiment_score, sentiment_reason, sentiment_topic,658 created_at659 FROM user_queries660 ORDER BY id DESC661 LIMIT 20662 """663 ).fetchall()664 ]665 sentiment_rows = conn.execute(666 """667 SELECT id, question, answer, sentiment, sentiment_score,668 sentiment_reason, sentiment_topic, created_at669 FROM user_queries670 ORDER BY id DESC671 LIMIT 500672 """673 ).fetchall()674 675 sentiment_counts = Counter()676 topic_counts = Counter()677 reason_counts = Counter()678 negative_topic_counts = Counter()679 not_feedback_count = 0680 recent_sentiment = []681 682 for row in sentiment_rows:683 payload = _sentiment_from_row(row)684 sentiment = payload["sentiment"]685 topic = payload["sentiment_topic"]686 reason = payload["sentiment_reason"]687 score = float(payload["sentiment_score"] or 0)688 if sentiment == "not_feedback":689 not_feedback_count += 1690 sentiment_counts["neutral"] += 1691 else:692 sentiment_counts[sentiment] += 1693 topic_counts[topic] += 1694 reason_counts[reason] += 1695 if sentiment == "negative":696 negative_topic_counts[topic] += 1697 698 if len(recent_sentiment) < 12:699 recent_sentiment.append({700 "id": row["id"],701 "question": row["question"],702 "sentiment": sentiment,703 "score": score,704 "topic": topic,705 "reason": reason,706 "timestamp": row["created_at"],707 })708 709 sentiment_total = sum(sentiment_counts.values())710 feedback_sentiment_total = sentiment_total - not_feedback_count711 712 return {713 "today_users": today_users,714 "today_questions": today_questions,715 "total_queries": total_queries,716 "total_users": total_users,717 "top_questions": top_questions,718 "mode_stats": mode_stats,719 "recent_visits": recent_visits,720 "sentiment_summary": [721 {"sentiment": key, "count": sentiment_counts[key]}722 for key in ("positive", "neutral", "negative")723 ],724 "not_feedback_count": not_feedback_count,725 "sentiment_topic_stats": [726 {"topic": topic, "count": count}727 for topic, count in topic_counts.most_common(8)728 ],729 "sentiment_reason_stats": [730 {"reason": reason, "count": count}731 for reason, count in reason_counts.most_common(8)732 ],733 "recent_sentiment": recent_sentiment,734 "sentiment_suggestions": _build_sentiment_suggestions(735 sentiment_counts,736 topic_counts,737 negative_topic_counts,738 feedback_sentiment_total,739 ),740 }741 742 743async def get_admin_stats() -> dict:744 return await asyncio.to_thread(_get_admin_stats_sync)745 746 747def _get_all_users_with_stats_sync() -> list[dict]:748 DATA_DIR.mkdir(parents=True, exist_ok=True)749 with _connect() as conn:750 rows = conn.execute(751 """752 SELECT753 q.user_id,754 u.username,755 u.nickname,756 COUNT(*) AS total_queries,757 MAX(q.created_at) AS last_active758 FROM user_queries AS q759 LEFT JOIN users AS u ON u.id = q.user_id760 GROUP BY q.user_id, u.username, u.nickname761 ORDER BY total_queries DESC, last_active DESC762 """763 ).fetchall()764 765 return [766 {767 "user_id": row["user_id"],768 "username": row["username"] or "",769 "nickname": row["nickname"] or "",770 "total_queries": row["total_queries"],771 "last_active": row["last_active"],772 }773 for row in rows774 ]775 776 777async def get_all_users_with_stats() -> list[dict]:778 return await asyncio.to_thread(_get_all_users_with_stats_sync)779 780 781def _get_user_detail_sync(user_id: str, limit: int = 50) -> list[dict]:782 DATA_DIR.mkdir(parents=True, exist_ok=True)783 with _connect() as conn:784 rows = conn.execute(785 """786 SELECT id, user_id, question, answer, mode,787 sentiment, sentiment_score, sentiment_reason, sentiment_topic,788 created_at789 FROM user_queries790 WHERE user_id = ?791 ORDER BY id DESC792 LIMIT ?793 """,794 (user_id, limit),795 ).fetchall()796 797 return [_format_query_row(row) for row in rows]798 799 800async def get_user_detail(user_id: str, limit: int = 50) -> list[dict]:801 return await asyncio.to_thread(_get_user_detail_sync, user_id, limit)802 803 804def _delete_query_sync(query_id: int) -> bool:805 DATA_DIR.mkdir(parents=True, exist_ok=True)806 with _connect() as conn:807 cursor = conn.execute("DELETE FROM user_queries WHERE id = ?", (query_id,))808 conn.commit()809 return cursor.rowcount > 0810 811 812async def delete_query(query_id: int) -> bool:813 return await asyncio.to_thread(_delete_query_sync, query_id)814 815 816def _search_queries_sync(keyword: str, limit: int = 50) -> list[dict]:817 DATA_DIR.mkdir(parents=True, exist_ok=True)818 pattern = f"%{keyword}%"819 with _connect() as conn:820 rows = conn.execute(821 """822 SELECT id, user_id, question, answer, mode,823 sentiment, sentiment_score, sentiment_reason, sentiment_topic,824 created_at825 FROM user_queries826 WHERE question LIKE ? OR answer LIKE ?827 ORDER BY id DESC828 LIMIT ?829 """,830 (pattern, pattern, limit),831 ).fetchall()832 833 return [_format_query_row(row) for row in rows]834 835 836async def search_queries(keyword: str, limit: int = 50) -> list[dict]:837 return await asyncio.to_thread(_search_queries_sync, keyword, limit)838 839 840def _log_knowledge_gap_sync(question: str, mode: str = "rag") -> None:841 DATA_DIR.mkdir(parents=True, exist_ok=True)842 with _connect() as conn:843 row = conn.execute(844 "SELECT id FROM knowledge_gaps WHERE question = ?",845 (question,),846 ).fetchone()847 if row:848 conn.execute(849 """850 UPDATE knowledge_gaps851 SET count = count + 1,852 mode = ?,853 status = CASE WHEN status = 'dismissed' THEN 'dismissed' ELSE 'pending' END,854 updated_at = CURRENT_TIMESTAMP855 WHERE id = ?856 """,857 (mode, row["id"]),858 )859 else:860 conn.execute(861 """862 INSERT INTO knowledge_gaps (question, mode, count, status)863 VALUES (?, ?, 1, 'pending')864 """,865 (question, mode),866 )867 conn.commit()868 869 870async def log_knowledge_gap(question: str, mode: str = "rag") -> None:871 await asyncio.to_thread(_log_knowledge_gap_sync, question, mode)872 873 874def _get_knowledge_gaps_sync(status: str = "pending", limit: int = 50) -> list[dict]:875 DATA_DIR.mkdir(parents=True, exist_ok=True)876 with _connect() as conn:877 rows = conn.execute(878 """879 SELECT id, question, mode, count, status, created_at, updated_at880 FROM knowledge_gaps881 WHERE status = ?882 ORDER BY count DESC, id DESC883 LIMIT ?884 """,885 (status, limit),886 ).fetchall()887 888 return [dict(row) for row in rows]889 890 891async def get_knowledge_gaps(status: str = "pending", limit: int = 50) -> list[dict]:892 return await asyncio.to_thread(_get_knowledge_gaps_sync, status, limit)893 894 895def _add_manual_knowledge_sync(896 question: str,897 answer: str,898 category: str = "general",899) -> int:900 DATA_DIR.mkdir(parents=True, exist_ok=True)901 with _connect() as conn:902 cursor = conn.execute(903 """904 INSERT INTO manual_knowledge (question, answer, category)905 VALUES (?, ?, ?)906 """,907 (question, answer, category),908 )909 conn.commit()910 return int(cursor.lastrowid)911 912 913async def add_manual_knowledge(914 question: str,915 answer: str,916 category: str = "general",917) -> int:918 return await asyncio.to_thread(919 _add_manual_knowledge_sync,920 question,921 answer,922 category,923 )924 925 926def _resolve_knowledge_gap_sync(gap_id: int, answer: str) -> bool:927 DATA_DIR.mkdir(parents=True, exist_ok=True)928 with _connect() as conn:929 gap = conn.execute(930 "SELECT question, mode FROM knowledge_gaps WHERE id = ?",931 (gap_id,),932 ).fetchone()933 if not gap:934 return False935 936 conn.execute(937 """938 INSERT INTO manual_knowledge (question, answer, category)939 VALUES (?, ?, ?)940 """,941 (gap["question"], answer, gap["mode"]),942 )943 conn.execute(944 """945 UPDATE knowledge_gaps946 SET status = 'resolved',947 updated_at = CURRENT_TIMESTAMP948 WHERE id = ?949 """,950 (gap_id,),951 )952 conn.commit()953 return True954 955 956async def resolve_knowledge_gap(gap_id: int, answer: str) -> bool:957 return await asyncio.to_thread(_resolve_knowledge_gap_sync, gap_id, answer)958 959 960def _dismiss_knowledge_gap_sync(gap_id: int) -> bool:961 DATA_DIR.mkdir(parents=True, exist_ok=True)962 with _connect() as conn:963 cursor = conn.execute(964 """965 UPDATE knowledge_gaps966 SET status = 'dismissed',967 updated_at = CURRENT_TIMESTAMP968 WHERE id = ?969 """,970 (gap_id,),971 )972 conn.commit()973 return cursor.rowcount > 0974 975 976async def dismiss_knowledge_gap(gap_id: int) -> bool:977 return await asyncio.to_thread(_dismiss_knowledge_gap_sync, gap_id)978 979 980def _get_manual_knowledge_sync(limit: int = 50) -> list[dict]:981 DATA_DIR.mkdir(parents=True, exist_ok=True)982 with _connect() as conn:983 rows = conn.execute(984 """985 SELECT id, question, answer, category, created_at986 FROM manual_knowledge987 ORDER BY id DESC988 LIMIT ?989 """,990 (limit,),991 ).fetchall()992 993 return [dict(row) for row in rows]994 995 996async def get_manual_knowledge(limit: int = 50) -> list[dict]:997 return await asyncio.to_thread(_get_manual_knowledge_sync, limit)998 999 1000def _delete_manual_knowledge_sync(entry_id: int) -> bool:1001 DATA_DIR.mkdir(parents=True, exist_ok=True)1002 with _connect() as conn:1003 cursor = conn.execute("DELETE FROM manual_knowledge WHERE id = ?", (entry_id,))1004 conn.commit()1005 return cursor.rowcount > 01006 1007 1008async def delete_manual_knowledge(entry_id: int) -> bool:1009 return await asyncio.to_thread(_delete_manual_knowledge_sync, entry_id)1010 1011 1012def _search_manual_knowledge_sync(question: str, top_k: int = 3) -> list[dict]:1013 DATA_DIR.mkdir(parents=True, exist_ok=True)1014 q_clean = question.strip()1015 if not q_clean:1016 return []1017 1018 ngrams = set()1019 for n in (2, 3):1020 for index in range(max(0, len(q_clean) - n + 1)):1021 ngrams.add(q_clean[index:index + n])1022 1023 with _connect() as conn:1024 rows = conn.execute(1025 """1026 SELECT id, question, answer, category, created_at1027 FROM manual_knowledge1028 ORDER BY id DESC1029 LIMIT 1001030 """1031 ).fetchall()1032 1033 matches = []1034 for row in rows:1035 score = 01036 row_question = row["question"]1037 for ngram in ngrams:1038 if ngram and ngram in row_question:1039 score += 11040 if q_clean in row_question or row_question in q_clean:1041 score += 51042 if score > 0:1043 payload = dict(row)1044 payload["score"] = score1045 matches.append(payload)1046 1047 matches.sort(key=lambda item: item["score"], reverse=True)1048 return matches[:top_k]1049 1050 1051async def search_manual_knowledge(question: str, top_k: int = 3) -> list[dict]:1052 return await asyncio.to_thread(_search_manual_knowledge_sync, question, top_k)1053 1054 1055def _hash_password(password: str, salt: str | None = None) -> tuple[str, str]:1056 password_salt = salt or os.urandom(16).hex()1057 password_hash = hashlib.pbkdf2_hmac(1058 "sha256",1059 password.encode("utf-8"),1060 bytes.fromhex(password_salt),1061 PASSWORD_ITERATIONS,1062 ).hex()1063 return password_hash, password_salt1064 1065 1066def _verify_password(password: str, password_hash: str, password_salt: str) -> bool:1067 candidate_hash, _ = _hash_password(password, password_salt)1068 return hmac.compare_digest(candidate_hash, password_hash)1069 1070 1071def _format_user_profile(row: sqlite3.Row) -> dict[str, str]:1072 return {1073 "userId": row["id"],1074 "username": row["username"],1075 "nickname": row["nickname"],1076 "avatar": row["avatar"],1077 "bio": row["bio"],1078 "interests": row["interests"],1079 "travelerType": row["traveler_type"],1080 "personality": row["personality"] or "friendly",1081 }1082 1083 1084def _create_user_sync(1085 username: str,1086 password: str,1087 nickname: str,1088 avatar: str = "",1089 bio: str = "",1090 interests: str = "",1091 traveler_type: str = "",1092 personality: str = "friendly",1093) -> dict[str, str]:1094 DATA_DIR.mkdir(parents=True, exist_ok=True)1095 user_id = uuid.uuid4().hex1096 password_hash, password_salt = _hash_password(password)1097 1098 with _connect() as conn:1099 try:1100 conn.execute(1101 """1102 INSERT INTO users (1103 id, username, password_hash, password_salt, nickname,1104 avatar, bio, interests, traveler_type, personality1105 )1106 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)1107 """,1108 (1109 user_id,1110 username,1111 password_hash,1112 password_salt,1113 nickname,1114 avatar,1115 bio,1116 interests,1117 traveler_type,1118 personality,1119 ),1120 )1121 conn.commit()1122 except sqlite3.IntegrityError as exc:1123 raise ValueError("username_taken") from exc1124 1125 row = conn.execute(1126 """1127 SELECT id, username, nickname, avatar, bio, interests, traveler_type, personality1128 FROM users1129 WHERE id = ?1130 """,1131 (user_id,),1132 ).fetchone()1133 1134 return _format_user_profile(row)1135 1136 1137async def create_user(1138 username: str,1139 password: str,1140 nickname: str,1141 avatar: str = "",1142 bio: str = "",1143 interests: str = "",1144 traveler_type: str = "",1145 personality: str = "friendly",1146) -> dict[str, str]:1147 return await asyncio.to_thread(1148 _create_user_sync,1149 username,1150 password,1151 nickname,1152 avatar,1153 bio,1154 interests,1155 traveler_type,1156 personality,1157 )1158 1159 1160def _authenticate_user_sync(username: str, password: str) -> dict[str, str] | None:1161 DATA_DIR.mkdir(parents=True, exist_ok=True)1162 with _connect() as conn:1163 row = conn.execute(1164 """1165 SELECT id, username, password_hash, password_salt, nickname,1166 avatar, bio, interests, traveler_type, personality1167 FROM users1168 WHERE username = ?1169 """,1170 (username,),1171 ).fetchone()1172 1173 if not row or not _verify_password(password, row["password_hash"], row["password_salt"]):1174 return None1175 1176 return _format_user_profile(row)1177 1178 1179async def authenticate_user(username: str, password: str) -> dict[str, str] | None:1180 return await asyncio.to_thread(_authenticate_user_sync, username, password)1181 1182 1183def _fetch_user_profile_sync(user_id: str) -> dict[str, str] | None:1184 DATA_DIR.mkdir(parents=True, exist_ok=True)1185 with _connect() as conn:1186 row = conn.execute(1187 """1188 SELECT id, username, nickname, avatar, bio, interests, traveler_type, personality1189 FROM users1190 WHERE id = ?1191 """,1192 (user_id,),1193 ).fetchone()1194 1195 return _format_user_profile(row) if row else None1196 1197 1198async def fetch_user_profile(user_id: str) -> dict[str, str] | None:1199 return await asyncio.to_thread(_fetch_user_profile_sync, user_id)1200