import { createHash, randomUUID } from 'node:crypto';
import type pg from 'pg';
import { HttpError } from '../../core/errors.js';
import type { RequestContext } from '../../core/http.js';
import { query, queryOne, transaction } from '../../core/db.js';
import type { TenantContentAuth } from './auth.js';

type JsonObject = Record<string, unknown>;

interface ImportIssue {
  rowNo: number;
  severity: 'error' | 'warning';
  code: string;
  fieldPath: string | null;
  message: string;
  details?: JsonObject;
}

interface NormalizedQuestion {
  legacyId: string | null;
  type: string;
  typeLabel: string | null;
  content: string;
  options: unknown[];
  correctOptionIndex: number | null;
  correctOptionIndices: number[];
  answerText: string | null;
  explanation: string | null;
  difficulty: number;
  tags: string[];
  mediaUrl: string | null;
  subQuestions: unknown[];
  codeLang: string | null;
  codeTemplate: string | null;
  examMarkers: JsonObject;
  sourceHash: string;
}

interface NormalizedVocabularyWord {
  legacyId: string | null;
  word: string;
  phonetic: string | null;
  meaning: string;
  example: string | null;
  exampleTranslation: string | null;
  difficulty: number | null;
  tags: string[];
  order: number;
  isActive: boolean;
  sourceHash: string;
  metadata: JsonObject;
}

interface NormalizedVocabularyUnit {
  legacyId: string | null;
  name: string;
  description: string | null;
  order: number;
  wordCount: number | null;
  isActive: boolean;
  words: NormalizedVocabularyWord[];
  sourceHash: string;
  metadata: JsonObject;
}

interface NormalizedHandbookEntry {
  legacyId: string | null;
  title: string;
  summary: string | null;
  content: string;
  tags: string[];
  order: number;
  isActive: boolean;
  sourceHash: string;
  metadata: JsonObject;
}

interface NormalizedHandbookSection {
  legacyId: string | null;
  name: string | null;
  description: string | null;
  order: number;
  isActive: boolean;
  entries: NormalizedHandbookEntry[];
  sourceHash: string;
  metadata: JsonObject;
}

interface NormalizedHandbookChapter {
  legacyId: string | null;
  name: string;
  description: string | null;
  order: number;
  isActive: boolean;
  sections: NormalizedHandbookSection[];
  sourceHash: string;
  metadata: JsonObject;
}

interface NormalizedHandbookSubject {
  legacyId: string | null;
  name: string;
  type: string | null;
  icon: string | null;
  color: string | null;
  description: string | null;
  order: number;
  isActive: boolean;
  chapters: NormalizedHandbookChapter[];
  sourceHash: string;
  metadata: JsonObject;
}

type ContentImportType = 'vocabulary' | 'handbook' | 'scoreline' | 'videos';
export type ExecutableContentImportType = 'questions' | ContentImportType;
type EntryBoundImportType = 'vocabulary' | 'handbook';
type GenericTargetEntryType = EntryBoundImportType | null;
type SourceImportFormat = 'json' | 'csv' | 'excel';

type ScorelineImportKind = 'field' | 'school' | 'major' | 'record';

interface NormalizedScorelineItem {
  kind: ScorelineImportKind;
  legacyId: string | null;
  regionId: string | null;
  sourceHash: string;
  metadata: JsonObject;
  fieldKey?: string;
  fieldName?: string;
  fieldType?: string | null;
  unit?: string | null;
  isFilter?: boolean;
  isRequired?: boolean;
  isVisible?: boolean;
  isTrend?: boolean;
  options?: string[];
  placeholder?: string | null;
  description?: string | null;
  schoolId?: string | null;
  schoolLegacyId?: string | null;
  schoolName?: string | null;
  schoolShortName?: string | null;
  schoolType?: string | null;
  isHot?: boolean;
  majorId?: string | null;
  majorLegacyId?: string | null;
  majorName?: string | null;
  order?: number;
  hasRestriction?: boolean;
  restrictionDesc?: string | null;
  year?: number;
  fieldValues?: JsonObject;
}

interface NormalizedVideoBinding {
  legacyId: string | null;
  questionId: string | null;
  legacyQuestionId: string | null;
  videoType: string;
  order: number;
}

interface NormalizedVideoImportItem {
  legacyId: string | null;
  title: string;
  description: string | null;
  videoUrl: string | null;
  thumbnailUrl: string | null;
  duration: number | null;
  knowledgeTags: string[];
  isGeneral: boolean;
  subjectId: string | null;
  difficulty: number | null;
  order: number;
  isActive: boolean;
  assetId: string | null;
  accessMode: 'free' | 'svip' | 'video_quota';
  freePreviewSeconds: number;
  bindings: NormalizedVideoBinding[];
  sourceHash: string;
  metadata: JsonObject;
}

interface PreviewResult<T = NormalizedQuestion> {
  job: {
    id: string;
    status: string;
    totalCount: number;
    validCount: number;
    errorCount: number;
    warningCount: number;
  };
  items: Array<{
    rowNo: number;
    status: 'valid' | 'invalid';
    externalId: string | null;
    normalized: T | null;
    issues: ImportIssue[];
  }>;
  issues: ImportIssue[];
}

const OBJECTIVE_TYPES = new Set(['choice', 'multi', 'judge', 'image']);
const READING_TYPE = 'reading';
const KNOWN_TYPES = new Set([
  ...OBJECTIVE_TYPES,
  READING_TYPE,
  'text',
  'terms',
  'short_answer',
  'composition',
  'discuss',
  'translation',
  'case_analysis',
  'brief_analysis',
  'calculation',
  'analysis_design',
  'combination',
  'solution',
]);

function objectValue(value: unknown): JsonObject {
  return value && typeof value === 'object' && !Array.isArray(value) ? value as JsonObject : {};
}

function stringValue(value: unknown) {
  return typeof value === 'string' && value.trim() ? value.trim() : '';
}

function sourceImportFormat(value: unknown): SourceImportFormat {
  const normalized = stringValue(value).toLowerCase();
  if (!normalized || normalized === 'json') return 'json';
  if (normalized === 'csv') return 'csv';
  if (['excel', 'xlsx'].includes(normalized)) return 'excel';
  throw new HttpError(400, 'sourceFormat must be json, csv, or excel', 'IMPORT_FORMAT_NOT_SUPPORTED');
}

function isAsyncExecution(value: unknown) {
  const normalized = stringValue(value).toLowerCase();
  return ['async', 'queued', 'queue'].includes(normalized);
}

function stringArrayValue(value: unknown) {
  if (!Array.isArray(value)) return [];
  return value.map(item => String(item).trim()).filter(Boolean);
}

function numberArrayValue(value: unknown) {
  if (!Array.isArray(value)) return [];
  return value
    .map(item => Number(item))
    .filter(Number.isFinite)
    .map(item => Math.trunc(item));
}

function parseQuestionItems(body: JsonObject) {
  const source = body.items ?? body.questions ?? body.payload;
  let parsed: unknown = source;
  if (typeof source === 'string') {
    try {
      parsed = JSON.parse(source);
    } catch {
      throw new HttpError(400, 'payload must be a valid JSON array string', 'INVALID_IMPORT_PAYLOAD');
    }
  }

  if (!Array.isArray(parsed)) {
    throw new HttpError(400, 'items/questions/payload must be a JSON array', 'INVALID_IMPORT_PAYLOAD');
  }
  if (parsed.length === 0) {
    throw new HttpError(400, 'Import payload must contain at least one item', 'EMPTY_IMPORT_PAYLOAD');
  }
  if (parsed.length > 2000) {
    throw new HttpError(400, 'A single import job can contain at most 2000 items', 'IMPORT_TOO_LARGE');
  }

  return parsed;
}

function parseJsonMaybe(value: unknown, code: string) {
  if (typeof value !== 'string') return value;
  try {
    return JSON.parse(value);
  } catch {
    throw new HttpError(400, 'payload must be valid JSON', code);
  }
}

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

function contentHash(value: unknown) {
  return createHash('sha256').update(JSON.stringify(value)).digest('hex');
}

function boolishValue(value: unknown, fallback: boolean) {
  if (typeof value === 'boolean') return value;
  if (typeof value === 'string') {
    const normalized = value.trim().toLowerCase();
    if (['true', '1', 'yes', 'y'].includes(normalized)) return true;
    if (['false', '0', 'no', 'n'].includes(normalized)) return false;
  }
  return fallback;
}

function boolValue(value: unknown, fallback: boolean) {
  return typeof value === 'boolean' ? value : fallback;
}

function nullableString(value: unknown) {
  return typeof value === 'string' && value.trim() ? value.trim() : null;
}

function requiredString(body: JsonObject, key: string) {
  const value = body[key];
  if (typeof value !== 'string' || !value.trim()) {
    throw new HttpError(400, `${key} is required`, 'REQUIRED_FIELD');
  }
  return value.trim();
}

async function routeDeps() {
  const [{ config }, request, auth, spreadsheet] = await Promise.all([
    import('../../core/config.js'),
    import('../../core/request.js'),
    import('./auth.js'),
    import('./import-spreadsheet.js'),
  ]);
  return {
    config,
    intParam: request.intParam,
    readJsonBody: request.readJsonBody,
    requiredString: request.requiredString,
    stringParam: request.stringParam,
    requireTenantContentEditor: auth.requireTenantContentEditor,
    expandSpreadsheetImportBody: spreadsheet.expandSpreadsheetImportBody,
  };
}

function integerValue(value: unknown, fallback: number) {
  const numberValue = Number(value ?? fallback);
  return Number.isFinite(numberValue) ? Math.trunc(numberValue) : fallback;
}

function nullableDifficulty(value: unknown, issues: ImportIssue[], rowNo: number, fieldPath: string) {
  if (value === undefined || value === null || value === '') return null;
  const parsed = Number(value);
  if (!Number.isFinite(parsed)) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'INVALID_DIFFICULTY',
      fieldPath,
      message: 'difficulty must be a number from 1 to 5',
    });
    return null;
  }
  const difficulty = Math.trunc(parsed);
  if (difficulty < 1 || difficulty > 5) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'DIFFICULTY_OUT_OF_RANGE',
      fieldPath,
      message: 'difficulty is outside the recommended 1-5 range and was clamped',
      details: { original: value },
    });
  }
  return Math.min(5, Math.max(1, difficulty));
}

function normalizeDifficulty(value: unknown, issues: ImportIssue[], rowNo: number) {
  if (value === undefined || value === null || value === '') return 1;
  const parsed = Number(value);
  if (!Number.isFinite(parsed)) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'INVALID_DIFFICULTY',
      fieldPath: 'difficulty',
      message: 'difficulty must be a number from 1 to 5',
    });
    return 1;
  }
  const difficulty = Math.trunc(parsed);
  if (difficulty < 1 || difficulty > 5) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'DIFFICULTY_OUT_OF_RANGE',
      fieldPath: 'difficulty',
      message: 'difficulty is outside the recommended 1-5 range and was clamped',
      details: { original: value },
    });
  }
  return Math.min(5, Math.max(1, difficulty));
}

function normalizeOptions(value: unknown, issues: ImportIssue[], rowNo: number, fieldPath = 'options') {
  if (!Array.isArray(value)) return [];
  const normalized = value.filter(item => {
    if (typeof item === 'string') return item.trim() !== '';
    return item && typeof item === 'object';
  });

  if (normalized.length !== value.length) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'EMPTY_OPTIONS_REMOVED',
      fieldPath,
      message: 'empty options were removed during normalization',
    });
  }

  return normalized;
}

function normalizeSubQuestions(value: unknown, issues: ImportIssue[], rowNo: number) {
  if (!Array.isArray(value)) return [];

  return value.map((raw, index) => {
    const sub = objectValue(raw);
    const fieldPrefix = `sub_questions[${index}]`;
    const type = stringValue(sub.type) || 'choice';
    const content = stringValue(sub.content);
    const options = normalizeOptions(sub.options, issues, rowNo, `${fieldPrefix}.options`);
    const correctOptionIndices = numberArrayValue(sub.correctOptionIndices ?? sub.correct_option_indices);
    const answerText = stringValue(sub.answerText ?? sub.answer_text);

    if (!content) {
      issues.push({
        rowNo,
        severity: 'error',
        code: 'SUB_QUESTION_CONTENT_REQUIRED',
        fieldPath: `${fieldPrefix}.content`,
        message: 'sub question content is required',
      });
    }

    if (OBJECTIVE_TYPES.has(type)) {
      validateObjectiveAnswer(options, correctOptionIndices, issues, rowNo, fieldPrefix);
    } else if (!answerText) {
      issues.push({
        rowNo,
        severity: 'error',
        code: 'SUB_QUESTION_ANSWER_REQUIRED',
        fieldPath: `${fieldPrefix}.answerText`,
        message: 'subjective sub question requires answerText',
      });
    }

    return {
      type,
      typeLabel: stringValue(sub.typeLabel ?? sub.type_label) || null,
      content,
      options,
      correctOptionIndices,
      answerText: answerText || null,
      explanation: stringValue(sub.explanation) || null,
    };
  });
}

function validateObjectiveAnswer(
  options: unknown[],
  correctOptionIndices: number[],
  issues: ImportIssue[],
  rowNo: number,
  fieldPrefix = '',
) {
  const prefix = fieldPrefix ? `${fieldPrefix}.` : '';
  if (options.length < 2) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'OPTIONS_REQUIRED',
      fieldPath: `${prefix}options`,
      message: 'objective question requires at least two options',
    });
  }

  if (correctOptionIndices.length === 0) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'CORRECT_OPTION_REQUIRED',
      fieldPath: `${prefix}correctOptionIndices`,
      message: 'objective question requires correctOptionIndices',
    });
  }

  for (const index of correctOptionIndices) {
    if (index < 0 || index >= options.length) {
      issues.push({
        rowNo,
        severity: 'error',
        code: 'CORRECT_OPTION_OUT_OF_RANGE',
        fieldPath: `${prefix}correctOptionIndices`,
        message: 'correct option index is outside the options range',
        details: { index, optionsCount: options.length },
      });
    }
  }
}

