// ═══════════════════════════════════════════════════════════════════════════
// Company → Create Job (same flow as scheme.qrs.ae) — 2026-11-10
//
//   Companies row "Create Job" / View company → 📁 Documents:
//   • one form (company, audit from Audit Schedule, stage, lead auditor, templates with Select All,
//     dates, standards, employees, NACE, MD, risk, schedule slot);
//   • Save creates ONE JOB PER TICKED TEMPLATE (own job number each) in scheme_dbs.jobs, exactly like
//     the scheme app does one POST /jobs per template;
//   • PDFs are generated on the fly when opened (like scheme) from the EXISTING .hbs templates;
//   • "Download All" zips one stage's documents on the server;
//   • the jobs also appear in the Job Registrar list (JobsService.findAll merges them in).
// Jobs created here are marked templateSnapshot.source = 'crm-company'.
// ═══════════════════════════════════════════════════════════════════════════
import { BadRequestException, ForbiddenException, Injectable, Logger, NotFoundException } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import * as fs from 'fs';
import * as os from 'os';
import * as path from 'path';
import { execFile } from 'child_process';
import { promisify } from 'util';
import dayjs from 'dayjs';
const runFile = promisify(execFile);
// eslint-disable-next-line @typescript-eslint/no-var-requires
const archiverLib = require('archiver');
/** archiver v8 exports classes (ZipArchive); older versions export a factory function. 2026-11-10 fix. */
const archiver = (_fmt: 'zip', opts: any) =>
  archiverLib.ZipArchive ? new archiverLib.ZipArchive(opts) : archiverLib('zip', opts);

import { PdfService } from '../pdf/pdf.service';
import { templates as TEMPLATE_REGISTRY } from '../pdf/template-registry';
import { CompanyDocumentsService, PackFields } from './company-documents.service';
import { PACK_FORMS, PackForm, PackStage, defaultFormKeys, jobPrefixFor, packStageFor, stdKeysFor, JOB_INITIAL_NUMBERS } from './pack.rules';
import { wordify } from './word-layout';
import { isInitialOnlyUser } from './initial-only';
// 2026-11-11 — editable auditee details, duplicate companies, Transfer
import { AUDITEE_FIELDS, Auditee, fromAuditRequest, fromCompany, hasAny, isPlaceholder, merge, sanitize } from './auditee';
import { ASSIGN_TABLE, assignedRowIds, assignmentsReady, forgetAssignmentsTable } from './job-assignments';
import { duplicatesOf } from '../companies/services/duplicate-index';

export const CRM_JOB_SOURCE = 'crm-company';

const ADMIN_IDS = [1, 8, 29];
const ALLOWED_ROLES = ['admin', 'superadmin', 'super-admin', 'scheme', 'coordinator'];
/** 2026-11-10 — modules.slug of the dynamic Job Documents permissions */
export const JOB_DOCS_MODULE = 'job-documents';

interface TemplateDef {
  id: number;
  name: string;
  stage_name: 'Stage 1' | 'Stage 2';
  stage: PackStage;
  file_path: string;
  prints: string[]; // job fields the template prints
}

export interface CreateJobsBody {
  audit_schedule_row_id: number;
  stage?: string; // "Stage 1" | "Stage 2" (current stage label, as in scheme)
  templateIds: number[];
  leadAuditorId?: number | null;
  standardIds?: number[];
  date?: string;
  docReview?: string | null;
  numEmployees?: number | string | null;
  naceEacCodes?: string | null;
  md?: string | null;
  mdRisk?: string | null;
  scheduleSlot?: string | null;
  technicalExpert?: string | null;
  expectedSurveillanceDate?: string | null;
  expectedIADate?: string | null;
  expectedMRMDate?: string | null;
  expectedInitialInquiryDate?: string | null;
  expectedInitial_Stage1_Date?: string | null;
  prepareDate?: string | null;
  approvedDate?: string | null;
  /** 2026-11-11 — edited auditee details printed on the documents */
  auditee?: Auditee | null;
  /** 2026-11-11 — also save the edited auditee details to the company record */
  updateCompany?: boolean;
}

const PRINT_FIELDS = ['approvedDate', 'prepareDate', 'docReview', 'numEmployees', 'naceEacCodes', 'md', 'mdRisk', 'technicalExpert'];

@Injectable()
export class CompanyJobsService {
  private readonly logger = new Logger('CompanyJobsService');
  private tplCache: { at: number; list: TemplateDef[] } | null = null;

  constructor(
    @InjectDataSource('scheme_dbs') private readonly db: DataSource,
    @InjectDataSource('certification_db') private readonly cert: DataSource,
    private readonly pdf: PdfService,
    private readonly docs: CompanyDocumentsService,
  ) {}

  // ───────────── access ─────────────
  // 2026-11-10 — DYNAMIC access from the database (module "Job Documents", slug job-documents):
  //   view          → see jobs & documents (list, View PDF, Word, Download All)
  //   create_stage1 → create Stage 1 jobs / documents
  //   create_stage2 → create Stage 2 jobs / documents
  // Given per user (Users → Permissions) or through a role (e.g. the "Job Documents" role).
  // The original hard-coded rule (legacyCanUse: admin ids + admin/scheme/coordinator roles) is kept
  // and still gives full access, so nobody loses what they had.
  async access(userId: number): Promise<{ view: boolean; stages: ('Stage 1' | 'Stage 2')[]; source: 'legacy' | 'permissions' | 'none' }> {
    if (await this.legacyCanUse(userId)) return { view: true, stages: ['Stage 1', 'Stage 2'], source: 'legacy' };
    let actions: string[] = [];
    try {
      const rows: { action: string }[] = await this.db.query(
        `SELECT p.action FROM permissions p JOIN modules m ON m.id = p.module_id
           JOIN user_permissions up ON up.permission_id = p.id
          WHERE m.slug = ? AND m.is_active = 1 AND up.user_id = ?
         UNION
         SELECT p.action FROM permissions p JOIN modules m ON m.id = p.module_id
           JOIN role_permissions rp ON rp.permission_id = p.id
           JOIN user_roles ur ON ur.role_id = rp.role_id
           JOIN roles r ON r.id = ur.role_id
          WHERE m.slug = ? AND m.is_active = 1 AND r.is_active = 1 AND ur.user_id = ?`,
        [JOB_DOCS_MODULE, userId, JOB_DOCS_MODULE, userId],
      );
      actions = rows.map((r) => String(r.action).toLowerCase());
    } catch (e: any) {
      this.logger.warn(`[ACCESS] permission lookup failed for user ${userId}: ${e?.message}`);
    }
    const stages: ('Stage 1' | 'Stage 2')[] = [];
    if (actions.includes('create_stage1')) stages.push('Stage 1');
    if (actions.includes('create_stage2')) stages.push('Stage 2');
    const view = actions.length > 0; // any Job Documents permission lets the user see the documents
    return { view, stages, source: view ? 'permissions' : 'none' };
  }

