// ═══════════════════════════════════════════════════════════════════════════
// Company documents (Job Registrar Stage 1 / Stage 2 packs) — 2026-11-10
//
// Companies → View company → 📁 Documents → Generate documents:
//   1. the user picks one of the company's audits (audit_schedule_rows) — company, stage,
//      standards, lead auditor, dates and schedule slot are filled from the audit;
//   2. the user types the job details the CRM does not store (NACE/EAC, MD, risk, employees,
//      review / prepare / approval dates, technical expert) and ticks the forms;
//   3. a job is saved in scheme_dbs.jobs (+ job_standards) with a job code from the same
//      sequence as the Job Registrar (max of certification_db.jobs and scheme_dbs.jobs);
//   4. every ticked form is rendered with the EXISTING .hbs templates + PdfService (Puppeteer),
//      one at a time, saved to storage/companyDocuments/<companyId>/<packId>/ and zipped.
// Packs are tracked in company_document_packs (migrations/001_company_document_packs.sql).
// ═══════════════════════════════════════════════════════════════════════════
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 path from 'path';
import dayjs from 'dayjs';
// 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 {
  JOB_INITIAL_NUMBERS,
  PACK_FORMS,
  PackField,
  PackForm,
  PackStage,
  defaultFormKeys,
  jobPrefixFor,
  packStageFor,
  stdKeysFor,
} from './pack.rules';

const ADMIN_IDS = [1, 8, 29];
const ALLOWED_ROLES = ['admin', 'superadmin', 'super-admin', 'scheme', 'coordinator'];
const STORAGE_ROOT = path.resolve(process.cwd(), 'storage', 'companyDocuments');

export interface PackFields {
  docReview?: string | null;
  prepareDate?: string | null;
  approvedDate?: string | null;
  numEmployees?: number | string | null;
  naceEacCodes?: string | null;
  md?: string | null;
  mdRisk?: 'Low' | 'Medium' | 'High' | string | null;
  technicalExpert?: string | null;
  // auto-filled from the audit, editable
  date?: string | null;
  scheduleSlot?: 'MORNING' | 'AFTERNOON' | 'FULL' | string | null;
  leadAuditorId?: number | null;
  standardIds?: number[];
  expectedSurveillanceDate?: string | null;
  expectedIADate?: string | null;
  expectedMRMDate?: string | null;
  expectedInitialInquiryDate?: string | null;
  expectedInitial_Stage1_Date?: string | null;
}

export interface GenerateBody {
  audit_schedule_row_id: number;
  stage: PackStage;
  forms: string[];
  fields: PackFields;
  pack_id?: number; // regenerate an existing pack (keeps its job code)
}

@Injectable()
export class CompanyDocumentsService {
  private readonly logger = new Logger('CompanyDocumentsService');
  private queue: Promise<void> = Promise.resolve();

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

