#!/usr/bin/env python3
"""
spider_unified.py — 统一列表蜘蛛 v2
==================================
只爬列表页的标题/URL/日期，不做详情页。
一个蜘蛛处理所有站点，支持：
  - 自定义 selector（来自 sites_cache.json）
  - 自动 data-attr 推断
  - 启发式通用选择器
  - 三规则过滤：5页/标题含"项目"/3年内

用法:
  python3 spider_unified.py                  # 批量跑所有站点（第一页）
  python3 spider_unified.py --pages=5        # 批量跑所有站点（最多5页）
  python3 spider_unified.py --url <url>      # 跑单个站点
  python3 spider_unified.py --snapshot       # 只看数据库统计，不跑
  python3 spider_unified.py --limit=100      # 只跑前100站
"""

import json, os, re, sys, hashlib, time, sqlite3, warnings, argparse
from datetime import datetime, timezone, timedelta
from urllib.parse import urljoin, urlparse
from concurrent.futures import ThreadPoolExecutor, as_completed

import requests
from parsel import Selector

warnings.filterwarnings("ignore", category=requests.packages.urllib3.exceptions.InsecureRequestWarning)

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "list_crawl.db")
CONFIG_PATH = os.path.join(BASE_DIR, "sites_cache.json")
THREE_YEARS_AGO = (datetime.now(timezone.utc) - timedelta(days=365*3)).strftime("%Y-%m-%d")

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/125.0.0.0 Safari/537.36",
}

# ═══════════════════════════════════════════
#  选择器 & 日期提取
# ═══════════════════════════════════════════
GENERIC_TITLE_SELECTORS = [
    "h1 a", "h2 a", "h3 a", "h4 a",
    "li a", "td a",
    ".title a", ".list a",
    ".news-title a", ".article-title a",
    "article a", ".item a",
]

SEMANTIC_DATE_SELECTORS = [
    "time::attr(datetime)", "time::text",
    "[class*=date]::text", "[class*=Date]::text",
    "[class*=time]::text", "[class*=Time]::text",
    "[class*=pub]::text", "[class*=riqi]::text", "[class*=sj]::text",
    "span.date::text", "em.date::text", "p.date::text",
]

WEAK_DATE_SELECTORS = ["span::text", "em::text", "i::text"]

DATE_RE = re.compile(
    r'(\d{4})[年\-/\.](\d{1,2})[月\-/\.](\d{1,2})'
    r'|(?<!\d)(\d{4})(\d{2})(\d{2})(?!\d)'
)

ONCLICK_RE = re.compile(r"""location\.href\s*=\s*['"]([^'"]+)['"]""")
DATA_ATTR_URL_RE = re.compile(r'data-url\s*=\s*["\']([^"\']+)["\']')
DATA_TITLE_RE = re.compile(r'data-title\s*=\s*["\']([^"\']+)["\']')
DATA_TIME_RE = re.compile(r'data-time\s*=\s*["\']([^"\']+)["\']')


def parse_date_str(raw: str):
    if not raw: return None
    m = DATE_RE.search(raw.strip())
    if not m: return None
    if m.group(1):
        y, mo, d = int(m.group(1)), int(m.group(2)), int(m.group(3))
    else:
        y, mo, d = int(m.group(4)), int(m.group(5)), int(m.group(6))
    if not (1990 <= y <= 2099 and 1 <= mo <= 12 and 1 <= d <= 31):
        return None
    return f"{y:04d}-{mo:02d}-{d:02d}"


def auto_selector(url: str) -> str:
    """自动推断列表页的链接选择器"""
    if re.search(r'list-(\d+)-\d+', url):
        m = re.search(r'list-(\d+)-\d+', url)
        return f'a[href*="content-{m.group(1)}"]'
    if '/art/' in url: return 'a[href*="/art/"]'
    if url.endswith('.shtml') or '/shtml/' in url: return 'a[href$=".shtml"]'
    if url.endswith('.jhtml') or 'jhtml' in url: return 'a[href$=".jhtml"]'
    if '/info/' in url: return 'a[href*="/info/"]'
    if 'content_' in url or re.search(r'/\d+/index\.htm', url):
        return 'a[href*="content_"]'
    m2 = re.search(r'/(xxgk|zwgk|news|gk)/[^/]*', url)
    if m2: return f'a[href*="/{m2.group(1)}/"]'
    m3 = re.search(r'/(\d+)/index\.s?html?$', url)
    if m3: return f'a[href*="/{m3.group(1)}/"]'
    m4 = re.search(r'/(\d+)/default\.s?html?$', url)
    if m4: return f'a[href*="/info/"], a[href*="content_"], a[href*="/{m4.group(1)}/"]'
    return ""


