import { randomUUID } from 'node:crypto';
import type pg from 'pg';
import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, optionalString, readJsonBody, requiredString, stringParam } from '../../core/request.js';
import { query, queryOne, transaction } from '../../core/db.js';
import { requireTenantContentEditor, type TenantContentAuth } from './auth.js';
import { boolValue, intValue, jsonArrayValue, jsonObjectValue, nullableString } from './utils.js';

const ENTRY_TYPES = ['question_practice', 'vocabulary', 'handbook', 'scoreline', 'resource', 'ai_report', 'custom'];
const ENTRY_VISIBILITIES = ['public', 'members', 'svip', 'hidden'];
const NODE_TYPES = ['category', 'subject', 'chapter', 'paper', 'school', 'major', 'exam_target', 'resource_group', 'custom'];
const MARKER_TYPES = ['school', 'major', 'subject', 'exam_track', 'course_package', 'sales_intent', 'custom'];
const COLLECTION_TYPES = ['dynamic', 'manual', 'paper', 'chapter', 'mock_exam'];
const COLLECTION_SOURCE_TYPES = ['filters', 'manual_questions', 'node_descendants', 'category', 'subject', 'question_bank'];
const PRACTICE_MODES = ['sequential', 'random', 'mock_exam', 'paper', 'wrong_review', 'favorite_review'];
const ASSEMBLY_TYPES = ['collection', 'node_descendants', 'manual', 'filters'];
const STATUSES = ['draft', 'active', 'archived'];

type JsonObject = Record<string, unknown>;

function choice(value: unknown, allowed: string[], fallback: string, label: string) {
  const candidate = nullableString(value) || fallback;
  if (!allowed.includes(candidate)) {
    throw new HttpError(400, `Invalid ${label}: ${candidate}`, 'INVALID_FIELD_VALUE');
  }
  return candidate;
}

function optionalChoice(value: unknown, allowed: string[], label: string) {
  const candidate = nullableString(value);
  if (!candidate) return null;
  if (!allowed.includes(candidate)) {
    throw new HttpError(400, `Invalid ${label}: ${candidate}`, 'INVALID_FIELD_VALUE');
  }
  return candidate;
}

function arrayFrom(value: unknown) {
  return Array.isArray(value) ? value : [];
}

function uuidArray(value: unknown) {
  return arrayFrom(value)
    .map(item => (typeof item === 'string' ? item.trim() : ''))
    .filter(Boolean);
}

function ltreeLabel(id: string) {
  return `n_${id.replace(/-/g, '')}`;
}

async function assertOptionalTenantReference(
  client: pg.PoolClient,
  tenantId: string,
  table: string,
  id: string | null,
  code: string,
) {
  if (!id) return;
  const row = await client.query(
    `select id from public.${table} where tenant_id = $1 and id = $2 limit 1`,
    [tenantId, id],
  );
  if (!row.rows[0]) {
    throw new HttpError(400, `${table} reference is not in this tenant`, code);
  }
}

async function recordNavigationAudit(auth: TenantContentAuth, action: string, targetType: string, targetId: string | null, details: JsonObject) {
  await query(
    `
    insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
    values ($1, $2, $3, $4, $5, $6::jsonb)
    `,
    [auth.tenantId, auth.userId, action, targetType, targetId, JSON.stringify(details)],
  );
}

export async function contentEntriesAdminRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const regionId = stringParam(ctx, 'regionId');
  const entryType = stringParam(ctx, 'entryType');

  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (regionId) {
    params.push(regionId);
    filters.push(`region_id = $${params.length}`);
  }
  if (entryType) {
    params.push(entryType);
    filters.push(`entry_type = $${params.length}`);
  }

  const items = await query(
    `
    select id, region_id as "regionId", legacy_id as "legacyId",
           entry_key as "entryKey", name, entry_type as "entryType",
           icon, route, description, visibility, access_rules as "accessRules",
           layout_config as "layoutConfig", sort_order as "order",
           is_active as "isActive", created_by as "createdBy",
           updated_by as "updatedBy", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.content_entries
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    `,
    params,
  );

  return { items };
}

