import asyncio
import pandas as pd
from datetime import datetime
from utils import execute_query, get_cloudwatch_logger, log_exception
from scripts.mysql_queries import UPDATE_LOANS_DATA_QUERY
from utils import local_tz
from datetime import datetime
current_time = datetime.now(local_tz).strftime('%Y-%m-%d %H:%M:%S')
logger = get_cloudwatch_logger(stream_name="co_lending_update_loans_data")

def get_dpd_bucket(dpd):
    if pd.isna(dpd) or dpd <= 0: return 'Current'
    if dpd <= 30: return '1-30'
    if dpd <= 60: return '31-60'
    if dpd <= 90: return '61-90'
    if dpd <= 120: return '91-120'
    if dpd <= 150: return '121-150'
    if dpd <= 180: return '151-180'
    if dpd <= 364: return '181-364'
    return '>=365'

def update_co_lending_loan_metrics(df):
    """
    Updates the co_lending_loan_metrics table with the calculated metrics per loan and rps_type.
    """
    if df.empty:
        return
        
    try:
        sql = """
        INSERT INTO co_lending_loan_metrics (
            loan_id, rps_type, pre_emi_due, principal_outstanding, interest_outstanding, interest_due, total_bounce_due, 
            total_penal_due, amount_overdue, total_outstanding, first_unpaid_emi_date, dpd, 
            dpd_bucket, emi_paid_count, emi_unpaid_count, created_at, updated_at
        ) VALUES (
            :loan_id, :rps_type, :pre_emi_due, :principal_outstanding, :interest_outstanding, :interest_due, :total_bounce_due, 
            :total_penal_due, :amount_overdue, :total_outstanding, :first_unpaid_emi_date, :dpd, 
            :dpd_bucket, :emi_paid_count, :emi_unpaid_count, :current_time, :current_time
        )
        ON DUPLICATE KEY UPDATE
            pre_emi_due = VALUES(pre_emi_due),
            principal_outstanding = VALUES(principal_outstanding),
            interest_outstanding = VALUES(interest_outstanding),
            interest_due = VALUES(interest_due),
            total_bounce_due = VALUES(total_bounce_due),
            total_penal_due = VALUES(total_penal_due),
            amount_overdue = VALUES(amount_overdue),
            total_outstanding = VALUES(total_outstanding),
            first_unpaid_emi_date = VALUES(first_unpaid_emi_date),
            dpd = VALUES(dpd),
            dpd_bucket = VALUES(dpd_bucket),
            emi_paid_count = VALUES(emi_paid_count),
            emi_unpaid_count = VALUES(emi_unpaid_count),
            updated_at = VALUES(updated_at)
        """
        
        batch_size = 500
        all_params = []
        
        for index, row in df.iterrows():
            dpd = int(row.dpd) if not pd.isna(row.dpd) else 0
            first_unpaid = row.first_unpaid_emi_date if not pd.isna(row.first_unpaid_emi_date) else None
            
            all_params.append({
                "loan_id": row.loan_id,
                "rps_type": row.rps_type,
                "pre_emi_due": round(float(row.pre_emi_due), 2) if not pd.isna(row.pre_emi_due) else 0.0,
                "principal_outstanding": round(float(row.principal_outstanding), 2) if not pd.isna(row.principal_outstanding) else 0.0,
                "interest_outstanding": round(float(row.interest_outstanding), 2) if not pd.isna(row.interest_outstanding) else 0.0,
                "interest_due": round(float(row.interest_due), 2) if not pd.isna(row.interest_due) else 0.0,
                "total_bounce_due": round(float(row.total_bounce_due), 2) if not pd.isna(row.total_bounce_due) else 0.0,
                "total_penal_due": round(float(row.total_penal_due), 2) if not pd.isna(row.total_penal_due) else 0.0,
                "amount_overdue": round(float(row.amount_overdue), 2) if not pd.isna(row.amount_overdue) else 0.0,
                "total_outstanding": round(float(row.total_outstanding), 2) if not pd.isna(row.total_outstanding) else 0.0,
                "first_unpaid_emi_date": first_unpaid,
                "dpd": dpd,
                "dpd_bucket": get_dpd_bucket(dpd),
                "emi_paid_count": int(row.emi_paid_count),
                "emi_unpaid_count": int(row.emi_unpaid_count),
                "current_time": current_time
            })
            
            if len(all_params) >= batch_size:
                execute_query(sql, all_params)
                all_params = []
                
        if all_params:
            execute_query(sql, all_params)
            
    except Exception as e:
        log_exception(logger, f"Error in update_co_lending_loan_metrics: {str(e)}")

def process_loan_updates(loan_ids=None):
    try:
        query = UPDATE_LOANS_DATA_QUERY
        params = {}
        if loan_ids:
            format_strings = ','.join([':l_' + str(j) for j in range(len(loan_ids))])
            where_clause = f"WHERE ep.deleted_at IS NULL AND ep.loan_id IN ({format_strings})"
            query = query.replace("WHERE ep.deleted_at IS NULL", where_clause)
            params = {f'l_{j}': lid for j, lid in enumerate(loan_ids)}
            
        df = execute_query(query, params=params)
        if df is None or df.empty:
            logger.send_log_event("INFO: No co-lending data to update.")
            return

        update_co_lending_loan_metrics(df)
        logger.send_log_event(f"INFO: Successfully updated co_lending_loan_metrics for {len(df)} loans.")
    except Exception as e:
        log_exception(logger, f"Error in process_loan_updates: {e}")

async def main():
    process_loan_updates()

if __name__ == '__main__':
    asyncio.run(main())