import {
  Injectable,
  NotFoundException,
  BadRequestException,
  ForbiddenException,
  Logger,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';

import {
  Lead,
  LeadStatus,
  ClientGroup,
  CLIENT_GROUP_LABELS,
  SELECTABLE_CLIENT_GROUPS,
  expandClientGroup,
  normalizeClientGroup,
} from '../entities/lead.entity';
import { LeadActivity } from '../entities/lead-activity.entity';
import { User } from '../../user/entities/user.entity';

import { CreateLeadDto } from '../dto/create-lead.dto';
import { UpdateLeadDto } from '../dto/update-lead.dto';
import { AssignLeadDto } from '../dto/assign-lead.dto';
import {
  ListLeadsQueryDto,
  KanbanQueryDto,
  KanbanLoadMoreQueryDto,
} from '../dto/list-leads.dto';
import { BulkUpdateLeadDto, BulkDestroyLeadDto } from '../dto/bulk-update-lead.dto';
import { CheckDuplicatesDto, RequestHandoverDto } from '../dto/check-duplicates.dto';
import { DuplicateLeadDetectorService } from './duplicate-lead-detector.service';
import { LeadNotificationsService } from './lead-notifications.service';

// Same permission-check pattern as the rest of your Nest modules.
const SUPER_ADMIN_IDS = [1, 8];
const MODULE_SLUG = 'leads';
const KANBAN_COLUMN_LIMIT = 50;

@Injectable()
export class LeadService {
  private readonly logger = new Logger('LeadService');

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
    private readonly duplicateDetector: DuplicateLeadDetectorService,
    private readonly leadNotifications: LeadNotificationsService,
  ) {}

  // ═══════════════════════════════════════════════════════════════════════
  // CLIENT GROUP OPTIONS — served to the frontend dropdown so the list of
  // groups lives in exactly one place (the entity) instead of being
  // duplicated in a React constant that drifts.
  // ═══════════════════════════════════════════════════════════════════════
  clientGroupOptions() {
    return {
      options: SELECTABLE_CLIENT_GROUPS.map((value) => ({
        value,
        label: CLIENT_GROUP_LABELS[value],
      })),
      // Documented for the UI: rows stored as QRS_B display and filter as QRS.
      legacy_rollup: { QRS_B: ClientGroup.QRS },
    };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // PERMISSIONS — DB-driven, same pattern used across your other modules.
  // Seed permissions rows for module slug 'leads' with actions:
  // 'view', 'view-all', 'create', 'update', 'delete', 'assign'.
  // ═══════════════════════════════════════════════════════════════════════
  private async userHasPermission(userId: number, action: string): Promise<boolean> {
    if (SUPER_ADMIN_IDS.includes(userId)) return true;

    const rolePerms = await this.dataSource.query(
      `
      SELECT 1 FROM users u
      INNER JOIN user_roles ur ON ur.user_id = u.id
      INNER JOIN role_permissions rp ON rp.role_id = ur.role_id
      INNER JOIN permissions p ON p.id = rp.permission_id
      INNER JOIN modules m ON m.id = p.module_id
      WHERE u.id = ? AND p.action = ? AND m.slug = ?
      LIMIT 1
      `,
      [userId, action, MODULE_SLUG],
    );
    if (rolePerms.length > 0) return true;

    try {
      const directPerms = await this.dataSource.query(
        `
        SELECT 1 FROM user_permissions up
        INNER JOIN permissions p ON p.id = up.permission_id
        INNER JOIN modules m ON m.id = p.module_id
        WHERE up.user_id = ? AND p.action = ? AND m.slug = ?
        LIMIT 1
        `,
        [userId, action, MODULE_SLUG],
      );
      return directPerms.length > 0;
    } catch {
      return false;
    }
  }

  private userCanViewAll(userId: number) {
    return this.userHasPermission(userId, 'view-all');
  }
  private userCanAssign(userId: number) {
    return this.userHasPermission(userId, 'assign');
  }

  // ═══════════════════════════════════════════════════════════════════════
  // LEAD CODE GENERATION — LEAD-{year}-{6-digit sequence}, scoped per year.
  // ═══════════════════════════════════════════════════════════════════════
  private async nextCode(manager = this.dataSource.manager): Promise<string> {
    const year = new Date().getFullYear();
    const prefix = `LEAD-${year}-`;

    const [row] = await manager.query(
      `SELECT lead_code FROM leads WHERE lead_code LIKE ? ORDER BY id DESC LIMIT 1 FOR UPDATE`,
      [`${prefix}%`],
    );

    let nextSeq = 1;
    if (row?.lead_code) {
      const tail = row.lead_code.slice(prefix.length);
      const parsed = parseInt(tail, 10);
      if (!isNaN(parsed)) nextSeq = parsed + 1;
    }
    return `${prefix}${String(nextSeq).padStart(6, '0')}`;
  }

  // ═══════════════════════════════════════════════════════════════════════
  // ACTIVITY LOG — stand-in for Activity::log(); swap for your real model
  // once available (see LeadActivity entity docblock).
  // ═══════════════════════════════════════════════════════════════════════
  private async logActivity(
    manager = this.dataSource.manager,
    leadId: number,
    actorLabel: string,
    message: string,
    createdBy: number | null = null,
  ) {
    await manager.insert(LeadActivity, {
      lead_id: leadId,
      created_by: createdBy,
      actor_label: actorLabel,
      message,
    });
  }

  // ═══════════════════════════════════════════════════════════════════════
  // LIST — filters mirror index(): search, status, assigned_to, source,
  // tag, date range + pagination.
  // ═══════════════════════════════════════════════════════════════════════
  async findAll(q: ListLeadsQueryDto, currentUserId: number) {
    const page = q.page || 1;
    const limit = q.limit || 20;
    const skip = (page - 1) * limit;

    const qb = this.dataSource
      .getRepository(Lead)
      .createQueryBuilder('l')
      .leftJoinAndSelect('l.assignedUser', 'assignedUser')
      .leftJoinAndSelect('l.assignedByUser', 'assignedByUser')
      .leftJoinAndSelect('l.creator', 'creator')
      .orderBy('l.created_at', 'DESC')
      .skip(skip)
      .take(limit);

    // Row-level scoping — no 'view-all' permission means "my leads only"
    // (mirrors Laravel's `mine` global scope).
    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll) {
      qb.andWhere('(l.assigned_to = :uid OR l.created_by = :uid)', { uid: currentUserId });
    }

    if (q.search) {
      qb.andWhere(
        '(l.company LIKE :s OR l.contact LIKE :s OR l.lead_code LIKE :s OR l.email LIKE :s OR l.phone LIKE :s)',
        { s: `%${q.search}%` },
      );
    }
    if (q.status) qb.andWhere('l.status = :status', { status: q.status });

    // 🆕 Client group. Expanded, not compared directly: asking for QRS has
    // to return the legacy QRS_B rows as well, otherwise the QRS filter
    // silently hides part of the QRS book.
    if (q.client_group) {
      const groups = expandClientGroup(q.client_group);
      if (groups.length) {
        qb.andWhere('l.client_group IN (:...groups)', { groups });
      }
    }

    if (q.assigned_to) qb.andWhere('l.assigned_to = :assignedTo', { assignedTo: q.assigned_to });
    if (q.source) qb.andWhere('l.source = :source', { source: q.source });
    if (q.tag) qb.andWhere('l.tags LIKE :tag', { tag: `%"${q.tag}"%` });
    if (q.date_from) qb.andWhere('l.created_at >= :from', { from: `${q.date_from} 00:00:00` });
    if (q.date_to) qb.andWhere('l.created_at <= :to', { to: `${q.date_to} 23:59:59` });

    const [data, total] = await qb.getManyAndCount();

    return {
      data,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // KANBAN — grouped by status, capped at KANBAN_COLUMN_LIMIT per column,
  // with true unbounded totals so "Load more" knows when to show.
  // ═══════════════════════════════════════════════════════════════════════
  async kanban(q: KanbanQueryDto, currentUserId: number) {
    const canViewAll = await this.userCanViewAll(currentUserId);
    const scope = (qb: any) => {
      if (!canViewAll) {
        qb.andWhere('(l.assigned_to = :uid OR l.created_by = :uid)', { uid: currentUserId });
      }
      if (q.assigned_to) {
        qb.andWhere('l.assigned_to = :assignedTo', { assignedTo: q.assigned_to });
      }
      if (q.client_group) {
        const groups = expandClientGroup(q.client_group);
        if (groups.length) qb.andWhere('l.client_group IN (:...groups)', { groups });
      }
      return qb;
    };

    const repo = this.dataSource.getRepository(Lead);
    const totalsRaw = await scope(
      repo.createQueryBuilder('l').select('l.status', 'status').addSelect('COUNT(*)', 'c').groupBy('l.status'),
    ).getRawMany();
    const totals: Record<string, number> = {};
    for (const row of totalsRaw) totals[row.status] = Number(row.c);

    const columns: Record<string, Lead[]> = {};
    for (const status of Object.values(LeadStatus)) {
      columns[status] = await scope(
        repo
          .createQueryBuilder('l')
          .leftJoinAndSelect('l.assignedUser', 'assignedUser')
          .where('l.status = :status', { status }),
      )
        .orderBy('l.created_at', 'DESC')
        .take(KANBAN_COLUMN_LIMIT)
        .getMany();
    }

    return { columns, totals, stats: await this.getAnalytics(currentUserId) };
  }

  async kanbanLoadMore(q: KanbanLoadMoreQueryDto, currentUserId: number) {
    const canViewAll = await this.userCanViewAll(currentUserId);
    const offset = Math.max(0, q.offset || 0);

    const base = () => {
      const qb = this.dataSource
        .getRepository(Lead)
        .createQueryBuilder('l')
        .where('l.status = :status', { status: q.status });
      if (!canViewAll) {
        qb.andWhere('(l.assigned_to = :uid OR l.created_by = :uid)', { uid: currentUserId });
      }
      if (q.assigned_to) qb.andWhere('l.assigned_to = :assignedTo', { assignedTo: q.assigned_to });
      if (q.client_group) {
        const groups = expandClientGroup(q.client_group);
        if (groups.length) qb.andWhere('l.client_group IN (:...groups)', { groups });
      }
      return qb;
    };

    const total = await base().getCount();
    const leads = await base()
      .leftJoinAndSelect('l.assignedUser', 'assignedUser')
      .orderBy('l.created_at', 'DESC')
      .skip(offset)
      .take(KANBAN_COLUMN_LIMIT)
      .getMany();

    return {
      cards: leads.map((l) => ({
        id: l.id,
        lead_code: l.lead_code,
        company: l.company,
        contact: l.contact,
        client_group: normalizeClientGroup(l.client_group),
        priority: l.priority,
        standards: l.standards ?? [],
        assignee: this.formatUserName(l.assignedUser),
        created_at: l.created_at,
      })),
      next_offset: offset + leads.length,
      remaining: Math.max(0, total - (offset + leads.length)),
    };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // STATS — dashboard KPI strip
  // ═══════════════════════════════════════════════════════════════════════
  async getAnalytics(currentUserId?: number) {
    const repo = this.dataSource.getRepository(Lead);
    let restrictToOwn = false;
    if (currentUserId) restrictToOwn = !(await this.userCanViewAll(currentUserId));

    const base = () => {
      const qb = repo.createQueryBuilder('l');
      if (restrictToOwn && currentUserId) {
        qb.andWhere('(l.assigned_to = :uid OR l.created_by = :uid)', { uid: currentUserId });
      }
      return qb;
    };

    const startOfMonth = new Date();
    startOfMonth.setDate(1);
    startOfMonth.setHours(0, 0, 0, 0);

    const [total, newThisMonth, converted, lost, groupRows] = await Promise.all([
      base().getCount(),
      base().andWhere('l.created_at >= :s', { s: startOfMonth }).getCount(),
      base().andWhere('l.status = :s', { s: LeadStatus.CONVERTED }).getCount(),
      base().andWhere('l.status = :s', { s: LeadStatus.LOST }).getCount(),
      base()
        .select('l.client_group', 'client_group')
        .addSelect('COUNT(*)', 'c')
        .groupBy('l.client_group')
        .getRawMany(),
    ]);

    // 🆕 Roll the raw group counts up in app code rather than SQL: QRS_B
    // and QRS come back as separate rows from MySQL and have to be merged
    // into one QRS bucket before the UI sees them.
    const byClientGroup: Record<string, number> = {
      [ClientGroup.QRS]: 0,
      [ClientGroup.TQS]: 0,
      [ClientGroup.QRS_NEW]: 0,
      unassigned: 0,
    };
    for (const row of groupRows) {
      const key = normalizeClientGroup(row.client_group) ?? 'unassigned';
      byClientGroup[key] = (byClientGroup[key] ?? 0) + Number(row.c);
    }

    return {
      total,
      new_month: newThisMonth,
      converted,
      lost,
      by_client_group: byClientGroup,
    };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // DISTINCT TAGS — for tag-filter suggestions (JSON stored as longtext,
  // so this is flattened/deduped in app code rather than SQL).
  // ═══════════════════════════════════════════════════════════════════════
  async distinctTags(): Promise<string[]> {
    const rows: { tags: string | null }[] = await this.dataSource.query(
      `SELECT tags FROM leads WHERE tags IS NOT NULL`,
    );
    const set = new Set<string>();
    for (const row of rows) {
      try {
        const parsed = JSON.parse(row.tags as any);
        if (Array.isArray(parsed)) parsed.forEach((t) => set.add(t));
      } catch {
        /* skip malformed rows */
      }
    }
    return [...set].sort();
  }

  // ═══════════════════════════════════════════════════════════════════════
  // FIND ONE
  // ═══════════════════════════════════════════════════════════════════════
  async findOne(id: number): Promise<Lead> {
    const lead = await this.dataSource
      .getRepository(Lead)
      .createQueryBuilder('l')
      .leftJoinAndSelect('l.assignedUser', 'assignedUser')
      .leftJoinAndSelect('l.assignedByUser', 'assignedByUser')
      .leftJoinAndSelect('l.creator', 'creator')
      .where('l.id = :id', { id })
      .getOne();

    if (!lead) throw new NotFoundException('Lead not found');
    return lead;
  }

  // ═══════════════════════════════════════════════════════════════════════
  // CHECK DUPLICATES — live check backing the create/edit form
  // ═══════════════════════════════════════════════════════════════════════
  async checkDuplicates(dto: CheckDuplicatesDto) {
    const [leadMatches, clientMatches] = await Promise.all([
      this.duplicateDetector.find(dto, dto.ignore_id ?? null),
      this.duplicateDetector.findInClients(dto),
    ]);

    return {
      matches: leadMatches.map((m) => ({
        id: m.lead.id,
        lead_code: m.lead.lead_code,
        company: m.lead.company,
        contact: m.lead.contact,
        email: m.lead.email,
        phone: m.lead.phone,
        status: m.lead.status,
        reasons: m.reasons,
        severity: m.severity,
        hard_signals: m.hardSignals,
        soft_signals: m.softSignals,
      })),
      client_matches: clientMatches, // empty until Client entity is wired in
    };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // CREATE — with duplicate detection + override
  // ═══════════════════════════════════════════════════════════════════════
  async create(dto: CreateLeadDto, currentUserId: number) {
    const override = !!dto.override_duplicate;

    const query = { company: dto.company, contact: dto.contact, phone: dto.phone, email: dto.email };
    const [leadMatches, clientMatches] = await Promise.all([
      this.duplicateDetector.find(query),
      this.duplicateDetector.findInClients(query),
    ]);
    const hardLeadHits = leadMatches.filter((m) => m.severity === 'high');
    const hardClientHits = clientMatches.filter((m) => m.severity === 'high');

    if ((hardLeadHits.length > 0 || hardClientHits.length > 0) && !override) {
      throw new BadRequestException({
        message:
          hardClientHits.length > 0
            ? 'This company already exists as a client. Open the client record, or resubmit with override_duplicate=true if this is genuinely different.'
            : 'Possible duplicate detected. Review and resubmit with override_duplicate=true if you still want to save.',
        duplicate_matches: hardLeadHits.map((m) => ({
          lead_code: m.lead.lead_code,
          company: m.lead.company,
          contact: m.lead.contact,
          reasons: m.reasons.map((r) => r.label).join(' · '),
        })),
        duplicate_client_matches: hardClientHits,
      });
    }

    const status = dto.status ?? LeadStatus.NEW;
    const now = new Date();

    const newId = await this.dataSource.transaction(async (manager) => {
      const leadCode = await this.nextCode(manager);

      const lead = manager.create(Lead, {
        lead_code: leadCode,
        company: dto.company,
        client_group: dto.client_group ?? null,
        contact: dto.contact ?? null,
        phone: dto.phone ?? null,
        email: dto.email ?? null,
        website: dto.website ?? null,
        standards: dto.standards ?? null,
        status,
        source: dto.source ?? null,
        priority: dto.priority ?? undefined,
        notes: dto.notes ?? null,
        tags: dto.tags ?? null,
        assigned_to: dto.assigned_to ?? currentUserId,
        assigned_by: currentUserId,
        assigned_at: now,
        created_by: currentUserId,
        lost_reason: status === LeadStatus.LOST ? dto.lost_reason ?? null : null,
        lost_notes: status === LeadStatus.LOST ? dto.lost_notes ?? null : null,
        lost_at: status === LeadStatus.LOST ? now : null,
        converted_at: null,
        created_at: now,
        updated_at: now,
      });

      const saved = await manager.save(lead);
      await this.logActivity(manager, saved.id, 'System', `Lead created as ${saved.status}`, currentUserId);
      return saved.id;
    });

    this.logger.log(`Lead created (id=${newId}, by=${currentUserId})`);

    // Fire-and-forget notification, same pattern as audit-requests: the
    // transaction has already committed, so a mail failure can't undo the
    // lead. LeadNotificationsService swallows its own errors.
    void this.leadNotifications.leadCreated(newId, currentUserId);

    return this.findOne(newId);
  }

  // ═══════════════════════════════════════════════════════════════════════
  // UPDATE
  // ═══════════════════════════════════════════════════════════════════════
  async update(id: number, dto: UpdateLeadDto, currentUserId: number): Promise<Lead> {
    const repo = this.dataSource.getRepository(Lead);
    const lead = await repo.findOne({ where: { id } });
    if (!lead) throw new NotFoundException('Lead not found');

    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && lead.assigned_to !== currentUserId && lead.created_by !== currentUserId) {
      throw new ForbiddenException('You do not have access to edit this lead');
    }

    // 'Converted' can only be reached via a dedicated convert flow, not here.
    if (dto.status === LeadStatus.CONVERTED && lead.status !== LeadStatus.CONVERTED) {
      throw new BadRequestException(
        'Cannot set status to Converted directly — use the convert-to-client flow.',
      );
    }

    const oldStatus = lead.status;
    const oldClientGroup = lead.client_group;
    const newStatus = dto.status ?? lead.status;
    const now = new Date();

    Object.assign(lead, dto);
    lead.updated_at = now;

    if (oldStatus !== newStatus) {
      if (newStatus === LeadStatus.LOST) {
        lead.lost_at = now;
        lead.lost_reason = dto.lost_reason ?? lead.lost_reason;
      } else if (oldStatus === LeadStatus.LOST) {
        lead.lost_at = null;
        lead.lost_reason = null;
        lead.lost_notes = null;
      }
      if (newStatus !== LeadStatus.LOST) {
        lead.lost_reason = null;
        lead.lost_notes = null;
      }
    }

    await this.dataSource.transaction(async (manager) => {
      await manager.save(lead);

      if (oldStatus !== newStatus) {
        let msg = `Status changed: ${oldStatus} → ${newStatus}`;
        if (newStatus === LeadStatus.LOST && lead.lost_reason) msg += ` (reason: ${lead.lost_reason})`;
        await this.logActivity(manager, id, 'System', msg, currentUserId);
      }

      if (dto.client_group !== undefined && dto.client_group !== oldClientGroup) {
        await this.logActivity(
          manager,
          id,
          'System',
          `Client group changed: ${oldClientGroup ?? '—'} → ${lead.client_group ?? '—'}`,
          currentUserId,
        );
      }
    });

    // Owner changes do not happen here — see assign() below.
    return this.findOne(id);
  }

  // ═══════════════════════════════════════════════════════════════════════
  // ASSIGN ★ — hand a lead to another person.
  //
  // The whole point of the endpoint is the notification, so the write is
  // wrapped in a pessimistic lock: two coordinators clicking "assign" on
  // the same lead within the same second would otherwise both read the
  // same previous owner and send two contradictory "it's yours" pings.
  // ═══════════════════════════════════════════════════════════════════════
  async assign(id: number, dto: AssignLeadDto, currentUserId: number) {
    const canAssign = await this.userCanAssign(currentUserId);
    if (!canAssign) {
      throw new ForbiddenException('You do not have permission to assign leads');
    }

    const targetExists = await this.dataSource
      .getRepository(User)
      .findOne({ where: { id: dto.assigned_to } as any });
    if (!targetExists) {
      throw new BadRequestException(`User #${dto.assigned_to} does not exist`);
    }

    const now = new Date();
    let previousAssignee: number | null = null;
    let unchanged = false;

    await this.dataSource.transaction(async (manager) => {
      const lead = await manager.findOne(Lead, {
        where: { id },
        lock: { mode: 'pessimistic_write' },
      });
      if (!lead) throw new NotFoundException('Lead not found');

      previousAssignee = lead.assigned_to;

      if (previousAssignee === dto.assigned_to) {
        unchanged = true;
        return;
      }

      lead.assigned_to = dto.assigned_to;
      lead.assigned_by = currentUserId;
      lead.assigned_at = now;
      lead.updated_at = now;
      await manager.save(lead);

      const fromLabel = previousAssignee ? `user #${previousAssignee}` : 'nobody';
      await this.logActivity(
        manager,
        id,
        'System',
        `Reassigned from ${fromLabel} to user #${dto.assigned_to}` +
          (dto.note ? ` — ${dto.note}` : ''),
        currentUserId,
      );
    });

    if (unchanged) {
      return { message: 'That person already owns this lead', changed: false };
    }

    // After commit. Real-time push + email both live in here.
    if (dto.notify !== false) {
      void this.leadNotifications.leadAssigned({
        leadId: id,
        newAssigneeId: dto.assigned_to,
        previousAssigneeId: previousAssignee,
        actorId: currentUserId,
        note: dto.note ?? null,
      });
    }

    this.logger.log(
      `Lead ${id} assigned: ${previousAssignee ?? 'none'} → ${dto.assigned_to} (by ${currentUserId})`,
    );

    return { message: 'Lead assigned', changed: true, lead: await this.findOne(id) };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // BULK UPDATE — status, reassign, or client group; one summary
  // notification per assignee rather than one per lead.
  // ═══════════════════════════════════════════════════════════════════════
  async bulkUpdate(dto: BulkUpdateLeadDto, currentUserId: number) {
    if (dto.action === 'reassign') {
      const canAssign = await this.userCanAssign(currentUserId);
      if (!canAssign) throw new ForbiddenException('You cannot reassign leads');
    }

    const repo = this.dataSource.getRepository(Lead);
    const leads = await repo.findByIds(dto.ids);
    const now = new Date();

    await this.dataSource.transaction(async (manager) => {
      for (const lead of leads) {
        const oldStatus = lead.status;

        if (dto.action === 'reassign') {
          lead.assigned_to = dto.assigned_to!;
          lead.assigned_by = currentUserId;
          lead.assigned_at = now;
          lead.updated_at = now;
          await manager.save(lead);
          await this.logActivity(
            manager,
            lead.id,
            'System',
            `Bulk reassigned to user #${dto.assigned_to}`,
            currentUserId,
          );
          continue;
        }

        if (dto.action === 'client_group') {
          const before = lead.client_group;
          lead.client_group = dto.client_group!;
          lead.updated_at = now;
          await manager.save(lead);
          if (before !== dto.client_group) {
            await this.logActivity(
              manager,
              lead.id,
              'System',
              `Bulk client group change: ${before ?? '—'} → ${dto.client_group}`,
              currentUserId,
            );
          }
          continue;
        }

        const newStatus = dto.status!;
        lead.status = newStatus;
        lead.updated_at = now;

        if (newStatus === LeadStatus.LOST) {
          lead.lost_at = now;
          lead.lost_reason = dto.lost_reason ?? null;
        } else if (oldStatus === LeadStatus.LOST) {
          lead.lost_at = null;
          lead.lost_reason = null;
          lead.lost_notes = null;
        }

        await manager.save(lead);

        if (oldStatus !== newStatus) {
          let msg = `Bulk status change: ${oldStatus} → ${newStatus}`;
          if (newStatus === LeadStatus.LOST && lead.lost_reason) msg += ` (reason: ${lead.lost_reason})`;
          await this.logActivity(manager, lead.id, 'System', msg, currentUserId);
        }
      }
    });

    if (dto.action === 'reassign' && leads.length > 0 && dto.assigned_to !== currentUserId) {
      void this.leadNotifications.leadsBulkAssigned({
        leadIds: leads.map((l) => l.id),
        newAssigneeId: dto.assigned_to!,
        actorId: currentUserId,
        note: dto.note ?? null,
      });
    }

    return { message: 'Leads updated', count: leads.length };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // BULK DESTROY (soft delete)
  // ═══════════════════════════════════════════════════════════════════════
  async bulkDestroy(dto: BulkDestroyLeadDto, currentUserId: number) {
    const repo = this.dataSource.getRepository(Lead);
    const leads = await repo.findByIds(dto.ids);

    await this.dataSource.transaction(async (manager) => {
      for (const lead of leads) {
        await this.logActivity(manager, lead.id, 'System', `Lead ${lead.lead_code} bulk-deleted`, currentUserId);
      }
      await manager.softDelete(Lead, dto.ids);
    });

    return { message: 'Leads deleted', count: leads.length };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // REMOVE (single, soft delete)
  // ═══════════════════════════════════════════════════════════════════════
  async remove(id: number, currentUserId: number) {
    const repo = this.dataSource.getRepository(Lead);
    const lead = await repo.findOne({ where: { id } });
    if (!lead) throw new NotFoundException('Lead not found');

    await this.dataSource.transaction(async (manager) => {
      await this.logActivity(manager, id, 'System', `Lead ${lead.lead_code} deleted`, currentUserId);
      await manager.softDelete(Lead, id);
    });

    return { message: 'Lead deleted', id };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // REQUEST HANDOVER — notify the current owner + assigners, no formal
  // transfer record (mirrors the Laravel version's lightweight approach).
  // ═══════════════════════════════════════════════════════════════════════
  async requestHandover(leadId: number, dto: RequestHandoverDto, requesterId: number, requesterName: string) {
    const repo = this.dataSource.getRepository(Lead);
    const lead = await repo.findOne({ where: { id: leadId }, withDeleted: false });
    if (!lead) throw new NotFoundException('Lead not found');

    if (lead.assigned_to === requesterId) {
      return { message: 'You already own this lead', skipped: true };
    }

    const note = dto.note ? ` — ${dto.note}` : '';
    await this.logActivity(
      this.dataSource.manager,
      leadId,
      'System',
      `${requesterName} requested to handle this lead (reason: ${dto.reason})${note}`,
      requesterId,
    );

    void this.leadNotifications.handoverRequested({
      lead,
      requesterId,
      requesterName,
      reason: dto.reason,
      note: dto.note ?? null,
    });

    return { message: 'Handover requested' };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // CSV IMPORT
  // ═══════════════════════════════════════════════════════════════════════
  private readonly IMPORT_COLUMNS = [
    'company',
    'client_group',
    'contact',
    'phone',
    'email',
    'website',
    'standards',
    'status',
    'priority',
    'source',
    'notes',
    'tags',
    'assigned_to_email',
  ];

  getImportTemplateCsv(): string {
    const header = this.IMPORT_COLUMNS.join(',');
    const example = [
      'Acme Industries LLC',
      'QRS',
      'Jane Doe',
      '+971 50 123 4567',
      'jane@acme.example',
      'https://www.acme.example',
      'ISO 9001|ISO 14001',
      'New',
      'Medium',
      'Website',
      '"Reached out via contact form, wants Stage 1 + Stage 2 quote"',
      'priority|hot',
      'marketing@example.com',
    ].join(',');
    return `\uFEFF${header}\n${example}\n`;
  }

  async importLeads(
    csvContent: string,
    options: { skipDuplicates: boolean; defaultAssignedTo: number },
    currentUserId: number,
  ) {
    const content = csvContent.replace(/^\uFEFF/, '').trim();
    const lines = content.split(/\r\n|\n|\r/);
    if (lines.length < 2) {
      throw new BadRequestException('The file is empty or has no data rows.');
    }

    const headerRow = this.parseCsvLine(lines.shift()!).map((h) => h.trim().toLowerCase());
    const colIndex: Record<string, number> = {};
    headerRow.forEach((h, i) => {
      if (this.IMPORT_COLUMNS.includes(h)) colIndex[h] = i;
    });
    if (colIndex['company'] === undefined) {
      throw new BadRequestException(
        'CSV must include a "company" column. Download the template to see all accepted columns.',
      );
    }

    const users = await this.dataSource.getRepository(User).find();
    const userByEmail = new Map<string, number>();
    for (const u of users as any[]) {
      if (u.email) userByEmail.set(u.email.toLowerCase(), u.id);
    }

    let created = 0;
    let skipped = 0;
    const errors: string[] = [];

    await this.dataSource.transaction(async (manager) => {
      for (let idx = 0; idx < lines.length; idx++) {
        const line = lines[idx];
        if (!line || line.trim() === '') continue;
        const rowNum = idx + 2;
        const cells = this.parseCsvLine(line);
        const get = (key: string) =>
          colIndex[key] !== undefined ? (cells[colIndex[key]] ?? '').trim() : '';

        const company = get('company');
        if (!company) {
          errors.push(`Row ${rowNum}: company is required.`);
          continue;
        }

        const email = get('email') || null;
        if (email && !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(email)) {
          errors.push(`Row ${rowNum}: invalid email '${email}'.`);
          continue;
        }

        const status = (get('status') || 'New') as LeadStatus;
        if (![LeadStatus.NEW, LeadStatus.INTERESTED, LeadStatus.LOST].includes(status)) {
          errors.push(`Row ${rowNum}: status must be New, Interested, or Lost (got '${status}').`);
          continue;
        }

        const priority = get('priority') || 'Medium';
        if (!['Low', 'Medium', 'High'].includes(priority)) {
          errors.push(`Row ${rowNum}: priority must be Low, Medium, or High (got '${priority}').`);
          continue;
        }

        // 🆕 Client group. QRS_B in a CSV is accepted and stored as QRS —
        // spreadsheets exported from the old system still carry it, and
        // rejecting the row would be a worse outcome than folding it.
        const rawGroup = get('client_group');
        let clientGroup: ClientGroup | null = null;
        if (rawGroup) {
          clientGroup = normalizeClientGroup(rawGroup);
          if (!clientGroup) {
            errors.push(
              `Row ${rowNum}: client_group must be one of ${SELECTABLE_CLIENT_GROUPS.join(', ')} (got '${rawGroup}').`,
            );
            continue;
          }
        }

        const standards = get('standards').split('|').map((s) => s.trim()).filter(Boolean);
        const tags = get('tags').split('|').map((s) => s.trim()).filter(Boolean);

        const assigneeEmail = get('assigned_to_email').toLowerCase();
        const assignedTo = (assigneeEmail && userByEmail.get(assigneeEmail)) || options.defaultAssignedTo;

        const query = { company, contact: get('contact') || null, phone: get('phone') || null, email };

        if (options.skipDuplicates) {
          const hardLead = (await this.duplicateDetector.find(query)).filter((m) => m.severity === 'high');
          const hardClient = (await this.duplicateDetector.findInClients(query)).filter(
            (m) => m.severity === 'high',
          );
          if (hardLead.length > 0 || hardClient.length > 0) {
            skipped++;
            continue;
          }
        }

        const now = new Date();
        const leadCode = await this.nextCode(manager);
        const lead = manager.create(Lead, {
          lead_code: leadCode,
          company,
          client_group: clientGroup,
          contact: get('contact') || null,
          phone: get('phone') || null,
          email,
          website: get('website') || null,
          standards: standards.length ? standards : null,
          status,
          source: get('source') || null,
          priority: priority as any,
          notes: get('notes') || null,
          tags: tags.length ? tags : null,
          assigned_to: assignedTo,
          assigned_by: currentUserId,
          assigned_at: now,
          created_by: currentUserId,
          created_at: now,
          updated_at: now,
        });
        const saved = await manager.save(lead);
        await this.logActivity(manager, saved.id, 'System', 'Lead created via CSV import', currentUserId);
        created++;
      }
    });

    return { created, skipped, errors };
  }

  /** Your User entity uses firstName/lastName rather than a single `name`. */
  private formatUserName(user: User | null | undefined): string | null {
    if (!user) return null;
    const first = (user as any).firstName ?? '';
    const last = (user as any).lastName ?? '';
    const full = `${first} ${last}`.trim();
    return full || (user as any).email || null;
  }

  /** Minimal CSV line parser — handles quoted fields with embedded commas. */
  private parseCsvLine(line: string): string[] {
    const result: string[] = [];
    let cur = '';
    let inQuotes = false;
    for (let i = 0; i < line.length; i++) {
      const ch = line[i];
      if (inQuotes) {
        if (ch === '"' && line[i + 1] === '"') {
          cur += '"';
          i++;
        } else if (ch === '"') {
          inQuotes = false;
        } else {
          cur += ch;
        }
      } else if (ch === '"') {
        inQuotes = true;
      } else if (ch === ',') {
        result.push(cur);
        cur = '';
      } else {
        cur += ch;
      }
    }
    result.push(cur);
    return result;
  }

  // ═══════════════════════════════════════════════════════════════════════
  // NOTIFICATIONS
  // All of it now lives in LeadNotificationsService — the stubs that used
  // to sit here are gone. Adjust method names to your real
  // NotificationsService / MailsService in that file's ADAPTER section.
  // ═══════════════════════════════════════════════════════════════════════
}