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

import {
  AuditSchedule,
  ScheduleStatus,
} from '../entities/audit-schedule.entity';
import {
  AuditScheduleRow,
  RowStatus,
} from '../entities/audit-schedule-row.entity';
import { AuditStatusHistory } from '../entities/audit-status-history.entity';
import { Company } from '../../companies/entities/company.entity';
import { Standard } from '../../standards/entities/standard.entity';
import { User } from '../../user/entities/user.entity';

import {
  CreateAuditScheduleDto,
  CreateAuditRowDto,
} from '../dto/create-audit-schedule.dto';
import { UpdateAuditScheduleDto } from '../dto/update-audit-schedule.dto';
import {
  UpdateAuditRowDto,
  CancelAuditRowDto,
  RescheduleAuditRowDto,
  BulkCancelDto,
} from '../dto/audit-row.dto';
import { ListSchedulesQueryDto } from '../dto/list-query.dto';
import { AuditCodeGeneratorService } from './audit-code-generator.service';

// 🆕 Notifications integration
import { NotificationsService } from '../../notifications/notifications.service';
import { NotificationType } from '../../notifications/enums/notification-type.enum';
import { MailsService } from '../../mails/mails.service';   // 🆕
import { AuditReportStorageService } from './audit-report-storage.service';   // 🆕  (match your path)

