import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { QueryEntity, QueryStage } from '../../entities/query.entity';
import { BookingEntity } from '../../entities/booking.entity';
import { QueryQuoteEntity } from '../../entities/query-quote.entity';
import { DriverEntity, DriverStatus } from '../../entities/driver.entity';
import { AuditLogEntity } from '../../entities/audit-log.entity';
import { UserEntity } from '../../entities/user.entity';
import { CLS_TENANT_ID } from '../../common/cls/cls.constants';
import { UserReportingService } from '../user/user-reporting.service';
import {
  applyQueryRowVisibility,
  applyBookingRowVisibility,
  type VisibleUserIds,
} from '../../common/auth/row-visibility';

@Injectable()
export class DashboardService {
  constructor(
    @InjectRepository(QueryEntity) private readonly queryRepo: Repository<QueryEntity>,
    @InjectRepository(BookingEntity) private readonly bookingRepo: Repository<BookingEntity>,
    @InjectRepository(QueryQuoteEntity) private readonly quoteRepo: Repository<QueryQuoteEntity>,
    @InjectRepository(DriverEntity) private readonly driverRepo: Repository<DriverEntity>,
    @InjectRepository(AuditLogEntity) private readonly auditRepo: Repository<AuditLogEntity>,
    @InjectRepository(UserEntity) private readonly userRepo: Repository<UserEntity>,
    private readonly cls: ClsService,
    private readonly userReporting: UserReportingService,
  ) {}

  private getTenantId(): string {
    return this.cls.get<string>(CLS_TENANT_ID);
  }

  /** Append `AND alias.destination_id = $n` for raw SQL. Caller must
   *  pass the same params array the query will be executed with. */
  private destSql(
    params: (string | number | string[])[],
    destinationId: string | undefined,
    alias = 'q',
  ): string {
    if (!destinationId) return '';
    params.push(destinationId);
    return ` AND ${alias}.destination_id = $${params.length}`;
  }

  /**
   * Append `AND (q.created_by ∈ ids OR q.sales_team overlaps ids)` to a
   * raw-SQL query on the `queries` alias `q`, pushing the id array as the
   * next positional param. Returns '' when `visibleUserIds` is null (full
   * visibility) so the SQL is byte-identical for Admin / view-all roles,
   * and `AND 1 = 0` when the set is empty. Raw-SQL mirror of
   * applyQueryRowVisibility (the query-builder equivalent).
   */
  private queryVisibilityRawSql(
    params: (string | number | string[])[],
    visibleUserIds: VisibleUserIds,
  ): string {
    if (visibleUserIds === null) return '';
    if (visibleUserIds.length === 0) return ' AND 1 = 0';
    params.push(visibleUserIds);
    const p = params.length;
    return (
      ` AND (q.created_by = ANY($${p}::uuid[])` +
      ` OR EXISTS (SELECT 1 FROM jsonb_array_elements_text(q.sales_team) elem WHERE elem = ANY($${p}::text[])))`
    );
  }

  async getStats(startDate?: string, endDate?: string, destinationId?: string) {
    const tenantId = this.getTenantId();

    const [
      totalQueries,
      active,
      revenue,
      upcomingData,
      availableDrivers,
      stageCounts,
      recentActivity,
      conversion,
      margin,
      specialActivities,
    ] = await Promise.all([
      this.countQueries(tenantId, startDate, endDate, destinationId),
      this.countActiveBookings(tenantId, destinationId),
      this.sumRevenue(tenantId, startDate, endDate, destinationId),
      this.getUpcomingTrips(tenantId, destinationId),
      this.countAvailableDrivers(tenantId, destinationId),
      this.getQueryStageCounts(tenantId, startDate, endDate, destinationId),
      this.getRecentActivity(tenantId),
      this.getOverallConversion(tenantId, startDate, endDate, destinationId),
      this.getOverallMargin(tenantId, startDate, endDate, destinationId),
      this.getSpecialActivityBookings(tenantId, destinationId),
    ]);

    return {
      total_queries: totalQueries,
      active_bookings: active.total,
      active_bookings_upcoming: active.upcoming,
      active_bookings_ongoing: active.ongoing,
      revenue,
      upcoming_trips: upcomingData.count,
      available_drivers: availableDrivers,
      query_stage_counts: stageCounts,
      upcoming_bookings: upcomingData.bookings,
      recent_activity: recentActivity,
      special_activity_bookings: specialActivities,
      // Tenant-wide analytical KPIs added 2026-05-08 for the Admin /
      // CEO dashboard (Session 5 of Role-Based Dashboards). Both can
      // be null when the period has no qualifying data — the FE
      // renders "—" rather than misleading 0%.
      conversion_pct: conversion,
      margin_pct: margin,
    };
  }

