import { existsSync, readFileSync } from "node:fs";
import { readFile } from "node:fs/promises";
import { dirname, resolve } from "node:path";
import { Prisma, PrismaClient } from "@prisma/client";
import { env } from "../env.js";
import { createId } from "./id.js";
import { isImageKitConfigured, uploadBinaryFileToImageKit } from "./imagekit.js";
import { prisma } from "./prisma.js";
import { saveBinaryFile } from "./storage.js";

export const NEURAHUB_IMPORT_TEMPLATE_CODE = "neurahub_document_import";
const NEURAHUB_DOCUMENT_LIBRARY_SOURCE_CODE = "legacy_neurahub";
const NEURAHUB_DOCUMENT_LIBRARY_NAME = "Workspace document library";
const NEURAHUB_FORM_TEMPLATE_CODE_PREFIX = "legacy_neurahub_form_";
const NEURAHUB_IMPORTED_FORM_LIBRARY_NAME = "Workspace form templates";

export function isWorkspaceLibraryWorkflowTemplateCode(code: string) {
  return code === NEURAHUB_IMPORT_TEMPLATE_CODE;
}

type LegacyDocumentTypeRow = {
  id: number;
  name: string;
  document_details: string | null;
  ai_validation_instructions: string | null;
  is_active: number;
  created_at: Date | string | null;
  updated_at: Date | string | null;
};

type DocumentTypeRow = {
  code: string;
  name: string;
  description: string | null;
};

type ImportedDocumentLibrarySummaryRow = {
  updated_at: Date;
  document_count: number;
};

type ImportedFormTemplateSummaryRow = {
  updated_at: Date;
  form_count: number;
  with_base_pdf_count: number;
  field_count: number;
};

type WorkspaceDocumentLibraryItemRow = {
  id: string;
  source_code: string;
  requirement_code: string;
  display_name: string;
  document_type_code: string;
  description: string | null;
  ai_instructions: string | null;
  is_required: number;
  minimum_quantity: number;
};

type LegacyFormTemplateRow = {
  id: number;
  name: string;
  original_file_name: string | null;
  file_path: string | null;
  is_active: number;
  created_at: Date | string | null;
  updated_at: Date | string | null;
};

type LegacyFormTemplateFieldRow = {
  id: number;
  template_id: number;
  field_key: string | null;
  field_label: string | null;
  field_description: string | null;
  field_target: string | null;
  default_checked: number | boolean | null;
  default_value: string | null;
  field_type: string | null;
  font_size: number | string | null;
  page_number: number | string | null;
  pos_x: Prisma.Decimal | number | string | null;
  pos_y: Prisma.Decimal | number | string | null;
  width: Prisma.Decimal | number | string | null;
  height: Prisma.Decimal | number | string | null;
  is_required: number | boolean | null;
  sort_order: number | string | null;
};

type ExistingFormTemplateRow = {
  id: string;
  code: string;
  base_pdf_file_id: string | null;
};

type ExistingFormFieldRow = {
  id: string;
  field_key: string;
  condition_expression_json: unknown;
};

type PrismaExecutor = Prisma.TransactionClient | PrismaClient;

export type ImportedDocumentLibrarySummary = {
  name: string;
  updatedAt: string;
  documentCount: number;
} | null;

export type WorkspaceDocumentLibraryItem = {
  id: string;
  sourceCode: string;
  requirementCode: string;
  displayName: string;
  documentTypeCode: string;
  description: string | null;
  aiInstructions: string | null;
  isRequired: boolean;
  minimumQuantity: number;
};

export type NeuraHubDocumentImportSummary = {
  sourceConfigured: boolean;
  sourceReachable: boolean;
  sourceCount: number;
  sourceError: string | null;
  importedLibrary: ImportedDocumentLibrarySummary;
};

export type ImportedFormTemplateSummary = {
  name: string;
  updatedAt: string;
  formCount: number;
  withBasePdfCount: number;
  fieldCount: number;
} | null;

export type NeuraHubFormImportSummary = {
  sourceConfigured: boolean;
  sourceReachable: boolean;
  sourceCount: number;
  sourceError: string | null;
  importedCatalog: ImportedFormTemplateSummary;
};

export type NeuraHubDocumentImportResult = {
  sourceCount: number;
  importedCount: number;
  documentTypesCreated: number;
  documentTypesReused: number;
  importedLibrary: Exclude<ImportedDocumentLibrarySummary, null>;
};

export type NeuraHubFormImportResult = {
  sourceCount: number;
  importedCount: number;
  createdCount: number;
  updatedCount: number;
  fieldsImportedCount: number;
  withBasePdfCount: number;
  templatesMissingBasePdfCount: number;
  importedCatalog: Exclude<ImportedFormTemplateSummary, null>;
};

type LegacyFormTemplateField = {
  id: number;
  fieldKey: string;
  fieldLabel: string;
  fieldDescription: string;
  fieldTarget: "client" | "internal" | "sponsor" | "default";
  defaultChecked: boolean;
  defaultValue: string;
  fieldType: string;
  fontSize: number | null;
  pageNumber: number | null;
  posX: number | null;
  posY: number | null;
  width: number | null;
  height: number | null;
  isRequired: boolean;
  sortOrder: number;
};

type LegacyFormTemplate = {
  id: number;
  name: string;
  originalFileName: string;
  filePath: string;
  fields: LegacyFormTemplateField[];
};

function normalizeIdentifier(value: string, maxLength: number) {
  const normalized =
    value
      .normalize("NFD")
      .replace(/[\u0300-\u036f]/g, "")
      .toLowerCase()
      .replace(/[^a-z0-9]+/g, "_")
      .replace(/^_+|_+$/g, "") || "document";

  return normalized.slice(0, Math.max(1, maxLength)).replace(/^_+|_+$/g, "") || "document";
}