  async canUse(userId: number): Promise<boolean> {
    return (await this.access(userId)).view;
  }

  /** original rule — kept unchanged */
  async legacyCanUse(userId: number): Promise<boolean> {
    if (ADMIN_IDS.includes(Number(userId))) return true;
    try {
      const rows: { r: string }[] = await this.db.query(
        `SELECT LOWER(r.name) AS r FROM user_roles ur JOIN roles r ON r.id = ur.role_id WHERE ur.user_id = ?`,
        [userId],
      );
      return rows.some((x) => ALLOWED_ROLES.includes(x.r));
    } catch {
      return false;
    }
  }
  private async assert(userId: number) {
    if (!(await this.canUse(userId))) throw new ForbiddenException('You do not have access to Job Documents. Ask an admin to give you the "Job Documents" role or permission.');
  }

  // ───────────── templates (scheme_dbs.templates with an existing .hbs file) ─────────────
  private async templateList(): Promise<TemplateDef[]> {
    if (this.tplCache && Date.now() - this.tplCache.at < 5 * 60_000) return this.tplCache.list;
    const rows: any[] = await this.db.query(
      `SELECT id, name, stage_name, file_path FROM templates
        WHERE template_type = 'DOCUMENT' AND is_active = 1 AND stage_name IN ('Stage 1','Stage 2')
        ORDER BY stage_name, id`,
    );
    const list: TemplateDef[] = [];
    for (const r of rows) {
      const stage: PackStage = r.stage_name === 'Stage 2' ? 'stage2' : 'stage1';
      const p = (TEMPLATE_REGISTRY as any)[stage]?.[r.file_path];
      if (!p || !fs.existsSync(p)) continue;
      let txt = '';
      try {
        txt = fs.readFileSync(p, 'utf8');
      } catch {
        /* ignore */
      }
      const prints = PRINT_FIELDS.filter((f) => new RegExp(`\\b${f}\\b`).test(txt));
      list.push({ id: Number(r.id), name: String(r.name).trim(), stage_name: r.stage_name, stage, file_path: r.file_path, prints });
    }
    this.tplCache = { at: Date.now(), list };
    return list;
  }

  /** Template ids recommended for this stage + standards (same rules as the pack catalogue). */
  private recommendedIds(stage: PackStage, stdNames: string[]): number[] {
    const keys = defaultFormKeys(stage, stdKeysFor(stdNames));
    return PACK_FORMS.filter((f) => f.stage === stage && keys.includes(f.key) && f.templateId).map((f) => f.templateId!) as number[];
  }

  // ───────────── form data ─────────────
  async formData(companyId: number, userId: number) {
    await this.assert(userId);
    const acc = await this.access(userId); // 2026-11-10: only the stages this user may create
    if (!acc.stages.length) throw new ForbiddenException('You can view job documents but cannot create jobs (no Stage 1 / Stage 2 permission).');
    const base = await this.docs.listAudits(companyId, userId); // company, audits with prefill
    // 2026-11-11 — Job Documents-only users: only INITIAL audits from the Audit Schedule
    const initialOnly = await isInitialOnlyUser(this.db, userId);
    if (initialOnly) {
      base.audits = base.audits.filter((a: any) => String(a.audit_type).toUpperCase() === 'INITIAL');
      if (!base.audits.length) throw new ForbiddenException('This company has no Initial audit scheduled — job documents can only be created for Initial audits.');
      // 2026-11-11 — audits transferred to this user (Companies → Transfer): only those
      const assigned = await assignedRowIds(this.db, userId);
      if (assigned) {
        base.audits = base.audits.filter((a: any) => assigned.has(Number(a.id)));
        if (!base.audits.length) throw new ForbiddenException('No audit of this company has been transferred to you.');
      }
    }
    const auditee = await this.auditeeSources(companyId, base.audits.map((a: any) => Number(a.id))); // 2026-11-11
    const templates = (await this.templateList()).filter((t) => acc.stages.includes(t.stage_name));
    const users: any[] = await this.db.query(
      `SELECT u.id, TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS name,
              MAX(LOWER(r.name) = 'auditor') AS is_auditor
         FROM users u LEFT JOIN user_roles ur ON ur.user_id = u.id LEFT JOIN roles r ON r.id = ur.role_id
        GROUP BY u.id ORDER BY is_auditor DESC, name`,
    );
    const standards: any[] = await this.db.query(`SELECT id, name, title FROM standards WHERE deleted_at IS NULL ORDER BY name`);
    return {
      company: base.company,
      audits: base.audits.map((a: any) => ({
        ...a,
        stage_label: a.default_stage === 'stage1' ? 'Stage 1' : 'Stage 2',
        auditee: auditee.perAudit.get(Number(a.id))?.prefill ?? auditee.companyPrefill, // 2026-11-11
        auditee_sources: auditee.perAudit.get(Number(a.id))?.sources ?? auditee.common, // 2026-11-11
        recommended_template_ids: {
          'Stage 1': this.recommendedIds('stage1', a.standards.map((s: any) => s.name)),
          'Stage 2': this.recommendedIds('stage2', a.standards.map((s: any) => s.name)),
        },
      })),
      templates: templates.map((t) => ({ id: t.id, name: t.name, stage_name: t.stage_name, prints: t.prints })),
      allowed_stages: acc.stages,
      initial_only: initialOnly,
      duplicates: auditee.duplicates, // 2026-11-11: look-alike companies (other spellings)
      can_update_company: true,
      users: users.map((u) => ({ id: u.id, name: u.name || `User #${u.id}`, is_auditor: !!Number(u.is_auditor) })),
      standards,
    };
  }

