import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Brackets, Repository } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { TenantAwareRepository } from '../../../common/repositories/tenant-aware.repository';
import { QueryEntity } from '../../../entities/query.entity';
import { allocateUniqueReferenceNumber } from '../../../common/utils/reference-number';
import {
  applyQueryRowVisibility,
  queryVisibilitySql,
  type VisibleUserIds,
} from '../../../common/auth/row-visibility';

@Injectable()
export class QueryRepository extends TenantAwareRepository<QueryEntity> {
  constructor(@InjectRepository(QueryEntity) repo: Repository<QueryEntity>, cls: ClsService) {
    super(repo, cls);
  }

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

  async getNextQueryNumber(): Promise<string> {
    // Per-tenant 6-digit random `QRY-NNNNNN` — see reference-number.ts.
    return allocateUniqueReferenceNumber('QRY', this.getTenantId(), (sql, params) =>
      this.repo.manager.query(sql, params),
    );
  }

  /**
   * Find queries with all the bells and whistles the controller needs:
   * pagination, search, structured filters, and the row-level
   * visibility scope from common/auth/visibility.ts.
   *
   * Replaces the previous chain (TenantAwareRepository.findPaginated +
   * post-fetch filtering) so search and visibility compose correctly:
   *   (tenant + soft_delete + structured filters)
   *     AND (search ILIKE across columns OR no-search)
   *     AND (visibility: all OR own)
   *
   * `visibleUserIds` controls scope: null = all; otherwise created_by or
   * sales_team overlap with any id (viewer + reporting chain).
   */
  async findPaginatedScoped(opts: {
    page?: number;
    limit?: number;
    search?: string;
    filters?: { stage?: string; destinationId?: string; sourceId?: string };
    /** Null = no scoping (admin / ops manager / trip coord). */
    visibleUserIds: VisibleUserIds;
  }) {
    const page = opts.page || 1;
    const limit = opts.limit || 10;
    const skip = (page - 1) * limit;
    const tenantId = this.getTenantId();

    const qb = this.repo
      .createQueryBuilder('q')
      .leftJoinAndSelect('q.destination', 'destination')
      .leftJoinAndSelect('q.source', 'source')
      .leftJoinAndSelect('q.quotes', 'quotes', 'quotes.is_deleted = false')
      .where('q.tenant_id = :tenantId', { tenantId })
      .andWhere('q.is_deleted = false')
      // Walk-in (instant-booking) queries are not pipeline queries —
      // they exist only to back a quick booking. Keep them out of the
      // Queries list. See migration 1787600000000.
      .andWhere('q.is_walk_in = false');

    // Structured filters
    if (opts.filters?.stage) qb.andWhere('q.stage = :stage', { stage: opts.filters.stage });
    if (opts.filters?.destinationId)
      qb.andWhere('q.destination_id = :destinationId', {
        destinationId: opts.filters.destinationId,
      });
    if (opts.filters?.sourceId)
      qb.andWhere('q.source_id = :sourceId', { sourceId: opts.filters.sourceId });

    // Search — OR across the same four columns the original
    // findPaginated covered. Wrapped in Brackets so the OR-group
    // doesn't bleed into the visibility AND below.
    const search = opts.search?.trim();
    if (search) {
      qb.andWhere(
        new Brackets((sub) => {
          sub
            .where('q.query_number ILIKE :s', { s: `%${search}%` })
            .orWhere('q.guest_name ILIKE :s', { s: `%${search}%` })
            .orWhere('q.guest_mobile ILIKE :s', { s: `%${search}%` })
            .orWhere('q.guest_email ILIKE :s', { s: `%${search}%` });
        }),
      );
    }

    applyQueryRowVisibility(qb, 'q', opts.visibleUserIds);

    qb.orderBy('q.created_at', 'DESC').skip(skip).take(limit);

    const [items, total] = await qb.getManyAndCount();
    return {
      items,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }

  async findAvailable(visibleUserIds: VisibleUserIds): Promise<QueryEntity[]> {
    const tenantId = this.getTenantId();
    const qb = this.repo
      .createQueryBuilder('q')
      .leftJoin(
        'quotations',
        'qt',
        'qt.query_id = q.id AND qt.tenant_id = q.tenant_id AND qt.is_deleted = false',
      )
      .where('q.tenant_id = :tenantId', { tenantId })
      .andWhere('q.is_deleted = false')
      .andWhere('q.stage = :stage', { stage: 'new' })
      .andWhere('qt.id IS NULL');
    applyQueryRowVisibility(qb, 'q', visibleUserIds);
    return qb.leftJoinAndSelect('q.destination', 'dest').orderBy('q.created_at', 'DESC').getMany();
  }

  async countByStage(
    visibleUserIds: VisibleUserIds,
    filters?: { destinationId?: string; sourceId?: string; search?: string },
  ): Promise<Record<string, number>> {
    const tenantId = this.getTenantId();
    const qb = this.repo
      .createQueryBuilder('q')
      .select('q.stage', 'stage')
      .addSelect('COUNT(*)', 'count')
      .where('q.tenant_id = :tenantId', { tenantId })
      .andWhere('q.is_deleted = false')
      // Exclude walk-in queries from the stage-tab counts — same policy
      // as the list above. See migration 1787600000000.
      .andWhere('q.is_walk_in = false');

    if (filters?.destinationId)
      qb.andWhere('q.destination_id = :destinationId', { destinationId: filters.destinationId });
    if (filters?.sourceId) qb.andWhere('q.source_id = :sourceId', { sourceId: filters.sourceId });
    const search = filters?.search?.trim();
    if (search) {
      qb.andWhere(
        new Brackets((sub) => {
          sub
            .where('q.query_number ILIKE :s', { s: `%${search}%` })
            .orWhere('q.guest_name ILIKE :s', { s: `%${search}%` })
            .orWhere('q.guest_mobile ILIKE :s', { s: `%${search}%` })
            .orWhere('q.guest_email ILIKE :s', { s: `%${search}%` });
        }),
      );
    }

    applyQueryRowVisibility(qb, 'q', visibleUserIds);
    const result = await qb.groupBy('q.stage').getRawMany();
    const counts: Record<string, number> = {};
    for (const row of result) {
      counts[row.stage] = parseInt(row.count, 10);
    }
    return counts;
  }

  /**
   * Visibility-aware existence check. Used by the service to translate
   * "row exists but you can't see it" into a 404 (not 403) — never
   * leak the existence of a row outside the viewer's scope.
   */
  async isVisibleTo(id: string, visibleUserIds: VisibleUserIds): Promise<boolean> {
    if (visibleUserIds === null) return true;
    if (visibleUserIds.length === 0) return false;
    const tenantId = this.getTenantId();
    const visSql = queryVisibilitySql('queries');
    const row = await this.repo.manager.query(
      `SELECT 1 FROM queries
       WHERE id = $1 AND tenant_id = $2 AND is_deleted = false
         AND ${visSql}
       LIMIT 1`,
      [id, tenantId, visibleUserIds],
    );
    return row.length > 0;
  }
}