#!/usr/bin/env python3
"""
Analytics Database - Usta Assistant Analiz Veritabanı
======================================================
Arama ve soru-cevap verilerini kaydeder.

Kullanım:
    from analytics_db import AnalyticsDB
    
    db = AnalyticsDB()
    db.log_search(brand="JCB", model="JS330", query="hidrolik pompa", ...)
    db.log_question(brand="JCB", model="JS330", question="basınç değeri?", ...)
"""

import os
import sqlite3
import uuid
from datetime import datetime
from typing import Dict, List, Optional
from dataclasses import dataclass


# Veritabanı yolu
DB_PATH = os.path.join(os.path.dirname(__file__), "usta_analytics.db")


@dataclass
class SearchLog:
    """Arama kaydı"""
    id: int
    session_id: str
    brand: str
    model: str
    query_text: str
    query_english: str
    results_count: int
    search_time_ms: int
    created_at: str


@dataclass
class QuestionLog:
    """Soru-cevap kaydı"""
    id: int
    session_id: str
    brand: str
    model: str
    pdf_path: str
    page_number: int
    question: str
    question_english: str
    answer_english: str
    answer_turkish: str
    confidence: str
    response_time_ms: int
    created_at: str


class AnalyticsDB:
    """
    Analiz Veritabanı
    
    Arama ve soru-cevap verilerini SQLite'a kaydeder.
    """
    
    def __init__(self, db_path: str = DB_PATH):
        self.db_path = db_path
        self.conn = None
        self._connect()
        self._create_tables()
    
    def _connect(self):
        """Veritabanına bağlan"""
        try:
            self.conn = sqlite3.connect(self.db_path, check_same_thread=False)
            self.conn.row_factory = sqlite3.Row
            print(f"✓ Analytics DB bağlantısı OK: {self.db_path}")
        except Exception as e:
            print(f"❌ Analytics DB bağlantı hatası: {e}")
            self.conn = None
    
    def _create_tables(self):
        """Tabloları oluştur"""
        if not self.conn:
            return
        
        cursor = self.conn.cursor()
        
        # Arama logları
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS search_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT,
                brand TEXT,
                model TEXT,
                query_text TEXT,
                query_english TEXT,
                results_count INTEGER,
                top_result_pdf TEXT,
                top_result_page INTEGER,
                top_result_score REAL,
                search_time_ms INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Soru-cevap logları
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS question_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT,
                brand TEXT,
                model TEXT,
                pdf_path TEXT,
                page_number INTEGER,
                question TEXT,
                question_english TEXT,
                answer_english TEXT,
                answer_turkish TEXT,
                confidence TEXT,
                safety_warnings TEXT,
                response_time_ms INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Çeviri rating/feedback tablosu
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS translation_feedback (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                session_id TEXT,
                feedback_type TEXT,  -- 'search_result' veya 'answer'
                original_text TEXT,
                translated_text TEXT,
                rating INTEGER,  -- 1-5 yıldız
                user_suggestion TEXT,  -- Kullanıcının önerdiği doğru çeviri
                brand TEXT,
                model TEXT,
                is_processed INTEGER DEFAULT 0,  -- Sözlüğe eklendi mi?
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # Sözlük önerileri tablosu (usta jargonu için)
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS dictionary_suggestions (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                english_term TEXT,
                turkish_term TEXT,
                suggested_by TEXT,  -- 'user' veya 'system'
                context TEXT,  -- Hangi bağlamda kullanıldı
                vote_count INTEGER DEFAULT 1,
                is_approved INTEGER DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE(english_term, turkish_term)
            )
        ''')
        
        # İndeksler
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_search_brand ON search_logs(brand)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_search_model ON search_logs(model)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_search_date ON search_logs(created_at)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_question_brand ON question_logs(brand)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_question_model ON question_logs(model)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_question_date ON question_logs(created_at)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_feedback_rating ON translation_feedback(rating)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_suggestion_term ON dictionary_suggestions(english_term)')
        
        self.conn.commit()
        print("✓ Analytics tabloları hazır")
    
    def log_search(
        self,
        brand: str,
        model: str,
        query_text: str,
        query_english: str = "",
        results_count: int = 0,
        top_result_pdf: str = "",
        top_result_page: int = 0,
        top_result_score: float = 0.0,
        search_time_ms: int = 0,
        session_id: str = None
    ) -> int:
        """
        Arama kaydı ekle.
        
        Returns:
            Eklenen kaydın ID'si
        """
        if not self.conn:
            return 0
        
        if not session_id:
            session_id = str(uuid.uuid4())[:8]
        
        try:
            cursor = self.conn.cursor()
            cursor.execute('''
                INSERT INTO search_logs 
                (session_id, brand, model, query_text, query_english, results_count, 
                 top_result_pdf, top_result_page, top_result_score, search_time_ms)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                session_id, brand, model, query_text, query_english, results_count,
                top_result_pdf, top_result_page, top_result_score, search_time_ms
            ))
            self.conn.commit()
            return cursor.lastrowid
        except Exception as e:
            print(f"⚠️ Arama log hatası: {e}")
            return 0
    
    def log_question(
        self,
        brand: str,
        model: str,
        pdf_path: str,
        page_number: int,
        question: str,
        question_english: str = "",
        answer_english: str = "",
        answer_turkish: str = "",
        confidence: str = "",
        safety_warnings: str = "",
        response_time_ms: int = 0,
        session_id: str = None
    ) -> int:
        """
        Soru-cevap kaydı ekle.
        
        Returns:
            Eklenen kaydın ID'si
        """
        if not self.conn:
            return 0
        
        if not session_id:
            session_id = str(uuid.uuid4())[:8]
        
        try:
            cursor = self.conn.cursor()
            cursor.execute('''
                INSERT INTO question_logs 
                (session_id, brand, model, pdf_path, page_number, question, 
                 question_english, answer_english, answer_turkish, confidence, 
                 safety_warnings, response_time_ms)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                session_id, brand, model, pdf_path, page_number, question,
                question_english, answer_english, answer_turkish, confidence,
                safety_warnings, response_time_ms
            ))
            self.conn.commit()
            return cursor.lastrowid
        except Exception as e:
            print(f"⚠️ Soru log hatası: {e}")
            return 0
    
    def get_popular_queries(self, brand: str = None, limit: int = 10) -> List[Dict]:
        """En popüler aramaları getir"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        
        if brand:
            cursor.execute('''
                SELECT query_text, COUNT(*) as count, brand, model
                FROM search_logs 
                WHERE brand = ?
                GROUP BY query_text 
                ORDER BY count DESC 
                LIMIT ?
            ''', (brand, limit))
        else:
            cursor.execute('''
                SELECT query_text, COUNT(*) as count, brand, model
                FROM search_logs 
                GROUP BY query_text 
                ORDER BY count DESC 
                LIMIT ?
            ''', (limit,))
        
        return [dict(row) for row in cursor.fetchall()]
    
    def get_popular_questions(self, brand: str = None, limit: int = 10) -> List[Dict]:
        """En popüler soruları getir"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        
        if brand:
            cursor.execute('''
                SELECT question, answer_turkish, COUNT(*) as count, brand, model
                FROM question_logs 
                WHERE brand = ?
                GROUP BY question 
                ORDER BY count DESC 
                LIMIT ?
            ''', (brand, limit))
        else:
            cursor.execute('''
                SELECT question, answer_turkish, COUNT(*) as count, brand, model
                FROM question_logs 
                GROUP BY question 
                ORDER BY count DESC 
                LIMIT ?
            ''', (limit,))
        
        return [dict(row) for row in cursor.fetchall()]
    
    def get_stats(self) -> Dict:
        """Genel istatistikleri getir"""
        if not self.conn:
            return {}
        
        cursor = self.conn.cursor()
        
        # Toplam arama
        cursor.execute('SELECT COUNT(*) FROM search_logs')
        total_searches = cursor.fetchone()[0]
        
        # Toplam soru
        cursor.execute('SELECT COUNT(*) FROM question_logs')
        total_questions = cursor.fetchone()[0]
        
        # Marka bazında dağılım
        cursor.execute('''
            SELECT brand, COUNT(*) as count 
            FROM search_logs 
            GROUP BY brand 
            ORDER BY count DESC
        ''')
        brands = [dict(row) for row in cursor.fetchall()]
        
        # Bugünkü aktivite
        cursor.execute('''
            SELECT COUNT(*) FROM search_logs 
            WHERE DATE(created_at) = DATE('now')
        ''')
        today_searches = cursor.fetchone()[0]
        
        cursor.execute('''
            SELECT COUNT(*) FROM question_logs 
            WHERE DATE(created_at) = DATE('now')
        ''')
        today_questions = cursor.fetchone()[0]
        
        return {
            "total_searches": total_searches,
            "total_questions": total_questions,
            "today_searches": today_searches,
            "today_questions": today_questions,
            "brands": brands
        }
    
    def find_similar_questions(self, question: str, brand: str = None, limit: int = 5) -> List[Dict]:
        """Benzer soruları ve cevapları bul (cache olarak kullanılabilir)"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        
        # Basit kelime eşleşmesi ile benzer soruları bul
        words = question.lower().split()
        conditions = " OR ".join(["LOWER(question) LIKE ?" for _ in words])
        params = [f"%{w}%" for w in words if len(w) > 2]
        
        if not params:
            return []
        
        query = f'''
            SELECT question, answer_turkish, confidence, pdf_path, page_number, brand, model
            FROM question_logs 
            WHERE ({conditions})
        '''
        
        if brand:
            query += " AND brand = ?"
            params.append(brand)
        
        query += f" ORDER BY created_at DESC LIMIT {limit}"
        
        cursor.execute(query, params)
        return [dict(row) for row in cursor.fetchall()]
    
    def log_translation_feedback(
        self,
        feedback_type: str,
        original_text: str,
        translated_text: str,
        rating: int,
        user_suggestion: str = "",
        brand: str = "",
        model: str = "",
        session_id: str = None
    ) -> int:
        """
        Çeviri feedback kaydı ekle.
        
        Args:
            feedback_type: 'search_result' veya 'answer'
            original_text: Orijinal İngilizce metin
            translated_text: Çevrilen Türkçe metin
            rating: 1-5 yıldız
            user_suggestion: Kullanıcının önerdiği doğru çeviri
            
        Returns:
            Eklenen kaydın ID'si
        """
        if not self.conn:
            return 0
        
        if not session_id:
            session_id = str(uuid.uuid4())[:8]
        
        try:
            cursor = self.conn.cursor()
            cursor.execute('''
                INSERT INTO translation_feedback 
                (session_id, feedback_type, original_text, translated_text, 
                 rating, user_suggestion, brand, model)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                session_id, feedback_type, original_text, translated_text,
                rating, user_suggestion, brand, model
            ))
            self.conn.commit()
            
            # Kötü rating (1-2) ve öneri varsa sözlük önerisine ekle
            if rating <= 2 and user_suggestion:
                self.add_dictionary_suggestion(
                    english_term=original_text[:200],
                    turkish_term=user_suggestion[:200],
                    suggested_by='user',
                    context=f"{brand} {model}"
                )
            
            return cursor.lastrowid
        except Exception as e:
            print(f"⚠️ Feedback log hatası: {e}")
            return 0
    
    def add_dictionary_suggestion(
        self,
        english_term: str,
        turkish_term: str,
        suggested_by: str = 'user',
        context: str = ""
    ) -> int:
        """
        Sözlük önerisi ekle veya var olanın oyunu artır.
        
        Returns:
            Eklenen/güncellenen kaydın ID'si
        """
        if not self.conn:
            return 0
        
        try:
            cursor = self.conn.cursor()
            
            # Var mı kontrol et
            cursor.execute('''
                SELECT id, vote_count FROM dictionary_suggestions 
                WHERE LOWER(english_term) = LOWER(?) AND LOWER(turkish_term) = LOWER(?)
            ''', (english_term.strip(), turkish_term.strip()))
            
            existing = cursor.fetchone()
            
            if existing:
                # Var, oy sayısını artır
                cursor.execute('''
                    UPDATE dictionary_suggestions 
                    SET vote_count = vote_count + 1 
                    WHERE id = ?
                ''', (existing[0],))
                self.conn.commit()
                return existing[0]
            else:
                # Yeni kayıt
                cursor.execute('''
                    INSERT INTO dictionary_suggestions 
                    (english_term, turkish_term, suggested_by, context)
                    VALUES (?, ?, ?, ?)
                ''', (english_term.strip(), turkish_term.strip(), suggested_by, context))
                self.conn.commit()
                return cursor.lastrowid
                
        except Exception as e:
            print(f"⚠️ Sözlük öneri hatası: {e}")
            return 0
    
    def get_low_rated_translations(self, limit: int = 20) -> List[Dict]:
        """Düşük puanlı çevirileri getir (iyileştirme için)"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        cursor.execute('''
            SELECT original_text, translated_text, rating, user_suggestion, 
                   COUNT(*) as count, brand, model
            FROM translation_feedback 
            WHERE rating <= 2
            GROUP BY original_text 
            ORDER BY count DESC, rating ASC
            LIMIT ?
        ''', (limit,))
        
        return [dict(row) for row in cursor.fetchall()]
    
    def get_pending_suggestions(self, limit: int = 50) -> List[Dict]:
        """Onay bekleyen sözlük önerilerini getir"""
        if not self.conn:
            return []
        
        cursor = self.conn.cursor()
        cursor.execute('''
            SELECT id, english_term, turkish_term, vote_count, context, created_at
            FROM dictionary_suggestions 
            WHERE is_approved = 0
            ORDER BY vote_count DESC
            LIMIT ?
        ''', (limit,))
        
        return [dict(row) for row in cursor.fetchall()]
    
    def approve_suggestion(self, suggestion_id: int) -> bool:
        """Sözlük önerisini onayla"""
        if not self.conn:
            return False
        
        try:
            cursor = self.conn.cursor()
            cursor.execute('''
                UPDATE dictionary_suggestions 
                SET is_approved = 1 
                WHERE id = ?
            ''', (suggestion_id,))
            self.conn.commit()
            return True
        except:
            return False
    
    def get_feedback_stats(self) -> Dict:
        """Feedback istatistikleri"""
        if not self.conn:
            return {}
        
        cursor = self.conn.cursor()
        
        # Ortalama rating
        cursor.execute('SELECT AVG(rating) FROM translation_feedback')
        avg_rating = cursor.fetchone()[0] or 0
        
        # Rating dağılımı
        cursor.execute('''
            SELECT rating, COUNT(*) as count 
            FROM translation_feedback 
            GROUP BY rating 
            ORDER BY rating
        ''')
        rating_dist = {row[0]: row[1] for row in cursor.fetchall()}
        
        # Toplam feedback
        cursor.execute('SELECT COUNT(*) FROM translation_feedback')
        total_feedback = cursor.fetchone()[0]
        
        # Öneri sayısı
        cursor.execute('SELECT COUNT(*) FROM dictionary_suggestions')
        total_suggestions = cursor.fetchone()[0]
        
        # Onaylanan öneri sayısı
        cursor.execute('SELECT COUNT(*) FROM dictionary_suggestions WHERE is_approved = 1')
        approved_suggestions = cursor.fetchone()[0]
        
        return {
            "average_rating": round(avg_rating, 2),
            "rating_distribution": rating_dist,
            "total_feedback": total_feedback,
            "total_suggestions": total_suggestions,
            "approved_suggestions": approved_suggestions
        }
    
    def close(self):
        """Bağlantıyı kapat"""
        if self.conn:
            self.conn.close()
            self.conn = None


# Test
if __name__ == "__main__":
    db = AnalyticsDB()
    
    # Test kayıtları
    db.log_search(
        brand="JCB",
        model="JS330",
        query_text="hidrolik pompa arızası",
        query_english="hydraulic pump failure",
        results_count=10,
        search_time_ms=1200
    )
    
    db.log_question(
        brand="JCB",
        model="JS330",
        pdf_path="/mnt/pdfs/JCB/JS330/service.pdf",
        page_number=412,
        question="relief valve basıncı ne?",
        question_english="what is the relief valve pressure?",
        answer_english="The relief valve pressure is 350 bar.",
        answer_turkish="Emniyet valfi basıncı 350 bar'dır.",
        confidence="high"
    )
    
    print("\n📊 İstatistikler:")
    print(db.get_stats())
    
    print("\n🔍 Popüler Aramalar:")
    print(db.get_popular_queries())
    
    db.close()