# sales/views/reports.py
import logging
import json
import csv
import string
from datetime import datetime, timedelta
from decimal import Decimal

from django.shortcuts import render, redirect, get_object_or_404
from django.contrib import messages
from django.contrib.auth.decorators import login_required
from django.http import JsonResponse, HttpResponse
from django.views.decorators.csrf import csrf_exempt
from django.utils import timezone
from django.utils.dateparse import parse_date
from django.db import transaction
from django.db.models import Sum, Count, Q, F
from django.core.exceptions import PermissionDenied

from inventory.models import Product
from core.models import Branch
from core.utils import check_limit_or_block, filter_orders_by_user_branch
from accounts.decorators import role_required, perm_required
from sales.models import (
    POSOrder, POSOrderItem, PaymentTransaction,
    DailySalesSummary, UnusualTransaction, Customer,
)
from companies.permissions import company_feature_required
from sales.forms import POSOrderForm, POSOrderItemForm

logger = logging.getLogger(__name__)


@login_required
@perm_required('can_view_reports')
def payment_summary(request):
    """
    Payment summary view for displaying analytics and sales data.
    This function dynamically generates summaries for today and the last 7 days.
    """
    # Get today's date
    today = timezone.now().date()

    # Generate today's summary
    today_orders = POSOrder.objects.filter(company=request.company, created_at__date=today, status='completed')
    today_summary_data = {
        'date': today,
        'total_sales': today_orders.aggregate(total=Sum('final_amount'))['total'] or 0,
        'total_transactions': today_orders.count(),
    }

    # Calculate method-specific totals for today
    for method in ['pos', 'transfer', 'cash', 'mobile_money']:
        method_orders = today_orders.filter(payment_method=method)
        today_summary_data[f'{method}_sales'] = method_orders.aggregate(
            total=Sum('final_amount')
        )['total'] or 0
        today_summary_data[f'{method}_transactions'] = method_orders.count()

    # Save or update today's summary in the database
    today_summary, created = DailySalesSummary.objects.update_or_create(
        date=today,
        company=request.company,
        defaults={
            'total_revenue': today_summary_data['total_sales'],
            'total_transactions': today_summary_data['total_transactions'],
            'cash_revenue': today_summary_data.get('cash_sales', 0),
            'cash_transactions': today_summary_data.get('cash_transactions', 0),
            'transfer_revenue': today_summary_data.get('transfer_sales', 0),
            'transfer_transactions': today_summary_data.get('transfer_transactions', 0),
            'pos_revenue': today_summary_data.get('pos_sales', 0),
            'pos_transactions': today_summary_data.get('pos_transactions', 0),
            'mobile_money_revenue': today_summary_data.get('mobile_money_sales', 0),
            'mobile_money_transactions': today_summary_data.get('mobile_money_transactions', 0),
        }
    )

    # Generate summaries for the last 7 days
    recent_dates = [today - timedelta(days=i) for i in range(7)]
    recent_summaries = []
    for date in recent_dates:
        orders = POSOrder.objects.filter(company=request.company, created_at__date=date, status='completed')
        summary_data = {
            'date': date,
            'total_sales': orders.aggregate(total=Sum('final_amount'))['total'] or 0,
            'total_transactions': orders.count(),
        }

        # Calculate method-specific totals for each day
        for method in ['pos', 'transfer', 'cash', 'mobile_money']:
            method_orders = orders.filter(payment_method=method)
            summary_data[f'{method}_sales'] = method_orders.aggregate(
                total=Sum('final_amount')
            )['total'] or 0
            summary_data[f'{method}_transactions'] = method_orders.count()

        # Save or update the summary in the database
        summary, _ = DailySalesSummary.objects.update_or_create(
            date=date,
            company=request.company,
            defaults={
                'total_revenue': summary_data['total_sales'],
                'total_transactions': summary_data['total_transactions'],
                'cash_revenue': summary_data.get('cash_sales', 0),
                'cash_transactions': summary_data.get('cash_transactions', 0),
                'transfer_revenue': summary_data.get('transfer_sales', 0),
                'transfer_transactions': summary_data.get('transfer_transactions', 0),
                'pos_revenue': summary_data.get('pos_sales', 0),
                'pos_transactions': summary_data.get('pos_transactions', 0),
                'mobile_money_revenue': summary_data.get('mobile_money_sales', 0),
                'mobile_money_transactions': summary_data.get('mobile_money_transactions', 0),
            }
        )
        recent_summaries.append(summary)

    # Fetch recent summaries (last 7 days) for display
    recent_summaries = DailySalesSummary.objects.filter(
        company=request.company,
        date__gte=today - timedelta(days=7)
    ).order_by('-date')

    # Calculate payment method distribution for today
    payment_methods = POSOrder.objects.filter(company=request.company, 
        created_at__date=today,
        status='completed'
    ).values('payment_method').annotate(
        total=Sum('final_amount'),
        count=Count('id')
    ).order_by('-total')

    # Calculate percentages for payment methods
    total_amount = sum(pm['total'] or 0 for pm in payment_methods)
    for pm in payment_methods:
        pm['percentage'] = round(((pm['total'] or 0) / total_amount) * 100, 2) if total_amount > 0 else 0

    # Map payment method codes to human-readable names
    PAYMENT_METHOD_CHOICES = dict(POSOrder.PAYMENT_METHODS)
    for pm in payment_methods:
        pm['display_name'] = PAYMENT_METHOD_CHOICES.get(pm['payment_method'], "Unknown")

    # Context dictionary with detailed explanation of each variable
    context = {
        'summary': today_summary,  # Today's summary
        'today_summary': today_summary,  # Duplicate for template consistency
        'recent_summaries': recent_summaries,  # Last 7 days' summaries
        'payment_methods': payment_methods,  # Payment method distribution
        'user_role': request.user.role,  # User role (e.g., admin, cashier)
        'user_name': request.user.get_full_name() or request.user.username,  # User name
    }

    return render(request, 'sales/payment_summary.html', context)


