import { ConflictException, Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository, type FindOptionsWhere } from 'typeorm';
import { ClsService } from 'nestjs-cls';
import { TenantAwareRepository } from '../../../common/repositories/tenant-aware.repository';
import { AgentCostEntity } from '../../../entities/agent-cost.entity';

export interface AssignedTripSource {
  id: string;
  name: string;
  agent_cost_id: string;
}

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

  async findByName(name: string): Promise<AgentCostEntity | null> {
    const tenantId = this.getTenantId();
    return this.repo
      .createQueryBuilder('c')
      .where('c.tenant_id = :tenantId', { tenantId })
      .andWhere('c.is_deleted = false')
      .andWhere('LOWER(c.name) = LOWER(:name)', { name: name.trim() })
      .getOne();
  }

  async findActiveById(id: string): Promise<AgentCostEntity | null> {
    return this.findOne({
      where: { id, is_active: true } as FindOptionsWhere<AgentCostEntity>,
    });
  }

  async countAssigned(agentCostId: string): Promise<number> {
    const tenantId = this.getTenantId();
    const rows = (await this.repo.manager.query(
      `SELECT COUNT(*)::int AS count
       FROM trip_sources
       WHERE tenant_id = $1
         AND agent_cost_id = $2
         AND is_deleted = false`,
      [tenantId, agentCostId],
    )) as Array<{ count: number }>;
    return rows[0]?.count ?? 0;
  }

  async listAssigned(agentCostIds: string[]): Promise<AssignedTripSource[]> {
    if (agentCostIds.length === 0) return [];
    const tenantId = this.getTenantId();
    return (await this.repo.manager.query(
      `SELECT id, name, agent_cost_id
       FROM trip_sources
       WHERE tenant_id = $1
         AND is_deleted = false
         AND agent_cost_id = ANY($2::uuid[])
       ORDER BY name ASC`,
      [tenantId, agentCostIds],
    )) as AssignedTripSource[];
  }

  /** Trip sources with no Agent Cost. Filtered in SQL, not by the client. */
  async listUnassigned(): Promise<Array<{ id: string; name: string }>> {
    const tenantId = this.getTenantId();
    return (await this.repo.manager.query(
      `SELECT id, name
       FROM trip_sources
       WHERE tenant_id = $1
         AND is_deleted = false
         AND agent_cost_id IS NULL
       ORDER BY name ASC`,
      [tenantId],
    )) as Array<{ id: string; name: string }>;
  }

  /**
   * Point unassigned trip sources at this rule. Rolls back if any id
   * is missing or already assigned.
   */
  async assignUnassigned(agentCostId: string, tripSourceIds: string[]): Promise<void> {
    const tenantId = this.getTenantId();
    const userId = this.getUserId();
    await this.repo.manager.transaction(async (manager) => {
      const eligible = (await manager.query(
        `SELECT id
         FROM trip_sources
         WHERE tenant_id = $1
           AND is_deleted = false
           AND agent_cost_id IS NULL
           AND id = ANY($2::uuid[])`,
        [tenantId, tripSourceIds],
      )) as Array<{ id: string }>;
      if (eligible.length !== tripSourceIds.length) {
        throw new ConflictException(
          'One or more trip sources are already assigned or were not found',
        );
      }
      await manager.query(
        `UPDATE trip_sources
         SET agent_cost_id = $1, updated_at = now(), updated_by = $2
         WHERE tenant_id = $3
           AND is_deleted = false
           AND agent_cost_id IS NULL
           AND id = ANY($4::uuid[])`,
        [agentCostId, userId, tenantId, tripSourceIds],
      );
      const assigned = (await manager.query(
        `SELECT id
         FROM trip_sources
         WHERE tenant_id = $1
           AND is_deleted = false
           AND agent_cost_id = $2
           AND id = ANY($3::uuid[])`,
        [tenantId, agentCostId, tripSourceIds],
      )) as Array<{ id: string }>;
      if (assigned.length !== tripSourceIds.length) {
        throw new ConflictException(
          'One or more trip sources are already assigned or were not found',
        );
      }
    });
  }

  /** Clear the rule only when the trip source currently uses this cost. */
  async unassign(agentCostId: string, tripSourceId: string): Promise<boolean> {
    const tenantId = this.getTenantId();
    const userId = this.getUserId();
    const existing = (await this.repo.manager.query(
      `SELECT id
       FROM trip_sources
       WHERE tenant_id = $1
         AND id = $2
         AND agent_cost_id = $3
         AND is_deleted = false`,
      [tenantId, tripSourceId, agentCostId],
    )) as Array<{ id: string }>;
    if (existing.length === 0) return false;
    await this.repo.manager.query(
      `UPDATE trip_sources
       SET agent_cost_id = NULL, updated_at = now(), updated_by = $1
       WHERE tenant_id = $2
         AND id = $3
         AND agent_cost_id = $4
         AND is_deleted = false`,
      [userId, tenantId, tripSourceId, agentCostId],
    );
    return true;
  }
}