3.view_new

import os
import io
import datetime
import pandas as pd
import pyarrow as pa
import pyarrow.parquet as pq

from django.shortcuts import render, redirect
from django.contrib import messages
from django.http import HttpResponse
from pathlib import Path

from . import forms
from . import queries
from functions import common_functions

BASE_DIR = Path(__file__).resolve().parent.parent
balans_svod_folder = BASE_DIR / "datasets" / "balans" / "svod"

# ==========================================
# 1. HELPER: DATA CALCULATION & STRUCTURE
# ==========================================
def calculate_blockc_metrics(df):
    """
    This calculates all the math for Block C.
    Returns a dictionary of {row_code: value}.
    You can easily import and reuse this function in your Svod page!
    """
    # 1. Pre-calculate frequently used accounts
    acc_30312 = df.loc[df['account'] == '30312', 'amount'].sum() if not df.empty else 0
    acc_30318 = df.loc[df['account'] == '30318', 'amount'].sum() if not df.empty else 0
    
    # Placeholder calculations - replace with actual logic later
    val_10111 = 502385173
    val_10112 = 0
    
    # 2. Map calculated values to unique 5-digit row codes
    metrics = {
        "10111": val_10111,
        "10112": val_10112,
        "10110": val_10111 - val_10112, # 1.1 = a - б
        "10120": 349727222,             # 1.2
        "10130": 6163146509,            # 1.3
        "10131": 4199167083,            # 1.3.a
        "10132": 1963979426,            # 1.3.б
        "10162": 23061469,              # 1.6.б
        "20000": 8255040730,            # V. ЖАМИ РЕГУЛЯТИВ КАПИТАЛ
        "90010": "116,15%",             # Ratios (can be strings too)
    }
    return metrics

def get_blockc_structure():
    """
    This defines the visual structure of the table.
    We just link the row_code to grab the calculated value automatically.
    """
    return [
        {"code": "10000", "no": "", "name": "I ДАРАЖАЛИ КАПИТАЛ", "is_header": True, "level": 0},
        {"code": "10100", "no": "1", "name": "I ДАРАЖАЛИ АСОСИЙ КАПИТАЛ", "is_header": True, "level": 1},
        {"code": "10110", "no": "1.1.", "name": "Оддий акциялар, нетто", "is_header": False, "level": 3, "formula": "1.1 = a - б"},
        {"code": "10111", "no": "a", "name": "Тўлиқ тўланган оддий акциялар", "is_header": False, "level": 4},
        {"code": "10112", "no": "б", "name": "Минус: Қайта сотиб олинган оддий акциялар", "is_header": False, "level": 4},
        {"code": "10120", "no": "1.2.", "name": "Қўшимча капитал (оддий)", "is_header": False, "level": 3},
        {"code": "10130", "no": "1.3.", "name": "Тақсимланмаган фойда (зарар)", "is_header": False, "level": 3},
        {"code": "10131", "no": "a", "name": "Капитал ва бошқа захиралар", "is_header": False, "level": 4},
        {"code": "10132", "no": "б", "name": "Тақсимланмаган фойда", "is_header": False, "level": 4},
        {"code": "10162", "no": "б", "name": "Муддати узайтирилган солиқ талаблари", "is_header": False, "level": 4},
        {"code": "20000", "no": "V", "name": "ЖАМИ РЕГУЛЯТИВ КАПИТАЛ", "is_header": True, "level": 1},
        {"code": "90010", "no": "VII", "name": "I ДАРАЖАЛИ АСОСИЙ КАПИТАЛНИНГ МОНАДЛИК КОЭФФИЦИЕНТИ (Л/I)", "is_header": True, "level": 2},
        # Add the rest of your structure here following this pattern
    ]

# ==========================================
# 2. CORE VIEWS
# ==========================================
def risk_home(request):
    # Keep this exactly as you had it
    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"balans_date_str_{request.user.id}"] = date_str
            balans_filepath_svod = f"{balans_svod_folder}/svod_{date_str}.parquet"
            if os.path.exists(balans_filepath_svod):
                request.session[f"balans_filepath_svod_{request.user.id}"] = balans_filepath_svod
                messages.success(request, "Data is already processed, you can see results!")
                return redirect('risk-result')
            else:
                return redirect('risk-get')
    else:
        form = forms.DateInputForm()
    return render(request, 'risk/home.html', {'form': form})

