#!/usr/bin/env python3
"""
crawler_lib.py — 爬虫共享库
==========================
所有 crawl_xxx.py 从此导入公共工具。

用法：
    from crawler_lib import (
        fetch_page, parse_date, clean_html,
        import_to_eia, save_jsonl, get_eia_conn
    )

约定：
    - fetch_page 自动处理超时和编码
    - import_to_eia 按 url 去重（INSERT OR IGNORE）
    - 正文保留 HTML 格式（<p>, <br>, <table>, <a> 等），不清除结构化标签
    - 每个爬虫输出两条路：直接入库 eia.db + JSONL 文件备份
"""

import json, os, re, sqlite3, hashlib, time, subprocess, tempfile
from datetime import datetime, timezone, timedelta
from urllib.parse import urljoin

import requests
import warnings
warnings.filterwarnings("ignore", category=requests.packages.urllib3.exceptions.InsecureRequestWarning)

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
EIA_DB = os.path.join(BASE_DIR, "eia.db")
OUTPUT_DIR = os.path.join(BASE_DIR, "output")

THREE_YEARS_AGO = (datetime.now(timezone.utc) - timedelta(days=365*3)).strftime("%Y-%m-%d")

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36",
}

# 通用日期正则
DATE_RE = re.compile(
    r'(\d{4})[年\-/\.](\d{1,2})[月\-/\.](\d{1,2})'
    r'|(?<!\d)(\d{4})(\d{2})(\d{2})(?!\d)'
)
META_DATE = re.compile(
    r'<meta[^>]*name=["\']?(?:publish_date|pubdate|date|ArticleTitleDate)["\']?[^>]*content=["\']([^"\']+)["\']',
    re.I
)
META_DATE2 = re.compile(
    r'<meta[^>]*content=["\']([^"\']+)["\'][^>]*name=["\']?(?:publish_date|pubdate|date)["\']?',
    re.I
)
TIME_TAG = re.compile(r'<time[^>]*datetime=["\']([^"\']+)["\']')

# em-dash 长横线日期: 2026—07—28 (U+2014) / 2026–07–28 (U+2013)
EM_DASH_DATE = re.compile(r'(\d{4})[—–](\d{1,2})[—–](\d{1,2})')


def md_links_to_html(text, fallback_title=""):
    """markdown 链接 → HTML 内嵌段 (附件/图片禁 markdown 格式, 用户偏好)

    - [text](url)   → <p><a href="url">text</a></p>
    - ![alt](url)   → <p><a href="url">查看图片</a></p>  (图片禁 markdown ![](url))
    - - [text](url) → <p><a href="url">text</a></p>       (附件列表项)
    - [](url) 空标题 → 用 fallback_title 填充
    """
    if not text or ("](http" not in text and "](https" not in text):
        return text
    # 1. 图片 markdown 优先 (alt 可含特殊字符)
    def repl_img(m):
        whole = m.group(0)          # ![xxx](http...)
        url = whole[whole.index('(') + 1:whole.rindex(')')]
        return f'<p><a href="{url}">查看图片</a></p>'
    text = re.sub(r'!\[[^\]]*?\]\(http[^)]*\)', repl_img, text)
    # 2. 空标题 [](url) → fallback_title
    if fallback_title:
        text = re.sub(r'\[\]\((http[^)]+)\)', lambda m: f'<p><a href="{m.group(1)}">{fallback_title}</a></p>', text)
    # 3. 列表项 "- [text](url)"
    text = re.sub(r'-\s*\[([^\]]+)\]\((http[^)]+)\)', r'<p><a href="\2">\1</a></p>', text)
    # 4. 普通 markdown [text](url) —— 标题可含嵌套方括号
    text = re.sub(r'\[((?:[^\[\]]|\[[^\]]*\])*)\]\((http[^)]+)\)', r'<p><a href="\2">\1</a></p>', text)
    # 5. 规范化连续空行
    text = re.sub(r'\n{3,}', '\n\n', text)
    return text.strip()


