#!/usr/bin/env python3
"""
crawl_ningguo_sthj.py - 宁国市生态环境分局-其他信息/通知公告
Site: www.ningguo.gov.cn
Branch: 383 (宁国市生态环境分局)
Channel: 22474 (其他信息/通知公告)
WAF: Openness CMS token_verified cookie bypass
"""

import sys, re, time, os, json
import requests
from bs4 import BeautifulSoup
from urllib.parse import urljoin
import urllib.parse

BASE_URL = "https://www.ningguo.gov.cn"
LIST_TPL = BASE_URL + "/XxgkContent/showList/383/22474/page_{}.html"
SITE_NAME = "ningguo_sthj"
SOURCE = "宁国市生态环境分局其他信息"
HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
    "Accept-Language": "zh-CN,zh;q=0.9"
}
TIMEOUT = 20
MAX_RETRIES = 3
SLEEP = 0.5
MAX_PAGES = 5

DB_PATH = os.getenv("SEARCH_DB", "/root/search.db")


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


def fetch(url):
    """Fetch with token_verified cookie bypass for Openness CMS WAF."""
    for attempt in range(MAX_RETRIES):
        try:
            s = requests.Session()
            s.cookies.set("token_verified", "true", domain="www.ningguo.gov.cn")
            r = s.get(url, headers=HEADERS, timeout=TIMEOUT)
            r.encoding = "utf-8"
            if r.status_code == 200:
                return r.text
            elif r.status_code == 408 and attempt < MAX_RETRIES - 1:
                time.sleep(SLEEP * (attempt + 1))
                continue
            else:
                print(f"  [WARN] Status {r.status_code} for {url}", file=sys.stderr)
                return None
        except Exception as e:
            if attempt < MAX_RETRIES - 1:
                time.sleep(SLEEP * (attempt + 1))
            else:
                print(f"  [WARN] Failed {url}: {e}", file=sys.stderr)
                return None
    return None


def parse_list_page(html):
    """Extract (title, url, date) from list page."""
    soup = BeautifulSoup(html, "html.parser")
    items = []
    seen_urls = set()
    for li in soup.find_all("li"):
        a = li.find("a", href=re.compile(r"/OpennessContent/show/\d+\.html"))
        if not a:
            continue
        href = a.get("href", "")
        full_url = urljoin(BASE_URL, href)
        if full_url in seen_urls:
            continue
        seen_urls.add(full_url)
        title = a.get("title", "") or a.get_text(strip=True)
        if not title:
            continue
        span = li.find("span")
        date = span.get_text(strip=True) if span else ""
        items.append((title.strip(), full_url, date))
    return items


def extract_content_parts(soup):
    """Extract text paragraphs and tables from detail page."""
    parts = []
    detailbox = soup.find("div", class_="g-detailbox")
    if not detailbox:
        return ""
    for child in detailbox.children:
        if child.name == "p":
            txt = child.get_text(strip=True)
            if txt:
                parts.append(txt)
        elif child.name == "table":
            md_table = html_table_to_html(child)
            if md_table:
                parts.append(md_table)
        elif child.name == "div":
            txt = child.get_text(strip=True)
            if txt and txt not in ["富文本", "文本下载"]:
                parts.append(txt)
        elif isinstance(child, str) and child.strip():
            txt = child.strip()
            if txt not in ["富文本", "文本下载"]:
                parts.append(txt)
    return "\n\n".join(parts)


def html_table_to_html(table, base_url=""):
    """保留 HTML 表格结构，仅将相对链接/图片转绝对 URL"""
    from bs4 import BeautifulSoup
    tbl = BeautifulSoup(str(table), 'html.parser')
    for a in tbl.find_all('a'):
        href = a.get('href', '')
        if href and not href.startswith(('http', 'javascript', '#')):
            a['href'] = urllib.parse.urljoin(base_url, href) if base_url else href
    for img in tbl.find_all('img'):
        src = img.get('src', '')
        if src and not src.startswith(('http', '//', 'data:')):
            img['src'] = urllib.parse.urljoin(base_url, src) if base_url else src
    return str(tbl)


def extract_attachments(soup, page_url):
    """Extract file attachments from detail page."""
    attachments = []
    for a in soup.find_all("a", href=True):
        href = a["href"]
        if re.search(r"\.(xls|xlsx|pdf|doc|docx|zip|rar)$", href, re.I):
            full_url = urljoin(page_url, href)
            name = a.get_text(strip=True) or href.split("/")[-1]
            if name and name not in ["文本下载", "富文本"]:
                attachments.append({"name": name, "url": full_url})
    return attachments


