import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import type {
  Prisma,
  PrismaClient,
  RgcDecisionStatus,
} from '../../src/generated/prisma/client';
import { normalizeSeedName } from './issue-matrix.seed';

export type SourceRgcDecision = {
  sourceNo: number;
  provenance: { workbook: string; sheet: string; row: number };
  workingGroup: string;
  issueTitle: string;
  issueDescription: string;
  recommendation: string;
  decision: string;
  decisionKh?: string | null;
  allowEnglishOnly?: boolean;
  matchingDecisions: string[];
  ministry: string;
  category: string;
  meetingDate: string;
  status: RgcDecisionStatus;
  focalPerson: string;
  focalPersonKh?: string | null;
  verificationSource: string | null;
  verificationSourceKh?: string | null;
  verificationLink: string | null;
  relatedAgencies: string[];
  holdReason?: string;
  progressAnswer?: {
    indicators?: string | null;
    indicatorsKh?: string | null;
    progressSolution: string | null;
    progressSolutionKh?: string | null;
    implementationChallenges: string | null;
    nextStep: string | null;
    nextStepKh?: string | null;
    dateOfIssueSolution?: string | null;
  };
};

export type RgcSeedSource = {
  batch: string;
  plenary: { name: string; meetingDate: string; matchingNames: string[] };
  decisions: SourceRgcDecision[];
  progressReport?: { year: number; semester: 'S1' | 'S2'; authorEmail?: string };
};

type RgcSeedOptions = {
  submitImported?: boolean;
  addMissingMinistries?: boolean;
  includeAllMinistries?: boolean;
  addProgressAnswers?: boolean;
  addMissingReportMinistries?: boolean;
};

