import { Injectable, ConflictException, NotFoundException, BadRequestException, Logger } from '@nestjs/common';
import { PrismaService } from '../../common/prisma.service';
import { HoldSeatsDto, JourneyDirection } from './seats.dto';
import { Cron, CronExpression } from '@nestjs/schedule';
import { SegmentsService } from '../segments/segments.service';
import { SystemConfigService, CONFIG_KEYS } from '../system-config/system-config.service';
import { AuditService } from '../../common/audit.service';
import { SmsClientService } from '../notifications/sms-client.service';
import { computePaymentDeadline } from '../../common/utils/payment-deadline.utils';
import { checkDirectionConflict } from '../../common/utils/journey-direction.utils';

@Injectable()
export class SeatsService {
  private readonly logger = new Logger(SeatsService.name);

  constructor(
    private prisma: PrismaService,
    private segmentsService: SegmentsService,
    private systemConfig: SystemConfigService,
    private auditService: AuditService,
    private sms: SmsClientService,
  ) {}

  async getSeatMap(scheduleId: string, coachTypeId?: string, journeyDirection?: JourneyDirection, originStationId?: string, destinationStationId?: string) {
    const schedule = await this.prisma.trainSchedule.findUnique({
      where: { id: scheduleId },
      select: { originStationId: true, destinationStationId: true },
    });
    if (!schedule) throw new NotFoundException('Schedule not found');

    const assignments = await this.prisma.coachAssignment.findMany({
      where: {
        scheduleId,
        ...(coachTypeId ? { coach: { coachTypeId } } : {}),
      },
      include: {
        coach: {
          include: {
            seats: { orderBy: [{ row: 'asc' }, { col: 'asc' }] },
            coachType: { include: { seatClasses: true } },
          },
        },
      },
      orderBy: { positionNumber: 'asc' },
    });

    const allSeatIds = assignments.flatMap((a: any) => a.coach.seats.map((s: any) => s.id));
    const effectiveStatuses = await this.resolveEffectiveStatuses(
      scheduleId, 
      allSeatIds, 
      originStationId ?? schedule.originStationId, 
      destinationStationId ?? schedule.destinationStationId,
      journeyDirection
    );

    return {
      coaches: assignments.map((a) => {
        const allSeats   = a.coach.seats;
        const coachTypeName = a.coach.coachType?.name ?? '';
        const seatClassNames = a.coach.coachType.seatClasses.map((sc: any) => sc.name);
        const isBedCoach = this.isBedCoach(coachTypeName);
        // Compute actual beds-per-room from first room to correctly identify VIP (4) vs Economy (6)
        const bedsPerRoom = isBedCoach
          ? allSeats.filter((s: any) => s.row === (allSeats[0] as any)?.row).length
          : 0;
        const bedCategory = isBedCoach ? this.getBedCategory(coachTypeName, bedsPerRoom) : null;

        const mappedSeats = allSeats.map((s: any) => {
          const resolvedBedPosition = isBedCoach
            ? this.resolveBedPosition(s.col, s.bedPosition)
            : s.bedPosition;
          const effectiveStatus = effectiveStatuses.get(s.id) ?? (s.status === 'BLOCKED' || s.status === 'BOOKED' ? s.status : 'AVAILABLE');
          return {
            id:          s.id,
            seatNumber:  s.seatNumber,
            label:       s.seatNumber,
            status:      effectiveStatus,
            kind:        s.kind,
            row:         s.row,
            col:         s.col,
            isWindow:    s.isWindow,
            isAisle:     s.isAisle,
            bedPosition: resolvedBedPosition,
            // Bed-specific fields (only when coach is a bed coach)
            ...(isBedCoach ? {
              room_id:  `${a.coach.id}-R${s.row}`,
              category: bedCategory,
              position: this.colToPosition(s.col, a.coach.arrangement),
              bed_type: this.bedPositionToType(resolvedBedPosition),
            } : {}),
          };
        });

        const base = {
          id:             a.coach.id,
          assignmentId:   a.id,
          coachNumber:    a.coach.number,
          label:          a.coach.number,
          mode:           a.coach.status,
          name:           `Coach ${a.coach.number}`,
          coachTypeId:    a.coach.coachType?.id ?? null,
          coachTypeName,
          isBedCoach,
          bedCategory,
          seatClasses:    seatClassNames,
          seatClass:      seatClassNames.length > 0 ? seatClassNames[0] : 'Standard',
          positionNumber: a.positionNumber,
          seatArrangement: a.coach.arrangement,
          totalSeats:     a.coach.capacity,
        };

        if (isBedCoach) {
          // Group seats into rooms; row = room number
          const roomMap = new Map<number, any[]>();
          for (const seat of mappedSeats) {
            if (!roomMap.has(seat.row)) roomMap.set(seat.row, []);
            roomMap.get(seat.row)!.push(seat);
          }
          const rooms = Array.from(roomMap.entries())
            .sort(([a], [b]) => a - b)
            .map(([roomNumber, beds]) => ({
              room_id:    `${a.coach.id}-R${roomNumber}`,
              roomNumber,
              category:   bedCategory,
              totalBeds:  beds.length,
              beds,
            }));
          return { ...base, rooms, seats: mappedSeats };
        }

        return { ...base, seats: mappedSeats };
      }),
    };
  }

  private isBedCoach(coachTypeName: string): boolean {
    const n = coachTypeName.toLowerCase();
    return n.includes('bed') || n.includes('berth') || n.includes('sleeper') || n.includes('couchette');
  }

  private getBedCategory(coachTypeName: string, bedsPerRoom?: number): 'ECONOMY_BED' | 'VIP_BED' {
    const n = coachTypeName.toLowerCase();
    // Name-based: VIP / Soft Berth Coach → VIP_BED
    if (n.includes('vip') || n.includes('soft')) return 'VIP_BED';
    // Beds-per-room fallback: 2 or 4 beds per room = VIP, more = Economy
    if (bedsPerRoom != null && bedsPerRoom <= 4) return 'VIP_BED';
    return 'ECONOMY_BED';
  }

  // col format: L1, L2, L3, R1, R2, R3  (new)  or  A, B, C, D  (legacy)
  // arrangement e.g. "2+2", "3+3", "2+0" → "leftCount+rightCount"
  private colToPosition(col: string, arrangement?: string): 'LEFT' | 'RIGHT' | null {
    if (!col) return null;
    // New named-col format: L1, L2, R1, R2 …
    if (/^L\d+$/.test(col)) return 'LEFT';
    if (/^R\d+$/.test(col)) return 'RIGHT';
    // Legacy single-letter cols (A, B, C, D …): derive from arrangement
    const colIndex = col.toUpperCase().charCodeAt(0) - 65; // A=0, B=1, C=2 …
    if (arrangement) {
      const [leftStr, rightStr] = arrangement.split('+');
      const rightCount = parseInt(rightStr ?? '0', 10);
      if (rightCount === 0) return 'LEFT'; // single-side berth coach — all LEFT
      const leftCount = parseInt(leftStr, 10) || 0;
      return colIndex < leftCount ? 'LEFT' : 'RIGHT';
    }
    return 'LEFT'; // safe default when no arrangement info
  }

  private bedPositionToType(bedPosition: string | null): 'LOWER' | 'MIDDLE' | 'UPPER' | null {
    if (!bedPosition) return null;
    const map: Record<string, 'LOWER' | 'MIDDLE' | 'UPPER'> = {
      lower: 'LOWER', middle: 'MIDDLE', upper: 'UPPER',
    };
    return map[bedPosition.toLowerCase()] ?? null;
  }

