"""
Admin portal API views — reproduces every PHP admin page's backend logic as REST endpoints.
Each view maps 1:1 to a PHP admin page's queries and business operations.
"""
import random
from decimal import Decimal
from django.db import transaction
from django.db.models import Sum, Count, Q, F, Value
from django.db.models.functions import Coalesce
from django.utils import timezone
from django.shortcuts import get_object_or_404
from django.contrib.auth import authenticate
from rest_framework import status
from rest_framework.decorators import api_view, permission_classes
from rest_framework.permissions import AllowAny, IsAuthenticated
from rest_framework.response import Response
from rest_framework_simplejwt.tokens import RefreshToken

from apps.core.models import (
    User, Package, Rank, Investment, Transaction, Pin,
    MatchingSchedule, Genealogy, CronLog
)
from .serializers import (
    AdminLoginSerializer, DashboardStatsSerializer, RecentTransactionSerializer,
    MemberListSerializer, MemberStatusUpdateSerializer, BulkDeleteSerializer,
    PackageSerializer, PackageCreateUpdateSerializer,
    PinListSerializer, PinGenerateSerializer,
    WithdrawalListSerializer,
    MatchingScheduleSerializer,
)
from .permissions import IsAdminUser


# ══════════════════════════════════════════════
# AUTH — maps to admin/login.php
# ══════════════════════════════════════════════

@api_view(['POST'])
@permission_classes([AllowAny])
def admin_login(request):
    """Admin login — matches admin/login.php session creation (supports username, email, or MID)."""
    login_input = str(request.data.get('username', '')).strip()
    password = str(request.data.get('password', ''))

    if not login_input or not password:
        return Response(
            {'success': False, 'error': {'code': 'INVALID_CREDENTIALS', 'message': 'Username/MID and password are required.'}},
            status=status.HTTP_400_BAD_REQUEST
        )

    # 1. Direct authenticate
    user = authenticate(username=login_input, password=password)

    # 2. Email or MID lookup
    if not user:
        u = User.objects.filter(
            Q(username__iexact=login_input) |
            Q(email__iexact=login_input) |
            Q(mid__iexact=login_input)
        ).first()
        if u and u.check_password(password):
            user = u

    if not user or not user.is_admin_user or user.status == 'suspended':
        return Response(
            {'success': False, 'error': {'code': 'INVALID_CREDENTIALS', 'message': 'Invalid admin credentials.'}},
            status=status.HTTP_401_UNAUTHORIZED
        )

    refresh = RefreshToken.for_user(user)
    return Response({
        'success': True,
        'access': str(refresh.access_token),
        'refresh': str(refresh),
        'admin': {
            'id': user.id,
            'username': user.username,
            'email': user.email,
        }
    })


# ══════════════════════════════════════════════
# DASHBOARD — maps to admin/dashboard.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_dashboard(request):
    """
    Dashboard overview — maps to admin/dashboard.php.
    Returns 6 stat cards + 10 most recent transactions.
    PHP queries reproduced exactly.
    """
    # Total Members: SELECT COUNT(*) FROM users
    total_members = User.objects.count()

    # Total Business Volume: SELECT SUM(amount) FROM investments
    total_business = Investment.objects.aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']

    # Total ROI Paid: SELECT SUM(amount) FROM transactions WHERE type = 'ROI'
    total_roi = Transaction.objects.filter(type='ROI').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']

    # Total Withdrawals Split: Requested, Disbursed (Approved), Pending, Rejected
    total_requested = Transaction.objects.filter(type='WITHDRAWAL').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_pending = Transaction.objects.filter(type='WITHDRAWAL', status='pending').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_approved = Transaction.objects.filter(type='WITHDRAWAL', status='completed').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_rejected = Transaction.objects.filter(type='WITHDRAWAL', status='rejected').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_fees_collected = Transaction.objects.filter(type='WITHDRAWAL', status='completed').aggregate(
        total=Coalesce(Sum('fee'), Decimal('0.00'))
    )['total']
    total_net_disbursed = total_approved - total_fees_collected

    # Active Plans: SELECT COUNT(*) FROM investments WHERE status = 'active'
    active_plans = Investment.objects.filter(status='active').count()

    # Available PINs: SELECT COUNT(*) FROM pins WHERE status = 'unused'
    available_pins = Pin.objects.filter(status='unused').count()

    # Recent Transactions: SELECT t.*, u.username FROM transactions t JOIN users u ... ORDER BY created_at DESC LIMIT 10
    recent_txns = Transaction.objects.select_related('user').order_by('-created_at')[:10]

    return Response({
        'stats': {
            'total_members': total_members,
            'total_business_volume': str(total_business),
            'total_roi_paid': str(total_roi),
            'total_withdrawals': str(total_approved),
            'total_withdrawals_disbursed': str(total_net_disbursed),
            'total_withdrawals_requested': str(total_requested),
            'total_withdrawals_pending': str(total_pending),
            'total_withdrawals_approved': str(total_approved),
            'total_withdrawals_rejected': str(total_rejected),
            'total_withdrawal_fees': str(total_fees_collected),
            'active_plans': active_plans,
            'available_pins': available_pins,
        },
        'recent_transactions': RecentTransactionSerializer(recent_txns, many=True).data,
    })


