from django.shortcuts import render
from django.db.models import Sum, Q, F
from django.http import JsonResponse, HttpResponseRedirect
from decimal import Decimal
from datetime import datetime, date
import time

from inventory.models import *
from sales.models import *
from expenses.models import *
from material.models import *
from store.models import *
from masters.models import *
from common.utils import *
from scheme.models import *
from claim_collect_upgrade.models import *
from stock.models import *

from company.models import company_table

def profit_loss(request):
    if 'user_id' in request.session:
        user_type = request.session.get('user_type')
        branch_id = request.session.get('branch_id')
        if user_type == 'stores' or user_type == 'admin': # checking access
            branch = branch_table.objects.filter(id=branch_id).first()
            company = company_table.objects.filter(status=1).first()
            image_url = 'https://biglitz.com/static/assets/img/logo/boys-logo.png'
            return render(request, 'profit_loss.html', {
                'branch': branch,
                'company': company,
                'image_url': image_url
            })
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")

def calculate_pl_data(branch_id, from_date, to_date):
    # ==========================================================
    # 1. TRADING ACCOUNT
    # ==========================================================
    
    # --- SALES ACCOUNTS ---
    sales_qs = sales_order_table.objects.filter(
        branch_id=branch_id,
        status=1, 
        is_active=1,
        inv_date__range=[from_date, to_date]
    )
    total_sales = sales_qs.aggregate(Sum('sub_total'))['sub_total__sum'] or 0
    
    # Stock Outward (Material Outward)
    stock_out_qs = material_outward_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        transfer_date__range=[from_date, to_date]
    )
    total_stock_outward = stock_out_qs.aggregate(Sum('sub_total'))['sub_total__sum'] or 0
    
    # Sales Return (Net)
    sales_ret_qs = sales_return_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        sr_date__range=[from_date, to_date]
    )
    total_sales_return = sales_ret_qs.aggregate(Sum('sub_total'))['sub_total__sum'] or 0
    
    # Gross Sales Subtotal = Sales + Outward - Return
    net_sales_group = Decimal(total_sales) + Decimal(total_stock_outward) - Decimal(total_sales_return)

    # --- PURCHASE ACCOUNTS ---
    # Purchases (Net)
    pur_qs = purchase_inward_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        pu_date__range=[from_date, to_date]
    )
    total_purchase = pur_qs.aggregate(Sum('sub_total'))['sub_total__sum'] or 0
    
    # Transfers (Material Inward)
    transfer_in_qs = material_inward_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        transfer_date__range=[from_date, to_date]
    )
    total_transfer_in = transfer_in_qs.aggregate(Sum('sub_total'))['sub_total__sum'] or 0
    
    # Purchase Return (Net)
    pur_ret_qs = purchase_return_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        pr_date__range=[from_date, to_date]
    )
    # Using aggregate on sub_total. Ensure pr_status is ignored if it's already filtered by general status=1
    total_purchase_return = pur_ret_qs.aggregate(Sum('sub_total'))['sub_total__sum'] or 0
    
    # Gross Purchase Subtotal = Purchase + Transfer - Return
    net_purchase_group = Decimal(total_purchase) + Decimal(total_transfer_in) - Decimal(total_purchase_return)
    
    # --- DISCOUNT FROM SUPPLIER (Debit Notes) ---
    claim_qs = tm_claim_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    )
    total_discount_from_supplier = claim_qs.aggregate(Sum('debit_amount'))['debit_amount__sum'] or 0
    
    # --- OPENING STOCK & CLOSING STOCK ---
    opening_stock_value = calculate_stock_valuation(branch_id, from_date, is_opening=True)
    closing_stock_value = calculate_stock_valuation(branch_id, to_date, is_opening=False)
    
    # --- GROSS PROFIT ---
    # Credit Side = Net Sales Group + Closing Stock
    total_trading_credit = net_sales_group + closing_stock_value
    # Debit Side = Opening Stock + Net Purchase Group + Discount from Supplier
    total_trading_debit_base = opening_stock_value + net_purchase_group + Decimal(total_discount_from_supplier)
    
    gross_profit = total_trading_credit - total_trading_debit_base

    # ==========================================================
    # 2. P&L ACCOUNT
    # ==========================================================
    
    # --- INDIRECT EXPENSES ---
    exp_qs = expense_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        date__range=[from_date, to_date]
    )
    
    indirect_expenses_list = []
    total_indirect_expenses = Decimal(0)
    
    # Group by expense type (master)
    exp_grouped = exp_qs.values('expense_id').annotate(total=Sum('amount'))
    
    for item in exp_grouped:
        e_id = item['expense_id']
        amount = Decimal(item['total'] or 0)
        e_name = getItemNameById(expense_master_table, e_id)
        indirect_expenses_list.append({
            'name': e_name,
            'amount': float(amount)
        })
        total_indirect_expenses += amount

    # --- NET PROFIT ---
    # Credit = Gross Profit (if positive)
    # Debit = Net Loss (if GP negative) + Indirect Expenses
    
    net_profit = gross_profit - total_indirect_expenses

    # Subtotals for display
    purchases_transfer = Decimal(total_purchase) + Decimal(total_transfer_in)
    sales_stock_outward = Decimal(total_sales) + Decimal(total_stock_outward)

    return {
        'trading': {
            'opening_stock': float(opening_stock_value),
            'sales': float(total_sales),
            'stock_outward': float(total_stock_outward),
            'sales_stock_outward': float(sales_stock_outward),
            'sales_return': float(total_sales_return),
            'net_sales_group': float(net_sales_group),
            'purchases': float(total_purchase),
            'transfer_in': float(total_transfer_in),
            'purchases_transfer': float(purchases_transfer),
            'purchase_return': float(total_purchase_return),
            'net_purchase_group': float(net_purchase_group),
            'discount_from_supplier': float(total_discount_from_supplier),
            'closing_stock': float(closing_stock_value),
            'gross_profit': float(gross_profit),
            'total_trading_dr': float(total_trading_debit_base + (gross_profit if gross_profit > 0 else 0)),
            'total_trading_cr': float(total_trading_credit + (abs(gross_profit) if gross_profit < 0 else 0)),
        },
        'pl': {
            'indirect_expenses': indirect_expenses_list,
            'total_indirect_expenses': float(total_indirect_expenses),
            'net_profit': float(net_profit),
            'total_pl_dr': float(max(0, -gross_profit) + total_indirect_expenses + (net_profit if net_profit > 0 else 0)),
            'total_pl_cr': float(max(0, gross_profit) + (abs(net_profit) if net_profit < 0 else 0))
        }
    }

