+
    j0                     R    R t ^ RIHtHtHt ^ RIt^ RIHt ^ RI	H
t
  ! R R4      tR# )zd
OMS Regression Data Service
Extracts and engineers features for ML models directly from OMS MySQL.
)ListOptionalDictN)
get_logger)
MySQLModelc                      a  ] tR t^t o Rt. ROt. ROtR tV 3R lR ltRV 3R lR llt	V 3R lR	 lt
RV 3R
 lR lltRtV tR# )OMSRegressionDataServicez<Pulls and engineers ML features from OMS operational tables.c                D    \        R 4      V n        \        4       V n        R# )oms_regression_data_serviceN)r   loggerr   _db)selfs   &UD:\Projects\python\client-ideal-oms_pyapi\app\services\oms_regression_data_service.py__init__!OMSRegressionDataService.__init__   s     !>?<    c                :   < V ^8  d   QhRS[ RS[P                  /#    
company_idreturnintpd	DataFrame)format__classdict__s   "r   __annotate__%OMSRegressionDataService.__annotate__"   s     * *C *BLL *r   c                f   RpV P                   P                  W!34      pV'       g   \        P                  ! 4       # \        P                  ! V4      pV P                  RR.,           pV FD  pWdP
                  9   g   K  \        P                  ! WF,          RR7      P                  ^ 4      WF&   KF  	  V# )z
Returns per-client feature matrix with binary label is_high_risk.
Excludes clients with fewer than 3 credited collections (insufficient history).
a  
            SELECT
                col.client_id,
                COUNT(col.id)                                                           AS total_collections,
                COALESCE(AVG(DATEDIFF(col.credited_date, col.collection_date)), 0)      AS avg_settlement_lag,
                SUM(CASE WHEN col.bounce_date IS NOT NULL THEN 1 ELSE 0 END)            AS bounce_count,
                SUM(CASE WHEN col.bounce_date IS NOT NULL THEN 1 ELSE 0 END)
                    / COUNT(col.id)                                                     AS bounce_rate,
                SUM(CASE WHEN col.collection_mode = 2 THEN 1 ELSE 0 END)
                    / COUNT(col.id)                                                     AS cheque_ratio,
                COALESCE(
                    MAX(cb.closing_bal) / NULLIF(MAX(cl.total_credit_limit), 0),
                    0
                )                                                                       AS balance_credit_ratio,
                CASE
                    WHEN AVG(DATEDIFF(col.credited_date, col.collection_date)) > 30
                         OR SUM(CASE WHEN col.bounce_date IS NOT NULL THEN 1 ELSE 0 END) > 0
                    THEN 1 ELSE 0
                END                                                                     AS is_high_risk
            FROM collection col
            JOIN clients cl ON cl.id = col.client_id AND cl.deleted_at IS NULL
            LEFT JOIN client_balance cb
                ON cb.client_id = col.client_id AND cb.company_id = col.company_id
            WHERE col.company_id = %s
              AND col.deleted_at IS NULL
              AND col.credited_date IS NOT NULL
            GROUP BY col.client_id
            HAVING total_collections >= 3
        is_high_risk	client_idcoerceerrors)r   execute_queryr   r   COLLECTION_FEATUREScolumns
to_numericfillna)r   r   sqlrowsdfnumeric_colscols   &&     r   !get_collection_risk_training_data:OMSRegressionDataService.get_collection_risk_training_data"   s    
: xx%%c=9<<>!\\$//>;2OOCjj --AHHK   	r   Nc                `   < V ^8  d   QhRS[ RS[S[S[ ,          ,          RS[P                  /# )r   r   
client_idsr   r   r   r   r   r   )r   r   s   "r   r   r   N   s6     2 22 T#Y'2 
	2r   c                   RpV.pV'       d:   RP                  R.\        V4      ,          4      pRV R2pVP                  V4       RV R2pV P                  P	                  V\        V4      4      pV'       g   \        P                  ! 4       # \        P                  ! V4      pV P                  R.,            FD  p	WP                  9   g   K  \        P                  ! W,          R	R