function normalizeQuestion(raw: unknown, rowNo: number) {
  const issues: ImportIssue[] = [];
  const source = objectValue(raw);
  if (!source || Object.keys(source).length === 0) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'ROW_OBJECT_REQUIRED',
      fieldPath: null,
      message: 'each import row must be an object',
    });
    return { normalized: null, issues };
  }

  const subQuestions = normalizeSubQuestions(source.sub_questions ?? source.subQuestions, issues, rowNo);
  let type = stringValue(source.type) || (subQuestions.length ? READING_TYPE : 'choice');
  if (subQuestions.length) type = READING_TYPE;

  if (!KNOWN_TYPES.has(type)) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'UNKNOWN_QUESTION_TYPE',
      fieldPath: 'type',
      message: 'unknown question type was kept for compatibility',
      details: { type },
    });
  }

  const content = stringValue(source.content);
  if (!content) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'CONTENT_REQUIRED',
      fieldPath: 'content',
      message: 'question content is required',
    });
  }

  const options = normalizeOptions(source.options, issues, rowNo);
  const legacyCorrectIndex = source.correctOptionIndex ?? source.correct_option_index;
  const correctOptionIndices = numberArrayValue(source.correctOptionIndices ?? source.correct_option_indices);
  if (correctOptionIndices.length === 0 && legacyCorrectIndex !== undefined && legacyCorrectIndex !== null && legacyCorrectIndex !== '') {
    const parsed = Number(legacyCorrectIndex);
    if (Number.isFinite(parsed)) correctOptionIndices.push(Math.trunc(parsed));
  }
  const answerText = stringValue(source.answerText ?? source.answer_text);

  if (OBJECTIVE_TYPES.has(type)) {
    validateObjectiveAnswer(options, correctOptionIndices, issues, rowNo);
  } else if (type === READING_TYPE) {
    if (subQuestions.length === 0) {
      issues.push({
        rowNo,
        severity: 'error',
        code: 'SUB_QUESTIONS_REQUIRED',
        fieldPath: 'sub_questions',
        message: 'reading question requires sub_questions',
      });
    }
  } else if (!answerText) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'ANSWER_TEXT_REQUIRED',
      fieldPath: 'answerText',
      message: 'subjective question requires answerText',
    });
  }

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id) || null,
    type,
    typeLabel: stringValue(source.typeLabel ?? source.type_label) || null,
    content,
    options,
    correctOptionIndex: correctOptionIndices.length === 1 ? correctOptionIndices[0] : null,
    correctOptionIndices,
    answerText: answerText || null,
    explanation: stringValue(source.explanation) || null,
    difficulty: normalizeDifficulty(source.difficulty, issues, rowNo),
    tags: stringArrayValue(source.tags),
    mediaUrl: stringValue(source.mediaUrl ?? source.media_url) || null,
    subQuestions,
    codeLang: stringValue(source.codeLang ?? source.code_lang) || null,
    codeTemplate: stringValue(source.codeTemplate ?? source.code_template) || null,
    examMarkers: objectValue(source.examMarkers ?? source.exam_markers),
  };

  const normalized: NormalizedQuestion = {
    ...normalizedWithoutHash,
    sourceHash: contentHash(normalizedWithoutHash),
  };

  return { normalized, issues };
}

