
    úh                     n   d dl mZ d dlmZmZmZ d dlZd dlmZmZm	Z	m
Z
 d dlZd dlZd dlZd dlZd dlmZ d dlmZmZ d dlmZmZmZ d dlmZ d d	lmZ d d
lmZmZ  ej>                  ej@                          ejB                  e"      Z#de$fdZ%	 d#dede&de&dee$ef   fdZ'd Z(	 d$dede
e&   dededee$ef   f
dZ)	 d$dede
e&   dede	e   dee$ef   f
dZ*	 d$dede&dede	e   dee$ef   f
dZ+	 	 	 d%dede&de	e&   de	e&   de	e&   dee$ef   fdZ,de-de$fd Z.	 d$dede&de	e&   dee$ef   fd!Z/d" Z0y)&    )Session)datetimedate	timedeltaN)DictAnyOptionalList)func)ConnectedAccountMasterAccount)AccountMetricsDailyPostMetricsDailyStorePerformanceScore)CalendarPostType)TwitterXProvider)
OAuthToken	DateRange)levelnetworkc                 \    | j                         dv rt        ddd      S t        d|        )zCReturn the appropriate social media provider based on network name.)twitterxx (twitter) zUnsupported network: )lowerr   
ValueError)r   s    M/var/www/html/hubwallet-dev/src/marketing/apps/Analytics/twitter_analytics.pyprovider_forr      s5    }}99B++ ,WI6
77    dbconnected_account_iddays_to_fetchreturnc           
          	  j                  t              j                  t        j                  k(  t        j                  j                  d            j                         }|sdddS  j                  t              j                  t        j                  |j                  k(        j                         }|r:|j                  j                         dk7  s|j                  j                         dk7  rdddS t        j                         j                         t        |      z
  }t        ddd      	 j!                  |j"                        t%        d	       t&        j)                  d
        't&        j+                  d|j                          dddS 	 d|j"                  i	 j1                  t3        |j5                         j5                                     }t7        |j9                  dd      |j9                  dd      |j9                  dd            } j;                  |        j=                           fd}	tA        jB                  |	      }
d|
_"        |
jG                          dd d|j9                  dd      dS # t,        $ r<}t&        j+                  dt/        |              ddt/        |       dcY d}~S d}~ww xY w# t,        $ rL} j?                          t&        j+                  dt/        |              ddt/        |       dcY d}~S d}~ww xY w# t,        $ r<}t&        j+                  dt/        |              ddt/        |       dcY d}~S d}~ww xY w)a7  
    Initialize metrics collection for a newly connected Twitter account.
    
    Args:
        db: Database session
        connected_account_id: ID of the connected account
        days_to_fetch: Number of days of historical data to fetch
        
    Returns:
        Dictionary with status information
    FConnected account not foundsuccessmessager   r   z"Account is not a Twitter/X accountdaysr   zTwitter Token :zTwitter Token: N-Failed to obtain temporary token for account z Failed to obtain temporary tokenzError getting temporary token: user_idstartend	followersr   new_followersimpressionsr"   metric_dater1   r2   r3   z Error fetching account metrics: c                     } 	 | j                  t              j                  t        j                  k(  t        j                  j                  d      t        j                  j                  d             j                         }|D ];  }|j                  r|j                  j                         n}|j                         }t        ||      }	 j                  |      \  }}|D ]  }|j                  d      |j                  k(  s"|j                  di       }	t        |j                   ||	j                  dd      |	j                  dd      |	j                  dd      |	j                  d	d      |	j                  d
d            }
| j#                  |
       | j%                           n t1        j2                  d       > t*        j5                  d        y # t&        $ rI}| j)                          t*        j-                  d|j                    dt/        |              Y d }~d }~ww xY w# t&        $ r+}t*        j-                  dt/        |              Y d }~y d }~ww xY w)NFr.   external_post_idmetricsr3   r   reachlikescommentsshares)post_idr"   r5   r3   r9   r:   r;   r<   z Error fetching metrics for post : g      ?z7Completed background fetch of post metrics for account z(Error in background post metrics fetch: )queryr   filterr"   
