${this.esc(subject)}

import { Injectable, Logger, BadRequestException, NotFoundException, } from '@nestjs/common'; import { InjectRepository, InjectDataSource } from '@nestjs/typeorm'; import { Repository, DataSource } from 'typeorm'; import { NcEntity } from './entities/nc.entity'; import { NcrEntryEntity } from './entities/ncr-entry.entity'; import { NcRemarkEntity } from './entities/nc-remark.entity'; import { CreateNcDto } from './dto/create-nc.dto'; import { createReadStream, existsSync, mkdirSync, writeFileSync, statSync } from 'fs'; import { join, extname, basename } from 'path'; import * as crypto from 'crypto'; import { MailsService } from '../mails/mails.service'; @Injectable() export class NcService { private readonly logger = new Logger(NcService.name); constructor( @InjectRepository(NcEntity, 'scheme_dbs') private readonly ncRepo: Repository, @InjectRepository(NcrEntryEntity, 'scheme_dbs') private readonly entryRepo: Repository, @InjectRepository(NcRemarkEntity, 'scheme_dbs') private readonly remarkRepo: Repository, // @InjectDataSource('scheme_dbs') // private readonly schemeDb: DataSource, @InjectDataSource('scheme_dbs') private readonly schemeDb: DataSource, private readonly mailsService: MailsService, // 👈 NEW ) { } private readonly NC_STORAGE_ROOT = process.env.NC_UPLOAD_ROOT || '/var/www/scheme_certiifcation/backend/storage/nc-uploads'; private readonly NC_ALLOWED = new Set([ '.pdf', '.doc', '.docx', '.jpg', '.jpeg', '.png', '.zip', ]); private parseDocs(raw?: string | null): { path: string; name: string }[] { if (!raw) return []; const s = String(raw).trim(); if (!s) return []; if (s.startsWith('[')) { try { const arr = JSON.parse(s); return Array.isArray(arr) ? arr.map((x: any) => (typeof x === 'string' ? { path: x, name: x.split('/').pop() || 'file' } : { path: x.path, name: x.name || (x.path || '').split('/').pop() || 'file' })) : []; } catch { return []; } } return [{ path: s, name: s.split('/').pop() || 'file' }]; } async clientRespondToFinding( ncId: number, entryId: number, companyIds: number[], correctiveAction: string, files?: { originalname: string; buffer: Buffer }[], ): Promise<{ ok: true; entry_id: number; documents: { path: string; name: string }[] }> { const nc = await this.ncRepo.findOne({ where: { id: ncId } }); if (!nc || !companyIds.includes(Number(nc.company_id))) { throw new NotFoundException('NC not found'); } const entry = await this.entryRepo.findOne({ where: { id: entryId, nc_id: ncId } }); if (!entry) throw new NotFoundException('Finding not found'); const docs = this.parseDocs(entry.document_path); if (files && files.length) { const dir = join(this.NC_STORAGE_ROOT, String(ncId)); mkdirSync(dir, { recursive: true }); for (const file of files) { if (!file?.buffer?.length) continue; const ext = extname(file.originalname).toLowerCase(); if (!this.NC_ALLOWED.has(ext)) { throw new BadRequestException(`File type ${ext || '(none)'} not allowed`); } const safe = basename(file.originalname).replace(/[^\w.\-]+/g, '_'); const fname = `${Date.now()}_${Math.random().toString(36).slice(2, 7)}_${safe}`; writeFileSync(join(dir, fname), file.buffer, { mode: 0o664 }); docs.push({ path: `new:${ncId}/${fname}`, name: safe }); } } entry.corrective_action = (correctiveAction || '').trim() || entry.corrective_action; entry.document_path = docs.length ? JSON.stringify(docs) : entry.document_path; entry.status = 'pending'; await this.entryRepo.save(entry); // 👇 NEW: notify auditor + coordinators about the client upload. // Wrapped in try/catch so email failures NEVER break the upload itself. try { await this.notifyStaffOnClientUpload(nc, entry, files || []); } catch (err: any) { this.logger.warn(`[client-upload-email] failed (upload itself succeeded): ${err.message}`); } return { ok: true, entry_id: entry.id, documents: docs }; } // 👇 NEW HELPER — add anywhere in the class (I'd put it right after clientRespondToFinding) private async notifyStaffOnClientUpload( nc: NcEntity, entry: NcrEntryEntity, files: { originalname: string; buffer: Buffer }[], ): Promise { // 1. Resolve the auditor — try followed_up_by, then closed_by, then created_by const ncRow = await this.schemeDb.query( `SELECT followed_up_by, closed_by, created_by FROM nc__ncs WHERE id = ? LIMIT 1`, [nc.id], ); const auditorUserId: number | null = ncRow?.[0]?.followed_up_by || ncRow?.[0]?.closed_by || ncRow?.[0]?.created_by || null; let auditorEmail: string | null = null; let auditorName = 'Auditor'; if (auditorUserId) { const uRows = await this.schemeDb.query( `SELECT email, firstName, lastName FROM users WHERE id = ? LIMIT 1`, [auditorUserId], ); auditorEmail = (uRows?.[0]?.email || '').trim().toLowerCase() || null; const fn = (uRows?.[0]?.firstName || '').trim(); const ln = (uRows?.[0]?.lastName || '').trim(); auditorName = [fn, ln].filter(Boolean).join(' ') || 'Auditor'; } // 2. Auditor is the only recipient — bail out if none if (!auditorEmail) { this.logger.warn(`[client-upload-email] no auditor email resolved for NC ${nc.id} — skipping`); return; } // 3. Company name (best-effort) let companyName = 'Client'; try { const cRows = await this.schemeDb.query( `SELECT name FROM companies WHERE id = ? LIMIT 1`, [nc.company_id], ); companyName = (cRows?.[0]?.name || '').trim() || 'Client'; } catch { /* company name is optional */ } // 4. Attachments — the files the client just uploaded const attachments = (files || []) .filter((f) => f?.buffer?.length) .map((f) => ({ filename: f.originalname, content: f.buffer, contentType: this.guessMime(f.originalname), })); // 5. Formatted timestamp const uploadedAt = new Date().toLocaleDateString('en-GB', { day: '2-digit', month: 'long', year: 'numeric', hour: '2-digit', minute: '2-digit', }); // 6. Numbered file list (professional, no emoji) const fileRows = attachments.length ? attachments.map((a, i) => ` ${i + 1}. ${this.esc(a.filename)} `).join('') : `No files attached`; // 7. Build the professional email const subject = `New Evidence Submitted by Client — NC #${nc.id}`; const html = ` ${this.esc(subject)}

Audit & Certification Platform

New Evidence Received

Client response to Non-Conformity Report #${nc.id}

Dear ${this.esc(auditorName)},

We would like to inform you that ${this.esc(companyName)} has submitted supporting evidence for a finding raised on Non-Conformity Report #${nc.id}.

The submission is now available for your review. Kindly examine the attached document(s) and update the finding status accordingly in the auditor dashboard at your earliest convenience.

Submission Summary

Client

${this.esc(companyName)}

NC Report

#${nc.id}

Finding ID

#${entry.id}

Submitted on

${this.esc(uploadedAt)}

Files received

${attachments.length}

Attached Documents

${fileRows}

Should you require any assistance or clarification regarding this submission, please do not hesitate to reach out to our support team.

Best regards,

Quality Registrar Systems

Audit & Certification Team

`; // 8. Send it — auditor only, no CC try { await this.mailsService.sendAsUser(auditorUserId, { to: auditorEmail, subject, html, ...(attachments.length ? { attachments } : {}), } as any); this.logger.log( `[client-upload-email] sent → to=${auditorEmail} attachments=${attachments.length}`, ); } catch (err: any) { this.logger.error(`[client-upload-email] mailsService.sendAsUser failed: ${err.message}`); throw err; } } // 👇 NEW small helpers — put them near the other private helpers (e.g. after parseDocs) private esc(s: string): string { return String(s ?? '').replace(/&/g, '&').replace(//g, '>'); } private guessMime(name: string): string { const ext = (name.split('.').pop() || '').toLowerCase(); return ext === 'pdf' ? 'application/pdf' : ext === 'png' ? 'image/png' : ext === 'jpg' || ext === 'jpeg' ? 'image/jpeg' : ext === 'doc' ? 'application/msword' : ext === 'docx' ? 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' : ext === 'zip' ? 'application/zip' : 'application/octet-stream'; } async openFindingEvidence( ncId: number, entryId: number, companyIds: number[], index = 0, ): Promise<{ stream: any; size: number; mime: string; filename: string }> { const nc = await this.ncRepo.findOne({ where: { id: ncId } }); if (!nc || !companyIds.includes(Number(nc.company_id))) { throw new NotFoundException('NC not found'); } const entry = await this.entryRepo.findOne({ where: { id: entryId, nc_id: ncId } }); const docs = this.parseDocs(entry?.document_path); const doc = docs[index]; if (!doc) throw new NotFoundException('Evidence not found'); const rel = doc.path.replace(/^new:/i, '').replace(/^\/+/, ''); const abs = join(this.NC_STORAGE_ROOT, rel); if (!existsSync(abs)) throw new NotFoundException('Evidence file missing'); const ext = extname(abs).toLowerCase(); const mime = ext === '.pdf' ? 'application/pdf' : ext === '.png' ? 'image/png' : ext === '.jpg' || ext === '.jpeg' ? 'image/jpeg' : ext === '.zip' ? 'application/zip' : ext === '.doc' ? 'application/msword' : ext === '.docx' ? 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' : 'application/octet-stream'; return { stream: createReadStream(abs), size: statSync(abs).size, mime, filename: doc.name }; } async create( dto: CreateNcDto, currentUserId: number, ): Promise<{ ok: true; nc_id: number; audit_id: number; findings_created: number; }> { const validFindings = (dto.findings || []).filter((f) => f.ncr_statement?.trim(), ); if (validFindings.length === 0) { throw new BadRequestException( 'At least one finding with a statement is required.', ); } // Verify the audit row exists (FK would catch it, but a clear error is nicer). const auditExists = await this.schemeDb.query( `SELECT id FROM audit_schedule_rows WHERE id = ? LIMIT 1`, [dto.audit_id], ); if (!auditExists.length) { throw new NotFoundException(`Audit ${dto.audit_id} not found`); } const qr = this.schemeDb.createQueryRunner(); await qr.connect(); await qr.startTransaction(); try { // Build the NC + its findings via cascade, save in one call. const nc = qr.manager.create(NcEntity, { audit_id: dto.audit_id, company_id: dto.company_id ?? null, auditee_name: dto.auditee_name ?? null, audit_type: dto.audit_type ?? null, nc_type: dto.nc_type, status: dto.status ?? 'open', follow_up_date: dto.follow_up_date || null, due_date: dto.due_date || null, follow_up_notes: dto.follow_up_notes ?? null, remark: dto.remark ?? null, created_by: currentUserId, entries: validFindings.map((f) => qr.manager.create(NcrEntryEntity, { nc_type: f.nc_type ?? dto.nc_type, ncr_statement: f.ncr_statement.trim(), criteria_clause: f.criteria_clause ?? null, corrective_action: f.corrective_action ?? null, status: f.status ?? 'open', }), ), }); const savedNc = await qr.manager.save(NcEntity, nc); // Optional opening remark. if (dto.remark && dto.remark.trim()) { await qr.manager.save( NcRemarkEntity, qr.manager.create(NcRemarkEntity, { nc_id: savedNc.id, user_id: currentUserId, remark: dto.remark.trim(), }), ); } await qr.commitTransaction(); const findingsCreated = savedNc.entries?.length ?? validFindings.length; this.logger.log( `[NC-CREATE] User ${currentUserId} raised NC ${savedNc.id} for audit ` + `${dto.audit_id} with ${findingsCreated} finding(s)`, ); return { ok: true, nc_id: savedNc.id, audit_id: dto.audit_id, findings_created: findingsCreated, }; } catch (err) { await qr.rollbackTransaction(); this.logger.error( `[NC-CREATE] Failed for audit ${dto.audit_id}: ${(err as Error).message}`, (err as Error).stack, ); throw err; } finally { await qr.release(); } } async getClientNcs(companyIds: number[]): Promise { if (!companyIds || companyIds.length === 0) return []; const ncs = await this.ncRepo.find({ where: companyIds.map((cid) => ({ company_id: cid })), order: { created_at: 'DESC' }, }); if (ncs.length === 0) return []; const ncIds = ncs.map((n) => n.id); // pull findings for all these NCs in one query const entries = await this.entryRepo .createQueryBuilder('e') .where('e.nc_id IN (:...ids)', { ids: ncIds }) .getMany(); const byNc = new Map(); for (const e of entries) { const arr = byNc.get(Number(e.nc_id)) || []; arr.push(e); byNc.set(Number(e.nc_id), arr); } return ncs.map((nc) => { const items = byNc.get(Number(nc.id)) || []; const open = items.filter((i) => i.status === 'open').length; const pending = items.filter((i) => i.status === 'pending').length; const closed = items.filter((i) => i.status === 'closed').length; return { id: nc.id, created_by: nc.created_by, audit_id: nc.audit_id, company_id: nc.company_id, auditee_name: nc.auditee_name, audit_type: nc.audit_type, nc_type: nc.nc_type, status: nc.status, due_date: nc.due_date, created_at: nc.created_at, findings_total: items.length, findings_open: open, findings_pending: pending, findings_closed: closed, }; }); } /** * One NC with all its findings — company-scoped so a client can only * open their own company's NC. */ async getClientNcDetail(ncId: number, companyIds: number[]): Promise { if (!companyIds || companyIds.length === 0) { throw new NotFoundException('NC not found'); } const nc = await this.ncRepo.findOne({ where: { id: ncId } }); if (!nc || !companyIds.includes(Number(nc.company_id))) { throw new NotFoundException('NC not found'); } const entries = await this.entryRepo.find({ where: { nc_id: ncId }, order: { id: 'ASC' }, }); return { id: nc.id, created_by: nc.created_by, audit_id: nc.audit_id, company_id: nc.company_id, auditee_name: nc.auditee_name, audit_type: nc.audit_type, nc_type: nc.nc_type, status: nc.status, due_date: nc.due_date, follow_up_date: nc.follow_up_date, remark: nc.remark, created_at: nc.created_at, findings: entries.map((e) => ({ id: e.id, nc_type: e.nc_type, ncr_statement: e.ncr_statement, criteria_clause: e.criteria_clause, corrective_action: e.corrective_action, document_path: e.document_path, documents: this.parseDocs(e.document_path), status: e.status, })), }; } /** Save the client's signature + optional stamp for an NC. */ async saveClientSignature( ncId: number, companyId: number, payload: { signer_name: string; signer_email?: string | null; signature_img: string; stamp_img?: string }, ): Promise<{ ok: true; document_hash: string; signed_at: string }> { const ds = this.schemeDb; // your @InjectDataSource('scheme_dbs') // stamp: if provided, save to company (reused later). If not, load company's saved stamp. let stamp = payload.stamp_img || null; if (stamp) { await ds.query(`UPDATE companies SET nc_stamp_img = ? WHERE id = ?`, [stamp, companyId]); } else { const rows = await ds.query(`SELECT nc_stamp_img FROM companies WHERE id = ? LIMIT 1`, [companyId]); stamp = rows?.[0]?.nc_stamp_img || null; } const signedAt = new Date(); const hash = crypto .createHash('sha256') .update(`${ncId}|${payload.signer_name}|${signedAt.toISOString()}`) .digest('hex') .slice(0, 12) .toUpperCase(); // upsert (unique on nc_id) await ds.query( `INSERT INTO nc_signatures (nc_id, company_id, signer_name, signer_email, signature_img, stamp_img, signed_at, document_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE signer_name = VALUES(signer_name), signer_email = VALUES(signer_email), signature_img = VALUES(signature_img), stamp_img = VALUES(stamp_img), signed_at = VALUES(signed_at), document_hash = VALUES(document_hash)`, [ncId, companyId, payload.signer_name, payload.signer_email || null, payload.signature_img, stamp, signedAt, hash], ); return { ok: true, document_hash: hash, signed_at: signedAt.toISOString() }; } /** Fetch a saved signature for an NC (used by the PDF builder). */ async getNcSignature(ncId: number): Promise { const ds = this.schemeDb; const rows = await ds.query( `SELECT signer_name, signer_email, signature_img, stamp_img, signed_at, document_hash FROM nc_signatures WHERE nc_id = ? LIMIT 1`, [ncId], ); return rows?.[0] || null; } }