o
    j0                     @   sJ   d Z ddlmZmZmZ ddlZddlmZ ddl	m
Z
 G dd dZdS )zd
OMS Regression Data Service
Extracts and engineers features for ML models directly from OMS MySQL.
    )ListOptionalDictN)
get_logger)
MySQLModelc                   @   s   e Zd ZdZg dZg dZdd Zdedej	fdd	Z
	
ddedeee  dej	fddZdedej	fddZ	
	
ddededeee  deee  dej	f
ddZd
S )OMSRegressionDataServicez<Pulls and engineers ML features from OMS operational tables.)total_collectionsavg_settlement_lagbounce_countbounce_ratecheque_ratiobalance_credit_ratio)
target_qty
season_numprev_attainment_pctproduct_head_iddistrict_idc                 C   s   t d| _t | _d S )Noms_regression_data_service)r   loggerr   _db)self r   F/var/www/html/oms-prod-mcp/app/services/oms_regression_data_service.py__init__   s   
z!OMSRegressionDataService.__init__
company_idreturnc                 C   sl   d}| j ||f}|st S t|}| jddg }|D ]}||jv r3tj|| ddd||< q|S )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errorsr   )r   execute_querypd	DataFrameCOLLECTION_FEATUREScolumns
to_numericfillna)r   r   sqlrowsdfnumeric_colscolr   r   r   !get_collection_risk_training_data"   s   

z:OMSRegressionDataService.get_collection_risk_training_dataN
client_idsc           
      C   s   d}|g}|rd dgt| }d| d}|| d| d}| j|t|}|s1t S t|}| jdg D ]}	|	|j	v rQtj
||	 d	d
d||	< q<|S )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   r   )joinlenextendr   r!   tupler"   r#   r$   r%   r&   r'   )
r   r   r.   client_filterparamsplaceholdersr(   r)   r*   r,   r   r   r   $get_collection_risk_scoring_featuresN   s$   



z=OMSRegressionDataService.get_collection_risk_scoring_featuresc                 C   s   d}| j ||f}|st S t|}dD ]}tj|| ddd||< q|g d}|ddg d	 |d
< |ddgd 	d	d|d< |ddgd
 
 }||dk j}|ddg}|j|j|  }||d
 d	k }|S )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
        )r   r   account_year_idr   
actual_qtyattainment_pctr   r   r   )r   r   from_periodr   r      r   r=   r      )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validr   r   r   get_sales_target_training_data   s,   
z7OMSRegressionDataService.get_sales_target_training_datar;   product_head_idsdistrict_idsc                 C   sP  d}d}|g}|rd dgt| }d| d}|| |r5d dgt| }d| d}|| d| d| d	}	| j|	t|}
|
sMt S t|
}d
D ]}tj|| dd	d||< qTd}| j|||f}|rt|}dD ]}tj|| dd	d||< qy|j
|g d ddgdd}nd|d< |d 	d|d< |S )z
        Returns feature rows for the given account year to run predictions on.
        Includes lag feature from the most recent completed season.
        r/   r0   r1   zAND phdt.product_head_id IN (r2   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	
        )r   r   r;   r   r   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
        )r   r   r   r   r   left)onhowg        r   )r3   r4   r5   r   r!   r6   r"   r#   r&   r'   merge)r   r   r;   rN   rO   	ph_filterd_filterr8   r9   r(   r)   r*   r,   prev_sql	prev_rowsprev_dfr   r   r   $get_sales_target_prediction_features   sH   





z=OMSRegressionDataService.get_sales_target_prediction_features)N)NN)__name__
__module____qualname____doc__r$   SALES_TARGET_FEATURESr   intr"   r#   r-   r   r   r:   rM   rY   r   r   r   r   r      s8    /

8>

r   )r]   typingr   r   r   pandasr"   app.utils.helpersr   app.models.mysql_modelr   r   r   r   r   r   <module>   s    