# ══════════════════════════════════════════════
# MEMBERS — maps to admin/members.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_members_list(request):
    """
    Members list with search/filter + pagination — maps to admin/members.php GET.
    Search: username, email, MID, or full_name.
    Supports: rank_id, package_amount filters.
    Pagination: page (1-indexed), page_size (default 25).
    """
    search = request.query_params.get('search', '')
    rank_filter = request.query_params.get('rank_id', '')
    package_filter = request.query_params.get('package_amount', '')

    try:
        page = max(1, int(request.query_params.get('page', 1)))
        page_size = min(100, max(10, int(request.query_params.get('page_size', 25))))
    except (ValueError, TypeError):
        page = 1
        page_size = 25

    # Build query matching PHP: SELECT DISTINCT u.* FROM users u ...
    queryset = User.objects.all()

    if package_filter:
        queryset = queryset.filter(
            investments__amount=Decimal(package_filter)
        ).distinct()

    if search:
        # Expanded search: username, email, MID, or full_name
        queryset = queryset.filter(
            Q(username__icontains=search)
            | Q(email__icontains=search)
            | Q(mid__icontains=search)
            | Q(full_name__icontains=search)
        )

    if rank_filter != '':
        queryset = queryset.filter(rank_id=int(rank_filter))

    queryset = queryset.order_by('-created_at')

    total = queryset.count()
    import math
    total_pages = max(1, math.ceil(total / page_size))
    page = min(page, total_pages)
    offset = (page - 1) * page_size
    paginated = queryset[offset: offset + page_size]

    ranks = list(Rank.objects.all().order_by('matching_business'))
    serializer = MemberListSerializer(
        paginated, many=True,
        context={'ranks': ranks}
    )

    return Response({
        'members': serializer.data,
        'total': total,
        'page': page,
        'page_size': page_size,
        'total_pages': total_pages,
        'ranks': [{'id': i + 1, 'name': r.name, 'matching': str(r.matching_business)} for i, r in enumerate(ranks)],
        'packages': list(Package.objects.values_list('amount', flat=True).order_by('amount')),
    })


@api_view(['PATCH'])
@permission_classes([IsAdminUser])
def admin_member_status(request, user_id):
    """Update member status — maps to admin/members.php POST action=update_status."""
    serializer = MemberStatusUpdateSerializer(data=request.data)
    serializer.is_valid(raise_exception=True)

    try:
        user = User.objects.get(id=user_id)
        user.status = serializer.validated_data['status']
        user.save(update_fields=['status'])
        return Response({'success': True, 'message': 'Status updated successfully.'})
    except User.DoesNotExist:
        return Response(
            {'success': False, 'error': 'User not found.'},
            status=status.HTTP_404_NOT_FOUND
        )


@api_view(['POST'])
@permission_classes([IsAdminUser])
def admin_members_bulk_delete(request):
    """Bulk delete members — maps to admin/members.php POST action=bulk_delete."""
    serializer = BulkDeleteSerializer(data=request.data)
    serializer.is_valid(raise_exception=True)

    ids = serializer.validated_data['ids']
    # Filter out root user ID 1 (safety, matching PHP)
    ids = [i for i in ids if i != 1]

    if not ids:
        return Response(
            {'success': False, 'error': 'Cannot delete root admin.'},
            status=status.HTTP_400_BAD_REQUEST
        )

    with transaction.atomic():
        # Delete in order matching PHP: genealogy, investments, transactions, wallets, matching_schedules, pins, users
        Genealogy.objects.filter(Q(user_id__in=ids) | Q(parent_id__in=ids)).delete()
        Investment.objects.filter(user_id__in=ids).delete()
        Transaction.objects.filter(Q(user_id__in=ids) | Q(related_user_id__in=ids)).delete()
        from apps.core.models import UserWallet
        UserWallet.objects.filter(user_id__in=ids).delete()
        MatchingSchedule.objects.filter(user_id__in=ids).delete()
        Pin.objects.filter(Q(used_by_id__in=ids) | Q(assigned_to_id__in=ids)).delete()
        count = User.objects.filter(id__in=ids).delete()[0]

    return Response({'success': True, 'deleted_count': count})


