Daily News

# Method 文件示例（带元数据）
# 用于增量抓取，记录上次抓取日期等信息

source_id: twitter-karpathy
source_name: Karpathy Twitter
source_url: https://x.com/karpathy
enabled: true
extends: browser-smart
detail_method: browser

# === 自动维护的元数据（由 skill 自动更新）===

# 上次抓取日期
last_fetched_date: "2026-01-27"

# 上次抓取数量
last_fetched_count: 5

# 该信源累计抓取总数
total_items_fetched: 127

# 首次抓取日期
first_fetch_date: "2026-01-15"

# === 增量抓取配置（可选）===

# 默认抓取策略
# - "incremental": 从 last_fetched_date 开始抓（默认）
# - "today": 只抓今天
# - "last-3-days": 抓最近3天
# - "all": 全量抓取
fetch_strategy: "incremental"

# 最小抓取间隔（分钟），防止过于频繁
min_fetch_interval: 30

# 用户画像
# 自然语言描述，没有固定格式
# 可以通过对话生成，也可以手动编辑

# 关于我（是谁、做什么工作）
about: |

# 当前关注（最近在意什么话题）
focus: |

# 不太关心（可选，降低这类内容的优先级）
low_priority: |

# 日报设置

# 输出语言
language: zh-CN

# 是否显示低优先级内容（1-2星）
show_low_priority: true

# 日报标题格式
title_format: "Daily News - {{date}}"

#!/usr/bin/env python3
"""
RSS/Atom Feed 通用获取方法

使用方式：
  python3 rss.py --url "https://example.com/feed.xml" [--limit 20]

输出：JSON 格式的文章元数据列表

依赖：pip install feedparser
"""

import argparse
import json
import sys
from datetime import datetime

try:
    import feedparser
except ImportError:
    print(json.dumps({"error": "Missing dependency. Install with: pip install feedparser"}))
    sys.exit(1)


def parse_date(entry) -> str | None:
    """从 entry 提取日期，返回 ISO 格式字符串"""
    for attr in ["published_parsed", "updated_parsed", "created_parsed"]:
        parsed = getattr(entry, attr, None)
        if parsed:
            try:
                return datetime(*parsed[:6]).isoformat()
            except (TypeError, ValueError):
                continue
    return None


def fetch(url: str, limit: int | None = None) -> list | dict:
    """
    获取 RSS/Atom feed 内容

    Args:
        url: Feed URL
        limit: 最大条目数（None 表示不限制）

    Returns:
        文章列表或错误信息
    """
    feed = feedparser.parse(url)

    # 检查解析错误
    if feed.bozo and not feed.entries:
        return {"error": f"Failed to parse feed: {feed.bozo_exception}"}

    items = []
    entries = feed.entries[:limit] if limit else feed.entries

    for entry in entries:
        item = {
            "title": getattr(entry, "title", "").strip(),
            "url": getattr(entry, "link", ""),
        }

        # 提取日期
        date = parse_date(entry)
        if date:
            item["published_at"] = date

        # 只添加有效条目
        if item.get("url") and item.get("title"):
            items.append(item)

    return items


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Fetch RSS/Atom feed")
    parser.add_argument("--url", required=True, help="Feed URL")
    parser.add_argument("--limit", type=int, help="Max items to fetch")
    args = parser.parse_args()

    result = fetch(args.url, args.limit)
    print(json.dumps(result, ensure_ascii=False, indent=2))

# Content Database Schema
# SQLite 数据库结构定义

tables:
  items:
    description: 内容元数据表
    columns:
      id:
        type: INTEGER PRIMARY KEY AUTOINCREMENT
        description: 自增主键
      source_id:
        type: TEXT NOT NULL
        description: 信源标识（对应 method 文件名）
      url:
        type: TEXT UNIQUE NOT NULL
        description: 内容 URL（唯一）
      title:
        type: TEXT NOT NULL
        description: 标题
      published_at:
        type: TEXT
        description: 发布时间（ISO 8601 格式，可空）
      discovered_at:
        type: TEXT NOT NULL
        description: 首次发现时间
      status:
        type: TEXT DEFAULT 'pending'
        description: |
          状态枚举：
          - pending: 待处理（未生成摘要）
          - summarized: 已生成摘要
          - reported: 已包含在日报中
    indexes:
      - idx_items_status ON items(status)
      - idx_items_source ON items(source_id)
      - idx_items_discovered ON items(discovered_at)

  summaries:
    description: 摘要表（与 items 一对一关联）
    columns:
      item_id:
        type: INTEGER PRIMARY KEY
        description: 关联 items.id
      summary:
        type: TEXT NOT NULL
        description: 100-150 字摘要
      relevance_score:
        type: INTEGER
        description: 相关度评分（1-5）
      relevance_reason:
        type: TEXT
        description: 相关原因说明
      keywords:
        type: TEXT
        description: 关键词（JSON 数组）
      summarized_at:
        type: TEXT NOT NULL
        description: 摘要生成时间
    foreign_keys:
      - item_id REFERENCES items(id)

  reports:
    description: 日报记录表
    columns:
      id:
        type: INTEGER PRIMARY KEY AUTOINCREMENT
      date:
        type: TEXT UNIQUE NOT NULL
        description: 日报日期（YYYY-MM-DD）
      item_count:
        type: INTEGER
        description: 包含条目数
      high_relevance_count:
        type: INTEGER
        description: 高相关条目数（4-5星）
      created_at:
        type: TEXT NOT NULL
        description: 生成时间
      file_path:
        type: TEXT
        description: 输出文件路径

