import {
  Injectable,
  NotFoundException,
  HttpException,
  HttpStatus,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { DataSource, Repository } from 'typeorm';
import { QueryRunner } from 'typeorm';
import { JobPosts } from './entities/job_posts.entity';
import { JobPostAiSettings } from '../job_post_ai_settings/entities/job_post_ai_settings.entity';
import { JobPostSkills } from '../job_post_skills/entities/job_post_skills.entity';
// import { JobMatchesService } from '../job_matches/job_matches.service';
import { DashboardService } from '../dashboard/dashboard.service';
import { CACHE_MANAGER } from '@nestjs/cache-manager';
import { Cache } from 'cache-manager';
import { Inject } from '@nestjs/common';
import { TagsService } from '../tags/tags.service';
import { JobsPostTagService } from '../jobs_post_tag/jobs_post_tag.service';
import { SkillsService } from '../skills/skills.service';
import { ActivityLogsService } from '../activity_logs/activity_logs.service';
import { PlansService } from '../plans/plans.service';
import { Plan } from '../plans/entities/plan.entity';
import { JobPackage } from '../plans/entities/job-package.entity';

import { WalletService } from '../wallet/wallet.service';

@Injectable()
export class JobPostsService {
  constructor(
    @InjectRepository(JobPosts)
    private readonly jobPostRepo: Repository<JobPosts>,
    @InjectRepository(JobPostSkills)
    private readonly jobPostSkillsRepository: Repository<JobPostSkills>,
    @InjectRepository(JobPostAiSettings)
    private readonly jobPostAiSettingsRepository: Repository<JobPostAiSettings>,
    private readonly dataSource: DataSource,
    private readonly dashboardService: DashboardService,
    @Inject(CACHE_MANAGER) private cacheManager: Cache,
    private readonly tagsService: TagsService,
    private readonly jobsPostTagService: JobsPostTagService,
    private readonly skillsService: SkillsService,
    private readonly activityLogsService: ActivityLogsService,
    private readonly plansService: PlansService,
    private readonly walletService: WalletService,
  ) {}

  // Fetch all active job posts (simple, no joins)
  async findAll(): Promise<JobPosts[]> {
    return this.jobPostRepo
      .createQueryBuilder('job')
      .where('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .getMany();
  }

  // Fetch featured job posts
  async findFeaturedJobs() {
    try {
      const result = await this.dataSource
        .createQueryBuilder()
        .select([
          'job.id AS id',
          'job.company_id AS company_id',
          'job.title AS title',
          'jobType.name AS job_type',
          'job.featured AS featured',
          'job.salary_min AS salary_min',
          'job.salary_max AS salary_max',
          'job.status AS status',
          'company.name AS company_name',
          'company.logo AS company_logo',
          'job.image_url AS image_url',
          'city.city_name AS city',
          'state.state_name AS state',
          'country.country_name AS country',
          'edu.name AS education',
          'exp.level AS experience',
          'exp.duration AS experience_duration',

          'roleLevel.name AS role_level',
          'jobLevel.name AS job_level',
          'job.expires_at AS expires_at',
          'job.created_at AS created_at',
          'job.created_at AS posted_date',
          'job.created_at AS posted_at',
          'exp.level AS experience_required',
        ])
        .from('job_posts', 'job')
        .innerJoin('companies', 'company', 'company.id = job.company_id')
        .leftJoin('city', 'city', 'city.city_id = job.city_id')
        .leftJoin('state', 'state', 'state.state_id = job.state_id')
        .leftJoin('country', 'country', 'country.country_id = job.country_id')
        .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
        .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
        .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
        .leftJoin(
          'role_levels',
          'roleLevel',
          'roleLevel.id = job.role_levels_id',
        )
        .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
        .where('job.status = :status', { status: 'open' })
        .andWhere('job.featured = :featured', { featured: true })
        .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())') // Filter out expired jobs
        .orderBy('job.created_at', 'DESC')
        .getRawMany();

      return result;
    } catch (error) {
      console.error('Error in findFeaturedJobs:', error);
      throw error;
    }
  }

  // Fetch a single job post with tags
  // Fetch a single job post with tags
  async findOne(id: number): Promise<any> {
    const job = await this.jobPostRepo.findOne({ where: { id } });
    if (!job) throw new NotFoundException('Job post not found');

    // Check for expiration
    // const isExpired = job.status !== 'open' || (job.expires_at && new Date(job.expires_at) < new Date());
    // if (isExpired) {
    //   throw new HttpException('Job has expired', HttpStatus.GONE);
    // }

    // Get tags for this job
    const tags = await this.getTagsForJob(id);

    // Get AI Interview settings for this job
    let aiSettings = null;
    try {
      aiSettings = await this.jobPostAiSettingsRepository.findOne({
        where: { job_post_id: id },
      });
    } catch (error) {
      console.error('Error fetching AI settings for job:', id, error);
      // Continue without AI settings if fetch fails
    }

    // Fetch role details manually since relations aren't set up
    let roleData = null;
    if (job.role_id) {
      try {
        const roles = await this.dataSource.query(
          'SELECT id, name, category FROM roles WHERE id = ?',
          [job.role_id],
        );
        if (roles && roles.length > 0) {
          roleData = roles[0];
        }
      } catch (error) {
        console.error('Error fetching role details for job:', id, error);
      }
    }

    // Fetch experience level name
    let experienceLevel = null;
    if (job.experience_id) {
      try {
        const expData = await this.dataSource.query(
          'SELECT level FROM experiences WHERE id = ?',
          [job.experience_id],
        );
        if (expData && expData.length > 0) {
          experienceLevel = expData[0].level;
        }
      } catch (error) {
        console.error('Error fetching experience level for job:', id, error);
      }
    }

    // Return job with tags and AI settings
    return {
      ...job,
      posted_date: job.created_at,
      posted_at: job.created_at,
      experience_required: experienceLevel || 'Fresher',
      tags: tags,
      // Include specific AI setting fields explicitly at the top level for easier frontend access if needed
      // or just pass the whole object
      ai_interview_enabled: aiSettings?.ai_enabled || false, // Ensure this flag is available
      ai_interview_duration: aiSettings?.ai_duration_minutes || 30,
      ai_validity_days: aiSettings?.ai_validity_days || 7,
      ai_settings: aiSettings,
      role: roleData
        ? {
            id: roleData.id,
            name: roleData.name,
            category: roleData.category,
          }
        : null,
      role_category: roleData?.category,
    };
  }

  /**
   * Builds a location lookup map from the database city + state tables.
   * Result is cached for 60 minutes to avoid repeated DB queries.
   */
  private async buildLocationLookup(): Promise<
    Record<string, 'city' | 'state'>
  > {
    const CACHE_KEY = 'location_lookup_map';
    const cached =
      await this.cacheManager.get<Record<string, 'city' | 'state'>>(CACHE_KEY);
    if (cached) return cached;

    const lookup: Record<string, 'city' | 'state'> = {};

    try {
      // Load all cities
      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';
        }
      }

      // Load all states
      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';
        }
      }
    } catch (err) {
      console.error(
        'buildLocationLookup: failed to load locations from DB',
        err,
      );
    }

    // Cache for 60 minutes (in milliseconds)
    await this.cacheManager.set(CACHE_KEY, lookup, 60 * 60 * 1000);
    return lookup;
  }

  async searchJobs(query: any) {
    // console.log('Backend searchJobs called with query:', query);
    const rawKeyword = query.keyword || '';
    const jobType = query.jobType || '';
    const company = query.company || '';
    const education = query.education || '';
    const experience = query.experience || '';
    const roleLevel = query.roleLevel || '';
    const jobLevel = query.jobLevel || '';
    const country = query.country || '';

    // Pagination parameters
    const page = parseInt(query.page) || 1;
    const limit = parseInt(query.limit) || 10;
    const offset = (page - 1) * limit;

    // ─── Smart Keyword Parsing ─────────────────────────────────────────────────
    // Strip common stop words naturally typed in job search phrases
    const STOP_WORDS = new Set([
      'jobs',
      'job',
      'in',
      'at',
      'for',
      'near',
      'the',
      'a',
      'an',
      'with',
      'and',
      'or',
      'on',
      'of',
      'to',
      'from',
      'by',
    ]);
    // Load city + state names from the database (cached 60 min)
    const KNOWN_LOCATIONS = await this.buildLocationLookup();

    let keyword = rawKeyword;
    let city = query.city || '';
    let state = query.state || '';

    // Split camelCase first (e.g. SeniorBackendDeveloper -> Senior Backend Developer)
    // Also handle hyphens, underscores, slashes as spaces (e.g. Senior-Backend-Developer)
    const spacedKeyword = rawKeyword
      .replace(/([a-z])([A-Z])/g, '$1 $2')
      .replace(/[-_\/]/g, ' ');

    // Only auto-parse if no explicit city/state already provided
    if (spacedKeyword && !city && !state) {
      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];
        // Try 2-word location name first (e.g. "new delhi", "west bengal")
        const twoWord = i + 1 < tokens.length ? `${tok} ${tokens[i + 1]}` : '';
        if (twoWord && KNOWN_LOCATIONS[twoWord]) {
          const locType = KNOWN_LOCATIONS[twoWord];
          if (locType === 'city' && !city) city = twoWord;
          else if (locType === 'state' && !state) state = twoWord;
          i++; // skip next token
        } else if (KNOWN_LOCATIONS[tok]) {
          const locType = KNOWN_LOCATIONS[tok];
          if (locType === 'city' && !city) city = tok;
          else if (locType === 'state' && !state) state = tok;
        } else if (!STOP_WORDS.has(tok) && tok.length > 0) {
          jobTokens.push(tok);
        }
      }
      // Rebuild clean keyword without location names & stop words
      keyword = jobTokens.join(' ');
    } else {
      keyword = spacedKeyword;
    }
    // ──────────────────────────────────────────────────────────────────────────

    let qb = this.dataSource
      .createQueryBuilder()
      .select([
        'job.id AS id',
        'job.company_id AS company_id',
        'job.title AS title',
        'job.description AS description',
        'company.name AS company_name',
        'company.logo AS company_logo',
        'job.image_url AS image_url',
        'jobType.name AS job_type',
        'job.salary_min AS salary_min',
        'job.salary_max AS salary_max',
        'job.status AS status',
        'job.featured AS featured',
        'city.city_name AS city',
        'state.state_name AS state',
        'country.country_name AS country',
        'edu.name AS education',
        'exp.level AS experience',
        'exp.level AS experience_required',
        'exp.duration AS experience_duration',
        'roleLevel.id AS category_id',
        'roleLevel.name AS category_name',
        'roleLevel.name AS role_level',
        'jobLevel.name AS job_level',
        'industry.name AS industry',
        'job.expires_at AS expires_at',
        'job.created_at AS created_at',
        'job.created_at AS posted_date',
        'job.created_at AS posted_at',
      ])
      .addSelect(
        "COALESCE(GROUP_CONCAT(DISTINCT skill.skill_name ORDER BY skill.skill_name SEPARATOR ', '), '')",
        'skill',
      )
      .from('job_posts', 'job')
      .innerJoin('companies', 'company', 'company.id = job.company_id')
      .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
      .leftJoin('city', 'city', 'city.city_id = job.city_id')
      .leftJoin('state', 'state', 'state.state_id = job.state_id')
      .leftJoin('country', 'country', 'country.country_id = job.country_id')
      .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
      .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
      .leftJoin('role_levels', 'roleLevel', 'roleLevel.id = job.role_levels_id')
      .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
      .leftJoin('industries', 'industry', 'industry.id = job.industry_id')
      .leftJoin('job_post_skills', 'jps', 'jps.job_post_id = job.id')
      .leftJoin('skills', 'skill', 'skill.id = jps.skill_id');

    // Advanced Search Parsing Logic
    if (keyword) {
      // 1-4. Trim, Lowercase, Remove special chars (e.g. react.js -> reactjs), Remove extra spaces
      const cleanSearch = keyword
        .trim()
        .toLowerCase()
        .replace(/[^\w\s-]/g, '') // Remove special characters except alphanumeric, spaces, and hyphens (to keep things like 'reactjs', 'node-js')
        .replace(/\s+/g, ' ');

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

      if (keywords.length > 0) {
        // 8. Build Search Query (WHERE clause)
        // Matches: 1️⃣ Title  2️⃣ Company  3️⃣ Skill  4️⃣ Description  5️⃣ Industry
        const whereConditions = keywords.map((kw: string, index: number) => {
          const paramName = `searchToken${index}`;
          qb.setParameter(paramName, `%${kw}%`);
          return `(
            LOWER(job.title) LIKE :${paramName} OR
            LOWER(company.name) LIKE :${paramName} OR
            LOWER(skill.skill_name) LIKE :${paramName} OR
            LOWER(job.description) LIKE :${paramName} OR
            LOWER(industry.name) LIKE :${paramName} OR
            LOWER(jobType.name) LIKE :${paramName}
          )`;
        });

        // Use OR to allow partial matches (e.g. searching "Back Office Executive" finds "Back Office")
        qb = qb.where(`(${whereConditions.join(' OR ')})`);

        // 9. Apply Ranking Logic (Exact -> Partial)
        // Add a relevance score column to order by. Exact matches get huge points. Partial keyword matches add up points.
        qb.setParameter('exactMatch', cleanSearch);

        let scoreSql = `
            CASE WHEN LOWER(job.title) = :exactMatch THEN 100
                 WHEN LOWER(job.title) LIKE :exactMatch THEN 80
                 WHEN LOWER(company.name) = :exactMatch THEN 60
                 ELSE 0
            END
        `;

        // Add points for each individual keyword matched
        // 1️⃣ Title Match (+10), 2️⃣ Company Match (+5), 3️⃣ Skill Match (+8), 4️⃣ Description Match (+3), 5️⃣ Industry Match (+6)
        keywords.forEach((kw: string, index: number) => {
          const paramName = `searchToken${index}`;
          scoreSql += ` + CASE WHEN LOWER(job.title) LIKE :${paramName} THEN 10 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(company.name) LIKE :${paramName} THEN 5 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(skill.skill_name) LIKE :${paramName} THEN 8 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(job.description) LIKE :${paramName} THEN 3 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(industry.name) LIKE :${paramName} THEN 6 ELSE 0 END `;
        });

        qb.addSelect(`(${scoreSql})`, 'relevance_score');
      }
    }

    // City filter
    if (city) {
      qb = qb.andWhere('LOWER(city.city_name) = LOWER(:city)', { city });
    }
    // Job type filter
    if (jobType) {
      qb = qb.andWhere('LOWER(jobType.name) = LOWER(:jobType)', { jobType });
    }
    // Company filter
    if (company) {
      qb = qb.andWhere('LOWER(company.name) = LOWER(:company)', { company });
    }
    // Education filter
    if (education) {
      qb = qb.andWhere('LOWER(edu.name) = LOWER(:education)', { education });
    }
    // Experience filter
    if (experience) {
      qb = qb.andWhere('LOWER(exp.level) = LOWER(:experience)', { experience });
    }
    // Role level filter
    if (roleLevel) {
      qb = qb.andWhere('LOWER(roleLevel.name) = LOWER(:roleLevel)', {
        roleLevel,
      });
    }
    // Job level filter
    if (jobLevel) {
      qb = qb.andWhere('LOWER(jobLevel.name) = LOWER(:jobLevel)', { jobLevel });
    }
    // State filter
    if (state) {
      qb = qb.andWhere('LOWER(state.state_name) = LOWER(:state)', { state });
    }
    // Country filter
    if (country) {
      qb = qb.andWhere('LOWER(country.country_name) = LOWER(:country)', {
        country,
      });
    }

    // Always filter for open jobs only
    if (keyword) {
      // If keyword search exists, add status filter with AND
      qb = qb.andWhere('job.status = :status', { status: 'open' });
      qb = qb.andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())'); // Filter out expired jobs
    } else {
      // If no keyword search, add status filter with WHERE
      qb = qb.where('job.status = :status', { status: 'open' });
      qb = qb.andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())'); // Filter out expired jobs
    }

    try {
      // Get total count for pagination
      const countQuery = qb.clone();
      const total = await countQuery.getCount();

      // Apply pagination and ordering
      if (keyword && keyword.trim().length > 0) {
        // Order by relevance score exact matches first, then featured, then newest
        qb.groupBy('job.id')
          .orderBy('relevance_score', 'DESC')
          .addOrderBy('job.featured', 'DESC')
          .addOrderBy('job.created_at', 'DESC');
      } else {
        qb.groupBy('job.id')
          .orderBy('job.featured', 'DESC')
          .addOrderBy('job.created_at', 'DESC');
      }

      const result = await qb.offset(offset).limit(limit).getRawMany();

      //console.log('Backend searchJobs result count:', result.length);
      //console.log('Backend searchJobs first result:', result[0]);

      return {
        data: result,
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      };
    } catch (error) {
      // console.error('Error in searchJobs:', error);
      return {
        success: false,
        message: 'Failed to fetch jobs',
        error: error.message,
      };
    }
  }

  // NEW: Server-side filtered jobs endpoint similar to companies
  async findFilteredJobs(filters: {
    search?: string;
    experience?: string;
    salary?: string;
    city?: string;
    state?: string;
    country?: string;
    jobFreshness?: string;
    jobType?: string;
    education?: string;
    jobLevel?: string;
    fresher?: boolean;
    jobsForWomen?: boolean;
    nightShift?: boolean;
    industry?: string;
    skill?: string;
    roleLevel?: number;
    role?: number;
    userId?: number;
    page?: number;
    limit?: number;
  }): Promise<{
    data: any[];
    total: number;
    page: number;
    limit: number;
    totalPages: number;
  }> {
    const page = filters.page || 1;
    const limit = filters.limit || 10;
    const offset = (page - 1) * limit;

    const queryBuilder = this.dataSource
      .createQueryBuilder()
      .select([
        'job.id AS id',
        'job.company_id AS company_id',
        'job.title AS title',
        'job.description AS description',
        'company.name AS company_name',
        'company.logo AS company_logo',
        'job.image_url AS image_url',
        'jobType.name AS job_type',
        'job.salary_min AS salary_min',
        'job.salary_max AS salary_max',
        'job.status AS status',
        'job.featured AS featured',
        'city.city_name AS city',
        'state.state_name AS state',
        'country.country_name AS country',
        'edu.name AS education',
        'exp.level AS experience',
        'exp.level AS experience_required',
        'exp.duration AS experience_duration',
        'roleLevel.id AS category_id',
        'roleLevel.name AS category_name',
        'roleLevel.name AS role_level',
        'jobLevel.name AS job_level',
        'job.expires_at AS expires_at',
        'job.created_at AS created_at',
        'job.created_at AS posted_date',
        'job.created_at AS posted_at',
      ])
      .addSelect(
        "COALESCE(GROUP_CONCAT(DISTINCT skill.skill_name ORDER BY skill.skill_name SEPARATOR ', '), '')",
        'skill',
      );

    if (filters.userId) {
      queryBuilder
        .leftJoin(
          'seeker_profiles',
          'sp_bookmark',
          'sp_bookmark.user_id = :userId',
          { userId: filters.userId },
        )
        .leftJoin(
          'job_saved',
          'js',
          'js.job_post_id = job.id AND js.seeker_id = sp_bookmark.id AND js.is_deleted = :isDeleted',
          { isDeleted: false },
        )
        .addSelect(
          'CASE WHEN js.id IS NOT NULL THEN 1 ELSE 0 END',
          'is_bookmark',
        );
    } else {
      queryBuilder.addSelect('0', 'is_bookmark');
    }

    queryBuilder
      .from('job_posts', 'job')
      .innerJoin('companies', 'company', 'company.id = job.company_id')
      .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
      .leftJoin('city', 'city', 'city.city_id = job.city_id')
      .leftJoin('state', 'state', 'state.state_id = job.state_id')
      .leftJoin('country', 'country', 'country.country_id = job.country_id')
      .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
      .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
      .leftJoin('role_levels', 'roleLevel', 'roleLevel.id = job.role_levels_id')
      .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
      .leftJoin('industries', 'industry', 'industry.id = job.industry_id')
      .leftJoin('job_post_skills', 'jps', 'jps.job_post_id = job.id')
      .leftJoin('skills', 'skill', 'skill.id = jps.skill_id')
      .where('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())');

    // ─── Smart Keyword Parsing (same logic as searchJobs) ─────────────────────
    let parsedSearch = filters.search || '';
    let parsedCity = filters.city || '';
    let parsedState = filters.state || '';

    // Split camelCase first (e.g. SeniorBackendDeveloper -> Senior Backend Developer)
    // Also handle hyphens, underscores, slashes as spaces (e.g. Senior-Backend-Developer)
    const spacedSearchF = parsedSearch
      .replace(/([a-z])([A-Z])/g, '$1 $2')
      .replace(/[-_\/]/g, ' ');

    if (spacedSearchF && !filters.city && !filters.state) {
      const STOP_WORDS_F = new Set([
        'jobs',
        'job',
        'in',
        'at',
        'for',
        'near',
        'the',
        'a',
        'an',
        'with',
        'and',
        'or',
        'on',
        'of',
        'to',
        'from',
        'by',
      ]);
      const KNOWN_LOC_F = await this.buildLocationLookup();

      const rawLowerF = spacedSearchF
        .toLowerCase()
        .trim()
        .replace(/[^\w\s]/g, '');
      const tokensF = rawLowerF.split(/\s+/);
      const jobToksF: string[] = [];
      let detCity = '';
      let detState = '';
      for (let i = 0; i < tokensF.length; i++) {
        const t = tokensF[i];
        const tw = i + 1 < tokensF.length ? `${t} ${tokensF[i + 1]}` : '';
        if (tw && KNOWN_LOC_F[tw]) {
          if (KNOWN_LOC_F[tw] === 'city' && !detCity) detCity = tw;
          else if (KNOWN_LOC_F[tw] === 'state' && !detState) detState = tw;
          i++;
        } else if (KNOWN_LOC_F[t]) {
          if (KNOWN_LOC_F[t] === 'city' && !detCity) detCity = t;
          else if (KNOWN_LOC_F[t] === 'state' && !detState) detState = t;
        } else if (!STOP_WORDS_F.has(t) && t.length > 0) {
          jobToksF.push(t);
        }
      }
      parsedSearch = jobToksF.join(' ');
      parsedCity = detCity || parsedCity;
      parsedState = detState || parsedState;
    } else {
      parsedSearch = spacedSearchF;
    }
    // ──────────────────────────────────────────────────────────────────────────

    // Apply search filter — uses parsedSearch (stop words & city stripped)
    if (parsedSearch) {
      const cleanSearch = parsedSearch
        .trim()
        .toLowerCase()
        .replace(/[^\w\s-]/g, '')
        .replace(/\s+/g, ' ');

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

      if (keywords.length > 0) {
        // 1️⃣ Title  2️⃣ Company  3️⃣ Skill  4️⃣ Description  5️⃣ Industry
        const whereConditions = keywords.map((kw: string, index: number) => {
          const paramName = `searchToken${index}`;
          queryBuilder.setParameter(paramName, `%${kw}%`);
          return `(
            LOWER(job.title) LIKE :${paramName} OR
            LOWER(company.name) LIKE :${paramName} OR
            LOWER(skill.skill_name) LIKE :${paramName} OR
            LOWER(job.description) LIKE :${paramName} OR
            LOWER(industry.name) LIKE :${paramName} OR
            LOWER(city.city_name) LIKE :${paramName} OR
            LOWER(state.state_name) LIKE :${paramName} OR
            LOWER(country.country_name) LIKE :${paramName}
          )`;
        });

        // Use OR for broader matching
        queryBuilder.andWhere(`(${whereConditions.join(' OR ')})`);

        queryBuilder.setParameter('exactMatch', cleanSearch);

        let scoreSql = `
            CASE WHEN LOWER(job.title) = :exactMatch THEN 100
                 WHEN LOWER(job.title) LIKE CONCAT('%', :exactMatch, '%') THEN 80
                 WHEN LOWER(company.name) = :exactMatch THEN 60
                 ELSE 0
            END
        `;

        // 1️⃣ Title(+10) 2️⃣ Company(+5) 3️⃣ Skill(+8) 4️⃣ Description(+3) 5️⃣ Industry(+6)
        keywords.forEach((kw: string, index: number) => {
          const paramName = `searchToken${index}`;
          scoreSql += ` + CASE WHEN LOWER(job.title) LIKE :${paramName} THEN 10 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(company.name) LIKE :${paramName} THEN 5 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(skill.skill_name) LIKE :${paramName} THEN 8 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(job.description) LIKE :${paramName} THEN 3 ELSE 0 END `;
          scoreSql += ` + CASE WHEN LOWER(industry.name) LIKE :${paramName} THEN 6 ELSE 0 END `;
        });

        queryBuilder.addSelect(`(${scoreSql})`, 'relevance_score');
      }
    }

    // Apply experience filter
    if (filters.experience) {
      queryBuilder.andWhere('LOWER(exp.level) = LOWER(:experience)', {
        experience: filters.experience,
      });
    }

    // Apply salary filter
    if (filters.salary) {
      const [min, max] = filters.salary.split('-').map(Number);
      queryBuilder.andWhere(
        'job.salary_max >= :minSalary AND job.salary_min <= :maxSalary',
        {
          minSalary: min,
          maxSalary: max,
        },
      );
    }

    // Apply location filters
    if (filters.country) {
      queryBuilder.andWhere('LOWER(country.country_name) = LOWER(:country)', {
        country: filters.country,
      });
    }

    if (parsedState) {
      queryBuilder.andWhere('LOWER(state.state_name) = LOWER(:state)', {
        state: parsedState,
      });
    }

    if (parsedCity) {
      queryBuilder.andWhere('LOWER(city.city_name) = LOWER(:city)', {
        city: parsedCity,
      });
    }

    // Apply job type filter
    if (filters.jobType) {
      if (filters.jobType === 'Work From Home') {
        // Filter by job_type_id = 8 for Work From Home
        queryBuilder.andWhere('job.job_type_id = :jobTypeId', {
          jobTypeId: 8,
        });
      } else if (filters.jobType === 'Part Time') {
        // Filter by job_type_id = 2 for Part Time
        queryBuilder.andWhere('job.job_type_id = :jobTypeId', {
          jobTypeId: 2,
        });
      } else if (filters.jobType === 'Full Time') {
        // Filter by job_type_id = 1 for Full Time
        queryBuilder.andWhere('job.job_type_id = :jobTypeId', {
          jobTypeId: 1,
        });
      } else {
        // Filter by job type name for other types
        queryBuilder.andWhere('LOWER(jobType.name) = LOWER(:jobType)', {
          jobType: filters.jobType,
        });
      }
    }

    // Apply education filter
    if (filters.education) {
      // Check if education is a number (ID) or string (name)
      const educationId = parseInt(filters.education);
      if (!isNaN(educationId)) {
        // Filter by education_id
        queryBuilder.andWhere('job.education_id = :educationId', {
          educationId: educationId,
        });
      } else {
        // Filter by education name (backward compatibility)
        queryBuilder.andWhere('LOWER(edu.name) = LOWER(:education)', {
          education: filters.education,
        });
      }
    }

    // Apply job level filter
    if (filters.jobLevel) {
      queryBuilder.andWhere('LOWER(jobLevel.name) = LOWER(:jobLevel)', {
        jobLevel: filters.jobLevel,
      });
    }

    // Apply fresher filter (Entry Level and Junior jobs)
    if (filters.fresher) {
      queryBuilder.andWhere('job.experience_id IN (:...fresherLevels)', {
        fresherLevels: [1], // Entry Level (4) and Junior (5)
      });
    }

    // Apply jobs for women filter
    if (filters.jobsForWomen) {
      queryBuilder.andWhere('job.gender = :gender', {
        gender: 'female',
      });
    }

    // Apply night shift filter
    if (filters.nightShift) {
      queryBuilder.andWhere('job.shift = :shift', {
        shift: 'night',
      });
    }

    // Apply job freshness filter
    if (filters.jobFreshness) {
      const days = parseInt(filters.jobFreshness);
      const cutoffDate = new Date();
      cutoffDate.setDate(cutoffDate.getDate() - days);
      queryBuilder.andWhere('job.created_at >= :cutoffDate', {
        cutoffDate: cutoffDate.toISOString(),
      });
    }

    // Apply role level filter (for category filtering)
    if (filters.roleLevel) {
      queryBuilder.andWhere('job.role_levels_id = :roleLevel', {
        roleLevel: filters.roleLevel,
      });
    }

    // Apply role filter (for vacancy filtering)
    if (filters.role) {
      queryBuilder.andWhere('job.role_id = :role', {
        role: filters.role,
      });
    }

    // Apply industry filter
    if (filters.industry) {
      // Check if industry is a number (ID) or string (name)
      const industryId = parseInt(filters.industry);
      if (!isNaN(industryId)) {
        // Filter by industry_id
        queryBuilder.andWhere('job.industry_id = :industryId', {
          industryId: industryId,
        });
      } else {
        // Filter by industry name (backward compatibility)
        queryBuilder.andWhere('LOWER(industry.name) = LOWER(:industry)', {
          industry: filters.industry,
        });
      }
    }

    if (filters.skill) {
      const skillId = parseInt(filters.skill);
      if (!isNaN(skillId)) {
        queryBuilder.andWhere('jps.skill_id = :skillId', { skillId });
      } else {
        queryBuilder.andWhere(
          'LOWER(skill.skill_name) LIKE LOWER(:skillName)',
          {
            skillName: `%${filters.skill}%`,
          },
        );
      }
    }

    // Get total count before applying pagination
    const totalCountQuery = this.dataSource
      .createQueryBuilder()
      .select('COUNT(DISTINCT job.id)', 'total')
      .from('job_posts', 'job')
      .leftJoin('companies', 'company', 'company.id = job.company_id')
      .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
      .leftJoin('city', 'city', 'city.city_id = job.city_id')
      .leftJoin('state', 'state', 'state.state_id = job.state_id')
      .leftJoin('country', 'country', 'country.country_id = job.country_id')
      .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
      .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
      .leftJoin('role_levels', 'roleLevel', 'roleLevel.id = job.role_levels_id')
      .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
      .leftJoin('industries', 'industry', 'industry.id = job.industry_id')
      .leftJoin('job_post_skills', 'jps', 'jps.job_post_id = job.id')
      .leftJoin('skills', 'skill', 'skill.id = jps.skill_id')
      .where('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())');

    // Apply the same filters to the count query
    if (parsedSearch) {
      const cleanSearch = parsedSearch
        .trim()
        .toLowerCase()
        .replace(/[^\w\s-]/g, '')
        .replace(/\s+/g, ' ');

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

      if (keywords.length > 0) {
        const whereConditions = keywords.map((kw: string, index: number) => {
          const paramName = `searchTokenCount${index}`;
          totalCountQuery.setParameter(paramName, `%${kw}%`);
          return `(
            LOWER(job.title) LIKE :${paramName} OR
            LOWER(company.name) LIKE :${paramName} OR
            LOWER(skill.skill_name) LIKE :${paramName} OR
            LOWER(job.description) LIKE :${paramName} OR
            LOWER(industry.name) LIKE :${paramName} OR
            LOWER(city.city_name) LIKE :${paramName} OR
            LOWER(state.state_name) LIKE :${paramName} OR
            LOWER(country.country_name) LIKE :${paramName}
          )`;
        });

        totalCountQuery.andWhere(`(${whereConditions.join(' OR ')})`);
      }
    }

    if (filters.experience) {
      totalCountQuery.andWhere('LOWER(exp.level) = LOWER(:experience)', {
        experience: filters.experience,
      });
    }

    if (filters.salary) {
      const [min, max] = filters.salary.split('-').map(Number);
      totalCountQuery.andWhere(
        'job.salary_max >= :minSalary AND job.salary_min <= :maxSalary',
        {
          minSalary: min,
          maxSalary: max,
        },
      );
    }

    if (filters.country) {
      totalCountQuery.andWhere(
        'LOWER(country.country_name) = LOWER(:country)',
        {
          country: filters.country,
        },
      );
    }

    if (parsedState) {
      totalCountQuery.andWhere('LOWER(state.state_name) = LOWER(:state)', {
        state: parsedState,
      });
    }

    if (parsedCity) {
      totalCountQuery.andWhere('LOWER(city.city_name) = LOWER(:city)', {
        city: parsedCity,
      });
    }

    if (filters.jobType) {
      if (filters.jobType === 'Work From Home') {
        // Filter by job_type_id = 8 for Work From Home
        totalCountQuery.andWhere('job.job_type_id = :jobTypeId', {
          jobTypeId: 8,
        });
      } else if (filters.jobType === 'Part Time') {
        // Filter by job_type_id = 2 for Part Time
        totalCountQuery.andWhere('job.job_type_id = :jobTypeId', {
          jobTypeId: 2,
        });
      } else if (filters.jobType === 'Full Time') {
        // Filter by job_type_id = 1 for Full Time
        totalCountQuery.andWhere('job.job_type_id = :jobTypeId', {
          jobTypeId: 1,
        });
      } else {
        // Filter by job type name for other types
        totalCountQuery.andWhere('LOWER(jobType.name) = LOWER(:jobType)', {
          jobType: filters.jobType,
        });
      }
    }

    if (filters.education) {
      // Check if education is a number (ID) or string (name)
      const educationId = parseInt(filters.education);
      if (!isNaN(educationId)) {
        // Filter by education_id
        totalCountQuery.andWhere('job.education_id = :educationId', {
          educationId: educationId,
        });
      } else {
        // Filter by education name (backward compatibility)
        totalCountQuery.andWhere('LOWER(edu.name) = LOWER(:education)', {
          education: filters.education,
        });
      }
    }

    if (filters.jobLevel) {
      totalCountQuery.andWhere('LOWER(jobLevel.name) = LOWER(:jobLevel)', {
        jobLevel: filters.jobLevel,
      });
    }

    if (filters.fresher) {
      totalCountQuery.andWhere('job.experience_id IN (:...fresherLevels)', {
        fresherLevels: [1], // Entry Level (4) and Junior (5)
      });
    }

    if (filters.jobsForWomen) {
      totalCountQuery.andWhere('job.gender = :gender', {
        gender: 'female',
      });
    }

    if (filters.nightShift) {
      totalCountQuery.andWhere('job.shift = :shift', {
        shift: 'night',
      });
    }

    if (filters.jobFreshness) {
      const days = parseInt(filters.jobFreshness);
      const cutoffDate = new Date();
      cutoffDate.setDate(cutoffDate.getDate() - days);
      totalCountQuery.andWhere('job.created_at >= :cutoffDate', {
        cutoffDate: cutoffDate.toISOString(),
      });
    }

    if (filters.roleLevel) {
      totalCountQuery.andWhere('job.role_levels_id = :roleLevel', {
        roleLevel: filters.roleLevel,
      });
    }

    if (filters.role) {
      totalCountQuery.andWhere('job.role_id = :role', {
        role: filters.role,
      });
    }

    if (filters.industry) {
      // Check if industry is a number (ID) or string (name)
      const industryId = parseInt(filters.industry);
      if (!isNaN(industryId)) {
        // Filter by industry_id
        totalCountQuery.andWhere('job.industry_id = :industryId', {
          industryId: industryId,
        });
      } else {
        // Filter by industry name (backward compatibility)
        totalCountQuery.andWhere('LOWER(industry.name) = LOWER(:industry)', {
          industry: filters.industry,
        });
      }
    }

    if (filters.skill) {
      const skillId = parseInt(filters.skill);
      if (!isNaN(skillId)) {
        totalCountQuery.andWhere('jps.skill_id = :skillId', { skillId });
      } else {
        totalCountQuery.andWhere(
          'LOWER(skill.skill_name) LIKE LOWER(:skillName)',
          {
            skillName: `%${filters.skill}%`,
          },
        );
      }
    }

    const totalCountResult = await totalCountQuery.getRawOne();
    const total = totalCountResult ? parseInt(totalCountResult.total) : 0;

    // Apply pagination and ordering
    if (parsedSearch && parsedSearch.trim().length > 0) {
      queryBuilder
        .groupBy('job.id')
        .orderBy('relevance_score', 'DESC')
        .addOrderBy('job.featured', 'DESC')
        .addOrderBy('job.created_at', 'DESC');
    } else {
      queryBuilder
        .groupBy('job.id')
        .orderBy('job.featured', 'DESC')
        .addOrderBy('job.created_at', 'DESC');
    }

    // Apply pagination
    queryBuilder.offset(offset).limit(limit);

    const result = await queryBuilder.getRawMany();
    // console.log('findFilteredJobs sample row:', result[0]);
    const totalPages = Math.ceil(total / limit);

    return {
      data: result,
      total,
      page,
      limit,
      totalPages,
    };
  }

  // NEW: Get filter options for jobs
  async getJobFilterOptions(): Promise<any> {
    try {
      // Get experience levels
      const experiences = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'level'])
        .from('experiences', 'e')
        .orderBy('e.level', 'ASC')
        .getRawMany();

      // Get education levels
      const educationLevels = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('education_levels', 'el')
        .orderBy('el.name', 'ASC')
        .getRawMany();

      // Get job types
      const jobTypes = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('job_type', 'jt')
        .orderBy('jt.name', 'ASC')
        .getRawMany();

      // Get job levels
      const jobLevels = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('job_level', 'jl')
        .orderBy('jl.name', 'ASC')
        .getRawMany();

      // Get countries
      const countries = await this.dataSource
        .createQueryBuilder()
        .select(['country_id as id', 'country_name as name'])
        .from('country', 'c')
        .where('c.status = :status', { status: 1 })
        .orderBy('c.country_name', 'ASC')
        .getRawMany();

      // Get states
      const states = await this.dataSource
        .createQueryBuilder()
        .select([
          's.state_id as id',
          's.state_name as name',
          's.country_id as countryId',
          'c.country_name as countryName',
        ])
        .from('state', 's')
        .leftJoin('country', 'c', 'c.country_id = s.country_id')
        .where('s.status = :status', { status: 1 })
        .orderBy('s.state_name', 'ASC')
        .getRawMany();

      // Get cities
      const cities = await this.dataSource
        .createQueryBuilder()
        .select([
          'c.city_id as id',
          'c.city_name as name',
          'c.state_id as stateId',
          's.state_name as stateName',
        ])
        .from('city', 'c')
        .leftJoin('state', 's', 's.state_id = c.state_id')
        .where('c.status = :status', { status: 1 })
        .orderBy('c.city_name', 'ASC')
        .getRawMany();

      return {
        experiences: experiences.map((e) => ({ id: e.id, name: e.level })),
        educationLevels: educationLevels.map((el) => ({
          id: el.id,
          name: el.name,
        })),
        jobTypes: jobTypes.map((jt) => ({ id: jt.id, name: jt.name })),
        jobLevels: jobLevels.map((jl) => ({ id: jl.id, name: jl.name })),
        countries: countries.map((c) => ({ id: c.id, name: c.name })),
        states: states.map((s) => ({
          id: s.id,
          name: s.name,
          countryId: s.countryId,
          country_name: s.countryName, // Compatibility with CategoryJobsClient
        })),
        cities: cities.map((c) => ({
          id: c.id,
          name: c.name,
          stateId: c.stateId,
          state_name: c.stateName, // Compatibility with CategoryJobsClient
        })),
      };
    } catch (error) {
      console.error('Error fetching job filter options:', error);
      throw error;
    }
  }

  // Create a new job post using query builder or transaction manager
  async createjob(
    data: any,
    options?: { creditsUsed?: number },
    queryRunner?: QueryRunner,
  ): Promise<JobPosts> {
    console.log('DEBUG: createjob called with:', {
      dataAssociateId: data.associate_id,
      dataUserId: data.userId,
      options,
    });

    const manager = queryRunner ? queryRunner.manager : this.dataSource.manager;

    // START TRANSACTIONAL INSERT
    // We use the manager to get the repository or build a query to ensure it's part of the transaction

    const result = await manager
      .createQueryBuilder()
      .insert()
      .into(JobPosts) // Use Entity class or table name 'job_posts'
      .values({
        company_id: data.company_id,
        associate_id: data.associate_id || null,
        title: data.title || null,
        address: data.address, // Added missing address field
        description: data.description,
        salary_min: data.salary_min || 0,
        salary_max: data.salary_max || 0,
        job_type_id: data.job_type_id || null,
        work_mode: data.work_mode || null,
        experience_id: data.experience_id || null,
        education_id: data.education_id || null,
        state_id: data.state_id || null,
        city_id: data.city_id || null,
        country_id: data.country_id || null,
        status: data.status || 'pending',

        // Additional fields mapped from data
        requirements: data.requirements || null,
        benefits: data.benefits || null,
        featured: data.featured || false,
        desirable: data.desirable || null,
        job_level_id: data.job_level_id || null,
        image_url: data.image_url || null,
        pincode: data.pincode ? Number(data.pincode) : null,
        area_id: data.area_id || null,
        vacancies: data.vacancies || 0,
        expires_at: data.expires_at || null,
        role_levels_id: data.role_levels_id || null,
        role_id: data.role_id || null,
        industry_id: data.industry_id || null,
        urgent_hiring: data.urgent_hiring || false,
        shift: data.shift || 'day',
        english_level: data.english_level || null,
        gender: data.gender || null,

        // Interview Section
        walk_in_interview: data.walk_in_interview || 'no',
        walkin_address: data.walkin_address || null,
        walkin_start_date: data.walkin_start_date || null,
        walkin_end_date: data.walkin_end_date || null,
        walkin_start_time: data.walkin_start_time || null,
        walkin_end_time: data.walkin_end_time || null,
        walkin_instructions: data.walkin_instructions || null,

        // Communication Preferences
        communication_preference: data.communication_preference || null,
        other_recruiter_name: data.other_recruiter_name || null,
        other_recruiter_whatsapp: data.other_recruiter_whatsapp || null,
        other_recruiter_email: data.other_recruiter_email || null,

        // Notification Preferences
        notification_preference: data.notification_preference || null,
      })
      .execute();

    const jobId = result.identifiers[0].id;
    const createdJob = await manager.findOne(JobPosts, {
      where: { id: jobId },
    });

    if (!createdJob) {
      throw new Error('Failed to retrieve created job.');
    }

    // Create AI Settings if enabled or present
    if (data.ai_interview_enabled) {
      // Use manager to save AI settings
      await manager.save(JobPostAiSettings, {
        job_post_id: jobId,
        ai_enabled: data.ai_interview_enabled,
        ai_duration_minutes: data.ai_interview_duration,
        ai_validity_days: data.ai_window_days,
        ai_start_time: data.ai_start_time,
        ai_end_time: data.ai_end_time,
        ai_question_count: data.ai_question_count || 5,
        ai_difficulty: data.ai_difficulty,
        ai_experience_level: data.ai_experience_level
          ? Number(data.ai_experience_level)
          : null,
        ai_config: data.ai_config,
        ai_schedule_requires_approval: data.ai_schedule_requires_approval,
      });
    }

    // Handle skills
    if (Array.isArray(data.skills) && data.skills.length > 0) {
      const skillIds: number[] = [];
      for (const skillItem of data.skills) {
        if (typeof skillItem === 'string') {
          try {
            const skill = await this.skillsService.createIfNotExists(
              skillItem,
              'tech',
            );
            skillIds.push(skill.id);
          } catch (error) {
            console.error('Error creating skill:', error);
          }
        } else if (typeof skillItem === 'number') {
          skillIds.push(skillItem);
        }
      }

      // Link skills using manager
      for (const skillId of skillIds) {
        await manager
          .createQueryBuilder()
          .insert()
          .into('job_post_skills')
          .values({ job_post_id: jobId, skill_id: skillId })
          .execute();
      }
    }

    // Handle tags
    if (Array.isArray(data.tags) && data.tags.length > 0) {
      const tagIds: number[] = [];
      for (const tagName of data.tags) {
        const tag = await this.tagsService.createIfNotExists(tagName);
        tagIds.push(tag.id);
      }

      // Inline transactional tag insertion to prevent Deadlock/Lock Wait Timeout
      // Using 'manager' ensures this happens within the same transaction as the job creation
      for (const tagId of tagIds) {
        await manager
          .createQueryBuilder()
          .insert()
          .into('jobs_post_tag')
          .values({
            job_post_id: jobId,
            tag_id: tagId,
          })
          .execute();
      }
    }

    // Handle perks
    if (Array.isArray(data.perks) || Array.isArray(data.customPerks)) {
      if (Array.isArray(data.perks) && data.perks.length > 0) {
        for (const perkId of data.perks) {
          await manager
            .createQueryBuilder()
            .insert()
            .into('job_perks')
            .values({ job_id: jobId, perk_id: perkId, custom_name: null })
            .execute();
        }
      }
      if (Array.isArray(data.customPerks) && data.customPerks.length > 0) {
        for (const customName of data.customPerks) {
          await manager
            .createQueryBuilder()
            .insert()
            .into('job_perks')
            .values({ job_id: jobId, perk_id: null, custom_name: customName })
            .execute();
        }
      }
    }

    // Trigger job matching logic (can be async/outside transaction)
    // MOVED to createWithUserId after commit to prevent deadlock/visibility issues
    // await this.dashboardService.createJobAlertsForNewPost(jobId);

    // LOGGING: Job Posting Activity
    try {
      const isAssociate = !!data.associate_id;
      const userId = isAssociate ? data.associate_id : data.userId;
      const userType = isAssociate ? 'associate' : 'recruiter';

      const isBillable = !isAssociate;
      const creditsUsed = options?.creditsUsed || 0;

      await this.activityLogsService.log(
        {
          userId: userId,
          userType: userType,
          actionType: 'job_create',
          actionName: `Created Job: ${createdJob.title}`,
          jobId: jobId,
          companyId: data.company_id,
          is_billable: isBillable,
          credits_used: creditsUsed,
          actionDetails: {
            jobId: jobId,
            title: createdJob.title,
            status: createdJob.status,
            posted_by_user: true,
            creditsUsed,
          },
        },
        manager,
      ); // Pass manager for transactional logging
    } catch (logError) {
      console.error('Failed to log job creation:', logError);
    }

    return createdJob;
  }

  // Update a job post with skills, tags, and perks
  async update(id: number, data: any): Promise<JobPosts> {
    const job = await this.findOne(id);

    if (!job) {
      // Handle case where job is not found, e.g., throw an error or return null
      throw new Error(`Job with ID ${id} not found.`);
    }
    //Extract arrays from data before updating
    const skills = data.required_skills || data.skills;
    const tags = data.job_tags || data.tags;
    const perks = data.perks;
    const customPerks = data.custom_perks;

    // Remove array fields from data object (they're not columns in job_posts table)
    delete data.required_skills;
    delete data.skills;
    delete data.job_tags;
    delete data.tags;
    delete data.perks;
    delete data.custom_perks;

    // Convert empty strings to null for date/time fields
    if (data.walkin_start_date === '') data.walkin_start_date = null;
    if (data.walkin_end_date === '') data.walkin_end_date = null;
    if (data.walkin_start_time === '') data.walkin_start_time = null;
    if (data.walkin_end_time === '') data.walkin_end_time = null;
    if (data.walkin_address === '') data.walkin_address = null;
    if (data.walkin_instructions === '') data.walkin_instructions = null;
    if (data.expires_at === '') data.expires_at = null;

    const oldStatus = job.status;
    // Update job_posts table
    Object.assign(job, data);
    await this.jobPostRepo.save(job);
    const newStatus = job.status;

    // Trigger Job Alerts if status transitioned to 'open'
    if (newStatus === 'open' && oldStatus !== 'open') {
      try {
        await this.dashboardService.createJobAlertsForNewPost(id);
      } catch (alertError) {
        console.error(
          'Failed to create job alerts during status update:',
          alertError,
        );
      }
    }

    // Update skills
    if (Array.isArray(skills)) {
      // Delete existing skills
      await this.dataSource
        .createQueryBuilder()
        .delete()
        .from('job_post_skills')
        .where('job_post_id = :jobId', { jobId: id })
        .execute();

      // Add new skills
      const skillIds: number[] = [];
      for (const skillItem of skills) {
        if (typeof skillItem === 'string') {
          try {
            const skill = await this.skillsService.createIfNotExists(
              skillItem,
              'tech',
            );
            skillIds.push(skill.id);
          } catch (error) {
            console.error('Error creating skill:', error);
          }
        } else if (typeof skillItem === 'number') {
          skillIds.push(skillItem);
        }
      }

      for (const skillId of skillIds) {
        try {
          await this.dataSource
            .createQueryBuilder()
            .insert()
            .into('job_post_skills')
            .values({
              job_post_id: id,
              skill_id: skillId,
            })
            .execute();
        } catch (error) {
          console.error('Error linking skill to job:', error);
        }
      }
    }

    // Update tags
    if (Array.isArray(tags)) {
      // Delete existing tags
      await this.dataSource
        .createQueryBuilder()
        .delete()
        .from('jobs_post_tag')
        .where('job_post_id = :jobId', { jobId: id })
        .execute();

      // Add new tags
      const tagIds: number[] = [];
      for (const tagName of tags) {
        const tag = await this.tagsService.createIfNotExists(tagName);
        tagIds.push(tag.id);
      }

      await this.jobsPostTagService.createJobTags(id, tagIds);
    }

    // Update perks
    if (Array.isArray(perks) || Array.isArray(customPerks)) {
      // Delete existing perks
      await this.dataSource
        .createQueryBuilder()
        .delete()
        .from('job_perks')
        .where('job_id = :jobId', { jobId: id })
        .execute();

      // Add standard perks
      if (Array.isArray(perks) && perks.length > 0) {
        for (const perkId of perks) {
          try {
            await this.dataSource
              .createQueryBuilder()
              .insert()
              .into('job_perks')
              .values({
                job_id: id,
                perk_id: perkId,
                custom_name: null,
              })
              .execute();
          } catch (error) {
            console.error('Error adding perk to job:', error);
          }
        }
      }

      // Add custom perks
      if (Array.isArray(customPerks) && customPerks.length > 0) {
        for (const customName of customPerks) {
          try {
            await this.dataSource
              .createQueryBuilder()
              .insert()
              .into('job_perks')
              .values({
                job_id: id,
                perk_id: null,
                custom_name: customName,
              })
              .execute();
          } catch (error) {
            console.error('Error adding custom perk to job:', error);
          }
        }
      }
    }

    // Extract AI Settings
    const aiSettings = {
      ai_interview_enabled: data.ai_interview_enabled,
      ai_duration_minutes: data.ai_interview_duration,
      ai_window_days: data.ai_window_days,
      ai_start_time: data.ai_start_time,
      ai_end_time: data.ai_end_time,
      ai_question_count: data.ai_question_count,
      ai_difficulty: data.ai_difficulty,
      ai_experience_level: data.ai_experience_level,
      ai_validity_days: data.ai_validity_days,
      ai_config: data.ai_config,
      ai_schedule_requires_approval: data.ai_schedule_requires_approval,
    };

    // Remove AI fields from data object
    delete data.ai_interview_enabled;
    delete data.ai_interview_duration;
    delete data.ai_window_days;
    delete data.ai_start_time;
    delete data.ai_end_time;
    delete data.ai_question_count;
    delete data.ai_difficulty;
    delete data.ai_experience_level;
    delete data.ai_validity_days;
    delete data.ai_config;
    delete data.ai_schedule_requires_approval;
    delete data.ai_generation_timing;
    delete data.ai_settings; // catch-all

    // Update AI Settings
    if (aiSettings.ai_interview_enabled !== undefined) {
      // Check if settings exist
      const existingSettings = await this.jobPostAiSettingsRepository.findOne({
        where: { job_post_id: id },
      });

      if (existingSettings) {
        // Update existing
        await this.jobPostAiSettingsRepository.update(
          { id: existingSettings.id },
          {
            ai_enabled: aiSettings.ai_interview_enabled,
            ai_duration_minutes: aiSettings.ai_duration_minutes,
            ai_validity_days: aiSettings.ai_validity_days,
            ai_start_time: aiSettings.ai_start_time,
            ai_end_time: aiSettings.ai_end_time,
            ai_question_count: aiSettings.ai_question_count,
            ai_difficulty: aiSettings.ai_difficulty,
            ai_experience_level: aiSettings.ai_experience_level
              ? Number(aiSettings.ai_experience_level)
              : null,
            ai_config: aiSettings.ai_config,
            ai_schedule_requires_approval:
              aiSettings.ai_schedule_requires_approval,
          },
        );
      } else if (aiSettings.ai_interview_enabled) {
        // Create new if valid and enabled
        await this.jobPostAiSettingsRepository.save({
          job_post_id: id,
          ai_enabled: aiSettings.ai_interview_enabled,
          ai_duration_minutes: aiSettings.ai_duration_minutes,
          ai_validity_days: aiSettings.ai_validity_days || 7,
          ai_start_time: aiSettings.ai_start_time,
          ai_end_time: aiSettings.ai_end_time,
          ai_question_count: aiSettings.ai_question_count || 5,
          ai_difficulty: aiSettings.ai_difficulty,
          ai_experience_level: aiSettings.ai_experience_level
            ? Number(aiSettings.ai_experience_level)
            : null,
          ai_config: aiSettings.ai_config,
          ai_schedule_requires_approval:
            aiSettings.ai_schedule_requires_approval,
        });
      }
    }

    // LOGGING: Job Update Activity (Report 1)
    try {
      // Determine action type based on changes
      let actionType = 'job_edit';
      if (data.status) {
        if (data.status === 'open' && job.status !== 'open')
          actionType = 'job_activate';
        else if (data.status === 'paused' && job.status !== 'paused')
          actionType = 'job_pause';
        else if (data.status === 'closed' && job.status !== 'closed')
          actionType = 'job_close';
      }

      // We need user context which might not be in 'data' for direct updates.
      // Assuming 'data.updated_by' or similar is passed, or we skip user specific fields if missing.
      // If triggered by admin/system, defaults apply.
      // Ideally, the controller should pass userId/userType in 'data'.

      if (data.updated_by_user_id) {
        const isAssociate = !!data.is_associate; // Helper flag from controller
        const userId = data.updated_by_user_id;
        const userType = isAssociate ? 'associate' : 'recruiter';

        await this.activityLogsService.log({
          userId: userId,
          userType: userType,
          actionType: actionType, // job_edit, job_activate, etc.
          actionName: `${actionType.replace('job_', '')} Job: ${job.title}`,
          jobId: id,
          companyId: job.company_id,
          is_billable: false, // Updates are generally non-billable unless specified
          credits_used: 0,
          actionDetails: {
            changes: Object.keys(data).join(', '),
            newStatus: data.status,
          },
        });
      }
    } catch (logError) {
      console.error('Failed to log job update:', logError);
    }

    return this.findOne(id);
  }

  // Remove a job post
  async remove(id: number): Promise<{ message: string }> {
    const job = await this.findOne(id);
    await this.jobPostRepo.remove(job);
    return { message: 'Job post deleted successfully' };
  }

  // --- NEW: Fetch all job posts with company, category, location details ---
  async findAllWithDetails() {
    try {
      const result = await this.dataSource
        .createQueryBuilder()
        .select([
          'job.id AS id',
          'job.company_id AS company_id',
          'job.title AS title',
          'jobType.name AS job_type',
          'job.featured AS featured',
          'job.salary_min AS salary_min',
          'job.salary_max AS salary_max',
          'job.status AS status',
          'company.name AS company_name',
          'company.logo AS company_logo',
          'job.image_url AS image_url',
          'city.city_name AS city',
          'state.state_name AS state',
          'country.country_name AS country',
          'edu.name AS education',
          'exp.level AS experience',
          'exp.duration AS experience_duration',
          'roleLevel.name AS role_level',
          'jobLevel.name AS job_level',
          'industry.name AS industry',
          'job.expires_at AS expires_at',
          'job.created_at AS created_at',
          'job.created_at AS posted_date',
        ])
        .from('job_posts', 'job')
        .innerJoin('companies', 'company', 'company.id = job.company_id')
        .leftJoin('city', 'city', 'city.city_id = job.city_id')
        .leftJoin('state', 'state', 'state.state_id = job.state_id')
        .leftJoin('country', 'country', 'country.country_id = job.country_id')
        .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
        .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
        .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
        .leftJoin(
          'role_levels',
          'roleLevel',
          'roleLevel.id = job.role_levels_id',
        )
        .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
        .leftJoin('industries', 'industry', 'industry.id = job.industry_id')
        .where('job.status = :status', { status: 'open' }) // Only return open jobs
        .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())') // Filter out expired jobs
        .getRawMany();

      // console.log('findAllWithDetails result:', result);
      return result;
    } catch (error) {
      //console.error('Error in findAllWithDetails:', error);
      throw error;
    }
  }

  // --- NEW: Fetch a single job post with details ---
  async getExpiryStatus(id: number) {
    const job = await this.jobPostRepo.findOne({
      where: { id },
      select: ['id', 'expires_at', 'status'],
    });
    if (!job) return { isExpired: false };
    const isExpired =
      job.status !== 'open' ||
      (job.expires_at && new Date(job.expires_at) < new Date());
    return { isExpired };
  }

  async findOneWithDetails(id: number, userId?: number) {
    // console.log('=== FETCHING JOB DETAILS ===');
    // console.log('Job ID:', id);

    const result = await this.dataSource
      .createQueryBuilder()
      .select([
        'job.id AS id',
        'job.company_id AS company_id',
        'job.title AS title',
        'job.address AS address',
        'job.salary_min AS salary_min',
        'job.salary_max AS salary_max',
        'job.work_mode AS work_mode',
        'job.status AS status',
        'job.featured AS featured',
        'job.requirements AS requirements',
        'job.benefits AS benefits',
        'job.desirable AS desirable',
        'job.pincode AS pincode',
        'job.area_id AS area_id',
        'job.vacancies AS vacancies',
        'job.urgent_hiring AS urgent_hiring',
        'job.shift AS shift',
        'job.english_level AS english_level',
        'job.gender AS gender',
        'job.walk_in_interview AS walk_in_interview',
        'job.walkin_address AS walkin_address',
        'job.walkin_start_date AS walkin_start_date',
        'job.walkin_end_date AS walkin_end_date',
        'job.walkin_start_time AS walkin_start_time',
        'job.walkin_end_time AS walkin_end_time',
        'job.walkin_instructions AS walkin_instructions',
        'job.communication_preference AS communication_preference',
        'job.other_recruiter_name AS other_recruiter_name',
        'job.other_recruiter_whatsapp AS other_recruiter_whatsapp',
        'job.other_recruiter_email AS other_recruiter_email',
        'job.notification_preference AS notification_preference',
        // AI Interview Configuration
        'ai.ai_enabled AS ai_interview_enabled',
        'ai.ai_duration_minutes AS ai_interview_duration',
        'ai.ai_validity_days AS ai_window_days',
        'ai.ai_start_time AS ai_start_time',
        'ai.ai_end_time AS ai_end_time',
        'ai.ai_question_count AS ai_question_count',
        'ai.ai_difficulty AS ai_difficulty',
        'ai.ai_experience_level AS ai_experience_level',
        'ai.ai_config AS ai_config',
        'ai.ai_schedule_requires_approval AS ai_schedule_requires_approval',
        // Job Type - name and ID
        'job.job_type_id AS job_type_id',
        'jobType.name AS job_type',
        'jobType.name AS job_type_name',
        // Location IDs and names
        'job.country_id AS country_id',
        'job.state_id AS state_id',
        'job.city_id AS city_id',
        'city.city_name AS city',
        'state.state_name AS state',
        'country.country_name AS country',
        'area.area AS area_name',
        // Experience - ID and name
        'job.experience_id AS experience_id',
        'exp.level AS experience',
        'exp.level AS experience_name',
        'exp.duration AS experience_duration',
        // Education - ID and name
        'job.education_id AS education_id',
        'edu.name AS education',
        'edu.name AS education_name',
        // Industry - ID and name
        'job.industry_id AS industry_id',
        'industry.name AS industry',
        // Role Level - ID and name
        'job.role_levels_id AS role_levels_id',
        'roleLevel.name AS role_level',
        // Role - ID and name
        'job.role_id AS role_id',
        'role.name AS role_name',
        'role.category AS role_category',
        // Job Level - ID and name
        'job.job_level_id AS job_level_id',
        'jobLevel.name AS job_level',
        'job.created_at AS created_at',
        'job.created_at AS posted_date',
        'job.updated_at AS updated_at',
        'job.expires_at AS expires_at',
        'company.name AS company_name',
        'company.logo AS company_logo',
        'company.website AS company_website',
        'job.image_url AS image_url',
        'job.description AS description',
      ])
      .from('job_posts', 'job')
      .innerJoin('companies', 'company', 'company.id = job.company_id')
      .leftJoin('city', 'city', 'city.city_id = job.city_id')
      .leftJoin('state', 'state', 'state.state_id = job.state_id')
      .leftJoin('country', 'country', 'country.country_id = job.country_id')
      .leftJoin('area', 'area', 'area.id = job.area_id')
      .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
      .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
      .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
      .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
      .leftJoin('role_levels', 'roleLevel', 'roleLevel.id = job.role_levels_id')
      .leftJoin('roles', 'role', 'role.id = job.role_id')
      .leftJoin('industries', 'industry', 'industry.id = job.industry_id')
      .leftJoin('job_post_ai_settings', 'ai', 'ai.job_post_id = job.id')
      .where('job.id = :id', { id })
      .getRawOne();

    // console.log('=== QUERY RESULT ===');
    // console.log('Raw query result:', result);

    if (!result) throw new NotFoundException('Job post not found');

    // Check for expiration
    // const isExpired = result.status !== 'open' || (result.expires_at && new Date(result.expires_at) < new Date());
    // if (isExpired) {
    //   throw new HttpException('Job has expired', HttpStatus.GONE);
    // }

    // Fetch skills from job_post_skills table
    const skills = await this.dataSource
      .createQueryBuilder()
      .select(['skills.id AS id', 'skills.skill_name AS name'])
      .from('job_post_skills', 'jps')
      .leftJoin('skills', 'skills', 'skills.id = jps.skill_id')
      .where('jps.job_post_id = :jobId', { jobId: id })
      .getRawMany();

    // console.log('=== SKILLS DEBUG ===');
    // console.log('Job ID:', id);
    // console.log('Skills query result:', skills);

    // Fetch tags from jobs_post_tag table
    const tags = await this.dataSource
      .createQueryBuilder()
      .select(['tags.id AS id', 'tags.name AS name'])
      .from('jobs_post_tag', 'jpt')
      .leftJoin('tags', 'tags', 'tags.id = jpt.tag_id')
      .where('jpt.job_post_id = :jobId', { jobId: id })
      .getRawMany();

    // console.log('=== TAGS DEBUG ===');
    // console.log('Tags query result:', tags);

    // Fetch perks from job_perks table
    const perks = await this.dataSource
      .createQueryBuilder()
      .select([
        'jp.id AS id',
        'jp.perk_id AS perk_id',
        'jp.custom_name AS custom_name',
        'perks.name AS name',
        'CASE WHEN jp.custom_name IS NOT NULL THEN 1 ELSE 0 END AS is_custom',
      ])
      .from('job_perks', 'jp')
      .leftJoin('perks', 'perks', 'perks.id = jp.perk_id')
      .where('jp.job_id = :jobId', { jobId: id })
      .getRawMany();

    // console.log('=== PERKS DEBUG ===');
    // console.log('Perks query result:', perks);

    // Add skills, tags, and perks to the result
    result.required_skills = skills;
    result.job_tags = tags;
    result.perks = perks;

    // console.log('=== FINAL RESULT ===');
    // console.log('Final result with skills, tags, and perks:', result);

    if (userId) {
      const seekerProfile = await this.dataSource
        .createQueryBuilder()
        .select('id')
        .from('seeker_profiles', 'sp')
        .where('sp.user_id = :userId', { userId })
        .getRawOne();
        
      if (seekerProfile) {
        const application = await this.dataSource
          .createQueryBuilder()
          .select('id')
          .from('job_applications', 'ja')
          .where('ja.job_post_id = :jobId', { jobId: id })
          .andWhere('ja.seeker_id = :seekerId', { seekerId: seekerProfile.id })
          .getRawOne();
        result.has_applied = !!application;
        
        const saved = await this.dataSource
          .createQueryBuilder()
          .select('id')
          .from('job_saved', 'js')
          .where('js.job_post_id = :jobId', { jobId: id })
          .andWhere('js.seeker_id = :seekerId', { seekerId: seekerProfile.id })
          .andWhere('js.is_deleted = false')
          .getRawOne();
        result.has_saved = !!saved;
        // console.log(`[DEBUG] findOneWithDetails id=${id}, userId=${userId}, seekerId=${seekerProfile.id}, application_found=${!!application}`);
      } else {
        result.has_applied = false;
        result.has_saved = false;
        // console.log(`[DEBUG] findOneWithDetails id=${id}, NO SEEKER PROFILE found for userId=${userId}!`);
      }
    } else {
      result.has_applied = false;
      result.has_saved = false;
      // console.log(`[DEBUG] findOneWithDetails id=${id}, NO USER_ID provided!`);
    }

    return result;
  }
  // async findOneWithDetails(id: number) {
  //   const result = await this.dataSource
  //     .createQueryBuilder()
  //     .select([
  //       'job.id AS id',
  //       'job.title AS title',
  //       'job.salary_range AS salary_range',
  //       'job.status AS status',
  //       'job.featured AS featured',
  //       'job.experience AS experience',
  //       'job.education AS education',
  //       'job.requirements AS requirements',
  //       'job.benefits AS benefits',
  //       'job.desirable AS desirable',
  //       'job.job_level AS job_level',
  //       'job.job_type AS job_type',
  //       'job.created_at AS created_at',
  //       'job.updated_at AS updated_at',
  //       'job.expires_at AS expires_at',
  //       'company.name AS company_name',
  //       'category.name AS category_name',
  //       'country.country_id AS country_id',
  //       'country.country_code AS country_code',
  //       'country.country_name AS country_name',
  //       'country.currency_symbol AS currency_symbol',
  //       'country.currency AS currency',
  //       'state.state_id AS state_id',
  //       'state.state_name AS state_name',
  //       'city.city_id AS city_id',
  //       'city.city_name AS city_name',
  //       'job.image_url AS image_url',
  //     ])
  //     .from('job_posts', 'job')
  //     .innerJoin('companies', 'company', 'company.id = job.company_id')
  //     .leftJoin('job_categories', 'category', 'category.id = job.category_id')
  //     .leftJoin('cities', 'city', 'city.city_id = job.city_id')
  //     .leftJoin('states', 'state', 'state.state_id = city.state_id')
  //     .leftJoin('countries', 'country', 'country.country_id = state.country_id')
  //     .where('job.id = :id', { id })
  //     .getRawOne();

  async getCompanyIdForUser(userId: number): Promise<number | null> {
    const company = await this.dataSource
      .createQueryBuilder()
      .select('companies.id')
      .from('companies', 'companies')
      .where('companies.user_id = :userId', { userId })
      .getOne();
    return company?.id ?? null;
  }
  // Get all available job categories for frontend dropdown

  // Get all available locations for frontend dropdown
  async getLocations() {
    try {
      const result = await this.dataSource
        .createQueryBuilder()
        .select([
          'city.city_id AS id',
          'city.city_name AS city',
          'state.state_id AS state_id',
          'state.state_name AS state',
          'country.country_id AS country_id',
          'country.country_name AS country',
          "CONCAT(city.city_name, ', ', state.state_name, ', ', country.country_name) AS full_location",
        ])
        .from('city', 'city')
        .leftJoin('state', 'state', 'state.state_id = city.state_id')
        .leftJoin('country', 'country', 'country.country_id = state.country_id')
        .where('city.status = 1')
        .andWhere('state.status = 1')
        .andWhere('country.status = 1')
        .orderBy('city.city_name', 'ASC')
        .getRawMany();
      return result;
    } catch (error) {
      //console.error('Error fetching locations:', error);
      throw new Error(`Failed to fetch locations: ${error.message}`);
    }
  }

  // Get all available job types for frontend dropdown
  async getJobTypes() {
    return this.dataSource
      .createQueryBuilder()
      .select(['id', 'name'])
      .from('job_type', 'jobType')
      .orderBy('jobType.name', 'ASC')
      .getRawMany();
  }

  // Get job types with job counts for homepage
  async getJobTypesWithCounts() {
    return this.dataSource
      .createQueryBuilder()
      .select(['jt.id as id', 'jt.name as name', 'COUNT(jp.id) as job_count'])
      .from('job_type', 'jt')
      .leftJoin(
        'job_posts',
        'jp',
        'jt.id = jp.job_type_id AND jp.status = :status AND (jp.expires_at IS NULL OR jp.expires_at > NOW())',
        { status: 'open' },
      )
      .groupBy('jt.id, jt.name')
      .orderBy('job_count', 'DESC')
      .getRawMany();
  }

  // Get all available experience levels for frontend dropdown
  async getExperienceLevels() {
    const result = await this.dataSource
      .createQueryBuilder()
      .select(['id', 'level', 'duration'])
      .from('experiences', 'exp')
      // .orderBy('exp.level', 'ASC')
      .getRawMany();

    // console.log('Experience levels from database:', result);
    return result;
  }

  // Get all available education levels for frontend dropdown
  async getEducationLevels() {
    return (
      this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('education_levels', 'edu')
        // .orderBy('edu.name', 'ASC')
        .getRawMany()
    );
  }

  // Get education levels with job counts for homepage
  async getEducationLevelsWithCounts() {
    return this.dataSource
      .createQueryBuilder()
      .select(['el.id as id', 'el.name as name', 'COUNT(jp.id) as job_count'])
      .from('education_levels', 'el')
      .leftJoin(
        'job_posts',
        'jp',
        'el.id = jp.education_id AND jp.status = :status AND (jp.expires_at IS NULL OR jp.expires_at > NOW())',
        { status: 'open' },
      )
      .where('el.status = :elStatus', { elStatus: 1 })
      .groupBy('el.id, el.name')
      .orderBy('job_count', 'DESC')
      .getRawMany();
  }

  // Get education levels for mega menu
  async getMegaMenuEducationLevels() {
    try {
      // console.log('Starting mega menu education levels query...');
      const educationLevels = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('education_levels', 'edu')
        .orderBy('edu.id', 'ASC')
        .getRawMany();

      // console.log('Education levels fetched:', educationLevels);
      return {
        success: true,
        data: educationLevels,
      };
    } catch (error) {
      console.error('Error fetching mega menu education levels:', error);
      throw new Error(
        `Failed to fetch mega menu education levels: ${error.message}`,
      );
    }
  }

  // Get industries for mega menu
  async getMegaMenuIndustries() {
    try {
      // console.log('Starting mega menu industries query...');
      const industries = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('industries', 'ind')
        .orderBy('ind.id', 'ASC')
        .getRawMany();

      // console.log('Industries fetched:', industries);
      return {
        success: true,
        data: industries,
      };
    } catch (error) {
      console.error('Error fetching mega menu industries:', error);
      throw new Error(`Failed to fetch mega menu industries: ${error.message}`);
    }
  }

  // Get all available role levels for frontend dropdown
  async getRoleLevels() {
    return this.dataSource
      .createQueryBuilder()
      .select(['id', 'name'])
      .from('role_levels', 'role')
      .orderBy('role.name', 'ASC')
      .getRawMany();
  }

  // Get tags for a specific job
  async getTagsForJob(jobPostId: number): Promise<any[]> {
    return this.jobsPostTagService.getTagsForJob(jobPostId);
  }

  // Get job posts by role level
  async findByRoleLevel(roleLevelId: number) {
    try {
      // Validate and convert roleLevelId to a proper number
      const validRoleLevelId = Number(roleLevelId);

      if (isNaN(validRoleLevelId) || validRoleLevelId <= 0) {
        throw new Error('Invalid role level ID provided');
      }

      const result = await this.dataSource
        .createQueryBuilder()
        .select([
          'job.id AS id',
          'job.title AS title',
          'jobType.name AS job_type',
          'job.featured AS featured',
          'job.salary_min AS salary_min',
          'job.salary_max AS salary_max',
          'job.status AS status',
          'company.name AS company_name',
          'company.logo AS company_logo',
          'job.image_url AS image_url',
          'city.city_name AS city',
          'state.state_name AS state',
          'country.country_name AS country',
          'edu.name AS education',
          'exp.level AS experience_required',
          'exp.duration AS experience_duration',
          'jobLevel.name AS job_level',
          'job.created_at AS created_at',
          'job.expires_at AS expires_at',
          'roleLevel.name AS role_level_name',
          'job.description AS description',
          'job.requirements AS requirements',
          'job.benefits AS benefits',
        ])
        .from('job_posts', 'job')
        .innerJoin('companies', 'company', 'company.id = job.company_id')
        .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
        .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
        .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
        .leftJoin('city', 'city', 'city.city_id = job.city_id')
        .leftJoin('state', 'state', 'state.state_id = job.state_id')
        .leftJoin('country', 'country', 'country.country_id = job.country_id')
        .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
        .leftJoin(
          'role_levels',
          'roleLevel',
          'roleLevel.id = job.role_levels_id',
        )
        .where('job.role_levels_id = :roleLevelId', {
          roleLevelId: validRoleLevelId,
        })
        .andWhere('job.status = :status', { status: 'open' })
        .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())') // Filter out expired jobs
        .orderBy('job.created_at', 'DESC')
        .getRawMany();

      return result;
    } catch (error) {
      console.error('Error in findByRoleLevel:', error);
      throw error;
    }
  }

  async findByRole(roleId: number) {
    try {
      // Validate and convert roleId to a proper number
      const validRoleId = Number(roleId);

      if (isNaN(validRoleId) || validRoleId <= 0) {
        throw new Error('Invalid role ID provided');
      }

      const result = await this.dataSource
        .createQueryBuilder()
        .select([
          'job.id AS id',
          'job.title AS title',
          'jobType.name AS job_type',
          'job.featured AS featured',
          'job.salary_min AS salary_min',
          'job.salary_max AS salary_max',
          'job.status AS status',
          'company.name AS company_name',
          'company.logo AS company_logo',
          'job.image_url AS image_url',
          'city.city_name AS city',
          'state.state_name AS state',
          'country.country_name AS country',
          'edu.name AS education',
          'exp.level AS experience_required',
          'exp.duration AS experience_duration',
          'jobLevel.name AS job_level',
          'job.created_at AS created_at',
          'job.expires_at AS expires_at',
          'role.name AS role_name',
          'job.description AS description',
          'job.requirements AS requirements',
          'job.benefits AS benefits',
        ])
        .from('job_posts', 'job')
        .innerJoin('companies', 'company', 'company.id = job.company_id')
        .leftJoin('education_levels', 'edu', 'edu.id = job.education_id')
        .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
        .leftJoin('job_level', 'jobLevel', 'jobLevel.id = job.job_level_id')
        .leftJoin('city', 'city', 'city.city_id = job.city_id')
        .leftJoin('state', 'state', 'state.state_id = job.state_id')
        .leftJoin('country', 'country', 'country.country_id = job.country_id')
        .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
        .leftJoin('roles', 'role', 'role.id = job.role_id')
        .where('job.role_id = :roleId', {
          roleId: validRoleId,
        })
        .andWhere('job.status = :status', { status: 'open' })
        .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())') // Filter out expired jobs
        .orderBy('job.created_at', 'DESC')
        .getRawMany();

      return result;
    } catch (error) {
      console.error('Error in findByRole:', error);
      throw error;
    }
  }

  async createWithUserId(data: any): Promise<JobPosts> {
    console.log(
      'DEBUG: createWithUserId received data:',
      JSON.stringify(data, null, 2),
    );

    // 0. Calculate Job Expiry (Automation)
    const defaultExpiry = new Date();
    try {
      let durationDays = null;

      if (data.job_package) {
        // Find package info
        const pkgQuery = isNaN(Number(data.job_package))
          ? 'SELECT * FROM job_packages WHERE code = ?'
          : 'SELECT * FROM job_packages WHERE id = ?';
        const [pkg] = await this.dataSource.query(pkgQuery, [data.job_package]);
        if (pkg && pkg.duration_days) {
          durationDays = Number(pkg.duration_days);
        }
      } else {
        // Find active subscription plan
        const subs = await this.dataSource.query(
          `SELECT us.*, p.job_active_days 
           FROM user_subscriptions us 
           JOIN plans p ON p.id = us.plan_id 
           WHERE us.user_id = ? AND us.status = 'active' AND us.ends_at > NOW() 
           ORDER BY us.created_at DESC LIMIT 1`,
          [data.userId],
        );
        const sub = subs[0];
        if (sub && sub.job_active_days) {
          durationDays = Number(sub.job_active_days);
        }
      }

      // If no package or subscription, fetch free tier config
      if (durationDays === null) {
        const [freeJobActiveDaysConfig] = await this.dataSource.query(
          'SELECT setting_value FROM config_setting WHERE setting_key = "free_job_active_days"',
        );
        durationDays = parseInt(freeJobActiveDaysConfig?.setting_value || '15');
      }

      defaultExpiry.setDate(defaultExpiry.getDate() + durationDays);
    } catch (err) {
      console.warn('Failed to calculate automated job expiry:', err);
      defaultExpiry.setDate(defaultExpiry.getDate() + 15);
    }

    // 0. Get user role to handle associates and recruiters differently
    const users = await this.dataSource.query(
      'SELECT id, role FROM users WHERE id = ?',
      [data.userId],
    );
    const user = users[0];

    if (!user) throw new Error('User not found');

    // 0. Finalize Job Expiry Date
    let jobExpiryDate: Date;
    if (data.expires_at) {
      const manualDate = new Date(data.expires_at);
      // For associates, we allow absolute override.
      // For recruiters, we cap it at defaultExpiry to enforce plan limits.
      if (user.role === 'associate') {
        jobExpiryDate = manualDate;
      } else {
        jobExpiryDate = defaultExpiry;
      }
    } else {
      jobExpiryDate = defaultExpiry;
    }

    let companyId = data.company_id;
    let associateId = null;

    // 1. Find company_id based on user role
    if (user.role === 'associate') {
      // Associates: company_id must be provided (from admin companies dropdown)
      if (!companyId) {
        throw new Error('Company ID is required for associates');
      }

      // Get associate_id
      const associate = await this.dataSource
        .createQueryBuilder()
        .select(['id'])
        .from('associates', 'a')
        .where('a.user_id = :userId', { userId: data.userId })
        .getRawOne();

      if (associate) {
        associateId = associate.id;
      }
    } else {
      // Recruiters: find their company if not provided
      if (!companyId) {
        const company = await this.dataSource
          .createQueryBuilder()
          .select('companies.id', 'id')
          .from('companies', 'companies')
          .where('companies.user_id = :userId', { userId: data.userId })
          .getRawOne();

        if (!company) throw new Error('Company not found for user');
        companyId = company.id;
      }
    }

    let roleId = data.role_id ? Number(data.role_id) : null;

    // 2. If role_id is not provided but role and role_definition are, create/find the role
    if (!roleId && data.role && data.role_definition && data.role_levels_id) {
      // Try to find the role
      const role = await this.dataSource
        .createQueryBuilder()
        .select('roles.id', 'id')
        .from('roles', 'roles')
        .where(
          'roles.name = :name AND roles.role_definition = :definition AND roles.role_levels_id = :levelId',
          {
            name: data.role,
            definition: data.role_definition,
            levelId: data.role_levels_id,
          },
        )
        .getRawOne();

      if (role) {
        roleId = role.id;
      } else {
        // Insert new role
        const insertResult = await this.dataSource
          .createQueryBuilder()
          .insert()
          .into('roles')
          .values({
            name: data.role,
            role_definition: data.role_definition,
            role_levels_id: data.role_levels_id,
          })
          .execute();
        roleId = insertResult.identifiers[0].id;
      }
    }

    // FINAL CHECK: If roleId is not a valid, non-zero number, throw error
    if (!roleId || isNaN(roleId) || roleId === 0) {
      throw new Error('Role is required and must be a valid non-zero number');
    }

    // 3. Use location IDs directly (frontend now sends IDs)
    const countryId = data.country_id;
    const stateId = data.state_id;
    const cityId = data.city_id;

    // console.log('roleId before insert:', roleId);

    // 4. Deduct Job Credit & Create Job (Transaction)
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      let creditsUsed = 0;

      // A. Deduct Credit (Recruiters only)
      if (user.role !== 'associate') {
        // Use dynamic credit cost if available, otherwise default to 1 (Basic Job Post)
        // Ensure credits_required is numeric and default to 1 if missing or somehow 0/invalid
        const cost =
          data.credits_required && Number(data.credits_required) >= 0
            ? Number(data.credits_required)
            : 1;

        // Use transaction-aware debit
        if (cost > 0) {
          await this.walletService.debitWallet(
            data.userId,
            'job',
            cost,
            'Job Posting Credit Deduction',
            'job_post',
            null,
            queryRunner.manager, // Pass the manager!
          );
          creditsUsed = cost;
        } else {
          // If cost is 0 (e.g. associate or free), just log usage as 0
          creditsUsed = 0;
        }
      }

      // B. Create Job (Pass QueryRunner)
      const job = await this.createjob(
        {
          ...data,
          company_id: companyId,
          associate_id: associateId,
          role_id: roleId,
          role_levels_id: data.role_levels_id,
          country_id: countryId,
          state_id: stateId,
          city_id: cityId,
          expires_at: jobExpiryDate,
        },
        { creditsUsed },
        queryRunner,
      );

      // C. Commit Transaction
      await queryRunner.commitTransaction();

      // D. Trigger Job Alerts (MOVED to approval flow)
      // Job alerts are now triggered only when the job is approved (status set to 'open')

      return job;
    } catch (error) {
      // E. Rollback on any error
      await queryRunner.rollbackTransaction();
      console.error('Transaction failed in createWithUserId:', error);
      throw error;
    } finally {
      // F. Release connection
      await queryRunner.release();
    }
  }

  async getRelatedJobs(
    jobId: number,
    limit: number = 6,
    page: number = 1,
    clearCache: boolean = false,
  ) {
    try {
      // Check cache first (unless clearCache is true)
      const cacheKey = `related_jobs_v2_${jobId}_${limit}_${page}`;
      if (!clearCache) {
        const cachedResult = (await this.cacheManager.get(cacheKey)) as any;
        if (cachedResult) {
          // console.log('Cache hit - cached data:', cachedResult);
          return {
            success: true,
            data: cachedResult.data,
            total: cachedResult.total,
            message: 'Related jobs fetched from cache',
          };
        }
      } else {
        // console.log('Cache bypassed due to clearCache parameter');
      }

      // Get current job details
      const currentJob = await this.dataSource
        .createQueryBuilder()
        .select([
          'job.id AS id',
          'job.role_levels_id AS role_levels_id',
          'job.city_id AS city_id',
          'job.state_id AS state_id',
          'job.job_type_id AS job_type_id',
          'job.experience_id AS experience_id',
        ])
        .from('job_posts', 'job')
        .where('job.id = :jobId', { jobId })
        .getRawOne();

      // console.log('Current job details:', currentJob);

      if (!currentJob) {
        return {
          success: false,
          data: [],
          message: 'Job not found',
        };
      }

      // Get total count for pagination
      const totalCount = await this.dataSource
        .createQueryBuilder()
        .select('COUNT(*)', 'count')
        .from('job_posts', 'job')
        .innerJoin('companies', 'company', 'company.id = job.company_id')
        .where('job.id != :jobId', { jobId })
        .andWhere('job.status = :status', { status: 'open' })
        .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())') // Filter out expired jobs
        .getRawOne();

      const total = parseInt(totalCount.count) || 0;
      const offset = (page - 1) * limit;

      // Get related jobs with similarity scoring
      const similarityParams = {
        roleLevelsId: currentJob.role_levels_id || null,
        cityId: currentJob.city_id || null,
        stateId: currentJob.state_id || null,
        jobTypeId: currentJob.job_type_id || null,
        workMode: currentJob.work_mode || null,
        experienceId: currentJob.experience_id || null,
      };

      const relatedJobs = await this.dataSource
        .createQueryBuilder()
        .select([
          'job.id AS id',
          'job.title AS title',
          'job.salary_min AS salary_min',
          'job.salary_max AS salary_max',
          'job.created_at AS created_at',
          'job.featured AS is_featured',
          'company.name AS company_name',
          'company.logo AS company_logo',
          'job.image_url AS image_url',
          'jobType.name AS job_type',
          'jobType.name AS job_type_name',
          'exp.level AS experience',
          'exp.duration AS experience_duration',
          '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',
        ])
        .addSelect(
          `(
            CASE 
              WHEN job.role_levels_id = :roleLevelsId THEN 0.4
              ELSE 0
            END +
            CASE 
              WHEN job.city_id = :cityId THEN 0.3
              WHEN job.state_id = :stateId THEN 0.15
              ELSE 0
            END +
            CASE 
              WHEN job.job_type_id = :jobTypeId THEN 0.2
              ELSE 0
            END +
            CASE 
              WHEN job.experience_id = :experienceId THEN 0.1
              ELSE 0
            END
          ) AS similarity_score`,
        )
        .from('job_posts', 'job')
        .innerJoin('companies', 'company', 'company.id = job.company_id')
        .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
        .leftJoin('experiences', 'exp', 'exp.id = job.experience_id')
        .leftJoin('city', 'city', 'city.city_id = job.city_id')
        .leftJoin('state', 'state', 'state.state_id = job.state_id')
        .leftJoin('country', 'country', 'country.country_id = job.country_id')
        .where('job.id != :jobId', { jobId })
        .andWhere('job.status = :status', { status: 'open' })
        .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())') // Filter out expired jobs
        .setParameters(similarityParams)
        .orderBy('similarity_score', 'DESC')
        .addOrderBy('job.created_at', 'DESC')
        .offset(offset)
        .limit(limit)
        .getRawMany();

      // Format the response
      const formattedJobs = relatedJobs.map((job) => ({
        id: job.id,
        title: job.title,
        company_name: job.company_name,
        company_logo: job.company_logo,
        image_url: job.image_url,
        location: job.location,
        salary_min: job.salary_min,
        salary_max: job.salary_max,
        job_type: job.job_type,
        posted_date: job.created_at,
        is_featured: job.is_featured === 1,
        similarity_score: parseFloat(job.similarity_score),
      }));

      // Cache the result for 15 minutes
      const cacheData = {
        data: formattedJobs,
        total: total,
      };

      await this.cacheManager.set(cacheKey, cacheData, 900000);

      return {
        success: true,
        data: formattedJobs,
        total: total,
        message: 'Related jobs fetched successfully',
      };
    } catch (error) {
      console.error('Error fetching related jobs:', error);
      return {
        success: false,
        data: [],
        message: 'Failed to fetch related jobs',
      };
    }
  }
  async getApplications(
    status?: string,
    page = 1,
    limit = 10,
    search?: string,
  ) {
    const qb = this.dataSource
      .createQueryBuilder()
      .select([
        'job.id AS id',
        'job.company_id AS company_id',
        'job.title AS title',
        'job.address AS address',
        'job.description AS description',
        'job.requirements AS requirements',
        'job.benefits AS benefits',
        'job.desirable AS desirable',
        'job.salary_min AS salary_min',
        'job.salary_max AS salary_max',
        'job.status AS status',
        'job.featured AS featured',
        'job.vacancies AS vacancies',
        'job.urgent_hiring AS urgent_hiring',
        'job.shift AS shift',
        'job.english_level AS english_level',
        'job.gender AS gender',
        'job.walk_in_interview AS walk_in_interview',
        'job.walkin_address AS walkin_address',
        'job.walkin_start_date AS walkin_start_date',
        'job.walkin_end_date AS walkin_end_date',
        'job.walkin_start_time AS walkin_start_time',
        'job.walkin_end_time AS walkin_end_time',
        'job.walkin_instructions AS walkin_instructions',
        'job.created_at AS created_at',
        'job.updated_at AS updated_at',
        'job.expires_at AS expires_at',
        'company.name AS company_name',
        'company.logo AS company_logo',
        'jobType.name AS job_type',
        'jobType.name AS job_type_name',
        'experience.level AS experience_name',
        'experience.duration AS experience_duration',
        'education.name AS education_name',
        'role.name AS role_name',
        'industry.name AS industry',
        'city.city_name AS city',
        'state.state_name AS state',
        'country.country_name AS country',
        'area.area AS area_name',
        'job.other_recruiter_name AS recruiter_name',
        'job.other_recruiter_name AS posted_by',
      ])
      .from('job_posts', 'job')
      .leftJoin('companies', 'company', 'company.id = job.company_id')
      .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
      .leftJoin(
        'experiences',
        'experience',
        'experience.id = job.experience_id',
      )
      .leftJoin(
        'education_levels',
        'education',
        'education.id = job.education_id',
      )
      .leftJoin('roles', 'role', 'role.id = job.role_id')
      .leftJoin('industries', 'industry', 'industry.id = job.industry_id')
      .leftJoin('city', 'city', 'city.city_id = job.city_id')
      .leftJoin('state', 'state', 'state.state_id = job.state_id')
      .leftJoin('country', 'country', 'country.country_id = job.country_id')
      .leftJoin('area', 'area', 'area.id = job.area_id');

    if (status) {
      if (status === 'expired') {
        qb.where('job.expires_at IS NOT NULL AND job.expires_at <= NOW()');
      } else {
        qb.where('job.status = :status', { status });
      }
    }

    if (search) {
      const searchTerm = `%${search.trim().toLowerCase()}%`;
      qb.andWhere(
        '(LOWER(job.title) LIKE :searchTerm OR LOWER(company.name) LIKE :searchTerm)',
        { searchTerm },
      );
    }
    qb.orderBy('job.created_at', 'DESC')
      .offset((page - 1) * limit)
      .limit(limit);

    const applications = await qb.getRawMany();

    // Fetch skills and perks for each job
    for (const job of applications) {
      // Fetch skills from job_post_skills table
      const skills = await this.dataSource
        .createQueryBuilder()
        .select(['skills.id AS id', 'skills.skill_name AS name'])
        .from('job_post_skills', 'jps')
        .leftJoin('skills', 'skills', 'skills.id = jps.skill_id')
        .where('jps.job_post_id = :jobId', { jobId: job.id })
        .getRawMany();

      // Fetch perks from job_perks table
      const perks = await this.dataSource
        .createQueryBuilder()
        .select([
          'jp.id AS id',
          'jp.perk_id AS perk_id',
          'jp.custom_name AS custom_name',
          'perks.name AS name',
          'CASE WHEN jp.custom_name IS NOT NULL THEN 1 ELSE 0 END AS is_custom',
        ])
        .from('job_perks', 'jp')
        .leftJoin('perks', 'perks', 'perks.id = jp.perk_id')
        .where('jp.job_id = :jobId', { jobId: job.id })
        .getRawMany();

      job.job_post_skill = skills;
      job.job_perks = perks;
    }

    // Get total count for pagination
    const countQb = this.dataSource
      .createQueryBuilder()
      .select('COUNT(*)', 'count')
      .from('job_posts', 'job')
      .leftJoin('companies', 'company', 'company.id = job.company_id');

    if (status) {
      if (status === 'expired') {
        countQb.where('job.expires_at IS NOT NULL AND job.expires_at <= NOW()');
      } else {
        countQb.where('job.status = :status', { status });
      }
    }

    if (search) {
      const searchTerm = `%${search.trim().toLowerCase()}%`;
      countQb.andWhere(
        '(LOWER(job.title) LIKE :searchTerm OR LOWER(company.name) LIKE :searchTerm)',
        { searchTerm },
      );
    }

    const totalResult = await countQb.getRawOne();
    const total = parseInt(totalResult.count);
    const totalPages = Math.ceil(total / limit);

    return {
      success: true,
      data: {
        applications,
        pagination: {
          total,
          totalPages,
        },
      },
    };
  }

  async getActiveJobStats() {
    const result = await this.dataSource
      .createQueryBuilder()
      .select('COUNT(*)', 'count')
      .from('job_posts', 'job')
      .where('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .getRawOne<{ count?: string }>();

    const activeJobs = Number(result?.count || 0);

    return {
      success: true,
      data: {
        activeJobs,
      },
    };
  }

  async updateStatus(
    id: number,
    status: 'open' | 'rejected',
    adminId: number,
    remarks?: string,
  ) {
    await this.jobPostRepo.update(id, {
      status,
      admin_remarks: remarks || null,
      reviewed_by_admin_id: adminId,
      reviewed_at: new Date(),
    });

    // Trigger Job Alerts if approved
    if (status === 'open') {
      try {
        await this.dashboardService.createJobAlertsForNewPost(id);
      } catch (alertError) {
        console.error(
          'Failed to create job alerts after approval:',
          alertError,
        );
      }
    }
    // console.log(
    //   `Job ${id} ${status} by admin ${adminId}${remarks ? ` with remarks: ${remarks}` : ''}`,
    // );
    return { success: true, message: `Job ${status}` };
  }

  async updateJobStatus(
    id: number,
    status: string,
    adminId: number,
    remarks?: string,
  ) {
    // Validate status
    const validStatuses: (
      | 'open'
      | 'closed'
      | 'paused'
      | 'pending'
      | 'rejected'
    )[] = ['pending', 'open', 'rejected', 'closed', 'paused'];
    if (!validStatuses.includes(status as any)) {
      throw new Error(
        `Invalid status: ${status}. Valid statuses are: ${validStatuses.join(', ')}`,
      );
    }

    await this.jobPostRepo.update(id, {
      status: status as 'open' | 'closed' | 'paused' | 'pending' | 'rejected',
      admin_remarks: remarks || null,
      reviewed_by_admin_id: adminId,
      reviewed_at: new Date(),
    });

    // Trigger Job Alerts if approved/opened
    if (status === 'open') {
      try {
        await this.dashboardService.createJobAlertsForNewPost(id);
      } catch (alertError) {
        console.error(
          'Failed to create job alerts after status update:',
          alertError,
        );
      }
    }
    // console.log(
    //   `Job ${id} status updated to ${status} by admin ${adminId}${remarks ? ` with remarks: ${remarks}` : ''}`,
    // );
    return { success: true, message: `Job status updated to ${status}` };
  }

  async massApprovePending(adminId: number, remarks?: string) {
    // 1. Fetch IDs of pending jobs before update
    const pendingJobs = await this.dataSource
      .createQueryBuilder()
      .select('job.id', 'id')
      .from('job_posts', 'job')
      .where('job.status = :status', { status: 'pending' })
      .getRawMany();

    const jobIds = pendingJobs.map((j) => j.id);

    // 2. Perform bulk update
    const result = await this.dataSource
      .createQueryBuilder()
      .update('job_posts')
      .set({
        status: 'open',
        admin_remarks: remarks || null,
        reviewed_by_admin_id: adminId,
        reviewed_at: new Date(),
      })
      .where('status = :status', { status: 'pending' })
      .execute();

    // 3. Trigger Job Alerts for each approved job
    for (const id of jobIds) {
      try {
        await this.dashboardService.createJobAlertsForNewPost(id);
      } catch (alertError) {
        console.error(
          `Failed to create job alerts for mass-approved job ${id}:`,
          alertError,
        );
      }
    }

    return {
      success: true,
      message: `${result.affected} pending jobs approved successfully`,
      count: result.affected,
    };
  }

  // Get dynamic directory links for the frontend
  async getDirectoryLinks() {
    const topCities = await this.dataSource.query(
      `SELECT c.city_name as name, COUNT(jp.id) as count 
       FROM city c 
       JOIN job_posts jp ON jp.city_id = c.city_id 
       WHERE jp.status = 'open' 
       GROUP BY c.city_id 
       ORDER BY count DESC 
       LIMIT 60`
    );

    const topRoles = await this.dataSource.query(
      `SELECT r.name as name, COUNT(jp.id) as count 
       FROM roles r 
       JOIN job_posts jp ON jp.role_id = r.id 
       WHERE jp.status = 'open' 
       GROUP BY r.id 
       ORDER BY count DESC 
       LIMIT 60`
    );

    const topIndustries = await this.dataSource.query(
      `SELECT i.name as name, COUNT(jp.id) as count 
       FROM industries i 
       JOIN job_posts jp ON jp.industry_id = i.id 
       WHERE jp.status = 'open' 
       GROUP BY i.id 
       ORDER BY count DESC 
       LIMIT 60`
    );

    return [
      {
        title: "Find Jobs by City",
        links: topCities.map((c: any) => `Jobs in ${c.name}`)
      },
      {
        title: "Popular Job Roles",
        links: topRoles.map((r: any) => `${r.name} Jobs`)
      },
      {
        title: "Jobs by Industry",
        links: topIndustries.map((i: any) => `${i.name} Jobs`)
      }
    ];
  }
}
