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

import { ExcelExportService } from '@/common/excel/excel-export.service';
import { UploadService } from '@/modules/upload/upload.service';
import { CreateCategoryDto } from './dto/create-category.dto';
import { ExportWorkingGroupIssuesDto } from './dto/export-working-group-issues.dto';
import { CreateIssueStatusDto } from './dto/create-issue-status.dto';
import {
  CreateIssueGovernmentAgencyDto,
  CreateWorkingGroupIssueDto,
} from './dto/create-working-group-issue.dto';
import { QueryWorkingGroupIssuesDto } from './dto/query-working-group-issue.dto';
import { UpdateCategoryDto } from './dto/update-category.dto';
import { UpdateIssueStatusDto } from './dto/update-issue-status.dto';
import { UpdateWorkingGroupIssueDto } from './dto/update-working-group-issue.dto';
import {
  CreateIssueData,
  IssueGovernmentAgencyInput,
  IssueListFilters,
  IssueSortField,
  IssueSortOrder,
  UpdateIssueData,
  WorkingGroupIssuesRepository,
} from './working-group-issues.repository';
import {
  buildIssueMatrixExportFilename,
  buildWorkingGroupIssuesExportFilename,
  getIssueMatrixExportColumns,
  getWorkingGroupIssuesExportColumns,
  mapIssueMatrixToExportRow,
  mapWorkingGroupIssueToExportRow,
} from './working-group-issues-export.mapper';

export type WorkingGroupIssueResponse = NonNullable<
  Awaited<ReturnType<WorkingGroupIssuesRepository['findById']>>
>;

export type IssueStatusOption = Awaited<
  ReturnType<WorkingGroupIssuesRepository['listIssueStatuses']>
>[number];

export type CategoryOption = Awaited<
  ReturnType<WorkingGroupIssuesRepository['listCategories']>
>[number];

export type GovernmentAgencyOption = Awaited<
  ReturnType<WorkingGroupIssuesRepository['listGovernmentAgencies']>
>[number];

export type CategoryDetail = NonNullable<
  Awaited<ReturnType<WorkingGroupIssuesRepository['findCategoryDetailById']>>
>;

export type IssueStatusDetail = NonNullable<
  Awaited<ReturnType<WorkingGroupIssuesRepository['findIssueStatusDetailById']>>
>;

export type WorkingGroupIssueSummary = Awaited<
  ReturnType<WorkingGroupIssuesRepository['getSummaryCounts']>
>;

export type PaginationMeta = {
  page: number;
  limit: number;
  total: number;
  totalPages: number;
};

export type PaginatedWorkingGroupIssuesResponse = {
  items: WorkingGroupIssueResponse[];
  meta: PaginationMeta;
};

export type WorkingGroupIssueMutationResponse = {
  message: string;
  issue: WorkingGroupIssueResponse;
};

export type CategoryMutationResponse = {
  message: string;
  category: CategoryDetail;
};

export type IssueStatusMutationResponse = {
  message: string;
  status: IssueStatusDetail;
};

export type MessageResponse = {
  message: string;
};

export type WorkingGroupIssuePrimaryGovernment = {
  workingGroup: {
    id: number;
    name: string;
  };
  governmentAgency: GovernmentAgencyOption;
};

// Media types accepted for issue attachments.
// 'svg' is listed separately because the upload module treats SVG as its
// own type. Keeping it preserves existing behavior for issue attachments.
const ISSUE_ATTACHMENT_MEDIA_TYPES = ['image', 'svg', 'pdf', 'document'];

// Name of the category that is used (and auto-created) when the caller
// does not pick one explicitly.
const DEFAULT_ISSUE_CATEGORY_NAME = 'General';

// Status names that feed the dashboard summary buckets. Matched
// case-insensitively against the IssueStatuses table.
const SUMMARY_STATUS_NAMES = {
  solved: 'Solved',
  inProgress: 'In Progress',
  notAddressed: 'Not Addressed',
};