export async function upsertContentEntryRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const name = requiredString(body, 'name');
  const entryKey = optionalString(body, 'entryKey') || optionalString(body, 'key') || nullableString(body.id) || randomUUID();
  const regionId = nullableString(body.regionId);
  const entryType = choice(body.entryType, ENTRY_TYPES, 'question_practice', 'entryType');
  const visibility = choice(body.visibility, ENTRY_VISIBILITIES, 'public', 'visibility');

  const item = await transaction(async client => {
    await assertOptionalTenantReference(client, auth.tenantId, 'regions', regionId, 'REGION_NOT_FOUND');

    const result = await client.query(
      `
      insert into public.content_entries (
        id, tenant_id, region_id, legacy_id, entry_key, name, entry_type,
        icon, route, description, visibility, access_rules, layout_config,
        sort_order, is_active, created_by, updated_by
      )
      values (
        coalesce($1::uuid, gen_random_uuid()), $2, $3::uuid, $4, $5, $6, $7,
        $8, $9, $10, $11, $12::jsonb, $13::jsonb,
        $14, $15, $16, $16
      )
      on conflict (tenant_id, entry_key)
      do update set region_id = excluded.region_id,
                    legacy_id = excluded.legacy_id,
                    name = excluded.name,
                    entry_type = excluded.entry_type,
                    icon = excluded.icon,
                    route = excluded.route,
                    description = excluded.description,
                    visibility = excluded.visibility,
                    access_rules = excluded.access_rules,
                    layout_config = excluded.layout_config,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      returning id, region_id as "regionId", legacy_id as "legacyId",
                entry_key as "entryKey", name, entry_type as "entryType",
                icon, route, description, visibility, access_rules as "accessRules",
                layout_config as "layoutConfig", sort_order as "order",
                is_active as "isActive", created_at as "createdAt",
                updated_at as "updatedAt"
      `,
      [
        nullableString(body.id),
        auth.tenantId,
        regionId,
        nullableString(body.legacyId),
        entryKey,
        name,
        entryType,
        nullableString(body.icon),
        nullableString(body.route),
        nullableString(body.description),
        visibility,
        jsonObjectValue(body.accessRules),
        jsonObjectValue(body.layoutConfig),
        intValue(body.order, 0),
        boolValue(body.isActive, true),
        auth.userId,
      ],
    );

    return result.rows[0];
  });

  await recordNavigationAudit(auth, 'content.entry.upserted', 'content_entry', String(item.id), { name, entryKey, entryType });
  return { item };
}

async function buildNodePath(client: pg.PoolClient, tenantId: string, entryId: string, nodeId: string, parentId: string | null) {
  if (!parentId) return { path: ltreeLabel(nodeId), depth: 0, parentPath: null as string | null };

  const parent = await client.query<{ path: string; depth: number }>(
    `
    select path::text as path, depth
    from public.content_nodes
    where tenant_id = $1 and entry_id = $2 and id = $3
    limit 1
    `,
    [tenantId, entryId, parentId],
  );
  if (!parent.rows[0]) {
    throw new HttpError(400, 'parentId is not in this entry', 'PARENT_NODE_NOT_FOUND');
  }

  return {
    path: `${parent.rows[0].path}.${ltreeLabel(nodeId)}`,
    depth: Number(parent.rows[0].depth || 0) + 1,
    parentPath: parent.rows[0].path,
  };
}