function buildUniqueCode(base: string, usedCodes: Set<string>, maxLength: number) {
  let candidate = base.slice(0, maxLength) || "document";

  if (!usedCodes.has(candidate)) {
    usedCodes.add(candidate);
    return candidate;
  }

  let sequence = 2;

  while (true) {
    const suffix = `_${sequence}`;
    const trimmedBase =
      candidate.slice(0, Math.max(1, maxLength - suffix.length)).replace(/_+$/g, "") || "document";
    const nextCandidate = `${trimmedBase}${suffix}`;

    if (!usedCodes.has(nextCandidate)) {
      usedCodes.add(nextCandidate);
      return nextCandidate;
    }

    sequence += 1;
  }
}

function truncateText(value: string, maxLength: number) {
  return value.length <= maxLength ? value : value.slice(0, Math.max(0, maxLength - 1)).trimEnd();
}

function normalizeLegacyFormFieldTarget(value: string | null | undefined) {
  const normalized = String(value ?? "")
    .trim()
    .toLowerCase();

  if (normalized === "client" || normalized === "sponsor" || normalized === "default") {
    return normalized;
  }

  return "internal" as const;
}

function toOptionalNumber(value: Prisma.Decimal | number | string | null | undefined) {
  if (value === null || value === undefined || value === "") {
    return null;
  }

  if (typeof value === "number") {
    return Number.isFinite(value) ? value : null;
  }

  const numericValue = Number(value);
  return Number.isFinite(numericValue) ? numericValue : null;
}

function buildLegacyFormTemplateCode(templateId: number) {
  return `${NEURAHUB_FORM_TEMPLATE_CODE_PREFIX}${templateId}`;
}

function buildLegacyFormDescription(template: LegacyFormTemplate) {
  const details = [
    "Imported from the legacy neuraHub form catalog.",
    template.originalFileName ? `Original file: ${template.originalFileName}` : null,
    template.filePath ? `Legacy file path: ${template.filePath}` : null,
  ].filter(Boolean);

  return truncateText(details.join("\n"), 1000) || "Imported from the legacy neuraHub form catalog.";
}

function buildLegacyFormFieldInstructions(field: LegacyFormTemplateField) {
  const details = [
    field.fieldDescription ? `Legacy description: ${field.fieldDescription}` : null,
    `Legacy target: ${field.fieldTarget}`,
    `Legacy field type: ${field.fieldType || "input"}`,
    field.defaultValue ? `Legacy default value: ${field.defaultValue}` : null,
    field.defaultChecked ? "Legacy default checked: yes" : null,
    field.pageNumber ? `Legacy page: ${field.pageNumber}` : null,
    field.posX !== null && field.posY !== null
      ? `Legacy position: x=${field.posX}, y=${field.posY}`
      : null,
    field.width !== null && field.height !== null
      ? `Legacy size: width=${field.width}, height=${field.height}`
      : null,
  ].filter(Boolean);

  return truncateText(details.join("\n"), 65535) || null;
}

function mapLegacyFormFieldDataType(fieldType: string) {
  const normalized = fieldType.trim().toLowerCase();

  if (["checkbox", "check", "boolean", "radio", "toggle"].includes(normalized)) {
    return "boolean";
  }

  if (["date", "datetime"].includes(normalized)) {
    return "date";
  }

  if (["number", "integer", "decimal", "currency"].includes(normalized)) {
    return "number";
  }

  if (["email"].includes(normalized)) {
    return "email";
  }

  if (["phone", "tel", "telephone"].includes(normalized)) {
    return "phone";
  }

  return "text";
}

function buildLegacyFormFieldMetadata(template: LegacyFormTemplate, field: LegacyFormTemplateField) {
  return {
    legacyImport: {
      source: "neurahub",
      templateId: template.id,
      fieldId: field.id,
      target: field.fieldTarget,
      fieldType: field.fieldType,
      defaultChecked: field.defaultChecked,
      defaultValue: field.defaultValue || null,
      fontSize: field.fontSize,
      pageNumber: field.pageNumber,
      posX: field.posX,
      posY: field.posY,
      width: field.width,
      height: field.height,
    },
  };
}

function extractLegacyImportedFieldId(conditionExpressionJson: unknown) {
  if (!conditionExpressionJson) {
    return null;
  }

  let parsedValue = conditionExpressionJson;

  if (typeof parsedValue === "string") {
    try {
      parsedValue = JSON.parse(parsedValue);
    } catch {
      return null;
    }
  }

  if (!parsedValue || typeof parsedValue !== "object") {
    return null;
  }

  const legacyImport =
    "legacyImport" in parsedValue &&
    parsedValue.legacyImport &&
    typeof parsedValue.legacyImport === "object"
      ? parsedValue.legacyImport
      : null;

  if (!legacyImport || !("fieldId" in legacyImport)) {
    return null;
  }

  const value = Number(legacyImport.fieldId);
  return Number.isFinite(value) ? value : null;
}

function parseEnvFile(filePath: string) {
  const raw = readFileSync(filePath, "utf8");
  const values: Record<string, string> = {};

  for (const line of raw.split(/\r?\n/)) {
    const trimmedLine = line.trim();

    if (!trimmedLine || trimmedLine.startsWith("#")) {
      continue;
    }

    const separatorIndex = trimmedLine.indexOf("=");

    if (separatorIndex <= 0) {
      continue;
    }

    const key = trimmedLine.slice(0, separatorIndex).trim();
    let value = trimmedLine.slice(separatorIndex + 1).trim();

    if (
      (value.startsWith("\"") && value.endsWith("\"")) ||
      (value.startsWith("'") && value.endsWith("'"))
    ) {
      value = value.slice(1, -1);
    }

    if (key) {
      values[key] = value;
    }
  }

  return values;
}