is_deletedis_r7   isnotall
created_atr   	isoformatr   fetch_post_metricsgetr   idaddcommit	Exceptionrollbackloggererrorstrtimesleepinfo)_dbpostspost	post_datedate_strpost_date_range
posts_data_	post_datar8   db_post_metricseaccount_refr"   r!   end_dateprovidertokens               r   fetch_post_metrics_backgroundzAinitialize_twitter_metrics.<locals>.fetch_post_metrics_backgroundu   s   C2R		"23::$99=QQ$//33E:$55;;DA #%	  " &$D:>// 4 4 6xI(224H&/hH&MO!(0(C(CE;Xg(h
A *4 &I(}}-?@DDYDYY*3--	2*F 3C,0GG9M0907M10M*1++gq*A*1++gq*A-4[[Q-G+2;;x+C	3" !$ 8 !$

 %)&6 JJsOM&$P UVjUklm % !'GyPRSVWXSYRZ%[\ !  RGAxPQQRsI   C
H3 9G
B#G-0H3 	H0'>H+%H3 +H00H3 3	I'<!I""I')targetTz7Initial Twitter metrics collection started for account in_progress)r(   r)   
account_idmetrics_collection_statusinitial_followersz%Error in initialize_twitter_metrics: Error: )$r?   r   r@   rI   rA   rB   firstr   master_account_idsocial_media_namer   r   nowr   r   r   exchange_token_tempexternal_account_idprintrN   rS   rO   rL   rP   fetch_account_metricsr   rF   r   rH   rJ   rK   rM   	threadingThreaddaemonr/   )r!   r"   r#   accountmaster_account
start_dater^   account_metricsdb_account_metricsrc   threadr_   r`   ra   rb   s   ``         @@@@r   initialize_twitter_metricsr{   $   so    FA((+,33#77''++E2
 %' 	
 $1NOO -077 9 99

%' 	 !A!A!G!G!I]!Z^l^~^~  _E  _E  _G  KT  _T$1UVV <<>&&(	} ==
 $BB/
	]001L1LME#U+KK/%12}LWZZLYZ#(5WXX  !'"="=>	^&<<UK<EJL`L`LbHPHZHZH\=^_O
 "5%9$)--k1=-11/1E+//qA" FF%&IIK4	R 4	Rn !!)FG PQePfg.)6!0!4!4[!!D
 	
s  	]LL:3q6(CD$3RSVWXSYRZ1[\\	]0  	^KKMLL;CF8DE$3STWXYTZS[1\]]	^R  A<SVHEF ws1vh-?@@As   A'M 0BM >>M =A'J" %M 5BK* AM "	K'+1K"K'M "K''M *	L?3AL:4L?5M :L??M 	N1N<NNc                  0   ddl m}   |        }t        j                         t	        d      z
  j                         }t        j                         j                         }	 |j                  t              j                  t        t        j                  t        j                  k(        j                  t        j                  j                  d      t        j                  j!                  d      t        j                  j                  d      t        j                  dk(        j#                         }t%        d|       |s't&        j)                  d	       	 |j+                          yt&        j)                  d
t-        |       d       t/        ddd      }|D ]<  }	 	 |j1                  |j2                        }t%        d|       |_t&        j5                  d|j                          t&        j7                  d|j                          |j1                  |j2                        }|j                  |j2                  d}	|j=                         }
t?        |
|
      }	 |jA                  ||	|      }|j                  tD              j                  tD        jF                  |j                  k(  tD        jH                  |k(        jK                         }|rd|jM                  d|jN                        |_'        |jM                  d|jP                        |_(        |jM                  d|jR                        |_)        n[tE        |j                  ||jM                  dd      |jM                  dd      |jM                  dd            }|jU                  |       |j                  tV              j                  tV        jF                  |j                  k(  tV        j                  j                  d      tV        jX                  j!                  d            j#                         }t&        j)                  dt-        |       d|j                          	 |j[                  ||	|      \  }}t&        j)                  dt-        |       d |j                          t-        |      dkD  rt&        j)                  d"|d           |D ]  t]        fd#|D        d      }t%        d$|       |s(|d%   }t&        j)                  d&j                   d'jX                   d(       |j                  t^              j                  t^        j`                  j                  k(  t^        jH                  |k(        jK                         }|ru|jM                  dd      |_)        |jM                  d)d      |_1        |jM                  d*d      |_2        |jM                  d+d      |_3        |jM                  d,d      |_4        3	 t_        j                  ||jM                  dd      |jM                  d)d      |jM                  d*d      |jM                  d+d      |jM                  d,d      -      }|jU                  |       t&        j)                  d.j                           	 |jk                          t&        j)                  d0|j                          to        jp                  d3       ? t&        j)                  d4       |j+                          y# t8        $ r9}t&        j5                  d|j                   dt;        |              Y d}~d}~ww xY w# t8        $ r}t&        j5                  dt;        |              dddd}	 t&        j)                  d|j                          |jC                  |j2                        }|r*t&        j)                  d       |jA                  ||	|      }|}n7# t8        $ r+}t&        j5                  dt;        |              Y d}~nd}~ww xY wY d}~d}~ww xY w# t8        $ r.}t&        j5                  d!t;        |              g }Y d}~d}~ww xY w# t8        $ r9}t&        j5                  d/j                   dt;        |              Y d}~d}~ww xY w# t8        $ rI}|jm                          t&        j5                  d1|j                   dt;        |              Y d}~/d}~ww xY w# t8        $ rI}|jm                          t&        j5                  d2|j                   dt;        |              Y d}~d}~ww xY w# t8        $ r,}t&        j5                  d5t;        |              Y d}~d}~ww xY w# |j+                          w xY w)6z
    Daily cron job to update Twitter metrics for all connected accounts.
    Should be scheduled to run once daily (preferably during low-traffic hours).
    r   get_db_session   r*   FN   zTwitter Accounts :z,No Twitter accounts found for metrics updatezUpdating metrics for z Twitter accountsr   zToken :r,   z)Falling back to stored token for account z*Error getting temporary token for account r>   )r-   app_idr.   z!Failed to fetch account metrics: )r1   r2   r3   z(Attempting to refresh token for account z4Token refreshed successfully, retrying metrics fetchzToken refresh also failed: r1   r2   r3   r4   zFound z posts in database for account z	Received z$ posts from Twitter API for account zFailed to fetch post metrics: zSample post data: c              3   H   K   | ]  }|d    j                   k(  s|  yw)r7   N)r7   ).0prV   s     r   	<genexpr>z/update_twitter_metrics_daily.<locals>.<genexpr>9  s%     )r1EWCX\`\q\qCq!)rs   ""zMatching Post :r8   zFound metrics for post z (external ID: )r9   r:   r;   r<   )post_type_idr5   r3   r9   r:   r;   r<   z$Created new metrics record for post z Error creating metrics for post z+Successfully committed metrics for account z%Failed to commit metrics for account z#Error updating metrics for account x   z&Completed daily Twitter metrics updatez'Error in update_twitter_metrics_daily: )9src.utils.dbr~   r   rm   r   r   r?   r   joinr   rk   rI   r@   rA   rB   ro   rC   rD   rp   rN   rS   closelenr   rn   	branch_idrO   warningrL   rP   rF   r   rq   refresh_token_tempr   r"   r5   rj   rH   r1   r2   r3   rJ   r   r7   rG   nextr   r   r9   r:   r;   r<   rK   rM   rQ   rR   )r~   r!   	yesterdaytodaytwitter_accountsra   ru   rb   r^   r_   yesterday_str
date_rangerx   refreshed_tokenrefresh_errorry   new_account_metricsrU   rZ   r[   matching_postr8   existing_metricsnew_metricsrV   s                           @r   update_twitter_metrics_dailyr      s8   
 , "B)"44::<ILLN!Ek88$45::+==AQAQQ

&''++E20066t<$$((/!	

 #% 	 	"$45  KKFGt 	
q 	+C0@,A+BBSTU $BB/ ( K	GI %889J9JKE)U+}'TU\U_U_T`%ab)RSZS]S]R^'_` ( < <W=N=N O +2*E*EPWPaPab !* 3 3 5&]N
Y&.&D&DUKYc&dO" &(XX.A%B%I%I'<<

