
    !+il(                     *   d 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
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mZ  ej0                  e      Zd Zd Zedk(  r0 ej:                  ej<                          e       Z e de        yy)zu
Hyper Forecast V2 - Product Metrics Sync Job
Calculates and syncs product-level metrics for intelligent forecasting
    N)datetime	timedelta)funcand_)Decimal)DictList)SessionLocal)Ad)MlOrderMlOrderItem)ProductForecast)STOCK_RISK_WARNING_DAYSSTOCK_RISK_CRITICAL_DAYSc                     t         j                  d       t               } 	 | j                  t              j                  t        j                  j                  ddg            j                         }t         j                  dt        |       d       t        j                         }|t        d      z
  }|t        d      z
  }|t        d	      z
  }g d
}d}|D ]  }	 | j                  t              j                  t        j                  |j                  k(        j!                         }	|	s't        |j                        }	| j#                  |	       |j$                  |	_        |j&                  |	_        |j(                  |	_        |j,                  |	_        |j.                  rt1        t3        |j.                              n
t1        d      |	_        |j4                  rt1        t3        |j4                              n
t1        d      |	_        |	j.                  rI|	j4                  r=|	j.                  dkD  r.|	j.                  |	j4                  z
  |	j.                  z  dz  |	_        | j                  t9        j:                  t<        j>                        jA                  d      t9        j:                  t<        jB                  t<        j>                  z        jA                  d            jE                  tF              j                  t<        jH                  |j                  k(  tF        jJ                  |k\  tF        j                  j                  |            j!                         }
|
jL                  xs d|	_'        t1        t3        |
jP                  xs d            |	_)        t1        t3        |	jN                  xs ddz              |	_*        | j                  t9        j:                  t<        j>                        jA                  d      t9        j:                  t<        jB                  t<        j>                  z        jA                  d            jE                  tF              j                  t<        jH                  |j                  k(  tF        jJ                  |k\  tF        j                  j                  |            j!                         }|jL                  xs d|	_+        t1        t3        |jP                  xs d            |	_,        t1        t3        |	jV                  xs ddz              |	_-        | j                  t9        j:                  t<        j>                        jA                  d            jE                  tF              j                  t<        jH                  |j                  k(  tF        jJ                  |k\  tF        jJ                  |k  tF        j                  j                  |            j]                         xs d}|dkD  r|	jN                  |z
  |z  dz  }t1        t3        t_        ta        |d      d                  jc                  t1        d            |	_2        |	jd                  dkD  rd|	_3        nG|	jd                  dk  rd|	_3        n0d|	_3        n(t1        d      |	_2        |	jN                  dk(  rdnd|	_3        |jh                  xs d|	_5        tm        |dd      xs d|	_7        |jp                  r|jh                  nd|	_9        tm        |dd      xs d|	_:        |	jj                  |	jn                  z   }|	jT                  rc|	jT                  dkD  rTt1        t3        |            |	jT                  z  }t_        |t1        d            jc                  t1        d            |	_;        nt1        d      |	_;        |	jj                  dk(  r|	jn                  dk(  rd|	_<        d|	_=        n|	jj                  dk(  r|	jn                  dkD  rd|	_<        d |	_=        np|	jv                  d!k  rd"|	_<        d|	_=        nR|	jv                  t|        k  rd#|	_<        d|	_=        n0|	jv                  t~        k  rd$|	_<        d|	_=        nd%|	_<        d |	_=        |	jj                  dkD  rd&nd'}|	jj                  dk(  r|	jn                  dkD  rd(}|	jj                  dkD  r0|	jv                  r$|	jv                  d)k  rt        |	jv                        }|	jT                  t1        t3        |            z  |	_A        |	j.                  r|	j                  |	j.                  z  n
t1        d      |	_B        |j                  dk(  |	_C        t        j                         |	_E        |d)z  } t        |        | j                          t         j                  d-| d.       d%|d/| j                          S # t        $ r0}t         j                  d*|j                   d+|        Y d,}~	 d,}~ww xY w# t        $ rP}t         j                  d0|        | j                          d1t3        |      d2cY d,}~| j                          S d,}~ww xY w# | j                          w xY w)3z~
    Main job to sync product-level metrics for forecasting.
    Should run daily (or more frequently for stock updates).
    z/[PRODUCT-SYNC] Starting product metrics sync...activepausedz[PRODUCT-SYNC] Processing z active/paused products...   )days      )paidshipped	deliveredr   )mlb_id0d   qtyrevenuegR?gR?@z0.00
   upidownstablestock_incoming
stock_tinyz999.9z0.1stockoutT
restockingF   criticallowwarningokg      ?g        g?   z [PRODUCT-SYNC] Error processing z: Nz[PRODUCT-SYNC] Synced z products successfully)statussyncedz[PRODUCT-SYNC] Sync failed: error)r.   message)Lloggerinfor
   queryr   filterr.   in_alllenr   nowr   r   r   idfirstaddtitleskucategory_namecategory_ml	thumbnailpricer   strcost