// Defaults applied when the caller omits the list-query params.
const DEFAULT_PAGE = 1;
const DEFAULT_LIMIT = 10;
const DEFAULT_SORT_BY: IssueSortField = 'createdAt';
const DEFAULT_SORT_ORDER: IssueSortOrder = 'desc';
const PRIVATE_SECTOR_STAKEHOLDER_TYPE_NAME = 'Private Sector';

@Injectable()
export class WorkingGroupIssuesService {
  constructor(
    private readonly issues: WorkingGroupIssuesRepository,
    private readonly uploads: UploadService,
    private readonly excelExport: ExcelExportService,
  ) {}

  // Create a new issue along with its government-agency assignments.
  async create(
    userId: number,
    dto: CreateWorkingGroupIssueDto,
    attachmentFile?: Express.Multer.File,
  ): Promise<WorkingGroupIssueMutationResponse> {
    const ownerStakeholder = await this.requirePrivateSectorStakeholder(userId);

    // Validate every foreign-key the caller picked before we hit the DB.
    await this.ensureIssueStatusExists(dto.issueStatusId);
    const categoryId = await this.resolveCategoryId(userId, dto.categoryId);

    if (dto.meetingRequestId !== undefined) {
      await this.ensureMeetingRequestExists(dto.meetingRequestId);
    }

    await this.ensureGovernmentAgenciesAreValid(dto.governmentAgencies);

    // Persist either the uploaded file or the provided attachment URL.
    const attachmentUrl = await this.resolveAttachmentUrl(dto, attachmentFile);

    const createData: CreateIssueData = {
      title: dto.title.trim(),
      description: dto.description.trim(),
      recommendation: dto.recommendation.trim(),
      attachment: attachmentUrl,
      issueStatusId: dto.issueStatusId,
      categoryId,
      meetingRequestId: dto.meetingRequestId,
      userId,
      stakeholderId: ownerStakeholder.id,
      governmentAgencies: this.toAgencyInputs(dto.governmentAgencies),
    };

    const issue = await this.issues.create(createData);

    return {
      message: 'Working group issue created successfully.',
      issue,
    };
  }

  // Return a paginated, filtered, and sorted page of active issues along
  // with pagination metadata so the client can render page controls.
  async findAll(
    query: QueryWorkingGroupIssuesDto,
  ): Promise<PaginatedWorkingGroupIssuesResponse> {
    return this.findIssuePage(query);
  }

  // Return a paginated page owned by the logged-in user's Private Sector.
  async findMyIssues(
    userId: number,
    query: QueryWorkingGroupIssuesDto,
  ): Promise<PaginatedWorkingGroupIssuesResponse> {
    const ownerStakeholder = await this.requirePrivateSectorStakeholder(userId);

    return this.findIssuePage(
      {
        ...query,
        userId: undefined,
      },
      ownerStakeholder.id,
    );
  }

  // Return issues created by Private Sector working groups. Access is gated
  // by the route's CASL policy ("read IssueMatrix"), so oversight roles like
  // CDC G-PSF can view the matrix without being a private-sector stakeholder.
  async findIssueMatrix(
    query: QueryWorkingGroupIssuesDto,
  ): Promise<PaginatedWorkingGroupIssuesResponse> {
    return this.findIssuePage(
      {
        ...query,
        userId: undefined,
      },
      undefined,
      {
        ownerStakeholderTypeName: PRIVATE_SECTOR_STAKEHOLDER_TYPE_NAME,
      },
    );
  }

  async exportMyIssues(
    userId: number,
    query: ExportWorkingGroupIssuesDto,
  ): Promise<{ buffer: Buffer; filename: string }> {
    const ownerStakeholder = await this.requirePrivateSectorStakeholder(userId);
    const lang = query.lang ?? 'en';
    const sortBy = query.sortBy ?? DEFAULT_SORT_BY;
    const sortOrder = query.sortOrder ?? DEFAULT_SORT_ORDER;
    const filters = this.buildExportFilters(query, {
      ownerStakeholderId: ownerStakeholder.id,
    });

    const items = await this.issues.findAllForExport({
      filters,
      sortBy,
      sortOrder,
    });

    const columns = getWorkingGroupIssuesExportColumns(lang);
    const rows = items.map((issue, index) =>
      mapWorkingGroupIssueToExportRow(issue, index, lang),
    );

    const buffer = await this.excelExport.buildWorkbookBuffer({
      sheetName: 'Working Group Issues',
      columns,
      rows,
    });

    return {
      buffer,
      filename: buildWorkingGroupIssuesExportFilename(),
    };
  }