  // Derives bedPosition from col when the seat was created with legacy A/B/C columns
  // (new coaches use L1/L2/L3/R1/R2/R3 and store bedPosition explicitly).
  // Col-to-tier mapping: A → lower, B → middle, C → upper, D → upper (4-tier).
  private resolveBedPosition(col: string, storedBedPosition: string | null): string | null {
    if (storedBedPosition) return storedBedPosition;
    const legacyMap: Record<string, string> = { A: 'lower', B: 'middle', C: 'upper', D: 'upper' };
    // Also handle numeric suffix in L/R cols: L1→lower, L2→middle, L3→upper
    if (/^[LR]\d+$/.test(col)) {
      const tier = parseInt(col.slice(1), 10);
      if (tier === 1) return 'lower';
      if (tier === 2) return 'middle';
      return 'upper';
    }
    return legacyMap[col?.toUpperCase()] ?? null;
  }

  // Delegates the actual "is this seat held/booked for this leg" determination to
  // SegmentsService.getSeatAvailabilityMap — the same canonical check search results
  // (availabilityByClass) use — so the seatmap and search results can never disagree
  // about seat availability again. Previously this method carried its own
  // separately-written copy of the same hold/JourneySegment-overlap logic.
  async resolveEffectiveStatuses(
    scheduleId: string,
    seatIds: string[],
    originStationId?: string,
    destinationStationId?: string,
    journeyDirection?: JourneyDirection,
  ): Promise<Map<string, string>> {
    const statusMap = new Map<string, string>();
    if (seatIds.length === 0) return statusMap;

    const stopTimes = await this.prisma.tripStopTime.findMany({
      where: { scheduleId },
      select: { stationId: true, sequence: true },
    });

    // No specific leg requested (or it doesn't resolve to real stops on this
    // schedule) — conservatively treat the whole schedule as one big leg, so any
    // resolvable hold/booking anywhere on it blocks these seats. Matches this
    // method's previous behavior when called without origin/destination.
    let reqFrom = -Infinity;
    let reqTo = Infinity;
    if (originStationId && destinationStationId) {
      const seqOf = (id: string) => stopTimes.find(s => s.stationId === id)?.sequence;
      const resolvedFrom = seqOf(originStationId);
      const resolvedTo = seqOf(destinationStationId);
      if (resolvedFrom !== undefined && resolvedTo !== undefined) {
        reqFrom = resolvedFrom;
        reqTo = resolvedTo;
      }
    }

    const [availability, persistedSeats, scheduleBlocks] = await Promise.all([
      this.segmentsService.getSeatAvailabilityMap(
        scheduleId, seatIds, stopTimes, reqFrom, reqTo, journeyDirection || JourneyDirection.ONE_WAY,
      ),
      this.prisma.seat.findMany({
        where: { id: { in: seatIds } },
        select: { id: true, status: true },
      }),
      this.prisma.seatBlock.findMany({
        where: { seatId: { in: seatIds }, scheduleId },
        select: { seatId: true },
      }),
    ]);

    const persistedStatus = new Map(persistedSeats.map(s => [s.id, s.status]));
    const scheduleBlockedIds = new Set(scheduleBlocks.map(b => b.seatId));

    for (const seatId of seatIds) {
      const persisted = persistedStatus.get(seatId);
      // Global BLOCKED/UNDER_MAINTENANCE (no scheduleId) — always honour
      if ((persisted as string) === 'BLOCKED' || (persisted as string) === 'UNDER_MAINTENANCE') {
        statusMap.set(seatId, persisted!);
      } else if (scheduleBlockedIds.has(seatId)) {
        // Schedule-scoped block — only blocked for this schedule
        statusMap.set(seatId, 'BLOCKED');
      } else {
        statusMap.set(seatId, availability.get(seatId) ?? 'AVAILABLE');
      }
    }

    return statusMap;
  }