  /**
   * Overall Quote → Booking conversion in the period. Computed as
   *   bookings.count / quotes.count
   * filtered by created_at, both bookings and quotes via their parent
   * query's created_at so the math is internally consistent (a Jan
   * query whose quote was sent in Mar but booked in May can still
   * land in a Jan view if it was a Jan-period query).
   *
   * Returns null when no quotes exist (avoid divide-by-zero / fake 0%).
   *
   * Walk-in (instant-booking) queries are excluded from BOTH sides —
   * they always carry a quote + a booking, so counting them would pin
   * the ratio toward 100% and mask real pipeline conversion. Excluding
   * both numerator and denominator keeps the math consistent. See
   * migration 1787600000000.
   */
  private async getOverallConversion(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
  ): Promise<number | null> {
    const params: (string | number)[] = [tenantId];
    let filterSql = '';
    if (startDate) {
      params.push(startDate);
      filterSql += ` AND q.created_at >= $${params.length}`;
    }
    if (endDate) {
      params.push(endDate + 'T23:59:59');
      filterSql += ` AND q.created_at <= $${params.length}`;
    }
    filterSql += this.destSql(params, destinationId);

    const rows = await this.queryRepo.manager.query(
      `
      SELECT
        COALESCE((SELECT COUNT(*) FROM query_quotes qt
                  JOIN queries q ON q.id = qt.query_id
                  WHERE q.tenant_id = $1 AND q.is_deleted = false AND q.is_walk_in = false AND qt.is_deleted = false${filterSql}), 0) AS quotes,
        COALESCE((SELECT COUNT(*) FROM bookings b
                  JOIN queries q ON q.id = b.query_id
                  WHERE q.tenant_id = $1 AND q.is_deleted = false AND q.is_walk_in = false AND b.is_deleted = false${filterSql}), 0) AS bookings
      `,
      params,
    );
    const quotes = Number(rows[0]?.quotes) || 0;
    const bookings = Number(rows[0]?.bookings) || 0;
    return quotes > 0 ? Math.round((bookings / quotes) * 100) : null;
  }

  /**
   * Overall margin % across bookings born in the period. Computed in
   * the booking's selling currency:
   *   (selling_total - grand_total*exchange_rate) / selling_total
   * `grand_total` is the operational/cost number; converting to
   * selling-currency via exchange_rate gives an apples-to-apples spread.
   *
   * Returns null when selling_total sums to 0 in the period.
   */
  private async getOverallMargin(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
  ): Promise<number | null> {
    // Cost is `grand_total` in the query/destination currency.
    // Convert to selling currency only when the booking actually
    // uses a different selling currency — applying exchange_rate
    // on same-currency rows (leftover FX ≠ 1) produced bogus
    // negative margins like -11%.
    const qb = this.bookingRepo
      .createQueryBuilder('b')
      .innerJoin('b.query', 'q', 'q.is_deleted = false')
      .select('COALESCE(SUM(b.selling_total), 0)', 'sell')
      .addSelect(
        `COALESCE(SUM(
          CASE
            WHEN b.selling_currency_id IS NOT NULL
             AND q.currency_id IS NOT NULL
             AND b.selling_currency_id <> q.currency_id
            THEN b.grand_total * COALESCE(NULLIF(b.exchange_rate, 0), 1)
            ELSE b.grand_total
          END
        ), 0)`,
        'cost_in_sell_ccy',
      )
      .where('b.tenant_id = :tenantId', { tenantId })
      .andWhere('b.is_deleted = false')
      .andWhere('b.status <> :cancelled', { cancelled: 'cancelled' });
    if (startDate) qb.andWhere('b.created_at >= :startDate', { startDate });
    if (endDate) qb.andWhere('b.created_at <= :endDate', { endDate: endDate + 'T23:59:59' });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    const row = await qb.getRawOne();
    const sell = Number(row?.sell) || 0;
    const cost = Number(row?.cost_in_sell_ccy) || 0;
    if (sell <= 0) return null;
    return Math.round(((sell - cost) / sell) * 100);
  }

  private async countQueries(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
  ): Promise<number> {
    const qb = this.queryRepo
      .createQueryBuilder('q')
      .where('q.tenant_id = :tenantId', { tenantId })
      .andWhere('q.is_deleted = false')
      // Walk-in queries back a booking, not a pipeline lead — exclude
      // them from the headline query count. See migration 1787600000000.
      .andWhere('q.is_walk_in = false');
    if (startDate) qb.andWhere('q.created_at >= :startDate', { startDate });
    if (endDate) qb.andWhere('q.created_at <= :endDate', { endDate: endDate + 'T23:59:59' });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    return qb.getCount();
  }

  private async countActiveBookings(
    tenantId: string,
    destinationId?: string,
  ): Promise<{ total: number; upcoming: number; ongoing: number }> {
    const base = (status: 'upcoming' | 'ongoing') => {
      const qb = this.bookingRepo
        .createQueryBuilder('b')
        .innerJoin('b.query', 'q', 'q.is_deleted = false')
        .where('b.tenant_id = :tenantId', { tenantId })
        .andWhere('b.is_deleted = false')
        .andWhere('b.status = :status', { status });
      if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
      return qb.getCount();
    };
    const [upcoming, ongoing] = await Promise.all([base('upcoming'), base('ongoing')]);
    return { total: upcoming + ongoing, upcoming, ongoing };
  }

  private async sumRevenue(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
  ): Promise<number> {
    const qb = this.bookingRepo
      .createQueryBuilder('b')
      .innerJoin('b.query', 'q', 'q.is_deleted = false')
      .select('COALESCE(SUM(b.grand_total), 0)', 'total')
      .where('b.tenant_id = :tenantId', { tenantId })
      .andWhere('b.is_deleted = false')
      .andWhere('b.status <> :cancelled', { cancelled: 'cancelled' });
    if (startDate) qb.andWhere('b.created_at >= :startDate', { startDate });
    if (endDate) qb.andWhere('b.created_at <= :endDate', { endDate: endDate + 'T23:59:59' });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    const result = await qb.getRawOne();
    return Number(result?.total) || 0;
  }

