#!/usr/bin/env python3
"""
crawl_debaoenv.py — 广东德宝环境技术研究有限公司 公示信息爬虫
============================================================
站点: https://www.debaoenv.com/Downs.aspx
内容: 环评/验收等公示信息，正文为PDF附件，不提取PDF文本，
      只记录标题 + PDF链接（可点击）。

模式:
  - 列表页: HTML分页 (Downs.aspx, Downs.aspx?page=N)
  - 详情页: Down.aspx?id=N → iframe内嵌PDF链接
  - 存储: 标题 + PDF URL内嵌到content/summary

用法:
  python3 crawl_debaoenv.py           # 增量爬 (第1页)
  python3 crawl_debaoenv.py --full    # 全量爬所有页
  python3 crawl_debaoenv.py --sync    # 仅同步到服务器
"""
import os, re, sys, time, sqlite3, subprocess, urllib.request, ssl
from urllib.parse import urljoin

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "debaoenv_results.db")
SITE_NAME = "德宝环境"
DOMAIN = "www.debaoenv.com"
SERVER_SSH = "root@1.94.217.116"
SERVER_SEARCH_DB = "/root/search.db"
CTX = ssl._create_unverified_context()
HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/125.0.0.0 Safari/537.36",
}

stats = {"new": 0, "skip": 0, "errors": 0}


def http_get(url, timeout=25):
    try:
        req = urllib.request.Request(url, headers=HEADERS)
        resp = urllib.request.urlopen(req, timeout=timeout, context=CTX)
        return resp.read().decode("utf-8", errors="replace")
    except Exception:
        return None


def init_db():
    conn = sqlite3.connect(DB_PATH, timeout=60)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("""CREATE TABLE IF NOT EXISTS crawl_results (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        site_name TEXT DEFAULT '德宝环境',
        title TEXT,
        url TEXT UNIQUE,
        content TEXT,
        publish_date TEXT,
        summary TEXT,
        crawled_at TEXT DEFAULT (datetime('now','localtime'))
    )""")
    conn.commit()
    conn.close()


def store_record(title, url, content, date, summary):
    conn = sqlite3.connect(DB_PATH, timeout=60)
    try:
        conn.execute(
            "INSERT OR IGNORE INTO crawl_results "
            "(title, url, content, publish_date, summary) "
            "VALUES (?,?,?,?,?)",
            (title, url, content, date, summary),
        )
        conn.commit()
        if conn.total_changes > 0:
            stats["new"] += 1
        else:
            stats["skip"] += 1
    except:
        stats["errors"] += 1
    finally:
        conn.close()


def sync_to_server():
    """将本地数据直接写入 search.db（服务器本地模式）"""
    print("\n📤 同步到 search.db...")

    conn = sqlite3.connect(DB_PATH, timeout=60)
    rows = conn.execute("SELECT title, url, content, publish_date, summary FROM crawl_results ORDER BY id").fetchall()
    conn.close()

    if not rows:
        print("  本地没有数据")
        return

    dst = sqlite3.connect("/root/search.db", timeout=60)
    dst.execute("PRAGMA journal_mode=WAL")

    site_name = "德宝环境"
    new_count = 0
    for r in rows:
        title, url, content, pub_date, summary = r
        try:
            dst.execute(
                "INSERT OR IGNORE INTO gov_raw "
                "(title, page_url, content, publish_date, summary, site_name, tags) "
                "VALUES (?,?,?,?,?,?,?)",
                (title, url, (content or "")[:500000], pub_date or "",
                 (summary or "")[:300], site_name, "")
            )
            if dst.total_changes > 0:
                new_count += 1
        except Exception as e:
            print(f"  Error: {e}")

    if new_count > 0:
        dst.commit()
        # Update FTS
        dst.execute(
            "INSERT OR REPLACE INTO gov_search(rowid,title,site_name,summary) "
            "SELECT r.id,r.title,r.site_name,r.summary FROM gov_raw r "
            "WHERE r.id NOT IN (SELECT rowid FROM gov_search) AND r.site_name=?",
            (site_name,))
        dst.commit()

    total = dst.execute(
        "SELECT COUNT(*) FROM gov_raw WHERE site_name=?", (site_name,)).fetchone()[0]
    dst.close()

    print(f"  OK {new_count}/{len(rows)} 条同步到 search.db (DB共{total}条)")