@login_required
@perm_required('can_view_reports')
def daily_dashboard(request):
    """Today's sales dashboard - branch aware"""
    today = timezone.now().date()

    user = request.user
    selected_branch_id = request.GET.get("branch", "all")

    # Base queryset: today's completed orders
    orders_qs = POSOrder.objects.filter(company=request.company, 
        created_at__date=today,
        status="completed",
    )

    # --- ROLE / BRANCH LOGIC (Option A strict) ---
    if user.role in ("manager", "cashier"):
        # Force their own branch
        if user.branch:
            orders_qs = orders_qs.filter(branch=user.branch)
            selected_branch_id = str(user.branch.id)
        else:
            # No branch assigned = no orders
            orders_qs = POSOrder.objects.none()
            selected_branch_id = "none"
    else:
        # superadmin / admin can choose branch from dropdown
        if selected_branch_id and selected_branch_id != "all":
            try:
                branch_id = int(selected_branch_id)
                orders_qs = orders_qs.filter(branch_id=branch_id)
            except ValueError:
                pass  # ignore bad input

    # --- Compute summary from filtered orders_qs ---
    total_revenue = orders_qs.aggregate(total=Sum("final_amount"))["total"] or 0
    total_transactions = orders_qs.count()

    # Payment method breakdown
    def pm_totals(method_code):
        m_qs = orders_qs.filter(payment_method=method_code)
        return (
            m_qs.aggregate(total=Sum("final_amount"))["total"] or 0,
            m_qs.count(),
        )

    cash_revenue, cash_transactions = pm_totals("cash")
    transfer_revenue, transfer_transactions = pm_totals("transfer")
    pos_revenue, pos_transactions = pm_totals("pos")
    mobile_revenue, mobile_transactions = pm_totals("mobile_money")

    # Profit + credit outstanding based on filtered orders
    from decimal import Decimal
    total_profit = Decimal("0")
    order_items = list(POSOrderItem.objects.filter(order__in=orders_qs).select_related("product"))
    for item in order_items:
        selling = item.unit_price or Decimal("0")
        cost = (item.product.cost_price or Decimal("0")) if item.product else Decimal("0")
        qty = item.quantity or 0
        total_profit += (selling - cost) * qty

    credit_outstanding = (
        orders_qs.filter(balance_amount__gt=0)
        .aggregate(total=Sum("balance_amount"))["total"] or 0
    )

    # Build a simple "summary" object so template works unchanged
    class SummaryObj:
        pass

    summary = SummaryObj()
    summary.total_revenue = total_revenue
    summary.total_transactions = total_transactions
    summary.cash_revenue = cash_revenue
    summary.cash_transactions = cash_transactions
    summary.transfer_revenue = transfer_revenue
    summary.transfer_transactions = transfer_transactions
    summary.pos_revenue = pos_revenue
    summary.pos_transactions = pos_transactions
    summary.mobile_money_revenue = mobile_revenue
    summary.mobile_money_transactions = mobile_transactions
    summary.total_profit = total_profit
    summary.credit_outstanding = credit_outstanding

    # --- Low stock products (global for now) ---
    low_stock_products = Product.objects.filter(company=request.company,
        stock_status__in=["low_stock", "out_of_stock"]
    ).order_by("stock_quantity")[:10]

    # --- Top selling items (last 24h) from filtered orders ---
    last_24h_start = timezone.now() - timedelta(hours=24)
    top_items_qs = POSOrderItem.objects.filter(
        order__company=request.company,
        order__created_at__gte=last_24h_start,
        order__status="completed",
    )

    # Apply same branch visibility rules to items
    if user.role in ("manager", "cashier"):
        if user.branch:
            top_items_qs = top_items_qs.filter(order__branch=user.branch)
        else:
            top_items_qs = POSOrderItem.objects.none()
    else:
        if selected_branch_id and selected_branch_id not in ("all", "none"):
            try:
                branch_id = int(selected_branch_id)
                top_items_qs = top_items_qs.filter(order__branch_id=branch_id)
            except ValueError:
                pass

    top_selling_items = (
        top_items_qs.values("product__name", "product__sku")
        .annotate(
            total_quantity=Sum("quantity"),
            total_revenue=Sum("total_price"),
        )
        .order_by("-total_quantity")[:5]
    )

    # For branch dropdown on the page (if your template uses it)
    branches = Branch.objects.filter(company=request.company, is_active=True).order_by("name")

    context = {
        "summary": summary,
        "top_selling_items": top_selling_items,
        "low_stock_products": low_stock_products,
        "today_orders": orders_qs.order_by("-created_at"),
        "branches": branches,
        "selected_branch_id": selected_branch_id,
        "user_role": user.role,
        "user_name": user.get_full_name() or user.username,
    }
    return render(request, "sales/daily_dashboard.html", context)


