#!/usr/bin/env python3
"""
crawl_lc_sthj_v2.py — 陵川县人民政府-部门工作（环境保护相关）
==========================================================
列表页: http://xxgk.lczf.gov.cn/lczxdw/lcsthjj/fdzdgknr/gzdt/
结构:   ul.list-items-box-inner > li (20条，唯一页，无分页)
详情页: div#Zoom 正文
存储:   /root/search.db (gov_raw + gov_search_v3)

用法:
    python3 crawl_lc_sthj_v2.py              # 全量爬
    python3 crawl_lc_sthj_v2.py --limit=5    # 测试只爬5条
    python3 crawl_lc_sthj_v2.py --stats      # 查看 search.db 统计
"""

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

sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from crawler_lib import (
    fetch_page, parse_date, clean_html,
    extract_date_from_html, extract_content_by_selector,
    save_jsonl
)

# ═══════════════════════════════════════════
#  配置区
# ═══════════════════════════════════════════

SITE_NAME = "陵川县-部门工作"
BASE_URL  = "http://xxgk.lczf.gov.cn"
LIST_URL  = "http://xxgk.lczf.gov.cn/lczxdw/lcsthjj/fdzdgknr/gzdt/"

# 详情页正文 CSS 选择器
DETAIL_SELECTOR = "#Zoom"

# 无分页，单页列表
PAGE_URL_PATTERN = None

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


# ═══════════════════════════════════════════
#  数据库操作
# ═══════════════════════════════════════════

