from django.contrib.auth.decorators import login_required
from django.shortcuts import render, get_object_or_404
from django.db.models import Sum, F, Count
from sales.models import Sale
from expenses.models import Expense
from inventory.models import Product
from customers.models import Customer
from suppliers.models import Supplier
from orders.models import Order
from hr.models import Employee, Attendance, Payroll, Leave
from django.utils import timezone
from datetime import timedelta
from django.template.loader import render_to_string
from django.http import HttpResponse
import pandas as pd
from decimal import Decimal


@login_required(login_url='/auth/login/')
def reports_dashboard(request):
    """Reports dashboard with overview of all key metrics"""
    # Get the current date and 30 days period
    today = timezone.now().date()
    thirty_days_ago = today - timedelta(days=30)
    
    # Calculate 30-day totals
    total_sales = Sale.objects.filter(
        is_complete=True, 
        sale_date__date__gte=thirty_days_ago,
        sale_date__date__lte=today
    ).aggregate(total=Sum('total_amount'))['total'] or 0
    
    total_expenses = Expense.objects.filter(
        date__gte=thirty_days_ago,
        date__lte=today
    ).aggregate(total=Sum('amount'))['total'] or 0
    
    # Calculate profit/loss
    profit_loss = total_sales - total_expenses
    
    # Total customers
    total_customers = Customer.objects.count()
    
    context = {
        'total_sales': total_sales,
        'total_expenses': total_expenses,
        'profit_loss': profit_loss,
        'total_customers': total_customers,
        'generated_date': timezone.now(),
    }
    
    return render(request, 'reports/reports_dashboard.html', context)


@login_required(login_url='/auth/login/')
# Profit and Loss Report
def profit_loss_report(request):
    # Get date range from user input (default to today if not provided)
    start_date = request.GET.get('start_date', timezone.now().date())
    end_date = request.GET.get('end_date', timezone.now().date())

    # Filter sales and expenses by date range
    total_sales = \
    Sale.objects.filter(is_complete=True, sale_date__range=[start_date, end_date]).aggregate(total=Sum('total_amount'))[
        'total'] or 0
    total_expenses = Expense.objects.filter(date__range=[start_date, end_date]).aggregate(total=Sum('amount'))[
                         'total'] or 0

    # Calculate profit or loss
    profit_loss = total_sales - total_expenses

    context = {
        'total_sales': total_sales,
        'total_expenses': total_expenses,
        'profit_loss': profit_loss,
        'start_date': start_date,
        'end_date': end_date,
    }

    return render(request, 'reports/profit_loss_report.html', context)


# Sales Report
@login_required(login_url='/auth/login/')
def sales_report(request):
    # Get date range
    start_date = request.GET.get('start_date', timezone.now().date())
    end_date = request.GET.get('end_date', timezone.now().date())

    # Filter and aggregate sales data
    sales = Sale.objects.filter(sale_date__range=[start_date, end_date]).aggregate(total_sales=Sum('total_amount'))
    top_selling_products = Sale.objects.values('items__product__name').annotate(
        total_sold=Sum('items__quantity')).order_by('-total_sold')[:5]

    context = {
        'sales': sales,
        'top_selling_products': top_selling_products,
        'start_date': start_date,
        'end_date': end_date,
    }

    return render(request, 'reports/sales_report.html', context)


# Expense Report
@login_required(login_url='/auth/login/')
def expense_report(request):
    start_date = request.GET.get('start_date', timezone.now().date())
    end_date = request.GET.get('end_date', timezone.now().date())

    expenses = Expense.objects.filter(date__range=[start_date, end_date]).values('category__name').annotate(
        total=Sum('amount'))

    context = {
        'expenses': expenses,
        'start_date': start_date,
        'end_date': end_date,
    }

    return render(request, 'reports/expense_report.html', context)


