from django.contrib.auth.models import User
from quotation.models import Order_Item,Technician_Assigned_Order_Item
from django.db.models import Count
from product.models import SerialNumber,Product
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
from datetime import date, timedelta

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 
        # users = User.objects.filter( deleted_at__isnull=True,  is_superuser=0,role__id=2  ).order_by('first_name')
        user_roles = user.role.all().values_list('id', flat=True) 
        if 1 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True,is_read=0, reminder_date__in=[today, None]).count()
        elif 2 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True,is_read=0, 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 TotalCount(request):
    # today = localdate()
    today = date.today()
    tomorrow = today + timedelta(days=1)
    day_after_tomorrow = today + timedelta(days=2)
    if request.user.is_authenticated:
        user = request.user 
        # users = User.objects.filter( deleted_at__isnull=True,  is_superuser=0,role__id=2  ).order_by('first_name')
        user_roles = user.role.all().values_list('id', flat=True) 
        if 1 in user_roles:
            customer_records =  Customernote.objects.filter(deleted_at__isnull=True,is_read=0, reminder_date__in=[today, None]).count()
            latest_notes = (
            Leadnote.objects.filter(
                    is_read=0,
                    deleted_at__isnull=True
                )
                .values('groupid')
                .annotate(lat_created_at=Max('created_at'))
            )
            leadcustomer_records = (
                Leadnote.objects.filter(
                    is_read=0,
                    deleted_at__isnull=True,
                )
                .filter(
                    Q(is_it_lead_demo=False, reminder_date__lte=today) |
                    Q(is_it_lead_demo=True, reminder_date__range=[today, day_after_tomorrow])
                )
                .filter(
                    Q(groupid__in=[note['groupid'] for note in latest_notes]),
                    Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                )
                .count()
            )
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True,reminder_date__isnull=False,is_read=0).filter(Q(is_it_lead_demo=False,reminder_date__lte=today) | Q(is_it_lead_demo=True,reminder_date__range=[today, day_after_tomorrow])).values('groupid').distinct().count()
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, reminder_date__isnull=False, reminder_date__lte=today, is_read=0).values('groupid').distinct().count()
#             .filter(
#     Q(reminder_date=today) | Q(reminder_date__isnull=True)
# )
        elif 2 in user_roles:
            customer_records = Customernote.objects.filter(deleted_at__isnull=True,is_read=0, assigned_to__id=user.id, reminder_date__in=[today, None]).count()
            latest_notes = ( Leadnote.objects.filter(  assigned_to__id=user.id,  deleted_at__isnull=True ).values('groupid').annotate(lat_created_at=Max('created_at')))
            leadcustomer_records = (
                Leadnote.objects.filter(
                    is_read=0,
                    assigned_to__id=user.id,
                    deleted_at__isnull=True,
                )
                .filter(
                    Q(is_it_lead_demo=False, reminder_date__lte=today) |
                    Q(is_it_lead_demo=True, reminder_date__range=[today, day_after_tomorrow])
                )
                .filter(
                    Q(groupid__in=[note['groupid'] for note in latest_notes]),
                    Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                )
                .count()
            )
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True,assigned_to__id=user.id,reminder_date__isnull=False,is_read=0).filter(Q(is_it_lead_demo=False,reminder_date__lte=today) | Q(is_it_lead_demo=True,reminder_date__range=[today, day_after_tomorrow])).values('groupid').distinct().count()
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, reminder_date__isnull=False, reminder_date__lte=today, is_read=0).values('groupid').distinct().count()
#             .filter(
#     Q(reminder_date=today) | Q(reminder_date__isnull=True)
# )
        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'))
    trade_count = Product.objects.filter(is_product=1,is_converted_from_trade=False,deleted_at__isnull=True).count()
    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+trade_count
    return {'notificationtotalcount': notificationtotalcount }

def TotalCountSales(request):
    # today = localdate()  
    today = date.today()
    tomorrow = today + timedelta(days=1)
    day_after_tomorrow = today + timedelta(days=2)
    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:
            latest_notes = (
            Leadnote.objects.filter(
                    is_read=0,
                    deleted_at__isnull=True
                )
                .values('groupid')
                .annotate(lat_created_at=Max('created_at'))
            )
            leadcustomer_records = (
                Leadnote.objects.filter(
                    is_read=0,
                    deleted_at__isnull=True,
                )
                .filter(
                    Q(is_it_lead_demo=False, reminder_date__lte=today) |
                    Q(is_it_lead_demo=True, reminder_date__range=[today, day_after_tomorrow])
                )
                .filter(
                    Q(groupid__in=[note['groupid'] for note in latest_notes]),
                    Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                )
                .count()
            )
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, reminder_date__isnull=False, reminder_date__lte=today, is_read=0).values('groupid').distinct().count()
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True,reminder_date__isnull=False,is_read=0).filter(Q(is_it_lead_demo=False,reminder_date__lte=today) | Q(is_it_lead_demo=True,reminder_date__range=[today, day_after_tomorrow])).values('groupid').distinct().count()
#             .filter(
#     Q(reminder_date=today) | Q(reminder_date__isnull=True)
# )
        elif 2 in user_roles:
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, reminder_date__isnull=False, reminder_date__lte=today, is_read=0).values('groupid').distinct().count()
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True,assigned_to__id=user.id,reminder_date__isnull=False,is_read=0).filter(Q(is_it_lead_demo=False,reminder_date__lte=today,is_read=0) | Q(is_it_lead_demo=True,reminder_date__range=[today, day_after_tomorrow],is_read=0)).values('groupid').distinct().count()
            # .filter(