def parse_detail(html, page_url):
    """Extract (title, publish_date, content, attachments) from detail page."""
    soup = BeautifulSoup(html, "html.parser")

    # Title
    title = ""
    mdetail = soup.find("div", class_="m-detailbox")
    if mdetail:
        utitle = mdetail.find("div", class_="u-title")
        if utitle:
            title = utitle.get_text(strip=True)
    if not title:
        title_tag = soup.find("title")
        if title_tag:
            title = title_tag.get_text(strip=True)
            title = re.sub(r"\s*[-–—]\s*宁国市人民政府\s*$", "", title)

    # Date
    publish_date = ""
    if mdetail:
        udesc = mdetail.find("div", class_="u-desc")
        if udesc:
            dm = re.search(r"(\d{4}-\d{2}-\d{2})", udesc.get_text())
            if dm:
                publish_date = dm.group(1)

    # Content
    content = extract_content_parts(soup)

    # Attachments
    attachments = extract_attachments(soup, page_url)

    return title, publish_date, content, attachments


def push_to_searchdb(items, site_name):
    """Insert items into gov_raw + FTS5 sync."""
    conn = get_conn()
    new_count = 0
    skip_count = 0

    for title, url, content, publish_date, attachments in items:
        summary = content[:500] if content else ""
        attach_json = json.dumps(attachments, ensure_ascii=False) if attachments else ""
        try:
            existing = conn.execute(
                "SELECT id FROM gov_raw WHERE page_url=?", (url,)
            ).fetchone()
            if existing:
                skip_count += 1
                continue

            conn.execute("""
                INSERT INTO gov_raw (site_name, title, content, publish_date, source_url, page_url, summary, attachments)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            """, (site_name, title, content, publish_date, url, url, summary, attach_json))

            record_id = conn.execute("SELECT last_insert_rowid()").fetchone()[0]
            if record_id:
                # 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, title, site_name, summary)
                )

            conn.commit()
            new_count += 1
            print(f"  [NEW] {publish_date} {title[:60]}...", flush=True)
        except Exception as e:
            print(f"  [ERROR] DB insert failed for {url}: {e}", file=sys.stderr)
            conn.rollback()

    conn.close()
    return new_count, skip_count


def main():
    import argparse
    parser = argparse.ArgumentParser(description="宁国市生态环境分局通知公告爬虫")
    parser.add_argument("--pages", type=int, default=MAX_PAGES, help="Pages to crawl")
    args = parser.parse_args()

    pages = min(args.pages, 17)
    all_items = []

    print(f"Starting {SOURCE} crawl, {pages} pages...")

    for page in range(1, pages + 1):
        url = LIST_TPL.format(page)
        print(f"  Page {page}/{pages}: {url}")
        html = fetch(url)
        if not html:
            print(f"  [SKIP] Page {page} failed")
            continue
        items = parse_list_page(html)
        print(f"    Found {len(items)} items")

        for idx, (title, list_url, date) in enumerate(items):
            print(f"    [{idx+1}/{len(items)}] {title[:40]}...", end=" ", flush=True)
            detail_html = fetch(list_url)
            if not detail_html:
                print("[SKIP] detail fetch failed")
                continue

            detail_title, publish_date, content, attachments = parse_detail(detail_html, list_url)
            final_title = detail_title or title
            final_date = publish_date or date

            # Embed attachments into content
            full_content = content
            if attachments:
                attach_links = []
                for att in attachments:
                    attach_links.append(f"[{att['name']}]({att['url']})")
                if full_content:
                    full_content += "\n\n**附件：**\n" + "\n".join(attach_links)
                else:
                    full_content = "**附件：**\n" + "\n".join(attach_links)

            all_items.append((final_title, list_url, full_content, final_date, attachments))
            print("ok")
            time.sleep(SLEEP)

        time.sleep(SLEEP)

    if not all_items:
        print("No items collected!")
        return

    # Check existing
    conn = get_conn()
    existing = conn.execute(
        "SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (SITE_NAME,)
    ).fetchone()[0]
    conn.close()

    print(f"\nExisting records for {SITE_NAME}: {existing}")
    print(f"New items to insert: {len(all_items)}")

    new_count, skip_count = push_to_searchdb(all_items, SITE_NAME)

    # Verify
    conn = get_conn()
    total = conn.execute(
        "SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (SITE_NAME,)
    ).fetchone()[0]
    fts_count = conn.execute(
        "SELECT COUNT(*) FROM gov_search WHERE site_name=?", (SITE_NAME,)
    ).fetchone()[0]
    conn.close()

    print(f"\n=== Result ===")
    print(f"  New: {new_count}, Skipped: {skip_count}")
    print(f"  Total in gov_raw: {total}")
    print(f"  FTS sync verified: {fts_count}")


if __name__ == "__main__":
    main()