import { Injectable, HttpStatus, InternalServerErrorException } from '@nestjs/common';
import { Kysely, sql } from 'kysely';
import { Logger } from 'pino-nestjs';
import { DB } from '@/database/database.type';

@Injectable()
export class SuburbsService {
  constructor(
    private readonly db: Kysely<DB>,
    private readonly logger: Logger,
  ) {}

  async getSuburbs() {
    try {
      const result = await this.db
        .selectFrom('pet_profiles')
        .select([
          'suburb',
          sql<number>`COUNT(*)::int`.as('count'),
          sql<string[]>`array_agg(DISTINCT pet_type)`.as('pet_types'),
        ])
        .where('suburb', 'is not', null)
        .where('suburb', '!=', '')
        .where('deleted_at', 'is', null)
        .groupBy('suburb')
        .orderBy('count', 'desc')
        .orderBy('suburb', 'asc')
        .execute();

      const data = result.map((r: any) => ({
        suburb: r.suburb,
        count: r.count,
        pet_types: r.pet_types || [],
      }));

      return {
        status: true,
        statusCode: HttpStatus.OK,
        data,
      };
    } catch (error) {
      this.logger.error('Error fetching suburbs', error);
      throw new InternalServerErrorException('Failed to fetch suburbs');
    }
  }

  // This endpoint is public and unauthenticated, and `SELECT *` includes owner
  // PII (name/phone) — a missing `suburb` must never fall through to a full
  // table dump. Express's own behavior here is effectively "no suburb = no
  // results" (its query interpolates an unset param to the literal string
  // "undefined", which never matches); we make that intent explicit instead
  // of relying on that coincidence.
  async getSuburbsInfo(suburb?: string) {
    try {
      if (!suburb || !suburb.trim()) {
        return {
          status: true,
          statusCode: HttpStatus.OK,
          data: [],
        };
      }

      const result = await this.db
        .selectFrom('pet_profiles')
        .selectAll()
        .where('deleted_at', 'is', null)
        .where('suburb', 'ilike', `%${suburb}%`)
        .execute();

      return {
        status: true,
        statusCode: HttpStatus.OK,
        data: result,
      };
    } catch (error) {
      this.logger.error('Error fetching suburbs info', error);
      throw new InternalServerErrorException('Failed to fetch suburbs info');
    }
  }
}