import { Injectable, BadRequestException } from '@nestjs/common';
import { InjectDataSource, InjectRepository } from '@nestjs/typeorm';
import { DataSource, Repository, Brackets } from 'typeorm';
import { UserEntity } from './entities/user.entity';
import { OtpRequests } from '../otp_requests/entities/otp_requests.entity';
import { RoleAccessEntity } from '../role_access/entities/role_access.entity';
import * as sharp from 'sharp';
import * as path from 'path';
import * as fs from 'fs';
import { SeekerProfiles } from '../seeker_profiles/entities/seeker_profiles.entity';
import { Companies } from '../companies/entities/companies.entity';
import { SeekerAddress } from '../seeker_address/entities/seeker_address.entity';
import { SeekerEducation } from '../seeker_education/entities/seeker_education.entity';
import { SeekerExperience } from '../seeker_experience/entities/seeker_experience.entity';
import { SeekerRoleExperience } from '../seeker_role_experience/entities/seeker_role_experience.entity';
import { SeekerCertification } from '../seeker_certifications/entities/seeker_certification.entity';
import { SeekerSkill } from '../seeker_skills/entities/seeker_skills.entity';
import { SeekerLanguage } from '../seeker_language/entities/seeker_language.entity';
import { SeekerResume } from '../seeker_resumes/entities/seeker_resumes.entity';
import { WalletService } from '../wallet/wallet.service';
import { SeekerAddressService } from '../seeker_address/seeker_address.service';
import { EmailService } from '../email/email.service';
//import { v4 as uuidv4 } from 'uuid';

@Injectable()
export class UserService {
  constructor(
    @InjectRepository(UserEntity)
    private readonly userRepo: Repository<UserEntity>,
    @InjectRepository(OtpRequests)
    private readonly otpRepo: Repository<OtpRequests>,
    @InjectDataSource()
    private dataSource: DataSource,
    private readonly walletService: WalletService,
    private readonly seekerAddressService: SeekerAddressService,
    private readonly emailService: EmailService,
  ) { }

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

  async findOne(id: number): Promise<UserEntity> {
    if (!id || isNaN(id)) return null;
    return this.userRepo.findOne({ where: { id } });
  }

  async create(data: Partial<UserEntity>): Promise<UserEntity> {
    const user = this.userRepo.create(data);
    return this.userRepo.save(user);
  }

  async update(id: number, data: Partial<UserEntity>): Promise<UserEntity> {
    if (!id || isNaN(id)) throw new Error('Invalid User ID for update');
    await this.userRepo.update(id, data);
    return this.findOne(id);
  }

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

  // Enhanced login with email or phone + password
  async loginWithPassword(
    emailOrPhone: string,
    password: string,
  ): Promise<UserEntity | null> {
    // Validate inputs to prevent NaN errors
    if (!emailOrPhone || !password) {
      throw new Error('Email/Phone and password are required');
    }

    // Check for NaN values
    if (emailOrPhone === 'NaN' || password === 'NaN') {
      throw new Error('Invalid input: NaN values detected');
    }

    // Check if inputs are strings
    if (typeof emailOrPhone !== 'string' || typeof password !== 'string') {
      throw new Error('Email/Phone and password must be strings');
    }

    // Trim whitespace
    const trimmedEmailOrPhone = emailOrPhone.trim();
    const trimmedPassword = password.trim();

    if (!trimmedEmailOrPhone || !trimmedPassword) {
      throw new Error('Email/Phone and password cannot be empty');
    }

    // console.log('Login attempt:', { emailOrPhone: trimmedEmailOrPhone, password: trimmedPassword });

    const user = await this.userRepo
      .createQueryBuilder('user')
      .addSelect('user.password')
      .where('(user.email = :emailOrPhone OR user.phone = :emailOrPhone)', {
        emailOrPhone: trimmedEmailOrPhone,
      })
      .andWhere('user.password = :password', { password: trimmedPassword })
      .andWhere('user.status = :status', { status: 'active' })
      .andWhere('user.authMethod = :authMethod', { authMethod: 'password' })
      .getOne();

    // console.log('Login result:', {
    //   user: user ? { id: user.id, email: user.email, role: user.role } : null,
    // });

    if (user) {
      delete user.password;
    }

    return user;
  }

  // Login with email or phone + OTP
  async loginWithOTP(
    emailOrPhone: string,
    otp: string,
    role?: string, // Valid roles: 'seeker', 'recruiter', 'franchise', etc.
  ): Promise<UserEntity | null> {
    try {
      // console.log(`Attempting OTP login for: ${emailOrPhone} with OTP: ${otp} and role: ${role}`);

      // First find the user
      const query = this.userRepo
        .createQueryBuilder('user')
        .where('(user.email = :emailOrPhone OR user.phone = :emailOrPhone)', {
          emailOrPhone,
        })
        .andWhere('user.status = :status', { status: 'active' });

      // If role is provided, filter by role
      if (role) {
        query.andWhere('user.role = :role', { role });
      }

      const user = await query.getOne();

      if (!user) {
        // console.log(`User not found or inactive: ${emailOrPhone}`);
        return null;
      }

      // console.log(`User found: ${user.id}, checking OTP...`);

      // Use the OtpRequests entity for better type safety
      const validOTP = await this.otpRepo
        .createQueryBuilder('otp')
        .where('otp.user_id = :userId', { userId: user.id })
        .andWhere('otp.otp = :otp', { otp })
        .andWhere('otp.otp_expires_at > NOW()')
        .getOne();

      if (!validOTP) {
        // console.log(`Invalid or expired OTP for user: ${user.id}`);
        // console.log(`Received OTP: "${otp}" (type: ${typeof otp})`);

        // Let's check what OTPs exist for this user for debugging
        const allOTPs = await this.otpRepo
          .createQueryBuilder('otp')
          .where('otp.user_id = :userId', { userId: user.id })
          .getMany();

        // console.log(`Available OTPs for user ${user.id}:`, allOTPs);

        // Check if there's a match with different data types
        const matchingOTP = allOTPs.find(
          (dbOtp) =>
            dbOtp.otp === otp ||
            dbOtp.otp === otp.toString() ||
            dbOtp.otp.toString() === otp,
        );

        if (matchingOTP) {
          // console.log(`Found matching OTP with different type:`, matchingOTP);
        }

        // Also check for expired OTPs
        const expiredOTPs = await this.otpRepo
          .createQueryBuilder('otp')
          .where('otp.user_id = :userId', { userId: user.id })
          .andWhere('otp.otp = :otp', { otp })
          .andWhere('otp.otp_expires_at <= NOW()')
          .getMany();

        if (expiredOTPs.length > 0) {
          // console.log(`Found expired OTPs:`, expiredOTPs);
        }

        return null;
      }

      // console.log(`Valid OTP found:`, validOTP);
      // console.log(`Deleting used OTP...`);

      // Delete the used OTP
      await this.otpRepo.delete(validOTP.id);

      // console.log(`OTP login successful for user: ${user.id}`);
      return user;
    } catch (error) {
      console.error('Error in loginWithOTP:', error);
      return null;
    }
  }

  // Generate and send OTP
  /**
   * Send OTP via email only based on phone number and role
   * Used as fallback when SMS is slow or fails
   */
  async sendEmailOTPByPhone(
    phone: string,
    role: string,
  ): Promise<{ success: boolean; message: string }> {
    try {
      // Find user by phone number AND role to ensure correct account
      const user = await this.userRepo
        .createQueryBuilder('user')
        .where('user.phone = :phone', { phone })
        .andWhere('user.role = :role', { role })
        .andWhere('user.status = :status', { status: 'active' })
        .getOne();

      if (!user) {
        return { success: false, message: 'User not found or inactive' };
      }

      if (!user.email) {
        return {
          success: false,
          message: 'No email address associated with this account',
        };
      }

      // Check if active OTP exists - use existing OTP if available
      const existingOTP = await this.otpRepo
        .createQueryBuilder('otp')
        .where('otp.user_id = :userId', { userId: user.id })
        .andWhere('otp.otp_expires_at > NOW()')
        .getOne();

      let otpToSend: string;

      if (existingOTP && existingOTP.otp) {
        // Use the existing OTP that was sent via SMS
        otpToSend = existingOTP.otp;
        // console.log(`Using existing OTP for email: ${otpToSend}`);
      } else {
        // No active OTP found - generate a new one (shouldn't happen in normal flow)
        otpToSend = Math.floor(1000 + Math.random() * 9000).toString();
        const otpExpiresAt = new Date(Date.now() + 15 * 60 * 1000);

        try {
          await this.otpRepo.insert({
            user_id: user.id,
            otp: otpToSend,
            otp_expires_at: otpExpiresAt,
            created_at: new Date(),
            updated_at: new Date(),
          });
        } catch (dbError) {
          console.error('Database error while saving OTP:', dbError);
          return { success: false, message: 'Failed to save OTP' };
        }
      }

      // Send the SAME OTP via email (same as SMS OTP)
      const emailSent = await this.sendOTPEmail(user.email, otpToSend);

      if (emailSent) {
        return {
          success: true,
          message: `OTP sent successfully to your registered email (${this.maskEmail(user.email)})`,
        };
      } else {
        return {
          success: false,
          message: 'Failed to send OTP via email',
        };
      }
    } catch (error) {
      console.error('Error sending email OTP:', error);
      return { success: false, message: 'Error sending OTP via email' };
    }
  }

  /**
   * Helper function to mask email address for privacy
   */
  private maskEmail(email: string): string {
    const [localPart, domain] = email.split('@');
    if (localPart.length <= 2) {
      return `${localPart[0]}***@${domain}`;
    }
    const visibleChars = Math.min(3, Math.floor(localPart.length / 3));
    const masked = localPart.substring(0, visibleChars) + '***';
    return `${masked}@${domain}`;
  }

  async generateAndSendOTP(
    emailOrPhone: string,
  ): Promise<{ success: boolean; message: string }> {
    try {
      // console.log(`Generating OTP for: ${emailOrPhone}`);

      // Find user by email or phone
      const user = await this.userRepo
        .createQueryBuilder('user')
        .where('(user.email = :emailOrPhone OR user.phone = :emailOrPhone)', {
          emailOrPhone,
        })
        .andWhere('user.status = :status', { status: 'active' })
        .getOne();

      if (!user) {
        // console.log(`User not found or inactive: ${emailOrPhone}`);
        return { success: false, message: 'User not found or inactive' };
      }

      // console.log(`User found: ${user.id}, generating OTP...`);

      // Generate OTP
      // const otp = Math.floor(100000 + Math.random() * 900000).toString(); // 6-digit OTP (COMMENTED OUT)
      const otp = Math.floor(1000 + Math.random() * 9000).toString(); // 4-digit OTP
      // OLD: const otpExpiresAt = new Date(Date.now() + 5 * 60 * 1000); // 5 minutes from now
      // NEW: Changed to 10 minutes
      const otpExpiresAt = new Date(Date.now() + 10 * 60 * 1000); // 10 minutes from now

      // console.log(`Generated OTP: ${otp}, expires at: ${otpExpiresAt}`);

      // Check if active OTP exists
      const existingOTP = await this.otpRepo
        .createQueryBuilder('otp')
        .where('otp.user_id = :userId', { userId: user.id })
        .andWhere('otp.otp_expires_at > NOW()')
        .getOne();

      // console.log(`Existing OTP found:`, existingOTP);

      try {
        if (existingOTP) {
          // console.log(`Updating existing OTP record: ${existingOTP.id}`);
          // Update existing OTP using entity
          await this.otpRepo.update(existingOTP.id, {
            otp: otp,
            otp_expires_at: otpExpiresAt,
            updated_at: new Date(),
          });
        } else {
          // console.log(`Inserting new OTP record for user: ${user.id}`);
          // Insert new OTP using entity
          const newOtp = this.otpRepo.create({
            user_id: user.id,
            otp: otp,
            otp_expires_at: otpExpiresAt,
            updated_at: new Date(),
          });
          await this.otpRepo.save(newOtp);
        }

        // Verify OTP was saved using entity
        const savedOTP = await this.otpRepo
          .createQueryBuilder('otp')
          .where('otp.user_id = :userId', { userId: user.id })
          .andWhere('otp.otp = :otp', { otp })
          .getOne();

        // console.log(`Verification - Saved OTP:`, savedOTP);

        if (!savedOTP) {
          console.error('OTP was not saved to database!');
          return { success: false, message: 'Failed to save OTP to database' };
        }
      } catch (dbError) {
        console.error('Database error while saving OTP:', dbError);
        return { success: false, message: 'Database error while saving OTP' };
      }

      // Send OTP via email and SMS based on user's contact information
      const emailSent = await this.sendOTPEmail(user.email, otp);

      // Only send SMS if user has a phone number
      let smsSent = false;
      if (user.phone) {
        smsSent = await this.sendOTPSMS(user.phone, otp);
      }

      // Determine response message based on what was actually sent
      if (emailSent && smsSent) {
        return {
          success: true,
          message: 'OTP sent successfully to email and phone',
        };
      } else if (emailSent && !user.phone) {
        return {
          success: true,
          message:
            'OTP sent successfully to email (no phone number registered)',
        };
      } else if (emailSent) {
        return {
          success: true,
          message: 'OTP sent successfully to email (SMS delivery failed)',
        };
      } else if (smsSent) {
        return {
          success: true,
          message: 'OTP sent successfully to phone (email delivery failed)',
        };
      } else {
        return {
          success: false,
          message: 'Failed to send OTP to any registered contact method',
        };
      }
    } catch (error) {
      console.error('Error generating OTP:', error);
      return { success: false, message: 'Error generating OTP' };
    }
  }

  // Test method to verify OTP entity is working
  async testOtpEntity(): Promise<any> {
    try {
      // console.log('Testing OTP entity...');

      // Test basic query
      const count = await this.otpRepo.count();
      // console.log(`Total OTP records: ${count}`);

      // Test finding recent OTPs
      const recentOtps = await this.otpRepo
        .createQueryBuilder('otp')
        .orderBy('otp.created_at', 'DESC')
        .limit(5)
        .getMany();

      // console.log(
      //   `Recent OTPs:`,
      //   recentOtps.map((otp) => ({
      //     id: otp.id,
      //     userId: otp.user_id,
      //     otp: otp.otp,
      //     createdAt: otp.created_at,
      //     expiresAt: otp.otp_expires_at,
      //   })),
      // );

      return {
        success: true,
        message: 'OTP entity test completed',
        count,
        recentOtps: recentOtps.length,
      };
    } catch (error) {
      console.error('OTP entity test failed:', error);
      return {
        success: false,
        message: error.message,
      };
    }
  }

