import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { TenantAwareRepository } from '../../../common/repositories/tenant-aware.repository';
import { BookingEntity, BookingStatus } from '../../../entities/booking.entity';
import {
  allocateBookingNumberFromQuery,
  allocateUniqueReferenceNumber,
  DEFAULT_BOOKING_PREFIX,
} from '../../../common/utils/reference-number';
import type { BookingPaymentGateInput } from '../services/booking-payment-gate';

@Injectable()
export class BookingRepository extends TenantAwareRepository<BookingEntity> {
  /** Public TypeORM repo handle for advanced querybuilder use inside the service. */
  public readonly ormRepo: Repository<BookingEntity>;

  constructor(@InjectRepository(BookingEntity) repo: Repository<BookingEntity>, cls: ClsService) {
    super(repo, cls);
    this.ormRepo = repo;
  }

  async findByIdWithRelations(id: string): Promise<BookingEntity | null> {
    const tenantId = this.getTenantId();
    const booking = await this.repo.findOne({
      where: { id, tenant_id: tenantId, is_deleted: false },
      relations: {
        query: {
          destination: {
            currency: true,
          },

          source: true,
          currency: true,
        },

        quote: {
          selling_currency: true,
        },

        selling_currency: true,

        hotel_items: {
          hotel: true,
          periods: true,
          extras: true,
        },

        transport_items: {
          transport_service: true,
          vehicle_type: true,
          driver: true,
          vehicle: true,
          supplier: true,
        },

        activity_items: {
          activity: true,
          ticket: true,
        },

        special_items: true,
        flight_details: true,
      },
    });

    if (booking) {
      if (booking.hotel_items) {
        booking.hotel_items = booking.hotel_items
          .filter((i) => !i.is_deleted)
          .sort((a, b) => (a.check_in_date || '').localeCompare(b.check_in_date || ''));
        // Sort children on each stay by sort_order for stable render.
        // Also strip soft-deleted rows — CASCADE hard-delete keeps the
        // table tidy at the FK boundary, but a manual soft-delete
        // (e.g. hardDeleteChildItems on quote side) may leave rows.
        for (const hi of booking.hotel_items) {
          if (hi.periods)
            hi.periods = hi.periods
              .filter((p) => !p.is_deleted)
              .sort((a, b) => (a.sort_order ?? 0) - (b.sort_order ?? 0));
          if (hi.extras)
            hi.extras = hi.extras
              .filter((e) => !e.is_deleted)
              .sort((a, b) => (a.sort_order ?? 0) - (b.sort_order ?? 0));
        }
      }
      if (booking.transport_items)
        booking.transport_items = booking.transport_items
          .filter((i) => !i.is_deleted)
          .sort((a, b) => (a.sort_order ?? 0) - (b.sort_order ?? 0));
      if (booking.activity_items)
        booking.activity_items = booking.activity_items
          .filter((i) => !i.is_deleted)
          .sort((a, b) => (a.sort_order ?? 0) - (b.sort_order ?? 0));
      if (booking.special_items)
        booking.special_items = booking.special_items
          .filter((i) => !i.is_deleted)
          .sort((a, b) => (a.sort_order ?? 0) - (b.sort_order ?? 0));
      if (booking.flight_details)
        booking.flight_details = booking.flight_details
          .filter((f) => !f.is_deleted)
          .sort((a, b) => (a.departure_time || '').localeCompare(b.departure_time || ''));
    }

    return booking;
  }

  /** Tenant's booking prefix from settings (default `BK`). */
  async getBookingPrefix(): Promise<string> {
    const rows: Array<{ booking_prefix: string | null }> = await this.repo.manager.query(
      `SELECT booking_prefix FROM tenant_settings
       WHERE tenant_id = $1 AND is_deleted = false LIMIT 1`,
      [this.getTenantId()],
    );
    return rows?.[0]?.booking_prefix || DEFAULT_BOOKING_PREFIX;
  }

  async getNextBookingNumber(): Promise<string> {
    // Walk-in / ad-hoc booking with no query seed — fresh per-tenant
    // 6-digit random `{prefix}-NNNNNN`. See reference-number.ts.
    const prefix = await this.getBookingPrefix();
    return allocateUniqueReferenceNumber(
      'BK',
      this.getTenantId(),
      (sql, params) => this.repo.manager.query(sql, params),
      prefix,
    );
  }

  /**
   * Booking number for a query→booking conversion (1:1) — reuses the
   * query's 6 digits with the tenant prefix so the pair reads as one
   * case: `QRY-093254` → `{prefix}-093254`.
   */
  async getBookingNumberForQuery(queryNumber: string): Promise<string> {
    const prefix = await this.getBookingPrefix();
    return allocateBookingNumberFromQuery(
      queryNumber,
      this.getTenantId(),
      (sql, params) => this.repo.manager.query(sql, params),
      prefix,
    );
  }

  /**
   * System job (cron / CLI) — no HTTP CLS. Lists active tenants so the
   * caller can pass `tenant_id` per subsequent query.
   */
  async listActiveTenantIdsForStatusSync(): Promise<string[]> {
    const rows: Array<{ id: string }> = await this.manager.query(
      `SELECT t.id FROM tenants t WHERE t.is_active = true ORDER BY t.id`,
    );
    return rows.map((r) => r.id);
  }

