#!/usr/bin/env python3
"""


crawl_eiacloud.py — 环评云(www.eiacloud.com) 多栏目爬虫
========================================================
站点: https://www.eiacloud.com/gs/list/1 (环评报告公示)
反爬: JS渲染分页，API POST /gs/gkPublishList
列表: API返回JSON，15条/页
详情: 静态HTML，正文在 div#contentContainer.detail_text

多栏目支持: 报告公示(id=1) / 验收报告(id=2) / 环评报告(id=3)
           水土保持(id=4) / 土壤污染(id=5) / 竣工调试(id=6)

用法:
  python3 crawl_eiacloud.py           # 增量爬(所有栏目)
  python3 crawl_eiacloud.py --full    # 全量爬(仅栏目1)
  python3 crawl_eiacloud.py --plate 3 # 只爬指定栏目
  python3 crawl_eiacloud.py --sync    # 仅同步

同步机制:
  - 服务器本地(/root/search.db 存在): 直接写入
  - Mac本地: 通过SSH推送到服务器 search.db
"""

import os, re, sys, time, json, sqlite3, subprocess
import requests
import warnings
import sys as _SYS
_MAX_PG = int(_SYS.argv[1]) if len(_SYS.argv) > 1 and _SYS.argv[1].isdigit() else None
if _MAX_PG is not None:
    print('[AutoPg] max_pages=' + str(_MAX_PG))
# END AUTO PAGES
warnings.filterwarnings('ignore')

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "eiacloud_results.db")
DOMAIN = "www.eiacloud.com"
SERVER_SSH = "root@1.94.217.116"
SERVER_SEARCH_DB = "/root/search.db"

API_URL = "https://www.eiacloud.com/gs/gkPublishList"
REQ_HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 Chrome/125.0.0.0 Safari/537.36",
    "X-Requested-With": "XMLHttpRequest",
    "Referer": "https://www.eiacloud.com/gs/list/1",
    "Content-Type": "application/x-www-form-urlencoded",
}
PAGE_SIZE = 15

# 栏目配置: {plate_id: (名称缩写, 增量页数, 全量总页数)}
PLATES = {
    1:  ("报告公示", 20, 4680),
    2:  ("验收报告", 5,  200),
    3:  ("环评报告", 5,  200),
    4:  ("水土保持", 3,  100),
    5:  ("土壤污染", 3,  100),
    6:  ("竣工调试", 3,  100),
    10: ("监测报告", 2,  50),
}

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


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


def add_plate_column():
    """给已有表加 plate_id 列"""
    conn = sqlite3.connect(DB_PATH)
    try:
        conn.execute("ALTER TABLE crawl_results ADD COLUMN plate_id TEXT DEFAULT '1'")
        conn.commit()
    except:
        pass
    conn.close()


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


def site_name_for(plate_id, plate_label):
    return f"环评云-{plate_label}"


def esc(v):
    if v is None: return "''"
    return "'" + str(v).replace("'", "''") + "'"


def is_running_on_server():
    return os.path.exists("/root/search.db")


def sync_to_server():
    """同步所有栏目的数据到服务器的 search.db"""
    print("\n📤 同步到 search.db...")

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

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

    if is_running_on_server():
        _sync_direct(rows)
    else:
        _sync_via_ssh(rows)


def _sync_direct(rows):
    dst = sqlite3.connect("/root/search.db")
    dst.execute("PRAGMA journal_mode=WAL")
    new_count = 0
    plate_map = {str(k): v[0] for k, v in PLATES.items()}

    for r in rows:
        title, url, content, pub_date, summary, pid = r
        sn = site_name_for(pid, plate_map.get(str(pid), "其他"))
        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], sn, "")
            )
            if dst.total_changes > 0:
                new_count += 1
        except Exception as e:
            print(f"  Error: {e}")

    if new_count > 0:
        dst.commit()
        # FTS
        for pid, sn in [(str(k), site_name_for(str(k), v[0])) for k, v in PLATES.items()]:
            dst.execute(
                "INSERT 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=?",
                (sn,))
        dst.commit()

    total = dst.execute(
        "SELECT COUNT(*) FROM gov_raw WHERE site_name LIKE '环评云%'"
    ).fetchone()[0]
    dst.close()
    print(f"  ✅ OK {new_count}/{len(rows)} 条同步到 search.db (DB共{total}条)")