def normalize_pub_date(raw_date):
    """把各种脏格式的发布日期统一成 YYYY-MM-DD（无匹配/垃圾 → ''）。

    覆盖格式（实测脏数据清单）：
      - 标准:        2026-07-31, 2026-7-2, 2026-07-31 09:00:00
      - 中文格式:    2026年07月13日, 2026年07月13
      - 长横线:      2026—07—28 (em-dash), 2026–07–28 (en-dash)
      - 斜杠/点:     2026/07/13, 2026.07.13
      - 紧凑:        20260713
      - 年月:        2026-03 → 2026-03-01
    """
    if not raw_date:
        return ''
    s = str(raw_date).strip()
    m = DATE_RE.search(s)
    if m:
        if m.group(1):
            y, mo, d = int(m.group(1)), int(m.group(2)), int(m.group(3))
        else:
            y, mo, d = int(m.group(4)), int(m.group(5)), int(m.group(6))
        try:
            datetime(y, mo, d)
        except ValueError:
            return ''
        return f"{y:04d}-{mo:02d}-{d:02d}"
    m = EM_DASH_DATE.search(s)
    if m:
        y, mo, d = int(m.group(1)), int(m.group(2)), int(m.group(3))
        try:
            datetime(y, mo, d)
        except ValueError:
            return ''
        return f"{y:04d}-{mo:02d}-{d:02d}"
    m = re.search(r'(\d{4})-(\d{2})$', s)      # YYYY-MM 年月
    if m:
        return f"{m.group(1)}-{m.group(2)}-01"
    m = re.search(r'^(\d{4})$', s)              # 纯年
    if m:
        return f"{m.group(1)}-01-01"
    return ''

OUTPUT_DIR = os.path.join(BASE_DIR, "output")


# ═══════════════════════════════════════════
#  网络请求
# ═══════════════════════════════════════════

def fetch_page(url, timeout=20, encoding='utf-8'):
    """GET 请求页面，返回文本"""
    try:
        r = requests.get(url, headers=HEADERS, timeout=timeout, verify=False)
        if r.status_code == 200:
            r.encoding = encoding
            return r.text
        return None
    except:
        return None


# ═══════════════════════════════════════════
#  日期提取
# ═══════════════════════════════════════════

def parse_date(text):
    """从任意字符串中提取 YYYY-MM-DD"""
    if not text:
        return ""
    m = DATE_RE.search(str(text).strip())
    if not m:
        return ""
    if m.group(1):
        y, mo, d = int(m.group(1)), int(m.group(2)), int(m.group(3))
    else:
        y, mo, d = int(m.group(4)), int(m.group(5)), int(m.group(6))
    if not (1990 <= y <= 2099 and 1 <= mo <= 12 and 1 <= d <= 31):
        return ""
    return f"{y:04d}-{mo:02d}-{d:02d}"


def extract_date_from_html(html):
    """从 HTML 中自动提取发布时间（meta/ time 标签优先）"""
    if not html:
        return ""
    for pat in [META_DATE, META_DATE2]:
        m = pat.search(html)
        if m:
            d = parse_date(m.group(1))
            if d:
                return d
    m = TIME_TAG.search(html)
    if m:
        d = parse_date(m.group(1)[:10])
        if d:
            return d
    # 正文中第一个日期
    m = DATE_RE.search(html)
    if m:
        if m.group(1):
            y, mo, d2 = int(m.group(1)), int(m.group(2)), int(m.group(3))
        else:
            y, mo, d2 = int(m.group(4)), int(m.group(5)), int(m.group(6))
        if 1990 <= y <= 2099 and 1 <= mo <= 12 and 1 <= d2 <= 31:
            return f"{y:04d}-{mo:02d}-{d2:02d}"
    return ""


# ═══════════════════════════════════════════
#  HTML 清洗
# ═══════════════════════════════════════════

