#!/usr/bin/env python3
"""
余江区（yujiang.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.yujiang.gov.cn'
LIST_URL = 'http://www.yujiang.gov.cn/module/xxgk/subjectinfo.jsp?standardXxgk=1&area=Y02024&page=1'
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 subjectinfo.jsp."""
    items = []
    for a in soup.find_all('a', href=True):
        txt = a.get_text(strip=True)
        h = a['href']
        if not txt or len(txt) < 10 or 'art/' not in h:
            continue
        b = a.find_next('b')
        if not b:
            continue
        date = b.get_text(strip=True).strip()
        if not re.match(r'\d{4}-\d{2}-\d{2}', date):
            continue
        detail_url = urljoin(BASE_URL, h)
        items.append((date, txt, detail_url))
    return items


def extract_content(soup):
    """Extract content from detail page."""
    # Content in div.screen2 or div.pcxx
    for sel in ['div.screen2', 'div.pcxx']:
        div = soup.select_one(sel)
        if div:
            # Remove style/script
            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)
            # Fix image paths
            content_html = re.sub(r'src="\.\./([^"]+)"', r'src="http://www.yujiang.gov.cn/\1"', content_html)
            return content_html.strip()
    # Fallback: barrierfree_container
    div = soup.find('div', id='barrierfree_container')
    if div:
        for tag in div.find_all(['style', 'script']):
            tag.decompose()
        return str(div)[:50000]
    return ''


def extract_attachments(soup):
    """Extract attachment links."""
    attachments = []
    for a in soup.find_all('a', href=True):
        h = a['href']
        if 'download/downfile' in h or '.pdf' in h or '.doc' in h:
            full_url = urljoin(BASE_URL, h)
            txt = a.get_text(strip=True) or h.split('/')[-1]
            attachments.append((txt, full_url))
    return attachments


def extract_date(soup):
    """Extract publish date from detail page."""
    # Look for date text
    text = soup.get_text()
    m = re.search(r'(\d{4}-\d{2}-\d{2})', text)
    return m.group(1) if m else ''


def extract_text_simple(html_content):
    """Convert HTML to plain text with paragraph breaks."""
    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', 'td', 'li']):
        text = tag.get_text(strip=True)
        if text:
            parts.append(text)
    return '\n\n'.join(parts) if parts else s.get_text(separator='\n', strip=True)


def format_with_attachments(content_html, attachments):
    """Append attachment links to content."""
    result = content_html
    if attachments:
        attach_text = '\n\n<h3>附件</h3>\n<ul>'
        for name, url in attachments:
            attach_text += '\n<li><a href="%s">%s</a></li>' % (url, name)
        attach_text += '\n</ul>'
        result += attach_text
    return result


def save_to_db(items_data):
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    
    existing = set()
    for row in c.execute('SELECT page_url FROM gov_raw WHERE page_url LIKE ?', ('%yujiang.gov.cn%',)):
        existing.add(row[0])
    
    count_new = 0
    count_skip = 0
    
    for date, title, detail_url, content_html, attachments, plain_text in items_data:
        if detail_url in existing:
            count_skip += 1
            continue
        
        summary = plain_text[:300] if plain_text else title
        full_content = format_with_attachments(content_html, attachments)
        plain_text_full = extract_text_simple(full_content)
        
        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, full_content, detail_url, date, date_rank_val))
        
        row_id = c.lastrowid
        try:
            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('余江区（yujiang.gov.cn）生态环境局信息爬虫')
    print('=' * 60)
    
    print('\nFetching list...')
    soup = get_soup(LIST_URL)
    if not soup:
        print('FAILED to fetch list')
        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)
        attachments = extract_attachments(soup)
        plain_text = extract_text_simple(content_html)
        
        all_data.append((date, title, detail_url, content_html, attachments, plain_text))
        print('OK (%dc, %d附件)' % (len(content_html), len(attachments)))
        
        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)
    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()