import re
import pandas as pd
import warnings
import os, traceback
from dotenv import load_dotenv
from datetime import datetime
from core_utilities import mail_service
from core_utilities import cf_encrypt_decrypt
import pytz, base64, hashlib, hmac
from Crypto.Cipher import AES
from Crypto.Util.Padding import unpad
import requests,time
from datetime import date
from utils import get_cloudwatch_logger
warnings.filterwarnings("ignore")
from concurrent.futures import ThreadPoolExecutor, as_completed
from utils import send_communication_logs
from utils import execute_query

load_dotenv()
request_time = datetime.now()

stream_name='overdue_Reminder'


logger = get_cloudwatch_logger(stream_name=stream_name)
PaymentClientID = os.getenv("Payment_Link_Client_Id")
PaymentSecertID = os.getenv("Payment_Link_Client_Secret")
Quicksmart_api_key = os.getenv("Quicksmart_api_key")
AdminPaymentUrl = os.getenv("Admin_Payment_URL")
ENCRYPT_DECRYPT_KEY = os.getenv('CF_ENCRYPT_DECRYPT_KEY')


def getpaymentlink(
    lender_id, total_amount, application_id, consumer_email, emi_date, purpose
):
    payment_url = AdminPaymentUrl

    payload = {
        "amount": total_amount,
        "email": consumer_email,
        "lender_id": lender_id,
        "id": application_id,
        "purpose": purpose,
        "emi_date": emi_date,
    }
    headers = {
        "Accept": "application/json",
        "X-Client-Id": PaymentClientID,
        "X-Client-Secret": PaymentSecertID,
        "Content-Type": "application/json",
    }

    try:
        response = requests.post(payment_url, headers=headers, json=payload, timeout=40)

        if response.status_code != 200:
            return {
                "error": f"Failed to get payment link. HTTP {response.status_code}",
                "details": response.text,
            }

        return response.text

    except Exception as e:
        return {"error": f"Exception occurred: {str(e)}"}


# --- Create and send SMS ---
def build_overdue_sms(applicant_name, delay_days, total_due, emi_amount, fee_amount, payment_link,consumer_mobile,pending_emi_count,amount_due_true,moratorium_amount,application_id):
    emi_amount = emi_amount + moratorium_amount
    if  amount_due_true and pending_emi_count>1:     
        templateid= 1307166478154391555
        message = (
            f"Dear {applicant_name}, You are {delay_days} days late on your payment of Rs.{total_due} due towards."
            f"EMI of Rs.{emi_amount} and fees of Rs.{fee_amount} Pay now on {payment_link}"
            f" We might have to pursue legal action if we don't hear from you. Call us on 91-8928769161 if you need any help or have already paid. Regards, Team Credit Fair"
        )

    else:
        templateid = 1307161744590392816
        message = (
            f"Dear {applicant_name}, You are {delay_days} days late on your payment of Rs.{total_due} due towards. "
            f"EMI of Rs {emi_amount} and fees of Rs {fee_amount} Pay now on {payment_link} "
            f"Late payments can spoil your credit history and you might not be able to get loans "
            f"in the future from any bank or finance company. Call us on +91-8928769161 if you need "
            f"any help or have already paid. Regards, Team Credit Fair"
        )

    # Hardcoded mobile number
    # consumer_mobile = 8779245135
    quicksmart_api_url = "https://api.telsp.in/pushapi/sendbulkmsg"
    params = {
        "username": "CreditFairTrNu",
        "dest": consumer_mobile,
        "apikey": Quicksmart_api_key,
        "signature": "KMCRED",
        "msgtype": "PM",
        "msgtxt": message,
        "templateid": templateid,
        "entityid": 1301159108481756475,
    }
    response = requests.get(quicksmart_api_url, params=params)
    
    prepared = requests.Request('GET', quicksmart_api_url, params=params).prepare()
    full_url = prepared.url

    Sms_response_logs = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: SMS Response for application id {application_id}: {response} {response.text}")

    sms_log_data = (
        int('91'+str(consumer_mobile)),
        message,
        full_url,
        response.text,
        "quicksmart",
        application_id,
        "application_id",
        107
    )
    send_communication_logs(*sms_log_data)
    return response