# ── 正文噪声块剥离（2026-09-11）──────────────────────────────────────
# 政府站常把整段内联 CSS 塞在正文里。实例：绍兴上虞区政府-环评公示某条 content
# 共 9853 字，其中约 9300 字是 3 个 <style> 块（.ewb-header/.bd_weixin_popup/…），
# 真文本（一张公示表）只有 548 字。不剥的后果：
#   ① search_app 列表摘要变成 CSS 文本（`content[:1200]` 整个落在 <style> 里 → 摘要空白）；
#   ② 详情页把 <style> 原样注入 → 全页 CSS 被污染；
#   ③ 每条详情白传 9KB 无用 CSS。
# 实测存量 8,513 / 556,959 行 content 含 <style>（1.5%）。入库剥掉 → 新数据不再有这问题。
_NOISE_BLOCK_RE = re.compile(r'<(script|style|noscript|template)\b[^>]*>.*?</\1\s*>', re.I | re.S)
_NOISE_OPEN_RE = re.compile(r'<(?:script|style|noscript|template)\b[^>]*>.*\Z', re.I | re.S)


def strip_noise_blocks(html):
    """剥掉 <script>/<style>/<noscript>/<template> 块（连内容）、HTML 注释、<link>。

    ⚠️ 只删标签不够 —— 标签**之间**的 CSS 文本会原样留下（那正是摘要变 CSS 的原因）。
    ⚠️ 截断兜底：调用方常先截断再传进来，可能正好切在 </style> 之前 → 未闭合的开标签
       必须整段截到末尾，否则 CSS 一截两断。
    """
    if not html:
        return ""
    s = html
    prev = None
    while prev != s:
        prev = s
        s = _NOISE_BLOCK_RE.sub(' ', s)
    s = _NOISE_OPEN_RE.sub(' ', s)
    s = re.sub(r'<!--.*?-->', ' ', s, flags=re.S)
    s = re.sub(r'<link\b[^>]*>', ' ', s, flags=re.I)
    return s


def clean_html(html):
    """清洗 HTML，保留结构化标签，去掉 style/class 等无用属性"""
    if not html:
        return ""
    raw = strip_noise_blocks(html)          # ⚠️ 先剥 <style>/<script> 块（连块内 CSS 文本）
    raw = re.sub(r' style="[^"]*"', '', raw)
    raw = re.sub(r' class="[^"]*"', '', raw)
    raw = re.sub(r'<span[^>]*>|</span>', '', raw)
    raw = re.sub(r'&nbsp;', ' ', raw)
    raw = re.sub(r'\n{3,}', '\n\n', raw)
    return raw.strip()


# ═══════════════════════════════════════════
#  正文提取（兜底策略）
# ═══════════════════════════════════════════

def extract_content_by_selector(html, css_selector):
    """用 CSS 选择器提取正文容器中的 HTML"""
    if not html or not css_selector:
        return ""
    from parsel import Selector
    sel = Selector(text=html)
    el = sel.css(css_selector)
    if not el:
        return ""
    raw = el.get()
    return clean_html(raw)


def extract_content_by_regex(html, pattern):
    """用正则提取正文容器中的 HTML"""
    if not html or not pattern:
        return ""
    m = re.search(pattern, html, re.DOTALL)
    if m:
        return clean_html(m.group(1))
    return ""


# ═══════════════════════════════════════════
#  数据库操作
# ═══════════════════════════════════════════

def get_eia_conn():
    """获取 eia.db 连接（WAL 模式）"""
    os.makedirs(os.path.dirname(EIA_DB) or '.', exist_ok=True)
    conn = sqlite3.connect(EIA_DB)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA busy_timeout=300000")
    conn.execute("PRAGMA synchronous=NORMAL")
    return conn


