#!/usr/bin/env python3
"""
会东县（schd.gov.cn）— 环境保护 > 环评公告 爬虫
写入 /root/search.db 的 gov_raw 表 + FTS
"""

import requests
import sqlite3
import time
import re
import sys
from bs4 import BeautifulSoup
from urllib.parse import urljoin

BASE_URL = 'http://www.schd.gov.cn'
LIST_URL = 'http://www.schd.gov.cn/xxgk/zdlyxxgk/hjbh/hpgg/'
DB_PATH = '/root/search.db'
SITE_NAME = '会东县环评公告'

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',
    'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8',
    'Accept-Language': 'zh-CN,zh;q=0.9,en;q=0.8',
}

session = requests.Session()
session.headers.update(HEADERS)


def get_soup(url, timeout=30):
    try:
        resp = session.get(url, timeout=timeout)
        resp.encoding = 'utf-8'
        return BeautifulSoup(resp.text, 'html.parser')
    except Exception as e:
        print('  ERROR: %s' % e, file=sys.stderr)
        return None


def extract_list_items(soup):
    """Extract (date, title, detail_url) from list page."""
    items = []
    for li in soup.find_all('li'):
        a = li.find('a', href=True)
        span = li.find('span')
        if not a or not span:
            continue
        title = a.get_text(strip=True)
        date = span.get_text(strip=True)
        # Skip non-content items (navigation, footer, short text)
        if not date or not re.match(r'\d{4}-\d{2}-\d{2}', date):
            continue
        if len(title) < 10:
            continue
        detail_url = urljoin(LIST_URL, a['href'])
        items.append((date, title, detail_url))
    return items


def extract_content(soup):
    """Extract main content HTML from detail page."""
    # Try multiple content containers
    for sel in ['div.xl-cont', 'div.trs_editor_view', 'div#font-s']:
        div = soup.select_one(sel)
        if div:
            for tag in div.find_all(['style', 'script']):
                tag.decompose()
            content_html = str(div)
            content_html = re.sub(r'\s*style="[^"]*"', '', content_html)
            content_html = re.sub(r"\s*style='[^']*'", '', content_html)
            # Keep images with their src attributes
            return content_html.strip()
    return ''


def extract_date(soup):
    """Extract publish date from detail page."""
    p = soup.find('p', class_='xl-p2')
    if p:
        m = re.search(r'(\d{4}-\d{2}-\d{2})', p.get_text())
        if m:
            return m.group(1)
    return ''


def extract_text_simple(html_content):
    """Convert HTML to plain text with paragraph breaks, keep image refs."""
    if not html_content:
        return ''
    s = BeautifulSoup(html_content, 'html.parser')
    for span in s.find_all('span'):
        span.unwrap()
    parts = []
    for tag in s.find_all(['p', 'div', 'tr', 'li']):
        text = tag.get_text(strip=True)
        if text:
            parts.append(text)
    # Also note images
    img_count = len(s.find_all('img'))
    if img_count:
        parts.append('[本页含 %d 张图片附件]' % img_count)
    return '\n\n'.join(parts) if parts else s.get_text(separator='\n', strip=True)


def save_to_db(items_data):
    conn = sqlite3.connect(DB_PATH, timeout=60)
    c = conn.cursor()
    
    existing = set()
    for row in c.execute('SELECT page_url FROM gov_raw WHERE page_url LIKE ?', ('%schd.gov.cn%',)):
        existing.add(row[0])
    
    count_new = 0
    count_skip = 0
    
    for date, title, detail_url, content_html, plain_text in items_data:
        if detail_url in existing:
            count_skip += 1
            continue
        
        summary = plain_text[:300] if plain_text else title
        
        import datetime
        date_rank_val = int(datetime.datetime.now().strftime('%Y%m%d'))
        c.execute('''
            INSERT INTO gov_raw (site_name, title, summary, content, page_url, publish_date, date_rank)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        ''', (SITE_NAME, title, summary, content_html, detail_url, date, date_rank_val))
        
        row_id = c.lastrowid
        try:
            # 2026-09-22: 先提交 gov_raw —— 下面手动写 FTS 会因触发器已写过同一
            #   rowid 而 IntegrityError，若不先 commit，这条记录会被一并回滚（静默丢数据）
            conn.commit()
            c.execute("INSERT INTO gov_search(rowid, title, site_name, summary) VALUES (?, ?, ?, ?)",
                      (row_id, title, SITE_NAME, summary[:200]))
        except Exception as e:
            print('  FTS error: %s' % e)
        
        count_new += 1
        existing.add(detail_url)
    
    conn.commit()
    conn.close()
    return count_new, count_skip


def main():
    print('=' * 60)
    print('会东县（schd.gov.cn）环评公告爬虫')
    print('=' * 60)
    
    print('\nFetching list: %s' % LIST_URL)
    soup = get_soup(LIST_URL)
    if not soup:
        print('FAILED to fetch list page')
        sys.exit(1)
    
    items = extract_list_items(soup)
    print('Found %d items' % len(items))
    
    if not items:
        print('No items to crawl')
        return
    
    all_data = []
    for idx, (date, title, detail_url) in enumerate(items, 1):
        print('  [%d/%d] %s' % (idx, len(items), title[:50]), end=' ')
        sys.stdout.flush()
        
        soup = get_soup(detail_url)
        if not soup:
            print('FAIL')
            continue
        
        if not date:
            date = extract_date(soup)
        
        content_html = extract_content(soup)
        plain_text = extract_text_simple(content_html)
        
        all_data.append((date, title, detail_url, content_html, plain_text))
        print('OK (%dc)' % len(content_html))
        
        time.sleep(0.5)
    
    print('\nSaving to database...')
    new_count, skip_count = save_to_db(all_data)
    print('New: %d, Skip: %d' % (new_count, skip_count))
    
    conn = sqlite3.connect(DB_PATH, timeout=60)
    total = conn.execute('SELECT COUNT(*) FROM gov_raw WHERE site_name = ?', (SITE_NAME,)).fetchone()[0]
    conn.close()
    print('Total in DB: %d' % total)
    print('\nDone!')


if __name__ == '__main__':
    main()