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 product.models import Product

import urllib.parse
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 Meta:
    managed = False
    db_table = 'leads_productusermanualpdfsent'
class Meta:
    managed = False
    db_table = 'leads_productspecificationsent'

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'),
    )
    LEAD_STATUS_CHOICES = (
        (1 , 'New'),
        (2, 'Initial Contact Made'),
        (3,'Un-Qualified'),
        (4,'In Discussion'),
        (5,'Demo Scheduled'),
        (6,'Quoted'),
        (7,'Closed Won'),
        (8,'Closed Lost'),
    )
    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)
    demo_date = models.DateField(null=True, blank=True)
    demo_time = models.TimeField(null=True, blank=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)
    status= models.BigIntegerField(choices=LEAD_STATUS_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)
    facebook  = models.URLField(blank=True, null=True)
    instagram = models.URLField(blank=True, null=True)
    linkedin  = models.URLField(blank=True, null=True)
    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 [])
    @property
    def product_spec_objects(self):
        # Get all products for which a spec PDF has been sent to this lead
        sent_product_ids = ProductSpecificationPDFSent.objects.filter(
            lead_id=self.id
        ).values_list('product_id', flat=True)
        return sent_product_ids
        # dd(Product.objects.filter(id__in=sent_product_ids))
        return Product.objects.filter(id__in=sent_product_ids)

    @property
    def product_user_manual_objects(self):
        # Get all products for which a manual PDF has been sent to this lead
        sent_product_ids = ProductUserManualPDFSent.objects.filter(
            lead_id=self.id
        ).values_list('product_id', flat=True)
        return sent_product_ids
        return Product.objects.filter(id__in=sent_product_ids)
    @property
    def has_any_pdf_sent(self):
        return ProductSpecificationPDFSent.objects.filter(lead_id=self.id).exists() or ProductUserManualPDFSent.objects.filter(lead_id=self.id).exists()
    def undelete(self):
        self.soft_deleted = False
        self.deleted_at = None
        self.is_active = True
        self.save()
    
    @property
    def quotes_count(self):
        return self.quotes.count()
    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 all_note_history(self):
        c=Leadnote.objects.filter(lead=self).order_by('-created_at')
        return c
    
    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')
        customer_history=ActivityLog.objects.filter(model_name = 'Leadnote',new_data__0__fields__lead = self.id ).order_by('-timestamp')
        # dd(customer_history)
        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,related_name='notes', 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)
    updated_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)
    is_it_lead_demo=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 
class ProductSpecificationPDFSent(models.Model):
    product = models.ForeignKey(
        'product.Product',
        related_name='specification_pdf_sent',
        on_delete=models.CASCADE
    )
    lead = models.ForeignKey(Lead, related_name='specification_pdf_lead',on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)
    # file = models.FileField(upload_to='product_specification_pdf/')
class ProductUserManualPDFSent(models.Model):
    product = models.ForeignKey(
        'product.Product',
        related_name='user_manual_pdf_sent',
        on_delete=models.CASCADE
    )
    lead = models.ForeignKey(Lead, related_name='user_manual_pdf_lead',on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)