def import_to_eia(item, conn=None):
    """
    导入单条到 eia.db。
    item 字段: site_name, title, url, content, pub_date, summary, tags
    返回: True=新增, False=跳过（重复 url）
    """
    own_conn = False
    if conn is None:
        conn = get_eia_conn()
        own_conn = True

    try:
        summary = item.get("summary", "")
        if not summary and item.get("content"):
            summary = re.sub(r'<[^>]+>', ' ', item["content"])
            summary = re.sub(r'\s+', ' ', summary).strip()[:300]

        conn.execute("""
            INSERT OR IGNORE INTO projects
                (site_name, title, url, content, pub_date, summary, tags)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        """, (
            (item.get("site_name") or "")[:200],
            (item.get("title") or "")[:500],
            item.get("url", ""),
            item.get("content", ""),
            (item.get("pub_date") or "")[:10],
            summary[:500],
            (item.get("tags") or "")[:100],
        ))
        inserted = conn.total_changes > 0

        if inserted and item.get("site_name"):
            site_key = hashlib.md5(item["site_name"].encode()).hexdigest()[:12]
            conn.execute("""
                INSERT INTO sites (site_key, name, total_items, last_sync)
                VALUES (?, ?, 1, CURRENT_TIMESTAMP)
                ON CONFLICT(site_key) DO UPDATE SET
                    total_items = total_items + 1,
                    last_sync = CURRENT_TIMESTAMP
            """, (site_key, item["site_name"][:200]))

        conn.commit()
        if own_conn:
            conn.close()
        return inserted

    except Exception as e:
        if own_conn:
            conn.close()
        return False


# ═══════════════════════════════════════════
#  JSONL 输出（备选方案，不依赖 eia.db）
# ═══════════════════════════════════════════

def save_jsonl(item, filepath=None):
    """单条追加到 JSONL 文件"""
    os.makedirs(OUTPUT_DIR, exist_ok=True)
    if filepath is None:
        now = datetime.now().strftime("%Y%m%d_%H%M%S")
        filepath = os.path.join(OUTPUT_DIR, f"crawl_{now}.jsonl")
    with open(filepath, "a", encoding="utf-8") as f:
        f.write(json.dumps(item, ensure_ascii=False) + "\n")
    return filepath


def push_jsonl_file(jsonl_path, batch_label=""):
    """读取 JSONL(字段兼容 url/page_url, date/pub_date/publish_date) 并直写 search.db。
    设置 JSONL_PATH 环境变量时跳过(测试模式)。返回入库条数。"""
    if os.getenv("JSONL_PATH"):
        print(f"  [TEST] JSONL_PATH 已设, 跳过入库: {jsonl_path}")
        return 0
    if not os.path.exists(jsonl_path):
        print(f"  [SKIP] JSONL 不存在: {jsonl_path}")
        return 0
    items = []
    with open(jsonl_path, encoding="utf-8") as f:
        for line in f:
            line = line.strip()
            if not line:
                continue
            try:
                r = json.loads(line)
            except Exception:
                continue
            url = r.get("page_url") or r.get("url") or r.get("source_url") or r.get("href") or r.get("link") or ""
            if not url:
                continue
            items.append({
                "site_name": r.get("site_name") or r.get("source") or "",
                "source_url": url,
                "url": url,
                "title": (r.get("title") or "")[:500],
                "pub_date": r.get("publish_date") or r.get("pub_date") or r.get("date") or "",
                "summary": (r.get("summary") or "")[:500],
                "content": r.get("content") or "",
                "attachments": r.get("attachments") or "",
            })
    if not items:
        print(f"  [SKIP] JSONL 为空: {jsonl_path}")
        return 0
    push_to_searchdb(items, batch_label=batch_label or items[0].get("site_name") or "jsonl")
    print(f"  [DB] 已入库 {len(items)} 条 from {jsonl_path}")
    return len(items)

def import_jsonl(filepath):
    """从 JSONL 文件批量导入 eia.db（可单独用）"""
    from import_to_eia import import_jsonl as _batch_import
    return _batch_import(filepath)


# ═══════════════════════════════════════════
#  批量爬取工具
# ═══════════════════════════════════════════