  async holdSeats(dto: HoldSeatsDto) {
    const passengerIds = dto.passengers.map(p => p.passengerId);
    const seatIds      = dto.passengers.map(p => p.seatId);

    if (new Set(passengerIds).size !== passengerIds.length)
      throw new BadRequestException('Duplicate passengerId in passengers list');
    if (new Set(seatIds).size !== seatIds.length)
      throw new BadRequestException('Duplicate seatId in passengers list');

    const holdMinutes = await this.systemConfig.getNumber(CONFIG_KEYS.SEAT_HOLD_DURATION_MINUTES);
    const expiresAt = new Date(Date.now() + holdMinutes * 60 * 1000);

    const [schedule, originStopTime, originRouteStop] = await Promise.all([
      this.prisma.trainSchedule.findUnique({
        where: { id: dto.scheduleId },
        select: {
          departureAt: true,
          route: { select: { checkinMinutesBefore: true } },
        },
      }),
      this.prisma.tripStopTime.findFirst({
        where: { scheduleId: dto.scheduleId, stationId: dto.originStationId },
        select: { plannedArrivalAt: true, plannedDepartureAt: true },
      }),
      this.prisma.routeStop.findFirst({
        where: {
          route: { schedules: { some: { id: dto.scheduleId } } },
          stationId: dto.originStationId,
        },
        select: { checkinMinutesBefore: true },
      }),
    ]);
    if (!schedule) throw new NotFoundException('Schedule not found');

    // Stop-level override wins; falls back to route-level; then to 30 min.
    const checkinMinutes = originRouteStop?.checkinMinutesBefore ?? schedule.route?.checkinMinutesBefore ?? 30;
    // Departure basis: plannedDepartureAt = arrival + dwell. For the origin there is no
    // arrival so plannedDepartureAt = schedule.departureAt. cutoffAt = departure - dwell = arrival,
    // so holding closes the moment the train reaches the boarding stop.
    const segmentDepartureAt = originStopTime?.plannedDepartureAt ?? originStopTime?.plannedArrivalAt ?? schedule.departureAt;
    const msUntilDeparture = segmentDepartureAt.getTime() - Date.now();
    if (msUntilDeparture <= checkinMinutes * 60 * 1000) {
      throw new BadRequestException(
        `Seats cannot be held within ${checkinMinutes} minute${checkinMinutes !== 1 ? 's' : ''} of departure`,
      );
    }

    const hold = await this.prisma.$transaction(async (tx) => {
      const seats = await tx.seat.findMany({
        where: { id: { in: seatIds } },
        select: { id: true, status: true, seatNumber: true },
      });

      if (seats.length !== seatIds.length) {
        const found = new Set(seats.map(s => s.id));
        const missing = seatIds.filter(id => !found.has(id));
        throw new NotFoundException(`Seat(s) not found: ${missing.join(', ')}`);
      }

      // Only the raw BLOCKED/UNDER_MAINTENANCE status (seat pulled out of service —
      // a genuine cross-schedule flag) is checked here. Seat.status is never written
      // for holds/bookings because coaches are reused across schedules; the
      // schedule-scoped SeatHold/JourneySegment checks below are the authoritative
      // source for whether a seat is taken on this specific schedule/leg.
      const blocked = seats.filter(s => s.status === 'BLOCKED' || (s.status as string) === 'UNDER_MAINTENANCE');
      if (blocked.length > 0)
        throw new ConflictException(`Seat(s) ${blocked.map(s => s.seatNumber).join(', ')} are already taken`);

      const seatLabelById = Object.fromEntries(seats.map(s => [s.id, s.seatNumber]));

      // Schedule-scoped blocks prevent holding a seat on this specific schedule
      // even if its global Seat.status is AVAILABLE.
      const scheduleBlockRecords = await tx.seatBlock.findMany({
        where: { scheduleId: dto.scheduleId, seatId: { in: seatIds } },
        select: { seatId: true },
      });
      if (scheduleBlockRecords.length > 0) {
        const blockedNums = scheduleBlockRecords.map(b => seatLabelById[b.seatId]).join(', ');
        throw new ConflictException(`Seat(s) ${blockedNums} are blocked for this schedule`);
      }

      const stopTimes = await tx.tripStopTime.findMany({
        where: { scheduleId: dto.scheduleId },
        select: { stationId: true, sequence: true },
      });

      // When no stop times exist, fall back to the schedule's own origin/destination
      // with synthetic sequences so the hold can still be created.
      let effectiveStopTimes = stopTimes;
      if (stopTimes.length === 0) {
        const sched = await tx.trainSchedule.findUnique({
          where: { id: dto.scheduleId },
          select: { originStationId: true, destinationStationId: true },
        });
        if (sched) {
          effectiveStopTimes = [
            { stationId: sched.originStationId, sequence: 0 },
            { stationId: sched.destinationStationId, sequence: 1 },
          ];
        }
      }

      const seqOf = (stationId: string) => effectiveStopTimes.find(s => s.stationId === stationId)?.sequence;
      const reqFrom = seqOf(dto.originStationId);
      const reqTo   = seqOf(dto.destinationStationId);

      if (reqFrom === undefined || reqTo === undefined)
        throw new BadRequestException('Origin or destination station not found');
      if (reqFrom >= reqTo)
        throw new BadRequestException('Origin must come before destination');

      const currentDirection = dto.journeyDirection || JourneyDirection.ONE_WAY;
      const activeHolds = await tx.seatHold.findMany({
        where: { scheduleId: dto.scheduleId, expiresAt: { gt: new Date() } },
        select: { seatIds: true, createdBy: true },
      });

      for (const h of activeHolds) {
        const rawSeatIds = h.seatIds as string[];
        let holdDirection = JourneyDirection.ONE_WAY;
        let holdFrom = 0, holdTo = Number.MAX_SAFE_INTEGER;
        let passengerIds: string[] = [];
        let legUnknown = true;

        try {
          if (h.createdBy?.trimStart().startsWith('{')) {
            const meta = JSON.parse(h.createdBy);
            holdFrom = seqOf(meta.originStationId) ?? 0;
            holdTo = seqOf(meta.destinationStationId) ?? Number.MAX_SAFE_INTEGER;
            holdDirection = meta.journeyDirection || JourneyDirection.ONE_WAY;
            passengerIds = (meta.passengers ?? []).map((p: any) => p.passengerId);
            legUnknown = !meta.originStationId || !meta.destinationStationId;
          }
        } catch { /* ignore */ }

        const legsOverlap = legUnknown || (holdFrom < reqTo && reqFrom < holdTo);
        if (!legsOverlap) continue;

        const directionsConflict = checkDirectionConflict(currentDirection, holdDirection);
        if (!directionsConflict) continue;

        for (const { passengerId, seatId } of dto.passengers) {
          if (rawSeatIds.includes(seatId)) {
            throw new ConflictException(`Seat ${seatLabelById[seatId]} is already held for this leg`);
          }
          if (!legUnknown && passengerIds.includes(passengerId)) {
            throw new ConflictException(`Passenger already holds a seat on this journey leg`);
          }
        }
      }

      const bookedSegments = await tx.journeySegment.findMany({
        where: {
          scheduleId: dto.scheduleId,
          seatId: { in: seatIds },
          journey: { status: { in: ['CONFIRMED', 'PENDING_PAYMENT'] } },
        },
        select: { seatId: true, departureStationId: true, arrivalStationId: true },
      });

      for (const seg of bookedSegments) {
        if (!seg.seatId) continue;
        const segFrom = seqOf(seg.departureStationId);
        const segTo   = seqOf(seg.arrivalStationId);
        const overlaps = (segFrom === undefined || segTo === undefined) ? true : segFrom < reqTo && reqFrom < segTo;
        if (overlaps) {
          throw new ConflictException(`Seat ${seatLabelById[seg.seatId]} is already booked for this leg`);
        }
      }

      const holdMeta = {
        originStationId: dto.originStationId,
        destinationStationId: dto.destinationStationId,
        journeyDirection: currentDirection,
        passengers: dto.passengers.map(p => ({ passengerId: p.passengerId, seatId: p.seatId })),
      };

      return tx.seatHold.create({
        data: {
          scheduleId: dto.scheduleId,
          passengerId: dto.passengers[0].passengerId,
          seatIds,
          createdBy: JSON.stringify(holdMeta),
          expiresAt,
        },
      });
    });

    return this.enrichHold(hold);
  }

  async getHolds(scheduleId?: string, passengerId?: string) {
    const holds = await this.prisma.seatHold.findMany({
      where: {
        expiresAt: { gt: new Date() },
        ...(scheduleId  ? { scheduleId }  : {}),
        ...(passengerId ? { passengerId } : {}),
      },
      orderBy: { createdAt: 'desc' },
    });
    return Promise.all(holds.map(h => this.enrichHold(h)));
  }

  async getHold(holdId: string) {
    const hold = await this.prisma.seatHold.findUnique({ where: { id: holdId } });
    if (!hold) throw new NotFoundException('Hold not found');
    return this.enrichHold(hold);
  }

  private async enrichHold(hold: any) {
    let originStationId: string | null = null;
    let destinationStationId: string | null = null;
    let passengerSeatMap: { passengerId: string; seatId: string }[] = [];

    try {
      if (hold.createdBy) {
        const raw = hold.createdBy;
        if (typeof raw === 'string' && raw.trimStart().startsWith('{')) {
          const meta = JSON.parse(raw);
          originStationId      = meta.originStationId      ?? null;
          destinationStationId = meta.destinationStationId ?? null;
          passengerSeatMap     = Array.isArray(meta.passengers) ? meta.passengers : [];
        }
      }
    } catch { /* ignore */ }

    const seatIds = hold.seatIds as string[];

    const [schedule, originStation, destinationStation, seats] = await Promise.all([
      this.prisma.trainSchedule.findUnique({
        where: { id: hold.scheduleId },
        include: { train: true, originStation: true, destinationStation: true },
      }),
      originStationId      ? this.prisma.station.findUnique({ where: { id: originStationId } })      : null,
      destinationStationId ? this.prisma.station.findUnique({ where: { id: destinationStationId } }) : null,
      this.prisma.seat.findMany({
        where: { id: { in: seatIds } },
        include: { coach: true },
      }),
    ]);

    let originSequence: number | null = null;
    let destinationSequence: number | null = null;
    if (originStationId && destinationStationId) {
      const stopTimes = await this.prisma.tripStopTime.findMany({
        where: { scheduleId: hold.scheduleId, stationId: { in: [originStationId, destinationStationId] } },
        select: { stationId: true, sequence: true },
      });
      originSequence      = stopTimes.find(s => s.stationId === originStationId)?.sequence      ?? null;
      destinationSequence = stopTimes.find(s => s.stationId === destinationStationId)?.sequence ?? null;
    }

    const seatById = Object.fromEntries(seats.map(s => [s.id, s]));

    const passengers = passengerSeatMap.length > 0
      ? passengerSeatMap.map(({ passengerId, seatId }) => {
          const s = seatById[seatId];
          return {
            passengerId,
            seat: s ? {
              id:         s.id,
              label:      s.seatNumber,
              seatNumber: s.seatNumber,
              coach:      s.coach.number,
              seatClass:  'Standard',
              row:        s.row,
              col:        s.col,
            } : { id: seatId },
          };
        })
      : seatIds.map(seatId => {
          const s = seatById[seatId];
          return {
            passengerId: hold.passengerId,
            seat: s ? {
              id:         s.id,
              label:      s.seatNumber,
              seatNumber: s.seatNumber,
              coach:      s.coach.number,
              seatClass:  'Standard',
              row:        s.row,
              col:        s.col,
            } : { id: seatId },
          };
        });

    return {
      holdId:     hold.id,
      expiresAt:  hold.expiresAt,
      createdAt:  hold.createdAt,
      ttlSeconds: Math.max(0, Math.floor((hold.expiresAt.getTime() - Date.now()) / 1000)),
      schedule: schedule ? {
        id:                   schedule.id,
        trainNumber:          schedule.train.number,
        trainName:            schedule.train.name,
        departureAt:          schedule.departureAt,
        arrivalAt:            schedule.arrivalAt,
        fullRouteOrigin:      schedule.originStation.name,
        fullRouteDestination: schedule.destinationStation.name,
      } : null,
      leg: {
        originStationId,
        originStationName:      originStation?.name ?? null,
        originStationCode:      originStation?.code ?? null,
        originSequence,
        destinationStationId,
        destinationStationName: destinationStation?.name ?? null,
        destinationStationCode: destinationStation?.code ?? null,
        destinationSequence,
      },
      passengers,
    };
  }

