import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, Brackets } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { TenantAwareRepository } from '../../../common/repositories/tenant-aware.repository';
import {
  BookingSettlementMasterEntity,
  CustomerSettlementStatus,
  SupplierSettlementStatus,
  OverallSettlementStatus,
} from '../../../entities/booking-settlement-master.entity';
import { MasterListQueryDto } from '../dto/master-list-query.dto';

/**
 * Repository for `booking_settlement_masters` — one row per booking.
 *
 * Inherits TenantAware find/save/update from the base; adds:
 *
 *   - `findActive(booking_id)` — find-or-create lookup (idempotent;
 *     guards the partial unique index)
 *   - `findDetailById(id)` — master + booking/query relations for the
 *     detail page
 *   - `findPaginatedList(query)` — list page with filter + search
 *   - `deriveRollup(master_id)` — aggregate over children that
 *     `recomputeMaster` writes back; SQL kept here so callers don't
 *     have to know the rollup formulae
 */
@Injectable()
export class BookingSettlementMasterRepository extends TenantAwareRepository<BookingSettlementMasterEntity> {
  constructor(
    @InjectRepository(BookingSettlementMasterEntity)
    repo: Repository<BookingSettlementMasterEntity>,
    cls: ClsService,
  ) {
    super(repo, cls);
  }

  /**
   * Find the active (non-soft-deleted) master for a booking. Returns
   * null when no master exists yet — `ensureMaster` is the caller
   * that handles the find-or-create flow on top of this.
   */
  async findActiveByBooking(booking_id: string): Promise<BookingSettlementMasterEntity | null> {
    return this.findOne({
      where: { booking_id },
      relations: { currency: true },
    });
  }

  /**
   * Detail bundle — master row + booking + guest info for the detail
   * page header. Children fetched separately (paginated).
   */
  async findDetailById(id: string): Promise<BookingSettlementMasterEntity | null> {
    return this.findOne({
      where: { id },
      relations: {
        booking: { query: true },
        currency: true,
      },
    });
  }

