import pandas as pd
import numpy as np
from datetime import datetime
from scripts.mysql_queries import bounce_penal_query
from core_utilities import mail_service
from utils import execute_query, zip_file, calculate_bounce_penal_function, get_cloudwatch_logger
import warnings, pytz, os, asyncio
warnings.filterwarnings('ignore')
local_tz = pytz.timezone("Asia/Kolkata")
BOUNCE_PENAL_DIR = "bounce_penal_logs_folder"
os.makedirs(BOUNCE_PENAL_DIR, exist_ok=True)
logger = get_cloudwatch_logger(stream_name="payment_reconcilition")


def update_emi_payment_details(calculated_df, batch_size=3000):
    """
    Updates emi_payments table with calculated bounce, penalty & payment_status details in batches.
    Parameters:
        calculated_df (pd.DataFrame): DataFrame containing EMI calculations with columns:
            - application_id, emi_payment_id, bounce_charges, delay_penalty, total_payable, amount_due, delay_days
        batch_size (int): Number of rows to update per batch execution.
    """
    now = datetime.now()
    sql = """
        update emi_payments
        set bounce_charges = :bounce_charges, delay_penalty = :delay_penalty, total_payable = :total_payable, amount_due = :amount_due,
            delay_days = :delay_days, updated_at = :updated_at,
            payment_status = CASE
                WHEN :amount_due = 0 THEN 'success'
                WHEN :amount_due > 0 AND :amount_due < :total_payable THEN 'partial'
                ELSE 'pending'
            END
        WHERE application_id = :application_id AND id = :id
    """
    all_params = [
        {
            "application_id": row.application_id, "id": row.emi_payment_id, "bounce_charges": row.bounce_charges, "delay_penalty": row.delay_penalty,
            "total_payable": row.total_payable, "amount_due": row.amount_due, "delay_days": row.delay_days, "updated_at": now,
        }
        for row in calculated_df.itertuples(index=False)
    ]
    for i in range(0, len(all_params), batch_size):
        batch = all_params[i:i + batch_size]
        execute_query(sql, batch)

    return True


async def function1(data: dict) -> dict:
    app_id_list = data.get("app_id_list", [])
    try:
        # Step 1. Fetch active application IDs (either from request or DB)
        if app_id_list:
            all_active_cases = app_id_list
        else:
            all_app_query = """
                select id from consumer_application where state in ('disbursed', 'nach registered')
                  and (deleted_at is null and is_deleted is null and merchant_id not in (73, 9421) and lender_id not in (18, 21))
                  and id not in (
                      select application_id
                      from payment_transactions
                      where payment_for in (
                          'Foreclosure', 'foreclosure_bpi', 'foreclosure_interest',
                          'foreclosure_principal', 'foreclosure_bounce',
                          'foreclosure_penal', 'foreclosure_over_payment'
                      )
                        and is_deleted_at is null
                        and deleted_at is null
                        and refunded_at is null
                  )
                  and id not in (
                      select application_id 
                      from online_payments 
                      where foreclosure_date is not null 
                        and foreclosure_date != '0' 
                        and foreclosure_date != '' 
                        and status = 'pending' 
                        and created_at >= NOW() - INTERVAL 24 HOUR
                  )
            """
            all_app_df = execute_query(all_app_query)
            all_active_cases = all_app_df['id'].tolist()

        if not all_active_cases:
            return {"status": "FAILED", "message": "No active cases in disbursed/nach registered status."}

        # Step 2: Fetch EMI payments data for above applications
        params = {"application_ids": tuple(all_active_cases)}
        ep_df = execute_query(bounce_penal_query, params=params)
        if ep_df.empty:
            return {"status": "FAILED", "message": "No EMI payment records found."}

        # Step 3: Calculate bounce & penalty charges
        calculated_df = (ep_df.groupby("application_id", group_keys=False)
                         .apply(lambda g: calculate_bounce_penal_function(g)).reset_index(drop=True))

        if not app_id_list: # if script running via cron, then share csv log file - for testing purpose
            now_str = datetime.now(local_tz).strftime('%Y-%m-%d_%H-%M-%S')
            for f in os.listdir(BOUNCE_PENAL_DIR):
                file_path = os.path.join(BOUNCE_PENAL_DIR, f)
                if os.path.isfile(file_path):
                    os.remove(file_path)
            bounce_penal_file = os.path.join(BOUNCE_PENAL_DIR, f"bounce_penal_log_file_{now_str}.csv")
            calculated_df_save = calculated_df[['emi_payment_id','application_id','due_date','emi_amount','bounce_charges',
                                                'delay_penalty','lender_id']]
            calculated_df_save.to_csv(bounce_penal_file, index=False)
            zip_path = bounce_penal_file.replace('.csv', f'_compressed.zip')
            zip_file(bounce_penal_file, zip_path)

            msg = f"Bounce Penal Logs {now_str}"
            email_list = ["rudraksha.vaity@creditfair.in"]
            mail_service.send_mail_with_attachment(email_list,f"Bounce Penal Log File\n {msg}",msg,zip_path)

        # Step 4: Update EMI payments table in batches
        update_emi_payment_details(calculated_df, batch_size=3000)
        summary_msg = f"Bounce, delay penalty & payment_status updated successfully for {app_id_list} cases." if app_id_list else f"Bounce & penalty updated successfully for {len(all_active_cases)} applications."
        return {"status": "SUCCESS", "message": summary_msg}

    except Exception as e:
        return {"status": "ERROR", "message": str(e)}


# if __name__ == '__main__':
#     data = {"app_id_list" : [14448, 1875, 12903, 1811, 6767]}
#     # data = {"app_id_list" : []}
#     result = asyncio.run(function1(data))
#     print(result)