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 { DriverEntity } from '../../../entities/driver.entity';

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

  async findAllWithRelations(): Promise<DriverEntity[]> {
    const tenantId = this.getTenantId();
    return this.repo.find({
      where: { tenant_id: tenantId, is_deleted: false },
      relations: {
        destination: true,
        supplier: true,
      },
      order: { name: 'ASC' },
    });
  }

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

  async findBySupplier(supplierId: string): Promise<DriverEntity[]> {
    const tenantId = this.getTenantId();
    return this.repo.find({
      where: { supplier_id: supplierId, tenant_id: tenantId, is_deleted: false },
      relations: {
        destination: true,
        supplier: true,
      },
      order: { name: 'ASC' },
    });
  }

  async findByLicenseNo(licenseNo: string): Promise<DriverEntity | null> {
    const tenantId = this.getTenantId();
    return this.repo.findOne({
      where: { license_no: licenseNo, tenant_id: tenantId, is_deleted: false },
    });
  }

  /** Phone match — compares digits only, ignoring spaces/-/+/(). Used
   *  by the "sync to masters" flow so ops doesn't end up with 3 driver
   *  rows for the same person formatted slightly differently. */
  async findByPhoneDigits(digits: string): Promise<DriverEntity | null> {
    const tenantId = this.getTenantId();
    if (!digits) return null;
    return this.repo
      .createQueryBuilder('d')
      .where('d.tenant_id = :tenantId', { tenantId })
      .andWhere('d.is_deleted = false')
      .andWhere(`REGEXP_REPLACE(d.phone, '[^0-9]', '', 'g') = :digits`, { digits })
      .getOne();
  }

  /** Name match (case-insensitive) scoped to a supplier — prevents two
   *  "Ali" drivers at the same supplier from piling up. */
  async findByNameIForSupplier(name: string, supplierId: string): Promise<DriverEntity | null> {
    const tenantId = this.getTenantId();
    if (!name || !supplierId) return null;
    return this.repo
      .createQueryBuilder('d')
      .where('d.tenant_id = :tenantId', { tenantId })
      .andWhere('d.supplier_id = :supplierId', { supplierId })
      .andWhere('d.is_deleted = false')
      .andWhere('LOWER(d.name) = LOWER(:name)', { name })
      .getOne();
  }

  async resolveSupplierDestination(supplierId: string): Promise<string | null> {
    const tenantId = this.getTenantId();
    if (!supplierId) return null;
    const rows: Array<{ destination_id: string }> = await this.repo.manager.query(
      `SELECT destination_id FROM suppliers
       WHERE id = $1 AND tenant_id = $2 AND is_deleted = false
       LIMIT 1`,
      [supplierId, tenantId],
    );
    return rows[0]?.destination_id ?? null;
  }

  /** Destination of a supplier IN THIS TENANT, or null when the supplier
   *  doesn't belong to the tenant (guards client-supplied FKs). */
  async tenantSupplierExists(supplierId: string): Promise<boolean> {
    const rows: Array<{ id: string }> = await this.repo.manager.query(
      `SELECT id FROM suppliers WHERE id = $1 AND tenant_id = $2 AND is_deleted = false LIMIT 1`,
      [supplierId, this.getTenantId()],
    );
    return rows.length > 0;
  }

  async tenantDestinationExists(destinationId: string): Promise<boolean> {
    const rows: Array<{ id: string }> = await this.repo.manager.query(
      `SELECT id FROM destinations WHERE id = $1 AND tenant_id = $2 AND is_deleted = false LIMIT 1`,
      [destinationId, this.getTenantId()],
    );
    return rows.length > 0;
  }

  /** Driver ids (among `driverIds`) already assigned to a live transport
   *  row on `date` — primary row or an extra-supplier split. */
  async findDriversBusyOn(driverIds: string[], date: string): Promise<Set<string>> {
    if (driverIds.length === 0) return new Set();
    const rows: Array<{ driver_id: string }> = await this.repo.manager.query(
      `SELECT DISTINCT ti.driver_id
         FROM booking_transport_items ti
         JOIN bookings b ON b.id = ti.booking_id AND b.is_deleted = false
        WHERE ti.tenant_id = $1 AND ti.is_deleted = false AND ti.date = $2
          AND ti.driver_id = ANY($3::uuid[])
       UNION
       SELECT DISTINCT s.driver_id
         FROM booking_transport_item_suppliers s
         JOIN booking_transport_items ti ON ti.id = s.transport_item_id AND ti.is_deleted = false
        WHERE s.tenant_id = $1 AND s.is_deleted = false AND ti.date = $2
          AND s.driver_id = ANY($3::uuid[])`,
      [this.getTenantId(), date, driverIds],
    );
    return new Set(rows.map((r) => r.driver_id));
  }

  /** Serialises placeholder creation per (tenant, supplier) so two
   *  concurrent ⚡ Auto clicks can't both mint the same "Driver N". */
  async withSupplierLock<R>(supplierId: string, fn: () => Promise<R>): Promise<R> {
    return this.repo.manager.transaction(async (em) => {
      await em.query('SELECT pg_advisory_xact_lock(hashtext($1))', [
        `driver-placeholder:${this.getTenantId()}:${supplierId}`,
      ]);
      return fn();
    });
  }
}