# User Profile Schema
# 用户画像配置结构定义

fields:
  interests:
    type: list[string]
    required: true
    description: 兴趣领域列表，用于内容相关度评估
    example:
      - "AI/人工智能"
      - "LLM/大语言模型"
      - "Agent/智能体"
      - "创业/Startup"
      - "产品设计"

  high_priority_keywords:
    type: list[string]
    required: false
    description: 高优先级关键词，匹配时提升相关度至 4-5 星
    example:
      - "Claude"
      - "GPT"
      - "Agent"
      - "发布"
      - "突破"

  ignore_keywords:
    type: list[string]
    required: false
    description: 忽略的关键词，匹配时降低优先级或跳过
    example:
      - "广告"
      - "招聘"
      - "sponsored"

  output:
    type: object
    required: false
    description: 日报输出偏好
    properties:
      max_items:
        type: integer
        default: 50
        description: 最大条目数
      group_by:
        type: enum
        values: [source, priority]
        default: source
        description: 分组方式
      language:
        type: string
        default: zh-CN
        description: 输出语言
      include_summary:
        type: boolean
        default: true
        description: 是否包含摘要
      include_links:
        type: boolean
        default: true
        description: 是否包含原文链接

#!/usr/bin/env python3
"""
Daily News Website Builder
将 Markdown 日报转换为终端风格的 HTML 网页
"""

import os
import re
import json
import shutil
from pathlib import Path
from datetime import datetime

def parse_markdown(md_content):
    """解析 Markdown 内容"""
    sections = {
        'title': '',
        'summary': [],
        'five_star': [],
        'four_star': [],
        'worth_viewing': []
    }

    lines = md_content.split('\n')
    current_section = None
    current_item = None

    for line in lines:
        line = line.strip()
        if not line:
            continue

        # 提取日期
        if line.startswith('# Daily News'):
            match = re.search(r'(\d{4}-\d{2}-\d{2})', line)
            if match:
                sections['date'] = match.group(1)
            continue

        # 导读
        if line == '## 导读':
            current_section = 'summary'
            continue

        # 五星推荐
        if line == '## 五星推荐':
            current_section = 'five_star'
            if current_item:
                sections[current_section].append(current_item)
            current_item = None
            continue

        # 四星推荐
        if line == '## 四星推荐':
            current_section = 'four_star'
            if current_item and current_section != 'summary':
                sections[current_section].append(current_item)
            current_item = None
            continue

        # 值得一看
        if line == '## 值得一看':
            current_section = 'worth_viewing'
            if current_item and current_section != 'summary':
                sections[current_section].append(current_item)
            current_item = None
            continue

        # 解析内容
        if current_section == 'summary' and line.startswith('- **'):
            # 导读条目
            match = re.match(r'- \*\*(.+?)\*\*：(.+)', line)
            if match:
                sections['summary'].append({
                    'topic': match.group(1),
                    'content': match.group(2)
                })

        elif current_section in ['five_star', 'four_star', 'worth_viewing']:
            # 文章条目
            if line.startswith('**['):
                # 新条目开始
                if current_item:
                    sections[current_section].append(current_item)
                current_item = {'title': '', 'url': '', 'summary': '', 'meta': ''}

                # 提取标题和URL
                match = re.match(r'\*\*\[(.+?)\]\((.+?)\)\*\*', line)
                if match:
                    current_item['title'] = match.group(1)
                    current_item['url'] = match.group(2)

            elif line.startswith('`') and '·' in line:
                # 元数据行
                current_item['meta'] = line.strip('`')

            elif line and not line.startswith('---') and not line.startswith('*Generated'):
                # 摘要内容
                if current_item['summary']:
                    current_item['summary'] += ' ' + line
                else:
                    current_item['summary'] = line

    # 添加最后一个条目
    if current_item and current_section in ['five_star', 'four_star', 'worth_viewing']:
        sections[current_section].append(current_item)

    return sections

