import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, In } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { TenantAwareRepository } from '../../../common/repositories/tenant-aware.repository';
import { TransportPricingEntity } from '../../../entities/transport-pricing.entity';

@Injectable()
export class TransportPricingRepository extends TenantAwareRepository<TransportPricingEntity> {
  constructor(
    @InjectRepository(TransportPricingEntity) repo: Repository<TransportPricingEntity>,
    cls: ClsService,
  ) {
    super(repo, cls);
  }

  async findByDestinationAndCurrency(destinationId: string, currencyId?: string): Promise<TransportPricingEntity[]> {
    const tenantId = this.getTenantId();
    const where: any = { tenant_id: tenantId, is_deleted: false };
    if (destinationId) where.destination_id = destinationId;
    if (currencyId) where.currency_id = currencyId;
    return this.repo.find({ where, relations: ['transport_service', 'currency'], order: { category: 'ASC', cab_type: 'ASC' } });
  }

  async findPaginatedByRoutes(query: {
    destinationId?: string; currencyId?: string; search?: string; page?: number; limit?: number;
  }) {
    const tenantId = this.getTenantId();
    const { destinationId, currencyId, search, page = 1, limit = 10 } = query;

    // Step 1: Count distinct routes
    const countQb = this.repo.createQueryBuilder('tp')
      .select('COUNT(DISTINCT tp.transport_service_id)', 'count')
      .where('tp.tenant_id = :tenantId AND tp.is_deleted = false', { tenantId });

    if (destinationId) countQb.andWhere('tp.destination_id = :destinationId', { destinationId });
    if (currencyId) countQb.andWhere('tp.currency_id = :currencyId', { currencyId });
    if (search) {
      countQb.leftJoin('tp.transport_service', 'ts_count');
      const words = search.replace(/→/g, ' ').split(/\s+/).filter(Boolean);
      if (words.length > 1) {
        const wordConditions = words.map((_, i) => `(ts_count.from_city ILIKE :sw${i} OR ts_count.to_city ILIKE :sw${i} OR ts_count.short_code ILIKE :sw${i})`).join(' AND ');
        const params: Record<string, string> = {};
        words.forEach((w, i) => { params[`sw${i}`] = `%${w}%`; });
        countQb.andWhere(`(${wordConditions})`, params);
      } else {
        countQb.andWhere('(ts_count.from_city ILIKE :search OR ts_count.to_city ILIKE :search OR ts_count.short_code ILIKE :search)', { search: `%${search}%` });
      }
    }

    const countResult = await countQb.getRawOne();
    const totalRoutes = parseInt(countResult?.count || '0', 10);

    // Step 2: Get paginated distinct transport_service_ids
    const routeQb = this.repo.createQueryBuilder('tp')
      .select('DISTINCT tp.transport_service_id', 'transport_service_id')
      .where('tp.tenant_id = :tenantId AND tp.is_deleted = false', { tenantId });

    if (destinationId) routeQb.andWhere('tp.destination_id = :destinationId', { destinationId });
    if (currencyId) routeQb.andWhere('tp.currency_id = :currencyId', { currencyId });
    if (search) {
      routeQb.leftJoin('tp.transport_service', 'ts');
      const words = search.replace(/→/g, ' ').split(/\s+/).filter(Boolean);
      if (words.length > 1) {
        const wordConditions = words.map((_, i) => `(ts.from_city ILIKE :rw${i} OR ts.to_city ILIKE :rw${i} OR ts.short_code ILIKE :rw${i})`).join(' AND ');
        const params: Record<string, string> = {};
        words.forEach((w, i) => { params[`rw${i}`] = `%${w}%`; });
        routeQb.andWhere(`(${wordConditions})`, params);
      } else {
        routeQb.andWhere('(ts.from_city ILIKE :search OR ts.to_city ILIKE :search OR ts.short_code ILIKE :search)', { search: `%${search}%` });
      }
    }

    const routeIds = await routeQb
      .orderBy('tp.transport_service_id', 'ASC')
      .offset((page - 1) * limit)
      .limit(limit)
      .getRawMany();

    const serviceIds = routeIds.map((r: any) => r.transport_service_id);
    if (serviceIds.length === 0) {
      return { items: [], meta: { total: totalRoutes, page, limit, totalPages: Math.ceil(totalRoutes / limit) } };
    }

    // Step 3: Fetch all pricing rows for those routes
    const items = await this.repo.find({
      where: serviceIds.map((id: string) => ({
        tenant_id: tenantId, transport_service_id: id, is_deleted: false,
        ...(currencyId ? { currency_id: currencyId } : {}),
      })),
      relations: ['transport_service', 'currency'],
      order: { category: 'ASC', cab_type: 'ASC' },
    });

    return { items, meta: { total: totalRoutes, page, limit, totalPages: Math.ceil(totalRoutes / limit) } };
  }

  async findExisting(transportServiceId: string, cabType: string, currencyId: string): Promise<TransportPricingEntity | null> {
    const tenantId = this.getTenantId();
    return this.repo.findOne({
      where: { transport_service_id: transportServiceId, cab_type: cabType, currency_id: currencyId, tenant_id: tenantId, is_deleted: false } as any,
    });
  }

  async findSoftDeleted(transportServiceId: string, cabType: string, currencyId: string): Promise<TransportPricingEntity | null> {
    const tenantId = this.getTenantId();
    return this.repo.findOne({
      where: { transport_service_id: transportServiceId, cab_type: cabType, currency_id: currencyId, tenant_id: tenantId, is_deleted: true } as any,
    });
  }

  async reactivate(id: string, data: Partial<TransportPricingEntity>): Promise<void> {
    await this.repo.update(id, { ...data, is_deleted: false } as any);
  }

  async getDistinctCabTypes(destinationId: string): Promise<string[]> {
    const tenantId = this.getTenantId();
    const result = await this.repo
      .createQueryBuilder('v')
      .select('DISTINCT v.cab_type', 'cab_type')
      .where('v.tenant_id = :tenantId AND v.is_deleted = false', { tenantId })
      .andWhere('v.destination_id = :destinationId', { destinationId })
      .getRawMany();
    return result.map((r: any) => r.cab_type).filter(Boolean);
  }
}