
    B j                     8   d dl mZmZ d dlZd dlmZmZmZ d dlmZm	Z	m
Z
mZ  ej                  e      Zd dlmZ d dlmZ d dlmZ d dlmZ d d	lmZmZ d d
lmZ d dlmZ d dlmZ d dl m!Z! d dl"m#Z# d Z$ ejJ                  ddg      d        Z&d Z'ddZ(d Z$y)    )requestjsonifyN)funcdescasc)datetimedate	timedeltatimezone
joinedload)api_bp)SessionLocalAd)MlOrderMlOrderItemMlMetricsDaily)ForecastLog)MeliApiService)
SyncEngine)STOCK_RISK_WARNING_DAYSc                    ddl m} ddlm} | j	                  |      j                   ||j                              }|r|j                  |j                  |k\        }|r|j                  |j                  |k        }|j                         S )z,Helper to fetch orders in a specific period.r   r   r   )
app.models.ml_orderr   sqlalchemy.ormr   queryoptionsitemsfilterdate_createdall)db
start_dateend_dater   r   r   s         I/var/www/hypershopcomercio.com.br/hyper-ai/app/api/endpoints/dashboard.pyget_orders_in_periodr(      sn    +)HHW%%j&?@EW11Z?@W11H<=99;    z/dashboard/metricsGET)methodsc                  6   t               } 	 t        j                  j                  dd      }t        j                  d|        t        t        d            }t        j                  |      }|j                  dddd      }d }d }d }d }d}	d}
|r't        |      j                         j                         nd}|d	k(  s
|d
k(  s|dk(  r*|}|t        ddd      z   }|t        d      z
  }|}d}	d}
n|dk(  s
|dk(  s|dk(  r5|t        d      z
  }|}|t        d      z
  }|t        d      z
  }d}	d}
n|dk(  r5|t        d      z
  }|}|t        d      z
  }|t        d      z
  }d}	d}
no|dk(  r5|t        d      z
  }|}|t        d      z
  }|t        d      z
  }d}	d}
n5|dk(  s|dk(  r^|j                  d       }|}|j                  d       }|t        d      z
  }|j                  d       }|}d!}	||z
  j                  dz   }
n|d"k(  s|d#k(  rl|j                  d       }|t        d      z
  }|j                  d       }|}|}|t        d      z
  }|j                  d       }|}d$}	||z
  j                  }
nW|j                         rt!        |      nd}|}
|t        |      z
  }|}|t        |dz        z
  }|t        |      z
  }d%| d&}	d' } ||      } ||      } ||      } ||      }t        j                  d(| d)|        | j#                  t$              j'                  t$        j(                  d*k(        j+                         }	 d}| j#                  t-        j.                  t0        j2                              j'                  t0        j4                  |j5                         k\        }|r|j5                         |j5                         k(  }d+}|d,v r|rd-}|r1|j'                  t0        j4                  |j5                         k        }n0|j'                  t0        j4                  |j5                         k        }|j7                         }|t!        |      nd}	 | j#                  t-        j.                  t0        j2                              j'                  t0        j4                  |j5                         k\        }|r0|j'                  t0        j4                  |j5                         k        }|j7                         }|t!        |      nd}d/} |dkD  r't=        |      t=        |      z
  t=        |      z  d0z  } n|dkD  rd1} | j#                  t>              jA                  tC        t>        jD                              }!|r"|!j'                  t>        jF                  |k\        }!|r"|!j'                  t>        jF                  |k        }!|!jI                  t>        jF                  jK                               }!|!jM                         }"	 tO        d2d3      5 }#t        j                         jQ                  d4      }$|#jS                  d5|$ d6| d7       |#jS                  d8| d9| d7       |#jS                  d:tU        |"       d7       d d d        d/}%d/}&d}'g }(h d<})|"D ]"  }*|*jV                  |)v r|dk(  r|*jV                  d=k(  r(t=        |*jX                  xs d      }+|*j(                  d>k(  r|*jZ                  rd?|*jZ                  v r	 d@dAh},|dk(  r*|*jV                  |,v r|%|+z  }%|'dz  }'|(j]                  |*       |*j(                  d>k(  r|%|+z  }%|&|+z  }&|'dz  }'|*j(                  dBk(  r|%|+z  }%|'dz  }'|(j]                  |*       |*j(                  dCk(  s|*j(                  dDk(  s|%|+z  }%|'dz  }'|(j]                  |*       % t=        |%      t=        |&      z
  }-| j#                  t>              j'                  t>        j^                  |k\        }.|r"|.j'                  t>        j^                  |k        }.|.jM                         }/d/}0d/}1d}2|/D ]w  }*t=        |*jX                  xs d      }+|*j(                  d>k(  s.|*j(                  dBk(  s|*j(                  dCk(  s|*j(                  dDk(  sY|0|+z  }0|2dz  }2|*j(                  d>k(  ss|1|+z  }1y t=        |0      t=        |1      z
  }3d/}4t=        |3      dkD  r&t=        |-      t=        |3      z
  t=        |3      z  d0z  }4d/}5|dkD  r|'|z  d0z  }5i }6i }7|(D ]  }*|*jD                  D ]  }8t=        |8j`                  xs d      |8jb                  xs dz  }9|6j                  |8jd                  d/      |9z   |6|8jd                  <   |7j                  |8jd                  d      |8jb                  xs dz   |7|8jd                  <     tg        |6jE                         dE d-F      d dG }:g };|%dkD  r|%ndH}<|:D =>cg c]  \  }=}>|=	 }?}=}>| j#                  t$              j'                  t$        jh                  jk                  |?            jM                         D @ci c]  }@|@jh                  |@ }A}@|:D ]  \  }=}9Aj                  |=      }@|9|<z  d0z  }B|;j]                  |=|@r@jl                  n|=@r@jn                  nd |9|7j                  |=d      @rt!        @jp                  xs d      nd@rt=        @jr                  xs d      nd/tu        Bd      dI        g }C|