# sales/views.py

@login_required
@perm_required('can_view_reports')
def yesterday_summary(request):
    """Yesterday's sales summary – branch aware"""
    yesterday = timezone.now().date() - timedelta(days=1)
    user = request.user
    selected_branch_id = request.GET.get("branch", "all")

    # Base queryset: yesterday's completed orders
    orders_qs = POSOrder.objects.filter(company=request.company,
        created_at__date=yesterday,
        status="completed",
    )

    # --- ROLE / BRANCH LOGIC ---
    if user.role in ("manager", "cashier"):
        if user.branch:
            orders_qs = orders_qs.filter(branch=user.branch)
            selected_branch_id = str(user.branch.id)
        else:
            orders_qs = POSOrder.objects.none()
            selected_branch_id = "none"
    else:
        if selected_branch_id and selected_branch_id != "all":
            try:
                branch_id = int(selected_branch_id)
                orders_qs = orders_qs.filter(branch_id=branch_id)
            except ValueError:
                pass

    # Summary (local, not saved to DB to avoid corrupting global summary)
    from decimal import Decimal

    total_revenue = orders_qs.aggregate(total=Sum("final_amount"))["total"] or Decimal("0")
    total_transactions = orders_qs.count()

    def pm_totals(method_code):
        m_qs = orders_qs.filter(payment_method=method_code)
        return (
            m_qs.aggregate(total=Sum("final_amount"))["total"] or Decimal("0"),
            m_qs.count(),
        )

    cash_revenue, cash_transactions = pm_totals("cash")
    transfer_revenue, transfer_transactions = pm_totals("transfer")
    pos_revenue, pos_transactions = pm_totals("pos")
    mobile_revenue, mobile_transactions = pm_totals("mobile_money")

    class SummaryObj:
        pass

    summary = SummaryObj()
    summary.total_revenue = total_revenue
    summary.total_transactions = total_transactions
    summary.cash_revenue = cash_revenue
    summary.cash_transactions = cash_transactions
    summary.transfer_revenue = transfer_revenue
    summary.transfer_transactions = transfer_transactions
    summary.pos_revenue = pos_revenue
    summary.pos_transactions = pos_transactions
    summary.mobile_money_revenue = mobile_revenue
    summary.mobile_money_transactions = mobile_transactions

    # Unusual transactions should also follow branch filter
    unusual_transactions = UnusualTransaction.objects.filter(company=request.company,
        order__in=orders_qs
    ).select_related("order")

    # Apply extra filters
    payment_method = request.GET.get("payment_method", "")
    min_amount = request.GET.get("min_amount", "")

    filtered_orders = orders_qs

    if payment_method:
        filtered_orders = filtered_orders.filter(payment_method=payment_method)

    if min_amount:
        try:
            min_amount_value = float(min_amount)
            filtered_orders = filtered_orders.filter(final_amount__gte=min_amount_value)
        except ValueError:
            pass

    # CSV export uses branch + filters
    if request.GET.get("export") == "csv":
        return export_yesterday_orders_csv(filtered_orders)

    branches = Branch.objects.filter(company=request.company, is_active=True).order_by("name")

    context = {
        "summary": summary,
        "yesterday_orders": filtered_orders.order_by("-created_at"),
        "unusual_transactions": unusual_transactions,
        "payment_method": payment_method,
        "min_amount": min_amount,
        "branches": branches,
        "selected_branch_id": selected_branch_id,
        "user_role": user.role,
        "user_name": user.get_full_name() or user.username,
    }
    return render(request, "sales/yesterday_summary.html", context)


