import {
  Injectable,
  NotFoundException,
  BadRequestException,
  ForbiddenException,
  Logger,
} from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource, In } from 'typeorm';

import { ChecklistTemplate } from '../entities/checklist-template.entity';
import { ChecklistTemplateItem } from '../entities/checklist-template-item.entity';
import { AuditChecklist, AuditChecklistStatus } from '../entities/audit-checklist.entity';
import { AuditChecklistItem, ChecklistItemStatus } from '../entities/audit-checklist-item.entity';
import { ChecklistItemHistory, ChecklistHistoryAction } from '../entities/checklist-item-history.entity';
import { AuditScheduleRow } from '../../audit-schedules/entities/audit-schedule-row.entity';
import { AuditRequest } from '../../audit-requests/entities/audit-request.entity';

import { ChecklistStorageService } from './checklist-storage.service';
import { ChecklistPdfService } from './checklist-pdf.service';
import { NotificationsService } from '../../notifications/notifications.service';
// ⚠ ADD these four values to ../../notifications/enums/notification-type.enum.ts
// (see README.md — one small copy-paste):
//   CHECKLIST_READY            = 'checklist_ready',
//   CHECKLIST_DOC_UPLOADED     = 'checklist_doc_uploaded',
//   CHECKLIST_CLIENT_SUBMITTED = 'checklist_client_submitted',
//   CHECKLIST_REVIEW_RESULT    = 'checklist_review_result',
import { NotificationType } from '../../notifications/enums/notification-type.enum';
import { MailsService } from '../../mails/mails.service';
import { NotifyClientDto } from '../dto/notify-client.dto';

const CLIENT_PORTAL_URL = process.env.CLIENT_PORTAL_URL || process.env.FRONTEND_URL || '';
const ADMIN_NOTIFY_EMAIL = process.env.CHECKLIST_ADMIN_NOTIFY_EMAIL || ''; // optional cc for client submissions