dk\  r|
nd}D| j#                  tv        jd                  t-        j.                  tv        jb                              jy                  t>              j'                  t>        jF                  |k\        j{                  tv        jd                        jM                         }E|ED FGci c]  \  }F}G|F|Gt!        G      nd }H}F}G| j#                  t$              j'                  t$        j(                  d*k(        jM                         }Id}Jd/}Kg }L|ID ]  }@Hj                  |@jh                  d      }M|MDz  }N|@jp                  xs d}OdJ}PNdkD  rONz  }PPt|        k  sJJdz  }Jt=        @jr                  xs d      Oz  }QK|Qz  }KLj]                  @jh                  |@jl                  |@jn                  tu        Pd      t=        |@jr                  xs d      dK        tg        LdL M      d dG }Rt        j                  dN       t        | |j5                         |j5                         |      }St        j                  dO       t        | |j5                         ||j5                         ||'P      }Tg }UdQ}Vt               }W|"D ]\  }*|*j(                  dBk(  s|*j(                  dCk(  s|*j(                  dDk(  s1|*jD                  D ]  }8Wj                  |8jd                          ^ g }UddRlCmD}X i }Y	 | j#                  X      jM                         D Zci c]8  }Z|Zj                  sZj                  j                         j                         |Z: }Y}Zi }[	 | j#                  t$              jM                         D \ci c]  }\|\jh                  |\ }[}\dQ}Vd/}]d/}^d/}_d}`d}ad}bi }ct               }d|"D ]t  }*|*j(                  d>k7  s|*jD                  D ]S  }ecj                  |ejd                  d      |ejb                  z   |c|ejd                  <   dj                  |ejd                         U v i }fd/}gd/}hdr	 t        |       }i|jQ                  dS      }j|jQ                  dS      }kdT|j dU|k }lddVlHmI}m dd lJ}ng }o| j#                  |m      j                  |lM      j                         }p|dWv rdXndY}qprtpj                  rh	 nj                  pj                        }r|rj                  dZd      }st        j                         j                         |sz
  qk  rrj                  d[g       }oos}ij                  d jkd-\      }o|orfps mld]^      }p| j                  |p       nj                  t        j                         j                         od_      p_M        | j                          oroD ]  }tt        |tt              st=        tj                  d`      xs d      }ut=        tj                  da      xs d      }vtj                  db      }wguz  }gh|vz  }h|wsnwcv sscw   }M|MdkD  s~vMz  fw<    |"D ]2  }*|*j(                  d>k(  }x|xrbdz  }bnadz  }at=        |*jX                  xs d      }yd/}zd/}{d/}|d/}}d/}~d/}d}|*jD                  D ]B  }8|8jb                  z  }t!        |8jb                  xs d      }Gt=        |8j`                  xs d      }d/}[j                  |8jd                        }@|@rt=        @j                  xs d      nd}|8j                  r(|8j                  j                         j                         nd}dkD  rGz  }nd/}r Yv rt=        Y   j                  xs d      }d/k(  rrj                  dd      }tU        |      dk\  rhddjy                  d de       }YjE                         D ]A  \  }}|j                        sj                  xs ddkD  s,t=        j                        } n Gz  }t=        |8j                  xs d      Gz  }|Gz  Vz  }d/}t=        |*j                  xs d      }|dkD  r#t        dtU        |*jD                              z  }n*@r(@j                  rt=        @j                  xs d      Gz  }d/}|8jd                  fv rf|8jd                     Gz  }{z  }{|z  }|}z  }}~z  }~z  }E {|z   }z   ~z   z   }zt=        y      t=        |z      z
  }t=        |y      dkD  rt=              t=        y      z  d0z  nd}xs]z  }]_z  }_`dz  }`d }df}d }|*jD                  rl|*jD                  d   }|jl                  }|j                  rj                  j                         nd }[j                  jd                        }|rjn                  }Uj]                  i dg|*jV                  dh|*j                  di|*jV                  dj|*jF                  r|*jF                  j                         nd dk|*j                  xs d dl|*j                  xs d j                         xs |*j                  xs dmdnydo|ydpdqdrds|*jD                  r|*jD                  d   jd                  nd dtdu|*j(                  dvdwdxzdy{|}~dzd{|*jD                  D ecg c]  }e|ejl                  |ej                  rej                  j                         nd ejd                  |ejb                  t=        |ej`                  xs d      [j                  ejd                        r%[j                  ejd                        jn                  nd d| c}ei       5 `dkD  r_`z  }^d/}	 t        | ||      }/d/}|/D ]<  }*d+}|*jV                  |)v rd-}|dk(  r|*jV                  d=k(  rd-}|dk(  xr |*jV                  ,v }rDd+}rd-}n |*j(                  d>k(  rn|*j(                  d}v rd-}snt=        |*jX                  xs d      }d/}|*jD                  D ]  }et!        |ejb                  xs d      }Gt=        ej`                  xs d      }[j                  ejd                        }@|@rt=        @j                  xs d      nd/}|dk(  rXej                  r(ej                  j                         j                         nd}|r Yv rt=        Y   j                  xs d      }Gz  }|Gz  Vz  }t=        ej                  xs d      Gz  }d/}t=        |*j                  xs d      }|dkD  r#t        dtU        |*jD                              z  }n*@r(@j                  rt=        @j                  xs d      Gz  }d/}|%dkD  rgdkD  rh|%z  }Gz  |z  }z   z   z   z   z  } t=              t=              z
  z  }? dkD  r t=        ]      t=              z
  }||z  d0z  }n]dkD  rd1}t        d/tu        t=        |-      t=        g      z
  d            }t        i d|d|dtu        | d      dtu        |-d      dtu        |%d      dtu        |&d      dtu        |4d      dadbd|'dtu        |gd      d|d]dtu        d      dtu        hd      d^d|	Jtu        Kd      |J|;RSTU|j                         |r|j                         nd |	dd	      | j                          S # t8        $ r}t;        d.|        d}Y d }~d }~ww xY w#  d}Y xY w# 1 sw Y   xY w# t8        $ r}t;        d;|        Y d }~d }~ww xY wc c}>}=w c c}@w c c}G}Fw c c}Zw #  Y xY wc c}\w #  Y xY w#  g }oY 
