from django.db import models, transaction
from admin_users.models import ImplementerDetails
from django.contrib.auth.models import User
from django.utils import timezone
import re
from pgvector.django import VectorField
import uuid
import os

def generate_public_id():
    return "TEMP_PUBLIC_ID"

def format_code(text: str, length: int) -> str:
    if not text:
        return "X" * length

    # Clean input
    clean = re.sub(r"[^A-Za-z ]", "", text).strip().upper()
    words = clean.split()

    # Step 1: take first letter of each word
    result = "".join(word[0] for word in words)

    # Step 2: if short, take remaining letters from LAST word
    if len(result) < length and words:
        last_word = words[-1]
        needed = length - len(result)

        # Skip first letter of last word (already used)
        extra = last_word[1:1 + needed]
        result += extra

    # Step 3: pad with X if still short
    return (result + ("X" * length))[:length]

def l0_video_upload_path(instance, filename):
    return os.path.join("l0_interview_videos", str(instance.matched_profile.id), filename)

class TblImpDepartment(models.Model):
    imp_department_id = models.AutoField(primary_key=True)
    implementer = models.ForeignKey(ImplementerDetails, models.DO_NOTHING, related_name="implementer", blank=True, null=True)
    imp_department_name = models.CharField(max_length=255)
    imp_dep_contact_name = models.CharField(max_length=100, blank=True, null=True)
    imp_dep_contact_email = models.CharField(max_length=255)
    imp_dep_country_code = models.CharField(max_length=5)
    imp_dep_contact_number = models.CharField(max_length=255)
    address1 = models.CharField(max_length=255, blank=True, null=True)
    address2 = models.CharField(max_length=255, blank=True, null=True)
    state = models.CharField(max_length=255, blank=True, null=True)
    pincode = models.CharField(max_length=255, blank=True, null=True)
    status = models.CharField(
        max_length=50,
        choices=[
            ("Active", "Active"),
            ("Inactive", "Inactive"),
        ],
        default="Active"
    )
    created_date = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    created_by = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    updated_by = models.CharField(max_length=100, blank=True, null=True)
    updated_date = models.DateTimeField(auto_now=True, null=True)

    class Meta:
        db_table = "tbl_impdepartment"
        ordering = ['-imp_department_id']
class TblClient(models.Model):
    client_id = models.AutoField(primary_key=True)
    imp_department = models.ForeignKey(
        TblImpDepartment,
        on_delete=models.DO_NOTHING,
        related_name="clients",
        blank=True,
        null=True
    )
    client_name = models.CharField(max_length=255)
    client_type = models.CharField(
        max_length=100,
        blank=True,
        null=True,
        default="End"
    )
    client_contact_name = models.CharField(max_length=255)
    client_contact_email = models.CharField(max_length=255)
    address1 = models.CharField(max_length=255, blank=True, null=True)
    address2 = models.CharField(max_length=255, blank=True, null=True)
    address2 = models.CharField(max_length=255, blank=True, null=True)
    state = models.CharField(max_length=255, blank=True, null=True)
    pincode = models.CharField(max_length=255, blank=True, null=True)
    client_country_code = models.CharField(max_length=5)
    client_contact_number = models.CharField(max_length=100, blank=True, null=True)
    status = models.CharField(
        max_length=50,
        choices=[
            ("Active", "Active"),
            ("Inactive", "Inactive"),
        ],
        default="Active"
    )
    created_date = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    created_by = models.CharField(max_length=100, blank=True, null=True)
    updated_by = models.CharField(max_length=100, blank=True, null=True)
    updated_date = models.DateTimeField(auto_now=True, null=True)
    client_logo = models.ImageField(upload_to='images/client/logo/', blank=True, null=True)
    website_url = models.CharField(max_length=200, blank=True, null=True)
    client_jd_serial_number = models.CharField(max_length=200, default=1)

    class Meta:
        db_table = "tbl_client"
        ordering = ['-client_id']

class TblClientDocument(models.Model):
    client = models.ForeignKey(
        TblClient,
        on_delete=models.CASCADE,
        related_name="documents"
    )

    document = models.FileField(
        upload_to="documents/client/",
        blank=False,
        null=False
    )

    uploaded_date = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "tbl_client_documents"


