#!/usr/bin/env python3
import os
"""
新疆生态环境保护产业协会 — 环评公示 爬虫
==============================
标准政府CMS列表页模式：
  python3 crawl_xjhbcy.py              # 增量（默认：只爬第1页最新）
  python3 crawl_xjhbcy.py --full       # 全量：706页
  python3 crawl_xjhbcy.py --pages=10   # 指定页数
  python3 crawl_xjhbcy.py --sync       # 同步已有数据到服务器

数据流：本地 quality_results.db → SSH → 服务器 search.db
"""

import sys, os, re, json, sqlite3, hashlib, time, base64, subprocess, html as html_mod
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"

SITE_NAME = "新疆生态环境保护产业协会"
DOMAIN = "www.xjhbcy.cn"
BASE_URL = "http://www.xjhbcy.cn"
LIST_BASE = "http://www.xjhbcy.cn/articles/list/121"
TOTAL_PAGES = 706

# ─── HTTP 工具 ───
import ssl, urllib.request
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, timeout=25):
    req = urllib.request.Request(url, headers=HEADERS)
    try:
        resp = urllib.request.urlopen(req, timeout=timeout, context=CTX)
        raw = resp.read()
        text = raw.decode("utf-8", errors="replace")
        return text
    except Exception as e:
        return None

# ─── 列表页解析 ───
def parse_list_page(html):
    """从 xjhbcy 列表页提取所有条目 (BeautifulSoup 容错, 勿用脆弱正则交替漏项)"""
    items = []
    seen_urls = set()
    from bs4 import BeautifulSoup
    soup = BeautifulSoup(html, "html.parser")
    for card in soup.select("div.sign-item.card"):
        # 链接: onclick 或 a[href]
        href = ""
        oc = card.get("onclick", "")
        m = re.search(r"['\"](/articles/show/\d+)['\"]", oc)
        if m:
            href = m.group(1)
        if not href:
            a = card.select_one("a[href*='/articles/show/']")
            if a:
                href = a.get("href", "")
        if not href:
            continue
        href = urljoin(BASE_URL, href)
        if href in seen_urls:
            continue
        seen_urls.add(href)
        # 标题
        title = ""
        h5 = card.select_one("h5.list-title")
        if h5:
            title = re.sub(r"<[^>]+>", "", str(h5)).strip()
        # 日期: day + year-month
        date = ""
        day = card.select_one("div.day")
        ym = card.select_one("div.year-month")
        if day and ym:
            d = day.get_text(strip=True)
            y = ym.get_text(strip=True).replace("年", "-").replace("月", "")
            if "-" not in y and len(y) == 7:
                y = y[:4] + "-" + y[4:]
            date = f"{y}-{d}"
        if not title:
            continue
        items.append({"title": title, "url": href, "date": date})
    return items

def get_detail(url):
    """获取详情页：标题、正文HTML"""
    html = http_get(url)
    if not html:
        return None

    # 标题
    title = ''
    tm = re.search(r'<h3[^>]*>(.*?)</h3>', html, re.DOTALL)
    if tm:
        title = re.sub(r'<[^>]+>', '', tm.group(1)).strip()

    # 正文：text-center mb-3 → footer_cont
    content = ''
    cm = re.search(
        r'<div class="text-center mb-3">(.*?)<div class="footer_cont',
        html, re.DOTALL
    )
    if cm:
        content = cm.group(1).strip()
        # 清理 script/style
        content = re.sub(r'<script[^>]*>.*?</script>', '', content, flags=re.DOTALL|re.I)
        content = re.sub(r'<style[^>]*>.*?</style>', '', content, flags=re.DOTALL|re.I)
        # 清理 inline style (大幅压缩)
        content = re.sub(r'\s+style="[^"]*"', '', content)
        # 去掉开头的<h3>重复标题和<p class="text-muted">元数据
        content = re.sub(r'^<h3>.*?</h3>\s*<p class="text-muted">.*?</p>\s*</div>\s*', '', content, flags=re.DOTALL)
        # 附件链接保留
        # 去掉多余空段落
        content = re.sub(r'<p>\s*<br\s*/?>\s*</p>', '', content)
        if len(content) > 10000:
            content = content[:10000] + '...'

    return {
        'title': title,
        'content': content,
    }

def extract_plain_text(html_content):
    """从HTML提取纯文本，保留段落结构"""
    if not html_content:
        return ''
    text = re.sub(r'</(p|div|li|h[1-6]|tr|td|th)>', r'</\1>\n\n', html_content)
    text = re.sub(r'<br\s*/?>', '\n', text)
    text = re.sub(r'<[^>]+>', '', text)
    text = html_mod.unescape(text)
    text = re.sub(r'[ \t]+', ' ', text)
    text = re.sub(r'\n{3,}', '\n\n', text)
    return text.strip()