def _extract_link_date(sel) -> str:
    """从 parsel 选择器提取日期"""
    container = sel
    for xp in ('ancestor::li[1]', 'ancestor::tr[1]', 'ancestor::dd[1]',
               'ancestor::dt[1]', 'ancestor::article[1]'):
        node = sel.xpath(xp)
        if node: container = node; break
    if container == sel:
        parent = sel.xpath('..')
        container = parent if parent else sel

    for s in SEMANTIC_DATE_SELECTORS:
        for txt in container.css(s).getall():
            d = parse_date_str(txt)
            if d: return d
    for s in WEAK_DATE_SELECTORS:
        for txt in container.css(s).getall():
            t = (txt or "").strip()
            if 0 < len(t) <= 40:
                d = parse_date_str(t)
                if d: return d
    for xp in ('following-sibling::*', 'preceding-sibling::*'):
        for sib in sel.xpath(xp):
            for s in SEMANTIC_DATE_SELECTORS:
                for txt in sib.css(s).getall():
                    d = parse_date_str(txt)
                    if d: return d
    for el in container.css("p, div, span, td, li, em"):
        txt = " ".join(el.css("::text").getall())
        if ("日期" in txt or "时间" in txt or "发布" in txt) and len(txt) <= 60:
            d = parse_date_str(txt)
            if d: return d
    outer = container.xpath('..')
    if outer:
        outer_txt = " ".join(outer.css("::text").getall())
        if len(outer_txt) <= 400:
            for s in SEMANTIC_DATE_SELECTORS:
                for txt in outer.css(s).getall():
                    d = parse_date_str(txt)
                    if d: return d
    return ""


def extract_data_attr_items(html: str, base_url: str):
    """提取 data-url / data-title 属性模式"""
    items = []
    for m in re.finditer(
        r'<(\w+)[^>]*data-url\s*=\s*["\']([^"\']+)["\'][^>]*data-title\s*=\s*["\']([^"\']+)["\'][^>]*data-time\s*=\s*["\']([^"\']+)["\'][^>]*>(.*?)</\1>',
        html, re.DOTALL
    ):
        href, title, date_str = m.group(2).strip(), m.group(3).strip(), m.group(4).strip()
        if href and title:
            if not href.startswith('http'): href = urljoin(base_url, href)
            items.append({"title": title, "url": href, "pub_date": parse_date_str(date_str) or ""})
    if len(items) >= 3: return items

    items2 = []
    for m in re.finditer(
        r'<(\w+)[^>]*data-url\s*=\s*["\']([^"\']+)["\'][^>]*data-title\s*=\s*["\']([^"\']+)["\'][^>]*>(.*?)</\1>',
        html, re.DOTALL
    ):
        href, title = m.group(2).strip(), m.group(3).strip()
        if href and title and 5 < len(title) < 150:
            block = m.group(0)
            date_str = ""
            dm = DATA_TIME_RE.search(block)
            if dm: date_str = parse_date_str(dm.group(1)) or ""
            if not href.startswith('http'): href = urljoin(base_url, href)
            items2.append({"title": title, "url": href, "pub_date": date_str})
    return items2 if len(items2) >= 3 else items


def extract_css_items(html: str, url: str, css_selector: str = ""):
    """从CSS选择器提取列表条目"""
    sel = Selector(text=html)
    items = []

    if css_selector:
        # 自定义选择器
        links = sel.css(css_selector)
        for a in links:
            text = " ".join(a.css("::text").getall()).strip()
            href = a.attrib.get("href", "")
            if 5 < len(text) < 150 and href:
                pub_date = _extract_link_date(a)
                items.append({"title": text, "url": urljoin(url, href), "pub_date": pub_date})
        if len(items) >= 3: return items, "custom"

    # data-attr
    items = extract_data_attr_items(html, url)
    if items: return items, "data_attr"

    # auto_selector
    auto_sel = auto_selector(url)
    if auto_sel:
        links = sel.css(auto_sel)
        for a in links:
            text = " ".join(a.css("::text").getall()).strip()
            href = a.attrib.get("href", "")
            if 5 < len(text) < 150 and href:
                pub_date = _extract_link_date(a)
                items.append({"title": text, "url": urljoin(url, href), "pub_date": pub_date})
        if len(items) >= 3: return items, "auto"

    # onclick
    for el in sel.css("[onclick]"):
        onclick = el.attrib.get("onclick", "")
        m = ONCLICK_RE.search(onclick)
        if not m: continue
        href = m.group(1).strip()
        if not href or href.startswith("javascript"): continue
        text = re.sub(r'\s+', ' ', " ".join(el.css("::text").getall()).strip()).strip()
        if 5 <= len(text) <= 300:
            pub_date = _extract_link_date(el)
            items.append({"title": text, "url": urljoin(url, href), "pub_date": pub_date})
    if len(items) >= 3: return items, "onclick"

    # generic
    for css_sel in GENERIC_TITLE_SELECTORS:
        links = sel.css(css_sel)
        if not links: continue
        candidates = []
        for a in links:
            text = " ".join(a.css("::text").getall()).strip()
            href = a.attrib.get("href", "")
            if 5 < len(text) < 150 and href:
                pub_date = _extract_link_date(a)
                candidates.append((text, urljoin(url, href), pub_date))
        if len(candidates) >= 2:
            for text, href, pub_date in candidates:
                items.append({"title": text, "url": href, "pub_date": pub_date})
            break
        if len(candidates) >= 1:
            for text, href, pub_date in candidates:
                items.append({"title": text, "url": href, "pub_date": pub_date})
            break
    return items, "generic"


