from utils import execute_query, get_homeville_data, get_cloudwatch_logger, log_exception
import asyncio, pytz, json, time
import pandas as pd
from datetime import datetime
from scripts.hm_update_data import hm_loan_schedule
local_tz = pytz.timezone("Asia/Kolkata")
now = datetime.now(local_tz).strftime('%Y-%m-%d %H:%M:%S')
logger = get_cloudwatch_logger(stream_name="homeville_rps_update")
# from pandasgui import show


def insert_emi_payments(input_df, batch_size=3000):
    sql = """
        insert into emi_payments (emi_date, pre_emi_amount, emi_amount, bounce_charges, delay_penalty, charges_waived, 
        created_at, updated_at, application_id, due_date, total_payable, amount_due, delay_days, payment_status)
        VALUES (:emi_date, :pre_emi_amount, :emi_amount, 0, 0, 0, :created_at, :updated_at, :application_id, :due_date, 
                :total_payable, :amount_due, 0, 
                case when :amount_due = 0 then 'success' 
                     when :amount_due > 0 and :amount_due < :total_payable then 'partial'
                     else 'pending' end)
    """
    all_params = [
        {
            "emi_date": row.repay_date, 
            "pre_emi_amount": row.repay_amount if row.repayment_type == 'PEMI' else 0,
            "emi_amount": row.repay_amount if row.repayment_type == 'EMI' else 0,
            "created_at": now, "updated_at": now, "application_id": row.application_id, "due_date": row.repay_date, 
            "total_payable": (row.repay_amount if row.repayment_type == 'PEMI' else 0) + (row.repay_amount if row.repayment_type == 'EMI' else 0),
            "amount_due": row.pending_emi_amount,
        }
        for row in input_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


def delete_emi_payments(input_df):
    ids = tuple(input_df["id"].tolist())
    sql = "delete from emi_payments where id in :ids"
    execute_query(sql, {"ids": ids})
    return True


def delete_rps_logs(input_df):
    ids = tuple(input_df["id"].tolist())
    sql = "delete from rps_logs where id in :ids"
    execute_query(sql, {"ids": ids})
    return True


def update_emi_payments(input_df, batch_size=3000):
    sql = """
        update emi_payments
        set pre_emi_amount = :pre_emi_amount, emi_amount = :emi_amount, bounce_charges = 0, delay_penalty = 0, total_payable = pre_emi_amount + emi_amount, amount_due = :amount_due, delay_days = 0, 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 = [
        {
            "pre_emi_amount": row.repay_amount if row.repayment_type == 'PEMI' else 0,
            "emi_amount": row.repay_amount if row.repayment_type == 'EMI' else 0,
            "id": row.id, "amount_due": row.pending_emi_amount, "updated_at": now, "application_id": row.application_id
        }
        for row in input_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


def insert_rps_logs(input_df, batch_size=3000):
    sql = """
        insert into rps_logs (application_id, installment_number, emi_date, due_date, bpi_interest, interest_comp, principal_comp, 
        emi_amount, total_payable, principal_outstanding, foreclosure_amount, loan_summary, deleted_at,
        created_at, updated_at)
        VALUES (:application_id, :installment_number, :emi_date, :due_date, :bpi_interest, :interest_comp, :principal_comp, :emi_amount, 
                :total_payable, :principal_outstanding, :foreclosure_amount, :loan_summary, :deleted_at, 
                :created_at, :updated_at)
    """
    all_params = [
        {   
            "application_id": row.application_id, "installment_number": row.installment_number, "emi_date": row.repay_date, 
            "due_date": row.repay_date, "bpi_interest": row.repay_amount if row.repayment_type == 'PEMI' else 0, 
            "interest_comp": row.repay_interest if row.repayment_type == 'EMI' else 0, "principal_comp": row.repay_principle,
            "emi_amount": row.repay_amount if row.repayment_type == 'EMI' else 0, "total_payable": row.repay_amount,
            "principal_outstanding": row.repay_os, "foreclosure_amount": row.repay_foreclosure, 
            "loan_summary": row.loan_summary, "deleted_at": None, "created_at": now, 
            "updated_at": now
        }
        for row in input_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


def update_rps_logs(input_df, batch_size=3000):
    sql = """
        update rps_logs
        set installment_number = :installment_number,bpi_interest = :bpi_interest, interest_comp = :interest_comp, principal_comp = :principal_comp, 
        emi_amount = :emi_amount, total_payable = bpi_interest + emi_amount, principal_outstanding = :principal_outstanding, foreclosure_amount = :foreclosure_amount,
        updated_at = :updated_at
        where application_id = :application_id AND id = :id
    """
    all_params = [
        {
            "installment_number": row.installment_number,"bpi_interest": row.repay_amount if row.repayment_type == 'PEMI' else 0, 
            "interest_comp": row.repay_interest if row.repayment_type == 'EMI' else 0, "principal_comp": row.repay_principle,
            "emi_amount": row.repay_amount if row.repayment_type == 'EMI' else 0, "principal_outstanding": row.repay_os, 
            "foreclosure_amount": row.repay_foreclosure,
            "id": row.id, "amount_due": row.pending_emi_amount, "updated_at": now, "application_id": row.application_id
        }
        for row in input_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


def update_payment_transactions(input_df, batch_size=3000):
    sql = """
        update payment_transactions 
            set amount = :amount, payment_date = :payment_date, updated_at = :updated_at, payment_reconciliation_date = :payment_date
        where application_id = :application_id and emi_payment_id = :id and payment_for = :payment_for and is_deleted_at is null
    """
    all_params = [
        {
            "amount": row.amount_paid, "payment_date": row.repaid_at, "updated_at": now, "payment_reconciliation_date": row.repaid_at,
            "id": row.id, "application_id": row.application_id, "payment_for": row.repayment_type
        }
        for row in input_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


def update_part_payment_transactions(input_df, batch_size=3000):
    sql = """
        update payment_transactions 
            set amount = :amount, updated_at = :updated_at
        where application_id = :application_id and emi_payment_id = :id and payment_for = :payment_for and payment_date = :payment_date
        and is_deleted_at is null
    """
    all_params = [
        {
            "amount": row.amount_paid, "payment_date": row.repaid_at, "updated_at": now,
            "id": row.id, "application_id": row.application_id, "payment_for": row.repayment_type
        }
        for row in input_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


def insert_payment_transaction(input_df, batch_size=3000):
    sql = f"""
        insert into payment_transactions (
            emi_payment_id, amount, mode, payment_date, status, created_at, updated_at, application_id, payment_for, 
            payment_reconciliation_date, bank_id
        ) values (
            :emi_payment_id, :amount, "LotusPay", :payment_date, "success", :created_at, :updated_at, :application_id, :payment_for,
            :payment_reconciliation_date, 31);
    """
    all_params = [
        {
            "emi_payment_id": row.id if row.repayment_type in ("PEMI", "EMI") else 0,
            "amount": row.amount_paid,
            "payment_date": row.repaid_at, "payment_reconciliation_date": row.repaid_at, "created_at": now, "updated_at": now,
            "application_id": row.application_id, 
            "payment_for": ("Pre-EMI" if row.repayment_type == "PEMI"
                           else "Foreclosure" if row.repayment_type == "foreclosure"
                           else "pre_part_payment" if row.repayment_type == "pre_part_payment"
                           else "EMI"
                           )
        }
        for row in input_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


def delete_payment_transactions(input_df, batch_size=3000):
    sql = """
        update payment_transactions 
            set is_deleted_at = :is_deleted_at, deleted_at = :is_deleted_at, deleted_by = :deleted_by
        where application_id = :application_id and emi_payment_id = :emi_payment_id and payment_for = :payment_for and is_deleted_at is null
    """
    all_params = [
        {
            "is_deleted_at": now, "deleted_by": 107,
            "emi_payment_id": row.emi_payment_id, "application_id": row.application_id, "payment_for": row.repayment_type
        }
        for row in input_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


def insert_foreclosure_breakup(input_df, batch_size=3000):
    sql = f"""
        insert into foreclosure_breakup (
            application_id, pre_emi_due, principal_due, interest_due, moratorium_due, bounce_due, penal_due, pf_due, advance_due, 
            foreclosure_amount, foreclosure_date, created_by, created_at, updated_at
        ) values (
            :application_id, 0, :principal_due, :interest_due, 0, 0, 0, 0, 0, :foreclosure_amount, :foreclosure_date, 107, :created_at,
            :updated_at);
    """
    all_params = [
        {
            "application_id": row.application_id, "principal_due": row.repay_principle, "interest_due": row.repay_interest,
            "foreclosure_amount": row.amount_paid, "foreclosure_date": row.repaid_at, "created_at": now, "updated_at": now
        }
        for row in input_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


def delete_foreclosure_breakup(input_df, batch_size=3000):
    sql = """
        update foreclosure_breakup 
            set deleted_at = :deleted_at
        where application_id = :application_id and deleted_at is null
    """
    all_params = [
        {
            "deleted_at": now, "application_id": row.application_id
        }
        for row in input_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


def update_foreclosure_breakup(input_df, batch_size=3000):
    sql = """
        update foreclosure_breakup 
            set principal_due = :principal_due, interest_due = :interest_due, foreclosure_amount = :foreclosure_amount, updated_at = :updated_at
        where application_id = :application_id and deleted_at is null
    """
    all_params = [
        {
            "principal_due": row.repay_principle, "interest_due": row.repay_interest, "updated_at": now, "foreclosure_amount": row.amount_paid,
            "application_id": row.application_id
        }
        for row in input_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



def get_hm_rps(app_id):

    # Fetch homeville case_id for given application_id
    hm_loans_fetch_query = """
        select homeville_loans.case_id
        from homeville_loans
        -- join consumer_application on consumer_application.id = homeville_loans.application_id 
        -- and consumer_application.deleted_at is null and consumer_application.is_deleted is null and consumer_application.merchant_id not in (73,9421)
        -- and consumer_application.state in ('disbursed','nach registered') and consumer_application.lender_id = 10
        where homeville_loans.application_id = :application_id
    """
    params = {'application_id':app_id}
    hm_loans_df = execute_query(hm_loans_fetch_query, params=params)
    if hm_loans_df.empty:
        return {"status": "Failed", "message": f"No case_id present in homville_loans table for application_id: {app_id}"}
    
    # Request RPS from homeville
    case_id = hm_loans_df['case_id'].iloc[0]
    rps_api_url = f"https://api-v2.bharathousingnetwork.com/api/v1/cases/{case_id}/repayment-schedule"
    rps_api_response = get_homeville_data(rps_api_url)
    if rps_api_response.get('status') != 'success':
        return rps_api_response
    rps_json = rps_api_response['data']['schedule']['user']
    # print(f"Homeville RPS fetched: {rps_json}")
    hm_rps_df = pd.DataFrame(rps_json)
    # hm_rps_df = pd.read_json("D:\\Python scripts\\cf-python-scripts\\testing_dir\\rps_normal.json")
    hm_rps_df['application_id'] = app_id
    hm_rps_df['repay_date'] = pd.to_datetime(hm_rps_df['repay_date']).dt.date
    hm_rps_df['repaid_at'] = pd.to_datetime(hm_rps_df['repaid_at']).dt.date
    cols = ['repay_amount', 'repay_interest', 'repay_principle', 'repay_os', 'interest_collected', 'principle_collected']
    hm_rps_df[cols] = hm_rps_df[cols].apply(pd.to_numeric, errors='coerce').fillna(0)
    hm_rps_df['amount_paid'] = hm_rps_df['interest_collected'] + hm_rps_df['principle_collected']
    if hm_rps_df['repayment_type'].iloc[0] == 'PEMI':
        hm_rps_df['installment_number'] = hm_rps_df.reset_index().index
    else:
        hm_rps_df['installment_number'] = hm_rps_df.reset_index().index + 1
    hm_rps_df.loc[hm_rps_df['repayment_type'] == 'part_payment', 'installment_number'] = 0 # Set installment_number to 0 where repayment_type is 'part_payment'
    hm_rps_df.loc[hm_rps_df['installment_number'] > 0, 'installment_number'] = range(1, len(hm_rps_df[hm_rps_df['installment_number'] > 0]) + 1) # fix the numbering again
    hm_rps_df = hm_rps_df[['application_id','installment_number','repay_date','repay_amount','repay_interest','repay_principle','amount_paid','repay_os','pending_emi_amount','repaid_at','repayment_type','interest_rate']]
    return hm_rps_df



async def homeville_rps_update_single_application(app_id):

    try: # Fetch Homeville RPS via API
        hm_rps_df = get_hm_rps(app_id)
        if isinstance(hm_rps_df, dict) and hm_rps_df.get("status") == "Failed":
            return hm_rps_df
        print(f"Homeville Repayment schedule fetched successfully for application_id: {app_id}")
        logger.send_log_event(f"INFO: Homeville Repayment schedule fetched successfully for application_id: {app_id}. Homeville RPS: {hm_rps_df.to_json(orient='records')}")
    except Exception as e:
        log_exception(logger, f"An unexpected error occurred: {e}")
        return {"status": "ERROR", "message": f"An unexpected error occurred: {e}"}

    # -------------- Handling EMI Payments -----------------------
    # Fetch existing emi_payments records for given application_id
    ep_fetch_query = """
        select id,emi_date,pre_emi_amount,emi_amount,payment_status from emi_payments where application_id = :application_id
    """
    params = {'application_id':app_id}
    existing_ep_df = execute_query(ep_fetch_query, params=params)
    if existing_ep_df.empty: # If the emi_payments table contains no records for given app_id, insert the Homeville RPS into it as is.
        logger.send_log_event(f"INFO: No data found in emi_payments for application_id: {app_id}. Inserting Homeville RPS.")
        try:
            hm_rps_df_insert = hm_rps_df[~hm_rps_df['repayment_type'].isin(['foreclosure', 'part_payment'])] # pass homeville rps without the foreclosure/pre_part_payment row
            insert_emi_payments(hm_rps_df_insert)
            print(f"Homeville RPS inserted in emi_payments table for application_id: {app_id}.")
        except Exception as e:
            log_exception(logger, f"Error inserting Homeville RPS into emi_payments table for application_id {app_id}: {str(e)}")
    else:
        # If emi_date exists in emi_payments, then update only those existing records in emi_payments
        ep_merged_df = hm_rps_df.merge(existing_ep_df,left_on='repay_date',right_on='emi_date')
        try:
            update_emi_payments(ep_merged_df)
            update_ep_msg = f"Homeville RPS updated in emi_payments table for application_id: {app_id}"
        except Exception as e:
            update_ep_msg = f"Error updating emi_payments table for application_id {app_id}: {str(e)}"
        logger.send_log_event(f"INFO: {update_ep_msg}")

        # delete records from emi_payments which are not in homeville rps
        existing_emis = set(existing_ep_df['emi_date'])
        hm_emis = set(hm_rps_df['repay_date'])
        to_delete = set(existing_emis - hm_emis)
        to_delete_rows = existing_ep_df[existing_ep_df['emi_date'].isin(to_delete)]
        # print(f"EMI dates to delete from emi_payments for application_id {app_id} : {to_delete}") #kept for logs remove later.
        if not to_delete_rows.empty:
            try:
                delete_emi_payments(to_delete_rows)
                logger.send_log_event(f"INFO: Deleted {len(to_delete_rows)} rows of {to_delete} from emi_payments for application_id {app_id} from emi_payments.")
            except Exception as e:
                log_exception(logger, f"Error deleting EMI payments: {str(e)} for application_id {app_id}")
        else:
            logger.send_log_event(f"INFO: No EMIs to delete for application_id {app_id} from emi_payments.")

        # insert records which are in homeville rps but not in emi_payments
        # existing_emis = set(existing_ep_df.loc[(existing_ep_df['payment_status'] == 'pending'),'emi_date']) # fetch all pending emi_dates
        existing_emis = set(existing_ep_df.loc[existing_ep_df['payment_status'].isin(['pending', 'partial']), 'emi_date'])
        existing_success = set(existing_ep_df.loc[existing_ep_df['payment_status'] == 'success', 'emi_date']) # success emis are to be excluded from insertion/deletion
        hm_emis = set(hm_rps_df.loc[hm_rps_df['repayment_type'].isin(['PEMI','EMI']), 'repay_date'])
        # hm_emis = set(hm_rps_df['repay_date'])
        to_insert = (hm_emis - existing_emis) - existing_success
        to_insert_rows = hm_rps_df[hm_rps_df['repay_date'].isin(to_insert)]
        # print(f"EMI Dates to insert in emi_payments for application_id {app_id} : {to_insert}")  #kept for logs remove later.
        if not to_insert_rows.empty:
            try:
                insert_emi_payments(to_insert_rows)
                logger.send_log_event(f"INFO: Inserted {len(to_insert)} rows of dates {to_insert} into emi_payments for application_id: {app_id}.")
            except Exception as e:
                log_exception(logger, f"Error inserting EMI payments for application_id {app_id} : {str(e)}")
        else:
            logger.send_log_event(f"INFO: No new EMIs to insert for application_id {app_id}.")


    # -------------- Handling RPS Logs -----------------------
    hm_rps_logs = hm_rps_df # create a separate df to insert in rps_logs
    hm_rps_logs['repay_foreclosure'] = hm_rps_logs['repay_os'] + hm_rps_logs['repay_interest'].shift(-1).fillna(0)
    hm_rps_logs.at[len(hm_rps_logs)-1, 'repay_foreclosure'] = 0
    loan_summary_dict = {"repayable": float(hm_rps_logs['repay_amount'].sum()), "subvention": 0, "interest_rate": 0, # generate a loan_summary dictionary
                          "total_interest": float(hm_rps_logs['repay_interest'].sum()), "principal_amount": float(hm_rps_logs['repay_principle'].sum()),
                          "total_subvention": 0, 
                          "annualized_interest_rate": float(hm_rps_logs['interest_rate'].iloc[0]) / 100 if pd.notnull(hm_rps_logs['interest_rate'].iloc[0]) else 0.0}
    hm_rps_logs['loan_summary'] = None
    hm_rps_logs.at[0, 'loan_summary'] = json.dumps(loan_summary_dict)
    hm_rps_logs_insert = hm_rps_logs[~hm_rps_logs['repayment_type'].isin(['foreclosure', 'part_payment'])]

    rps_fetch_query = """
        select id,installment_number,emi_date from rps_logs where deleted_at is null and application_id = :application_id
    """
    params = {'application_id':app_id}
    existing_rps_df = execute_query(rps_fetch_query, params=params)
    if existing_rps_df.empty: # If the rps_logs table contains no records for given app_id, insert the Homeville RPS into it as is.
        logger.send_log_event(f'No data found in rps_logs for application_id: {app_id}. Inserting Homeville RPS.')
        try:
            insert_rps_logs(hm_rps_logs_insert)
            logger.send_log_event(f"INFO: Homeville RPS inserted in rps_logs table for application_id: {app_id}.")
        except Exception as e:
            log_exception(logger, f"Error inserting Homeville RPS into rps_logs table for application_id {app_id}: {str(e)}")
    else:
        # If emi_date exists in rps_logs, then update only those existing records in rps_logs
        rps_merged_df = hm_rps_df.merge(existing_rps_df,left_on='repay_date',right_on='emi_date')
        try:
            rps_merged_df.rename(columns={'installment_number_x':'installment_number'},inplace=True)
            update_rps_logs(rps_merged_df)
            update_rps_msg = f"Homeville RPS updated in rps_logs table for application_id: {app_id}"
        except Exception as e:
            update_rps_msg = f"Error updating rps_logs table for application_id {app_id}: {str(e)}"
        logger.send_log_event(f"INFO: {update_rps_msg}")

        # delete records from rps_logs which are not in homeville rps
        existing_emis = set(existing_rps_df['emi_date'])
        hm_emis = set(hm_rps_df['repay_date'])
        to_delete = set(existing_emis - hm_emis)
        to_delete_rows = existing_rps_df[existing_rps_df['emi_date'].isin(to_delete)]
        # print(f"EMI dates to delete from rps_logs for application_id {app_id} : {to_delete}") #kept for logs remove later.
        if not to_delete_rows.empty:
            try:
                delete_rps_logs(to_delete_rows)
                logger.send_log_event(f"INFO: Deleted {len(to_delete_rows)} rows of {to_delete} from rps_logs for application_id {app_id} from rps_logs.")
            except Exception as e:
                log_exception(logger, f"Error deleting rps_logs: {str(e)} for application_id {app_id}")
        else:
            logger.send_log_event(f"INFO: No EMIs to delete for application_id {app_id} from rps_logs.")

        # insert records which are in homeville rps but not in rps_logs
        existing_emis = set(existing_rps_df['emi_date']) # fetch all emi_dates present in rps_logs
        hm_emis = set(hm_rps_df.loc[hm_rps_df['repayment_type'].isin(['PEMI','EMI']), 'repay_date'])
        to_insert = (hm_emis - existing_emis)
        to_insert_rows = hm_rps_df[hm_rps_df['repay_date'].isin(to_insert)]
        # print(f"EMI Dates to insert in rps_logs for application_id {app_id} : {to_insert}")  #kept for logs remove later.
        if not to_insert_rows.empty:
            try:
                insert_rps_logs(to_insert_rows)
                logger.send_log_event(f"INFO: Inserted {len(to_insert)} rows of dates {to_insert} into rps_logs for application_id: {app_id}.")
            except Exception as e:
                log_exception(logger, f"Error inserting rps_logs for application_id {app_id} : {str(e)}")
        else:
            logger.send_log_event(f"INFO: No new EMIs to insert for application_id {app_id} in rps_logs.")


    # -------------- Handling Payment transactions -----------------------

    # If emi_payment_id exists in payment_transactions, then update only those existing records
    existing_ep_df = execute_query(ep_fetch_query, params=params)
    ep_merged_df = hm_rps_df.merge(existing_ep_df,left_on='repay_date',right_on='emi_date',how='inner')
    try:
        update_payment_transactions(ep_merged_df)
        update_pt_msg = f'Homeville payments updated in payment_transactions table for application_id: {app_id}'
    except Exception as e:
        update_pt_msg = f"Error updating payment_transactions table for application_id {app_id} : {str(e)}"
    logger.send_log_event(f"INFO: {update_pt_msg}")

    # insert emi_payment_id present in emi_payments but not in payment_transactions
    payments_query = """ 
    select application_id, emi_payment_id, payment_date, payment_reconciliation_date, amount, payment_for repayment_type from payment_transactions
    where (deleted_at is null and is_deleted_at is null and refunded_at is null) and (application_id = :application_id) 
    and (payment_for in ('Pre-EMI','EMI'))
    """
    params = {'application_id':app_id}
    payments_df = execute_query(payments_query, params=params)
    existing_emis = set(ep_merged_df.loc[ep_merged_df['amount_paid'] > 0, 'id'])
    existing_payments = set(payments_df['emi_payment_id'])
    to_insert_pt = (existing_emis - existing_payments)
    to_insert_rows_pt = ep_merged_df[ep_merged_df['id'].isin(to_insert_pt)]
    # print(f"Payments to insert of application_id {app_id} for these emi_payment_ids : {to_insert_pt}") # # kept for logs remove later
    if not to_insert_rows_pt.empty:
        try:
            insert_payment_transaction(to_insert_rows_pt)
            logger.send_log_event(f"INFO: Inserted payments for these emi_payment_id: {to_insert_pt} into payment_transactions for application_id {app_id}.")
        except Exception as e:
            log_exception(logger, f"Error inserting payment_transactions for application_id {app_id}: {str(e)}")
    else:
        logger.send_log_event(f"INFO: No new payments to insert for application_id {app_id}.")


    # set deleted_at for emi_payment_id present in payment_transactions but not in emi_payments
    existing_emis = set(ep_merged_df['id'])
    existing_payments = set(payments_df['emi_payment_id'])
    to_delete_pt = existing_payments - existing_emis
    to_delete_rows_pt = payments_df[payments_df['emi_payment_id'].isin(to_delete_pt)]
    # print(f"Payments to be deleted of application_id {app_id} for these emi_payment_ids : {to_delete_pt}") # kept for logs remove later
    if not to_delete_rows_pt.empty:
        try:
            delete_payment_transactions(to_delete_rows_pt)
            logger.send_log_event(f"INFO: Deleted payments for these emi_payment_id: {to_delete_pt} from payment_transactions for application_id {app_id}.")
        except Exception as e:
            log_exception(logger, f"Error deleting payment_transactions for application_id {app_id}: {str(e)}")
    else:
        logger.send_log_event(f"INFO: No payments to delete for application_id {app_id}.")
    
    # Update loans data for Homeville cases
    logger.send_log_event(f"INFO: Updating element & additional table with POS, DPD data for application_id: {app_id}.")
    await hm_loan_schedule(app_id)

    # handling foreclosure transactions
    #hm_foreclosure_df = hm_rps_df.loc[hm_rps_df['repayment_type'].isin(['foreclosure']), ['application_id','repaid_at', 'amount_paid', 'repayment_type']]
    hm_foreclosure_df = hm_rps_df.loc[hm_rps_df['repayment_type'].isin(['foreclosure']), ['application_id', 'repaid_at', 'amount_paid', 'repay_interest',
                                                                                          'repay_principle', 'repayment_type']]
    existing_foreclosure_query = """ 
        select application_id,min(payment_date) repaid_at,sum(amount) amount_paid,id from payment_transactions 
        where application_id = :application_id and (payment_for = 'Foreclosure') and (deleted_at is null and is_deleted_at is null and refunded_at is null) 
        group by application_id
    """
    params = {'application_id':app_id}
    existing_foreclosure_df = execute_query(existing_foreclosure_query,params=params)
    if existing_foreclosure_df.empty and not hm_foreclosure_df.empty: # If foreclosure record does not exist in payment_transactions, then insert
        try:
            hm_foreclosure_df['repayment_type'] = 'foreclosure'
            logger.send_log_event(f"INFO: Inserting foreclosure record: {hm_foreclosure_df.to_dict(orient='records')} in payment_transactions & foreclosure_breakup for application_id: {app_id}")
            insert_payment_transaction(hm_foreclosure_df)
            insert_foreclosure_breakup(hm_foreclosure_df)
        except Exception as e:
            log_exception(logger, f"Error inserting foreclosure record in payment_transactions for application_id {app_id}: {str(e)}") 
    elif hm_foreclosure_df.empty and not existing_foreclosure_df.empty: # If foreclosure record does not exist in homeville api and exist in payment_transactions, then delete
        try:
            existing_foreclosure_df[['repayment_type', 'emi_payment_id']] = ['Foreclosure', 0]
            logger.send_log_event(f"INFO: Existing Foreclosure record to be deleted: {existing_foreclosure_df.to_dict(orient='records')} from payment_transactions & foreclosure_breakup for application_id: {app_id}")
            delete_payment_transactions(existing_foreclosure_df)
            delete_foreclosure_breakup(hm_foreclosure_df)
        except Exception as e:
            log_exception(logger, f"Error deleting foreclosure record from payment_transactions for application_id {app_id}: {str(e)}")
    elif not hm_foreclosure_df.empty and not existing_foreclosure_df.empty: # If foreclosure record exist in both homeville api and payment_transactions then check for update
        foreclosure_merged_df = pd.merge(hm_foreclosure_df, existing_foreclosure_df, on='application_id', suffixes=('_hm', '_ex'))
        update_mask = (foreclosure_merged_df['repaid_at_hm'] != foreclosure_merged_df['repaid_at_ex']) | (round(foreclosure_merged_df['amount_paid_hm']) != foreclosure_merged_df['amount_paid_ex'])
        if update_mask.any(): # If difference in foreclosure payment date and amount then update in payment_transactions according to homeville api
            try:
                foreclosure_merged_df.rename(columns={'id_ex':'id','amount_paid_hm':'amount_paid','repaid_at_hm':'repaid_at'},inplace=True)
                foreclosure_merged_df[['repayment_type', 'id']] = ['Foreclosure', 0]
                logger.send_log_event(f"INFO: Updating foreclosure records: {foreclosure_merged_df.to_dict(orient='records')} in payment_transactions & foreclosure_breakup for application_id: {app_id}")
                update_payment_transactions(foreclosure_merged_df)
                update_foreclosure_breakup(foreclosure_merged_df)
            except Exception as e:
                log_exception(logger, f"Error updating foreclosure record from payment_transactions for application_id {app_id}: {str(e)}")
        else: # If no differences, no update needed
            logger.send_log_event(f"INFO: Foreclosure update not needed.")


    # handling part_payment transactions
    hm_part_payment_df = hm_rps_df.loc[hm_rps_df['repayment_type'].isin(['part_payment']), ['application_id','repaid_at', 'amount_paid', 'repayment_type']]
    existing_part_payment_query = """ 
        select date(payment_date) repaid_at,amount,id from payment_transactions 
        where application_id = :application_id and (payment_for = 'pre_part_payment') and (deleted_at is null and is_deleted_at is null and refunded_at is null) 
    """
    params = {'application_id':app_id}
    existing_part_payment_df = execute_query(existing_part_payment_query,params=params)

    # If part_payment exists in both homeville rps and payment_transactions, update those
    part_payments_merged_df = hm_part_payment_df.merge(existing_part_payment_df,on='repaid_at',how='inner')
    if not part_payments_merged_df.empty:
        try:
            part_payments_merged_df[['repayment_type', 'emi_payment_id']] = ['pre_part_payment', 0]
            update_part_payment_transactions(part_payments_merged_df)
            update_pt_msg = f'Homeville pre_part_payments updated in payment_transactions table for application_id: {app_id}'
        except Exception as e:
            update_pt_msg = f"Error updating pre_part_payments in payment_transactions table for application_id {app_id} : {str(e)}"
        logger.send_log_event(f"INFO: {update_pt_msg}")

    # insert pre_part_payments which are in homeville rps but not in payment_transactions
    hm_part_payments = set(hm_part_payment_df['repaid_at'])
    existing_part_payments = set(existing_part_payment_df['repaid_at'])
    to_insert_part = (hm_part_payments - existing_part_payments)
    to_insert_rows_part = hm_part_payment_df[hm_part_payment_df['repaid_at'].isin(to_insert_part)]
    if not to_insert_rows_part.empty:
        try:
            to_insert_rows_part['repayment_type'] = 'pre_part_payment'
            insert_payment_transaction(to_insert_rows_part)
            logger.send_log_event(f"INFO: Inserted pre_part_payments of these dates: {to_insert_part} into payment_transactions for application_id {app_id}.")
        except Exception as e:
            log_exception(logger, f"Error inserting pre_part_payments in payment_transactions for application_id {app_id}: {str(e)}")

    # set deleted_at for pre_part_payment present in payment_transactions but not in homeville rps
    to_delete_part = (existing_part_payments - hm_part_payments)
    to_delete_rows_part = existing_part_payment_df[existing_part_payment_df['repaid_at'].isin(to_delete_part)]
    if not to_delete_rows_part.empty:
        try:
            existing_part_payment_df[['repayment_type', 'emi_payment_id']] = ['pre_part_payment', 0]
            delete_payment_transactions(to_delete_rows_part)
            logger.send_log_event(f"INFO: Deleted pre_part_payments of these dates: {to_delete_part} from payment_transactions for application_id {app_id}.")
        except Exception as e:
            log_exception(logger, f"Error deleting pre_part_payments in payment_transactions for application_id {app_id}: {str(e)}")

    return {"status": "Success", "message": f"Homeville RPS updated successfully for application_id: {app_id}"}


async def function1(data):
    app_id_list = data.get("app_id_list", [])
    if not app_id_list:
        logger.send_log_event(f"INFO: No application IDs provided. Running for all active Homeville cases.")
        print("No application IDs provided. Running for all active Homeville cases.")
        all_app_query = """
        select consumer_application.id
        from homeville_loans
        join consumer_application on consumer_application.id = homeville_loans.application_id 
        and consumer_application.deleted_at is null and consumer_application.is_deleted is null and consumer_application.merchant_id not in (73,9421)
        and consumer_application.state in ('disbursed','nach registered') and consumer_application.lender_id = 10
        """
        all_app_df = execute_query(all_app_query)
        app_id_list = set(all_app_df['id'].tolist())

    start_time = time.time()
    logger.send_log_event(f"INFO: Starting RPS update process for {len(app_id_list)} applications...")

    sem = asyncio.Semaphore(5)
    loop = asyncio.get_running_loop()

    async def safe_run(app_id):
        async with sem:
            try:
                # Wrap the blocking call
                result = await loop.run_in_executor(None, lambda: asyncio.run(homeville_rps_update_single_application(app_id)))
                return app_id, result
            except Exception as e:
                return app_id, {"status": "ERROR", "message": str(e)}

    tasks = [safe_run(app_id) for app_id in app_id_list]
    results = await asyncio.gather(*tasks, return_exceptions=True)
    
    # Initialize tracking lists
    processed_ids = []
    failed_ids = []

    for app_id, res in results:
        # Check if the result is a dictionary and has success status
        if isinstance(res, dict) and res.get("status") == "Success":
            processed_ids.append(app_id)
        else:
            failed_ids.append(app_id)
            logger.send_log_event(f"ERROR: Application ID {app_id} failed. Result: {res}")

    end_time = time.time()
    total_duration = end_time - start_time

    logger.send_log_event(
        f"INFO: Finished. Total Time: {total_duration:.2f}s. "
        f"Processed: {len(processed_ids)}, Failed: {len(failed_ids)}"
    )

    return {
        "status": "OK",
        "message": (
            f"Homeville RPS updated successfully for {len(processed_ids)} application(s). "
            f"Failed for {len(failed_ids)} application(s)."
        ),
        "data": {
        "failed_application_ids": failed_ids
    }
    }



# if __name__ == '__main__':
#     data = {"app_id_list": [243324]} # 243324, 254117
    
#     asyncio.run(function1(data))