import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, IsNull, FindOptionsWhere } 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: FindOptionsWhere<TransportPricingEntity> = {
      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: true,
        vehicle_type: true,
        currency: true,
      },
      order: { category: 'ASC', vehicle_type_id: '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
    // GROUP BY achieves DISTINCT while letting us aggregate MAX(created_at)
    // for the newest-first ordering below.
    const routeQb = this.repo
      .createQueryBuilder('tp')
      .select('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}%` },
        );
      }
    }

    // Sort newest first — pricing rows scaffold-create on service add,
    // so newly-created routes used to land somewhere random in the
    // service_id-sorted list (UUIDs are unordered) and ops had to page
    // through dozens of rows to find them. Ordering by created_at DESC
    // puts new services on page 1. transport_service_id stays in the
    // SELECT so DISTINCT still groups identically; we also include it
    // in ORDER BY as a stable tiebreaker for rows created in the same
    // millisecond (seeder runs).
    routeQb.addSelect('MAX(tp.created_at)', 'tp_created_at');
    routeQb.groupBy('tp.transport_service_id');
    const routeIds = await routeQb
      .orderBy('tp_created_at', 'DESC')
      .addOrderBy('tp.transport_service_id', 'ASC')
      .offset((page - 1) * limit)
      .limit(limit)
      .getRawMany();

    const serviceIds = routeIds.map(
      (r: { transport_service_id: string }) => 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: true,
        vehicle_type: true,
        currency: true,
      },
      order: { category: 'ASC', vehicle_type_id: 'ASC' },
    });

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

  /**
   * findExisting — locates the canonical row for an upsert by
   * (service, vehicle_type_id|NULL, mode, currency). SIC rows store
   * vehicle_type_id=NULL, so the lookup uses IsNull() rather than
   * `= null` which TypeORM would translate to `vehicle_type_id = NULL`
   * (always false in SQL three-valued logic).
   *
   * Defaults: mode='PVT' when omitted so callers from the pre-mode
   * era continue to find their PVT rows untouched.
   */
  async findExisting(
    transportServiceId: string,
    vehicleTypeId: string | null | undefined,
    currencyId: string,
    mode: 'PVT' | 'SIC' = 'PVT',
  ): Promise<TransportPricingEntity | null> {
    const tenantId = this.getTenantId();
    const where: FindOptionsWhere<TransportPricingEntity> = {
      transport_service_id: transportServiceId,
      currency_id: currencyId,
      tenant_id: tenantId,
      is_deleted: false,
      mode: mode as TransportPricingEntity['mode'],
    };
    where.vehicle_type_id = mode === 'SIC' || vehicleTypeId == null ? IsNull() : vehicleTypeId;
    return this.repo.findOne({ where });
  }

  async findSoftDeleted(
    transportServiceId: string,
    vehicleTypeId: string | null | undefined,
    currencyId: string,
    mode: 'PVT' | 'SIC' = 'PVT',
  ): Promise<TransportPricingEntity | null> {
    const tenantId = this.getTenantId();
    const where: FindOptionsWhere<TransportPricingEntity> = {
      transport_service_id: transportServiceId,
      currency_id: currencyId,
      tenant_id: tenantId,
      is_deleted: true,
      mode: mode as TransportPricingEntity['mode'],
    };
    where.vehicle_type_id = mode === 'SIC' || vehicleTypeId == null ? IsNull() : vehicleTypeId;
    return this.repo.findOne({ where });
  }

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

  async findAllForExport(destinationId?: string): Promise<TransportPricingEntity[]> {
    const tenantId = this.getTenantId();
    // Narrow to a single destination when the caller supplies one so the
    // Download Sample button honours the workspace destination filter
    // — UI would otherwise download every destination's rows regardless
    // of what's selected in the top header.
    //
    // Sample order matches the on-screen TransportPricingPage grouping:
    //   route (from_city → to_city) ASC → category ASC (PVT before SIC)
    //   → vehicle type name ASC. Previously the export sorted by
    //   category + vehicle_type_id (UUID), producing an effectively
    //   random-looking order that UAT 2026-06-30 flagged.
    const qb = this.repo
      .createQueryBuilder('tp')
      .leftJoinAndSelect('tp.transport_service', 'transport_service')
      .leftJoinAndSelect('tp.vehicle_type', 'vehicle_type')
      .leftJoinAndSelect('tp.currency', 'currency')
      .where('tp.tenant_id = :tenantId', { tenantId })
      .andWhere('tp.is_deleted = false');
    if (destinationId) {
      qb.andWhere('tp.destination_id = :destinationId', { destinationId });
    }
    qb.orderBy('transport_service.from_city', 'ASC')
      .addOrderBy('transport_service.to_city', 'ASC')
      .addOrderBy('tp.category', 'ASC')
      .addOrderBy('vehicle_type.name', 'ASC', 'NULLS LAST');
    return qb.getMany();
  }

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