def filter_items(items, page_date=""):
    """三规则过滤：标题含"项目" + 3年内"""
    filtered = []
    for item in items:
        if "项目" not in item["title"]: continue
        date_str = item["pub_date"] or page_date
        if date_str and date_str < THREE_YEARS_AGO: continue
        filtered.append(item)
    return filtered


def fetch_page(url, timeout=20):
    try:
        r = requests.get(url, headers=HEADERS, timeout=timeout, verify=False)
        r.encoding = "utf-8"
        return r.text if r.status_code == 200 else None
    except:
        return None


def extract_page_date(html):
    sel = Selector(text=html)
    for s in SEMANTIC_DATE_SELECTORS:
        val = sel.css(s).get("")
        d = parse_date_str(val.strip() if val else "")
        if d: return d
    return ""


def crawl_site(site_cfg, max_pages=5):
    """爬取单个站点"""
    url = site_cfg["url"]
    name = site_cfg.get("name", url)
    css_selector = site_cfg.get("selector", "").strip()
    site_key = hashlib.md5(url.encode()).hexdigest()[:12]

    page_url = url
    all_items = []
    seen_urls = set()
    first_page_date = ""
    method_used = "none"

    for page in range(1, max_pages + 1):
        if page > 1:
            page_url = _build_page_url(url, page)
            if not page_url or page_url in seen_urls:
                break
        seen_urls.add(page_url)

        html = fetch_page(page_url)
        if not html:
            break

        items, method = extract_css_items(html, page_url, css_selector)
        if page == 1:
            method_used = method
            first_page_date = extract_page_date(html)

        if not items:
            break

        new_items = [i for i in items if i["url"] not in seen_urls]
        for i in items:
            seen_urls.add(i["url"])

        if not new_items:
            break

        all_items.extend(new_items)
        if page == 1:
            dates = [i["pub_date"] for i in new_items if i["pub_date"]]
            if dates:
                oldest = min(dates)
                if oldest < THREE_YEARS_AGO:
                    break

    filtered = filter_items(all_items, first_page_date)
    return {
        "key": site_key,
        "name": name,
        "url": url,
        "total": len(all_items),
        "filtered": len(filtered),
        "method": method_used,
        "items": filtered,
    }


def _build_page_url(base_url, page):
    """翻页URL构造（简单模式）"""
    if page == 1: return base_url
    for pat in [
        (r'(.*?/)(index|default|list)\.s?html?$', lambda m, n: f"{m.group(1)}index_{n}.html"),
        (r'(.*?/)(index|default|list)\.s?html?$', lambda m, n: f"{m.group(1)}{m.group(2)}_{n}.shtml"),
    ]:
        m = re.match(pat[0], base_url)
        if m: return pat[1](m, page)
    return None


# ═══════════════════════════════════════════
#  数据库
# ═══════════════════════════════════════════
def init_db():
    conn = sqlite3.connect(DB_PATH)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS sites (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            site_key TEXT UNIQUE,
            name TEXT,
            url TEXT,
            selector TEXT DEFAULT '',
            method TEXT DEFAULT '',
            status TEXT DEFAULT 'pending',
            last_crawled TEXT,
            total_items INTEGER DEFAULT 0,
            project_items INTEGER DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            site_key TEXT,
            site_name TEXT,
            title TEXT,
            url TEXT UNIQUE,
            pub_date TEXT,
            content TEXT DEFAULT '',
            status TEXT DEFAULT 'list_only',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS crawl_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            site_key TEXT,
            site_name TEXT,
            method TEXT,
            total INTEGER,
            filtered INTEGER,
            duration_sec REAL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_items_site_key ON items(site_key)
    """)
    conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_items_status ON items(status)
    """)
    conn.commit()
    return conn


