#!/usr/bin/env python3
"""
睿霖化工 (www.ruilinhuagong.com) 爬虫 — 2026-09-08 修复
结构: 首页单页表格 → 所有条目直链PDF
正文内容为PDF，服务器详情页展示为附件链接

修复内容:
  - DB 目标改为 /root/search.db (SEARCH_DB env, 与其余爬虫一致)
  - 删除自建表/自建 FTS 逻辑 (曾因遗留旧 schema 表导致 page_url 列缺失,
    每天 38 条全部"入库失败"、search.db 0 增长)
  - 按主库 gov_raw 列族写入 + gov_search(rowid,title,site_name,summary)
"""
import re
import requests
import sqlite3
import os
import sys
from datetime import datetime

SITE_NAME = '睿霖化工'
BASE_URL = 'https://www.ruilinhuagong.com'
DB_PATH = os.getenv('SEARCH_DB', '/root/search.db')


def fetch_list():
    """获取首页所有条目"""
    resp = requests.get(f'{BASE_URL}/', timeout=30)
    resp.encoding = 'utf-8'
    html = resp.text

    pattern = r'<td class="host-open-content">.*?<a href="([^"]*)"[^>]*>([^<]+)</a>'
    matches = re.findall(pattern, html, re.DOTALL)

    items = []
    seen = set()
    for href, title in matches:
        href = href.strip()
        if not href:
            continue
        title = title.strip()
        if not title or title in seen:
            continue
        seen.add(title)
        if href.startswith('http'):
            pdf_url = href
        else:
            pdf_url = BASE_URL + href

        pub_date = ''
        m = re.search(r'/uploadfile/(\d{4})(\d{2})/', href)
        if m:
            pub_date = f'{m.group(1)}-{m.group(2)}'

        items.append({'title': title, 'pdf_url': pdf_url, 'publish_date': pub_date})

    return items


def main():
    items = fetch_list()
    print(f'=== {SITE_NAME} 爬虫 ===')
    print(f'找到 {len(items)} 条记录')

    conn = sqlite3.connect(DB_PATH, timeout=60)
    conn.execute("PRAGMA busy_timeout=30000")
    cur = conn.cursor()
    new_count = 0
    fail_count = 0

    for item in items:
        pdf_url = item['pdf_url']
        title = item['title']
        pub = item['publish_date']
        date_rank = int(pub.replace('-', '')) if pub and len(pub) >= 8 else 0

        content_html = (f'<p>该公示内容为PDF文件，请点击下方链接查看：</p>'
                        f'<p><a href="{pdf_url}" target="_blank" rel="noopener">查看PDF文档: {pdf_url}</a></p>')
        summary = f'{title} PDF: {pdf_url}'[:300]

        try:
            cur.execute(
                "INSERT OR IGNORE INTO gov_raw "
                "(site_name, title, source_url, page_url, publish_date, date_rank, content, summary, status, category, script_name) "
                "VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                (SITE_NAME, title, pdf_url, pdf_url, pub, date_rank,
                 content_html, summary, 'published', '', os.path.basename(__file__))
            )
            if cur.rowcount > 0:
                rid = cur.lastrowid
                new_count += 1
                try:
                    # 2026-09-22: 先提交 gov_raw —— 库上触发器已维护 FTS，下面这条手动写入会因
                    #   rowid 重复而 IntegrityError；不先 commit 会把 gov_raw 那条一并回滚（静默丢数据）
                    conn.commit()
                    cur.execute(
                        "INSERT OR REPLACE INTO gov_search(rowid, title, site_name, summary) VALUES(?,?,?,?)",
                        (rid, title, SITE_NAME, summary[:300])
                    )
                except sqlite3.IntegrityError:
                    pass
        except Exception as e:
            fail_count += 1
            print(f'  入库失败: {title[:40]}... {e}')

    conn.commit()
    total_rows = cur.execute(
        "SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (SITE_NAME,)).fetchone()[0]
    conn.close()
    print(f'入库: 新增{new_count}, 失败{fail_count}, 累计{total_rows}')


if __name__ == '__main__':
    main()