  private async getUpcomingTrips(tenantId: string, destinationId?: string) {
    const qb = this.bookingRepo
      .createQueryBuilder('b')
      .innerJoinAndSelect('b.query', 'q', 'q.is_deleted = false')
      .leftJoinAndSelect('q.destination', 'd')
      .where('b.tenant_id = :tenantId', { tenantId })
      .andWhere('b.is_deleted = false')
      .andWhere('b.status = :status', { status: 'upcoming' })
      .orderBy('q.start_date', 'ASC')
      .take(10);
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });

    const [bookings, count] = await qb.getManyAndCount();

    return {
      count,
      bookings: bookings.map((b) => ({
        id: b.id,
        booking_number: b.booking_number,
        guest_name: b.query?.guest_name || '',
        destination_name: b.query?.destination?.name || '—',
        start_date: b.query?.start_date,
        end_date: b.query?.end_date,
        status: b.status,
      })),
    };
  }

  private async countAvailableDrivers(tenantId: string, destinationId?: string): Promise<number> {
    const qb = this.driverRepo
      .createQueryBuilder('d')
      .where('d.tenant_id = :tenantId', { tenantId })
      .andWhere('d.is_deleted = false')
      .andWhere('d.is_active = true')
      .andWhere('d.status = :status', { status: DriverStatus.AVAILABLE });
    if (destinationId) qb.andWhere('d.destination_id = :destinationId', { destinationId });
    return qb.getCount();
  }

  private async getQueryStageCounts(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
  ): Promise<Record<string, number>> {
    const qb = this.queryRepo
      .createQueryBuilder('q')
      .select('q.stage', 'stage')
      .addSelect('COUNT(*)', 'count')
      .where('q.tenant_id = :tenantId', { tenantId })
      .andWhere('q.is_deleted = false')
      // Exclude walk-in queries — they'd otherwise inflate the
      // "converted" bucket. See migration 1787600000000.
      .andWhere('q.is_walk_in = false')
      .groupBy('q.stage');
    if (startDate) qb.andWhere('q.created_at >= :startDate', { startDate });
    if (endDate) qb.andWhere('q.created_at <= :endDate', { endDate: endDate + 'T23:59:59' });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    const rows = await qb.getRawMany();
    const counts: Record<string, number> = {};
    for (const row of rows) counts[row.stage] = Number(row.count);
    return counts;
  }

  private async getRecentActivity(tenantId: string) {
    // Get latest audit entry per entity (grouped by entity_type + entity_id)
    // Using a subquery to get the max created_at per entity, then join back
    const rawEntries = await this.auditRepo
      .createQueryBuilder('a')
      .leftJoinAndSelect('a.performer', 'u')
      .where('a.tenant_id = :tenantId', { tenantId })
      .orderBy('a.created_at', 'DESC')
      .take(50)
      .getMany();

    // Group by entity_type + entity_id, keep only the latest per entity
    const seen = new Map<string, (typeof rawEntries)[0]>();
    for (const entry of rawEntries) {
      const key = `${entry.entity_type}:${entry.entity_id}`;
      if (!seen.has(key)) seen.set(key, entry);
    }
    const grouped = Array.from(seen.values()).slice(0, 10);

    // Enrich with entity-specific data (guest name, number)
    const entityIds = { query: new Set<string>(), booking: new Set<string>() };
    for (const e of grouped) {
      if (e.entity_type === 'query') entityIds.query.add(e.entity_id);
      if (e.entity_type === 'booking') entityIds.booking.add(e.entity_id);
    }

    const queryMap = new Map<string, { query_number: string; guest_name: string }>();
    const bookingMap = new Map<string, { booking_number: string; guest_name: string }>();

    if (entityIds.query.size > 0) {
      const queries = await this.queryRepo
        .createQueryBuilder('q')
        .select(['q.id', 'q.query_number', 'q.guest_name'])
        .where('q.id IN (:...ids)', { ids: [...entityIds.query] })
        .getMany();
      for (const q of queries)
        queryMap.set(q.id, { query_number: q.query_number, guest_name: q.guest_name || '' });
    }

    if (entityIds.booking.size > 0) {
      const bookings = await this.bookingRepo
        .createQueryBuilder('b')
        .leftJoinAndSelect('b.query', 'q')
        .select(['b.id', 'b.booking_number', 'q.guest_name'])
        .where('b.id IN (:...ids)', { ids: [...entityIds.booking] })
        .getMany();
      for (const b of bookings)
        bookingMap.set(b.id, {
          booking_number: b.booking_number,
          guest_name: b.query?.guest_name || '',
        });
    }

    return grouped.map((e) => {
      const qData = queryMap.get(e.entity_id);
      const bData = bookingMap.get(e.entity_id);
      return {
        id: e.id,
        entity_type: e.entity_type,
        entity_id: e.entity_id,
        action: e.action,
        performer_name: (e as { performer?: { name?: string } }).performer?.name || null,
        created_at: e.created_at,
        entity_number: qData?.query_number || bData?.booking_number || null,
        guest_name: qData?.guest_name || bData?.guest_name || null,
      };
    });
  }

  // ───────────────────────────────────────────────────────────────────
  // Sales Head dashboard — team-wide aggregates.
  //
  // Gated at the controller layer by Permissions(QUERY:edit) which is
  // satisfied by Admin (DELETE) and Sales Head (EDIT) only — Sales
  // Team users (QUERY:view) cannot reach this endpoint.
  //
  // Date filter is applied to query.created_at uniformly. Booking and
  // quote counts are filtered by their parent query's created_at so
  // the funnel is internally consistent (a booking born from a Jan
  // query never shows up in a "this-month" view in May).
  // ───────────────────────────────────────────────────────────────────

  async getSalesHeadStats(startDate?: string, endDate?: string, destinationId?: string) {
    const tenantId = this.getTenantId();
    // Role-based scope (client policy 2026-10-09): a Sales Head sees only
    // their own + reporting-chain queries/bookings — UNLESS the role
    // carries view_all_bookings_queries, which resolves to `null` (no
    // filter). Admin also reaches this endpoint and resolves to `null`,
    // so its numbers are unchanged. When non-null, every aggregate below
    // is filtered to the visible-user set.
    const visibleUserIds = await this.userReporting.resolveVisibleUserIds('query');
    // `null` ⇒ scope 'all' (Admin via booking:delete, or a role with the
    // view_all_bookings_queries flag). Only those roles may see the
    // agent-wise Source Revenue Mix — for anyone scoped it would leak the
    // tenant's full agent roster + cross-team counts, so we don't even
    // compute/return it (client policy 2026-10-10; same rationale as the
    // 3+ char agent search on Query/Booking). The FE also hides the card.
    const canSeeAllAgents = visibleUserIds === null;
    const [funnelByRep, agingQueries, sourceRevenueMix, quoteTurnaround] = await Promise.all([
      this.getFunnelByRep(tenantId, startDate, endDate, destinationId, visibleUserIds),
      this.getAgingQueries(tenantId, destinationId, visibleUserIds),
      canSeeAllAgents
        ? this.getSourceRevenueMix(tenantId, startDate, endDate, destinationId, visibleUserIds)
        : Promise.resolve([] as Awaited<ReturnType<DashboardService['getSourceRevenueMix']>>),
      this.getQuoteTurnaround(tenantId, startDate, endDate, destinationId, visibleUserIds),
    ]);
    return {
      funnel_by_rep: funnelByRep,
      aging_queries: agingQueries,
      source_revenue_mix: sourceRevenueMix,
      quote_turnaround: quoteTurnaround,
    };
  }

  /**
   * For each user that owns or shares a query in the period, return
   * { user_id, user_name, queries, quotes, bookings, conversion_pct }.
   *
   * "Owns or shares" = `created_by = user.id` OR `user.id = ANY(sales_team)`.
   * `sales_team` is a jsonb string array, hence the `?` operator.
   *
   * Conversion = bookings / quotes (since you need a quote before you
   * book). NULL when quotes = 0 to avoid divide-by-zero on the FE.
   */
  private async getFunnelByRep(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
    visibleUserIds: VisibleUserIds = null,
  ) {
    // Build the period filter once and reuse — Postgres positional
    // params start at $1 = tenant_id. Destination is appended after
    // the date bounds so every CTE stays internally consistent.
    const params: (string | number | string[])[] = [tenantId];
    let periodSql = '';
    if (startDate) {
      params.push(startDate);
      periodSql += ` AND q.created_at >= $${params.length}`;
    }
    if (endDate) {
      params.push(endDate + 'T23:59:59');
      periodSql += ` AND q.created_at <= $${params.length}`;
    }
    periodSql += this.destSql(params, destinationId);
    // Role-scope every `queries` reference in both CTE queries below.
    // Pushed after the period params so the $-index is stable for both.
    periodSql += this.queryVisibilityRawSql(params, visibleUserIds);

    // Step 1 — collect candidate user IDs from queries in the period.
    // Pull both `created_by` and every entry in `sales_team[]`.
    const userRows = await this.queryRepo.manager.query(
      `
      WITH period_queries AS (
        SELECT id, created_by, sales_team
        FROM queries q
        WHERE q.tenant_id = $1 AND q.is_deleted = false${periodSql}
      ),
      flattened AS (
        SELECT DISTINCT created_by AS user_id FROM period_queries WHERE created_by IS NOT NULL
        UNION
        SELECT DISTINCT (jsonb_array_elements_text(sales_team))::uuid AS user_id FROM period_queries WHERE jsonb_typeof(sales_team) = 'array'
      )
      SELECT user_id FROM flattened
      `,
      params,
    );

    const userIds: string[] = (userRows as { user_id: string }[])
      .map((r) => r.user_id)
      .filter(Boolean);
    if (userIds.length === 0) return [];

    const users = await this.userRepo.find({
      where: userIds.map((id) => ({ id, tenant_id: tenantId })),
      select: {
        id: true,
        name: true,
      },
    });
    const userMap = new Map(users.map((u) => [u.id, u.name]));

    // Step 2 — for each user, count queries / quotes / bookings whose
    // parent query is owned-or-shared by this user. Single round-trip
    // via a CTE that explodes sales_team and tags rows.
    const counts = await this.queryRepo.manager.query(
      `
      WITH owners AS (
        SELECT q.id AS query_id, q.created_by AS user_id, q.is_walk_in
        FROM queries q
        WHERE q.tenant_id = $1 AND q.is_deleted = false${periodSql}
        UNION
        SELECT q.id, (jsonb_array_elements_text(q.sales_team))::uuid AS user_id, q.is_walk_in
        FROM queries q
        WHERE q.tenant_id = $1 AND q.is_deleted = false AND jsonb_typeof(q.sales_team) = 'array'${periodSql}
      ),
      query_per_user AS (
        -- Walk-in queries are not pipeline leads — exclude from the
        -- per-rep query tally. See migration 1787600000000.
        SELECT user_id, COUNT(DISTINCT query_id) AS queries
        FROM owners WHERE is_walk_in = false GROUP BY user_id
      ),
      quote_per_user AS (
        SELECT o.user_id, COUNT(DISTINCT qt.id) AS quotes
        FROM owners o
        JOIN query_quotes qt ON qt.query_id = o.query_id AND qt.is_deleted = false
        GROUP BY o.user_id
      ),
      booking_per_user AS (
        -- Walk-in bookings ARE real bookings — keep them in the tally so
        -- a rep who closed only walk-ins still shows their bookings.
        SELECT o.user_id, COUNT(DISTINCT b.id) AS bookings
        FROM owners o
        JOIN bookings b ON b.query_id = o.query_id AND b.is_deleted = false
        GROUP BY o.user_id
      ),
      all_users AS (
        SELECT user_id FROM query_per_user
        UNION SELECT user_id FROM quote_per_user
        UNION SELECT user_id FROM booking_per_user
      )
      SELECT
        u.user_id,
        COALESCE(q.queries, 0) AS queries,
        COALESCE(qt.quotes, 0) AS quotes,
        COALESCE(b.bookings, 0) AS bookings
      FROM all_users u
      LEFT JOIN query_per_user q ON q.user_id = u.user_id
      LEFT JOIN quote_per_user qt ON qt.user_id = u.user_id
      LEFT JOIN booking_per_user b ON b.user_id = u.user_id
      `,
      params,
    );

    return (
      counts as {
        user_id: string;
        queries: number | string;
        quotes: number | string;
        bookings: number | string;
      }[]
    )
      .map((row) => {
        const queries = Number(row.queries) || 0;
        const quotes = Number(row.quotes) || 0;
        const bookings = Number(row.bookings) || 0;
        return {
          user_id: row.user_id,
          user_name: userMap.get(row.user_id) || 'Unknown',
          queries,
          quotes,
          bookings,
          // Conversion = bookings / quotes. NULL when no quotes — the
          // FE shows "—" for these, not 0% (which would imply rejected).
          conversion_pct: quotes > 0 ? Math.round((bookings / quotes) * 100) : null,
        };
      })
      .sort((a, b) => b.queries - a.queries);
  }

  /**
   * Counts of open queries (stage NEW or IN_PROGRESS) that have been
   * sitting unactioned for >3 days and >7 days. The thresholds match
   * scope.md "Aging Queries (>3d / >7d)". Date filter from the page
   * pills is intentionally NOT applied — aging is always relative to
   * "now", regardless of which period the user is viewing.
   */
  private async getAgingQueries(
    tenantId: string,
    destinationId?: string,
    visibleUserIds: VisibleUserIds = null,
  ) {
    const now = new Date();
    const cutoff3 = new Date(now);
    cutoff3.setDate(now.getDate() - 3);
    const cutoff7 = new Date(now);
    cutoff7.setDate(now.getDate() - 7);

    const baseQb = () => {
      const qb = this.queryRepo
        .createQueryBuilder('q')
        .where('q.tenant_id = :tenantId', { tenantId })
        .andWhere('q.is_deleted = false')
        .andWhere('q.stage IN (:...stages)', { stages: [QueryStage.NEW, QueryStage.IN_PROGRESS] });
      if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
      applyQueryRowVisibility(qb, 'q', visibleUserIds);
      return qb;
    };

    const [over3, over7] = await Promise.all([
      baseQb().andWhere('q.created_at < :c', { c: cutoff3 }).getCount(),
      baseQb().andWhere('q.created_at < :c', { c: cutoff7 }).getCount(),
    ]);
    return { over_3_days: over3, over_7_days: over7 };
  }

  /**
   * Booked revenue grouped by trip source (B2B agent / direct / etc.),
   * in the booking's selling currency. Sums `selling_total` because
   * that's the customer-facing revenue (operational `grand_total` is
   * cost). Ordered descending so the largest contributor lands on top.
   */
  private async getSourceRevenueMix(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
    visibleUserIds: VisibleUserIds = null,
  ) {
    const qb = this.bookingRepo
      .createQueryBuilder('b')
      .innerJoin('b.query', 'q', 'q.is_deleted = false')
      .leftJoin('q.source', 's')
      .select('s.id', 'source_id')
      .addSelect('s.name', 'source_name')
      .addSelect('COALESCE(SUM(b.selling_total), 0)', 'revenue')
      .addSelect('COUNT(b.id)', 'bookings')
      .where('b.tenant_id = :tenantId', { tenantId })
      .andWhere('b.is_deleted = false')
      .andWhere('b.status <> :cancelled', { cancelled: 'cancelled' })
      .groupBy('s.id')
      .addGroupBy('s.name')
      .orderBy('revenue', 'DESC');
    if (startDate) qb.andWhere('b.created_at >= :startDate', { startDate });
    if (endDate) qb.andWhere('b.created_at <= :endDate', { endDate: endDate + 'T23:59:59' });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    applyBookingRowVisibility(qb, 'b', 'q', visibleUserIds);
    const rows = await qb.getRawMany();
    return rows.map((r) => ({
      source_id: r.source_id,
      source_name: r.source_name || 'Unknown',
      revenue: Number(r.revenue) || 0,
      bookings: Number(r.bookings) || 0,
    }));
  }

  /**
   * Median + p95 turnaround time from a query landing to its first
   * quote. Computed in seconds via Postgres percentile_cont. NULL when
   * no quotes exist in the period — the FE shows "—".
   */
  private async getQuoteTurnaround(
    tenantId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
    visibleUserIds: VisibleUserIds = null,
  ) {
    const params: (string | number | string[])[] = [tenantId];
    let filterSql = '';
    if (startDate) {
      params.push(startDate);
      filterSql += ` AND q.created_at >= $${params.length}`;
    }
    if (endDate) {
      params.push(endDate + 'T23:59:59');
      filterSql += ` AND q.created_at <= $${params.length}`;
    }
    filterSql += this.destSql(params, destinationId);
    filterSql += this.queryVisibilityRawSql(params, visibleUserIds);

    const rows = await this.queryRepo.manager.query(
      `
      WITH first_quote AS (
        SELECT q.id AS query_id, MIN(qt.created_at) AS first_quote_at
        FROM queries q
        JOIN query_quotes qt ON qt.query_id = q.id AND qt.is_deleted = false
        WHERE q.tenant_id = $1 AND q.is_deleted = false${filterSql}
        GROUP BY q.id
      ),
      diffs AS (
        SELECT GREATEST(0, EXTRACT(EPOCH FROM (fq.first_quote_at - q.created_at))) AS secs
        FROM first_quote fq
        JOIN queries q ON q.id = fq.query_id
      )
      SELECT
        percentile_cont(0.5) WITHIN GROUP (ORDER BY secs)  AS median_secs,
        percentile_cont(0.95) WITHIN GROUP (ORDER BY secs) AS p95_secs,
        COUNT(*) AS sample_size
      FROM diffs
      `,
      params,
    );

    const r = rows[0] || {};
    const sample = Number(r.sample_size) || 0;
    return {
      median_seconds: sample > 0 ? Number(r.median_secs) || null : null,
      p95_seconds: sample > 0 ? Number(r.p95_secs) || null : null,
      sample_size: sample,
    };
  }

  // ───────────────────────────────────────────────────────────────────
  // Sales Team dashboard — per-user aggregates ("My X" cards).
  //
  // Endpoint is JWT-only (any authed user), but every query is scoped
  // to the caller's user_id. A Sales Head can technically call this
  // and get their own personal numbers — that's fine, they're a
  // user too.
  //
  // "Mine" means owned-or-shared, matching the Sales Head funnel
  // semantics: created_by = me OR me ∈ sales_team.
  // ───────────────────────────────────────────────────────────────────

  async getSalesTeamStats(
    userId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
  ) {
    const tenantId = this.getTenantId();
    const [openQueries, pendingQuotes, mtdConversions, hotLeads] = await Promise.all([
      this.countMyOpenQueries(tenantId, userId, destinationId),
      this.countMyPendingQuotes(tenantId, userId, destinationId),
      this.countMyConversions(tenantId, userId, startDate, endDate, destinationId),
      this.countMyHotLeads(tenantId, userId, destinationId),
    ]);
    return {
      my_open_queries: openQueries,
      my_pending_quotes: pendingQuotes,
      my_mtd_conversions: mtdConversions,
      my_hot_leads: hotLeads,
      // Today's Follow-ups is a deliberate stub — there's no
      // follow_up_date field on QueryEntity yet. Returning null tells
      // the FE to render "—" with a tooltip explaining the gap rather
      // than fake a number.
      my_followups_today: null,
    };
  }

  /** Open = stage in (new, in_progress). Owned-or-shared. */
  private async countMyOpenQueries(
    tenantId: string,
    userId: string,
    destinationId?: string,
  ): Promise<number> {
    const qb = this.queryRepo
      .createQueryBuilder('q')
      .where('q.tenant_id = :tenantId', { tenantId })
      .andWhere('q.is_deleted = false')
      .andWhere('q.stage IN (:...stages)', { stages: [QueryStage.NEW, QueryStage.IN_PROGRESS] })
      .andWhere('(q.created_by = :userId OR q.sales_team @> :userJson)', {
        userId,
        userJson: JSON.stringify([userId]),
      });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    return qb.getCount();
  }

  /** Quotes on queries that are still in pre-conversion stages. */
  private async countMyPendingQuotes(
    tenantId: string,
    userId: string,
    destinationId?: string,
  ): Promise<number> {
    const qb = this.quoteRepo
      .createQueryBuilder('qt')
      .leftJoin('qt.query', 'q')
      .where('qt.tenant_id = :tenantId', { tenantId })
      .andWhere('qt.is_deleted = false')
      .andWhere('q.is_deleted = false')
      .andWhere('q.stage IN (:...stages)', {
        stages: [QueryStage.NEW, QueryStage.IN_PROGRESS, QueryStage.ON_HOLD],
      })
      .andWhere('(q.created_by = :userId OR q.sales_team @> :userJson)', {
        userId,
        userJson: JSON.stringify([userId]),
      });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    return qb.getCount();
  }

  /** Bookings I touched, born in the period. */
  private async countMyConversions(
    tenantId: string,
    userId: string,
    startDate?: string,
    endDate?: string,
    destinationId?: string,
  ): Promise<number> {
    const qb = this.bookingRepo
      .createQueryBuilder('b')
      .leftJoin('b.query', 'q')
      .where('b.tenant_id = :tenantId', { tenantId })
      .andWhere('b.is_deleted = false')
      .andWhere('q.is_deleted = false')
      .andWhere('(q.created_by = :userId OR q.sales_team @> :userJson)', {
        userId,
        userJson: JSON.stringify([userId]),
      });
    if (startDate) qb.andWhere('b.created_at >= :startDate', { startDate });
    if (endDate) qb.andWhere('b.created_at <= :endDate', { endDate: endDate + 'T23:59:59' });
    if (destinationId) qb.andWhere('q.destination_id = :destinationId', { destinationId });
    return qb.getCount();
  }

  /**
   * Hot leads — queries flagged with 'hot' or 'priority' in remark_tags.
   * Approximation until a dedicated `is_priority` flag lands. Using
   * jsonb `?|` operator (any-of) since remark_tags is a jsonb array.
   */
  private async countMyHotLeads(
    tenantId: string,
    userId: string,
    destinationId?: string,
  ): Promise<number> {
    const params: (string | number)[] = [
      tenantId,
      QueryStage.NEW,
      QueryStage.IN_PROGRESS,
      userId,
      JSON.stringify([userId]),
    ];
    let destSql = '';
    if (destinationId) {
      params.push(destinationId);
      destSql = ` AND q.destination_id = $${params.length}`;
    }
    const rows = await this.queryRepo.manager.query(
      `
      SELECT COUNT(*)::int AS c FROM queries q
      WHERE q.tenant_id = $1 AND q.is_deleted = false
        AND q.stage IN ($2, $3)
        AND (q.created_by = $4 OR q.sales_team @> $5::jsonb)
        AND (
          q.remark_tags ?| array['hot', 'Hot', 'priority', 'Priority', 'HOT', 'PRIORITY']
        )${destSql}
      `,
      params,
    );
    return Number(rows[0]?.c) || 0;
  }

  // ───────────────────────────────────────────────────────────────────
  // Ops Manager dashboard — operational execution today / tomorrow.
  //
  // All cards in this aggregate intentionally ignore the page filter —
  // the Ops view is "what needs my attention right now", not a
  // historical lens. The dashboard pills still render for consistency
  // (and so the user can switch to a different role's view via login)
  // but they don't influence what comes back here.
  //
  // Data drawn from booking_transport_items + booking_activity_items —
  // joined to bookings to filter out cancelled / soft-deleted rows.
  // ───────────────────────────────────────────────────────────────────

  async getOpsStats(destinationId?: string) {
    const tenantId = this.getTenantId();
    const today = new Date().toISOString().split('T')[0];
    const tmr = new Date();
    tmr.setDate(tmr.getDate() + 1);
    const tomorrow = tmr.toISOString().split('T')[0];

    const [todayMovements, unassigned, tomorrowServices, availableDrivers, specialActivities] =
      await Promise.all([
        this.countMovementsOnDate(tenantId, today, destinationId),
        this.countUnassignedUpcoming(tenantId, destinationId),
        this.countMovementsOnDate(tenantId, tomorrow, destinationId),
        this.countAvailableDrivers(tenantId, destinationId),
        this.getSpecialActivityBookings(tenantId, destinationId),
      ]);

    return {
      today_movements: todayMovements,
      unassigned_bookings: unassigned,
      tomorrow_services: tomorrowServices,
      available_drivers: availableDrivers,
      special_activity_bookings: specialActivities,
      // Conflicts / vouchers / supplier-confirmations / briefings stay
      // null until the underlying schema fields land. FE renders "—"
      // with the explainer hint; same pattern as my_followups_today.
      service_conflicts: null,
      voucher_dispatch_pending: null,
      supplier_confirmations_pending: null,
    };
  }

  /** Trip Coordinator — services on bookings I created or own. */
  async getTripCoordinatorStats(userId: string, destinationId?: string) {
    const tenantId = this.getTenantId();
    const today = new Date().toISOString().split('T')[0];
    const tmr = new Date();
    tmr.setDate(tmr.getDate() + 1);
    const tomorrow = tmr.toISOString().split('T')[0];

    const [myToday, myTomorrow] = await Promise.all([
      this.countMyMovementsOnDate(tenantId, userId, today, destinationId),
      this.countMyMovementsOnDate(tenantId, userId, tomorrow, destinationId),
    ]);

    return {
      my_today_services: myToday,
      my_tomorrow_services: myTomorrow,
      vouchers_to_send: null,
      driver_briefings_due: null,
      supplier_confirmations_pending: null,
    };
  }

  private async getSpecialActivityBookings(
    tenantId: string,
    destinationId?: string,
  ): Promise<
    Array<{
      booking_id: string;
      booking_number: string;
      guest_name: string | null;
      activity_name: string;
      activity_date: string;
      ticket_status: string;
    }>
  > {
    const params: (string | number)[] = [tenantId];
    const destSql = this.destSql(params, destinationId, 'q');
    const rows = await this.bookingRepo.manager.query(
      `
      SELECT * FROM (
        -- One row per (booking, activity, date); age-group siblings
        -- collapse, preferring a still-Pending ticket so the ticketing
        -- team never misses one.
        SELECT DISTINCT ON (b.id, a.id, bai.date)
          b.id AS booking_id,
          b.booking_number,
          q.guest_name,
          a.name AS activity_name,
          bai.date::text AS activity_date,
          bai.ticket_status
        FROM booking_activity_items bai
        JOIN bookings b ON b.id = bai.booking_id AND b.tenant_id = $1
        JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        JOIN activities a ON a.id = bai.activity_id AND a.is_deleted = false
        WHERE bai.tenant_id = $1
          AND bai.is_deleted = false
          AND b.is_deleted = false
          AND b.status <> 'cancelled'
          AND a.is_special = true
          -- "Future" in IST (business timezone), not the DB server's.
          AND bai.date >= (NOW() AT TIME ZONE 'Asia/Kolkata')::date
          ${destSql}
        ORDER BY b.id, a.id, bai.date, (COALESCE(bai.ticket_status, 'Pending') = 'Pending') DESC
      ) upcoming
      -- Soonest first so the 50-row cap never hides the next activities.
      ORDER BY activity_date ASC, booking_number ASC
      LIMIT 50
      `,
      params,
    );
    return (rows || []).map(
      (r: {
        booking_id: string;
        booking_number: string;
        guest_name: string | null;
        activity_name: string;
        activity_date: string;
        ticket_status: string;
      }) => ({
        booking_id: r.booking_id,
        booking_number: r.booking_number,
        guest_name: r.guest_name,
        activity_name: r.activity_name,
        activity_date: r.activity_date,
        ticket_status: r.ticket_status || 'Pending',
      }),
    );
  }

  /**
   * Count of transport + activity items scheduled on the given date,
   * across all non-deleted, non-cancelled bookings in the tenant.
   * Single SQL with UNION ALL — keeps the round-trip count to one.
   */
  private async countMovementsOnDate(
    tenantId: string,
    isoDate: string,
    destinationId?: string,
  ): Promise<number> {
    const destSql = destinationId
      ? ` AND q.destination_id = $3`
      : '';
    const params: (string | number)[] = destinationId
      ? [tenantId, isoDate, destinationId]
      : [tenantId, isoDate];
    const rows = await this.bookingRepo.manager.query(
      `
      SELECT COUNT(*)::int AS c FROM (
        SELECT bti.id FROM booking_transport_items bti
        JOIN bookings b ON b.id = bti.booking_id
        INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        WHERE b.tenant_id = $1 AND b.is_deleted = false AND b.status <> 'cancelled'
          AND bti.is_deleted = false AND bti.date = $2${destSql}
        UNION ALL
        SELECT bai.id FROM booking_activity_items bai
        JOIN bookings b ON b.id = bai.booking_id
        INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        WHERE b.tenant_id = $1 AND b.is_deleted = false AND b.status <> 'cancelled'
          AND bai.is_deleted = false AND bai.date = $2${destSql}
      ) movements
      `,
      params,
    );
    return Number(rows[0]?.c) || 0;
  }

  /**
   * Same as countMovementsOnDate but scoped to bookings the given user
   * created OR is in the parent query's sales_team. Keeps the
   * Coordinator's view focused on their work.
   */
  private async countMyMovementsOnDate(
    tenantId: string,
    userId: string,
    isoDate: string,
    destinationId?: string,
  ): Promise<number> {
    const destSql = destinationId ? ` AND q.destination_id = $5` : '';
    const params: (string | number)[] = destinationId
      ? [tenantId, userId, JSON.stringify([userId]), isoDate, destinationId]
      : [tenantId, userId, JSON.stringify([userId]), isoDate];
    const rows = await this.bookingRepo.manager.query(
      `
      WITH my_bookings AS (
        SELECT b.id
        FROM bookings b
        INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
        WHERE b.tenant_id = $1 AND b.is_deleted = false AND b.status <> 'cancelled'
          AND (b.created_by = $2 OR q.created_by = $2 OR q.sales_team @> $3::jsonb)${destSql}
      )
      SELECT COUNT(*)::int AS c FROM (
        SELECT bti.id FROM booking_transport_items bti
        JOIN my_bookings mb ON mb.id = bti.booking_id
        WHERE bti.is_deleted = false AND bti.date = $4
        UNION ALL
        SELECT bai.id FROM booking_activity_items bai
        JOIN my_bookings mb ON mb.id = bai.booking_id
        WHERE bai.is_deleted = false AND bai.date = $4
      ) movements
      `,
      params,
    );
    return Number(rows[0]?.c) || 0;
  }

  /**
   * Upcoming bookings missing a driver or vehicle on at least one of
   * their transport items. We DON'T count activity items here — those
   * are typically guide-only. Every transport item should be assigned
   * before the booking goes "ongoing".
   */
  private async countUnassignedUpcoming(
    tenantId: string,
    destinationId?: string,
  ): Promise<number> {
    // A transport leg is assigned when it has a driver (registered id,
    // free-text name, or junction-table assignment) AND a vehicle
    // (registered id or free-text plate). Counting only item.driver_id
    // / item.vehicle_id over-counted after assignments moved onto the
    // supplier junction + free-text fallbacks.
    const destSql = destinationId ? ` AND q.destination_id = $2` : '';
    const params: (string | number)[] = destinationId ? [tenantId, destinationId] : [tenantId];
    const rows = await this.bookingRepo.manager.query(
      `
      SELECT COUNT(DISTINCT b.id)::int AS c
      FROM bookings b
      JOIN booking_transport_items bti ON bti.booking_id = b.id AND bti.is_deleted = false
      INNER JOIN queries q ON q.id = b.query_id AND q.is_deleted = false
      WHERE b.tenant_id = $1 AND b.is_deleted = false
        AND b.status IN ('upcoming', 'ongoing')
        ${destSql}
        AND NOT (
          (
            bti.driver_id IS NOT NULL
            OR NULLIF(BTRIM(bti.driver_name_text), '') IS NOT NULL
            OR EXISTS (
              SELECT 1 FROM booking_transport_item_suppliers j
              WHERE j.transport_item_id = bti.id
                AND j.is_deleted = false
                AND (
                  j.driver_id IS NOT NULL
                  OR NULLIF(BTRIM(j.driver_name_text), '') IS NOT NULL
                )
            )
          )
          AND (
            bti.vehicle_id IS NOT NULL
            OR NULLIF(BTRIM(bti.vehicle_no_text), '') IS NOT NULL
          )
        )
      `,
      params,
    );
    return Number(rows[0]?.c) || 0;
  }
}