class TblDepartment(models.Model):
    department_id = models.AutoField(primary_key=True)
    client = models.ForeignKey(TblClient, on_delete=models.CASCADE, related_name="client", blank=True, null=True)
    department_name = models.CharField(max_length=255)
    dep_contact_name = models.CharField(max_length=100, blank=True, null=True)
    dep_contact_email = models.CharField(max_length=255)
    dep_country_code = models.CharField(max_length=5)
    dep_contact_number = models.CharField(max_length=255)
    address1 = models.CharField(max_length=255, blank=True, null=True)
    address2 = models.CharField(max_length=255, blank=True, null=True)
    address2 = models.CharField(max_length=255, blank=True, null=True)
    state = models.CharField(max_length=255, blank=True, null=True)
    pincode = models.CharField(max_length=255, blank=True, null=True)
    status = models.CharField(
        max_length=50,
        choices=[
            ("Active", "Active"),
            ("Inactive", "Inactive"),
        ],
        default="Active"
    )
    created_date = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    created_by = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    updated_by = models.CharField(max_length=100, blank=True, null=True)
    updated_date = models.DateTimeField(auto_now=True, null=True)

    class Meta:
        db_table = "tbl_department"
        ordering = ['-department_id']

class TblVendor(models.Model):
    vendor_id = models.AutoField(primary_key=True)
    vendor_name = models.CharField(max_length=255)
    vendor_type = models.CharField(max_length=100, blank=True, null=True)
    vendor_contact_name = models.CharField(max_length=255)
    vendor_contact_email = models.CharField(max_length=255)
    address1 = models.CharField(max_length=255, blank=True, null=True)
    address2 = models.CharField(max_length=255, blank=True, null=True)
    address2 = models.CharField(max_length=255, blank=True, null=True)
    state = models.CharField(max_length=255, blank=True, null=True)
    pincode = models.CharField(max_length=255, blank=True, null=True)
    vendor_country_code = models.CharField(max_length=5)
    vendor_contact_number = models.CharField(max_length=100, blank=True, null=True)
    status = models.CharField(
        max_length=50,
        choices=[
            ("Active", "Active"),
            ("Inactive", "Inactive"),
        ],
        default="Active"
    )
    created_date = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    created_by = models.CharField(max_length=100, blank=True, null=True)
    updated_by = models.CharField(max_length=100, blank=True, null=True)
    updated_date = models.DateTimeField(auto_now=True, null=True)
    vendor_logo = models.ImageField(upload_to='images/vendor/logo/', blank=True, null=True)
    website_url = models.URLField(max_length=200, blank=True, null=True)

    class Meta:
        db_table = "tbl_vendor"
        ordering = ['-vendor_id']

class TblVendorDocument(models.Model):
    vendor = models.ForeignKey(
        TblVendor,
        on_delete=models.CASCADE,
        related_name="documents"
    )

    document = models.FileField(
        upload_to="documents/vendor/",
        blank=False,
        null=False
    )

    uploaded_date = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "tbl_vendor_documents"

# class TblJobType(models.Model):
#     job_type_id = models.CharField(max_length=100, primary_key=True, blank=False, null=False)
#     job_type_detail = models.CharField(max_length=255)
#     created_by = models.CharField(max_length=100, blank=True, null=True)
#     created_dt = models.DateTimeField(auto_now_add=True, blank=True, null=True)
#     updated_by = models.CharField(max_length=100, blank=True, null=True)
#     updated_dt = models.DateTimeField(auto_now=True, null=True)

#     class Meta:
#         db_table = "tbl_job_type"

