#!/usr/bin/env python3
"""Sync today's data from quality_results.db to remote server's gov_raw table."""
import sqlite3
import os
import sys
import tempfile
from datetime import datetime

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, 'quality_results.db')
REMOTE = 'root@1.94.217.116'
SERVER_DB = '/root/search.db'

site_name_map = {
    'www.fogang.gov.cn': '佛冈县人民政府',
    'www.xjhbcy.cn': '新疆生态环境保护产业协会',
    'www.xjzdkfq.cn': '新疆准东经济技术开发区',
    'jzssthjj.zwgk.jingzhou.gov.cn': '荆州市生态环境局',
    'www.eiafans.com': '环评爱好者',
    'www.cnhu.com': '新和成环境信息',
}

def query_today_data():
    """Query quality_results for today's data from scrapy_to_searchdb domains."""
    today = datetime.now().strftime('%Y-%m-%d')
    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()
    
    domains = list(site_name_map.keys())
    placeholders = ','.join('?' for _ in domains)
    
    cur.execute(f'''
        SELECT domain, title, url, content, publish_date, summary
        FROM quality_results 
        WHERE domain IN ({placeholders})
        AND crawled_at LIKE ?
        ORDER BY domain, publish_date DESC
    ''', domains + [f'{today}%'])
    
    rows = cur.fetchall()
    conn.close()
    return rows

def generate_sql(rows):
    """Generate INSERT OR IGNORE SQL statements."""
    lines = []
    by_site = {}
    for domain, title, url, content, publish_date, summary in rows:
        site_name = site_name_map.get(domain, domain)
        if site_name not in by_site:
            by_site[site_name] = []
        
        # Escape single quotes for SQL
        def esc(s):
            if s is None:
                return ''
            return s.replace("'", "''")
        
        tags = ''
        sql = f"""INSERT OR IGNORE INTO gov_raw (title, page_url, content, publish_date, summary, site_name, tags) VALUES ('{esc(title)}', '{esc(url)}', '{esc(content)}', '{esc(publish_date)}', '{esc(summary)}', '{esc(site_name)}', '{esc(tags)}');"""
        by_site[site_name].append(sql)
    
    return by_site

def sync_to_server(by_site):
    """Write SQL to temp file, SCP to server, import via sqlite3."""
    total = sum(len(v) for v in by_site.values())
    if total == 0:
        print("  ℹ️  No new data to sync")
        return 0
    
    # Write SQL file
    sql_path = '/tmp/gov_raw_sync.sql'
    with open(sql_path, 'w', encoding='utf-8') as f:
        f.write("BEGIN TRANSACTION;\n")
        for site, stmts in by_site.items():
            for s in stmts:
                f.write(s + '\n')
        f.write("COMMIT;\n")
    
    print(f"  📝 Wrote {total} INSERT statements to {sql_path}")
    
    # SCP to server
    r = os.system(f'scp -o StrictHostKeyChecking=no -o ConnectTimeout=10 {sql_path} {REMOTE}:/tmp/gov_raw_sync.sql 2>&1')
    if r != 0:
        print(f"  ❌ SCP failed (exit={r}), retrying once...")
        import time
        time.sleep(3)
        r = os.system(f'scp -o StrictHostKeyChecking=no -o ConnectTimeout=15 {sql_path} {REMOTE}:/tmp/gov_raw_sync.sql 2>&1')
        if r != 0:
            print(f"  ❌ SCP retry also failed")
            return -1
    
    print(f"  ✅ SCP uploaded to server")
    
    # Import on server
    import_cmd = f'ssh -o StrictHostKeyChecking=no -o ConnectTimeout=10 {REMOTE} "cd /root && sqlite3 {SERVER_DB} < /tmp/gov_raw_sync.sql && echo \\"IMPORT OK\\" && rm -f /tmp/gov_raw_sync.sql" 2>&1'
    r = os.system(import_cmd)
    if r != 0:
        print(f"  ❌ SQL import failed (exit={r})")
        # Retry
        import time
        time.sleep(3)
        r = os.system(import_cmd)
        if r != 0:
            print(f"  ❌ SQL import retry also failed")
            return -1
    
    print(f"  ✅ SQL import successful")
    
    # Print summary by site
    print(f"\n  📊 Sync summary:")
    for site, stmts in by_site.items():
        print(f"    {site}: {len(stmts)} records")
    
    return total

def main():
    print(f"  🔍 Syncing scrapy_to_searchdb sites...")
    rows = query_today_data()
    print(f"  Found {len(rows)} new records today")
    
    by_site = generate_sql(rows)
    total = sync_to_server(by_site)
    
    return total

if __name__ == '__main__':
    main()