async function assertTargetReferences(client: pg.PoolClient, auth: TenantContentAuth, body: JsonObject) {
  const subjectId = requiredString(body, 'subjectId');
  const categoryId = requiredString(body, 'categoryId');
  const nodeId = nullableString(body.nodeId);
  const questionBankId = nullableString(body.questionBankId);
  const regionId = nullableString(body.regionId);
  const entryId = nullableString(body.entryId);
  const contentNodeId = nullableString(body.contentNodeId);
  const collectionId = nullableString(body.collectionId) || nullableString(body.primaryCollectionId);

  const subject = await client.query(
    'select id, region_id from public.subjects where tenant_id = $1 and id = $2 limit 1',
    [auth.tenantId, subjectId],
  );
  if (!subject.rows[0]) {
    throw new HttpError(400, 'subjectId is not in this tenant', 'SUBJECT_NOT_FOUND');
  }

  const category = await client.query(
    'select id from public.categories where tenant_id = $1 and id = $2 and subject_id = $3 limit 1',
    [auth.tenantId, categoryId, subjectId],
  );
  if (!category.rows[0]) {
    throw new HttpError(400, 'categoryId is not in this tenant or subject', 'CATEGORY_NOT_FOUND');
  }

  if (nodeId) {
    const node = await client.query(
      'select id from public.module_nodes where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, nodeId],
    );
    if (!node.rows[0]) {
      throw new HttpError(400, 'nodeId is not in this tenant', 'NODE_NOT_FOUND');
    }
  }

  if (questionBankId) {
    const questionBank = await client.query(
      'select id from public.question_banks where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, questionBankId],
    );
    if (!questionBank.rows[0]) {
      throw new HttpError(400, 'questionBankId is not in this tenant', 'QUESTION_BANK_NOT_FOUND');
    }
  }

  if (regionId) {
    const region = await client.query(
      'select id from public.regions where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, regionId],
    );
    if (!region.rows[0]) {
      throw new HttpError(400, 'regionId is not in this tenant', 'REGION_NOT_FOUND');
    }
  }

  if (entryId) {
    const entry = await client.query(
      'select id from public.content_entries where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, entryId],
    );
    if (!entry.rows[0]) {
      throw new HttpError(400, 'entryId is not in this tenant', 'ENTRY_NOT_FOUND');
    }
  }

  if (contentNodeId) {
    const contentNode = await client.query(
      'select id from public.content_nodes where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, contentNodeId],
    );
    if (!contentNode.rows[0]) {
      throw new HttpError(400, 'contentNodeId is not in this tenant', 'CONTENT_NODE_NOT_FOUND');
    }
  }

  if (collectionId) {
    const collection = await client.query(
      'select id from public.question_collections where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, collectionId],
    );
    if (!collection.rows[0]) {
      throw new HttpError(400, 'collectionId is not in this tenant', 'QUESTION_COLLECTION_NOT_FOUND');
    }
  }

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

async function createQuestionPreviewJob(auth: TenantContentAuth, body: JsonObject): Promise<PreviewResult> {
  const rawItems = parseQuestionItems(body);
  const sourceFormat = sourceImportFormat(body.sourceFormat);
  const sourceName = stringValue(body.sourceName) || null;

  return transaction(async client => {
    const target = await assertTargetReferences(client, auth, body);
    const normalizedItems = rawItems.map((raw, index) => {
      const rowNo = index + 1;
      const { normalized, issues } = normalizeQuestion(raw, rowNo);
      return {
        rowNo,
        status: issues.some(issue => issue.severity === 'error') ? 'invalid' as const : 'valid' as const,
        externalId: normalized?.legacyId || null,
        source: raw,
        normalized,
        issues,
      };
    });

    const issues = normalizedItems.flatMap(item => item.issues);
    const errorCount = issues.filter(issue => issue.severity === 'error').length;
    const warningCount = issues.filter(issue => issue.severity === 'warning').length;
    const validCount = normalizedItems.filter(item => item.status === 'valid').length;
    const rawPayload = JSON.stringify(rawItems);
    const normalizedPayload = JSON.stringify(normalizedItems.map(item => item.normalized).filter(Boolean));

    const jobResult = await client.query(
      `
      insert into public.content_import_jobs (
        tenant_id, created_by, import_type, source_format, status,
        source_name, source_hash, target_region_id, target_subject_id,
        target_category_id, target_node_id, target_question_bank_id,
        target_entry_id, target_content_node_id, target_collection_id,
        dry_run, total_count, valid_count, error_count, warning_count,
        summary, raw_payload, normalized_payload, parser_metadata
      )
      values (
        $1, $2, 'questions', $3, 'preview',
        $4, $5, $6::uuid, $7::uuid,
        $8::uuid, $9::uuid, $10::uuid,
        $11::uuid, $12::uuid, $13::uuid,
        true, $14, $15, $16, $17,
        $18::jsonb, $19::jsonb, $20::jsonb, $21::jsonb
      )
      returning id, status, total_count as "totalCount", valid_count as "validCount",
                error_count as "errorCount", warning_count as "warningCount"
      `,
      [
        auth.tenantId,
        auth.userId,
        sourceFormat,
        sourceName,
        contentHash(rawItems),
        target.regionId,
        target.subjectId,
        target.categoryId,
        target.nodeId,
        target.questionBankId,
        target.entryId,
        target.contentNodeId,
        target.collectionId,
        rawItems.length,
        validCount,
        errorCount,
        warningCount,
        JSON.stringify({ target, generatedAt: new Date().toISOString() }),
        rawPayload,
        normalizedPayload,
        JSON.stringify(objectValue(body.spreadsheet)),
      ],
    );

    const job = jobResult.rows[0];
    const responseItems: PreviewResult['items'] = [];

    for (const item of normalizedItems) {
      const itemResult = await client.query(
        `
        insert into public.content_import_items (
          tenant_id, job_id, row_no, external_id, status, target_type,
          source_payload, normalized_payload, content_hash, issues_count
        )
        values ($1, $2, $3, $4, $5, 'question', $6::jsonb, $7::jsonb, $8, $9)
        returning id
        `,
        [
          auth.tenantId,
          job.id,
          item.rowNo,
          item.externalId,
          item.status,
          JSON.stringify(item.source),
          JSON.stringify(item.normalized || {}),
          item.normalized?.sourceHash || contentHash(item.source),
          item.issues.length,
        ],
      );

      const itemId = itemResult.rows[0].id;
      for (const issue of item.issues) {
        await client.query(
          `
          insert into public.content_import_issues (
            tenant_id, job_id, item_id, row_no, severity, code,
            field_path, message, details
          )
          values ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb)
          `,
          [
            auth.tenantId,
            job.id,
            itemId,
            issue.rowNo,
            issue.severity,
            issue.code,
            issue.fieldPath,
            issue.message,
            JSON.stringify(issue.details || {}),
          ],
        );
      }

      responseItems.push({
        rowNo: item.rowNo,
        status: item.status,
        externalId: item.externalId,
        normalized: item.normalized,
        issues: item.issues,
      });
    }

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'content.import.previewed', 'content_import_job', $3, $4::jsonb)
      `,
      [auth.tenantId, auth.userId, job.id, JSON.stringify({ importType: 'questions', total: rawItems.length, errorCount, warningCount })],
    );

    return {
      job,
      items: responseItems,
      issues,
    };
  });
}

interface GenericImportTarget {
  regionId: string | null;
  entryId: string | null;
  contentNodeId: string | null;
}

interface NormalizedImportItem<T> {
  rowNo: number;
  status: 'valid' | 'invalid';
  externalId: string | null;
  source: unknown;
  normalized: T | null;
  issues: ImportIssue[];
}

function parseVocabularyItems(body: JsonObject) {
  const source = parseJsonMaybe(
    body.items ?? body.units ?? body.vocabularyUnits ?? body.vocabulary_units ?? body.payload ?? body,
    'INVALID_VOCABULARY_IMPORT_PAYLOAD',
  );
  const payload = objectValue(source);

  let units = Array.isArray(source) ? source : asArray(
    payload.units ??
      payload.vocabularyUnits ??
      payload.vocabulary_units ??
      payload['vocabulary_units_示例数据'],
  );
  const flatWords = asArray(
    payload.words ??
      payload.vocabularyWords ??
      payload.vocabulary_words ??
      payload.vocabulary ??
      payload['vocabulary_示例数据'],
  );

  if (units.length === 0 && flatWords.length > 0) {
    units = [{
      legacyId: stringValue(payload.unitLegacyId ?? payload.unitId ?? payload.legacyId) || null,
      name: stringValue(payload.unitName ?? payload.name) || '默认单词单元',
      description: stringValue(payload.description) || null,
      words: flatWords,
    }];
  } else if (flatWords.length > 0) {
    const unitBuckets = new Map<number, unknown[]>();
    const unitLookup = new Map<string, number>();
    units.forEach((unit, index) => {
      const raw = objectValue(unit);
      for (const key of [
        raw.id,
        raw.legacyId,
        raw.legacy_id,
        raw.externalId,
        raw.external_id,
        raw.name,
      ]) {
        const value = stringValue(key);
        if (value) unitLookup.set(value, index);
      }
    });

    flatWords.forEach(word => {
      const raw = objectValue(word);
      const ref = stringValue(raw.unitId ?? raw.unit_id ?? raw.unitLegacyId ?? raw.unit_legacy_id ?? raw.unitName ?? raw.unit);
      const index = unitLookup.get(ref);
      if (index !== undefined) {
        const bucket = unitBuckets.get(index) || [];
        bucket.push(word);
        unitBuckets.set(index, bucket);
      }
    });

    units = units.map((unit, index) => ({
      ...objectValue(unit),
      words: [...asArray(objectValue(unit).words), ...(unitBuckets.get(index) || [])],
    }));
  }

  if (units.length === 0) {
    throw new HttpError(400, 'Vocabulary import payload must contain at least one unit', 'EMPTY_IMPORT_PAYLOAD');
  }
  if (units.length > 500) {
    throw new HttpError(400, 'A single vocabulary import job can contain at most 500 units', 'IMPORT_TOO_LARGE');
  }
  return { units, flatWords };
}

function normalizeVocabularyWord(raw: unknown, rowNo: number, index: number, issues: ImportIssue[]) {
  const source = objectValue(raw);
  const fieldPrefix = `words[${index}]`;
  const word = stringValue(source.word ?? source.name);
  const meaning = stringValue(source.meaning ?? source.translation ?? source.definition);

  if (!word) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'VOCABULARY_WORD_REQUIRED',
      fieldPath: `${fieldPrefix}.word`,
      message: 'vocabulary word is required',
    });
  }
  if (!meaning) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'VOCABULARY_MEANING_REQUIRED',
      fieldPath: `${fieldPrefix}.meaning`,
      message: 'vocabulary meaning is required',
    });
  }

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null,
    word,
    phonetic: stringValue(source.phonetic ?? source.pronunciation) || null,
    meaning,
    example: stringValue(source.example) || null,
    exampleTranslation: stringValue(source.exampleTranslation ?? source.example_translation) || null,
    difficulty: nullableDifficulty(source.difficulty, issues, rowNo, `${fieldPrefix}.difficulty`),
    tags: stringArrayValue(source.tags),
    order: integerValue(source.order ?? source.sortOrder ?? source.sort_order, index + 1),
    isActive: boolishValue(source.isActive ?? source.is_active, true),
    metadata: objectValue(source.metadata),
  };

  return {
    ...normalizedWithoutHash,
    sourceHash: contentHash(normalizedWithoutHash),
  } satisfies NormalizedVocabularyWord;
}

function normalizeVocabularyUnit(raw: unknown, rowNo: number, orphanWordsCount: number) {
  const issues: ImportIssue[] = [];
  const source = objectValue(raw);
  const name = stringValue(source.name ?? source.title);
  if (!name) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'VOCABULARY_UNIT_NAME_REQUIRED',
      fieldPath: 'name',
      message: 'vocabulary unit name is required',
    });
  }

  const words = asArray(source.words).map((word, index) => normalizeVocabularyWord(word, rowNo, index, issues));
  if (words.length === 0) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'VOCABULARY_UNIT_EMPTY',
      fieldPath: 'words',
      message: 'vocabulary unit has no words',
    });
  }
  if (orphanWordsCount > 0 && rowNo === 1) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'VOCABULARY_ORPHAN_WORDS_IGNORED',
      fieldPath: 'words',
      message: 'some flat vocabulary rows could not be matched to a unit and were ignored',
      details: { orphanWordsCount },
    });
  }

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null,
    name,
    description: stringValue(source.description) || null,
    order: integerValue(source.order ?? source.sortOrder ?? source.sort_order, rowNo),
    wordCount: source.wordCount === undefined && source.word_count === undefined ? words.length : integerValue(source.wordCount ?? source.word_count, words.length),
    isActive: boolishValue(source.isActive ?? source.is_active, true),
    words,
    metadata: objectValue(source.metadata),
  };

  return {
    normalized: {
      ...normalizedWithoutHash,
      sourceHash: contentHash(normalizedWithoutHash),
    } satisfies NormalizedVocabularyUnit,
    issues,
  };
}

function createVocabularyNormalizedItems(body: JsonObject): NormalizedImportItem<NormalizedVocabularyUnit>[] {
  const { units, flatWords } = parseVocabularyItems(body);
  const attachedWordCount = units.reduce((sum, unit) => sum + asArray(objectValue(unit).words).length, 0);
  const orphanWordsCount = Math.max(0, flatWords.length - attachedWordCount);

  return units.map((raw, index) => {
    const rowNo = index + 1;
    const { normalized, issues } = normalizeVocabularyUnit(raw, rowNo, orphanWordsCount);
    return {
      rowNo,
      status: issues.some(issue => issue.severity === 'error') ? 'invalid' : 'valid',
      externalId: normalized.legacyId || null,
      source: raw,
      normalized,
      issues,
    };
  });
}

function parseHandbookItems(body: JsonObject) {
  const source = parseJsonMaybe(
    body.items ?? body.subjects ?? body.handbooks ?? body.books ?? body.payload ?? body,
    'INVALID_HANDBOOK_IMPORT_PAYLOAD',
  );
  const payload = objectValue(source);

  let subjects = Array.isArray(source) ? source : asArray(
    payload.subjects ??
      payload.handbooks ??
      payload.books ??
      payload.handbookSubjects ??
      payload.handbook_subjects ??
      payload['handbook_categories_示例数据'],
  );

  if (subjects.length === 0 && payload.book && typeof payload.book === 'object') {
    subjects = [payload.book];
  }

  const flatEntries = asArray(
    payload.entries ??
      payload.handbookEntries ??
      payload.handbook_entries ??
      payload['handbook_entries_示例数据'],
  );

  if (subjects.length === 0 && flatEntries.length > 0) {
    subjects = [{
      name: stringValue(payload.subjectName ?? payload.categoryName ?? payload.name) || '默认知识手册',
      chapters: [{
        name: stringValue(payload.chapterName) || '默认章节',
        sections: [{ name: stringValue(payload.sectionName) || null, entries: flatEntries }],
      }],
    }];
  } else if (flatEntries.length > 0) {
    const subjectLookup = new Map<string, number>();
    subjects.forEach((subject, index) => {
      const raw = objectValue(subject);
      for (const key of [raw.id, raw.legacyId, raw.legacy_id, raw.externalId, raw.external_id, raw.name]) {
        const value = stringValue(key);
        if (value) subjectLookup.set(value, index);
      }
    });

    const buckets = new Map<number, unknown[]>();
    flatEntries.forEach(entry => {
      const raw = objectValue(entry);
      const ref = stringValue(raw.subjectId ?? raw.subject_id ?? raw.categoryId ?? raw.category_id ?? raw.subjectName ?? raw.categoryName ?? raw.category);
      const index = subjectLookup.get(ref);
      if (index !== undefined) {
        const bucket = buckets.get(index) || [];
        bucket.push(entry);
        buckets.set(index, bucket);
      }
    });

    subjects = subjects.map((subject, index) => {
      const extraEntries = buckets.get(index) || [];
      if (extraEntries.length === 0) return subject;
      const raw = objectValue(subject);
      return {
        ...raw,
        chapters: [
          ...asArray(raw.chapters),
          {
            name: '默认章节',
            sections: [{ name: null, entries: extraEntries }],
          },
        ],
      };
    });
  }

  if (subjects.length === 0) {
    throw new HttpError(400, 'Handbook import payload must contain at least one subject/book', 'EMPTY_IMPORT_PAYLOAD');
  }
  if (subjects.length > 300) {
    throw new HttpError(400, 'A single handbook import job can contain at most 300 subjects/books', 'IMPORT_TOO_LARGE');
  }
  return subjects;
}

function normalizeHandbookEntry(raw: unknown, rowNo: number, fieldPrefix: string, fallbackOrder: number, issues: ImportIssue[]) {
  const source = objectValue(raw);
  const title = stringValue(source.title ?? source.name);
  const content = stringValue(source.content ?? source.body);
  if (!title) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'HANDBOOK_ENTRY_TITLE_REQUIRED',
      fieldPath: `${fieldPrefix}.title`,
      message: 'handbook entry title is required',
    });
  }
  if (!content) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'HANDBOOK_ENTRY_CONTENT_REQUIRED',
      fieldPath: `${fieldPrefix}.content`,
      message: 'handbook entry content is required',
    });
  }

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null,
    title,
    summary: stringValue(source.summary) || null,
    content,
    tags: stringArrayValue(source.tags),
    order: integerValue(source.order ?? source.sortOrder ?? source.sort_order, fallbackOrder),
    isActive: boolishValue(source.isActive ?? source.is_active, true),
    metadata: {
      ...objectValue(source.metadata),
      hasNext: boolishValue(source.hasNext ?? source.has_next, false),
      nextEntryId: stringValue(source.nextEntryId ?? source.next_entry_id) || null,
    },
  };

  return {
    ...normalizedWithoutHash,
    sourceHash: contentHash(normalizedWithoutHash),
  } satisfies NormalizedHandbookEntry;
}

function normalizeHandbookSection(raw: unknown, rowNo: number, fieldPrefix: string, fallbackOrder: number, issues: ImportIssue[]) {
  const source = objectValue(raw);
  const entries = asArray(source.entries ?? source.handbookEntries ?? source.handbook_entries)
    .map((entry, index) => normalizeHandbookEntry(entry, rowNo, `${fieldPrefix}.entries[${index}]`, index + 1, issues));

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null,
    name: stringValue(source.name ?? source.title) || null,
    description: stringValue(source.description) || null,
    order: integerValue(source.order ?? source.sortOrder ?? source.sort_order, fallbackOrder),
    isActive: boolishValue(source.isActive ?? source.is_active, true),
    entries,
    metadata: objectValue(source.metadata),
  };

  return {
    ...normalizedWithoutHash,
    sourceHash: contentHash(normalizedWithoutHash),
  } satisfies NormalizedHandbookSection;
}

function normalizeHandbookChapter(raw: unknown, rowNo: number, fieldPrefix: string, fallbackOrder: number, issues: ImportIssue[]) {
  const source = objectValue(raw);
  const name = stringValue(source.name ?? source.title);
  if (!name) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'HANDBOOK_CHAPTER_NAME_REQUIRED',
      fieldPath: `${fieldPrefix}.name`,
      message: 'handbook chapter name is required',
    });
  }

  const directEntries = asArray(source.entries ?? source.handbookEntries ?? source.handbook_entries);
  const rawSections = asArray(source.sections ?? source.handbookSections ?? source.handbook_sections);
  const sections = [
    ...rawSections,
    ...(directEntries.length ? [{ name: null, entries: directEntries }] : []),
  ].map((section, index) => normalizeHandbookSection(section, rowNo, `${fieldPrefix}.sections[${index}]`, index + 1, issues));

  if (sections.length === 0) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'HANDBOOK_CHAPTER_EMPTY',
      fieldPath: `${fieldPrefix}.sections`,
      message: 'handbook chapter has no sections or entries',
    });
  }

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null,
    name,
    description: stringValue(source.description) || null,
    order: integerValue(source.order ?? source.sortOrder ?? source.sort_order, fallbackOrder),
    isActive: boolishValue(source.isActive ?? source.is_active, true),
    sections,
    metadata: objectValue(source.metadata),
  };

  return {
    ...normalizedWithoutHash,
    sourceHash: contentHash(normalizedWithoutHash),
  } satisfies NormalizedHandbookChapter;
}

function normalizeHandbookSubject(raw: unknown, rowNo: number) {
  const issues: ImportIssue[] = [];
  const source = objectValue(raw);
  const name = stringValue(source.name ?? source.title);
  if (!name) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'HANDBOOK_SUBJECT_NAME_REQUIRED',
      fieldPath: 'name',
      message: 'handbook subject/book name is required',
    });
  }

  const chapters = asArray(source.chapters ?? source.handbookChapters ?? source.handbook_chapters)
    .map((chapter, index) => normalizeHandbookChapter(chapter, rowNo, `chapters[${index}]`, index + 1, issues));
  if (chapters.length === 0) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'HANDBOOK_SUBJECT_EMPTY',
      fieldPath: 'chapters',
      message: 'handbook subject/book has no chapters',
    });
  }

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null,
    name,
    type: stringValue(source.type) || null,
    icon: stringValue(source.icon) || null,
    color: stringValue(source.color) || null,
    description: stringValue(source.description) || null,
    order: integerValue(source.order ?? source.sortOrder ?? source.sort_order, rowNo),
    isActive: boolishValue(source.isActive ?? source.is_active, true),
    chapters,
    metadata: objectValue(source.metadata),
  };

  return {
    normalized: {
      ...normalizedWithoutHash,
      sourceHash: contentHash(normalizedWithoutHash),
    } satisfies NormalizedHandbookSubject,
    issues,
  };
}

function createHandbookNormalizedItems(body: JsonObject): NormalizedImportItem<NormalizedHandbookSubject>[] {
  return parseHandbookItems(body).map((raw, index) => {
    const rowNo = index + 1;
    const { normalized, issues } = normalizeHandbookSubject(raw, rowNo);
    return {
      rowNo,
      status: issues.some(issue => issue.severity === 'error') ? 'invalid' : 'valid',
      externalId: normalized.legacyId || null,
      source: raw,
      normalized,
      issues,
    };
  });
}

function parseBoundedItems(body: JsonObject, keys: string[], code: string, label: string, maxItems: number) {
  const rawSource = keys.reduce<unknown>((found, key) => found ?? body[key], undefined) ?? body.payload;
  const source = parseJsonMaybe(rawSource ?? body, code);
  const payload = objectValue(source);
  const items = Array.isArray(source)
    ? source
    : keys.reduce<unknown[]>((found, key) => (found.length ? found : asArray(payload[key])), []);
  if (items.length === 0) {
    throw new HttpError(400, `${label} import payload must contain at least one item`, 'EMPTY_IMPORT_PAYLOAD');
  }
  if (items.length > maxItems) {
    throw new HttpError(400, `A single ${label} import job can contain at most ${maxItems} items`, 'IMPORT_TOO_LARGE');
  }
  return items;
}

function uuidLike(value: string | null) {
  return Boolean(value && /^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$/i.test(value));
}

function nullableUuidValue(value: unknown, issues: ImportIssue[], rowNo: number, fieldPath: string) {
  const normalized = stringValue(value) || null;
  if (normalized && !uuidLike(normalized)) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'INVALID_UUID',
      fieldPath,
      message: `${fieldPath} must be a valid UUID`,
    });
  }
  return normalized;
}

function normalizeSortOrder(value: unknown, fallback: number) {
  return integerValue(value, fallback);
}

function normalizeScorelineKind(value: unknown): ScorelineImportKind | null {
  const normalized = stringValue(value).toLowerCase().replace(/[-\s]/g, '_');
  if (['field', 'fields', 'scoreline_field', 'scoreline_fields'].includes(normalized)) return 'field';
  if (['school', 'schools', 'scoreline_school', 'scoreline_schools'].includes(normalized)) return 'school';
  if (['major', 'majors', 'scoreline_major', 'scoreline_majors'].includes(normalized)) return 'major';
  if (['record', 'records', 'scoreline_record', 'scoreline_records'].includes(normalized)) return 'record';
  return null;
}

function scorelineFieldValues(source: JsonObject) {
  const explicit = objectValue(source.fieldValues ?? source.field_values ?? source.values);
  const reserved = new Set([
    'id',
    'legacyId',
    'legacy_id',
    'externalId',
    'external_id',
    'kind',
    'type',
    'regionId',
    'region_id',
    'schoolId',
    'school_id',
    'schoolLegacyId',
    'school_legacy_id',
    'schoolName',
    'school_name',
    'majorId',
    'major_id',
    'majorLegacyId',
    'major_legacy_id',
    'majorName',
    'major_name',
    'year',
    'fieldValues',
    'field_values',
    'values',
    'metadata',
  ]);
  const inferred: JsonObject = {};
  for (const [key, value] of Object.entries(source)) {
    if (!reserved.has(key) && value !== undefined && value !== null && value !== '') inferred[key] = value as unknown;
  }
  return Object.keys(explicit).length ? explicit : inferred;
}

function normalizeScorelineItem(raw: unknown, rowNo: number, defaultRegionId: string | null) {
  const issues: ImportIssue[] = [];
  const source = objectValue(raw);
  const kind = normalizeScorelineKind(source.kind ?? source.itemType ?? source.item_type ?? source.type);
  if (!kind) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'SCORELINE_KIND_REQUIRED',
      fieldPath: 'kind',
      message: 'scoreline item kind must be one of field, school, major, record',
    });
  }

  const regionId = nullableUuidValue(source.regionId ?? source.region_id ?? defaultRegionId, issues, rowNo, 'regionId');
  const legacyId = stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null;
  const base = {
    kind: kind || 'record',
    legacyId,
    regionId,
    metadata: objectValue(source.metadata),
  };

  let normalizedWithoutHash: Omit<NormalizedScorelineItem, 'sourceHash'>;
  if (kind === 'field') {
    const fieldKey = stringValue(source.fieldKey ?? source.field_key ?? source.key);
    const fieldName = stringValue(source.fieldName ?? source.field_name ?? source.name ?? source.label);
    if (!fieldKey) {
      issues.push({ rowNo, severity: 'error', code: 'SCORELINE_FIELD_KEY_REQUIRED', fieldPath: 'fieldKey', message: 'scoreline fieldKey is required' });
    }
    if (!fieldName) {
      issues.push({ rowNo, severity: 'error', code: 'SCORELINE_FIELD_NAME_REQUIRED', fieldPath: 'fieldName', message: 'scoreline fieldName is required' });
    }
    normalizedWithoutHash = {
      ...base,
      kind: 'field',
      fieldKey,
      fieldName,
      fieldType: stringValue(source.fieldType ?? source.field_type) || 'number',
      unit: stringValue(source.unit) || null,
      isFilter: boolishValue(source.isFilter ?? source.is_filter, false),
      isRequired: boolishValue(source.isRequired ?? source.is_required, false),
      isVisible: boolishValue(source.isVisible ?? source.is_visible, true),
      isTrend: boolishValue(source.isTrend ?? source.is_trend, false),
      options: stringArrayValue(source.options),
      placeholder: stringValue(source.placeholder) || null,
      description: stringValue(source.description) || null,
      order: normalizeSortOrder(source.sortOrder ?? source.sort_order ?? source.order, rowNo),
    };
  } else if (kind === 'school') {
    const schoolName = stringValue(source.name ?? source.schoolName ?? source.school_name);
    if (!schoolName) {
      issues.push({ rowNo, severity: 'error', code: 'SCORELINE_SCHOOL_NAME_REQUIRED', fieldPath: 'name', message: 'scoreline school name is required' });
    }
    normalizedWithoutHash = {
      ...base,
      kind: 'school',
      schoolName,
      schoolShortName: stringValue(source.shortName ?? source.short_name) || null,
      schoolType: stringValue(source.schoolType ?? source.type) || null,
      isHot: boolishValue(source.isHot ?? source.is_hot, false),
      order: normalizeSortOrder(source.sortOrder ?? source.sort_order ?? source.order, rowNo),
    };
  } else if (kind === 'major') {
    const schoolId = nullableUuidValue(source.schoolId ?? source.school_id, issues, rowNo, 'schoolId');
    const schoolLegacyId = stringValue(source.schoolLegacyId ?? source.school_legacy_id) || null;
    const schoolName = stringValue(source.schoolName ?? source.school_name) || null;
    const majorName = stringValue(source.name ?? source.majorName ?? source.major_name);
    if (!schoolId && !schoolLegacyId && !schoolName) {
      issues.push({ rowNo, severity: 'error', code: 'SCORELINE_MAJOR_SCHOOL_REQUIRED', fieldPath: 'schoolId', message: 'major requires schoolId, schoolLegacyId, or schoolName' });
    }
    if (!majorName) {
      issues.push({ rowNo, severity: 'error', code: 'SCORELINE_MAJOR_NAME_REQUIRED', fieldPath: 'name', message: 'scoreline major name is required' });
    }
    normalizedWithoutHash = {
      ...base,
      kind: 'major',
      schoolId,
      schoolLegacyId,
      schoolName,
      majorName,
      order: normalizeSortOrder(source.sortOrder ?? source.sort_order ?? source.order, rowNo),
      hasRestriction: boolishValue(source.hasRestriction ?? source.has_restriction, false),
      restrictionDesc: stringValue(source.restrictionDesc ?? source.restriction_desc) || null,
    };
  } else {
    const schoolId = nullableUuidValue(source.schoolId ?? source.school_id, issues, rowNo, 'schoolId');
    const majorId = nullableUuidValue(source.majorId ?? source.major_id, issues, rowNo, 'majorId');
    const schoolLegacyId = stringValue(source.schoolLegacyId ?? source.school_legacy_id) || null;
    const majorLegacyId = stringValue(source.majorLegacyId ?? source.major_legacy_id) || null;
    const schoolName = stringValue(source.schoolName ?? source.school_name) || null;
    const majorName = stringValue(source.majorName ?? source.major_name) || null;
    const year = integerValue(source.year, 0);
    if (!year || year < 1900 || year > 2200) {
      issues.push({ rowNo, severity: 'error', code: 'SCORELINE_YEAR_INVALID', fieldPath: 'year', message: 'scoreline record year must be a valid year' });
    }
    if (!schoolId && !schoolLegacyId && !schoolName) {
      issues.push({ rowNo, severity: 'error', code: 'SCORELINE_RECORD_SCHOOL_REQUIRED', fieldPath: 'schoolId', message: 'record requires schoolId, schoolLegacyId, or schoolName' });
    }
    normalizedWithoutHash = {
      ...base,
      kind: 'record',
      schoolId,
      schoolLegacyId,
      schoolName,
      majorId,
      majorLegacyId,
      majorName,
      year,
      fieldValues: scorelineFieldValues(source),
    };
  }

  const normalized: NormalizedScorelineItem = {
    ...normalizedWithoutHash,
    sourceHash: contentHash(normalizedWithoutHash),
  };

  return { normalized, issues };
}

function createScorelineNormalizedItems(body: JsonObject): NormalizedImportItem<NormalizedScorelineItem>[] {
  const source = parseJsonMaybe(body.items ?? body.payload ?? body, 'INVALID_SCORELINE_IMPORT_PAYLOAD');
  const payload = objectValue(source);
  const defaultRegionId = nullableString(body.regionId) || nullableString(payload.regionId ?? payload.region_id);
  const buckets = [
    ...asArray(payload.fields ?? payload.scorelineFields ?? payload.scoreline_fields).map(item => ({ ...objectValue(item), kind: 'field' })),
    ...asArray(payload.schools ?? payload.scorelineSchools ?? payload.scoreline_schools).map(item => ({ ...objectValue(item), kind: 'school' })),
    ...asArray(payload.majors ?? payload.scorelineMajors ?? payload.scoreline_majors).map(item => ({ ...objectValue(item), kind: 'major' })),
    ...asArray(payload.records ?? payload.scorelineRecords ?? payload.scoreline_records).map(item => ({ ...objectValue(item), kind: 'record' })),
  ];
  const rawItems = Array.isArray(source) ? source : buckets.length ? buckets : asArray(payload.items);
  if (rawItems.length === 0) {
    throw new HttpError(400, 'Scoreline import payload must contain at least one item', 'EMPTY_IMPORT_PAYLOAD');
  }
  if (rawItems.length > 3000) {
    throw new HttpError(400, 'A single scoreline import job can contain at most 3000 items', 'IMPORT_TOO_LARGE');
  }

  return rawItems.map((raw, index) => {
    const rowNo = index + 1;
    const { normalized, issues } = normalizeScorelineItem(raw, rowNo, defaultRegionId);
    return {
      rowNo,
      status: issues.some(issue => issue.severity === 'error') ? 'invalid' : 'valid',
      externalId: normalized.legacyId || null,
      source: raw,
      normalized,
      issues,
    };
  });
}

function normalizeVideoAccessMode(value: unknown, issues: ImportIssue[], rowNo: number) {
  const normalized = stringValue(value).toLowerCase();
  if (!normalized) return 'svip';
  if (['free', 'svip', 'video_quota'].includes(normalized)) return normalized as NormalizedVideoImportItem['accessMode'];
  issues.push({
    rowNo,
    severity: 'error',
    code: 'VIDEO_ACCESS_MODE_INVALID',
    fieldPath: 'accessMode',
    message: 'video accessMode must be free, svip, or video_quota',
  });
  return 'svip';
}

function normalizeVideoBinding(raw: unknown, rowNo: number, index: number, issues: ImportIssue[]): NormalizedVideoBinding {
  const source = objectValue(raw);
  const questionId = nullableUuidValue(source.questionId ?? source.question_id, issues, rowNo, `bindings[${index}].questionId`);
  const legacyQuestionId = stringValue(source.legacyQuestionId ?? source.legacy_question_id ?? source.questionLegacyId ?? source.question_legacy_id) || null;
  if (!questionId && !legacyQuestionId) {
    issues.push({
      rowNo,
      severity: 'error',
      code: 'VIDEO_BINDING_QUESTION_REQUIRED',
      fieldPath: `bindings[${index}].questionId`,
      message: 'video binding requires questionId or legacyQuestionId',
    });
  }
  return {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id) || null,
    questionId,
    legacyQuestionId,
    videoType: stringValue(source.videoType ?? source.video_type) || 'specific',
    order: integerValue(source.order ?? source.sortOrder ?? source.sort_order, index),
  };
}

function normalizeVideoImportItem(raw: unknown, rowNo: number) {
  const issues: ImportIssue[] = [];
  const source = objectValue(raw);
  const title = stringValue(source.title ?? source.name);
  if (!title) {
    issues.push({ rowNo, severity: 'error', code: 'VIDEO_TITLE_REQUIRED', fieldPath: 'title', message: 'video title is required' });
  }
  const videoUrl = stringValue(source.videoUrl ?? source.video_url ?? source.url) || null;
  const assetId = nullableUuidValue(source.assetId ?? source.asset_id, issues, rowNo, 'assetId');
  if (!videoUrl && !assetId) {
    issues.push({
      rowNo,
      severity: 'warning',
      code: 'VIDEO_PLAYABLE_SOURCE_MISSING',
      fieldPath: 'videoUrl',
      message: 'video has no videoUrl or assetId; it can be saved but playback will be unavailable until a resource is bound',
    });
  }
  const subjectId = nullableUuidValue(source.subjectId ?? source.subject_id, issues, rowNo, 'subjectId');
  const directQuestionId = source.questionId ?? source.question_id;
  const directLegacyQuestionId = source.legacyQuestionId ?? source.legacy_question_id ?? source.questionLegacyId ?? source.question_legacy_id;
  const bindings = [
    ...asArray(source.bindings ?? source.questionBindings ?? source.question_bindings),
    ...(directQuestionId || directLegacyQuestionId
      ? [{
        questionId: directQuestionId,
        legacyQuestionId: directLegacyQuestionId,
        videoType: source.videoType ?? source.video_type,
        order: source.bindingOrder ?? source.binding_order ?? source.order,
      }]
      : []),
  ].map((binding, index) => normalizeVideoBinding(binding, rowNo, index, issues));

  const normalizedWithoutHash = {
    legacyId: stringValue(source.legacyId ?? source.legacy_id ?? source.externalId ?? source.external_id ?? source.id) || null,
    title,
    description: stringValue(source.description) || null,
    videoUrl,
    thumbnailUrl: stringValue(source.thumbnailUrl ?? source.thumbnail_url) || null,
    duration: source.duration === undefined && source.durationSeconds === undefined && source.duration_seconds === undefined
      ? null
      : Math.max(0, integerValue(source.duration ?? source.durationSeconds ?? source.duration_seconds, 0)),
    knowledgeTags: stringArrayValue(source.knowledgeTags ?? source.knowledge_tags ?? source.tags),
    isGeneral: boolishValue(source.isGeneral ?? source.is_general, false),
    subjectId,
    difficulty: nullableDifficulty(source.difficulty, issues, rowNo, 'difficulty'),
    order: normalizeSortOrder(source.sortOrder ?? source.sort_order ?? source.order, rowNo),
    isActive: boolishValue(source.isActive ?? source.is_active, true),
    assetId,
    accessMode: normalizeVideoAccessMode(source.accessMode ?? source.access_mode, issues, rowNo),
    freePreviewSeconds: Math.max(0, integerValue(source.freePreviewSeconds ?? source.free_preview_seconds, 0)),
    bindings,
    metadata: objectValue(source.metadata),
  };

  const normalized: NormalizedVideoImportItem = {
    ...normalizedWithoutHash,
    sourceHash: contentHash(normalizedWithoutHash),
  };
  return { normalized, issues };
}

function createVideoNormalizedItems(body: JsonObject): NormalizedImportItem<NormalizedVideoImportItem>[] {
  return parseBoundedItems(body, ['items', 'videos', 'videoExplanations', 'video_explanations'], 'INVALID_VIDEO_IMPORT_PAYLOAD', 'video', 2000)
    .map((raw, index) => {
      const rowNo = index + 1;
      const { normalized, issues } = normalizeVideoImportItem(raw, rowNo);
      return {
        rowNo,
        status: issues.some(issue => issue.severity === 'error') ? 'invalid' : 'valid',
        externalId: normalized.legacyId || null,
        source: raw,
        normalized,
        issues,
      };
    });
}

async function assertGenericTargetReferences(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  body: JsonObject,
  expectedEntryType: GenericTargetEntryType,
): Promise<GenericImportTarget> {
  const regionId = nullableString(body.regionId);
  let entryId = nullableString(body.entryId);
  const contentNodeId = nullableString(body.contentNodeId);

  if (regionId) {
    const region = await client.query('select id from public.regions where tenant_id = $1 and id = $2 limit 1', [auth.tenantId, regionId]);
    if (!region.rows[0]) throw new HttpError(400, 'regionId is not in this tenant', 'REGION_NOT_FOUND');
  }

  if (entryId) {
    const entry = await client.query<{ id: string; entry_type: string }>(
      'select id, entry_type from public.content_entries where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, entryId],
    );
    if (!entry.rows[0]) throw new HttpError(400, 'entryId is not in this tenant', 'ENTRY_NOT_FOUND');
    if (expectedEntryType && entry.rows[0].entry_type !== expectedEntryType) {
      throw new HttpError(400, `entryId must be a ${expectedEntryType} content entry`, 'ENTRY_TYPE_MISMATCH');
    }
  }

  if (contentNodeId) {
    const node = await client.query<{ id: string; entry_id: string }>(
      'select id, entry_id from public.content_nodes where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, contentNodeId],
    );
    if (!node.rows[0]) throw new HttpError(400, 'contentNodeId is not in this tenant', 'CONTENT_NODE_NOT_FOUND');
    if (entryId && node.rows[0].entry_id !== entryId) {
      throw new HttpError(400, 'contentNodeId is not under entryId', 'CONTENT_NODE_ENTRY_MISMATCH');
    }
    entryId = entryId || node.rows[0].entry_id;
  }

  return { regionId, entryId, contentNodeId };
}

async function createGenericPreviewJob<T>(
  auth: TenantContentAuth,
  body: JsonObject,
  importType: ContentImportType,
  targetType: string,
  normalizedItems: NormalizedImportItem<T>[],
  expectedEntryType: GenericTargetEntryType = importType === 'vocabulary' || importType === 'handbook' ? importType : null,
): Promise<PreviewResult<T>> {
  const sourceFormat = sourceImportFormat(body.sourceFormat);
  const sourceName = stringValue(body.sourceName) || null;

  return transaction(async client => {
    const target = await assertGenericTargetReferences(client, auth, body, expectedEntryType);
    const issues = normalizedItems.flatMap(item => item.issues);
    const errorCount = issues.filter(issue => issue.severity === 'error').length;
    const warningCount = issues.filter(issue => issue.severity === 'warning').length;
    const validCount = normalizedItems.filter(item => item.status === 'valid').length;

    const jobResult = await client.query(
      `
      insert into public.content_import_jobs (
        tenant_id, created_by, import_type, source_format, status,
        source_name, source_hash, target_region_id, target_entry_id,
        target_content_node_id, dry_run, total_count, valid_count,
        error_count, warning_count, summary, raw_payload, normalized_payload, parser_metadata
      )
      values (
        $1, $2, $3, $4, 'preview',
        $5, $6, $7::uuid, $8::uuid,
        $9::uuid, true, $10, $11,
        $12, $13, $14::jsonb, $15::jsonb, $16::jsonb, $17::jsonb
      )
      returning id, status, total_count as "totalCount", valid_count as "validCount",
                error_count as "errorCount", warning_count as "warningCount"
      `,
      [
        auth.tenantId,
        auth.userId,
        importType,
        sourceFormat,
        sourceName,
        contentHash(normalizedItems.map(item => item.source)),
        target.regionId,
        target.entryId,
        target.contentNodeId,
        normalizedItems.length,
        validCount,
        errorCount,
        warningCount,
        JSON.stringify({ target, generatedAt: new Date().toISOString() }),
        JSON.stringify(normalizedItems.map(item => item.source)),
        JSON.stringify(normalizedItems.map(item => item.normalized).filter(Boolean)),
        JSON.stringify(objectValue(body.spreadsheet)),
      ],
    );

    const job = jobResult.rows[0];
    const responseItems: PreviewResult<T>['items'] = [];
    for (const item of normalizedItems) {
      const itemResult = await client.query(
        `
        insert into public.content_import_items (
          tenant_id, job_id, row_no, external_id, status, target_type,
          source_payload, normalized_payload, content_hash, issues_count
        )
        values ($1, $2, $3, $4, $5, $6, $7::jsonb, $8::jsonb, $9, $10)
        returning id
        `,
        [
          auth.tenantId,
          job.id,
          item.rowNo,
          item.externalId,
          item.status,
          targetType,
          JSON.stringify(item.source),
          JSON.stringify(item.normalized || {}),
          item.normalized && typeof item.normalized === 'object' && 'sourceHash' in item.normalized
            ? String((item.normalized as { sourceHash: string }).sourceHash)
            : contentHash(item.source),
          item.issues.length,
        ],
      );

      const itemId = itemResult.rows[0].id;
      for (const issue of item.issues) {
        await client.query(
          `
          insert into public.content_import_issues (
            tenant_id, job_id, item_id, row_no, severity, code,
            field_path, message, details
          )
          values ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb)
          `,
          [
            auth.tenantId,
            job.id,
            itemId,
            issue.rowNo,
            issue.severity,
            issue.code,
            issue.fieldPath,
            issue.message,
            JSON.stringify(issue.details || {}),
          ],
        );
      }

      responseItems.push({
        rowNo: item.rowNo,
        status: item.status,
        externalId: item.externalId,
        normalized: item.normalized,
        issues: item.issues,
      });
    }

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, $3, 'content_import_job', $4, $5::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        `content.import.${importType}.previewed`,
        job.id,
        JSON.stringify({ importType, total: normalizedItems.length, errorCount, warningCount }),
      ],
    );

    return { job, items: responseItems, issues };
  });
}

async function loadPreviewJob(client: pg.PoolClient, auth: TenantContentAuth, jobId: string) {
  const result = await client.query<{
    id: string;
    status: string;
    total_count: number;
    valid_count: number;
    error_count: number;
    warning_count: number;
    target_region_id: string | null;
    target_subject_id: string;
    target_category_id: string;
    target_node_id: string | null;
    target_question_bank_id: string | null;
    target_entry_id: string | null;
    target_content_node_id: string | null;
    target_collection_id: string | null;
  }>(
    `
    select id, status, total_count, valid_count, error_count, warning_count,
           target_region_id, target_subject_id, target_category_id,
           target_node_id, target_question_bank_id,
           target_entry_id, target_content_node_id, target_collection_id
    from public.content_import_jobs
    where tenant_id = $1 and id = $2 and import_type = 'questions'
    limit 1
    for update
    `,
    [auth.tenantId, jobId],
  );

  const job = result.rows[0];
  if (!job) {
    throw new HttpError(404, 'Import job not found', 'IMPORT_JOB_NOT_FOUND');
  }
  if (['importing', 'failed'].includes(job.status)) {
    throw new HttpError(409, `Import job is ${job.status}`, 'IMPORT_JOB_NOT_READY');
  }
  return job;
}

async function loadGenericPreviewJob(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  jobId: string,
  importType: ContentImportType,
) {
  const result = await client.query<{
    id: string;
    status: string;
    total_count: number;
    valid_count: number;
    error_count: number;
    warning_count: number;
    target_region_id: string | null;
    target_entry_id: string | null;
    target_content_node_id: string | null;
  }>(
    `
    select id, status, total_count, valid_count, error_count, warning_count,
           target_region_id, target_entry_id, target_content_node_id
    from public.content_import_jobs
    where tenant_id = $1 and id = $2 and import_type = $3
    limit 1
    for update
    `,
    [auth.tenantId, jobId, importType],
  );

  const job = result.rows[0];
  if (!job) throw new HttpError(404, 'Import job not found', 'IMPORT_JOB_NOT_FOUND');
  if (['importing', 'failed'].includes(job.status)) {
    throw new HttpError(409, `Import job is ${job.status}`, 'IMPORT_JOB_NOT_READY');
  }
  return job;
}

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

async function resolveScorelineSchool(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  normalized: Pick<NormalizedScorelineItem, 'schoolId' | 'schoolLegacyId' | 'schoolName' | 'regionId'>,
) {
  if (normalized.schoolId) {
    const byId = await client.query<{ id: string; name: string | null }>(
      'select id, name from public.scoreline_schools where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, normalized.schoolId],
    );
    if (!byId.rows[0]) throw new HttpError(400, 'schoolId is not in this tenant', 'SCORELINE_SCHOOL_NOT_FOUND');
    return byId.rows[0];
  }
  if (normalized.schoolLegacyId) {
    const byLegacy = await client.query<{ id: string; name: string | null }>(
      'select id, name from public.scoreline_schools where tenant_id = $1 and legacy_id = $2 limit 1',
      [auth.tenantId, normalized.schoolLegacyId],
    );
    if (byLegacy.rows[0]) return byLegacy.rows[0];
  }
  if (normalized.schoolName) {
    const byName = await client.query<{ id: string; name: string | null }>(
      `
      select id, name
      from public.scoreline_schools
      where tenant_id = $1
        and ($2::uuid is null or region_id = $2::uuid)
        and name = $3
      order by created_at asc
      limit 1
      `,
      [auth.tenantId, normalized.regionId, normalized.schoolName],
    );
    if (byName.rows[0]) return byName.rows[0];
  }
  throw new HttpError(400, 'scoreline school reference was not found', 'SCORELINE_SCHOOL_NOT_FOUND');
}

async function resolveScorelineMajor(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  normalized: NormalizedScorelineItem,
  schoolId: string | null,
) {
  if (normalized.majorId) {
    const byId = await client.query<{ id: string; name: string | null }>(
      'select id, name from public.scoreline_majors where tenant_id = $1 and id = $2 limit 1',
      [auth.tenantId, normalized.majorId],
    );
    if (!byId.rows[0]) throw new HttpError(400, 'majorId is not in this tenant', 'SCORELINE_MAJOR_NOT_FOUND');
    return byId.rows[0];
  }
  if (normalized.majorLegacyId) {
    const byLegacy = await client.query<{ id: string; name: string | null }>(
      'select id, name from public.scoreline_majors where tenant_id = $1 and legacy_id = $2 limit 1',
      [auth.tenantId, normalized.majorLegacyId],
    );
    if (byLegacy.rows[0]) return byLegacy.rows[0];
  }
  if (normalized.majorName) {
    const byName = await client.query<{ id: string; name: string | null }>(
      `
      select id, name
      from public.scoreline_majors
      where tenant_id = $1
        and ($2::uuid is null or school_id = $2::uuid)
        and name = $3
      order by created_at asc
      limit 1
      `,
      [auth.tenantId, schoolId, normalized.majorName],
    );
    if (byName.rows[0]) return byName.rows[0];
  }
  return null;
}

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

function nodeKeyFor(prefix: string, legacyId: string) {
  return `${prefix}:${legacyId}`.slice(0, 240);
}

async function upsertImportContentNode(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  params: {
    entryId: string | null;
    regionId: string | null;
    parentId: string | null;
    nodeKey: string;
    name: string;
    nodeType: 'category' | 'subject' | 'chapter' | 'custom';
    order: number;
    isActive: boolean;
    isLeaf: boolean;
    metadata: JsonObject;
  },
) {
  if (!params.entryId) return null;

  const existing = 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, params.entryId, params.nodeKey],
  );
  const nodeId = existing.rows[0]?.id || randomUUID();

  let parentPath: string | null = null;
  let parentDepth = -1;
  if (params.parentId) {
    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
      `,
      [auth.tenantId, params.entryId, params.parentId],
    );
    if (!parent.rows[0]) {
      throw new HttpError(400, 'parent content node is not in this entry', 'PARENT_NODE_NOT_FOUND');
    }
    parentPath = parent.rows[0].path;
    parentDepth = Number(parent.rows[0].depth || 0);
  }

  const path = parentPath ? `${parentPath}.${ltreeLabel(nodeId)}` : ltreeLabel(nodeId);
  const depth = parentDepth + 1;
  const node = await client.query<{ id: string }>(
    `
    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, metadata, created_by, updated_by
    )
    values (
      $1, $2, $3, $4::uuid, $5::uuid, null, $6,
      $7, $8, null, '{}'::jsonb, $9::ltree, $10, $11,
      $12, true, $13, $14::jsonb, $15, $15
    )
    on conflict (id)
    do update set region_id = excluded.region_id,
                  parent_id = excluded.parent_id,
                  node_key = excluded.node_key,
                  name = excluded.name,
                  node_type = excluded.node_type,
                  path = excluded.path,
                  depth = excluded.depth,
                  sort_order = excluded.sort_order,
                  is_active = excluded.is_active,
                  is_leaf = excluded.is_leaf,
                  metadata = excluded.metadata,
                  updated_by = excluded.updated_by,
                  updated_at = now()
    returning id
    `,
    [
      nodeId,
      auth.tenantId,
      params.entryId,
      params.regionId,
      params.parentId,
      params.nodeKey,
      params.name,
      params.nodeType,
      path,
      depth,
      params.order,
      params.isActive,
      params.isLeaf,
      JSON.stringify(params.metadata),
      auth.userId,
    ],
  );

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

  return node.rows[0].id;
}

