Ë
    F()jÌ~  ã            	       óD  — d dl Z d dlZd dlZd dlZd dlZd dlZd dlZd dlmZm	Z	m
Z
mZ d dlmZmZmZmZmZ d dlmZ d dlmZ d dlmZ d dlmZ d dlmZ  e j:                  e«      Zd d	l m Z m!Z! d
e"fd„Z#dde$de$de$fd„Z%de$de$fd„Z&de$fd„Z'de$fd„Z(dde$de$de$de$fd„Z)y)é    N)Úget_db_connectionÚupsert_thread_analyticsÚinsert_llm_usageÚbootstrap_tenant)Úget_chat_historyÚget_all_threadsÚget_total_ai_responses_countÚget_aggregated_ai_responsesÚget_latest_storage_snapshot)Úcheck_quota)Úclient)Úsettings)ÚANALYTICS_ANALYSIS_PROMPT)Úsql)ÚdatetimeÚ	timedeltaÚ
size_bytesc                 ó–   — | dk\  rt        | dz  d«      › d�S | dk\  rt        | dz  d«      › d�S | dk\  rt        | dz  d«      › d�S | › d�S )	zI
    Dynamically adapts units from GB down to Bytes for readability.
    i   @é   z GBi   z MBi   z KBz Bytes)Úround)r   s    ú5/var/www/html/strategist-ai/app/services/analytics.pyÚformat_storage_sizer      sw   € ð �WÒÜ˜
 gÑ.°Ó2Ð3°3Ð7Ð7Ø	�wÒ	Ü˜
 gÑ.°Ó2Ð3°3Ð7Ð7Ø	�tÒ	Ü˜
 TÑ)¨1Ó-Ð.¨cÐ2Ð2à�˜VÐ$Ð$ó    Ú
time_rangeÚstart_date_strÚend_date_strc                 óú  — t        j                  «       }|rB	 t        j                  |d«      }|r%t        j                  |d«      t        dd¬«      z   }n|}d} | dk7  r‚| d
k(  r|t        d¬«      z
  |}}nk| dk(  r|t        d¬«      z
  |}}nT| dk(  r|t        d¬«      z
  |}}n=| dk(  r|t        d¬«      z
  |}}n&| dk(  r|t        d¬«      z
  |}}nt        ddd«      |}}z
  }||z
  }|}	d|j                  d«      › d�}
d|j                  d«      › d�}d|j                  d«      › d�}d|	j                  d«      › d�}|j                  d«      |j                  d«      |j                  d«      |	j                  d«      dœ}|j                  }|dk  r| d
k(  rdnd| d
k(  rdnd}}n|dk  rd\  }}n|dk  rd \  }}nd!\  }}|
||||||fS # t        $ r&}t
        j                  d|› d�«       d} Y d	}~�Œœd	}~ww xY w)"z¶
    Returns (current_start_sql, current_end_sql, prev_start_sql, prev_end_sql, bucket_size, label_format, iso_bounds)
    iso_bounds is a dict: {cs_iso, ce_iso, ps_iso, pe_iso}
    z%Y-%m-%dr   éÿÿÿÿ)ÚdaysÚsecondsÚcustomzError parsing custom dates: z. Falling back to 30d.Ú30dNÚ1h)ÚhoursÚ24hé   Ú7dé   )r   é   Ú90déZ   i²  zTIMESTAMP 'z%Y-%m-%d %H:%M:%Sú')ÚcsÚceÚpsÚpeÚminuteÚhourzHH24:MIúHH24:00é   )r2   r3   é<   )ÚdayzMM-DD)ÚmonthzMon YYYY)	r   ÚutcnowÚstrptimer   Ú	ExceptionÚloggerÚerrorÚstrftimer   )r   r   r   ÚnowÚcs_dtÚce_dtÚeÚdurationÚps_dtÚpe_dtr-   r.   r/   r0   Ú
iso_boundsr   ÚbucketÚfmts                     r   Úget_time_boundsrH   '   sM  € ô
 �/‰/Ó
€Cáð
	Ü×%Ñ% n°jÓAˆEÙÜ ×)Ñ)¨,¸