exY w# t8        $ r}t;        dc|        Y d }~	zd }~ww xY wc c}ew # t8        $ r}t;        d~|        d/}Y d }~d }~ww xY w# t8        $ rH}dd ld}|j                          t        d+t        |      g d      dfcY d }~| j                          S d }~ww xY w# | j                          w xY w)Ndays7z2[DEBUG_DASHBOARD] Starting metrics fetch for days=hoursr   )hourminutesecondmicrosecond 1hojetoday   ;   )r1   minutesseconds   r-   Hoje0	yesterdayontem   Ontem      u   Últimos 7 dias30   <   u   Últimos 30 diascurrent_month	mes_atual)dayu
   Mês Atual
last_monthmes_passadou   Mês Passadou	   Últimos z diasc                 F    | sy | j                  d       t        d      z   S )Ntzinfo   r0   )replacer
   )dts    r'   to_utc_naivez+get_dashboard_metrics.<locals>.to_utc_naive}   s$    d ::T:*YQ-???r)   z"[DEBUG_DASHBOARD] UTC Boundaries:  to activeF)r7   r.   rK   Tz'[DASHBOARD_DEBUG] Visits Query Failed:         d   g      Y@zdebug_log.txtaz%Y-%m-%d %H:%M:%Sz	
[DEBUG] z - Dashboard Access - Period: 
z[DEBUG] StartUTC: z
, EndUTC: z[DEBUG] Orders Found: zLogging failed: >   2000014149744094200001429395416420000144199248602000014334785924	cancellednot_delivered20000143306257102000014403184862paidshipped	deliveredc                     | d   S )Nr>    xs    r'   <lambda>z'get_dashboard_metrics.<locals>.<lambda>M  s
    ad r)   )keyreverse         ?)idtitle	thumbnailrevenuequantitystockprice
percentagei'  )rq   rr   rs   
days_stockrw   c                     | d   S )Nry   ri   rj   s    r'   rl   z'get_dashboard_metrics.<locals>.<lambda>  s
    1\? r)   )rm   z*[DEBUG_DASHBOARD] Calculating cash flow...z'[DEBUG_DASHBOARD] Cash flow calculated.)end_date_localcurrent_visitscurrent_salesy&1?TinyProductz%Y-%m-%d
