#!/usr/bin/env python3
"""
中国石化安庆分公司 - 公示信息爬虫
http://apw.sinopec.com/apw/csr/environmental_health/

CMS: SharePoint，无WAF，可直接用 requests
"""

import sys, os, re, subprocess, time

# ---------- 配置 ----------
SITE_NAME = "镇海石化"
BASE_URL = "http://zrcc.sinopec.com/zrcc/csr/safe_envir/lft_zxjk/eia_site"
LIST_URL = "http://zrcc.sinopec.com/zrcc/csr/safe_envir/lft_zxjk/eia_site/"
DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "quality_results.db")
SERVER_SSH = "root@1.94.217.116"

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


def get_list_items(html):
    """从列表页HTML提取所有条目"""
    items = []
    # 方式1: title 属性存标题（页面1）
    for m in re.finditer(r'<a[^>]*title="([^"]*)"[^>]*href="(/zrcc/csr/safe_envir/lft_zxjk/eia_site/\d+/news_[\w_]+\.shtml)"', html):
        href = "http://zrcc.sinopec.com" + m.group(2)
        title = m.group(1).strip()
        items.append({'title': title, 'href': href})
    # 方式2: a 标签文字做标题（分页页面）
    if not items:
        for m in re.finditer(r'href="(/zrcc/csr/safe_envir/lft_zxjk/eia_site/\d+/news_[\w_]+\.shtml)"[^>]*>([^<]+)</a>', html):
            href = "http://zrcc.sinopec.com" + m.group(1)
            title = m.group(2).strip()
            if title:
                items.append({'title': title, 'href': href})
    return items


def get_detail(url):
    """访问详情页提取标题、日期、正文"""
    import requests
    try:
        r = requests.get(url, headers=HEADERS, timeout=30)
        r.encoding = 'utf-8'
        html = r.text
    except Exception as e:
        return {'error': str(e)}
    
    # 标题
    title = ""
    m = re.search(r'<div class="lfnews-title">(.*?)</div>', html, re.DOTALL)
    if m:
        title = re.sub(r'<[^>]+>', '', m.group(1)).strip()
    
    if not title:
        m = re.search(r'<title>([^<]+)</title>', html)
        if m:
            title = re.sub(r'&nbsp;.*', '', m.group(1)).strip()
    
    # 日期（从 URL 路径提取）
    pub_date = ""
    m = re.search(r'/(\d{8})/news_[\w_]+\.shtml', url)
    if m:
        d = m.group(1)
        pub_date = f"{d[:4]}-{d[4:6]}-{d[6:8]} 08:00:00"
    
    # 正文
    m = re.search(r'<div class="lfnews-content">(.*?)</div>\s*<div class="lfnews-bottom"', html, re.DOTALL)
    if m:
        content_html = m.group(1)
        # 去掉 SharePoint 控件标记
        content_html = re.sub(r'<div[^>]*style=\'display:none\'>[^<]*</div>', '', content_html)
        content_html = re.sub(r'<div[^>]*__ControlWrapper[^>]*>|</div>', '', content_html)
        
        # 去掉"页面内容"前缀
        content_html = re.sub(r'^\s*页面内容\s*', '', content_html)
        
        # 附件链接补全为绝对路径
        def make_abs_link(m):
            href = m.group(1)
            text = m.group(3)
            if href.startswith('/'):
                href = 'http://zrcc.sinopec.com' + href
            return f'<a href="{href}">{text}</a>'
        content_html = re.sub(r'<a[^>]*href="([^"]+\.(pdf|docx?|xlsx?))"[^>]*>([^<]+)</a>',
                              make_abs_link, content_html, flags=re.I)
        
        # 清理内联样式和多余的 span
        content_html = re.sub(r' style="[^"]*"', '', content_html)
        content_html = re.sub(r'<span[^>]*>|</span>', '', content_html)
        content_html = re.sub(r'&nbsp;', ' ', content_html)
        
        # 保留下来的内容直接作为 content（含 <p> <a> 标签）
        content = content_html.strip()
    else:
        content = ""
    
    # 如果没有内容
    if not content or len(content) < 20:
        content = f'<p>{title}</p>'
    
    return {
        'title': title,
        'pub_date': pub_date,
        'content': content,
        'url': url,
    }