  /**
   * Paginated list for the Settlements page. Sorts by recency
   * (last_activity_at DESC) so masters with the most recent activity
   * bubble to the top of the dispatcher's view.
   *
   * Filters: overall_status (defaults to all), search (matches booking
   * number / guest name / mobile), destination, date window on the
   * booking travel start_date (q.start_date) — UAT 2026-06-30:
   * stakeholder direction is to anchor all settlement filters to the
   * trip date, matching the stats bar + analytical tabs. Earlier we
   * filtered on m.last_activity_at (TIMESTAMP) which broke same-day
   * From=To selections because <= '2026-06-30' compares against
   * '2026-06-30 00:00:00' and excludes mid-day activity.
   */
  async findPaginatedList(query: MasterListQueryDto) {
    const tenantId = this.currentTenantId();
    const page = Math.max(1, Number(query.page) || 1);
    const limit = Math.min(100, Math.max(1, Number(query.limit) || 10));
    const offset = (page - 1) * limit;

    const qb = this.repo
      .createQueryBuilder('m')
      .leftJoinAndSelect('m.booking', 'b')
      .leftJoinAndSelect('b.query', 'q')
      .leftJoinAndSelect('m.currency', 'c')
      .where('m.tenant_id = :tenantId', { tenantId })
      .andWhere('m.is_deleted = false')
      // Exclude settlements whose booking was soft-deleted — the master
      // row can outlive a deleted booking (delete preserves masters with
      // recorded payments), so filter on the parent, not just m.is_deleted.
      .andWhere('b.is_deleted = false');

    if (query.status) qb.andWhere('m.overall_status = :status', { status: query.status });
    if (query.destination_id) {
      qb.andWhere('q.destination_id = :destId', { destId: query.destination_id });
    }
    if (query.date_from) qb.andWhere('q.start_date >= :df', { df: query.date_from });
    if (query.date_to) qb.andWhere('q.start_date <= :dt', { dt: query.date_to });
    if (query.search) {
      qb.andWhere(
        new Brackets((b2) => {
          b2.where('b.booking_number ILIKE :s', { s: `%${query.search}%` })
            .orWhere('q.guest_name ILIKE :s', { s: `%${query.search}%` })
            .orWhere('q.guest_mobile ILIKE :s', { s: `%${query.search}%` });
        }),
      );
    }

    qb.orderBy('m.last_activity_at', 'DESC').skip(offset).take(limit);
    const [items, total] = await qb.getManyAndCount();
    return {
      items,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }

  /**
   * Aggregate child rows into the master's rollup shape. Single SQL
   * over `payment_settlements` so the rollup is O(1) writes per child
   * mutation.
   *
   * Status priority on overall_status:
   *   1. customer_refund_due > 0 → REFUND_DUE (wins over everything)
   *   2. customer + supplier both fully cleared → COMPLETED
   *   3. either side has movement → IN_PROGRESS
   *   4. else → PENDING
   *
   * HOLD is NEVER set by this method — admin action only. Callers
   * skip this when master is currently HOLD (handled at service).
   */
  async deriveRollup(masterId: string): Promise<{
    customer_expected: number;
    customer_received: number;
    customer_refund_due: number;
    customer_status: CustomerSettlementStatus;
    supplier_expected: number;
    supplier_paid: number;
    supplier_status: SupplierSettlementStatus;
    planned_margin: number;
    realised_margin: number;
    cost_expected: number;
    cost_margin: number;
    overall_status: OverallSettlementStatus;
  }> {
    const tenantId = this.currentTenantId();
    const rows = (await this.manager.query(
      `
      SELECT
        COALESCE(SUM(CASE WHEN party_type = 'CUSTOMER' AND type = 'COLLECTION'
                          THEN amount ELSE 0 END), 0)                        AS cust_exp,
        COALESCE(SUM(CASE WHEN party_type = 'CUSTOMER' AND type = 'COLLECTION'
                          THEN amount_settled ELSE 0 END), 0)                AS cust_rcv,
        COALESCE(SUM(CASE WHEN party_type = 'CUSTOMER' AND type = 'REFUND'
                          THEN amount - amount_settled ELSE 0 END), 0)       AS cust_refund,
        COALESCE(SUM(CASE WHEN party_type = 'SUPPLIER'
                          THEN amount ELSE 0 END), 0)                        AS sup_exp,
        COALESCE(SUM(CASE WHEN party_type = 'SUPPLIER'
                          THEN amount_settled ELSE 0 END), 0)                AS sup_paid,
        -- Line-level cost basis — Σ actual_supplier_cost across the
        -- booking's transport + activity + hotel + special line items.
        -- Transport/activity snapshot from Floor at booking-create;
        -- hotel/special are ops-entered post-assignment. Drives the
        -- HONEST margin column. Joined via the master's booking_id.
        COALESCE((
          SELECT SUM(c) FROM (
            SELECT COALESCE(actual_supplier_cost, 0) AS c
              FROM booking_transport_items
             WHERE tenant_id = $1 AND is_deleted = false
               AND booking_id = (
                 SELECT booking_id FROM booking_settlement_masters
                  WHERE id = $2
               )
            UNION ALL
            SELECT COALESCE(actual_supplier_cost, 0) AS c
              FROM booking_activity_items
             WHERE tenant_id = $1 AND is_deleted = false
               AND booking_id = (
                 SELECT booking_id FROM booking_settlement_masters
                  WHERE id = $2
               )
            UNION ALL
            SELECT COALESCE(actual_supplier_cost, 0) AS c
              FROM booking_hotel_items
             WHERE tenant_id = $1 AND is_deleted = false
               AND booking_id = (
                 SELECT booking_id FROM booking_settlement_masters
                  WHERE id = $2
               )
            UNION ALL
            SELECT COALESCE(actual_supplier_cost, 0) AS c
              FROM booking_special_items
             WHERE tenant_id = $1 AND is_deleted = false
               AND booking_id = (
                 SELECT booking_id FROM booking_settlement_masters
                  WHERE id = $2
               )
          ) all_lines
        ), 0) AS cost_exp
        FROM payment_settlements
       WHERE tenant_id = $1
         AND master_id = $2
         AND is_deleted = false
      `,
      [tenantId, masterId],
    )) as Array<{
      cust_exp: string;
      cust_rcv: string;
      cust_refund: string;
      sup_exp: string;
      sup_paid: string;
      cost_exp: string;
    }>;

    const r = rows[0] ?? {
      cust_exp: '0',
      cust_rcv: '0',
      cust_refund: '0',
      sup_exp: '0',
      sup_paid: '0',
      cost_exp: '0',
    };
    const customer_expected = Number(r.cust_exp);
    const customer_received = Number(r.cust_rcv);
    const customer_refund_due = Number(r.cust_refund);
    const supplier_expected = Number(r.sup_exp);
    const supplier_paid = Number(r.sup_paid);
    const cost_expected = Number(r.cost_exp);

    // Customer status priority — refund_due wins; then paid > partial > pending.
    let customer_status: CustomerSettlementStatus;
    if (customer_refund_due > 0.005) customer_status = CustomerSettlementStatus.REFUND_DUE;
    else if (customer_expected <= 0.005) customer_status = CustomerSettlementStatus.PENDING;
    else if (customer_received + 0.005 >= customer_expected)
      customer_status = CustomerSettlementStatus.PAID;
    else if (customer_received > 0.005) customer_status = CustomerSettlementStatus.PARTIAL;
    else customer_status = CustomerSettlementStatus.PENDING;

    // Supplier status — no refund concept; PAID/PARTIAL/PENDING.
    let supplier_status: SupplierSettlementStatus;
    if (supplier_expected <= 0.005) supplier_status = SupplierSettlementStatus.PENDING;
    else if (supplier_paid + 0.005 >= supplier_expected)
      supplier_status = SupplierSettlementStatus.PAID;
    else if (supplier_paid > 0.005) supplier_status = SupplierSettlementStatus.PARTIAL;
    else supplier_status = SupplierSettlementStatus.PENDING;

    const planned_margin = customer_expected - supplier_expected;
    const realised_margin = customer_received - supplier_paid;
    // Honest margin — selling minus the Floor-snapshot cost basis.
    // Doesn't lie when suppliers haven't been picked yet.
    const cost_margin = customer_expected - cost_expected;

    // Overall — REFUND_DUE > COMPLETED > IN_PROGRESS > PENDING.
    let overall_status: OverallSettlementStatus;
    if (customer_refund_due > 0.005) overall_status = OverallSettlementStatus.REFUND_DUE;
    else if (
      customer_expected > 0.005 &&
      customer_received + 0.005 >= customer_expected &&
      supplier_paid + 0.005 >= supplier_expected
    )
      overall_status = OverallSettlementStatus.COMPLETED;
    else if (customer_received > 0.005 || supplier_paid > 0.005)
      overall_status = OverallSettlementStatus.IN_PROGRESS;
    else overall_status = OverallSettlementStatus.PENDING;

    return {
      customer_expected: +customer_expected.toFixed(2),
      customer_received: +customer_received.toFixed(2),
      customer_refund_due: +customer_refund_due.toFixed(2),
      customer_status,
      supplier_expected: +supplier_expected.toFixed(2),
      supplier_paid: +supplier_paid.toFixed(2),
      supplier_status,
      planned_margin: +planned_margin.toFixed(2),
      realised_margin: +realised_margin.toFixed(2),
      cost_expected: +cost_expected.toFixed(2),
      cost_margin: +cost_margin.toFixed(2),
      overall_status,
    };
  }
}