autotask_functions

import pandas as pd
import os
import numpy as np
from django.contrib import messages
# ВПР function
def join_two_df(df_left, df_right, left_key, right_key, column_to_add):
    result_df = df_left.merge(df_right[[right_key, column_to_add]],how='left',left_on=left_key,right_on=right_key)
    if right_key in result_df.columns:
        result_df = result_df.drop(columns=[right_key])
    return result_df


def autotask_second_process(filepath):
    try:
        oked_df = pd.read_excel('utils/useforauto.xlsx', sheet_name = 'okeder')
        loan_type_df = pd.read_excel('utils/useforauto.xlsx', sheet_name = 'loantype')
        client_type_df = pd.read_excel('utils/useforauto.xlsx', sheet_name = 'clients')
        #year_begin_df = pd.read_excel('utils/useforauto.xlsx', sheet_name = 'inception')
        minibank_df = pd.read_excel('utils/useforauto.xlsx', sheet_name = 'minibank_new')
        anketa_ipoteka = pd.read_excel('utils/useforauto.xlsx', sheet_name = 'ipoteka_vtorichka')
        oked_df['code_2'] = oked_df['code_2'].astype(str)
        print('utils are set!')
    except:
        print('problem with utils!')
    anketa_ipoteka['passporter'] = anketa_ipoteka['passporter'].astype(str).str.lower()
    counteragent_mapping = {
    'Частные предприятия, хозяйства, товарищества и общества': 'Част. Предпр.',
    'Индивидуальные предприниматели': 'ИП',
    'Небанковские финансовые институты': 'НФИ',
    'Физические лица': 'Физ. Лиц.',
    'Негосударственные некоммерческие организации': 'НКО',
    'Предприятия с участием иностранного капитала': 'Предпр. с ИК',
    'Государственные организации и предприятия': 'Гос. Орг.'
    }

    df = pd.DataFrame()
    if os.path.exists(filepath):
        df = pd.read_csv(filepath)
        df = df[df['Сокр. Балансовый счет']<15800].copy()
        print("Initial Shape=>", df.shape)
        print("Intitial Portfel=>", df['Ос. кр. на балансе(экв.брутто)'].sum())
        df['Сокр. Балансовый счет3'] = df['Сокр. Балансовый счет'].astype(str).str[:3].copy()
        df['amount_95413'] = df['Остаток 95413 - экв.просроч.']+df['Остаток 95413 - экв.сроч.']
        #df.loc[df['Код анкеты'] == 11381106, 'Ос. кр. на балансе(экв.брутто)'] -= 15091.32
        
        df['brutto_without_95413'] = df['Ос. кр. на балансе(экв.брутто)']-df['amount_95413']
        df['Discount'] = df['Discount'].fillna(0)
        df['Ос. кр. на балансе(экв.брутто)'] = df['Ос. кр. на балансе(экв.брутто)']+df['Discount']
        df['brutto_without_95413'] = df['brutto_without_95413']+df['Discount']
        
        print('After deduction of discount, portfel=>:',df['Ос. кр. на балансе(экв.брутто)'].sum())
        df['given_date'] = pd.to_datetime(df['дата выдачи кредита'], errors = 'coerce')
        df['given_date'] = df['given_date'].dt.to_period("M") 
        loan_type_df['loan_name'] = loan_type_df['loan_name'].astype(str).str.lower()
        loan_type_df['loan_type'] = loan_type_df['loan_type'].astype(str).str.lower()
        client_type_df['passport'] = client_type_df['passport'].astype(str).str.lower()
        critical_cols = ['Из них судебные (экв.)','Остаток 91501 (общий) экв.', 'Начислен. проц. (эквив.)', 
                        'Просроч. проценты (экв.)', 'Остаток 16379','Сумма срочной комисии(16405)' ]
        
        df['critical_amounts'] = df['Ос. кр. на балансе(экв.брутто)']+df[critical_cols].fillna(0).sum(axis = 1)
        df = df[df['critical_amounts']>0].copy()
        print("After critical amount applied, the shape=>", df.shape)
        print("After critical amount applied, the portfel=>",df['Ос. кр. на балансе(экв.брутто)'].sum())
        df['Тип кредита'] = df['Тип кредита'].astype(str).str.lower()
        df['Кредитный паспорт'] = df['Кредитный паспорт'].astype(str).str.lower()
        df['is_corporate'] = df['Сокр. Балансовый счет3'].apply(lambda x: 'Jismoniy' if (x == '125')|(x=='149') else 'Yuridik')
        df['Макс.дни просрочки'] = df['Макс.дни просрочки'].fillna(0)
        df['overdue_days'] = df['Макс.дни просрочки'].apply(lambda x:'0-30' if x<=30 else ('30-60' if x<=60 else ('60-90' if x <=90 else '90+')))
        df['oked'] = df[['ОКЭД физического лица', 'ОКЭД']].apply(
        lambda x: x['ОКЭД физического лица']
        if not (pd.isna(x['ОКЭД физического лица']) or x['ОКЭД физического лица'] == 0)
        else (x['ОКЭД'] if not (pd.isna(x['ОКЭД']) or x['ОКЭД'] == 0) else 0),
        axis=1)
        df['oked'] = df['oked'].astype(str).str[:2]
        df['oked']=df['oked'].fillna('00')
        df = pd.merge(df, oked_df[['class_1', 'code_2', 'class_2']], left_on = 'oked', right_on = 'code_2', how = 'left')
        print("1 After some process applied, the portfel=>",df['Ос. кр. на балансе(экв.брутто)'].sum())
        df = pd.merge(df, minibank_df, left_on = 'Минибанк', right_on = 'minicode', how = 'left')
        print("2 After some process applied, the portfel=>",df['Ос. кр. на балансе(экв.брутто)'].sum())
        df['currency'] = df['Валюта'].apply(lambda x: 'local' if x == 0 else 'global')
        df['Кредитный департамент'] = df['Кредитный департамент'].fillna('No') 
        
        df.loc[(df['Тип кредита']=='легкое авто ип малый бизнес')|(df['Тип кредита']=='легкое авто ип малый бизнес_2'), 'Кредитный департамент'] = "03-Малое кредитование"
        df.loc[(df['Тип кредита']=='легкое авто ип малый бизнес')|(df['Тип кредита']=='легкое авто ип малый бизнес_2'), 'Кредитный паспорт'] = "легкое авто"
        df = pd.merge(df, loan_type_df, left_on = 'Тип кредита', right_on = 'loan_name', how = 'left')
        print("3 After some process applied, the portfel=>",df['Ос. кр. на балансе(экв.брутто)'].sum())
        df = pd.merge(df, client_type_df, left_on = 'Код анкеты', right_on = 'dealid', how = 'left')
        print("4 After some process applied, the portfel=>",df['Ос. кр. на балансе(экв.брутто)'].sum())
        mask = df['Код анкеты'].isin(client_type_df['dealid'])
        df.loc[mask, 'loan_type'] = df.loc[mask, 'passport']
        df = df[df['critical_amounts']>0].copy()
        print("After some process applied, the shape=>", df.shape)
        print("After some process applied, the portfel=>",df['Ос. кр. на балансе(экв.брутто)'].sum())    
        df['reserve_percent'] = np.where(df['Ос. кр. на балансе(экв.брутто)'] == 0, 0, df['SUMM'] / df['Ос. кр. на балансе(экв.брутто)'])
        df['is_npl'] = df.apply(
        lambda x: 'yes' if ((x['Ос. кр. на балансе(экв.брутто)'] > 0) &
                            ((x['overdue_days'] == '90+') |(x['Из них судебные (экв.)'] > 0) |
                            (x['Код категория качества'] in [3, 4, 5]) |
                            (x['amount_95413'] > 0))) else 'no', axis=1)
        print("NPL=>", df['is_npl'].value_counts())    
        df['department'] = np.where((df['Кредитный департамент'] == "No"),'01-Кредитный департамент',
            np.where(df['Сокр. Балансовый счет'] == 11101, '01-Кредитный департамент', df['Кредитный департамент'])) 
        print('__________________________Attention!____________________________')
        print("125 and 149 accounts:=>", df[df['is_corporate']=='Jismoniy']['department'].value_counts())
        df['department'] = df[['department', 'is_corporate']].apply(lambda x: '02-Розничный департамент' if (x['is_corporate']=='Jismoniy')&(x['department']=='01-Кредитный департамент') else x['department'], axis =1)
        print("125 and 149 accounts Again:=>", df[df['is_corporate']=='Jismoniy']['department'].value_counts())
        conditions = [
            (df['department'] == '03-Малое кредитование'),
            (df['Сокр. Балансовый счет'] == 11101),
            (df['department'] == '01-Кредитный департамент'),
            (df['department'] == '02-Розничный департамент'),
            (df['department'] == '04-Андерайтинговая служба')
        ]
        choices = [
            df['Кредитный паспорт'],
            'Факторинг',
            'Корпоратив кредит',
            df['loan_type'],
            'Корпоратив кредит',
        ]
        df['loan_describe'] = np.select(conditions, choices, default=df['Кредитный паспорт'])
        df.loc[df['department']=='04-Андерайтинговая служба', 'loan_describe'] = 'Андерайтинговая служба'
        df['department'] = df['department'].str.replace("04-Андерайтинговая служба", '01-Кредитный департамент')
        df['loan_describe'] = df['loan_describe'].str.replace("микрокредит ип в наличной форме", 'кредит ип в наличной форме')
        df = pd.merge(df,anketa_ipoteka, left_on = 'Код анкеты', right_on = 'anketa', how = 'left')

        df.loc[df['Код анкеты'].isin(anketa_ipoteka['anketa']),'loan_describe'] = df.loc[df['Код анкеты'].isin(anketa_ipoteka['anketa']),'passporter'] 
        indices = (df['department'] == '03-Малое кредитование') & ((df['loan_describe'].isna())|(df['loan_describe']=='nan'))
        print("MK Passport Problems=>:")
        print(df.loc[indices, 'loan_describe'].value_counts())
        df.loc[indices, 'loan_describe'] = df.loc[indices, 'Тип кредита']
        df = df.rename({'nomi':'minibank_name', 'viloyat':'region'}, axis = 1)
        df['counteragent'] = df['Тип контрагента'].map(counteragent_mapping)
        columns = ['minibank_name', 'region', 'Банк филиали МФО раками', 'currency', 'department', 
        'loan_describe', 'class_1', 'is_npl', 'overdue_days', 'Код категория качества', 
        'Половая принадлежность', 'counteragent', 'is_corporate']
        existing_columns = [col for col in columns if col in df.columns]
        df = df.reset_index(drop=True)
        df[existing_columns] = df[existing_columns].fillna('другие')
        print('__________________________Attention!____________________________')
        print("Passports that are missing:=>", df[df['loan_describe']=='другие'][['department','Код анкеты','Кредитный паспорт', "Тип кредита"]])
        print("Passports that are missing count:=>", df[df['loan_describe']=='другие']["Тип кредита"].value_counts())
        
        print('__________________________Attention!____________________________2')
        print("MFO that are missing:=>", df[df['Банк филиали МФО раками']=='другие'][['department','Ос. кр. на балансе(экв.брутто)','Код анкеты','Банк филиали МФО раками', 'minibank_name']])
        print('__________________________Attention!____________________________3')
        print("Minibank that are missing:=>", df[df['minibank_name']=='другие'][['department','Ос. кр. на балансе(экв.брутто)','Код анкеты','Банк филиали МФО раками', 'minibank_name']])

        
        
        valid_contragents = df[(df['is_npl'] == 'yes') & 
                (df['Код контрагент'].notna()) & 
                (df['Код контрагент'] != 0) & 
                (df['Ос. кр. на балансе(экв.брутто)'] != 0) & 
                (df['Код контрагент'] != '')]
        
        valid_clients = df[(df['is_npl'] == 'yes') & 
                (df['ПИНФЛ'].notna()) & 
                (df['ПИНФЛ'] != 0) & 
                (df['Ос. кр. на балансе(экв.брутто)'] != 0) & 
                (df['ПИНФЛ'] != '')]
        
        # Get unique ПИНФЛ values
        contragents_with_npl = valid_contragents['Код контрагент'].unique()
        clients_with_npl = valid_clients['ПИНФЛ'].unique()
        df.loc[df['Код контрагент'].isin(contragents_with_npl), 'is_npl'] = 'yes'
        print("After Contragent appied=>",df['is_npl'].value_counts())   
        df.loc[df['ПИНФЛ'].isin(clients_with_npl), 'is_npl'] = 'yes'
        print("After PINFL appied=>",df['is_npl'].value_counts())    
       
        
        ready_df = df.groupby(['given_date','minibank_name', 'region', 'Банк филиали МФО раками','Балансовый счет','currency', 'department', 'loan_describe', 'class_1', 'is_npl', 'overdue_days', 'Код категория качества', 'Половая принадлежность', 'counteragent', 'is_corporate'], dropna=False).agg({'Ос. кр. на балансе(экв.брутто)': 'sum','brutto_without_95413':'sum', 'Остаток по кредиту на балансе (нетто)': 'sum','SUMM': 'sum','Йиллик фоиз ставкаси': 'mean','Код контрагент':'count' }).reset_index()
        print(ready_df.columns)
        ready_df['Код категория качества'] = ready_df['Код категория качества'].fillna(5)
        ready_df['interest_cat'] = ready_df['Йиллик фоиз ставкаси'].apply(lambda x: '0-5%' if x < 5 else ('5-10%' if x < 10 else ('10-15%' if x < 15 else ('15-20%' if x < 20 else ("20-25%" if x < 25 else ('25-30%' if x < 30 else ('30-35%' if x <35 else ("35-40%" if x < 40 else ("+40%" if x >= 40 else "0-5%")))))))))
        
        ready_df['Банк филиали МФО раками'] = ready_df['Банк филиали МФО раками'].astype(str).str.zfill(5)
        ready_df['quality_standart'] = ready_df['Код категория качества'].apply(lambda x: 'Стандартный' if x == 1 else ('Субстандартный' if x == 2 else ('Неудовлетворительный' if x == 3 else ("Сомнительный" if x == 4 else "Безнадежный"))))
        print("Final Shape=>", df.shape)
        print("Final Portfel=>",df['Ос. кр. на балансе(экв.брутто)'].sum()) 
        print("Final Ready Portfel=>",ready_df['Ос. кр. на балансе(экв.брутто)'].sum()) 
        return df, ready_df