from sqlalchemy import Column, Integer, String, ForeignKey, Date, Boolean, Float, Text, ARRAY, TIMESTAMP
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from enum import Enum as PyEnum
from xtend_recruit_back.db.base import Base



class ExperienceType(PyEnum):
    PROFESSIONAL = "professional"
    ACADEMIC = "academic"

class LanguageProficiency(PyEnum):
    BASIC = "basic"
    INTERMEDIATE = "intermediate"
    FLUENT = "fluent"
    NATIVE = "native"


class Email(Base):
    __tablename__ = "email"
    id = Column(Integer, primary_key=True, index=True)
    sender_email = Column(String, nullable=False)
    sender_name = Column(String, nullable=True)
    subject = Column(String, nullable=True)
    body = Column(String, nullable=True)
    is_extracted = Column(Boolean, default=False)
    
    cvs = relationship("CV", back_populates="email")

class CV(Base):
    __tablename__ = "cv"
    id = Column(Integer, primary_key=True, index=True)
    email_id = Column(Integer, ForeignKey("email.id"), nullable=False)
    file_url = Column(String, nullable=False)
    extracted_at = Column(TIMESTAMP, nullable=True)

    email = relationship("Email", back_populates="cvs")
    candidate = relationship("Candidate", uselist=False, back_populates="cv")

class Candidate(Base):
    __tablename__ = "candidate"
    id = Column(Integer, primary_key=True, index=True)
    cv_id = Column(Integer, ForeignKey("cv.id"), unique=True, nullable=False)
    name = Column(String, nullable=True)
    email = Column(String, nullable=True)
    phone = Column(String, nullable=True)
    address = Column(String, nullable=True)
    linkedin = Column(String, nullable=True)

    cv = relationship("CV", back_populates="candidate")
    experiences = relationship("Experience", back_populates="candidate")
    educations = relationship("Education", back_populates="candidate")
    certificates = relationship("Certificate", back_populates="candidate")
    skills = relationship(
    "CandidateSkill",
    back_populates="candidate",
)
    scores = relationship("Score", back_populates="candidate")

class Experience(Base):
    __tablename__ = "experience"
    id = Column(Integer, primary_key=True, index=True)
    candidate_id = Column(Integer, ForeignKey("candidate.id"), nullable=False)
    job_title = Column(String, nullable=False)
    company = Column(String, nullable=False)
    type = Column(String, nullable=True)
    key_skills = Column(ARRAY(String), nullable=True)
    start_date = Column(Date, nullable=True)
    end_date = Column(Date, nullable=True)
    description = Column(Text, nullable=True)
    
    candidate = relationship("Candidate", back_populates="experiences")

class Education(Base):
    __tablename__ = "education"
    id = Column(Integer, primary_key=True, index=True)
    candidate_id = Column(Integer, ForeignKey("candidate.id"), nullable=False)
    degree = Column(String, nullable=False)
    institution = Column(String, nullable=False)
    graduation_date = Column(Date, nullable=True)
    candidate = relationship("Candidate", back_populates="educations")

class Certificate(Base):
    __tablename__ = "certificate"
    id = Column(Integer, primary_key=True, index=True)
    candidate_id = Column(Integer, ForeignKey("candidate.id"), nullable=False)
    name = Column(String, nullable=True)
    issuer = Column(String, nullable=True)
    issue_date = Column(Date, nullable=True)
    candidate = relationship("Candidate", back_populates="certificates")

class Language(Base):
    __tablename__ = "language"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    code = Column(String, unique=True, nullable=False)
    name = Column(String, nullable=False)

class CandidateLanguage(Base):
    __tablename__ = "candidate_language"
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    candidate_id = Column(Integer, ForeignKey("candidate.id"))
    language_id = Column(Integer, ForeignKey("language.id"))
    proficiency = Column(String, nullable=False)



class Skill(Base):
    __tablename__ = "skill"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, unique=True, nullable=False)

    candidates = relationship(
        "CandidateSkill", back_populates="skill", cascade="all, delete-orphan"
    )


class CandidateSkill(Base):
    __tablename__ = "candidate_skill"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    candidate_id = Column(Integer, ForeignKey("candidate.id"))
    skill_id = Column(Integer, ForeignKey("skill.id"))

    candidate = relationship("Candidate", back_populates="skills")
    skill = relationship("Skill", back_populates="candidates")