J'33y@& %' #
 &3B3F3F{TfTpTp3q&07F7J7J?\n\|\|7}&45D5H5HXjXvXv5w&2 +>-4ZZ$-"1"5"5k1"E&5&9&9/1&M$3$7$7q$I+' FF./ !1299$99WZZG$//33E:$55;;DA #%	  fSZL0OPWPZPZ|\]$$,$?$?{T^$_MJ KK)C
O+<<`ahakak`l mn z?Q&KK"4Z]O DE " %eD$()rZ)rtx$yM+];$"/	":&=dggYoVZVkVkUllm$no ,.884D+E+L+L,99TWWD,88IE,  %' )
 ,;B;;}VW;X,85<[[!5L,25<[[!5L,28?JPQ8R,56=kk(A6N,3e.>150907M10M*1++gq*A*1++gq*A-4[[Q-G+2;;x+C	/" !#{ 3 &.RSWSZSZR[,\ ]G%ePaIIKKK"Mgjj\ Z[ JJsOWK	X 	<= 	
I ! LL#Mgjj\Y[\_`a\b[c!de ! YLL#DSVH!MN45Z[&\OY&Nwzzl$[\*2*E*EgFWFW*X*"KK(^_.6.L.L_^iku.vO$3E$ Y'B3}CUBV%WXXYYh ! $LL#A#a&!JK!#J$X $- e &/OPTPWPWyXZ[^_`[aZb-c d de ! aKKMLL#HTVWZ[\W]V^!_``a  B7::,bQTUVQWPXYZ  I>s1vhGHHI 	
s^  !C3e &5e B[=%6c6]/Gc6A`"Ac6Dc6*Ba	c62b!?-e =	\?.\:4c68e :\??c6	`'`3A)_`	`&!````c6`c6"	a+#ac6ac6	b%.bc6bc6!	c3*>c.(c6.c33c66	e?>e=e ee 	f !e;5f ;f  f fconnected_account_idsrw   r`   c                 r   |s"t        j                         j                         }	 | j                  t              j                  t        j                  j                  |      t        j                  j                  d            j                         }|sdddS |D cg c]  }|j                   }}| j                  t              j                  t        j                  j                  |            j                         }|D ci c]  }|j                  | }	}| j                  t              j                  t        j                  j                  |      t        j                  |k\  t        j                  |k        j!                  t        j                        }
|
j                         }|ri }|D ]=  }|j                  |vrg ||j                  <   ||j                     j#                  |       ? t%        d |j'                         D              }t%        d |j'                         D              }t%        d |D              }|r|t)        |      z  nd}nd}d}d}d}||||t)        |      d}| j                  t*        t,              j/                  t,        t*        j0                  t,        j                  k(        j/                  t        t,        j                  t        j                  k(        j                  t,        j                  j                  |      t*        j                  |k\  t*        j                  |k  t,        j                  j                  d            }|j                         }t)        |      }t%        d	 |D              }t%        d
 |D              }t%        d |D              }t%        d |D              }||z   |z   }|j3                  |||||||dkD  r||z  ndd       g }|D ]  \  }}|j4                  |j6                  z   |j8                  z   }|j:                  dkD  r||j:                  z  nd}|j#                  |j                  |j<                  |j>                  |j@                  r|j@                  jC                         nd|j:                  |j4                  |j6                  |j8                  ||j<                  rd|j<                   ndd
        |jE                  d d       g } |D ]T  }|	jG                  |j                        }!| j#                  |j                  |jH                  |!r|!jJ                  ndd       V d| |jC                         |jC                         ||z
  jL                  dz   d||dd |D cg c]J  }|j                  jC                         |j                  |jN                  |jP                  |j:                  dL c}dS c c}w c c}w c c}w # tR        $ r<}"tT        jW                  dtY        |"              ddtY        |"       dcY d}"~"S d}"~"ww xY w)a  
    Generate a report of Twitter metrics for multiple connected accounts.
    
    Args:
        db: Database session
        connected_account_ids: List of IDs of the connected Twitter accounts
        start_date: Start date for report
        end_date: End date for report (defaults to today)
        
    Returns:
        Dictionary with aggregated account metrics, post metrics, and trends
    FNo connected accounts foundr'   c              3   B   K   | ]  }|r|d    j                   nd  yw)r   N)r1   r   r8   s     r   r   z-get_twitter_metrics_report.<locals>.<genexpr>  s)      * *1%%a7*s   c              3   z   K   | ]3  }t        |      d kD  r|d   j                  |d   j                  z
  nd 5 yw)   r   r   N)r   r1   r   s     r   r   z-get_twitter_metrics_report.<locals>.<genexpr>  sE      ) CFg,QRBR&&)=)==XYY)   9;c              3   4   K   | ]  }|j                     y wNr3   )r   metrics     r   r   z-get_twitter_metrics_report.<locals>.<genexpr>  s     #U6F$6$6#U   r   )followers_currentfollowers_growthtotal_impressionsavg_daily_impressionstotal_accountsc              3   :   K   | ]  \  }}|j                     y wr   r   r   pmr[   s      r   r   z-get_twitter_metrics_report.<locals>.<genexpr>  s     $NAR^^$N   c              3   :   K   | ]  \  }}|j                     y wr   r:   r   s      r   r   z-get_twitter_metrics_report.<locals>.<genexpr>  s     BEBrxxBr   c              3   :   K   | ]  \  }}|j                     y wr   r;   r   s      r   r   z-get_twitter_metrics_report.<locals>.<genexpr>  s     !H%"a"++!Hr   c              3   :   K   | ]  \  }}|j                     y wr   r<   r   s      r   r   z-get_twitter_metrics_report.<locals>.<genexpr>  s     Deb!		Dr   )