# --- Create and send Email ---
def send_mail_ses(lender_id, application_id, applicant_name, consumer_email,
                  delay_days, total_amount, emi_amount, charges, payment_link_overdue,pending_emi_count,emi_summary_df,amount_due_true,loan_application_no,moratorium_amount):
    bank_details = {
        3: {
            "Beneficiary account number": "918020009293418",
            "Beneficiary name": "Avanse Financial Services Ltd",
            "IFSC code": "UTIB0000028",
            "Bank Name": "Axis Bank",
        },
        7: {
            "Beneficiary account number": "000705047644",
            "Beneficiary name": "Transactree Technologies Private Limited",
            "IFSC code": "ICIC0000007",
            "Bank Name": "ICICI BANK LTD",
        },
        4: {
            "Beneficiary account number": "200999509663",
            "Beneficiary name": "Aditya Birla Finance LTD",
            "IFSC code": "INDB0000001",
            "Bank Name": "INDUSIND BANK LTD",
        },
        11: {
            'Beneficiary account number': '409002234039',
            'Beneficiary name': 'K M GLOBAL P2P FINANCE PRIVATE LIMITED COLLECTION ACCOUNT',
            'IFSC code': 'RATN0000088',
            'Bank Name': 'RBL Bank'
        },
        2: {
            "Beneficiary account number": "10245910063",
            "Beneficiary name": "BLACKSOIL CAPITAL PRIVATE LIMITED",
            "IFSC code": "IDFB0040101",
            "Bank Name": "IDFC FIRST Bank",
        },
        20: {
            "Beneficiary account number": "10245910063",
            "Beneficiary name": "BLACKSOIL CAPITAL PRIVATE LIMITED",
            "IFSC code": "IDFB0040101",
            "Bank Name": "IDFC FIRST Bank",
        }
    }.get(lender_id, {
        'Beneficiary account number': '10245910063',
        'Beneficiary name': 'BLACKSOIL CAPITAL PRIVATE LIMITED',
        'IFSC code': 'IDFB0040101',
        'Bank Name': 'IDFC FIRST Bank'
    })
    
    
    subject = f"Your payment of Rs {total_amount} is overdue"
    
        
    emi_summary_html = ""
    if emi_summary_df is not None:
        emi_summary_df_formatted = emi_summary_df.copy()

        # 1. Format the currency columns
        currency_cols = [
            "Due Amount",
            "Bounce Charges",
            "Late Fees",
            "Payable Amount",
            "Amount Paid",
        ]
        
        for col in currency_cols:
            if col in emi_summary_df_formatted.columns:
                emi_summary_df_formatted[col] = emi_summary_df_formatted[col].apply(
                    lambda x: f"₹{x:,.0f}"
                )

        emi_summary_df_formatted = emi_summary_df_formatted[
            [
                "Due Date",
                "Due Amount",
                "Bounce Charges",
                "Late Fees",
                "Amount Paid",
                "Payable Amount",
            ]
        ]

        emi_summary_html = emi_summary_df_formatted.to_html(index=False, escape=False)

        emi_summary_html = (
            "<style>"
            "table { border-collapse: collapse; width: 100%; border: none; }"
            "th, td { border: none; padding: 4px 8px; text-align: left; }"
            "</style>" + emi_summary_html
        )
        
    if moratorium_amount>0:
        moratorium_text = f"and moratorium interest amount Rs {moratorium_amount}"
    else:
        moratorium_text = ""
        
    
    if pending_emi_count>1 and amount_due_true:
        # Multiple pending EMIs
        msg = (
        f"<html>"
        f"<body>"
        f"<p><b>Dear {applicant_name}</b>,</p>"
        f"<p>You are <b> {delay_days:.0f} days </b> late on your payment</p>"
        f"<p><a href='{payment_link_overdue}'>Click here to pay</a></p>"
        f"<p>Here is the break up of the payments due:</p>"
        f"{emi_summary_html}</br>"
        f"<p>Please pay using the link provided or transfer a total of <b>Rs {total_amount:,.0f}</b> immediately "
        f"that includes Installment of Rs {emi_amount:,.0f} and fees of Rs {charges:,.0f} {moratorium_text} to our Bank account - details as follows: </p>"
        f"<ul style='list-style-type:none; padding:0; margin:0;'>"
        f"<li>Beneficiary account number: <b>{bank_details['Beneficiary account number']}</b></li>"
        f"<li>Beneficiary name: <b>{bank_details['Beneficiary name']}</b></li>"
        f"<li>IFSC code: <b>{bank_details['IFSC code']}</b></li>"
        f"<li>Bank Name: <b>{bank_details['Bank Name']}</b></li>"
        f"<p>We might have to pursue legal action if we don't hear from you. Call us on +91-8928769161 or email us at payments@creditfair.in if you face any issues while making the payment.</p>"
        f"<p>*Please ignore this message if you have already paid.</p>"
        f"</body>"
        f"</html>"
    )
     
    else:   
        # Single pending EMI

        msg = (
        f"<html>"
        f"<body>"
        f"<p><b>Dear {applicant_name},</b></p>"
        f"<p>You are <b>{delay_days:.0f}</b> days late on your payment.</p>"
        f"<p><a href='{payment_link_overdue}'>Click here to pay</a></p>"
        f"Please pay using above link or send a total of <b>Rs {total_amount:,.0f}</b> immediately that includes Installment of Rs <b>{emi_amount:,.0f}</b> and fees of Rs <b>{charges:,.0f} </b> {moratorium_text} to our Bank account - details as follows:</p>"
        f"<ul style='list-style-type:none; padding:0; margin:0;'>"
        f"<li>Beneficiary account number: <b>{bank_details['Beneficiary account number']}</b></li>"
        f"<li>Beneficiary name: <b>{bank_details['Beneficiary name']}</b></li>"
        f"<li>IFSC code: <b>{bank_details['IFSC code']}</b></li>"
        f"<li>Bank Name: <b>{bank_details['Bank Name']}</b></li>"
        f"<p>Late payments can spoil your credit history and you might not be able to get loans in the future from any finance company or bank unless you get in touch with us and make a payment. Call us on +91-8928769161 or email us at payments@creditfair.in if you face any issues while making the payment.</p>"
        f"<p>*Please ignore this message if you have already paid.</p>"
        f"</body>"
        f"</html>"        
    )
        
    # email harcoded
    # consumer_email = "vedant.jadhav@creditfair.in"
    result = mail_service.send_mail([consumer_email], msg, subject)
    
    email_response_logs = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Email Response: Email Sent for {application_id}:{result}")

    return result


