#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""导入 wszf_tzgg.jsonl (温宿通知公告) 到 search.db"""
import json, os, re, sqlite3
from datetime import datetime as _dt

dbp = os.environ.get('SEARCH_DB', '/root/search.db')
db = sqlite3.connect(dbp)
db.execute("PRAGMA journal_mode=WAL")
db.execute("PRAGMA busy_timeout=300000")
db.execute("PRAGMA synchronous=NORMAL")
db.execute("""CREATE VIRTUAL TABLE IF NOT EXISTS gov_search USING fts5(
    title, site_name, summary, tokenize=trigram
)""")
db.execute("""CREATE TRIGGER IF NOT EXISTS trg_gov_raw_fts_ins AFTER INSERT ON gov_raw BEGIN
  INSERT INTO gov_search(rowid, title, site_name, summary)
  VALUES (new.id, new.title, new.site_name, new.summary);
END""")
db.execute("""CREATE TRIGGER IF NOT EXISTS trg_gov_raw_fts_del AFTER DELETE ON gov_raw BEGIN
  DELETE FROM gov_search WHERE rowid = old.id;
END""")
db.execute("""CREATE TRIGGER IF NOT EXISTS trg_gov_raw_fts_upd AFTER UPDATE ON gov_raw BEGIN
  DELETE FROM gov_search WHERE rowid = old.id;
  INSERT INTO gov_search(rowid, title, site_name, summary)
  VALUES (new.id, new.title, new.site_name, new.summary);
END""")
db.commit()

SITE_NAME = '温宿县-通知公告'
SCRIPT = 'crawl_wszf_tzgg.py'
added = 0
errors = 0
with open('/root/gov_crawler/wszf_tzgg.jsonl', encoding='utf-8') as f:
    for line in f:
        line = line.strip()
        if not line:
            continue
        try:
            item = json.loads(line)
            title = (item.get("title") or "")[:500]
            page_url = (item.get("page_url") or item.get("url") or "")[:1000]
            content = (item.get("content") or item.get("body") or "").strip()
            publish_date = (item.get("publish_date") or item.get("date") or "")[:20]
            att = item.get("attachments") or []
            attachments_str = json.dumps(att, ensure_ascii=False) if att else ""
            if not page_url or not title:
                errors += 1
                continue
            plain = re.sub(r'<[^>]+>', ' ', content)
            plain = re.sub(r'\s+', ' ', plain).strip()
            summary = plain[:200]
            dr = 0
            dm = re.search(r'(\d{4})-(\d{2})-(\d{2})', publish_date)
            if dm:
                try:
                    dr = int(_dt(int(dm.group(1)), int(dm.group(2)), int(dm.group(3))).timestamp())
                except Exception:
                    dr = 0
            has_table = 1 if '<table' in content else 0
            db.execute("DELETE FROM gov_raw WHERE page_url = ?", (page_url,))
            db.execute(
                "INSERT INTO gov_raw (title, page_url, content, publish_date, site_name, source_url, status, attachments, script_name, summary, date_rank, has_table) VALUES (?, ?, ?, ?, ?, ?, 'synced', ?, ?, ?, ?, ?)",
                (title, page_url, content, publish_date, SITE_NAME, page_url, attachments_str, SCRIPT, summary, dr, has_table)
            )
            added += 1
        except Exception as e:
            errors += 1
db.commit()
db.close()
print(f"[DB] wszf_tzgg 导入完成: added={added} errors={errors}")