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 sales_analysis(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')
        customers = selectList(customer_table, order_by='name')
        subcategory = selectList(sub_category_table, order_by='name')
        brands = selectList(brand_table, order_by='name')
        models = selectList(model_table, order_by='name')
        variants = selectList(variant_table, order_by='name')
        colors = selectList(color_table, order_by='name')
        
        return render(request, 'sales_analysis.html', {
            'customers': customers,
            'subcategory': subcategory,
            'brands': brands,
            'models': models,
            'variants': variants,
            'colors': colors,
            'branch': branch,
        })
    else:
        return HttpResponseRedirect("/")
    


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

    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 sales tax report details'
        })

    customer_id = request.POST.get('customer_id')
    branch_id = request.POST.get('branch_id')
    subcategory_id = request.POST.get('subcategory_id')
    brand_id = request.POST.get('brand_id')
    model_id = request.POST.get('model_id')
    variant_id = request.POST.get('variant_id')
    color_id = request.POST.get('color_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 customer_id:
        query &= Q(customer_id=customer_id)

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

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

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

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

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

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

    

    formatted = []   
    

    formatted.append({

        'id':'',
        'branch':'',
        'sku':'',
        'subcategory':'',
        'brand':'',
        'model':'',
        'variant':'',
        'color':'',
        'inward_no':'',
        'inward_date':'',
        'supplier_no':'',
        'supplier_date':'',
        'supplier':'',
        'purchase_price':'',
        'imei':'',
        'inv_no':'',
        'inv_date':'',
        'customer':'',
        'customer_phone':'',
        'selling_price':'',
        'margin':'',
        'special':'',
        'upgrade':'',
        'price_drop':'',
        'employee':''
        
    })

    return JsonResponse({'data': formatted})



from django.http import JsonResponse
from django.db.models import Q
from datetime import datetime
from django.http import JsonResponse
from django.db.models import Q

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

    has_access, _ = 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 sales analysis report'
        })

    # -----------------------------
    # Filters
    # -----------------------------
    customer_id    = request.POST.get('customer_id')
    branch_id_post = request.POST.get('branch_id')
    subcategory_id = request.POST.get('subcategory_id')
    brand_id       = request.POST.get('brand_id')
    model_id       = request.POST.get('model_id')
    variant_id     = request.POST.get('variant_id')
    color_id       = request.POST.get('color_id')
    from_date      = request.POST.get('from_date')
    to_date        = request.POST.get('to_date')

    # -----------------------------
    # 1️⃣ TM SALES
    # -----------------------------
    sales_q = Q(status=1, current_fy=financial_year)
    if not is_ho:
        sales_q &= Q(branch_id=branch_id)

    if customer_id:
        sales_q &= Q(customer_id=customer_id)
    if branch_id_post:
        sales_q &= Q(branch_id=branch_id_post)
    if from_date and to_date:
        sales_q &= Q(inv_date__range=[from_date, to_date])

    tm_sales = sales_order_table.objects.filter(sales_q).values(
        'id', 'inv_no', 'inv_date',
        'customer_name', 'customer_phone', 'customer_type',
        'branch_id', 'employee_id'
    )

    tm_sales_map = {s['id']: s for s in tm_sales}
    tm_sales_ids = list(tm_sales_map.keys())

    if not tm_sales_ids:
        return JsonResponse({'data': []})

    # -----------------------------
    # 2️⃣ TX SALES
    # -----------------------------
    tx_q = Q(status=1, is_active=1, tm_sales_id__in=tm_sales_ids)

    if subcategory_id:
        tx_q &= Q(subcategory_id=subcategory_id)
    if brand_id:
        tx_q &= Q(brand_id=brand_id)
    if model_id:
        tx_q &= Q(model_id=model_id)
    if variant_id:
        tx_q &= Q(variant_id=variant_id)
    if color_id:
        tx_q &= Q(color_id=color_id)

    tx_sales = list(
        child_sales_order_table.objects.filter(tx_q).values(
            'tm_sales_id', 'imei_no', 'source',
            'subcategory_id', 'brand_id',
            'model_id', 'variant_id', 'color_id',
            'amount', 'db_price',
            'special_amount', 'upgrade_amount'
        )
    )

    # -----------------------------
    # 3️⃣ IMEI COLLECTION
    # -----------------------------
    imei_list = list({r['imei_no'] for r in tx_sales if r['imei_no']})

    # -----------------------------
    # OPENING STOCK
    # -----------------------------
    opening_map = {
        o['imei_no']: {
            'purchase_price': float(o['db_price'] or 0),
            'stock_no'      : o['stock_no'],
            'date'          : o['date'].strftime('%d-%m-%Y') if o['date'] else ''
        }
        for o in opening_stock_table.objects.filter(
            imei_no__in=imei_list,
            status=1,
            is_active=1,
            branch_id=branch_id
        ).values('imei_no', 'db_price', 'stock_no', 'date')
    }



    # -----------------------------
    # PURCHASE INWARD
    # -----------------------------
    imei_to_pu = {}
    purchase_price_map = {}

    for r in child_purchase_inward_table.objects.filter(
        imei_no__in=imei_list,
        status=1,
        branch_id=branch_id
    ).values('imei_no', 'tm_pu_id', 'amount'):
        imei_to_pu[r['imei_no']] = r['tm_pu_id']
        purchase_price_map[r['imei_no']] = float(r['amount'] or 0)

    pu_map = {
        p['id']: p
        for p in purchase_inward_table.objects.filter(
            id__in=imei_to_pu.values(),
            status=1,
            branch_id=branch_id
        ).values(
            'id', 'pu_no', 'pu_date',
            'supplier_id', 'supplier_no', 'supplier_date'
        )
    }

    # -----------------------------
    # MATERIAL TRANSFER IN
    # -----------------------------
    # -----------------------------
    # MATERIAL INWARD MAPS
    # -----------------------------
    child_mat_qs = child_material_inward_table.objects.filter(
        imei_no__in=imei_list,
        status=1,
        branch_id=branch_id
    ).values(
        'imei_no', 'tm_material_id', 'amount'
    )

    imei_to_material = {}
    material_price_map = {}

    for m in child_mat_qs:
        imei_to_material[m['imei_no']] = m['tm_material_id']
        material_price_map[m['imei_no']] = float(m['amount'] or 0)

    tm_material = material_inward_table.objects.filter(
        id__in=imei_to_material.values(),
        status=1,
        is_active=1
    ).values(
        'id', 'transfer_no', 'transfer_date', 'supply_branch_id'
    )

    material_map = {m['id']: m for m in tm_material}


    # -----------------------------
    # 4️⃣ BUILD REPORT
    # -----------------------------
    formatted = []
    counter = 1

    for row in tx_sales:
        tm = tm_sales_map[row['tm_sales_id']]
        imei = row['imei_no']

        source_data = resolve_imei_source(
            imei,
            opening_map,
            imei_to_pu,
            pu_map,
            purchase_price_map,
            imei_to_material,
            material_map,
            material_price_map
        )

        if source_data:
            purchase_price = source_data['purchase_price']
            inward_no      = source_data['inward_no']
            inward_date    = source_data['inward_date']
            supplier       = source_data['supplier']
            supplier_no    = source_data['supplier_no']
            supplier_date  = source_data['supplier_date']
        else:
            purchase_price = 0.0
            inward_no = inward_date = supplier = supplier_no = supplier_date = ''

        # 🔥 SALES DB PRICE OVERRIDE (FINAL)
        sales_db_price = float(row.get('db_price') or 0)
        if sales_db_price > 0:
            purchase_price = sales_db_price

        selling_price = float(row['amount'] or 0)
        margin = selling_price - purchase_price
        price_drop = get_price_drop(
            imei=imei,
            inv_date=tm['inv_date'],
            branch_id=tm['branch_id']
        )
        mop_discount = price_drop.get('mop_discount', 0)
        bop_discount = price_drop.get('bop_discount', 0)
        wsp_discount = price_drop.get('wsp_discount', 0)
        fsp_discount = price_drop.get('fsp_discount', 0)


        sku = select_row(
            item_table,
            {
                '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']
            }
        )

        formatted.append({
            'id'             : counter,
            'branch'         : getItemNameById(branch_table, tm['branch_id']),
            'sku'            : sku.sku_text if sku else '',
            'subcategory'    : getItemNameById(sub_category_table, row['subcategory_id']),
            'brand'          : getItemNameById(brand_table, row['brand_id']),
            'model'          : getItemNameById(model_table, row['model_id']),
            'variant'        : getItemNameById(variant_table, row['variant_id']),
            'color'          : getItemNameById(color_table, row['color_id']),
            'inward_no'      : inward_no,
            'inward_date'    : inward_date,
            'supplier_no'    : supplier_no,
            'supplier_date'  : supplier_date,
            'supplier'       : supplier,
            'purchase_price' : f"{purchase_price:.2f}",
            'imei'           : imei,
            'inv_no'         : tm['inv_no'],
            'inv_date'       : tm['inv_date'].strftime('%d-%m-%Y'),
            'customer'       : tm['customer_name'],
            'customer_phone' : tm['customer_phone'],
            'customer_type'  : tm['customer_type'],
            'selling_price'  : f"{selling_price:.2f}",
            'margin'         : f"{margin:.2f}",
            'special'        : f"{row['special_amount'] or 0:.2f}",
            'upgrade'        : f"{row['upgrade_amount'] or 0:.2f}",
            'mop_discount'     : f"{mop_discount:.2f}",
            'bop_discount'     : f"{bop_discount:.2f}",
            'wsp_discount'     : f"{wsp_discount:.2f}",
            'fsp_discount'     : f"{fsp_discount:.2f}",
            'employee'       : getItemNameById(employee_table, tm['employee_id']),
            
        })

        counter += 1


    return JsonResponse({'data': formatted})

