import crypto 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, transaction } from '../../core/db.js';
import { requireTenantContentEditor, type TenantContentAuth } from './auth.js';
import { boolValue, intValue, jsonObjectValue, nullableString } from './utils.js';

interface EligibleBankRow {
  grantId: string;
  sourceQuestionBankId: string;
  sourceQuestionBankName: string;
  sourceTenantId: string;
  sourceRegionId: string | null;
  sourceRegionName: string | null;
  grantScope: string;
  allowedPlanCodes: string[];
  allowedRegionIds: string[];
  allowedSubjectIds: string[];
  accessPlanCode: string | null;
  accessMode: string | null;
  questionCount: number;
  adoptedId: string | null;
  adoptionStatus: string | null;
  syncStatus: string | null;
  targetQuestionBankId: string | null;
  targetEntryId: string | null;
  targetCollectionId: string | null;
  copiedQuestionCount: number | null;
  lastSyncedAt: string | null;
}

interface AdoptionRow {
  id: string;
  tenantId: string;
  sourceQuestionBankId: string;
  grantId: string | null;
  targetQuestionBankId: string | null;
  targetEntryId: string | null;
  targetCollectionId: string | null;
  adoptionMode: string;
  status: string;
  syncStatus: string;
  sourceSnapshot: unknown;
  copiedQuestionCount: number;
  metadata: Record<string, unknown>;
  createdAt: string;
  updatedAt: string;
}

interface ContentNotificationRow {
  id: string;
  notificationType: string;
  status: string;
  severity: string;
  adoptionId: string | null;
  sourceQuestionBankId: string | null;
  title: string;
  message: string;
  actionLabel: string | null;
  actionPath: string | null;
  metadata: Record<string, unknown>;
  createdAt: string;
  updatedAt: string;
  readAt: string | null;
  resolvedAt: string | null;
}

interface SourceQuestionSnapshot {
  id: string;
  type: string;
  typeLabel: string | null;
  difficulty: number | null;
  tags: unknown[];
  mediaUrl: string | null;
  hasVideoExplanation: boolean;
  content: string | null;
  options: unknown[];
  correctOptionIndex: number | null;
  correctOptionIndices: unknown[];
  answerText: string | null;
  explanation: string | null;
  subQuestions: unknown[];
  codeLang: string | null;
  codeTemplate: string | null;
  sourceHash: string;
}

interface SyncQuestionResult {
  sourceQuestionId: string;
  targetQuestionId: string | null;
  action: 'inserted' | 'updated' | 'skipped' | 'conflict';
  sourceHash: string;
  previousSourceHash: string | null;
  targetHash: string | null;
  resolution?: 'keep_local';
}

interface PublicQuestionBankSyncAuth {
  tenantId: string;
  userId: string | null;
  role: string;
  permissions: Record<string, unknown>;
  templatePermissions: Record<string, unknown>;
}

export interface PublicQuestionBankSyncInput {
  tenantId: string;
  adoptionId: string;
  actorUserId?: string | null;
  copyLimit?: number;
  triggeredBy?: 'manual' | 'worker';
  workerId?: string | null;
}

const PUBLIC_BANK_ACCESS_CTES = `
    with active_subscriptions as (
      select s.plan_code, s.metadata, coalesce(p.feature_flags, '{}'::jsonb) as plan_feature_flags
      from public.tenant_subscriptions s
      left join public.platform_saas_plans p on p.code = s.plan_code
      where s.tenant_id = $1
        and s.status in ('trial', 'active')
        and (s.expires_at is null or s.expires_at > now())
    ),
    source_subjects as (
      select q.question_bank_id, array_agg(distinct q.subject_id) filter (where q.subject_id is not null) as subject_ids
      from public.questions q
      where q.status = 'published'
      group by q.question_bank_id
    ),
    eligible_grants as (
      select g.*,
             access.plan_code as access_plan_code,
             access.access_mode
      from public.question_bank_grants g
      join public.question_banks source_qb
        on source_qb.id = g.source_question_bank_id
       and source_qb.source_scope = 'platform'
       and source_qb.status = 'active'
      left join source_subjects ss on ss.question_bank_id = source_qb.id
      left join lateral (
        select s.plan_code,
               coalesce(
                 nullif(s.metadata #>> '{publicQuestionBankAccess,mode}', ''),
                 nullif(s.plan_feature_flags #>> '{publicQuestionBanks,mode}', ''),
                 'all'
               ) as access_mode
        from active_subscriptions s
        where app.public_question_bank_subscription_allows(
          s.metadata,
          s.plan_feature_flags,
          source_qb.id,
          source_qb.region_id,
          coalesce(ss.subject_ids, '{}'::uuid[])
        )
        order by case when g.grant_scope in ('plans', 'mixed') and s.plan_code = any(g.allowed_plan_codes) then 0 else 1 end,
                 s.plan_code
        limit 1
      ) access on true
      where g.status = 'active'
        and (g.starts_at is null or g.starts_at <= now())
        and (g.expires_at is null or g.expires_at > now())
        and app.public_question_bank_grant_allows(
          g.allowed_region_ids,
          g.allowed_subject_ids,
          source_qb.region_id,
          coalesce(ss.subject_ids, '{}'::uuid[])
        )
        and (
          (
            g.grant_scope = 'all_active_tenants'
            and access.plan_code is not null
          )
          or (
            g.grant_scope in ('plans', 'mixed')
            and access.plan_code = any(g.allowed_plan_codes)
          )
          or (
            g.grant_scope in ('tenants', 'mixed')
            and $1 = any(g.allowed_tenant_ids)
            and (
              g.metadata->>'requiresActiveSubscription' = 'false'
              or access.plan_code is not null
            )
          )
        )
    )
`;

function slugFromName(name: string) {
  const ascii = name
    .normalize('NFKD')
    .replace(/[^\w\s-]/g, '')
    .trim()
    .toLowerCase()
    .replace(/[\s_]+/g, '-')
    .replace(/-+/g, '-')
    .slice(0, 48);
  return ascii || 'public-bank';
}

async function ensureTenantRegion(client: pg.PoolClient, targetTenantId: string, sourceTenantId: string, sourceRegionId: string | null) {
  if (!sourceRegionId) return null;
  const existing = await client.query<{ id: string }>(
    `
    select id
    from public.regions
    where tenant_id = $1
      and config->'sourceAdoption'->>'sourceTenantId' = $2
      and config->'sourceAdoption'->>'sourceRegionId' = $3
    limit 1
    `,
    [targetTenantId, sourceTenantId, sourceRegionId],
  );
  if (existing.rows[0]) return existing.rows[0].id;

  const source = await client.query<{
    name: string;
    code: string | null;
    short_name: string | null;
    full_name: string | null;
    icon: string | null;
    pinyin: string | null;
    sort_order: number;
    is_hot: boolean;
    config: Record<string, unknown>;
  }>(
    `
    select name, code, short_name, full_name, icon, pinyin, sort_order, is_hot, config
    from public.regions
    where tenant_id = $1 and id = $2
    limit 1
    `,
    [sourceTenantId, sourceRegionId],
  );
  const row = source.rows[0];
  if (!row) return null;

  const inserted = await client.query<{ id: string }>(
    `
      insert into public.regions (
        tenant_id, name, code, short_name, full_name, icon, pinyin,
      sort_order, is_hot, is_active, config
      )
      values (
        $1, $2, $3, $4, $5, $6, $7,
      $8, $9, true, $10::jsonb
      )
      returning id
    `,
    [
      targetTenantId,
      row.name,
      row.code,
      row.short_name,
      row.full_name,
      row.icon,
      row.pinyin,
      row.sort_order,
      row.is_hot,
      JSON.stringify({
        ...(row.config || {}),
        sourceAdoption: {
          source: 'public_question_bank_adoption',
          sourceTenantId,
          sourceRegionId,
        },
      }),
    ],
  );

  return inserted.rows[0]?.id || null;
}

async function loadEligibleGrant(
  client: pg.PoolClient,
  tenantId: string,
  grantId: string,
) {
  const result = await client.query<EligibleBankRow>(
    `
${PUBLIC_BANK_ACCESS_CTES}
    select g.id as "grantId",
           qb.id as "sourceQuestionBankId",
           qb.name as "sourceQuestionBankName",
           qb.tenant_id as "sourceTenantId",
           qb.region_id as "sourceRegionId",
           r.name as "sourceRegionName",
           g.grant_scope as "grantScope",
           g.allowed_plan_codes as "allowedPlanCodes",
           g.allowed_region_ids as "allowedRegionIds",
           g.allowed_subject_ids as "allowedSubjectIds",
           g.access_plan_code as "accessPlanCode",
           g.access_mode as "accessMode",
           coalesce(qs.question_count, 0)::integer as "questionCount",
           a.id as "adoptedId",
           a.status as "adoptionStatus",
           a.sync_status as "syncStatus",
           a.target_question_bank_id as "targetQuestionBankId",
           a.target_entry_id as "targetEntryId",
           a.target_collection_id as "targetCollectionId",
           a.copied_question_count as "copiedQuestionCount",
           a.last_synced_at as "lastSyncedAt"
    from eligible_grants g
    join public.question_banks qb on qb.id = g.source_question_bank_id
    left join public.regions r on r.id = qb.region_id and r.tenant_id = qb.tenant_id
    left join lateral (
      select count(*)::integer as question_count
      from public.questions q
      where q.tenant_id = qb.tenant_id
        and q.question_bank_id = qb.id
        and q.status = 'published'
    ) qs on true
    left join public.tenant_question_bank_adoptions a
      on a.tenant_id = $1
     and a.source_question_bank_id = qb.id
    where g.id = $2
    limit 1
    `,
    [tenantId, grantId],
  );
  return result.rows[0] || null;
}