def generate_html(data, all_dates):
    """生成 HTML 页面"""

    # 星级图标
    star_icons = {
        5: '★★★★★',
        4: '★★★★☆',
        3: '★★★☆☆'
    }

    # 生成日期导航
    date_nav = ''
    for d in sorted(all_dates, reverse=True):
        active = 'active' if d == data.get('date') else ''
        date_nav += f'<a href="{d}.html" class="date-link {active}">{d}</a>'

    # 生成导读
    summary_html = ''
    for item in data.get('summary', []):
        summary_html += f'''
        <div class="summary-item">
            <span class="summary-topic">{item['topic']}</span>
            <span class="summary-content">{item['content']}</span>
        </div>
        '''

    # 生成文章列表
    def generate_articles(articles, stars):
        html = ''
        for article in articles:
            if not article.get('title'):
                continue
            html += f'''
            <article class="news-item">
                <div class="news-header">
                    <a href="{article.get('url', '#')}" class="news-title" target="_blank" rel="noopener">{article.get('title', 'Untitled')}</a>
                    <span class="news-meta">{article.get('meta', '')}</span>
                </div>
                <p class="news-summary">{article.get('summary', '')}</p>
            </article>
            '''
        return html

    five_star_html = generate_articles(data.get('five_star', []), 5)
    four_star_html = generate_articles(data.get('four_star', []), 4)
    worth_html = generate_articles(data.get('worth_viewing', []), 3)

    html = f'''<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Daily News - {data.get('date', '')}</title>
    <link rel="preconnect" href="https://fonts.googleapis.com">
    <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
    <link href="https://fonts.googleapis.com/css2?family=JetBrains+Mono:wght@400;600;700&family=Inter:wght@400;500;600&display=swap" rel="stylesheet">
    <style>
        :root {{
            --bg-primary: #fafafa;
            --bg-secondary: #f5f5f5;
            --bg-terminal: #1a1a1a;
            --text-primary: #1a1a1a;
            --text-secondary: #666666;
            --text-muted: #999999;
            --accent: #2563eb;
            --accent-light: #3b82f6;
            --border: #e5e5e5;
            --border-light: #f0f0f0;
            --star: #f59e0b;
            --code-bg: #f4f4f4;
        }}

        * {{
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }}

        body {{
            font-family: 'Inter', -apple-system, BlinkMacSystemFont, sans-serif;
            background: var(--bg-primary);
            color: var(--text-primary);
            line-height: 1.6;
        }}

        /* Terminal Header */
        .terminal-header {{
            background: var(--bg-terminal);
            color: #fff;
            padding: 1rem 2rem;
            font-family: 'JetBrains Mono', monospace;
        }}

        .terminal-line {{
            display: flex;
            align-items: center;
            gap: 0.5rem;
            margin-bottom: 0.25rem;
        }}

        .terminal-prompt {{
            color: #10b981;
        }}

        .terminal-cursor {{
            display: inline-block;
            width: 8px;
            height: 1.2em;
            background: #10b981;
            animation: blink 1s infinite;
            vertical-align: text-bottom;
        }}

        @keyframes blink {{
            0%, 50% {{ opacity: 1; }}
            51%, 100% {{ opacity: 0; }}
        }}

        /* Navigation */
        .nav-container {{
            background: var(--bg-secondary);
            border-bottom: 1px solid var(--border);
            padding: 1rem 2rem;
            overflow-x: auto;
        }}

        .date-nav {{
            display: flex;
            gap: 0.5rem;
            font-family: 'JetBrains Mono', monospace;
            font-size: 0.875rem;
        }}

        .date-link {{
            padding: 0.5rem 1rem;
            color: var(--text-secondary);
            text-decoration: none;
            border-radius: 4px;
            transition: all 0.2s;
            white-space: nowrap;
        }}

        .date-link:hover {{
            background: var(--bg-primary);
            color: var(--accent);
        }}

        .date-link.active {{
            background: var(--accent);
            color: white;
        }}

        /* Main Content */
        .container {{
            max-width: 900px;
            margin: 0 auto;
            padding: 3rem 2rem;
        }}

        .page-title {{
            font-size: 2.5rem;
            font-weight: 700;
            margin-bottom: 0.5rem;
            font-family: 'JetBrains Mono', monospace;
        }}

        .page-subtitle {{
            color: var(--text-muted);
            font-family: 'JetBrains Mono', monospace;
            margin-bottom: 3rem;
        }}

        /* Summary Section */
        .summary-section {{
            background: var(--bg-secondary);
            border-radius: 12px;
            padding: 1.5rem;
            margin-bottom: 3rem;
        }}

        .section-title {{
            font-size: 0.875rem;
            font-weight: 600;
            color: var(--text-muted);
            text-transform: uppercase;
            letter-spacing: 0.05em;
            margin-bottom: 1rem;
            font-family: 'JetBrains Mono', monospace;
        }}

        .summary-item {{
            padding: 0.75rem 0;
            border-bottom: 1px solid var(--border);
        }}

        .summary-item:last-child {{
            border-bottom: none;
        }}

        .summary-topic {{
            font-weight: 600;
            color: var(--accent);
            margin-right: 0.5rem;
        }}

        .summary-content {{
            color: var(--text-secondary);
        }}

        /* News Sections */
        .news-section {{
            margin-bottom: 3rem;
        }}

        .section-header {{
            display: flex;
            align-items: center;
            gap: 0.75rem;
            margin-bottom: 1.5rem;
            padding-bottom: 0.75rem;
            border-bottom: 2px solid var(--border);
        }}

        .section-name {{
            font-size: 1.25rem;
            font-weight: 600;
        }}

        .star-rating {{
            color: var(--star);
            font-size: 0.875rem;
        }}

        .news-item {{
            padding: 1.5rem;
            margin-bottom: 1rem;
            background: white;
            border: 1px solid var(--border-light);
            border-radius: 8px;
            transition: all 0.2s;
        }}

        .news-item:hover {{
            border-color: var(--accent);
            box-shadow: 0 4px 12px rgba(37, 99, 235, 0.08);
        }}

        .news-header {{
            display: flex;
            justify-content: space-between;
            align-items: flex-start;
            gap: 1rem;
            margin-bottom: 0.75rem;
        }}

        .news-title {{
            font-size: 1.125rem;
            font-weight: 600;
            color: var(--text-primary);
            text-decoration: none;
            line-height: 1.4;
        }}

        .news-title:hover {{
            color: var(--accent);
        }}

        .news-meta {{
            font-family: 'JetBrains Mono', monospace;
            font-size: 0.75rem;
            color: var(--text-muted);
            background: var(--code-bg);
            padding: 0.25rem 0.5rem;
            border-radius: 4px;
            white-space: nowrap;
        }}

        .news-summary {{
            color: var(--text-secondary);
            line-height: 1.7;
        }}

        /* Footer */
        .footer {{
            margin-top: 4rem;
            padding-top: 2rem;
            border-top: 1px solid var(--border);
            text-align: center;
            color: var(--text-muted);
            font-family: 'JetBrains Mono', monospace;
            font-size: 0.875rem;
        }}

        /* Responsive */
        @media (max-width: 768px) {{
            .container {{
                padding: 1.5rem;
            }}

            .page-title {{
                font-size: 1.75rem;
            }}

            .news-header {{
                flex-direction: column;
                gap: 0.5rem;
            }}

            .nav-container {{
                padding: 0.75rem 1rem;
            }}
        }}
    </style>
</head>
<body>
    <header class="terminal-header">
        <div class="terminal-line">
            <span class="terminal-prompt">$</span>
            <span>daily-news --date {data.get('date', '')}</span>
            <span class="terminal-cursor"></span>
        </div>
        <div class="terminal-line">
            <span class="terminal-prompt">&gt;</span>
            <span>Generating report... Done.</span>
        </div>
    </header>

    <nav class="nav-container">
        <div class="date-nav">
            {date_nav}
        </div>
    </nav>

    <main class="container">
        <h1 class="page-title">Daily News</h1>
        <p class="page-subtitle">// {data.get('date', '')}</p>

        <section class="summary-section">
            <div class="section-title">$ cat summary.md</div>
            {summary_html if summary_html else '<p class="text-muted">暂无导读</p>'}
        </section>

        {f'''
        <section class="news-section">
            <div class="section-header">
                <span class="section-name">五星推荐</span>
                <span class="star-rating">{star_icons[5]}</span>
            </div>
            {five_star_html}
        </section>
        ''' if five_star_html else ''}

        {f'''
        <section class="news-section">
            <div class="section-header">
                <span class="section-name">四星推荐</span>
                <span class="star-rating">{star_icons[4]}</span>
            </div>
            {four_star_html}
        </section>
        ''' if four_star_html else ''}

        {f'''
        <section class="news-section">
            <div class="section-header">
                <span class="section-name">值得一看</span>
                <span class="star-rating">{star_icons[3]}</span>
            </div>
            {worth_html}
        </section>
        ''' if worth_html else ''}

        <footer class="footer">
            <p>Generated by Daily News Skill | Cloudflare Pages</p>
        </footer>
    </main>
</body>
</html>'''

    return html

