#!/home/ubuntu/project/we-mp-rss-main/venv/bin/python3
"""
选题日报数据采集脚本
查询 we-mp-rss 数据库最近 N 小时文章 + 考试日历，输出格式化文本供 AI 分析。
用法: python3 daily_topic_data.py [hours=36] [db_path]
"""

import sqlite3
import json
import sys
from datetime import datetime, timezone, timedelta

HOURS = int(sys.argv[1]) if len(sys.argv) > 1 else 36
DB_PATH = sys.argv[2] if len(sys.argv) > 2 else "/home/ubuntu/project/we-mp-rss-data/db.db"
CALENDAR_PATH = "/home/ubuntu/project/config/exam_calendar.json"

beijing = timezone(timedelta(hours=8))
now = datetime.now(beijing)
cutoff_ts = int((now - timedelta(hours=HOURS)).timestamp())

# ── 1. 查询文章 ──
try:
    db = sqlite3.connect(f"file:{DB_PATH}?mode=ro", uri=True)
    db.row_factory = sqlite3.Row
    rows = db.execute("""
        SELECT a.title, a.url, a.publish_time, a.description, f.mp_name
        FROM articles a JOIN feeds f ON a.mp_id = f.id
        WHERE a.publish_time > ?
        ORDER BY a.publish_time DESC
    """, (cutoff_ts,)).fetchall()
    db.close()
except Exception as e:
    print(f"DB_ERROR: {e}")
    sys.exit(1)

# ── 2. 查询考试日历 ──
try:
    with open(CALENDAR_PATH) as f:
        calendar = json.load(f)
    if not isinstance(calendar, dict):
        calendar = {"events": []}
except Exception:
    calendar = {"events": []}

# 未来 14 天内的考试节点
future_events = []
for ev in calendar.get("events", []):
    if not isinstance(ev, dict) or "date" not in ev:
        continue
    parts = ev["date"].split("-")
    if len(parts) != 2:
        continue
    month, day = int(parts[0]), int(parts[1])
    event_date = datetime(now.year, month, day, tzinfo=beijing)
    # 如果今年已过，用明年
    if event_date < now:
        event_date = datetime(now.year + 1, month, day, tzinfo=beijing)
    days_left = (event_date - now).days
    if 0 <= days_left <= 14:
        future_events.append((days_left, ev["title"], ev.get("topics", [])))

future_events.sort()

# ── 3. 输出 ──
print(f"# 选题日报数据 ({now.strftime('%Y-%m-%d %H:%M')} 采集)")
print(f"## 查询窗口: 最近 {HOURS} 小时 (自 {datetime.fromtimestamp(cutoff_ts, beijing).strftime('%m-%d %H:%M')})")
print(f"## 文章总数: {len(rows)}")
print()

if rows:
    print("## 文章列表")
    print("| # | 时间 | 来源 | 标题 |")
    print("|---|------|------|------|")
    for i, r in enumerate(rows, 1):
        pt = datetime.fromtimestamp(r["publish_time"], beijing).strftime("%m-%d %H:%M")
        title = r["title"].replace("|", "｜")[:60]
        print(f"| {i} | {pt} | {r['mp_name']} | {title} |")
    print()
    print("## 文章详情")
    for i, r in enumerate(rows, 1):
        pt = datetime.fromtimestamp(r["publish_time"], beijing).strftime("%m-%d %H:%M")
        desc = (r["description"] or "").strip()[:100]
        print(f"### {i}. [{pt}] {r['mp_name']}：《{r['title']}》")
        if desc:
            print(f"   摘要: {desc}")
        print(f"   链接: {r['url']}")
        print()
else:
    print("⚠ 最近无新文章。")
    print()

if future_events:
    print("## 未来 14 天考试日历节点")
    for days_left, title, topics in future_events:
        print(f"- ⏰ **{days_left}天后**: {title}")
        for t in topics[:3]:
            print(f"  - {t}")
else:
    print("## 未来 14 天考试日历")
    print("无临近节点。")