import {
  Injectable,
  NotFoundException,
  ConflictException,
  BadRequestException,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { FindOptionsWhere, Repository, IsNull, In } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { SupplierCostPriceRepository } from './repositories/supplier-cost-price.repository';
import {
  CreateSupplierCostPriceDto,
  UpdateSupplierCostPriceDto,
  BulkApplySupplierCostPriceDto,
} from './dto';
import {
  SupplierCostPriceEntity,
  SupplierCostPriceMode,
} from '../../entities/supplier-cost-price.entity';
import { TransportServiceEntity } from '../../entities/transport-service.entity';
import { VehicleTypeEntity } from '../../entities/vehicle-type.entity';
import { SeasonEntity } from '../../entities/season.entity';
import { SupplierEntity } from '../../entities/supplier.entity';
import { CLS_TENANT_ID, CLS_USER_ID } from '../../common/cls/cls.constants';
import { findMatchingSeasonId } from '../../common/utils/season-date';

/**
 * Mode-aware identity resolver — SIC rows always carry
 * vehicle_type_id=NULL even when the client passes one in. This
 * matches the schema's partial-unique-index assumption and keeps
 * the upsert key consistent across CRUD and bulk-apply paths.
 */
function resolveCostIdentity(input: { mode?: 'PVT' | 'SIC'; vehicle_type_id?: string | null }) {
  const mode: SupplierCostPriceMode =
    input.mode === 'SIC' ? SupplierCostPriceMode.SIC : SupplierCostPriceMode.PVT;
  const vehicleTypeId = mode === SupplierCostPriceMode.SIC ? null : (input.vehicle_type_id ?? null);
  return { mode, vehicleTypeId };
}

/**
 * SupplierCostPriceService — CRUD + bulk-apply for the cost-side of
 * the transport pricing matrix. The selling side stays on the
 * existing transport_pricing table; this service is the flip-side
 * used by the quote editor's selling-below-cost warning path and by
 * cost reports.
 */
@Injectable()
export class SupplierCostPriceService {
  constructor(
    private readonly repo: SupplierCostPriceRepository,
    private readonly cls: ClsService,
    @InjectRepository(TransportServiceEntity)
    private readonly transportServiceRepo: Repository<TransportServiceEntity>,
    @InjectRepository(VehicleTypeEntity)
    private readonly vehicleTypeRepo: Repository<VehicleTypeEntity>,
    @InjectRepository(SeasonEntity)
    private readonly seasonRepo: Repository<SeasonEntity>,
    @InjectRepository(SupplierEntity)
    private readonly supplierRepo: Repository<SupplierEntity>,
  ) {}

  private tenantId(): string {
    return this.cls.get<string>(CLS_TENANT_ID);
  }
  private userId(): string | null {
    return this.cls.get<string>(CLS_USER_ID) || null;
  }

  async findBySupplier(supplier_id: string) {
    return this.repo.findBySupplier(supplier_id);
  }

  async findBestMatch(args: {
    supplier_id: string;
    transport_service_id: string;
    vehicle_type_id?: string | null;
    mode?: 'PVT' | 'SIC';
    season_id?: string | null;
    /** When set and season_id is omitted, resolve the destination
     *  season that contains this YYYY-MM-DD and prefer that cost
     *  row (all-season fallback stays inside findBestMatch). */
    date?: string | null;
  }) {
    const { mode, vehicleTypeId } = resolveCostIdentity({
      mode: args.mode,
      vehicle_type_id: args.vehicle_type_id,
    });
    const seasonId =
      args.season_id ||
      (args.date
        ? await this.resolveSeasonIdForServiceDate(args.transport_service_id, args.date)
        : null);
    return this.repo.findBestMatch({
      supplier_id: args.supplier_id,
      transport_service_id: args.transport_service_id,
      vehicle_type_id: vehicleTypeId,
      mode,
      season_id: seasonId,
    });
  }

  /**
   * Map a travel date to the destination season that contains it.
   * Tenant-scoped via the transport service row — a missing / other-
   * tenant service returns null so lookup falls through to all-season.
   */
  private async resolveSeasonIdForServiceDate(
    transportServiceId: string,
    date: string,
  ): Promise<string | null> {
    const tenantId = this.tenantId();
    const service = await this.transportServiceRepo.findOne({
      where: { id: transportServiceId, tenant_id: tenantId, is_deleted: false },
      select: { id: true, destination_id: true },
    });
    if (!service?.destination_id) return null;
    const seasons = await this.seasonRepo.find({
      where: {
        destination_id: service.destination_id,
        tenant_id: tenantId,
        is_deleted: false,
        is_active: true,
      },
    });
    return findMatchingSeasonId(seasons, date);
  }

  /**
   * Cost must stay at or under the admin floor for the same route,
   * mode, vehicle (PVT) or shared coach (SIC), and season. A missing
   * floor means no cap. Compared in the destination currency.
   */
  private async assertCostsWithinFloor(
    combos: Array<{
      transport_service_id: string;
      vehicle_type_id: string | null;
      mode: 'PVT' | 'SIC';
      season_id: string | null;
      cost_price: number;
    }>,
  ): Promise<void> {
    if (combos.length === 0) return;
    const tenantId = this.tenantId();
    const serviceIds = [...new Set(combos.map((c) => c.transport_service_id))];
    const rows = (await this.transportServiceRepo.manager.query(
      `
      SELECT
        tp.transport_service_id,
        tp.vehicle_type_id,
        tp.mode,
        tp.floor_default_price,
        tp.floor_season_1_price,
        tp.floor_season_2_price,
        tp.floor_season_3_price,
        tp.floor_season_4_price,
        tp.floor_season_5_price,
        sr.id AS season_id,
        sr.season_index
      FROM transport_pricing tp
      JOIN transport_services ts
        ON ts.id = tp.transport_service_id AND ts.tenant_id = tp.tenant_id
      JOIN destinations d
        ON d.id = ts.destination_id AND d.currency_id = tp.currency_id
      LEFT JOIN (
        SELECT destination_id, id,
               ROW_NUMBER() OVER (
                 PARTITION BY destination_id
                 ORDER BY sort_order ASC NULLS LAST, name ASC
               ) AS season_index
          FROM seasons
         WHERE tenant_id = $1
           AND is_deleted = false
           AND is_active = true
      ) sr ON sr.destination_id = ts.destination_id
      WHERE tp.tenant_id = $1
        AND tp.is_deleted = false
        AND tp.transport_service_id = ANY($2::uuid[])
      `,
      [tenantId, serviceIds],
    )) as Array<{
      transport_service_id: string;
      vehicle_type_id: string | null;
      mode: string;
      floor_default_price: string | null;
      floor_season_1_price: string | null;
      floor_season_2_price: string | null;
      floor_season_3_price: string | null;
      floor_season_4_price: string | null;
      floor_season_5_price: string | null;
      season_id: string | null;
      season_index: string | null;
    }>;

    const floors = new Map<string, { fallback: number | null; bySeason: Map<string, number | null> }>();
    for (const row of rows) {
      const key = `${row.transport_service_id}|${row.vehicle_type_id ?? ''}|${row.mode}`;
      let entry = floors.get(key);
      if (!entry) {
        entry = {
          fallback: row.floor_default_price == null ? null : Number(row.floor_default_price),
          bySeason: new Map(),
        };
        floors.set(key, entry);
      }
      if (row.season_id && row.season_index) {
        const seasonal = row[`floor_season_${row.season_index}_price` as keyof typeof row] as
          | string
          | null;
        entry.bySeason.set(row.season_id, seasonal == null ? null : Number(seasonal));
      }
    }

    let breaches = 0;
    let lowestFloor = Number.POSITIVE_INFINITY;
    for (const combo of combos) {
      const key = `${combo.transport_service_id}|${combo.vehicle_type_id ?? ''}|${combo.mode}`;
      const entry = floors.get(key);
      if (!entry) continue;
      const floor = combo.season_id
        ? (entry.bySeason.get(combo.season_id) ?? null)
        : entry.fallback;
      if (floor == null) continue;
      if (combo.cost_price > floor + 0.005) {
        breaches += 1;
        if (floor < lowestFloor) lowestFloor = floor;
      }
    }
    if (breaches > 0) {
      const floorLabel = Number.isFinite(lowestFloor) ? lowestFloor.toFixed(2) : '';
      throw new BadRequestException(
        breaches === 1
          ? `Cost price cannot be greater than the floor price (${floorLabel})`
          : `Cost price cannot be greater than the floor price on ${breaches} cells (lowest floor ${floorLabel})`,
      );
    }
  }

  /**
   * Upsert on the unique index — create a new row if the combo is
   * fresh; update the cost when the (supplier × service × type ×
   * season) tuple already exists. Stakeholder's bulk-apply paths
   * rely on this being idempotent.
   */
  async upsert(dto: CreateSupplierCostPriceDto): Promise<{ id: string; created: boolean }> {
    const tenantId = this.tenantId();
    const userId = this.userId();
    const { mode, vehicleTypeId } = resolveCostIdentity(dto);
    await this.assertCostsWithinFloor([
      {
        transport_service_id: dto.transport_service_id,
        vehicle_type_id: vehicleTypeId,
        mode,
        season_id: dto.season_id ?? null,
        cost_price: Number(dto.cost_price),
      },
    ]);

    // Mode-aware lookup. SIC rows have vehicle_type_id IS NULL, and
    // straight `null` would translate to `= NULL` (always false in
    // SQL three-valued logic) — IsNull() yields the correct
    // `IS NULL` predicate.
    const existing = await this.repo.ormRepo.findOne({
      where: {
        tenant_id: tenantId,
        is_deleted: false,
        supplier_id: dto.supplier_id,
        transport_service_id: dto.transport_service_id,
        vehicle_type_id: vehicleTypeId === null ? IsNull() : vehicleTypeId,
        mode,
        season_id: dto.season_id ?? IsNull(),
      },
    });
    if (existing) {
      await this.repo.ormRepo.update(existing.id, {
        cost_price: dto.cost_price,
        currency_id: dto.currency_id ?? existing.currency_id,
        notes: dto.notes ?? existing.notes,
        is_active: dto.is_active ?? existing.is_active,
        updated_by: userId,
      });
      return { id: existing.id, created: false };
    }

    const row = await this.repo.save({
      tenant_id: tenantId,
      supplier_id: dto.supplier_id,
      transport_service_id: dto.transport_service_id,
      vehicle_type_id: vehicleTypeId,
      mode,
      season_id: dto.season_id ?? null,
      cost_price: dto.cost_price,
      currency_id: dto.currency_id ?? null,
      notes: dto.notes ?? null,
      is_active: dto.is_active ?? true,
      created_by: userId,
      updated_by: userId,
    });
    return { id: row.id, created: true };
  }

  async update(id: string, dto: UpdateSupplierCostPriceDto) {
    const tenantId = this.tenantId();
    const row = await this.repo.ormRepo.findOne({
      where: { id, tenant_id: tenantId, is_deleted: false },
    });
    if (!row) throw new NotFoundException('Cost price not found');
    const touchesPrice =
      dto.cost_price !== undefined ||
      dto.transport_service_id !== undefined ||
      dto.vehicle_type_id !== undefined ||
      dto.mode !== undefined ||
      dto.season_id !== undefined;
    const nextMode = dto.mode === 'SIC' ? 'SIC' : dto.mode === 'PVT' ? 'PVT' : row.mode;
    const nextVehicle =
      nextMode === 'SIC' ? null : (dto.vehicle_type_id ?? row.vehicle_type_id);
    if (touchesPrice) {
      await this.assertCostsWithinFloor([
        {
          transport_service_id: dto.transport_service_id ?? row.transport_service_id,
          vehicle_type_id: nextVehicle,
          mode: nextMode === 'SIC' ? 'SIC' : 'PVT',
          season_id: dto.season_id !== undefined ? (dto.season_id ?? null) : row.season_id,
          cost_price: Number(dto.cost_price ?? row.cost_price),
        },
      ]);
    }
    const updateData: Partial<SupplierCostPriceEntity> = { updated_by: this.userId() };
    for (const f of [
      'cost_price',
      'currency_id',
      'notes',
      'is_active',
      'season_id',
      'vehicle_type_id',
      'transport_service_id',
    ] as const) {
      const value = (dto as Record<string, unknown>)[f];
      if (value !== undefined) {
        (updateData as Record<string, unknown>)[f] = value;
      }
    }
    try {
      await this.repo.ormRepo.update(id, updateData);
    } catch (e: unknown) {
      const message = e instanceof Error ? e.message : String(e);
      if (message.includes('duplicate')) {
        throw new ConflictException('Another cost-price row already covers that combination');
      }
      throw e;
    }
    return { id, message: 'Cost price updated' };
  }

  async remove(id: string) {
    const tenantId = this.tenantId();
    const row = await this.repo.ormRepo.findOne({
      where: { id, tenant_id: tenantId, is_deleted: false },
    });
    if (!row) throw new NotFoundException('Cost price not found');
    await this.repo.softDelete(id);
    return { message: 'Cost price removed' };
  }

  /**
   * Routes ticked on the supplier form. Empty assignment means this
   * supplier has no cost-price surface — we do not fall back to every
   * destination (or tenant) route.
   */
  private async linkedServiceIdsForSupplier(
    supplierId: string,
    tenantId: string,
  ): Promise<string[]> {
    const supplier = await this.supplierRepo.findOne({
      where: { id: supplierId, tenant_id: tenantId, is_deleted: false },
      select: { id: true, linked_transport_services: true },
    });
    return supplier?.linked_transport_services ?? [];
  }

  /**
   * Bulk-apply — upsert the same cost_price across every combination
   * of the scoped ids.
   *
   * Service scope: explicit `transport_service_ids`, else the
   * supplier's linked routes. Empty linked list → applied=0 (no
   * tenant-wide fan-out).
   * Vehicle-type scope: empty list still means "all active types"
   * (PVT only).
   */
  async bulkApply(dto: BulkApplySupplierCostPriceDto): Promise<{ applied: number }> {
    const tenantId = this.tenantId();
    const mode: SupplierCostPriceMode =
      dto.mode === 'SIC' ? SupplierCostPriceMode.SIC : SupplierCostPriceMode.PVT;

    // Resolve the effective service id list. Prefer the caller's
    // explicit ids (the FE matrix passes the assigned routes). When
    // omitted, fall back to the supplier's linked_transport_services
    // — never every active route in the tenant.
    const services = dto.transport_service_ids?.length
      ? dto.transport_service_ids
      : await this.linkedServiceIdsForSupplier(dto.supplier_id, tenantId);
    if (services.length === 0) return { applied: 0 };

    // For SIC the vehicle-type loop collapses to a single [null]
    // pass — pricing has no vehicle axis. PVT keeps the original
    // matrix shape (every vehicle type the user scoped, or every active
    // type if they left the field empty).
    const vehicleTypes: (string | null)[] =
      mode === SupplierCostPriceMode.SIC
        ? [null]
        : dto.vehicle_type_ids?.length
          ? dto.vehicle_type_ids
          : (
              await this.vehicleTypeRepo.find({
                where: { tenant_id: tenantId, is_deleted: false, is_active: true },
                select: {
                  id: true,
                },
              })
            ).map((t) => t.id);

    // Resolve the season-id list to upsert against.
    //   - season_id explicitly set → write a single row at that season
    //     (existing per-season behaviour).
    //   - season_id null → "All seasons (default)" in the UI. Fan out
    //     across every active season configured for the destinations
    //     of the in-scope services, so the matrix's seasoned columns
    //     (Peak / High Shoulder / Low Shoulder / Off-Peak) all render
    //     the bulk-applied value. Pre-fix this wrote a single
    //     NULL-season fallback row that never appeared in the matrix
    //     (only seasoned columns are drawn) — toast claimed N applied
    //     but ops saw zero cells change.
    //   - season_id null AND no seasons configured for the
    //     destination → fall back to the legacy NULL-season write so
    //     tenants who don't use seasons still get a value persisted.
    let seasonIds: (string | null)[];
    if (dto.season_id) {
      seasonIds = [dto.season_id];
    } else {
      // Look up destinations for all in-scope services to scope the
      // season fan-out tightly. Same tenant; the index on
      // (tenant_id, destination_id, name) keeps the query cheap.
      const svcRows = await this.transportServiceRepo.find({
        where: { tenant_id: tenantId, is_deleted: false },
        select: {
          id: true,
          destination_id: true,
        },
      });
      const destIds = Array.from(
        new Set(svcRows.filter((s) => services.includes(s.id)).map((s) => s.destination_id)),
      );
      const seasonsForScope =
        destIds.length > 0
          ? await this.seasonRepo.find({
              where: destIds.map((dId): FindOptionsWhere<SeasonEntity> => ({
                tenant_id: tenantId,
                destination_id: dId,
                is_deleted: false,
                is_active: true,
              })),
              select: {
                id: true,
              },
            })
          : [];
      seasonIds = seasonsForScope.length > 0 ? seasonsForScope.map((s) => s.id) : [null]; // legacy fallback: NULL-season row when destination has no seasons
    }

    await this.assertCostsWithinFloor(
      services.flatMap((svc) =>
        vehicleTypes.flatMap((vt) =>
          seasonIds.map((seasonId) => ({
            transport_service_id: svc,
            vehicle_type_id: vt,
            mode: mode === SupplierCostPriceMode.SIC ? ('SIC' as const) : ('PVT' as const),
            season_id: seasonId,
            cost_price: Number(dto.cost_price),
          })),
        ),
      ),
    );

    // Batched upsert. Pre-fix this loop did one findOne + one
    // update/save per (svc × vt × season) combo — for a typical
    // supplier with 30 routes × 5 vehicle types × 4 seasons that's
    // 600 sequential round-trips inside one transaction. Bulk apply
    // took 5–10 seconds on the dev server.
    //
    // New shape: ONE select for all candidate existing rows, then
    // chunked bulk INSERT for new rows + a single UPDATE for the
    // existing ones. Same atomicity (still inside the transaction),
    // same final result, ~50× fewer round-trips.
    const userId = this.userId();
    let applied = 0;
    await this.repo.ormRepo.manager.transaction(async (manager) => {
      const txRepo = manager.getRepository(SupplierCostPriceEntity);

      // 1) Pull every existing row for this supplier × scope in one
      //    query. Mode + supplier are the tight scope; we filter
      //    services in-memory below since `services` is the upsert
      //    universe and `In(services)` keeps the SQL bounded.
      const existingRows = await txRepo.find({
        where: {
          tenant_id: tenantId,
          is_deleted: false,
          supplier_id: dto.supplier_id,
          transport_service_id: In(services),
          mode,
        },
      });

      // Composite key matches the FE matrix's keying: nulls for SIC
      // vehicle and all-season fallback are sentinels, not UUIDs.
      const keyOf = (svc: string, vtId: string | null, seasonId: string | null) =>
        `${svc}|${vtId ?? '_'}|${seasonId ?? '_'}`;
      const existingByKey = new Map<string, SupplierCostPriceEntity>();
      for (const r of existingRows) {
        existingByKey.set(keyOf(r.transport_service_id, r.vehicle_type_id, r.season_id), r);
      }

      // 2) Partition the (svc × vt × season) cartesian into two
      //    buckets: rows to UPDATE (already present) and rows to
      //    INSERT (new).
      const updateIds: string[] = [];
      const inserts: Partial<SupplierCostPriceEntity>[] = [];
      for (const svc of services) {
        for (const vt of vehicleTypes) {
          for (const seasonId of seasonIds) {
            const existing = existingByKey.get(keyOf(svc, vt, seasonId));
            if (existing) {
              updateIds.push(existing.id);
            } else {
              inserts.push({
                tenant_id: tenantId,
                supplier_id: dto.supplier_id,
                transport_service_id: svc,
                vehicle_type_id: vt,
                mode,
                season_id: seasonId,
                cost_price: dto.cost_price,
                // Currency on insert: explicit dto value, else null.
                // (Existing rows preserve their currency on update
                //  unless dto.currency_id is set — see UPDATE below.)
                currency_id: dto.currency_id ?? null,
                is_active: true,
                created_by: userId,
                updated_by: userId,
              });
            }
            applied += 1;
          }
        }
      }

      // 3) Bulk UPDATE existing rows to the new cost_price. Currency
      //    is only overwritten when the caller explicitly passed one;
      //    otherwise each row keeps its previous currency_id (matches
      //    the pre-batch behaviour `dto.currency_id ?? existing.currency_id`).
      if (updateIds.length > 0) {
        const patch: Partial<SupplierCostPriceEntity> = {
          cost_price: dto.cost_price,
          updated_by: userId,
        };
        if (dto.currency_id) patch.currency_id = dto.currency_id;
        await txRepo.update({ id: In(updateIds) }, patch);
      }

      // 4) Bulk INSERT new rows. Postgres caps parameters per
      //    statement at ~65k; chunking at 500 rows × ~10 params each
      //    keeps every chunk well under the ceiling and avoids a
      //    single oversized INSERT for very wide matrices.
      if (inserts.length > 0) {
        const CHUNK = 500;
        for (let i = 0; i < inserts.length; i += CHUNK) {
          await txRepo.insert(inserts.slice(i, i + CHUNK));
        }
      }
    });
    return { applied };
  }
}