@api_view(['POST'])
@permission_classes([IsAdminUser])
def admin_reset_system(request):
    """
    Reset system (clear all except root) — maps to admin/members.php POST action=clear_system.
    Exactly reproduces the PHP transaction.
    """
    with transaction.atomic():
        Genealogy.objects.filter(Q(user_id__gt=1) | Q(parent_id__gt=1)).delete()
        Investment.objects.filter(user_id__gt=1).delete()
        Transaction.objects.filter(Q(user_id__gt=1) | Q(related_user_id__gt=1)).delete()
        from apps.core.models import UserWallet
        UserWallet.objects.filter(user_id__gt=1).delete()
        MatchingSchedule.objects.filter(user_id__gt=1).delete()
        Pin.objects.filter(Q(used_by_id__gt=1) | Q(assigned_to_id__gt=1)).delete()
        User.objects.filter(id__gt=1).delete()

        # Reset root user MLM metrics (matching PHP)
        User.objects.filter(id=1).update(
            rank_id=0,
            total_investment=Decimal('0.00'),
            left_leg_business=Decimal('0.00'),
            right_leg_business=Decimal('0.00'),
            rank_income_days=0,
            sponsor=None,
            placement=None,
            status='active',
        )

    return Response({'success': True, 'message': 'System reset complete.'})


# ══════════════════════════════════════════════
# PACKAGES — maps to admin/packages.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_packages_list(request):
    """List all packages — maps to admin/packages.php GET."""
    packages = Package.objects.all().order_by('amount')
    return Response({
        'packages': PackageSerializer(packages, many=True).data,
    })


@api_view(['POST'])
@permission_classes([IsAdminUser])
def admin_package_create(request):
    """Create new package — maps to admin/packages.php POST (no id)."""
    serializer = PackageCreateUpdateSerializer(data=request.data)
    serializer.is_valid(raise_exception=True)
    package = serializer.save()
    return Response({
        'success': True,
        'package': PackageSerializer(package).data,
    }, status=status.HTTP_201_CREATED)


@api_view(['PUT', 'DELETE'])
@permission_classes([IsAdminUser])
def admin_package_update(request, package_id):
    """Update or delete package — maps to admin/packages.php."""
    try:
        package = Package.objects.get(id=package_id)
    except Package.DoesNotExist:
        return Response({'success': False, 'error': 'Package not found.'}, status=status.HTTP_404_NOT_FOUND)

    if request.method == 'DELETE':
        if package.investment_set.exists() or package.pin_set.exists():
            return Response({
                'success': False,
                'error': 'Cannot delete package: active investments or generated PINs exist for this package.'
            }, status=status.HTTP_400_BAD_REQUEST)

        package.delete()
        return Response({'success': True, 'message': 'Package deleted successfully.'})

    serializer = PackageCreateUpdateSerializer(package, data=request.data)
    serializer.is_valid(raise_exception=True)
    serializer.save()
    return Response({'success': True, 'package': PackageSerializer(package).data})


# ══════════════════════════════════════════════
# PIN MANAGEMENT — maps to admin/pins.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_pins_list(request):
    """
    List PINs with status filter — maps to admin/pins.php GET.
    Supports: status=used|unused filter tabs.
    """
    status_filter = request.query_params.get('status', '')

    queryset = Pin.objects.select_related('package', 'assigned_to', 'used_by')

    if status_filter == 'used':
        queryset = queryset.filter(status='used')
    elif status_filter == 'unused':
        queryset = queryset.filter(status='unused')

    queryset = queryset.order_by('-created_at')

    return Response({
        'pins': PinListSerializer(queryset, many=True).data,
        'packages': PackageSerializer(Package.objects.all().order_by('amount'), many=True).data,
    })