7      P                  ^ 4      W&   KF  	  V# )z
Returns current feature values for scoring (no label column).
Uses last 12 months of data for recency.
client_ids=None means score all active clients.
 ,%szAND col.client_id IN ()a  
            SELECT
                col.client_id,
                COUNT(col.id)                                                           AS total_collections,
                COALESCE(AVG(DATEDIFF(col.credited_date, col.collection_date)), 0)      AS avg_settlement_lag,
                SUM(CASE WHEN col.bounce_date IS NOT NULL THEN 1 ELSE 0 END)            AS bounce_count,
                SUM(CASE WHEN col.bounce_date IS NOT NULL THEN 1 ELSE 0 END)
                    / NULLIF(COUNT(col.id), 0)                                          AS bounce_rate,
                SUM(CASE WHEN col.collection_mode = 2 THEN 1 ELSE 0 END)
                    / NULLIF(COUNT(col.id), 0)                                          AS cheque_ratio,
                COALESCE(
                    MAX(cb.closing_bal) / NULLIF(MAX(cl.total_credit_limit), 0),
                    0
                )                                                                       AS balance_credit_ratio
            FROM collection col
            JOIN clients cl ON cl.id = col.client_id AND cl.deleted_at IS NULL
            LEFT JOIN client_balance cb
                ON cb.client_id = col.client_id AND cb.company_id = col.company_id
            WHERE col.company_id = %s
              AND col.deleted_at IS NULL
              AND col.credited_date IS NOT NULL
              AND col.collection_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
              zV
            GROUP BY col.client_id
            HAVING total_collections >= 1
        r!   r"   r#   )joinlenextendr   r%   tupler   r   r&   r'   r(   r)   )
r   r   r2   client_filterparamsplaceholdersr*   r+   r,   r.   s
   &&&       r   $get_collection_risk_scoring_features=OMSRegressionDataService.get_collection_risk_scoring_featuresN   s     88TFS_$<=L4\N!DMMM*%, o 	-4 xx%%c5=9<<>!\\$++{m;;Cjj --AHHK < 	r   c                :   < V ^8  d   QhRS[ RS[P                  /# r   r   )r   r   s   "r   r   r      s     8 8 8 8r   c                B   RpV P                   P                  W!34      pV'       g   \        P                  ! 4       # \        P                  ! V4      pR	 F2  p\        P                  ! WE,          RR7      P                  ^ 4      WE&   K4  	  VP                  . R
O4      pVP                  RR.4      P                  4       ^,           VR&   VP                  RR.4      R,          P                  ^4      P                  ^ 4      VR&   VP                  RR.4      R,          P                  4       pWf^8  ,          P                  pVP                  RR.4      pVP                  VP                  P                  V4      ,          P                  4       pWDR,          ^8  ,          pV# )u   
Returns historical attainment % per product_head × district × season.
Adds lag-1 prev_attainment_pct feature via pandas shift.
Requires at least 2 seasons per combination.
a  
            SELECT
                phdt.product_head_id,
                phdt.district_id,
                phdt.account_id                                                          AS account_year_id,
                phdt.target_qty,
                COALESCE(SUM(tsp.qty), 0)                                               AS actual_qty,
                COALESCE(SUM(tsp.qty), 0) / NULLIF(phdt.target_qty, 0) * 100           AS attainment_pct,
                ay.from_period
            FROM product_head_district_target phdt
            JOIN mas_accountyear ay ON ay.id = phdt.account_id
            LEFT JOIN transaction_sales ts
                ON ts.account_year_id = phdt.account_id
               AND ts.company_id = %s
               AND ts.status = 5
               AND ts.deleted_by > 0
            LEFT JOIN transaction_sales_products tsp ON tsp.tran_sales_id = ts.id
            LEFT JOIN products p
                ON p.id = tsp.product_id
               AND p.product_head_id = phdt.product_head_id
            WHERE phdt.target_qty > 0
            GROUP BY phdt.product_head_id, phdt.district_id, phdt.account_id,
                     phdt.target_qty, ay.from_period
            ORDER BY phdt.product_head_id, phdt.district_id, ay.from_period
        product_head_iddistrict_idattainment_pctr"   r#   
season_numprev_attainment_pct)rD   rE   account_year_id
target_qty
actual_qtyrF   )rD   rE   from_period)r   r%   r   r   r(   r)   sort_valuesgroupbycumcountshiftcountindex	set_indexlocisinreset_index)r   r   r*   r+   r,   r.   countsvalids   &&      r   get_sales_target_training_data7OMSRegressionDataService.get_sales_target_training_data   sc   2 xx%%c=9<<>!\\$BCmmBGH=DDQGBGB
 ^^MN::'8-&HIRRTWXX<$&JJ.%