ads_cache__)SystemConfig)r7   r8   r9   i  i  	timestampdata)fastcache)rm   group)r   r   amountcostitem_idz[ERROR] Ads Fetch CRASHED: -u   Produto Indisponívelrq   logistic_typeorder_idr	   
buyer_name zCliente Desconhecidototaltotal_revenuers   rr   sku
ml_item_idru   status
net_marginmargin_percent
total_costcosts)producttaxfeeshippingadsr    )rr   r   r   ru   rw   rs   )re   rf   rg   z$[WARNING] Profit Trend Calc Failed: 	total_ads	visits_7dvisits_trend
revenue_7drevenue_gross_7drevenue_cancelled_7drevenue_trendsales_count_7dsales_count_cancelledsales_count_total_transrevenue_adsrevenue_organic	profit_7dprofit_trendads_cost_7daverage_marginperiod_label)	start_utcend_utcperiod)	low_stock_adsstock_risk_valuestock_risk_countparetostock_risks	cash_flowconversion_badges
sales_list
debug_info)successerrorr   i  )fr   r   argsgetloggerinfor   r
   r   nowrT   strlowerstripr-   isdigitintr   r   r!   r   countr   sumr   visitsr	   scalar	Exceptionprintfloatr   r   r   r    r"   order_byr   r#   openstrftimewritelenml_order_idtotal_amounttagsappenddate_closed
unit_priceru   r   sortedrq   in_rr   rs   available_quantityrw   roundr   joingroup_byr   get_cash_flow_dataget_conversion_distributionsetaddapp.models.tiny_productr   r   upperr   app.models.system_configr   json	filter_byfirstvalueloadsr   get_ads_performancedumpscommit
isinstancedictr   split
startswithsale_feeshipping_costmaxfree_shippingshipping_type	isoformatbuyer_first_namebuyer_last_namebuyer_nicknamer(   r   close	traceback	print_exc)r$   
days_paramtz_brnow_brtoday_br_startstart_date_brend_date_brprev_start_date_brprev_end_date_brr   days_int_for_stockp_cleanfirst_of_currlast_of_prevfirst_of_prevlast_of_month_before_lastd_valrV   start_date_utcend_date_utcprev_start_date_utcprev_end_date_utcr   visits_currentq_visitsis_today_endshould_be_inclusive
res_scalareq_prevres_prev_scalarvisits_prevr   q_orderscurr_ordersfts
curr_grosscurr_cancelledsales_count_currentvalid_orders_for_paretoIGNORED_IDSovalUNCANCELLED_IDSsales_current_sumq_prev_ordersprev_orders
prev_grossprev_cancelledsales_count_prevsales_prev_sumr   conversion_rateitem_revenue_mapitem_qty_mapitemrevsorted_itemspareto_datatotal_period_revenue_for_sharepidr   
pareto_idsadads_mapshare	risky_ads	days_span
sales_datar   qty	sales_map
active_adsr   r   stock_risks_listsold_qtyvelocityrv   
days_coverval_at_risk	top_riskscash_flow_databadgesr   TAX_RATEitem_idsr   
tiny_cachet	ads_cacher[   calculated_profitcalculated_avg_margintotal_margin_percent_sumvalid_items_countsales_count_validr   item_sold_qty_mapall_item_idsiads_cost_per_unit_mapr   r   meli_serviced_fromd_to	cache_keyr   r   ads_datasc
cache_time
cached_obj	cached_tsrowr_amountr_cost	r_item_idis_cancelled	order_rev
order_costsum_prod_costsum_tax_costsum_fee_costsum_shipping_costsum_ads_costtotal_order_qtyr   p_costad_costitem_sku_norm
found_costpartsprefixt_skut_prodf_costt_costs_costorder_shippinga_cost
margin_valr   first_item_thumbfirst_item_titlefirst_item_skuf_itemad_objr   prev_profit
is_ignoredis_uncancelledis_effective_saleo_revo_costu_costsku_norm	ads_ratioprofit_diffr   r   s                                                                                                                                                                      r'   get_dashboard_metricsr     sp   	Bp\\%%fc2
HUV ,-e$  QqPQR! 6@#j/'')//1Rc>W.'W2D*M(92rSU+VVK!/)2C!C-!L!"^w+5G9K*YA->>M(K!/)2C!C-	q0AA"L!"^*YA->>M K!/)2D!D-	q0AA,L!"_*YB-??M K!/)2D!D-	r0BB-L!#'7k+A*22q29M K*22q29M(9!+<<L!-!5!5!!5!<,'L"-"=!C!Ca!G$=(@*22q29M(9!+<<L(00Q07M'K)M(5	q8I(I%!:!B!Bq!B!I,)L"-"=!C!C %,OO$5CL1E!&*YE-BBM K!/)2K!K-	u0EE&ugU3L
	@ &m4#K0*+=>()9:88H\N[\HHRL''		X(=>DDF		Nxx)>)> ?@GGH[H[_l_q_q_sHstH!,!1!1!3v{{}!D',$"==,+/('!)1D1DHXHXHZ1Z![h!)1D1D{GWGWGY1Y!Zh!*J0:0FS_AN
	XXdhh~'<'<=>EEnFYFY]o]t]t]vFvwF~':':=M=R=R=T'TU$mmoO2A2M#o.STK ?">2U;5GG5Q\K]]addLa L 88G$,,Z-FG
 w';';~'MNHw';';l'JKH $$W%9%9%>%>%@Alln	+os+ Gq\\^,,-@A*RD(FzlRTUV,^,<J|nTVWX0[1A0B"EF	G 
"$
  =	2A}}+ S Q]]6H%H +!,C xx;&166o6O   23EFO_,/1Qs"$)$(//2xx;&c!
