import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import { Prisma, type PrismaClient } from '../../src/generated/prisma/client';

// Approved historical import: expose saved S1 2026 answers without notifications.
// Do not reset completed/reviewed submissions or affect another reporting period.
export async function seedS12026AnswerVisibility(
  prisma: PrismaClient,
  apply = false,
  ministryNames?: string[],
) {
  return prisma.$transaction(
    async (tx) => {
      if (apply) {
        await tx.$queryRaw`SELECT 1 AS locked FROM pg_advisory_xact_lock(20261031, 26)`;
        await tx.$executeRaw`LOCK TABLE "progress_reports", "progress_report_ministries", "progress_report_updates" IN SHARE ROW EXCLUSIVE MODE`;
      } else {
        await tx.$executeRaw`SET TRANSACTION READ ONLY`;
      }
      const reports = await tx.progressReport.findMany({
        where: { year: 2026, semester: 'S1', deletedAt: null },
        select: { id: true },
      });
      if (reports.length > 1)
        throw new Error('Multiple active S1 2026 reports; stop for review.');
      if (!reports.length) return { reportId: null, submissions: [] };
      const reportId = reports[0].id;
      const assignments = await tx.progressReportMinistry.findMany({
        where: {
          progressReportId: reportId,
          deletedAt: null,
          status: { in: ['REQUESTED', 'PREPARING'] },
          ...(ministryNames
            ? { ministry: { name: { in: ministryNames } } }
            : {}),
        },
        select: { id: true, ministryId: true, status: true, submittedAt: true },
      });
      const submissions: { ministryId: number; answers: number }[] = [];
      for (const assignment of assignments) {
        const answers = await tx.progressReportUpdate.count({
          where: {
            progressReportId: reportId,
            ministryId: assignment.ministryId,
            deletedAt: null,
          },
        });
        if (!answers) continue;
        if (apply) {
          const result = await tx.progressReportMinistry.updateMany({
            where: {
              id: assignment.id,
              deletedAt: null,
              status: assignment.status,
            },
            data: {
              status: 'SUBMITTED',
              submittedAt: assignment.submittedAt ?? new Date(),
            },
          });
          if (result.count !== 1)
            throw new Error(
              'Submission changed during seed; retry after review.',
            );
        }
        submissions.push({ ministryId: assignment.ministryId, answers });
      }
      return { reportId, submissions };
    },
    { isolationLevel: 'Serializable', timeout: 30000 },
  );
}

type IssueUpdateSource = {
  sourceRef: string;
  issueNumber: string;
  authorEmail: string | null;
  issueStatusCode: 'SOLVED' | 'IN_PROGRESS' | 'NOT_ADDRESSED';
  issueResolveId?: number | null;
  indicators?: Prisma.JsonValue;
  indicatorsKh?: Prisma.JsonValue;
  progressSolution?: Prisma.JsonValue;
  implementationChallenges?: Prisma.JsonValue;
  requests?: Prisma.JsonValue;
  sourceOfVerification?: string | null;
  sourceOfVerificationKh?: string | null;
  linkToVerificationSource?: string | null;
  dateOfIssueSolution?: string | null;
  attachement?: string | null;
  next_step?: Prisma.JsonValue;
};

type MinistrySource = {
  ministryName: string;
  assignedUserEmail: string | null;
  status: 'REQUESTED' | 'PREPARING';
  attachment?: string | null;
  issueUpdates: IssueUpdateSource[];
};

type ReportSource = {
  sourceRef: string;
  title: string;
  description?: Prisma.JsonValue;
  year: number;
  semester: 'S1' | 'S2';
  status: 'DRAFT';
  ownerEmail: string | null;
  attachment?: string | null;
  draftSemesterReport?: string | null;
  finalSemesterReport?: string | null;
  draftSemesterReportStatus?: 'DRAFT' | null;
  finalSemesterReportStatus?: 'DRAFT' | null;
  ministries: MinistrySource[];
};

