
    !+i,                         d dl mZmZmZ d dlmZ d dlmZm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Z ej(                  e      Z G d	 d
      Zy)    )funcand_case)Session)datetime	timedelta)MlOrderMlOrderItem)	FixedCostProductFinancialMetric)Ad)PurchaseOrderPurchaseStatusNc            
       J    e Zd ZdefdZd Zdededededef
d	Z	dd
Z
ddZy)FinancialServicedbc                     || _         y )N)r   )selfr   s     L/var/www/hypershopcomercio.com.br/hyper-ai/app/services/financial_service.py__init__zFinancialService.__init__   s	        c                 Z   t         j                  d       | j                  j                  t	        j
                  t        j                              j                  t        j                  dk(        j                         xs d}t         j                  d|d       t        j                         }|t        d      z
  }|t        d      z
  }| j                  j                  t        j                        j!                  t"              j                  t"        j$                  |k\  t        j                  j'                  d	            j)                         j+                         }|D cg c]  }|d   s	|d    }}t         j                  d
t-        |       d       | j                  j                  t	        j
                  t        j.                  t        j0                  z              j!                  t"              j                  t"        j$                  |k\  t"        j2                  dk(        j                         xs d}t         j                  d|d       |D ]  }	| j5                  |	||||        | j                  j7                          t         j                  d       y	c c}w )u   
        Calcula métricas financeiras (taxa de devolução e rateio de custos) para todos os SKUs
        com vendas nos últimos 30-90 dias.
        u.   Iniciando cálculo de métricas financeiras...Tr   u*   Custo Fixo Total Mensal da Operação: R$ z.2fZ   days   NzProcessando z SKUs ativos recentemente.paidzFaturamento Total (30d): R$ u   Cálculo financeiro concluído.)loggerinfor   queryr   sumr   amountfilteractivescalarr   utcnowr   r
   skujoinr	   date_createdisnotdistinctalllenquantity
unit_pricestatus_process_skucommit)
r   total_fixed_costnowlast_90dlast_30dskusssku_listtotal_revenue_30dr'   s
             r   calculate_metricsz"FinancialService.calculate_metrics   s   
 	DE  77==)2B2B)CDKKIL\L\`dLdellnsrs@AQRU@VWX oo++++ ww}}[__-227;BB  H,OO!!$'
 (*SSU 	
 #'/Q!A$AaD//l3x=/1KLM !GGMMHH[))K,B,BBC

$w-  H,NNf$
 &(
  	 	23DS2IJK 	\Cc#46FRZ[	\ 	56% 0s   )
J(4J(r'   total_revenue_companyr3   date_30ddate_90dc                 
   | j                   j                  t        j                  t        j
                              j                  t              j                  t        j                  |k(  t        j                  |k\        j                         xs d}|dk(  ry| j                   j                  t        j                  t        j
                              j                  t              j                  t        j                  |k(  t        j                  |k\  t        j                  dk7        j                         xs d}||z  }d}	| j                   j                  t        j                  t        j
                  t        j                  z              j                  t              j                  t        j                  |k(  t        j                  |k\  t        j                  dk(        j                         xs d}
d}d}|dkD  rt        |
      t        |      z  }t        |      |z  }| j                   j                  t        j                  t        j
                              j                  t              j                  t        j                  |k(  t        j                  |k\  t        j                  dk(        j                         xs d}|dkD  r||z  }| j                   j                  t              j                  t        j                  |k(        j!                         }|s't        |      }| j                   j#                  |       ||_        |	|_        ||_        ||_        t-        j.                         |_        y)u<   Calcula métricas específicas para um SKU e salva no banco.r   Nr   g      4@           )r'   )r   r    r   r!   r
   r.   r(   r	   r#   r'   r)   r%   r0   r/   floatr   firstaddreturn_rate_90davg_return_costrevenue_share_30dcalculated_fixed_cost_sharer   r&   last_calculated_at)r   r'   r<   r3   r=   r>   total_items_90dreturned_items_90dreturn_raterF   sku_revenue_30drevenue_sharecalculated_share_valuetotal_share_amountsku_units_30dmetrics                   r   r1   zFinancialService._process_sku;   s   
 ''--1E1E(FGLLWU\\OOs"  H,
 &(  	
 a
 "WW]]488K4H4H+IJOOPWX__OOs"  H,NNf$
 &(	 	 	 )?:
   ''--HH[))K,B,BBC

$w-OOs"  H,NNf$
 &(  	 !$ 1$!/2U;P5QQM "''7!8=!H !GGMM$((;3G3G*HINNwW^^3&$$0&( fh	 	  q );m)K& 56==>T>X>X\_>_`ffh+4FGGKK!,!0#0 -C*$,OO$5!r   c           	         | j                   j                  t              j                  t        j                  dk(        j                         }d}d}d}|D ]s  }|j                  xs ddz  }||z  }	|j                  xs d}
