+
    ö]‰i�P  ã            
       ój  € R t ^ RIt^ RIt^ RIt^ RIHtHt ^ RIHt ]P                  P                  ^ ]P                  P                  ]P                  P                  ]P                  P                  ]4      4      4      4       ^ RIHtHtHtHtHtHtHtHt R tR tR tR tR	 tR
 tR R ltR4R R lltR R ltR R ltR R lt R R lt!R R lt"R R lt#R R lt$R R lt%R R  lt&R! R" lt'R5R# R$ llt(R5R% R& llt)R5R' R( llt*R) R* lt+R6R+ R, llt,R- R. lt-R/ t.R5R0 R1 llt/R6R2 R3 llt0R# )7uM   
æœˆæ¬¡ãƒ¬ãƒ�ãƒ¼ãƒˆç”Ÿæˆ�ã‚µãƒ¼ãƒ“ã‚¹ - ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹æŽ¥ç¶šãƒ»ã‚¯ã‚¨ãƒª
N)ÚdatetimeÚ	timedelta)Ú
monthrange)ÚDB_TYPEÚSQLITE_PATHÚPG_HOSTÚPG_PORTÚ	PG_DBNAMEÚPG_USERÚPG_PASSWORDÚUNANSWERED_PATTERNSc            	     ó  € \         R8X  d1   ^ RIp V P                  \        \        \
        \        \        R7      pV# \        p\        P                  P                  V4      '       g|   \        P                  P                  \        P                  P                  \        P                  P                  \        P                  P                  \        4      4      4      V4      p\         P                  ! V4      p\         P"                  Vn        V# )u)   DBæŽ¥ç¶šã‚’å�–å¾—ï¼ˆèª­ã�¿å�–ã‚Šå°‚ç”¨ï¼‰ÚpgsqlN)ÚhostÚportÚdbnameÚuserÚpassword)r   Úpsycopg2Úconnectr   r   r	   r
   r   r   ÚosÚpathÚisabsÚjoinÚdirnameÚabspathÚ__file__Úsqlite3ÚRowÚrow_factory)r   ÚconnÚdb_paths      ÚBC:\Users\tmash\Documents\claude_code\chat\reports\core\database.pyÚget_connectionr#      s®   € ä�'ÔÛØ×ÑÜÜÜÜÜ ð  ó 
