#!/usr/bin/env python3
"""
GERÇEK KALİTE SKORLAMA SİSTEMİ
- DeepL uyumsuzluk testi
- Round-trip testi
- Teknik terim özellikleri
"""

import sqlite3
import requests
import time
import json
import sys
import re
from datetime import datetime
from concurrent.futures import ThreadPoolExecutor, as_completed

# Konfigürasyon
DEEPL_API_KEY = "b121dc7e-8e98-427f-8984-54c4d4f0851e"
DEEPL_API_URL = "https://api.deepl.com/v2/translate"
DB_PATH = "/mnt/pdfs/dictionary.db"
OUTPUT_DB = "/mnt/pdfs/dictionary_scored.db"

# Rate limiting
REQUESTS_PER_SECOND = 20
BATCH_SIZE = 50

def translate_batch(texts, source_lang, target_lang):
    """Batch çeviri - birden fazla metni tek seferde çevir"""
    try:
        response = requests.post(
            DEEPL_API_URL,
            headers={
                "Authorization": f"DeepL-Auth-Key {DEEPL_API_KEY}",
                "Content-Type": "application/json"
            },
            json={
                "text": texts,
                "source_lang": source_lang,
                "target_lang": target_lang
            },
            timeout=30
        )
        
        if response.status_code == 200:
            result = response.json()
            return [t["text"] for t in result.get("translations", [])], None
        elif response.status_code == 429:
            return None, "RATE_LIMIT"
        else:
            return None, f"HTTP {response.status_code}"
    
    except Exception as e:
        return None, str(e)

def normalize_text(text):
    """Karşılaştırma için normalize et"""
    if not text:
        return ""
    # Küçük harf, fazla boşlukları temizle
    text = text.lower().strip()
    text = re.sub(r'\s+', ' ', text)
    text = re.sub(r'[^\w\s]', '', text)
    return text

def calculate_similarity(text1, text2):
    """Jaccard benzerliği hesapla"""
    if not text1 or not text2:
        return 0.0
    
    t1 = set(normalize_text(text1).split())
    t2 = set(normalize_text(text2).split())
    
    if not t1 or not t2:
        return 0.0
    
    intersection = len(t1 & t2)
    union = len(t1 | t2)
    
    return intersection / union if union > 0 else 0.0

def calculate_deepl_mismatch_score(dict_tr, deepl_tr):
    """DeepL uyumsuzluk skoru (0-40)"""
    if not deepl_tr:
        return 20  # DeepL çeviremedi = muhtemelen teknik
    
    similarity = calculate_similarity(dict_tr, deepl_tr)
    
    if similarity < 0.3:
        return 40  # Çok farklı = KRİTİK teknik terim
    elif similarity < 0.5:
        return 30
    elif similarity < 0.7:
        return 20
    elif similarity < 0.9:
        return 10
    else:
        return 0  # Aynı = DeepL zaten biliyor

def calculate_roundtrip_score(original_en, back_en):
    """Round-trip skoru (0-30)"""
    if not back_en:
        return 10
    
    similarity = calculate_similarity(original_en, back_en)
    
    if similarity >= 0.9:
        return 30  # Mükemmel tutarlılık
    elif similarity >= 0.7:
        return 20
    elif similarity >= 0.5:
        return 10
    else:
        return 0  # Tutarsız

def calculate_technical_score(en_term, tr_term):
    """Teknik terim özellikleri skoru (0-20)"""
    score = 0
    
    if not en_term:
        return 0
    
    # Compound terim (2+ kelime)
    words = en_term.split()
    if len(words) >= 2:
        score += 5
    
    # Parça numarası içermiyor
    if not re.search(r'\d{3,}', en_term):
        score += 5
    
    # Uygun uzunluk (3-40 karakter)
    if 3 <= len(en_term) <= 40:
        score += 5
    
    # Özel karakter az
    special_chars = len(re.findall(r'[^a-zA-Z0-9\s\-,]', en_term))
    if special_chars <= 1:
        score += 3
    
    # Büyük harf formatı (teknik terim göstergesi)
    if en_term.isupper():
        score += 2
    
    return min(score, 20)

def calculate_source_score(category):
    """Kaynak güvenilirliği skoru (0-10)"""
    high_quality = ['parts-catalog', 'equipment', 'compound', 'parts', 'hmk-katalog']
    medium_quality = ['katalog-detailed', 'dynapac', 'deep-scan']
    
    if category in high_quality:
        return 10
    elif category in medium_quality:
        return 7
    else:
        return 4

