from django.contrib.auth.models import User
from quotation.models import Order_Item
from django.db.models import Count
from product.models import SerialNumber
from service.models import Parts_Request
from datetime import datetime
from customer.models import Customer,Customer_Info ,Customernote
from authentication.models import User,Role
from django.utils.timezone import localdate
from leads.models import Lead,Leadnote
from django.db.models import Q
from django.db.models import Max,Min
def active_arrival_count(request):
    s=SerialNumber.objects.filter(Arrival_ETA_date=datetime.today(),arrived_date__isnull = True,bill__isnull = False).count()
    return {'active_arrival_count': s }
def active_arrival_list(request):
    s=SerialNumber.objects.filter(Arrival_ETA_date=datetime.today(),arrived_date__isnull = True,bill__isnull = False)
    return {'active_arrival_list': s }  
def product_list_to_bill_count(request):
    order_items= Order_Item.objects.filter(serial_number__isnull=True,is_trade=0).values('product__id','product__product_name','product__sku').annotate(qty=Count('product__id'))
    order_item=[]
    for or1 in order_items:
        sr=SerialNumber.objects.filter(product__id=or1['product__id'],customer=None).count()
        if or1['qty']-sr == 0:
            pass
        else:
            or1['reuiredbilledqty']=or1['qty']-sr
            order_item.append(or1)
    if len(order_item) > 0:
        return {'unread_count': len(order_item)}
    else:
        return {'unread_count': 0 }
        
def order_item_serial_number_unallocate_list(request):
    order_items=Order_Item.objects.filter(serial_number__isnull=True,is_trade=0)
  
    total_records = order_items.count()
    if total_records > 0:
        return {'unallocate_count': total_records }
    else:
        return {'unallocate_count': 0 }

def part_requested_list(request):
    technician_part_needed_job_lists =  Parts_Request.objects.filter(technician_assigned_job__job_status=2)
    total_records = technician_part_needed_job_lists.count()
   
    if total_records > 0:
        return {'part_requested_count': total_records }
    else:
        return {'part_requested_count': 0 }

def note_customer_count(request):
    # dd(request.user)
    today = localdate()  
    if request.user.is_authenticated:
        user = request.user 
        excluded_role_ids = [1,4,5]  #
        users = User.objects.filter(deleted_at__isnull=True, is_superuser=0).exclude(role__id__in=excluded_role_ids)
        user_roles = user.role.all().values_list('id', flat=True) 
        if 1 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True, reminder_date__in=[today, None]).count()
        elif 3 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, reminder_date__in=[today, None]).count()
        else:
            customer_records = 0
        # customer_records = Customernote.objects.filter(deleted_at__isnull=True).count()
    else:
        customer_records=0
    if customer_records > 0:
        return {'customer_records_count': customer_records }
    else:
        return {'customer_records_count': 0 }