  // ───────────── create: one job per template ─────────────
  async createJobs(companyId: number, body: CreateJobsBody, userId: number) {
    await this.assert(userId);
    const [company] = await this.db.query(`SELECT * FROM companies WHERE id = ?`, [companyId]);
    if (!company) throw new NotFoundException('Company not found');
    const rowId = Number(body.audit_schedule_row_id);
    const [row] = await this.db.query(`SELECT id, company_id, lead_auditor_id, audit_type, status FROM audit_schedule_rows WHERE id = ?`, [rowId]);
    if (!row || Number(row.company_id) !== Number(companyId)) throw new BadRequestException('Select an audit of this company.');
    // 2026-11-11 — Job Documents-only users may create jobs from INITIAL audits only
    if ((await isInitialOnlyUser(this.db, userId)) && (String(row.audit_type).toUpperCase() !== 'INITIAL' || row.status === 'CANCELLED')) {
      throw new ForbiddenException('You can create job documents only for Initial audits.');
    }
    // 2026-11-11 — when audits were transferred to this user, only those audits
    if (await isInitialOnlyUser(this.db, userId)) {
      const assigned = await assignedRowIds(this.db, userId);
      if (assigned && !assigned.has(rowId)) throw new ForbiddenException('This audit has not been transferred to you.');
    }

    const all = await this.templateList();
    const ids = [...new Set((body.templateIds || []).map(Number))];
    const chosen = all.filter((t) => ids.includes(t.id));
    if (!chosen.length) throw new BadRequestException('Select at least one template.');
    // 2026-11-10: dynamic Stage 1 / Stage 2 permission
    const accC = await this.access(userId);
    const blocked = chosen.filter((t) => !accC.stages.includes(t.stage_name));
    if (blocked.length) {
      throw new ForbiddenException(`You do not have permission to create ${[...new Set(blocked.map((t) => t.stage_name))].join(' / ')} documents.`);
    }

    const needApproval = chosen.filter((t) => t.prints.includes('approvedDate'));
    const d = (v: any) => (v && /^\d{4}-\d{2}-\d{2}/.test(String(v)) ? String(v).slice(0, 10) : null);
    if (needApproval.length && !d(body.approvedDate)) {
      throw new BadRequestException(`Document Approval Date is needed for: ${needApproval.map((t) => t.name).join(', ')}.`);
    }
    const md = body.md === undefined || body.md === null || String(body.md).trim() === '' ? null : String(body.md).trim();
    if (md && !/^(\d+(\.\d+)?|\.\d+)$/.test(md)) throw new BadRequestException('MD must be a valid number (e.g. .5, 1, 2).');
    const emp = body.numEmployees === '' || body.numEmployees === null || body.numEmployees === undefined ? null : Number(body.numEmployees);
    if (emp !== null && (!Number.isInteger(emp) || emp < 0)) throw new BadRequestException('No. of employees must be a whole number.');
    const risk = ['Low', 'Medium', 'High'].includes(String(body.mdRisk)) ? String(body.mdRisk) : null;
    if (!risk) throw new BadRequestException('Select MD Risk.');
    const date = d(body.date);
    if (!date) throw new BadRequestException('Audit date is required.');

    let stdIds = (body.standardIds || []).map(Number).filter((x) => x > 0);
    if (!stdIds.length) {
      stdIds = (await this.db.query(`SELECT standard_id FROM audit_schedule_row_standards WHERE row_id = ?`, [rowId])).map((x: any) => Number(x.standard_id));
    }
    const standards: any[] = stdIds.length ? await this.db.query(`SELECT id, name, title FROM standards WHERE id IN (?)`, [stdIds]) : [];
    if (!standards.length) throw new BadRequestException('Select at least one standard.');
    const leadId = Number(body.leadAuditorId || row.lead_auditor_id) || null;
    if (!leadId) throw new BadRequestException('Select a lead auditor.');

    const companySnapshot: any = {
      companyName: company.name,
      companyAddress: company.address,
      companyScope: company.scope_of_work,
      companycity: company.city,
      companycontact_person: company.contact_person,
      companydesignation: company.designation,
      companyemail: company.email,
      companymobile: company.mobile,
      companytelephone: company.telephone,
      companyFax: company.fax,
      companyvalidity: company.validity,
      companyaccreditation: company.accreditation,
      companycertification_body: company.accreditation,
      companyRefrence: company.reference_number,
    };
    // 2026-11-11 — edited auditee details: stored on every job and printed on its documents
    const auditee = sanitize(body.auditee);
    if (auditee && hasAny(auditee)) {
      companySnapshot.auditeeEdited = true;
      companySnapshot.auditee = auditee;
      if (auditee.contact_person) companySnapshot.companycontact_person = auditee.contact_person;
      if (auditee.designation) companySnapshot.companydesignation = auditee.designation;
      if (auditee.email) companySnapshot.companyemail = auditee.email;
      if (auditee.mobile) companySnapshot.companymobile = auditee.mobile;
      if (auditee.telephone) companySnapshot.companytelephone = auditee.telephone;
      if (auditee.address) companySnapshot.companyAddress = auditee.address;
      if (auditee.scope_of_work) companySnapshot.companyScope = auditee.scope_of_work;
    }
    const jobAddress = String(companySnapshot.companyAddress ?? '').slice(0, 255) || null;
    const jobScope = companySnapshot.companyScope ?? null;
    const slot = ['MORNING', 'AFTERNOON', 'FULL'].includes(String(body.scheduleSlot)) ? String(body.scheduleSlot) : 'FULL';

    const created: { id: number; jobCode: string; template: string; stage: string }[] = [];
    const qr = this.db.createQueryRunner();
    await qr.connect();
    let locked = false;
    try {
      const r = await qr.query(`SELECT GET_LOCK('crm_job_code', 20) AS ok`);
      if (Number(r?.[0]?.ok) !== 1) throw new BadRequestException('Job numbers are busy, please try again.');
      locked = true;
      for (const t of chosen) {
        const contents: any[] = await qr.query(
          `SELECT id, title, content, functional_area, startTime, endTime FROM template_contents WHERE template_id = ?`,
          [t.id],
        );
        const jobCode = await this.nextJobCode(standards, qr);
        const snapshot = {
          id: t.id,
          name: t.name,
          stageName: t.stage_name,
          filePath: t.file_path,
          isActive: true,
          contents,
          source: CRM_JOB_SOURCE,
          audit_schedule_row_id: rowId,
          technicalExpert: body.technicalExpert ? String(body.technicalExpert).slice(0, 200) : null,
          created_by: userId,
        };
        const ins: any = await qr.query(
          `INSERT INTO jobs (jobCode, stage, date, numEmployees, naceEacCodes, mdRisk, company_id, lead_auditor_id, template_id, md,
                             docReview, templateSnapshot, isPdfGenerated, companyName, companyAddress, companyScope, companySnapshot,
                             scheduleSlot, expectedSurveillanceDate, expectedIADate, expectedMRMDate, expectedInitialInquiryDate,
                             expectedInitial_Stage1_Date, prepareDate, approvedDate, created_at, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(6), NOW(6))`,
          [
            jobCode,
            t.stage_name,
            date,
            emp,
            body.naceEacCodes ? String(body.naceEacCodes).slice(0, 500) : null,
            risk,
            companyId,
            leadId,
            t.id,
            md,
            d(body.docReview) ?? date,
            JSON.stringify(snapshot),
            company.name,
            jobAddress, // 2026-11-11: edited auditee address (was company.address)
            jobScope, // 2026-11-11: edited scope (was company.scope_of_work)
            JSON.stringify(companySnapshot),
            slot,
            d(body.expectedSurveillanceDate),
            d(body.expectedIADate),
            d(body.expectedMRMDate),
            d(body.expectedInitialInquiryDate),
            d(body.expectedInitial_Stage1_Date),
            d(body.prepareDate),
            d(body.approvedDate),
          ],
        );
        const id = Number(ins.insertId);
        for (const s of standards) await qr.query(`INSERT IGNORE INTO job_standards (job_id, standard_id) VALUES (?, ?)`, [id, s.id]);
        created.push({ id, jobCode, template: t.name, stage: t.stage_name });
      }
    } finally {
      if (locked) {
        try {
          await qr.query(`SELECT RELEASE_LOCK('crm_job_code')`);
        } catch {
          /* ignore */
        }
      }
      await qr.release();
    }
    this.logger.log(`[JOBS] company ${companyId}: ${created.length} job(s) created by user ${userId}`);
    // 2026-11-11 — optionally correct the company record with the edited auditee details
    let companyUpdated: string[] = [];
    if (body.updateCompany && auditee && hasAny(auditee)) {
      try {
        companyUpdated = await this.saveAuditeeToCompany(company, auditee, userId);
      } catch (e: any) {
        this.logger.warn(`[JOBS] company ${companyId}: auditee details not saved to company: ${e?.message}`);
      }
    }
    return { created: created.length, jobs: created, company_updated: companyUpdated };
  }

