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

export type SourceIssue = {
  sourceNo: number;
  workingGroup: string;
  title: string;
  description: string;
  recommendation: string;
  status: string;
  category: string;
  agencies: { name: string; order: number }[];
  matchingTitles?: string[];
  statusCode?: string;
  provenance?: { workbook: string; sheet: string; row: number };
  holdReason?: string;
  sourceOfVerification?: string | null;
  linkToVerificationSource?: string | null;
  history?: {
    sourceRecord: number;
    meetingType: string | null;
    meetingDate: string | null;
    governmentDecision: string | null;
    sourceOfVerification: string | null;
    linkToVerificationSource: string | null;
  };
};

type References = {
  groups: { id: number; name: string; userIds: number[] }[];
  agencies: { id: number; name: string }[];
  categories: { id: number; name: string; khName?: string | null }[];
  statuses: { id: number; name: string; code?: string | null }[];
  existing: { id: number; title: string; stakeholderId: number }[];
};

const GROUP_ALIASES: Record<string, string> = {
  'Agriculture and Agro-Industry': 'Agriculture & Agro-Industry',
  'SMEs, Manufacturing, and Services': 'Manufacturing & SMEs',
  'Transportation and Infrastructure': 'Transport and Infrastructure',
  'Digital Economy, Society and Telecommunications':
    'Digital Economy, Society & Telecommunications',
  'Land Administration, Security, Public Order':
    'Land Administration, Security & Public Order',
};

const AGENCY_ALIASES: Record<string, string> = {
  MLVT: 'MoLVT',
  Moc: 'MoC',
  'The Electricity Authority of Cambodia (EAC)': 'EAC',
  ក្រសួងមហាផ្ទៃ: 'MoI',
  'ក្រសួងរៀបចំដែនដី នគរូបនីយកម្ម និងសំណង់': 'MLMUPC',
};

const CATEGORY_ALIASES: Record<string, string> = {
  'Human Resource': 'Human Resources',
  License: 'Licensing',
  Market: 'Market Access',
  Markets: 'Market Access',
  ទីផ្សារ: 'Market Access',
  បទបញ្ញត្តិ: 'Regulation',
};

export function normalizeSeedName(value: string) {
  return value
    .normalize('NFC')
    .replace(/[\u200B-\u200D\uFEFF]/g, '')
    .trim()
    .replace(/\s+/g, ' ')
    .toLowerCase();
}

// Remote databases require an explicit database name to avoid accidental imports.
export function validateIssueMatrixSeedTarget(
  connectionString: string,
  expectedDatabase?: string,
) {
  const target = new URL(connectionString);
  if (!['postgres:', 'postgresql:'].includes(target.protocol)) {
    throw new Error('DATABASE_URL must point to PostgreSQL');
  }
  const database = decodeURIComponent(target.pathname.slice(1));
  if (!database) throw new Error('DATABASE_URL must include a database name');
  if (expectedDatabase !== undefined) {
    if (!expectedDatabase || expectedDatabase !== database) {
      throw new Error(
        '--database must match the database name in DATABASE_URL',
      );
    }
  } else if (
    !['localhost', '127.0.0.1', '[::1]'].includes(target.hostname) ||
    database !== 'gpsf_mis'
  ) {
    throw new Error(
      'For this target, pass --database=NAME matching DATABASE_URL',
    );
  }
  return { host: target.hostname, port: target.port || '5432', database };
}

function findNamed<T extends { name: string; khName?: string | null }>(
  items: T[],
  name: string,
) {
  const matches = items.filter((item) =>
    [item.name, item.khName].some(
      (label) => label && normalizeSeedName(label) === normalizeSeedName(name),
    ),
  );
  if (matches.length > 1) throw new Error(`Ambiguous reference: ${name}`);
  return matches[0];
}