  async releaseHold(holdId: string) {
    const hold = await this.prisma.seatHold.findUnique({ where: { id: holdId } });
    if (!hold) throw new NotFoundException('Hold not found');
    await this.prisma.seatHold.delete({ where: { id: holdId } });
    return { released: true, holdId };
  }

  // Called right after a booking (PNR) is created, and again on successful payment.
  // Extends the SeatHold(s) covering these seats to the booking's actual payment
  // deadline — the same MIN(createdAt + 2h, departureAt - 30min) window TasksService
  // uses to auto-cancel unpaid bookings — instead of leaving them on the original
  // short seat-selection hold (5 min by default). Without this, the hold could expire
  // while the customer was still on the payment page, and a second customer could
  // hold/book the exact same seat out from under them.
  async confirmSeats(seatIds: string[], now: Date = new Date()): Promise<void> {
    if (seatIds.length === 0) return;

    const holds = await this.prisma.seatHold.findMany({
      where: { seatIds: { hasSome: seatIds } },
      select: { id: true, scheduleId: true, expiresAt: true },
    });
    if (holds.length === 0) return;

    const scheduleIds = Array.from(new Set(holds.map(h => h.scheduleId)));
    const schedules = await this.prisma.trainSchedule.findMany({
      where: { id: { in: scheduleIds } },
      select: { id: true, departureAt: true },
    });
    const departureById = new Map(schedules.map(s => [s.id, s.departureAt]));

    let extended = 0;
    await Promise.all(
      holds.map(async (hold) => {
        const departureAt = departureById.get(hold.scheduleId);
        if (!departureAt) return;
        const deadline = computePaymentDeadline(now, departureAt);
        // Only ever extend forward — never shorten a hold that's already valid longer
        // than the payment deadline would give it (e.g. a second confirmSeats call on
        // the same booking, or a hold that was already extended).
        if (deadline <= hold.expiresAt) return;
        await this.prisma.seatHold.update({ where: { id: hold.id }, data: { expiresAt: deadline } });
        extended++;
      }),
    );

    if (extended > 0) {
      this.logger.log(
        `Extended ${extended} seat hold(s) covering ${seatIds.length} seat(s) to their booking's payment deadline`,
      );
    }
  }

  // Delete the Journey (and its JourneySegments) scoped to this booking.
  async releaseSeats(bookingId: string) {
    await this.prisma.journey.deleteMany({ where: { bookingId } as any });
  }

  async getBlockedSeats() {
    const blocks = await this.prisma.seatBlock.findMany({
      include: {
        seat: { include: { coach: { select: { number: true } } } },
      },
      orderBy: { blockedAt: 'desc' },
    });
    return blocks.map(b => ({
      id: b.id,
      seatId: b.seatId,
      seatNumber: b.seat.seatNumber,
      coachNumber: b.seat.coach.number,
      scheduleId: b.scheduleId,
      reason: b.reason,
      blockedBy: b.blockedBy,
      blockedAt: b.blockedAt,
      unblockAt: b.unblockAt,
    }));
  }

  async getCoachesWithAvailability(scheduleId: string, originStationId?: string, destinationStationId?: string) {
    const schedule = await this.prisma.trainSchedule.findUnique({
      where: { id: scheduleId },
      select: { originStationId: true, destinationStationId: true },
    });
    if (!schedule) throw new NotFoundException('Schedule not found');

    const assignments = await this.prisma.coachAssignment.findMany({
      where: { scheduleId },
      include: {
        coach: {
          include: {
            seats: { select: { id: true, status: true, seatNumber: true } },
            coachType: { include: { seatClasses: { select: { name: true } } } },
          },
        },
      },
      orderBy: { positionNumber: 'asc' },
    });

    const allSeatIds = assignments.flatMap(a => a.coach.seats.map(s => s.id));
    const effectiveStatuses = await this.resolveEffectiveStatuses(
      scheduleId,
      allSeatIds,
      originStationId ?? schedule.originStationId,
      destinationStationId ?? schedule.destinationStationId,
    );

    return assignments.map(a => {
      const seats = a.coach.seats.filter(s => s.seatNumber && !s.seatNumber.startsWith('-'));
      const totalSeats = seats.length;
      const unavailable = seats.filter(s => {
        const status = effectiveStatuses.get(s.id) ?? (s.status === 'BLOCKED' || s.status === 'BOOKED' ? s.status : 'AVAILABLE');
        return status === 'HELD' || status === 'BOOKED' || status === 'BLOCKED';
      }).length;

      return {
        coachId: a.coach.id,
        coachTypeId: a.coach.coachType?.id ?? null,
        coachNumber: a.coach.number,
        positionNumber: a.positionNumber,
        coachTypeName: a.coach.coachType?.name ?? '',
        seatClasses: a.coach.coachType?.seatClasses.map(sc => sc.name) ?? [],
        totalSeats,
        availableSeats: totalSeats - unavailable,
        heldSeats: seats.filter(s => (effectiveStatuses.get(s.id) ?? (s.status === 'BLOCKED' || s.status === 'BOOKED' ? s.status : 'AVAILABLE')) === 'HELD').length,
        bookedSeats: seats.filter(s => (effectiveStatuses.get(s.id) ?? (s.status === 'BLOCKED' || s.status === 'BOOKED' ? s.status : 'AVAILABLE')) === 'BOOKED').length,
      };
    });
  }

  async autoAssignSeats(scheduleId: string, count: number, seatClassName: string): Promise<string[]> {
    const schedule = await this.prisma.trainSchedule.findUnique({
      where: { id: scheduleId },
      select: { originStationId: true, destinationStationId: true },
    });
    if (!schedule) throw new NotFoundException('Schedule not found');

    const seats = await this.prisma.seat.findMany({
      where: {
        coach: { assignments: { some: { scheduleId } } },
        seatNumber: { not: '' },
        NOT: [{ seatNumber: { startsWith: '-' } }, { status: 'BLOCKED' }, { status: 'UNDER_MAINTENANCE' as any }],
      },
      orderBy: [{ coach: { number: 'asc' } }, { row: 'asc' }, { col: 'asc' }],
    });

    const allSeatIds = seats.map(s => s.id);
    const stopTimes = await this.prisma.tripStopTime.findMany({
      where: { scheduleId },
      select: { stationId: true, sequence: true },
    });
    const seqOf = (id: string) => stopTimes.find(s => s.stationId === id)?.sequence;
    const reqFrom = seqOf(schedule.originStationId) ?? 0;
    const reqTo = seqOf(schedule.destinationStationId) ?? stopTimes.length;

    const [unavailable, scheduleBlocks] = await Promise.all([
      this.segmentsService.getSeatAvailabilityMap(
        scheduleId, allSeatIds, stopTimes, reqFrom, reqTo,
      ),
      this.prisma.seatBlock.findMany({
        where: { scheduleId, seatId: { in: allSeatIds } },
        select: { seatId: true },
      }),
    ]);
    const scheduleBlockedIds = new Set(scheduleBlocks.map(b => b.seatId));

    const availableSeats = seats.filter(s => !unavailable.has(s.id) && !scheduleBlockedIds.has(s.id));

    if (availableSeats.length < count) {
      throw new ConflictException(`Only ${availableSeats.length} seats available, requested ${count}`);
    }

    const assigned = this.findContiguousSeats(availableSeats, count);
    return assigned.map((s) => s.id);
  }