# Inventory Report
@login_required(login_url='/auth/login/')
def inventory_report(request):
    # Show total inventory value and low stock items
    total_inventory_value = Product.objects.aggregate(total_value=Sum(F('stock_quantity') * F('buying_price')))[
                                'total_value'] or 0
    low_stock_items = Product.objects.filter(stock_quantity__lt=10)

    context = {
        'total_inventory_value': total_inventory_value,
        'low_stock_items': low_stock_items,
    }

    return render(request, 'reports/inventory_report.html', context)


# Customer Statement Report (PDF Export)
@login_required(login_url='/auth/login/')
def customer_statement_pdf(request, customer_id):
    from django.template.loader import get_template
    from xhtml2pdf import pisa
    from customers.models import Customer
    from sales.models import Sale
    from settings.models import Shop
    from datetime import datetime, timedelta
    
    customer = get_object_or_404(Customer, customer_id=customer_id)
    
    # Get shop details with fallback
    try:
        shop = Shop.objects.first()
    except Shop.DoesNotExist:
        shop = None
    
    # Get absolute logo path for PDF generation
    logo_path = None
    if shop and shop.logo:
        try:
            logo_path = shop.logo.path
        except:
            logo_path = None
    
    # Date range filters
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')
    sort_order = request.GET.get('sort_order', 'desc')
    
    # Set default dates if not provided
    if not start_date:
        start_date = (timezone.now() - timedelta(days=30)).date()
    else:
        start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
    
    if not end_date:
        end_date = timezone.now().date()
    else:
        end_date = datetime.strptime(end_date, '%Y-%m-%d').date()
    
    # Calculate opening balance (transactions before start_date)
    opening_balance = Decimal('0.00')
    opening_sales = Sale.objects.filter(
        customer=customer, 
        payment_method='credit',
        sale_date__date__lt=start_date
    ).order_by('sale_date')
    
    for sale in opening_sales:
        opening_balance += sale.total_amount
        credit_paid = sale.credit_amount or Decimal('0.00')
        if credit_paid > 0:
            opening_balance -= credit_paid
    
    # Get all transactions for the specified period
    all_sales = Sale.objects.filter(
        customer=customer,
        sale_date__date__gte=start_date,
        sale_date__date__lte=end_date
    ).order_by('sale_date')
    
    # Calculate running balance starting from opening balance
    running_balance = opening_balance
    statement_entries = []
    
    # Initialize totals
    total_debits = Decimal('0.00')
    total_credits = Decimal('0.00')
    total_cash_sales = Decimal('0.00')
    
    # Sort sales by date for proper balance calculation
    sales_for_balance = all_sales.order_by('sale_date')
    
    for sale in sales_for_balance:
        payment_method_display = dict(Sale.PAYMENT_METHOD_CHOICES).get(sale.payment_method, sale.payment_method)
        
        if sale.payment_method == 'credit':
            # For credit sales, show the invoice amount as debit
            running_balance += sale.total_amount
            total_debits += sale.total_amount
            
            statement_entries.append({
                'date': sale.sale_date,
                'description': f'Invoice #{sale.id} ({payment_method_display})',
                'cash_sale': None,
                'credit_sale': sale.total_amount,
                'credit_payment': None,
                'balance': running_balance,
                'sale': sale
            })
            
            # If there was a credit payment, show it as credit
            if sale.credit_amount and sale.credit_amount > 0:
                running_balance -= sale.credit_amount
                total_credits += sale.credit_amount
                statement_entries.append({
                    'date': sale.sale_date,
                    'description': f'Payment for Invoice #{sale.id}',
                    'cash_sale': None,
                    'credit_sale': None,
                    'credit_payment': sale.credit_amount,
                    'balance': running_balance,
                    'sale': sale
                })
        else:
            # For cash, M-Pesa, and cheque sales, show as paid transaction
            total_credits += sale.total_amount
            total_cash_sales += sale.total_amount
            statement_entries.append({
                'date': sale.sale_date,
                'description': f'Sale #{sale.id} ({payment_method_display})',
                'cash_sale': sale.total_amount,
                'credit_sale': None,
                'credit_payment': None,
                'balance': running_balance,  # Balance doesn't change for paid transactions
                'sale': sale
            })
    
    # Sort statement entries by date and sort_order
    statement_entries.sort(key=lambda x: x['date'], reverse=(sort_order == 'desc'))
    
    context = {
        'customer': customer,
        'statement_entries': statement_entries,
        'opening_balance': opening_balance,
        'final_balance': running_balance,
        'start_date': start_date,
        'end_date': end_date,
        'generated_date': timezone.now(),
        'shop': shop,
        'logo_path': logo_path,
        'total_debits': total_debits,
        'total_credits': total_credits,
        'total_cash_sales': total_cash_sales,
    }
    
    template = get_template('reports/customer_statement_pdf.html')
    html = template.render(context)
    
    response = HttpResponse(content_type='application/pdf')
    response['Content-Disposition'] = f'attachment; filename="statement_{customer.customer_id}.pdf"'
    
    pisa_status = pisa.CreatePDF(html, dest=response)
    if pisa_status.err:
        return HttpResponse('We had some errors <pre>' + html + '</pre>')
    
    return response