export function buildIssueMatrixSeedPlan(
  source: SourceIssue[],
  references: References,
  titleOverrides: Record<number, string> = {},
) {
  const inserts: {
    sourceNo: number;
    workingGroup: string;
    data: {
      title: string;
      description: string;
      recommendation: string;
      stakeholderId: number;
      userId: number;
      categoryId: number;
      issueStatusId: number;
    };
    agencies: { name: string; order: number }[];
  }[] = [];
  const skipped: { sourceNo: number; reason: string; issueId?: number }[] = [];
  const warnings: { sourceNo: number; reason: string }[] = [];
  const skippedAgencies: { sourceNo: number; name: string; order: number }[] =
    [];
  const errors: string[] = [];
  const held: { sourceNo: number; reason: string; blocking: boolean }[] = [];
  const unresolved: { sourceNo: number; reason: string }[] = [];
  const seen = new Set<string>();

  for (const issue of source) {
    if (issue.holdReason) {
      held.push({
        sourceNo: issue.sourceNo,
        reason: issue.holdReason,
        blocking: false,
      });
    }
    const groupName = GROUP_ALIASES[issue.workingGroup] ?? issue.workingGroup;
    const group = findNamed(references.groups, groupName);
    if (!group || group.userIds.length === 0) {
      if (issue.provenance && !issue.holdReason) {
        unresolved.push({
          sourceNo: issue.sourceNo,
          reason: `No active PSWG account for ${groupName}`,
        });
        continue;
      }
      skipped.push({
        sourceNo: issue.sourceNo,
        reason: 'No active PSWG account for this working group',
      });
      continue;
    }
    if (group.userIds.length > 1) {
      errors.push(
        `Row ${issue.sourceNo}: more than one active PSWG account for ${groupName}`,
      );
      continue;
    }

    const title = titleOverrides[issue.sourceNo] ?? issue.title;
    const titles = [title, issue.title, ...(issue.matchingTitles ?? [])];
    const keys = titles.map((name) => `${group.id}:${normalizeSeedName(name)}`);
    const duplicateSource = keys.some((key) => seen.has(key));
    keys.forEach((key) => seen.add(key));
    if (issue.holdReason) continue;
    const matches = references.existing.filter(
      (row) =>
        row.stakeholderId === group.id &&
        titles.some(
          (candidate) =>
            normalizeSeedName(row.title) === normalizeSeedName(candidate),
        ),
    );
    if (matches.length > 1) {
      held.push({
        sourceNo: issue.sourceNo,
        reason: `Ambiguous existing issues: ${matches.map((row) => row.id).join(', ')}`,
        blocking: true,
      });
      continue;
    }
    const existing = matches[0];
    if (existing || duplicateSource) {
      if (duplicateSource && issue.provenance && !existing) {
        held.push({
          sourceNo: issue.sourceNo,
          reason: 'Overlapping curated source aliases',
          blocking: true,
        });
        continue;
      }
      skipped.push({
        sourceNo: issue.sourceNo,
        reason: 'Matching issue already exists',
        issueId: existing?.id,
      });
      continue;
    }

    // Keep a formatting-only candidate out of the import without blocking
    // unrelated rows. Multiple candidates still require review before writes.
    const compact = (name: string) =>
      normalizeSeedName(name).replace(/[\p{P}\p{Z}\s]/gu, '');
    const possible =
      (issue.provenance || issue.matchingTitles?.length) &&
      references.existing.filter(
        (row) =>
          row.stakeholderId === group.id &&
          titles.some(
            (name) =>
              compact(name).length >= 20 &&
              compact(name) === compact(row.title),
          ),
      );
    if (possible && possible.length) {
      held.push({
        sourceNo: issue.sourceNo,
        reason: `Possible existing issues: ${possible.map((row) => row.id).join(', ')}`,
        blocking: possible.length > 1,
      });
      continue;
    }

    const categoryName = CATEGORY_ALIASES[issue.category] ?? issue.category;
    const category = findNamed(references.categories, categoryName);
    const statuses = issue.statusCode
      ? references.statuses.filter((row) => row.code === issue.statusCode)
      : [];
    if (statuses.length > 1)
      throw new Error(`Ambiguous status code: ${issue.statusCode}`);
    const status = issue.statusCode
      ? statuses[0]
      : findNamed(references.statuses, issue.status);
    if (!category || !status || !title.trim() || [...title].length > 200) {
      if (issue.provenance) {
        unresolved.push({
          sourceNo: issue.sourceNo,
          reason: `Missing category/status or invalid title: ${issue.category} / ${issue.statusCode}`,
        });
        continue;
      }
      errors.push(
        `Row ${issue.sourceNo}: missing category/status or title exceeds 200 characters`,
      );
      continue;
    }

    const agencyNames = new Set<string>();
    const agencyOrders = new Set<number>();
    const agencies: { name: string; order: number }[] = [];
    for (const agency of issue.agencies) {
      const name = AGENCY_ALIASES[agency.name] ?? agency.name;
      const existingAgency = findNamed(references.agencies, name);
      const normalized = normalizeSeedName(existingAgency?.name ?? name);
      if (agencyNames.has(normalized)) continue;
      if (
        !Number.isInteger(agency.order) ||
        agency.order < 1 ||
        agency.order > 5 ||
        agencyOrders.has(agency.order)
      ) {
        errors.push(`Row ${issue.sourceNo}: invalid agency order`);
        continue;
      }
      agencyNames.add(normalized);
      agencyOrders.add(agency.order);
      if (!existingAgency) {
        if (issue.provenance)
          unresolved.push({
            sourceNo: issue.sourceNo,
            reason: `Missing agency (slot ${agency.order}): ${name}`,
          });
        skippedAgencies.push({
          sourceNo: issue.sourceNo,
          name,
          order: agency.order,
        });
        continue;
      }
      agencies.push({ name: existingAgency.name, order: agency.order });
    }
    if (!issue.agencies.some((agency) => agency.order === 1)) {
      errors.push(`Row ${issue.sourceNo}: missing primary agency`);
      continue;
    }
    if (unresolved.some((row) => row.sourceNo === issue.sourceNo)) continue;

    let description = issue.description;
    if (title !== issue.title) {
      description =
        `${description}\n\nOriginal issue title: ${issue.title}`.trim();
      warnings.push({
        sourceNo: issue.sourceNo,
        reason: 'Shortened title; full original retained in description',
      });
    }
    inserts.push({
      sourceNo: issue.sourceNo,
      workingGroup: group.name,
      data: {
        title,
        description,
        recommendation: issue.recommendation,
        stakeholderId: group.id,
        userId: group.userIds[0],
        categoryId: category.id,
        issueStatusId: status.id,
      },
      agencies,
    });
  }
  if (errors.length) throw new Error(errors.join('\n'));
  return { inserts, skipped, held, unresolved, warnings, skippedAgencies };
}