def build():
    """构建网站"""
    workspace = Path.home() / 'Documents/1-Projects/每日资讯日报'
    output_dir = workspace / 'output'
    dist_dir = workspace / 'website/dist'

    # 清理并重建 dist
    if dist_dir.exists():
        shutil.rmtree(dist_dir)
    dist_dir.mkdir(parents=True)

    # 获取所有日期
    all_dates = []
    for md_file in sorted(output_dir.glob('*.md')):
        date = md_file.stem
        all_dates.append(date)

    print(f"找到 {len(all_dates)} 个日报文件")

    # 生成每个页面的 HTML
    for md_file in output_dir.glob('*.md'):
        date = md_file.stem
        md_content = md_file.read_text(encoding='utf-8')
        data = parse_markdown(md_content)
        data['date'] = date

        html = generate_html(data, all_dates)

        output_file = dist_dir / f'{date}.html'
        output_file.write_text(html, encoding='utf-8')
        print(f"生成: {output_file.name}")

    # 复制最新的作为 index.html
    if all_dates:
        latest = max(all_dates)
        shutil.copy(dist_dir / f'{latest}.html', dist_dir / 'index.html')
        print(f"首页: {latest}.html -> index.html")

    print(f"\n构建完成！输出目录: {dist_dir}")

if __name__ == '__main__':
    build()

#!/usr/bin/env python3
"""
Daily News 数据库操作脚本

命令：
  init           - 初始化数据库
  add-items      - 添加条目（自动去重）
  list-pending   - 列出待处理条目
  update-summary - 更新摘要
  list-today     - 列出今日内容
  list-range     - 列出日期范围内容
  list-sources   - 列出所有信源
  stats          - 统计信息
  last-report    - 获取上次日报日期

使用示例：
  python3 db.py init --db ./data/news.db
  python3 db.py add-items --db ./data/news.db --source claude-blog --items '[...]'
  python3 db.py list-pending --db ./data/news.db --limit 10
  python3 db.py update-summary --db ./data/news.db --id 1 --data '{...}'
  python3 db.py list-today --db ./data/news.db
  python3 db.py list-range --db ./data/news.db --from 2026-01-10 --to 2026-01-15
  python3 db.py last-report --db ./data/news.db
"""