  // Access is gated by the route's CASL policy ("export IssueMatrix").
  async exportIssueMatrix(
    query: ExportWorkingGroupIssuesDto,
  ): Promise<{ buffer: Buffer; filename: string }> {
    const lang = query.lang ?? 'en';
    const sortBy = query.sortBy ?? DEFAULT_SORT_BY;
    const sortOrder = query.sortOrder ?? DEFAULT_SORT_ORDER;
    const filters = this.buildExportFilters(query, {
      ownerStakeholderIds: query.ownerStakeholderIds,
      ownerStakeholderTypeName: PRIVATE_SECTOR_STAKEHOLDER_TYPE_NAME,
    });

    const items = await this.issues.findAllForExport({
      filters,
      sortBy,
      sortOrder,
    });

    const columns = getIssueMatrixExportColumns(lang);
    const rows = items.map((issue) => mapIssueMatrixToExportRow(issue, lang));

    const buffer = await this.excelExport.buildWorkbookBuffer({
      sheetName: 'Issue Matrix',
      columns,
      rows,
    });

    return {
      buffer,
      filename: buildIssueMatrixExportFilename(),
    };
  }

  async getMyPrimaryGovernment(
    userId: number,
  ): Promise<WorkingGroupIssuePrimaryGovernment> {
    const ownerStakeholder = await this.requirePrivateSectorStakeholder(userId);

    if (!ownerStakeholder.relatedStakeholderId) {
      throw new BadRequestException(
        'Working group does not have a related government agency',
      );
    }

    const governmentAgency = await this.issues.findGovernmentAgencyById(
      ownerStakeholder.relatedStakeholderId,
    );

    if (!governmentAgency) {
      throw new BadRequestException(
        'Related government agency was not found or is inactive',
      );
    }

    return {
      workingGroup: {
        id: ownerStakeholder.id,
        name: ownerStakeholder.name,
      },
      governmentAgency,
    };
  }

  private async findIssuePage(
    query: QueryWorkingGroupIssuesDto,
    ownerStakeholderId?: number,
    options?: {
      excludeOwnerStakeholderId?: number;
      ownerStakeholderTypeName?: string;
    },
  ): Promise<PaginatedWorkingGroupIssuesResponse> {
    const page = query.page ?? DEFAULT_PAGE;
    const limit = query.limit ?? DEFAULT_LIMIT;
    const sortBy = query.sortBy ?? DEFAULT_SORT_BY;
    const sortOrder = query.sortOrder ?? DEFAULT_SORT_ORDER;
    const skip = (page - 1) * limit;

    const filters = this.buildListFilters(query, ownerStakeholderId, options);

    const { items, total } = await this.issues.findPaginated({
      filters,
      sortBy,
      sortOrder,
      skip,
      take: limit,
    });

    const totalPages = total === 0 ? 0 : Math.ceil(total / limit);

    return {
      items,
      meta: { page, limit, total, totalPages },
    };
  }

  // Return a single issue or throw 404 when it does not exist.
  async findOne(
    id: number,
    userId?: number,
  ): Promise<WorkingGroupIssueResponse> {
    return this.findAccessibleIssue(id, userId, 'view');
  }

