#!/usr/bin/env python3
"""
旌德县（ahjd.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 = 'https://www.ahjd.gov.cn'
LIST_URL = 'https://www.ahjd.gov.cn/Jczwgk/showList/0/111001001/page_{}.html'
DB_PATH = '/root/search.db'
SITE_NAME = '旌德县环评审批'
MAX_PAGES = 20
REQUEST_DELAY = 1.0

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',
    'Referer': 'https://www.ahjd.gov.cn/Jczwgk/',
}

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

# Suppress SSL warnings
import urllib3
urllib3.disable_warnings(urllib3.exceptions.InsecureRequestWarning)


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


def extract_date(soup, url):
    """Extract publish date from detail page or fallback to URL."""
    # Try u-wzinfo span
    info_span = soup.find('span', class_='u-wzinfo')
    if info_span:
        text = info_span.get_text(strip=True)
        m = re.search(r'(\d{4}-\d{2}-\d{2})', text)
        if m:
            return m.group(1)
    # Try meta tags
    meta = soup.find('meta', attrs={'name': 'PubDate'})
    if meta and meta.get('content'):
        m = re.search(r'(\d{4}-\d{2}-\d{2})', meta['content'])
        if m:
            return m.group(1)
    return ''


def extract_content(soup):
    """Extract main content HTML from detail page."""
    div = soup.find('div', class_='j-fontContent') or soup.find('div', class_='m-dttexts')
    if not div:
        return ''
    # Remove style/script
    for tag in div.find_all(['style', 'script']):
        tag.decompose()
    # Get HTML for tables, attachments
    content_html = str(div)
    # Clean up excessive inline styles
    content_html = re.sub(r'\s*style="[^"]*"', '', content_html)
    content_html = re.sub(r"\s*style='[^']*'", '', content_html)
    return content_html.strip()


def extract_attachments(soup):
    """Extract attachment links (PDF/DOC etc)."""
    attachments = []
    down_div = soup.find('div', class_='m-dtdownload')
    if down_div:
        for a in down_div.find_all('a', href=True):
            href = a['href']
            text = a.get_text(strip=True)
            full_url = urljoin(BASE_URL, href)
            attachments.append(full_url)
    return attachments


def extract_list_items(soup):
    """Extract (date, title, detail_url) from list page."""
    items = []
    list_div = soup.find('div', class_='m-liststyle2')
    if not list_div:
        return items
    for li in list_div.find_all('li'):
        a = li.find('a', href=True)
        span = li.find('span')
        if not a:
            continue
        title = a.get_text(strip=True)
        date = span.get_text(strip=True) if span else ''
        detail_url = urljoin(BASE_URL, a['href'])
        items.append((date, title, detail_url))
    return items


def extract_text_simple(html_content):
    """Convert HTML to plain text with paragraph breaks."""
    s = BeautifulSoup(html_content, 'html.parser')
    # Unwrap spans
    for span in s.find_all('span'):
        span.unwrap()
    # Process paragraphs
    parts = []
    for tag in s.find_all(['p', 'div', 'tr', 'li']):
        text = tag.get_text(strip=True)
        if text:
            parts.append(text)
    # If no structured tags found, just get all text
    if not parts:
        text = s.get_text(separator='\n', strip=True)
        parts = [text]
    return '\n\n'.join(parts)


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 url in attachments:
            name = url.split('/')[-1].split('?')[0]
            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):
    """Batch insert into gov_raw + FTS."""
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    
    # Get existing URLs to skip duplicates
    existing = set()
    for row in c.execute('SELECT page_url FROM gov_raw WHERE page_url LIKE ?', ('%ahjd.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 from plain text
        summary = plain_text[:300] if plain_text else title
        
        # Content with attachments appended
        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
        
        # Sync to FTS (external content table gov_search backed by gov_raw)
        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 for %s: %s' % (title[:30], e))
        
        count_new += 1
        existing.add(detail_url)
    
    conn.commit()
    conn.close()
    return count_new, count_skip


def main():
    print('=' * 60)
    print('旌德县（ahjd.gov.cn）环评审批数据爬虫')
    print('=' * 60)
    
    # Step 1: Collect all list page items
    all_items = []
    total_pages = 0
    
    for page in range(1, MAX_PAGES + 1):
        url = LIST_URL.format(page)
        print('\nPage %d: fetching...' % page, end=' ')
        sys.stdout.flush()
        
        soup = get_soup(url)
        if not soup:
            print('SKIP (fetch failed)')
            time.sleep(REQUEST_DELAY)
            break
        
        items = extract_list_items(soup)
        if not items:
            print('END (no items at page %d)' % page)
            break
        
        print('%d items' % len(items))
        total_pages = page
        all_items.extend(items)
        
        if page < MAX_PAGES:
            time.sleep(REQUEST_DELAY)
    
    print('\nTotal list items collected: %d (from %d pages)' % (len(all_items), total_pages))
    
    # Step 2: Fetch detail pages
    all_data = []
    for idx, (date, title, detail_url) in enumerate(all_items, 1):
        print('  [%d/%d] %s' % (idx, len(all_items), title[:50]), end=' ')
        sys.stdout.flush()
        
        soup = get_soup(detail_url)
        if not soup:
            print('FAIL')
            continue
        
        # Extract date from detail page if empty
        if not date:
            date = extract_date(soup, detail_url)
        
        # Extract content and attachments
        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' + (' [%d附件]' % len(attachments) if attachments else ''))
        
        time.sleep(REQUEST_DELAY)
    
    # Step 3: Save to database
    print('\nSaving to database...')
    new_count, skip_count = save_to_db(all_data)
    print('New: %d, Skip: %d' % (new_count, skip_count))
    
    # Step 4: Verify
    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()