import { Injectable, Logger } from '@nestjs/common';
import { DataSource } from 'typeorm';
import { CompaniesService } from '../../modules/companies/companies.service';
import { RolesService } from '../../modules/roles/roles.service';
import { CityService } from '../../modules/city/city.service';
import { DashboardService } from '../../modules/dashboard/dashboard.service';

type JobListOptions = {
  userId?: number;
  cityId?: number;
  areaId?: number;
  roleId?: number;
  page: number;
  limit: number;
  profileContext?: ProfileContext;
  skipCount?: boolean;
};

type ProfileContext = {
  seekerId: number | null;
  cityId: number | null;
  areaId: number | null;
  roleIds: number[];
  skillIds: number[];
  roleCategory: 'blue_collar' | 'white_collar' | null;
  userCard?: {
    id: number;
    name: string;
    profileCompletion: number;
    profileImage: string;
    notificationCount: number;
  };
};

type SearchOptions = {
  query?: string;
  skills?: string;
  designation?: string;
  company?: string;
  location?: string;
  education?: string;
  jobType?: string;
  city?: string;
  industry?: string;
  limit?: number;
};

type MobileSearchJobsOptions = {
  query?: string;
  location?: string;
  education?: string;
  jobTypes?: string;
  jobTypeIds?: string;
  cities?: string;
  cityIds?: string;
  industryIds?: string;
  educationIds?: string;
  page: number;
  limit: number;
};

type MobileFilterJobsOptions = {
  jobTypes?: string;
  jobTypeIds?: string;
  cities?: string;
  cityIds?: string;
  industryIds?: string;
  educationIds?: string;
  page: number;
  limit: number;
};

type IdParseResult = {
  ids: number[];
};

@Injectable()
export class MobileDashboardService {
  private readonly logger = new Logger(MobileDashboardService.name);

  constructor(
    private readonly dataSource: DataSource,
    private readonly companiesService: CompaniesService,
    private readonly rolesService: RolesService,
    private readonly cityService: CityService,
    private readonly dashboardService: DashboardService,
  ) {}

  private locationLookup: Record<string, 'city' | 'state' | 'country'> | null = null;

  private async buildLocationLookup(): Promise<Record<string, 'city' | 'state' | 'country'>> {
    if (this.locationLookup) return this.locationLookup;

    const lookup: Record<string, 'city' | 'state' | 'country'> = {};
    try {
      const cities: { city_name: string }[] = await this.dataSource.query(
        'SELECT city_name FROM city WHERE city_name IS NOT NULL',
      );
      for (const row of cities) {
        if (row.city_name) {
          lookup[row.city_name.toLowerCase().trim()] = 'city';
        }
      }

      const states: { state_name: string }[] = await this.dataSource.query(
        'SELECT state_name FROM state WHERE state_name IS NOT NULL',
      );
      for (const row of states) {
        if (row.state_name) {
          lookup[row.state_name.toLowerCase().trim()] = 'state';
        }
      }

      const countries: { country_name: string }[] = await this.dataSource.query(
        'SELECT country_name FROM country WHERE country_name IS NOT NULL',
      );
      for (const row of countries) {
        if (row.country_name) {
          lookup[row.country_name.toLowerCase().trim()] = 'country';
        }
      }
    } catch (err) {
      this.logger.error('buildLocationLookup failed', err);
    }
    this.locationLookup = lookup;
    return lookup;
  }



  private getUserCardFromContext(profileContext: ProfileContext) {
    const user = profileContext.userCard || {
      id: 0,
      name: 'User',
      profileCompletion: 0,
      profileImage: 'https://cdn.jobvumi.com/users/default.png',
      notificationCount: 0,
    };
    return {
      profileCompletion: user.profileCompletion,
      user,
    };
  }

  // ============================================================================
  // Mobile Dashboard Search/Filter APIs (spec-compliant)
  // ============================================================================

