import type { FastifyInstance, FastifyReply, FastifyRequest } from "fastify";
import { Prisma } from "@prisma/client";
import { z } from "zod";
import { requireSession } from "../../lib/auth.js";
import { writeAuditLog } from "../../lib/audit.js";
import { createId } from "../../lib/id.js";
import {
  isWorkspaceLibraryWorkflowTemplateCode,
  listWorkspaceDocumentLibraryItems,
} from "../../lib/neurahub.js";
import { prisma } from "../../lib/prisma.js";
import { getSessionProfile } from "../../lib/session.js";

type WorkflowConditionKey = string;
const workflowFinalPacketPartyTypes = [
  "client",
  "sponsor",
  "internal",
  "dependents",
] as const;
type WorkflowFinalPacketPartyType = (typeof workflowFinalPacketPartyTypes)[number];
const workflowConditionKeySchema = z.string().min(1).max(120);

const workflowTemplateSchema = z.object({
  code: z.string().min(2).max(50),
  name: z.string().min(2).max(255),
  caseTypeCode: z.string().min(2).max(50),
  caseSubtypeCode: z.string().max(50).optional().or(z.literal("")),
  description: z.string().max(1000).optional().or(z.literal("")),
});

const workflowStepSchema = z.object({
  stepCode: z.string().min(2).max(50),
  name: z.string().min(2).max(255),
  stepOrder: z.coerce.number().int().positive(),
  stepType: z.string().min(2).max(50).default("task"),
  description: z.string().max(1000).optional().or(z.literal("")),
  appliesWhen: z.array(workflowConditionKeySchema).max(16).optional().default([]),
  responsePrompts: z
    .array(
      z.object({
        id: z.string().optional(),
        question: z.string().min(1).max(1000),
        targetType: z.enum(["client", "sponsor", "internal"]).default("client"),
      }),
    )
    .max(50)
    .optional()
    .default([]),
});

const requiredDocumentSchema = z.object({
  workflowStepId: z.string().uuid().optional().nullable(),
  documentTypeCode: z.string().min(2).max(50),
  requirementCode: z.string().min(2).max(100),
  displayName: z.string().min(2).max(255),
  description: z.string().max(1000).optional().or(z.literal("")),
  aiInstructions: z.string().max(2000).optional().or(z.literal("")),
  appliesWhen: z.array(workflowConditionKeySchema).max(16).optional().default([]),
  isRequired: z.boolean().default(true),
  minimumQuantity: z.coerce.number().int().positive().default(1),
});

const requiredFormSchema = z.object({
  workflowStepId: z.string().uuid().optional().nullable(),
  formTemplateId: z.string().uuid(),
  requirementCode: z.string().min(2).max(100),
  appliesWhen: z.array(workflowConditionKeySchema).max(16).optional().default([]),
  isRequired: z.boolean().default(true),
});

const workflowFinalPacketItemSchema = z.object({
  sourceType: z.enum(["document", "form", "condition"]),
  sourceClientId: z.string().min(1).max(120),
  partyType: z.enum(workflowFinalPacketPartyTypes).default("client"),
  sourceDisplayName: z.string().max(255).optional().or(z.literal("")),
  sortOrder: z.coerce.number().int().positive().optional(),
});

const workflowTemplateReplaceSchema = z.object({
  name: z.string().min(2).max(255),
  caseTypeCode: z.string().min(2).max(50),
  caseSubtypeCode: z.string().max(50).optional().or(z.literal("")),
  description: z.string().max(1000).optional().or(z.literal("")),
  steps: z
    .array(
      z.object({
        clientStepId: z.string().min(1).max(120),
        stepCode: z.string().min(2).max(50),
        name: z.string().min(2).max(255),
        stepOrder: z.coerce.number().int().positive(),
        stepType: z.string().min(2).max(50).default("task"),
        description: z.string().max(1000).optional().or(z.literal("")),
        appliesWhen: z.array(workflowConditionKeySchema).max(16).optional().default([]),
        responsePrompts: z
          .array(
            z.object({
              id: z.string().optional(),
              question: z.string().min(1).max(1000),
              targetType: z.enum(["client", "sponsor", "internal"]).default("client"),
            }),
          )
          .max(50)
          .optional()
          .default([]),
      }),
    )
    .min(1),
  requiredDocuments: z
    .array(
      z.object({
        clientDocumentId: z.string().min(1).max(120),
        workflowStepClientId: z.string().min(1).max(120).optional().nullable(),
        documentTypeCode: z.string().min(2).max(50),
        requirementCode: z.string().min(2).max(100),
        displayName: z.string().min(2).max(255),
        description: z.string().max(1000).optional().or(z.literal("")),
        aiInstructions: z.string().max(2000).optional().or(z.literal("")),
        appliesWhen: z.array(workflowConditionKeySchema).max(16).optional().default([]),
        isRequired: z.boolean().default(true),
        minimumQuantity: z.coerce.number().int().positive().default(1),
      }),
    )
    .optional()
    .default([]),
  requiredForms: z
    .array(
      z.object({
        clientFormId: z.string().min(1).max(120),
        workflowStepClientId: z.string().min(1).max(120).optional().nullable(),
        formTemplateId: z.string().uuid(),
        requirementCode: z.string().min(2).max(100),
        appliesWhen: z.array(workflowConditionKeySchema).max(16).optional().default([]),
        isRequired: z.boolean().default(true),
      }),
    )
    .optional()
    .default([]),
  finalPacketItems: z.array(workflowFinalPacketItemSchema).optional().default([]),
});

const workflowDraftResponsePromptSchema = z.object({
  id: z.string().min(1).max(120),
  question: z.string().max(1000),
  targetType: z.enum(["client", "sponsor", "internal"]),
});

const workflowDraftStepSchema = z.object({
  id: z.string().min(1).max(120),
  stepCode: z.string().max(50),
  name: z.string().max(255),
  stepType: z.string().max(50),
  description: z.string().max(1000),
  appliesWhen: z.array(workflowConditionKeySchema).max(16).default([]),
  responsePrompts: z.array(workflowDraftResponsePromptSchema).max(200).default([]),
});

const workflowDraftRequiredDocumentSchema = z.object({
  id: z.string().min(1).max(120),
  workflowStepDraftId: z.string().max(120),
  documentTypeCode: z.string().max(50),
  requirementCode: z.string().max(100),
  displayName: z.string().max(255),
  description: z.string().max(1000),
  aiInstructions: z.string().max(2000),
  appliesWhen: z.array(workflowConditionKeySchema).max(16).default([]),
  isRequired: z.boolean(),
  minimumQuantity: z.string().max(20),
});

const workflowDraftRequiredFormSchema = z.object({
  id: z.string().min(1).max(120),
  workflowStepDraftId: z.string().max(120),
  formTemplateId: z.string().max(120),
  requirementCode: z.string().max(100),
  appliesWhen: z.array(workflowConditionKeySchema).max(16).default([]),
  isRequired: z.boolean(),
});

const workflowDraftFinalPacketItemSchema = z.object({
  id: z.string().min(1).max(120),
  sourceType: z.enum(["document", "form", "condition"]),
  sourceDraftId: z.string().min(1).max(120),
  partyType: z.enum(workflowFinalPacketPartyTypes),
});

const workflowTemplateDraftSchema = z.object({
  version: z.literal(1),
  savedAt: z.string().datetime(),
  sourceRevision: z.string().nullable().optional().default(null),
  workflowForm: z.object({
    name: z.string().max(255),
    caseTypeCode: z.string().max(50),
    caseSubtypeCode: z.string().max(50),
    description: z.string().max(1000),
    steps: z.array(workflowDraftStepSchema).max(500),
    requiredDocuments: z.array(workflowDraftRequiredDocumentSchema).max(500),
    requiredForms: z.array(workflowDraftRequiredFormSchema).max(500),
    finalPacketItems: z.array(workflowDraftFinalPacketItemSchema).max(500),
  }),
  expandedWorkflowStepId: z.string().min(1).max(120).nullable(),
  responsesDialog: z.object({
    isOpen: z.boolean(),
    targetStepId: z.string().min(1).max(120).nullable(),
    targetCondition: workflowConditionKeySchema.nullable(),
    questions: z.array(workflowDraftResponsePromptSchema).max(200).default([]),
  }),
});

