autotask_view

from django.shortcuts import render, redirect
from . import queries
import getpass
import oracledb
import os
import datetime
import environ
env = environ.Env()
environ.Env.read_env() 
import pandas as pd
import numpy as np
import json
from . import forms
import datetime
from . import functions
from django.http import JsonResponse
import plotly.graph_objects as go
from django.contrib import messages
from django.contrib.auth.decorators import login_required

brutto_local = 19778424285.1859
brutto_foreign = 11805395412.5211
brutto_total = brutto_local+brutto_foreign

netto_local = 18979696782.3661
netto_foreign = 11646187829.9893
netto_total = netto_local+netto_foreign
@login_required
def autotask_home(request):
    autotask_filepath_processed = request.session.get(f'autotask_filepath_processed_{request.user.id}')
    if request.method == 'POST':
        form = forms.DateInputForm(request.POST)
        if form.is_valid():
            selected_date = form.cleaned_data['selected_date']
            date_str = str(selected_date)
            request.session[f'autotask_date_str_{request.user.id}'] = date_str
            autotask_filepath_processed = f"autotask/processed/ready_{date_str}.csv"
            autotask_filepath_raw = f"autotask/raw/portfolio_{date_str}.csv"
            
            if os.path.exists(autotask_filepath_processed):
                request.session[f'autotask_filepath_processed_{request.user.id}'] = autotask_filepath_processed
                print('The data for the selected_date exists so you can see results')
                return redirect('autotask-results-data')
            elif os.path.exists(autotask_filepath_raw):
                print('we have not processed file but we already have raw data, so going to process')
                request.session[f'autotask_filepath_raw_{request.user.id}'] = autotask_filepath_raw
                return redirect('autotask-process-data')
            else:
                print('We have neither processed nor raw data, so connecting to db...')
                return redirect('autotask-get-data')
    else:
        form = forms.DateInputForm()
    return render(request, 'autotask/autotask_home.html', {'form':form})

@login_required
def autotask_get_data(request):   
    df = pd.DataFrame()
    
    autotask_date_str = request.session.get(f'autotask_date_str_{request.user.id}')
    if autotask_date_str:
        try:
            selected_date = datetime.datetime.strptime(autotask_date_str, '%Y-%m-%d').date()
        except ValueError:
            # Handle invalid date format
            selected_date = None  # or set a default date
    autotask_filepath_raw = f"autotask/raw/portfolio_{selected_date}.csv"
    request.session[f'autotask_filepath_raw_{request.user.id}'] = autotask_filepath_raw
    if os.path.exists(autotask_filepath_raw):
        print('filepath exists. reading file...', autotask_filepath_raw )
        return redirect('autotask-process-data')
    else:    
        print('filepath does not exist. getting data...')
         
        connection = oracledb.connect(
        user = os.environ.get("ORACLE_USER"),
        password = os.environ.get("ORACLE_PASSWORD"),
        dsn = os.environ.get("ORACLE_DSN"))
        print('Connected')
        cursor = connection.cursor()
        print('Cursored')
        try:
            cursor.execute(queries.lp_for_auto, {'pdate':selected_date})
            print("Portfel query is executed")
            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchall()
            print('Portfel query is fetched')

        except Exception as e:
            import traceback
            traceback.print_exc()
            print('Error happened on Portfolio query', e)
        finally:
            cursor.close()    
        df = pd.DataFrame(rows, columns = columns)
        if df.empty:
            messages.warning(request, 'The data could not be fetched since it is not available yet!')
            return redirect('autotask-home')
        cursor = connection.cursor()
        try:
            cursor.execute(queries.maks_dni, {'pdate':selected_date})
            print("Max days query is executed")
            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchall() 
            print('Max days query is fetched')

        except Exception as e:
            import traceback
            traceback.print_exc()
            print('Error happened on Max days query', e)
        finally:
            cursor.close()  
          
        maks_dni = pd.DataFrame(rows, columns = columns)
        
        cursor = connection.cursor()
        try:
            cursor.execute(queries.factoring, {'pdate':selected_date})
            print("Factoring query is executed")
            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchall()
            print('Factoring query is fetched')

        except Exception as e:
            import traceback
            traceback.print_exc()
            print('Error happened on Factoring query', e)
        finally:
            cursor.close()
        factoring = pd.DataFrame(rows, columns = columns)
    
        cursor = connection.cursor()
        try:
            cursor.execute(queries.reserve, {'pdate':selected_date})
            print("Reserve query is executed")
            columns = [col[0] for col in cursor.description]
            rows = cursor.fetchall()
            print('Reserve query is fetched')

        except Exception as e:
            import traceback
            traceback.print_exc()
            print('Error happened on Reserve query', e)
        finally:
            cursor.close()     
            connection.close()
        reserve = pd.DataFrame(rows, columns = columns)
        new_reserve = reserve.groupby('DEALID')['SUMM'].sum().reset_index()

        try:   
            df = pd.merge(df, new_reserve, left_on = 'Код анкеты', right_on = 'DEALID', how = 'left')
            df = pd.merge(df, factoring[['DEALID', 'Discount']], left_on = 'Код анкеты', right_on = 'DEALID', how = 'left')
            df = pd.merge(df, maks_dni[['Код сделки Кредита', 'Макс.дни просрочки']], left_on = 'Код анкеты', right_on = 'Код сделки Кредита', how = 'left')
        except:
            messages.warning(request, 'The data could not be fetched since it is not available yet!')
            return redirect('autotask-home')    
        os.makedirs('autotask/raw', exist_ok = True)
        try:
            if df.empty == False:
                df.to_csv(autotask_filepath_raw,  index = False)
        except:
            pass
        return redirect('autotask-process-data')
    return render(request, 'autotask/autotask_get_data.html')

