import { Prisma, type PrismaClient } from '../../src/generated/prisma/client';
import { normalizeSeedName } from './issue-matrix.seed';
import { seedWorkingGroupPrefix } from './issue-number.seed';
import {
  applyMeetingPlan,
  buildMeetingPlan,
  validateMeetingSources,
  type MeetingSource,
  type ResolutionPlan,
} from './issue-progress-meetings.seed';

type IssueSource = {
  matchByContent?: boolean;
  sourceRef: string;
  issueNumber: string | null;
  workingGroup: string;
  ownerEmail: string | null;
  title: string;
  titleKh?: string | null;
  description: string;
  descriptionKh?: string | null;
  recommendation: string;
  recommendationKh?: string | null;
  category: string;
  statusCode: string;
  attachment?: string | null;
  agencies: { name: string; order: number }[];
};

type AnswerSource = {
  sourceRef: string;
  issueRef: string;
  authorEmail: string | null;
  issueStatusCode: string;
  resolutionRef?: string | null;
  indicators?: Prisma.JsonValue;
  indicatorsKh?: Prisma.JsonValue;
  progressSolution?: Prisma.JsonValue;
  progressSolutionKh?: Prisma.JsonValue;
  nextStep?: Prisma.JsonValue;
  nextStepKh?: Prisma.JsonValue;
  implementationChallenges?: Prisma.JsonValue;
  sourceOfVerification?: string | null;
  sourceOfVerificationKh?: string | null;
  linkToVerificationSource?: string | null;
  dateOfIssueSolution?: string | null;
  attachement?: string | null;
};

type MinistrySource = {
  ministryName: string;
  assignedUserEmail: string | null;
  attachment?: string | null;
  issueUpdates: AnswerSource[];
};

type ReportSource = {
  sourceRef: string;
  title: string;
  year: number;
  semester: 'S1' | 'S2';
  ownerEmail: string | null;
  status?: 'DRAFT';
  ministries: MinistrySource[];
};

export type IssueProgressSource = {
  schemaVersion: 1;
  exampleOnly: boolean;
  issues: IssueSource[];
  reports: ReportSource[];
  meetings?: MeetingSource[];
};

const answerJsonFields = [
  'indicators',
  'indicatorsKh',
  'progressSolution',
  'progressSolutionKh',
  'nextStep',
  'nextStepKh',
  'implementationChallenges',
] as const;
const answerTextFields = [
  'sourceOfVerification',
  'sourceOfVerificationKh',
  'linkToVerificationSource',
  'attachement',
] as const;
const issueKhFields = ['titleKh', 'descriptionKh', 'recommendationKh'] as const;

function object(
  value: unknown,
  keys: string[],
  label: string,
): asserts value is Record<string, unknown> {
  if (!value || typeof value !== 'object' || Array.isArray(value))
    throw new Error(`${label} must be an object`);
  for (const key of Object.keys(value))
    if (!keys.includes(key)) throw new Error(`${label}: unknown field ${key}`);
}

function text(
  value: unknown,
  label: string,
  max = 100000,
): asserts value is string {
  if (typeof value !== 'string' || !value.trim() || value.length > max)
    throw new Error(`${label} must contain 1-${max} characters`);
}

function optionalText(value: unknown, label: string, max?: number) {
  if (value != null) text(value, label, max);
}

function email(value: unknown, label: string, exampleOnly: boolean) {
  if (value === null && exampleOnly) return;
  text(value, label, 254);
  if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value))
    throw new Error(`${label} must be an existing account email`);
}

function unique(value: string, seen: Set<string>, label: string) {
  if (seen.has(value)) throw new Error(`Repeated ${label}: ${value}`);
  seen.add(value);
}

