import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { CACHE_MANAGER } from '@nestjs/cache-manager';
import { Cache } from 'cache-manager';
import { Inject } from '@nestjs/common';
import { JobPosts } from '../job_posts/entities/job_posts.entity';
import { JobApplications } from '../job_applications/entities/job_applications.entity';
import { Companies } from '../companies/entities/companies.entity';
import { UserEntity } from '../user/entities/user.entity';
import { JobViews } from '../job_views/entities/job_views.entity';
import { SeekerProfiles } from '../seeker_profiles/entities/seeker_profiles.entity';
import { City } from '../city/entities/city.entity';
import { Associates } from '../associates/entities/associates.entity';

@Injectable()
export class AnalyticsService {
  constructor(
    @InjectRepository(JobPosts)
    private jobPostsRepository: Repository<JobPosts>,
    @InjectRepository(JobApplications)
    private jobApplicationsRepository: Repository<JobApplications>,
    @InjectRepository(Companies)
    private companiesRepository: Repository<Companies>,
    @InjectRepository(UserEntity)
    private userRepository: Repository<UserEntity>,
    @InjectRepository(JobViews)
    private jobViewsRepository: Repository<JobViews>,
    @InjectRepository(SeekerProfiles)
    private seekerProfilesRepository: Repository<SeekerProfiles>,
    @InjectRepository(City)
    private cityRepository: Repository<City>,
    @InjectRepository(Associates)
    private associatesRepository: Repository<Associates>,
    @Inject(CACHE_MANAGER) private cacheManager: Cache,
  ) {}

  async getJobAnalytics(dateRange: number, franchiseId?: number) {
    // Check cache first
    const cacheKey = `job_analytics_${dateRange}${franchiseId ? `_franchise_${franchiseId}` : ''}`;
    const cachedData = await this.cacheManager.get(cacheKey);
    if (cachedData) {
      return cachedData as any;
    }

    // Debug logging
    // console.log(' Job Analytics Debug:', {
    //   dateRange,
    //   dateRangeType: typeof dateRange,
    //   franchiseId,
    //   cacheKey,
    // });

    const dateFilter = this.getDateFilter(dateRange, 'job.created_at');

    // Build base query with franchise filter if provided
    const baseQuery = this.jobPostsRepository
      .createQueryBuilder('job')
      .innerJoin('companies', 'c', 'c.id = job.company_id');

    // Only apply date filter if dateRange is specified and > 0
    if (dateRange > 0) {
      baseQuery.where(dateFilter);
    } else {
      // For all-time data, don't apply any date filter
      baseQuery.where('1=1');
    }

    if (franchiseId) {
      baseQuery.andWhere('c.franchise_id = :franchiseId', { franchiseId });
    }

    // Use Promise.all for parallel execution
    const [totalJobs, activeJobs, jobTrends, jobPostsWithStats] =
      await Promise.all([
        // Total jobs
        baseQuery.getCount(),

        // Active jobs (status = 'open')
        this.jobPostsRepository
          .createQueryBuilder('job')
          .innerJoin('companies', 'c', 'c.id = job.company_id')
          .where('job.status = :status', { status: 'open' })
          .andWhere(
            franchiseId ? 'c.franchise_id = :franchiseId' : '1=1',
            franchiseId ? { franchiseId } : {},
          )
          .andWhere(dateRange > 0 ? dateFilter : '1=1')
          .getCount(),

        // Job trends by month (dynamic based on dateRange)
        this.jobPostsRepository
          .createQueryBuilder('job')
          .innerJoin('companies', 'c', 'c.id = job.company_id')
          .select('DATE_FORMAT(job.created_at, "%Y-%m-01") as month')
          .addSelect('COUNT(*) as count')
          .where(dateRange > 0 ? dateFilter : '1=1')
          .andWhere(
            franchiseId ? 'c.franchise_id = :franchiseId' : '1=1',
            franchiseId ? { franchiseId } : {},
          )
          .groupBy('month')
          .orderBy('month', 'ASC')
          .getRawMany(),

        // Job posts with views and applications
        this.jobPostsRepository
          .createQueryBuilder('job')
          .innerJoin('companies', 'c', 'c.id = job.company_id')
          .leftJoin('job_applications', 'app', 'app.job_post_id = job.id')
          .leftJoin('job_views', 'jv', 'jv.job_post_id = job.id')
          .select([
            'job.id as job_id',
            'job.title as job_title',
            'job.status as job_status',
            'COUNT(DISTINCT jv.id) as views',
            'COUNT(app.id) as applications',
          ])
          .andWhere(
            franchiseId ? 'c.franchise_id = :franchiseId' : '1=1',
            franchiseId ? { franchiseId } : {},
          )
          .andWhere(dateRange > 0 ? dateFilter : '1=1')
          .groupBy('job.id, job.title, job.status')
          .orderBy('job.created_at', 'DESC')
          .limit(10)
          .getRawMany(),
      ]);

    // Debug logging for job trends
    // console.log(' Raw Job Trends from DB:', jobTrends);

    // Generate complete month range based on date range
    const completeJobTrends = this.generateCompleteMonthRange(
      dateRange,
      jobTrends,
      'job',
    );

    const result = {
      totalJobs,
      activeJobs,
      jobTrends: completeJobTrends,
      jobPostsWithStats: jobPostsWithStats.map((job) => ({
        id: job.job_id,
        title: job.job_title,
        status: job.job_status,
        views: parseInt(job.views) || 0,
        applications: parseInt(job.applications) || 0,
      })),
    };

    // console.log(' Processed Job Trends:', result.jobTrends);

    // Cache for 5 minutes
    await this.cacheManager.set(cacheKey, result, 300000);
    return result;
  }