import argparse
import json
import sqlite3
from datetime import datetime, date
from pathlib import Path


def get_db(db_path: str) -> sqlite3.Connection:
    """获取数据库连接"""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    return conn


def init_db(db_path: str) -> dict:
    """初始化数据库"""
    # 确保目录存在
    Path(db_path).parent.mkdir(parents=True, exist_ok=True)

    conn = get_db(db_path)
    conn.executescript("""
        -- 内容元数据表
        CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            source_id TEXT NOT NULL,
            url TEXT UNIQUE NOT NULL,
            title TEXT NOT NULL,
            published_at TEXT,
            discovered_at TEXT NOT NULL,
            status TEXT DEFAULT 'pending'
        );

        -- 摘要表
        CREATE TABLE IF NOT EXISTS summaries (
            item_id INTEGER PRIMARY KEY,
            summary TEXT NOT NULL,
            relevance_score INTEGER,
            relevance_reason TEXT,
            keywords TEXT,
            summarized_at TEXT NOT NULL,
            FOREIGN KEY (item_id) REFERENCES items(id)
        );

        -- 日报记录表
        CREATE TABLE IF NOT EXISTS reports (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            date TEXT UNIQUE NOT NULL,
            item_count INTEGER,
            high_relevance_count INTEGER,
            created_at TEXT NOT NULL,
            file_path TEXT
        );

        -- 索引
        CREATE INDEX IF NOT EXISTS idx_items_status ON items(status);
        CREATE INDEX IF NOT EXISTS idx_items_source ON items(source_id);
        CREATE INDEX IF NOT EXISTS idx_items_discovered ON items(discovered_at);
    """)
    conn.commit()
    conn.close()

    return {"status": "initialized", "path": db_path}


def add_items(db_path: str, source_id: str, items: list) -> dict:
    """添加条目（自动去重）"""
    conn = get_db(db_path)
    now = datetime.now().isoformat()

    added = 0
    skipped = 0

    for item in items:
        try:
            conn.execute(
                """INSERT INTO items (source_id, url, title, published_at, discovered_at)
                   VALUES (?, ?, ?, ?, ?)""",
                (source_id, item["url"], item["title"], item.get("published_at"), now)
            )
            added += 1
        except sqlite3.IntegrityError:
            # URL 已存在
            skipped += 1

    conn.commit()
    conn.close()

    return {
        "status": "ok",
        "source_id": source_id,
        "added": added,
        "skipped": skipped,
        "total": len(items)
    }


def list_pending(db_path: str, limit: int = 10) -> list:
    """列出待处理条目"""
    conn = get_db(db_path)
    rows = conn.execute(
        """SELECT id, source_id, url, title, published_at, discovered_at
           FROM items
           WHERE status = 'pending'
           ORDER BY discovered_at DESC
           LIMIT ?""",
        (limit,)
    ).fetchall()
    conn.close()

    return [dict(r) for r in rows]


def list_pending_by_date(db_path: str, from_date: str, to_date: str = None, limit: int = 50) -> list:
    """
    按日期范围列出待处理条目（兜底过滤）

    Args:
        from_date: 开始日期 (YYYY-MM-DD)
        to_date: 结束日期 (YYYY-MM-DD)，默认今天
        limit: 最大条数
    """
    conn = get_db(db_path)

    if to_date is None:
        to_date = date.today().isoformat()

    rows = conn.execute(
        """SELECT id, source_id, url, title, published_at, discovered_at
           FROM items
           WHERE status = 'pending'
             AND (published_at IS NULL OR date(published_at) BETWEEN ? AND ?)
           ORDER BY published_at DESC NULLS LAST, discovered_at DESC
           LIMIT ?""",
        (from_date, to_date, limit)
    ).fetchall()
    conn.close()

    return [dict(r) for r in rows]


def update_summary(db_path: str, item_id: int, data: dict) -> dict:
    """更新摘要"""
    conn = get_db(db_path)
    now = datetime.now().isoformat()

    # 插入或更新摘要
    conn.execute(
        """INSERT OR REPLACE INTO summaries
           (item_id, summary, relevance_score, relevance_reason, keywords, summarized_at)
           VALUES (?, ?, ?, ?, ?, ?)""",
        (
            item_id,
            data["summary"],
            data.get("relevance_score"),
            data.get("relevance_reason"),
            json.dumps(data.get("keywords", [])),
            now
        )
    )

    # 更新条目状态
    conn.execute(
        "UPDATE items SET status = 'summarized' WHERE id = ?",
        (item_id,)
    )

    conn.commit()
    conn.close()

    return {"status": "ok", "item_id": item_id}


def list_today(db_path: str) -> list:
    """列出今日内容（含摘要）"""
    conn = get_db(db_path)
    today = date.today().isoformat()

    rows = conn.execute(
        """SELECT
             i.id, i.source_id, i.url, i.title, i.published_at, i.discovered_at, i.status,
             s.summary, s.relevance_score, s.relevance_reason, s.keywords
           FROM items i
           LEFT JOIN summaries s ON i.id = s.item_id
           WHERE date(i.discovered_at) = ?
           ORDER BY s.relevance_score DESC NULLS LAST, i.discovered_at DESC""",
        (today,)
    ).fetchall()
    conn.close()

    result = []
    for r in rows:
        item = dict(r)
        # 解析 keywords JSON
        if item.get("keywords"):
            try:
                item["keywords"] = json.loads(item["keywords"])
            except json.JSONDecodeError:
                item["keywords"] = []
        result.append(item)

    return result