class TblJobDescription(models.Model):
    jd_id = models.AutoField(primary_key=True)
    jd_display_id = models.CharField(max_length=50, unique=True)
    client_id = models.ForeignKey(TblClient, on_delete=models.CASCADE, related_name="jd_client", blank=True, null=True)
    department_id = models.ForeignKey(TblDepartment, on_delete=models.CASCADE, related_name="jd_department", blank=True, null=True)
    implementor_id = models.ForeignKey(ImplementerDetails, models.DO_NOTHING, related_name="jd_implementor", blank=True, null=True)
    client_job_id = models.CharField(max_length=200, blank=True, null=True)
    jd_date = models.DateField(blank=True, null=True)
    job_title = models.CharField(max_length=500)
    ai_title = models.CharField(max_length=500, blank=True, null=True)
    job_type = models.CharField(max_length=200, blank=True, null=True)
    years_of_experience = models.CharField(max_length=200)
    duration = models.CharField(max_length=200, blank=True, null=True)
    about_company = models.TextField(blank=True, null=True)
    job_summary = models.TextField()
    responsibilities = models.TextField()
    domain_requirements = models.TextField(blank=True, null=True)
    certification_requirements = models.TextField(blank=True, null=True)
    security_clearance_requirements = models.TextField(blank=True, null=True)
    onsite_job = models.CharField(max_length=500, blank=True, null=True)
    job_location = models.CharField(max_length=500, blank=True, null=True)
    required_qualifications = models.TextField(blank=True, null=True)
    preferred_qualifications = models.TextField(blank=True, null=True)
    working_hours = models.TextField(blank=True, null=True)
    benefits = models.TextField(blank=True, null=True)
    jd_source = models.CharField(max_length=500, blank=True, null=True)
    jd_source_txt = models.CharField(max_length=500, blank=True, null=True)
    search_pattern = models.JSONField(default=list, blank=True, null=True)
    requirement_priority = models.CharField(max_length=500, blank=True, null=True)
    salary_range = models.CharField(max_length=500, blank=True, null=True)
    no_of_open_positions = models.CharField(max_length=500, default="1", blank=True, null=True)
    apply_token_url = models.UUIDField(default=uuid.uuid4, unique=True, blank=True, null=True, editable=False)
    status = models.CharField(max_length=100, blank=True, null=True)
    jd_stage = models.CharField(max_length=100, default="Created")
    has_error = models.BooleanField(default=False)
    error_desc = models.TextField(blank=True, null=True)
    changes = models.TextField(blank=True, null=True)
    last_profile_search_time = models.DateTimeField(null=True, blank=True)
    duplicated_jd_id = models.JSONField(default=list, blank=True, null=True)
    jd_responsibilities_embedding = VectorField(dimensions=768, null=True)
    ai_title_similarity = models.CharField(max_length=100, blank=True, null=True)
    public_token = models.UUIDField(default=uuid.uuid4, editable=False, unique=True)
    jd_status_reason = models.TextField(null=True, blank=True)
    jd_status_reason_type = models.CharField(
        max_length=20,
        choices=[("On Hold","On Hold"),("Cancelled","Cancelled")],
        null=True,
        blank=True
    )
    created_by = models.CharField(max_length=100, blank=True, null=True)
    created_dt = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    updated_by = models.CharField(max_length=100, blank=True, null=True)
    updated_dt = models.DateTimeField(auto_now=True, null=True)

    class Meta:
        db_table = "tbl_job_description"
        ordering = ['-jd_id']

    def save(self, *args, **kwargs):
        if not self.jd_display_id:
            with transaction.atomic():
                date_str = timezone.now().strftime("%y%m%d")

                if self.client_id:
                    client = (
                        TblClient.objects
                        .select_for_update()
                        .get(pk=self.client_id.pk)
                    )
                    client_code = str(client.client_id).zfill(3)
                    job_code = format_code(self.job_title, 4)
                    current_serial = int(client.client_jd_serial_number or 0)
                    serial_str = str(current_serial).zfill(3)

                    self.jd_display_id = f"{client_code}_{job_code}_{date_str}_{serial_str}"

                    client.client_jd_serial_number = current_serial + 1
                    client.save(update_fields=["client_jd_serial_number"])
                else:
                    implemntor_data = (
                        ImplementerDetails.objects.get(pk=self.implementor_id.pk)
                    )

                    implementor_code = format_code(implemntor_data.display_name, 3)
                    job_code = format_code(self.job_title, 4)
                    
                    current_serial = int(implemntor_data.implementor_jd_serial_number or 0)
                    serial_str = str(current_serial).zfill(3)

                    self.jd_display_id = f"{implementor_code}_{job_code}_{date_str}_{serial_str}"

                    implemntor_data.implementor_jd_serial_number = current_serial + 1
                    implemntor_data.save(update_fields=["implementor_jd_serial_number"])

        super().save(*args, **kwargs)

