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

import {
  Prisma,
  ProgressReportDeadlineStatus,
  ProgressReportMeetingStatus,
  ProgressReportMinistryStatus,
  ProgressReportStatus,
  RgcDecisionStatus,
} from '@/generated/prisma/client';
import { PrismaService } from '@/prisma/prisma.service';

const latestMeetingOrderBy = [
  { createdAt: 'desc' },
  { id: 'desc' },
] satisfies Prisma.ProgressReportMeetingOrderByWithRelationInput[];

const OPEN_ISSUE_STATUS_CODES = ['IN_PROGRESS', 'NOT_ADDRESSED'];
const PROGRESS_UPDATE_STATUS_CODES = ['IN_PROGRESS', 'NOT_ADDRESSED', 'SOLVED'];
const OPEN_RGC_DECISION_STATUSES = [
  RgcDecisionStatus.IN_PROGRESS,
  RgcDecisionStatus.NOT_ADDRESSED,
];
const PRIMARY_AGENCY_ORDER = 1;
const PSWG_VISIBLE_STATUSES = [
  ProgressReportMinistryStatus.SHARED_WITH_PSWG,
  ProgressReportMinistryStatus.PSWG_REVIEWED,
  ProgressReportMinistryStatus.SUBMITTED,
  ProgressReportMinistryStatus.CDC_UNDER_REVIEW,
  ProgressReportMinistryStatus.COMPLETED,
];

// These states prove that a report reached CDC even when an older migrated
// record does not have submission audit fields yet.
const CDC_SUBMITTED_STATUSES = [
  ProgressReportMinistryStatus.SUBMITTED,
  ProgressReportMinistryStatus.CDC_UNDER_REVIEW,
  ProgressReportMinistryStatus.COMPLETED,
];

const assignmentRelations = {
  ministry: {
    select: {
      id: true,
      name: true,
      description: true,
      logo: true,
      relatedStakeholderId: true,
    },
  },
  user: {
    select: {
      id: true,
      name: true,
      email: true,
      position: true,
    },
  },
  reviewedBy: {
    select: {
      id: true,
      name: true,
      position: true,
    },
  },
  progressReport: {
    include: {
      deadlines: {
        where: {
          deletedAt: null,
          status: ProgressReportDeadlineStatus.SENT,
        },
        orderBy: { deadline: 'asc' },
      },
      meetings: {
        where: {
          deletedAt: null,
          status: ProgressReportMeetingStatus.SENT,
        },
        orderBy: latestMeetingOrderBy,
      },
    },
  },
} satisfies Prisma.ProgressReportMinistryInclude;

export type ProgressReportMinistryWithRelations =
  Prisma.ProgressReportMinistryGetPayload<{
    include: typeof assignmentRelations;
  }>;

// The CDC table only needs basic Ministry information. Keep this query small;
// the full assignment relations are loaded by the separate detail endpoint.
const assignmentListSelect = {
  id: true,
  ministryId: true,
  attachment: true,
  ministry: {
    select: {
      id: true,
      name: true,
      description: true,
      logo: true,
    },
  },
} satisfies Prisma.ProgressReportMinistrySelect;

export type ProgressReportMinistryListItem =
  Prisma.ProgressReportMinistryGetPayload<{
    select: typeof assignmentListSelect;
  }>;

const issueCommentSelect = {
  id: true,
  progressReportUpdateIssueId: true,
  progressReportUpdatePlenaryDecisionId: true,
  commentTypeId: true,
  comment: true,
  userId: true,
  createdAt: true,
  updatedAt: true,
  commentType: {
    select: {
      id: true,
      name: true,
    },
  },
  user: {
    select: {
      id: true,
      name: true,
    },
  },
} satisfies Prisma.ProgressReportIssueCommentSelect;

export type ProgressReportIssueCommentWithRelations =
  Prisma.ProgressReportIssueCommentGetPayload<{
    select: typeof issueCommentSelect;
  }>;