// ✅ Super-admin user IDs that always see everything — same convention as
// the inquiries / audit-requests modules.
const SUPER_ADMIN_IDS = [1, 8];

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

  constructor(
    @InjectDataSource('scheme_dbs')
    private readonly dataSource: DataSource,
    private readonly codeGenerator: AuditCodeGeneratorService,
    private readonly notifications: NotificationsService,
    private readonly mailsService: MailsService,
    private readonly reportStorage: AuditReportStorageService,   // 🆕

  ) { }

  // ✅ ACCESS CONTROL — permission-based, NO hardcoded role names.
  // 🆕 A row is "assigned" to a user if they're the lead auditor OR one of
  // the co-auditors. Used everywhere access used to check lead_auditor_id
  // only, which silently locked co-auditors out of audits they were
  // assigned to (My Audits list, workspace, mark-complete, documents).
  private isAssignedToRow(row: AuditScheduleRow, userId: number): boolean {
    if (row.lead_auditor_id === userId) return true;
    return (row.co_auditors ?? []).some((u) => u.id === userId);
  }

  private async userCanViewAll(userId: number): Promise<boolean> {
    if (SUPER_ADMIN_IDS.includes(userId)) {
      this.logger.log(`[AUDIT-SCH-PERM] User ${userId} → SUPER ADMIN bypass`);
      return true;
    }

    const rolePerms = await this.dataSource.query(
      `
      SELECT 1
      FROM users u
      INNER JOIN user_roles ur ON ur.user_id = u.id
      INNER JOIN role_permissions rp ON rp.role_id = ur.role_id
      INNER JOIN permissions p ON p.id = rp.permission_id
      INNER JOIN modules m ON m.id = p.module_id
      WHERE u.id = ?
        AND p.action = 'view-all'
        AND m.slug = 'audit-schedules'
      LIMIT 1
      `,
      [userId],
    );

    if (rolePerms.length > 0) {
      this.logger.log(
        `[AUDIT-SCH-PERM] User ${userId} → has VIEW-ALL via role`,
      );
      return true;
    }

    try {
      const directPerms = await this.dataSource.query(
        `
        SELECT 1
        FROM user_permissions up
        INNER JOIN permissions p ON p.id = up.permission_id
        INNER JOIN modules m ON m.id = p.module_id
        WHERE up.user_id = ?
          AND p.action = 'view-all'
          AND m.slug = 'audit-schedules'
        LIMIT 1
        `,
        [userId],
      );
      if (directPerms.length > 0) {
        this.logger.log(
          `[AUDIT-SCH-PERM] User ${userId} → has VIEW-ALL via direct grant`,
        );
        return true;
      }
    } catch {
      /* user_permissions table may not exist — that's fine */
    }

    this.logger.log(
      `[AUDIT-SCH-PERM] User ${userId} → NO view-all, restricted to own rows`,
    );
    return false;
  }
  private async sendAsUserSafe(
    senderUserId: number | null | undefined,
    mail: { to: string; cc?: string; bcc?: string; subject: string; html: string; attachments?: any[] },
  ): Promise<void> {
    if (!senderUserId) return;
    await this.mailsService
      .sendAsUser(senderUserId, mail)
      .catch((e) =>
        this.logger.warn(`[AUDIT-SCH-MAIL] sendAsUser failed: ${e.message}`),
      );
  }
  // ✅ NEW — ACTION-LEVEL ACCESS CONTROL.
  // Checks whether `userId` has a specific permission ACTION on the
  // 'audit-schedules' module — e.g. 'edit', 'delete', 'cancel',
  // 'reschedule', 'bulk-cancel'. Same lookup shape as userCanViewAll().
  // Super-admin IDs always pass. Used to block mutating endpoints for
  // users (e.g. auditors) who only have 'view'.
  private async userCan(userId: number, action: string): Promise<boolean> {
    if (SUPER_ADMIN_IDS.includes(userId)) {
      return true;
    }

    const rolePerms = await this.dataSource.query(
      `
      SELECT 1
      FROM users u
      INNER JOIN user_roles ur ON ur.user_id = u.id
      INNER JOIN role_permissions rp ON rp.role_id = ur.role_id
      INNER JOIN permissions p ON p.id = rp.permission_id
      INNER JOIN modules m ON m.id = p.module_id
      WHERE u.id = ?
        AND p.action = ?
        AND m.slug = 'audit-schedules'
      LIMIT 1
      `,
      [userId, action],
    );
    if (rolePerms.length > 0) return true;

    try {
      const directPerms = await this.dataSource.query(
        `
        SELECT 1
        FROM user_permissions up
        INNER JOIN permissions p ON p.id = up.permission_id
        INNER JOIN modules m ON m.id = p.module_id
        WHERE up.user_id = ?
          AND p.action = ?
          AND m.slug = 'audit-schedules'
        LIMIT 1
        `,
        [userId, action],
      );
      if (directPerms.length > 0) return true;
    } catch {
      /* user_permissions table may not exist — that's fine */
    }

    return false;
  }

  // ✅ NEW — guard helper: throws ForbiddenException when the user lacks
  // the action. Logs the rejection so it's visible in pm2 logs.
  private async assertUserCan(
    userId: number | undefined,
    action: string,
  ): Promise<void> {
    // No userId on the request → cannot verify → reject (controller always
    // provides one for mutating endpoints).
    if (userId === undefined || userId === null) {
      this.logger.warn(
        `[AUDIT-SCH-PERM] Action '${action}' rejected — no user id on request`,
      );
      throw new ForbiddenException(
        'You do not have permission to perform this action.',
      );
    }
    const allowed = await this.userCan(userId, action);
    if (!allowed) {
      this.logger.warn(
        `[AUDIT-SCH-PERM] User ${userId} → DENIED action '${action}'`,
      );
      throw new ForbiddenException(
        `You do not have permission to ${action} audit schedules.`,
      );
    }
    this.logger.log(
      `[AUDIT-SCH-PERM] User ${userId} → allowed action '${action}'`,
    );
  }

  // ═══════════════════════════════════════════════════════════════
  // CREATE schedule with rows (transactional, auto-generates codes)
  // ═══════════════════════════════════════════════════════════════
  async create(dto: CreateAuditScheduleDto, currentUserId: number) {
    // ✅ Permission check — only users with 'create' may create schedules.
    await this.assertUserCan(currentUserId, 'create');

    // Run the writes inside a transaction.
    const newId = await this.dataSource.transaction(async (manager) => {
      // 1. Validate coordinator exists
      const coordinator = await manager.findOne(User, {
        where: { id: dto.coordinator_id },
      });
      if (!coordinator) {
        throw new BadRequestException('Coordinator user not found');
      }

      // 2. Create parent schedule
      const schedule = manager.create(AuditSchedule, {
        schedule_date: dto.schedule_date,
        title: dto.title ?? this.defaultTitle(dto.schedule_date),
        client_group: dto.client_group,
        coordinator_id: dto.coordinator_id,
        source_email: dto.source_email,
        status: dto.status ?? ScheduleStatus.DRAFT,
        notes: dto.notes,
        created_by_id: currentUserId,
      });
      const savedSchedule = await manager.save(schedule);

      // 3. Create each row with generated audit_code
      for (let i = 0; i < dto.rows.length; i++) {
        const rowDto = dto.rows[i];
        const row = await this.buildRowEntity(
          manager,
          savedSchedule,
          rowDto,
          i + 1,
        );
        const savedRow = await manager.save(row);

        // Initial history entry
        await manager.save(
          manager.create(AuditStatusHistory, {
            row_id: savedRow.id,
            new_status: savedRow.status,
            changed_by_id: currentUserId,
            reason: 'Initial creation',
          }),
        );
      }

      return savedSchedule.id;
    });

    this.logger.log(
      `Schedule created (id=${newId}, rows=${dto.rows.length}, user=${currentUserId})`,
    );

    // Transaction committed — safe to load via default datasource.
    return this.findOne(newId);
  }

  // FIND single schedule with all rows + relations
  // ✅ CHANGED: optional `currentUserId`. When provided and the user does
  async findOne(
    id: number,
    manager?: EntityManager,
    currentUserId?: number,
  ): Promise<AuditSchedule> {
    const repo = manager
      ? manager.getRepository(AuditSchedule)
      : this.dataSource.getRepository(AuditSchedule);

    const schedule = await repo
      .createQueryBuilder('sch')
      .leftJoinAndSelect('sch.coordinator', 'coordinator')
      .leftJoinAndSelect('sch.created_by', 'created_by')
      .leftJoinAndSelect('sch.rows', 'rows')
      .leftJoinAndSelect('rows.company', 'company')
      .leftJoinAndSelect('rows.lead_auditor', 'lead_auditor')
      .leftJoinAndSelect('rows.co_auditors', 'co_auditors')
      .leftJoinAndSelect('rows.standards', 'standards')
      .leftJoinAndSelect('rows.cancelled_by', 'cancelled_by')
      .where('sch.id = :id', { id })
      .orderBy('rows.row_no', 'ASC')
      .getOne();

    if (!schedule) throw new NotFoundException('Audit schedule not found');

    // ✅ Auditor row-level scoping — strip rows that don't belong to them.
    if (currentUserId) {
      const canViewAll = await this.userCanViewAll(currentUserId);
      if (!canViewAll) {
        const ownRows = (schedule.rows || []).filter(
          (r) => r.lead_auditor_id === currentUserId,
        );
        if (!ownRows.length) {
          // Auditor has no rows in this schedule — treat as not found.
          throw new NotFoundException('Audit schedule not found');
        }
        schedule.rows = ownRows;
      }
    }

    // 🆕 Attach the audit-request submitter (requested_by) to each row,
    //    so the detail modal can show submitter for Surveillance/Recert.
    if (schedule.rows?.length) {
      const ds = manager ?? this.dataSource;
      const rowIds = schedule.rows.map((r) => r.id);

      // row_id → requested_by_id  (audit_request_id is not a mapped prop)
      const links: Array<{ row_id: number; requested_by_id: number | null }> =
        await ds.query(
          `
          SELECT asr.id AS row_id, ar.requested_by_id AS requested_by_id
          FROM audit_schedule_rows asr
          LEFT JOIN audit_requests ar ON ar.id = asr.audit_request_id
          WHERE asr.id IN (?)
          `,
          [rowIds],
        );

      const userIds = Array.from(
        new Set(
          links
            .map((l) => l.requested_by_id)
            .filter((v): v is number => !!v),
        ),
      );

      const userRepo = manager
        ? manager.getRepository(User)
        : this.dataSource.getRepository(User);

      const users = userIds.length
        ? await userRepo.find({ where: { id: In(userIds) } })
        : [];
      const userById = new Map(users.map((u) => [u.id, u]));

      const submitterByRow = new Map<number, User | null>();
      for (const l of links) {
        submitterByRow.set(
          Number(l.row_id),
          l.requested_by_id ? userById.get(l.requested_by_id) ?? null : null,
        );
      }

      for (const r of schedule.rows) {
        (r as any).submitted_by = submitterByRow.get(r.id) ?? null;
      }
    }
    return schedule;

  }

  // ✅ CHANGED: optional `currentUserId`. When the user does NOT have
  // ═══════════════════════════════════════════════════════════════
  async findAll(q: ListSchedulesQueryDto, currentUserId?: number) {
    const page = q.page || 1;
    const limit = q.limit || 25;
    const skip = (page - 1) * limit;

    // ✅ Resolve view-all once.
    let restrictToOwnRows = false;
    if (currentUserId) {
      const canViewAll = await this.userCanViewAll(currentUserId);
      restrictToOwnRows = !canViewAll;
    }

    const qb = this.dataSource
      .getRepository(AuditSchedule)
      .createQueryBuilder('sch')
      .leftJoinAndSelect('sch.coordinator', 'coordinator')
      .leftJoin('sch.rows', 'rows')
      .addSelect('COUNT(rows.id)', 'row_count')
      .groupBy('sch.id')
      .addGroupBy('coordinator.id')
      .orderBy('sch.schedule_date', 'DESC')
      .addOrderBy('sch.id', 'DESC')
      .skip(skip)
      .take(limit);

    // ✅ Auditor scoping — only schedules that have at least one row
    // assigned to this auditor (as LEAD or CO-auditor). Use an EXISTS
    // sub-query so pagination and COUNT stay correct.
    if (restrictToOwnRows && currentUserId) {
      qb.andWhere(
        `EXISTS (
          SELECT 1 FROM audit_schedule_rows asr
          WHERE asr.schedule_id = sch.id
            AND (
              asr.lead_auditor_id = :auditorId
              OR asr.id IN (
                SELECT arc.row_id FROM audit_row_co_auditors arc
                WHERE arc.auditor_id = :auditorId
              )
            )
        )`,
        { auditorId: currentUserId },
      );
    }

    if (q.search) {
      // 🛠 FIX: the search box says "Search by title, audit code,
      // company..." but this used to only match sch.title / client_group /
      // the schedule's own numeric id — audit code and company name were
      // never actually searched, so a coordinator looking for a client had
      // to open every single day and scan the expanded rows by eye. Now it
      // also checks the audit_code and company name on any row inside the
      // schedule, so searching a client jumps straight to the right date.
      qb.andWhere(
        `(sch.title LIKE :s
          OR sch.client_group LIKE :s
          OR CAST(sch.id AS CHAR) LIKE :s
          OR EXISTS (
            -- ⚠️ Assumes the companies table is named "companies" (matches
            -- your snake_case convention elsewhere) — company.entity.ts
            -- wasn't in the files you sent me, so please confirm this
            -- table name before deploying.
            SELECT 1 FROM audit_schedule_rows asr2
            LEFT JOIN companies co2 ON co2.id = asr2.company_id
            WHERE asr2.schedule_id = sch.id
              AND (asr2.audit_code LIKE :s OR co2.name LIKE :s)
          ))`,
        { s: `%${q.search}%` },
      );
    }
    if (q.status) qb.andWhere('sch.status = :status', { status: q.status });
    if (q.client_group)
      qb.andWhere('sch.client_group = :cg', { cg: q.client_group });
    if (q.date_from)
      qb.andWhere('sch.schedule_date >= :df', { df: q.date_from });
    if (q.date_to) qb.andWhere('sch.schedule_date <= :dt', { dt: q.date_to });

    const { entities, raw } = await qb.getRawAndEntities();
    const total = await qb.getCount();

    // Stitch row_count back into entities
    let data = entities.map((sch, i) => ({
      ...sch,
      row_count: Number(raw[i]?.row_count ?? 0),
    }));

    // ✅ For auditors, trim each schedule's rows + row_count down to only
    // their own rows (lead OR co-auditor). ALSO — 🆕 PERFORMANCE FIX — when
    // a search is active, eagerly attach full rows (with co_auditors too)
    // to every matched schedule right here. Previously the frontend had to
    // fire one extra `GET /audit-schedules/:id` per matched schedule to
    // auto-expand search results, which is what made search feel slow.
    // Now that data rides along with the list response itself.
    const needsRowPreload =
      (restrictToOwnRows && currentUserId) || !!q.search;

    if (needsRowPreload && data.length) {
      const scheduleIds = data.map((s) => s.id);

      const rowsQb = this.dataSource
        .getRepository(AuditScheduleRow)
        .createQueryBuilder('row')
        .leftJoinAndSelect('row.company', 'company')
        .leftJoinAndSelect('row.lead_auditor', 'lead_auditor')
        .leftJoinAndSelect('row.co_auditors', 'co_auditors')
        .leftJoinAndSelect('row.standards', 'standards')
        .where('row.schedule_id IN (:...sids)', { sids: scheduleIds })
        .orderBy('row.row_no', 'ASC');

      if (restrictToOwnRows && currentUserId) {
        rowsQb
          .leftJoin('row.co_auditors', 'my_co')
          .andWhere(
            '(row.lead_auditor_id = :auditorId OR my_co.id = :auditorId)',
            { auditorId: currentUserId },
          );
      }

      const matchedRows = await rowsQb.getMany();

      const rowsBySchedule = new Map<number, AuditScheduleRow[]>();
      for (const row of matchedRows) {
        const list = rowsBySchedule.get(row.schedule_id) ?? [];
        list.push(row);
        rowsBySchedule.set(row.schedule_id, list);
      }

      data = data.map((sch) => {
        const rows = rowsBySchedule.get(sch.id) ?? [];
        return {
          ...sch,
          rows,
          // Only override row_count for the "own rows" case — for a plain
          // search, row_count should still reflect the TOTAL rows in that
          // schedule (not just the matched one), so leave it as-is.
          row_count:
            restrictToOwnRows && currentUserId ? rows.length : sch.row_count,
        };
      });
    }

    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }

  // ═══════════════════════════════════════════════════════════════
  // UPDATE schedule (parent fields only)
  // ═══════════════════════════════════════════════════════════════
  // ✅ CHANGED: accepts optional `currentUserId` for permission enforcement.
  async update(
    id: number,
    dto: UpdateAuditScheduleDto,
    currentUserId?: number,
  ) {
    // ✅ Permission check. Publishing (status → PUBLISHED) needs 'publish';
    // any other field update needs 'edit'.
    const isPublishing = dto.status === ScheduleStatus.PUBLISHED;
    await this.assertUserCan(currentUserId, isPublishing ? 'publish' : 'edit');

    const repo = this.dataSource.getRepository(AuditSchedule);
    const schedule = await repo.findOne({ where: { id } });
    if (!schedule) throw new NotFoundException('Audit schedule not found');

    const oldStatus = schedule.status;

    Object.assign(schedule, dto);
    await repo.save(schedule);

    // 🆕 Fire notifications when status transitions to PUBLISHED
    if (
      dto.status === ScheduleStatus.PUBLISHED &&
      oldStatus !== ScheduleStatus.PUBLISHED
    ) {
      // fire-and-forget — never let notification failures break the API response
      this.notifySchedulePublished(id).catch((err) =>
        this.logger.warn(
          `Notify schedule.published failed for id=${id}: ${err.message}`,
        ),
      );
    }

    return this.findOne(id);
  }

  // ═══════════════════════════════════════════════════════════════
  // DELETE schedule (cascades to rows)
  // ═══════════════════════════════════════════════════════════════
  // ✅ CHANGED: accepts optional `currentUserId` for permission enforcement.
  async remove(id: number, currentUserId?: number) {
    // ✅ Permission check — only users with 'delete' may delete schedules.
    await this.assertUserCan(currentUserId, 'delete');

    const result = await this.dataSource
      .getRepository(AuditSchedule)
      .delete(id);
    if (!result.affected)
      throw new NotFoundException('Audit schedule not found');
    return { message: 'Audit schedule deleted', id };
  }

  // ═══════════════════════════════════════════════════════════════
  // ADD a new row to existing schedule
  // ═══════════════════════════════════════════════════════════════
  async addRow(
    scheduleId: number,
    rowDto: CreateAuditRowDto,
    currentUserId: number,
  ) {
    // ✅ Permission check — adding a row is an edit of the schedule.
    await this.assertUserCan(currentUserId, 'edit');

    return this.dataSource.transaction(async (manager) => {
      const schedule = await manager.findOne(AuditSchedule, {
        where: { id: scheduleId },
        relations: ['rows'],
      });
      if (!schedule) throw new NotFoundException('Audit schedule not found');

      const nextRowNo =
        (schedule.rows?.reduce((max, r) => Math.max(max, r.row_no), 0) ?? 0) +
        1;

      const row = await this.buildRowEntity(
        manager,
        schedule,
        rowDto,
        nextRowNo,
      );
      const saved = await manager.save(row);

      await manager.save(
        manager.create(AuditStatusHistory, {
          row_id: saved.id,
          new_status: saved.status,
          changed_by_id: currentUserId,
          reason: 'Row added',
        }),
      );

      return this.findOneRow(saved.id, manager);
    });
  }

  // ═══════════════════════════════════════════════════════════════
  // UPDATE a row
  // ═══════════════════════════════════════════════════════════════
  async updateRow(
    scheduleId: number,
    rowId: number,
    dto: UpdateAuditRowDto,
    currentUserId: number,
  ) {
    // ✅ Permission check — editing a row is an edit of the schedule.
    await this.assertUserCan(currentUserId, 'edit');

    return this.dataSource.transaction(async (manager) => {
      const row = await manager.findOne(AuditScheduleRow, {
        where: { id: rowId, schedule_id: scheduleId },
        relations: ['standards'],
      });
      if (!row)
        throw new NotFoundException('Audit row not found in this schedule');

      const oldStatus = row.status;

      // Update simple fields
      if (dto.audit_type !== undefined) row.audit_type = dto.audit_type;
      if (dto.audit_stage !== undefined) row.audit_stage = dto.audit_stage;
      if (dto.audit_mode !== undefined) row.audit_mode = dto.audit_mode;
      if (dto.accreditation !== undefined)
        row.accreditation = dto.accreditation;
      if (dto.company_id !== undefined) row.company_id = dto.company_id;
      if (dto.lead_auditor_id !== undefined)
        row.lead_auditor_id = dto.lead_auditor_id;
      if (dto.audit_time !== undefined) row.audit_time = dto.audit_time;
      if (dto.audit_time_label !== undefined)
        row.audit_time_label = dto.audit_time_label;
      if (dto.notes !== undefined) row.notes = dto.notes;
      if (dto.status !== undefined) row.status = dto.status;

      // Standards (many-to-many)
      if (dto.standard_ids) {
        row.standards = await manager.find(Standard, {
          where: { id: In(dto.standard_ids) },
        });
      }

      // 🆕 Co-auditors (many-to-many) — send [] to clear
      if (dto.co_auditor_ids !== undefined) {
        const coIds = [
          ...new Set(
            dto.co_auditor_ids.filter(
              (cid) => cid !== (dto.lead_auditor_id ?? row.lead_auditor_id),
            ),
          ),
        ];
        row.co_auditors = coIds.length
          ? await manager.find(User, { where: { id: In(coIds) } })
          : [];
      }

      const saved = await manager.save(row);

      // Log status change if it happened
      if (dto.status && dto.status !== oldStatus) {
        await manager.save(
          manager.create(AuditStatusHistory, {
            row_id: rowId,
            old_status: oldStatus,
            new_status: dto.status,
            changed_by_id: currentUserId,
            reason: 'Status updated via row edit',
          }),
        );
      }

      return this.findOneRow(saved.id, manager);
    });
  }

  // ═══════════════════════════════════════════════════════════════
  // DELETE a row (hard delete)
  // ═══════════════════════════════════════════════════════════════
  // ✅ CHANGED: accepts optional `currentUserId` for permission enforcement.
  async removeRow(scheduleId: number, rowId: number, currentUserId?: number) {
    // ✅ Permission check — deleting a row needs 'delete'.
    await this.assertUserCan(currentUserId, 'delete');

    const result = await this.dataSource
      .getRepository(AuditScheduleRow)
      .delete({ id: rowId, schedule_id: scheduleId });
    if (!result.affected)
      throw new NotFoundException('Audit row not found in this schedule');
    return { message: 'Audit row deleted', id: rowId };
  }

  // ═══════════════════════════════════════════════════════════════
  // CANCEL a row (preserves audit_code for future reschedule)
  // ═══════════════════════════════════════════════════════════════
  async cancelRow(
    rowId: number,
    dto: CancelAuditRowDto,
    currentUserId: number,
  ) {
    // ✅ Permission check — only users with 'cancel' may cancel an audit row.
    await this.assertUserCan(currentUserId, 'cancel');

    const result = await this.dataSource.transaction(async (manager) => {
      const row = await manager.findOne(AuditScheduleRow, {
        where: { id: rowId },
      });
      if (!row) throw new NotFoundException('Audit row not found');

      if (row.status === RowStatus.CANCELLED) {
        throw new BadRequestException('Row is already cancelled');
      }
      if (row.status === RowStatus.COMPLETED) {
        throw new BadRequestException('Cannot cancel a completed audit');
      }

      const oldStatus = row.status;
      row.status = RowStatus.CANCELLED;
      row.cancelled_at = new Date();
      row.cancelled_by_id = currentUserId;
      row.cancellation_reason = dto.cancellation_reason;
      row.cancellation_notes = dto.cancellation_notes;

      await manager.save(row);

      await manager.save(
        manager.create(AuditStatusHistory, {
          row_id: rowId,
          old_status: oldStatus,
          new_status: RowStatus.CANCELLED,
          changed_by_id: currentUserId,
          reason: `Cancelled: ${dto.cancellation_reason}`,
          notes: dto.cancellation_notes,
        }),
      );

      return this.findOneRow(rowId, manager);
    });

    // 🆕 Notify after transaction commits — fire and forget
    this.notifyAuditRowCancelled(rowId, dto, currentUserId).catch((err) =>
      this.logger.warn(
        `Notify audit.cancelled failed for row=${rowId}: ${err.message}`,
      ),
    );

    return result;
  }

  // ═══════════════════════════════════════════════════════════════
  // RESCHEDULE a row — audit_code is PRESERVED
  // ═══════════════════════════════════════════════════════════════
  async rescheduleRow(
    rowId: number,
    dto: RescheduleAuditRowDto,
    currentUserId: number,
  ) {
    // ✅ Permission check — only users with 'reschedule' may reschedule.
    await this.assertUserCan(currentUserId, 'reschedule');

    let capturedOldDate: string | null = null;

    const result = await this.dataSource.transaction(async (manager) => {
      const row = await manager.findOne(AuditScheduleRow, {
        where: { id: rowId },
        relations: ['schedule', 'co_auditors'],
      });
      if (!row) throw new NotFoundException('Audit row not found');

      if (row.status === RowStatus.COMPLETED) {
        throw new BadRequestException('Cannot reschedule a completed audit');
      }

      const oldStatus = row.status;
      const oldDate = row.schedule.schedule_date;
      capturedOldDate = oldDate; // 🆕 capture for post-commit notification

      // Capture original date on FIRST reschedule
      if (!row.original_audit_date) {
        row.original_audit_date = oldDate;
      }

      // ✅ SPLIT BY DATE — move ONLY this row to a schedule for the new date.
      // The other rows on the original date stay where they are.
      const newScheduleDate = dto.new_audit_date;

      if (oldDate !== newScheduleDate) {
        const oldSchedule = row.schedule;

        // Find an existing (non-cancelled) schedule for the new date with the
        // same coordinator + group, or create one.
        let targetSchedule = await manager
          .getRepository(AuditSchedule)
          .createQueryBuilder('sch')
          .setLock('pessimistic_write')
          .where('sch.schedule_date = :d', { d: newScheduleDate })
          .andWhere('sch.coordinator_id = :cid', {
            cid: oldSchedule.coordinator_id,
          })
          .andWhere('sch.status != :cancelled', {
            cancelled: ScheduleStatus.CANCELLED,
          })
          .getOne();

        if (!targetSchedule) {
          targetSchedule = manager.create(AuditSchedule, {
            schedule_date: newScheduleDate,
            title: this.defaultTitle(newScheduleDate),
            client_group: oldSchedule.client_group,
            coordinator_id: oldSchedule.coordinator_id,
            status: ScheduleStatus.PUBLISHED,
            created_by_id: currentUserId,
          });
          targetSchedule = await manager.save(targetSchedule);
        }

        // Next row_no in the target schedule
        const targetRows = await manager.find(AuditScheduleRow, {
          where: { schedule_id: targetSchedule.id },
          select: ['row_no'],
        });
        const nextRowNo =
          (targetRows.reduce((max, r) => Math.max(max, r.row_no), 0) ?? 0) + 1;

        // Move ONLY this row to the new schedule
        row.schedule_id = targetSchedule.id;
        row.schedule = targetSchedule;
        row.row_no = nextRowNo;
      }

      // Update row's time + status
      if (dto.new_audit_time !== undefined) row.audit_time = dto.new_audit_time;
      if (dto.new_audit_time_label !== undefined)
        row.audit_time_label = dto.new_audit_time_label;

      // 🆕 Reassign lead / co-auditor(s), if requested. Capture who had it
      // BEFORE the change (only when the assignment actually changes) so
      // the previous auditor's My Audits can still show it as "Rescheduled"
      // instead of it silently vanishing from their list.
      const oldLeadAuditorId = row.lead_auditor_id;
      const oldCoAuditorIds = (row.co_auditors ?? []).map((u) => u.id);

      const leadChanging =
        dto.new_lead_auditor_id !== undefined &&
        dto.new_lead_auditor_id !== oldLeadAuditorId;

      let coChanging = false;
      if (dto.new_co_auditor_ids !== undefined) {
        const nextLeadId = dto.new_lead_auditor_id ?? oldLeadAuditorId;
        const coIds = [
          ...new Set(
            dto.new_co_auditor_ids.filter((cid) => cid !== nextLeadId),
          ),
        ];
        coChanging =
          coIds.length !== oldCoAuditorIds.length ||
          coIds.some((id) => !oldCoAuditorIds.includes(id));
        row.co_auditors = coIds.length
          ? await manager.find(User, { where: { id: In(coIds) } })
          : [];
      }

      if (leadChanging || coChanging) {
        row.previous_lead_auditor_id = oldLeadAuditorId;
        row.previous_co_auditor_ids = oldCoAuditorIds;
      }

      if (dto.new_lead_auditor_id !== undefined)
        row.lead_auditor_id = dto.new_lead_auditor_id;

      row.reschedule_count += 1;
      row.reschedule_reason = dto.reschedule_reason;
      row.last_rescheduled_at = new Date();
      row.status = RowStatus.PENDING; // back to pending on new date

      // Clear cancellation if it was cancelled before reschedule
      row.cancelled_at = null;
      row.cancelled_by_id = null;
      row.cancellation_reason = null;
      row.cancellation_notes = null;

      await manager.save(row);

      await manager.save(
        manager.create(AuditStatusHistory, {
          row_id: rowId,
          old_status: oldStatus,
          new_status: RowStatus.PENDING,
          changed_by_id: currentUserId,
          reason: `Rescheduled to ${newScheduleDate}`,
          notes: dto.reschedule_reason,
        }),
      );

      return this.findOneRow(rowId, manager);
    });

    // 🆕 Notify after transaction commits — fire and forget
    this.notifyAuditRowRescheduled(
      rowId,
      dto,
      currentUserId,
      capturedOldDate,
    ).catch((err) =>
      this.logger.warn(
        `Notify audit.rescheduled failed for row=${rowId}: ${err.message}`,
      ),
    );

    return result;
  }

  // ═══════════════════════════════════════════════════════════════
  // BULK CANCEL all rows in a schedule (e.g. external event)
  // ═══════════════════════════════════════════════════════════════
  async bulkCancel(
    scheduleId: number,
    dto: BulkCancelDto,
    currentUserId: number,
  ) {
    // ✅ Permission check — only users with 'bulk-cancel' may bulk-cancel.
    await this.assertUserCan(currentUserId, 'bulk-cancel');

    const result = await this.dataSource.transaction(async (manager) => {
      const rows = await manager.find(AuditScheduleRow, {
        where: { schedule_id: scheduleId },
      });
      if (!rows.length)
        throw new NotFoundException('No rows found in this schedule');

      let cancelled = 0;
      for (const row of rows) {
        if (
          row.status === RowStatus.CANCELLED ||
          row.status === RowStatus.COMPLETED
        )
          continue;

        const oldStatus = row.status;
        row.status = RowStatus.CANCELLED;
        row.cancelled_at = new Date();
        row.cancelled_by_id = currentUserId;
        row.cancellation_reason = dto.cancellation_reason;
        row.cancellation_notes = dto.cancellation_notes;
        await manager.save(row);

        await manager.save(
          manager.create(AuditStatusHistory, {
            row_id: row.id,
            old_status: oldStatus,
            new_status: RowStatus.CANCELLED,
            changed_by_id: currentUserId,
            reason: `Bulk cancel: ${dto.cancellation_reason}`,
            notes: dto.cancellation_notes,
          }),
        );
        cancelled++;
      }

      // Mark schedule itself as cancelled if all rows cancelled
      const schedule = await manager.findOne(AuditSchedule, {
        where: { id: scheduleId },
      });
      if (!schedule) {
        throw new NotFoundException('Audit schedule not found');
      }
      schedule.status = ScheduleStatus.CANCELLED;
      await manager.save(schedule);

      return {
        message: `Bulk cancellation complete: ${cancelled} rows cancelled`,
        cancelled_count: cancelled,
      };
    });

    // 🆕 Notify after transaction commits — fire and forget
    this.notifyAuditScheduleBulkCancelled(
      scheduleId,
      dto,
      currentUserId,
      result.cancelled_count,
    ).catch((err) =>
      this.logger.warn(
        `Notify audit.bulk_cancelled failed for schedule=${scheduleId}: ${err.message}`,
      ),
    );

    return result;
  }

  // ═══════════════════════════════════════════════════════════════
  // HISTORY: last N audits per company
  // ═══════════════════════════════════════════════════════════════
  async getCompanyAuditHistory(companyId: number, limit = 5) {
    return this.dataSource
      .getRepository(AuditScheduleRow)
      .createQueryBuilder('row')
      .leftJoinAndSelect('row.schedule', 'schedule')
      .leftJoinAndSelect('row.standards', 'standards')
      .leftJoinAndSelect('row.lead_auditor', 'auditor')
      .where('row.company_id = :cid', { cid: companyId })
      .orderBy('schedule.schedule_date', 'DESC')
      .addOrderBy('row.id', 'DESC')
      .limit(limit)
      .getMany();
  }

  // ═══════════════════════════════════════════════════════════════
  // HISTORY: full audit trail of one row
  // ═══════════════════════════════════════════════════════════════
  async getRowAuditTrail(rowId: number) {
    return this.dataSource
      .getRepository(AuditStatusHistory)
      .createQueryBuilder('h')
      .leftJoinAndSelect('h.changed_by', 'user')
      .where('h.row_id = :id', { id: rowId })
      .orderBy('h.created_at', 'ASC')
      .getMany();
  }

  // ═══════════════════════════════════════════════════════════════
  // ANALYTICS dashboard counts
  // ═══════════════════════════════════════════════════════════════
  async getAnalytics() {
    const repo = this.dataSource.getRepository(AuditScheduleRow);

    const [total, completed, pending, cancelled, thisWeek] = await Promise.all([
      repo.count(),
      repo.count({ where: { status: RowStatus.COMPLETED } }),
      repo.count({
        where: [{ status: RowStatus.PENDING }, { status: RowStatus.CONFIRMED }],
      }),
      repo.count({ where: { status: RowStatus.CANCELLED } }),
      this.countThisWeek(),
    ]);

    const completionRate = total > 0 ? (completed / total) * 100 : 0;

    return {
      total_audits: total,
      completed,
      pending,
      cancelled,
      this_week: thisWeek,
      completion_rate: Math.round(completionRate * 10) / 10,
    };
  }

  private async countThisWeek(): Promise<number> {
    const now = new Date();
    const start = new Date(now);
    start.setDate(now.getDate() - now.getDay()); // Sunday
    start.setHours(0, 0, 0, 0);
    const end = new Date(start);
    end.setDate(start.getDate() + 7);

    return this.dataSource
      .getRepository(AuditScheduleRow)
      .createQueryBuilder('row')
      .innerJoin('row.schedule', 'sch')
      .where('sch.schedule_date >= :s AND sch.schedule_date < :e', {
        // 🛠 FIX: was raw `.toISOString().slice(0, 10)` (UTC) — now uses the
        // local-safe helper so the "this week" count matches the same week
        // your coordinators actually see on screen.
        s: this.toISODate(start),
        e: this.toISODate(end),
      })
      .getCount();
  }

  // ═══════════════════════════════════════════════════════════════
  // Audits scheduled on a specific date
  // ═══════════════════════════════════════════════════════════════
  async findByDate(date: string) {
    return this.dataSource
      .getRepository(AuditSchedule)
      .createQueryBuilder('sch')
      .leftJoinAndSelect('sch.rows', 'rows')
      .leftJoinAndSelect('rows.company', 'company')
      .leftJoinAndSelect('rows.lead_auditor', 'auditor')
      .leftJoinAndSelect('rows.standards', 'standards')
      .leftJoinAndSelect('sch.coordinator', 'coordinator')
      .where('sch.schedule_date = :d', { d: date })
      .orderBy('rows.row_no', 'ASC')
      .getMany();
  }

  // ═══════════════════════════════════════════════════════════════
  // PRIVATE HELPERS
  // ═══════════════════════════════════════════════════════════════
  // findOneRow accepts an optional `manager` so it can run inside an
  // open transaction and see rows not yet committed.
  private async findOneRow(
    rowId: number,
    manager?: EntityManager,
  ): Promise<AuditScheduleRow> {
    const repo = manager
      ? manager.getRepository(AuditScheduleRow)
      : this.dataSource.getRepository(AuditScheduleRow);

    const row = await repo
      .createQueryBuilder('row')
      .leftJoinAndSelect('row.schedule', 'schedule')
      .leftJoinAndSelect('row.company', 'company')
      .leftJoinAndSelect('row.lead_auditor', 'lead_auditor')
      .leftJoinAndSelect('row.co_auditors', 'co_auditors')
      .leftJoinAndSelect('row.standards', 'standards')
      .leftJoinAndSelect('row.cancelled_by', 'cancelled_by')
      .where('row.id = :id', { id: rowId })
      .getOne();

    if (!row) throw new NotFoundException('Audit row not found');
    return row;
  }

  private defaultTitle(scheduleDate: string): string {
    // 🛠 FIX: was `new Date(scheduleDate)`, which parses a date-only string
    // as UTC midnight, then read back with local getters below — mismatched
    // on any server whose TZ isn't UTC. parseISODateLocal avoids the UTC
    // round-trip entirely.
    const d = this.parseISODateLocal(scheduleDate);
    const day = String(d.getDate()).padStart(2, '0');
    const month = d.toLocaleString('en-US', { month: 'long' }).toUpperCase();
    const year = d.getFullYear();
    return `AUDIT SCHEDULE FOR ${day}TH ${month} ${year}`;
  }

  /**
   * Build a new AuditScheduleRow entity (not yet persisted) with all
   * validations, code generation, and many-to-many standards loaded.
   */
  private async buildRowEntity(
    manager: EntityManager,
    schedule: AuditSchedule,
    rowDto: CreateAuditRowDto,
    rowNo: number,
  ): Promise<AuditScheduleRow> {
    // Validate company
    const company = await manager.findOne(Company, {
      where: { id: rowDto.company_id },
    });
    if (!company)
      throw new BadRequestException(
        `Company id ${rowDto.company_id} not found`,
      );

    // Validate auditor
    const auditor = await manager.findOne(User, {
      where: { id: rowDto.lead_auditor_id },
    });
    if (!auditor)
      throw new BadRequestException(
        `Lead auditor id ${rowDto.lead_auditor_id} not found`,
      );

    // 🆕 Validate + load co-auditors (Auditor 2, 3, ...)
    const coIds = [
      ...new Set(
        (rowDto.co_auditor_ids ?? []).filter(
          (cid) => cid !== rowDto.lead_auditor_id,
        ),
      ),
    ];
    const coAuditors = coIds.length
      ? await manager.find(User, { where: { id: In(coIds) } })
      : [];
    if (coAuditors.length !== coIds.length) {
      throw new BadRequestException('One or more co-auditor IDs are invalid');
    }

    // Load standards
    const standards = await manager.find(Standard, {
      where: { id: In(rowDto.standard_ids) },
    });
    if (standards.length !== rowDto.standard_ids.length) {
      throw new BadRequestException('One or more standard IDs are invalid');
    }

    // Generate audit_code inside the same transaction
    const auditCode = await this.codeGenerator.generate(
      rowDto.audit_type,
      schedule.schedule_date,
      manager,
    );

    const row = manager.create(AuditScheduleRow, {
      schedule_id: schedule.id,
      schedule,
      row_no: rowNo,
      audit_code: auditCode,
      audit_type: rowDto.audit_type,
      audit_stage: rowDto.audit_stage,
      audit_mode: rowDto.audit_mode,
      accreditation: rowDto.accreditation,
      company_id: rowDto.company_id,
      company,
      lead_auditor_id: rowDto.lead_auditor_id,
      lead_auditor: auditor,
      co_auditors: coAuditors,   // 🆕
      standards,
      audit_time: rowDto.audit_time,
      audit_time_label: rowDto.audit_time_label,
      notes: rowDto.notes,
    });

    return row;
  }

  // ═══════════════════════════════════════════════════════════════
  // 🆕 NOTIFICATION HELPERS — called fire-and-forget AFTER transactions
  // Each resolves 3 recipients (marketing + auditor + coordinator),
  // builds email HTML via MailsService helpers, and calls
  // notifications.send() to fan out via in-app + email + WhatsApp.
  // ═══════════════════════════════════════════════════════════════

  /**
   * 🆕 Resolve the 3 recipients for an audit row:
   *   - Marketing  → company.created_by_id
   *   - Auditor    → row.lead_auditor_id
   *   - Coordinator→ schedule.coordinator_id
   * Returns deduplicated, only valid users.
   */
  private async resolveRecipientsForRow(
    rowId: number,
  ): Promise<
    Array<{ user: User; role: 'MARKETING' | 'AUDITOR' | 'COORDINATOR' }>
  > {
    const row = await this.dataSource.getRepository(AuditScheduleRow).findOne({
      where: { id: rowId },
      relations: [
        'company',
        'lead_auditor',
        'schedule',
        'schedule.coordinator',
      ],
    });
    if (!row) return [];

    const userRepo = this.dataSource.getRepository(User);
    const out: Array<{
      user: User;
      role: 'MARKETING' | 'AUDITOR' | 'COORDINATOR';
    }> = [];
    const seen = new Set<number>();

    // Marketing — from company.created_by_id (best-effort)
    const marketingId = (row.company as any)?.created_by_id;
    if (marketingId) {
      const mkt = await userRepo.findOne({ where: { id: marketingId } });
      if (mkt && !seen.has(mkt.id)) {
        out.push({ user: mkt, role: 'MARKETING' });
        seen.add(mkt.id);
      }
    }

    // Lead auditor
    if (row.lead_auditor && !seen.has(row.lead_auditor.id)) {
      out.push({ user: row.lead_auditor, role: 'AUDITOR' });
      seen.add(row.lead_auditor.id);
    }

    // Coordinator
    if (row.schedule?.coordinator && !seen.has(row.schedule.coordinator.id)) {
      out.push({ user: row.schedule.coordinator, role: 'COORDINATOR' });
      seen.add(row.schedule.coordinator.id);
    }

    return out;
  }

  /** Build a single-line WhatsApp message for an audit event. */
  private buildWhatsappMessage(args: {
    headline: string;
    auditCode: string;
    companyName: string;
    auditDate: string;
    auditTimeLabel?: string;
    extras?: string[];
  }): string {
    const lines = [
      `*${args.headline}*`,
      ``,
      `📋 ${args.auditCode}`,
      `🏢 ${args.companyName}`,
      `📅 ${args.auditDate}${args.auditTimeLabel ? ` · ${args.auditTimeLabel}` : ''}`,
    ];
    if (args.extras?.length) {
      lines.push(``, ...args.extras);
    }
    return lines.join('\n');
  }

  /** 🆕 Fired when a schedule transitions DRAFT → PUBLISHED. */
  private async notifySchedulePublished(scheduleId: number): Promise<void> {
    const schedule = await this.dataSource
      .getRepository(AuditSchedule)
      .findOne({
        where: { id: scheduleId },
        relations: ['rows', 'coordinator'],
      });
    if (!schedule || !schedule.rows?.length) return;

    const coordinatorId = schedule.coordinator_id;   // 🆕 sender = coordinator

    for (const row of schedule.rows) {
      const recipients = await this.resolveRecipientsForRow(row.id);
      if (!recipients.length) continue;

      const fullRow = await this.findOneRow(row.id);
      const standards =
        fullRow.standards?.map((s) => s.name).join(', ') || undefined;

      for (const r of recipients) {
        const ctx = {
          recipientName: this.fullName(r.user),
          auditCode: fullRow.audit_code,
          companyName: fullRow.company?.name || '—',
          auditDate: schedule.schedule_date,
          auditTimeLabel: fullRow.audit_time_label ?? undefined,
          auditorName: fullRow.lead_auditor
            ? this.fullName(fullRow.lead_auditor)
            : undefined,
          standards,
          scheduleId: schedule.id,
        };

        const whatsappMsg = this.buildWhatsappMessage({
          headline: 'Audit Scheduled',
          auditCode: ctx.auditCode,
          companyName: ctx.companyName,
          auditDate: ctx.auditDate,
          auditTimeLabel: ctx.auditTimeLabel,
          extras: ctx.auditorName ? [`👤 Auditor: ${ctx.auditorName}`] : [],
        });

        const emailSubject = `📅 Audit Scheduled: ${ctx.auditCode}`;
        const emailHtml = this.buildPublishedHtml(ctx);

        // 🆕 send the email FROM the coordinator's own mailbox, TO this recipient
        await this.sendAsUserSafe(coordinatorId, {
          to: r.user.email,
          subject: emailSubject,
          html: emailHtml,
        });

        // in-app bell + WhatsApp only — email handled above
        await this.notifications.send({
          type: NotificationType.AUDIT_SCHEDULE_PUBLISHED,
          title: `Audit scheduled: ${ctx.auditCode}`,
          body: `${ctx.companyName} · ${ctx.auditDate}${ctx.auditTimeLabel ? ` · ${ctx.auditTimeLabel}` : ''}`,
          target_user_ids: [r.user.id],
          reference_id: row.id,
          reference_type: 'audit_row',
          is_urgent: false,
          requires_action: false,
          link_url: `/audit-schedules/${schedule.id}`,
          payload: { audit_code: ctx.auditCode, row_id: row.id, role: r.role },
          whatsapp_message: whatsappMsg,
          // email_subject / email_html intentionally removed
        });
      }
    }
  }

  /** 🆕 Fired when a single row is cancelled. */
  private async notifyAuditRowCancelled(
    rowId: number,
    dto: CancelAuditRowDto,
    triggeredByUserId: number,
  ): Promise<void> {
    const recipients = await this.resolveRecipientsForRow(rowId);
    if (!recipients.length) return;

    const fullRow = await this.findOneRow(rowId);
    const schedule = await this.dataSource
      .getRepository(AuditSchedule)
      .findOne({ where: { id: fullRow.schedule_id } });

    const coordinatorId = schedule?.coordinator_id ?? null;   // 🆕 sender

    const triggeredBy = await this.dataSource
      .getRepository(User)
      .findOne({ where: { id: triggeredByUserId } });

    const standards =
      fullRow.standards?.map((s) => s.name).join(', ') || undefined;

    for (const r of recipients) {
      const ctx = {
        recipientName: this.fullName(r.user),
        auditCode: fullRow.audit_code,
        companyName: fullRow.company?.name || '—',
        auditDate: schedule?.schedule_date || '—',
        auditTimeLabel: fullRow.audit_time_label ?? undefined,
        auditorName: fullRow.lead_auditor
          ? this.fullName(fullRow.lead_auditor)
          : undefined,
        standards,
        scheduleId: fullRow.schedule_id,
        reason: dto.cancellation_reason,
        notes: dto.cancellation_notes,
        triggeredByName: triggeredBy ? this.fullName(triggeredBy) : undefined,
      };

      const whatsappMsg = this.buildWhatsappMessage({
        headline: '❌ Audit Cancelled',
        auditCode: ctx.auditCode,
        companyName: ctx.companyName,
        auditDate: ctx.auditDate,
        auditTimeLabel: ctx.auditTimeLabel,
        extras: [
          `Reason: ${ctx.reason}`,
          ctx.triggeredByName ? `By: ${ctx.triggeredByName}` : '',
        ].filter(Boolean) as string[],
      });

      const emailSubject = `❌ Audit Cancelled: ${ctx.auditCode}`;
      const emailHtml = this.buildCancelledHtml(ctx);

      // 🆕 send FROM coordinator's mailbox, TO this recipient
      await this.sendAsUserSafe(coordinatorId, {
        to: r.user.email,
        subject: emailSubject,
        html: emailHtml,
      });

      await this.notifications.send({
        type: NotificationType.AUDIT_ROW_CANCELLED,
        title: `Audit cancelled: ${ctx.auditCode}`,
        body: `${ctx.companyName} · ${ctx.reason}`,
        target_user_ids: [r.user.id],
        reference_id: rowId,
        reference_type: 'audit_row',
        is_urgent: true,
        requires_action: false,
        link_url: `/audit-schedules/${ctx.scheduleId}`,
        payload: { audit_code: ctx.auditCode, row_id: rowId, role: r.role },
        whatsapp_message: whatsappMsg,
        // email_subject / email_html intentionally removed
      });
    }
  }

  /** 🆕 Fired when a single row is rescheduled. */
  private async notifyAuditRowRescheduled(
    rowId: number,
    dto: RescheduleAuditRowDto,
    triggeredByUserId: number,
    oldDate: string | null,
  ): Promise<void> {
    const recipients = await this.resolveRecipientsForRow(rowId);
    if (!recipients.length) return;

    const fullRow = await this.findOneRow(rowId);

    // 🆕 sender = coordinator of this row's schedule
    const schedule = await this.dataSource
      .getRepository(AuditSchedule)
      .findOne({ where: { id: fullRow.schedule_id } });
    const coordinatorId = schedule?.coordinator_id ?? null;

    const triggeredBy = await this.dataSource
      .getRepository(User)
      .findOne({ where: { id: triggeredByUserId } });

    const standards =
      fullRow.standards?.map((s) => s.name).join(', ') || undefined;

    for (const r of recipients) {
      const ctx = {
        recipientName: this.fullName(r.user),
        auditCode: fullRow.audit_code,
        companyName: fullRow.company?.name || '—',
        auditDate: dto.new_audit_date,
        auditTimeLabel:
          dto.new_audit_time_label ?? fullRow.audit_time_label ?? undefined,
        auditorName: fullRow.lead_auditor
          ? this.fullName(fullRow.lead_auditor)
          : undefined,
        standards,
        scheduleId: fullRow.schedule_id,
        reason: dto.reschedule_reason,
        oldDate: oldDate ?? undefined,
        newDate: dto.new_audit_date,
        triggeredByName: triggeredBy ? this.fullName(triggeredBy) : undefined,
      };

      const whatsappMsg = this.buildWhatsappMessage({
        headline: '🔄 Audit Rescheduled',
        auditCode: ctx.auditCode,
        companyName: ctx.companyName,
        auditDate: ctx.newDate,
        auditTimeLabel: ctx.auditTimeLabel,
        extras: [
          ctx.oldDate ? `Was: ${ctx.oldDate}` : '',
          `Reason: ${ctx.reason}`,
          ctx.triggeredByName ? `By: ${ctx.triggeredByName}` : '',
        ].filter(Boolean) as string[],
      });

      const emailSubject = `🔄 Audit Rescheduled: ${ctx.auditCode}`;
      const emailHtml = this.buildRescheduledHtml(ctx);

      // 🆕 send FROM coordinator's mailbox
      await this.sendAsUserSafe(coordinatorId, {
        to: r.user.email,
        subject: emailSubject,
        html: emailHtml,
      });

      await this.notifications.send({
        type: NotificationType.AUDIT_ROW_RESCHEDULED,
        title: `Audit rescheduled: ${ctx.auditCode}`,
        body: `${ctx.companyName} · now ${ctx.newDate}`,
        target_user_ids: [r.user.id],
        reference_id: rowId,
        reference_type: 'audit_row',
        is_urgent: true,
        requires_action: false,
        link_url: `/audit-schedules/${ctx.scheduleId}`,
        payload: { audit_code: ctx.auditCode, row_id: rowId, role: r.role },
        whatsapp_message: whatsappMsg,
        // email_subject / email_html intentionally removed
      });
    }
  }

  /** 🆕 Fired when an entire schedule is bulk-cancelled. */
  private async notifyAuditScheduleBulkCancelled(
    scheduleId: number,
    dto: BulkCancelDto,
    triggeredByUserId: number,
    cancelledCount: number,
  ): Promise<void> {
    const schedule = await this.dataSource
      .getRepository(AuditSchedule)
      .findOne({
        where: { id: scheduleId },
        relations: ['rows'],
      });
    if (!schedule) return;

    const coordinatorId = schedule.coordinator_id ?? null;   // 🆕 sender

    const triggeredBy = await this.dataSource
      .getRepository(User)
      .findOne({ where: { id: triggeredByUserId } });
    const triggeredByName = triggeredBy ? this.fullName(triggeredBy) : undefined;

    const recipientMap = new Map<
      number,
      { user: User; role: 'MARKETING' | 'AUDITOR' | 'COORDINATOR' }
    >();
    for (const row of schedule.rows || []) {
      const recipients = await this.resolveRecipientsForRow(row.id);
      for (const r of recipients) {
        if (!recipientMap.has(r.user.id)) {
          recipientMap.set(r.user.id, r);
        }
      }
    }

    for (const r of recipientMap.values()) {
      const ctx = {
        recipientName: this.fullName(r.user),
        auditCode: `Schedule #${scheduleId}`,
        companyName: '—',
        auditDate: schedule.schedule_date,
        scheduleId,
        reason: dto.cancellation_reason,
        notes: dto.cancellation_notes,
        triggeredByName,
        affectedCount: cancelledCount,
      };

      const whatsappMsg = this.buildWhatsappMessage({
        headline: '🚫 All Audits Cancelled',
        auditCode: `Schedule #${scheduleId}`,
        companyName: `${cancelledCount} audit(s)`,
        auditDate: schedule.schedule_date,
        extras: [
          `Reason: ${ctx.reason}`,
          triggeredByName ? `By: ${triggeredByName}` : '',
        ].filter(Boolean) as string[],
      });

      const emailSubject = `❌ All Audits Cancelled on ${schedule.schedule_date}`;
      const emailHtml = this.buildBulkCancelledHtml(ctx);

      // 🆕 send FROM coordinator's mailbox
      await this.sendAsUserSafe(coordinatorId, {
        to: r.user.email,
        subject: emailSubject,
        html: emailHtml,
      });

      await this.notifications.send({
        type: NotificationType.AUDIT_SCHEDULE_BULK_CANCELLED,
        title: `${cancelledCount} audit(s) cancelled on ${schedule.schedule_date}`,
        body: `Reason: ${ctx.reason}`,
        target_user_ids: [r.user.id],
        reference_id: scheduleId,
        reference_type: 'audit_schedule',
        is_urgent: true,
        requires_action: false,
        link_url: `/audit-schedules/${scheduleId}`,
        payload: { schedule_id: scheduleId, role: r.role },
        whatsapp_message: whatsappMsg,
        // email_subject / email_html intentionally removed
      });
    }
  }
  async myRows(currentUserId: number, q: any) {
    const page = q.page || 1;
    const limit = q.limit || 25;
    const skip = (page - 1) * limit;

    // Resolve date range from preset if provided, otherwise use raw from/to.
    const { date_from, date_to } = this.resolveMyAuditsDateRange(
      q.date_preset,
      q.date_from,
      q.date_to,
    );

    // 🆕 Check if user can see all audits based on role
    const canViewAll = await this.userIsMyAuditsViewAll(currentUserId);

    const qb = this.dataSource
      .getRepository(AuditScheduleRow)
      .createQueryBuilder('row')
      .leftJoinAndSelect('row.schedule', 'schedule')
      .leftJoinAndSelect('schedule.coordinator', 'coordinator')
      .leftJoinAndSelect('row.company', 'company')
      .leftJoinAndSelect('row.lead_auditor', 'lead_auditor')
      .leftJoinAndSelect('row.co_auditors', 'co_auditors') // 🆕 so the UI can show co-auditor chips too
      .leftJoinAndSelect('row.standards', 'standards')
      // 🆕 needed to match rows where the current user is a co-auditor, not just lead
      .leftJoin('row.co_auditors', 'my_co_auditor_filter');

    if (canViewAll) {
      qb.where('1 = 1'); // see all audits
    } else {
      // 🛠 FIX: previously only `row.lead_auditor_id = :uid`, so a user
      // assigned as CO-auditor never saw the audit on their own "My Audits"
      // dashboard — only the lead auditor did. Now both see it.
      // 🆕 Also matches rows this user was PREVIOUSLY lead/co-auditor on but
      // got reassigned away from during a reschedule — so it doesn't just
      // silently vanish from their list (see reassigned_from_you below).
      qb.where(
        `(row.lead_auditor_id = :uid
          OR my_co_auditor_filter.id = :uid
          OR row.previous_lead_auditor_id = :uid
          OR JSON_CONTAINS(row.previous_co_auditor_ids, :uidJson))`,
        { uid: currentUserId, uidJson: JSON.stringify(currentUserId) },
      );
      qb.distinct(true);
    }

    if (q.status) {
      qb.andWhere('row.status = :status', { status: q.status });
    }
    if (date_from) {
      qb.andWhere('schedule.schedule_date >= :df', { df: date_from });
    }
    if (date_to) {
      qb.andWhere('schedule.schedule_date <= :dt', { dt: date_to });
    }
    if (q.search) {
      qb.andWhere(
        '(row.audit_code LIKE :s OR company.name LIKE :s OR standards.name LIKE :s)',
        { s: `%${q.search}%` },
      );
    }

    qb.orderBy('schedule.schedule_date', 'DESC')
      .addOrderBy('row.audit_time', 'ASC')
      .addOrderBy('row.id', 'DESC')
      .skip(skip)
      .take(limit);

    const [rawData, total] = await qb.getManyAndCount();

    // 🆕 Per-viewer flag: true if this row was reassigned away from the
    // current user to someone else. The row's real `status` is shared by
    // everyone (it resets to PENDING for the new assignee on reschedule),
    // so this is computed per-request rather than stored — the frontend
    // uses it to show "Rescheduled" on the old auditor's card specifically,
    // without affecting what the new lead/co-auditor sees.
    const data = rawData.map((row) => {
      const isCurrentlyAssigned =
        row.lead_auditor_id === currentUserId ||
        (row.co_auditors ?? []).some((u) => u.id === currentUserId);
      const wasPreviouslyAssigned =
        row.previous_lead_auditor_id === currentUserId ||
        (row.previous_co_auditor_ids ?? []).includes(currentUserId);
      return {
        ...row,
        reassigned_from_you: !isCurrentlyAssigned && wasPreviouslyAssigned,
      };
    });

    return {
      data,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.ceil(total / limit),
      },
    };
  }
  async myScheduleMaster(currentUserId: number, q: any) {
    this.logger.log(
      `[SCHED-MASTER] currentUserId=${currentUserId} q=${JSON.stringify(q)}`,
    );

    const page = q.page || 1;
    const limit = q.limit || 25;
    const skip = (page - 1) * limit;

    const { date_from, date_to } = this.resolveMyAuditsDateRange(
      q.date_preset,
      q.date_from,
      q.date_to,
    );

    const canViewAll = await this.userIsMyAuditsViewAll(currentUserId);

    const applyScope = (qb: any) => {
      if (canViewAll) {
        qb.where('1 = 1');
      } else {
        // 🛠 FIX: added the audit_row_co_auditors subquery so co-auditors
        // (not just the lead auditor / request creator) see these rows too.
        qb.where(
          `(row.lead_auditor_id = :uid
            OR row.id IN (
              SELECT asr.id
              FROM audit_schedule_rows asr
              INNER JOIN audit_requests ar ON ar.id = asr.audit_request_id
              WHERE ar.requested_by_id = :uid
            )
            OR row.id IN (
              SELECT arc.row_id
              FROM audit_row_co_auditors arc
              WHERE arc.auditor_id = :uid
            ))`,
          { uid: currentUserId },
        );
      }
      if (q.status) qb.andWhere('row.status = :status', { status: q.status });
      if (date_from)
        qb.andWhere('schedule.schedule_date >= :df', { df: date_from });
      if (date_to)
        qb.andWhere('schedule.schedule_date <= :dt', { dt: date_to });
      if (q.search) {
        qb.andWhere(
          '(row.audit_code LIKE :s OR company.name LIKE :s OR standards.name LIKE :s)',
          { s: `%${q.search}%` },
        );
      }
      return qb;
    };

    // ── Page of rows ──────────────────────────────────────────────────────
    const dataQb = this.dataSource
      .getRepository(AuditScheduleRow)
      .createQueryBuilder('row')
      .leftJoinAndSelect('row.schedule', 'schedule')
      .leftJoinAndSelect('schedule.coordinator', 'coordinator')
      .leftJoinAndSelect('row.company', 'company')
      .leftJoinAndSelect('row.lead_auditor', 'lead_auditor')
      .leftJoinAndSelect('row.standards', 'standards');

    applyScope(dataQb);

    dataQb
      .orderBy('schedule.schedule_date', 'DESC')
      .addOrderBy('row.audit_time', 'ASC')
      .addOrderBy('row.id', 'DESC')
      .skip(skip)
      .take(limit);

    const [data, total] = await dataQb.getManyAndCount();
    this.logger.log(`[SCHED-MASTER] total rows matched = ${total}`);

    // ── 🆕 Attach submitter (requested_by) to each row

    if (data.length) {
      const rowIds = data.map((r) => r.id);

      const links: Array<{ row_id: number; requested_by_id: number | null }> =
        await this.dataSource.query(
          `
          SELECT asr.id AS row_id, ar.requested_by_id AS requested_by_id
          FROM audit_schedule_rows asr
          LEFT JOIN audit_requests ar ON ar.id = asr.audit_request_id
          WHERE asr.id IN (?)
          `,
          [rowIds],
        );

      const userIds = Array.from(
        new Set(
          links
            .map((l) => l.requested_by_id)
            .filter((v): v is number => !!v),
        ),
      );

      const users = userIds.length
        ? await this.dataSource
          .getRepository(User)
          .find({ where: { id: In(userIds) } })
        : [];
      const userById = new Map(users.map((u) => [u.id, u]));

      const submitterByRow = new Map<number, any>();
      for (const l of links) {
        submitterByRow.set(
          Number(l.row_id),
          l.requested_by_id ? userById.get(l.requested_by_id) ?? null : null,
        );
      }

      for (const r of data) {
        (r as any).submitted_by = submitterByRow.get(r.id) ?? null;
      }

      // debug: how many rows got a submitter attached
      const withSub = data.filter((r) => (r as any).submitted_by).length;
      this.logger.log(
        `[SCHED-MASTER] submitters attached: ${withSub}/${data.length}`,
      );
    }

    // ── Type counts over the FULL scoped set ──────────────────────────────
    const countQb = this.dataSource
      .getRepository(AuditScheduleRow)
      .createQueryBuilder('row')
      .leftJoin('row.schedule', 'schedule')
      .leftJoin('row.company', 'company')
      .leftJoin('row.standards', 'standards')
      .select('UPPER(row.audit_type)', 'type')
      .addSelect('COUNT(DISTINCT row.id)', 'cnt')
      .groupBy('UPPER(row.audit_type)');

    applyScope(countQb);

    const rawCounts: Array<{ type: string; cnt: string }> =
      await countQb.getRawMany();

    const byType = (t: string) =>
      Number(rawCounts.find((r) => r.type === t)?.cnt ?? 0);

    const counts = {
      initial: byType('INITIAL'),
      surveillance: byType('SURVEILLANCE'),
      recertification: byType('RECERTIFICATION'),
      total: rawCounts.reduce((s, r) => s + Number(r.cnt), 0),
    };

    return {
      data,
      counts,
      meta: { total, page, limit, totalPages: Math.ceil(total / limit) },
    };
  }
  /**
   * KPI tiles for the auditor's home screen:
   *   today                  — rows scheduled for today
   *   this_week              — rows scheduled this week (Mon–Sun)
   *   in_progress            — rows currently in IN_PROGRESS status
   *   completed_this_month   — rows completed since start of current month
   */
  async myRowsAnalytics(currentUserId: number) {
    const today = this.toISODate(new Date());

    // This week — Mon to Sun
    const now = new Date();
    const day = now.getDay(); // 0=Sun, 1=Mon
    const monday = new Date(now);
    monday.setDate(now.getDate() - (day === 0 ? 6 : day - 1));
    const sunday = new Date(monday);
    sunday.setDate(monday.getDate() + 6);

    // Start of month
    const startOfMonth = new Date(now.getFullYear(), now.getMonth(), 1);

    // 🆕 Check if user can see all audits based on role
    const canViewAll = await this.userIsMyAuditsViewAll(currentUserId);

    const baseQb = () => {
      const qb = this.dataSource
        .getRepository(AuditScheduleRow)
        .createQueryBuilder('row')
        .innerJoin('row.schedule', 'schedule');
      if (canViewAll) {
        qb.where('1 = 1');
      } else {
        qb.where('row.lead_auditor_id = :uid', { uid: currentUserId });
      }
      return qb;
    };

    const [todayCount, thisWeekCount, inProgressCount, completedThisMonth] =
      await Promise.all([
        baseQb()
          .andWhere('schedule.schedule_date = :d', { d: today })
          .getCount(),
        baseQb()
          .andWhere(
            'schedule.schedule_date >= :s AND schedule.schedule_date <= :e',
            {
              s: this.toISODate(monday),
              e: this.toISODate(sunday),
            },
          )
          .getCount(),
        baseQb()
          .andWhere('row.status = :st', { st: RowStatus.IN_PROGRESS })
          .getCount(),
        baseQb()
          .andWhere('row.status = :st', { st: RowStatus.COMPLETED })
          .andWhere('row.updated_at >= :som', { som: startOfMonth })
          .getCount(),
      ]);

    return {
      today: todayCount,
      this_week: thisWeekCount,
      in_progress: inProgressCount,
      completed_this_month: completedThisMonth,
    };
  }

  /**
   * Single audit workspace — row + relations + audit trail.
   * Auth: must be the lead auditor or a co-auditor on the row, or have view-all permission.
   * Used by the /audits/[rowId] detail page.
   */
  async workspace(rowId: number, currentUserId: number) {
    const row = await this.findOneRow(rowId);

    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && !this.isAssignedToRow(row, currentUserId)) {
      throw new ForbiddenException('This audit is not assigned to you');
    }

    // 🆕 Read the linked audit_request_id directly from the row table
    //    (it may not be a mapped entity property).
    const linkRows = await this.dataSource.query(
      `SELECT audit_request_id FROM audit_schedule_rows WHERE id = ? LIMIT 1`,
      [rowId],
    );
    const auditRequestId = linkRows[0]?.audit_request_id ?? null;

    // Pull the related audit request (marketing/coordinator remarks + auditees).
    const auditRequest = auditRequestId
      ? await this.dataSource
        .createQueryBuilder()
        .select('ar.*')
        .from('audit_requests', 'ar')
        .where('ar.id = :id', { id: auditRequestId })
        .getRawOne()
      : null;

    // 🆕 Normalise auditees (stored as JSON) into a parsed array so the
    //    frontend can read audit_request.auditees directly.
    if (auditRequest && typeof auditRequest.auditees === 'string') {
      try {
        auditRequest.auditees = JSON.parse(auditRequest.auditees);
      } catch {
        auditRequest.auditees = [];
      }
    }

    const history = await this.getRowAuditTrail(rowId);

    return { row, audit_request: auditRequest, history };
  }

  /**
   * Reuses the existing updateRow() machinery (logs history, etc.).
   */
  async markComplete(rowId: number, currentUserId: number) {
    const row = await this.findOneRow(rowId);

    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && !this.isAssignedToRow(row, currentUserId)) {
      throw new ForbiddenException('You can only complete your own audits');
    }

    if (row.status === RowStatus.COMPLETED) {
      return this.findOneRow(rowId); // already done — no-op
    }
    if (row.status === RowStatus.CANCELLED) {
      throw new BadRequestException('Cannot complete a cancelled audit');
    }

    // an 'edit' on the schedule.
    return this.dataSource.transaction(async (manager) => {
      const oldStatus = row.status;
      const fresh = await manager.findOne(AuditScheduleRow, {
        where: { id: rowId },
      });
      if (!fresh) throw new NotFoundException('Audit row not found');

      fresh.status = RowStatus.COMPLETED;
      await manager.save(fresh);

      await manager.save(
        manager.create(AuditStatusHistory, {
          row_id: rowId,
          old_status: oldStatus,
          new_status: RowStatus.COMPLETED,
          changed_by_id: currentUserId,
          reason: 'Marked complete by auditor',
        }),
      );

      return this.findOneRow(rowId, manager);
    });
  }
  private static readonly DOC_COLUMN: Record<string, string> = {
    stage1_report: 'stg1_audit_report',
    stage2_report: 'stg2_audit_report',
    attendance: 'attendance_doc',
    nc_form: 'nc_form_doc',
    support_docs: 'support_docs',
  };

  async uploadAuditReport(
    rowId: number,
    docType: string,
    file: Express.Multer.File,
    currentUserId: number,
  ): Promise<{
    ok: true;
    row_id: number;
    doc_type: string;
    document_path: string;
    filename: string;
    size: number;
  }> {
    const column = AuditSchedulesService.DOC_COLUMN[docType];
    if (!column) {
      throw new BadRequestException(`Unknown document type: ${docType}`);
    }

    // Permission: lead auditor OR co-auditor on the row, or a view-all user.
    const row = await this.findOneRow(rowId);
    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && !this.isAssignedToRow(row, currentUserId)) {
      throw new ForbiddenException('This audit is not assigned to you');
    }

    // Read the previous path so we can clean it up if it was a managed file.
    const beforeRows = await this.dataSource.query(
      `SELECT \`${column}\` AS old_path FROM audit_schedule_rows WHERE id = ?`,
      [rowId],
    );
    const oldPath: string | null = beforeRows[0]?.old_path ?? null;

    // Save to disk via the storage service.
    let saved;
    try {
      saved = await this.reportStorage.saveReport(docType as any, rowId, {
        originalname: file.originalname,
        mimetype: file.mimetype,
        buffer: file.buffer,
        size: file.size,
      });
    } catch (err: any) {
      throw new BadRequestException(err.message);
    }

    // Persist the new path on the row.
    await this.dataSource.query(
      `UPDATE audit_schedule_rows SET \`${column}\` = ?, updated_at = NOW() WHERE id = ?`,
      [saved.dbPath, rowId],
    );

    // Delete the previous file only if it was a managed (new:) file.
    if (oldPath && this.reportStorage.isNewPath(oldPath)) {
      await this.reportStorage.deleteNewFile(oldPath).catch((e) =>
        this.logger.warn(`Failed to delete old ${docType} for row ${rowId}: ${e.message}`),
      );
    }

    this.logger.log(
      `[AUDIT-DOC] User ${currentUserId} uploaded ${docType} for row ${rowId} → ${saved.dbPath}`,
    );

    return {
      ok: true,
      row_id: rowId,
      doc_type: docType,
      document_path: saved.dbPath,
      filename: file.originalname,
      size: saved.size,
    };
  }

  async openAuditReport(
    rowId: number,
    docType: string,
    currentUserId: number,
  ): Promise<{
    stream: import('stream').Readable;
    size: number;
    mimeType: string;
    filename: string;
  }> {
    const column = AuditSchedulesService.DOC_COLUMN[docType];
    if (!column) {
      throw new BadRequestException(`Unknown document type: ${docType}`);
    }

    // Permission: lead auditor OR co-auditor on the row, or a view-all user.
    const row = await this.findOneRow(rowId);
    const canViewAll = await this.userCanViewAll(currentUserId);
    if (!canViewAll && !this.isAssignedToRow(row, currentUserId)) {
      throw new ForbiddenException('This audit is not assigned to you');
    }

    const rows = await this.dataSource.query(
      `SELECT \`${column}\` AS doc_path FROM audit_schedule_rows WHERE id = ?`,
      [rowId],
    );
    const docPath: string | null = rows[0]?.doc_path ?? null;
    if (!docPath) {
      throw new NotFoundException(`No ${docType} uploaded for this audit`);
    }

    return this.reportStorage.openFile(docPath);
  }
  /**
   * Date-preset resolver shared by myRows().
   * Returns ISO date strings (YYYY-MM-DD) or undefined.
   */
  private resolveMyAuditsDateRange(
    preset: string | undefined,
    rawFrom: string | undefined,
    rawTo: string | undefined,
  ): { date_from?: string; date_to?: string } {
    if (preset && preset !== 'all') {
      const today = new Date();

      if (preset === 'today') {
        const d = this.toISODate(today);
        return { date_from: d, date_to: d };
      }
      if (preset === 'this_week') {
        const day = today.getDay();
        const monday = new Date(today);
        monday.setDate(today.getDate() - (day === 0 ? 6 : day - 1));
        const sunday = new Date(monday);
        sunday.setDate(monday.getDate() + 6);
        return {
          date_from: this.toISODate(monday),
          date_to: this.toISODate(sunday),
        };
      }
      if (preset === 'this_month') {
        const start = new Date(today.getFullYear(), today.getMonth(), 1);
        const end = new Date(today.getFullYear(), today.getMonth() + 1, 0);
        return {
          date_from: this.toISODate(start),
          date_to: this.toISODate(end),
        };
      }
      if (preset === 'past') {
        const yesterday = new Date(today);
        yesterday.setDate(today.getDate() - 1);
        return { date_to: this.toISODate(yesterday) };
      }
    }
    return { date_from: rawFrom, date_to: rawTo };
  }

  // 🛠 FIX: was `d.toISOString().slice(0, 10)`, which converts to UTC first.
  // For a server whose local timezone isn't UTC, that silently shifts the
  // calendar date by a day for part of the day (e.g. any time after 8pm
  // local in UTC+4 rolls into "tomorrow" in UTC). Build the string from
  // LOCAL getters instead, so it always matches the server's wall-clock date.
  private toISODate(d: Date): string {
    const y = d.getFullYear();
    const m = String(d.getMonth() + 1).padStart(2, '0');
    const day = String(d.getDate()).padStart(2, '0');
    return `${y}-${m}-${day}`;
  }

  // 🛠 FIX: safely parse a "YYYY-MM-DD" (date-only, no time) string into a
  // local Date at local midnight. Using `new Date("2026-07-21")` directly
  // parses it as UTC midnight per the ISO-8601 spec, which — combined with
  // local getters elsewhere — is the root cause of the "wrong date" bug.
  // This helper is UTC-vs-local-safe regardless of server timezone.
  private parseISODateLocal(dateStr: string): Date {
    const [y, m, d] = dateStr.split('-').map((v) => parseInt(v, 10));
    return new Date(y, (m || 1) - 1, d || 1);
  }
  /**
   * 🆕 Role-based view-all check for My Audits.
   * Returns true if user has any of the "view-all" roles → they see ALL audits.
   * Returns false otherwise → they see only their own (lead_auditor_id).
   * No hardcoded user IDs. Add/remove roles by editing VIEW_ALL_ROLES.
   */
  private async userIsMyAuditsViewAll(userId: number): Promise<boolean> {
    // 🆕 Super-admin IDs always see everything (same as userCanViewAll).
    if (SUPER_ADMIN_IDS.includes(userId)) {
      this.logger.log(`[MY-AUDITS-PERM] User ${userId} → SUPER ADMIN bypass → VIEW ALL`);
      return true;
    }

    // Role names (lowercase) that should see ALL audits on /my-audits.
    const VIEW_ALL_ROLES = ['super-admin', 'scheme', 'coordinator'];

    const result = await this.dataSource.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],
    );

    const roleNames: string[] = result.map((r: any) => r.role_name);
    const hasViewAll = roleNames.some((rn) => VIEW_ALL_ROLES.includes(rn));

    this.logger.log(
      `[MY-AUDITS-PERM] User ${userId} roles=[${roleNames.join(',')}] → ${hasViewAll ? 'VIEW ALL' : 'OWN ONLY'}`,
    );

    return hasViewAll;
  }
  // ═══════════════════════════════════════════════════════════════
  // HTML builders for emails (call MailsService brand shell)
  // Implemented inline here for now; can be moved to a templating
  // service later if you want admin-editable templates.
  // ═══════════════════════════════════════════════════════════════

  private fullName(u: User): string {
    const f = (u as any).firstName || '';
    const l = (u as any).lastName || '';
    const both = `${f} ${l}`.trim();
    return both || u.email;
  }

  private buildPublishedHtml(ctx: {
    recipientName: string;
    auditCode: string;
    companyName: string;
    auditDate: string;
    auditTimeLabel?: string;
    auditorName?: string;
    standards?: string;
    scheduleId?: number;
  }): string {
    const link = ctx.scheduleId
      ? `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-schedules/${ctx.scheduleId}`
      : null;
    return this.qrsShell(
      'Audit Schedule Published',
      `
        <div style="font-size:48px;margin-bottom:8px;">📅</div>
        <h2 style="color:#1a0440;font-size:20px;margin:0 0 12px;">Audit Scheduled</h2>
        <p style="color:#666;font-size:14px;line-height:1.7;margin:0 0 24px;">
          Hello <strong>${ctx.recipientName}</strong>,<br/>
          An audit has been scheduled and you have been assigned as a recipient.
        </p>
        ${this.detailBlock(ctx)}
        ${link ? `<a href="${link}" style="display:inline-block;padding:13px 32px;background:linear-gradient(135deg,#8b14d4,#4a0080);color:#fff;text-decoration:none;border-radius:8px;font-weight:700;font-size:15px;margin-top:24px;">View Schedule →</a>` : ''}
      `,
    );
  }

  private buildCancelledHtml(ctx: any): string {
    const link = ctx.scheduleId
      ? `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-schedules/${ctx.scheduleId}`
      : null;
    return this.qrsShell(
      'Audit Cancelled',
      `
        <div style="font-size:48px;margin-bottom:8px;">❌</div>
        <h2 style="color:#1a0440;font-size:20px;margin:0 0 12px;">Audit Cancelled</h2>
        <p style="color:#666;font-size:14px;line-height:1.7;margin:0 0 24px;">
          Hello <strong>${ctx.recipientName}</strong>,<br/>
          An audit assigned to you has been <strong>cancelled</strong>.
        </p>
        ${this.detailBlock(ctx)}
        <div style="background:#fee2e2;border-left:4px solid #dc2626;padding:14px 18px;border-radius:8px;text-align:left;margin-top:20px;">
          <p style="margin:0;color:#991b1b;font-size:13px;"><strong>Reason:</strong> ${ctx.reason || '—'}</p>
          ${ctx.notes ? `<p style="margin:6px 0 0;color:#991b1b;font-size:13px;"><strong>Notes:</strong> ${ctx.notes}</p>` : ''}
          ${ctx.triggeredByName ? `<p style="margin:6px 0 0;color:#991b1b;font-size:12px;"><strong>Cancelled by:</strong> ${ctx.triggeredByName}</p>` : ''}
        </div>
        ${link ? `<a href="${link}" style="display:inline-block;padding:13px 32px;background:linear-gradient(135deg,#8b14d4,#4a0080);color:#fff;text-decoration:none;border-radius:8px;font-weight:700;font-size:15px;margin-top:24px;">View Schedule →</a>` : ''}
      `,
    );
  }

  private buildRescheduledHtml(ctx: any): string {
    const link = ctx.scheduleId
      ? `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-schedules/${ctx.scheduleId}`
      : null;
    return this.qrsShell(
      'Audit Rescheduled',
      `
        <div style="font-size:48px;margin-bottom:8px;">🔄</div>
        <h2 style="color:#1a0440;font-size:20px;margin:0 0 12px;">Audit Rescheduled</h2>
        <p style="color:#666;font-size:14px;line-height:1.7;margin:0 0 24px;">
          Hello <strong>${ctx.recipientName}</strong>,<br/>
          An audit has been rescheduled to a new date.
        </p>
        ${this.detailBlock(ctx)}
        <div style="background:#fef3c7;border-left:4px solid #f59e0b;padding:14px 18px;border-radius:8px;text-align:left;margin-top:20px;">
          <p style="margin:0;color:#92400e;font-size:13px;"><strong>Original date:</strong> ${ctx.oldDate || '—'}</p>
          <p style="margin:6px 0;color:#92400e;font-size:13px;"><strong>New date:</strong> ${ctx.newDate || '—'}${ctx.auditTimeLabel ? ` at ${ctx.auditTimeLabel}` : ''}</p>
          ${ctx.reason ? `<p style="margin:6px 0 0;color:#92400e;font-size:13px;"><strong>Reason:</strong> ${ctx.reason}</p>` : ''}
          ${ctx.triggeredByName ? `<p style="margin:6px 0 0;color:#92400e;font-size:12px;"><strong>Rescheduled by:</strong> ${ctx.triggeredByName}</p>` : ''}
        </div>
        ${link ? `<a href="${link}" style="display:inline-block;padding:13px 32px;background:linear-gradient(135deg,#8b14d4,#4a0080);color:#fff;text-decoration:none;border-radius:8px;font-weight:700;font-size:15px;margin-top:24px;">View Schedule →</a>` : ''}
      `,
    );
  }

  private buildBulkCancelledHtml(ctx: any): string {
    const link = ctx.scheduleId
      ? `${process.env.APP_URL || 'https://web.qrsyst.com'}/audit-schedules/${ctx.scheduleId}`
      : null;
    return this.qrsShell(
      'Audits Cancelled',
      `
        <div style="font-size:48px;margin-bottom:8px;">🚫</div>
        <h2 style="color:#1a0440;font-size:20px;margin:0 0 12px;">All Audits Cancelled</h2>
        <p style="color:#666;font-size:14px;line-height:1.7;margin:0 0 24px;">
          Hello <strong>${ctx.recipientName}</strong>,<br/>
          All <strong>${ctx.affectedCount ?? ''} audits</strong> scheduled for <strong>${ctx.auditDate}</strong> have been cancelled.
        </p>
        <div style="background:#fee2e2;border-left:4px solid #dc2626;padding:14px 18px;border-radius:8px;text-align:left;">
          <p style="margin:0;color:#991b1b;font-size:13px;"><strong>Reason:</strong> ${ctx.reason || '—'}</p>
          ${ctx.notes ? `<p style="margin:6px 0 0;color:#991b1b;font-size:13px;"><strong>Notes:</strong> ${ctx.notes}</p>` : ''}
          ${ctx.triggeredByName ? `<p style="margin:6px 0 0;color:#991b1b;font-size:12px;"><strong>Cancelled by:</strong> ${ctx.triggeredByName}</p>` : ''}
        </div>
        ${link ? `<a href="${link}" style="display:inline-block;padding:13px 32px;background:linear-gradient(135deg,#8b14d4,#4a0080);color:#fff;text-decoration:none;border-radius:8px;font-weight:700;font-size:15px;margin-top:24px;">View Schedule →</a>` : ''}
      `,
    );
  }

  private qrsShell(headerLabel: string, innerHtml: string): string {
    return `
      <div style="font-family:'Segoe UI',sans-serif;max-width:560px;margin:0 auto;background:#fff;border-radius:16px;overflow:hidden;box-shadow:0 4px 24px rgba(0,0,0,0.08);">
        <div style="background:linear-gradient(135deg,#8b14d4,#4a0080);padding:32px;text-align:center;">
          <h1 style="color:#fff;font-size:20px;margin:0;">Quality Registrar Systems</h1>
          <p style="color:rgba(255,255,255,0.7);margin:6px 0 0;font-size:13px;">${headerLabel}</p>
        </div>
        <div style="padding:36px 32px;text-align:center;">
          ${innerHtml}
        </div>
        <div style="background:#f8f5ff;padding:16px 32px;text-align:center;border-top:1px solid #ede8ff;">
          <p style="color:#bbb;font-size:11px;margin:0;">© ${new Date().getFullYear()} Quality Registrar Systems.</p>
        </div>
      </div>
    `;
  }

  private detailBlock(ctx: {
    auditCode: string;
    companyName: string;
    auditDate: string;
    auditTimeLabel?: string;
    auditorName?: string;
    standards?: string;
  }): string {
    return `
      <div style="background:#f8f5ff;border:1px solid #ede8ff;border-radius:12px;padding:18px 22px;text-align:left;margin:0 auto;">
        <table style="width:100%;border-collapse:collapse;font-size:13px;color:#555;">
          <tr><td style="padding:5px 0;color:#888;width:40%;">Audit Code:</td><td style="padding:5px 0;color:#1a0440;font-weight:700;font-family:'Courier New',monospace;">${ctx.auditCode}</td></tr>
          <tr><td style="padding:5px 0;color:#888;">Company:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.companyName}</td></tr>
          <tr><td style="padding:5px 0;color:#888;">Date:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.auditDate}${ctx.auditTimeLabel ? ` · ${ctx.auditTimeLabel}` : ''}</td></tr>
          ${ctx.auditorName ? `<tr><td style="padding:5px 0;color:#888;">Auditor:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.auditorName}</td></tr>` : ''}
          ${ctx.standards ? `<tr><td style="padding:5px 0;color:#888;">Standards:</td><td style="padding:5px 0;color:#1a0440;font-weight:600;">${ctx.standards}</td></tr>` : ''}
        </table>
      </div>
    `;
  }
  
}