  /** Same format as JobsService (PREFIX:MM-N, month ≥ 10); N continues from the highest in BOTH job tables. */
  private async nextJobCode(standards: any[], qr: any): Promise<string> {
    let month = new Date().getMonth() + 1;
    if (month < 10) month = 10;
    const mm = String(month).padStart(2, '0');
    const prefixes = [...new Set(standards.map((s) => jobPrefixFor(s.name)).filter(Boolean))] as string[];
    if (!prefixes.length) prefixes.push('JOB');
    const out: string[] = [];
    for (const prefix of prefixes) {
      let max = 0;
      const scan = async (run: (sql: string, p: any[]) => Promise<any[]>) => {
        try {
          const rows = await run(`SELECT jobCode FROM jobs WHERE jobCode LIKE ? ORDER BY id DESC LIMIT 500`, [`%${prefix}:%`]);
          for (const r of rows)
            for (const c of String(r.jobCode || '').split(',').map((x: string) => x.trim())) {
              if (!c.startsWith(`${prefix}:`)) continue;
              const n = parseInt(c.split('-')[1], 10);
              if (!isNaN(n) && n > max) max = n;
            }
        } catch (e: any) {
          this.logger.warn(`[JOBS] number scan failed: ${e?.message}`);
        }
      };
      await scan((sql, p) => qr.query(sql, p));
      await scan((sql, p) => this.cert.query(sql, p));
      out.push(`${prefix}:${mm}-${max ? max + 1 : JOB_INITIAL_NUMBERS[prefix] ?? 1000}`);
    }
    return out.join(', ');
  }

  // ───────────── list (company Documents tab) ─────────────
  async listForCompany(companyId: number, userId: number) {
    await this.assert(userId);
    const rows: any[] = await this.db.query(
      `SELECT j.id, j.jobCode, j.stage, DATE_FORMAT(j.date, '%Y-%m-%d') AS date, j.template_id, j.created_at,
              j.templateSnapshot, t.name AS template_name,
              TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS lead_auditor
         FROM jobs j
         LEFT JOIN templates t ON t.id = j.template_id
         LEFT JOIN users u ON u.id = j.lead_auditor_id
        WHERE j.company_id = ? AND JSON_UNQUOTE(JSON_EXTRACT(j.templateSnapshot, '$.source')) = ?
        ORDER BY j.created_at DESC, j.id DESC`,
      [companyId, CRM_JOB_SOURCE],
    );
    const ids = rows.map((r) => r.id);
    const stds: any[] = ids.length
      ? await this.db.query(`SELECT js.job_id, s.name FROM job_standards js JOIN standards s ON s.id = js.standard_id WHERE js.job_id IN (?)`, [ids])
      : [];
    return rows.map((r) => {
      const snap = this.json(r.templateSnapshot);
      return {
        id: r.id,
        jobCodes: String(r.jobCode || '').split(',').map((s) => s.trim()).filter(Boolean),
        stage: r.stage,
        date: r.date,
        template: r.template_name || snap?.name || '—',
        standards: stds.filter((s) => s.job_id === r.id).map((s) => s.name),
        lead_auditor: r.lead_auditor || null,
        audit_schedule_row_id: snap?.audit_schedule_row_id ?? null,
        created_at: r.created_at,
        pdf_url: `/api/company-documents/jobs/${r.id}/pdf`,
      };
    });
  }

  // ───────────── PDF on the fly ─────────────
  async jobPdf(jobId: number, userId: number): Promise<{ buf: Buffer; name: string }> {
    await this.assert(userId);
    const [job] = await this.db.query(`SELECT * FROM jobs WHERE id = ?`, [jobId]);
    if (!job) throw new NotFoundException('Job not found');
    return this.renderJob(job);
  }

  private async renderJob(job: any): Promise<{ buf: Buffer; name: string }> {
    const { html, name } = await this.renderJobHtml(job);
    const buf = await this.pdf.generatePdf(html);
    return { buf, name: `${name}.pdf` };
  }

  /** 2026-11-10: Word copy — the same filled HTML converted by LibreOffice (editable; layout close to the PDF). */
  private async renderJobDocx(job: any): Promise<{ buf: Buffer; name: string }> {
    const { html, name } = await this.renderJobHtml(job);
    const buf = await this.htmlToDocx(html);
    return { buf, name: `${name}.docx` };
  }

  async jobDocx(jobId: number, userId: number): Promise<{ buf: Buffer; name: string }> {
    await this.assert(userId);
    const [job] = await this.db.query(`SELECT * FROM jobs WHERE id = ?`, [jobId]);
    if (!job) throw new NotFoundException('Job not found');
    return this.renderJobDocx(job);
  }