export async function loadIssueMatrixSeedReferences(
  database: Prisma.TransactionClient,
) {
  const stakeholders = await database.stakeholder.findMany({
    where: {
      active: true,
      deletedAt: null,
      stakeholderType: { deletedAt: null },
    },
    select: {
      id: true,
      name: true,
      stakeholderType: { select: { name: true } },
      users: {
        where: {
          user: {
            isActive: true,
            deletedAt: null,
            roles: {
              some: {
                role: {
                  name: { equals: 'private_sector', mode: 'insensitive' },
                  deletedAt: null,
                },
              },
            },
          },
        },
        select: { userId: true },
      },
    },
  });
  const [categories, statuses, existing] = await Promise.all([
    database.categories.findMany({
      where: { deletedAt: null },
      select: { id: true, name: true, khName: true },
    }),
    database.issueStatuses.findMany({
      where: { deletedAt: null },
      select: { id: true, name: true, code: true },
    }),
    database.issues.findMany({
      // Include deleted issues so importing never recreates an existing record.
      select: { id: true, title: true, stakeholderId: true },
    }),
  ]);
  return {
    groups: stakeholders
      .filter((row) => row.stakeholderType.name === 'Private Sector')
      .map((row) => ({
        id: row.id,
        name: row.name,
        userIds: row.users.map((user) => user.userId),
      })),
    agencies: stakeholders.filter(
      (row) => row.stakeholderType.name === 'Ministry',
    ),
    categories,
    statuses,
    existing,
  };
}