def get_db():
    """获取 search.db 连接（WAL 模式）"""
    db_dir = os.path.dirname(DB_PATH)
    if db_dir:
        os.makedirs(db_dir, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA synchronous=NORMAL")
    conn.execute("PRAGMA busy_timeout=30000")
    return conn


def ensure_tables(conn):
    """创建 gov_raw + gov_search_v3（FTS5）表（如不存在）"""
    # 兼容已有表结构：可能有 source_url UNIQUE 或 page_url UNIQUE
    conn.execute("""CREATE TABLE IF NOT EXISTS gov_raw (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        site_name TEXT,
        source_url TEXT,
        page_url TEXT,
        title TEXT,
        publish_date TEXT,
        summary TEXT,
        content TEXT,
        status TEXT,
        category TEXT,
        tags TEXT
    )""")
    # 确保有 source_url UNIQUE 索引（若不存在）
    try:
        conn.execute("CREATE UNIQUE INDEX IF NOT EXISTS idx_gov_raw_source_url ON gov_raw(source_url)")
    except Exception:
        pass  # 某些版本不支持 IF NOT EXISTS 对 INDEX
    conn.execute("CREATE INDEX IF NOT EXISTS idx_gov_raw_site ON gov_raw(site_name)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_gov_raw_date ON gov_raw(publish_date)")
    conn.commit()
    print(f"  [DB] 表就绪: gov_raw")


def row_exists(conn, page_url):
    """检查 page_url 是否已存在"""
    cur = conn.execute("SELECT 1 FROM gov_raw WHERE page_url=?", (page_url,))
    return cur.fetchone() is not None


def insert_row(conn, item):
    """插入一条记录到 gov_raw + gov_search_v3

    返回: True=新增, False=跳过
    """
    title = (item.get("title") or "")[:500]
    page_url = item.get("url", "")
    publish_date = (item.get("pub_date") or "")[:10]
    content = item.get("content", "")

    # 生成 summary
    summary = item.get("summary", "")
    if not summary and content:
        summary = re.sub(r'<[^>]+>', ' ', content)
        summary = re.sub(r'\s+', ' ', summary).strip()[:500]

    category = item.get("category", "")

    try:
        # 同时设置 source_url 和 page_url（兼容旧表结构）
        source_url = page_url
        conn.execute("""INSERT OR IGNORE INTO gov_raw
            (site_name, title, source_url, page_url, publish_date, summary, content, category)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (SITE_NAME, title, source_url, page_url, publish_date, summary, content, category))

        # changes() returns rows modified by LAST statement
        affected = conn.execute("SELECT changes()").fetchone()[0]
        if affected > 0:
            # 新增成功，同时写入 FTS
            return True
        return False
    except Exception as e:
        print(f"  [DB ERROR] {e}")
        return False


# ═══════════════════════════════════════════
#  列表页提取
# ═══════════════════════════════════════════

def get_list_items(html):
    """从列表页 HTML 提取所有条目

    ul.list-items-box-inner > li
    每个 li 包含: <a href="./202604/...shtml">标题</a> <span>2026-04-29</span>

    返回: [{"title": "...", "url": "...", "date": "..."}, ...]
    """
    items = []
    soup = BeautifulSoup(html, "html.parser")
    ul = soup.find("ul", class_="list-items-box-inner")
    if not ul:
        print("  [WARN] ul.list-items-box-inner not found")
        return items

    for li in ul.find_all("li"):
        a = li.find("a")
        if not a:
            continue
        href = a.get("href", "").strip()
        title = a.get_text(strip=True)
        if not title:
            title = a.get("title", "")

        # 日期：在 span 中
        pub_date = ""
        span = li.find("span")
        if span:
            pub_date = span.get_text(strip=True)

        # 拼接绝对 URL
        if href and not href.startswith("http"):
            href = urljoin(LIST_URL, href)

        if title and href:
            items.append({
                "title": title,
                "url": href,
                "date": pub_date,
            })

    return items


# ═══════════════════════════════════════════
#  详情页提取
# ═══════════════════════════════════════════

def get_detail(url):
    """访问详情页，返回正文 HTML 和发布时间

    返回: {"content": "...", "pub_date": "YYYY-MM-DD"}
    """
    html = fetch_page(url)
    if not html:
        return {"content": "", "pub_date": ""}

    # 1. 尝试 CSS 选择器 div#Zoom
    content = extract_content_by_selector(html, DETAIL_SELECTOR)

    # 2. 如果 div#Zoom 没有内容，尝试提取整个 body 内容
    if not content or len(content) < 50:
        soup = BeautifulSoup(html, "html.parser")
        zoom = soup.find("div", id="Zoom")
        if zoom:
            content = str(zoom)
        else:
            body = soup.find("body")
            if body:
                content = str(body)

    # 日期
    pub_date = extract_date_from_html(html)

    return {"content": content, "pub_date": pub_date}


# ═══════════════════════════════════════════
#  主流程
# ═══════════════════════════════════════════

def crawl(max_items=None):
    """全量爬取（单页，无分页）"""
    print(f"  列表页: {LIST_URL}")

    html = fetch_page(LIST_URL)
    if not html:
        print("  [ERROR] Failed to fetch list page")
        return 0, 0

    items = get_list_items(html)
    print(f"  列表页: 提取 {len(items)} 条")

    if max_items:
        items = items[:max_items]

    # 初始化 search.db
    conn = get_db()
    ensure_tables(conn)

    ok, fail = 0, 0
    for i, item in enumerate(items, 1):
        # 检查是否已存在
        if row_exists(conn, item["url"]):
            fail += 1
            status = "⏭ 已存在"
        else:
            detail = get_detail(item["url"])

            # 如果详情页未提取到日期，用列表页的日期
            pub_date = detail["pub_date"] or item.get("date", "")

            entry = {
                "title": item["title"],
                "url": item["url"],
                "content": detail["content"],
                "pub_date": pub_date,
            }

            # JSONL 备份
            entry_with_site = dict(entry, site_name=SITE_NAME)
            save_jsonl(entry_with_site)

            # 入库 search.db
            if insert_row(conn, entry):
                ok += 1
                status = "✅ 新增"
            else:
                fail += 1
                status = "⏭ 写入失败"

        conn.commit()

        display_date = item.get('date','') or (locals().get('pub_date') or '')
        if i % 5 == 0 or i == len(items):
            print(f"  [{i}/{len(items)}] {display_date} {item['title'][:40]}... {status}")
        else:
            print(f"  {display_date} {item['title'][:40]}... {status}")

    conn.close()
    print(f"  ✅ 完成: 新增{ok}, 跳过{fail}")
    return ok, fail


if __name__ == "__main__":
    import argparse
    parser = argparse.ArgumentParser(description=f"爬虫: {SITE_NAME}")
    parser.add_argument("--limit", type=int, help="测试：只跑 N 条")
    parser.add_argument("--stats", action="store_true", help="查看 search.db 统计")
    args = parser.parse_args()

    if args.stats:
        conn = get_db()
        total = conn.execute("SELECT COUNT(*) FROM gov_raw").fetchone()[0]
        site_count = conn.execute(
            "SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (SITE_NAME,)
        ).fetchone()[0]
        print(f"📊 search.db (gov_raw): 共 {total} 条, 其中 '{SITE_NAME}' {site_count} 条")
        conn.close()
        sys.exit(0)

    print(f"\n📡 [{SITE_NAME}] 开始爬取")
    t0 = time.time()
    crawl(max_items=args.limit)
    print(f"⏱ 耗时: {time.time()-t0:.1f}s\n")