function buildMysqlDatabaseUrl(config: {
  host: string;
  port: string;
  database: string;
  user: string;
  password: string;
}) {
  const credentials = `${encodeURIComponent(config.user)}:${encodeURIComponent(config.password)}`;
  return `mysql://${credentials}@${config.host}:${config.port}/${config.database}`;
}

function resolveNeuraHubDatabaseUrl() {
  const directUrl = env.NEURAHUB_DATABASE_URL.trim();

  if (directUrl) {
    return directUrl;
  }

  const candidateEnvFiles = [
    env.NEURAHUB_ENV_FILE.trim(),
    resolve(process.cwd(), "../neuraHub/.env"),
    resolve(process.cwd(), "../../neuraHub/.env"),
    resolve(process.cwd(), "../../../neuraHub/.env"),
  ].filter(Boolean);

  for (const envFile of candidateEnvFiles) {
    if (!existsSync(envFile)) {
      continue;
    }

    const values = parseEnvFile(envFile);
    const host = String(values.DB_HOST ?? "").trim();
    const port = String(values.DB_PORT ?? "3306").trim();
    const database = String(values.DB_NAME ?? "").trim();
    const user = String(values.DB_USER ?? "").trim();
    const password = String(values.DB_PASS ?? "");

    if (!host || !database || !user) {
      continue;
    }

    return buildMysqlDatabaseUrl({
      host,
      port: port || "3306",
      database,
      user,
      password,
    });
  }

  return "";
}

function resolveNeuraHubProjectRoot() {
  const configuredEnvFile = env.NEURAHUB_ENV_FILE.trim();
  const candidateRoots = [
    configuredEnvFile ? dirname(configuredEnvFile) : "",
    resolve(process.cwd(), "neuraHub"),
    resolve(process.cwd(), "../neuraHub"),
    resolve(process.cwd(), "../../neuraHub"),
    resolve(process.cwd(), "../../../neuraHub"),
  ].filter(Boolean);

  for (const candidateRoot of candidateRoots) {
    if (existsSync(resolve(candidateRoot, "public"))) {
      return candidateRoot;
    }
  }

  return "";
}

function isMissingWorkspaceDocumentLibraryTableError(error: unknown) {
  if (!(error instanceof Error)) {
    return false;
  }

  const message = error.message.toLowerCase();
  return (
    message.includes("workspace_document_library_items") &&
    (message.includes("doesn't exist") ||
      message.includes("does not exist") ||
      message.includes("unknown table"))
  );
}

async function withNeuraHubPrisma<T>(task: (client: PrismaClient) => Promise<T>) {
  const databaseUrl = resolveNeuraHubDatabaseUrl();

  if (!databaseUrl) {
    throw new Error(
      "neuraHub database is not configured. Set NEURAHUB_DATABASE_URL or NEURAHUB_ENV_FILE.",
    );
  }

  const client = new PrismaClient({
    datasources: {
      db: {
        url: databaseUrl,
      },
    },
  });

  try {
    return await task(client);
  } finally {
    await client.$disconnect().catch(() => undefined);
  }
}

async function listLegacyFormTemplates(): Promise<LegacyFormTemplate[]> {
  const { templates, fields } = await withNeuraHubPrisma(async (client) => {
    const templates = await client.$queryRaw<LegacyFormTemplateRow[]>`
      SELECT
        id,
        name,
        original_file_name,
        file_path,
        is_active,
        created_at,
        updated_at
      FROM document_form_templates
      WHERE is_active = 1
      ORDER BY name ASC, id ASC
    `;

    const templateIds = templates
      .map((row) => Number(row.id))
      .filter((rowId) => Number.isFinite(rowId) && rowId > 0);

    const fields = templateIds.length
      ? await client.$queryRaw<LegacyFormTemplateFieldRow[]>`
          SELECT
            id,
            template_id,
            field_key,
            field_label,
            field_description,
            field_target,
            default_checked,
            default_value,
            field_type,
            font_size,
            page_number,
            pos_x,
            pos_y,
            width,
            height,
            is_required,
            sort_order
          FROM document_form_template_fields
          WHERE template_id IN (${Prisma.join(templateIds)})
          ORDER BY template_id ASC, sort_order ASC, id ASC
        `
      : [];

    return { templates, fields };
  });

  const fieldsByTemplateId = new Map<number, LegacyFormTemplateField[]>();

  for (const row of fields) {
    const templateId = Number(row.template_id);
    if (!Number.isFinite(templateId) || templateId <= 0) {
      continue;
    }

    const fallbackLabel = String(row.field_label ?? row.field_key ?? "").trim();
    const field = {
      id: Number(row.id),
      fieldKey: String(row.field_key ?? "").trim(),
      fieldLabel: fallbackLabel,
      fieldDescription: String(row.field_description ?? "").trim(),
      fieldTarget: normalizeLegacyFormFieldTarget(row.field_target),
      defaultChecked: Boolean(Number(row.default_checked ?? 0)),
      defaultValue: String(row.default_value ?? "").trim(),
      fieldType: String(row.field_type ?? "input").trim() || "input",
      fontSize: toOptionalNumber(row.font_size),
      pageNumber: toOptionalNumber(row.page_number),
      posX: toOptionalNumber(row.pos_x),
      posY: toOptionalNumber(row.pos_y),
      width: toOptionalNumber(row.width),
      height: toOptionalNumber(row.height),
      isRequired: Boolean(Number(row.is_required ?? 0)),
      sortOrder: Number(row.sort_order ?? 0) || 0,
    } satisfies LegacyFormTemplateField;

    if (!field.fieldKey && !field.fieldLabel && !field.fieldDescription) {
      continue;
    }

    const collection = fieldsByTemplateId.get(templateId) ?? [];
    collection.push(field);
    fieldsByTemplateId.set(templateId, collection);
  }

  return templates
    .map((row) => {
      const templateId = Number(row.id);
      const name = String(row.name ?? "").trim();

      if (!Number.isFinite(templateId) || templateId <= 0 || !name) {
        return null;
      }

      const templateFields = [...(fieldsByTemplateId.get(templateId) ?? [])].sort((left, right) => {
        if (left.sortOrder !== right.sortOrder) {
          return left.sortOrder - right.sortOrder;
        }

        return left.id - right.id;
      });

      return {
        id: templateId,
        name,
        originalFileName: String(row.original_file_name ?? "").trim(),
        filePath: String(row.file_path ?? "").trim(),
        fields: templateFields,
      } satisfies LegacyFormTemplate;
    })
    .filter((template): template is LegacyFormTemplate => Boolean(template));
}

