4. balancer

def categorize_account(account_code):
    """
    Categorizes a 5-digit Uzbek banking account code into logical dashboard buckets.
    Based on МЮ №773-17.
    """
    acc_str = str(account_code)[:5]
    prefix_1 = acc_str[:1]
    prefix_2 = acc_str[:2]
    prefix_3 = acc_str[:3]

    category = "Неизвестно"
    sub_category = "Прочее"

    # 1. ASSETS (Активы)
    if prefix_1 == '1':
        category = "Активы (Assets)"
        if prefix_3 in ['101']:
            sub_category = "Наличные средства (Cash)"
        elif prefix_3 in ['103', '105']:
            sub_category = "Средства в ЦБ и банках"
        elif prefix_2 in ['11', '12', '13', '14', '15']:
            sub_category = "Кредитный портфель (Loans)"
        elif prefix_2 in ['16']:
            sub_category = "Основные средства и НМА"
        else:
            sub_category = "Прочие активы"

    # 2. LIABILITIES (Обязательства)
    elif prefix_1 == '2':
        category = "Обязательства (Liabilities)"
        if prefix_3 in ['202', '204', '206', '226']:
            sub_category = "Депозиты клиентов (Deposits)"
        elif prefix_3 in ['210', '216']:
            sub_category = "Средства других банков"
        elif prefix_3 in ['220']:
            sub_category = "Кредиты к оплате (Borrowings)"
        else:
            sub_category = "Прочие обязательства"

    # 3. EQUITY (Капитал)
    elif prefix_1 == '3':
        category = "Капитал (Equity)"
        if prefix_3 in ['303']:
            sub_category = "Уставный капитал"
        elif prefix_3 in ['312']:
            sub_category = "Нераспределенная прибыль"
        else:
            sub_category = "Прочий капитал"
            
    # 4. INCOME & EXPENSES (if they exist in your daily balans before closing)
    elif prefix_1 == '4':
        category = "Доходы (Income)"
        sub_category = "Доходы"
    elif prefix_1 == '5':
        category = "Расходы (Expenses)"
        sub_category = "Расходы"

    return category, sub_category

def risk_combined_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')

    # Read Data
    df = pd.read_parquet(balans_filepath)
    df.columns = ['arcdate', 'account', 'amount']
    
    # 1. Apply mapping
    # Create new columns for Category and SubCategory
    df[['category', 'sub_category']] = df['account'].apply(
        lambda acc: pd.Series(categorize_account(acc))
    )

    # 2. Fix the signs (Liabilities and Equity are usually negative in a balanced DB)
    # We take the absolute value so they display nicely on the dashboard
    df['amount_abs'] = df['amount'].abs()

    # 3. Calculate Totals
    total_assets = df[df['category'] == 'Активы (Assets)']['amount_abs'].sum()
    total_liabilities = df[df['category'] == 'Обязательства (Liabilities)']['amount_abs'].sum()
    total_equity = df[df['category'] == 'Капитал (Equity)']['amount_abs'].sum()

    # 4. Group by Sub-Category to get the structure (for CSS Progress bars)
    structure_df = df.groupby(['category', 'sub_category'])['amount_abs'].sum().reset_index()
    
    # Calculate share percentage
    def get_share(row):
        if row['category'] == 'Активы (Assets)' and total_assets > 0:
            return (row['amount_abs'] / total_assets) * 100
        elif row['category'] == 'Обязательства (Liabilities)' and total_liabilities > 0:
            return (row['amount_abs'] / total_liabilities) * 100
        elif row['category'] == 'Капитал (Equity)' and total_equity > 0:
            return (row['amount_abs'] / total_equity) * 100
        return 0

    structure_df['share_pct'] = structure_df.apply(get_share, axis=1)
    
    # Sort so biggest shares are on top
    structure_df = structure_df.sort_values(by=['category', 'amount_abs'], ascending=[True, False])

    # Convert to dictionary for the template
    context = {
        'date_str': request.session.get(f"balans_date_str_{request.user.id}"),
        'total_assets': f"{total_assets:,.0f}".replace(",", " "),
        'total_liabilities': f"{total_liabilities:,.0f}".replace(",", " "),
        'total_equity': f"{total_equity:,.0f}".replace(",", " "),
        'assets_structure': structure_df[structure_df['category'] == 'Активы (Assets)'].to_dict('records'),
        'liabilities_structure': structure_df[structure_df['category'] == 'Обязательства (Liabilities)'].to_dict('records'),
        'equity_structure': structure_df[structure_df['category'] == 'Капитал (Equity)'].to_dict('records'),
    }
    
    return render(request, 'risk/combined_home.html', context)