def export_yesterday_orders_csv(orders):
    """Export yesterday's orders to CSV"""
    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename="yesterday_orders.csv"'
    
    writer = csv.writer(response)
    writer.writerow([
        'Order Number', 'Date', 'Cashier', 'Payment Method', 
        'Total Amount', 'Customer Name', 'Customer Phone'
    ])
    
    for order in orders:
        writer.writerow([
            order.order_number,
            order.created_at.strftime('%Y-%m-%d %H:%M'),
            order.cashier,
            order.get_payment_method_display(),
            order.final_amount,
            order.customer_name,
            order.customer_phone
        ])
    
    return response


@login_required
@perm_required('can_view_reports')
def transaction_history(request):
    """Show recent POS orders with search, day/range filter, and pagination."""
    from django.core.paginator import Paginator
    today = timezone.now().date()
    date_str    = request.GET.get("date") or ""
    date_from   = request.GET.get("date_from") or ""
    date_to     = request.GET.get("date_to") or ""
    search_query = request.GET.get("q") or ""

    orders = POSOrder.objects.filter(company=request.company, status="completed").order_by("-created_at")

    # Date range takes priority over single date
    if date_from or date_to:
        if date_from:
            df = parse_date(date_from)
            if df:
                orders = orders.filter(created_at__date__gte=df)
        if date_to:
            dt = parse_date(date_to)
            if dt:
                orders = orders.filter(created_at__date__lte=dt)
    elif date_str:
        selected_date = parse_date(date_str) or today
        orders = orders.filter(created_at__date=selected_date)
    else:
        selected_date = today
        orders = orders.filter(created_at__date=selected_date)

    if search_query:
        orders = orders.filter(
            Q(order_number__icontains=search_query)
            | Q(customer_name__icontains=search_query)
            | Q(customer_phone__icontains=search_query)
        )

    paginator  = Paginator(orders, 30)
    page_number = request.GET.get("page")
    page_obj   = paginator.get_page(page_number)

    context = {
        "orders": page_obj,
        "page_obj": page_obj,
        "selected_date": date_str or today,
        "date_from": date_from,
        "date_to": date_to,
        "search_query": search_query,
        "user_role": request.user.role,
    }
    return render(request, "sales/transaction_history.html", context)