  // Update an existing issue, optionally replacing its agency list and attachment.
  async update(
    id: number,
    dto: UpdateWorkingGroupIssueDto,
    attachmentFile?: Express.Multer.File,
    userId?: number,
  ): Promise<WorkingGroupIssueMutationResponse> {
    const existing = await this.findAccessibleIssue(id, userId, 'update');

    if (dto.issueStatusId !== undefined) {
      await this.ensureIssueStatusExists(dto.issueStatusId);
    }

    if (dto.categoryId !== undefined) {
      await this.ensureCategoryExists(dto.categoryId);
    }

    if (dto.meetingRequestId !== undefined) {
      await this.ensureMeetingRequestExists(dto.meetingRequestId);
    }

    if (dto.governmentAgencies !== undefined) {
      await this.ensureGovernmentAgenciesAreValid(dto.governmentAgencies);
    }

    // Build the update payload. We pass `attachment` only when the caller
    // either uploaded a file or supplied an attachment URL — leaving it
    // untouched if neither was provided.
    const attachmentChanged =
      attachmentFile !== undefined || dto.attachment !== undefined;
    let uploadedAttachmentUrl: string | null = null;

    try {
      const attachmentUrl = attachmentChanged
        ? await this.resolveAttachmentUrl(dto, attachmentFile)
        : undefined;

      if (attachmentFile && attachmentUrl) {
        uploadedAttachmentUrl = attachmentUrl;
      }

      const updateData: UpdateIssueData = {
        ...(dto.title !== undefined && { title: dto.title.trim() }),
        ...(dto.description !== undefined && {
          description: dto.description.trim(),
        }),
        ...(dto.recommendation !== undefined && {
          recommendation: dto.recommendation.trim(),
        }),
        ...(attachmentChanged && { attachment: attachmentUrl ?? null }),
        ...(dto.issueStatusId !== undefined && {
          issueStatusId: dto.issueStatusId,
        }),
        ...(dto.categoryId !== undefined && {
          categoryId: dto.categoryId,
        }),
        ...(dto.meetingRequestId !== undefined && {
          meetingRequestId: dto.meetingRequestId,
        }),
      };

      const agencies =
        dto.governmentAgencies !== undefined
          ? this.toAgencyInputs(dto.governmentAgencies)
          : undefined;

      const issue = await this.issues.update(id, updateData, agencies);

      if (
        uploadedAttachmentUrl &&
        existing.attachment &&
        existing.attachment !== uploadedAttachmentUrl
      ) {
        await this.uploads.remove(existing.attachment);
      }

      return {
        message: 'Working group issue updated successfully.',
        issue,
      };
    } catch (error) {
      if (uploadedAttachmentUrl) {
        await this.uploads.remove(uploadedAttachmentUrl);
      }

      throw error;
    }
  }

  // Soft-delete an issue.
  async remove(id: number, userId?: number): Promise<MessageResponse> {
    await this.findAccessibleIssue(id, userId, 'delete');
    await this.issues.softDelete(id);
    return { message: 'Working group issue deleted successfully.' };
  }

  // Dashboard summary: total active issues plus a bucket count per named
  // status. Names that don't exist in the IssueStatuses table simply report 0.
  getSummary(): Promise<WorkingGroupIssueSummary> {
    return this.issues.getSummaryCounts(SUMMARY_STATUS_NAMES);
  }

  async getMySummary(userId: number): Promise<WorkingGroupIssueSummary> {
    const ownerStakeholder = await this.requirePrivateSectorStakeholder(userId);

    return this.issues.getSummaryCounts(SUMMARY_STATUS_NAMES, {
      ownerStakeholderId: ownerStakeholder.id,
    });
  }

  // Access is gated by the route's CASL policy ("read IssueMatrix").
  async getIssueMatrixSummary(): Promise<WorkingGroupIssueSummary> {
    return this.issues.getSummaryCounts(SUMMARY_STATUS_NAMES, {
      ownerStakeholderTypeName: PRIVATE_SECTOR_STAKEHOLDER_TYPE_NAME,
    });
  }

  // Form-support endpoints: thin pass-throughs to the repository lookups.
  listIssueStatuses(): Promise<IssueStatusOption[]> {
    return this.issues.listIssueStatuses();
  }

  listCategories(): Promise<CategoryOption[]> {
    return this.issues.listCategories();
  }

  // --- Issue category CRUD ---------------------------------------------

  // Create a new category. Name must be unique among active categories
  // (case-insensitive so "General" and "general" don't both end up in
  // the dropdown).
  async createCategory(
    userId: number,
    dto: CreateCategoryDto,
  ): Promise<CategoryMutationResponse> {
    const name = dto.name.trim();
    await this.ensureCategoryNameIsUnique(name);

    const category = await this.issues.createCategoryDetail(name, userId);
    return {
      message: 'Issue category created successfully.',
      category,
    };
  }