total_posttotal_post_impressionstotal_post_reactionstotal_post_likestotal_post_commentstotal_post_sharesavg_post_engagement_rateN!https://twitter.com/i/web/status/)
r=   r7   content	posted_atr3   r:   r;   r<   engagement_rate	permalinkc                     | d   S Nr    r   s    r   <lambda>z,get_twitter_metrics_report.<locals>.<lambda>  s    Q'8%9 r    TkeyreverseUnknownrI   usernameplatformr   )rw   r`   r+   
   )r   r"   r1   r2   r3   )r(   accounts_infor   account_summary	top_postsdaily_metricsz%Error in get_twitter_metrics_report: ri   )-r   rm   r   r?   r   r@   rI   in_rA   rB   rD   rk   r   r   r"   r5   order_byappendsumvaluesr   r   r   r   r   updater:   r;   r<   r3   r7   r   rE   rF   sortrH   external_account_namerl   r+   r1   r2   rL   rN   rO   rP   )#r!   r   rw   r`   accountsru   master_account_idsmaster_accountsmamaster_account_mapaccount_metrics_queryrx   metrics_by_accountr   total_followers_currenttotal_followers_growthr   r   r   post_metrics_querypost_metricstotal_postsr   r   r   r   r   r   r   rV   
engagementr   r   rv   r^   s#                                      r   get_twitter_metrics_reportr   w  s   $ <<>&&(\A88,-44##$9:''++E2
 #% 	
 $1NOO HPPGg77PP((=188  !34

#% 	
 3BBBbeeRiBB !#)< = D D44889NO++z9++x7!
 (&22
3	 	 0335 !#) O..6HHFH&v'B'BC"6#>#>?FFvNO '* *188:* '#
 &) )188:) &"
 !$#U_#U UP_$5O8L$Lef!&'#%&" !$%! "9 6!2%:!(m
  XX
 $))-=-@-@@
 $115E5H5HH
 &11556KL((J6((H4''++E2	
 	  *--/,'!$$N$N!NB\BB!!H<!HHD|DD/2EEHYY 	%&<$8 0#6!2YorsYs(<?U(Uyz 
 	 	$ 	HBBKK/"));J=?^^a=Oj2>>9UVO77$($9$9<<<@OOT__668QU!~~KK))#2\`\q\q@AVAV@WXw{ 		$ 	94H  	G/33G4M4MNN  jj#99@NN<<T]" 	 *(224$..0!J.44q8
  /"3B !0  #..88:,2,G,G!'!1!1%+%9%9#)#5#5
 	
w Q C~  A<SVHEF ws1vh-?@@AsQ   A3W1 W1 W"1AW1 W'Q4W1 AW,W1 "W1 1	X6:1X1+X61X6c                    |s"t        j                         j                         }	 | j                  t              j                  t        j                  j                  |      t        j                  j                  d            j                         }|sdddS i }|D ]  }| j                  t              j                  t        j                  |j                  k(        j                         }|r|j                  nd}||vrg ||<   ||   j                  |j                          | j                  t               j                  t         j"                  j                  |      t         j$                  |k\  t         j$                  |k        j'                  t         j$                        j                         }	g }
|D ]  }| j                  t(        t*              j-                  t*        t(        j.                  t*        j                  k(        j                  t*        j"                  |k(  t(        j$                  |k\  t(        j$                  |k  t*        j                  j                  d            j                         }|D ]  \  }}|
j                  |||d         |
}t1        |      }t3        d |D              }t3        d |D              }t3        d |D              }i }|j5                         D ]  \  }}|D cg c]  }|d	   |v s|d
    }}t1        |      t3        d |D              t3        d |D              t3        d |D              t3        d |D              dd||<   t3        d |D              }t3        d |D              }|dkD  s||z  ||   d<    g }|}||k  r|j7                         ddddddddddd}|D cg c]  }|d
   j$                  |k(  s| }}|D ]  }|d
   }|d	   }| j                  t              j                  t        j                  |k(        j                         }|rM| j                  t              j                  t        j                  |j                  k(        j                         nd}|r|j                  j9                         nd}d|v sd|v r`|d   dxx   dz  cc<   |d   dxx   |j:                  z  cc<   |d   dxx   |j<                  z  cc<   |d   dxx   |j>                  z  cc<   (d |v s.|d!   dxx   dz  cc<   |d!   dxx   |j:                  z  cc<   |d!   dxx   |j<                  z  cc<   |d!   dxx   |j>                  z  cc<    |j                  |       |tA        d"      z  }||k  rg }|D ]  }|d
   }|d#   }|j:                  |j<                  z   |j>                  z   } |jB                  dkD  r| |jB                  z  nd}!|j                  |jD                  |jF                  r|jF                  j7                         nd|jB                  |j:                  |j<                  |j>                  |!|jH                  rd$|jH                   ndd%        |jK                  d& d'(       |dd) }t3        d* |D              }t3        d+ |D              }"|dkD  r|"|z  nd}#|||"|#|d,}$d'd-||||dd.|||$|j5                         D %cg c]  \  }}%d/|i|% c}%}d0d1S c c}w c c}w c c}%}w # tL        $ r<}&tN        jQ                  d2tS        |&              dd3tS        |&       dcY d}&~&S d}&~&ww xY w)4ak  
    Generate comprehensive social media dashboard analytics for UI display.
    
    Args:
        db: Database session
        connected_account_ids: List of connected account IDs
        start_date: Start date for analytics
        end_date: End date for analytics (defaults to today)
        
    Returns:
        Dictionary with dashboard analytics data
    Fr   r'   r   )r   ptrf   c              3   :   K   | ]  }|d    j                     ywr   Nr   r   items     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>x  s     H4d4j..Hr   c              3   :   K   | ]  }|d    j                     ywr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>y  s     JTT$Z00Jr   c              3   :   K   | ]  }|d    j                     ywr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>z  s     F4:,,Fr   rf   r   c              3   4   K   | ]  }|j                     y wr   r   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s      Cb Cr   c              3   4   K   | ]  }|j                     y wr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s     EEr   c              3   4   K   | ]  }|j                     y wr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s     ABbiiAr   c              3   4   K   | ]  }|j                     y wr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s     "Kb2>>"Kr   g        )rU   	reactionsr;   r<   r3   r   c              3   4   K   | ]  }|j                     y wr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s     #LrBNN#Lr   c              3   h   K   | ]*  }|j                   |j                  z   |j                  z    , y wr   r:   r;   r<   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s&     "_"288bkk#9BII#E"_   02r   r   )rU   r   r;   r<   )r   r   facebook_pageNunknownr   r   rU   r   r   r;   r<   facebookr   r*   r   r   r   r   r3   r   r;   r<   r   r   c                     | d   S r   r   r   s    r   r   z6get_social_media_dashboard_analytics.<locals>.<lambda>      a(9&: r    Tr   r   c              3   :   K   | ]  }|d    j                     ywr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s     P4T
 6 6Pr   c              3   z   K   | ]3  }|d    j                   |d    j                  z   |d    j                  z    5 ywr   r   r   s     r   r   z7get_social_media_dashboard_analytics.<locals>.<genexpr>  s9     t_cT
 0 04:3F3F FdIZIZ Ztr   post_impressionspost_retweetspost_engagementspost_engagement_ratemedia_engagementsz7Social media dashboard analytics generated successfully)r   total_reactionstotal_commentstotal_sharespercentage_changer   )overall_metricsr   
top_tweetsengagement_metricssocial_networks)r(   r)   dataz/Error in get_social_media_dashboard_analytics: ri   )*r   rm   r   r?   r   r@   rI   r   rA   rB   rD   r   rk   rj   rl   r   r   r"   r5   r   r   r   r   r   r   r   itemsrF   r   r:   r;   r<   r   r3   r   rE   r7   r   rL   rN   rO   rP   )'r!   r   rw   r`   r   accounts_by_platformru   rv   r   rx   post_metrics_datarf   account_post_metricsr   r   r   r   r  r  r  platform_metricsaccount_idsr   platform_postsr   total_engagementr   current_date
daily_datadate_post_metricsr  rV   r   r   total_engagementsoverall_engagement_rater  r8   r^   s'                                          r   $get_social_media_dashboard_analyticsr#  +  s   $ <<>&&(uA88,-44##$9:''++E2
 #% 	
 $1NOO  " 	>GXXm4;;  G$=$==eg  <J~77yH3313$X. *11'**=	> ((#67>>44889NO++z9++x7
 (&22
3CCE	 	 / 	J#%88  $ d  --1A1D1DD f 55C ,,
: ,,8 ++//6	
 ce ! / B!((",* 	* ) ,'H<HHJ\JJFFF %9%?%?%A 	e!Hk5AgTT,EW[fEfd4jgNg ^,  CN CCEnEEA.AA""KN"KK#&*X& !$#L^#L L""_P^"__ 1$@PSd@d *+<=#	e( !h&$..0%&QAQRS+,1!WX!YJ 3? i$$t*BXBX\hBh i i) G$Z!,/
 ((#34;;<L<O<OS]<]^ddf % "$-!8!?!?!$$(A(AA"%'$  HV>;;AAC[d(C8Oy)'2a72y)+6"((B6y)*5D5y)(3ryy@38+/8A=8/<H</
;r{{J;/9RYYF9+G.   ,I1--LE h&J 
  	DdB:DBKK/"));J=?^^a=Oj2>>9UVO<<<@OOT__668QU!~~XXKK))#2\`\q\q@AVAV@WXw{	 		$ 	:DI^
  P<PPtgsttK\_`K`"36G"Gfg !2) 1$;!.
 P $/'6&4$0),$ "/(&8
 0@/E/E/G	$ ,(G "8!$
 	
y h8 !j\$  AFs1vhOP ws1vh-?@@Asq   A3\ I!\ ;\\B \ 9\ 	\!\%D0\ B\ E\ 5\\ \ 	]"&1]]"]"c                 &   |s"t        j                         j                         }	 | j                  t              j                  t        j                  |k(  t        j                  j                  d            j                         }|sdddS | j                  t              j                  t        j                  |j                  k(        j                         }| j                  t        t              j                  t        t        j                  t        j                  k(        j                  t        j                   |k(  t        j"                  |k\  t        j"                  |k  t        j                  j                  d            j%                         }t'        d |D              }t'        d |D              }t'        d |D              }	t'        d |D              }
t'        d |D              }t)        |      }|d	kD  r||z  nd	}g }|D ]  \  }}|j*                  |j,                  z   |j.                  z   }|j0                  d	kD  r||j0                  z  nd	}|j3                  |j4                  |j6                  r|j6                  j9                         nd
|j0                  |j*                  |j,                  |j.                  ||j:                  rd|j:                   nd
d        |j=                  d d       |d
d }g }|}||k  rm|D cg c]  \  }}|j"                  |k(  s| }}}t'        d |D              }|j3                  |j9                         |d       |t?        d      z  }||k  rmd|j                  |j@                  |r|jB                  ndd||||||d||	|
|d|dz  |dz  dz  |dz  dz  gddS c c}}w # tD        $ r<}tF        jI                  dtK        |              ddtK        |       dcY d
}~S d
}~ww xY w) aq  
    Get analytics for a specific account (like @SB_Bakery123 from the image).
    
    Args:
        db: Database session
        connected_account_id: ID of the connected account
        start_date: Start date for analytics
        end_date: End date for analytics (defaults to today)
        
    Returns:
        Dictionary with account-specific analytics data
    Fr&   r'   c              3   :   K   | ]  \  }}|j                     y wr   r   r   s      r   r   z1get_account_specific_analytics.<locals>.<genexpr>+  s     I52qIr   c              3   n   K   | ]-  \  }}|j                   |j                  z   |j                  z    / y wr   r   r   s      r   r   z1get_account_specific_analytics.<locals>.<genexpr>,  s*     ]ur12;; 6 B]   35c              3   :   K   | ]  \  }}|j                     y wr   r   r   s      r   r   z1get_account_specific_analytics.<locals>.<genexpr>-  s     =ur1"((=r   c              3   :   K   | ]  \  }}|j                     y wr   r   r   s      r   r   z1get_account_specific_analytics.<locals>.<genexpr>.  s     CURR[[Cr   c              3   :   K   | ]  \  }}|j                     y wr   r   r   s      r   r   z1get_account_specific_analytics.<locals>.<genexpr>/  s     ?Q299?r   r   Nr   r  c                     | d   S r   r   r   s    r   r   z0get_account_specific_analytics.<locals>.<lambda>E  r  r    Tr   r   c              3   h   K   | ]*  }|j                   |j                  z   |j                  z    , y wr   r   r   s     r   r   z1get_account_specific_analytics.<locals>.<genexpr>M  s&     "]"288bkk#9BII#E"]r   )r   engagementsr   r*   r   r   r  )r   total_likesr  r  d   gffffff?g?)account_infor  r  total_metricsoverall_engagement_rates)r(   r  z)Error in get_account_specific_analytics: ri   )&r   rm   r   r?   r   r@   rI   rA   rB   rj   r   rk   r   r   r   r   r"   r5   rD   r   r   r:   r;   r<   r3   r   r   rE   rF   r7   r   r   r   rl   rL   rN   rO   rP   )r!   r"   rw   r`   ru   rv   r   r   r!  r.  r  r  r   r"  r  r   rV   r   r   daily_engagementsr  r[   date_metricsdaily_engagementr^   s                            r   get_account_specific_analyticsr6    s   $ <<>&&(iA((+,33#77''++E2
 %' 	
 $1NOO -077 9 99