def main():
    print("=" * 70)
    print("🎯 GERÇEK KALİTE SKORLAMA SİSTEMİ")
    print("=" * 70)
    print(f"⏰ Başlangıç: {datetime.now().strftime('%H:%M:%S')}")
    sys.stdout.flush()
    
    # Kaynak DB
    src_conn = sqlite3.connect(DB_PATH)
    src_conn.row_factory = sqlite3.Row
    src_cursor = src_conn.cursor()
    
    # Hedef DB (skorlanmış)
    dst_conn = sqlite3.connect(OUTPUT_DB)
    dst_cursor = dst_conn.cursor()
    
    # Yeni tablo oluştur
    dst_cursor.execute('DROP TABLE IF EXISTS scored_terms')
    dst_cursor.execute('''
        CREATE TABLE scored_terms (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            original_id INTEGER,
            en_term TEXT,
            tr_term TEXT,
            deepl_tr TEXT,
            back_en TEXT,
            category TEXT,
            deepl_mismatch_score INTEGER,
            roundtrip_score INTEGER,
            technical_score INTEGER,
            source_score INTEGER,
            total_score INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    dst_cursor.execute('CREATE INDEX idx_total_score ON scored_terms(total_score DESC)')
    dst_conn.commit()
    
    # Tüm terimleri al
    print("\n📥 Terimler okunuyor...")
    src_cursor.execute('''
        SELECT term_id, canonical_en, canonical_tr, category 
        FROM technical_terms 
        WHERE canonical_en IS NOT NULL 
        AND canonical_tr IS NOT NULL
        AND LENGTH(canonical_en) >= 3
        AND LENGTH(canonical_en) <= 100
    ''')
    
    all_terms = src_cursor.fetchall()
    total_terms = len(all_terms)
    print(f"✅ {total_terms:,} terim alındı")
    
    # İstatistikler
    stats = {
        'processed': 0,
        'deepl_errors': 0,
        'high_score': 0,  # 70+
        'medium_score': 0,  # 50-69
        'low_score': 0,  # <50
    }
    
    start_time = time.time()
    batch_data = []
    
    # Batch işleme
    print(f"\n🔄 DeepL testi başlıyor (batch size: {BATCH_SIZE})...")
    print("-" * 70)
    sys.stdout.flush()
    
    for batch_start in range(0, total_terms, BATCH_SIZE):
        batch_end = min(batch_start + BATCH_SIZE, total_terms)
        batch_terms = all_terms[batch_start:batch_end]
        
        # EN metinleri topla
        en_texts = [t['canonical_en'] for t in batch_terms]
        
        # DeepL EN→TR çevirisi
        deepl_translations, error = translate_batch(en_texts, "EN", "TR")
        
        if error == "RATE_LIMIT":
            print("   ⚠️ Rate limit, 5 saniye bekleniyor...")
            time.sleep(5)
            deepl_translations, error = translate_batch(en_texts, "EN", "TR")
        
        if error:
            stats['deepl_errors'] += len(batch_terms)
            deepl_translations = [None] * len(batch_terms)
        
        # Round-trip: TR→EN geri çeviri (sadece DeepL farklı çevirdiyse)
        tr_texts_for_roundtrip = []
        roundtrip_indices = []
        
        for i, (term, deepl_tr) in enumerate(zip(batch_terms, deepl_translations)):
            if deepl_tr:
                similarity = calculate_similarity(term['canonical_tr'], deepl_tr)
                if similarity < 0.7:  # Farklıysa round-trip yap
                    tr_texts_for_roundtrip.append(term['canonical_tr'])
                    roundtrip_indices.append(i)
        
        # Round-trip çevirisi
        back_translations = [None] * len(batch_terms)
        if tr_texts_for_roundtrip:
            back_results, _ = translate_batch(tr_texts_for_roundtrip, "TR", "EN")
            if back_results:
                for idx, back_en in zip(roundtrip_indices, back_results):
                    back_translations[idx] = back_en
        
        # Skorlama
        for i, term in enumerate(batch_terms):
            term_id = term['term_id']
            en = term['canonical_en']
            tr = term['canonical_tr']
            category = term['category'] or 'general'
            deepl_tr = deepl_translations[i] if deepl_translations else None
            back_en = back_translations[i]
            
            # Skorları hesapla
            deepl_score = calculate_deepl_mismatch_score(tr, deepl_tr)
            roundtrip_score = calculate_roundtrip_score(en, back_en) if back_en else 15
            technical_score = calculate_technical_score(en, tr)
            source_score = calculate_source_score(category)
            
            total_score = deepl_score + roundtrip_score + technical_score + source_score
            
            # İstatistik güncelle
            stats['processed'] += 1
            if total_score >= 70:
                stats['high_score'] += 1
            elif total_score >= 50:
                stats['medium_score'] += 1
            else:
                stats['low_score'] += 1
            
            # Kaydet
            batch_data.append((
                term_id, en, tr, deepl_tr, back_en, category,
                deepl_score, roundtrip_score, technical_score, source_score, total_score
            ))
        
        # Her 500 terimde kaydet ve ilerleme göster
        if len(batch_data) >= 500:
            dst_cursor.executemany('''
                INSERT INTO scored_terms 
                (original_id, en_term, tr_term, deepl_tr, back_en, category,
                 deepl_mismatch_score, roundtrip_score, technical_score, source_score, total_score)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', batch_data)
            dst_conn.commit()
            batch_data = []
        
        # İlerleme göster
        if (batch_end) % 1000 == 0 or batch_end == total_terms:
            elapsed = time.time() - start_time
            rate = stats['processed'] / elapsed if elapsed > 0 else 0
            remaining = (total_terms - stats['processed']) / rate / 60 if rate > 0 else 0
            pct = stats['processed'] / total_terms * 100
            
            print(f"   ✓ {stats['processed']:,}/{total_terms:,} [{pct:.1f}%] | "
                  f"70+: {stats['high_score']:,} | "
                  f"~{remaining:.0f}dk kaldı")
            sys.stdout.flush()
        
        # Rate limiting
        time.sleep(0.1)
    
    # Kalan batch'i kaydet
    if batch_data:
        dst_cursor.executemany('''
            INSERT INTO scored_terms 
            (original_id, en_term, tr_term, deepl_tr, back_en, category,
             deepl_mismatch_score, roundtrip_score, technical_score, source_score, total_score)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', batch_data)
        dst_conn.commit()
    
    # Sonuçları göster
    print("\n" + "=" * 70)
    print("📈 SONUÇLAR")
    print("=" * 70)
    
    total = stats['processed']
    print(f"📊 Toplam işlenen: {total:,}")
    print(f"⭐ Yüksek skor (70+): {stats['high_score']:,} ({stats['high_score']/total*100:.1f}%)")
    print(f"✅ Orta skor (50-69): {stats['medium_score']:,} ({stats['medium_score']/total*100:.1f}%)")
    print(f"⚠️ Düşük skor (<50): {stats['low_score']:,} ({stats['low_score']/total*100:.1f}%)")
    print(f"🔴 DeepL hataları: {stats['deepl_errors']:,}")
    
    # Skor dağılımı
    print("\n📊 SKOR DAĞILIMI:")
    dst_cursor.execute('''
        SELECT 
            CASE 
                WHEN total_score >= 90 THEN '90-100'
                WHEN total_score >= 80 THEN '80-89'
                WHEN total_score >= 70 THEN '70-79'
                WHEN total_score >= 60 THEN '60-69'
                WHEN total_score >= 50 THEN '50-59'
                ELSE '0-49'
            END as range,
            COUNT(*) as count
        FROM scored_terms
        GROUP BY range
        ORDER BY range DESC
    ''')
    for row in dst_cursor.fetchall():
        print(f"   {row[0]}: {row[1]:,}")
    
    # En yüksek skorlu örnekler
    print("\n📝 EN YÜKSEK SKORLU 20 TERİM:")
    dst_cursor.execute('''
        SELECT total_score, en_term, tr_term, deepl_tr,
               deepl_mismatch_score, roundtrip_score, technical_score
        FROM scored_terms 
        ORDER BY total_score DESC 
        LIMIT 20
    ''')
    for row in dst_cursor.fetchall():
        score, en, tr, deepl_tr, dm, rt, tech = row
        print(f"   [{score}] {en[:35]:<35} → {tr[:25]:<25}")
        if deepl_tr and deepl_tr != tr:
            print(f"         DeepL: {deepl_tr[:40]} (DM:{dm} RT:{rt} T:{tech})")
    
    elapsed_total = (time.time() - start_time) / 60
    print(f"\n⏱️ Toplam süre: {elapsed_total:.1f} dakika")
    print(f"✅ Skorlanmış DB kaydedildi: {OUTPUT_DB}")
    print(f"⏰ Bitiş: {datetime.now().strftime('%H:%M:%S')}")
    
    src_conn.close()
    dst_conn.close()

if __name__ == "__main__":
    main()