def resolve_imei_source(
    imei,
    opening_map,
    imei_to_pu,
    pu_map,
    purchase_price_map,
    imei_to_material,
    material_map,
    material_price_map
):
    # -----------------------------
    # OPENING STOCK
    # -----------------------------
    if imei in opening_map:
        o = opening_map[imei]
        return {
            'source'        : 'opening_stock',
            'purchase_price': o['purchase_price'],
            'inward_no'     : o['stock_no'],
            'inward_date'   : o['date'],
            'supplier'      : '-',
            'supplier_no'   : '-',
            'supplier_date' : '-',
        }

    # -----------------------------
    # PURCHASE INWARD
    # -----------------------------
    if imei in imei_to_pu:
        pu = pu_map.get(imei_to_pu[imei])
        if pu:
            return {
                'source'        : 'purchase',
                'purchase_price': purchase_price_map.get(imei, 0),
                'inward_no'     : pu['pu_no'],
                'inward_date'   : pu['pu_date'].strftime('%d-%m-%Y'),
                'supplier'      : getItemNameById(supplier_table, pu['supplier_id']),
                'supplier_no'   : pu['supplier_no'],
                'supplier_date' : pu['supplier_date'].strftime('%d-%m-%Y'),
            }

    # -----------------------------
    # MATERIAL INWARD
    # -----------------------------
    if imei in imei_to_material:
        mat = material_map.get(imei_to_material[imei])
        if mat:
            return {
                'source'        : 'material_inward',
                'purchase_price': material_price_map.get(imei, 0),
                'inward_no'     : mat['transfer_no'],
                'inward_date'   : mat['transfer_date'].strftime('%d-%m-%Y'),
                'supplier'      : getItemNameById(branch_table, mat['supply_branch_id']),
                'supplier_no'   : '-',
                'supplier_date' : '-',
            }

    return None