def _sync_via_ssh(rows):
    import base64

    plate_map = {str(k): v[0] for k, v in PLATES.items()}

    # 查服务器已有URL
    all_site_names = "','".join(site_name_for(str(k), v[0]) for k, v in PLATES.items())
    check_cmd = (
        f"sqlite3 {SERVER_SEARCH_DB} "
        f"\"SELECT page_url FROM gov_raw WHERE site_name IN ('{all_site_names}')\""
    )
    try:
        r = subprocess.run(
            ['ssh', SERVER_SSH, check_cmd],
            capture_output=True, text=True, timeout=30
        )
        existing_urls = set(r.stdout.strip().split('\n')) if r.stdout.strip() else set()
    except:
        existing_urls = set()

    batch = []
    for r in rows:
        title, url, content, pub_date, summary, pid = r
        if url in existing_urls:
            continue
        sn = site_name_for(pid, plate_map.get(str(pid), "其他"))
        batch.append((title, url, (content or "")[:500000], pub_date or "",
                       (summary or "")[:300], sn, ""))

    if not batch:
        print(f"  ✅ 所有 {len(rows)} 条已在服务器中存在，无需同步")
        return

    print(f"  待同步 {len(batch)} 条...")

    BATCH_SIZE = 50
    pushed = 0
    for i in range(0, len(batch), BATCH_SIZE):
        chunk = batch[i:i + BATCH_SIZE]
        values_clause = ",\n".join(
            f"({esc(t)},{esc(u)},{esc(c)},{esc(d)},{esc(s)},{esc(sn)},{esc('')})"
            for t, u, c, d, s, sn, _ in chunk
        )
        sql = (
            f"ATTACH DATABASE '{SERVER_SEARCH_DB}' AS maindb;\n"
            f"INSERT OR IGNORE INTO maindb.gov_raw "
            f"(title, page_url, content, publish_date, summary, site_name, tags) "
            f"VALUES\n{values_clause};\n"
            f"DETACH DATABASE maindb;\n"
        )
        try:
            encoded = base64.b64encode(sql.encode()).decode()
            cmd = f'echo {encoded} | base64 -d | ssh {SERVER_SSH} "sqlite3 {SERVER_SEARCH_DB}"'
            r = subprocess.run(cmd, shell=True, capture_output=True, text=True, timeout=30)
            if r.returncode != 0:
                for item in chunk:
                    _sync_one_via_ssh(item)
            pushed += len(chunk)
            print(f"  ✅ 批{i//BATCH_SIZE+1}: {len(chunk)}条 (累计{pushed}/{len(batch)})")
        except:
            for item in chunk:
                _sync_one_via_ssh(item)
            pushed += len(chunk)

    # FTS
    for pid_label, sn in [(str(k), site_name_for(str(k), v[0])) for k, v in PLATES.items()]:
        fts_sql = (
            f"INSERT INTO gov_search(rowid,title,site_name,summary) "
            f"SELECT r.id,r.title,r.site_name,r.summary FROM gov_raw r "
            f"WHERE r.id NOT IN (SELECT rowid FROM gov_search) AND r.site_name='{sn}';"
        )
        try:
            subprocess.run(
                ['ssh', SERVER_SSH, f'sqlite3 {SERVER_SEARCH_DB} "{fts_sql}"'],
                capture_output=True, timeout=30
            )
        except:
            pass

    # 统计
    try:
        r = subprocess.run(
            ['ssh', SERVER_SSH,
             f"sqlite3 {SERVER_SEARCH_DB} \"SELECT COUNT(*) FROM gov_raw WHERE site_name LIKE '环评云%'\""],
            capture_output=True, text=True, timeout=10
        )
        total = r.stdout.strip()
        print(f"  📊 服务器 search.db 环评云系列共 {total} 条")
    except:
        pass


