
    whE                     |    d dl Zd dlZd dlmZ d dlZd dlmZ d dlmZm	Z	 ddl
mZmZmZmZ ddlmZ  G d d	      Zy)
    N)load_workbook)Session)DictAny   )Module10RequestModule10ResponseMetricValueOverview)DatabaseServicec                       e Zd ZddefdZdedefdZdedefdZ	de
defd	Zd
edefdZdededefdZdedefdZdedefdZdedee
ef   defdZdefdZy)Module10ServiceNdbc                 F    d| _         || _        |rt        |      | _        y y )Nzod:\after train\report\retail-simulation-api\excel_project\data\Simulation Test file - clean_complete_model.xlsx)
excel_pathr   r   
db_service)selfr   s     ?/var/www/html/retail-simulation-api/app/api/module10/service.py__init__zModule10Service.__init__   s'     M-b1DO     requestreturnc                    K   	 | j                  |      }| j                  ||      }t        dg d||      S # t        $ r2}t	        dt        |              | j                  |      cY d}~S d}~ww xY ww)z<Calculate Module 10 metrics dynamically based on user inputs+Module 10 - Metrics and Performance IndexeszY1-Y2zY3-Y4zY5-Y6moduleperiods_analyzedmetrics_valuesoverviewzError in calculate_metrics: N)_calculate_dynamic_metrics_generate_dynamic_overviewr	   	Exceptionprintstr_get_fallback_response)r   r   metrics_datar    es        r   calculate_metricsz!Module10Service.calculate_metrics   s|     	8::7CL66|WMH#D!<+!	   	80Q9:..w77	8s1   A638 A6	A3'A.(A3)A6.A33A6c                 b   i }i }t        |      D ])  }|j                  d      rt        ||d      }|%|||<   + t        dt	        |       d       |j                         D ]5  \  }}t        |d      s| j                  ||      }|j                  |       7 |s| j                  |      }|S )z2Calculate metrics dynamically based on user inputs_NzProcessing z input fields dynamicallyY1_Y2)
dir
startswithgetattrr$   lenitemshasattr_derive_metrics_from_inputupdate#_calculate_default_business_metrics)r   r   r'   input_fields
field_namefield_valueperiod_increasebase_metricss           r   r!   z*Module10Service._calculate_dynamic_metrics$   s     g,J((-%gz4@*/:L,	 ' 	C-..GHI ,8+=+=+?'J0#>>z?[##L1	 ,@ CCLQLr   
input_namec                    i }d|j                         v rrt        |j                  dz  |j                  dz  |j                  dz        |d<   t        |j                  dz  |j                  dz  |j                  d	z        |d
<   |S d|j                         v rlt        |j                  |j                  |j                        |d<   t        |j                   dz  |j                   dz  |j                   dz        |d<   |S d|j                         v rrt        |j                  dz  |j                  dz  |j                  dz        |d<   t        |j                  dz  |j                  dz  |j                  dz        |d<   |S d|j                         v rit        |j                  |j                  |j                        |d<   t        |j                  dz  |j                  dz  |j                  dz        |d<   |S d|j                         v sd|j                         v rlt        |j                  |j                  |j                        |d<   t        |j                   dz  |j                   dz  |j                   dz        |d<   |S |j                  dd      j                         dz   }t        |j                  |j                  |j                        ||<   |S ) z1Derive business metrics from specific input field
investmentg333333?g?gffffff?Y1_Y2_increaseY3_Y4_increaseY5_Y6_increasezInvestment ROIg?g?g      ?zAsset GrowthrentzOperating Cost Impact      ?zProfit Margin Impact	inventoryg      ?zSales Growthgffffff?zInventory TurnoveronlinezDigital Revenueg333333?Customer AcquisitionbillsexpensezOperating Expensesg333333?zCost Efficiencyr+    z Impact)lowerr
   r,   Y3_Y4Y5_Y6replacetitle)r   r;   r9   metricsmetric_names        r   r3   z*Module10Service._derive_metrics_from_input?   s#    :++--(3.44s:.44s:.44s:)G$%
 '2.44s:.44s:.44s:'GN#~ s z''))/:.44.44.440G+,
 /: / 5 55; / 5 55; / 5 55;/G*+f [ J,,..&1.44s:.44s:.44s:'GN#
 -8.44s:.44s:.44s:-G()N C ))++)4.44.44.44*G%&
 /:.44s:.44s:.44s:/G*+6 + 