#%#q(# V#c!
#q(#'..q1Y&!((k*Ac!
#q(#'..q1{=	2~ "*-n0EE )001D1DH[1[\*11'2E2EHY2YZ] $'')
 	+A,1-SK'188v+=YAVZ[ZbZbfqZqs"!Q&!HH+#s*^	+ z*U>-BB 1$#$56~9NNRWXfRggknnM A2^CsJO ( 	lA lDOO0q1T]]5GaH4D4H4HZ]4^ad4d 10<0@0@RS0TX\XeXeXjij0kT__-	l	l .446NTXYZ\[\]7AA~3& )55fc1c5
5')xx|':':25599Z;P'Q'U'U'WX255"9XX$ 	HCS!B99S@E%'S-/R\\T(,,S!4<>R227a8A13rxx}1-#E1o	  		" 	 +=*A&q	 XXk44dhh{?S?S6TUWffW-A-A^-S&T !7!78 	 V``\WcW#/c#hq@`	`XXb\((h)>?CCE
 	B }}RUUA.H)+H)).QEJ!|"X-
33 A% #BHHM2U: K/  ''%%XX!#"'
A"6"288=q1) 	* +1JKBQO	@A+B0B0B0DkFVFVFXZ_`=> - &++-)-
 
  5 	2Axx6!QXX%:ahh+>UGG 2DLL12	2 
 	8
	=?XXk=R=V=V=Xb\]\a\a155;;=..0!3bZb 		+-88B<+;+;+=>a!$$'>Y>  ##&  ! u 	4Axx;&'' 4Q7H7L7LQ\\[\7]`a`j`j7j&q||4!%%all34	4 !# 7-b1&//