def leadnote_customer_count(request):
    # dd(request.user)
    today = localdate()  
    if request.user.is_authenticated:
        user = request.user 
        excluded_role_ids = [1,4,5]  #
        users = User.objects.filter(deleted_at__isnull=True, is_superuser=0).exclude(role__id__in=excluded_role_ids)
        user_roles = user.role.all().values_list('id', flat=True) 
        if 1 in user_roles:
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True,
            # # reminder_date__in=[today, None]
            # is_read=0,
            # reminder_date__isnull=False,
            # reminder_date__lte=today
            # ).count()
            latest_notes = (
                Leadnote.objects.filter(
                    #reminder_date__in=[today, None],
                    reminder_date__isnull=False,
                    reminder_date__lte=today,
                    is_read=0,
                    deleted_at__isnull=True
                )
                .values('groupid')  # Group by groupid
                .annotate(lat_created_at=Max('created_at'))  # Get latest created_at per group
                )
            
            # Fetch actual notes matching the latest created_at per groupid
            leadcustomer_records =  Leadnote.objects.filter(
                # reminder_date__in=[today, None],
                reminder_date__isnull=False,
                reminder_date__lte=today,
                is_read=0,
                deleted_at__isnull=True
                ).filter(
                Q(groupid__in=[note['groupid'] for note in latest_notes]),
                Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                ).order_by('-created_at').count()
        elif 2 in user_roles:
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, 
            # # reminder_date__in=[today, None]
            # is_read=0,
            # reminder_date__isnull=False,
            # reminder_date__lte=today
            # ).count()
            latest_notes = (
                Leadnote.objects.filter(
                    # reminder_date__in=[today, None],
                    reminder_date__isnull=False,
                    reminder_date__lte=today,
                    is_read=0,
                    assigned_to__id=user.id,
                    deleted_at__isnull=True
                )
                .values('groupid')  # Group by groupid
                .annotate(lat_created_at=Max('created_at'))  # Get latest created_at per group
            )

        # Fetch actual notes matching the latest created_at per groupid
            leadcustomer_records =  Leadnote.objects.filter(
                # reminder_date__in=[today, None],
                reminder_date__isnull=False,
                reminder_date__lte=today,
                is_read=0,
                assigned_to__id=user.id,
                deleted_at__isnull=True
            ).filter(
                Q(groupid__in=[note['groupid'] for note in latest_notes]),
                Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
            ).order_by('-created_at').count()
        else:
            leadcustomer_records = 0
        # customer_records = Customernote.objects.filter(deleted_at__isnull=True).count()
    else:
        leadcustomer_records=0
    if leadcustomer_records > 0:
        return {'leadcustomer_records_count': leadcustomer_records }
    else:
        return {'leadcustomer_records_count': 0 }
def TotalCountSales(request):
    today = localdate()  
    if request.user.is_authenticated:
        user = request.user 
        excluded_role_ids = [1,4,5]  #
        users = User.objects.filter(deleted_at__isnull=True, is_superuser=0).exclude(role__id__in=excluded_role_ids)
        user_roles = user.role.all().values_list('id', flat=True) 
        if 1 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True, reminder_date__in=[today, None]).count()
        elif 3 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, reminder_date__in=[today, None]).count()
        else:
            customer_records = 0
        # customer_records = Customernote.objects.filter(deleted_at__isnull=True).count()
    else:
        customer_records=0
    if request.user.is_authenticated:
        user = request.user 
        excluded_role_ids = [1,4,5]  #
        users = User.objects.filter(deleted_at__isnull=True, is_superuser=0).exclude(role__id__in=excluded_role_ids)
        user_roles = user.role.all().values_list('id', flat=True) 
        if 1 in user_roles:
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True,
            # # reminder_date__in=[today, None]
            # is_read=0,
            # reminder_date__isnull=False,
            # reminder_date__lte=today
            # ).count()
            latest_notes = (
                Leadnote.objects.filter(
                    #reminder_date__in=[today, None],
                    reminder_date__isnull=False,
                    reminder_date__lte=today,
                    is_read=0,
                    deleted_at__isnull=True
                )
                .values('groupid')  # Group by groupid
                .annotate(lat_created_at=Max('created_at'))  # Get latest created_at per group
                )
            
            # Fetch actual notes matching the latest created_at per groupid
            leadcustomer_records =  Leadnote.objects.filter(
                # reminder_date__in=[today, None],
                reminder_date__isnull=False,
                reminder_date__lte=today,
                is_read=0,
                deleted_at__isnull=True
                ).filter(
                Q(groupid__in=[note['groupid'] for note in latest_notes]),
                Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                ).order_by('-created_at').count()
        elif 2 in user_roles:
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, 
            # # reminder_date__in=[today, None]
            # is_read=0,
            # reminder_date__isnull=False,
            # reminder_date__lte=today
            # ).count()
            latest_notes = (
                Leadnote.objects.filter(
                    # reminder_date__in=[today, None],
                    reminder_date__isnull=False,
                    reminder_date__lte=today,
                    is_read=0,
                    assigned_to__id=user.id,
                    deleted_at__isnull=True
                )
                .values('groupid')  # Group by groupid
                .annotate(lat_created_at=Max('created_at'))  # Get latest created_at per group
            )

        # Fetch actual notes matching the latest created_at per groupid
            leadcustomer_records =  Leadnote.objects.filter(
                # reminder_date__in=[today, None],
                reminder_date__isnull=False,
                reminder_date__lte=today,
                is_read=0,
                assigned_to__id=user.id,
                deleted_at__isnull=True
            ).filter(
                Q(groupid__in=[note['groupid'] for note in latest_notes]),
                Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
            ).order_by('-created_at').count()
        else:
            leadcustomer_records = 0
        # customer_records = Customernote.objects.filter(deleted_at__isnull=True).count()
    else:
        leadcustomer_records=0
    notificationtotalcountsales=leadcustomer_records+customer_records
    return {'notificationtotalcountsales': notificationtotalcountsales }
