import { Prisma } from "@prisma/client";
import { prisma } from "./prisma.js";
import { createId } from "./id.js";
import { createAiRun, finishAiRun, runJsonChatCompletion } from "./tenant-ai.js";

const WORKFLOW_TEMPLATE_ANALYSIS_AI_RUN_TYPE = "workflow_blueprint_analysis";
const WORKFLOW_TEMPLATE_ANALYSIS_STATUS_CODES = ["ready", "heuristic", "failed", "pending"] as const;

type WorkflowTemplateAnalysisStatusCode =
  (typeof WORKFLOW_TEMPLATE_ANALYSIS_STATUS_CODES)[number];

type WorkflowAnalysisDocumentSource = {
  id: string;
  requirementCode: string;
  displayName: string;
  documentTypeCode: string;
  description: string | null;
  stepName: string | null;
  minimumQuantity: number;
  isRequired: boolean;
  appliesWhen: string[];
  aiInstructions: string | null;
};

type WorkflowAnalysisQuestionSource =
  | {
      id: string;
      sourceType: "workflow_prompt";
      questionText: string;
      targetType: "client" | "sponsor" | "internal";
      stepName: string | null;
      appliesWhen: string[];
    }
  | {
      id: string;
      sourceType: "form_field";
      targetType: "client";
      formTemplateId: string;
      formCode: string;
      formName: string;
      fieldKey: string;
      label: string;
      instructions: string | null;
      sectionName: string | null;
      appliesWhen: string[];
      isRequired: boolean;
    };

type WorkflowTemplateAnalysisContext = {
  workflowTemplateId: string;
  workflowCode: string;
  workflowName: string;
  caseTypeCode: string;
  caseSubtypeCode: string | null;
  description: string | null;
  requiredDocuments: WorkflowAnalysisDocumentSource[];
  questionSources: WorkflowAnalysisQuestionSource[];
};

export type WorkflowTemplateAnalysisChecklistItem = {
  id: string;
  title: string;
  details: string;
  stepName: string | null;
  requirementCode: string | null;
  documentTypeCode: string | null;
  minimumQuantity: number;
  isRequired: boolean;
  appliesWhen: string[];
  sourceReferenceIds: string[];
  sourceReferences: string[];
};

export type WorkflowTemplateAnalysisQuestionItem = {
  id: string;
  questionText: string;
  rationale: string;
  targetType: "client" | "sponsor" | "internal";
  priority: "critical" | "high" | "normal" | "low";
  isConfirmation: boolean;
  appliesWhen: string[];
  sourceReferenceIds: string[];
  sourceFormNames: string[];
  sourceFieldKeys: string[];
  sourcePromptIds: string[];
  sourcePrompts: string[];
  ambiguityNotes: string[];
};

export type WorkflowTemplateAnalysisAmbiguousQuestion = {
  id: string;
  sourceReferenceIds: string[];
  sourceQuestions: string[];
  reason: string;
  resolvedQuestionText: string | null;
};

export type WorkflowTemplateAnalysisPayload = {
  checklist: WorkflowTemplateAnalysisChecklistItem[];
  uniqueQuestions: WorkflowTemplateAnalysisQuestionItem[];
  ambiguousQuestions: WorkflowTemplateAnalysisAmbiguousQuestion[];
};

export type WorkflowTemplateAnalysisRecord = WorkflowTemplateAnalysisPayload & {
  workflowTemplateId: string;
  statusCode: WorkflowTemplateAnalysisStatusCode;
  sourceMode: "ai" | "heuristic" | "manual" | "none";
  isEdited: boolean;
  generatedAt: string | null;
  errorMessage: string | null;
  summary: {
    documentCount: number;
    uniqueQuestionCount: number;
    ambiguousQuestionCount: number;
  };
};

type WorkflowTemplateAnalysisStorageRow = {
  workflow_template_id: string;
  status_code: string;
  generated_analysis_json: unknown;
  edited_analysis_json: unknown;
  generated_at: Date | string | null;
  error_message: string | null;
};

let ensureWorkflowTemplateAnalysisTablePromise: Promise<void> | null = null;
let ensureWorkflowTemplateAnalysisAiRunTypePromise: Promise<void> | null = null;

function isMysqlDuplicateKeyNameError(error: unknown, keyName: string) {
  if (!(error instanceof Error)) {
    return false;
  }

  return (
    error.message.includes("Duplicate key name") &&
    error.message.includes(keyName)
  );
}

function normalizeText(value: unknown, maxLength = 2000) {
  return String(value ?? "")
    .replace(/\s+/g, " ")
    .trim()
    .slice(0, Math.max(1, maxLength));
}

function normalizeConditionKeys(values: unknown) {
  if (!Array.isArray(values)) {
    return [] as string[];
  }

  return Array.from(
    new Set(
      values
        .map((item) => normalizeText(item, 120).toLowerCase())
        .filter(Boolean),
    ),
  );
}

function normalizeReferenceIds(values: unknown, allowedIds: Set<string>) {
  if (!Array.isArray(values)) {
    return [] as string[];
  }

  return Array.from(
    new Set(
      values
        .map((item) => normalizeText(item, 160))
        .filter((item) => allowedIds.has(item)),
    ),
  );
}

function normalizeQuestionTargetType(
  value: unknown,
): WorkflowTemplateAnalysisQuestionItem["targetType"] {
  const normalized = normalizeText(value, 20).toLowerCase();

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

  return "client";
}

function normalizeQuestionPriority(
  value: unknown,
): WorkflowTemplateAnalysisQuestionItem["priority"] {
  const normalized = normalizeText(value, 20).toLowerCase();

  if (normalized === "critical" || normalized === "high" || normalized === "low") {
    return normalized;
  }

  return "normal";
}

function normalizeAnalysisId(value: unknown, prefix: string, index: number) {
  const normalized = normalizeText(value, 120)
    .toLowerCase()
    .replace(/[^a-z0-9_:-]+/g, "_")
    .replace(/^_+|_+$/g, "");

  return normalized || `${prefix}_${index + 1}`;
}

function parseJsonRecord(value: unknown) {
  if (!value) {
    return null;
  }

  if (typeof value === "string") {
    try {
      return JSON.parse(value) as Record<string, unknown>;
    } catch {
      return null;
    }
  }

  if (typeof value === "object" && value !== null) {
    return value as Record<string, unknown>;
  }

  return null;
}