;"++J7 )$8	AXXl+55)5DJJL$.2H$HSd
"((&%)ZZ%9
$.NN;$B	$LLN446BjP'1~~fb'AH  +??fdY]?^H!!-)7!KBFF2J#'::)1)A)A)C$,/ $ 		' Y%c40',SWWX->-C!'DH%*3776?+?a%@F(+	(:I'83K'61K(Y:K-K+<Y+G#+a<GMPXGX$9)$DY*  O	AHH3L%*%!Q&! ann12IJ  MLL #LO ?'4==0$--,1-"4??#7a8
 ]]4??3 24%1- =AHH 0 6 6 8"Q;$s]F "%J %*)D%*:m+D+I+I+NQ%O
 "S(] - 3 3C 8u:?%(XXeCRj%9F1;1A1A1C *v#(#3#3F#;AQPQUV@V16v{{1CJ$)*
 (#-F t}}12S8 $c)H4 !&q';!!<!A%+c!S\.BBFB,,"2#3#3#8q9C?F ??&;;24??CcIF'&&!V+!&?'B '5DGXX[ggJy)E*,==JMRS\M]`aMaeJ/%	2BBSHghN  !Z/!(N:(!Q&!  $6!Nww#)<< 7=zz!1!1!3t"v'8'89'-'7'7$  amm   AMM  ann002$	 
 !3!3!9r :!A<M<M<SQS;TU[[]  Labaqaq  L  vL       -  )  ~  qwwaggaj33D  O  !((  j  !.   j! " ,'' 1'# 0  77$  WW,-EE155;;=t"#,, !

"1<<#415JS--XYXdXdJeq||!<!F!Fko $1   _O	b q $<?P$P! i	/4GIZ[[[ " YC#MM[0t*#%!--;M*M\`z#-#@#eQ]]VeEe &+"")-&hh+- hh"BB)-&%"1>>#6Q7U!Vgg 4N"1::?3&+ALL,=A&> (mmALL99;%1"5"aKAB(;(;(=BX (X-C*/
80D0I0I0NQ*O"(3, #-s"2X"="'

a"83">"%*/0D1*E*Q.&4s1c!''l7K&KV R%5%5&+B,<,<,A&BS&HV, #&&N{Q)4z)AY'1C'79&DV6F?V#;f#Dv#MMi4Nl !U5\E&M%AB[sYCv a$%67%:LL!,{!:c A#a' % c5/@)AE+DV)VXY#Z[  %
%
%
 E,2%
 % 115	%

 j! 4%
 #E.!$<%
 U=!4%
 /%
 $%:%
 &':%
 5a0%
  !%
$ *%%
& E,2'%
( 5a0)%
* 3+%
. L/%
0 . %&6 : 0!$'!'$+5577C<113&A%
 %` 	
a  	;A3?@N		K8G G
  	+%aS)**	+J 6X8 aP c
 ?j&#%:  3A378^$l  	8<=L	d  V
  	 	 	
 	
s  MAk; "DAh 4B1Ah9 %C5Ak; Ai 'A2AiAi !DAk; 7C>Ak; 6Ak; D?Ak; Ai5AAk; /Ai;EAk; Aj 7BAk; EAk;  8Ak; !Aj :Aj+Aj7Aj 9Ak; <%Aj !Aj5Aj 7.Ak; &A-Ak; BAj* A'Aj  ABAj* CAAj* D3Aj* D8
Aj* E
Aj* EFAk; K(Ak; K<KAk; WB+AkY9Ak; ZA>Ak \G>Ak dC2Ak; h	Ah6hAh1h+Ak; h1Ah6h6Ak; h9Ai h=Ak; iAiiAi i	Ai2iAi-i'Ak; i-Ai2i2Ak; jAj jAjjAk; jAj jAjjAk; j Aj'j$Aj* j*	Akj3AkkAk; kAkkAk; k	Ak8kAk3k-Ak; k3Ak8k8Ak; k;	Aml-Aml1Aml2Am mAmmAm mAm!c                    t         j                  d| d|        ||z
  j                  dk  }i }|}|r$t        dd      D ]  }|dd}|ddddd d	||<    n7||k  r2|j	                  d
      }|ddddd d	||<   |t        d      z  }||k  r2t        j                  |t        j                  j                         |      j                  t        j                        j                  d       }	t        j                  |t        j                  j                         |      j                  t        j                        j                  d       }
| j                  t               j#                  t%        t         j&                              j)                  t         j*                  |	k\  t         j*                  |
k        j-                         }t/               }t/               }|D ]b  }|j&                  D ]Q  }|j0                  r|j3                  |j0                         |j4                  s7|j3                  |j4                         S d ddlm} ddlm} i }|rh| j                  |      j)                  |j>                  jA                  tC        |                  j-                         D ci c]  }|j>                  | }}i }|rh| j                  |      j)                  |j4                  jA                  tC        |                  j-                         D ci c]  }|j4                  | }}d}t        jD                  |      }d}|r$|tG        jH                         k(  r|jJ                  }nd}d}|D ]|  }|jL                  dk(  r|j*                  xs |jN                  j                  t        j                        j                  |      }|r6|jJ                  }|dd}|r3||k  r.|tQ        |jR                  xs d      z  }n|j	                  d
      }||v stQ        |jR                  xs d      }d}|j&                  D ]`  }tU        |jV                  xs d      }tQ        |jX                  xs d      }||z  } |j[                  |j0                        }!|!rtQ        |!j\                  xs d      nd}"|"dk(  rJ|j4                  r>|j[                  |j4                        }#|#r!|#j\                  rtQ        |#j\                        }"|"|z  }$| |z  }%tQ        |j^                  xs d      |z  }&tQ        |j`                  xs d      }'tc        |j&                        }(|'dkD  r|(dkD  r|'|(z  })n-|!r)|!jd                  rtQ        |!j`                  xs d      |z  })nd})||$|%z   |&z   |)z   z  }c ||   dxx   |z  cc<   ||   dxx   |z  cc<   tQ        ||   d         tQ        ||   d         z
  ||   d<    ||z
  }*|*j                  dk(  r|t        d      z
  }+|t        d      z
  },n|t        d      z
  },|,|*z
  }+t        j                  |+t        j                  j                         |      j                  t        j                        j                  d       }-t        j                  |,t        j                  j                         |      j                  t        j                        j                  d       }.| j                  t               j)                  t         j*                  |-k\  t         j*                  |.k        j-                         }/d}0|/D ]  }|jL                  dk(  r|jN                  j                  t        j                        j                  |      }|rM|jJ                  }|dd}|rv|tG        jH                         k(  r_||k  rZ|0tQ        |jR                  xs d      z  }0n=|jG                         |+z
  j                  }1|t        |1      z   }2|2j	                  d
      }||v s||   dxx   tQ        |jR                  xs d      z  cc<    |r;	 | j                  tf              j)                  ti        jF                  tf        jj                        |k(        jm                  tf        jj                  jo                               j-                         }3i }4|3D ]I  }5|5jj                  jJ                  }|dd}|4j[                  |d      tQ        |5jp                        z   |4|<   K t        dd      D ]Y  }|dd}||v s||4v r|4|   dkD  r|4|   ||   d<   &|0dkD  r||0z  }6t        t        |6d      d      }6nd}6||   d   |6z  ||   d<   [ 	 tC        |jw                               S c c}w c c}w # tr        $ rB}7tu        d|7        t        dd      D ]  }|dd}||v s||   d   ||   d<    Y d }7~7id }7~7ww xY w)Nz-[DEBUG_DASHBOARD] get_cash_flow_data called: rW   r>   r      02hrY   )namereceitacustolucroreceita_anteriorreceita_projetadaz%d/%mr?   rQ   r   r   r   i  r~   ra   r  r  r  r  r  g?g      @rp   z%[ERROR] Dashboard Projection failed: )<r   r   r-   ranger   r
   r   combinemintime
