#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Crawler for 黄平县人民政府 - 通知公告
Site: www.qdnhp.gov.cn (贵州黔东南州黄平县)
CMS: TRS WCM, JS document.write() list + right_list anchors
Pattern: 10 str_1 JS items (same on every page) + 20 right_list anchors per page
Total: 6 pages, ~104 records
"""
import requests, re, subprocess, sys, time, os
from bs4 import BeautifulSoup
from urllib.parse import urljoin

DB_PATH = "/root/search.db"
SITE_NAME = "黄平县人民政府-通知公告"
INDUSTRY = "07"
BASE_URL = "https://www.qdnhp.gov.cn"
LIST_URL = "https://www.qdnhp.gov.cn/xwzx/tzgg/"
MAX_PAGES = 5

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
    "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
}


def esc(s):
    return (s or "").replace("'", "''")


def extract_items_from_page(html, page_url):
    """Extract list items from both str_1 JS blocks and right_list anchors"""
    items = []
    seen_urls = set()

    # Method 1: str_1/str_2 JS blocks (10 items, same on every page)
    for m in re.finditer(r'var str_1 = "([^"]+)";\s*var str_2 = "([^"]+)";', html):
        url = m.group(1).strip()
        title = m.group(2).strip()
        if url not in seen_urls:
            seen_urls.add(url)
            date_str = extract_date_from_url(url)
            items.append((title, url, date_str))

    # Method 2: right_list section - target=_blank anchor links
    right_idx = html.find('class="right_list')
    if right_idx >= 0:
        right_section = html[right_idx:]
        for m in re.finditer(r'href="(https://www\.qdnhp\.gov\.cn/xwzx/tzgg/\d+/t\d+_\d+\.html)"\s*title="([^"]+)"', right_section):
            url = m.group(1).strip()
            title = m.group(2).strip()
            if url not in seen_urls:
                seen_urls.add(url)
                date_str = extract_date_from_url(url)
                items.append((title, url, date_str))

    return items


def extract_date_from_url(url):
    """Extract YYYY-MM-DD date from URL like /202607/t20260710_90606664.html"""
    m = re.search(r'/t(\d{4})(\d{2})(\d{2})_\d+\.html', url)
    if m:
        return f"{m.group(1)}-{m.group(2)}-{m.group(3)}"
    m = re.search(r'/(\d{4})(\d{2})/', url)
    if m:
        return f"{m.group(1)}-{m.group(2)}-01"
    return ""


def parse_detail(url):
    """Parse detail page"""
    try:
        r = requests.get(url, headers=HEADERS, timeout=15)
        r.encoding = 'utf-8'
        if r.status_code != 200:
            return None, None, None
    except Exception as e:
        print("  [ERR] %s" % e, flush=True)
        return None, None, None

    soup = BeautifulSoup(r.text, 'html.parser')

    # Title
    title = ""
    title_tag = soup.find('title')
    if title_tag:
        raw = title_tag.get_text(strip=True)
        for sep in [" - ", "-", "——", "—"]:
            if sep in raw:
                parts = raw.split(sep)
                parts.sort(key=len, reverse=True)
                title = parts[0].strip()
                break
        if not title:
            title = raw

    # Date from NewsArticlePubDay
    date_str = ""
    pubday = soup.find(id='NewsArticlePubDay')
    if pubday:
        m = re.search(r'(\d{4}[-/]\d{1,2}[-/]\d{1,2})', pubday.get_text())
        if m:
            date_str = m.group(1).replace('/', '-')
    if not date_str:
        m = re.search(r'(\d{4}[-/]\d{1,2}[-/]\d{1,2})', r.text)
        if m:
            date_str = m.group(1).replace('/', '-')

    # Content from div.zx_content.t_l
    content = ""
    zx_content = soup.find('div', class_='zx_content')
    if not zx_content:
        zx_content = soup.find('div', class_='nry_content')

    if zx_content:
        ps = zx_content.find_all('p')
        if ps:
            paras = []
            for p in ps:
                txt = p.get_text(separator='', strip=True)
                if txt and len(txt) > 5:
                    paras.append(txt)
            content = "\n\n".join(paras)
        else:
            for unwanted in zx_content.find_all(['script', 'style', 'a']):
                if unwanted.name == 'a' and '字体' in unwanted.get_text():
                    unwanted.decompose()
            content = zx_content.get_text(separator='\n', strip=True)

    return title, date_str, content


def save_to_db(items):
    """Save items to search.db using sqlite3 CLI"""
    if not items:
        print("No items to save.", flush=True)
        return

    new_count = 0
    for title, url, date_str, content in items:
        check_cmd = "sqlite3 %s \"SELECT COUNT(*) FROM gov_raw WHERE page_url='%s';\""
        proc = subprocess.run(
            check_cmd % (DB_PATH, esc(url)),
            shell=True, capture_output=True, text=True, timeout=10
        )
        try:
            exists = int(proc.stdout.strip())
        except ValueError:
            exists = 0
        if exists > 0:
            continue

        source = "黄平县人民政府"
        sql = "INSERT INTO gov_raw(page_url, title, content, publish_date, summary, source_url, site_name, industry) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');"
        insert_sql = sql % (
            esc(url), esc(title), esc(content), esc(date_str),
            esc(title[:200]), esc(url), esc(SITE_NAME), esc(INDUSTRY)
        )
        proc = subprocess.run(
            ["sqlite3", "-cmd", ".timeout 60000", DB_PATH],
            input=insert_sql, capture_output=True, text=True, timeout=10
        )
        if proc.returncode != 0:
            err = proc.stderr.strip()
            if "UNIQUE" in err:
                pass
            else:
                print("  [ERR] insert failed: %s - %s" % (err, url), flush=True)
        else:
            new_count += 1
            if new_count <= 5 or new_count % 10 == 0:
                print("  [OK] #%d %s" % (new_count, title[:40]), flush=True)

    print("Inserted %d new records." % new_count, flush=True)


def sync_fts():
    sql = "INSERT OR IGNORE INTO gov_search(rowid, title, site_name) SELECT r.rowid, r.title, r.site_name FROM gov_raw r LEFT JOIN gov_search s ON r.rowid = s.rowid WHERE s.rowid IS NULL;"
    proc = subprocess.run(
        ["sqlite3", "-cmd", ".timeout 60000", DB_PATH],
        input="BEGIN;" + sql + "COMMIT;",
        capture_output=True, text=True, timeout=120
    )
    if proc.returncode == 0:
        print("FTS sync done.", flush=True)
    else:
        print("FTS sync ERR: %s" % proc.stderr[:200], flush=True)


def main():
    print("=" * 50, flush=True)
    print("Starting: %s" % SITE_NAME, flush=True)
    print("Target: %s, max pages: %d" % (LIST_URL, MAX_PAGES), flush=True)
    print("=" * 50, flush=True)

    seen_urls = set()
    all_items = []

    for page in range(1, MAX_PAGES + 1):
        if page == 1:
            page_url = LIST_URL
        else:
            page_url = "https://www.qdnhp.gov.cn/xwzx/tzgg/index_%d.html" % page

        try:
            r = requests.get(page_url, headers=HEADERS, timeout=15)
            if r.status_code != 200:
                print("Page %d: HTTP %d (stop)" % (page, r.status_code), flush=True)
                break
            r.encoding = 'utf-8'

            items = extract_items_from_page(r.text, page_url)
            new_on_page = 0
            for title, url, date_str in items:
                if url not in seen_urls:
                    seen_urls.add(url)
                    all_items.append((title, url, date_str))
                    new_on_page += 1

            print("Page %d: %d items found, %d new (total: %d)" % (
                page, len(items), new_on_page, len(all_items)), flush=True)

            if new_on_page == 0:
                print("  No new items, stopping pagination.", flush=True)
                break
        except Exception as e:
            print("Page %d ERR: %s" % (page, e), flush=True)
            break

    print("\nTotal unique items found: %d" % len(all_items), flush=True)
    if not all_items:
        print("No items to process.", flush=True)
        return

    # Parse detail pages
    successes = []
    total = len(all_items)
    for i, (title, url, date_str) in enumerate(all_items, 1):
        if i <= 3 or i % 10 == 0 or i == total:
            print("[%d/%d] %s" % (i, total, title[:40]), flush=True)
        detail_title, detail_date, content = parse_detail(url)
        if not detail_title:
            detail_title = title
        if not detail_date:
            detail_date = date_str
        successes.append((detail_title, url, detail_date, content))

    # Save to DB
    print("\n--- Saving to DB ---", flush=True)
    save_to_db(successes)

    # FTS sync
    print("\n--- FTS Sync ---", flush=True)
    sync_fts()

    print("\nDone: %d unique items, saved to DB" % len(all_items), flush=True)


if __name__ == '__main__':
    main()