ÓCÄiÐUVÐ`bÔFcÑc‘à�à!ˆJð
 �XÒØ˜ÒØ¤°Ô!3Ñ3°S�5‰EØ˜5Ò Ø¤°Ô!4Ñ4°c�5‰EØ˜4ÒØ¤°Ô!2Ñ2°C�5‰EØ˜5Ò Ø¤°Ô!3Ñ3°S�5‰EØ˜5Ò Ø¤°Ô!3Ñ3°S�5‰Eä# D¨!¨QÓ/°�5ˆEà�u‰}€HØ�HÑ€EØ€Eð �u—~‘~Ð&9Ó:Ð;¸1Ð	=€BØ�u—~‘~Ð&9Ó:Ð;¸1Ð	=€BØ�u—~‘~Ð&9Ó:Ð;¸1Ð	=€BØ�u—~‘~Ð&9Ó:Ð;¸1Ð	=€Bð �n‰n˜ZÓ(Ø�n‰n˜ZÓ(Ø�n‰n˜ZÓ(Ø�n‰n˜ZÓ(ñ	€Jð �=‰=€DØˆa‚xØ",°Ò"4‘h¸&ÈzÐ]aÒOaÁ)Ðgp�‰Ø	�ŠØ'‰ˆ‘Ø	�ŠØ$‰ˆ‘à)‰ˆ�àˆr�2�r˜6 3¨
Ð2Ð2øô_ ò 	Ü�L‰LÐ7¸°sÐ:PÐQÔRØŽJûð	ús   ˜AG Ç	G:ÇG5Ç5G:Ú	tenant_idÚ	thread_idc           
   ƒ   ó¤  K  — t         j                  d|› d| › �«       t        | d«       t        | |«      }|st         j	                  d|› �«       yd}|D ]?  }|j                  dd«      }|j                  d	d«      }||j                  «       › d
|› d�z  }ŒA t        j                  |¬«      }	 t        j                  j                  j                  t        j                  d|dœgdddi¬«      }t        j                   |j"                  d   j$                  j&                  «      }	||	d<   |j(                  }
t+        | dt        j                  |
j,                  |
j.                  |
j0                  d|i«       t3        | |	«       t         j                  d|› �«       y# t4        $ r*}t         j7                  d|› d| › d
|› �d¬«       Y d}~yd}~ww xY w­w)zO
    Fetches history for a thread and performs a single batch AI analysis.
    zAnalyzing thread: z for tenant: Ú	ai_tokenszNo history found for thread FÚ ÚroleÚunknownÚcontentú: Ú
)Úconversation_textÚuser)rN   rP   iX  ÚtypeÚjson_object)ÚmodelÚmessagesÚmax_completion_tokensÚresponse_formatr   rJ   zThread Analysisz'Successfully analyzed and saved thread TzFailed to analyze thread z for tenant ©Úexc_infoN)r;   Úinfor   r   ÚwarningÚgetÚupperr   ÚformatÚopenai_clientÚchatÚcompletionsÚcreater   Ú	LLM_MODELÚjsonÚloadsÚchoicesÚmessagerP   Úusager   Úprompt_tokensÚcompletion_tokensÚtotal_tokensr   r:   r<   )rI   rJ   ÚhistoryrS   ÚmsgrN   rP   Úanalysis_promptÚresponseÚanalysis_datark   rA   s               r   Úanalyze_threadrt   h   sÒ  è ø€ ô ‡K�KÐ$ Y K¨}¸Y¸KÐHÔIô �	˜;Ô'ä˜y¨)Ó4€GáÜ�‰Ð5°i°[ÐAÔBØàÐØò <ˆØ�w‰w�v˜yÓ)ˆØ—'‘'˜) RÓ(ˆØ §
¡
£˜~¨R°¨y¸Ð;Ñ;Ñð<ô
 0×6Ñ6ÐIZÔ[€OðÜ ×%Ñ%×1Ñ1×8Ñ8Ü×$Ñ$Ø%°/ÑBÐCØ"%Ø# ]Ð3ð	 9ó 
ˆô Ÿ
™
 8×#3Ñ#3°AÑ#6×#>Ñ#>×#FÑ#FÓGˆØ%.ˆ�kÑ"ð —‘ˆÜØØÜ×ÑØ×ÑØ×#Ñ#Ø×ÑØ˜)Ð$ô	
ô 	  	¨=Ô9Ü�‰Ð=¸i¸[ÐIÔJØøÜò Ü�‰Ð0°°¸<È	À{ÐRTÐUVÐTWÐXÐcgˆÔhÜûðüs1   ‚B+GÂ.C+F ÆGÆ	GÆ# GÇGÇGÇGc           
   ƒ   ó&  K  — t         j                  d| › �«       t        j                  «       }t        j                  j                  |d«      }t        j                  j                  «       }t        j                  |d<   t        t        j                  «      |d<   t        j                  |d<   t        j                  |d<   t        j                  |d<   | |d<   	 t        t        d	d
«      }|t        j                  j                  |d«      t        j                  j                  |d«      t!        j"                  d«      g}t%        d„ |D «       d«      }|st         j'                  d«       yt)        j*                  |ddd||t(        j,                  j.                  t(        j,                  j.                  ¬«      ƒ d{  –—† }|j1                  «       ƒ d{  –—† \  }}	|j2                  dk7  r�t         j'                  d|j2                  › �«       |r;|j5                  «       j7                  «       D ]  }
t         j'                  d|
› �«       Œ |	r;|	j5                  «       j7                  «       D ]  }
t         j'                  d|
› �«       Œ y|r;|j5                  «       j7                  «       D ]  }
t         j                  d|
› �«       Œ t         j                  d«       y7 �Œ7 �Œ
# t8        $ r$}t         j'                  d|› �d¬«       Y d}~yd}~ww xY w­w)zW
    Executes dbt run for a specific tenant using non-blocking asyncio subprocess.
    zExecuting dbt run for tenant: Ú	analyticsÚDB_HOSTÚDB_PORTÚDB_USERÚDB_PASSWORDÚDB_NAMEÚ	TENANT_IDÚDBT_EXECUTABLErM   zvenv_dbt/bin/dbtzvenv/bin/dbtÚdbtc              3   óf   K  — | ])  }|sŒt         j                  j                  |«      sŒ&|–— Œ+ y ­w)N)ÚosÚpathÚexists)Ú.0r�   s     r   ú	<genexpr>z%run_dbt_for_tenant.<locals>.<genexpr>¶   s"   è ø€ Ò\ DºdÄrÇwÁwÇ~Á~ÐVZÕG[œ4Ñ\ùs   ‚1Š1ª1NzLdbt executable not found. Set DBT_EXECUTABLE or install dbt in venv/bin/dbt.FÚrunz--profiles-dirú.)ÚcwdÚenvÚstdoutÚstderrr   zdbt run failed. Return code: zdbt STDOUT: zdbt STDERR: zdbt run completed successfullyTzFailed to execute dbt: r[   )r;   r]   r€   Úgetcwdr�   ÚjoinÚenvironÚcopyr   rw   Ústrrx   ry   rz   r{   ÚgetattrÚshutilÚwhichÚnextr<   ÚasyncioÚcreate_subprocess_execÚ
subprocessÚPIPEÚcommunicateÚ
returncodeÚdecodeÚ
splitlinesr:   )rI   Úproject_rootÚdbt_project_dirrˆ   Úconfigured_dbtÚdbt_candidatesÚ
dbt_scriptÚprocessr‰   rŠ   ÚlinerA   s               r   Úrun_dbt_for_tenantr£   �   sŒ  è ø€ ô ‡K�KÐ0°°Ð<Ô=Ü—9‘9“;€LÜ—g‘g—l‘l <°Ó=€Oô �*‰*�/‰/Ó
€CÜ×%Ñ%€Cˆ	�NÜœ×)Ñ)Ó*€Cˆ	�NÜ×%Ñ%€Cˆ	�NÜ!×-Ñ-€CˆÑÜ×%Ñ%€Cˆ	�NØ €CˆÑð*Ü ¤Ð+;¸RÓ@ˆàÜ�G‰G�L‰L˜Ð'9Ó:Ü�G‰G�L‰L˜ ~Ó6Ü�L‰L˜Óð	
ˆô Ñ\¨NÔ\Ð^bÓcˆ
ÙÜ�L‰LØ^ôð ä×6Ñ6Ø˜Ð/°ØØÜ×%Ñ%×*Ñ*Ü×%Ñ%×*Ñ*ô
÷ 
ˆð  '×2Ñ2Ó4×4‰ˆ�à×Ñ Ò"Ü�L‰LÐ8¸×9KÑ9KÐ8LÐMÔNÙØ"ŸM™M›O×6Ñ6Ó8ò 8�DÜ—L‘L <°¨vÐ!6Õ7ð8áØ"ŸM™M›O×6Ñ6Ó8ò 8�DÜ—L‘L <°¨vÐ!6Õ7ð8àáØŸ™›×2Ñ2Ó4ò 3�Ü—‘˜l¨4¨&Ð1Õ2ð3ä�‰Ð4Ô5Øð1
ùð 5úô" ò Ü�‰Ð.¨q¨cÐ2¸TˆÔBÜûðüsp   ‚CLÃBK! Å+LÅ,AK! Æ<KÆ=K! ÇKÇB2K! ÊLÊAK! ËLËK! ËK! Ë!	LË*L	ÌLÌ	LÌLc           
   ƒ   ó¤  K  — t         j                  d| › �«       	 t        | «       t        | «      }|D �cg c]  }|d   ‘Œ	 }}t        «       }g }	 |j                  «       5 }|j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  «       D �ch c]  }|d   ’Œ	 }	}|D �
cg c]	  }
|
|	vsŒ|
‘Œ }}
ddd«       |r=t         j                  d	t        |«      › d
�«       |D ]  }
t        | |
«      ƒ d{  –—†  Œ t!        | «      ƒ d{  –—† }|st         j#                  d| › d�«       	 |j                  «       5 }i }|j                  t        j                  d«      j                  t        j                  | «      «      «       |j%                  «       }|r4|j&                  D �cg c]  }|d   ‘Œ	 }}t)        t+        ||«      «      |d<   |j                  t        j                  d«      j                  t        j                  | «      «      «       |j                  «       }|rB|j&                  D �cg c]  }|d   ‘Œ	 }}|D �cg c]  }t)        t+        ||«      «      ‘Œ c}|d<   |j                  t        j                  d«      j                  t        j                  | «      «      «       |j%                  «       }|r4|j&                  D �cg c]  }|d   ‘Œ	 }}t)        t+        ||«      «      |d<   |j                  t        j                  d«      j                  t        j                  | «      «      «       |j%                  «       }|r4|j&                  D �cg c]  }|d   ‘Œ	 }}t)        t+        ||«      «      |d<   |j                  t        j                  d«      j                  t        j                  | «      «      «       |j%                  «       }|r4|j&                  D �cg c]  }|d   ‘Œ	 }}t)        t+        ||«      «      |d<   |j                  t        j                  d«      j                  t        j                  | «      «      «       |j%                  «       }|r4|j&                  D �cg c]  }|d   ‘Œ	 }}t)        t+        ||«      «      |d<   	 |j                  t        j                  d«      j                  t        j                  | «      «      «       |j%                  «       }|r4|j&                  D �cg c]  }|d   ‘Œ	 }}t)        t+        ||«      «      |d<   t-        | «      }||d<   d| |dœcddd«       |j/                  «        S # t        $ r&}t         j	                  d| › d|› �«       Y d}~�Œzd}~ww xY wc c}w c c}w c c}
w # 1 sw Y   �ŒÞxY w# t        $ r%}t         j	                  d|› �«       |}Y d}~�Œ
d}~ww xY w7 �ŒÚ7 �ŒÈc c}w c c}w c c}w c c}w c c}w c c}w c c}w c c}w # t        $ r&}t         j	                  d| › d|› �«       Y d}~�Œd}~ww xY w# 1 sw Y   nxY wnF# t        $ r:}t         j#                  d |› �d!¬"«       d#| i dœcY d}~|j/                  «        S d}~ww xY w	 |j/                  «        y# |j/                  «        w xY w­w)$zP
    Orchestrates the fresh analysis and returns KPIs from dbt Mart tables.
    z+Refreshing analytics discovery for tenant: z"Self-healing bootstrap failed for z* (ignoring if schema is already correct): NrJ   zR
                SELECT thread_id FROM {}.strategist_thread_analytics
            r   zBError checking analyzed threads (likely table doesn't exist yet): zFound z" unanalyzed threads. Processing...z%dbt transformation failed for tenant z. KPIs might be stale.z*SELECT * FROM {}.mart_general_chat_metricsÚgeneralz,SELECT * FROM {}.mart_customer_intent_demandÚintent_and_demandz+SELECT * FROM {}.mart_conversion_enablementÚ
conversionz)SELECT * FROM {}.mart_customer_experienceÚcustomer_experiencez"SELECT * FROM {}.mart_voc_insightsÚvoice_of_customerz(SELECT * FROM {}.mart_helpdesk_ticketingÚhelpdeskz$SELECT * FROM {}.mart_resource_usageÚresource_usagez#Could not fetch resource usage for rQ   Útotal_ai_responsesÚsuccess)ÚstatusrI   Úmetricsz!Failed to fetch KPIs from Marts: Tr[   Úpartial_success)r;   r]   r   r:   r^   r   r   ÚcursorÚexecuter   ÚSQLra   Ú
IdentifierÚfetchallÚlenrt   r£   r<   ÚfetchoneÚdescriptionÚdictÚzipr	   Úclose)rI   rA   Úall_threadsÚtÚ
thread_idsÚconnÚ
unanalyzedÚcurÚrowÚanalyzed_idsÚtidÚdbt_successÚfinal_metricsÚdÚdescÚintent_rowsÚrÚrer¬   s                      r   Úget_kpisrÌ   Ú   s  è ø€ ô ‡K�KÐ=¸i¸[ÐIÔJðvÜ˜Ô#ô
 " )Ó,€KØ*5Ö6 Q�!�K“.Ð6€JÐ6ô Ó€DØ€Jð	 Ø�[‰[‹]ð 	P˜cØ�K‰KœŸ™ð !ó ç‘œŸ™ yÓ1Ó2ô4ð /2¯l©l«nÖ= s˜C ›FÐ=ˆLÐ=Ø)3ÖO #°sÀ,Ò7Nš#ÐOˆJÐO÷	Pñ Ü�‰�fœS ›_Ð-Ð-OÐPÔQØò 	1ˆCÜ  ¨CÓ0×0Ñ0ð	1ô +¨9Ó5×5€KÙÜ�‰Ð<¸Y¸KÐG]Ð^Ô_ðGØ�[‰[‹]ð @	˜càˆMð �K‰KœŸ™Ð LÓM×TÑTÔUX×UcÑUcÐdmÓUnÓoÔpØ—,‘,“.ˆCÙØ&)§o¡oÖ6 ˜˜!›Ð6�Ð6Ü+/´°D¸#³Ó+?�˜iÑ(ð �K‰KœŸ™Ð NÓO×VÑVÔWZ×WeÑWeÐfoÓWpÓqÔrØŸ,™,›.ˆKÙØ&)§o¡oÖ6 ˜˜!›Ð6�Ð6ØR]Ö5^ÈQ´d¼3¸tÀQ»<Õ6HÒ5^�Ð1Ñ2ð �K‰KœŸ™Ð MÓN×UÑUÔVY×VdÑVdÐenÓVoÓpÔqØ—,‘,“.ˆCÙØ&)§o¡oÖ6 ˜˜!›Ð6�Ð6Ü.2´3°t¸S³>Ó.B�˜lÑ+ð �K‰KœŸ™Ð KÓL×SÑSÔTW×TbÑTbÐclÓTmÓnÔoØ—,‘,“.ˆCÙØ&)§o¡oÖ6 ˜˜!›Ð6�Ð6Ü7;¼CÀÀc»NÓ7K�Ð3Ñ4ð �K‰KœŸ™Ð DÓE×LÑLÌSÏ^É^Ð\eÓMfÓgÔhØ—,‘,“.ˆCÙØ&)§o¡oÖ6 ˜˜!›Ð6�Ð6Ü59¼#¸dÀC».Ó5I�Ð1Ñ2ð �K‰KœŸ™Ð JÓK×RÑRÔSV×SaÑSaÐbkÓSlÓmÔnØ—,‘,“.ˆCÙØ&)§o¡oÖ6 ˜˜!›Ð6�Ð6Ü,0´°T¸3³Ó,@�˜jÑ)ðXØ—‘œCŸG™GÐ$JÓK×RÑRÔSV×SaÑSaÐbkÓSlÓmÔnØ—l‘l“n�ÙØ*-¯/©/Ö: Q˜A˜a›DÐ:�DÐ:Ü6:¼3¸tÀS»>Ó6J�MÐ"2Ñ3ô
 ">¸iÓ!HÐØ2DˆMÐ.Ñ/ð $Ø&Ø(ñ÷y@	ð @	ðL 	�
‰
�øôQ ò vÜ�‰Ð;¸I¸;ÐFpÐqrÐpsÐt×uÒuûðvüò
 7ùò >ùÚO÷	Pñ 	Pûô ò  Ü�‰Ð[Ð\]Ð[^Ð_Ô`ØŽ