@login_required
@perm_required('can_export_data')
def export_transactions(request):
    """Export filtered POS orders (same filters as transaction_history)."""
    today = timezone.now().date()
    date_str = request.GET.get("date") or ""
    search_query = request.GET.get("q") or ""

    if date_str:
        # Use parse_date to convert the date string into a date object
        selected_date = parse_date(date_str) or today
    else:
        selected_date = today

    orders = POSOrder.objects.filter(company=request.company, status="completed").order_by("-created_at")

    if selected_date:
        orders = orders.filter(created_at__date=selected_date)

    if search_query:
        orders = orders.filter(
            Q(order_number__icontains=search_query)
            | Q(customer_name__icontains=search_query)
            | Q(customer_phone__icontains=search_query)
        )

    response = HttpResponse(content_type="text/csv")
    filename = f"transactions_{selected_date.isoformat()}.csv"
    response["Content-Disposition"] = f'attachment; filename="{filename}"'

    writer = csv.writer(response)
    writer.writerow(
        [
            "order_number",
            "date",
            "time",
            "customer_name",
            "customer_phone",
            "payment_method",
            "total_amount",
            "discount_amount",
            "final_amount",
            "status",
        ]
    )

    for order in orders:
        dt = timezone.localtime(order.created_at) if timezone.is_aware(order.created_at) else order.created_at
        writer.writerow(
            [
                order.order_number,
                dt.date().isoformat(),
                dt.time().strftime("%H:%M:%S"),
                order.customer_name or "",
                order.customer_phone or "",
                order.payment_method,
                order.total_amount,
                order.discount_amount,
                order.final_amount,
                order.status,
            ]
        )

    return response




@login_required
@perm_required('can_export_data')
def export_all_orders(request):
    """Export all POS orders as CSV (summary)."""
    response = HttpResponse(content_type="text/csv")
    filename = f"orders_all_{timezone.now().date().isoformat()}.csv"
    response["Content-Disposition"] = f'attachment; filename="{filename}"'

    writer = csv.writer(response)
    writer.writerow(
        [
            "order_number",
            "created_date",
            "created_time",
            "customer_name",
            "customer_phone",
            "payment_method",
            "total_amount",
            "discount_amount",
            "final_amount",
            "status",
        ]
    )

    orders = POSOrder.objects.filter(company=request.company).order_by("-created_at")
    for order in orders:
        dt = timezone.localtime(order.created_at) if timezone.is_aware(order.created_at) else order.created_at
        writer.writerow(
            [
                order.order_number,
                dt.date().isoformat(),
                dt.time().strftime("%H:%M:%S"),
                order.customer_name or "",
                order.customer_phone or "",
                order.payment_method,
                order.total_amount,
                order.discount_amount,
                order.final_amount,
                order.status,
            ]
        )

    return response


@login_required
@perm_required('can_export_data')
def export_order_items(request):
    """Export all order line items as CSV."""
    response = HttpResponse(content_type="text/csv")
    filename = f"order_items_{timezone.now().date().isoformat()}.csv"
    response["Content-Disposition"] = f'attachment; filename="{filename}"'

    writer = csv.writer(response)
    writer.writerow(
        [
            "order_number",
            "order_date",
            "product_name",
            "product_sku",
            "quantity",
            "unit_price",
            "line_total",
        ]
    )

    items = POSOrderItem.objects.select_related("order", "product").filter(order__company=request.company).order_by(
        "-order__created_at"
    )
    for item in items:
        order = item.order
        product = item.product
        dt = timezone.localtime(order.created_at) if timezone.is_aware(order.created_at) else order.created_at
        writer.writerow(
            [
                order.order_number,
                dt.date().isoformat(),
                product.name if product else "",
                product.sku if product else "",
                item.quantity,
                item.unit_price,
                item.total_price,
            ]
        )

    return response