export type ProgressReportSeedSource = {
  schemaVersion: 1;
  exampleOnly: boolean;
  reports: ReportSource[];
};

// The JSON is an import format, not a new API response or database model.
export function loadProgressReportSeedSource(
  path = join(__dirname, 'progress-report-source.json'),
): ProgressReportSeedSource {
  return JSON.parse(readFileSync(path, 'utf8')) as ProgressReportSeedSource;
}

function requireObject(value: unknown, keys: string[], label: string) {
  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 requireText(value: unknown, label: string, max = 500) {
  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 && value !== undefined) {
    requireText(value, label, max ?? Number.MAX_SAFE_INTEGER);
  }
}

function requireEmail(value: unknown, label: string) {
  requireText(value, label, 254);
  if (!/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value as string)) {
    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);
}

export function validateProgressReportSeedSource(
  source: ProgressReportSeedSource,
) {
  requireObject(source, ['schemaVersion', 'exampleOnly', 'reports'], 'Source');
  if (source.schemaVersion !== 1 || typeof source.exampleOnly !== 'boolean') {
    throw new Error('Use schemaVersion=1 and an explicit exampleOnly boolean');
  }
  if (!Array.isArray(source.reports))
    throw new Error('reports must be an array');
  // Incomplete example rows are intentionally never imported.
  if (source.exampleOnly) return;

  const periods = new Set<string>();
  const sourceRefs = new Set<string>();
  for (const report of source.reports) {
    requireObject(
      report,
      [
        'sourceRef',
        'title',
        'description',
        'year',
        'semester',
        'status',
        'ownerEmail',
        'attachment',
        'draftSemesterReport',
        'finalSemesterReport',
        'draftSemesterReportStatus',
        'finalSemesterReportStatus',
        'ministries',
      ],
      'Report',
    );
    requireText(report.sourceRef, 'Report sourceRef');
    unique(report.sourceRef, sourceRefs, 'sourceRef');
    requireText(report.title, 'Report title', 255);
    requireEmail(report.ownerEmail, 'Report ownerEmail');
    if (
      !Number.isInteger(report.year) ||
      report.year < 1900 ||
      report.year > 9999
    ) {
      throw new Error('Report year must be an integer between 1900 and 9999');
    }
    if (!['S1', 'S2'].includes(report.semester))
      throw new Error('Use semester S1 or S2');
    unique(`${report.year}/${report.semester}`, periods, 'report period');
    if (report.status !== 'DRAFT')
      throw new Error('This seed creates DRAFT reports only');
    for (const field of [
      'draftSemesterReportStatus',
      'finalSemesterReportStatus',
    ] as const) {
      if (report[field] != null && report[field] !== 'DRAFT') {
        throw new Error('This seed does not publish report documents');
      }
    }
    for (const field of [
      'attachment',
      'draftSemesterReport',
      'finalSemesterReport',
    ] as const) {
      optionalText(report[field], field, 500);
    }
    if (!Array.isArray(report.ministries))
      throw new Error('ministries must be an array');
    const ministries = new Set<string>();
    for (const ministry of report.ministries) {
      requireObject(
        ministry,
        [
          'ministryName',
          'assignedUserEmail',
          'status',
          'attachment',
          'issueUpdates',
        ],
        'Ministry',
      );
      requireText(ministry.ministryName, 'ministryName');
      unique(
        ministry.ministryName.trim().toLowerCase(),
        ministries,
        'ministry',
      );
      if (ministry.assignedUserEmail !== null)
        requireEmail(ministry.assignedUserEmail, 'assignedUserEmail');
      if (!['REQUESTED', 'PREPARING'].includes(ministry.status)) {
        throw new Error(
          'Only REQUESTED or PREPARING assignments are supported; no submission history is invented',
        );
      }
      optionalText(ministry.attachment, 'Ministry attachment', 500);
      if (!Array.isArray(ministry.issueUpdates))
        throw new Error('issueUpdates must be an array');
      const issues = new Set<string>();
      for (const update of ministry.issueUpdates) {
        requireObject(
          update,
          [
            'sourceRef',
            'issueNumber',
            'authorEmail',
            'issueStatusCode',
            'issueResolveId',
            'indicators',
            'indicatorsKh',
            'progressSolution',
            'implementationChallenges',
            'requests',
            'sourceOfVerification',
            'sourceOfVerificationKh',
            'linkToVerificationSource',
            'dateOfIssueSolution',
            'attachement',
            'next_step',
          ],
          'Issue update',
        );
        requireText(update.sourceRef, 'Update sourceRef');
        unique(update.sourceRef, sourceRefs, 'sourceRef');
        requireText(update.issueNumber, 'issueNumber', 30);
        unique(update.issueNumber, issues, 'issue in ministry');
        requireEmail(update.authorEmail, 'Update authorEmail');
        if (
          !['SOLVED', 'IN_PROGRESS', 'NOT_ADDRESSED'].includes(
            update.issueStatusCode,
          )
        ) {
          throw new Error(
            'Use issueStatusCode SOLVED, IN_PROGRESS or NOT_ADDRESSED',
          );
        }
        if (
          update.issueResolveId != null &&
          (!Number.isInteger(update.issueResolveId) ||
            update.issueResolveId < 1)
        ) {
          throw new Error(
            'issueResolveId must be an existing positive ID or null',
          );
        }
        for (const field of [
          'sourceOfVerification',
          'sourceOfVerificationKh',
          'linkToVerificationSource',
        ] as const) {
          optionalText(update[field], field);
        }
        optionalText(update.attachement, 'attachement', 500);
        if (update.dateOfIssueSolution != null) {
          const date = new Date(update.dateOfIssueSolution);
          if (
            !Number.isFinite(date.getTime()) ||
            date.toISOString() !== update.dateOfIssueSolution
          ) {
            throw new Error(
              'dateOfIssueSolution must be an ISO UTC timestamp such as 2026-06-30T00:00:00.000Z',
            );
          }
        }
      }
    }
  }
}

