#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
补充缺失的英文翻译
将中文数据库中缺失的英文翻译补充完整
"""

import sqlite3

def add_missing_english_translations():
    """添加缺失的英文翻译"""
    
    db_path = '/root/老业务网站8.30/backend/ai_agent_qa.db'
    
    # 缺失的英文翻译数据
    missing_english_data = [
        {
            "question": "What specific measures does SEO technical optimization include?",
            "answer": "We provide comprehensive SEO technical optimization services: 1) Automatically generate XML Sitemap, including all page URLs, update frequency, and priority settings; 2) Structured data markup (Schema.org) to improve search result display effects; 3) Page speed optimization, including image compression, CSS/JS merging, CDN acceleration; 4) Mobile adaptation optimization to ensure responsive design; 5) URL structure optimization using semantic paths; 6) Automatic Meta tag generation, including title, description, keywords; 7) Internal link strategy implementation to establish page weight transfer; 8) External link building support to enhance domain authority; 9) Google Analytics and Search Console integration for real-time SEO effect monitoring; 10) Regular SEO reports providing optimization suggestions and effect analysis.",
            "category": "SEO Optimization",
            "keywords": "SEO, technical, optimization, XML, Sitemap, structured data, speed, mobile, URL, Meta, internal links, external links, Google"
        }
    ]
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        print("🇺🇸 开始补充缺失的英文翻译...")
        
        # 检查是否已经存在这个翻译
        cursor.execute("SELECT COUNT(*) FROM qa_pairs WHERE language = 'en' AND question = ?", 
                      ("What specific measures does SEO technical optimization include?",))
        existing_count = cursor.fetchone()[0]
        
        if existing_count > 0:
            print("✅ 该英文翻译已存在，跳过添加")
        else:
            # 插入缺失的英文翻译
            for qa in missing_english_data:
                cursor.execute("""
                    INSERT INTO qa_pairs (question, answer, language, category, keywords)
                    VALUES (?, ?, 'en', ?, ?)
                """, (qa['question'], qa['answer'], qa['category'], qa['keywords']))
            
            conn.commit()
            print(f"✅ 成功添加 {len(missing_english_data)} 条缺失的英文翻译")
        
        # 验证数据
        cursor.execute("SELECT COUNT(*) FROM qa_pairs WHERE language = 'en'")
        count = cursor.fetchone()[0]
        print(f"📊 英文数据库中现有 {count} 条记录")
        
        # 显示SEO优化分类的统计
        cursor.execute("SELECT COUNT(*) FROM qa_pairs WHERE language = 'en' AND category = 'SEO Optimization'")
        seo_count = cursor.fetchone()[0]
        print(f"📊 SEO优化分类现有 {seo_count} 条记录")
        
        # 显示新增的翻译
        print("\n📝 新增的英文翻译:")
        cursor.execute("SELECT question, answer FROM qa_pairs WHERE language = 'en' AND question = ?", 
                      ("What specific measures does SEO technical optimization include?",))
        new_translations = cursor.fetchall()
        for q, a in new_translations:
            print(f"Q: {q}")
            print(f"A: {a[:200]}...")
            print()
        
        return True
        
    except Exception as e:
        print(f"❌ 补充英文翻译失败: {e}")
        return False
    finally:
        if conn:
            conn.close()

if __name__ == "__main__":
    print("🚀 开始补充缺失的英文翻译...")
    success = add_missing_english_translations()
    if success:
        print("🎉 英文翻译补充完成！")
    else:
        print("💥 英文翻译补充失败！")