async function currentVersionHash(client: pg.PoolClient, questionId: string) {
  const result = await client.query<{ source_hash: string | null }>(
    `
    select v.source_hash
    from public.questions q
    join public.question_versions v on v.id = q.current_version_id
    where q.id = $1
    limit 1
    `,
    [questionId],
  );
  return result.rows[0]?.source_hash || null;
}

async function importOneQuestion(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  job: {
    id: string;
    target_subject_id: string;
    target_category_id: string;
    target_node_id: string | null;
    target_question_bank_id: string | null;
    target_entry_id: string | null;
    target_content_node_id: string | null;
    target_collection_id: string | null;
  },
  item: {
    id: string;
    row_no: number;
    normalized_payload: NormalizedQuestion;
  },
) {
  const normalized = item.normalized_payload;
  const legacyId = normalized.legacyId || `content-import:${job.id}:${item.row_no}`;
  const existing = await client.query<{ id: string }>(
    'select id from public.questions where tenant_id = $1 and legacy_id = $2 limit 1 for update',
    [auth.tenantId, legacyId],
  );
  const existingQuestion = existing.rows[0];

  let questionId = existingQuestion?.id || '';
  if (!existingQuestion) {
    const inserted = await client.query<{ id: string }>(
      `
      insert into public.questions (
        tenant_id, question_bank_id, subject_id, category_id, node_id,
        entry_id, content_node_id, primary_collection_id,
        legacy_id, type, type_label, difficulty, tags, media_url, status,
        exam_markers
      )
      values (
        $1, $2::uuid, $3::uuid, $4::uuid, $5::uuid,
        $6::uuid, $7::uuid, $8::uuid,
        $9, $10, $11, $12, $13::jsonb, $14, 'published',
        $15::jsonb
      )
      returning id
      `,
      [
        auth.tenantId,
        job.target_question_bank_id,
        job.target_subject_id,
        job.target_category_id,
        job.target_node_id,
        job.target_entry_id,
        job.target_content_node_id,
        job.target_collection_id,
        legacyId,
        normalized.type,
        normalized.typeLabel,
        normalized.difficulty,
        JSON.stringify(normalized.tags),
        normalized.mediaUrl,
        JSON.stringify(normalized.examMarkers),
      ],
    );
    questionId = inserted.rows[0].id;
  } else {
    await client.query(
      `
      update public.questions
      set question_bank_id = $3::uuid,
          subject_id = $4::uuid,
          category_id = $5::uuid,
          node_id = $6::uuid,
          entry_id = $7::uuid,
          content_node_id = $8::uuid,
          primary_collection_id = $9::uuid,
          type = $10,
          type_label = $11,
          difficulty = $12,
          tags = $13::jsonb,
          media_url = $14,
          exam_markers = $15::jsonb,
          status = 'published',
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        auth.tenantId,
        questionId,
        job.target_question_bank_id,
        job.target_subject_id,
        job.target_category_id,
        job.target_node_id,
        job.target_entry_id,
        job.target_content_node_id,
        job.target_collection_id,
        normalized.type,
        normalized.typeLabel,
        normalized.difficulty,
        JSON.stringify(normalized.tags),
        normalized.mediaUrl,
        JSON.stringify(normalized.examMarkers),
      ],
    );
  }

  if (job.target_collection_id) {
    await client.query(
      `
      insert into public.question_collection_items (tenant_id, collection_id, question_id, sort_order)
      values (
        $1, $2, $3,
        coalesce((select max(sort_order) + 1 from public.question_collection_items where tenant_id = $1 and collection_id = $2), 0)
      )
      on conflict (tenant_id, collection_id, question_id) do nothing
      `,
      [auth.tenantId, job.target_collection_id, questionId],
    );
    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, job.target_collection_id],
    );
  }

  const previousHash = existingQuestion ? await currentVersionHash(client, questionId) : null;
  if (previousHash && previousHash === normalized.sourceHash) {
    await client.query(
      `
      update public.content_import_items
      set status = 'skipped', target_id = $3, updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [auth.tenantId, item.id, questionId],
    );
    return 'skipped' as const;
  }

  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 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,
      questionId,
      nextVersionNo,
      normalized.content,
      JSON.stringify(normalized.options),
      normalized.correctOptionIndex,
      JSON.stringify(normalized.correctOptionIndices),
      normalized.answerText,
      normalized.explanation,
      JSON.stringify(normalized.subQuestions),
      normalized.codeLang,
      normalized.codeTemplate,
      normalized.sourceHash,
      auth.userId,
    ],
  );

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

  const status = existingQuestion ? 'updated' : 'inserted';
  await client.query(
    `
    update public.content_import_items
    set status = $3, target_id = $4, updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [auth.tenantId, item.id, status, questionId],
  );

  return status as 'inserted' | 'updated';
}

async function importOneVocabularyUnit(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  job: {
    id: string;
    target_region_id: string | null;
    target_entry_id: string | null;
    target_content_node_id: string | null;
  },
  item: {
    id: string;
    row_no: number;
    normalized_payload: NormalizedVocabularyUnit;
  },
) {
  const normalized = item.normalized_payload;
  const unitLegacyId = normalized.legacyId || `content-import:${job.id}:unit:${item.row_no}`;
  const unitNodeId = await upsertImportContentNode(client, auth, {
    entryId: job.target_entry_id,
    regionId: job.target_region_id,
    parentId: job.target_content_node_id,
    nodeKey: nodeKeyFor('vocabulary-unit', unitLegacyId),
    name: normalized.name,
    nodeType: 'category',
    order: normalized.order,
    isActive: normalized.isActive,
    isLeaf: true,
    metadata: { source: 'content_import', importJobId: job.id, legacyId: unitLegacyId },
  });

  const existing = await client.query<{ id: string; source_hash: string | null }>(
    'select id, source_hash from public.vocabulary_units where tenant_id = $1 and legacy_id = $2 limit 1 for update',
    [auth.tenantId, unitLegacyId],
  );
  const existingUnit = existing.rows[0];
  const unit = await client.query<{ id: string }>(
    `
    insert into public.vocabulary_units (
      tenant_id, region_id, entry_id, content_node_id, legacy_id, name,
      description, word_count, sort_order, is_active, source_hash, metadata
    )
    values ($1, $2::uuid, $3::uuid, $4::uuid, $5, $6, $7, $8, $9, $10, $11, $12::jsonb)
    on conflict (tenant_id, legacy_id)
    do update set region_id = excluded.region_id,
                  entry_id = excluded.entry_id,
                  content_node_id = excluded.content_node_id,
                  name = excluded.name,
                  description = excluded.description,
                  word_count = excluded.word_count,
                  sort_order = excluded.sort_order,
                  is_active = excluded.is_active,
                  source_hash = excluded.source_hash,
                  metadata = excluded.metadata,
                  updated_at = now()
    returning id
    `,
    [
      auth.tenantId,
      job.target_region_id,
      job.target_entry_id,
      unitNodeId,
      unitLegacyId,
      normalized.name,
      normalized.description,
      normalized.wordCount ?? normalized.words.length,
      normalized.order,
      normalized.isActive,
      normalized.sourceHash,
      JSON.stringify({ ...normalized.metadata, source: 'content_import', importJobId: job.id }),
    ],
  );

  let insertedCount = existingUnit ? 0 : 1;
  let updatedCount = existingUnit && existingUnit.source_hash !== normalized.sourceHash ? 1 : 0;
  let skippedCount = existingUnit && existingUnit.source_hash === normalized.sourceHash ? 1 : 0;

  for (const [index, word] of normalized.words.entries()) {
    const wordLegacyId = word.legacyId || `${unitLegacyId}:word:${word.word.toLowerCase()}:${index + 1}`;
    const wordExisting = await client.query<{ id: string; source_hash: string | null }>(
      'select id, source_hash from public.vocabulary_words where tenant_id = $1 and legacy_id = $2 limit 1 for update',
      [auth.tenantId, wordLegacyId],
    );
    const existingWord = wordExisting.rows[0];
    await client.query(
      `
      insert into public.vocabulary_words (
        tenant_id, unit_id, entry_id, content_node_id, legacy_id, word,
        phonetic, meaning, example, example_translation, difficulty,
        tags, sort_order, is_active, source_hash, metadata
      )
      values (
        $1, $2, $3::uuid, $4::uuid, $5, $6,
        $7, $8, $9, $10, $11,
        $12::jsonb, $13, $14, $15, $16::jsonb
      )
      on conflict (tenant_id, legacy_id)
      do update set unit_id = excluded.unit_id,
                    entry_id = excluded.entry_id,
                    content_node_id = excluded.content_node_id,
                    word = excluded.word,
                    phonetic = excluded.phonetic,
                    meaning = excluded.meaning,
                    example = excluded.example,
                    example_translation = excluded.example_translation,
                    difficulty = excluded.difficulty,
                    tags = excluded.tags,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    source_hash = excluded.source_hash,
                    metadata = excluded.metadata,
                    updated_at = now()
      `,
      [
        auth.tenantId,
        unit.rows[0].id,
        job.target_entry_id,
        unitNodeId,
        wordLegacyId,
        word.word,
        word.phonetic,
        word.meaning,
        word.example,
        word.exampleTranslation,
        word.difficulty,
        JSON.stringify(word.tags),
        word.order,
        word.isActive,
        word.sourceHash,
        JSON.stringify({ ...word.metadata, source: 'content_import', importJobId: job.id }),
      ],
    );

    if (!existingWord) insertedCount += 1;
    else if (existingWord.source_hash === word.sourceHash) skippedCount += 1;
    else updatedCount += 1;
  }

  await client.query(
    `
    update public.vocabulary_units
    set word_count = (
          select count(*)
          from public.vocabulary_words
          where tenant_id = $1 and unit_id = $2 and is_active = true
        ),
        updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [auth.tenantId, unit.rows[0].id],
  );

  const itemStatus = insertedCount > 0 ? 'inserted' : updatedCount > 0 ? 'updated' : 'skipped';
  await client.query(
    `
    update public.content_import_items
    set status = $3, target_id = $4, updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [auth.tenantId, item.id, itemStatus, unit.rows[0].id],
  );

  return { insertedCount, updatedCount, skippedCount };
}

async function importOneHandbookSubject(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  job: {
    id: string;
    target_region_id: string | null;
    target_entry_id: string | null;
    target_content_node_id: string | null;
  },
  item: {
    id: string;
    row_no: number;
    normalized_payload: NormalizedHandbookSubject;
  },
) {
  const normalized = item.normalized_payload;
  const subjectLegacyId = normalized.legacyId || `content-import:${job.id}:handbook:${item.row_no}`;
  const subjectNodeId = await upsertImportContentNode(client, auth, {
    entryId: job.target_entry_id,
    regionId: job.target_region_id,
    parentId: job.target_content_node_id,
    nodeKey: nodeKeyFor('handbook-subject', subjectLegacyId),
    name: normalized.name,
    nodeType: 'subject',
    order: normalized.order,
    isActive: normalized.isActive,
    isLeaf: normalized.chapters.length === 0,
    metadata: { source: 'content_import', importJobId: job.id, legacyId: subjectLegacyId },
  });

  const existing = await client.query<{ id: string; source_hash: string | null }>(
    'select id, source_hash from public.handbook_subjects where tenant_id = $1 and legacy_id = $2 limit 1 for update',
    [auth.tenantId, subjectLegacyId],
  );
  const existingSubject = existing.rows[0];
  const subject = await client.query<{ id: string }>(
    `
    insert into public.handbook_subjects (
      tenant_id, region_id, entry_id, content_node_id, legacy_id, name,
      type, icon, color, description, sort_order, is_active, metadata, source_hash
    )
    values (
      $1, $2::uuid, $3::uuid, $4::uuid, $5, $6,
      $7, $8, $9, $10, $11, $12, $13::jsonb, $14
    )
    on conflict (tenant_id, legacy_id)
    do update set region_id = excluded.region_id,
                  entry_id = excluded.entry_id,
                  content_node_id = excluded.content_node_id,
                  name = excluded.name,
                  type = excluded.type,
                  icon = excluded.icon,
                  color = excluded.color,
                  description = excluded.description,
                  sort_order = excluded.sort_order,
                  is_active = excluded.is_active,
                  metadata = excluded.metadata,
                  source_hash = excluded.source_hash,
                  updated_at = now()
    returning id
    `,
    [
      auth.tenantId,
      job.target_region_id,
      job.target_entry_id,
      subjectNodeId,
      subjectLegacyId,
      normalized.name,
      normalized.type,
      normalized.icon,
      normalized.color,
      normalized.description,
      normalized.order,
      normalized.isActive,
      JSON.stringify({ ...normalized.metadata, source: 'content_import', importJobId: job.id }),
      normalized.sourceHash,
    ],
  );

  let insertedCount = existingSubject ? 0 : 1;
  let updatedCount = existingSubject && existingSubject.source_hash !== normalized.sourceHash ? 1 : 0;
  let skippedCount = existingSubject && existingSubject.source_hash === normalized.sourceHash ? 1 : 0;

  for (const [chapterIndex, chapter] of normalized.chapters.entries()) {
    const chapterLegacyId = chapter.legacyId || `${subjectLegacyId}:chapter:${chapterIndex + 1}`;
    const chapterNodeId = await upsertImportContentNode(client, auth, {
      entryId: job.target_entry_id,
      regionId: job.target_region_id,
      parentId: subjectNodeId,
      nodeKey: nodeKeyFor('handbook-chapter', chapterLegacyId),
      name: chapter.name,
      nodeType: 'chapter',
      order: chapter.order,
      isActive: chapter.isActive,
      isLeaf: chapter.sections.length === 0,
      metadata: { source: 'content_import', importJobId: job.id, legacyId: chapterLegacyId },
    });
    const chapterExisting = await client.query<{ id: string; source_hash: string | null }>(
      'select id, source_hash from public.handbook_chapters where tenant_id = $1 and legacy_id = $2 limit 1 for update',
      [auth.tenantId, chapterLegacyId],
    );
    const chapterRow = await client.query<{ id: string }>(
      `
      insert into public.handbook_chapters (
        tenant_id, subject_id, entry_id, content_node_id, legacy_id, name,
        description, sort_order, is_active, source_hash, metadata
      )
      values ($1, $2, $3::uuid, $4::uuid, $5, $6, $7, $8, $9, $10, $11::jsonb)
      on conflict (tenant_id, legacy_id)
      do update set subject_id = excluded.subject_id,
                    entry_id = excluded.entry_id,
                    content_node_id = excluded.content_node_id,
                    name = excluded.name,
                    description = excluded.description,
                    sort_order = excluded.sort_order,
                    is_active = excluded.is_active,
                    source_hash = excluded.source_hash,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id
      `,
      [
        auth.tenantId,
        subject.rows[0].id,
        job.target_entry_id,
        chapterNodeId,
        chapterLegacyId,
        chapter.name,
        chapter.description,
        chapter.order,
        chapter.isActive,
        chapter.sourceHash,
        JSON.stringify({ ...chapter.metadata, source: 'content_import', importJobId: job.id }),
      ],
    );
    if (!chapterExisting.rows[0]) insertedCount += 1;
    else if (chapterExisting.rows[0].source_hash === chapter.sourceHash) skippedCount += 1;
    else updatedCount += 1;

    for (const [sectionIndex, section] of chapter.sections.entries()) {
      const sectionLegacyId = section.legacyId || `${chapterLegacyId}:section:${sectionIndex + 1}`;
      const sectionNodeId = section.name
        ? await upsertImportContentNode(client, auth, {
          entryId: job.target_entry_id,
          regionId: job.target_region_id,
          parentId: chapterNodeId,
          nodeKey: nodeKeyFor('handbook-section', sectionLegacyId),
          name: section.name,
          nodeType: 'custom',
          order: section.order,
          isActive: section.isActive,
          isLeaf: true,
          metadata: {
            ...section.metadata,
            source: 'content_import',
            importJobId: job.id,
            legacyId: sectionLegacyId,
            kind: 'handbook_section',
            description: section.description,
          },
        })
        : chapterNodeId;

      for (const [entryIndex, entry] of section.entries.entries()) {
        const entryLegacyId = entry.legacyId || `${sectionLegacyId}:entry:${entryIndex + 1}`;
        const entryExisting = await client.query<{ id: string; source_hash: string | null }>(
          'select id, source_hash from public.handbook_entries where tenant_id = $1 and legacy_id = $2 limit 1 for update',
          [auth.tenantId, entryLegacyId],
        );
        await client.query(
          `
          insert into public.handbook_entries (
            tenant_id, chapter_id, entry_id, content_node_id, legacy_id, title,
            summary, content, tags, sort_order, is_active, source_hash, metadata
          )
          values (
            $1, $2, $3::uuid, $4::uuid, $5, $6,
            $7, $8, $9::jsonb, $10, $11, $12, $13::jsonb
          )
          on conflict (tenant_id, legacy_id)
          do update set chapter_id = excluded.chapter_id,
                        entry_id = excluded.entry_id,
                        content_node_id = excluded.content_node_id,
                        title = excluded.title,
                        summary = excluded.summary,
                        content = excluded.content,
                        tags = excluded.tags,
                        sort_order = excluded.sort_order,
                        is_active = excluded.is_active,
                        source_hash = excluded.source_hash,
                        metadata = excluded.metadata,
                        updated_at = now()
          `,
          [
            auth.tenantId,
            chapterRow.rows[0].id,
            job.target_entry_id,
            sectionNodeId,
            entryLegacyId,
            entry.title,
            entry.summary,
            entry.content,
            JSON.stringify(entry.tags),
            entry.order,
            entry.isActive,
            entry.sourceHash,
            JSON.stringify({
              ...entry.metadata,
              source: 'content_import',
              importJobId: job.id,
              sectionLegacyId,
              sectionName: section.name,
            }),
          ],
        );
        if (!entryExisting.rows[0]) insertedCount += 1;
        else if (entryExisting.rows[0].source_hash === entry.sourceHash) skippedCount += 1;
        else updatedCount += 1;
      }
    }
  }

  const itemStatus = insertedCount > 0 ? 'inserted' : updatedCount > 0 ? 'updated' : 'skipped';
  await client.query(
    `
    update public.content_import_items
    set status = $3, target_id = $4, updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [auth.tenantId, item.id, itemStatus, subject.rows[0].id],
  );

  return { insertedCount, updatedCount, skippedCount };
}