function extractResponsePrompts(value: unknown) {
  const parsed = parseJsonRecord(value);
  const responsePrompts = Array.isArray(parsed?.responsePrompts)
    ? parsed.responsePrompts
    : [];

  return responsePrompts
    .map((item, index) => {
      const prompt = item as Record<string, unknown>;
      const question = normalizeText(prompt.question, 1000);

      if (!question) {
        return null;
      }

      return {
        id: normalizeAnalysisId(prompt.id, "prompt", index),
        question,
        targetType: normalizeQuestionTargetType(prompt.targetType),
      };
    })
    .filter(
      (
        item,
      ): item is {
        id: string;
        question: string;
        targetType: "client" | "sponsor" | "internal";
      } => item !== null,
    );
}

function extractConditionConfig(value: unknown) {
  const parsed = parseJsonRecord(value);
  return {
    aiInstructions: normalizeText(parsed?.aiInstructions, 6000) || null,
    appliesWhen: normalizeConditionKeys(parsed?.appliesWhen),
  };
}

function trimForPrompt(value: string, maxLength: number) {
  return value.length > maxLength ? `${value.slice(0, maxLength - 1)}…` : value;
}

function buildDefaultChecklistDetails(document: WorkflowAnalysisDocumentSource) {
  const parts = [
    document.description,
    document.stepName ? `Step: ${document.stepName}` : null,
    document.minimumQuantity > 1 ? `Minimum quantity: ${document.minimumQuantity}` : null,
    document.aiInstructions ? trimForPrompt(document.aiInstructions, 280) : null,
  ].filter(Boolean);

  return parts.join(" • ") || "Required to support this workflow.";
}

function buildFormFieldQuestionLabel(source: Extract<WorkflowAnalysisQuestionSource, { sourceType: "form_field" }>) {
  const label = normalizeText(source.label, 255) || normalizeText(source.fieldKey, 120);
  return label.replace(/\s+/g, " ").trim();
}

function buildFallbackQuestionText(source: WorkflowAnalysisQuestionSource) {
  if (source.sourceType === "workflow_prompt") {
    return normalizeText(source.questionText, 1000);
  }

  const label = buildFormFieldQuestionLabel(source);
  if (!label) {
    return "Provide the required information for this form field.";
  }

  const normalizedLabel = label.replace(/[.:?]+$/g, "").trim();
  const questionLead = /^(do|did|have|has|are|is|was|were|will|can|should|what|when|where|which|who|why|how)\b/i.test(
    normalizedLabel,
  )
    ? normalizedLabel
    : `Provide ${normalizedLabel}`;

  return questionLead.endsWith("?") ? questionLead : `${questionLead}.`;
}

function normalizeQuestionConcept(value: string) {
  return value
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .toLowerCase()
    .replace(/\b(please|provide|enter|write|list|the|a|an|your)\b/g, " ")
    .replace(/[^a-z0-9]+/g, " ")
    .replace(/\s+/g, " ")
    .trim();
}

function summarizeQuestionSources(sources: WorkflowAnalysisQuestionSource[]) {
  const formNames = Array.from(
    new Set(
      sources
        .filter((item): item is Extract<WorkflowAnalysisQuestionSource, { sourceType: "form_field" }> =>
          item.sourceType === "form_field",
        )
        .map((item) => item.formName),
    ),
  );
  const promptSources = Array.from(
    new Set(
      sources
        .filter((item): item is Extract<WorkflowAnalysisQuestionSource, { sourceType: "workflow_prompt" }> =>
          item.sourceType === "workflow_prompt",
        )
        .map((item) => item.questionText),
    ),
  );
  const fieldKeys = Array.from(
    new Set(
      sources
        .filter((item): item is Extract<WorkflowAnalysisQuestionSource, { sourceType: "form_field" }> =>
          item.sourceType === "form_field",
        )
        .map((item) => item.fieldKey),
    ),
  );
  const promptIds = Array.from(
    new Set(
      sources
        .filter((item): item is Extract<WorkflowAnalysisQuestionSource, { sourceType: "workflow_prompt" }> =>
          item.sourceType === "workflow_prompt",
        )
        .map((item) => item.id),
    ),
  );
  const appliesWhen = Array.from(
    new Set(sources.flatMap((item) => item.appliesWhen)),
  );

  return {
    formNames,
    promptSources,
    fieldKeys,
    promptIds,
    appliesWhen,
  };
}

