import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Roles } from './entities/roles.entity';
import { Repository } from 'typeorm';
import { DataSource } from 'typeorm';

@Injectable()
export class RolesService {
  constructor(
    @InjectRepository(Roles)
    private readonly rolesRepository: Repository<Roles>,
    private readonly dataSource: DataSource,
  ) {}

  async findAll(): Promise<Roles[]> {
    return this.rolesRepository.find({ where: { status: 1 } });
  }

  async search(query: string) {
    const q = `%${query || ''}%`;
    return this.rolesRepository.query(
      `SELECT * FROM roles WHERE status = 1 AND LOWER(name) LIKE LOWER(?) ORDER BY name ASC LIMIT 10`,
      [q],
    );
  }

  //   async findAllPaginated(page: number = 1, limit: number = 10, roleLevelId?: number, search?: string) {
  //     const skip = (page - 1) * limit;
  //     const where: any = {};
  //     if (roleLevelId) {
  //       where.roleLevelsId = roleLevelId;
  //     }

  //     // Build query with search
  //     const queryBuilder = this.rolesRepository.createQueryBuilder('role')
  //       .where(where);

  //     // Add search filter if provided
  //     if (search && search.trim()) {
  //       queryBuilder.andWhere('LOWER(role.name) LIKE LOWER(:search)', { search: `%${search}%` });
  //     }

  //     const [data, total] = await queryBuilder
  //       .skip(skip)
  //       .take(limit)
  //       .orderBy('role.name', 'ASC')
  //       .getManyAndCount();

  //     return {
  //       success: true,
  //       data,
  //       meta: {
  //         totalItems: total,
  //         itemsPerPage: limit,
  //         currentPage: page,
  //         totalPages: Math.ceil(total / limit),
  //       },
  //     };
  //   }
  //  async findAllPaginated(page: number = 1, limit: number = 10, roleLevelId?: number, search?: string, category?: 'blue_collar' | 'white_collar') {
  //     const skip = (page - 1) * limit;
  //     const where: any = { status: 1 };
  //     if (roleLevelId) {
  //       where.roleLevelsId = roleLevelId;
  //     }
  //     // Apply category filter (blue_collar / white_collar)
  //     if (category) {
  //       where.category = category;
  //     }

  //     // Build query with search
  //     const queryBuilder = this.rolesRepository.createQueryBuilder('role')
  //       .where(where);

  //     // Add search filter if provided
  //     if (search && search.trim()) {
  //       queryBuilder.andWhere('LOWER(role.name) LIKE LOWER(:search)', { search: `%${search}%` });
  //     }

  //     const [data, total] = await queryBuilder
  //       .skip(skip)
  //       .take(limit)
  //       .orderBy('role.name', 'ASC')
  //       .getManyAndCount();

  //     return {
  //       success: true,
  //       data,
  //       meta: {
  //         totalItems: total,
  //         itemsPerPage: limit,
  //         currentPage: page,
  //         totalPages: Math.ceil(total / limit),
  //       },
  //     };
  //   }

  async findAllPaginated(
    page: number = 1,
    limit: number = 20,
    roleLevelId?: number,
    search?: string,
    category?: 'blue_collar' | 'white_collar',
  ) {
    const safeLimit = Math.min(Number(limit) || 20, 200); // max 200 per page
    const skip = (page - 1) * safeLimit;
    const where: any = { status: 1 };
    if (roleLevelId) {
      where.roleLevelsId = roleLevelId;
    }
    // Apply category filter (blue_collar / white_collar)
    if (category) {
      where.category = category;
    }

    // Build query with search
    const queryBuilder = this.rolesRepository
      .createQueryBuilder('role')
      .where(where);

    // Search filter: only apply if 2+ characters (avoids noisy single-char queries)
    const trimmedSearch = (search || '').trim();
    if (trimmedSearch.length >= 2) {
      queryBuilder.andWhere('LOWER(role.name) LIKE LOWER(:search)', {
        search: `%${trimmedSearch}%`,
      });
    }

    const [data, total] = await queryBuilder
      .skip(skip)
      .take(safeLimit)
      .orderBy('role.name', 'ASC')
      .getManyAndCount();

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

    return {
      success: true,
      data,
      meta: {
        totalItems: total,
        itemsPerPage: safeLimit,
        currentPage: page,
        totalPages,
        hasMore: page < totalPages, // Mobile uses this to show "load more"
      },
    };
  }

  async getRolesWithOpenPositions() {
    return this.dataSource
      .createQueryBuilder()
      .select([
        'role.id AS id',
        'role.name AS name',
        'role.role_levels_id AS role_levels_id',
        'role.created_at AS created_at',
        'role.updated_at AS updated_at',
        'role.role_definition AS role_definition',
        'COUNT(job.id) AS open_positions',
      ])
      .from('roles', 'role')
      .leftJoin(
        'job_posts',
        'job',
        'job.role_id = role.id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .groupBy('role.id')
      .orderBy('open_positions', 'DESC')
      .getRawMany();
  }

  async getPopularRoles() {
    return this.dataSource
      .createQueryBuilder()
      .select([
        'role.id AS id',
        'role.name AS name',
        'role.role_levels_id AS role_levels_id',
        'role.created_at AS created_at',
        'role.updated_at AS updated_at',
        'role.role_definition AS role_definition',
        'role.is_popular AS is_popular',
        'COUNT(DISTINCT job.id) AS open_positions',
      ])
      .from('roles', 'role')
      .leftJoin(
        'job_posts',
        'job',
        'job.role_id = role.id AND job.status = :status AND (job.expires_at IS NULL OR job.expires_at > NOW())',
        { status: 'open' },
      )
      .leftJoin('companies', 'co', 'co.id = job.company_id')
      .where('role.is_popular = :isPopular', { isPopular: true })
      .andWhere('role.status = :activeRole', { activeRole: 1 })
      .andWhere('(job.id IS NULL OR (co.status = 1 AND co.is_deleted = 0))')
      .groupBy('role.id')
      .orderBy('open_positions', 'DESC')
      .getRawMany();
  }
  async getMockRoles(): Promise<Roles[]> {
    return this.rolesRepository.find({
      where: {
        forMock: 1,
        status: 1,
      },
      order: {
        name: 'ASC',
      },
    });
  }

  async findOne(id: number): Promise<Roles> {
    return this.rolesRepository.findOne({ where: { id } });
  }

  async create(body: any): Promise<any> {
    const { role_level_id, ...rest } = body;
    const newRole = this.rolesRepository.create({
      ...rest,
      roleLevelsId: role_level_id,
    });
    return this.rolesRepository.save(newRole);
  }

  async update(id: number, body: any): Promise<any> {
    const { role_level_id, ...rest } = body;
    const updateData: any = { ...rest };
    if (role_level_id !== undefined) {
      updateData.roleLevelsId = role_level_id;
    }
    await this.rolesRepository.update(id, updateData);
    return this.rolesRepository.findOne({ where: { id } });
  }

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