ûð úð 1ùð 6ûò 7ùò 7ùÚ5^ùò 7ùò 7ùò 7ùò 7ùò ;øäò XÜ—‘Ð!DÀYÀKÈrÐRTÐQUÐV×WÒWûðXú÷k@	ð @	úð @	øôB ò TÜ�‰Ð8¸¸Ð<ÀtˆÔLà+¸)ÐPRÑSÔSà�
‰
�ûðTúðC@	ðL 	�
‰
�øˆ�
‰
�üsÀ  ‚[œU7 §[¶V)Á[ÁW Á!AV8Â:V.ÃV8Ã	V3ÃV3ÃV8ÃW Ã$8[ÄW6Ä[Ä1W9Ä2[ÅY# Å"A)YÇW<ÇB YÉXÉ#YÉ)XÊA,YË1XË=B YÍ=XÎ	B YÐ	XÐB YÒXÒ!YÒ<A&X$Ô"XÔ.X$ÕYÕ	Y# Õ&[Õ7	V&Ö V!Ö[Ö!V&Ö&[Ö.
V8Ö8WÖ=W ×	W3×W.×([×.W3×3[×9[×<#YØX$Ø$	YØ-YÙYÙYÙYÙYÙY# Ù"Z; Ù#	Z&Ù,Z!ÚZ&ÚZ; Ú[Ú!Z&Ú&Z; Ú*[Ú;[Û[Ú
start_dateÚend_datec              ƒ   óø  K  — t         j                  d| › d|› d|› d|› �«       t        | «       t        «       }	 t	        |||«      \  }}}}}	}
}|j                  «       5 }t        j                  d«      j                  t        j                  | «      t        j                  |«      t        j                  |«      t        j                  |«      t        j                  |«      t        j                  | «      ¬«      }	 |j                  |«       |j                  «       }|s	 ddd«       |j                  «        y|j                  D �cg c]  }|d   ‘Œ	 }}t        t!        ||«      «      }d„ } ||j+                  d«      |j+                  d«      «      \  }}} ||j+                  d«      |j+                  d«      «      \  }}} ||j+                  d«      |j+                  d«      «      \  }}}|j                  d| f«       d\  } }!|j                  «       d   r°t        j                  d«      j                  t        j                  | «      t        j                  |«      t        j                  |«      t        j                  |«      t        j                  |«      ¬«      }"|j                  |"«       |j                  «       }#|#r|#\  } }! || |!«      \  }$}%}&t-        | |d   |d   «      }'t-        | |d   |d   «      }( ||'|(«      \  })}*}+t/        | |d   «      },t/        | |d   «      }-|,dk(  rddlm}.  |.| «      }, ||,|-«      \  }$}/}0t5        |,«      }1t7        |j+                  d«      xs d«      }2t7        |j+                  d «      xs d«      }3|j+                  d«      �t9        |2d!z   d"z  d#z  d!z   d!«      nd$}4|j+                  d «      �t9        |3d!z   d"z  d#z  d!z   d!«      nd$}5 ||4|5«      \  }$}6}7t;        |«      ||d%œt9        |d!«      › d&�||d%œt9        |d!«      › d'�||d%œ|4› d(�|6|7d%œt;        |)«      |*|+d%œt;        | «      |%|&d%œ|1|/xs d)|0d%œd*œ}8g g g };}:}9d+}<	 t        j                  d,«      j                  t        j                  | «      t        j                  |	«      t        j                  |
«      t        j                  |«      t        j                  |«      ¬-«      }=|j                  |=«       |j=                  «       }>|>s¬	 t        j                  d0«      j                  t        j                  | «      t        j                  |	«      t        j                  |
«      t        j                  |«      t        j                  |«      ¬-«      }?|j                  |?«       |j=                  «       }>d1}<|>rD|>D �@cg c]  }@|@d   ‘Œ	 }9}@|>D �@cg c]  }@|@d!   ‘Œ	 }:}@|>D �@cg c]  }@t9        |@d"   xs dd!«      ‘Œ };}@|9d4|:d5œgd6œ}A|9d7|;d5œgd6œ}Bg g }D}Cg }E	 t        j                  d8«      j                  t        j                  | «      t        j                  |«      t        j                  |«      ¬9«      }F|j                  |F«       |j=                  «       }E|<sErt?        d;„ ED «       «      s‚	 t        j                  d<«      j                  t        j                  | «      t        j                  |«      t        j                  |«      ¬9«      }G|j                  |G«       |j=                  «       }EEr$ED �Hcg c]  }H|Hd   ‘Œ	 }C}HED �Hcg c]  }H|Hd!   ‘Œ	 }D}HtA        D«      xs d!}IDD �Jcg c]  }Jt9        |JIz  d?z  d!«      ‘Œ }K}JC|KDd@œgd6œ}L|8A|LBdAœdBœcddd«       |j                  «        S c c}w # t"        $ �rš}t         j%                  d	| › d
|› d�«       |j'                  «        t        j                  d«      }t        j                  d«      j                  t        j                  | «      t        j                  |«      t        j                  |«      t        j                  |«      t        j                  |«      ¬«      }	 |j                  |«       |j                  «       }|sY d}~ddd«       |j                  «        y|j                  D �cg c]  }|d   ‘Œ	 nc c}w }}t        t!        ||«      «      }nM# t"        $ rA}t         j)                  d| › d
|› �«       Y d}~Y d}~ddd«       |j                  «        yd}~ww xY wY d}~�Œäd}~ww xY w# t"        $ r9}t         j%                  d.| › d
|› d/�«       |j'                  «        g }>Y d}~�ŒŽd}~ww xY w# t"        $ r7}t         j%                  d2| › d
|› d3�«       |j'                  «        Y d}~�Œ#d}~ww xY wc c}@w c c}@w c c}@w # t"        $ r7}t         j%                  d:| › d
|› d/�«       |j'                  «        Y d}~�Œ•d}~ww xY w# t"        $ r7}t         j%                  d=| › d
|› d>�«       |j'                  «        Y d}~�Œ@d}~ww xY wc c}Hw c c}Hw c c}Jw # 1 sw Y   nxY wnC# t"        $ r7}t         j)                  dC| › d
|› �d1¬D«       Y d}~|j                  «        yd}~ww xY w	 |j                  «        y# |j                  «        w xY w­w)Ez­
    Returns a simplified set of KPIs specifically for UI dashboards, filtered by time range (relative or absolute).
    Ensures self-healing migration for old tenants.
    z%Fetching general UI KPIs for tenant: z	, range: z
