
    Yj&             	          U d 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ZddlZddl	m
Z
mZ ddlmZmZ ddlmZ ddlmZ ddlZddlmZmZmZmZmZmZmZmZ ddlmZ  ee      j?                         j@                  Z!e!j@                  Z"ejF                  jI                  d e%e"             ejF                  jI                  d e%e"d	z               dd
l&m'Z'm(Z( ddl)Z*ddl+m,Z,m-Z-m.Z.m/Z/m0Z0 ddl1Z2ddl3Z3 ee4      Z5ejl                  jo                  dd      e5_8        de5jr                  d<    ed      e5jr                  d<   de5jr                  d<   e5ju                  e3jv                         dZ<de' ddddZ=de' dddZ>dZ?de? dZ@	  ed      j                         j                         ZC ed!      ZE ed"      ZFddd#ZGd$ZHi ZIeJeKd%<   d$ZLi ZMeJeKd&<   d$ZNi ZOeJeKd'<   d$ZP ed(      ZQdaRi ZSeJeKd)<   d$ZTi ZUeJeKd*<   d$ZVd+eWeJ   fd,ZXd-e%d+eJdz  fd.ZYd/e%d0e%dz  d1e%dz  fd2ZZd-e%d+eWeJ   fd3Z[d-e%d+eJfd4Z\d5eJd6eJd+eJfd7Z]d-e%d/e%d0e%d1e%fd8Z^d9d:d;d<d9d=Z_d+e%fd>Z`d+e%fd?Zad+e%fd@Zbd+ecfdAZdd/e%d0e%d1e%d+eJfdBZedCeWd+eJfdDZfd/e%d0e%d1e%fdEZgd/e%d0e%d1e%d+eWeJ   fdFZhddGecd+eJfdHZid+e%fdIZjdJeJd+eJfdKZkdL ZldMdMdNdOdPdQZmdRdSdTdUdVZndW Zodd/e%d0e%d1e%d+eJfdXZpd/e%d0e%d1e%d+eWeJ   fdYZqd/e%d0e%d1e%fdZZre5j                  d[        Ztd\ Zue5j                  d]d^d_g`      da        Zwe5j                  db      dc        Zxe5j                  dd      e-de               Zye5j                  df      e-dg               Zze5j                  dh      e-di               Z{e5j                  dj      e-dk               Z|e5j                  dl      e-dm               Z}e5j                  dn      e-do               Z~e5j                  dp      e-dq               Ze5j                  dr      e-ds               Ze5j                  dt      e-du               Ze5j                  dv      e-dw               Ze5j                  dx      e-dy               Ze5j                  dz      e-d{               Ze5j                  d|      e-d}               Ze5j                  d~      e-d               ZdZdddZg dg ddZdddddZde%fdZded+e%fdZd+e%fdZded+e%fdZe5j                  d      e-d               Ze5j                  d      e-d               Ze5j                  d      e-d               Ze5j                  d      e-d               Ze5j                  d      d        Zd Zd Z e        e4dk(  r8 ecejl                  jo                  dd            Ze5j1                  ded       yy# eD$ r d ZCY w xY w)u{   
Dashboard PX3 Lab — esqueleto inicial.
Login simples, sidebar fixa, secao de Pipelines com dados reais do Linkia (GHL).
    N)ThreadPoolExecutoras_completed)datetime	timedelta)Path)ZoneInfo)Flaskrender_templaterequestredirecturl_forsessionflashjsonify)GoogleAdsClientrelatorio_semanal)	PX3_TOKENPX3_LOCATION_ID)check_loginlogin_requiredUSERSget_user_roleget_user_ghl_idDASHBOARD_SECRETzpx3-dash-secret-change-me-2026px3dashSESSION_COOKIE_NAME   daysPERMANENT_SESSION_LIFETIMELaxSESSION_COOKIE_SAMESITEz$https://services.leadconnectorhq.comzBearer z
2021-07-28zapplication/json)AuthorizationVersionAcceptzContent-Typez
2021-04-15)r#   r$   r%   act_519765955435243z!https://graph.facebook.com/v21.0/z	/insightsz/opt/mia/config/meta_token.txt zAmerica/Sao_PauloUTC)datatsi  _opps_cache_meta_cache_conv_cachez&/opt/mia/config/google_ads_tokens.json_gads_cache_overview_cachereturnc                  (   t        j                          } t        d   r| t        d   z
  t        k  r	t        d   S 	 t        j                  t
         dt        dt        id      }|j                          |j                         j	                  dg       xs g }g }|D ]  }|j	                  d
      }|sg }|j	                  dg       xs g D ]5  }|j                  |j	                  d
      |j	                  dd      d       7 |j                  ||j	                  dd      ||D ci c]  }|d
   |d    c}d        |t        d<   | t        d<   |S # t        $ r.}t        j                  j                  d|        g }Y d	}~d	}~ww xY wc c}w )z9Lista todas as pipelines do location, com cache de 5 min.r)   r*   z/opportunities/pipelines
locationId   headersparamstimeout	pipelineszFalha ao listar pipelines: Nidstagesnamer'   )r9   r;   Sem nome)r9   r;   r:   
stages_map)time_pipelines_cache	_PIPE_TTLrequestsgetGHL_BASEHEADERSr   raise_for_statusjson	Exceptionapploggerwarningappend)	nowrpipeser8   ppidr:   ss	            3/opt/mia/workspace/clientes/px3lab/dashboard/app.pylistar_pipelinesrT   z   s   
))+CS+;D+A%AY$N''LLj01 /2	
 	
["-3 I 
eeDkx$* 	JAMMtaeeFB6GHI	Jfj1 ;ABaqw&	1B		

   )V T1  

8<=$ Cs   AE &F	F$FFpipeline_idc                 :    t               D ]  }|d   | k(  s|c S  y )Nr9   )rT   )rU   rP   s     rS   get_pipelinerW      s*     T7k!H     presetcustom_inicio
custom_fimc                    t        j                  t              }|j                  dddd      }| dk(  r(|t	        d      z
  j                  dddd      }|}d}n=| d	k(  r(|t	        d
      z
  j                  dddd      }|}d}n| dk(  r|j                  ddddd      }|}d}n| dk(  r@|j                  ddddd      }|t	        d      z
  }	|	j                  ddddd      }|	}d}n| dk(  r|r|r	 t        j
                  |      j                  t        dddd      }t        j
                  |      j                  t        dddd      }|j                  d       d|j                  d       }n!t        j                         \  }
}}}}|
|||dfS |j                  t              }
|j                  t              }|
||||fS # t        $ r t        ddd      cY S w xY w)z
    Retorna (inicio_utc, fim_utc, inicio_br, fim_br, label).
    presets: semana_atual | ultimos_7 | ultimos_30 | este_mes | mes_passado | custom
       ;   r   )hourminutesecondmicrosecond	ultimos_7   r   Ultimos 7 dias
ultimos_30r   Ultimos 30 diaseste_mes   )dayr_   r`   ra   rb   Este mesmes_passado)secondsMes passadocustom)tzinfor_   r`   ra   rb   %d/%m/%Y a semana_atualNzEsta semana (sex 09h - sex 09h))r   rL   TZ_BRreplacer   fromisoformatstrftimerG   calcular_periodorelcalcular_janela
astimezoneTZ_UTC)rY   rZ   r[   agora_brhoje_br	inicio_brfim_brlabelprimeiro_dia_mesultimo_dia_passado
inicio_utcfim_utclbls                rS   rx   rx      s.   
 ||E"HBr"!LG	q 11::!TUcd:e	 	<		r 22;;1UVde;f	!	:	$$1QTU$V		=	 #++!A[\+]-	!0DD&..11Qq^_.`	#	8	*	@ ..}=EEUYZcdmn|}E~I++J7??uSU^`ikyz?{F ))*56c&//*:U9VWE 7:6I6I6K3
GS)V7Iv7XXX%%f-J'Gw	6588  	@#ND$??	@s   8A?G
 
G#"G#c                    g }d}d}d}	 |dz  }t         | dd}|r|r
||d<   ||d<   	 t        j                  t         dt        |d	
      }|j
                  dk(  rt        j                  d       h|j                          |j                         }|j                  dg       xs g }	|j                  |	       |j                  di       xs i }