// Add missing historical answers only. Never update an existing report,
// assignment or answer. Missing assignments are opt-in and start Preparing.
export async function seedRgcProgressAnswers(
  tx: Prisma.TransactionClient,
  source: RgcSeedSource,
  decisionIds: Map<number, number>,
  apply: boolean,
  plannedMinistries = new Map<number, number>(),
  addMissingReportMinistries = false,
  decisionAuthorId?: number,
) {
  const period = source.progressReport;
  if (!period) return { planned: 0, created: 0, kept: 0 };
  const reports = await tx.progressReport.findMany({
    where: { year: period.year, semester: period.semester, deletedAt: null },
    select: { id: true },
  });
  if (reports.length !== 1)
    throw new Error('Expected one active progress report for the RGC answers');
  const reportId = reports[0].id;
  const result = { planned: 0, created: 0, kept: 0 };
  for (const row of source.decisions) {
    if (!row.progressAnswer || row.holdReason) continue;
    const decisionId = decisionIds.get(row.sourceNo);
    if (!decisionId && (apply || !plannedMinistries.has(row.sourceNo)))
      throw new Error(
        `Create decision ${row.sourceNo} before previewing its progress answer`,
      );
    const decision = decisionId
      ? await tx.plenaryRgcDecision.findFirst({
          where: { id: decisionId, deletedAt: null },
          select: { stakeholderId: true },
        })
      : { stakeholderId: plannedMinistries.get(row.sourceNo)! };
    if (!decision)
      throw new Error(`Decision ${row.sourceNo} is missing or deleted`);
    const ministryId = decision.stakeholderId;
    const assignment = await tx.progressReportMinistry.findFirst({
      where: { progressReportId: reportId, ministryId, deletedAt: null },
      select: { id: true },
    });
    // CDC is an agency without a ministry login. Reuse the verified decision
    // author; never create a login or grant a stakeholder membership for it.
    const author = source.batch === 'real-cdc-19'
      ? (decisionAuthorId ? { id: decisionAuthorId } : null)
      : period.authorEmail ? await tx.user.findFirst({
      where: {
        email: period.authorEmail,
        isActive: true,
        deletedAt: null,
        stakeholders: { some: { stakeholderId: ministryId } },
      },
      select: { id: true },
    }) : null;
    const statuses = await tx.issueStatuses.findMany({
      where: { code: row.status, deletedAt: null },
      select: { id: true },
    });
    if (
      (!assignment && !addMissingReportMinistries) ||
      !author ||
      statuses.length !== 1
    )
      throw new Error(
        `Missing assignment, ministry author or status for RGC ${row.sourceNo}`,
      );
    if (!assignment) {
      const deletedAssignment = await tx.progressReportMinistry.findFirst({
        where: { progressReportId: reportId, ministryId },
        select: { id: true },
      });
      if (deletedAssignment)
        throw new Error(
          `Deleted ministry assignment for RGC ${row.sourceNo}; review before importing`,
        );
      if (apply)
        await tx.progressReportMinistry.create({
          data: {
            progressReportId: reportId,
            ministryId,
            userId: author.id,
            status: 'PREPARING',
          },
        });
    }
    const existing = decisionId
      ? await tx.progressReportUpdatePlenaryDecision.findUnique({
          where: {
            progressReportId_ministryId_plenaryDecisionId: {
              progressReportId: reportId,
              ministryId,
              plenaryDecisionId: decisionId,
            },
          },
          include: { progressReportUpdate: true },
        })
      : null;
    if (existing) {
      if (existing.deletedAt || existing.progressReportUpdate.deletedAt)
        throw new Error(
          `RGC ${row.sourceNo} has a deleted answer; restore it manually first`,
        );
      result.kept++;
      continue;
    }
    result.planned++;
    if (!apply) continue;
    const answer = await tx.progressReportUpdate.create({
      data: {
        progressReportId: reportId,
        ministryId,
        userId: author.id,
        issueStatusId: statuses[0].id,
        ...(row.progressAnswer.indicators == null
          ? {}
          : { indicators: row.progressAnswer.indicators }),
        ...(row.progressAnswer.progressSolution == null
          ? {}
          : { progressSolution: row.progressAnswer.progressSolution }),
        ...(row.progressAnswer.implementationChallenges == null
          ? {}
          : {
              implementationChallenges:
                row.progressAnswer.implementationChallenges,
            }),
        ...(row.progressAnswer.nextStep == null
          ? {}
          : { next_step: row.progressAnswer.nextStep }),
        // New bilingual sources explicitly provide the optional Khmer answer keys.
        // Keep legacy source behavior unchanged.
        sourceOfVerification:
          'progressSolutionKh' in row.progressAnswer
            ? row.verificationSource
            : row.verificationSourceKh || row.verificationSource,
        linkToVerificationSource: row.verificationLink,
        // Only use exact source dates approved for import.
        dateOfIssueSolution: row.progressAnswer.dateOfIssueSolution
          ? parseDate(row.progressAnswer.dateOfIssueSolution)
          : null,
        progressReportUpdatePlenaryDecisions: {
          create: {
            progressReportId: reportId,
            ministryId,
            plenaryDecisionId: decisionId!,
          },
        },
      },
      select: { id: true },
    });
    if (row.verificationSourceKh)
      await tx.$executeRaw`UPDATE progress_report_updates SET source_of_verification_kh = ${row.verificationSourceKh} WHERE id = ${answer.id}`;
    const data = row.progressAnswer;
    if (
      data.indicatorsKh != null ||
      data.progressSolutionKh != null ||
      data.nextStepKh != null
    )
      await tx.$executeRaw`UPDATE progress_report_updates
        SET indicators_kh = ${data.indicatorsKh == null ? null : JSON.stringify(data.indicatorsKh)}::jsonb,
            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 = ${answer.id}`;
    result.created++;
  }
  return result;
}

type RgcSeedReferences = {
  actors: { id: number }[];
  ministries: { id: number; name: string }[];
  categories: { id: number; name: string; khName: string | null }[];
  plenaries: {
    id: number;
    name: string;
    meetingDate: Date;
    deletedAt: Date | null;
    ministries: { stakeholderId: number }[];
  }[];
  existing: {
    id: number;
    stakeholderId: number;
    meetingDate: Date;
    decision: string;
    plenaryId: number | null;
    decisionKh?: string | null;
    categoryId?: number;
    status?: RgcDecisionStatus;
    userId: number;
    submittedToCdcAt: Date | null;
    deletedAt: Date | null;
  }[];
};