  private findContiguousSeats(seats: any[], count: number): any[] {
    if (count === 1) return [seats[0]];

    const grouped = new Map<string, any[]>();
    for (const seat of seats) {
      const key = `${seat.coachId}-${seat.row}`;
      if (!grouped.has(key)) grouped.set(key, []);
      grouped.get(key)!.push(seat);
    }

    for (const rowSeats of grouped.values()) {
      if (rowSeats.length >= count) {
        return rowSeats.slice(0, count);
      }
    }

    return seats.slice(0, count);
  }

  async exportSeatsCSV(scheduleId: string): Promise<string> {
    const assignments = await this.prisma.coachAssignment.findMany({
      where: { scheduleId },
      include: { coach: { include: { seats: { orderBy: [{ row: 'asc' }, { col: 'asc' }] } } } },
    });
    const rows = ['coachId,coachLabel,row,col,seatNumber,kind,status,premiumFeeMinor'];
    for (const a of assignments) {
      for (const seat of a.coach.seats) {
        rows.push(`${a.coach.id},${a.coach.number},${seat.row},${seat.col},${seat.seatNumber},${seat.kind},${seat.status},${seat.premiumFeeMinor}`);
      }
    }
    return rows.join('\n');
  }

  async previewSeatsCSV(csvContent: string): Promise<{ valid: number; invalid: number; errors: string[] }> {
    const lines = csvContent.trim().split('\n').slice(1);
    const errors: string[] = [];
    let valid = 0;
    let invalid = 0;

    for (let i = 0; i < lines.length; i++) {
      const parts = lines[i].split(',');
      if (parts.length < 8) {
        errors.push(`Line ${i + 2}: Invalid format`);
        invalid++;
        continue;
      }
      const [coachId, , row, col, seatNumber] = parts;
      if (!coachId || !row || !col || !seatNumber) {
        errors.push(`Line ${i + 2}: Missing required fields`);
        invalid++;
        continue;
      }
      valid++;
    }

    return { valid, invalid, errors: errors.slice(0, 10) };
  }

  async importSeatsCSV(scheduleId: string, csvContent: string, commit: boolean): Promise<{ imported: number; errors: string[] }> {
    const lines = csvContent.trim().split('\n').slice(1);
    const errors: string[] = [];
    let imported = 0;

    if (!commit) {
      return { imported: 0, errors: ['Preview mode'] };
    }

    for (let i = 0; i < lines.length; i++) {
      try {
        const parts = lines[i].split(',');
        const [coachId, , row, col, seatNumber, kind, status, premiumFeeMinor] = parts;

        await this.prisma.seat.upsert({
          where: { coachId_row_col: { coachId, row: parseInt(row), col } },
          update: {
            seatNumber,
            kind: kind as any,
            status: status as any,
            premiumFeeMinor: parseInt(premiumFeeMinor) || 0,
          },
          create: {
            coachId,
            row: parseInt(row),
            col,
            seatNumber,
            kind: kind as any,
            status: status as any,
            premiumFeeMinor: parseInt(premiumFeeMinor) || 0,
          },
        });
        imported++;
      } catch (err) {
        errors.push(`Line ${i + 2}: ${err instanceof Error ? err.message : String(err)}`);
      }
    }

    return { imported, errors: errors.slice(0, 10) };
  }

  async blockSeat(seatId: string, reason: string, scheduleId?: string) {
    const seat = await this.prisma.seat.findUnique({ where: { id: seatId } });
    if (!seat) throw new NotFoundException('Seat not found');

    // Schedule-scoped block: only affects this schedule, not all schedules
    // Global block (no scheduleId): sets Seat.status = BLOCKED for all schedules
    if (scheduleId) {
      await this.prisma.seatBlock.create({ data: { seatId, scheduleId, reason, blockedBy: 'system' } });
    } else {
      await this.prisma.seat.update({ where: { id: seatId }, data: { status: 'BLOCKED' } });
      await this.prisma.seatBlock.create({ data: { seatId, reason, blockedBy: 'system' } });
    }
    await this.auditService.log({ action: 'UPDATE', entityType: 'Seat', entityId: seatId, newData: { status: 'BLOCKED', reason, scheduleId } });
    return { blocked: true, seatId, reason, scheduleId };
  }

  async unblockSeat(seatId: string, scheduleId?: string) {
    const seat = await this.prisma.seat.findUnique({ where: { id: seatId } });
    if (!seat) throw new NotFoundException('Seat not found');

    if (scheduleId) {
      await this.prisma.seatBlock.deleteMany({ where: { seatId, scheduleId } });
    } else {
      await this.prisma.seat.update({ where: { id: seatId }, data: { status: 'AVAILABLE' } });
      await this.prisma.seatBlock.deleteMany({ where: { seatId, scheduleId: null } });
    }
    await this.auditService.log({ action: 'UPDATE', entityType: 'Seat', entityId: seatId, newData: { status: 'AVAILABLE', scheduleId } });
    return { unblocked: true, seatId, scheduleId };
  }

  async setMaintenance(seatId: string, reason: string) {
    const seat = await this.prisma.seat.findUnique({ where: { id: seatId } });
    if (!seat) throw new NotFoundException('Seat not found');
    if (seat.status === 'BOOKED') throw new BadRequestException('Cannot set a booked seat to maintenance');
    await this.prisma.seat.update({ where: { id: seatId }, data: { status: 'UNDER_MAINTENANCE' as any } });
    await this.prisma.seatBlock.create({ data: { seatId, reason: `MAINTENANCE: ${reason}`, blockedBy: 'system' } });
    return { maintenance: true, seatId, reason };
  }

  async clearMaintenance(seatId: string) {
    const seat = await this.prisma.seat.findUnique({ where: { id: seatId } });
    if (!seat) throw new NotFoundException('Seat not found');
    await this.prisma.seat.update({ where: { id: seatId }, data: { status: 'AVAILABLE' as any } });
    await this.prisma.seatBlock.deleteMany({ where: { seatId } });
    return { maintenance: false, seatId };
  }

  async removeSeat(seatId: string) {
    const seat = await this.prisma.seat.findUnique({ where: { id: seatId } });
    if (!seat) throw new NotFoundException('Seat not found');
    if (!seat.seatNumber || seat.seatNumber.startsWith('-')) throw new BadRequestException('Seat already removed');

    // Mark as removed, then renumber all active seats in the coach
    await this.prisma.seat.update({
      where: { id: seatId },
      data: { seatNumber: `-${seat.seatNumber}` },
    });

    await this.renumberCoachSeats(seat.coachId);
    await this.auditService.log({ action: 'DELETE', entityType: 'Seat', entityId: seatId, oldData: { seatNumber: seat.seatNumber, coachId: seat.coachId } });
    return { removed: true, seatId, originalSeatNumber: seat.seatNumber };
  }

