from django.shortcuts import render
from django.db.models import Sum
from decimal import Decimal
from django.http import HttpResponse,HttpResponseRedirect,JsonResponse,FileResponse

from store.models import * 
from expenses.models import *
from inventory.models import *
from sales.models import *
from masters.models import *
from claim_collect_upgrade.models import *
from voucher.models import *
from common.utils import *
from material.models import *
from django.db.models import Q 
from common.utils import *


def get_current_balance(branch_id, financial_year):

    # opening_balance = branch_table.objects.filter(
    #     id=branch_id,
    #     status=1,
    #     is_active=1
    # ).values_list('captial', flat=True).first() or Decimal('0.00')

    # -------- CASH COMPONENTS --------  

    # 1. Sales (Cash In)
    sales_cash = transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        payment_type='cash',
        status=1,
        is_active=1
    ).aggregate(total=Sum('amount'))['total'] or Decimal('0.00')
    
    # 2. Purchase Inward (Cash Out)
    purch_cash = purchase_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).aggregate(total=Sum('cash'))['total'] or Decimal('0.00')

    # 3. Purchase Return (Cash In) - Both pending and approved affect balance, but only if they have IMEIs
    approved_return_ids = child_purchase_return_table.objects.exclude(
        Q(imei_no__isnull=True) | Q(imei_no='')
    ).values_list('tm_return_id', flat=True)

    purch_ret_cash = purchase_return_table.objects.filter(
        id__in=approved_return_ids,
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        pr_status__in=['pending', 'approved', 'Approved'],
        is_active=1
    ).aggregate(total=Sum('cash'))['total'] or Decimal('0.00')

    # 4. Expenses (Cash Out)
    exp_cash = expense_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).aggregate(total=Sum('cash'))['total'] or Decimal('0.00')

    # 5. Sales Return (Cash Out)
    sales_ret_cash = return_transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_type='cash'
    ).aggregate(total=Sum('amount'))['total'] or Decimal('0.00')

    # 6. Contra
    contra_bank_to_cash = contra_sales_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        payment_type='bank_to_cash',
        is_active=1,
        status=1
    ).aggregate(total=Sum('amount'))['total'] or Decimal('0.00')

    contra_cash_to_bank = contra_sales_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        payment_type='cash_to_bank',
        is_active=1,
        status=1
    ).aggregate(total=Sum('amount'))['total'] or Decimal('0.00')

    # 7. Material Inward (Cash Out)
    material_cash = material_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        cash__gt=0
    ).aggregate(total=Sum('cash'))['total'] or Decimal('0.00')

    # 8. Claims (Cash In)
    claims_cash = tm_claim_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).aggregate(total=Sum('total_amount'))['total'] or Decimal('0.00')
    
    # 9. Upgrades (Cash In)
    upgrades_cash = tm_upgrade_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).aggregate(total=Sum('total_amount'))['total'] or Decimal('0.00')


    # Initialize cash_balance
    cash_balance = Decimal('0.00')

    # ---------- VOUCHERS (CASH SIDE) ----------
    voucher_qs = voucher_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_mode='cash'
    )

    for v in voucher_qs:
        if v.voucher_type in ['receipt', 'cash_receipt']:
            cash_balance += Decimal(v.receivable_amount or 0)
        elif v.voucher_type in ['payment', 'cash_payment']:
            cash_balance -= Decimal(v.payable_amount or 0)
   
    # Convert all to Decimal
    # opening_balance = d2(opening_balance)
    sales_cash = d2(sales_cash)
    purch_cash = d2(purch_cash)
    purch_ret_cash = d2(purch_ret_cash)
    exp_cash = d2(exp_cash)
    sales_ret_cash = d2(sales_ret_cash)
    contra_bank_to_cash = d2(contra_bank_to_cash)
    contra_cash_to_bank = d2(contra_cash_to_bank)
    material_cash = d2(material_cash)
    claims_cash = d2(claims_cash)
    upgrades_cash = d2(upgrades_cash)

   
    base_cash = (
        # opening_balance +
        sales_cash +
        purch_ret_cash +
        claims_cash +
        upgrades_cash +
        contra_bank_to_cash -
        purch_cash -
        exp_cash -
        sales_ret_cash -
        material_cash -
        contra_cash_to_bank
    )
    
    # Add base cash to voucher movements
    cash_balance += base_cash 

    # -------- BANK --------
    bank_data = get_bank_balances(branch_id, financial_year)

    total_bank_balance = Decimal(bank_data['total_bank_balance'])
    bank_wise = bank_data['bank_list']

    current_balance = cash_balance + total_bank_balance

    return {
        'current_balance': round(current_balance, 2),
        'cash_balance': round(cash_balance, 2),
        'bank_balance': round(total_bank_balance, 2),
        'bank_wise': bank_wise
    }

