// src/inquiries/inquiries.service.ts
import {
  Injectable,
  NotFoundException,
  BadRequestException,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource, In } from 'typeorm';
import {
  Inquiry,
  InquiryStatus,
  InquiryDocument,
  InquiryDocumentType,
} from './entities/inquiry.entity';
import { Company } from '../companies/entities/company.entity';
import { Standard } from '../standards/entities/standard.entity';
import { User } from './../user/entities/user.entity';
import { CreateInquiryDto } from './dto/create-inquiry.dto';
import { UpdateInquiryDto } from './dto/update-inquiry.dto';
import { DraftGeneratorService } from './draft-generator.service';
import { InquiryNotificationsService } from '../inquiry-notifications/inquiry-notifications.service';
import * as fs from 'fs';
import * as path from 'path';

// ✅ NEW — Super-admin user IDs that always get notifications (in addition to target user)
const SUPER_ADMIN_NOTIFY_IDS = [1, 8];

const EXTRA_ADMIN_EMAILS = [
  'coordinator@iicc.ae',
  // 'manzoorqrs@gmail.com',
  'developer1@altayaboon.com',

];

// ✅ NEW — Submitter row type for filter dropdown
type SubmitterRow = {
  id: number;
  firstName: string;
  lastName: string;
  email: string;
};

