from django.shortcuts import render
from django.db.models import Q
from django.http import HttpResponseRedirect, JsonResponse
from sales.models import *
from company.models import *
from masters.models import *
from common.utils import *
from inventory.models import *
from supplier.models import *
from material.models import *
from stock.models import *
from scheme.models import *

def commission_report(request):
    if 'user_id' in request.session:
        user_type = request.session.get('user_type')
        branch_id = request.session.get('branch_id')
        role_id = request.session.get('role_id')      
        branch = selectList(branch_table, order_by='name')
        
        return render(request, 'commission.html', {
            'branch': branch,
        })
    else:
        return HttpResponseRedirect("/")

def ajax_commission_report(request):
    role_id = request.session.get('role_id')
    is_ho = request.session.get('is_ho')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)

    # Note: Using sales_tax_report permission as a placeholder for now
    has_access, error_message = check_user_access(role_id, 'sales_tax_report', "read")
    if not has_access:
        return JsonResponse({
            'message': 'permission',
            'error_message': 'You do not have permission to view commission report details'
        })

    branch_id = request.POST.get('branch_id')
    from_date = request.POST.get('from_date')
    to_date = request.POST.get('to_date')

    # 1️⃣ Sales Filter
    # -----------------------------
    query = Q(status=1, current_fy=financial_year)

    if from_date and to_date:
        query &= Q(inv_date__range=[from_date, to_date])

    if branch_id:
        query &= Q(branch_id=branch_id)

    sales_ids = sales_order_table.objects.filter(query).values_list('id', flat=True)

    # 2️⃣ Fetch Child Sales Records with Invoice Date
    # -----------------------------
    child_sales = child_sales_order_table.objects.filter(tm_sales_id__in=sales_ids, status=1).order_by('-id')

    # 3️⃣ Master Data Cache for efficiency
    # -----------------------------
    branches = {b.id: b.name for b in branch_table.objects.filter(is_active=1)}

    formatted = []
    for i, row in enumerate(child_sales):
        # Get inv_date from tm_sales
        tm_sale = sales_order_table.objects.filter(id=row.tm_sales_id).first()
        inv_date = tm_sale.inv_date if tm_sale else None

        # Price Drop Logic
        price_drop_amount = 0
        if inv_date and row.imei_no:
            # Find the latest price history record before or on inv_date
            latest_price_tm = price_history_table.objects.filter(
                from_date__lte=inv_date,
                status=1
            ).order_by('-from_date').first()

            if latest_price_tm:
                # Check child price history with imei
                price_child = tx_price_history_table.objects.filter(
                    tm_price_id=latest_price_tm.id,
                    imei_no=row.imei_no,
                    status=1
                ).first()
                if price_child:
                    price_drop_amount = float(price_child.mop_after_discount) if price_child.mop_after_discount else 0

        # Purchase Price Lookup
        purchase_price = 0
        if row.imei_no:
            # 1. Purchase Inward
            pinward = child_purchase_inward_table.objects.filter(imei_no=row.imei_no, status=1).order_by('-id').first()
            if pinward:
                purchase_price = float(pinward.fsp) if pinward.fsp else (float(pinward.db_price) if pinward.db_price else 0)
            
            # 2. Opening Stock
            if not purchase_price:
                opening = opening_stock_table.objects.filter(imei_no=row.imei_no, status=1).order_by('-id').first()
                if opening:
                    purchase_price = float(opening.fsp) if opening.fsp else (float(opening.db_price) if opening.db_price else 0)
            
            # 3. Material Inward
            if not purchase_price:
                mat_in = child_material_inward_table.objects.filter(imei_no=row.imei_no, status=1).order_by('-id').first()
                if mat_in:
                    purchase_price = float(mat_in.fsp) if mat_in.fsp else (float(mat_in.db_price) if mat_in.db_price else 0)
            
            # 4. Sales Return
            if not purchase_price:
                sreturn = child_sales_return_table.objects.filter(imei_no=row.imei_no, status=1).order_by('-id').first()
                if sreturn:
                    purchase_price = float(sreturn.fsp) if sreturn.fsp else (float(sreturn.db_price) if sreturn.db_price else 0)

        # Fetch SKU text from item table
        item = item_table.objects.filter(
            sub_category_id=row.subcategory_id,
            brand_id=row.brand_id,
            model_id=row.model_id,
            variant_id=row.variant_id,
            color_id=row.color_id,
            status=1
        ).first()

        sku_text = item.sku_text if item else ''

        formatted.append({
            'id': i + 1,
            'branch': branches.get(row.branch_id, ''),
            'sku': sku_text,
            'imei': row.imei_no if row.imei_no else '',
            'sales_value': format(float(row.amount) if row.amount else 0, ".2f"),
            'special_scheme': format(float(row.special_amount) if row.special_amount else 0, ".2f"),
            'price_drop': format(price_drop_amount, ".2f"),
            'fsp_purchase_price': format(purchase_price, ".2f"),
            'front_margin': '',
            'slabwise_scheme': '',
            'upgrade': '',
            'boys_scheme': '',
            'backend_margin': '',
            'total_margin_1': '',
            'gst_deduction': '',
            'total_margin_2': '',
        })




    return JsonResponse({'data': formatted})