def batch_crawl(urls, crawl_func, workers=5, label="批量"):
    """
    通用批量并发爬取。
    crawl_func(url) 返回 item dict 或 None。
    """
    from concurrent.futures import ThreadPoolExecutor, as_completed
    ok, fail = 0, 0
    conn = get_eia_conn()
    os.makedirs(OUTPUT_DIR, exist_ok=True)
    jsonl_path = os.path.join(OUTPUT_DIR, f"batch_{datetime.now().strftime('%Y%m%d_%H%M%S')}.jsonl")

    with ThreadPoolExecutor(max_workers=workers) as ex:
        futures = {ex.submit(crawl_func, url): url for url in urls}
        for i, fut in enumerate(as_completed(futures), 1):
            url = futures[fut]
            try:
                item = fut.result()
                if item:
                    save_jsonl(item, jsonl_path)
                    import_to_eia(item, conn)
                    ok += 1
                else:
                    fail += 1
            except:
                fail += 1
            if i % 10 == 0 or i == len(urls):
                print(f"  [{i}/{len(urls)}] ✅{ok} ❌{fail}")

    conn.close()
    print(f"  ✅ 完成: 成功{ok}, 失败{fail}")
    return ok, fail


# ═══════════════════════════════════════════
#  推送至服务器 search.db
# ═══════════════════════════════════════════

SERVER = "root@1.94.217.116"
PASSWORD = ""  # 2026-09-10 明文凭据已移除(该常量无人使用; 推送走 push_to_searchdb)