@login_required
def autotask_process_data(request):
    autotask_filepath_raw = request.session.get(f'autotask_filepath_raw_{request.user.id}')
    print(autotask_filepath_raw)
    try:
        raw_df, df = functions.autotask_second_process(autotask_filepath_raw) 
    except Exception as e:
        messages.error(request, f'{e} The database is not fetched since it is not yet available')
        return redirect('autotask-results-data')
    autotask_date_str = request.session.get(f'autotask_date_str_{request.user.id}')
    autotask_filepath_processed = f"autotask/processed/ready_{autotask_date_str}.csv"
    autotask_filepath_processed_raw = f"autotask/rprocessed/rpro_{autotask_date_str}.csv"
    request.session[f'autotask_filepath_processed_{request.user.id}'] = autotask_filepath_processed
    if os.path.exists(autotask_filepath_processed):
        print('The file is already processed, so skipping ...')
        return redirect('autotask-results-data')
    else:
        print('filepath does not exist, creating ...')
        os.makedirs('autotask/processed', exist_ok = True)
        os.makedirs('autotask/rprocessed', exist_ok = True)
        df.to_csv(autotask_filepath_processed)
        print("Processed file is saved")
        try:
            raw_df.to_csv(autotask_filepath_processed_raw)
            print("Processed Raw is saved")
        except:
            pass
        return redirect('autotask-results-data')
    return render(request, 'autotask/autotask_process_data.html', {'date_str':autotask_date_str})