# ─── DB 操作 ───
def ensure_table():
    conn = sqlite3.connect(QUALITY_DB, timeout=60)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS crawl_results (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            site_id INTEGER DEFAULT 0,
            title TEXT,
            url TEXT UNIQUE,
            content TEXT,
            publish_date TEXT,
            summary TEXT,
            domain TEXT,
            category TEXT DEFAULT '',
            content_hash TEXT,
            crawl_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()
    conn.close()

def is_url_crawled(url):
    conn = sqlite3.connect(QUALITY_DB, timeout=60)
    cur = conn.execute("SELECT id FROM crawl_results WHERE url=?", (url,))
    r = cur.fetchone()
    conn.close()
    return r is not None

def store_item(item):
    url = item['url']
    content = item.get('content', '')
    summary = item.get('summary', extract_plain_text(content)[:300])
    date = item['date']
    title = item['title']

    # 自动分类阶段标签
    cat = categorize(title, summary)
    content_hash = hashlib.md5((content or '').encode()).hexdigest()

    conn = sqlite3.connect(QUALITY_DB, timeout=60)
    try:
        conn.execute(
            "INSERT OR IGNORE INTO crawl_results "
            "(title, url, content, publish_date, summary, domain, category, content_hash) "
            "VALUES (?,?,?,?,?,?,?,?)",
            (title, url, content, date, summary, DOMAIN, cat, content_hash)
        )
        conn.commit()
        return conn.total_changes > 0
    finally:
        conn.close()

def categorize(title, text):
    t = (title + ' ' + text).lower()
    tags = []
    for stage, kws in [
        ('环评阶段', ['环评', '环境影响评价', '环境影响报告', '第一次公示', '征求意见']),
        ('审批报批', ['审批', '批复', '拟批复', '报批', '拟报批', '许可', '批准']),
        ('竣工验收', ['验收', '竣工', '试运行', '试生产']),
        ('招标采购', ['招标', '投标', '采购', '询价', '中標']),
        ('规划设计', ['规划', '设计', '可研', '可行性研究', '选址']),
        ('安全评价', ['安全评价', '安评', '安全预评价', '安全验收']),
    ]:
        if any(kw in t for kw in kws):
            tags.append(stage)
    return ','.join(tags)

# ─── 同步到服务器 ───
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")
    dst.execute("PRAGMA busy_timeout=300000")

    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 "")[:8000], 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 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}条)")


def main():
    args = sys.argv[1:]
    full_mode = '--full' in args
    sync_only = '--sync' in args

    if sync_only:
        sync_to_server()
        return

    if full_mode:
        pages_to_crawl = TOTAL_PAGES
        mode = "全量"
    else:
        pages_match = [a for a in args if a.startswith('--pages=')]
        pages_to_crawl = int(pages_match[0].split('=')[1]) if pages_match else 1
        mode = f"增量（{pages_to_crawl}页）"

    print(f"🔍 {SITE_NAME}爬虫 — {mode}模式")
    print(f"  总页数: {TOTAL_PAGES}, 本次爬取: {pages_to_crawl}页")
    print()

    ensure_table()

    new_total = 0
    skip_total = 0
    for page in range(1, pages_to_crawl + 1):
        if page == 1:
            url = LIST_BASE
        else:
            url = f"{LIST_BASE}?page={page}"

        html = http_get(url)
        if not html:
            print(f"  [p{page:3d}/{pages_to_crawl}] ✗ 获取列表页失败")
            continue

        items = parse_list_page(html)
        if not items:
            print(f"  [p{page:3d}/{pages_to_crawl}] ✗ 未解析到条目")
            continue

        page_new = 0
        page_skip = 0
        for item in items:
            if is_url_crawled(item['url']):
                page_skip += 1
                skip_total += 1
                continue

            detail = get_detail(item['url'])
            if detail:
                item['title'] = detail['title'] or item['title']
                item['content'] = detail['content'] or ''
                item['summary'] = extract_plain_text(detail.get('content', ''))[:300]

            store_item(item)
            page_new += 1
            new_total += 1

        print(f"  [p{page:3d}/{pages_to_crawl}] ✓ {len(items)}条 (新增{page_new} 跳过{page_skip})" + ' ' * 20, end='\r')

        # 礼貌间隔（全量时稍微快一点）
        time.sleep(0.3 if full_mode else 0.5)

    print()
    print(f"\n📊 完成！新增: {new_total} | 跳过: {skip_total}")

    if new_total > 0:
        sync_to_server()
    else:
        print("  无新数据，跳过同步")

if __name__ == '__main__':
    main()