const activateWorkflowSchema = z.object({
  caseId: z.string().uuid(),
  workflowTemplateId: z.string().uuid(),
});

function normalizeWorkflowTemplateCode(value: string) {
  return (
    value
      .normalize("NFD")
      .replace(/[\u0300-\u036f]/g, "")
      .toLowerCase()
      .replace(/[^a-z0-9]+/g, "") || "workflow"
  );
}

function buildWorkflowPacketTemplateCode(templateId: string) {
  return `workflow_${templateId}`;
}

function parseWorkflowTemplateDraftState(value: unknown) {
  if (typeof value !== "string" || !value.trim()) {
    return null;
  }

  try {
    const parsedValue = JSON.parse(value);
    const result = workflowTemplateDraftSchema.safeParse(parsedValue);
    return result.success ? result.data : null;
  } catch {
    return null;
  }
}

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

  return Array.from(
    new Set(
      values
        .map((item) =>
          String(item ?? "")
            .normalize("NFD")
            .replace(/[\u0300-\u036f]/g, "")
            .trim()
            .toLowerCase()
            .replace(/[^a-z0-9]+/g, "_")
            .replace(/^_+|_+$/g, ""),
        )
        .filter((item): item is WorkflowConditionKey => Boolean(item)),
    ),
  );
}

async function resolveUniqueWorkflowTemplateCode(lawFirmId: string, code: string) {
  const normalizedBaseCode = normalizeWorkflowTemplateCode(code);
  const existingTemplates = await prisma.$queryRaw<Array<{ code: string }>>`
    SELECT code
    FROM workflow_templates
    WHERE law_firm_id = ${lawFirmId}
      AND version_number = 1
  `;
  const existingCodes = new Set(existingTemplates.map((item) => item.code.toLowerCase()));

  if (!existingCodes.has(normalizedBaseCode)) {
    return normalizedBaseCode;
  }

  let sequence = 2;

  while (true) {
    const suffix = `_${sequence}`;
    const basePart =
      normalizedBaseCode.slice(0, Math.max(1, 50 - suffix.length)).replace(/_+$/g, "") ||
      "workflow";
    const nextCode = `${basePart}${suffix}`;

    if (!existingCodes.has(nextCode.toLowerCase())) {
      return nextCode;
    }

    sequence += 1;
  }
}

function parseWorkflowResponsePrompts(value: unknown) {
  if (!value) {
    return [];
  }

  let parsedValue = value;

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

  const rawItems =
    parsedValue &&
    typeof parsedValue === "object" &&
    Array.isArray((parsedValue as { responsePrompts?: unknown[] }).responsePrompts)
      ? (parsedValue as { responsePrompts: unknown[] }).responsePrompts
      : [];

  return rawItems
    .map((item, index) => {
      if (!item || typeof item !== "object") {
        return null;
      }

      const question = String((item as { question?: string }).question ?? "").trim();

      if (!question) {
        return null;
      }

      return {
        id: String((item as { id?: string }).id ?? `prompt_${index + 1}`),
        question,
        targetType:
          String((item as { targetType?: string }).targetType ?? "client") === "sponsor"
            ? "sponsor"
            : String((item as { targetType?: string }).targetType ?? "client") === "internal"
              ? "internal"
              : "client",
      };
    })
    .filter(
      (
        item,
      ): item is {
        id: string;
        question: string;
        targetType: "client" | "sponsor" | "internal";
      } => item !== null,
    );
}

function parseWorkflowDocumentAiInstructions(value: unknown) {
  const config = parseWorkflowConditionConfig(value);
  return config.aiInstructions;
}

function parseWorkflowConditionConfig(value: unknown) {
  if (!value) {
    return {
      aiInstructions: null,
      appliesWhen: [] as WorkflowConditionKey[],
    };
  }

  let parsedValue = value;

  if (typeof parsedValue === "string") {
    try {
      parsedValue = JSON.parse(parsedValue);
    } catch {
      return {
        aiInstructions: null,
        appliesWhen: [] as WorkflowConditionKey[],
      };
    }
  }

  if (!parsedValue || typeof parsedValue !== "object") {
    return {
      aiInstructions: null,
      appliesWhen: [] as WorkflowConditionKey[],
    };
  }

  const aiInstructions = String(
    (parsedValue as { aiInstructions?: string }).aiInstructions ?? "",
  ).trim();

  return {
    aiInstructions: aiInstructions || null,
    appliesWhen: normalizeWorkflowAppliesWhen(
      (parsedValue as { appliesWhen?: unknown[] }).appliesWhen ?? [],
    ),
  };
}

function buildWorkflowConditionExpressionJson(input: {
  aiInstructions?: string | null;
  appliesWhen?: WorkflowConditionKey[];
}) {
  const aiInstructions = String(input.aiInstructions ?? "").trim();
  const appliesWhen = normalizeWorkflowAppliesWhen(input.appliesWhen ?? []);

  if (!aiInstructions && appliesWhen.length === 0) {
    return null;
  }

  return JSON.stringify({
    aiInstructions: aiInstructions || undefined,
    appliesWhen: appliesWhen.length ? appliesWhen : undefined,
  });
}

function buildWorkflowConditionFactMap(
  facts: Array<{
    field_key: string;
    raw_value: string | null;
    normalized_value_json: unknown;
  }>,
) {
  const factMap = new Map<string, string[]>();

  for (const fact of facts) {
    const values: string[] = [];

    if (typeof fact.raw_value === "string" && fact.raw_value.trim()) {
      values.push(fact.raw_value.trim());
    }

    let parsedNormalizedValue = fact.normalized_value_json;

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

    if (
      parsedNormalizedValue &&
      typeof parsedNormalizedValue === "object" &&
      !Array.isArray(parsedNormalizedValue)
    ) {
      const candidateValue = (parsedNormalizedValue as { value?: unknown }).value;

      if (candidateValue !== undefined && candidateValue !== null) {
        values.push(String(candidateValue));
      }
    }

    if (!values.length) {
      continue;
    }

    const currentValues = factMap.get(fact.field_key) ?? [];
    factMap.set(fact.field_key, [...currentValues, ...values]);
  }

  return factMap;
}

function normalizeWorkflowConditionValue(value: string) {
  return value
    .normalize("NFD")
    .replace(/[\u0300-\u036f]/g, "")
    .trim()
    .toLowerCase();
}

function isTruthyWorkflowConditionValue(value: string) {
  return ["1", "true", "yes", "y", "sim", "s"].includes(
    normalizeWorkflowConditionValue(value),
  );
}

function matchesWorkflowAppliesWhen(
  appliesWhen: WorkflowConditionKey[],
  factMap: Map<string, string[]>,
) {
  const normalizedConditions = normalizeWorkflowAppliesWhen(appliesWhen);

  if (!normalizedConditions.length) {
    return true;
  }

  const maritalStatuses = (factMap.get("client_marital_status") ?? []).map((item) =>
    normalizeWorkflowConditionValue(item),
  );
  const arrestedValues = (factMap.get("client_has_been_arrested") ?? []).map((item) =>
    normalizeWorkflowConditionValue(item),
  );
  const dependentsValues = (factMap.get("client_has_dependents") ?? []).map((item) =>
    normalizeWorkflowConditionValue(item),
  );

  return normalizedConditions.every((condition) => {
    if (condition === "married") {
      return maritalStatuses.some((item) => ["married", "casado", "casada"].includes(item));
    }

    if (condition === "divorced") {
      return maritalStatuses.some((item) => ["divorced", "divorciado", "divorciada"].includes(item));
    }

    if (condition === "arrested") {
      return arrestedValues.some((item) => isTruthyWorkflowConditionValue(item));
    }

    if (condition === "has_dependents") {
      return dependentsValues.some((item) => isTruthyWorkflowConditionValue(item));
    }

    const genericConditionValues = (factMap.get(condition) ?? []).map((item) =>
      normalizeWorkflowConditionValue(item),
    );

    return genericConditionValues.some((item) => isTruthyWorkflowConditionValue(item));
  });
}

