""" 投递记录路由:上报投递结果、查询历史、统计 """ import re from datetime import datetime, timedelta from pathlib import Path from typing import Optional, Tuple from fastapi import APIRouter, Depends, HTTPException, Query from sqlalchemy.orm import Session from sqlalchemy import func from loguru import logger from ..auth import get_current_user from ..database import get_db from ..models import User, JobApplication from ..online import require_online_user from ..schemas import ApplicationIn, ApplicationOut, Message, StatsOut router = APIRouter(prefix="/api/jobs", tags=["投递记录"]) # jd_text 入库上限(字符):超过则落盘为文件(库内只存路径), # 控制写入包体积,避免触发 MySQL max_allowed_packet(1153) JD_TEXT_LIMIT = 20000 # 归档目录:项目根目录下的 jd_archive/(本文件位于 server/routers/,上溯两级即项目根) PROJECT_ROOT = Path(__file__).resolve().parents[2] JD_DIR_NAME = "jd_archive" def _archive_jd(user_id: int, job_id: str, jd_text: Optional[str]) -> Tuple[Optional[str], Optional[str]]: """JD 存储分流:≤ 上限直接入库;超限写入 jd_archive/ 文件。 返回 (jd_text, jd_file):两者必有一空。文件名按 用户_职位 去重,重复上报覆盖""" if not jd_text or len(jd_text) <= JD_TEXT_LIMIT: return jd_text, None safe_job = re.sub(r"[^\w\-]", "_", job_id or "")[:48] or "unknown" rel = f"{JD_DIR_NAME}/{user_id}_{safe_job}.txt" try: target = PROJECT_ROOT / rel target.parent.mkdir(parents=True, exist_ok=True) target.write_text(jd_text, encoding="utf-8") logger.info(f"超长 JD 已归档到文件({len(jd_text)} 字符):{rel}") return None, rel except OSError as e: # 落盘失败也不能把巨包塞进数据库:截断到上限入库,保底不丢全部信息且不炸库 logger.error(f"JD 归档落盘失败,截断入库:{e}") return jd_text[:JD_TEXT_LIMIT], None def _clip(value: Optional[str], limit: int) -> Optional[str]: """按列长度截断文本,防止超长写入导致 1153 / Data too long""" return value[:limit] if value else value def _sanitize(req: ApplicationIn) -> ApplicationIn: """上报字段统一裁剪(与 models.py 列长度保持一致); jd_text 不在此截断——由 _archive_jd 按上限分流到数据库/文件""" req.job_title = _clip(req.job_title, 128) req.company = _clip(req.company, 128) req.salary = _clip(req.salary, 32) req.location = _clip(req.location, 64) req.apply_type = _clip(req.apply_type, 20) or "skipped" req.ai_reason = _clip(req.ai_reason, 255) req.skip_reason = _clip(req.skip_reason, 255) req.greeting = _clip(req.greeting, 500) return req @router.post("/report", response_model=ApplicationOut) def report_application( req: ApplicationIn, session_id: Optional[int] = Query(None, description="所属会话 ID"), current_user: User = Depends(require_online_user), db: Session = Depends(get_db), ): """ 客户端上报一条投递/跳过记录(前置校验:RPA 必须在线)。 同一用户 + 同一职位 ID 去重,已存在则更新。 """ req = _sanitize(req) # JD 分流:短文本入库,超长落盘为文件(库存路径) jd_text, jd_file = _archive_jd(current_user.id, req.job_id, req.jd_text) existing = db.query(JobApplication).filter( JobApplication.user_id == current_user.id, JobApplication.job_id == req.job_id, ).first() if existing: existing.apply_type = req.apply_type existing.ai_score = req.ai_score existing.ai_reason = req.ai_reason existing.skip_reason = req.skip_reason # 重新评估上报刷新时间戳:时间倒序视图展示最新一次决策, # 否则记录停留在首次上报时间,看起来像“漏报” existing.applied_at = datetime.utcnow() if req.jd_text: # 新 JD 覆盖旧值;文本与归档文件二者只留其一,避免读到陈旧数据 existing.jd_text = jd_text existing.jd_file = jd_file if req.greeting: existing.greeting = req.greeting if session_id: existing.session_id = session_id record = existing else: record = JobApplication( user_id=current_user.id, session_id=session_id, job_id=req.job_id, job_title=req.job_title, company=req.company, salary=req.salary, location=req.location, apply_type=req.apply_type, ai_score=req.ai_score, ai_reason=req.ai_reason, skip_reason=req.skip_reason, jd_text=jd_text, jd_file=jd_file, greeting=req.greeting, ) db.add(record) db.commit() db.refresh(record) return record @router.get("/check/{job_id}", response_model=dict) def check_applied( job_id: str, current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): """检查某职位是否已投递过(用于客户端去重)""" record = db.query(JobApplication).filter( JobApplication.user_id == current_user.id, JobApplication.job_id == job_id, JobApplication.apply_type.in_(["chat", "deliver"]), ).first() return {"job_id": job_id, "applied": record is not None} @router.get("/today-count", response_model=dict) def today_count( current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): """当前用户当天(北京时间)实际投递数量,用于客户端每日上限判断""" now_cn = datetime.utcnow() + timedelta(hours=8) start_utc = now_cn.replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(hours=8) count = db.query(JobApplication).filter( JobApplication.user_id == current_user.id, JobApplication.apply_type.in_(["chat", "deliver"]), JobApplication.applied_at >= start_utc, ).count() return {"count": count} @router.get("/today", response_model=dict) def today_applications( current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): """当天(北京时间)全部投递/跳过记录,供独立展示前端使用""" now_cn = datetime.utcnow() + timedelta(hours=8) start_utc = now_cn.replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(hours=8) rows = db.query(JobApplication).filter( JobApplication.user_id == current_user.id, JobApplication.applied_at >= start_utc, ).order_by(JobApplication.applied_at.desc()).all() applied = skipped = 0 items = [] for r in rows: if r.apply_type in ("chat", "deliver"): applied += 1 elif r.apply_type == "skipped": skipped += 1 items.append({ "job_title": r.job_title, "company": r.company, "salary": r.salary, "location": r.location, "apply_type": r.apply_type, "ai_score": r.ai_score, "ai_reason": r.ai_reason, "skip_reason": r.skip_reason, "applied_at": (r.applied_at + timedelta(hours=8)).strftime("%H:%M:%S"), }) return {"applied": applied, "skipped": skipped, "total": len(rows), "items": items} @router.get("/history", response_model=list[ApplicationOut]) def list_applications( apply_type: Optional[str] = Query(None, description="过滤投递类型"), limit: int = Query(50, le=500), current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): """获取投递历史(按时间倒序)""" query = db.query(JobApplication).filter(JobApplication.user_id == current_user.id) if apply_type: query = query.filter(JobApplication.apply_type == apply_type) return query.order_by(JobApplication.applied_at.desc()).limit(limit).all() @router.get("/page", response_model=dict) def page_applications( page: int = Query(1, ge=1, description="页码(1 起)"), page_size: int = Query(20, ge=1, le=100, description="每页条数"), apply_type: Optional[str] = Query(None, description="投递结果:chat/deliver/skipped"), start: Optional[str] = Query(None, description="起始日期(北京时间,YYYY-MM-DD,含)"), end: Optional[str] = Query(None, description="结束日期(北京时间,YYYY-MM-DD,含)"), current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): """投递记录分页筛选(北京时间范围 + 投递结果),时间倒序""" query = db.query(JobApplication).filter(JobApplication.user_id == current_user.id) # 北京时间日期区间 → UTC 区间(北京 00:00 == UTC 前一日 16:00) try: if start: d = datetime.strptime(start, "%Y-%m-%d") query = query.filter(JobApplication.applied_at >= d - timedelta(hours=8)) if end: d = datetime.strptime(end, "%Y-%m-%d") + timedelta(days=1) query = query.filter(JobApplication.applied_at < d - timedelta(hours=8)) except ValueError: raise HTTPException(status_code=422, detail="日期格式应为 YYYY-MM-DD") if apply_type: query = query.filter(JobApplication.apply_type == apply_type) # 筛选范围内统计(不受分页影响) total = query.count() applied = query.filter(JobApplication.apply_type.in_(["chat", "deliver"])).count() skipped = query.filter(JobApplication.apply_type == "skipped").count() rows = query.order_by(JobApplication.applied_at.desc()) \ .offset((page - 1) * page_size).limit(page_size).all() items = [{ "id": r.id, "job_title": r.job_title, "company": r.company, "salary": r.salary, "location": r.location, "apply_type": r.apply_type, "ai_score": r.ai_score, "ai_reason": r.ai_reason, "skip_reason": r.skip_reason, "greeting": r.greeting, # JD 全文截断,供客户端悬停摘要展示,控制分页响应体积; # 完整内容(含归档文件)通过 /{app_id}/jd 接口按需获取,has_jd 标记有无 JD 可看 "jd_text": (r.jd_text or "")[:500], "has_jd": bool(r.jd_text or r.jd_file), "applied_at": (r.applied_at + timedelta(hours=8)).strftime("%Y-%m-%d %H:%M:%S"), } for r in rows] return { "items": items, "total": total, "applied": applied, "skipped": skipped, "page": page, "page_size": page_size, "pages": (total + page_size - 1) // page_size if page_size else 1, } @router.get("/stats", response_model=StatsOut) def get_stats( current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): """获取投递统计""" base = db.query(JobApplication).filter(JobApplication.user_id == current_user.id) def count(**filters): q = base for k, v in filters.items(): q = q.filter(getattr(JobApplication, k) == v) return q.count() today_str = datetime.utcnow().strftime("%Y-%m-%d") total_applied = base.filter(JobApplication.apply_type.in_(["chat", "deliver"])).count() today_applied = base.filter( JobApplication.apply_type.in_(["chat", "deliver"]), func.date(JobApplication.applied_at) == today_str, ).count() return StatsOut( total_applied=total_applied, total_chat=count(apply_type="chat"), total_deliver=count(apply_type="deliver"), total_skipped=count(apply_type="skipped"), today_applied=today_applied, ) @router.get("/{app_id}/jd", response_model=dict) def get_application_jd( app_id: int, current_user: User = Depends(get_current_user), db: Session = Depends(get_db), ): """查看单条投递记录的完整 JD:先取 jd_text 字段,为空则读 jd_file 归档文件""" record = db.query(JobApplication).filter( JobApplication.id == app_id, JobApplication.user_id == current_user.id, ).first() if not record: raise HTTPException(status_code=404, detail="投递记录不存在") if record.jd_text: return {"id": app_id, "source": "db", "jd_text": record.jd_text} if record.jd_file: target = (PROJECT_ROOT / record.jd_file).resolve() # 防路径穿越:归档文件必须位于项目根目录内,防止库内路径被篡改后读到任意文件 if target.is_file() and str(target).startswith(str(PROJECT_ROOT)): return {"id": app_id, "source": "file", "jd_text": target.read_text(encoding="utf-8")} raise HTTPException(status_code=410, detail="JD 归档文件不存在或已丢失") raise HTTPException(status_code=404, detail="该记录未包含 JD")