def sync_to_server(records):
    """同步到远程服务器: SCP SQL 文件 + SSH 执行"""
    if not records:
        return
    
    sql_lines = []
    for r in records:
        title = r['title'].replace("'", "''")
        url = r['url'].replace("'", "''")
        content = (r.get('content', '') or '').replace("'", "''")
        date = r.get('pub_date', '')
        sql_lines.append(
            f"INSERT OR IGNORE INTO gov_raw (site_name, page_url, title, content, publish_date, summary, date_rank) "
            f"VALUES ('{SITE_NAME}','{url}','{title}','{content}','{date}','{SITE_NAME}','{date}');"
        )
    
    sql = "BEGIN;" + "".join(sql_lines) + "COMMIT;"
    tmp = "/tmp/sync_apw.sql"
    with open(tmp, "w") as f:
        f.write(sql)
    
    r = subprocess.run(["scp", "-o", "ConnectTimeout=10", tmp, f"{SERVER_SSH}:{tmp}"],
                       capture_output=True, text=True, timeout=60)
    if r.returncode != 0:
        print(f"  SCP失败: {r.stderr}")
        os.unlink(tmp)
        return
    
    r = subprocess.run(["ssh", "-o", "ConnectTimeout=10", SERVER_SSH,
                        f"sqlite3 /root/search.db < {tmp} && rm {tmp}"],
                       capture_output=True, text=True, timeout=120)
    if r.returncode == 0:
        print("  同步OK")
    else:
        print(f"  同步失败: {r.stderr[:200]}")
    
    os.unlink(tmp)
    
    subprocess.run(["ssh", "-o", "ConnectTimeout=10", SERVER_SSH,
                    "sqlite3 /root/search.db \"INSERT INTO gov_search(gov_raw) SELECT rowid FROM gov_raw WHERE rowid NOT IN (SELECT rowid FROM gov_search);\""],
                   capture_output=True, text=True, timeout=60)
    print("  FTS更新完成")


def main():
    print(f"\n爬取: {SITE_NAME}")
    import requests
    
    all_details = []
    visited_urls = set()
    
    # 爬取列表页
    for page_n in range(1, 10):
        if page_n == 1:
            url = BASE_URL + "/"
        else:
            # 尝试分页（格式可能不同站不同）
            url = f"http://zrcc.sinopec.com/zrcc/csr/safe_envir/lft_zxjk/eia_site/default_{page_n-1}.shtml"
        
        print(f"  第{page_n}页: ", end="", flush=True)
        
        try:
            r = requests.get(url, headers=HEADERS, timeout=30)
            r.encoding = 'utf-8'
            html = r.text
        except Exception as e:
            print(f"请求失败: {e}")
            break
        
        items = get_list_items(html)
        if not items:
            print("0 条")
            break
        
        # 去重
        new_items = [i for i in items if i['href'] not in visited_urls]
        if not new_items or len(new_items) < len(items) * 0.3:
            print(f"{len(items)} 条 (已爬)")
            break
        
        print(f"{len(new_items)} 条")
        
        for item in new_items:
            title_short = item['title'][:50]
            print(f"    详情: {title_short}... ", end="", flush=True)
            
            detail = get_detail(item['href'])
            visited_urls.add(item['href'])
            
            if detail.get('error'):
                print(f"FAIL: {detail['error']}")
                continue
            
            if len(detail.get('content', '')) < 20:
                print("内容过短，跳过")
                continue
            
            all_details.append(detail)
            print(f"OK ({len(detail['content'])}字)")
    
    if not all_details:
        print("未爬取到有效数据")
        return
    
    print(f"\n共获取 {len(all_details)} 条有效记录")
    
    # 保存到本地 SQLite
    import sqlite3
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    
    new_count = 0
    for r in all_details:
        try:
            cur = conn.execute(
                'INSERT OR IGNORE INTO quality_results (site_name, title, url, content, publish_date) VALUES (?, ?, ?, ?, ?)',
                (SITE_NAME, r['title'], r['url'], r['content'], r['pub_date'])
            )
            if cur.rowcount > 0:
                new_count += 1
        except:
            pass
    
    conn.commit()
    total = conn.execute('SELECT COUNT(*) FROM quality_results').fetchone()[0]
    conn.close()
    print(f"入库: 新增{new_count}, 累计{total}")
    
    # 同步到服务器
    print("同步到服务器...")
    sync_to_server(all_details)
    
    print("完成")


if __name__ == "__main__":
    main()