ˆð ˆô ˆÜ�w‰w�}‰}˜W×%Ò%Ü—g‘g—l‘l¤2§7¡7§?¡?´2·7±7·?±?Ä2Ç7Á7Ç?Á?ÔS[ÓC\Ó3]Ó#^Ð`gÓhˆGÜ�Š˜wÓ'ˆÜ"Ÿ;™;ˆÔØˆó    c                óŒ   € \         R8X  d+   ^ RIpV P                  VP                  P                  R7      # V P                  4       # )u6   ã‚«ãƒ¼ã‚½ãƒ«ã‚’å�–å¾—ï¼ˆPostgreSQLã�¯RealDictCursorï¼‰r   N)Úcursor_factory)r   Úpsycopg2.extrasÚcursorÚextrasÚRealDictCursor)r    r   s   & r"   Ú
get_cursorr+   (   s4   € ä�'ÔÛØ�{‰{¨(¯/©/×*HÑ*Hˆ{ÓIÐIØ�;‰;‹=Ðr$   c                 ó    € \         R8X  d   R# R# )u2   DBã‚¿ã‚¤ãƒ—ã�«å¿œã�˜ã�Ÿæ—¥ä»˜ã‚­ãƒ£ã‚¹ãƒˆã‚’è¿”ã�™r   ztimestamp::datezDATE(timestamp)©r   © r$   r"   Ú_get_date_castr/   0   s   € ä�'ÔÙ Ùr$   c                 ó    € \         R8X  d   R# R# )u,   DBã‚¿ã‚¤ãƒ—ã�«å¿œã�˜ã�Ÿæ™‚é–“æŠ½å‡ºã‚’è¿”ã�™r   z%EXTRACT(HOUR FROM timestamp)::INTEGERz*CAST(strftime('%H', timestamp) AS INTEGER)r-   r.   r$   r"   Ú_get_hour_extractr1   7   s   € ä�'ÔÙ6Ù7r$   c                 ó    € \         R8X  d   R# R# )ud   DBã‚¿ã‚¤ãƒ—ã�«å¿œã�˜ã�Ÿæ›œæ—¥æŠ½å‡ºã‚’è¿”ã�™ï¼ˆ0=æ—¥æ›œ, 6=åœŸæ›œ â†’ 0=æœˆæ›œ, 6=æ—¥æ›œã�«å¤‰æ�›ï¼‰r   z/(EXTRACT(DOW FROM timestamp)::INTEGER + 6) %% 7z4(CAST(strftime('%w', timestamp) AS INTEGER) + 6) % 7r-   r.   r$   r"   Ú_get_day_of_week_extractr3   >   s   € ä�'Ôñ AáAr$   c                 ó°   € . p \          F:  p\        R8X  d   V P                  RV R24       K%  V P                  RV R24       K<  	  RP                  V 4      # )u$   æœªå›žç­”åˆ¤å®šã�®SQLæ�¡ä»¶ã‚’æ§‹ç¯‰r   zassistant_message LIKE '%%z%%'zassistant_message LIKE '%z%'z OR )r   r   Úappendr   )Ú
conditionsÚpatterns     r"   Ú_build_unanswered_conditionr8   H   sZ   € à€Jß&ˆä�gÔØ×ÑÐ :¸7¸)À3ÐGÖHà×ÑÐ 9¸'¸À"ÐEÖFñ 'ð �;‰;�zÓ"Ð"r$   c                ó$   € V ^8„  d   QhR\         /# )é   Úquery©Ústr)Úformats   "r"   Ú__annotate__r?   T   s   € ÷ ñ œ#ñ r$   c                ó@   € \         R8X  d   V P                  RR4      # V # )u5   DBã‚¿ã‚¤ãƒ—ã�«å¿œã�˜ã�¦ãƒ—ãƒ¬ãƒ¼ã‚¹ãƒ›ãƒ«ãƒ€ã‚’å¤‰æ�›r   Ú?z%s)r   Úreplace)r;   s   &r"   Ú_prepare_queryrC   T   s    € ä�'Ôà�}‰}˜S $Ó'Ð'Ø€Lr$   c                ó0   € V ^8„  d   QhR\         R\        /# )r:   r;   Úparams)r=   Útuple)r>   s   "r"   r?   r?   \   s   € ÷ ,ñ ,œCð ,¬ñ ,r$   c                ó>   € \        V4      pV P                  W24       R# )u9   ã‚¯ã‚¨ãƒªã‚’å®Ÿè¡Œï¼ˆãƒ—ãƒ¬ãƒ¼ã‚¹ãƒ›ãƒ«ãƒ€å¤‰æ�›ä»˜ã��ï¼‰N)rC   Úexecute)r(   r;   rE   Úconverted_querys   &&& r"   Ú_executerJ   \   s   € ä$ UÓ+€OØ
‡N�N�?Ö+r$   c                ó0   € V ^8„  d   QhR\         R\         /# ©r:   Ú
start_dateÚend_dater<   )r>   s   "r"   r?   r?   b   s   € ÷ 'ñ '¤#ð '´ñ 'r$   c                óæ   € \        4       p\        V4      p\        4       pRV R2p\        W5W34       VP	                  4       pVP                  4        V Uu. uF  p\        V4      NK  	  up# u upi )u   æœŸé–“å†…ã�®ä¼šè©±ã‚’å�–å¾—zŒ
        SELECT id, session_id, user_message, assistant_message, timestamp, ip_address, user_agent
        FROM conversations
        WHERE ú4 BETWEEN ? AND ?
        ORDER BY timestamp ASC
    ©r#   r+   r/   rJ   ÚfetchallÚcloseÚdict©rM   rN   r    r(   Ú	date_castr;   ÚrowsÚrows   &&      r"   Úget_conversationsrY   b   ót   € äÓ€DÜ˜Ó€FÜÓ €Iðð ˆkð ð€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„LÙ!%Ó&¡˜#ŒD�ŽI¡Ñ&Ð&ùÒ&ó   ÁA.c                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   t   s   € ÷ 'ñ 'œcð '¬Sñ 'r$   c                óæ   € \        4       p\        V4      p\        4       pRV R2p\        W5W34       VP	                  4       pVP                  4        V Uu. uF  p\        V4      NK  	  up# u upi )u*   æœŸé–“å†…ã�®ãƒ•ã‚£ãƒ¼ãƒ‰ãƒ�ãƒƒã‚¯ã‚’å�–å¾—zw
        SELECT id, session_id, feedback, question, answer, timestamp, ip_address
        FROM feedbacks
        WHERE rP   rQ   rU   s   &&      r"   Úget_feedbacksr^   t   rZ   r[   c                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   †   s   € ÷ Jñ J¬#ð J¼ñ Jr$   c                ó2  € \        4       p\        V4      p\        4       p\        4       p/ pRV R2p\	        W7W34       VP                  4       R,          VR&   RV RV R2p\	        W7W34       VP                  4       R,          VR&   RV RV R2p\	        W7W34       VP                  4       R,          VR	&   R
