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
import urllib.parse
# from customer.models import Customer
class Meta:
    managed = False  # tells Django not to handle migrations for this model
    db_table = 'leads_lead'  # exact table name in your DB (adjust if different)
class Meta:
    managed = False
    db_table = 'leads_leadnote'
class Meta:
    managed = False
    db_table = 'leads_lead_quote'
class Meta:
    managed = False
    db_table = 'leads_leadattachmentfiles'
class Meta:
    managed = False
    db_table = 'leads_leadattachmentphotos'

class Lead(models.Model):
    IS_INDIVIDUAL_CHOICES = (
        (True, 'Individual'),
        (False, 'Company'),
    )
    BUSINESS_TYPE_CHOICES = (
        (1, 'Commercial'),
        (2, 'Residential'),
        (3,'Shopfitters'),
        (4,'Other'),
    )
    LEAD_SOURCE_CHOICES = (
       (1 , 'Sales agent'),
        (2, 'Facebook'),
        (3,'Instagram'),
        (4,'Linkin'),
        (5,'Website'),
        (6,'Referral'),
    )
    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,blank=True, null=True)
    city = models.CharField(max_length=255,blank=True, null=True)
    state = models.CharField(max_length=255,blank=True, null=True)
    post_code = models.CharField(max_length=6,blank=True, null=True)
    country = models.CharField(max_length=255,blank=True, null=True)
    geolocation=models.CharField(max_length=255,blank=True, null=True)
    work_phone = models.CharField(max_length=21,blank=True, null=True)
    business_type = models.BigIntegerField(choices=BUSINESS_TYPE_CHOICES,blank=True, null=True)
    lead_source= models.BigIntegerField(choices=LEAD_SOURCE_CHOICES,blank=True, null=True)
    mobile = 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,blank=True, null=True)
    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)
    assigned_to = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="assigned_leads")  # User who receives the note
    customer_id = models.CharField(max_length=11,blank=True, null=True)
    products = models.JSONField(default=list, blank=True, null=True)  # New field
    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_lead')
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='updated_lead')
    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_lead')
    soft_deleted = models.BooleanField(default=False)
    # is_primary = models.BooleanField(default=False)
    # primary_mail=models.EmailField(max_length=255)
    
    def generate_map_url(self):
        address = f"{self.street}, {self.city}, {self.state} {self.post_code}, {self.country}"
        return f"https://www.google.com/maps/search/?api=1&query={urllib.parse.quote(address)}"

    def save(self, *args, **kwargs):
        # if self.street and self.city and self.state and self.post_code and self.country:
        self.geolocation = self.generate_map_url()
        super().save(*args, **kwargs)
        
    def soft_delete(self):
        self.soft_deleted = True
        self.deleted_at = timezone.now()
        self.is_active = False
        self.save()
     
    @property
    def product_objects(self):
        from product.models import ProductCategory,Product
        return Product.objects.filter(id__in=self.products or [])
    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}"
        
      
            
    
    def note_history(self):
        # Get latest created_at for each groupid
        latest_notes = (
            Leadnote.objects.filter(
                lead=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 Leadnote.objects.filter(
            lead=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 = 'Lead', instance_id = self.id)| Q(new_data__0__fields__lead = self.id )).order_by('-timestamp')
      
        if customer_history:
            return customer_history
        else:
            return None              

class LeadAttachmentPhotos(models.Model):
    lead = models.ForeignKey(Lead, on_delete=models.CASCADE)
    attachment = models.FileField(upload_to='attachments/', blank=True, null=True)
    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_leadattachmentphotos") 
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="updated_leadattachmentphotos")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="created_leadattachmentphotos") 
    created_at = models.DateTimeField(auto_now_add=True)
    soft_deleted = models.BooleanField(default=False)
    
    def soft_delete(self):
        self.soft_deleted = True
        self.deleted_at = timezone.now()
        self.save()
    
    def is_image(self):
        # dd(self.attachment.name.lower().endswith(('.png', '.jpg', '.jpeg', '.gif')))
        return self.attachment.name.lower().endswith(('.png', '.jpg', '.jpeg', '.gif'))

    def filename(self):
        return self.attachment.name.split('/')[-1]
class LeadAttachmentFiles(models.Model):
    lead = models.ForeignKey(Lead, on_delete=models.CASCADE)
    attachment = models.FileField(upload_to='attachments/', blank=True, null=True)
    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_leadattachmentfiles") 
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="updated_leadattachmentfiles")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="created_leadattachmentfiles") 
    created_at = models.DateTimeField(auto_now_add=True)
    soft_deleted = models.BooleanField(default=False)
    
    def soft_delete(self):
        self.soft_deleted = True
        self.deleted_at = timezone.now()
        self.save()
    
    def is_image(self):
        # dd(self.attachment.name.lower().endswith(('.png', '.jpg', '.jpeg', '.gif')))
        return self.attachment.name.lower().endswith(('.png', '.jpg', '.jpeg', '.gif'))

    def filename(self):
        return self.attachment.name.split('/')[-1]

class Leadnote(models.Model):
    lead = models.ForeignKey(Lead, on_delete=models.CASCADE)
    note = models.TextField()
    groupid=models.BigIntegerField()
    reminder_date = models.DateField(null=True, blank=True)
    meeting_time = models.TimeField(null=True, blank=True)
    mail_status= models.BooleanField(default=False)
    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_leadnotes")  # User who receives the note
    meeting_invitation = models.BooleanField(default=False)
    meet_link = models.URLField(max_length=500, null=True, blank=True)
    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_leadnotes") 
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="updated_leadnotes")
    created_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name="created_leadnotes") 
    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=Leadnote.objects.filter(groupid = self.groupid,deleted_at__isnull=True).order_by('-created_at')
        return c
        
        
class Lead_Quote(models.Model):
    CREATED = 0
    OPEN=1
    CLOSE=2
    
    STATUS_CHOICES = (
        (CREATED, 'Created'),
        (OPEN, 'Mail Sent'),
        (CLOSE, 'Close'),
    )
    lead=models.ForeignKey(Lead, on_delete=models.CASCADE)
    attachment=models.FileField(upload_to='leadQuote_attachment/', null=True)
    version = models.BigIntegerField()
    mailstatus = models.PositiveSmallIntegerField(choices=STATUS_CHOICES,default=0)
    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_lead_quote')
    updated_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True, blank=True, related_name='updated_lead_quote')
    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_lead_quote')
    soft_deleted = models.BooleanField(default=False)
    
    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 f"Lead Quote {self.id} for {self.lead}"
    
   
    @property           
    def get_history(self):
        
        # bill_history1=ActivityLog.objects.filter(new_data__0__fields__supplier_bill= self.id )
        # bill_history=list(bill_history1) +  list(ActivityLog.objects.filter(model_name = 'Supplier_Bill', instance_id = self.id))
        
        bill_history=ActivityLog.objects.filter(Q(model_name = 'Lead_Quote', instance_id = self.id)| Q(new_data__0__fields__lead_quote = self.id )).order_by('-timestamp')
        
          
        if bill_history:
            return bill_history
        else:
            return None