#!/usr/bin/env python3
"""


crawl_zichuan.py - 淄博市淄川区生态环境局信息公开爬虫
Site: www.zichuan.gov.cn
Channel: 行政许可公示 (site_zcqsthjj)
Total: 1674 records, 168 pages
CMS: 大汉版通政府信息公开平台
"""
import sys, re, json, time, os, socket, hashlib
from datetime import datetime, timedelta
from urllib.request import Request, urlopen
from urllib.error import URLError, HTTPError

import sys as _SYS
_MAX_PG = int(_SYS.argv[1]) if len(_SYS.argv) > 1 and _SYS.argv[1].isdigit() else None
if _MAX_PG is not None:
    print('[AutoPg] max_pages=' + str(_MAX_PG))
# END AUTO PAGES
BASE_URL = "http://www.zichuan.gov.cn"
CHANNEL_PATH = "/gongkai/site_zcqsthjj/channel_c_5f9f6a3dc916f710ccee90f9_n_1605684149.7068"
HEADERS = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36"}
TIMEOUT = 20
MAX_RETRIES = 3
SLEEP = 0.1

# --- DB setup ---
import sqlite3
DB_PATH = os.getenv("SEARCH_DB", "/root/search.db")

def get_conn():
    conn = sqlite3.connect(DB_PATH, timeout=60)
    conn.execute("PRAGMA journal_mode=WAL")
    return conn