export async function contentNodesAdminRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const entryId = stringParam(ctx, 'entryId');
  const parentParam = ctx.url.searchParams.get('parentId');
  const includeInactive = stringParam(ctx, 'includeInactive') === 'true';
  const markerType = stringParam(ctx, 'markerType');
  const mode = stringParam(ctx, 'mode') || 'children';

  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (entryId) {
    params.push(entryId);
    filters.push(`entry_id = $${params.length}`);
  }
  if (parentParam !== null) {
    if (parentParam === 'root') {
      filters.push('parent_id is null');
    } else {
      params.push(parentParam);
      filters.push(`parent_id = $${params.length}`);
    }
  }
  if (!includeInactive) filters.push('is_active = true');
  if (markerType) {
    params.push(markerType);
    filters.push(`marker_type = $${params.length}`);
  }

  const orderClause = mode === 'flat' ? 'coalesce(path::text, name) asc, sort_order asc' : 'sort_order asc, created_at asc';
  const items = await query(
    `
    select id, entry_id as "entryId", region_id as "regionId",
           parent_id as "parentId", legacy_id as "legacyId",
           node_key as "nodeKey", name, node_type as "nodeType",
           marker_type as "markerType", marker_config as "markerConfig",
           path::text as path, depth, sort_order as "order",
           is_active as "isActive", is_selectable as "isSelectable",
           is_leaf as "isLeaf", access_rules as "accessRules", metadata, created_by as "createdBy",
           updated_by as "updatedBy", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.content_nodes
    where ${filters.join(' and ')}
    order by ${orderClause}
    `,
    params,
  );

  return { items };
}

export async function upsertContentNodeRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  let nodeId = nullableString(body.id) || randomUUID();
  const entryId = requiredString(body, 'entryId');
  const parentId = nullableString(body.parentId);
  const name = requiredString(body, 'name');
  const nodeKey = nullableString(body.nodeKey) || nullableString(body.key);
  const nodeType = choice(body.nodeType, NODE_TYPES, 'category', 'nodeType');
  const markerType = optionalChoice(body.markerType, MARKER_TYPES, 'markerType');
  const regionId = nullableString(body.regionId);

  const item = await transaction(async client => {
    await assertOptionalTenantReference(client, auth.tenantId, 'content_entries', entryId, 'ENTRY_NOT_FOUND');
    await assertOptionalTenantReference(client, auth.tenantId, 'regions', regionId, 'REGION_NOT_FOUND');

    if (!nullableString(body.id) && nodeKey) {
      const keyedNode = await client.query<{ id: string }>(
        `
        select id
        from public.content_nodes
        where tenant_id = $1 and entry_id = $2 and node_key = $3
        limit 1
        for update
        `,
        [auth.tenantId, entryId, nodeKey],
      );
      nodeId = keyedNode.rows[0]?.id || nodeId;
    }

    const existing = await client.query<{ path: string | null; parent_id: string | null }>(
      'select path::text as path, parent_id from public.content_nodes where tenant_id = $1 and id = $2 limit 1 for update',
      [auth.tenantId, nodeId],
    );
    const previousPath = existing.rows[0]?.path || null;
    const { path, depth } = await buildNodePath(client, auth.tenantId, entryId, nodeId, parentId);

    const result = await client.query(
      `
      insert into public.content_nodes (
        id, tenant_id, entry_id, region_id, parent_id, legacy_id, node_key,
        name, node_type, marker_type, marker_config, path, depth, sort_order,
        is_active, is_selectable, is_leaf, access_rules, metadata, created_by, updated_by
      )
      values (
        $1, $2, $3, $4::uuid, $5::uuid, $6, $7,
        $8, $9, $10, $11::jsonb, $12::ltree, $13, $14,
        $15, $16, $17, $18::jsonb, $19::jsonb, $20, $20
      )
      on conflict (id)
      do update set entry_id = excluded.entry_id,
                    region_id = excluded.region_id,
                    parent_id = excluded.parent_id,
                    legacy_id = excluded.legacy_id,
                    node_key = excluded.node_key,
                    name = excluded.name,
                    node_type = excluded.node_type,
                    marker_type = excluded.marker_type,
                    marker_config = excluded.marker_config,
                    path = excluded.path,
                    depth = excluded.depth,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    is_selectable = excluded.is_selectable,
                    is_leaf = excluded.is_leaf,
                    access_rules = excluded.access_rules,
                    metadata = excluded.metadata,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      where public.content_nodes.tenant_id = excluded.tenant_id
      returning id, entry_id as "entryId", region_id as "regionId",
                parent_id as "parentId", legacy_id as "legacyId",
                node_key as "nodeKey", name, node_type as "nodeType",
                marker_type as "markerType", marker_config as "markerConfig",
                path::text as path, depth, sort_order as "order",
                is_active as "isActive", is_selectable as "isSelectable",
                is_leaf as "isLeaf", access_rules as "accessRules", metadata, created_at as "createdAt",
                updated_at as "updatedAt"
      `,
      [
        nodeId,
        auth.tenantId,
        entryId,
        regionId,
        parentId,
        nullableString(body.legacyId),
        nodeKey,
        name,
        nodeType,
        markerType,
        jsonObjectValue(body.markerConfig),
        path,
        depth,
        intValue(body.order, 0),
        boolValue(body.isActive, true),
        boolValue(body.isSelectable, true),
        boolValue(body.isLeaf, false),
        jsonObjectValue(body.accessRules),
        jsonObjectValue(body.metadata),
        auth.userId,
      ],
    );

    if (parentId) {
      await client.query(
        'update public.content_nodes set is_leaf = false, updated_at = now() where tenant_id = $1 and id = $2',
        [auth.tenantId, parentId],
      );
    }

    if (previousPath && previousPath !== path) {
      await client.query(
        `
        update public.content_nodes
        set path = $3::ltree || subpath(path, nlevel($2::ltree)),
            depth = nlevel($3::ltree || subpath(path, nlevel($2::ltree))) - 1,
            updated_at = now()
        where tenant_id = $1
          and id <> $4
          and path <@ $2::ltree
        `,
        [auth.tenantId, previousPath, path, nodeId],
      );
    }

    return result.rows[0];
  });

  await recordNavigationAudit(auth, 'content.node.upserted', 'content_node', String(item.id), { name, nodeType, markerType });
  return { item };
}