class UserClient(models.Model):
    user = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="client_mappings"
    )

    client = models.ForeignKey(
        TblClient,
        on_delete=models.CASCADE,
        related_name="user_mappings"
    )

    class Meta:
        db_table = "user_client"
        unique_together = ("user", "client")

class AssignJd(models.Model):
    user = models.ForeignKey(
    User,
    on_delete=models.CASCADE,
    related_name="assignjd"
    )       
    client = models.ForeignKey(TblClient, on_delete=models.CASCADE)
    departments = models.ManyToManyField(TblDepartment, blank=True)
    jd = models.ForeignKey(TblJobDescription, on_delete=models.CASCADE)
    status = models.CharField(max_length=100, default="Assigned")
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

class UserRecruiterMapping(models.Model):
    user = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="recruiter_mappings"
    )
    recruiter = models.ForeignKey(
        User,
        on_delete=models.CASCADE,
        related_name="mapped_delivery_managers"
    )

    class Meta:
        unique_together = ("user", "recruiter")

class EmailOTPUser(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="api_email_otps")
    counter = models.IntegerField(default=0)   # used for HOTP
    is_verified = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now_add=True)  # record when created
    updated_at = models.DateTimeField(auto_now=True)      # record when updated

    def __str__(self):
        return f"OTP for {self.user.username} (Verified: {self.is_verified})"
    
class LinkedInJobPost(models.Model):
    jd = models.ForeignKey(
        TblJobDescription,
        on_delete=models.CASCADE,
        related_name="linkedin_posts"
    )
    posted_by = models.ForeignKey(
        User,
        on_delete=models.CASCADE
    )

    linkedin_post_urn = models.CharField(
        max_length=255,
        blank=True,
        null=True
    )

    apply_url = models.URLField(
        blank=True,
        null=True
    )
    status = models.CharField(
        max_length=20,
        choices=[
            ("Posted", "Posted"),
            ("Failed", "Failed"),
            ("Replaced", "Replaced"),
        ],
        default="Posted"
    )
    posted_payload = models.JSONField(
        blank=True,
        null=True,
        help_text="Fields selected and custom text used for posting"
    )

    posted_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return f"{self.jd.jd_display_id} - {self.status}"
    
class ImplementerPortalConfig(models.Model):

    PORTAL_CHOICES = (
        ("linkedin", "LinkedIn"),
        ("naukri", "Naukri"),
        ("monster", "Monster"),
        ("job bank", "Job bank"),
        ("job seek", "Job seek"),
        ("foundit", "Foundit")
    )

    implementer = models.ForeignKey(
        ImplementerDetails,
        on_delete=models.CASCADE,
        related_name="portal_configs"
    )

    portal = models.CharField(max_length=50, choices=PORTAL_CHOICES)
    name = models.CharField(max_length=255, blank=True, null=True)

    client_id = models.CharField(max_length=255, blank=True, null=True)
    client_secret = models.CharField(max_length=255, blank=True, null=True)
    redirect_uri = models.URLField(blank=True, null=True)
    scope = models.CharField(
        max_length=255,
        blank=True,
        null=True,
        default="openid profile email w_member_social"
    )

    portal_username = models.CharField(max_length=255, blank=True, null=True)
    portal_password = models.TextField(blank=True, null=True)

    has_token = models.BooleanField(default=False)

    access_token = models.TextField(blank=True, null=True)
    refresh_token = models.TextField(blank=True, null=True)
    expires_at = models.DateTimeField(blank=True, null=True)

    portal_user_urn = models.CharField(max_length=255, blank=True, null=True)

    is_connected = models.BooleanField(default=False)
    is_active = models.BooleanField(default=True)

    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        db_table = "implementer_portal_config"

