"""controllers/Staff/LoanregisterController.py  (replace your file)"""
import logging
from urllib.parse import urlencode

from django.db.models import (
    Count, DecimalField, F, OuterRef, Q, Subquery, Sum, Value
)
from django.db.models.functions import Coalesce
from django.http import JsonResponse
from django.urls import reverse
from django.views.decorators.http import require_POST

from NexusApp.models import Place, LoanPayment
from NexusApp.models import Loan
from NexusApp.utils.excel import date_range, range_label, xlsx_response

logger = logging.getLogger(__name__)
MONEY = DecimalField(max_digits=14, decimal_places=2)


def admin_view_loan_request_page(request):
    if request.headers.get('x-requested-with') != 'XMLHttpRequest':
        return JsonResponse({'status': 'error', 'message': 'Invalid request.'}, status=400)

    try:
        draw = int(request.GET.get('draw', 1))
        start = int(request.GET.get('start', 0))
        length = int(request.GET.get('length', 10))
        search = request.GET.get('search[value]', '').strip()
        order_col = request.GET.get('order[0][column]', '')
        order_dir = request.GET.get('order[0][dir]', 'asc')

        # total paid per place (separate subquery so sums are not multiplied)
        paid_sq = (
            LoanPayment.objects.filter(loan__place=OuterRef('pk'))
            .values('loan__place').annotate(s=Sum('amount')).values('s')
        )

        places = Place.objects.annotate(
            total_loans=Count('loans'),
            completed_loans=Count('loans', filter=Q(loans__status='completed')),
            total_amount=Coalesce(Sum('loans__loan_amount'), Value(0), output_field=MONEY),
            payable=Coalesce(Sum('loans__total_payable'), Value(0), output_field=MONEY),
            paid=Coalesce(Subquery(paid_sq, output_field=MONEY), Value(0), output_field=MONEY),
        ).annotate(pending_amount=F('payable') - F('paid'))

        records_total = Place.objects.count()
        if search:
            places = places.filter(name__icontains=search)
        records_filtered = places.count()

        order_map = {'1': 'name', '2': 'total_loans', '3': 'total_amount'}
        if order_col in order_map:
            field = order_map[order_col]
            direction = '-' if order_dir == 'desc' else ''
            places = places.order_by(f'{direction}{field}', '-created_at', '-id')
        else:
            # Latest added place first
            places = places.order_by('-created_at', '-id')

        rows = places[start:] if length == -1 else places[start:start + length]

        data = []
        for i, p in enumerate(rows, start=start + 1):
            q = urlencode({'place': p.name})
            data.append({
                'DT_RowIndex': i,
                'place_id': p.id,
                'place_name': p.name,
                'total_loans': p.total_loans,
                'completed_loans': p.completed_loans,
                'total_amount': float(p.total_amount),
                'total_amount_display': f"₹{p.total_amount:,.2f}",
                'pending_amount_display': f"₹{max(p.pending_amount, 0):,.2f}",
                'report_url': reverse('admin:loan_reports', args=[p.id]),
                'completed_url': reverse('admin:place_completed_loan', args=[p.id]),
            })

        return JsonResponse({
            'draw': draw,
            'recordsTotal': records_total,
            'recordsFiltered': records_filtered,
            'data': data,
        })

    except Exception:
        logger.exception('view_loan_request_page failed')
        return JsonResponse({
            'draw': 1, 'recordsTotal': 0, 'recordsFiltered': 0, 'data': [],
            'error': 'Could not load loan registers.',
        }, status=500)

@require_POST
def admin_add_place(request):
    name = request.POST.get('place_name', '').strip()
    if not name:
        return JsonResponse({'status': 'error', 'message': 'Place name is required.'}, status=400)
    if len(name) > 150:
        return JsonResponse({'status': 'error', 'message': 'Max 150 characters.'}, status=400)
    place, created = Place.objects.get_or_create(name=name)
    if not created:
        return JsonResponse({'status': 'error', 'message': 'Place already exists.'}, status=400)
    return JsonResponse({'status': 'success', 'message': 'Place added.', 'id': place.id})


@require_POST
def admin_update_place(request):
    place_id = request.POST.get('place_id')
    name = request.POST.get('place_name', '').strip()
    if not name:
        return JsonResponse({'status': 'error', 'message': 'Place name is required.'}, status=400)
    try:
        place = Place.objects.get(pk=place_id)
    except (Place.DoesNotExist, ValueError):
        return JsonResponse({'status': 'error', 'message': 'Place not found.'}, status=404)
    if Place.objects.exclude(pk=place.pk).filter(name__iexact=name).exists():
        return JsonResponse({'status': 'error', 'message': 'Place already exists.'}, status=400)
    place.name = name
    place.save(update_fields=['name'])   # all loans follow automatically (FK)
    return JsonResponse({'status': 'success', 'message': 'Place updated.'})