const openIssueRelations = {
  issueStatus: {
    select: { id: true, code: true, name: true },
  },
  category: {
    select: { id: true, name: true },
  },
  stakeholder: {
    select: { id: true, name: true, logo: true },
  },
  governmentAgencies: {
    orderBy: { agencyOrder: 'asc' },
    select: {
      agencyOrder: true,
      stakeholder: {
        select: {
          id: true,
          name: true,
          description: true,
          logo: true,
        },
      },
    },
  },
  progressReportUpdates: {
    where: { deletedAt: null },
    take: 1,
    orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
    include: {
      issueStatus: {
        select: { id: true, code: true, name: true },
      },
      user: {
        select: { id: true, name: true },
      },
      issueResolve: {
        select: {
          id: true,
          issueId: true,
          rgcDecision: true,
          nextStep: true,
        },
      },
      progressReportUpdateIssues: {
        select: {
          id: true,
          ministryId: true,
          issueId: true,
          progressReportIssueComments: {
            where: { deletedAt: null },
            orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
            take: 1,
            select: issueCommentSelect,
          },
        },
      },
      progressReportUpdatePlenaryDecisions: {
        select: {
          id: true,
          progressReportId: true,
          ministryId: true,
          plenaryDecisionId: true,
          progressReportIssueComments: {
            where: { deletedAt: null },
            orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
            take: 1,
            select: issueCommentSelect,
          },
        },
      },
    },
  },
} satisfies Prisma.IssuesInclude;

export type MinistryOpenIssue = Prisma.IssuesGetPayload<{
  include: typeof openIssueRelations;
}>;

const progressReportUpdateRelations = {
  issueStatus: {
    select: { id: true, code: true, name: true },
  },
  user: {
    select: { id: true, name: true },
  },
  issueResolve: {
    select: {
      id: true,
      issueId: true,
      rgcDecision: true,
      nextStep: true,
    },
  },
  progressReportUpdateIssues: {
    select: {
      id: true,
      ministryId: true,
      issueId: true,
      progressReportIssueComments: {
        where: { deletedAt: null },
        orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
        take: 1,
        select: issueCommentSelect,
      },
    },
  },
  progressReportUpdatePlenaryDecisions: {
    select: {
      id: true,
      progressReportId: true,
      ministryId: true,
      plenaryDecisionId: true,
      progressReportIssueComments: {
        where: { deletedAt: null },
        orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
        take: 1,
        select: issueCommentSelect,
      },
    },
  },
} satisfies Prisma.ProgressReportUpdateInclude;

export type ProgressReportUpdateWithRelations =
  Prisma.ProgressReportUpdateGetPayload<{
    include: typeof progressReportUpdateRelations;
  }>;

const progressReportRgcDecisionUpdateRelations = {
  progressReportUpdate: {
    include: progressReportUpdateRelations,
  },
  progressReportIssueComments: {
    where: { deletedAt: null },
    orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
    take: 1,
    select: issueCommentSelect,
  },
} satisfies Prisma.ProgressReportUpdatePlenaryDecisionInclude;

export type ProgressReportRgcDecisionUpdate =
  Prisma.ProgressReportUpdatePlenaryDecisionGetPayload<{
    include: typeof progressReportRgcDecisionUpdateRelations;
  }>;

const pswgRgcDecisionRelations = {
  category: {
    select: { id: true, name: true },
  },
  indicator: {
    select: { id: true, name: true, description: true },
  },
  decisionIssues: {
    where: { issue: { deletedAt: null } },
    include: {
      issue: {
        select: {
          id: true,
          title: true,
          stakeholder: {
            select: { id: true, name: true, logo: true },
          },
        },
      },
    },
  },
} satisfies Prisma.PlenaryRgcDecisionInclude;

export type PswgRgcDecision = Prisma.PlenaryRgcDecisionGetPayload<{
  include: typeof pswgRgcDecisionRelations;
}>;

export type ProgressReportRgcDecision = PswgRgcDecision;

@Injectable()
export class MinistryProgressReportsRepository {
  constructor(private readonly prisma: PrismaService) {}