function snapshotMap(value: unknown): Record<string, string> {
  if (!value || typeof value !== 'object' || Array.isArray(value)) return {};
  const questions = (value as { questions?: unknown }).questions;
  if (!questions || typeof questions !== 'object' || Array.isArray(questions)) return {};
  const result: Record<string, string> = {};
  for (const [key, raw] of Object.entries(questions as Record<string, unknown>)) {
    if (typeof raw === 'string' && raw) result[key] = raw;
    else if (raw && typeof raw === 'object' && typeof (raw as { sourceHash?: unknown }).sourceHash === 'string') {
      result[key] = (raw as { sourceHash: string }).sourceHash;
    }
  }
  return result;
}

function sourceSnapshotObject(value: unknown) {
  const snapshot = objectValue(value);
  return {
    ...snapshot,
    questions: objectValue(snapshot.questions),
  };
}

function setSourceSnapshotQuestion(snapshot: Record<string, unknown> & { questions: Record<string, unknown> }, sourceQuestionId: string, sourceHash: string, extra: Record<string, unknown> = {}) {
  snapshot.questions[sourceQuestionId] = {
    ...objectValue(snapshot.questions[sourceQuestionId]),
    ...extra,
    sourceHash,
    syncedAt: new Date().toISOString(),
  };
}

function lastSyncConflicts(metadata: Record<string, unknown>) {
  const lastSync = objectValue(metadata.lastSync);
  return Array.isArray(lastSync.conflicts) ? lastSync.conflicts.map(item => objectValue(item)) : [];
}

function resolvedPublicBankConflicts(metadata: Record<string, unknown>) {
  return objectValue(metadata.resolvedPublicBankConflicts);
}

async function sourceQuestionSnapshots(client: pg.PoolClient, input: {
  sourceTenantId: string;
  sourceQuestionBankId: string;
  copyLimit: number;
}) {
  const sourceQuestions = await client.query<{
    id: string;
    type: string;
    type_label: string | null;
    difficulty: number | null;
    tags: unknown[];
    media_url: string | null;
    has_video_explanation: boolean;
    current_version_id: string | null;
    content: string | null;
    options: unknown[];
    correct_option_index: number | null;
    correct_option_indices: unknown[];
    answer_text: string | null;
    explanation: string | null;
    sub_questions: unknown[];
    code_lang: string | null;
    code_template: string | null;
    source_hash: string | null;
  }>(
    `
    select q.id, q.subject_id, q.category_id, q.type, q.type_label,
           q.difficulty, q.tags, q.media_url, q.has_video_explanation,
           q.current_version_id,
           v.content, v.options, v.correct_option_index, v.correct_option_indices,
           v.answer_text, v.explanation, v.sub_questions, v.code_lang,
           v.code_template, v.source_hash
    from public.questions q
    left join public.question_versions v on v.id = q.current_version_id
    where q.tenant_id = $1
      and q.question_bank_id = $2
      and q.status = 'published'
    order by q.created_at asc
    limit $3
    `,
    [input.sourceTenantId, input.sourceQuestionBankId, input.copyLimit],
  );

  return sourceQuestions.rows.map((source): SourceQuestionSnapshot => ({
    id: source.id,
    type: source.type,
    typeLabel: source.type_label,
    difficulty: source.difficulty,
    tags: source.tags || [],
    mediaUrl: source.media_url,
    hasVideoExplanation: source.has_video_explanation,
    content: source.content,
    options: source.options || [],
    correctOptionIndex: source.correct_option_index,
    correctOptionIndices: source.correct_option_indices || [],
    answerText: source.answer_text,
    explanation: source.explanation,
    subQuestions: source.sub_questions || [],
    codeLang: source.code_lang,
    codeTemplate: source.code_template,
    sourceHash: source.source_hash || `public:${source.id}`,
  }));
}