@login_required
@perm_required('can_export_data')
def export_payments(request):
    """Export all payment transactions as CSV."""
    response = HttpResponse(content_type="text/csv")
    filename = f"payments_{timezone.now().date().isoformat()}.csv"
    response["Content-Disposition"] = f'attachment; filename="{filename}"'

    writer = csv.writer(response)
    writer.writerow(
        [
            "order_number",
            "payment_method",
            "amount",
            "reference_number",
            "transaction_id",
            "status",
            "created_at",
        ]
    )

    payments = PaymentTransaction.objects.select_related("order").filter(company=request.company).order_by(
        "-created_at"
    )
    for p in payments:
        writer.writerow(
            [
                p.order.order_number if p.order else "",
                p.payment_method,
                p.amount,
                p.reference_number,
                p.transaction_id,
                p.status,
                p.created_at,
            ]
        )

    return response


@login_required
@perm_required('can_view_pnl')
@company_feature_required('feature_profit_loss')
def pnl_report(request):
    """One-click P&L report with date range filter and export."""
    from accounts.audit import log_action
    log_action(request, 'view_pnl', target='P&L Report')

    today = timezone.now().date()
    date_from_str = request.GET.get('date_from', str(today))
    date_to_str = request.GET.get('date_to', str(today))

    try:
        date_from = datetime.strptime(date_from_str, '%Y-%m-%d').date()
    except ValueError:
        date_from = today

    try:
        date_to = datetime.strptime(date_to_str, '%Y-%m-%d').date()
    except ValueError:
        date_to = today

    # All completed orders in range
    orders = POSOrder.objects.filter(
        company=request.company,
        created_at__date__gte=date_from,
        created_at__date__lte=date_to,
        status='completed',
    )

    # Apply branch filter
    user = request.user
    if user.role in ('manager', 'cashier') and getattr(user, 'branch', None):
        orders = orders.filter(branch=user.branch)

    total_revenue = orders.aggregate(t=Sum('final_amount'))['t'] or Decimal('0')
    total_orders_count = orders.count()
    credit_outstanding = orders.filter(balance_amount__gt=0).aggregate(t=Sum('balance_amount'))['t'] or Decimal('0')

    total_cost = Decimal('0')
    gross_profit = Decimal('0')
    items = POSOrderItem.objects.filter(order__in=orders).select_related('product')
    for item in items:
        cost = (item.product.cost_price or Decimal('0'))
        total_cost += cost * item.quantity
        gross_profit += (item.unit_price - cost) * item.quantity

    from analytics.models import OperatingExpense
    operating_expenses = OperatingExpense.objects.filter(
        company=request.company,
        expense_date__gte=date_from,
        expense_date__lte=date_to
    )
    total_operating_expenses = operating_expenses.aggregate(t=Sum('amount'))['t'] or Decimal('0')
    
    true_net_profit = gross_profit - total_operating_expenses

    # Payment breakdown
    payment_breakdown = []
    for code, label in POSOrder.PAYMENT_METHODS:
        m_rev = orders.filter(payment_method=code).aggregate(t=Sum('final_amount'))['t'] or Decimal('0')
        m_count = orders.filter(payment_method=code).count()
        if m_count > 0:
            payment_breakdown.append({'code': code, 'label': label, 'revenue': m_rev, 'count': m_count})

    # Per-day breakdown
    from django.db.models.functions import TruncDate
    daily_data = (
        orders.annotate(day=TruncDate('created_at'))
        .values('day')
        .annotate(rev=Sum('final_amount'), cnt=Count('id'))
        .order_by('day')
    )

    context = {
        'date_from': date_from,
        'date_to': date_to,
        'total_revenue': total_revenue,
        'total_cost': total_cost,
        'total_profit': gross_profit,  # Renamed internally for gross profit 
        'gross_profit': gross_profit,
        'total_operating_expenses': total_operating_expenses,
        'true_net_profit': true_net_profit,
        'credit_outstanding': credit_outstanding,
        'total_orders_count': total_orders_count,
        'payment_breakdown': payment_breakdown,
        'daily_data': daily_data,
        'user_role': user.role,
        'user_name': user.get_full_name() or user.username,
    }
    return render(request, 'sales/pnl_report.html', context)