V R2p\	        W7W34       VP                  4       R,          VR&   RV R2p\	        W7W34       VP                  4       R,          VR&   RV R2p\	        W7W34       VP                  4       R,          VR&   RV R2p\	        W7W34       VP                  4       R,          VR&   VR,          VR,          ,           VR&   VP                  4        V# )u   æœŸé–“ã�®çµ±è¨ˆã‚µãƒžãƒªãƒ¼zC
        SELECT COUNT(*) as count FROM conversations
        WHERE ú BETWEEN ? AND ?
    ÚcountÚtotal_conversationsz" BETWEEN ? AND ?
        AND NOT (ú)
    Úanswered_countú BETWEEN ? AND ?
        AND (Úunanswered_countzU
        SELECT COUNT(DISTINCT session_id) as count FROM conversations
        WHERE Úunique_sessionsz?
        SELECT COUNT(*) as count FROM feedbacks
        WHERE z3 BETWEEN ? AND ?
        AND feedback = 'like'
    Úlikes_countz6 BETWEEN ? AND ?
        AND feedback = 'dislike'
    Údislikes_countzU
        SELECT COUNT(DISTINCT ip_address) as count FROM conversations
        WHERE zM BETWEEN ? AND ?
        AND ip_address IS NOT NULL AND ip_address != ''
    Ú
unique_ipsÚtotal_feedbacks)r#   r+   r/   r8   rJ   ÚfetchonerS   )rM   rN   r    r(   rV   Úunanswered_condÚstatsr;   s   &&      r"   Úget_statistics_for_periodrp   †   s  € äÓ€DÜ˜Ó€FÜÓ €IÜ1Ó3€Oà€Eðàˆkð ð€Eô ˆV˜ZÐ2Ô3Ø#)§?¡?Ó#4°WÕ#=€EÐ
Ñ ðàˆkð Ø!Ð"ð #ð€Eô
 ˆV˜ZÐ2Ô3Ø$Ÿo™oÓ/°Õ8€EÐ
Ñðàˆkð ØÐð ð€Eô
 ˆV˜ZÐ2Ô3Ø &§¡Ó 1°'Õ :€EÐ
Ñðàˆkð ð€Eô ˆV˜ZÐ2Ô3Ø%Ÿ™Ó0°Õ9€EÐ
Ñðàˆkð ð€Eô
 ˆV˜ZÐ2Ô3Ø!Ÿ?™?Ó,¨WÕ5€Eˆ-Ñðàˆkð ð€Eô
 ˆV˜ZÐ2Ô3Ø$Ÿo™oÓ/°Õ8€EÐ
Ñðàˆkð ð€Eô
 ˆV˜ZÐ2Ô3Ø Ÿ/™/Ó+¨GÕ4€Eˆ,Ñð  % ]Õ3°eÐ<LÕ6MÕM€EÐ
