import {
  BadRequestException,
  ForbiddenException,
  Injectable,
  Logger,
  NotFoundException,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { Readable } from 'stream';
import { NcFileStorageService } from './nc-file-storage.service';
import { ClosurePdfService } from './closure-pdf.service';
import { MailsService } from '../../mails/mails.service';


const VIEW_ALL_ROLES = ['super-admin', 'scheme', 'coordinator', 'marketing'];

export interface ClosureRowDto {
  entry_id: number;
  evidence_received: string;
  submitted_docs: string;
  result_accepted: boolean;
  remarks: string;
  status?: 'open' | 'closed';
}

export interface SaveClosureDto {
  closed_entry_ids: number[];
  rows: ClosureRowDto[];
  verification_by_auditor: string;
  auditor_name: string;
  closure_date: string;
  send_to_client: boolean;
  closure_to?: string;    // 🆕
  closure_cc?: string;    // 🆕
  closure_bcc?: string;   // 🆕
  finalize: boolean;
}

export interface ClosureResponse {
  nc_id: number;
  source: 'QRS' | 'TQS' | 'NEW';
  is_finalized: boolean;
  finalized_at: string | null;
  signed_copy_path: string | null;
  signature_path: string | null;
  verification_by_auditor: string;
  auditor_name: string;
  closure_date: string | null;
  send_to_client: boolean;
  rows: Array<{
    id: number;
    entry_id: number;
    evidence_received: string;
    submitted_docs: string;
    result_accepted: boolean;
    remarks: string;
  }>;
}

@Injectable()
export class NcClosureService {
  private readonly logger = new Logger(NcClosureService.name);

  constructor(
    @InjectDataSource('qrs') private readonly qrsDs: DataSource,
    @InjectDataSource('tqs') private readonly tqsDs: DataSource,
    @InjectDataSource('scheme_dbs') private readonly schemeDb: DataSource,
    private readonly fileStorage: NcFileStorageService,
    private readonly pdfService: ClosurePdfService, // 🆕 PHASE 2B
    private readonly mailsService: MailsService,    // 🆕 closure email

  ) { }

  // ═══════════════════════════════════════════════════════════════════
  // 🆕 Datasource resolver — THREE-way (QRS / TQS / NEW).
  //    NEW NCs and their ncr_entries live in scheme_dbs , NOT tqs.
  //    A two-way ternary (source === 'QRS' ? qrs : tqs) silently routed
  //    every NEW request to the TQS database, which is why finalizing a
  //    NEW closure failed with "findings do not belong to NC".
  // ═══════════════════════════════════════════════════════════════════
  private dsFor(source: 'QRS' | 'TQS' | 'NEW'): DataSource {
    if (source === 'QRS') return this.qrsDs;
    if (source === 'TQS') return this.tqsDs;
    return this.schemeDb; // NEW
  }

  // ═══════════════════════════════════════════════════════════════════
  // Permissions
  // ═══════════════════════════════════════════════════════════════════

  private async userCanViewAll(userId: number): Promise<boolean> {
    if (!userId) return false;
    const rows = await this.schemeDb.query(
      `SELECT LOWER(r.name) AS role_name
       FROM users u
       INNER JOIN user_roles ur ON ur.user_id = u.id
       INNER JOIN roles r ON r.id = ur.role_id
       WHERE u.id = ?`,
      [userId],
    );
    return (rows as any[]).some((r) =>
      VIEW_ALL_ROLES.includes(r.role_name),
    );
  }
  private async userHasNcPermission(
    userId: number,
    action: string,
  ): Promise<boolean> {
    if (!userId) return false;
    try {
      const rows = await this.schemeDb.query(
        `SELECT 1
         FROM user_permissions up
         INNER JOIN permissions p ON p.id = up.permission_id
        WHERE up.user_id = ? AND p.action = ?
        LIMIT 1`,
        [userId, action],
      );
      return (rows as any[]).length > 0;
    } catch (e: any) {
      this.logger.error(`[NC-PERM] lookup failed: ${e.message}`);
      return false;
    }
  }
  private async assertCanEdit(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    userId: number,
  ): Promise<void> {
    const ds = this.dsFor(source);
    const rows = await ds.query(
      `SELECT followed_up_by, closed_by FROM nc__ncs WHERE id = ?`,
      [ncId],
    );
    if (!rows.length) {
      throw new NotFoundException(`NC ${ncId} not found in ${source}`);
    }
    const canViewAll = await this.userCanViewAll(userId);
    if (canViewAll) return;

    // 🆕 anyone granted the final-closure permission can close
    if (await this.userHasNcPermission(userId, 'final-closure')) return;

    const r = rows[0];
    if (r.followed_up_by !== userId && r.closed_by !== userId) {
      throw new ForbiddenException(
        `You do not have permission to close NC ${ncId}`,
      );
    }
  }
  /** scheme_dbs s user id → legacy QRS/TQS user id, matched by email. */
  private async resolveSourceUserId(
    schemeUserId: number,
    source: 'QRS' | 'TQS',
  ): Promise<number | null> {
    const schemeRows = await this.schemeDb.query(
      `SELECT email FROM users WHERE id = ? LIMIT 1`,
      [schemeUserId],
    );
    const email = (schemeRows[0]?.email || '').trim().toLowerCase();
    if (!email) return null;

    const ds = source === 'QRS' ? this.qrsDs : this.tqsDs;
    const rows = await ds.query(
      `SELECT id FROM users WHERE LOWER(email) = ? LIMIT 1`,
      [email],
    );
    return rows.length ? Number(rows[0].id) : null;
  }
  // ═══════════════════════════════════════════════════════════════════
  // GET closure
  // ═══════════════════════════════════════════════════════════════════

  async getClosure(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
  ): Promise<ClosureResponse> {
    const ds = this.dsFor(source);

    const ncRow = await ds.query(
      `SELECT id, status, closed_at FROM nc__ncs WHERE id = ?`,
      [ncId],
    );
    if (!ncRow.length) {
      throw new NotFoundException(`NC ${ncId} not found in ${source}`);
    }
    const nc = ncRow[0];

    const rows = await ds.query(
      `SELECT id,
              ncrentry_id        AS entry_id,
              remarks            AS evidence_received,
              submitted_documents AS submitted_docs,
              result_accepted,
              verification_by    AS verification_by_auditor,
              auditor_name,
              auditor_signature,
              closure_date,
              final_closure_pdf  AS signed_copy_path,
              created_at
       FROM nc_final_closures
       WHERE nc_id = ?
       ORDER BY id ASC`,
      [ncId],
    );

    const first = rows[0] || {};
    const isFinalized = !!nc.closed_at && rows.length > 0;

    return {
      nc_id: ncId,
      source,
      is_finalized: isFinalized,
      finalized_at: nc.closed_at ? new Date(nc.closed_at).toISOString() : null,
      signed_copy_path: first.signed_copy_path || null,
      signature_path: first.auditor_signature || null,
      verification_by_auditor: first.verification_by_auditor || '',
      auditor_name: first.auditor_name || '',
      closure_date: first.closure_date
        ? this.toIsoDate(first.closure_date)
        : null,
      send_to_client: false,
      rows: rows.map((r: any) => ({
        id: r.id,
        entry_id: r.entry_id,
        evidence_received: r.evidence_received || '',
        submitted_docs: r.submitted_docs || '',
        result_accepted: !!r.result_accepted,
        remarks: '',
      })),
    };
  }

  // ═══════════════════════════════════════════════════════════════════
  // SAVE closure (draft or finalize)
  // ═══════════════════════════════════════════════════════════════════

  async saveClosure(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    dto: SaveClosureDto,
    files: {
      signedCopy?: Express.Multer.File;
      signature?: Express.Multer.File;
    },
    currentUserId: number,
  ): Promise<{
    ok: true;
    is_finalized: boolean;
    rows_saved: number;
    entries_closed: number;
    merged_pdf_sha256?: string;     // 🆕 PHASE 2B — hash of the final merged file
    merged_pdf_pages?: number;      // 🆕 PHASE 2B — total pages
    merged_pdf_content_hash?: string; // 🆕 PHASE 2B v2 — hash of verification page content
  }> {
    await this.assertCanEdit(source, ncId, currentUserId);
    // Legacy QRS/TQS store closed_by against THEIR users table, not scheme_dbs .
    const closedById =
      source === 'NEW'
        ? currentUserId
        : await this.resolveSourceUserId(currentUserId, source);

    if (source !== 'NEW' && closedById == null) {
      throw new BadRequestException(
        `Your account is not linked to a ${source} user (by email), so this NC cannot be closed under your name. Ask an admin to add your email in the ${source} system.`,
      );
    }
    this.validateSaveDto(dto);

    const ds = this.dsFor(source);

    if (dto.closed_entry_ids.length > 0) {
      const entryCheck = await ds.query(
        `SELECT id FROM ncr_entries WHERE nc_id = ? AND id IN (?)`,
        [ncId, dto.closed_entry_ids],
      );
      if (entryCheck.length !== dto.closed_entry_ids.length) {
        throw new BadRequestException(
          `One or more findings do not belong to NC ${ncId}`,
        );
      }
    }

    const ncMetaRow = await ds.query(
      `SELECT closed_at FROM nc__ncs WHERE id = ?`,
      [ncId],
    );
    const alreadyFinalized = !!ncMetaRow[0]?.closed_at;
    if (alreadyFinalized && !dto.finalize) {
      this.logger.warn(
        `[NC-CLOSURE] Editing an already-finalized closure for NC ${ncId} as a draft.`,
      );
    }

    // ─── 1. Save uploaded files BEFORE the transaction ───
    let newSignedCopyPath: string | null = null;
    if (files.signedCopy) {
      try {
        const saved = await this.fileStorage.saveClosureSignedCopy(
          source,
          ncId,
          {
            originalname: files.signedCopy.originalname,
            mimetype: files.signedCopy.mimetype,
            buffer: files.signedCopy.buffer,
            size: files.signedCopy.size,
          },
        );
        newSignedCopyPath = saved.dbPath;
      } catch (err: any) {
        throw new BadRequestException(
          `Signed copy upload failed: ${err.message}`,
        );
      }
    }

    let newSignaturePath: string | null = null;
    if (files.signature) {
      try {
        const saved = await this.fileStorage.saveAuditorSignature(source, {
          originalname: files.signature.originalname,
          mimetype: files.signature.mimetype,
          buffer: files.signature.buffer,
          size: files.signature.size,
        });
        newSignaturePath = saved.dbPath;
      } catch (err: any) {
        throw new BadRequestException(
          `Signature upload failed: ${err.message}`,
        );
      }
    }

    // ─── 2. Resolve final file paths (new uploads OR existing in DB) ───
    const oldRows = await ds.query(
      `SELECT DISTINCT final_closure_pdf, auditor_signature
       FROM nc_final_closures WHERE nc_id = ?`,
      [ncId],
    );

    const currentSignedCopy =
      newSignedCopyPath ||
      this.firstNonEmpty(oldRows, 'final_closure_pdf');
    const currentSignature =
      newSignaturePath || this.firstNonEmpty(oldRows, 'auditor_signature');

    // ─── 3. 🆕 PHASE 2B — If finalizing, generate merged PDF BEFORE DB write ───
    let mergedPdfPath: string | null = null;
    let mergedPdfSha256: string | undefined;
    let mergedPdfPages: number | undefined;
    let mergedPdfContentHash: string | undefined; // 🆕 v2

    if (dto.finalize) {
      try {
        const pdfInput = await this.buildPdfInput(
          source,
          ncId,
          dto,
          currentSignedCopy,
          currentSignature,
        );
        const result = await this.pdfService.generateMergedPdf(pdfInput);
        mergedPdfPath = result.dbPath;
        mergedPdfSha256 = result.fileHash;
        mergedPdfPages = result.pages;
        mergedPdfContentHash = result.contentHash;
      } catch (err: any) {
        // PDF generation failure shouldn't lose the user's data — clean up
        // uploaded files and surface a clear error.
        if (newSignedCopyPath) {
          await this.fileStorage
            .deleteNewFile(newSignedCopyPath, source)
            .catch(() => null);
        }
        if (newSignaturePath) {
          await this.fileStorage
            .deleteNewFile(newSignaturePath, source)
            .catch(() => null);
        }
        this.logger.error(
          `[NC-CLOSURE] PDF generation failed for ${source}/NC${ncId}: ${err.message}`,
          err.stack,
        );
        throw new BadRequestException(
          `Could not generate the merged PDF: ${err.message}. Closure not saved.`,
        );
      }
    }

    // ─── 4. Persist closure rows in transaction ───
    const qr = ds.createQueryRunner();
    await qr.connect();
    await qr.startTransaction();

    let rowsSaved = 0;
    let entriesClosed = 0;

    try {
      // On finalize, store the MERGED pdf path. Draft → store signed copy.
      const finalPdfPath = mergedPdfPath || currentSignedCopy;

      await qr.query(`DELETE FROM nc_final_closures WHERE nc_id = ?`, [ncId]);

      for (const entryId of dto.closed_entry_ids) {
        const row = dto.rows.find((r) => r.entry_id === entryId);
        if (!row) continue;

        const entryRow = await qr.query(
          `SELECT nc_type, criteria_clause FROM ncr_entries
           WHERE id = ? AND nc_id = ?`,
          [entryId, ncId],
        );
        const ncType = entryRow[0]?.nc_type || '';
        const clause = entryRow[0]?.criteria_clause || '';

        const result = await qr.query(
          `INSERT INTO nc_final_closures
           (nc_id, ncrentry_id, final_closure_pdf, nc_type, critical_clause,
            submitted_documents, verification_by,
            result_accepted, result_not_accepted, remarks,
            auditor_name, auditor_signature, closure_date,
            created_at, updated_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())`,
          [
            ncId,
            entryId,
            finalPdfPath,
            ncType,
            clause,
            row.submitted_docs || '',
            dto.verification_by_auditor || '',
            row.result_accepted ? 1 : 0,
            row.result_accepted ? 0 : 1,
            row.evidence_received || '',
            dto.auditor_name || '',
            currentSignature,
            dto.closure_date || null,
          ],
        );
        if (result.affectedRows > 0) rowsSaved++;
      }

      if (dto.finalize) {
        // 🆕 Use the per-row status dropdown. Fall back to the accept flag
        //    only when status wasn't sent (older frontend).
        const statusOf = (r: ClosureRowDto): 'open' | 'closed' =>
          r.status ?? (r.result_accepted ? 'closed' : 'open');

        const closeIds = dto.rows
          .filter((r) => statusOf(r) === 'closed')
          .map((r) => r.entry_id);
        const openIds = dto.rows
          .filter((r) => statusOf(r) === 'open')
          .map((r) => r.entry_id);

        if (closeIds.length > 0) {
          const result = await qr.query(
            `UPDATE ncr_entries SET status = 'closed', updated_at = NOW()
             WHERE nc_id = ? AND id IN (?)`,
            [ncId, closeIds],
          );
          entriesClosed = result.affectedRows || 0;
        }

        // 🆕 allow re-opening if the dropdown was flipped back to Open
        if (openIds.length > 0) {
          await qr.query(
            `UPDATE ncr_entries SET status = 'open', updated_at = NOW()
             WHERE nc_id = ? AND id IN (?)`,
            [ncId, openIds],
          );
        }

        const openCount = await qr.query(
          `SELECT COUNT(*) AS cnt FROM ncr_entries
           WHERE nc_id = ? AND status != 'closed'`,
          [ncId],
        );

        if (Number(openCount[0]?.cnt || 0) === 0) {
          await qr.query(
            `UPDATE nc__ncs SET status = 'closed', closed_by = ?,
                                closed_at = NOW(), updated_at = NOW()
             WHERE id = ?`,
            [closedById, ncId],
          );
        } else {
          await qr.query(
            `UPDATE nc__ncs SET closed_by = ?, closed_at = NOW(),
                                updated_at = NOW()
             WHERE id = ?`,
            [closedById, ncId],
          );
        }
      }

      await qr.commitTransaction();
      // 🆕 Email the merged closure PDF to the client (best-effort, non-blocking)
      if (dto.finalize && dto.send_to_client && mergedPdfPath) {
        this.sendClosureEmail(source, ncId, mergedPdfPath, dto, currentUserId)
          .catch((e) =>
            this.logger.warn(
              `[NC-CLOSURE-MAIL] send failed for NC ${ncId}: ${e.message}`,
            ),
          );
      }
      // ─── 5. Cleanup ───
      // On finalize: delete the original signed copy (it's now embedded in merged PDF)
      if (dto.finalize && mergedPdfPath && currentSignedCopy) {
        if (
          currentSignedCopy !== mergedPdfPath &&
          this.fileStorage.isNewPath(currentSignedCopy)
        ) {
          await this.fileStorage
            .deleteNewFile(currentSignedCopy, source)
            .catch((e) => {
              this.logger.warn(
                `Failed to delete original signed copy ${currentSignedCopy}: ${e.message}`,
              );
            });
        }
      }

      // Delete OLD files that were replaced by new uploads (draft re-save)
      if (newSignedCopyPath) {
        await this.deleteIfReplaced(
          oldRows,
          'final_closure_pdf',
          newSignedCopyPath,
          source,
        );
      }
      if (newSignaturePath) {
        await this.deleteIfReplaced(
          oldRows,
          'auditor_signature',
          newSignaturePath,
          source,
        );
      }

      this.logger.log(
        `[NC-CLOSURE] User ${currentUserId} saved closure for ${source}/NC${ncId} ` +
        `— rows=${rowsSaved} finalize=${dto.finalize} closed=${entriesClosed} ` +
        (mergedPdfSha256 ? `merged-sha=${mergedPdfSha256.slice(0, 12)}…` : ''),
      );

      return {
        ok: true,
        is_finalized: dto.finalize,
        rows_saved: rowsSaved,
        entries_closed: entriesClosed,
        merged_pdf_sha256: mergedPdfSha256,
        merged_pdf_pages: mergedPdfPages,
        merged_pdf_content_hash: mergedPdfContentHash,
      };
    } catch (err) {
      await qr.rollbackTransaction();
      if (newSignedCopyPath) {
        await this.fileStorage
          .deleteNewFile(newSignedCopyPath, source)
          .catch(() => null);
      }
      if (newSignaturePath) {
        await this.fileStorage
          .deleteNewFile(newSignaturePath, source)
          .catch(() => null);
      }
      if (mergedPdfPath) {
        await this.fileStorage
          .deleteNewFile(mergedPdfPath, source)
          .catch(() => null);
      }
      this.logger.error(
        `[NC-CLOSURE] Save failed for ${source}/NC${ncId}: ${(err as Error).message}`,
        (err as Error).stack,
      );
      throw err;
    } finally {
      await qr.release();
    }
  }

  // ═══════════════════════════════════════════════════════════════════
  // 🆕 PHASE 2B — Build the PDF input by joining NC + closure data
  // ═══════════════════════════════════════════════════════════════════

  private async buildPdfInput(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    dto: SaveClosureDto,
    signedCopyDbPath: string | null,
    signatureDbPath: string | null,
  ): Promise<any> {
    const ds = this.dsFor(source);

    // Fetch NC + company info.
    // Legacy QRS/TQS schema references the company via client_id / serve_id.
    // The scheme_dbs s (NEW) schema has neither column — it uses company_id —
    // so selecting client_id/serve_id there throws "Unknown column".
    const ncRow =
      source === 'NEW'
        ? await ds.query(
          `SELECT id, NULL AS client_id, NULL AS serve_id
             FROM nc__ncs WHERE id = ?`,
          [ncId],
        )
        : await ds.query(
          `SELECT id, client_id, serve_id
             FROM nc__ncs WHERE id = ?`,
          [ncId],
        );
    if (!ncRow.length) {
      throw new Error(`NC ${ncId} not found`);
    }

    // Resolve company name from the right legacy table.
    // QRS schema: `clients` (for direct clients) and `newsurve` (for surveillance audits).
    // Wrap in try/catch so missing tables/columns never block the PDF generation.
    let companyName = `NC #${ncId}`;
    let auditeeFromNc = '';

    try {
      const ncFull = await ds.query(
        `SELECT auditee_name FROM nc__ncs WHERE id = ?`,
        [ncId],
      );
      auditeeFromNc = ncFull[0]?.auditee_name || '';
    } catch (e: any) {
      this.logger.warn(`Could not read auditee_name from nc__ncs: ${e.message}`);
    }

    // 🆕 NEW NCs (scheme_dbs ) reference the company via `company_id`, joined to
    // the `companies` table — NOT the legacy `clients`/`newsurve` tables.
    if (source === 'NEW') {
      try {
        const companyRows = await ds.query(
          `SELECT c.name
             FROM nc__ncs n
             LEFT JOIN companies c ON c.id = n.company_id
            WHERE n.id = ? LIMIT 1`,
          [ncId],
        );
        if (companyRows[0]?.name) {
          companyName = companyRows[0].name;
        }
      } catch (e: any) {
        this.logger.warn(
          `Could not fetch company name for NEW NC ${ncId}: ${e.message}`,
        );
      }
    } else if (ncRow[0].client_id) {
      try {
        const clientRow = await ds.query(
          `SELECT * FROM clients WHERE id = ? LIMIT 1`,
          [ncRow[0].client_id],
        );
        if (clientRow.length) {
          companyName =
            clientRow[0].company_name ||
            clientRow[0].name ||
            clientRow[0].client_name ||
            companyName;
        }
      } catch (e: any) {
        this.logger.warn(`Could not fetch from clients table: ${e.message}`);
      }
    } else if (ncRow[0].serve_id) {
      try {
        const surveRow = await ds.query(
          `SELECT * FROM newsurve WHERE id = ? LIMIT 1`,
          [ncRow[0].serve_id],
        );
        if (surveRow.length) {
          companyName =
            surveRow[0].company_name ||
            surveRow[0].name ||
            surveRow[0].auditee ||
            surveRow[0].auditee_name ||
            companyName;
        }
      } catch (e: any) {
        this.logger.warn(`Could not fetch from newsurve table: ${e.message}`);
      }
    }

    // Final fallback: use auditee name from nc__ncs (always available)
    if (companyName === `NC #${ncId}` && auditeeFromNc) {
      companyName = auditeeFromNc;
    }

    // Fetch ALL entries for this NC so we can compute originalIndex
    // (the "#" column in the verification page reflects the position in
    //  the full findings list, not the position in the closed list)
    const allEntries = await ds.query(
      `SELECT id, nc_type, ncr_statement, criteria_clause
       FROM ncr_entries WHERE nc_id = ? ORDER BY id ASC`,
      [ncId],
    );
    const entryIndexById = new Map<number, number>();
    const entryById = new Map<number, any>();
    allEntries.forEach((e: any, idx: number) => {
      entryIndexById.set(e.id, idx + 1); // 1-based
      entryById.set(e.id, e);
    });

    // Resolve absolute paths for signed copy + signature
    const signedCopyAbsPath = signedCopyDbPath
      ? this.fileStorage.resolveAbsolutePath(signedCopyDbPath, source)
      : null;
    const signatureAbsPath = signatureDbPath
      ? this.fileStorage.resolveAbsolutePath(signatureDbPath, source)
      : null;

    return {
      source,
      ncId,
      ncCode: `NC-${source}-${String(ncId).padStart(6, '0')}`,
      companyName,
      auditorName: dto.auditor_name || '',
      closureDate: dto.closure_date || new Date().toISOString().slice(0, 10),
      verificationText: dto.verification_by_auditor || '',
      findings: dto.closed_entry_ids.map((entryId) => {
        const e = entryById.get(entryId);
        const row = dto.rows.find((r) => r.entry_id === entryId);
        return {
          originalIndex: entryIndexById.get(entryId) || 0,
          ncType: e?.nc_type || '',
          clause: e?.criteria_clause || '',
          evidenceReceived: row?.evidence_received || '',
          submittedDocs: row?.submitted_docs || '',
          accepted: !!row?.result_accepted,
        };
      }),
      signedCopyAbsPath,
      signatureAbsPath,
    };
  }

  /** @deprecated kept for backward compat — no longer used in template v2 */
  private async resolveStandardNames(
    source: 'QRS' | 'TQS' | 'NEW',
    standardJson: string | null,
  ): Promise<string[]> {
    if (!standardJson) return [];
    const ds = this.dsFor(source);
    let ids: number[] = [];
    try {
      const parsed = JSON.parse(standardJson);
      if (Array.isArray(parsed)) {
        ids = parsed.map((x) => Number(x)).filter((x) => !isNaN(x));
      }
    } catch {
      return [];
    }
    if (!ids.length) return [];

    const rows = await ds.query(
      `SELECT name FROM nc_standards WHERE id IN (?)`,
      [ids],
    );
    return rows.map((r: any) => r.name);
  }

  // ═══════════════════════════════════════════════════════════════════
  // Delete draft closure
  // ═══════════════════════════════════════════════════════════════════

  async deleteDraftClosure(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    currentUserId: number,
  ): Promise<{ ok: true; deleted_rows: number }> {
    await this.assertCanEdit(source, ncId, currentUserId);

    const ds = this.dsFor(source);

    const ncRow = await ds.query(
      `SELECT closed_at FROM nc__ncs WHERE id = ?`,
      [ncId],
    );
    if (ncRow[0]?.closed_at) {
      throw new BadRequestException(
        `Cannot delete a finalized closure. Reopen the NC first.`,
      );
    }

    const oldRows = await ds.query(
      `SELECT DISTINCT final_closure_pdf, auditor_signature
       FROM nc_final_closures WHERE nc_id = ?`,
      [ncId],
    );

    const result = await ds.query(
      `DELETE FROM nc_final_closures WHERE nc_id = ?`,
      [ncId],
    );

    for (const r of oldRows) {
      if (r.final_closure_pdf) {
        await this.fileStorage
          .deleteNewFile(r.final_closure_pdf, source)
          .catch(() => null);
      }
      if (r.auditor_signature) {
        await this.fileStorage
          .deleteNewFile(r.auditor_signature, source)
          .catch(() => null);
      }
    }

    this.logger.log(
      `[NC-CLOSURE] User ${currentUserId} discarded draft closure for ${source}/NC${ncId}`,
    );

    return { ok: true, deleted_rows: result.affectedRows || 0 };
  }

  // ═══════════════════════════════════════════════════════════════════
  // Open a closure file
  // ═══════════════════════════════════════════════════════════════════

  async openClosureFile(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    kind: 'signed_copy' | 'signature',
  ): Promise<{
    stream: Readable;
    size: number;
    mimeType: string;
    filename: string;
  }> {
    const ds = this.dsFor(source);

    const column =
      kind === 'signed_copy' ? 'final_closure_pdf' : 'auditor_signature';

    const rows = await ds.query(
      `SELECT ${column} AS path FROM nc_final_closures
       WHERE nc_id = ? AND ${column} IS NOT NULL LIMIT 1`,
      [ncId],
    );

    if (!rows.length || !rows[0].path) {
      throw new NotFoundException(`No ${kind} found for NC ${ncId}`);
    }

    return this.fileStorage.openFile(rows[0].path, source);
  }

  // ═══════════════════════════════════════════════════════════════════
  // Helpers
  // ═══════════════════════════════════════════════════════════════════

  private validateSaveDto(dto: SaveClosureDto): void {
    if (!Array.isArray(dto.closed_entry_ids)) {
      throw new BadRequestException('closed_entry_ids must be an array');
    }
    if (dto.closed_entry_ids.length === 0) {
      throw new BadRequestException(
        'At least one finding must be selected to save closure',
      );
    }
    if (!Array.isArray(dto.rows)) {
      throw new BadRequestException('rows must be an array');
    }
    for (const id of dto.closed_entry_ids) {
      if (!dto.rows.find((r) => r.entry_id === id)) {
        throw new BadRequestException(`Missing row data for entry_id ${id}`);
      }
    }
    if (dto.finalize) {
      if (!dto.auditor_name?.trim()) {
        throw new BadRequestException(
          'Auditor name is required to finalize closure',
        );
      }
      if (!dto.closure_date) {
        throw new BadRequestException(
          'Closure date is required to finalize closure',
        );
      }
    }
  }

  private firstNonEmpty(rows: any[], column: string): string | null {
    for (const r of rows) {
      if (r[column]) return r[column];
    }
    return null;
  }

  private async deleteIfReplaced(
    oldRows: any[],
    column: string,
    newPath: string,
    source: 'QRS' | 'TQS' | 'NEW',
  ): Promise<void> {
    for (const r of oldRows) {
      const oldPath = r[column];
      if (
        oldPath &&
        oldPath !== newPath &&
        this.fileStorage.isNewPath(oldPath)
      ) {
        await this.fileStorage.deleteNewFile(oldPath, source).catch((e) => {
          this.logger.warn(
            `Failed to delete old ${column} ${oldPath}: ${e.message}`,
          );
        });
      }
    }
  }

  private toIsoDate(d: any): string {
    if (!d) return '';
    if (typeof d === 'string') return d.slice(0, 10);
    if (d instanceof Date) return d.toISOString().slice(0, 10);
    return '';
  }
  // ═══════════════════════════════════════════════════════════════════
  // 🆕 Professional closure-email body (shared by finalize + resend)
  // ═══════════════════════════════════════════════════════════════════
  private buildClosureEmailHtml(auditorName?: string | null): string {
    const signoff = (auditorName || '').trim() || 'Audit Team';
    return `
<div style="font-family:Arial,Helvetica,sans-serif;font-size:14px;line-height:1.6;color:#1f2937;max-width:600px;">
  <p style="margin:0 0 16px;">Dear Sir / Madam,</p>

  <p style="margin:0 0 16px;">
    We are pleased to inform you that the non-conformities raised during your
    recent audit have been reviewed and verified, and the corresponding closure
    has now been finalised.
  </p>

  <p style="margin:0 0 16px;">
    Please find attached the <strong>Non-Conformity Closure &amp; Verification
    Report</strong>, which documents the evidence received, the verification
    carried out by the auditor, and the final acceptance of each finding.
    This report serves as the official record confirming that the relevant
    non-conformities have been satisfactorily addressed and closed.
  </p>

  <p style="margin:0 0 16px;">
    We kindly request that you retain this document for your records. Should you
    have any questions regarding the closure or require any further
    clarification, please do not hesitate to contact us.
  </p>

  <p style="margin:0 0 4px;">
    Thank you for your cooperation throughout the certification process.
  </p>

  <p style="margin:24px 0 0;">
    Kind regards,<br>
    <strong>${signoff}</strong><br>
    <span style="color:#6b7280;">Quality Registrar Systems</span>
  </p>
</div>`.trim();
  }
  // 🆕 Email the finalized merged closure PDF to the client
  // ═══════════════════════════════════════════════════════════════════
  private async sendClosureEmail(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    mergedPdfDbPath: string,
    dto: SaveClosureDto,
    currentUserId: number,
  ): Promise<void> {
    const ds = this.dsFor(source);

    const clean = (s?: string) =>
      (s || '')
        .split(',')
        .map((x) => x.trim())
        .filter(Boolean)
        .join(', ') || null;

    // Only send to the typed "To". No auto-fill from the record.
    const clientEmail = clean(dto.closure_to);

    if (!clientEmail) {
      this.logger.log(
        `[NC-CLOSURE-MAIL] no "To" typed for NC ${ncId} — skipping email`,
      );
      return;
    }

    // Read the merged PDF off disk into a Buffer for the attachment
    const { stream } = await this.fileStorage.openFile(mergedPdfDbPath, source);
    const chunks: Buffer[] = [];
    for await (const c of stream) chunks.push(c as Buffer);
    const pdf = Buffer.concat(chunks);

    await this.mailsService.sendAsUser(currentUserId, {
      to: clientEmail,
      cc: clean(dto.closure_cc) || undefined,
      bcc: clean(dto.closure_bcc) || undefined,
      subject: `Non-Conformity Closure & Verification Report`,
      html: this.buildClosureEmailHtml(dto.auditor_name),
      attachments: [
        {
          filename: `NC-${ncId}-closure.pdf`,
          content: pdf,
          contentType: 'application/pdf',
        },
      ],
    });

    this.logger.log(
      `[NC-CLOSURE-MAIL] sent closure for NC ${ncId} → ${clientEmail}`,
    );
  }
  // ═══════════════════════════════════════════════════════════════════
  // 🆕 Standalone "Send now" — email the existing merged PDF on demand
  // ═══════════════════════════════════════════════════════════════════
  async resendClosureEmail(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    dto: { closure_to?: string; closure_cc?: string; closure_bcc?: string },
    currentUserId: number,
  ): Promise<{ ok: true; sent_to: string }> {
    await this.assertCanEdit(source, ncId, currentUserId);

    const clean = (s?: string) =>
      (s || '').split(',').map((x) => x.trim()).filter(Boolean).join(', ') || null;

    const to = clean(dto.closure_to);
    if (!to) {
      throw new BadRequestException('Enter at least one "To" address.');
    }

    const ds = this.dsFor(source);
    const rows = await ds.query(
      `SELECT final_closure_pdf AS path FROM nc_final_closures
       WHERE nc_id = ? AND final_closure_pdf IS NOT NULL LIMIT 1`,
      [ncId],
    );
    if (!rows.length || !rows[0].path) {
      throw new NotFoundException(
        `No finalized closure PDF found for NC ${ncId}. Finalize the closure first.`,
      );
    }

    const auditorRow = await ds.query(
      `SELECT auditor_name FROM nc_final_closures WHERE nc_id = ? LIMIT 1`,
      [ncId],
    );
    const auditorName = auditorRow[0]?.auditor_name || 'Audit Team';

    const { stream } = await this.fileStorage.openFile(rows[0].path, source);
    const chunks: Buffer[] = [];
    for await (const c of stream) chunks.push(c as Buffer);
    const pdf = Buffer.concat(chunks);

    await this.mailsService.sendAsUser(currentUserId, {
      to,
      cc: clean(dto.closure_cc) || undefined,
      bcc: clean(dto.closure_bcc) || undefined,
      subject: `Non-Conformity Closure & Verification Report`,
      html: this.buildClosureEmailHtml(auditorName),
      attachments: [
        { filename: `NC-${ncId}-closure.pdf`, content: pdf, contentType: 'application/pdf' },
      ],
    });

    this.logger.log(`[NC-CLOSURE-MAIL] manual resend NC ${ncId} → ${to}`);
    return { ok: true, sent_to: to };
  }

  // ═══════════════════════════════════════════════════════════════════
  // 🆕 Send the NC's uploaded evidence files as a SEPARATE email.
  //     Independent of the closure email — no merged PDF here.
  // ═══════════════════════════════════════════════════════════════════
  async sendEvidenceEmail(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    dto: { evidence_to?: string; evidence_cc?: string; evidence_bcc?: string },
    currentUserId: number,
  ): Promise<{ ok: true; sent_to: string; files_attached: number }> {
    await this.assertCanEdit(source, ncId, currentUserId);

    const clean = (s?: string) =>
      (s || '').split(',').map((x) => x.trim()).filter(Boolean).join(', ') || null;

    const to = clean(dto.evidence_to);
    if (!to) {
      throw new BadRequestException('Enter at least one "To" address.');
    }

    const ds = this.dsFor(source);

    // Collect every uploaded evidence file for this NC's findings.
    const rows = await ds.query(
      `SELECT id, document_path
       FROM ncr_entries
      WHERE nc_id = ? AND document_path IS NOT NULL AND document_path <> ''`,
      [ncId],
    );

    const attachments: Array<{
      filename: string;
      content: Buffer;
      contentType: string;
    }> = [];

    for (const r of rows as any[]) {
      try {
        const { stream, filename, mimeType } = await this.fileStorage.openFile(
          r.document_path,
          source,
        );
        const chunks: Buffer[] = [];
        for await (const c of stream) chunks.push(c as Buffer);
        attachments.push({
          filename: `evidence-entry${r.id}-${filename}`,
          content: Buffer.concat(chunks),
          contentType: mimeType || 'application/octet-stream',
        });
      } catch (e: any) {
        this.logger.warn(
          `[NC-EVIDENCE-MAIL] skipped evidence for entry ${r.id} (NC ${ncId}): ${e.message}`,
        );
      }
    }

    if (attachments.length === 0) {
      throw new BadRequestException(
        `No evidence files have been uploaded for NC ${ncId}.`,
      );
    }

    await this.mailsService.sendAsUser(currentUserId, {
      to,
      cc: clean(dto.evidence_cc) || undefined,
      bcc: clean(dto.evidence_bcc) || undefined,
      subject: `Audit Evidence — NC-${source}-${String(ncId).padStart(6, '0')}`,
      html: this.buildEvidenceEmailHtml(),
      attachments,
    });

    this.logger.log(
      `[NC-EVIDENCE-MAIL] NC ${ncId} → ${to} (${attachments.length} file(s))`,
    );

    return { ok: true, sent_to: to, files_attached: attachments.length };
  }

  // Simple body for the evidence email (kept separate from closure body).
  private buildEvidenceEmailHtml(): string {
    return `
<div style="font-family:Arial,Helvetica,sans-serif;font-size:14px;line-height:1.6;color:#1f2937;max-width:600px;">
  <p style="margin:0 0 16px;">Dear Sir / Madam,</p>
  <p style="margin:0 0 16px;">
    Please find attached the supporting evidence documents submitted against the
    non-conformities raised during your recent audit.
  </p>
  <p style="margin:0 0 16px;">
    Kindly retain these for your records. Should you require any clarification,
    please do not hesitate to contact us.
  </p>
  <p style="margin:24px 0 0;">
    Kind regards,<br>
    <span style="color:#6b7280;">Quality Registrar Systems</span>
  </p>
</div>`.trim();
  }
  // ═══════════════════════════════════════════════════════════════════
  // 🆕 Finalized-closures datatable
  // ═══════════════════════════════════════════════════════════════════
  async listFinalizedClosures(filters: {
    source?: 'QRS' | 'TQS' | 'NEW' | 'All';
    nc_type?: string;
    date_from?: string;
    date_to?: string;
    search?: string;
  }): Promise<any[]> {
    const want = (s: string) =>
      !filters.source || filters.source === 'All' || filters.source === s;

    const runNew = async () => {
      const rows = await this.schemeDb
        .query(
          `SELECT n.id AS nc_id, n.nc_type, n.closed_at,
                  c.name AS company_name, s.schedule_date AS audit_date,
                  MIN(fc.final_closure_pdf) AS merged_pdf,
                  MIN(fc.closure_date) AS closure_date,
                  MIN(fc.auditor_name) AS auditor_name,
                  SUM(CASE WHEN e.status = 'closed' THEN 1 ELSE 0 END) AS findings_closed,
                  SUM(CASE WHEN e.document_path IS NOT NULL THEN 1 ELSE 0 END) AS evidence_count
             FROM nc__ncs n
             LEFT JOIN companies c            ON c.id = n.company_id
             LEFT JOIN audit_schedule_rows ar ON ar.id = n.audit_id
             LEFT JOIN audit_schedules s      ON s.id = ar.schedule_id
             LEFT JOIN nc_final_closures fc   ON fc.nc_id = n.id
             LEFT JOIN ncr_entries e          ON e.nc_id = n.id
            WHERE n.closed_at IS NOT NULL
            GROUP BY n.id`,
        )
        .catch((e: any) => {
          this.logger.warn(`[CLOSURE-LIST] NEW failed: ${e.message}`);
          return [];
        });
      return rows.map((r: any) => this.shapeClosureRow('NEW', r));
    };

    const runLegacy = async (ds: DataSource, source: 'QRS' | 'TQS') => {
      const rows = await ds
        .query(
          `SELECT n.id AS nc_id, n.nc_type, n.closed_at,
                  COALESCE(cl.company_name, sv.company_name) AS company_name,
                  COALESCE(cl.auditdate, sv.auditdate) AS audit_date,
                  MIN(fc.final_closure_pdf) AS merged_pdf,
                  MIN(fc.closure_date) AS closure_date,
                  MIN(fc.auditor_name) AS auditor_name,
                  SUM(CASE WHEN e.status = 'closed' THEN 1 ELSE 0 END) AS findings_closed,
                  SUM(CASE WHEN e.document_path IS NOT NULL THEN 1 ELSE 0 END) AS evidence_count
             FROM nc__ncs n
             LEFT JOIN clients  cl ON cl.id = n.client_id
             LEFT JOIN newsurve sv ON sv.id = n.serve_id
             LEFT JOIN nc_final_closures fc ON fc.nc_id = n.id
             LEFT JOIN ncr_entries e        ON e.nc_id = n.id
            WHERE n.closed_at IS NOT NULL
            GROUP BY n.id`,
        )
        .catch((e: any) => {
          this.logger.warn(`[CLOSURE-LIST] ${source} failed: ${e.message}`);
          return [];
        });
      return rows.map((r: any) => this.shapeClosureRow(source, r));
    };

    const [nw, qrs, tqs] = await Promise.all([
      want('NEW') ? runNew() : Promise.resolve([]),
      want('QRS') ? runLegacy(this.qrsDs, 'QRS') : Promise.resolve([]),
      want('TQS') ? runLegacy(this.tqsDs, 'TQS') : Promise.resolve([]),
    ]);

    let out = [...nw, ...qrs, ...tqs];

    if (filters.nc_type && filters.nc_type !== 'All') {
      out = out.filter(
        (r) => (r.nc_type || '').toLowerCase() === filters.nc_type!.toLowerCase(),
      );
    }
    if (filters.date_from)
      out = out.filter((r) => !r.audit_date || r.audit_date >= filters.date_from!);
    if (filters.date_to)
      out = out.filter((r) => !r.audit_date || r.audit_date <= filters.date_to!);

    const s = (filters.search || '').trim().toLowerCase();
    if (s)
      out = out.filter((r) => (r.company_name || '').toLowerCase().includes(s));

    out.sort(
      (a, b) =>
        new Date(b.closure_date ?? 0).getTime() -
        new Date(a.closure_date ?? 0).getTime(),
    );
    return out;
  }

  private shapeClosureRow(source: 'QRS' | 'TQS' | 'NEW', r: any) {
    return {
      source,
      nc_id: Number(r.nc_id),
      nc_code: `NC-${source}-${String(r.nc_id).padStart(6, '0')}`,
      company_name: (r.company_name || '').trim() || null,
      nc_type: r.nc_type ?? null,
      audit_date: r.audit_date ? this.toIsoDate(r.audit_date) : null,
      closure_date: r.closure_date ? this.toIsoDate(r.closure_date) : null,
      auditor_name: (r.auditor_name || '').trim() || null,
      findings_closed: Number(r.findings_closed || 0),
      evidence_count: Number(r.evidence_count || 0),
      merged_pdf_path: r.merged_pdf || null,
    };
  }
  private async userIsSuperAdmin(userId: number): Promise<boolean> {
    if (!userId) return false;
    const rows = await this.schemeDb.query(
      `SELECT 1
         FROM users u
         INNER JOIN user_roles ur ON ur.user_id = u.id
         INNER JOIN roles r       ON r.id = ur.role_id
        WHERE u.id = ? AND LOWER(r.name) = 'super-admin'
        LIMIT 1`,
      [userId],
    );
    return (rows as any[]).length > 0;
  }

  /**
   * Delete a FINALIZED closure (super-admin only) and reopen the NC.
   * - removes nc_final_closures rows
   * - deletes the merged PDF + signature files (new-format only)
   * - sets the NC's findings back to `open`
   * - clears nc__ncs.status/closed_at/closed_by
   */
  async deleteFinalizedClosure(
    source: 'QRS' | 'TQS' | 'NEW',
    ncId: number,
    currentUserId: number,
  ): Promise<{ ok: true; reopened: boolean; deleted_rows: number }> {
    if (!(await this.userIsSuperAdmin(currentUserId))) {
      throw new ForbiddenException(
        'Only a super-admin can delete a final closure.',
      );
    }

    const ds = this.dsFor(source);

    const ncRow = await ds.query(`SELECT id FROM nc__ncs WHERE id = ?`, [ncId]);
    if (!ncRow.length) {
      throw new NotFoundException(`NC ${ncId} not found in ${source}`);
    }

    // Grab file paths before deleting the rows.
    const oldRows = await ds.query(
      `SELECT DISTINCT final_closure_pdf, auditor_signature
         FROM nc_final_closures WHERE nc_id = ?`,
      [ncId],
    );

    const qr = ds.createQueryRunner();
    await qr.connect();
    await qr.startTransaction();
    let deletedRows = 0;
    try {
      const res = await qr.query(
        `DELETE FROM nc_final_closures WHERE nc_id = ?`,
        [ncId],
      );
      deletedRows = res?.affectedRows ?? 0;

      // Reopen all findings and the NC itself.
      await qr.query(
        `UPDATE ncr_entries SET status = 'open', updated_at = NOW()
          WHERE nc_id = ?`,
        [ncId],
      );
      await qr.query(
        `UPDATE nc__ncs
            SET status = 'open', closed_at = NULL, closed_by = NULL,
                updated_at = NOW()
          WHERE id = ?`,
        [ncId],
      );

      await qr.commitTransaction();
    } catch (err) {
      await qr.rollbackTransaction();
      this.logger.error(
        `[NC-CLOSURE] super-admin delete failed for ${source}/NC${ncId}: ${(err as Error).message}`,
      );
      throw err;
    } finally {
      await qr.release();
    }

    // Best-effort file cleanup (new-format files only; legacy untouched).
    for (const r of oldRows) {
      if (r.final_closure_pdf && this.fileStorage.isNewPath(r.final_closure_pdf)) {
        await this.fileStorage
          .deleteNewFile(r.final_closure_pdf, source)
          .catch(() => null);
      }
      if (r.auditor_signature && this.fileStorage.isNewPath(r.auditor_signature)) {
        await this.fileStorage
          .deleteNewFile(r.auditor_signature, source)
          .catch(() => null);
      }
    }

    this.logger.log(
      `[NC-CLOSURE] super-admin ${currentUserId} deleted finalized closure ${source}/NC${ncId} — NC reopened (rows=${deletedRows})`,
    );
    return { ok: true, reopened: true, deleted_rows: deletedRows };
  }


}