import {
  Injectable,
  NotFoundException,
  BadRequestException,
  ForbiddenException,
  Logger,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource, EntityManager, In } from 'typeorm';
import * as fs from 'fs';
import * as path from 'path';
import {
  AuditRequest,
  AuditRequestStatus,
  ClientGroup,
} from '../entities/audit-request.entity';
import {
  AuditSchedule,
  ScheduleStatus,
} from '../../audit-schedules/entities/audit-schedule.entity';
import {
  AuditScheduleRow,
  RowStatus,
} from '../../audit-schedules/entities/audit-schedule-row.entity';
import { AuditStatusHistory } from '../../audit-schedules/entities/audit-status-history.entity';
import { Company } from '../../companies/entities/company.entity';
import { CompanySequence } from '../../companies/entities/company-sequence.entity';
import { Standard } from '../../standards/entities/standard.entity';
import { User } from '../../user/entities/user.entity';

import { CreateAuditRequestDto } from '../dto/create-audit-request.dto';
import { UpdateAuditRequestDto } from '../dto/update-audit-request.dto';
import { ScheduleAuditRequestDto } from '../dto/schedule-audit-request.dto';
import { RejectAuditRequestDto } from '../dto/reject-audit-request.dto';
import { CancelAuditRequestDto } from '../dto/cancel-audit-request.dto';
import { ListAuditRequestsQueryDto } from '../dto/list-audit-requests.dto';

// Existing services from audit-schedules — we reuse the audit code generator
import { AuditCodeGeneratorService } from '../../audit-schedules/services/audit-code-generator.service';

// Notifications — same pattern as audit-schedules
import { NotificationsService } from '../../notifications/notifications.service';
import { NotificationType } from '../../notifications/enums/notification-type.enum';
import { MailsService } from '../../mails/mails.service';
import { CreateBatchAuditRequestDto } from '../dto/create-batch-audit-request.dto';
// 🆕 Proceed to Inquiry
import { InquiriesService } from '../../inquiries/inquiries.service';
import { Inquiry } from '../../inquiries/entities/inquiry.entity';
import {
  ProceedToInquiryDto,
  ProceedBatchToInquiryDto,
} from '../dto/proceed-to-inquiry.dto';
// Email template builders
import {
  buildAuditEmail,
  AuditEmailContext,
} from '../templates/audit-email-template';
import {
  buildAuditorAssignmentEmail,
  AuditorScheduleRow,
} from '../templates/auditor-assignment-email-template';
import { CompanyResolverService } from '../../companies/services/company-resolver.service'; // 🆕
import { CompanySyncService } from '../../companies/services/company-sync.service';

const SUPER_ADMIN_IDS = [1, 8];

const SUPER_ADMIN_ROLE_ID = 1;
const COORDINATOR_ROLE_ID = 5;

// Email addresses that should NEVER receive admin/coordinator notification
// emails, even if they hold a matching role. Compared case-insensitively.
const EMAIL_DO_NOT_SEND = ['coordinator@iicc.ae', 'mianaliibrahim@gmail.com'];
const EXTRA_ADMIN_EMAILS = ['account.ehs@qrs.ae'];


