#!/usr/bin/env python3
"""
crawl_lechang.py - 乐昌市生态环境分局 建设项目环境影响评价信息 爬虫

站点: 韶关市生态环境局乐昌分局
栏目: 建设项目环境影响评价信息
URL: http://www.lechang.gov.cn/zdlyxxgk/dzjg/sgssthjjlcfj/hjbhxxgk/jsxmhjyxpjxx/
CMS: TRS + 365cyd CDN
特点: CDN只允许某些源IP访问，列表index_N.html静态分页，详情content/post_XXXX.html
"""
import sys, re, time, json, os, hashlib
from datetime import datetime, timedelta
from urllib.parse import urljoin
import subprocess

# ─── 配置 ───
SITE_NAME = "乐昌市-建设项目环境影响评价"
BASE_URL = "http://www.lechang.gov.cn/zdlyxxgk/dzjg/sgssthjjlcfj/hjbhxxgk/jsxmhjyxpjxx/"
WORKING_IP = "36.158.231.195"  # CDN 可通过的源IP
CUTOFF_DATE = (datetime.now() - timedelta(days=365*3)).strftime('%Y-%m-%d')

# 远端服务器配置
REMOTE_HOST = "hw-backup"
REMOTE_DB = "/root/search.db"

# ─── HTTP 请求（通过固定IP） ───
def fetch_url(url, timeout=15):
    """使用固定IP + 正确Host访问"""
    cmd = [
        'curl', '-sL', '--connect-timeout', str(timeout), '--max-time', str(timeout+5),
        '--resolve', f'www.lechang.gov.cn:80:{WORKING_IP}',
        '-H', 'Host: www.lechang.gov.cn',
        '-A', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36'
    ]
    # 如果已经是完整URL，直接使用
    if url.startswith('http'):
        cmd.append(url)
    else:
        cmd.append(urljoin(BASE_URL, url))
    
    r = subprocess.run(cmd, capture_output=True, text=True, timeout=timeout+10)
    if r.returncode != 0 or not r.stdout:
        return None
    return r.stdout

def fetch_list_page(page_num):
    """获取列表页HTML"""
    if page_num == 1:
        url = BASE_URL
    else:
        url = urljoin(BASE_URL, f'index_{page_num}.html')
    return fetch_url(url)

def parse_list_page(html):
    """从列表页提取文章条目"""
    if not html:
        return []
    items = []
    # 查找 <ul class="comlist3 mt10">
    ul_match = re.search(r'<ul[^>]*class="comlist3[^"]*"[^>]*>(.*?)</ul>', html, re.DOTALL)
    if not ul_match:
        return []
    
    ul_content = ul_match.group(1)
    # 提取每个 <li>
    li_pattern = re.compile(r'<li[^>]*>(.*?)</li>', re.DOTALL)
    for li_match in li_pattern.finditer(ul_content):
        li_html = li_match.group(1)
        # 日期
        date_m = re.search(r'<span>([^<]+)</span>', li_html)
        pub_date = date_m.group(1).strip() if date_m else ''
        # 标题 + 链接
        a_m = re.search(r'<a[^>]*href="([^"]*)"[^>]*title="([^"]*)"', li_html)
        if a_m:
            url = a_m.group(1)
            title = a_m.group(2)
        else:
            # 回退：从链接文本提取
            a_m2 = re.search(r'<a[^>]*href="([^"]*)"[^>]*>(.*?)</a>', li_html, re.DOTALL)
            if a_m2:
                url = a_m2.group(1)
                title = re.sub(r'<[^>]+>', '', a_m2.group(2)).strip()
            else:
                continue
        
        if not title or not url:
            continue
        
        items.append({
            'title': title,
            'url': url if url.startswith('http') else urljoin(BASE_URL, url),
            'date': pub_date,
        })
    return items