|
j                  d      }|
j                  d      }|
j                  d      r|	r|dkD  r	 |S t        j                  d       # t        $ r0}t        j                  j                  d|  d|        Y d}~|S d}~ww xY w)zJVariacao do buscar_oportunidades do relatorio, mas para qualquer pipeline.Nr   ri   d   )location_idrU   limit
startAfterstartAfterIdz/opportunities/searchr   r4        zErro buscar opps : opportunitiesmetanextPageUrl2   g333333?)r   rA   rB   rC   rD   status_coder>   sleeprE   rF   rG   rH   rI   rJ   extend)rU   todasstart_afterstart_after_idpaginasr6   rM   r)   rO   oppsr   s              rS   buscar_opps_pipeliner      sg   EKNG
1*&

 >#.F< %3F>"	*12	A }}#

1 668D
 xx,2Txx#)rhh|,.1xx&dglL 	

4C ,  	JJ!2;-r!EF L	s   AD" 1 D" "	E+%EEc                    t        |       }g }g }|D ]  }t        j                  |j                  dd            }t        j                  |j                  dd            xs% t        j                  |j                  dd            }|r1||cxk  r|k  r&n n#|j	                  |       |j	                  |       |s||cxk  r|k  sn |j	                  |        t        |D ch c]  }|j                  d      s|d    c}      }	i }
i }d }d }t        d	      5 }|	D cg c]  }|j                  ||       }}t        |      D ]  }|j                         \  }}||
|<    	 d d d        t        d	      5 }|	D cg c]  }|j                  ||       }}t        |      D ]  }|j                         \  }}|||<    	 d d d        ||||
|d
S c c}w c c}w # 1 sw Y   xxY wc c}w # 1 sw Y   ,xY w)N	createdAtr'   lastStageChangeAtlastStatusChangeAt	contactIdc                 0    | t        j                  |       fS N)ry   buscar_notascids    rS   _fnotesz'coletar_dados_pipeline.<locals>._fnotes  s    C$$S)))rX   c                 0    | t        j                  |       fS r   )ry   buscar_contator   s    rS   	_fcontactz)coletar_dados_pipeline.<locals>._fcontact  s    C&&s+++rX   
   )max_workers)r   dentronovosnotas_por_contatocontatos_full)
r   ry   parse_iso_utcrB   rK   listr   submitr   result)rU   r   r   r   r   r   opcreatedlast_changecontact_idsr   r   r   r   exr   futsfutnotascts                       rS   coletar_dados_pipeliner     s    -EFE 	##BFF;$;<''/BB(GH 
CL]L]FF',M
 zW77LLMM"Z;A'AMM"	 &PBBFF;<O;PQKM*, 
	+ +r3>?C		'3'??% 	+CJC%*c"	++ 
	+ $r5@Ac		)S)AA% 	$CjjlGC!#M#	$$ .& - Q @+ + B$ $sH   'G>G"G"'G *G"?G3G.*G3G""G+.G33G<dadosr=   c                 ,   | d   }| d   }| d   }| d   }dt         dt         dt         fd}d	}	d	}
d	}i }|D ]  }|j                  d
d      }|j                  |d      }|j                  dd      }|dk(  r|dz   }n|dk(  r|dz   }n|}|j                  |d	      dz   ||<    |||      }|dk(  r|
dz  }
|	dz  }	|dk(  r|dz  }|dk(  s|	dz  }	 t        |      }t        |      }|r|	|z  dz  nd	}g }|D ]  }|j                  d      }|j                  |i       xs i }|j                  d      xs dj                         xsT |j                  dd      xs d d|j                  dd      xs d j                         xs |j                  d      xs d}|j                  d      xs d}|j                  |g       xs g }g }|D ]L  }t	        j
                  |j                  dd            }|s+||cxk  r|k  s7n :|j                  ||f       N |j                  d d !       |r^|d	   d   }t	        j                  |j                  d"      xs |j                  d#      xs d      } | rt	        j                  | d$      nd%}!nd%}!|j                  d
d      }|j                  |d      }|j                  d      dk(  r|dz   }n|j                  d      dk(  r|dz   }t	        j
                  |j                  d&d            }"|"r$|"j                  t              j                  d'      nd(}#|j                  ||t        j                  d)d|xs d      ||#|"r|"j                         nd	|!d*        |j                  d+ ,       |||	|
||||d-S ).Nr   r   r   r   
stage_nomestatusr0   c                     | xs dj                         }|xs dj                         }|dk(  sd|v sd|v sd|v ry|dk(  sd|v sd|v ry	d
|v sd|v sd|v sd|v sd|v ryy)Nr'   wonganhamosfechadvencidolostperdidperdaperdidoqualificpropostaagendamzlead em testenegociaqualificadooutros)lower)r   r   rR   sts       rS   _classificaz%agregar_pipeline.<locals>._classifica8  s    2$$&l!!#;*/X]i1n<8q=GqL?jAoa?VWCW[dhi[i rX   r   pipelineStageIdr'   	SEM STAGEr   
 (perdido)r    (ganho)ri   r   r   r   r   r   contactName	firstName lastNamer;   r<   phone	dateAddedc                     | d   S Nr    xs    rS   <lambda>z"agregar_pipeline.<locals>.<lambda>s  s
    1Q4 rX   T)keyreversebodyTextbody   zSem observacao no periodor   %d/%m/%Y %H:%M-z\D)nometelefonetelefone_cleanstagedata_entrada	data_sortobsc                     | d    S )Nr   r   r   s    rS   r   z"agregar_pipeline.<locals>.<lambda>  s    q~o rX   r   )totalr   qualificadosfechamentosperdidos	taxa_qualcontagem_stagelinhas)strrB   lenstripry   r   rK   sort