function buildHeuristicAnalysis(
  context: WorkflowTemplateAnalysisContext,
): WorkflowTemplateAnalysisPayload {
  const checklist = context.requiredDocuments.map((document, index) => ({
    id: normalizeAnalysisId(document.id, "document", index),
    title: document.displayName,
    details: buildDefaultChecklistDetails(document),
    stepName: document.stepName,
    requirementCode: document.requirementCode,
    documentTypeCode: document.documentTypeCode,
    minimumQuantity: Math.max(1, Number(document.minimumQuantity || 1)),
    isRequired: Boolean(document.isRequired),
    appliesWhen: document.appliesWhen,
    sourceReferenceIds: [document.id],
    sourceReferences: [document.displayName],
  }));

  const groupedSources = new Map<string, WorkflowAnalysisQuestionSource[]>();

  for (const source of context.questionSources) {
    const baseLabel =
      source.sourceType === "workflow_prompt"
        ? source.questionText
        : buildFormFieldQuestionLabel(source);
    const key = normalizeQuestionConcept(baseLabel) || source.id;
    const bucket = groupedSources.get(key) ?? [];
    bucket.push(source);
    groupedSources.set(key, bucket);
  }

  const uniqueQuestions: WorkflowTemplateAnalysisQuestionItem[] = [];
  const ambiguousQuestions: WorkflowTemplateAnalysisAmbiguousQuestion[] = [];

  Array.from(groupedSources.entries()).forEach(([key, sources], index) => {
    const promptSource =
      sources.find((item): item is Extract<WorkflowAnalysisQuestionSource, { sourceType: "workflow_prompt" }> =>
        item.sourceType === "workflow_prompt",
      ) ?? null;
    const leadSource = promptSource ?? sources[0];
    const questionText = buildFallbackQuestionText(leadSource);
    const targetType = leadSource.targetType;
    const summary = summarizeQuestionSources(sources);
    const ambiguityNotes: string[] = [];
    const distinctTexts = Array.from(
      new Set(
        sources.map((item) =>
          item.sourceType === "workflow_prompt"
            ? item.questionText
            : buildFormFieldQuestionLabel(item),
        ),
      ),
    );

    if (distinctTexts.length > 1) {
      ambiguityNotes.push("Multiple workflow prompts or form fields were merged into one question.");
      ambiguousQuestions.push({
        id: `ambiguous_${index + 1}`,
        sourceReferenceIds: sources.map((item) => item.id),
        sourceQuestions: distinctTexts,
        reason: "The workflow uses different wording for the same underlying fact.",
        resolvedQuestionText: questionText,
      });
    }

    uniqueQuestions.push({
      id: `question_${index + 1}`,
      questionText,
      rationale:
        summary.formNames.length > 0 || summary.promptSources.length > 0
          ? [
              summary.formNames.length > 0
                ? `Needed for ${summary.formNames.join(", ")}`
                : null,
              summary.promptSources.length > 0
                ? `Aligned with ${summary.promptSources.length} workflow prompt(s)`
                : null,
            ]
              .filter(Boolean)
              .join(" • ")
          : "Needed to complete this workflow.",
      targetType,
      priority:
        sources.some(
          (item) => item.sourceType === "form_field" && item.isRequired,
        )
          ? "high"
          : "normal",
      isConfirmation: false,
      appliesWhen: summary.appliesWhen,
      sourceReferenceIds: sources.map((item) => item.id),
      sourceFormNames: summary.formNames,
      sourceFieldKeys: summary.fieldKeys,
      sourcePromptIds: summary.promptIds,
      sourcePrompts: summary.promptSources,
      ambiguityNotes,
    });
  });

  return {
    checklist,
    uniqueQuestions,
    ambiguousQuestions,
  };
}

function buildWorkflowAnalysisPrompt(context: WorkflowTemplateAnalysisContext) {
  const documentSources = context.requiredDocuments.map((item) => ({
    id: item.id,
    requirementCode: item.requirementCode,
    displayName: item.displayName,
    documentTypeCode: item.documentTypeCode,
    description: item.description,
    stepName: item.stepName,
    minimumQuantity: item.minimumQuantity,
    isRequired: item.isRequired,
    appliesWhen: item.appliesWhen,
    aiInstructions: item.aiInstructions,
  }));

  const questionSources = context.questionSources.map((item) =>
    item.sourceType === "workflow_prompt"
      ? {
          id: item.id,
          sourceType: item.sourceType,
          questionText: item.questionText,
          targetType: item.targetType,
          stepName: item.stepName,
          appliesWhen: item.appliesWhen,
        }
      : {
          id: item.id,
          sourceType: item.sourceType,
          formTemplateId: item.formTemplateId,
          formCode: item.formCode,
          formName: item.formName,
          fieldKey: item.fieldKey,
          label: item.label,
          instructions: item.instructions,
          sectionName: item.sectionName,
          targetType: item.targetType,
          appliesWhen: item.appliesWhen,
          isRequired: item.isRequired,
        },
  );

  const userPrompt = JSON.stringify(
    {
      workflow: {
        id: context.workflowTemplateId,
        code: context.workflowCode,
        name: context.workflowName,
        caseTypeCode: context.caseTypeCode,
        caseSubtypeCode: context.caseSubtypeCode,
        description: context.description,
      },
      requiredDocuments: documentSources,
      questionSources,
    },
    null,
    2,
  );

  return {
    systemPrompt: [
      "You are a senior legal workflow analyst for immigration law firms.",
      "Analyze a workflow template and return only valid JSON.",
      "Goals:",
      "1. Build a concise checklist of every required document for the workflow.",
      "2. Review workflow prompts and form-field requirements.",
      "3. Detect overlapping, redundant, vague, or ambiguous questions.",
      "4. Rewrite them into the minimum set of unique client-facing questions needed to complete the workflow.",
      "Rules:",
      "- Use only the provided sourceReferenceIds. Do not invent source ids.",
      "- Merge duplicate questions when multiple prompts or form fields point to the same fact.",
      "- Rewrite vague questions into clear, direct language.",
      "- Keep questions generic for a workflow template. Do not assume a specific client.",
      "- A question can target client, sponsor, or internal.",
      "- Document checklist entries must preserve every required document, even if merged.",
      "- If multiple document sources represent the same evidence, merge them into one checklist item and include all sourceReferenceIds.",
      "- ambiguousQuestions must explain which original sources were ambiguous and how they were resolved.",
      "Return JSON with this shape:",
      "{",
      '  "documentChecklist": [{',
      '    "id": "string",',
      '    "title": "string",',
      '    "details": "string",',
      '    "sourceReferenceIds": ["required_document_source_id"],',
      '    "appliesWhen": ["condition_key"],',
      '    "stepName": "string|null",',
      '    "minimumQuantity": 1,',
      '    "isRequired": true',
      "  }],",
      '  "uniqueQuestions": [{',
      '    "id": "string",',
      '    "questionText": "string",',
      '    "rationale": "string",',
      '    "targetType": "client|sponsor|internal",',
      '    "priority": "critical|high|normal|low",',
      '    "isConfirmation": false,',
      '    "sourceReferenceIds": ["question_source_id"],',
      '    "appliesWhen": ["condition_key"],',
      '    "ambiguityNotes": ["string"]',
      "  }],",
      '  "ambiguousQuestions": [{',
      '    "id": "string",',
      '    "sourceReferenceIds": ["question_source_id"],',
      '    "sourceQuestions": ["original wording"],',
      '    "reason": "string",',
      '    "resolvedQuestionText": "string|null"',
      "  }]",
      "}",
    ].join("\n"),
    userPrompt,
  };
}

function buildDocumentLookup(context: WorkflowTemplateAnalysisContext) {
  return new Map(context.requiredDocuments.map((item) => [item.id, item]));
}

function buildQuestionSourceLookup(context: WorkflowTemplateAnalysisContext) {
  return new Map(context.questionSources.map((item) => [item.id, item]));
}

