import pandas as pd
from datetime import datetime
from scripts.mysql_queries import closed_cancelled_settled_check
from scripts.foreclosure_breakup import function1 as foreclosure_func
from utils import execute_query, insert_payment_transaction, get_cloudwatch_logger
logger = get_cloudwatch_logger(stream_name="settlement_reconcile")
import pytz, asyncio
local_tz = pytz.timezone("Asia/Kolkata")

async def function1(data):
    now = datetime.now(local_tz).strftime('%Y-%m-%d %H:%M:%S')
    data['request_time'] = now
    application_id = data.get('application_id')
    amount = data.get('amount')
    request = data
    logger.send_log_event(f"INFO: In settlement_reconcile function. Logging request: {request}")

    # Check if case is closed, cancelled, settled, foreclosed or prepartpayment done.
    validate_app_id_df = execute_query(closed_cancelled_settled_check, params = {"application_ids": tuple([application_id])})
    if isinstance(validate_app_id_df, str):
        return {"status": "ERROR", "message": validate_app_id_df}
    if isinstance(validate_app_id_df, pd.DataFrame) and validate_app_id_df.empty:
        return {"status": "FAILED", "message": f"No data found for Application_id: {application_id}"}

    
    row = validate_app_id_df.iloc[0].fillna(0).to_dict() # Convert the single-row dataframe to a dictionary

    if row['app_status'] == 'closed':
        return {"status": "FAILED", "message": f"Application_id: {application_id} is closed. Settlement entry not allowed."}
    if row['app_status'] == 'cancelled':
        return {"status": "FAILED", "message": f"Application_id: {application_id} is cancelled. Settlement entry not allowed."}
    if row['foreclosed'] > 0:
        return {"status": "FAILED", "message": f"Application_id: {application_id} is foreclosed. Settlement entry not allowed."}
    if row['settled'] > 0:
        return {"status": "FAILED", "message": f"Application_id: {application_id} is already settled. Settlement entry not allowed."}
    if row['pre_part_payment'] > 0:
        return {"status": "FAILED", "message": f"Application_id: {application_id} has pre part payment entry. Settlement entry not allowed."}

    if amount <= 0:
        return {"status": "FAILED", "message": "Settlement amount should be greater than 0"}


    # Call foreclosure breakup API & get POS, interest OS, Bounce, penal etc values
    foreclosure_response = await foreclosure_func(data = {"application_id": application_id})
    if foreclosure_response.get('status') == 'Success':
        foreclosure_msg = foreclosure_response['message']
        df_reconciled = pd.DataFrame(foreclosure_response.get("data", []))
    else:
        return {"status": "ERROR", "message": foreclosure_response.get('message', 'Unknown error')}

    principal_outstanding = foreclosure_msg.get('Principal due')
    interest_outstanding = foreclosure_msg.get('Interest Outstanding')
    bounce_outstanding = foreclosure_msg.get('Bounce charges due')
    penal_outstanding = foreclosure_msg.get('Penal charges due')
    total_loan_outstanding = principal_outstanding + interest_outstanding + bounce_outstanding + penal_outstanding
    logger.send_log_event(f"INFO: Total Loan Outstanding = {total_loan_outstanding}, Principal Outstanding = {principal_outstanding}, \
                Interest Outstanding = {interest_outstanding}, Bounce Outstanding = {bounce_outstanding}, Penal Outstanding: {penal_outstanding}")


    if amount >= total_loan_outstanding:
        return {"status": "ERROR", "message": "Settlement amount >= Total loan outstanding. Please add this amount as normal payment."}


    # Calculate settlement & Write off amounts
    original_settlement_amount = amount  # Keep for reference

    # allocate settlement amount to principal first
    principal_settled = min(amount, principal_outstanding)
    amount -= principal_settled
    principal_wo = principal_outstanding - principal_settled

    # then Interest
    interest_settled = min(amount, interest_outstanding)
    amount -= interest_settled
    interest_wo = interest_outstanding - interest_settled

    # then Bounce Charges
    bounce_settled = min(amount, bounce_outstanding)
    amount -= bounce_settled
    bounce_wo = bounce_outstanding - bounce_settled

    # then Penal Charges
    penal_settled = min(amount, penal_outstanding)
    amount -= penal_settled
    penal_wo = penal_outstanding - penal_settled

    # pass all settlement & written - off records in payment_transactions table
    def safe_insert(amount, label, data):
        if round(amount) >= 1:
            data["amount"] = amount
            data["payment_for"] = label
            insert_payment_transaction(data)

    safe_insert(principal_settled, "settled_principal", data)
    safe_insert(interest_settled, "settled_interest", data)
    safe_insert(bounce_settled, "settled_bounce", data)
    safe_insert(penal_settled, "settled_penal", data)
    safe_insert(principal_wo, "written_off_principal", data)
    safe_insert(interest_wo, "written_off_interest", data)
    safe_insert(bounce_wo, "waived_off_bounce", data)
    safe_insert(penal_wo, "waived_off_penal", data)


   # Add unpaid bounce & penal amount as in existing charges_waived amount for respective emi_payment_id and update waive_off_logs accordingly
    now = datetime.now(local_tz).strftime('%Y-%m-%d %H:%M:%S')
    updated_count = 0
    logs_created = 0

    for row in df_reconciled.itertuples():
        charges_to_waive = float(row.charges_to_be_waived)
        total_paid = float(row.total_amount_paid)

        # Update emi_payments
        update_ep_query = f"""
            update emi_payments
            set
                charges_waived = charges_waived + GREATEST(0, FLOOR({charges_to_waive})),
                total_payable = (pre_emi_amount + emi_amount + bounce_charges + delay_penalty) - charges_waived,
                amount_due = total_payable - GREATEST(0, FLOOR({total_paid})),
                updated_at = '{now}'
            where application_id = {application_id} AND id = {row.emi_payment_id};
        """
        execute_query(update_ep_query)
        updated_count += 1

        # Conditionally insert into waive_off_logs
        if charges_to_waive > 0:
            insert_waive_log_query = f"""
                insert into waive_off_logs (
                    emi_payment_id, waived_by, status, created_at, updated_at, amount, application_id
                ) VALUES (
                    {row.emi_payment_id}, 107, 'Active', '{now}', '{now}', {charges_to_waive}, {application_id}
                );
            """
            execute_query(insert_waive_log_query)
            logs_created += 1

    if updated_count > 0 or logs_created > 0:
        logger.send_log_event(f"INFO: Updated emi_payments for {updated_count} rows.")
        logger.send_log_event(f"INFO: Inserted waive_off_logs for {logs_created} rows.")


    # Update TOS, POS, Emi due in consumer_application_element
    update_cae_query = f"""
        update consumer_application_element
        set
            amount_over_due = 0,
            total_amount_due = 0,
            principal_due = 0,
            total_principal_due = 0,
            emi_pending = 0,
            updated_at = '{now}'
        where application_id = {application_id};
    """
    execute_query(update_cae_query)
    logger.send_log_event(f"INFO: Updated TOS, POS, Emi due in consumer_application_element table for application_id: {application_id}.")

    resp_data = {"total_loan_outstanding": float(total_loan_outstanding),
        "principal_outstanding": float(principal_outstanding),
        "interest_outstanding": float(interest_outstanding),
        "bounce_outstanding": float(bounce_outstanding),
        "penal_outstanding": float(penal_outstanding)}

    response = {
        "status": "Success",
        "message": "Settlement entry created.",
        "data": resp_data,
        "Response time":now
    }
    return response   



# if __name__ == "__main__":
#     data = {
#         "application_id": 13394,
#         "payment_date": "01-10-2025",
#         "amount": 1
#     }
#     print(asyncio.run(function1(data)))