class Score(Base):
    __tablename__ = "score"
    id = Column(Integer, primary_key=True, index=True)
    candidate_id = Column(Integer, ForeignKey("candidate.id"), nullable=False)
    similarity_score = Column(Float, nullable=True)
    candidate = relationship("Candidate", back_populates="scores")
from typing import List, Optional
from datetime import date
from cvs_manager.models import ExperienceType, LanguageProficiency
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import and_, or_
from ..repositories import CandidateRepository
from ..models import Candidate
from ..schemas import CandidateFlat
from .dto import CandidateFilterDTO

class FilterService:
    def __init__(self, session: AsyncSession):
        self.candidate_repo = CandidateRepository(session)

    async def filter_candidates(self, filters: CandidateFilterDTO) -> List[CandidateFlat]:
        """
        Filters candidates based on the provided criteria.
        Converts high-level DTO filters to database queries.
        """
        # Convert DTO filters to repository-compatible format
        repo_filters = self._build_repository_filters(filters)
        
        # Define which relationships to load based on filters
        relationships = self._get_required_relationships(filters)
        
        return await self.candidate_repo.get_all(
            filters=repo_filters,
            relationships=relationships
        )

    def _build_repository_filters(self, filters: CandidateFilterDTO) -> dict:
        """Converts DTO filters to SQLAlchemy-compatible filters"""
        repo_filters = {}
        
        # Age range (assuming birth_date field exists)
        if filters.age_range:
            min_age, max_age = filters.age_range
            repo_filters.update({
                "birth_date__lte": date.today().replace(year=date.today().year - min_age),
                "birth_date__gte": date.today().replace(year=date.today().year - max_age)
            })
        
        # Creation date
        if filters.created_at:
            repo_filters["created_at"] = filters.created_at
        
        # Rating
        if filters.rating:
            repo_filters["rating__gte"] = filters.rating
        
        # Languages (requires relationship filtering)
        if filters.languages:
            repo_filters["languages__name__in"] = filters.languages
        
        # Skills (search in experiences)
        if filters.skills:
            repo_filters["experiences__key_skills__contains"] = filters.skills
        
        # Experience type
        if filters.experience:
            repo_filters["experiences__type"] = filters.experience
        
        # Employment type
        if filters.employment:
            repo_filters["employment"] = filters.employment
            
        return repo_filters

    def _get_required_relationships(self, filters: CandidateFilterDTO) -> List[str]:
        """Determines which relationships to eager load based on filters"""
        relationships = []
        
        if filters.languages or any(f for f in filters.dict() if f.startswith('languages_')):
            relationships.append("languages")
            
        if filters.skills or filters.experience:
            relationships.append("experiences")
            
        return relationships

    async def get_filter_options(self) -> dict:
        """Returns available filter options for UI"""
        return {
            "experience_types": [e.value for e in ExperienceType],
            "employment_types": ["Full-time", "Part-time", "Contract", "Internship"],
            "language_proficiencies": [lp.value for lp in LanguageProficiency]
        }from typing import Any, Dict, List, Optional
from ..models import Candidate
from ..schemas import CandidateFlat, CandidateUncommitted
from xtend_recruit_back.utils.repositories import BaseRepository

class CandidateRepository(BaseRepository[Candidate]):
    schema_class = Candidate

    async def get(self, id_: int) -> CandidateFlat:
        result = await self._get(
            id_,
            relationships=[
                "cv",
                "experiences",
                "educations",
                "certificates",
                "languages"
            ]
        )
        return CandidateFlat.model_validate(result)

    async def create(self, schema: CandidateUncommitted) -> CandidateFlat:
        instance: Candidate = await self._save(schema.model_dump())
        return CandidateFlat.model_validate(instance)

    async def update(self, id_: int, payload: Dict[str, Any]) -> CandidateFlat:
        instance = await self._update("id", id_, payload)
        return CandidateFlat.model_validate(instance)

    async def get_all(
        self, 
        filters: Optional[Dict[str, Any]] = None,
        relationships: Optional[List[str]] = None
    ) -> List[CandidateFlat]:
        """The ONLY get_all implementation needed across all repos"""
        return [
            CandidateFlat.model_validate(candidate)
            async for candidate in self._all_with_filters(
                filters=filters,
                relationships=relationships
            )
        ] is this correct ?