  async undoRemoveSeat(seatId: string) {
    const seat = await this.prisma.seat.findUnique({ where: { id: seatId } });
    if (!seat) throw new NotFoundException('Seat not found');
    if (!seat.seatNumber || !seat.seatNumber.startsWith('-')) {
      throw new BadRequestException('Seat is not removed');
    }

    // Restore with a temporary placeholder number, then renumber
    await this.prisma.seat.update({ where: { id: seatId }, data: { seatNumber: `__restore__${seatId}` } });
    await this.renumberCoachSeats(seat.coachId);

    const restored = await this.prisma.seat.findUnique({ where: { id: seatId } });
    return { restored: true, seatId, seatNumber: restored?.seatNumber };
  }

  /**
   * Renumbers all active (non-removed) seats in a coach sequentially starting from 1,
   * ordered by row then col. Removed seats (prefixed with "-") keep their slot but
   * are excluded from the numbering sequence so numbers remain continuous.
   */
  private async renumberCoachSeats(coachId: string): Promise<void> {
    const allSeats = await this.prisma.seat.findMany({
      where: { coachId },
      orderBy: [{ row: 'asc' }, { col: 'asc' }],
      select: { id: true, seatNumber: true },
    });

    const activeSeats = allSeats.filter(
      (s) => s.seatNumber && !s.seatNumber.startsWith('-') && !s.seatNumber.startsWith('__restore__'),
    );

    await Promise.all(
      activeSeats.map((s, idx) =>
        this.prisma.seat.update({
          where: { id: s.id },
          data: { seatNumber: String(idx + 1) },
        }),
      ),
    );
  }

  // Runs every minute, but is also safe to call on-demand (e.g. right after a hold's
  // TTL is read back to the client) — expiresAt/now are both absolute UTC instants
  // (Date objects, not wall-clock strings), so this is correct regardless of the
  // server's or a client's local timezone; there's no wall-clock parsing involved.
  @Cron(CronExpression.EVERY_MINUTE)
  async expireHolds() {
    try {
      const result = await this.expireHoldsCore();
      if (result.expiredHolds > 0) {
        this.logger.log(
          `Expired ${result.expiredHolds} hold(s): released ${result.releasedSeatIds.length} seat(s), ` +
          `skipped ${result.skippedSeatIds.length} still held by another active hold on the same schedule`,
        );
      }
    } catch (error) {
      // A failed run must not crash the process or silently go unnoticed — the next
      // scheduled run one minute later will retry the same (still-expired) holds,
      // since nothing here is deleted/updated until the queries above succeed.
      this.logger.error('Failed to expire seat holds', error instanceof Error ? error.stack : error);
    }
  }

  async expireHoldsCore(now: Date = new Date()): Promise<{
    expiredHolds: number;
    releasedSeatIds: string[];
    skippedSeatIds: string[];
  }> {
    const expired = await this.prisma.seatHold.findMany({
      where: { expiresAt: { lt: now } },
      select: { id: true, scheduleId: true, seatIds: true },
    });
    if (expired.length === 0) {
      return { expiredHolds: 0, releasedSeatIds: [], skippedSeatIds: [] };
    }

    // Still-active holds — scoped per (scheduleId, seatId), not just seatId. The same
    // physical Seat row is reused across every recurring date a coach runs, so the
    // same seatId legitimately appears in unrelated holds for other schedules; without
    // this scoping, an unrelated active hold on a DIFFERENT schedule would wrongly
    // block release of a seat whose hold expired on THIS schedule, leaving it stuck at
    // status 'HELD' indefinitely.
    const activeHolds = await this.prisma.seatHold.findMany({
      where: { expiresAt: { gte: now } },
      select: { scheduleId: true, seatIds: true },
    });
    const stillHeldKeys = new Set(
      activeHolds.flatMap(h => (h.seatIds as string[]).map(seatId => `${h.scheduleId}:${seatId}`)),
    );

    const releasedSeatIds = new Set<string>();
    const skippedSeatIds = new Set<string>();
    for (const hold of expired) {
      for (const seatId of hold.seatIds as string[]) {
        if (stillHeldKeys.has(`${hold.scheduleId}:${seatId}`)) {
          skippedSeatIds.add(seatId);
        } else {
          releasedSeatIds.add(seatId);
        }
      }
    }

    await this.prisma.seatHold.deleteMany({ where: { expiresAt: { lt: now } } });

    return {
      expiredHolds: expired.length,
      releasedSeatIds: Array.from(releasedSeatIds),
      skippedSeatIds: Array.from(skippedSeatIds), // kept for logging/API compat; no DB writes needed
    };
  }

  // ─────────────────────────────────────────────────────────────────────────
  // Duplicate-seat management (backoffice)
  // ─────────────────────────────────────────────────────────────────────────