from decimal import Decimal

def get_price_drop(imei, inv_date, branch_id):
    """
    Returns applicable price drop discounts for a given IMEI
    based on invoice date and branch.
    """

    # 1️⃣ Get latest applicable price history (from_date <= inv_date)
    price_master = (
        price_history_table.objects
        .filter(
            branch_id=branch_id,
            from_date__lte=inv_date,
            is_active=1
        )
        .order_by('-from_date')
        .first()
    )

    if not price_master:
        return {
            'fsp_discount': 0.00,
            'wsp_discount': 0.00,
            'mop_discount': 0.00,
            'bop_discount': 0.00,
        }

    # 2️⃣ Check IMEI-wise transaction price history
    tx_price = (
        tx_price_history_table.objects
        .filter(
            tm_price_id=price_master.id,
            imei_no=imei,
            is_active=1
        )
        .first()
    )

    if not tx_price:
        return {
            'fsp_discount': 0.00,
            'wsp_discount': 0.00,
            'mop_discount': 0.00,
            'bop_discount': 0.00,
        }

    # 3️⃣ Return discounts
    return {
        'fsp_discount': float(tx_price.fsp_discount or 0),
        'wsp_discount': float(tx_price.wsp_discount or 0),
        'mop_discount': float(tx_price.mop_discount or 0),
        'bop_discount': float(tx_price.bop_discount or 0),
    }