def check_available_balance(branch_id, financial_year, cash_amount, bank_amount, bank_id, exclude_expense_id=None):
    """
    Generic function to check if enough cash and bank balance is available.
    """
    balances = get_current_balance(branch_id, financial_year)
    total_cash = Decimal(str(balances['cash_balance']))
    
    # If editing, we should add back the current expense's values to see true availability
    if exclude_expense_id:
        try:
            old_exp = expense_table.objects.get(id=exclude_expense_id)
            total_cash += d2(old_exp.cash)
            # We will handle bank below
        except expense_table.DoesNotExist:
            pass

    # 1. Check Cash
    if cash_amount > total_cash:
        return False, "Available cash is less"

    # 2. Check Bank
    if bank_amount > 0:
        if not bank_id:
            return False, "Please select a bank"
        
        target_bank_id = int(bank_id)
        bank_list = balances['bank_wise']
        available_bank_bal = Decimal('0.00')
        
        # Find the specific bank balance
        for b in bank_list:
            if b['bank_id'] == target_bank_id:
                available_bank_bal = Decimal(str(b['balance']))
                break
        
        # If editing, add back the old bank amount IF it was for the same bank
        if exclude_expense_id:
            try:
                old_exp = expense_table.objects.get(id=exclude_expense_id)
                if old_exp.bank_id == target_bank_id:
                    available_bank_bal += d2(old_exp.bank)
            except expense_table.DoesNotExist:
                pass

        if bank_amount > available_bank_bal:
            return False, "Available bank balance is less"

    return True, ""

from decimal import Decimal

def d2(val):
    return Decimal(str(val or "0")).quantize(Decimal("0.00"))


from decimal import Decimal
from django.db.models import Sum
from decimal import Decimal
from django.db.models import Sum