  // Fetch one category or throw 404.
  async getCategory(id: number): Promise<CategoryDetail> {
    const category = await this.issues.findCategoryDetailById(id);
    if (!category) {
      throw new NotFoundException('Issue category not found');
    }
    return category;
  }

  // Update a category's name. Re-checks uniqueness (excluding the row
  // being updated) so two categories never share a name.
  async updateCategory(
    id: number,
    dto: UpdateCategoryDto,
  ): Promise<CategoryMutationResponse> {
    await this.ensureCategoryExists(id);

    const trimmedName = dto.name?.trim();
    if (trimmedName !== undefined) {
      await this.ensureCategoryNameIsUnique(trimmedName, id);
    }

    const category = await this.issues.updateCategory(id, {
      name: trimmedName,
    });

    return {
      message: 'Issue category updated successfully.',
      category,
    };
  }

  // Soft-delete a category. Existing issues that reference it keep their
  // FK — they just point at a soft-deleted row.
  async removeCategory(id: number): Promise<MessageResponse> {
    await this.ensureCategoryExists(id);
    await this.issues.softDeleteCategory(id);
    return { message: 'Issue category deleted successfully.' };
  }

  // --- Issue status CRUD -----------------------------------------------

  // Create a new status. Name AND code must each be unique among active
  // statuses (case-insensitive so "In Progress" and "in progress" don't
  // both end up in the dropdown, and "IN_PROGRESS" doesn't collide with
  // "in_progress").
  async createStatus(
    userId: number,
    dto: CreateIssueStatusDto,
  ): Promise<IssueStatusMutationResponse> {
    const name = dto.name.trim();
    const code = dto.code.trim().toUpperCase();
    await this.ensureStatusNameIsUnique(name);
    await this.ensureStatusCodeIsUnique(code);

    const status = await this.issues.createIssueStatusDetail(
      name,
      code,
      userId,
    );
    return {
      message: 'Issue status created successfully.',
      status,
    };
  }

  // Fetch one status or throw 404.
  async getStatus(id: number): Promise<IssueStatusDetail> {
    const status = await this.issues.findIssueStatusDetailById(id);
    if (!status) {
      throw new NotFoundException('Issue status not found');
    }
    return status;
  }

  // Update a status's name and/or code. Re-checks uniqueness (excluding
  // the row being updated) so two statuses never share a name or code.
  async updateStatus(
    id: number,
    dto: UpdateIssueStatusDto,
  ): Promise<IssueStatusMutationResponse> {
    await this.ensureIssueStatusExists(id);

    const trimmedName = dto.name?.trim();
    if (trimmedName !== undefined) {
      await this.ensureStatusNameIsUnique(trimmedName, id);
    }

    const trimmedCode = dto.code?.trim().toUpperCase();
    if (trimmedCode !== undefined) {
      await this.ensureStatusCodeIsUnique(trimmedCode, id);
    }

    const status = await this.issues.updateIssueStatus(id, {
      name: trimmedName,
      code: trimmedCode,
    });

    return {
      message: 'Issue status updated successfully.',
      status,
    };
  }

  // Soft-delete a status. Existing issues that reference it keep their
  // FK — they just point at a soft-deleted row.
  async removeStatus(id: number): Promise<MessageResponse> {
    await this.ensureIssueStatusExists(id);
    await this.issues.softDeleteIssueStatus(id);
    return { message: 'Issue status deleted successfully.' };
  }

  listGovernmentAgencies(): Promise<GovernmentAgencyOption[]> {
    return this.issues.listGovernmentAgencies();
  }

  // --- Private helpers ---------------------------------------------------