%!E!HVVAY 	 !
 .>?MSSU{#))\\,m<=VVBHHMM%()557 < 1$%	r   c          
         < V ^8  d   QhRS[ RS[ RS[S[S[ ,          ,          RS[S[S[ ,          ,          RS[P                  /# )r   r   rI   product_head_idsdistrict_idsr   r3   )r   r   s   "r   r   r      sZ     T TT T #49-	T
 tCy)T 
Tr   c                   RpRpV.pV'       d:   RP                  R.\        V4      ,          4      pRV R2pVP                  V4       V'       d:   RP                  R.\        V4      ,          4      pRV R2pVP                  V4       RV RV R	2p	V P                  P	                  V	\        V4      4      p
V
'       g   \        P                  ! 4       # \        P                  ! V
4      pR F2  p\        P                  ! W,          RR7      P                  ^ 4      W&   K4  	  RpV P                  P	                  WV34      pV'       do   \        P                  ! V4      pR F2  p\        P                  ! W,          RR7      P                  ^ 4      W&   K4  	  VP                  V. RO,          R
R.RR7      pMRVR&   VR,          P                  ^ 4      VR&   V# )z
Returns feature rows for the given account year to run predictions on.
Includes lag feature from the most recent completed season.
r5   r6   r7   zAND phdt.product_head_id IN (r8   zAND phdt.district_id IN (a_  
            SELECT
                phdt.product_head_id,
                phdt.district_id,
                phdt.account_id AS account_year_id,
                phdt.target_qty,
                1 AS season_num
            FROM product_head_district_target phdt
            WHERE phdt.account_id = %s
              AND phdt.target_qty > 0
              z
              z	
        rD   rE   r"   r#   a  
            SELECT
                phdt.product_head_id,
                phdt.district_id,
                COALESCE(SUM(tsp.qty), 0) / NULLIF(phdt.target_qty, 0) * 100 AS prev_attainment_pct
            FROM product_head_district_target phdt
            JOIN mas_accountyear ay ON ay.id = phdt.account_id
            LEFT JOIN transaction_sales ts
                ON ts.account_year_id = phdt.account_id
               AND ts.company_id = %s
               AND ts.status = 5
               AND ts.deleted_by > 0
            LEFT JOIN transaction_sales_products tsp ON tsp.tran_sales_id = ts.id
            LEFT JOIN products p
                ON p.id = tsp.product_id
               AND p.product_head_id = phdt.product_head_id
            WHERE phdt.account_id = (
                SELECT id FROM mas_accountyear
                WHERE from_period < (SELECT from_period FROM mas_accountyear WHERE id = %s)
                ORDER BY from_period DESC LIMIT 1
            )
            GROUP BY phdt.product_head_id, phdt.district_id, phdt.target_qty
        rH   left)onhowg        )rD   rE   rI   rJ   )rD   rE   rH   )r9   r:   r;   r   r%   r<   r   r   r(   r)   merge)r   r   rI   r\   r]   	ph_filterd_filterr>   r?   r*   r+   r,   r.   prev_sql	prev_rowsprev_dfs   &&&&&           r   $get_sales_target_prediction_features=OMSRegressionDataService.get_sales_target_prediction_features   s    	!"88TFS1A-B$BCL7~QGIMM*+88TFS->$>?L2<.BHMM,'
 k j 	 xx%%c5=9<<>!\\$VCmmBGH=DDQGBG W	. HH**8/5RS	ll9-GP!}}W\(KRRSTU QQR%}5  B ),B$%$&'<$=$D$DQ$G !	r   )r   r   )total_collectionsavg_settlement_lagbounce_countbounce_ratecheque_ratiobalance_credit_ratio)rJ   rG   rH   rD   rE   )N)NN)__name__
__module____qualname____firstlineno____doc__r&   SALES_TARGET_FEATURESr   r/   r@   rY   rh   __static_attributes____classdictcell__)r   s   @r   r   r      sP     F

 * *X2 2p8 8tT T Tr   r   )rt   typingr   r   r   pandasr   app.utils.helpersr   app.models.mysql_modelr   r    r   r   <module>r}      s'   
 ( '  ( -G Gr   