astimezoner   utcrT   r   r   r   r   r   r    r!   r   r#   r   r   r   r   app.models.adr   r   r   rq   r   listr   r	   r9   r2   r   r"   r   r   r   ru   r   r   r   r   r   r   r   r   r   	hora_alvor   r   valor_previstor   r   values)8r$   r%   r&   tz_obj	is_hourly
chart_datacurrr  rm   start_dtend_dtordersrK  skusr%  r2  r   r   rN  r[   rL  rM  current_total_so_far	now_localcurrent_bucket_idxcurrent_bucket_valrJ  dt_localorder_revenuerg  r?  r   item_revenuer9  	unit_cost	tiny_prod	prod_costtax_costfee_costry  	num_itemsr   period_delta
prev_startprev_endprev_start_dtprev_end_dtr*  prev_total_so_far	days_difftarget_datelogsforecast_bucketsloggrowth_factorr  s8                                                           r'   r   r   U  sw	   
KK?
|4PXzZ[J&,,1I JD q" 	ArF!*Cs$'dJsO	 h--(Cs$'dJsO
 I1%%D h 
HLL,=,=,?OZZ[c[g[ghppx|p}Hh(9(9(;FKVVW_WcWcdlltxlyFXXg&&z'--'@AHHI\I\`hIhjqj}j}  BH  kH  I  M  M  OF uH5D ,GG 	,DT__ =xx$((+	,,
 !3I&(hhrl&9&9"%%))DN:S&T&X&X&Z[QTT1W[	[J(*(=(D(D[__EXEXY]^bYcEd(e(i(i(kl1aeeQhl
l V$IZ4::</&^^ H >k88{"H MM3Q^^<<HLL<Q\\]cdArF!*CQ"44%q~~/B)CC%##G,C*!!.."5A6MJ  'N$--,1-"4??#7a8
)C/ ]]4??335E"'',Q/3	 >dhh *txx 8I Y^^$))..$9	%O	 ((2 !!3!4s:
 "'q';!!<L	!A%)a-$2Y$>MB,,$)"*:*:*?a$@3$FM$'Mi(2X=MM
O'NR sOI&-7&sOG$
2$',Z_Y-G'H5Q[\_Q`ahQiKj'jJsOG$}>kD j(LA)"33
iQ// 	q 11,
$$Z1B1B1DVT__`h`l`lmuu  ~Bu  CM""8X\\->->-@P[[\d\h\hiqqy}q~K((7#**7+>+>-+OQXQdQdhsQstxxzK N88{"H>>)))>II&Q ArF!*CZ4::<7AAS<S!U1>>+>Q%??! ":5;;I$yi'@@K&&w/C*sO./59L13MM/'N. "	_88K(//		+//0J>h{,,0023CCE 
  " cMM&&2aj(8(<(<S#(FsOaOaIb(b %c 1b\ s2aj*$..3CC3H13L?OPS?T
3(;< -q0,@CT,TM,/M30G,MM,/M?I#Oa?ber?r
3(;<s, 
!!#$$C \ mj  	_9!=>1b\ _2aj*$;Ec?K];^JsO$78_	_s2   )f1f6C*f; 	Af; ;	h'h,hhc                    ddl m} ddlm} |r||z
  j                  dz   }nd}|dkD  r||z  dz  nd}	|t        |      z
  }
|t        d      z
  }t        | j                  t        j                  |j                              j                  |j                  |
k\  |j                  |k        j                         xs d      }ddlm} t!        t        d	
            }t#        j$                  |
t"        j&                  j)                         |      j+                  t         j,                        j/                  d      }t#        j$                  |t"        j0                  j)                         |      j+                  t         j,                        j/                  d      }t        | j                  t        j2                  |j4                              j                  |j6                  |k\  |j6                  |k  |j8                  j;                  g d            j                         xs d      }|dkD  r||z  dz  nd}t=        |      dkD  r't=        |	      t=        |      z
  t=        |      z  dz  }n	|	dk(  rdnd}| j                  |j>                  t        j                  |j                        jA                  d      t        j                  |jB                        jA                  d            j                  |j                  |k\  |j                  |r|n|t        |      z   k        jE                  |j>                        jG                  t        j                  |j                        dkD        jI                  tK        t        j                  |jB                        t        jL                  t        j                  |j                        d      z              jO                  d      jQ                         }g }|D ]'  }| j                  |      j                  |j4                  |j>                  k(        jS                         }|sL|jT                  sY|jT                  dkD  sit=        |jV                  xs d      }t=        |jT                  xs d      }||z  dz  }|jY                  |j4                  t[        |j\                        dkD  r|j\                  dd dz   n|j\                  |j^                  t        |      t        |      t=        ta        |d            d       * | j                  |j>                  t        j                  |j                        jA                  d      t        j                  |jB                        jA                  d            j                  |j                  |k\  |j                  |r|n|t        |      z   k        jE                  |j>                        jG                  t        j                  |j                        dkD        jQ                         }d}d}d}|D ]t  }|jT                  s|jT                  dkD  s t=        |jV                  xs d      }t=        |jT                  xs d      }||z  dz  }|dkD  r|dz  }e|dk\  r|dz  }p|dz  }v t=        ta        |d            t=        |      dk\  t=        ta        |	d            t=        ta        |d            ||ddd|ddd|dddgdS )u   
    Calculate conversion stats including:
    - Conversion trend vs previous period
    - Top converting ads (with links)
    - Distribution by conversion quality (BONS, MÉD, RUIM)
    r   r   r   r>   rF   rZ   r?   r   r/   r0   rQ   N)re   rf   rg   partially_paidtotal_visitstotal_sales
   ro   (   z...rD   )rq   rr   rs   r   salesr/  rS   BONSztext-emerald-400)r&  labelcoloru   MÉDztext-amber-400RUIMztext-rose-400)trendis_positivecurrent_rate	prev_ratetop_convertersdistribution)1app.models.ml_metrics_dailyr   r  r   r-   r
   r   r   r   r   r   r!   r	   r   r   r   r   r   r  r  r  r  r  rT   r   r   rq   r   r   r   r   r   r  	sales_qtyr   havingr   r   nulliflimitr#   r   r  r  r   r   rr   rs   r   ) r$   start_date_localstart_dt_utcr{   r|   r}   r   r   period_dayscurrent_conversionr  r  prev_visits_qr   r  
p_start_dtp_end_dtprev_sales_qprev_conversionconversion_trendtop_ads_queryr  ra  r9  t_salest_visits	conv_rateall_ads_querybonsmedioruimrates                                    r'   r   r   (  s    ;  %(88>>B DRTUCU-.83>[\ "I;$??J)"33H.*?*?!@AHHz)x' fh M , YR()E !!*hll.?.?.A%P[[\d\h\hiqqy}q~J(,,*;*;*=eLWWX`XdXdemmuymzHrxx

7:: 67>>z)x'MN fh	 	L ?La>O|m3c9UVO _!"#569OOSXYhSiimpp 2a 71S HH&&'--n=))*00? f//.FVYbhsYtFtu hf&&'",hTXXn../$++dhh~G\G\>]_`2aabeAhssu   N XXb\  #++!56<<>#""s'7'7!';COO0q1GS--23H 8+s2I!!ee25bhh-"2D#2."((\\h-W#(y!)<#=#   HH&&'--n=))*00? f//.FVYbhsYtFtu hf&&'!+	ce  DED 
??r2BNN/a0GR__12Hh&#-Dax	
	
 u-q12-.!3e$6:;5!45(64FGF5EF6OD
 r)   c                     ddl m} | j                  |      j                  t	        |j
                              j                  |j                  |k\  |j                  |k        j                         S )zA
    Helper to fetch orders in a period with items preloaded
    r   r   )	r   r   r   r   r   r    r!   r   r#   )r$   r  r  r   s       r'   r(   r(     s\     ,88G$$Z%>?FFx'f$ 
cer)   )Nr   r   ))flaskr   r   logging
sqlalchemyr   r   r   r   r	   r
   r   	getLogger__name__r   r   r   app.apir   app.core.databaser   r  r   r   r   r   r  r   app.models.forecast_learningr   app.services.meli_apir   app.services.sync_enginer   app.core.constantsr   r(   router  r   r   ri   r)   r'   <module>r     s    "  & & 8 8			8	$ %  *  4 6 4 0 / 6	 "UG4r 5rjQ%f@Fr)   