  findActiveReport(progressReportId: number) {
    return this.prisma.progressReport.findFirst({
      where: { id: progressReportId, deletedAt: null },
      select: { id: true, title: true, status: true, userId: true },
    });
  }

  findManyAndCount(options: {
    progressReportId: number;
    skip: number;
    take: number;
    status?: Prisma.EnumProgressReportMinistryStatusFilter;
    hasBeenSubmitted?: boolean;
    search?: string;
  }) {
    const where: Prisma.ProgressReportMinistryWhereInput = {
      progressReportId: options.progressReportId,
      deletedAt: null,
      ...(options.status ? { status: options.status } : {}),
      ...(options.hasBeenSubmitted
        ? { status: { in: CDC_SUBMITTED_STATUSES } }
        : {}),
      ...(options.search
        ? {
            ministry: {
              name: { contains: options.search, mode: 'insensitive' },
            },
          }
        : {}),
    };

    return this.prisma.$transaction([
      this.prisma.progressReportMinistry.findMany({
        where,
        skip: options.skip,
        take: options.take,
        orderBy: [{ ministry: { name: 'asc' } }, { id: 'asc' }],
        select: assignmentListSelect,
      }),
      this.prisma.progressReportMinistry.count({ where }),
    ]);
  }

  findActiveAssignment(progressReportId: number, ministryId: number) {
    return this.prisma.progressReportMinistry.findFirst({
      where: {
        progressReportId,
        ministryId,
        deletedAt: null,
        progressReport: { deletedAt: null },
      },
      include: assignmentRelations,
    });
  }

  countOpenPrimaryIssuesByMinistryIds(ministryIds: number[]) {
    if (ministryIds.length === 0) return Promise.resolve([]);

    return this.prisma.governmentAgencyIssue.groupBy({
      by: ['stakeholderId'],
      where: {
        stakeholderId: { in: ministryIds },
        agencyOrder: PRIMARY_AGENCY_ORDER,
        issue: {
          deletedAt: null,
          issueStatus: {
            code: { in: OPEN_ISSUE_STATUS_CODES },
            deletedAt: null,
          },
        },
      },
      _count: { _all: true },
    });
  }

  findPswgAssignmentsAndCount(options: {
    pswgIds: number[];
    skip: number;
    take: number;
  }) {
    const where: Prisma.ProgressReportMinistryWhereInput = {
      deletedAt: null,
      status: { in: PSWG_VISIBLE_STATUSES },
      ministry: {
        active: true,
        deletedAt: null,
        relatedStakeholderId: { in: options.pswgIds },
        stakeholderType: {
          name: { equals: 'Ministry', mode: 'insensitive' },
          deletedAt: null,
        },
      },
      progressReport: {
        status: ProgressReportStatus.SENT,
        deletedAt: null,
      },
    };

    return this.prisma.$transaction([
      this.prisma.progressReportMinistry.findMany({
        where,
        skip: options.skip,
        take: options.take,
        orderBy: [
          { progressReport: { year: 'desc' } },
          { progressReport: { semester: 'desc' } },
          { ministry: { name: 'asc' } },
          { id: 'desc' },
        ],
        include: assignmentRelations,
      }),
      this.prisma.progressReportMinistry.count({ where }),
    ]);
  }

  findPswgAssignmentById(assignmentId: number, pswgIds: number[]) {
    return this.prisma.progressReportMinistry.findFirst({
      where: {
        id: assignmentId,
        deletedAt: null,
        status: { in: PSWG_VISIBLE_STATUSES },
        ministry: {
          active: true,
          deletedAt: null,
          relatedStakeholderId: { in: pswgIds },
        },
        progressReport: {
          status: ProgressReportStatus.SENT,
          deletedAt: null,
        },
      },
      include: assignmentRelations,
    });
  }

