#!/usr/bin/env python3
"""
cnhu.com (浙江新和成) 环境信息公开爬虫
====================
列表页 → PDF直链 → 入库搜索
  python3 crawl_cnhu.py              # 全量爬取
  python3 crawl_cnhu.py --test       # 测试模式
  python3 crawl_cnhu.py --sync       # 同步到服务器

详情页为PDF文件，直接嵌入PDF链接即可在线预览。
"""

import sys, os, re, json, sqlite3, ssl, urllib.request, base64, subprocess
from datetime import datetime
from urllib.parse import urljoin

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
QUALITY_DB = os.path.join(BASE_DIR, "quality_results.db")
SERVER_SSH = "root@1.94.217.116"
SERVER_DB = "/root/quality_results.db"
SERVER_SEARCH_DB = "/root/search.db"

LIST_URL = "https://www.cnhu.com/environment/42/zh-cn"
BASE = "https://www.cnhu.com"
SITE_NAME = "新和成环境信息"
DOMAIN = "www.cnhu.com"

CTX = ssl._create_unverified_context()
HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/125.0.0.0 Safari/537.36",
}

def http_get(url):
    req = urllib.request.Request(url, headers=HEADERS)
    try:
        resp = urllib.request.urlopen(req, timeout=20, context=CTX)
        return resp.read().decode("utf-8", errors="replace")
    except Exception as e:
        return None

def parse_list(html):
    """解析列表页，提取标题+PDF链接，日期从文件名提取"""
    items = []
    # 匹配 <li> 以内
    for m in re.finditer(
        r'<li>(.*?)</li>',
        html, re.DOTALL
    ):
        block = m.group(1)
        
        # PDF链接
        href_m = re.search(r'href="([^"]+\.pdf)"', block)
        if not href_m:
            continue
        pdf_path = href_m.group(1)
        pdf_url = urljoin(BASE, pdf_path)
        
        # 标题
        title_m = re.search(r'<b[^>]*class="mediaul_frb"[^>]*>(.*?)</b>', block, re.DOTALL)
        if not title_m:
            title_m = re.search(r'<span[^>]*class="mediaul_frtext"[^>]*>(.*?)</span>', block, re.DOTALL)
        if not title_m:
            continue
        title = re.sub(r'<[^>]+>', '', title_m.group(1)).strip()
        if not title:
            continue
        
        # 日期从PDF文件名提取 (e.g., /2026/05/15/xxx.pdf → 2026-05-15)
        date = ""
        date_m = re.search(r'/(\d{4})/(\d{2})/(\d{2})/', pdf_path)
        if date_m:
            date = f"{date_m.group(1)}-{date_m.group(2)}-{date_m.group(3)}"
        
        # 摘要 — 用 📎 附件：格式，_text_to_html() 会自动转成可点击链接
        summary = f"📎 附件：在线预览 ({pdf_url})"
        content = f"<p>📄 <a href=\"{pdf_url}\" target=\"_blank\">点击在线查看PDF</a></p><p>{title}</p>"
        
        items.append({
            "title": title,
            "url": pdf_url,
            "date": date,
            "summary": summary,
            "content": content,
        })
    
    return items

def init_db():
    conn = sqlite3.connect(QUALITY_DB, timeout=60)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("""CREATE TABLE IF NOT EXISTS quality_results (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        site_name TEXT NOT NULL,
        domain TEXT,
        title TEXT,
        url TEXT UNIQUE,
        content TEXT,
        publish_date TEXT,
        summary TEXT,
        crawled_at TEXT DEFAULT (datetime('now','localtime'))
    )""")
    conn.commit()
    conn.close()

def store(items):
    new = 0
    conn = sqlite3.connect(QUALITY_DB, timeout=60)
    conn.execute("PRAGMA journal_mode=WAL")
    for item in items:
        try:
            conn.execute("DELETE FROM quality_results WHERE url=?", (item["url"],))
            conn.execute(
                "INSERT INTO quality_results (site_name, domain, title, url, content, publish_date, summary) VALUES (?,?,?,?,?,?,?)",
                (SITE_NAME, DOMAIN, item["title"], item["url"], item["content"], item["date"], item["summary"]),
            )
            new += 1
        except Exception as e:
            print(f"    ❌ DB: {e}")
    conn.commit()
    conn.close()
    return new

def sync_to_server():
    """将本地数据直接写入 search.db（服务器本地模式）"""
    print("\n📤 同步到 search.db...")

    conn = sqlite3.connect(QUALITY_DB, timeout=60)
    rows = conn.execute(
        "SELECT title, url, content, publish_date, summary FROM crawl_results WHERE domain=? ORDER BY id",
        (DOMAIN,)
    ).fetchall()
    conn.close()

    if not rows:
        print("  本地没有数据")
        return

    dst = sqlite3.connect("/root/search.db", timeout=60)
    dst.execute("PRAGMA journal_mode=WAL")

    site_name = "新和成环境信息"
    new_count = 0
    for r in rows:
        title, url, content, pub_date, summary = r
        try:
            dst.execute(
                "INSERT OR IGNORE INTO gov_raw "
                "(title, page_url, content, publish_date, summary, site_name, tags) "
                "VALUES (?,?,?,?,?,?,?)",
                (title, url, (content or "")[:500000], pub_date or "",
                 (summary or "")[:300], site_name, "")
            )
            if dst.total_changes > 0:
                new_count += 1
        except Exception as e:
            print(f"  Error: {e}")

    if new_count > 0:
        dst.commit()
        # Update FTS
        dst.execute(
            "INSERT OR REPLACE INTO gov_search(rowid,title,site_name,summary) "
            "SELECT r.id,r.title,r.site_name,r.summary FROM gov_raw r "
            "WHERE r.id NOT IN (SELECT rowid FROM gov_search) AND r.site_name=?",
            (site_name,))
        dst.commit()

    total = dst.execute(
        "SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (site_name,)).fetchone()[0]
    dst.close()

    print(f"  OK {new_count}/{len(rows)} 条同步到 search.db (DB共{total}条)")


if __name__ == "__main__":
    import argparse
    parser = argparse.ArgumentParser(description="新和成环境信息爬虫")
    parser.add_argument("--test", action="store_true", help="测试模式")
    parser.add_argument("--sync", action="store_true", help="仅同步")
    args = parser.parse_args()
    
    if args.sync:
        sync_to_server()
        sys.exit(0)
    
    print(f"\n{'='*50}")
    print(f"🚀 {SITE_NAME}")
    print(f"{'='*50}")
    
    html = http_get(LIST_URL)
    if not html:
        print("❌ 列表页获取失败")
        sys.exit(1)
    
    items = parse_list(html)
    print(f"  📋 解析到 {len(items)} 条记录")
    
    if args.test:
        print(f"\n🔬 测试模式：")
        for item in items[:5]:
            print(f"  ✅ {item['title'][:40]}... | {item['date']} | {item['url'][-30:]}")
        print(f"  ...（共{len(items)}条，未存库）")
        sys.exit(0)
    
    init_db()
    n = store(items)
    
    print(f"  ✅ 入库 {n} 条")
    print(f"\n  📊 范围: {items[-1]['date']} ~ {items[0]['date']}")
    print(f"  📄 全部为PDF文档")
    
    sync_to_server()
    print(f"\n{'='*50}")
    print(f"🏁 完成")