  async getApplicationAnalytics(dateRange: number, franchiseId?: number) {
    // Check cache first
    const cacheKey = `application_analytics_${dateRange}${franchiseId ? `_franchise_${franchiseId}` : ''}`;
    const cachedData = await this.cacheManager.get(cacheKey);
    if (cachedData) {
      return cachedData as any;
    }

    // Debug logging
    // console.log(' Application Analytics Debug:', {
    //   dateRange,
    //   dateRangeType: typeof dateRange,
    //   franchiseId,
    //   cacheKey,
    // });

    const dateFilter = this.getDateFilter(dateRange, 'app.applied_at');
    const viewsDateFilter = this.getDateFilter(dateRange, 'jv.viewed_at');

    // Use Promise.all for parallel execution
    const [
      totalApplications,
      applicationsPerJob,
      totalViews,
      monthlyApplications,
    ] = await Promise.all([
      // Total applications
      this.jobApplicationsRepository
        .createQueryBuilder('app')
        .innerJoin('job_posts', 'job', 'job.id = app.job_post_id')
        .innerJoin('companies', 'c', 'c.id = job.company_id')
        .andWhere(
          franchiseId ? 'c.franchise_id = :franchiseId' : '1=1',
          franchiseId ? { franchiseId } : {},
        )
        .andWhere(dateRange > 0 ? dateFilter : '1=1')
        .getCount(),

      // Applications per job
      this.jobApplicationsRepository
        .createQueryBuilder('app')
        .innerJoin('job_posts', 'job', 'job.id = app.job_post_id')
        .innerJoin('companies', 'c', 'c.id = job.company_id')
        .select(['job.title as jobTitle', 'COUNT(app.id) as applications'])
        .andWhere(
          franchiseId ? 'c.franchise_id = :franchiseId' : '1=1',
          franchiseId ? { franchiseId } : {},
        )
        .andWhere(dateRange > 0 ? dateFilter : '1=1')
        .groupBy('app.job_post_id, job.title')
        .orderBy('applications', 'DESC')
        .limit(10)
        .getRawMany(),

      // Total views for conversion calculation
      this.jobViewsRepository
        .createQueryBuilder('jv')
        .innerJoin('job_posts', 'job', 'job.id = jv.job_post_id')
        .innerJoin('companies', 'c', 'c.id = job.company_id')
        .andWhere(
          franchiseId ? 'c.franchise_id = :franchiseId' : '1=1',
          franchiseId ? { franchiseId } : {},
        )
        .andWhere(dateRange > 0 ? viewsDateFilter : '1=1')
        .getCount(),

      // Monthly applications trend
      this.jobApplicationsRepository
        .createQueryBuilder('app')
        .innerJoin('job_posts', 'job', 'job.id = app.job_post_id')
        .innerJoin('companies', 'c', 'c.id = job.company_id')
        .select([
          'YEAR(app.applied_at) as year',
          'MONTH(app.applied_at) as month',
          'COUNT(app.id) as count',
        ])
        .andWhere(
          franchiseId ? 'c.franchise_id = :franchiseId' : '1=1',
          franchiseId ? { franchiseId } : {},
        )
        .andWhere(dateRange > 0 ? dateFilter : '1=1')
        .groupBy('YEAR(app.applied_at), MONTH(app.applied_at)')
        .orderBy('year', 'ASC')
        .addOrderBy('month', 'ASC')
        .getRawMany(),
    ]);

    const conversionRate =
      totalViews > 0 ? (totalApplications / totalViews) * 100 : 0;

    // Debug logging for monthly applications
    // console.log(' Raw Monthly Applications from DB:', monthlyApplications);

    // Generate complete month range based on date range
    const completeMonthlyApplications = this.generateCompleteMonthRange(
      dateRange,
      monthlyApplications,
      'application',
    );

    const result = {
      totalApplications,
      conversionRate: Math.round(conversionRate * 100) / 100,
      applicationsPerJob: applicationsPerJob.map((job) => ({
        jobTitle: job.jobTitle,
        applicationCount: parseInt(job.applications),
      })),
      monthlyApplications: completeMonthlyApplications,
    };

    // console.log(
    //   ' Processed Monthly Applications:',
    //   result.monthlyApplications,
    // );

    // Debug logging
    // console.log(' Application Analytics Result:');
    // console.log('Total Applications:', totalApplications);
    // console.log('Monthly Applications Raw:', monthlyApplications);
    // console.log('Monthly Applications Processed:', result.monthlyApplications);

    // Cache for 5 minutes
    await this.cacheManager.set(cacheKey, result, 300000);
    return result;
  }