async function importOneScorelineItem(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  job: {
    id: string;
    target_region_id: string | null;
  },
  item: {
    id: string;
    row_no: number;
    normalized_payload: NormalizedScorelineItem;
  },
) {
  const normalized = item.normalized_payload;
  const regionId = normalized.regionId || job.target_region_id;
  if (regionId) await assertTenantReference(client, auth, 'regions', regionId, 'REGION_NOT_FOUND');
  let targetId = '';
  let insertedCount = 0;
  let updatedCount = 0;
  let skippedCount = 0;

  if (normalized.kind === 'field') {
    const legacyId = normalized.legacyId || `scoreline-field:${regionId || 'global'}:${normalized.fieldKey}`;
    const existing = await client.query<{ id: string; source_hash: string | null }>(
      'select id, source_hash from public.scoreline_fields where tenant_id = $1 and legacy_id = $2 limit 1 for update',
      [auth.tenantId, legacyId],
    );
    const previousHash = existing.rows[0]?.source_hash || null;
    const row = await client.query<{ id: string }>(
      `
      insert into public.scoreline_fields (
        tenant_id, region_id, legacy_id, field_key, field_name, field_type,
        unit, is_filter, is_required, is_visible, is_trend, options,
        placeholder, description, sort_order, source_hash, metadata
      )
      values ($1, $2::uuid, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12::jsonb, $13, $14, $15, $16, $17::jsonb)
      on conflict (tenant_id, region_id, field_key)
      do update set legacy_id = excluded.legacy_id,
                    field_name = excluded.field_name,
                    field_type = excluded.field_type,
                    unit = excluded.unit,
                    is_filter = excluded.is_filter,
                    is_required = excluded.is_required,
                    is_visible = excluded.is_visible,
                    is_trend = excluded.is_trend,
                    options = excluded.options,
                    placeholder = excluded.placeholder,
                    description = excluded.description,
                    sort_order = excluded.sort_order,
                    source_hash = excluded.source_hash,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id
      `,
      [
        auth.tenantId,
        regionId,
        legacyId,
        normalized.fieldKey,
        normalized.fieldName,
        normalized.fieldType || 'number',
        normalized.unit || null,
        normalized.isFilter === true,
        normalized.isRequired === true,
        normalized.isVisible !== false,
        normalized.isTrend === true,
        JSON.stringify(normalized.options || []),
        normalized.placeholder || null,
        normalized.description || null,
        normalized.order || 0,
        normalized.sourceHash,
        JSON.stringify({ ...normalized.metadata, source: 'content_import', importJobId: job.id }),
      ],
    );
    targetId = row.rows[0].id;
    if (!existing.rows[0]) insertedCount = 1;
    else if (previousHash === normalized.sourceHash) skippedCount = 1;
    else updatedCount = 1;
  } else if (normalized.kind === 'school') {
    const legacyId = normalized.legacyId || `scoreline-school:${regionId || 'global'}:${normalized.schoolName}`;
    const existing = await client.query<{ id: string; source_hash: string | null }>(
      'select id, source_hash from public.scoreline_schools where tenant_id = $1 and legacy_id = $2 limit 1 for update',
      [auth.tenantId, legacyId],
    );
    const previousHash = existing.rows[0]?.source_hash || null;
    const row = await client.query<{ id: string }>(
      `
      insert into public.scoreline_schools (
        tenant_id, region_id, legacy_id, name, short_name, type, is_hot, sort_order, source_hash, metadata
      )
      values ($1, $2::uuid, $3, $4, $5, $6, $7, $8, $9, $10::jsonb)
      on conflict (tenant_id, legacy_id)
      do update set region_id = excluded.region_id,
                    name = excluded.name,
                    short_name = excluded.short_name,
                    type = excluded.type,
                    is_hot = excluded.is_hot,
                    sort_order = excluded.sort_order,
                    source_hash = excluded.source_hash,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id
      `,
      [
        auth.tenantId,
        regionId,
        legacyId,
        normalized.schoolName,
        normalized.schoolShortName || null,
        normalized.schoolType || null,
        normalized.isHot === true,
        normalized.order || 0,
        normalized.sourceHash,
        JSON.stringify({ ...normalized.metadata, source: 'content_import', importJobId: job.id }),
      ],
    );
    targetId = row.rows[0].id;
    if (!existing.rows[0]) insertedCount = 1;
    else if (previousHash === normalized.sourceHash) skippedCount = 1;
    else updatedCount = 1;
  } else if (normalized.kind === 'major') {
    const school = await resolveScorelineSchool(client, auth, normalized);
    const legacyId = normalized.legacyId || `scoreline-major:${school.id}:${normalized.majorName}`;
    const existing = await client.query<{ id: string; source_hash: string | null }>(
      'select id, source_hash from public.scoreline_majors where tenant_id = $1 and legacy_id = $2 limit 1 for update',
      [auth.tenantId, legacyId],
    );
    const previousHash = existing.rows[0]?.source_hash || null;
    const row = await client.query<{ id: string }>(
      `
      insert into public.scoreline_majors (
        tenant_id, region_id, school_id, legacy_id, name, sort_order,
        has_restriction, restriction_desc, source_hash, metadata
      )
      values ($1, $2::uuid, $3, $4, $5, $6, $7, $8, $9, $10::jsonb)
      on conflict (tenant_id, legacy_id)
      do update set region_id = excluded.region_id,
                    school_id = excluded.school_id,
                    name = excluded.name,
                    sort_order = excluded.sort_order,
                    has_restriction = excluded.has_restriction,
                    restriction_desc = excluded.restriction_desc,
                    source_hash = excluded.source_hash,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id
      `,
      [
        auth.tenantId,
        regionId,
        school.id,
        legacyId,
        normalized.majorName,
        normalized.order || 0,
        normalized.hasRestriction === true,
        normalized.restrictionDesc || null,
        normalized.sourceHash,
        JSON.stringify({ ...normalized.metadata, source: 'content_import', importJobId: job.id }),
      ],
    );
    targetId = row.rows[0].id;
    if (!existing.rows[0]) insertedCount = 1;
    else if (previousHash === normalized.sourceHash) skippedCount = 1;
    else updatedCount = 1;
  } else {
    const school = await resolveScorelineSchool(client, auth, normalized);
    const major = await resolveScorelineMajor(client, auth, normalized, school.id);
    const legacyId = normalized.legacyId || `scoreline-record:${school.id}:${major?.id || 'none'}:${normalized.year}`;
    const existing = await client.query<{ id: string; source_hash: string | null }>(
      'select id, source_hash from public.scoreline_records where tenant_id = $1 and legacy_id = $2 limit 1 for update',
      [auth.tenantId, legacyId],
    );
    const previousHash = existing.rows[0]?.source_hash || null;
    const row = await client.query<{ id: string }>(
      `
      insert into public.scoreline_records (
        tenant_id, region_id, school_id, major_id, legacy_id,
        year, school_name, major_name, field_values, source_hash, metadata
      )
      values ($1, $2::uuid, $3::uuid, $4::uuid, $5, $6, $7, $8, $9::jsonb, $10, $11::jsonb)
      on conflict (tenant_id, legacy_id)
      do update set region_id = excluded.region_id,
                    school_id = excluded.school_id,
                    major_id = excluded.major_id,
                    year = excluded.year,
                    school_name = excluded.school_name,
                    major_name = excluded.major_name,
                    field_values = excluded.field_values,
                    source_hash = excluded.source_hash,
                    metadata = excluded.metadata,
                    updated_at = now()
      returning id
      `,
      [
        auth.tenantId,
        regionId,
        school.id,
        major?.id || null,
        legacyId,
        normalized.year,
        normalized.schoolName || school.name,
        normalized.majorName || major?.name || null,
        JSON.stringify(normalized.fieldValues || {}),
        normalized.sourceHash,
        JSON.stringify({ ...normalized.metadata, source: 'content_import', importJobId: job.id }),
      ],
    );
    targetId = row.rows[0].id;
    if (!existing.rows[0]) insertedCount = 1;
    else if (previousHash === normalized.sourceHash) skippedCount = 1;
    else updatedCount = 1;
  }

  const itemStatus = insertedCount > 0 ? 'inserted' : updatedCount > 0 ? 'updated' : 'skipped';
  await client.query(
    `
    update public.content_import_items
    set status = $3, target_id = $4, updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [auth.tenantId, item.id, itemStatus, targetId],
  );
  return { insertedCount, updatedCount, skippedCount };
}

async function importOneVideo(
  client: pg.PoolClient,
  auth: TenantContentAuth,
  job: {
    id: string;
  },
  item: {
    id: string;
    row_no: number;
    normalized_payload: NormalizedVideoImportItem;
  },
) {
  const normalized = item.normalized_payload;
  if (normalized.subjectId) await assertTenantReference(client, auth, 'subjects', normalized.subjectId, 'SUBJECT_NOT_FOUND');
  if (normalized.assetId) await assertTenantReference(client, auth, 'content_assets', normalized.assetId, 'ASSET_NOT_FOUND');
  const legacyId = normalized.legacyId || `content-import:${job.id}:video:${item.row_no}`;
  const existing = await client.query<{ id: string; source_hash: string | null }>(
    'select id, source_hash from public.video_explanations where tenant_id = $1 and legacy_id = $2 limit 1 for update',
    [auth.tenantId, legacyId],
  );
  const previousHash = existing.rows[0]?.source_hash || null;
  const video = await client.query<{ id: string }>(
    `
    insert into public.video_explanations (
      tenant_id, legacy_id, title, description, video_url, thumbnail_url,
      duration_seconds, knowledge_tags, is_general, subject_id, difficulty,
      sort_order, is_active, asset_id, access_mode, free_preview_seconds, source_hash, metadata
    )
    values (
      $1, $2, $3, $4, $5, $6,
      $7, $8::jsonb, $9, $10::uuid, $11,
      $12, $13, $14::uuid, $15, $16, $17, $18::jsonb
    )
    on conflict (tenant_id, legacy_id)
    do update set title = excluded.title,
                  description = excluded.description,
                  video_url = excluded.video_url,
                  thumbnail_url = excluded.thumbnail_url,
                  duration_seconds = excluded.duration_seconds,
                  knowledge_tags = excluded.knowledge_tags,
                  is_general = excluded.is_general,
                  subject_id = excluded.subject_id,
                  difficulty = excluded.difficulty,
                  sort_order = excluded.sort_order,
                  is_active = excluded.is_active,
                  asset_id = excluded.asset_id,
                  access_mode = excluded.access_mode,
                  free_preview_seconds = excluded.free_preview_seconds,
                  source_hash = excluded.source_hash,
                  metadata = excluded.metadata,
                  updated_at = now()
    returning id
    `,
    [
      auth.tenantId,
      legacyId,
      normalized.title,
      normalized.description,
      normalized.videoUrl,
      normalized.thumbnailUrl,
      normalized.duration,
      JSON.stringify(normalized.knowledgeTags),
      normalized.isGeneral,
      normalized.subjectId,
      normalized.difficulty,
      normalized.order,
      normalized.isActive,
      normalized.assetId,
      normalized.accessMode,
      normalized.freePreviewSeconds,
      normalized.sourceHash,
      JSON.stringify({ ...normalized.metadata, source: 'content_import', importJobId: job.id }),
    ],
  );

  let insertedCount = existing.rows[0] ? 0 : 1;
  let updatedCount = existing.rows[0] && previousHash !== normalized.sourceHash ? 1 : 0;
  let skippedCount = existing.rows[0] && previousHash === normalized.sourceHash ? 1 : 0;

  for (const [index, binding] of normalized.bindings.entries()) {
    let questionId = binding.questionId;
    if (!questionId && binding.legacyQuestionId) {
      const question = await client.query<{ id: string }>(
        'select id from public.questions where tenant_id = $1 and legacy_id = $2 limit 1',
        [auth.tenantId, binding.legacyQuestionId],
      );
      questionId = question.rows[0]?.id || null;
    }
    if (!questionId) {
      throw new HttpError(400, 'video binding question was not found', 'QUESTION_NOT_FOUND');
    }
    await assertTenantReference(client, auth, 'questions', questionId, 'QUESTION_NOT_FOUND');
    const bindingLegacyId = binding.legacyId || `${legacyId}:question:${questionId}:${index + 1}`;
    const existingBinding = await client.query<{ id: string }>(
      'select id from public.question_videos where tenant_id = $1 and legacy_id = $2 limit 1 for update',
      [auth.tenantId, bindingLegacyId],
    );
    await client.query(
      `
      insert into public.question_videos (
        tenant_id, question_id, video_id, legacy_id, legacy_question_id,
        legacy_video_id, video_type, sort_order, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb)
      on conflict (tenant_id, legacy_id)
      do update set question_id = excluded.question_id,
                    video_id = excluded.video_id,
                    legacy_question_id = excluded.legacy_question_id,
                    legacy_video_id = excluded.legacy_video_id,
                    video_type = excluded.video_type,
                    sort_order = excluded.sort_order,
                    metadata = excluded.metadata,
                    updated_at = now()
      `,
      [
        auth.tenantId,
        questionId,
        video.rows[0].id,
        bindingLegacyId,
        binding.legacyQuestionId,
        legacyId,
        binding.videoType,
        binding.order,
        JSON.stringify({ source: 'content_import', importJobId: job.id }),
      ],
    );
    await client.query('update public.questions set has_video_explanation = true, updated_at = now() where tenant_id = $1 and id = $2', [
      auth.tenantId,
      questionId,
    ]);
    if (!existingBinding.rows[0]) insertedCount += 1;
    else updatedCount += 1;
  }

  const itemStatus = insertedCount > 0 ? 'inserted' : updatedCount > 0 ? 'updated' : 'skipped';
  await client.query(
    `
    update public.content_import_items
    set status = $3, target_id = $4, updated_at = now()
    where tenant_id = $1 and id = $2
    `,
    [auth.tenantId, item.id, itemStatus, video.rows[0].id],
  );
  return { insertedCount, updatedCount, skippedCount };
}

interface ContentImportExecutionOptions {
  jobId: string;
  importType: ExecutableContentImportType;
  allowPartial?: boolean;
  allowQueuedJob?: boolean;
}

interface ContentImportExecutionResult {
  jobId: string;
  status: string;
  idempotent?: boolean;
  insertedCount: number;
  updatedCount: number;
  skippedCount: number;
  errorCount?: number;
  warningCount?: number;
}

async function executeQuestionsImportJob(
  auth: TenantContentAuth,
  input: Omit<ContentImportExecutionOptions, 'importType'>,
) {
  const allowPartial = input.allowPartial === true;
  return transaction(async client => {
    const job = await loadPreviewJob(client, auth, input.jobId);

    if (job.status === 'completed' || job.status === 'completed_with_errors') {
      return {
        jobId: job.id,
        status: job.status,
        idempotent: true,
        insertedCount: 0,
        updatedCount: 0,
        skippedCount: 0,
      };
    }
    if (job.status === 'pending' && input.allowQueuedJob !== true) {
      throw new HttpError(409, 'Import job is queued for async processing', 'IMPORT_JOB_QUEUED');
    }

    if (job.error_count > 0 && !allowPartial) {
      await client.query(
        `
        update public.content_import_jobs
        set status = 'rejected',
            error_message = 'Preview contains validation errors',
            locked_at = null,
            locked_by = null,
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [auth.tenantId, job.id],
      );
      throw new HttpError(409, 'Preview contains validation errors. Fix issues or set allowPartial=true.', 'IMPORT_HAS_ERRORS');
    }

    await client.query(
      `
      update public.content_import_jobs
      set status = 'importing', dry_run = false, started_at = coalesce(started_at, now()), updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [auth.tenantId, job.id],
    );

    const itemResult = await client.query<{
      id: string;
      row_no: number;
      normalized_payload: NormalizedQuestion;
    }>(
      `
      select id, row_no, normalized_payload
      from public.content_import_items
      where tenant_id = $1 and job_id = $2 and status = 'valid'
      order by row_no asc
      for update
      `,
      [auth.tenantId, job.id],
    );

    let insertedCount = 0;
    let updatedCount = 0;
    let skippedCount = 0;
    for (const item of itemResult.rows) {
      const status = await importOneQuestion(client, auth, job, item);
      if (status === 'inserted') insertedCount += 1;
      if (status === 'updated') updatedCount += 1;
      if (status === 'skipped') skippedCount += 1;
    }

    const finalStatus = job.error_count > 0 ? 'completed_with_errors' : 'completed';
    await client.query(
      `
      update public.content_import_jobs
      set status = $3,
          inserted_count = $4,
          updated_count = $5,
          skipped_count = $6,
          summary = coalesce(summary, '{}'::jsonb) || $7::jsonb,
          finished_at = now(),
          locked_at = null,
          locked_by = null,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        auth.tenantId,
        job.id,
        finalStatus,
        insertedCount,
        updatedCount,
        skippedCount,
        JSON.stringify({ insertedCount, updatedCount, skippedCount, importedAt: new Date().toISOString() }),
      ],
    );

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, 'content.import.questions.completed', 'content_import_job', $3, $4::jsonb)
      `,
      [auth.tenantId, auth.userId, job.id, JSON.stringify({ insertedCount, updatedCount, skippedCount, allowPartial })],
    );

    return {
      jobId: job.id,
      status: finalStatus,
      insertedCount,
      updatedCount,
      skippedCount,
      errorCount: job.error_count,
      warningCount: job.warning_count,
    };
  });
}

async function executeGenericImportJob<T>(
  auth: TenantContentAuth,
  input: Omit<ContentImportExecutionOptions, 'importType'> & { importType: ContentImportType },
  importOne: (
    client: pg.PoolClient,
    auth: TenantContentAuth,
    job: {
      id: string;
      target_region_id: string | null;
      target_entry_id: string | null;
      target_content_node_id: string | null;
    },
    item: {
      id: string;
      row_no: number;
      normalized_payload: T;
    },
  ) => Promise<{ insertedCount: number; updatedCount: number; skippedCount: number }>,
) {
  const allowPartial = input.allowPartial === true;
  return transaction(async client => {
    const job = await loadGenericPreviewJob(client, auth, input.jobId, input.importType);
    if (job.status === 'completed' || job.status === 'completed_with_errors') {
      return {
        jobId: job.id,
        status: job.status,
        idempotent: true,
        insertedCount: 0,
        updatedCount: 0,
        skippedCount: 0,
      };
    }
    if (job.status === 'pending' && input.allowQueuedJob !== true) {
      throw new HttpError(409, 'Import job is queued for async processing', 'IMPORT_JOB_QUEUED');
    }

    if (job.error_count > 0 && !allowPartial) {
      await client.query(
        `
        update public.content_import_jobs
        set status = 'rejected',
            error_message = 'Preview contains validation errors',
            locked_at = null,
            locked_by = null,
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [auth.tenantId, job.id],
      );
      throw new HttpError(409, 'Preview contains validation errors. Fix issues or set allowPartial=true.', 'IMPORT_HAS_ERRORS');
    }

    await client.query(
      `
      update public.content_import_jobs
      set status = 'importing', dry_run = false, started_at = coalesce(started_at, now()), updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [auth.tenantId, job.id],
    );

    const itemResult = await client.query<{
      id: string;
      row_no: number;
      normalized_payload: T;
    }>(
      `
      select id, row_no, normalized_payload
      from public.content_import_items
      where tenant_id = $1 and job_id = $2 and status = 'valid'
      order by
        case
          when $3 = 'scoreline' then
            case normalized_payload ->> 'kind'
              when 'field' then 1
              when 'school' then 2
              when 'major' then 3
              when 'record' then 4
              else 9
            end
          else 1
        end,
        row_no asc
      for update
      `,
      [auth.tenantId, job.id, input.importType],
    );

    let insertedCount = 0;
    let updatedCount = 0;
    let skippedCount = 0;
    for (const item of itemResult.rows) {
      const status = await importOne(client, auth, job, item);
      insertedCount += status.insertedCount;
      updatedCount += status.updatedCount;
      skippedCount += status.skippedCount;
    }

    const finalStatus = job.error_count > 0 ? 'completed_with_errors' : 'completed';
    await client.query(
      `
      update public.content_import_jobs
      set status = $3,
          inserted_count = $4,
          updated_count = $5,
          skipped_count = $6,
          summary = coalesce(summary, '{}'::jsonb) || $7::jsonb,
          finished_at = now(),
          locked_at = null,
          locked_by = null,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        auth.tenantId,
        job.id,
        finalStatus,
        insertedCount,
        updatedCount,
        skippedCount,
        JSON.stringify({ insertedCount, updatedCount, skippedCount, importedAt: new Date().toISOString() }),
      ],
    );

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, $3, 'content_import_job', $4, $5::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        `content.import.${input.importType}.completed`,
        job.id,
        JSON.stringify({ insertedCount, updatedCount, skippedCount, allowPartial }),
      ],
    );

    return {
      jobId: job.id,
      status: finalStatus,
      insertedCount,
      updatedCount,
      skippedCount,
      errorCount: job.error_count,
      warningCount: job.warning_count,
    };
  });
}