function json(value: Prisma.JsonValue | undefined) {
  return value == null ? Prisma.DbNull : value;
}

async function account(
  tx: Prisma.TransactionClient,
  email: string,
  roleNames: string[],
  ministryId?: number,
) {
  const user = await tx.user.findFirst({
    where: {
      email,
      deletedAt: null,
      isActive: true,
      roles: {
        some: {
          role: { name: { in: roleNames }, guardName: 'web', deletedAt: null },
        },
      },
      ...(ministryId === undefined
        ? {}
        : { stakeholders: { some: { stakeholderId: ministryId } } }),
    },
    select: { id: true },
  });
  if (!user)
    throw new Error(`Missing, inactive or ineligible account: ${email}`);
  return user.id;
}

async function buildPlan(
  tx: Prisma.TransactionClient,
  source: ProgressReportSeedSource,
) {
  const reports: {
    source: ReportSource;
    userId: number;
    ministries: {
      source: MinistrySource;
      ministryId: number;
      userId: number | null;
      updates: {
        source: IssueUpdateSource;
        issueId: number;
        issueStatusId: number;
        userId: number;
      }[];
    }[];
  }[] = [];
  const skipped: { sourceRef: string; reportId: number; reason: string }[] = [];

  for (const report of source.reports) {
    const existing = await tx.progressReport.findMany({
      where: { year: report.year, semester: report.semester },
      select: { id: true, title: true, deletedAt: true },
    });
    const active = existing.filter((row) => !row.deletedAt);
    if (
      active.length > 1 ||
      (active.length === 1 && active[0].title !== report.title)
    ) {
      throw new Error(
        `An active report for ${report.semester} in ${report.year} already exists; it will not be overwritten`,
      );
    }
    const match =
      active[0] ?? existing.find((row) => row.title === report.title);
    if (match) {
      skipped.push({
        sourceRef: report.sourceRef,
        reportId: match.id,
        reason: match.deletedAt
          ? 'Matching deleted report preserved; not restored'
          : 'Existing report preserved; no children appended or changed',
      });
      continue;
    }
    const userId = await account(tx, report.ownerEmail!, [
      'admin',
      'cdc_g-psf',
    ]);
    const planned: (typeof reports)[number] = {
      source: report,
      userId,
      ministries: [],
    };
    for (const ministry of report.ministries) {
      const matches = await tx.stakeholder.findMany({
        where: {
          name: ministry.ministryName,
          active: true,
          deletedAt: null,
          stakeholderType: { name: 'Ministry', deletedAt: null },
        },
        select: { id: true },
      });
      if (matches.length !== 1)
        throw new Error(
          `Missing or ambiguous ministry: ${ministry.ministryName}`,
        );
      const ministryId = matches[0].id;
      const assignedUserId =
        ministry.assignedUserEmail === null
          ? null
          : await account(
              tx,
              ministry.assignedUserEmail,
              ['ministry'],
              ministryId,
            );
      const plannedMinistry: (typeof planned.ministries)[number] = {
        source: ministry,
        ministryId,
        userId: assignedUserId,
        updates: [],
      };
      for (const update of ministry.issueUpdates) {
        const issue = await tx.issues.findUnique({
          where: { numberIssue: update.issueNumber },
          select: {
            id: true,
            deletedAt: true,
            stakeholder: {
              select: {
                id: true,
                active: true,
                deletedAt: true,
                stakeholderType: { select: { name: true, deletedAt: true } },
              },
            },
            governmentAgencies: {
              select: { stakeholderId: true, agencyOrder: true },
            },
          },
        });
        if (!issue || issue.deletedAt)
          throw new Error(`Missing or deleted issue: ${update.issueNumber}`);
        const group = issue.stakeholder;
        if (
          !group.active ||
          group.deletedAt ||
          group.stakeholderType.deletedAt ||
          group.stakeholderType.name !== 'Private Sector'
        ) {
          throw new Error(
            `Issue ${update.issueNumber} has no active working-group owner`,
          );
        }
        if (
          !issue.governmentAgencies.some(
            (agency) =>
              agency.stakeholderId === ministryId && agency.agencyOrder === 1,
          )
        ) {
          throw new Error(
            `Issue ${update.issueNumber} has no primary-agency assignment to ministry ${ministry.ministryName}`,
          );
        }
        if (update.issueResolveId != null) {
          const resolve = await tx.issueResolves.findFirst({
            where: {
              id: update.issueResolveId,
              issueId: issue.id,
              deletedAt: null,
              meetingSummary: { deletedAt: null },
            },
            select: { id: true },
          });
          if (!resolve)
            throw new Error(
              `Resolution ${update.issueResolveId} does not belong to issue ${update.issueNumber}`,
            );
        }
        const statuses = await tx.issueStatuses.findMany({
          where: { code: update.issueStatusCode, deletedAt: null },
          select: { id: true },
        });
        if (statuses.length !== 1)
          throw new Error(
            `Missing or ambiguous issue status: ${update.issueStatusCode}`,
          );
        plannedMinistry.updates.push({
          source: update,
          issueId: issue.id,
          issueStatusId: statuses[0].id,
          userId: await account(
            tx,
            update.authorEmail!,
            ['ministry'],
            ministryId,
          ),
        });
      }
      planned.ministries.push(plannedMinistry);
    }
    reports.push(planned);
  }
  return { reports, skipped };
}