strip_htmltruncarr{   rt   rw   resub	timestamp)$r   r=   r   r   r   r   r   r   r   r   r   r   r   r   stage_idr   r   stage_nome_displayclsr   novos_totalr   r   r   r   r   r   r   notas_na_janelandultimoobs_txtr   r   data_entrada_brs$                                       rS   agregar_pipeliner  1  s   8_F'NE/*M12	 	S 	S 	 LKHN 66+R0^^Hk:
"%V!+l!:u_!+j!8!+-;-?-?@RTU-VYZ-Z)**f-*1KALIMHM!AL), KEe*K6A+c1qIF .
ff[!sB'-2VVM"(b//1 &&R(.B/q
21F1L"0MNTTVvvf~ 	 	 66'?(b!%%c2.4" 	/A!!!%%R"89AZ1//&&1v.	/ 	>$Q'*FnnVZZ
%;%Wvzz&?Q%WUWXG/6#++gs+<WC-C66+R0^^Hk:
66(v%#l2JVVH&#j0J##BFF;$;<DKGu%../?@QT 	 	$"$&&HN"C# /4;W..0
	
I.
` KK-K. $"(	 	rX   c                 r   |  d| d| d| }t        j                          }|t        v r.t        |   }||d   z
  t        k  r|d   |d   |d   |d   |d   fS t        |       }|syt	        ||xs d	|xs d	      \  }}	}
}}t        |d
   ||	      }t        ||d   ||	      }|||||	|dt        |<   ||||	|fS )zDRetorna (dados, agg, inicio_utc, fim_utc, label) com cache de 5 min.|r*   r   aggr   r   r   )NNNNr'   Nr9   r=   )r*   r   r  r   r   r   )r>   r+   	_OPPS_TTLrW   rx   r   r  )rU   rY   rZ   r[   	cache_keyrL   entrypiper   r   
_inicio_br_fim_brr   r   r  s                  rS   coletar_cachedr    s   -q-*FI
))+CKI&ty(>5<|1DeIFVX]^eXfff$D)6F%z'9T73JWe #4:z7CE
5$|"4j'
JC  K	 #z7E11rX   last_7dlast_30d
this_month
last_month)rc   rf   rh   rl   rs   c                     	 t        | xs d      }|d}|j                  dd      j                  dd      j                  dd      }d| S # t        t        f$ r d}Y Nw xY w)Nr           z,.2f,@.zR$ )float	TypeError
ValueErrorru   )valuevrR   s      rS   _fmt_brlr(    sq    %*1 T(A			#s##C-55c3?A9 z" s   A A A c                     	 t        t        | xs d            }|dj	                  dd      S # t        t        f$ r d}Y 'w xY w)Nr   r   r"  )intr#  r$  r%  ru   r&  r'  s     rS   _fmt_intr,    sN    ejq!" UC%% z" s   . AAc                 \    	 t        | xs d      }|ddS # t        t        f$ r d}Y w xY w)Nr   r  .2f%)r#  r$  r%  r+  s     rS   _fmt_pctr0    s@    %*1 WA; z" s    ++c           	          | syd}| D ]U  }|j                  d      xs dj                         }|dv s+	 |t        t        |j                  d      xs d            z  }W |S # t        t
        f$ r Y kw xY w)zASoma actions de tipo 'lead' + 'offsite_conversion.fb_pixel_lead'.r   action_typer'   )leadz offsite_conversion.fb_pixel_leadr&  )rB   r   r*  r#  r$  r%  )actionsr   ats       rS   _extrair_leadsr7    s    E UU=!'R..0<<U155>#6Q788	 L z* s   *A""A43A4c                    t         st        d      t         dddd}d}| dk(  ra|r_|r]d|d	|d
|d<   	 t        j                  |      j	                  d      }t        j                  |      j	                  d      }| d| }n4t        j                  | d      }||d<   ddddd}|j                  |d      }t        j                  t        |d      }	|	j                  dk7  r(t        d|	j                   d|	j                  dd        |	j                         }
|
j                  dg       xs g |dS # t
        $ r
 | d| }Y w xY w)zn
    Busca insights de campanhas no Meta Ads.
    Retorna dict com 'campanhas' (lista) e 'periodo_label'.
    z<Token Meta nao configurado em /opt/mia/config/meta_token.txtzRcampaign_name,campaign_id,spend,impressions,clicks,ctr,cpm,actions,reach,objectivecampaignr   )access_tokenfieldslevelr   r'   ro   
{"since":"","until":""}
time_rangerq   rr   r  date_presetre   rg   rk   rn   )r  r  r  r  r   r6   r7      z	Meta API r   Ni,  r)   )campanhas_rawperiodo_label)_META_TOKENRuntimeErrorr   rv   rw   rG   META_PRESET_MAPrB   rA   META_GRAPH_URLr   textrF   )rY   rZ   r[   r6   r   didfrA  	label_maprM   r)   s              rS   buscar_meta_insightsrN    sc   
 YZZ $f	F Em
@MzZ|	6''6??
KB''
3<<ZHBd#bTNE &))&)< +}')$'	
	 k+;<^FB?A}}Yq}}oRt~FGG668D!XXfb17R%PP#  	6$oS5E	6s   AD5 5EErD  c                    d}d}d}d}d}g }| D ]  }	 t        |j                  d      xs d      }	 t	        t        |j                  d      xs d            }		 t	        t        |j                  d      xs d            }
	 t        |j                  d      xs d      }	 t        |j                  d      xs d      }	 t	        t        |j                  d      xs d            }t        |j                  d	      xs g       }|dkD  r||z  nd}||z  }||	z  }||
z  }||z  }||z  }|j                  |j                  d
      xs d|j                  d      xs dt        |      |t        |	      t        |
      ||dkD  rt        |      ndt        |      t        |      d
        |j                  d        |dkD  r||z  nd}|dkD  r||z  dz  nd}|dkD  r||z  dz  nd}t        |      t        |      t        |      ||dkD  rt        |      ndt        |      t        |      d}||dS # t        t        f$ r d}Y w xY w# t        t        f$ r d}	Y w xY w# t        t        f$ r d}
Y w xY w# t        t        f$ r d}Y w xY w# t        t        f$ r d}Y w xY w# t        t        f$ r d}Y w xY w)z@Agrega resumo e tabela de campanhas a partir do raw da API Meta.r  r   spendimpressionsclicksctrcpmreachr4  campaign_namer<   	objectiver   )
r   objetivogasto	gasto_raw
impressoescliquesleadscplrS  rT  c                     | d    S )NrZ  r   r   s    rS   r   zagregar_meta.<locals>.<lambda>Y  s    !K. rX   r   r     )gasto_totalr[  r\  r]  r^  rS  rT  )resumo	campanhas)r#  rB   r$  r%  r*  r7  rK   r(  r,  r0  r   )rD  ra  impressoes_totalcliques_totalleads_totalreach_totalrc  crP  r[  r\  rS  rT  rU  r]  r^  	cpl_total	ctr_total	cpm_totalrb  s                       rS   agregar_metarl    s   KMKKI .	!%%.-A.E	U155#7#<1=>J	%h 4156G	e)*C	e)*C	aeeGn123E quuY/526!&uu}uJ& uuEE/*8jk*1ce_":.($)AI8C=3C=C=
 	G.b NN0N1/:Q{*CI<Lq<P!11C7VYI;Ka;O//$6UXI  ,/0M*&1Aox	"3	"	"F 955} :& 	E	 :& 	J	 :& 	G	 :& 	C	 :& 	C	 :& 	E	sj   H6'I'I&I>"J'J.6I
II#"I#&I;:I;>JJJ+*J+.KKc                    d|  d| d| }t        j                          }|t        v r&t        |   }||d   z
  t        k  r|d   |d   |d   fS t        | ||      }t	        |d         }||d   |d   |d   dt        |<   |d   |d   |d   fS )	Nzmeta|r  r*   rb  rc  rE  rD  r*   rb  rc  rE  )r>   r,   	_META_TTLrN  rl  )rY   rZ   r[   r  rL   r  insightsr  s           rS   coletar_meta_cachedrq  l  s    xqq=I
))+CKI&ty(?E+$6o8NNN#FM:FH
x0
1Ch-%!/2	K	 x=#k*H_,EEErX   c           
         t         sg S t         ddddd}| dk(  r|r|rd|d|d	|d
<   nt        j                  | d      }||d<   	 t        j                  t        |d      }|j
                  dk7  rg S |j                         j                  dg       xs g }g }|D ]  }|j                  d      xs d}		 t        j                  |	      }
|
j                  d      }	 t        |j                  d      xs d      }t        |j                  d      xs g       }|j                  ||	|t!        |d      d        |j#                  d        |S # t        $ r g cY S w xY w# t        $ r |	}Y w xY w# t        t        f$ r d}Y w xY w)z
    Busca insights diarios do Meta Ads (breakdown=date), retorna lista
    [{"data": "dd/mm", "leads": int, "gasto": float}] ordenada por data.
    zspend,actionsaccountri   rC  )r:  r;  r<  time_incrementr   ro   r=  r>  r?  r@  r  rA  r   rB  r)   
date_startr'   %d/%mrP  r   r  r4  r   )r)   data_isor]  rY  c                 &    | j                  dd      S )Nrw  r'   )rB   r   s    rS   r   z$buscar_meta_diario.<locals>.<lambda>  s    QUU:r2 rX   r   )rF  rH  rB   rA   rI  r   rF   rG   r   rv   rw   r#  r$  r%  r7  rK   roundr   )rY   rZ   r[   r6   rA  rM   r)   serierowru  dtr   rP  r]  s                 rS   buscar_meta_diarior}    s   
 	 $!F m
@MzZ|%))&)< +}LLC==CIvvx||FB'-2 E hWW\*0b
	''
3BKK(E	#'''*/a0E swwy17R8eeV[\acdVefgh 
JJ2J3L'  	  	E	 :& 	E	s<   ,E 4$E 5&E E1EE E.-E.1FFlimit_totalc                 z   g }t               }d}d}d}d}||k  rbt        |      | k  rS|dz  }t        dd}|r||d<   	 t        j                  t
         dt        |d	
      }|j                  dk(  rt        j                  d       t|j                          |j                         }	|	j	                  dg       xs g }|sn|dk(  rt!        |	j	                  d      xs d      }|D ]<  }|j	                  d      }|r||v r|j#                  |       |j%                  |       > |d   }|j	                  d      xs g }|r|d   nd}|r||k(  rn,|}t        j                  d       ||k  rt        |      | k  rS|d|  |dS # t        $ r,}
t        j                  j                  d|
        Y d}
~
8d}
~
ww xY w)z
    Busca conversas via /conversations/search.
    Pagina ate atingir limit_total (max ~200 = 2 paginas) usando startAfterDate.
    Retorna dict com 'conversations' (lista) e 'total' (numero da API).
    r   Nr   ri   r   )r2   r   startAfterDatez/conversations/searchr   r4   r   zErro buscar conversas: conversationsr   r9   r   g?)r  r   )setr   r   rA   rB   rC   CONV_HEADERSr   r>   r   rE   rF   rG   rH   rI   rJ   r*  addrK   )r~  r  vistos	total_apicursorr   max_paginasr6   rM   r)   rO   
page_convsrh  r   	last_itemsort_arr
new_cursors                    rS   buscar_conversas_ghlr    s    !#MuFIFGK
K
C$6$D1 /#>'-F#$	*12$	A }}#

1 668D
 XXor28b
a<DHHW-23I 	$A%%+C#-JJsO  #	$ rN	==(.B$,Xa[$
Z61

3W K
C$6$DZ +<K89MM7  	JJ!8<=	s    AF 	 F 	F:"F55F:c                     | sy	 t        j                  t        |       dz  t              }|j	                  d      S # t
        $ r Y yw xY w)Nr   r`  )tz%d/%m %H:%M)r   fromtimestampr*  rt   rw   rG   )ts_msr|  s     rS   _fmt_data_msgr    sI    ##CJ$5%@{{=)) s   7= 	A	A	payloadc                 <   | j                  d      xs g }| j                  d      xs d}d}i }g }h d}|D ]  }|j                  d      xs |j                  d      xs dj                         j                         }||vrLt        |j                  d      xs d      }	|	dkD  r||	z  }|j                  |d      d	z   ||<   |j                  d
      xs |j                  d      xs d}