  async getCompanyAnalytics(dateRange?: number, franchiseId?: number) {
    // Check cache first (temporarily disabled to force refresh)
    const cacheKey = `company_analytics_v4_${dateRange || 'all'}${franchiseId ? `_franchise_${franchiseId}` : ''}`;
    const cachedData = await this.cacheManager.get(cacheKey);
    if (cachedData) {
      return cachedData as any;
    }

    // For admin analytics (no franchiseId), get data from all franchises
    // For franchise analytics, validate franchiseId
    if (franchiseId !== undefined && (!franchiseId || isNaN(franchiseId))) {
      console.error('Invalid franchiseId:', franchiseId);
      return {
        activeCompanies: 0,
        inactiveCompanies: 0,
        companiesWithActiveJobs: 0,
        companiesWithJobs: [],
      };
    }
    // For franchise analytics:
    // - Active/Inactive companies: Show current status, not filtered by creation date
    // - Companies with jobs: Apply date filter to job creation, not company creation
    const jobDateFilter = dateRange
      ? this.getDateFilter(dateRange, 'job.created_at')
      : '1=1';

    // Active companies (current status, not filtered by creation date)
    const activeCompaniesQuery = this.companiesRepository
      .createQueryBuilder('company')
      .where('company.status = :status', { status: 1 })
      .andWhere('company.is_deleted = 0')
      .andWhere(
        franchiseId ? 'company.franchise_id = :franchiseId' : '1=1',
        franchiseId ? { franchiseId } : {},
      );

    const activeCompanies = await activeCompaniesQuery.getCount();

    // Debug logging
    // console.log('Company Analytics Debug:', {
    //   franchiseId,
    //   activeCompanies,
    //   query: activeCompaniesQuery.getSql(),
    //   parameters: activeCompaniesQuery.getParameters()
    // });

    // Inactive companies (current status, not filtered by creation date)
    const inactiveCompanies = await this.companiesRepository
      .createQueryBuilder('company')
      .where('company.status = :status', { status: 0 })
      .andWhere('company.is_deleted = 0')
      .andWhere(
        franchiseId ? 'company.franchise_id = :franchiseId' : '1=1',
        franchiseId ? { franchiseId } : {},
      )
      .getCount();

    // Companies with job posts (filtered by job creation date)
    const companiesWithJobs = await this.companiesRepository
      .createQueryBuilder('company')
      .leftJoin('job_posts', 'job', 'job.company_id = company.id')
      .select(['company.name as companyName', 'COUNT(job.id) as jobCount'])
      .where('company.is_deleted = 0')
      .andWhere('company.status = :status', { status: 1 }) // Only active companies
      .andWhere(jobDateFilter) // Apply date filter to job creation
      .andWhere(
        franchiseId ? 'company.franchise_id = :franchiseId' : '1=1',
        franchiseId ? { franchiseId } : {},
      )
      .groupBy('company.id, company.name')
      .having('jobCount > 0')
      .orderBy('jobCount', 'DESC')
      .limit(10)
      .getRawMany();

    // Count of companies that have posted jobs in the date range
    const companiesWithActiveJobs = await this.companiesRepository
      .createQueryBuilder('company')
      .innerJoin('job_posts', 'job', 'job.company_id = company.id')
      .where('company.is_deleted = 0')
      .andWhere('company.status = :status', { status: 1 })
      .andWhere(jobDateFilter)
      .andWhere(
        franchiseId ? 'company.franchise_id = :franchiseId' : '1=1',
        franchiseId ? { franchiseId } : {},
      )
      .distinct()
      .getCount();

    const result = {
      activeCompanies,
      inactiveCompanies,
      companiesWithActiveJobs, // New metric for companies with jobs in date range
      companiesWithJobs: companiesWithJobs.map((company) => ({
        companyName: company.companyName,
        jobCount: parseInt(company.jobCount),
      })),
    };

    // Cache for 5 minutes
    await this.cacheManager.set(cacheKey, result, 300000);
    return result;
  }