def TotalCount(request):
    today = localdate()
    if request.user.is_authenticated:
        user = request.user 
        excluded_role_ids = [1,4,5]  #
        users = User.objects.filter(deleted_at__isnull=True, is_superuser=0).exclude(role__id__in=excluded_role_ids)
        user_roles = user.role.all().values_list('id', flat=True) 
        if 1 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True, reminder_date__in=[today, None]).count()
            latest_notes = (
                Leadnote.objects.filter(
                    #reminder_date__in=[today, None],
                    reminder_date__isnull=False,
                    reminder_date__lte=today,
                    is_read=0,
                    deleted_at__isnull=True
                )
                .values('groupid')  # Group by groupid
                .annotate(lat_created_at=Max('created_at'))  # Get latest created_at per group
                )
            
            # Fetch actual notes matching the latest created_at per groupid
            leadcustomer_records =  Leadnote.objects.filter(
                # reminder_date__in=[today, None],
                reminder_date__isnull=False,
                reminder_date__lte=today,
                is_read=0,
                deleted_at__isnull=True
                ).filter(
                Q(groupid__in=[note['groupid'] for note in latest_notes]),
                Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                ).order_by('-created_at').count()
        elif 2 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, reminder_date__in=[today, None]).count()
            latest_notes = (
                Leadnote.objects.filter(
                    # reminder_date__in=[today, None],
                    reminder_date__isnull=False,
                    reminder_date__lte=today,
                    is_read=0,
                    assigned_to__id=user.id,
                    deleted_at__isnull=True
                )
                .values('groupid')  # Group by groupid
                .annotate(lat_created_at=Max('created_at'))  # Get latest created_at per group
            )

        # Fetch actual notes matching the latest created_at per groupid
            leadcustomer_records =  Leadnote.objects.filter(
                # reminder_date__in=[today, None],
                reminder_date__isnull=False,
                reminder_date__lte=today,
                is_read=0,
                assigned_to__id=user.id,
                deleted_at__isnull=True
            ).filter(
                Q(groupid__in=[note['groupid'] for note in latest_notes]),
                Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
            ).order_by('-created_at').count()
        else:
            customer_records = 0
            leadcustomer_records = 0
    else:
        customer_records = 0
        leadcustomer_records = 0
    technician_part_needed_job_lists =  Parts_Request.objects.filter(technician_assigned_job__job_status=2).count()
    order_itemsunread=Order_Item.objects.filter(serial_number__isnull=True,is_trade=0).count()
    order_items= Order_Item.objects.filter(serial_number__isnull=True,is_trade=0).values('product__id','product__product_name','product__sku').annotate(qty=Count('product__id'))
    order_item=[]
    for or1 in order_items:
        sr=SerialNumber.objects.filter(product__id=or1['product__id'],customer=None).count()
        if or1['qty']-sr == 0:
            pass
        else:
            or1['reuiredbilledqty']=or1['qty']-sr
            order_item.append(or1)
    notificationtotalcount=len(order_item)+order_itemsunread+technician_part_needed_job_lists+customer_records+leadcustomer_records
    return {'notificationtotalcount': notificationtotalcount }