def fetch_list_page(page=1):
    """获取列表页，返回 [{'title','url','date'}]"""
    url = f"https://{DOMAIN}/Downs.aspx" if page == 1 else f"https://{DOMAIN}/Downs.aspx?page={page}"
    html = http_get(url)
    if not html:
        print(f"  ⚠️ 列表页{page}加载失败")
        return []

    items = []
    for m in re.finditer(
        r'<a href="(/Down\.aspx\?id=\d+)"[^>]*title="([^"]*)"[^>]*>([^<]+)</a>',
        html
    ):
        href = m.group(1).strip()
        title = m.group(2).strip() or m.group(3).strip()
        full_url = f"https://{DOMAIN}{href}"
        items.append({"url": full_url, "title": title})

    dates = re.findall(r'发布日期：(\d{4}-\d{2}-\d{2})', html)
    for i, item in enumerate(items):
        item["date"] = dates[i] if i < len(dates) else ""

    return items


def fetch_detail(detail_url):
    """抓取详情页，提取标题+PDF URL（不下载PDF）"""
    html = http_get(detail_url)
    if not html:
        return None

    # 标题
    title = ""
    tm = re.search(r'<title>(.*?)</title>', html)
    if tm:
        title = tm.group(1).strip()

    # PDF URL
    pdf_url = ""
    pm = re.search(r'<iframe[^>]*src="([^"]+\.pdf)"', html)
    if pm:
        pdf_rel = pm.group(1).strip()
        pdf_url = urljoin(f"https://{DOMAIN}", pdf_rel)

    # 日期
    date = ""
    dm = re.search(r'发布日期[：:]\s*(\d{4}-\d{2}-\d{2})', html)
    if dm:
        date = dm.group(1)

    # content = 标题 + PDF可点击链接
    pdf_name = pdf_url.split("/")[-1] if pdf_url else ""
    if pdf_url:
        content = f'<p><a href="{pdf_url}" target="_blank">📄 下载PDF附件: {pdf_name}</a></p>'
    else:
        content = f"<p>{title}</p>"

    summary = f"{title}\nPDF: {pdf_url}" if pdf_url else title

    return {
        "title": title or "",
        "content": content,
        "pdf_url": pdf_url,
        "date": date,
        "summary": summary,
    }


def main():
    import argparse
    parser = argparse.ArgumentParser()
    parser.add_argument("--full", action="store_true", help="全量")
    parser.add_argument("--pages", type=int, default=1, help="页数")
    parser.add_argument("--sync", action="store_true", help="仅同步")
    parser.add_argument("--test", type=int, default=0, help="测试N条")
    args = parser.parse_args()

    init_db()

    if args.sync:
        sync_to_server()
        return

    max_pages = 9999 if args.full else args.pages
    start_time = time.time()

    total_new = 0
    for page in range(1, max_pages + 1):
        items = fetch_list_page(page)
        if not items:
            print(f"⏹ 第{page}页无数据，结束")
            break

        print(f"\n📃 第{page}页 ({len(items)}条)")

        for item in items:
            if args.test and total_new >= args.test:
                break

            conn = sqlite3.connect(DB_PATH, timeout=60)
            exists = conn.execute(
                "SELECT 1 FROM crawl_results WHERE url=?", (item["url"],)
            ).fetchone()
            conn.close()
            if exists:
                stats["skip"] += 1
                continue

            print(f"  📄 {item['title'][:35]}...", end="", flush=True)
            detail = fetch_detail(item["url"])

            if detail is None:
                print(" ✗ 详情页失败")
                stats["errors"] += 1
                continue

            store_record(
                detail["title"] or item["title"],
                item["url"],
                detail["content"],
                detail.get("date") or item.get("date", ""),
                detail["summary"],
            )

            if stats["new"] > 0:
                total_new += 1
                pdf_name = detail.get("pdf_url", "").split("/")[-1] if detail.get("pdf_url") else "无PDF"
                print(f" ✅ {pdf_name}")
            else:
                print(" ⏭️ 重复")

            time.sleep(0.3)

    elapsed = time.time() - start_time
    print(f"\n{'='*50}")
    print(f"🏁 完成! 新增:{stats['new']} 跳过:{stats['skip']} 错误:{stats['errors']}")
    print(f"⏱️ {elapsed:.0f}s")

    if stats["new"] > 0:
        sync_to_server()


if __name__ == "__main__":
    main()