from django.db.models import DecimalField, ExpressionWrapper, F, Sum
from django.db.models.functions import TruncDate
from sales.models import Sale, SaleItem
from .models import Expense, OtherIncome, OwnerDraw, Account


def get_summary(start_date, end_date):
    """
    Core financial summary for a given date range.
    Returns all figures needed for the dashboard.
    """

    # ── Sales ──────────────────────────────────────────────────────
    sales = Sale.objects.filter(
        date__range=(start_date, end_date),
        status='completed'
    )

    sale_items = SaleItem.objects.filter(
        sale__date__range=(start_date, end_date),
        sale__status='completed'
    )
    revenue_expr = ExpressionWrapper(
        F('quantity') * F('unit_price'),
        output_field=DecimalField(max_digits=20, decimal_places=2),
    )
    cogs_expr = ExpressionWrapper(
        F('quantity') * F('unit_cost'),
        output_field=DecimalField(max_digits=20, decimal_places=2),
    )
    totals = sale_items.aggregate(
        revenue=Sum(revenue_expr),
        cogs=Sum(cogs_expr),
    )
    revenue = totals['revenue'] or 0
    cogs = totals['cogs'] or 0

    # Account for discounts
    total_discounts = sales.aggregate(
        total=Sum('discount')
    )['total'] or 0

    revenue = revenue - total_discounts
    gross_profit = revenue - cogs

    # ── Expenses ───────────────────────────────────────────────────
    total_expenses = Expense.objects.filter(
        date__range=(start_date, end_date)
    ).aggregate(total=Sum('amount'))['total'] or 0

    # ── Other Income ───────────────────────────────────────────────
    total_other_income = OtherIncome.objects.filter(
        date__range=(start_date, end_date)
    ).aggregate(total=Sum('amount'))['total'] or 0

    # ── Owner Draws ────────────────────────────────────────────────
    total_draws = OwnerDraw.objects.filter(
        date__range=(start_date, end_date)
    ).aggregate(total=Sum('amount'))['total'] or 0

    # ── Net Profit ─────────────────────────────────────────────────
    # Net Profit = Gross Profit + Other Income - Expenses
    net_profit = gross_profit + total_other_income - total_expenses

    # ── Account Balances ───────────────────────────────────────────
    accounts = Account.objects.filter(is_active=True)
    total_balance = sum(a.balance for a in accounts)

    # ── Sales Count ────────────────────────────────────────────────
    sales_count = sales.count()

    return {
        'revenue': revenue,
        'cogs': cogs,
        'gross_profit': gross_profit,
        'total_expenses': total_expenses,
        'total_other_income': total_other_income,
        'total_draws': total_draws,
        'net_profit': net_profit,
        'accounts': accounts,
        'total_balance': total_balance,
        'sales_count': sales_count,
        'start_date': start_date,
        'end_date': end_date,
    }


def get_expenses_by_category(start_date, end_date):
    """Expenses broken down by category."""
    return Expense.objects.filter(
        date__range=(start_date, end_date)
    ).values(
        'category__name'
    ).annotate(
        total=Sum('amount')
    ).order_by('-total')


def get_daily_sales(start_date, end_date):
    """Daily sales totals for a chart."""
    revenue_expr = ExpressionWrapper(
        F('quantity') * F('unit_price'),
        output_field=DecimalField(max_digits=20, decimal_places=2),
    )
    return SaleItem.objects.filter(
        sale__date__range=(start_date, end_date),
        sale__status='completed'
    ).annotate(
        day=TruncDate('sale__date')
    ).values('day').annotate(
        revenue=Sum(revenue_expr),
    ).order_by('day')


def get_top_products(start_date, end_date, limit=5):
    """Best selling products by revenue."""
    revenue_expr = ExpressionWrapper(
        F('quantity') * F('unit_price'),
        output_field=DecimalField(max_digits=20, decimal_places=2),
    )
    return SaleItem.objects.filter(
        sale__date__range=(start_date, end_date),
        sale__status='completed'
    ).values(
        'product__name'
    ).annotate(
        total_qty=Sum('quantity'),
        total_revenue=Sum(revenue_expr),
    ).order_by('-total_revenue')[:limit]


def get_recent_sales(limit=10):
    return Sale.objects.filter(
        status='completed'
    ).order_by('-date', '-created_at')[:limit]


def get_recent_expenses(limit=10):
    return Expense.objects.select_related(
        'category', 'account'
    ).order_by('-date', '-created_at')[:limit]