def _sync_one_via_ssh(item):
    import base64
    t, u, c, d, s, sn, _ = item
    sql = (
        f"INSERT OR IGNORE INTO gov_raw "
        f"(title, page_url, content, publish_date, summary, site_name, tags) "
        f"VALUES ({esc(t)},{esc(u)},{esc(c)},{esc(d)},{esc(s)},{esc(sn)},{esc('')});"
    )
    try:
        encoded = base64.b64encode(sql.encode()).decode()
        cmd = f'echo {encoded} | base64 -d | ssh {SERVER_SSH} "sqlite3 {SERVER_SEARCH_DB}"'
        subprocess.run(cmd, shell=True, capture_output=True, timeout=15)
    except:
        pass


def make_session():
    s = requests.Session()
    s.headers.update({"User-Agent": REQ_HEADERS["User-Agent"]})
    s.get("https://www.eiacloud.com/gs/list/1", timeout=30, verify=False)
    return s


def fetch_list_page(session, gk_plate_id, page_num):
    """调用API获取指定栏目的列表页"""
    data = {
        "gkPlateId": str(gk_plate_id),
        "pageModel.numberNo": str(page_num),
        "pageModel.numberSize": str(PAGE_SIZE),
    }
    try:
        r = session.post(API_URL, data=data, headers=REQ_HEADERS, timeout=30, verify=False)
        items = json.loads(r.text)
        result = []
        for item in items:
            item_id = item.get("id", "")
            title = item.get("title", "").strip()
            if not title or not item_id:
                continue
            if item_id == "21010yzwak":
                continue
            ts = item.get("createDate", 0)
            if ts:
                date = time.strftime("%Y-%m-%d", time.localtime(ts / 1000))
            else:
                date = item.get("readableCreateDate", "")
            result.append({
                "url": f"https://{DOMAIN}/gs/detail/{gk_plate_id}?id={item_id}",
                "title": title,
                "date": date,
                "id": item_id,
            })
        return result
    except Exception as e:
        print(f"  ⚠️ API错误(栏目{gk_plate_id}第{page_num}页): {e}")
        return None


def extract_content_container(html):
    idx = html.find('id="contentContainer"')
    if idx == -1:
        return ""
    gt = html.find('>', idx)
    if gt == -1:
        return ""
    inner_start = gt + 1
    depth = 0
    i = inner_start
    while i < len(html):
        if html[i:i+4] == '<div':
            ci = html.find('>', i)
            if ci != -1 and html[ci-1] != '/':
                depth += 1
            i = ci + 1
        elif html[i:i+6] == '</div>' and i > 0:
            if depth == 0:
                return html[inner_start:i]
            depth -= 1
            i += 6
        else:
            i += 1
    return ""


def fetch_detail(session, detail_url, retry=2):
    for attempt in range(retry + 1):
        try:
            r = session.get(detail_url, timeout=30, verify=False)
            html = r.text
        except:
            if attempt < retry:
                time.sleep(2)
                continue
            return None

        if len(html) < 500:
            if attempt < retry:
                time.sleep(2)
                continue
            return None

        if 'exception.css' in html:
            return None

        title = ""
        tm = re.search(r'<title>(.*?)</title>', html, re.DOTALL)
        if tm:
            title = tm.group(1).strip()
        if not title:
            atm = re.search(r'<meta[^>]*name="ArticleTitle"[^>]*content="([^"]+)"', html, re.I)
            if atm:
                title = atm.group(1).strip()

        content_raw = extract_content_container(html)
        content = ""
        if content_raw:
            content_raw = re.sub(r'<script[^>]*>.*?</script>', '', content_raw, flags=re.DOTALL|re.I)
            content_raw = re.sub(r'<style[^>]*>.*?</style>', '', content_raw, flags=re.DOTALL|re.I)
            content = content_raw.strip()

        date = ""
        dm = re.search(r'发表于\s*(\d{4}-\d{2}-\d{2})', html)
        if dm:
            date = dm.group(1)

        attaches = []
        for am in re.finditer(r'<a[^>]*href="([^"]+\.(?:pdf|doc|docx|xls|xlsx|zip|rar))"[^>]*>([^<]+)</a>',
                               content or html, re.I):
            url = am.group(1).strip()
            name = am.group(2).strip()
            if url.startswith('http'):
                attaches.append(f'<a href="{url}" target="_blank">{name}</a>')
            else:
                attaches.append(f'<a href="https://{DOMAIN}{url}" target="_blank">{name}</a>')
        if attaches and content:
            content += '<br><br><strong>附件：</strong><br>' + '<br>'.join(attaches)

        summary = re.sub(r'<[^>]+>', '', content or '')
        summary = re.sub(r'\s+', ' ', summary).strip()[:500] if summary else title

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

    return None