, custom: z to aº  
                WITH metrics AS (
                    SELECT
                        count(distinct case when session_start >= {cs} and session_start <= {ce} then thread_id end) as current_conversations,
                        count(distinct case when session_start >= {ps} and session_start <= {pe} then thread_id end) as prev_conversations,
                        
                        -- Using explicit fallback for avg_response_time_seconds if missing
                        CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = {cs_schema} AND table_name = 'int_chat_sessions' AND column_name = 'avg_response_time_seconds') 
                             THEN avg(case when session_start >= {cs} and session_start <= {ce} then avg_response_time_seconds end) 
                             ELSE 0 END as current_avg_response_time,
                        CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = {cs_schema} AND table_name = 'int_chat_sessions' AND column_name = 'avg_response_time_seconds') 
                             THEN avg(case when session_start >= {ps} and session_start <= {pe} then avg_response_time_seconds end) 
                             ELSE 0 END as prev_avg_response_time,

                        (sum(case when session_start >= {cs} and session_start <= {ce} then is_resolved::int else 0 end)::float / 
                            nullif(count(case when session_start >= {cs} and session_start <= {ce} then thread_id end), 0) * 100) as current_resolution_rate,
                        (sum(case when session_start >= {ps} and session_start <= {pe} then is_resolved::int else 0 end)::float / 
                            nullif(count(case when session_start >= {ps} and session_start <= {pe} then thread_id end), 0) * 100) as prev_resolution_rate,

                        avg(case when session_start >= {cs} and session_start <= {ce} then sentiment_score end) as current_satisfaction,
                        avg(case when session_start >= {ps} and session_start <= {pe} then sentiment_score end) as prev_satisfaction
                    FROM {schema}.int_chat_sessions
                )
                SELECT * FROM metrics
            )Úschemar-   r.   r/   r0   Ú	cs_schemaNr   z/Failed to query int_chat_sessions directly for rQ   z. Trying raw table fallback...aÆ  
                    WITH session_raw AS (
                        SELECT 
                            thread_id,
                            MIN(created_at) as session_start
                        FROM {schema}.strategist_feedback
                        GROUP BY thread_id
                    ),
                    metrics_raw AS (
                        SELECT
                            s.thread_id,
                            s.session_start,
                            t.is_resolved,
                            t.sentiment_score
                        FROM session_raw s
                        LEFT JOIN {schema}.strategist_thread_analytics t ON s.thread_id = t.thread_id
                    ),
                    aggregated AS (
                        SELECT
                            count(distinct case when session_start >= {cs} then thread_id end) as current_conversations,
                            count(distinct case when session_start >= {ps} and session_start < {cs} then thread_id end) as prev_conversations,
                            
                            (sum(case when session_start >= {cs} then is_resolved::int else 0 end)::float / 
                                nullif(count(case when session_start >= {cs} then thread_id end), 0) * 100) as current_resolution_rate,
                            (sum(case when session_start >= {ps} and session_start < {cs} then is_resolved::int else 0 end)::float / 
                                nullif(count(case when session_start >= {ps} and session_start < {cs} then thread_id end), 0) * 100) as prev_resolution_rate,

                            avg(case when session_start >= {cs} then sentiment_score end) as current_satisfaction,
                            avg(case when session_start >= {ps} and session_start < {cs} then sentiment_score end) as prev_satisfaction
                        FROM aggregated_metrics
                    )
                    SELECT * FROM aggregated
                aÕ  
                    SELECT 
                        (SELECT count(distinct thread_id) FROM {schema}.strategist_feedback WHERE created_at >= {cs} AND created_at <= {ce}) as current_conversations,
                        (SELECT count(distinct thread_id) FROM {schema}.strategist_feedback WHERE created_at >= {ps} AND created_at <= {pe}) as prev_conversations,
                        0 as current_avg_response_time, 
                        0 as prev_avg_response_time,
                        (SELECT (sum(is_resolved::int)::float / nullif(count(*), 0) * 100) FROM {schema}.strategist_thread_analytics WHERE created_at >= {cs} AND created_at <= {ce}) as current_resolution_rate,
                        (SELECT (sum(is_resolved::int)::float / nullif(count(*), 0) * 100) FROM {schema}.strategist_thread_analytics WHERE created_at >= {ps} AND created_at <= {pe}) as prev_resolution_rate,
                        (SELECT avg(sentiment_score) FROM {schema}.strategist_thread_analytics WHERE created_at >= {cs} AND created_at <= {ce}) as current_satisfaction,
                        (SELECT avg(sentiment_score) FROM {schema}.strategist_thread_analytics WHERE created_at >= {ps} AND created_at <= {pe}) as prev_satisfaction
                )rÐ   r-   r.   r/   r0   z8Critical failure: Both view and raw fallback failed for c                 ó´   — t        | xs d«      } t        |xs d«      }|dk(  r| ddfS | |z
  }||z  dz  }|dkD  rdn|dk  rdnd}|dkD  rdnd}| |› |d	›d
