import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, Not, IsNull } from 'typeorm';
import { City } from './entities/city.entity';

@Injectable()
export class CityService {
  constructor(
    @InjectRepository(City)
    private readonly cityRepository: Repository<City>,
  ) {}

  // Fetch all records from city table
  async findAll() {
    return this.cityRepository
      .createQueryBuilder('c')
      .where('c.status = :status', { status: 1 })
      .orderBy('c.city_id', 'ASC')
      .getMany();
  }

  async findFeatured() {
    return this.cityRepository.find({
      where: {
        status: 1,
        is_featured: 1,
      },
      order: {
        city_name: 'ASC',
      },
    });
  }

  async search(query: string) {
    const q = `%${query || ''}%`;
    return this.cityRepository.query(
      `SELECT 
        c.city_id, 
        c.city_name, 
        c.state_id, 
        c.status,
        c.is_featured,
        s.state_name, 
        co.country_id,
        co.country_name,
        CONCAT(c.city_name, ', ', s.state_name, ', ', co.country_name) AS full_location
       FROM city c 
       LEFT JOIN state s ON c.state_id = s.state_id 
       LEFT JOIN country co ON s.country_id = co.country_id
       WHERE c.status = 1 AND LOWER(c.city_name) LIKE LOWER(?) 
       ORDER BY c.is_featured DESC, c.city_name ASC LIMIT 100`,
      [q],
    );
  }

  async findByState(
    stateId: number,
    page: number = 1,
    limit: number = 10,
    status?: number,
  ) {
    const skip = (page - 1) * limit;
    const where: any = { state_id: stateId };
    if (status !== undefined) where.status = status;

    const [data, total] = await this.cityRepository.findAndCount({
      where,
      skip,
      take: limit,
      order: { city_name: 'ASC' },
    });

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

  async findOne(id: number): Promise<City> {
    return this.cityRepository.findOne({ where: { city_id: id } });
  }

  async create(createCityDto: any): Promise<any> {
    const newCity = this.cityRepository.create(createCityDto);
    return this.cityRepository.save(newCity);
  }

  async update(id: number, updateCityDto: any): Promise<any> {
    delete updateCityDto.id;
    await this.cityRepository.update({ city_id: id }, updateCityDto);
    return this.cityRepository.findOne({ where: { city_id: id } });
  }

  async remove(id: number): Promise<void> {
    await this.cityRepository.delete({ city_id: id });
  }
  async getCitiesByFrontOrder(): Promise<City[]> {
    return this.cityRepository.find({
      where: {
        status: 1,
        front_order: Not(IsNull()),
      },
      order: {
        front_order: 'ASC',
      },
    });
  }
}