async function syncQuestionSnapshot(client: pg.PoolClient, input: {
  auth: PublicQuestionBankSyncAuth;
  sourceTenantId: string;
  sourceQuestionBankId: string;
  targetQuestionBankId: string;
  targetEntryId: string;
  targetCollectionId: string;
  source: SourceQuestionSnapshot;
  previousSourceHash: string | null;
  conflictResolutions?: Record<string, unknown>;
  order: number;
}) {
  const legacyId = `public:${input.sourceTenantId}:${input.source.id}`;
  const existing = await client.query<{
    id: string;
    source_hash: string | null;
  }>(
    `
    select q.id, v.source_hash
    from public.questions q
    left join public.question_versions v on v.id = q.current_version_id
    where q.tenant_id = $1 and q.legacy_id = $2
    limit 1
    for update of q
    `,
    [input.auth.tenantId, legacyId],
  );
  const existingQuestion = existing.rows[0] || null;
  const targetHash = existingQuestion?.source_hash || null;

  if (existingQuestion) {
    const targetMatchesCurrent = !!targetHash && targetHash === input.source.sourceHash;
    const targetMatchesPrevious = !!targetHash && !!input.previousSourceHash && targetHash === input.previousSourceHash;
    if (!targetMatchesCurrent && !targetMatchesPrevious) {
      const resolved = objectValue(input.conflictResolutions?.[input.source.id]);
      if (
        resolved.decision === 'keep_local'
        && resolved.sourceHash === input.source.sourceHash
        && resolved.targetHash === targetHash
      ) {
        return {
          sourceQuestionId: input.source.id,
          targetQuestionId: existingQuestion.id,
          action: 'skipped',
          sourceHash: input.source.sourceHash,
          previousSourceHash: input.previousSourceHash,
          targetHash,
          resolution: 'keep_local',
        } satisfies SyncQuestionResult;
      }
      return {
        sourceQuestionId: input.source.id,
        targetQuestionId: existingQuestion.id,
        action: 'conflict',
        sourceHash: input.source.sourceHash,
        previousSourceHash: input.previousSourceHash,
        targetHash,
      } satisfies SyncQuestionResult;
    }
  }

  const questionResult = await client.query<{ id: string }>(
    `
    insert into public.questions (
      tenant_id, question_bank_id, entry_id, primary_collection_id,
      legacy_id, type, type_label, difficulty, tags, media_url,
      has_video_explanation, status, exam_markers
    )
    values (
      $1, $2, $3, $4,
      $5, $6, $7, $8, $9::jsonb, $10,
      $11, 'published', $12::jsonb
    )
    on conflict (tenant_id, legacy_id)
    do update set question_bank_id = excluded.question_bank_id,
                  entry_id = excluded.entry_id,
                  primary_collection_id = excluded.primary_collection_id,
                  type = excluded.type,
                  type_label = excluded.type_label,
                  difficulty = excluded.difficulty,
                  tags = excluded.tags,
                  media_url = excluded.media_url,
                  has_video_explanation = excluded.has_video_explanation,
                  status = 'published',
                  exam_markers = excluded.exam_markers,
                  updated_at = now()
    returning id
    `,
    [
      input.auth.tenantId,
      input.targetQuestionBankId,
      input.targetEntryId,
      input.targetCollectionId,
      legacyId,
      input.source.type,
      input.source.typeLabel,
      input.source.difficulty,
      JSON.stringify(input.source.tags || []),
      input.source.mediaUrl,
      input.source.hasVideoExplanation,
      JSON.stringify({
        sourceTenantId: input.sourceTenantId,
        sourceQuestionBankId: input.sourceQuestionBankId,
        sourceQuestionId: input.source.id,
      }),
    ],
  );
  const questionId = questionResult.rows[0].id;

  const action: SyncQuestionResult['action'] = existingQuestion
    ? targetHash === input.source.sourceHash
      ? 'skipped'
      : 'updated'
    : 'inserted';

  if (action !== 'skipped') {
    const latest = await client.query<{ version_no: number }>(
      'select coalesce(max(version_no), 0) as version_no from public.question_versions where question_id = $1',
      [questionId],
    );
    const nextVersionNo = Number(latest.rows[0]?.version_no || 0) + 1;
    const versionNo = existingQuestion ? nextVersionNo : 1;

    const versionResult = await client.query<{ id: string }>(
      `
      insert into public.question_versions (
        tenant_id, question_id, version_no, content, options,
        correct_option_index, correct_option_indices, answer_text,
        explanation, sub_questions, code_lang, code_template,
        source_hash, created_by
      )
      values (
        $1, $2, $3, $4, $5::jsonb,
        $6, $7::jsonb, $8,
        $9, $10::jsonb, $11, $12,
        $13, $14
      )
      on conflict (question_id, version_no)
      do update set content = excluded.content,
                    options = excluded.options,
                    correct_option_index = excluded.correct_option_index,
                    correct_option_indices = excluded.correct_option_indices,
                    answer_text = excluded.answer_text,
                    explanation = excluded.explanation,
                    sub_questions = excluded.sub_questions,
                    code_lang = excluded.code_lang,
                    code_template = excluded.code_template,
                    source_hash = excluded.source_hash
      returning id
      `,
      [
        input.auth.tenantId,
        questionId,
        versionNo,
        input.source.content,
        JSON.stringify(input.source.options || []),
        input.source.correctOptionIndex,
        JSON.stringify(input.source.correctOptionIndices || []),
        input.source.answerText,
        input.source.explanation,
        JSON.stringify(input.source.subQuestions || []),
        input.source.codeLang,
        input.source.codeTemplate,
        input.source.sourceHash,
        input.auth.userId,
      ],
    );

    await client.query(
      'update public.questions set current_version_id = $3, updated_at = now() where tenant_id = $1 and id = $2',
      [input.auth.tenantId, questionId, versionResult.rows[0].id],
    );
  }

  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, null, true, $6::jsonb)
    on conflict (tenant_id, collection_id, question_id)
    do update set section_key = excluded.section_key,
                  sort_order = excluded.sort_order,
                  metadata = excluded.metadata,
                  updated_at = now()
    `,
    [
      input.auth.tenantId,
      input.targetCollectionId,
      questionId,
      input.source.type,
      input.order,
      JSON.stringify({ source: 'public_question_bank_adoption', sourceQuestionId: input.source.id }),
    ],
  );

  return {
    sourceQuestionId: input.source.id,
    targetQuestionId: questionId,
    action,
    sourceHash: input.source.sourceHash,
    previousSourceHash: input.previousSourceHash,
    targetHash,
  } satisfies SyncQuestionResult;
}

function syncCounts(results: SyncQuestionResult[]) {
  return {
    inserted: results.filter(item => item.action === 'inserted').length,
    updated: results.filter(item => item.action === 'updated').length,
    skipped: results.filter(item => item.action === 'skipped').length,
    conflicts: results.filter(item => item.action === 'conflict').length,
  };
}

function publicBankNotificationHash(parts: unknown[]) {
  return crypto.createHash('sha256').update(JSON.stringify(parts)).digest('hex').slice(0, 40);
}

function publicBankChangedResults(results: SyncQuestionResult[]) {
  return results
    .filter(item => item.action === 'inserted' || item.action === 'updated' || item.action === 'conflict')
    .map(item => ({
      sourceQuestionId: item.sourceQuestionId,
      action: item.action,
      sourceHash: item.sourceHash,
      previousSourceHash: item.previousSourceHash,
      targetHash: item.targetHash,
    }))
    .sort((left, right) => `${left.sourceQuestionId}:${left.action}`.localeCompare(`${right.sourceQuestionId}:${right.action}`));
}

async function upsertPublicBankSyncNotification(client: pg.PoolClient, input: {
  auth: PublicQuestionBankSyncAuth;
  adoption: AdoptionRow;
  grant: EligibleBankRow;
  results: SyncQuestionResult[];
  counts: ReturnType<typeof syncCounts>;
  conflicts: SyncQuestionResult[];
  triggeredBy: 'manual' | 'worker';
  workerId?: string | null;
}) {
  const changedResults = publicBankChangedResults(input.results);
  if (!changedResults.length) return;

  const hasConflicts = input.conflicts.length > 0;
  const notificationType = hasConflicts ? 'public_question_bank_conflict' : 'public_question_bank_synced';
  const severity = hasConflicts ? 'warning' : 'success';
  const dedupeKey = [
    'public-bank',
    input.adoption.id,
    notificationType,
    publicBankNotificationHash(changedResults),
  ].join(':');
  const title = hasConflicts
    ? `${input.grant.sourceQuestionBankName} 有同步冲突`
    : `${input.grant.sourceQuestionBankName} 已同步`;
  const message = hasConflicts
    ? `平台公共题库有 ${input.conflicts.length} 道题与租户本地改写冲突，请处理后再发布给学生。`
    : `平台公共题库已同步：新增 ${input.counts.inserted} 题，更新 ${input.counts.updated} 题。`;
  const actionPath = hasConflicts
    ? `/tenant-admin/content?adoptionId=${input.adoption.id}&panel=public-bank-conflicts`
    : `/tenant-admin/content?adoptionId=${input.adoption.id}&panel=public-banks`;
  const actionLabel = hasConflicts ? '处理冲突' : '查看同步结果';

  await client.query(
    `
    insert into public.tenant_content_notifications (
      tenant_id, notification_type, status, severity,
      adoption_id, source_question_bank_id,
      title, message, action_label, action_path,
      dedupe_key, metadata, created_by
    )
    values (
      $1, $2, 'unread', $3,
      $4, $5,
      $6, $7, $8, $9,
      $10, $11::jsonb, $12
    )
    on conflict (tenant_id, notification_type, dedupe_key)
    where dedupe_key is not null
    do update set severity = excluded.severity,
                  title = excluded.title,
                  message = excluded.message,
                  action_label = excluded.action_label,
                  action_path = excluded.action_path,
                  metadata = excluded.metadata,
                  updated_at = now()
    `,
    [
      input.auth.tenantId,
      notificationType,
      severity,
      input.adoption.id,
      input.grant.sourceQuestionBankId,
      title,
      message,
      actionLabel,
      actionPath,
      dedupeKey,
      JSON.stringify({
        adoptionId: input.adoption.id,
        sourceQuestionBankId: input.grant.sourceQuestionBankId,
        sourceQuestionBankName: input.grant.sourceQuestionBankName,
        targetQuestionBankId: input.adoption.targetQuestionBankId,
        targetEntryId: input.adoption.targetEntryId,
        targetCollectionId: input.adoption.targetCollectionId,
        syncStatus: hasConflicts ? 'conflict' : 'synced',
        counts: input.counts,
        changedResults: changedResults.slice(0, 50),
        triggeredBy: input.triggeredBy,
        workerId: input.workerId || null,
      }),
      input.auth.userId,
    ],
  );
}

async function markPublicBankConflictNotificationsResolved(client: pg.PoolClient, input: {
  tenantId: string;
  adoptionId: string;
  actorUserId: string | null;
}) {
  await client.query(
    `
    update public.tenant_content_notifications
    set status = 'resolved',
        read_by = coalesce(read_by, $3),
        read_at = coalesce(read_at, now()),
        resolved_at = coalesce(resolved_at, now()),
        updated_at = now()
    where tenant_id = $1
      and adoption_id = $2
      and notification_type = 'public_question_bank_conflict'
      and status <> 'dismissed'
    `,
    [input.tenantId, input.adoptionId, input.actorUserId],
  );
}

async function markPublicBankFailureNotificationsResolved(client: pg.PoolClient, input: {
  tenantId: string;
  adoptionId: string;
  actorUserId: string | null;
}) {
  await client.query(
    `
    update public.tenant_content_notifications
    set status = 'resolved',
        read_by = coalesce(read_by, $3),
        read_at = coalesce(read_at, now()),
        resolved_at = coalesce(resolved_at, now()),
        updated_at = now()
    where tenant_id = $1
      and adoption_id = $2
      and notification_type = 'public_question_bank_sync_failed'
      and status <> 'dismissed'
    `,
    [input.tenantId, input.adoptionId, input.actorUserId],
  );
}

function objectValue(value: unknown): Record<string, unknown> {
  return value && typeof value === 'object' && !Array.isArray(value) ? value as Record<string, unknown> : {};
}

function buildSourceSnapshot(input: {
  sourceTenantId: string;
  sourceQuestionBankId: string;
  sourceQuestionBankName: string;
  sourceRegionId: string | null;
  sourceRegionName: string | null;
  sourceQuestionCount: number;
  sourceQuestions: SourceQuestionSnapshot[];
  syncSummary?: Record<string, unknown>;
}) {
  return {
    sourceTenantId: input.sourceTenantId,
    sourceQuestionBankId: input.sourceQuestionBankId,
    sourceQuestionBankName: input.sourceQuestionBankName,
    sourceRegionId: input.sourceRegionId,
    sourceRegionName: input.sourceRegionName,
    sourceQuestionCount: input.sourceQuestionCount,
    questions: Object.fromEntries(
      input.sourceQuestions.map(question => [
        question.id,
        {
          sourceHash: question.sourceHash,
          syncedAt: new Date().toISOString(),
        },
      ]),
    ),
    syncSummary: input.syncSummary || undefined,
  };
}

async function syncQuestionsSnapshot(client: pg.PoolClient, input: {
  auth: PublicQuestionBankSyncAuth;
  sourceTenantId: string;
  sourceQuestionBankId: string;
  targetQuestionBankId: string;
  targetEntryId: string;
  targetCollectionId: string;
  copyLimit: number;
  previousSnapshot?: unknown;
  conflictResolutions?: Record<string, unknown>;
}) {
  const sources = await sourceQuestionSnapshots(client, input);
  const previous = snapshotMap(input.previousSnapshot);
  const results: SyncQuestionResult[] = [];
  let order = 0;
  for (const source of sources) {
    const result = await syncQuestionSnapshot(client, {
      auth: input.auth,
      sourceTenantId: input.sourceTenantId,
      sourceQuestionBankId: input.sourceQuestionBankId,
      targetQuestionBankId: input.targetQuestionBankId,
      targetEntryId: input.targetEntryId,
      targetCollectionId: input.targetCollectionId,
      source,
      previousSourceHash: previous[source.id] || null,
      conflictResolutions: input.conflictResolutions,
      order,
    });
    results.push(result);
    order += 1;
  }

  await client.query(
    `
    update public.question_collections
    set question_count = (
          select count(*)
          from public.question_collection_items
          where tenant_id = $1 and collection_id = $2
        ),
        updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [input.auth.tenantId, input.targetCollectionId],
  );

  return { sourceQuestions: sources, results, counts: syncCounts(results) };
}