# Customer Report View
@login_required(login_url='/auth/login/')
def customer_report(request):
    # Get the date range from the user input (default to last 30 days)
    start_date = request.GET.get('start_date', timezone.now().date() - timedelta(days=30))
    end_date = request.GET.get('end_date', timezone.now().date())

    # Total number of customers
    total_customers = Customer.objects.filter(date_added__range=[start_date, end_date]).count()

    # Top customers by purchase volume
    top_customers = Sale.objects.filter(sale_date__range=[start_date, end_date]).values(
        'customer__id', 'customer__first_name', 'customer__last_name'
    ).annotate(total_spent=Sum('total_amount')).order_by('-total_spent')[:5]

    # Customers nearing credit limit
    customers_nearing_credit_limit = Customer.objects.filter(credit_balance__gte=F('credit_limit') - 100)

    # Customer growth over the date range
    customer_growth = Customer.objects.filter(date_added__range=[start_date, end_date]).count()

    context = {
        'total_customers': total_customers,
        'top_customers': top_customers,
        'customers_nearing_credit_limit': customers_nearing_credit_limit,
        'customer_growth': customer_growth,
        'start_date': start_date,
        'end_date': end_date,
    }

    return render(request, 'reports/customer_report.html', context)


# Supplier Report View
@login_required(login_url='/auth/login/')
def supplier_report(request):
    # Get the date range from the user input (default to last 30 days)
    start_date = request.GET.get('start_date', timezone.now().date() - timedelta(days=30))
    end_date = request.GET.get('end_date', timezone.now().date())

    # Total number of suppliers
    total_suppliers = Supplier.objects.count()

    # Top suppliers by expenditure
    top_suppliers = Order.objects.filter(created_at__range=[start_date, end_date]).annotate(
        total_spent=Sum(F('items__quantity_ordered') * F('items__buying_price'))
    ).values('supplier__name', 'total_spent').order_by('-total_spent')[:5]

    # Total expenditure on suppliers in the selected period
    total_expenditure = Order.objects.filter(created_at__range=[start_date, end_date]).aggregate(
        total_spent=Sum(F('items__quantity_ordered') * F('items__buying_price'))
    )['total_spent'] or 0

    # Pending orders for suppliers
    pending_orders = Order.objects.filter(is_received=False, created_at__range=[start_date, end_date]).values(
        'supplier__name'
    ).annotate(total_pending=Count('id'))

    context = {
        'total_suppliers': total_suppliers,
        'top_suppliers': top_suppliers,
        'total_expenditure': total_expenditure,
        'pending_orders': pending_orders,
        'start_date': start_date,
        'end_date': end_date,
    }

    return render(request, 'reports/supplier_report.html', context)