  findOpenPrimaryIssues(
    progressReportId: number,
    ministryId: number,
    workingGroupIds?: number[],
  ): Promise<MinistryOpenIssue[]> {
    return this.prisma.issues.findMany({
      where: {
        deletedAt: null,
        ...(workingGroupIds ? { stakeholderId: { in: workingGroupIds } } : {}),
        issueStatus: {
          code: { in: OPEN_ISSUE_STATUS_CODES },
          deletedAt: null,
        },
        stakeholder: {
          active: true,
          deletedAt: null,
          stakeholderType: {
            name: { equals: 'Private Sector', mode: 'insensitive' },
            deletedAt: null,
          },
        },
        governmentAgencies: {
          some: {
            stakeholderId: ministryId,
            agencyOrder: PRIMARY_AGENCY_ORDER,
            stakeholder: {
              active: true,
              deletedAt: null,
              stakeholderType: {
                name: { equals: 'Ministry', mode: 'insensitive' },
                deletedAt: null,
              },
            },
          },
        },
      },
      orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
      include: {
        ...openIssueRelations,
        progressReportUpdates: {
          ...openIssueRelations.progressReportUpdates,
          where: {
            progressReportId,
            ministryId,
            deletedAt: null,
          },
        },
      },
    });
  }

  findOpenPrimaryIssue(issueId: number, ministryId: number) {
    return this.prisma.issues.findFirst({
      where: {
        id: issueId,
        deletedAt: null,
        issueStatus: {
          code: { in: OPEN_ISSUE_STATUS_CODES },
          deletedAt: null,
        },
        stakeholder: {
          active: true,
          deletedAt: null,
        },
        governmentAgencies: {
          some: {
            stakeholderId: ministryId,
            agencyOrder: PRIMARY_AGENCY_ORDER,
            stakeholder: {
              active: true,
              deletedAt: null,
              stakeholderType: {
                name: { equals: 'Ministry', mode: 'insensitive' },
                deletedAt: null,
              },
            },
          },
        },
      },
      select: { id: true },
    });
  }

  findProgressUpdateStatus(issueStatusId: number) {
    return this.prisma.issueStatuses.findFirst({
      where: {
        id: issueStatusId,
        code: { in: PROGRESS_UPDATE_STATUS_CODES },
        deletedAt: null,
      },
      select: { id: true, code: true, name: true },
    });
  }

  findLatestIssueResolve(issueId: number) {
    return this.prisma.issueResolves.findFirst({
      where: {
        issueId,
        deletedAt: null,
      },
      orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
      select: {
        id: true,
        issueId: true,
      },
    });
  }

  findProgressUpdateStatusByCode(code: RgcDecisionStatus) {
    return this.prisma.issueStatuses.findFirst({
      where: {
        code,
        deletedAt: null,
      },
      select: {
        id: true,
        code: true,
        name: true,
      },
    });
  }

  findProgressReportUpdate(
    progressReportId: number,
    ministryId: number,
    issueId: number,
  ) {
    return this.prisma.progressReportUpdate.findUnique({
      where: {
        progressReportId_ministryId_issueId: {
          progressReportId,
          ministryId,
          issueId,
        },
      },
      include: progressReportUpdateRelations,
    });
  }

  findProgressReportUpdateIssue(
    progressReportId: number,
    ministryId: number,
    issueId: number,
  ) {
    return this.prisma.progressReportUpdateIssue.findFirst({
      where: {
        ministryId,
        issueId,
        progressReportUpdate: {
          progressReportId,
          ministryId,
          issueId,
          deletedAt: null,
        },
      },
      select: {
        id: true,
        progressReportIssueComments: {
          where: { deletedAt: null },
          orderBy: [{ updatedAt: 'desc' }, { id: 'desc' }],
          take: 1,
          select: issueCommentSelect,
        },
      },
    });
  }

  findActiveCommentType(commentTypeId: number) {
    return this.prisma.commentType.findFirst({
      where: {
        id: commentTypeId,
        deletedAt: null,
      },
      select: {
        id: true,
        name: true,
      },
    });
  }