export async function executeContentImportJob(
  auth: TenantContentAuth,
  input: ContentImportExecutionOptions,
): Promise<ContentImportExecutionResult> {
  if (input.importType === 'questions') {
    return executeQuestionsImportJob(auth, input);
  }
  if (input.importType === 'vocabulary') {
    return executeGenericImportJob(auth, { ...input, importType: 'vocabulary' }, importOneVocabularyUnit);
  }
  if (input.importType === 'handbook') {
    return executeGenericImportJob(auth, { ...input, importType: 'handbook' }, importOneHandbookSubject);
  }
  if (input.importType === 'scoreline') {
    return executeGenericImportJob(auth, { ...input, importType: 'scoreline' }, importOneScorelineItem);
  }
  if (input.importType === 'videos') {
    return executeGenericImportJob(auth, { ...input, importType: 'videos' }, importOneVideo);
  }
  throw new HttpError(400, 'Unsupported import type', 'IMPORT_TYPE_NOT_SUPPORTED');
}

async function queueContentImportJob(
  auth: TenantContentAuth,
  input: ContentImportExecutionOptions,
) {
  const allowPartial = input.allowPartial === true;
  return transaction(async client => {
    const result = await client.query<{
      id: string;
      status: string;
      error_count: number;
      warning_count: number;
      attempt_count: number;
      max_attempts: number;
    }>(
      `
      select id, status, error_count, warning_count, attempt_count, max_attempts
      from public.content_import_jobs
      where tenant_id = $1 and id = $2 and import_type = $3
      limit 1
      for update
      `,
      [auth.tenantId, input.jobId, input.importType],
    );
    const job = result.rows[0];
    if (!job) throw new HttpError(404, 'Import job not found', 'IMPORT_JOB_NOT_FOUND');
    if (job.status === 'completed' || job.status === 'completed_with_errors') {
      return {
        jobId: job.id,
        status: job.status,
        idempotent: true,
        executionMode: 'async',
        errorCount: job.error_count,
        warningCount: job.warning_count,
      };
    }
    if (job.status === 'pending') {
      return {
        jobId: job.id,
        status: job.status,
        idempotent: true,
        executionMode: 'async',
        errorCount: job.error_count,
        warningCount: job.warning_count,
      };
    }
    if (job.status === 'importing') {
      throw new HttpError(409, 'Import job is already importing', 'IMPORT_JOB_NOT_READY');
    }
    if (['failed', 'rejected'].includes(job.status)) {
      throw new HttpError(409, `Import job is ${job.status}`, 'IMPORT_JOB_NOT_READY');
    }
    if (job.error_count > 0 && !allowPartial) {
      await client.query(
        `
        update public.content_import_jobs
        set status = 'rejected',
            error_message = 'Preview contains validation errors',
            updated_at = now()
        where tenant_id = $1 and id = $2
        `,
        [auth.tenantId, job.id],
      );
      throw new HttpError(409, 'Preview contains validation errors. Fix issues or set allowPartial=true.', 'IMPORT_HAS_ERRORS');
    }

    const queuedAt = new Date().toISOString();
    await client.query(
      `
      update public.content_import_jobs
      set execution_mode = 'async',
          status = 'pending',
          dry_run = false,
          queued_at = coalesce(queued_at, now()),
          locked_at = null,
          locked_by = null,
          next_attempt_at = now(),
          summary = coalesce(summary, '{}'::jsonb) || $3::jsonb,
          updated_at = now()
      where tenant_id = $1 and id = $2
      `,
      [
        auth.tenantId,
        job.id,
        JSON.stringify({
          importOptions: { allowPartial },
          queuedAt,
        }),
      ],
    );

    await client.query(
      `
      insert into public.audit_logs (tenant_id, actor_user_id, action, target_type, target_id, details)
      values ($1, $2, $3, 'content_import_job', $4, $5::jsonb)
      `,
      [
        auth.tenantId,
        auth.userId,
        `content.import.${input.importType}.queued`,
        job.id,
        JSON.stringify({ allowPartial, executionMode: 'async', queuedAt }),
      ],
    );

    return {
      jobId: job.id,
      status: 'pending',
      executionMode: 'async',
      errorCount: job.error_count,
      warningCount: job.warning_count,
    };
  });
}