class TblMatchedProfiles(models.Model):
    resume_id = models.IntegerField()
    job_id = models.IntegerField()
    status = models.CharField(max_length=50)
    status_updated_at = models.DateTimeField(default=timezone.now)
    l0_scheduled_at = models.DateTimeField(null=True, blank=True)
    l0_interview_summary = models.JSONField(default=list, blank=True, null=True)
    accepted_message = models.TextField(null=True, blank=True)
    accepted_at = models.DateTimeField(null=True, blank=True)
    interview_scheduled = models.BooleanField(default=False)
    interview_datetime = models.DateTimeField(null=True, blank=True)
    interview_link = models.CharField(max_length=500, null=True, blank=True)
    notice_period = models.CharField(max_length=50, null=True, blank=True)
    current_salary = models.CharField(max_length=50, null=True, blank=True)
    expected_salary = models.CharField(max_length=50, null=True, blank=True)
    visa_status = models.CharField(max_length=100, null=True, blank=True)
    remarks = models.TextField(max_length=4000, blank=True, null=True)
    match_score = models.FloatField()
    primaryskill_score = models.FloatField(null=True)
    secondaryskill_score = models.FloatField(null=True)
    experience_score = models.BooleanField()
    responsibilities_score = models.FloatField(null=True)
    tech_framework_score = models.FloatField(null=True)
    created_date = models.DateTimeField(auto_now_add=True)

    def save(self, *args, **kwargs):
        if self.pk:
            old = TblMatchedProfiles.objects.get(pk=self.pk)
            if old.status != self.status:
                self.status_updated_at = timezone.now()
        else:
            self.status_updated_at = timezone.now()

        super().save(*args, **kwargs)