  private wordQueue: Promise<any> = Promise.resolve();
  /** LibreOffice is run one file at a time (its profile cannot be shared by parallel runs). */
  private htmlToDocx(html: string): Promise<Buffer> {
    const job = this.wordQueue.then(async () => {
      const dir = await fs.promises.mkdtemp(path.join(os.tmpdir(), 'jobdoc-'));
      try {
        const src = path.join(dir, 'document.html');
        // 2026-11-10: PDF-identical layout (styles measured in Chromium); old HTML clean-up kept as fallback
        let prepared: string;
        try {
          prepared = await this.wordLayoutHtml(html);
        } catch (e: any) {
          this.logger.warn(`[JOBS] Word layout pass failed, using simple conversion: ${e?.message}`);
          prepared = this.wordFriendlyHtml(html);
        }
        await fs.promises.writeFile(src, await this.embedImages(prepared), 'utf8');
        await runFile(
          process.env.SOFFICE_PATH || 'soffice',
          [
            `-env:UserInstallation=file://${path.join(os.tmpdir(), 'lo-company-jobs')}`,
            '--headless',
            '--infilter=HTML (StarWriter)',
            '--convert-to',
            'docx:MS Word 2007 XML',
            '--outdir',
            dir,
            src,
          ],
          { timeout: 120_000 },
        );
        return await fs.promises.readFile(path.join(dir, 'document.docx'));
      } finally {
        await fs.promises.rm(dir, { recursive: true, force: true });
      }
    });
    this.wordQueue = job.catch(() => undefined);
    return job;
  }

  /**
   * 2026-11-10: open the filled form in the same Chromium that prints the PDF (same 250 mm page width),
   * then let word-layout.ts write every element's final look inline so Word shows the PDF design.
   */
  private async wordLayoutHtml(html: string): Promise<string> {
    const pdf: any = this.pdf;
    const page = await pdf.acquirePage();
    try {
      await page.setViewport({ width: 945, height: 1160 });
      await page.setContent(html, { waitUntil: 'load', timeout: 20_000 });
      await page.addStyleTag({ content: "body { font-family: 'Source Sans Pro', Arial, sans-serif; font-size: 10pt; }" });
      await page.evaluate(() => Promise.all(Array.from(document.images).map((i) => (i.complete ? null : new Promise((r) => { i.onload = i.onerror = r; })))));
      const out: string = await page.evaluate(wordify);
      const css = '<style>@page{size:250mm 307mm;margin:6mm 8mm}</style>';
      return /<\/head>/i.test(out) ? out.replace(/<\/head>/i, `${css}</head>`) : css + out;
    } finally {
      pdf.releasePage(page);
    }
  }

  /** Images (logo, crest) are fetched and embedded, so the Word file shows them on any computer. */
  private imgCache = new Map<string, string>();
  private async embedImages(html: string): Promise<string> {
    const srcs = [...new Set([...html.matchAll(/<img[^>]*\bsrc=['"](https?:\/\/[^'"]+)['"]/gi)].map((m) => m[1]))];
    for (const u of srcs) {
      try {
        let data = this.imgCache.get(u);
        if (!data) {
          const r = await fetch(u, { signal: AbortSignal.timeout(8000) });
          if (!r.ok) continue;
          const type = r.headers.get('content-type') || 'image/png';
          data = `data:${type};base64,${Buffer.from(await r.arrayBuffer()).toString('base64')}`;
          if (this.imgCache.size < 50) this.imgCache.set(u, data);
        }
        html = html.split(u).join(data);
      } catch {
        /* keep the link if the image cannot be fetched */
      }
    }
    return html;
  }

