import { Injectable } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class SeekerPreferencesService {
  constructor(private readonly dataSource: DataSource) {}

  async saveSeekerPreferences(data: {
    user_id: number;
    roles?: number[];
    skills?: number[];
  }): Promise<any> {
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      // Check if user exists
      const userExists = await queryRunner.manager
        .createQueryBuilder()
        .select('u.id')
        .from('users', 'u')
        .where('u.id = :userId', { userId: data.user_id })
        .getRawOne();

      if (!userExists) {
        throw new Error(`User with ID ${data.user_id} does not exist`);
      }

      // Check if user already has preferences
      const existingPreference = await queryRunner.manager
        .createQueryBuilder()
        .select('sp.id')
        .from('seeker_preferences', 'sp')
        .where('sp.user_id = :userId', { userId: data.user_id })
        .getRawOne();

      let preferenceId: number;

      if (existingPreference) {
        // Update the existing preference row if needed
        await queryRunner.manager
          .createQueryBuilder()
          .update('seeker_preferences')
          .set({ is_active: true }) // update fields as needed
          .where('id = :id', { id: existingPreference.id })
          .execute();

        preferenceId = existingPreference.id;

        // Delete existing roles and skills
        await queryRunner.manager
          .createQueryBuilder()
          .delete()
          .from('seeker_preferred_roles')
          .where('seeker_preference_id = :preferenceId', { preferenceId })
          .execute();

        await queryRunner.manager
          .createQueryBuilder()
          .delete()
          .from('seeker_preferred_skills')
          .where('seeker_preference_id = :preferenceId', { preferenceId })
          .execute();
      } else {
        // Insert new preference
        try {
          const insertQuery = queryRunner.manager
            .createQueryBuilder()
            .insert()
            .into('seeker_preferences')
            .values({
              user_id: data.user_id,
              is_active: true,
            });

          const preferenceResult = await insertQuery.execute();

          // Check if insert was successful using raw result
          if (preferenceResult.raw && preferenceResult.raw.insertId) {
            preferenceId = preferenceResult.raw.insertId;
          } else if (
            preferenceResult.identifiers &&
            preferenceResult.identifiers[0]
          ) {
            preferenceId = preferenceResult.identifiers[0].id;
          } else {
            throw new Error(
              'Failed to create seeker preference record - no insert ID returned',
            );
          }
        } catch (insertError) {
          throw new Error(
            `Failed to create seeker preference record: ${insertError.message}`,
          );
        }
      }

      // Insert preferred roles if provided
      if (data.roles && data.roles.length > 0) {
        // Check if all roles exist
        for (const roleId of data.roles) {
          const roleExists = await queryRunner.manager
            .createQueryBuilder()
            .select('r.id')
            .from('roles', 'r')
            .where('r.id = :roleId', { roleId })
            .getRawOne();

          if (!roleExists) {
            throw new Error(`Role with ID ${roleId} does not exist`);
          }
        }

        for (const roleId of data.roles) {
          await queryRunner.manager
            .createQueryBuilder()
            .insert()
            .into('seeker_preferred_roles')
            .values({
              seeker_preference_id: preferenceId,
              role_id: roleId,
            })
            .execute();
        }
      }

      // Insert preferred skills if provided
      if (data.skills && data.skills.length > 0) {
        // Check if all skills exist
        for (const skillId of data.skills) {
          const skillExists = await queryRunner.manager
            .createQueryBuilder()
            .select('s.id')
            .from('skills', 's')
            .where('s.id = :skillId', { skillId })
            .getRawOne();

          if (!skillExists) {
            throw new Error(`Skill with ID ${skillId} does not exist`);
          }
        }

        for (const skillId of data.skills) {
          await queryRunner.manager
            .createQueryBuilder()
            .insert()
            .into('seeker_preferred_skills')
            .values({
              seeker_preference_id: preferenceId,
              skill_id: skillId,
            })
            .execute();
        }
      }

      await queryRunner.commitTransaction();
      return { preferenceId, message: 'Preferences saved successfully' };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  async getSeekerPreferences(userId: number): Promise<any> {
    const preferences = await this.dataSource
      .createQueryBuilder()
      .select([
        'sp.id AS preference_id',
        'sp.user_id',
        'spr.role_id',
        'sps.skill_id',
      ])
      .from('seeker_preferences', 'sp')
      .leftJoin(
        'seeker_preferred_roles',
        'spr',
        'spr.seeker_preference_id = sp.id',
      )
      .leftJoin(
        'seeker_preferred_skills',
        'sps',
        'sps.seeker_preference_id = sp.id',
      )
      .where('sp.user_id = :userId', { userId })
      .getRawMany();

    const roles = [
      ...new Set(
        preferences
          .map((p) => p.role_id)
          .filter((id) => id !== null && id !== undefined)
          .map(Number),
      ),
    ];

    const skills = [
      ...new Set(
        preferences
          .map((p) => p.skill_id)
          .filter((id) => id !== null && id !== undefined)
          .map(Number),
      ),
    ];

    return {
      preference_id: preferences[0]?.preference_id ?? null,
      roles,
      skills,
    };
  }
}