  /**
   * Bookings eligible for date-driven status (excludes cancelled,
   * completed, and on_hold — those rows are never rewritten).
   * Raw SQL: CLS is absent in cron — `tenant_id` is required in WHERE.
   */
  async findEligibleForDateSync(tenantId: string): Promise<
    Array<{
      id: string;
      status: string;
      start_date: string;
      end_date: string;
    }>
  > {
    return this.manager.query(
      `SELECT b.id,
              b.status,
              to_char(q.start_date, 'YYYY-MM-DD') AS start_date,
              to_char(q.end_date, 'YYYY-MM-DD') AS end_date
         FROM bookings b
         INNER JOIN queries q
           ON q.id = b.query_id
          AND q.tenant_id = b.tenant_id
        WHERE b.tenant_id = $1
          AND q.tenant_id = $1
          AND b.is_deleted = false
          AND q.is_deleted = false
          AND b.status NOT IN ('cancelled', 'completed', 'on_hold')`,
      [tenantId],
    );
  }

  /**
   * Inputs for the Movement Dashboard payment gate, keyed by booking id.
   * Collected = Σ amount_settled on live CUSTOMER COLLECTION settlements,
   * converted from the selling currency back to the itinerary currency
   * (destination currency, same as the Day-wise Itinerary card) via
   * `exchange_rate` when they differ. Raw SQL — tenant_id is applied
   * manually on every table.
   */
  async findPaymentGateInputs(bookingIds: string[]): Promise<Map<string, BookingPaymentGateInput>> {
    const out = new Map<string, BookingPaymentGateInput>();
    if (bookingIds.length === 0) return out;
    const tenantId = this.getTenantId();

    const [bookingRows, dayRows] = await Promise.all([
      this.manager.query(
        `SELECT b.id,
                b.is_guaranteed,
                b.guarantee_description,
                c.code AS currency_code,
                COALESCE((
                  SELECT SUM(
                           CASE WHEN ps.currency_id = COALESCE(d.currency_id, q.currency_id)
                                THEN ps.amount_settled
                                ELSE ps.amount_settled / NULLIF(b.exchange_rate, 0)
                           END)
                    FROM payment_settlements ps
                   WHERE ps.tenant_id = $1
                     AND ps.booking_id = b.id
                     AND ps.is_deleted = false
                     AND ps.party_type = 'CUSTOMER'
                     AND ps.type = 'COLLECTION'
                ), 0) AS paid
           FROM bookings b
           JOIN queries q ON q.id = b.query_id AND q.tenant_id = $1
           LEFT JOIN destinations d ON d.id = q.destination_id AND d.tenant_id = $1
           LEFT JOIN currencies c ON c.id = COALESCE(d.currency_id, q.currency_id)
          WHERE b.tenant_id = $1
            AND b.is_deleted = false
            AND b.id = ANY($2::uuid[])`,
        [tenantId, bookingIds],
      ) as Promise<
        Array<{
          id: string;
          is_guaranteed: boolean;
          guarantee_description: string | null;
          currency_code: string | null;
          paid: string;
        }>
      >,
      this.manager.query(
        `SELECT booking_id, to_char(day, 'YYYY-MM-DD') AS day, SUM(amount) AS amount
           FROM (
             SELECT booking_id, date AS day, subtotal AS amount
               FROM booking_transport_items
              WHERE tenant_id = $1 AND is_deleted = false AND date IS NOT NULL
                AND booking_id = ANY($2::uuid[])
             UNION ALL
             SELECT booking_id, date, subtotal
               FROM booking_activity_items
              WHERE tenant_id = $1 AND is_deleted = false AND date IS NOT NULL
                AND booking_id = ANY($2::uuid[])
             UNION ALL
             SELECT booking_id, date, total_price
               FROM booking_special_items
              WHERE tenant_id = $1 AND is_deleted = false AND date IS NOT NULL
                AND booking_id = ANY($2::uuid[])
             UNION ALL
             SELECT h.booking_id, gs::date, h.subtotal / h.nights
               FROM booking_hotel_items h
              CROSS JOIN LATERAL generate_series(
                       h.check_in_date::timestamp,
                       h.check_out_date::timestamp - interval '1 day',
                       interval '1 day') gs
              WHERE h.tenant_id = $1 AND h.is_deleted = false AND h.nights > 0
                AND h.booking_id = ANY($2::uuid[])
           ) items
          GROUP BY booking_id, day`,
        [tenantId, bookingIds],
      ) as Promise<Array<{ booking_id: string; day: string; amount: string }>>,
    ]);

    for (const b of bookingRows) {
      out.set(b.id, {
        is_guaranteed: !!b.is_guaranteed,
        guarantee_description: b.guarantee_description ?? null,
        paid: Number(b.paid) || 0,
        day_amounts: new Map(),
        currency_code: b.currency_code,
      });
    }
    for (const r of dayRows) {
      out.get(r.booking_id)?.day_amounts.set(r.day, Number(r.amount) || 0);
    }
    return out;
  }

  async bulkUpdateStatus(tenantId: string, ids: string[], status: BookingStatus): Promise<void> {
    if (ids.length === 0) return;
    await this.manager.query(
      `UPDATE bookings
          SET status = $2,
              updated_at = NOW()
        WHERE tenant_id = $1
          AND is_deleted = false
          AND id = ANY($3::uuid[])
          AND status NOT IN ('cancelled', 'completed', 'on_hold')
          AND status <> $2`,
      [tenantId, status, ids],
    );
  }
}