def process_item(session, item, plate_id):
    conn = sqlite3.connect(DB_PATH)
    exists = conn.execute("SELECT 1 FROM crawl_results WHERE url=?", (item["url"],)).fetchone()
    conn.close()
    if exists:
        return False

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

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

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

    clen = len(detail.get("content", ""))
    print(f" ✅ {clen}B")
    return True


def crawl_plate(session, gk_plate_id, plate_label, max_pages):
    """爬取指定栏目"""
    print(f"\n{'='*50}")
    print(f"📂 栏目{gk_plate_id}: {plate_label} (共{max_pages}页)")
    print(f"{'='*50}")

    total_new = 0
    for page_num in range(1, min(max_pages, _MAX_PG or max_pages)+1):
        items = fetch_list_page(session, gk_plate_id, page_num)
        if items is None:
            print(f"  ❌ 栏目{gk_plate_id}第{page_num}页API失败，跳过该栏目")
            break
        if not items:
            print(f"  📭 第{page_num}页为空，结束")
            break

        print(f"📃 第{page_num}页 ({len(items)}条)")
        for item in items:
            if process_item(session, item, gk_plate_id):
                total_new += 1
            # 随机间隔1-3秒，防止反爬
            time.sleep(1 + abs(hash(item["url"])) % 3)

        # 每页间隔5-8秒
        if page_num < max_pages:
            page_delay = 5 + (page_num % 4)
            print(f"  ⏳ 等待{page_delay}s...")
            time.sleep(page_delay)

    print(f"  ✅ 栏目{gk_plate_id}完成: 新增{total_new}")

    # 每爬完一个栏目就同步一次（节省时间）
    if total_new > 0:
        sync_to_server()

    return total_new


def main():
    import argparse
    parser = argparse.ArgumentParser()
    parser.add_argument("--full", action="store_true", help="全量(仅栏目1)")
    parser.add_argument("--sync", action="store_true", help="仅同步")
    parser.add_argument("--test", type=int, default=0, help="测试N条")
    parser.add_argument("--plate", type=int, default=0, help="只爬指定栏目ID")
    parser.add_argument("--pages", type=int, default=0, help="限制每栏目最大页数")
    args = parser.parse_args()

    init_db()
    add_plate_column()

    if args.sync:
        sync_to_server()
        return

    start_time = time.time()
    session = make_session()

    plates_to_crawl = []
    if args.plate > 0:
        if args.plate in PLATES:
            label, inc_pages, full_pages = PLATES[args.plate]
            mp = full_pages if args.full else inc_pages
            plates_to_crawl = [(args.plate, label, mp)]
        else:
            print(f"❌ 未知栏目ID: {args.plate}")
            sys.exit(1)
    elif args.full:
        # 全量仅爬栏目1（太大）
        label, _, full_pages = PLATES[1]
        plates_to_crawl = [(1, label, full_pages)]
    else:
        for pid, (label, inc_pages, _) in PLATES.items():
            if args.pages > 0:
                inc_pages = min(inc_pages, args.pages)
            plates_to_crawl.append((pid, label, inc_pages))

    total_new = 0
    for pid, label, mp in plates_to_crawl:
        total_new += crawl_plate(session, pid, label, mp)

    elapsed = time.time() - start_time
    print(f"\n{'='*50}")
    print(f"🏁 总计: 新增{stats['new']} 跳过{stats['skip']} 错误{stats['errors']} (栏目数{len(plates_to_crawl)})")
    print(f"⏱️ {elapsed:.0f}s ({elapsed/60:.1f}min)")


if __name__ == "__main__":
    main()