#!/usr/bin/env python3
"""
Import fushun data from wrong DB into production DB, fix harvin content
"""
import sqlite3
import json
import sys
import time
import re
sys.path.insert(0, '/root/gov_crawler')
from crawl_harvin import fetch, extract_detail as harvin_extract

PROD_DB = '/root/search.db'
WRONG_DB = '/root/gov_crawler/gov_raw.db'
SITE_NAME = '抚顺市人民政府'
GROUP = '公示公告'

def import_fushun():
    """Import existing 233 fushun records from wrong DB to production DB"""
    src = sqlite3.connect(WRONG_DB)
    dst = sqlite3.connect(PROD_DB)
    dst.execute('PRAGMA busy_timeout=5000')
    dc = dst.cursor()
    
    rows = src.execute("SELECT title, url, date, content, site_name, column_name, group_name, attachments FROM gov_raw").fetchall()
    print(f"Reading {len(rows)} records from wrong DB...")
    
    new_count = 0
    for title, url, pub_date, content, site, col, grp, attrs_json in rows:
        summary = (content or '')[:200]
        if not summary:
            summary = title or ''
        try:
            dc.execute('''INSERT OR IGNORE INTO gov_raw 
                (site_name, source_url, page_url, title, publish_date, content, summary, category, status, attachments)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'published', ?)''',
                (SITE_NAME, '', url, title, pub_date or '', content or '', summary, GROUP, attrs_json or '[]'))
            if dc.rowcount > 0:
                new_count += 1
        except Exception as e:
            print(f"  DB error: {e}")
    
    dst.commit()
    src.close()
    dst.close()
    print(f"Imported {new_count} fushun records into production DB")
    return new_count

def check_harvin():
    """Check harvin records in production DB"""
    conn = sqlite3.connect(PROD_DB)
    c = conn.cursor()
    
    rows = c.execute("""
        SELECT id, page_url, site_name, substr(content, 1, 200) 
        FROM gov_raw 
        WHERE site_name LIKE '%祥云%' AND content LIKE '%<table%'
        ORDER BY id
    """).fetchall()
    print(f"\nHarvin records in prod DB with HTML tables: {len(rows)}")
    for r_id, url, sn, preview in rows[:3]:
        print(f"  [id={r_id}] {url[:60]}")
    
    conn.close()
    return rows

def fix_harvin_records():
    """Fix harvin records in production DB"""
    conn = sqlite3.connect(PROD_DB)
    conn.execute('PRAGMA busy_timeout=5000')
    c = conn.cursor()
    
    # Get all harvin records
    rows = c.execute("""
        SELECT id, page_url, title, substr(content, 1, 200)
        FROM gov_raw 
        WHERE site_name LIKE '%祥云%'
        ORDER BY id
    """).fetchall()
    print(f"Total harvin records in prod DB: {len(rows)}")
    
    fixed = 0
    bad = 0
    for row_id, url, old_title, old_preview in rows:
        if not url or 'harvin' not in url.lower():
            continue
        
        # Detect bad content
        has_html_table = '<table' in (old_preview or '')
        has_metadata = '分类：' in (old_preview or '')[:500]
        
        if has_html_table or has_metadata:
            bad += 1
            try:
                html = fetch(url)
                result = harvin_extract(html, url)
                new_content = result.get('content', '')
                
                if new_content and not new_content.startswith('<table'):
                    c.execute(
                        "UPDATE gov_raw SET content = ?, title = ?, publish_date = ? WHERE id = ?",
                        (new_content, result.get('title', old_title), result.get('date', ''), row_id)
                    )
                    fixed += 1
                    print(f"  FIXED [id={row_id}] {old_title[:40]}")
                    time.sleep(0.3)
            except Exception as e:
                print(f"  ERROR [id={row_id}] {url[:60]}: {e}")
    
    conn.commit()
    conn.close()
    print(f"\nHarvin fix: {bad} bad records, {fixed} fixed")
    return fixed

def fix_harvin_fts():
    """Update FTS for harvin records"""
    conn = sqlite3.connect(PROD_DB)
    conn.execute('PRAGMA busy_timeout=5000')
    c = conn.cursor()
    
    # Get all harvin rowids
    rows = c.execute("""
        SELECT id, site_name, title, substr(content, 1, 500)
        FROM gov_raw 
        WHERE site_name LIKE '%祥云%'
    """).fetchall()
    
    for r_id, sn, title, summary in rows:
        c.execute(
            "UPDATE gov_search SET site_name = ?, title = ?, summary = ? WHERE rowid = ?",
            (sn, title or '', summary or '', r_id)
        )
    
    # Also add any missing
    c.execute("""
        INSERT OR IGNORE INTO gov_search(rowid, site_name, title, summary)
        SELECT r.id, r.site_name, r.title, substr(coalesce(nullif(r.content,''), r.title, ''), 1, 500)
        FROM gov_raw r
        LEFT JOIN gov_search fts ON fts.rowid = r.id
        WHERE fts.rowid IS NULL AND r.site_name LIKE '%祥云%'
    """)
    
    conn.commit()
    conn.close()
    print(f"FTS synced for harvin records")

if __name__ == '__main__':
    step1 = sys.argv[1] if len(sys.argv) > 1 else 'all'
    
    if step1 in ['all', 'fushun']:
        print("=== 导入 Fushun 数据到生产 DB ===")
        n = import_fushun()
        if n > 0:
            print(f"导入 {n} 条，FTS 由触发器自动同步")
    
    if step1 in ['all', 'harvin']:
        print("\n=== 修复 Harvin 生产 DB 记录 ===")
        fix_harvin_records()
        fix_harvin_fts()
    
    print("\nDone.")