import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource } from 'typeorm';
import { Associates } from './entities/associates.entity';
import { UserEntity } from '../user/entities/user.entity';
import { SkillsService } from '../skills/skills.service';
import { TagsService } from '../tags/tags.service';
import { JobsPostTagService } from '../jobs_post_tag/jobs_post_tag.service';
import { WalletService } from '../wallet/wallet.service';
import { EmailService } from '../email/email.service';

@Injectable()
export class AssociatesService {
  constructor(
    @InjectRepository(Associates)
    private readonly associatesRepo: Repository<Associates>,
    @InjectRepository(UserEntity)
    private readonly userRepo: Repository<UserEntity>,
    private readonly dataSource: DataSource,
    private readonly skillsService: SkillsService,
    private readonly tagsService: TagsService,
    private readonly jobsPostTagService: JobsPostTagService,
    private readonly walletService: WalletService,
    private readonly emailService: EmailService,
  ) {}

  // Get all associates with pagination
  async getAllAssociates(page: number = 1, limit: number = 10) {
    try {
      const safePage =
        isNaN(Number(page)) || Number(page) < 1 ? 1 : Number(page);
      const safeLimit =
        isNaN(Number(limit)) || Number(limit) < 1 ? 10 : Number(limit);
      const offset = (safePage - 1) * safeLimit;

      // Get associates with user details
      const query = `
        SELECT 
          a.id as associate_id,
          a.user_id,
          a.country_id,
          a.state_id,
          a.city_id,
          a.area_id,
          a.address,
          a.pincode,
          a.latitude,
          a.longitude,
          a.status,
          a.created_by,
          a.created_at,
          a.updated_at,
          u.full_name,
          u.email,
          u.phone,
          u.username,
          u.password,
          u.status as user_status,
          co.country_name,
          s.state_name,
          c.city_name,
          ar.area as area_name
        FROM associates a
        INNER JOIN users u ON a.user_id = u.id
        LEFT JOIN country co ON a.country_id = co.country_id
        LEFT JOIN state s ON a.state_id = s.state_id
        LEFT JOIN city c ON a.city_id = c.city_id
        LEFT JOIN area ar ON a.area_id = ar.id
        ORDER BY a.created_at DESC
        LIMIT ? OFFSET ?
      `;

      const associates = await this.dataSource.query(query, [
        safeLimit,
        offset,
      ]);

      // Get total count
      const countQuery = `SELECT COUNT(*) as total FROM associates`;
      const [{ total }] = await this.dataSource.query(countQuery);

      // Get stats
      const statsQuery = `
        SELECT 
          COUNT(*) as total_associates,
          SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) as active_associates,
          SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) as inactive_associates
        FROM associates
      `;
      const [stats] = await this.dataSource.query(statsQuery);

      // Get job posts count for associates
      const jobPostsQuery = `
        SELECT 
          COUNT(*) as total_job_posts,
          SUM(CASE WHEN status = 'open' AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP) THEN 1 ELSE 0 END) as active_job_posts
        FROM job_posts
        WHERE associate_id IS NOT NULL
      `;
      const [jobStats] = await this.dataSource.query(jobPostsQuery);

      return {
        success: true,
        data: associates,
        currentPage: safePage,
        totalPages: Math.ceil(total / safeLimit),
        totalItems: total,
        stats: {
          totalAssociates: parseInt(stats.total_associates || 0),
          activeAssociates: parseInt(stats.active_associates || 0),
          inactiveAssociates: parseInt(stats.inactive_associates || 0),
          totalJobPosts: parseInt(jobStats.total_job_posts || 0),
          activeJobPosts: parseInt(jobStats.active_job_posts || 0),
        },
      };
    } catch (error) {
      console.error('Error in getAllAssociates:', error);
      throw error;
    }
  }

  // Get associate by ID with details
  async getAssociateById(id: number) {
    try {
      const query = `
        SELECT 
          a.id as associate_id,
          a.user_id,
          a.country_id,
          a.state_id,
          a.city_id,
          a.area_id,
          a.address,
          a.pincode,
          a.latitude,
          a.longitude,
          a.status,
          a.created_by,
          a.created_at,
          a.updated_at,
          u.full_name,
          u.email,
          u.phone,
          u.username,
          u.password,
          u.status as user_status,
          co.country_name,
          s.state_name,
          c.city_name,
          ar.area as area_name,
          creator.full_name as created_by_name
        FROM associates a
        INNER JOIN users u ON a.user_id = u.id
        LEFT JOIN country co ON a.country_id = co.country_id
        LEFT JOIN state s ON a.state_id = s.state_id
        LEFT JOIN city c ON a.city_id = c.city_id
        LEFT JOIN area ar ON a.area_id = ar.id
        LEFT JOIN users creator ON a.created_by = creator.id
        WHERE a.id = ?
        LIMIT 1
      `;

      const [associate] = await this.dataSource.query(query, [id]);

      if (!associate) {
        throw new NotFoundException('Associate not found');
      }

      const [{ total_job_posts, active_job_posts }] =
        await this.dataSource.query(
          `SELECT COUNT(*) as total_job_posts,
                COUNT(CASE WHEN status = 'open' AND (expires_at IS NULL OR expires_at > CURRENT_TIMESTAMP) THEN 1 END) as active_job_posts
         FROM job_posts WHERE associate_id = ?`,
          [id],
        );

      const [applications] = await this.dataSource.query(
        `SELECT COUNT(*) as total_applications,
                COUNT(CASE WHEN ja.status = 'Applied' THEN 1 END) as applied_count,
                COUNT(CASE WHEN ja.status = 'Shortlisted' THEN 1 END) as shortlisted_count,
                COUNT(CASE WHEN ja.status = 'Interview' THEN 1 END) as interview_count,
                COUNT(CASE WHEN ja.status = 'Hired' THEN 1 END) as hired_count,
                COUNT(CASE WHEN ja.status = 'Rejected' THEN 1 END) as rejected_count
         FROM job_applications ja
         INNER JOIN job_posts jp ON ja.job_post_id = jp.id
         INNER JOIN seeker_profiles sp ON ja.seeker_id = sp.id
         INNER JOIN users u ON sp.user_id = u.id
         WHERE jp.associate_id = ?`,
        [id],
      );

      const [{ saved_candidates }] = await this.dataSource.query(
        `SELECT COUNT(*) as saved_candidates FROM saved_candidates WHERE associate_id = ? AND is_deleted = 0`,
        [id],
      );

      return {
        success: true,
        data: {
          associate,
          stats: {
            totalJobPosts: parseInt(total_job_posts || 0),
            activeJobPosts: parseInt(active_job_posts || 0),
            totalApplications: parseInt(applications?.total_applications || 0),
            appliedCount: parseInt(applications?.applied_count || 0),
            shortlistedCount: parseInt(applications?.shortlisted_count || 0),
            interviewCount: parseInt(applications?.interview_count || 0),
            hiredCount: parseInt(applications?.hired_count || 0),
            rejectedCount: parseInt(applications?.rejected_count || 0),
            savedCandidates: parseInt(saved_candidates || 0),
          },
        },
      };
    } catch (error) {
      console.error('Error in getAssociateById:', error);
      throw error;
    }
  }