# # --- Database Connection ---
# def get_db_connection():
#     return mysql.connector.connect(
#         host=os.getenv("MYSQL_HOST_READ"),
#         database=os.getenv("MYSQL_DB"),
#         user=os.getenv("MYSQL_USER"),
#         password=os.getenv("MYSQL_PASSWORD"),
#     )


# --- Per-application threaded processing function ---
def process_application(application_id):
    result = {
        "application_id": application_id,
        "payment_link": None,
        "mobile_number":None,
        "email_address":None,
        "email_sent": False,
        "sms_sent": False,
        "error": None,
    }
    errors = []
    try:
        
        
        # connection = get_db_connection()
        # Check if no overdue is pending
        # Below Code Handled from PHP Admin Side
        # is_paymentmade = """
        
        #     select consumer_application.id application_id, state app_status,foreclosed,settled,pre_part_payment
        #     from consumer_application
        #     left join (
        #         select application_id,
        #         max(case when payment_for in ('Forclouser','Foreclosed','Foreclosure') then 1 else 0 end) foreclosed,
        #         max(case when (payment_for in ('Write-off','settled_principal','settled_interest','settled_penal','settled_bounce',
        #             'written_off_principal','written_off_interest','written_off_bounce','written_off_penal')) then 1 else 0 end) settled,
        #         max(case when payment_for = 'pre_part_payment' then 1 else 0 end) pre_part_payment
        #         from payment_transactions
        #         where (deleted_at is null and is_deleted_at is null and refunded_at is null)
        #         group by 1
        #     )as payment_transactions_cte on payment_transactions_cte.application_id = consumer_application.id
        #     where consumer_application.id IN (%s)
        
        # """

        # status_df = pd.read_sql(is_paymentmade, connection, params=(application_id,))
        # # print(status_df)

        # if not status_df.empty:
        #     app_status = status_df.loc[0, "app_status"]
        #     is_foreclosed = status_df.loc[0, "foreclosed"]
        #     is_settled = status_df.loc[0, "settled"]

        #     # Define a list of rules: (Condition to check, Reason to log)
        #     exclusion_rules = [
        #         (is_foreclosed == 1, "is_foreclosed"),
        #         (is_settled == 1, "is_settled"),
        #         (app_status in ["closed", "cancelled"], f"app_status={app_status}")
        #     ]

        #     exclusion_reasons = [reason for condition, reason in exclusion_rules if condition]

        #     if exclusion_reasons:
        #         reasons_str = ", ".join(exclusion_reasons)
        #         result["error"] = f"Reminder stopped for application {application_id}. Reasons: {reasons_str}"   
        #         print(f"Reminder stopped for application {application_id}. Reasons: {reasons_str}")
                
        #         logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Excluded Id: Reminder not sent for Application ID {application_id} due to case is marked for {reasons_str}")
                
        #         return result 
        # Get the values from query
        
        Amount_query = """
            WITH payment_transactions_cte AS (
                SELECT application_id, emi_payment_id, payment_for, payment_date, amount
                FROM payment_transactions
                WHERE (deleted_at IS NULL AND is_deleted_at IS NULL AND refunded_at IS NULL)
                AND (payment_for IN ('EMI', 'Pre-EMI'))
                AND application_id IN :application_ids
            )
            , emi_payments_cte AS (
                SELECT id, application_id, emi_date,
                LEAST(emi_amount, (emi_amount + bounce_charges + delay_penalty) - charges_waived) - ifnull(moratorium_amount,0) AS emi_amount_expected,
                ROUND((pre_emi_amount + emi_amount + bounce_charges + delay_penalty) - charges_waived) - ifnull(moratorium_amount,0)  AS total_amount_expected,
                delay_days,
                ifnull(moratorium_amount,0) as moratorium_amount,payment_status
                FROM emi_payments
                WHERE (DATE(emi_date) < CURDATE())
                AND application_id IN :application_ids
            )
            , emi_cte1 AS (
                SELECT *, IF(total_amount_received < total_amount_expected, 1, 0) AS emi_overdue_count
                FROM (
                    SELECT
                        emi_payments_cte.application_id,
                        emi_payments_cte.emi_date,
                        emi_payments_cte.emi_amount_expected,
                        IF(
                            IFNULL(SUM(payment_transactions_cte.amount), 0) > emi_payments_cte.emi_amount_expected,
                            emi_payments_cte.emi_amount_expected,
                            IFNULL(SUM(payment_transactions_cte.amount), 0)
                        ) AS emi_amount_received, -- if more amount received then expected then show expected amount, else show received amount
                        emi_payments_cte.total_amount_expected,
                        IFNULL(SUM(payment_transactions_cte.amount), 0) AS total_amount_received,
                        delay_days,moratorium_amount,payment_status
                    FROM emi_payments_cte
                    LEFT JOIN payment_transactions_cte ON payment_transactions_cte.application_id = emi_payments_cte.application_id
                    AND payment_transactions_cte.emi_payment_id = emi_payments_cte.id
                    GROUP BY 1, 2, 3, emi_payments_cte.total_amount_expected, delay_days
                ) AS yy
            )
            SELECT
                application_id,
                GREATEST(SUM(total_amount_expected) - SUM(total_amount_received), 0) AS total_amount_due,
                GREATEST(SUM(emi_amount_expected) - SUM(emi_amount_received), 0) AS pending_installment,
                GREATEST((SUM(total_amount_expected) - SUM(total_amount_received)) - (SUM(emi_amount_expected) - SUM(emi_amount_received)), 0) AS pending_charges,
                MAX(CASE WHEN payment_status <> 'success' then delay_days else 0 end) AS delay_days,
                SUM(emi_overdue_count) AS pending_emi_count,
                SUM(moratorium_amount) AS moratorium_amount
            FROM emi_cte1
            GROUP BY 1
        """

        df = execute_query(Amount_query, params={"application_ids": tuple([application_id])})
        # df = pd.read_sql(Amount_query, connection, params={"application_ids": [application_id]})
        total_amount = int(df.loc[0, "total_amount_due"])
        emi_amount = df.loc[0, "pending_installment"]
        charges = df.loc[0, "pending_charges"]
        delay_days = df.loc[0, "delay_days"]
        pending_emi_count = df.loc[0, "pending_emi_count"]
        moratorium_amount = int(df.loc[0, "moratorium_amount"])
        total_amount = total_amount + moratorium_amount         
        if total_amount <= 0:
            print(
                f"This application has total amount as  0 hence reminder should be stopped for application{application_id}"
            )
               
            total_amount = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Total Overdue Due: For {application_id} the total overdue amount is <=0 hence reminder should be stopped for application id ")
            
            return result
        
        # CHeck for if any amount due value is grrater than 299
        amount_due_query = """
            SELECT EXISTS (
                SELECT 1 
                FROM emi_payments
                WHERE application_id = :application_ids
                AND emi_date < CURRENT_DATE
                AND amount_due > 299
            ) AS is_amount_due_gt_299;
        """
        amount_due_flag =  execute_query(amount_due_query, params={"application_ids": tuple([application_id])})
        # amount_due_flag = pd.read_sql(amount_due_query, connection, params={"application_ids": tuple([application_id])})
        amount_due_true = amount_due_flag.iloc[0, 0]
        
        
        
        loan_application_number = """ select loan_application_no from pro_forma_invoice where application_id = :application_ids"""
        loan_app_df =  execute_query(loan_application_number, params={"application_ids": tuple([application_id])})
        # loan_app_df = pd.read_sql(loan_application_number, connection, params=(application_id,))
        loan_application_no = loan_app_df.loc[0, "loan_application_no"]
        loan_application_no = loan_application_no.upper() 
        emi_summary_df = None
        
        # THis is more mutiple payments if payment is more than 1 
        if pending_emi_count > 1 and amount_due_true:
            # FOr mutiple payment
            if df.empty:
                result["error"] = f"No pending dues for Application {application_id}"
                # connection.close()
                return result

            # This below query geyt the all the summary out of table
            pending_query = """
                SELECT *,total_payable - amount_due AS amount_paid,emi_amount-ifnull(moratorium_amount,0)as moratorium_emi_amount
                FROM emi_payments
                WHERE application_id = :application_ids
                AND emi_date < CURRENT_DATE
                AND (payment_status = 'pending' OR payment_status = 'partial')
            """
            # pending_df = pd.read_sql(
            #     pending_query, connection, params=(application_id,)
            # )
            
            pending_df =  execute_query(pending_query, params={"application_ids": tuple([application_id])})

            if not pending_df.empty:
                emi_summary_df = pd.DataFrame({
                    "Due Date": pending_df["emi_date"],
                    "Due Amount": pending_df["moratorium_emi_amount"],
                    "Bounce Charges": pending_df["bounce_charges"],
                    "Late Fees": pending_df["delay_penalty"],
                    "Payable Amount": pending_df["amount_due"],
                    "Amount Paid": pending_df["amount_paid"]    
                })
                        
            # print(emi_summary_df)
                  
        # Fetch consumer details
        consumer_query = """SELECT lender_id, name, email, mobile_number,merchant_id FROM consumer_application WHERE id = :application_ids"""
        # df1 = pd.read_sql(consumer_query, connection, params=(application_id,))
        df1 = execute_query(consumer_query, params={"application_ids": (application_id,)})
        applicant_name = df1.loc[0, "name"]        
        email_encrypted = df1.loc[0, "email"]
        mobile_number_encrypted  = df1.loc[0, "mobile_number"]

        consumer_email = cf_encrypt_decrypt.wall_decrypt(email_encrypted, key=ENCRYPT_DECRYPT_KEY)
        if consumer_email is None:
            consumer_email = email_encrypted #use original if decryption fails
            
        result["email_address"] = consumer_email
        
        # Check if mobile number is valid else decrypt
        consumer_mobile = cf_encrypt_decrypt.wall_decrypt(mobile_number_encrypted, key=ENCRYPT_DECRYPT_KEY)
        
        if consumer_mobile is None:
            consumer_mobile = mobile_number_encrypted #use original if decryption fails
            
        result["mobile_number"] = consumer_mobile
        lender_id = int(df1.loc[0, "lender_id"])
        # merchant_id = int(df1.loc[0, "merchant_id"])
        # Handled From PHP Admin Side
        # excluded_merchants = [73,6418,6517,6598,6746,6754,6865,8378,9421,6600]
        # if merchant_id in excluded_merchants:
        #     result["error"] = f"Merchant {merchant_id} is excluded from reminders"
        #     merchants_excluded = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: OverDue Excluded: For {application_id} Merchant {merchant_id} is excluded from reminders")
        #     print(result)
        #     return result

        emi_date = date.today().isoformat()

        # Get the payment Link from admin panel
        payment_link_overdue = getpaymentlink(
            lender_id,
            total_amount,
            application_id,
            consumer_email,
            purpose="emi & penalty",
            emi_date=emi_date,
        )

        if "error" in payment_link_overdue:
            errors.append(payment_link_overdue["error"])
            print(errors)

        else:
            result["payment_link"] = "Payment link Received"
            # Send Email
            try:
                email_status = send_mail_ses(
                    lender_id=lender_id,
                    application_id=application_id,
                    applicant_name=applicant_name,
                    consumer_email=consumer_email,
                    delay_days=delay_days,
                    total_amount=total_amount,
                    emi_amount=emi_amount,
                    charges=charges,
                    payment_link_overdue=payment_link_overdue,
                    pending_emi_count = pending_emi_count,
                    emi_summary_df = emi_summary_df,
                    amount_due_true=amount_due_true,
                    loan_application_no=loan_application_no,
                    moratorium_amount=moratorium_amount
                )
                if email_status:
                    result["email_sent"] = True
            except Exception as e:
                errors.append(f"Email error: {str(e)}")

            # Send SMS
            try:
                sms_status = build_overdue_sms(
                    applicant_name=applicant_name,
                    delay_days=int(delay_days),
                    total_due=int(total_amount),
                    emi_amount=int(emi_amount),
                    fee_amount=int(charges),
                    consumer_mobile=consumer_mobile,
                    payment_link=payment_link_overdue,
                    pending_emi_count=pending_emi_count,
                    amount_due_true=amount_due_true,
                    moratorium_amount=moratorium_amount,
                    application_id=application_id
                )
                if sms_status:
                    result["sms_sent"] = True
            except Exception as e:
                errors.append(f"SMS error: {str(e)}")

        if errors:
            result["error"] = " | ".join(errors)

        # connection.close()

    except Exception as e:
        result["error"] = str(e)
        
    per_application_logs = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Application Details: {result}")
    return result