export async function publicQuestionBanksRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const q = stringParam(ctx, 'q');
  const regionId = stringParam(ctx, 'regionId');
  const onlyNotAdopted = stringParam(ctx, 'onlyNotAdopted') === 'true';
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query<EligibleBankRow>(
    `
${PUBLIC_BANK_ACCESS_CTES}
    select g.id as "grantId",
           qb.id as "sourceQuestionBankId",
           qb.name as "sourceQuestionBankName",
           qb.tenant_id as "sourceTenantId",
           qb.region_id as "sourceRegionId",
           r.name as "sourceRegionName",
           g.grant_scope as "grantScope",
           g.allowed_plan_codes as "allowedPlanCodes",
           g.allowed_region_ids as "allowedRegionIds",
           g.allowed_subject_ids as "allowedSubjectIds",
           g.access_plan_code as "accessPlanCode",
           g.access_mode as "accessMode",
           coalesce(qs.question_count, 0)::integer as "questionCount",
           a.id as "adoptedId",
           a.status as "adoptionStatus",
           a.sync_status as "syncStatus",
           a.target_question_bank_id as "targetQuestionBankId",
           a.target_entry_id as "targetEntryId",
           a.target_collection_id as "targetCollectionId",
           a.copied_question_count as "copiedQuestionCount",
           a.last_synced_at as "lastSyncedAt"
    from eligible_grants g
    join public.question_banks qb on qb.id = g.source_question_bank_id
    left join public.regions r on r.id = qb.region_id and r.tenant_id = qb.tenant_id
    left join public.tenant_question_bank_adoptions a
      on a.tenant_id = $1
     and a.source_question_bank_id = qb.id
    left join lateral (
      select count(*)::integer as question_count
      from public.questions q
      where q.tenant_id = qb.tenant_id
        and q.question_bank_id = qb.id
        and q.status = 'published'
    ) qs on true
    where qb.source_scope = 'platform'
      and qb.status = 'active'
      and ($2::uuid is null or qb.region_id = $2::uuid)
      and ($3::text = '' or qb.name ilike '%' || $3 || '%' or coalesce(r.name, '') ilike '%' || $3 || '%')
      and ($4::boolean = false or a.id is null)
    order by r.sort_order asc nulls last, qb.created_at desc
    limit $5
    `,
    [auth.tenantId, regionId || null, q, onlyNotAdopted, limit],
  );

  return { items };
}

export async function adoptPublicQuestionBankRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const grantId = requiredString(body, 'grantId');
  const copyLimit = Math.max(1, Math.min(intValue(body.copyLimit, 200), 1000));
  const entryNameInput = nullableString(body.entryName);
  const collectionNameInput = nullableString(body.collectionName);
  const isActive = boolValue(body.isActive, true);

  const item = await transaction(async client => {
    const grant = await loadEligibleGrant(client, auth.tenantId, grantId);
    if (!grant) {
      throw new HttpError(403, 'Question bank grant is not available for this tenant', 'QUESTION_BANK_GRANT_NOT_AVAILABLE');
    }
    if (grant.adoptedId && grant.adoptionStatus !== 'archived') {
      throw new HttpError(409, 'Question bank has already been adopted by this tenant', 'QUESTION_BANK_ALREADY_ADOPTED');
    }

    const targetRegionId = await ensureTenantRegion(client, auth.tenantId, grant.sourceTenantId, grant.sourceRegionId);
    const baseKey = `public-${slugFromName(grant.sourceQuestionBankName)}-${grant.sourceQuestionBankId.slice(0, 8)}`;
    const entryName = entryNameInput || `${grant.sourceQuestionBankName}`;
    const collectionName = collectionNameInput || `${grant.sourceQuestionBankName}题目`;

    const targetBankResult = await client.query<{ id: string }>(
      `
      insert into public.question_banks (tenant_id, region_id, name, source_scope, status, metadata)
      values ($1, $2::uuid, $3, 'tenant', 'active', $4::jsonb)
      returning id
      `,
      [
        auth.tenantId,
        targetRegionId,
        grant.sourceQuestionBankName,
        JSON.stringify({
          source: 'public_question_bank_adoption',
          sourceTenantId: grant.sourceTenantId,
          sourceQuestionBankId: grant.sourceQuestionBankId,
          grantId,
        }),
      ],
    );
    const targetQuestionBankId = targetBankResult.rows[0].id;

    const entryResult = await client.query<{ id: string }>(
      `
      insert into public.content_entries (
        tenant_id, region_id, entry_key, name, entry_type,
        icon, route, description, visibility, access_rules,
        layout_config, sort_order, is_active, created_by, updated_by
      )
      values (
        $1, $2::uuid, $3, $4, 'question_practice',
        $5, '/practice', $6, 'public', '{}'::jsonb,
        $7::jsonb, 100, $8, $9, $9
      )
      returning id
      `,
      [
        auth.tenantId,
        targetRegionId,
        baseKey,
        entryName,
        optionalString(body, 'icon') || 'book-open',
        `采纳自平台公共题库：${grant.sourceQuestionBankName}`,
        jsonObjectValue(body.layoutConfig || { source: 'public_question_bank_adoption', tabs: ['all', 'paper', 'chapter', 'type'] }),
        isActive,
        auth.userId,
      ],
    );
    const targetEntryId = entryResult.rows[0].id;

    const collectionResult = await client.query<{ id: string }>(
      `
      insert into public.question_collections (
        tenant_id, region_id, entry_id, question_bank_id, name,
        collection_type, source_type, filters, question_count,
        status, sort_order, access_rules, metadata, created_by, updated_by
      )
      values (
        $1, $2::uuid, $3, $4, $5,
        'manual', 'manual_questions', '{}'::jsonb, 0,
        'active', 1, $6::jsonb, $7::jsonb, $8, $8
      )
      returning id
      `,
      [
        auth.tenantId,
        targetRegionId,
        targetEntryId,
        targetQuestionBankId,
        collectionName,
        jsonObjectValue(body.accessRules),
        JSON.stringify({
          source: 'public_question_bank_adoption',
          sourceTenantId: grant.sourceTenantId,
          sourceQuestionBankId: grant.sourceQuestionBankId,
          grantId,
        }),
        auth.userId,
      ],
    );
    const targetCollectionId = collectionResult.rows[0].id;

    const syncResult = await syncQuestionsSnapshot(client, {
      auth,
      sourceTenantId: grant.sourceTenantId,
      sourceQuestionBankId: grant.sourceQuestionBankId,
      targetQuestionBankId,
      targetEntryId,
      targetCollectionId,
      copyLimit,
    });
    const copiedQuestionCount = syncResult.sourceQuestions.length;
    const sourceSnapshot = buildSourceSnapshot({
      sourceTenantId: grant.sourceTenantId,
      sourceQuestionBankId: grant.sourceQuestionBankId,
      sourceQuestionBankName: grant.sourceQuestionBankName,
      sourceRegionId: grant.sourceRegionId,
      sourceRegionName: grant.sourceRegionName,
      sourceQuestionCount: grant.questionCount,
      sourceQuestions: syncResult.sourceQuestions,
      syncSummary: syncResult.counts,
    });

    const adoptionResult = await client.query<AdoptionRow>(
      `
      insert into public.tenant_question_bank_adoptions (
        tenant_id, source_question_bank_id, grant_id, target_question_bank_id,
        target_entry_id, target_collection_id, adoption_mode, status,
        sync_status, source_snapshot, copied_question_count, metadata,
        created_by, updated_by, last_synced_at
      )
      values (
        $1, $2, $3, $4,
        $5, $6, 'copied_snapshot', 'active',
        'synced', $7::jsonb, $8, $9::jsonb,
        $10, $10, now()
      )
      on conflict (tenant_id, source_question_bank_id)
      do update set grant_id = excluded.grant_id,
                    target_question_bank_id = excluded.target_question_bank_id,
                    target_entry_id = excluded.target_entry_id,
                    target_collection_id = excluded.target_collection_id,
                    status = 'active',
                    sync_status = excluded.sync_status,
                    source_snapshot = excluded.source_snapshot,
                    copied_question_count = excluded.copied_question_count,
                    metadata = excluded.metadata,
                    updated_by = excluded.updated_by,
                    last_synced_at = excluded.last_synced_at,
                    updated_at = now()
      returning id, tenant_id as "tenantId",
                source_question_bank_id as "sourceQuestionBankId",
                grant_id as "grantId",
                target_question_bank_id as "targetQuestionBankId",
                target_entry_id as "targetEntryId",
                target_collection_id as "targetCollectionId",
                adoption_mode as "adoptionMode", status, sync_status as "syncStatus",
                source_snapshot as "sourceSnapshot",
                copied_question_count as "copiedQuestionCount",
                metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        grant.sourceQuestionBankId,
        grantId,
        targetQuestionBankId,
        targetEntryId,
        targetCollectionId,
        JSON.stringify(sourceSnapshot),
        copiedQuestionCount,
        jsonObjectValue(body.metadata),
        auth.userId,
      ],
    );

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'content.public_question_bank.adopted', 'tenant_question_bank_adoption', $3, $4::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        adoptionResult.rows[0].id,
        JSON.stringify({
          grantId,
          sourceQuestionBankId: grant.sourceQuestionBankId,
          targetQuestionBankId,
          targetEntryId,
          targetCollectionId,
          copiedQuestionCount,
          syncSummary: syncResult.counts,
        }),
      ],
    );

    return adoptionResult.rows[0];
  });

  return { item };
}

export async function executePublicQuestionBankSync(input: PublicQuestionBankSyncInput) {
  const copyLimit = Math.max(1, Math.min(intValue(input.copyLimit, 1000), 1000));
  const auth: PublicQuestionBankSyncAuth = {
    tenantId: input.tenantId,
    userId: input.actorUserId || null,
    role: input.triggeredBy === 'worker' ? 'system_worker' : 'tenant_content_editor',
    permissions: { 'content:*': true },
    templatePermissions: {},
  };

  return transaction(async client => {
    const adoptionResult = await client.query<AdoptionRow>(
      `
      select id, tenant_id as "tenantId",
             source_question_bank_id as "sourceQuestionBankId",
             grant_id as "grantId",
             target_question_bank_id as "targetQuestionBankId",
             target_entry_id as "targetEntryId",
             target_collection_id as "targetCollectionId",
             adoption_mode as "adoptionMode", status,
             sync_status as "syncStatus",
             source_snapshot as "sourceSnapshot",
             copied_question_count as "copiedQuestionCount",
             metadata, created_at as "createdAt", updated_at as "updatedAt"
      from public.tenant_question_bank_adoptions
      where tenant_id = $1
        and id = $2
        and status in ('active', 'sync_pending')
      limit 1
      for update
      `,
      [auth.tenantId, input.adoptionId],
    );
    const adoption = adoptionResult.rows[0];
    if (!adoption) {
      throw new HttpError(404, 'Question bank adoption not found', 'QUESTION_BANK_ADOPTION_NOT_FOUND');
    }
    if (!adoption.grantId) {
      throw new HttpError(409, 'Question bank adoption has no active grant', 'QUESTION_BANK_ADOPTION_GRANT_MISSING');
    }
    if (!adoption.targetQuestionBankId || !adoption.targetEntryId || !adoption.targetCollectionId) {
      throw new HttpError(409, 'Question bank adoption target is incomplete', 'QUESTION_BANK_ADOPTION_TARGET_MISSING');
    }

    const grant = await loadEligibleGrant(client, auth.tenantId, adoption.grantId);
    if (!grant || grant.sourceQuestionBankId !== adoption.sourceQuestionBankId) {
      throw new HttpError(403, 'Question bank grant is not available for this tenant', 'QUESTION_BANK_GRANT_NOT_AVAILABLE');
    }

    const syncResult = await syncQuestionsSnapshot(client, {
      auth,
      sourceTenantId: grant.sourceTenantId,
      sourceQuestionBankId: grant.sourceQuestionBankId,
      targetQuestionBankId: adoption.targetQuestionBankId,
      targetEntryId: adoption.targetEntryId,
      targetCollectionId: adoption.targetCollectionId,
      copyLimit,
      previousSnapshot: adoption.sourceSnapshot,
      conflictResolutions: resolvedPublicBankConflicts(adoption.metadata || {}),
    });
    const conflicts = syncResult.results.filter(item => item.action === 'conflict');
    const syncStatus = conflicts.length ? 'failed' : 'synced';
    const sourceSnapshot = buildSourceSnapshot({
      sourceTenantId: grant.sourceTenantId,
      sourceQuestionBankId: grant.sourceQuestionBankId,
      sourceQuestionBankName: grant.sourceQuestionBankName,
      sourceRegionId: grant.sourceRegionId,
      sourceRegionName: grant.sourceRegionName,
      sourceQuestionCount: grant.questionCount,
      sourceQuestions: syncResult.sourceQuestions,
      syncSummary: syncResult.counts,
    });
    const metadata = {
      ...(adoption.metadata || {}),
      lastSync: {
        status: syncStatus,
        counts: syncResult.counts,
        conflictCount: conflicts.length,
        conflicts: conflicts.slice(0, 50),
        triggeredBy: input.triggeredBy || 'manual',
        workerId: input.workerId || null,
        finishedAt: new Date().toISOString(),
      },
      publicBankSyncWorker: {
        ...(
          adoption.metadata?.publicBankSyncWorker
          && typeof adoption.metadata.publicBankSyncWorker === 'object'
          && !Array.isArray(adoption.metadata.publicBankSyncWorker)
            ? adoption.metadata.publicBankSyncWorker as Record<string, unknown>
            : {}
        ),
        lastStatus: syncStatus,
        lastWorkerId: input.workerId || null,
        lastFinishedAt: new Date().toISOString(),
      },
    };

    const updated = await client.query<AdoptionRow>(
      `
      update public.tenant_question_bank_adoptions
      set sync_status = $3,
          status = 'active',
          source_snapshot = $4::jsonb,
          copied_question_count = $5,
          metadata = $6::jsonb,
          updated_by = $7,
          last_synced_at = now(),
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, tenant_id as "tenantId",
                source_question_bank_id as "sourceQuestionBankId",
                grant_id as "grantId",
                target_question_bank_id as "targetQuestionBankId",
                target_entry_id as "targetEntryId",
                target_collection_id as "targetCollectionId",
                adoption_mode as "adoptionMode", status,
                sync_status as "syncStatus",
                source_snapshot as "sourceSnapshot",
                copied_question_count as "copiedQuestionCount",
                metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        adoption.id,
        syncStatus,
        JSON.stringify(sourceSnapshot),
        syncResult.sourceQuestions.length,
        JSON.stringify(metadata),
        auth.userId,
      ],
    );

    if (!conflicts.length) {
      await markPublicBankConflictNotificationsResolved(client, {
        tenantId: auth.tenantId,
        adoptionId: adoption.id,
        actorUserId: auth.userId,
      });
    }
    await markPublicBankFailureNotificationsResolved(client, {
      tenantId: auth.tenantId,
      adoptionId: adoption.id,
      actorUserId: auth.userId,
    });

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'content.public_question_bank.synced', 'tenant_question_bank_adoption', $3, $4::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        adoption.id,
        JSON.stringify({
          grantId: adoption.grantId,
          sourceQuestionBankId: grant.sourceQuestionBankId,
          targetQuestionBankId: adoption.targetQuestionBankId,
          targetEntryId: adoption.targetEntryId,
          targetCollectionId: adoption.targetCollectionId,
          syncStatus,
          syncSummary: syncResult.counts,
          conflicts,
          triggeredBy: input.triggeredBy || 'manual',
          workerId: input.workerId || null,
        }),
      ],
    );

    await upsertPublicBankSyncNotification(client, {
      auth,
      adoption: updated.rows[0],
      grant,
      results: syncResult.results,
      counts: syncResult.counts,
      conflicts,
      triggeredBy: input.triggeredBy || 'manual',
      workerId: input.workerId || null,
    });

    return {
      item: updated.rows[0],
      sync: {
        status: conflicts.length ? 'conflict' : 'synced',
        counts: syncResult.counts,
        results: syncResult.results,
      },
    };
  });
}

export async function syncPublicQuestionBankRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  return executePublicQuestionBankSync({
    tenantId: auth.tenantId,
    actorUserId: auth.userId,
    adoptionId: requiredString(body, 'adoptionId'),
    copyLimit: intValue(body.copyLimit, 1000),
    triggeredBy: 'manual',
  });
}

export async function publicQuestionBankConflictsRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const adoptionId = stringParam(ctx, 'adoptionId');
  if (!adoptionId) {
    throw new HttpError(400, 'adoptionId is required', 'REQUIRED_FIELD');
  }

  const rows = await query<{
    id: string;
    syncStatus: string;
    metadata: Record<string, unknown>;
    lastSyncedAt: string | null;
    updatedAt: string;
  }>(
    `
    select id, sync_status as "syncStatus", metadata,
           last_synced_at as "lastSyncedAt", updated_at as "updatedAt"
    from public.tenant_question_bank_adoptions
    where tenant_id = $1 and id = $2 and status <> 'archived'
    limit 1
    `,
    [auth.tenantId, adoptionId],
  );
  const adoption = rows[0];
  if (!adoption) {
    throw new HttpError(404, 'Question bank adoption not found', 'QUESTION_BANK_ADOPTION_NOT_FOUND');
  }

  const lastSync = objectValue(adoption.metadata?.lastSync);
  const conflicts = Array.isArray(lastSync.conflicts) ? lastSync.conflicts : [];
  return {
    item: {
      adoptionId: adoption.id,
      syncStatus: adoption.syncStatus,
      lastSyncedAt: adoption.lastSyncedAt,
      updatedAt: adoption.updatedAt,
      conflictCount: Number(lastSync.conflictCount || conflicts.length || 0),
      counts: objectValue(lastSync.counts),
      conflicts,
    },
  };
}

export async function resolvePublicQuestionBankConflictRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const adoptionId = requiredString(body, 'adoptionId');
  const sourceQuestionId = requiredString(body, 'sourceQuestionId');
  const resolution = requiredString(body, 'resolution');
  if (!['accept_platform', 'keep_local'].includes(resolution)) {
    throw new HttpError(400, 'resolution must be accept_platform or keep_local', 'PUBLIC_BANK_CONFLICT_RESOLUTION_INVALID');
  }

  const item = await transaction(async client => {
    const adoptionResult = await client.query<AdoptionRow>(
      `
      select id, tenant_id as "tenantId",
             source_question_bank_id as "sourceQuestionBankId",
             grant_id as "grantId",
             target_question_bank_id as "targetQuestionBankId",
             target_entry_id as "targetEntryId",
             target_collection_id as "targetCollectionId",
             adoption_mode as "adoptionMode", status,
             sync_status as "syncStatus",
             source_snapshot as "sourceSnapshot",
             copied_question_count as "copiedQuestionCount",
             metadata, created_at as "createdAt", updated_at as "updatedAt"
      from public.tenant_question_bank_adoptions
      where tenant_id = $1 and id = $2 and status <> 'archived'
      limit 1
      for update
      `,
      [auth.tenantId, adoptionId],
    );
    const adoption = adoptionResult.rows[0];
    if (!adoption) throw new HttpError(404, 'Question bank adoption not found', 'QUESTION_BANK_ADOPTION_NOT_FOUND');
    if (!adoption.targetQuestionBankId || !adoption.targetEntryId || !adoption.targetCollectionId) {
      throw new HttpError(409, 'Question bank adoption target is incomplete', 'QUESTION_BANK_ADOPTION_TARGET_MISSING');
    }

    const lastConflicts = lastSyncConflicts(adoption.metadata || {});
    const conflict = lastConflicts.find(item => item.sourceQuestionId === sourceQuestionId);
    if (!conflict) {
      throw new HttpError(404, 'Public question bank conflict not found', 'PUBLIC_BANK_CONFLICT_NOT_FOUND');
    }
    const targetQuestionId = nullableString(conflict.targetQuestionId);
    if (!targetQuestionId) {
      throw new HttpError(409, 'Conflict has no target question', 'PUBLIC_BANK_CONFLICT_TARGET_MISSING');
    }

    const grant = adoption.grantId ? await loadEligibleGrant(client, auth.tenantId, adoption.grantId) : null;
    if (!grant || grant.sourceQuestionBankId !== adoption.sourceQuestionBankId) {
      throw new HttpError(403, 'Question bank grant is not available for this tenant', 'QUESTION_BANK_GRANT_NOT_AVAILABLE');
    }
    const source = (await sourceQuestionSnapshots(client, {
      sourceTenantId: grant.sourceTenantId,
      sourceQuestionBankId: grant.sourceQuestionBankId,
      copyLimit: 1000,
    })).find(question => question.id === sourceQuestionId);
    if (!source) throw new HttpError(404, 'Source question not found', 'PUBLIC_BANK_SOURCE_QUESTION_NOT_FOUND');

    const target = await client.query<{ id: string; source_hash: string | null }>(
      `
      select q.id, v.source_hash
      from public.questions q
      left join public.question_versions v on v.id = q.current_version_id
      where q.tenant_id = $1 and q.id = $2
      limit 1
      for update of q
      `,
      [auth.tenantId, targetQuestionId],
    );
    if (!target.rows[0]) throw new HttpError(404, 'Target question not found', 'PUBLIC_BANK_TARGET_QUESTION_NOT_FOUND');

    const snapshot = sourceSnapshotObject(adoption.sourceSnapshot);
    const metadata: Record<string, unknown> = {
      ...(adoption.metadata || {}),
      resolvedPublicBankConflicts: {
        ...resolvedPublicBankConflicts(adoption.metadata || {}),
      },
    };
    const resolved = objectValue(metadata.resolvedPublicBankConflicts);

    if (resolution === 'accept_platform') {
      const latest = await client.query<{ version_no: number }>(
        'select coalesce(max(version_no), 0) as version_no from public.question_versions where question_id = $1',
        [targetQuestionId],
      );
      const nextVersionNo = Number(latest.rows[0]?.version_no || 0) + 1;
      const version = await client.query<{ id: string }>(
        `
        insert into public.question_versions (
          tenant_id, question_id, version_no, content, options,
          correct_option_index, correct_option_indices, answer_text,
          explanation, sub_questions, code_lang, code_template,
          source_hash, created_by
        )
        values ($1, $2, $3, $4, $5::jsonb, $6, $7::jsonb, $8, $9, $10::jsonb, $11, $12, $13, $14)
        returning id
        `,
        [
          auth.tenantId,
          targetQuestionId,
          nextVersionNo,
          source.content,
          JSON.stringify(source.options || []),
          source.correctOptionIndex,
          JSON.stringify(source.correctOptionIndices || []),
          source.answerText,
          source.explanation,
          JSON.stringify(source.subQuestions || []),
          source.codeLang,
          source.codeTemplate,
          source.sourceHash,
          auth.userId,
        ],
      );
      await client.query(
        `
        update public.questions
        set question_bank_id = $3,
            entry_id = $4,
            primary_collection_id = $5,
            type = $6,
            type_label = $7,
            difficulty = $8,
            tags = $9::jsonb,
            media_url = $10,
            has_video_explanation = $11,
            status = 'published',
            current_version_id = $12,
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [
          auth.tenantId,
          targetQuestionId,
          adoption.targetQuestionBankId,
          adoption.targetEntryId,
          adoption.targetCollectionId,
          source.type,
          source.typeLabel,
          source.difficulty,
          JSON.stringify(source.tags || []),
          source.mediaUrl,
          source.hasVideoExplanation,
          version.rows[0].id,
        ],
      );
      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, null, true, $6::jsonb)
        on conflict (tenant_id, collection_id, question_id)
        do update set section_key = excluded.section_key,
                      metadata = excluded.metadata,
                      updated_at = now()
        `,
        [
          auth.tenantId,
          adoption.targetCollectionId,
          targetQuestionId,
          source.type,
          0,
          JSON.stringify({
            source: 'public_question_bank_adoption',
            sourceQuestionId,
            lastConflictResolution: 'accept_platform',
          }),
        ],
      );
      setSourceSnapshotQuestion(snapshot, sourceQuestionId, source.sourceHash, { resolution: 'accept_platform' });
      delete resolved[sourceQuestionId];
    } else {
      setSourceSnapshotQuestion(snapshot, sourceQuestionId, source.sourceHash, { resolution: 'keep_local' });
      resolved[sourceQuestionId] = {
        decision: 'keep_local',
        sourceHash: source.sourceHash,
        targetHash: target.rows[0].source_hash,
        resolvedAt: new Date().toISOString(),
        resolvedBy: auth.userId,
      };
    }

    const remainingConflicts = lastConflicts.filter(item => item.sourceQuestionId !== sourceQuestionId);
    const lastSync = {
      ...objectValue((adoption.metadata || {}).lastSync),
      status: remainingConflicts.length ? 'failed' : 'resolved',
      conflictCount: remainingConflicts.length,
      conflicts: remainingConflicts,
      lastResolution: {
        sourceQuestionId,
        resolution,
        resolvedAt: new Date().toISOString(),
      },
    };
    metadata.resolvedPublicBankConflicts = resolved;
    metadata.lastSync = lastSync;

    const updated = await client.query<AdoptionRow>(
      `
      update public.tenant_question_bank_adoptions
      set sync_status = $3,
          source_snapshot = $4::jsonb,
          metadata = $5::jsonb,
          updated_by = $6,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, tenant_id as "tenantId",
                source_question_bank_id as "sourceQuestionBankId",
                grant_id as "grantId",
                target_question_bank_id as "targetQuestionBankId",
                target_entry_id as "targetEntryId",
                target_collection_id as "targetCollectionId",
                adoption_mode as "adoptionMode", status,
                sync_status as "syncStatus",
                source_snapshot as "sourceSnapshot",
                copied_question_count as "copiedQuestionCount",
                metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        adoption.id,
        remainingConflicts.length ? 'failed' : 'synced',
        JSON.stringify(snapshot),
        JSON.stringify(metadata),
        auth.userId,
      ],
    );

    if (!remainingConflicts.length) {
      await markPublicBankConflictNotificationsResolved(client, {
        tenantId: auth.tenantId,
        adoptionId: adoption.id,
        actorUserId: auth.userId,
      });
    }

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'content.public_question_bank.conflict_resolved', 'tenant_question_bank_adoption', $3, $4::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        adoption.id,
        JSON.stringify({
          sourceQuestionBankId: adoption.sourceQuestionBankId,
          sourceQuestionId,
          targetQuestionId,
          resolution,
          sourceHash: source.sourceHash,
          previousTargetHash: target.rows[0].source_hash,
          remainingConflictCount: remainingConflicts.length,
        }),
      ],
    );

    return {
      adoption: updated.rows[0],
      sourceQuestionId,
      targetQuestionId,
      resolution,
      remainingConflictCount: remainingConflicts.length,
      syncStatus: updated.rows[0].syncStatus,
    };
  });

  return { item };
}

type PublicQuestionBankConflictResolution = 'accept_platform' | 'keep_local';

function parsePublicQuestionBankConflictResolution(value: string): PublicQuestionBankConflictResolution {
  if (value === 'accept_platform' || value === 'keep_local') return value;
  throw new HttpError(400, 'resolution must be accept_platform or keep_local', 'PUBLIC_BANK_CONFLICT_RESOLUTION_INVALID');
}

function parseNotificationStatus(value: string) {
  if (['unread', 'read', 'dismissed', 'resolved'].includes(value)) return value;
  throw new HttpError(400, 'status must be unread, read, dismissed, or resolved', 'CONTENT_NOTIFICATION_STATUS_INVALID');
}

function parseNotificationUpdateStatus(value: string) {
  if (['read', 'dismissed', 'resolved'].includes(value)) return value;
  throw new HttpError(400, 'status must be read, dismissed, or resolved', 'CONTENT_NOTIFICATION_STATUS_INVALID');
}

function uuidString(value: unknown) {
  const candidate = typeof value === 'string' ? value.trim() : '';
  if (!candidate) return '';
  if (!/^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(candidate)) {
    throw new HttpError(400, 'notificationIds must contain UUID values', 'CONTENT_NOTIFICATION_ID_INVALID');
  }
  return candidate;
}

function sourceQuestionIdsFromBody(value: unknown) {
  if (!Array.isArray(value)) return [];
  const seen = new Set<string>();
  const ids: string[] = [];
  for (const item of value) {
    const id = typeof item === 'string' ? item.trim() : '';
    if (id && !seen.has(id)) {
      seen.add(id);
      ids.push(id);
    }
  }
  return ids;
}

async function resolvePublicQuestionBankConflictsBatch(auth: TenantContentAuth, input: {
  adoptionId: string;
  sourceQuestionIds: string[];
  resolution: PublicQuestionBankConflictResolution;
  resolveAll: boolean;
  limit: number;
}) {
  const limit = Math.max(1, Math.min(intValue(input.limit, 50), 100));
  const sourceQuestionIds = input.sourceQuestionIds.slice(0, 100);
  if (!input.resolveAll && sourceQuestionIds.length === 0) {
    throw new HttpError(400, 'sourceQuestionIds is required unless resolveAll is true', 'PUBLIC_BANK_CONFLICT_SOURCE_IDS_REQUIRED');
  }
  if (!input.resolveAll && input.sourceQuestionIds.length > limit) {
    throw new HttpError(400, `Batch can resolve at most ${limit} conflicts`, 'PUBLIC_BANK_CONFLICT_BATCH_LIMIT_EXCEEDED');
  }

  return transaction(async client => {
    const adoptionResult = await client.query<AdoptionRow>(
      `
      select id, tenant_id as "tenantId",
             source_question_bank_id as "sourceQuestionBankId",
             grant_id as "grantId",
             target_question_bank_id as "targetQuestionBankId",
             target_entry_id as "targetEntryId",
             target_collection_id as "targetCollectionId",
             adoption_mode as "adoptionMode", status,
             sync_status as "syncStatus",
             source_snapshot as "sourceSnapshot",
             copied_question_count as "copiedQuestionCount",
             metadata, created_at as "createdAt", updated_at as "updatedAt"
      from public.tenant_question_bank_adoptions
      where tenant_id = $1 and id = $2 and status <> 'archived'
      limit 1
      for update
      `,
      [auth.tenantId, input.adoptionId],
    );
    const adoption = adoptionResult.rows[0];
    if (!adoption) throw new HttpError(404, 'Question bank adoption not found', 'QUESTION_BANK_ADOPTION_NOT_FOUND');
    if (!adoption.targetQuestionBankId || !adoption.targetEntryId || !adoption.targetCollectionId) {
      throw new HttpError(409, 'Question bank adoption target is incomplete', 'QUESTION_BANK_ADOPTION_TARGET_MISSING');
    }

    const lastConflicts = lastSyncConflicts(adoption.metadata || {});
    const conflictsBySourceId = new Map<string, Record<string, unknown>>();
    for (const conflict of lastConflicts) {
      const sourceQuestionId = nullableString(conflict.sourceQuestionId);
      if (sourceQuestionId && !conflictsBySourceId.has(sourceQuestionId)) {
        conflictsBySourceId.set(sourceQuestionId, conflict);
      }
    }

    const selectedConflicts = input.resolveAll
      ? lastConflicts.filter(conflict => nullableString(conflict.sourceQuestionId)).slice(0, limit)
      : sourceQuestionIds.map(sourceQuestionId => conflictsBySourceId.get(sourceQuestionId));
    const missingIds = input.resolveAll ? [] : sourceQuestionIds.filter(sourceQuestionId => !conflictsBySourceId.has(sourceQuestionId));
    if (missingIds.length) {
      throw new HttpError(404, 'Public question bank conflict not found', 'PUBLIC_BANK_CONFLICT_NOT_FOUND');
    }

    const grant = adoption.grantId ? await loadEligibleGrant(client, auth.tenantId, adoption.grantId) : null;
    if (!grant || grant.sourceQuestionBankId !== adoption.sourceQuestionBankId) {
      throw new HttpError(403, 'Question bank grant is not available for this tenant', 'QUESTION_BANK_GRANT_NOT_AVAILABLE');
    }

    const sourceQuestions = await sourceQuestionSnapshots(client, {
      sourceTenantId: grant.sourceTenantId,
      sourceQuestionBankId: grant.sourceQuestionBankId,
      copyLimit: 1000,
    });
    const sourceById = new Map(sourceQuestions.map(question => [question.id, question]));

    const snapshot = sourceSnapshotObject(adoption.sourceSnapshot);
    const metadata: Record<string, unknown> = {
      ...(adoption.metadata || {}),
      resolvedPublicBankConflicts: {
        ...resolvedPublicBankConflicts(adoption.metadata || {}),
      },
    };
    const resolved = objectValue(metadata.resolvedPublicBankConflicts);
    const processedSourceIds = new Set<string>();
    const results: Array<{
      sourceQuestionId: string;
      targetQuestionId: string;
      resolution: PublicQuestionBankConflictResolution;
      sourceHash: string;
      previousTargetHash: string | null;
    }> = [];

    for (const conflict of selectedConflicts) {
      if (!conflict) continue;
      const sourceQuestionId = nullableString(conflict.sourceQuestionId);
      if (!sourceQuestionId || processedSourceIds.has(sourceQuestionId)) continue;

      const targetQuestionId = nullableString(conflict.targetQuestionId);
      if (!targetQuestionId) {
        throw new HttpError(409, 'Conflict has no target question', 'PUBLIC_BANK_CONFLICT_TARGET_MISSING');
      }

      const source = sourceById.get(sourceQuestionId);
      if (!source) throw new HttpError(404, 'Source question not found', 'PUBLIC_BANK_SOURCE_QUESTION_NOT_FOUND');

      const target = await client.query<{ id: string; source_hash: string | null }>(
        `
        select q.id, v.source_hash
        from public.questions q
        left join public.question_versions v on v.id = q.current_version_id
        where q.tenant_id = $1 and q.id = $2
        limit 1
        for update of q
        `,
        [auth.tenantId, targetQuestionId],
      );
      const targetRow = target.rows[0];
      if (!targetRow) throw new HttpError(404, 'Target question not found', 'PUBLIC_BANK_TARGET_QUESTION_NOT_FOUND');

      if (input.resolution === 'accept_platform') {
        const latest = await client.query<{ version_no: number }>(
          'select coalesce(max(version_no), 0) as version_no from public.question_versions where question_id = $1',
          [targetQuestionId],
        );
        const nextVersionNo = Number(latest.rows[0]?.version_no || 0) + 1;
        const version = await client.query<{ id: string }>(
          `
          insert into public.question_versions (
            tenant_id, question_id, version_no, content, options,
            correct_option_index, correct_option_indices, answer_text,
            explanation, sub_questions, code_lang, code_template,
            source_hash, created_by
          )
          values ($1, $2, $3, $4, $5::jsonb, $6, $7::jsonb, $8, $9, $10::jsonb, $11, $12, $13, $14)
          returning id
          `,
          [
            auth.tenantId,
            targetQuestionId,
            nextVersionNo,
            source.content,
            JSON.stringify(source.options || []),
            source.correctOptionIndex,
            JSON.stringify(source.correctOptionIndices || []),
            source.answerText,
            source.explanation,
            JSON.stringify(source.subQuestions || []),
            source.codeLang,
            source.codeTemplate,
            source.sourceHash,
            auth.userId,
          ],
        );
        await client.query(
          `
          update public.questions
          set question_bank_id = $3,
              entry_id = $4,
              primary_collection_id = $5,
              type = $6,
              type_label = $7,
              difficulty = $8,
              tags = $9::jsonb,
              media_url = $10,
              has_video_explanation = $11,
              status = 'published',
              current_version_id = $12,
              updated_at = now()
          where tenant_id = $1 and id = $2
          `,
          [
            auth.tenantId,
            targetQuestionId,
            adoption.targetQuestionBankId,
            adoption.targetEntryId,
            adoption.targetCollectionId,
            source.type,
            source.typeLabel,
            source.difficulty,
            JSON.stringify(source.tags || []),
            source.mediaUrl,
            source.hasVideoExplanation,
            version.rows[0].id,
          ],
        );
        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, null, true, $6::jsonb)
          on conflict (tenant_id, collection_id, question_id)
          do update set section_key = excluded.section_key,
                        metadata = excluded.metadata,
                        updated_at = now()
          `,
          [
            auth.tenantId,
            adoption.targetCollectionId,
            targetQuestionId,
            source.type,
            0,
            JSON.stringify({
              source: 'public_question_bank_adoption',
              sourceQuestionId,
              lastConflictResolution: 'accept_platform',
            }),
          ],
        );
        setSourceSnapshotQuestion(snapshot, sourceQuestionId, source.sourceHash, { resolution: 'accept_platform' });
        delete resolved[sourceQuestionId];
      } else {
        setSourceSnapshotQuestion(snapshot, sourceQuestionId, source.sourceHash, { resolution: 'keep_local' });
        resolved[sourceQuestionId] = {
          decision: 'keep_local',
          sourceHash: source.sourceHash,
          targetHash: targetRow.source_hash,
          resolvedAt: new Date().toISOString(),
          resolvedBy: auth.userId,
        };
      }

      processedSourceIds.add(sourceQuestionId);
      results.push({
        sourceQuestionId,
        targetQuestionId,
        resolution: input.resolution,
        sourceHash: source.sourceHash,
        previousTargetHash: targetRow.source_hash,
      });
    }

    if (input.resolution === 'accept_platform' && results.length) {
      await client.query(
        `
        update public.question_collections
        set question_count = (
              select count(*)
              from public.question_collection_items
              where tenant_id = $1 and collection_id = $2
            ),
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [auth.tenantId, adoption.targetCollectionId],
      );
    }

    const remainingConflicts = lastConflicts.filter(conflict => {
      const sourceQuestionId = nullableString(conflict.sourceQuestionId);
      return !sourceQuestionId || !processedSourceIds.has(sourceQuestionId);
    });
    const now = new Date().toISOString();
    const lastSync = {
      ...objectValue((adoption.metadata || {}).lastSync),
      status: remainingConflicts.length ? 'failed' : 'resolved',
      conflictCount: remainingConflicts.length,
      conflicts: remainingConflicts,
      lastBatchResolution: {
        sourceQuestionIds: results.map(item => item.sourceQuestionId),
        resolution: input.resolution,
        resolvedAt: now,
        processedCount: results.length,
      },
      lastResolution: results.length === 1 ? {
        sourceQuestionId: results[0]?.sourceQuestionId,
        resolution: input.resolution,
        resolvedAt: now,
      } : objectValue((adoption.metadata || {}).lastSync).lastResolution,
    };
    metadata.resolvedPublicBankConflicts = resolved;
    metadata.lastSync = lastSync;

    const updated = await client.query<AdoptionRow>(
      `
      update public.tenant_question_bank_adoptions
      set sync_status = $3,
          source_snapshot = $4::jsonb,
          metadata = $5::jsonb,
          updated_by = $6,
          updated_at = now()
      where tenant_id = $1 and id = $2
      returning id, tenant_id as "tenantId",
                source_question_bank_id as "sourceQuestionBankId",
                grant_id as "grantId",
                target_question_bank_id as "targetQuestionBankId",
                target_entry_id as "targetEntryId",
                target_collection_id as "targetCollectionId",
                adoption_mode as "adoptionMode", status,
                sync_status as "syncStatus",
                source_snapshot as "sourceSnapshot",
                copied_question_count as "copiedQuestionCount",
                metadata, created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        auth.tenantId,
        adoption.id,
        remainingConflicts.length ? 'failed' : 'synced',
        JSON.stringify(snapshot),
        JSON.stringify(metadata),
        auth.userId,
      ],
    );

    if (!remainingConflicts.length) {
      await markPublicBankConflictNotificationsResolved(client, {
        tenantId: auth.tenantId,
        adoptionId: adoption.id,
        actorUserId: auth.userId,
      });
    }

    for (const result of results) {
      await client.query(
        `
        insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
        values ($1, $2, 'content.public_question_bank.conflict_resolved', 'tenant_question_bank_adoption', $3, $4::jsonb)
        `,
        [
          auth.tenantId,
          auth.userId,
          adoption.id,
          JSON.stringify({
            sourceQuestionBankId: adoption.sourceQuestionBankId,
            sourceQuestionId: result.sourceQuestionId,
            targetQuestionId: result.targetQuestionId,
            resolution: input.resolution,
            sourceHash: result.sourceHash,
            previousTargetHash: result.previousTargetHash,
            remainingConflictCount: remainingConflicts.length,
            batch: true,
          }),
        ],
      );
    }

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'content.public_question_bank.conflicts_batch_resolved', 'tenant_question_bank_adoption', $3, $4::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        adoption.id,
        JSON.stringify({
          sourceQuestionBankId: adoption.sourceQuestionBankId,
          resolution: input.resolution,
          requestedCount: input.resolveAll ? null : sourceQuestionIds.length,
          processedCount: results.length,
          remainingConflictCount: remainingConflicts.length,
          resolveAll: input.resolveAll,
          limit,
          results,
        }),
      ],
    );

    return {
      adoption: updated.rows[0],
      resolution: input.resolution,
      processedCount: results.length,
      remainingConflictCount: remainingConflicts.length,
      syncStatus: updated.rows[0].syncStatus,
      results,
    };
  });
}

export async function resolvePublicQuestionBankConflictsRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const adoptionId = requiredString(body, 'adoptionId');
  const resolution = parsePublicQuestionBankConflictResolution(requiredString(body, 'resolution'));
  const sourceQuestionIds = sourceQuestionIdsFromBody(body.sourceQuestionIds);
  const resolveAll = boolValue(body.resolveAll, false);
  const limit = Math.max(1, Math.min(intValue(body.limit, sourceQuestionIds.length || 50), 100));

  const item = await resolvePublicQuestionBankConflictsBatch(auth, {
    adoptionId,
    sourceQuestionIds,
    resolution,
    resolveAll,
    limit,
  });

  return { item };
}

export async function tenantContentNotificationsRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const rawStatus = stringParam(ctx, 'status');
  const status = rawStatus ? parseNotificationStatus(rawStatus) : '';
  const rawType = stringParam(ctx, 'notificationType');
  const adoptionId = stringParam(ctx, 'adoptionId');
  const limit = intParam(ctx, 'limit', 50, 100);

  const items = await query<ContentNotificationRow>(
    `
    select id,
           notification_type as "notificationType",
           status,
           severity,
           adoption_id as "adoptionId",
           source_question_bank_id as "sourceQuestionBankId",
           title,
           message,
           action_label as "actionLabel",
           action_path as "actionPath",
           metadata,
           created_at as "createdAt",
           updated_at as "updatedAt",
           read_at as "readAt",
           resolved_at as "resolvedAt"
    from public.tenant_content_notifications
    where tenant_id = $1
      and ($2::text = '' or status = $2)
      and ($3::text = '' or notification_type = $3)
      and ($4::uuid is null or adoption_id = $4::uuid)
    order by created_at desc
    limit $5
    `,
    [auth.tenantId, status, rawType, adoptionId || null, limit],
  );

  const summaryRows = await query<{ status: string; count: number }>(
    `
    select status, count(*)::integer as count
    from public.tenant_content_notifications
    where tenant_id = $1
      and ($2::text = '' or notification_type = $2)
    group by status
    `,
    [auth.tenantId, rawType],
  );
  const summary = Object.fromEntries(summaryRows.map(row => [row.status, Number(row.count || 0)]));

  return { items, summary };
}

