import {
  BadRequestException,
  ForbiddenException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';

import {
  PlenaryStatus,
  Prisma,
  type PlenaryRgcDecision,
  type RgcDecisionStatus,
} from '@/generated/prisma/client';
import { PrismaService } from '@/prisma/prisma.service';

import { ResendMailPlenaryService } from '../mail/resend-mail-plenary.service';

import { CreateCdcRgcDecisionDto } from './dto/create-cdc-rgc-decision.dto';
import { CreateRgcDecisionDto } from './dto/create-rgc-decision.dto';
import { RgcDecisionQueryDto } from './dto/rgc-decision-query.dto';
import { UpdateRgcDecisionDto } from './dto/update-rgc-decision.dto';

const MINISTRY_STAKEHOLDER_TYPE = 'Ministry';

const MONTHS: Record<string, number> = {
  january: 0,
  february: 1,
  march: 2,
  april: 3,
  may: 4,
  june: 5,
  july: 6,
  august: 7,
  september: 8,
  october: 9,
  november: 10,
  december: 11,
};

type StakeholderItem = {
  id: number;
  name: string;
  logo: string | null;
};

type CategoryItem = {
  id: number;
  name: string;
};

type IndicatorItem = {
  id: number;
  name: string;
  description: string | null;
};

type UserItem = {
  id: number;
  name: string;
  email: string;
};

type PlenaryItem = {
  id: number;
  name: string;
};

type CsvValue = string | number | boolean | Date | null | undefined;

type DecisionRelations = {
  stakeholders: Map<number, StakeholderItem>;
  categories: Map<number, CategoryItem>;
  indicators: Map<number, IndicatorItem>;
  users: Map<number, UserItem>;
  plenaries: Map<number, PlenaryItem>;
  issuesMap: Map<number, any[]>;
};

function createDate(
  year: number,
  month: number,
  day: number,
  fieldName: string,
): Date {
  const date = new Date(Date.UTC(year, month, day, 12, 0, 0));

  const isValid =
    date.getUTCFullYear() === year &&
    date.getUTCMonth() === month &&
    date.getUTCDate() === day;

  if (!isValid) {
    throw new BadRequestException(`${fieldName} is not a valid date.`);
  }

  return date;
}

function parseRgcDecisionDate(value: string, fieldName: string): Date {
  const rawValue = value.trim();

  if (!rawValue) {
    throw new BadRequestException(`${fieldName} is required.`);
  }

  const friendlyMatch = rawValue.match(
    /^([A-Za-z]+)\s+(\d{1,2}),\s*(\d{4})(?:\s*(?:At\s+|,\s*|\s+)(\d{1,2}):(\d{2})\s*(AM|PM))?$/i,
  );

  if (friendlyMatch) {
    const [, monthName, dayText, yearText, hourText, minuteText, periodText] =
      friendlyMatch;

    const month = MONTHS[monthName.toLowerCase()];
    const day = Number(dayText);
    const year = Number(yearText);

    if (
      month === undefined ||
      !Number.isInteger(day) ||
      !Number.isInteger(year) ||
      day < 1
    ) {
      throw new BadRequestException(`${fieldName} is not a valid date.`);
    }

    const hasTime =
      hourText !== undefined ||
      minuteText !== undefined ||
      periodText !== undefined;

    if (hasTime) {
      const hour = Number(hourText);
      const minute = Number(minuteText);
      const period = periodText?.toUpperCase();

      if (
        !Number.isInteger(hour) ||
        !Number.isInteger(minute) ||
        hour < 1 ||
        hour > 12 ||
        minute < 0 ||
        minute > 59 ||
        (period !== 'AM' && period !== 'PM')
      ) {
        throw new BadRequestException(
          `${fieldName} time must be like: 10:00 AM`,
        );
      }
    }

    return createDate(year, month, day, fieldName);
  }

  const isoMatch = rawValue.match(/^(\d{4})-(\d{2})-(\d{2})(?:T.*)?$/);

  if (isoMatch) {
    const [, yearText, monthText, dayText] = isoMatch;

    return createDate(
      Number(yearText),
      Number(monthText) - 1,
      Number(dayText),
      fieldName,
    );
  }

  throw new BadRequestException(
    `${fieldName} must be like: June 25, 2025 or June 25, 2025 At 10:00 AM`,
  );
}

@Injectable()
export class RgcDecisionService {
  constructor(
    private readonly prisma: PrismaService,
    private readonly resendMailPlenaryService: ResendMailPlenaryService,
  ) {}

  async getIssues(): Promise<{ items: any[] }> {
    const items = await this.prisma.issues.findMany({
      where: {
        deletedAt: null,
        OR: [
          {
            issueStatus: {
              code: {
                in: [
                  'IN_PROGRESS',
                  'NOT_ADDRESSED',
                  'in_progress',
                  'not_addressed',
                ],
                mode: 'insensitive',
              },
            },
          },
          {
            issueStatus: {
              name: {
                in: [
                  'In Progress',
                  'Not Addressed',
                  'in progress',
                  'not addressed',
                ],
                mode: 'insensitive',
              },
            },
          },
        ],
      },
      include: {
        issueStatus: {
          select: { id: true, code: true, name: true },
        },
        category: {
          select: { id: true, name: true },
        },
        meetingRequest: {
          select: {
            id: true,
            title: true,
            meetings: {
              select: {
                id: true,
                meetingDate: true,
                startTime: true,
                endTime: true,
              },
            },
          },
        },
        stakeholder: {
          select: {
            id: true,
            name: true,
            logo: true,
            stakeholderType: { select: { id: true, name: true } },
          },
        },
        user: {
          select: {
            id: true,
            email: true,
            name: true,
            position: true,
            avatar: true,
          },
        },
        governmentAgencies: {
          orderBy: { agencyOrder: 'asc' },
          select: {
            agencyOrder: true,
            stakeholder: {
              select: {
                id: true,
                name: true,
                logo: true,
                stakeholderType: { select: { id: true, name: true } },
              },
            },
          },
        },
      },
      orderBy: {
        createdAt: 'desc',
      },
    });

    return { items };
  }

  async getCategories(): Promise<{ items: CategoryItem[] }> {
    const items = await this.prisma.categories.findMany({
      where: {
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
      },
      orderBy: {
        name: 'asc',
      },
    });

    return { items };
  }

  async getMinistries(): Promise<{ items: StakeholderItem[] }> {
    const items = await this.prisma.stakeholder.findMany({
      where: {
        deletedAt: null,
        active: true,
        stakeholderType: {
          deletedAt: null,
          name: {
            equals: MINISTRY_STAKEHOLDER_TYPE,
            mode: 'insensitive',
          },
        },
      },
      select: {
        id: true,
        name: true,
        logo: true,
      },
      orderBy: {
        name: 'asc',
      },
    });

    return { items };
  }

  async getPlenaries(): Promise<{ items: PlenaryItem[] }> {
    const items = await this.prisma.plenary.findMany({
      where: {
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
      },
      orderBy: [
        {
          meetingDate: 'desc',
        },
        {
          id: 'desc',
        },
      ],
    });

    return {
      items: items.map((item) => ({
        id: item.id,
        name: item.name?.trim() || `Plenary ${item.id}`,
      })),
    };
  }

  async getPlenaryMinistries(
    plenaryId: number,
    currentUserId?: number,
  ): Promise<{ items: StakeholderItem[] }> {
    await this.getActivePlenary(plenaryId);
    await this.ensurePlenaryVisibleToCurrentUser(plenaryId, currentUserId);

    const attachedMinistryIds = await this.getAttachedMinistryIds(plenaryId);
    const userMinistryIds = await this.getCurrentUserMinistryIds(currentUserId);

    const allowedMinistryIds =
      userMinistryIds.length > 0
        ? attachedMinistryIds.filter((id) => userMinistryIds.includes(id))
        : attachedMinistryIds;

    const items = await this.getActiveMinistriesByIds(allowedMinistryIds);

    return { items };
  }

  async findAll(query: RgcDecisionQueryDto, currentUserId?: number) {
    const page = query.page ?? 1;
    const limit = query.limit ?? 10;
    const skip = (page - 1) * limit;

    const where: Prisma.PlenaryRgcDecisionWhereInput = {
      deletedAt: null,
    };
    let ministryIds: number[] = [];

    if (query.ministryOnly === true) {
      ministryIds = await this.getCurrentUserMinistryIds(currentUserId);
      where.stakeholderId = { in: ministryIds };
    }

    if (query.plenaryId !== undefined) {
      where.plenaryId = query.plenaryId;
    }

    if (query.categoryId !== undefined) {
      where.categoryId = query.categoryId;
    }

    if (query.indicatorId !== undefined) {
      where.indicatorId = query.indicatorId;
    }

    const status = this.toDecisionStatus(query.status);

    if (status) {
      where.status = status;
    }

    if (query.search?.trim()) {
      where.decision = {
        contains: query.search.trim(),
        mode: 'insensitive',
      };
    }

    // Keep an explicit stakeholder filter inside the signed-in Ministry scope.
    if (query.stakeholderId !== undefined) {
      where.stakeholderId =
        query.ministryOnly === true &&
        !ministryIds.includes(query.stakeholderId)
          ? { in: [] }
          : query.stakeholderId;
    }

    const [items, total] = await this.prisma.$transaction([
      this.prisma.plenaryRgcDecision.findMany({
        where,
        orderBy: {
          createdAt: 'desc',
        },
        skip,
        take: limit,
      }),
      this.prisma.plenaryRgcDecision.count({ where }),
    ]);

    const hydratedItems = await this.hydrateDecisions(items);

    return {
      items:
        query.ministryOnly === true
          ? await this.attachLatestProgressUpdates(hydratedItems, ministryIds)
          : hydratedItems,
      meta: {
        total,
        page,
        limit,
        totalPages: Math.max(1, Math.ceil(total / limit)),
      },
    };
  }

  private async attachLatestProgressUpdates<T extends { id: number }>(
    decisions: T[],
    ministryIds: number[],
  ) {
    if (decisions.length === 0 || ministryIds.length === 0) {
      return decisions.map((decision) => ({
        ...decision,
        progressUpdate: null,
      }));
    }

    const relations =
      await this.prisma.progressReportUpdatePlenaryDecision.findMany({
        where: {
          plenaryDecisionId: {
            in: decisions.map((decision) => decision.id),
          },
          ministryId: { in: ministryIds },
          deletedAt: null,
          progressReportUpdate: { deletedAt: null },
        },
        include: { progressReportUpdate: true },
      });

    const newestRelations = [...relations].sort(
      (first, second) =>
        second.progressReportUpdate.updatedAt.getTime() -
          first.progressReportUpdate.updatedAt.getTime() ||
        second.id - first.id,
    );
    const latestByDecisionId = new Map<
      number,
      (typeof newestRelations)[number]['progressReportUpdate']
    >();

    for (const relation of newestRelations) {
      if (!latestByDecisionId.has(relation.plenaryDecisionId)) {
        latestByDecisionId.set(
          relation.plenaryDecisionId,
          relation.progressReportUpdate,
        );
      }
    }

    return decisions.map((decision) => {
      const update = latestByDecisionId.get(decision.id);

      return {
        ...decision,
        progressUpdate: update
          ? {
              id: update.id,
              indicators: update.indicators,
              progressSolution: update.progressSolution,
              implementationChallenges: update.implementationChallenges,
              requests: update.requests,
              nextStep: update.next_step,
              dateOfIssueSolution: update.dateOfIssueSolution
                ? update.dateOfIssueSolution.toISOString().slice(0, 10)
                : null,
              attachment: update.attachement,
              updatedAt: update.updatedAt,
            }
          : null,
      };
    });
  }

  async findAllForCdcGpsf(query: RgcDecisionQueryDto, currentUserId?: number) {
    const page = query.page ?? 1;
    const limit = query.limit ?? 10;
    const skip = (page - 1) * limit;

    const where: Prisma.PlenaryRgcDecisionWhereInput = {
      deletedAt: null,
      OR:
        currentUserId !== undefined
          ? [
              {
                submittedToCdcAt: {
                  not: null,
                },
              },
              {
                submittedToCdcAt: null,
                userId: currentUserId,
              },
            ]
          : [
              {
                submittedToCdcAt: {
                  not: null,
                },
              },
            ],
    };

    if (query.plenaryId !== undefined) {
      where.plenaryId = query.plenaryId;
    }

    if (query.categoryId !== undefined) {
      where.categoryId = query.categoryId;
    }

    if (query.indicatorId !== undefined) {
      where.indicatorId = query.indicatorId;
    }

    if (query.stakeholderId !== undefined) {
      where.stakeholderId = query.stakeholderId;
    }

    const status = this.toDecisionStatus(query.status);

    if (status) {
      where.status = status;
    }

    if (query.search?.trim()) {
      const keyword = query.search.trim();

      where.OR = [
        {
          decision: {
            contains: keyword,
            mode: 'insensitive',
          },
        },
        {
          focalPerson: {
            contains: keyword,
            mode: 'insensitive',
          },
        },
        {
          verificationSource: {
            contains: keyword,
            mode: 'insensitive',
          },
        },
      ];
    }

    const [items, total] = await this.prisma.$transaction([
      this.prisma.plenaryRgcDecision.findMany({
        where,
        orderBy: {
          createdAt: 'desc',
        },
        skip,
        take: limit,
      }),
      this.prisma.plenaryRgcDecision.count({
        where,
      }),
    ]);

    return {
      items: await this.hydrateDecisions(items),
      meta: {
        total,
        page,
        limit,
        totalPages: Math.max(1, Math.ceil(total / limit)),
      },
    };
  }

  async findOne(decisionId: number, currentUserId?: number) {
    const decision = await this.getActiveDecision(decisionId);

    await this.ensureDecisionVisibleToCurrentUser(decision, currentUserId);

    const items = await this.hydrateDecisions([decision]);

    if (items.length === 0) {
      throw new NotFoundException('RGC Decision not found.');
    }

    return items[0];
  }

  async create(dto: CreateRgcDecisionDto, requestedUserId: number) {
    return this.createForMinistry(dto, requestedUserId);
  }

  async createForMinistry(dto: CreateRgcDecisionDto, requestedUserId: number) {
    const plenary = await this.getActivePlenary(dto.plenaryId);

    if (plenary.status !== PlenaryStatus.SENT) {
      throw new BadRequestException(
        'RGC Decision can be created only after Plenary has been sent.',
      );
    }

    const actor = await this.getActiveUser(requestedUserId);
    const actorMinistryIds = await this.getCurrentUserMinistryIds(actor.id);

    if (actorMinistryIds.length === 0) {
      throw new ForbiddenException(
        'Only a Ministry user can create RGC Decision records.',
      );
    }

    const stakeholderId = await this.resolveStakeholderForActor(
      dto.plenaryId,
      dto.stakeholderId,
      actorMinistryIds,
    );

    const categoryId = await this.resolveCategoryId(
      dto.categoryId,
      dto.category,
      actor.id,
    );

    const indicatorId = await this.resolveIndicatorId(dto.indicatorId);

    const meetingDate = dto.meetingDate
      ? parseRgcDecisionDate(dto.meetingDate, 'Meeting Date')
      : plenary.meetingDate;

    const status = this.toDecisionStatus(dto.status) ?? 'NOT_ADDRESSED';

    const createData: Prisma.PlenaryRgcDecisionCreateInput = {
      plenary: {
        connect: {
          id: dto.plenaryId,
        },
      },

      stakeholder: {
        connect: {
          id: stakeholderId,
        },
      },

      category: {
        connect: {
          id: categoryId,
        },
      },

      indicator: indicatorId
        ? {
            connect: {
              id: indicatorId,
            },
          }
        : undefined,

      meetingDate,
      status,
      focalPerson: dto.focalPerson?.trim() || actor.name || actor.email,
      decision: dto.decision.trim(),
      verificationSource: dto.verificationSource?.trim() || null,
      verificationLink: dto.verificationLink?.trim() || null,

      user: {
        connect: {
          id: actor.id,
        },
      },
    };

    if (dto.issueIds?.length) {
      createData.decisionIssues = {
        create: dto.issueIds.map((issueId: number) => ({
          issue: {
            connect: {
              id: issueId,
            },
          },
        })),
      };
    }

    const created = await this.prisma.plenaryRgcDecision.create({
      data: createData,
    });

    return this.findOne(created.id, actor.id);
  }

  async createForCdcGpsf(
    dto: CreateCdcRgcDecisionDto,
    requestedUserId: number,
  ) {
    const actor = await this.getActiveUser(requestedUserId);

    await this.assertCdcGpsfUser(actor.id);

    const plenary = await this.getActivePlenary(dto.plenaryId);

    const stakeholderId = await this.resolveStakeholderForCdc(
      dto.stakeholderId,
    );

    const categoryId = await this.resolveCategoryId(
      dto.categoryId,
      dto.category,
      actor.id,
    );

    const indicatorId = await this.resolveIndicatorId(dto.indicatorId);

    const meetingDate = parseRgcDecisionDate(dto.meetingDate, 'Meeting Date');

    const status = this.toDecisionStatus(dto.status) ?? 'NOT_ADDRESSED';

    const createData: Prisma.PlenaryRgcDecisionUncheckedCreateInput = {
      plenaryId: plenary.id,
      stakeholderId,
      categoryId,
      indicatorId: indicatorId ?? undefined,
      meetingDate,
      status,
      focalPerson: dto.focalPerson?.trim() || actor.name || actor.email,
      decision: dto.decision.trim(),
      verificationSource: dto.verificationSource?.trim() || null,
      verificationLink: dto.verificationLink?.trim() || null,
      submittedToCdcAt: dto.saveAsDraft ? null : new Date(),
      userId: actor.id,
    };

    if (dto.issueIds && dto.issueIds.length > 0) {
      // eslint-disable-next-line @typescript-eslint/no-unsafe-member-access
      (createData as any).decisionIssues = {
        create: dto.issueIds.map((issueId: number) => ({
          issue: { connect: { id: issueId } },
        })),
      };
    }

    const created = await this.prisma.plenaryRgcDecision.create({
      data: createData,
    });

    return this.findOne(created.id, actor.id);
  }

  async update(
    decisionId: number,
    dto: UpdateRgcDecisionDto,
    requestedUserId: number,
  ) {
    const current = await this.getActiveDecision(decisionId);
    const actor = await this.getActiveUser(requestedUserId);

    await this.ensureDecisionEditableByRelatedMinistry(current, actor.id);

    const actorMinistryIds = await this.getCurrentUserMinistryIds(actor.id);

    let nextStakeholderId = current.stakeholderId;
    let nextCategoryId = current.categoryId;
    let nextIndicatorId = current.indicatorId;

    if (dto.stakeholderId !== undefined) {
      nextStakeholderId = await this.resolveStakeholderForActor(
        current.plenaryId,
        dto.stakeholderId,
        actorMinistryIds,
      );
    }

    if (dto.categoryId !== undefined || dto.category !== undefined) {
      nextCategoryId = await this.resolveCategoryId(
        dto.categoryId,
        dto.category,
        actor.id,
      );
    }

    if (dto.indicatorId !== undefined) {
      nextIndicatorId = await this.resolveIndicatorId(dto.indicatorId);
    }

    const updateData: Prisma.PlenaryRgcDecisionUncheckedUpdateInput = {
      stakeholderId: nextStakeholderId,
      categoryId: nextCategoryId,
      indicatorId: nextIndicatorId ?? undefined,
    };

    if (dto.meetingDate !== undefined) {
      updateData.meetingDate = parseRgcDecisionDate(
        dto.meetingDate,
        'Meeting Date',
      );
    }

    if (dto.status !== undefined) {
      const status = this.toDecisionStatus(dto.status);

      if (status) {
        updateData.status = status;
      }
    }

    if (dto.focalPerson !== undefined) {
      updateData.focalPerson = dto.focalPerson.trim();
    }

    if (dto.decision !== undefined) {
      updateData.decision = dto.decision.trim();
    }

    if (dto.verificationSource !== undefined) {
      updateData.verificationSource = dto.verificationSource.trim() || null;
    }

    if (dto.verificationLink !== undefined) {
      updateData.verificationLink = dto.verificationLink.trim() || null;
    }

    if (dto.issueIds !== undefined) {
      // eslint-disable-next-line @typescript-eslint/no-unsafe-member-access
      (updateData as any).decisionIssues = {
        deleteMany: {},
        create: dto.issueIds.map((issueId: number) => ({
          issue: { connect: { id: issueId } },
        })),
      };
    }

    await this.prisma.plenaryRgcDecision.update({
      where: {
        id: decisionId,
      },
      data: updateData,
    });

    return this.findOne(decisionId, actor.id);
  }

  async submitToCdc(decisionId: number, requestedUserId: number) {
    const decision = await this.getActiveDecision(decisionId);

    if (!decision.plenaryId) {
      throw new BadRequestException(
        'This RGC Decision is not linked to a Plenary.',
      );
    }

    const plenaryId = decision.plenaryId;
    const actorUser = await this.getActiveUser(requestedUserId);

    await this.ensureDecisionEditableByCurrentUser(decision, actorUser.id);

    const hydratedDecision = await this.findOne(decision.id, actorUser.id);

    const receiverTargets = await this.getCdcReceiverTargets();

    if (
      receiverTargets.stakeholderIds.length === 0 &&
      receiverTargets.userIds.length === 0
    ) {
      throw new BadRequestException(
        'CDC G-PSF receiver was not found. Please check CDC stakeholder or CDC user setup.',
      );
    }

    const plenaryName = hydratedDecision.plenary?.name?.trim() || 'Plenary';

    const ministryName =
      hydratedDecision.stakeholder?.name?.trim() || 'Ministry';

    const categoryName = hydratedDecision.category?.trim() || 'RGC Decision';

    const title = `${ministryName} submitted RGC Decision`;

    const message = `${ministryName} submitted an RGC Decision for "${plenaryName}".`;

    const notificationData: Prisma.InputJsonObject = {
      notificationLabel: 'RGC Decision Submitted',
      rgcDecisionId: decision.id,
      plenaryId,
      plenaryName,
      stakeholderId: decision.stakeholderId,
      ministryName,
      categoryId: decision.categoryId,
      categoryName,
      status: hydratedDecision.status,
      senderName: actorUser.name,
      url: `/cdc-gpsf/plenary/plenaries/${plenaryId}`,
    };

    let created = 0;
    let updated = 0;

    for (const receiverStakeholderId of receiverTargets.stakeholderIds) {
      const result = await this.createOrUpdateSubmitNotification({
        decisionId: decision.id,
        title,
        message,
        senderUserId: actorUser.id,
        receiverStakeholderId,
        data: notificationData,
      });

      if (result === 'created') {
        created += 1;
      } else {
        updated += 1;
      }
    }

    for (const receiverUserId of receiverTargets.userIds) {
      const result = await this.createOrUpdateSubmitNotification({
        decisionId: decision.id,
        title,
        message,
        senderUserId: actorUser.id,
        receiverUserId,
        data: notificationData,
      });

      if (result === 'created') {
        created += 1;
      } else {
        updated += 1;
      }
    }

    const submittedDecision = await this.prisma.plenaryRgcDecision.update({
      where: {
        id: decision.id,
      },
      data: {
        submittedToCdcAt: new Date(),
      },
    });

    const receiverEmails = await this.getCdcReceiverEmails(receiverTargets);

    await this.sendSubmitToCdcEmailNotification({
      to: receiverEmails,
      plenaryId,
      rgcDecisionId: decision.id,
      plenaryName,
      ministryName,
      categoryName,
      status: hydratedDecision.status,
      decision: hydratedDecision.decision,
      submittedBy: actorUser.name,
    });

    const submittedItems = await this.hydrateDecisions([submittedDecision]);

    return {
      success: true,
      message: 'RGC Decision submitted to CDC G-PSF successfully.',
      data: submittedItems[0] ?? hydratedDecision,
      notifications: {
        receiverStakeholderIds: receiverTargets.stakeholderIds,
        receiverUserIds: receiverTargets.userIds,
        created,
        updated,
      },
    };
  }

  async remove(
    decisionId: number,
    requestedUserId: number,
  ): Promise<{ success: boolean; message: string }> {
    const decision = await this.getActiveDecision(decisionId);

    await this.ensureDecisionEditableByCurrentUser(decision, requestedUserId);

    await this.prisma.plenaryRgcDecision.update({
      where: {
        id: decisionId,
      },
      data: {
        deletedAt: new Date(),
      },
    });

    return {
      success: true,
      message: 'RGC Decision deleted successfully.',
    };
  }

  async getScorecard(plenaryId?: number, currentUserId?: number) {
    if (plenaryId !== undefined) {
      await this.getActivePlenary(plenaryId);
      await this.ensurePlenaryVisibleToCurrentUser(plenaryId, currentUserId);
    }

    const where: Prisma.PlenaryRgcDecisionWhereInput = {
      deletedAt: null,
    };

    if (plenaryId !== undefined) {
      where.plenaryId = plenaryId;
    }

    const ministryIds = await this.getCurrentUserMinistryIds(currentUserId);

    if (ministryIds.length > 0) {
      where.stakeholderId = {
        in: ministryIds,
      };
    }

    const decisions = await this.prisma.plenaryRgcDecision.findMany({
      where,
      select: {
        status: true,
        categoryId: true,
        stakeholderId: true,
      },
    });

    const categoryIds = [...new Set(decisions.map((item) => item.categoryId))];
    const stakeholderIds = [
      ...new Set(decisions.map((item) => item.stakeholderId)),
    ];

    const [categories, ministries] = await Promise.all([
      this.getCategoriesByIds(categoryIds),
      this.getStakeholdersByIds(stakeholderIds),
    ]);

    const categoryMap = new Map(categories.map((item) => [item.id, item]));
    const ministryMap = new Map(ministries.map((item) => [item.id, item]));

    const categoryCount = new Map<number, number>();
    const ministryCount = new Map<number, number>();

    for (const decision of decisions) {
      categoryCount.set(
        decision.categoryId,
        (categoryCount.get(decision.categoryId) ?? 0) + 1,
      );

      ministryCount.set(
        decision.stakeholderId,
        (ministryCount.get(decision.stakeholderId) ?? 0) + 1,
      );
    }

    return {
      total: decisions.length,
      byStatus: [
        {
          status: 'Not Addressed',
          count: decisions.filter((item) => item.status === 'NOT_ADDRESSED')
            .length,
        },
        {
          status: 'In Progress',
          count: decisions.filter((item) => item.status === 'IN_PROGRESS')
            .length,
        },
        {
          status: 'Solved',
          count: decisions.filter((item) => item.status === 'SOLVED').length,
        },
      ],
      byCategory: [...categoryCount.entries()]
        .map(([categoryId, count]) => ({
          categoryId,
          category: categoryMap.get(categoryId)?.name ?? 'Unknown Category',
          count,
        }))
        .sort((a, b) => b.count - a.count),
      byMinistry: [...ministryCount.entries()]
        .map(([stakeholderId, count]) => ({
          stakeholderId,
          stakeholder:
            ministryMap.get(stakeholderId)?.name ?? 'Unknown Ministry',
          count,
        }))
        .sort((a, b) => b.count - a.count),
    };
  }

  async exportCsv(
    plenaryId: number,
    currentUserId?: number,
  ): Promise<{ fileName: string; content: string }> {
    const plenary = await this.getActivePlenary(plenaryId);

    await this.ensurePlenaryVisibleToCurrentUser(plenaryId, currentUserId);

    const where: Prisma.PlenaryRgcDecisionWhereInput = {
      plenaryId,
      deletedAt: null,
    };

    const ministryIds = await this.getCurrentUserMinistryIds(currentUserId);

    if (ministryIds.length > 0) {
      where.stakeholderId = {
        in: ministryIds,
      };
    }

    const decisions = await this.prisma.plenaryRgcDecision.findMany({
      where,
      orderBy: {
        id: 'asc',
      },
    });

    const items = await this.hydrateDecisions(decisions);

    const headers: CsvValue[] = [
      'No',
      'RGC Decision',
      'Meeting Date',
      'Category',
      'Indicator',
      'Status',
      'Focal Person',
      'Ministry',
      'Verification Source',
      'Verification Link',
      'Created By',
      'Created At',
      'Updated At',
    ];

    const rows: CsvValue[][] = items.map((item, index) => [
      index + 1,
      item.decision,
      this.toDateOnly(item.meetingDate),
      item.category,
      item.indicatorName,
      item.status,
      item.focalPerson,
      item.stakeholder.name,
      item.verificationSource,
      item.verificationLink,
      item.createdBy.name,
      item.createdAt,
      item.updatedAt,
    ]);

    const csv = [headers, ...rows]
      .map((row) => row.map((value) => this.escapeCsv(value)).join(','))
      .join('\r\n');

    return {
      fileName: `${this.safeFileName(plenary.name)}-rgc-decisions.csv`,
      content: `\uFEFF${csv}`,
    };
  }

  private async getActivePlenary(plenaryId: number) {
    const plenary = await this.prisma.plenary.findFirst({
      where: {
        id: plenaryId,
        deletedAt: null,
      },
    });

    if (!plenary) {
      throw new NotFoundException('Plenary not found.');
    }

    return plenary;
  }

  private async getActiveDecision(
    decisionId: number,
  ): Promise<PlenaryRgcDecision> {
    const decision = await this.prisma.plenaryRgcDecision.findFirst({
      where: {
        id: decisionId,
        deletedAt: null,
      },
    });

    if (!decision) {
      throw new NotFoundException('RGC Decision not found.');
    }

    return decision;
  }

  private async getActiveUser(userId: number): Promise<UserItem> {
    const user = await this.prisma.user.findFirst({
      where: {
        id: userId,
        deletedAt: null,
        isActive: true,
      },
      select: {
        id: true,
        name: true,
        email: true,
      },
    });

    if (!user) {
      throw new BadRequestException(
        'Authenticated user does not exist or is inactive.',
      );
    }

    return {
      id: user.id,
      name: user.name?.trim() || user.email,
      email: user.email,
    };
  }

  private async getAttachedMinistryIds(plenaryId: number): Promise<number[]> {
    const rows = await this.prisma.plenaryMinistry.findMany({
      where: {
        plenaryId,
      },
      select: {
        stakeholderId: true,
      },
    });

    return rows.map((item) => item.stakeholderId);
  }

  private async resolveStakeholderForActor(
    plenaryId: number | null,
    requestedStakeholderId: number | undefined,
    actorMinistryIds: number[],
  ): Promise<number> {
    if (!plenaryId) {
      throw new BadRequestException('plenaryId is required.');
    }

    const attachedMinistryIds = await this.getAttachedMinistryIds(plenaryId);

    if (attachedMinistryIds.length === 0) {
      throw new BadRequestException(
        'This Plenary does not have any attached Ministry.',
      );
    }

    const availableMinistryIds = attachedMinistryIds.filter((id) =>
      actorMinistryIds.includes(id),
    );

    if (availableMinistryIds.length === 0) {
      throw new ForbiddenException(
        'This Plenary was not sent to the logged-in user Ministry.',
      );
    }

    const activeMinistries =
      await this.getActiveMinistriesByIds(availableMinistryIds);

    const activeMinistryIds = activeMinistries.map((item) => item.id);

    if (activeMinistryIds.length === 0) {
      throw new ForbiddenException(
        'The Ministry attached to this Plenary is inactive or deleted.',
      );
    }

    if (
      requestedStakeholderId !== undefined &&
      activeMinistryIds.includes(requestedStakeholderId)
    ) {
      return requestedStakeholderId;
    }

    if (activeMinistryIds.length === 1) {
      return activeMinistryIds[0];
    }

    throw new BadRequestException(
      'Please select one Ministry attached to this Plenary.',
    );
  }

  private async resolveStakeholderForCdc(
    requestedStakeholderId: number | undefined,
  ): Promise<number> {
    if (!requestedStakeholderId) {
      throw new BadRequestException(
        'stakeholderId is required for CDC G-PSF Create RGC.',
      );
    }

    const activeMinistries = await this.getActiveMinistriesByIds([
      requestedStakeholderId,
    ]);

    if (activeMinistries.length === 0) {
      throw new BadRequestException(
        'Selected Ministry does not exist or is inactive.',
      );
    }

    return requestedStakeholderId;
  }

  private async resolveIndicatorId(
    indicatorId?: number,
  ): Promise<number | null> {
    if (indicatorId === undefined || indicatorId === null) {
      return null;
    }

    const indicator = await this.prisma.indicator.findFirst({
      where: {
        id: indicatorId,
        deletedAt: null,
      },
      select: {
        id: true,
      },
    });

    if (!indicator) {
      throw new BadRequestException(
        'Selected Indicator does not exist or has been deleted.',
      );
    }

    return indicator.id;
  }

  private async assertCdcGpsfUser(userId: number): Promise<void> {
    const user = await this.prisma.user.findFirst({
      where: {
        id: userId,
        deletedAt: null,
        isActive: true,
      },
      select: {
        id: true,
        name: true,
        email: true,
        position: true,
      },
    });

    if (!user) {
      throw new ForbiddenException(
        'Only CDC or CDC G-PSF user can create RGC Decision from CDC screen.',
      );
    }

    const normalizeIdentity = (value?: string | null): string =>
      String(value ?? '')
        .trim()
        .toLowerCase()
        .replace(/[\s_-]+/g, ' ');

    const directIdentityText = [user.name, user.email, user.position]
      .map((value) => normalizeIdentity(value))
      .filter(Boolean)
      .join(' ');

    const isDirectCdcUser =
      directIdentityText.includes('cdc') ||
      directIdentityText.includes('g psf') ||
      directIdentityText.includes('gpsf') ||
      directIdentityText.includes('cefp') ||
      directIdentityText.includes('secretariat');

    if (isDirectCdcUser) {
      return;
    }

    const linkedStakeholders = await this.prisma.stakeholderUser.findMany({
      where: {
        userId: user.id,
        stakeholder: {
          deletedAt: null,
          active: true,
        },
      },
      select: {
        stakeholder: {
          select: {
            name: true,
            stakeholderType: {
              select: {
                name: true,
              },
            },
          },
        },
      },
    });

    const linkedIdentityText = linkedStakeholders
      .flatMap((item) => [
        item.stakeholder.name,
        item.stakeholder.stakeholderType?.name,
      ])
      .map((value) => normalizeIdentity(value))
      .filter(Boolean)
      .join(' ');

    const isLinkedCdcUser =
      linkedIdentityText.includes('cdc') ||
      linkedIdentityText.includes('g psf') ||
      linkedIdentityText.includes('gpsf') ||
      linkedIdentityText.includes('cefp') ||
      linkedIdentityText.includes('secretariat');

    if (isLinkedCdcUser) {
      return;
    }

    throw new ForbiddenException(
      'Only CDC or CDC G-PSF user can create RGC Decision from CDC screen.',
    );
  }

  private async resolveCategoryId(
    categoryId: number | undefined,
    categoryName: string | undefined,
    userId: number,
  ): Promise<number> {
    if (categoryId !== undefined) {
      const category = await this.prisma.categories.findFirst({
        where: {
          id: categoryId,
          deletedAt: null,
        },
        select: {
          id: true,
          name: true,
        },
      });

      if (!category) {
        throw new BadRequestException('Selected Category does not exist.');
      }

      return category.id;
    }

    const cleanCategory = categoryName?.trim();

    if (!cleanCategory) {
      throw new BadRequestException('categoryId or category is required.');
    }

    const existing = await this.prisma.categories.findFirst({
      where: {
        name: {
          equals: cleanCategory,
          mode: 'insensitive',
        },
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
      },
    });

    if (existing) {
      return existing.id;
    }

    const created = await this.prisma.categories.create({
      data: {
        name: cleanCategory,
        userId,
      },
      select: {
        id: true,
        name: true,
      },
    });

    return created.id;
  }

  private async ensurePlenaryVisibleToCurrentUser(
    plenaryId: number,
    currentUserId?: number,
  ) {
    const userMinistryIds = await this.getCurrentUserMinistryIds(currentUserId);

    if (userMinistryIds.length === 0) {
      return;
    }

    const relation = await this.prisma.plenaryMinistry.findFirst({
      where: {
        plenaryId,
        stakeholderId: {
          in: userMinistryIds,
        },
      },
      select: {
        plenaryId: true,
      },
    });

    if (!relation) {
      throw new NotFoundException('Plenary not found.');
    }
  }

  /**
   * A Ministry can view a decision when:
   * 1. the decision belongs directly to that Ministry; or
   * 2. the Ministry is attached to the same Plenary through PlenaryMinistry.
   *
   * Users that are not Ministry users, such as CDC G-PSF users, keep the
   * existing unrestricted visibility behavior.
   */
  private async ensureDecisionVisibleToCurrentUser(
    decision: PlenaryRgcDecision,
    currentUserId?: number,
  ) {
    const userMinistryIds = await this.getCurrentUserMinistryIds(currentUserId);

    if (userMinistryIds.length === 0) {
      return;
    }

    const hasAccess = await this.isDecisionRelatedToMinistries(
      decision,
      userMinistryIds,
    );

    if (!hasAccess) {
      throw new NotFoundException('RGC Decision not found.');
    }
  }

  /**
   * Allows any Ministry attached to the decision's Plenary to edit it.
   * This method is used only by update(), so Submit/Delete keep their
   * original owner-Ministry protection.
   */
  private async ensureDecisionEditableByRelatedMinistry(
    decision: PlenaryRgcDecision,
    currentUserId: number,
  ) {
    const userMinistryIds = await this.getCurrentUserMinistryIds(currentUserId);

    if (userMinistryIds.length === 0) {
      throw new ForbiddenException(
        'Only a related Ministry user can edit this RGC Decision.',
      );
    }

    const hasAccess = await this.isDecisionRelatedToMinistries(
      decision,
      userMinistryIds,
    );

    if (!hasAccess) {
      throw new ForbiddenException(
        'Your Ministry is not related to this RGC Decision Plenary.',
      );
    }
  }

  /**
   * Owner-only permission retained for Submit to CDC and Delete.
   */
  private async ensureDecisionEditableByCurrentUser(
    decision: PlenaryRgcDecision,
    currentUserId: number,
  ) {
    const userMinistryIds = await this.getCurrentUserMinistryIds(currentUserId);

    if (userMinistryIds.length === 0) {
      throw new ForbiddenException(
        'Only a Ministry user can modify RGC Decision records.',
      );
    }

    if (!userMinistryIds.includes(decision.stakeholderId)) {
      throw new ForbiddenException('You cannot modify this RGC Decision.');
    }
  }

  /**
   * Returns true when the decision belongs to one of the Ministries or when
   * one of the Ministries is attached to the same Plenary.
   */
  private async isDecisionRelatedToMinistries(
    decision: PlenaryRgcDecision,
    ministryIds: number[],
  ): Promise<boolean> {
    if (ministryIds.includes(decision.stakeholderId)) {
      return true;
    }

    if (!decision.plenaryId) {
      return false;
    }

    const relation = await this.prisma.plenaryMinistry.findFirst({
      where: {
        plenaryId: decision.plenaryId,
        stakeholderId: {
          in: ministryIds,
        },
      },
      select: {
        plenaryId: true,
      },
    });

    return Boolean(relation);
  }

  private async getCurrentUserMinistryIds(
    currentUserId?: number,
  ): Promise<number[]> {
    if (!currentUserId) {
      return [];
    }

    const rows = await this.prisma.stakeholderUser.findMany({
      where: {
        userId: currentUserId,
        stakeholder: {
          deletedAt: null,
          active: true,
          stakeholderType: {
            deletedAt: null,
            name: {
              equals: MINISTRY_STAKEHOLDER_TYPE,
              mode: 'insensitive',
            },
          },
        },
      },
      select: {
        stakeholderId: true,
      },
    });

    return rows.map((item) => item.stakeholderId);
  }

  private async hydrateDecisions(decisions: PlenaryRgcDecision[]) {
    if (decisions.length === 0) {
      return [];
    }

    const decisionIds = decisions.map((d) => d.id);

    const stakeholderIds = [
      ...new Set(decisions.map((item) => item.stakeholderId)),
    ];

    const categoryIds = [...new Set(decisions.map((item) => item.categoryId))];

    const indicatorIds = [
      ...new Set(
        decisions
          .map((item) => item.indicatorId)
          .filter((id): id is number => typeof id === 'number'),
      ),
    ];

    const userIds = [...new Set(decisions.map((item) => item.userId))];

    const plenaryIds = [
      ...new Set(
        decisions
          .map((item) => item.plenaryId)
          .filter((id): id is number => typeof id === 'number'),
      ),
    ];

    const [
      stakeholders,
      categories,
      indicators,
      users,
      plenaries,
      decisionIssues,
    ] = await Promise.all([
      this.getStakeholdersByIds(stakeholderIds),
      this.getCategoriesByIds(categoryIds),
      this.getIndicatorsByIds(indicatorIds),
      this.getUsersByIds(userIds),
      this.getPlenariesByIds(plenaryIds),
      this.prisma.decisionIssue.findMany({
        where: {
          plenaryRgcDecisionId: { in: decisionIds },
        },
        include: {
          issue: {
            include: {
              issueStatus: {
                select: { id: true, code: true, name: true },
              },
              category: {
                select: { id: true, name: true },
              },
              meetingRequest: {
                select: {
                  id: true,
                  title: true,
                  meetings: {
                    select: {
                      id: true,
                      meetingDate: true,
                      startTime: true,
                      endTime: true,
                    },
                  },
                },
              },
              stakeholder: {
                select: {
                  id: true,
                  name: true,
                  logo: true,
                  stakeholderType: { select: { id: true, name: true } },
                },
              },
              user: {
                select: {
                  id: true,
                  email: true,
                  name: true,
                  position: true,
                  avatar: true,
                },
              },
              governmentAgencies: {
                orderBy: { agencyOrder: 'asc' },
                select: {
                  agencyOrder: true,
                  stakeholder: {
                    select: {
                      id: true,
                      name: true,
                      logo: true,
                      stakeholderType: { select: { id: true, name: true } },
                    },
                  },
                },
              },
            },
          },
        },
      }),
    ]);

    const issuesMap = new Map<number, any[]>();
    for (const di of decisionIssues) {
      const existing = issuesMap.get(di.plenaryRgcDecisionId) ?? [];
      if (di.issue) {
        existing.push(di.issue);
      }
      issuesMap.set(di.plenaryRgcDecisionId, existing);
    }

    const relations: DecisionRelations = {
      stakeholders: new Map(stakeholders.map((item) => [item.id, item])),
      categories: new Map(categories.map((item) => [item.id, item])),
      indicators: new Map(indicators.map((item) => [item.id, item])),
      users: new Map(users.map((item) => [item.id, item])),
      plenaries: new Map(plenaries.map((item) => [item.id, item])),
      issuesMap,
    };

    return decisions.map((decision) => this.mapDecision(decision, relations));
  }

  private mapDecision(
    decision: PlenaryRgcDecision,
    relations: DecisionRelations,
  ) {
    const stakeholder = relations.stakeholders.get(decision.stakeholderId) ?? {
      id: decision.stakeholderId,
      name: 'Unknown Ministry',
      logo: null,
    };

    const category = relations.categories.get(decision.categoryId) ?? {
      id: decision.categoryId,
      name: 'Unknown Category',
    };

    const indicator =
      decision.indicatorId === null
        ? null
        : (relations.indicators.get(decision.indicatorId) ?? {
            id: decision.indicatorId,
            name: 'Unknown Indicator',
            description: null,
          });

    const createdBy = relations.users.get(decision.userId) ?? {
      id: decision.userId,
      name: 'Unknown User',
      email: '',
    };

    const plenary =
      decision.plenaryId === null
        ? null
        : (relations.plenaries.get(decision.plenaryId) ?? {
            id: decision.plenaryId,
            name: 'Unknown Plenary',
          });

    const issues = relations.issuesMap.get(decision.id) ?? [];

    return {
      id: decision.id,
      plenaryId: decision.plenaryId,
      plenary,
      stakeholderId: stakeholder.id,
      stakeholder,
      categoryId: category.id,
      category: category.name,
      categoryInfo: category,
      indicatorId: indicator?.id ?? null,
      indicator,
      indicatorName: indicator?.name ?? '',
      indicatorDescription: indicator?.description ?? '',
      meetingDate: decision.meetingDate,
      status: this.mapDecisionStatus(decision.status),
      statusCode: decision.status,
      focalPerson: decision.focalPerson,
      decision: decision.decision,
      verificationSource: decision.verificationSource ?? '',
      verificationLink: decision.verificationLink ?? '',
      submittedToCdcAt: decision.submittedToCdcAt,
      saveAsDraft: decision.submittedToCdcAt === null,
      isDraft: decision.submittedToCdcAt === null,
      createdBy,
      issues,
      createdAt: decision.createdAt,
      updatedAt: decision.updatedAt,
    };
  }

  private async getActiveMinistriesByIds(
    ids: number[],
  ): Promise<StakeholderItem[]> {
    if (ids.length === 0) {
      return [];
    }

    return this.prisma.stakeholder.findMany({
      where: {
        id: {
          in: ids,
        },
        deletedAt: null,
        active: true,
        stakeholderType: {
          deletedAt: null,
          name: {
            equals: MINISTRY_STAKEHOLDER_TYPE,
            mode: 'insensitive',
          },
        },
      },
      select: {
        id: true,
        name: true,
        logo: true,
      },
      orderBy: {
        name: 'asc',
      },
    });
  }

  private async getStakeholdersByIds(
    ids: number[],
  ): Promise<StakeholderItem[]> {
    if (ids.length === 0) {
      return [];
    }

    return this.prisma.stakeholder.findMany({
      where: {
        id: {
          in: ids,
        },
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
        logo: true,
      },
    });
  }

  private async getCategoriesByIds(ids: number[]): Promise<CategoryItem[]> {
    if (ids.length === 0) {
      return [];
    }

    return this.prisma.categories.findMany({
      where: {
        id: {
          in: ids,
        },
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
      },
    });
  }

  private async getIndicatorsByIds(ids: number[]): Promise<IndicatorItem[]> {
    if (ids.length === 0) {
      return [];
    }

    return this.prisma.indicator.findMany({
      where: {
        id: {
          in: ids,
        },
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
        description: true,
      },
    });
  }

  private async getUsersByIds(ids: number[]): Promise<UserItem[]> {
    if (ids.length === 0) {
      return [];
    }

    const users = await this.prisma.user.findMany({
      where: {
        id: {
          in: ids,
        },
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
        email: true,
      },
    });

    return users.map((user) => ({
      id: user.id,
      name: user.name?.trim() || user.email,
      email: user.email,
    }));
  }

  private async getPlenariesByIds(ids: number[]): Promise<PlenaryItem[]> {
    if (ids.length === 0) {
      return [];
    }

    return this.prisma.plenary.findMany({
      where: {
        id: {
          in: ids,
        },
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
      },
    });
  }

  private async getCdcReceiverTargets(): Promise<{
    stakeholderIds: number[];
    userIds: number[];
  }> {
    const cdcStakeholders = await this.prisma.stakeholder.findMany({
      where: {
        deletedAt: null,
        active: true,
        OR: [
          { name: { contains: 'CDC', mode: 'insensitive' } },
          { name: { contains: 'G-PSF', mode: 'insensitive' } },
          { name: { contains: 'GPSF', mode: 'insensitive' } },
          { name: { contains: 'Secretariat', mode: 'insensitive' } },
          {
            stakeholderType: {
              name: { contains: 'CDC', mode: 'insensitive' },
            },
          },
          {
            stakeholderType: {
              name: { contains: 'G-PSF', mode: 'insensitive' },
            },
          },
          {
            stakeholderType: {
              name: { contains: 'GPSF', mode: 'insensitive' },
            },
          },
        ],
      },
      select: {
        id: true,
        users: {
          select: {
            userId: true,
          },
        },
      },
      orderBy: {
        id: 'asc',
      },
    });

    const stakeholderIds = Array.from(
      new Set(
        cdcStakeholders
          .map((stakeholder) => Number(stakeholder.id))
          .filter((id) => Number.isInteger(id) && id > 0),
      ),
    );

    const userIdsFromStakeholders = Array.from(
      new Set(
        cdcStakeholders
          .flatMap((stakeholder) =>
            stakeholder.users.map((link) => Number(link.userId)),
          )
          .filter((id) => Number.isInteger(id) && id > 0),
      ),
    );

    const directUsers = await this.prisma.user.findMany({
      where: {
        deletedAt: null,
        isActive: true,
        OR: [
          { position: { contains: 'CDC', mode: 'insensitive' } },
          { position: { contains: 'G-PSF', mode: 'insensitive' } },
          { position: { contains: 'GPSF', mode: 'insensitive' } },
          { position: { contains: 'Secretariat', mode: 'insensitive' } },
          { name: { contains: 'CDC', mode: 'insensitive' } },
          { email: { contains: 'cdc', mode: 'insensitive' } },
        ],
      },
      select: {
        id: true,
      },
      orderBy: {
        id: 'asc',
      },
    });

    const directUserIds = directUsers
      .map((user) => Number(user.id))
      .filter((id) => Number.isInteger(id) && id > 0);

    const userIds = Array.from(
      new Set(
        directUserIds.filter(
          (userId) => !userIdsFromStakeholders.includes(userId),
        ),
      ),
    );

    return {
      stakeholderIds,
      userIds,
    };
  }

  private async createOrUpdateSubmitNotification(params: {
    decisionId: number;
    title: string;
    message: string;
    senderUserId: number;
    receiverStakeholderId?: number;
    receiverUserId?: number;
    data: Prisma.InputJsonObject;
  }): Promise<'created' | 'updated'> {
    const existingNotifications = await this.prisma.systemNotification.findMany(
      {
        where: {
          deletedAt: null,
          type: 'RGC_DECISION_SUBMITTED',
          ...(params.receiverStakeholderId
            ? {
                receiverStakeholderId: params.receiverStakeholderId,
              }
            : {}),
          ...(params.receiverUserId
            ? {
                receiverUserId: params.receiverUserId,
              }
            : {}),
        },
        select: {
          id: true,
          data: true,
        },
      },
    );

    const existingNotification = existingNotifications.find(
      (notification) =>
        this.getJsonNumber(notification.data, 'rgcDecisionId') ===
        params.decisionId,
    );

    if (existingNotification) {
      await this.prisma.systemNotification.update({
        where: {
          id: existingNotification.id,
        },
        data: {
          title: params.title,
          message: params.message,
          senderUserId: params.senderUserId,
          isRead: false,
          readAt: null,
          data: params.data,
        },
      });

      return 'updated';
    }

    await this.prisma.systemNotification.create({
      data: {
        title: params.title,
        message: params.message,
        type: 'RGC_DECISION_SUBMITTED',
        senderUserId: params.senderUserId,
        ...(params.receiverStakeholderId
          ? {
              receiverStakeholderId: params.receiverStakeholderId,
            }
          : {}),
        ...(params.receiverUserId
          ? {
              receiverUserId: params.receiverUserId,
            }
          : {}),
        data: params.data,
      },
    });

    return 'created';
  }

  private async getCdcReceiverEmails(targets: {
    stakeholderIds: number[];
    userIds: number[];
  }): Promise<string[]> {
    const stakeholderLinks = targets.stakeholderIds.length
      ? await this.prisma.stakeholderUser.findMany({
          where: {
            stakeholderId: {
              in: targets.stakeholderIds,
            },
          },
          select: {
            userId: true,
          },
        })
      : [];

    const userIds = Array.from(
      new Set(
        [...targets.userIds, ...stakeholderLinks.map((link) => link.userId)]
          .map((id) => Number(id))
          .filter((id) => Number.isInteger(id) && id > 0),
      ),
    );

    if (userIds.length === 0) {
      return [];
    }

    const users = await this.prisma.user.findMany({
      where: {
        id: {
          in: userIds,
        },
        deletedAt: null,
        isActive: true,
      },
      select: {
        email: true,
      },
    });

    return Array.from(
      new Set(
        users
          .map((user) => user.email?.trim())
          .filter((email): email is string => this.isValidEmail(email)),
      ),
    );
  }

  private async sendSubmitToCdcEmailNotification(params: {
    to: string[];
    plenaryId: number;
    rgcDecisionId: number;
    plenaryName: string;
    ministryName: string;
    categoryName?: string | null;
    status?: string | null;
    decision?: string | null;
    submittedBy?: string | null;
  }): Promise<void> {
    if (params.to.length === 0) {
      console.warn(
        'CDC G-PSF email receiver was not found in DB. Skip email notification.',
      );

      return;
    }

    try {
      await this.resendMailPlenaryService.sendRgcDecisionSubmittedToCdc({
        to: params.to,
        plenaryId: params.plenaryId,
        rgcDecisionId: params.rgcDecisionId,
        plenaryName: params.plenaryName,
        ministryName: params.ministryName,
        categoryName: params.categoryName,
        status: params.status,
        decision: params.decision,
        submittedBy: params.submittedBy,
      });
    } catch (error) {
      console.error('Submit to CDC email notification failed:', error);
    }
  }

  private isValidEmail(value?: string | null): value is string {
    if (!value) {
      return false;
    }

    return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value.trim());
  }

  private getJsonNumber(data: unknown, key: string): number | null {
    if (!data || typeof data !== 'object' || Array.isArray(data)) {
      return null;
    }

    const value = (data as Record<string, unknown>)[key];
    const parsedValue = Number(value);

    return Number.isInteger(parsedValue) && parsedValue > 0
      ? parsedValue
      : null;
  }

  private toDecisionStatus(value?: string): RgcDecisionStatus | undefined {
    if (!value) {
      return undefined;
    }

    const normalized = value.trim().toLowerCase();

    if (
      normalized === 'not addressed' ||
      normalized === 'not_addressed' ||
      normalized === 'not-addressed' ||
      normalized === 'notaddressed'
    ) {
      return 'NOT_ADDRESSED';
    }

    if (
      normalized === 'in progress' ||
      normalized === 'in_progress' ||
      normalized === 'in-progress' ||
      normalized === 'inprogress'
    ) {
      return 'IN_PROGRESS';
    }

    if (normalized === 'solved') {
      return 'SOLVED';
    }

    throw new BadRequestException('Invalid RGC Decision status.');
  }

  private mapDecisionStatus(status: RgcDecisionStatus): string {
    if (status === 'NOT_ADDRESSED') {
      return 'Not Addressed';
    }

    if (status === 'IN_PROGRESS') {
      return 'In Progress';
    }

    if (status === 'SOLVED') {
      return 'Solved';
    }

    return 'Not Addressed';
  }

  private toDateOnly(date: Date): string {
    return date.toISOString().slice(0, 10);
  }

  private safeFileName(value: string): string {
    const fileName = value
      .trim()
      .replace(/[\\/:*?"<>|]+/g, '-')
      .replace(/\s+/g, '-')
      .replace(/-+/g, '-')
      .replace(/^-|-$/g, '');

    return fileName || 'plenary';
  }

  private escapeCsv(value: CsvValue): string {
    if (value === null || value === undefined) {
      return '';
    }

    let text = '';

    if (value instanceof Date) {
      text = value.toISOString();
    } else if (typeof value === 'string') {
      text = value;
    } else if (typeof value === 'number' || typeof value === 'boolean') {
      text = `${value}`;
    }

    return `"${text.replace(/"/g, '""')}"`;
  }
}