#!/usr/bin/env python3
"""
青岛市生态环境局 - 建设项目环境影响评价文件受理情况公示 爬虫
列表页: ASP.NET WebForms (postback分页) + curl详情页
"""
import json, os, re, sqlite3, subprocess, sys, time, urllib.parse, urllib.request

SITE_NAME = "青岛市生态环境局"
LIST_URL = "http://mbee.qingdao.gov.cn:8082/m2/ZWGKNew/webgs/list1.aspx?m=357"
DB_PATH = os.path.expanduser("~/gov_crawler/qingdao_results.db")
SERVER_SSH = "root@1.94.217.116"
SERVER_SEARCH_DB = "/root/search.db"

def init_db():
    conn = sqlite3.connect(DB_PATH, timeout=60)
    conn.execute('''CREATE TABLE IF NOT EXISTS crawl_results (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT, url TEXT UNIQUE, content TEXT,
        publish_date TEXT, summary TEXT
    )''')
    conn.execute('DELETE FROM crawl_results')
    conn.commit()
    return conn

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

def fetch_page(url, post_data=None):
    """Fetch a page with optional POST data."""
    if post_data:
        body = urllib.parse.urlencode(post_data).encode()
        req = urllib.request.Request(url, data=body, headers={
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
            'Content-Type': 'application/x-www-form-urlencoded',
        })
    else:
        req = urllib.request.Request(url, headers={
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
        })
    resp = urllib.request.urlopen(req, timeout=20)
    return resp.read().decode('utf-8', 'ignore')

def extract_viewstate(html):
    """Extract ASP.NET hidden fields for postback."""
    vs = re.search(r'__VIEWSTATE.*?value="([^"]*)"', html)
    ev = re.search(r'__EVENTVALIDATION.*?value="([^"]*)"', html)
    vsg = re.search(r'__VIEWSTATEGENERATOR.*?value="([^"]*)"', html)
    return {
        '__VIEWSTATE': vs.group(1) if vs else '',
        '__EVENTVALIDATION': ev.group(1) if ev else '',
        '__VIEWSTATEGENERATOR': vsg.group(1) if vsg else '',
    }

def extract_links(html):
    """Extract project links from list page HTML."""
    # Find the table-list section - area between <div class="table-list"> and </tbody>
    area = re.search(r'<div class="table-list">(.*?)</tbody>', html, re.DOTALL)
    if not area:
        return []
    
    list_html = area.group(1)
    links = re.findall(
        r'<a[^>]*href="(/m2/webgs/list1detaile\.aspx\?n=[^"]+)"[^>]*>([^<]+)</a>',
        list_html
    )
    return [(title.strip(), "http://mbee.qingdao.gov.cn:8082" + href) for href, title in links]

def extract_content(html):
    """Extract content from the detail page table.
    Returns (content_html, summary_text) - content is HTML formatted."""
    content_div = re.search(
        r'<div class="table-detail[^>]*>(.*?)</div>\s*<div',
        html, re.DOTALL
    )
    if not content_div:
        return "", ""
    
    table_html = content_div.group(1)
    
    # Find all <tr> rows
    rows = re.findall(r'<tr>(.*?)</tr>', table_html, re.DOTALL)
    
    html_lines = []
    rowspan_label = ""  # track label from rowspan cell
    
    for row in rows:
        # Extract cells
        cells = re.findall(r'<td[^>]*>(.*?)</td>', row, re.DOTALL)
        if not cells:
            continue
        
        has_rowspan = bool(re.search(r'rowspan', row))
        
        cleaned_labels = []
        cleaned_values = []
        
        for ci, c in enumerate(cells):
            # Check if this cell has a PDF link
            pdf_match = re.search(r'<a[^>]*href="([^"]+)"[^>]*>([^<]+)</a>', c)
            
            # Remove <br> (merge split text)
            c_no_br = re.sub(r'<br\s*/?>', '', c)
            # Strip all HTML tags BUT keep <a> for PDF links
            if pdf_match:
                # Rebuild: keep the <a> tag
                a_tag = f'<a href="{pdf_match.group(1)}">{pdf_match.group(2)}</a>'
                rest = re.sub(r'<a[^>]*>.*?</a>', '', c_no_br)
                rest = re.sub(r'<[^>]+>', '', rest)
                rest = re.sub(r'\s+', '', rest)
                cleaned = rest
                # We'll attach the PDF link separately
            else:
                c_clean = re.sub(r'<[^>]+>', '', c_no_br)
                c_clean = c_clean.replace('&nbsp;', ' ')
                c_clean = c_clean.replace('\xa0', ' ')
                c_clean = c_clean.replace('&amp;', '&')
                c_clean = re.sub(r'\s+', '', c_clean).strip()
                cleaned = c_clean
            
            if pdf_match:
                cleaned_labels.append(cleaned)
                # Store PDF info for the value cell
                cleaned_values.append(f'{pdf_match.group(2)} → {pdf_match.group(1)}')
            else:
                cleaned_labels.append(cleaned)
        
        # Now build the line
        if has_rowspan:
            rowspan_label = cleaned_labels[0] if cleaned_labels else ""
            remaining = cleaned_labels[1:] if len(cleaned_labels) > 1 else []
            if len(remaining) >= 2:
                sub_label = remaining[0]
                value = remaining[1]
                # If there's a PDF for this, use it
                combined_label = rowspan_label.rstrip('：') + sub_label
                html_lines.append(f'<div class="cl">{combined_label} {value}</div>')
            elif len(remaining) == 1:
                html_lines.append(f'<div class="cl">{rowspan_label} {remaining[0]}</div>')
        else:
            if len(cleaned_labels) >= 2:
                label = cleaned_labels[0]
                value = cleaned_labels[1]
                # Check if there was a PDF
                pdf_text = ""
                for c in cells:
                    pm = re.search(r'<a[^>]*href="([^"]+)"[^>]*>([^<]+)</a>', c)
                    if pm:
                        pdf_text = f'<a href="{pm.group(1)}">{pm.group(2)}</a> → {pm.group(1)}'
                
                if pdf_text:
                    # Value cell has PDF - use PDF text with link
                    # But first clean the value to get just the link
                    pdf_value = re.sub(r'<[^>]+>', '', c_no_br) if not pdf_match else ""
                    pdf_value = re.sub(r'\s+', '', pdf_value) if pdf_value else ""
                    html_lines.append(f'<div class="cl">{label} {pdf_text}</div>')
                elif value:
                    html_lines.append(f'<div class="cl">{label} {value}</div>')
                else:
                    html_lines.append(f'<div class="cl">{label} （空）</div>')
            elif len(cleaned_labels) == 1 and cleaned_labels[0]:
                html_lines.append(f'<div class="cl">{cleaned_labels[0]}</div>')
    
    content_html = '\n'.join(html_lines)
    content_html = content_html.replace('（空）→', '（空）')
    
    # Summary: plain text
    summary_text = re.sub(r'<[^>]+>', '', content_html)
    summary_text = re.sub(r'\s+', ' ', summary_text).strip()[:500]
    
    return content_html, summary_text