%' 	
 xx
 $))-=-@-@@
 &115II((J6((H4''++E2	

 #% 	  ILII]P\]]===ClCC?,??,'K\_`K`"36G"Gfg 
$ 	HBBKK/"));J=?^^a=Oj2>>9UVO<<<@OOT__668QU!~~XXKK))#2\`\q\q@AVAV@WXw{	 			  	:DI^
 !h&,8[52qBNNl<ZB[L[""]P\"]]$$$..0/& 
 I1--L h&  "** ' = =DR @ @Xa!
 )(9%1(9,C):' $/#.&4$0	" ,c1,t3s:,s2c9-)
 	
 \P  A@QIJ ws1vh-?@@AsE   A'O JO O'O+AO 7AO O 	P1PPPstore_idr   score_month
score_yearc                    	 |st        j                         j                  }|st        j                         j                  }t	        ||d      }|dk(  rt	        |dz   dd      t        d      z
  }nt	        ||dz   d      t        d      z
  }| j                  t              j                  t        j                  |k(  t        j                  j                  d            }|r"|j                  t        j                  |k(        }|j                         }|sdd||dS i }	d}
d}d}d}t        |      }|D ]  }| j                  t              j                  t        j                   |j"                  k(        j%                         }|sT|j&                  j)                         }| j                  t*              j                  t*        j,                  |j                   k(  t*        j.                  |k\  t*        j.                  |k        j1                  t*        j.                  j3                               j%                         }| j                  t4        t6              j9                  t6        t4        j:                  t6        j                   k(        j                  t6        j,                  |j                   k(  t4        j.                  |k\  t4        j.                  |k  t6        j                  j                  d            j                         }t=        d |D              }t=        d	 |D              }|r|j>                  nd}t        |      }|dkD  r||z  d
z  nd}|dkD  r||z  d
z  nd}|dz  d
z  }|dz  |dz  z   |dz  z   }tA        |d
      }tC        |d      tC        |d      tC        |d      tC        |d      ||||d|	|<   |