function resolveChecklistItemsFromAi(
  value: unknown,
  context: WorkflowTemplateAnalysisContext,
  heuristic: WorkflowTemplateAnalysisPayload,
) {
  const documentLookup = buildDocumentLookup(context);
  const allowedIds = new Set(documentLookup.keys());
  const items = Array.isArray(value) ? value : [];
  const normalizedItems = items
    .map((item, index): WorkflowTemplateAnalysisChecklistItem | null => {
      const row = item as Record<string, unknown>;
      const sourceReferenceIds = normalizeReferenceIds(row.sourceReferenceIds, allowedIds);
      const linkedDocuments = sourceReferenceIds
        .map((id) => documentLookup.get(id))
        .filter((linked): linked is WorkflowAnalysisDocumentSource => Boolean(linked));

      if (!linkedDocuments.length) {
        return null;
      }
      const normalizedItem: WorkflowTemplateAnalysisChecklistItem = {
        id: normalizeAnalysisId(row.id, "document", index),
        title: normalizeText(row.title, 255) || linkedDocuments[0].displayName,
        details:
          normalizeText(row.details, 2000) || buildDefaultChecklistDetails(linkedDocuments[0]),
        stepName: normalizeText(row.stepName, 255) || linkedDocuments.find((doc) => doc.stepName)?.stepName || null,
        requirementCode: linkedDocuments[0].requirementCode,
        documentTypeCode: linkedDocuments[0].documentTypeCode,
        minimumQuantity: Math.max(
          1,
          Number(row.minimumQuantity || Math.max(...linkedDocuments.map((doc) => doc.minimumQuantity))),
        ),
        isRequired:
          typeof row.isRequired === "boolean"
            ? row.isRequired
            : linkedDocuments.some((doc) => doc.isRequired),
        appliesWhen:
          normalizeConditionKeys(row.appliesWhen).length > 0
            ? normalizeConditionKeys(row.appliesWhen)
            : Array.from(new Set(linkedDocuments.flatMap((doc) => doc.appliesWhen))),
        sourceReferenceIds,
        sourceReferences: linkedDocuments.map((doc) => doc.displayName),
      };

      return normalizedItem;
    })
    .filter((item): item is WorkflowTemplateAnalysisChecklistItem => item !== null);

  const coveredIds = new Set(normalizedItems.flatMap((item) => item.sourceReferenceIds));
  const missing = heuristic.checklist.filter(
    (item) => !item.sourceReferenceIds.every((id) => coveredIds.has(id)),
  );

  return [...normalizedItems, ...missing];
}

function resolveQuestionItemsFromAi(
  value: unknown,
  context: WorkflowTemplateAnalysisContext,
  heuristic: WorkflowTemplateAnalysisPayload,
) {
  const sourceLookup = buildQuestionSourceLookup(context);
  const allowedIds = new Set(sourceLookup.keys());
  const items = Array.isArray(value) ? value : [];
  const normalizedItems = items
    .map((item, index) => {
      const row = item as Record<string, unknown>;
      const sourceReferenceIds = normalizeReferenceIds(row.sourceReferenceIds, allowedIds);
      const linkedSources = sourceReferenceIds
        .map((id) => sourceLookup.get(id))
        .filter((linked): linked is WorkflowAnalysisQuestionSource => Boolean(linked));

      if (!linkedSources.length) {
        return null;
      }

      const summary = summarizeQuestionSources(linkedSources);
      const targetType =
        normalizeQuestionTargetType(row.targetType) ||
        linkedSources[0]?.targetType ||
        "client";

      return {
        id: normalizeAnalysisId(row.id, "question", index),
        questionText:
          normalizeText(row.questionText, 1000) || buildFallbackQuestionText(linkedSources[0]),
        rationale:
          normalizeText(row.rationale, 2000) ||
          heuristic.uniqueQuestions.find((entry) =>
            entry.sourceReferenceIds.some((id) => sourceReferenceIds.includes(id)),
          )?.rationale ||
          "Needed to complete this workflow.",
        targetType,
        priority: normalizeQuestionPriority(row.priority),
        isConfirmation: Boolean(row.isConfirmation),
        appliesWhen:
          normalizeConditionKeys(row.appliesWhen).length > 0
            ? normalizeConditionKeys(row.appliesWhen)
            : summary.appliesWhen,
        sourceReferenceIds,
        sourceFormNames: summary.formNames,
        sourceFieldKeys: summary.fieldKeys,
        sourcePromptIds: summary.promptIds,
        sourcePrompts: summary.promptSources,
        ambiguityNotes: Array.isArray(row.ambiguityNotes)
          ? row.ambiguityNotes.map((note) => normalizeText(note, 1000)).filter(Boolean)
          : [],
      } satisfies WorkflowTemplateAnalysisQuestionItem;
    })
    .filter((item): item is WorkflowTemplateAnalysisQuestionItem => item !== null);

  const coveredIds = new Set(normalizedItems.flatMap((item) => item.sourceReferenceIds));
  const missing = heuristic.uniqueQuestions.filter(
    (item) => !item.sourceReferenceIds.every((id) => coveredIds.has(id)),
  );

  return [...normalizedItems, ...missing];
}

function resolveAmbiguousItemsFromAi(
  value: unknown,
  context: WorkflowTemplateAnalysisContext,
  heuristic: WorkflowTemplateAnalysisPayload,
) {
  const sourceLookup = buildQuestionSourceLookup(context);
  const allowedIds = new Set(sourceLookup.keys());
  const items = Array.isArray(value) ? value : [];
  const normalizedItems = items
    .map((item, index) => {
      const row = item as Record<string, unknown>;
      const sourceReferenceIds = normalizeReferenceIds(row.sourceReferenceIds, allowedIds);
      const linkedSources = sourceReferenceIds
        .map((id) => sourceLookup.get(id))
        .filter((linked): linked is WorkflowAnalysisQuestionSource => Boolean(linked));

      if (!linkedSources.length) {
        return null;
      }

      const sourceQuestions =
        Array.isArray(row.sourceQuestions) && row.sourceQuestions.length > 0
          ? row.sourceQuestions.map((entry) => normalizeText(entry, 1000)).filter(Boolean)
          : linkedSources.map((entry) =>
              entry.sourceType === "workflow_prompt"
                ? entry.questionText
                : buildFormFieldQuestionLabel(entry),
            );

      return {
        id: normalizeAnalysisId(row.id, "ambiguous", index),
        sourceReferenceIds,
        sourceQuestions,
        reason:
          normalizeText(row.reason, 1500) ||
          "The workflow contains overlapping prompts for the same fact.",
        resolvedQuestionText: normalizeText(row.resolvedQuestionText, 1000) || null,
      } satisfies WorkflowTemplateAnalysisAmbiguousQuestion;
    })
    .filter((item): item is WorkflowTemplateAnalysisAmbiguousQuestion => item !== null);

  if (normalizedItems.length > 0) {
    return normalizedItems;
  }

  return heuristic.ambiguousQuestions;
}