// Preview reads references only. Apply revalidates and inserts everything together.
export async function seedIssueMatrix(
  database: PrismaClient,
  source: SourceIssue[],
  apply = false,
  titleOverrides: Record<number, string> = {},
) {
  return database.$transaction(
    async (transaction) => {
      // Serialize this seed so two simultaneous runs cannot insert duplicate issues.
      if (apply) {
        await transaction.$queryRaw`SELECT 1 AS locked FROM pg_advisory_xact_lock(20261019, 29)`;
        // Keep normal issue creation from racing the duplicate check.
        await transaction.$executeRaw`LOCK TABLE "issues" IN SHARE ROW EXCLUSIVE MODE`;
      } else {
        await transaction.$executeRaw`SET TRANSACTION READ ONLY`;
      }
      const references = await loadIssueMatrixSeedReferences(transaction);
      const plan = buildIssueMatrixSeedPlan(source, references, titleOverrides);
      const created: {
        sourceNo: number;
        issueId: number;
        workingGroup: string;
      }[] = [];
      if (apply) {
        if (plan.unresolved.length || plan.held.some((row) => row.blocking)) {
          throw new Error(
            `Import stopped before writes. Resolve references or ambiguous matches first:\n${JSON.stringify({ unresolved: plan.unresolved, held: plan.held }, null, 2)}`,
          );
        }
        const agencyIds = new Map(
          references.agencies.map((agency) => [
            normalizeSeedName(agency.name),
            agency.id,
          ]),
        );
        for (const row of plan.inserts) {
          const issue = await transaction.issues.create({
            data: {
              ...row.data,
              governmentAgencies: {
                create: row.agencies.map((agency) => ({
                  stakeholderId: agencyIds.get(normalizeSeedName(agency.name))!,
                  agencyOrder: agency.order,
                  createdById: row.data.userId,
                })),
              },
            },
            select: { id: true },
          });
          created.push({
            sourceNo: row.sourceNo,
            issueId: issue.id,
            workingGroup: row.workingGroup,
          });
        }
        await backfillSeedIssueNumbers(
          transaction,
          created.map((row) => row.issueId),
        );
      }
      return {
        mode: apply ? 'apply' : 'preview',
        planned: plan.inserts.length,
        inserts: plan.inserts,
        held: plan.held,
        unresolved: plan.unresolved,
        groups: new Set(plan.inserts.map((row) => row.data.stakeholderId)).size,
        skippedAgencies: plan.skippedAgencies,
        skipped: plan.skipped,
        warnings: plan.warnings,
        created,
      };
    },
    { timeout: 30000 },
  );
}

// Shared by the main seed and the standalone preview/apply command.
export async function seedIssueMatrixFromSource(
  database: PrismaClient,
  apply = false,
  batch: 'legacy' | 'oct2026-kh' | 'all' = 'legacy',
) {
  const legacy = JSON.parse(
    readFileSync(join(__dirname, 'issue-matrix-source.json'), 'utf8'),
  ) as { issues: SourceIssue[] };
  const curated = JSON.parse(
    readFileSync(
      join(__dirname, 'issue-matrix-kh-oct2026-source.json'),
      'utf8',
    ),
  ) as { issues: SourceIssue[] };
  const source = selectIssueMatrixSeedSource(
    legacy.issues,
    curated.issues,
    batch,
  );
  return seedIssueMatrix(database, source, apply, {
    17: 'Email, phone number and provincial registration requirements for enterprise branches',
  });
}

// Enrich legacy matching only; never alter its stored title or source JSON.
export function selectIssueMatrixSeedSource(
  legacy: SourceIssue[],
  curated: SourceIssue[],
  batch: 'legacy' | 'oct2026-kh' | 'all',
) {
  const legacyWithAliases = legacy.map((issue) => {
    const aliases = curated.filter(
      (row) =>
        normalizeSeedName(
          GROUP_ALIASES[row.workingGroup] ?? row.workingGroup,
        ) ===
          normalizeSeedName(
            GROUP_ALIASES[issue.workingGroup] ?? issue.workingGroup,
          ) &&
        [row.title, ...(row.matchingTitles ?? [])].some(
          (title) =>
            normalizeSeedName(title) === normalizeSeedName(issue.title),
        ),
    );
    return {
      ...issue,
      matchingTitles: [
        ...(issue.matchingTitles ?? []),
        ...aliases.flatMap((row) => [row.title, ...(row.matchingTitles ?? [])]),
      ],
      holdReason:
        aliases.find((row) => row.holdReason)?.holdReason ?? issue.holdReason,
    };
  });
  if (batch === 'oct2026-kh') return curated;
  return batch === 'all'
    ? [...curated, ...legacyWithAliases]
    : legacyWithAliases;
}