// Compare rich-text database content with plain workbook text, never rewriting it.
export function normalizeRgcSeedDecision(value: string) {
  const plain = value
    .replace(/<[^>]+>/g, ' ')
    .replace(/&#(x[\da-f]+|\d+);/gi, (_, code: string) => {
      const point =
        code[0].toLowerCase() === 'x'
          ? parseInt(code.slice(1), 16)
          : Number(code);
      return point > 0 && point <= 0x10ffff ? String.fromCodePoint(point) : '';
    })
    .replace(
      /&(nbsp|amp|lt|gt|quot|apos);/gi,
      (entity) =>
        ({
          '&nbsp;': ' ',
          '&amp;': '&',
          '&lt;': '<',
          '&gt;': '>',
          '&quot;': '"',
          '&apos;': "'",
        })[entity.toLowerCase()] ?? entity,
    )
    .replace(/[០-៩]/g, (digit) => String('០១២៣៤៥៦៧៨៩'.indexOf(digit)));
  return normalizeSeedName(plain);
}

function parseDate(value: string) {
  const date = new Date(`${value}T00:00:00.000Z`);
  if (
    !/^\d{4}-\d{2}-\d{2}$/.test(value) ||
    !Number.isFinite(date.getTime()) ||
    date.toISOString().slice(0, 10) !== value
  ) {
    throw new Error(`Invalid source meeting date: ${value}`);
  }
  return date;
}