�|fS )Nr   zN/AÚflatéd   ÚupÚdownú+rM   z.2fú%)Úfloat)ÚcurrentÚprevÚdiffÚpctÚ	directionÚsigns         r   Ú
calc_trendz$get_general_kpis.<locals>.calc_trendÌ  s…   € Ü ¢¨1Ó-�Ü˜TšY QÓ'�Ø˜1’9Ø" E¨6Ð1Ð1Ø ‘~�Ø˜d‘{ cÑ)�Ø$(¨1¢H™D¸TÀAºX±6È6�	Ø! Ašg‘s¨2�Ø 4 &¨¨S¨	°Ð 3°YÐ>Ð>r   Úcurrent_conversationsÚprev_conversationsÚcurrent_avg_response_timeÚprev_avg_response_timeÚcurrent_resolution_rateÚprev_resolution_ratezÎ
                SELECT EXISTS (
                    SELECT FROM information_schema.tables 
                    WHERE table_schema = %s AND table_name = 'strategist_llm_usage'
                )
            )r   r   ax  
                    SELECT 
                        COALESCE(SUM(CASE WHEN created_at >= {cs} AND created_at <= {ce} THEN total_tokens ELSE 0 END), 0) as cur_tokens,
                        COALESCE(SUM(CASE WHEN created_at >= {ps} AND created_at <= {pe} THEN total_tokens ELSE 0 END), 0) as prev_tokens
                    FROM {schema}.strategist_llm_usage
                r-   r.   r/   r0   )Úget_vector_db_sizeÚcurrent_satisfactionÚprev_satisfactionr   r4   é   g        )ÚvalueÚtrendÚtrend_directionÚsrØ   z/5ÚLatest)Útotal_conversationsÚavg_response_timeÚresolution_rateÚuser_satisfactionr¬   rL   Ústorage_capacityFa
  
                    SELECT 
                        to_char(date_trunc({bucket}, session_start), {fmt}) as label,
                        count(distinct thread_id) as users,
                        avg(avg_response_time_seconds) as resp_time
                    FROM {schema}.int_chat_sessions
                    WHERE session_start >= {cs} AND session_start <= {ce}
                    GROUP BY 1, date_trunc({bucket}, session_start)
                    ORDER BY date_trunc({bucket}, session_start) ASC
                )rÐ   rF   rG   r-   r.   zChart query failed for z. Trying raw feedback fallback.a  
                        SELECT
                            to_char(date_trunc({bucket}, created_at), {fmt}) as label,
                            count(distinct thread_id) as users,
                            0 as resp_time,
                            date_trunc({bucket}, created_at) as bucket_start
                        FROM {schema}.strategist_feedback
                        WHERE created_at >= {cs} AND created_at <= {ce}
                        GROUP BY 1, 4
                        ORDER BY bucket_start ASC
                    Tz'Raw feedback chart fallback failed for z. Returning empty chart data.ÚUsers)ÚlabelÚdata)ÚlabelsÚdatasetszAvg Response Time (s)a(  
                    SELECT 'Website Chat' as channel, 
                        count(distinct thread_id) as conversations
                    FROM {schema}.int_chat_sessions
                    WHERE session_start >= {cs} AND session_start <= {ce}
                    GROUP BY 1
                )rÐ   r-   r.   zChannel query failed for c              3   ó&   K  — | ]	  }|d    –— Œ y­w)r   N© )rƒ   Úcs     r   r„   z#get_general_kpis.<locals>.<genexpr>–  s   è ø€ ÒFcÐPQÀqÈÅtÑFcùs   ‚a;  
                        SELECT 'Website Chat' as channel,
                            count(distinct thread_id) as conversations
                        FROM {schema}.strategist_feedback
                        WHERE created_at >= {cs} AND created_at <= {ce}
                        GROUP BY 1
                    z)Raw feedback channel fallback failed for z. Returning empty data.rÔ   )r÷   Ú