|j	                  t        |
xs d      |f        |j                  d        g }|d d D ]l  \  }}|j                  d      xs dj                         xs' |j                  d      xs dj                         xs d}|j                  d      xs dj                         }t        j                  dd|      }t        |      dkD  r|d d dz   }|j                  d      xs |j                  d      xs dj                         j                         }|j                  d      xs d}|r|nd}|j	                  |j                  d      xs d||j                  d      xs d|xs dt        |      ||t        |j                  d      xs d      ||d
       o ||||dS ) Nr  r   r   >   TYPE_SMS
TYPE_PHONETYPE_WHATSAPPTYPE_INSTAGRAMTYPE_FB_MESSENGERlastMessageTypetypeDESCONHECIDOunreadCountri   lastMessageDatedateUpdatedc                     | d    S r   r   r   s    rS   r   z#agregar_conversas.<locals>.<lambda>  s    AaD5 rX   r   r   r   r'   fullNamer<   lastMessageBodyz\s+r   P   M   z...
assignedTozSem atribuicaor9   r   z(sem mensagem))
conv_idr   r   
ultimo_msgr)   r   tipo	nao_lidas	atribuidoatribuido_id)r   r  por_tipoultimas)
rB   r   upperr*  rK   r   r  r  r   r  )r  convsr  r  r  ultimas_rawCANAIS_ACEITOSrh  r  unreadlast_msg_tsr  r  r   r   r  r  s                    rS   agregar_conversasr    s   KK(.BEG$)II!H*,KgN 7'(KAEE&MK^RRTZZ\ ~%QUU=).Q/A:I!dA.2ee-.K!%%2FK!C 0q11567  )G$ qUU=!'R..0 j!'R..0 	
 '(.B557vvfc4(t9r>9u$D'(KAEE&MK^RRTZZ\uu\*0b$0L6F	uuT{(bg,"2"2!%(QUU=16Q7"(
 	!< 	 rX   c                      d} t        j                          }| t        v rt        |    }||d   z
  t        k  r|d   S t        d      }t	        |      }||dt        | <   |S )N	conversasr*   r)   rC  )r~  r*   r)   )r>   r-   	_CONV_TTLr  r  )r  rL   r  r  r  s        rS   coletar_conversas_cachedr  =  sg    I
))+CKI&ty(= "s3G
G
$C$'5K	JrX   LAST_7_DAYSLAST_30_DAYS
THIS_MONTH
LAST_MONTHrs   rc   rf   rh   rl   re   rg   rk   rn   )r  r  r  r  c            	          t         Xt        t              5 } t        j                  |       }d d d        t        j                  d   |d   |d   |d   |d   dd      a t         S # 1 sw Y   :xY w)Ndeveloper_token	client_idclient_secretrefresh_tokencustomer_idT)r  r  r  r  login_customer_iduse_proto_plus)_gads_clientopen_GADS_TOKENS_PATHrF   loadr   load_from_dict)fr6  s     rS   _get_gads_clientr  ^  s}    #$ 			!A	&55 !23;//!"=!1"7
  	 	s   A%%A.c                    | dk(  r^|r\|rZd| d| d}	 t        j                  |      j                  d      }t        j                  |      j                  d      }| d| }n1t        j                  | d      }d| }t        j                  |d	      }d
| d}t        t              5 }	t        j                  |	      }
ddd       t               }|j                  d      }|j                  
d   |      }g }d}d}d}d}|D ]M  }|j                  j                  dz  }t!        |j                  j"                        }t!        |j                  j$                        }t'        |j                  j(                        }||z  }||z  }||z  }||z  }|dkD  r||z  nd}|j+                  |j,                  j.                  |j,                  j0                  j.                  t3        |      |t5        |      |t!        |      |dkD  rt3        |      nd|j                  j6                  dz  ddt3        |j                  j8                  dz        d
       P |dkD  r||z  nd}|dkD  r||z  dz  nd}|dkD  r||z  dz  nd}t3        |      t5        |      t5        |      t!        |      |dkD  rt3        |      nd|ddt3        |      d||dS # t        $ r | d| }Y Mw xY w# 1 sw Y   (xY w)z*Busca metricas de campanhas do Google Ads.ro   segments.date BETWEEN '' AND ''rq   rr   r  segments.date DURING rg   a  
        SELECT campaign.name, campaign.status,
               metrics.cost_micros, metrics.impressions, metrics.clicks,
               metrics.conversions, metrics.ctr, metrics.average_cpm,
               metrics.cost_per_conversion
        FROM campaign
        WHERE z
          AND campaign.status != 'REMOVED'
          AND metrics.cost_micros > 0
        ORDER BY metrics.cost_micros DESC
        LIMIT 20
    NGoogleAdsServicer  r  queryr  r   @B r   r   r.  r/  )
r   r   rY  rZ  r[  r\  
conversoesr^  rS  rT  r`  )ra  r[  r\  r  r^  rS  rT  )rb  rc  rE  )r   rv   rw   rG   GADS_PRESET_MAPrB   GADS_LABEL_MAPr  r  rF   r  r  get_servicesearchmetricscost_microsr*  rQ  rR  r#  conversionsrK   r9  r;   r   r(  r,  rS  average_cpm)rY   rZ   r[   date_clauserK  rL  r   periodr  r  r6  client
ga_serviceresponserc  total_gastototal_impressoestotal_cliques
total_convr{  rY  r[  r\  convr^  ri  rj  rk  s                               rS   fetch_google_adsr  n  s%   m
/gj\QRS	6''6??
KB''
3<<ZHBd#bTNE !$$V^<-fX6""6+<= m E 
	  AIIaL F##$67J  Q}-=U KHIKMJ '')3001
ckk(()S[[,,-uJ& d
 $qut|cLL%%ll))..e_":.d)$'!G8C=kkoo+C02CKK33i?@
 	4 /91nz)#I<Lq<P!11C7VYI;Ka;O//$6UXI $K0"#34.j/*3a-8I&S_A&I&
    	6$oS5E	6( s   AK *K&K#"K#&K0c           
         | dk(  r|r|r
d| d| d}nt         j                  | d      }d| }d| d}	 t        t              5 }t	        j
                  |      }d	d	d	       t               }|j                  d
      }	|	j                  d   |      }
i }|
D ]x  }|j                  j                  }||vrddd||<   ||   dxx   |j                  j                  dz  z  cc<   ||   dxx   t        |j                  j                        z  cc<   z g }t!        |j#                               D ]^  }	 t%        j&                  |      }|j)                  d      }|j+                  ||t-        ||   d   d      t/        ||   d         d       ` |S # 1 sw Y   8xY w# t        $ r g cY S w xY w# t        $ r |}Y dw xY w)z:Retorna serie diaria do Google Ads [{data, leads, gasto}].ro   r  r  r  r  r  z
        SELECT segments.date,
               metrics.cost_micros,
               metrics.conversions
        FROM campaign
        WHERE zf
          AND campaign.status != 'REMOVED'
        ORDER BY segments.date ASC
        LIMIT 1000
    Nr  r  r  r  )rY  r]  rY  r  r]  rv  r   )r)   rw  rY  r]  )r  rB   r  r  rF   r  r  r  r  rG   segmentsdater  r  r#  r  sortedkeysr   rv   rw   rK   ry  r*  )rY   rZ   r[   r  r  r  r  r6  r  r  r  por_diar{  r  rz  r|  r   s                    rS   fetch_google_ads_diarior    s   m
/gj\QRS $$V^<-fX6
 m 	E#$ 			!A	!#''(:;