export function buildRgcDecisionSeedPlan(
  source: RgcSeedSource,
  refs: RgcSeedReferences,
  options: RgcSeedOptions = {},
) {
  const approvedSubmissionBatches = [
    'oct2026-kh',
    'real-maff-19-wg-a',
    'real-maff-19-wg-i',
    'real-mot-19-wg-b',
    'real-misti-19-wg-c',
    'real-mlvt-19-wg-h',
    'real-gdce-19-oct2026',
    'real-gdt-19-oct2026',
    'real-mef-19-oct2026',
    'real-moi-19-nov2026',
    'real-mpwt-19-wg-f',
    'real-nbc-19-wg-e',
    'real-mlmupc-19-wg-m',
    'real-mme-19-wg-j',
    'real-shv-admin-19',
    'real-cdc-19',
  ];
  if (
    options.submitImported &&
    !approvedSubmissionBatches.includes(source.batch)
  )
    throw new Error('Automatic submission is not approved for this batch');
  if (refs.actors.length !== 1)
    throw new Error('Exactly one active CDC G-PSF seed owner is required');
  const date = parseDate(source.plenary.meetingDate);
  const names = [source.plenary.name, ...source.plenary.matchingNames].map(
    normalizeSeedName,
  );
  const sameDate = refs.plenaries.filter(
    (row) =>
      row.meetingDate.toISOString().slice(0, 10) === source.plenary.meetingDate,
  );
  // These Nov 13 decisions also belong to the verified Nov 22 Plenary 19.
  const eligibleParents =
    ['real-cdc-19', 'real-shv-admin-19', 'real-gdt-19-oct2026', 'real-mef-19-oct2026', 'real-moi-19-nov2026'].includes(source.batch)
      ? refs.plenaries.filter((row) =>
          [source.plenary.meetingDate, '2023-11-22'].includes(
            row.meetingDate.toISOString().slice(0, 10),
          ),
        )
      : sameDate;
  const parents = eligibleParents.filter((row) =>
    names.includes(normalizeSeedName(row.name)),
  );
  if (parents.length > 1 || (sameDate.length > 0 && parents.length === 0))
    throw new Error(
      'Ambiguous historical plenary; review the existing record before importing',
    );
  if (parents[0]?.deletedAt)
    throw new Error(
      `Historical plenary ${parents[0].id} is deleted; it will not be restored automatically`,
    );
  const parent = parents[0];
  const inserts: {
    sourceNo: number;
    data: Omit<Prisma.PlenaryRgcDecisionUncheckedCreateInput, 'plenaryId'>;
    khmer?: {
      decisionKh: string | null;
      focalPersonKh: string | null;
      verificationSourceKh: string | null;
    };
  }[] = [];
  const skipped: { sourceNo: number; reason: string; decisionId?: number }[] =
    [];
  const submissions: {
    sourceNo: number;
    decisionId: number;
    stakeholderId: number;
    meetingDate: Date;
    decision: string;
  }[] = [];
  const held: { sourceNo: number; reason: string; blocking: boolean }[] = [];
  const unresolved: { sourceNo: number; reason: string }[] = [];
  const seen = new Map<string, SourceRgcDecision>();
  // References contain only active, non-deleted Ministry stakeholders.
  // Include them even when every decision was already imported on an earlier run.
  const ministryIds = new Set<number>(
    options.includeAllMinistries ? refs.ministries.map((row) => row.id) : [],
  );

  for (const row of source.decisions) {
    if (row.holdReason) {
      held.push({
        sourceNo: row.sourceNo,
        reason: row.holdReason,
        blocking: false,
      });
      continue;
    }
    if (row.progressAnswer) {
      const answer = row.progressAnswer;
      const invalidText = Object.values(answer).some(
        (value) => value != null && typeof value !== 'string',
      );
      const solutionDate = answer.dateOfIssueSolution;
      if (
        invalidText ||
        (solutionDate != null &&
          (!/^\d{4}-\d{2}-\d{2}$/.test(solutionDate) ||
            Number.isNaN(new Date(solutionDate).getTime()) ||
            new Date(solutionDate).toISOString().slice(0, 10) !== solutionDate))
      ) {
        unresolved.push({
          sourceNo: row.sourceNo,
          reason: 'Invalid progress answer text or exact solution date',
        });
        continue;
      }
    }
    const ministries = refs.ministries.filter(
      (item) =>
        normalizeSeedName(item.name) === normalizeSeedName(row.ministry),
    );
    if (ministries.length !== 1) {
      unresolved.push({
        sourceNo: row.sourceNo,
        reason: `Missing or ambiguous primary ministry: ${row.ministry}`,
      });
      continue;
    }
    const ministry = ministries[0];
    if (source.batch === 'real-shv-admin-19' || source.batch === 'real-cdc-19') {
      const isCdc = source.batch === 'real-cdc-19';
      const agency = isCdc ? 'CDC' : 'SHV Admin';
      if (!parent)
        throw new Error(`${agency} requires the existing Plenary 19`);
      const english = normalizeRgcSeedDecision(row.decision);
      const khmer = normalizeRgcSeedDecision(row.decisionKh ?? '');
      const decisionNumber = (text: string) => text.match(/^\(?(\d+(?:\.\d+)+)/)?.[1];
      const number = decisionNumber(english);
      const numbers = isCdc ? ['11.4.1', '11.2', '11.4', '11.6'] : ['6.3.3'];
      if (!number || !numbers.includes(number) || ministry.name !== agency || !khmer ||
          (isCdc && (row.workingGroup !== 'Other issues' || decisionNumber(khmer) !== number)))
        throw new Error(`Unexpected ${agency} source identity`);
      const candidates = refs.existing.filter((item) => {
        const texts = [item.decision, item.decisionKh ?? ''].map(
          normalizeRgcSeedDecision,
        );
        const samePlenary =
          item.plenaryId === parent.id ||
          item.meetingDate.toISOString().slice(0, 10) === row.meetingDate;
        return texts.some(
          (text) =>
            text === english ||
            text === khmer ||
            (samePlenary && decisionNumber(text) === number),
        );
      });
      if (
        candidates.length > 1 ||
        candidates.some(
          (item) =>
            item.deletedAt ||
            item.plenaryId !== parent.id ||
            item.stakeholderId !== ministry.id ||
            item.userId !== refs.actors[0].id ||
            item.status !== row.status ||
            !refs.categories.some(
              (category) =>
                category.id === item.categoryId &&
                category.name === row.category,
            ) ||
            normalizeRgcSeedDecision(item.decision) !== english ||
            normalizeRgcSeedDecision(item.decisionKh ?? '') !== khmer,
        )
      )
        throw new Error(
          `Conflicting or deleted Plenary 19 decision ${number}; no ${agency} import`,
        );
      ministryIds.add(ministry.id);
    }
    const labels = [
      row.decision,
      row.decisionKh ?? '',
      ...row.matchingDecisions,
    ]
      .filter((value) => value.trim())
      .map(normalizeRgcSeedDecision);
    const matches = refs.existing.filter(
      (item) =>
        item.stakeholderId === ministry.id &&
        labels.includes(normalizeRgcSeedDecision(item.decision)),
    );
    if (
      matches.length > 1 ||
      matches.some(
        (item) =>
          item.meetingDate.toISOString().slice(0, 10) !== row.meetingDate,
      )
    ) {
      held.push({
        sourceNo: row.sourceNo,
        reason: `Ambiguous existing decisions or conflicting meeting dates: ${matches.map((item) => item.id).join(', ')}`,
        blocking: true,
      });
      continue;
    }
    const keys = labels.map(
      (label) => `${ministry.id}:${row.meetingDate}:${label}`,
    );
    const earlier = keys.map((key) => seen.get(key)).find(Boolean);
    keys.forEach((key) => seen.set(key, row));
    if (matches[0] || earlier) {
      if (
        earlier &&
        (earlier.status !== row.status || earlier.category !== row.category)
      )
        held.push({
          sourceNo: row.sourceNo,
          reason: 'Conflicting repeated source classification/status',
          blocking: true,
        });
      else {
        skipped.push({
          sourceNo: row.sourceNo,
          reason: 'Matching decision already exists',
          decisionId: matches[0]?.id,
        });
        const existing = matches[0];
        // Alias matches still prevent duplicates, but never publish an older
        // English record, another owner's draft, or a deleted record.
        if (
          options.submitImported &&
          !earlier &&
          existing &&
          parent &&
          existing.plenaryId === parent.id &&
          existing.userId === refs.actors[0].id &&
          existing.deletedAt === null &&
          existing.submittedToCdcAt === null &&
          normalizeSeedName(existing.decision) ===
            normalizeSeedName(row.decision)
        ) {
          submissions.push({
            sourceNo: row.sourceNo,
            decisionId: existing.id,
            stakeholderId: existing.stakeholderId,
            meetingDate: existing.meetingDate,
            decision: existing.decision,
          });
        }
      }
      continue;
    }
    const categoryLabel =
      row.category === 'អាជ្ញាប័ណ្ណ' ? 'Licensing' : row.category;
    const categories = refs.categories.filter((item) =>
      [item.name, item.khName].some(
        (label) =>
          label &&
          normalizeSeedName(label) === normalizeSeedName(categoryLabel),
      ),
    );
    if (categories.length !== 1) {
      unresolved.push({
        sourceNo: row.sourceNo,
        reason: `Missing or ambiguous category: ${row.category}`,
      });
      continue;
    }
    const meetingDate = parseDate(row.meetingDate);
    if (
      row.meetingDate !== source.plenary.meetingDate ||
      !row.decision.trim() ||
      (!/[\u1780-\u17ff]/u.test(row.decisionKh || row.decision) &&
        !(row.allowEnglishOnly === true && row.decisionKh === null && /[a-z]/i.test(row.decision))) ||
      [row.decisionKh, row.focalPersonKh, row.verificationSourceKh].some(
        (value) => value != null && typeof value !== 'string',
      ) ||
      (row.focalPersonKh != null && [...row.focalPersonKh].length > 255) ||
      !row.focalPerson.trim() ||
      [...row.focalPerson].length > 255 ||
      !['SOLVED', 'IN_PROGRESS', 'NOT_ADDRESSED'].includes(row.status)
    ) {
      unresolved.push({
        sourceNo: row.sourceNo,
        reason: 'Invalid Khmer decision, focal person, status, or plenary date',
      });
      continue;
    }
    if (
      row.verificationLink &&
      (row.verificationLink.length > 500 ||
        !/^https?:\/\//i.test(row.verificationLink))
    ) {
      unresolved.push({
        sourceNo: row.sourceNo,
        reason: 'Invalid verification link',
      });
      continue;
    }
    if (
      parent &&
      !options.addMissingMinistries &&
      !parent.ministries.some((item) => item.stakeholderId === ministry.id)
    ) {
      unresolved.push({
        sourceNo: row.sourceNo,
        reason: `Historical plenary ${parent.id} is not assigned to ministry ${ministry.name}; existing links will not be changed`,
      });
      continue;
    }
    ministryIds.add(ministry.id);
    inserts.push({
      sourceNo: row.sourceNo,
      ...(row.decisionKh !== undefined ||
      row.focalPersonKh !== undefined ||
      row.verificationSourceKh !== undefined
        ? {
            khmer: {
              decisionKh: row.decisionKh ?? null,
              focalPersonKh: row.focalPersonKh ?? null,
              verificationSourceKh: row.verificationSourceKh ?? null,
            },
          }
        : {}),
      data: {
        stakeholderId: ministry.id,
        categoryId: categories[0].id,
        indicatorId: null,
        meetingDate,
        status: row.status,
        focalPerson: row.focalPerson,
        decision: row.decision,
        verificationSource: row.verificationSource,
        verificationLink: row.verificationLink,
        userId: refs.actors[0].id,
        submittedToCdcAt: options.submitImported ? new Date() : null,
        revisionOpen: false,
      },
    });
  }
  return {
    inserts,
    skipped,
    submissions,
    held,
    unresolved,
    plenary: {
      id: parent?.id ?? null,
      name: source.plenary.name,
      meetingDate: parent?.meetingDate ?? date,
      userId: refs.actors[0].id,
      ministryIds: [...ministryIds],
    },
  };
}

async function loadReferences(
  tx: Prisma.TransactionClient,
): Promise<RgcSeedReferences> {
  const [actors, ministries, categories, plenaries, existing] =
    await Promise.all([
      tx.user.findMany({
        where: {
          isActive: true,
          deletedAt: null,
          roles: {
            some: {
              role: {
                name: { equals: 'cdc_g-psf', mode: 'insensitive' },
                deletedAt: null,
              },
            },
          },
        },
        select: { id: true },
      }),
      tx.stakeholder.findMany({
        where: {
          active: true,
          deletedAt: null,
          stakeholderType: { name: 'Ministry', deletedAt: null },
        },
        select: { id: true, name: true },
      }),
      tx.categories.findMany({
        where: { deletedAt: null },
        select: { id: true, name: true, khName: true },
      }),
      tx.plenary.findMany({
        select: {
          id: true,
          name: true,
          meetingDate: true,
          deletedAt: true,
          ministries: { select: { stakeholderId: true } },
        },
      }),
      // Include soft-deleted records: never recreate or restore an existing decision.
      tx.plenaryRgcDecision.findMany({
        select: {
          id: true,
          stakeholderId: true,
          meetingDate: true,
          decision: true,
          decisionKh: true,
          status: true,
          categoryId: true,
          plenaryId: true,
          userId: true,
          submittedToCdcAt: true,
          deletedAt: true,
        },
      }),
    ]);
  return { actors, ministries, categories, plenaries, existing };
}

export function loadRgcDecisionSeedSource(): RgcSeedSource {
  return JSON.parse(
    readFileSync(
      join(__dirname, 'rgc-decisions-kh-oct2026-source.json'),
      'utf8',
    ),
  ) as RgcSeedSource;
}

export async function seedRgcDecisions(
  database: PrismaClient,
  source: RgcSeedSource,
  apply = false,
  createHistoricalPlenary = false,
  options: RgcSeedOptions = {},
) {
  return database.$transaction(
    async (tx) => {
      if (apply) {
        await tx.$queryRaw`SELECT 1 AS locked FROM pg_advisory_xact_lock(20261027, 19)`;
        await tx.$executeRaw`LOCK TABLE "plenaries", "plenary_ministries", "plenary_rgc_decisions" IN SHARE ROW EXCLUSIVE MODE`;
        if (options.addProgressAnswers)
          await tx.$executeRaw`LOCK TABLE "progress_reports", "progress_report_ministries", "progress_report_updates", "progress_report_update_plenary_decisions" IN SHARE ROW EXCLUSIVE MODE`;
      } else await tx.$executeRaw`SET TRANSACTION READ ONLY`;
      const refs = await loadReferences(tx);
      let plannedAgency: string | null = null;
      if (source.batch === 'real-cdc-19') {
        // Agency only: do not create a user, role, membership or group pairing.
        const types = await tx.stakeholderTypes.findMany({ where: { name: 'Ministry', deletedAt: null } });
        const matches = await tx.stakeholder.findMany({ where: { name: { equals: 'CDC', mode: 'insensitive' } } });
        if (types.length !== 1 || matches.length > 1 || matches.some(row =>
          row.name !== 'CDC' || !row.active || row.deletedAt || row.stakeholderTypeId !== types[0].id))
          throw new Error('Conflicting CDC agency or Ministry type; nothing changed');
        if (!matches.length) {
          plannedAgency = 'CDC';
          const data = { name: 'CDC', description: 'Council for the Development of Cambodia', khName: 'ក្រុមប្រឹក្សាអភិវឌ្ឍន៍កម្ពុជា', khAbbreviation: 'ក.អ.ក.', stakeholderTypeId: types[0].id };
          refs.ministries.push(apply ? await tx.stakeholder.create({ data }) : { id: -1, name: 'CDC' });
        }
      }
      const plan = buildRgcDecisionSeedPlan(source, refs, options);
      const created: { sourceNo: number; decisionId: number }[] = [];
      const submitted: { sourceNo: number; decisionId: number }[] = [];
      let plenaryId = plan.plenary.id;
      let createdPlenary = false;
      if (apply) {
        if (plan.unresolved.length || plan.held.some((row) => row.blocking))
          throw new Error(
            `Import stopped before writes: ${JSON.stringify({ unresolved: plan.unresolved, held: plan.held })}`,
          );
        // Check the additive migration before creating any records. Keep raw
        // writes here so this seed does not require changing API selections.
        if (plan.inserts.some((row) => row.khmer)) {
          await tx.$queryRaw`SELECT decision_kh, focal_person_kh, verification_source_kh FROM plenary_rgc_decisions LIMIT 0`;
        }
        if (plan.inserts.length && plenaryId === null) {
          if (!createHistoricalPlenary)
            throw new Error(
              'Historical plenary is missing. Review the preview and pass --create-historical-plenary to create it.',
            );
          const parent = await tx.plenary.create({
            data: {
              name: plan.plenary.name,
              meetingDate: plan.plenary.meetingDate,
              userId: plan.plenary.userId,
              status: 'DRAFT',
              deadline: null,
              ministries: {
                create: plan.plenary.ministryIds.map((stakeholderId) => ({
                  stakeholderId,
                })),
              },
            },
            select: { id: true },
          });
          plenaryId = parent.id;
          createdPlenary = true;
        }
        if (plenaryId !== null && options.addMissingMinistries) {
          await tx.plenaryMinistry.createMany({
            data: plan.plenary.ministryIds.map((stakeholderId) => ({
              plenaryId,
              stakeholderId,
            })),
            skipDuplicates: true,
          });
        }
        for (const row of plan.inserts) {
          const saved = await tx.plenaryRgcDecision.create({
            data: { ...row.data, plenaryId },
            select: { id: true },
          });
          if (row.khmer) {
            await tx.$executeRaw`UPDATE plenary_rgc_decisions
              SET decision_kh = ${row.khmer.decisionKh},
                  focal_person_kh = ${row.khmer.focalPersonKh},
                  verification_source_kh = ${row.khmer.verificationSourceKh}
              WHERE id = ${saved.id}`;
          }
          created.push({ sourceNo: row.sourceNo, decisionId: saved.id });
          if (options.submitImported)
            submitted.push({ sourceNo: row.sourceNo, decisionId: saved.id });
        }
        for (const row of plan.submissions) {
          // Only the source-verified imports are shared. Do not call workflow
          // services: those send notifications, which this seed must not send.
          const result = await tx.plenaryRgcDecision.updateMany({
            where: {
              id: row.decisionId,
              plenaryId,
              userId: plan.plenary.userId,
              stakeholderId: row.stakeholderId,
              meetingDate: row.meetingDate,
              decision: row.decision,
              submittedToCdcAt: null,
              deletedAt: null,
            },
            data: { submittedToCdcAt: new Date() },
          });
          if (result.count !== 1)
            throw new Error(
              `Imported decision ${row.decisionId} changed before submission`,
            );
          submitted.push({
            sourceNo: row.sourceNo,
            decisionId: row.decisionId,
          });
        }
      }
      const progressAnswers = options.addProgressAnswers
        ? await seedRgcProgressAnswers(
            tx,
            source,
            new Map(
              [...plan.skipped, ...created]
                .filter((row) => row.decisionId != null)
                .map((row) => [row.sourceNo, row.decisionId!]),
            ),
            apply,
            new Map(
              plan.inserts.map((row) => [row.sourceNo, row.data.stakeholderId]),
            ),
            options.addMissingReportMinistries,
            refs.actors[0]?.id,
          )
        : undefined;
      return {
        progressAnswers,
        mode: apply ? 'apply' : 'preview',
        ...(plannedAgency ? { plannedAgency } : {}),
        planned: plan.inserts.length,
        inserts: plan.inserts,
        skipped: plan.skipped,
        submissions: plan.submissions,
        submissionPlanned:
          plan.submissions.length +
          (options.submitImported ? plan.inserts.length : 0),
        submitted,
        held: plan.held,
        unresolved: plan.unresolved,
        plenary: { ...plan.plenary, id: plenaryId, created: createdPlenary },
        created,
      };
    },
    { timeout: 30000 },
  );
}