export async function questionCollectionsAdminRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const nodeId = stringParam(ctx, 'nodeId');
  const entryId = stringParam(ctx, 'entryId');
  const status = stringParam(ctx, 'status');
  const limit = intParam(ctx, 'limit', 100, 500);

  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (nodeId) {
    params.push(nodeId);
    filters.push(`node_id = $${params.length}`);
  }
  if (entryId) {
    params.push(entryId);
    filters.push(`entry_id = $${params.length}`);
  }
  if (status) {
    params.push(status);
    filters.push(`status = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select id, region_id as "regionId", entry_id as "entryId",
           node_id as "nodeId", subject_id as "subjectId",
           category_id as "categoryId", question_bank_id as "questionBankId",
           legacy_id as "legacyId", name, collection_type as "collectionType",
           source_type as "sourceType", filters, question_count as "questionCount",
           total_score as "totalScore", duration_minutes as "durationMinutes",
           status, sort_order as "order", access_rules as "accessRules", metadata,
           created_by as "createdBy", updated_by as "updatedBy",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.question_collections
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

async function assertCollectionReferences(client: pg.PoolClient, auth: TenantContentAuth, body: JsonObject) {
  const regionId = nullableString(body.regionId);
  const entryId = nullableString(body.entryId);
  const nodeId = nullableString(body.nodeId);
  const subjectId = nullableString(body.subjectId);
  const categoryId = nullableString(body.categoryId);
  const questionBankId = nullableString(body.questionBankId);

  await assertOptionalTenantReference(client, auth.tenantId, 'regions', regionId, 'REGION_NOT_FOUND');
  await assertOptionalTenantReference(client, auth.tenantId, 'content_entries', entryId, 'ENTRY_NOT_FOUND');
  await assertOptionalTenantReference(client, auth.tenantId, 'content_nodes', nodeId, 'CONTENT_NODE_NOT_FOUND');
  await assertOptionalTenantReference(client, auth.tenantId, 'subjects', subjectId, 'SUBJECT_NOT_FOUND');
  await assertOptionalTenantReference(client, auth.tenantId, 'categories', categoryId, 'CATEGORY_NOT_FOUND');
  await assertOptionalTenantReference(client, auth.tenantId, 'question_banks', questionBankId, 'QUESTION_BANK_NOT_FOUND');

  return { regionId, entryId, nodeId, subjectId, categoryId, questionBankId };
}

async function replaceCollectionItems(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  collectionId: string,
  questions: unknown[],
) {
  await client.query('delete from public.question_collection_items where tenant_id = $1 and collection_id = $2', [auth.tenantId, collectionId]);

  let order = 0;
  for (const raw of questions) {
    const item = typeof raw === 'string' ? { questionId: raw } : raw && typeof raw === 'object' ? raw as JsonObject : {};
    const questionId = nullableString(item.questionId ?? item.id);
    if (!questionId) continue;
    const question = await client.query<{ id: string }>(
      'select id from public.questions where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, questionId],
    );
    if (!question.rows[0]) {
      throw new HttpError(400, `Question ${questionId} is not in this tenant`, 'QUESTION_NOT_FOUND');
    }
    await client.query(
      `
      insert into public.question_collection_items (
        tenant_id, collection_id, question_id, section_key, sort_order, score, required, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8::jsonb)
      on conflict (tenant_id, collection_id, question_id)
      do update set section_key = excluded.section_key,
                    sort_order = excluded.sort_order,
                    score = excluded.score,
                    required = excluded.required,
                    metadata = excluded.metadata,
                    updated_at = now()
      `,
      [
        auth.tenantId,
        collectionId,
        questionId,
        nullableString(item.sectionKey),
        item.order === undefined ? order : intValue(item.order, order),
        item.score === undefined || item.score === null ? null : Number(item.score),
        boolValue(item.required, true),
        jsonObjectValue(item.metadata),
      ],
    );
    order += 1;
  }

  const count = await client.query<{ count: string }>(
    'select count(*)::text as count from public.question_collection_items where tenant_id = $1 and collection_id = $2',
    [auth.tenantId, collectionId],
  );
  const questionCount = Number(count.rows[0]?.count || 0);
  await client.query(
    'update public.question_collections set question_count = $3, updated_at = now() where tenant_id = $1 and id = $2',
    [auth.tenantId, collectionId, questionCount],
  );
  return questionCount;
}

export async function upsertQuestionCollectionRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const name = requiredString(body, 'name');
  const collectionType = choice(body.collectionType, COLLECTION_TYPES, 'dynamic', 'collectionType');
  const sourceType = choice(body.sourceType, COLLECTION_SOURCE_TYPES, collectionType === 'manual' ? 'manual_questions' : 'filters', 'sourceType');
  const status = choice(body.status, STATUSES, 'active', 'status');

  const item = await transaction(async client => {
    const refs = await assertCollectionReferences(client, auth, body);
    const result = await client.query(
      `
      insert into public.question_collections (
        id, tenant_id, region_id, entry_id, node_id, subject_id,
        category_id, question_bank_id, legacy_id, name, collection_type,
        source_type, filters, question_count, total_score, duration_minutes,
        status, sort_order, access_rules, metadata, created_by, updated_by
      )
      values (
        coalesce($1::uuid, gen_random_uuid()), $2, $3::uuid, $4::uuid, $5::uuid, $6::uuid,
        $7::uuid, $8::uuid, $9, $10, $11,
        $12, $13::jsonb, $14, $15, $16,
        $17, $18, $19::jsonb, $20::jsonb, $21, $21
      )
      on conflict (id)
      do update set region_id = excluded.region_id,
                    entry_id = excluded.entry_id,
                    node_id = excluded.node_id,
                    subject_id = excluded.subject_id,
                    category_id = excluded.category_id,
                    question_bank_id = excluded.question_bank_id,
                    legacy_id = excluded.legacy_id,
                    name = excluded.name,
                    collection_type = excluded.collection_type,
                    source_type = excluded.source_type,
                    filters = excluded.filters,
                    question_count = excluded.question_count,
                    total_score = excluded.total_score,
                    duration_minutes = excluded.duration_minutes,
                    status = excluded.status,
                    sort_order = excluded.sort_order,
                    access_rules = excluded.access_rules,
                    metadata = excluded.metadata,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      where public.question_collections.tenant_id = excluded.tenant_id
      returning id, region_id as "regionId", entry_id as "entryId",
                node_id as "nodeId", subject_id as "subjectId",
                category_id as "categoryId", question_bank_id as "questionBankId",
                legacy_id as "legacyId", name, collection_type as "collectionType",
                source_type as "sourceType", filters, question_count as "questionCount",
                total_score as "totalScore", duration_minutes as "durationMinutes",
                status, sort_order as "order", access_rules as "accessRules", metadata,
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        nullableString(body.id),
        auth.tenantId,
        refs.regionId,
        refs.entryId,
        refs.nodeId,
        refs.subjectId,
        refs.categoryId,
        refs.questionBankId,
        nullableString(body.legacyId),
        name,
        collectionType,
        sourceType,
        jsonObjectValue(body.filters),
        body.questionCount === undefined ? 0 : intValue(body.questionCount, 0),
        body.totalScore === undefined || body.totalScore === null ? null : Number(body.totalScore),
        body.durationMinutes === undefined ? null : intValue(body.durationMinutes, 0),
        status,
        intValue(body.order, 0),
        jsonObjectValue(body.accessRules),
        jsonObjectValue(body.metadata),
        auth.userId,
      ],
    );

    let collection = result.rows[0];
    if (Array.isArray(body.questions) || Array.isArray(body.questionIds)) {
      const questionCount = await replaceCollectionItems(client, auth, collection.id, Array.isArray(body.questions) ? body.questions : uuidArray(body.questionIds));
      collection = { ...collection, questionCount };
    }

    return collection;
  });

  await recordNavigationAudit(auth, 'content.collection.upserted', 'question_collection', String(item.id), { name, collectionType, sourceType });
  return { item };
}

export async function replaceQuestionCollectionItemsRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const collectionId = requiredString(body, 'collectionId');
  const questions = Array.isArray(body.questions) ? body.questions : uuidArray(body.questionIds);

  const item = await transaction(async client => {
    await assertOptionalTenantReference(client, auth.tenantId, 'question_collections', collectionId, 'QUESTION_COLLECTION_NOT_FOUND');
    const questionCount = await replaceCollectionItems(client, auth, collectionId, questions);
    return { collectionId, questionCount };
  });

  await recordNavigationAudit(auth, 'content.collection.items_replaced', 'question_collection', collectionId, { questionCount: item.questionCount });
  return { item };
}

export async function practiceBlueprintsAdminRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const nodeId = stringParam(ctx, 'nodeId');
  const collectionId = stringParam(ctx, 'collectionId');
  const mode = stringParam(ctx, 'mode');
  const limit = intParam(ctx, 'limit', 100, 500);

  const params: unknown[] = [auth.tenantId];
  const filters = ['tenant_id = $1'];
  if (nodeId) {
    params.push(nodeId);
    filters.push(`node_id = $${params.length}`);
  }
  if (collectionId) {
    params.push(collectionId);
    filters.push(`collection_id = $${params.length}`);
  }
  if (mode) {
    params.push(mode);
    filters.push(`mode = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select id, region_id as "regionId", entry_id as "entryId",
           node_id as "nodeId", collection_id as "collectionId",
           legacy_id as "legacyId", name, mode,
           assembly_type as "assemblyType", question_limit as "questionLimit",
           duration_minutes as "durationMinutes", total_score as "totalScore",
           pass_score as "passScore", sections, rules, access_rules as "accessRules", status,
           sort_order as "order", created_by as "createdBy",
           updated_by as "updatedBy", created_at as "createdAt",
           updated_at as "updatedAt"
    from public.practice_blueprints
    where ${filters.join(' and ')}
    order by sort_order asc, created_at asc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function upsertPracticeBlueprintRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const name = requiredString(body, 'name');
  const mode = choice(body.mode, PRACTICE_MODES, 'sequential', 'mode');
  const assemblyType = choice(body.assemblyType, ASSEMBLY_TYPES, 'collection', 'assemblyType');
  const status = choice(body.status, STATUSES, 'active', 'status');

  const item = await transaction(async client => {
    const regionId = nullableString(body.regionId);
    const entryId = nullableString(body.entryId);
    const nodeId = nullableString(body.nodeId);
    const collectionId = nullableString(body.collectionId);
    await assertOptionalTenantReference(client, auth.tenantId, 'regions', regionId, 'REGION_NOT_FOUND');
    await assertOptionalTenantReference(client, auth.tenantId, 'content_entries', entryId, 'ENTRY_NOT_FOUND');
    await assertOptionalTenantReference(client, auth.tenantId, 'content_nodes', nodeId, 'CONTENT_NODE_NOT_FOUND');
    await assertOptionalTenantReference(client, auth.tenantId, 'question_collections', collectionId, 'QUESTION_COLLECTION_NOT_FOUND');

    const result = await client.query(
      `
      insert into public.practice_blueprints (
        id, tenant_id, region_id, entry_id, node_id, collection_id,
        legacy_id, name, mode, assembly_type, question_limit,
        duration_minutes, total_score, pass_score, sections, rules,
        access_rules, status, sort_order, created_by, updated_by
      )
      values (
        coalesce($1::uuid, gen_random_uuid()), $2, $3::uuid, $4::uuid, $5::uuid, $6::uuid,
        $7, $8, $9, $10, $11,
        $12, $13, $14, $15::jsonb, $16::jsonb,
        $17::jsonb, $18, $19, $20, $20
      )
      on conflict (id)
      do update set region_id = excluded.region_id,
                    entry_id = excluded.entry_id,
                    node_id = excluded.node_id,
                    collection_id = excluded.collection_id,
                    legacy_id = excluded.legacy_id,
                    name = excluded.name,
                    mode = excluded.mode,
                    assembly_type = excluded.assembly_type,
                    question_limit = excluded.question_limit,
                    duration_minutes = excluded.duration_minutes,
                    total_score = excluded.total_score,
                    pass_score = excluded.pass_score,
                    sections = excluded.sections,
                    rules = excluded.rules,
                    access_rules = excluded.access_rules,
                    status = excluded.status,
                    sort_order = excluded.sort_order,
                    updated_by = excluded.updated_by,
                    updated_at = now()
      where public.practice_blueprints.tenant_id = excluded.tenant_id
      returning id, region_id as "regionId", entry_id as "entryId",
                node_id as "nodeId", collection_id as "collectionId",
                legacy_id as "legacyId", name, mode,
                assembly_type as "assemblyType", question_limit as "questionLimit",
                duration_minutes as "durationMinutes", total_score as "totalScore",
                pass_score as "passScore", sections, rules, access_rules as "accessRules", status,
                sort_order as "order", created_at as "createdAt",
                updated_at as "updatedAt"
      `,
      [
        nullableString(body.id),
        auth.tenantId,
        regionId,
        entryId,
        nodeId,
        collectionId,
        nullableString(body.legacyId),
        name,
        mode,
        assemblyType,
        body.questionLimit === undefined ? null : intValue(body.questionLimit, 0),
        body.durationMinutes === undefined ? null : intValue(body.durationMinutes, 0),
        body.totalScore === undefined || body.totalScore === null ? null : Number(body.totalScore),
        body.passScore === undefined || body.passScore === null ? null : Number(body.passScore),
        jsonArrayValue(body.sections),
        jsonObjectValue(body.rules),
        jsonObjectValue(body.accessRules),
        status,
        intValue(body.order, 0),
        auth.userId,
      ],
    );

    return result.rows[0];
  });

  await recordNavigationAudit(auth, 'content.practice_blueprint.upserted', 'practice_blueprint', String(item.id), { name, mode });
  return { item };
}