def list_range(db_path: str, from_date: str, to_date: str) -> list:
    """列出日期范围内容（含摘要）"""
    conn = get_db(db_path)

    rows = conn.execute(
        """SELECT
             i.id, i.source_id, i.url, i.title, i.published_at, i.discovered_at, i.status,
             s.summary, s.relevance_score, s.relevance_reason, s.keywords
           FROM items i
           LEFT JOIN summaries s ON i.id = s.item_id
           WHERE date(i.discovered_at) BETWEEN ? AND ?
           ORDER BY s.relevance_score DESC NULLS LAST, i.discovered_at DESC""",
        (from_date, to_date)
    ).fetchall()
    conn.close()

    result = []
    for r in rows:
        item = dict(r)
        if item.get("keywords"):
            try:
                item["keywords"] = json.loads(item["keywords"])
            except json.JSONDecodeError:
                item["keywords"] = []
        result.append(item)

    return result


def last_report(db_path: str) -> dict:
    """获取上次日报信息"""
    conn = get_db(db_path)
    row = conn.execute(
        """SELECT date, item_count, high_relevance_count, created_at, file_path
           FROM reports
           ORDER BY date DESC
           LIMIT 1"""
    ).fetchone()
    conn.close()

    if row:
        return dict(row)
    return {"date": None, "message": "No reports found"}


def list_sources(db_path: str) -> list:
    """列出所有信源及统计"""
    conn = get_db(db_path)
    rows = conn.execute(
        """SELECT
             source_id,
             COUNT(*) as total_items,
             SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending_count,
             MAX(discovered_at) as last_discovered
           FROM items
           GROUP BY source_id
           ORDER BY last_discovered DESC"""
    ).fetchall()
    conn.close()

    return [dict(r) for r in rows]


def stats(db_path: str) -> dict:
    """统计信息"""
    conn = get_db(db_path)

    # 总条目数
    total = conn.execute("SELECT COUNT(*) FROM items").fetchone()[0]

    # 按状态统计
    status_counts = {}
    for row in conn.execute("SELECT status, COUNT(*) FROM items GROUP BY status"):
        status_counts[row[0]] = row[1]

    # 今日新增
    today = date.today().isoformat()
    today_count = conn.execute(
        "SELECT COUNT(*) FROM items WHERE date(discovered_at) = ?",
        (today,)
    ).fetchone()[0]

    # 信源数
    source_count = conn.execute(
        "SELECT COUNT(DISTINCT source_id) FROM items"
    ).fetchone()[0]

    # 高相关内容数（4-5星）
    high_relevance = conn.execute(
        "SELECT COUNT(*) FROM summaries WHERE relevance_score >= 4"
    ).fetchone()[0]

    conn.close()

    return {
        "total_items": total,
        "status_counts": status_counts,
        "today_count": today_count,
        "source_count": source_count,
        "high_relevance_count": high_relevance
    }


def record_report(db_path: str, report_date: str, item_count: int, high_count: int, file_path: str) -> dict:
    """记录日报"""
    conn = get_db(db_path)
    now = datetime.now().isoformat()

    try:
        conn.execute(
            """INSERT INTO reports (date, item_count, high_relevance_count, created_at, file_path)
               VALUES (?, ?, ?, ?, ?)""",
            (report_date, item_count, high_count, now, file_path)
        )
        conn.commit()
        status = "created"
    except sqlite3.IntegrityError:
        # 已存在，更新
        conn.execute(
            """UPDATE reports
               SET item_count = ?, high_relevance_count = ?, created_at = ?, file_path = ?
               WHERE date = ?""",
            (item_count, high_count, now, file_path, report_date)
        )
        conn.commit()
        status = "updated"

    conn.close()
    return {"status": status, "date": report_date}


# ==================== 增量抓取相关功能 ====================

def check_existing_urls(db_path: str, urls: list) -> set:
    """批量检查 URL 是否已存在

    Args:
        urls: 要检查的 URL 列表

    Returns:
        已存在的 URL 集合
    """
    if not urls:
        return set()

    conn = get_db(db_path)
    placeholders = ','.join(['?' for _ in urls])
    rows = conn.execute(
        f"SELECT url FROM items WHERE url IN ({placeholders})",
        tuple(urls)
    ).fetchall()
    conn.close()

    return set(row[0] for row in rows)


