File size: 5,879 Bytes
f8cc540
f3391c9
f8cc540
 
 
 
 
 
 
f3391c9
f8cc540
 
 
 
 
 
 
 
 
f3391c9
f8cc540
 
f3391c9
 
f8cc540
 
f3391c9
 
 
 
 
 
 
 
 
 
f8cc540
 
 
 
 
f3391c9
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f8cc540
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f3391c9
f8cc540
f3391c9
 
 
f8cc540
 
f3391c9
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
f8cc540
 
 
 
 
 
f3391c9
 
 
 
 
f8cc540
 
 
 
 
 
 
 
 
 
f3391c9
f8cc540
f3391c9
 
 
 
f8cc540
 
 
f3391c9
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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
90
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
144
145
146
147
148
"""
Automotive Intelligence Dashboard — Database Module (raw sqlite3)
"""
from __future__ import annotations

import hashlib
import json
import logging
import sqlite3
import threading
from datetime import datetime, timedelta
from typing import Any, Dict, List, Optional

from config import config

logger = logging.getLogger(__name__)


class NewsDatabase:
    """Thread-safe SQLite store for automotive news."""

    def __init__(self, db_path: Optional[str] = None) -> None:
        self._path = str(db_path or config.database_path)
        self._local = threading.local()
        self._ensure_schema()

    def _conn(self) -> sqlite3.Connection:
        """Get a thread-local connection."""
        if not hasattr(self._local, "conn") or self._local.conn is None:
            conn = sqlite3.connect(self._path, check_same_thread=False)
            conn.row_factory = sqlite3.Row
            conn.execute("PRAGMA journal_mode=WAL")
            conn.execute("PRAGMA busy_timeout=5000")
            self._local.conn = conn
        return self._local.conn

    # ------------------------------------------------------------------
    # Schema
    # ------------------------------------------------------------------

    def _ensure_schema(self) -> None:
        c = self._conn()
        c.execute("""CREATE TABLE IF NOT EXISTS news (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title_en TEXT, title_zh TEXT,
            summary_en TEXT, summary_zh TEXT,
            content TEXT, url TEXT, source TEXT,
            language TEXT, category TEXT,
            published_at TEXT, fetched_at TEXT,
            companies TEXT, roles TEXT, content_hash TEXT UNIQUE
        )""")
        c.execute("CREATE INDEX IF NOT EXISTS idx_news_source ON news(source)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_news_pub ON news(published_at)")
        c.execute("CREATE INDEX IF NOT EXISTS idx_news_hash ON news(content_hash)")

        c.execute("""CREATE TABLE IF NOT EXISTS crawl_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            source TEXT, articles_fetched INTEGER, articles_new INTEGER,
            status TEXT, error TEXT,
            started_at TEXT, completed_at TEXT
        )""")
        c.commit()

    # ------------------------------------------------------------------
    # Helpers
    # ------------------------------------------------------------------

    @staticmethod
    def _hash(content: str) -> str:
        return hashlib.sha256(content.encode("utf-8", errors="replace")).hexdigest()

    # ------------------------------------------------------------------
    # Write
    # ------------------------------------------------------------------

    def upsert_article(self, article: Dict[str, Any]) -> bool:
        title = (article.get("title_en") or "").strip()
        if not title:
            return False
        content = (article.get("content") or article.get("summary_en") or "").strip()
        h = self._hash(content or title)

        c = self._conn()
        cur = c.execute("SELECT id FROM news WHERE content_hash = ?", (h,))
        if cur.fetchone():
            return False

        c.execute(
            """INSERT INTO news (title_en, title_zh, summary_en, summary_zh, content,
               url, source, language, category, published_at, fetched_at,
               companies, roles, content_hash)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (title,
             article.get("title_zh") or "",
             (article.get("summary_en") or "")[:500],
             (article.get("summary_zh") or "")[:300],
             content[:2000],
             article.get("url", ""),
             article.get("source", "unknown"),
             article.get("language", "en"),
             article.get("category", "general"),
             article.get("published_at", datetime.now().isoformat()),
             datetime.now().isoformat(),
             json.dumps(article.get("companies", []), ensure_ascii=False),
             json.dumps(article.get("roles", []), ensure_ascii=False),
             h))
        c.commit()
        return True

    # ------------------------------------------------------------------
    # Read
    # ------------------------------------------------------------------

    def get_news(self, start_date=None, limit=100, company=None, role=None, category=None):
        c = self._conn()
        rows = [dict(r) for r in c.execute(
            "SELECT * FROM news ORDER BY published_at DESC LIMIT ?", (limit,))]

        if start_date:
            rows = [r for r in rows if (r.get("published_at") or "") >= start_date]
        if role and role != "全部":
            rows = [r for r in rows if role in (r.get("roles") or "[]")]
        if company and company != "全部":
            rows = [r for r in rows if company in (r.get("companies") or "[]")]
        if category and category != "全部":
            rows = [r for r in rows if (r.get("category") or "").lower() == category.lower()]
        return rows

    def get_recent_count(self, hours=24):
        cutoff = (datetime.now() - timedelta(hours=hours)).isoformat()
        c = self._conn()
        results = {}
        for r in c.execute("SELECT source, COUNT(*) as cnt FROM news WHERE fetched_at >= ? GROUP BY source", (cutoff,)):
            results[r["source"]] = r["cnt"]
        return results

    def total_count(self) -> int:
        c = self._conn()
        return c.execute("SELECT COUNT(*) FROM news").fetchone()[0]

    def log_crawl(self, source, fetched, new, status="ok", error=""):
        c = self._conn()
        now = datetime.now().isoformat()
        c.execute(
            "INSERT INTO crawl_log (source, articles_fetched, articles_new, status, error, started_at, completed_at) VALUES (?,?,?,?,?,?,?)",
            (source, fetched, new, status, error, now, now))
        c.commit()