|j                  xs d}||	|
z  z  }||	|z  z  }|j                  xs d|j                  xs dz   }|||z  z  }u ||z  dz  }||z  }| j                   j                  t        j                  t        j                              j                  t        j                  j                  t         j"                  t         j$                  t         j&                  g            j)                         xs d}||z   ||z   z
  }t+        dt-        |            t-        |      t-        |      t-        |      t-        |      t-        |      |t/        j0                         dS )u   
        Calcula o Open-to-Buy (Verba Disponível para Compra) Global.
        OTB = (Vendas Previstas + Estoque Final Desejado) - (Estoque Atual + Pedidos em Aberto)
        Retorna valor monetário (R$).
        r$   r@   r         >@   )	otb_valueprojected_sales_valueprojected_cogstarget_ending_inventorycurrent_inventory_valueon_order_valueperiod_dayscalculation_date)r   r    r   r#   r0   r,   	sales_30dpricecostavailable_quantity
stock_fullr   r!   r   
total_costin_r   SENT	CONFIRMEDSHIPPINGr%   maxrB   r   r&   )r   days_periodtarget_weeks_supplyadsprojected_revenuerX   rZ   addaily_salesforecast_qtyr_   r`   current_invweekly_cogsrY   r[   rV   s                    r   calculate_otbzFinancialService.calculate_otb   s    ggmmB&&ryyH'<=AAC"% 	:B<<,14K&4LHHME77<aD!55lT11N 005A"--:L1MK#{T'99#	:" &3q8"-0C"C txx0H0H'IJVM((,,##((''.    
   	 $&==BY\jBjk	 Qi 01%*+<%=#N3',-D'E',-D'E#N3& ( 1	
 		
r   c                 "   t        j                         j                         }|t        |      z   }i }|}||k  r1|j	                  d      }|ddg dd||<   |t        d      z  }||k  r1| j
                  j                  t              j                  t        j                  dk(        j                         }d}|D ]+  }	|	j                  xs ddz  }
||
|	j                  xs dz  z  }- d	}||z  }|D ]  }||   d
xx   |z  cc<    | j
                  j                  t              j                  t        j                  dk(        j                         }|D ]  }|j                  xs d}|}||k  s|j                   |k(  rp|j	                  d      }||v r[||   dxx   t#        |j$                        z  cc<   ||   d   j'                  |j(                  t#        |j$                        dd       |t        d      z  }||k  r | j
                  j                  t*              j                  t*        j                  j-                  t.        j0                  t.        j2                  t.        j4                  g            j                         }|D ]  }|j6                  }|st9        |t               r|j                         }||cxk  r|k  s>n A|j	                  d      }||v sWt#        |j:                  xs d      t#        |j<                  xs d      z   }||   dxx   |z  cc<   ||   d   j'                  d|j>                   |dd        tA        |jC                         d       }d}|D ]  }|d
   |d   z
  }||z  }||d<    |S )u   
        Gera fluxo de caixa projetado diário.
        Inflows: Vendas diárias previstas.
        Outflows: Contas Fixas (no dia de pagamento), Pedidos de Compra (na data esperada).
        r   z%Y-%m-%dr@   )dateinflowoutflowdetailsaccumulatedrA   r$   r   rT   g      ?ru   Trv   rw   fixed)namevaluetypezPedido #purchasec                     | d   S )Nrt    )xs    r   <lambda>z;FinancialService.get_cash_flow_projection.<locals>.<lambda>   s
    6 r   )keyrx   )"r   r&   rt   r   strftimer   r    r   r#   r0   r,   r^   r_   r   r$   day_of_monthdayrB   r"   appendrz   r   rd   r   re   rf   rg   expected_date
isinstancerc   additional_costsidsortedvalues)r   r   todayend_datetimelinecurrentd_strrk   daily_revenuerm   rn   avg_net_rateestimated_daily_inflowfixed_costsr`   r   
check_dateordersorderdue_datevalresultrx   itemnets                            r   get_cash_flow_projectionz)FinancialService.get_cash_flow_projection   s    !&&(9$// !$$Z0E',XZknoHUOya((G ! ggmmB&&ryyH'<=AAC 	<BLL-A5[kRXX];;]	< !.!= 	@EUOH%)??%	@ ggmmI.55i6F6F$6NOSSU 		0D##(qCJ(>>S(&//
;E( 	2eDKK6HH2 	299499W\]a]h]hWisz:{|iQ//
 (		0 }-44  $$n&9&9>;S;SUcUlUl%mn

#% 	  	}E**Hh1'}}HH00$--j9E(#E$4$4$9:U5CYCYC^]^=__ 	2c92 	299XehhZCXcfpz:{|	} )/BC  	.Dx.4	?2C3K"-D	.
 r   N)r      )r   )__name__
__module____qualname__r   r   r;   strrB   r   r1   rr   r   r   r   r   r   r      sV    7 )7VD6 D6E D6UZ D6fn D6  {C D6P7
rJr   r   )
sqlalchemyr   r   r   sqlalchemy.ormr   r   r   app.models.ml_orderr	   r
   app.models.financialr   r   app.models.adr   app.models.supplyr   r   logging	getLoggerr   r   r   r   r   r   <module>r      s>    ' ' " ( 4 B  ; 			8	$z zr   