from django.db import models
from authentication.models import User
from django.utils import timezone
from authentication.models import ActivityLog
from django.db.models import Q
from django.db.models import Subquery, OuterRef
from django.db.models import Max
from leads.models import Lead

class Customer(models.Model):
    user=models.ForeignKey(User, on_delete=models.CASCADE,null=True,related_name='user')
    myob_uid=models.TextField(null=True,blank=True)
    myob_location=models.TextField(null=True)
    current_row_version=models.TextField(null=True)
    customer = models.ForeignKey(User, on_delete=models.CASCADE,null=True )
    IS_INDIVIDUAL_CHOICES = (
        (True, 'Individual'),
        (False, 'Company'),
    )
    is_individual = models.BooleanField(choices=IS_INDIVIDUAL_CHOICES)
    company_name = models.CharField(max_length=255, blank=True, null=True)
    last_name = models.CharField(max_length=255, blank=True, null=True)
    first_name = models.CharField(max_length=255,blank=True, null=True)
    is_active = models.BooleanField(default=True)
    street = models.CharField(max_length=255)
    city = models.CharField(max_length=255)
    state = models.CharField(max_length=255)
    post_code = models.CharField(max_length=6,blank=True, null=True)
    country = models.CharField(max_length=255)
    phone1 = models.CharField(max_length=21)
    phone2 = models.CharField(max_length=21, blank=True, null=True)
    phone3 = models.CharField(max_length=21, blank=True, null=True)
    fax = models.CharField(max_length=21, blank=True, null=True)
    email = models.EmailField(max_length=255)
    website = models.URLField(max_length=255, blank=True, null=True)
    contact_name = models.CharField(max_length=25, blank=True, null=True)
    salutation = models.CharField(max_length=255, blank=True, null=True)
    abn=models.CharField(max_length=11,blank=True, null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='created_customers')
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='updated_customers')
    deleted_at = models.DateTimeField(blank=True, null=True)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_customers')
    soft_deleted = models.BooleanField(default=False)
    is_primary = models.BooleanField(default=False)
    primary_mail=models.EmailField(max_length=255,null=True, blank=True)
    lead=models.ForeignKey(Lead, on_delete=models.SET_NULL, null=True, blank=True, related_name='converted_lead')
    def total_amount(self):
        invoices = self.invoice_set.all()  
        total_amount = sum(invoice.total for invoice in invoices)
        return  "${:,.2f}".format(total_amount)
        
    def primary_email(self):
        users = self.user 
        return  users.username
    def due_amount(self):
        invoices = self.invoice_set.all()  
        total_amount = sum(invoice.total for invoice in invoices)
        total_paid_amount =  sum(invoice.total_paid_amount or 0 for invoice in invoices)
        due_amount = total_amount - total_paid_amount
        return "${:,.2f}".format(due_amount)
    
    def total_paid_amount(self):
        invoices = self.invoice_set.all()  
        total_paid_amount = sum(invoice.total_paid_amount or 0 for invoice in invoices)
        return "${:,.2f}".format(total_paid_amount)

    def soft_delete(self):
        self.soft_deleted = True
        self.deleted_at = timezone.now()
        self.is_active = False
        self.save()
     

    def undelete(self):
        self.soft_deleted = False
        self.deleted_at = None
        self.is_active = True
        self.save()
      

    def __str__(self):
        return self.company_name or f"{self.first_name} {self.last_name}"
        
    def get_status(self):
         return "Active" if self.is_active else "Inactive"
         
    def full_name(self):
        return self.company_name or f"{self.first_name}{self.last_name}"
        
    @property    
    def get_primary_email(self):
        primary_contact_info = self.contact_information.filter(is_primary=True).first()
        if primary_contact_info:
            return primary_contact_info.email
        else:
            return self.email    
    @property
    def quotes_count(self):
        return self.quotes.count()   
    @property
    # def note_history(self):
    #     # c=Customernote.objects.filter(customer = self,deleted_at__isnull=True)
    #     subquery = Customernote.objects.filter(
    #         customer=self, 
    #         deleted_at__isnull=True, 
    #         groupid=OuterRef('groupid')
    #     ).order_by('-created_at').values('id')[:1]  # Get latest note ID per groupid
        
    #     c = Customernote.objects.filter(id__in=Subquery(subquery))
    #     return c
    def note_history(self):
        # Get latest created_at for each groupid
        latest_notes = (
            Customernote.objects.filter(
                customer=self, 
                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
        return Customernote.objects.filter(
            customer=self,
            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') 
    @property           
    def get_history(self):
    
        # customer_history1=ActivityLog.objects.filter(new_data__0__fields__customer= self.id )
        # customer_history=list(customer_history1) +  list(ActivityLog.objects.filter(model_name = 'Customer', instance_id = self.id))
        
        customer_history=ActivityLog.objects.filter(Q(model_name = 'Customer', instance_id = self.id)| Q(new_data__0__fields__customer = self.id )).order_by('-timestamp')
      
        if customer_history:
            return customer_history
        else:
            return None              

    @property   
    def get_cust_info(self):
        c=Customer_Info.objects.filter(customer = self)
        return c

class Customer_Info(models.Model):
        customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='contact_information')
        company_name = models.CharField(max_length=255, blank=True, null=True)
        first_name = models.CharField(max_length=255, blank=True, null=True)
        last_name = models.CharField(max_length=255, blank=True, null=True)
        email = models.EmailField(max_length=255, blank=True, null=True)
        phone1 = models.CharField(max_length=21, blank=True, null=True)
        position = models.CharField(max_length=255, blank=True, null=True)
        is_primary = models.BooleanField(default=False)
        created_at = models.DateTimeField(auto_now_add=True)
        updated_at = models.DateTimeField(auto_now=True)
        created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='created_customer_info')
        updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='updated_customer_info')
        deleted_at = models.DateTimeField(blank=True, null=True)
        deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='deleted_customer_info')
        soft_deleted = models.BooleanField(default=False)
        user=models.ForeignKey(User, on_delete=models.CASCADE,null=True,related_name='user_info')

        def __str__(self):
            return self.company_name or f"{self.first_name} {self.last_name}"
          
          
        def soft_delete(self):
            self.soft_deleted = True
            self.deleted_at = timezone.now()
            self.is_active = False
            self.save()
     

        def undelete(self):
            self.soft_deleted = False
            self.deleted_at = None
            self.is_active = True
            self.save()

class Customernote(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    note = models.TextField()
    groupid=models.BigIntegerField()
    reminder_date = models.DateField(null=True, blank=True)
    is_read = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)
    assigned_to = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="assigned_notes")  # User who receives the note
    deleted_at = models.DateTimeField(blank=True, null=True)
    deleted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="deleted_notes") 
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="updated_notes")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="created_notes") 
    soft_deleted = models.BooleanField(default=False)
    
    def soft_delete(self):
        self.soft_deleted = True
        self.deleted_at = timezone.now()
        self.save()
    
    @property
    def note_history(self):
        c=Customernote.objects.filter(groupid = self.groupid,deleted_at__isnull=True).order_by('-created_at')
        return c