def ensure_table(conn):
    # Check if table exists with expected columns
    existing = conn.execute("PRAGMA table_info(gov_raw)").fetchall()
    col_names = [r[1] for r in existing]
    if not col_names:
        conn.execute("""
            CREATE TABLE gov_raw (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT,
                content TEXT,
                publish_date TEXT,
                url TEXT,
                site_name TEXT,
                source TEXT DEFAULT '',
                attachments TEXT DEFAULT '',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)

def fetch(url, retries=MAX_RETRIES):
    for attempt in range(retries):
        try:
            req = Request(url, headers=HEADERS)
            with urlopen(req, timeout=TIMEOUT) as resp:
                data = resp.read()
            try: return data.decode("utf-8")
            except: return data.decode("gbk", errors="replace")
        except Exception as e:
            if attempt < retries - 1:
                time.sleep(SLEEP * (attempt + 1))
            else:
                print(f"  [WARN] Failed to fetch {url}: {e}", file=sys.stderr)
                return None

def parse_list_page(html):
    """Parse list page: extract (title, url, date) tuples"""
    items = []
    # Find all <td class="zfxxgk-list-title"><a href="...">title</a> + <td class="zfxxgk-list-time">date
    rows = re.findall(
        r'<td class="zfxxgk-list-title"><a href="([^"]*)">(.*?)</a></td>\s*<td class="zfxxgk-list-time">(.*?)</td>',
        html, re.DOTALL
    )
    for href, title_html, date in rows:
        title = re.sub(r'<[^>]+>', '', title_html).strip()
        full_url = BASE_URL + href if href.startswith("/") else href
        date = date.strip()
        items.append((title, full_url, date))
    return items

def parse_detail(html, url):
    """Parse detail page: extract title, date, content"""
    title = ""
    publish_date = ""
    content = ""
    
    # Title from meta tag
    m = re.search(r'<meta name="ArticleTitle" content="([^"]*)"', html)
    if m:
        title = m.group(1).strip()
    
    # Publish date from meta tag
    m = re.search(r'<meta name="PubDate" content="([^"]*)"', html)
    if m:
        publish_date = m.group(1).strip()[:10]
    
    # Content from the inner details-content div (id="details-content")
    m = re.search(r'<div class="details-content " id="details-content">(.*?)</div>\s*</div>\s*(?:<!--endprint-->|</div>)', html, re.DOTALL)
    if not m:
        # Fallback: outer details-content
        m = re.search(r'<div class="details-content">(.*?)</div>\s*</div>', html, re.DOTALL)
    
    if m:
        content_html = m.group(1)
        # Remove the h1.details-title (title already captured from meta)
        content_html = re.sub(r'<h1 class="details-title">.*?</h1>', '', content_html, flags=re.DOTALL)
        # Remove the details-info clear div (date/print/share controls)
        content_html = re.sub(r'<div class="details-info clear">.*?</div>', '', content_html, flags=re.DOTALL)
        content = content_html.strip()
    
    # Resolve attachment URLs
    content = re.sub(
        r'href="(doc_[^"]*\.(?:doc|docx|pdf|xls|xlsx))"',
        lambda m: f'href="{BASE_URL}{"/" if not m.group(1).startswith("/") else ""}{m.group(1)}"',
        content
    )
    
    # If no content from details-content, try the whole page's content area
    if not content:
        m = re.search(r'class="zfxxgk-text"[^>]*>(.*?)</div>', html, re.DOTALL)
        if m:
            content = m.group(1).strip()
    
    return title, publish_date, content

def crawl_all(limit_months=36):
    """Main crawl function"""
    conn = get_conn()
    ensure_table(conn)
    
    site_name = "zcqsthjj"  # 淄川区生态环境局
    
    # Date filter: 3 years ago from today
    cutoff_date = (datetime.now() - timedelta(days=limit_months * 30)).strftime("%Y-%m-%d")
    print(f"Date cutoff: {cutoff_date} (articles older than this will be skipped)")
    
    total_new = 0
    total_skipped = 0
    page = 1
    max_pages = 200  # safety limit
    
    while page <= (_MAX_PG or max_pages):
        # Build list page URL
        list_url = f"{BASE_URL}{CHANNEL_PATH}/?open=fdzdgknr&nr_page={page}&nr_per_page=10"
        print(f"\n--- Page {page} ---")
        print(f"Fetching: {list_url}")
        
        html = fetch(list_url)
        if not html:
            print("  Failed to fetch, stopping")
            break
        
        items = parse_list_page(html)
        if not items:
            print("  No items found, stopping")
            break
        
        print(f"  Found {len(items)} items")
        
        # Check if all items on this page are too old
        all_too_old = True
        for title, detail_url, list_date in items:
            date_to_check = list_date
            if date_to_check >= cutoff_date:
                all_too_old = False
                break
        
        if all_too_old and page > 1:
            print(f"  All items older than {cutoff_date}, stopping pagination")
            break
        
        for title, detail_url, list_date in items:
            # Check date filter
            if list_date < cutoff_date:
                print(f"  [SKIP] {list_date} {title[:40]}... (older than {cutoff_date})")
                total_skipped += 1
                continue
            
            # Check if already exists in DB (by page_url)
            existing = conn.execute(
                "SELECT id FROM gov_raw WHERE page_url=? AND site_name=?",
                (detail_url, site_name)
            ).fetchone()
            if existing:
                print(f"  [EXISTS] {list_date} {title[:40]}...")
                total_skipped += 1
                continue
            
            # Fetch detail page
            time.sleep(SLEEP)
            detail_html = fetch(detail_url)
            if not detail_html:
                print(f"  [FAIL] {list_date} {title[:40]}...")
                continue
            
            detail_title, pub_date, content = parse_detail(detail_html, detail_url)
            if not detail_title:
                detail_title = title  # fallback to list title
            
            if not pub_date:
                pub_date = list_date
            
            # Generate stable ID from URL
            url_hash = hashlib.md5(detail_url.encode()).hexdigest()[:16]
            record_id = int(url_hash, 16) % 999999999
            
            summary = content[:200] if content else ""
            
            # Insert into DB
            conn.execute(
                """INSERT OR REPLACE INTO gov_raw (id, site_name, title, content, publish_date, source_url, page_url, summary, script_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'crawl_zichuan.py')""",
                (record_id, site_name, detail_title, content, pub_date, detail_url, detail_url, summary)
            )
            
            # Update FTS
            # 2026-09-22: 先提交 gov_raw —— 库上触发器已维护 FTS，下面这条手动写入会因
            #   rowid 重复而 IntegrityError；不先 commit 会把 gov_raw 那条一并回滚（静默丢数据）
            conn.commit()
            conn.execute(
                "INSERT OR REPLACE INTO gov_search(rowid, title, site_name, summary) VALUES (?, ?, ?, ?)",
                (record_id, detail_title, site_name, summary)
            )
            conn.commit()
            total_new += 1
            
            source_display = f" [{content[:30]}...]" if content else ""
            print(f"  [NEW] {pub_date} {detail_title[:50]}...{source_display}")
        
        page += 1
        time.sleep(SLEEP)
    
    conn.close()
    print(f"\n=== Done! New: {total_new}, Skipped/Existing: {total_skipped}, Pages crawled: {page-1} ===")
    return total_new

if __name__ == "__main__":
    crawl_all()