# Industry classification for titles
INDUSTRY_KEYWORDS = {
    'pharma': ['药品', '制药', '医药', '药业', '药械', 'APIs', 'API', 'GMP', 'cGMP', '疫苗', '生物制品', '干细胞', '抗体', '基因', '诊断试剂', '医疗器械', '医药', '胶原蛋白', '单克隆', '血浆', '胰岛素', '造影剂', '缝合线', '导管', '内窥镜', '起搏器', '透析', '神经外科', '片剂', '丸剂', '胶囊', '软膏', '注射剂', '注射器', '口服液', '辅料', 'CDMO', '多肽', '原药', '母药', '原料药', '兽药', '制剂', '中成药', '生物医药', '制药项目', '医药中间体', '原料药及', '药厂', '制药厂', '医药产业园', '药用'],
    'production': ['油田', '气田', '采油', '采气', '钻井', '页岩气', '海上平台', '酸气处理', '天然气处理站', '天然气集气站', '天然气生产平台', '煤层气生产平台'],
    'transmission': ['管道', '输气', '输油', '门站', '计量站', '压气站', '阀室', '装卸站及专用铁路'],
    'terminals': ['接收站', 'LNG终端', 'LNG', '天然气终端', '天然气储存终端', '油库', '罐区', '原油储罐', '成品油罐', '储运', '成品油仓储码头', '电煤港', '煤炭港', '油品码头', '原油码头', '成品油码头', 'LNG码头', '液化天然气码头', '天然气码头', '油气码头', '液体化工码头', '加油站', '加气站', '加油站扩建', '成品油销售', '燃气公司', '燃气供应', '液化气站', '充电站', '换电站'],
    'port': ['港口', '码头', '泊位', '港区', '港务', '集装箱', '散货', '件杂货', '客滚', '轮渡', '航运', '疏浚', '航道', '船闸', '引航', '渡口', '装卸'],
    'hpi': ['炼化一体', '炼化', '炼油', '炼厂', '石脑油', '催化裂化', '加氢裂化', 'FCC'],
    'altfuel': ['生物柴油', '生物乙醇', '可持续航空燃料', '沼气', '燃料电池', '乙醇燃料', '甲醇燃料', 'SAF', '生物质颗粒', '生物质燃料', '生物质成型', '生物质能', '生物质气化'],
    'power': ['发电', '电站', '光伏', '风电', '太阳能', '储能', 'BESS', 'CAES', 'CSP', '变电站', '变电所', '输变电', '输电', '电网', '核电', '水电站', '抽水蓄能', '压缩空气储能', '煤电', '供热', '生物质发电', '生物质锅炉', '生物质热电'],
    'metals': ['金属与矿物', '有色金属', '稀土', '稀有金属', '不锈钢', '钢铁', '冶金', '矿山', '采矿', '选矿', '尾矿', '冶炼', '钢厂', '轧钢', '炼铁', '炼钢', '铁合金', '钢铁厂', '金矿', '铁矿', '铜矿', '铝土矿', '磷矿', '石墨矿', '石墨化电极', '石墨', '钢结构', '石料', '多金属矿', '锌矿', '铅矿', '镍矿', '镁合金', '铝合金', '钛合金', '铜合金', '锌合金', '轻合金', '铝板', '铝业', '铜业', '锌业', '金属', '矿物', '铝', '铜', '锌', '铅', '镍', '锡', '钨', '钼', '合金', '矿', '水泥', '预拌混凝土', '建材', '石材', '瓷砖', '石膏板', '防水材料', '保温材料', '耐火材料', '马口铁', '易拉罐', '混凝土', '砂浆', '商砼', '沥青混凝土', '水泥制品', '搅拌站', '预拌砂浆'],
    'pulp': ['纸浆', '造纸', '纸板', '木材', '木业', '人造板', '瓦楞', '刨花板', '胶合板', '纸厂', '印刷品', '印刷包装', '纸包装'],
    'food': ['食品', '饮料', '肉类', '屠宰', '乳', '牛奶', '果汁', '啤酒', '调味', '面包', '豆制品', '食用油', '饲料', '糖', '淀粉', '酱油', '酵母', '茶', '咖啡', '水产', '禽', '畜禽', '碾米', '酒厂', '烈酒', '植物产品', '瓶装水', '矿泉水', '烘焙', '花生加工', '鸡蛋加工', '鱼片', '香料', '养殖', '生猪', '蛋鸡', '肉鸭', '肉鸡', '水产养殖', '酿酒', '白酒', '啤酒厂', '酱', '预制菜', '果干', '果汁厂', '肉类加工', '屠宰场', '饲料厂'],
    'cpi': ['废矿物油', '化工', '化学品', '树脂', '聚合物', '聚乙烯', '聚丙烯', 'PVC', 'PET', 'PTA', 'ABS', 'EVA', 'HDPE', 'LDPE', 'PE管', 'PPR', '塑料', '橡胶', '涂料', '染料', '肥料', '农药', '溶剂', '催化剂', '酸', '酯', '醇', '硫酸', '盐酸', '氢氧化', '氧化物', '丙烯', '乙烯', '苯', '酮', '醚', '胺', '烯', '硅', '氟', '氯', '溴', '碘', '粘合剂', '添加剂', '阻燃剂', '表面活性剂', '精细化学品', '水处理剂', '复合肥', '水溶肥', '颜料', '洗涤剂', '增塑剂', '活性炭', '高纯度化学品', '湿化学品', '过氧化氢', '甲醛', '甲醇', '氨', '氧气', '空气分离', '烧碱', '化学试剂', '化学中间体', '炭黑', '气凝胶', '脱硫剂', '纺织助剂', '纤维素纤维', '紫外线吸收剂', '电解质', '杀菌剂', '杀虫剂', '除草剂', '制冷剂', '减水剂', '乙炔', '双酚', '莱赛尔纤维', '脂肪', '炔', '腈', '酐', '醛', '酚', '氢能', '绿氢', '氢气', '电解水制氢', '电解制氢', '水电解制氢', '制氢', '氟化石墨', '全氟聚醚', '氟化改性', '高纯氟气', '软包装', '复合膜', '植物保护', '种子处理剂', '农药制剂', '农药中间体', '植物蛋白', '生物农药', '农药原药'],
    'logistics': ['物流', '物流中心', '物流园', '仓储物流', '保税物流', '冷链物流', '快递分拨'],
    'manufacturing': ['建筑用砂', '制造', '设备', '机械', '零部件', '电子', '汽车', '半导体', '电池', '家电', '仪器', '配件', '泵', '压缩机', '阀门', '轴承', '传感器', '显示屏', '印刷电路板', '印刷线路板', '连接器', '变压器', '无人机', '模具', '晶圆', '数控机床', '造船', '起重机', '空调', '触摸屏', '芯片', '机器人', '雷达', '集成电路', '光刻胶', '电机', '锅炉', '过滤器', '船厂', '机床', '紧固件', '风机', '飞机', '数据中心', '家具', '包装', '电缆', '电线电缆', '特种电缆', '面料', '染整', '织造', '针织', '梭织', '制鞋', '鞋', '标签', '不干胶', '五金', '五金制品', '制品', '电子元件', '光学'],
}