((**i:;K;K;M.M,7.44.44.44-G()
 *5 / 5 55; / 5 55; / 5 55;*G%&  %,,S#6<<>JK#..44.44.44$GK  r   r6   c                 "    dt        ddd      iS )zCCalculate default business metrics when no specific inputs providedzBusiness Performance      $@       @      @r>   )r
   )r   r6   s     r   r5   z3Module10Service._calculate_default_business_metrics   s!     #K#""%
 	
r   r'   c                 D   g }|j                         D ]  \  }}|j                  }|j                  }|j                  }|dkD  r|j	                  | d| d       n|dk  r|j	                  | d| d       ||kD  r|j	                  | d       n||dz  k  r|j	                  | d       |d	kD  s|j	                  | d
| d        t        fdt              D              }	t        t        |      |t        |	      t        t        |            ddddd      S )zGGenerate dynamic overview based on actual inputs and calculated metrics   z: Strong early growth (z% in Y1-Y2)r   z: Initial decline (z: Accelerating growth trendrC   z: Growth slowing in mid-term
   z: Sustained long-term growth (z%)c              3   \   K   | ]#  }|j                  d       st        |      d % yw)r+   Nr   )r.   r/   ).0r7   r   s     r   	<genexpr>z=Module10Service._generate_dynamic_overview.<locals>.<genexpr>   s3      il
 * 5 5c :wwPZ?[?g ls   ),z(Dynamic calculation based on user inputsz-Initial period with user-defined growth ratesz.Mid-term period with projected business impactz+Long-term period with sustained performance)inputs_providedmetrics_calculatedanalysis_typeY1_Y2_periodY3_Y4_periodY5_Y6_periodtotal_metrics_analyzedkey_insightsperformance_summary)