function resolveWorkflowDependentCount(factMap: Map<string, string[]>) {
  const rawDependentCounts = [
    ...(factMap.get("client_dependents_count") ?? []),
    ...(factMap.get("client_number_of_dependents") ?? []),
  ];
  const numericDependentCounts = rawDependentCounts
    .map((value) => {
      const normalizedValue = String(value ?? "").trim();

      if (!normalizedValue) {
        return null;
      }

      const directNumber = Number(normalizedValue);

      if (Number.isFinite(directNumber) && directNumber > 0) {
        return Math.trunc(directNumber);
      }

      const digitMatch = normalizedValue.match(/\d+/);

      if (!digitMatch) {
        return null;
      }

      const parsedDigits = Number(digitMatch[0]);
      return Number.isFinite(parsedDigits) && parsedDigits > 0 ? Math.trunc(parsedDigits) : null;
    })
    .filter((value): value is number => value !== null);

  if (numericDependentCounts.length) {
    return Math.max(...numericDependentCounts);
  }

  const hasDependents = (factMap.get("client_has_dependents") ?? []).some((item) =>
    isTruthyWorkflowConditionValue(item),
  );

  return hasDependents ? 1 : 0;
}

function formatWorkflowConditionCardTitle(condition: WorkflowConditionKey) {
  if (condition === "married") {
    return "Se for casado";
  }

  if (condition === "divorced") {
    return "Se for divorciado";
  }

  if (condition === "arrested") {
    return "Se foi preso";
  }

  if (condition === "has_dependents") {
    return "Por dependente";
  }

  return condition
    .split("_")
    .filter(Boolean)
    .map((item) => item.charAt(0).toUpperCase() + item.slice(1))
    .join(" ");
}

function normalizeWorkflowFinalPacketPartyType(
  value: unknown,
  fallback: WorkflowFinalPacketPartyType = "client",
) {
  const normalizedValue = String(value ?? "").trim();
  return workflowFinalPacketPartyTypes.includes(
    normalizedValue as WorkflowFinalPacketPartyType,
  )
    ? (normalizedValue as WorkflowFinalPacketPartyType)
    : fallback;
}

function resolveDefaultWorkflowFinalPacketPartyType(input: {
  conditionKey?: WorkflowConditionKey | null;
  appliesWhen?: WorkflowConditionKey[];
}) {
  if (
    input.conditionKey === "has_dependents" ||
    normalizeWorkflowAppliesWhen(input.appliesWhen ?? []).includes("has_dependents")
  ) {
    return "dependents" as const;
  }

  return "client" as const;
}

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

  let parsedValue = value;

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

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

  const sourceType = String(
    (parsedValue as { sourceType?: string }).sourceType ?? "",
  ).trim();
  const workflowRequiredDocumentId = String(
    (parsedValue as { workflowRequiredDocumentId?: string }).workflowRequiredDocumentId ?? "",
  ).trim();
  const workflowRequiredFormId = String(
    (parsedValue as { workflowRequiredFormId?: string }).workflowRequiredFormId ?? "",
  ).trim();
  const requirementCode = String(
    (parsedValue as { requirementCode?: string }).requirementCode ?? "",
  ).trim();
  const conditionKey = normalizeWorkflowAppliesWhen([
    String((parsedValue as { conditionKey?: string }).conditionKey ?? "").trim(),
  ])[0];
  const appliesWhen = normalizeWorkflowAppliesWhen(
    (parsedValue as { appliesWhen?: unknown[] }).appliesWhen ?? [],
  );
  const partyType = normalizeWorkflowFinalPacketPartyType(
    (parsedValue as { partyType?: string }).partyType ?? "",
    resolveDefaultWorkflowFinalPacketPartyType({
      conditionKey: conditionKey ?? null,
      appliesWhen,
    }),
  );

  return {
    sourceType:
      sourceType === "form"
        ? "form"
        : sourceType === "document"
          ? "document"
          : sourceType === "condition"
            ? "condition"
            : null,
    workflowRequiredDocumentId: workflowRequiredDocumentId || null,
    workflowRequiredFormId: workflowRequiredFormId || null,
    requirementCode: requirementCode || null,
    conditionKey: conditionKey ?? null,
    partyType,
  };
}

async function resolveRequiredDocumentTypeCode(documentTypeCode: string) {
  const normalizedCode = documentTypeCode.trim();

  if (normalizedCode) {
    const [matchedDocumentType] = await prisma.$queryRaw<Array<{ code: string }>>`
      SELECT code
      FROM document_types
      WHERE code = ${normalizedCode}
      LIMIT 1
    `;

    if (matchedDocumentType?.code) {
      return matchedDocumentType.code;
    }
  }

  const [fallbackDocumentType] = await prisma.$queryRaw<Array<{ code: string }>>`
    SELECT code
    FROM document_types
    WHERE code = ${"other_supporting"}
    LIMIT 1
  `;

  if (fallbackDocumentType?.code) {
    return fallbackDocumentType.code;
  }

  const [firstDocumentType] = await prisma.$queryRaw<Array<{ code: string }>>`
    SELECT code
    FROM document_types
    ORDER BY code ASC
    LIMIT 1
  `;

  return firstDocumentType?.code ?? "other_supporting";
}