INDUSTRY_ORDER = ["pharma", "production", "transmission", "terminals", "port", "hpi", "altfuel", "power", "metals", "pulp", "food", "cpi", "logistics", "manufacturing"]

# 行政公告排除（2026-08-11 用户决策: 行政公告保持 other）
ADMIN_EXCLUDE = [
    '医保', '药店', '医药机构', '定点零售', '零售药店', '医保服务协议',
    '医保定点', '集采药品销售', '药品经营', '药品价格', '门诊慢性病',
    '医疗保障', '医保目录', '药师', '执业药师',
]

def classify_industry(title):
    """Classify a title into industry id (English). Longest keyword wins, case-insensitive."""
    if not title:
        return "other"
    t = title.lower()
    # 行政公告排除: 医保/药店/定点零售等 → other (用户决策 2026-08-11)
    for kw in ADMIN_EXCLUDE:
        if kw.lower() in t:
            return 'other'
    if '铜梁' in t or '铜仁' in t:
        t = t.replace('铜梁', '·').replace('铜仁', '·')
    pairs = []
    for ind_id, kws in INDUSTRY_KEYWORDS.items():
        for kw in kws:
            pairs.append((len(kw), ind_id, kw.lower()))
    pairs.sort(key=lambda x: -x[0])
    # 用户2026-08-23: 包含"制品" → 制造业, 除非命中已有的行业特定制品词(生物制品/水泥制品/豆制品/五金制品等)
    if '制品' in t:
        existing_goods = [kw for ind, kws in INDUSTRY_KEYWORDS.items() for kw in kws if kw.endswith('制品') and len(kw) >= 3]
        if not any(kw in t for kw in existing_goods):
            return 'manufacturing'
    for _len, ind_id, kw in pairs:
        if kw in t:
            return ind_id
    return "other"