async function listLegacyDocumentTypes() {
  const rows = await withNeuraHubPrisma((client) =>
    client.$queryRaw<LegacyDocumentTypeRow[]>`
      SELECT
        id,
        name,
        document_details,
        ai_validation_instructions,
        is_active,
        created_at,
        updated_at
      FROM document_type_catalog
      WHERE is_active = 1
      ORDER BY name ASC
    `,
  );

  const dedupedRows = new Map<string, LegacyDocumentTypeRow>();

  for (const row of rows) {
    const name = String(row.name ?? "").trim();

    if (!name) {
      continue;
    }

    const key = name.toLowerCase();
    const existing = dedupedRows.get(key);

    if (!existing) {
      dedupedRows.set(key, row);
      continue;
    }

    const existingUpdatedAt = existing.updated_at ? new Date(existing.updated_at).getTime() : 0;
    const nextUpdatedAt = row.updated_at ? new Date(row.updated_at).getTime() : 0;

    if (nextUpdatedAt >= existingUpdatedAt) {
      dedupedRows.set(key, row);
    }
  }

  return Array.from(dedupedRows.values()).map((row) => ({
    id: row.id,
    name: String(row.name ?? "").trim(),
    documentDetails: String(row.document_details ?? "").trim(),
    aiValidationInstructions: String(row.ai_validation_instructions ?? "").trim(),
  }));
}

async function getImportedDocumentLibrarySummary(lawFirmId: string) {
  let row: ImportedDocumentLibrarySummaryRow | undefined;

  try {
    [row] = await prisma.$queryRaw<ImportedDocumentLibrarySummaryRow[]>`
      SELECT
        MAX(updated_at) AS updated_at,
        COUNT(id) AS document_count
      FROM workspace_document_library_items
      WHERE law_firm_id = ${lawFirmId}
        AND source_code = ${NEURAHUB_DOCUMENT_LIBRARY_SOURCE_CODE}
    `;
  } catch (error) {
    if (isMissingWorkspaceDocumentLibraryTableError(error)) {
      return null;
    }

    throw error;
  }

  if (!row || Number(row.document_count ?? 0) === 0 || !row.updated_at) {
    return null;
  }

  return {
    name: NEURAHUB_DOCUMENT_LIBRARY_NAME,
    updatedAt: new Date(row.updated_at).toISOString(),
    documentCount: Number(row.document_count ?? 0),
  };
}

async function getImportedFormTemplateSummary(lawFirmId: string): Promise<ImportedFormTemplateSummary> {
  const [row] = await prisma.$queryRaw<ImportedFormTemplateSummaryRow[]>`
    SELECT
      MAX(ft.updated_at) AS updated_at,
      COUNT(ft.id) AS form_count,
      SUM(CASE WHEN ft.base_pdf_file_id IS NULL THEN 0 ELSE 1 END) AS with_base_pdf_count,
      COALESCE((
        SELECT COUNT(ff.id)
        FROM form_fields ff
        INNER JOIN form_templates ft2 ON ft2.id = ff.form_template_id
        WHERE ft2.law_firm_id = ${lawFirmId}
          AND ft2.code LIKE ${`${NEURAHUB_FORM_TEMPLATE_CODE_PREFIX}%`}
      ), 0) AS field_count
    FROM form_templates ft
    WHERE ft.law_firm_id = ${lawFirmId}
      AND ft.code LIKE ${`${NEURAHUB_FORM_TEMPLATE_CODE_PREFIX}%`}
  `;

  if (!row || Number(row.form_count ?? 0) === 0 || !row.updated_at) {
    return null;
  }

  return {
    name: NEURAHUB_IMPORTED_FORM_LIBRARY_NAME,
    updatedAt: new Date(row.updated_at).toISOString(),
    formCount: Number(row.form_count ?? 0),
    withBasePdfCount: Number(row.with_base_pdf_count ?? 0),
    fieldCount: Number(row.field_count ?? 0),
  };
}