  async getUserAnalytics(dateRange: number, franchiseId?: number) {
    const dateFilter = this.getDateFilter(dateRange, 'user.createdAt');

    // Total users count (all users for global, franchise-applied users for franchise)
    const totalUsers = franchiseId
      ? await this.userRepository
          .createQueryBuilder('user')
          .innerJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
          .innerJoin('job_applications', 'ja', 'ja.seeker_id = sp.id')
          .innerJoin('job_posts', 'jp', 'jp.id = ja.job_post_id')
          .innerJoin('companies', 'c', 'c.id = jp.company_id')
          .where('user.status = :status', { status: 'active' })
          .andWhere('c.franchise_id = :franchiseId', { franchiseId })
          .andWhere(
            dateRange > 0
              ? `ja.applied_at >= DATE_SUB(NOW(), INTERVAL ${dateRange} DAY)`
              : '1=1',
          )
          .distinct()
          .getCount()
      : await this.userRepository
          .createQueryBuilder('user')
          .where('user.status = :status', { status: 'active' })
          .andWhere(dateRange > 0 ? dateFilter : '1=1')
          .getCount();

    // Active users (users who applied to franchise companies)
    const activeUsers = franchiseId
      ? await this.userRepository
          .createQueryBuilder('user')
          .innerJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
          .innerJoin('job_applications', 'ja', 'ja.seeker_id = sp.id')
          .innerJoin('job_posts', 'jp', 'jp.id = ja.job_post_id')
          .innerJoin('companies', 'c', 'c.id = jp.company_id')
          .where('user.status = :status', { status: 'active' })
          .andWhere('c.franchise_id = :franchiseId', { franchiseId })
          .andWhere(
            dateRange > 0
              ? `ja.applied_at >= DATE_SUB(NOW(), INTERVAL ${dateRange} DAY)`
              : '1=1',
          )
          .distinct()
          .getCount()
      : await this.userRepository
          .createQueryBuilder('user')
          .where('user.status = :status', { status: 'active' })
          .andWhere(dateRange > 0 ? dateFilter : '1=1')
          .getCount();

    // New users this month (who applied to franchise companies)
    const newUsersThisMonth = franchiseId
      ? await this.userRepository
          .createQueryBuilder('user')
          .innerJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
          .innerJoin('job_applications', 'ja', 'ja.seeker_id = sp.id')
          .innerJoin('job_posts', 'jp', 'jp.id = ja.job_post_id')
          .innerJoin('companies', 'c', 'c.id = jp.company_id')
          .where('user.status = :status', { status: 'active' })
          .andWhere('c.franchise_id = :franchiseId', { franchiseId })
          .andWhere('ja.applied_at >= DATE_FORMAT(NOW(), "%Y-%m-01")')
          .distinct()
          .getCount()
      : await this.userRepository
          .createQueryBuilder('user')
          .where('user.status = :status', { status: 'active' })
          .andWhere('user.createdAt >= DATE_FORMAT(NOW(), "%Y-%m-01")')
          .getCount();

    // New users per month (dynamic based on dateRange) - who applied to franchise companies
    const newUsersPerMonth = franchiseId
      ? await this.userRepository
          .createQueryBuilder('user')
          .innerJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
          .innerJoin('job_applications', 'ja', 'ja.seeker_id = sp.id')
          .innerJoin('job_posts', 'jp', 'jp.id = ja.job_post_id')
          .innerJoin('companies', 'c', 'c.id = jp.company_id')
          .select('DATE_FORMAT(ja.applied_at, "%Y-%m-01") as month')
          .addSelect('COUNT(DISTINCT user.id) as count')
          .where(
            dateRange > 0
              ? `ja.applied_at >= DATE_SUB(NOW(), INTERVAL ${dateRange} DAY)`
              : '1=1',
          )
          .andWhere('c.franchise_id = :franchiseId', { franchiseId })
          .andWhere('user.status = :status', { status: 'active' })
          .groupBy('month')
          .orderBy('month', 'ASC')
          .getRawMany()
      : await this.userRepository
          .createQueryBuilder('user')
          .select('DATE_FORMAT(user.createdAt, "%Y-%m-01") as month')
          .addSelect('COUNT(*) as count')
          .where(
            dateRange > 0
              ? `user.createdAt >= DATE_SUB(NOW(), INTERVAL ${dateRange} DAY)`
              : '1=1',
          )
          .andWhere('user.status = :status', { status: 'active' })
          .groupBy('month')
          .orderBy('month', 'ASC')
          .getRawMany();

    // Users by role (who applied to franchise companies)
    const usersByRole = franchiseId
      ? await this.userRepository
          .createQueryBuilder('user')
          .innerJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
          .innerJoin('job_applications', 'ja', 'ja.seeker_id = sp.id')
          .innerJoin('job_posts', 'jp', 'jp.id = ja.job_post_id')
          .innerJoin('companies', 'c', 'c.id = jp.company_id')
          .select('user.role as role')
          .addSelect('COUNT(DISTINCT user.id) as count')
          .where('c.franchise_id = :franchiseId', { franchiseId })
          .andWhere('user.status = :status', { status: 'active' })
          .groupBy('user.role')
          .getRawMany()
      : await this.userRepository
          .createQueryBuilder('user')
          .select('user.role as role')
          .addSelect('COUNT(*) as count')
          .where('user.status = :status', { status: 'active' })
          .groupBy('user.role')
          .getRawMany();

    return {
      totalUsers,
      activeUsers,
      newUsersThisMonth,
      newUsersPerMonth: newUsersPerMonth.map((month) => {
        const date = new Date(month.month);
        return {
          month: date.getMonth() + 1, // JavaScript months are 0-indexed
          year: date.getFullYear(),
          count: parseInt(month.count),
        };
      }),
      usersByRole: usersByRole.map((role) => ({
        role: role.role,
        count: parseInt(role.count),
      })),
    };
  }