function normalizeWorkflowTemplateAnalysisPayload(
  value: unknown,
  fallback: WorkflowTemplateAnalysisPayload,
  context?: WorkflowTemplateAnalysisContext,
) {
  const parsed = parseJsonRecord(value);

  if (!parsed || !context) {
    return fallback;
  }

  return {
    checklist: resolveChecklistItemsFromAi(parsed.documentChecklist, context, fallback),
    uniqueQuestions: resolveQuestionItemsFromAi(parsed.uniqueQuestions, context, fallback),
    ambiguousQuestions: resolveAmbiguousItemsFromAi(parsed.ambiguousQuestions, context, fallback),
  };
}

function normalizeStoredWorkflowTemplateAnalysisPayload(
  value: unknown,
): WorkflowTemplateAnalysisPayload | null {
  const parsed = parseJsonRecord(value);

  if (!parsed) {
    return null;
  }

  const checklist = Array.isArray(parsed.checklist)
    ? parsed.checklist
        .map((item, index) => {
          const row = item as Record<string, unknown>;
          const title = normalizeText(row.title, 255);

          if (!title) {
            return null;
          }

          return {
            id: normalizeAnalysisId(row.id, "document", index),
            title,
            details: normalizeText(row.details, 2000),
            stepName: normalizeText(row.stepName, 255) || null,
            requirementCode: normalizeText(row.requirementCode, 100) || null,
            documentTypeCode: normalizeText(row.documentTypeCode, 50) || null,
            minimumQuantity: Math.max(1, Number(row.minimumQuantity || 1)),
            isRequired: row.isRequired !== false,
            appliesWhen: normalizeConditionKeys(row.appliesWhen),
            sourceReferenceIds: Array.isArray(row.sourceReferenceIds)
              ? row.sourceReferenceIds.map((entry) => normalizeText(entry, 160)).filter(Boolean)
              : [],
            sourceReferences: Array.isArray(row.sourceReferences)
              ? row.sourceReferences.map((entry) => normalizeText(entry, 255)).filter(Boolean)
              : [],
          } satisfies WorkflowTemplateAnalysisChecklistItem;
        })
        .filter((item): item is WorkflowTemplateAnalysisChecklistItem => item !== null)
    : [];

  const uniqueQuestions = Array.isArray(parsed.uniqueQuestions)
    ? parsed.uniqueQuestions
        .map((item, index) => {
          const row = item as Record<string, unknown>;
          const questionText = normalizeText(row.questionText, 1000);

          if (!questionText) {
            return null;
          }

          return {
            id: normalizeAnalysisId(row.id, "question", index),
            questionText,
            rationale: normalizeText(row.rationale, 2000),
            targetType: normalizeQuestionTargetType(row.targetType),
            priority: normalizeQuestionPriority(row.priority),
            isConfirmation: Boolean(row.isConfirmation),
            appliesWhen: normalizeConditionKeys(row.appliesWhen),
            sourceReferenceIds: Array.isArray(row.sourceReferenceIds)
              ? row.sourceReferenceIds.map((entry) => normalizeText(entry, 160)).filter(Boolean)
              : [],
            sourceFormNames: Array.isArray(row.sourceFormNames)
              ? row.sourceFormNames.map((entry) => normalizeText(entry, 255)).filter(Boolean)
              : [],
            sourceFieldKeys: Array.isArray(row.sourceFieldKeys)
              ? row.sourceFieldKeys.map((entry) => normalizeText(entry, 120)).filter(Boolean)
              : [],
            sourcePromptIds: Array.isArray(row.sourcePromptIds)
              ? row.sourcePromptIds.map((entry) => normalizeText(entry, 120)).filter(Boolean)
              : [],
            sourcePrompts: Array.isArray(row.sourcePrompts)
              ? row.sourcePrompts.map((entry) => normalizeText(entry, 1000)).filter(Boolean)
              : [],
            ambiguityNotes: Array.isArray(row.ambiguityNotes)
              ? row.ambiguityNotes.map((entry) => normalizeText(entry, 1000)).filter(Boolean)
              : [],
          } satisfies WorkflowTemplateAnalysisQuestionItem;
        })
        .filter((item): item is WorkflowTemplateAnalysisQuestionItem => item !== null)
    : [];

  const ambiguousQuestions = Array.isArray(parsed.ambiguousQuestions)
    ? parsed.ambiguousQuestions
        .map((item, index) => {
          const row = item as Record<string, unknown>;
          const reason = normalizeText(row.reason, 1500);

          if (!reason) {
            return null;
          }

          return {
            id: normalizeAnalysisId(row.id, "ambiguous", index),
            sourceReferenceIds: Array.isArray(row.sourceReferenceIds)
              ? row.sourceReferenceIds.map((entry) => normalizeText(entry, 160)).filter(Boolean)
              : [],
            sourceQuestions: Array.isArray(row.sourceQuestions)
              ? row.sourceQuestions.map((entry) => normalizeText(entry, 1000)).filter(Boolean)
              : [],
            reason,
            resolvedQuestionText: normalizeText(row.resolvedQuestionText, 1000) || null,
          } satisfies WorkflowTemplateAnalysisAmbiguousQuestion;
        })
        .filter((item): item is WorkflowTemplateAnalysisAmbiguousQuestion => item !== null)
    : [];

  return {
    checklist,
    uniqueQuestions,
    ambiguousQuestions,
  };
}

function buildAnalysisSummary(payload: WorkflowTemplateAnalysisPayload) {
  return {
    documentCount: payload.checklist.length,
    uniqueQuestionCount: payload.uniqueQuestions.length,
    ambiguousQuestionCount: payload.ambiguousQuestions.length,
  };
}