async function removeLegacyImportWorkflowTemplates(executor: PrismaExecutor, lawFirmId: string) {
  const templates = await executor.$queryRaw<Array<{ id: string }>>`
    SELECT id
    FROM workflow_templates
    WHERE law_firm_id = ${lawFirmId}
      AND code = ${NEURAHUB_IMPORT_TEMPLATE_CODE}
  `;

  if (templates.length === 0) {
    return;
  }

  const templateIds = templates.map((item) => item.id);
  const packetTemplateCodes = templateIds.map((templateId) => `workflow_${templateId}`);

  if (packetTemplateCodes.length > 0) {
    const packetTemplates = await executor.$queryRaw<Array<{ id: string }>>`
      SELECT id
      FROM packet_templates
      WHERE law_firm_id = ${lawFirmId}
        AND code IN (${Prisma.join(packetTemplateCodes)})
    `;

    if (packetTemplates.length > 0) {
      const packetTemplateIds = packetTemplates.map((item) => item.id);
      await executor.$executeRaw`
        DELETE FROM packet_template_items
        WHERE packet_template_id IN (${Prisma.join(packetTemplateIds)})
      `;
      await executor.$executeRaw`
        DELETE FROM packet_templates
        WHERE id IN (${Prisma.join(packetTemplateIds)})
      `;
    }
  }

  await executor.$executeRaw`
    DELETE FROM workflow_required_forms
    WHERE workflow_template_id IN (${Prisma.join(templateIds)})
  `;
  await executor.$executeRaw`
    DELETE FROM workflow_required_documents
    WHERE workflow_template_id IN (${Prisma.join(templateIds)})
  `;
  await executor.$executeRaw`
    DELETE FROM workflow_steps
    WHERE workflow_template_id IN (${Prisma.join(templateIds)})
  `;
  await executor.$executeRaw`
    DELETE FROM workflow_templates
    WHERE id IN (${Prisma.join(templateIds)})
  `;
}

async function resolveLegacyDocumentTypeCode(
  executor: PrismaExecutor,
  documentType: {
    name: string;
    documentDetails: string;
  },
  state: {
    documentTypesByName: Map<string, DocumentTypeRow>;
    usedCodes: Set<string>;
  },
) {
  const normalizedName = documentType.name.trim().toLowerCase();
  const existingDocumentType = state.documentTypesByName.get(normalizedName);

  if (existingDocumentType?.code) {
    return {
      code: existingDocumentType.code,
      created: false,
    };
  }

  const nextCode = buildUniqueCode(
    `legacy_${normalizeIdentifier(documentType.name, 36)}`,
    state.usedCodes,
    50,
  );
  const description = truncateText(documentType.documentDetails.trim(), 255) || null;

  await executor.$executeRaw`
    INSERT INTO document_types (
      code,
      name,
      description,
      category_code,
      is_identity_document,
      is_expirable
    ) VALUES (
      ${nextCode},
      ${documentType.name},
      ${description},
      ${"supporting"},
      0,
      0
    )
  `;

  const createdRow = {
    code: nextCode,
    name: documentType.name,
    description,
  };

  state.documentTypesByName.set(normalizedName, createdRow);

  return {
    code: nextCode,
    created: true,
  };
}

async function readLegacyFormTemplatePdfBytes(template: LegacyFormTemplate) {
  const normalizedFilePath = template.filePath.trim();

  if (!normalizedFilePath) {
    return null;
  }

  if (/^https?:\/\//i.test(normalizedFilePath)) {
    const response = await fetch(normalizedFilePath);

    if (!response.ok) {
      throw new Error(
        `Unable to download the legacy PDF for "${template.name}" (${response.status} ${response.statusText}).`,
      );
    }

    return Buffer.from(await response.arrayBuffer());
  }

  const projectRoot = resolveNeuraHubProjectRoot();

  if (!projectRoot) {
    throw new Error(
      "Legacy neuraHub project files are not accessible. Set NEURAHUB_ENV_FILE or place the neuraHub project alongside neurav2.",
    );
  }

  const relativePath = normalizedFilePath.replace(/^\/+/, "");
  const candidatePaths = [
    resolve(projectRoot, "public", relativePath),
    resolve(projectRoot, relativePath),
  ];

  for (const candidatePath of candidatePaths) {
    if (existsSync(candidatePath)) {
      return readFile(candidatePath);
    }
  }

  throw new Error(`Legacy PDF file not found for "${template.name}" at "${normalizedFilePath}".`);
}

async function storeImportedFormAsset(input: {
  executor: PrismaExecutor;
  lawFirmId: string;
  userId: string;
  template: LegacyFormTemplate;
  bytes: Buffer;
}) {
  const fileName =
    input.template.originalFileName ||
    `${normalizeIdentifier(input.template.name, 80).replace(/_+/g, "_") || "legacy_form"}.pdf`;

  const stored = isImageKitConfigured()
    ? await uploadBinaryFileToImageKit({
        bytes: input.bytes,
        fileName,
        mimeType: "application/pdf",
        folder: "/workflow-forms/legacy-neurahub",
      })
    : await (async () => {
        const localFile = await saveBinaryFile({
          lawFirmId: input.lawFirmId,
          caseId: null,
          fileName,
          bytes: input.bytes,
          kind: "uploads",
        });

        return {
          storageProvider: "local_dev",
          storageBucket: "workspace",
          objectKey: localFile.relativeObjectKey,
          storageRegion: "local",
          storedFileName: localFile.storedFileName,
          checksumSha256: localFile.checksumSha256,
        };
      })();

  const repositoryItemId = createId();
  const fileId = createId();

  await input.executor.$executeRaw`
    INSERT INTO repository_items (
      id,
      law_firm_id,
      item_type_code,
      subject,
      summary_text,
      source_entity_type,
      source_entity_id,
      created_by_user_id,
      external_reference,
      occurred_at,
      created_at,
      updated_at
    ) VALUES (
      ${repositoryItemId},
      ${input.lawFirmId},
      ${"document"},
      ${`${input.template.name} base PDF`},
      ${"Imported from the legacy neuraHub form catalog."},
      ${"legacy_neurahub_form_template"},
      ${String(input.template.id)},
      ${input.userId},
      ${`neurahub_form_template:${input.template.id}:base_pdf`},
      ${new Date()},
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
  `;

  await input.executor.$executeRaw`
    INSERT INTO files (
      id,
      law_firm_id,
      client_id,
      case_id,
      repository_item_id,
      storage_provider,
      storage_bucket,
      object_key,
      storage_region,
      original_file_name,
      stored_file_name,
      mime_type,
      size_bytes,
      checksum_sha256,
      is_encrypted,
      uploaded_by_user_id,
      uploaded_at,
      created_at
    ) VALUES (
      ${fileId},
      ${input.lawFirmId},
      NULL,
      NULL,
      ${repositoryItemId},
      ${stored.storageProvider},
      ${stored.storageBucket},
      ${stored.objectKey},
      ${stored.storageRegion},
      ${fileName},
      ${stored.storedFileName},
      ${"application/pdf"},
      ${input.bytes.length},
      ${stored.checksumSha256},
      0,
      ${input.userId},
      NOW(),
      CURRENT_TIMESTAMP
    )
  `;

  return fileId;
}