r1   r?   r@   rA   appendsumr-   r   r0   r%   )
r   r'   r   rc   rP   metric_valuey1_y2y3_y4y5_y6input_counts
     `       r   r"   z*Module10Service._generate_dynamic_overview   sP    *6););)=%K //E //E //E rz##{m3J5'Q\$]^##{m3Fug[$YZu}##{m3N$OP$##{m3O$PQrz##{m3QRWQXXZ$[\# *>(  ic'l i i #&|#4%#&{#3&)#l*;&<!K O P M!
 	
r   c                 j    | j                  |      }| j                  ||      }t        dg d||      S )z(Fallback response when calculation failsr   r   r   )r!   r"   r	   )r   r   fallback_metricsr    s       r   r&   z&Module10Service._get_fallback_response   s?    ::7C223CWM@8+	
 	
r   simulation_idc                 h  K   | j                   s| j                         S 	 | j                   j                  |      }| j                   j                  |      }ddlm} t               }|j                         D ]L  \  }t        t              st        fddD              s, |d   d   d         }t        |||       N | j                  |       d{   }| j                  ||      }	|	S 7 # t        $ r1}
t        d	t!        |
              | j                         cY d}
~
S d}
~
ww xY ww)
z<Calculate metrics using database simulation data dynamicallyr   )PeriodIncreasec              3   &   K   | ]  }|v  
 y wN )rY   kperiod_datas     r   rZ   z<Module10Service.calculate_from_simulation.<locals>.<genexpr>   s     8oSnak9ISns   )r,   rK   rL   r,   rK   rL   Nz$Error in calculate_from_simulation: )r   _get_sample_responseget_simulation_inputsget_simulation_period_inputsschemarp   r   r1   
isinstancedictallsetattrr)   _enhance_with_database_insightsr#   r$   r%   )r   rn   	db_inputsperiod_inputsrp   r   r7   r9   resultenhanced_resultr(   ru   s              @r   calculate_from_simulationz)Module10Service.calculate_from_simulation   s"    ,,..	/==mLI OOHHWM /%'G ,9+>+>+@'
Kk40S8oSn8o5o&4)'2)'2)'2'O
 GZA ,A  11'::F #BB69UO"" ;  	/8QAB,,..	/sS   D2A,C5 C5 #7C5 C3C5 2D23C5 5	D/>&D*$D/%D2*D//D2r   r   c           
         	 |j                   j                  j                         }d|v r<|d   }|j                  d|d    d|d    d       |j                  d|d   d	d
       d|v r>|d   }|d   }|dz  }|j                  d|dd       |j                  d|d   d	d       d|v rP|d   }|d   }	|j                  d|	dd       d|v r,|d   d   dz  }|dz  |	z  dz  }
|j                  d|
dd       d|v r+|d   }|d   |d   z   }|dz  }|j                  d |dd!       |j                  j                         }d|v rd|v r|d   }|d   }|j                  di       j                  dd"      d"kD  r+|d   dz  |d   d   z  }t        |d#z  |d$z  |d%z  &      |d'<   |d   dz  |d   z  }t        |d(z  |dz  |d)z  &      |d*<   |j                   j                  j                         }d+|d,<   d-|d.<   d/|d0<   t        |j                  |j                  |t        t        |      ||1      2      }|S # t        $ r#}t        d3t        |              |cY d4}~S d4}~ww xY w)5z7Enhance Module 10 results with actual database insights
brand_datazBrand Analysis: 
brand_namez with store_formatsz store formatszTotal Store Area: total_store_size,z sq ft planned
sales_datatotal_monthly_sales   u   Revenue Projection: ₹z,.0fz annual sales targetzCustomer Traffic: bills_per_monthz bills per monthinvestment_datatotal_capital_investmentu   Capital Investment: ₹z total investment plannedg333333?d   zProjected ROI: z.1fz% annual return on investmentoperational_datamonthly_rentmonthly_utilitiesu   Operating Expenses: ₹z annual operational costsr   g?g{Gz?gQ?r>   zSales per Sq FtrV   rW   zAsset Turnover Ratioz1Dynamic calculation from actual simulation inputsdata_sourcezAll 10 modules data integratedmodules_integratedz/Database-driven with business logic enhancementcalculation_methodra   r   z(Error enhancing with database insights: N)r    rc   copyre   r   getr
   rd   r	   r   r   r   r0   r#   r$   r%   )r   r   r   enhanced_insightsr   r   monthly_salesannual_salesr   total_investmentroi_percentager   monthly_opexannual_opexenhanced_metricssales_per_sqftasset_turnoverenhanced_summaryr   r(   s                       r   r~   z/Module10Service._enhance_with_database_insights   sr   T	 & < < A A C y(&|4
!((+;J|<T;UU[\fgv\w[x  yG  *H  I!((+=jI[>\]^=__m)no y(&|4
 *+@ A,r1!((+B<PTBUUi)jk!((+=jIZ>[\]=^^n)op !I-"+,=">#23M#N !((+BCSTXBYYr)st  9,#,\#:;P#QTV#VL&2T&9=M%MPS%SN%,,~c>RRo-pq "Y.#,-?#@ /?BRSfBgg*R/!((+B;tBTTm)no  &4499; y(->)-K&|4
"+,="> ==r2667I1MPQQ%/0E%F%KiXdNefxNy%yN:E'5';'5'<'5'<;$%67 ",,A!BR!G/ZtJu!u;F#1B#6#1B#6#1B#6< !78  &BBGGI.a]+5U125f12 /}}!'!8!8/!+./?+@!2(8		O #" 	<SVHEFM	s   H9H< <	I(I#I(#I(c                     t        ddd      t        ddd      t        dd	d      t        ddd      t        dd
d      d}g d}t        dg d|t        t        |      |dddddd            S )z8Enhanced sample response with realistic business metricsg      9@g      2@g      .@r>   g      (@rR   rS   g      >@g      4@rT   g      @)zRevenue GrowthzProfit Margin ImprovementrF   zOperational EfficiencyzMarket Share Growth)z2Strong revenue growth projected across all periodsz-Profit margins expected to improve with scalez3Customer acquisition rates highest in initial yearsz5Operational efficiency gains through experience curvez,Market share growth sustainable in long termz5Module 10 - Metrics and Performance Indexes (Dynamic)r   z+Comprehensive business performance analysisz!All simulation modules integratedz#High growth phase with market entryz)Consolidation phase with efficiency focusz&Maturity phase with sustainable growth)r]   data_integrationr^   r_   r`   ra   r   )r
   r	   r   r0   )r   sample_metricssample_insightss      r   rv   z$Module10Service._get_sample_responseF  s     *###
 *5##"*
 %0###%
 '2###'
 $/"""$+
8
  J8)'*>':,%R(K$I$O$L%
	
 	
r   rr   )__name__
__module____qualname__r   r   r   r	   r)   r{   r!   r%   r3   r5   r   r"   r&   intr   r   r   r~   rv   rs   r   r   r   r   
   s    27 28 8CS 8&/ d 6JS Jd JX
 
 
'
t '
o '
Zb '
R

o 

BR 

"/S "/EU "/HV6F VSWX[]`X`Sa Vfv Vp5
&6 5
r   r   )pandaspdopenpyxlr   ossqlalchemy.ormr   typingr   r   ry   r   r	   r
   r   r   r   r   rs   r   r   <module>r      s,      " 	 "  L L 'q
 q
r   