# Employee Report
@login_required(login_url='/auth/login/')
def employee_report(request):
    # Get the date range from the user input (default to last 30 days)
    start_date = request.GET.get('start_date', timezone.now().date() - timedelta(days=30))
    end_date = request.GET.get('end_date', timezone.now().date())

    # Total number of employees
    total_employees = Employee.objects.count()

    # Employee attendance summary (present/absent count)
    attendance_summary = Attendance.objects.filter(date__range=[start_date, end_date]).values(
        'employee__first_name', 'employee__last_name', 'status'
    ).annotate(total=Count('status')).order_by('-total')

    # Total payroll paid in the selected period
    total_payroll = Payroll.objects.filter(month__range=[start_date, end_date]).aggregate(
        total_paid=Sum('salary_paid')
    )['total_paid'] or 0

    # Employee growth (new employees added in the period)
    employee_growth = Employee.objects.filter(hire_date__range=[start_date, end_date]).count()

    # Employee leave summary
    leave_summary = Leave.objects.filter(start_date__range=[start_date, end_date]).values(
        'employee__first_name', 'employee__last_name', 'start_date', 'end_date', 'approved'
    ).order_by('start_date')

    context = {
        'total_employees': total_employees,
        'attendance_summary': attendance_summary,
        'total_payroll': total_payroll,
        'employee_growth': employee_growth,
        'leave_summary': leave_summary,
        'start_date': start_date,
        'end_date': end_date,
    }

    return render(request, 'reports/employee_report.html', context)


# Order Report
@login_required(login_url='/auth/login/')
def order_report(request):
    # Pending orders and recent order history
    start_date = request.GET.get('start_date', timezone.now().date())
    end_date = request.GET.get('end_date', timezone.now().date())

    pending_orders = Order.objects.filter(is_received=False).count()
    recent_orders = Order.objects.filter(created_at__range=[start_date, end_date])

    context = {
        'pending_orders': pending_orders,
        'recent_orders': recent_orders,
        'start_date': start_date,
        'end_date': end_date,
    }

    return render(request, 'reports/order_report.html', context)


# Export Profit and Loss Report to PDF

# Export Profit and Loss Report to Excel
@login_required(login_url='/auth/login/')
def export_profit_loss_excel(request):
    # Get the date range from the user input
    start_date = request.GET.get('start_date', timezone.now().date())
    end_date = request.GET.get('end_date', timezone.now().date())

    # Calculate total sales and expenses in the date range
    total_sales = Sale.objects.filter(is_complete=True, sale_date__range=[start_date, end_date]).aggregate(total=Sum('total_amount'))['total'] or 0
    total_expenses = Expense.objects.filter(date__range=[start_date, end_date]).aggregate(total=Sum('amount'))['total'] or 0

    # Calculate profit or loss
    profit_loss = total_sales - total_expenses

    # Create a Pandas DataFrame with the data
    data = {
        'Metric': ['Total Sales', 'Total Expenses', 'Profit/Loss'],
        'Amount (Ksh)': [total_sales, total_expenses, profit_loss]
    }
    df = pd.DataFrame(data)

    # Create an HTTP response with an Excel file
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="profit_loss_report.xlsx"'

    # Write the DataFrame to an Excel file using Pandas
    df.to_excel(response, index=False)

    return response


# View for exporting Inventory Report to Excel
@login_required(login_url='/auth/login/')
def export_inventory_report_excel(request):
    # Get the date range from the user input (optional, depends on your logic)
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')

    # Fetch the low stock items (modify query based on your needs)
    low_stock_items = Product.objects.filter(stock_quantity__lt=10).values('name', 'category__name', 'stock_quantity', 'reorder_level')

    # Create a Pandas DataFrame with the data
    df = pd.DataFrame(low_stock_items)

    # Create an HTTP response with an Excel file
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename="inventory_report.xlsx"'

    # Write the DataFrame to an Excel file using Pandas and openpyxl
    df.to_excel(response, index=False, engine='openpyxl')

    return response