const EMAIL_RE = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

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

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
    private readonly storage: ChecklistStorageService,
    private readonly pdf: ChecklistPdfService,
    private readonly notifications: NotificationsService,
    private readonly mailsService: MailsService,
  ) { }

  // ==========================================================================
  // TEMPLATE MANAGEMENT - full CRUD, powers the admin templates page
  // (unchanged from the previous draft)
  // ==========================================================================

  /**
   * @param userId  current requester (from JWT).
   * @param onlyMine  when true, restricts to templates this user generated
   *   (created_by = userId). Frontend sends this for any user who lacks the
   *   "view-all" permission on the checklist-templates module — mirrors the
   *   My Audits "assigned to me only" scoping, applied to templates instead.
   */
  async listTemplates(userId?: number, onlyMine?: boolean): Promise<ChecklistTemplate[]> {
    const qb = this.dataSource
      .getRepository(ChecklistTemplate)
      .createQueryBuilder('t')
      .leftJoinAndSelect('t.items', 'items', 'items.is_active = 1')
      .leftJoinAndSelect('t.standard', 'standard')
      .leftJoinAndSelect('t.created_by_user', 'created_by_user');

    if (onlyMine && userId) {
      qb.andWhere('t.created_by = :userId', { userId });
    }

    return qb
      .orderBy('t.is_generic', 'DESC')
      .addOrderBy('t.name', 'ASC')
      .addOrderBy('items.sort_order', 'ASC')
      .getMany();
  }

  async createTemplate(
    dto: {
      name: string;
      is_generic: boolean;
      standard_id: number | null;
      standard_ids?: number[] | null;
      items: { item_text: string; sort_order: number }[];
    },
    userId?: number,
  ): Promise<ChecklistTemplate> {
    if (!dto.name?.trim()) throw new BadRequestException('Template name is required.');
    const ids = dto.standard_ids?.length ? dto.standard_ids : (dto.standard_id ? [dto.standard_id] : []);
    if (!dto.is_generic && ids.length === 0) {
      throw new BadRequestException('Select at least one standard, or mark this template as generic.');
    }
    if (!dto.items || dto.items.length === 0) {
      throw new BadRequestException('Add at least one checklist item.');
    }

    return this.dataSource.transaction(async (manager) => {
      const template = manager.create(ChecklistTemplate, {
        name: dto.name.trim(),
        is_generic: dto.is_generic,
        // Keep standard_id set to the first selected for backward compat
        standard_id: dto.is_generic ? null : (ids[0] ?? null),
        standard_ids: dto.is_generic ? null : (ids.length > 0 ? ids : null),
        created_by: userId ?? null,
      });
      await manager.save(template);

      const items = dto.items.map((i) =>
        manager.create(ChecklistTemplateItem, {
          template_id: template.id,
          item_text: i.item_text.trim(),
          sort_order: i.sort_order,
        }),
      );
      await manager.save(items);

      this.logger.log(`✓ Template "${template.name}" created (id=${template.id}, ${items.length} items)`);
      return { ...template, items } as ChecklistTemplate;
    });
  }

  async updateTemplate(
    templateId: number,
    dto: {
      name: string;
      is_generic: boolean;
      standard_id: number | null;
      standard_ids?: number[] | null;
      items: { id?: number; item_text: string; sort_order: number }[];
    },
  ): Promise<ChecklistTemplate> {
    if (!dto.name?.trim()) throw new BadRequestException('Template name is required.');
    if (!dto.items || dto.items.length === 0) {
      throw new BadRequestException('Add at least one checklist item.');
    }
    const ids = dto.standard_ids?.length ? dto.standard_ids : (dto.standard_id ? [dto.standard_id] : []);

    return this.dataSource.transaction(async (manager) => {
      const template = await manager.findOne(ChecklistTemplate, { where: { id: templateId } });
      if (!template) throw new NotFoundException(`Template ${templateId} not found`);

      template.name = dto.name.trim();
      template.is_generic = dto.is_generic;
      template.standard_id = dto.is_generic ? null : (ids[0] ?? null);
      template.standard_ids = dto.is_generic ? null : (ids.length > 0 ? ids : null);
      await manager.save(template);

      const existingItems = await manager.find(ChecklistTemplateItem, { where: { template_id: templateId } as any });
      const incomingIds = new Set(dto.items.filter((i) => i.id).map((i) => i.id));

      const toRemove = existingItems.filter((i) => !incomingIds.has(i.id));
      for (const item of toRemove) {
        const inUse = await manager.count(AuditChecklistItem, { where: { template_item_id: item.id } as any });
        if (inUse === 0) {
          // Never used on a real audit — safe to hard-delete.
          await manager.delete(ChecklistTemplateItem, item.id);
        } else {
          // Used on an audit — can't delete (would corrupt history), so hide it.
          await manager.update(ChecklistTemplateItem, item.id, { is_active: false } as any);
        }
      }

      for (const incoming of dto.items.filter((i) => i.id)) {
        await manager.update(ChecklistTemplateItem, incoming.id, {
          item_text: incoming.item_text.trim(),
          sort_order: incoming.sort_order,
        });
      }

      const newItems = dto.items
        .filter((i) => !i.id)
        .map((i) =>
          manager.create(ChecklistTemplateItem, {
            template_id: templateId,
            item_text: i.item_text.trim(),
            sort_order: i.sort_order,
          }),
        );
      if (newItems.length) await manager.save(newItems);

      const finalItems = await manager.find(ChecklistTemplateItem, {
        where: { template_id: templateId, is_active: true } as any,
        order: { sort_order: 'ASC' } as any,
      });

      this.logger.log(`✓ Template ${templateId} updated (${finalItems.length} items)`);
      return { ...template, items: finalItems };
    });
  }

  async deleteTemplate(templateId: number): Promise<void> {
    const repo = this.dataSource.getRepository(ChecklistTemplate);
    const template = await repo.findOne({ where: { id: templateId }, relations: ['items'] });
    if (!template) throw new NotFoundException(`Template ${templateId} not found`);

    // Block deletion if any of this template's items have been used on a real audit.
    const itemIds = (template.items || []).map((i) => i.id);
    const inUse = itemIds.length
      ? await this.dataSource.getRepository(AuditChecklistItem).count({
        where: { template_item_id: In(itemIds) } as any,
      })
      : 0;

    if (inUse > 0) {
      throw new BadRequestException(
        'This template has been used on audits and cannot be deleted. Edit it instead.',
      );
    }

    await repo.remove(template); // items cascade — safe, nothing references them
    this.logger.log(`✓ Template ${templateId} deleted`);
  }

  async getAvailableTemplates(auditScheduleRowId: number): Promise<ChecklistTemplate[]> {
    const row = await this.dataSource.getRepository(AuditScheduleRow).findOne({
      where: { id: auditScheduleRowId },
      relations: ['standards'],
    });
    if (!row) throw new NotFoundException(`Audit ${auditScheduleRowId} not found`);

    const standardIds = ((row as any).standards || []).map((s: any) => s.id);

    const qb = this.dataSource
      .getRepository(ChecklistTemplate)
      .createQueryBuilder('t')
      .leftJoinAndSelect('t.items', 'items', 'items.is_active = 1')
      .leftJoinAndSelect('t.standard', 'standard')
      .where('t.is_generic = 1');

    if (standardIds.length > 0) {
      // Match templates by the legacy standard_id column
      qb.orWhere('t.standard_id IN (:...standardIds)', { standardIds });
      // Also match templates that use the new standard_ids JSON array
      for (let i = 0; i < standardIds.length; i++) {
        qb.orWhere(`JSON_CONTAINS(t.standard_ids, :sid${i})`, {
          [`sid${i}`]: JSON.stringify(standardIds[i]),
        });
      }
    }

    return qb.orderBy('t.is_generic', 'DESC').getMany();
  }

  // ==========================================================================
  // BUILD - auditor picks a standard, checkmarks items, submits for review
  // ==========================================================================

  async buildChecklist(
    auditScheduleRowId: number,
    standardId: number,
    selectedTemplateItemIds: number[],
    builtByUserId: number,
  ): Promise<AuditChecklist> {
    const created = await this.dataSource.transaction(async (manager) => {
      const row = await manager.findOne(AuditScheduleRow, {
        where: { id: auditScheduleRowId },
        relations: ['company'],
      });
      if (!row) throw new NotFoundException(`Audit ${auditScheduleRowId} not found`);

      const existing = await manager.findOne(AuditChecklist, {
        where: { audit_schedule_row_id: auditScheduleRowId, standard_id: standardId } as any,
      });
      if (existing) {
        throw new BadRequestException('A checklist for this standard already exists on this audit.');
      }

      if (selectedTemplateItemIds.length === 0) {
        throw new BadRequestException('Select at least one checklist item.');
      }

      const items = await manager.findByIds(ChecklistTemplateItem, selectedTemplateItemIds);
      if (items.length !== selectedTemplateItemIds.length) {
        throw new BadRequestException('One or more selected items could not be found.');
      }

      const checklist = manager.create(AuditChecklist, {
        audit_schedule_row_id: auditScheduleRowId,
        company_id: (row as any).company_id,
        standard_id: standardId,
        status: AuditChecklistStatus.DRAFT,
      });
      await manager.save(checklist);

      const rows = items.map((item) =>
        manager.create(AuditChecklistItem, {
          audit_checklist_id: checklist.id,
          template_item_id: item.id,
          status: ChecklistItemStatus.MISSING,
        }),
      );
      await manager.save(rows);

      this.logger.log(`✓ Checklist ${checklist.id} built for audit ${auditScheduleRowId} - ${rows.length} items`);
      return checklist;
    });

    // Return with everything the review screen needs in one round-trip.
    return this.getChecklistWithRelations(created.id);
  }

  /** Draft was built but the auditor wants to change the item selection -
   *  discard and rebuild. Refused once the client has been notified. */
  async deleteDraft(checklistId: number): Promise<void> {
    const checklist = await this.dataSource.getRepository(AuditChecklist).findOne({
      where: { id: checklistId },
    });
    if (!checklist) throw new NotFoundException(`Checklist ${checklistId} not found`);
    if (checklist.client_notified_at) {
      throw new BadRequestException('The client has already been notified - this checklist can no longer be discarded.');
    }
    const uploaded = await this.dataSource.getRepository(AuditChecklistItem).count({
      where: { audit_checklist_id: checklistId, status: ChecklistItemStatus.UPLOADED } as any,
    });
    if (uploaded > 0) {
      throw new BadRequestException('Documents have already been uploaded against this checklist.');
    }
    await this.dataSource.getRepository(AuditChecklist).remove(checklist); // items cascade
    this.logger.log(`✓ Draft checklist ${checklistId} discarded`);
  }

  // ==========================================================================
  // SUBMIT = auditor reviewed the PDF and confirmed. Still internal -
  // the client hears nothing until the explicit notify step below.
  // ==========================================================================

  async submitChecklist(auditChecklistId: number, submittedByUserId: number): Promise<AuditChecklist> {
    const checklist = await this.dataSource.getRepository(AuditChecklist).findOne({
      where: { id: auditChecklistId },
    });
    if (!checklist) throw new NotFoundException(`Checklist ${auditChecklistId} not found`);

    checklist.status = AuditChecklistStatus.SUBMITTED;
    checklist.submitted_by = submittedByUserId;
    checklist.submitted_at = new Date();
    await this.dataSource.getRepository(AuditChecklist).save(checklist);

    this.logger.log(`✓ Checklist ${auditChecklistId} confirmed by user ${submittedByUserId}`);
    return this.getChecklistWithRelations(auditChecklistId);
  }

  // ==========================================================================
  // NOTIFY CLIENT - explicit action. Multiple emails (auto-fetched on the
  // frontend, editable there), one branded email per recipient + one in-app
  // real-time notification to the company's portal users.
  // ==========================================================================

  async notifyClient(auditChecklistId: number, dto: NotifyClientDto, triggeredByUserId: number): Promise<AuditChecklist> {
    const checklist = await this.getChecklistWithRelations(auditChecklistId);
    if (checklist.status !== AuditChecklistStatus.SUBMITTED) {
      throw new BadRequestException('Confirm the checklist before notifying the client.');
    }

    const emails = Array.from(
      new Set((dto.emails || []).map((e) => (e || '').trim().toLowerCase()).filter(Boolean)),
    );
    if (emails.length === 0) throw new BadRequestException('Add at least one client email.');
    const bad = emails.filter((e) => !EMAIL_RE.test(e));
    if (bad.length) throw new BadRequestException(`Invalid email address: ${bad.join(', ')}`);

    checklist.notify_emails = JSON.stringify(emails);
    checklist.client_message = dto.message?.trim() || null;
    checklist.due_date = dto.due_date || null;
    checklist.client_notified_at = new Date();
    await this.dataSource.getRepository(AuditChecklist).save(checklist);

    const auditCode = (checklist.audit_schedule_row as any)?.audit_code || `audit ${checklist.audit_schedule_row_id}`;
    const itemCount = (checklist.items || []).length;
    const portalLink = this.clientPortalLink(checklist.audit_schedule_row_id);

    // 1) In-app real-time notification to the company's portal users
    const clientUserIds = await this.resolveClientUserIds(checklist.company_id);
    if (clientUserIds.length > 0) {
      await this.safeNotify({
        type: (NotificationType as any).CHECKLIST_READY,
        title: 'Your audit checklist is ready',
        body: `${itemCount} document(s) requested for ${auditCode}${dto.due_date ? ` · due ${dto.due_date}` : ''}`,
        target_user_ids: clientUserIds,
        reference_id: checklist.id,
        reference_type: 'audit_checklist',
        is_urgent: false,
        requires_action: true,
        link_url: `/client/dashboard/audits/${checklist.audit_schedule_row_id}`,
        payload: { audit_checklist_id: checklist.id, audit_schedule_row_id: checklist.audit_schedule_row_id },
      });
    } else {
      this.logger.warn(`No portal users resolved for company ${checklist.company_id} - email only.`);
    }

    // 2) Branded email to every address the auditor confirmed — with the
    //    checklist PDF attached (same document served on the portal).
    let attachments: { filename: string; content: Buffer; contentType: string }[] = [];
    try {
      const pdfBuffer = await this.pdf.generate(this.pdf.dataFromChecklist(checklist));
      attachments = [
        { filename: this.pdf.filenameFor(checklist), content: pdfBuffer, contentType: 'application/pdf' },
      ];
    } catch (err: any) {
      this.logger.error(`Checklist PDF generation failed (email goes out without attachment): ${err.message}`);
    }
    await this.sendEmail(
      emails,
      `Documents requested for your audit ${auditCode}`,
      this.checklistEmailHtml({
        heading: '📋 Documents requested for your audit',
        companyName: (checklist.company as any)?.name || 'your company',
        auditCode,
        standardName: (checklist.standard as any)?.name || '',
        itemCount,
        dueDate: dto.due_date || null,
        message: dto.message || null,
        ctaLabel: 'Sign in to your client portal',
        ctaUrl: portalLink,
        footer: 'The full checklist is attached as a PDF. Open My Audits → this audit → Checklist to upload each document.',
      }),
      attachments,
      triggeredByUserId,
    );

    this.logger.log(`✓ Client notified for checklist ${auditChecklistId} → ${emails.join(', ')}`);
    return this.getChecklistWithRelations(auditChecklistId);
  }

  // ==========================================================================
  // CLIENT-PORTAL SIDE - read, upload, submit
  // ==========================================================================

  /** Client-portal entry point. The portal addresses audits by
   *  audit_requests.id (see ClientPortalService.getAudits/getAuditProgress),
   *  while checklists key on audit_schedule_row_id - translate here, with
   *  the same company double-check the rest of client-portal uses. */
  async getChecklistsForClientByRequest(auditRequestId: number, companyId: number): Promise<AuditChecklist[]> {
    const request = await this.dataSource.getRepository(AuditRequest).findOne({
      where: { id: auditRequestId, company_id: companyId } as any,
    });
    if (!request) throw new NotFoundException(`Audit ${auditRequestId} not found`);
    const rowId = (request as any).audit_schedule_row_id;
    if (!rowId) return []; // not scheduled yet - nothing to show
    return this.getChecklistsForClient(rowId, companyId);
  }

  /** All checklists on one audit, scoped to the client's own company. Only
   *  ones the auditor has actually sent (client_notified_at set). */
  async getChecklistsForClient(auditScheduleRowId: number, companyId: number): Promise<AuditChecklist[]> {
    const lists = await this.loadChecklists({ audit_schedule_row_id: auditScheduleRowId, company_id: companyId });
    return lists.filter((c) => !!c.client_notified_at);
  }

  async uploadClientDocument(
    itemId: number,
    companyId: number,
    uploadedByUserId: number,
    file: { originalname: string; mimetype: string; buffer: Buffer; size: number },
  ): Promise<AuditChecklistItem> {
    const saved = await this.dataSource.transaction(async (manager) => {
      const item = await manager.findOne(AuditChecklistItem, {
        where: { id: itemId },
        relations: ['audit_checklist', 'template_item'],
      });
      if (!item) throw new NotFoundException(`Checklist item ${itemId} not found`);

      if (item.audit_checklist.company_id !== companyId) {
        throw new ForbiddenException('This checklist item does not belong to your company.');
      }
      if (!item.audit_checklist.client_notified_at) {
        throw new ForbiddenException('This checklist has not been sent to you yet.');
      }
      if (item.status === ChecklistItemStatus.APPROVED) {
        throw new BadRequestException('This document is already approved.');
      }

      const { dbPath } = await this.storage.saveDocument(itemId, file);

      item.status = ChecklistItemStatus.UPLOADED;
      item.document_path = dbPath;
      item.document_original_name = file.originalname;
      item.uploaded_by = uploadedByUserId;
      item.uploaded_at = new Date();
      item.rejection_note = null;
      await manager.save(item);

      const history = manager.create(ChecklistItemHistory, {
        audit_checklist_item_id: itemId,
        action: ChecklistHistoryAction.UPLOADED,
        document_path: dbPath,
        document_original_name: file.originalname,
        performed_by: uploadedByUserId,
      });
      await manager.save(history);

      // If the client had already submitted and is now changing a file,
      // reopen the checklist so the auditor reviews the fresh document and
      // the client can submit again.
      if (item.audit_checklist.client_submitted_at) {
        await manager.update(AuditChecklist, item.audit_checklist_id, {
          client_submitted_at: null,
        } as any);
      }

      return item;
    });

    // Notify the lead auditor - real-time bell AND email - every upload.
    // Verbose logging so pm2 shows exactly what ran and why.
    try {
      const checklist = await this.dataSource.getRepository(AuditChecklist).findOne({
        where: { id: saved.audit_checklist_id },
        relations: [
          'audit_schedule_row',
          'audit_schedule_row.lead_auditor',
          'company',
          'standard',
          'items',
          'items.template_item',
        ],
      });
      const row: any = checklist?.audit_schedule_row;
      const leadAuditor: any = row?.lead_auditor;
      const leadAuditorId = row?.lead_auditor_id ?? leadAuditor?.id;
      const companyName = (checklist?.company as any)?.name || 'Client';
      const auditCode = row?.audit_code || `audit ${checklist?.audit_schedule_row_id}`;
      const savedItem = (checklist?.items || []).find((i) => i.id === saved.id);
      const itemText = (savedItem?.template_item as any)?.item_text || 'checklist item';
      const items = checklist?.items || [];
      const uploaded = items.filter((i) => i.status !== 'missing').length;

      this.logger.log(
        `[upload-notify] checklist=${saved.audit_checklist_id} leadAuditorId=${leadAuditorId ?? 'NONE'} ` +
        `leadAuditorEmail=${leadAuditor?.email ?? 'NONE'} item="${itemText}"`,
      );

      if (leadAuditorId) {
        await this.safeNotify({
          type: (NotificationType as any).CHECKLIST_DOC_UPLOADED,
          title: 'Client uploaded a document',
          body: `${companyName} uploaded "${file.originalname}" (${itemText})`,
          target_user_ids: [leadAuditorId],
          reference_id: saved.audit_checklist_id,
          reference_type: 'audit_checklist',
          is_urgent: false,
          requires_action: false,
          link_url: `/audit-schedules/${checklist?.audit_schedule_row_id}`,
          payload: { audit_checklist_id: saved.audit_checklist_id, item_id: saved.id },
        });
      } else {
        this.logger.warn('[upload-notify] no lead_auditor_id on the schedule row - auditor bell skipped.');
      }

      const auditorEmail = (leadAuditor?.email || '').trim();
      if (auditorEmail && EMAIL_RE.test(auditorEmail)) {
        // Build the auditor's display name for the greeting
        const auditorName =
          [leadAuditor?.firstName, leadAuditor?.lastName].filter(Boolean).join(' ').trim()
          || leadAuditor?.name
          || 'Auditor';

        await this.sendEmail(
          [auditorEmail],
          `New document from ${companyName} — ${auditCode}`,
          this.checklistEmailHtml({
            heading: 'New document received from client',
            companyName,
            recipientName: auditorName,   // 👈 addressed to the auditor, not the client
            auditCode,
            standardName: (checklist?.standard as any)?.name || '',
            itemCount: items.length,
            dueDate: checklist?.due_date || null,
            message: null,   // 👈 using extraHtml below for richer formatting
            extraHtml: `
        <table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#eff6ff;border:1px solid #bfdbfe;border-radius:8px;margin-bottom:14px;">
          <tr><td style="padding:14px 16px;font-size:13px;color:#1e3a8a;line-height:1.8;">
            <div style="font-weight:700;color:#1e40af;margin-bottom:10px;font-size:14px;">📎 Upload details</div>
            <div><strong>Client:</strong> ${this.esc(companyName)}</div>
            <div><strong>Checklist item:</strong> ${this.esc(itemText)}</div>
            <div><strong>File name:</strong> ${this.esc(file.originalname)}</div>
            <div><strong>Progress:</strong> ${uploaded} of ${items.length} document(s) uploaded</div>
          </td></tr>
        </table>
        <p style="margin:0 0 12px;font-size:13px;color:#334155;">
          The uploaded document is <strong>attached to this email</strong> for your review. You can also open it from the audit workspace.
        </p>
      `,
            ctaLabel: 'Open the audit workspace',
            ctaUrl: this.internalLink(`/audit-schedules/${checklist?.audit_schedule_row_id}`),
            footer: 'Review each document as it arrives, or wait for the client to submit them all.',
          }),
          [
            {
              filename: file.originalname,       // 👈 attach the uploaded file
              content: file.buffer,
              contentType: file.mimetype,
            },
          ],
          leadAuditorId ?? null,
        );


      } else {
        this.logger.warn(
          `[upload-notify] auditor email missing/invalid ("${leadAuditor?.email ?? ''}") - upload email skipped.`,
        );
      }
    } catch (err: any) {
      this.logger.warn(`[upload-notify] failed (upload itself succeeded): ${err.message}`);
    }

    return saved;
  }

  /** Client clicked their own Submit after uploading everything. Notifies
   *  the lead auditor in-app (real-time) AND by email; optional admin cc. */
  async clientSubmitChecklist(auditChecklistId: number, companyId: number): Promise<void> {
    const checklist = await this.dataSource.getRepository(AuditChecklist).findOne({
      where: { id: auditChecklistId, company_id: companyId } as any,
      relations: ['audit_schedule_row', 'audit_schedule_row.lead_auditor', 'company', 'items'],
    });
    if (!checklist) throw new NotFoundException('Checklist not found for your company.');

    const items = checklist.items || [];
    const notUploaded = items.filter(
      (i) => i.status === ChecklistItemStatus.MISSING || i.status === ChecklistItemStatus.REJECTED,
    );
    if (notUploaded.length > 0) {
      throw new BadRequestException(`Upload all documents first - ${notUploaded.length} still pending.`);
    }

    checklist.client_submitted_at = new Date();
    await this.dataSource.getRepository(AuditChecklist).save(checklist);

    const auditCode = (checklist.audit_schedule_row as any)?.audit_code || `audit ${checklist.audit_schedule_row_id}`;
    const companyName = (checklist.company as any)?.name || 'Client';
    const leadAuditor: any = (checklist.audit_schedule_row as any)?.lead_auditor;
    const leadAuditorId = (checklist.audit_schedule_row as any)?.lead_auditor_id;

    // Real-time (bell + socket via NotificationsService)
    if (leadAuditorId) {
      await this.safeNotify({
        type: (NotificationType as any).CHECKLIST_CLIENT_SUBMITTED,
        title: 'Client has submitted their checklist',
        body: `${companyName} - ${items.length}/${items.length} documents in · ${auditCode}`,
        target_user_ids: [leadAuditorId],
        reference_id: checklist.id,
        reference_type: 'audit_checklist',
        is_urgent: false,
        requires_action: true,
        link_url: `/audit-schedules/${checklist.audit_schedule_row_id}`,
        payload: { audit_checklist_id: checklist.id },
      });
    }

    // Email to the lead auditor (+ optional admin copy via env)
    const to = [leadAuditor?.email, ADMIN_NOTIFY_EMAIL].filter((e) => e && EMAIL_RE.test(e)) as string[];
    if (to.length) {
      await this.sendEmail(
        to,
        `${companyName} submitted their checklist - ${auditCode}`,
        this.checklistEmailHtml({
          heading: '✅ Client submitted all documents',
          companyName,
          auditCode,
          standardName: '',
          itemCount: items.length,
          dueDate: checklist.due_date,
          message: null,
          ctaLabel: 'Open the audit workspace',
          ctaUrl: this.internalLink(`/audit-schedules/${checklist.audit_schedule_row_id}`),
          footer: 'Every document is in - review and approve or reject each one.',
        }),
      );
    }

    this.logger.log(`✓ Client submitted checklist ${auditChecklistId}`);
  }

  // ==========================================================================
  // AUDITOR REVIEW - approve or reject each item; auto-detect completion
  // ==========================================================================

  async reviewItem(
    itemId: number,
    decision: 'approve' | 'reject',
    note: string | null,
    reviewedByUserId: number,
  ): Promise<AuditChecklistItem> {
    const reviewed = await this.dataSource.transaction(async (manager) => {
      const item = await manager.findOne(AuditChecklistItem, {
        where: { id: itemId },
        relations: ['audit_checklist'],
      });
      if (!item) throw new NotFoundException(`Checklist item ${itemId} not found`);
      if (item.status !== ChecklistItemStatus.UPLOADED) {
        throw new BadRequestException('Only uploaded items can be reviewed.');
      }

      item.status = decision === 'approve' ? ChecklistItemStatus.APPROVED : ChecklistItemStatus.REJECTED;
      item.reviewed_by = reviewedByUserId;
      item.reviewed_at = new Date();
      item.rejection_note = decision === 'reject' ? note : null;
      await manager.save(item);

      const history = manager.create(ChecklistItemHistory, {
        audit_checklist_item_id: itemId,
        action: decision === 'approve' ? ChecklistHistoryAction.APPROVED : ChecklistHistoryAction.REJECTED,
        note: decision === 'reject' ? note : null,
        performed_by: reviewedByUserId,
      });
      await manager.save(history);

      return item;
    });

    // Everything approved? Stamp completed_at (the "notify" of completion
    // happens through notifyClientOfReview so results go out as ONE batch).
    try {
      const siblings = await this.dataSource.getRepository(AuditChecklistItem).find({
        where: { audit_checklist_id: reviewed.audit_checklist_id } as any,
      });
      const allApproved = siblings.length > 0 && siblings.every((i) => i.status === ChecklistItemStatus.APPROVED);
      if (allApproved) {
        await this.dataSource
          .getRepository(AuditChecklist)
          .update(reviewed.audit_checklist_id, { completed_at: new Date() } as any);
        this.logger.log(`✓ Checklist ${reviewed.audit_checklist_id} fully approved - completed_at stamped`);
      }
    } catch (err: any) {
      this.logger.warn(`Completion check failed: ${err.message}`);
    }

    return reviewed;
  }

  /** ONE batched review-result notification (approved / rejected summary) -
   *  in-app real-time + email to the same addresses used at notify time.
   *  Also serves as the legacy notify-rejections endpoint. */
  async notifyClientOfReview(auditChecklistId: number, triggeredByUserId: number | null = null): Promise<void> {
    const checklist = await this.getChecklistWithRelations(auditChecklistId);

    const items = checklist.items || [];
    const approved = items.filter((i) => i.status === ChecklistItemStatus.APPROVED);
    const rejected = items.filter((i) => i.status === ChecklistItemStatus.REJECTED);
    if (approved.length === 0 && rejected.length === 0) {
      throw new BadRequestException('Nothing has been reviewed yet.');
    }

    const auditCode = (checklist.audit_schedule_row as any)?.audit_code || `audit ${checklist.audit_schedule_row_id}`;
    const allApproved = rejected.length === 0 && approved.length === items.length;

    const title = allApproved
      ? 'All documents approved ✅'
      : `${rejected.length} document(s) need to be re-uploaded`;
    const body = allApproved
      ? `${auditCode} - your document checklist is complete.`
      : rejected.map((i) => (i.template_item as any)?.item_text).filter(Boolean).join(', ');

    // In-app real-time to portal users
    const clientUserIds = await this.resolveClientUserIds(checklist.company_id);
    if (clientUserIds.length > 0) {
      await this.safeNotify({
        type: (NotificationType as any).CHECKLIST_REVIEW_RESULT,
        title,
        body,
        target_user_ids: clientUserIds,
        reference_id: checklist.id,
        reference_type: 'audit_checklist',
        is_urgent: !allApproved,
        requires_action: !allApproved,
        link_url: `/client/dashboard/audits/${checklist.audit_schedule_row_id}`,
        payload: {
          audit_checklist_id: checklist.id,
          approved_item_ids: approved.map((i) => i.id),
          rejected_item_ids: rejected.map((i) => i.id),
        },
      });
    }

    // Email to the confirmed notify addresses
    const emails = this.parseNotifyEmails(checklist.notify_emails);
    if (emails.length) {
      const rejectedRows = rejected
        .map(
          (i) =>
            `<tr><td style="padding:7px 10px;border:1px solid #e2e8f0">${this.esc((i.template_item as any)?.item_text || '')}</td>` +
            `<td style="padding:7px 10px;border:1px solid #e2e8f0;color:#991b1b">${this.esc(i.rejection_note || '')}</td></tr>`,
        )
        .join('');
      await this.sendEmail(
        emails,
        allApproved ? `All documents approved - ${auditCode}` : `Action needed on your documents - ${auditCode}`,
        this.checklistEmailHtml({
          heading: allApproved ? '✅ All documents approved' : '🔍 Review update on your documents',
          companyName: (checklist.company as any)?.name || 'your company',
          auditCode,
          standardName: (checklist.standard as any)?.name || '',
          itemCount: items.length,
          dueDate: checklist.due_date,
          message: allApproved
            ? 'Your document checklist is complete. Thank you.'
            : `${approved.length} approved · ${rejected.length} rejected - please re-upload the items below.`,
          extraHtml: rejectedRows
            ? `<table style="width:100%;border-collapse:collapse;margin-top:10px;font-size:12px">` +
            `<tr><th style="background:#4a0080;color:#fff;padding:7px 10px;text-align:left">Document</th>` +
            `<th style="background:#4a0080;color:#fff;padding:7px 10px;text-align:left">Auditor's note</th></tr>${rejectedRows}</table>`
            : '',
          ctaLabel: 'Open your client portal',
          ctaUrl: this.clientPortalLink(checklist.audit_schedule_row_id),
          footer: allApproved ? '' : 'Rejected items show the auditor\'s note next to a Re-upload button.',
        }),
        undefined,
        triggeredByUserId,
      );
    }

    this.logger.log(
      `✓ Review result sent for checklist ${auditChecklistId} (${approved.length} approved / ${rejected.length} rejected)`,
    );
  }

  // ==========================================================================
  // READS + DOCUMENTS
  // ==========================================================================

  /** Auditor's grouped view: every checklist on the audit (one per
   *  standard) with items, uploader/reviewer names and standard. */
  async getChecklistsForAuditor(auditScheduleRowId: number): Promise<AuditChecklist[]> {
    return this.loadChecklists({ audit_schedule_row_id: auditScheduleRowId });
  }

  /** Legacy flat list - kept so nothing that already calls
   *  GET audits/:rowId/checklist breaks. */
  async getChecklistForAuditor(auditScheduleRowId: number): Promise<AuditChecklistItem[]> {
    const checklists = await this.dataSource.getRepository(AuditChecklist).find({
      where: { audit_schedule_row_id: auditScheduleRowId } as any,
    });
    if (checklists.length === 0) return [];

    const checklistIds = checklists.map((c) => c.id);
    return this.dataSource
      .getRepository(AuditChecklistItem)
      .createQueryBuilder('item')
      .leftJoinAndSelect('item.template_item', 'template_item')
      .leftJoinAndSelect('item.uploaded_by_user', 'uploaded_by_user')
      .leftJoinAndSelect('item.reviewed_by_user', 'reviewed_by_user')
      .where('item.audit_checklist_id IN (:...checklistIds)', { checklistIds })
      .orderBy('template_item.sort_order', 'ASC')
      .getMany();
  }

  /** Stream a checklist document (staff side - any authenticated user). */
  async openItemDocument(itemId: number) {
    const item = await this.dataSource.getRepository(AuditChecklistItem).findOne({ where: { id: itemId } });
    if (!item || !item.document_path) throw new NotFoundException('No document uploaded for this item.');
    return this.storage.openFile(item.document_path);
  }

  /** Stream a checklist document (client side - own company only). */
  async openItemDocumentForCompany(itemId: number, companyId: number) {
    const item = await this.dataSource.getRepository(AuditChecklistItem).findOne({
      where: { id: itemId },
      relations: ['audit_checklist'],
    });
    if (!item || !item.document_path) throw new NotFoundException('No document uploaded for this item.');
    if (item.audit_checklist.company_id !== companyId) {
      throw new ForbiddenException('This document does not belong to your company.');
    }
    return this.storage.openFile(item.document_path);
  }

  /** The checklist itself as a PDF (staff side). */
  async getChecklistPdf(checklistId: number): Promise<{ buffer: Buffer; filename: string }> {
    const checklist = await this.getChecklistWithRelations(checklistId);
    const buffer = await this.pdf.generate(this.pdf.dataFromChecklist(checklist));
    return { buffer, filename: this.pdf.filenameFor(checklist) };
  }

  /** The checklist PDF for the client portal — own company + only after
   *  the auditor actually sent it. */
  async getChecklistPdfForCompany(checklistId: number, companyId: number): Promise<{ buffer: Buffer; filename: string }> {
    const checklist = await this.getChecklistWithRelations(checklistId);
    if (checklist.company_id !== companyId) {
      throw new ForbiddenException('This checklist does not belong to your company.');
    }
    if (!checklist.client_notified_at) {
      throw new ForbiddenException('This checklist has not been sent to you yet.');
    }
    const buffer = await this.pdf.generate(this.pdf.dataFromChecklist(checklist));
    return { buffer, filename: this.pdf.filenameFor(checklist) };
  }

  // ==========================================================================
  // Internal helpers
  // ==========================================================================

  private async loadChecklists(where: Record<string, any>): Promise<AuditChecklist[]> {
    return this.dataSource
      .getRepository(AuditChecklist)
      .createQueryBuilder('c')
      .leftJoinAndSelect('c.standard', 'standard')
      .leftJoinAndSelect('c.company', 'company')
      .leftJoinAndSelect('c.audit_schedule_row', 'row')
      .leftJoinAndSelect('row.standards', 'row_standards')
      .leftJoinAndSelect('c.submitted_by_user', 'submitted_by_user')
      .leftJoinAndSelect('c.items', 'items')
      .leftJoinAndSelect('items.template_item', 'template_item')
      .leftJoinAndSelect('items.uploaded_by_user', 'uploaded_by_user')
      .leftJoinAndSelect('items.reviewed_by_user', 'reviewed_by_user')
      .where(where)
      .orderBy('c.id', 'ASC')
      .addOrderBy('template_item.sort_order', 'ASC')
      .getMany();
  }

  private async getChecklistWithRelations(id: number): Promise<AuditChecklist> {
    // Object-literal where() keys must be plain entity property names -
    // TypeORM prefixes the query alias itself.
    const lists = await this.loadChecklists({ id });
    if (lists.length) return lists[0];
    const found = await this.dataSource.getRepository(AuditChecklist).findOne({
      where: { id },
      relations: [
        'standard',
        'company',
        'audit_schedule_row',
        'audit_schedule_row.standards',
        'submitted_by_user',
        'items',
        'items.template_item',
        'items.uploaded_by_user',
        'items.reviewed_by_user',
      ],
    });
    if (!found) throw new NotFoundException(`Checklist ${id} not found`);
    (found.items || []).sort(
      (a, b) => ((a.template_item as any)?.sort_order ?? 0) - ((b.template_item as any)?.sort_order ?? 0),
    );
    return found;
  }

  /** Which internal user ids should get the client-portal bell for this
   *  company? In this system a client login is keyed by EMAIL, so the
   *  reliable path is:
   *    client_access_tokens.company_id  →  client_email
   *      →  users.email  →  users.id
   *  company_users is used as an extra source when it happens to be
   *  populated. Both are UNION-ed and de-duped. Any failure logs and
   *  returns [] so the email still goes out. */
  private async resolveClientUserIds(companyId: number): Promise<number[]> {
    const ids = new Set<number>();

    // Path 1 — token email → user (the dependable one here)
    try {
      const rows: any[] = await this.dataSource.query(
        `SELECT DISTINCT u.id AS user_id
           FROM client_access_tokens t
           JOIN users u ON u.email = t.client_email
          WHERE t.company_id = ?`,
        [companyId],
      );
      for (const r of rows) {
        const n = Number(r.user_id);
        if (Number.isFinite(n) && n > 0) ids.add(n);
      }
    } catch (err: any) {
      this.logger.warn(`resolveClientUserIds via client_access_tokens failed: ${err.message}`);
    }

    // Path 2 — company_users bridge (bonus, if populated)
    try {
      const rows: any[] = await this.dataSource.query(
        'SELECT user_id FROM company_users WHERE company_id = ?',
        [companyId],
      );
      for (const r of rows) {
        const n = Number(r.user_id);
        if (Number.isFinite(n) && n > 0) ids.add(n);
      }
    } catch {
      /* table/column optional - path 1 already covers this system */
    }

    const out = Array.from(ids);
    if (out.length === 0) {
      this.logger.warn(
        `resolveClientUserIds(${companyId}) found no portal user - ` +
        'no client_access_tokens row whose client_email matches a users.email, and no company_users row. ' +
        'Email still sent; in-app bell skipped.',
      );
    }
    return out;
  }

  private async safeNotify(payload: Record<string, any>): Promise<void> {
    try {
      this.logger.log(
        `[notify] type=${payload.type} → users=[${(payload.target_user_ids || []).join(',')}] "${payload.title}"`,
      );
      await (this.notifications as any).send(payload);
      this.logger.log(`[notify] ✓ NotificationsService.send() returned for type=${payload.type}`);
    } catch (err: any) {
      this.logger.error(`[notify] ✗ send() threw for type=${payload.type}: ${err.message}`);
    }
  }

  /** Delivery through YOUR MailsService.sendAsUser():
   *   · asUserId set (auditor actions) → sends from that auditor's own
   *     mailbox via email_settings; no row → MailsService falls back to
   *     the default env transporter automatically.
   *   · asUserId null (system events, e.g. client submitted) → default
   *     env transporter.
   *  Attachments are nodemailer-native, so the checklist PDF just works. */
  private async sendEmail(
    to: string[],
    subject: string,
    html: string,
    attachments?: { filename: string; content: Buffer; contentType: string }[],
    asUserId: number | null = null,
  ): Promise<void> {
    try {
      await this.mailsService.sendAsUser(asUserId, {
        to,
        subject,
        html,
        ...(attachments && attachments.length ? { attachments } : {}),
      });
      this.logger.log(`✓ Email sent via MailsService.sendAsUser(${asUserId ?? 'default'}) → ${to.join(', ')}`);
    } catch (err: any) {
      this.logger.error(`Email send failed (${subject} → ${to.join(', ')}): ${err.message}`);
    }
  }

  private parseNotifyEmails(raw: string | null): string[] {
    if (!raw) return [];
    try {
      const arr = JSON.parse(raw);
      return Array.isArray(arr) ? arr.filter((e) => typeof e === 'string' && EMAIL_RE.test(e)) : [];
    } catch {
      return raw.split(',').map((e) => e.trim()).filter((e) => EMAIL_RE.test(e));
    }
  }

  private clientPortalLink(auditScheduleRowId: number): string {
    return CLIENT_PORTAL_URL
      ? `${CLIENT_PORTAL_URL.replace(/\/$/, '')}/client/dashboard/audits/${auditScheduleRowId}`
      : '';
  }

  private internalLink(path: string): string {
    const base = process.env.FRONTEND_URL || '';
    return base ? `${base.replace(/\/$/, '')}${path}` : '';
  }

  private esc(s: string): string {
    return String(s).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
  }

  private checklistEmailHtml(opts: {
    heading: string;
    companyName: string;
    recipientName?: string;
    auditCode: string;
    standardName: string;
    itemCount: number;
    dueDate: string | null;
    message: string | null;
    ctaLabel: string;
    ctaUrl: string;
    footer: string;
    extraHtml?: string;
  }): string {
    const LOGO = 'https://crm.qrs.ae/qrslogo.jpg';
    const cta = opts.ctaUrl
      ? `<table role="presentation" width="100%" cellpadding="0" cellspacing="0"><tr><td align="center" style="padding:16px 0 6px;">
          <table role="presentation" cellpadding="0" cellspacing="0"><tr>
            <td style="background:linear-gradient(135deg,#1a1054,#4a0080,#7c3aed);border-radius:10px;box-shadow:0 4px 14px rgba(74,0,128,0.3);">
              <a href="${opts.ctaUrl}" target="_blank" style="display:inline-block;padding:12px 32px;font-size:14px;font-weight:700;color:#ffffff;text-decoration:none;letter-spacing:0.3px;">${this.esc(opts.ctaLabel)} &rarr;</a>
            </td>
          </tr></table>
        </td></tr></table>`
      : '';

    return `<!doctype html><html><head><meta charset="UTF-8"/><meta name="viewport" content="width=device-width,initial-scale=1.0"/></head>
<body style="margin:0;padding:0;background:#f4f2f7;font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,Arial,sans-serif;-webkit-font-smoothing:antialiased;">
<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#f4f2f7;">
<tr><td align="center" style="padding:28px 16px;">
 
<table role="presentation" width="560" cellpadding="0" cellspacing="0" style="max-width:560px;width:100%;background:#ffffff;border-radius:14px;overflow:hidden;box-shadow:0 1px 3px rgba(0,0,0,0.06),0 4px 16px rgba(0,0,0,0.04);">
 
  <!-- Header -->
  <tr>
    <td style="background:linear-gradient(135deg,#1a1054 0%,#4a0080 50%,#7c3aed 100%);padding:24px 28px 20px;text-align:center;">
      <img src="${LOGO}" alt="Quality Registrar Systems" width="160" style="display:block;margin:0 auto 12px;max-width:160px;height:auto;"/>
      <div style="font-size:10px;color:rgba(255,255,255,0.6);font-weight:600;letter-spacing:.12em;margin-bottom:10px;">AUDIT & CERTIFICATION PLATFORM</div>
      <div style="color:#ffffff;font-size:19px;font-weight:700;line-height:1.3;">${this.esc(opts.heading)}</div>
      <div style="margin-top:10px;">
        <span style="display:inline-block;background:rgba(255,255,255,.14);border:1px solid rgba(255,255,255,.3);color:#ffffff;font-size:11px;font-weight:700;font-family:monospace;padding:4px 12px;border-radius:99px;">AUDIT ${this.esc(opts.auditCode)}</span>
      </div>
    </td>
  </tr>
 
  <!-- Gold accent -->
  <tr><td style="height:3px;background:linear-gradient(90deg,#d4a843,#7c3aed,#d4a843);"></td></tr>
 
  <!-- Body -->
  <tr>
    <td style="padding:22px 28px;color:#334155;font-size:14px;line-height:1.7;">
      <p style="margin:0 0 12px;">Dear <strong>${this.esc(opts.recipientName || opts.companyName)}</strong>,</p>
 
      ${opts.itemCount ? `
      <table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#f8fafc;border:1px solid #e2e8f0;border-radius:8px;margin-bottom:14px;">
        <tr>
          <td style="padding:12px 16px;">
            <div style="display:flex;gap:16px;font-size:13px;">
              <span><strong>${opts.itemCount}</strong> document(s)</span>
              ${opts.standardName ? `<span style="color:#64748b;">· <strong>${this.esc(opts.standardName)}</strong></span>` : ''}
              ${opts.dueDate ? `<span style="color:#b45309;">· Due <strong>${this.esc(opts.dueDate)}</strong></span>` : ''}
            </div>
          </td>
        </tr>
      </table>` : ''}
 
      ${opts.message ? `
      <table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#eff6ff;border:1px solid #bfdbfe;border-radius:8px;margin-bottom:14px;">
        <tr>
          <td style="padding:12px 16px;font-size:13px;color:#1e40af;">
            💬 <strong>Message from your auditor:</strong><br/>
            <span style="color:#1e3a8a;">${this.esc(opts.message)}</span>
          </td>
        </tr>
      </table>` : ''}
 
      ${opts.extraHtml || ''}
      ${cta}
 
      ${opts.footer ? `<div style="color:#94a3b8;font-size:12px;margin-top:8px;">${this.esc(opts.footer)}</div>` : ''}
    </td>
  </tr>
 
  <!-- Footer -->
  <tr>
    <td style="padding:20px 28px;background:#f8fafc;border-top:1px solid #e2e8f0;text-align:center;">
      <img src="${LOGO}" alt="QRS" width="70" style="display:block;margin:0 auto 8px;max-width:70px;height:auto;opacity:0.5;"/>
      <div style="font-size:11px;color:#94a3b8;line-height:1.7;">
        &copy; ${new Date().getFullYear()} Quality Registrar Systems. All rights reserved.<br/>
        <a href="mailto:info@qrsyst.com" style="color:#4a0080;text-decoration:none;">info@qrsyst.com</a> &middot;
        <a href="https://qrsyst.com/" style="color:#4a0080;text-decoration:none;">qrsyst.com</a>
      </div>
    </td>
  </tr>
 
</table>
 
</td></tr></table>
</body></html>`;
  }
}