import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, DataSource, Brackets } from 'typeorm';
import { Companies } from './entities/companies.entity';
import { JobPosts } from '../job_posts/entities/job_posts.entity';
import { UserEntity } from '../user/entities/user.entity';
import { leadTokens, toMatchKey } from '../../utility/universalSlug';
import { CompanySocialLink } from '../company_social_links/entities/company_social_links.entity';
import { EmailService } from '../email/email.service';

@Injectable()
export class CompaniesService {
  private normalizeCompanySearch(search?: string) {
    return search?.toLowerCase().trim().replace(/\s+/g, ' ') || '';
  }

  private normalizeCompactCompanySearch(search?: string) {
    return this.normalizeCompanySearch(search).replace(/\s+/g, '');
  }

  constructor(
    @InjectRepository(Companies)
    private readonly companyRepo: Repository<Companies>,
    @InjectRepository(JobPosts)
    private readonly jobPostsRepo: Repository<JobPosts>,
    private readonly dataSource: DataSource,
    @InjectRepository(UserEntity)
    private readonly userRepo: Repository<UserEntity>,
    private readonly emailService: EmailService,
  ) { }

  // 🔹 NEW: Create recruiter + company together
  async addAdminCompany(payload: any, requesterId: number) {
    // console.log('Adding admin company with payload:', payload);
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      let requester = null;

      if (requesterId) {
        requester = await queryRunner.manager.findOne(UserEntity, {
          where: { id: requesterId },
        });
      }

      const isAdmin = requester?.role === 'admin';

      // 2️ Business rule: Use payload values if provided (for admin created companies),
      // otherwise determine based on requester role
      const companyStatus =
        payload.status !== undefined ? payload.status : isAdmin ? 1 : 0;
      const companyApproved =
        payload.is_approved !== undefined
          ? payload.is_approved
          : isAdmin
            ? 1
            : 0;
      // "addAdminCompany" creates a user account + company, so it should be considered "Real" (0), not "JV" (1),
      // even if created by an admin.
      const isadmincompany =
        payload.is_admin_company !== undefined ? payload.is_admin_company : 0;

      // Check if company name + city combination already exists
      const existingCompany = await queryRunner.manager
        .createQueryBuilder()
        .select(['id', 'name'])
        .from(Companies, 'c')
        .where('c.name = :name', { name: payload.name })
        .andWhere('c.city_id = :cityId', { cityId: payload.city_id })
        .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 })
        .getOne();

      if (existingCompany) {
        throw new Error(
          'Company with this name already exists in this city. Please choose a different name or city.',
        );
      }

      // 1️⃣ Create recruiter user ONLY if this is NOT a JV dummy company
      let userId = null;

      if (isadmincompany !== 1) {
        // This is a real company with a recruiter 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,
              role: 'recruiter',
              status: 'active',
            },
          ])
          .execute();

        userId = insertUser.identifiers[0].id;
      }
      // For JV dummy companies (is_admin_company = 1), userId remains null

      // 2️⃣ Create company linked with user_id
      const insertCompany = await queryRunner.manager
        .createQueryBuilder()
        .insert()
        .into(Companies)
        .values([
          {
            name: payload.name,
            website: payload.website || null,
            about: payload.about || null,
            vision: payload.vision || null,
            founded_at: payload.founded_at
              ? new Date(payload.founded_at)
              : null,
            location: payload.location || null,
            country_id: payload.country_id || null,
            state_id: payload.state_id || null,
            city_id: payload.city_id || null,
            pincode: payload.pincode || null,
            latitude: payload.latitude ? Number(payload.latitude) : null,
            longitude: payload.longitude ? Number(payload.longitude) : null,
            industry_id: payload.industry_id || null,
            company_size_id: payload.company_size_id || null,
            organization_type_id: payload.organization_type_id || null,
            user_id: userId,
            status: companyStatus, // admin → 1; normal → 0
            is_approved: companyApproved,
            is_admin_company: isadmincompany,
            logo: payload.logo || null,
            banner_url: payload.banner_url || null,
            gst_number: payload.gst_number || null,
            cin_number: payload.cin_number || null,
            area_id: payload.area_id || null,
            email: payload.email || null, // Add email for dummy company
            phone: payload.phone || null, // Add phone for dummy company
          },
        ])
        .execute();

      const companyId = insertCompany.identifiers[0].id;

      // 3️⃣ Add Social Links if provided
      if (
        payload.social_links &&
        Array.isArray(payload.social_links) &&
        payload.social_links.length > 0
      ) {
        const socialLinksValues = payload.social_links.map((link) => ({
          companyId: companyId,
          platformId: link.platform_id,
          url: link.url,
        }));

        await queryRunner.manager
          .createQueryBuilder()
          .insert()
          .into(CompanySocialLink)
          .values(socialLinksValues)
          .execute();
      }

      // 4️⃣ Add Key People if provided
      console.log('🔍 Key People Payload:', payload.key_people);
      if (
        payload.key_people &&
        Array.isArray(payload.key_people) &&
        payload.key_people.length > 0
      ) {
        const keyPeopleValues = payload.key_people.map((person) => ({
          company_id: companyId,
          name: person.name,
          designation: person.designation,
          email: person.email || null,
          phone: person.phone || null,
          visibility_level: person.visibility_level || 'full_contact',
        }));

        console.log('🔍 Key People Values to Insert:', keyPeopleValues);

        await queryRunner.manager
          .createQueryBuilder()
          .insert()
          .into('company_key_people')
          .values(keyPeopleValues)
          .execute();

        console.log(' Key People Inserted Successfully');
      } else {
        console.log(' No key people to insert or invalid data');
      }

      await queryRunner.commitTransaction();

      // Fetch the created social links and key people to return in response
      const socialLinksData = await queryRunner.manager.query(
        'SELECT platform_id, url FROM company_social_links WHERE company_id = ?',
        [companyId],
      );

      const keyPeopleData = await queryRunner.manager.query(
        'SELECT id, name, designation, email, phone, visibility_level FROM company_key_people WHERE company_id = ?',
        [companyId],
      );

      return {
        success: true,
        message:
          isadmincompany === 1
            ? 'JV Dummy Company created successfully'
            : 'Company and recruiter created successfully',
        data: {
          user: userId
            ? {
              username: payload.username,
              email: payload.email,
              password: payload.password, //  Only return for popup credentials
            }
            : null,
          companyName: payload.name,
          companyId: companyId,
          social_links: socialLinksData,
          key_people: keyPeopleData,
        },
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw new Error('Failed to create company: ' + error.message);
    } finally {
      await queryRunner.release();
    }
  }

  async toggleCompanyStatus(companyId: number) {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      const company = await queryRunner.manager
        .createQueryBuilder(Companies, 'c')
        .where('c.id = :id', { id: companyId })
        .getOne();

      if (!company) {
        throw new Error('Company not found');
      }

      const newStatus = company.status === 1 ? 0 : 1;

      await queryRunner.manager
        .createQueryBuilder()
        .update(Companies)
        .set({ status: newStatus })
        .where('id = :id', { id: companyId })
        .execute();

      if (company.user_id) {
        await queryRunner.manager
          .createQueryBuilder()
          .update(UserEntity)
          .set({ status: newStatus === 1 ? 'active' : 'inactive' })
          .where('id = :userId', { userId: company.user_id })
          .execute();
      }

      // 🔥 Get updated stats after toggle
      const stats = await queryRunner.manager
        .createQueryBuilder(Companies, 'c')
        .select([
          'COUNT(c.id) as totalCompanies',
          'SUM(CASE WHEN c.status = 1 AND c.is_deleted = 0 THEN 1 ELSE 0 END) as activeCompanies',
          'SUM(CASE WHEN c.status = 0 AND c.is_deleted = 0 THEN 1 ELSE 0 END) as inactiveCompanies',
          // Total job posts from all companies
          '(SELECT COUNT(*) FROM job_posts jp INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0) as totalJobPosts',
          // Active job posts from active companies only
          "(SELECT COUNT(*) FROM job_posts jp INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0 AND c2.status = 1 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as activeJobPosts",
          // Inactive job posts from inactive companies
          '(SELECT COUNT(*) FROM job_posts jp INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0 AND c2.status = 0) as inactiveJobPosts',
          // Total applications from all companies
          `(SELECT COUNT(*)
          FROM job_applications ja
          INNER JOIN job_posts jp ON jp.id = ja.job_post_id
          INNER JOIN companies c2 ON c2.id = jp.company_id
          WHERE c2.is_deleted = 0 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as totalApplications`,
          // Active applications from active companies only
          `(SELECT COUNT(*)
          FROM job_applications ja
          INNER JOIN job_posts jp ON jp.id = ja.job_post_id
          INNER JOIN companies c2 ON c2.id = jp.company_id
          WHERE c2.is_deleted = 0 AND c2.status = 1 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as activeApplications`,
          // Inactive applications from inactive companies
          `(SELECT COUNT(*)
          FROM job_applications ja
          INNER JOIN job_posts jp ON jp.id = ja.job_post_id
          INNER JOIN companies c2 ON c2.id = jp.company_id
          WHERE c2.is_deleted = 0 AND c2.status = 0 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as inactiveApplications`,
        ])
        .where('c.is_deleted = :isDeleted', { isDeleted: 0 })
        .getRawOne();

      let recruiterUser = null;
      if (company && company.user_id) {
        recruiterUser = await queryRunner.manager.findOne(UserEntity, {
          where: { id: company.user_id }
        });
      }

      await queryRunner.commitTransaction();

      if (recruiterUser && recruiterUser.email) {
        if (newStatus === 1) {
          (async () => {
            try {
              const countResult = await this.userRepo.query(
                `SELECT COUNT(*) as totalSeekers FROM users WHERE role = 'seeker' AND status = 'active'`
              );
              const totalSeekers = countResult[0]?.totalSeekers || 0;

              const sampleSeekers = await this.userRepo.query(
                `SELECT u.full_name, r.name as profession_name,
                   (
                     SELECT GROUP_CONCAT(sk.skill_name SEPARATOR ', ')
                     FROM seeker_skills ss
                     INNER JOIN skills sk ON sk.id = ss.skill_id
                     WHERE ss.seeker_id = sp.id
                   ) as skills
                 FROM seeker_profiles sp 
                 INNER JOIN users u ON u.id = sp.user_id 
                 LEFT JOIN roles r ON r.id = sp.profession_id
                 WHERE u.role = 'seeker' AND u.status = 'active'
                 ORDER BY u.id DESC 
                 LIMIT 3`
              );

              await this.emailService.sendTemplateEmail(
                'recruiter_profile_approved',
                recruiterUser.email,
                recruiterUser.fullName || 'Recruiter',
                {
                  preferred_name: recruiterUser.fullName || 'Recruiter',
                  candidate_count: totalSeekers,
                  candidates: sampleSeekers.map((s: any) => ({
                    full_name: s.full_name,
                    profession_name: s.profession_name || 'Professional Seeker',
                    skills: s.skills || 'Not Specified'
                  }))
                }
              );
            } catch (err) {
              console.error('Failed to send recruiter approved email:', err);
            }
          })();
        } else {
          this.emailService.sendTemplateEmail(
            'recruiter_profile_rejected',
            recruiterUser.email,
            recruiterUser.fullName || 'Recruiter',
            { preferred_name: recruiterUser.fullName || 'Recruiter' }
          ).catch(err => console.error('Failed to send recruiter rejected email:', err));
        }
      }

      return {
        success: true,
        message: `Company and recruiter status updated to ${newStatus === 1 ? 'Active' : 'Inactive'}`,
        data: {
          companyId,
          status: newStatus,
          stats: {
            totalCompanies: Number(stats?.totalCompanies ?? 0),
            activeCompanies: Number(stats?.activeCompanies ?? 0),
            inactiveCompanies: Number(stats?.inactiveCompanies ?? 0),
            totalJobPosts: Number(stats?.totalJobPosts ?? 0),
            activeJobPosts: Number(stats?.activeJobPosts ?? 0),
            inactiveJobPosts: Number(stats?.inactiveJobPosts ?? 0),
            totalApplications: Number(stats?.totalApplications ?? 0),
            activeApplications: Number(stats?.activeApplications ?? 0),
            inactiveApplications: Number(stats?.inactiveApplications ?? 0),
          },
        },
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw new Error(error.message);
    } finally {
      await queryRunner.release();
    }
  }
  async findAllPaginated(page: number, limit: number, search?: string) {
    try {
      const normalizedSearch = this.normalizeCompanySearch(search);
      const compactSearch = this.normalizeCompactCompanySearch(search);
      const offset = (page - 1) * limit;

      // 1) Use a query that selects company and user columns with aggregated counts from joins.
      // Doing this with LEFT JOINs to subqueries is generally faster than correlated subqueries in SELECT
      // when we need to order by those counts.
      const queryBuilder = this.companyRepo
        .createQueryBuilder('c')
        .leftJoin('users', 'u', 'u.id = c.user_id')
        .leftJoin('city', 'city', 'city.city_id = c.city_id')
        .leftJoin('state', 'state', 'state.state_id = c.state_id')
        .leftJoin('country', 'country', 'country.country_id = c.country_id')
        // Join with aggregated job post counts
        .leftJoin(
          (subQuery) =>
            subQuery
              .select('jp.company_id', 'company_id')
              .addSelect('COUNT(*)', 'count')
              .addSelect(
                'SUM(CASE WHEN (jp.status = "closed" OR (jp.expires_at IS NOT NULL AND jp.expires_at <= NOW())) THEN 1 ELSE 0 END)',
                'expiredCount',
              )
              .from('job_posts', 'jp')
              .groupBy('jp.company_id'),
          'jp_counts',
          'jp_counts.company_id = c.id',
        )
        // Join with aggregated application counts
        .leftJoin(
          (subQuery) =>
            subQuery
              .select('jp2.company_id', 'company_id')
              .addSelect('COUNT(*)', 'count')
              .from('job_applications', 'ja')
              .innerJoin('job_posts', 'jp2', 'jp2.id = ja.job_post_id')
              .where("jp2.status = 'open'")
              .andWhere('(jp2.expires_at IS NULL OR jp2.expires_at > NOW())')
              .groupBy('jp2.company_id'),
          'ja_counts',
          'ja_counts.company_id = c.id',
        )
        // Join with aggregated view counts
        .leftJoin(
          (subQuery) =>
            subQuery
              .select('jp3.company_id', 'company_id')
              .addSelect('COUNT(*)', 'count')
              .from('job_views', 'jv')
              .innerJoin('job_posts', 'jp3', 'jp3.id = jv.job_post_id')
              .groupBy('jp3.company_id'),
          'jv_counts',
          'jv_counts.company_id = c.id',
        )
        .select([
          'c.id as c_id',
          'c.name as c_name',
          'c.founded_at as c_founded_at',
          'c.logo as c_logo',
          'c.banner_url as c_banner_url',
          'c.about as c_about',
          'c.vision as c_vision',
          'c.location as c_location',
          'c.website as c_website',
          'c.company_size_id as c_company_size_id',
          'c.organization_type_id as c_organization_type_id',
          'c.created_at as c_created_at',
          'c.updated_at as c_updated_at',
          'c.industry_id as c_industry_id',
          'c.user_id as c_user_id',
          'c.country_id as c_country_id',
          'c.state_id as c_state_id',
          'c.city_id as c_city_id',
          'c.pincode as c_pincode',
          'c.latitude as c_latitude',
          'c.longitude as c_longitude',
          'c.featured as c_featured',
          'c.status as c_status',
          'c.is_deleted as c_is_deleted',
          'c.franchise_id as c_franchise_id',
          'c.gst_number as c_gst_number',
          'c.cin_number as c_cin_number',
          'c.is_admin_company as c_is_admin_company',
          'c.email as c_email',
          'c.phone as c_phone',
          'u.email as user_email',
          'u.full_name as user_full_name',
          'u.phone as user_phone',
          'u.role as user_role',
          'COALESCE(jp_counts.count, 0) as jobPosts',
          'COALESCE(ja_counts.count, 0) as applications',
          'COALESCE(jp_counts.expiredCount, 0) as expiredJobs',
          'COALESCE(jv_counts.count, 0) as views',
        ])
        .where('c.is_deleted = :isDeleted', { isDeleted: 0 });

      if (normalizedSearch) {
        queryBuilder.andWhere(
          new Brackets((qb) => {
            const lowerSearch = `%${normalizedSearch}%`;
            const compactLikeSearch = `%${compactSearch}%`;
            qb.where('LOWER(REPLACE(c.name, " ", "")) LIKE :compactSearch', {
              compactSearch: compactLikeSearch,
            })
              .orWhere('LOWER(c.name) LIKE :search', { search: lowerSearch })
              .orWhere('LOWER(c.email) LIKE :search', { search: lowerSearch })
              .orWhere('LOWER(u.email) LIKE :search', { search: lowerSearch })
              .orWhere(
                'LOWER(REPLACE(city.city_name, " ", "")) LIKE :compactSearch',
                {
                  compactSearch: compactLikeSearch,
                },
              )
              .orWhere('LOWER(city.city_name) LIKE :search', {
                search: lowerSearch,
              })
              .orWhere(
                'LOWER(REPLACE(state.state_name, " ", "")) LIKE :compactSearch',
                {
                  compactSearch: compactLikeSearch,
                },
              )
              .orWhere('LOWER(state.state_name) LIKE :search', {
                search: lowerSearch,
              })
              .orWhere(
                'LOWER(REPLACE(country.country_name, " ", "")) LIKE :compactSearch',
                { compactSearch: compactLikeSearch },
              )
              .orWhere('LOWER(country.country_name) LIKE :search', {
                search: lowerSearch,
              });
          }),
        );
      }

      const rawData = await queryBuilder
        .orderBy('c.status', 'DESC')
        .addOrderBy('COALESCE(ja_counts.count, 0)', 'DESC') // Order by the joined count
        .addOrderBy('c.created_at', 'DESC')
        .offset(offset)
        .limit(limit)
        .getRawMany();

      // Normalize numeric fields
      const data = rawData.map((r: any) => ({
        ...r,
        jobPosts: Number(r.jobPosts ?? 0),
        applications: Number(r.applications ?? 0),
        expiredJobs: Number(r.expiredJobs ?? 0),
        views: Number(r.views ?? 0),
      }));

      // total companies count for pagination (only non-deleted)
      const countQuery = this.companyRepo
        .createQueryBuilder('c')
        .leftJoin('users', 'u', 'u.id = c.user_id')
        .leftJoin('city', 'city', 'city.city_id = c.city_id')
        .leftJoin('state', 'state', 'state.state_id = c.state_id')
        .leftJoin('country', 'country', 'country.country_id = c.country_id')
        .where('c.is_deleted = :isDeleted', { isDeleted: 0 });

      if (normalizedSearch) {
        countQuery.andWhere(
          new Brackets((qb) => {
            const lowerSearch = `%${normalizedSearch}%`;
            const compactLikeSearch = `%${compactSearch}%`;
            qb.where('LOWER(REPLACE(c.name, " ", "")) LIKE :compactSearch', {
              compactSearch: compactLikeSearch,
            })
              .orWhere('LOWER(c.name) LIKE :search', { search: lowerSearch })
              .orWhere('LOWER(c.email) LIKE :search', { search: lowerSearch })
              .orWhere('LOWER(u.email) LIKE :search', { search: lowerSearch })
              .orWhere(
                'LOWER(REPLACE(city.city_name, " ", "")) LIKE :compactSearch',
                {
                  compactSearch: compactLikeSearch,
                },
              )
              .orWhere('LOWER(city.city_name) LIKE :search', {
                search: lowerSearch,
              })
              .orWhere(
                'LOWER(REPLACE(state.state_name, " ", "")) LIKE :compactSearch',
                {
                  compactSearch: compactLikeSearch,
                },
              )
              .orWhere('LOWER(state.state_name) LIKE :search', {
                search: lowerSearch,
              })
              .orWhere(
                'LOWER(REPLACE(country.country_name, " ", "")) LIKE :compactSearch',
                { compactSearch: compactLikeSearch },
              )
              .orWhere('LOWER(country.country_name) LIKE :search', {
                search: lowerSearch,
              });
          }),
        );
      }

      const totalCount = await countQuery.getCount();

      // 2) Compute global stats - we filter stats by search too so cards reflect results if searching
      const stats = {
        totalCompanies: 0,
        activeCompanies: 0,
        inactiveCompanies: 0,
        totalJobPosts: 0,
        activeJobPosts: 0,
        inactiveJobPosts: 0,
        totalApplications: 0,
        activeApplications: 0,
        inactiveApplications: 0,
        totalExpiredJobs: 0,
      };

      if (!normalizedSearch) {
        // Global stats for dashboard cards - super fast raw SQL for non-search case
        const globalStatsRaw = await this.dataSource.query(`
          SELECT 
            (SELECT COUNT(*) FROM companies WHERE is_deleted = 0) as totalCompanies,
            (SELECT COUNT(*) FROM companies WHERE is_deleted = 0 AND status = 1) as activeCompanies,
            (SELECT COUNT(*) FROM companies WHERE is_deleted = 0 AND status = 0) as inactiveCompanies,
            (SELECT COUNT(*) FROM job_posts jp INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0) as totalJobPosts,
            (SELECT COUNT(*) FROM job_posts jp INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as activeJobPosts,
            (SELECT COUNT(*) FROM companies c2 WHERE c2.is_deleted = 0 AND c2.status = 0) as inactiveJobPostsCount,
            (SELECT COUNT(*) FROM job_applications ja INNER JOIN job_posts jp ON jp.id = ja.job_post_id INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as totalApplications,
            (SELECT COUNT(*) FROM job_applications ja INNER JOIN job_posts jp ON jp.id = ja.job_post_id INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0 AND c2.status = 1 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as activeApplications,
            (SELECT COUNT(*) FROM job_applications ja INNER JOIN job_posts jp ON jp.id = ja.job_post_id INNER JOIN companies c2 ON c2.id = jp.company_id WHERE c2.is_deleted = 0 AND c2.status = 0 AND jp.status = 'open' AND (jp.expires_at IS NULL OR jp.expires_at > NOW())) as inactiveApplications,
            (SELECT COUNT(*) FROM job_posts jp WHERE jp.status = 'closed' OR (jp.expires_at IS NOT NULL AND jp.expires_at <= NOW())) as totalExpiredJobs
        `);

        const s = globalStatsRaw[0];
        stats.totalCompanies = Number(s?.totalCompanies ?? 0);
        stats.activeCompanies = Number(s?.activeCompanies ?? 0);
        stats.inactiveCompanies = Number(s?.inactiveCompanies ?? 0);
        stats.totalJobPosts = Number(s?.totalJobPosts ?? 0);
        stats.activeJobPosts = Number(s?.activeJobPosts ?? 0);
        stats.inactiveJobPosts = Number(s?.inactiveJobPostsCount ?? 0); // Correctly mapped
        stats.totalApplications = Number(s?.totalApplications ?? 0);
        stats.activeApplications = Number(s?.activeApplications ?? 0);
        stats.inactiveApplications = Number(s?.inactiveApplications ?? 0);
        stats.totalExpiredJobs = Number(s?.totalExpiredJobs ?? 0);
      } else {
        // When searching, stats show filtered company counts
        const statsQuery = this.companyRepo
          .createQueryBuilder('c')
          .leftJoin('users', 'u', 'u.id = c.user_id')
          .leftJoin('city', 'city', 'city.city_id = c.city_id')
          .leftJoin('state', 'state', 'state.state_id = c.state_id')
          .leftJoin('country', 'country', 'country.country_id = c.country_id')
          .select([
            'COUNT(c.id) as totalCompanies',
            'SUM(CASE WHEN c.status = 1 THEN 1 ELSE 0 END) as activeCompanies',
            'SUM(CASE WHEN c.status = 0 THEN 1 ELSE 0 END) as inactiveCompanies',
          ])
          .where('c.is_deleted = :isDeleted', { isDeleted: 0 });

        const searchLower = `%${normalizedSearch}%`;
        const compactLikeSearch = `%${compactSearch}%`;
        statsQuery.andWhere(
          new Brackets((qb) => {
            qb.where('LOWER(REPLACE(c.name, " ", "")) LIKE :compactSearch', {
              compactSearch: compactLikeSearch,
            })
              .orWhere('LOWER(c.name) LIKE :search', { search: searchLower })
              .orWhere('LOWER(c.email) LIKE :search', { search: searchLower })
              .orWhere('LOWER(u.email) LIKE :search', { search: searchLower })
              .orWhere(
                'LOWER(REPLACE(city.city_name, " ", "")) LIKE :compactSearch',
                {
                  compactSearch: compactLikeSearch,
                },
              )
              .orWhere('LOWER(city.city_name) LIKE :search', {
                search: searchLower,
              })
              .orWhere(
                'LOWER(REPLACE(state.state_name, " ", "")) LIKE :compactSearch',
                {
                  compactSearch: compactLikeSearch,
                },
              )
              .orWhere('LOWER(state.state_name) LIKE :search', {
                search: searchLower,
              })
              .orWhere(
                'LOWER(REPLACE(country.country_name, " ", "")) LIKE :compactSearch',
                { compactSearch: compactLikeSearch },
              )
              .orWhere('LOWER(country.country_name) LIKE :search', {
                search: searchLower,
              });
          }),
        );

        const searchStatsRaw = await statsQuery.getRawOne();
        stats.totalCompanies = Number(searchStatsRaw?.totalCompanies ?? 0);
        stats.activeCompanies = Number(searchStatsRaw?.activeCompanies ?? 0);
        stats.inactiveCompanies = Number(
          searchStatsRaw?.inactiveCompanies ?? 0,
        );
      }

      // Return the same shape as before for frontend compatibility
      return {
        success: true,
        data,
        totalItems: Number(totalCount),
        totalPages: Math.ceil(Number(totalCount) / limit),
        currentPage: page,
        stats,
      };
    } catch (error) {
      console.error('findAllPaginated aggregated error:', error);
      return {
        success: false,
        message: 'Failed to fetch companies',
        error: error.message,
      };
    }
  }

  async findAll(): Promise<Companies[]> {
    return this.companyRepo.find();
  }

  // Get companies for job post dropdown - for admin and associate (shows only dummy companies where is_admin_company = 1)
  async getCompaniesForJobPost(
    userRole: string,
    page: number = 1,
    limit: number = 10,
    search?: string,
  ): Promise<any> {
    try {
      const normalizedRole = userRole?.toLowerCase();
      const pageNum = Math.max(1, Number(page) || 1);
      const limitNum = Number(limit);

      if (normalizedRole === 'admin' || normalizedRole === 'associate') {
        const queryBuilder = this.companyRepo.createQueryBuilder('c');

        queryBuilder
          .leftJoin('city', 'city', 'city.city_id = c.city_id')
          .leftJoin('state', 'state', 'state.state_id = c.state_id');

        queryBuilder.select([
          'c.id AS id',
          'c.name AS name',
          'c.logo AS logo',
          'c.location AS location',
          'c.is_admin_company AS is_admin_company',
          'c.gst_number AS gst_number',
          'c.cin_number AS cin_number',
          'c.website AS website',
          'city.city_name AS city_name',
          'state.state_name AS state_name',
        ]);

        queryBuilder
          .where('c.status = :status', { status: 1 })
          .andWhere('c.is_admin_company = :isAdmin', { isAdmin: 1 })
          .andWhere('c.user_id IS NULL')
          .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 });

        if (search) {
          const searchLower = `%${search.toLowerCase()}%`;
          queryBuilder.andWhere('LOWER(c.name) LIKE :search', {
            search: searchLower,
          });
        }

        let companies;
        let total;

        // If limit is -1 or any negative number, fetch all
        if (limitNum <= 0) {
          companies = await queryBuilder.orderBy('c.name', 'ASC').getRawMany();
          total = companies.length;
        } else {
          const skip = (pageNum - 1) * limitNum;
          total = await queryBuilder.getCount();

          companies = await queryBuilder
            .orderBy('c.name', 'ASC')
            .offset(skip)
            .limit(limitNum)
            .getRawMany();
        }

        return {
          success: true,
          data: companies,
          total,
          page: limitNum <= 0 ? 1 : pageNum,
          limit: limitNum <= 0 ? total : limitNum,
          totalPages: limitNum <= 0 ? 1 : Math.ceil(total / limitNum),
          message: `${normalizedRole === 'admin' ? 'Admin' : 'Associate'} companies fetched successfully`,
        };
      }

      return {
        success: false,
        data: [],
        message: 'This endpoint is only for admin and associate users',
      };
    } catch (error) {
      console.error('Error in getCompaniesForJobPost:', error);
      return {
        success: false,
        data: [],
        message: 'Failed to fetch companies',
        error: error.message,
      };
    }
  }

  // Create admin dummy company
  // NOTE: Commented out because dummy companies are hardcoded in SQL migration file
  // async createAdminDummyCompany(data: Partial<Companies>): Promise<any> {
  //   try {
  //     // Check if company name already exists
  //     if (data.name) {
  //       const existingCompany = await this.companyRepo
  //         .createQueryBuilder('c')
  //         .where('c.name = :name', { name: data.name })
  //         .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 })
  //         .getOne();

  //       if (existingCompany) {
  //         throw new Error(
  //           'Company name already exists. Please choose a different name.',
  //         );
  //       }
  //     }

  //     // Create company with is_admin_company = 1
  //     const companyData = {
  //       ...data,
  //       is_admin_company: 1,
  //       status: 1,
  //       is_approved: 1,
  //       is_rejected: 0,
  //       user_id: null, // No user associated
  //       featured: 0,
  //       is_deleted: 0,
  //     };

  //     const newCompany = this.companyRepo.create(companyData);
  //     const savedCompany = await this.companyRepo.save(newCompany);

  //     return {
  //       success: true,
  //       data: savedCompany,
  //       message: 'Admin dummy company created successfully',
  //     };
  //   } catch (error) {
  //     console.error('Error in createAdminDummyCompany:', error);
  //     return {
  //       success: false,
  //       message: 'Failed to create admin dummy company',
  //       error: error.message,
  //     };
  //   }
  // }

  async findOne(id: number): Promise<Companies> {
    const company = await this.companyRepo.findOne({ where: { id } });
    if (!company) {
      throw new NotFoundException('Company not found');
    }
    return company;
  }

  async create(data: Partial<Companies>): Promise<Companies> {
    // Check if company name + city combination already exists
    if (data.name && data.city_id) {
      const existingCompany = await this.companyRepo
        .createQueryBuilder('c')
        .where('c.name = :name', { name: data.name })
        .andWhere('c.city_id = :cityId', { cityId: data.city_id })
        .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 })
        .getOne();

      if (existingCompany) {
        throw new Error(
          'Company with this name already exists in this city. Please choose a different name or city.',
        );
      }
    }

    const newCompany = await this.companyRepo.save(
      this.companyRepo.create(data),
    );

    // Handle social links if provided
    if (
      (data as any).social_links &&
      Array.isArray((data as any).social_links)
    ) {
      try {
        const socialLinksRepo =
          this.dataSource.getRepository(CompanySocialLink);
        const socialLinks = (data as any).social_links.map((link: any) => ({
          companyId: newCompany.id,
          platformId: link.platform_id,
          url: link.url,
        }));
        await socialLinksRepo.save(socialLinks);
      } catch (error) {
        console.error('Error saving social links:', error);
        // Continue even if social links fail - company is created
      }
    }

    // Handle key people if provided
    if ((data as any).key_people && Array.isArray((data as any).key_people)) {
      try {
        const keyPeopleValues = (data as any).key_people.map((person: any) => ({
          company_id: newCompany.id,
          name: person.name,
          designation: person.designation,
          email: person.email || null,
          phone: person.phone || null,
          visibility_level: person.visibility_level || 'full_contact',
        }));

        await this.dataSource
          .createQueryBuilder()
          .insert()
          .into('company_key_people')
          .values(keyPeopleValues)
          .execute();
      } catch (error) {
        console.error('Error saving key people:', error);
      }
    }

    return newCompany;
  }

  async update(id: number, data: Partial<Companies>): Promise<Companies> {
    // Sanitize data: convert empty strings to null for nullable FK fields to prevent DB errors
    console.log(
      `[Update Company v2] Payload for ID ${id}:`,
      JSON.stringify(data),
    );

    // Sanitize data: convert empty strings to null for nullable FK fields to prevent DB errors
    const nullableIdFields = [
      'area_id',
      'city_id',
      'state_id',
      'country_id',
      'industry_id',
      'organization_type_id',
      'company_size_id',
      'franchise_id',
      'user_id',
      'logo',
      'banner_url',
      'cin_number',
      'gst_number',
    ];

    for (const field of nullableIdFields) {
      // Explicitly cast to any to access dynamic properties and check for empty string
      if ((data as any)[field] === '' || (data as any)[field] === 'null') {
        (data as any)[field] = null;
      }
    }

    console.log(
      `[Update Company] Sanitized Payload for ID ${id}:`,
      JSON.stringify(data),
    );

    // Capture arrays to update separately
    const socialLinksToUpdate = (data as any).social_links;
    const keyPeopleToUpdate = (data as any).key_people;

    // Remove non-column fields that might be present in the payload
    delete (data as any).social_links;
    delete (data as any).key_people;
    delete (data as any).jobPosts;
    delete (data as any).applications;
    delete (data as any).expiredJobs;
    delete (data as any).views;
    delete (data as any).c_id; // aliases from raw query
    delete (data as any).user_full_name;
    delete (data as any).user_email;
    delete (data as any).user_phone;
    delete (data as any).user_role;

    // Check if company name + city combination already exists (excluding current company )
    if (data.name && data.city_id) {
      const existingCompany = await this.companyRepo
        .createQueryBuilder('c')
        .where('c.name = :name', { name: data.name })
        .andWhere('c.city_id = :cityId', { cityId: data.city_id })
        .andWhere('c.id != :id', { id })
        .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 })
        .getOne();

      if (existingCompany) {
        throw new Error(
          'Company with this name already exists in this city. Please choose a different name or city.',
        );
      }
    }

    await this.companyRepo.update(id, data);

    // Update Social Links
    if (socialLinksToUpdate && Array.isArray(socialLinksToUpdate)) {
      try {
        const socialLinksRepo =
          this.dataSource.getRepository(CompanySocialLink);
        // Delete all existing for this company
        await socialLinksRepo.delete({ companyId: id });

        // Insert new ones
        const newLinks = socialLinksToUpdate.map((link: any) => ({
          companyId: id,
          platformId: link.platform_id,
          url: link.url,
        }));
        if (newLinks.length > 0) {
          await socialLinksRepo.save(newLinks);
        }
      } catch (error) {
        console.error('Error updating social links:', error);
      }
    }

    // Update Key People
    if (keyPeopleToUpdate && Array.isArray(keyPeopleToUpdate)) {
      try {
        // Delete all existing
        await this.dataSource.query(
          'DELETE FROM company_key_people WHERE company_id = ?',
          [id],
        );

        // Insert new ones
        const newPeople = keyPeopleToUpdate.map((person: any) => ({
          company_id: id,
          name: person.name,
          designation: person.designation,
          email: person.email || null,
          phone: person.phone || null,
          visibility_level: person.visibility_level || 'full_contact',
        }));

        if (newPeople.length > 0) {
          await this.dataSource
            .createQueryBuilder()
            .insert()
            .into('company_key_people')
            .values(newPeople)
            .execute();
        }
      } catch (error) {
        console.error('Error updating key people:', error);
      }
    }

    return this.findOne(id);
  }

  async remove(id: number): Promise<void> {
    await this.companyRepo.delete(id);
  }

  async findAllWithOpenPositions(limit?: number): Promise<any[]> {
    const qb = this.companyRepo
      .createQueryBuilder('companies')
      .leftJoin(
        'job_posts',
        'job',
        'job.company_id = companies.id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .leftJoin(
        'country',
        'country',
        'country.country_id = companies.country_id',
      )
      .leftJoin('state', 'state', 'state.state_id = companies.state_id')
      .leftJoin('city', 'city', 'city.city_id = companies.city_id')
      .select([
        'companies.id as id',
        'companies.name as name',
        'companies.featured as featured',
        'CONCAT(city.city_name, ", ", state.state_name, ", ", country.country_name) as location',
        'companies.logo as logo',
        'COUNT(job.id) as positions',
      ])
      .where('companies.is_deleted = :isDeleted', { isDeleted: 0 })
      .andWhere('companies.status = :companyStatus', { companyStatus: 1 })
      .andWhere('companies.is_approved = :isApproved', { isApproved: 1 })
      .andWhere('companies.is_rejected = :isRejected', { isRejected: 0 })
      .groupBy('companies.id')
      .addGroupBy('companies.name')
      .addGroupBy('companies.logo')
      .addGroupBy('country.country_name')
      .addGroupBy('state.state_name')
      .addGroupBy('city.city_name')
      .orderBy('positions', 'DESC');

    if (limit) {
      qb.limit(limit);
    }

    return qb.getRawMany();
  }

  async findFilteredCompanies(filters: {
    search?: string;
    industry?: string;
    organizationType?: string;
    companySize?: string;
    country?: string;
    state?: string;
    city?: string;
    featured?: string;
    positions?: string;
    excludeAdmin?: string | boolean;
    page?: number;
    limit?: number;
  }): Promise<{
    data: any[];
    total: number;
    page: number;
    limit: number;
    totalPages: number;
  }> {
    // console.log(' Backend - Received filters:', filters);
    // console.log(' Backend - City filter value:', filters.city);
    // console.log(' Backend - Featured filter value:', filters.featured);
    const page = filters.page || 1;
    const limit = filters.limit || 10;
    const offset = (page - 1) * limit;

    const queryBuilder = this.companyRepo
      .createQueryBuilder('companies')
      .leftJoin(
        'job_posts',
        'job',
        'job.company_id = companies.id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .leftJoin('industries', 'industry', 'industry.id = companies.industry_id')
      .leftJoin(
        'organization_types',
        'orgType',
        'orgType.id = companies.organization_type_id',
      )
      .leftJoin(
        'company_sizes',
        'companySize',
        'companySize.id = companies.company_size_id',
      )
      .leftJoin('users', 'user', 'user.id = companies.user_id')
      .leftJoin(
        'country',
        'country',
        'country.country_id = companies.country_id',
      )
      .leftJoin('state', 'state', 'state.state_id = companies.state_id')
      .leftJoin('city', 'city', 'city.city_id = companies.city_id')
      .select([
        'companies.id as id',
        'companies.name as name',
        'companies.featured as featured',
        'COALESCE(NULLIF(CONCAT_WS(", ", NULLIF(city.city_name, ""), NULLIF(state.state_name, ""), NULLIF(country.country_name, "")), ""), companies.location) as location',
        'companies.website as website',
        'companies.about as description',
        'companies.founded_at as founded',
        'companies.logo as logo',
        'COUNT(job.id) as positions',
        'industry.name as industry',
        'orgType.label as organizationType',
        'companySize.label as companySize',
        // 'user.email as email',
        // 'user.phone as phone',
        'CASE WHEN companies.is_admin_company = 1 THEN companies.email ELSE user.email END as email',
        'CASE WHEN companies.is_admin_company = 1 THEN companies.phone ELSE user.phone END as phone',
        'country.country_name as country',
        'state.state_name as state',
        'city.city_name as city',
      ])
      .groupBy('companies.id')
      .addGroupBy('companies.name')
      .addGroupBy('companies.website')
      .addGroupBy('companies.about')
      .addGroupBy('companies.founded_at')
      .addGroupBy('companies.featured')
      .addGroupBy('companies.logo')
      .addGroupBy('companies.is_admin_company')
      .addGroupBy('companies.email')
      .addGroupBy('companies.phone')
      .addGroupBy('industry.name')
      .addGroupBy('orgType.label')
      .addGroupBy('companySize.label')
      .addGroupBy('user.email')
      .addGroupBy('user.phone')
      .addGroupBy('country.country_name')
      .addGroupBy('state.state_name')
      .addGroupBy('city.city_name')
      .where('companies.is_deleted = :isDeleted', { isDeleted: 0 })
      .andWhere('companies.status = :status', { status: 1 })
      .andWhere('companies.is_approved = :isApproved', { isApproved: 1 })
      .andWhere('companies.is_rejected = :isRejected', { isRejected: 0 });

    // Apply search filter
    // Apply search filter
    if (filters.search) {
      const raw = filters.search.trim();

      // Normalize search string: lowercase, replace special chars with spaces
      // We do NOT use toMatchKey here because it expands abbreviations (e.g. Pvt -> Private)
      // which might not exist in the database column (e.g. it has "PVT.").
      const normalizedRaw = raw
        .toLowerCase()
        .replace(/[^a-z0-9\s]/g, ' ')
        .replace(/\s+/g, ' ')
        .trim();

      const tokens = leadTokens(raw, 6);

      queryBuilder.andWhere(
        new Brackets((qb) => {
          // Strategy 1: Match normalized words against normalized company name
          if (normalizedRaw) {
            const searchWords = normalizedRaw.split(' ').filter(Boolean);
            if (searchWords.length > 0) {
              qb.where(
                new Brackets((q1) => {
                  searchWords.forEach((word, idx) => {
                    // Match each word. We replace common punctuation in DB with spaces to ensure separation
                    // e.g. "Q.B.Finance" -> "Q B Finance"
                    q1.andWhere(
                      `LOWER(REPLACE(REPLACE(companies.name, '.', ' '), '-', ' ')) LIKE :word${idx}`,
                      {
                        [`word${idx}`]: `%${word}%`,
                      },
                    );
                  });
                }),
              );
            }
          }

          // Strategy 2: Also search in location and industry for broader results
          qb.orWhere(
            '(city.city_name LIKE :locSearch OR state.state_name LIKE :locSearch OR country.country_name LIKE :locSearch OR industry.name LIKE :locSearch)',
            { locSearch: `%${raw}%` },
          );

          // Strategy 3: Token-based search (original logic for backward compatibility)
          if (tokens.length) {
            qb.orWhere(
              new Brackets((q2) => {
                for (let i = 0; i < tokens.length; i++) {
                  q2.andWhere(`companies.name LIKE :tk${i}`, {
                    [`tk${i}`]: `%${tokens[i]}%`,
                  });
                }
              }),
            );
          }
        }),
      );
    }

    // Apply industry filter
    if (filters.industry) {
      // console.log(' Backend - Applying industry filter:', filters.industry);
      queryBuilder.andWhere('industry.name = :industry', {
        industry: filters.industry,
      });
    }

    // Apply organization type filter
    if (filters.organizationType) {
      // console.log(
      //   ' Backend - Applying organization type filter:',
      //   filters.organizationType,
      // );
      queryBuilder.andWhere('orgType.label = :organizationType', {
        organizationType: filters.organizationType,
      });
    }

    // Apply company size filter
    if (filters.companySize) {
      // console.log(
      //   ' Backend - Applying company size filter:',
      //   filters.companySize,
      // );
      queryBuilder.andWhere('companySize.label = :companySize', {
        companySize: filters.companySize,
      });
    }

    // Apply location filters (country, state, city)
    if (filters.country) {
      // console.log(' Backend - Applying country filter:', filters.country);
      queryBuilder.andWhere('country.country_name = :country', {
        country: filters.country,
      });
    }

    if (filters.state) {
      // console.log(' Backend - Applying state filter:', filters.state);
      queryBuilder.andWhere('state.state_name = :state', {
        state: filters.state,
      });
    }

    if (filters.city) {
      // console.log(' Backend - Applying city filter:', filters.city);
      queryBuilder.andWhere('city.city_name = :city', {
        city: filters.city,
      });
    } else {
      // console.log(' Backend - No city filter applied');
    }

    // Apply featured filter
    if (filters.featured) {
      // console.log(' Backend - Featured filter value:', filters.featured);
      // console.log(' Backend - Filter type:', typeof filters.featured);
      // console.log(' Backend - Filter length:', filters.featured.length);
      if (filters.featured === 'featured') {
        // console.log(' Backend - Applying featured = 1 filter');
        queryBuilder.andWhere('companies.featured = :featured', {
          featured: 1,
        });
      } else if (filters.featured === 'non-featured') {
        // console.log(' Backend - Applying featured = 0 filter');
        queryBuilder.andWhere('companies.featured = :featured', {
          featured: 0,
        });
      } else {
        // console.log(
        //   ' Backend - Featured filter value does not match expected values',
        // );
      }
    } else {
      // console.log(' Backend - No featured filter applied');
    }

    // Debug: Log the generated SQL query
    // console.log(' Backend - Generated SQL query:', queryBuilder.getSql());

    // Debug: Check what featured values exist in the database
    // const featuredCheckQuery = this.companyRepo
    //   .createQueryBuilder('companies')
    //   .select(['companies.id', 'companies.name', 'companies.featured'])
    //   .limit(10);
    // const featuredCheckResult = await featuredCheckQuery.getRawMany();
    // console.log(
    //   ' Backend - Sample featured values in database:',
    //   featuredCheckResult,
    // );

    // Also check the actual SQL for featured values
    // const featuredSQL = featuredCheckQuery.getSql();
    // console.log(' Backend - Featured check SQL:', featuredSQL);

    // Apply positions filter (minimum positions)
    if (filters.positions) {
      const minPositions = parseInt(filters.positions);
      queryBuilder.having('COUNT(job.id) >= :minPositions', { minPositions });
    }

    // Apply excludeAdmin filter
    if (filters.excludeAdmin === 'true' || filters.excludeAdmin === true) {
      queryBuilder.andWhere('companies.is_admin_company = :excludeAdminVal', {
        excludeAdminVal: 0,
      });
      queryBuilder.andWhere('companies.user_id IS NOT NULL');
    }

    // Get total count before applying pagination
    const totalCountQuery = this.companyRepo
      .createQueryBuilder('companies')
      .leftJoin(
        'job_posts',
        'job',
        'job.company_id = companies.id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .leftJoin('industries', 'industry', 'industry.id = companies.industry_id')
      .leftJoin(
        'organization_types',
        'orgType',
        'orgType.id = companies.organization_type_id',
      )
      .leftJoin(
        'company_sizes',
        'companySize',
        'companySize.id = companies.company_size_id',
      )
      .leftJoin('users', 'user', 'user.id = companies.user_id')
      .leftJoin(
        'country',
        'country',
        'country.country_id = companies.country_id',
      )
      .leftJoin('state', 'state', 'state.state_id = companies.state_id')
      .leftJoin('city', 'city', 'city.city_id = companies.city_id')
      .select('COUNT(DISTINCT companies.id)', 'total')
      .where('companies.is_deleted = :isDeleted', { isDeleted: 0 })
      .andWhere('companies.status = :status', { status: 1 })
      .andWhere('companies.is_approved = :isApproved', { isApproved: 1 })
      .andWhere('companies.is_rejected = :isRejected', { isRejected: 0 });

    // Apply the same filters to the count query
    if (filters.search) {
      const normalizedSearch = filters.search.trim().replace(/\./g, '');
      totalCountQuery.andWhere(
        "(REPLACE(companies.name, '.', '') LIKE :search OR city.city_name LIKE :search OR state.state_name LIKE :search OR country.country_name LIKE :search OR industry.name LIKE :search)",
        { search: `%${normalizedSearch}%` },
      );
    }

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

    if (filters.organizationType) {
      totalCountQuery.andWhere('orgType.label = :organizationType', {
        organizationType: filters.organizationType,
      });
    }

    if (filters.companySize) {
      totalCountQuery.andWhere('companySize.label = :companySize', {
        companySize: filters.companySize,
      });
    }

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

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

    if (filters.city) {
      // console.log(
      //   ' Backend - Count query - Applying city filter:',
      //   filters.city,
      // );
      totalCountQuery.andWhere('city.city_name = :city', {
        city: filters.city,
      });
    } else {
      // console.log(' Backend - Count query - No city filter applied');
    }

    if (filters.featured) {
      // console.log(
      //   ' Backend - Count query - Featured filter value:',
      //   filters.featured,
      // );
      // console.log(
      //   ' Backend - Count query - Filter type:',
      //   typeof filters.featured,
      // );
      if (filters.featured === 'featured') {
        // console.log(' Backend - Count query - Applying featured = 1 filter');
        totalCountQuery.andWhere('companies.featured = :featured', {
          featured: 1,
        });
      } else if (filters.featured === 'non-featured') {
        // console.log(' Backend - Count query - Applying featured = 0 filter');
        totalCountQuery.andWhere('companies.featured = :featured', {
          featured: 0,
        });
      } else {
        // console.log(
        //   ' Backend - Count query - Featured filter value does not match expected values',
        // );
      }
    } else {
      // console.log(' Backend - Count query - No featured filter applied');
    }

    // Apply positions filter to count query as well
    if (filters.positions) {
      const minPositions = parseInt(filters.positions);
      totalCountQuery.having('COUNT(job.id) >= :minPositions', {
        minPositions,
      });
    }

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

    // Order by status first (active companies first), then featured status, then by created date
    queryBuilder
      // .orderBy('companies.status', 'DESC')
      // .addOrderBy('companies.featured', 'DESC')
      // .addOrderBy('companies.created_at', 'DESC');
      .orderBy('companies.featured', 'DESC')
      .addOrderBy('positions', 'DESC')
      .addOrderBy('companies.created_at', 'DESC');
    // Apply pagination
    queryBuilder.offset(offset).limit(limit);

    const result = await queryBuilder.getRawMany();

    // Test: Get companies in Bhubaneswar specifically
    if (filters.city) {
      // const cityCompaniesQuery = this.companyRepo
      //   .createQueryBuilder('companies')
      //   .leftJoin('city', 'city', 'city.city_id = companies.city_id')
      //   .select([
      //     'companies.id',
      //     'companies.name',
      //     'companies.featured',
      //     'city.city_name',
      //   ])
      //   .where('city.city_name = :city', { city: filters.city })
      //   .limit(5);
      // const cityCompanies = await cityCompaniesQuery.getRawMany();
      // console.log(
      //   ' Backend - Companies in city',
      //   filters.city,
      //   ':',
      //   cityCompanies,
      // );
    }

    const totalPages = Math.ceil(total / limit);

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

  async getFilterOptions(): Promise<any> {
    try {
      // Get industries
      const industries = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('industries', 'i')
        .where('i.status = 1')
        .orderBy('i.name', 'ASC')
        .getRawMany();

      // Get company sizes
      const companySizes = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'label'])
        .from('company_sizes', 'cs')
        .where('cs.status = 1')
        .orderBy('cs.label', 'ASC')
        .getRawMany();

      // Get organization types
      const organizationTypes = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'label'])
        .from('organization_types', 'ot')
        .where('ot.status = 1')
        .orderBy('ot.label', 'ASC')
        .getRawMany();

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

      // Get states
      const states = await this.dataSource
        .createQueryBuilder()
        .select(['state_id as id', 'state_name as name', 'country_id'])
        .from('state', 's')
        .where('s.status = 1')
        .orderBy('s.state_name', 'ASC')
        .getRawMany();

      // Get cities
      const cities = await this.dataSource
        .createQueryBuilder()
        .select(['city_id as id', 'city_name as name', 'state_id'])
        .from('city', 'c')
        .where('c.status = 1')
        .orderBy('c.city_name', 'ASC')
        .getRawMany();

      return {
        industries: industries.map((i) => ({ id: i.id, name: i.name })),
        companySizes: companySizes.map((cs) => ({ id: cs.id, name: cs.label })),
        organizationTypes: organizationTypes.map((ot) => ({
          id: ot.id,
          name: ot.label,
        })),
        countries: countries.map((c) => ({ id: c.id, name: c.name })),
        states: states.map((s) => ({
          id: s.id,
          name: s.name,
          countryId: s.country_id,
        })),
        cities: cities.map((c) => ({
          id: c.id,
          name: c.name,
          stateId: c.state_id,
        })),
      };
    } catch (error) {
      console.error('Error fetching filter options:', error);
      throw error;
    }
  }
  // async findOneWithOpenJobs(id: number, page = 1, limit = 9): Promise<any> {
  //   // Join industries to get industry name
  //   const company = await this.companyRepo
  //     .createQueryBuilder('company')
  //     .leftJoin('industries', 'industry', 'industry.id = company.industry_id')
  //     .addSelect(['industry.name as industry_name'])
  //     .where('company.id = :id', { id })
  //     .getRawOne();
  //   if (!company) throw new NotFoundException('Company not found');

  //   const openJobs = await this.jobPostsRepo
  //     .createQueryBuilder('job')
  //     .leftJoin('city', 'city', 'city.city_id = job.city_id')
  //     .leftJoin('state', 'state', 'state.state_id = job.state_id')
  //     .leftJoin('country', 'country', 'country.country_id = job.country_id')
  //     .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
  //     .addSelect([
  //       'city.city_name as city_name',
  //       'state.state_name as state_name',
  //       'country.country_name as country_name',
  //       'jobType.name as job_type_name',
  //       'job.featured as featured',
  //     ])
  //     .where('job.company_id = :id', { id })
  //     .andWhere('job.status = :status', { status: 'open' })
  //     .skip((page - 1) * limit)
  //     .take(limit)
  //     .getRawMany();

  //   const total = await this.jobPostsRepo
  //     .createQueryBuilder('job')
  //     .where('job.company_id = :id', { id })
  //     .andWhere('job.status = :status', { status: 'open' })
  //     .getCount();
  //   return {
  //     company: {
  //       ...company,
  //       industry: company.industry_name,
  //     },
  //     openJobs,
  //     total,
  //   };
  // }
  async findOneWithOpenJobs(id: number, page = 1, limit = 9): Promise<any> {
    // Fetch company details with all metadata (industry, orgType, size)
    const company = await this.companyRepo
      .createQueryBuilder('company')
      .leftJoin('industries', 'industry', 'industry.id = company.industry_id')
      .leftJoin(
        'organization_types',
        'orgType',
        'orgType.id = company.organization_type_id',
      )
      .leftJoin('company_sizes', 'size', 'size.id = company.company_size_id')
      .leftJoin('users', 'user', 'user.id = company.user_id')
      .leftJoin('city', 'city', 'city.city_id = company.city_id')
      .leftJoin('state', 'state', 'state.state_id = company.state_id')
      .leftJoin('country', 'country', 'country.country_id = company.country_id')
      .leftJoin('area', 'area', 'area.id = company.area_id')
      .addSelect([
        'industry.name AS industry_name',
        'orgType.label AS organization_type_name',
        'size.label AS company_size_name',
        "DATE_FORMAT(company.founded_at, '%Y-%m-%d') AS company_founded_at",
        'user.email AS user_email',
        'user.phone AS user_phone',
        'COALESCE(NULLIF(CONCAT_WS(", ", NULLIF(city.city_name, ""), NULLIF(state.state_name, ""), NULLIF(country.country_name, "")), ""), company.location) AS smart_location',
        'company.location AS c_location',
        'city.city_name AS ci_city_name',
        'state.state_name AS s_state_name',
        'country.country_name AS co_country_name',
        'area.area AS area_name',
        'company.logo AS c_logo',
        'company.banner_url AS c_banner_url',
        'company.name AS c_name',
        'company.about AS c_about',
        'company.is_admin_company AS c_is_admin_company',
        'company.email AS c_email',
        'company.phone AS c_phone',
        'company.status AS c_status',
        'company.created_at AS c_created_at',
      ])
      .where('company.id = :id', { id })
      .getRawOne();

    if (!company) throw new NotFoundException('Company not found');

    // Fetch social links joined with platform names
    const socialLinks = await this.companyRepo
      .createQueryBuilder('company')
      .leftJoin(
        'company_social_links',
        'links',
        'links.company_id = company.id',
      )
      .leftJoin(
        'social_platform',
        'platform',
        'platform.id = links.platform_id',
      )
      .select(['platform.name AS platform', 'links.url AS url'])
      .where('company.id = :id', { id })
      .getRawMany();

    // Fetch key people
    const keyPeople = await this.dataSource.query(
      'SELECT id, name, designation, email, phone, visibility_level FROM company_key_people WHERE company_id = ?',
      [id],
    );

    // Fetch open jobs with pagination
    const offset = (page - 1) * limit;

    const openJobs = await this.jobPostsRepo
      .createQueryBuilder('job')
      .leftJoin('companies', 'company', 'company.id = job.company_id')
      .leftJoin('city', 'city', 'city.city_id = job.city_id')
      .leftJoin('state', 'state', 'state.state_id = job.state_id')
      .leftJoin('country', 'country', 'country.country_id = job.country_id')
      .leftJoin('job_type', 'jobType', 'jobType.id = job.job_type_id')
      .addSelect([
        'job.id AS job_id',
        'job.title AS job_title',
        'job.salary_min',
        'job.salary_max',
        'job.status',
        'job.featured',
        'city.city_name AS city_name',
        'state.state_name AS state_name',
        'country.country_name AS country_name',
        'jobType.name AS job_type_name',
        'company.logo AS company_logo',
        'job.image_url AS image_url',
      ])
      .where('job.company_id = :id', { id })
      .andWhere('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .orderBy('job.created_at', 'DESC')
      .offset(offset)
      .limit(limit)
      .getRawMany();

    // Count total open jobs
    const total = await this.jobPostsRepo
      .createQueryBuilder('job')
      .where('job.company_id = :id', { id })
      .andWhere('job.status = :status', { status: 'open' })
      .andWhere('(job.expires_at IS NULL OR job.expires_at > NOW())')
      .getCount();

    return {
      company: {
        ...company,
        industry: company.industry_name,
        organization_type: company.organization_type_name,
        company_size: company.company_size_name,
        social_links: socialLinks.map((link) => ({
          platform: link.platform,
          url: link.url,
        })),
        key_people: keyPeople,
      },
      openJobs,
      total,
    };
  }
  //find company_id
  async findCompanyIdByUserId(userId: number): Promise<number | null> {
    const company = await this.companyRepo.findOne({
      where: { user_id: userId },
      select: ['id'],
    });
    return company ? company.id : null;
  }

  // Check if company name is available
  async checkNameAvailability(name: string): Promise<Companies | null> {
    return this.companyRepo
      .createQueryBuilder('c')
      .where('c.name = :name', { name })
      .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 })
      .getOne();
  }

  // Get companies for admin approval (pending companies)
  async getCompaniesForApproval(
    page: number = 1,
    limit: number = 10,
    status?: string,
    search?: string,
  ) {
    const dataQuery = this.companyRepo
      .createQueryBuilder('c')
      .leftJoin('users', 'u', 'u.id = c.user_id')
      .select([
        'c.id',
        'c.name',
        'c.founded_at',
        'c.logo',
        'c.banner_url',
        'c.about',
        'c.vision',
        'c.location',
        'c.website',
        'c.company_size_id',
        'c.organization_type_id',
        'c.created_at',
        'c.updated_at',
        'c.industry_id',
        'c.user_id',
        'c.country_id',
        'c.state_id',
        'c.city_id',
        'c.pincode',
        'c.latitude',
        'c.longitude',
        'c.featured',
        'c.status',
        'c.is_approved',
        'c.is_rejected',
        'c.is_deleted',
        'c.franchise_id',
        'c.gst_number',
        'c.cin_number',
        'c.is_admin_company',
        'u.email as user_email',
        'u.full_name as user_full_name',
        'u.phone as user_phone',
        'u.role as user_role',
      ])
      .where('c.is_deleted = :isDeleted', { isDeleted: 0 })
      .andWhere('c.is_admin_company = :isAdminCompany', { isAdminCompany: 0 })
      .andWhere('u.status = :userStatus', { userStatus: 'active' })
      .andWhere(
        new Brackets((qb) => {
          qb.where('u.onboarding_status = :onboardingStatus', {
            onboardingStatus: 'completed',
          }).orWhere('u.onboarding_status IS NULL');
        }),
      );

    if (status === 'pending') {
      dataQuery.andWhere('c.is_approved = 0 AND c.is_rejected = 0');
    } else if (status === 'approved') {
      dataQuery.andWhere('c.is_approved = 1');
    } else if (status === 'rejected') {
      dataQuery.andWhere('c.is_rejected = 1');
    }

    if (search) {
      const normalizedSearch = search.trim().toLowerCase();
      const searchWithNoSpaces = normalizedSearch.replace(/\s+/g, '');
      dataQuery.andWhere(
        new Brackets((qb) => {
          qb.where('LOWER(REPLACE(c.name, " ", "")) LIKE :search', {
            search: `%${searchWithNoSpaces}%`,
          })
            .orWhere('LOWER(u.email) LIKE :rawSearch', {
              rawSearch: `%${normalizedSearch}%`,
            })
            .orWhere('u.phone LIKE :rawSearch', {
              rawSearch: `%${normalizedSearch}%`,
            });
        }),
      );
    }

    // Apply relevance scoring and ordering
    if (search) {
      const normalizedSearch = search.trim().toLowerCase();
      const searchWithNoSpaces = normalizedSearch.replace(/\s+/g, '');

      // Add a hidden selection for relevance score
      dataQuery.addSelect(
        `
        CASE 
          WHEN LOWER(REPLACE(c.name, ' ', '')) = :exactSearchName THEN 1
          WHEN LOWER(u.email) = :exactSearchRaw THEN 1
          WHEN u.phone = :exactSearchRaw THEN 1
          ELSE 2
        END
      `,
        'relevance_score',
      );

      dataQuery.setParameters({
        exactSearchName: searchWithNoSpaces,
        exactSearchRaw: normalizedSearch,
      });

      dataQuery.orderBy('relevance_score', 'ASC');
      dataQuery.addOrderBy(
        `
        CASE 
          WHEN c.is_approved = 0 AND c.is_rejected = 0 THEN 1
          WHEN c.is_approved = 1 THEN 2
          WHEN c.is_rejected = 1 THEN 3
          ELSE 4
        END
        `,
        'ASC',
      );
    } else {
      dataQuery.orderBy(
        `
        CASE 
          WHEN c.is_approved = 0 AND c.is_rejected = 0 THEN 1
          WHEN c.is_approved = 1 THEN 2
          WHEN c.is_rejected = 1 THEN 3
          ELSE 4
        END
        `,
        'ASC',
      );
    }
    dataQuery.addOrderBy('c.created_at', 'DESC');

    const data = await dataQuery
      .offset((page - 1) * limit)
      .limit(limit)
      .getRawMany();

    // Get total items for pagination (respecting status filter)
    const filteredCountQuery = this.companyRepo
      .createQueryBuilder('c')
      .leftJoin('users', 'u', 'u.id = c.user_id')
      .where('c.is_deleted = :isDeleted', { isDeleted: 0 })
      .andWhere('c.is_admin_company = :isAdminCompany', { isAdminCompany: 0 })
      .andWhere('u.status = :userStatus', { userStatus: 'active' })
      .andWhere(
        new Brackets((qb) => {
          qb.where('u.onboarding_status = :onboardingStatus', {
            onboardingStatus: 'completed',
          }).orWhere('u.onboarding_status IS NULL');
        }),
      );

    if (status === 'pending') {
      filteredCountQuery.andWhere('c.is_approved = 0 AND c.is_rejected = 0');
    } else if (status === 'approved') {
      filteredCountQuery.andWhere('c.is_approved = 1');
    } else if (status === 'rejected') {
      filteredCountQuery.andWhere('c.is_rejected = 1');
    }

    if (search) {
      const normalizedSearch = search.trim().toLowerCase();
      const searchWithNoSpaces = normalizedSearch.replace(/\s+/g, '');
      filteredCountQuery.andWhere(
        new Brackets((qb) => {
          qb.where('LOWER(REPLACE(c.name, " ", "")) LIKE :search', {
            search: `%${searchWithNoSpaces}%`,
          })
            .orWhere('LOWER(u.email) LIKE :rawSearch', {
              rawSearch: `%${normalizedSearch}%`,
            })
            .orWhere('u.phone LIKE :rawSearch', {
              rawSearch: `%${normalizedSearch}%`,
            });
        }),
      );
    }

    const totalItemsCount = await filteredCountQuery.getCount();

    const stats = await this.companyRepo
      .createQueryBuilder('c')
      .leftJoin('users', 'u', 'u.id = c.user_id')
      .select([
        'COUNT(c.id) AS total_count',
        'SUM(CASE WHEN c.is_approved = 0 AND c.is_rejected = 0 THEN 1 ELSE 0 END) AS pending_count',
        'SUM(CASE WHEN c.is_approved = 1 THEN 1 ELSE 0 END) AS approved_count',
        'SUM(CASE WHEN c.is_rejected = 1 THEN 1 ELSE 0 END) AS rejected_count',
      ])
      .where('c.is_deleted = :isDeleted', { isDeleted: 0 })
      .andWhere('c.is_admin_company = :isAdminCompany', { isAdminCompany: 0 })
      .andWhere('u.status = :userStatus', { userStatus: 'active' })
      .andWhere(
        new Brackets((qb) => {
          qb.where('u.onboarding_status = :onboardingStatus', {
            onboardingStatus: 'completed',
          }).orWhere('u.onboarding_status IS NULL');
        }),
      )
      .getRawOne();

    const totalCount = parseInt(stats.total_count || '0');
    const pendingCount = parseInt(stats.pending_count || '0');
    const approvedCount = parseInt(stats.approved_count || '0');
    const rejectedCount = parseInt(stats.rejected_count || '0');

    // Transform the data to clean field names
    const transformedData = data.map((item) => ({
      id: item.c_id,
      name: item.c_name,
      founded_at: item.c_founded_at,
      logo: item.c_logo,
      banner_url: item.c_banner_url,
      about: item.c_about,
      vision: item.c_vision,
      location: item.c_location,
      website: item.c_website,
      company_size_id: item.c_company_size_id,
      organization_type_id: item.c_organization_type_id,
      industry_id: item.c_industry_id,
      user_id: item.c_user_id,
      country_id: item.c_country_id,
      state_id: item.c_state_id,
      city_id: item.c_city_id,
      pincode: item.c_pincode,
      latitude: item.c_latitude,
      longitude: item.c_longitude,
      featured: item.c_featured,
      status: item.c_status,
      is_approved: item.c_is_approved,
      is_rejected: item.c_is_rejected,
      is_deleted: item.c_is_deleted,
      franchise_id: item.c_franchise_id,
      gst_number: item.c_gst_number,
      cin_number: item.c_cin_number,
      is_admin_company: item.c_is_admin_company,
      created_at: item.c_created_at,
      updated_at: item.c_updated_at,
      user_email: item.user_email,
      user_full_name: item.user_full_name,
      user_phone: item.user_phone,
      user_role: item.user_role,
    }));

    return {
      success: true,
      data: transformedData,
      totalItems: totalItemsCount,
      totalPages: Math.ceil(totalItemsCount / limit),
      currentPage: page,
      counts: {
        totalApplications: totalCount,
        pendingReview: pendingCount,
        approved: approvedCount,
        rejected: rejectedCount,
      },
    };
  }

  // Approve company
  async approveCompany(companyId: number) {
    let company: Companies | null = null;
    let recruiterUser: UserEntity | null = null;
    let responsePayload: {
      success: boolean;
      message: string;
      data: { companyId: number; status: string };
    } | null = null;

    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      company = await queryRunner.manager
        .createQueryBuilder(Companies, 'c')
        .where('c.id = :id', { id: companyId })
        .getOne();

      if (!company) {
        throw new Error('Company not found');
      }

      if (company.user_id) {
        recruiterUser = await queryRunner.manager
          .createQueryBuilder(UserEntity, 'u')
          .where('u.id = :id', { id: company.user_id })
          .getOne();
      }

      // Update company status to active
      await queryRunner.manager
        .createQueryBuilder()
        .update(Companies)
        .set({
          status: 1,
          is_approved: 1,
          is_rejected: 0,
        })
        .where('id = :id', { id: companyId })
        .execute();

      // Update user status to active
      if (company.user_id) {
        await queryRunner.manager
          .createQueryBuilder()
          .update(UserEntity)
          .set({ status: 'active' })
          .where('id = :userId', { userId: company.user_id })
          .execute();
      }

      await queryRunner.commitTransaction();

      responsePayload = {
        success: true,
        message: 'Company approved successfully',
        data: { companyId, status: 'approved' },
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw new Error('Failed to approve company: ' + error.message);
    } finally {
      await queryRunner.release();
    }

    if (recruiterUser?.email) {
      try {
        const emailSent = await this.sendRecruiterApprovalEmail({
          email: recruiterUser.email,
          fullName: recruiterUser.fullName,
          companyName: company?.name,
        });
        if (!emailSent) {
          console.error(
            'Recruiter approval email did not report success for user:',
            recruiterUser.email,
          );
        }
      } catch (emailError) {
        console.error('Error sending recruiter approval email:', emailError);
      }
    }

    return (
      responsePayload ?? {
        success: true,
        message: 'Company approved successfully',
        data: { companyId, status: 'approved' },
      }
    );
  }

  private async sendRecruiterApprovalEmail({
    email,
    fullName,
    companyName,
  }: {
    email: string;
    fullName?: string | null;
    companyName?: string;
  }): Promise<boolean> {
    try {
      const preferredName =
        (fullName && fullName.trim().length > 0 && fullName.trim()) ||
        email.split('@')[0];

      const templateName = 'recruiter_approval';
      const context = {
        preferred_name: preferredName,
        company_name: companyName || '',
        company_subtitle: companyName ? `for <strong>${companyName}</strong>` : '',
      };

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

      return success;
    } catch (error) {
      console.error('Failed to send recruiter approval email:', error);
      return false;
    }
  }

  // Reject company
  async rejectCompany(companyId: number) {
    let company: Companies | null = null;
    let recruiterUser: UserEntity | null = null;
    let responsePayload: {
      success: boolean;
      message: string;
      data: { companyId: number; status: string };
    } | null = null;

    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      company = await queryRunner.manager
        .createQueryBuilder(Companies, 'c')
        .where('c.id = :id', { id: companyId })
        .getOne();

      if (!company) {
        throw new Error('Company not found');
      }

      if (company.user_id) {
        recruiterUser = await queryRunner.manager
          .createQueryBuilder(UserEntity, 'u')
          .where('u.id = :id', { id: company.user_id })
          .getOne();
      }

      // Mark company as rejected and set status to inactive
      await queryRunner.manager
        .createQueryBuilder()
        .update(Companies)
        .set({
          status: 0, // Set company status to inactive
          is_approved: 0,
          is_rejected: 1,
        })
        .where('id = :id', { id: companyId })
        .execute();

      // Keep user active so they can see their company profile and rejection message
      // User status remains 'active' to allow login and view rejection details

      await queryRunner.commitTransaction();

      responsePayload = {
        success: true,
        message: 'Company rejected successfully',
        data: { companyId, status: 'rejected' },
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw new Error('Failed to reject company: ' + error.message);
    } finally {
      await queryRunner.release();
    }

    if (recruiterUser?.email) {
      try {
        const emailSent = await this.sendRecruiterRejectionEmail({
          email: recruiterUser.email,
          fullName: recruiterUser.fullName,
          companyName: company?.name,
        });
        if (!emailSent) {
          console.error(
            'Recruiter rejection email did not report success for user:',
            recruiterUser.email,
          );
        }
      } catch (emailError) {
        console.error('Error sending recruiter rejection email:', emailError);
      }
    }

    return (
      responsePayload ?? {
        success: true,
        message: 'Company rejected successfully',
        data: { companyId, status: 'rejected' },
      }
    );
  }

  private async sendRecruiterRejectionEmail({
    email,
    fullName,
    companyName,
  }: {
    email: string;
    fullName?: string | null;
    companyName?: string;
  }): Promise<boolean> {
    try {
      const preferredName =
        (fullName && fullName.trim().length > 0 && fullName.trim()) ||
        email.split('@')[0];

      const templateName = 'recruiter_rejection';
      const context = {
        preferred_name: preferredName,
        company_name: companyName || '',
        company_context: companyName ? ` for <strong>${companyName}</strong>` : '',
      };

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

      return success;
    } catch (error) {
      console.error('Failed to send recruiter rejection email:', error);
      return false;
    }
  }
}
