from django.db.models import Q, F
from collections import defaultdict, Counter
from decimal import Decimal
from inventory.models import *
from sales.models import *
from stock.models import *
from material.models import *
from supplier.models import *
from masters.models import *
from common.utils import getItemNameById, format_amount, get_final_price_for_imei

from store.models import *
from wholesale.models import *

# ---------------------------------------------------------
# 🔹 Helper: get EAN and HSN if missing
# ---------------------------------------------------------
def get_ean_hsn(row):
    """
    Returns a tuple (ean, hsn_code, shelf_life) for the given row.
    If row has ean or hsn_code, use that.
    Otherwise, fetch from item_table using subcategory, brand, model, variant, color.
    """
    ean = getattr(row, 'ean', '') or ''
    hsn = getattr(row, 'hsn_code', '') or ''
    shelf_life = getattr(row, 'shelf_life', '') or ''

    if not ean or not hsn or not shelf_life:
        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,
            is_active=1
        ).first()
        if item:
            ean = item.ean or ean
            hsn = item.hsn_code or hsn
            shelf_life = item.cell_life or ''

    return ean, hsn, shelf_life

from datetime import date
from scheme.models import item_scheme_table
from decimal import Decimal
def get_imei_stock_summary_data(branch_id, is_ho, filters, include_schemes=False):
    """
    Common logic to fetch and format IMEI stock summary data.
    
    Args:
        branch_id (int): The current branch ID.
        is_ho (int): 1 if HO, 0 otherwise.
        filters (dict): Dictionary containing filter keys:
            - subcategory, brand_id, model_id, variant_id, color_id
            - stock (filter for 'stock' or 'demo')
            - branch_id (filter for specific branch if HO)
        include_schemes (bool): Whether to calculate and include active scheme values.

    Returns:
        tuple: (available_qty, formatted_data_list)
    """

    subcategory_id = filters.get('subcategory')
    brand_id       = filters.get('brand_id')
    model_id       = filters.get('model_id')
    variant_id     = filters.get('variant_id')
    color_id       = filters.get('color_id')
    stock_filter   = filters.get('stock')
    branch_filter  = filters.get('branch_id')

    # ✅ HO Override: Use filtered branch if selected
    if is_ho == 1 and branch_filter:
        branch_id = branch_filter

    formatted = []
    counter = 1
    available_qty = 0
    processed_imei_set = set()

    # -------------------------
    # SOLD / REMOVED IMEIS (Tracked per Branch)
    # -------------------------
    
    # Base filter for sold items
    sold_filter = Q(is_active=1, status=1)

    # -------------------------
    # NET STOCK TRACKING (Per Branch)
    # -------------------------
    
    # 🔹 Use counters to track total Inwards and Outwards per Branch
    inwards_count = defaultdict(lambda: Counter())
    outwards_count = defaultdict(lambda: Counter())

    # Initial Filter (Active items)
    base_filter = Q(is_active=1, status=1)
    if is_ho == 1:
        if branch_filter:
            base_filter &= Q(branch_id=branch_filter)
    else:
        base_filter &= Q(branch_id=branch_id)

    # IDs of sales to wholesale customers
    wholesale_sales_ids = list(sales_order_table.objects.filter(customer_type__iexact='wholesale').values_list('id', flat=True))

    # 1. OUTWARDS (Removals)
    # Sales: IMEIs sold to customers (Exclude Wholesale Initial Sales)
    sales_sold = child_sales_order_table.objects.filter(base_filter)\
        .exclude(tm_sales_id__in=wholesale_sales_ids)\
        .exclude(imei_no__isnull=True).exclude(imei_no='')\
        .values('imei_no', 'branch_id')
    for r in sales_sold:
        outwards_count[r['branch_id']][r['imei_no']] += 1

    # Wholesale Final Sold (tm_wholesale)
    wholesale_sold_imeis = set(wholesale_child_sales_order_table.objects.filter(
        status=1
    ).exclude(imei_no__isnull=True).exclude(imei_no='').values_list('imei_no', flat=True))
    
    # Wholesale Pending (Non Open Stock)
    pending_wholesale_imeis = set(child_sales_order_table.objects.filter(
        base_filter,
        tm_sales_id__in=wholesale_sales_ids
    ).exclude(imei_no__isnull=True).exclude(imei_no='').values_list('imei_no', flat=True))

    # Purchase Return: IMEIs returned to supplier - Only count approved ones
    purchase_returns = child_purchase_return_table.objects.filter(
        base_filter,
        tm_return_id__in=purchase_return_table.objects.exclude(
            pr_status__iexact='reversed'
        ).values_list('id', flat=True)
    ).exclude(imei_no__isnull=True).exclude(imei_no='')\
        .values('imei_no', 'branch_id')
    for r in purchase_returns:
        outwards_count[r['branch_id']][r['imei_no']] += 1

    # Material Outward: IMEIs transferred out
    outwards = child_material_outward_table.objects.filter(base_filter)\
        .exclude(tm_material_id__in=material_outward_table.objects.filter(outward_status='rejected').values_list('id', flat=True))\
        .exclude(imei_no__isnull=True).exclude(imei_no='')\
        .values('imei_no', 'branch_id')
    for r in outwards:
        outwards_count[r['branch_id']][r['imei_no']] += 1

    # 2. INWARDS (Additions)
    stock_sources = [
        ('Purchase', child_purchase_inward_table),
        ('Material In', child_material_inward_table),
        ('Opening Stock', opening_stock_table),
        ('Sales Return', child_sales_return_table)
    ]

    all_inward_rows = []
    
    for source_name, model in stock_sources:
        # Apply filters (Category, Brand, Model, etc.)
        item_filters = base_filter
        if subcategory_id:
            item_filters &= Q(subcategory_id=subcategory_id)
        if brand_id:
            item_filters &= Q(brand_id=brand_id)
        if model_id:
            item_filters &= Q(model_id=model_id)
        if variant_id:
            item_filters &= Q(variant_id=variant_id)
        if color_id:
            item_filters &= Q(color_id=color_id)

        rows = model.objects.filter(item_filters)\
            .exclude(imei_no__isnull=True).exclude(imei_no='')
        
        for row in rows:
            inwards_count[row.branch_id][row.imei_no] += 1
            # Attach source name for later display
            row.source_type = source_name
            all_inward_rows.append(row)

    # -------------------------
    # PROCESS INWARDS CHRONOLOGICALLY
    # -------------------------
    
    # Sort all inward records by created_on DESC (Latest Inward Wins)
    all_inward_rows.sort(key=lambda x: x.created_on, reverse=True)

    def format_imei_row(row):
        nonlocal counter, available_qty

        # Unique key for this IMEI at this branch
        imei_branch_key = (row.imei_no, row.branch_id)
        if not row.imei_no or imei_branch_key in processed_imei_set:
            return

        # Check global wholesale sold
        if row.imei_no in wholesale_sold_imeis:
            return

        # 🔹 Net Stock Check
        net_at_branch = inwards_count[row.branch_id][row.imei_no] - outwards_count[row.branch_id][row.imei_no]
        
        if net_at_branch <= 0:
            return

        # Mark as processed so we only show the LATEST record for this IMEI-Branch pair
        processed_imei_set.add(imei_branch_key)
        
        # Check Non Open Stock
        is_non_open = row.imei_no in pending_wholesale_imeis

        # Apply stock / demo / non-open filter
        if stock_filter == 'non_open_stock':
            if not is_non_open: return
        elif stock_filter == 'stock':
            if is_non_open: return  # Filter out Non Open Stock for strict 'stock' requests
            if getattr(row, 'is_demo', 0) == 1: return
        elif stock_filter == 'demo':
            if getattr(row, 'is_demo', 0) == 0: return
        elif stock_filter == 'all_except_non_open':
             if is_non_open: return
             # Allow both stock (is_demo=0) and demo (is_demo=1)
        elif filters.get('include_non_open'): # Default view (no filter selected)
             # Show STOCK + NON OPEN STOCK (Exclude DEMO)
             if getattr(row, 'is_demo', 0) == 1: return
        else: # Fallback (should ideally not happen if filters are controlled)
             if is_non_open: return
             if getattr(row, 'is_demo', 0) == 1: return

        # Stock status badge logic
        action_button = ""
        stock_badge = ""
        
        if is_non_open:
            stock_badge = '<span class="badge bg-secondary">Non Open Stock</span>'
            action_button = '<button class="btn btn-xs btn-secondary" disabled>Non Open</button>'
        elif getattr(row, 'is_demo', 0):
            stock_badge = '<span class="badge bg-danger">Demo</span>'
            action_button = (
                f'<button class="btn btn-xs btn-warning" '
                f'onclick="make_demo(\'{row.imei_no}\')" '
                f'title="Revert Demo">Revert</button>'
            )
        else:
            stock_badge = '<span class="badge bg-success">Stock</span>'
            action_button = (
                f'<button class="btn btn-xs btn-success" '
                f'onclick="make_demo(\'{row.imei_no}\')" '
                f'title="Make Demo">Demo</button>'
            )

        # Get EAN / HSN
        ean, hsn, shelf_life = get_ean_hsn(row)

        available_qty += 1

        # Price calculation
        fsp, wsp, mop, bop = get_final_price_for_imei(
            branch_id=row.branch_id,
            subcategory_id=row.subcategory_id,
            brand_id=row.brand_id,
            model_id=row.model_id,
            variant_id=row.variant_id,
            imei_no=row.imei_no,
            fsp=getattr(row, 'fsp', 0),
            wsp=getattr(row, 'wsp', 0),
            mop=getattr(row, 'mop', 0),
            bop=getattr(row, 'bop', 0)
        )
        
        # Base MOP/BOP to Decimal
        mop = Decimal(str(mop)) if mop is not None else Decimal(0)
        bop = Decimal(str(bop)) if bop is not None else Decimal(0)
        
        # Rate and Amount
        rate = getattr(row, 'rate', 0) or getattr(row, 'db_price', 0)
        qty = float(getattr(row, 'quantity', 1))
        amount = getattr(row, 'amount', 0) or (float(rate) * qty)
        db_price = getattr(row, 'db_price', 0)
        
        # Schemes
        scheme_mop = Decimal(0)
        scheme_bop = Decimal(0)
        if include_schemes and getattr(row, 'is_demo', 0) == 0:
            today = date.today()
            active_schemes = item_scheme_table.objects.filter(
                status=1,
                from_date__lte=today,
                to_date__gte=today,
                subcategory_id=row.subcategory_id,
                brand_id=row.brand_id,
                model_id=row.model_id,
                variant_id=row.variant_id
            )
            for scheme in active_schemes:
                mop_drop = Decimal(str(scheme.mop_drop or 0))
                bop_drop = Decimal(str(scheme.bop_drop or 0))
                if scheme.scheme_type in ['upgrade', 'special']:
                    mop += mop_drop
                    bop += bop_drop
                    scheme_mop += mop_drop
                    scheme_bop += bop_drop
        if is_ho == 0:
            amount_qs = fsp
        else:
            if row.source_type == 'Material In':
                print('source_type',row.source_type)
                supply_branch = branch_table.objects.filter(id=getattr(row, 'branch_id',0)).first()
                print('supply_branch',supply_branch.is_ho)
                if supply_branch and supply_branch.is_ho == 1:
                    purchase_row = child_purchase_inward_table.objects.filter(imei_no=row.imei_no, status=1).order_by('-created_on').first()
                    print('purchase_row',purchase_row, row.imei_no)
                    if purchase_row:
                        amount_qs = purchase_row.amount
                    else:
                        opening_row = opening_stock_table.objects.filter(imei_no=row.imei_no, status=1).first()
                        if opening_row:
                            amount_qs = opening_row.db_price
                        else:
                            amount_qs = db_price
                else:
                    amount_qs = db_price
            else:
                amount_qs = amount
        
        amount_qs = format_amount(amount_qs)
        formatted.append({
            'id': counter,
            'action': action_button,
            'branch': getItemNameById(branch_table, row.branch_id),
            'sub_category': 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),
            'shelf_life': shelf_life,
            'imei': row.imei_no,
            'ean': ean,
            'hsn_code': hsn,
            'source': row.source_type,
            'fsp': format_amount(fsp),
            'wsp': format_amount(wsp),
            'mrp': format_amount(getattr(row, 'mrp', 0)),
            'mop': format_amount(mop),
            'bop': format_amount(bop),
            'rate': format_amount(rate),
            'amount': format_amount(amount_qs),
            'db_price': format_amount(db_price),
            'stock': 1,
            'is_demo': getattr(row, 'is_demo', 0),
            'demo_badge': stock_badge,
            'scheme_mop': format_amount(scheme_mop),
            'scheme_bop': format_amount(scheme_bop),
        })
        counter += 1

    # Format the rows
    for row in all_inward_rows:
        format_imei_row(row)

    return available_qty, formatted