export async function registerWorkflowRoutes(app: FastifyInstance) {
  app.get("/document-library", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    return listWorkspaceDocumentLibraryItems(profile.lawFirm.id);
  });

  app.get("/templates", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const templates = await prisma.$queryRaw<
      Array<{
        id: string;
        law_firm_id: string | null;
        code: string;
        name: string;
        case_type_code: string;
        case_subtype_code: string | null;
        description: string | null;
        is_system_template: number;
        builder_draft_json: string | null;
        builder_draft_saved_at: Date | null;
      }>
    >`
      SELECT
        id,
        law_firm_id,
        code,
        name,
        case_type_code,
        case_subtype_code,
        description,
        is_system_template,
        builder_draft_json,
        builder_draft_saved_at
      FROM workflow_templates
      WHERE law_firm_id = ${profile.lawFirm.id} OR law_firm_id IS NULL
      ORDER BY is_system_template DESC, name ASC
    `;

    const steps = templates.length
      ? await prisma.$queryRaw<
          Array<{
            id: string;
            workflow_template_id: string;
            step_code: string;
            name: string;
            step_order: number;
            step_type: string;
            description: string | null;
            condition_expression_json: unknown;
            completion_rule_json: unknown;
          }>
        >`
          SELECT
            id,
            workflow_template_id,
            step_code,
            name,
            step_order,
            step_type,
            description,
            condition_expression_json,
            completion_rule_json
          FROM workflow_steps
          WHERE workflow_template_id IN (${Prisma.join(templates.map((item) => item.id))})
            AND retired_at IS NULL
          ORDER BY step_order ASC
        `
      : [];

    const requiredForms = templates.length
      ? await prisma.$queryRaw<
          Array<{
            id: string;
            workflow_template_id: string;
            workflow_step_id: string | null;
            form_template_id: string;
            requirement_code: string;
            form_name: string;
            condition_expression_json: unknown;
            is_required: number;
          }>
        >`
          SELECT
            wrf.id,
            wrf.workflow_template_id,
            wrf.workflow_step_id,
            wrf.form_template_id,
            wrf.requirement_code,
            ft.name AS form_name,
            wrf.condition_expression_json,
            wrf.is_required
          FROM workflow_required_forms wrf
          JOIN form_templates ft ON ft.id = wrf.form_template_id
          WHERE wrf.workflow_template_id IN (${Prisma.join(templates.map((item) => item.id))})
            AND wrf.retired_at IS NULL
          ORDER BY wrf.created_at ASC
        `
      : [];

    const requiredDocuments = templates.length
      ? await prisma.$queryRaw<
          Array<{
            id: string;
            workflow_template_id: string;
            workflow_step_id: string | null;
            requirement_code: string;
            display_name: string;
            document_type_code: string;
            description: string | null;
            condition_expression_json: unknown;
            is_required: number;
            minimum_quantity: number;
          }>
        >`
          SELECT
            id,
            workflow_template_id,
            workflow_step_id,
            requirement_code,
            display_name,
            document_type_code,
            description,
            condition_expression_json,
            is_required,
            minimum_quantity
          FROM workflow_required_documents
          WHERE workflow_template_id IN (${Prisma.join(templates.map((item) => item.id))})
            AND retired_at IS NULL
          ORDER BY created_at ASC
        `
      : [];

    const workflowPacketTemplateCodes = templates.map((item) =>
      buildWorkflowPacketTemplateCode(item.id),
    );
    const packetTemplates = workflowPacketTemplateCodes.length
      ? await prisma.$queryRaw<
          Array<{
            id: string;
            code: string;
          }>
        >`
          SELECT id, code
          FROM packet_templates
          WHERE law_firm_id = ${profile.lawFirm.id}
            AND code IN (${Prisma.join(workflowPacketTemplateCodes)})
          ORDER BY updated_at DESC
        `
      : [];
    const packetTemplateItems = packetTemplates.length
      ? await prisma.$queryRaw<
          Array<{
            id: string;
            packet_template_id: string;
            sort_order: number;
            item_type: string;
            display_name: string;
            form_template_id: string | null;
            document_type_code: string | null;
            condition_expression_json: unknown;
          }>
        >`
          SELECT
            id,
            packet_template_id,
            sort_order,
            item_type,
            display_name,
            form_template_id,
            document_type_code,
            condition_expression_json
          FROM packet_template_items
          WHERE packet_template_id IN (${Prisma.join(packetTemplates.map((item) => item.id))})
            AND retired_at IS NULL
          ORDER BY sort_order ASC
        `
      : [];
    const packetTemplateIdByCode = new Map(packetTemplates.map((item) => [item.code, item.id]));

    return templates.map((template) => ({
      id: template.id,
      code: template.code,
      name: template.name,
      caseTypeCode: template.case_type_code,
      caseSubtypeCode: template.case_subtype_code,
      description: template.description,
      isSystemTemplate: Boolean(template.is_system_template),
      isWorkspaceLibrary: isWorkspaceLibraryWorkflowTemplateCode(template.code),
      draftState: parseWorkflowTemplateDraftState(template.builder_draft_json),
      steps: steps
        .filter((step) => step.workflow_template_id === template.id)
        .map((step) => ({
          id: step.id,
          stepCode: step.step_code,
          name: step.name,
          stepOrder: step.step_order,
          stepType: step.step_type,
          description: step.description,
          appliesWhen: parseWorkflowConditionConfig(step.condition_expression_json).appliesWhen,
          responsePrompts: parseWorkflowResponsePrompts(step.completion_rule_json),
        })),
      requiredForms: requiredForms
        .filter((item) => item.workflow_template_id === template.id)
        .map((item) => ({
          id: item.id,
          workflowStepId: item.workflow_step_id,
          formTemplateId: item.form_template_id,
          requirementCode: item.requirement_code,
          formName: item.form_name,
          appliesWhen: parseWorkflowConditionConfig(item.condition_expression_json).appliesWhen,
          isRequired: Boolean(item.is_required),
        })),
      requiredDocuments: requiredDocuments
        .filter((item) => item.workflow_template_id === template.id)
        .map((item) => ({
          id: item.id,
          workflowStepId: item.workflow_step_id,
          requirementCode: item.requirement_code,
          displayName: item.display_name,
          documentTypeCode: item.document_type_code,
          description: item.description,
          aiInstructions: parseWorkflowDocumentAiInstructions(item.condition_expression_json),
          appliesWhen: parseWorkflowConditionConfig(item.condition_expression_json).appliesWhen,
          isRequired: Boolean(item.is_required),
          minimumQuantity: item.minimum_quantity,
        })),
      finalPacketItems: packetTemplateItems
        .filter(
          (item) =>
            item.packet_template_id ===
            packetTemplateIdByCode.get(buildWorkflowPacketTemplateCode(template.id)),
        )
        .map((item) => {
          const config = parseWorkflowPacketItemConfig(item.condition_expression_json);

          return {
            id: item.id,
            sourceType:
              config?.sourceType === "condition"
                ? "condition"
                : config?.sourceType === "form" || item.form_template_id
                  ? "form"
                  : "document",
            sourceId:
              config?.sourceType === "condition"
                ? config.conditionKey || item.id
                : config?.workflowRequiredFormId ||
                  config?.workflowRequiredDocumentId ||
                  config?.requirementCode ||
                  item.id,
            partyType:
              config?.partyType ??
              resolveDefaultWorkflowFinalPacketPartyType({
                conditionKey: config?.conditionKey ?? null,
              }),
            displayName: item.display_name,
            sortOrder: item.sort_order,
          };
        }),
    }));
  });

  app.post("/templates", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const payload = workflowTemplateSchema.parse(request.body);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const id = createId();
    const code = await resolveUniqueWorkflowTemplateCode(profile.lawFirm.id, payload.code);
    await prisma.$executeRaw`
      INSERT INTO workflow_templates (
        id, law_firm_id, code, name, case_type_code, case_subtype_code, version_number,
        description, is_active, is_system_template, created_by_user_id, created_at, updated_at
      ) VALUES (
        ${id},
        ${profile.lawFirm.id},
        ${code},
        ${payload.name},
        ${payload.caseTypeCode},
        ${payload.caseSubtypeCode || null},
        1,
        ${payload.description || null},
        1,
        0,
        ${profile.user.id},
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
    `;

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
        entityType: "workflow_template",
        entityId: id,
        action: "workflow_template.create",
        afterJson: {
          ...payload,
          code,
        },
        request,
      });

    return reply.code(201).send({ id, code });
  });

  const saveWorkflowTemplateDraftHandler = async (request: FastifyRequest, reply: FastifyReply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const parsedPayload = workflowTemplateDraftSchema.safeParse(request.body);
    const { templateId } = request.params as { templateId: string };

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    if (!parsedPayload.success) {
      return reply.badRequest(
        parsedPayload.error.issues[0]?.message ?? "Invalid workflow draft payload.",
      );
    }

    const payload = parsedPayload.data;

    const [template] = await prisma.$queryRaw<
      Array<{
        id: string;
        name: string;
        case_type_code: string;
        is_system_template: number;
      }>
    >`
      SELECT id, name, case_type_code, is_system_template
      FROM workflow_templates
      WHERE id = ${templateId}
        AND law_firm_id = ${profile.lawFirm.id}
      LIMIT 1
    `;

    if (!template) {
      throw reply.notFound("Workflow template not found.");
    }

    if (template.is_system_template) {
      throw reply.badRequest("System workflow templates cannot store drafts.");
    }

    const resolvedName = payload.workflowForm.name.trim() || template.name;
    const resolvedCaseTypeCode =
      payload.workflowForm.caseTypeCode.trim() || template.case_type_code;
    const resolvedCaseSubtypeCode = payload.workflowForm.caseSubtypeCode.trim();
    const resolvedDescription = payload.workflowForm.description.trim();
    const savedAt = new Date().toISOString();
    const draftState = {
      ...payload,
      savedAt,
    };

    await prisma.$executeRaw`
      UPDATE workflow_templates
      SET
        name = ${resolvedName},
        case_type_code = ${resolvedCaseTypeCode},
        case_subtype_code = ${resolvedCaseSubtypeCode || null},
        description = ${resolvedDescription || null},
        builder_draft_json = ${JSON.stringify(draftState)},
        builder_draft_saved_at = CURRENT_TIMESTAMP,
        updated_at = CURRENT_TIMESTAMP
      WHERE id = ${templateId}
        AND law_firm_id = ${profile.lawFirm.id}
    `;

    return reply.send({ saved: true, id: templateId, savedAt });
  };

  app.put("/templates/:templateId/draft", saveWorkflowTemplateDraftHandler);
  app.post("/templates/:templateId/draft", saveWorkflowTemplateDraftHandler);

  app.put("/templates/:templateId", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const payload = workflowTemplateReplaceSchema.parse(request.body);
    const { templateId } = request.params as { templateId: string };

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const [template] = await prisma.$queryRaw<
      Array<{
        id: string;
        law_firm_id: string | null;
        code: string;
        name: string;
        case_type_code: string;
        case_subtype_code: string | null;
        description: string | null;
        is_system_template: number;
      }>
    >`
      SELECT
        id,
        law_firm_id,
        code,
        name,
        case_type_code,
        case_subtype_code,
        description,
        is_system_template
      FROM workflow_templates
      WHERE id = ${templateId}
      LIMIT 1
    `;

    if (!template) {
      throw reply.notFound("Workflow template not found");
    }

    if (Boolean(template.is_system_template)) {
      throw reply.badRequest("System workflow templates cannot be edited");
    }

    if (template.law_firm_id !== profile.lawFirm.id) {
      throw reply.notFound("Workflow template not found");
    }

    const stepClientIds = new Set(payload.steps.map((step) => step.clientStepId));

    if (
      payload.requiredDocuments.some(
        (item) => item.workflowStepClientId && !stepClientIds.has(item.workflowStepClientId),
      )
    ) {
      throw reply.badRequest("Documento vinculado a uma etapa invalida.");
    }

    if (
      payload.requiredForms.some(
        (item) => item.workflowStepClientId && !stepClientIds.has(item.workflowStepClientId),
      )
    ) {
      throw reply.badRequest("Formulario vinculado a uma etapa invalida.");
    }

    const documentClientIds = new Set(
      payload.requiredDocuments.map((item) => item.clientDocumentId),
    );
    const formClientIds = new Set(payload.requiredForms.map((item) => item.clientFormId));
    const conditionSourceIds = new Set(
      [
        ...payload.steps.flatMap((item) => normalizeWorkflowAppliesWhen(item.appliesWhen)),
        ...payload.requiredDocuments.flatMap((item) =>
          normalizeWorkflowAppliesWhen(item.appliesWhen),
        ),
        ...payload.requiredForms.flatMap((item) =>
          normalizeWorkflowAppliesWhen(item.appliesWhen),
        ),
      ],
    );

    const formTemplateIds = Array.from(
      new Set(payload.requiredForms.map((item) => item.formTemplateId)),
    );
    const formTemplateNames = formTemplateIds.length
      ? await prisma.$queryRaw<Array<{ id: string; name: string }>>`
          SELECT id, name
          FROM form_templates
          WHERE id IN (${Prisma.join(formTemplateIds)})
        `
      : [];
    const formNameByTemplateId = new Map(formTemplateNames.map((item) => [item.id, item.name]));

    if (
      payload.finalPacketItems.some((item) =>
        item.sourceType === "document"
          ? !documentClientIds.has(item.sourceClientId)
          : item.sourceType === "form"
            ? !formClientIds.has(item.sourceClientId)
            : !conditionSourceIds.has(item.sourceClientId as WorkflowConditionKey),
      )
    ) {
      throw reply.badRequest("Processo final vinculado a documento ou formulario invalido.");
    }

    await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        UPDATE workflow_templates
        SET
          name = ${payload.name},
          case_type_code = ${payload.caseTypeCode},
          case_subtype_code = ${payload.caseSubtypeCode || null},
          description = ${payload.description || null},
          builder_draft_json = NULL,
          builder_draft_saved_at = NULL,
          updated_at = CURRENT_TIMESTAMP
        WHERE id = ${templateId}
          AND law_firm_id = ${profile.lawFirm.id}
      `;

      await tx.$executeRaw`
        UPDATE workflow_required_forms
        SET retired_at = CURRENT_TIMESTAMP
        WHERE workflow_template_id = ${templateId}
          AND retired_at IS NULL
      `;
      await tx.$executeRaw`
        UPDATE workflow_required_documents
        SET retired_at = CURRENT_TIMESTAMP
        WHERE workflow_template_id = ${templateId}
          AND retired_at IS NULL
      `;
      await tx.$executeRaw`
        UPDATE workflow_steps
        SET retired_at = CURRENT_TIMESTAMP
        WHERE workflow_template_id = ${templateId}
          AND retired_at IS NULL
      `;

      const workflowPacketTemplateCode = buildWorkflowPacketTemplateCode(templateId);
      const [existingPacketTemplate] = await tx.$queryRaw<Array<{ id: string }>>`
        SELECT id
        FROM packet_templates
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND code = ${workflowPacketTemplateCode}
          AND version_number = 1
        LIMIT 1
      `;
      const packetTemplateId = existingPacketTemplate?.id ?? createId();

      if (existingPacketTemplate?.id) {
        await tx.$executeRaw`
          UPDATE packet_templates
          SET
            name = ${`${payload.name} • Processo final`},
            description = ${`Ordem final do processo para o workflow ${templateId}`},
            is_active = 1,
            updated_at = CURRENT_TIMESTAMP
          WHERE id = ${packetTemplateId}
        `;
      } else {
        await tx.$executeRaw`
          INSERT INTO packet_templates (
            id, law_firm_id, code, name, version_number, description, is_active, created_at, updated_at
          ) VALUES (
            ${packetTemplateId},
            ${profile.lawFirm.id},
            ${workflowPacketTemplateCode},
            ${`${payload.name} • Processo final`},
            1,
            ${`Ordem final do processo para o workflow ${templateId}`},
            1,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
          )
        `;
      }

      await tx.$executeRaw`
        UPDATE packet_template_items
        SET retired_at = CURRENT_TIMESTAMP
        WHERE packet_template_id = ${packetTemplateId}
          AND retired_at IS NULL
      `;

      const stepIdByClientId = new Map<string, string>();
      const documentIdByRequirementCode = new Map<
        string,
        {
          id: string;
          displayName: string;
          clientDocumentId: string;
          requirementCode: string;
          documentTypeCode: string;
          appliesWhen: WorkflowConditionKey[];
        }
      >();
      const formIdByRequirementCode = new Map<
        string,
        {
          id: string;
          clientFormId: string;
          formTemplateId: string;
          requirementCode: string;
          formName: string;
          appliesWhen: WorkflowConditionKey[];
        }
      >();

      for (const step of payload.steps) {
        const stepId = createId();
        stepIdByClientId.set(step.clientStepId, stepId);

        const responsePrompts = step.responsePrompts
          .map((item, index) => ({
            id: item.id?.trim() || `prompt_${index + 1}`,
            question: item.question.trim(),
            targetType: item.targetType,
          }))
          .filter((item) => item.question);
        const conditionExpressionJson = buildWorkflowConditionExpressionJson({
          appliesWhen: step.appliesWhen,
        });
        const completionRuleJson = responsePrompts.length
          ? JSON.stringify({ responsePrompts })
          : null;

        await tx.$executeRaw`
          INSERT INTO workflow_steps (
            id, workflow_template_id, step_code, name, step_order, step_type, description,
            condition_expression_json, completion_rule_json, sla_days, created_at, updated_at
          ) VALUES (
            ${stepId},
            ${templateId},
            ${step.stepCode},
            ${step.name},
            ${step.stepOrder},
            ${step.stepType},
            ${step.description || null},
            ${conditionExpressionJson},
            ${completionRuleJson},
            NULL,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
          )
        `;
      }

      for (const item of payload.requiredDocuments) {
        const documentTypeCode = await resolveRequiredDocumentTypeCode(item.documentTypeCode);
        const conditionExpressionJson = buildWorkflowConditionExpressionJson({
          aiInstructions: item.aiInstructions,
          appliesWhen: item.appliesWhen,
        });
        const workflowRequiredDocumentId = createId();

        await tx.$executeRaw`
          INSERT INTO workflow_required_documents (
            id, workflow_template_id, workflow_step_id, document_type_code, requirement_code,
            display_name, description, is_required, minimum_quantity, condition_expression_json, created_at
          ) VALUES (
            ${workflowRequiredDocumentId},
            ${templateId},
            ${item.workflowStepClientId ? stepIdByClientId.get(item.workflowStepClientId) ?? null : null},
            ${documentTypeCode},
            ${item.requirementCode},
            ${item.displayName},
            ${item.description || null},
            ${item.isRequired ? 1 : 0},
            ${item.minimumQuantity},
            ${conditionExpressionJson},
            CURRENT_TIMESTAMP
          )
        `;

        documentIdByRequirementCode.set(item.requirementCode, {
          id: workflowRequiredDocumentId,
          clientDocumentId: item.clientDocumentId,
          displayName: item.displayName,
          requirementCode: item.requirementCode,
          documentTypeCode,
          appliesWhen: normalizeWorkflowAppliesWhen(item.appliesWhen),
        });
      }

      for (const item of payload.requiredForms) {
        const workflowRequiredFormId = createId();
        const conditionExpressionJson = buildWorkflowConditionExpressionJson({
          appliesWhen: item.appliesWhen,
        });
        await tx.$executeRaw`
          INSERT INTO workflow_required_forms (
            id, workflow_template_id, workflow_step_id, form_template_id, requirement_code,
            is_required, due_offset_days, condition_expression_json, created_at
          ) VALUES (
            ${workflowRequiredFormId},
            ${templateId},
            ${item.workflowStepClientId ? stepIdByClientId.get(item.workflowStepClientId) ?? null : null},
            ${item.formTemplateId},
            ${item.requirementCode},
            ${item.isRequired ? 1 : 0},
            NULL,
            ${conditionExpressionJson},
            CURRENT_TIMESTAMP
          )
        `;

        formIdByRequirementCode.set(item.requirementCode, {
          id: workflowRequiredFormId,
          clientFormId: item.clientFormId,
          formTemplateId: item.formTemplateId,
          requirementCode: item.requirementCode,
          formName:
            formNameByTemplateId.get(item.formTemplateId) || item.requirementCode || "Formulario",
          appliesWhen: normalizeWorkflowAppliesWhen(item.appliesWhen),
        });
      }

      for (const item of payload.finalPacketItems
        .slice()
        .sort((left, right) => (left.sortOrder ?? 0) - (right.sortOrder ?? 0))) {
        const conditionKey =
          item.sourceType === "condition"
            ? normalizeWorkflowAppliesWhen([item.sourceClientId])[0] ?? null
            : null;
        const linkedDocument =
          item.sourceType === "document"
            ? Array.from(documentIdByRequirementCode.values()).find(
                (documentItem) => documentItem.clientDocumentId === item.sourceClientId,
              ) ?? null
            : null;
        const linkedForm =
          item.sourceType === "form"
            ? Array.from(formIdByRequirementCode.values()).find(
                (formItem) => formItem.clientFormId === item.sourceClientId,
              ) ?? null
            : null;

        if (!linkedDocument && !linkedForm && !conditionKey) {
          continue;
        }

        const displayName =
          item.sourceType === "condition"
            ? item.sourceDisplayName?.trim() ||
              formatWorkflowConditionCardTitle(conditionKey ?? "has_dependents")
            : linkedDocument?.displayName || linkedForm?.formName || "Item";
        const conditionExpressionJson = JSON.stringify({
          sourceType: item.sourceType,
          partyType: item.partyType,
          workflowRequiredDocumentId: linkedDocument?.id ?? null,
          workflowRequiredFormId: linkedForm?.id ?? null,
          requirementCode: linkedDocument?.requirementCode ?? linkedForm?.requirementCode ?? null,
          conditionKey,
          appliesWhen:
            item.sourceType === "condition"
              ? (conditionKey ? [conditionKey] : [])
              : linkedDocument?.appliesWhen ?? linkedForm?.appliesWhen ?? [],
        });

        await tx.$executeRaw`
          INSERT INTO packet_template_items (
            id, packet_template_id, sort_order, item_type, display_name, form_template_id,
            document_type_code, source_strategy, condition_expression_json, is_required, created_at
          ) VALUES (
            ${createId()},
            ${packetTemplateId},
            ${item.sortOrder ?? payload.finalPacketItems.indexOf(item) + 1},
            ${
              item.sourceType === "form"
                ? "workflow_form"
                : item.sourceType === "condition"
                  ? "workflow_condition_group"
                  : "workflow_document"
            },
            ${displayName},
            ${linkedForm?.formTemplateId ?? null},
            ${linkedDocument?.documentTypeCode ?? null},
            ${"workflow_final_process"},
            ${conditionExpressionJson},
            1,
            CURRENT_TIMESTAMP
          )
        `;
      }
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "workflow_template",
      entityId: templateId,
      action: "workflow_template.update",
      beforeJson: {
        code: template.code,
        name: template.name,
        caseTypeCode: template.case_type_code,
        caseSubtypeCode: template.case_subtype_code,
        description: template.description,
      },
      afterJson: {
        code: template.code,
        name: payload.name,
        caseTypeCode: payload.caseTypeCode,
        caseSubtypeCode: payload.caseSubtypeCode || null,
        description: payload.description || null,
        steps: payload.steps.length,
        requiredDocuments: payload.requiredDocuments.length,
        requiredForms: payload.requiredForms.length,
        finalPacketItems: payload.finalPacketItems.length,
      },
      request,
    });

    return reply.send({ updated: true, id: templateId, code: template.code });
  });

  app.delete("/templates/:templateId", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { templateId } = request.params as { templateId: string };

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const [template] = await prisma.$queryRaw<
      Array<{
        id: string;
        law_firm_id: string | null;
        code: string;
        name: string;
        case_type_code: string;
        case_subtype_code: string | null;
        description: string | null;
        is_system_template: number;
      }>
    >`
      SELECT
        id,
        law_firm_id,
        code,
        name,
        case_type_code,
        case_subtype_code,
        description,
        is_system_template
      FROM workflow_templates
      WHERE id = ${templateId}
      LIMIT 1
    `;

    if (!template) {
      throw reply.notFound("Workflow template not found");
    }

    if (Boolean(template.is_system_template)) {
      throw reply.badRequest("System workflow templates cannot be deleted");
    }

    if (template.law_firm_id !== profile.lawFirm.id) {
      throw reply.notFound("Workflow template not found");
    }

    const [instanceUsage] = await prisma.$queryRaw<Array<{ total: bigint | number }>>`
      SELECT COUNT(*) AS total
      FROM workflow_instances
      WHERE workflow_template_id = ${templateId}
        AND law_firm_id = ${profile.lawFirm.id}
    `;

    const deletedInstanceCount = Number(instanceUsage?.total ?? 0);

    await prisma.$transaction(async (tx) => {
      await tx.$executeRaw`
        UPDATE missing_information_rules
        SET workflow_template_id = NULL
        WHERE workflow_template_id = ${templateId}
      `;
      await tx.$executeRaw`
        UPDATE questionnaire_runs qr
        JOIN workflow_instances wi ON wi.id = qr.workflow_instance_id
        SET qr.workflow_instance_id = NULL
        WHERE wi.workflow_template_id = ${templateId}
          AND qr.law_firm_id = ${profile.lawFirm.id}
      `;
      await tx.$executeRaw`
        UPDATE case_required_documents crd
        LEFT JOIN workflow_instances wi ON wi.id = crd.workflow_instance_id
        LEFT JOIN workflow_required_documents wrd ON wrd.id = crd.workflow_required_document_id
        SET
          crd.workflow_instance_id = NULL,
          crd.workflow_required_document_id = NULL
        WHERE crd.law_firm_id = ${profile.lawFirm.id}
          AND (
            wi.workflow_template_id = ${templateId}
            OR wrd.workflow_template_id = ${templateId}
          )
      `;
      await tx.$executeRaw`
        UPDATE case_forms cf
        LEFT JOIN workflow_instances wi ON wi.id = cf.workflow_instance_id
        LEFT JOIN workflow_required_forms wrf ON wrf.id = cf.workflow_required_form_id
        SET
          cf.workflow_instance_id = NULL,
          cf.workflow_required_form_id = NULL
        WHERE cf.law_firm_id = ${profile.lawFirm.id}
          AND (
            wi.workflow_template_id = ${templateId}
            OR wrf.workflow_template_id = ${templateId}
          )
      `;
      await tx.$executeRaw`
        DELETE wsi
        FROM workflow_step_instances wsi
        JOIN workflow_instances wi ON wi.id = wsi.workflow_instance_id
        WHERE wi.workflow_template_id = ${templateId}
      `;
      await tx.$executeRaw`
        DELETE FROM workflow_instances
        WHERE workflow_template_id = ${templateId}
          AND law_firm_id = ${profile.lawFirm.id}
      `;
      await tx.$executeRaw`
        DELETE FROM workflow_required_forms
        WHERE workflow_template_id = ${templateId}
      `;
      await tx.$executeRaw`
        DELETE FROM workflow_required_documents
        WHERE workflow_template_id = ${templateId}
      `;
      await tx.$executeRaw`
        DELETE FROM workflow_steps
        WHERE workflow_template_id = ${templateId}
      `;
      await tx.$executeRaw`
        DELETE pti
        FROM packet_template_items pti
        JOIN packet_templates pt ON pt.id = pti.packet_template_id
        WHERE pt.law_firm_id = ${profile.lawFirm.id}
          AND pt.code = ${buildWorkflowPacketTemplateCode(templateId)}
      `;
      await tx.$executeRaw`
        DELETE FROM packet_templates
        WHERE law_firm_id = ${profile.lawFirm.id}
          AND code = ${buildWorkflowPacketTemplateCode(templateId)}
      `;
      await tx.$executeRaw`
        DELETE FROM workflow_templates
        WHERE id = ${templateId}
          AND law_firm_id = ${profile.lawFirm.id}
      `;
    });

    await writeAuditLog({
      lawFirmId: profile.lawFirm.id,
      officeId: profile.user.primaryOfficeId ?? null,
      actorUserId: profile.user.id,
      entityType: "workflow_template",
      entityId: templateId,
      action: "workflow_template.delete",
      beforeJson: {
        code: template.code,
        name: template.name,
        caseTypeCode: template.case_type_code,
        caseSubtypeCode: template.case_subtype_code,
        description: template.description,
        deletedInstanceCount,
      },
      request,
    });

    return reply.send({ deleted: true });
  });

  app.post("/templates/:templateId/steps", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const payload = workflowStepSchema.parse(request.body);
    const { templateId } = request.params as { templateId: string };

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const id = createId();
    const responsePrompts = payload.responsePrompts
      .map((item, index) => ({
        id: item.id?.trim() || `prompt_${index + 1}`,
        question: item.question.trim(),
        targetType: item.targetType,
      }))
      .filter((item) => item.question);
    const conditionExpressionJson = buildWorkflowConditionExpressionJson({
      appliesWhen: payload.appliesWhen,
    });
    const completionRuleJson = responsePrompts.length
      ? JSON.stringify({ responsePrompts })
      : null;
    await prisma.$executeRaw`
      INSERT INTO workflow_steps (
        id, workflow_template_id, step_code, name, step_order, step_type, description,
        condition_expression_json, completion_rule_json, sla_days, created_at, updated_at
      ) VALUES (
        ${id},
        ${templateId},
        ${payload.stepCode},
        ${payload.name},
        ${payload.stepOrder},
        ${payload.stepType},
        ${payload.description || null},
        ${conditionExpressionJson},
        ${completionRuleJson},
        NULL,
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
    `;

    return reply.code(201).send({ id });
  });

  app.post("/templates/:templateId/required-documents", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const payload = requiredDocumentSchema.parse(request.body);
    const { templateId } = request.params as { templateId: string };

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const id = createId();
    const documentTypeCode = await resolveRequiredDocumentTypeCode(payload.documentTypeCode);
    const conditionExpressionJson = buildWorkflowConditionExpressionJson({
      aiInstructions: payload.aiInstructions,
      appliesWhen: payload.appliesWhen,
    });
    await prisma.$executeRaw`
      INSERT INTO workflow_required_documents (
        id, workflow_template_id, workflow_step_id, document_type_code, requirement_code,
        display_name, description, is_required, minimum_quantity, condition_expression_json, created_at
      ) VALUES (
        ${id},
        ${templateId},
        ${payload.workflowStepId ?? null},
        ${documentTypeCode},
        ${payload.requirementCode},
        ${payload.displayName},
        ${payload.description || null},
        ${payload.isRequired ? 1 : 0},
        ${payload.minimumQuantity},
        ${conditionExpressionJson},
        CURRENT_TIMESTAMP
      )
    `;

    return reply.code(201).send({ id });
  });

  app.post("/templates/:templateId/required-forms", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const payload = requiredFormSchema.parse(request.body);
    const { templateId } = request.params as { templateId: string };

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const id = createId();
    const conditionExpressionJson = buildWorkflowConditionExpressionJson({
      appliesWhen: payload.appliesWhen,
    });
    await prisma.$executeRaw`
      INSERT INTO workflow_required_forms (
        id, workflow_template_id, workflow_step_id, form_template_id, requirement_code,
        is_required, due_offset_days, condition_expression_json, created_at
      ) VALUES (
        ${id},
        ${templateId},
        ${payload.workflowStepId ?? null},
        ${payload.formTemplateId},
        ${payload.requirementCode},
        ${payload.isRequired ? 1 : 0},
        NULL,
        ${conditionExpressionJson},
        CURRENT_TIMESTAMP
      )
    `;

    return reply.code(201).send({ id });
  });

  app.post("/instances", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const payload = activateWorkflowSchema.parse(request.body);

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const [workflowTemplate] = await prisma.$queryRaw<
      Array<{ id: string }>
    >`
      SELECT id
      FROM workflow_templates
      WHERE id = ${payload.workflowTemplateId}
      LIMIT 1
    `;

    if (!workflowTemplate) {
      throw reply.notFound("Workflow template not found");
    }

    const [caseRow] = await prisma.$queryRaw<Array<{ id: string; client_id: string }>>`
      SELECT id, client_id
      FROM cases
      WHERE id = ${payload.caseId}
        AND law_firm_id = ${profile.lawFirm.id}
      LIMIT 1
    `;

    if (!caseRow) {
      throw reply.notFound("Case not found");
    }

    const caseFacts = await prisma.$queryRaw<
      Array<{
        field_key: string;
        raw_value: string | null;
        normalized_value_json: unknown;
      }>
    >`
      SELECT df.field_key, cf.raw_value, cf.normalized_value_json
      FROM case_facts cf
      JOIN data_fields df ON df.id = cf.data_field_id
      WHERE cf.law_firm_id = ${profile.lawFirm.id}
        AND cf.client_id = ${caseRow.client_id}
        AND (${payload.caseId} IS NULL OR cf.case_id = ${payload.caseId} OR cf.case_id IS NULL)
        AND cf.deleted_at IS NULL
        AND cf.status_code <> 'rejected'
      ORDER BY cf.created_at DESC
    `;
    const caseFactMap = buildWorkflowConditionFactMap(caseFacts);
    const dependentCount = resolveWorkflowDependentCount(caseFactMap);

    const steps = await prisma.$queryRaw<
      Array<{ id: string; step_order: number; condition_expression_json: unknown }>
    >`
      SELECT id, step_order, condition_expression_json
      FROM workflow_steps
      WHERE workflow_template_id = ${payload.workflowTemplateId}
        AND retired_at IS NULL
      ORDER BY step_order ASC
    `;

    const materializedSteps = steps.flatMap((step) => {
      const appliesWhen = parseWorkflowConditionConfig(step.condition_expression_json).appliesWhen;

      if (!matchesWorkflowAppliesWhen(appliesWhen, caseFactMap)) {
        return [];
      }

      return [{
        ...step,
        appliesWhen,
      }];
    });

    const instanceId = createId();
    await prisma.$executeRaw`
      INSERT INTO workflow_instances (
        id, law_firm_id, case_id, workflow_template_id, status_code, started_at,
        completed_at, current_step_order, template_snapshot_json, created_by_user_id,
        created_at, updated_at
      ) VALUES (
        ${instanceId},
        ${profile.lawFirm.id},
        ${payload.caseId},
        ${payload.workflowTemplateId},
        'active',
        NOW(),
        NULL,
        ${materializedSteps[0]?.step_order ?? 1},
        NULL,
        ${profile.user.id},
        CURRENT_TIMESTAMP,
        CURRENT_TIMESTAMP
      )
    `;

    for (const [stepIndex, step] of materializedSteps.entries()) {
      const decisionJson =
        step.appliesWhen.includes("has_dependents") && dependentCount > 0
          ? JSON.stringify({ dependentCount })
          : null;

      await prisma.$executeRaw`
        INSERT INTO workflow_step_instances (
          id, workflow_instance_id, workflow_step_id, assignee_user_id, status_code,
          due_at, started_at, completed_at, decision_json, created_at, updated_at
        ) VALUES (
          ${createId()},
          ${instanceId},
          ${step.id},
          NULL,
          ${stepIndex === 0 ? "active" : "pending"},
          NULL,
          ${stepIndex === 0 ? new Date() : null},
          NULL,
          ${decisionJson},
          CURRENT_TIMESTAMP,
          CURRENT_TIMESTAMP
        )
      `;
    }

    const requiredDocuments = await prisma.$queryRaw<
      Array<{
        id: string;
        document_type_code: string;
        display_name: string;
        condition_expression_json: unknown;
        is_required: number;
      }>
    >`
      SELECT id, document_type_code, display_name, condition_expression_json, is_required
      FROM workflow_required_documents
      WHERE workflow_template_id = ${payload.workflowTemplateId}
        AND retired_at IS NULL
    `;

    let createdRequiredDocuments = 0;
    for (const item of requiredDocuments) {
      const appliesWhen = parseWorkflowConditionConfig(item.condition_expression_json).appliesWhen;

      if (
        !matchesWorkflowAppliesWhen(appliesWhen, caseFactMap)
      ) {
        continue;
      }

      const repeatCount = appliesWhen.includes("has_dependents")
        ? Math.max(1, dependentCount)
        : 1;

      for (let index = 0; index < repeatCount; index += 1) {
        const requirementName =
          repeatCount > 1
            ? `${item.display_name} • Dependente ${index + 1}`
            : item.display_name;

        await prisma.$executeRaw`
          INSERT INTO case_required_documents (
            id, law_firm_id, case_id, workflow_instance_id, workflow_required_document_id,
            document_type_code, requirement_name, is_required, status_code, due_at,
            satisfied_by_document_record_id, notes, created_at, updated_at
          ) VALUES (
            ${createId()},
            ${profile.lawFirm.id},
            ${payload.caseId},
            ${instanceId},
            ${item.id},
            ${item.document_type_code},
            ${requirementName},
            ${item.is_required},
            'pending',
            NULL,
            NULL,
            NULL,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
          )
        `;
        createdRequiredDocuments += 1;
      }
    }

    const requiredForms = await prisma.$queryRaw<
      Array<{
        id: string;
        form_template_id: string;
        form_name: string;
        condition_expression_json: unknown;
        is_required: number;
      }>
    >`
      SELECT wrf.id, wrf.form_template_id, ft.name AS form_name, wrf.condition_expression_json, wrf.is_required
      FROM workflow_required_forms wrf
      JOIN form_templates ft ON ft.id = wrf.form_template_id
      WHERE wrf.workflow_template_id = ${payload.workflowTemplateId}
        AND wrf.retired_at IS NULL
    `;

    let createdCaseForms = 0;
    for (const item of requiredForms) {
      const appliesWhen = parseWorkflowConditionConfig(item.condition_expression_json).appliesWhen;

      if (
        !matchesWorkflowAppliesWhen(appliesWhen, caseFactMap)
      ) {
        continue;
      }

      const repeatCount = appliesWhen.includes("has_dependents")
        ? Math.max(1, dependentCount)
        : 1;

      for (let index = 0; index < repeatCount; index += 1) {
        const requirementName =
          repeatCount > 1
            ? `${item.form_name} • Dependente ${index + 1}`
            : item.form_name;

        await prisma.$executeRaw`
          INSERT INTO case_forms (
            id, law_firm_id, case_id, workflow_instance_id, workflow_required_form_id,
            form_template_id, requirement_name, is_required, status_code, due_at,
            last_reviewed_at, created_at, updated_at
          ) VALUES (
            ${createId()},
            ${profile.lawFirm.id},
            ${payload.caseId},
            ${instanceId},
            ${item.id},
            ${item.form_template_id},
            ${requirementName},
            ${item.is_required},
            'pending',
            NULL,
            NULL,
            CURRENT_TIMESTAMP,
            CURRENT_TIMESTAMP
          )
        `;
        createdCaseForms += 1;
      }
    }

    return reply.code(201).send({
      workflowInstanceId: instanceId,
      createdSteps: materializedSteps.length,
      createdRequiredDocuments,
      createdCaseForms,
    });
  });

  app.get("/case/:caseId", async (request, reply) => {
    const session = await requireSession(request, reply);
    const profile = await getSessionProfile(session);
    const { caseId } = request.params as { caseId: string };

    if (!profile) {
      throw reply.unauthorized("Session is no longer valid");
    }

    const [instance] = await prisma.$queryRaw<
      Array<{
        id: string;
        workflow_template_id: string;
        status_code: string;
      }>
    >`
      SELECT id, workflow_template_id, status_code
      FROM workflow_instances
      WHERE case_id = ${caseId}
        AND law_firm_id = ${profile.lawFirm.id}
      ORDER BY created_at DESC
      LIMIT 1
    `;

    if (!instance) {
      return {
        workflowInstance: null,
        steps: [],
        requiredDocuments: [],
        caseForms: [],
      };
    }

    const [steps, requiredDocuments, caseForms] = await Promise.all([
      prisma.$queryRaw<
        Array<{
          id: string;
          status_code: string;
          step_name: string;
          step_order: number;
        }>
      >`
        SELECT wsi.id, wsi.status_code, ws.name AS step_name, ws.step_order
        FROM workflow_step_instances wsi
        JOIN workflow_steps ws ON ws.id = wsi.workflow_step_id
        WHERE wsi.workflow_instance_id = ${instance.id}
        ORDER BY ws.step_order ASC
      `,
      prisma.$queryRaw<
        Array<{
          id: string;
          requirement_name: string;
          status_code: string;
        }>
      >`
        SELECT id, requirement_name, status_code
        FROM case_required_documents
        WHERE workflow_instance_id = ${instance.id}
        ORDER BY created_at ASC
      `,
      prisma.$queryRaw<
        Array<{
          id: string;
          requirement_name: string;
          status_code: string;
        }>
      >`
        SELECT id, requirement_name, status_code
        FROM case_forms
        WHERE workflow_instance_id = ${instance.id}
        ORDER BY created_at ASC
      `,
    ]);

    return {
      workflowInstance: instance,
      steps,
      requiredDocuments,
      caseForms,
    };
  });
}