  // Forgot password - send OTP
  async forgotPasswordSendOTP(
    emailOrPhone: string,
  ): Promise<{ success: boolean; message: string }> {
    try {
      // console.log(
      //   `Processing forgot password OTP request for: ${emailOrPhone}`,
      // );

      // Find user by email or phone
      const user = await this.userRepo
        .createQueryBuilder('user')
        .where('(user.email = :emailOrPhone OR user.phone = :emailOrPhone)', {
          emailOrPhone,
        })
        .andWhere('user.status = :status', { status: 'active' })
        .getOne();

      if (!user) {
        // console.log(`User not found: ${emailOrPhone}`);
        return {
          success: false,
          message:
            'If an account with this email/phone exists, an OTP has been sent.',
        };
      }

      // Generate and send OTP using existing method
      const result = await this.generateAndSendOTP(emailOrPhone);

      if (result.success) {
        // console.log(`Forgot password OTP sent to: ${emailOrPhone}`);
        return {
          success: true,
          message:
            'OTP has been sent to your email and phone. Please check and enter the code.',
        };
      } else {
        // console.log(`Failed to send forgot password OTP to: ${emailOrPhone}`);
        return {
          success: false,
          message: result.message || 'Failed to send OTP. Please try again.',
        };
      }
    } catch (error) {
      console.error('Forgot password OTP error:', error);
      return {
        success: false,
        message:
          'An error occurred while processing your request. Please try again.',
      };
    }
  }

  // Verify OTP only (for forgot password flow)
  async verifyOTP(
    emailOrPhone: string,
    otp: string,
  ): Promise<{ success: boolean; message: string }> {
    try {
      //console.log(`Verifying OTP for: ${emailOrPhone}`);

      // Find user by email or phone
      const user = await this.userRepo
        .createQueryBuilder('user')
        .where('(user.email = :emailOrPhone OR user.phone = :emailOrPhone)', {
          emailOrPhone,
        })
        .andWhere('user.status = :status', { status: 'active' })
        .getOne();

      if (!user) {
        // console.log(`User not found: ${emailOrPhone}`);
        return {
          success: false,
          message:
            'Invalid email/phone or OTP. Please check your credentials and try again.',
        };
      }

      // Verify OTP
      const validOTP = await this.otpRepo
        .createQueryBuilder('otp')
        .where('otp.user_id = :userId', { userId: user.id })
        .andWhere('otp.otp = :otp', { otp })
        .andWhere('otp.otp_expires_at > :now', { now: new Date() })
        .getOne();

      if (!validOTP) {
        // console.log(`Invalid or expired OTP for user ${user.id}`);
        return {
          success: false,
          message: 'Invalid or expired OTP. Please request a new one.',
        };
      }

      //console.log(`OTP verified successfully for user ${user.id}`);

      return {
        success: true,
        message: 'OTP verified successfully.',
      };
    } catch (error) {
      console.error('OTP verification error:', error);
      return {
        success: false,
        message: 'An error occurred while verifying OTP. Please try again.',
      };
    }
  }

  // Forgot password - verify OTP and reset password
  async forgotPasswordReset(
    emailOrPhone: string,
    otp: string,
    newPassword: string,
  ): Promise<{ success: boolean; message: string }> {
    try {
      // console.log(`Processing forgot password reset for: ${emailOrPhone}`);

      // Find user by email or phone
      const user = await this.userRepo
        .createQueryBuilder('user')
        .where('(user.email = :emailOrPhone OR user.phone = :emailOrPhone)', {
          emailOrPhone,
        })
        .andWhere('user.status = :status', { status: 'active' })
        .getOne();

      if (!user) {
        // console.log(`User not found: ${emailOrPhone}`);
        return {
          success: false,
          message:
            'Invalid email/phone or OTP. Please check your credentials and try again.',
        };
      }

      // Verify OTP using existing method
      const validOTP = await this.otpRepo
        .createQueryBuilder('otp')
        .where('otp.user_id = :userId', { userId: user.id })
        .andWhere('otp.otp = :otp', { otp })
        .andWhere('otp.otp_expires_at > :now', { now: new Date() })
        .getOne();

      if (!validOTP) {
        // console.log(`Invalid or expired OTP for user ${user.id}`);
        return {
          success: false,
          message: 'Invalid or expired OTP. Please request a new one.',
        };
      }

      // Update password
      // console.log(`Updating password for user ${user.id} to: ${newPassword}`);
      try {
        const updateResult = await this.userRepo.update(user.id, {
          password: newPassword,
        });
        // console.log(`Password update result:`, updateResult);

        if (updateResult.affected === 0) {
          console.error(
            `No rows were affected when updating password for user ${user.id}`,
          );
          return {
            success: false,
            message: 'Failed to update password. Please try again.',
          };
        }
      } catch (updateError) {
        console.error('Error updating password:', updateError);
        return {
          success: false,
          message: 'Database error while updating password. Please try again.',
        };
      }

      // Delete the used OTP
      await this.otpRepo.delete(validOTP.id);

      // Verify the password was updated
      // const updatedUser = await this.userRepo
      //   .createQueryBuilder('user')
      //   .addSelect('user.password')
      //   .where('user.id = :userId', { userId: user.id })
      //   .getOne();

      // console.log(
      //   `Password verification - User ID: ${updatedUser.id}, Password: ${updatedUser.password}`,
      // );

      // console.log(`Password reset successful for user ${user.id}`);

      return {
        success: true,
        message:
          'Password has been reset successfully. You can now login with your new password.',
      };
    } catch (error) {
      console.error('Forgot password reset error:', error);
      return {
        success: false,
        message:
          'An error occurred while resetting your password. Please try again.',
      };
    }
  }

  // Send OTP via email using EmailService
  private async sendOTPEmail(email: string, otp: string): Promise<boolean> {
    try {
      const brevoApiKey = process.env.BREVO_API_KEY;

      if (!brevoApiKey) {
        console.error('Brevo API key not configured');
        // console.log(`[DEV] Email OTP for ${email}: ${otp}`);
        return true; // Return true for development to allow testing
      }

      const templateName = 'otp_login';
      const toName = email.split('@')[0];
      const context = {
        otp: otp
      };

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

      if (success) {
        // console.log(`Email OTP sent successfully to ${email}`);
        return true;
      } else {
        console.error('Error sending email OTP via EmailService');
        return false;
      }
    } catch (error) {
      console.error('Error sending email OTP:', error);
      return false;
    }
  }

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

      const isRecruiter = role === 'recruiter';
      const normalizedIsApproved = isRecruiter ? Boolean(isApproved) : true;

      let templateName = 'registration_welcome_seeker';
      if (isRecruiter) {
        templateName = normalizedIsApproved
          ? 'registration_welcome_recruiter_approved'
          : 'registration_welcome_recruiter_pending';
      }

      const context = {
        preferredName,
        companyName: companyName || '',
        username: username || '—',
        email,
      };

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

      // Log dispatch to database
      await this.dataSource.query(`
        INSERT INTO email_sending_logs (recipient_email, recipient_name, recipient_type, email_type, status, reason)
        VALUES (?, ?, ?, ?, ?, ?)
      `, [
        email,
        preferredName,
        isRecruiter ? 'recruiter' : 'seeker',
        templateName,
        success ? 'sent' : 'failed',
        success ? 'Sent registration email successfully' : 'Failed or template not active'
      ]).catch(err => console.error('Failed to log registration email to database:', err));