$$=1A$O  "G >LLG#&5GAJ
7s{{66BB
7uS[[%<%<==> EGLLN# 	''*BKK(E 	71:g.2G,-	
 	 L;	 	
  	   	E	s;   F F9F 2&F/FF F,+F,/F=<F=c                     d|  d| d| }t        j                          }|t        v r&t        |   }||d   z
  t        k  r|d   |d   |d   fS t        | ||      }||d   |d   |d   dt        |<   |d   |d   |d   fS )z>Retorna (resumo, campanhas, periodo_label) com cache de 5 min.zgads|r  r*   rb  rc  rE  rn  )r>   r.   	_GADS_TTLr  )rY   rZ   r[   r  rL   r  r   s          rS   coletar_gads_cachedr    s    xqq=I
))+CKI&ty(?E+$6o8NNNV]J?E/;'/	K	 ?E+.o0FFFrX   c                      g } 	 t               } | t        j                  d      t        j                  dd      t	        j
                         j                  dS # t        $ r g } Y Ww xY w)Nuserroler'   )pipelines_menucurrent_usercurrent_rolecurrent_year)rT   rG   r   rB   r   rL   year)rN   s    rS   inject_globalsr    sb    E "  F+FB/ ++	   s   
A A&%A&c                  \    t        j                  d      dk(  rt        t        d            S y)zCRedireciona salesperson para /meus-leads. Retorna redirect ou None.r  salesperson
meus_leadsN)r   rB   r   r   r   rX   rS   _salesperson_guardr     s&    {{6m+-..rX   z/loginGETPOST)methodsc                  d   t         j                  dk(  rt         j                  j                  d      xs dj	                         j                         } t         j                  j                  d      xs dj	                         }t        | |      rdt        _        | t        d<   t        |       }|t        d<   t        |       t        d<   |d	k(  rt        t        d
            S t         j                  j                  d      xs t        d      }t        |      S t        dd       t        d      S )Nr  usernamer'   passwordTr  r  ghl_idr  followupnextindexzUsuario ou senha invalidos.errorz
login.html)r   methodformrB   r   r   r   r   	permanentr   r   r   r   argsr   r
   )r  r	  r  nxts       rS   loginr  *  s    ~~LL$$Z06B==?EEGLL$$Z06B==?x* $G&GFO *D"GFO / 9GH}$
 344,,""6*>gg.>CC= +W5<((rX   z/logoutc                  R    t        j                          t        t        d            S )Nr  )r   clearr   r   r   rX   rS   logoutr  =  s    MMOGG$%%rX   /c                      t               } | r| S t        j                  j                  dd      }t        j                  j                  dd      }t        j                  j                  dd      }t	        dd|||      S )	NrY   rf   inicior'   fimz
index.htmlhome)activerY   rZ   r[   r  r   r  rB   r
   guardrY   rZ   r[   s       rS   r  r  C  sr      E\\h5FLL$$Xr2M!!%,J# rX   z/api/overviewc                  V	  # t         j                  j                  dd      } t         j                  j                  dd      }t         j                  j                  dd      }d|  d| d| }t        j                         }|t        v r&t        |   }||d   z
  t
        k  rt        |d	         S d
| dddddddddddddd}	 t        | ||      \  }}}	t        | ||      }
g }|xs g d d D ]b  }|j                  |j                  d      xs dt        |j                  d      xs d      t        |j                  d      xs d      d       d |	|d<   d
||
|d|d<   	 t#        | ||      \  }}}t%        | ||      }|d   s||d<   d
||d|d<   	 t'               }ddddd d!}g }|j                  d"      xs i j)                         D ]2  \  }}|j                  |j                  ||      t        |      d#       4 |j+                  d$ %       d
|j                  d&d      |j                  d'd      |d(|d)<   	 dd l#d+t         d,t         f#fd-}t/               }|rd }|D ]  } ||d.         }d/|v sd0|v s|} n |s|d   }t1        |d1   | ||      \  }}}}} |g }!t3        |d2   j5                               xs d3}"|d2   j)                         D ]*  \  }}|!j                  ||t7        ||"z  d4z  d3      d5       , |!j+                  d6 %       |d   s| |d<   d
|d1   |d.   |d7   |d8   |d9   |d:   t7        |d;   d3      |d&   d<|!d d= d>|d?<   ndd@d|d?<   nddAd|d?<   ||dCt        |<   t        |      S # t        $ r>}t        j                  j                  d|        dt!        |      d|d<   Y d }~bd }~ww xY w# t        $ r>}t        j                  j                  d|        dt!        |      d|d<   Y d }~zd }~ww xY w# t        $ r>}t        j                  j                  d*|        dt!        |      d|d)<   Y d }~d }~ww xY w# t        $ r>}t        j                  j                  dB|        dt!        |      d|d?<   Y d }~7d }~ww xY w)DNrY   rf   r  r'   r  z	overview|r  r*   r)   TFokerro)r#  rY   rE  r   googler  pipeline   r   r   r]  r   rZ  )r   r]  rY  rE  )r#  rb  serie_diariatop_campanhasr   zoverview meta: )r#  rb  r(  r%  zoverview google: WhatsApp	Instagram	MessengerSMSTelefone)r  r  r  r  r  r  )canalqtdc                     | d    S Nr0  r   r   s    rS   r   zapi_overview.<locals>.<lambda>  s    ahY rX   r   r   r  )r#  r   r  	por_canalr  zoverview conversas: rR   r0   c                 |    dj                  fdj                  d| xs d      D              j                         S )Nr'   c              3   F   K   | ]  }j                  |      r|  y wr   	combining).0rh  _uds     rS   	<genexpr>z.api_overview.<locals>._norm.<locals>.<genexpr>  s!      qAQ   !!NFKDjoin	normalizer   )rR   r9  s    rS   _normzapi_overview.<locals>._norm  s7    77 ==b9 egrX   r;   padraopx3r9   r   ri   r   r   r0  pctc                     | d    S r2  r   r   s    rS   r   zapi_overview.<locals>.<lambda>  s    %y rX   r   r   r   r   r   r   r   r   r   r   r      )r#  r9   r   kpisr:   r&  z	Sem dadoszNenhuma pipelinezoverview pipeline: r  )r   r  rB   r>   r/   _OVERVIEW_TTLr   rq  r}  rK   r*  r#  rG   rH   rI   rJ   r   r  r  r  itemsr   unicodedatarT   r  sumvaluesry  )$rY   rZ   r[   r  rL   r  outresumo_metacampanhas_meta
label_meta
serie_metatop_camprh  rO   resumo_g_camp_glabel_gserie_gr  canais_labelr3  kr'  r@  rN   primeiroppr
  _dr  _ini_fimlabel_pstages_rowstotal_stager9  s$                                      @rS   api_overviewrb  U  sM    \\h5FLL$$Xr2M!!%,JF81]O1ZLAI
))+CO#	*t},5=)) b)+!2. "-C42Efm]g2h/^Z'zJ
 &B+ 	AOOf,QUU7^0q1quu[16Q7 	  *O!&%	
F6%8PZ%["'7)&-L?##*C #
H9') ()!,$
 	XXj)/R668 	ODAq|'7'71'=c!fMN	O./XXgq)+q1"	
K58!	S 	S 	
 !"H "V*%q=UaZ!H	
  8+9z,(BT4  !#&6"7">">"@AFQ 01779 DAq&&!" $Q_s%:A>(    %8 9?++2C("4.$V,!$W(+N(;'*='9$'
O%*3{+;Q%?!$W *"1o#J */"DJ%*4FGC
O
 ),S!9OI3<K  4

_QC01"CF3F4  6

.qc23$c!f5H62  9

1!56"'Q8K9p  8

045!&A7J8so   BN $1O B)P  9Q! :Q! ?C-Q! 	O
3OO
	P3PP	Q 3QQ!	R(*3R##R(z
/pipelinesc            
          t               } | r| S t               }|rt        t        d|d   d               S t	        ddd d dddd	      S )
Npipeline_detailr   r9   )rU   pipelines.htmlr8   r'   rs   )r  r&  r  rE  rY   rZ   r[   )r  rT   r   r   r
   )r   rN   s     rS   pipelines_listrf    s_      EE 1uQx~NOO	 	rX   z/pipelines/<pipeline_id>c           	      b   t               }|r|S t        |       }|s t        dd       t        t	        d            S t
        j                  j                  dd      }t
        j                  j                  dd      }t
        j                  j                  dd      }t        d	d