function resolveWorkflowTemplateAnalysisRecord(
  row: WorkflowTemplateAnalysisStorageRow | null,
  workflowTemplateId: string,
): WorkflowTemplateAnalysisRecord | null {
  if (!row) {
    return null;
  }

  const edited = normalizeStoredWorkflowTemplateAnalysisPayload(row.edited_analysis_json);
  const generated = normalizeStoredWorkflowTemplateAnalysisPayload(row.generated_analysis_json);
  const payload = edited ?? generated;

  if (!payload) {
    return null;
  }

  const statusCode = WORKFLOW_TEMPLATE_ANALYSIS_STATUS_CODES.includes(
    row.status_code as WorkflowTemplateAnalysisStatusCode,
  )
    ? (row.status_code as WorkflowTemplateAnalysisStatusCode)
    : "pending";
  const isEdited = Boolean(edited);
  const sourceMode =
    isEdited
      ? "manual"
      : statusCode === "heuristic"
        ? "heuristic"
        : generated
          ? "ai"
          : "none";

  return {
    workflowTemplateId,
    statusCode,
    sourceMode,
    isEdited,
    generatedAt: row.generated_at ? new Date(row.generated_at).toISOString() : null,
    errorMessage: row.error_message,
    ...payload,
    summary: buildAnalysisSummary(payload),
  };
}

async function loadWorkflowTemplateAnalysisContext(input: {
  lawFirmId: string;
  workflowTemplateId: string;
}) {
  const [workflowTemplate] = await prisma.$queryRaw<
    Array<{
      id: string;
      code: string;
      name: string;
      case_type_code: string;
      case_subtype_code: string | null;
      description: string | null;
      law_firm_id: string | null;
      is_system_template: number;
    }>
  >`
    SELECT
      id,
      code,
      name,
      case_type_code,
      case_subtype_code,
      description,
      law_firm_id,
      is_system_template
    FROM workflow_templates
    WHERE id = ${input.workflowTemplateId}
      AND (law_firm_id = ${input.lawFirmId} OR law_firm_id IS NULL)
    LIMIT 1
  `;

  if (!workflowTemplate) {
    throw new Error("Workflow template not found.");
  }

  const steps = await prisma.$queryRaw<
    Array<{
      id: string;
      step_code: string;
      name: string;
      step_order: number;
      condition_expression_json: unknown;
      completion_rule_json: unknown;
    }>
  >`
    SELECT
      id,
      step_code,
      name,
      step_order,
      condition_expression_json,
      completion_rule_json
    FROM workflow_steps
    WHERE workflow_template_id = ${input.workflowTemplateId}
      AND retired_at IS NULL
    ORDER BY step_order ASC, created_at ASC
  `;
  const stepNameById = new Map(steps.map((item) => [item.id, item.name]));

  const requiredDocuments = await prisma.$queryRaw<
    Array<{
      id: string;
      requirement_code: string;
      display_name: string;
      document_type_code: string;
      description: string | null;
      workflow_step_id: string | null;
      minimum_quantity: number;
      is_required: number;
      condition_expression_json: unknown;
    }>
  >`
    SELECT
      id,
      requirement_code,
      display_name,
      document_type_code,
      description,
      workflow_step_id,
      minimum_quantity,
      is_required,
      condition_expression_json
    FROM workflow_required_documents
    WHERE workflow_template_id = ${input.workflowTemplateId}
      AND retired_at IS NULL
    ORDER BY created_at ASC
  `;

  const requiredForms = await prisma.$queryRaw<
    Array<{
      id: string;
      form_template_id: string;
      requirement_code: string;
      workflow_step_id: string | null;
      condition_expression_json: unknown;
      is_required: number;
      form_name: string;
      form_code: string;
    }>
  >`
    SELECT
      wrf.id,
      wrf.form_template_id,
      wrf.requirement_code,
      wrf.workflow_step_id,
      wrf.condition_expression_json,
      wrf.is_required,
      ft.name AS form_name,
      ft.code AS form_code
    FROM workflow_required_forms wrf
    JOIN form_templates ft ON ft.id = wrf.form_template_id
    WHERE wrf.workflow_template_id = ${input.workflowTemplateId}
      AND wrf.retired_at IS NULL
    ORDER BY wrf.created_at ASC
  `;

  const formTemplateIds = Array.from(new Set(requiredForms.map((item) => item.form_template_id)));
  const formFields = formTemplateIds.length
    ? await prisma.$queryRaw<
        Array<{
          form_template_id: string;
          field_key: string;
          label: string;
          section_name: string | null;
          instructions: string | null;
          is_required: number;
          condition_expression_json: unknown;
          page_number: number | null;
        }>
      >`
        SELECT
          form_template_id,
          field_key,
          label,
          section_name,
          instructions,
          is_required,
          condition_expression_json,
          page_number
        FROM form_fields
        WHERE form_template_id IN (${Prisma.join(formTemplateIds)})
          AND (
            is_required = 1
            OR instructions IS NOT NULL
            OR condition_expression_json IS NOT NULL
          )
        ORDER BY form_template_id ASC, page_number ASC, section_name ASC, label ASC
      `
    : [];

  const questionSources: WorkflowAnalysisQuestionSource[] = [];

  steps.forEach((step) => {
    const condition = extractConditionConfig(step.condition_expression_json);
    extractResponsePrompts(step.completion_rule_json).forEach((prompt, index) => {
      questionSources.push({
        id: `prompt:${step.step_code}:${prompt.id || index + 1}`,
        sourceType: "workflow_prompt",
        questionText: prompt.question,
        targetType: prompt.targetType,
        stepName: step.name,
        appliesWhen: condition.appliesWhen,
      });
    });
  });

  const requiredFormByTemplateId = new Map<
    string,
    Array<{
      formName: string;
      formCode: string;
      appliesWhen: string[];
      isRequired: boolean;
    }>
  >();
  for (const requiredForm of requiredForms) {
    const config = extractConditionConfig(requiredForm.condition_expression_json);
    const bucket = requiredFormByTemplateId.get(requiredForm.form_template_id) ?? [];
    bucket.push({
      formName: requiredForm.form_name,
      formCode: requiredForm.form_code,
      appliesWhen: config.appliesWhen,
      isRequired: Boolean(requiredForm.is_required),
    });
    requiredFormByTemplateId.set(requiredForm.form_template_id, bucket);
  }

  formFields.forEach((field) => {
    const formBindings = requiredFormByTemplateId.get(field.form_template_id) ?? [];
    if (formBindings.length === 0) {
      return;
    }

    const normalizedInstructions = normalizeText(field.instructions, 500);
    formBindings.forEach((binding) => {
      questionSources.push({
        id: `field:${binding.formCode}:${field.field_key}`,
        sourceType: "form_field",
        targetType: "client",
        formTemplateId: field.form_template_id,
        formCode: binding.formCode,
        formName: binding.formName,
        fieldKey: field.field_key,
        label: normalizeText(field.label, 255),
        instructions: normalizedInstructions || null,
        sectionName: normalizeText(field.section_name, 255) || null,
        appliesWhen: binding.appliesWhen,
        isRequired: Boolean(field.is_required) || binding.isRequired,
      });
    });
  });

  return {
    workflowTemplateId: workflowTemplate.id,
    workflowCode: workflowTemplate.code,
    workflowName: workflowTemplate.name,
    caseTypeCode: workflowTemplate.case_type_code,
    caseSubtypeCode: workflowTemplate.case_subtype_code,
    description: workflowTemplate.description,
    requiredDocuments: requiredDocuments.map((item) => {
      const condition = extractConditionConfig(item.condition_expression_json);
      return {
        id: `document:${item.id}`,
        requirementCode: item.requirement_code,
        displayName: item.display_name,
        documentTypeCode: item.document_type_code,
        description: item.description,
        stepName: item.workflow_step_id ? stepNameById.get(item.workflow_step_id) ?? null : null,
        minimumQuantity: item.minimum_quantity,
        isRequired: Boolean(item.is_required),
        appliesWhen: condition.appliesWhen,
        aiInstructions: condition.aiInstructions,
      } satisfies WorkflowAnalysisDocumentSource;
    }),
    questionSources,
  } satisfies WorkflowTemplateAnalysisContext;
}