  // Get associate by user_id with details
  async getAssociateByUserId(userId: number) {
    try {
      const query = `
        SELECT 
          a.id as associate_id,
          a.user_id,
          a.country_id,
          a.state_id,
          a.city_id,
          a.area_id,
          a.address,
          a.pincode,
          a.latitude,
          a.longitude,
          a.status,
          a.created_by,
          a.created_at,
          a.updated_at,
          u.full_name,
          u.email,
          u.phone,
          u.username,
          u.status as user_status,
          co.country_name,
          s.state_name,
          c.city_name,
          ar.area as area_name,
          creator.full_name as created_by_name
        FROM associates a
        INNER JOIN users u ON a.user_id = u.id
        LEFT JOIN country co ON a.country_id = co.country_id
        LEFT JOIN state s ON a.state_id = s.state_id
        LEFT JOIN city c ON a.city_id = c.city_id
        LEFT JOIN area ar ON a.area_id = ar.id
        LEFT JOIN users creator ON a.created_by = creator.id
        WHERE a.user_id = ?
        LIMIT 1
      `;

      const [associate] = await this.dataSource.query(query, [userId]);

      if (!associate) {
        throw new NotFoundException('Associate not found for this user');
      }

      return {
        success: true,
        data: associate,
      };
    } catch (error) {
      console.error('Error in getAssociateByUserId:', error);
      throw error;
    }
  }

  // Create associate (user + associate record)
  async createAssociate(payload: any, adminId: number) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // Check if email already exists
      const existingUser = await queryRunner.manager
        .createQueryBuilder()
        .select('id')
        .from(UserEntity, 'u')
        .where('u.email = :email', { email: payload.email })
        .getOne();

      if (existingUser) {
        throw new Error('Email already exists');
      }

      // Check if username already exists
      const existingUsername = await queryRunner.manager
        .createQueryBuilder()
        .select('id')
        .from(UserEntity, 'u')
        .where('u.username = :username', { username: payload.username })
        .getOne();

      if (existingUsername) {
        throw new Error('Username already exists');
      }

      // Create user
      const insertUser = await queryRunner.manager
        .createQueryBuilder()
        .insert()
        .into(UserEntity)
        .values([
          {
            fullName: payload.full_name,
            username: payload.username,
            email: payload.email,
            phone: payload.phone,
            password: payload.password, // Plain password - no bcrypt as per requirement
            role: 'associate',
            status: 'active',
            phoneVerified: false,
            authMethod: 'password', // Associates use password auth like admin
          },
        ])
        .execute();

      const userId = insertUser.identifiers[0].id;

      // Create associate record
      const insertAssociate = await queryRunner.manager
        .createQueryBuilder()
        .insert()
        .into(Associates)
        .values([
          {
            user_id: userId,
            country_id: payload.country_id || null,
            state_id: payload.state_id || null,
            city_id: payload.city_id || null,
            area_id: payload.area_id || null,
            address: payload.address || null,
            pincode: payload.pincode || null,
            latitude: payload.latitude ? Number(payload.latitude) : null,
            longitude: payload.longitude ? Number(payload.longitude) : null,
            created_by: adminId,
            status: payload.status !== undefined ? payload.status : 1,
          },
        ])
        .execute();

      await queryRunner.commitTransaction();