@api_view(['POST'])
@permission_classes([IsAdminUser])
def admin_pin_generate(request):
    """
    Generate PINs — maps to admin/pins.php POST action=generate.
    PIN format: OPT + 6 random digits (matching PHP).
    """
    serializer = PinGenerateSerializer(data=request.data)
    serializer.is_valid(raise_exception=True)

    package_id = serializer.validated_data['package_id']
    count = serializer.validated_data['count']
    assign_username = serializer.validated_data.get('assign_username', '')

    try:
        package = Package.objects.get(id=package_id)
    except Package.DoesNotExist:
        return Response({'success': False, 'error': 'Package not found.'}, status=status.HTTP_400_BAD_REQUEST)

    assigned_to = None
    if assign_username:
        assigned_to = User.objects.filter(username=assign_username).first()

    new_pins = []
    for _ in range(count):
        # Format matching PHP: "OPT" + 6 random digits
        while True:
            pin_code = 'OPT' + str(random.randint(100000, 999999))
            if not Pin.objects.filter(pin_code=pin_code).exists():
                break

        pin = Pin.objects.create(
            pin_code=pin_code,
            package=package,
            assigned_to=assigned_to,
        )
        new_pins.append({
            'pin_code': pin.pin_code,
            'package_name': package.name,
        })

    return Response({
        'success': True,
        'generated_pins': new_pins,
        'count': len(new_pins),
    })


# ══════════════════════════════════════════════
# WITHDRAWALS — maps to admin/withdrawals.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_withdrawals_list(request):
    """
    Withdrawal list with date filter — maps to admin/withdrawals.php.
    Supports: start_date, end_date, status query params.
    """
    start_date = request.query_params.get('start_date', '')
    end_date = request.query_params.get('end_date', '')
    status_filter = request.query_params.get('status', '')

    queryset = Transaction.objects.filter(type='WITHDRAWAL').select_related('user')

    if start_date:
        queryset = queryset.filter(created_at__date__gte=start_date)
    if end_date:
        queryset = queryset.filter(created_at__date__lte=end_date)
    if status_filter and status_filter != 'all':
        queryset = queryset.filter(status=status_filter)

    queryset = queryset.order_by('-created_at')

    # Summary metrics across all withdrawal records
    total_requested = Transaction.objects.filter(type='WITHDRAWAL').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_pending = Transaction.objects.filter(type='WITHDRAWAL', status='pending').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_approved = Transaction.objects.filter(type='WITHDRAWAL', status='completed').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_rejected = Transaction.objects.filter(type='WITHDRAWAL', status='rejected').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_fees = Transaction.objects.filter(type='WITHDRAWAL', status='completed').aggregate(
        total=Coalesce(Sum('fee'), Decimal('0.00'))
    )['total']
    total_net = total_approved - total_fees

    return Response({
        'withdrawals': WithdrawalListSerializer(queryset, many=True).data,
        'summary': {
            'total_requested': str(total_requested),
            'total_pending': str(total_pending),
            'total_approved': str(total_approved),
            'total_rejected': str(total_rejected),
            'total_fees': str(total_fees),
            'total_net_disbursed': str(total_net),
        }
    })


@api_view(['POST'])
@permission_classes([IsAdminUser])
def admin_withdrawal_approve(request, transaction_id):
    """Admin approves pending withdrawal request."""
    txn = get_object_or_404(Transaction, id=transaction_id, type='WITHDRAWAL')
    if txn.status != 'pending':
        return Response({
            'success': False,
            'error': f'Withdrawal is already {txn.status}. Only pending requests can be approved.'
        }, status=status.HTTP_400_BAD_REQUEST)

    with transaction.atomic():
        txn.status = 'completed'
        txn.save(update_fields=['status'])

    return Response({
        'success': True,
        'message': f'Withdrawal #REQ-{txn.id} of ${txn.amount:,.2f} has been approved successfully.'
    })