def profit_loss_view(request):
    branch_id = request.session.get('branch_id')
    
    from_date_str = request.POST.get('from_date')
    to_date_str = request.POST.get('to_date')
    
    if not from_date_str or not to_date_str:
        return JsonResponse({'message': 'Invalid dates'})

    try:
        from_date = datetime.strptime(from_date_str, '%Y-%m-%d').date()
        to_date = datetime.strptime(to_date_str, '%Y-%m-%d').date()
    except ValueError:
        return JsonResponse({'message': 'Invalid date format'})

    data = calculate_pl_data(branch_id, from_date, to_date)
    return JsonResponse(data)

def calculate_stock_valuation(branch_id, target_date, is_opening=False):
    """
    Calculates the value of stock at a specific date.
    Value = Sum(Quantity * DB_Price) for all items.
    
    Logic:
    1. Get current stock (simplest) OR
    2. Start from 0 and replay all transactions up to target_date.
    
    Given no 'historical stock' snapshot table, Replay is necessary.
    
    Transactions affecting stock:
    (+) Opening Stock (initial)
    (+) Purchase Inward
    (+) Material Inward
    (+) Sales Return
    (-) Sales
    (-) Purchase Return
    (-) Material Outward
    
    All must be <= target_date.
    If is_opening is True, we want stock at START of that day (exclude transactions of that day).
    If is_opening is False, we want stock at END of that day (include transactions of that day).
    """
    
    date_filter = {}
    if is_opening:
        # Strictly less than target_date
        date_operator = 'lt'
    else:
        # Less than or equal to target_date
        date_operator = 'lte'
        
    def get_qs_sum(model, date_field, qty_field='quantity'):
        filter_kwargs = {
            'branch_id': branch_id,
            'status': 1,
            'is_active': 1,
            f"{date_field}__{date_operator}": target_date
        }
        # We need to perform aggregation per item to multiply by price.
        # But aggregate across all items x price is faster if we do it in python or DB complex query.
        # Since DB price varies per item, we group by item.
        return model.objects.filter(**filter_kwargs).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum(qty_field))

    # 1. Opening Stock Table (Initial Loads)
    # opening_stock_table usually has 'date'.
    # 1. Opening Stock Table (Initial Loads)
    # opening_stock_table usually has 'date'.
    # We use date__lte even for opening stock to ensure we catch migration entries made on the start date.
    qs_os = opening_stock_table.objects.filter(
        branch_id=branch_id,
        status=1,
        is_active=1,
        date__lte=target_date
    ).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum('quantity'))
    
    # 2. Purchase Inward
    # child_purchase_inward_table links to tm_purchase_inward which has pu_date
    qs_pi = child_purchase_inward_table.objects.filter(
        branch_id=branch_id, status=1, is_active=1,
        tm_pu_id__in=purchase_inward_table.objects.filter(
            branch_id=branch_id, status=1, 
            **{f"pu_date__{date_operator}": target_date}
        ).values('id')
    ).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum('quantity'))
    
    # 3. Sales
    qs_sales = child_sales_order_table.objects.filter(
        branch_id=branch_id, status=1, is_active=1,
        tm_sales_id__in=sales_order_table.objects.filter(
            branch_id=branch_id, status=1, 
            **{f"inv_date__{date_operator}": target_date}
        ).values('id')
    ).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum('quantity'))

    # 4. Sales Return
    qs_sr = child_sales_return_table.objects.filter(
        branch_id=branch_id, status=1, is_active=1,
        tm_return_id__in=sales_return_table.objects.filter(
            branch_id=branch_id, status=1, 
            **{f"sr_date__{date_operator}": target_date}
        ).values('id')
    ).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum('quantity'))
    
    # 5. Purchase Return
    qs_pr = child_purchase_return_table.objects.filter(
        branch_id=branch_id, status=1, is_active=1,
        tm_return_id__in=purchase_return_table.objects.filter(
            branch_id=branch_id, status=1, 
            **{f"pr_date__{date_operator}": target_date}
        ).values('id')
    ).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum('quantity'))
    
    # 6. Material In
    qs_mi = child_material_inward_table.objects.filter(
        branch_id=branch_id, status=1, is_active=1,
        tm_material_id__in=material_inward_table.objects.filter(
            branch_id=branch_id, status=1, 
            **{f"transfer_date__{date_operator}": target_date}
        ).values('id')
    ).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum('quantity'))
    
    # 7. Material Out
    qs_mo = child_material_outward_table.objects.filter(
        branch_id=branch_id, status=1, is_active=1,
        tm_material_id__in=material_outward_table.objects.filter(
            branch_id=branch_id, status=1, 
            **{f"transfer_date__{date_operator}": target_date}
        ).values('id')
    ).annotate(sub_category_id=F('subcategory_id')).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id').annotate(total_qty=Sum('quantity'))

    # Aggregate all into a comprehensive dictionary
    # Key: (sub, brand, model, var, col) -> Qty
    stock_map = {}
    
    def merge_qs(qs, factor=1):
        for item in qs:
            key = (item['sub_category_id'], item['brand_id'], item['model_id'], item['variant_id'], item['color_id'])
            stock_map[key] = stock_map.get(key, 0) + (item['total_qty'] * factor)

    merge_qs(qs_os, 1)
    merge_qs(qs_pi, 1)
    merge_qs(qs_mi, 1)
    merge_qs(qs_sr, 1)
    
    merge_qs(qs_sales, -1)
    merge_qs(qs_pr, -1)
    merge_qs(qs_mo, -1)
    
    # Calculate Value
    total_val = Decimal(0)
    
    # Fetch all item prices once? Or per item.
    # We need DB Price (Purchase Price).
    # Optimization: Filter Item Table by keys present in stock_map checking > 0
    
    keys_with_stock = [k for k, v in stock_map.items() if v > 0]
    
    if not keys_with_stock:
        return Decimal(0)
        
    # This is slightly inefficient if many items, but best we can do without a single item lookup
    # We can fetch all items and make a lookup dict.
    
    all_items = item_table.objects.filter(status=1).values('sub_category_id', 'brand_id', 'model_id', 'variant_id', 'color_id', 'db_price')
    price_lookup = {
        (i['sub_category_id'], i['brand_id'], i['model_id'], i['variant_id'], i['color_id']): Decimal(i['db_price'] or 0)
        for i in all_items
    }
    
    for key, qty in stock_map.items():
        if qty > 0:
            price = price_lookup.get(key, Decimal(0))
            total_val += qty * price
            
    return total_val