|d   ||||      S )NzPipeline nao encontrada.r  rf  rY   rs   r  r'   r  re  r8   r9   )r  active_pipeline_idr&  rY   rZ   r[   )	r  rW   r   r   r   r   r  rB   r
   )rU   r   r  rY   rZ   r[   s         rS   rd  rd    s      E$D('2 0122\\h7FLL$$Xr2M!!%,J:# rX   z/api/pipeline/<pipeline_id>c                    t         j                  j                  dd      }t         j                  j                  dd      }t         j                  j                  dd      }t        |       }|st	        ddd      d	fS 	 t        | |||      \  }}}}}	|j                  t              }|j                  t              }|d   j                         D cg c]?  \  }}||t        |t        t!        |d   j#                               d      z  dz  d      dA }}}t	        d|	|j%                  d      |j%                  d      |d   |d   |d   |d   t        |d   d      |d   d||d   d      S # t        $ rB}
t        j                  j                  d
       t	        dt        |
      d      dfcY d }
~
S d }
~
ww xY wc c}}w )NrY   rs   r  r'   r  FzPipeline nao encontradar"  i  zerro api_pipeline  r   ri   r   rC  Tr   r   r   r   r   r   r   rF  r   )r#  rE  r   r   rH  r:   r   )r   r  rB   rW   r   r  rG   rH   rI   	exceptionr   r{   rt   rJ  ry  maxrL  rM  rw   )rU   rY   rZ   r[   r  _dadosr  r   r   r   rO   r   r   rY  r'  r`  s                   rS   api_pipelinern    s    \\h7FLL$$Xr2M!!%,J$De-FGH#MM;2@
3
/Z% %%e,I&F ()//1Aq AeAC<L8M8T8T8V4WYZ0[,[^a,acd&efK 
 ''(89//"23\/}-Js;/3\
 h-    ;

01eSV45s::;s%   ?E1 AF?1	F<:7F71F<7F<z	/meta-adsc                      t               } | r| S t        j                  j                  dd      }t        j                  j                  dd      }t        j                  j                  dd      }t	        ddd|||	      S )
NrY   rc   r  r'   r  zmeta_ads.htmlmeta_adszMeta Adsr  section_titlerY   rZ   r[   r  r  s       rS   rp  rp  E  su      E\\h4FLL$$Xr2M!!%,J # rX   z/api/meta-adsc                     t         j                  j                  dd      } t         j                  j                  dd      }t         j                  j                  dd      }	 t        | ||      \  }}}t        d
|||d      S # t        $ rB}t
        j                  j                  d       t        dt        |      d      d	fcY d }~S d }~ww xY w)NrY   rc   r  r'   r  zerro api_meta_adsFr"  rj  Tr#  rE  rb  rc  )
r   r  rB   rq  rG   rH   rI   rk  r   r   rY   rZ   r[   rb  rc  rE  rO   s          rS   api_meta_adsrv  X  s     \\h4FLL$$Xr2M!!%,J;+>v}V`+a(	=
 &	  	  ;

01eSV45s::;   "B 	C7C	C	Cz/google-adsc                      t               } | r| S t        j                  j                  dd      }t        j                  j                  dd      }t        j                  j                  dd      }t	        ddd|||	      S )
NrY   rf   r  r'   r  zgoogle_ads.html
google_ads
Google Adsrq  r  r  s       rS   ry  ry  m  su      E\\h5FLL$$Xr2M!!%,J"# rX   z/api/google-adsc                     t         j                  j                  dd      } t         j                  j                  dd      }t         j                  j                  dd      }	 t        | ||      \  }}}t        d
|||d      S # t        $ rB}t
        j                  j                  d       t        dt        |      d      d	fcY d }~S d }~ww xY w)NrY   rf   r  r'   r  zerro api_google_adsFr"  rj  Trt  )
r   r  rB   r  rG   rH   rI   rk  r   r   ru  s          rS   api_google_adsr|    s     \\h5FLL$$Xr2M!!%,J;+>v}V`+a(	=
 &	  	  ;

23eSV45s::;rw  z
/conversasc                  :    t               } | r| S t        ddd      S )Nzconversas.htmlr  	Conversas)r  rr  )r  r
   )r   s    rS   r  r    s$      E+K{[[rX   z/api/conversasc                      	 t               } t        ddi|       S # t        $ rB}t        j                  j	                  d       t        dt        |      d      dfcY d }~S d }~ww xY w)Nzerro api_conversasFr"  rj  r#  T)r  rG   rH   rI   rk  r   r   )r)   rO   s     rS   api_conversasr    sd    ;') D$'$'((  ;

12eSV45s::;s   
 	A&7A!A&!A&z!/api/conversa/<conv_id>/mensagensc           	         t          d|  d}	 t        j                  |t        ddid      }|j                  dk7  rt        d|j                  d d d	      d
fS |j                         j                  di       j                  dg       xs g }d }|j                  |       t        j                  dt        j                         }g }|D ]5  }|j                  dd      xs |j                  dd      }	|j                  dd      }
|j                  dd      xs |j                  dd      xs d}|j#                         swd}|j%                  |      }|r@|j'                  d      j#                         }||j)                         d  j#                         }|j                  d      xs d}t+        j,                  |      }|r%|j/                  t0              j3                  d      }nd}|j5                  |
||||	d       8 t        d|d      S # t        $ rB}t
        j                  j                  d       t        dt        |      d	      d
fcY d }~S d }~ww xY w)Nz/conversations/z	/messagesr   r   r3   r4   zerro api_conversa_mensagensFr"  rj  rC  messagesc                 ~    | j                  d      xs d}t        j                  |      }|r|j                         S dS )Nr   r'   r   )rB   ry   r   r  )mr  r|  s      rS   _tsz#api_conversa_mensagens.<locals>._ts  s8    EE+$"q!!#r||~**rX   r   z^\[([^\]]+)\]:\s*messageTyper'   r  	directionr   rJ  ri   r   r  )direcaocorpo	remetenter)   r  T)r#  	mensagens)rC   rA   rB   r  rG   rH   rI   rk  r   r   r   rJ  rF   r   r  compileUNICODEr   matchgroupendry   r   r{   rt   rw   rK   )r  urlrM   rO   msgsr  
_PREFIX_RE	resultador  r  r  r  r  m_prefixd_strdt_utcdata_fmts                    rS   api_conversa_mensagensr    s-    JogYi
8C;LLlGS>SUV
 	}}eQVVDS\:;S@@668<<
B'++J;ArD+
 	II#I 0"**=JI uu]B'<155+<%%R(fb!<QUU62%6<"{{}	##E* q)//1I(,,./*002Ek"(b""5)((/88GHH"
 	%4 $Y788]  ;

:;eSV45s::;s   H 	I7IIIz/rastreamentoc            	      	  % t               } | r| S t        j                  j                  dd      }t        j                  j                  dd      }t        j                  j                  dd      }t	        j
                         }t	        j                  |      }t	        j                         }t	        j                  |      }dg ddd}	 dd l	%dt        d	t        f%fd
}	t               }
d }|
D ]  } |	|d         }d|v sd|v s|} n |s|
r|
d   }|rt        |d         }i }|D ]y  }|j                  dd      }|d   j                  |d      }|j                  d      xs dj                         }|dk(  r|dz  }n
|dk(  r|dz  }|j                  |d      dz   ||<   { t        |j                               }|j!                         D cg c]  \  }}|||r||z  dz  ndd }}}|j#                  d        |d   ||dd}i g g i i dd}i i i dd}d}	 t	        j,                  t.        t0              }d!}d!}	 t3        |||      \  }}}t5        |||      } | j                  d"g       xs g D ]$  }!	 |t7        |!j                  d#      xs d      z  }& 	 	 t;        |||      }"|"j                  d%g       xs g D ]$  }!	 |t7        |!j                  d&      xs d      z  }& |s|"j                  d'd      }	 t	        j<                  |||      }d|d<   d)d*d+d,d-d.}#d/d0d1d2d3d.}$t?        	 dGi d4d5d6d7d8|d9|d:|d;|d<|d=|#d>|$d?|d@|dA|xs dBd|dC|dD|dEt        j@                  jC                  dF      S c c}}w # t$        $ r8}t&        j(                  j+                  d       t        |      |d<   Y d }~d }~ww xY w# t$        $ r<}t&        j(                  j+                  d        i g g i i t        |      d}Y d }~d }~ww xY w# t$        $ r Y w xY w# t$        $ r-}t&        j(                  j9                  d$|        Y d }~d }~ww xY w# t$        $ r Y w xY w# t$        $ r-}t&        j(                  j9                  d(|        Y d }~d }~ww xY w# t$        $ r}i i i t        |      d}Y d }~d }~ww xY w)HNrY   rf   r  r'   r  r   )pipeline_nomer:   r   r$  rR   r0   c                 |    dj                  fdj                  d| xs d      D              j                         S )Nr'   c              3   F   K   | ]  }j                  |      r|  y wr   r6  )r8  rh  rK  s     rS   r:  z.rastreamento.<locals>._norm.<locals>.<genexpr>  s$      I^I^_`Iar;  r<  r=  )rR   rK  s    rS   r@  zrastreamento.<locals>._norm  s9    77 &00bA egrX   r;   rA  rB  r9   r   r=   r   r   r   r   r   r   ri   r   rC  c                     | d    S r2  r   r   s    rS   r   zrastreamento.<locals>.<lambda>  s    AeH9 rX   r   zerro funil rastreamentor$  )r3  canaisr(  r   tempos_globaisr$  )r   r%  r   r$  zerro atribuicao rastreamentor  rD  rP  znao pegou gasto Meta: rc  rZ  rE  znao pegou gasto Google Ads: zMeta (Facebook/Instagram)rz  r*  zCRM / ManualzDireto / Organico)r   r%  whatsappcrmdiretoz#4267B2z#EA4335z#25D366z#8B5CF6z#94A3B8r  rastreamentorr  Rastreamento
agg_clicksagg_pvsfunil
atribuicaocpa_realcanal_label_mapcanal_cores
gasto_metagasto_googleperiodo_label_adsr   rZ   r[   base_urlr  )zrastreamento.html)"r  r   r  rB   trackingcarregar_clicksagregar_clickscarregar_pageviewsagregar_pageviewsrK  r   rT   r   r   rL  rM  rJ  r   rG   rH   rI   rk  coletar_atribuicao_cachedrD   r   rq  rN  r#  rJ   r  calcular_cpa_realr
   host_urlrstrip)&r   rY   rZ   r[   rR  r  	pageviewsr  r  r@  rN   alvorP   r
  r   stage_countr   sidsnamer   total_oprY  r'  r`  rO   r  r  r  r  r  rO  _crp  rh  gadsr  r  rK  s&                                        @rS   r  r    s     E \\h5FLL$$Xr2M!!%,J %%'F((0J++-I((3G !BBGE-	S 	S 	
 !" 	Aai A1}!		
 8D'T
3D*,K Cff.3\*..sK@&&*0b779V#\)Eu_Z'E%0__UA%>%BE"C ;--/0H (--/Aq Ahq8|c/ATUVK  !45!%f%!	E  "RRAJb2rBHI77Q
 JL9-@Xb-c*R* (zJor28b 	AeAEE'N$7a88
	
?zB+r*0b 	AaeeK&8&=A >>	
 ! $" =K--j*lS ,%O K  % 	
     (    " ,2s  $  !" !!((-# ]  

67Af  I

;<#%R!RQI
I   9

3A37889    ?

9!=>>?  K"r3q6JKs   
6N	 N	 C
N	 N* N	 O  7P% 8!PP% &Q. !Q%Q. <R' N	 		O
-OO
	P1PP	P"P% !P""P% %	Q."QQ	Q+'Q. *Q++Q. .	R$7"RR$'	S0SSz/api/rastreamento/atribuicaoc                  0   t               } | rt        ddi      dfS t        j                  j	                  d      dk(  }	 t        j                  t        t        |      }t        |      S # t        $ r"}t        dt        |      i      dfcY d}~S d}~ww xY w)	z=Endpoint JSON pro front consumir/atualizar (cache de 30 min).r  	forbiddeni  refresh1)forcerj  N)r  r   r   r  rB   r  r  rD   r   rG   r   )r   r  r)   rO   s       rS   api_rastreamento_atribuicaor  x  s      E-.33LLY'3.E/11'?RWXt} /Q()3../s   *A* *	B3B
BBz=/opt/mia/workspace/clientes/px3lab/followup_notif/followup.dbzNutricao Cloud PhotoRFzIniciou Fluxo de Compra Cloud)nutricao_cloud_photorfiniciou_fluxo_compra_cloud)
ri      $   0   x      h  X      )	r  r  r  r  r  r  r  r  r  MoniqueMaryViniciusNaiane)FKCKhrtdwZjHS6ISgAFFn2TXqcFucc2mSfK5p0Do3egsMwQ3G5rHFHVhp5550ZFCVED2VANUkXjfB4tLr&  c                 N   | syt        |       j                         }|sy|j                  dd      }|j                  d      r|dd dz   }	 t	        j
                  |      }|j                  |j                  t              }|j                  t              S # t        $ r Y yw xY w)zGParse timestamps salvos pelo followup_notif (isoformat, com ou sem tz).Nr   TZr  z+00:00)rp   )