@login_required
def autotask_results_data(request):
    selected_date = request.session.get(f'autotask_date_str_{request.user.id}')
    autotask_filepath_processed = request.session.get(f'autotask_filepath_processed_{request.user.id}')
    choice = request.GET.get('choice')
    try:
        df = pd.read_csv(autotask_filepath_processed)
        df['loan_describe'] = df['loan_describe'].str.capitalize()
    except:
        df = None
    if df is None:
        messages.error(request, 'Данные за эту дату еще не обработаны. Пожалуйста, сначала обработайте данные на этой странице.')
        return redirect('autotask-home')

    #df.to_excel('result1.xlsx')
    choice_mapping = {
        'Сум/Валюта': 'currency',
        'Экономической Деятельности': 'class_1',
        'Кредитного Продукта по РК': 'lp_rk',
        'Кредитного Продукта по МК': 'lp_mk',
        'Кредитного Продукта по КК': 'lp_kk',
        'Кредитов по каждому филиалу': 'minibank_name',
        'Кредитов по каждому област': 'region',
        'npl90+ Все': 'npl_all',
        'NPL90+ РК': 'npl_rk',
        'NPL90+ МК': 'npl_mk',
        'NPL90+ Филиал': 'npl_branch',
        'NPL90+ Область': 'npl_region'
    }

    russian_choices = list(choice_mapping.keys())
    selected_russian_choice = request.GET.get('choice', russian_choices[0])  # Default to first choice if none provided
    selected_english_choice = choice_mapping.get(selected_russian_choice, choice_mapping[russian_choices[0]])
    lp_total = df['Ос. кр. на балансе(экв.брутто)'].sum()
        

    if selected_english_choice == 'currency':
        names_for_currency = ["Доля кредитного портфеля в активах банка","Уровень годового прироста кредитного портфеля","Уровень годового прироста кредитного портфеля (в национальной валюте)","Уровень годового прироста кредитного портфеля (в иностранной валюте-всего)"]
        share_lp_bank = 64.3060848532371
        lp_start = 31583819697.7069*1000
        lp_total = df['Ос. кр. на балансе(экв.брутто)'].sum()    
        lp_local_start = 19778424285.1859*1000
        lp_local_current = df[df['currency']=='local']['Ос. кр. на балансе(экв.брутто)'].sum()
        lp_global_start = 11805395412.5211*1000
        lp_global_current = df[df['currency']=='global']['Ос. кр. на балансе(экв.брутто)'].sum()
        lp_change = (lp_total-lp_start)/lp_start*100
        lp_local_change = (lp_local_current-lp_local_start)/lp_local_start*100
        lp_global_change = (lp_global_current-lp_global_start)/lp_global_start*100
        values_for_currency = [share_lp_bank, lp_change, lp_local_change, lp_global_change] 
        cb_limit = [67, 17, 2, 45]
        self_limit = [66, 16.5, 1.0, 44.5]
        result = pd.DataFrame({'features':names_for_currency, 'percentage':values_for_currency, 'cb_limit':cb_limit, 'self_limit':self_limit})
        result['percentage']= result['percentage'].round(2)
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['features', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
    
    elif selected_english_choice == 'npl_all':
        names_for_all = ["Соотношение суммы проблемных активов - активы, качество которых классифицируется как «неудовлетворительные», «сомнительные» и «безнадежные» (на балансе и забалансом) к общей сумме активов",
        "Доля проблемных кредитов NPL (90+) в общем кредитном портфеле по Банку","Доля проблемных кредитов по кредитам корпоративного кредитования NPL 90+ в кредитном портфеле корпоративного кредитования",
        "Доля проблемных кредитов по кредитам малого кредитования NPL 90+ в кредитном портфеле малого кредитования","Доля проблемных кредитов по кредитам розничного кредитования NPL 90+ в кредитном портфеле розничного кредитования"]
        
        
        npl_on_all_lp = df[df['is_npl']=='yes']['Ос. кр. на балансе(экв.брутто)'].sum()/lp_total*100
        npl_on_kk_lp = df[(df['is_npl']=='yes')&(df['department']=='01-Кредитный департамент')]['Ос. кр. на балансе(экв.брутто)'].sum()/df[df['department']=='01-Кредитный департамент']['Ос. кр. на балансе(экв.брутто)'].sum()*100
        npl_on_mk_lp = df[(df['is_npl']=='yes')&(df['department']=='03-Малое кредитование')]['Ос. кр. на балансе(экв.брутто)'].sum()/df[df['department']=='03-Малое кредитование']['Ос. кр. на балансе(экв.брутто)'].sum()*100
        npl_on_rk_lp = df[(df['is_npl']=='yes')&(df['department']=='РК')]['Ос. кр. на балансе(экв.брутто)'].sum()/df[df['department']=='РК']['Ос. кр. на балансе(экв.брутто)'].sum()*100
        npl_on_all_lp = npl_on_all_lp.round(2)
        npl_on_kk_lp = npl_on_kk_lp.round(2) 
        npl_on_rk_lp = npl_on_rk_lp.round(2) 
        npl_on_mk_lp = npl_on_mk_lp.round(2) 
        
        values_for_all = [4.1, npl_on_all_lp, npl_on_kk_lp, npl_on_mk_lp, npl_on_rk_lp] 
        
        cb_limit = [5, 5, 5, 5,5]
        self_limit = [4, 4, 4, 4,4]
        result = pd.DataFrame({'features':names_for_all, 'percentage':values_for_all, 'cb_limit':cb_limit, 'self_limit':self_limit})
        result['percentage']= result['percentage'].round(2)
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['features', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        print(result_df)
    elif selected_english_choice == 'class_1':
        result = df.groupby(selected_english_choice)['Ос. кр. на балансе(экв.брутто)'].sum().reset_index()
        result = result[result[selected_english_choice]!='not have']
        result['percentage'] = result['Ос. кр. на балансе(экв.брутто)']/lp_total*100
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 20
        result['self_limit'] = 19
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[[selected_english_choice, 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    elif selected_english_choice == 'lp_rk':
        df_filter = df[df['department']=='02-Розничный департамент'] 

        result = df_filter.groupby('loan_describe')['Ос. кр. на балансе(экв.брутто)'].sum().reset_index()
        print(result['loan_describe'].value_counts())
        result = result[result['loan_describe']!='Not have']    
        result['percentage'] = result['Ос. кр. на балансе(экв.брутто)']/lp_total*100
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 25
        result['self_limit'] = 24
        result.loc[result['loan_describe']=='Автокредитование (первичный+вторичный рынок)', 'cb_limit'] = 40
        result.loc[result['loan_describe']=='Автокредитование (первичный+вторичный рынок)', 'self_limit'] = 38
        
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['loan_describe', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
           
    elif selected_english_choice == 'lp_mk':
        df_filter = df[df['department']=='03-Малое кредитование'] 
        print(df_filter[df_filter['loan_describe']=='Оборотный капитал для производственных нужд']['Ос. кр. на балансе(экв.брутто)'].sum())
        result = df_filter.groupby('loan_describe')['Ос. кр. на балансе(экв.брутто)'].sum().reset_index()
        print(result[result['loan_describe']=='Оборотный капитал для производственных нужд'])
        result['percentage'] = result['Ос. кр. на балансе(экв.брутто)']/lp_total*100
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 9
        result['self_limit'] = 7
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['loan_describe', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    elif selected_english_choice == 'lp_kk':
        df_filter = df[(df['department']=='01-Кредитный департамент')|(df['department']=='04-Андерайтинговая служба')] 
        result = df_filter.groupby('loan_describe')['Ос. кр. на балансе(экв.брутто)'].sum().reset_index()
        result = result[result['loan_describe']!='Not have']    
        result['percentage'] = result['Ос. кр. на балансе(экв.брутто)']/lp_total*100
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 30
        result['self_limit'] = 28
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['loan_describe', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    elif selected_english_choice == 'minibank_name': 
        result = df.groupby(selected_english_choice)['Ос. кр. на балансе(экв.брутто)'].sum().reset_index()
        result = result[result[selected_english_choice]!='not have']    
        result['percentage'] = result['Ос. кр. на балансе(экв.брутто)']/lp_total*100
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 15
        result['self_limit'] = 13
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['minibank_name', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    
        
    elif selected_english_choice == 'region':
        result = df.groupby(selected_english_choice)['Ос. кр. на балансе(экв.брутто)'].sum().reset_index()
        result = result[result[selected_english_choice]!='not have']
        result['percentage'] = result['Ос. кр. на балансе(экв.брутто)']/lp_total*100
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 13
        result['self_limit'] = 11
        result.loc[result['region'] == 'г. Ташкент с учетом Ташкентской области', 'cb_limit'] = 60
        result.loc[result['region'] == 'г. Ташкент с учетом Ташкентской области', 'self_limit'] = 55
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['region', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    elif selected_english_choice == 'npl_rk':
        df_filter = df[df['department']=='02-Розничный департамент']
        df_filter = df_filter[df_filter['loan_describe']!='Not have']
        result = pd.pivot_table(df_filter, index='loan_describe', columns = 'is_npl', values='Ос. кр. на балансе(экв.брутто)', aggfunc = 'sum').reset_index()
        result['no'] = result['no'].fillna(0)    
        result['percentage'] = result['yes'].div(result['yes'] + result['no']).mul(100).fillna(0)
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 5
        result['self_limit'] = 4
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['loan_describe', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    elif selected_english_choice == 'npl_mk':
        df_filter = df[df['department']=='03-Малое кредитование']
        df_filter = df_filter[df_filter['loan_describe']!='Not have']
        result = pd.pivot_table(df_filter, index='loan_describe', columns = 'is_npl', values='Ос. кр. на балансе(экв.брутто)', aggfunc = 'sum').reset_index()
        
        result['no'] = result['no'].fillna(0)    
        
        result['percentage'] = result['yes'].div(result['yes'] + result['no']).mul(100).fillna(0)
        
        result['percentage']= result['percentage'].round(1)
        print(result[result['loan_describe']=='Оборотный капитал для производственных нужд'])
        
        result['cb_limit'] = 5
        result['self_limit'] = 4
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['loan_describe', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    elif selected_english_choice == 'npl_branch':
        result = pd.pivot_table(df, index = 'minibank_name', columns = 'is_npl', values = 'Ос. кр. на балансе(экв.брутто)', aggfunc = 'sum').reset_index() 
        result = result[result['minibank_name']!='not have']    
        result['no'] = result['no'].fillna(0)    
        
        result['percentage'] = result['yes'].div(result['yes'] + result['no']).mul(100).fillna(0)
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 5
        result['self_limit'] = 4
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['minibank_name', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    elif selected_english_choice == 'npl_region':
        result = pd.pivot_table(df, index = 'region', columns = 'is_npl', values = 'Ос. кр. на балансе(экв.брутто)', aggfunc = 'sum').reset_index() 
        result = result[result['region']!='not have']    
        result['no'] = result['no'].fillna(0)    
        
        result['percentage'] = result['yes'].div(result['yes'] + result['no']).mul(100).fillna(0)
        result['percentage']= result['percentage'].round(1)
        result['cb_limit'] = 5
        result['self_limit'] = 4
        result['cb_comply'] = result[['percentage', 'cb_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['cb_limit'] else 'не нарушен', axis = 1)
        result['self_comply'] = result[['percentage', 'self_limit']].apply(lambda x: 'нарушен' if x['percentage']>x['self_limit'] else 'не нарушен', axis = 1)
        result_df = result[['region', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']]
        
    else:
        result = df.groupby(selected_english_choice)['Ос. кр. на балансе(экв.брутто)'].sum().reset_index()

    if selected_english_choice == 'currency':
        pass
    elif selected_english_choice == 'npl_all':
        pass
    else:
        result_df = result_df.sort_values('percentage', ascending = False) 
    result_df = result_df.reset_index(drop = True)
    result_df.columns = ['feature', 'percentage', 'cb_limit', 'self_limit', 'cb_comply', 'self_comply']
    table = go.Figure(data=[go.Table(
    # Define column widths (region wider)
    columnwidth=[300, 100, 100, 100, 100, 100],
    header=dict(
        values=['Показатель', 'Фактическое значение (%)', 'Предельное значение (%)', 'Стресс-Уровень (%)', 'Соответствие предельному значению', 'Соответствие стресс уровню'],
        fill_color='#4CAF50',
        align=['left', 'center', 'center', 'center', 'center', 'center'],  # Left align for Pokazatel, center for others
        font=dict(color='white', size=14),
        height=40
    ),
    cells=dict(
        values=[
            result_df['feature'],
            result_df['percentage'].round(1),
            result_df['cb_limit'].round(1),
            result_df['self_limit'].round(1),
            result_df['cb_comply'],
            result_df['self_comply']
        ],
        fill_color=[['#f5f5f5', '#ffffff'] * len(result_df)],  # Alternating row colors
        align=['left', 'center', 'center', 'center', 'center', 'center'],  # Left align for Pokazatel, center for others
        font=dict(size=12),
        height=30,
        # Conditional formatting for compliance
        font_color=[
            ['black'] * len(result_df),  # Region
            ['black'] * len(result_df),  # Percentage
            ['black'] * len(result_df),  # CB Limit
            ['black'] * len(result_df),  # Self Limit
            ['red' if x == 'нарушен' else 'green' for x in result_df['cb_comply']],  # CB Compliance
            ['red' if x == 'нарушен' else 'green' for x in result_df['self_comply']]  # Self Compliance
        ]
    )
    )])

    # Configure layout with title and increased top margin
    table.update_layout(
        margin=dict(l=10, r=10, t=80, b=10),  # Increased top margin to 80 for title and spacing
        height=90 + 55 * len(result_df),  # Dynamic height based on rows
        title=dict(
            text=selected_russian_choice,  # Customize your title
            x=0.5,  # Center the title
            xanchor="center",
            font=dict(size=16, color="black")
        )
    )

    # Convert Plotly figure to HTML div
    plot_div = table.to_html(full_html=False, include_plotlyjs='cdn')

    context = {
        'selected_date': selected_date,
        'choices': russian_choices,  # Pass Russian choices for frontend dropdown
        'selected_choice': selected_russian_choice,  # Pass the selected Russian choice
        'plot_div': plot_div  # Convert DataFrame result to a list of dictionaries for template
    }

    return render(request, 'autotask/autotask_results.html', context)