// SLOT SYSTEM CONFIG
const SUBMISSION_WINDOW_DAYS = 3;
const MAX_AUDITS_PER_DAY = 12;

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

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
    private readonly companyResolver: CompanyResolverService,  // 🆕 ADDED
    private readonly companySyncService: CompanySyncService,
    private readonly codeGenerator: AuditCodeGeneratorService,
    private readonly notifications: NotificationsService,
    private readonly mailsService: MailsService,             // 🆕
    private readonly inquiriesService: InquiriesService,     // 🆕 proceed-to-inquiry
  ) { }
  private async generateCompanyCodeInTx(
    name: string,
    manager: EntityManager,
  ): Promise<string> {
    const seqRow = await manager
      .createQueryBuilder(CompanySequence, 'seq')
      .setLock('pessimistic_write')
      .where('seq.id = :id', { id: 1 })
      .getOne();

    if (!seqRow) {
      throw new BadRequestException(
        'Company sequence row missing — cannot generate company code',
      );
    }

    let initials = name
      .split(' ')
      .filter((w) => w.length > 2)
      .map((w) => w[0].toUpperCase())
      .join('')
      .substring(0, 3);

    if (initials.length < 3) {
      initials = name
        .replace(/[^A-Za-z]/g, '')
        .substring(0, 3)
        .toUpperCase();
    }

    const now = new Date();
    const year = now.getFullYear();
    const month = String(now.getMonth() + 1).padStart(2, '0');

    let companyCode = '';
    while (true) {
      seqRow.last_number = Number(seqRow.last_number) + 1;
      if (seqRow.last_number > 999999) seqRow.last_number = 1;

      const uniqueSeq = seqRow.last_number.toString().padStart(6, '0');
      companyCode = `${initials}-${year}-${month}-${uniqueSeq}`;

      const exists = await manager.findOne(Company, {
        where: { company_code: companyCode },
      });
      if (!exists) break;
    }

    await manager.save(seqRow);
    return companyCode;
  }

  private async sendAsUserSafe(
    senderUserId: number | null | undefined,
    mail: {
      to: string;
      cc?: string;
      bcc?: string;
      subject: string;
      html: string;
      attachments?: any[];
    },
  ): Promise<void> {
    if (!senderUserId) return;
    await this.mailsService
      .sendAsUser(senderUserId, mail)
      .catch((e) =>
        this.logger.warn(`[AUDIT-REQ-MAIL] sendAsUser failed: ${e.message}`),
      );
  }
  // 🆕 ENRICH COMPANY — after proceed creates/resolves the company, fill its
  // data entered by users is never overwritten.
  private async enrichCompanyFromRequest(
    companyId: number,
    request: AuditRequest,
  ): Promise<void> {
    try {
      const repo = this.dataSource.getRepository(Company);
      const company = await repo.findOne({
        where: { id: companyId },
        relations: ['standards'],   // ⚠️ remove this line if Company has no standards relation
      });
      if (!company) return;

      const isPlaceholder = (v: any): boolean => {
        const s = String(v ?? '').trim().toLowerCase();
        return (
          !s ||
          s === 'n/a' ||
          s === 'unknown' ||
          s === 'unknown@example.com' ||
          s === '0000000000'
        );
      };

      let changed = false;
      const fill = (field: keyof Company, value: any) => {
        if (value && isPlaceholder((company as any)[field])) {
          (company as any)[field] = value;
          changed = true;
        }
      };

      // ── contact details from the request's auditee ──
      fill('contact_person', request.auditee_name);
      fill('email', request.auditee_email);
      fill('mobile', request.auditee_contact);
      fill('designation', request.auditees?.[0]?.designation);

      // ── location → address + city (city = first part before comma) ──
      fill('address', request.location);
      fill('city', request.location?.split(',')[0]?.trim());

      // ── certification context ──
      fill('accreditation', request.accreditation);
      fill('certification_body', String(request.client_group));
      fill('client_group', String(request.client_group));

      // ── standards — add the request's standards, keep existing ones ──
      if (request.standard_ids?.length && Array.isArray((company as any).standards)) {
        const existing = new Set(
          ((company as any).standards as Standard[]).map((s) => s.id),
        );
        const missing = request.standard_ids.filter((sid) => !existing.has(sid));
        if (missing.length) {
          const toAdd = await this.dataSource
            .getRepository(Standard)
            .find({ where: { id: In(missing) } });
          (company as any).standards = [
            ...((company as any).standards as Standard[]),
            ...toAdd,
          ];
          changed = true;
        }
      }

      if (changed) {
        await repo.save(company);
        this.logger.log(
          `[COMPANY-ENRICH] company #${companyId} filled from audit request #${request.id}`,
        );
      }
    } catch (e: any) {
      this.logger.warn(
        `[COMPANY-ENRICH] failed for company #${companyId}: ${e?.message}`,
      );
    }
  }
  // 🆕 Build safe attachment list — skips missing files, de-dupes names
  private buildAttachments(
    documents:
      | Array<{ filename: string; path: string; size?: number }>
      | null
      | undefined,
  ): any[] {
    const seen = new Map<string, number>();
    const out: any[] = [];

    for (const d of documents ?? []) {
      const fullPath = path.join(process.cwd(), d.path);

      if (!fs.existsSync(fullPath)) {
        this.logger.warn(
          `[AUDIT-REQ-MAIL] attachment missing on disk, skipped: ${d.path}`,
        );
        continue;
      }

      let name = d.filename;
      const count = (seen.get(name.toLowerCase()) ?? 0) + 1;
      seen.set(name.toLowerCase(), count);
      if (count > 1) {
        const dot = name.lastIndexOf('.');
        name =
          dot > 0
            ? `${name.slice(0, dot)} (${count})${name.slice(dot)}`
            : `${name} (${count})`;
      }

      out.push({ filename: name, path: fullPath });
    }

    this.logger.log(
      `[AUDIT-REQ-MAIL] attaching ${out.length}/${(documents ?? []).length} file(s): ${out
        .map((a) => a.filename)
        .join(', ')}`,
    );
    return out;
  }
  // 🆕 ADMIN EMAIL — fetch the email addresses of everyone holding any of the
  // given role ids (used to email coordinators + super admins about new requests).
  private async getEmailsByRoleIds(roleIds: number[]): Promise<string[]> {
    if (!roleIds?.length) return [];
    try {
      const rows = await this.dataSource.query(
        `
        SELECT DISTINCT u.email
        FROM users u
        INNER JOIN user_roles ur ON ur.user_id = u.id
        WHERE ur.role_id IN (?)
          AND u.email IS NOT NULL
          AND u.email <> ''
        `,
        [roleIds],
      );
      const emails = rows
        .map((r: any) => r.email)
        .filter(Boolean)
        .filter(
          (e: string) =>
            !EMAIL_DO_NOT_SEND.includes(String(e).trim().toLowerCase()),
        );

      // 🐞 DEBUG — which roles were queried and which emails came back
      console.log('[ADMIN-EMAIL][getEmailsByRoleIds] roleIds =', roleIds);
      console.log('[ADMIN-EMAIL][getEmailsByRoleIds] raw rows =', rows);
      console.log('[ADMIN-EMAIL][getEmailsByRoleIds] excluded =', EMAIL_DO_NOT_SEND);
      console.log('[ADMIN-EMAIL][getEmailsByRoleIds] emails  =', emails);

      return emails;
    } catch (e: any) {
      this.logger.warn(`[AUDIT-REQ-MAIL] getEmailsByRoleIds failed: ${e.message}`);
      console.log('[ADMIN-EMAIL][getEmailsByRoleIds] ERROR =', e.message);
      return [];
    }
  }

  // ✅ ACCESS CONTROL — permission-based, NO hardcoded role names.

  private async userCanViewAll(userId: number): Promise<boolean> {
    if (SUPER_ADMIN_IDS.includes(userId)) {
      this.logger.log(`[AUDIT-REQ-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 = 'audit-requests'
      LIMIT 1
      `,
      [userId],
    );

    if (rolePerms.length > 0) {
      this.logger.log(
        `[AUDIT-REQ-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 = 'audit-requests'
        LIMIT 1
        `,
        [userId],
      );
      if (directPerms.length > 0) {
        this.logger.log(
          `[AUDIT-REQ-PERM] User ${userId} → has VIEW-ALL via direct grant`,
        );
        return true;
      }
    } catch {
      /* user_permissions table may not exist — that's fine */
    }

    this.logger.log(
      `[AUDIT-REQ-PERM] User ${userId} → NO view-all, restricted to own`,
    );
    return false;
  }
  private prettyRowStatus(status: string | null | undefined): string {
    const s = String(status || '').toUpperCase();
    const map: Record<string, string> = {
      PENDING: 'Pending',
      CONFIRMED: 'Scheduled',
      COMPLETED: 'Completed',
      CANCELLED: 'Cancelled',
      RESCHEDULED: 'Rescheduled',
    };
    return map[s] || (status ? String(status) : 'Pending');
  }

    /**
   * 🆕 Notify the marketing submitter that their audit request has been
   * proceeded to an inquiry. Sent ONLY to the submitter — no CC — so the
   * admin/coordinator email sent by InquiriesService.create() is unchanged.
   */
  private async notifyRequestProceededToInquiry(
    requestId: number,
    inquiry: any,
    triggeredByUserId: number,
  ): Promise<void> {
    try {
      const request = await this.findOne(requestId);
      if (!request?.requested_by?.email) return;

      // Skip if the submitter is the same person who clicked Proceed —
      // they already receive the inquiry email as the inquiry submitter.
      if (request.requested_by_id === triggeredByUserId) return;

      const submitterName = this.fullName(request.requested_by);
      const actor = await this.dataSource
        .getRepository(User)
        .findOne({ where: { id: triggeredByUserId } });
      const actorName = actor ? this.fullName(actor) : 'Coordinator';

      const companyName =
        inquiry?.company?.name ?? request.company?.name ?? request.company_name ?? '—';
      const standards = await this.getStandardNames(request.standard_ids);
      const certStr = this.formatCertificationType(request.certification_type as any);
      const dateTimeStr = this.formatAuditDateTime(
        (inquiry?.audit_date ?? request.proposed_date) as any,
        undefined,
        request.mode as any,
      );

      const html = buildAuditEmail({
        recipientName: submitterName,
        introLine:
          `Your audit request <strong>#${request.id}</strong> has been proceeded to inquiry ` +
          `<strong style="font-family:'Courier New',monospace;">${inquiry?.inquiry_ref ?? inquiry?.id}</strong>` +
          ` by <strong>${actorName}</strong>. Below is a copy of the inquiry details.`,
        subjectPrefix: '📋 Inquiry Created',
        highlightColor: 'green',
        companyName,
        auditeeName: request.auditee_name,
        auditeeContact: request.auditee_contact,
        auditeeEmail: request.auditee_email,
        standards,
        dateTimeOfAudit: dateTimeStr,
        locationDetails: request.location,
        certification: certStr,
        accreditation: request.accreditation,
        marketingRemarks: [
          inquiry?.inquiry_type ? `Inquiry Type: ${inquiry.inquiry_type}` : '',
          inquiry?.audit_stage ? `Audit Stage: ${inquiry.audit_stage}` : '',
          inquiry?.auditor_name ? `Auditor: ${inquiry.auditor_name}` : '',
          inquiry?.cert_body ? `Cert Body: ${inquiry.cert_body}` : '',
          inquiry?.previous_cert_no ? `Previous Cert No: ${inquiry.previous_cert_no}` : '',
          inquiry?.notes ? `Notes: ${inquiry.notes}` : '',
        ].filter(Boolean).join('<br/>') || undefined,
        ctaLabel: 'View Inquiry',
        ctaUrl: `${process.env.APP_URL || 'https://crm.qrsyst.com'}/inquiries/${inquiry?.id}`,
        ...this.qrsSender({
          senderName: actorName,
          senderEmail: actor?.email ?? undefined,
        }),
      });

      // TO = submitter only. No cc / bcc → admin & coordinator get nothing extra.
      await this.sendAsUserSafe(triggeredByUserId, {
        to: request.requested_by.email,
        subject: `${inquiry?.inquiry_ref ?? 'Inquiry'} — Inquiry Created for ${companyName}`,
        html,
        attachments: this.buildAttachments(request.documents),
      });

      this.logger.log(
        `[PROCEED-MAIL] inquiry copy sent to submitter ${request.requested_by.email} for request #${requestId}`,
      );
    } catch (e: any) {
      this.logger.warn(`[PROCEED-MAIL] failed: ${e?.message}`);
    }
  }
  // ═══════════════════════════════════════════════════════════════════════
  // CREATE — Marketing submits a new audit request
  // Maps directly from the "New Audit Request" form fields.
  // ═══════════════════════════════════════════════════════════════════════
  async create(
    dto: CreateAuditRequestDto,
    currentUserId: number,
  ): Promise<AuditRequest> {
    // 🔧 AUDIT-FIX-1: Moved resolveCompany INSIDE the transaction to prevent
    //    race conditions — two concurrent requests for the same new company
    //    can no longer both pass "no match" and collide on insert.
    let companyId: number | null = null;
    let companyBranchId: number | null = null;

    const newId = await this.dataSource.transaction(async (manager) => {
      // ✅ Resolve company INSIDE the transaction
      if (dto.company_id || dto.company_name) {
        const company = await this.companyResolver.resolveCompany(
          {
            company_id: dto.company_id ?? null,
            name: dto.company_name,
            trade_license_no: null,
            client_group: dto.client_group,
          },
          manager,  // 🔧 use transaction manager, not a separate entityManager
        );
        companyId = company.id;

        // Resolve branch (head office by default)
        const branch = await this.companyResolver.resolveBranch(
          company.id,
          {
            company_branch_id: dto.company_branch_id ?? null,
            branch_name: dto.branch_name ?? null,
            address: dto.location ?? null,
            contact_person: dto.auditee_name ?? null,
            email: dto.auditee_email ?? null,
            mobile: dto.auditee_contact ?? null,
          },
          manager,  // 🔧 use transaction manager
        );
        companyBranchId = branch.id;
      }
      // Validate standards exist
      const standards = await manager.find(Standard, {
        where: { id: In(dto.standard_ids) },
      });
      console.log('[STD-CHECK] sent ids  =', dto.standard_ids);
      console.log('[STD-CHECK] found ids =', standards.map((s) => s.id));
      if (standards.length !== dto.standard_ids.length) {
        throw new BadRequestException('One or more standard IDs are invalid');
      }

      // Create the request row
      const request = manager.create(AuditRequest, {
        company_id: companyId,
        company_branch_id: companyBranchId,  // ✅ FIX: Set this field
        company_name: dto.company_name,
        company_source: dto.company_source ?? 'Client',
        client_group: dto.client_group ?? ClientGroup.QRS,
        client_ref_id: dto.client_ref_id ?? null,
        auditee_name: dto.auditee_name,
        auditee_contact: dto.auditee_contact,
        auditee_email: dto.auditee_email,
        auditees: dto.auditees ?? [],
        standard_ids: dto.standard_ids,
        certification_type: dto.certification_type,
        accreditation: dto.accreditation,
        scope_of_work: dto.scope_of_work ?? null,
        previous_cert_no: dto.previous_cert_no ?? null,
        proposed_date: dto.proposed_date,
        proposed_time: dto.proposed_time,
        location: dto.location,
        mode: dto.mode,
        marketing_remarks: dto.marketing_remarks ?? null,
        status: AuditRequestStatus.SUBMITTED,
        requested_by_id: currentUserId,
      });

      // SLOT SYSTEM — validate before saving
      await this.validateSlotAvailability(dto.proposed_date, manager);

      const saved = await manager.save(request);
      // 🆕 NEW - Link standards to company (smart: only add missing ones)
      if (companyId && standards.length) {
        const company = await manager.findOne(Company, { where: { id: companyId } });
        if (company) {
          // ✅ SMART: Only add standards that aren't already linked
          const existing = new Set(
            (company.standards as any[] || []).map((s: any) => s.id),
          );
          const toAdd = standards.filter((s) => !existing.has(s.id));
          if (toAdd.length) {
            company.standards = [
              ...(company.standards || []),
              ...toAdd,
            ];
            await manager.save(company);
            this.logger.log(`[AUDIT-CREATE] Linked ${toAdd.length} new standard(s) to company #${companyId}`);
          } else {
            this.logger.log(`[AUDIT-CREATE] Company #${companyId} already has all standards`);
          }
        }
      }

      return saved.id;
    });

    // 🆕 NEW - Sync company details automatically
    try {
      if (companyId) {
        const request = await this.findOne(newId);
        await this.companySyncService.syncFromAuditRequest(
          request,
          currentUserId,
          this.dataSource.createEntityManager(),
        );
      }
    } catch (e: any) {
      this.logger.warn(`[AUDIT-CREATE] Sync failed: ${e?.message}`);
    }

    this.logger.log(
      `Audit request created (id=${newId}, user=${currentUserId}, company=${companyId})`,
    );

    return this.findOne(newId);
  }
  async createBatch(
    dto: CreateBatchAuditRequestDto,
    currentUserId: number,
  ): Promise<{
    ok: true;
    created: Array<{ index: number; id: number; company_name: string }>;
    count: number;
  }> {
    if (!dto.clients?.length) {
      throw new BadRequestException('At least one client is required.');
    }

    // Validate the shared standards
    const allStdIds = [
      ...new Set(dto.clients.flatMap((c) => c.standard_ids ?? [])),
    ];

    const standards = await this.dataSource.getRepository(Standard).find({
      where: { id: In(allStdIds) },
    });

    if (standards.length !== allStdIds.length) {
      throw new BadRequestException('One or more standard IDs are invalid');
    }

    const created: Array<{ index: number; id: number; company_name: string }> = [];

    for (const [idx, c] of dto.clients.entries()) {
      let companyId: number | null = null;
      let companyBranchId: number | null = null;

      // 🔧 AUDIT-FIX-2: Moved resolveCompany INSIDE the transaction (same fix as create)
      const batchId = await this.dataSource.transaction(async (manager) => {
        // ✅ Resolve company INSIDE the transaction
        if (c.company_id || c.company_name) {
          const company = await this.companyResolver.resolveCompany(
            {
              company_id: c.company_id ?? null,
              name: c.company_name,
              trade_license_no: null,
              client_group: dto.client_group,
            },
            manager,  // 🔧 use transaction manager
          );
          companyId = company.id;

          // Resolve branch
          const branch = await this.companyResolver.resolveBranch(
            company.id,
            {
              company_branch_id: c.company_branch_id ?? null,
              branch_name: c.branch_name ?? null,
              address: c.location ?? null,
              contact_person: c.auditee_name ?? null,
              email: c.auditee_email ?? null,
              mobile: c.auditee_contact ?? null,
            },
            manager,  // 🔧 use transaction manager
          );
          companyBranchId = branch.id;
        }
        const request = manager.create(AuditRequest, {
          company_id: companyId,
          company_branch_id: companyBranchId,  // ✅ FIX: Set this field
          company_name: c.company_name,
          company_source: c.company_source ?? 'Client',
          client_group: dto.client_group ?? ClientGroup.QRS,
          client_ref_id: c.client_ref_id ?? null,
          auditee_name: c.auditee_name,
          auditee_contact: c.auditee_contact,
          auditee_email: c.auditee_email,
          auditees: c.auditees ?? [],
          standard_ids: c.standard_ids,
          certification_type: c.certification_type,
          accreditation: dto.accreditation,
          scope_of_work: c.scope_of_work ?? null,
          previous_cert_no: c.previous_cert_no ?? null,
          proposed_date: c.proposed_date,
          proposed_time: c.proposed_time,
          location: c.location,
          mode: c.mode,
          marketing_remarks: c.marketing_remarks ?? null,
          status: AuditRequestStatus.SUBMITTED,
          requested_by_id: currentUserId,
        });

        // SLOT SYSTEM — validate before saving
        await this.validateSlotAvailability(c.proposed_date, manager);

        const saved = await manager.save(request);
        if (companyId) {
          const company = await manager.findOne(Company, { where: { id: companyId } });
          if (company) {
            const existing = new Set(
              (company.standards as any[] || []).map((s: any) => s.id),
            );
            const toAdd = standards.filter((s) => !existing.has(s.id));
            if (toAdd.length) {
              company.standards = [
                ...(company.standards || []),
                ...toAdd,
              ];
              await manager.save(company);
            }
          }
        }

        return saved.id;
      });

      // 🆕 SYNC COMPANY DETAILS
      try {
        if (companyId) {
          const request = await this.findOne(batchId);
          await this.companySyncService.syncFromAuditRequest(
            request,
            currentUserId,
            this.dataSource.createEntityManager(),
          );
        }
      } catch (e: any) {
        this.logger.warn(`[BATCH-${idx}] Sync failed: ${e?.message}`);
      }

      created.push({
        index: idx,
        id: batchId,
        company_name: c.company_name,
      });
    }

    return {
      ok: true,
      created,
      count: created.length,
    };
  }
  // ═══════════════════════════════════════════════════════════════════════
  // SEARCH COMPANIES — For dropdown/autocomplete in audit request form
  // Case-insensitive search, user can only see their own companies
  // ═══════════════════════════════════════════════════════════════════════
  async searchCompanies(query: string, currentUserId: number): Promise<any[]> {
    try {
      const qb = this.dataSource
        .getRepository(Company)
        .createQueryBuilder('c')
        .select(['c.id', 'c.name', 'c.company_code', 'c.contact_person', 'c.email', 'c.mobile'])
        .orderBy('c.name', 'ASC')
        .take(20);  // Limit to 20 results

      // 🆕 FIXED: Case-insensitive search using LOWER()
      if (query && query.trim()) {
        qb.where('LOWER(c.name) LIKE LOWER(:q)', { q: `%${query}%` });
      }

      // 🆕 ADDED: User permission check - QRS/TQS users only see their companies
      const canViewAll = await this.userCanViewAll(currentUserId);
      if (!canViewAll) {
        // Non-admin: only companies they have created audit requests for
        qb.innerJoin(
          AuditRequest,
          'ar',
          'ar.company_id = c.id AND ar.requested_by_id = :uid',
          { uid: currentUserId },
        );
      }

      const companies = await qb.getMany();
      this.logger.log(`[SEARCH-COMPANIES] query="${query}" user=${currentUserId} found=${companies.length}`);
      return companies;
    } catch (e: any) {
      this.logger.warn(`[SEARCH-COMPANIES] error: ${e?.message}`);
      return [];
    }
  }

  // ═══════════════════════════════════════════════════════════════════════
  // FIND ONE — with all relations needed by the UI
  // ═══════════════════════════════════════════════════════════════════════
  async findOne(id: number, manager?: EntityManager): Promise<AuditRequest> {
    const repo = manager
      ? manager.getRepository(AuditRequest)
      : this.dataSource.getRepository(AuditRequest);

    const request = await repo
      .createQueryBuilder('ar')
      .leftJoinAndSelect('ar.company', 'company')
      .leftJoinAndSelect('ar.requested_by', 'requested_by')
      .leftJoinAndSelect('ar.reviewed_by', 'reviewed_by')
      .leftJoinAndSelect('ar.audit_schedule_row', 'audit_schedule_row')
      .leftJoinAndSelect('audit_schedule_row.schedule', 'schedule')
      .leftJoinAndSelect('audit_schedule_row.lead_auditor', 'lead_auditor')
      .where('ar.id = :id', { id })
      .getOne();

    if (!request) {
      throw new NotFoundException('Audit request not found');
    }
    this.logger.log(`[FINDONE] id=${id} documents=${JSON.stringify(request.documents)}`);
    return request;

  }

  async findAll(q: ListAuditRequestsQueryDto, currentUserId?: number) {
    const page = q.page || 1;
    const limit = q.limit || 25;
    const skip = (page - 1) * limit;

    const qb = this.dataSource
      .getRepository(AuditRequest)
      .createQueryBuilder('ar')
      .leftJoinAndSelect('ar.company', 'company')
      .leftJoinAndSelect('ar.requested_by', 'requested_by')
      .leftJoinAndSelect('ar.audit_schedule_row', 'audit_schedule_row')
      .leftJoinAndSelect('audit_schedule_row.lead_auditor', 'lead_auditor')
      .orderBy('ar.proposed_date', 'DESC')
      .addOrderBy('ar.id', 'DESC')
      .skip(skip)
      .take(limit);

    // ✅ Permission-based row scoping — if the user has no 'view-all'
    // permission on the audit-requests module, restrict to their own rows.
    if (currentUserId) {
      const canViewAll = await this.userCanViewAll(currentUserId);
      if (!canViewAll) {
        qb.andWhere('ar.requested_by_id = :uid', { uid: currentUserId });
      }
    }

    if (q.status) {
      qb.andWhere('ar.status = :status', { status: q.status });
    }

    if (q.company_id) {
      qb.andWhere('ar.company_id = :cid', { cid: q.company_id });
      // 🆕 ADDED: Check if user has access to this company
      if (currentUserId) {
        const canViewAll = await this.userCanViewAll(currentUserId);
        if (!canViewAll) {
          // Non-admin users can only access companies they created audit requests for
          qb.andWhere('ar.requested_by_id = :uid', { uid: currentUserId });
        }
      }
    }

    if (q.requested_by_id) {
      qb.andWhere('ar.requested_by_id = :ruid', { ruid: q.requested_by_id });
    }
    if (q.date_from) {
      qb.andWhere('ar.proposed_date >= :df', { df: q.date_from });
    }
    if (q.date_to) {
      qb.andWhere('ar.proposed_date <= :dt', { dt: q.date_to });
    }

    // 🆕 FIXED: Case-insensitive search using LOWER()
    if (q.search) {
      qb.andWhere(
        '(LOWER(ar.auditee_name) LIKE LOWER(:s) OR LOWER(ar.auditee_email) LIKE LOWER(:s) OR LOWER(company.name) LIKE LOWER(:s) OR CAST(ar.id AS CHAR) LIKE :s)',
        { s: `%${q.search}%` },
      );
    }

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

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

  // ═══════════════════════════════════════════════════════════════════════
  // UPDATE — marketing edits before scheduling
  // ═══════════════════════════════════════════════════════════════════════
  async update(
    id: number,
    dto: UpdateAuditRequestDto,
    currentUserId: number,
  ): Promise<AuditRequest> {
    const repo = this.dataSource.getRepository(AuditRequest);
    const request = await repo.findOne({ where: { id } });
    if (!request) {
      throw new NotFoundException('Audit request not found');
    }

    // Only the requester can edit (or admin — checked at controller level)
    // edit ANY request. Everyone else can only edit their own.
    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && request.requested_by_id !== currentUserId) {
      throw new ForbiddenException('You can only edit your own requests');
    }

    // Cannot edit after scheduled/completed/rejected/cancelled
    const editable: AuditRequestStatus[] = [
      AuditRequestStatus.DRAFT,
      AuditRequestStatus.SUBMITTED,
      AuditRequestStatus.UNDER_REVIEW,
    ];
    if (!canViewAll && !editable.includes(request.status)) {
      throw new BadRequestException(
        `Cannot edit a request in status "${request.status}"`,
      );
    }

    // 🔧 AUDIT-FIX-4: If company_name changed, re-resolve the company so
    //    company_id points to the correct record (not the old one).
    if (
      dto.company_name &&
      dto.company_name.trim() !== (request.company_name || '').trim()
    ) {
      try {
        const resolved = await this.companyResolver.resolveCompany({
          company_id: null,
          name: dto.company_name,
          trade_license_no: null,
          client_group: request.client_group as string,
        });
        request.company_id = resolved.id;
        this.logger.log(
          `[UPDATE] company_name changed → re-resolved to company #${resolved.id}`,
        );
      } catch (e: any) {
        this.logger.warn(`[UPDATE] company re-resolve failed: ${e?.message}`);
      }
    }

    Object.assign(request, dto);
    await repo.save(request);

    this.logger.log(`Audit request updated (id=${id}, user=${currentUserId})`);
    return this.findOne(id);
  }

  // ═══════════════════════════════════════════════════════════════════════
  // MARK UNDER REVIEW — coordinator opens the request
  // Auto-triggered when coordinator opens the detail page for the first time
  // ═══════════════════════════════════════════════════════════════════════
  async markUnderReview(
    id: number,
    currentUserId: number,
  ): Promise<AuditRequest> {
    const repo = this.dataSource.getRepository(AuditRequest);
    const request = await repo.findOne({ where: { id } });
    if (!request) {
      throw new NotFoundException('Audit request not found');
    }

    // Only transition if currently SUBMITTED
    if (request.status !== AuditRequestStatus.SUBMITTED) {
      return this.findOne(id); // already moved past or backwards — no-op
    }

    request.status = AuditRequestStatus.UNDER_REVIEW;
    request.reviewed_by_id = currentUserId;
    request.reviewed_at = new Date();
    await repo.save(request);

    this.logger.log(
      `Audit request marked under_review (id=${id}, user=${currentUserId})`,
    );
    return this.findOne(id);
  }

  // SCHEDULE — the KEY action ⭐

  async schedule(
    id: number,
    dto: ScheduleAuditRequestDto,
    currentUserId: number,
  ): Promise<AuditRequest> {
    const result = await this.dataSource.transaction(async (manager) => {
      // ── Step 1: Lock and load the audit_request ────────────────────
      const request = await manager
        .getRepository(AuditRequest)
        .createQueryBuilder('ar')
        .setLock('pessimistic_write')
        .where('ar.id = :id', { id })
        .getOne();

      if (!request) {
        throw new NotFoundException('Audit request not found');
      }

      // Validate status — can only schedule from SUBMITTED or UNDER_REVIEW
      const schedulable: AuditRequestStatus[] = [
        AuditRequestStatus.SUBMITTED,
        AuditRequestStatus.UNDER_REVIEW,
      ];
      if (!schedulable.includes(request.status)) {
        throw new BadRequestException(
          `Cannot schedule a request in status "${request.status}"`,
        );
      }

      if (request.audit_schedule_row_id) {
        throw new BadRequestException(
          `Request #${id} is already linked to audit row #${request.audit_schedule_row_id}`,
        );
      }

      // ── Step 2: Validate ALL auditors exist (lead + co-auditors) ────
      const coIds = (dto.co_auditor_ids ?? []).filter(
        (cid) => cid !== dto.lead_auditor_id, // lead can't also be co-auditor
      );
      const allAuditorIds = [...new Set([dto.lead_auditor_id, ...coIds])];
      const auditors = await manager.find(User, {
        where: { id: In(allAuditorIds) },
      });
      if (auditors.length !== allAuditorIds.length) {
        throw new BadRequestException('One or more auditor IDs not found');
      }
      const auditor = auditors.find((a) => a.id === dto.lead_auditor_id)!;
      const coAuditors = auditors.filter((a) => a.id !== dto.lead_auditor_id);

      // ── Step 3: Resolve the company ──────────────────────────────────
      // 🔧 AUDIT-FIX-3: Replaced inline LOWER() lookup + raw company creation
      //    with companyResolver.resolveCompany() — same as create() and createBatch().
      //    This ensures normalized_name is always set and duplicate prevention works.
      let company: Company | null = null;

      if (request.company_id || request.company_name) {
        company = await this.companyResolver.resolveCompany(
          {
            company_id: request.company_id ?? null,
            name: request.company_name ?? '',
            trade_license_no: null,
            client_group: dto.client_group,
          },
          manager,
        );
      }

      if (!company) {
        throw new BadRequestException(
          'Audit request has no company name to schedule against',
        );
      }

      // Point the request at the resolved real company id.
      request.company_id = company.id;

      // ── Step 4: Load standards from request.standard_ids ────────────
      const standards = await manager.find(Standard, {
        where: { id: In(request.standard_ids) },
      });
      if (standards.length !== request.standard_ids.length) {
        throw new BadRequestException(
          'Some standards from the request no longer exist',
        );
      }

      // ── Step 5: FIND-OR-CREATE the parent audit_schedules row ───────
      // Coordinator may already have a schedule for this date — reuse it.
      // Otherwise create a new one in PUBLISHED status (since we're
      // adding a confirmed audit, not a draft).
      let schedule = await manager
        .getRepository(AuditSchedule)
        .createQueryBuilder('sch')
        .setLock('pessimistic_write')
        .where('sch.schedule_date = :d', { d: dto.audit_date })
        .andWhere('sch.coordinator_id = :cid', { cid: currentUserId })
        .andWhere('sch.status != :cancelled', {
          cancelled: ScheduleStatus.CANCELLED,
        })
        .getOne();

      if (!schedule) {
        schedule = manager.create(AuditSchedule, {
          schedule_date: dto.audit_date,
          title: this.defaultScheduleTitle(dto.audit_date),
          client_group: dto.client_group,
          coordinator_id: currentUserId,
          status: ScheduleStatus.PUBLISHED,
          created_by_id: currentUserId,
        });
        schedule = await manager.save(schedule);
        this.logger.log(
          `Created new audit_schedule (id=${schedule.id}, date=${dto.audit_date})`,
        );
      }

      // ── Step 6: Determine row_no for the new row ───────────────────
      const existingRows = await manager.find(AuditScheduleRow, {
        where: { schedule_id: schedule.id },
        select: ['row_no'],
      });
      const nextRowNo =
        (existingRows.reduce((max, r) => Math.max(max, r.row_no), 0) ?? 0) + 1;

      // ── Step 7: Generate audit_code (uses existing service) ────────
      const auditCode = await this.codeGenerator.generate(
        dto.audit_type,
        dto.audit_date,
        manager,
      );

      // ── Step 8: INSERT audit_schedule_row ───────────────────────────
      const row = manager.create(AuditScheduleRow, {
        schedule_id: schedule.id,
        schedule,
        row_no: nextRowNo,
        audit_code: auditCode,
        audit_type: dto.audit_type,
        audit_stage: dto.audit_stage,
        audit_mode: dto.audit_mode ?? (request.mode as any),
        accreditation: request.accreditation,
        company_id: request.company_id,
        company,
        lead_auditor_id: dto.lead_auditor_id,
        lead_auditor: auditor,
        co_auditors: coAuditors,   // 🆕
        standards,
        audit_time: dto.audit_time,
        audit_time_label: dto.audit_time_label,
        notes: dto.notes,
        // Link back to the request that produced this row
        audit_request_id: request.id,
      } as any);
      const savedRow = await manager.save(row);

      // ── Step 9: Audit history entry for the new row ─────────────────
      await manager.save(
        manager.create(AuditStatusHistory, {
          row_id: savedRow.id,
          new_status: savedRow.status,
          changed_by_id: currentUserId,
          reason: `Created from audit request #${request.id}`,
        }),
      );

      // ── Step 10: UPDATE audit_request — link + status change ────────
      request.status = AuditRequestStatus.SCHEDULED;
      request.audit_schedule_row_id = savedRow.id;
      request.coordinator_remarks = dto.coordinator_remarks ?? null;
      request.scheduled_at = new Date();
      if (!request.reviewed_by_id) {
        request.reviewed_by_id = currentUserId;
        request.reviewed_at = new Date();
      }
      await manager.save(request);

      this.logger.log(
        `Audit request scheduled (request_id=${request.id}, row_id=${savedRow.id}, schedule_id=${schedule.id})`,
      );

      return { requestId: request.id, rowId: savedRow.id };
    });

    // 🆕 BACK-FILL — if this request was already proceeded to an inquiry,
    // push the newly assigned lead auditor onto it (only if still TBD).
    try {
      const fresh = await this.findOne(result.requestId);
      if (fresh.inquiry_id && fresh.audit_schedule_row?.lead_auditor) {
        const inqRepo = this.dataSource.getRepository(Inquiry);
        const inq = await inqRepo.findOne({ where: { id: fresh.inquiry_id } });
        if (inq && (!inq.auditor_name || inq.auditor_name === 'TBD')) {
          inq.auditor_name = this.fullName(fresh.audit_schedule_row.lead_auditor);
          await inqRepo.save(inq);
          this.logger.log(
            `[PROCEED-SYNC] inquiry #${inq.id} auditor_name back-filled from schedule of request #${fresh.id}`,
          );
        }
      }
    } catch (e: any) {
      this.logger.warn(`[PROCEED-SYNC] auditor back-fill failed: ${e?.message}`);
    }

    // ─── AFTER COMMIT: fire notifications (fire-and-forget) ──────────────
    this.notifyRequestScheduled(
      result.requestId,
      result.rowId,
      currentUserId,
    ).catch((err) =>
      this.logger.warn(
        `Notify audit-request.scheduled failed for id=${result.requestId}: ${err.message}`,
      ),
    );

    return this.findOne(result.requestId);
  }

  /** Map audit-request certification_type → inquiry_type used by the Inquiry module.
   *  ⚠️ Verify the right-hand values against your Inquiry.inquiry_type values. */
  private mapCertTypeToInquiryType(certType: string | null | undefined): string {
    const t = String(certType || '').toUpperCase();
    const map: Record<string, string> = {
      INITIAL: 'INITIAL',
      SURVEILLANCE: 'SURVEILLANCE',
      SURVEILLANCE_1: 'SURVEILLANCE',
      SURVEILLANCE_2: 'SURVEILLANCE',
      RECERTIFICATION: 'RE_CERTIFICATION',
      SURVEILLANCE_RECERT: 'RE-CERTIFICATION',
      'RECERTIFICATION OR RENEWAL': 'RE-CERTIFICATION',
    };
    return map[t] || 'INITIAL';
  }

  async proceedToInquiry(id: number, dto: any, currentUserId: number): Promise<{ request: any; inquiry: any }> {
    const request = await this.findOne(id);
    if (request.inquiry_id) {
      throw new BadRequestException('Request #' + id + ' already has inquiry #' + request.inquiry_id);
    }
    const blocked = [AuditRequestStatus.REJECTED, AuditRequestStatus.CANCELLED];
    if (blocked.includes(request.status)) {
      throw new BadRequestException('Cannot proceed a request in status "' + request.status + '"');
    }
    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && request.requested_by_id !== currentUserId) {
      throw new ForbiddenException('You can only proceed your own requests');
    }
    const leadAuditorName = request.audit_schedule_row?.lead_auditor ? this.fullName(request.audit_schedule_row.lead_auditor) : undefined;
    const inquiryDto: any = {
      company_id: request.company_id ?? undefined,
      company_name: request.company?.name ?? request.company_name,
      inquiry_type: dto.inquiry_type ?? 'INITIAL',
      audit_date: dto.audit_date ?? request.proposed_date,
      auditor_name: dto.auditor_name ?? leadAuditorName ?? 'TBD',
      cert_body: dto.cert_body ?? (String(request.client_group) === 'TQS' ? 'TQS' : 'QRS'),
      audit_stage: dto.audit_stage,
      previous_cert_no: dto.previous_cert_no ?? request.previous_cert_no ?? undefined,
      scope_of_work: dto.scope_of_work ?? request.scope_of_work ?? undefined,
      notes: dto.notes ?? request.marketing_remarks ?? 'Created from audit request #' + request.id,
      standards: dto.standards?.length ? dto.standards : request.standard_ids,
      submitted_by_id: currentUserId,
      assigned_to_id: dto.assigned_to_id,
      surveillance_audit_due: dto.surveillance_audit_due ?? null,
      recertification_due: dto.recertification_due ?? null,
    };
    const inquiry = await this.inquiriesService.create(inquiryDto);
    if (request.documents?.length) {
      try {
        const inqRepo = this.dataSource.getRepository(Inquiry);
        const inq = await inqRepo.findOne({ where: { id: inquiry.id } });
        if (inq) {
          const copied = request.documents.map((d: any) => ({
            type: 'other' as any,
            doc_type: d.doc_type,
            path: d.path,
            filename: d.filename,
            uploaded_at: new Date().toISOString(),
            uploaded_by: currentUserId,
          }));
          const existing = Array.isArray((inq as any).documents) ? (inq as any).documents : [];
          (inq as any).documents = [...existing, ...copied];
          await inqRepo.save(inq);
          this.logger.log('[PROCEED] copied ' + copied.length + ' doc(s) from request #' + id + ' to inquiry #' + inquiry.id);
        }
      } catch (e: any) {
        this.logger.warn('[PROCEED] doc copy failed: ' + e?.message);
      }
    }
    const repo = this.dataSource.getRepository(AuditRequest);
    request.inquiry_id = inquiry.id;
    if (!request.company_id && (inquiry as any).company?.id) {
      request.company_id = (inquiry as any).company.id;
    }
    await repo.save(request);
    this.logger.log('[PROCEED] request #' + id + ' -> inquiry #' + inquiry.id);
    await this.notifyRequestProceededToInquiry(id, inquiry, currentUserId);

    return { request: await this.findOne(id), inquiry };
  }

  async proceedBatchToInquiry(dto: any, currentUserId: number): Promise<{ ok: true; results: any[] }> {
    const results: any[] = [];
    for (const reqId of dto.request_ids) {
      try {
        const { inquiry } = await this.proceedToInquiry(reqId, dto.overrides ?? {}, currentUserId);
        results.push({ id: reqId, ok: true, inquiry_id: inquiry.id, inquiry_ref: inquiry.inquiry_ref });
      } catch (e: any) {
        results.push({ id: reqId, ok: false, error: e?.message ?? 'Failed' });
      }
    }
    return { ok: true, results };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // REJECT — coordinator declines
  // ═══════════════════════════════════════════════════════════════════════
  async reject(
    id: number,
    dto: RejectAuditRequestDto,
    currentUserId: number,
  ): Promise<AuditRequest> {
    const repo = this.dataSource.getRepository(AuditRequest);
    const request = await repo.findOne({ where: { id } });
    if (!request) {
      throw new NotFoundException('Audit request not found');
    }

    const rejectable: AuditRequestStatus[] = [
      AuditRequestStatus.SUBMITTED,
      AuditRequestStatus.UNDER_REVIEW,
    ];
    if (!rejectable.includes(request.status)) {
      throw new BadRequestException(
        `Cannot reject a request in status "${request.status}"`,
      );
    }

    request.status = AuditRequestStatus.REJECTED;
    request.rejection_reason = dto.rejection_reason;
    request.reviewed_by_id = currentUserId;
    if (!request.reviewed_at) {
      request.reviewed_at = new Date();
    }

    await repo.save(request);

    this.logger.log(`Audit request rejected (id=${id}, user=${currentUserId})`);

    // Notify the requester
    this.notifyRequestRejected(id, dto.rejection_reason, currentUserId).catch(
      (err) =>
        this.logger.warn(
          `Notify audit-request.rejected failed for id=${id}: ${err.message}`,
        ),
    );

    return this.findOne(id);
  }

  // ═══════════════════════════════════════════════════════════════════════
  // CANCEL — marketing cancels their own (before scheduled), or coordinator cancels
  // ✅ CHANGED: dropped the `isMarketingOnly` param. Ownership is now decided
  // via userCanViewAll() — a user WITHOUT view-all may only cancel their own
  // request, and may not cancel one that is already SCHEDULED. Users WITH
  // view-all (admin / scheme / coordinator) can cancel any cancellable request.
  // ═══════════════════════════════════════════════════════════════════════
  async cancel(
    id: number,
    dto: CancelAuditRequestDto,
    currentUserId: number,
  ): Promise<AuditRequest> {
    const repo = this.dataSource.getRepository(AuditRequest);
    const request = await repo.findOne({ where: { id } });
    if (!request) {
      throw new NotFoundException('Audit request not found');
    }

    // ✅ Decide privileges from the permission system (no role strings).
    const canViewAll = await this.userCanViewAll(currentUserId);
    const isOwnerOnly = !canViewAll; // no view-all → restricted to own rows

    if (isOwnerOnly && request.requested_by_id !== currentUserId) {
      throw new ForbiddenException('You can only cancel your own requests');
    }

    // A restricted user cannot cancel a request that's already scheduled —
    // they must ask the coordinator to cancel the audit_schedule_row instead.
    if (isOwnerOnly && request.status === AuditRequestStatus.SCHEDULED) {
      throw new BadRequestException(
        'Cannot cancel — request is already scheduled. Contact coordinator to cancel the audit.',
      );
    }

    const cancellable: AuditRequestStatus[] = [
      AuditRequestStatus.DRAFT,
      AuditRequestStatus.SUBMITTED,
      AuditRequestStatus.UNDER_REVIEW,
    ];
    if (!cancellable.includes(request.status)) {
      throw new BadRequestException(
        `Cannot cancel a request in status "${request.status}"`,
      );
    }

    request.status = AuditRequestStatus.CANCELLED;
    request.rejection_reason = `${dto.cancellation_reason}${dto.cancellation_notes ? ` — ${dto.cancellation_notes}` : ''}`;
    await repo.save(request);

    this.logger.log(
      `Audit request cancelled (id=${id}, user=${currentUserId})`,
    );

    return this.findOne(id);
  }

  // ═══════════════════════════════════════════════════════════════════════
  // ANALYTICS — dashboard counters
  // ✅ CHANGED: dropped the `isMarketingOnly` param. Scoping mirrors findAll:
  // a user without view-all sees counts for ONLY their own requests.
  // ═══════════════════════════════════════════════════════════════════════
  async getAnalytics(currentUserId?: number) {
    const repo = this.dataSource.getRepository(AuditRequest);

    // ✅ Resolve view-all once, then reuse for every count query.
    let restrictToOwn = false;
    if (currentUserId) {
      const canViewAll = await this.userCanViewAll(currentUserId);
      restrictToOwn = !canViewAll;
    }

    const baseQb = () => {
      const qb = repo.createQueryBuilder('ar');
      if (restrictToOwn && currentUserId) {
        qb.andWhere('ar.requested_by_id = :uid', { uid: currentUserId });
      }
      return qb;
    };

    const [submitted, underReview, scheduled, completed, rejected, cancelled] =
      await Promise.all([
        baseQb()
          .andWhere('ar.status = :s', { s: AuditRequestStatus.SUBMITTED })
          .getCount(),
        baseQb()
          .andWhere('ar.status = :s', { s: AuditRequestStatus.UNDER_REVIEW })
          .getCount(),
        baseQb()
          .andWhere('ar.status = :s', { s: AuditRequestStatus.SCHEDULED })
          .getCount(),
        baseQb()
          .andWhere('ar.status = :s', { s: AuditRequestStatus.COMPLETED })
          .getCount(),
        baseQb()
          .andWhere('ar.status = :s', { s: AuditRequestStatus.REJECTED })
          .getCount(),
        baseQb()
          .andWhere('ar.status = :s', { s: AuditRequestStatus.CANCELLED })
          .getCount(),
      ]);

    return {
      submitted,
      under_review: underReview,
      scheduled,
      completed,
      rejected,
      cancelled,
      total:
        submitted + underReview + scheduled + completed + rejected + cancelled,
    };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // PRIVATE HELPERS
  // ═══════════════════════════════════════════════════════════════════════

  private defaultScheduleTitle(scheduleDate: string): string {
    const d = new Date(scheduleDate);
    const day = String(d.getDate()).padStart(2, '0');
    const month = d.toLocaleString('en-US', { month: 'long' }).toUpperCase();
    const year = d.getFullYear();
    return `AUDIT SCHEDULE FOR ${day}TH ${month} ${year}`;
  }

  private fullName(u: User): string {
    const f = (u as any).firstName || '';
    const l = (u as any).lastName || '';
    const both = `${f} ${l}`.trim();
    return both || u.email;
  }

  /**
   * Fetch comma-separated standard names from the standard_ids stored on the request.
   * Returns "—" if none could be loaded.
   */
  private async getStandardNames(standardIds: number[]): Promise<string> {
    if (!standardIds?.length) return '—';
    const standards = await this.dataSource
      .getRepository(Standard)
      .find({ where: { id: In(standardIds) } });
    if (!standards.length) return '—';
    return standards.map((s) => (s as any).name).join(', ');
  }

  /**
   * Format a YYYY-MM-DD date + HH:mm:ss time into the QRS email format,
   * e.g. "12th May 2026 at 11.00 AM (Online Audit)"
   */
  private formatAuditDateTime(
    isoDate: string,
    isoTime: string | undefined,
    mode: string | undefined,
  ): string {
    const d = new Date(isoDate);
    const day = d.getDate();
    const month = d.toLocaleString('en-US', { month: 'long' });
    const year = d.getFullYear();

    // English ordinal suffix (1st, 2nd, 3rd, 4th, ...)
    const suffix = (() => {
      const j = day % 10;
      const k = day % 100;
      if (j === 1 && k !== 11) return 'st';
      if (j === 2 && k !== 12) return 'nd';
      if (j === 3 && k !== 13) return 'rd';
      return 'th';
    })();

    let timeStr = '';
    if (isoTime) {
      const [hh, mm] = isoTime.split(':').map((v) => parseInt(v, 10) || 0);
      const period = hh >= 12 ? 'PM' : 'AM';
      const hour12 = hh % 12 === 0 ? 12 : hh % 12;
      timeStr = ` at ${hour12}.${mm.toString().padStart(2, '0')} ${period}`;
    }

    const modeStr = mode
      ? ` (${mode === 'ONLINE' ? 'Online Audit' : mode === 'ONSITE' ? 'Onsite Audit' : mode === 'HYBRID' ? 'Hybrid Audit' : mode})`
      : '';
    return `${day}${suffix} ${month} ${year}${timeStr}${modeStr}`;
  }

  /**
   * Pretty certification type, e.g. SURVEILLANCE_2 → "2nd Surveillance"
   */
  private formatCertificationType(certType: string): string {
    const map: Record<string, string> = {
      INITIAL: 'Initial Certification',
      SURVEILLANCE: 'Surveillance',
      SURVEILLANCE_1: '1st Surveillance',
      SURVEILLANCE_2: '2nd Surveillance',
      RECERTIFICATION: 'Re-Certification',
      SURVEILLANCE_RECERT: 'Surveillance & Re-Certification',
      'Recertification or renewal': 'Recertification or Renewal',
    };
    return map[certType] || certType;
  }
  private prettyAuditMode(mode: string | null | undefined): string {
    const m = String(mode || '').toUpperCase();
    const map: Record<string, string> = {
      ONLINE: 'Online',
      ONSITE: 'Onsite',
      OFFICE: 'Office',
      HYBRID: 'Hybrid',
      REMOTE: 'Remote',
    };
    return map[m] || (mode ? String(mode) : '—');
  }

  /**
   * Returns the QRS signature block fields. Override individual fields by
   * passing partial overrides (e.g. when sender info should differ).
   */
  private qrsSender(
    overrides?: Partial<
      Pick<
        AuditEmailContext,
        | 'senderName'
        | 'senderTitle'
        | 'senderPhone'
        | 'senderEmail'
        | 'senderAddress'
        | 'senderWebsite'
      >
    >,
  ) {
    return {
      senderName: overrides?.senderName ?? 'Ms. Julie',
      senderTitle: overrides?.senderTitle ?? 'Sales & Marketing Manager',
      senderPhone: overrides?.senderPhone ?? '026714302 / 0509014931',
      senderEmail: overrides?.senderEmail ?? 'account@qrs.ae',
      senderAddress:
        overrides?.senderAddress ??
        'Office # 203 Nasser Tower, Saeed Bin Ahmed Al Otaiba St. Al Danah, Zone 1, Abu Dhabi, UAE',
      senderWebsite: overrides?.senderWebsite ?? 'www.qrsyst.com',
    };
  }

  // ═══════════════════════════════════════════════════════════════════════
  // NOTIFICATION HELPERS — match the audit-schedules notification pattern.
  // Each called fire-and-forget AFTER its transaction commits.
  // ═══════════════════════════════════════════════════════════════════════

  /**
   * Notify the submitter when their request has been recorded.
   * Acts as a confirmation receipt on the marketing user's bell + email.
   */
  private readonly submitEmailSent = new Set<number>();

  private async notifyRequestSubmitted(requestId: number): Promise<void> {
    // 🛡️ Guard — only send the submit email ONCE per request
    if (this.submitEmailSent.has(requestId)) {
      this.logger.log(
        `[SUBMIT-MAIL] skipped duplicate send for request #${requestId}`,
      );
      return;
    }
    this.submitEmailSent.add(requestId);

    const request = await this.findOne(requestId);
    if (!request) return;

    const companyName = request.company?.name ?? request.company_name ?? '—';
    const requesterName = request.requested_by
      ? this.fullName(request.requested_by)
      : 'Marketing';

    // Build all the data for the QRS-style email table
    const standardsStr = await this.getStandardNames(request.standard_ids);
    const dateTimeStr = this.formatAuditDateTime(
      request.proposed_date,
      request.proposed_time,
      request.mode as any,
    );
    const certStr = this.formatCertificationType(
      request.certification_type as any,
    );

    const whatsappMsg = [
      `*📋 Audit Request Submitted*`,
      ``,
      `🏢 ${companyName}`,
      `📅 Proposed: ${dateTimeStr}`,
      `📍 ${request.location}`,
      ``,
      `We'll notify you when the coordinator schedules this audit.`,
    ].join('\n');

    const emailHtml = buildAuditEmail({
      recipientName: requesterName,
      introLine:
        "Your audit request has been recorded successfully. Below are the details — we'll notify you once the coordinator schedules this audit.",
      subjectPrefix: '📋 Audit Request Submitted',
      highlightColor: 'yellow',
      companyName,
      auditeeName: request.auditee_name,
      auditeeContact: request.auditee_contact,
      auditeeEmail: request.auditee_email,
      standards: standardsStr,
      dateTimeOfAudit: dateTimeStr,
      locationDetails: request.location,
      certification: certStr,
      accreditation: request.accreditation,
      marketingRemarks: request.marketing_remarks ?? undefined,
      ctaLabel: 'View Request',
      ctaUrl: `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-requests/${request.id}`,
      ...this.qrsSender({
        senderName: requesterName,
        senderEmail: request.requested_by?.email ?? undefined,
      }),
    });

    // attach the uploaded documents to the email
    const attachments = this.buildAttachments(request.documents);

    // send the actual email FROM the submitter's own mailbox, WITH attachments
    await this.sendAsUserSafe(request.requested_by_id, {
      to: request.requested_by?.email ?? '',
      subject: `${companyName} — Audit Request Submitted`,
      html: emailHtml,
      attachments,
    });

    // ════════════════════════════════════════════════════════════════════
    // ADMIN EMAIL — email coordinators + super admins that a new request
    // needs review.
    // ════════════════════════════════════════════════════════════════════
    const adminEmails = await this.getEmailsByRoleIds([
      COORDINATOR_ROLE_ID,
      SUPER_ADMIN_ROLE_ID,
    ]);

    // 🆕 merge in always-notify admin address(es), de-duplicated and still
    // 🆕 manzoorqrs@gmail.com is a QRS-only admin — never notify for TQS clients.
    const isTqs = request.client_group === ClientGroup.TQS;

    const extraAdmins = isTqs
      ? []
      : EXTRA_ADMIN_EMAILS
        .map((e) => e.trim().toLowerCase())
        .filter((e) => e && !EMAIL_DO_NOT_SEND.includes(e));

    const submitterEmail = (request.requested_by?.email || '')
      .trim()
      .toLowerCase();

    const allAdminEmails = Array.from(
      new Set([...adminEmails.map((e) => e.toLowerCase()), ...extraAdmins]),
    ).filter((e) => e !== submitterEmail); // 🛡️ don't double-email the submitter

    console.log('[ADMIN-EMAIL] client_group =', request.client_group, 'isTqs =', isTqs);
    console.log('[ADMIN-EMAIL] role emails  =', adminEmails);
    console.log('[ADMIN-EMAIL] extra admins =', extraAdmins);
    console.log('[ADMIN-EMAIL] FINAL recipients =', allAdminEmails);

    if (allAdminEmails.length) {
      const adminHtml = buildAuditEmail({
        recipientName: 'Team',
        introLine: `A new audit request has been submitted by <strong>${requesterName}</strong> and needs your review. Below are the details.`,
        subjectPrefix: '📋 New Audit Request — Action Required',
        highlightColor: 'yellow',
        companyName,
        auditeeName: request.auditee_name,
        auditeeContact: request.auditee_contact,
        auditeeEmail: request.auditee_email,
        standards: standardsStr,
        dateTimeOfAudit: dateTimeStr,
        locationDetails: request.location,
        certification: certStr,
        accreditation: request.accreditation,
        marketingRemarks: request.marketing_remarks ?? undefined,
        ctaLabel: 'Review Request',
        ctaUrl: `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-requests/${request.id}`,
        ...this.qrsSender(),
      });

      // sent FROM the submitter's mailbox, TO all admins/coordinators + extras.
      await this.sendAsUserSafe(request.requested_by_id, {
        to: allAdminEmails.join(','),
        subject: `New Audit Request — ${companyName} (needs review)`,
        html: adminHtml,
        attachments,          // 🆕 same files the submitter got
      });

      console.log('[ADMIN-EMAIL] sendAsUserSafe call completed for requestId =', requestId);
    } else {
      console.log('[ADMIN-EMAIL] NO admin emails found — nothing sent for requestId =', requestId);
    }

    // in-app bell + WhatsApp only — email handled above via sendAsUser
    await this.notifications.send({
      type: NotificationType.AUDIT_REQUEST_SUBMITTED,
      title: `Audit request submitted: ${companyName}`,
      body: `Your request for ${request.proposed_date} has been received`,
      target_user_ids: [request.requested_by_id],
      target_role_ids: [COORDINATOR_ROLE_ID, SUPER_ADMIN_ROLE_ID],
      reference_id: requestId,
      reference_type: 'audit_request',
      is_urgent: false,
      requires_action: false,
      link_url: `/audit-requests/${requestId}`,
      payload: {
        request_id: requestId,
        company_name: companyName,
        proposed_date: request.proposed_date,
      },
      whatsapp_message: whatsappMsg,
    });
  }
  /**
   * Notify the marketing submitter when the coordinator schedules their audit.
   */
  private async notifyRequestScheduled(
    requestId: number,
    rowId: number,
    triggeredByUserId: number,
  ): Promise<void> {
    const request = await this.findOne(requestId);
    if (!request) return;

    const row = await this.dataSource.getRepository(AuditScheduleRow).findOne({
      where: { id: rowId },
      relations: ['lead_auditor', 'co_auditors', 'schedule', 'schedule.coordinator', 'standards', 'company'],
    });
    if (!row) return;

    const coordinatorId = triggeredByUserId;   // 🆕 sender = coordinator who scheduled

    // 🆕 Build attachments from the request's uploaded documents — reused for
    // the auditor's assignment email so they receive the same files.
    const auditAttachments = this.buildAttachments(request.documents);

    const standards =
      row.standards?.map((s) => (s as any).name).join(', ') ||
      (await this.getStandardNames(request.standard_ids));
    const companyName =
      row.company?.name ?? request.company?.name ?? request.company_name ?? '—';

    const submitterName = request.requested_by ? this.fullName(request.requested_by) : 'Marketing';
    const auditorName = row.lead_auditor ? this.fullName(row.lead_auditor) : 'Lead Auditor';
    const coordinatorName = row.schedule?.coordinator ? this.fullName(row.schedule.coordinator) : 'Coordinator';

    const dateTimeStr = this.formatAuditDateTime(
      row.schedule.schedule_date as any,
      row.audit_time as any,
      (row.audit_mode as any) || (request.mode as any),
    );
    const certStr = this.formatCertificationType(request.certification_type as any);
    const scheduleDateLabel = this.formatAuditDateTime(row.schedule.schedule_date as any, undefined, undefined);

    const whatsappMsg = [
      `*✅ Audit Scheduled*`, ``,
      `📋 ${row.audit_code}`,
      `🏢 ${companyName}`,
      `📅 ${dateTimeStr}`,
      auditorName ? `👤 Auditor: ${auditorName}` : '',
    ].filter(Boolean).join('\n');

    // ── Submitter (marketing) email ───────────────────────────────────────
    const submitterEmailHtml = buildAuditEmail({
      recipientName: submitterName,
      introLine: `Good news! Your audit request has been scheduled. Audit Code: <strong style="font-family:'Courier New',monospace;">${row.audit_code}</strong>${auditorName ? ` · Assigned Auditor: <strong>${auditorName}</strong>` : ''}.`,
      subjectPrefix: '✅ Audit Scheduled',
      highlightColor: 'green',
      companyName,
      auditeeName: request.auditee_name,
      auditeeContact: request.auditee_contact,
      auditeeEmail: request.auditee_email,
      standards,
      dateTimeOfAudit: dateTimeStr,
      locationDetails: request.location,
      certification: certStr,
      accreditation: request.accreditation,
      marketingRemarks: request.marketing_remarks ?? undefined,
      ctaLabel: 'View Schedule',
      ctaUrl: `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-schedules/${row.schedule_id}`,
      ...this.qrsSender({
        senderName: coordinatorName,                                  // 🆕 coordinator signs it
        senderEmail: row.schedule?.coordinator?.email ?? undefined,   // 🆕
      }),
    });

    // 🆕 send submitter email FROM coordinator's mailbox
    await this.sendAsUserSafe(coordinatorId, {
      to: request.requested_by?.email ?? '',
      subject: `${row.audit_code} — Audit Scheduled for ${companyName}`,
      html: submitterEmailHtml,
      attachments: auditAttachments,   // ✅ this is here
    });

    await this.notifications.send({
      type: NotificationType.AUDIT_REQUEST_SCHEDULED,
      title: `Audit scheduled: ${row.audit_code}`,
      body: `${companyName} · ${row.schedule.schedule_date} · ${row.audit_time_label}`,
      target_user_ids: [request.requested_by_id],
      target_role_ids: [SUPER_ADMIN_ROLE_ID],
      reference_id: rowId,
      reference_type: 'audit_row',
      is_urgent: false,
      requires_action: false,
      link_url: `/audit-requests/${requestId}`,
      payload: { audit_code: row.audit_code, row_id: rowId, request_id: requestId },
      whatsapp_message: whatsappMsg,
      // email_subject / email_html intentionally removed
    });

    // ── Auditor emails — day-grid template, sent to lead + all co-auditors ──
    const allAssignedAuditors = [
      row.lead_auditor,
      ...(row.co_auditors ?? []),
    ].filter(Boolean) as User[];

    for (const assignedAuditor of allAssignedAuditors) {
      const isLead = assignedAuditor.id === row.lead_auditor_id;
      const assignedName = this.fullName(assignedAuditor);

      const scheduleRows = await this.loadAuditorDayGrid(
        assignedAuditor.id,
        row.schedule.schedule_date as any,
        rowId,
      );

      const auditorEmailHtml = buildAuditorAssignmentEmail({
        auditorName: assignedName,
        auditorEmail: assignedAuditor.email,
        scheduleDateLabel,
        coordinatorName,
        clientGroup: row.schedule?.client_group ?? undefined,
        auditCode: row.audit_code,
        companyName,
        auditeeName: request.auditee_name,
        auditeeContact: request.auditee_contact,
        auditeeEmail: request.auditee_email,
        dateTimeOfAudit: dateTimeStr,
        standards,
        certification: certStr,
        accreditation: request.accreditation,
        locationDetails: request.location,
        marketingRemarks: request.marketing_remarks ?? undefined,
        coordinatorRemarks: request.coordinator_remarks ?? undefined,
        ctaUrl: `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-schedules/${row.schedule_id}`,
        scheduleRows,
      });

      // send FROM coordinator's mailbox, WITH attachments
      await this.sendAsUserSafe(coordinatorId, {
        to: assignedAuditor.email ?? '',
        subject: `${isLead ? 'Lead Auditor' : 'Co-Auditor'} Assignment — ${row.audit_code} on ${scheduleDateLabel}`,
        html: auditorEmailHtml,
        attachments: auditAttachments,
      });

      await this.notifications.send({
        type: NotificationType.AUDIT_REQUEST_SCHEDULED,
        title: `${isLead ? 'Lead Auditor' : 'Co-Auditor'} Assignment: ${row.audit_code}`,
        body: `${companyName} · ${row.schedule.schedule_date} · ${row.audit_time_label}`,
        target_user_ids: [assignedAuditor.id],
        target_role_ids: [],
        reference_id: rowId,
        reference_type: 'audit_row',
        is_urgent: false,
        requires_action: true,
        link_url: `/audit-schedules/${row.schedule_id}`,
        payload: { audit_code: row.audit_code, row_id: rowId, request_id: requestId, role: isLead ? 'LEAD_AUDITOR' : 'CO_AUDITOR' },
        whatsapp_message: whatsappMsg,
      });
    }
  }

  /**
   * Load all audits assigned to this auditor on the given date, for the
   * "Your audit schedule for the day" grid in the auditor email. Marks the
   * row matching `currentRowId` so the template can highlight it.
   */
  private async loadAuditorDayGrid(
    auditorId: number,
    scheduleDate: string,
    currentRowId: number,
  ): Promise<AuditorScheduleRow[]> {
    const rows = await this.dataSource
      .getRepository(AuditScheduleRow)
      .createQueryBuilder('row')
      .leftJoinAndSelect('row.schedule', 'schedule')
      .leftJoinAndSelect('row.company', 'company')
      .leftJoinAndSelect('row.standards', 'standards')
      .leftJoin('row.co_auditors', 'coaud')
      .where('(row.lead_auditor_id = :aid OR coaud.id = :aid)', { aid: auditorId })
      .andWhere('schedule.schedule_date = :d', { d: scheduleDate })
      .orderBy('row.audit_time', 'ASC')
      .addOrderBy('row.id', 'ASC')
      .getMany();

    return rows.map((r) => ({
      audit_code: r.audit_code,
      category: this.formatCertificationType(r.audit_type as any),
      company_name: r.company?.name ?? '—',
      standards:
        (r.standards ?? []).map((s) => (s as any).name).join(', ') || '—',
      accreditation: r.accreditation ?? '—',
      stage: r.audit_stage ?? '—',
      mode: this.prettyAuditMode(r.audit_mode as any),
      coordinator: r.schedule?.coordinator_id
        ? `User #${r.schedule.coordinator_id}`
        : '—',
      time_label: r.audit_time_label || '—',
      status: this.prettyRowStatus(r.status as any),
      is_this_audit: r.id === currentRowId,
    }));
  }

  /**
   * Notify marketing when their request is rejected.
   */
  private async notifyRequestRejected(
    requestId: number,
    reason: string,
    triggeredByUserId: number,
  ): Promise<void> {
    const request = await this.findOne(requestId);
    if (!request) return;

    const triggeredBy = await this.dataSource
      .getRepository(User)
      .findOne({ where: { id: triggeredByUserId } });

    const companyName = request.company?.name ?? request.company_name ?? '—';
    const triggeredByName = triggeredBy ? this.fullName(triggeredBy) : 'Coordinator';

    const standardsStr = await this.getStandardNames(request.standard_ids);
    const dateTimeStr = this.formatAuditDateTime(
      request.proposed_date, request.proposed_time, request.mode as any,
    );
    const certStr = this.formatCertificationType(request.certification_type as any);

    const whatsappMsg = [
      `*❌ Audit Request Rejected*`, ``,
      `🏢 ${companyName}`,
      `📅 Was proposed: ${dateTimeStr}`,
      `Reason: ${reason}`,
      `By: ${triggeredByName}`,
    ].join('\n');

    const emailHtml = buildAuditEmail({
      recipientName: this.fullName(request.requested_by),
      introLine: `Unfortunately, your audit request has been declined by <strong>${triggeredByName}</strong>.<br/><br/><strong style="color:#991b1b;">Reason:</strong> ${reason}`,
      subjectPrefix: '❌ Audit Request Rejected',
      highlightColor: 'red',
      companyName,
      auditeeName: request.auditee_name,
      auditeeContact: request.auditee_contact,
      auditeeEmail: request.auditee_email,
      standards: standardsStr,
      dateTimeOfAudit: dateTimeStr,
      locationDetails: request.location,
      certification: certStr,
      accreditation: request.accreditation,
      marketingRemarks: request.marketing_remarks ?? undefined,
      ctaLabel: 'View Request',
      ctaUrl: `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-requests/${request.id}`,
      ...this.qrsSender({
        senderName: triggeredByName,                       // 🆕 coordinator who rejected
        senderEmail: triggeredBy?.email ?? undefined,      // 🆕
      }),
    });

    // 🆕 send FROM the rejecting coordinator's mailbox
    await this.sendAsUserSafe(triggeredByUserId, {
      to: request.requested_by?.email ?? '',
      subject: `${companyName} — Audit Request Rejected`,
      html: emailHtml,
    });

    await this.notifications.send({
      type: NotificationType.AUDIT_REQUEST_REJECTED,
      title: `Audit request rejected: ${companyName}`,
      body: `Reason: ${reason}`,
      target_user_ids: [request.requested_by_id],
      target_role_ids: [],
      reference_id: requestId,
      reference_type: 'audit_request',
      is_urgent: true,
      requires_action: false,
      link_url: `/audit-requests/${requestId}`,
      payload: { request_id: requestId, reason },
      whatsapp_message: whatsappMsg,
      // email_subject / email_html intentionally removed
    });
  }
  // ═══════════════════════════════════════════════════════════════════════
  // EMAIL HTML BUILDERS — match your existing brand shell from audit-schedules
  // ═══════════════════════════════════════════════════════════════════════

  private buildSubmittedHtml(ctx: {
    companyName: string;
    requesterName: string;
    proposedDate: string;
    proposedTime: string;
    auditeeName: string;
    requestId: number;
  }): string {
    const link = `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-requests/${ctx.requestId}`;
    return this.qrsShell(
      'New Audit Request',
      `
        <div style="font-size:48px;margin-bottom:8px;">📋</div>
        <h2 style="color:#1a0440;font-size:20px;margin:0 0 12px;">New Audit Request Submitted</h2>
        <p style="color:#666;font-size:14px;line-height:1.7;margin:0 0 24px;">
          A new audit request has been submitted by <strong>${ctx.requesterName}</strong> and needs your review.
        </p>
        <div style="background:#f8f5ff;border:1px solid #ede8ff;border-radius:12px;padding:18px 22px;text-align:left;margin:0 auto;">
          <table style="width:100%;border-collapse:collapse;font-size:13px;color:#555;">
            <tr><td style="padding:5px 0;color:#888;width:40%;">Company:</td><td style="padding:5px 0;color:#1a0440;font-weight:700;">${ctx.companyName}</td></tr>
            <tr><td style="padding:5px 0;color:#888;">Auditee:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.auditeeName}</td></tr>
            <tr><td style="padding:5px 0;color:#888;">Proposed:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.proposedDate} · ${ctx.proposedTime}</td></tr>
            <tr><td style="padding:5px 0;color:#888;">Submitted by:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.requesterName}</td></tr>
          </table>
        </div>
        <a href="${link}" style="display:inline-block;padding:13px 32px;background:linear-gradient(135deg,#8b14d4,#4a0080);color:#fff;text-decoration:none;border-radius:8px;font-weight:700;font-size:15px;margin-top:24px;">Review Request →</a>
      `,
    );
  }

  private buildScheduledHtml(ctx: {
    recipientName: string;
    auditCode: string;
    companyName: string;
    auditDate: string;
    auditTimeLabel?: string;
    auditorName?: string;
    standards?: string;
    scheduleId?: number;
  }): string {
    const link = ctx.scheduleId
      ? `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-schedules/${ctx.scheduleId}`
      : null;
    return this.qrsShell(
      'Audit Scheduled',
      `
        <div style="font-size:48px;margin-bottom:8px;">✅</div>
        <h2 style="color:#1a0440;font-size:20px;margin:0 0 12px;">Audit Scheduled</h2>
        <p style="color:#666;font-size:14px;line-height:1.7;margin:0 0 24px;">
          Hello <strong>${ctx.recipientName}</strong>,<br/>
          The audit has been scheduled and confirmed.
        </p>
        <div style="background:#f8f5ff;border:1px solid #ede8ff;border-radius:12px;padding:18px 22px;text-align:left;margin:0 auto;">
          <table style="width:100%;border-collapse:collapse;font-size:13px;color:#555;">
            <tr><td style="padding:5px 0;color:#888;width:40%;">Audit Code:</td><td style="padding:5px 0;color:#1a0440;font-weight:700;font-family:'Courier New',monospace;">${ctx.auditCode}</td></tr>
            <tr><td style="padding:5px 0;color:#888;">Company:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.companyName}</td></tr>
            <tr><td style="padding:5px 0;color:#888;">Date:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.auditDate}${ctx.auditTimeLabel ? ` · ${ctx.auditTimeLabel}` : ''}</td></tr>
            ${ctx.auditorName ? `<tr><td style="padding:5px 0;color:#888;">Auditor:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.auditorName}</td></tr>` : ''}
            ${ctx.standards ? `<tr><td style="padding:5px 0;color:#888;">Standards:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.standards}</td></tr>` : ''}
          </table>
        </div>
        ${link ? `<a href="${link}" style="display:inline-block;padding:13px 32px;background:linear-gradient(135deg,#8b14d4,#4a0080);color:#fff;text-decoration:none;border-radius:8px;font-weight:700;font-size:15px;margin-top:24px;">View Schedule →</a>` : ''}
      `,
    );
  }

  private buildRejectedHtml(ctx: {
    recipientName: string;
    companyName: string;
    proposedDate: string;
    reason: string;
    triggeredByName?: string;
    requestId: number;
  }): string {
    const link = `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-requests/${ctx.requestId}`;
    return this.qrsShell(
      'Audit Request Rejected',
      `
        <div style="font-size:48px;margin-bottom:8px;">❌</div>
        <h2 style="color:#1a0440;font-size:20px;margin:0 0 12px;">Audit Request Rejected</h2>
        <p style="color:#666;font-size:14px;line-height:1.7;margin:0 0 24px;">
          Hello <strong>${ctx.recipientName}</strong>,<br/>
          Your audit request for <strong>${ctx.companyName}</strong> has been rejected.
        </p>
        <div style="background:#fee2e2;border-left:4px solid #dc2626;padding:14px 18px;border-radius:8px;text-align:left;">
          <p style="margin:0;color:#991b1b;font-size:13px;"><strong>Reason:</strong> ${ctx.reason}</p>
          ${ctx.triggeredByName ? `<p style="margin:6px 0 0;color:#991b1b;font-size:12px;"><strong>Rejected by:</strong> ${ctx.triggeredByName}</p>` : ''}
        </div>
        <a href="${link}" style="display:inline-block;padding:13px 32px;background:linear-gradient(135deg,#8b14d4,#4a0080);color:#fff;text-decoration:none;border-radius:8px;font-weight:700;font-size:15px;margin-top:24px;">View Request →</a>
      `,
    );
  }

  private qrsShell(headerLabel: string, innerHtml: string): string {
    return `
      <div style="font-family:'Segoe UI',sans-serif;max-width:560px;margin:0 auto;background:#fff;border-radius:16px;overflow:hidden;box-shadow:0 4px 24px rgba(0,0,0,0.08);">
        <div style="background:linear-gradient(135deg,#8b14d4,#4a0080);padding:32px;text-align:center;">
          <h1 style="color:#fff;font-size:20px;margin:0;">Quality Registrar Systems</h1>
          <p style="color:rgba(255,255,255,0.7);margin:6px 0 0;font-size:13px;">${headerLabel}</p>
        </div>
        <div style="padding:36px 32px;text-align:center;">
          ${innerHtml}
        </div>
        <div style="background:#f8f5ff;padding:16px 32px;text-align:center;border-top:1px solid #ede8ff;">
          <p style="color:#bbb;font-size:11px;margin:0;">© ${new Date().getFullYear()} Quality Registrar Systems.</p>
        </div>
      </div>
    `;
  }
  async remove(
    id: number,
    currentUserId: number,
  ): Promise<{ message: string; id: number }> {
    const repo = this.dataSource.getRepository(AuditRequest);
    const request = await repo.findOne({ where: { id } });
    if (!request) {
      throw new NotFoundException('Audit request not found');
    }

    // ── Permission check — must have 'delete' on the audit-requests module ──
    const canDelete = await this.userCanDelete(currentUserId);
    if (!canDelete) {
      throw new ForbiddenException(
        'You do not have permission to delete audit requests.',
      );
    }

    // ── Ownership check — restricted users may only delete their own ────────
    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && request.requested_by_id !== currentUserId) {
      throw new ForbiddenException('You can only delete your own requests.');
    }

    // ── Status guard — COMPLETED can never be deleted ───────────────────────
    if (request.status === AuditRequestStatus.COMPLETED) {
      throw new BadRequestException('Cannot delete a completed audit request.');
    }

    // ── Status guard — SCHEDULED is linked to a real audit row ──────────────
    // Deleting it here would orphan the audit_schedule_row. Block it.
    if (
      request.status === AuditRequestStatus.SCHEDULED ||
      request.audit_schedule_row_id
    ) {
      throw new BadRequestException(
        'Cannot delete — this request is already scheduled and linked to an audit. ' +
        'Cancel the audit in the Audit Schedule module first, then delete.',
      );
    }

    // 🆕 Status guard — linked to an inquiry via Proceed to Inquiry.
    // Deleting would orphan the inquiry's back-link. Block it.
    if (request.inquiry_id) {
      throw new BadRequestException(
        `Cannot delete — this request is linked to inquiry #${request.inquiry_id}. ` +
        'Delete or unlink the inquiry first.',
      );
    }

    // ── Safe to delete (DRAFT / SUBMITTED / UNDER_REVIEW / REJECTED /
    //    CANCELLED — none of these have a linked audit row) ─────────────────
    await repo.delete(id);

    this.logger.log(
      `Audit request deleted (id=${id}, user=${currentUserId}, status=${request.status})`,
    );

    return { message: 'Audit request deleted', id };
  }

  // Super-admin IDs always pass.
  private async userCanDelete(userId: number): 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 = 'delete'
        AND m.slug = 'audit-requests'
      LIMIT 1
      `,
      [userId],
    );
    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 = 'delete'
          AND m.slug = 'audit-requests'
        LIMIT 1
        `,
        [userId],
      );
      if (directPerms.length > 0) return true;
    } catch {
      /* user_permissions table may not exist — that's fine */
    }

    return false;
  }
  // ═══════════════════════════════════════════════════════════════════════
  // UPLOAD DOCUMENTS — save files to disk, store metadata, THEN send the
  // submit email with those files attached.
  // ═══════════════════════════════════════════════════════════════════════
  async uploadDocuments(
    requestId: number,
    files: {
      trade_license?: Express.Multer.File[];
      previous_certificate?: Express.Multer.File[];
      files?: Express.Multer.File[];
    },
    uploadedBy: number,
  ): Promise<AuditRequest> {
    const repo = this.dataSource.getRepository(AuditRequest);
    const request = await repo.findOne({ where: { id: requestId } });
    if (!request) throw new NotFoundException('Audit request not found');

    // 🆕 MANDATORY DOCUMENT RULE
    // Trade license: always required. Previous certificate: required unless
    // this is an INITIAL certification (no previous cert exists yet).
    const existing = Array.isArray(request.documents) ? request.documents : [];
    const hasType = (t: string) =>
      existing.some((d: any) => d.doc_type === t) ||
      ((files as any)[t]?.length ?? 0) > 0;

    if (!hasType('trade_license')) {
      throw new BadRequestException('Trade License is required — please attach it.');
    }
    const needsPrevCert = request.certification_type !== ('INITIAL' as any);
    if (needsPrevCert && !hasType('previous_certificate')) {
      throw new BadRequestException(
        'Previous Certificate is required for this certification type — please attach it.',
      );
    }

    // 🆕 store each file WITH its doc_type
    const mapDocs = (list: Express.Multer.File[] | undefined, doc_type: string) =>
      (list ?? []).map((file) => ({
        doc_type,
        filename: file.originalname,
        path: `uploads/audit-requests/${requestId}/${file.filename}`,
        mimetype: file.mimetype,
        size: file.size,
      }));

    const newDocs = [
      ...mapDocs(files.trade_license, 'trade_license'),
      ...mapDocs(files.previous_certificate, 'previous_certificate'),
      ...mapDocs(files.files, 'other'),
    ];

    if (newDocs.length) {
      request.documents = [...existing, ...newDocs] as any;
      await repo.save(request);
      this.logger.log(`Saved ${newDocs.length} typed document(s) to audit request #${requestId}`);
    }

    // Now fire the submit email WITH the uploaded files attached
    this.notifyRequestSubmitted(requestId).catch((err) =>
      this.logger.warn(`Notify submitted (after upload) failed for id=${requestId}: ${err.message}`),
    );

    return this.findOne(requestId);
  }

  private async validateSlotAvailability(proposedDate: string, manager: EntityManager, excludeRequestId?: number): Promise<void> {
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    const proposed = new Date(proposedDate + 'T00:00:00');
    const windowEnd = new Date(today);
    windowEnd.setDate(windowEnd.getDate() + SUBMISSION_WINDOW_DAYS);
    if (proposed < today) {
      throw new BadRequestException('Cannot submit audit request for a past date (' + proposedDate + ').');
    }
    if (proposed > windowEnd) {
      throw new BadRequestException('Proposed date ' + proposedDate + ' is outside the submission window. You can only submit for today up to ' + this.formatDateStr(windowEnd) + '.');
    }
    let qb = manager.createQueryBuilder(AuditRequest, 'ar').where('ar.proposed_date = :date', { date: proposedDate }).andWhere('ar.status NOT IN (:...excluded)', { excluded: [AuditRequestStatus.CANCELLED, AuditRequestStatus.REJECTED] });
    if (excludeRequestId) { qb = qb.andWhere('ar.id != :excludeId', { excludeId: excludeRequestId }); }
    const count = await qb.getCount();
    if (count >= MAX_AUDITS_PER_DAY) {
      throw new BadRequestException('Slot full for ' + proposedDate + '. Maximum ' + MAX_AUDITS_PER_DAY + ' audits per day reached (' + count + ' booked). Contact super admin to increase the limit.');
    }
    this.logger.log('[SLOT-CHECK] Date ' + proposedDate + ' has ' + count + '/' + MAX_AUDITS_PER_DAY + ' audits');
  }

  async getAvailableSlots(): Promise<{ window_start: string; window_end: string; max_per_day: number; dates: { date: string; booked: number; available: number; status: string }[] }> {
    const today = new Date();
    today.setHours(0, 0, 0, 0);
    const windowEnd = new Date(today);
    windowEnd.setDate(windowEnd.getDate() + SUBMISSION_WINDOW_DAYS);
    const windowStart = this.formatDateStr(today);
    const windowEndStr = this.formatDateStr(windowEnd);
    const activeRequests = await this.dataSource.getRepository(AuditRequest).createQueryBuilder('ar').where('ar.proposed_date BETWEEN :from AND :to', { from: windowStart, to: windowEndStr }).andWhere('ar.status NOT IN (:...excluded)', { excluded: [AuditRequestStatus.CANCELLED, AuditRequestStatus.REJECTED] }).getMany();
    const countMap = new Map<string, number>();
    for (const req of activeRequests) {
      const ds = req.proposed_date;
      countMap.set(ds, (countMap.get(ds) || 0) + 1);
    }
    const dates: { date: string; booked: number; available: number; status: string }[] = [];
    for (let d = new Date(today); d <= windowEnd; d.setDate(d.getDate() + 1)) {
      const ds = this.formatDateStr(d);
      const booked = countMap.get(ds) || 0;
      const available = Math.max(0, MAX_AUDITS_PER_DAY - booked);
      dates.push({ date: ds, booked, available, status: available > 0 ? 'open' : 'full' });
    }
    return { window_start: windowStart, window_end: windowEndStr, max_per_day: MAX_AUDITS_PER_DAY, dates };
  }

  private formatDateStr(d: Date): string {
    return d.toISOString().split('T')[0];
  }
}