|z  }
||z  }||z  }||z  } |	r-t=        d |	jE                         D              t        |	      z  }nd}|dkD  r|
|z  d
z  nd}|dkD  r||z  d
z  nd}|dz  d
z  }| j                  tF              j                  tF        j                  |k(  tF        j                  |k(  tF        jH                  |k(  tF        jJ                  |k(        j%                         } | rtC        |d      | _&        tC        |d      | _'        tC        |d      | _(        tC        |d      | _)        tU        jV                  |	      | _,        || _-        |
| _.        || _/        || _0        || _1        t        j                  td        jf                        | _4        netG        ||tC        |d      tC        |d      tC        |d      tC        |d      tU        jV                  |	      ||
|||||      }!| jk                  |!       | jm                          d||||tC        |d      tC        |d      tC        |d      tC        |d      |	||
|||dto        |      dS # tp        $ rL}"tr        ju                  dtw        |"              | jy                          ddtw        |"       dcY d}"~"S d}"~"ww xY w)a  
    Calculate performance score for a store based on all connected social media accounts.
    
    Args:
        db: Database session
        store_id: Store ID
        branch_id: Optional branch ID
        score_month: Month for score calculation (1-12, defaults to current month)
        score_year: Year for score calculation (defaults to current year)
        
    Returns:
        Dictionary with performance score details
    r      r*   Fz*No connected accounts found for this store)r(   r)   r7  r   r   c              3   n   K   | ]-  \  }}|j                   |j                  z   |j                  z    / y wr   r   r   s      r   r   z4calculate_store_performance_score.<locals>.<genexpr>  s+     %cURQRbhh&<ryy&H%cr'  c              3   :   K   | ]  \  }}|j                     y wr   r   r   s      r   r   z4calculate_store_performance_score.<locals>.<genexpr>  s     &P%"ar~~&Pr   r/     g333333?g      ?g333333?   )scorer   reach_efficiencyposting_consistencyrU   r   r3   r1   c              3   &   K   | ]	  }|d      yw)r@  Nr   )r   r@  s     r   r   z4calculate_store_performance_score.<locals>.<genexpr>  s     U5gUs   )r7  r   overall_scorer   rA  rB  platform_scoresr   r  r   total_followersconnected_accounts_countr8  r9  Tr   r  r   rF  rG  )r(   r7  r   r8  r9  rD  r   rA  rB  rE  r1  score_interpretationz%Error calculating performance score: ri   r'   N)=r   rm   monthyearr   r   r?   r   r@   r7  rA   rB   r   rD   r   r   rI   rk   rj   rl   r   r   r"   r5   r   descr   r   r   r   r   r1   minroundr   r   r8  r9  rD  r   rA  rB  jsondumpsrE  r   r  r   rF  rG  timezoneutc