  findActiveIssueComment(
    commentId: number,
    progressReportUpdateIssueId: number,
  ) {
    return this.prisma.progressReportIssueComment.findFirst({
      where: {
        id: commentId,
        progressReportUpdateIssueId,
        deletedAt: null,
      },
      select: issueCommentSelect,
    });
  }

  createIssueComment(input: {
    progressReportUpdateIssueId: number;
    commentTypeId: number;
    comment: Prisma.InputJsonValue;
    userId: number;
  }): Promise<ProgressReportIssueCommentWithRelations> {
    return this.prisma.progressReportIssueComment.create({
      data: input,
      select: issueCommentSelect,
    });
  }

  updateIssueComment(
    commentId: number,
    input: {
      commentTypeId: number;
      comment: Prisma.InputJsonValue;
      userId: number;
    },
  ): Promise<ProgressReportIssueCommentWithRelations> {
    return this.prisma.progressReportIssueComment.update({
      where: { id: commentId },
      data: input,
      select: issueCommentSelect,
    });
  }

  softDeleteIssueComment(commentId: number) {
    return this.prisma.progressReportIssueComment.update({
      where: { id: commentId },
      data: { deletedAt: new Date() },
      select: { id: true },
    });
  }

  findActiveRgcDecisionComment(
    commentId: number,
    progressReportUpdatePlenaryDecisionId: number,
  ) {
    return this.prisma.progressReportIssueComment.findFirst({
      where: {
        id: commentId,
        progressReportUpdatePlenaryDecisionId,
        deletedAt: null,
      },
      select: issueCommentSelect,
    });
  }

  createRgcDecisionComment(input: {
    progressReportUpdatePlenaryDecisionId: number;
    commentTypeId: number;
    comment: Prisma.InputJsonValue;
    userId: number;
  }): Promise<ProgressReportIssueCommentWithRelations> {
    return this.prisma.progressReportIssueComment.create({
      data: input,
      select: issueCommentSelect,
    });
  }

  updateRgcDecisionComment(
    commentId: number,
    input: {
      commentTypeId: number;
      comment: Prisma.InputJsonValue;
      userId: number;
    },
  ): Promise<ProgressReportIssueCommentWithRelations> {
    return this.prisma.progressReportIssueComment.update({
      where: { id: commentId },
      data: input,
      select: issueCommentSelect,
    });
  }

  softDeleteRgcDecisionComment(commentId: number) {
    return this.prisma.progressReportIssueComment.update({
      where: { id: commentId },
      data: { deletedAt: new Date() },
      select: { id: true },
    });
  }

  upsertProgressReportUpdate(input: {
    progressReportId: number;
    ministryId: number;
    issueId: number;
    issueStatusId: number;
    issueResolveId: number;
    indicators: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    progressSolution: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    implementationChallenges:
      | Prisma.InputJsonValue
      | Prisma.NullableJsonNullValueInput;
    requests: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    sourceOfVerification: string | null;
    linkToVerificationSource: string | null;
    nextStep: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    dateOfIssueSolution: Date | null;
    attachement: string | null;
    userId: number;
  }): Promise<ProgressReportUpdateWithRelations> {
    const {
      progressReportId,
      ministryId,
      issueId,
      userId,
      issueStatusId,
      issueResolveId,
      nextStep,
      ...values
    } = input;

    return this.prisma.$transaction(async (transaction) => {
      const update = await transaction.progressReportUpdate.upsert({
        where: {
          progressReportId_ministryId_issueId: {
            progressReportId,
            ministryId,
            issueId,
          },
        },
        create: {
          progressReportId,
          ministryId,
          issueId,
          userId,
          issueStatusId,
          issueResolveId,
          ...values,
        },
        update: {
          userId,
          issueStatusId,
          issueResolveId,
          deletedAt: null,
          ...values,
        },
      });

      // Issue status lives on the master Issues table, so mirror it there.
      await transaction.issues.update({
        where: { id: issueId },
        data: { issueStatusId },
      });

      // `nextStep` now belongs to IssueResolves in the final schema.
      await transaction.issueResolves.update({
        where: { id: issueResolveId },
        data: { nextStep },
      });

      // Keep the new relation table synchronized with the saved update.
      await transaction.progressReportUpdateIssue.upsert({
        where: {
          progressReportUpdateId_ministryId_issueId: {
            progressReportUpdateId: update.id,
            ministryId,
            issueId,
          },
        },
        create: {
          progressReportUpdateId: update.id,
          ministryId,
          issueId,
        },
        update: {},
      });

      return transaction.progressReportUpdate.findUniqueOrThrow({
        where: { id: update.id },
        include: progressReportUpdateRelations,
      });
    });
  }