# for completed loan page view functioon

def admin_place_completed_loan_data(request):
    place_id = request.GET.get('place_id')
    loans = Loan.objects.filter(place_id=place_id, status='completed') \
                    .select_related('customer') \
                    .order_by('-closed_date', '-id')
    data = []
    for i, l in enumerate(loans, start=1):
        data.append({
            'id': l.id,
            'sno': i,
            'customer_name': l.customer_name,
            'phone_number': l.phone_number,
            'loan_date': l.loan_date.strftime('%Y-%m-%d'),
            'loan_amount': float(l.loan_amount),
            'payment_type': l.get_payment_type_display(),
            'number_of_dues': l.number_of_dues,
            'closed_date': l.closed_date.strftime('%Y-%m-%d') if l.closed_date else '-',
        })
    return JsonResponse({'data': data})

# staff panel loan entries page view function
def admin_loan_entries_data(request):
    paid_sq = (
        LoanPayment.objects.filter(loan=OuterRef('pk'))
        .values('loan').annotate(s=Sum('amount')).values('s')
    )
    loans = (
        Loan.objects.select_related('place', 'customer')
        .annotate(paid=Coalesce(Subquery(paid_sq, output_field=MONEY),
                                Value(0), output_field=MONEY))
        .order_by('-id')
    )

    data = []
    for i, l in enumerate(loans, start=1):
        pending = max(l.total_payable - l.paid, 0)
        data.append({
            'id': l.id,
            'sno': i,
            'customer_name': l.customer_name,
            'phone_number': l.phone_number,
            'place': l.place.name,
            'loan_amount': float(l.loan_amount),
            'paid_amount': float(l.paid),
            'pending_amount': float(pending),
            'due_amount': float(l.due_amount),
            'number_of_dues': l.number_of_dues,
            'status': 'Completed' if l.status == 'completed' else 'Pending',
        })
    return JsonResponse({'data': data})

# loan entries page completed data page functon

def completed_loans_filtered(request, qs):
    """Completed loans closed inside from_date / to_date, plus the optional ?search= text."""
    start, end = date_range(request)                 # on closed_date
    if start:
        qs = qs.filter(closed_date__gte=start)
    if end:
        qs = qs.filter(closed_date__lte=end)
    search = request.GET.get('search', '').strip()
    if search:
        qs = qs.filter(
            Q(customer__customer_name__icontains=search)
            | Q(customer__phone_number__icontains=search)
            | Q(place__name__icontains=search)
            | Q(payment_type__icontains=search)
        )
    return qs


def completed_loan_rows(loans):
    return [{
        'id': l.id,
        'sno': i,
        'customer_name': l.customer_name,
        'phone_number': l.phone_number,
        'place': l.place.name,
        'loan_date': l.loan_date.strftime('%Y-%m-%d'),
        'loan_amount': float(l.loan_amount),
        'payment_type': l.get_payment_type_display(),
        'number_of_dues': l.number_of_dues,
        'closed_date': l.closed_date.strftime('%Y-%m-%d') if l.closed_date else '-',
    } for i, l in enumerate(loans, start=1)]


def completed_loans_xlsx(request, rows):
    start, end = date_range(request)
    body = [[r['sno'], r['customer_name'], r['phone_number'], r['place'], r['loan_date'],
             r['loan_amount'], r['payment_type'], r['number_of_dues'], r['closed_date']]
            for r in rows]
    return xlsx_response(
        'completed_loans', 'Completed Loans',
        ['S.No', 'Customer Name', 'Phone', 'Place', 'Loan Date', 'Loan Amount',
         'Payment Type', 'Number of Dues', 'Closed Date'],
        body, subtitle=f'Closed date: {range_label(start, end)}',
        totals=['Total', '', '', '', '', sum(r['loan_amount'] for r in rows)] if rows else None)


def _admin_completed_loans(request):
    return completed_loans_filtered(
        request,
        Loan.objects.filter(status='completed')
        .select_related('place', 'customer')
        .order_by('-closed_date', '-id'),
    )


def admin_completed_loan_data(request):
    return JsonResponse({'data': completed_loan_rows(_admin_completed_loans(request))})


def admin_completed_loan_export(request):
    return completed_loans_xlsx(request, completed_loan_rows(_admin_completed_loans(request)))