updated_atrJ   rK   get_score_interpretationrL   rN   rO   rP   rM   )#r!   r7  r   r8  r9  rw   r`   r?   connected_accountsrE  r  r   rF  r   rG  ru   rv   r   rx   r   platform_engagementplatform_impressionsplatform_followersr  r   rA  rB  platform_scorerD  r"  overall_reach_efficiencyoverall_posting_consistencyexisting_score	new_scorer^   s#                                      r   !calculate_store_performance_scorer^  }  s\   (yA",,...K!,,J *k15
"JNAq1I14EEHJa;iQ>OOH )*11%%1''++E2

 LL!1!;!;y!HIE"YY[! G$&	  #&'9#: ) @	*GXXm4;;  G$=$==eg  "%77==?H !hh':;BB#88GJJF#//:=#//8; h*66;;=>uuw	  88   d  --1A1D1DD f 55C ,,
: ,,8 ++//6	
 ce  #&%cVb%c"c#&&P<&P#P >M!:!:ST .N UiklTl25IICOrsOTfijTj 47I IC Opq#1B#6#< .38H48OPTgjnTnoN !5N ~q1#(!#<$)*:A$>',-@!'D'13/	)OH%  33!5511O>)KA@	*F UO<R<R<TUUX[\kXllMM SdfgRg#36G#G##MmnRadeRe$5$G#$Mkl '2R'7#'=# "78??!**h6!++y8!--<!,,
:	

 %' 	 +0+BN(-23JA-NN*.34La.PN+167RTU1VN.-1ZZ-HN*)4N&.>N+/@N,-<N*6NN3(0X\\(BN% .!##M15 %&=q A!&'?!C$)*Eq$I $

? ;'!1"3 /)A'%I  FF9
		  "&$"=!4$%<a@ %&> B#()Da#H.*$4%6#2,D %=]$K%
 	
*  A<SVHEF
 ws1vh-?@@As&   DX S-X 	Y"AYY"Y"r@  c                 @    | dk\  ry| dk\  ry| dk\  ry| dk\  ry| d	k\  ry
y)z,Get interpretation of the performance score.Z   zExcellent performanceP   zVery good performanceF   zGood performance<   zAverage performance2   zBelow averagezNeeds improvementr   )r@  s    r   rT  rT  M  s7    {&	"&	"!	"$	""r    c                    	 | j                  t              j                  t        j                  |k(  t        j                  dk(        j                  t        j                  j                         t        j                  j                               }|r"|j                  t        j                  |k(        }|j                         }t        j                         }g }t        d      D ]  }|t        d|z        z
  }|j                  }	|j                   }