margin_pctr   sumr   quantitylabel
unit_pricejoinr   
ml_item_iddate_closedr   total_units_7dr   total_revenue_7davg_units_7dtotal_units_30dtotal_revenue_30davg_units_30dscalarminmaxquantize	trend_pcttrendavailable_quantitystock_currentgetattrr$   is_full
stock_fullstock_localdays_of_coveragestock_statushas_rupture_riskr   r   floatforecast_units_todayforecast_revenue_today	is_activeutcnow
updated_at	Exceptionr0   _calculate_abc_curvecommitcloserollback)dbproductsr9   week_ago	month_agotwo_weeks_agovalid_statusesr/   productpfsales_7d	sales_30dprev_week_salesvaltotal_stock_for_coveragecoveragestock_factores                     C/var/www/hypershopcomercio.com.br/hyper-ai/app/jobs/product_sync.pysync_product_metricsr~      s   
 KKAB	Bd88B<&&ryy}}h5I'JKOOQ0X?YZ[ lln**),,	iR00 : F	GEXXo.55#**gjj8%'  (

;BFF2J #== !(!6!6&00:A--73w}}#56WUX\8?'#gll"34'RU, 88BHHqL&(hh&8BHH%Ds%JBM 88HH[11288?HH[33k6J6JJKQQR[\ $w-**gjj8''83NN&&~6! %'  %-LL$5A!&-c(2B2B2Ga.H&I#")#r/@/@/EA.J*K"L HHHH[11288?HH[33k6J6JJKQQR[\ $w-**gjj8''94NN&&~6! %'  &/]]%7a"'.s93D3D3I/J'K$#*30B0B0Ga2/M+N#O  #%((HH[11288?#$w-**gjj8''=8''(2NN&&~6	!
 &(#      #Q&--??RVYYC#*3s3sG3Df/M+N#O#X#XY`agYh#iBL||b(#'+#)#+#*3<BL+-+<+<+AxtBH $+#=#=#B $+G5Eq$I$NQ!>Eoo : :ST!(,!B!Ga ,.+;+;b>O>O+O( ??r':&s+C'DEWH*-h8H*I*R*RSZ[`Sa*bB'*1'*:B' ##q(R->->!-C&0BO*.B'%%*r/@/@1/D&2BO*/B'((1,&0BO*.B'((+CC&+BO*.B'((+BB&/BO*.B'&*BO*/B' ')&6&6&:s##q(R->->-B#&L##a'B,?,?BDWDWZ[D[#()<)<#=L*,//GCDU<V*V'RTRZRZB,C,Cbhh,N`ghk`l) '( : ( 1!EF	R 	R 
		,VH4JKL&1 	
#  ?

|2aSQR  63A378
!c!f55

6
 	
s[   C h "bg	=;h 		h%g=7h =hh 	i5iii! ii! !i3c                    	 | j                  t              j                  t        j                  dk(        j	                  t        j
                  j                               j                         }|syt        d |D              }|dk(  r|D ]  }d|_	        d|_
         yd}|D ]K  }|t        |j
                  xs d      z  }||z  }|dk  rd|_	        n|d	k  rd
|_	        nd|_	        d|_
        M t        j                  dt        |       d       y# t        $ r"}t        j!                  d|        Y d}~yd}~ww xY w)zj
    Calculate ABC curve based on revenue.
    A: Top 80% of revenue
    B: Next 15%
    C: Bottom 5%
    TNc              3   N   K   | ]  }t        |j                  xs d         yw)r   N)rb   rQ   ).0ps     r}   	<genexpr>z'_calculate_abc_curve.<locals>.<genexpr>   s      NE!"5"5":;Ns   #%r   Cr   g?Agffffff?Bz([PRODUCT-SYNC] ABC curve calculated for z	 productsz,[PRODUCT-SYNC] Error calculating ABC curve: )r4   r   r5   re   order_byrQ   descr7   rF   curvecurve_criteriarb   r2   r3   r8   rh   r0   )rm   rn   total_revenuer   
cumulativepctr|   s          r}   ri   ri      s@   #I88O,33%%-

(?4499;
<SSU 	  NXNNA -#, -  
 
	)A% 3 3 8q99J},Cd{(A
	) 	>s8}oYWX ICA3GHHIs%   A0D 3,D  A4D 	E D;;E __main__)levelzResult: )!__doc__loggingr   r   
sqlalchemyr   r   decimalr   typingr   r	   app.core.databaser
   app.models.adr   app.models.ml_orderr   r   app.models.product_forecastr   app.core.constantsr   r   	getLogger__name__r2   r~   ri   basicConfigINFOresultprint     r}   <module>r      s     ( !   *  4 7 P			8	$m`*IZ zGgll+!#F	HVH
 r   