def import_sites_from_config():
    """从 sites_cache.json 导入站点列表到 sites 表"""
    conn = init_db()
    cur = conn.execute("SELECT COUNT(*) FROM sites")
    count = cur.fetchone()[0]
    if count > 0:
        print(f"  sites 表已有 {count} 个站点，跳过导入")
        conn.close()
        return

    with open(CONFIG_PATH, encoding="utf-8") as f:
        data = json.load(f)

    sites = data.get("sites", [])
    imported = 0
    for s in sites:
        url = s.get("url", "").strip()
        if not url:
            continue
        site_key = hashlib.md5(url.encode()).hexdigest()[:12]
        try:
            conn.execute(
                "INSERT OR IGNORE INTO sites (site_key, name, url, selector) VALUES (?, ?, ?, ?)",
                (site_key, s.get("name", url)[:200], url, s.get("selector", ""))
            )
            if conn.total_changes > 0:
                imported += 1
        except:
            pass
    conn.commit()
    conn.close()
    print(f"  ✅ 导入 {imported} 个站点到数据库")


def save_results(result, duration):
    """保存爬取结果到数据库"""
    conn = sqlite3.connect(DB_PATH)

    # 更新站点状态
    conn.execute(
        "UPDATE sites SET method=?, status=?, last_crawled=CURRENT_TIMESTAMP, total_items=?, project_items=? WHERE site_key=?",
        (result["method"], "done" if result["filtered"] > 0 else "empty",
         result["total"], result["filtered"], result["key"])
    )

    # 写入条目
    new_count = 0
    for item in result["items"]:
        try:
            conn.execute(
                "INSERT OR IGNORE INTO items (site_key, site_name, title, url, pub_date) VALUES (?, ?, ?, ?, ?)",
                (result["key"], result["name"], item["title"], item["url"], item.get("pub_date", ""))
            )
            if conn.total_changes > 0:
                new_count += 1
        except:
            pass

    # 写日志
    conn.execute(
        "INSERT INTO crawl_log (site_key, site_name, method, total, filtered, duration_sec) VALUES (?, ?, ?, ?, ?, ?)",
        (result["key"], result["name"], result["method"], result["total"], result["filtered"], duration)
    )
    conn.commit()
    conn.close()
    return new_count


def print_snapshot():
    """打印数据库快照"""
    conn = init_db()
    
    total_sites = conn.execute("SELECT COUNT(*) FROM sites").fetchone()[0]
    total_items = conn.execute("SELECT COUNT(*) FROM items").fetchone()[0]
    project_items = conn.execute("SELECT COUNT(*) FROM items WHERE title LIKE '%项目%'").fetchone()[0]
    done_sites = conn.execute("SELECT COUNT(*) FROM sites WHERE status='done'").fetchone()[0]
    empty_sites = conn.execute("SELECT COUNT(*) FROM sites WHERE status='empty'").fetchone()[0]
    pending_sites = conn.execute("SELECT COUNT(*) FROM sites WHERE status='pending'").fetchone()[0]
    
    print(f"\n{'='*50}")
    print(f"  📊 list_crawl.db 快照")
    print(f"{'='*50}")
    print(f"  站点总数: {total_sites}")
    print(f"  已爬取:   {done_sites}")
    print(f"  空站:     {empty_sites}")
    print(f"  未爬:     {pending_sites}")
    print(f"  列表条目: {total_items}")
    print(f"  含\"项目\": {project_items}")
    print(f"{'='*50}")
    
    if done_sites > 0:
        print(f"\n  各站数据量排行:")
        rows = conn.execute(
            "SELECT name, total_items, project_items, method FROM sites WHERE status='done' ORDER BY project_items DESC LIMIT 20"
        ).fetchall()
        for r in rows:
            print(f"    {r[0][:50]:50s} 总{r[1]:4d} 项目{r[2]:3d} [{r[3]}]")
    
    conn.close()