async function upsertWorkflowTemplateAnalysisRow(input: {
  lawFirmId: string;
  workflowTemplateId: string;
  aiRunId?: string | null;
  statusCode: WorkflowTemplateAnalysisStatusCode;
  generatedPayload?: WorkflowTemplateAnalysisPayload | null;
  editedPayload?: WorkflowTemplateAnalysisPayload | null;
  errorMessage?: string | null;
  clearEdited?: boolean;
}) {
  await ensureWorkflowTemplateAiAnalysisTable();

  const analysisId = createId();
  await prisma.$executeRaw`
    INSERT INTO workflow_template_ai_analyses (
      id,
      law_firm_id,
      workflow_template_id,
      ai_run_id,
      status_code,
      generated_analysis_json,
      edited_analysis_json,
      error_message,
      generated_at,
      created_at,
      updated_at
    ) VALUES (
      ${analysisId},
      ${input.lawFirmId},
      ${input.workflowTemplateId},
      ${input.aiRunId ?? null},
      ${input.statusCode},
      ${input.generatedPayload ? JSON.stringify(input.generatedPayload) : null},
      ${input.editedPayload ? JSON.stringify(input.editedPayload) : null},
      ${input.errorMessage ?? null},
      ${input.generatedPayload ? new Date() : null},
      CURRENT_TIMESTAMP,
      CURRENT_TIMESTAMP
    )
    ON DUPLICATE KEY UPDATE
      ai_run_id = VALUES(ai_run_id),
      status_code = VALUES(status_code),
      generated_analysis_json = COALESCE(VALUES(generated_analysis_json), generated_analysis_json),
      edited_analysis_json = ${
        input.clearEdited
          ? null
          : input.editedPayload
            ? Prisma.sql`VALUES(edited_analysis_json)`
            : Prisma.sql`edited_analysis_json`
      },
      error_message = VALUES(error_message),
      generated_at = COALESCE(VALUES(generated_at), generated_at),
      updated_at = CURRENT_TIMESTAMP
  `;
}

export async function ensureWorkflowTemplateAiAnalysisTable() {
  if (!ensureWorkflowTemplateAnalysisTablePromise) {
    ensureWorkflowTemplateAnalysisTablePromise = (async () => {
      await prisma.$executeRawUnsafe(`
        CREATE TABLE IF NOT EXISTS workflow_template_ai_analyses (
          id CHAR(36) NOT NULL PRIMARY KEY,
          law_firm_id CHAR(36) NOT NULL,
          workflow_template_id CHAR(36) NOT NULL,
          ai_run_id CHAR(36) NULL,
          status_code VARCHAR(50) NOT NULL DEFAULT 'pending',
          generated_analysis_json JSON NULL,
          edited_analysis_json JSON NULL,
          error_message TEXT NULL,
          generated_at DATETIME NULL,
          created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
          deleted_at TIMESTAMP NULL DEFAULT NULL,
          UNIQUE KEY uq_workflow_template_ai_analyses_scope (law_firm_id, workflow_template_id),
          KEY idx_workflow_template_ai_analyses_template (workflow_template_id),
          KEY idx_workflow_template_ai_analyses_law_firm (law_firm_id, status_code, updated_at),
          KEY idx_workflow_template_ai_analyses_ai_run (ai_run_id),
          CONSTRAINT fk_workflow_template_ai_analyses_law_firm
            FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
          CONSTRAINT fk_workflow_template_ai_analyses_workflow
            FOREIGN KEY (workflow_template_id) REFERENCES workflow_templates (id),
          CONSTRAINT fk_workflow_template_ai_analyses_ai_run
            FOREIGN KEY (ai_run_id) REFERENCES ai_runs (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      `);
      const existingIndexes = await prisma.$queryRaw<
        Array<{ index_name: string }>
      >`
        SELECT index_name
        FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = ${"workflow_template_ai_analyses"}
          AND index_name IN (${Prisma.join([
            "uq_workflow_template_ai_analyses_template",
            "uq_workflow_template_ai_analyses_scope",
            "idx_workflow_template_ai_analyses_template",
          ])})
        GROUP BY index_name
      `;
      const indexNames = new Set(existingIndexes.map((item) => item.index_name));

      if (!indexNames.has("idx_workflow_template_ai_analyses_template")) {
        try {
          await prisma.$executeRawUnsafe(`
            ALTER TABLE workflow_template_ai_analyses
            ADD KEY idx_workflow_template_ai_analyses_template (workflow_template_id)
          `);
        } catch (error) {
          if (!isMysqlDuplicateKeyNameError(error, "idx_workflow_template_ai_analyses_template")) {
            throw error;
          }
        }
      }

      if (indexNames.has("uq_workflow_template_ai_analyses_template")) {
        await prisma.$executeRawUnsafe(`
          ALTER TABLE workflow_template_ai_analyses
          DROP INDEX uq_workflow_template_ai_analyses_template
        `);
      }

      if (!indexNames.has("uq_workflow_template_ai_analyses_scope")) {
        try {
          await prisma.$executeRawUnsafe(`
            ALTER TABLE workflow_template_ai_analyses
            ADD UNIQUE KEY uq_workflow_template_ai_analyses_scope (law_firm_id, workflow_template_id)
          `);
        } catch (error) {
          if (!isMysqlDuplicateKeyNameError(error, "uq_workflow_template_ai_analyses_scope")) {
            throw error;
          }
        }
      }
    })().catch((error) => {
      ensureWorkflowTemplateAnalysisTablePromise = null;
      throw error;
    });
  }

  await ensureWorkflowTemplateAnalysisTablePromise;
}