r   r   ru   endswithr   rv   r%  rp   r|   r{   )r&  rR   r|  s      rS   _parse_dt_utcr    s    E
A			#sAzz#crFX##A& 
yyZZvZ&==  	  s   B 	B$#B$hoursc                     t        t        t        | dz              d      }t        |d      \  }}|dk\  r|dz  }|dz  }|r| d| dS | dS | d|d	d
S )N<   r   r  r  d h diaszh 02dr  )rl  r*  ry  divmod)r  	total_minr  r  r  rem_hs         rS   _format_elapsedr    s|    Cebj)*A.I)R DAqBwGBS5'##E{S1S'rX   c                     | y	 t        |       }|dk\  rt        |d      \  }}|r| d| dS | dS | dS # t        t        f$ r Y yw xY w)Nr   r  r  r  r  r  )r*  r$  r%  r  )delay_hr  r  rems       rS   _format_delayr    su    L 	Bw23S3%q>!E{S7N z" s   8 A
	A
	elapsed_hc                     | dk\  ry| dk\  ryy)Nr  redr  yellowgreenr   )r  s    rS   	_semaforor    s    BBrX   z	/followupc            	      H    t        dddt        j                  dd            S )Nzfollowup.htmlr  z	Follow-upr  r  )r  rr  	user_role)r
   r   rB   r   rX   rS   r  r    s(     !++fm4	 rX   z/api/followupc                     t        j                  dd      } t        j                  d      }g }	 t        j                  t        d      }t        j
                  |_        	 |j                  d      j                         }|j                  d      j                         }|j                          	 i }|D ]<  }|d   }	|j%                  |	t'                     j)                  t+        |d                > t-        j.                  t0              }
|D ]Y  }|d   xs d}| dk(  r|r||k7  r|d   xs d}t3        |d         }|s4|
|z
  j5                         dz  }|dk  rd}t6        j                  |g       }|j                  |d   t'                     }|D cg c]	  }||vs| }}|rt9        |      nd }|rt;        |      nd }|j=                  |d   |d   xs d|d   xs d|t>        j                  ||      |jA                         tC        |d      tE        |      tG        |      ||tI        |      nd||tI        |      nd|| dk(  rtJ        j                  |      nd d       \ |jM                  d         t!        d!|t+        tO        jN                               | d"      S # |j                          w xY w# t        j                  $ r1}t        j                  j                  d|        g g }}Y d }~Md }~wt        $ rB}t        j                  j                  d	       t!        d
t#        |      d      dfcY d }~S d }~ww xY wc c}w )#Nr  r  r
        .@r7   
                SELECT id, contact_id, contact_name, contact_phone,
                       assigned_user_id, tag, tag_added_at
                  FROM events
                4SELECT event_id, delay_hours FROM notifications_sentzapi_followup DB indisponivel: zerro api_followupFr"  rj  event_iddelay_hoursassigned_user_idr'   tagtag_added_at      @r   r  r9   contact_namer<   contact_phoner   	Concluidor   manager)r9   r  r  r  	tag_labelr   elapsed_hourselapsed_labelsemaforoproxima_notif_hproxima_notif_labelultima_notif_hultima_notif_labelr  assigned_namec                     | d    S Nr  r   rM   s    rS   r   zapi_followup.<locals>.<lambda>0      ?!3 3 rX   r   T)r#  rowsr*   r  )(r   rB   _sqlite3connectFOLLOWUP_DBRowrow_factoryexecutefetchallcloseOperationalErrorrH   rI   rJ   rG   rk  r   r   
setdefaultr  r  r*  r   rL   r|   r  total_secondsFOLLOWUP_DELAYSminrl  rK   FOLLOWUP_TAG_LABELS	isoformatry  r  r  r  GHL_USER_NAMESr   r>   )r  r
  rows_outconnevents
