import { Injectable, BadRequestException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { DataSource, Repository } from 'typeorm';
import { SeekerProfiles } from './entities/seeker_profiles.entity';
import { UserEntity } from '../user/entities/user.entity';
import { SeekerAddressService } from '../seeker_address/seeker_address.service';
import { SeekerAddress } from '../seeker_address/entities/seeker_address.entity';

@Injectable()
export class SeekerProfilesService {
  constructor(
    @InjectRepository(SeekerProfiles)
    private readonly seekerProfilesRepo: Repository<SeekerProfiles>,
    @InjectRepository(UserEntity)
    private readonly userRepo: Repository<UserEntity>,
    @InjectRepository(SeekerAddress)
    private readonly seekerAddressRepo: Repository<SeekerAddress>,
    private readonly dataSource: DataSource,
  ) {}

  async updateBasicDetails(userId: number, data: any) {
    if (data.date_of_birth) {
      const dob = new Date(data.date_of_birth);
      const today = new Date();
      let age = today.getFullYear() - dob.getFullYear();
      const m = today.getMonth() - dob.getMonth();
      if (m < 0 || (m === 0 && today.getDate() < dob.getDate())) {
        age--;
      }
      if (age < 16) {
        throw new BadRequestException('You must be at least 16 years old.');
      }
    }

    // 1. Update User Entity (Name, Email, Phone)
    const userUpdates: any = {};
    if (data.fullName !== undefined) userUpdates.fullName = data.fullName;
    if (data.phone !== undefined) userUpdates.phone = data.phone;
    if (data.email !== undefined) userUpdates.email = data.email;

    if (Object.keys(userUpdates).length > 0) {
      await this.userRepo.update(userId, userUpdates);
    }

    // Lookup functions for mapping strings to IDs
    const lookupId = async (tableName: string, nameValue: string, pkColumn: string = 'id', nameColumn: string = 'name'): Promise<number | null> => {
      if (!nameValue || typeof nameValue !== 'string') return null;
      const res = await this.dataSource.query(`SELECT ${pkColumn} FROM ${tableName} WHERE LOWER(${nameColumn}) = ? LIMIT 1`, [nameValue.toLowerCase()]);
      return res.length > 0 ? res[0][pkColumn] : null;
    };

    // 2. Update SeekerProfiles Entity
    const profileUpdates: any = {};
    if (data.profession_id !== undefined) profileUpdates.profession_id = data.profession_id;
    if (data.professional_title !== undefined) profileUpdates.professional_title = data.professional_title;
    if (data.date_of_birth !== undefined) profileUpdates.date_of_birth = data.date_of_birth;
    if (data.gender !== undefined) profileUpdates.gender = data.gender;
    if (data.marital_status !== undefined) profileUpdates.marital_status = data.marital_status;

    // Resolve qualification string to education_id
    if (data.qualification && typeof data.qualification === 'string') {
      const eduId = await lookupId('education_levels', data.qualification, 'id', 'name');
      if (eduId) profileUpdates.education_id = eduId;
    }
    
    // Resolve experience string to experiences_id
    if (data.experience && typeof data.experience === 'string') {
      const expId = await lookupId('experiences', data.experience, 'id', 'level');
      if (expId) profileUpdates.experiences_id = expId;
    }

    if (Object.keys(profileUpdates).length > 0) {
      const existingProfile = await this.seekerProfilesRepo.findOne({ where: { user_id: userId } });
      if (existingProfile) {
        await this.seekerProfilesRepo.update({ user_id: userId }, profileUpdates);
      } else {
        await this.seekerProfilesRepo.save({ user_id: userId, ...profileUpdates });
      }
    }

    // Ensure profile exists for address and skills insertion
    const profile = await this.seekerProfilesRepo.findOne({ where: { user_id: userId } });
    if (!profile) return { success: true, message: 'Updated basic details' };

    // 3. Update SeekerSkills
    if (data.skills_ids !== undefined) {
      // Delete existing skills
      await this.dataSource.query('DELETE FROM seeker_skills WHERE seeker_id = ?', [profile.id]);
      
      // Insert new skills
      if (Array.isArray(data.skills_ids) && data.skills_ids.length > 0) {
        for (const skillId of data.skills_ids) {
          await this.dataSource.query('INSERT INTO seeker_skills (seeker_id, skill_id) VALUES (?, ?)', [profile.id, skillId]);
        }
      }
    }

    // 3.5 Update SeekerLanguages
    const languagesArray = data.languages_ids ?? data.languages ?? data.languagesIds;
    if (languagesArray !== undefined) {
      await this.dataSource.query('DELETE FROM seeker_language WHERE seeker_id = ?', [profile.id]);
      
      if (Array.isArray(languagesArray) && languagesArray.length > 0) {
        for (const langItem of languagesArray) {
          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.');
          }

          if (!isNaN(id)) {
            await this.dataSource.query(
              'INSERT INTO seeker_language (seeker_id, language_id, english_level) VALUES (?, ?, ?)',
              [profile.id, id, englishLevel]
            );
          }
        }
      }
    }

    // 4. Update SeekerAddress Entity
    const hasAddressFields = data.currentAddress || data.pincode || data.area || data.state || data.city || data.country;
    
    if (hasAddressFields) {
      const countryId = await lookupId('country', data.country, 'country_id', 'country_name');
      const stateId = await lookupId('state', data.state, 'state_id', 'state_name');
      const cityId = await lookupId('city', data.city, 'city_id', 'city_name');
      const areaId = await lookupId('area', data.area, 'id', 'area');

      console.log('--- ADDRESS UPDATE DEBUG ---');
      console.log('Incoming data:', data);
      console.log('Lookup Results:', { countryId, stateId, cityId, areaId });

      const addressData = {
        seeker_profile_id: profile.id,
        permanent_address: data.currentAddress || null,
        permanent_pincode: data.pincode || null,
        permanent_country_id: countryId || null,
        permanent_state_id: stateId || null,
        permanent_city_id: cityId || null,
        permanent_area_id: areaId || null,
        same_as_permanent: 1, // Defaulting to same for UI simplicity
        communication_address: data.currentAddress || null,
        communication_pincode: data.pincode || null,
        communication_country_id: countryId || null,
        communication_state_id: stateId || null,
        communication_city_id: cityId || null,
        communication_area_id: areaId || null,
      };

      const existingAddresses = await this.seekerAddressRepo.find({ where: { seeker_profile_id: profile.id }, order: { id: 'ASC' } });

      if (existingAddresses.length > 0) {
        await this.seekerAddressRepo.update(existingAddresses[0].id, addressData);
      } else {
        await this.seekerAddressRepo.save(addressData);
      }
    }

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