| j                  t              j                  t        j                  |k(  t        j                  |	k(  t        j                  |
k(  t        j                  dk(        }|r"|j                  t        j                  |k(        }|j                         }|ri }|j"                  r 	 t%        j&                  |j"                        }|j+                  |	|
|j,                  |j.                  |j0                  |j2                  ||j4                  |j6                  |j8                  |j:                  |j<                  dt?        |j,                        |j@                  jC                         |jD                  jC                         d       |j+                  |	|
ddddi dddddddd	d	d        |jG                          d	}|r i }|j"                  r 	 t%        j&                  |j"                        }||j                  |j                  |j                  |j,                  |j.                  |j0                  |j2                  ||j4                  |j6                  |j8                  |j:                  |j<                  dt?        |j,                        |j@                  jC                         |jD                  jC                         d
}n||d	d	ddddi dddddddd	d	d
}d||||dS # t$        j(                  $ r i }Y 3w xY w# t$        j(                  $ r i }Y $w xY w# tH        $ r<}tJ        jM                  dtO        |              ddtO        |       dcY d	}~S d	}~ww xY w)a   
    Get the latest performance score and last 12 months of scores for a store.
    
    Args:
        db: Database session
        store_id: Store ID
        branch_id: Optional branch ID
        
    Returns:
        Dictionary with latest performance score and last 12 months data
    Tr;  r>  r*   rH  )rJ  rK  rD  r   rA  rB  rE  r1  rI  rE   rS  r   zNo data availableN)r7  r   r8  r9  rD  r   rA  rB  rE  r1  rI  rE   rS  )r(   r7  r   latest_scorelast_12_monthsz"Error getting performance scores: Fri   r'   )(r?   r   r@   r7  	is_activer   r9  rL  r8  r   rj   r   rm   ranger   rJ  rK  rE  rO  loadsJSONDecodeErrorr   rD  r   rA  rB  r   r  r   rF  rG  rT  rE   rF   rS  r   rL   rN   rO   rP   )r!   r7  r   latest_queryrf  r  rg  itarget_datetarget_monthtarget_yearmonth_querymonth_scorerE  latest_score_datar^   s                   r   get_latest_performance_scorert  ]  se    \Axx 56==!**h6!++t3
 (!,,113!--224
 	 '../D/N/NR[/[\L#))+  ||~r C	A&Q)??K&,,L%**K ((#89@@%..(:%11\A%00K?%//47	K )001F1P1PT]1]^%++-K"$..-*.**[5P5P*Q %%)'%0%>%>'2'B'B(3(D(D+6+J+J'6'2'>'>,7,H,H-8-J-J+6+F+F4?4X4X& -E[E^E^,_"-"8"8"B"B"D"-"8"8"B"B"D#' * %%)'%&'(()+,')'(,--.+,45& -@"&"&#' cC	L 	  ! O++)&*jj1M1M&NO
 %)33+77*55!-!;!;#/#?#?$0$A$A'3'G'G#2#/#;#;(4(E(E)5)G)G'3'C'C0<0U0U" )AA[A[(\*55??A*55??A'!. %&#"!"#$$%'(#%#$())*'(01" )<""'!.  "-,
 	
M  // -*,-n ++ )&(O)n  A9#a&BC ws1vh-?@@Ash   GP6 O>#DP6 /P C/P6 >PP6 PP6 P3/P6 2P33P6 6	Q;?1Q60Q;6Q;c            
      p   ddl m}   |        }	 t        j                         }|j                  }|j
                  }|j                  t        j                        j                  t        j                  j                  d            j                         j                         }|D cg c]  }|d   	 }}t        j                  dt!        |       d| d|        d}d}|D ]k  }	 t#        ||d||      }	|	d   r$|d	z  }t        j                  d
| d|	d           n0|d	z  }t        j%                  d| d|	j'                  dd              m t        j                  d| d|        dd| d||||d|j-                          S c c}w # t(        $ r3}
|d	z  }t        j%                  d| dt+        |
              Y d}
~
d}
~
ww xY w# t(        $ rL}
t        j%                  dt+        |
              ddt+        |
       dcY d}
~
|j-                          S d}
~
ww xY w# |j-                          w xY w)z
    Monthly cron job to calculate performance scores for all stores.
    Should be scheduled to run on the 1st of each month.
    r   r}   Fz#Calculating performance scores for z stores for /Nr(   r   z(Successfully calculated score for store r>   rD  z$Failed to calculate score for store r)   zUnknown errorz"Error calculating score for store z:Monthly performance score calculation completed. Success: z
, Failed: TzCalculated scores for z stores)r(   r)   successful_calculationsfailed_calculationsr8  r9  z0Error in monthly performance score calculation: ri   r'   )r   r~   r   rm   rJ  rK  r?   r   r7  r@   rA   rB   distinctrD   rN   rS   r   r^  rO   rH   rL   rP   r   )r~   r!   r  r8  r9  	store_idsr7  rw  rx  resultr^   s              r   $calculate_monthly_performance_scoresr|    s\   
 , "B3||~"((!&&
 HH-667>>''++E2

(*SSU 	 2;;XXa[;	;9#i.9IVaUbbcdncopq"#! 	H:$Z )$+q0+KK"J8*TVW]^mWnVo pq'1,'LL#GzQSTZT^T^_hjyTzS{!|}	$ 	PQhPiis  uH  tI  J  	K /0G/HP'>#6&$
 	
Q <(  #q(#A(2cRSfXVW   AGAxPQ ws1vh-?@@

	A 	
sm   BG &F22G %A(F)G G 	G)G>G GG 	H 1HH H# H  H# #H5)r>  r   )NNN)1sqlalchemy.ormr   r   r   r   loggingtypingr   r   r	   r
   requestsrO  rQ   rr   
sqlalchemyr    src.marketing.apps.Account.modelr   r   "src.marketing.apps.Analytics.modelr   r   r   src.marketing.apps.post.modelr   /src.marketing.core.Analytics.providers.twitterxr   +src.marketing.core.Analytics.providers.baser   r   basicConfigINFO	getLogger__name__rN   rP   r   intr{   r   r   r#  r6  r^  floatrT  rt  r|  r   r    r   <module>r     ss   " . .  , ,      L k k : L M   ',, '			8	$
8# 8 VAVAVA VA 
#s(^	VApu~ 	qAqA9qA qA 	qA
 
#s(^qAp  $	JAJA9JA JA tn	JA
 
#s(^JAb  $	~A~A~A ~A tn	~A
 
#s(^~AP  $!% $MAMAMA }MA #	MA
 MA 
#s(^MA`#E #c #&  $lAlAlA }lA 
#s(^	lA^;r    