#!/usr/bin/env python3
"""
crawl_huanpingbao.py — 环评宝爬虫
=================================
http://www.huanpingbao.cn/jcb-portal/publicity/indexPublicity

API：
  列表: GET /jcb-rest/publicityList?limit=25&offset={offset}
  详情: 列表自带 content（完整HTML），无需额外请求
  总数: ~40427 条

规则：
  - 全量爬取（API 分页，无页数限制）
  - 正文保留 HTML 格式
  - 结果写 search.db（gov_raw 表，触发器自动建 FTS）

用法：
  python3 crawl_huanpingbao.py             # 全量爬取
  python3 crawl_huanpingbao.py --limit=10  # 测试跑 10 条
  python3 crawl_huanpingbao.py --stats     # 看 search.db 统计
"""

import re, json, os, sys, time, html as html_mod
import urllib.request, urllib.parse, hashlib, ssl, sqlite3
from datetime import datetime, timezone
from urllib.parse import urljoin

ssl._create_default_https_context = ssl._create_unverified_context

# ── 配置 ──
SITE_NAME = "环评宝"
BASE_URL = "http://www.huanpingbao.cn"
API_URL = BASE_URL + "/jcb-rest/publicityList"
DETAIL_URL_TPL = BASE_URL + "/jcb-portal/publicity/publicity_detail?id={id}"
SERVER_SEARCH_DB = "/root/search.db"   # 直接写服务器的 search.db

# 本机临时 JSONL 备份（可选）
LOCAL_OUTPUT = os.path.join(os.path.dirname(os.path.abspath(__file__)), "output", "huanpingbao.jsonl")

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",
}

PUBLICITY_TYPE_MAP = {0: "环评公示", 1: "验收公示", 2: "其他公示", 3: "应急预案公示"}
THREE_YEARS_AGO = (datetime.now(timezone.utc).timestamp() - 365*3*86400)


def http_get(url, timeout=30):
    req = urllib.request.Request(url, headers=HEADERS)
    return urllib.request.urlopen(req, timeout=timeout).read().decode('utf-8', errors='replace')


def fetch_page(url, params=None):
    """GET 请求，返回 JSON 或 None"""
    if params:
        qs = urllib.parse.urlencode(params)
        full_url = f"{url}?{qs}"
    else:
        full_url = url
    try:
        resp = http_get(full_url)
        return json.loads(resp) if resp else None
    except Exception as e:
        print(f"  ⚠️ 请求失败: {full_url[:80]} -> {e}")
        return None


def parse_date(date_str):
    """解析 YYYY-MM-DD HH:MM:SS → YYYY-MM-DD"""
    if not date_str:
        return ""
    m = re.search(r'(\d{4})-(\d{2})-(\d{2})', str(date_str))
    if m:
        return f"{m.group(1)}-{m.group(2)}-{m.group(3)}"
    return ""


def build_item(api_item):
    """将 API 返回的单条数据转为 search.db 格式"""
    item_id = api_item.get("id")
    title = (api_item.get("title") or "").strip()
    raw_content = api_item.get("content") or ""

    # 解码 HTML 实体: &lt;p&gt; → <p>
    content = html_mod.unescape(raw_content)

    # 构建页面 URL（用于去重）
    page_url = DETAIL_URL_TPL.format(id=item_id)

    # 日期
    pub_date = parse_date(api_item.get("createTime", ""))

    # 来源
    create_user = (api_item.get("createusername") or "").strip()

    # 正文前 300 字做摘要
    text = re.sub(r'<[^>]+>', ' ', content)
    text = re.sub(r'\s+', ' ', text).strip()
    summary = text[:300]

    # 附件
    doc_url = api_item.get("documentUrl") or ""
    if doc_url and doc_url.strip():
        # URL decode
        doc_name = urllib.parse.unquote(doc_url)
        # 拼完整下载链接
        full_doc_url = f"http://newfile.123jcb.com/reports/{doc_url}" if not doc_url.startswith('http') else doc_url
        content += f'\n<p><a href="{full_doc_url}" target="_blank">📎 附件: {doc_name}</a></p>'

    # 类型标签
    ptype = api_item.get("publicityType")
    ptype_label = PUBLICITY_TYPE_MAP.get(ptype, "其他")
    industry = (api_item.get("industryName") or "").strip()

    # 组装标签
    tags = ptype_label
    if industry:
        tags += f",{industry}"

    return {
        "site_name": SITE_NAME,
        "source_url": BASE_URL + "/jcb-portal/publicity/indexPublicity",
        "page_url": page_url,
        "title": title,
        "publish_date": pub_date,
        "summary": summary,
        "content": content,
        "status": "active",
        "category": ptype_label,
        "tags": tags,
    }