def fetch_detail(item):
    """爬取详情页，提取正文"""
    html = fetch_url(item['url'], timeout=20)
    if not html:
        return None
    
    # 检测是否被CDN拦截
    if '创宇盾' in html or '403 Forbidden' in html or 'error_403' in html:
        print(f'    [CDN BLOCKED] {item["url"]}')
        return None
    
    # 标题：优先从 ArticleTitle meta 取
    title_m = re.search(r'<meta\s+name="ArticleTitle"\s+content="([^"]*)"', html)
    if title_m:
        item['title'] = title_m.group(1)
    
    # 日期：从 PubDate meta
    date_m = re.search(r'<meta\s+name="PubDate"\s+content="([^"]*)"', html)
    if date_m:
        item['date'] = date_m.group(1)[:10]
    
    # 正文
    content_m = re.search(r'id="content1"[^>]*>(.*?)</div>', html, re.DOTALL)
    if not content_m:
        return None
    
    body_html = content_m.group(1).strip()
    
    # 检查正文是否只有"详见附件"之类无实际内容
    body_text = re.sub(r'<[^>]+>', '', body_html)
    body_text = re.sub(r'\s+', '', body_text).strip()
    
    if not body_text or len(body_text) < 20:
        return None  # 无实际正文，跳过
    
    # 提取附件链接
    attachments = []
    doc_pattern = re.compile(r'<a[^>]*href="([^"]+\.(?:doc|docx|pdf|xls|xlsx|zip|rar))"[^>]*>([^<]+)', re.DOTALL)
    for dm in doc_pattern.finditer(body_html):
        attachments.append(f'<a href="{dm.group(1)}">{dm.group(2).strip()}</a>')
    nfw_pattern = re.compile(r'<a[^>]*class="nfw-cms-attachment"[^>]*href="([^"]+)"[^>]*alt="([^"]*)"', re.DOTALL)
    for nm in nfw_pattern.finditer(body_html):
        attachments.append(f'<a href="{nm.group(1)}">{nm.group(2).strip()}</a>')
    if attachments:
        body_html += '<p>附件：</p>' + '<br>'.join(attachments)
    
    item['content'] = body_html
    return item

def push_to_server(records):
    """将记录推送到远端服务器的search.db（通过临时SQL文件）"""
    if not records:
        return
    
    # 构建 SQL 文件内容
    sql_lines = ['PRAGMA journal_mode=WAL;', 'PRAGMA synchronous=NORMAL;', 'BEGIN TRANSACTION;']
    for r in records:
        title = r['title'][:500].replace("'", "''")
        url = r['url'].replace("'", "''")
        date = r.get('date', '')[:10].replace("'", "''")
        content = r.get('content', '').replace("'", "''")
        source = SITE_NAME.replace("'", "''")
        # 摘要
        summary = re.sub(r'<[^>]+>', ' ', content)
        summary = re.sub(r'\s+', ' ', summary).strip()[:300].replace("'", "''")
        
        sql_lines.append(
            f"INSERT OR IGNORE INTO gov_raw "
            f"(site_name, source_url, page_url, title, publish_date, summary, content, status, category) "
            f"VALUES ('{source}', '', '{url}', '{title}', '{date}', "
            f"'{summary}', '{content}', 'active', '');"
        )
    sql_lines.append('COMMIT;')
    
    # 同步 FTS
    sql_lines.append(
        "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);"
    )
    
    sql_content = '\n'.join(sql_lines)
    
    local_sql = '/tmp/lechang_insert.sql'
    remote_sql = '/tmp/lechang_insert.sql'
    
    with open(local_sql, 'w', encoding='utf-8') as f:
        f.write(sql_content)
    
    r = subprocess.run(['scp', local_sql, f'{REMOTE_HOST}:{remote_sql}'], capture_output=True, text=True, timeout=15)
    if r.returncode != 0:
        print(f'scp error: {r.stderr[:100]}')
        return False
    
    r2 = subprocess.run(['ssh', REMOTE_HOST,
                         f'sqlite3 /root/search.db < {remote_sql} && rm -f {remote_sql}'],
                        capture_output=True, text=True, timeout=60)
    if r2.returncode != 0:
        print(f'sqlite error: {r2.stderr[:300]}')
        # 尝试不删，下次覆盖
        return False
    return True

