portfel_view

import polars as pl
import pandas as pd
import numpy as np
import pyarrow as pa
import pyarrow.parquet as pq
import io
import getpass,oracledb,os,json,datetime
import os

from . import forms
from . import queries
from .functions import process_portfel_best, prepare_df, generate_risk_chart
from functions import common_functions
from pathlib import Path
from django.shortcuts import render, redirect
from django.contrib import messages
from django.http import HttpResponse

BASE_DIR = Path(__file__).resolve().parent.parent

portfel_input_folder = BASE_DIR / "datasets" / "portfel" / "input"
portfel_svod_folder = BASE_DIR / "datasets" / "portfel" / "svod"
portfel_all_folder = BASE_DIR / "datasets" / "portfel" / "all"
utils_path = BASE_DIR / "utils" / "useforauto.xlsx"

def portfel_home(request):
    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"portfel_date_str_{request.user.id}"] = date_str
            portfel_filepath_svod = f"{portfel_svod_folder}/svod_{date_str}.parquet"
            portfel_filepath_all = f"{portfel_all_folder}/all_{date_str}.parquet"
            portfel_filepath_raw = f"{portfel_input_folder}/raw_{date_str}.parquet"

            if os.path.exists(portfel_filepath_svod):
                request.session[f"portfel_filepath_svod_{request.user.id}"] = portfel_filepath_svod
                print("data exist")
                messages.success(request, "Data is already processec, you can see results!")
                return redirect('portfel-result')
            elif os.path.exists(portfel_filepath_raw):
                print('we have not svod file but we already have raw data, so going to process')
                request.session[f'portfel_filepath_raw_{request.user.id}'] = portfel_filepath_raw
                messages.success(request, "Raw Data exist, but not svod, starting ...")
                return redirect('portfel-process')
            else:
                print('We have neither svod nor raw data, so connecting to db...')
                messages.success(request, "We have neither svod nor raw data, so connecting to db...")
                return redirect('portfel-get')
    else:
        form = forms.DateInputForm()
    return render(request, 'portfel/home.html', {'form':form})

def portfel_get(request):
    # Ensure directories exist
    os.makedirs(portfel_input_folder, exist_ok=True)
    os.makedirs(portfel_svod_folder, exist_ok=True)
    os.makedirs(portfel_all_folder, exist_ok=True)

    # Initialize control variables
    conn = None
    pq_writer = None
    file_written = False
    
    # Retrieve and parse date from session
    portfel_date_str = request.session.get(f"portfel_date_str_{request.user.id}")
    if portfel_date_str:
        try:
            selected_date = datetime.datetime.strptime(portfel_date_str, '%Y-%m-%d').date()
        except ValueError:
            messages.warning(request, "The Date selected is unfamiliar")
            return redirect('portfel-home')
    else:
        messages.warning(request, "No date found in session.")
        return redirect('portfel-home')

    # Setup the file path dynamically based on the date
    portfel_filepath_raw = f"{portfel_input_folder}/raw_{selected_date}.parquet"
    request.session[f"portfel_filepath_raw_{request.user.id}"] = portfel_filepath_raw
    
    # Check if the file already exists on disk
    if os.path.exists(portfel_filepath_raw):
        print('File exists, processing existing file.')
        messages.success(request, "The File already exists, so processing")
        return redirect('portfel-process')
    
    print('Getting Data from Oracle...')
    try:
        print("Connecting directly to Oracle data warehouse...")
        conn = common_functions.get_oracle_connection()
        
        print("Executing Portfel query and preparing data streams...")
        batch_generator = conn.fetch_df_batches(
            queries.best_portfel_script, 
            parameters={'pdate': selected_date},
            size=100000  # Stream in highly optimized 100k row chunks
        )
        
        print("Streaming rows directly into parquet file...")
        for odf in batch_generator:
            arrow_table = pa.table(odf)
            
            # Check if the query returned 0 rows on the very first batch
            if pq_writer is None and arrow_table.num_rows == 0:
                break  # Stops processing, file_written remains False
                
            # Initialize writer with the dynamic file path and arrow schema
            if pq_writer is None:
                pq_writer = pq.ParquetWriter(portfel_filepath_raw, arrow_table.schema)
                
            pq_writer.write_table(arrow_table)
            file_written = True
            print(f"Wrote batch of {arrow_table.num_rows} rows directly to disk")

    except Exception as e:
        import traceback
        traceback.print_exc()
        print('Error happened on Portfolio query:', e)
        messages.error(request, f"Database or extraction error occurred: {e}")
        return redirect('portfel-home')

    finally:
        # CRITICAL: Safely close resources under all conditions
        if pq_writer:
            pq_writer.close()
            print("Parquet file writer finalized and closed.")
        
        if conn:
            conn.close()
            print("Oracle database connection safely closed.")

    # Handle the empty dataset redirect condition cleanly
    if not file_written:
        messages.warning(request, 'The data could not be fetched since it is not available yet!')
        return redirect('portfel-home')

    # If file was successfully built, proceed to the processing route
    print(f"Portfel file created successfully at: {portfel_filepath_raw}")
    return redirect('portfel-process')

