#!/usr/bin/env python3
"""
新疆生态环境保护产业协会 — 环评公示 爬虫
==============================
标准政府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 列表页提取所有条目"""
    items = []
    seen_urls = set()

    # 匹配 sign-item card 块
    # onclick="window.location.href='/articles/show/123'"
    for m in re.finditer(
        r"""<div[^>]*class="sign-item\s+card"[^>]*onclick="window\.location\.href\s*=\s*'([^']+)'[^>]*>"""
        r""".*?<h5[^>]*class="list-title"[^>]*>(.*?)</h5>"""
        r""".*?<div class="day">(.*?)</div>"""
        r""".*?<div class="year-month">(.*?)</div>""",
        html, re.DOTALL
    ):
        href = m.group(1).strip()
        title = re.sub(r'<[^>]+>', '', m.group(2)).strip()
        day = m.group(3).strip()
        year_month = m.group(4).strip()

        if not title or not href:
            continue

        # 构建完整URL
        if not href.startswith('http'):
            href = urljoin(BASE_URL, href)
        if href in seen_urls:
            continue
        seen_urls.add(href)

        # 组合日期: year-month + day
        date = year_month.replace('年', '-').replace('月', '')
        if '-' not in date and len(date) == 7:
            date = date[:4] + '-' + date[4:]
        date = f"{date}-{day}"

        items.append({
            'title': title,
            'url': href,
            'date': date,
        })

    if not items:
        # 备选方案：直接找 <a href="/articles/show/...">
        for m in re.finditer(
            r'<a[^>]*href="(/articles/show/\d+)"[^>]*class="link-btn"[^>]*>(.*?)</a>',
            html, re.DOTALL
        ):
            href = m.group(1).strip()
            title = re.sub(r'<[^>]+>', '', m.group(2)).strip()
            if not title or not href:
                continue
            href = urljoin(BASE_URL, href)
            if href in seen_urls:
                continue
            seen_urls.add(href)
            items.append({'title': title, 'url': href, '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)
    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)
    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)
    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)
    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")
    dst.execute("PRAGMA journal_mode=WAL")

    site_name = "新疆生态环境保护产业协会"
    new_count = 0
    for r in rows:
        title, url, content, pub_date, summary, cat = 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, cat or "")
            )
            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()