# # 🔧 Add parent dir to sys.path before importing local modules
# import os, sys
# sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

# Now safe to import local modules
import base64, warnings
import pandas as pd
from io import StringIO
from dateutil.relativedelta import relativedelta
from utils import execute_query, upload_docs
from scripts.mysql_queries import vivriti_cam_query
import numpy as np
warnings.filterwarnings('ignore')


def generate_months(start_date, end_date):
    months = []
    current_date = start_date
    while current_date < end_date:
        months.append(current_date.strftime('%Y-%m'))
        current_date += relativedelta(months=1)
    if end_date.strftime('%Y-%m') not in months:
        months.append(end_date.strftime('%Y-%m'))
    return months


async def function1(data):
    application_id = data.get('application_id')
    user_id = data.get('user_id')
    all_tradeline_df = execute_query(vivriti_cam_query, params={"application_id": application_id})

    if all_tradeline_df is None or all_tradeline_df.empty:
        return f"No data found for application_id: {application_id}"


    # Ensure necessary columns are numeric to prevent type errors in calculations
    all_tradeline_df['inverter_KW_installation'] = pd.to_numeric(all_tradeline_df['inverter_KW_installation'], errors='coerce').fillna(0)
    all_tradeline_df['emi_amount'] = pd.to_numeric(all_tradeline_df['emi_amount'], errors='coerce').fillna(0)
    all_tradeline_df['firstemidate'] = pd.to_datetime(all_tradeline_df['firstemidate']).dt.date

    calc_df = all_tradeline_df[all_tradeline_df['eligibility'] == 'eligible'][['tradeline_id','firstemidate','dateclosed','emi_amount']]
    if calc_df.empty:
        final_df = all_tradeline_df.copy()
    else:
        calc_df['dateclosed'] = calc_df['dateclosed'].fillna(pd.Timestamp.today().date())
        calc_df['dateclosed'] = pd.to_datetime(calc_df['dateclosed']).dt.date
        calc_df['months'] = calc_df.apply(lambda row: generate_months(row['firstemidate'], row['dateclosed']), axis=1)
        calc_df = calc_df.explode('months')

        # get tradeline,month wise sum of emi_amount
        monthwise_pivot = calc_df.pivot_table(index=['tradeline_id'],columns='months',values='emi_amount',aggfunc='sum',fill_value=0).reset_index()

        # join/merge monthwise pivot with original dataframe
        final_df = pd.merge(all_tradeline_df,monthwise_pivot,on='tradeline_id',how='left')

        # code to add Total row to the end of CAM report chart
        month_year_list = monthwise_pivot.columns.to_list()
        month_year_list.remove('tradeline_id')
        final_df[month_year_list] = final_df[month_year_list].fillna(0).astype(int)
        final_df[['emi_amount','inverter_KW_installation']] = final_df[['emi_amount','inverter_KW_installation']].fillna(0).astype(int)
        total_row = pd.DataFrame(final_df[month_year_list].sum()).T # create a row(Total) which has month_wise sum of all emi_amount
        final_df = pd.concat([final_df, total_row]).reset_index(drop=True)


    final_df.drop(columns=['application_id','consumer_name','loan_value','tenure','roi','emi','merchant_name','type_of_customer','inverter_KW_installation',
                           'tradeline_id','firstemidate'],axis=1,inplace=True)
    max_emi_serviced = 0 if calc_df.empty else final_df.iloc[-1,1:].max()
    active_emi_amount = final_df[(final_df['account_status'] =='Active') & (final_df['eligibility'] == 'eligible')]['emi_amount'].sum()
    solar_type_unit = 8 if all_tradeline_df['type_of_customer'][0] =='Residential' else 15
    kw = all_tradeline_df['inverter_KW_installation'][0]
    power_savings = 120 * kw * solar_type_unit
    difference = (max_emi_serviced + power_savings) - active_emi_amount
    eligible_emi = difference * 0.5
    calc_dict = {'Max EMI Serviced:' : max_emi_serviced, 'Power Savings:' : power_savings, 'Active EMIs: ': active_emi_amount, 'Difference: ': difference,
                  'Eligible EMI: ': eligible_emi}
    calc_dict_df = pd.DataFrame([calc_dict])
    calc_dict_df = calc_dict_df.transpose()
    calc_dict_df.reset_index(inplace=True)
    calc_dict_df.columns = ['Details', 'Values']


    # consumer_details dictionary
    consumer_details_dict = {
        'Application ID:' : int(all_tradeline_df['application_id'][0]), 
        'Customer Name:' : all_tradeline_df['consumer_name'][0], 'Loan Value: ': all_tradeline_df['loan_value'][0], 
        'Tenure: ': all_tradeline_df['tenure'][0], 'ROI: ': all_tradeline_df['roi'][0], 'EMI: ': all_tradeline_df['emi'][0], 
        'Merchant name: ': all_tradeline_df['merchant_name'][0],'Type Of Customer': all_tradeline_df['type_of_customer'][0],
        'KW Applied': all_tradeline_df['inverter_KW_installation'][0]
    }
    consumer_details_df = pd.DataFrame([consumer_details_dict])
    consumer_details_df = consumer_details_df.transpose()
    consumer_details_df.reset_index(inplace=True)
    consumer_details_df.columns = ['Details', 'Values']
    

    # CAM report File creation 
    empty_df = pd.DataFrame([[np.nan] * 1] * 3, columns=['']) # create 3 x 1 empty df to seperate actual dataframes
    csv_buffer = StringIO()
    consumer_details_df.to_csv(csv_buffer, index=False)
    empty_df.to_csv(csv_buffer, mode='a', header=False, index=False)
    # final_df.at[len(final_df) - 1,'type_of_loan'] = 'Total'
    final_df.to_csv(csv_buffer, mode='a', index=False)
    empty_df.to_csv(csv_buffer, mode='a', header=False, index=False)
    calc_dict_df.to_csv(csv_buffer, mode='a', index=False)


    # Convert CSV to base64
    csv_data = csv_buffer.getvalue()
    base64_data = base64.b64encode(csv_data.encode()).decode()


    # Pass base64 string to cf_upload_docs_api
    upload_response = upload_docs('csv', application_id, base64_data, 'VIVRITI_CAM_REPORT', user_id)

    if upload_response.get("status") == "OK":
        return "Vivriti CAM report generated successfully"
    else:
        return f"Upload API error: {upload_response.get('message')}"


# if __name__ == "__main__":
#     test_input = {
#         "application_id": 12390,
#         "user_id": 443
#     }
#     print(function1(test_input))