async function runGenericImport<T>(
  auth: TenantContentAuth,
  body: JsonObject,
  importType: ContentImportType,
  createPreview: () => Promise<PreviewResult<T>>,
  importOne: (
    client: pg.PoolClient,
    auth: TenantContentAuth,
    job: {
      id: string;
      target_region_id: string | null;
      target_entry_id: string | null;
      target_content_node_id: string | null;
    },
    item: {
      id: string;
      row_no: number;
      normalized_payload: T;
    },
  ) => Promise<{ insertedCount: number; updatedCount: number; skippedCount: number }>,
) {
  const allowPartial = boolValue(body.allowPartial, false);
  const jobId = nullableString(body.previewJobId) || nullableString(body.jobId);
  const createdPreview = jobId ? null : await createPreview();
  const finalJobId = jobId || createdPreview?.job.id || '';
  const result = isAsyncExecution(body.executionMode ?? body.execution_mode)
    ? await queueContentImportJob(auth, { jobId: finalJobId, importType, allowPartial })
    : await executeGenericImportJob(auth, { jobId: finalJobId, importType, allowPartial }, importOne);

  return { item: result, preview: createdPreview };
}

export async function previewQuestionsImportRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'questions');
  return createQuestionPreviewJob(auth, body);
}

export async function previewVocabularyImportRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'vocabulary');
  return createGenericPreviewJob(auth, body, 'vocabulary', 'vocabulary_unit', createVocabularyNormalizedItems(body));
}