async function syncImportedLegacyFormTemplate(input: {
  lawFirmId: string;
  userId: string;
  template: LegacyFormTemplate;
  dataFieldsByKey: Map<string, string>;
  existingTemplateByCode: Map<string, ExistingFormTemplateRow>;
  existingImportedTemplateIds: Set<string>;
}) {
  const templateCode = buildLegacyFormTemplateCode(input.template.id);
  const importedBasePdfBytes = await (async () => {
    try {
      return await readLegacyFormTemplatePdfBytes(input.template);
    } catch {
      return null;
    }
  })();

  const existingTemplate = input.existingTemplateByCode.get(templateCode) ?? null;

  return prisma.$transaction(async (transaction) => {
    let basePdfFileId = existingTemplate?.base_pdf_file_id ?? null;

    if (importedBasePdfBytes?.length) {
      basePdfFileId = await storeImportedFormAsset({
        executor: transaction,
        lawFirmId: input.lawFirmId,
        userId: input.userId,
        template: input.template,
        bytes: importedBasePdfBytes,
      });
    }

    const templateId = existingTemplate?.id ?? createId();
    const description = buildLegacyFormDescription(input.template);

    if (existingTemplate) {
      await transaction.$executeRaw`
        UPDATE form_templates
        SET
          name = ${input.template.name},
          description = ${description},
          version_label = ${"legacy"},
          base_pdf_file_id = ${basePdfFileId},
          updated_at = CURRENT_TIMESTAMP
        WHERE id = ${templateId}
      `;
    } else {
      await transaction.$executeRaw`
        INSERT INTO form_templates (
          id,
          law_firm_id,
          code,
          name,
          version_label,
          base_pdf_file_id,
          description,
          is_active,
          is_system_template,
          created_at,
          updated_at
        ) VALUES (
          ${templateId},
          ${input.lawFirmId},
          ${templateCode},
          ${input.template.name},
          ${"legacy"},
          ${basePdfFileId},
          ${description},
          1,
          0,
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;
    }

    input.existingImportedTemplateIds.add(templateId);

    const existingFields = await transaction.$queryRaw<ExistingFormFieldRow[]>`
      SELECT id, field_key, condition_expression_json
      FROM form_fields
      WHERE form_template_id = ${templateId}
    `;

    const existingFieldsByLegacyId = new Map<number, ExistingFormFieldRow>();

    for (const existingField of existingFields) {
      const sourceFieldId = extractLegacyImportedFieldId(existingField.condition_expression_json);
      if (sourceFieldId !== null) {
        existingFieldsByLegacyId.set(sourceFieldId, existingField);
      }
    }

    const usedFieldKeys = new Set(existingFields.map((field) => field.field_key));
    let fieldsImportedCount = 0;

    for (const legacyField of input.template.fields) {
      const existingField = existingFieldsByLegacyId.get(legacyField.id) ?? null;
      const normalizedFieldKeyBase = normalizeIdentifier(
        legacyField.fieldKey || legacyField.fieldLabel || `field_${legacyField.id}`,
        90,
      );
      const nextFieldKey = existingField
        ? existingField.field_key
        : buildUniqueCode(normalizedFieldKeyBase || `field_${legacyField.id}`, usedFieldKeys, 100);
      const pdfFieldName = truncateText(legacyField.fieldKey || nextFieldKey, 255);
      const instructions = buildLegacyFormFieldInstructions(legacyField);
      const conditionExpressionJson = JSON.stringify(
        buildLegacyFormFieldMetadata(input.template, legacyField),
      );

      if (existingField) {
        await transaction.$executeRaw`
          UPDATE form_fields
          SET
            section_name = ${legacyField.fieldTarget === "default" ? "default" : legacyField.fieldTarget},
            page_number = ${legacyField.pageNumber ?? 1},
            field_key = ${nextFieldKey},
            label = ${truncateText(legacyField.fieldLabel || legacyField.fieldKey || nextFieldKey, 255)},
            pdf_field_name = ${pdfFieldName},
            data_type = ${mapLegacyFormFieldDataType(legacyField.fieldType)},
            is_required = ${legacyField.isRequired ? 1 : 0},
            condition_expression_json = ${conditionExpressionJson},
            instructions = ${instructions},
            updated_at = CURRENT_TIMESTAMP
          WHERE id = ${existingField.id}
        `;

        const dataFieldId = input.dataFieldsByKey.get(nextFieldKey);
        if (dataFieldId) {
          await transaction.$executeRaw`
            INSERT IGNORE INTO form_mappings (
              id,
              form_template_id,
              form_field_id,
              data_field_id,
              mapping_strategy,
              confidence_threshold,
              is_active,
              created_at,
              updated_at
            ) VALUES (
              ${createId()},
              ${templateId},
              ${existingField.id},
              ${dataFieldId},
              ${"direct"},
              ${0.8},
              1,
              CURRENT_TIMESTAMP,
              CURRENT_TIMESTAMP
            )
          `;
        }
      } else {
        const fieldId = createId();

        await transaction.$executeRaw`
          INSERT INTO form_fields (
            id,
            form_template_id,
            section_name,
            page_number,
            field_key,
            label,
            pdf_field_name,
            data_type,
            is_required,
            is_repeatable,
            condition_expression_json,
            instructions,
            created_at,
            updated_at
          ) VALUES (
            ${fieldId},
            ${templateId},
            ${legacyField.fieldTarget === "default" ? "default" : legacyField.fieldTarget},
            ${legacyField.pageNumber ?? 1},
            ${nextFieldKey},
            ${truncateText(legacyField.fieldLabel || legacyField.fieldKey || nextFieldKey, 255)},
            ${pdfFieldName},
            ${mapLegacyFormFieldDataType(legacyField.fieldType)},
            ${legacyField.isRequired ? 1 : 0},
            0,
            ${conditionExpressionJson},
            ${instructions},
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
          )
        `;

        const dataFieldId = input.dataFieldsByKey.get(nextFieldKey);
        if (dataFieldId) {
          await transaction.$executeRaw`
            INSERT IGNORE INTO form_mappings (
              id,
              form_template_id,
              form_field_id,
              data_field_id,
              mapping_strategy,
              confidence_threshold,
              is_active,
              created_at,
              updated_at
            ) VALUES (
              ${createId()},
              ${templateId},
              ${fieldId},
              ${dataFieldId},
              ${"direct"},
              ${0.8},
              1,
              CURRENT_TIMESTAMP,
              CURRENT_TIMESTAMP
            )
          `;
        }
      }

      fieldsImportedCount += 1;
    }

    input.existingTemplateByCode.set(templateCode, {
      id: templateId,
      code: templateCode,
      base_pdf_file_id: basePdfFileId,
    });

    return {
      created: !existingTemplate,
      updated: Boolean(existingTemplate),
      fieldsImportedCount,
      hasBasePdf: Boolean(basePdfFileId),
    };
  });
}

export async function listWorkspaceDocumentLibraryItems(
  lawFirmId: string,
): Promise<WorkspaceDocumentLibraryItem[]> {
  let rows: WorkspaceDocumentLibraryItemRow[] = [];

  try {
    rows = await prisma.$queryRaw<WorkspaceDocumentLibraryItemRow[]>`
      SELECT
        id,
        source_code,
        requirement_code,
        display_name,
        document_type_code,
        description,
        ai_instructions,
        is_required,
        minimum_quantity
      FROM workspace_document_library_items
      WHERE law_firm_id = ${lawFirmId}
      ORDER BY display_name ASC, created_at ASC
    `;
  } catch (error) {
    if (isMissingWorkspaceDocumentLibraryTableError(error)) {
      return [];
    }

    throw error;
  }

  return rows.map((row) => ({
    id: row.id,
    sourceCode: row.source_code,
    requirementCode: row.requirement_code,
    displayName: row.display_name,
    documentTypeCode: row.document_type_code,
    description: row.description,
    aiInstructions: row.ai_instructions,
    isRequired: Boolean(row.is_required),
    minimumQuantity: Number(row.minimum_quantity ?? 1),
  }));
}

export async function getNeuraHubDocumentImportSummary(
  lawFirmId: string,
): Promise<NeuraHubDocumentImportSummary> {
  const importedLibrary = await getImportedDocumentLibrarySummary(lawFirmId);
  const databaseUrl = resolveNeuraHubDatabaseUrl();

  if (!databaseUrl) {
    return {
      sourceConfigured: false,
      sourceReachable: false,
      sourceCount: 0,
      sourceError:
        "Configure NEURAHUB_DATABASE_URL or NEURAHUB_ENV_FILE to import from the legacy neuraHub database.",
      importedLibrary,
    };
  }

  try {
    const documentTypes = await listLegacyDocumentTypes();

    return {
      sourceConfigured: true,
      sourceReachable: true,
      sourceCount: documentTypes.length,
      sourceError: null,
      importedLibrary,
    };
  } catch (error) {
    return {
      sourceConfigured: true,
      sourceReachable: false,
      sourceCount: 0,
      sourceError: error instanceof Error ? error.message : "Unable to connect to neuraHub.",
      importedLibrary,
    };
  }
}

export async function getNeuraHubFormImportSummary(
  lawFirmId: string,
): Promise<NeuraHubFormImportSummary> {
  const importedCatalog = await getImportedFormTemplateSummary(lawFirmId);
  const databaseUrl = resolveNeuraHubDatabaseUrl();

  if (!databaseUrl) {
    return {
      sourceConfigured: false,
      sourceReachable: false,
      sourceCount: 0,
      sourceError:
        "Configure NEURAHUB_DATABASE_URL or NEURAHUB_ENV_FILE to import from the legacy neuraHub database.",
      importedCatalog,
    };
  }

  try {
    const templates = await listLegacyFormTemplates();

    return {
      sourceConfigured: true,
      sourceReachable: true,
      sourceCount: templates.length,
      sourceError: null,
      importedCatalog,
    };
  } catch (error) {
    return {
      sourceConfigured: true,
      sourceReachable: false,
      sourceCount: 0,
      sourceError: error instanceof Error ? error.message : "Unable to connect to neuraHub.",
      importedCatalog,
    };
  }
}

export async function importNeuraHubDocumentCatalog(input: {
  lawFirmId: string;
  userId: string;
}) {
  const legacyDocumentTypes = await listLegacyDocumentTypes();

  if (legacyDocumentTypes.length === 0) {
    throw new Error("No active document types were found in the neuraHub database.");
  }

  let documentTypesCreated = 0;

  try {
    await prisma.$transaction(async (transaction) => {
      await removeLegacyImportWorkflowTemplates(transaction, input.lawFirmId);
      await transaction.$executeRaw`
        DELETE FROM workspace_document_library_items
        WHERE law_firm_id = ${input.lawFirmId}
          AND source_code = ${NEURAHUB_DOCUMENT_LIBRARY_SOURCE_CODE}
      `;

      const currentDocumentTypes = await transaction.$queryRaw<DocumentTypeRow[]>`
        SELECT code, name, description
        FROM document_types
        ORDER BY code ASC
      `;

      const documentTypeState = {
        documentTypesByName: new Map(
          currentDocumentTypes.map((item) => [item.name.trim().toLowerCase(), item]),
        ),
        usedCodes: new Set(currentDocumentTypes.map((item) => item.code)),
      };
      const usedRequirementCodes = new Set<string>();

      for (const legacyDocumentType of legacyDocumentTypes) {
        const { code: documentTypeCode, created } = await resolveLegacyDocumentTypeCode(
          transaction,
          legacyDocumentType,
          documentTypeState,
        );

        if (created) {
          documentTypesCreated += 1;
        }

        const requirementCode = buildUniqueCode(
          `neurahub_${normalizeIdentifier(legacyDocumentType.name, 88)}`,
          usedRequirementCodes,
          100,
        );

        await transaction.$executeRaw`
          INSERT INTO workspace_document_library_items (
            id,
            law_firm_id,
            source_code,
            source_reference,
            document_type_code,
            requirement_code,
            display_name,
            description,
            ai_instructions,
            is_required,
            minimum_quantity,
            created_by_user_id,
            created_at
          ) VALUES (
            ${createId()},
            ${input.lawFirmId},
            ${NEURAHUB_DOCUMENT_LIBRARY_SOURCE_CODE},
            ${String(legacyDocumentType.id)},
            ${documentTypeCode},
            ${requirementCode},
            ${legacyDocumentType.name},
            ${legacyDocumentType.documentDetails || null},
            ${legacyDocumentType.aiValidationInstructions || null},
            1,
            1,
            ${input.userId},
            CURRENT_TIMESTAMP
          )
        `;
      }
    });
  } catch (error) {
    if (isMissingWorkspaceDocumentLibraryTableError(error)) {
      throw new Error(
        "Workspace document library table is missing. Run database/mysql/024_workspace_document_library.sql.",
      );
    }

    throw error;
  }

  const importedLibrary = await getImportedDocumentLibrarySummary(input.lawFirmId);

  if (!importedLibrary) {
    throw new Error("Imported neuraHub document library was not found after import.");
  }

  return {
    sourceCount: legacyDocumentTypes.length,
    importedCount: legacyDocumentTypes.length,
    documentTypesCreated,
    documentTypesReused: legacyDocumentTypes.length - documentTypesCreated,
    importedLibrary,
  } satisfies NeuraHubDocumentImportResult;
}

export async function importNeuraHubFormTemplates(input: {
  lawFirmId: string;
  userId: string;
}): Promise<NeuraHubFormImportResult> {
  const legacyTemplates = await listLegacyFormTemplates();

  if (legacyTemplates.length === 0) {
    throw new Error("No active form templates were found in the neuraHub database.");
  }

  const existingTemplates = await prisma.$queryRaw<ExistingFormTemplateRow[]>`
    SELECT id, code, base_pdf_file_id
    FROM form_templates
    WHERE law_firm_id = ${input.lawFirmId}
      AND code LIKE ${`${NEURAHUB_FORM_TEMPLATE_CODE_PREFIX}%`}
  `;
  const dataFields = await prisma.$queryRaw<Array<{ id: string; field_key: string }>>`
    SELECT id, field_key
    FROM data_fields
  `;

  const existingTemplateByCode = new Map(existingTemplates.map((row) => [row.code, row]));
  const dataFieldsByKey = new Map<string, string>(
    dataFields
      .map((row) => [String(row.field_key ?? "").trim(), row.id] as const)
      .filter(([key]) => Boolean(key)),
  );
  const existingImportedTemplateIds = new Set(existingTemplates.map((row) => row.id));
  let createdCount = 0;
  let updatedCount = 0;
  let fieldsImportedCount = 0;
  let withBasePdfCount = 0;

  for (const legacyTemplate of legacyTemplates) {
    const result = await syncImportedLegacyFormTemplate({
      lawFirmId: input.lawFirmId,
      userId: input.userId,
      template: legacyTemplate,
      dataFieldsByKey,
      existingTemplateByCode,
      existingImportedTemplateIds,
    });

    if (result.created) {
      createdCount += 1;
    }

    if (result.updated) {
      updatedCount += 1;
    }

    if (result.hasBasePdf) {
      withBasePdfCount += 1;
    }

    fieldsImportedCount += result.fieldsImportedCount;
  }

  const importedCatalog = await getImportedFormTemplateSummary(input.lawFirmId);

  if (!importedCatalog) {
    throw new Error("Imported neuraHub form templates were not found after import.");
  }

  return {
    sourceCount: legacyTemplates.length,
    importedCount: legacyTemplates.length,
    createdCount,
    updatedCount,
    fieldsImportedCount,
    withBasePdfCount,
    templatesMissingBasePdfCount: Math.max(0, legacyTemplates.length - withBasePdfCount),
    importedCatalog,
  };
}