@api_view(['POST'])
@permission_classes([IsAdminUser])
def admin_withdrawal_reject(request, transaction_id):
    """Admin rejects pending withdrawal request, refunding gross amount to user's wallet."""
    txn = get_object_or_404(Transaction, id=transaction_id, type='WITHDRAWAL')
    if txn.status != 'pending':
        return Response({
            'success': False,
            'error': f'Withdrawal is already {txn.status}. Only pending requests can be rejected.'
        }, status=status.HTTP_400_BAD_REQUEST)

    with transaction.atomic():
        txn.status = 'rejected'
        txn.save(update_fields=['status'])

        # Create auditable reversal refund transaction to restore funds
        Transaction.objects.create(
            user=txn.user,
            type='WITHDRAWAL_REFUND',
            status='completed',
            amount=txn.amount,
            fee=Decimal('0.00'),
            net_amount=txn.amount,  # Restores the gross $100.00 to member's wallet ledger!
            description=f"Refund reversal for rejected withdrawal #REQ-{txn.id} (${txn.amount:,.2f})"
        )

    return Response({
        'success': True,
        'message': f'Withdrawal #REQ-{txn.id} has been rejected. ${txn.amount:,.2f} has been refunded to {txn.user.username}\'s wallet.'
    })


# ══════════════════════════════════════════════
# BUSINESS REPORTS — maps to admin/reports.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_reports(request):
    """
    Financial & Ecosystem Health Reports — maps to admin/reports.php.
    Reproduces all 4 report sections with exact PHP queries.
    """
    # Report 1: Global Inflow vs Outflow
    total_inflow = Investment.objects.aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']

    total_roi = Transaction.objects.filter(type='ROI').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_level = Transaction.objects.filter(type='LEVEL_INCOME').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']
    total_rank = Transaction.objects.filter(type='RANK_INCOME').aggregate(
        total=Coalesce(Sum('amount'), Decimal('0.00'))
    )['total']

    total_outflow = total_roi + total_level + total_rank
    net_reserve = total_inflow - total_outflow

    # Report 2: Liability & Cap Status
    passive_data = Investment.objects.filter(status='active').aggregate(
        active_investment=Coalesce(Sum('amount'), Decimal('0.00')),
        passive_roi_paid=Coalesce(Sum('roi_earned'), Decimal('0.00')),
    )
    active_inv = passive_data['active_investment']
    max_passive = active_inv * Decimal('2.0')
    passive_paid = passive_data['passive_roi_paid']
    remaining_passive = max(Decimal('0.00'), max_passive - passive_paid)
    passive_pct = min(100.0, float(passive_paid / max_passive * 100)) if max_passive > 0 else 0.0

    total_user_inv = User.objects.filter(status='active').aggregate(
        total=Coalesce(Sum('total_investment'), Decimal('0.00'))
    )['total']
    max_global_cap = total_user_inv * Decimal('2.0')

    total_incomes = Transaction.objects.filter(
        type__in=['ROI', 'LEVEL_INCOME', 'RANK_INCOME']
    ).aggregate(total=Coalesce(Sum('amount'), Decimal('0.00')))['total']

    remaining_global = max(Decimal('0.00'), max_global_cap - total_incomes)
    global_pct = min(100.0, float(total_incomes / max_global_cap * 100)) if max_global_cap > 0 else 0.0

    # Report 3: PIN Ledger
    pin_stats = Pin.objects.values('status').annotate(
        count=Count('id'),
        total_value=Coalesce(Sum('package__amount'), Decimal('0.00'))
    )
    pin_ledger = {'unused': {'count': 0, 'value': Decimal('0.00')}, 'used': {'count': 0, 'value': Decimal('0.00')}}
    for stat in pin_stats:
        if stat['status'] in pin_ledger:
            pin_ledger[stat['status']]['count'] = stat['count']
            pin_ledger[stat['status']]['value'] = stat['total_value']

    # Report 4: Fee & Withdrawal
    w_summary = Transaction.objects.filter(type='WITHDRAWAL').aggregate(
        count=Count('id'),
        total_requested=Coalesce(Sum('amount'), Decimal('0.00')),
        total_fees=Coalesce(Sum('fee'), Decimal('0.00')),
    )

    return Response({
        'total_inflow': str(total_inflow),
        'total_roi': str(total_roi),
        'total_level': str(total_level),
        'total_rank': str(total_rank),
        'total_outflow': str(total_outflow),
        'net_reserve_health': str(net_reserve),

        'used_pins_count': pin_ledger['used']['count'],
        'used_pins_value': str(pin_ledger['used']['value']),
        'unused_pins_count': pin_ledger['unused']['count'],
        'unused_pins_value': str(pin_ledger['unused']['value']),
        'total_minted_value': str(pin_ledger['used']['value'] + pin_ledger['unused']['value']),
        'total_pins_count': pin_ledger['used']['count'] + pin_ledger['unused']['count'],

        'active_investment': str(active_inv),
        'max_passive_liability': str(max_passive),
        'passive_roi_paid': str(passive_paid),
        'remaining_passive_liability': str(remaining_passive),
        'passive_progress_percent': round(passive_pct, 1),
        'total_user_investment': str(total_user_inv),
        'max_global_id_cap': str(max_global_cap),
        'total_incomes_paid': str(total_incomes),
        'remaining_global_id_cap': str(remaining_global),
        'global_id_cap_progress': round(global_pct, 1),

        'total_requested_withdrawals': str(w_summary['total_requested']),
        'total_withdrawal_fees': str(w_summary['total_fees']),
        'net_paid_withdrawals': str(w_summary['total_requested'] - w_summary['total_fees']),
        'withdrawal_count': w_summary['count'],
    })