@Injectable()
export class InquiriesService {
  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
    private readonly draftGenerator: DraftGeneratorService,
    private readonly inquiryNotificationsService: InquiryNotificationsService,
  ) { }

  // ═════════════════════════════════════════════════════════════
  // ✅ Check if user can see all inquiries (admin or has view-all)
  // ═════════════════════════════════════════════════════════════
  private async userCanViewAll(userId: number): Promise<boolean> {
    if (SUPER_ADMIN_NOTIFY_IDS.includes(userId)) {
      console.log(`[INQUIRIES-PERM] User ${userId} → SUPER ADMIN bypass`);
      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 = 'view-all'
        AND m.slug = 'inquiries'
      LIMIT 1
      `,
      [userId],
    );

    if (rolePerms.length > 0) {
      console.log(`[INQUIRIES-PERM] User ${userId} → has VIEW-ALL via role`);
      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 = 'view-all'
          AND m.slug = 'inquiries'
        LIMIT 1
        `,
        [userId],
      );
      if (directPerms.length > 0) {
        console.log(
          `[INQUIRIES-PERM] User ${userId} → has VIEW-ALL via direct grant`,
        );
        return true;
      }
    } catch {
      /* user_permissions table may not exist — that's fine */
    }

    console.log(
      `[INQUIRIES-PERM] User ${userId} → NO view-all, restricted to own`,
    );
    return false;
  }


  // ── Generate INQ-YYYY-MM-NNNN reference ──────────────────────
  private async generateInquiryRef(): Promise<string> {
    const now = new Date();
    const year = now.getFullYear();
    const month = String(now.getMonth() + 1).padStart(2, '0');
    const prefix = `INQ-${year}-${month}-`;

    const result = await this.dataSource
      .getRepository(Inquiry)
      .createQueryBuilder('inq')
      .select('MAX(inq.inquiry_ref)', 'maxRef')
      .where('inq.inquiry_ref LIKE :prefix', { prefix: `${prefix}%` })
      .getRawOne();

    let nextSeq = 1;
    if (result?.maxRef) {
      const lastSeq = parseInt(result.maxRef.split('-').pop() ?? '0', 10);
      nextSeq = lastSeq + 1;
    }

    return `${prefix}${String(nextSeq).padStart(4, '0')}`;
  }

  // ── Notification rules — who gets notified for each status ───
  private getNotificationConfig(
    status: string,
    inquiry: Inquiry,
  ): {
    targetUser: User | null;
    type: string;
    message: string;
    pdf_url?: string;
    docx_url?: string;
  } | null {
    const ref = inquiry.inquiry_ref;
    const company = inquiry.company?.name ?? '';

    const cacheBust = (inquiry as any).draft_generated_at
      ? new Date((inquiry as any).draft_generated_at).getTime()
      : Date.now();

    switch (status) {
      case 'IN_REVIEW':
        return {
          targetUser: inquiry.submitted_by ?? null,
          type: 'IN_REVIEW',
          message: `🔍 Your inquiry ${ref} (${company}) has been picked up by Scheme and is now In Review.`,
        };

      case 'DRAFT_READY':
        return {
          targetUser: inquiry.submitted_by ?? null,
          type: 'DRAFT_READY',
          message: `📄 Draft certificate is ready for ${ref} (${company}). Please review and confirm or request changes.`,
          pdf_url: inquiry.draft_pdf_path
            ? `/api/inquiries/${inquiry.id}/download-draft/pdf?v=${cacheBust}`
            : undefined,
          docx_url: inquiry.draft_docx_path
            ? `/api/inquiries/${inquiry.id}/download-draft/docx?v=${cacheBust}`
            : undefined,
        };

      case 'CHANGES_REQUESTED':
        return {
          targetUser: inquiry.assigned_to ?? null,
          type: 'CHANGES_REQUESTED',
          message: `↩ Marketing requested changes on ${ref} (${company}). Please fix and regenerate the draft.`,
        };

      case 'CLIENT_CONFIRMED':
        return {
          targetUser: inquiry.assigned_to ?? null,
          type: 'CLIENT_CONFIRMED',
          message: `✅ Client confirmed the draft for ${ref} (${company}). Please issue the final certificate now.`,
        };

      case 'FINAL_ISSUED':
        return {
          targetUser: inquiry.submitted_by ?? null,
          type: 'FINAL_ISSUED',
          message: `🏆 Final certificate has been issued for ${ref} (${company}). Process complete!`,
        };

      default:
        return null;
    }
  }

  // ── Marketing submits new inquiry ────────────────────────────
  async create(dto: CreateInquiryDto) {
    const { saved, company, schemeUsers, submitterName } =
      await this.dataSource.transaction(async (manager) => {
        // ── Resolve company: by id → by EXACT name → create minimal ──
        let company = dto.company_id
          ? await manager.findOne(Company, {
            where: { id: dto.company_id },
            relations: ['standards', 'country'],
          })
          : null;

        const companyName = ((dto as any).company_name || '').trim();

        if (!company && companyName) {
          company = await manager
            .createQueryBuilder(Company, 'c')
            .leftJoinAndSelect('c.standards', 'standards')
            .where('LOWER(TRIM(c.name)) = LOWER(:name)', { name: companyName.toLowerCase() })
            .getOne();
        }

        if (!company && companyName) {
          company = await manager.save(
            manager.create(Company, {
              name: companyName,
              company_code: `INQ-${Date.now()}-${Math.floor(Math.random() * 1000)}`,
              address: 'N/A',
              city: 'N/A',
              contact_person: 'N/A',
              designation: 'N/A',
              email: 'unknown@example.com',
              mobile: '0000000000',
              telephone: '0000000000',
              fax: '0000000000',
              validity: 'N/A',
              certification_body: dto.cert_body || 'QRS',
              client_group: dto.cert_body || 'QRS',
              accreditation: 'N/A',
              scope_of_work: (dto as any).scope_of_work?.trim() || 'N/A',   // 🆕
              country: { id: 3 } as any,
            }),
          );
        }

        if (!company) throw new BadRequestException('Company not found');
        console.log(`🏢 [TRACE] company_id=${dto.company_id} company_name="${(dto as any).company_name}" → resolved: id=${company.id} name="${company.name}"`);
        const inquiry_ref = await this.generateInquiryRef();
        // 🆕 SCOPE OF WORK SYNC — write the submitted scope onto the
        // companies row. Runs for BOTH existing and newly created companies.
        // Only overwrites when a real value was sent — never wipes an
        // existing scope with a blank or a placeholder.
        const incomingScope = ((dto as any).scope_of_work || '').trim();
        if (
          incomingScope &&
          incomingScope !== company.scope_of_work &&
          !['N/A', 'Unknown'].includes(incomingScope)
        ) {
          company.scope_of_work = incomingScope;
          company = await manager.save(company);
          console.log(
            `📝 [SCOPE-SYNC] company #${company.id} scope_of_work updated from inquiry submit`,
          );
        }
        const inquiry = manager.create(Inquiry, {
          inquiry_ref,
          inquiry_type: dto.inquiry_type,
          audit_date: dto.audit_date,
          auditor_name: dto.auditor_name,
          cert_body: dto.cert_body, // ✅ NEW
          audit_stage: dto.audit_stage, // ✅ NEW
          previous_cert_no: dto.previous_cert_no,
          scope_of_work: (dto as any).scope_of_work ?? null,   // 🆕 snapshot on the inquiry
          notes: dto.notes,
          status: InquiryStatus.PENDING,
          company,
          surveillance_audit_due: dto.surveillance_audit_due ?? null,
          recertification_due: dto.recertification_due ?? null,
        });

        if (dto.submitted_by_id) {
          const user = await manager.findOne(User, {
            where: { id: dto.submitted_by_id },
          });
          if (!user)
            throw new BadRequestException('Submitted by user not found');
          inquiry.submitted_by = user;
        }

        if (dto.assigned_to_id) {
          const schemeUser = await manager.findOne(User, {
            where: { id: dto.assigned_to_id },
          });
          if (schemeUser) inquiry.assigned_to = schemeUser;
        }

        inquiry.standards = dto.standards?.length
          ? await manager.find(Standard, { where: { id: In(dto.standards) } })
          : (company.standards ?? []);

        const saved = await manager.save(inquiry);

        const submitterName = inquiry.submitted_by
          ? `${inquiry.submitted_by.firstName} ${inquiry.submitted_by.lastName}`.trim()
          : 'Marketing';

        const schemeUsers = (await this.dataSource.query(`
          SELECT DISTINCT u.id, u.email, u.firstName, u.lastName
          FROM users u
          LEFT JOIN user_roles ur ON ur.user_id = u.id
          LEFT JOIN roles r ON r.id = ur.role_id
          WHERE LOWER(r.name) = 'scheme'
             OR u.id IN (1, 8)
        `)) as {
          id: number;
          email: string;
          firstName: string;
          lastName: string;
        }[];

        console.log(
          '🔔 Recipients found (scheme + super admins):',
          schemeUsers,
        );
        console.log('🔔 Recipients count:', schemeUsers.length);

        return { saved, company, schemeUsers, submitterName };
      });

    // ─── ✅ CHANGED — Notification fan-out ─────────────────────────────
    //
    // BEFORE (the bug since ~12 May): only the submitter got emit() called,
    // so only the submitter got a row in inquiry_notifications + a realtime
    // toast. Developer 1 / super-admins / other scheme users saw NOTHING on
    // the notifications page or as a toast. They only got the email via the
    // SMTP CC list.
    //
    // AFTER (the fix): emit() is called for EVERY recipient — submitter +
    // all scheme/super-admin users. BUT only the submitter's call carries a
    // `targetEmail`, so the SMTP email is sent ONCE (with the SCHEME_CC_EMAIL
    // CC list, added automatically inside emit() for NEW_INQUIRY). The other
    // calls have targetEmail=undefined, which makes the service skip the
    // email step — those users only get a DB row + socket broadcast.
    //
    // Net effect:
    //   - Same single email + CC list as today (no duplicate emails)
    //   - Developer 1 / scheme users / super-admins now SEE the notification
    //     on the bell, page, and as a realtime toast — like before 12 May.
    // ───────────────────────────────────────────────────────────────────

    console.log('═══════════════════════════════════════════════════════');
    console.log('📧 [CREATE INQUIRY] Notification fan-out');
    console.log('   🔖 Inquiry Ref:', saved.inquiry_ref);
    console.log('   👤 Submitter ID:', saved.submitted_by?.id);
    console.log('   👤 Submitter Name:', submitterName);
    console.log('   📨 Submitter Email:', saved.submitted_by?.email);
    console.log(
      '   🎯 Scheme + super-admin recipients found:',
      schemeUsers.length,
    );
    console.log('═══════════════════════════════════════════════════════');

    // Build a deduplicated recipient list — submitter FIRST (so they get
    // the email), then every scheme + super-admin user. A Map keyed by
    // user id removes duplicates (the submitter may also be a scheme user).
    const recipientMap = new Map<
      number,
      { id: number; email: string; firstName?: string; lastName?: string }
    >();

    if (saved.submitted_by?.id) {
      recipientMap.set(saved.submitted_by.id, {
        id: saved.submitted_by.id,
        email: saved.submitted_by.email,
        firstName: saved.submitted_by.firstName,
        lastName: saved.submitted_by.lastName,
      });
    }
    for (const u of schemeUsers) {
      if (!recipientMap.has(u.id)) recipientMap.set(u.id, u);
    }

    const recipients = Array.from(recipientMap.values());
    const submitterId = saved.submitted_by?.id;

    console.log(
      `📤 [CREATE INQUIRY] Fanning out to ${recipients.length} recipients`,
    );
    const ccEmails: string[] = EXTRA_ADMIN_EMAILS.filter((e) => e && e.trim());

    for (const r of recipients) {
      const isSubmitter = r.id === submitterId;
      console.log(
        `   → user ${r.id} (${r.email}) ${isSubmitter ? '+ EMAIL with CC' : '(in-app only)'
        }`,
      );

      await this.inquiryNotificationsService.emit({
        targetUserId: r.id,
        // Only the SUBMITTER carries an email — emit() skips SMTP when
        // targetEmail is undefined. The CC list (SCHEME_CC_EMAIL) is
        // added inside sendEmail() for NEW_INQUIRY type, so the single
        // email still reaches everyone in CC.
        targetEmail: isSubmitter ? r.email : undefined,
        type: 'NEW_INQUIRY',
        inquiry_id: saved.id,
        inquiry_ref: saved.inquiry_ref,
        company_name: company.name,
        old_status: '',
        new_status: 'PENDING',
        timestamp: new Date().toISOString(),
        message: `📋 New inquiry ${saved.inquiry_ref} submitted by ${submitterName} for ${company.name}. Please review.`,
        inquiry_type: saved.inquiry_type,
        auditor_name: saved.auditor_name,
        previous_cert_no: saved.previous_cert_no,
        cert_body: saved.cert_body,
        audit_stage: saved.audit_stage,
        submitted_by_name: submitterName,
        submitted_by_email: saved.submitted_by?.email,
        notes: saved.notes, // ✅ NEW
        actor_user_id: dto.submitted_by_id, // 🆕   ← THIS IS THE ONLY NEW LINE

      });
    }

    console.log(
      `✅ [CREATE INQUIRY] Done. Fanned out to ${recipients.length} users.`,
    );
    // 🆕 also email the always-notify admin address(es) for new inquiries
    for (const adminEmail of EXTRA_ADMIN_EMAILS) {
      const clean = adminEmail.trim().toLowerCase();
      if (!clean) continue;

      console.log(`📤 [CREATE INQUIRY] Extra admin email → ${clean}`);

      await this.inquiryNotificationsService.emit({
        targetUserId: 0,            // no in-app user — email only
        targetEmail: clean,         // carries an email → SMTP fires
        type: 'NEW_INQUIRY',
        inquiry_id: saved.id,
        inquiry_ref: saved.inquiry_ref,
        company_name: company.name,
        old_status: '',
        new_status: 'PENDING',
        timestamp: new Date().toISOString(),
        message: `📋 New inquiry ${saved.inquiry_ref} submitted by ${submitterName} for ${company.name}. Please review.`,
        inquiry_type: saved.inquiry_type,
        auditor_name: saved.auditor_name,
        previous_cert_no: saved.previous_cert_no,
        cert_body: saved.cert_body,
        audit_stage: saved.audit_stage,
        submitted_by_name: submitterName,
        submitted_by_email: saved.submitted_by?.email,
        notes: saved.notes, // ✅ NEW
        actor_user_id: dto.submitted_by_id, // 🆕

      });
    }
    console.log('═══════════════════════════════════════════════════════');

    return saved;
  }

  // ── Scheme updates inquiry fields / changes status ───────────
  async update(id: number, dto: UpdateInquiryDto) {
    const { saved, inquiry, old_status } = await this.dataSource.transaction(
      async (manager) => {
        const inquiry = await manager.findOne(Inquiry, {
          where: { id },
          relations: ['company', 'standards', 'submitted_by', 'assigned_to'],
        });
        if (!inquiry) throw new NotFoundException('Inquiry not found');

        const old_status = inquiry.status;
        Object.assign(inquiry, dto);

        if (dto.standards?.length) {
          inquiry.standards = await manager.find(Standard, {
            where: { id: In(dto.standards.map(Number)) },
          });
        }

        const saved = await manager.save(inquiry);
        return { saved, inquiry, old_status };
      },
    );

    if (dto.status && dto.status !== old_status) {
      const config = this.getNotificationConfig(dto.status, inquiry);

      if (config) {
        const recipientIds = new Set<number>();
        if (config.targetUser) recipientIds.add(config.targetUser.id);
        SUPER_ADMIN_NOTIFY_IDS.forEach((sid) => recipientIds.add(sid));

        const idsArr = Array.from(recipientIds);
        const placeholders = idsArr.map(() => '?').join(',');
        const recipients = (await this.dataSource.query(
          `SELECT id, email FROM users WHERE id IN (${placeholders})`,
          idsArr,
        )) as { id: number; email: string }[];

        // ✅ NEW — Suppress EMAIL (keep in-app notification + socket) when an
        // inquiry jumps straight from PENDING to FINAL_ISSUED.
        const suppressEmail =
          String(old_status) === 'PENDING' &&
          String(dto.status) === 'FINAL_ISSUED';
        if (suppressEmail) {
          console.log(
            `🔕 [UPDATE INQUIRY] PENDING → FINAL_ISSUED for ${inquiry.inquiry_ref} — email suppressed (in-app only)`,
          );
        }

        // ✅ DEBUG LOGS — Track who gets emailed when status changes
        console.log('═══════════════════════════════════════════════════════');
        console.log('📧 [UPDATE INQUIRY] Status change emails');
        console.log('   🔖 Inquiry Ref:', inquiry.inquiry_ref);
        console.log('   🔄 Status:', old_status, '→', dto.status);
        console.log('   🏷️  Type:', config.type);
        console.log('   📨 Total recipients:', recipients.length);
        console.log(
          '   📨 Recipients list:',
          recipients.map((r) => ({ id: r.id, email: r.email })),
        );
        console.log('═══════════════════════════════════════════════════════');

        for (const r of recipients) {
          console.log(
            `📤 [UPDATE INQUIRY] Sending email to → ID: ${r.id}, Email: ${r.email}`,
          );

          await this.inquiryNotificationsService.emit({
            targetUserId: r.id,
            targetEmail: suppressEmail ? undefined : r.email,
            type: config.type,
            inquiry_id: inquiry.id,
            inquiry_ref: inquiry.inquiry_ref,
            company_name: inquiry.company?.name ?? '',
            old_status,
            new_status: dto.status,
            message: config.message,
            timestamp: new Date().toISOString(),
            pdf_url: config.pdf_url,
            docx_url: config.docx_url,
            inquiry_type: inquiry.inquiry_type,
            auditor_name: inquiry.auditor_name,
            previous_cert_no: inquiry.previous_cert_no,
            cert_body: inquiry.cert_body, // ✅ NEW
            audit_stage: inquiry.audit_stage, // ✅ NEW
            submitted_by_name: inquiry.submitted_by // ✅ NEW
              ? `${inquiry.submitted_by.firstName} ${inquiry.submitted_by.lastName}`.trim()
              : undefined,
            submitted_by_email: inquiry.submitted_by?.email,
            actor_user_id: inquiry.assigned_to?.id ?? inquiry.submitted_by?.id, // 🆕

          });
        }

        console.log(
          `✅ [UPDATE INQUIRY] Done. Total emails sent: ${recipients.length}`,
        );
        console.log('═══════════════════════════════════════════════════════');
      }
    }

    return saved;
  }

  // ═════════════════════════════════════════════════════════════
  // ✅ Paginated list with row-level filtering
  // ═════════════════════════════════════════════════════════════
  async findAll(options?: {
    page?: number;
    limit?: number;
    status?: string;
    search?: string;
    currentUserId?: number;
    submitted_by_id?: string; // ✅ NEW
  }) {
    const page = options?.page || 1;
    const limit = options?.limit || 50;
    const skip = (page - 1) * limit;

    const query = this.dataSource
      .getRepository(Inquiry)
      .createQueryBuilder('inquiry')
      .leftJoinAndSelect('inquiry.company', 'company')
      .leftJoinAndSelect('inquiry.standards', 'standards')
      .leftJoinAndSelect('inquiry.submitted_by', 'submitted_by')
      .leftJoinAndSelect('inquiry.assigned_to', 'assigned_to')
      .orderBy('inquiry.id', 'DESC')
      .skip(skip)
      .take(limit);

    // Row-level access filter
    if (options?.currentUserId) {
      const canViewAll = await this.userCanViewAll(options.currentUserId);
      if (!canViewAll) {
        query.andWhere('inquiry.submitted_by_id = :uid', {
          uid: options.currentUserId,
        });
      }
    }

    if (options?.status) {
      query.andWhere('inquiry.status = :status', { status: options.status });
    }
    if (options?.search) {
      query.andWhere(
        'LOWER(company.name) LIKE :s OR inquiry.inquiry_ref LIKE :s',
        { s: `%${options.search.toLowerCase()}%` },
      );
    }

    // ✅ NEW — filter by selected submitter (super admin / scheme only)
    if (options?.submitted_by_id) {
      query.andWhere('inquiry.submitted_by_id = :sbid', {
        sbid: Number(options.submitted_by_id),
      });
    }

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

  // ── Find one ─────────────────────────────────────────────────
  findOne(id: number) {
    return this.dataSource
      .getRepository(Inquiry)
      .createQueryBuilder('inquiry')
      .leftJoinAndSelect('inquiry.company', 'company')
      .leftJoinAndSelect('inquiry.standards', 'standards')
      .leftJoinAndSelect('inquiry.submitted_by', 'submitted_by')
      .leftJoinAndSelect('inquiry.assigned_to', 'assigned_to')
      .where('inquiry.id = :id', { id })
      .getOne();
  }

  remove(id: number) {
    return this.dataSource.getRepository(Inquiry).delete(id);
  }

  // ── Generate draft + notify Marketing ────────────────────────
  async generateDraft(id: number) {
    const inquiry = await this.dataSource
      .getRepository(Inquiry)
      .createQueryBuilder('inquiry')
      .leftJoinAndSelect('inquiry.company', 'company')
      .leftJoinAndSelect('inquiry.standards', 'standards')
      .leftJoinAndSelect('inquiry.submitted_by', 'submitted_by')
      .leftJoinAndSelect('inquiry.assigned_to', 'assigned_to')
      .where('inquiry.id = :id', { id })
      .getOne();

    if (!inquiry) throw new NotFoundException('Inquiry not found');

    if (!inquiry.standards?.length) {
      throw new BadRequestException('No standards selected on this inquiry');
    }

    if (!inquiry.certificate_number) {
      throw new BadRequestException(
        'Certificate number is required before generating draft',
      );
    }

    const [pdfPath, docxPath] = await Promise.all([
      this.draftGenerator.generateDraftPdf(inquiry),
      this.draftGenerator.generateDraftDocx(inquiry),
    ]);

    const draftGeneratedAt = new Date();

    await this.dataSource.getRepository(Inquiry).update(id, {
      draft_pdf_path: pdfPath,
      draft_docx_path: docxPath,
      status: InquiryStatus.DRAFT_READY,
      // @ts-ignore — new column added to Inquiry entity
      draft_generated_at: draftGeneratedAt,
    });

    const draftRecipientIds = new Set<number>();
    if (inquiry.submitted_by) draftRecipientIds.add(inquiry.submitted_by.id);
    SUPER_ADMIN_NOTIFY_IDS.forEach((sid) => draftRecipientIds.add(sid));

    const draftIdsArr = Array.from(draftRecipientIds);
    const draftPlaceholders = draftIdsArr.map(() => '?').join(',');
    const draftRecipients = (await this.dataSource.query(
      `SELECT id, email FROM users WHERE id IN (${draftPlaceholders})`,
      draftIdsArr,
    )) as { id: number; email: string }[];

    // ✅ DEBUG LOGS — Track who gets emailed when draft is ready
    console.log('═══════════════════════════════════════════════════════');
    console.log('📧 [GENERATE DRAFT] Draft ready emails');
    console.log('   🔖 Inquiry Ref:', inquiry.inquiry_ref);
    console.log('   📨 Total recipients:', draftRecipients.length);
    console.log(
      '   📨 Recipients list:',
      draftRecipients.map((r) => ({ id: r.id, email: r.email })),
    );
    console.log('═══════════════════════════════════════════════════════');

    for (const r of draftRecipients) {
      console.log(
        `📤 [GENERATE DRAFT] Sending email to → ID: ${r.id}, Email: ${r.email}`,
      );

      await this.inquiryNotificationsService.emit({
        targetUserId: r.id,
        targetEmail: r.email,
        type: 'DRAFT_READY',
        inquiry_id: inquiry.id,
        inquiry_ref: inquiry.inquiry_ref,
        company_name: inquiry.company?.name ?? '',
        old_status: inquiry.status,
        new_status: 'DRAFT_READY',
        timestamp: new Date().toISOString(),
        message: `📄 Draft certificate is ready for ${inquiry.inquiry_ref} (${inquiry.company?.name}). Please review and confirm or request changes.`,
        pdf_url: `/api/inquiries/${id}/download-draft/pdf?v=${draftGeneratedAt.getTime()}`,
        docx_url: `/api/inquiries/${id}/download-draft/docx?v=${draftGeneratedAt.getTime()}`,
        inquiry_type: inquiry.inquiry_type,
        auditor_name: inquiry.auditor_name,
        previous_cert_no: inquiry.previous_cert_no,
      });
    }

    console.log(
      `✅ [GENERATE DRAFT] Done. Total emails sent: ${draftRecipients.length}`,
    );
    console.log('═══════════════════════════════════════════════════════');

    return {
      message: 'Draft generated successfully',
      pdf_path: pdfPath,
      docx_path: docxPath,
      status: InquiryStatus.DRAFT_READY,
      draft_generated_at: draftGeneratedAt,
    };
  }

  // ── Get file buffer for download ──────────────────────────────
  getDraftFile(filePath: string): { buffer: Buffer; fileName: string } {
    const fullPath = path.join(process.cwd(), filePath);

    if (!fs.existsSync(fullPath)) {
      throw new NotFoundException('Draft file not found. Please regenerate.');
    }

    const buffer = fs.readFileSync(fullPath);
    const fileName = path.basename(fullPath);

    return { buffer, fileName };
  }

  // ═════════════════════════════════════════════════════════════
  // ✅ Save uploaded files into inquiry.documents JSON
  // ═════════════════════════════════════════════════════════════
  async uploadDocuments(
    inquiryId: number,
    files: Express.Multer.File[],
    types: string[],
    uploadedBy?: number,
  ) {
    if (!files?.length) {
      throw new BadRequestException('No files uploaded');
    }

    const inquiry = await this.dataSource.getRepository(Inquiry).findOne({
      where: { id: inquiryId },
    });
    if (!inquiry) throw new NotFoundException('Inquiry not found');

    const allowed: InquiryDocumentType[] = [
      'trade_license',
      'previous_certificate',
      'audit_report',
      'scope_letter',
      'other',
    ];

    const newDocs: InquiryDocument[] = files.map((file, i) => {
      const requestedType = (types[i] || 'other') as InquiryDocumentType;
      const safeType: InquiryDocumentType = allowed.includes(requestedType)
        ? requestedType
        : 'other';

      return {
        type: safeType,
        path: `uploads/inquiries/${inquiryId}/${file.filename}`,
        filename: file.originalname,
        uploaded_at: new Date().toISOString(),
        uploaded_by: uploadedBy,
      };
    });

    const existing = Array.isArray(inquiry.documents)
      ? inquiry.documents.filter(
        (d): d is InquiryDocument => typeof d === 'object',
      )
      : [];

    inquiry.documents = [...existing, ...newDocs];

    await this.dataSource.getRepository(Inquiry).save(inquiry);

    return {
      message: 'Documents uploaded successfully',
      count: newDocs.length,
      documents: inquiry.documents,
    };
  }

  // ═════════════════════════════════════════════════════════════
  // ✅ Delete one document by its index in the array
  // ═════════════════════════════════════════════════════════════
  async deleteDocument(inquiryId: number, index: number) {
    const inquiry = await this.dataSource.getRepository(Inquiry).findOne({
      where: { id: inquiryId },
    });
    if (!inquiry) throw new NotFoundException('Inquiry not found');

    const docs = Array.isArray(inquiry.documents) ? [...inquiry.documents] : [];
    if (index < 0 || index >= docs.length) {
      throw new BadRequestException('Invalid document index');
    }

    const removed = docs.splice(index, 1)[0];

    try {
      if (removed?.path) {
        const fullPath = path.join(process.cwd(), removed.path);
        if (fs.existsSync(fullPath)) fs.unlinkSync(fullPath);
      }
    } catch (err) {
      console.warn('Could not delete file from disk:', (err as Error).message);
    }

    inquiry.documents = docs;
    await this.dataSource.getRepository(Inquiry).save(inquiry);

    return { message: 'Document deleted', documents: inquiry.documents };
  }

  // ═════════════════════════════════════════════════════════════
  // ✅ Pipeline summary also filters by user
  // ═════════════════════════════════════════════════════════════
  async getPipelineSummary(
    currentUserId?: number,
  ): Promise<Record<string, number>> {
    const query = this.dataSource
      .getRepository(Inquiry)
      .createQueryBuilder('inq')
      .select('inq.status', 'status')
      .addSelect('COUNT(*)', 'count')
      .groupBy('inq.status');

    if (currentUserId) {
      const canViewAll = await this.userCanViewAll(currentUserId);
      if (!canViewAll) {
        query.andWhere('inq.submitted_by_id = :uid', { uid: currentUserId });
      }
    }

    const results = await query.getRawMany();

    const summary: Record<string, number> = {
      PENDING: 0,
      IN_REVIEW: 0,
      DRAFT_READY: 0,
      CHANGES_REQUESTED: 0,
      CLIENT_CONFIRMED: 0,
      FINAL_ISSUED: 0,
    };

    results.forEach((r) => {
      if (summary[r.status] !== undefined) {
        summary[r.status] = Number(r.count);
      }
    });

    return summary;
  }

  // ═════════════════════════════════════════════════════════════
  // ✅ NEW — Returns distinct list of users who have submitted inquiries
  //    (used by super admin / scheme filter dropdown)
  // ═════════════════════════════════════════════════════════════
  // ═════════════════════════════════════════════════════════════
  // ✅ NEW — Returns distinct list of users who have submitted inquiries
  //    (used by super admin / scheme filter dropdown)
  // ═════════════════════════════════════════════════════════════
  async getSubmittersList(): Promise<SubmitterRow[]> {
    const result = await this.dataSource.query(`
      SELECT DISTINCT u.id, u.firstName, u.lastName, u.email
      FROM users u
      INNER JOIN inquiry i ON i.submitted_by_id = u.id
      ORDER BY u.firstName, u.lastName
    `);
    return result;
  }
}