def portfel_process(request):
    portfel_filepath_raw = request.session.get(f"portfel_filepath_raw_{request.user.id}")
    try:
        df_raw = pd.read_parquet(portfel_filepath_raw)
        svod_df, all_df = process_portfel_best(df_raw, utils_path)
        portfel_date_str = request.session.get(f'portfel_date_str_{request.user.id}')
        portfel_filepath_svod = f"{portfel_svod_folder}/svod_{portfel_date_str}.parquet"
        portfel_filepath_all = f"{portfel_all_folder}/all_{portfel_date_str}.parquet"
        request.session[f"portfel_filepath_svod_{request.user.id}"] = portfel_filepath_svod
        if os.path.exists(portfel_filepath_svod):
            print('The file is already proceessed, skipping ...')
            messages.success(request, 'portfel is already svod')
            return redirect('portfel-result')
        else:
            print('filepath does not exist, creating ...')
            svod_df.to_parquet(portfel_filepath_svod)
            print("svod Svod file is saved")
            try:
                all_df.to_parquet(portfel_filepath_all)
                print("All File is saved")
            except Exception as e:
                print(f"All file is not saved: {e}")
            return redirect('portfel-result')
    except Exception as e:
        messages.error(request, f'{e} The database is not fetched since it is not yet available')
        return redirect('portfel-home')
    return render(request, 'portfel/process.html')

def portfel_result(request):
    selected_date = request.session.get(f"portfel_date_str_{request.user.id}")
    portfel_filepath_svod = request.session.get(f'portfel_filepath_svod_{request.user.id}')
    if selected_date is None and portfel_filepath is None:
        selected_date = common_functions.find_recent_date()
        portfel_date_str = str(selected_date)
        request.session[f"portfel_date_str_{request.user.id}"] = selected_date_str
        portfel_filepath_svod = f"{portfel_svod_folder}/svod_{portfel_date_str}.parquet"
        request.session[f"portfel_filepath_svod_{request.user.id}"] = portfel_filepath_svod
        
    if os.path.exists(portfel_filepath_svod):
        print(f'{portfel_filepath_svod} exists')
        try:
            df = pd.read_parquet(portfel_filepath_svod)  
            if df.empty:
                messages.warning(request, 'Df is empty')
                return redirect('portfel-home')
            
            grouped_rk = prepare_df(df, 'branch', 'rk')
            grouped_mk = prepare_df(df, 'branch', 'mk')
            grouped_kk = prepare_df(df, 'branch', 'kk')
            grouped_currency = prepare_df(df, 'currency')

            grouped_currency['brutto_bad']
            
            
            try:
                chart_rk  = generate_risk_chart(grouped_rk, group_col = 'branch', custom_color = '#1e3655')
                chart_mk  = generate_risk_chart(grouped_mk, group_col = 'branch', custom_color = '#174d39' )
                chart_kk  = generate_risk_chart(grouped_kk, group_col = 'branch', custom_color = '#5E4D21')
                
            except Exception as e:
                print(f"Error happened: {e}")
                messages.warning(request, f"Error happened during generating charts: {e}")
                return redirect('portfel-home')
            context = {
                "chart_rk": chart_rk,
                "chart_mk": chart_mk,
                "chart_kk": chart_kk
            }
            return render(request, 'portfel/result.html', context=context)
        except Exception as e:        
            print(f"Detailed error: {e}")  # Print the exact error message
            messages.warning(request, f"Cant read the df: {e}") 
            return redirect('portfel-home')
    else:
        messages.warning(request, 'Portfel is not ready, please select a date to start with')
        return redirect('portfel-home')



def download_portfel_summary(request):
    selected_date = request.session.get(f"portfel_date_str_{request.user.id}")
    selected_date_str = str(selected_date)
    portfel_filepath_svod = request.session.get(f'portfel_filepath_svod_{request.user.id}')
    if os.path.exists(portfel_filepath_svod):
        df = pd.read_parquet(portfel_filepath_svod)  
        if df.empty:
            messages.warning(request, 'Df is empty')
            return redirect('portfel-home')
        buffer = io.BytesIO()
        print(df.columns)
        if len(df.columns)==23:
                df.columns = ['Дата выдачы',"Филиаль","Регион","Банк филиали МФО раками","Валюта","Отделение","Описание кредита","ОКЕД","Является ли НПЛ?","Дни просрочки","Код категории качества",
                "Пол",'Тип контрагента','Юр./Физ.',"Основной кредит на балансе (эквивалент брутто)",'brutto_without_95413',"Остаток по кредиту на балансе (нетто)","Сумма резерва (эквивалент)","Ср. Процент",
                "Количество",'Категория Процент',"Стандарт качества"]
            
        #df['npl'] = df[['Основной кредит на балансе (эквивалент брутто)', 'Является ли НПЛ?']].apply(lambda x: x['Основной кредит на балансе (эквивалент брутто)'] if x['Является ли НПЛ?']=='yes' else 0, axis = 1)
        # Save DataFrame to Excel in the buffer
        with pd.ExcelWriter(buffer, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='svod')
        buffer.seek(0)
        
        # Create the response with the Excel file
        response = HttpResponse(
            buffer,
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response['Content-Disposition'] = f'attachment; filename="Portfel_{selected_date_str}.xlsx"'
        return response