def extract_title(html, list_title):
    """Extract full project name from detail page.
    Falls back to list page title if not found."""
    m = re.search(r'项目名称：</td>\s*<td[^>]*>\s*(.*?)\s*</td>', html, re.DOTALL)
    if m:
        full_title = m.group(1).strip()
        if full_title:
            return full_title
    return list_title

def extract_pub_date(html):
    """Extract publish date from detail page."""
    # Try id="slsj" (受理时间 cell) first
    d = re.search(r'id="slsj"[^>]*>\s*(\d{4})年(\d{2})月(\d{2})日', html)
    if d:
        return f"{d.group(1)}-{d.group(2)}-{d.group(3)}"
    # Fallback: meta PubDate
    d2 = re.search(r'PubDate[^>]*content="([^"]+)"', html)
    if d2:
        return d2.group(1)[:10]
    return ""

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 crawl(test_limit=0):
    conn = init_db()
    stats = {"new": 0, "skip": 0, "errors": 0}
    all_links = []
    
    # ---- 页面1 ----
    print("📃 第1页")
    html = fetch_page(LIST_URL)
    viewstate = extract_viewstate(html)
    links = extract_links(html)
    all_links.extend(links)
    print(f"  📄 提取 {len(links)} 条")
    
    # ---- 页面2 (如果有) ----
    # 通过AspNetPager链接数判断是否有更多页
    if not test_limit or len(all_links) < test_limit:
        # 构建POST数据翻到第2页
        post_data = dict(viewstate)
        post_data['__EVENTTARGET'] = 'ctl00$right_content$Pagination$pg'
        post_data['__EVENTARGUMENT'] = '2'
        
        print("📃 第2页")
        html2 = fetch_page(LIST_URL, post_data)
        links2 = extract_links(html2)
        
        for title, url in links2:
            if not any(url == u for _, u in all_links):
                all_links.append((title, url))
        print(f"  📄 提取 {len(links2)} 条")
    
    print(f"\n📄 共提取 {len(all_links)} 条链接，开始抓取详情页...")
    
    for idx, (title, url) in enumerate(all_links):
        if test_limit > 0 and idx >= test_limit:
            break
        try:
            html = fetch_page(url)
            
            content, summary = extract_content(html)
            pub_date = extract_pub_date(html)
            full_title = extract_title(html, title)
            
            conn.execute(
                "INSERT OR IGNORE INTO crawl_results (title, url, content, publish_date, summary) VALUES (?,?,?,?,?)",
                (full_title[:500], url, content, pub_date, summary)
            )
            conn.commit()
            
            size = len(content or "")
            print(f"  ✅ {size}B | {full_title[:50]}")
            stats['new'] += 1
            
        except Exception as e:
            print(f"  ❌ {str(e)[:60]} | {title[:40]}")
            stats['errors'] += 1
        
        time.sleep(0.3)
    
    conn.close()
    
    print(f"\n{'='*50}")
    print(f"🏁 新增:{stats['new']} 跳过:{stats['skip']} 错误:{stats['errors']}")
    
    if stats['new'] > 0:
        sync_to_server()
    else:
        print("⚠️ 无新数据，跳过同步")

if __name__ == "__main__":
    import argparse
    parser = argparse.ArgumentParser()
    parser.add_argument('--full', action='store_true')
    parser.add_argument('--test', type=int, default=0)
    parser.add_argument('--sync', action='store_true')
    args = parser.parse_args()
    
    if args.sync:
        sync_to_server()
    else:
        crawl(test_limit=args.test)