import pandas as pd
from datetime import datetime
import pytz, os, asyncio, re
import numpy as np
from core_utilities import mail_service
from scripts.mysql_queries import bureau_report_cases_query, bureau_report_data_query
# from scripts.bounce_penal_update import function1 as bounce_penal_function
# from scripts.overdue_update import function1 as overdue_function
from scripts.update_loans_data import loan_metrics_calculation
from utils import execute_query, decrypt_dataframe_columns, zip_file, get_cloudwatch_logger, log_exception
logger = get_cloudwatch_logger(stream_name="bureau_report_script")
local_tz = pytz.timezone("Asia/Kolkata")

BUREAU_DIR = "bureau_report_folder"
os.makedirs(BUREAU_DIR, exist_ok=True)


def function1():
    try:
        bureau = "cibil"
        email_list = ["imran@creditfair.in", "amol@creditfair.in", "disha.doshi@creditfair.in", "sunil@creditfair.in", "rudraksha.vaity@creditfair.in"]
        # email_list = ["rudraksha.vaity@creditfair.in"]

        # Step 1: Get all relevant applications
        request_time = datetime.now()
        logger.send_log_event(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: In bureau_report_function, fetching data from database...")
        print(f"[{request_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: In bureau_report_function, fetching data from database...")
        app_df = execute_query(bureau_report_cases_query)
        app_ids = app_df['id'].tolist()

        if not app_ids:
            return {"status": "FAILED", "message": "No applications found"}

        logger.send_log_event(f"INFO: Fetched {len(app_ids)} application_ids.")
        print(f"INFO: Fetched {len(app_ids)} application_ids.")

        # Update bounce penal, POS, TOS, overdue & other amounts prior to foreclosure calculation to get updated data
        async def _async_tasks():
            await loan_metrics_calculation({"app_id_list": app_ids})
        asyncio.run(_async_tasks())

        base_df = execute_query(bureau_report_data_query, params={'app_ids': tuple(app_ids)})

        logger.send_log_event("INFO: Decrypting columns...")
        print("INFO: Decrypting columns...")
        base_df = decrypt_dataframe_columns(base_df, ["Income Tax ID Number", "Telephone No.Mobile", "Email ID 1", "Universal ID Number"])

        # logger.send_log_event("INFO: Fetching total amount due, amount over due, dpd...")
        # final_bureau_df = pd.merge(base_df, overdue_df[['application_id','total_amount_due','amount_over_due','dpd']], on='application_id', how='left')

        base_df['No of Days Past Due'] = base_df['No of Days Past Due'].clip(upper=999)

        # Aadhaar no. should be 12 digits
        # base_df["Universal ID Number"] = base_df["Universal ID Number"].astype("Int64")  # keep nullable int type
        # base_df["Universal ID Number"] = base_df["Universal ID Number"].where(base_df["Universal ID Number"].astype(str).str.match(r"^\d{12}$"),np.nan)
        col = base_df["Universal ID Number"].astype(str).str.strip()
        col = col.where(col.str.fullmatch(r"\d{12}"), np.nan)
        base_df["Universal ID Number"] = col.astype("Int64")

        # Change date column format to ddmmyyyy
        date_cols = ['Date of Birth', 'Date Opened/Disbursed', 'Date of Last Payment', 'Date Closed', 'Date Reported']
        # base_df[date_cols] = base_df[date_cols].apply(lambda col: pd.to_datetime(col).dt.strftime('%d%m%Y'))
        base_df[date_cols] = base_df[date_cols].apply(
            lambda col: pd.to_datetime(col, errors="coerce").dt.strftime("%d%m%Y")
        )

        # Asset classification
        conditions = [
            (base_df['No of Days Past Due'] >= 365) |
            (base_df['settled_flag'] > 0) |
            (base_df['reason'].isin(['non_recoverable', 'fraud_by_customer', '365_plus_dpd'])),
            base_df['No of Days Past Due'] > 120
        ]
        base_df['Asset Classification'] = np.select(conditions, ['04', '02'], default='01')

        # Member code & name logic
        is_cibil = (bureau == 'cibil')
        is_lender_1 = (base_df['lender_id'] == 1)

        base_df['Current/New Member Code'] = np.select([
            is_lender_1 & is_cibil,
            is_lender_1 & ~is_cibil,
            ~is_lender_1 & is_cibil,
            ~is_lender_1 & ~is_cibil
        ], ['NB45420001', '027FP18918', 'NB49040001', '027FP19233'], default='')

        base_df['Current/New Member Short Name'] = np.select([
            is_lender_1 & is_cibil,
            is_lender_1 & ~is_cibil,
            ~is_lender_1 & is_cibil,
            ~is_lender_1 & ~is_cibil
        ], ['PRIDEFIN', 'MKHO', 'KMGLOBAL', 'COSR'], default='')

        base_df.sort_values(by='application_id',inplace=True)

        ILLEGAL_CHAR_RE = re.compile(r"[\x00-\x08\x0B\x0C\x0E-\x1F]")
        SPECIAL_CHAR_RE = re.compile(r"[^A-Za-z0-9\s]")
        # base_df["Address Line 1"] = (base_df["Address Line 1"].astype("string").apply(lambda x: ILLEGAL_CHAR_RE.sub("", x))
        # .apply(lambda x: SPECIAL_CHAR_RE.sub("", x)).str.replace(r"\s+", " ", regex=True).fillna(""))
        base_df["Address Line 1"] = (
            base_df["Address Line 1"]
                .astype("string")
                .str.strip()
                .replace("None", "", regex=False)
                .fillna("")
                .str.replace(ILLEGAL_CHAR_RE, "", regex=True)
                .str.replace(SPECIAL_CHAR_RE, "", regex=True)
                .str.replace(r"\s+", " ", regex=True)
                .str.strip()
        )


        now_str = datetime.now(local_tz).strftime('%Y-%m-%d_%H-%M-%S')
        for f in os.listdir(BUREAU_DIR):
            file_path = os.path.join(BUREAU_DIR, f)
            if os.path.isfile(file_path):
                os.remove(file_path)
        bureau_report_file = os.path.join(BUREAU_DIR, f"bureau_report_{now_str}.xlsx")
        base_df.to_excel(bureau_report_file, index=False)
        zip_path = bureau_report_file.replace('.xlsx', f'_compressed.zip')
        zip_file(bureau_report_file, zip_path)
    except Exception as e:
        log_exception(logger, f"Error while generating bureau report: {e}")
        print(f"Error while generating bureau report: {e}")
        return {"status": "ERROR", "message": f"Bureau report generation failed: {e}."}

    msg = f"Bureau Report {now_str}"
    mail_result = mail_service.send_mail_with_attachment(email_list,f"Hi Team\nPFA {msg}",msg,zip_path)

    try: # after sharing mail delete file from bureau_report_folder
        os.remove(bureau_report_file)
        for f in os.listdir(BUREAU_DIR):
            file_path = os.path.join(BUREAU_DIR, f)
            os.remove(file_path)
        logger.send_log_event("INFO: Removed file from bureau_report_folder.")
        print("INFO: Removed file from bureau_report_folder.")
    except Exception as e:
        log_exception(logger, f"Error while cleaning up {BUREAU_DIR}: {e}")
        print(f"Error while cleaning up {BUREAU_DIR}: {e}")
    
    response_time = datetime.now()
    elapsed_time = (response_time - request_time).total_seconds()
    if mail_result:
        logger.send_log_event(f"[{response_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Bureau report shared via email successfully. "
        f"Response time: {elapsed_time:.2f} seconds.")
        print(f"[{response_time.strftime('%Y-%m-%d %H:%M:%S')}] INFO: Bureau report shared via email successfully. Response time: {elapsed_time:.2f} seconds.")
        return {"status": "Success", "message": f"Bureau report shared with {email_list}"}
    else:
        logger.send_log_event(f"FAILED: Bureau report mail sharing failed. Mail API response: {mail_result}")
        print(f"FAILED: Bureau report mail sharing failed. Mail API response: {mail_result}")
    return {"status": "FAILED", "message": f"Bureau report sharing failed via mail. Mail API response: {mail_result}"}


if __name__ == "__main__":
    # data = {"bureau":"cibil", "email_list": ["rudraksha.vaity@creditfair.in","rudraksha.vaity@creditfair.in"]}
    # print(function1(data))
    function1()