  async getOverview(dateRange: number) {
    // Check cache first
    const cacheKey = `analytics_overview_${dateRange}`;
    const cachedData = await this.cacheManager.get(cacheKey);
    if (cachedData) {
      return cachedData as any;
    }

    const [
      jobAnalytics,
      applicationAnalytics,
      companyAnalytics,
      userAnalytics,
    ] = await Promise.all([
      this.getJobAnalytics(dateRange),
      this.getApplicationAnalytics(dateRange),
      this.getCompanyAnalytics(),
      this.getUserAnalytics(dateRange),
    ]);

    const result = {
      kpi: {
        totalJobs: jobAnalytics.totalJobs,
        totalApplications: applicationAnalytics.totalApplications,
        activeCompanies: companyAnalytics.activeCompanies,
        activeUsers: userAnalytics.activeUsers,
      },
      jobTrends: jobAnalytics.jobTrends,
      applicationsPerJob: applicationAnalytics.applicationsPerJob,
      jobPostsWithStats: jobAnalytics.jobPostsWithStats,
    };

    // Cache for 5 minutes
    await this.cacheManager.set(cacheKey, result, 300000);
    return result;
  }

  // ═══════════════════════════════════════════════════════════════════
  // CITY-WISE JOB ANALYTICS
  // ═══════════════════════════════════════════════════════════════════