def run_phase1():
    """Phase 1: 扫描列表页，收集所有3年内文章"""
    all_items = []
    page = 1
    while True:
        print(f'List page {page}...', end=' ', flush=True)
        html = fetch_list_page(page)
        if not html:
            print('FAIL (empty)')
            break
        items = parse_list_page(html)
        if not items:
            print('END (no items)')
            break
        print(f'{len(items)} items, {items[0]["date"]}~{items[-1]["date"]}')
        
        # 检查是否超出3年
        for item in items:
            if item['date'] and item['date'] >= CUTOFF_DATE:
                all_items.append(item)
        
        # 如果最后一篇已超出3年，停止
        if items[-1]['date'] and items[-1]['date'] < CUTOFF_DATE:
            print(f'  Cutoff reached ({CUTOFF_DATE}), stopping')
            break
        
        page += 1
        if page > 30:
            break
        
        time.sleep(0.5)  # 礼貌延迟
    
    print(f'\nPhase 1 done: {len(all_items)} items within 3 years')
    return all_items

def run_phase2(items):
    """Phase 2: 并发爬详情页，提取正文"""
    total = len(items)
    if total == 0:
        return []
    
    results = []
    ok_count = 0
    skip_count = 0
    
    from concurrent.futures import ThreadPoolExecutor, as_completed
    
    with ThreadPoolExecutor(max_workers=5) as ex:
        futures = {ex.submit(fetch_detail, item): item for item in items}
        for i, fut in enumerate(as_completed(futures), 1):
            item = futures[fut]
            try:
                detail = fut.result()
                if detail and detail.get('content'):
                    results.append(detail)
                    ok_count += 1
                else:
                    skip_count += 1
            except Exception as e:
                skip_count += 1
            
            if i % 30 == 0 or i == total:
                print(f'  [{i}/{total}] ✅{ok_count} ⏭️{skip_count}')
    
    print(f'\nPhase 2 done: {ok_count} with content, {skip_count} skipped')
    return results

def check_existing():
    """检查已经在库中的URL"""
    ssh_cmd = ['ssh', f'root@{REMOTE_HOST}',
               f'sqlite3 {REMOTE_DB} "SELECT page_url FROM gov_raw WHERE site_name=\'{SITE_NAME}\';"']
    r = subprocess.run(ssh_cmd, capture_output=True, text=True, timeout=15)
    if r.returncode == 0 and r.stdout:
        return set(line.strip() for line in r.stdout.strip().split('\n') if line.strip())
    return set()

def push_batch(items, batch_size=50):
    """分批推送到服务器"""
    for i in range(0, len(items), batch_size):
        batch = items[i:i+batch_size]
        print(f'  Pushing batch {i//batch_size+1}/{(len(items)-1)//batch_size+1} ({len(batch)} records)...', end=' ', flush=True)
        if push_to_server(batch):
            print('✅')
        else:
            print('❌')
        time.sleep(0.3)

if __name__ == '__main__':
    import argparse
    parser = argparse.ArgumentParser(description='乐昌环评爬虫')
    parser.add_argument('--incremental', action='store_true', help='增量模式：只爬最新条目')
    parser.add_argument('--test', action='store_true', help='测试模式：只爬首页')
    parser.add_argument('--pages', type=int, default=0, help='指定页码数')
    args = parser.parse_args()
    
    print(f'=== 乐昌市-建设项目环境影响评价 爬虫 ===')
    print(f'Cutoff: {CUTOFF_DATE}')
    if args.incremental:
        print('Mode: 增量增量')
    elif args.test:
        print('Mode: 测试 (仅1页)')
    else:
        print('Mode: 全量')
    
    # Phase 1: 列表
    all_items = run_phase1()
    if not all_items:
        print('No items found')
        sys.exit(0)
    
    if args.test:
        all_items = all_items[:5]
    
    # 增量模式：过滤已存在的
    if args.incremental:
        existing = check_existing()
        print(f'Existing in DB: {len(existing)}')
        all_items = [i for i in all_items if i['url'] not in existing]
        if not all_items:
            print('Nothing new to crawl')
            sys.exit(0)
        print(f'New items to crawl: {len(all_items)}')
    
    if args.pages > 0:
        all_items = all_items[:args.pages * 20]
    
    # Phase 2: 爬详情
    results = run_phase2(all_items)
    
    # 推送到服务器
    if results:
        print(f'\nPushing {len(results)} records to {REMOTE_HOST}...')
        push_batch(results)
    
    print(f'\n=== Done: {len(results)} records pushed ===')