def push_to_searchdb(items, batch_label="crawl"):
    """Write directly to search.db (local version, no SSH)"""
    if not items:
        print("  [SKIP] no data")
        return

    import sqlite3, re, os, json
    from datetime import datetime

    default_db = "/root/search.db"
    db_path = os.getenv("SEARCH_DB", default_db)
    db = sqlite3.connect(db_path)
    db.execute("PRAGMA journal_mode=WAL")
    db.execute("PRAGMA busy_timeout=300000")
    db.execute("PRAGMA synchronous=NORMAL")
    # Ensure tables exist
    db.execute("""CREATE TABLE IF NOT EXISTS gov_raw (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        site_name TEXT, source_url TEXT UNIQUE, page_url TEXT,
        title TEXT, publish_date TEXT, summary TEXT,
        content TEXT, status TEXT, category TEXT, tags TEXT,
        industry TEXT DEFAULT '', script_name TEXT DEFAULT '',
        attachments TEXT DEFAULT '', group_name TEXT DEFAULT '',
        has_table INTEGER DEFAULT 0, date_rank TEXT DEFAULT '', visits INTEGER DEFAULT 0
    )""")
    for _col in ('script_name', 'attachments', 'group_name', 'has_table', 'date_rank', 'visits'):
        try:
            db.execute(f"ALTER TABLE gov_raw ADD COLUMN {_col} TEXT DEFAULT ''")
        except:
            pass
    import sys
    caller_script = os.path.basename(sys.argv[0])
    if not caller_script.endswith('.py'):
        caller_script = ''
    db.execute("""CREATE VIRTUAL TABLE IF NOT EXISTS gov_search USING fts5(
        title, site_name, summary, tokenize=trigram
    )""")
    # FTS 同步由触发器自动维护 (根治 DELETE/UPDATE 后孤儿行导致标题错位)
    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()

    ok, skip = 0, 0
    for item in items:
        try:
            raw_date = item.get("pub_date", "")
            norm_date = normalize_pub_date(raw_date)

            _raw_content = item.get("content", "") or ""
            # ⚠️ 入库前剥 <style>/<script> 块：政府站正文常带整段内联 CSS
            #    （某条 9853 字里 9300 字是 CSS）→ 不剥会让列表摘要变 CSS、详情页样式被污染。
            _html_content = strip_noise_blocks(md_links_to_html(_raw_content, item.get("title") or ""))
            _raw_summary = item.get("summary") or ""
            _html_summary = md_links_to_html(_raw_summary, item.get("title") or "")
            if not _html_summary or _html_summary == _raw_content[:300] or _html_summary == _raw_content:
                _html_summary = _html_content[:300]

            entry = {
                "site_name": item.get("site_name", ""),
                "source_url": item.get("source_url", ""),
                "page_url": item.get("url", ""),
                "title": (item.get("title") or "")[:500],
                "publish_date": norm_date,
                "summary": _html_summary[:500],
                "content": _html_content,
                "status": "active",
                "category": item.get("category", ""),
                "tags": item.get("tags", ""),
                "script_name": caller_script,
                "attachments": item.get("attachments", ""),
                "group_name": item.get("group_name", ""),
                "has_table": 1 if "<table" in (item.get("content") or "") else 0,
                "industry": classify_industry(item.get("title") or ""),
            }

            _cur = db.execute("""INSERT OR IGNORE INTO gov_raw
                (site_name, source_url, page_url, title, publish_date,
                 summary, content, status, category, tags, attachments, industry, script_name,
                 group_name, has_table)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", (
                entry["site_name"], entry["source_url"], entry["page_url"],
                entry["title"][:500], entry["publish_date"],
                entry["summary"][:500], entry["content"],
                entry["status"], entry["category"], entry["tags"],
                entry["attachments"], entry["industry"],
                entry["script_name"], entry["group_name"], entry["has_table"],
            ))
            if _cur.rowcount and _cur.rowcount > 0:
                ok += 1
            else:
                skip += 1
        except Exception as e:
            skip += 1
            print(f"    [WARN] insert error: {e}")

    db.commit()

    # FTS 由触发器自动同步 (trg_gov_raw_fts_ins)。轻量兜底: 只补本次 items, 不全表扫描
    try:
        for item in items:
            r = db.execute("SELECT id, title, site_name, summary FROM gov_raw WHERE source_url=?",
                           (item.get("source_url", ""),)).fetchone()
            if r:
                db.execute("INSERT OR IGNORE INTO gov_search(rowid, title, site_name, summary) VALUES (?,?,?,?)", r)
        db.commit()
        print(f"  [DB] new:{ok} skip:{skip} FTS synced")
    except Exception as e:
        print(f"  [DB] new:{ok} skip:{skip} FTS ERROR: {e}")

    # 正文空检查
    empty_count = sum(1 for item in items if not item.get('content', '').strip())
    if empty_count:
        print(f"  ⚠️  正文为空: {empty_count}/{len(items)} 条")
        for item in items:
            if not item.get('content', '').strip():
                title = (item.get('title') or '')[:60]
                print(f"      空: {title}")

    db.close()