export async function ensureWorkflowTemplateAnalysisAiRunType() {
  if (!ensureWorkflowTemplateAnalysisAiRunTypePromise) {
    ensureWorkflowTemplateAnalysisAiRunTypePromise = (async () => {
      await prisma.$executeRaw`
        INSERT IGNORE INTO ai_run_types (code, name, description, billable_default)
        VALUES (
          ${WORKFLOW_TEMPLATE_ANALYSIS_AI_RUN_TYPE},
          ${"Workflow Blueprint Analysis"},
          ${"Analyze workflow templates and generate document checklists plus unique intake questions"},
          1
        )
      `;
    })().catch((error) => {
      ensureWorkflowTemplateAnalysisAiRunTypePromise = null;
      throw error;
    });
  }

  await ensureWorkflowTemplateAnalysisAiRunTypePromise;
}

export async function listWorkflowTemplateAiAnalyses(input: {
  lawFirmId: string;
  workflowTemplateIds: string[];
}) {
  await ensureWorkflowTemplateAiAnalysisTable();

  if (input.workflowTemplateIds.length === 0) {
    return new Map<string, WorkflowTemplateAnalysisRecord>();
  }

  const rows = await prisma.$queryRaw<WorkflowTemplateAnalysisStorageRow[]>`
    SELECT
      workflow_template_id,
      status_code,
      generated_analysis_json,
      edited_analysis_json,
      generated_at,
      error_message
    FROM workflow_template_ai_analyses
    WHERE law_firm_id = ${input.lawFirmId}
      AND workflow_template_id IN (${Prisma.join(input.workflowTemplateIds)})
      AND deleted_at IS NULL
  `;

  return new Map(
    rows
      .map((row) =>
        resolveWorkflowTemplateAnalysisRecord(row, row.workflow_template_id),
      )
      .filter((item): item is WorkflowTemplateAnalysisRecord => Boolean(item))
      .map((item) => [item.workflowTemplateId, item]),
  );
}

export async function getWorkflowTemplateAiAnalysis(input: {
  lawFirmId: string;
  workflowTemplateId: string;
}) {
  const analyses = await listWorkflowTemplateAiAnalyses({
    lawFirmId: input.lawFirmId,
    workflowTemplateIds: [input.workflowTemplateId],
  });

  return analyses.get(input.workflowTemplateId) ?? null;
}

export async function regenerateWorkflowTemplateAiAnalysis(input: {
  lawFirmId: string;
  workflowTemplateId: string;
}) {
  const context = await loadWorkflowTemplateAnalysisContext(input);
  const heuristicPayload = buildHeuristicAnalysis(context);
  let aiRunId: string | null = null;
  let statusCode: WorkflowTemplateAnalysisStatusCode = "heuristic";
  let errorMessage: string | null = null;
  let generatedPayload = heuristicPayload;

  try {
    await ensureWorkflowTemplateAnalysisAiRunType();
    const aiRun = await createAiRun({
      lawFirmId: input.lawFirmId,
      runType: WORKFLOW_TEMPLATE_ANALYSIS_AI_RUN_TYPE,
      status: "running",
    });
    aiRunId = aiRun.id;

    const prompts = buildWorkflowAnalysisPrompt(context);
    const aiResponse = await runJsonChatCompletion({
      lawFirmId: input.lawFirmId,
      systemPrompt: prompts.systemPrompt,
      userPrompt: prompts.userPrompt,
      maxCompletionTokens: 3500,
    });

    generatedPayload = normalizeWorkflowTemplateAnalysisPayload(
      aiResponse.json,
      heuristicPayload,
      context,
    );
    statusCode = "ready";

    await finishAiRun({
      aiRunId,
      status: "completed",
      inputTokens: aiResponse.usage.inputTokens,
      outputTokens: aiResponse.usage.outputTokens,
      estimatedCost: 0,
    });
  } catch (error) {
    errorMessage = error instanceof Error ? error.message : "Unable to generate workflow analysis.";
    statusCode = heuristicPayload.uniqueQuestions.length > 0 || heuristicPayload.checklist.length > 0
      ? "heuristic"
      : "failed";

    if (aiRunId) {
      await finishAiRun({
        aiRunId,
        status: "failed",
        estimatedCost: 0,
        errorMessage,
      });
    }
  }

  await upsertWorkflowTemplateAnalysisRow({
    lawFirmId: input.lawFirmId,
    workflowTemplateId: input.workflowTemplateId,
    aiRunId,
    statusCode,
    generatedPayload,
    errorMessage,
    clearEdited: true,
  });

  return getWorkflowTemplateAiAnalysis(input);
}

export async function saveWorkflowTemplateAiAnalysisEdits(input: {
  lawFirmId: string;
  workflowTemplateId: string;
  analysis: WorkflowTemplateAnalysisPayload;
}) {
  await upsertWorkflowTemplateAnalysisRow({
    lawFirmId: input.lawFirmId,
    workflowTemplateId: input.workflowTemplateId,
    statusCode: "ready",
    editedPayload: input.analysis,
    errorMessage: null,
  });

  return getWorkflowTemplateAiAnalysis(input);
}