#!/usr/bin/env python3
"""Crawler for sthj.fy.gov.cn - 阜阳市生态环境局 行政许可（环评）"""
import urllib.request, urllib.error, ssl, re, sqlite3, os, sys, time
from datetime import datetime

ssl_ctx = ssl.create_default_context()
ssl_ctx.check_hostname = False
ssl_ctx.verify_mode = ssl.CERT_NONE

BASE_DOMAIN = "sthj.fy.gov.cn"
SERVER_IP = "124.232.185.40"  # Direct IP behind QAX CloudWAF
SITE_NAME = "sthj_fy"
HEADERS = {
    "Host": BASE_DOMAIN,
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36"
}

DB_PATH = "/root/search.db"
MAX_PAGES = 5  # Latest 75 records for daily run

# gov_raw table schema: page_url (unique), source_url, title, publish_date, content, summary, site_name, category
PAGE_URL_BASE = "https://" + BASE_DOMAIN

def fetch(url):
    req = urllib.request.Request("https://" + SERVER_IP + url, headers=HEADERS)
    resp = urllib.request.urlopen(req, context=ssl_ctx, timeout=30)
    html = resp.read().decode("utf-8", errors="replace")
    return html

def extract_detail(detail_url):
    """Fetch and extract detail page content"""
    html = fetch(detail_url)
    # Title
    t = re.search(r'<div class="text-center u-title">(.*?)</div>', html, re.DOTALL)
    title = t.group(1).strip() if t else ""
    # Date
    d = re.search(r'发布时间：(\d{4}-\d{2}-\d{2})', html)
    pub_date = d.group(1) if d else ""
    # Source
    s = re.search(r'来源：([^<]+)', html)
    source = s.group(1).strip() if s else "阜阳市生态环境局"
    # Content - div.g-detailbox#zoom
    c = re.search(r'<div class="g-detailbox[^>]* id="zoom"[^>]*>(.*?)</div>\s*</div>', html, re.DOTALL)
    content_html = c.group(1) if c else ""
    if not content_html:
        c2 = re.search(r'id="zoom"[^>]*>(.*?)</div>\s*</div>', html, re.DOTALL)
        content_html = c2.group(1) if c2 else ""
    # Clean content: extract text from HTML, preserve paragraph breaks
    content_text = re.sub(r'<p[^>]*>', '\n', content_html)
    content_text = re.sub(r'</p>', '\n', content_text)
    content_text = re.sub(r'<br\s*/?>', '\n', content_text)
    content_text = re.sub(r'<[^>]+>', '', content_text)
    content_text = re.sub(r'&nbsp;', ' ', content_text)
    content_text = re.sub(r'\n\s*\n+', '\n\n', content_text)
    content_text = content_text.strip()
    
    return title, pub_date, source, content_text, content_html

def main():
    conn = sqlite3.connect(DB_PATH, timeout=60)
    c = conn.cursor()
    
    # Ensure gov_raw table exists
    c.execute('''CREATE TABLE IF NOT EXISTS gov_raw (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT,
        url TEXT UNIQUE,
        content TEXT,
        summary TEXT,
        pub_date TEXT,
        site_name TEXT,
        crawl_time TEXT
    )''')
    conn.commit()
    
    total_new = 0
    total_skip = 0
    
    for page in range(1, MAX_PAGES + 1):
        list_url = "/OpennessTarget/31/8325/page_%d.html" % page
        print("[%s] Fetching list page %d..." % (datetime.now().strftime("%H:%M:%S"), page))
        
        try:
            html = fetch(list_url)
        except Exception as e:
            print("  ERROR fetching list page %d: %s" % (page, e))
            time.sleep(2)
            continue
        
        # Extract items
        items = re.findall(r'href="(/OpennessContent/show/(\d+)\.html)"[^>]*>(.*?)</a>', html, re.DOTALL)
        print("  Found %d items" % len(items))
        
        for href, item_id, title_html in items:
            page_url = PAGE_URL_BASE + href
            title = re.sub(r'<[^>]+>', '', title_html).strip()
            
            # Check if already exists by page_url
            c.execute("SELECT id FROM gov_raw WHERE page_url = ?", (page_url,))
            if c.fetchone():
                total_skip += 1
                continue
            
            # Fetch detail page
            try:
                detail_title, pub_date, source, content_text, content_html = extract_detail(href)
                if not detail_title:
                    detail_title = title
                if not content_text or len(content_text) < 50:
                    print("  SKIP %s: empty or too short content" % item_id)
                    total_skip += 1
                    continue
                
                # Summary
                summary = content_text[:300] if len(content_text) > 300 else content_text
                
                # Insert into gov_raw (page_url, source_url, title, publish_date, content, summary, site_name, category)
                c.execute(
                    "INSERT OR IGNORE INTO gov_raw (page_url, source_url, title, publish_date, content, summary, site_name, category) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
                    (page_url, PAGE_URL_BASE + href, detail_title, pub_date, content_text, summary, SITE_NAME, "环评公示")
                )
                if c.rowcount > 0:
                    total_new += 1
                    print("  + %s: %s" % (item_id, detail_title[:50]))
                conn.commit()
                time.sleep(0.3)  # Be polite
                
            except Exception as e:
                print("  ERROR detail %s: %s" % (item_id, e))
                conn.rollback()
                time.sleep(1)
                continue
    
    conn.close()
    print("\n=== Done ===")
    print("New: %d, Skipped: %d" % (total_new, total_skip))

if __name__ == "__main__":
    main()