def add_items_incremental(db_path: str, source_id: str, items: list, date_range_start: str = None) -> dict:
    """增量添加条目（带预检查和同步日志）

    Args:
        date_range_start: 抓取日期范围的开始日期（用于记录日志）
    """
    conn = get_db(db_path)
    now = datetime.now().isoformat()

    # 1. 批量检查已存在的 URL
    urls = [item["url"] for item in items]
    existing_urls = check_existing_urls(db_path, urls)

    # 2. 过滤新条目
    new_items = []
    duplicates = []
    for item in items:
        if item["url"] in existing_urls:
            duplicates.append(item)
        else:
            new_items.append(item)

    # 3. 入库新条目
    added = 0
    for item in new_items:
        try:
            conn.execute(
                """INSERT INTO items (source_id, url, title, published_at, discovered_at)
                   VALUES (?, ?, ?, ?, ?)""",
                (source_id, item["url"], item["title"], item.get("published_at"), now)
            )
            added += 1
        except sqlite3.IntegrityError:
            # 并发情况下可能仍有重复
            duplicates.append(item)

    # 4. 记录同步日志
    if items:
        latest_date = max(
            (item.get("published_at", "") for item in items if item.get("published_at")),
            default=""
        )
        conn.execute(
            """INSERT INTO source_sync_log
               (source_id, sync_date, items_fetched, items_new, items_duplicate,
                latest_item_date, date_range_start, created_at)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (source_id, now[:10], len(items), added, len(duplicates),
             latest_date, date_range_start, now)
        )

        # 5. 更新 source_status
        total_fetched = conn.execute(
            "SELECT COUNT(*) FROM items WHERE source_id = ?",
            (source_id,)
        ).fetchone()[0]

        conn.execute(
            """INSERT INTO source_status (source_id, last_fetched_date, last_fetched_count,
               total_items_fetched, updated_at)
               VALUES (?, ?, ?, ?, ?)
               ON CONFLICT(source_id) DO UPDATE SET
               last_fetched_date = excluded.last_fetched_date,
               last_fetched_count = excluded.last_fetched_count,
               total_items_fetched = excluded.total_items_fetched,
               updated_at = excluded.updated_at""",
            (source_id, now[:10], added, total_fetched, now)
        )

    conn.commit()
    conn.close()

    return {
        "status": "ok",
        "source_id": source_id,
        "fetched": len(items),
        "added": added,
        "duplicates": len(duplicates),
        "duplicate_urls": [d["url"] for d in duplicates[:5]]  # 只显示前5个
    }


def get_source_status(db_path: str, source_id: str = None) -> dict:
    """获取信源同步状态"""
    conn = get_db(db_path)

    if source_id:
        row = conn.execute(
            """SELECT source_id, last_fetched_date, last_fetched_count,
               total_items_fetched, updated_at
               FROM source_status WHERE source_id = ?""",
            (source_id,)
        ).fetchone()
        conn.close()
        return dict(row) if row else {"source_id": source_id, "last_fetched_date": None}
    else:
        rows = conn.execute(
            """SELECT source_id, last_fetched_date, last_fetched_count,
               total_items_fetched, updated_at
               FROM source_status ORDER BY updated_at DESC"""
        ).fetchall()
        conn.close()
        return [dict(r) for r in rows]


def list_sync_log(db_path: str, source_id: str = None, limit: int = 10) -> list:
    """获取同步日志"""
    conn = get_db(db_path)

    if source_id:
        rows = conn.execute(
            """SELECT * FROM source_sync_log
               WHERE source_id = ?
               ORDER BY sync_date DESC LIMIT ?""",
            (source_id, limit)
        ).fetchall()
    else:
        rows = conn.execute(
            """SELECT * FROM source_sync_log
               ORDER BY sync_date DESC LIMIT ?""",
            (limit,)
        ).fetchall()

    conn.close()
    return [dict(r) for r in rows]


def main():
    parser = argparse.ArgumentParser(description="Daily News Database Operations")
    subparsers = parser.add_subparsers(dest="command", help="Command to run")

    # init
    init_parser = subparsers.add_parser("init", help="Initialize database")
    init_parser.add_argument("--db", required=True, help="Database path")

    # add-items
    add_parser = subparsers.add_parser("add-items", help="Add items")
    add_parser.add_argument("--db", required=True, help="Database path")
    add_parser.add_argument("--source", required=True, help="Source ID")
    add_parser.add_argument("--items", required=True, help="Items JSON")

    # list-pending
    pending_parser = subparsers.add_parser("list-pending", help="List pending items")
    pending_parser.add_argument("--db", required=True, help="Database path")
    pending_parser.add_argument("--limit", type=int, default=10, help="Max items")

    # update-summary
    summary_parser = subparsers.add_parser("update-summary", help="Update summary")
    summary_parser.add_argument("--db", required=True, help="Database path")
    summary_parser.add_argument("--id", required=True, type=int, help="Item ID")
    summary_parser.add_argument("--data", required=True, help="Summary JSON")

    # list-today
    today_parser = subparsers.add_parser("list-today", help="List today's items")
    today_parser.add_argument("--db", required=True, help="Database path")

    # list-range
    range_parser = subparsers.add_parser("list-range", help="List items in date range")
    range_parser.add_argument("--db", required=True, help="Database path")
    range_parser.add_argument("--from", dest="from_date", required=True, help="Start date (YYYY-MM-DD)")
    range_parser.add_argument("--to", dest="to_date", required=True, help="End date (YYYY-MM-DD)")

    # last-report
    last_report_parser = subparsers.add_parser("last-report", help="Get last report info")
    last_report_parser.add_argument("--db", required=True, help="Database path")

    # list-sources
    sources_parser = subparsers.add_parser("list-sources", help="List sources")
    sources_parser.add_argument("--db", required=True, help="Database path")

    # stats
    stats_parser = subparsers.add_parser("stats", help="Show statistics")
    stats_parser.add_argument("--db", required=True, help="Database path")

    # record-report
    report_parser = subparsers.add_parser("record-report", help="Record report")
    report_parser.add_argument("--db", required=True, help="Database path")
    report_parser.add_argument("--date", required=True, help="Report date (YYYY-MM-DD)")
    report_parser.add_argument("--items", required=True, type=int, help="Item count")
    report_parser.add_argument("--high", required=True, type=int, help="High relevance count")
    report_parser.add_argument("--file", required=True, help="Output file path")

    # add-items-incremental (增量抓取)
    incremental_parser = subparsers.add_parser("add-items-incremental", help="Add items with incremental check")
    incremental_parser.add_argument("--db", required=True, help="Database path")
    incremental_parser.add_argument("--source", required=True, help="Source ID")
    incremental_parser.add_argument("--items", required=True, help="Items JSON")
    incremental_parser.add_argument("--since", help="Date range start (YYYY-MM-DD)")

    # check-existing (批量检查URL)
    check_parser = subparsers.add_parser("check-existing", help="Check if URLs exist")
    check_parser.add_argument("--db", required=True, help="Database path")
    check_parser.add_argument("--urls", required=True, help="URLs JSON array")

    # source-status (获取信源状态)
    status_parser = subparsers.add_parser("source-status", help="Get source sync status")
    status_parser.add_argument("--db", required=True, help="Database path")
    status_parser.add_argument("--source", help="Source ID (optional)")

    # sync-log (获取同步日志)
    log_parser = subparsers.add_parser("sync-log", help="Get sync log")
    log_parser.add_argument("--db", required=True, help="Database path")
    log_parser.add_argument("--source", help="Source ID filter")
    log_parser.add_argument("--limit", type=int, default=10, help="Max entries")

    args = parser.parse_args()

    if args.command == "init":
        result = init_db(args.db)
    elif args.command == "add-items":
        items = json.loads(args.items)
        result = add_items(args.db, args.source, items)
    elif args.command == "list-pending":
        result = list_pending(args.db, args.limit)
    elif args.command == "update-summary":
        data = json.loads(args.data)
        result = update_summary(args.db, args.id, data)
    elif args.command == "list-today":
        result = list_today(args.db)
    elif args.command == "list-range":
        result = list_range(args.db, args.from_date, args.to_date)
    elif args.command == "last-report":
        result = last_report(args.db)
    elif args.command == "list-sources":
        result = list_sources(args.db)
    elif args.command == "stats":
        result = stats(args.db)
    elif args.command == "record-report":
        result = record_report(args.db, args.date, args.items, args.high, args.file)
    elif args.command == "add-items-incremental":
        items = json.loads(args.items)
        result = add_items_incremental(args.db, args.source, items, args.since)
    elif args.command == "check-existing":
        urls = json.loads(args.urls)
        existing = check_existing_urls(args.db, urls)
        result = {"existing_urls": list(existing), "count": len(existing)}
    elif args.command == "source-status":
        result = get_source_status(args.db, args.source)
    elif args.command == "sync-log":
        result = list_sync_log(args.db, args.source, args.limit)
    else:
        parser.print_help()
        return

    print(json.dumps(result, ensure_ascii=False, indent=2))


if __name__ == "__main__":
    main()

-- Daily News Database Migration V2
-- 添加增量抓取相关表

-- 1. 信源同步日志表
CREATE TABLE IF NOT EXISTS source_sync_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_id TEXT NOT NULL,
    sync_date TEXT NOT NULL,
    items_fetched INTEGER DEFAULT 0,
    items_new INTEGER DEFAULT 0,
    items_duplicate INTEGER DEFAULT 0,
    items_skipped INTEGER DEFAULT 0,  -- 因日期限制跳过的条目
    latest_item_date TEXT,
    date_range_start TEXT,
    date_range_end TEXT,
    created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_sync_log_source ON source_sync_log(source_id);
CREATE INDEX IF NOT EXISTS idx_sync_log_date ON source_sync_log(sync_date);

-- 2. 信源状态表（快速查询用）
CREATE TABLE IF NOT EXISTS source_status (
    source_id TEXT PRIMARY KEY,
    last_fetched_date TEXT,
    last_fetched_count INTEGER DEFAULT 0,
    total_items_fetched INTEGER DEFAULT 0,
    total_items_unique INTEGER DEFAULT 0,
    first_fetch_date TEXT,
    updated_at TEXT NOT NULL
);

-- 3. 添加 URL 哈希索引（加速去重查询）
-- 注意：SQLite 不支持直接函数索引，使用触发器维护
CREATE INDEX IF NOT EXISTS idx_items_url_prefix ON items(url);

-- 迁移完成标记
CREATE TABLE IF NOT EXISTS schema_version (
    version INTEGER PRIMARY KEY,
    applied_at TEXT NOT NULL,
    description TEXT
);

INSERT INTO schema_version (version, applied_at, description)
VALUES (2, datetime('now'), 'Add incremental fetch support: source_sync_log, source_status tables');