from django.db.models import Sum, Count, Q, Avg
from expenses.models import *
from store.models import *
from masters.models import *
from sales.models import *
from customer.models import *
from inventory.models import *
from stock.models import *
from datetime import datetime, timedelta
from common.utils import *

def get_admin_dashboard_data(request):
    # Filters
    period = request.GET.get('period', 'today')
    selected_branch_id = request.GET.get('branch_id', 'all')
    
    today = datetime.now().date()
    start_date = today
    end_date = today

    if period == 'yesterday':
        start_date = today - timedelta(days=1)
        end_date = start_date
    elif period == 'last_7_days':
        start_date = today - timedelta(days=7)
    elif period == 'this_month':
        start_date = today.replace(day=1)
    elif period == 'last_month':
        last_month_end = today.replace(day=1) - timedelta(days=1)
        start_date = last_month_end.replace(day=1)
        end_date = last_month_end
    elif period == 'this_year':
        start_date = today.replace(month=1, day=1)
    elif period == 'all':
        start_date = datetime(2000, 1, 1).date()

    # Base filters
    sales_filter = Q(inv_date__range=[start_date, end_date], status=1, is_active=1)
    purchase_filter = Q(pu_date__range=[start_date, end_date], status=1, is_active=1)
    expense_filter = Q(date__range=[start_date, end_date], status=1, is_active=1)
    customer_filter = Q(created_on__date__range=[start_date, end_date], status=1, is_active=1)

    # Apply branch filter if selected
    if selected_branch_id != 'all':
        sales_filter &= Q(branch_id=selected_branch_id)
        purchase_filter &= Q(branch_id=selected_branch_id)
        expense_filter &= Q(branch_id=selected_branch_id)
        customer_filter &= Q(branch_id=selected_branch_id)

    # 1. Total Sales & Orders
    sales_data = sales_order_table.objects.filter(sales_filter).aggregate(
        total_amt=Sum('total_amount'), 
        total_count=Count('id'),
        avg_order=Avg('total_amount')
    )
    total_sales = float(sales_data['total_amt'] or 0)
    total_orders = sales_data['total_count'] or 0
    avg_order_value = float(sales_data['avg_order'] or 0)

    # 2. Total Purchase
    purchase_data = purchase_inward_table.objects.filter(purchase_filter).aggregate(
        total_amt=Sum('grand_total'),
        purchase_count=Count('id')
    )
    total_purchase = float(purchase_data['total_amt'] or 0)
    purchase_orders = purchase_data['purchase_count'] or 0

    # 3. Total Expenses
    total_expenses = float(expense_table.objects.filter(expense_filter).aggregate(
        total_amt=Sum('amount')
    )['total_amt'] or 0)

    # 4. Profit Calculation
    cost_of_sales = float(child_sales_order_table.objects.filter(
        tm_sales_id__in=sales_order_table.objects.filter(sales_filter).values_list('id', flat=True),
        status=1, is_active=1
    ).aggregate(total_cost=Sum('purchase_price'))['total_cost'] or 0)
    
    gross_profit = total_sales - cost_of_sales
    net_profit = gross_profit - total_expenses

    # 5. Inventory Stats (Branch specific if filtered)
    inventory_filter = Q(status=1, is_active=1)
    if selected_branch_id != 'all':
        inventory_filter &= Q(branch_id=selected_branch_id)

    total_stock_qty = opening_stock_table.objects.filter(inventory_filter).aggregate(
        total_qty=Sum('quantity')
    )['total_qty'] or 0
    
    total_stock_value = float(child_purchase_inward_table.objects.filter(inventory_filter).aggregate(
        total_val=Sum(F('quantity') * F('rate'))
    )['total_val'] or 0)

    # 6. Customer Metrics
    new_customers = customer_table.objects.filter(customer_filter).count()
    total_customers_filter = Q(status=1)
    if selected_branch_id != 'all':
        total_customers_filter &= Q(branch_id=selected_branch_id)
    total_customers = customer_table.objects.filter(total_customers_filter).count()

    # 7. Returns Metrics
    sales_returns = sales_return_table.objects.filter(sr_date__range=[start_date, end_date], status=1, is_active=1)
    if selected_branch_id != 'all':
        sales_returns = sales_returns.filter(branch_id=selected_branch_id)
    sales_returns_data = sales_returns.aggregate(amt=Sum('total_amount'), count=Count('id'))
    total_sales_returns = float(sales_returns_data['amt'] or 0)
    sales_return_count = sales_returns_data['count'] or 0

    purchase_returns = purchase_return_table.objects.filter(pr_date__range=[start_date, end_date], status=1, is_active=1)
    if selected_branch_id != 'all':
        purchase_returns = purchase_returns.filter(branch_id=selected_branch_id)
    purchase_returns_data = purchase_returns.aggregate(amt=Sum('total_amount'), count=Count('id'))
    total_purchase_returns = float(purchase_returns_data['amt'] or 0)
    purchase_return_count = purchase_returns_data['count'] or 0

    total_margin = (net_profit / total_sales * 100) if total_sales > 0 else 0

    # Branch-wise Detailed Summary (Always for all active branches)
    branches = branch_table.objects.filter(status=1)
    branch_summary = []
    
    # We use the original sales_filter without the selected_branch_id for the summary table
    summary_sales_filter = Q(inv_date__range=[start_date, end_date], status=1, is_active=1)
    summary_expense_filter = Q(date__range=[start_date, end_date], status=1, is_active=1)
    summary_purchase_filter = Q(pu_date__range=[start_date, end_date], status=1, is_active=1)

    for branch in branches:
        b_sales_data = sales_order_table.objects.filter(summary_sales_filter, branch_id=branch.id).aggregate(
            amt=Sum('total_amount'), count=Count('id')
        )
        b_sales = float(b_sales_data['amt'] or 0)
        b_orders = b_sales_data['count'] or 0
        
        b_expenses = float(expense_table.objects.filter(summary_expense_filter, branch_id=branch.id).aggregate(amt=Sum('amount'))['amt'] or 0)
        b_purchase = float(purchase_inward_table.objects.filter(summary_purchase_filter, branch_id=branch.id).aggregate(amt=Sum('grand_total'))['amt'] or 0)
        
        b_cos = float(child_sales_order_table.objects.filter(
            tm_sales_id__in=sales_order_table.objects.filter(summary_sales_filter, branch_id=branch.id).values_list('id', flat=True),
            status=1, is_active=1
        ).aggregate(total_cost=Sum('purchase_price'))['total_cost'] or 0)
        
        b_gross_profit = b_sales - b_cos
        b_net_profit = b_gross_profit - b_expenses
        
        branch_summary.append({
            'id': branch.id,
            'name': branch.name,
            'sales': b_sales,
            'orders': b_orders,
            'purchase': b_purchase,
            'expenses': b_expenses,
            'profit': b_net_profit,
            'margin': (b_net_profit / b_sales * 100) if b_sales > 0 else 0
        })

    # Sort branch summary by sales
    branch_summary = sorted(branch_summary, key=lambda x: x['sales'], reverse=True)
    
    # Calculate performance percentage relative to top performer
    total_sales_max = branch_summary[0]['sales'] if branch_summary else 0
    grand_total_sales = sum(b['sales'] for b in branch_summary)
    for b in branch_summary:
        b['performance_pct'] = (b['sales'] / total_sales_max * 100) if total_sales_max > 0 else 0
        b['revenue_share'] = (b['sales'] / grand_total_sales * 100) if grand_total_sales > 0 else 0

    # 8. Chart Data (Sales and Profit by Branch)
    chart_data = {
        'labels': [b['name'] for b in branch_summary],
        'sales': [b['sales'] for b in branch_summary],
        'profit': [b['profit'] for b in branch_summary],
        'margin': [round(b['margin'], 1) for b in branch_summary]
    }

    # Top Selling Products
    top_products = child_sales_order_table.objects.filter(
        tm_sales_id__in=sales_order_table.objects.filter(sales_filter).values_list('id', flat=True),
        status=1, is_active=1
    ).values('model_id').annotate(
        qty=Sum('quantity'),
        revenue=Sum('amount')
    ).order_by('-qty')[:5]

    for p in top_products:
        p['model_name'] = getItemNameById(model_table, p['model_id'])
        p['revenue'] = float(p['revenue'] or 0)

    # 9. Low Stock Alerts (Across branches or selected branch)
    low_stock_filter = Q(quantity__lte=5, status=1, is_active=1)
    if selected_branch_id != 'all':
        low_stock_filter &= Q(branch_id=selected_branch_id)
    
    low_stock_items = opening_stock_table.objects.filter(low_stock_filter).order_by('quantity')[:5]
    for item in low_stock_items:
        item.model_name = getItemNameById(model_table, item.model_id)
        item.branch_name = getItemNameById(branch_table, item.branch_id)

    # 10. Recent Expenses
    recent_expenses = expense_table.objects.filter(expense_filter).order_by('-date', '-id')[:5]
    for exp in recent_expenses:
        exp.branch_name = getItemNameById(branch_table, exp.branch_id)
        exp.category_name = getItemNameById(expense_master_table , exp.category_id)

    # 11. Payment Mode Analysis
    payment_filter = Q(date__range=[start_date, end_date], status=1, is_active=1)
    if selected_branch_id != 'all':
        payment_filter &= Q(branch_id=selected_branch_id)
    
    payment_modes = transaction_table.objects.filter(payment_filter).values('payment_type').annotate(
        total=Sum('amount')
    ).order_by('-total')
    
    for pm in payment_modes:
        pm['total'] = float(pm['total'] or 0)

    data = {
        'total_sales': total_sales,
        'total_orders': total_orders,
        'avg_order_value': avg_order_value,
        'total_purchase': total_purchase,
        'purchase_orders': purchase_orders,
        'total_expenses': total_expenses,
        'gross_profit': gross_profit,
        'net_profit': net_profit,
        'new_customers': new_customers,
        'total_customers': total_customers,
        'total_stores': branches.count(),
        'total_stock_qty': total_stock_qty,
        'total_stock_value': total_stock_value,
        'total_sales_returns': total_sales_returns,
        'sales_return_count': sales_return_count,
        'total_purchase_returns': total_purchase_returns,
        'purchase_return_count': purchase_return_count,
        'total_margin': total_margin,
        'period': period,
        'selected_branch_id': selected_branch_id,
        'all_branches': branches,
        'branch_summary': branch_summary,
        'chart_data': chart_data,
        'top_products': top_products,
        'recent_sales': sales_order_table.objects.filter(sales_filter).order_by('-created_on')[:5],
        'low_stock_items': low_stock_items,
        'recent_expenses': recent_expenses,
        'payment_modes': payment_modes
    }
    
    return data