  async getDuplicateSeats(date: string, scheduleId?: string) {
    const dayStart = new Date(`${date}T00:00:00.000Z`);
    const dayEnd   = new Date(`${date}T23:59:59.999Z`);

    const schedules = await this.prisma.trainSchedule.findMany({
      where: {
        departureAt: { gte: dayStart, lte: dayEnd },
        ...(scheduleId ? { id: scheduleId } : {}),
      },
      orderBy: { departureAt: 'asc' },
      include: {
        originStation:      { select: { name: true } },
        destinationStation: { select: { name: true } },
        coachAssignments: {
          orderBy: { positionNumber: 'asc' },
          include: {
            coach: {
              include: {
                coachType: { select: { name: true } },
                seats: {
                  orderBy: [{ row: 'asc' }, { col: 'asc' }],
                  select: { id: true, seatNumber: true, status: true, coachId: true },
                },
              },
            },
          },
        },
      },
    });

    const result = [];

    for (const schedule of schedules) {
      // All confirmed BookingSeat rows for this schedule
      const bookingSeats = await this.prisma.bookingSeat.findMany({
        where: {
          OR: [
            { scheduleId: schedule.id },
            { booking: { scheduleId: schedule.id } },
          ],
          booking: { status: { in: ['CONFIRMED', 'BOARDED'] } },
        },
        select: {
          id: true, seatId: true, scheduleId: true, leg: true, passengerName: true,
          seat: { select: { coachId: true } },
          booking: {
            select: {
              id: true, bookingRef: true, scheduleId: true,
              originStationId: true, destinationStationId: true,
              createdAt: true, contactPhone: true,
            },
          },
        },
      });

      // Seats occupied by any confirmed journey on this schedule (source of truth)
      const journeySegments = await this.prisma.journeySegment.findMany({
        where: {
          scheduleId: schedule.id,
          seatId:     { not: null },
          journey:    { status: { in: ['CONFIRMED', 'PENDING_PAYMENT'] } },
        },
        select: { seatId: true },
      });
      const occupiedIds = new Set(journeySegments.map(js => js.seatId!));

      // Group BookingSeat rows by seatId::leg to find candidate duplicates,
      // then filter to only those whose booking segments actually overlap.
      type BS = (typeof bookingSeats)[number];
      const groups = new Map<string, BS[]>();
      for (const bs of bookingSeats) {
        const key = `${bs.seatId}::${bs.leg}`;
        if (!groups.has(key)) groups.set(key, []);
        groups.get(key)!.push(bs);
      }

      // Build stop-sequence map for this schedule once
      const stopTimes = await this.prisma.tripStopTime.findMany({
        where: { scheduleId: schedule.id },
        select: { stationId: true, sequence: true },
      });
      const seqOf = (stationId: string | null | undefined): number | undefined =>
        stationId ? stopTimes.find(s => s.stationId === stationId)?.sequence : undefined;

      // Fetch JourneySegment ranges for all booking IDs in candidate groups
      const candidateBookingIds = [...new Set(
        [...groups.values()].filter(g => g.length > 1).flatMap(g => g.map(bs => bs.booking.id)),
      )];
      const candidateSegments = candidateBookingIds.length > 0
        ? await this.prisma.journeySegment.findMany({
            where: {
              scheduleId: schedule.id,
              journey: { bookingId: { in: candidateBookingIds }, status: { in: ['CONFIRMED', 'PENDING_PAYMENT'] } },
            },
            select: { departureStationId: true, arrivalStationId: true, journey: { select: { bookingId: true } } },
          })
        : [];

      // Collapse per-booking segments into a single [from, to) range
      const rangeByBookingId = new Map<string, { from: number; to: number }>();
      for (const seg of candidateSegments) {
        const bookingId = seg.journey.bookingId;
        if (!bookingId) continue;
        const depSeq = seqOf(seg.departureStationId);
        const arrSeq = seqOf(seg.arrivalStationId);
        if (depSeq === undefined || arrSeq === undefined) continue;
        const existing = rangeByBookingId.get(bookingId);
        rangeByBookingId.set(bookingId, existing
          ? { from: Math.min(existing.from, depSeq), to: Math.max(existing.to, arrSeq) }
          : { from: depSeq, to: arrSeq });
      }

      // Fall back to booking-level origin/destination when JourneySegments are missing
      const rangeForBooking = (bs: BS): { from: number; to: number } | null => {
        const fromSegments = rangeByBookingId.get(bs.booking.id);
        if (fromSegments) return fromSegments;
        // BookingSeat.scheduleId tells us which leg this seat belongs to
        const bsScheduleId = bs.scheduleId ?? bs.booking.scheduleId;
        if (bsScheduleId !== schedule.id) return null;
        const from = seqOf(bs.booking.originStationId);
        const to = seqOf(bs.booking.destinationStationId);
        if (from === undefined || to === undefined) return null;
        return { from, to };
      };

      // Two bookings are true duplicates only if their segments overlap
      const segmentsOverlap = (a: { from: number; to: number }, b: { from: number; to: number }) =>
        a.from < b.to && b.from < a.to;

      // All seats held by any confirmed BookingSeat — union of JourneySegment-based
      // occupancy AND BookingSeat-based occupancy so that seats whose JourneySegments
      // are missing (e.g. created via enhanced-seats path without bookingId) are still
      // excluded from the available list.
      const bookedSeatIds = new Set<string>([
        ...occupiedIds,
        ...bookingSeats.map(bs => bs.seatId).filter((id): id is string => id !== null && id !== undefined),
      ]);

      const coachReports = [];

      for (const assignment of schedule.coachAssignments) {
        const coach = assignment.coach;

        // Duplicate groups whose seat belongs to this coach
        const duplicates = [];
        for (const [key, group] of groups) {
          if (group.length <= 1) continue;
          if (group[0].seat.coachId !== coach.id) continue;

          // Filter to bookings that actually have overlapping segments
          const overlapping: BS[] = [];
          for (let i = 0; i < group.length; i++) {
            const rangeA = rangeForBooking(group[i]);
            for (let j = i + 1; j < group.length; j++) {
              const rangeB = rangeForBooking(group[j]);
              // If either range is unknown, conservatively treat as overlap
              const isOverlap = !rangeA || !rangeB || segmentsOverlap(rangeA, rangeB);
              if (isOverlap) {
                if (!overlapping.includes(group[i])) overlapping.push(group[i]);
                if (!overlapping.includes(group[j])) overlapping.push(group[j]);
              }
            }
          }
          if (overlapping.length <= 1) continue;

          const [seatId] = key.split('::');
          const seat = coach.seats.find(s => s.id === seatId);
          duplicates.push({
            seatId,
            seatNumber: seat?.seatNumber ?? seatId,
            leg: overlapping[0].leg,
            bookings: overlapping.map(bs => ({
              bookingSeatId:  bs.id,
              bookingId:      bs.booking.id,
              bookingRef:     bs.booking.bookingRef,
              passengerName:  bs.passengerName,
              contactPhone:   bs.booking.contactPhone,
              createdAt:      bs.booking.createdAt,
            })),
          });
        }

        // Free seats in this coach — excludes BLOCKED, all confirmed BookingSeat
        // assignments, and all confirmed JourneySegment occupancies.
        const availableSeats = coach.seats
          .filter(s =>
            (s.status as string) !== 'BLOCKED' &&
            !s.seatNumber.startsWith('-') &&
            !bookedSeatIds.has(s.id),
          )
          .map(s => ({ seatId: s.id, seatNumber: s.seatNumber }));

        coachReports.push({
          coachId:       coach.id,
          coachNumber:   coach.number,
          coachTypeName: coach.coachType.name,
          duplicates,
          availableSeats,
        });
      }

      if (coachReports.some(c => c.duplicates.length > 0)) {
        result.push({
          scheduleId:  schedule.id,
          departureAt: schedule.departureAt,
          origin:      schedule.originStation.name,
          destination: schedule.destinationStation.name,
          coaches:     coachReports,
        });
      }
    }

    const totalDuplicates = result.reduce(
      (sum, s) => sum + s.coaches.reduce((cs, c) => cs + c.duplicates.length, 0),
      0,
    );

    return { date, schedules: result, totalDuplicates };
  }

