o 3iO @sdZddlZddlZddlZddlmZddlmZmZmZddl m Z ej ej edZe GdddZe Gd d d ZGd d d Zed kreZejdddddddejdddddddddd edeeedeeedSdS)u 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?", ...) N)datetime)DictListOptional) dataclasszusta_analytics.dbc@sZeZdZUdZeed<eed<eed<eed<eed<eed<eed<eed <eed <d S) SearchLogu Arama kaydıid session_idbrandmodel query_text query_english results_countsearch_time_ms created_atN__name__ __module__ __qualname____doc__int__annotations__strrr./var/www/html/PEPCVSON/scripts/analytics_db.pyrs  rc@szeZdZUdZeed<eed<eed<eed<eed<eed<eed<eed <eed <eed <eed <eed <eed<dS) QuestionLoguSoru-cevap kaydırr r r pdf_path page_numberquestionquestion_englishanswer_englishanswer_turkish confidenceresponse_time_msrNrrrrrr)s  rc@seZdZdZefdefddZddZddZ dKd edededede dede de de dede fddZ dLd ededede dedededed ed!ed"e dede fd#d$Z dMd ed&e de efd'd(ZdMd ed&e de efd)d*Zdefd+d,ZdNded ed&e de efd.d/Z dOd0ed1ed2ed3e d4ed ededede fd5d6Z 7 dPd8ed9ed:ed;ede f dz3AnalyticsDB.get_popular_queries..r&r3r4fetchallr)r rEr3rrrget_popular_queries  zAnalyticsDB.get_popular_queriescCrF)uEn popüler soruları getira SELECT question, answer_turkish, COUNT(*) as count, brand, model FROM question_logs WHERE brand = ? GROUP BY question ORDER BY count DESC LIMIT ? z SELECT question, answer_turkish, COUNT(*) as count, brand, model FROM question_logs GROUP BY question ORDER BY count DESC LIMIT ? cSrGrrHrJrrrrM8rNz5AnalyticsDB.get_popular_questions..rOrQrrrget_popular_questionsrSz!AnalyticsDB.get_popular_questionscCs|jsiS|j}|d|d}|d|d}|ddd|D}|d|d}|d|d}|||||d S) zGenel istatistikleri getirz SELECT COUNT(*) FROM search_logsrz"SELECT COUNT(*) FROM question_logsz SELECT brand, COUNT(*) as count FROM search_logs GROUP BY brand ORDER BY count DESC cSrGrrHrJrrrrMPrNz)AnalyticsDB.get_stats..zh SELECT COUNT(*) FROM search_logs WHERE DATE(created_at) = DATE('now') zj SELECT COUNT(*) FROM question_logs WHERE DATE(created_at) = DATE('now') )total_searchestotal_questionstoday_searchestoday_questionsbrands)r&r3r4fetchonerP)r)r3rUrVrYrWrXrrr get_stats:s&          zAnalyticsDB.get_statsc Cs|jsgS|j}|}ddd|D}dd|D}|s%gSd|d}|r6|d7}|||d|7}|||d d|DS) uABenzer soruları ve cevapları bul (cache olarak kullanılabilir)z OR cSsg|]}dqS)zLOWER(question) LIKE ?r)rK_rrrrMpsz6AnalyticsDB.find_similar_questions..cSs$g|]}t|dkrd|dqS)%)len)rKwrrrrMqs$z SELECT question, answer_turkish, confidence, pdf_path, page_number, brand, model FROM question_logs WHERE (z ) z AND brand = ?z ORDER BY created_at DESC LIMIT cSrGrrHrJrrrrMrN)r&r3lowersplitjoinappendr4rP) r)rr rEr3words conditionsparamsqueryrrrfind_similar_questionsgs"    z"AnalyticsDB.find_similar_questions feedback_type original_texttranslated_textratinguser_suggestionc Cs|jsdS|sttdd}z8|j} | d||||||||f|j|dkrF|rF|j|dd|ddd|d|d | jWSt yc} zt d | WYd} ~ dSd} ~ ww) u Ç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 rNr<z INSERT INTO translation_feedback (session_id, feedback_type, original_text, translated_text, rating, user_suggestion, brand, model) VALUES (?, ?, ?, ?, ?, ?, ?, ?) r^user ) english_term turkish_term suggested_bycontextu⚠️ Feedback log hatası: ) r&rr>r?r3r4r5add_dictionary_suggestionr@r1r0) r)rkrlrmrnror r r r3r2rrrlog_translation_feedbacks2     z$AnalyticsDB.log_translation_feedbackrqrsrtrurvc Cs|jsdSzA|j}|d||f|}|r0|d|df|j|dWS|d||||f|j|jWSty`}ztd|WYd}~dSd}~ww)u Sözlük önerisi ekle veya var olanın oyunu artır. Returns: Eklenen/güncellenen kaydın ID'si rz SELECT id, vote_count FROM dictionary_suggestions WHERE LOWER(english_term) = LOWER(?) AND LOWER(turkish_term) = LOWER(?) z UPDATE dictionary_suggestions SET vote_count = vote_count + 1 WHERE id = ? z INSERT INTO dictionary_suggestions (english_term, turkish_term, suggested_by, context) VALUES (?, ?, ?, ?) u ⚠️ Sözlük öneri hatası: N) r&r3r4striprZr5r@r1r0)r)rsrtrurvr3existingr2rrrrws0     z%AnalyticsDB.add_dictionary_suggestioncC4|jsgS|j}|d|fdd|DS)u7Düşük puanlı çevirileri getir (iyileştirme için)a2 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 ? cSrGrrHrJrrrrMrNz:AnalyticsDB.get_low_rated_translations..rOr)rEr3rrrget_low_rated_translationss  z&AnalyticsDB.get_low_rated_translations2cCr|)u)Onay bekleyen sözlük önerilerini getirz SELECT id, english_term, turkish_term, vote_count, context, created_at FROM dictionary_suggestions WHERE is_approved = 0 ORDER BY vote_count DESC LIMIT ? cSrGrrHrJrrrrMrNz7AnalyticsDB.get_pending_suggestions..rOr}rrrget_pending_suggestionss z#AnalyticsDB.get_pending_suggestions suggestion_idcCs@|jsdSz|j}|d|f|jWdSYdS)uSözlük önerisini onaylaFz~ UPDATE dictionary_suggestions SET is_approved = 1 WHERE id = ? T)r&r3r4r5)r)rr3rrrapprove_suggestions  zAnalyticsDB.approve_suggestioncCs|jsiS|j}|d|dpd}|ddd|D}|d|d}|d|d}|d|d}t|d ||||d S) zFeedback istatistikleriz,SELECT AVG(rating) FROM translation_feedbackrz SELECT rating, COUNT(*) as count FROM translation_feedback GROUP BY rating ORDER BY rating cSsi|] }|d|dqS)rrrJrrr 5sz2AnalyticsDB.get_feedback_stats..z)SELECT COUNT(*) FROM translation_feedbackz+SELECT COUNT(*) FROM dictionary_suggestionszASELECT COUNT(*) FROM dictionary_suggestions WHERE is_approved = 1r^)average_ratingrating_distributiontotal_feedbacktotal_suggestionsapproved_suggestions)r&r3r4rZrPround)r)r3 avg_rating rating_distrrrrrrget_feedback_stats#s&         zAnalyticsDB.get_feedback_statscCs|jr |jd|_dSdS)uBağlantıyı kapatN)r&close)r)rrrrKs  zAnalyticsDB.close)r6rr6rr7rN)r6r6r6r6r6rN)NrD)Nr\)r6r6r6N)rqr6)r{)r)rrrrDB_PATHrr*r'r(rfloatrArCrrrRrTr[rjrxrwr~rboolrrrrrrrr$;s _    1      . -$  = 2 (r$__main__JCBJS330uhidrolik pompa arızasızhydraulic pump failurerDi)r r r r rrz/mnt/pdfs/JCB/JS330/service.pdfiurelief valve basıncı ne?z"what is the relief valve pressure?z%The relief valve pressure is 350 bar.u%Emniyet valfi basıncı 350 bar'dır.high) r r rrrrr r!r"u 📊 İstatistikler:u 🔍 Popüler Aramalar:)rosr,r>rtypingrrr dataclassesrpathrddirname__file__rrrr$rdbrArCr0r[rRrrrrrsX