#  --- Main API function fetching data and  calling email/sms functions in parallel ---
def overdue_main(application_ids, collectionEmail):
    application_ids = list(set(application_ids))
    results = []
    failed_ids = []
    sms_failed_count = 0
    email_failed_count = 0
    max_workers = min(20, len(application_ids))  # Number of application to be processed

    with ThreadPoolExecutor(max_workers=max_workers) as executor:
        future_to_appid = {
            executor.submit(process_application, app_id): app_id
            for app_id in application_ids
        }
        for future in as_completed(future_to_appid):
            app_id = future_to_appid[future]
            try:
                result = future.result()
                results.append(result)
                
                # Count SMS / Email failures
                if not result.get("sms_sent", False):
                    sms_failed_count += 1
                if not result.get("email_sent", False):
                    email_failed_count += 1
                    
                # Overall failed IDs
                if result.get("error") or not result.get("email_sent", False)  or not result.get("sms_sent", False):
                    failed_ids.append(str(app_id))
            except Exception as exc:
                failed_ids.append(str(app_id))
                sms_failed_count += 1
                email_failed_count += 1
                results.append(
                    {
                        "application_id": app_id,
                        "email_sent": False,
                        "sms_sent": False,
                        "error": str(exc),
                    }
                )

    all_statuses_list = []
    for res in results:
        app_id = res.get("application_id")
        sms_status = "sent" if res.get("sms_sent") else "not sent"
        email_status = "sent" if res.get("email_sent") else "not sent"
        get_mobile_no = res.get("mobile_number") or "not available"
        get_email_add = res.get("email_address") or "not available"
        
        all_statuses_list.append({"Application_ID": app_id, "Mobile_number":get_mobile_no, "SMS_status": sms_status,"Email_Address":get_email_add, "Email_status":email_status})

    _send_status_report(all_statuses_list, collectionEmail)

    response = {
        "message": "Overdue reminder",
        "response": {
            "total_application_id": len(application_ids),
            "failed": len(set(failed_ids)),
            "failed_application_ids": list(set(failed_ids)),
            "sms_failed_count": sms_failed_count,
            "email_failed_count": email_failed_count,
        },
    }
    Batch_Log = logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Batch Completed Details: {response}")
    return response