# ══════════════════════════════════════════════
# RECENT MATCHES — maps to admin/matches.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_recent_matches(request):
    """Recent match & rank achievements — maps to admin/matches.php."""
    matches = MatchingSchedule.objects.select_related(
        'user', 'user__sponsor', 'user__placement'
    ).order_by('-created_at', '-id')[:10]

    return Response({
        'matches': MatchingScheduleSerializer(matches, many=True).data,
    })


# ══════════════════════════════════════════════
# PLACEMENT CONFLICTS — maps to admin/placement_conflicts.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_placement_conflicts(request):
    """
    Detect placement conflicts — maps to admin/placement_conflicts.php.
    Finds users where 2+ children occupy the same position under a parent.
    """
    from django.db.models import Count as DjCount

    # Find parents with duplicate positions
    conflicts = (
        User.objects
        .filter(placement__isnull=False, position__isnull=False)
        .values('placement_id', 'position')
        .annotate(cnt=DjCount('id'))
        .filter(cnt__gt=1)
    )

    conflict_details = []
    for c in conflicts:
        parent = User.objects.filter(id=c['placement_id']).first()
        children = User.objects.filter(
            placement_id=c['placement_id'],
            position=c['position']
        ).values('id', 'username', 'mid', 'position', 'created_at')

        conflict_details.append({
            'parent_id': c['placement_id'],
            'parent_username': parent.username if parent else 'Unknown',
            'parent_mid': parent.mid if parent else 'Unknown',
            'position': c['position'],
            'count': c['cnt'],
            'children': list(children),
        })

    # Also find orphaned users (placement_id references non-existent user)
    orphans = User.objects.filter(
        placement__isnull=False
    ).exclude(
        placement_id__in=User.objects.values_list('id', flat=True)
    ).values('id', 'username', 'mid', 'placement_id')

    return Response({
        'conflicts': conflict_details,
        'orphans': list(orphans),
        'total_conflicts': len(conflict_details),
        'total_orphans': len(orphans),
    })


@api_view(['POST'])
@permission_classes([IsAdminUser])
def admin_placement_fix(request):
    """Fix placement conflicts — maps to admin/placement_conflicts.php POST actions."""
    action = request.data.get('action')

    if action == 'move_other_position':
        user_id = request.data.get('user_id')
        placement_id = request.data.get('placement_id')
        current_pos = request.data.get('current_position')
        new_pos = 'right' if current_pos == 'left' else 'left'

        with transaction.atomic():
            # Check if new position is vacant
            occupied = User.objects.filter(placement_id=placement_id, position=new_pos).exists()
            if occupied:
                return Response(
                    {'success': False, 'error': f"The '{new_pos}' position is already occupied."},
                    status=status.HTTP_400_BAD_REQUEST
                )

            User.objects.filter(id=user_id, placement_id=placement_id).update(position=new_pos)

        return Response({'success': True, 'message': f'Moved user to {new_pos} position.'})

    elif action == 'set_placement_null':
        user_id = request.data.get('user_id')
        with transaction.atomic():
            User.objects.filter(id=user_id).update(placement=None, position=None)
        return Response({'success': True, 'message': 'Placement removed.'})

    return Response({'success': False, 'error': 'Unknown action.'}, status=status.HTTP_400_BAD_REQUEST)


