
    Lj                     L    d Z ddlZddlZddlZg dZd Zd Zd Zd Zd Z	d	 Z
y)
ax  DB access for QuitSure AI Support (logging chats + feedback).

Follows the chatbot-onboarding / discovery pattern: PyMySQL, DictCursor, a fresh
connection per call, env creds (DB_HOST / DB_PORT / DB_DATABASE / DB_USERNAME /
DB_PASSWORD, loaded from .env by app.py). Naming follows the QuitSure convention:
tbl_ tables, Hungarian columns (iUserID, vMessage, bEscalate, dCreated ...).

Tables (tbl_SupportChatLog, tbl_SupportChatFeedback) are created by the BE/DBA team
in the test DB; see docs/DB_SCHEMA.sql. Writes are best-effort: logging must never
break a reply, so if the DB is unreachable or the tables don't exist, it no-ops.
    N))
message_id
vMessageID)user_idiUserID)programvProgram)platform	vPlatform)messagevMessage)retrieval_queryvRetrievalQuery)best_sim
decBestSim)escalate	bEscalate)escalate_reasonvEscalateReason)answervAnswer)gatevGatec                  D   t        j                  t        j                  d   t	        t        j                  j                  dd            t        j                  d   t        j                  d   t        j                  d   t         j                  j                  ddd	      S )	NDB_HOSTDB_PORTi  DB_USERNAMEDB_PASSWORDDB_DATABASE   )	hostportuserpassworddatabasecursorclassconnect_timeoutread_timeoutwrite_timeout)pymysqlconnectosenvironintgetcursors
DictCursor     &/var/www/python/quitsure-support/db.py_connr4      sp    ??ZZ	"	401ZZ&M*M*OO..
 
r2   c                  R    t        t        j                  j                  d            S )Nr   )boolr+   r,   r.   r1   r2   r3   enabledr7   (   s    

y)**r2   c                 D    	 t        |       S # t        t        f$ r Y y w xY w)N)r-   	TypeError
ValueError)vs    r3   _int_or_noner<   ,   s'    1vz" s   
 c            
      :   t               sy t        D ci c]  \  }}|| j                  |       }}}t        |d         |d<   t        D cg c]  \  }}|	 }}}	 t	               }	 |j                         5 }|j                  ddj                  |       ddj                  d |D               d|       d d d        |j                          |j                          y c c}}w c c}}w # 1 sw Y   6xY w# |j                          w xY w# t        $ r Y y w xY w)Nr   z INSERT INTO tbl_SupportChatLog (,z
) VALUES (c              3   ,   K   | ]  }d |z   dz     yw)z%(z)sNr1   ).0cs     r3   	<genexpr>zlog_chat.<locals>.<genexpr>>   s     /NAq4/Ns   ))r7   	_CHAT_MAPr.   r<   r4   cursorexecutejoincommitclose	Exception)fappdbvals_colsconncurs           r3   log_chatrS   3   s   9*34wsBBc
N4D4"4	?3DO%&51bB&D&
w	 Y#>sxx~>N O''*xx/N/N'N&OqRSWYY KKMJJL 5&Y Y
 JJL sM   C!C'
D )C9 9?C-8C9 D -C62C9 9DD 	DDc                 J   t               sy 	 t               }	 |j                         5 }|j                  d|xs dt	        |      ||f       d d d        |j                          |j                          y # 1 sw Y   *xY w# |j                          w xY w# t        $ r Y y w xY w)NzaINSERT INTO tbl_SupportChatFeedback (vMessageID, iUserID, vRating, vNote) VALUES (%s, %s, %s, %s)unknown)r7   r4   rE   rF   r<   rH   rI   rJ   )tsr   r   ratingnoterQ   rR   s          r3   log_feedbackrY   F   s    9w	 w# 68B8OiQ]^eQfhnpt7uww KKMJJLw w JJL s?   
B B $A5B $B 5A>:B BB 	B"!B"c            
      L   ddddg dddg d} t               s| S 	 t               }	 |j                         5 fd} |d      } |d      } |d      } |d      }j                  d	       j	                         D cg c]  }|d
   xs d|d   d }}j                  d       j	                         D ci c]  }|d   |d    }	}j                  d       j	                         D 
cg c])  }
t        |
d         |
d   |
d   |
d   xs dd d |
d
   d+ }}
d d d        	j                  dd      |	j                  dd      dd|j                          S c c}w c c}w c c}
w # 1 sw Y   UxY w# |j                          w xY w# t        $ r | cY S w xY w)Nr   )updown)totalanswereddeclined	escalatedreasonsfeedback	downvotesc                 z    j                  |        t        j                         j                               d   S )Nr   )rF   listfetchonevalues)sqlrR   s    r3   onezget_stats.<locals>.one`   s.    KK$ 5 5 78;;r2   z'SELECT COUNT(*) FROM tbl_SupportChatLogzuSELECT COUNT(*) FROM tbl_SupportChatLog WHERE bEscalate=0 AND (vEscalateReason IS NULL OR vEscalateReason='greeting')zLSELECT COUNT(*) FROM tbl_SupportChatLog WHERE vEscalateReason='out_of_scope'z9SELECT COUNT(*) FROM tbl_SupportChatLog WHERE bEscalate=1zxSELECT vEscalateReason, COUNT(*) AS c FROM tbl_SupportChatLog WHERE bEscalate=1 GROUP BY vEscalateReason ORDER BY c DESCr   rU   rA   )reasoncountzKSELECT vRating, COUNT(*) AS c FROM tbl_SupportChatFeedback GROUP BY vRatingvRatingzSELECT f.dCreated, f.vNote, c.vMessage, c.vAnswer, c.vEscalateReason FROM tbl_SupportChatFeedback f LEFT JOIN tbl_SupportChatLog c ON f.vMessageID=c.vMessageID WHERE f.vRating='down' ORDER BY f.dCreated DESC LIMIT 25dCreatedvNoter   r    i,  )rV   rX   r   r   rj   r[   r\   )	r7   r4   rE   rF   fetchallstrr.   rI   rJ   )emptyrQ   ri   r]   r^   r_   r`   rra   fbddownsrR   s               @r3   	get_statsrw   W   s   QAAq!(<2OE9w	 2#< EF  Y Zmn [\	 G Hcfcococqr^_a(9&:&GiRSTWRXYrrij47LLNCqa	lAcF*CC W X #&,,.2 !$AjM 2AgJSTU_S`%&y\%7R#$>!L]J^` 2 2%2* #h]f&266$?TVTZTZ[acdTe4f!&( JJL sC
2%2 22 JJL sj   
F F  A	E4 E%%E4;E*%E40.E/E4 4F  F %E44E=9F   FF F#"F#)__doc__r+   r)   pymysql.cursorsrD   r4   r7   r<   rS   rY   rw   r1   r2   r3   <module>rz      s:   
 
  	+&"#r2   