notif_rowsrO   sent_by_eventnreidnow_utcevassignedr  	tag_addedr  delaysenviadosr  	pendentesproximaultimas                         rS   api_followupr4    s-    ;;v}-D[["FH;T:#<<	\\ hj  Fhj  JJL M In  ce,00R5F1GHI ll6"G &()/R= X/io2!"^"45	 9,;;=F1M $$S"- $$RXsu5 &<1!8*;Q<	<$-#i.4"*XT(~.<*06B,00c:%//1"=!4,];!-0&=D=P=#9Va$;A;M-"7SV (>Bi>On00:UY
 	-&P MM3M4$))+	  w JJL$$ $

;A3?@
 ;

01eSV45s::;< =sM   0J=  >J( J= 	MM(J::J= =M
&K<<M
7M?M
M
z/meus-leadsc            
          t        j                  d      dk(  rt        t        d            S t	        dddt        j                  dd      t        j                  dd	      
      S )Nr  r  r  zmeus_leads.htmlr  z
Meus Leadsr  r  r'   )r  rr  r  r  )r   rB   r   r   r
   r   rX   rS   r  r  :  sW     {{6i'
+,,"++fm4VR( rX   z/api/meus-leadsc                     t        j                  dd      } t        j                  d      }g }	 t        j                  t        d      }t        j
                  |_        	 |j                  d      j                         }|j                  d      j                         }|j                          	 i }|D ]<  }|d   }	|j%                  |	t'                     j)                  t+        |d                > t-        j.                  t0              }
|D ]8  }|d   xs d}|r||k7  r|d   xs d}t3        |d         }|s/|
|z
  j5                         dz  }|dk  rd}t6        j                  |g       }|j                  |d   t'                     }|D cg c]	  }||vs| }}|rt9        |      nd	}|rt;        |      nd	}|j=                  |d   |d   xs d|d   xs d|t>        j                  ||      |jA                         tC        |d      tE        |      tG        |      ||tI        |      nd||tI        |      ndd       ; |jK                  d        tM        |      tO        d  |D              tO        d! |D              tO        d" |D              d#}t!        d$| ||t+        tQ        jP                               d%      S # |j                          w xY w# t        j                  $ r1}t        j                  j                  d|        g g }}Y d	}~md	}~wt        $ rB}t        j                  j                  d
       t!        dt#        |      d      dfcY d	}~S d	}~ww xY wc c}w )&zBSempre filtra pelo ghl_id do usuario logado, independente do role.r  r'   r
  r  r  r  r  z api_meus_leads DB indisponivel: Nzerro api_meus_leadsFr"  rj  r  r  r  r  r   r  r   r  r9   r  r<   r  r   r  r   )r9   r  r  r  r  r   r  r  r	  r
  r  r  r  c                     | d    S r  r   r  s    rS   r   z api_meus_leads.<locals>.<lambda>  r  rX   r   c              3   2   K   | ]  }|d    dk(  sd  yw)r	  r  ri   Nr   r8  rM   s     rS   r:  z!api_meus_leads.<locals>.<genexpr>  s     DAQz]e-CaD   c              3   2   K   | ]  }|d    dk(  sd  yw)r	  r  ri   Nr   r9  s     rS   r:  z!api_meus_leads.<locals>.<genexpr>  s     GAQz]h-FaGr:  c              3   2   K   | ]  }|d    dk(  sd  yw)r	  r  ri   Nr   r9  s     rS   r:  z!api_meus_leads.<locals>.<genexpr>  s     FAQz]g-EaFr:  )r   r  r  r  T)r#  vendedorr  rb  r*   ))r   rB   r  r  r  r  r  r  r  r  r  rH   rI   rJ   rG   rk  r   r   r  r  r  r*  r   rL   r|   r  r  r  r   rl  rK   r!  r"  ry  r  r  r  r   r   rL  r>   )r  r
  r$  r%  r&  r'  rO   r(  r)  r*  r+  r,  r-  r  r.  r  r/  r0  r  r1  r2  r3  rb  s                          rS   api_meus_leadsr>  I  sI    {{62&H[["FH;T:#<<	\\ hj  Fhj  JJL M In  ce,00R5F1GHI ll6"G #()/RV+io2!"^"45	 9,;;=F1M $$S"- $$RXsu5 &<1!8*;Q<	<$-#i.4"*XT(~.<*06B,00c:%//1"=!4,];!-0&=D=P=#9Va$;A;M-"7SV
 	+#J MM3M4 XDDDGGGFFF	F $))+  { JJL$$ $

=aSAB
 ;

23eSV45s::;8 =sM   0K  >K K 	M-M-KK M*0&LM*(7M%M*%M*z/healthzc                  T    t        dt        t        j                               d      S )NT)r#  r*   )r   r*  r>   r   rX   rS   healthzr@    s    $c$))+&6788rX   c                     	 t                g d} | D ]  }	 t	        |dd       	 t        |dd         	 t               }|D ]  }dD ]  }	 t        |d   |dd         y# t        $ r t        j                          Y rw xY w# t        $ r t        j                          Y ~w xY w# t        $ r t        j                          Y w xY w# t        $ r t        j                          Y w xY w# t        $ r t        j                          Y yw xY w)zBRoda em background a cada 4 min para manter caches sempre quentes.r  r'   )rs   rf   rh   r9   N)r  rG   	traceback	print_excrq  r  rT   r  )presetsrY   rN   rP   s       rS   _warm_all_cachesrE    s    " UG "	"B/	"B/"	 " 	*AD **"1T7FB;*	*#    	"!	"  	"!	" ! *'')* sh   
A BB%C+ 	CC+ A?>A?B"!B"%CCC(%C+ 'C((C+ +D
Dc                      d } t        j                  | dd      }|j                          t        j                  t        dd      j                          y )Nc                      	 t        j                  d       	 t                !# t        $ r t	        j
                          Y  w xY w)Nr  )r>   r   rE  rG   rB  rC  r   rX   rS   _loopz"_start_cache_warmer.<locals>._loop  s=    JJsO& "   &##%&s   
# AATzcache-warmer-loop)targetdaemonr;   zcache-warmer-initial)	threadingThreadstartrE  )rH  r6  s     rS   _start_cache_warmerrN    sD    & 	d9LMAGGI,T@VW]]_rX   __main__PORTi"  z0.0.0.0F)hostportdebug)rC  )rf   r'   r'   )__doc__ossysr>   htmlrF   r  rK  rB  concurrent.futuresr   r   r   r   pathlibr   zoneinfor   rA   flaskr	   r
   r   r   r   r   r   r   google.ads.googleads.clientr   __file__resolveparent
SCRIPT_DIRPX3_DIRpathinsertr   
config_ghlr   r   gerar_relatoriory   authr   r   r   r   r   sqlite3r  r  __name__rH   environrB   
secret_keyconfigregister_blueprintbprC   rD   r  META_ACCOUNT_IDrI  	read_textr   rF  rG   rt   r|   r?   r@   r+   dict__annotations__r  r,   ro  r-   r  r  r  r.   r  r/   rI  r   rT   rW   rx   r   r   r  r  rH  r(  r,  r0  r*  r7  rN  rl  rq  r}  r  r  r  r  r  r  r  r  r  r  context_processorr  r  router  r  r  rb  rf  rd  rn  rp  rv  ry  r|  r  r  r  r  r  r  r!  r  r#  r  r#  r  r  r  r  r4  r  r>  r@  rE  rN  rR  runr   rX   rS   <module>ru     s   
 
    	   ? (   	 	 	 8 (^##%,,



 3w<   3w!445 6 1    
 Ho 24TU$-

  !+4"+=

' ((-

$ %   x{{ #1yk* &	 yk*  (4_4EYO78BBDJJLK 	$%	% !* 	 T 	 T 	 T 	 AB T 	  '$t* 'Tc dTk )9S )9t )9t )9X(c (d4j (V, ,T ,^mD md mD m`2 2S 2 2RU 2D s &s &s s 'Q 'QS 'Qc 'Qd 'QTJ6 J6 J6ZF FC FS F&.s .3 .C .DQUJ .h:Nc :ND :NzC @t @ @F$ "  $%	 SS S SVY Scg Sl1C 1 1 1QUVZQ[ 1hG GC GS G,  " 8eV_-) .)$ 9& &
 3    ?N  Nb <  ( %&  '0 ()(  *(V ;  " ?  & =  "   & <\  \ )  ) ./29  029j ?S  Sl )*
/  +
/  N 7"A  M"M &"&$	! !(	5 	S 	c  3  ;   ?U  Up =
  
 Y  Yx :9 9>`   zrzz~~fd+,DGGUG3 [<  Ks   	$V; ;WW