@login_required
@perm_required('can_export_data')
@company_feature_required('feature_profit_loss')
def export_pnl(request):
    """Export P&L as Excel or CSV."""
    import openpyxl
    from openpyxl.styles import Font, PatternFill, Alignment

    today = timezone.now().date()
    date_from_str = request.GET.get('date_from', str(today))
    date_to_str = request.GET.get('date_to', str(today))

    try:
        date_from = datetime.strptime(date_from_str, '%Y-%m-%d').date()
    except ValueError:
        date_from = today
    try:
        date_to = datetime.strptime(date_to_str, '%Y-%m-%d').date()
    except ValueError:
        date_to = today

    orders = POSOrder.objects.filter(
        company=request.company,
        created_at__date__gte=date_from,
        created_at__date__lte=date_to,
        status='completed',
    ).order_by('created_at')

    user = request.user
    if user.role in ('manager', 'cashier') and getattr(user, 'branch', None):
        orders = orders.filter(branch=user.branch)

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = 'P&L Report'

    header_fill = PatternFill('solid', fgColor='667EEA')
    header_font = Font(bold=True, color='FFFFFF')
    bold = Font(bold=True)

    headers = ['Order #', 'Date', 'Cashier', 'Customer', 'Payment', 'Revenue', 'Cost', 'Profit', 'Credit Balance']
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center')

    row_idx = 2
    totals = {'revenue': Decimal('0'), 'cost': Decimal('0'), 'profit': Decimal('0')}

    for order in orders:
        items = POSOrderItem.objects.filter(order=order).select_related('product')
        # Calculate precise cost
        cost = sum(((item.product.cost_price or Decimal('0')) if item.product else Decimal('0')) * item.quantity for item in items)
        profit = float(order.final_amount) - float(cost)
        totals['revenue'] += order.final_amount
        totals['cost'] += cost
        totals['profit'] += Decimal(str(profit))

        ws.append([
            order.order_number,
            order.created_at.strftime('%Y-%m-%d %H:%M'),
            order.cashier,
            order.customer_name or '—',
            order.get_payment_method_display(),
            float(order.final_amount),
            float(cost),
            profit,
            float(order.balance_amount),
        ])
        row_idx += 1

    # Totals row
    ws.append(['', '', '', '', 'TOTALS', float(totals['revenue']), float(totals['cost']), float(totals['profit']), ''])
    total_row = ws.max_row
    for col in range(1, 10):
        ws.cell(row=total_row, column=col).font = bold

    response = HttpResponse(
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = f'attachment; filename="PnL_{date_from}_to_{date_to}.xlsx"'
    wb.save(response)

    from accounts.audit import log_action
    log_action(request, 'export_data', target=f'P&L {date_from} to {date_to}')
    return response


# ═══════════════════════════════════════════════════════════
#  FEATURE 7: SUSPICIOUS ACTIVITY
# ═══════════════════════════════════════════════════════════

@login_required
@role_required(['admin'])

def end_of_day(request):
    """End-of-day cash reconciliation view."""
    from sales.models import CashReconciliation
    from django.db.models import Sum

    today = timezone.now().date()
    settings = None  # removed
    currency_symbol = settings.currency_symbol if settings else '₦'

    expected = (
        POSOrder.objects.filter(
            company=request.company,
            status='completed',
            payment_method='cash',
            created_at__date=today,
        ).aggregate(total=Sum('final_amount'))['total'] or 0
    )

    existing = CashReconciliation.objects.filter(company=request.company, date=today).first()

    if request.method == 'POST':
        try:
            actual = Decimal(str(request.POST.get('actual', '0')))
        except Exception:
            actual = Decimal('0')
        notes = request.POST.get('notes', '')
        rec, _ = CashReconciliation.objects.update_or_create(
            company=request.company,
            date=today,
            defaults={
                'expected': expected,
                'actual': actual,
                'cashier': request.user.username,
                'notes': notes,
            }
        )
        messages.success(request, f"Reconciliation saved. Variance: {currency_symbol}{rec.variance:+.2f}")
        return redirect('sales:end_of_day')

    history = CashReconciliation.objects.filter(company=request.company).order_by('-date')[:10]

    context = {
        'today': today,
        'expected': expected,
        'existing': existing,
        'history': history,
        'currency_symbol': currency_symbol,
    }
    return render(request, 'sales/end_of_day.html', context)


# ─────────────────────────────────────────────────────────────
# CUSTOMER DETAIL  (#11)
# ─────────────────────────────────────────────────────────────



@login_required
@role_required(['admin', 'manager'])
def sales_by_item_report(request):
    """Sales by Item Summary Report with CSV Export."""
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')
    
    orders = POSOrder.objects.filter(company=request.company, status='completed')
    if start_date:
        orders = orders.filter(created_at__date__gte=start_date)
    if end_date:
        orders = orders.filter(created_at__date__lte=end_date)
        
    order_items = POSOrderItem.objects.filter(order__in=orders).select_related('product', 'product__category')
    
    from decimal import Decimal
    
    item_summaries = {}
    for item in order_items:
        key = item.product.id if item.product else f"Unlinked-{item.product_name}"
        
        if key not in item_summaries:
            item_summaries[key] = {
                'item_name': item.product.name if item.product else item.product_name,
                'sku': item.product.sku if item.product else '',
                'category': item.product.category.name if item.product and item.product.category else 'No Category',
                'quantity': 0,
                'gross_sales': Decimal('0.00'),
                'refunds': Decimal('0.00'),
                'discounts': Decimal('0.00'),
                'net_sales': Decimal('0.00'),
                'cost_of_goods': Decimal('0.00'),
                'gross_profit': Decimal('0.00'),
                'margin': Decimal('0.00'),
                'taxes': Decimal('0.00'),
            }
            
        summary = item_summaries[key]
        summary['quantity'] += item.quantity
        gross = item.unit_price * item.quantity
        summary['gross_sales'] += gross
        
        order_subtotal = item.order.total_amount
        if order_subtotal > 0:
            ratio = gross / order_subtotal
            item_discount = item.order.discount_amount * ratio
            item_tax = item.order.tax_amount * ratio
        else:
            item_discount = Decimal('0.00')
            item_tax = Decimal('0.00')
            
        summary['discounts'] += item_discount
        summary['taxes'] += item_tax
        summary['refunds'] += Decimal('0.00')
        
        net = gross - item_discount
        summary['net_sales'] += net
        
        cost = (item.product.cost_price or Decimal('0.00')) if item.product else Decimal('0.00')
        cog = cost * item.quantity
        summary['cost_of_goods'] += cog
        
        profit = net - cog
        summary['gross_profit'] += profit
        
    for key, summary in item_summaries.items():
        if summary['net_sales'] > 0:
            summary['margin'] = (summary['gross_profit'] / summary['net_sales']) * 100
            
    report_data = sorted(item_summaries.values(), key=lambda x: x['quantity'], reverse=True)
    
    if request.GET.get('export') == 'csv':
        response = HttpResponse(content_type='text/csv')
        response['Content-Disposition'] = 'attachment; filename="sales_by_items_summary.csv"'
        
        writer = csv.writer(response)
        writer.writerow(['Item name', 'SKU', 'Category', 'Items sold', 'Gross sales', 'Refunds', 'Discounts', 'Net sales', 'Cost of goods', 'Gross profit', 'Margin', 'Taxes'])
        
        for row in report_data:
            writer.writerow([
                row['item_name'], row['sku'], row['category'], row['quantity'],
                round(row['gross_sales'], 2), round(row['refunds'], 2), round(row['discounts'], 2),
                round(row['net_sales'], 2), round(row['cost_of_goods'], 2),
                round(row['gross_profit'], 2), f"{round(row['margin'], 2)}%", round(row['taxes'], 2)
            ])
            
        return response
        
    context = {
        'start_date': start_date,
        'end_date': end_date,
        'report_data': report_data,
        'user_role': request.user.role,
        'user_name': request.user.get_full_name() or request.user.username,
    }
    return render(request, 'sales/sales_by_item.html', context)