# (Keep your risk_get_data view as it was, it is perfectly fine!)

def risk_result(request):
    """
    Cleaned up the redundant file checking.
    """
    selected_date = request.session.get(f"balans_date_str_{request.user.id}")
    balans_filepath = request.session.get(f"balans_filepath_svod_{request.user.id}")

    if not selected_date or not balans_filepath:
        # Fallback if session is empty
        selected_date = str(common_functions.find_recent_date())
        balans_filepath = f"{balans_svod_folder}/svod_{selected_date}.parquet"
        request.session[f"balans_date_str_{request.user.id}"] = selected_date
        request.session[f"balans_filepath_svod_{request.user.id}"] = balans_filepath

    if not os.path.exists(balans_filepath):
        messages.warning(request, "Data file missing. Please query the date again.")
        return redirect('risk-home')

    # Optional: Parse date nicely for the template (e.g., '28 Aug 2026')
    try:
        date_obj = datetime.datetime.strptime(selected_date, '%Y-%m-%d')
        formatted_date = date_obj.strftime('%d.%m.%Y')
    except:
        formatted_date = selected_date

    return render(request, 'risk/result.html', {'balans_date_str': formatted_date})

def risk_blockc_home(request):
    balans_filepath = request.session.get(f"balans_filepath_svod_{request.user.id}")
    
    if not balans_filepath or not os.path.exists(balans_filepath):
        messages.warning(request, "No data available. Please select a date first.")
        return redirect('risk-home')

    df = pd.read_parquet(balans_filepath)
    df.columns = ['arcdate', 'account', 'amount']
    
    # 1. Calculate the metrics (math logic)
    metrics = calculate_blockc_metrics(df)
    
    # 2. Get the structure (UI logic) and inject values
    table_data = get_blockc_structure()
    for row in table_data:
        val = metrics.get(row['code'], "")
        if isinstance(val, (int, float)) and val != "":
            # Format nicely with spaces
            row['value'] = f"{val:,}".replace(",", " ")
        else:
            row['value'] = val

    context = {
        'bank_name': 'АТБ Капиталбанк',
        'report_date': request.session.get(f"balans_date_str_{request.user.id}"),
        'table_data': table_data
    }
    return render(request, 'risk/blockc_home.html', context)

# ==========================================
# 3. EXCEL DOWNLOAD VIEW (New Backend Export)
# ==========================================
def risk_blockc_excel(request):
    balans_filepath = request.session.get(f"balans_filepath_svod_{request.user.id}")
    if not balans_filepath or not os.path.exists(balans_filepath):
        messages.error(request, 'No data to download.')
        return redirect('risk-home')

    df = pd.read_parquet(balans_filepath)
    df.columns = ['arcdate', 'account', 'amount']
    
    metrics = calculate_blockc_metrics(df)
    table_data = get_blockc_structure()
    
    # Build a flat list for the Excel dataframe
    excel_rows = []
    for row in table_data:
        excel_rows.append({
            "№": row['no'],
            "Кўрсаткичлар": row['name'],
            "Жами капитал (Сумда)": metrics.get(row['code'], "")
        })
        
    export_df = pd.DataFrame(excel_rows)

    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
        export_df.to_excel(writer, sheet_name='Block_C', index=False)
        # Auto adjust column width
        worksheet = writer.sheets['Block_C']
        worksheet.set_column('A:A', 8)
        worksheet.set_column('B:B', 60)
        worksheet.set_column('C:C', 25)

    buffer.seek(0)
    response = HttpResponse(
        buffer.read(), 
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    # Give it a dynamic name based on date
    date_str = request.session.get(f"balans_date_str_{request.user.id}", "date")
    response['Content-Disposition'] = f'attachment; filename="BlockC_Report_{date_str}.xlsx"'
    
    return response

# ... (Keep your other empty views for appetite, limit, etc.)