def _send_status_report(all_statuses_list, collectionEmail):
    """
    Creates an Excel report of all application statuses and emails it.
    """
    # 1. Exit early if there's nothing to report
    if not all_statuses_list:
        print("No application statuses to report. Skipping email.")
        return

    print(f"Processing status report for {len(all_statuses_list)} applications...")
    attachment_path = None

    try:
        # 2. Create the Excel file with two columns
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        attachment_path = f"overdue_status_report_{timestamp}.xlsx"

        # Create DataFrame from the list of dictionaries
        df = pd.DataFrame(all_statuses_list, columns=["Application_ID","Mobile_number","SMS_status","Email_Address","Email_status"])
        df.to_excel(attachment_path, index=False)

        subject = "Overdue Reminder Sent Status"
        message_body = """
        <p>Dear Team,</p>
        <p>Please find the overdue reminder status file attached.</p>
        """

        # 4. Send the mail
        print(f"Sending status report to {collectionEmail}...")
        
        collectionEmails = [collectionEmail, "vedant.jadhav@creditfair.in","ashish.gupta@creditfair.in","abhishek.shetty@creditfair.in","sunil@creditfair.in","rudraksha.vaity@creditfair.in","suyog@creditfair.in","disha.doshi@creditfair.in"]
        for email in collectionEmails:
            print(f"Sending report to {email}...")
            mail_service.send_mail_with_attachment(
                customer_mail=[email], 
                message=message_body,
                subject=subject,
                attachment_path=attachment_path,
            )

    except Exception as e:
        print(f"CRITICAL: Failed to create or send status report: {e}")
        traceback.print_exc()

    finally:
        # 5. Clean up the temporary file
        if attachment_path and os.path.exists(attachment_path):
            os.remove(attachment_path)
            print(f"Cleaned up temporary file: {attachment_path}")