  // Build the plain filter object the repository expects from the validated
  // query DTO. Dates are parsed here so the repo only deals with `Date`
  // instances. Cross-field validation (createdFrom <= createdTo) lives here
  // because it is business-rule territory, not a single-field constraint.
  private buildExportFilters(
    query: ExportWorkingGroupIssuesDto,
    options: {
      ownerStakeholderId?: number;
      ownerStakeholderIds?: number[];
      ownerStakeholderTypeName?: string;
    },
  ): IssueListFilters {
    return {
      search: query.search?.trim() || undefined,
      issueStatusIds: query.issueStatusIds,
      categoryIds: query.categoryIds,
      primaryAgencyIds: query.primaryAgencyIds,
      ownerStakeholderId: options.ownerStakeholderId,
      ownerStakeholderIds: options.ownerStakeholderIds,
      ownerStakeholderTypeName: options.ownerStakeholderTypeName,
      years: query.years,
      hasAttachment: query.hasAttachment,
    };
  }

  private buildListFilters(
    query: QueryWorkingGroupIssuesDto,
    ownerStakeholderId?: number,
    options?: {
      excludeOwnerStakeholderId?: number;
      ownerStakeholderTypeName?: string;
    },
  ): IssueListFilters {
    const createdFrom = query.createdFrom
      ? new Date(query.createdFrom)
      : undefined;
    const createdTo = query.createdTo ? new Date(query.createdTo) : undefined;

    if (createdFrom && createdTo && createdFrom > createdTo) {
      throw new BadRequestException(
        'createdFrom must be earlier than or equal to createdTo',
      );
    }

    return {
      search: query.search?.trim() || undefined,
      issueStatusId: query.issueStatusId,
      categoryId: query.categoryId,
      meetingRequestId: query.meetingRequestId,
      userId: query.userId,
      ownerStakeholderId,
      excludeOwnerStakeholderId: options?.excludeOwnerStakeholderId,
      ownerStakeholderTypeName: options?.ownerStakeholderTypeName,
      stakeholderId: query.stakeholderId,
      primaryAgencyId: query.primaryAgencyId,
      createdFrom,
      createdTo,
      hasAttachment: query.hasAttachment,
    };
  }

  private async requirePrivateSectorStakeholder(userId: number) {
    const stakeholder =
      await this.issues.findPrivateSectorStakeholderByUserId(userId);

    if (!stakeholder) {
      throw new ForbiddenException(
        'User is not assigned to a private sector working group',
      );
    }

    return stakeholder;
  }

  private async findAccessibleIssue(
    issueId: number,
    userId: number | undefined,
    action: 'view' | 'update' | 'delete',
  ): Promise<WorkingGroupIssueResponse> {
    const issue = await this.issues.findById(issueId);

    if (!issue) {
      throw new NotFoundException('Working group issue not found');
    }

    if (userId === undefined) {
      return issue;
    }

    const stakeholder =
      await this.issues.findPrivateSectorStakeholderByUserId(userId);

    // Users without a Private Sector stakeholder are handled by route
    // permissions. This keeps admin-style accounts able to manage all issues.
    if (!stakeholder) {
      return issue;
    }

    const issueOwnerTypeName = issue.stakeholder?.stakeholderType?.name?.trim();
    const isPrivateSectorIssue =
      issueOwnerTypeName?.toLowerCase() ===
      PRIVATE_SECTOR_STAKEHOLDER_TYPE_NAME.toLowerCase();

    if (action === 'view' && isPrivateSectorIssue) {
      return issue;
    }

    if (issue.stakeholderId !== stakeholder.id) {
      throw new ForbiddenException(
        `You cannot ${action} another working group's issue`,
      );
    }

    return issue;
  }

  // Map the DTO agency items into the plain shape the repository expects.
  private toAgencyInputs(
    agencies: CreateIssueGovernmentAgencyDto[],
  ): IssueGovernmentAgencyInput[] {
    return agencies.map((agency) => ({
      stakeholderId: agency.stakeholderId,
      agencyOrder: agency.agencyOrder,
    }));
  }