# ═══════════════════════════════════════════
#  Main
# ═══════════════════════════════════════════
def main():
    parser = argparse.ArgumentParser(description="统一列表蜘蛛 v2")
    parser.add_argument("--url", help="单个URL")
    parser.add_argument("--name", help="站点名（配合--url使用）")
    parser.add_argument("--selector", help="CSS选择器（配合--url使用）")
    parser.add_argument("--pages", type=int, default=1, help="最大翻页数")
    parser.add_argument("--limit", type=int, default=0, help="只跑前N站")
    parser.add_argument("--snapshot", action="store_true", help="只看数据库统计")
    parser.add_argument("--import-sites", action="store_true", help="导入站点配置到数据库")
    parser.add_argument("--batch", action="store_true", help="批量跑所有站点")
    parser.add_argument("--resume", action="store_true", help="继续跑 pending 站点")
    parser.add_argument("--file", help="URL列表文件（仅跑文件中匹配的站点）")
    parser.add_argument("--workers", type=int, default=5, help="并发数")
    args = parser.parse_args()

    if args.snapshot:
        print_snapshot()
        return

    if args.import_sites:
        import_sites_from_config()
        print_snapshot()
        return

    # 单个URL
    if args.url:
        site_cfg = {"url": args.url, "name": args.name or args.url, "selector": args.selector or ""}
        t0 = time.time()
        result = crawl_site(site_cfg, args.pages)
        duration = time.time() - t0
        new = save_results(result, duration)

        print(f"\n{'='*50}")
        print(f"  📍 {result['name']}")
        print(f"  URL: {result['url']}")
        print(f"  方法: {result['method']}")
        print(f"  耗时: {duration:.1f}s")
        print(f"{'='*50}")
        print(f"  原始: {result['total']} 条")
        print(f"  含\"项目\": {result['filtered']} 条")
        print(f"  入库新增: {new} 条")
        if result["items"]:
            print(f"\n  前10条:")
            for i, item in enumerate(result["items"][:10], 1):
                print(f"    {i:2d}. [{item['pub_date'] or '???'}] {item['title'][:65]}")
        return

    # 批量
    if args.batch or args.resume:
        conn = sqlite3.connect(DB_PATH)
        # 当指定了--file时，先全部加载再过滤
        if args.file:
            rows = conn.execute("SELECT site_key, name, url, selector FROM sites ORDER BY id").fetchall()
        elif args.resume:
            rows = conn.execute("SELECT site_key, name, url, selector FROM sites WHERE status='pending' ORDER BY id LIMIT ?",
                                (args.limit or 999999,)).fetchall()
        else:
            rows = conn.execute("SELECT site_key, name, url, selector FROM sites ORDER BY id LIMIT ?",
                                (args.limit or 999999,)).fetchall()
        conn.close()

        # 如果指定了 --file（URL列表文件），只跑文件中匹配的站点
        url_filter = set()
        if args.file:
            with open(args.file) as f:
                for line in f:
                    line = line.strip()
                    if '\t' in line:
                        url_filter.add(line.split('\t')[1].strip().rstrip('/'))
                    elif not line.startswith('#') and line:
                        url_filter.add(line.rstrip('/'))
            filtered_rows = [r for r in rows if r[2].strip().rstrip('/') in url_filter]
            print(f"  📋 文件匹配: {len(filtered_rows)}/{len(rows)} 个站点")
            rows = filtered_rows
            # 应用limit到匹配后的结果
            if args.limit and len(rows) > args.limit:
                rows = rows[:args.limit]

        print(f"\n📋 批量爬取: {len(rows)} 个站点 (并发={args.workers})")
        
        def crawl_one(row):
            site_cfg = {"url": row[2], "name": row[1], "selector": row[3] or ""}
            t0 = time.time()
            result = crawl_site(site_cfg, args.pages)
            duration = time.time() - t0
            new = save_results(result, duration)
            return result, duration, new

        ok, empty, fail = 0, 0, 0
        t_start = time.time()

        with ThreadPoolExecutor(max_workers=args.workers) as executor:
            futures = {executor.submit(crawl_one, row): row for row in rows}
            for i, future in enumerate(as_completed(futures), 1):
                row = futures[future]
                try:
                    result, duration, new = future.result()
                    if result["filtered"] > 0:
                        ok += 1
                        tag = f"✅ {result['filtered']}条/{result['total']}条 [{result['method']}]"
                    elif result["total"] > 0:
                        empty += 1
                        tag = f"📭 无项目/{result['total']}条 [{result['method']}]"
                    else:
                        empty += 1
                        tag = f"⬜ 0条 [{result['method']}]"
                    print(f"  [{i:4d}/{len(rows)}] {tag} {row[1][:40]:40s} {duration:.1f}s")
                except Exception as e:
                    fail += 1
                    print(f"  [{i:4d}/{len(rows)}] ❌ {str(e)[:50]} {row[1][:40]}")

        total_time = time.time() - t_start
        print(f"\n  📊 完成: ✅ {ok} / 📭 {empty} / ❌ {fail}  耗时 {total_time:.0f}s")
        print_snapshot()
        return

    parser.print_help()


if __name__ == "__main__":
    main()