def get_bank_balances(branch_id, financial_year):
    bank_balances = {}
    
    # Get HO status and branch info
    is_ho = 0
    branch = branch_table.objects.filter(id=branch_id).first()
    if branch:
        is_ho = branch.is_ho or 0

    # 0. Load Initial Balances
    allowed_bank_ids = set()
    
    if is_ho == 1:
        # HO: Load non-default banks from bank_table
        banks_master = bank_table.objects.filter(status=1)
        for b in banks_master:
            bank_balances[b.id] = d2(b.opening)
            allowed_bank_ids.add(b.id)
    else:
        # Branch: Load only the default bank from branch_table
        banks_master = bank_table.objects.filter(status=1, is_default=1)
        for b in banks_master:
            bank_balances[b.id] = d2(b.opening)
            allowed_bank_ids.add(b.id)

    # ---------- SALES (BANK IN) ----------
    sales = transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        payment_type='bank',
        status=1,
        is_active=1
    ).values('bank_id').annotate(total=Sum('amount'))

    for row in sales:
        bank_id = row['bank_id']
        bank_balances[bank_id] = bank_balances.get(
            bank_id, Decimal('0.00')
        ) + d2(row['total'])

    # ---------- TM COLLECTION (BANK IN) ----------
    collections = tm_collection_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        bank_id__gt=0
    ).values('bank_id').annotate(total=Sum('receipt'))

    for row in collections:
        bank_id = row['bank_id']
        bank_balances[bank_id] = bank_balances.get(
            bank_id, Decimal('0.00')
        ) + d2(row['total'])

    # ---------- EXPENSES (BANK OUT) ---------- (Has bank_id)
    expenses = expense_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).values('bank_id').annotate(total=Sum('bank'))
    
    for row in expenses:
        bank_id = row['bank_id']
        bank_balances[bank_id] = bank_balances.get(
            bank_id, Decimal('0.00')
        ) - d2(row['total'])

    # ---------- SALES RETURN (BANK OUT) ---------- (Has bank_id)
    sales_returns = return_transaction_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        payment_type='bank',
        status=1,
        is_active=1
    ).values('bank_id').annotate(total=Sum('amount'))
    
    for row in sales_returns:
        bank_id = row['bank_id']
        bank_balances[bank_id] = bank_balances.get(
            bank_id, Decimal('0.00')
        ) - d2(row['total'])

    # ---------- CONTRA ----------
    contra = contra_sales_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).values('bank_id', 'payment_type').annotate(total=Sum('amount'))

    for row in contra:
        bank_id = row['bank_id']
        amount = d2(row['total'])
        bank_balances.setdefault(bank_id, Decimal('0.00'))

        if row['payment_type'] == 'cash_to_bank':
            bank_balances[bank_id] += amount
        elif row['payment_type'] == 'bank_to_cash':
            bank_balances[bank_id] -= amount

    # ---------- VOUCHERS (BANK SIDE) ----------
    voucher_qs = voucher_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1,
        payment_mode='bank'
    )

    for v in voucher_qs:
        bank_id = v.bank_id
        bank_balances.setdefault(bank_id, Decimal('0.00'))
        if v.voucher_type in ['receipt', 'cash_receipt']:
            bank_balances[bank_id] += Decimal(v.receivable_amount or 0)
        elif v.voucher_type in ['payment', 'cash_payment']:
            bank_balances[bank_id] -= Decimal(v.payable_amount or 0)

    # ---------- UNLISTED BANK MOVEMENT (Purchases \u0026 Material Inwards) ----------
    # Since Purchase Inward/Return and Material Inward tables don't always track bank_id.
    
    purch_bank_out = purchase_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).aggregate(total=Sum('bank'))['total'] or 0

    approved_return_ids = child_purchase_return_table.objects.exclude(
        Q(imei_no__isnull=True) | Q(imei_no='')
    ).values_list('tm_return_id', flat=True)
    
    purch_ret_bank_in = purchase_return_table.objects.filter(
        id__in=approved_return_ids,
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        pr_status__in=['pending', 'approved', 'Approved'],
        is_active=1
    ).aggregate(total=Sum('bank'))['total'] or 0

    material_bank_out = material_inward_table.objects.filter(
        branch_id=branch_id,
        current_fy=financial_year,
        status=1,
        is_active=1
    ).aggregate(total=Sum('bank'))['total'] or 0
    
    unlisted_adjustment = d2(purch_ret_bank_in) - d2(purch_bank_out) - d2(material_bank_out)
    
    # ---------- BANK NAMES ----------
    bank_ids = [bid for bid in bank_balances.keys() if bid] # Filter None
    bank_map = {
        b.id: b.name
        for b in bank_table.objects.filter(id__in=bank_ids, status=1)
    }

    # ---------- FINAL RESPONSE ----------
    bank_list = []
    total_bank_balance = Decimal('0.00')

    # 1. Add all tracked bank balances to total
    for bank_id, balance in bank_balances.items():
        if bank_id and bank_id in allowed_bank_ids:
            total_bank_balance += balance
            bank_list.append({
                'bank_id': bank_id,
                'bank_name': bank_map.get(bank_id, 'Unknown Bank'),
                'balance': round(balance, 2)
            })
        else:
            # Transactions with bank_id=0 or None (lost/untracked bank IDs)
            total_bank_balance += balance
    
    # 2. Add unlisted adjustment (tables without bank_id field) to total
    total_bank_balance += unlisted_adjustment
    
    return {
        'bank_list': bank_list,
        'total_bank_balance': round(total_bank_balance, 2)
    }