#     Q(reminder_date=today) | Q(reminder_date__isnull=True)
# )
            # latest_notes = ( Leadnote.objects.filter(  assigned_to__id=user.id,  deleted_at__isnull=True ).values('groupid').annotate(lat_created_at=Max('created_at')))
 
            # leadcustomer_records = ( Leadnote.objects.filter(is_read=0,assigned_to__id=user.id,deleted_at__isnull=True,).filter(
            #         Q( is_it_lead_demo=False,  reminder_date__lte=today ) | Q( is_it_lead_demo=True, reminder_date__range=[today, day_after_tomorrow] )
            #     )
            #     .filter(
            #         Q(groupid__in=[note['groupid'] for note in latest_notes]),
            #         Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
            #     ).count()
            latest_notes = ( Leadnote.objects.filter(  assigned_to__id=user.id,  deleted_at__isnull=True ).values('groupid').annotate(lat_created_at=Max('created_at')))
            leadcustomer_records = (
                Leadnote.objects.filter(
                    is_read=0,
                    assigned_to__id=user.id,
                    deleted_at__isnull=True,
                )
                .filter(
                    Q(is_it_lead_demo=False, reminder_date__lte=today) |
                    Q(is_it_lead_demo=True, reminder_date__range=[today, day_after_tomorrow])
                )
                .filter(
                    Q(groupid__in=[note['groupid'] for note in latest_notes]),
                    Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                )
                .count()
            )

        else:
            leadcustomer_records = 0
        # customer_records = Customernote.objects.filter(deleted_at__isnull=True).count()
    else:
        leadcustomer_records=0
    # dd(leadcustomer_records,customer_records)
    notificationtotalcountsales=leadcustomer_records+customer_records
    return {'notificationtotalcountsales': notificationtotalcountsales }

def leadnote_customer_count(request):
    # dd(request.user)
    # today = localdate() 
    today = date.today()
    tomorrow = today + timedelta(days=1)
    day_after_tomorrow = today + timedelta(days=2)
    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__isnull=False, reminder_date__lte=today, is_read=0).values('groupid').distinct().count()
            latest_notes = (
            Leadnote.objects.filter(
                    is_read=0,
                    deleted_at__isnull=True
                )
                .values('groupid')
                .annotate(lat_created_at=Max('created_at'))
            )
            leadcustomer_records = (
                Leadnote.objects.filter(
                    is_read=0,
                    deleted_at__isnull=True,
                )
                .filter(
                    Q(is_it_lead_demo=False, reminder_date__lte=today) |
                    Q(is_it_lead_demo=True, reminder_date__range=[today, day_after_tomorrow])
                )
                .filter(
                    Q(groupid__in=[note['groupid'] for note in latest_notes]),
                    Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                )
                .count()
            )
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True,reminder_date__isnull=False,is_read=0).filter(Q(is_it_lead_demo=False,reminder_date__lte=today) | Q(is_it_lead_demo=True,reminder_date__range=[today, day_after_tomorrow])).values('groupid').distinct().count()
#             .filter(
#     Q(reminder_date=today) | Q(reminder_date__isnull=True)
# )
            
        elif 2 in user_roles:
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, reminder_date__isnull=False, reminder_date__lte=today, is_read=0).values('groupid').distinct().count()
            # leadcustomer_records = Leadnote.objects.filter(deleted_at__isnull=True, assigned_to__id=user.id, reminder_date__isnull=False,is_read=0).filter(Q(is_it_lead_demo=False,reminder_date__lte=today) | Q(is_it_lead_demo=True,reminder_date__range=[today, day_after_tomorrow])).values('groupid').distinct().count()
            # .filter(
            #     Q(reminder_date=today) | Q(reminder_date__isnull=True)
            # )
            latest_notes = ( Leadnote.objects.filter(  assigned_to__id=user.id,  deleted_at__isnull=True ).values('groupid').annotate(lat_created_at=Max('created_at')))
            leadcustomer_records = (
                Leadnote.objects.filter(
                    is_read=0,
                    assigned_to__id=user.id,
                    deleted_at__isnull=True,
                )
                .filter(
                    Q(is_it_lead_demo=False, reminder_date__lte=today) |
                    Q(is_it_lead_demo=True, reminder_date__range=[today, day_after_tomorrow])
                )
                .filter(
                    Q(groupid__in=[note['groupid'] for note in latest_notes]),
                    Q(created_at__in=[note['lat_created_at'] for note in latest_notes])
                )
                .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 trade_product_count(request):
    count = Product.objects.filter(
        is_product=1,
        is_converted_from_trade=False,
        deleted_at__isnull=True
    ).count()
    return {'trade_product_count': count}
    
def service_request_count(request):
    count = Technician_Assigned_Order_Item.objects.filter(
        is_renewal_active=True
    ).filter(
        Q(is_5_month_reminder_sent=True) |
        Q(is_11_month_reminder_sent=True)
    ).count()
    return {'service_request_count': count}