  updateIssueStatus(issueId: number, issueStatusId: number) {
    return this.prisma.issues.update({
      where: { id: issueId },
      data: { issueStatusId },
      select: {
        id: true,
        issueStatus: {
          select: { id: true, code: true, name: true },
        },
      },
    });
  }

  findPswgRgcDecisions(
    ministryId: number,
    workingGroupIds: number[],
  ): Promise<PswgRgcDecision[]> {
    return this.prisma.plenaryRgcDecision.findMany({
      where: {
        deletedAt: null,
        stakeholderId: ministryId,
        stakeholder: {
          active: true,
          deletedAt: null,
        },
        category: { deletedAt: null },
        decisionIssues: {
          some: {
            issue: {
              deletedAt: null,
              stakeholderId: { in: workingGroupIds },
              stakeholder: {
                active: true,
                deletedAt: null,
              },
              governmentAgencies: {
                some: {
                  stakeholderId: ministryId,
                  agencyOrder: PRIMARY_AGENCY_ORDER,
                },
              },
            },
          },
        },
      },
      orderBy: [{ meetingDate: 'desc' }, { id: 'desc' }],
      include: {
        ...pswgRgcDecisionRelations,
        decisionIssues: {
          ...pswgRgcDecisionRelations.decisionIssues,
          where: {
            issue: {
              deletedAt: null,
              stakeholderId: { in: workingGroupIds },
              governmentAgencies: {
                some: {
                  stakeholderId: ministryId,
                  agencyOrder: PRIMARY_AGENCY_ORDER,
                },
              },
            },
          },
        },
      },
    });
  }

  findMinistryRgcDecisions(
    ministryId: number,
  ): Promise<ProgressReportRgcDecision[]> {
    return this.prisma.plenaryRgcDecision.findMany({
      where: {
        deletedAt: null,
        stakeholderId: ministryId,
        status: { in: OPEN_RGC_DECISION_STATUSES },
        stakeholder: {
          active: true,
          deletedAt: null,
        },
        category: { deletedAt: null },
      },
      orderBy: [{ meetingDate: 'desc' }, { id: 'desc' }],
      include: pswgRgcDecisionRelations,
    });
  }

  findOpenMinistryRgcDecision(plenaryDecisionId: number, ministryId: number) {
    return this.prisma.plenaryRgcDecision.findFirst({
      where: {
        id: plenaryDecisionId,
        stakeholderId: ministryId,
        status: { in: OPEN_RGC_DECISION_STATUSES },
        deletedAt: null,
        stakeholder: {
          active: true,
          deletedAt: null,
        },
        category: { deletedAt: null },
      },
      include: pswgRgcDecisionRelations,
    });
  }

  findMinistryRgcDecisionById(
    plenaryDecisionId: number,
    ministryId: number,
  ): Promise<ProgressReportRgcDecision | null> {
    return this.prisma.plenaryRgcDecision.findFirst({
      where: {
        id: plenaryDecisionId,
        stakeholderId: ministryId,
        deletedAt: null,
      },
      include: pswgRgcDecisionRelations,
    });
  }

  updateRgcDecisionStatus(
    plenaryDecisionId: number,
    status: RgcDecisionStatus,
  ) {
    return this.prisma.plenaryRgcDecision.update({
      where: { id: plenaryDecisionId },
      data: { status },
      select: { id: true, status: true },
    });
  }

