import sys
import os
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..', 'model')))


from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy.orm import joinedload
from sqlalchemy import or_
from sqlalchemy.exc import IntegrityError
from model.database import Client, AppliedJob, Job

def get_filtered_jobs(session: AsyncSession, user_job_titles, minimum_annual_salary, salary_currency,
                      user_location_type, user_locations, user_levels, user_job_types):
    # Строим фильтры для каждого из параметров пользователя
    filters = []

    # Фильтрация по job_title (примерное совпадение)
    if user_job_titles:
        filters.append(Job.job_title.in_(user_job_titles))

    # Фильтрация по minimum_annual_salary (если указано)
    if minimum_annual_salary is not None:
        filters.append(Job.minimum_annual_salary >= minimum_annual_salary)

    # Фильтрация по salary_currency (если указано)
    if salary_currency is not None:
        filters.append(Job.salary_currency == salary_currency)

    # Фильтрация по location_type (если указано)
    if user_location_type:
        filters.append(Job.location_type == user_location_type)

    # Фильтрация по location (разделяем по каждому городу)
    if user_locations:
        location_filters = [Job.location.ilike(location) for location in user_locations]
        filters.append(or_(*location_filters))

    # Фильтрация по job_level (если указаны)
    if user_levels:
        filters.append(Job.job_level.in_(user_levels))

    # Фильтрация по job_type (если указаны)
    if user_job_types:
        filters.append(Job.job_type.in_(user_job_types))

    # Выполняем запрос с применением всех фильтров
    query = session.query(Job).filter(*filters)

    # Получаем все результаты
    jobs = query.all()

    return jobs

# Пример использования функции
jobs = get_filtered_jobs(
    session=session,
    user_job_titles=["Electronics Engineer", "Hardware Engineer"],
    minimum_annual_salary=None,
    salary_currency=None,
    user_location_type=None,
    user_locations=["Burnaby, Canada", "Vancouver, Canada", "Toronto, Canada"],
    user_levels=["Mid", "Senior", "Manager"],
    user_job_types=["Full-time", "Permanent"]
)

# Выводим вакансии
for job in jobs:
    print(job.job_title, job.location, job.job_level, job.job_type)