  // Upload the file (if any) or fall back to the URL on the DTO.
  // Returns null when neither is provided (so the column is cleared).
  private async resolveAttachmentUrl(
    dto: Pick<CreateWorkingGroupIssueDto, 'attachment'>,
    attachmentFile?: Express.Multer.File,
  ): Promise<string | null> {
    if (attachmentFile) {
      const uploaded = await this.uploads.save(attachmentFile, 'issues', {
        allowedMediaTypes: ISSUE_ATTACHMENT_MEDIA_TYPES,
      });
      return uploaded.url;
    }

    return dto.attachment?.trim() || null;
  }

  // Pick the chosen category, or look up / create the default one.
  private async resolveCategoryId(
    userId: number,
    categoryId?: number,
  ): Promise<number> {
    if (categoryId !== undefined) {
      await this.ensureCategoryExists(categoryId);
      return categoryId;
    }

    const existingDefault = await this.issues.findCategoryByName(
      DEFAULT_ISSUE_CATEGORY_NAME,
    );
    if (existingDefault) {
      return existingDefault.id;
    }

    const createdDefault = await this.issues.createCategory(
      DEFAULT_ISSUE_CATEGORY_NAME,
      userId,
    );
    return createdDefault.id;
  }

  private async ensureIssueExists(id: number): Promise<void> {
    const found = await this.issues.findMinimalById(id);
    if (!found) {
      throw new NotFoundException('Working group issue not found');
    }
  }

  private async ensureIssueStatusExists(id: number): Promise<void> {
    const found = await this.issues.findIssueStatusById(id);
    if (!found) {
      throw new NotFoundException('Issue status not found');
    }
  }

  private async ensureCategoryExists(id: number): Promise<void> {
    const found = await this.issues.findCategoryById(id);
    if (!found) {
      throw new NotFoundException('Issue category not found');
    }
  }

  // Reject a duplicate name (case-insensitive). When `excludeId` is set,
  // the row being updated is ignored so it does not collide with itself.
  private async ensureCategoryNameIsUnique(
    name: string,
    excludeId?: number,
  ): Promise<void> {
    const existing = await this.issues.findCategoryByName(name, excludeId);
    if (existing) {
      throw new ConflictException(
        'Issue category with this name already exists',
      );
    }
  }

  // Reject a duplicate name (case-insensitive). When `excludeId` is set,
  // the row being updated is ignored so it does not collide with itself.
  private async ensureStatusNameIsUnique(
    name: string,
    excludeId?: number,
  ): Promise<void> {
    const existing = await this.issues.findIssueStatusByName(name, excludeId);
    if (existing) {
      throw new ConflictException('Issue status with this name already exists');
    }
  }

  // Reject a duplicate code (case-insensitive). When `excludeId` is set,
  // the row being updated is ignored so it does not collide with itself.
  private async ensureStatusCodeIsUnique(
    code: string,
    excludeId?: number,
  ): Promise<void> {
    const existing = await this.issues.findIssueStatusByCode(code, excludeId);
    if (existing) {
      throw new ConflictException('Issue status with this code already exists');
    }
  }

  private async ensureMeetingRequestExists(id: number): Promise<void> {
    const found = await this.issues.findMeetingRequestById(id);
    if (!found) {
      throw new NotFoundException('Meeting request not found');
    }
  }

  // Reject duplicate stakeholder ids or duplicate ordering, then check
  // that every selected stakeholder really is a government agency.
  private async ensureGovernmentAgenciesAreValid(
    agencies: CreateIssueGovernmentAgencyDto[],
  ): Promise<void> {
    const stakeholderIds = new Set<number>();
    const agencyOrders = new Set<number>();

    for (const agency of agencies) {
      if (stakeholderIds.has(agency.stakeholderId)) {
        throw new BadRequestException('Each agency can only be selected once');
      }
      if (agencyOrders.has(agency.agencyOrder)) {
        throw new BadRequestException(
          'Each agency order can only be used once',
        );
      }
      stakeholderIds.add(agency.stakeholderId);
      agencyOrders.add(agency.agencyOrder);
    }

    const foundAgencies = await this.issues.findGovernmentAgenciesByIds([
      ...stakeholderIds,
    ]);

    if (foundAgencies.length !== stakeholderIds.size) {
      throw new NotFoundException(
        'One or more government agencies were not found',
      );
    }
  }
}