      return {
        success: true,
        message: 'Associate created successfully',
        data: {
          associate_id: insertAssociate.identifiers[0].id,
          user_id: userId,
        },
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      console.error('Error in createAssociate:', error);
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  // Update associate
  async updateAssociate(id: number, payload: any) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // Get associate
      const associate = await queryRunner.manager
        .createQueryBuilder()
        .select(['id', 'user_id'])
        .from(Associates, 'a')
        .where('a.id = :id', { id })
        .getRawOne();

      if (!associate) {
        throw new NotFoundException('Associate not found');
      }

      // Update user info if provided
      if (
        payload.full_name ||
        payload.email ||
        payload.phone ||
        payload.password
      ) {
        // Build dynamic UPDATE query to only update provided fields
        const updateFields: string[] = [];
        const updateValues: any[] = [];

        if (payload.full_name) {
          updateFields.push('full_name = ?');
          updateValues.push(payload.full_name);
        }
        if (payload.email) {
          updateFields.push('email = ?');
          updateValues.push(payload.email);
        }
        if (payload.phone) {
          updateFields.push('phone = ?');
          updateValues.push(payload.phone);
        }
        // Only update password if it's provided and not empty
        if (payload.password && payload.password.trim() !== '') {
          updateFields.push('password = ?');
          updateValues.push(payload.password);
        }

        if (updateFields.length > 0) {
          updateValues.push(associate.user_id); // Add user_id at the end
          const updateUserQuery = `
            UPDATE users 
            SET ${updateFields.join(', ')}
            WHERE id = ?
          `;
          await queryRunner.manager.query(updateUserQuery, updateValues);
        }
      }

      // Update associate info
      const updateAssociateQuery = `
        UPDATE associates 
        SET 
          country_id = COALESCE(?, country_id),
          state_id = COALESCE(?, state_id),
          city_id = COALESCE(?, city_id),
          area_id = COALESCE(?, area_id),
          address = COALESCE(?, address),
          pincode = COALESCE(?, pincode),
          latitude = COALESCE(?, latitude),
          longitude = COALESCE(?, longitude),
          status = COALESCE(?, status),
          updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
      `;
      await queryRunner.manager.query(updateAssociateQuery, [
        payload.country_id || null,
        payload.state_id || null,
        payload.city_id || null,
        payload.area_id || null,
        payload.address || null,
        payload.pincode || null,
        payload.latitude ? Number(payload.latitude) : null,
        payload.longitude ? Number(payload.longitude) : null,
        payload.status !== undefined ? payload.status : null,
        id,
      ]);

      // If status is being updated, sync user status
      if (payload.status !== undefined) {
        const userStatus = payload.status === 1 ? 'active' : 'inactive';
        await queryRunner.manager.query(
          'UPDATE users SET status = ? WHERE id = ?',
          [userStatus, associate.user_id],
        );
      }

      await queryRunner.commitTransaction();

      return {
        success: true,
        message: 'Associate updated successfully',
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      console.error('Error in updateAssociate:', error);
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  // Toggle associate status (soft delete)
  async toggleAssociateStatus(id: number) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // Get current status and user_id
      const [associate] = await queryRunner.query(
        'SELECT id, status, user_id FROM associates WHERE id = ? LIMIT 1',
        [id],
      );

      if (!associate) {
        throw new NotFoundException('Associate not found');
      }

      const newStatus = associate.status === 1 ? 0 : 1;
      const userStatus = newStatus === 1 ? 'active' : 'inactive';

      // Update associate status
      await queryRunner.query(
        'UPDATE associates SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [newStatus, id],
      );

      // Update user status to match associate status
      await queryRunner.query('UPDATE users SET status = ? WHERE id = ?', [
        userStatus,
        associate.user_id,
      ]);

      // Note: We do NOT pause/unpause jobs as per requirement

      await queryRunner.commitTransaction();

      return {
        success: true,
        message: `Associate ${newStatus === 1 ? 'activated' : 'deactivated'} successfully`,
        data: {
          newStatus,
        },
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      console.error('Error in toggleAssociateStatus:', error);
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  // Share associate credentials via email
  async shareAssociateCredentials(id: number, email: string, message?: string) {
    try {
      // Get associate and user details
      const query = `
        SELECT 
          u.full_name,
          u.email,
          u.username,
          u.password
        FROM associates a
        INNER JOIN users u ON a.user_id = u.id
        WHERE a.id = ?
      `;

      const [associate] = await this.dataSource.query(query, [id]);

      if (!associate) {
        throw new NotFoundException('Associate not found');
      }

      const templateName = 'associate_credentials';
      const toName = associate.full_name || email.split('@')[0];
      const context = {
        full_name: associate.full_name,
        username: associate.username,
        email: associate.email,
        password: associate.password || 'Not available',
        message_section: message
          ? `<p style="margin: 0 0 24px 0; padding: 16px; background: #eff6ff; border-left: 4px solid #2563eb; border-radius: 6px; font-size: 15px; color: #1e40af;">
                <strong>Personal Message:</strong><br>
                ${message}
              </p>`
          : '',
      };

      const success = await this.emailService.sendTemplateEmail(
        templateName,
        email,
        toName,
        context
      );

      if (success) {
        return {
          success: true,
          message: `Credentials shared successfully to ${email}`,
        };
      } else {
        return {
          success: false,
          message: 'Failed to send credentials email',
        };
      }
    } catch (error) {
      console.error('Error sharing associate credentials:', error);
      throw error;
    }
  }

  // Delete associate (soft delete)
  async deleteAssociate(id: number) {
    try {
      const result = await this.dataSource.query(
        'UPDATE associates SET status = 0, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [id],
      );

      if (result.affectedRows === 0) {
        throw new NotFoundException('Associate not found');
      }

      return {
        success: true,
        message: 'Associate deleted successfully',
      };
    } catch (error) {
      console.error('Error in deleteAssociate:', error);
      throw error;
    }
  }

  // Get associate's job posts by associate_id
  async getAssociateJobPosts(
    associateId: number,
    page: number = 1,
    limit: number = 10,
  ) {
    try {
      const {
        page: safePage,
        limit: safeLimit,
        offset,
      } = this.sanitizePagination(page, limit);

      const query = `
        SELECT 
          jp.id,
          jp.title,
          jp.status,
          jp.featured,
          jp.created_at,
          jp.expires_at,
          jp.vacancies,
          jp.salary_min,
          jp.salary_max,
          c.name as company_name,
          c.logo as company_logo,
          ct.city_name as location,
          jt.name as job_type,
          COUNT(DISTINCT ja.id) as application_count
        FROM job_posts jp
        LEFT JOIN companies c ON jp.company_id = c.id
        LEFT JOIN city ct ON jp.city_id = ct.city_id
        LEFT JOIN job_type jt ON jp.job_type_id = jt.id
        LEFT JOIN job_applications ja ON jp.id = ja.job_post_id
        WHERE jp.associate_id = ?
        GROUP BY jp.id, jp.title, jp.status, jp.featured, jp.created_at, jp.expires_at, jp.vacancies, jp.salary_min, jp.salary_max, c.name, c.logo, ct.city_name, jt.name
        ORDER BY jp.created_at DESC
        LIMIT ? OFFSET ?
      `;

      const jobPosts = await this.dataSource.query(query, [
        associateId,
        safeLimit,
        offset,
      ]);

      // Get total count
      const [{ total }] = await this.dataSource.query(
        'SELECT COUNT(*) as total FROM job_posts WHERE associate_id = ?',
        [associateId],
      );

      return {
        success: true,
        data: {
          jobs: jobPosts,
          total,
          totalPages: Math.ceil(total / safeLimit),
          currentPage: safePage,
        },
      };
    } catch (error) {
      console.error('Error in getAssociateJobPosts:', error);
      throw error;
    }
  }

  // Promote associate job
  async promoteAssociateJob(userId: number, jobId: number) {
    try {
      // 1. Find associate by userId
      const associate = await this.associatesRepo.findOne({
        where: { user_id: userId },
      });

      if (!associate) {
        throw new NotFoundException('Associate not found for this user');
      }

      // 2. Check if job exists and belongs to this associate
      const [job] = await this.dataSource.query(
        'SELECT id, featured FROM job_posts WHERE id = ? AND associate_id = ? LIMIT 1',
        [jobId, associate.id],
      );

      if (!job) {
        throw new NotFoundException(
          'Job not found or you do not have permission to promote this job',
        );
      }

      // 3. Toggle featured status
      const newFeaturedStatus = job.featured === 1 ? 0 : 1;

      await this.dataSource.query(
        'UPDATE job_posts SET featured = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?',
        [newFeaturedStatus, jobId],
      );

      return {
        success: true,
        message: newFeaturedStatus
          ? 'Job promoted successfully'
          : 'Job unpromoted successfully',
        data: {
          jobId,
          featured: newFeaturedStatus,
        },
      };
    } catch (error) {
      console.error('Error in promoteAssociateJob:', error);
      throw error;
    }
  }

  // Get job posts by user_id (for logged-in associate)
  async getJobPostsByUserId(
    userId: number,
    page: number = 1,
    limit: number = 10,
    search?: string,
    status?: string,
    hasApplications?: string,
  ) {
    try {
      // First get associate_id from user_id
      const [associate] = await this.dataSource.query(
        'SELECT id FROM associates WHERE user_id = ? LIMIT 1',
        [userId],
      );

      if (!associate) {
        return {
          success: false,
          message: 'Associate not found for this user',
          data: {
            jobs: [],
            total: 0,
            totalPages: 0,
            currentPage: page,
            activeJobs: 0,
            totalApplications: 0,
          },
        };
      }

      const {
        page: safePage,
        limit: safeLimit,
        offset,
      } = this.sanitizePagination(page, limit);

      // Build WHERE clause with filters
      const whereConditions = ['jp.associate_id = ?'];
      const queryParams: any[] = [associate.id];

      // Add search filter
      if (search && search.trim()) {
        whereConditions.push('(jp.title LIKE ? OR c.name LIKE ?)');
        const searchPattern = `%${search.trim()}%`;
        queryParams.push(searchPattern, searchPattern);
      }

      // Add status filter
      if (status && status !== 'all') {
        if (status === 'active') {
          whereConditions.push(
            "jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > CURRENT_TIMESTAMP)",
          );
        } else if (status === 'expired') {
          whereConditions.push('jp.expires_at <= CURRENT_TIMESTAMP');
          // } else if (status === 'inactive') {
          //   whereConditions.push(
          //     "(jp.status = 'closed' OR jp.status = 'paused')",
          //   );
        } else if (status === 'pending') {
          whereConditions.push("jp.status = 'pending'");
        } else if (status === 'rejected') {
          whereConditions.push("jp.status = 'rejected'");
        }
      }

      const whereClause = 'WHERE ' + whereConditions.join(' AND ');

      // Add HAVING clause for jobs with applications filter
      const havingClause =
        hasApplications === 'true' ? 'HAVING application_count > 0' : '';

      // Determine ORDER BY clause - sort by latest application if filtering by hasApplications
      const orderByClause =
        hasApplications === 'true'
          ? 'ORDER BY latest_application DESC, jp.created_at DESC'
          : 'ORDER BY jp.created_at DESC';

      const jobLimitOffset = safeLimit === -1 ? '' : ' LIMIT ? OFFSET ?';
      const jobLimitOffsetParams = safeLimit === -1 ? [] : [safeLimit, offset];

      // Get filtered jobs with pagination
      const query = `
        SELECT 
          jp.id,
          jp.title,
          jp.status,
          jp.featured,
          jp.created_at,
          jp.expires_at,
          jp.vacancies,
          jp.salary_min,
          jp.salary_max,
          c.name as company_name,
          c.logo as company_logo,
          ct.city_name as location,
          jt.name as job_type,
          COUNT(DISTINCT ja.id) as application_count,
          MAX(ja.applied_at) as latest_application
        FROM job_posts jp
        LEFT JOIN companies c ON jp.company_id = c.id
        LEFT JOIN city ct ON jp.city_id = ct.city_id
        LEFT JOIN job_type jt ON jp.job_type_id = jt.id
        LEFT JOIN job_applications ja ON jp.id = ja.job_post_id
        ${whereClause}
        GROUP BY jp.id, jp.title, jp.status, jp.featured, jp.created_at, jp.expires_at, jp.vacancies, jp.salary_min, jp.salary_max, c.name, c.logo, ct.city_name, jt.name
        ${havingClause}
        ${orderByClause}
        ${jobLimitOffset}
      `;

      const jobPosts = await this.dataSource.query(query, [
        ...queryParams,
        ...jobLimitOffsetParams,
      ]);

      // Get total count with same filters including hasApplications
      const countQuery = `
        SELECT COUNT(*) as total
        FROM (
          SELECT jp.id, COUNT(DISTINCT ja.id) as application_count
          FROM job_posts jp
          LEFT JOIN companies c ON jp.company_id = c.id
          LEFT JOIN job_applications ja ON jp.id = ja.job_post_id
          ${whereClause}
          GROUP BY jp.id
          ${havingClause}
        ) as filtered_jobs
      `;
      const [{ total }] = await this.dataSource.query(countQuery, queryParams);

      const statsQuery = `
        SELECT 
          COUNT(DISTINCT jp.id) as total_jobs,
          COUNT(DISTINCT CASE WHEN jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > CURRENT_TIMESTAMP) THEN jp.id END) as active_jobs,
          COUNT(DISTINCT CASE WHEN jp.status = 'pending' THEN jp.id END) as pending_jobs,
          COUNT(DISTINCT CASE WHEN DATE(jp.created_at) = CURRENT_DATE THEN jp.id END) as jobs_today,
          COUNT(DISTINCT ja.id) as total_applications,
          COUNT(DISTINCT CASE WHEN ja.id IS NOT NULL THEN jp.id END) as jobs_with_applicants
        FROM job_posts jp
        LEFT JOIN companies c ON jp.company_id = c.id
        LEFT JOIN job_applications ja ON jp.id = ja.job_post_id
        WHERE ${whereConditions.join(' AND ')}
      `;

      const [stats] = await this.dataSource.query(statsQuery, queryParams);

      return {
        success: true,
        data: {
          jobs: jobPosts,
          total: parseInt(total || 0), // Filtered total for pagination
          totalPages:
            safeLimit === -1 ? 1 : Math.ceil(parseInt(total || 0) / safeLimit),
          currentPage: safePage,
          globalTotalJobs: parseInt(stats.total_jobs || 0),
          activeJobs: parseInt(stats.active_jobs || 0),
          pendingJobs: parseInt(stats.pending_jobs || 0),
          jobsToday: parseInt(stats.jobs_today || 0),
          totalApplications: parseInt(stats.total_applications || 0),
          jobsWithApplicants: parseInt(stats.jobs_with_applicants || 0),
        },
      };
    } catch (error) {
      console.error('Error in getJobPostsByUserId:', error);
      throw error;
    }
  }

  // Get applications by user_id (for logged-in associate)
  async getApplicationsByUserId(
    userId: number,
    page: number = 1,
    limit: number = 10,
  ) {
    try {
      // First get associate_id from user_id
      const [associate] = await this.dataSource.query(
        'SELECT id FROM associates WHERE user_id = ? LIMIT 1',
        [userId],
      );

      if (!associate) {
        return {
          success: false,
          message: 'Associate not found for this user',
          data: {
            applications: [],
            total: 0,
            totalPages: 0,
            currentPage: page,
          },
        };
      }

      // Use the existing method with associate_id
      return await this.getAssociateApplications(associate.id, page, limit);
    } catch (error) {
      console.error('Error in getApplicationsByUserId:', error);
      throw error;
    }
  }

  // Get applications for associate's job posts
  async getAssociateApplications(
    associateId: number,
    page: number = 1,
    limit: number = 10,
  ) {
    try {
      const {
        page: safePage,
        limit: safeLimit,
        offset,
      } = this.sanitizePagination(page, limit);

      const query = `
        SELECT 
          ja.id,
          ja.job_post_id,
          ja.seeker_id,
          ja.status,
          ja.applied_at,
          ja.cover_letter,
          ja.resume_id,
          COALESCE(sr.file_url, latest_resume.file_url) as resume_url,
          COALESCE(sr.name, latest_resume.name) as resume_name,
          jp.title as job_title,
          jp.id as job_id,
          COALESCE(u.full_name, u2.full_name) as applicant_name,
          COALESCE(u.email, u2.email) as email,
          COALESCE(u.phone, u2.phone) as phone
        FROM job_applications ja
        INNER JOIN job_posts jp ON ja.job_post_id = jp.id
        LEFT JOIN seeker_profiles sp ON ja.seeker_id = sp.id
        INNER JOIN users u ON sp.user_id = u.id
        LEFT JOIN users u2 ON ja.seeker_id = u2.id
        LEFT JOIN seeker_resumes sr ON ja.resume_id = sr.id
        LEFT JOIN (
          SELECT seeker_id, file_url, name, uploaded_at
          FROM seeker_resumes
          WHERE (seeker_id, uploaded_at) IN (
            SELECT seeker_id, MAX(uploaded_at)
            FROM seeker_resumes
            GROUP BY seeker_id
          )
        ) latest_resume ON sp.id = latest_resume.seeker_id
        WHERE jp.associate_id = ?
        ORDER BY ja.applied_at DESC
        LIMIT ? OFFSET ?
      `;

      const applications = await this.dataSource.query(query, [
        associateId,
        safeLimit,
        offset,
      ]);

      // Get total count
      const [{ total }] = await this.dataSource.query(
        `SELECT COUNT(*) as total 
         FROM job_applications ja
         INNER JOIN job_posts jp ON ja.job_post_id = jp.id
         WHERE jp.associate_id = ?`,
        [associateId],
      );

      return {
        success: true,
        data: {
          applications,
          total,
          totalPages: Math.ceil((total || 0) / safeLimit),
          currentPage: safePage,
        },
      };
    } catch (error) {
      console.error('Error in getAssociateApplications:', error);
      throw error;
    }
  }

  // Helper: Get associate_id from user_id
  private async getAssociateIdByUserId(userId: number): Promise<number> {
    const [associate] = await this.dataSource.query(
      'SELECT id FROM associates WHERE user_id = ? LIMIT 1',
      [userId],
    );

    if (!associate) {
      throw new NotFoundException('Associate not found for this user');
    }

    return associate.id;
  }

  // Create job post for associate
  async createAssociateJobPost(payload: any) {
    try {
      // console.log('DEBUG: createAssociateJobPost received payload:', JSON.stringify(payload, null, 2));

      // Validate inputs to prevent NaN errors in SQL
      const userId = Number(payload.userId);
      const companyId = Number(payload.company_id);

      if (isNaN(userId) || userId <= 0) {
        throw new Error(
          'Invalid User ID: Job post requires a valid associate user.',
        );
      }

      if (isNaN(companyId) || companyId <= 0) {
        throw new Error(
          'Invalid Company ID: You must select a valid dummy company.',
        );
      }

      // Get associate_id from user_id
      const [associate] = await this.dataSource.query(
        'SELECT id FROM associates WHERE user_id = ? LIMIT 1',
        [userId],
      );

      if (!associate) {
        const [userExists] = await this.dataSource.query(
          'SELECT id, role FROM users WHERE id = ?',
          [userId],
        );
        console.warn(
          `Associate record missing for user_id ${userId}. User exists in 'users' table:`,
          !!userExists,
          userExists?.role,
        );
        throw new Error(
          `Associate not found for this user (User ID: ${userId}, Role: ${userExists?.role || 'unknown'})`,
        );
      }

      // Validate company is a dummy company
      const [company] = await this.dataSource.query(
        'SELECT id, is_admin_company FROM companies WHERE id = ? AND is_admin_company = 1 LIMIT 1',
        [companyId],
      );

      if (!company) {
        throw new Error(
          'Invalid company. Associates can only use dummy companies.',
        );
      }

      // 0. Calculate Job Expiry (Automation)
      let jobExpiryDate = payload.expires_at
        ? new Date(payload.expires_at)
        : null;
      try {
        let durationDays = 15; // Default for associates/admin companies

        // 1. Try to get duration from the selected job package (highest priority)
        let packageFound = false;
        if (payload.job_package && !isNaN(Number(payload.job_package))) {
          const [jobPackage] = await this.dataSource.query(
            'SELECT duration_days FROM job_packages WHERE id = ? OR code = ? LIMIT 1',
            [payload.job_package, payload.job_package],
          );
          if (jobPackage && jobPackage.duration_days) {
            durationDays = Number(jobPackage.duration_days);
            packageFound = true;
          }
        }

        // 2. Fallback to active subscription plan if no package selected
        // if (!packageFound) {
        //   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`,
        //     [payload.userId]
        //   );
        //   const sub = subs[0];
        //   if (sub && sub.job_active_days) {
        //     durationDays = Number(sub.job_active_days);
        //   }
        // }

        const defaultExpiry = new Date();
        defaultExpiry.setDate(defaultExpiry.getDate() + durationDays);

        // If the associate provided a custom override, use it; otherwise use the calculated default.
        jobExpiryDate = payload.expires_at
          ? new Date(payload.expires_at)
          : defaultExpiry;
      } catch (err) {
        console.warn(
          'Failed to calculate automated job expiry for associate:',
          err,
        );
      }

      // Create job post with associate_id
      const jobData = {
        ...payload,
        associate_id: associate.id,
        status: 'pending', // Jobs start as pending for review
      };

      // Use existing job creation logic
      const result = await this.dataSource
        .createQueryBuilder()
        .insert()
        .into('job_posts')
        .values({
          company_id: jobData.company_id,
          associate_id: jobData.associate_id,
          title: jobData.title || null,
          address: jobData.address,
          description: jobData.description,
          requirements: jobData.requirements || null,
          benefits: jobData.benefits || null,
          featured: jobData.featured || false,
          salary_min: jobData.salary_min || 0,
          salary_max: jobData.salary_max || 0,
          desirable: jobData.desirable || null,
          job_level_id: jobData.job_level_id || null,
          status: jobData.status || 'pending',
          image_url: jobData.image_url || null,
          pincode: jobData.pincode ? Number(jobData.pincode) : null,
          area_id: jobData.area_id || null,
          vacancies: jobData.vacancies || 0,
          expires_at: jobExpiryDate,
          job_type_id: jobData.job_type_id || null,
          experience_id: jobData.experience_id || null,
          education_id: jobData.education_id || null,
          role_levels_id: jobData.role_levels_id || null,
          country_id: jobData.country_id || null,
          state_id: jobData.state_id || null,
          city_id: jobData.city_id || null,
          role_id: jobData.role_id || null,
          industry_id: jobData.industry_id || null,
          urgent_hiring: jobData.urgent_hiring || false,
          shift: jobData.shift || 'day',
          english_level: jobData.english_level || null,
          gender: jobData.gender || null,
          walk_in_interview: jobData.walk_in_interview || 'no',
          walkin_address: jobData.walkin_address || null,
          walkin_start_date: jobData.walkin_start_date || null,
          walkin_end_date: jobData.walkin_end_date || null,
          walkin_start_time: jobData.walkin_start_time || null,
          walkin_end_time: jobData.walkin_end_time || null,
          walkin_instructions: jobData.walkin_instructions || null,
          communication_preference: jobData.communication_preference || null,
          other_recruiter_name: jobData.other_recruiter_name || null,
          other_recruiter_whatsapp: jobData.other_recruiter_whatsapp || null,
          other_recruiter_email: jobData.other_recruiter_email || null,
          notification_preference: jobData.notification_preference || null,
          // AI Interview Configuration
          ai_interview_enabled: jobData.ai_interview_enabled || false,
          ai_interview_duration: jobData.ai_interview_duration || null,
          ai_window_days: jobData.ai_window_days || null,
          ai_start_time: jobData.ai_start_time || null,
          ai_end_time: jobData.ai_end_time || null,
          ai_question_set_id: jobData.ai_question_set_id || null,
        })
        .execute();

      const jobId = result.identifiers[0].id;

      // --- Handle skills (accept both `skills` and `required_skills`) ---
      let incomingSkills: any[] = [];
      if (Array.isArray(payload.skills)) {
        incomingSkills = payload.skills;
      } else if (Array.isArray(payload.required_skills)) {
        incomingSkills = payload.required_skills;
      }

      if (incomingSkills.length > 0) {
        const skillIds: number[] = [];
        for (const skillItem of incomingSkills) {
          if (typeof skillItem === 'string') {
            try {
              const skill = await this.skillsService.createIfNotExists(
                skillItem,
                'tech',
              );
              skillIds.push(skill.id);
            } catch (error) {
              // eslint-disable-next-line no-console
              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: jobId, skill_id: skillId })
              .execute();
          } catch (error) {
            // eslint-disable-next-line no-console
            console.error('Error linking skill to job:', error);
          }
        }
      }

      // --- Handle tags (accept both `tags` and `job_tags`) ---
      let incomingTags: any[] = [];
      if (Array.isArray(payload.tags)) {
        incomingTags = payload.tags;
      } else if (Array.isArray(payload.job_tags)) {
        incomingTags = payload.job_tags;
      }

      if (incomingTags.length > 0) {
        const tagIds: number[] = [];
        for (const tagName of incomingTags) {
          try {
            const tag = await this.tagsService.createIfNotExists(tagName);
            tagIds.push(tag.id);
          } catch (error) {
            // eslint-disable-next-line no-console
            console.error('Error creating tag:', error);
          }
        }

        try {
          await this.jobsPostTagService.createJobTags(jobId, tagIds);
        } catch (error) {
          // eslint-disable-next-line no-console
          console.error('Error linking tags to job:', error);
        }
      }

      const finalResponse = {
        success: true,
        message: 'Job post created successfully',
        data: { id: jobId, job_id: jobId },
      };

      // --- Log Wallet Transaction for Tracking ---
      try {
        const credits = payload.credits_required || 0;
        await this.walletService.createWalletIfNotExists(payload.userId);

        // Fetch the wallet
        const [wallet] = await this.dataSource.query(
          'SELECT id, job_credits FROM wallets WHERE user_id = ? LIMIT 1',
          [payload.userId],
        );

        if (wallet) {
          // Log a debit transaction for tracking purposes (no actual deduction for associates)
          await this.dataSource.query(
            `INSERT INTO wallet_transactions 
             (wallet_id, type, credit_type, amount, reason, reference_type, reference_id, balance_after) 
             VALUES (?, 'debit', 'job', ?, 'job_post (agent)', 'job_post', ?, ?)`,
            [
              wallet.id,
              credits,
              jobId,
              wallet.job_credits, // Balance remains same
            ],
          );
        }
      } catch (err) {
        console.error(
          'Failed to log wallet transaction for associate job post:',
          err,
        );
        // Don't throw error here to avoid failing job creation if logging fails
      }

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

  // Get applications with full candidate details by user_id
  async getApplicationsWithCandidateDetails(
    userId: number,
    page: number = 1,
    limit: number = 10,
    statusFilter?: string,
    jobIdFilter?: number,
  ) {
    try {
      const associateId = await this.getAssociateIdByUserId(userId);
      const {
        page: safePage,
        limit: safeLimit,
        offset,
      } = this.sanitizePagination(page, limit);

      let whereClause = 'WHERE jp.associate_id = ?';
      const params: any[] = [associateId];

      if (statusFilter && statusFilter !== 'all') {
        whereClause += ' AND ja.status = ?';
        params.push(statusFilter);
      }

      if (jobIdFilter) {
        whereClause += ' AND jp.id = ?';
        params.push(jobIdFilter);
      }

      const query = `
        SELECT 
          ja.id as application_id,
          ja.job_post_id,
          ja.seeker_id,
          ja.status,
          ja.applied_at,
          ja.cover_letter,
          ja.resume_id,
          jp.id as job_id,
          jp.title as job_title,
          jp.status as job_status,
          u.id as user_id,
          u.full_name as candidate_name,
          u.email as candidate_email,
          u.phone as candidate_phone,
          u.profile_picture,
          sp.professional_title,
          sp.bio_graphy,
          exp.level as experience_name,
          edu.name as education_name,
          COALESCE(sr.file_url, latest_resume.file_url) as cv_url,
          COALESCE(sr.file_name, latest_resume.file_name) as cv_filename,
          COALESCE(sr.name, latest_resume.name) as resume_name,
          c.name as company_name,
          city.city_name as location
        FROM job_applications ja
        INNER JOIN job_posts jp ON ja.job_post_id = jp.id
        INNER JOIN seeker_profiles sp ON ja.seeker_id = sp.id
        INNER JOIN users u ON sp.user_id = u.id
        LEFT JOIN experiences exp ON sp.experiences_id = exp.id
        LEFT JOIN education_levels edu ON sp.education_id = edu.id
        LEFT JOIN seeker_resumes sr ON ja.resume_id = sr.id AND sr.is_deleted = 0
        LEFT JOIN (
          SELECT seeker_id, file_url, file_name, name, uploaded_at
          FROM seeker_resumes
          WHERE is_deleted = 0 AND (seeker_id, uploaded_at) IN (
            SELECT seeker_id, MAX(uploaded_at)
            FROM seeker_resumes
            WHERE is_deleted = 0
            GROUP BY seeker_id
          )
        ) latest_resume ON sp.id = latest_resume.seeker_id
        LEFT JOIN companies c ON jp.company_id = c.id
        LEFT JOIN city ON jp.city_id = city.city_id
        ${whereClause}
        ORDER BY ja.applied_at DESC
        LIMIT ? OFFSET ?
      `;

      params.push(safeLimit, offset);
      const applications = await this.dataSource.query(query, params);

      // Get total count
      const countParams = params.slice(0, -2);
      const [{ total }] = await this.dataSource.query(
        `SELECT COUNT(*) as total 
         FROM job_applications ja
         INNER JOIN job_posts jp ON ja.job_post_id = jp.id
         ${whereClause}`,
        countParams,
      );

      // Get status summary
      const statusQuery = `
        SELECT 
          ja.status,
          COUNT(*) as count
        FROM job_applications ja
        INNER JOIN job_posts jp ON ja.job_post_id = jp.id
        WHERE jp.associate_id = ?
        GROUP BY ja.status
      `;
      const statusSummary = await this.dataSource.query(statusQuery, [
        associateId,
      ]);

      return {
        success: true,
        data: {
          applications,
          total: parseInt(total || 0),
          totalPages: Math.ceil(parseInt(total || 0) / safeLimit),
          currentPage: safePage,
          statusSummary,
        },
      };
    } catch (error) {
      console.error('Error in getApplicationsWithCandidateDetails:', error);
      throw error;
    }
  }

  // Update application status by user_id
  async updateApplicationStatusByUserId(
    userId: number,
    applicationId: number,
    status: string,
  ) {
    try {
      const associateId = await this.getAssociateIdByUserId(userId);

      // Verify this application belongs to associate's job
      const [application] = await this.dataSource.query(
        `SELECT ja.id 
         FROM job_applications ja
         INNER JOIN job_posts jp ON ja.job_post_id = jp.id
         WHERE ja.id = ? AND jp.associate_id = ?
         LIMIT 1`,
        [applicationId, associateId],
      );

      if (!application) {
        throw new NotFoundException('Application not found or unauthorized');
      }

      const validStatuses = [
        'Applied',
        'Shortlisted',
        'Interview',
        'Hired',
        'Rejected',
      ];
      if (!validStatuses.includes(status)) {
        throw new Error('Invalid status');
      }

      await this.dataSource.query(
        'UPDATE job_applications SET status = ? WHERE id = ?',
        [status, applicationId],
      );

      if (['Shortlisted', 'Interview', 'Hired', 'Rejected'].includes(status)) {
        const [details] = await this.dataSource.query(
          `SELECT 
             ja.id,
             ja.status,
             jp.title AS job_title,
             c.name AS company_name,
             sp.id AS seeker_id,
             u.email AS candidate_email,
             u.full_name AS candidate_name,
             u.username AS candidate_username
           FROM job_applications ja
           INNER JOIN job_posts jp ON ja.job_post_id = jp.id
           INNER JOIN companies c ON jp.company_id = c.id
           INNER JOIN seeker_profiles sp ON ja.seeker_id = sp.id
           INNER JOIN users u ON sp.user_id = u.id
           WHERE ja.id = ?
           LIMIT 1`,
          [applicationId],
        );
        if (details && details.candidate_email) {
          const preferredName =
            details.candidate_name ||
            details.candidate_username ||
            (details.candidate_email || '').split('@')[0];

          const templateName = 'application_status_update';
          const context = {
            preferred_name: preferredName,
            job_title: details.job_title || 'a position',
            company_name: details.company_name || 'the company',
            status: status
          };

          await this.emailService.sendTemplateEmail(
            templateName,
            details.candidate_email,
            preferredName,
            context
          );
        }
      }

      return {
        success: true,
        message: 'Application status updated successfully',
      };
    } catch (error) {
      console.error('Error in updateApplicationStatusByUserId:', error);
      throw error;
    }
  }

  // Get application statistics by user_id
  async getApplicationStatsByUserId(userId: number) {
    try {
      const associateId = await this.getAssociateIdByUserId(userId);

      const query = `
        SELECT 
          COUNT(DISTINCT ja.id) as total_applications,
          COUNT(DISTINCT jp.id) as total_job_posts
        FROM job_applications ja
        INNER JOIN job_posts jp ON ja.job_post_id = jp.id
        WHERE jp.associate_id = ?
      `;

      const [stats] = await this.dataSource.query(query, [associateId]);

      const activeQuery = `
        SELECT 
          COUNT(DISTINCT CASE WHEN LOWER(ja.status) IN ('applied', 'pending') THEN ja.id END) as applied_count,
          COUNT(DISTINCT CASE WHEN LOWER(ja.status) = 'shortlisted' THEN ja.id END) as shortlisted_count,
          COUNT(DISTINCT CASE WHEN LOWER(ja.status) = 'interview' THEN ja.id END) as interview_count,
          COUNT(DISTINCT CASE WHEN LOWER(ja.status) = 'hired' THEN ja.id END) as hired_count,
          COUNT(DISTINCT CASE WHEN LOWER(ja.status) = 'rejected' THEN ja.id END) as rejected_count
        FROM job_applications ja
        INNER JOIN job_posts jp ON ja.job_post_id = jp.id
        WHERE jp.associate_id = ?
      `;
      const [activeStats] = await this.dataSource.query(activeQuery, [
        associateId,
      ]);

      return {
        success: true,
        data: {
          ...stats,
          total_applications: parseInt(stats.total_applications || 0),
          applied_count: parseInt(activeStats.applied_count || 0),
          shortlisted_count: parseInt(activeStats.shortlisted_count || 0),
          interview_count: parseInt(activeStats.interview_count || 0),
          hired_count: parseInt(activeStats.hired_count || 0),
          rejected_count: parseInt(activeStats.rejected_count || 0),
          total_job_posts: parseInt(stats.total_job_posts || 0),
        },
      };
    } catch (error) {
      console.error('Error in getApplicationStatsByUserId:', error);
      throw error;
    }
  }

  // Get saved candidates by user_id
  async getSavedCandidatesByUserId(
    userId: number,
    page: number = 1,
    limit: number = 10,
  ) {
    let associateId: number;
    try {
      // Ensure page and limit are valid numbers to prevent NaN in offset calculation
      const safePage =
        isNaN(Number(page)) || Number(page) < 1 ? 1 : Number(page);
      const safeLimit =
        isNaN(Number(limit)) || Number(limit) < 1 ? 10 : Number(limit);

      associateId = await this.getAssociateIdByUserId(userId);

      if (isNaN(associateId)) {
        throw new Error('Invalid Associate ID');
      }

      const offset = (safePage - 1) * safeLimit;

      const query = `
        SELECT 
          sc.id as saved_candidate_id,
          sc.candidate_id,
          sc.notes,
          sc.status as saved_status,
          sc.created_at as saved_at,
          sp.id as seeker_id,
          sp.professional_title,
          sp.bio_graphy,
          u.id as user_id,
          u.full_name as candidate_name,
          u.email as candidate_email,
          u.phone as candidate_phone,
          u.profile_picture,
          exp.level as experience_name,
          edu.name as education_name,
          sr.file_url as cv_url,
          sr.file_name as cv_filename,
          COUNT(DISTINCT CASE 
            WHEN jp.associate_id = ? THEN ja.id 
          END) as applications_count
        FROM saved_candidates sc
        INNER JOIN seeker_profiles sp ON sc.candidate_id = sp.id
        INNER JOIN users u ON sp.user_id = u.id
        LEFT JOIN experiences exp ON sp.experiences_id = exp.id
        LEFT JOIN education_levels edu ON sp.education_id = edu.id
        LEFT JOIN (
          SELECT seeker_id, file_url, file_name, uploaded_at
          FROM seeker_resumes
          WHERE is_deleted = 0 AND (seeker_id, uploaded_at) IN (
            SELECT seeker_id, MAX(uploaded_at)
            FROM seeker_resumes
            WHERE is_deleted = 0
            GROUP BY seeker_id
          )
        ) sr ON sp.id = sr.seeker_id
        LEFT JOIN job_applications ja ON sp.id = ja.seeker_id
        LEFT JOIN job_posts jp ON ja.job_post_id = jp.id
        WHERE sc.associate_id = ? AND sc.is_deleted = 0
        GROUP BY sc.id, sp.id, u.id, exp.level, edu.name, sr.file_url, sr.file_name
        ORDER BY sc.created_at DESC
        LIMIT ? OFFSET ?
      `;

      const candidates = await this.dataSource.query(query, [
        associateId, // First ? - for COUNT CASE
        associateId, // Second ? - for WHERE clause
        safeLimit,
        offset,
      ]);

      const [{ total }] = await this.dataSource.query(
        'SELECT COUNT(*) as total FROM saved_candidates WHERE associate_id = ? AND is_deleted = 0',
        [associateId],
      );

      return {
        success: true,
        data: {
          candidates: candidates,
          pagination: {
            page: safePage,
            limit: safeLimit,
            total: parseInt(total || 0),
            totalPages: Math.ceil(parseInt(total || 0) / safeLimit),
          },
        },
        // Also include flat structure for backwards compatibility
        candidates: candidates,
        total: parseInt(total || 0),
        totalPages: Math.ceil(parseInt(total || 0) / safeLimit),
        currentPage: safePage,
      };
    } catch (error) {
      console.error('Error in getSavedCandidatesByUserId:', error);
      console.error('UserId:', userId, 'AssociateId:', associateId);
      throw error;
    }
  }

  // Save candidate by user_id
  async saveCandidateByUserId(
    userId: number,
    candidateId: number,
    notes?: string,
  ) {
    try {
      const associateId = await this.getAssociateIdByUserId(userId);

      // Convert user_id to seeker_profile.id (candidateId is actually user_id from frontend)
      const [seekerProfile] = await this.dataSource.query(
        'SELECT id FROM seeker_profiles WHERE user_id = ? LIMIT 1',
        [candidateId],
      );

      if (!seekerProfile) {
        throw new Error('Seeker profile not found for this candidate');
      }

      const seekerProfileId = seekerProfile.id;

      // Check if already saved
      const [existing] = await this.dataSource.query(
        'SELECT id FROM saved_candidates WHERE associate_id = ? AND candidate_id = ? AND is_deleted = 0 LIMIT 1',
        [associateId, seekerProfileId],
      );

      if (existing) {
        throw new Error('Candidate already saved');
      }

      await this.dataSource.query(
        'INSERT INTO saved_candidates (associate_id, candidate_id, notes, status) VALUES (?, ?, ?, ?)',
        [associateId, seekerProfileId, notes || null, 'saved'],
      );

      return {
        success: true,
        message: 'Candidate saved successfully',
      };
    } catch (error) {
      console.error('Error in saveCandidateByUserId:', error);
      throw error;
    }
  }

  // Remove saved candidate by user_id
  async removeSavedCandidateByUserId(userId: number, candidateId: number) {
    try {
      const associateId = await this.getAssociateIdByUserId(userId);

      // Convert user_id to seeker_profile.id
      const [seekerProfile] = await this.dataSource.query(
        'SELECT id FROM seeker_profiles WHERE user_id = ? LIMIT 1',
        [candidateId],
      );

      if (!seekerProfile) {
        throw new Error('Seeker profile not found for this candidate');
      }

      const seekerProfileId = seekerProfile.id;

      const result = await this.dataSource.query(
        'UPDATE saved_candidates SET is_deleted = 1 WHERE associate_id = ? AND candidate_id = ?',
        [associateId, seekerProfileId],
      );

      if (result.affectedRows === 0) {
        throw new NotFoundException('Saved candidate not found');
      }

      return {
        success: true,
        message: 'Candidate removed from saved list',
      };
    } catch (error) {
      console.error('Error in removeSavedCandidateByUserId:', error);
      throw error;
    }
  }

  // Check if candidate is saved by user_id
  async checkSavedCandidateByUserId(userId: number, candidateId: number) {
    try {
      const associateId = await this.getAssociateIdByUserId(userId);

      // Convert user_id to seeker_profile.id
      const [seekerProfile] = await this.dataSource.query(
        'SELECT id FROM seeker_profiles WHERE user_id = ? LIMIT 1',
        [candidateId],
      );

      if (!seekerProfile) {
        return {
          success: true,
          isSaved: false,
        };
      }

      const seekerProfileId = seekerProfile.id;

      const [saved] = await this.dataSource.query(
        'SELECT id FROM saved_candidates WHERE associate_id = ? AND candidate_id = ? AND is_deleted = 0 LIMIT 1',
        [associateId, seekerProfileId],
      );

      return {
        success: true,
        isSaved: !!saved,
      };
    } catch (error) {
      console.error('Error in checkSavedCandidateByUserId:', error);
      throw error;
    }
  }

  private sanitizePagination(page: any, limit: any) {
    const safePage = isNaN(Number(page)) || Number(page) < 1 ? 1 : Number(page);
    const safeLimit =
      isNaN(Number(limit)) || Number(limit) < 1 ? 10 : Number(limit);
    return {
      page: safePage,
      limit: safeLimit,
      offset: (safePage - 1) * safeLimit,
    };
  }

  // NOTE: CV usage methods moved to AssociateCvUsageService
  // See: backend/src/modules/associate_cv_usage/associate_cv_usage.service.ts
  // These old methods are deprecated and should not be used
}