def save_to_local(item):
    """保存到本地 JSONL 备份"""
    os.makedirs(os.path.dirname(LOCAL_OUTPUT), exist_ok=True)
    with open(LOCAL_OUTPUT, "a", encoding="utf-8") as f:
        f.write(json.dumps(item, ensure_ascii=False) + "\n")


def write_to_searchdb(conn, item):
    """写入 search.db 的 gov_raw 表"""
    try:
        conn.execute("""
            INSERT OR IGNORE INTO gov_raw
                (site_name, source_url, page_url, title, publish_date,
                 summary, content, status, category, tags)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, (
            item["site_name"],
            item["source_url"],
            item["page_url"],
            item["title"][:500],
            item["publish_date"],
            item["summary"][:500],
            item["content"],
            item["status"],
            item["category"],
            item["tags"],
        ))
        return conn.total_changes > 0
    except Exception as e:
        print(f"  ⚠️ 写入失败: {e}")
        return False


def crawl(max_items=None):
    """全量爬取"""
    print(f"\n📡 [{SITE_NAME}] API爬取 (limit=25/页)")

    # 连接本地 eia.db 获取已有 site_key（用于跳过去重）
    total_fetched = 0
    ok = 0
    skip = 0

    # 建立连接
    import subprocess
    # 用 SSH 在服务器上操作，走管道写数据
    # 先在本地收集，批量写入
    conn = sqlite3.connect(":memory:")
    conn.execute("""
        CREATE TABLE IF NOT EXISTS temp_items (
            site_name TEXT, source_url TEXT, page_url TEXT UNIQUE,
            title TEXT, publish_date TEXT, summary TEXT,
            content TEXT, status TEXT, category TEXT, tags TEXT
        )
    """)

    offset = 0
    limit = 25
    page = 0
    has_more = True

    while has_more:
        page += 1
        if max_items and total_fetched >= max_items:
            break

        params = {"limit": limit, "offset": offset}
        data = fetch_page(API_URL, params)

        if not data:
            print(f"  ⚠️ 第{page}页无响应，终止")
            break

        rows = data.get("row") or data.get("rows")
        total = data.get("total", 0)

        if not rows:
            break

        for api_item in rows:
            if max_items and total_fetched >= max_items:
                break

            item = build_item(api_item)
            save_to_local(item)

            try:
                conn.execute("""
                    INSERT OR IGNORE INTO temp_items
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                """, (
                    item["site_name"], item["source_url"], item["page_url"],
                    item["title"], item["publish_date"], item["summary"],
                    item["content"], item["status"], item["category"], item["tags"],
                ))
                if conn.total_changes > 0:
                    ok += 1
                else:
                    skip += 1
            except:
                skip += 1

            total_fetched += 1

        offset += limit
        has_more = total_fetched < total
        print(f"  [第{page}页] {total_fetched}/{total} 条 | ✅{ok} (本地暂存)")

    conn.close()

    print(f"\n  📊 采集完成: 共{total_fetched}条, 新增{ok}")

    # 提示导入
    print(f"\n  📤 导入命令:")
    print(f"     python3 import_to_server.py output/huanpingbao.jsonl")
    print(f"     或:")
    print(f"     python3 import_to_server.py {LOCAL_OUTPUT}")

    return ok, skip


def print_stats():
    """查看 search.db 统计"""
    import subprocess
    cmd = "ssh -o StrictHostKeyChecking=no root@1.94.217.116 \"sqlite3 /root/search.db 'SELECT COUNT(*) FROM gov_raw WHERE site_name=\\\"环评宝\\\"'\""
    result = subprocess.run(cmd, shell=True, capture_output=True, text=True, timeout=10)
    count = result.stdout.strip()
    print(f"📊 search.db 环评宝数据: {count} 条")

    cmd2 = "ssh -o StrictHostKeyChecking=no root@1.94.217.116 \"sqlite3 /root/search.db 'SELECT COUNT(*) FROM gov_raw'\""
    result2 = subprocess.run(cmd2, shell=True, capture_output=True, text=True, timeout=10)
    total = result2.stdout.strip()
    print(f"📊 search.db 全部数据: {total} 条")


if __name__ == "__main__":
    import argparse
    parser = argparse.ArgumentParser(description=f"爬虫: {SITE_NAME}")
    parser.add_argument("--limit", type=int, help="测试：只跑 N 条")
    parser.add_argument("--stats", action="store_true", help="查看 search.db 统计")
    args = parser.parse_args()

    if args.stats:
        print_stats()
        sys.exit(0)

    # 先看当前 state
    print("当前 search.db 状态：")
    print_stats()

    t0 = time.time()
    ok, skip = crawl(max_items=args.limit)
    print(f"\n⏱ 耗时: {time.time()-t0:.1f}s")

    print("\n写入后状态：")
    print_stats()