Ñà‡J�J„LØ€Lr$   c                ó0   € V ^8„  d   QhR\         R\         /# )r:   ÚyearÚmonth)Úint)r>   s   "r"   r?   r?   Ó   s   € ÷ $ñ $¤ð $¬Sñ $r$   c                óî  € \        W4      w  r#V  RVR R2pV  RVR RVR 2pV^8X  d   V ^,
          p^pMT pV^,
          p\        Wg4      w  r(V RVR R2p	V RVR RVR 2p
\        WE4      p\        Wš4      pR pRVRVRRV! VR,          VR,          4      R	V! VR	,          VR	,          4      R
V! VR
,          VR
,          4      RV! VR,          VR,          4      RV! VR,          VR,          4      //# )u   å‰�æœˆæ¯”è¼ƒãƒ‡ãƒ¼ã‚¿Ú-Ú02dz-01c                 óf   € V^ 8X  d   V ^ 8”  d   ^d# ^ # \        W,
          V,          ^d,          ^4      # )é    )Úround)ÚcurrentÚpreviouss   &&r"   Úcalc_changeÚ+get_monthly_comparison.<locals>.calc_changeè   s4   € Ø�qŒ=Ø! Aœ+�3Ð,¨1Ð,Ü�gÕ(¨HÕ4°sÕ:¸AÓ>Ð>r$   r{   r|   Úchangesrc   re   rg   ri   rj   )r   rp   )rr   rs   Ú_Úlast_dayÚcurrent_startÚcurrent_endÚ	prev_yearÚ
prev_monthÚprev_last_dayÚ
prev_startÚprev_endÚcurrent_statsÚ
prev_statsr}   s   &&            r"   Úget_monthly_comparisonr‹   Ó   sP  € ô ˜TÓ)�K€AØ�f˜A˜e C˜[¨Ð,€MØ�F˜!˜E #˜; a¨° ~Ð6€Kð �„zØ˜1•Hˆ	Ø‰
àˆ	Ø˜Q•Yˆ
Ü! )Ó8Ñ€AØ�;˜a 
¨3Ð/¨sÐ3€JØ�˜A˜j¨Ð-¨Q¨}¸SÐ.AÐB€Hä-¨mÓI€MÜ*¨:Ó@€Jò?ð 	�=Ø�JØØ!¡;¨}Ð=RÕ/SÐU_Ð`uÕUvÓ#wØ™k¨-Ð8HÕ*IÈ:ÐVfÕKgÓhØ¡¨MÐ:LÕ,MÈzÐZlÕOmÓ nØ™; }°]Õ'CÀZÐP]ÕE^Ó_Ø™k¨-Ð8HÕ*IÈ:ÐVfÕKgÓhð
ð
ð 
r$   c                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   ú   s   € ÷ ñ ¤Sð ´Cñ r$   c                ó’  € \        4       p\        V4      p\        4       p\        4       pRV RV RV RV RV RV R2p\	        W6W34       VP                  4       pVP                  4        . pV FS  p	\        V	4      p
\        V
R,          R	4      '       d   V
R,          P                  R
4      V
R&   VP                  V
4       KU  	  V# )u   æ—¥åˆ¥çµ±è¨ˆú
        SELECT
            z� as date,
            COUNT(*) as total_conversations,
            COUNT(DISTINCT session_id) as unique_sessions,
            SUM(CASE WHEN (zI) THEN 1 ELSE 0 END) as unanswered_count,
            SUM(CASE WHEN NOT (zP) THEN 1 ELSE 0 END) as answered_count
        FROM conversations
        WHERE ú" BETWEEN ? AND ?
        GROUP BY ú
        ORDER BY ú	 ASC
    ÚdateÚstrftimeú%Y-%m-%d)r#   r+   r/   r8   rJ   rR   rS   rT   Úhasattrr“   r5   )rM   rN   r    r(   rV   rn   r;   rW   ÚresultrX   Úds   &&         r"   Úget_daily_statisticsr˜   ú   sè   € äÓ€DÜ˜Ó€FÜÓ €IÜ1Ó3€OðàˆKð ð ,Ð,ð - Ø /Ð0ð 1àˆkð Ø�ð Ø�ð ð€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„Là€FÛˆÜ�‹Iˆä�1�V•9˜j×)Ò)Ø˜&�	×*Ñ*¨:Ó6ˆAˆf‰IØ�‰�aÖñ ð €Mr$   c                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?     s   € ÷ 'ñ '¤cð '´Sñ 'r$   c           	     ó   € \        4       p\        V4      p\        4       p\        4       p\	        4       pRV RV RV RV R2	p\        W7W34       VP                  4       pVP                  4        V U	u. uF  p	\        V	4      NK  	  up	# u up	i )u   æ™‚é–“å¸¯åˆ¥çµ±è¨ˆrŽ   zR as hour,
            COUNT(*) as total_conversations,
            SUM(CASE WHEN (úR) THEN 1 ELSE 0 END) as unanswered_count
        FROM conversations
        WHERE r�   z
        ORDER BY hour ASC
    ©	r#   r+   r/   r1   r8   rJ   rR   rS   rT   ©
rM   rN   r    r(   rV   Úhour_extractrn   r;   rW   rX   s
   &&        r"   Úget_hourly_statisticsrŸ     s¡   € äÓ€DÜ˜Ó€FÜÓ €IÜ$Ó&€LÜ1Ó3€OðàˆNð à+Ð,ð -àˆkð Ø�ð  ð	€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„LÙ!%Ó&¡˜#ŒD�ŽI¡Ñ&Ð&ùÒ&ó   Á4Bc                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   3  s   € ÷ 'ñ '¬3ð '¼#ñ 'r$   c           	     ó   € \        4       p\        V4      p\        4       p\        4       p\	        4       pRV RV RV RV R2	p\        W7W34       VP                  4       pVP                  4        V U	u. uF  p	\        V	4      NK  	  up	# u up	i )u'   æ›œæ—¥åˆ¥çµ±è¨ˆï¼ˆæœˆæ›œ=0, æ—¥æ›œ=6ï¼‰rŽ   zY as day_of_week,
            COUNT(*) as total_conversations,
            SUM(CASE WHEN (r›   r�   z&
        ORDER BY day_of_week ASC
    )	r#   r+   r/   r3   r8   rJ   rR   rS   rT   )
rM   rN   r    r(   rV   Údow_extractrn   r;   rW   rX   s
   &&        r"   Úget_day_of_week_statisticsr¤   3  s¡   € äÓ€DÜ˜Ó€FÜÓ €IÜ*Ó,€KÜ1Ó3€OðàˆMð à+Ð,ð -àˆkð Ø�ð ð	€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„LÙ!%Ó&¡˜#ŒD�ŽI¡Ñ&Ð&ùÒ&r    c                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   K  s   € ÷ 'ñ '¬ð '´sñ 'r$   c                ó&  € \        4       p\        V4      p\        4       p\        4       p\	        4       pRV RV RV RV RV R2p\        W7W34       VP                  4       pVP                  4        V U	u. uF  p	\        V	4      NK  	  up	# u up	i )u2   æ›œæ—¥Ã—æ™‚é–“å¸¯åˆ†å¸ƒï¼ˆãƒ’ãƒ¼ãƒˆãƒžãƒƒãƒ—ç”¨ï¼‰rŽ   z as day_of_week,
            zQ as hour,
            COUNT(*) as count
        FROM conversations
        WHERE r�   z, z(
        ORDER BY day_of_week, hour
    )	r#   r+   r/   r1   r3   rJ   rR   rS   rT   )
rM   rN   r    r(   rV   rž   r£   r;   rW   rX   s
   &&        r"   Úget_hour_by_day_of_weekr§   K  s¨   € äÓ€DÜ˜Ó€FÜÓ €IÜ$Ó&€LÜ*Ó,€KðàˆMð ØˆNð ð ˆkð Ø�˜b  ð /ð	€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„LÙ!%Ó&¡˜#ŒD�ŽI¡Ñ&Ð&ùÒ&s   Á7Bc                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   c  s   € ÷ ñ ¤#ð ´ñ r$   c           
     ó’  € \        4       p\        V4      p\        4       pRV R2p\        W5W34       VP	                  4       pVP                  4        V'       g   R^ R^ R^ R^ /# V Uu. uF  qwR,          NK  	  ppR\        \        V4      \        V4      ,          ^4      R\        V4      R\        V4      R\        V4      /# u upi )u6   å¹³å�‡ä¼šè©±ã‚¿ãƒ¼ãƒ³æ•°ï¼ˆã‚»ãƒƒã‚·ãƒ§ãƒ³ã�‚ã�Ÿã‚Šï¼‰zt
        SELECT
            session_id,
            COUNT(*) as turn_count
        FROM conversations
        WHERE z1 BETWEEN ? AND ?
        GROUP BY session_id
    ÚaverageÚmaxÚminÚtotal_sessionsÚ
turn_count)r#   r+   r/   rJ   rR   rS   rz   ÚsumÚlenr«   r¬   )	rM   rN   r    r(   rV   r;   rW   rX   Úturnss	   &&       r"   Úget_average_turnsr²   c  sÐ   € äÓ€DÜ˜Ó€FÜÓ €Iðð
 ˆkð ð€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„LçØ˜1˜e Q¨¨qÐ2BÀAÐFÐFá*.Ó/©$ 3�×Ð©$€EÐ/à”5œ˜U›¤c¨%£jÕ0°!Ó4ØŒs�5‹zØŒs�5‹zØœ#˜e›*ð	ð ùò 0s   Á)Cc                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   �  s   € ÷ 'ñ '¤sð '´cñ 'r$   c           	     ó   € \        4       p\        V4      p\        4       p\        4       p\	        4       pRV RV RV RV R2	p\        W7W34       VP                  4       pVP                  4        V U	u. uF  p	\        V	4      NK  	  up	# u up	i )u   æœªè§£æ±ºã�Œå¤šã�„æ™‚é–“å¸¯rŽ   z\ as hour,
            COUNT(*) as unanswered_count
        FROM conversations
        WHERE rf   z)
        GROUP BY z,
        ORDER BY unanswered_count DESC
    rœ   r�   s
   &&        r"   Úget_unresolved_by_hourrµ   �  s£   € äÓ€DÜ˜Ó€FÜÓ €IÜ$Ó&€LÜ1Ó3€OðàˆNð ð ˆkð ØÐð Ø�ð  ð	€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„LÙ!%Ó&¡˜#ŒD�ŽI¡Ñ&Ð&ùÒ&r    c                ó<   € V ^8„  d   QhR\         R\         R\        /# ©r:   rM   rN   Úlimit©r=   rt   )r>   s   "r"   r?   r?   ™  s&   € ÷ Kñ KœSð K¬Cð K¼ñ Kr$   c                óô   € \        4       p\        V4      p\        4       pRV R2p\        WFW34       VP	                  4       pVP                  4        \        V Uu. uF  qˆR,          NK  	  upV4      # u upi )u   é »å‡ºã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ú>
        SELECT user_message FROM conversations
        WHERE ra   Úuser_message)r#   r+   r/   rJ   rR   rS   Ú_extract_keywords©	rM   rN   r¸   r    r(   rV   r;   rW   rX   s	   &&&      r"   Úget_keywordsr¿   ™  s{   € äÓ€DÜ˜Ó€FÜÓ €Iðàˆkð ð€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„Lä¹TÓB¹T°c .×1Ð1¹TÑBÀEÓJÐJùÒBs   ÁA5c                ó<   € V ^8„  d   QhR\         R\         R\        /# r·   r¹   )r>   s   "r"   r?   r?   ª  s&   € ÷ Kñ K¬ð K´sð KÄ3ñ Kr$   c                ó  € \        4       p\        V4      p\        4       p\        4       pRV RV R2p\	        WGW34       VP                  4       pVP                  4        \        V U	u. uF  q™R,          NK  	  up	V4      # u up	i )u!   æœªè§£æ±ºã�«å¤šã�„ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰r»   rf   rd   r¼   )r#   r+   r/   r8   rJ   rR   rS   r½   )
rM   rN   r¸   r    r(   rV   rn   r;   rW   rX   s
   &&&       r"   Úget_unresolved_keywordsrÂ   ª  sŽ   € äÓ€DÜ˜Ó€FÜÓ €IÜ1Ó3€Oðàˆkð ØÐð ð€Eô
 ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„Lä¹TÓB¹T°c .×1Ð1¹TÑBÀEÓJÐJùÒBs   Á)Bc                ó0   € V ^8„  d   QhR\         R\        /# )r:   Úmessagesr¸   )Úlistrt   )r>   s   "r"   r?   r?   ½  s   € ÷ #ñ #¤ð #¬Sñ #r$   c                ó¤  € ^ RI p0 RkRkRkRkRkRkRkR	kR
kRkRkRkRkRkRkRkRkRkRkRkRkRkRkRkRkRkRkRkRkRkR kR!kR"kR#kp/ pV  F]  pV'       g   K  VP                  R$V4      pV F8  pWs9  g   K  \        V4      ^8¼  g   K  VP                  V^ 4      ^,           WG&   K:  	  K_  	  \	        VP                  4       R% R&R'7      pVRV # )(u-   ãƒ¡ãƒƒã‚»ãƒ¼ã‚¸ã�‹ã‚‰ã‚­ãƒ¼ãƒ¯ãƒ¼ãƒ‰ã‚’æŠ½å‡ºNu   ã�¯u   ã�Œu   ã�®u   ã�«u   ã‚’u   ã�§u   ã�¨u   ã�§ã�™u   ã�¾ã�™u   ã�‹u	   ã�§ã�™ã�‹u	   ã�¾ã�™ã�‹u   ã�«ã�¤ã�„ã�¦u   ã�¨ã�¯u   ã�ªã‚“u   ã�©ã�†u   ã�©ã�®u   ã�“ã�®u   ã��ã�®u   ã�„ã‚‹u   ã�‚ã‚‹u   ã�™ã‚‹u   ã�ªã‚‹u	   ã�§ã��ã‚‹u   ã‚Œã‚‹u	   ã‚‰ã‚Œã‚‹u   ã�ªã�„u   ã�Ÿã�„u	   ã�»ã�—ã�„u   ã��ã� ã�•ã�„u   ã�Šã�­ã�Œã�„u	   ã�—ã�¾ã�†u   ã�¦ã‚‹u   ã�¦ã�„u*   [ã��-ã‚“ã‚¡-ãƒ¶ãƒ¼ä¸€-é¾ ã€…ã€†ãƒµãƒ¶]{2,}c                 ó   € V ^,          # ©é   r.   ©Úxs   &r"   Ú<lambda>Ú#_extract_keywords.<locals>.<lambda>Ñ  s   € ¸Q¸q¾Tr$   T©ÚkeyÚreverse)ÚreÚfindallr°   ÚgetÚsortedÚitems)	rÄ   r¸   rÑ   Ú
stop_wordsÚkeywordsÚmessageÚwordsÚwordÚsorted_keywordss	   &&       r"   r½   r½   ½  sÈ  € ãðj�%ð j˜ð j ð j uð j¨eð j°Uð j¸Eð jÀ8ð jÈXð jÐW\ð jÐ^ið jØðjØ-ðjØ/7ðjØ9AðjØCKðjØMUðjØW_ðjØaiðjàðjà$ðjà&.ðjà08ðjà:EðjàGOðjàQ\ðjà^fðjð ðjð (ðjð *8ðjð :Hðjð JUðjð W_ðjð aiðj€Jð
 €HÛˆßÙà—
‘
ÐHÈ'ÓRˆÛˆDØÖ%¬#¨d«)°q®.Ø!)§¡¨d°AÓ!6¸Õ!:�“ó ñ ô ˜XŸ^™^Ó-±>È4ÔP€OØ˜6˜EÐ"Ð"r$   c                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   Õ  s   € ÷ 'ñ '¬ð '¼ñ 'r$   c                ó   € \        4       p\        V4      p\        4       p\        4       pRV RV R2p\	        W6W34       VP                  4       pVP                  4        V Uu. uF  p\        V4      NK  	  up# u upi )u$   å›žç­”ã�§ã��ã�ªã�‹ã�£ã�Ÿè³ªå•�ä¸€è¦§z]
        SELECT user_message, timestamp, session_id
        FROM conversations
        WHERE rf   z&)
        ORDER BY timestamp DESC
    )r#   r+   r/   r8   rJ   rR   rS   rT   )	rM   rN   r    r(   rV   rn   r;   rW   rX   s	   &&       r"   Úget_unanswered_questionsrÞ   Õ  s‡   € äÓ€DÜ˜Ó€FÜÓ €IÜ1Ó3€Oðð ˆkð ØÐð ð	€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„LÙ!%Ó&¡˜#ŒD�ŽI¡Ñ&Ð&ùÒ&s   Á$A;c                ó<   € V ^8„  d   QhR\         R\         R\        /# r·   r¹   )r>   s   "r"   r?   r?   é  s!   € ÷ 'ñ '¤Cð '´3ð '¼sñ 'r$   c                óè   € \        4       p\        V4      p\        4       pRV R2p\        WFWV34       VP	                  4       pVP                  4        V Uu. uF  p\        V4      NK  	  up# u upi )u   ä½Žè©•ä¾¡ãƒ•ã‚£ãƒ¼ãƒ‰ãƒ�ãƒƒã‚¯zQ
        SELECT question, answer, timestamp
        FROM feedbacks
        WHERE zf BETWEEN ? AND ?
        AND feedback = 'dislike'
        ORDER BY timestamp DESC
        LIMIT ?
    rQ   r¾   s	   &&&      r"   Úget_recent_dislikedrá   é  sv   € äÓ€DÜ˜Ó€FÜÓ €Iðð ˆkð ð€Eô ˆV˜Z°5Ð9Ô:Ø�?‰?Ó€DØ‡J�J„LÙ!%Ó&¡˜#ŒD�ŽI¡Ñ&Ð&ùÒ&s   ÁA/c                ó0   € V ^8„  d   QhR\         R\         /# rL   r<   )r>   s   "r"   r?   r?   ý  s   € ÷ ñ ¬ð ¼ñ r$   c           	     ór  € \        4       p\        V4      p\        4       pRV RV RV RV R2	p\        W5W34       VP	                  4       pVP                  4        . pV FS  p\        V4      p	\        V	R,          R4      '       d   V	R,          P                  R4      V	R&   VP                  V	4       KU  	  V# )	u!   æ—¥åˆ¥ãƒ•ã‚£ãƒ¼ãƒ‰ãƒ�ãƒƒã‚¯çµ±è¨ˆrŽ   zå as date,
            SUM(CASE WHEN feedback = 'like' THEN 1 ELSE 0 END) as likes,
            SUM(CASE WHEN feedback = 'dislike' THEN 1 ELSE 0 END) as dislikes,
            COUNT(*) as total
        FROM feedbacks
        WHERE r�   r�   r‘   r’   r“   r”   )
r#   r+   r/   rJ   rR   rS   rT   r•   r“   r5   )
rM   rN   r    r(   rV   r;   rW   r–   rX   r—   s
   &&        r"   Úget_feedback_daily_statsrä   ý  sÊ   € äÓ€DÜ˜Ó€FÜÓ €IðàˆKð ð
 ˆkð Ø�ð Ø�ð ð
€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„Là€FÛˆÜ�‹IˆÜ�1�V•9˜j×)Ò)Ø˜&�	×*Ñ*¨:Ó6ˆAˆf‰IØ�‰�aÖñ	 ð
 €Mr$   c                 ó¸  € \         P                  P                  \         P                  P                  \         P                  P                  \        4      4      R4      p / p\         P                  P                  V 4      '       g   V#  \        V RRR7      ;_uu_ 4       pV Fq  pVP                  4       pRV9   g   K  VP                  R^4      w  rEVP                  R4      ^ ,          pWa9  g   KP  VP                  4       P                  R4      W&   Ks  	  RRR4       V#   + '       g   i     T# ; i  \         d   p\        R	T 24        Rp?T# Rp?ii ; i)
u<   category.txtã�‹ã‚‰ã‚«ãƒ†ã‚´ãƒªãƒžãƒƒãƒ”ãƒ³ã‚°ã‚’èª­ã�¿è¾¼ã�¿zcategory.txtÚrzutf-8)ÚencodingÚ:rv   Ú"Nu/   ã‚«ãƒ†ã‚´ãƒªãƒ•ã‚¡ã‚¤ãƒ«èª­ã�¿è¾¼ã�¿ã‚¨ãƒ©ãƒ¼: )r   r   r   r   r   ÚexistsÚopenÚstripÚsplitÚ	ExceptionÚprint)Úcategory_pathÚ
categoriesÚfÚlineÚcat_idÚcat_nameÚmain_idÚes           r"   Úload_category_mappingrø     s  € ä—G‘G—L‘L¤§¡§¡´·±·±ÄÓ1JÓ!KÈ^Ó\€MØ€Jä�7‰7�>‰>˜-×(Ò(ØÐðEÜ�- ¨w×7Õ7¸1Û�Ø—z‘z“|�Ø˜$–;Ø'+§z¡z°#°qÓ'9Ñ$�Fà$Ÿl™l¨3Ó/°Õ2�GØÖ0Ø.6¯n©nÓ.>×.DÑ.DÀSÓ.I˜
Ó+ñ ÷ 8ð Ð÷ 8Ö7ð Ðûô ô EÜÐ?À¸sÐC×DÐDàÐûðEúsB   Â	D5 ÂD!Â>/D!Ã2%D!ÄD5 Ä!D2	Ä,D5 Ä2D5 Ä5EÅ EÅEc                ó<   € V ^8„  d   QhR\         R\         R\        /# r·   r¹   )r>   s   "r"   r?   r?   3  s!   € ÷ ñ ¤Sð ´Cð Äñ r$   c                óš  € ^ RI p\        4       p\        V4      p\        4       pRV R2p\	        WWW34       VP                  4       pVP                  4        / p	V FL  p
V
R,          ;'       g    RpVP                  RV4      pV F  pV	P                  V^ 4      ^,           W�&   K   	  KN  	  \        V	P                  4       R RR	7      pVRV # )
u'   ã‚ˆã��å�‚ç…§ã�•ã‚Œã‚‹Q&Aãƒ©ãƒ³ã‚­ãƒ³ã‚°NúC
        SELECT assistant_message FROM conversations
        WHERE ra   Úassistant_messageÚ ú\*\*Q(\d+(?:-\d+)?):c                 ó   € V ^,          # rÈ   r.   rÊ   s   &r"   rÌ   Ú&get_qa_usage_ranking.<locals>.<lambda>M  s   € ¸¸!¾r$   TrÎ   )rÑ   r#   r+   r/   rJ   rR   rS   rÒ   rÓ   rÔ   rÕ   )rM   rN   r¸   rÑ   r    r(   rV   r;   rW   Ú	qa_countsrX   ÚmsgÚidsÚqa_idÚ	sorted_qas   &&&            r"   Úget_qa_usage_rankingr  3  sÔ   € ãäÓ€DÜ˜Ó€FÜÓ €Iðàˆkð ð€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„Lð €IÛˆØÐ%Õ&×,Ð,¨"ˆà�j‰jÐ0°#Ó6ˆÛˆEØ(Ÿ}™}¨U°AÓ6¸Õ:ˆIÓó ñ	 ô �y—‘Ó(©nÀdÔK€IØ�V�eÐÐr$   c                ó<   € V ^8„  d   QhR\         R\         R\        /# r·   r¹   )r>   s   "r"   r?   r?   Q  s!   € ÷ +ñ +¬3ð +¼#ð +Äcñ +r$   c                ó~  € ^ RI p\        4       p\        V4      p\        4       pRV R2p\	        WWW34       VP                  4       pVP                  4        \        4       p	/ p
V F‡  pVR,          ;'       g    RpVP                  RV4      pV FY  pVP                  R4      ^ ,          pV	P                  VRV 24      pWú9  d	   R	VR
^ /W¯&   W¯,          R
;;,          ^,          uu&   K[  	  K‰  	  \        V
P                  4       R RR7      pV UUu. uF  w  ppVVR	,          VR
,          3NK  	  pppVRV # u uppi )u'   ã‚«ãƒ†ã‚´ãƒªåˆ¥Q&Aå�‚ç…§ãƒ©ãƒ³ã‚­ãƒ³ã‚°Nrû   ra   rü   rý   rþ   rv   u   ã‚«ãƒ†ã‚´ãƒªÚnamerb   c                 ó"   € V ^,          R,          # )rÉ   rb   r.   rÊ   s   &r"   rÌ   Ú,get_category_usage_ranking.<locals>.<lambda>v  s   € �a˜•d˜7–mr$   TrÎ   )rÑ   r#   r+   r/   rJ   rR   rS   rø   rÒ   rí   rÓ   rÔ   rÕ   )rM   rN   r¸   rÑ   r    r(   rV   r;   rW   rñ   Úcategory_countsrX   r  r  r  rô   rõ   Úsorted_categoriesÚdatar–   s   &&&                 r"   Úget_category_usage_rankingr  Q  sT  € ãäÓ€DÜ˜Ó€FÜÓ €Iðàˆkð ð€Eô ˆV˜ZÐ2Ô3Ø�?‰?Ó€DØ‡J�J„Lô 'Ó(€Jð €OÛˆØÐ%Õ&×,Ð,¨"ˆà�j‰jÐ0°#Ó6ˆÛˆEà—[‘[ Ó% aÕ(ˆFØ!—~‘~ f°¸V¸HÐ.EÓFˆHàÔ,Ø+1°8¸WÀaÐ*H�Ñ'ØÕ# G×,°Õ1Õ,ó ñ	 ô Ø×ÑÓÙ#ØôÐñ IZÔZÑHY¹¸Àˆv�t˜F•| T¨'¥]Ó3ÑHY€FÑZØ�&�5ˆ>Ðùó [s   ÄD9)r.   )é   )é
   )1Ú__doc__r   r   Úsysr   r   Úcalendarr   r   Úinsertr   r   r   Úconfigr   r   r   r   r	   r
   r   r   r#   r+   r/   r1   r3   r8   rC   rJ   rY   r^   rp   r‹   r˜   rŸ   r¤   r§   r²   rµ   r¿   rÂ   r½   rÞ   rá   rä   rø   r  r  r.   r$   r"   Ú<module>r     sø   ðñó Û 	Û 
ß (Ý ð ‡�‡���2—7‘7—?‘? 2§7¡7§?¡?°2·7±7·?±?À8Ó3LÓ#MÓNÔ O÷÷ ó òò,òò8òBò	#õ÷,õ'õ$'õ$JõZ$õNõB'õ0'õ0'õ0õ<'÷0K÷"K÷&#õ0'÷('õ(ò<÷0÷<+ñ +r$   