export async function seedProgressReports(
  database: PrismaClient,
  source = loadProgressReportSeedSource(),
  apply = false,
) {
  validateProgressReportSeedSource(source);
  if (source.exampleOnly) {
    return {
      mode: apply ? 'apply' : 'preview',
      exampleOnly: true,
      created: 0,
      planned: 0,
      skipped: [],
      message:
        'Example source disabled. Fill account emails and actual data, then set exampleOnly=false.',
    };
  }
  return database.$transaction(
    async (tx) => {
      if (apply) {
        await tx.$queryRaw`SELECT 1 AS locked FROM pg_advisory_xact_lock(20261030, 31)`;
        await tx.$executeRaw`LOCK TABLE "progress_reports", "progress_report_ministries", "progress_report_updates", "progress_report_update_issues" IN SHARE ROW EXCLUSIVE MODE`;
      } else {
        await tx.$executeRaw`SET TRANSACTION READ ONLY`;
      }
      // Resolve the complete batch before any insert. Existing records are never updated.
      const plan = await buildPlan(tx, source);
      const created: { sourceRef: string; reportId: number }[] = [];
      if (apply) {
        for (const item of plan.reports) {
          const report = item.source;
          const savedReport = await tx.progressReport.create({
            data: {
              title: report.title,
              description: json(report.description),
              year: report.year,
              semester: report.semester,
              status: 'DRAFT',
              userId: item.userId,
              attachment: report.attachment,
              draftSemesterReport: report.draftSemesterReport,
              finalSemesterReport: report.finalSemesterReport,
              draftSemesterReportStatus: report.draftSemesterReportStatus,
              finalSemesterReportStatus: report.finalSemesterReportStatus,
            },
            select: { id: true },
          });
          for (const ministry of item.ministries) {
            await tx.progressReportMinistry.create({
              data: {
                progressReportId: savedReport.id,
                ministryId: ministry.ministryId,
                userId: ministry.userId,
                status: ministry.source.status,
                attachment: ministry.source.attachment,
                issues: ministry.updates.length,
              },
            });
            for (const itemUpdate of ministry.updates) {
              const update = itemUpdate.source;
              const savedUpdate = await tx.progressReportUpdate.create({
                data: {
                  progressReportId: savedReport.id,
                  ministryId: ministry.ministryId,
                  issueId: itemUpdate.issueId,
                  issueStatusId: itemUpdate.issueStatusId,
                  issueResolveId: update.issueResolveId,
                  userId: itemUpdate.userId,
                  indicators: json(update.indicators),
                  progressSolution: json(update.progressSolution),
                  implementationChallenges: json(
                    update.implementationChallenges,
                  ),
                  requests: json(update.requests),
                  sourceOfVerification: update.sourceOfVerification,
                  linkToVerificationSource: update.linkToVerificationSource,
                  dateOfIssueSolution: update.dateOfIssueSolution
                    ? new Date(update.dateOfIssueSolution)
                    : null,
                  attachement: update.attachement,
                  next_step: json(update.next_step),
                },
                select: { id: true },
              });
              // These additive database fields intentionally have not required a client regeneration.
              // Only the row just created above is touched; no historical update is changed.
              if (
                update.indicatorsKh != null ||
                update.sourceOfVerificationKh != null
              ) {
                await tx.$executeRaw`UPDATE "progress_report_updates"
                SET "indicators_kh" = ${update.indicatorsKh == null ? null : JSON.stringify(update.indicatorsKh)}::jsonb,
                    "source_of_verification_kh" = ${update.sourceOfVerificationKh ?? null}
                WHERE "id" = ${savedUpdate.id}`;
              }
              await tx.progressReportUpdateIssue.create({
                data: {
                  progressReportUpdateId: savedUpdate.id,
                  ministryId: ministry.ministryId,
                  issueId: itemUpdate.issueId,
                },
              });
            }
          }
          created.push({
            sourceRef: report.sourceRef,
            reportId: savedReport.id,
          });
        }
      }
      return {
        mode: apply ? 'apply' : 'preview',
        exampleOnly: false,
        planned: plan.reports.length,
        created: created.length,
        records: created,
        skipped: plan.skipped,
        reports: plan.reports.map((report) => ({
          sourceRef: report.source.sourceRef,
          year: report.source.year,
          semester: report.source.semester,
          ministries: report.ministries.length,
          issueUpdates: report.ministries.reduce(
            (sum, ministry) => sum + ministry.updates.length,
            0,
          ),
        })),
      };
    },
    {
      isolationLevel: Prisma.TransactionIsolationLevel.Serializable,
      timeout: 60000,
    },
  );
}