export async function updateTenantContentNotificationStatusRoute(ctx: RequestContext) {
  const auth = await requireTenantContentEditor(ctx);
  const body = await readJsonBody(ctx);
  const status = parseNotificationUpdateStatus(requiredString(body, 'status'));
  const ids = Array.isArray(body.notificationIds)
    ? body.notificationIds
        .map(item => uuidString(item))
        .filter(Boolean)
        .slice(0, 100)
    : [];
  if (!ids.length) {
    throw new HttpError(400, 'notificationIds is required', 'CONTENT_NOTIFICATION_IDS_REQUIRED');
  }

  const updated = await query<ContentNotificationRow>(
    `
    update public.tenant_content_notifications
    set status = $3,
        read_by = case when $3 in ('read', 'dismissed', 'resolved') then coalesce(read_by, $4) else read_by end,
        read_at = case when $3 in ('read', 'dismissed', 'resolved') then coalesce(read_at, now()) else read_at end,
        resolved_at = case when $3 = 'resolved' then coalesce(resolved_at, now()) else resolved_at end,
        updated_at = now()
    where tenant_id = $1
      and id = any($2::uuid[])
    returning id,
              notification_type as "notificationType",
              status,
              severity,
              adoption_id as "adoptionId",
              source_question_bank_id as "sourceQuestionBankId",
              title,
              message,
              action_label as "actionLabel",
              action_path as "actionPath",
              metadata,
              created_at as "createdAt",
              updated_at as "updatedAt",
              read_at as "readAt",
              resolved_at as "resolvedAt"
    `,
    [auth.tenantId, ids, status, auth.userId],
  );

  return {
    item: {
      status,
      requestedCount: ids.length,
      updatedCount: updated.length,
      notifications: updated,
    },
  };
}