  findProgressReportRgcDecisionUpdate(
    progressReportId: number,
    ministryId: number,
    plenaryDecisionId: number,
  ) {
    return this.prisma.progressReportUpdatePlenaryDecision.findUnique({
      where: {
        progressReportId_ministryId_plenaryDecisionId: {
          progressReportId,
          ministryId,
          plenaryDecisionId,
        },
      },
      include: progressReportRgcDecisionUpdateRelations,
    });
  }

  findProgressReportRgcDecisionUpdates(
    progressReportId: number,
    ministryId: number,
    plenaryDecisionIds: number[],
  ): Promise<ProgressReportRgcDecisionUpdate[]> {
    if (plenaryDecisionIds.length === 0) return Promise.resolve([]);

    return this.prisma.progressReportUpdatePlenaryDecision.findMany({
      where: {
        progressReportId,
        ministryId,
        plenaryDecisionId: { in: plenaryDecisionIds },
        deletedAt: null,
        progressReportUpdate: { deletedAt: null },
      },
      include: progressReportRgcDecisionUpdateRelations,
    });
  }

  upsertProgressReportRgcDecisionUpdate(input: {
    progressReportId: number;
    ministryId: number;
    plenaryDecisionId: number;
    issueStatusId: number;
    status: RgcDecisionStatus;
    categoryId?: number;
    category?: string;
    indicatorId?: number;
    meetingDate?: Date;
    focalPerson: string;
    decision: string;
    verificationSource?: string;
    verificationLink?: string;
    indicators: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    progressSolution: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    implementationChallenges:
      | Prisma.InputJsonValue
      | Prisma.NullableJsonNullValueInput;
    requests: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    sourceOfVerification: string | null;
    linkToVerificationSource: string | null;
    nextStep: Prisma.InputJsonValue | Prisma.NullableJsonNullValueInput;
    dateOfIssueSolution: Date | null;
    attachement: string | null;
    userId: number;
  }): Promise<ProgressReportUpdateWithRelations> {
    const {
      progressReportId,
      ministryId,
      plenaryDecisionId,
      issueStatusId,
      userId,
      nextStep,
      status,
      categoryId,
      category,
      indicatorId,
      meetingDate,
      focalPerson,
      decision,
      verificationSource,
      verificationLink,
      ...values
    } = input;

    return this.prisma.$transaction(async (transaction) => {
      let nextCategoryId = categoryId;
      const nextIndicatorId = indicatorId;

      if (nextCategoryId !== undefined) {
        const selectedCategory = await transaction.categories.findFirst({
          where: { id: nextCategoryId, deletedAt: null },
          select: { id: true },
        });

        if (!selectedCategory) {
          throw new BadRequestException('Selected Category does not exist.');
        }
      } else if (category?.trim()) {
        const categoryName = category.trim();
        const existingCategory = await transaction.categories.findFirst({
          where: {
            name: { equals: categoryName, mode: 'insensitive' },
            deletedAt: null,
          },
          select: { id: true },
        });

        nextCategoryId = existingCategory
          ? existingCategory.id
          : (
              await transaction.categories.create({
                data: { name: categoryName, userId },
                select: { id: true },
              })
            ).id;
      }

      if (nextIndicatorId !== undefined) {
        const selectedIndicator = await transaction.indicator.findFirst({
          where: { id: nextIndicatorId, deletedAt: null },
          select: { id: true },
        });

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

      await transaction.plenaryRgcDecision.update({
        where: { id: plenaryDecisionId },
        data: {
          status,
          focalPerson: focalPerson.trim(),
          decision: decision.trim(),
          ...(nextCategoryId !== undefined && {
            categoryId: nextCategoryId,
          }),
          ...(nextIndicatorId !== undefined && {
            indicatorId: nextIndicatorId,
          }),
          ...(meetingDate !== undefined && { meetingDate }),
          ...(verificationSource !== undefined && {
            verificationSource: verificationSource.trim() || null,
          }),
          ...(verificationLink !== undefined && {
            verificationLink: verificationLink.trim() || null,
          }),
        },
      });

      const existingRelation =
        await transaction.progressReportUpdatePlenaryDecision.findUnique({
          where: {
            progressReportId_ministryId_plenaryDecisionId: {
              progressReportId,
              ministryId,
              plenaryDecisionId,
            },
          },
          select: { progressReportUpdateId: true },
        });

      const update = existingRelation
        ? await transaction.progressReportUpdate.update({
            where: { id: existingRelation.progressReportUpdateId },
            data: {
              issueStatusId,
              userId,
              next_step: nextStep,
              deletedAt: null,
              ...values,
            },
          })
        : await transaction.progressReportUpdate.create({
            data: {
              progressReportId,
              ministryId,
              issueStatusId,
              userId,
              next_step: nextStep,
              ...values,
            },
          });

      await transaction.progressReportUpdatePlenaryDecision.upsert({
        where: {
          progressReportId_ministryId_plenaryDecisionId: {
            progressReportId,
            ministryId,
            plenaryDecisionId,
          },
        },
        create: {
          progressReportUpdateId: update.id,
          progressReportId,
          ministryId,
          plenaryDecisionId,
        },
        update: {
          progressReportUpdateId: update.id,
          deletedAt: null,
        },
      });

      return transaction.progressReportUpdate.findUniqueOrThrow({
        where: { id: update.id },
        include: progressReportUpdateRelations,
      });
    });
  }

  updateAssignment(
    assignmentId: number,
    data: Prisma.ProgressReportMinistryUpdateInput,
  ) {
    return this.prisma.progressReportMinistry.update({
      where: { id: assignmentId },
      data,
      include: assignmentRelations,
    });
  }

  async findActiveMinistryIds(): Promise<number[]> {
    const ministries = await this.prisma.stakeholder.findMany({
      where: {
        active: true,
        deletedAt: null,
        stakeholderType: {
          name: { equals: 'Ministry', mode: 'insensitive' },
          deletedAt: null,
        },
      },
      select: { id: true },
    });

    return ministries.map((ministry) => ministry.id);
  }

  // Basic profile of one user, used to show who is preparing a report
  // before it has actually been submitted.
  findUserProfile(userId: number) {
    return this.prisma.user.findFirst({
      where: { id: userId, isActive: true, deletedAt: null },
      select: {
        id: true,
        name: true,
        position: true,
      },
    });
  }

  findUserAccessContext(userId: number) {
    return this.prisma.user.findFirst({
      where: { id: userId, isActive: true, deletedAt: null },
      select: {
        roles: {
          where: { role: { deletedAt: null } },
          select: { role: { select: { name: true } } },
        },
        stakeholders: {
          where: {
            stakeholder: {
              active: true,
              deletedAt: null,
              stakeholderType: {
                name: { equals: 'Ministry', mode: 'insensitive' },
                deletedAt: null,
              },
            },
          },
          select: { stakeholderId: true },
        },
      },
    });
  }

  findPswgUserAccessContext(userId: number) {
    return this.prisma.user.findFirst({
      where: { id: userId, isActive: true, deletedAt: null },
      select: {
        roles: {
          where: { role: { deletedAt: null } },
          select: { role: { select: { name: true } } },
        },
        stakeholders: {
          where: {
            stakeholder: {
              active: true,
              deletedAt: null,
              stakeholderType: {
                name: { equals: 'Private Sector', mode: 'insensitive' },
                deletedAt: null,
              },
            },
          },
          select: { stakeholderId: true },
        },
      },
    });
  }

  async findActiveUserIdsByRoleName(roleName: string): Promise<number[]> {
    const links = await this.prisma.userHasRoles.findMany({
      where: {
        role: { name: roleName, deletedAt: null },
        user: { isActive: true, deletedAt: null },
      },
      select: { userId: true },
    });

    return Array.from(new Set(links.map((link) => link.userId)));
  }
}