#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Crawler for 清远市清城区-政务动态 (gkmlpt)
API: /qyqczsj/gkmlpt/api/all/3423 (list) + /api/post/{id} (detail)
"""
import requests, re, subprocess, sys, time, json
from bs4 import BeautifulSoup

DB_PATH = "/root/search.db"
SITE_NAME = "清远市清城区-政务动态"
INDUSTRY = "环评公示"
BASE_URL = "http://www.qingcheng.gov.cn"
LIST_API = BASE_URL + "/qyqczsj/gkmlpt/api/all/3423"
DETAIL_API = BASE_URL + "/qyqczsj/gkmlpt/api/post"

HEADERS = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36",
    "Accept": "application/json, text/plain, */*",
    "Accept-Language": "zh-CN,zh;q=0.9",
}
PER_PAGE = 46


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


def timestamp_to_date(ts):
    """Convert unix timestamp to YYYY-MM-DD"""
    import datetime
    try:
        return datetime.datetime.fromtimestamp(ts, tz=datetime.timezone(datetime.timedelta(hours=8))).strftime("%Y-%m-%d")
    except:
        return ""


def fetch_list(page):
    """Fetch list page from API"""
    url = "%s?page=%d&limit=%d" % (LIST_API, page, PER_PAGE)
    try:
        r = requests.get(url, headers=HEADERS, timeout=15)
        if r.status_code == 200:
            return r.json()
    except Exception as e:
        print("  [ERR] List page %d: %s" % (page, e), flush=True)
    return None


def fetch_detail(article_id):
    """Fetch detail from API"""
    url = "%s/%d" % (DETAIL_API, article_id)
    try:
        r = requests.get(url, headers=HEADERS, timeout=15)
        if r.status_code == 200:
            return r.json()
    except Exception as e:
        print("  [ERR] Detail %d: %s" % (article_id, e), flush=True)
    return None


def extract_content(html_content):
    """Extract clean text from HTML content field"""
    if not html_content:
        return ""
    soup = BeautifulSoup(html_content, 'html.parser')
    ps = soup.find_all('p')
    if ps:
        paras = []
        for p in ps:
            txt = p.get_text(separator='', strip=True)
            if txt:
                paras.append(txt)
        return '\n\n'.join(paras)
    return soup.get_text(separator='', strip=True)


def insert_one(page_url, title, content, pub_date):
    summary = (content or "")[:200]
    sql = """INSERT INTO gov_raw (page_url, title, publish_date, content, site_name, industry, summary)
VALUES ('%(u)s','%(t)s','%(d)s','%(c)s','%(s)s','%(i)s','%(sum)s')""" % {
        'u': esc(page_url), 't': esc(title), 'd': esc(pub_date),
        'c': esc(content), 's': SITE_NAME, 'i': INDUSTRY, 'sum': esc(summary)
    }
    result = subprocess.run(
        ["sqlite3", "-cmd", ".timeout 60000", DB_PATH, sql],
        capture_output=True, text=True, timeout=10,
    )
    if result.returncode != 0 and "UNIQUE" not in result.stderr:
        print("  [DB ERROR] %s" % result.stderr, file=sys.stderr)
        return False
    # FTS
    fts_sql = """INSERT OR REPLACE INTO gov_search(rowid, title, site_name, summary)
SELECT r.rowid, r.title, r.site_name, r.summary
FROM gov_raw r LEFT JOIN gov_search s ON r.rowid = s.rowid
WHERE r.page_url='%(u)s' AND s.rowid IS NULL""" % {'u': esc(page_url)}
    subprocess.run(["sqlite3", "-cmd", ".timeout 60000", DB_PATH, fts_sql], capture_output=True, text=True, timeout=30)
    return True


def crawl():
    max_pages = 5
    if len(sys.argv) > 1:
        try:
            max_pages = int(sys.argv[1])
        except ValueError:
            pass

    print("[%s] Starting crawl, max_pages=%d" % (SITE_NAME, max_pages), flush=True)

    all_articles = []
    for page in range(1, max_pages + 1):
        data = fetch_list(page)
        if not data or 'articles' not in data:
            break
        arts = data['articles']
        if not arts:
            break
        print("  Page %d: %d items" % (page, len(arts)), flush=True)
        all_articles.extend(arts)
        if len(arts) < PER_PAGE:
            break
        time.sleep(0.3)

    print("Total items: %d" % len(all_articles), flush=True)

    new_count = 0
    dup_count = 0
    for i, art in enumerate(all_articles):
        aid = art['id']
        title = art['title']
        print("  [%d/%d] %s..." % (i+1, len(all_articles), title[:40]), flush=True)

        # Get date from timestamp
        pub_date = ""
        if 'date' in art and art['date']:
            pub_date = timestamp_to_date(art['date'])
        if not pub_date and 'created_at' in art:
            m = re.search(r'(\d{4}-\d{2}-\d{2})', str(art['created_at']))
            if m:
                pub_date = m.group(1)

        # Fetch detail for content
        detail = fetch_detail(aid)
        if detail:
            content = extract_content(detail.get('content', ''))
            detail_title = detail.get('title', '') or title
            if not pub_date and 'publish_time' in detail:
                if detail['publish_time']:
                    m = re.search(r'(\d{4}-\d{2}-\d{2})', str(detail['publish_time']))
                    if m:
                        pub_date = m.group(1)
        else:
            content = title
            detail_title = title

        page_url = art.get('url', "%s/%d" % (DETAIL_API, aid))
        if insert_one(page_url, detail_title, content, pub_date):
            new_count += 1
        else:
            dup_count += 1
        time.sleep(0.2)

    print("\nDone! New: %d, Duplicates: %d" % (new_count, dup_count), flush=True)


if __name__ == "__main__":
    crawl()