# ══════════════════════════════════════════════
# LEVEL INCOME AUDIT — maps to admin/check_level_income.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_level_income_audit(request):
    """
    Level income audit — maps to admin/check_level_income.php.
    Checks each investment's level income distribution against expected values.
    """
    # Business config matching PHP config.php
    level_percentages = {
        1: Decimal('5'), 2: Decimal('2'), 3: Decimal('2'),
        4: Decimal('1'), 5: Decimal('1'), 6: Decimal('1'),
        7: Decimal('0.50'), 8: Decimal('0.50'), 9: Decimal('0.50'),
        10: Decimal('0.50'), 11: Decimal('0.50'), 12: Decimal('0.50'),
    }

    # Get recent investments to audit
    investments = Investment.objects.select_related('user', 'package').order_by('-created_at')[:20]

    audit_results = []
    for inv in investments:
        # Get genealogy parents for this investor
        parents = Genealogy.objects.filter(
            user=inv.user, level__lte=12
        ).select_related('parent').order_by('level')

        level_details = []
        has_missing = False

        for parent_entry in parents:
            level = parent_entry.level
            if level not in level_percentages:
                continue

            expected = (inv.amount * level_percentages[level]) / Decimal('100')

            # Check if level income transaction exists
            actual_txn = Transaction.objects.filter(
                user=parent_entry.parent,
                related_user=inv.user,
                type='LEVEL_INCOME',
                level=level,
            ).first()

            actual_amount = actual_txn.amount if actual_txn else Decimal('0.00')
            is_missing = actual_txn is None

            if is_missing:
                has_missing = True

            level_details.append({
                'level': level,
                'parent_id': parent_entry.parent.id,
                'parent_username': parent_entry.parent.username,
                'parent_mid': parent_entry.parent.mid,
                'expected_amount': str(expected),
                'actual_amount': str(actual_amount),
                'is_missing': is_missing,
            })

        audit_results.append({
            'investment_id': inv.id,
            'user_id': inv.user.id,
            'username': inv.user.username,
            'mid': inv.user.mid,
            'amount': str(inv.amount),
            'package_name': inv.package.name,
            'created_at': inv.created_at.strftime('%Y-%m-%d %H:%M'),
            'has_missing': has_missing,
            'levels': level_details,
        })

    return Response({
        'audits': audit_results,
        'level_percentages': {str(k): str(v) for k, v in level_percentages.items()},
    })


# ══════════════════════════════════════════════
# CORRECT ROI — maps to admin/correct_roi.php
# ══════════════════════════════════════════════

@api_view(['GET'])
@permission_classes([IsAdminUser])
def admin_correct_roi(request):
    """
    ROI correction analysis — maps to admin/correct_roi.php.
    Shows investments with missing ROI payouts.
    """
    from datetime import datetime, timedelta

    daily_rate = Decimal('0.0050')  # 0.50%
    cap_multiplier = Decimal('2.0')  # 200%

    investments = Investment.objects.filter(
        status__in=['active', 'completed']
    ).select_related('user', 'package').order_by('-created_at')[:50]

    results = []
    for inv in investments:
        max_roi = inv.amount * cap_multiplier
        expected_daily = inv.amount * daily_rate

        # Count actual ROI transactions
        actual_roi_count = Transaction.objects.filter(
            investment=inv, type='ROI'
        ).count()

        actual_roi_total = Transaction.objects.filter(
            investment=inv, type='ROI'
        ).aggregate(total=Coalesce(Sum('amount'), Decimal('0.00')))['total']

        # Calculate expected days since creation
        days_since = (timezone.now() - inv.created_at).days
        expected_days = min(days_since, 400)

        missing_days = max(0, expected_days - actual_roi_count)
        missing_amount = expected_daily * missing_days

        results.append({
            'investment_id': inv.id,
            'user_id': inv.user.id,
            'username': inv.user.username,
            'mid': inv.user.mid,
            'amount': str(inv.amount),
            'package_name': inv.package.name,
            'status': inv.status,
            'created_at': inv.created_at.strftime('%Y-%m-%d %H:%M'),
            'days_since_creation': days_since,
            'expected_roi_days': expected_days,
            'actual_roi_count': actual_roi_count,
            'actual_roi_total': str(actual_roi_total),
            'max_roi': str(max_roi),
            'expected_daily_roi': str(expected_daily),
            'missing_days': missing_days,
            'missing_amount': str(missing_amount),
            'has_issues': missing_days > 0,
        })

    return Response({
        'investments': results,
        'config': {
            'daily_rate': str(daily_rate),
            'cap_multiplier': str(cap_multiplier),
            'max_days': 400,
        }
    })