export async function previewHandbookImportRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'handbook');
  return createGenericPreviewJob(auth, body, 'handbook', 'handbook_subject', createHandbookNormalizedItems(body));
}

export async function previewScorelineImportRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'scoreline');
  return createGenericPreviewJob(auth, body, 'scoreline', 'scoreline_item', createScorelineNormalizedItems(body), null);
}

export async function previewVideosImportRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'videos');
  return createGenericPreviewJob(auth, body, 'videos', 'video_explanation', createVideoNormalizedItems(body), null);
}

export async function importQuestionsRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'questions');
  const allowPartial = boolValue(body.allowPartial, false);
  const jobId = nullableString(body.previewJobId) || nullableString(body.jobId);

  const createdPreview = jobId ? null : await createQuestionPreviewJob(auth, body);
  const finalJobId = jobId || createdPreview?.job.id || '';

  const result = isAsyncExecution(body.executionMode ?? body.execution_mode)
    ? await queueContentImportJob(auth, { jobId: finalJobId, importType: 'questions', allowPartial })
    : await executeContentImportJob(auth, { jobId: finalJobId, importType: 'questions', allowPartial });

  return { item: result, preview: createdPreview };
}

export async function importVocabularyRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'vocabulary');
  return runGenericImport(
    auth,
    body,
    'vocabulary',
    () => createGenericPreviewJob(auth, body, 'vocabulary', 'vocabulary_unit', createVocabularyNormalizedItems(body)),
    importOneVocabularyUnit,
  );
}

export async function importHandbookRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'handbook');
  return runGenericImport(
    auth,
    body,
    'handbook',
    () => createGenericPreviewJob(auth, body, 'handbook', 'handbook_subject', createHandbookNormalizedItems(body)),
    importOneHandbookSubject,
  );
}

export async function importScorelineRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'scoreline');
  return runGenericImport(
    auth,
    body,
    'scoreline',
    () => createGenericPreviewJob(auth, body, 'scoreline', 'scoreline_item', createScorelineNormalizedItems(body), null),
    importOneScorelineItem,
  );
}

export async function importVideosRoute(ctx: RequestContext) {
  const { config, expandSpreadsheetImportBody, readJsonBody, requireTenantContentEditor } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const rawBody = await readJsonBody(ctx, { maxBytes: config.maxImportJsonBodyBytes });
  const body = await expandSpreadsheetImportBody(rawBody, 'videos');
  return runGenericImport(
    auth,
    body,
    'videos',
    () => createGenericPreviewJob(auth, body, 'videos', 'video_explanation', createVideoNormalizedItems(body), null),
    importOneVideo,
  );
}

const importJobSelect = `
  id, import_type as "importType", source_format as "sourceFormat",
  status, source_name as "sourceName", source_hash as "sourceHash",
  target_region_id as "targetRegionId", target_subject_id as "targetSubjectId",
  target_category_id as "targetCategoryId", target_node_id as "targetNodeId",
  target_question_bank_id as "targetQuestionBankId",
  target_entry_id as "targetEntryId", target_content_node_id as "targetContentNodeId",
  target_collection_id as "targetCollectionId",
  dry_run as "dryRun", total_count as "totalCount",
  valid_count as "validCount", error_count as "errorCount",
  warning_count as "warningCount", inserted_count as "insertedCount",
  updated_count as "updatedCount", skipped_count as "skippedCount",
  execution_mode as "executionMode", queued_at as "queuedAt",
  locked_at as "lockedAt", locked_by as "lockedBy",
  attempt_count as "attemptCount", max_attempts as "maxAttempts",
  next_attempt_at as "nextAttemptAt", parser_metadata as "parserMetadata",
  summary, error_message as "errorMessage",
  started_at as "startedAt", finished_at as "finishedAt",
  created_by as "createdBy", created_at as "createdAt", updated_at as "updatedAt"
`;

export async function importJobsRoute(ctx: RequestContext) {
  const { intParam, requireTenantContentEditor, stringParam } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const importType = stringParam(ctx, 'importType');
  const status = stringParam(ctx, 'status');

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

  const items = await query(
    `
    select ${importJobSelect}
    from public.content_import_jobs
    where ${filters.join(' and ')}
    order by created_at desc
    limit $${params.length}
    `,
    params,
  );

  return { items };
}

export async function importJobDetailRoute(ctx: RequestContext) {
  const { intParam, requireTenantContentEditor, requiredString, stringParam } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const jobId = requiredString({ jobId: stringParam(ctx, 'jobId') }, 'jobId');
  const issueLimit = intParam(ctx, 'issueLimit', 20, 200);

  const item = await queryOne<Record<string, unknown>>(
    `
    select ${importJobSelect}
    from public.content_import_jobs
    where tenant_id = $1 and id = $2
    limit 1
    `,
    [auth.tenantId, jobId],
  );
  if (!item) {
    throw new HttpError(404, 'Import job not found', 'IMPORT_JOB_NOT_FOUND');
  }

  const issueSummary = await query<{ severity: string; count: string }>(
    `
    select severity, count(*)::text as count
    from public.content_import_issues
    where tenant_id = $1 and job_id = $2
    group by severity
    order by case severity when 'error' then 1 when 'warning' then 2 else 9 end
    `,
    [auth.tenantId, jobId],
  );
  const itemStatusSummary = await query<{ status: string; count: string }>(
    `
    select status, count(*)::text as count
    from public.content_import_items
    where tenant_id = $1 and job_id = $2
    group by status
    order by status asc
    `,
    [auth.tenantId, jobId],
  );
  const recentIssues = await query(
    `
    select i.id, i.row_no as "rowNo", i.severity, i.code,
           i.field_path as "fieldPath", i.message, i.details,
           item.external_id as "externalId", item.status as "itemStatus",
           i.created_at as "createdAt"
    from public.content_import_issues i
    left join public.content_import_items item on item.id = i.item_id
    where i.tenant_id = $1 and i.job_id = $2
    order by i.row_no asc nulls last, case i.severity when 'error' then 1 else 2 end, i.created_at asc
    limit $3
    `,
    [auth.tenantId, jobId, issueLimit],
  );

  const summary = objectValue(item.summary);
  return {
    item,
    issueSummary: Object.fromEntries(issueSummary.map(row => [row.severity, Number(row.count || 0)])),
    itemStatusSummary: Object.fromEntries(itemStatusSummary.map(row => [row.status, Number(row.count || 0)])),
    recentIssues,
    importPostCheck: summary.importPostCheck || null,
    worker: {
      executionMode: item.executionMode,
      queuedAt: item.queuedAt,
      lockedAt: item.lockedAt,
      lockedBy: item.lockedBy,
      attemptCount: item.attemptCount,
      maxAttempts: item.maxAttempts,
      nextAttemptAt: item.nextAttemptAt,
      lastWorkerError: objectValue(summary.lastWorkerError),
    },
  };
}

export async function importIssuesRoute(ctx: RequestContext) {
  const { intParam, requireTenantContentEditor, requiredString, stringParam } = await routeDeps();
  const auth = await requireTenantContentEditor(ctx);
  const jobId = requiredString({ jobId: stringParam(ctx, 'jobId') }, 'jobId');
  const limit = intParam(ctx, 'limit', 500, 2000);

  const job = await queryOne<{ id: string }>(
    'select id from public.content_import_jobs where tenant_id = $1 and id = $2 limit 1',
    [auth.tenantId, jobId],
  );
  if (!job) {
    throw new HttpError(404, 'Import job not found', 'IMPORT_JOB_NOT_FOUND');
  }

  const items = await query(
    `
    select i.id, i.row_no as "rowNo", i.severity, i.code,
           i.field_path as "fieldPath", i.message, i.details,
           item.external_id as "externalId", item.status as "itemStatus",
           i.created_at as "createdAt"
    from public.content_import_issues i
    left join public.content_import_items item on item.id = i.item_id
    where i.tenant_id = $1 and i.job_id = $2
    order by i.row_no asc nulls last, case i.severity when 'error' then 1 else 2 end, i.created_at asc
    limit $3
    `,
    [auth.tenantId, jobId, limit],
  );

  return { items };
}