class L0InterviewList(models.Model):
    matched_profile = models.ForeignKey(TblMatchedProfiles, on_delete=models.CASCADE, related_name="l0_matched_profiles")
    status = models.CharField(max_length=255, null=True, blank=True)
    interview_score = models.JSONField(default=list, null=True, blank=True)
    interview_video = models.FileField(upload_to=l0_video_upload_path, null=True, blank=True)
    interview_completed_time = models.DateTimeField(null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

class L0InterviewSessions(models.Model):
    matched_profile = models.ForeignKey(TblMatchedProfiles, on_delete=models.CASCADE, related_name="l0_matched_profiles_sessions")
    l0_interview = models.ForeignKey(L0InterviewList, on_delete=models.CASCADE, related_name="l0_interview")
    recorded_video = models.FileField(upload_to=l0_video_upload_path, null=True, blank=True)
    question = models.TextField()
    answer = models.TextField( null=True, blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

class TblCandidateProfile(models.Model):
    id = models.AutoField(primary_key=True)
    first_name = models.CharField(max_length=250)
    last_name = models.CharField(max_length=250, blank=True, null=True)
    email = models.CharField(max_length=250, unique=True, null=True, blank=True)
    coutry_code = models.CharField(max_length=50, null=True, blank=True)
    mobile_number = models.CharField(max_length=50, null=True, blank=True)
    candidate_photo = models.ImageField(upload_to="Candidate Images/", null=True, blank=True)
    source = models.CharField(max_length=50, null=True, blank=True, default="manual")
    source_ref = models.CharField(max_length=500, null=True, blank=True)
    candidate_status = models.CharField(max_length=100, blank=True, null=True)
    age = models.CharField(max_length=100, blank=True, null=True)
    gender = models.CharField(max_length=100, blank=True, null=True)
    notice_period = models.CharField(max_length=500, blank=True, null=True)
    employment_type = models.CharField(max_length=500, blank=True, null=True)
    notes = models.TextField(blank=True, null=True)
    current_salary = models.CharField(max_length=500, blank=True, null=True)
    expected_salary = models.CharField(max_length=500, blank=True, null=True)
    visa_status = models.CharField(max_length=500, blank=True, null=True)
    source_ref = models.CharField(max_length=200, blank=True, null=True)
    created_by = models.CharField(max_length=100, blank=True, null=True)
    created_dt = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    updated_by = models.CharField(max_length=100, blank=True, null=True)
    updated_dt = models.DateTimeField(auto_now=True, null=True)

    class Meta:
        ordering = ['-id']

class TblCandidateResume(models.Model):
    resume_id = models.AutoField(primary_key=True)
    candidate_id = models.ForeignKey(TblCandidateProfile, on_delete=models.CASCADE, related_name="candidate_resume", blank=True, null=True)
    title = models.CharField(max_length=250)
    totalexp = models.CharField(max_length=250, null=True, blank=True)
    company = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    responsibility_summary = models.TextField(blank=True, null=True)
    resume_responsibilities_summary_embedding = VectorField(dimensions=768, null=True)
    salary = models.CharField(max_length=250, null=True, blank=True)
    location = models.CharField(max_length=250, null=True, blank=True)
    email = models.CharField(max_length=250, null=True, blank=True)
    mobile_number = models.CharField(max_length=50, null=True, blank=True)
    primary_skills = models.JSONField(
        default=list
    )
    technical_skills = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    tools_and_frameworks = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    soft_skills = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    domain_skills = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    education = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    certifications = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    projects = models.JSONField(
        default=list,
        blank=True,
        null=True
    )
    status = models.CharField(max_length=100, blank=True, null=True)
    resume_file = models.CharField(max_length=500, blank=True, null=True)
    parsed_text = models.TextField(blank=True, null=True)
    version = models.CharField(max_length=100, blank=True, null=True)
    is_latest = models.BooleanField(default=True)
    resume_source = models.CharField(max_length=200, blank=True, null=True)
    l0_interview_data = models.ForeignKey(L0InterviewList, on_delete=models.SET_NULL, related_name="l0_candidate_interview", blank=True, null=True)
    l0_interview_summary = models.JSONField(default=list, blank=True, null=True)
    created_by = models.CharField(max_length=100, blank=True, null=True)
    created_dt = models.DateTimeField(auto_now_add=True, blank=True, null=True)
    updated_by = models.CharField(max_length=100, blank=True, null=True)
    updated_dt = models.DateTimeField(auto_now=True, null=True)

class career(models.Model):
    implementer = models.ForeignKey(
        ImplementerDetails,
        on_delete=models.CASCADE,
        related_name="careers"
    )

    name = models.CharField(max_length=100)
    code = models.CharField(max_length=50)

    logo = models.ImageField(upload_to="careers/", null=True, blank=True)

    db_username = models.CharField(max_length=150)
    db_password = models.CharField(max_length=255)
    ip_address = models.GenericIPAddressField()
    port = models.IntegerField(default=5432)

    is_active = models.BooleanField(default=True)
    last_sync = models.DateTimeField(null=True, blank=True)

    created_by = models.CharField(max_length=100, blank=True, null=True)
    created_dt = models.DateTimeField(auto_now_add=True, blank=True, null=True)

    class Meta:
        db_table = "tbl_careers"
        unique_together = ("implementer", "code")

    def __str__(self):
        return f"{self.implementer.display_name} - {self.name}"

class FounditJobPost(models.Model):
    jd = models.ForeignKey(TblJobDescription, on_delete=models.CASCADE, related_name="foundit_posts")
    job_id = models.CharField(max_length=255, blank=True, null=True) # The ID returned by Foundit
    posted_by = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    created_dt = models.DateTimeField(auto_now_add=True)

    class Meta:
        db_table = "tbl_foundit_job_post"

class FacebookJobPost(models.Model):
    jd = models.ForeignKey(
        TblJobDescription,
        on_delete=models.CASCADE,
        related_name="facebook_posts"
    )
    posted_by = models.ForeignKey(User, on_delete=models.CASCADE)
    facebook_post_id = models.CharField(max_length=255, null=True, blank=True)
    apply_url = models.CharField(max_length=500)
    status = models.CharField(
        max_length=50,
        choices=[("Posted", "Posted"), ("Failed", "Failed")]
    )
    posted_payload = models.JSONField(default=dict)
    posted_at = models.DateTimeField(auto_now_add=True)

class JobApplication(models.Model):
    jd = models.ForeignKey(
        TblJobDescription,
        on_delete=models.CASCADE,
        related_name="applications"
    )

    full_name = models.CharField(max_length=200)
    email = models.EmailField()
    mobile = models.CharField(max_length=20)

    resume = models.FileField(
        upload_to="public_resumes/"
    )

    source = models.CharField(
        max_length=50,
        default="Public"
    )

    applied_at = models.DateTimeField(auto_now_add=True)

    ip_address = models.GenericIPAddressField(null=True, blank=True)
    user_agent = models.TextField(null=True, blank=True)

    def __str__(self):
        return f"{self.full_name} - {self.jd.jd_display_id}"

class TblJDResumeSeen(models.Model):
    jd_id = models.IntegerField()
    resume_id = models.IntegerField()
    created_date = models.DateTimeField(auto_now_add=True)

    class Meta:
        unique_together = ('jd_id', 'resume_id')

class CareerJobApplication(models.Model):
    jd = models.ForeignKey(
        TblJobDescription,
        on_delete=models.CASCADE,
        related_name="career_applications"
    )

    full_name = models.CharField(max_length=200)
    email = models.EmailField()
    mobile = models.CharField(max_length=20)

    resume = models.FileField(
        upload_to="public_resumes/"
    )

    source = models.CharField(
        max_length=50,
        default="Public"
    )

    applied_at = models.DateTimeField(auto_now_add=True)

    ip_address = models.GenericIPAddressField(null=True, blank=True)
    user_agent = models.TextField(null=True, blank=True)

    def __str__(self):
        return f"{self.full_name} - {self.jd.jd_display_id}"
    
class EmailQueue(models.Model):
    STATUS_CHOICES = (
        ("PENDING", "Pending"),
        ("SENT", "Sent"),
        ("FAILED", "Failed"),
    )

    to_email = models.EmailField()
    subject = models.CharField(max_length=255)
    body = models.TextField()
    from_email = models.EmailField()
    retry_count = models.PositiveIntegerField(default=0)
    max_retries = models.PositiveIntegerField(default=5)
    status = models.CharField(
        max_length=10, choices=STATUS_CHOICES, default="PENDING"
    )
    last_error = models.TextField(blank=True, null=True)
    next_retry_at = models.DateTimeField(default=timezone.now)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def can_retry(self):
        return self.retry_count < self.max_retries

class WhatsAppQueue(models.Model):
    to_phone = models.CharField(max_length=20)
    template_name = models.CharField(max_length=100)
    template_params = models.JSONField()
    status = models.CharField(
        max_length=20,
        choices=[
            ("SENT", "Sent"),
            ("FAILED", "Failed"),
            ("PENDING", "Pending"),
        ],
        default="PENDING",
    )
    retry_count = models.IntegerField(default=0)
    next_retry_at = models.DateTimeField(null=True, blank=True)
    last_error = models.TextField(blank=True, null=True)
    created_at = models.DateTimeField(auto_now_add=True)

class SMSQueue(models.Model):
    to_phone = models.CharField(max_length=20)
    message = models.TextField()
    status = models.CharField(max_length=20, default="PENDING")
    retry_count = models.IntegerField(default=0)
    next_retry_at = models.DateTimeField(null=True, blank=True)
    last_error = models.TextField(null=True, blank=True)

class PublicLink(models.Model):
    token = models.CharField(max_length=20, unique=True)
    resume_id = models.CharField(max_length=100, null=True, blank=True)
    jd_id = models.CharField(max_length=100, null=True, blank=True)
    link_type = models.CharField(max_length=50)  # shortlist / interview / job
    created_at = models.DateTimeField(auto_now_add=True)

    def __str__(self):
        return self.token
    
class TblMatchedProfilesCandidate(models.Model):
    resume_id = models.IntegerField()
    jd_id = models.IntegerField()
    status = models.CharField(max_length=20, null=True, blank=True)
    overall_score = models.FloatField(null=True, blank=True)
    primaryskill_score = models.FloatField(null=True, blank=True)
    technicalskill_score = models.FloatField(null=True, blank=True)
    experience_score = models.BooleanField(null=True, blank=True)
    responsibilities_score = models.FloatField(null=True, blank=True)
    tech_framework_score = models.FloatField(null=True, blank=True)
    created_date = models.DateTimeField(auto_now_add=True)

class JobEmailShare(models.Model):
    job = models.ForeignKey(
        "TblJobDescription",
        on_delete=models.CASCADE,
        related_name="email_shares"
    )
    your_name = models.CharField(max_length=200)
    your_email = models.EmailField()
    friend_emails = models.TextField()
    created_at = models.DateTimeField(default=timezone.now)

    def __str__(self):
        return f"{self.your_name} shared {self.job.job_title}"