  /** Small fixes so Writer lays the forms out like the PDF (explicit table widths, ticks as ☑ / ☐, logo size). */
  private wordFriendlyHtml(html: string): string {
    let s = html;
    s = s.replace(/<table(?![^>]*\bwidth=)/gi, '<table width="100%"');
    s = s.replace(/white-space\s*:\s*nowrap\s*;?/gi, '');
    s = s.replace(/max-width\s*:\s*\d+px\s*;?/gi, '');
    s = s.replace(/<input[^>]*type=['"]checkbox['"][^>]*\bchecked\b[^>]*>/gi, '☑ ');
    s = s.replace(/<input[^>]*type=['"]checkbox['"][^>]*>/gi, '☐ ');
    s = s.replace(/<img([^>]*class=['"][^'"]*logo[^'"]*['"][^>]*)>/gi, (m, attrs) =>
      /\bwidth=/.test(attrs) ? m : `<img${attrs} width="80" height="80">`,
    );
    const css =
      '<style>@page{size:250mm 307mm;margin:8mm} body{padding:0!important;font-size:9pt!important} ' +
      '.doc-table{font-size:8.5pt!important} .quality-registrar-text{font-size:18pt!important}</style>';
    return /<\/head>/i.test(s) ? s.replace(/<\/head>/i, `${css}</head>`) : css + s;
  }

  private async renderJobHtml(job: any): Promise<{ html: string; name: string }> {
    const snap = this.json(job.templateSnapshot) || {};
    if (snap.source !== CRM_JOB_SOURCE) throw new BadRequestException('Not a CRM company job.');
    const tpl = (await this.templateList()).find((t) => t.id === Number(job.template_id));
    if (!tpl) throw new NotFoundException('Template file not found for this job.');
    const stds: any[] = await this.db.query(`SELECT standard_id FROM job_standards WHERE job_id = ?`, [job.id]);
    const ymd = (v: any) => (v ? dayjs(v).format('YYYY-MM-DD') : null);
    const fields: PackFields = {
      date: ymd(job.date),
      docReview: ymd(job.docReview),
      prepareDate: ymd(job.prepareDate),
      approvedDate: ymd(job.approvedDate),
      numEmployees: job.numEmployees,
      naceEacCodes: job.naceEacCodes,
      md: job.md,
      mdRisk: job.mdRisk,
      technicalExpert: snap.technicalExpert ?? null,
      scheduleSlot: job.scheduleSlot,
      leadAuditorId: job.lead_auditor_id,
      standardIds: stds.map((s) => Number(s.standard_id)),
      expectedSurveillanceDate: ymd(job.expectedSurveillanceDate),
      expectedIADate: ymd(job.expectedIADate),
      expectedMRMDate: ymd(job.expectedMRMDate),
      expectedInitialInquiryDate: ymd(job.expectedInitialInquiryDate),
      expectedInitial_Stage1_Date: ymd(job.expectedInitial_Stage1_Date),
    };
    const ctx: any = await (this.docs as any).buildContext(job.company_id, snap.audit_schedule_row_id, tpl.stage, fields);
    // 2026-11-11 — auditee details edited in Create Job win over the (live) company record
    const cs = this.json(job.companySnapshot);
    if (cs?.auditeeEdited && cs.auditee) {
      for (const f of AUDITEE_FIELDS) {
        const v = cs.auditee[f];
        if (v) {
          ctx.company[f] = v;
          if (ctx.job?.companySnapshot) ctx.job.companySnapshot[this.snapKey(f)] = v;
        }
      }
    }
    ctx.job.id = job.id;
    ctx.job.jobCode = job.jobCode;
    const def: PackForm = { key: `t${tpl.id}`, stage: tpl.stage, registryKey: tpl.file_path, templateId: tpl.id, title: tpl.name, fields: [] };
    const data = await (this.docs as any).templateData(ctx, def, fields);
    const html = this.pdf.renderTemplate(tpl.stage, tpl.file_path, data);
    const name = `${tpl.name} - ${String(job.jobCode).split(',')[0].trim()}`.replace(/[\\/:*?"<>|]+/g, '-');
    return { html, name };
  }

  // ───────────── Download All (one stage) ─────────────
  async zipStage(companyId: number, stage: string, userId: number, res: any, format: 'pdf' | 'docx' = 'pdf') {
    await this.assert(userId);
    const st = stage === 'Stage 2' ? 'Stage 2' : stage === 'Stage 1' ? 'Stage 1' : null;
    if (!st) throw new BadRequestException('Select a stage.');
    const jobs: any[] = await this.db.query(
      `SELECT * FROM jobs WHERE company_id = ? AND stage = ? AND JSON_UNQUOTE(JSON_EXTRACT(templateSnapshot, '$.source')) = ? ORDER BY id`,
      [companyId, st, CRM_JOB_SOURCE],
    );
    if (!jobs.length) throw new NotFoundException(`No documents for ${st}.`);
    const [company] = await this.db.query(`SELECT name FROM companies WHERE id = ?`, [companyId]);
    const zipName = `${String(company?.name || 'Company').replace(/[^A-Za-z0-9]+/g, '_').slice(0, 60)}_${st.replace(' ', '_')}_${format === 'docx' ? 'Word' : 'PDF'}_Documents.zip`;
    // 2026-11-10: build the whole zip in memory first, then send it (a failure now returns a clear JSON error
    // instead of a half-sent zip / "Internal server error")
    const { PassThrough } = require('stream');
    const sink = new PassThrough();
    const chunks: Buffer[] = [];
    sink.on('data', (c: Buffer) => chunks.push(c));
    const done = new Promise<void>((resolve, reject) => {
      sink.on('end', () => resolve());
      sink.on('error', reject);
    });
    const archive = archiver('zip', { zlib: { level: 9 } });
    archive.on('error', (e: any) => sink.destroy(e));
    archive.pipe(sink);
    const used = new Set<string>();
    for (const j of jobs) {
      try {
        const { buf, name } = format === 'docx' ? await this.renderJobDocx(j) : await this.renderJob(j);
        const ext = format === 'docx' ? '.docx' : '.pdf';
        let n = name;
        let k = 2;
        while (used.has(n)) n = name.replace(ext, ` (${k++})${ext}`);
        used.add(n);
        archive.append(buf, { name: n });
      } catch (e: any) {
        archive.append(`Could not generate job ${j.id} (${j.jobCode}): ${e?.message}`, { name: `ERROR - job ${j.id}.txt` });
      }
    }
    await archive.finalize();
    await done;
    const zip = Buffer.concat(chunks);
    res.setHeader('Content-Type', 'application/zip');
    res.setHeader('Content-Disposition', `attachment; filename="${zipName}"`);
    res.setHeader('Content-Length', String(zip.length));
    res.end(zip);
  }

  // ═══════════════ 2026-11-11 — auditee details (Create Job) ═══════════════
  private snapKey(f: string): string {
    return (
      {
        contact_person: 'companycontact_person',
        designation: 'companydesignation',
        mobile: 'companymobile',
        telephone: 'companytelephone',
        email: 'companyemail',
        address: 'companyAddress',
        scope_of_work: 'companyScope',
      } as Record<string, string>
    )[f] ?? f;
  }

  /**
   * Prefill + "copy from" sources for the auditee card, per audit:
   *   1. the Audit Request of that audit (auditee name / contact / email / designation / scope)
   *   2. the company record
   *   3. the company's latest other audit request
   *   4. look-alike (duplicate) companies and their latest audit request
   * Placeholders (Unknown, N/A, 0000000000, unknown@example.com…) count as empty.
   */
  private async auditeeSources(companyId: number, rowIds: number[]) {
    type Src = { key: string; label: string; values: Auditee };
    const [company] = await this.db.query(`SELECT * FROM companies WHERE id = ?`, [companyId]);
    const comp = fromCompany(company);
    const reqRows: any[] = rowIds.length
      ? await this.db.query(
          `SELECT r.id AS row_id, ar.* FROM audit_schedule_rows r JOIN audit_requests ar ON ar.id = r.audit_request_id WHERE r.id IN (?)`,
          [rowIds],
        )
      : [];
    const [latestReq] = await this.db.query(`SELECT * FROM audit_requests WHERE company_id = ? ORDER BY id DESC LIMIT 1`, [companyId]);

    // look-alike companies
    let dupRows: any[] = [];
    const dupReq = new Map<number, any>();
    try {
      const ids = await duplicatesOf(this.db, companyId);
      if (ids.length) {
        dupRows = await this.db.query(`SELECT * FROM companies WHERE id IN (?) ORDER BY id`, [ids.slice(0, 10)]);
        const reqs: any[] = await this.db.query(
          `SELECT ar.* FROM audit_requests ar
             JOIN (SELECT company_id, MAX(id) AS id FROM audit_requests WHERE company_id IN (?) GROUP BY company_id) x ON x.id = ar.id`,
          [ids.slice(0, 10)],
        );
        for (const r of reqs) dupReq.set(Number(r.company_id), r);
      }
    } catch (e: any) {
      this.logger.warn(`[AUDITEE] duplicate lookup failed for company ${companyId}: ${e?.message}`);
    }
    const dupSources: Src[] = dupRows.map((d) => ({
      key: `dup-${d.id}`,
      label: `Duplicate company: ${String(d.name).trim()} (#${d.id})`,
      values: merge(fromAuditRequest(dupReq.get(Number(d.id))), fromCompany(d)),
    }));

    const common: Src[] = [{ key: 'company', label: 'Company record', values: comp }];
    if (latestReq) common.push({ key: `req-${latestReq.id}`, label: `Latest audit request #${latestReq.id}`, values: fromAuditRequest(latestReq) });
    common.push(...dupSources);
    const usable = (list: Src[]) => list.filter((x) => hasAny(x.values));

    const perAudit = new Map<number, { prefill: Auditee; sources: Src[] }>();
    for (const id of rowIds) {
      const ar = reqRows.find((r) => Number(r.row_id) === id);
      const own: Src[] = ar ? [{ key: `req-${ar.id}`, label: `Audit request #${ar.id} (this audit)`, values: fromAuditRequest(ar) }] : [];
      const sources = usable([...own, ...common.filter((c) => !own.some((o) => o.key === c.key))]);
      perAudit.set(id, { prefill: merge(...sources.map((x) => x.values)), sources });
    }
    return {
      perAudit,
      companyPrefill: merge(...usable(common).map((x) => x.values)),
      common: usable(common),
      duplicates: dupRows.map((d) => ({
        id: Number(d.id),
        name: d.name,
        company_code: d.company_code,
        contact_person: isPlaceholder(d.contact_person) ? null : d.contact_person,
        mobile: isPlaceholder(d.mobile) ? null : d.mobile,
        email: isPlaceholder(d.email) ? null : d.email,
      })),
    };
  }

  /** Write the edited auditee details back to the company (only real, changed values; logged in company_change_log). */
  private async saveAuditeeToCompany(company: any, a: Auditee, userId: number): Promise<string[]> {
    const limit: Record<string, number> = { contact_person: 100, designation: 255, mobile: 50, telephone: 20, email: 150, address: 65000, scope_of_work: 65000 };
    const changed: { f: string; old: any; val: string }[] = [];
    for (const f of AUDITEE_FIELDS) {
      const val = (a[f] || '').trim();
      if (!val || isPlaceholder(val) || val.length > limit[f]) continue;
      if (String(company[f] ?? '').trim() === val) continue;
      changed.push({ f, old: company[f] ?? null, val });
    }
    if (!changed.length) return [];
    await this.db.query(
      `UPDATE companies SET ${changed.map((c) => `\`${c.f}\` = ?`).join(', ')}, updated_at = NOW() WHERE id = ?`,
      [...changed.map((c) => c.val), company.id],
    );
    for (const c of changed) {
      try {
        await this.db.query(
          `INSERT INTO company_change_log (company_id, field_name, old_value, new_value, source_audit_id, changed_by_user_id, created_at)
           VALUES (?, ?, ?, ?, NULL, ?, NOW())`,
          [company.id, c.f, c.old === null ? null : String(c.old), c.val, userId || null],
        );
      } catch {
        /* the change itself is saved; the log is best effort */
      }
    }
    this.logger.log(`[AUDITEE] company ${company.id}: ${changed.map((c) => c.f).join(', ')} updated by user ${userId}`);
    return changed.map((c) => c.f);
  }

  // ═══════════════ 2026-11-11 — Transfer audits to a Job Documents user ═══════════════
  private async assertAdmin(userId: number) {
    if (!(await this.legacyCanUse(userId))) throw new ForbiddenException('Only admins / coordinators can transfer audits.');
  }

  /** Users that can receive transfers (Job Documents users first) + how many audits each has now. */
  async transferUsers(userId: number) {
    await this.assertAdmin(userId);
    const users: any[] = await this.db.query(
      `SELECT u.id, TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS name, u.email,
              EXISTS (SELECT 1 FROM user_permissions up JOIN permissions p ON p.id = up.permission_id JOIN modules m ON m.id = p.module_id
                       WHERE up.user_id = u.id AND m.slug = ?)
              OR EXISTS (SELECT 1 FROM user_roles ur JOIN role_permissions rp ON rp.role_id = ur.role_id
                           JOIN permissions p ON p.id = rp.permission_id JOIN modules m ON m.id = p.module_id
                          WHERE ur.user_id = u.id AND m.slug = ?) AS job_docs
         FROM users u
        WHERE COALESCE(u.isClient, 0) = 0 AND COALESCE(u.isActive, 1) = 1
        ORDER BY job_docs DESC, name`,
      [JOB_DOCS_MODULE, JOB_DOCS_MODULE],
    );
    const counts = new Map<number, number>();
    if (await assignmentsReady(this.db)) {
      const rows: any[] = await this.db.query(`SELECT user_id, COUNT(*) AS n FROM ${ASSIGN_TABLE} GROUP BY user_id`);
      for (const r of rows) counts.set(Number(r.user_id), Number(r.n));
    }
    return users.map((u) => ({ id: Number(u.id), name: u.name || u.email || `User #${u.id}`, email: u.email, job_docs: !!Number(u.job_docs), transferred: counts.get(Number(u.id)) ?? 0 }));
  }

  /** Audits of one month (Initial only by default) with their current transfer. */
  async transferPreview(userId: number, month: string, type: string) {
    await this.assertAdmin(userId);
    if (!/^\d{4}-\d{2}$/.test(String(month))) throw new BadRequestException('Pick a month (YYYY-MM).');
    const ready = await assignmentsReady(this.db);
    const onlyInitial = String(type || 'INITIAL').toUpperCase() !== 'ALL';
    const rows: any[] = await this.db.query(
      `SELECT r.id, r.audit_code, r.audit_type, r.audit_stage, r.status, r.company_id, c.name AS company_name, c.company_code,
              DATE_FORMAT(s.schedule_date, '%Y-%m-%d') AS schedule_date,
              TRIM(CONCAT(COALESCE(la.firstName,''),' ',COALESCE(la.lastName,''))) AS lead_auditor
              ${ready ? `, a.user_id AS assigned_to, TRIM(CONCAT(COALESCE(au.firstName,''),' ',COALESCE(au.lastName,''))) AS assigned_to_name, DATE_FORMAT(a.assigned_at, '%Y-%m-%d %H:%i') AS assigned_at` : ''}
         FROM audit_schedule_rows r
         JOIN audit_schedules s ON s.id = r.schedule_id
         LEFT JOIN companies c ON c.id = r.company_id
         LEFT JOIN users la ON la.id = r.lead_auditor_id
         ${ready ? `LEFT JOIN ${ASSIGN_TABLE} a ON a.row_id = r.id LEFT JOIN users au ON au.id = a.user_id` : ''}
        WHERE DATE_FORMAT(s.schedule_date, '%Y-%m') = ? AND r.status <> 'CANCELLED'
              ${onlyInitial ? `AND r.audit_type = 'INITIAL'` : ''}
        ORDER BY s.schedule_date, r.id`,
      [month],
    );
    const ids = rows.map((r) => r.id);
    const stds: any[] = ids.length
      ? await this.db.query(
          `SELECT rs.row_id, st.name FROM audit_schedule_row_standards rs JOIN standards st ON st.id = rs.standard_id WHERE rs.row_id IN (?)`,
          [ids],
        )
      : [];
    const docs: any[] = ids.length
      ? await this.db.query(
          `SELECT CAST(JSON_UNQUOTE(JSON_EXTRACT(templateSnapshot, '$.audit_schedule_row_id')) AS UNSIGNED) AS row_id, COUNT(*) AS n
             FROM jobs WHERE JSON_UNQUOTE(JSON_EXTRACT(templateSnapshot, '$.source')) = ?
              AND CAST(JSON_UNQUOTE(JSON_EXTRACT(templateSnapshot, '$.audit_schedule_row_id')) AS UNSIGNED) IN (?)
            GROUP BY row_id`,
          [CRM_JOB_SOURCE, ids],
        )
      : [];
    return {
      ready, // false → the job_document_assignments table is not created yet (run-002.js)
      month,
      audits: rows.map((r) => ({
        id: Number(r.id),
        audit_code: r.audit_code,
        audit_type: r.audit_type,
        audit_stage: r.audit_stage,
        status: r.status,
        company_id: Number(r.company_id),
        company_name: r.company_name,
        company_code: r.company_code,
        schedule_date: r.schedule_date,
        lead_auditor: r.lead_auditor || null,
        standards: stds.filter((x) => Number(x.row_id) === Number(r.id)).map((x) => x.name),
        documents: Number(docs.find((d) => Number(d.row_id) === Number(r.id))?.n ?? 0),
        assigned_to: r.assigned_to ? Number(r.assigned_to) : null,
        assigned_to_name: r.assigned_to_name || null,
        assigned_at: r.assigned_at || null,
      })),
    };
  }

  private async assertReady() {
    forgetAssignmentsTable();
    if (!(await assignmentsReady(this.db))) {
      throw new BadRequestException('Transfer is not set up yet: the server admin must run  node src/company-documents/migrations/run-002.js  in the CRM backend folder.');
    }
  }

  /** Transfer audits to one user (an audit already transferred to someone else moves to the new user). */
  async transfer(userId: number, body: { toUserId: number; rowIds: number[]; note?: string }) {
    await this.assertAdmin(userId);
    await this.assertReady();
    const to = Number(body?.toUserId);
    const ids = [...new Set((body?.rowIds || []).map(Number).filter((x) => x > 0))];
    if (!to) throw new BadRequestException('Choose the user to transfer to.');
    if (!ids.length) throw new BadRequestException('Select at least one audit.');
    const [u] = await this.db.query(`SELECT id FROM users WHERE id = ?`, [to]);
    if (!u) throw new BadRequestException('User not found.');
    const valid: any[] = await this.db.query(`SELECT id FROM audit_schedule_rows WHERE id IN (?)`, [ids]);
    if (!valid.length) throw new BadRequestException('Audits not found.');
    const note = body?.note ? String(body.note).slice(0, 255) : null;
    await this.db.query(
      `INSERT INTO ${ASSIGN_TABLE} (row_id, user_id, assigned_by, assigned_at, note) VALUES ?
       ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), assigned_by = VALUES(assigned_by), assigned_at = VALUES(assigned_at), note = VALUES(note)`,
      [valid.map((v) => [Number(v.id), to, userId || null, new Date(), note])],
    );
    this.logger.log(`[TRANSFER] ${valid.length} audit(s) → user ${to} by user ${userId}`);
    return { transferred: valid.length, to };
  }

  /** Take audits back (they become visible to the user again only through the normal Initial-audit rule). */
  async untransfer(userId: number, body: { rowIds: number[] }) {
    await this.assertAdmin(userId);
    await this.assertReady();
    const ids = [...new Set((body?.rowIds || []).map(Number).filter((x) => x > 0))];
    if (!ids.length) throw new BadRequestException('Select at least one audit.');
    const r: any = await this.db.query(`DELETE FROM ${ASSIGN_TABLE} WHERE row_id IN (?)`, [ids]);
    return { removed: Number(r?.affectedRows ?? 0) };
  }

  /** Current transfers grouped by user and audit month. */
  async transferSummary(userId: number) {
    await this.assertAdmin(userId);
    if (!(await assignmentsReady(this.db))) return { ready: false, groups: [] };
    const rows: any[] = await this.db.query(
      `SELECT a.user_id, TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS name,
              DATE_FORMAT(s.schedule_date, '%Y-%m') AS month, COUNT(*) AS audits, COUNT(DISTINCT r.company_id) AS companies,
              GROUP_CONCAT(r.id) AS row_ids
         FROM ${ASSIGN_TABLE} a
         JOIN audit_schedule_rows r ON r.id = a.row_id
         JOIN audit_schedules s ON s.id = r.schedule_id
         LEFT JOIN users u ON u.id = a.user_id
        GROUP BY a.user_id, name, month
        ORDER BY name, month`,
    );
    return {
      ready: true,
      groups: rows.map((r) => ({
        user_id: Number(r.user_id),
        name: r.name || `User #${r.user_id}`,
        month: r.month,
        audits: Number(r.audits),
        companies: Number(r.companies),
        row_ids: String(r.row_ids || '').split(',').map(Number).filter(Boolean),
      })),
    };
  }

  /** For the logged-in user: is the list limited to transferred audits? (banner in Companies) */
  async myTransfers(userId: number) {
    if (!(await isInitialOnlyUser(this.db, userId))) return { mode: 'all' as const, audits: 0, months: [] as string[] };
    const ids = await assignedRowIds(this.db, userId);
    if (!ids) return { mode: 'initial' as const, audits: 0, months: [] as string[] };
    const months: any[] = await this.db.query(
      `SELECT DISTINCT DATE_FORMAT(s.schedule_date, '%Y-%m') AS m FROM audit_schedule_rows r JOIN audit_schedules s ON s.id = r.schedule_id
        WHERE r.id IN (?) ORDER BY m`,
      [[...ids]],
    );
    return { mode: 'transferred' as const, audits: ids.size, months: months.map((x) => x.m) };
  }

  private json(v: any) {
    if (!v) return null;
    if (typeof v === 'object') return v;
    try {
      return JSON.parse(v);
    } catch {
      return null;
    }
  }

  /** used by JobsService (Job Registrar list) */
  static isCrmSnapshot(snap: any) {
    return snap && snap.source === CRM_JOB_SOURCE;
  }
}

// keep helper import used (stage mapping is shared with the pack feature)
void packStageFor;