  // ───────────────────────────── access ─────────────────────────────
  async canUse(userId: number): Promise<boolean> {
    if (ADMIN_IDS.includes(Number(userId))) return true;
    // 2026-11-10: dynamic — any "Job Documents" (modules.slug = job-documents) permission, direct or via a role
    try {
      const [hit] = await this.db.query(
        `SELECT 1 AS ok FROM permissions p JOIN modules m ON m.id = p.module_id
          WHERE m.slug = 'job-documents' AND m.is_active = 1 AND (
                p.id IN (SELECT permission_id FROM user_permissions WHERE user_id = ?)
             OR p.id IN (SELECT rp.permission_id FROM role_permissions rp JOIN user_roles ur ON ur.role_id = rp.role_id WHERE ur.user_id = ?))
          LIMIT 1`,
        [userId, userId],
      );
      if (hit) return true;
    } catch { /* table/module missing → fall through to the original rule */ }
    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 assertCanUse(userId: number) {
    if (!(await this.canUse(userId))) {
      throw new ForbiddenException('Only admin, scheme or coordinator users can generate company documents.');
    }
  }

  // ───────────────────────────── catalogue ─────────────────────────────
  private fileExists(f: PackForm): boolean {
    const p = (TEMPLATE_REGISTRY as any)[f.stage]?.[f.registryKey];
    return !!p && fs.existsSync(p);
  }

  catalogue() {
    return PACK_FORMS.filter((f) => this.fileExists(f)).map((f) => ({
      key: f.key,
      stage: f.stage,
      title: f.title,
      form_no: f.formNo ?? null,
      fields: f.fields,
      variant_for: f.variantFor ?? null,
      for_std: f.forStd ?? null,
    }));
  }

  // ───────────────────────────── company audits + prefill ─────────────────────────────
  async listAudits(companyId: number, userId: number) {
    await this.assertCanUse(userId);
    const [company] = await this.db.query(`SELECT id, name FROM companies WHERE id = ? LIMIT 1`, [companyId]);
    if (!company) throw new NotFoundException('Company not found');

    const rows: any[] = await this.db.query(
      `SELECT r.id, r.audit_code, r.audit_type, r.audit_stage, r.audit_mode, r.status,
              TIME_FORMAT(r.audit_time, '%H:%i') AS audit_time, r.lead_auditor_id, r.audit_request_id,
              DATE_FORMAT(s.schedule_date, '%Y-%m-%d') AS schedule_date,
              TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS lead_auditor,
              DATE_FORMAT(ar.created_at, '%Y-%m-%d') AS request_date
         FROM audit_schedule_rows r
         LEFT JOIN audit_schedules s ON s.id = r.schedule_id
         LEFT JOIN users u ON u.id = r.lead_auditor_id
         LEFT JOIN audit_requests ar ON ar.id = r.audit_request_id
        WHERE r.company_id = ? AND r.status <> 'CANCELLED'
        ORDER BY s.schedule_date DESC, r.id DESC`,
      [companyId],
    );
    const ids = rows.map((r) => r.id);
    const stds: any[] = ids.length
      ? await this.db.query(
          `SELECT rs.row_id, st.id, st.name, st.title
             FROM audit_schedule_row_standards rs JOIN standards st ON st.id = rs.standard_id
            WHERE rs.row_id IN (?)`,
          [ids],
        )
      : [];
    const stage1Date = rows
      .filter((r) => packStageFor(r.audit_type, r.audit_stage) === 'stage1' && r.schedule_date)
      .map((r) => r.schedule_date)
      .sort()[0] ?? null;

    const audits = rows.map((r) => {
      const standards = stds.filter((s) => s.row_id === r.id).map((s) => ({ id: s.id, name: s.name, title: s.title }));
      const stage = packStageFor(r.audit_type, r.audit_stage);
      const keys = stdKeysFor(standards.map((s) => s.name));
      const base = stage === 'stage1' ? r.schedule_date : stage1Date || r.schedule_date;
      const plus = (months: number) => (base ? dayjs(base).add(months, 'month').format('YYYY-MM-DD') : null);
      const t = r.audit_time ? Number(r.audit_time.slice(0, 2)) : null;
      return {
        id: r.id,
        audit_code: r.audit_code,
        audit_type: r.audit_type,
        audit_stage: r.audit_stage,
        audit_mode: r.audit_mode,
        status: r.status,
        schedule_date: r.schedule_date,
        audit_time: r.audit_time,
        lead_auditor_id: r.lead_auditor_id,
        lead_auditor: r.lead_auditor || null,
        standards,
        default_stage: stage,
        default_forms: {
          stage1: defaultFormKeys('stage1', keys),
          stage2: defaultFormKeys('stage2', keys),
        },
        prefill: {
          date: r.schedule_date,
          scheduleSlot: t === null ? 'FULL' : t < 13 ? 'MORNING' : 'AFTERNOON',
          leadAuditorId: r.lead_auditor_id,
          standardIds: standards.map((s) => s.id),
          expectedSurveillanceDate: plus(12),
          expectedIADate: plus(4),
          expectedMRMDate: plus(3),
          expectedInitialInquiryDate: r.request_date,
          expectedInitial_Stage1_Date: stage1Date,
          docReview: dayjs().format('YYYY-MM-DD'),
          prepareDate: dayjs().format('YYYY-MM-DD'),
        },
      };
    });

    return {
      company,
      audits,
      catalogue: this.catalogue(),
      packs: await this.listPacksRaw(companyId),
      can_generate: true,
    };
  }

  async listPacks(companyId: number, userId: number) {
    await this.assertCanUse(userId);
    return this.listPacksRaw(companyId);
  }

  private async listPacksRaw(companyId: number): Promise<any[]> {
    try {
      const rows: any[] = await this.db.query(
        `SELECT p.*, TRIM(CONCAT(COALESCE(u.firstName,''),' ',COALESCE(u.lastName,''))) AS created_by_name,
                r.audit_code, r.audit_stage, DATE_FORMAT(s.schedule_date, '%Y-%m-%d') AS schedule_date
           FROM company_document_packs p
           LEFT JOIN users u ON u.id = p.created_by
           LEFT JOIN audit_schedule_rows r ON r.id = p.audit_schedule_row_id
           LEFT JOIN audit_schedules s ON s.id = r.schedule_id
          WHERE p.company_id = ?
          ORDER BY p.id DESC`,
        [companyId],
      );
      return rows.map((p) => ({
        ...p,
        forms: this.json(p.forms_json, []),
        fields: this.json(p.fields_json, {}),
        forms_json: undefined,
        fields_json: undefined,
        zip_path: undefined,
        has_zip: !!p.zip_path,
      }));
    } catch (e: any) {
      if (this.isMissingTable(e)) return [];
      throw e;
    }
  }

  async codebook(q: string) {
    const term = String(q || '').trim();
    if (term.length < 1) return [];
    const like = `%${term}%`;
    return this.db.query(
      `SELECT id, eac_iaf_codes, division, \`group\`, class, nace_rev1, nace_rev2_description, risk
         FROM codebook
        WHERE eac_iaf_codes LIKE ? OR nace_rev1 LIKE ? OR nace_rev2_description LIKE ? OR division LIKE ? OR \`group\` LIKE ? OR class LIKE ?
        ORDER BY id LIMIT 40`,
      [like, like, like, like, like, like],
    );
  }

  // ───────────────────────────── generate ─────────────────────────────
  async generate(companyId: number, body: GenerateBody, userId: number) {
    await this.assertCanUse(userId);
    const stage: PackStage = body?.stage === 'stage2' ? 'stage2' : body?.stage === 'stage1' ? 'stage1' : (null as any);
    if (!stage) throw new BadRequestException('stage must be stage1 or stage2');
    const rowId = Number(body?.audit_schedule_row_id);
    const [row] = await this.db.query(`SELECT id, company_id FROM audit_schedule_rows WHERE id = ? LIMIT 1`, [rowId]);
    if (!row || Number(row.company_id) !== Number(companyId)) {
      throw new BadRequestException('Pick an audit of this company.');
    }
    const cat = this.catalogue().filter((f) => f.stage === stage);
    const keys = (Array.isArray(body.forms) ? body.forms : []).filter((k) => cat.some((f) => f.key === k));
    if (!keys.length) throw new BadRequestException('Tick at least one form.');

    const fields = this.cleanFields(body.fields || {});
    const needApproval = PACK_FORMS.filter((f) => f.stage === stage && keys.includes(f.key) && f.fields.includes('approvedDate'));
    if (needApproval.length && !fields.approvedDate) {
      throw new BadRequestException(
        `Document Approval Date is needed for: ${needApproval.map((f) => f.title).join(', ')}. Enter it, or untick those forms.`,
      );
    }

    const formsState = keys.map((k) => {
      const f = PACK_FORMS.find((x) => x.stage === stage && x.key === k)!;
      return { key: k, title: f.title, form_no: f.formNo ?? null, status: 'pending', file: null as string | null, error: null as string | null };
    });

    let packId = Number(body.pack_id) || 0;
    try {
      if (packId) {
        const [p] = await this.db.query(`SELECT id, company_id, status FROM company_document_packs WHERE id = ?`, [packId]);
        if (!p || Number(p.company_id) !== Number(companyId)) throw new NotFoundException('Pack not found');
        if (p.status === 'queued' || p.status === 'generating') throw new BadRequestException('This pack is already being generated.');
        await this.db.query(
          `UPDATE company_document_packs
              SET audit_schedule_row_id = ?, stage = ?, status = 'queued', forms_json = ?, fields_json = ?, error = NULL,
                  version = version + 1, updated_by = ?, updated_at = NOW()
            WHERE id = ?`,
          [rowId, stage, JSON.stringify(formsState), JSON.stringify(fields), userId, packId],
        );
      } else {
        const r: any = await this.db.query(
          `INSERT INTO company_document_packs (company_id, audit_schedule_row_id, stage, status, forms_json, fields_json, version, created_by, created_at, updated_at)
           VALUES (?, ?, ?, 'queued', ?, ?, 1, ?, NOW(), NOW())`,
          [companyId, rowId, stage, JSON.stringify(formsState), JSON.stringify(fields), userId],
        );
        packId = Number(r.insertId);
      }
    } catch (e: any) {
      if (this.isMissingTable(e)) {
        throw new BadRequestException(
          'Company documents are not set up yet: run backend/src/company-documents/migrations/run-001.js once on the server.',
        );
      }
      throw e;
    }

    // one pack at a time keeps Puppeteer memory low
    const id = packId;
    this.queue = this.queue.then(() => this.runPack(id)).catch((e) => this.logger.error(`[DOCS] queue error: ${e?.message}`));
    return this.getPack(packId);
  }

  async getPackForUser(packId: number, userId: number) {
    await this.assertCanUse(userId);
    return this.getPack(packId);
  }

  private async getPack(packId: number) {
    const [p] = await this.db.query(`SELECT * FROM company_document_packs WHERE id = ?`, [packId]);
    if (!p) throw new NotFoundException('Pack not found');
    return {
      ...p,
      forms: this.json(p.forms_json, []),
      fields: this.json(p.fields_json, {}),
      forms_json: undefined,
      fields_json: undefined,
      zip_path: undefined,
      has_zip: !!p.zip_path,
    };
  }

  /** Absolute path of one generated file (or the zip) after access checks. */
  async filePath(packId: number, which: string, userId: number): Promise<{ abs: string; name: string; type: string }> {
    await this.assertCanUse(userId);
    const [p] = await this.db.query(`SELECT * FROM company_document_packs WHERE id = ?`, [packId]);
    if (!p) throw new NotFoundException('Pack not found');
    const dir = path.join(STORAGE_ROOT, String(p.company_id), String(p.id));
    const base = `${(p.job_code || `pack-${p.id}`).replace(/[^A-Za-z0-9_-]+/g, '_')}`;
    if (which === 'zip') {
      if (!p.zip_path) throw new NotFoundException('ZIP not ready');
      const abs = path.join(dir, path.basename(p.zip_path));
      if (!fs.existsSync(abs)) throw new NotFoundException('ZIP file missing');
      return { abs, name: `${base}_${p.stage}.zip`, type: 'application/zip' };
    }
    const forms: any[] = this.json(p.forms_json, []);
    const f = forms.find((x) => x.key === which && x.file);
    if (!f) throw new NotFoundException('Document not ready');
    const abs = path.join(dir, path.basename(f.file));
    if (!fs.existsSync(abs)) throw new NotFoundException('File missing');
    return { abs, name: path.basename(f.file), type: 'application/pdf' };
  }

  // ───────────────────────────── worker ─────────────────────────────
  private async runPack(packId: number) {
    const [p] = await this.db.query(`SELECT * FROM company_document_packs WHERE id = ?`, [packId]);
    if (!p) return;
    const forms: any[] = this.json(p.forms_json, []);
    const fields: PackFields = this.json(p.fields_json, {});
    const stage: PackStage = p.stage;
    await this.db.query(`UPDATE company_document_packs SET status = 'generating', updated_at = NOW() WHERE id = ?`, [packId]);

    try {
      const ctx = await this.buildContext(p.company_id, p.audit_schedule_row_id, stage, fields);
      const job = await this.saveJob(p, ctx, fields, forms);
      ctx.job.id = job.id;
      ctx.job.jobCode = job.jobCode;

      const dir = path.join(STORAGE_ROOT, String(p.company_id), String(p.id));
      fs.mkdirSync(dir, { recursive: true });
      for (const old of fs.readdirSync(dir)) fs.unlinkSync(path.join(dir, old)); // regenerate = fresh set

      let n = 0;
      for (const f of forms) {
        n++;
        const def = PACK_FORMS.find((x) => x.stage === stage && x.key === f.key)!;
        try {
          const data = await this.templateData(ctx, def, fields);
          const html = this.pdf.renderTemplate(stage, def.registryKey, data);
          const buf = await this.pdf.generatePdf(html);
          const file = `${String(n).padStart(2, '0')}-${(def.formNo ? `Form ${def.formNo} ` : '') + def.title}`
            .replace(/[\\/:*?"<>|]+/g, '-')
            .replace(/\s+/g, ' ')
            .trim() + '.pdf';
          fs.writeFileSync(path.join(dir, file), buf);
          f.status = 'done';
          f.file = file;
          f.error = null;
        } catch (e: any) {
          f.status = 'failed';
          f.error = String(e?.message || e).slice(0, 300);
          this.logger.warn(`[DOCS] pack ${packId} form ${f.key} failed: ${f.error}`);
        }
        await this.db.query(`UPDATE company_document_packs SET forms_json = ?, updated_at = NOW() WHERE id = ?`, [
          JSON.stringify(forms),
          packId,
        ]);
      }

      const okFiles = forms.filter((f) => f.status === 'done');
      let zipName: string | null = null;
      if (okFiles.length) {
        zipName = `${job.jobCode.replace(/[^A-Za-z0-9_-]+/g, '_')}_${stage}.zip`;
        await this.zip(dir, okFiles.map((f) => f.file), path.join(dir, zipName));
      }
      const status = okFiles.length === forms.length ? 'done' : okFiles.length ? 'partial' : 'failed';
      await this.db.query(
        `UPDATE company_document_packs
            SET status = ?, forms_json = ?, zip_path = ?, job_id = ?, job_code = ?, generated_at = NOW(), updated_at = NOW(),
                error = ?
          WHERE id = ?`,
        [status, JSON.stringify(forms), zipName, job.id, job.jobCode, status === 'failed' ? 'No document could be generated.' : null, packId],
      );
      await this.db.query(`UPDATE jobs SET isPdfGenerated = ?, pdfPath = ? WHERE id = ?`, [
        okFiles.length ? 1 : 0,
        zipName ? `companyDocuments/${p.company_id}/${p.id}/${zipName}` : null,
        job.id,
      ]);
      this.logger.log(`[DOCS] pack ${packId} (${job.jobCode}) ${status}: ${okFiles.length}/${forms.length}`);
    } catch (e: any) {
      this.logger.error(`[DOCS] pack ${packId} failed: ${e?.message}`);
      await this.db.query(
        `UPDATE company_document_packs SET status = 'failed', error = ?, updated_at = NOW() WHERE id = ?`,
        [String(e?.message || e).slice(0, 500), packId],
      );
    }
  }

  private zip(dir: string, files: string[], out: string): Promise<void> {
    return new Promise((resolve, reject) => {
      const output = fs.createWriteStream(out);
      const archive = archiver('zip', { zlib: { level: 9 } });
      output.on('close', () => resolve());
      archive.on('error', reject);
      archive.pipe(output);
      for (const f of files) archive.file(path.join(dir, f), { name: f });
      archive.finalize();
    });
  }

  // ───────────────────────────── data for the templates ─────────────────────────────
  private async buildContext(companyId: number, rowId: number, stage: PackStage, fields: PackFields) {
    const [company] = await this.db.query(`SELECT * FROM companies WHERE id = ?`, [companyId]);
    if (!company) throw new Error('Company not found');
    const [row] = await this.db.query(
      `SELECT r.*, DATE_FORMAT(s.schedule_date, '%Y-%m-%d') AS schedule_date
         FROM audit_schedule_rows r LEFT JOIN audit_schedules s ON s.id = r.schedule_id WHERE r.id = ?`,
      [rowId],
    );
    if (!row) throw new Error('Audit not found');

    const stdIds = (fields.standardIds && fields.standardIds.length ? fields.standardIds : null) ??
      (await this.db.query(`SELECT standard_id FROM audit_schedule_row_standards WHERE row_id = ?`, [rowId])).map((x: any) => x.standard_id);
    const standards: any[] = stdIds.length
      ? await this.db.query(`SELECT id, name, title FROM standards WHERE id IN (?) ORDER BY id`, [stdIds])
      : [];

    const leadId = Number(fields.leadAuditorId || row.lead_auditor_id) || null;
    const [lead] = leadId
      ? await this.db.query(`SELECT id, firstName, lastName, email FROM users WHERE id = ?`, [leadId])
      : [null];

    // Stage 1 audit of this company (shown before Stage 2 in the stage tables)
    const stageRows: any[] = await this.db.query(
      `SELECT r.id, r.audit_type, r.audit_stage, DATE_FORMAT(s.schedule_date, '%Y-%m-%d') AS d, u.firstName, u.lastName
         FROM audit_schedule_rows r
         LEFT JOIN audit_schedules s ON s.id = r.schedule_id
         LEFT JOIN users u ON u.id = r.lead_auditor_id
        WHERE r.company_id = ? AND r.status <> 'CANCELLED'
        ORDER BY s.schedule_date ASC`,
      [companyId],
    );
    const fmt = (d: any) => (d ? dayjs(d).format('DD MMMM YYYY') : '');
    const date = fields.date || row.schedule_date;
    const current = {
      stageName: stage === 'stage1' ? 'Stage 1' : 'Stage 2',
      auditDate: fmt(date),
      auditBy: lead ? { firstName: lead.firstName, lastName: lead.lastName } : null,
      technicalExpert: fields.technicalExpert || '',
      md: fields.md || '',
      docReview: fields.docReview || null,
      date,
    };
    const stages: any[] = [];
    if (stage === 'stage2') {
      const s1 = stageRows.find((r) => packStageFor(r.audit_type, r.audit_stage) === 'stage1' && r.id !== row.id);
      if (s1) {
        stages.push({
          stageName: 'Stage 1',
          auditDate: fmt(s1.d),
          auditBy: { firstName: s1.firstName, lastName: s1.lastName },
          technicalExpert: fields.technicalExpert || '',
          md: fields.md || '',
          date: s1.d,
        });
      }
    }
    stages.push(current);

    const companySnapshot = {
      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,
      fax: company.fax,
      companyvalidity: company.validity,
      companyaccreditation: company.accreditation,
      companycertification_body: company.accreditation,
      companyRefrence: company.reference_number,
    };

    // "CERT. NO." on the forms = the company's certificate number(s) for these standards (new_certificate), if issued
    let certNo: string | null = null;
    try {
      const certs: any[] = await this.db.query(
        `SELECT certificate_no FROM new_certificate
          WHERE company_id = ? AND certificate_no IS NOT NULL AND certificate_no <> ''
            ${standards.length ? 'AND standard_id IN (?)' : ''}
          ORDER BY (status = 'active') DESC, id DESC`,
        standards.length ? [companyId, standards.map((s) => s.id)] : [companyId],
      );
      certNo = [...new Set(certs.map((c) => String(c.certificate_no).trim()))].join(', ') || null;
    } catch {
      certNo = null;
    }

    return {
      company: {
        ...company,
        ct_num: certNo,
        standardsList: standards.map((s) => s.name),
      },
      row,
      standards,
      lead,
      stages,
      date,
      companySnapshot,
      job: {
        id: 0,
        jobCode: '',
        date,
        md: fields.md || '',
        mdRisk: fields.mdRisk || '',
        numEmployees: fields.numEmployees ?? null,
        naceEacCodes: fields.naceEacCodes || '',
        docReview: fields.docReview || null,
        stage: stage === 'stage1' ? 'Stage 1' : 'Stage 2',
        companySnapshot,
        company: { reference_number: company.reference_number },
        leadAuditor: lead,
        standards,
      } as any,
    };
  }

  private async templateData(ctx: any, def: PackForm, fields: PackFields) {
    let content: any[] = [];
    if (def.templateId) {
      const rows: any[] = await this.db.query(
        `SELECT startTime, endTime, functional_area, content FROM template_contents WHERE template_id = ?`,
        [def.templateId],
      );
      const toMin = (t: string) => {
        const [h, m] = String(t || '0:0').split(':').map(Number);
        return (h || 0) * 60 + (m || 0);
      };
      content = rows
        .map((item) => ({
          startTime: item.startTime,
          endTime: item.endTime,
          functionalArea: item.functional_area || 'Function Area',
          content: item.content
            ? String(item.content).split('\n').map((l: string) => l.replace(/^- /, '').trim()).filter(Boolean)
            : [],
        }))
        .sort((a, b) => toMin(a.startTime) - toMin(b.startTime));
      const slot = String(fields.scheduleSlot || 'FULL').toUpperCase();
      if (slot === 'MORNING') content = content.filter((i) => toMin(i.endTime) <= 13 * 60);
      if (slot === 'AFTERNOON') content = content.filter((i) => toMin(i.startTime) >= 14 * 60);
    }

    return {
      job: ctx.job,
      company: ctx.company,
      audits: [],
      stages: ctx.stages,
      template: { id: def.templateId, name: def.title, filePath: def.registryKey },
      templateContent: content,
      jobCodes: ctx.job.jobCode ? ctx.job.jobCode.split(',').map((s: string) => s.trim()) : [],
      stage: ctx.job.stage,
      standards: ctx.standards,
      leadAuditor: ctx.lead,
      date: ctx.date,
      docReview: fields.docReview || ctx.date || null,
      numEmployees: fields.numEmployees ?? null,
      naceEacCodes: fields.naceEacCodes || '',
      md: fields.md || '',
      mdRisk: fields.mdRisk || '',
      expectedSurveillanceDate: fields.expectedSurveillanceDate || '',
      expectedIADate: fields.expectedIADate || '',
      expectedMRMDate: fields.expectedMRMDate || '',
      expectedInitial_Stage1_Date: fields.expectedInitial_Stage1_Date || '',
      prepareDate: fields.prepareDate || '',
      approvedDate: fields.approvedDate || '',
      expectedInitialInquiryDate: fields.expectedInitialInquiryDate || '',
      templateSnapshot: null,
    };
  }

  // ───────────────────────────── job record (scheme_dbs.jobs) ─────────────────────────────
  private async saveJob(p: any, ctx: any, f: PackFields, forms: any[]): Promise<{ id: number; jobCode: string }> {
    const stageEnum = p.stage === 'stage1' ? 'Stage 1' : 'Stage 2';
    const plan = PACK_FORMS.find((x) => x.stage === p.stage && forms.some((fm) => fm.key === x.key) && x.variantFor !== undefined)
      ?? PACK_FORMS.find((x) => x.stage === p.stage && forms.some((fm) => fm.key === x.key));
    const templateId = plan?.templateId ?? null;
    const risk = ['Low', 'Medium', 'High'].includes(String(f.mdRisk)) ? f.mdRisk : 'Low';
    const values = {
      stage: stageEnum,
      date: ctx.date,
      numEmployees: f.numEmployees === null || f.numEmployees === undefined || f.numEmployees === '' ? null : Number(f.numEmployees),
      naceEacCodes: f.naceEacCodes || null,
      mdRisk: risk,
      company_id: p.company_id,
      lead_auditor_id: ctx.lead?.id ?? null,
      template_id: templateId,
      md: f.md || null,
      docReview: f.docReview || null,
      templateSnapshot: JSON.stringify({ id: templateId, name: plan?.title ?? null, filePath: plan?.registryKey ?? null, pack_id: p.id, forms: forms.map((x) => x.key) }),
      companySnapshot: JSON.stringify(ctx.companySnapshot),
      companyName: ctx.company.name,
      companyAddress: ctx.company.address,
      companyScope: ctx.company.scope_of_work,
      scheduleSlot: f.scheduleSlot || 'FULL',
      expectedSurveillanceDate: f.expectedSurveillanceDate || null,
      expectedIADate: f.expectedIADate || null,
      expectedMRMDate: f.expectedMRMDate || null,
      expectedInitialInquiryDate: f.expectedInitialInquiryDate || null,
      expectedInitial_Stage1_Date: f.expectedInitial_Stage1_Date || null,
      prepareDate: f.prepareDate || null,
      approvedDate: f.approvedDate || null,
    };

    if (p.job_id) {
      const [j] = await this.db.query(`SELECT id, jobCode FROM jobs WHERE id = ?`, [p.job_id]);
      if (j) {
        const cols = Object.keys(values);
        await this.db.query(
          `UPDATE jobs SET ${cols.map((c) => `\`${c}\` = ?`).join(', ')}, updated_at = NOW(6) WHERE id = ?`,
          [...cols.map((c) => (values as any)[c]), j.id],
        );
        await this.db.query(`DELETE FROM job_standards WHERE job_id = ?`, [j.id]);
        for (const s of ctx.standards) await this.db.query(`INSERT IGNORE INTO job_standards (job_id, standard_id) VALUES (?, ?)`, [j.id, s.id]);
        return { id: j.id, jobCode: j.jobCode };
      }
    }

    // new job: numbering serialised with a named lock
    const qr = this.db.createQueryRunner();
    await qr.connect();
    let locked = false;
    try {
      const r = await qr.query(`SELECT GET_LOCK('crm_job_code', 15) AS ok`);
      if (Number(r?.[0]?.ok) !== 1) throw new Error('Job number is busy, please try again.');
      locked = true;
      const jobCode = await this.nextJobCode(ctx.standards, qr);
      const cols = Object.keys(values);
      const ins: any = await qr.query(
        `INSERT INTO jobs (jobCode, ${cols.map((c) => `\`${c}\``).join(', ')}, isPdfGenerated, created_at, updated_at)
         VALUES (?, ${cols.map(() => '?').join(', ')}, 0, NOW(6), NOW(6))`,
        [jobCode, ...cols.map((c) => (values as any)[c])],
      );
      const id = Number(ins.insertId);
      for (const s of ctx.standards) await qr.query(`INSERT IGNORE INTO job_standards (job_id, standard_id) VALUES (?, ?)`, [id, s.id]);
      await qr.query(`UPDATE company_document_packs SET job_id = ?, job_code = ? WHERE id = ?`, [id, jobCode, p.id]);
      return { id, jobCode };
    } finally {
      if (locked) {
        try {
          await qr.query(`SELECT RELEASE_LOCK('crm_job_code')`);
        } catch {
          /* ignore */
        }
      }
      await qr.release();
    }
  }

  /** 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 codes: 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(`[DOCS] job number scan failed: ${e?.message}`);
        }
      };
      await scan((sql, p) => qr.query(sql, p));
      await scan((sql, p) => this.cert.query(sql, p));
      const next = max ? max + 1 : JOB_INITIAL_NUMBERS[prefix] ?? 1000;
      codes.push(`${prefix}:${mm}-${next}`);
    }
    return codes.join(', ');
  }

  // ───────────────────────────── helpers ─────────────────────────────
  private cleanFields(raw: any): PackFields {
    const d = (v: any) => (v && /^\d{4}-\d{2}-\d{2}/.test(String(v)) ? String(v).slice(0, 10) : null);
    const s = (v: any, n = 255) => (v === undefined || v === null || String(v).trim() === '' ? null : String(v).trim().slice(0, n));
    const md = s(raw.md, 20);
    if (md && !/^(\d+(\.\d+)?|\.\d+)$/.test(md)) throw new BadRequestException('MD must be a number such as .5, 1 or 2.');
    const emp = raw.numEmployees === '' || raw.numEmployees === null || raw.numEmployees === undefined ? null : Number(raw.numEmployees);
    if (emp !== null && (!Number.isInteger(emp) || emp < 0)) throw new BadRequestException('No. of employees must be a whole number.');
    const risk = s(raw.mdRisk, 10);
    if (risk && !['Low', 'Medium', 'High'].includes(risk)) throw new BadRequestException('MD Risk must be Low, Medium or High.');
    const slot = s(raw.scheduleSlot, 10);
    return {
      docReview: d(raw.docReview),
      prepareDate: d(raw.prepareDate),
      approvedDate: d(raw.approvedDate),
      numEmployees: emp,
      naceEacCodes: s(raw.naceEacCodes, 500),
      md,
      mdRisk: risk,
      technicalExpert: s(raw.technicalExpert, 200),
      date: d(raw.date),
      scheduleSlot: slot && ['MORNING', 'AFTERNOON', 'FULL'].includes(slot) ? slot : 'FULL',
      leadAuditorId: Number(raw.leadAuditorId) || null,
      standardIds: Array.isArray(raw.standardIds) ? raw.standardIds.map(Number).filter((x: number) => x > 0) : [],
      expectedSurveillanceDate: d(raw.expectedSurveillanceDate),
      expectedIADate: d(raw.expectedIADate),
      expectedMRMDate: d(raw.expectedMRMDate),
      expectedInitialInquiryDate: d(raw.expectedInitialInquiryDate),
      expectedInitial_Stage1_Date: d(raw.expectedInitial_Stage1_Date),
    };
  }

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

  private isMissingTable(e: any) {
    return /company_document_packs/.test(e?.message || '') && /doesn't exist|ER_NO_SUCH_TABLE/i.test(e?.message || '');
  }

  /** Field names used in the UI for the "printed on" hints. */
  static readonly FIELD_LIST: PackField[] = ['approvedDate', 'prepareDate', 'docReview', 'numEmployees', 'naceEacCodes', 'md', 'mdRisk', 'technicalExpert'];
}