#!/usr/bin/env python3
"""
睿霖化工 (www.ruilinhuagong.com) 爬虫
结构: 首页单页表格 → 所有条目直链PDF
正文内容为PDF，服务器详情页展示为附件链接
"""
import re
import requests
import sqlite3
import os
import sys
from datetime import datetime

SITE_NAME = '睿霖化工'
SITE_DOMAIN = 'www.ruilinhuagong.com'
BASE_URL = 'https://www.ruilinhuagong.com'

# 数据库路径
DB_PATH = os.environ.get('DB_PATH', os.path.join(os.path.dirname(os.path.abspath(__file__)), 'search.db'))
if not os.path.isabs(DB_PATH):
    DB_PATH = os.path.abspath(DB_PATH)

def fetch_list():
    """获取首页所有条目"""
    resp = requests.get(f'{BASE_URL}/', timeout=30)
    resp.encoding = 'utf-8'
    html = resp.text
    
    # 提取所有 <td class="host-open-content"> 行
    pattern = r'<td class="host-open-content">.*?<a href="([^"]*)"[^>]*>([^<]+)</a>'
    matches = re.findall(pattern, html, re.DOTALL)
    
    items = []
    seen = set()
    for href, title in matches:
        href = href.strip()
        if not href:
            continue  # 跳过空链接
        title = title.strip()
        if not title or title in seen:
            continue
        seen.add(title)
        
        # 补全为绝对URL
        if href.startswith('http'):
            pdf_url = href
        else:
            pdf_url = BASE_URL + href
        
        # 从URL提取日期（/uploadfile/YYYYMM/xxx.pdf → YYYY-MM）
        pub_date = ''
        m = re.search(r'/uploadfile/(\d{4})(\d{2})/', href)
        if m:
            pub_date = f'{m.group(1)}-{m.group(2)}'
        
        items.append({
            'title': title,
            'pdf_url': pdf_url,
            'publish_date': pub_date,
        })
    
    return items

def init_db():
    """确保数据库表存在（匹配服务器schema）"""
    db = sqlite3.connect(DB_PATH)
    db.execute('''CREATE TABLE IF NOT EXISTS gov_raw (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        site_name TEXT,
        source_url TEXT,
        page_url TEXT,
        title TEXT,
        publish_date TEXT,
        date_rank INTEGER DEFAULT 0,
        summary TEXT,
        status TEXT,
        category TEXT DEFAULT '',
        visits INTEGER DEFAULT 0,
        content TEXT DEFAULT '',
        tags TEXT DEFAULT ''
    )''')
    try:
        db.execute('''CREATE VIRTUAL TABLE IF NOT EXISTS gov_search USING fts5(
            title, content, site_name,
            content='gov_raw', content_rowid='id',
            tokenize='unicode61'
        )''')
    except sqlite3.OperationalError:
        pass
    db.commit()
    db.close()

def save_items(items):
    """入库，正文为PDF链接"""
    db = sqlite3.connect(DB_PATH)
    cur = db.cursor()
    new_count = 0
    total = len(items)
    
    for item in items:
        # 正文显示为可点击的PDF链接
        content_html = f'<p>📄 该公示内容为PDF文件，请点击下方链接查看：</p><p><a href="{item["pdf_url"]}" target="_blank" rel="noopener" style="display:inline-block;padding:10px 20px;background:#4361ee;color:#fff;border-radius:6px;text-decoration:none;font-size:14px">📄 查看PDF文档</a></p><p><a href="{item["pdf_url"]}" target="_blank" rel="noopener" style="color:#1a73e8;font-size:13px">{item["pdf_url"]}</a></p>'
        
        try:
            cur.execute('''INSERT OR IGNORE INTO gov_raw 
                (title, content, publish_date, page_url, source_url, site_name, status)
                VALUES (?, ?, ?, ?, ?, ?, ?)''', (
                item['title'],
                content_html,
                item['publish_date'],
                item['pdf_url'],
                'www.ruilinhuagong.com',
                '睿霖化工',
                'published',
            ))
            if cur.rowcount > 0:
                new_count += 1
                row_id = cur.lastrowid
                try:
                    db.execute('INSERT INTO gov_search(rowid, title, content, site_name) VALUES (?,?,?,?)',
                               (row_id, item['title'], content_html, '睿霖化工'))
                except sqlite3.IntegrityError:
                    pass
        except Exception as e:
            print(f'  入库失败: {item["title"][:30]}... {e}')
    
    db.commit()
    total_rows = cur.execute('SELECT COUNT(*) FROM gov_raw WHERE site_name=?', ('睿霖化工',)).fetchone()[0]
    db.close()
    print(f'共获取 {total} 条有效记录')
    print(f'入库: 新增{new_count}, 累计{total_rows}')
    return new_count

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 FROM crawl_results ORDER BY id").fetchall()
    conn.close()

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

    dst = sqlite3.connect("/root/search.db")
    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 "")[:8000], 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 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}条)")


if __name__ == '__main__':
    print(f'=== {SITE_NAME} 爬虫 ({SITE_DOMAIN}) ===')
    init_db()
    items = fetch_list()
    print(f'找到 {len(items)} 条记录')
    save_items(items)
    
    if len(sys.argv) > 1 and sys.argv[1] == '--sync':
        sync_to_server()