raw_counts)Útotal_usersÚconversation_channelsÚresponse_time)Útop_kpisÚchartsz(Failed to fetch general KPIs for tenant r[   )!r;   r]   r   r   rH   r±   r   r³   ra   r´   ÚLiteralr²   r·   r»   r¸   r¹   rº   r:   r^   Úrollbackr<   r_   r
   r   Úapp.services.databaserç   r   rÙ   r   Úintrµ   ÚanyÚsum)MrI   r   rÍ   rÎ   r¿   Ú	cur_startÚcur_endÚ
prev_startÚprev_endrF   Ú	label_fmtÚisorÁ   Ú	kpi_queryrÂ   rÇ   rÈ   r¯   rA   Ú	raw_queryÚfallback_queryÚe2rà   Úcur_convÚtr_convÚdir_convÚcur_respÚtr_respÚdir_respÚcur_resÚtr_resÚdir_resÚ
cur_tokensÚprev_tokensÚtoken_queryÚtrowÚ_Ú	tr_tokensÚ
dir_tokensÚcur_ai_resp_countÚprev_ai_resp_countÚcur_ai_respÚ
tr_ai_respÚdir_ai_respÚcur_storage_bytesÚprev_storage_bytesrç   Ú
tr_storageÚdir_storageÚcur_storage_displayÚcur_satÚprev_satÚstar_curÚ	star_prevÚtr_satÚdir_satr  rø   Ú
users_dataÚresp_time_dataÚchart_from_raw_feedbackÚchart_queryÚ
chart_rowsÚraw_chart_queryrÊ   Úchart_total_usersÚchart_response_timeÚclabelsÚccountsÚchannels_dataÚchannel_queryÚraw_channel_queryrü   Útotal_cÚcntÚcpercentÚchart_channelssM                                                                                r   Úget_general_kpisrD  L  sé
  è ø€ ô
 ‡K�KÐ7¸	°{À)ÈJÈ<ÐWaÐblÐamÐmqÐrzÐq{Ð|Ô}ô �YÔäÓ€DðmÜKZÐ[eÐgqÐs{ÓK|ÑHˆ	�7˜J¨°&¸)ÀSà�[‰[‹]ð e	˜cäŸ™ð !ó ÷0 ‘Ü—~‘~ iÓ0Ü—7‘7˜9Ó%Ü—7‘7˜7Ó#Ü—7‘7˜:Ó&Ü—7‘7˜8Ó$ÜŸ+™+ iÓ0ð ó ð1 ðBL Ø—‘˜IÔ&Ø—l‘l“n�ÙØ÷Oe	ðT 	�
‰
�ðE
 '*§o¡oÖ6 ˜˜!›Ð6�Ð6Üœs 4¨›~Ó.�òR	?ñ +5°W·[±[ÐAXÓ5YÐ[b×[fÑ[fÐg{Ó[|Ó*}Ñ'ˆH�g˜xÙ*4°W·[±[ÐA\Ó5]Ð_f×_jÑ_jð  lDó  `Eó  +FÑ'ˆH�g˜xÙ'1°'·+±+Ð>WÓ2XÐZa×ZeÑZeÐf|ÓZ}Ó'~Ñ$ˆG�V˜Wð
 �K‰Kð ð
 �ôð '+Ñ#ˆJ˜Ø�|‰|‹~˜aÒ Ü!Ÿg™gð 'ó ÷
 ‘VÜŸ>™>¨)Ó4Ü—w‘w˜yÓ)Ü—w‘w˜wÓ'Ü—w‘w˜zÓ*Ü—w‘w˜xÓ(ð ó ð ð —‘˜KÔ(Ø—|‘|“~�ÙØ.2Ñ+�J á'1°*¸kÓ'JÑ$ˆAˆy˜*ô !<¸IÀsÈ4ÁyÐRUÐVZÑR[Ó \ÐÜ!<¸YÈÈDÉ	ÐSVÐW[ÑS\Ó!]ÐÙ3=Ð>OÐQcÓ3dÑ0ˆK˜ [ô !<¸IÀsÈ4ÁyÓ QÐÜ!<¸YÈÈDÉ	Ó!RÐð ! AÒ%ÝDÙ$6°yÓ$AÐ!ñ *4Ð4EÐGYÓ)ZÑ&ˆAˆz˜;ä"5Ð6GÓ"HÐô ˜GŸK™KÐ(>Ó?ÒDÀ1ÓEˆGÜ˜WŸ[™[Ð)<Ó=ÒBÀÓCˆHØ@GÇÁÐLbÓ@cÐ@o”u˜w¨™{¨aÑ/°1Ñ4°qÑ8¸!Ô<ÐuxˆHØBIÇ+Á+ÐNaÓBbÐBnœ ¨1¡°Ñ1°QÑ6¸Ñ:¸AÔ>ÐtwˆIÙ!+¨H°iÓ!@ÑˆAˆv�wô ! ›]Ø$Ø'/ñ(ô !& h°Ó 2Ð3°1Ð5Ø$Ø'/ñ&ô !& g¨qÓ 1Ð2°!Ð4Ø#Ø'.ñ$ð !)˜z¨˜_Ø#Ø'.ñ&ô ! Ó-Ø'Ø'2ñ'ô ! ›_Ø&Ø'1ñð 1Ø'Ò3¨8Ø'2ñ%ñ?$ˆHðP 24°R¸ �JˆFØ&+Ð#ð Ü!Ÿg™gð 	'ó 	÷ ‘VÜŸ>™>¨)Ó4ÜŸ;™; vÓ.ÜŸ™ IÓ.Ü—w‘w˜yÓ)Ü—w‘w˜wÓ'ð ó ð ð" —‘˜KÔ(Ø Ÿ\™\›^�
ñ ð$Ü&)§g¡gð 
/ó 
'÷  ™Ü"Ÿ~™~¨iÓ8Ü"Ÿ{™{¨6Ó2ÜŸK™K¨	Ó2ÜŸ7™7 9Ó-ÜŸ7™7 7Ó+ð  ó ð $ð" —K‘K Ô0Ø!$§¡£�JØ.2Ð+ñ
 Ø(2Ö3 1˜!˜A›$Ð3�Ð3Ø,6Ö7 q˜a ›dÐ7�
Ð7Ø?IÖ!J¸!¤%¨¨!©ª	°°1Õ"5Ð!J�Ð!Jð !Ø'.¸
ÑCÐDñ!Ðð !Ø'>ÈÑWÐXñ#Ðð  " 2�WˆGØˆMð Ü #§¡ð )ó !÷ ‘VÜŸ>™>¨)Ó4Ü—w‘w˜yÓ)Ü—w‘w˜wÓ'ð ó ð ð —‘˜MÔ*Ø #§¡£�ñ
 '©mÄ3ÑFcÐUbÔFcÔCcð$Ü(+¯©ð 1ó )÷  ™Ü"Ÿ~™~¨iÓ8ÜŸ7™7 9Ó-ÜŸ7™7 7Ó+ð  ó ð &ð —K‘KÐ 1Ô2Ø$'§L¡L£N�Mñ
 Ø)6Ö7 A˜1˜Q›4Ð7�Ð7Ø)6Ö7 A˜1˜Q›4Ð7�Ð7ä˜'“lÒ' aˆGØ?FÖG¸œ˜s 7™{¨CÑ/°Õ3ÐGˆHÐGð "à$Ø")ñð ñˆNð %à#4Ø-;Ø%8ññ÷}
e	ð e	ðT 	�
‰
�ùòE
 7øäó E Ü—‘Ð!PÐQZÐP[Ð[]Ð^_Ð]`Ð`~Ðô  AØ—‘”ô  ŸG™Gð  %ó  �	ôJ "%§¡ð 
*ó 
"÷ ‘VÜŸ>™>¨)Ó4Ü—w‘w˜yÓ)Ü—w‘w˜wÓ'Ü—w‘w˜zÓ*Ü—w‘w˜xÓ(ð ó ð ð$ Ø—K‘K Ô/ØŸ,™,›.�CÙ£t÷Ue	ðT 	�
‰
�ð +.¯/©/Ö: Q˜A˜a›DÑ:ùÒ:�DÐ:Ü"¤3 t¨S£>Ó2‘GøÜ ò  Ü—L‘LÐ#[Ð\eÐ[fÐfhÐikÐhlÐ!mÔnß÷_e	ðT 	�
‰
�ûð{ úõ ûðEE ûô\ ò  Ü—‘Ð!8¸¸À2ÀaÀSÐHgÐhÔiØ—‘”Ø–
ûð ûô6 !ò $Ü—N‘NÐ%LÈYÈKÐWYÐZ[ÐY\Ð\yÐ#zÔ{Ø—M‘M—O’Oûð$üò
 4ùÚ7ùÚ!Jøô< ò  Ü—‘Ð!:¸9¸+ÀRÈÀsÐJiÐjÔkØ—‘—’ûð ûô& !ò $Ü—N‘NÐ%NÈyÈkÐY[Ð\]Ð[^Ð^uÐ#vÔwØ—M‘M—O’Oûð$üò
 8ùÚ7ùò H÷i
e	ð e	úð e	øôL ò Ü�‰Ð?À	¸{È"ÈQÈCÐPÐ[_ˆÔ`Ûà�
‰
�ûð	úðMe	ðT 	�
‰
�øˆ�
‰
�üs	  ‚7k:º%j ÁBjÃ=#^0Ä!j Ä)k:Ä:^0Å^+Å^0Å+K1jÑB)eÔjÔ	B+fÖ4jÖ:g×j×g$×j×g)×8jØBg.ÚjÚ+Bh1Ü,jÜ2i4Ü>jÝi9ÝjÝ%i>Ý>jÞ	j Þk:Þ+^0Þ0
eÞ:C	eâ#c<â'jâ+j â3k:ãc<ãcãc<ã;eã<	eäeä eä$jä(j ä0k:åeåeå	jåeåjå	få .fæjæfæjæ	gæ%,gçjçgçjç.	h.ç7,h)è#jè)h.è.jè1	i1è:,i,é&jé,i1é1jêjêj êk% ê	kêkê6k% ê:k:ëkëk% ëk:ë%k7ë7k:)NN)r"   NN)*Úloggingrg   r–   r€   r‘   r”   Úsysr  r   r   r   r   Úapp.services.firestore_servicer   r   r	   r
   r   Úapp.services.quotasr   Úapp.services.embeddingr   rb   Úapp.core.configr   Úapp.core.promptsr   Úpsycopg2r   Ú	getLoggerÚ__name__r;   r   r   r  r   r�   rH   rt   r£   rÌ   rD  rû   r   r   ú<module>rO     sÏ   ðÛ Û Û Û 	Û Û Û 
ß pÓ p÷õ õ ,Ý :Ý $Ý 6Ý à	ˆ×	Ñ	˜8Ó	$€ç (ð% Có %ñ?3 ð ?3°Sð ?3Èsó ?3ðB3 Cð 3°Có 3ðj;¨ó ;ðzq˜có qñdx cð x°sð xÐPSð xÐfiô xr   