  /**
   * Get cities with active jobs with pagination
   * Supports search by city name and optional date filtering
   */
  async getCityWiseActiveJobs(
    page: number = 1,
    limit: number = 20,
    searchQuery?: string,
    dateRange?: number,
    fromDate?: string,
    toDate?: string,
  ) {
    const offset = (page - 1) * limit;
    const { filter: dateFilter, params: dateParams } = this.buildDateFilter(
      dateRange,
      'job.created_at',
      fromDate,
      toDate,
    );

    // Build the base query for cities with active jobs
    let query = this.cityRepository
      .createQueryBuilder('city')
      .innerJoin(
        'job_posts',
        'job',
        'job.city_id = city.city_id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .select('city.city_id', 'cityId')
      .addSelect('city.city_name', 'cityName')
      .addSelect('city.state_id', 'stateId')
      .addSelect('COUNT(DISTINCT job.id)', 'totalActiveJobs')
      .andWhere(dateFilter, dateParams);

    if (searchQuery && searchQuery.trim()) {
      query = query.andWhere('city.city_name LIKE :search', {
        search: `%${searchQuery}%`,
      });
    }

    query = query
      .groupBy('city.city_id, city.city_name, city.state_id')
      .orderBy('totalActiveJobs', 'DESC');

    // Get total count for pagination
    const countQuery = this.cityRepository
      .createQueryBuilder('city')
      .innerJoin(
        'job_posts',
        'job',
        'job.city_id = city.city_id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .andWhere(dateFilter, dateParams);

    if (searchQuery && searchQuery.trim()) {
      countQuery.andWhere('city.city_name LIKE :search', {
        search: `%${searchQuery}%`,
      });
    }

    const totalCount = await countQuery.distinct(true).getCount();

    // Get paginated results
    const cities = await query.limit(limit).offset(offset).getRawMany();

    return {
      data: cities.map((city) => ({
        cityId: city.cityId,
        cityName: city.cityName,
        stateId: city.stateId,
        totalActiveJobs: parseInt(city.totalActiveJobs),
      })),
      pagination: {
        page,
        limit,
        totalCount,
        totalPages: Math.ceil(totalCount / limit),
      },
    };
  }

  /**
   * Get detailed jobs in a specific city
   * Shows Job Title, Posted By (Associate/Company), Date & Time
   */
  async getJobsInCity(
    cityId: number,
    page: number = 1,
    limit: number = 20,
    dateRange?: number,
    fromDate?: string,
    toDate?: string,
  ) {
    const offset = (page - 1) * limit;
    const { filter: dateFilter, params: dateParams } = this.buildDateFilter(
      dateRange,
      'job.created_at',
      fromDate,
      toDate,
    );

    // Query to get jobs with posted by information using joins
    const jobs = await this.jobPostsRepository
      .createQueryBuilder('job')
      .leftJoin('companies', 'company', 'company.id = job.company_id')
      .leftJoin('associates', 'associate', 'associate.id = job.associate_id')
      .leftJoin('users', 'u', 'u.id = associate.user_id')
      .where('job.city_id = :cityId', { cityId })
      .andWhere('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .andWhere(dateFilter, dateParams)
      .select([
        'job.id as jobId',
        'job.title as jobTitle',
        'job.created_at as postedDate',
        'job.company_id as companyId',
        'job.associate_id as associateId',
        // Logic to determine postedBy and postedByType
        'CASE WHEN job.associate_id IS NOT NULL THEN u.full_name ELSE company.name END as postedBy',
        'CASE WHEN job.associate_id IS NOT NULL THEN "Associate" ELSE "Recruiter" END as postedByType',
      ])
      .orderBy('job.created_at', 'DESC')
      .limit(limit)
      .offset(offset)
      .getRawMany();

    const enrichedJobs = jobs.map((job) => ({
      jobId: job.jobId,
      jobTitle: job.jobTitle,
      postedBy: job.postedBy || 'User',
      postedByType: job.postedByType,
      postedDate: job.postedDate,
      companyId: job.companyId,
      associateId: job.associateId,
    }));

    // Get total count
    const totalCountQuery = this.jobPostsRepository
      .createQueryBuilder('job')
      .where('job.city_id = :cityId', { cityId })
      .andWhere('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .andWhere(dateFilter, dateParams);

    const totalCount = await totalCountQuery.getCount();

    // Get city details
    const city = await this.cityRepository.findOne({
      where: { city_id: cityId },
    });

    return {
      cityId,
      cityName: city?.city_name || 'Unknown',
      data: enrichedJobs,
      pagination: {
        page,
        limit,
        totalCount,
        totalPages: Math.ceil(totalCount / limit),
      },
    };
  }

  /**
   * Get cities with NO active jobs
   * Supports search and pagination
   */
  async getCitiesWithoutJobs(
    page: number = 1,
    limit: number = 20,
    searchQuery?: string,
  ) {
    const offset = (page - 1) * limit;

    // Get all cities
    let citiesQuery = this.cityRepository.createQueryBuilder('city');

    if (searchQuery && searchQuery.trim()) {
      citiesQuery = citiesQuery.where('city.city_name LIKE :search', {
        search: `%${searchQuery}%`,
      });
    }

    // Get cities that have no active job posts
    const citiesWithActiveJobs = await this.jobPostsRepository
      .createQueryBuilder('job')
      .select('DISTINCT job.city_id', 'city_id')
      .where('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .getRawMany();

    const activeJobCityIds = citiesWithActiveJobs.map((c) => c.city_id);

    // Get cities without active jobs
    if (activeJobCityIds.length > 0) {
      citiesQuery = citiesQuery.andWhere(
        'city.city_id NOT IN (:...activeJobCityIds)',
        {
          activeJobCityIds,
        },
      );
    }

    // Get total count
    const totalCountQuery = this.cityRepository.createQueryBuilder('city');
    if (searchQuery && searchQuery.trim()) {
      totalCountQuery.where('city.city_name LIKE :search', {
        search: `%${searchQuery}%`,
      });
    }
    if (activeJobCityIds.length > 0) {
      totalCountQuery.andWhere('city.city_id NOT IN (:...activeJobCityIds)', {
        activeJobCityIds,
      });
    }
    const totalCount = await totalCountQuery.getCount();

    // Get paginated results
    const cities = await citiesQuery
      .orderBy('city.city_name', 'ASC')
      .limit(limit)
      .offset(offset)
      .getMany();

    return {
      data: cities.map((city) => ({
        cityId: city.city_id,
        cityName: city.city_name,
        stateId: city.state_id,
      })),
      pagination: {
        page,
        limit,
        totalCount,
        totalPages: Math.ceil(totalCount / limit),
      },
    };
  }

  /**
   * Export cities with active jobs as CSV
   * Supports date filtering and search
   */
  async exportCityWiseActiveJobsCSV(
    format: 'summary' | 'detailed' = 'summary',
    searchQuery?: string,
    dateRange?: number,
    fromDate?: string,
    toDate?: string,
  ): Promise<string> {
    const { filter: dateFilter, params: dateParams } = this.buildDateFilter(
      dateRange,
      'job.created_at',
      fromDate,
      toDate,
    );

    let query = this.cityRepository
      .createQueryBuilder('city')
      .innerJoin(
        'job_posts',
        'job',
        'job.city_id = city.city_id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .andWhere(dateFilter, dateParams);

    if (searchQuery && searchQuery.trim()) {
      query = query.andWhere('city.city_name LIKE :search', {
        search: `%${searchQuery}%`,
      });
    }

    if (format === 'summary') {
      // Summary format: City, Total Jobs
      const cities = await query
        .select('city.city_name', 'cityName')
        .addSelect('COUNT(DISTINCT job.id)', 'totalJobs')
        .groupBy('city.city_id, city.city_name')
        .orderBy('totalJobs', 'DESC')
        .getRawMany();

      // Generate CSV
      let csv = 'City,Total Jobs\n';
      for (const city of cities) {
        csv += `"${city.cityName}",${city.totalJobs}\n`;
      }
      return csv;
    } else {
      // Detailed format: City, Job Title, Posted By, Posted Date
      const jobsQuery = this.jobPostsRepository
        .createQueryBuilder('job')
        .innerJoin('city', 'city', 'city.city_id = job.city_id')
        .where('job.status = :status', { status: 'open' })
        .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
        .andWhere(dateFilter, dateParams)
        .select([
          'city.city_name AS cityName',
          'job.title AS jobTitle',
          'job.company_id AS companyId',
          'job.associate_id AS associateId',
          'job.created_at AS createdAt',
        ]);

      if (searchQuery && searchQuery.trim()) {
        jobsQuery.andWhere('city.city_name LIKE :search', {
          search: `%${searchQuery}%`,
        });
      }

      const jobs = await jobsQuery
        .orderBy('city.city_name', 'ASC')
        .addOrderBy('job.created_at', 'DESC')
        .getRawMany();

      // Enrich with posted by information
      let csv = 'City,Job Title,Posted By,Posted Date\n';

      for (const job of jobs) {
        let postedBy = 'Unknown';

        if (job.associateId) {
          const associate = await this.associatesRepository.findOne({
            where: { id: job.associateId },
          });
          if (associate) {
            const user = await this.userRepository.findOne({
              where: { id: associate.user_id },
            });
            postedBy = user?.fullName || 'Associate';
          }
        } else if (job.companyId) {
          const company = await this.companiesRepository.findOne({
            where: { id: job.companyId },
          });
          postedBy = company?.name || 'Company';
        }

        const postedDate = new Date(job.createdAt).toLocaleString();
        csv += `"${job.cityName || 'Unknown'}","${job.jobTitle}","${postedBy}","${postedDate}"\n`;
      }
      return csv;
    }
  }

  /**
   * Export cities with no jobs as CSV
   * Respects search filter
   */
  async exportCitiesWithoutJobsCSV(searchQuery?: string): Promise<string> {
    // Get all cities without active jobs
    let citiesQuery = this.cityRepository.createQueryBuilder('city');

    if (searchQuery && searchQuery.trim()) {
      citiesQuery = citiesQuery.where('city.city_name LIKE :search', {
        search: `%${searchQuery}%`,
      });
    }

    // Get cities that have no active job posts
    const citiesWithActiveJobs = await this.jobPostsRepository
      .createQueryBuilder('job')
      .select('DISTINCT job.city_id', 'city_id')
      .where('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .getRawMany();

    const activeJobCityIds = citiesWithActiveJobs.map((c) => c.city_id);

    // Get cities without active jobs
    if (activeJobCityIds.length > 0) {
      citiesQuery = citiesQuery.andWhere(
        'city.city_id NOT IN (:...activeJobCityIds)',
        {
          activeJobCityIds,
        },
      );
    }

    const cities = await citiesQuery.orderBy('city.city_name', 'ASC').getMany();

    // Generate CSV
    let csv = 'City\n';
    for (const city of cities) {
      csv += `"${city.city_name}"\n`;
    }
    return csv;
  }

  private generateCompleteMonthRange(
    dateRange: number,
    data: any[],
    type: 'job' | 'application',
  ): any[] {
    const now = new Date();
    const monthsToShow = Math.ceil(dateRange / 30); // Convert days to months

    // Generate complete month range
    const completeRange = [];
    for (let i = monthsToShow - 1; i >= 0; i--) {
      const date = new Date(now.getFullYear(), now.getMonth() - i, 1);
      const month = date.getMonth() + 1;
      const year = date.getFullYear();

      // Find existing data for this month
      let existingData = null;
      if (type === 'job') {
        existingData = data.find((item) => {
          const itemDate = new Date(item.month);
          return (
            itemDate.getMonth() + 1 === month && itemDate.getFullYear() === year
          );
        });
      } else {
        existingData = data.find(
          (item) =>
            parseInt(item.month) === month && parseInt(item.year) === year,
        );
      }

      if (type === 'job') {
        completeRange.push({
          month,
          year,
          count: existingData ? parseInt(existingData.count) : 0,
        });
      } else {
        completeRange.push({
          year,
          month,
          count: existingData ? parseInt(existingData.count) : 0,
        });
      }
    }

    // console.log(` Generated complete ${type} month range:`, {
    //   dateRange,
    //   monthsToShow,
    //   completeRange,
    // });

    return completeRange;
  }

  private buildDateFilter(
    dateRange: number | undefined,
    dateField: string = 'created_at',
    fromDate?: string,
    toDate?: string,
  ): { filter: string; params: Record<string, any> } {
    if (fromDate && toDate) {
      return {
        filter: `${dateField} BETWEEN :fromDate AND :toDate`,
        params: {
          fromDate: this.normalizeDateParam(fromDate),
          toDate: this.normalizeDateParam(toDate, true),
        },
      };
    }

    if (fromDate) {
      return {
        filter: `${dateField} >= :fromDate`,
        params: { fromDate: this.normalizeDateParam(fromDate) },
      };
    }

    if (toDate) {
      return {
        filter: `${dateField} <= :toDate`,
        params: { toDate: this.normalizeDateParam(toDate, true) },
      };
    }

    if (dateRange && dateRange > 0) {
      return {
        filter: `${dateField} >= DATE_SUB(NOW(), INTERVAL ${dateRange} DAY)`,
        params: {},
      };
    }

    return { filter: '1=1', params: {} };
  }

  private normalizeDateParam(date: string, endOfDay: boolean = false): string {
    const trimmed = date.trim();
    if (/^\d{4}-\d{2}-\d{2}$/.test(trimmed)) {
      return `${trimmed} ${endOfDay ? '23:59:59' : '00:00:00'}`;
    }
    return trimmed;
  }

  private getDateFilter(
    dateRange: number,
    dateField: string = 'created_at',
  ): string {
    return this.buildDateFilter(dateRange, dateField).filter;
  }
}