  async resolveDuplicateSeats(bookingSeatIds: string[], coachIds: string[]) {
    if (bookingSeatIds.length === 0) return { resolved: 0, unresolved: 0, results: [] };

    // Load BookingSeat rows with full booking + schedule context
    const bookingSeats = await this.prisma.bookingSeat.findMany({
      where: { id: { in: bookingSeatIds } },
      select: {
        id: true, seatId: true, leg: true, scheduleId: true,
        seat: { select: { seatNumber: true } },
        booking: {
          select: {
            id: true, bookingRef: true, scheduleId: true,
            status: true, contactPhone: true, passengerId: true,
            totalMinor: true, currency: true,
            originStationId: true, destinationStationId: true,
            schedule: {
              select: {
                originStationId:      true,
                destinationStationId: true,
                originStation:        { select: { name: true } },
                destinationStation:   { select: { name: true } },
                departureAt:          true,
              },
            },
          },
        },
      },
    });

    if (bookingSeats.length !== bookingSeatIds.length) {
      const found   = new Set(bookingSeats.map(bs => bs.id));
      const missing = bookingSeatIds.filter(id => !found.has(id));
      throw new NotFoundException(`BookingSeat(s) not found: ${missing.join(', ')}`);
    }

    const invalid = bookingSeats.filter(bs => !['CONFIRMED', 'BOARDED'].includes(bs.booking.status));
    if (invalid.length > 0) {
      throw new BadRequestException(
        `Bookings must be CONFIRMED or BOARDED: ${invalid.map(bs => bs.booking.bookingRef).join(', ')}`,
      );
    }

    // Load all non-blocked, non-removed seats from the selected coaches (ordered for deterministic pick)
    const coachSeats = await this.prisma.seat.findMany({
      where: {
        coachId:    { in: coachIds },
        status:     { not: 'BLOCKED' },
        NOT: { seatNumber: { startsWith: '-' } },
      },
      select: { id: true, seatNumber: true, coachId: true, row: true, col: true },
      orderBy: [{ coachId: 'asc' }, { row: 'asc' }, { col: 'asc' }],
    });

    // Build occupied-seat sets per schedule from confirmed JourneySegments
    const scheduleIds = [
      ...new Set(
        bookingSeats
          .map(bs => bs.scheduleId ?? bs.booking.scheduleId)
          .filter((id): id is string => id !== null && id !== undefined),
      ),
    ];

    const occupiedBySchedule = new Map<string, Set<string>>();
    await Promise.all(
      scheduleIds.map(async scheduleId => {
        const segments = await this.prisma.journeySegment.findMany({
          where: {
            scheduleId,
            seatId:  { not: null },
            journey: { status: { in: ['CONFIRMED', 'PENDING_PAYMENT', 'BOARDED'] } },
          },
          select: { seatId: true },
        });
        occupiedBySchedule.set(scheduleId, new Set(segments.map(s => s.seatId!)));
      }),
    );

    // Track seats assigned within this batch to prevent double-assignment
    const assignedInBatch = new Set<string>();

    const results:    { bookingRef: string; oldSeatNumber: string; newSeatNumber: string; contactPhone: string | null }[] = [];
    const unresolved: { bookingRef: string; reason: string }[] = [];

    for (const bs of bookingSeats) {
      const scheduleId = (bs.scheduleId ?? bs.booking.scheduleId)!;
      const occupied   = occupiedBySchedule.get(scheduleId) ?? new Set<string>();

      // Pick the first available seat across the selected coaches
      const newSeat = coachSeats.find(
        seat =>
          !occupied.has(seat.id) &&
          !assignedInBatch.has(seat.id) &&
          seat.id !== bs.seatId,
      );

      if (!newSeat) {
        unresolved.push({
          bookingRef: bs.booking.bookingRef,
          reason:     'No available seat found in selected coaches',
        });
        this.logger.warn(
          `Duplicate resolve: no seat available for ${bs.booking.bookingRef} (schedule ${scheduleId})`,
        );
        continue;
      }

      await this.prisma.$transaction(async tx => {
        // 1. Change the seat on the booking and ticket.
        await tx.bookingSeat.update({
          where: { id: bs.id },
          data:  { seatId: newSeat.id, seatLabelSnapshot: newSeat.seatNumber },
        });
        await tx.ticket.updateMany({
          where: { bookingId: bs.booking.id, seatId: bs.seatId, leg: bs.leg },
          data:  { seatId: newSeat.id },
        });

        // 2. Point the existing JourneySegments to the new seat.
        //    The Journey is already linked to this booking via bookingId;
        //    just update the seatId in its hop rows for this schedule.
        const journey = await tx.journey.findFirst({
          where:  { bookingId: bs.booking.id },
          select: { id: true },
        });

        if (!journey) {
          // No Journey/JourneySegment for this booking (e.g. duplicate that was never
          // processed by finalizePaymentSuccess). Create them now using the same logic,
          // scoped to the booking's origin→destination leg so the seatmap shows BOOKED
          // only for the correct range of stops.
          const originId = bs.booking.originStationId ?? bs.booking.schedule?.originStationId;
          const destId   = bs.booking.destinationStationId ?? bs.booking.schedule?.destinationStationId;

          const stopTimes = await tx.tripStopTime.findMany({
            where:   { scheduleId },
            orderBy: { sequence: 'asc' },
            select:  { stationId: true },
          });

          const originIdx = originId ? stopTimes.findIndex(s => s.stationId === originId) : 0;
          const destIdx   = destId   ? stopTimes.findIndex(s => s.stationId === destId)   : stopTimes.length - 1;
          const fromIdx   = originIdx >= 0 ? originIdx : 0;
          const toIdx     = destIdx   >= 0 ? destIdx   : stopTimes.length - 1;

          const newJourney = await tx.journey.create({
            data: {
              passengerId: bs.booking.passengerId,
              bookingId:   bs.booking.id,
              status:      'CONFIRMED',
              totalMinor:  bs.booking.totalMinor,
              currency:    bs.booking.currency,
            } as any,
          });

          const segments = [];
          for (let i = fromIdx; i < toIdx; i++) {
            segments.push({
              journeyId:          newJourney.id,
              scheduleId,
              segmentOrder:       i - fromIdx,
              seatId:             newSeat.id,
              coachId:            newSeat.coachId,
              departureStationId: stopTimes[i].stationId,
              arrivalStationId:   stopTimes[i + 1].stationId,
            });
          }
          if (segments.length > 0) {
            await tx.journeySegment.createMany({ data: segments, skipDuplicates: true });
          }
          this.logger.log(
            `No Journey for ${bs.booking.bookingRef} — created Journey + ${segments.length} segment(s) for seat ${newSeat.seatNumber}`,
          );
          return;
        }

        const { count } = await tx.journeySegment.updateMany({
          where: { journeyId: journey.id, scheduleId, seatId: bs.seatId },
          data:  { seatId: newSeat.id },
        });

        // Journey exists but had no segments (e.g. booking confirmed via a path
        // that skipped JourneySegment creation). Create them now for the new seat
        // so the seatmap reflects BOOKED.
        if (count === 0) {
          const originId = bs.booking.originStationId ?? bs.booking.schedule?.originStationId;
          const destId   = bs.booking.destinationStationId ?? bs.booking.schedule?.destinationStationId;
          const stopTimes = await tx.tripStopTime.findMany({
            where:   { scheduleId },
            orderBy: { sequence: 'asc' },
            select:  { stationId: true },
          });
          const originIdx = originId ? stopTimes.findIndex(s => s.stationId === originId) : 0;
          const destIdx   = destId   ? stopTimes.findIndex(s => s.stationId === destId)   : stopTimes.length - 1;
          const fromIdx   = originIdx >= 0 ? originIdx : 0;
          const toIdx     = destIdx   >= 0 ? destIdx   : stopTimes.length - 1;
          const segments  = [];
          for (let i = fromIdx; i < toIdx; i++) {
            segments.push({
              journeyId:          journey.id,
              scheduleId,
              segmentOrder:       i - fromIdx,
              seatId:             newSeat.id,
              coachId:            newSeat.coachId,
              departureStationId: stopTimes[i].stationId,
              arrivalStationId:   stopTimes[i + 1].stationId,
            });
          }
          if (segments.length > 0) {
            await tx.journeySegment.createMany({ data: segments, skipDuplicates: true });
          }
          this.logger.log(
            `Seat reassigned: ${bs.booking.bookingRef} ` +
            `${bs.seat?.seatNumber ?? bs.seatId} → ${newSeat.seatNumber} ` +
            `(0 existing segments — created ${segments.length} new hop(s))`,
          );
        } else {
          this.logger.log(
            `Seat reassigned: ${bs.booking.bookingRef} ` +
            `${bs.seat?.seatNumber ?? bs.seatId} → ${newSeat.seatNumber} ` +
            `(${count} segment hop(s) updated)`,
          );
        }
      });

      // Mark as taken so the next booking in this batch doesn't get the same seat
      assignedInBatch.add(newSeat.id);
      occupied.add(newSeat.id);

      const oldSeatNumber = bs.seat?.seatNumber ?? '?';
      const origin        = bs.booking.schedule?.originStation?.name ?? '';
      const dest          = bs.booking.schedule?.destinationStation?.name ?? '';

      if (bs.booking.contactPhone) {
        const message =
          `EDR: Your booking ${bs.booking.bookingRef} (${origin} → ${dest}): ` +
          `your seat has been changed from seat ${oldSeatNumber} to seat ${newSeat.seatNumber}. ` +
          `We apologize for any inconvenience.`;
        await this.sms.sendSms({ to: bs.booking.contactPhone, message }).catch(() => null);
      }

      this.logger.log(
        `Duplicate resolved: ${bs.booking.bookingRef} seat ${oldSeatNumber} → ${newSeat.seatNumber}`,
      );

      results.push({
        bookingRef:    bs.booking.bookingRef,
        oldSeatNumber,
        newSeatNumber: newSeat.seatNumber,
        contactPhone:  bs.booking.contactPhone,
      });
    }

    return {
      resolved:          results.length,
      unresolved:        unresolved.length,
      results,
      ...(unresolved.length > 0 ? { unresolvedDetails: unresolved } : {}),
    };
  }
}
