import {
  Injectable,
  Logger,
  BadRequestException,
  InternalServerErrorException,
  NotFoundException,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import * as fs from 'fs';
import * as path from 'path';
import { Response } from 'express';

import { DashboardService } from '../../../modules/dashboard/dashboard.service';
import { UserService } from '../../../modules/user/user.service';
import { SeekerPreferencesService } from '../../../modules/seeker_preferences/seeker_preferences.service';
import { AreaService } from '../../../modules/area/area.service';
import { RolesService } from '../../../modules/roles/roles.service';
import { SeekerAddressService } from '../../../modules/seeker_address/seeker_address.service';

@Injectable()
export class MobileSeekerProfileSettingsService {
  private readonly logger = new Logger(MobileSeekerProfileSettingsService.name);

  constructor(
    private readonly dashboardService: DashboardService,
    private readonly userService: UserService,
    private readonly seekerPreferencesService: SeekerPreferencesService,
    private readonly areaService: AreaService,
    private readonly rolesService: RolesService,
    private readonly seekerAddressService: SeekerAddressService,
    @InjectDataSource() private readonly dataSource: DataSource,
  ) { }

  async getProfileSettings(userId: number) {
    if (!userId || userId <= 0) {
      throw new BadRequestException('Valid userId is required');
    }

    this.logger.log(`Mobile seeker profile request - userId: ${userId}`);

    const profile = {
      user: null as any,
      seeker_profile: null as any,
      experiences: [] as any[],
      educations: [] as any[],
      skills: [] as any[],
      languages: [] as any[],
      resumes: [] as any[],
      social_links: [] as any[],
      address: null as any,
      contact_info: null as any,
      job_preferences: {
        preference_id: null,
        roles: [],
        skills: [],
      } as any,
    };

    let seekerId: number | null = null;
    let seekerProfileId: number | null = null;

    /*
     * ---------------------------------------------------------
     * USER VALIDATION
     * ---------------------------------------------------------
     */
    try {
      const user = await this.userService.findOne(userId);

      if (!user) {
        this.logger.warn(`User not found for userId ${userId}`);

        return {
          success: false,
          message: 'User not found',
          profile: null,
        };
      }

      profile.user = user;
    } catch (error) {
      this.logger.error(
        `Failed to fetch user for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );

      throw new InternalServerErrorException('Failed to fetch user profile');
    }

    /*
     * ---------------------------------------------------------
     * SEEKER PROFILE
     * ---------------------------------------------------------
     */
    try {
      const seekerProfileData =
        await this.dashboardService.getSeekerProfile(userId);

      if (seekerProfileData?.success && seekerProfileData?.profile) {
        profile.seeker_profile = seekerProfileData.profile;
        await this.enrichPreferredWorkLocation(profile.seeker_profile);

        // IMPORTANT:
        // - seekerId is used to load experiences/educations/skills/languages/social links.
        // - seekerProfileId is used to load/save seeker address and seeker_profile-specific info.
        // These are usually different DB identifiers.
        //
        // DashboardService.getSeekerProfile(userId) returns a seeker_profile object.
        // We treat:
        //   - seeker_profile.id        => seekerProfileId
        //   - seeker_profile.seeker_id (or seekerId) => seekerId
        seekerProfileId = seekerProfileData.profile.id;
        seekerId =
          // TS-safe access: returned seeker_profile shape differs across queries.
          // eslint-disable-next-line @typescript-eslint/no-explicit-any
          (seekerProfileData.profile as any).seeker_id ??
          // eslint-disable-next-line @typescript-eslint/no-explicit-any
          (seekerProfileData.profile as any).seekerId ??
          null;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch seeker profile for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }

    /*
     * ---------------------------------------------------------
     * SEEKER ID FALLBACK
     * ---------------------------------------------------------
     */
    if (!seekerId) {
      try {
        const seekerIdData =
          await this.dashboardService.getSeekerIdByUserId(userId);

        if (seekerIdData) {
          seekerId = seekerIdData;
        }
      } catch (error) {
        this.logger.warn(`Could not fetch seekerId for userId ${userId}`);
      }
    }

    /*
     * ---------------------------------------------------------
     * SEEKER RELATED DATA
     * ---------------------------------------------------------
     */
    if (seekerId) {
      await this.loadExperiences(profile, seekerId);
      await this.loadEducations(profile, seekerId);
      await this.loadSkills(profile, seekerId);
      await this.loadLanguages(profile, seekerId);
      await this.loadSocialLinks(profile, seekerId);
    }

    if (seekerProfileId) {
      await this.loadAddress(profile, seekerProfileId);
    }

    /*
     * ---------------------------------------------------------
     * USER RELATED DATA
     * ---------------------------------------------------------
     */
    await this.loadResumes(profile, userId);
    await this.loadContactInfo(profile, userId);
    await this.loadJobPreferences(profile, userId);

    const [completionData, fullProfileData] = await Promise.all([
      this.loadProfileCompletion(userId),
      this.loadFullProfile(userId),
    ]);

    return {
      success: true,
      profile,
      editable_profile: await this.serializeEditableProfile(
        profile,
        completionData,
        fullProfileData,
      ),
    };
  }

  async getEditableProfile(userId: number) {
    const settings = await this.getProfileSettings(userId);

    if (!settings?.success) {
      return settings;
    }

    return {
      success: true,
      editable_profile: settings.editable_profile,
    };
  }

  private async getLightweightFullUser(userId: number) {
    try {
      const rows = await this.dataSource.query(
        `SELECT 
          u.full_name, 
          u.email, 
          u.phone, 
          u.profile_picture, 
          u.selected_role_category,
          COALESCE(GROUP_CONCAT(DISTINCT sr.name SEPARATOR ", "), r.name) as profession_name
         FROM users u
         LEFT JOIN seeker_profile sp ON sp.user_id = u.id
         LEFT JOIN roles r ON r.id = sp.profession_id
         LEFT JOIN seeker_role_experience sre ON sre.seeker_id = sp.id AND sre.status = 1
         LEFT JOIN roles sr ON sr.id = sre.designation_id
         WHERE u.id = ?
         GROUP BY u.id`,
        [userId]
      );
      return rows[0] ? { ...rows[0], user: rows[0] } : null;
    } catch (err) {
      this.logger.warn(`Failed to load lightweight full user for ${userId}: ${err.message}`);
      return null;
    }
  }

  async getBasicDetailsEditForm(userId: number) {
    if (!userId || userId <= 0) {
      throw new BadRequestException('Valid userId is required');
    }

    const profile: any = {};

    // Fetch required data for basic details concurrently to optimize load time
    const [user, seekerProfileData, lightweightFullUser, contactInfoData] = await Promise.all([
      this.userService.findOne(userId).catch((err) => {
        this.logger.error(`Failed to fetch user in basic details edit: ${err.message}`);
        return null;
      }),
      this.dashboardService.getSeekerProfile(userId).catch((err) => {
        this.logger.error(`Failed to fetch seeker profile in basic details edit: ${err.message}`);
        return null;
      }),
      this.getLightweightFullUser(userId),
      this.dashboardService.getUserContactInfo(userId).catch((err) => {
        this.logger.error(`Failed to fetch contact info in basic details edit: ${err.message}`);
        return null;
      }),
    ]);

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

    profile.user = user;
    
    if (contactInfoData?.success && contactInfoData?.user) {
      profile.contact_info = contactInfoData.user;
    }

    let seekerProfileId: number | null = null;
    if (seekerProfileData?.success && seekerProfileData?.profile) {
      profile.seeker_profile = seekerProfileData.profile;
      await this.enrichPreferredWorkLocation(profile.seeker_profile);
      seekerProfileId = seekerProfileData.profile.id;
    }

    if (seekerProfileId) {
      await this.loadAddress(profile, seekerProfileId);
    }

    const fullUser = lightweightFullUser?.user || {};
    const isBlueCollar = await this.isBlueCollar(profile, lightweightFullUser);

    return {
      success: true,
      basic_details_form: this.buildBasicDetailsEditForm(
        profile,
        profile.seeker_profile || {},
        fullUser,
        isBlueCollar,
      ),
    };
  }

  async updateBasicDetails(body: any) {
    const userId = this.requirePositiveNumber(body.userId, 'userId');
    const profileId = await this.requireSeekerProfileId(userId);
    const isBlueCollar = await this.resolveIsBlueCollarForUpdate(userId, body);

    const addressPayload = isBlueCollar
      ? {}
      : await this.enrichCurrentAddressFromPincode(body);
    const preferredWorkPayload = isBlueCollar
      ? this.buildPreferredWorkLocationPayload(body)
      : {};

    let professionalTitle = body.professionalTitle;
    const professionId = body.professionId ?? body.profession_id;

    if (professionId && (!professionalTitle || !isNaN(Number(professionalTitle)))) {
      try {
        const role = await this.rolesService.findOne(Number(professionId));
        if (role) {
          professionalTitle = role.name;
        }
      } catch (err) {
        this.logger.warn(`Failed to retrieve role name for professionId: ${professionId}`);
      }
    }

    const payload = {
      fullName: body.fullName,
      phone: body.phone,
      ...(isBlueCollar ? {} : { email: body.email }),
      professional_title: isBlueCollar ? undefined : professionalTitle,
      date_of_birth: body.dateOfBirth,
      gender: body.gender,
      marital_status: body.maritalStatus,
      education_id: body.educationId,
      experiences_id: body.experiencesId,
      profession_id: professionId,
      availability: body.availability,
      biography: body.biography,
      personal_website: body.personalWebsite,
      selected_role_category: body.selectedRoleCategory || body.selected_role_category,
      ...(isBlueCollar ? preferredWorkPayload : addressPayload),
    };

    const result = await this.dashboardService.updateSeekerProfileBasicInfo(
      profileId,
      this.compactObject(payload),
    );

    if (body.profilePicture !== undefined) {
      const oldUser = await this.userService.findOne(userId);
      const newProfilePicture = body.profilePicture || null;
      if (oldUser && oldUser.profilePicture && oldUser.profilePicture !== newProfilePicture) {
        try {
          const oldFilePath = path.join(process.cwd(), oldUser.profilePicture);
          if (fs.existsSync(oldFilePath)) {
            fs.unlinkSync(oldFilePath);
          }
        } catch (err) {
          this.logger.warn(`Failed to delete old profile picture file: ${oldUser.profilePicture}`);
        }
      }
      await this.userService.update(userId, { profilePicture: newProfilePicture });
    }

    if (!isBlueCollar && Object.keys(addressPayload).length > 0) {
      const existingAddress =
        await this.dashboardService.getSeekerAddress(profileId);
      await this.seekerAddressService.upsertAddress(profileId, {
        ...this.preservePermanentAddressFields(existingAddress?.address),
        ...addressPayload,
        sameAsPermanent: false,
      });
    }

    return {
      success: result?.success !== false,
      message: result?.message || 'Profile basic details updated',
      result,
    };
  }

  async updateSkills(body: any) {
    const userId = this.requirePositiveNumber(body.userId, 'userId');
    const profileId = await this.requireSeekerProfileId(userId);
    const skills = this.parseEditableArray(body.skills ?? body.skillIds);

    const result = await this.dashboardService.updateSeekerProfileBasicInfo(
      profileId,
      { skills },
    );

    return {
      success: result?.success !== false,
      message: 'Skills updated',
      skills,
    };
  }

  async updateLanguages(body: any) {
    const userId = this.requirePositiveNumber(body.userId, 'userId');
    const profileId = await this.requireSeekerProfileId(userId);
    const languages = this.parseEditableArray(
      body.languages ?? body.languageIds,
    );

    const result = await this.dashboardService.updateSeekerProfileBasicInfo(
      profileId,
      { languages },
    );

    return {
      success: result?.success !== false,
      message: 'Languages updated',
      languages,
    };
  }

  async uploadResume(userId: number, file: Express.Multer.File, name?: string) {
    if (!file) {
      throw new BadRequestException('Resume file is required');
    }

    const validUserId = this.requirePositiveNumber(userId, 'userId');
    const seekerId = await this.requireSeekerProfileId(validUserId);
    const result = await this.dashboardService.uploadResume(
      seekerId,
      file,
      name,
    );

    return {
      success: result?.success !== false,
      message: (result as any)?.message || 'Resume uploaded',
      result,
    };
  }

  async deleteResume(userId: number, resumeId: number) {
    this.requirePositiveNumber(userId, 'userId');
    this.requirePositiveNumber(resumeId, 'resumeId');
    return this.dashboardService.softDeleteSeekerResume(userId, resumeId);
  }

  async getResumes(userId: number) {
    this.requirePositiveNumber(userId, 'userId');
    return this.dashboardService.getSeekerResumesByUserId(userId);
  }

  async updateResume(
    userId: number,
    resumeId: number,
    file?: Express.Multer.File,
    name?: string,
  ) {
    this.requirePositiveNumber(userId, 'userId');
    this.requirePositiveNumber(resumeId, 'resumeId');

    const seekerId = await this.requireSeekerProfileId(userId);

    const resumes = await this.dataSource.query(
      `SELECT * FROM seeker_resumes WHERE id = ? AND seeker_id = ? AND is_deleted = 0 LIMIT 1`,
      [resumeId, seekerId],
    );

    if (!resumes || resumes.length === 0) {
      throw new BadRequestException('Resume not found or does not belong to this user');
    }

    const resumeEntity = resumes[0];
    const updates: Record<string, any> = {};

    if (name) {
      updates.name = name;
    }

    if (file) {
      updates.file_url = file.path;
      updates.file_name = file.originalname;
      if (!name) {
        updates.name = file.originalname;
      }

      if (resumeEntity.file_url) {
        try {
          const path = require('path');
          const fs = require('fs');
          const oldFilePath = path.join(process.cwd(), resumeEntity.file_url);
          if (fs.existsSync(oldFilePath)) {
            fs.unlinkSync(oldFilePath);
          }
        } catch (err) {
          this.logger.warn(`Failed to delete old resume file: ${resumeEntity.file_url}`);
        }
      }
    }

    if (Object.keys(updates).length > 0) {
      const setClauses = Object.keys(updates)
        .map((key) => `${key} = ?`)
        .join(', ');
      const values = [...Object.values(updates), resumeId];

      await this.dataSource.query(
        `UPDATE seeker_resumes SET ${setClauses} WHERE id = ?`,
        values,
      );
    }

    const updated = await this.dataSource.query(
      `SELECT * FROM seeker_resumes WHERE id = ? LIMIT 1`,
      [resumeId],
    );

    return {
      success: true,
      message: 'Resume updated successfully',
      resume: updated?.[0] || null,
    };
  }

  async addWorkExperience(body: any) {
    const seekerId = await this.resolveSeekerIdFromBody(body);
    const experience = await this.dashboardService.addSeekerExperience({
      ...body,
      seeker_id: seekerId,
    });

    return {
      success: true,
      message: 'Work experience added',
      experience,
    };
  }

  async updateWorkExperience(id: number, body: any) {
    this.requirePositiveNumber(id, 'experienceId');
    const experience = await this.dashboardService.updateSeekerExperience(
      id,
      body,
    );

    return {
      success: true,
      message: 'Work experience updated',
      experience,
    };
  }

  async deleteWorkExperience(id: number) {
    this.requirePositiveNumber(id, 'experienceId');
    await this.dashboardService.deleteSeekerExperience(id);
    return { success: true, message: 'Work experience deleted' };
  }

  async getRolesData(userId?: number) {
    const roles = await this.dataSource.query(
      `SELECT id, name FROM roles WHERE category = 'blue_collar' AND status = 1 ORDER BY name ASC`
    );
    const experiences = await this.dataSource.query(
      `SELECT id, level as name, duration FROM experiences WHERE status = 1`
    );

    let selectedRoles = [];
    if (userId) {
      try {
        const seekerId = await this.resolveSeekerIdFromBody({ userId });
        selectedRoles = await this.dataSource.query(
          `SELECT sre.id, sre.designation_id as roleId, sre.experience_id as experienceId, r.name as role_name, e.level as experience_level, e.duration as experience_duration
           FROM seeker_role_experience sre
           LEFT JOIN roles r ON r.id = sre.designation_id
           LEFT JOIN experiences e ON e.id = sre.experience_id
           WHERE sre.seeker_id = ?`,
          [seekerId]
        );
      } catch (error) {
        // If user not found, just return empty selected roles
      }
    }

    return {
      success: true,
      data: {
        roles,
        experiences,
        selectedRoles,
      }
    };
  }

  async bulkUpdateRoleExperiences(body: any) {
    const seekerId = await this.resolveSeekerIdFromBody(body);
    const roleExperiences = body.roleExperiences;

    if (!Array.isArray(roleExperiences)) {
      throw new BadRequestException('roleExperiences must be an array');
    }

    if (roleExperiences.length > 4) {
      throw new BadRequestException('Maximum 4 role experiences allowed');
    }

    // Fetch existing records for this user
    const existingRecords = await this.dataSource.query(
      `SELECT id FROM seeker_role_experience WHERE seeker_id = ? ORDER BY id ASC`,
      [seekerId],
    );

    // We will batch all updates, inserts, and deletes into single queries
    const upsertValues: any[] = [];
    const upsertPlaceholders: string[] = [];
    
    const insertValues: any[] = [];
    const insertPlaceholders: string[] = [];
    
    const deleteIds: number[] = [];

    for (let i = 0; i < Math.max(roleExperiences.length, existingRecords.length); i++) {
      const newRole = roleExperiences[i];
      const existingRow = existingRecords[i];

      if (newRole && existingRow) {
        upsertPlaceholders.push(`(?, ?, ?, ?, 1)`);
        upsertValues.push(existingRow.id, seekerId, newRole.roleId, newRole.experienceId);
      } else if (newRole && !existingRow) {
        insertPlaceholders.push(`(?, ?, ?, 1)`);
        insertValues.push(seekerId, newRole.roleId, newRole.experienceId);
      } else if (!newRole && existingRow) {
        deleteIds.push(existingRow.id);
      }
    }

    if (upsertPlaceholders.length > 0) {
      await this.dataSource.query(
        `INSERT INTO seeker_role_experience (id, seeker_id, designation_id, experience_id, status) VALUES ${upsertPlaceholders.join(', ')} ON DUPLICATE KEY UPDATE designation_id = VALUES(designation_id), experience_id = VALUES(experience_id)`,
        upsertValues
      );
    }

    if (insertPlaceholders.length > 0) {
      await this.dataSource.query(
        `INSERT INTO seeker_role_experience (seeker_id, designation_id, experience_id, status) VALUES ${insertPlaceholders.join(', ')}`,
        insertValues
      );
    }

    if (deleteIds.length > 0) {
      const placeholders = deleteIds.map(() => '?').join(', ');
      await this.dataSource.query(
        `DELETE FROM seeker_role_experience WHERE id IN (${placeholders})`,
        deleteIds
      );
    }

    return {
      success: true,
      message: 'Role experiences successfully saved',
    };
  }

  async addEducation(body: any) {
    const seekerId = await this.resolveSeekerIdFromBody(body);
    const education = await this.dashboardService.addSeekerEducation({
      ...body,
      seeker_id: seekerId,
      education_level_id:
        body.educationLevelId ?? body.education_level_id ?? body.education_id,
    });

    return {
      success: true,
      message: 'Education added',
      education,
    };
  }

  async updateEducation(id: number, body: any) {
    this.requirePositiveNumber(id, 'educationId');
    const education = await this.dashboardService.updateSeekerEducation(id, {
      ...body,
      education_level_id:
        body.educationLevelId ?? body.education_level_id ?? body.education_id,
    });

    return {
      success: true,
      message: 'Education updated',
      education,
    };
  }

  async deleteEducation(id: number) {
    this.requirePositiveNumber(id, 'educationId');
    await this.dashboardService.deleteSeekerEducation(id);
    return { success: true, message: 'Education deleted' };
  }

  async getPublicProfile(userId: number) {
    if (!userId || userId <= 0) {
      throw new BadRequestException('Valid userId is required');
    }

    this.logger.log(`Mobile public seeker profile request - userId: ${userId}`);

    const fullProfile =
      await this.dashboardService.getSeekerFullProfile(userId);

    if (!fullProfile?.success) {
      return fullProfile;
    }

    return {
      success: true,
      profile: this.serializePublicProfile(fullProfile),
    };
  }

  private serializePublicProfile(fullProfile: any) {
    const profile = { ...(fullProfile.profile || {}) };

    delete profile.date_of_birth;
    delete profile.gender;
    delete profile.marital_status;

    return {
      user: {
        name: fullProfile.user?.name || null,
        full_name: fullProfile.user?.full_name || null,
        profile_picture: fullProfile.user?.profile_picture || null,
        selected_role_category:
          fullProfile.user?.selected_role_category || null,
        experience_name: fullProfile.user?.experience_name || null,
        education_name: fullProfile.user?.education_name || null,
        profession_name: fullProfile.user?.profession_name || null,
      },
      seeker_profile: profile,
      location: this.serializePublicLocation(fullProfile.address),
      education: fullProfile.education || [],
      experience: fullProfile.experience || [],
      skills: fullProfile.skills || [],
      languages: fullProfile.languages || [],
      social_links: fullProfile.socialLinks || [],
      role_experiences: fullProfile.roleExperiences || [],
    };
  }

  private serializePublicLocation(address: any) {
    if (!address) {
      return null;
    }

    return {
      permanent_area_name: address.permanent_area_name || null,
      permanent_city_name: address.permanent_city_name || null,
      permanent_state_name: address.permanent_state_name || null,
      permanent_country_name: address.permanent_country_name || null,
      communication_area_name: address.communication_area_name || null,
      communication_city_name: address.communication_city_name || null,
      communication_state_name: address.communication_state_name || null,
      communication_country_name: address.communication_country_name || null,
    };
  }

  private async serializeEditableProfile(
    profile: any,
    completionData: any,
    fullProfileData: any,
  ) {
    const user = profile.contact_info || profile.user || {};
    const seekerProfile = profile.seeker_profile || {};
    const fullUser = fullProfileData?.user || {};
    const latestResume = this.getLatestResume(profile.resumes);
    const isBlueCollar = await this.isBlueCollar(profile, fullProfileData);

    let resolvedProfessionalTitle =
      seekerProfile.professional_title ||
      fullUser.profession_name ||
      fullUser.professional_title ||
      null;

    // Resolve name from profession_id if it's numeric/empty or for both categories
    // If user is white collar and has a custom professional title, do not overwrite it with role name from profession_id
    const hasCustomTitle = seekerProfile.professional_title && isNaN(Number(seekerProfile.professional_title));
    const profIdToFetch = (!isBlueCollar && hasCustomTitle)
      ? null
      : (seekerProfile.profession_id || (!isNaN(Number(resolvedProfessionalTitle)) && resolvedProfessionalTitle !== null && resolvedProfessionalTitle.trim() !== '' ? Number(resolvedProfessionalTitle) : null));

    if (profIdToFetch) {
      try {
        const roleData = await this.dataSource.query('SELECT name FROM roles WHERE id = ?', [profIdToFetch]);
        if (roleData && roleData.length > 0) {
          resolvedProfessionalTitle = roleData[0].name;
        }
      } catch (e) {
        // Ignore error and fallback to whatever it was
      }
    }

    if (isBlueCollar && resolvedProfessionalTitle && resolvedProfessionalTitle.includes(',')) {
      resolvedProfessionalTitle = resolvedProfessionalTitle.split(',')[0].trim();
    }

    return {
      header: {
        user_id: user.id || seekerProfile.user_id || null,
        seeker_profile_id: seekerProfile.id || null,
        full_name:
          user.fullName || user.full_name || fullUser.full_name || null,
        professional_title: resolvedProfessionalTitle,
        profile_picture:
          user.profilePicture ||
          user.profile_picture ||
          fullUser.profile_picture ||
          null,
        location: this.buildProfileLocation(
          profile.address,
          seekerProfile,
          isBlueCollar,
        ),
        completion_percentage: completionData?.completion || 0,
        missing_fields: completionData?.missingFields || [],
      },
      basic_details: this.buildBasicDetailsView(
        profile,
        seekerProfile,
        fullUser,
        isBlueCollar,
      ),

      // Requested screen keys
      resume: latestResume,
      key_skills: (profile.skills || []).map((skill: any) => ({
        id: skill.skill_id || skill.id || null,
        seeker_skill_id: skill.id || null,
        name: skill.skill_name || skill.name || null,
        proficiency_percentage: skill.proficiency_percentage || null,
      })),
      languages: (profile.languages || []).map((language: any) => ({
        id: language.language_id || language.id || null,
        seeker_language_id: language.id || null,
        name: language.language_name || language.name || null,
      })),
      work_experiences: isBlueCollar
        ? this.buildBlueCollarWorkExperiences(profile.experiences)
        : this.buildWhiteCollarWorkExperiences(profile.experiences),
      ...(isBlueCollar && {
        role_experiences: this.buildSeekerRoleExperiences(
          fullProfileData?.roleExperiences || [],
        ),
      }),
      education: (profile.educations || []).map((education: any) => ({
        id: education.id,
        institute_name:
          education.institute_name ||
          education.college_name ||
          education.institution ||
          null,
        degree: education.degree || null,
        degree_id: education.degree_id || null,
        specialization:
          education.specialization ||
          education.specilization ||
          education.specilizations ||
          null,
        specialization_id: education.specialization_id || null,
        month: education.month || null,
        year: education.year || education.passing_year || null,
      })),

      section_state: {
        has_resume: Boolean(latestResume),
        has_skills: (profile.skills || []).length > 0,
        has_languages: (profile.languages || []).length > 0,
        has_work_experience: (profile.experiences || []).length > 0,
        has_education: (profile.educations || []).length > 0,
      },
    };
  }

  private buildBasicDetailsView(
    profile: any,
    seekerProfile: any,
    fullUser: any,
    isBlueCollar: boolean,
  ) {
    const qualificationName =
      fullUser.education_name ||
      profile.educations?.[0]?.degree ||
      profile.educations?.[0]?.notes ||
      null;

    const experienceName =
      fullUser.experience_name || profile.experiences?.[0]?.designation || null;

    const view: Record<string, any> = {
      profilePicture:
        profile.contact_info?.profilePicture ||
        profile.contact_info?.profile_picture ||
        profile.user?.profilePicture ||
        profile.user?.profile_picture ||
        fullUser.profile_picture ||
        null,
      phone: profile.contact_info?.phone || profile.user?.phone || null,
      birthday: this.formatDisplayDate(seekerProfile.date_of_birth),
      gender: seekerProfile.gender || null,
      qualification: {
        id: seekerProfile.education_id || null,
        name: qualificationName,
      },
      experience: {
        id: seekerProfile.experiences_id || null,
        name: experienceName,
      },
    };

    if (!isBlueCollar) {
      view.email =
        profile.contact_info?.email ||
        profile.user?.email ||
        fullUser.email ||
        null;
    }

    return view;
  }

  private buildBasicDetailsEditForm(
    profile: any,
    seekerProfile: any,
    fullUser: any,
    isBlueCollar: boolean,
  ) {
    const fullName =
      profile.contact_info?.fullName ||
      profile.contact_info?.full_name ||
      profile.user?.full_name ||
      profile.user?.fullName ||
      fullUser.full_name ||
      null;

    const selectedRoleCategory =
      profile.user?.selected_role_category ||
      profile.user?.selectedRoleCategory ||
      fullUser.selected_role_category ||
      fullUser.selectedRoleCategory ||
      null;

    return {
      fullName,
      professionalTitle: (() => {
        const rawTitle =
          seekerProfile.professional_title ||
          fullUser.profession_name ||
          fullUser.professional_title ||
          null;
        if (isBlueCollar && rawTitle && rawTitle.includes(',')) {
          return rawTitle.split(',')[0].trim();
        }
        return rawTitle;
      })(),
      profilePicture:
        profile.contact_info?.profilePicture ||
        profile.contact_info?.profile_picture ||
        profile.user?.profilePicture ||
        profile.user?.profile_picture ||
        fullUser.profile_picture ||
        null,
      phone: profile.contact_info?.phone || profile.user?.phone || null,
      ...(isBlueCollar
        ? {}
        : {
          email:
            profile.contact_info?.email ||
            profile.user?.email ||
            fullUser.email ||
            null,
        }),
      dateOfBirth: this.formatDate(seekerProfile.date_of_birth),
      gender: seekerProfile.gender || null,
      educationId: seekerProfile.education_id || null,
      experiencesId: seekerProfile.experiences_id || null,
      selectedRoleCategory,
      ...this.buildUniversalAddressFields(profile.address, seekerProfile, isBlueCollar),
    };
  }

  /**
   * Address/location fields for basic details edit.
   * White collar: full address form. Blue collar: preferred city only.
   */
  private buildUniversalAddressFields(
    address: any,
    seekerProfile: any,
    isBlueCollar: boolean,
  ) {
    if (isBlueCollar) {
      return {
        preferredWorkCityId: seekerProfile.preferred_work_city_id || null,
        preferredWorkCityName: seekerProfile.preferred_city_name || null,
        preferredWorkStateName: seekerProfile.preferred_state_name || null,
        preferredWorkLocalityId: seekerProfile.preferred_work_locality_area_id || null,
        preferredWorkLocalityName: seekerProfile.preferred_area_name || null,
      };
    }

    const current = this.buildCurrentAddress(address);

    return {
      currentAddress: current?.address ?? null,
      pincode: current?.pincode ?? null,
      areaId: current?.area_id ?? null,
      areaName: current?.area_name ?? null,
      cityId: current?.city_id ?? null,
      cityName: current?.city_name ?? null,
      stateId: current?.state_id ?? null,
      stateName: current?.state_name ?? null,
      countryId: current?.country_id ?? null,
      countryName: current?.country_name ?? null,
    };
  }

  private buildPreferredWorkLocationPayload(body: any) {
    const hasCityId =
      body.cityId !== undefined ||
      body.city_id !== undefined ||
      body.preferredWorkCityId !== undefined ||
      body.preferred_work_city_id !== undefined;

    if (!hasCityId) {
      return {};
    }

    const cityId = this.getValue(
      body.preferredWorkCityId,
      body.preferred_work_city_id,
      body.cityId,
      body.city_id,
    );
    const parsedCityId =
      cityId === undefined || cityId === null || cityId === ''
        ? null
        : Number(cityId);

    if (parsedCityId !== null && !Number.isFinite(parsedCityId)) {
      throw new BadRequestException(
        'preferredWorkCityId must be a valid number',
      );
    }

    const payload: Record<string, number | null> = {
      preferredWorkCityId: parsedCityId,
      preferred_work_city_id: parsedCityId,
    };

    if (
      body.preferredWorkLocalityId !== undefined ||
      body.preferred_work_locality_area_id !== undefined
    ) {
      const localityId = this.getValue(
        body.preferredWorkLocalityId,
        body.preferred_work_locality_area_id,
      );
      const parsedLocalityId =
        localityId === undefined || localityId === null || localityId === ''
          ? null
          : Number(localityId);
      payload.preferredWorkLocalityId = parsedLocalityId;
      payload.preferred_work_locality_area_id = parsedLocalityId;
    }

    return payload;
  }

  private async resolveIsBlueCollarForUpdate(userId: number, body: any) {
    const categoryFromBody = this.getValue(
      body.selectedRoleCategory,
      body.selected_role_category,
    );
    if (categoryFromBody) {
      return String(categoryFromBody).toLowerCase() === 'blue_collar';
    }

    try {
      const user = await this.userService.findOne(userId);
      if (user?.selectedRoleCategory) {
        return user.selectedRoleCategory === 'blue_collar';
      }

      const profileResult =
        await this.dashboardService.getSeekerProfileByUserId(userId);
      const professionId = profileResult?.profile?.profession_id;
      if (professionId) {
        const role = await this.rolesService.findOne(professionId);
        const category = role?.category || 'white_collar';
        return category === 'blue_collar';
      }
    } catch (error) {
      this.logger.warn(
        `Failed to resolve blue collar status for userId ${userId}`,
        error instanceof Error ? error.message : String(error),
      );
    }

    return false;
  }

  private async enrichPreferredWorkLocation(seekerProfile: any) {
    if (!seekerProfile?.preferred_work_city_id) {
      return;
    }

    try {
      const rows = await this.dataSource.query(
        `SELECT c.city_name, s.state_name, s.state_id
         FROM city c
         LEFT JOIN state s ON s.state_id = c.state_id
         WHERE c.city_id = ?
         LIMIT 1`,
        [seekerProfile.preferred_work_city_id],
      );

      if (rows?.[0]) {
        seekerProfile.preferred_city_name =
          seekerProfile.preferred_city_name || rows[0].city_name;
        seekerProfile.preferred_state_name = rows[0].state_name;
        seekerProfile.preferred_state_id = rows[0].state_id;
      }
    } catch (error) {
      this.logger.warn(
        `Failed to enrich preferred work location for profile ${seekerProfile.id}`,
      );
    }

    if (
      seekerProfile.preferred_work_locality_area_id &&
      !seekerProfile.preferred_area_name
    ) {
      try {
        const areaRows = await this.dataSource.query(
          `SELECT area FROM area WHERE id = ? LIMIT 1`,
          [seekerProfile.preferred_work_locality_area_id],
        );
        if (areaRows?.[0]) {
          seekerProfile.preferred_area_name = areaRows[0].area;
        }
      } catch (error) {
        this.logger.warn(
          `Failed to enrich preferred work locality for profile ${seekerProfile.id}`,
        );
      }
    }
  }

  private buildProfileLocation(
    address: any,
    seekerProfile: any,
    isBlueCollar: boolean,
  ) {
    if (isBlueCollar) {
      const city = seekerProfile?.preferred_city_name;
      const state = seekerProfile?.preferred_state_name;
      return [city, state].filter(Boolean).join(', ') || null;
    }

    const commCity = address?.communication_city_name;
    const commState = address?.communication_state_name;
    if (commCity || commState) {
      return [commCity, commState].filter(Boolean).join(', ') || null;
    }

    const permCity = address?.permanent_city_name;
    const permState = address?.permanent_state_name;
    if (permCity || permState) {
      return [permCity, permState].filter(Boolean).join(', ') || null;
    }

    return null;
  }

  private readAddressField(address: any, ...keys: string[]) {
    for (const key of keys) {
      const value = address?.[key];
      if (value !== undefined && value !== null && value !== '') {
        return value;
      }
    }

    return null;
  }

  private buildCurrentAddress(address: any) {
    if (!address) {
      return null;
    }

    return {
      address: this.readAddressField(
        address,
        'communication_address',
        'sa_communication_address',
      ),
      pincode: this.readAddressField(
        address,
        'communication_pincode',
        'sa_communication_pincode',
      ),
      area_id: this.readAddressField(
        address,
        'communication_area_id',
        'sa_communication_area_id',
      ),
      area_name: address.communication_area_name ?? null,
      city_id: this.readAddressField(
        address,
        'communication_city_id',
        'sa_communication_city_id',
      ),
      city_name: address.communication_city_name ?? null,
      state_id: this.readAddressField(
        address,
        'communication_state_id',
        'sa_communication_state_id',
      ),
      state_name: address.communication_state_name ?? null,
      country_id: this.readAddressField(
        address,
        'communication_country_id',
        'sa_communication_country_id',
      ),
      country_name: address.communication_country_name ?? null,
    };
  }

  private preservePermanentAddressFields(address: any) {
    if (!address) {
      return {};
    }

    return {
      permanentAddress: address.permanent_address ?? address.sa_permanent_address,
      permanentPincode:
        address.permanent_pincode ?? address.sa_permanent_pincode,
      permanentCountryId:
        address.permanent_country_id ?? address.sa_permanent_country_id,
      permanentStateId:
        address.permanent_state_id ?? address.sa_permanent_state_id,
      permanentCityId: address.permanent_city_id ?? address.sa_permanent_city_id,
      permanentAreaId: address.permanent_area_id ?? address.sa_permanent_area_id,
    };
  }

  private async isBlueCollar(profile: any, fullProfileData: any) {
    const userCategory = this.getValue(
      profile.user?.selectedRoleCategory,
      profile.user?.selected_role_category,
      fullProfileData?.user?.selected_role_category,
      fullProfileData?.user?.selectedRoleCategory,
    );
    if (userCategory) {
      return String(userCategory).toLowerCase() === 'blue_collar';
    }

    // Map Role ID to Category - fallback when user category is missing
    const mapRoleToCategory = async (roleId: number): Promise<string> => {
      try {
        const role = await this.rolesService.findOne(roleId);
        if (!role) {
          return 'white_collar'; // Default to white collar if role not found
        }
        return role.category || 'white_collar'; // Default to white collar if category not set
      } catch (error) {
        this.logger.warn(
          `Failed to determine category for role_id ${roleId}, defaulting to white_collar`,
        );
        return 'white_collar';
      }
    };

    // Fallback: Check profession_id when user category is not set
    const professionId = profile.seeker_profile?.profession_id;
    if (professionId) {
      const category = await mapRoleToCategory(professionId);
      this.logger.debug(`User profession_id ${professionId} is ${category}`);
      return category === 'blue_collar';
    }

    return false;
  }

  private buildBlueCollarWorkExperiences(experiences: any[]) {
    if (!Array.isArray(experiences) || experiences.length === 0) {
      return [];
    }

    return experiences.map((experience: any) => ({
      id: experience.id || null,
      job_title: experience.designation || null,
      job_role: experience.department || null,
      company_name: experience.company || null,
      employment_type: experience.department || null,
      start_date: this.formatDate(experience.start_date),
      end_date: this.formatDate(experience.end_date) || null,
      description:
        experience.responsibilities || experience.description || null,
    }));
  }

  private buildSeekerRoleExperiences(roleExperiences: any[]) {
    if (!Array.isArray(roleExperiences) || roleExperiences.length === 0) {
      return [];
    }

    return roleExperiences.map((re: any) => ({
      id: re.id || null,
      role_id: re.designation_id || null,
      experience_id: re.experience_id || null,
      role_name: re.designation_name || null,
      experience_level: re.experience_level || null,
      experience_duration: re.experience_duration || null,
    }));
  }

  private buildWhiteCollarWorkExperiences(experiences: any[]) {
    if (!Array.isArray(experiences) || experiences.length === 0) {
      return [];
    }

    return experiences.map((experience: any) => ({
      id: experience.id || null,
      job_title: experience.designation || null,
      job_role: experience.department || null,
      company_name: experience.company || null,
      employment_type: experience.department || null,
      start_date: this.formatDate(experience.start_date),
      end_date: this.formatDate(experience.end_date) || null,
      description:
        experience.responsibilities || experience.description || null,
    }));
  }

  private buildExperienceYear(experience: any) {
    if (!experience) {
      return null;
    }

    const startDate = this.formatDate(experience.start_date);
    const endDate = experience.current
      ? 'Present'
      : this.formatDate(experience.end_date);

    if (!startDate) {
      return endDate || null;
    }

    return endDate ? `${startDate} - ${endDate}` : startDate;
  }

  private getLatestResume(resumes: any[]) {
    if (!Array.isArray(resumes) || resumes.length === 0) {
      return null;
    }

    const sorted = [...resumes].sort((a, b) => {
      const aTime = new Date(a.uploaded_at || a.created_at || 0).getTime();
      const bTime = new Date(b.uploaded_at || b.created_at || 0).getTime();
      return bTime - aTime;
    });
    const resume = sorted[0];

    return {
      id: resume.id,
      name: resume.name || resume.original_name || null,
      file_url: resume.file_url || null,
      uploaded_at: resume.uploaded_at || null,
      is_default: Boolean(resume.is_default),
    };
  }

  private formatDate(value: any) {
    if (!value) {
      return null;
    }

    const date = new Date(value);
    if (Number.isNaN(date.getTime())) {
      return value;
    }

    return date.toISOString().slice(0, 10);
  }

  private formatDisplayDate(value: any) {
    if (!value) {
      return null;
    }

    const date = new Date(value);
    if (Number.isNaN(date.getTime())) {
      return value;
    }

    return date.toLocaleDateString('en-GB', {
      day: 'numeric',
      month: 'short',
      year: 'numeric',
    });
  }

  private async resolveSeekerIdFromBody(body: any) {
    if (body.seeker_id || body.seekerId) {
      return this.requirePositiveNumber(
        body.seeker_id ?? body.seekerId,
        'seeker_id',
      );
    }

    const userId = this.requirePositiveNumber(body.userId, 'userId');
    return this.requireSeekerProfileId(userId);
  }

  private async requireSeekerProfileId(userId: number) {
    const seekerId = await this.dashboardService.getSeekerIdByUserId(userId);

    if (!seekerId) {
      throw new BadRequestException('Seeker profile not found');
    }

    return seekerId;
  }

  private requirePositiveNumber(value: any, fieldName: string) {
    const numberValue = Number(value);

    if (!Number.isFinite(numberValue) || numberValue <= 0) {
      throw new BadRequestException(`${fieldName} is required`);
    }

    return numberValue;
  }

  private parseEditableArray(value: any) {
    if (value === undefined || value === null || value === '') {
      return [];
    }

    if (Array.isArray(value)) {
      return value;
    }

    if (typeof value === 'string') {
      try {
        const parsed = JSON.parse(value);
        return Array.isArray(parsed) ? parsed : [];
      } catch {
        return value
          .split(',')
          .map((item) => item.trim())
          .filter(Boolean);
      }
    }

    return [];
  }

  private getValue(...values: any[]) {
    return values.find(
      (value) => value !== undefined && value !== null && value !== '',
    );
  }

  private async enrichCurrentAddressFromPincode(body: any) {
    const currentAddress = body.currentAddress;
    const pincode = body.pincode;

    if (
      currentAddress === undefined &&
      pincode === undefined &&
      body.areaId === undefined
    ) {
      return {};
    }

    const addressPayload: any = {
      communicationAddress: currentAddress,
      communicationPincode: pincode,
      communicationCountryId: body.countryId,
      communicationStateId: body.stateId,
      communicationCityId: body.cityId,
      communicationAreaId: body.areaId,
      sameAsPermanent: false,
    };

    if (!pincode) {
      return addressPayload;
    }

    const areas = await this.areaService.getAreasByPincode(String(pincode), 1);
    if (!Array.isArray(areas) || areas.length === 0) {
      return addressPayload;
    }

    const requestedAreaId = Number(addressPayload.communicationAreaId);
    const matchedArea =
      areas.find((area) => Number(area.id) === requestedAreaId) || areas[0];

    return {
      ...addressPayload,
      communicationCountryId:
        addressPayload.communicationCountryId || matchedArea.country_id,
      communicationStateId:
        addressPayload.communicationStateId || matchedArea.state_id,
      communicationCityId:
        addressPayload.communicationCityId || matchedArea.city_id,
      communicationAreaId: addressPayload.communicationAreaId || matchedArea.id,
    };
  }

  private compactObject(data: Record<string, any>) {
    return Object.fromEntries(
      Object.entries(data).filter(([, value]) => value !== undefined),
    );
  }

  private async loadProfileCompletion(userId: number) {
    try {
      return await this.dashboardService.getSeekerProfileCompletion(userId);
    } catch (error) {
      this.logger.error(
        `Failed to fetch profile completion for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );
      return null;
    }
  }

  private async loadFullProfile(userId: number) {
    try {
      return await this.dashboardService.getSeekerFullProfile(userId);
    } catch (error) {
      this.logger.error(
        `Failed to fetch full profile names for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );
      return null;
    }
  }

  /*
   * ---------------------------------------------------------
   * EXPERIENCES
   * ---------------------------------------------------------
   */
  private async loadExperiences(profile: any, seekerId: number) {
    try {
      const experiences =
        await this.dashboardService.getSeekerExperiences(seekerId);

      if (Array.isArray(experiences) && experiences.length > 0) {
        profile.experiences = experiences;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch experiences for seekerId ${seekerId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * EDUCATIONS
   * ---------------------------------------------------------
   */
  private async loadEducations(profile: any, seekerId: number) {
    try {
      const educations =
        await this.dashboardService.getSeekerEducations(seekerId);

      if (Array.isArray(educations) && educations.length > 0) {
        // Enrich degree and specialization names if their IDs are present
        for (const edu of educations) {
          if (edu.degree_id && !edu.degree) {
            try {
              const rows = await this.dataSource.query(
                `SELECT name FROM degree WHERE id = ? LIMIT 1`,
                [edu.degree_id],
              );
              if (rows?.[0]?.name) {
                edu.degree = rows[0].name;
              }
            } catch (err) {
              // ignore
            }
          }
          if (edu.specialization_id && !edu.specialization) {
            try {
              const rows = await this.dataSource.query(
                `SELECT name FROM specialization WHERE id = ? LIMIT 1`,
                [edu.specialization_id],
              );
              if (rows?.[0]?.name) {
                edu.specialization = rows[0].name;
              }
            } catch (err) {
              // ignore
            }
          }
        }
        profile.educations = educations;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch educations for seekerId ${seekerId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * SKILLS
   * ---------------------------------------------------------
   */
  private async loadSkills(profile: any, seekerId: number) {
    try {
      const skills = await this.dashboardService.getSeekerSkills(seekerId);

      if (Array.isArray(skills) && skills.length > 0) {
        profile.skills = skills;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch skills for seekerId ${seekerId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * LANGUAGES
   * ---------------------------------------------------------
   */
  private async loadLanguages(profile: any, seekerId: number) {
    try {
      const languages =
        await this.dashboardService.getSeekerLanguages(seekerId);

      if (Array.isArray(languages) && languages.length > 0) {
        profile.languages = languages;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch languages for seekerId ${seekerId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * SOCIAL LINKS
   * ---------------------------------------------------------
   */
  private async loadSocialLinks(profile: any, seekerId: number) {
    try {
      const socialLinks =
        await this.dashboardService.getSeekerSocialLinks(seekerId);

      if (Array.isArray(socialLinks) && socialLinks.length > 0) {
        profile.social_links = socialLinks;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch social links for seekerId ${seekerId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * ADDRESS
   * ---------------------------------------------------------
   */
  private async loadAddress(profile: any, seekerProfileId: number) {
    try {
      const addressData =
        await this.dashboardService.getSeekerAddress(seekerProfileId);

      if (addressData?.success && addressData?.address) {
        profile.address = addressData.address;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch address for seekerProfileId ${seekerProfileId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * RESUMES
   * ---------------------------------------------------------
   */
  private async loadResumes(profile: any, userId: number) {
    try {
      const resumesData =
        await this.dashboardService.getSeekerResumesByUserId(userId);

      if (
        resumesData?.success &&
        Array.isArray(resumesData?.resumes) &&
        resumesData.resumes.length > 0
      ) {
        profile.resumes = resumesData.resumes;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch resumes for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * CONTACT INFO
   * ---------------------------------------------------------
   */
  private async loadContactInfo(profile: any, userId: number) {
    try {
      const contactInfoData =
        await this.dashboardService.getUserContactInfo(userId);

      if (contactInfoData?.success && contactInfoData?.user) {
        profile.contact_info = contactInfoData.user;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch contact info for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  /*
   * ---------------------------------------------------------
   * JOB PREFERENCES
   * ---------------------------------------------------------
   */
  private async loadJobPreferences(profile: any, userId: number) {
    try {
      const preferencesData =
        await this.seekerPreferencesService.getSeekerPreferences(userId);

      if (
        preferencesData &&
        (preferencesData.preference_id ||
          preferencesData.roles?.length > 0 ||
          preferencesData.skills?.length > 0)
      ) {
        profile.job_preferences = preferencesData;
      }
    } catch (error) {
      this.logger.error(
        `Failed to fetch job preferences for userId ${userId}`,
        error instanceof Error ? error.stack : String(error),
      );
    }
  }

  async uploadProfilePicture(userId: number, file: Express.Multer.File) {
    if (!userId || userId <= 0) {
      throw new BadRequestException('Valid userId is required');
    }
    if (!file) {
      throw new BadRequestException('Profile picture file is required');
    }

    const user = await this.userService.findOne(userId);
    if (!user) {
      throw new BadRequestException('User not found');
    }

    // 1. Delete old profile picture if exists to save disk space
    if (user.profilePicture) {
      try {
        const oldFilePath = path.join(process.cwd(), user.profilePicture);
        if (fs.existsSync(oldFilePath)) {
          fs.unlinkSync(oldFilePath);
        }
      } catch (err) {
        this.logger.warn(`Failed to delete old profile picture file: ${user.profilePicture}`);
      }
    }

    // 2. Normalize and save the new profile picture path
    const profilePicturePath = file.path.replace(/\\/g, '/');
    await this.userService.update(userId, { profilePicture: profilePicturePath });

    return {
      success: true,
      profilePicture: profilePicturePath,
    };
  }

  async deleteProfilePicture(userId: number) {
    if (!userId || userId <= 0) {
      throw new BadRequestException('Valid userId is required');
    }

    const user = await this.userService.findOne(userId);
    if (!user) {
      throw new BadRequestException('User not found');
    }

    if (user.profilePicture) {
      try {
        const filePath = path.join(process.cwd(), user.profilePicture);
        if (fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
        }
      } catch (err) {
        this.logger.warn(`Failed to delete profile picture file: ${user.profilePicture}`);
      }
      await this.userService.update(userId, { profilePicture: null });
    }

    return {
      success: true,
      message: 'Profile picture deleted successfully',
    };
  }

  async getProfilePicture(userId: number, res: Response) {
    if (!userId || userId <= 0) {
      throw new BadRequestException('Valid userId is required');
    }

    const user = await this.userService.findOne(userId);
    if (!user) {
      throw new BadRequestException('User not found');
    }

    if (!user.profilePicture) {
      throw new NotFoundException('Profile picture not set');
    }

    const filePath = path.join(process.cwd(), user.profilePicture);
    if (!fs.existsSync(filePath)) {
      throw new NotFoundException('Profile picture file not found');
    }

    return res.sendFile(filePath);
  }
}