function rejectSampleText(value: unknown) {
  if (
    typeof value === 'string' &&
    /^(EXAMPLE\s*:|\[REPLACE_)/i.test(value.trim())
  )
    throw new Error(
      'Replace all EXAMPLE: answers and placeholders before enabling the source',
    );
  if (Array.isArray(value)) value.forEach(rejectSampleText);
  else if (value && typeof value === 'object')
    Object.values(value).forEach(rejectSampleText);
}

// Validate the entire document, including cross-references, before touching a database.
export function validateIssueProgressSource(
  value: unknown,
): asserts value is IssueProgressSource {
  object(
    value,
    ['schemaVersion', 'exampleOnly', 'issues', 'reports', 'meetings'],
    'Source',
  );
  if (value.schemaVersion !== 1 || typeof value.exampleOnly !== 'boolean')
    throw new Error('Use schemaVersion=1 and an explicit exampleOnly boolean');
  if (!Array.isArray(value.issues) || !Array.isArray(value.reports))
    throw new Error('issues and reports must be arrays');
  if (!value.exampleOnly) rejectSampleText(value);
  const refs = new Set<string>();
  const issueRefs = new Set<string>();
  const aliases = new Set<string>();
  const numbers = new Set<string>();
  for (const issue of value.issues as unknown[]) {
    object(
      issue,
      [
        'sourceRef',
        'issueNumber',
        'workingGroup',
        'ownerEmail',
        'matchByContent',
        'title',
        ...issueKhFields,
        'description',
        'recommendation',
        'category',
        'statusCode',
        'attachment',
        'agencies',
      ],
      'Issue',
    );
    for (const key of [
      'sourceRef',
      'workingGroup',
      'title',
      'category',
      'statusCode',
    ])
      text(issue[key], key, key === 'title' ? 200 : 255);
    unique(issue.sourceRef as string, refs, 'sourceRef');
    if (
      issue.matchByContent !== undefined &&
      typeof issue.matchByContent !== 'boolean'
    )
      throw new Error('matchByContent must be boolean');
    issueRefs.add(issue.sourceRef as string);
    for (const key of ['description', 'recommendation']) {
      if (typeof issue[key] !== 'string')
        throw new Error(
          `${key} must be a string; use an empty string for unavailable original content`,
        );
    }
    for (const key of issueKhFields)
      optionalText(issue[key], key, key === 'titleKh' ? 200 : undefined);
    const issueAliases = new Set(
      [issue.title, issue.titleKh]
        .filter(
          (alias): alias is string =>
            typeof alias === 'string' && Boolean(alias.trim()),
        )
        .map(normalizeSeedName),
    );
    for (const alias of issueAliases)
      unique(
        `${normalizeSeedName(issue.workingGroup as string)}:${alias}${issue.matchByContent ? ':' + JSON.stringify([issue.description, issue.recommendation].map(normalizeSeedName)) : ''}`,
        aliases,
        'working-group title',
      );
    if (issue.issueNumber !== null) {
      text(issue.issueNumber, 'issueNumber', 30);
      if (!/^WG\d{2}_\d{4,}$/.test(issue.issueNumber))
        throw new Error(
          'Use a canonical issueNumber such as WG01_0001, or null for a new issue',
        );
      unique(issue.issueNumber, numbers, 'issueNumber');
    }
    email(issue.ownerEmail, 'Issue ownerEmail', value.exampleOnly);
    optionalText(issue.attachment, 'attachment', 255);
    if (!Array.isArray(issue.agencies) || !issue.agencies.length)
      throw new Error('Each issue needs a primary agency');
    const agencyNames = new Set<string>();
    const orders = new Set<number>();
    for (const agency of issue.agencies as unknown[]) {
      object(agency, ['name', 'order'], 'Agency');
      text(agency.name, 'Agency name', 255);
      if (
        !Number.isInteger(agency.order) ||
        Number(agency.order) < 1 ||
        orders.has(Number(agency.order))
      )
        throw new Error('Agency orders must be unique positive integers');
      orders.add(Number(agency.order));
      unique(normalizeSeedName(agency.name), agencyNames, 'agency');
    }
    if (!orders.has(1)) throw new Error('agency order 1 is required');
  }
  const periods = new Set<string>();
  for (const report of value.reports as unknown[]) {
    object(
      report,
      [
        'sourceRef',
        'title',
        'year',
        'semester',
        'ownerEmail',
        'status',
        'ministries',
      ],
      'Report',
    );
    text(report.sourceRef, 'Report sourceRef', 255);
    unique(report.sourceRef, refs, 'sourceRef');
    text(report.title, 'Report title', 255);
    if (
      !Number.isInteger(report.year) ||
      Number(report.year) < 1900 ||
      Number(report.year) > 9999 ||
      !['S1', 'S2'].includes(String(report.semester))
    )
      throw new Error('Use a valid report year and S1 or S2');
    unique(
      `${String(report.year)}/${String(report.semester)}`,
      periods,
      'report period',
    );
    if (report.status !== undefined && report.status !== 'DRAFT')
      throw new Error('Only DRAFT report creation is supported');
    email(report.ownerEmail, 'Report ownerEmail', value.exampleOnly);
    if (!Array.isArray(report.ministries))
      throw new Error('ministries must be an array');
    const ministries = new Set<string>();
    for (const ministry of report.ministries as unknown[]) {
      object(
        ministry,
        ['ministryName', 'assignedUserEmail', 'attachment', 'issueUpdates'],
        'Ministry',
      );
      text(ministry.ministryName, 'ministryName', 255);
      unique(normalizeSeedName(ministry.ministryName), ministries, 'ministry');
      email(ministry.assignedUserEmail, 'assignedUserEmail', value.exampleOnly);
      optionalText(ministry.attachment, 'Ministry attachment', 500);
      if (!Array.isArray(ministry.issueUpdates))
        throw new Error('issueUpdates must be an array');
      const answers = new Set<string>();
      for (const answer of ministry.issueUpdates as unknown[]) {
        object(
          answer,
          [
            'sourceRef',
            'issueRef',
            'authorEmail',
            'issueStatusCode',
            'resolutionRef',
            ...answerJsonFields,
            ...answerTextFields,
            'dateOfIssueSolution',
          ],
          'Answer',
        );
        text(answer.sourceRef, 'Answer sourceRef', 255);
        unique(answer.sourceRef, refs, 'sourceRef');
        text(answer.issueRef, 'issueRef', 255);
        if (!issueRefs.has(answer.issueRef))
          throw new Error(`Unknown issueRef: ${answer.issueRef}`);
        unique(answer.issueRef, answers, 'answer for issue');
        email(answer.authorEmail, 'authorEmail', value.exampleOnly);
        optionalText(answer.resolutionRef, 'resolutionRef', 255);
        if (
          !['SOLVED', 'IN_PROGRESS', 'NOT_ADDRESSED'].includes(
            String(answer.issueStatusCode),
          )
        )
          throw new Error(
            'Answers require SOLVED, IN_PROGRESS or NOT_ADDRESSED',
          );
        for (const key of answerTextFields)
          optionalText(
            answer[key],
            key,
            key === 'attachement' ? 500 : undefined,
          );
        for (const key of answerJsonFields)
          if (answer[key] === undefined) continue;
          else if (typeof answer[key] === 'string' && !answer[key].trim())
            throw new Error(`${key}: use null instead of empty text`);
        if (answer.dateOfIssueSolution != null) {
          text(answer.dateOfIssueSolution, 'dateOfIssueSolution', 24);
          const date = new Date(answer.dateOfIssueSolution);
          if (
            !Number.isFinite(date.getTime()) ||
            date.toISOString() !== answer.dateOfIssueSolution
          )
            throw new Error(
              'dateOfIssueSolution must be an ISO UTC timestamp such as 2026-06-30T00:00:00.000Z',
            );
        }
      }
    }
  }
  const resolutionRefs = validateMeetingSources(
    value.meetings ?? [],
    issueRefs,
    refs,
    value.exampleOnly,
    { object, text, optionalText, email, unique },
  );
  for (const report of value.reports as ReportSource[]) {
    for (const ministry of report.ministries) {
      for (const answer of ministry.issueUpdates) {
        if (answer.resolutionRef == null) continue;
        const resolution = resolutionRefs.get(answer.resolutionRef);
        if (!resolution || resolution.issueRef !== answer.issueRef)
          throw new Error(
            `Unknown resolutionRef or wrong issue: ${answer.resolutionRef}`,
          );
      }
    }
  }
}

async function loadReferences(tx: Prisma.TransactionClient) {
  const [groups, categories, statuses, users, issues, khRows] =
    await Promise.all([
      tx.stakeholder.findMany({
        where: {
          active: true,
          deletedAt: null,
          stakeholderType: { deletedAt: null },
        },
        select: {
          id: true,
          name: true,
          khName: true,
          description: true,
          stakeholderType: { select: { name: true } },
        },
      }),
      tx.categories.findMany({
        where: { deletedAt: null },
        select: { id: true, name: true, khName: true },
      }),
      tx.issueStatuses.findMany({
        where: { deletedAt: null },
        select: { id: true, code: true },
      }),
      tx.user.findMany({
        where: { isActive: true, deletedAt: null },
        select: {
          id: true,
          email: true,
          roles: {
            where: { role: { guardName: 'web', deletedAt: null } },
            select: { role: { select: { name: true } } },
          },
          stakeholders: { select: { stakeholderId: true } },
        },
      }),
      tx.issues.findMany({ include: { governmentAgencies: true } }),
      tx.$queryRaw<
        { id: number; titleKh: string | null }[]
      >`SELECT id, title_kh AS "titleKh" FROM issues`,
    ]);
  return { groups, categories, statuses, users, issues, khRows };
}

function one<T>(matches: T[], label: string): T {
  if (matches.length !== 1) throw new Error(`Missing or ambiguous ${label}`);
  return matches[0];
}

function named<T extends { name: string; khName?: string | null }>(
  rows: T[],
  name: string,
  label: string,
) {
  return one(
    rows.filter((row) =>
      [row.name, row.khName].some(
        (value) =>
          value && normalizeSeedName(value) === normalizeSeedName(name),
      ),
    ),
    `${label}: ${name}`,
  );
}

type References = Awaited<ReturnType<typeof loadReferences>>;
function account(
  refs: References,
  email: string | null,
  roles: string[],
  stakeholderId?: number,
) {
  return one(
    refs.users.filter(
      (user) =>
        user.email === email &&
        user.roles.some(({ role }) => roles.includes(role.name)) &&
        (stakeholderId === undefined ||
          user.stakeholders.some(
            (link) => link.stakeholderId === stakeholderId,
          )),
    ),
    `eligible account: ${email ?? '(not supplied)'}`,
  ).id;
}

type IssuePlan = {
  source: IssueSource;
  id: number | null;
  number: string;
  groupId: number;
  userId: number;
  categoryId: number;
  statusId: number;
  agencies: { stakeholderId: number; agencyOrder: number }[];
  missingAgencies: { stakeholderId: number; agencyOrder: number }[];
  primaryRequestId: number | null;
  updatedAt: Date | null;
};
type AnswerPlan = {
  source: AnswerSource;
  issue: IssuePlan;
  statusId: number;
  userId: number;
  existingId: number | null;
  missingLink: boolean;
  resolution: ResolutionPlan | null;
  missingResolutionLink: boolean;
  updatedAt: Date | null;
};
type MinistryPlan = {
  source: MinistrySource;
  ministryId: number;
  userId: number;
  existingId: number | null;
  answers: AnswerPlan[];
};
type ReportPlan = {
  source: ReportSource;
  existingId: number | null;
  userId: number;
  ministries: MinistryPlan[];
};

function sameJson(left: unknown, right: unknown): boolean {
  if (left == null || right == null) return left == null && right == null;
  if (Array.isArray(left) || Array.isArray(right))
    return (
      Array.isArray(left) &&
      Array.isArray(right) &&
      left.length === right.length &&
      left.every((item, index) => sameJson(item, right[index]))
    );
  if (typeof left === 'object' && typeof right === 'object') {
    const a = left as Record<string, unknown>;
    const b = right as Record<string, unknown>;
    return (
      Object.keys(a).length === Object.keys(b).length &&
      Object.keys(a).every(
        (key) => Object.hasOwn(b, key) && sameJson(a[key], b[key]),
      )
    );
  }
  return left === right;
}

async function buildPlan(
  tx: Prisma.TransactionClient,
  source: IssueProgressSource,
  addOnlyToExistingReports = false,
  preserveExistingResolutions = false,
  finalizeSeedMeetings = false,
) {
  const refs = await loadReferences(tx);
  const statusId = (code: string) =>
    one(
      refs.statuses.filter((row) => row.code === code),
      `issue status: ${code}`,
    ).id;
  const khById = new Map(refs.khRows.map((row) => [row.id, row.titleKh]));
  const planned: IssuePlan[] = [];
  const reserved = new Set(
    refs.issues
      .map((issue) => issue.numberIssue)
      .filter((value) => value !== null),
  );
  const owners = new Map<string, number>();
  for (const group of refs.groups.filter(
    (row) => row.stakeholderType.name === 'Private Sector',
  )) {
    const prefix = seedWorkingGroupPrefix(group);
    if (owners.has(prefix))
      throw new Error(`Ambiguous working-group prefix: ${prefix}`);
    owners.set(prefix, group.id);
  }
  const selectedIds = new Set<number>();
  const resolvedAliases = new Set<string>();
  for (const issue of source.issues) {
    const group = named(
      refs.groups.filter(
        (row) => row.stakeholderType.name === 'Private Sector',
      ),
      issue.workingGroup,
      'working group',
    );
    const prefix = seedWorkingGroupPrefix(group);
    const aliases = [issue.title, issue.titleKh]
      .filter((value): value is string => Boolean(value?.trim()))
      .map(normalizeSeedName);
    for (const alias of new Set(aliases))
      unique(
        `${group.id}:${alias}${issue.matchByContent ? ':' + JSON.stringify([issue.description, issue.recommendation].map(normalizeSeedName)) : ''}`,
        resolvedAliases,
        'resolved working-group title',
      );
    const byTitle = refs.issues.filter(
      (row) =>
        row.stakeholderId === group.id &&
        (!issue.matchByContent ||
          (normalizeSeedName(row.description ?? '') ===
            normalizeSeedName(issue.description) &&
            normalizeSeedName(row.recommendation ?? '') ===
              normalizeSeedName(issue.recommendation))) &&
        [row.title, khById.get(row.id)].some(
          (value) => value && aliases.includes(normalizeSeedName(value)),
        ),
    );
    const byNumber = issue.issueNumber
      ? refs.issues.find((row) => row.numberIssue === issue.issueNumber)
      : undefined;
    // Same-title sub-issues are safe only when every existing candidate is
    // accounted for by a complete content match in this reviewed source.
    if (issue.matchByContent) {
      const unknown = refs.issues.some(
        (row) =>
          row.stakeholderId === group.id &&
          [row.title, khById.get(row.id)].some(
            (value) => value && aliases.includes(normalizeSeedName(value)),
          ) &&
          !source.issues.some(
            (candidate) =>
              candidate.matchByContent &&
              normalizeSeedName(candidate.workingGroup) ===
                normalizeSeedName(issue.workingGroup) &&
              normalizeSeedName(candidate.description) ===
                normalizeSeedName(row.description ?? '') &&
              normalizeSeedName(candidate.recommendation) ===
                normalizeSeedName(row.recommendation ?? ''),
          ),
      );
      if (unknown)
        throw new Error(`Ambiguous same-title issue: ${issue.sourceRef}`);
    }
    if (
      byTitle.length > 1 ||
      (byNumber &&
        (byNumber.stakeholderId !== group.id ||
          !byTitle.some((row) => row.id === byNumber.id)))
    )
      throw new Error(`Conflicting issue number/title: ${issue.sourceRef}`);
    const existing = byNumber ?? byTitle[0];
    if (existing?.deletedAt)
      throw new Error(
        `Matching issue is deleted; not restored: ${issue.sourceRef}`,
      );
    if (existing && selectedIds.has(existing.id))
      throw new Error(`Two source issues match database issue ${existing.id}`);
    if (existing) selectedIds.add(existing.id);
    if (
      existing &&
      issue.issueNumber &&
      existing.numberIssue !== issue.issueNumber
    )
      throw new Error(
        `Existing issue number differs: ${issue.sourceRef}; correct the source, not the database`,
      );
    let number = existing?.numberIssue ?? '';
    if (!existing) {
      const suffixes = [...reserved]
        .filter((value) => value.startsWith(prefix))
        .map((value) => {
          const suffix = value.slice(prefix.length);
          if (!/^\d+$/.test(suffix))
            throw new Error(`Invalid existing issue number: ${value}`);
          return Number(suffix);
        });
      number = `${prefix}${String(Math.max(0, ...suffixes) + 1).padStart(4, '0')}`;
      if (issue.issueNumber && issue.issueNumber !== number)
        throw new Error(
          `New issue ${issue.sourceRef} would receive ${number}; set issueNumber=null to generate it`,
        );
      reserved.add(number);
    }
    const agencies = issue.agencies.map((agency) => ({
      stakeholderId: named(
        refs.groups.filter((row) => row.stakeholderType.name === 'Ministry'),
        agency.name,
        'agency',
      ).id,
      agencyOrder: agency.order,
    }));
    if (
      new Set(agencies.map((agency) => agency.stakeholderId)).size !==
      agencies.length
    )
      throw new Error(
        `Agency aliases resolve to the same ministry: ${issue.sourceRef}`,
      );
    const missingAgencies = agencies.filter((agency) => {
      const links = existing?.governmentAgencies ?? [];
      if (
        links.some(
          (link) =>
            (link.agencyOrder === agency.agencyOrder &&
              link.stakeholderId !== agency.stakeholderId) ||
            (link.stakeholderId === agency.stakeholderId &&
              link.agencyOrder !== agency.agencyOrder),
        )
      )
        throw new Error(
          `Existing agency assignment conflicts: ${issue.sourceRef}`,
        );
      return !links.some(
        (link) =>
          link.stakeholderId === agency.stakeholderId &&
          link.agencyOrder === agency.agencyOrder,
      );
    });
    planned.push({
      source: issue,
      id: existing?.id ?? null,
      number,
      groupId: group.id,
      userId: account(refs, issue.ownerEmail, ['private_sector'], group.id),
      categoryId: named(refs.categories, issue.category, 'category').id,
      statusId: statusId(issue.statusCode),
      agencies,
      missingAgencies,
      primaryRequestId: existing?.meetingRequestId ?? null,
      updatedAt: existing?.updatedAt ?? null,
    });
  }

  const issuesByRef = new Map(
    planned.map((item) => [item.source.sourceRef, item]),
  );
  const reports: ReportPlan[] = [];
  const meetings = await buildMeetingPlan(tx, source.meetings ?? [], {
    finalizeSeedMeetings,
    preserveExistingResolutions,
    issues: planned,
    ministryId: (name) =>
      named(
        refs.groups.filter((row) => row.stakeholderType.name === 'Ministry'),
        name,
        'meeting ministry',
      ).id,
    authorId: (email, ministryId) =>
      account(refs, email, ['ministry'], ministryId),
    statusId,
    sameJson,
  });
  const resolutionsByRef = new Map(
    meetings
      .flatMap((meeting) => meeting.resolutions)
      .map((resolution) => [resolution.source.sourceRef, resolution]),
  );
  for (const report of source.reports) {
    const matches = await tx.progressReport.findMany({
      where: { year: report.year, semester: report.semester },
    });
    const active = matches.filter((item) => !item.deletedAt);
    if (active.length > 1)
      throw new Error(`Ambiguous active report: ${report.sourceRef}`);
    const existing = active[0];
    if (
      existing &&
      (existing.title !== report.title ||
        (!addOnlyToExistingReports && existing.status !== 'DRAFT'))
    )
      throw new Error(
        `Existing report must have the same title and be DRAFT: ${report.sourceRef}`,
      );
    if (
      !existing &&
      matches.some((item) => item.deletedAt && item.title === report.title)
    )
      throw new Error(
        `Matching report is deleted; not restored: ${report.sourceRef}`,
      );
    const reportPlan: ReportPlan = {
      source: report,
      existingId: existing?.id ?? null,
      userId: account(refs, report.ownerEmail, ['admin', 'cdc_g-psf']),
      ministries: [],
    };
    const resolvedMinistries = new Set<string>();
    for (const ministry of report.ministries) {
      const ministryId = named(
        refs.groups.filter((row) => row.stakeholderType.name === 'Ministry'),
        ministry.ministryName,
        'ministry',
      ).id;
      unique(String(ministryId), resolvedMinistries, 'resolved ministry');
      const userId = account(
        refs,
        ministry.assignedUserEmail,
        ['ministry'],
        ministryId,
      );
      const assignment = existing
        ? await tx.progressReportMinistry.findUnique({
            where: {
              progressReportId_ministryId: {
                progressReportId: existing.id,
                ministryId,
              },
            },
          })
        : null;
      if (
        assignment &&
        (assignment.deletedAt ||
          (!addOnlyToExistingReports &&
            !['REQUESTED', 'PREPARING'].includes(assignment.status)))
      )
        throw new Error(
          `Existing ministry assignment cannot be changed in this Draft-only seed: ${ministry.ministryName}`,
        );
      if (
        assignment &&
        !addOnlyToExistingReports &&
        ministry.attachment !== undefined &&
        assignment.attachment !== ministry.attachment
      )
        throw new Error(
          `Existing ministry attachment differs: ${ministry.ministryName}`,
        );
      const ministryPlan: MinistryPlan = {
        source: ministry,
        ministryId,
        userId,
        existingId: assignment?.id ?? null,
        answers: [],
      };
      for (const answer of ministry.issueUpdates) {
        const issue = issuesByRef.get(answer.issueRef)!;
        if (
          !issue.agencies.some(
            (agency) =>
              agency.stakeholderId === ministryId && agency.agencyOrder === 1,
          )
        )
          throw new Error(
            `Answer ministry must be the primary agency: ${answer.sourceRef}`,
          );
        const authorId = account(
          refs,
          answer.authorEmail,
          ['ministry'],
          ministryId,
        );
        const answerStatusId = statusId(answer.issueStatusCode);
        const resolution = answer.resolutionRef
          ? resolutionsByRef.get(answer.resolutionRef)!
          : null;
        if (
          resolution &&
          (resolution.issue !== issue || resolution.ministryId !== ministryId)
        )
          throw new Error(
            `Resolution must belong to the same issue and ministry: ${answer.sourceRef}`,
          );
        const saved =
          existing && issue.id !== null
            ? await tx.progressReportUpdate.findUnique({
                where: {
                  progressReportId_ministryId_issueId: {
                    progressReportId: existing.id,
                    ministryId,
                    issueId: issue.id,
                  },
                },
              })
            : null;
        const link = saved
          ? await tx.progressReportUpdateIssue.findUnique({
              where: {
                progressReportUpdateId_ministryId_issueId: {
                  progressReportUpdateId: saved.id,
                  ministryId,
                  issueId: issue.id!,
                },
              },
            })
          : null;
        if (saved?.deletedAt)
          throw new Error(
            `Existing answer is deleted; not restored: ${answer.sourceRef}`,
          );
        if (saved && !addOnlyToExistingReports) {
          if (
            resolution &&
            saved.issueResolveId != null &&
            saved.issueResolveId !== resolution.id
          )
            throw new Error(
              `Existing answer has another resolution; not overwritten: ${answer.sourceRef}`,
            );
          if (
            saved.deletedAt ||
            saved.issueStatusId !== answerStatusId ||
            saved.userId !== authorId
          )
            throw new Error(
              `Existing answer conflicts; not overwritten: ${answer.sourceRef}`,
            );
          const [kh] = await tx.$queryRaw<
            {
              indicatorsKh: Prisma.JsonValue;
              sourceOfVerificationKh: string | null;
            }[]
          >`SELECT indicators_kh AS "indicatorsKh", source_of_verification_kh AS "sourceOfVerificationKh" FROM progress_report_updates WHERE id = ${saved.id}`;
          const comparable = {
            ...saved,
            nextStep: saved.next_step,
            ...kh,
            dateOfIssueSolution:
              saved.dateOfIssueSolution?.toISOString() ?? null,
          };
          for (const key of [
            ...answerJsonFields,
            ...answerTextFields,
            'dateOfIssueSolution',
          ] as const) {
            if (
              Object.hasOwn(answer, key) &&
              !sameJson(answer[key], comparable[key])
            )
              throw new Error(
                `Existing answer ${key} differs; not overwritten: ${answer.sourceRef}`,
              );
          }
        }
        ministryPlan.answers.push({
          source: answer,
          issue,
          statusId: answerStatusId,
          userId: authorId,
          existingId: saved?.id ?? null,
          missingLink: !link && !(saved && addOnlyToExistingReports),
          resolution,
          missingResolutionLink: Boolean(
            resolution &&
            saved &&
            saved.issueResolveId == null &&
            !addOnlyToExistingReports,
          ),
          updatedAt: saved?.updatedAt ?? null,
        });
      }
      reportPlan.ministries.push(ministryPlan);
    }
    reports.push(reportPlan);
  }
  return { issues: planned, reports, meetings };
}

const json = (value: Prisma.JsonValue | undefined) =>
  value == null ? Prisma.DbNull : value;

export async function seedIssueProgress(
  database: PrismaClient,
  source: unknown,
  apply = false,
  options: {
    addOnlyToExistingReports?: boolean;
    finalizeSeedMeetings?: boolean;
    preserveExistingResolutions?: boolean;
  } = {},
) {
  validateIssueProgressSource(source);
  if (source.exampleOnly) {
    if (apply)
      throw new Error(
        'Cannot apply an exampleOnly source. Supply verified answers and accounts in a separate enabled JSON file.',
      );
    return {
      mode: 'preview',
      exampleOnly: true,
      message:
        'Example disabled: syntax and issueRef links checked; database references were not checked and no writes were made.',
    };
  }
  return database.$transaction(
    async (tx) => {
      if (apply) {
        await tx.$queryRaw`SELECT 1 AS locked FROM pg_advisory_xact_lock(20261031, 34)`;
        await tx.$executeRaw`LOCK TABLE "issues", "government_agency_issue", "progress_reports", "progress_report_ministries", "progress_report_updates", "progress_report_update_issues", "meeting_requests", "government_agency_meeting_request", "issue_meeting_requests", "meetings", "meeting_meeting_requests", "meeting_summaries", "meeting_summary_meetings", "issue_resolves", "issue_resolve_agencies" IN SHARE ROW EXCLUSIVE MODE`;
      } else await tx.$executeRaw`SET TRANSACTION READ ONLY`;
      const plan = await buildPlan(
        tx,
        source,
        options.addOnlyToExistingReports,
        options.preserveExistingResolutions,
        options.finalizeSeedMeetings,
      );
      const summary = {
        mode: apply ? 'apply' : 'preview',
        exampleOnly: false,
        issues: plan.issues.map((item) => ({
          sourceRef: item.source.sourceRef,
          action: item.id === null ? 'create' : 'keep',
          issueId: item.id,
          issueNumber: item.number || null,
          agencyLinksToAdd: item.missingAgencies.length,
        })),
        reports: plan.reports.map((item) => ({
          sourceRef: item.source.sourceRef,
          action:
            item.existingId === null
              ? 'create-draft'
              : options.addOnlyToExistingReports
                ? 'keep-existing'
                : 'keep-draft',
          reportId: item.existingId,
          ministriesToAdd: item.ministries.filter(
            (ministry) => ministry.existingId === null,
          ).length,
          answersToAdd: item.ministries
            .flatMap((ministry) => ministry.answers)
            .filter((answer) => answer.existingId === null).length,
          answerLinksToAdd: item.ministries
            .flatMap((ministry) => ministry.answers)
            .filter((answer) => answer.missingLink).length,
          resolutionLinksToAdd: item.ministries
            .flatMap((ministry) => ministry.answers)
            .filter((answer) => answer.missingResolutionLink).length,
        })),
        meetings: plan.meetings.map((item) => ({
          sourceRef: item.source.sourceRef,
          requestId: item.requestId,
          requestsToSubmit: Number(
            item.submitRequest || (item.finalize && !item.requestId),
          ),
          summariesToComplete: Number(
            item.completeSummary || (item.finalize && !item.summaryId),
          ),
          summariesToShare: Number(
            item.shareSummary || (item.finalize && !item.summaryId),
          ),
          meetingId: item.meetingId,
          assignedMeetingDate: item.source.meetingDate,
          calendarUpdates: Number(item.calendarUpdate),
          meetingTextChange: item.renameMeeting
            ? {
                from: {
                  title: item.source.previousTitle,
                  description: item.source.previousDescription,
                },
                to: {
                  title: item.source.title,
                  description: item.source.description,
                },
              }
            : null,
          summaryId: item.summaryId,
          requestsToAdd: item.requestId === null ? 1 : 0,
          requestTitlesToRename: item.previousRequestTitle ? 1 : 0,
          requestDescriptionsToUpdate: item.previousRequestDescription ? 1 : 0,
          meetingsToAdd: item.meetingId === null ? 1 : 0,
          summariesToAdd: item.summaryId === null ? 1 : 0,
          issueLinksToAdd: item.issues.filter(
            (issue) => issue.missingRequestLink,
          ).length,
          primaryIssueLinksToAdd: item.issues.filter(
            (issue) => issue.setPrimary,
          ).length,
          resolutionsToAdd: item.resolutions.filter(
            (resolution) => resolution.id === null,
          ).length,
          resolutionAgencyLinksToAdd: item.resolutions.reduce(
            (total, resolution) => total + resolution.missingAgencyIds.length,
            0,
          ),
          requestAgencyLinksToAdd: item.missingRequestAgency ? 1 : 0,
          meetingRequestLinksToAdd: item.missingMeetingLink ? 1 : 0,
          summaryMeetingLinksToAdd: item.missingSummaryLink ? 1 : 0,
        })),
      };
      if (!apply) return summary;
      for (const item of plan.issues) {
        if (item.id === null) {
          const saved = await tx.issues.create({
            data: {
              title: item.source.title,
              description: item.source.description,
              recommendation: item.source.recommendation,
              numberIssue: item.number,
              stakeholderId: item.groupId,
              userId: item.userId,
              categoryId: item.categoryId,
              issueStatusId: item.statusId,
              attachment: item.source.attachment,
            },
            select: { id: true, updatedAt: true },
          });
          item.id = saved.id;
          item.updatedAt = saved.updatedAt;
          summary.issues.find(
            (row) => row.sourceRef === item.source.sourceRef,
          )!.issueId = saved.id;
          // Additive Khmer columns do not require regenerating the Prisma client.
          await tx.$executeRaw`UPDATE issues SET title_kh = ${item.source.titleKh ?? null}, description_kh = ${item.source.descriptionKh ?? null}, recommendation_kh = ${item.source.recommendationKh ?? null} WHERE id = ${saved.id}`;
        }
        if (item.missingAgencies.length)
          await tx.governmentAgencyIssue.createMany({
            data: item.missingAgencies.map((agency) => ({
              ...agency,
              issueId: item.id!,
            })),
          });
      }
      await applyMeetingPlan(tx, plan.meetings);
      for (const item of plan.meetings) {
        Object.assign(
          summary.meetings.find(
            (row) => row.sourceRef === item.source.sourceRef,
          )!,
          {
            requestId: item.requestId,
            meetingId: item.meetingId,
            summaryId: item.summaryId,
          },
        );
      }
      for (const item of plan.reports) {
        const reportId =
          item.existingId ??
          (
            await tx.progressReport.create({
              data: {
                title: item.source.title,
                year: item.source.year,
                semester: item.source.semester,
                status: 'DRAFT',
                userId: item.userId,
              },
              select: { id: true },
            })
          ).id;
        summary.reports.find(
          (row) => row.sourceRef === item.source.sourceRef,
        )!.reportId = reportId;
        for (const ministry of item.ministries) {
          if (ministry.existingId === null)
            await tx.progressReportMinistry.create({
              data: {
                progressReportId: reportId,
                ministryId: ministry.ministryId,
                userId: ministry.userId,
                status: 'PREPARING',
                attachment: ministry.source.attachment,
                issues: ministry.answers.length,
              },
            });
          for (const answer of ministry.answers) {
            if (answer.existingId !== null) {
              if (answer.missingResolutionLink) {
                const linked = await tx.progressReportUpdate.updateMany({
                  where: {
                    id: answer.existingId,
                    deletedAt: null,
                    issueResolveId: null,
                  },
                  data: {
                    issueResolveId: answer.resolution!.id!,
                    updatedAt: answer.updatedAt ?? new Date(),
                  },
                });
                if (linked.count !== 1)
                  throw new Error(
                    'Answer resolution link changed; preview the batch again',
                  );
              }
              if (answer.missingLink)
                await tx.progressReportUpdateIssue.create({
                  data: {
                    progressReportUpdateId: answer.existingId,
                    ministryId: ministry.ministryId,
                    issueId: answer.issue.id!,
                  },
                });
              continue;
            }
            const data = answer.source;
            const saved = await tx.progressReportUpdate.create({
              data: {
                progressReportId: reportId,
                ministryId: ministry.ministryId,
                issueId: answer.issue.id!,
                issueStatusId: answer.statusId,
                userId: answer.userId,
                issueResolveId: answer.resolution?.id ?? null,
                indicators: json(data.indicators),
                progressSolution: json(data.progressSolution),
                next_step: json(data.nextStep),
                implementationChallenges: json(data.implementationChallenges),
                sourceOfVerification: data.sourceOfVerification,
                linkToVerificationSource: data.linkToVerificationSource,
                dateOfIssueSolution: data.dateOfIssueSolution
                  ? new Date(data.dateOfIssueSolution)
                  : null,
                attachement: data.attachement,
              },
              select: { id: true },
            });
            if (
              data.indicatorsKh != null ||
              data.sourceOfVerificationKh != null
            )
              await tx.$executeRaw`UPDATE progress_report_updates SET indicators_kh = ${data.indicatorsKh == null ? null : JSON.stringify(data.indicatorsKh)}::jsonb, source_of_verification_kh = ${data.sourceOfVerificationKh ?? null} WHERE id = ${saved.id}`;
            if (data.progressSolutionKh != null || data.nextStepKh != null)
              await tx.$executeRaw`UPDATE progress_report_updates SET progress_solution_kh = ${data.progressSolutionKh == null ? null : JSON.stringify(data.progressSolutionKh)}::jsonb, next_step_kh = ${data.nextStepKh == null ? null : JSON.stringify(data.nextStepKh)}::jsonb WHERE id = ${saved.id}`;
            await tx.progressReportUpdateIssue.create({
              data: {
                progressReportUpdateId: saved.id,
                ministryId: ministry.ministryId,
                issueId: answer.issue.id!,
              },
            });
          }
        }
      }
      return summary;
    },
    {
      isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
      maxWait: 10000,
      timeout: 120000,
    },
  );
}