      return success;
    } catch (error) {
      console.error('Error sending registration email:', error);
      return false;
    }
  }

  // Send OTP via SMS using Textlocal API
  private async sendOTPSMS(phone: string, otp: string): Promise<boolean> {
    // try {
    //   // Authorisation details
    //   const apiKey = process.env.TEXTLOCAL_API_KEY;
    //   const sender = process.env.TEXTLOCAL_SENDER; // Sender name for the SMS
    //   const test = '0'; // Set to 1 for testing, 0 for actual messages
    //   const apiUrl = 'https://api.textlocal.in/send/'; // TextLocal API endpoint
    //   // Process the message text
    //   const baseMessage = `Dear Customer, Your One Time Password(OTP) for creating ticket in TechSquadTeam is ${otp} .It is valid for 10 mins. PLEASE DO NOT SHARE THE OTP WITH ANYONE.`;
    //   const text = baseMessage.trim();
    //   // Add extension text
    //   const ext = null; // You can modify this if needed
    //   let finalText = text;
    //   if (!ext) {
    //     finalText = text + ' TST24X7 ONCALL SERVICES PRIVATE LIMITED';
    //   } else {
    //     finalText = text + 'TST24X7';
    //   }
    //   const message = encodeURIComponent(finalText);
    //   // Prepare data to be sent in the POST request (matching your PHP)
    //   const data = {
    //     apikey: apiKey,
    //     numbers: phone,
    //     sender: sender,
    //     message: message,
    //     test: test,
    //   };
    //   // Perform the POST request
    //   const response = await fetch(apiUrl, {
    //     method: 'POST',
    //     headers: {
    //       'Content-Type': 'application/x-www-form-urlencoded',
    //     },
    //     body: new URLSearchParams(data).toString(),
    //   });
    //   // Get the result from the API response
    //   const result = await response.text();
    //   // Optional: Log the result for debugging purposes (matching your PHP)
    //   // console.log('SMS API Response: ' + result);
    //   // Parse the result to check if it was successful
    //   try {
    //     const parsedResult = JSON.parse(result);
    //     if (parsedResult.status === 'success') {
    //       // console.log(`SMS OTP sent successfully to ${phone}`);
    //       return true;
    //     } else {
    //       // console.error('TextLocal API Error:', parsedResult);
    //       return false;
    //     }
    //   } catch (parseError) {
    //     // console.error('Error parsing SMS API response:', parseError);
    //     // console.log('Raw response:', result);
    //     return false;
    //   }
    // } catch (error) {
    //   console.error('Error sending SMS OTP:', error);
    //   return false;
    // }
    // Using msg2all API
    try {
      const userId = process.env.IKONTEL_USERID;
      const password = process.env.IKONTEL_PASSWORD;
      const version = process.env.IKONTEL_VERSION;
      const senderId = process.env.IKONTEL_SENDERID;
      const apiUrl = 'https://ikontel.msg2all.com/TRANSAPI/sendsms.jsp';

      if (!userId || !password || !senderId) {
        console.error(
          'Missing Ikontel SMS credentials in environment variables.',
        );
        return false;
      }

      // Process the message text
      const finalText = `${otp} is your OTP for Jobvumi portal. Valid for 10 mins. Do not share OTP with anyone. Jobvumi representatives will never ask for it.`;

      // Prepare data to be sent in the POST request
      const data = {
        login: userId,
        passwd: password,
        version: version,
        msisdn: phone,
        msg_type: 'text',
        msg: finalText,
        sender_id: senderId,
      };

      // Perform the POST request
      const response = await fetch(apiUrl, {
        method: 'POST',
        headers: {
          'Content-Type': 'application/x-www-form-urlencoded',
        },
        body: new URLSearchParams(data).toString(),
      });

      // Get the result from the API response
      const result = await response.text();

      //   console.log("IKONTEL SMS API Response: " + result)

      try {
        const parsedResult = JSON.parse(result);
        if (
          parsedResult.result &&
          parsedResult.result.status &&
          parsedResult.result.status.statusCode === '0'
        ) {
          return true;
        } else {
          console.error('IKONTEL API Error:', parsedResult);
          return false;
        }
      } catch (parseError) {
        if (
          result.includes('success') ||
          result.includes('OK') ||
          result.includes('Sent') ||
          result.includes('"statusCode":"0"')
        ) {
          //   console.log(`SMS OTP sent successfully to ${phone}`)
          return true;
        }
        console.error('Error parsing IKONTEL SMS API response:', parseError);
        // console.log("Raw response:", result)
        return false;
      }
    } catch (error) {
      console.error('Error sending SMS OTP:', error);
      return false;
    }
  }

  async findByEmailWithPassword(
    email: string,
    password: string,
  ): Promise<UserEntity | null> {
    // console.log('Finding user by email:', email);
    // console.log('Using password:', password);
    const user = await this.userRepo
      .createQueryBuilder('user')
      .addSelect('user.password') // explicitly include password
      .where('user.email = :email', { email })
      .andWhere('user.password = :password', { password })
      .andWhere('user.status = :status', { status: 'active' })
      .getOne();

    if (user) {
      delete user.password; // remove password before returning
    }

    return user;
  }
  async registerUser(data: any): Promise<any> {
    // console.log('Register payload:', data);
    console.log(
      '👤 [REGISTER] registerUser called with role:',
      data.role,
      'email:',
      data.email,
    );

    const {
      full_name,
      username,
      email,
      phone,
      password,
      role,
      companyName,
      gst_number,
      cin_number,
      ...jobSeekerFields
    } = data;

    // Validate required fields
    if (!full_name || !username || !email || !password || !role) {
      throw new Error(
        'Missing required fields: full_name, username, email, password, role',
      );
    }

    // Check if user already exists with the same email or username
    const existingUser = await this.dataSource
      .createQueryBuilder()
      .select(['id', 'email', 'username'])
      .from('users', 'u')
      .where('u.email = :email', { email })
      .orWhere('u.username = :username', { username })
      .getOne();

    if (existingUser) {
      if (existingUser.email === email) {
        throw new Error('User with this email already exists');
      }
      if (existingUser.username === username) {
        throw new Error('Username already taken');
      }
    }

    // Clean and validate jobSeekerFields to prevent NaN values
    const cleanJobSeekerFields = {};
    if (role === 'seeker' && jobSeekerFields) {
      Object.keys(jobSeekerFields).forEach((key) => {
        const value = jobSeekerFields[key];
        // Skip null, undefined, NaN, or empty string values
        if (
          value !== null &&
          value !== undefined &&
          value !== '' &&
          !isNaN(value)
        ) {
          cleanJobSeekerFields[key] = value;
        }
      });
    }

    // 1. Insert user
    const userResult = await this.dataSource
      .createQueryBuilder()
      .insert()
      .into('users')
      .values({
        fullName: full_name,
        username,
        email,
        phone: phone || null,
        password,
        role,
        status: 'active',
        // Remove explicit timestamp assignments - let database handle them
      })
      .execute();

    const userId = userResult.identifiers[0].id;
    console.log('👤 [REGISTER] User created with ID:', userId, 'role:', role);
    // console.log('Inserted userId:', userId);

    // 2. Insert into companies or job_seeker based on role
    if (role === 'recruiter' && companyName) {
      // Check if company name already exists
      const existingCompany = await this.dataSource
        .createQueryBuilder()
        .select(['id', 'name'])
        .from('companies', 'c')
        .where('c.name = :name', { name: companyName })
        .andWhere('c.is_deleted = :isDeleted', { isDeleted: 0 })
        .getOne();

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

      // Validate that at least one of CIN or GST is provided for direct registration
      if (!gst_number && !cin_number) {
        throw new Error(
          'Either CIN number or GST number is required for recruiter registration',
        );
      }

      await this.dataSource
        .createQueryBuilder()
        .insert()
        .into('companies')
        .values({
          name: companyName,
          user_id: userId,
          gst_number: gst_number || null,
          cin_number: cin_number || null,
          created_at: new Date(),
          updated_at: new Date(),
        })
        .execute();
    } else if (role === 'seeker') {
      // Only insert if there are valid fields
      if (Object.keys(cleanJobSeekerFields).length > 0) {
        await this.dataSource
          .createQueryBuilder()
          .insert()
          .into('seeker_profiles')
          .values({
            user_id: userId,
            ...cleanJobSeekerFields,
            // Remove explicit timestamp assignments - let database handle them
          })
          .execute();
      } else {
        // Insert minimal seeker profile with just user_id
        await this.dataSource
          .createQueryBuilder()
          .insert()
          .into('seeker_profiles')
          .values({
            user_id: userId,
            // Remove explicit timestamp assignments - let database handle them
          })
          .execute();
      }
    }

    // 3. New Recruiter Onboarding Credits (Free Tier)
    if (role === 'recruiter') {
      console.log(
        '🔍 [ONBOARDING] Starting onboarding credits for recruiter:',
        userId,
      );
      try {
        // Fetch configs for free credits
        const [freeJobPostsConfig] = await this.dataSource.query(
          'SELECT setting_value FROM config_setting WHERE setting_key = "free_job_posts"',
        );
        console.log(
          '🔍 [ONBOARDING] free_job_posts config:',
          freeJobPostsConfig,
        );

        const [freeCVViewsConfig] = await this.dataSource.query(
          'SELECT setting_value FROM config_setting WHERE setting_key = "cv_limit_free"',
        );
        console.log('🔍 [ONBOARDING] cv_limit_free config:', freeCVViewsConfig);

        const [validityDaysConfig] = await this.dataSource.query(
          'SELECT setting_value FROM config_setting WHERE setting_key = "free_credit_validity_days"',
        );

        const freeJobPosts = parseInt(freeJobPostsConfig?.setting_value || '1');
        const freeCVViews = parseInt(freeCVViewsConfig?.setting_value || '5');
        const validityDays = parseInt(
          validityDaysConfig?.setting_value || '30',
        );

        console.log(
          '🔍 [ONBOARDING] Parsed values - freeJobPosts:',
          freeJobPosts,
          'freeCVViews:',
          freeCVViews,
          'validityDays:',
          validityDays,
        );

        // Calculate expiry for registration bonus
        const expiresAt = new Date();
        expiresAt.setDate(expiresAt.getDate() + validityDays);

        console.log('🔍 [ONBOARDING] Expiry date calculated:', expiresAt);

        // Credit Job Posts (30 Days Expiry)
        if (freeJobPosts > 0) {
          console.log('🔍 [ONBOARDING] Crediting job posts...', freeJobPosts);
          await this.walletService.creditWallet(
            userId,
            'job',
            freeJobPosts,
            'Onboarding Bonus: Free Job Post',
            'onboarding',
            userId,
            null,
            expiresAt,
          );
          console.log('✅ [ONBOARDING] Job posts credited successfully');
        } else {
          console.log('⚠️ [ONBOARDING] freeJobPosts is 0, skipping job credit');
        }

        // Credit CV Views (30 Days Expiry)
        if (freeCVViews > 0) {
          console.log('🔍 [ONBOARDING] Crediting CV views...', freeCVViews);
          await this.walletService.creditWallet(
            userId,
            'database',
            freeCVViews,
            'Onboarding Bonus: Free Database Credits',
            'onboarding',
            userId,
            null,
            expiresAt,
          );
          console.log('✅ [ONBOARDING] Database credits credited successfully');
        } else {
          console.log(
            '⚠️ [ONBOARDING] freeCVViews is 0, skipping database credit',
          );
        }

        console.log(
          '✅ [ONBOARDING] All onboarding credits added successfully',
        );
      } catch (creditError) {
        console.error(
          '❌ [ONBOARDING] Failed to add onboarding credits:',
          creditError,
        );
        console.error(
          '❌ [ONBOARDING] Error details:',
          creditError.message,
          creditError.stack,
        );
        // Don't throw - user is already registered, this is non-critical bonus
      }
    } else {
      console.log(
        '⏭️ [ONBOARDING] Role is not recruiter:',
        role,
        '- skipping onboarding credits',
      );
    }

    try {
      await this.sendRegistrationEmail({
        email,
        fullName: full_name,
        role,
        username,
        companyName,
        isApproved: role === 'recruiter' ? false : true,
      });
    } catch (error) {
      console.error('Error sending registration email:', error);
    }

    console.log(
      '✅ [REGISTER] User registration completed successfully. userId:',
      userId,
    );
    return { userId };
  }
  //adminfranchise
  async findAllWithQuery(options: any = {}) {
    const { page = 1, limit = 10, status, role, search, hasCompany } = options;
    const offset = (page - 1) * limit;

    const query = this.userRepo
      .createQueryBuilder('user')
      .select([
        'user.id AS id',
        'user.fullName AS fullName',
        'user.username AS username',
        'user.email AS email',
        // 'user.password', // Password usually not needed in list
        'user.phone AS phone',
        'user.role AS role',
        'user.status AS status',
        'user.createdAt AS createdAt',
        'user.updatedAt AS updatedAt',
        'companies.name AS company_name', // Get company name
      ])
      .leftJoin(Companies, 'companies', 'companies.user_id = user.id') // Join companies
      .where('user.deletedAt IS NULL')
      .andWhere('user.role != :excludeRole', { excludeRole: 'franchise' });

    // ✅ Optional status filter
    if (status) {
      query.andWhere('user.status = :status', { status });
    }

    // ✅ Optional role filter
    if (role) {
      query.andWhere('user.role = :role', { role });

      // If role is recruiter, only show approved ones
      if (role === 'recruiter') {
        query.andWhere('companies.is_approved = 1');
      }
    }

    // ✅ Optional hasCompany filter
    if (hasCompany === 'true' || hasCompany === true) {
      query.andWhere('companies.id IS NOT NULL');
    }

    // ✅ Search filter
    if (search) {
      // Clean search term
      const searchTerm = `%${search}%`;
      query.andWhere(
        new Brackets((qb) => {
          qb.where('user.fullName LIKE :search', { search: searchTerm })
            .orWhere('user.email LIKE :search', { search: searchTerm })
            .orWhere('user.phone LIKE :search', { search: searchTerm })
            .orWhere('companies.name LIKE :search', { search: searchTerm });
        }),
      );
    }

    query.orderBy('user.createdAt', 'DESC');

    // Get count
    const total = await query.getCount();

    // Get data with pagination
    const users = await query.offset(offset).limit(limit).getRawMany();

    // Calculate stats for the filtered role if role is specified
    let stats = null;
    if (role) {
      const statsQuery = this.userRepo
        .createQueryBuilder('user')
        .select('user.status', 'status')
        .addSelect('COUNT(*)', 'count')
        .where('user.deletedAt IS NULL')
        .andWhere('user.role = :role', { role })
        .groupBy('user.status');

      const statusCounts = await statsQuery.getRawMany();

      const activeCount =
        statusCounts.find((s) => s.status === 'active')?.count || 0;
      const inactiveCount =
        statusCounts.find((s) => s.status === 'inactive')?.count || 0;
      const bannedCount =
        statusCounts.find((s) => s.status === 'banned')?.count || 0;

      stats = {
        total,
        active: parseInt(activeCount),
        inactive: parseInt(inactiveCount),
        banned: parseInt(bannedCount),
      };
    }

    return {
      success: true,
      data: {
        users, // This is now raw data with company_name
        pagination: {
          page,
          limit,
          total,
          totalPages: Math.ceil(total / limit),
        },
        stats,
      },
    };
  }

  // New method for filtered applicants with experience, roles, and qualification
  async findFilteredApplicants(
    options: {
      page?: number;
      limit?: number;
      status?: string;
      role?: string;
      experience?: string;
      qualification?: string;
      roles?: string;
      city?: string;
      search?: string;
      cvLastUpdated?: string;
      availability?: string;
      jobTitle?: string;
      skills?: string;
      onboardingStatus?: string;
    } = {},
  ) {
    const {
      page = 1,
      limit = 10,
      status,
      role,
      experience,
      qualification,
      roles,
      city,
      search,
      cvLastUpdated,
      availability,
      jobTitle,
      skills,
      onboardingStatus,
    } = options;
    const offset = (page - 1) * limit;

    const query = this.dataSource
      .createQueryBuilder()
      .select([
        'user.id as id',
        'user.full_name as fullName',
        'user.username as username',
        'user.email as email',
        'user.phone as phone',
        'user.role as role',
        'user.status as status',
        'user.selected_role_category as selected_role_category',
        'user.onboarding_status as onboarding_status',
        'user.onboarding_step as onboarding_step',
        'education.name as education_name',
        'exp.level as experience_name',
        // Prioritize new multi-select roles over legacy profession_id
        'COALESCE(GROUP_CONCAT(DISTINCT s_roles.name SEPARATOR ", "), roles.name) as professionName',
        // Prioritize Preferred City for Blue Collar, Permanent City for White Collar (with fallback to Communication City)
        '(CASE WHEN user.selected_role_category = "blue_collar" THEN pref_city.city_name ELSE COALESCE(city.city_name, comm_city.city_name) END) as cityName',
        // Get State Name matching the selected City (with fallback to Communication State)
        '(CASE WHEN user.selected_role_category = "blue_collar" THEN pref_state.state_name ELSE COALESCE(perm_state.state_name, comm_state.state_name) END) as stateName',
        'user.created_at as createdAt',
        'user.updated_at as updatedAt',
      ])
      .from('users', 'user')
      .leftJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
      .leftJoin('experiences', 'exp', 'exp.id = sp.experiences_id')
      .leftJoin(
        'education_levels',
        'education',
        'education.id = sp.education_id',
      )
      .leftJoin('roles', 'roles', 'roles.id = sp.profession_id')
      // Join for Multi-Select Roles (Blue Collar)
      .leftJoin(
        'seeker_role_experience',
        'sre',
        'sre.seeker_id = sp.id AND sre.status = 1',
      )
      .leftJoin('roles', 's_roles', 's_roles.id = sre.designation_id')
      .leftJoin('experiences', 's_exp', 's_exp.id = sre.experience_id')

      .leftJoin(
        'seeker_address',
        'address',
        'address.seeker_profile_id = sp.id',
      )
      .leftJoin('city', 'city', 'city.city_id = address.permanent_city_id')
      .leftJoin('state', 'perm_state', 'perm_state.state_id = city.state_id')
      .leftJoin('city', 'comm_city', 'comm_city.city_id = address.communication_city_id')
      .leftJoin('state', 'comm_state', 'comm_state.state_id = comm_city.state_id')
      .leftJoin(
        'city',
        'pref_city',
        'pref_city.city_id = sp.preferred_work_city_id',
      )
      .leftJoin(
        'state',
        'pref_state',
        'pref_state.state_id = pref_city.state_id',
      )
      .where('user.deleted_at IS NULL')
      .andWhere('user.role = :role', { role: role || 'seeker' })
      .andWhere('LOWER(user.full_name) NOT LIKE :testName', { testName: '%test%' })
      .andWhere('LOWER(user.full_name) NOT LIKE :guestName', { guestName: '%guest%' });

    // Optional status filter
    if (status) {
      query.andWhere('user.status = :status', { status });
    }

    // Optional onboarding status filter
    if (onboardingStatus) {
      query.andWhere('user.onboarding_status = :onboardingStatus', {
        onboardingStatus,
      });
    }

    // Search filter - name, phone, or city
    if (search) {
      const trimmedSearch = search.trim().toLowerCase();
      const searchTerm = `%${trimmedSearch}%`;

      query.andWhere(
        new Brackets((qb) => {
          qb.where(
            'LOWER(user.full_name) LIKE :searchTerm',
            { searchTerm },
          )
            .orWhere('user.phone LIKE :searchTerm', { searchTerm })
            .orWhere(
              'LOWER(city.city_name) LIKE :searchTerm',
              { searchTerm },
            )
            .orWhere(
              'LOWER(pref_city.city_name) LIKE :searchTerm',
              { searchTerm },
            );
        }),
      );
    }

    // Experience filter - filter by experience level ID or name (Check both legacy and new table)
    if (experience) {
      query.andWhere(
        '(exp.level LIKE :experience OR s_exp.level LIKE :experience)',
        { experience: `%${experience}%` },
      );
    }

    // Qualification filter - filter by education level ID or name
    if (qualification) {
      query.andWhere('education.name LIKE :qualification', {
        qualification: `%${qualification}%`,
      });
    }

    // Roles filter - filter by profession name (Check both legacy and new table)
    if (roles) {
      query.andWhere('(roles.name LIKE :roles OR s_roles.name LIKE :roles)', {
        roles: `%${roles}%`,
      });
    }

    // City filter - filter by city name (Check both permanent and preferred)
    if (city) {
      query.andWhere(
        '(city.city_name LIKE :city OR pref_city.city_name LIKE :city)',
        { city: `%${city}%` },
      );
    }

    // Group by user to avoid duplicates from joins
    
    // Availability filter
    if (availability) {
      query.andWhere('sp.your_availability = :availability', { availability });
    }

    // Job Title filter (uses roles)
    if (jobTitle) {
      query.andWhere('(roles.name LIKE :jobTitle OR s_roles.name LIKE :jobTitle)', {
        jobTitle: `%${jobTitle}%`,
      });
    }

    // Skills filter
    if (skills) {
      query.leftJoin('seeker_skills', 'ss', 'ss.seeker_id = sp.id')
           .leftJoin('skills', 'skill_table', 'skill_table.id = ss.skill_id')
           .andWhere('skill_table.skill_name LIKE :skills', { skills: `%${skills}%` });
    }

    // CV Last Updated filter
    if (cvLastUpdated) {
      query.leftJoin('seeker_resumes', 's_res', 's_res.seeker_id = user.id AND s_res.is_deleted = false');
      
      let dateThreshold = new Date();
      if (cvLastUpdated === 'last_7_days') {
        dateThreshold.setDate(dateThreshold.getDate() - 7);
      } else if (cvLastUpdated === 'last_30_days') {
        dateThreshold.setDate(dateThreshold.getDate() - 30);
      } else if (cvLastUpdated === 'last_6_months') {
        dateThreshold.setMonth(dateThreshold.getMonth() - 6);
      } else if (cvLastUpdated === 'last_year') {
        dateThreshold.setFullYear(dateThreshold.getFullYear() - 1);
      }

      if (cvLastUpdated !== 'all') {
        query.andWhere('s_res.uploaded_at >= :dateThreshold', { dateThreshold });
      }
    }
  
    query.groupBy('user.id');

    // Priority ordering for search matches
    if (search) {
      const exactSearch = search.trim().toLowerCase();
      query
        .orderBy(
          `(CASE WHEN LOWER(user.full_name) = :exactSearch THEN 0 ELSE 1 END)`,
          'ASC',
        )
        .addOrderBy('user.created_at', 'DESC')
        .setParameter('exactSearch', exactSearch);
    } else {
      query.orderBy('user.created_at', 'DESC');
    }

    query.offset(offset).limit(limit);

    const users = await query.getRawMany();

    // Get total count separately
    const countQuery = this.dataSource
      .createQueryBuilder()
      .select('COUNT(DISTINCT user.id)', 'count')
      .from('users', 'user')
      .leftJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
      .leftJoin('experiences', 'exp', 'exp.id = sp.experiences_id')
      .leftJoin(
        'education_levels',
        'education',
        'education.id = sp.education_id',
      )
      .leftJoin('roles', 'roles', 'roles.id = sp.profession_id')
      // Join for Multi-Select Roles
      .leftJoin(
        'seeker_role_experience',
        'sre',
        'sre.seeker_id = sp.id AND sre.status = 1',
      )
      .leftJoin('roles', 's_roles', 's_roles.id = sre.designation_id')
      .leftJoin('experiences', 's_exp', 's_exp.id = sre.experience_id')

      .leftJoin(
        'seeker_address',
        'address',
        'address.seeker_profile_id = sp.id',
      )
      .leftJoin('city', 'city', 'city.city_id = address.permanent_city_id')
      .leftJoin(
        'city',
        'pref_city',
        'pref_city.city_id = sp.preferred_work_city_id',
      )
      .where('user.deleted_at IS NULL')
      .andWhere('user.role = :role', { role: role || 'seeker' });

    // Apply same filters for count
    if (status) {
      countQuery.andWhere('user.status = :status', { status });
    }
    if (onboardingStatus) {
      countQuery.andWhere('user.onboarding_status = :onboardingStatus', {
        onboardingStatus,
      });
    }
    if (search) {
      const trimmedSearch = search.trim().toLowerCase();
      const searchTerm = `%${trimmedSearch}%`;
      const searchNoSpace = `%${trimmedSearch.replace(/\s+/g, '')}%`;

      countQuery.andWhere(
        new Brackets((qb) => {
          qb.where(
            'LOWER(REPLACE(user.full_name, " ", "")) LIKE :searchNoSpace',
            { searchNoSpace },
          )
            .orWhere('user.phone LIKE :searchTerm', { searchTerm })
            .orWhere(
              'LOWER(REPLACE(city.city_name, " ", "")) LIKE :searchNoSpace',
              { searchNoSpace },
            )
            .orWhere(
              'LOWER(REPLACE(pref_city.city_name, " ", "")) LIKE :searchNoSpace',
              { searchNoSpace },
            );
        }),
      );
    }
    if (experience) {
      countQuery.andWhere(
        '(exp.level LIKE :experience OR s_exp.level LIKE :experience)',
        { experience: `%${experience}%` },
      );
    }
    if (qualification) {
      countQuery.andWhere('education.name LIKE :qualification', {
        qualification: `%${qualification}%`,
      });
    }
    if (roles) {
      countQuery.andWhere(
        '(roles.name LIKE :roles OR s_roles.name LIKE :roles)',
        { roles: `%${roles}%` },
      );
    }
    if (city) {
      countQuery.andWhere(
        '(city.city_name LIKE :city OR pref_city.city_name LIKE :city)',
        { city: `%${city}%` },
      );
    }

    const totalResult = await countQuery.getRawOne();
    const total = parseInt(totalResult.count) || 0;

    // Calculate stats for the filtered results
    const statsQuery = this.dataSource
      .createQueryBuilder()
      .select('user.status', 'status')
      .addSelect('COUNT(DISTINCT user.id)', 'count')
      .from('users', 'user')
      .leftJoin('seeker_profiles', 'sp', 'sp.user_id = user.id')
      .leftJoin('experiences', 'exp', 'exp.id = sp.experiences_id')
      .leftJoin(
        'education_levels',
        'education',
        'education.id = sp.education_id',
      )
      .leftJoin('roles', 'roles', 'roles.id = sp.profession_id')
      // Join for Multi-Select Roles
      .leftJoin(
        'seeker_role_experience',
        'sre',
        'sre.seeker_id = sp.id AND sre.status = 1',
      )
      .leftJoin('roles', 's_roles', 's_roles.id = sre.designation_id')
      .leftJoin('experiences', 's_exp', 's_exp.id = sre.experience_id')

      .leftJoin(
        'seeker_address',
        'address',
        'address.seeker_profile_id = sp.id',
      )
      .leftJoin('city', 'city', 'city.city_id = address.permanent_city_id')
      .leftJoin(
        'city',
        'pref_city',
        'pref_city.city_id = sp.preferred_work_city_id',
      )
      .where('user.deleted_at IS NULL')
      .andWhere('user.role = :role', { role: role || 'seeker' });

    // Apply same filters for stats
    if (status) {
      statsQuery.andWhere('user.status = :status', { status });
    }
    if (onboardingStatus) {
      statsQuery.andWhere('user.onboarding_status = :onboardingStatus', {
        onboardingStatus,
      });
    }
    if (search) {
      const trimmedSearch = search.trim().toLowerCase();
      const searchTerm = `%${trimmedSearch}%`;
      const searchNoSpace = `%${trimmedSearch.replace(/\s+/g, '')}%`;

      statsQuery.andWhere(
        new Brackets((qb) => {
          qb.where(
            'LOWER(REPLACE(user.full_name, " ", "")) LIKE :searchNoSpace',
            { searchNoSpace },
          )
            .orWhere('user.phone LIKE :search', { search: searchTerm })
            .orWhere(
              'LOWER(REPLACE(city.city_name, " ", "")) LIKE :searchNoSpace',
              { searchNoSpace },
            )
            .orWhere(
              'LOWER(REPLACE(pref_city.city_name, " ", "")) LIKE :searchNoSpace',
              { searchNoSpace },
            );
        }),
      );
    }
    if (experience) {
      statsQuery.andWhere(
        '(exp.level LIKE :experience OR s_exp.level LIKE :experience)',
        { experience: `%${experience}%` },
      );
    }
    if (qualification) {
      statsQuery.andWhere('education.name LIKE :qualification', {
        qualification: `%${qualification}%`,
      });
    }
    if (roles) {
      statsQuery.andWhere(
        '(roles.name LIKE :roles OR s_roles.name LIKE :roles)',
        { roles: `%${roles}%` },
      );
    }
    if (city) {
      statsQuery.andWhere(
        '(city.city_name LIKE :city OR pref_city.city_name LIKE :city)',
        { city: `%${city}%` },
      );
    }

    statsQuery.groupBy('user.status');
    const statusCounts = await statsQuery.getRawMany();

    const activeCount =
      statusCounts.find((s) => s.status === 'active')?.count || 0;
    const inactiveCount =
      statusCounts.find((s) => s.status === 'inactive')?.count || 0;
    const bannedCount =
      statusCounts.find((s) => s.status === 'banned')?.count || 0;

    const stats = {
      total,
      active: parseInt(activeCount),
      inactive: parseInt(inactiveCount),
      banned: parseInt(bannedCount),
    };

    return {
      success: true,
      data: {
        users,
        pagination: {
          page,
          limit,
          total,
          totalPages: Math.ceil(total / limit),
        },
        stats,
      },
    };
  }

  // 🔹 Normal user update with explicit QueryBuilder
  async updateUser(id: number, data: Partial<UserEntity>): Promise<UserEntity> {
    await this.userRepo
      .createQueryBuilder()
      .update(UserEntity)
      .set({
        username: data.email,
        email: data.email,
        password: data.password || undefined, // ✅ only update if provided
        fullName: data.fullName,
        phone: data.phone || null,
        role: data.role,
        // status: data.status || 'active',
      })
      .where('id = :id', { id })
      .execute();

    // ❌ Clean up role_access if role is not operation
    if (data.role !== 'operation') {
      await this.dataSource
        .createQueryBuilder()
        .delete()
        .from(RoleAccessEntity)
        .where('userId = :id', { id })
        .execute();
    }

    return this.findOne(id);
  }

  // user.service.ts
  async updateStatus(
    id: number,
    status: 'active' | 'inactive' | 'banned',
  ): Promise<any> {
    // 1️⃣ Get user
    const user = await this.findOne(id);
    if (!user) {
      throw new Error('User not found');
    }

    await this.userRepo
      .createQueryBuilder()
      .update(UserEntity)
      .set({ status })
      .where('id = :id', { id })
      .execute();

    // 2️ If recruiter → update company status and is_deleted
    if (user.role === 'recruiter') {
      const companyStatus = status === 'active' ? 1 : 0;
      const isDeleted = status === 'active' ? 0 : 1;
      await this.dataSource.query(
        'UPDATE companies SET status = ?, is_deleted = ? WHERE user_id = ?',
        [companyStatus, isDeleted, id],
      );
    }

    // 3️ If operation → update role_access status
    if (user.role === 'operation') {
      await this.dataSource
        .createQueryBuilder()
        .update(RoleAccessEntity)
        .set({ status })
        .where('userId = :id', { id })
        .execute();
    }

    return { success: true, message: `User status updated to ${status}` };
  }

  // NEW OTP AUTHENTICATION METHODS

  /**
   * Check if phone exists and return all roles associated with it
   */
  async checkPhoneExists(phone: string, role?: string) {
    // Check for active users
    const activeUsers = await this.userRepo.find({
      where: {
        phone: phone,
        status: 'active',
        ...(role && { role: role as any }),
      },
      select: ['id', 'role', 'fullName'],
    });

    // Check for inactive users (for reactivation)
    const inactiveUsers = await this.userRepo.find({
      where: {
        phone: phone,
        status: 'inactive',
        ...(role && { role: role as any }),
      },
      select: ['id', 'role', 'fullName', 'email'],
    });

    // Filter out admin (admin uses separate login page)
    const nonAdminActiveUsers = activeUsers.filter((u) => u.role !== 'admin');
    const nonAdminInactiveUsers = inactiveUsers.filter(
      (u) => u.role !== 'admin',
    );

    // If no active users, check for inactive users
    if (nonAdminActiveUsers.length === 0) {
      // Demo account check for Google/Play Store verification
      // If the demo number has NO active user in DB → treat as NEW registration (exists: false)
      // This allows the mobile dev to always test the full registration flow after deleting the demo user
      if (phone === process.env.DEMO_MOBILE_NUMBER) {
        if (nonAdminInactiveUsers.length > 0) {
          // Demo number has an inactive account → treat as inactive (admin can reactivate)
          const availableRolesForRegistration =
            this.getAvailableRolesForRegistration(nonAdminInactiveUsers);
          return {
            exists: false, // Allow fresh registration even if inactive record exists for demo number
            roles: [],
            requiresRoleSelection: false,
            availableRolesForRegistration,
            hasInactiveAccounts: true,
            inactiveRoles: nonAdminInactiveUsers.map((u) => ({
              role: u.role,
              fullName: u.fullName,
              userId: u.id,
              email: this.maskEmail(u.email),
            })),
          };
        }
        // No user at all → fresh registration
        return { exists: false };
      }

      if (nonAdminInactiveUsers.length > 0) {
        // Get roles that are available for registration (excluding inactive roles)
        const availableRolesForRegistration =
          this.getAvailableRolesForRegistration(nonAdminInactiveUsers);

        // Return inactive account info for reactivation
        return {
          exists: role ? true : false,
          roles: [],
          requiresRoleSelection: false,
          availableRolesForRegistration,
          hasInactiveAccounts: true,
          inactiveRoles: nonAdminInactiveUsers.map((u) => ({
            role: u.role,
            fullName: u.fullName,
            userId: u.id,
            email: this.maskEmail(u.email),
          })),
        };
      }

      return { exists: false };
    }

    // Get roles that are available for registration
    // Exclude both active AND inactive roles from available roles for registration
    const allExistingRoles = [
      ...nonAdminActiveUsers.map((u) => u.role),
      ...nonAdminInactiveUsers.map((u) => u.role),
    ];
    const availableRolesForRegistration = this.getAvailableRolesForRegistration(
      allExistingRoles.map((role) => ({ role })),
    );

    return {
      exists: true,
      roles: nonAdminActiveUsers.map((u) => ({
        role: u.role,
        fullName: u.fullName,
        userId: u.id,
      })),
      requiresRoleSelection: true, // Always show role selection, even for single role
      availableRolesForRegistration: availableRolesForRegistration,
      // Also include inactive accounts info if any (for reactivation, not registration)
      hasInactiveAccounts: nonAdminInactiveUsers.length > 0,
      inactiveRoles:
        nonAdminInactiveUsers.length > 0
          ? nonAdminInactiveUsers.map((u) => ({
            role: u.role,
            fullName: u.fullName,
            userId: u.id,
            hasEmail: !!u.email,
          }))
          : [],
    };
  }

  /**
   * Get roles available for registration (excluding existing roles)
   * Only includes seeker and recruiter (franchise registration handled separately)
   */
  private getAvailableRolesForRegistration(existingUsers: any[]): string[] {
    const existingRoles = existingUsers.map((u) => u.role);
    const allRoles = ['seeker', 'recruiter']; // Only seeker and recruiter (NO franchise)
    return allRoles.filter((role) => !existingRoles.includes(role));
  }

  /**
   * Send OTP for specific role (existing user login)
   */
  async sendOTPForRole(phone: string, role: string) {
    const user = await this.userRepo.findOne({
      where: { phone, role: role as any, status: 'active' },
    });

    if (!user) {
      if (phone === '7003585693' || phone === process.env.DEMO_MOBILE_NUMBER) {
        // Fallback for demo numbers if user record doesn't exist yet
        return { success: true, message: 'OTP sent successfully' };
      }
      throw new Error('User not found');
    }

    // Generate 4-digit OTP
    const otp =
      phone === process.env.DEMO_MOBILE_NUMBER || phone === '7003585693'
        ? '1234'
        : Math.floor(1000 + Math.random() * 9000).toString();
    const otpExpiresAt = new Date(Date.now() + 10 * 60 * 1000); // 10 minutes

    // Check if OTP already exists for this user
    const existingOTP = await this.otpRepo.findOne({
      where: { user_id: user.id },
    });

    if (existingOTP) {
      // Update existing OTP
      await this.otpRepo.update(existingOTP.id, {
        otp: otp,
        otp_expires_at: otpExpiresAt,
      });
    } else {
      // Create new OTP
      await this.otpRepo.save({
        user_id: user.id,
        otp: otp,
        otp_expires_at: otpExpiresAt,
      });
    }

    // Send OTP via SMS
    await this.sendOTPSMS(phone, otp);
    // return { success: true };
    return {
      success: true,
      hasEmail: !!(user.email && user.email.includes('@')),
    };
  }

  /**
   * Verify OTP and login for specific role
   */
  async loginWithOTPAndRole(phone: string, otp: string, role: string) {
    // console.log(`[Demo Debug] Attempting login for phone: ${phone}, otp: ${otp}, role: ${role}`);
    // console.log(`[Demo Debug] Env Mobile: ${process.env.DEMO_MOBILE_NUMBER}, Env OTP: ${process.env.DEMO_OTP}`);

    // Use QueryBuilder to ensure we get all fields including profilePicture
    let user = await this.userRepo
      .createQueryBuilder('user')
      .where('user.phone = :phone', { phone })
      .andWhere('user.role = :role', { role })
      .andWhere('user.status = :status', { status: 'active' })
      .addSelect('user.profilePicture') // Explicitly select profilePicture
      .getOne();

    // Demo account auto-creation
    if (
      !user &&
      (phone === process.env.DEMO_MOBILE_NUMBER || phone === '7003585693') &&
      (role === 'seeker' || role === 'recruiter')
    ) {
      const tempUsername = `user_${phone}_${Date.now()}`;
      const placeholderName = 'Demo Seeker';

      await this.dataSource.query(
        `INSERT INTO users (
          username, full_name, email, phone, password, role, auth_method, phone_verified, 
          status, onboarding_status, onboarding_step, created_at, updated_at
        ) VALUES (?, ?, NULL, ?, NULL, ?, 'otp', 1, 'active', 'not_started', 0, NOW(), NOW())`,
        [tempUsername, placeholderName, phone, role],
      );

      user = await this.userRepo
        .createQueryBuilder('user')
        .where('user.phone = :phone', { phone })
        .andWhere('user.role = :role', { role })
        .getOne();

      if (user) {
        if (role === 'seeker') {
          await this.dataSource.query(
            `INSERT INTO seeker_profiles (user_id, created_at, updated_at) VALUES (?, NOW(), NOW())`,
            [user.id],
          );
        } else if (role === 'recruiter') {
          const placeholderCompanyName = `Company_${phone}`;
          await this.dataSource.query(
            `INSERT INTO companies (user_id, name, status, featured, is_deleted, is_approved, created_at, updated_at) 
             VALUES (?, ?, 1, 0, 0, 0, NOW(), NOW())`,
            [user.id, placeholderCompanyName],
          );
        }
      }
    }

    if (!user) return null;

    // Find valid OTP for this user
    const otpRecord = await this.otpRepo
      .createQueryBuilder('otp')
      .where('otp.user_id = :userId', { userId: user.id })
      .andWhere('otp.otp = :otp', { otp })
      .andWhere('otp.otp_expires_at > :now', { now: new Date() })
      .getOne();

    if (
      !otpRecord &&
      !(
        (phone === process.env.DEMO_MOBILE_NUMBER || phone === '7003585693') &&
        otp === '1234'
      )
    )
      return null;

    // Delete used OTP
    if (otpRecord) {
      await this.otpRepo.delete(otpRecord.id);
    }

    // Mark phone as verified
    const updateData: any = { phoneVerified: true };

    // If seeker and onboarding not started, set to in_progress step 1
    // This is because we removed the post-auth role/category selection modal
    if (
      user.role === 'seeker' &&
      (user.onboardingStatus === 'not_started' || !user.onboardingStatus)
    ) {
      updateData.onboardingStatus = 'in_progress';
      updateData.onboardingStep = 1;
    }

    await this.userRepo.update(user.id, updateData);

    return user;
  }

  /**
   * Send registration OTP for new users
   */
  async sendRegistrationOTP(phone: string, role: string) {
    // Check if account already exists (active or inactive)
    const exists = await this.userRepo.findOne({
      where: { phone, role: role as any },
    });

    if (exists) {
      // If account is inactive, suggest reactivation
      if (exists.status === 'inactive') {
        throw new Error(
          'Account exists but is inactive. Please reactivate your account instead.',
        );
      }
      throw new Error('Account already exists. Please login instead.');
    }

    // Generate 4-digit OTP
    const otp =
      phone === process.env.DEMO_MOBILE_NUMBER || phone === '7003585693'
        ? '1234'
        : Math.floor(1000 + Math.random() * 9000).toString();
    // Format datetime for IST (India Standard Time: UTC+5:30)
    const now = new Date();
    const istOffset = 5.5 * 60 * 60 * 1000; // 5 hours 30 minutes in milliseconds
    const otpExpiresAt = new Date(now.getTime() + istOffset + 10 * 60 * 1000); // IST + 10 minutes
    const formattedExpiry = otpExpiresAt
      .toISOString()
      .slice(0, 19)
      .replace('T', ' ');

    // Delete any existing registration OTP for this phone+role
    await this.dataSource.query(
      'DELETE FROM registration_otps WHERE phone_number = ? AND role = ?',
      [phone, role],
    );

    // Insert new registration OTP with IST-formatted datetime
    await this.dataSource.query(
      'INSERT INTO registration_otps (phone_number, otp, role, otp_expires_at) VALUES (?, ?, ?, ?)',
      [phone, otp, role, formattedExpiry],
    );

    // Send OTP via SMS
    await this.sendOTPSMS(phone, otp);
    return { success: true };
  }

  /**
   * Verify registration OTP
   */
  async verifyRegistrationOTP(
    phone: string,
    otp: string,
    role: string,
    source: 'web' | 'android' | 'ios' = 'web',
    deviceType?: string,
  ) {
    // console.log(`[Demo Debug] Attempting registration for phone: ${phone}, otp: ${otp}, role: ${role}`);
    // Get current IST time for comparison
    const now = new Date();
    const istOffset = 5.5 * 60 * 60 * 1000;
    const istNow = new Date(now.getTime() + istOffset);
    const formattedNow = istNow.toISOString().slice(0, 19).replace('T', ' ');

    // Find valid registration OTP
    const [record] = await this.dataSource.query(
      'SELECT * FROM registration_otps WHERE phone_number = ? AND otp = ? AND role = ? AND otp_expires_at > ?',
      [phone, otp, role, formattedNow],
    );

    if (
      !record &&
      !(
        (phone === process.env.DEMO_MOBILE_NUMBER || phone === '7003585693') &&
        otp === '1234'
      )
    )
      return false;

    // Mark as verified
    if (record) {
      await this.dataSource.query(
        'UPDATE registration_otps SET verified = 1 WHERE id = ?',
        [record.id],
      );
    }

    // [MOBILE-FIRST] Create User immediately if not exists
    const existingUser = await this.userRepo.findOne({
      where: { phone, role: role as any },
    });

    if (!existingUser) {
      // Create a temporary "Guest" user
      // We use a placeholder username and fullName to satisfy NOT NULL constraints
      const tempUsername = `user_${phone}_${Date.now()}`;
      const placeholderName = 'Guest User'; // Will be updated in completeOnboarding

      // Unified logic: All users start in_progress at Step 1
      const onboardingStatus = 'in_progress';
      const onboardingStep = source === 'web' ? 1 : 0;
      const mobileOnboardingStep = source !== 'web' ? 1 : null;

      await this.dataSource.query(
        `INSERT INTO users (
          username, full_name, email, phone, password, role, auth_method, phone_verified, 
          status, onboarding_status, onboarding_step, mobile_onboarding_step, selected_role_category,
          created_at, updated_at
        ) VALUES (?, ?, NULL, ?, NULL, ?, 'otp', 1, 'active', ?, ?, ?, ?, NOW(), NOW())`,
        [
          tempUsername,
          placeholderName,
          phone,
          role,
          onboardingStatus,
          onboardingStep,
          mobileOnboardingStep,
          null, // selected_role_category = NULL initially
        ],
      );

      // Get the newly created user ID
      const [newUser] = await this.dataSource.query(
        'SELECT id FROM users WHERE phone = ? AND role = ?',
        [phone, role],
      );

      if (newUser) {
        if (role === 'seeker') {
          // Create minimal seeker profile
          await this.dataSource.query(
            `INSERT INTO seeker_profiles (
              user_id, 
              created_at, 
              updated_at
            ) VALUES (?, NOW(), NOW())`,
            [newUser.id],
          );
        } else if (role === 'recruiter') {
          // Create minimal company profile
          // Use placeholder name that will be updated during onboarding
          const placeholderCompanyName = `Company_${phone}`;
          await this.dataSource.query(
            `INSERT INTO companies (
              user_id,
              name,
              status,
              featured,
              is_deleted,
              is_approved,
              created_at,
              updated_at
            ) VALUES (?, ?, 1, 0, 0, 0, NOW(), NOW())`,
            [newUser.id, placeholderCompanyName],
          );
        }
      }
    }

    return true;
  }

  /**
   * Send reactivation OTP for inactive users
   */
  async sendReactivationOTP(phone: string, role: string) {
    // Only seeker and recruiter can use reactivation via mobile — admin cannot
    if (role === 'admin' || (role !== 'seeker' && role !== 'recruiter')) {
      throw new Error(
        'Reactivation is only available for seeker and recruiter accounts',
      );
    }

    // Check if inactive account exists
    const user = await this.userRepo.findOne({
      where: { phone, role: role as any, status: 'inactive' },
      select: ['id', 'email'],
    });

    if (!user) {
      throw new Error(
        'No inactive account found for this phone number and role',
      );
    }

    // Generate 4-digit OTP
    const otp =
      phone === process.env.DEMO_MOBILE_NUMBER
        ? process.env.DEMO_OTP
        : Math.floor(1000 + Math.random() * 9000).toString();
    // Format datetime for IST (India Standard Time: UTC+5:30)
    const now = new Date();
    const istOffset = 5.5 * 60 * 60 * 1000; // 5 hours 30 minutes in milliseconds
    const otpExpiresAt = new Date(now.getTime() + istOffset + 10 * 60 * 1000); // IST + 10 minutes
    const formattedExpiry = otpExpiresAt
      .toISOString()
      .slice(0, 19)
      .replace('T', ' ');

    // Delete any existing reactivation OTP for this phone+role
    // Note: We'll use a prefix in the role field or check by phone+role combination
    // Since flow_type might not exist, we'll use a separate identifier
    await this.dataSource.query(
      'DELETE FROM registration_otps WHERE phone_number = ? AND role = ?',
      [phone, role],
    );

    // Insert new reactivation OTP with IST-formatted datetime
    // Store with a marker in role or use a separate approach
    await this.dataSource.query(
      'INSERT INTO registration_otps (phone_number, otp, role, otp_expires_at) VALUES (?, ?, ?, ?)',
      [phone, otp, role, formattedExpiry],
    );

    // Send OTP via SMS (always send — demo number is mobile dev's real number)
    await this.sendOTPSMS(phone, otp);
    return { success: true, hasEmail: !!user.email };
  }

  /**
   * Verify reactivation OTP and activate account
   */
  async verifyReactivationOTP(phone: string, otp: string, role: string) {
    // Only seeker and recruiter can use reactivation via mobile — admin cannot
    if (role === 'admin' || (role !== 'seeker' && role !== 'recruiter')) {
      throw new Error(
        'Reactivation is only available for seeker and recruiter accounts',
      );
    }

    // Get current IST time for comparison
    const now = new Date();
    const istOffset = 5.5 * 60 * 60 * 1000;
    const istNow = new Date(now.getTime() + istOffset);
    const formattedNow = istNow.toISOString().slice(0, 19).replace('T', ' ');

    // Find valid reactivation OTP (check if user is inactive to ensure it's a reactivation OTP)
    const [record] = await this.dataSource.query(
      'SELECT * FROM registration_otps WHERE phone_number = ? AND otp = ? AND role = ? AND otp_expires_at > ?',
      [phone, otp, role, formattedNow],
    );

    if (
      !record &&
      !(
        phone === process.env.DEMO_MOBILE_NUMBER && otp === process.env.DEMO_OTP
      )
    )
      return false;

    // Find the inactive user
    const user = await this.userRepo.findOne({
      where: { phone, role: role as any, status: 'inactive' },
    });

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

    // If recruiter, update company status FIRST (before user status)
    // This prevents "profile not found" errors
    if (role === 'recruiter') {
      await this.dataSource.query(
        'UPDATE companies SET status = 1, is_deleted = 0 WHERE user_id = ?',
        [user.id],
      );
    }

    // Then activate the user account
    await this.userRepo.update(user.id, {
      status: 'active',
      deletedAt: null, // Clear deletedAt timestamp
    });

    // Mark OTP as verified
    if (record) {
      await this.dataSource.query(
        'UPDATE registration_otps SET verified = 1 WHERE id = ?',
        [record.id],
      );
    }

    // Return the updated user to ensure data is fresh
    const updatedUser = await this.userRepo.findOne({
      where: { id: user.id },
    });

    return updatedUser ? true : false;
  }

  /**
   * Send reactivation OTP via email (fallback)
   */
  async sendReactivationOTPEmail(
    phone: string,
    role: string,
  ): Promise<{ success: boolean; message: string }> {
    try {
      // console.log(
      //   `[Reactivation Email OTP] Starting for phone: ${phone}, role: ${role}`,
      // );

      // Find inactive user by phone number AND role
      const user = await this.userRepo
        .createQueryBuilder('user')
        .where('user.phone = :phone', { phone })
        .andWhere('user.role = :role', { role })
        .andWhere('user.status = :status', { status: 'inactive' })
        .getOne();

      if (!user) {
        // console.log(
        //   `[Reactivation Email OTP] User not found for phone: ${phone}, role: ${role}`,
        // );
        return { success: false, message: 'Inactive account not found' };
      }

      if (!user.email) {
        // console.log(`[Reactivation Email OTP] No email for user: ${user.id}`);
        return {
          success: false,
          message: 'No email address associated with this account',
        };
      }

      // console.log(
      //   `[Reactivation Email OTP] User found with email: ${this.maskEmail(user.email)}`,
      // );

      // Get the existing reactivation OTP from registration_otps table
      const now = new Date();
      const istOffset = 5.5 * 60 * 60 * 1000;
      const istNow = new Date(now.getTime() + istOffset);
      const formattedNow = istNow.toISOString().slice(0, 19).replace('T', ' ');

      // console.log(
      //   `[Reactivation Email OTP] Querying OTP with formattedNow: ${formattedNow}`,
      // );

      const [otpRecord] = await this.dataSource.query(
        'SELECT * FROM registration_otps WHERE phone_number = ? AND role = ? AND otp_expires_at > ? ORDER BY id DESC LIMIT 1',
        [phone, role, formattedNow],
      );

      if (!otpRecord) {
        // console.log(
        //   `[Reactivation Email OTP] No active OTP found for phone: ${phone}, role: ${role}`,
        // );
        // Try to find any OTP record (for debugging)
        const [anyOtpRecord] = await this.dataSource.query(
          'SELECT * FROM registration_otps WHERE phone_number = ? AND role = ? ORDER BY id DESC LIMIT 1',
          [phone, role],
        );
        if (anyOtpRecord) {
          // console.log(
          //   `[Reactivation Email OTP] Found expired OTP: expires_at=${anyOtpRecord.otp_expires_at}, current=${formattedNow}`,
          // );
        }
        return {
          success: false,
          message: 'No active OTP found. Please request a new OTP first.',
        };
      }

      // console.log(
      //   `[Reactivation Email OTP] Found OTP record, sending email with OTP: ${otpRecord.otp}`,
      // );

      // Send the SAME OTP via email using the same method as login
      const emailSent = await this.sendOTPEmail(user.email, otpRecord.otp);

      if (emailSent) {
        // console.log(
        //   `[Reactivation Email OTP] Email sent successfully to ${this.maskEmail(user.email)}`,
        // );
        return {
          success: true,
          message: `OTP sent successfully to your registered email (${this.maskEmail(user.email)})`,
        };
      } else {
        // console.error(
        //   `[Reactivation Email OTP] Failed to send email to ${this.maskEmail(user.email)}`,
        // );
        return {
          success: false,
          message: 'Failed to send OTP via email. Please try again.',
        };
      }
    } catch (error) {
      // console.error('[Reactivation Email OTP] Error:', error);
      // console.error('[Reactivation Email OTP] Error stack:', error.stack);
      return {
        success: false,
        message: `Error sending OTP via email: ${error.message || 'Unknown error'}`,
      };
    }
  }

  /**
   * Find user by phone and role
   */
  async findOneByPhone(phone: string): Promise<UserEntity | null> {
    return await this.userRepo.findOne({ where: { phone } });
  }

  async findByPhoneAndRole(
    phone: string,
    role: string,
  ): Promise<UserEntity | null> {
    return await this.userRepo.findOne({
      where: { phone, role: role as any },
    });
  }

  /**
   * Check if email exists for a different phone number
   * Used during seeker onboarding to validate email uniqueness
   */
  async checkEmailExistsForDifferentPhone(
    email: string,
    phone: string,
    userId?: number,
  ): Promise<{ exists: boolean; message?: string }> {
    if (!email) {
      return { exists: false };
    }

    const userWithEmail = await this.userRepo.findOne({
      where: { email },
      select: ['id', 'phone', 'email'],
    });

    if (!userWithEmail) {
      return { exists: false };
    }

    // Same user keeping/updating their own email during onboarding
    if (userId && userWithEmail.id === Number(userId)) {
      return { exists: false };
    }

    return {
      exists: true,
      message:
        'This email is already registered. Please use a different email.',
    };
  }

  /**
   * Update user's onboarding step
   */
  async updateOnboardingStep(userId: number, step: number) {
    await this.userRepo.update(userId, {
      onboardingStep: step,
    });

    return { success: true, step };
  }

  /**
   * Update onboarding data (progressive save)
   * Handles both User entity and Profile entities (Seeker/Company)
   */
  async updateOnboardingData(userId: number, data: any) {
    const user = await this.findOne(userId);
    if (!user) {
      throw new Error('User not found');
    }

    // Normalize snake_case field names sent by the web existing-user PUT path.
    // The web frontend populates form fields from DB responses (snake_case column names)
    // so these aliases make sure they reach the camelCase checks below.
    if (data.profession_id !== undefined && data.professionId === undefined)
      data.professionId = data.profession_id;
    if (
      data.professional_title !== undefined &&
      data.professionalTitle === undefined
    )
      data.professionalTitle = data.professional_title;
    if (data.date_of_birth !== undefined && data.dateOfBirth === undefined)
      data.dateOfBirth = data.date_of_birth;
    if (data.marital_status !== undefined && data.maritalStatus === undefined)
      data.maritalStatus = data.marital_status;
    if (data.education_id !== undefined && data.educationId === undefined)
      data.educationId = data.education_id;
    if (data.experiences_id !== undefined && data.experiencesId === undefined)
      data.experiencesId = data.experiences_id;
    if (
      data.personal_website !== undefined &&
      data.personalWebsite === undefined
    )
      data.personalWebsite = data.personal_website;
    if (
      data.preferred_work_city_id !== undefined &&
      data.preferredWorkCityId === undefined
    )
      data.preferredWorkCityId = data.preferred_work_city_id;
    if (
      data.preferred_work_locality_area_id !== undefined &&
      data.preferredWorkLocalityId === undefined
    )
      data.preferredWorkLocalityId = data.preferred_work_locality_area_id;
    if (data.your_availability !== undefined && data.availability === undefined)
      data.availability = data.your_availability;
    if (
      data.bio_graphy !== undefined &&
      data.biography === undefined &&
      data.bio === undefined
    )
      data.biography = data.bio_graphy;

    // 1. Update User Entity fields
    const userUpdates: any = {};

    // 0. Auto-transition from 'not_started' to 'in_progress'
    if (user.onboardingStatus === 'not_started') {
      userUpdates.onboardingStatus = 'in_progress';
      userUpdates.onboardingStep = 1;
    }

    if (data.fullName) userUpdates.fullName = data.fullName;
    if (data.email) {
      const normalizedEmail = String(data.email).trim().toLowerCase();
      // Case-insensitive check — findOne with ORM is case-sensitive on some collations
      const [existingEmailRow] = await this.dataSource.query(
        'SELECT id FROM users WHERE LOWER(email) = ? AND id != ? LIMIT 1',
        [normalizedEmail, userId],
      );
      if (existingEmailRow) {
        throw new Error(
          'This email is already registered. Please use a different email.',
        );
      }
      userUpdates.email = normalizedEmail;
    }
    if (data.phone) userUpdates.phone = data.phone;
    if (data.seekerType) userUpdates.seekerType = data.seekerType;
    if (data.profilePicture) userUpdates.profilePicture = data.profilePicture;
    if (data.selectedRoleCategory || data.selected_role_category) {
      userUpdates.selectedRoleCategory =
        data.selectedRoleCategory || data.selected_role_category;
    }

    // console.log(' [UPDATE ONBOARDING DATA] User updates:', userUpdates);

    // NEW: Handle explicit step update from merged endpoint
    if (data.step !== undefined) userUpdates.onboardingStep = data.step;
    if (data.onboardingStep !== undefined)
      userUpdates.onboardingStep = data.onboardingStep;
    if (data.mobileOnboardingStep !== undefined)
      userUpdates.mobileOnboardingStep = data.mobileOnboardingStep;
    if (data.mobile_onboarding_step !== undefined)
      userUpdates.mobileOnboardingStep = data.mobile_onboarding_step;

    if (Object.keys(userUpdates).length > 0) {
      console.log(`[DEBUG] Updating user ${userId} fields:`, userUpdates);
      await this.userRepo.update(userId, userUpdates);
    }

    // 2. Update Profile Entity fields based on role
    if (user.role === 'seeker') {
      const seekerRepo = this.dataSource.getRepository(SeekerProfiles);
      const profileUpdates: any = {};

      // Map Role Category specific fields (Fix: white_collar -> seeker_profiles, blue_collar -> seeker_role_experience)
      const currentCategory =
        data.selectedRoleCategory ||
        data.selected_role_category ||
        user.selectedRoleCategory;

      if (currentCategory === 'white_collar') {
        if (
          data.experiencesId !== undefined ||
          data.experienceId !== undefined
        ) {
          const expVal = data.experiencesId ?? data.experienceId;
          profileUpdates.experiences_id =
            expVal === '' ? null : Number(expVal) || null;
        }
        if (data.professionId !== undefined)
          profileUpdates.profession_id =
            data.professionId === '' ? null : Number(data.professionId) || null;
      } else if (currentCategory === 'blue_collar') {
        // For blue-collar, clear experiences_id as it's handled separately,
        // but keep profession_id if it helps the web UI show the current role.
        profileUpdates.experiences_id = null;
        if (data.professionId !== undefined) {
          profileUpdates.profession_id =
            data.professionId === '' ? null : Number(data.professionId) || null;
        }
      }

      // Map other general profile fields
      if (data.educationId !== undefined)
        profileUpdates.education_id =
          data.educationId === '' ? null : Number(data.educationId) || null;
      if (data.professionalTitle !== undefined)
        profileUpdates.professional_title = data.professionalTitle;
      if (data.dateOfBirth !== undefined)
        profileUpdates.date_of_birth = data.dateOfBirth;
      if (data.gender !== undefined) profileUpdates.gender = data.gender;
      if (data.maritalStatus !== undefined)
        profileUpdates.marital_status = data.maritalStatus;
      if (data.availability !== undefined)
        profileUpdates.your_availability = data.availability;
      if (data.biography !== undefined)
        profileUpdates.bio_graphy = data.biography;
      if (data.bio !== undefined) profileUpdates.bio_graphy = data.bio;
      if (data.personalWebsite !== undefined)
        profileUpdates.personal_website = data.personalWebsite;

      // Handle Skills & Languages (align frontend/backend keys)
      if (data.skillsIds !== undefined || data.skills !== undefined)
        profileUpdates.skills_ids = data.skillsIds ?? data.skills;
      if (data.languagesIds !== undefined || data.languages !== undefined)
        profileUpdates.languages_ids = data.languagesIds ?? data.languages;

      if (data.preferredWorkCityId !== undefined)
        profileUpdates.preferred_work_city_id =
          data.preferredWorkCityId === ''
            ? null
            : Number(data.preferredWorkCityId) || null;
      if (data.preferredWorkLocalityId !== undefined)
        profileUpdates.preferred_work_locality_area_id =
          data.preferredWorkLocalityId === ''
            ? null
            : Number(data.preferredWorkLocalityId) || null;

      const hasAddressUpdates =
        data.permanentAddress !== undefined ||
        data.permanentPincode !== undefined ||
        data.permanentCityId !== undefined ||
        data.permanentStateId !== undefined ||
        data.permanentCountryId !== undefined ||
        data.permanentAreaId !== undefined ||
        data.communicationAddress !== undefined ||
        data.communicationPincode !== undefined ||
        data.communicationCityId !== undefined ||
        data.communicationStateId !== undefined ||
        data.communicationCountryId !== undefined ||
        data.communicationAreaId !== undefined ||
        data.sameAsPermanent !== undefined;

      const needsProfileRecord =
        Object.keys(profileUpdates).length > 0 ||
        hasAddressUpdates ||
        (data.experiences && Array.isArray(data.experiences)) ||
        (data.educations && Array.isArray(data.educations)) ||
        (data.roleExperiences && Array.isArray(data.roleExperiences));

      let profileId: number | null = null;

      if (needsProfileRecord) {
        const profile = await seekerRepo.findOne({
          where: { user_id: userId },
        });

        if (profile) {
          if (Object.keys(profileUpdates).length > 0) {
            await seekerRepo.update({ user_id: userId }, profileUpdates);
          }
          profileId = profile.id;
        } else {
          const newProfile = await seekerRepo.save({
            user_id: userId,
            ...profileUpdates,
          });
          profileId = newProfile.id;
        }
      }

      if (profileId) {
        // 2.1 Update Address (if provided)
        if (currentCategory === 'blue_collar') {
          const addressRepo = this.dataSource.getRepository(SeekerAddress);
          await addressRepo.delete({ seeker_profile_id: profileId });
        } else if (hasAddressUpdates) {
          await this.seekerAddressService.upsertAddress(profileId, data);
        }

        // 2.2 Update Experiences (if provided)
        if (data.experiences && Array.isArray(data.experiences)) {
          const experienceRepo =
            this.dataSource.getRepository(SeekerExperience);
          await experienceRepo.delete({ seeker_id: profileId });

          const newExperiences = data.experiences.map((exp: any) => ({
            seeker_id: profileId,
            company: exp.company,
            department: exp.department,
            designation: exp.designation,
            start_date: exp.startDate,
            end_date: exp.current ? null : exp.endDate,
            current: exp.current ? true : false,
            responsibilities: exp.responsibilities,
          }));

          if (newExperiences.length > 0) {
            await experienceRepo.save(newExperiences);
          }
        }

        // 2.3 Update Educations (if provided)
        if (data.educations && Array.isArray(data.educations)) {
          const educationRepo = this.dataSource.getRepository(SeekerEducation);
          await educationRepo.delete({ seeker_id: profileId });

          const newEducations = data.educations.map((edu: any) => ({
            seeker_id: profileId,
            education_id: edu.education_id,
            degree: edu.degree,
            year: edu.year,
            notes: edu.notes,
          }));

          if (newEducations.length > 0) {
            await educationRepo.save(newEducations);
          }
        }

        // 2.4 Update Role Experiences (Blue Collar Multi-Select)
        if (data.roleExperiences && Array.isArray(data.roleExperiences)) {
          const sreRepo = this.dataSource.getRepository(SeekerRoleExperience);
          await sreRepo.delete({ seeker_id: profileId });

          const newRoleExperiences = data.roleExperiences.map((item: any) => ({
            seeker_id: profileId,
            designation_id: item.roleId,
            experience_id: item.experienceId,
            status: 1,
          }));

          if (newRoleExperiences.length > 0) {
            await sreRepo.save(newRoleExperiences);
          }
        }

        // 2.5 Update Skills
        const skillsArray = data.skillsIds ?? data.skills;
        if (skillsArray && Array.isArray(skillsArray)) {
          const skillsRepo = this.dataSource.getRepository(SeekerSkill);
          await skillsRepo.delete({ seeker_id: profileId });
          const newSkills = skillsArray
            .map((skillId: any) => ({
              seeker_id: profileId,
              skill_id: Number(skillId),
            }))
            .filter((s) => !isNaN(s.skill_id));
          if (newSkills.length > 0) {
            await skillsRepo.save(newSkills);
          }
        }

        // 2.6 Update Languages
        const languagesArray = data.languagesIds ?? data.languages;
        if (languagesArray && Array.isArray(languagesArray)) {
          const langRepo = this.dataSource.getRepository(SeekerLanguage);
          await langRepo.delete({ seeker_id: profileId });
          const newLanguages = languagesArray
            .map((langItem: any) => {
              const isObj = typeof langItem === 'object' && langItem !== null;
              const id = isObj ? Number(langItem.id || langItem.language_id) : Number(langItem);
              const englishLevel = isObj && id === 13 ? langItem.english_level || null : null;
              
              if (id === 13 && !englishLevel) {
                throw new BadRequestException('English proficiency level is required when English is selected.');
              }

              return {
                seeker_id: profileId,
                language_id: id,
                english_level: englishLevel,
              };
            })
            .filter((l) => !isNaN(l.language_id));
          if (newLanguages.length > 0) {
            await langRepo.save(newLanguages);
          }
        }
      }
    } else if (user.role === 'recruiter') {
      const companyRepo = this.dataSource.getRepository(Companies);
      const companyUpdates: any = {};

      if (data.companyName !== undefined)
        companyUpdates.name = data.companyName;
      if (data.about !== undefined) companyUpdates.about = data.about;
      if (data.vision !== undefined) companyUpdates.vision = data.vision;
      if (data.website !== undefined) companyUpdates.website = data.website;
      if (data.gstNumber !== undefined)
        companyUpdates.gst_number = data.gstNumber;
      if (data.cinNumber !== undefined)
        companyUpdates.cin_number = data.cinNumber;

      // Map address fields
      if (data.address !== undefined) companyUpdates.location = data.address; // Map 'address' to 'location'
      if (data.location !== undefined) companyUpdates.location = data.location;

      if (data.countryId !== undefined)
        companyUpdates.country_id = Number(data.countryId) || null;
      if (data.stateId !== undefined)
        companyUpdates.state_id = Number(data.stateId) || null;
      if (data.cityId !== undefined)
        companyUpdates.city_id = Number(data.cityId) || null;
      if (data.pincode !== undefined) companyUpdates.pincode = data.pincode;
      if (data.areaId !== undefined)
        companyUpdates.area_id = Number(data.areaId) || null;

      // Map foundation year
      if (data.foundedYear !== undefined) {
        // Create a date object for Jan 1st of the year
        const year = parseInt(data.foundedYear);
        if (!isNaN(year)) {
          companyUpdates.founded_at = new Date(year, 0, 1);
        }
      }
      if (data.foundationDate !== undefined)
        companyUpdates.founded_at = data.foundationDate;

      if (data.companySizeId !== undefined)
        companyUpdates.company_size_id = Number(data.companySizeId) || null;
      if (data.industryId !== undefined)
        companyUpdates.industry_id = Number(data.industryId) || null;
      if (data.organizationTypeId !== undefined)
        companyUpdates.organization_type_id =
          Number(data.organizationTypeId) || null;

      if (Object.keys(companyUpdates).length > 0) {
        const company = await companyRepo.findOne({
          where: { user_id: userId },
        });
        if (company) {
          await companyRepo.update({ user_id: userId }, companyUpdates);
        } else {
          await companyRepo.save({ user_id: userId, ...companyUpdates });
        }
      }
    }

    return { success: true };
  }

  /**
   * Get onboarding data for prefilling
   */
  async getOnboardingData(userId: number) {
    const user = await this.findOne(userId);
    if (!user) return null;

    if (user.role === 'seeker') {
      const profile = await this.dataSource
        .getRepository(SeekerProfiles)
        .findOne({ where: { user_id: userId } });

      // console.log(`[DEBUG] getOnboardingData for user ${userId}:`, {
      //   hasProfile: !!profile,
      //   profileId: profile?.id,
      //   role: user.role
      // });

      let address = null;
      let educations = [];
      let experiences = [];
      let resumes = [];
      let skills = [];
      let certifications = [];
      let social_links = [];

      if (profile) {
        if (profile.profession_id) {
          try {
            const roleData = await this.dataSource.query(
              'SELECT name, category FROM roles WHERE id = ? LIMIT 1',
              [profile.profession_id]
            );
            if (roleData && roleData.length > 0) {
              (profile as any).professional_title = roleData[0].name;
              (profile as any).profession_category = roleData[0].category;
            }
          } catch (error) {
            console.error('Error fetching profession role:', error);
          }
        }

        if (profile.education_id) {
          try {
            const eduData = await this.dataSource.query(
              'SELECT name FROM education_levels WHERE id = ? LIMIT 1',
              [profile.education_id]
            );
            if (eduData && eduData.length > 0) {
              (profile as any).qualification = eduData[0].name;
            }
          } catch (error) {
            console.error('Error fetching education level:', error);
          }
        }

        if (profile.experiences_id) {
          try {
            const expData = await this.dataSource.query(
              'SELECT level, duration FROM experiences WHERE id = ? LIMIT 1',
              [profile.experiences_id]
            );
            if (expData && expData.length > 0) {
              (profile as any).experience = expData[0].level;
              (profile as any).experience_duration = expData[0].duration;
            }
          } catch (error) {
            console.error('Error fetching experience level:', error);
          }
        }

        const rawAddress = await this.dataSource.query(
          `SELECT sa.*, c.country_name as country, s.state_name as state, ci.city_name as city, a.area as area
           FROM seeker_address sa
           LEFT JOIN country c ON c.country_id = sa.permanent_country_id
           LEFT JOIN state s ON s.state_id = sa.permanent_state_id
           LEFT JOIN city ci ON ci.city_id = sa.permanent_city_id
           LEFT JOIN area a ON a.id = sa.permanent_area_id
           WHERE sa.seeker_profile_id = ? LIMIT 1`,
          [profile.id]
        );
        address = rawAddress.length > 0 ? rawAddress[0] : null;

        educations = await this.dataSource
          .getRepository(SeekerEducation)
          .find({ where: { seeker_id: profile.id } });

        certifications = await this.dataSource
          .getRepository(SeekerCertification)
          .find({ where: { seeker_id: profile.id }, order: { created_at: 'DESC' } });

        experiences = await this.dataSource
          .getRepository(SeekerExperience)
          .find({ where: { seeker_id: profile.id } });

        resumes = await this.dataSource.getRepository(SeekerResume).find({
          where: { seeker_id: profile.id },
          order: { uploaded_at: 'DESC' as any },
        });

        // Fetch role experiences for blue-collar applicants
        const roleExperiences = await this.dataSource
          .getRepository(SeekerRoleExperience)
          .find({ where: { seeker_id: profile.id, status: 1 } });

        skills = await this.dataSource.query(
          `SELECT ss.skill_id as id, s.skill_name 
           FROM seeker_skills ss
           JOIN skills s ON s.id = ss.skill_id
           WHERE ss.seeker_id = ?`,
          [profile.id]
        );

        const languages = await this.dataSource.query(
          `SELECT sl.id as seekerLanguage_id, sl.english_level as seekerLanguage_english_level, 
                  l.id as language_id, l.name as language_name
           FROM seeker_language sl
           LEFT JOIN languages l ON l.id = sl.language_id
           WHERE sl.seeker_id = ?`,
          [profile.id]
        );

        social_links = await this.dataSource.query(
          `SELECT sl.id, sl.platform_id, sl.url, sp.name as platform_name, sp.icon_url
           FROM seeker_social_links sl
           LEFT JOIN social_platform sp ON sp.id = sl.platform_id
           WHERE sl.seeker_id = ?`,
          [profile.id]
        );

        return {
          user,
          profile,
          address,
          educations,
          experiences,
          resumes,
          roleExperiences,
          skills,
          languages,
          certifications,
          social_links,
        };
      }

      return { user, profile, address, educations, experiences, resumes, certifications };
    } else if (user.role === 'recruiter') {
      const company = await this.dataSource
        .getRepository(Companies)
        .findOne({ where: { user_id: userId } });
      return { user, company };
    }

    return { user };
  }

  /**
   * Complete onboarding - Create user account after OTP verification
   */
  async completeOnboarding(data: any) {
    const { phone, role, ...formData } = data;
    console.log(
      '👤 [COMPLETE_ONBOARDING] Full Data Payload:',
      JSON.stringify(data, null, 2),
    );
    console.log(
      '👤 [COMPLETE_ONBOARDING] Method called with phone:',
      phone,
      'role:',
      role,
    );

    // Verify registration OTP was verified
    // NEW: Bypass this if userId is provided (meaning user is already authenticated/context is available)
    if (!data.userId) {
      const [otpRecord] = await this.dataSource.query(
        'SELECT * FROM registration_otps WHERE phone_number = ? AND role = ? AND verified = 1',
        [phone, role],
      );

      if (!otpRecord) {
        throw new Error('Phone not verified or verification expired');
      }
    } else {
      console.log(
        '👤 [COMPLETE_ONBOARDING] Bypassing OTP check for existing userId:',
        data.userId,
      );
    }

    // Check if account already exists
    let existingUser = null;
    if (data.userId) {
      existingUser = await this.userRepo.findOne({
        where: { id: data.userId },
      });
    } else {
      existingUser = await this.userRepo.findOne({
        where: { phone, role: role as any },
      });
    }

    let userId: number;
    let username: string;

    if (existingUser) {
      // [MOBILE-FIRST] User exists (created during OTP verification), so UPDATE it
      // Generate proper username
      username = `${role}_${phone}_${Date.now()}`;

      console.log(
        '👤 [COMPLETE_ONBOARDING] Updating existing user. ID:',
        existingUser.id,
        'Email from formData:',
        formData.email,
      );

      await this.dataSource.query(
        `UPDATE users 
         SET full_name = ?, 
             email = ?, 
             username = ?, 
             seeker_type = ?,
             selected_role_category = ?,
             onboarding_status = 'completed',
             onboarding_step = NULL,
             mobile_onboarding_step = NULL,
             updated_at = NOW() 
         WHERE id = ?`,
        [
          formData.fullName,
          formData.email || null,
          username,
          formData.seekerType || null,
          formData.selectedRoleCategory ||
          formData.selected_role_category ||
          null,
          existingUser.id,
        ],
      );

      userId = existingUser.id;
      console.log(
        '👤 [COMPLETE_ONBOARDING] User updated with ID:',
        userId,
        'role:',
        role,
      );
    } else {
      // Fallback: Create new user
      const userResult = await this.dataSource.query(
        `INSERT INTO users (username, full_name, email, phone, password, role, auth_method, phone_verified, status, seeker_type, selected_role_category, onboarding_status, onboarding_step, created_at, updated_at) 
         VALUES (?, ?, ?, ?, NULL, ?, 'otp', 1, 'active', ?, ?, 'completed', NULL, NOW(), NOW())`,
        [
          username,
          formData.fullName,
          formData.email || null,
          phone,
          role,
          formData.seekerType || null,
          formData.selectedRoleCategory ||
          formData.selected_role_category ||
          null,
        ],
      );
      userId = userResult.insertId;
      console.log(
        '👤 [COMPLETE_ONBOARDING] User created with ID:',
        userId,
        'role:',
        role,
      );
    }

    // Create role-specific records
    if (role === 'seeker') {
      // Insert/Update seeker profile with all onboarding data (handled via repository to avoid duplicate key errors)
      const seekerRepo = this.dataSource.getRepository(SeekerProfiles);

      const profileData: any = {
        professional_title: formData.professionalTitle || null,
        education_id: formData.educationId || null,
        date_of_birth: formData.dateOfBirth || null,
        gender: formData.gender || null,
        marital_status: formData.maritalStatus || null,
        your_availability: formData.availability || null,
        bio_graphy: formData.bio || null,
        personal_website: formData.personalWebsite || null,
        skills_ids: formData.skillsIds
          ? typeof formData.skillsIds === 'string'
            ? JSON.parse(formData.skillsIds)
            : formData.skillsIds
          : null,
        languages_ids: formData.languagesIds
          ? typeof formData.languagesIds === 'string'
            ? JSON.parse(formData.languagesIds)
            : formData.languagesIds
          : null,
        preferred_work_city_id: formData.preferredWorkCityId
          ? parseInt(formData.preferredWorkCityId)
          : null,
        preferred_work_locality_area_id: formData.preferredWorkLocalityId
          ? parseInt(formData.preferredWorkLocalityId)
          : null,
      };

      // Role & Experience mapping based on category
      const currentCategory =
        formData.selectedRoleCategory || formData.selected_role_category;
      if (currentCategory === 'white_collar') {
        profileData.experiences_id = formData.experienceId || null;
        profileData.profession_id = formData.professionId || null;
      } else {
        // Blue-collar: save these to seeker_role_experience (handled below)
        profileData.experiences_id = null;
        profileData.profession_id = null;
      }

      const existingProfile = await seekerRepo.findOne({
        where: { user_id: userId },
      });

      let seekerProfileId: number;

      if (existingProfile) {
        await seekerRepo.update({ user_id: userId }, profileData);
        seekerProfileId = existingProfile.id;
      } else {
        const newProfile = await seekerRepo.save({
          user_id: userId,
          ...profileData,
          created_at: new Date(),
          updated_at: new Date(),
        });
        seekerProfileId = newProfile.id;
      }

      // Insert or Update address information if provided
      // Explicitly handle Blue Collar address cleanup
      if (currentCategory === 'blue_collar') {
        console.log(
          '👤 [COMPLETE_ONBOARDING] Blue collar detected. Removing any existing addresses for profile:',
          seekerProfileId,
        );
        await this.dataSource.query(
          'DELETE FROM seeker_address WHERE seeker_profile_id = ?',
          [seekerProfileId],
        );
      } else if (
        formData.permanentAddress ||
        formData.permanentCityId ||
        formData.communicationAddress ||
        formData.communicationCityId ||
        formData.currentAddress ||
        formData.city
      ) {
        await this.seekerAddressService.upsertAddress(
          seekerProfileId,
          formData,
        );
      }

      // Insert or Update Experiences (if provided)
      if (formData.experiences && Array.isArray(formData.experiences)) {
        const experienceRepo = this.dataSource.getRepository(SeekerExperience);
        await experienceRepo.delete({ seeker_id: seekerProfileId });

        const newExperiences = formData.experiences.map((exp: any) => ({
          seeker_id: seekerProfileId,
          company: exp.company,
          department: exp.department,
          designation: exp.designation,
          start_date: exp.startDate,
          end_date: exp.current ? null : exp.endDate,
          current: exp.current ? true : false,
          responsibilities: exp.responsibilities,
        }));

        if (newExperiences.length > 0) {
          await experienceRepo.save(newExperiences);
        }
      }

      // Insert or Update Educations (if provided)
      if (formData.educations && Array.isArray(formData.educations)) {
        const educationRepo = this.dataSource.getRepository(SeekerEducation);
        await educationRepo.delete({ seeker_id: seekerProfileId });

        const newEducations = formData.educations.map((edu: any) => ({
          seeker_id: seekerProfileId,
          education_id: edu.education_id,
          degree: edu.degree,
          year: edu.year,
          notes: edu.notes,
        }));

        if (newEducations.length > 0) {
          await educationRepo.save(newEducations);
        }
      }

      // Insert or Update Role Experiences (Blue Collar Multi-Select)
      if (formData.roleExperiences && Array.isArray(formData.roleExperiences)) {
        const sreRepo = this.dataSource.getRepository(SeekerRoleExperience);
        await sreRepo.delete({ seeker_id: seekerProfileId });

        const newRoleExperiences = formData.roleExperiences.map(
          (item: any) => ({
            seeker_id: seekerProfileId,
            designation_id: item.roleId,
            experience_id: item.experienceId,
          }),
        );

        if (newRoleExperiences.length > 0) {
          await sreRepo.save(newRoleExperiences);
        }
      }
    } else if (role === 'recruiter' && formData.companyName) {
      // Handle logo file if provided
      let logoPath = null;
      if (data.logoFile) {
        try {
          // Process logo using Sharp
          const uploadDir = 'uploads/company-logos';
          await fs.promises.mkdir(uploadDir, { recursive: true });

          const timestamp = Date.now();
          const filename = `${timestamp}-${data.logoFile.originalname}`;
          const outputPath = path.join(uploadDir, filename);

          // Resize and optimize logo (same as RecruiterSettings)
          await sharp(data.logoFile.buffer || data.logoFile.path)
            .resize(68, 68, { fit: 'cover' })
            .jpeg({ quality: 95 })
            .toFile(outputPath);

          logoPath = outputPath.replace(/\\/g, '/');
        } catch (error) {
          console.error('Error processing logo:', error);
          // If processing fails, continue without logo
        }
      }

      // Handle banner file if provided
      let bannerPath = null;
      if (data.bannerFile) {
        try {
          const uploadDir = 'uploads/company-banners';
          await fs.promises.mkdir(uploadDir, { recursive: true });

          const timestamp = Date.now();
          const filename = `${timestamp}-${data.bannerFile.originalname}`;
          const outputPath = path.join(uploadDir, filename);

          // Resize and optimize banner (same as RecruiterSettings)
          await sharp(data.bannerFile.buffer || data.bannerFile.path)
            .resize(1920, 312, { fit: 'cover' })
            .jpeg({ quality: 92 })
            .toFile(outputPath);

          bannerPath = outputPath.replace(/\\/g, '/');
        } catch (error) {
          console.error('Error processing banner:', error);
          // If processing fails, continue without banner
        }
      }

      // Insert/Update company with all onboarding data
      const companyRepo = this.dataSource.getRepository(Companies);

      const companyData = {
        name: formData.companyName,
        user_id: userId,
        gst_number: formData.gstNumber || null,
        cin_number: formData.cinNumber || null,
        about: formData.about || null,
        vision: formData.vision || null,
        founded_at: formData.foundedYear || null,
        location: formData.address || null,
        website: formData.website || null,
        company_size_id: formData.companySizeId || null,
        organization_type_id: formData.organizationTypeId || null,
        industry_id: formData.industryId || null,
        country_id: formData.countryId || null,
        state_id: formData.stateId || null,
        city_id: formData.cityId || null,
        pincode: formData.pincode || null,
        area_id: formData.areaId || null,
        logo: logoPath || null,
        banner_url: bannerPath || null,
        latitude: formData.latitude || null,
        longitude: formData.longitude || null,
        // Status fields usually default to 0/false on creation, keep logic if update needs to preserve them?
        // If updating, we shouldn't reset approval status unless logic requires.
        // For new profile, defaults are used.
      };

      const existingCompany = await companyRepo.findOne({
        where: { user_id: userId },
      });

      if (existingCompany) {
        // Update existing (don't overwrite status/approval unless needed)
        // Ensure logo/banner are updated only if new paths are provided
        const updatePayload: any = { ...companyData };
        if (!logoPath) delete updatePayload.logo;
        if (!bannerPath) delete updatePayload.banner_url;

        await companyRepo.update({ user_id: userId }, updatePayload);
      } else {
        // Create new
        await companyRepo.save({
          ...companyData,
          is_approved: 0,
          status: 0,
          featured: 0,
          is_deleted: 0,
          created_at: new Date(),
          updated_at: new Date(),
        });
      }

      // If logo was uploaded, set it as user's profile picture
      if (logoPath) {
        await this.dataSource.query(
          'UPDATE users SET profile_picture = ? WHERE id = ?',
          [logoPath, userId],
        );
      }
    }
    // Add franchise logic here if needed

    if (formData.email) {
      // Fire and forget: don't wait for the third-party email API to return
      // This prevents the front-end from hanging if Brevo is slow
      this.sendRegistrationEmail({
        email: formData.email,
        fullName: formData.fullName,
        role,
        username,
        companyName: formData.companyName,
        isApproved: role === 'recruiter' ? false : true,
      }).catch((err) => {
        console.error('Error sending onboarding registration email:', err);
      });
    }

    // Delete registration OTP after account creation
    await this.dataSource.query(
      'DELETE FROM registration_otps WHERE phone_number = ? AND role = ?',
      [phone, role],
    );

    // 3️⃣ NEW RECRUITER ONBOARDING CREDITS (Free Tier) - Same as registerUser
    if (role === 'recruiter') {
      console.log(
        '🔍 [ONBOARDING] Starting onboarding credits for recruiter:',
        userId,
      );
      try {
        // Fetch configs for free credits
        const [freeJobPostsConfig] = await this.dataSource.query(
          'SELECT setting_value FROM config_setting WHERE setting_key = "free_job_posts"',
        );
        console.log(
          '🔍 [ONBOARDING] free_job_posts config:',
          freeJobPostsConfig,
        );

        const [freeCVViewsConfig] = await this.dataSource.query(
          'SELECT setting_value FROM config_setting WHERE setting_key = "cv_limit_free"',
        );
        console.log('🔍 [ONBOARDING] cv_limit_free config:', freeCVViewsConfig);

        const freeJobPosts = parseInt(freeJobPostsConfig?.setting_value || '1');
        const freeCVViews = parseInt(freeCVViewsConfig?.setting_value || '5');

        console.log(
          '🔍 [ONBOARDING] Parsed values - freeJobPosts:',
          freeJobPosts,
          'freeCVViews:',
          freeCVViews,
        );

        // Calculate 30-day expiry for registration bonus
        const expiresAt = new Date();
        expiresAt.setDate(expiresAt.getDate() + 30);

        console.log('🔍 [ONBOARDING] Expiry date calculated:', expiresAt);

        // Credit Job Posts (30 Days Expiry)
        if (freeJobPosts > 0) {
          console.log('🔍 [ONBOARDING] Crediting job posts...', freeJobPosts);
          await this.walletService.creditWallet(
            userId,
            'job',
            freeJobPosts,
            'Onboarding Bonus: Free Job Post',
            'onboarding',
            userId,
            null,
            expiresAt,
          );
          console.log('✅ [ONBOARDING] Job posts credited successfully');
        } else {
          console.log('⚠️ [ONBOARDING] freeJobPosts is 0, skipping job credit');
        }

        // Credit CV Views (30 Days Expiry)
        if (freeCVViews > 0) {
          console.log('🔍 [ONBOARDING] Crediting CV views...', freeCVViews);
          await this.walletService.creditWallet(
            userId,
            'database',
            freeCVViews,
            'Onboarding Bonus: Free Database Credits',
            'onboarding',
            userId,
            null,
            expiresAt,
          );
          console.log('✅ [ONBOARDING] CV views credited successfully');
        } else {
          console.log(
            '⚠️ [ONBOARDING] freeCVViews is 0, skipping database credit',
          );
        }

        console.log(
          '✅ [ONBOARDING] All onboarding credits added successfully',
        );
      } catch (creditError) {
        console.error(
          '❌ [ONBOARDING] Failed to add onboarding credits:',
          creditError,
        );
        console.error(
          '❌ [ONBOARDING] Error details:',
          creditError.message,
          creditError.stack,
        );
        // Don't throw - user is already registered, this is non-critical bonus
      }
    } else {
      console.log(
        '⏭️ [ONBOARDING] Role is not recruiter:',
        role,
        '- skipping onboarding credits',
      );
    }

    // Mark onboarding as completed
    await this.dataSource.query(
      `UPDATE users 
       SET onboarding_status = 'completed', 
           onboarding_step = NULL,
           updated_at = NOW()
       WHERE id = ?`,
      [userId],
    );

    // Return created user
    const savedUser = await this.userRepo.findOne({ where: { id: userId } });
    console.log(
      '✅ [COMPLETE_ONBOARDING] User onboarding completed successfully. userId:',
      userId,
    );

    if (savedUser && savedUser.role === 'recruiter' && savedUser.email) {
      this.emailService.sendTemplateEmail(
        'recruiter_pending_approval',
        savedUser.email,
        savedUser.fullName || 'Recruiter',
        { preferred_name: savedUser.fullName || 'Recruiter' }
      ).catch(err => console.error('Failed to send recruiter pending approval email:', err));
    }

    return savedUser;
  }

  /**
   * Mark onboarding as completed
   */
  async markOnboardingComplete(userId: number) {
    const user = await this.userRepo.findOne({ where: { id: userId } });
    await this.dataSource.query(
      `UPDATE users 
       SET onboarding_status = 'completed', 
           onboarding_step = NULL,
           updated_at = NOW()
       WHERE id = ?`,
      [userId],
    );
    if (user && user.role === 'recruiter' && user.email) {
      this.emailService.sendTemplateEmail(
        'recruiter_pending_approval',
        user.email,
        user.fullName || 'Recruiter',
        { preferred_name: user.fullName || 'Recruiter' }
      ).catch(err => console.error('Failed to send recruiter pending approval email:', err));
    }
    return { success: true };
  }
  // Admin: Register Recruiter and Link to Seed Company
  async registerRecruiterAndLinkCompany(data: any) {
    const {
      fullName,
      email,
      phone,
      companyId,
      gstNumber,
      cinNumber,
      sendWelcomeEmail,
    } = data;

    // 1. Validate GST or CIN
    if (!gstNumber && !cinNumber) {
      throw new Error('At least one of GST Number or CIN Number is required');
    }

    // 2. Check email unique
    const existingUser = await this.dataSource.query(
      'SELECT id FROM users WHERE email = ? LIMIT 1',
      [email],
    );
    if (existingUser.length > 0) {
      throw new Error('Email already registered');
    }

    // 3. Verify seed company
    const companyCheck = await this.dataSource.query(
      'SELECT id, is_admin_company, user_id, name FROM companies WHERE id = ? LIMIT 1',
      [companyId],
    );

    if (companyCheck.length === 0) {
      throw new Error(`Company with ID ${companyId} not found`);
    }

    const company = companyCheck[0];

    if (company.is_admin_company !== 1) {
      throw new Error(
        `Company "${company.name}" is not a seed/JV company (is_admin_company = ${company.is_admin_company})`,
      );
    }

    if (company.user_id !== null) {
      throw new Error(
        `Company "${company.name}" already has an owner (user_id = ${company.user_id})`,
      );
    }

    // 4. Create recruiter user
    const username = `recruiter_${phone}_${Date.now()}`;
    const userResult = await this.dataSource.query(
      `INSERT INTO users (username, full_name, email, phone, role, 
       auth_method, phone_verified, status, onboarding_status, created_at, updated_at) 
       VALUES (?, ?, ?, ?, 'recruiter', 'otp', 1, 'active', 'completed', NOW(), NOW())`,
      [username, fullName, email, phone],
    );
    const userId = userResult.insertId;

    // 5. Link to company (Direct update to allow correcting prefilled data)
    await this.dataSource.query(
      `UPDATE companies SET 
       user_id = ?, 
       is_admin_company = 0, 
       gst_number = ?, 
       cin_number = ?,
       is_approved = 1,
       status = 1,
       updated_at = NOW()
       WHERE id = ?`,
      [userId, gstNumber || null, cinNumber || null, companyId],
    );

    // 6. Send welcome email
    if (sendWelcomeEmail) {
      await this.sendRecruiterWelcomeEmail(email, fullName, company.name);
    }

    return {
      success: true,
      userId,
      companyId,
      message: 'Recruiter registered and linked successfully',
    };
  }

  private async sendRecruiterWelcomeEmail(
    email: string,
    fullName: string,
    companyName: string,
  ) {
    try {
      const templateName = 'recruiter_welcome_linked';
      const toName = fullName;
      const context = {
        full_name: fullName,
        company_name: companyName,
      };

      await this.emailService.sendTemplateEmail(
        templateName,
        email,
        toName,
        context
      );
    } catch (error) {
      console.error('Error sending welcome email:', error);
    }
  }
}
