import { PaginatedResponse } from '@edr/types';
import { Injectable } from '@nestjs/common';
import { DataSource, FindManyOptions, Repository } from 'typeorm';
import { paginateQuery } from '../../../common/utils/pagination.util';
import { ListRatesQueryDto } from '../dto/list-rule-engine-query.dto';
import { Rate } from '../entities/rate.entity';
import { IRatesRepository } from '../interfaces/rates.repository.interface';

@Injectable()
export class RatesRepository implements IRatesRepository {
  private readonly repo: Repository<Rate>;

  constructor(private readonly dataSource: DataSource) {
    this.repo = this.dataSource.getRepository(Rate);
  }

  findById(id: string): Promise<Rate | null> {
    return this.repo.findOne({ where: { id } });
  }

  /**
   * Every LIVE rate, newest first. The ordering is load-bearing: pricing picks
   * the first match for a pattern, so without it Postgres heap order decided
   * which of two overlapping rates a booking was billed at. Newest-first also
   * means the most recent configuration wins where legacy overlaps still exist.
   */
  findLiveRates(): Promise<Rate[]> {
    return this.repo
      .createQueryBuilder('rate')
      .where('rate.status = :status', { status: 'LIVE' })
      .orderBy('rate.created_at', 'DESC')
      .getMany();
  }

  findLiveRatesDetailed(): Promise<Rate[]> {
    return this.repo
      .createQueryBuilder('rate')
      .leftJoinAndSelect('rate.originYard', 'originYard')
      .leftJoinAndSelect('rate.destinationYard', 'destinationYard')
      .leftJoinAndSelect('rate.containerType', 'containerType')
      .leftJoinAndSelect('rate.cargoType', 'cargoType')
      .where('rate.status = :status', { status: 'LIVE' })
      .orderBy('rate.appliesTo', 'ASC')
      .addOrderBy('rate.tradeDirection', 'ASC')
      .addOrderBy('originYard.label', 'ASC')
      .addOrderBy('destinationYard.label', 'ASC')
      .addOrderBy('rate.rateValue', 'ASC')
      .getMany();
  }

  /**
   * Find a non-superseded rate matching an identity pattern — the same tuple the
   * `UQ_rates_pattern` unique index enforces. Used to reject duplicates before
   * insert so the admin gets a friendly error instead of a raw constraint fault.
   * NULL scope columns are matched with IS NULL, mirroring the COALESCE index.
   */
  /**
   * The live/draft rate already covering a pricing pattern, if any.
   *
   * `rateUnit` is optional on purpose. Where pricing resolves ONE rate for a
   * lane (base freight, customs, lashing, empty return) the unit is not part of
   * the identity — a per-container and a per-wagon row for the same lane are
   * two answers to one question and the engine picks whichever came back first,
   * so the caller omits it and the second row is rejected. Additive surcharges
   * (hazard, reefer, demurrage…) are the opposite: the engine bills every
   * matching rate by its own unit, so one per freight shape is the design and
   * the caller passes the unit to keep them apart.
   */
  findByPattern(pattern: {
    rateType: string;
    rateUnit?: string;
    containerTypeId?: string | null;
    cargoTypeId?: string | null;
    tradeDirection?: string | null;
    originYardId?: string | null;
    destinationYardId?: string | null;
  }): Promise<Rate | null> {
    const qb = this.repo
      .createQueryBuilder('rate')
      .where('rate.rate_type = :rateType', { rateType: pattern.rateType })
      .andWhere('rate.status <> :superseded', { superseded: 'SUPERSEDED' });

    if (pattern.rateUnit) {
      qb.andWhere('rate.rate_unit = :rateUnit', { rateUnit: pattern.rateUnit });
    }

    if (pattern.containerTypeId) {
      qb.andWhere('rate.container_type_id = :containerTypeId', { containerTypeId: pattern.containerTypeId });
    } else {
      qb.andWhere('rate.container_type_id IS NULL');
    }
    if (pattern.cargoTypeId) {
      qb.andWhere('rate.cargo_type_id = :cargoTypeId', { cargoTypeId: pattern.cargoTypeId });
    } else {
      qb.andWhere('rate.cargo_type_id IS NULL');
    }
    if (pattern.tradeDirection) {
      qb.andWhere('rate.trade_direction = :tradeDirection', { tradeDirection: pattern.tradeDirection });
    } else {
      qb.andWhere('rate.trade_direction IS NULL');
    }
    if (pattern.originYardId) {
      qb.andWhere('rate.origin_yard_id = :originYardId', { originYardId: pattern.originYardId });
    } else {
      qb.andWhere('rate.origin_yard_id IS NULL');
    }
    if (pattern.destinationYardId) {
      qb.andWhere('rate.destination_yard_id = :destinationYardId', {
        destinationYardId: pattern.destinationYardId,
      });
    } else {
      qb.andWhere('rate.destination_yard_id IS NULL');
    }

    return qb.getOne();
  }

  findAll(options?: FindManyOptions<Rate>): Promise<Rate[]> {
    return this.repo.find(options);
  }

  findAndCount(options?: FindManyOptions<Rate>): Promise<[Rate[], number]> {
    return this.repo.findAndCount(options);
  }

  /** Paged list with server-side search (type/status/unit/currency), newest first. */
  findPaged(query: ListRatesQueryDto): Promise<PaginatedResponse<Rate>> {
    const qb = this.repo
      .createQueryBuilder('rate')
      // The admin table shows the leg a base-freight rate prices — without the
      // yards joined the route columns have only ids to render.
      .leftJoinAndSelect('rate.originYard', 'originYard')
      .leftJoinAndSelect('rate.destinationYard', 'destinationYard')
      .orderBy('rate.createdAt', query.sortOrder ?? 'DESC');

    if (query.status) {
      qb.andWhere('rate.status = :status', { status: query.status });
    }
    if (query.rateType) {
      qb.andWhere('rate.rateType = :rateType', { rateType: query.rateType });
    }
    // Category tabs on the admin page: comma-separated appliesTo / trigger
    // lists, ANDed together (e.g. appliesTo=OTHER + trigger=CUSTOMS_CLEARANCE).
    const csv = (v?: string) =>
      (v ?? '')
        .split(',')
        .map((s) => s.trim())
        .filter(Boolean);
    const appliesTo = csv(query.appliesTo);
    if (appliesTo.length > 0) {
      qb.andWhere('rate.appliesTo IN (:...appliesTo)', { appliesTo });
    }
    const triggers = csv(query.trigger);
    if (triggers.length > 0) {
      qb.andWhere('rate.trigger IN (:...triggers)', { triggers });
    }
    if (query.search) {
      qb.andWhere(
        '(rate.rateType ILIKE :search OR rate.status ILIKE :search OR rate.rateUnit ILIKE :search OR rate.currency ILIKE :search)',
        { search: `%${query.search}%` },
      );
    }

    return paginateQuery(qb, query);
  }

  async create(data: Partial<Rate>): Promise<Rate> {
    const entity = this.repo.create(data);
    return this.repo.save(entity);
  }

  async update(id: string, data: Partial<Rate>): Promise<Rate | null> {
    await this.repo.update(id, data as never);
    return this.findById(id);
  }

  async softDelete(id: string): Promise<void> {
    await this.repo.softDelete(id);
  }
}