  async getDashboard(options: {
    userId: number;
    limit?: number;
    city?: string;
    type?: string;
    industry?: string;
    education?: string;
  }) {
    const { userId } = options;
    const limit = options.limit || 10;
    
    const extraFilters: string[] = [];
    const extraParams: any[] = [];
    if (options.city) {
      const { ids } = this.parseCsvIds(options.city);
      const names = this.parseCsvStrings(options.city);
      if (ids.length > 0) {
        extraFilters.push(`job.city_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM city c WHERE c.city_id = job.city_id AND LOWER(c.city_name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }
    if (options.type) {
      const { ids } = this.parseCsvIds(options.type);
      const names = this.parseCsvStrings(options.type);
      if (ids.length > 0) {
        extraFilters.push(`job.job_type_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM job_type jt WHERE jt.id = job.job_type_id AND LOWER(jt.name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }
    if (options.industry) {
      const { ids } = this.parseCsvIds(options.industry);
      const names = this.parseCsvStrings(options.industry);
      if (ids.length > 0) {
        extraFilters.push(`job.industry_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM industries ind WHERE ind.id = job.industry_id AND LOWER(ind.name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }
    if (options.education) {
      const { ids } = this.parseCsvIds(options.education);
      const names = this.parseCsvStrings(options.education);
      if (ids.length > 0) {
        extraFilters.push(`job.education_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM education_levels edu WHERE edu.id = job.education_id AND LOWER(edu.name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }
    const profileContext = await this.getProfileContext(userId);

    if (profileContext.roleCategory === 'white_collar') {
      const halfLimit = Math.ceil(limit / 2);
      const recLimit = limit - halfLimit;

      const jobsForProfile = await this.fetchWhiteCollarSequentialJobs({
        profile: profileContext,
        page: 1,
        limit: halfLimit,
        type: 'profile',
        skipCount: true,
        extraFilters,
        extraParams,
      });

      const profileJobIds = jobsForProfile.data.map((j) => Number(j.job_id)).filter(id => id > 0);

      const [recommendedJobs, topCompanies] = await Promise.all([
        this.fetchWhiteCollarSequentialJobs({
          profile: profileContext,
          page: 1,
          limit: recLimit,
          type: 'recommended',
          excludeJobIds: profileJobIds,
          skipCount: true,
          extraFilters,
          extraParams,
        }),
        this.getTopCompanies(limit),
      ]);

      const userCard = this.getUserCardFromContext(profileContext);

      return {
        success: true,
        jobs_based_on_profile: jobsForProfile.data,
        recommended_jobs: recommendedJobs.data,
        top_companies: topCompanies.companies,
        profile_context: recommendedJobs.profile_context || jobsForProfile.profile_context,

        // Mobile dashboard UI metadata
        user: userCard.user,
        searchPlaceholder: 'Search for role, company, location...',
        banners: [
          {
            id: 'BNR001',
            title: 'Find the job that fits your life',
            description: 'Explore thousands of non-IT jobs and build your career',
            image: 'https://cdn.jobvumi.com/banner/blue_collar_banner.png',
            buttonText: 'Explore Jobs',
            redirectType: 'jobs',
            redirectId: null,
          },
        ],
        profileCard: {
          title: 'Let employers find you',
          profileImage: userCard.user.profileImage,
          description: 'Complete your profile and stand out to top employers',
          completionPercentage: userCard.profileCompletion,
          actionButton: 'Complete Profile',
        },
      };
    }

    const halfLimit = Math.ceil(limit / 2);
    const recLimit = limit - halfLimit;

    const jobsForProfile = await this.fetchBlueCollarSequentialJobs({
      profile: profileContext,
      page: 1,
      limit: halfLimit,
      type: 'profile',
      skipCount: true,
      extraFilters,
      extraParams,
    });

    const profileJobIds = jobsForProfile.data.map((j) => Number(j.job_id)).filter(id => id > 0);

    const [recommendedJobs, topCompanies] = await Promise.all([
      this.fetchBlueCollarSequentialJobs({
        profile: profileContext,
        page: 1,
        limit: recLimit,
        type: 'recommended',
        excludeJobIds: profileJobIds,
        skipCount: true,
        extraFilters,
        extraParams,
      }),
      this.getTopCompanies(limit),
    ]);

    const userCard = this.getUserCardFromContext(profileContext);

    return {
      success: true,
      jobs_based_on_profile: jobsForProfile.data,
      recommended_jobs: recommendedJobs.data,
      top_companies: topCompanies.companies,
      profile_context: recommendedJobs.profile_context || jobsForProfile.profile_context,

      // Mobile dashboard UI metadata
      user: userCard.user,
      searchPlaceholder: 'Search for role, company, location...',
      banners: [
        {
          id: 'BNR001',
          title: 'Find the job that fits your life',
          description: 'Explore thousands of non-IT jobs and build your career',
          image: 'https://cdn.jobvumi.com/banner/blue_collar_banner.png',
          buttonText: 'Explore Jobs',
          redirectType: 'jobs',
          redirectId: null,
        },
      ],
      profileCard: {
        title: 'Let employers find you',
        profileImage: userCard.user.profileImage,
        description: 'Complete your profile and stand out to top employers',
        completionPercentage: userCard.profileCompletion,
        actionButton: 'Complete Profile',
      },
    };
  }

  async search(options: SearchOptions) {
    const {
      query = '',
      skills,
      designation,
      company,
      location,
      education,
      jobType,
      city,
      industry,
      limit = 10,
    } = options;

    const normalizedQuery = query.trim();

    // If query is empty, keep current behavior: no results.
    if (!normalizedQuery) {
      return {
        success: true,
        query: normalizedQuery,
        roles: [],
        companies: [],
        locations: [],
        jobs: [],
      };
    }

    const [roles, companies, locations, jobs] = await Promise.all([
      this.searchRoles(normalizedQuery, limit),
      this.searchCompanies(normalizedQuery, limit),
      this.searchLocations(normalizedQuery, limit),
      this.searchJobs({
        query: normalizedQuery,
        skills,
        designation,
        company,
        location,
        education,
        jobType,
        city,
        industry,
        limit,
      }),
    ]);

    return {
      success: true,
      query: normalizedQuery,
      roles,
      companies,
      locations,
      jobs,
    };
  }

  // ==========================================================================
  // Spec endpoint: GET /mobile/dashboard/search?query=&location=&education=&page=&limit=
  // ==========================================================================
  async searchDashboardJobsMobile(options: MobileSearchJobsOptions) {
    const {
      query,
      location,
      education,
      jobTypes,
      jobTypeIds,
      cities,
      cityIds,
      industryIds,
      educationIds,
      page,
      limit,
    } = options;

    const filters: string[] = [];
    const params: any[] = [];

    const { ids: parsedJobTypeIds } = this.parseCsvIds(jobTypeIds);
    const { ids: parsedCityIds } = this.parseCsvIds(cityIds);
    const { ids: parsedIndustryIds } = this.parseCsvIds(industryIds);
    const { ids: parsedEducationIds } = this.parseCsvIds(educationIds);

    const cityNames = this.parseCsvStrings(cities);
    const jobTypeNames = this.parseCsvStrings(jobTypes);

    // ─── Smart Keyword Parsing ─────────────────────────────────────────────────
    const STOP_WORDS = new Set([
      'jobs',
      'job',
      'in',
      'at',
      'for',
      'near',
      'the',
      'a',
      'an',
      'with',
      'and',
      'or',
      'on',
      'of',
      'to',
      'from',
      'by',
    ]);
    const KNOWN_LOCATIONS = await this.buildLocationLookup();

    let keyword = query || '';
    let autoCity = '';
    let autoState = '';
    let autoCountry = '';

    const spacedKeyword = (query || '')
      .replace(/([a-z])([A-Z])/g, '$1 $2')
      .replace(/[-_\/]/g, ' ');

    const hasExplicitLocation = !!(location || cities || cityIds);

    if (spacedKeyword && !hasExplicitLocation) {
      const rawLower = spacedKeyword
        .toLowerCase()
        .trim()
        .replace(/[^\w\s]/g, '');
      const tokens = rawLower.split(/\s+/);
      const jobTokens: string[] = [];

      for (let i = 0; i < tokens.length; i++) {
        const tok = tokens[i];
        const twoWord = i + 1 < tokens.length ? `${tok} ${tokens[i + 1]}` : '';
        if (twoWord && KNOWN_LOCATIONS[twoWord]) {
          const locType = KNOWN_LOCATIONS[twoWord];
          if (locType === 'city' && !autoCity) autoCity = twoWord;
          else if (locType === 'state' && !autoState) autoState = twoWord;
          else if (locType === 'country' && !autoCountry) autoCountry = twoWord;
          i++; // skip next token
        } else if (KNOWN_LOCATIONS[tok]) {
          const locType = KNOWN_LOCATIONS[tok];
          if (locType === 'city' && !autoCity) autoCity = tok;
          else if (locType === 'state' && !autoState) autoState = tok;
          else if (locType === 'country' && !autoCountry) autoCountry = tok;
        } else if (!STOP_WORDS.has(tok) && tok.length > 0) {
          jobTokens.push(tok);
        }
      }
      keyword = jobTokens.join(' ');
    } else {
      keyword = spacedKeyword;
    }

    // ─── Keyword Filters and Relevance Scoring ───────────────────────────────
    let relevanceScoreSql = '';
    const relevanceScoreParams: any[] = [];
    let hasKeywordSearch = false;

    if (keyword) {
      const cleanSearch = keyword
        .trim()
        .toLowerCase()
        .replace(/[^\w\s-]/g, '')
        .replace(/\s+/g, ' ');

      const keywords = cleanSearch
        .split(' ')
        .filter((k: string) => k.length > 0);

      if (keywords.length > 0) {
        hasKeywordSearch = true;

        const keywordFilters: string[] = [];
        for (const kw of keywords) {
          keywordFilters.push(`(
            LOWER(job.title) LIKE ? OR
            LOWER(company.name) LIKE ? OR
            EXISTS (
              SELECT 1
              FROM job_post_skills jps
              INNER JOIN skills sk ON sk.id = jps.skill_id
              WHERE jps.job_post_id = job.id AND LOWER(sk.skill_name) LIKE ?
            ) OR
            LOWER(job.description) LIKE ? OR
            LOWER(industry.name) LIKE ? OR
            LOWER(jobType.name) LIKE ? OR
            LOWER(role.name) LIKE ?
          )`);
          const kwLike = `%${kw}%`;
          params.push(kwLike, kwLike, kwLike, kwLike, kwLike, kwLike, kwLike);
        }

        filters.push(`(${keywordFilters.join(' OR ')})`);

        relevanceScoreSql = `(
          CASE WHEN LOWER(job.title) = ? THEN 100
               WHEN LOWER(job.title) LIKE ? THEN 80
               WHEN LOWER(company.name) = ? THEN 60
               ELSE 0
          END
        `;
        relevanceScoreParams.push(cleanSearch, `%${cleanSearch}%`, cleanSearch);

        for (const kw of keywords) {
          relevanceScoreSql += `
            + CASE WHEN LOWER(job.title) LIKE ? THEN 10 ELSE 0 END
            + CASE WHEN LOWER(company.name) LIKE ? THEN 5 ELSE 0 END
            + CASE WHEN EXISTS (
                SELECT 1
                FROM job_post_skills jps
                INNER JOIN skills sk ON sk.id = jps.skill_id
                WHERE jps.job_post_id = job.id AND LOWER(sk.skill_name) LIKE ?
              ) THEN 8 ELSE 0 END
            + CASE WHEN LOWER(job.description) LIKE ? THEN 3 ELSE 0 END
            + CASE WHEN LOWER(industry.name) LIKE ? THEN 6 ELSE 0 END
            + CASE WHEN LOWER(role.name) LIKE ? THEN 10 ELSE 0 END
          `;
          const kwLike = `%${kw}%`;
          relevanceScoreParams.push(kwLike, kwLike, kwLike, kwLike, kwLike, kwLike);
        }
        relevanceScoreSql += `) AS relevance_score`;
      }
    }

    // Apply auto-parsed locations
    if (autoCity) {
      filters.push(`LOWER(city.city_name) = LOWER(?)`);
      params.push(autoCity);
    }
    if (autoState) {
      filters.push(`LOWER(state.state_name) = LOWER(?)`);
      params.push(autoState);
    }
    if (autoCountry) {
      filters.push(`LOWER(country.country_name) = LOWER(?)`);
      params.push(autoCountry);
    }

    // Apply explicit location
    if (location) {
      filters.push(`(
        LOWER(city.city_name) LIKE LOWER(?)
        OR LOWER(state.state_name) LIKE LOWER(?)
        OR LOWER(country.country_name) LIKE LOWER(?)
        OR EXISTS (
          SELECT 1
          FROM area a
          WHERE a.id = job.area_id
            AND LOWER(a.area) LIKE LOWER(?)
        )
      )`);
      const locLike = `%${location}%`;
      params.push(locLike, locLike, locLike, locLike);
    }

    // Apply explicit education
    if (education) {
      filters.push(`EXISTS (
        SELECT 1
        FROM education_levels edu
        WHERE edu.id = job.education_id
          AND LOWER(edu.name) LIKE LOWER(?)
      )`);
      params.push(`%${education}%`);
    }

    // Additional filter params
    if (parsedJobTypeIds.length > 0) {
      filters.push(
        `job.job_type_id IN (${this.placeholders(parsedJobTypeIds.length)})`,
      );
      params.push(...parsedJobTypeIds);
    } else if (jobTypeNames.length > 0) {
      filters.push(`EXISTS (
        SELECT 1
        FROM job_type jt
        WHERE jt.id = job.job_type_id
          AND LOWER(jt.name) IN (${jobTypeNames
            .map(() => 'LOWER(?)')
            .join(', ')})
      )`);
      params.push(...jobTypeNames);
    }

    if (parsedCityIds.length > 0) {
      filters.push(
        `job.city_id IN (${this.placeholders(parsedCityIds.length)})`,
      );
      params.push(...parsedCityIds);
    } else if (cityNames.length > 0) {
      filters.push(`EXISTS (
        SELECT 1
        FROM city c
        WHERE c.city_id = job.city_id
          AND LOWER(c.city_name) IN (${cityNames
            .map(() => 'LOWER(?)')
            .join(', ')})
      )`);
      params.push(...cityNames);
    }

    if (parsedIndustryIds.length > 0) {
      filters.push(
        `job.industry_id IN (${this.placeholders(parsedIndustryIds.length)})`,
      );
      params.push(...parsedIndustryIds);
    }

    if (parsedEducationIds.length > 0) {
      filters.push(
        `job.education_id IN (${this.placeholders(parsedEducationIds.length)})`,
      );
      params.push(...parsedEducationIds);
    }

    const where = filters.length > 0 ? `AND ${filters.join(' AND ')}` : '';

    const offset = (page - 1) * limit;

    const dataParams = [
      ...(hasKeywordSearch ? relevanceScoreParams : []),
      ...params,
      limit,
      offset,
    ];
    const countParams = [...params];

    let baseSql = this.baseJobSelectForSearch(true);
    if (hasKeywordSearch && relevanceScoreSql) {
      baseSql = baseSql.replace(
        'SELECT DISTINCT',
        `SELECT DISTINCT ${relevanceScoreSql}, `,
      );
    }

    const orderBySql = hasKeywordSearch
      ? 'ORDER BY relevance_score DESC, job.featured DESC, job.created_at DESC'
      : 'ORDER BY job.featured DESC, job.created_at DESC';

    const jobsRows = await this.dataSource.query(
      `
      ${baseSql}
      WHERE job.status = 'open'
        AND (job.expires_at IS NULL OR job.expires_at > NOW())
        AND company.status = 1
        AND company.is_deleted = 0
        ${where}
      ${orderBySql}
      LIMIT ? OFFSET ?
    `,
      dataParams,
    );

    const countRows = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT job.id) AS total
      FROM job_posts job
      INNER JOIN companies company ON company.id = job.company_id
      LEFT JOIN roles role ON role.id = job.role_id
      LEFT JOIN city city ON city.city_id = job.city_id
      LEFT JOIN state state ON state.state_id = job.state_id
      LEFT JOIN country country ON country.country_id = job.country_id
      LEFT JOIN industries industry ON industry.id = job.industry_id
      LEFT JOIN job_type jobType ON jobType.id = job.job_type_id
      LEFT JOIN education_levels edu ON edu.id = job.education_id
      WHERE job.status = 'open'
        AND (job.expires_at IS NULL OR job.expires_at > NOW())
        AND company.status = 1
        AND company.is_deleted = 0
        ${where}
    `,
      countParams,
    );

    const total = Number(countRows?.[0]?.total) || 0;

    const jobIds = Array.from(
      new Set(
        jobsRows.map((j: any) => Number(j.id)).filter((id: number) => id > 0),
      ),
    );

    let skillsByJobId = new Map<number, string[]>();
    if (jobIds.length > 0) {
      const skillsRows = await this.dataSource.query(
        `
        SELECT
          jps.job_post_id AS job_post_id,
          s.skill_name AS skill_name
        FROM job_post_skills jps
        INNER JOIN skills s ON s.id = jps.skill_id
        WHERE jps.job_post_id IN (${this.placeholders(jobIds.length)})
        `,
        jobIds,
      );

      skillsByJobId = new Map<number, string[]>();
      for (const row of skillsRows as any[]) {
        const jobPostId = Number(row.job_post_id);
        if (!skillsByJobId.has(jobPostId)) skillsByJobId.set(jobPostId, []);
        const arr = skillsByJobId.get(jobPostId) as string[];
        if (row.skill_name && !arr.includes(row.skill_name))
          arr.push(row.skill_name);
      }
    }

    return {
      success: true,
      data: jobsRows.map((j: any) =>
        this.serializeJobForSearchAndFilter(
          j,
          skillsByJobId.get(Number(j.id)) || [],
          false,
        ),
      ),
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
      },
    };
  }

  // ==========================================================================
  // Spec endpoint: GET /mobile/dashboard/jobs/filter?jobTypes=&cityIds=&...

  // ==========================================================================
  async filterDashboardJobsMobile(options: MobileFilterJobsOptions) {
    const {
      jobTypes,
      jobTypeIds,
      cities,
      cityIds,
      industryIds,
      educationIds,
      page,
      limit,
    } = options;

    const { ids: parsedJobTypeIds } = this.parseCsvIds(jobTypeIds);
    const { ids: parsedCityIds } = this.parseCsvIds(cityIds);
    const { ids: parsedIndustryIds } = this.parseCsvIds(industryIds);
    const { ids: parsedEducationIds } = this.parseCsvIds(educationIds);

    const cityNames = this.parseCsvStrings(cities);
    const jobTypeNames = this.parseCsvStrings(jobTypes);

    const filters: string[] = [];
    const params: any[] = [];

    if (parsedJobTypeIds.length > 0) {
      filters.push(
        `job.job_type_id IN (${this.placeholders(parsedJobTypeIds.length)})`,
      );
      params.push(...parsedJobTypeIds);
    } else if (jobTypeNames.length > 0) {
      filters.push(`EXISTS (
        SELECT 1
        FROM job_type jt
        WHERE jt.id = job.job_type_id
          AND LOWER(jt.name) IN (${jobTypeNames
            .map(() => 'LOWER(?)')
            .join(', ')})
      )`);
      params.push(...jobTypeNames.map((n) => n));
    }

    if (parsedCityIds.length > 0) {
      filters.push(
        `job.city_id IN (${this.placeholders(parsedCityIds.length)})`,
      );
      params.push(...parsedCityIds);
    } else if (cityNames.length > 0) {
      filters.push(`EXISTS (
        SELECT 1
        FROM city c
        WHERE c.city_id = job.city_id
          AND LOWER(c.city_name) IN (${cityNames
            .map(() => 'LOWER(?)')
            .join(', ')})
      )`);
      params.push(...cityNames);
    }

    if (parsedIndustryIds.length > 0) {
      filters.push(
        `job.industry_id IN (${this.placeholders(parsedIndustryIds.length)})`,
      );
      params.push(...parsedIndustryIds);
    }

    if (parsedEducationIds.length > 0) {
      filters.push(
        `job.education_id IN (${this.placeholders(parsedEducationIds.length)})`,
      );
      params.push(...parsedEducationIds);
    }

    const where = filters.length > 0 ? `AND ${filters.join(' AND ')}` : '';
    const offset = (page - 1) * limit;

    const jobsRows = await this.dataSource.query(
      `
      ${this.baseJobSelectForSearch(true)}
      WHERE job.status = 'open'
        AND (job.expires_at IS NULL OR job.expires_at > NOW())
        AND company.status = 1
        AND company.is_deleted = 0
        ${where}
      ORDER BY job.created_at DESC
      LIMIT ? OFFSET ?
    `,
      [...params, limit, offset],
    );

    const countRows = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT job.id) AS total
      FROM job_posts job
      INNER JOIN companies company ON company.id = job.company_id
      LEFT JOIN roles role ON role.id = job.role_id
      LEFT JOIN city city ON city.city_id = job.city_id
      LEFT JOIN state state ON state.state_id = job.state_id
      LEFT JOIN country country ON country.country_id = job.country_id
      ${this.baseAreaJoinSql()}
      WHERE job.status = 'open'
        AND (job.expires_at IS NULL OR job.expires_at > NOW())
        AND company.status = 1
        AND company.is_deleted = 0
        ${where}
    `,
      [...params],
    );

    const total = Number(countRows?.[0]?.total) || 0;

    const jobIds = Array.from(
      new Set(
        jobsRows.map((j: any) => Number(j.id)).filter((id: number) => id > 0),
      ),
    );

    let skillsByJobId = new Map<number, string[]>();
    if (jobIds.length > 0) {
      const skillsRows = await this.dataSource.query(
        `
        SELECT
          jps.job_post_id AS job_post_id,
          s.skill_name AS skill_name
        FROM job_post_skills jps
        INNER JOIN skills s ON s.id = jps.skill_id
        WHERE jps.job_post_id IN (${this.placeholders(jobIds.length)})
        `,
        jobIds,
      );

      skillsByJobId = new Map<number, string[]>();
      for (const row of skillsRows as any[]) {
        const jobPostId = Number(row.job_post_id);
        if (!skillsByJobId.has(jobPostId)) skillsByJobId.set(jobPostId, []);
        const arr = skillsByJobId.get(jobPostId) as string[];
        if (row.skill_name && !arr.includes(row.skill_name))
          arr.push(row.skill_name);
      }
    }

    return {
      success: true,
      data: jobsRows.map((j: any) =>
        this.serializeJobForSearchAndFilter(
          j,
          skillsByJobId.get(Number(j.id)) || [],
          false,
        ),
      ),
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
      },
    };
  }

  // ==========================================================================
  // Filter metadata endpoints
  // ==========================================================================

  async getMobileJobTypesForFilter() {
    // job types are stored in job_type table
    const rows = await this.dataSource.query(
      `SELECT id, name FROM job_type WHERE status = 1 ORDER BY name ASC`,
    );
    return {
      success: true,
      data: rows.map((r: any) => ({ id: r.id, name: r.name })),
    };
  }

  async getMobileCitiesForFilter(options: { query?: string }) {
    const { query } = options;
    const q = query ? `%${query}%` : `%`;

    const rows = await this.dataSource.query(
      `
      SELECT 
        city_id AS id,
        city_name AS name,
        CONCAT(city_name, ', ', COALESCE(state_name,''), COALESCE('', '')) AS full_location
      FROM city c
      LEFT JOIN state s ON s.state_id = c.state_id
      WHERE c.status = 1
        AND (LOWER(c.city_name) LIKE LOWER(?) OR ? = '%%')
      ORDER BY c.is_featured DESC, c.city_name ASC
      LIMIT 50
    `,
      [q, q],
    );

    return {
      success: true,
      data: rows.map((r: any) => ({
        id: r.id,
        name: r.name,
        full_location: r.full_location || r.name,
      })),
    };
  }

  async getMobileIndustriesForFilter() {
    const rows = await this.dataSource.query(
      `SELECT id, name FROM industries WHERE status = 1 ORDER BY name ASC`,
    );
    return {
      success: true,
      data: rows.map((r: any) => ({ id: r.id, name: r.name })),
    };
  }

  async getMobileEducationLevelsForFilter() {
    const rows = await this.dataSource.query(
      `SELECT id, name FROM education_levels WHERE status = 1 ORDER BY name ASC`,
    );
    return {
      success: true,
      data: rows.map((r: any) => ({ id: r.id, name: r.name })),
    };
  }

  private async hasAtLeastJobs(
    filters: string[],
    params: any[],
    threshold: number,
  ): Promise<boolean> {
    const where = [
      "job.status = 'open'",
      '(job.expires_at IS NULL OR job.expires_at > NOW())',
      'company.status = 1',
      'company.is_deleted = 0',
      ...filters,
    ].join(' AND ');

    const countRows = await this.dataSource.query(
      `
      SELECT 1
      FROM job_posts job
      INNER JOIN companies company ON company.id = job.company_id
      LEFT JOIN roles role ON role.id = job.role_id
      WHERE ${where}
      LIMIT ?
      `,
      [...params, threshold],
    );

    return countRows.length >= threshold;
  }

  async getJobsForProfile(options: JobListOptions) {
    const profile = options.profileContext
      ? options.profileContext
      : options.userId
        ? await this.getProfileContext(options.userId)
        : this.emptyProfileContext();

    if (profile.roleCategory === 'white_collar') {
      const adjustedProfile = {
        ...profile,
        roleIds: options.roleId ? [options.roleId] : profile.roleIds,
        cityId: options.cityId || profile.cityId,
        areaId: options.areaId || profile.areaId,
      };
      return this.fetchWhiteCollarSequentialJobs({
        profile: adjustedProfile,
        page: options.page,
        limit: options.limit,
        type: 'profile',
        skipCount: options.skipCount,
      });
    }

    if (profile.roleCategory === 'blue_collar') {
      const adjustedProfile = {
        ...profile,
        roleIds: options.roleId ? [options.roleId] : profile.roleIds,
        cityId: options.cityId || profile.cityId,
        areaId: options.areaId || profile.areaId,
      };
      return this.fetchBlueCollarSequentialJobs({
        profile: adjustedProfile,
        page: options.page,
        limit: options.limit,
        type: 'profile',
        skipCount: options.skipCount,
      });
    }

    const roleIds = options.roleId ? [options.roleId] : profile.roleIds;
    const skillIds = profile.skillIds;
    const cityId = options.cityId || profile.cityId;
    const areaId = options.areaId || profile.areaId;

    const buildFilters = (useArea: boolean, useCity: boolean, useRolesSkills: boolean) => {
      const f: string[] = [];
      const p: any[] = [];

      if (profile.roleCategory) {
        f.push("COALESCE(role.category, 'white_collar') = ?");
        p.push(profile.roleCategory);
      }

      if (options.roleId) {
        f.push('job.role_id = ?');
        p.push(options.roleId);
      } else if (useRolesSkills) {
        if (roleIds.length > 0) {
          f.push(`job.role_id IN (${this.placeholders(roleIds.length)})`);
          p.push(...roleIds);
        } else if (skillIds.length > 0) {
          f.push(this.buildSkillExistsCondition(skillIds));
          p.push(...skillIds);
        }
      }

      if (cityId && useCity) {
        f.push('job.city_id = ?');
        p.push(cityId);
      }

      if (areaId && useArea) {
        f.push('job.area_id = ?');
        p.push(areaId);
      }

      return { filters: f, params: p };
    };

    const threshold = 5;
    let selectedFilters = buildFilters(true, true, true);
    let hasEnough = await this.hasAtLeastJobs(selectedFilters.filters, selectedFilters.params, threshold);

    if (!hasEnough && areaId) {
      const t2 = buildFilters(false, true, true);
      hasEnough = await this.hasAtLeastJobs(t2.filters, t2.params, threshold);
      if (hasEnough) {
        selectedFilters = t2;
      }
    }

    if (!hasEnough && cityId) {
      const t3 = buildFilters(false, false, true);
      hasEnough = await this.hasAtLeastJobs(t3.filters, t3.params, threshold);
      if (hasEnough) {
        selectedFilters = t3;
      }
    }

    if (!hasEnough && !options.roleId && (roleIds.length > 0 || skillIds.length > 0)) {
      const t4 = buildFilters(false, false, false);
      selectedFilters = t4;
    }

    const hasAreaFilter = selectedFilters.filters.some((f) => f.includes('area_id'));
    const hasCityFilter = selectedFilters.filters.some((f) => f.includes('city_id'));
    const hasRoleSkillFilter = selectedFilters.filters.some(
      (f) => f.includes('role_id') || f.includes('skill_id'),
    );

    const activeRoleIds = hasRoleSkillFilter ? roleIds : [];
    const activeSkillIds = hasRoleSkillFilter ? skillIds : [];
    const activeCityId = hasCityFilter ? cityId : null;
    const activeAreaId = hasAreaFilter ? areaId : null;

    return this.getOpenJobs({
      page: options.page,
      limit: options.limit,
      filters: selectedFilters.filters,
      params: selectedFilters.params,
      orderBy: this.buildMatchOrder(activeRoleIds, activeSkillIds, activeCityId, activeAreaId),
      orderParams: [
        ...activeRoleIds,
        ...activeSkillIds,
        ...(activeCityId ? [activeCityId] : []),
        ...(activeAreaId ? [activeAreaId] : []),
      ],
      profile,
      skipCount: options.skipCount,
    });
  }

  async getJobDetails(jobId: number, userId?: number) {
    const [jobRows, skillsRows, perksRows] = await Promise.all([
      this.dataSource.query(
        `
        SELECT
          job.id,
          job.title,
          job.description,
          job.salary_min,
          job.salary_max,
          job.created_at,
          job.shift,
          job.english_level,
          job.gender,
          job.other_recruiter_name,
          company.name AS company_name,
          company.logo AS company_logo,
          company.about AS company_description,
          company.location AS company_address,
          u.full_name AS recruiter_name,
          city.city_name AS city,
          state.state_name AS state,
          country.country_name AS country,
          area.area AS area_name,
          CONCAT_WS(', ', company.location, area.area, city.city_name, state.state_name, country.country_name) AS full_company_address,
          CONCAT_WS(', ', area.area, city.city_name, state.state_name, country.country_name) AS job_location,
          industry.name AS industry_name,
          role.name AS role_name,
          jobType.name AS employment_type,
          exp.level AS experience_level,
          exp.duration AS experience_duration,
          edu.name AS education_name,
          job.education_id,
          job.status,
          job.expires_at
        FROM job_posts job
        INNER JOIN companies company ON company.id = job.company_id
        LEFT JOIN users u ON u.id = company.user_id
        LEFT JOIN city city ON city.city_id = job.city_id
        LEFT JOIN state state ON state.state_id = job.state_id
        LEFT JOIN country country ON country.country_id = job.country_id
        LEFT JOIN area area ON area.id = job.area_id
        LEFT JOIN industries industry ON industry.id = job.industry_id
        LEFT JOIN roles role ON role.id = job.role_id
        LEFT JOIN job_type jobType ON jobType.id = job.job_type_id
        LEFT JOIN experiences exp ON exp.id = job.experience_id
        LEFT JOIN education_levels edu ON edu.id = job.education_id
        WHERE job.id = ?
        LIMIT 1
        `,
        [jobId],
      ),
      this.dataSource.query(
        `
        SELECT s.skill_name
        FROM job_post_skills jps
        INNER JOIN skills s ON s.id = jps.skill_id
        WHERE jps.job_post_id = ?
        `,
        [jobId],
      ),
      this.dataSource.query(
        `
        SELECT COALESCE(p.name, jp.custom_name) AS perk_name
        FROM job_perks jp
        LEFT JOIN perks p ON p.id = jp.perk_id
        WHERE jp.job_id = ?
        `,
        [jobId],
      ),
    ]);

    const job = jobRows[0];
    if (!job) {
      return { success: false, message: 'Job not found or is no longer open' };
    }

    let is_applied = false;
    let is_saved = false;

    if (userId) {
      const [appliedRows, savedRows] = await Promise.all([
        this.dataSource.query(
          `SELECT 1 FROM job_applications ja
           INNER JOIN seeker_profiles sp ON sp.id = ja.seeker_id
           WHERE ja.job_post_id = ? AND sp.user_id = ?`,
          [jobId, userId],
        ),
        this.dataSource.query(
          `SELECT 1 FROM job_saved js
           INNER JOIN seeker_profiles sp ON sp.id = js.seeker_id
           WHERE js.job_post_id = ? AND sp.user_id = ? AND js.is_deleted = 0`,
          [jobId, userId],
        ),
      ]);
      is_applied = appliedRows.length > 0;
      is_saved = savedRows.length > 0;
    }

    const isExpired =
      job.status !== 'open' ||
      (job.expires_at && new Date(job.expires_at) <= new Date());

    if (isExpired && !is_applied) {
      return { success: false, message: 'Job not found or is no longer open' };
    }

    let recruiterName = job.company_name;
    if (job.other_recruiter_name) {
      recruiterName = job.other_recruiter_name;
    } else if (job.recruiter_name) {
      recruiterName = job.recruiter_name;
    }

    const deduplicateAddress = (addressStr: string) => {
      if (!addressStr || addressStr === 'Not specified') return addressStr;
      const parts = addressStr
        .split(',')
        .map((p) => p.trim())
        .filter(Boolean);
      const seen = new Set<string>();
      return parts
        .filter((part) => {
          const lower = part.toLowerCase();
          if (seen.has(lower)) return false;
          seen.add(lower);
          return true;
        })
        .join(', ');
    };

    return {
      success: true,
      job: {
        id: job.id,
        title: job.title,
        is_applied,
        is_saved,
        is_expired: !!isExpired,
        isExpired: !!isExpired,
        status: isExpired ? 'expired' : job.status,
        company_name: job.company_name,
        company_logo: job.company_logo || null,
        location: deduplicateAddress(job.job_location || 'Not specified'),
        salary_min: job.salary_min,
        salary_max: job.salary_max,
        skills: skillsRows.map((s: any) => s.skill_name),
        posted_date: job.created_at,
        posted_ago: this.getTimeAgoString(job.created_at),
        description: job.description || '',

        role: {
          industry: job.industry_name || 'Not specified',
          category: job.role_name || 'Not specified',
          employment_type: job.employment_type || 'Full Time',
          shift: job.shift
            ? job.shift.charAt(0).toUpperCase() + job.shift.slice(1) + ' Shift'
            : 'Not specified',
        },
        benefits: perksRows.map((p: any) => p.perk_name),
        requirements: {
          experience: job.experience_duration
            ? `${job.experience_level} (${job.experience_duration})`
            : job.experience_level || 'Any Experience',
          education: {
            id: job.education_id ?? null,
            name: job.education_name ?? 'Any Education',
          },
          skills:
            skillsRows.map((s: any) => s.skill_name).join(', ') ||
            'Not specified',
          english_level: job.english_level || 'Any',
          gender: job.gender
            ? job.gender.charAt(0).toUpperCase() + job.gender.slice(1)
            : 'Any',
        },
        company_details: {
          name: job.company_name,
          address: deduplicateAddress(
            job.full_company_address || job.company_address || 'Not specified',
          ),
          description: job.company_description || '',
        },
        posted_by: {
          name: recruiterName,
          role: 'Company Representative',
        },
      },
    };
  }

  async getRecommendedJobs(options: {
    userId: number;
    page: number;
    limit: number;
    profileContext?: ProfileContext;
    skipCount?: boolean;
    city?: string;
    type?: string;
    industry?: string;
    education?: string;
  }) {
    const profile = options.profileContext
      ? options.profileContext
      : await this.getProfileContext(options.userId);

    const extraFilters: string[] = [];
    const extraParams: any[] = [];
    if (options.city) {
      const { ids } = this.parseCsvIds(options.city);
      const names = this.parseCsvStrings(options.city);
      if (ids.length > 0) {
        extraFilters.push(`job.city_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM city c WHERE c.city_id = job.city_id AND LOWER(c.city_name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }
    if (options.type) {
      const { ids } = this.parseCsvIds(options.type);
      const names = this.parseCsvStrings(options.type);
      if (ids.length > 0) {
        extraFilters.push(`job.job_type_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM job_type jt WHERE jt.id = job.job_type_id AND LOWER(jt.name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }
    if (options.industry) {
      const { ids } = this.parseCsvIds(options.industry);
      const names = this.parseCsvStrings(options.industry);
      if (ids.length > 0) {
        extraFilters.push(`job.industry_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM industries ind WHERE ind.id = job.industry_id AND LOWER(ind.name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }
    if (options.education) {
      const { ids } = this.parseCsvIds(options.education);
      const names = this.parseCsvStrings(options.education);
      if (ids.length > 0) {
        extraFilters.push(`job.education_id IN (${this.placeholders(ids.length)})`);
        extraParams.push(...ids);
      } else if (names.length > 0) {
        extraFilters.push(`EXISTS (SELECT 1 FROM education_levels edu WHERE edu.id = job.education_id AND LOWER(edu.name) IN (${names.map(() => 'LOWER(?)').join(', ')}))`);
        extraParams.push(...names);
      }
    }

    if (profile.roleCategory === 'white_collar') {
      const dashboardHalfLimit = 5;
      const customOffset = dashboardHalfLimit + (options.page - 1) * options.limit;
      return this.fetchWhiteCollarSequentialJobs({
        profile,
        page: options.page,
        limit: options.limit,
        type: 'recommended',
        customOffset,
        skipCount: options.skipCount,
        extraFilters,
        extraParams,
      });
    }

    if (profile.roleCategory === 'blue_collar') {
      const dashboardHalfLimit = 5;
      const customOffset = dashboardHalfLimit + (options.page - 1) * options.limit;
      return this.fetchBlueCollarSequentialJobs({
        profile,
        page: options.page,
        limit: options.limit,
        type: 'recommended',
        customOffset,
        skipCount: options.skipCount,
        extraFilters,
        extraParams,
      });
    }

    const buildFilters = (useCity: boolean, useRelevance: boolean) => {
      const f: string[] = [...extraFilters];
      const p: any[] = [...extraParams];

      if (profile.roleCategory) {
        f.push("COALESCE(role.category, 'white_collar') = ?");
        p.push(profile.roleCategory);
      }

      if (profile.cityId && useCity) {
        f.push('job.city_id = ?');
        p.push(profile.cityId);
      }

      if (useRelevance) {
        const relevanceConditions: string[] = [];
        const relevanceParams: any[] = [];

        if (profile.roleIds.length > 0) {
          relevanceConditions.push(
            `job.role_id IN (${this.placeholders(profile.roleIds.length)})`,
          );
          relevanceParams.push(...profile.roleIds);
        }

        if (profile.skillIds.length > 0) {
          relevanceConditions.push(`EXISTS (
            SELECT 1
            FROM job_post_skills jps
            WHERE jps.job_post_id = job.id
              AND jps.skill_id IN (${this.placeholders(profile.skillIds.length)})
          )`);
          relevanceParams.push(...profile.skillIds);
        }

        if (profile.areaId) {
          relevanceConditions.push('job.area_id = ?');
          relevanceParams.push(profile.areaId);
        }

        if (relevanceConditions.length > 0) {
          f.push(`(${relevanceConditions.join(' OR ')})`);
          p.push(...relevanceParams);
        }
      }

      return { filters: f, params: p };
    };

    const threshold = 5;
    const relevanceAvailable = !!(
      profile.roleIds.length > 0 ||
      profile.skillIds.length > 0 ||
      profile.areaId
    );

    let selectedFilters = buildFilters(true, true);
    let hasEnough = await this.hasAtLeastJobs(selectedFilters.filters, selectedFilters.params, threshold);

    if (!hasEnough && relevanceAvailable) {
      const t2 = buildFilters(true, false);
      hasEnough = await this.hasAtLeastJobs(t2.filters, t2.params, threshold);
      if (hasEnough) {
        selectedFilters = t2;
      }
    }

    if (!hasEnough && profile.cityId) {
      const t3 = buildFilters(false, true);
      hasEnough = await this.hasAtLeastJobs(t3.filters, t3.params, threshold);
      if (hasEnough) {
        selectedFilters = t3;
      }
    }

    if (!hasEnough) {
      selectedFilters = buildFilters(false, false);
    }

    const hasCityFilter = selectedFilters.filters.some((f) => f.includes('city_id'));
    const hasRelevanceFilter = selectedFilters.filters.some(
      (f) => f.includes('role_id') || f.includes('skill_id') || f.includes('area_id'),
    );

    const activeRoleIds = hasRelevanceFilter ? profile.roleIds : [];
    const activeSkillIds = hasRelevanceFilter ? profile.skillIds : [];
    const activeCityId = hasCityFilter ? profile.cityId : null;
    const activeAreaId = hasRelevanceFilter ? profile.areaId : null;

    const activeProfile: ProfileContext = {
      ...profile,
      roleIds: activeRoleIds,
      skillIds: activeSkillIds,
      cityId: activeCityId,
      areaId: activeAreaId,
    };

    return this.getOpenJobs({
      page: options.page,
      limit: options.limit,
      filters: selectedFilters.filters,
      params: selectedFilters.params,
      orderBy: this.buildRecommendationOrder(activeProfile),
      orderParams: [
        ...activeRoleIds,
        ...(activeCityId ? [activeCityId] : []),
        ...(activeAreaId ? [activeAreaId] : []),
        ...activeSkillIds,
      ],
      profile: activeProfile,
      skipCount: options.skipCount,
    });
  }

  async getTopCompanies(limit = 10) {
    const companies = await this.companiesService.findAllWithOpenPositions(limit);

    return {
      success: true,
      companies: companies.slice(0, limit).map((company) => ({
        company_id: company.id,
        company_name: company.name,
        logo: company.logo || null,
        location: company.location || null,
        featured: Number(company.featured) || 0,
        open_positions: Number(company.positions) || 0,
      })),
    };
  }

  async getCompanyJobs(options: {
    companyId: number;
    page: number;
    limit: number;
    status: 'open' | 'closed' | 'paused' | 'all' | string;
  }) {
    const { companyId, page, limit } = options;
    const normalizedStatus = (options.status || 'open')
      .toString()
      .toLowerCase();

    const filters: string[] = [];
    const params: any[] = [companyId];

    filters.push('job.company_id = ?');

    // company is still required; join query below does it.
    // base select already joins companies.

    if (normalizedStatus === 'open') {
      filters.push("job.status = 'open'");
      filters.push('(job.expires_at IS NULL OR job.expires_at > NOW())');
    } else if (
      normalizedStatus === 'closed' ||
      normalizedStatus === 'paused' ||
      normalizedStatus === 'inactive'
    ) {
      // For closed/paused we just filter by status.
      filters.push('job.status = ?');
      params.push(
        normalizedStatus === 'inactive' ? 'closed' : normalizedStatus,
      );
    } else if (normalizedStatus === 'all') {
      // No extra job.status filter.
    } else {
      // fallback: treat as explicit status
      filters.push('job.status = ?');
      params.push(normalizedStatus);
    }

    const where = filters.join(' AND ');
    const offset = (page - 1) * limit;

    // 1) fetch jobs
    const jobsRows = await this.dataSource.query(
      `
      ${this.baseJobSelectForSearch(true)}
      WHERE ${where}
        AND company.status = 1
        AND company.is_deleted = 0
      ORDER BY job.created_at DESC
      LIMIT ? OFFSET ?
      `,
      [...params, limit, offset],
    );

    // 2) total
    const countRows = await this.dataSource.query(
      `
      SELECT COUNT(DISTINCT job.id) AS total
      FROM job_posts job
      INNER JOIN companies company ON company.id = job.company_id
      LEFT JOIN roles role ON role.id = job.role_id
      LEFT JOIN city city ON city.city_id = job.city_id
      LEFT JOIN state state ON state.state_id = job.state_id
      LEFT JOIN country country ON country.country_id = job.country_id
      LEFT JOIN area area ON area.id = job.area_id
      LEFT JOIN industries industry ON industry.id = job.industry_id
      LEFT JOIN education_levels edu ON edu.id = job.education_id
      LEFT JOIN job_type jobType ON jobType.id = job.job_type_id
      LEFT JOIN experiences exp ON exp.id = job.experience_id
      WHERE ${where}
        AND company.status = 1
        AND company.is_deleted = 0
      `,
      params,
    );

    const total = Number(countRows?.[0]?.total) || 0;

    const jobIds = Array.from(
      new Set(
        (jobsRows || [])
          .map((j: any) => Number(j.id))
          .filter((id: number) => id > 0),
      ),
    );

    // 3) attach skills
    let skillsByJobId = new Map<number, string[]>();
    if (jobIds.length > 0) {
      const skillsRows = await this.dataSource.query(
        `
        SELECT
          jps.job_post_id AS job_post_id,
          s.skill_name AS skill_name
        FROM job_post_skills jps
        INNER JOIN skills s ON s.id = jps.skill_id
        WHERE jps.job_post_id IN (${this.placeholders(jobIds.length)})
        `,
        jobIds,
      );

      skillsByJobId = new Map<number, string[]>();
      for (const row of skillsRows as any[]) {
        const jobPostId = Number(row.job_post_id);
        if (!skillsByJobId.has(jobPostId)) skillsByJobId.set(jobPostId, []);
        const arr = skillsByJobId.get(jobPostId) as string[];
        if (row.skill_name && !arr.includes(row.skill_name))
          arr.push(row.skill_name);
      }
    }

    return {
      success: true,
      data: (jobsRows || []).map((j: any) =>
        this.serializeJobForSearchAndFilter(
          j,
          skillsByJobId.get(Number(j.id)) || [],
          false,
        ),
      ),
      pagination: {
        page,
        limit,
        total,
        total_pages: Math.ceil(total / limit),
      },
    };
  }

  private async searchRoles(query: string, limit: number) {
    const roles = await this.rolesService.search(query);
    return roles.slice(0, limit).map((role) => ({
      id: role.id,
      name: role.name,
      role_level_id: role.role_levels_id ?? role.roleLevelsId ?? null,
      category: role.category || null,
      type: 'role',
    }));
  }

  private async searchLocations(query: string, limit: number) {
    const locations = await this.cityService.search(query);
    return locations.slice(0, limit).map((location) => ({
      id: location.city_id,
      name: location.city_name,
      state_id: location.state_id,
      state_name: location.state_name || null,
      country_id: location.country_id || null,
      country_name: location.country_name || null,
      full_location: location.full_location || location.city_name,
      type: 'location',
    }));
  }

  private async searchCompanies(query: string, limit: number) {
    const rows = await this.dataSource.query(
      `
      SELECT
        company.id,
        company.name,
        company.logo,
        company.featured,
        CONCAT(city.city_name, ', ', state.state_name, ', ', country.country_name) AS location,
        COUNT(job.id) AS open_positions
      FROM companies company
      LEFT JOIN job_posts job
        ON job.company_id = company.id
        AND job.status = 'open'
        AND (job.expires_at IS NULL OR job.expires_at > NOW())
      LEFT JOIN city city ON city.city_id = company.city_id
      LEFT JOIN state state ON state.state_id = company.state_id
      LEFT JOIN country country ON country.country_id = company.country_id
      WHERE company.status = 1
        AND company.is_deleted = 0
        AND LOWER(company.name) LIKE LOWER(?)
      GROUP BY company.id
      ORDER BY open_positions DESC, company.featured DESC, company.name ASC
      LIMIT ?
      `,
      [`%${query}%`, limit],
    );

    return rows.map((company) => ({
      id: company.id,
      name: company.name,
      logo: company.logo || null,
      featured: Number(company.featured) || 0,
      location: company.location || null,
      open_positions: Number(company.open_positions) || 0,
      type: 'company',
    }));
  }

  private async searchJobs(options: SearchOptions) {
    const {
      query,
      skills,
      designation,
      company,
      location,
      education,
      jobType,
      city,
      industry,
      limit = 10,
    } = options;

    const filters: string[] = [];
    const params: any[] = [];

    if (query) {
      filters.push('LOWER(job.title) LIKE LOWER(?)');
      params.push(`%${query}%`);
    }

    if (designation) {
      filters.push('LOWER(role.name) LIKE LOWER(?)');
      params.push(`%${designation}%`);
    }

    if (company) {
      filters.push('LOWER(company.name) LIKE LOWER(?)');
      params.push(`%${company}%`);
    }

    if (city) {
      filters.push('LOWER(city.city_name) LIKE LOWER(?)');
      params.push(`%${city}%`);
    }

    if (location) {
      filters.push(
        '(LOWER(CONCAT(city.city_name, ", ", state.state_name, ", ", country.country_name)) LIKE LOWER(?) ' +
          'OR LOWER(city.city_name) LIKE LOWER(?) ' +
          'OR LOWER(state.state_name) LIKE LOWER(?) ' +
          'OR LOWER(country.country_name) LIKE LOWER(?))',
      );
      const like = `%${location}%`;
      params.push(like, like, like, like);
    }

    if (industry) {
      filters.push(`EXISTS (
        SELECT 1
        FROM industries ind
        WHERE ind.id = job.industry_id AND LOWER(ind.name) LIKE LOWER(?)
      )`);
      params.push(`%${industry}%`);
    }

    if (education) {
      filters.push(`EXISTS (
        SELECT 1
        FROM education_levels edu
        WHERE edu.id = job.education_id AND LOWER(edu.name) LIKE LOWER(?)
      )`);
      params.push(`%${education}%`);
    }

    if (jobType) {
      filters.push(`EXISTS (
        SELECT 1
        FROM job_type jt
        WHERE jt.id = job.job_type_id AND LOWER(jt.name) LIKE LOWER(?)
      )`);
      params.push(`%${jobType}%`);
    }

    if (skills) {
      const skillTokens = skills
        .split(',')
        .map((s) => s.trim())
        .filter(Boolean);

      for (const token of skillTokens) {
        filters.push(`EXISTS (
          SELECT 1
          FROM job_post_skills jps
          INNER JOIN skills sk ON sk.id = jps.skill_id
          WHERE jps.job_post_id = job.id
            AND LOWER(sk.skill_name) LIKE LOWER(?)
        )`);
        params.push(`%${token}%`);
      }
    }

    const where = filters.length > 0 ? `AND ${filters.join(' AND ')}` : '';

    const rows = await this.dataSource.query(
      `
    ${this.baseJobSelect()}
    WHERE job.status = 'open'
      AND company.status = 1
      AND company.is_deleted = 0
      ${where}
    LIMIT ?
    `,
      [...params, limit],
    );

    return rows.map((job) => this.serializeJob(job));
  }

  async getOpenJobs(options: {
    page: number;
    limit: number;
    filters: string[];
    params: any[];
    orderBy: string;
    orderParams: any[];
    profile: ProfileContext;
    knownCount?: number;
    skipCount?: boolean;
  }) {
    const offset = (options.page - 1) * options.limit;
    const where = [
      "job.status = 'open'",
      '(job.expires_at IS NULL OR job.expires_at > NOW())',
      'company.status = 1',
      'company.is_deleted = 0',
      ...options.filters,
    ].join(' AND ');

    const dataParams = [
      ...options.params,
      ...options.orderParams,
      options.limit,
      offset,
    ];
    const countParams = [...options.params];

    const jobsPromise = this.dataSource.query(
      `
      ${this.baseJobSelect()}
      WHERE ${where}
      ${options.orderBy}
      LIMIT ? OFFSET ?
      `,
      dataParams,
    );

    let total = options.knownCount;
    let jobs: any[];

    if (options.skipCount) {
      jobs = await jobsPromise;
      total = 0;
    } else if (total !== undefined) {
      jobs = await jobsPromise;
    } else {
      const [jobsResult, countRows] = await Promise.all([
        jobsPromise,
        this.dataSource.query(
          `
          SELECT COUNT(DISTINCT job.id) AS total
          FROM job_posts job
          INNER JOIN companies company ON company.id = job.company_id
          LEFT JOIN roles role ON role.id = job.role_id
          WHERE ${where}
          `,
          countParams,
        ),
      ]);
      jobs = jobsResult;
      total = Number(countRows?.[0]?.total) || 0;
    }

    // Attach skills for each job in this page (needed for recommended_jobs/cards)
    const jobIds = Array.from(
      new Set(
        (jobs || []).map((j: any) => Number(j.id)).filter((id) => id > 0),
      ),
    );

    let skillsByJobId = new Map<number, string[]>();
    if (jobIds.length > 0) {
      const skillsRows = await this.dataSource.query(
        `
        SELECT
          jps.job_post_id AS job_post_id,
          s.skill_name AS skill_name
        FROM job_post_skills jps
        INNER JOIN skills s ON s.id = jps.skill_id
        WHERE jps.job_post_id IN (${this.placeholders(jobIds.length)})
        `,
        jobIds,
      );

      skillsByJobId = new Map<number, string[]>();
      for (const row of skillsRows as any[]) {
        const jobId = Number(row.job_post_id);
        if (!skillsByJobId.has(jobId)) skillsByJobId.set(jobId, []);
        const arr = skillsByJobId.get(jobId) as string[];
        if (row.skill_name && !arr.includes(row.skill_name))
          arr.push(row.skill_name);
      }
    }

    return {
      success: true,
      profile_context: {
        seeker_id: options.profile.seekerId,
        preferred_city_id: options.profile.cityId,
        preferred_area_id: options.profile.areaId,
        role_ids: options.profile.roleIds,
        skill_ids: options.profile.skillIds,
      },
      data: (jobs || []).map((job: any) => {
        job.skills = skillsByJobId.get(Number(job.id)) || [];
        return this.serializeJob(job);
      }),
      pagination: {
        page: options.page,
        limit: options.limit,
        total,
        total_pages: Math.ceil(total / options.limit),
      },
    };
  }

  private async getProfileContext(userId: number): Promise<ProfileContext> {
    try {
      const [profileRow] = await this.dataSource.query(
        `
        SELECT
          u.id AS user_id,
          u.full_name,
          u.profile_picture,
          u.selected_role_category,
          sp.id AS profile_id,
          sp.profession_id,
          sp.preferred_work_city_id,
          sp.preferred_work_locality_area_id,
          sp.skills_ids,
          sa.communication_city_id,
          sa.communication_area_id
        FROM users u
        LEFT JOIN seeker_profiles sp ON sp.user_id = u.id
        LEFT JOIN seeker_address sa ON sa.seeker_profile_id = sp.id
        WHERE u.id = ?
        LIMIT 1
        `,
        [userId],
      );

      if (!profileRow) {
        return this.emptyProfileContext();
      }

      const roleCategory = profileRow.selected_role_category || 'white_collar';
      const profileId = profileRow.profile_id ? Number(profileRow.profile_id) : null;

      let cityId = null;
      let areaId = null;

      if (profileId) {
        if (roleCategory === 'blue_collar') {
          cityId = profileRow.preferred_work_city_id
            ? Number(profileRow.preferred_work_city_id)
            : null;
          areaId = profileRow.preferred_work_locality_area_id
            ? Number(profileRow.preferred_work_locality_area_id)
            : null;
        } else {
          cityId = profileRow.communication_city_id
            ? Number(profileRow.communication_city_id)
            : null;
          areaId = profileRow.communication_area_id
            ? Number(profileRow.communication_area_id)
            : null;
        }
      }

      const [roleRows, skillRows] = profileId
        ? await Promise.all([
            this.dataSource.query(
              `
              SELECT designation_id AS role_id
              FROM seeker_role_experience
              WHERE seeker_id = ? AND status = 1 AND designation_id IS NOT NULL
              `,
              [profileId],
            ),
            this.dataSource.query(
              `
              SELECT skill_id
              FROM seeker_skills
              WHERE seeker_id = ? AND skill_id IS NOT NULL
              `,
              [profileId],
            ),
          ])
        : [[], []];

      const roleIds = this.uniqueNumbers([
        profileRow.profession_id,
        ...roleRows.map((row) => row.role_id),
      ]);
      const profileSkillIds = this.parseIdArray(profileRow.skills_ids);

      // User card completion percentage calculation
      let profileCompletion = 0;
      let calculatedViaService = false;
      try {
        const completionRes = await this.dashboardService.getSeekerProfileCompletion(userId);
        if (completionRes && completionRes.success) {
          profileCompletion = completionRes.completion;
          calculatedViaService = true;
        }
      } catch (error) {
        this.logger.error(`Failed to calculate profile completion via DashboardService for userId ${userId}`, error);
      }

      if (!calculatedViaService) {
        const hasProfession = Number(profileRow.profession_id) > 0 ? 1 : 0;
        const hasCity = Number(profileRow.preferred_work_city_id) > 0 ? 1 : 0;
        const hasArea = Number(profileRow.preferred_work_locality_area_id) > 0 ? 1 : 0;
        
        let parsedSkills = [];
        if (profileRow.skills_ids) {
          if (Array.isArray(profileRow.skills_ids)) {
            parsedSkills = profileRow.skills_ids;
          } else if (typeof profileRow.skills_ids === 'string') {
            try {
              const parsed = JSON.parse(profileRow.skills_ids);
              if (Array.isArray(parsed)) parsedSkills = parsed;
            } catch {}
          }
        }
        const hasSkills = parsedSkills.length > 0 ? 1 : 0;
        const completed = hasProfession + hasCity + hasArea + hasSkills;
        profileCompletion = Math.round((completed / 4) * 100);
      }

      const name = profileRow.full_name || 'User';
      const profileImage = profileRow.profile_picture || 'https://cdn.jobvumi.com/users/default.png';

      return {
        seekerId: profileId,
        cityId,
        areaId,
        roleIds,
        skillIds: this.uniqueNumbers([
          ...skillRows.map((row) => row.skill_id),
          ...profileSkillIds,
        ]),
        roleCategory,
        userCard: {
          id: userId,
          name,
          profileCompletion,
          profileImage,
          notificationCount: 0,
        },
      };
    } catch (error) {
      this.logger.error(
        `Failed to build mobile dashboard profile context for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );
      return this.emptyProfileContext();
    }
  }

  private baseJobSelect() {
    return `
      SELECT DISTINCT
        job.id,
        job.title,
        job.company_id,
        job.role_id,
        job.city_id,
        job.job_type_id,
        job.experience_id,
        job.salary_min,
        job.salary_max,
        job.vacancies,
        job.featured,
        job.urgent_hiring,
        job.created_at,
        job.expires_at,
        company.name AS company_name,
        company.logo AS company_logo,
        role.name AS role_name,
        city.city_name AS city,
        state.state_name AS state,
        country.country_name AS country,
        CONCAT(city.city_name, ', ', state.state_name, ', ', country.country_name) AS location,

        jobType.name AS job_type_name,
        exp.level AS experience_level_name,
        exp.duration AS experience_duration
      FROM job_posts job

      INNER JOIN companies company ON company.id = job.company_id
      LEFT JOIN roles role ON role.id = job.role_id
      LEFT JOIN city city ON city.city_id = job.city_id
      LEFT JOIN state state ON state.state_id = job.state_id
      LEFT JOIN country country ON country.country_id = job.country_id

      LEFT JOIN job_type jobType ON jobType.id = job.job_type_id
      LEFT JOIN experiences exp ON exp.id = job.experience_id
    `;
  }

  private buildMatchOrder(
    roleIds: number[],
    skillIds: number[],
    cityId?: number | null,
    areaId?: number | null,
  ) {
    const scoreParts: string[] = [];

    if (roleIds.length > 0) {
      scoreParts.push(
        `CASE WHEN job.role_id IN (${this.placeholders(roleIds.length)}) THEN 50 ELSE 0 END`,
      );
    }

    if (skillIds.length > 0) {
      scoreParts.push(
        `CASE WHEN ${this.buildSkillExistsCondition(skillIds)} THEN 35 ELSE 0 END`,
      );
    }

    if (cityId) {
      scoreParts.push('CASE WHEN job.city_id = ? THEN 30 ELSE 0 END');
    }

    if (areaId) {
      scoreParts.push('CASE WHEN job.area_id = ? THEN 20 ELSE 0 END');
    }

    if (scoreParts.length === 0) {
      return `
        ORDER BY job.featured DESC, job.created_at DESC
      `;
    }

    const score = scoreParts.join(' + ');

    return `
      ORDER BY (${score}) DESC, job.featured DESC, job.created_at DESC
    `;
  }

  private buildRecommendationOrder(profile: ProfileContext) {
    const scoreParts: string[] = [];

    if (profile.roleIds.length > 0) {
      scoreParts.push(
        `CASE WHEN job.role_id IN (${this.placeholders(profile.roleIds.length)}) THEN 50 ELSE 0 END`,
      );
    }

    if (profile.cityId) {
      scoreParts.push('CASE WHEN job.city_id = ? THEN 30 ELSE 0 END');
    }

    if (profile.areaId) {
      scoreParts.push('CASE WHEN job.area_id = ? THEN 20 ELSE 0 END');
    }

    if (profile.skillIds.length > 0) {
      scoreParts.push(`
        CASE WHEN EXISTS (
          SELECT 1
          FROM job_post_skills jps
          WHERE jps.job_post_id = job.id
            AND jps.skill_id IN (${this.placeholders(profile.skillIds.length)})
        ) THEN 20 ELSE 0 END
      `);
    }

    if (scoreParts.length === 0) {
      return `
        ORDER BY job.featured DESC, job.created_at DESC
      `;
    }

    const score = scoreParts.join(' + ');

    return `
      ORDER BY (${score}) DESC, job.featured DESC, job.created_at DESC
    `;
  }

  private serializeJob(job: any) {
    return {
      job_id: job.id,
      title: job.title,
      company_id: job.company_id,
      company_name: job.company_name,
      company_logo: job.company_logo || null,
      role_id: job.role_id,
      role_name: job.role_name || null,
      city_id: job.city_id,
      city: job.city || null,
      state: job.state || null,
      country: job.country || null,
      location: job.location || null,
      salary_min: Number(job.salary_min) || 0,
      salary_max: Number(job.salary_max) || 0,
      vacancies: Number(job.vacancies) || 0,
      featured: Boolean(job.featured),
      urgent_hiring: Boolean(job.urgent_hiring),
      created_at: job.created_at,
      posted_ago: this.getTimeAgoString(job.created_at),
      expires_at: job.expires_at,

      // foreign keys -> display strings
      type: job.job_type_name || null,
      experience_level: job.experience_duration
        ? `${job.experience_level_name} (${job.experience_duration})`
        : job.experience_level_name || 'Any Experience',

      // Added for mobile dashboard to show skills on each recommended/profile job card.
      skills: Array.isArray(job.skills) ? job.skills : [],
    };
  }

  private emptyProfileContext(): ProfileContext {
    return {
      seekerId: null,
      cityId: null,
      areaId: null,
      roleIds: [],
      skillIds: [],
      roleCategory: null,
    };
  }

  private uniqueNumbers(values: any[]) {
    return [...new Set(values.map(Number).filter((value) => value > 0))];
  }

  private parseIdArray(value: any) {
    if (!value) {
      return [];
    }

    if (Array.isArray(value)) {
      return this.uniqueNumbers(value);
    }

    if (typeof value === 'string') {
      try {
        const parsed = JSON.parse(value);
        return Array.isArray(parsed) ? this.uniqueNumbers(parsed) : [];
      } catch {
        return [];
      }
    }

    return [];
  }

  private buildSkillExistsCondition(skillIds: number[]) {
    return `
      EXISTS (
        SELECT 1
        FROM job_post_skills jps
        WHERE jps.job_post_id = job.id
          AND jps.skill_id IN (${this.placeholders(skillIds.length)})
      )
    `;
  }

  private placeholders(count: number) {
    return Array.from({ length: count }, () => '?').join(', ');
  }

  private parseCsvIds(value?: string): IdParseResult {
    if (!value) return { ids: [] };
    const ids = value
      .split(',')
      .map((v) => Number(v.trim()))
      .filter((n) => Number.isFinite(n) && n > 0);
    return { ids: Array.from(new Set(ids)) };
  }

  private parseCsvStrings(value?: string): string[] {
    if (!value) return [];
    const items = value
      .split(',')
      .map((v) => v.trim())
      .filter(Boolean);
    // distinct case-insensitively
    const seen = new Set<string>();
    const out: string[] = [];
    for (const s of items) {
      const k = s.toLowerCase();
      if (seen.has(k)) continue;
      seen.add(k);
      out.push(s);
    }
    return out;
  }

  private baseJobSelectForSearch(_includeAreaJoin: boolean) {
    // _includeAreaJoin kept for backward compatibility
    // Must include all fields needed for serializeJobForSearchAndFilter
    return `
      SELECT DISTINCT
        job.id,
        job.title,
        job.company_id,
        job.role_id,
        job.city_id,
        job.education_id,
        job.industry_id,
        job.job_type_id,
        job.salary_min,
        job.salary_max,
        job.vacancies,
        job.featured,
        job.urgent_hiring,
        job.created_at,
        job.expires_at,
        company.name AS company_name,
        company.logo AS company_logo,
        role.name AS role_name,
        industry.name AS industry_name,
        edu.name AS education_name,
        jobType.name AS employment_type,
        city.city_name AS city_name,
        area.area AS area_name,
        state.state_name AS state_name,
        country.country_name AS country_name,
        exp.level AS experience_level_name,
        exp.duration AS experience_duration,

        CONCAT(
          COALESCE(city.city_name,'') ,
          CASE WHEN area.area IS NOT NULL THEN CONCAT(', ', area.area) ELSE '' END
        ) AS location_short,
        CONCAT(
          COALESCE(city.city_name,'') ,
          CASE WHEN area.area IS NOT NULL THEN CONCAT(', ', area.area) ELSE '' END,
          CASE WHEN state.state_name IS NOT NULL THEN CONCAT(', ', state.state_name) ELSE '' END,
          CASE WHEN country.country_name IS NOT NULL THEN CONCAT(', ', country.country_name) ELSE '' END
        ) AS location
      FROM job_posts job
      INNER JOIN companies company ON company.id = job.company_id
      LEFT JOIN roles role ON role.id = job.role_id
      LEFT JOIN industries industry ON industry.id = job.industry_id
      LEFT JOIN education_levels edu ON edu.id = job.education_id
      LEFT JOIN job_type jobType ON jobType.id = job.job_type_id
      LEFT JOIN experiences exp ON exp.id = job.experience_id
      LEFT JOIN city city ON city.city_id = job.city_id
      LEFT JOIN state state ON state.state_id = job.state_id
      LEFT JOIN country country ON country.country_id = job.country_id
      LEFT JOIN area area ON area.id = job.area_id
    `;
  }

  private serializeJobForSearchAndFilter(
    job: any,
    skillNames: string[] = [],
    isBookmarked = false,
  ) {
    return {
      job_id: job.id,
      title: job.title,
      company_name: job.company_name,
      company_logo: job.company_logo || null,
      job_type: job.employment_type ?? job.job_type_name ?? null,
      experience_level: job.experience_duration
        ? `${job.experience_level_name} (${job.experience_duration})`
        : job.experience_level_name || 'Any Experience',

      job_location: job.location ?? job.location_short ?? null,
      salary_min: Number(job.salary_min) || 0,
      salary_max: Number(job.salary_max) || 0,
      required_skills: skillNames,
      posted_ago: this.getTimeAgoString(job.created_at),
      // per request: skip isBookmark for now (return false)
      isBookmarked,
    };
  }

  private getTimeAgoString(createdAt: any): string {
    const createdDate = createdAt ? new Date(createdAt) : null;
    const now = new Date();

    if (!createdDate || Number.isNaN(createdDate.getTime())) {
      return '';
    }

    const diffMs = now.getTime() - createdDate.getTime();

    // Future dates shouldn't happen, but handle gracefully.
    if (diffMs < 0) return 'just now';

    const minutes = Math.floor(diffMs / (60 * 1000));
    if (minutes < 1) return 'just now';
    if (minutes < 60) return `${minutes} minutes ago`;

    const hours = Math.floor(minutes / 60);
    if (hours < 24) return `${hours} hours ago`;

    const days = Math.floor(hours / 24);
    if (days < 30) return `${days} days ago`;

    const months = Math.floor(days / 30);
    if (months < 12) return `${months} months ago`;

    const years = Math.floor(months / 12);
    return `${years} years ago`;
  }

  private formatExperienceLevel(
    levelName?: string,
    duration?: string | number,
  ): string | null {
    const name = typeof levelName === 'string' ? levelName.trim() : '';
    const dur =
      duration === null || duration === undefined
        ? ''
        : String(duration).trim();

    if (!name) return null;
    if (dur) return `${name} (${dur})`;
    return name;
  }

  private baseAreaJoinSql() {
    // baseJobSelectForSearch already includes area join.
    return ``;
  }

  // ============================================================================
  // Save/Unsave Job APIs (for mobile dashboard)
  // ============================================================================
  // Uses existing dashboard-service logic already implemented in
  // src/modules/dashboard/dashboard.service.ts

  async saveJobFromDashboard(userId: number, jobPostId: string) {
    // Delegate to the main DashboardService saveJob
    // Note: we import dynamically to avoid tight coupling in this file.
    const { DashboardService } = await import('../../modules/dashboard/dashboard.service');
    // eslint-disable-next-line @typescript-eslint/no-var-requires
    const dashboardService = (global as any).dashboardServiceInstance
      ? (global as any).dashboardServiceInstance
      : null;

    if (!dashboardService) {
      // Fallback: run the same logic via direct SQL (minimal implementation)
      const seekerProfileRows = await this.dataSource.query(
        `SELECT id FROM seeker_profiles WHERE user_id = ? LIMIT 1`,
        [userId],
      );
      const seekerProfile = seekerProfileRows?.[0];
      if (!seekerProfile?.id) {
        return { success: false, message: 'Seeker profile not found' };
      }
      const seekerId = Number(seekerProfile.id);

      const existingRows = await this.dataSource.query(
        `SELECT id, is_deleted FROM job_saved WHERE seeker_id = ? AND job_post_id = ? LIMIT 1`,
        [seekerId, Number(jobPostId)],
      );
      const existing = existingRows?.[0];
      if (existing && existing.is_deleted === 0) {
        return { success: false, message: 'Already bookmarked' };
      }
      if (existing && existing.is_deleted === 1) {
        await this.dataSource.query(
          `UPDATE job_saved SET is_deleted = 0 WHERE id = ?`,
          [existing.id],
        );
        return { success: true, message: 'Job re-bookmarked successfully' };
      }

      await this.dataSource.query(
        `INSERT INTO job_saved (seeker_id, job_post_id, is_deleted, saved_at, updated_at)
         VALUES (?, ?, 0, NOW(), NOW())`,
        [seekerId, Number(jobPostId)],
      );
      return { success: true, message: 'Job bookmarked successfully' };
    }

    return dashboardService.saveJob(String(userId), String(jobPostId));
  }

  async unsaveJobFromDashboard(userId: number, jobPostId: string) {
    const seekerProfileRows = await this.dataSource.query(
      `SELECT id FROM seeker_profiles WHERE user_id = ? LIMIT 1`,
      [userId],
    );
    const seekerProfile = seekerProfileRows?.[0];
    if (!seekerProfile?.id) {
      return { success: false, message: 'Seeker profile not found' };
    }

    const seekerId = Number(seekerProfile.id);

    await this.dataSource.query(
      `UPDATE job_saved
       SET is_deleted = 1, updated_at = NOW()
       WHERE seeker_id = ? AND job_post_id = ?`,
      [seekerId, Number(jobPostId)],
    );

    return { success: true, message: 'Job removed from bookmarks' };
  }

  async isJobSavedFromDashboard(userId: number, jobPostId: string) {
    const seekerProfileRows = await this.dataSource.query(
      `SELECT id FROM seeker_profiles WHERE user_id = ? LIMIT 1`,
      [userId],
    );
    const seekerProfile = seekerProfileRows?.[0];
    if (!seekerProfile?.id) return { isBookmarked: false };

    const seekerId = Number(seekerProfile.id);

    const existsRows = await this.dataSource.query(
      `SELECT 1 FROM job_saved
       WHERE seeker_id = ? AND job_post_id = ? AND is_deleted = 0
       LIMIT 1`,
      [seekerId, Number(jobPostId)],
    );

    return { isBookmarked: (existsRows?.length || 0) > 0 };
  }

  async fetchWhiteCollarSequentialJobs(options: {
    profile: ProfileContext;
    page: number;
    limit: number;
    type: 'profile' | 'recommended';
    excludeJobIds?: number[];
    customOffset?: number;
    skipCount?: boolean;
    extraFilters?: string[];
    extraParams?: any[];
  }) {
    const { profile, page, limit, type, excludeJobIds, customOffset } = options;
    const roleIds = profile.roleIds || [];
    const skillIds = profile.skillIds || [];
    const cityId = profile.cityId;

    const offset = customOffset !== undefined ? customOffset : (page - 1) * limit;
    const requiredCount = offset + limit;
    const jobsList: any[] = [];
    const jobIdsSet = new Set<number>();

    const initialExcludes = excludeJobIds || [];
    for (const id of initialExcludes) {
      jobIdsSet.add(id);
    }

    const baseWhere = [
      "job.status = 'open'",
      '(job.expires_at IS NULL OR job.expires_at > NOW())',
      'company.status = 1',
      'company.is_deleted = 0',
      "COALESCE(role.category, 'white_collar') = 'white_collar'",
      ...(options.extraFilters || []),
    ].join(' AND ');

    const addJobs = (newJobs: any[]) => {
      for (const job of newJobs) {
        if (!jobIdsSet.has(job.id)) {
          jobIdsSet.add(job.id);
          jobsList.push(job);
        }
      }
    };

    const buildExcludeCondition = () => {
      let excludeCondition = '';
      const excludeParams: any[] = [];
      if (jobIdsSet.size > 0) {
        const ids = Array.from(jobIdsSet);
        excludeCondition = `AND job.id NOT IN (${this.placeholders(ids.length)})`;
        excludeParams.push(...ids);
      }
      return { excludeCondition, excludeParams };
    };

    if (type === 'profile') {
      // Step 1: Match by Professional Title + Location (roleIds + cityId)
      if (roleIds.length > 0 && cityId) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const roleCityJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition} AND job.role_id IN (${this.placeholders(roleIds.length)}) AND job.city_id = ?
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...roleIds, cityId, requiredCount],
        );
        addJobs(roleCityJobs);
      }

      // Step 2: Match by Professional Title (roleIds)
      if (jobsList.length < requiredCount && roleIds.length > 0) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const roleJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition} AND job.role_id IN (${this.placeholders(roleIds.length)})
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...roleIds, requiredCount - jobsList.length],
        );
        addJobs(roleJobs);
      }
    } else {
      // type === 'recommended'
      // Step 1: Match by Skill + Location (skillIds + cityId)
      if (skillIds.length > 0 && cityId) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const skillCityJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition}
            AND ${this.buildSkillExistsCondition(skillIds)}
            AND job.city_id = ?
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...skillIds, cityId, requiredCount],
        );
        addJobs(skillCityJobs);
      }

      // Step 2: Match by Skill (skillIds)
      if (jobsList.length < requiredCount && skillIds.length > 0) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const skillJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition}
            AND ${this.buildSkillExistsCondition(skillIds)}
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...skillIds, requiredCount - jobsList.length],
        );
        addJobs(skillJobs);
      }
    }

    // Step 5/Fallback: Fallback Category Match (just white_collar category)
    if (jobsList.length < requiredCount) {
      const { excludeCondition, excludeParams } = buildExcludeCondition();
      const fallbackJobs = await this.dataSource.query(
        `
        ${this.baseJobSelect()}
        WHERE ${baseWhere} ${excludeCondition}
        ORDER BY job.featured DESC, job.created_at DESC
        LIMIT ?
        `,
        [...(options.extraParams || []), ...excludeParams, requiredCount - jobsList.length],
      );
      addJobs(fallbackJobs);
    }

    const paginatedJobs = jobsList.slice(offset, offset + limit);

    // Attach skills for each job in this page (needed for UI/cards)
    const pageJobIds = Array.from(
      new Set(
        paginatedJobs.map((j: any) => Number(j.id)).filter((id) => id > 0),
      ),
    );

    let skillsByJobId = new Map<number, string[]>();
    if (pageJobIds.length > 0) {
      const skillsRows = await this.dataSource.query(
        `
        SELECT
          jps.job_post_id AS job_post_id,
          s.skill_name AS skill_name
        FROM job_post_skills jps
        INNER JOIN skills s ON s.id = jps.skill_id
        WHERE jps.job_post_id IN (${this.placeholders(pageJobIds.length)})
        `,
        pageJobIds,
      );

      skillsByJobId = new Map<number, string[]>();
      for (const row of skillsRows as any[]) {
        const jobId = Number(row.job_post_id);
        if (!skillsByJobId.has(jobId)) skillsByJobId.set(jobId, []);
        const arr = skillsByJobId.get(jobId) as string[];
        if (row.skill_name && !arr.includes(row.skill_name))
          arr.push(row.skill_name);
      }
    }

    return {
      success: true,
      profile_context: {
        seeker_id: profile.seekerId,
        preferred_city_id: profile.cityId,
        preferred_area_id: profile.areaId,
        role_ids: profile.roleIds,
        skill_ids: profile.skillIds,
      },
      data: paginatedJobs.map((job: any) => {
        job.skills = skillsByJobId.get(Number(job.id)) || [];
        return this.serializeJob(job);
      }),
      pagination: {
        page,
        limit,
        total: jobsList.length,
        hasMore: jobsList.length > offset + limit,
      },
    };
  }

  async fetchBlueCollarSequentialJobs(options: {
    profile: ProfileContext;
    page: number;
    limit: number;
    type: 'profile' | 'recommended';
    excludeJobIds?: number[];
    customOffset?: number;
    skipCount?: boolean;
    extraFilters?: string[];
    extraParams?: any[];
  }) {
    const { profile, page, limit, type, excludeJobIds, customOffset } = options;
    const roleIds = profile.roleIds || [];
    const skillIds = profile.skillIds || [];
    const cityId = profile.cityId;
    const areaId = profile.areaId;

    const offset = customOffset !== undefined ? customOffset : (page - 1) * limit;
    const requiredCount = offset + limit;
    const jobsList: any[] = [];
    const jobIdsSet = new Set<number>();

    const initialExcludes = excludeJobIds || [];
    for (const id of initialExcludes) {
      jobIdsSet.add(id);
    }

    const baseWhere = [
      "job.status = 'open'",
      '(job.expires_at IS NULL OR job.expires_at > NOW())',
      'company.status = 1',
      'company.is_deleted = 0',
      "role.category = 'blue_collar'",
      ...(options.extraFilters || []),
    ].join(' AND ');

    const addJobs = (newJobs: any[]) => {
      for (const job of newJobs) {
        if (!jobIdsSet.has(job.id)) {
          jobIdsSet.add(job.id);
          jobsList.push(job);
        }
      }
    };

    const buildExcludeCondition = () => {
      let excludeCondition = '';
      const excludeParams: any[] = [];
      if (jobIdsSet.size > 0) {
        const ids = Array.from(jobIdsSet);
        excludeCondition = `AND job.id NOT IN (${this.placeholders(ids.length)})`;
        excludeParams.push(...ids);
      }
      return { excludeCondition, excludeParams };
    };

    if (type === 'profile') {
      // Step 1: Match by Professional Title + Locality Area (roleIds + areaId)
      if (roleIds.length > 0 && areaId) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const roleAreaJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition} AND job.role_id IN (${this.placeholders(roleIds.length)}) AND job.area_id = ?
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...roleIds, areaId, requiredCount],
        );
        addJobs(roleAreaJobs);
      }

      // Step 2: Match by Professional Title + City (roleIds + cityId)
      if (jobsList.length < requiredCount && roleIds.length > 0 && cityId) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const roleCityJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition} AND job.role_id IN (${this.placeholders(roleIds.length)}) AND job.city_id = ?
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...roleIds, cityId, requiredCount - jobsList.length],
        );
        addJobs(roleCityJobs);
      }

      // Step 3: Match by Professional Title (roleIds)
      if (jobsList.length < requiredCount && roleIds.length > 0) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const roleJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition} AND job.role_id IN (${this.placeholders(roleIds.length)})
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...roleIds, requiredCount - jobsList.length],
        );
        addJobs(roleJobs);
      }
    } else {
      // type === 'recommended'
      // Step 1: Match by Skill + City (skillIds + cityId)
      if (skillIds.length > 0 && cityId) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const skillCityJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition}
            AND ${this.buildSkillExistsCondition(skillIds)}
            AND job.city_id = ?
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...skillIds, cityId, requiredCount],
        );
        addJobs(skillCityJobs);
      }

      // Step 2: Match by Skill (skillIds)
      if (jobsList.length < requiredCount && skillIds.length > 0) {
        const { excludeCondition, excludeParams } = buildExcludeCondition();
        const skillJobs = await this.dataSource.query(
          `
          ${this.baseJobSelect()}
          WHERE ${baseWhere} ${excludeCondition}
            AND ${this.buildSkillExistsCondition(skillIds)}
          ORDER BY job.featured DESC, job.created_at DESC
          LIMIT ?
          `,
          [...(options.extraParams || []), ...excludeParams, ...skillIds, requiredCount - jobsList.length],
        );
        addJobs(skillJobs);
      }
    }

    // Fallback Category Match (strictly category = 'blue_collar')
    if (jobsList.length < requiredCount) {
      const { excludeCondition, excludeParams } = buildExcludeCondition();
      const fallbackJobs = await this.dataSource.query(
        `
        ${this.baseJobSelect()}
        WHERE ${baseWhere} ${excludeCondition}
        ORDER BY job.featured DESC, job.created_at DESC
        LIMIT ?
        `,
        [...(options.extraParams || []), ...excludeParams, requiredCount - jobsList.length],
      );
      addJobs(fallbackJobs);
    }

    const paginatedJobs = jobsList.slice(offset, offset + limit);

    // Attach skills for each job in this page (needed for UI/cards)
    const pageJobIds = Array.from(
      new Set(
        paginatedJobs.map((j: any) => Number(j.id)).filter((id) => id > 0),
      ),
    );

    let skillsByJobId = new Map<number, string[]>();
    if (pageJobIds.length > 0) {
      const skillsRows = await this.dataSource.query(
        `
        SELECT
          jps.job_post_id AS job_post_id,
          s.skill_name AS skill_name
        FROM job_post_skills jps
        INNER JOIN skills s ON s.id = jps.skill_id
        WHERE jps.job_post_id IN (${this.placeholders(pageJobIds.length)})
        `,
        pageJobIds,
      );

      skillsByJobId = new Map<number, string[]>();
      for (const row of skillsRows as any[]) {
        const jobId = Number(row.job_post_id);
        if (!skillsByJobId.has(jobId)) skillsByJobId.set(jobId, []);
        const arr = skillsByJobId.get(jobId) as string[];
        if (row.skill_name && !arr.includes(row.skill_name))
          arr.push(row.skill_name);
      }
    }

    return {
      success: true,
      profile_context: {
        seeker_id: profile.seekerId,
        preferred_city_id: profile.cityId,
        preferred_area_id: profile.areaId,
        role_ids: profile.roleIds,
        skill_ids: profile.skillIds,
      },
      data: paginatedJobs.map((job: any) => {
        job.skills = skillsByJobId.get(Number(job.id)) || [];
        return this.serializeJob(job);
      }),
      pagination: {
        page,
        limit,
        total: jobsList.length,
        hasMore: jobsList.length > offset + limit,
      },
    };
  }
}

