import type pg from 'pg';
import { HttpError, type RequestContext } from '../../core/http.js';
import {
  intParam,
  optionalInteger,
  optionalString,
  optionalStringArray,
  readJsonBody,
  requiredString,
  stringParam,
  tenantIdFrom,
  userIdFrom,
} from '../../core/request.js';
import { query, queryOne, transaction } from '../../core/db.js';
import { assertAnswerSessionAccess, authorizePracticeSession, recordPracticeAccessEvent } from './access.js';
import { autoGrantBadges, type AutoBadgeGrant } from '../profile/badges.js';

interface QuestionAnswerRow {
  question_id: string;
  question_version_id: string | null;
  type: string | null;
  correct_option_index: number | null;
  correct_option_indices: unknown;
  answer_text: string | null;
  sub_questions: unknown;
}

interface PracticeBlueprintRow {
  id: string;
  entry_id: string | null;
  node_id: string | null;
  collection_id: string | null;
  mode: string;
  assembly_type: string;
  question_limit: number | null;
  duration_minutes: number | null;
  total_score: string | number | null;
  sections: unknown;
  rules: Record<string, unknown>;
}

interface PracticeSessionReportSessionRow {
  id: string;
  tenantId: string;
  userId: string;
  mode: string;
  blueprintId: string | null;
  collectionId: string | null;
  entryId: string | null;
  contentNodeId: string | null;
  questionIds: unknown;
  questionCount: number;
  durationMinutes: number | null;
  totalScore: string | number | null;
  startedAt: string;
  finishedAt: string | null;
  metadata: Record<string, unknown>;
}

interface PracticeSessionDetailRow extends PracticeSessionReportSessionRow {
  targetType: string | null;
  targetId: string | null;
  accessMode: string | null;
  accessEntitlementId: string | null;
  consumedFreeQuota: number;
  accessSnapshot: unknown;
  expiresAt: string | null;
}

interface PracticeSessionReportRow {
  id: string;
  tenantId: string;
  userId: string;
  practiceSessionId: string;
  blueprintId: string | null;
  collectionId: string | null;
  mode: string;
  totalQuestions: number;
  answeredCount: number;
  correctCount: number;
  wrongCount: number;
  unansweredCount: number;
  score: string | number;
  totalScore: string | number;
  accuracy: string | number;
  durationSeconds: number;
  startedAt: string | null;
  submittedAt: string;
  sectionStats: unknown;
  questionResults: unknown;
  wrongQuestionIds: unknown;
  metadata: Record<string, unknown>;
  createdAt: string;
  updatedAt: string;
}

interface PracticeSessionReportWithBadges {
  report: PracticeSessionReportRow;
  autoBadges: AutoBadgeGrant[];
}

interface PracticeSessionReportAnswerRow {
  questionId: string;
  selectedOptions: unknown;
  answerText: string | null;
  answerPayload: unknown;
  isCorrect: boolean | null;
  answeredAt: string;
}

interface PracticeSessionReportQuestionRow {
  questionId: string;
  type: string;
  typeLabel: string | null;
  collectionId: string | null;
  sectionKey: string | null;
  sortOrder: number | null;
  itemScore: string | number | null;
  content: string | null;
  explanation: string | null;
  correctOptionIndex: number | null;
  correctOptionIndices: unknown;
  answerText: string | null;
  subQuestions: unknown;
}

interface CompositeSubAnswer {
  subQuestionId: string;
  selectedOptions: string[];
  answerText: string;
  selfJudgedCorrect?: boolean;
}

interface CompositeSubQuestion {
  id: string;
  type: string;
  typeLabel: string | null;
  content: string | null;
  options: unknown[];
  correctOptionIndex: number | null;
  correctOptionIndices: number[];
  answerText: string | null;
  explanation: string | null;
  score: number | null;
}

interface CompositeSubResult {
  subQuestionId: string;
  order: number;
  type: string;
  typeLabel: string | null;
  content: string | null;
  selectedOptions: string[];
  answerText: string | null;
  isCorrect: boolean | null;
  selfJudged: boolean;
  correctOptionIndex: number | null;
  correctOptionIndices: number[];
  correctAnswerText: string | null;
  explanation: string | null;
  answered: boolean;
  score: number;
  totalScore: number;
}

interface CompositeAnswerEvaluation {
  mode: 'composite';
  subResults: CompositeSubResult[];
  answeredCount: number;
  correctCount: number;
  wrongCount: number;
  unansweredCount: number;
  isCorrect: boolean | null;
}

interface PracticeReportQuestionResult {
  questionId: string;
  questionType: string;
  sectionKey: string;
  score: number;
  totalScore: number;
  answered: boolean;
  isCorrect: boolean | null;
  selectedOptions: string[];
  answerText: string | null;
  answeredAt: string | null;
  correctOptionIndex: number | null;
  correctOptionIndices: number[];
  correctAnswerText: string | null;
  explanation: string | null;
  content: string | null;
  subResults?: CompositeSubResult[];
}

interface SectionDefinition {
  key: string;
  title: string | null;
  questionType: string | null;
  scoreEach: number | null;
  sortOrder: number;
}

interface SectionStat {
  key: string;
  title: string | null;
  questionType: string | null;
  questionCount: number;
  answeredCount: number;
  correctCount: number;
  wrongCount: number;
  unansweredCount: number;
  score: number;
  totalScore: number;
  accuracy: number;
  sortOrder: number;
}

interface WordProgressReviewRow {
  id: string;
  wordId: string;
  status: string;
  correctCount: number;
  wrongCount: number;
  reviewCount: number;
  correctStreak: number;
  easeFactor: string | number;
  nextReviewDate: string | null;
}

interface PracticeAssembly {
  mode: string;
  targetType: string | null;
  targetId: string | null;
  blueprintId: string | null;
  collectionId: string | null;
  entryId: string | null;
  contentNodeId: string | null;
  questionLimit: number;
  durationMinutes: number | null;
  totalScore: number | null;
  sections: unknown[];
  rules: Record<string, unknown>;
}

type TrendMetric = {
  date: string;
  answeredCount: number;
  correctCount: number;
  wrongCount: number;
  accuracy: number;
  sessionCount: number;
  reportCount: number;
  score: number;
  totalScore: number;
};

function normalizeStringArray(value: unknown): string[] {
  if (!Array.isArray(value)) return [];
  return value.map(item => String(item)).filter(item => item !== '');
}

function normalizeNumberArray(value: unknown): number[] {
  if (!Array.isArray(value)) return [];
  return value
    .map(item => Number(item))
    .filter(item => Number.isFinite(item))
    .map(item => Math.trunc(item))
    .sort((a, b) => a - b);
}

function normalizeAnswerText(value: unknown) {
  return String(value ?? '').trim().replace(/\s+/g, ' ');
}

function arraysEqual<T>(left: T[], right: T[]) {
  return left.length === right.length && left.every((value, index) => value === right[index]);
}

function isObjectiveType(type: string | null | undefined) {
  const normalized = String(type || '').toLowerCase();
  return ['choice', 'multi', 'judge', 'image'].includes(normalized);
}

function normalizeSelectedOptions(value: unknown) {
  return normalizeStringArray(value)
    .map(item => String(Number(item)))
    .filter(item => item !== 'NaN');
}

function subQuestionIdAt(index: number, value: Record<string, unknown>) {
  const raw = value.id ?? value.subQuestionId ?? value.key;
  return typeof raw === 'string' && raw.trim() ? raw.trim() : `sub_${index + 1}`;
}

function normalizeCompositeSubQuestions(value: unknown): CompositeSubQuestion[] {
  return jsonArray(value).map((raw, index) => {
    const source = jsonObject(raw);
    const correctOptionIndices = normalizeNumberArray(source.correctOptionIndices ?? source.correct_option_indices);
    const correctOptionIndex = source.correctOptionIndex ?? source.correct_option_index;
    const numericCorrectOptionIndex = correctOptionIndex === null || correctOptionIndex === undefined
      ? null
      : Number(correctOptionIndex);
    const score = source.score === undefined || source.score === null ? null : finiteNumber(source.score, 0);
    return {
      id: subQuestionIdAt(index, source),
      type: typeof source.type === 'string' && source.type.trim() ? source.type.trim() : 'choice',
      typeLabel: typeof source.typeLabel === 'string'
        ? source.typeLabel
        : typeof source.type_label === 'string'
          ? source.type_label
          : null,
      content: typeof source.content === 'string' ? source.content : null,
      options: jsonArray(source.options),
      correctOptionIndex: typeof numericCorrectOptionIndex === 'number' && Number.isFinite(numericCorrectOptionIndex)
        ? Math.trunc(numericCorrectOptionIndex)
        : null,
      correctOptionIndices,
      answerText: typeof source.answerText === 'string'
        ? source.answerText
        : typeof source.answer_text === 'string'
          ? source.answer_text
          : null,
      explanation: typeof source.explanation === 'string' ? source.explanation : null,
      score: score === null ? null : round2(score),
    };
  });
}

function parseSubAnswers(value: unknown): CompositeSubAnswer[] {
  if (value === undefined || value === null) return [];
  if (!Array.isArray(value)) {
    throw new HttpError(400, 'subAnswers must be an array', 'INVALID_SUB_ANSWERS');
  }
  return value.map((raw, index) => {
    const source = jsonObject(raw);
    const subQuestionId = source.subQuestionId ?? source.id ?? source.key;
    if (typeof subQuestionId !== 'string' || !subQuestionId.trim()) {
      throw new HttpError(400, `subAnswers[${index}].subQuestionId is required`, 'SUB_ANSWER_ID_REQUIRED');
    }
    const selfJudgedCorrect = source.selfJudgedCorrect;
    if (selfJudgedCorrect !== undefined && selfJudgedCorrect !== null && typeof selfJudgedCorrect !== 'boolean') {
      throw new HttpError(400, `subAnswers[${index}].selfJudgedCorrect must be boolean`, 'INVALID_BOOLEAN_FIELD');
    }
    return {
      subQuestionId: subQuestionId.trim(),
      selectedOptions: normalizeSelectedOptions(source.selectedOptions),
      answerText: typeof source.answerText === 'string' ? source.answerText : '',
      selfJudgedCorrect: typeof selfJudgedCorrect === 'boolean' ? selfJudgedCorrect : undefined,
    };
  });
}

function judgeObjectiveAnswer(selectedOptions: string[], correctOptionIndices: number[], correctOptionIndex: number | null) {
  if (correctOptionIndices.length) {
    return arraysEqual(
      selectedOptions.map(item => Number(item)).filter(Number.isFinite).map(item => Math.trunc(item)).sort((a, b) => a - b),
      correctOptionIndices,
    );
  }

  if (correctOptionIndex !== null && correctOptionIndex !== undefined) {
    return selectedOptions.length === 1 && Number(selectedOptions[0]) === correctOptionIndex;
  }

  return null;
}

function judgeAnswer(row: QuestionAnswerRow, selectedOptions: string[], answerText: string) {
  const correctIndices = normalizeNumberArray(row.correct_option_indices);
  if (correctIndices.length) {
    return judgeObjectiveAnswer(selectedOptions, correctIndices, null);
  }

  if (row.correct_option_index !== null && row.correct_option_index !== undefined) {
    return judgeObjectiveAnswer(selectedOptions, [], row.correct_option_index);
  }

  if (row.answer_text) {
    return normalizeAnswerText(answerText) === normalizeAnswerText(row.answer_text);
  }

  return null;
}

function hasObjectiveAnswer(row: QuestionAnswerRow) {
  return normalizeNumberArray(row.correct_option_indices).length > 0 ||
    (row.correct_option_index !== null && row.correct_option_index !== undefined);
}

function hasSubObjectiveAnswer(row: CompositeSubQuestion) {
  return row.correctOptionIndices.length > 0 ||
    (row.correctOptionIndex !== null && row.correctOptionIndex !== undefined);
}

function evaluateCompositeAnswer(row: QuestionAnswerRow, subAnswers: CompositeSubAnswer[]): CompositeAnswerEvaluation {
  const subQuestions = normalizeCompositeSubQuestions(row.sub_questions);
  if (!subQuestions.length) {
    throw new HttpError(400, 'Question does not contain sub questions', 'COMPOSITE_QUESTION_REQUIRED');
  }

  const answerBySubId = new Map<string, CompositeSubAnswer>();
  for (const answer of subAnswers) {
    if (answerBySubId.has(answer.subQuestionId)) {
      throw new HttpError(400, `Duplicate sub answer ${answer.subQuestionId}`, 'DUPLICATE_SUB_ANSWER');
    }
    answerBySubId.set(answer.subQuestionId, answer);
  }

  const allowedIds = new Set(subQuestions.map(item => item.id));
  for (const answer of subAnswers) {
    if (!allowedIds.has(answer.subQuestionId)) {
      throw new HttpError(400, `Unknown sub question ${answer.subQuestionId}`, 'UNKNOWN_SUB_ANSWER');
    }
  }

  const fallbackScore = subQuestions.length ? round2(1 / subQuestions.length) : 0;
  const subResults: CompositeSubResult[] = subQuestions.map((subQuestion, index) => {
    const answer = answerBySubId.get(subQuestion.id) || null;
    const selectedOptions = answer?.selectedOptions || [];
    const answerText = answer?.answerText || '';
    const objective = isObjectiveType(subQuestion.type) || hasSubObjectiveAnswer(subQuestion);
    const answered = objective ? selectedOptions.length > 0 : Boolean(answerText.trim() || answer?.selfJudgedCorrect !== undefined);
    let isCorrect: boolean | null = null;
    let selfJudged = false;

    if (answer) {
      if (answer.selfJudgedCorrect !== undefined) {
        if (objective) {
          throw new HttpError(400, 'Self judgment is only allowed for subjective sub questions', 'SELF_JUDGMENT_NOT_ALLOWED');
        }
        isCorrect = answer.selfJudgedCorrect;
        selfJudged = true;
      } else if (objective) {
        isCorrect = judgeObjectiveAnswer(selectedOptions, subQuestion.correctOptionIndices, subQuestion.correctOptionIndex);
      } else if (subQuestion.answerText) {
        isCorrect = normalizeAnswerText(answerText) === normalizeAnswerText(subQuestion.answerText);
      }
    }

    const totalScore = subQuestion.score !== null ? subQuestion.score : fallbackScore;
    return {
      subQuestionId: subQuestion.id,
      order: index + 1,
      type: subQuestion.type,
      typeLabel: subQuestion.typeLabel,
      content: subQuestion.content,
      selectedOptions,
      answerText: answered ? answerText : null,
      isCorrect,
      selfJudged,
      correctOptionIndex: subQuestion.correctOptionIndex,
      correctOptionIndices: subQuestion.correctOptionIndices,
      correctAnswerText: subQuestion.answerText,
      explanation: subQuestion.explanation,
      answered,
      score: isCorrect === true ? round2(totalScore) : 0,
      totalScore: round2(totalScore),
    };
  });

  const answeredCount = subResults.filter(item => item.answered).length;
  const correctCount = subResults.filter(item => item.isCorrect === true).length;
  const wrongCount = subResults.filter(item => item.answered && item.isCorrect !== true).length;
  const unansweredCount = Math.max(0, subResults.length - answeredCount);
  if (answeredCount === 0) {
    throw new HttpError(400, 'At least one sub question must be answered', 'SUB_ANSWERS_EMPTY');
  }
  const judgedResults = subResults.filter(item => item.isCorrect !== null);
  const isCorrect = subResults.length > 0 && judgedResults.length === subResults.length
    ? subResults.every(item => item.isCorrect === true)
    : answeredCount > 0
      ? false
      : null;

  return {
    mode: 'composite',
    subResults,
    answeredCount,
    correctCount,
    wrongCount,
    unansweredCount,
    isCorrect,
  };
}

function normalizeStoredSubResults(value: unknown): CompositeSubResult[] {
  return jsonArray(value).map((raw, index) => {
    const source = jsonObject(raw);
    const selectedOptions = normalizeStringArray(source.selectedOptions);
    const correctOptionIndex = source.correctOptionIndex;
    const numericCorrectOptionIndex = correctOptionIndex === null || correctOptionIndex === undefined
      ? null
      : Number(correctOptionIndex);
    const isCorrect = typeof source.isCorrect === 'boolean' ? source.isCorrect : null;
    return {
      subQuestionId: typeof source.subQuestionId === 'string' && source.subQuestionId.trim()
        ? source.subQuestionId.trim()
        : `sub_${index + 1}`,
      order: Number.isFinite(Number(source.order)) ? Math.trunc(Number(source.order)) : index + 1,
      type: typeof source.type === 'string' && source.type.trim() ? source.type.trim() : 'choice',
      typeLabel: typeof source.typeLabel === 'string' ? source.typeLabel : null,
      content: typeof source.content === 'string' ? source.content : null,
      selectedOptions,
      answerText: typeof source.answerText === 'string' ? source.answerText : null,
      isCorrect,
      selfJudged: source.selfJudged === true,
      correctOptionIndex: typeof numericCorrectOptionIndex === 'number' && Number.isFinite(numericCorrectOptionIndex)
        ? Math.trunc(numericCorrectOptionIndex)
        : null,
      correctOptionIndices: normalizeNumberArray(source.correctOptionIndices),
      correctAnswerText: typeof source.correctAnswerText === 'string' ? source.correctAnswerText : null,
      explanation: typeof source.explanation === 'string' ? source.explanation : null,
      answered: source.answered === true,
      score: round2(finiteNumber(source.score, 0)),
      totalScore: round2(finiteNumber(source.totalScore, 0)),
    };
  });
}

function storedCompositeSubResults(subResults: CompositeSubResult[]) {
  return subResults.map(item => ({
    subQuestionId: item.subQuestionId,
    order: item.order,
    selectedOptions: item.selectedOptions,
    answerText: item.answerText,
    isCorrect: item.isCorrect,
    selfJudged: item.selfJudged,
    answered: item.answered,
    score: item.score,
    totalScore: item.totalScore,
  }));
}

function enrichCompositeSubResults(subResults: CompositeSubResult[], subQuestionsValue: unknown): CompositeSubResult[] {
  const subQuestionById = new Map(normalizeCompositeSubQuestions(subQuestionsValue).map(item => [item.id, item]));
  return subResults.map(item => {
    const subQuestion = subQuestionById.get(item.subQuestionId);
    if (!subQuestion) return item;
    return {
      ...item,
      type: subQuestion.type,
      typeLabel: subQuestion.typeLabel,
      content: subQuestion.content,
      correctOptionIndex: subQuestion.correctOptionIndex,
      correctOptionIndices: subQuestion.correctOptionIndices,
      correctAnswerText: subQuestion.answerText,
      explanation: subQuestion.explanation,
    };
  });
}

function scaleCompositeSubResults(subResults: CompositeSubResult[], questionScore: number) {
  if (!subResults.length) return { subResults: [], score: 0 };
  const rawTotal = round2(subResults.reduce((sum, item) => sum + finiteNumber(item.totalScore, 0), 0));
  const fallbackTotalEach = round2(questionScore / Math.max(1, subResults.length));
  const scale = rawTotal > 0 ? questionScore / rawTotal : 1;
  const scaled = subResults.map(item => {
    const totalScore = rawTotal > 0 ? round2(finiteNumber(item.totalScore, 0) * scale) : fallbackTotalEach;
    return {
      ...item,
      totalScore,
      score: item.isCorrect === true ? totalScore : 0,
    };
  });
  return {
    subResults: scaled,
    score: round2(scaled.reduce((sum, item) => sum + item.score, 0)),
  };
}

function optionalBodyBoolean(body: Record<string, unknown>, key: string): boolean | undefined {
  const value = body[key];
  if (value === undefined || value === null) return undefined;
  if (typeof value === 'boolean') return value;
  throw new HttpError(400, `${key} must be boolean`, 'INVALID_BOOLEAN_FIELD');
}

function resolveJudgedAnswer(
  row: QuestionAnswerRow,
  selectedOptions: string[],
  answerText: string,
  selfJudgedCorrect: boolean | undefined,
) {
  if (selfJudgedCorrect !== undefined) {
    if (hasObjectiveAnswer(row)) {
      throw new HttpError(400, 'Self judgment is only allowed for subjective questions', 'SELF_JUDGMENT_NOT_ALLOWED');
    }
    return selfJudgedCorrect;
  }
  return judgeAnswer(row, selectedOptions, answerText);
}

function optionalBodyString(body: Record<string, unknown>, key: string) {
  const value = body[key];
  return typeof value === 'string' && value.trim() ? value.trim() : null;
}

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

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

function positiveInt(value: unknown, fallback: number, max = 500) {
  const parsed = Number(value ?? fallback);
  if (!Number.isFinite(parsed) || parsed <= 0) return fallback;
  return Math.min(Math.trunc(parsed), max);
}

function finiteNumber(value: unknown, fallback = 0) {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? parsed : fallback;
}

function round2(value: number) {
  return Math.round(value * 100) / 100;
}

function round4(value: number) {
  return Math.round(value * 10000) / 10000;
}

async function totalPracticeReportCount(client: pg.PoolClient, tenantId: string, userId: string) {
  const result = await client.query<{ count: string }>(
    `
    select count(*)::text as count
    from public.practice_session_reports
    where tenant_id = $1 and user_id = $2
    `,
    [tenantId, userId],
  );
  return Number(result.rows[0]?.count || 0);
}

async function bestMockExamScore(client: pg.PoolClient, tenantId: string, userId: string) {
  const result = await client.query<{ score: string | number | null }>(
    `
    select max(score) as score
    from public.practice_session_reports
    where tenant_id = $1
      and user_id = $2
      and mode = 'mock_exam'
    `,
    [tenantId, userId],
  );
  return finiteNumber(result.rows[0]?.score, 0);
}

async function masteredWordCount(client: pg.PoolClient, tenantId: string, userId: string) {
  const result = await client.query<{ count: string }>(
    `
    select count(*)::text as count
    from public.user_word_progress
    where tenant_id = $1
      and user_id = $2
      and status = 'mastered'
    `,
    [tenantId, userId],
  );
  return Number(result.rows[0]?.count || 0);
}

async function grantLearningBadgesForPracticeReport(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    userId: string;
    report: PracticeSessionReportRow;
  },
) {
  const practiceCount = await totalPracticeReportCount(client, input.tenantId, input.userId);
  const grants: AutoBadgeGrant[] = [
    ...(await autoGrantBadges(client, {
      tenantId: input.tenantId,
      userId: input.userId,
      trigger: 'practice_count',
      evidence: {
        practiceCount,
        practiceReportCount: practiceCount,
        latestReportId: input.report.id,
        latestPracticeSessionId: input.report.practiceSessionId,
        latestPracticeMode: input.report.mode,
        latestAnsweredCount: Number(input.report.answeredCount || 0),
        latestCorrectCount: Number(input.report.correctCount || 0),
        latestAccuracy: finiteNumber(input.report.accuracy, 0),
      },
    })),
  ];

  if (input.report.mode === 'mock_exam') {
    const mockExamScore = finiteNumber(input.report.score, 0);
    const mockExamTotalScore = finiteNumber(input.report.totalScore, 0);
    grants.push(
      ...(await autoGrantBadges(client, {
        tenantId: input.tenantId,
        userId: input.userId,
        trigger: 'mock_exam_score',
        evidence: {
          mockExamScore,
          mockExamTotalScore,
          mockExamAccuracy: finiteNumber(input.report.accuracy, 0),
          mockExamBestScore: await bestMockExamScore(client, input.tenantId, input.userId),
          practiceCount,
          reportId: input.report.id,
          practiceSessionId: input.report.practiceSessionId,
          blueprintId: input.report.blueprintId,
          collectionId: input.report.collectionId,
        },
      })),
    );
  }

  return grants;
}

async function grantVocabularyMasteredBadges(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    userId: string;
    progress: Record<string, unknown>;
    source: string;
  },
) {
  if (input.progress.status !== 'mastered') return [];
  const count = await masteredWordCount(client, input.tenantId, input.userId);
  return autoGrantBadges(client, {
    tenantId: input.tenantId,
    userId: input.userId,
    trigger: 'vocabulary_mastered',
    evidence: {
      vocabularyMasteredCount: count,
      masteredWordsCount: count,
      progressId: input.progress.id,
      wordId: input.progress.wordId,
      correctCount: input.progress.correctCount,
      wrongCount: input.progress.wrongCount,
      reviewCount: input.progress.reviewCount,
      correctStreak: input.progress.correctStreak,
      source: input.source,
    },
  });
}

function dateParam(ctx: RequestContext, name: string) {
  const value = stringParam(ctx, name);
  if (!value) return '';
  if (!/^\d{4}-\d{2}-\d{2}$/.test(value)) {
    throw new HttpError(400, `${name} must be YYYY-MM-DD`, 'INVALID_DATE_PARAM');
  }
  return value;
}

function idArrayFromJson(value: unknown): string[] {
  return normalizeStringArray(value);
}

function sectionDefinitionsFrom(metadata: Record<string, unknown>): SectionDefinition[] {
  const assembly = jsonObject(metadata.assembly);
  return jsonArray(assembly.sections).map((section, index) => {
    const object = jsonObject(section);
    const key = String(object.key || object.sectionKey || object.questionType || object.type || `section_${index + 1}`);
    const title = typeof object.title === 'string'
      ? object.title
      : typeof object.name === 'string'
        ? object.name
        : null;
    const questionType = typeof object.questionType === 'string'
      ? object.questionType
      : typeof object.type === 'string'
        ? object.type
        : null;
    const scoreEach = object.scoreEach === undefined && object.score === undefined ? null : finiteNumber(object.scoreEach ?? object.score, 0);
    return { key, title, questionType, scoreEach, sortOrder: index };
  });
}

function sectionForQuestion(row: PracticeSessionReportQuestionRow, sections: SectionDefinition[]) {
  const direct = row.sectionKey ? sections.find(section => section.key === row.sectionKey) : undefined;
  if (direct) return direct;
  const byType = sections.find(section => section.questionType && section.questionType === row.type);
  if (byType) return byType;
  return {
    key: row.sectionKey || row.type || 'default',
    title: row.typeLabel,
    questionType: row.type || null,
    scoreEach: null,
    sortOrder: sections.length,
  };
}

function resolveQuestionScore(input: {
  row: PracticeSessionReportQuestionRow;
  section: SectionDefinition;
  fallbackScore: number;
}) {
  if (input.row.itemScore !== null && input.row.itemScore !== undefined) return finiteNumber(input.row.itemScore, input.fallbackScore);
  if (input.section.scoreEach !== null && Number.isFinite(input.section.scoreEach)) return input.section.scoreEach;
  return input.fallbackScore;
}

function modeFrom(value: string) {
  if (['sequential', 'random', 'mock_exam', 'paper', 'wrong_review', 'favorite_review', 'chapter'].includes(value)) return value;
  return 'chapter';
}

async function loadPracticeBlueprint(tenantId: string, blueprintId: string) {
  const row = await queryOne<PracticeBlueprintRow>(
    `
    select id, entry_id, node_id, collection_id, mode, assembly_type,
           question_limit, duration_minutes, total_score, sections, rules
    from public.practice_blueprints
    where tenant_id = $1 and id = $2 and status = 'active'
    limit 1
    `,
    [tenantId, blueprintId],
  );
  if (!row) {
    throw new HttpError(404, 'Practice blueprint not found', 'PRACTICE_BLUEPRINT_NOT_FOUND');
  }
  return row;
}

async function buildPracticeAssembly(tenantId: string, body: Record<string, unknown>): Promise<PracticeAssembly> {
  const blueprintId = optionalBodyString(body, 'blueprintId');
  const targetType = optionalString(body, 'targetType') || null;
  const targetId = optionalString(body, 'targetId') || null;
  const inputMode = modeFrom(optionalString(body, 'mode') || 'chapter');

  if (!blueprintId) {
    return {
      mode: inputMode,
      targetType,
      targetId,
      blueprintId: null,
      collectionId: optionalBodyString(body, 'collectionId'),
      entryId: optionalBodyString(body, 'entryId'),
      contentNodeId: optionalBodyString(body, 'contentNodeId') || (targetType === 'content_node' ? targetId : null),
      questionLimit: positiveInt(body.questionLimit, 100),
      durationMinutes: body.durationMinutes === undefined ? null : positiveInt(body.durationMinutes, 60, 24 * 60),
      totalScore: body.totalScore === undefined || body.totalScore === null ? null : Number(body.totalScore),
      sections: jsonArray(body.sections),
      rules: body.rules && typeof body.rules === 'object' && !Array.isArray(body.rules) ? body.rules as Record<string, unknown> : {},
    };
  }

  const blueprint = await loadPracticeBlueprint(tenantId, blueprintId);
  return {
    mode: modeFrom(blueprint.mode),
    targetType: targetType || 'blueprint',
    targetId: targetId || blueprint.id,
    blueprintId,
    collectionId: optionalBodyString(body, 'collectionId') || blueprint.collection_id,
    entryId: optionalBodyString(body, 'entryId') || blueprint.entry_id,
    contentNodeId: optionalBodyString(body, 'contentNodeId') || blueprint.node_id,
    questionLimit: positiveInt(body.questionLimit ?? blueprint.question_limit, 100),
    durationMinutes: body.durationMinutes === undefined
      ? blueprint.duration_minutes
      : positiveInt(body.durationMinutes, blueprint.duration_minutes || 60, 24 * 60),
    totalScore: body.totalScore === undefined || body.totalScore === null
      ? (blueprint.total_score === null ? null : Number(blueprint.total_score))
      : Number(body.totalScore),
    sections: jsonArray(body.sections).length ? jsonArray(body.sections) : jsonArray(blueprint.sections),
    rules: {
      ...(blueprint.rules || {}),
      ...(body.rules && typeof body.rules === 'object' && !Array.isArray(body.rules) ? body.rules as Record<string, unknown> : {}),
    },
  };
}

async function collectFromCollection(
  tenantId: string,
  collectionId: string,
  limit: number,
  randomize: boolean,
  typeFilter?: string | null,
  sectionKey?: string | null,
  excludeIds: string[] = [],
) {
  const params: unknown[] = [tenantId, collectionId];
  const filters = ['ci.tenant_id = $1', 'ci.collection_id = $2', `q.status = 'published'`];
  if (typeFilter) {
    params.push(typeFilter);
    filters.push(`q.type = $${params.length}`);
  }
  if (sectionKey) {
    params.push(sectionKey);
    filters.push(`ci.section_key = $${params.length}`);
  }
  if (excludeIds.length) {
    params.push(excludeIds);
    filters.push(`q.id <> all($${params.length}::uuid[])`);
  }
  params.push(limit);

  const rows = await query<{ id: string }>(
    `
    select q.id
    from public.question_collection_items ci
    join public.questions q on q.id = ci.question_id and q.tenant_id = ci.tenant_id
    where ${filters.join(' and ')}
    order by ${randomize ? 'random()' : 'ci.sort_order asc, q.created_at asc'}
    limit $${params.length}
    `,
    params,
  );
  return rows.map(row => row.id);
}

async function collectFromContentNode(
  tenantId: string,
  contentNodeId: string,
  limit: number,
  randomize: boolean,
  typeFilter?: string | null,
  excludeIds: string[] = [],
) {
  const params: unknown[] = [tenantId, contentNodeId];
  const filters = ['q.tenant_id = $1', `q.status = 'published'`];
  if (typeFilter) {
    params.push(typeFilter);
    filters.push(`q.type = $${params.length}`);
  }
  if (excludeIds.length) {
    params.push(excludeIds);
    filters.push(`q.id <> all($${params.length}::uuid[])`);
  }
  params.push(limit);

  const rows = await query<{ id: string }>(
    `
    with target_node as (
      select path
      from public.content_nodes
      where tenant_id = $1 and id = $2 and is_active = true
      limit 1
    )
    select q.id
    from public.questions q
    left join public.content_nodes n on n.id = q.content_node_id and n.tenant_id = q.tenant_id
    where ${filters.join(' and ')}
      and exists (select 1 from target_node)
      and (
        q.content_node_id = $2
        or n.path <@ (select path from target_node)
      )
    order by ${randomize ? 'random()' : 'q.created_at asc'}
    limit $${params.length}
    `,
    params,
  );
  return rows.map(row => row.id);
}

async function collectFromLegacyTarget(
  tenantId: string,
  targetType: string | null,
  targetId: string | null,
  limit: number,
  randomize: boolean,
) {
  if (!targetType || !targetId) return [];
  const columnByTarget: Record<string, string> = {
    subject: 'subject_id',
    category: 'category_id',
    node: 'node_id',
    question_bank: 'question_bank_id',
  };
  const column = columnByTarget[targetType];
  if (!column) return [];

  const rows = await query<{ id: string }>(
    `
    select id
    from public.questions
    where tenant_id = $1 and status = 'published' and ${column} = $2
    order by ${randomize ? 'random()' : 'created_at asc'}
    limit $3
    `,
    [tenantId, targetId, limit],
  );
  return rows.map(row => row.id);
}

async function collectWrongReviewQuestions(tenantId: string, userId: string, limit: number) {
  const rows = await query<{ id: string }>(
    `
    select q.id
    from public.wrong_questions wq
    join public.questions q on q.id = wq.question_id and q.tenant_id = wq.tenant_id
    where wq.tenant_id = $1
      and wq.user_id = $2
      and wq.resolved_at is null
      and q.status = 'published'
    order by wq.wrong_count desc, wq.last_wrong_at asc
    limit $3
    `,
    [tenantId, userId, limit],
  );
  return rows.map(row => row.id);
}

async function collectFavoriteReviewQuestions(tenantId: string, userId: string, limit: number) {
  const rows = await query<{ id: string }>(
    `
    select q.id
    from public.favorite_questions fq
    join public.questions q on q.id = fq.question_id and q.tenant_id = fq.tenant_id
    where fq.tenant_id = $1
      and fq.user_id = $2
      and q.status = 'published'
    order by fq.created_at desc
    limit $3
    `,
    [tenantId, userId, limit],
  );
  return rows.map(row => row.id);
}

function sectionType(section: unknown) {
  if (!section || typeof section !== 'object' || Array.isArray(section)) return null;
  const object = section as Record<string, unknown>;
  const value = object.questionType ?? object.type;
  return typeof value === 'string' && value.trim() ? value.trim() : null;
}

function sectionKey(section: unknown) {
  if (!section || typeof section !== 'object' || Array.isArray(section)) return null;
  const object = section as Record<string, unknown>;
  const value = object.sectionKey ?? object.key;
  return typeof value === 'string' && value.trim() ? value.trim() : null;
}

function sectionLimit(section: unknown, fallback: number) {
  if (!section || typeof section !== 'object' || Array.isArray(section)) return fallback;
  const object = section as Record<string, unknown>;
  return positiveInt(object.questionCount ?? object.limit, fallback);
}

async function assembleQuestionIds(tenantId: string, userId: string, assembly: PracticeAssembly) {
  const randomize = assembly.mode === 'random' || assembly.mode === 'mock_exam' || assembly.rules.randomize === true;
  const questionIds: string[] = [];
  const pushUnique = (ids: string[]) => {
    for (const id of ids) {
      if (!questionIds.includes(id)) questionIds.push(id);
    }
  };

  if (assembly.mode === 'wrong_review') {
    return collectWrongReviewQuestions(tenantId, userId, assembly.questionLimit);
  }
  if (assembly.mode === 'favorite_review') {
    return collectFavoriteReviewQuestions(tenantId, userId, assembly.questionLimit);
  }

  if (assembly.sections.length && (assembly.collectionId || assembly.contentNodeId)) {
    for (const section of assembly.sections) {
      const limit = sectionLimit(section, assembly.questionLimit);
      const type = sectionType(section);
      const key = sectionKey(section);
      const ids = assembly.collectionId
        ? await collectFromCollection(tenantId, assembly.collectionId, limit, randomize, type, key, questionIds)
        : await collectFromContentNode(tenantId, assembly.contentNodeId || '', limit, randomize, type, questionIds);
      pushUnique(ids);
    }
    return questionIds.slice(0, assembly.questionLimit);
  }

  if (assembly.collectionId) {
    return collectFromCollection(tenantId, assembly.collectionId, assembly.questionLimit, randomize);
  }
  if (assembly.contentNodeId) {
    return collectFromContentNode(tenantId, assembly.contentNodeId, assembly.questionLimit, randomize);
  }
  return collectFromLegacyTarget(tenantId, assembly.targetType, assembly.targetId, assembly.questionLimit, randomize);
}

export async function createPracticeSessionRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const assembly = await buildPracticeAssembly(tenantId, body);
  const questionIds = await assembleQuestionIds(tenantId, userId, assembly);

  if ((assembly.blueprintId || assembly.collectionId || assembly.contentNodeId) && questionIds.length === 0) {
    throw new HttpError(409, 'No published questions are available for this practice target', 'NO_PRACTICE_QUESTIONS');
  }
  if (assembly.mode === 'wrong_review' && questionIds.length === 0) {
    throw new HttpError(409, 'No unresolved wrong questions are available for review', 'NO_WRONG_REVIEW_QUESTIONS');
  }
  if (assembly.mode === 'favorite_review' && questionIds.length === 0) {
    throw new HttpError(409, 'No favorite questions are available for review', 'NO_FAVORITE_REVIEW_QUESTIONS');
  }

  const item = await transaction(async client => {
    const access = await authorizePracticeSession(client, {
      tenantId,
      userId,
      questionIds,
      assembly,
    });
    const sessionResult = await client.query(
      `
      insert into public.practice_sessions (
        tenant_id, user_id, mode, target_type, target_id,
        blueprint_id, collection_id, entry_id, content_node_id,
        question_ids, question_count, duration_minutes, total_score,
        access_mode, access_entitlement_id, consumed_free_quota, access_snapshot,
        expires_at, metadata
      )
      values (
        $1, $2, $3, $4, $5::uuid,
        $6::uuid, $7::uuid, $8::uuid, $9::uuid,
        $10::jsonb, $11, $12, $13,
        $14, $15::uuid, $16, $17::jsonb,
        case when $12::integer is null then null else now() + make_interval(mins => $12::integer) end,
        $18::jsonb
      )
      returning id, tenant_id as "tenantId", user_id as "userId", mode,
                target_type as "targetType", target_id as "targetId",
                blueprint_id as "blueprintId", collection_id as "collectionId",
                entry_id as "entryId", content_node_id as "contentNodeId",
                question_ids as "questionIds", question_count as "questionCount",
                duration_minutes as "durationMinutes", total_score as "totalScore",
                access_mode as "accessMode", access_entitlement_id as "accessEntitlementId",
                consumed_free_quota as "consumedFreeQuota", access_snapshot as "accessSnapshot",
                expires_at as "expiresAt", started_at as "startedAt",
                finished_at as "finishedAt", metadata
      `,
      [
        tenantId,
        userId,
        assembly.mode,
        assembly.targetType,
        assembly.targetId,
        assembly.blueprintId,
        assembly.collectionId,
        assembly.entryId,
        assembly.contentNodeId,
        JSON.stringify(access.questionIds),
        access.questionIds.length,
        assembly.durationMinutes,
        assembly.totalScore,
        access.accessMode,
        access.entitlementId,
        access.consumedFreeQuota,
        JSON.stringify(access.accessSnapshot),
        JSON.stringify({
          ...(body.metadata && typeof body.metadata === 'object' && !Array.isArray(body.metadata) ? body.metadata : {}),
          assembly: {
            sections: assembly.sections,
            rules: assembly.rules,
          },
        }),
      ],
    );

    await recordPracticeAccessEvent(client, {
      tenantId,
      userId,
      practiceSessionId: sessionResult.rows[0].id,
      eventType: 'session_created',
      accessMode: access.accessMode,
      requestedCount: questionIds.length,
      grantedCount: access.questionIds.length,
      consumedFreeQuota: access.consumedFreeQuota,
      scopeType: access.accessSnapshot.quotaScopeType as string | undefined,
      scopeId: access.accessSnapshot.quotaScopeId as string | undefined,
      entitlementId: access.entitlementId,
      metadata: access.accessSnapshot,
    });

    return sessionResult.rows[0];
  });

  return { item };
}

export async function submitAnswerRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const questionId = requiredString(body, 'questionId');
  const selectedOptions = optionalStringArray(body, 'selectedOptions');
  const answerText = optionalString(body, 'answerText');
  const selfJudgedCorrect = optionalBodyBoolean(body, 'selfJudgedCorrect');
  const subAnswers = parseSubAnswers(body.subAnswers);
  const practiceSessionId = optionalString(body, 'practiceSessionId') || null;

  const question = await queryOne<QuestionAnswerRow>(
    `
    select q.id as question_id, q.current_version_id as question_version_id,
           q.type, v.correct_option_index, v.correct_option_indices, v.answer_text,
           v.sub_questions
    from public.questions q
    left join public.question_versions v on v.id = q.current_version_id
    where q.tenant_id = $1 and q.id = $2 and q.status = 'published'
    limit 1
    `,
    [tenantId, questionId],
  );

  if (!question) {
    throw new HttpError(404, 'Question not found', 'QUESTION_NOT_FOUND');
  }

  const result = await transaction(async client => {
    await assertAnswerSessionAccess(client, { tenantId, userId, practiceSessionId, questionId });
    const compositeEvaluation = subAnswers.length ? evaluateCompositeAnswer(question, subAnswers) : null;
    if (!compositeEvaluation && normalizeCompositeSubQuestions(question.sub_questions).length) {
      throw new HttpError(400, 'Composite questions must be answered with subAnswers', 'SUB_ANSWERS_REQUIRED');
    }
    if (compositeEvaluation && (selectedOptions.length || answerText || selfJudgedCorrect !== undefined)) {
      throw new HttpError(400, 'Composite answers must not mix top-level answer fields', 'MIXED_COMPOSITE_ANSWER');
    }
    const judged = compositeEvaluation?.isCorrect ?? resolveJudgedAnswer(question, selectedOptions, answerText, selfJudgedCorrect);
    const answerPayload = compositeEvaluation ? {
      mode: compositeEvaluation.mode,
      subAnswers: subAnswers.map(item => ({
        subQuestionId: item.subQuestionId,
        selectedOptions: item.selectedOptions,
        answerText: item.answerText || null,
        selfJudgedCorrect: item.selfJudgedCorrect,
      })),
      subResults: storedCompositeSubResults(compositeEvaluation.subResults),
      summary: {
        answeredCount: compositeEvaluation.answeredCount,
        correctCount: compositeEvaluation.correctCount,
        wrongCount: compositeEvaluation.wrongCount,
        unansweredCount: compositeEvaluation.unansweredCount,
      },
    } : {};

    const answerResult = await client.query(
      `
      insert into public.answer_records (
        tenant_id, user_id, question_id, question_version_id, practice_session_id,
        selected_options, answer_text, answer_payload, is_correct
      )
      values ($1, $2, $3, $4, $5, $6::jsonb, $7, $8::jsonb, $9)
      returning id, question_id as "questionId", question_version_id as "questionVersionId",
                selected_options as "selectedOptions", answer_text as "answerText",
                answer_payload as "answerPayload", is_correct as "isCorrect", answered_at as "answeredAt"
      `,
      [
        tenantId,
        userId,
        questionId,
        question.question_version_id,
        practiceSessionId,
        JSON.stringify(selectedOptions),
        answerText || null,
        JSON.stringify(answerPayload),
        judged,
      ],
    );

    if (judged === false) {
      await client.query(
        `
        insert into public.wrong_questions (tenant_id, user_id, question_id, wrong_count, last_wrong_at, resolved_at)
        values ($1, $2, $3, 1, now(), null)
        on conflict (tenant_id, user_id, question_id)
        do update set wrong_count = public.wrong_questions.wrong_count + 1,
                      last_wrong_at = now(),
                      resolved_at = null
        `,
        [tenantId, userId, questionId],
      );
    } else if (judged === true) {
      await client.query(
        `
        update public.wrong_questions
        set resolved_at = coalesce(resolved_at, now())
        where tenant_id = $1 and user_id = $2 and question_id = $3 and resolved_at is null
        `,
        [tenantId, userId, questionId],
      );
    }

    await client.query(
      `
      update public.student_profiles
      set questions_answered_today = questions_answered_today + 1,
          stats = coalesce(stats, '{}'::jsonb) || jsonb_strip_nulls(jsonb_build_object(
            'totalAnswered', coalesce((stats->>'totalAnswered')::integer, 0) + 1,
            'correctCount', case when $3::boolean is true then coalesce((stats->>'correctCount')::integer, 0) + 1 else null end,
            'wrongCount', case when $3::boolean is false then coalesce((stats->>'wrongCount')::integer, 0) + 1 else null end
          )),
          updated_at = now()
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId, judged],
    );

    return {
      ...answerResult.rows[0],
      selfJudged: selfJudgedCorrect !== undefined,
      subResults: compositeEvaluation?.subResults,
      compositeSummary: compositeEvaluation ? {
        answeredCount: compositeEvaluation.answeredCount,
        correctCount: compositeEvaluation.correctCount,
        wrongCount: compositeEvaluation.wrongCount,
        unansweredCount: compositeEvaluation.unansweredCount,
      } : undefined,
    };
  });

  return { item: result };
}

function formatReport(row: PracticeSessionReportRow) {
  return {
    ...row,
    totalQuestions: Number(row.totalQuestions),
    answeredCount: Number(row.answeredCount),
    correctCount: Number(row.correctCount),
    wrongCount: Number(row.wrongCount),
    unansweredCount: Number(row.unansweredCount),
    score: finiteNumber(row.score),
    totalScore: finiteNumber(row.totalScore),
    accuracy: finiteNumber(row.accuracy),
    durationSeconds: Number(row.durationSeconds || 0),
    sectionStats: jsonArray(row.sectionStats),
    questionResults: jsonArray(row.questionResults),
    wrongQuestionIds: normalizeStringArray(row.wrongQuestionIds),
    metadata: jsonObject(row.metadata),
  };
}

function practiceSessionStatus(row: { finishedAt?: string | null; expiresAt?: string | null }) {
  if (row.finishedAt) return 'finished';
  if (row.expiresAt && new Date(row.expiresAt).getTime() <= Date.now()) return 'expired';
  return 'active';
}

function formatPracticeSession(row: PracticeSessionDetailRow & Record<string, unknown>) {
  const questionIds = idArrayFromJson(row.questionIds);
  return {
    ...row,
    questionIds,
    questionCount: Number(row.questionCount || questionIds.length),
    durationMinutes: row.durationMinutes === null || row.durationMinutes === undefined ? null : Number(row.durationMinutes),
    totalScore: row.totalScore === null || row.totalScore === undefined ? null : finiteNumber(row.totalScore, 0),
    consumedFreeQuota: row.consumedFreeQuota === undefined ? undefined : Number(row.consumedFreeQuota || 0),
    accessSnapshot: jsonObject(row.accessSnapshot),
    metadata: jsonObject(row.metadata),
    status: practiceSessionStatus(row),
  };
}

async function fetchPracticeReportBySession(
  client: { query: pg.PoolClient['query'] },
  tenantId: string,
  userId: string,
  practiceSessionId: string,
) {
  const result = await client.query<PracticeSessionReportRow>(
    `
    select id, tenant_id as "tenantId", user_id as "userId",
           practice_session_id as "practiceSessionId",
           blueprint_id as "blueprintId", collection_id as "collectionId", mode,
           total_questions as "totalQuestions", answered_count as "answeredCount",
           correct_count as "correctCount", wrong_count as "wrongCount",
           unanswered_count as "unansweredCount",
           score, total_score as "totalScore", accuracy,
           duration_seconds as "durationSeconds",
           started_at as "startedAt", submitted_at as "submittedAt",
           section_stats as "sectionStats", question_results as "questionResults",
           wrong_question_ids as "wrongQuestionIds", metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.practice_session_reports
    where tenant_id = $1 and user_id = $2 and practice_session_id = $3
    limit 1
    `,
    [tenantId, userId, practiceSessionId],
  );
  return result.rows[0] || null;
}

async function buildPracticeSessionReport(
  client: pg.PoolClient,
  tenantId: string,
  userId: string,
  practiceSessionId: string,
) {
  const existing = await fetchPracticeReportBySession(client, tenantId, userId, practiceSessionId);
  if (existing) return existing;

  const sessionResult = await client.query<PracticeSessionReportSessionRow>(
    `
    select id, tenant_id as "tenantId", user_id as "userId", mode,
           blueprint_id as "blueprintId", collection_id as "collectionId",
           entry_id as "entryId", content_node_id as "contentNodeId",
           question_ids as "questionIds", question_count as "questionCount",
           duration_minutes as "durationMinutes", total_score as "totalScore",
           started_at as "startedAt", finished_at as "finishedAt", metadata
    from public.practice_sessions
    where tenant_id = $1 and user_id = $2 and id = $3
    for update
    `,
    [tenantId, userId, practiceSessionId],
  );
  const session = sessionResult.rows[0];
  if (!session) {
    throw new HttpError(404, 'Practice session not found', 'PRACTICE_SESSION_NOT_FOUND');
  }

  const existingAfterLock = await fetchPracticeReportBySession(client, tenantId, userId, practiceSessionId);
  if (existingAfterLock) return existingAfterLock;

  const questionIds = idArrayFromJson(session.questionIds);
  if (questionIds.length === 0) {
    throw new HttpError(409, 'Practice session has no question snapshot', 'PRACTICE_SESSION_EMPTY');
  }

  const sectionDefinitions = sectionDefinitionsFrom(session.metadata);
  const fallbackQuestionScore = session.totalScore
    ? finiteNumber(session.totalScore, questionIds.length) / Math.max(1, questionIds.length)
    : 1;

  const questionRows = await client.query<PracticeSessionReportQuestionRow>(
    `
    select q.id as "questionId", q.type, q.type_label as "typeLabel",
           ci.collection_id as "collectionId", ci.section_key as "sectionKey",
           ci.sort_order as "sortOrder", ci.score as "itemScore",
           v.content, v.explanation,
           v.correct_option_index as "correctOptionIndex",
           v.correct_option_indices as "correctOptionIndices",
           v.answer_text as "answerText", v.sub_questions as "subQuestions"
    from public.questions q
    left join public.question_versions v on v.id = q.current_version_id
    left join public.question_collection_items ci
      on ci.tenant_id = q.tenant_id
     and ci.question_id = q.id
     and ci.collection_id = $3::uuid
    where q.tenant_id = $1 and q.id = any($2::uuid[])
    `,
    [tenantId, questionIds, session.collectionId],
  );
  const questionById = new Map(questionRows.rows.map(row => [row.questionId, row]));

  const answerRows = await client.query<PracticeSessionReportAnswerRow>(
    `
    select distinct on (question_id)
           question_id as "questionId", selected_options as "selectedOptions",
           answer_text as "answerText", answer_payload as "answerPayload",
           is_correct as "isCorrect",
           answered_at as "answeredAt"
    from public.answer_records
    where tenant_id = $1
      and user_id = $2
      and practice_session_id = $3
      and question_id = any($4::uuid[])
    order by question_id, answered_at desc
    `,
    [tenantId, userId, practiceSessionId, questionIds],
  );
  const answerByQuestionId = new Map(answerRows.rows.map(row => [row.questionId, row]));

  const sectionStatsByKey = new Map<string, SectionStat>();
  const questionResults: PracticeReportQuestionResult[] = [];
  const wrongQuestionIds: string[] = [];

  for (const questionId of questionIds) {
    const question = questionById.get(questionId);
    if (!question) continue;
    const answer = answerByQuestionId.get(questionId) || null;
    const section = sectionForQuestion(question, sectionDefinitions);
    const questionScore = round2(resolveQuestionScore({ row: question, section, fallbackScore: fallbackQuestionScore }));
    const answered = !!answer;
    const isCorrect = answer?.isCorrect ?? null;
    const storedPayload = jsonObject(answer?.answerPayload);
    const storedSubResults = enrichCompositeSubResults(normalizeStoredSubResults(storedPayload.subResults), question.subQuestions);
    const scaledComposite = storedSubResults.length ? scaleCompositeSubResults(storedSubResults, questionScore) : null;
    const earnedScore = scaledComposite ? scaledComposite.score : isCorrect === true ? questionScore : 0;
    const sectionStat = sectionStatsByKey.get(section.key) || {
      key: section.key,
      title: section.title,
      questionType: section.questionType,
      questionCount: 0,
      answeredCount: 0,
      correctCount: 0,
      wrongCount: 0,
      unansweredCount: 0,
      score: 0,
      totalScore: 0,
      accuracy: 0,
      sortOrder: section.sortOrder,
    };

    sectionStat.questionCount += 1;
    sectionStat.totalScore = round2(sectionStat.totalScore + questionScore);
    if (answered) sectionStat.answeredCount += 1;
    if (isCorrect === true) {
      sectionStat.correctCount += 1;
      sectionStat.score = round2(sectionStat.score + earnedScore);
    } else if (answered) {
      sectionStat.wrongCount += 1;
      wrongQuestionIds.push(questionId);
    } else {
      sectionStat.unansweredCount += 1;
    }
    sectionStatsByKey.set(section.key, sectionStat);

    questionResults.push({
      questionId,
      questionType: question.type,
      sectionKey: section.key,
      score: earnedScore,
      totalScore: questionScore,
      answered,
      isCorrect,
      selectedOptions: normalizeStringArray(answer?.selectedOptions),
      answerText: answer?.answerText || null,
      answeredAt: answer?.answeredAt || null,
      correctOptionIndex: question.correctOptionIndex,
      correctOptionIndices: normalizeNumberArray(question.correctOptionIndices),
      correctAnswerText: question.answerText,
      explanation: question.explanation,
      content: question.content,
      subResults: scaledComposite?.subResults,
    });
  }

  const sectionStats = [...sectionStatsByKey.values()]
    .sort((left, right) => left.sortOrder - right.sortOrder || left.key.localeCompare(right.key))
    .map(section => ({
      ...section,
      unansweredCount: Math.max(0, section.questionCount - section.answeredCount),
      accuracy: round4(section.questionCount ? section.correctCount / section.questionCount : 0),
      score: round2(section.score),
      totalScore: round2(section.totalScore),
    }));

  const totalQuestions = questionResults.length;
  const answeredCount = questionResults.filter(item => item.answered).length;
  const correctCount = questionResults.filter(item => item.isCorrect === true).length;
  const wrongCount = questionResults.filter(item => item.answered && item.isCorrect !== true).length;
  const unansweredCount = Math.max(0, totalQuestions - answeredCount);
  const score = round2(questionResults.reduce((sum, item) => sum + item.score, 0));
  const computedTotalScore = round2(questionResults.reduce((sum, item) => sum + item.totalScore, 0));
  const configuredTotalScore = session.totalScore ? round2(finiteNumber(session.totalScore, computedTotalScore)) : computedTotalScore;
  const submittedAt = new Date();
  const startedAt = new Date(session.startedAt);
  const durationSeconds = Number.isFinite(startedAt.getTime())
    ? Math.max(0, Math.trunc((submittedAt.getTime() - startedAt.getTime()) / 1000))
    : 0;

  const reportResult = await client.query<PracticeSessionReportRow>(
    `
    insert into public.practice_session_reports (
      tenant_id, user_id, practice_session_id, blueprint_id, collection_id, mode,
      total_questions, answered_count, correct_count, wrong_count, unanswered_count,
      score, total_score, accuracy, duration_seconds, started_at, submitted_at,
      section_stats, question_results, wrong_question_ids, metadata
    )
    values (
      $1, $2, $3, $4::uuid, $5::uuid, $6,
      $7, $8, $9, $10, $11,
      $12, $13, $14, $15, $16, $17,
      $18::jsonb, $19::jsonb, $20::jsonb, $21::jsonb
    )
    returning id, tenant_id as "tenantId", user_id as "userId",
              practice_session_id as "practiceSessionId",
              blueprint_id as "blueprintId", collection_id as "collectionId", mode,
              total_questions as "totalQuestions", answered_count as "answeredCount",
              correct_count as "correctCount", wrong_count as "wrongCount",
              unanswered_count as "unansweredCount",
              score, total_score as "totalScore", accuracy,
              duration_seconds as "durationSeconds",
              started_at as "startedAt", submitted_at as "submittedAt",
              section_stats as "sectionStats", question_results as "questionResults",
              wrong_question_ids as "wrongQuestionIds", metadata,
              created_at as "createdAt", updated_at as "updatedAt"
    `,
    [
      tenantId,
      userId,
      practiceSessionId,
      session.blueprintId,
      session.collectionId,
      session.mode,
      totalQuestions,
      answeredCount,
      correctCount,
      wrongCount,
      unansweredCount,
      score,
      configuredTotalScore,
      round4(totalQuestions ? correctCount / totalQuestions : 0),
      durationSeconds,
      session.startedAt,
      submittedAt.toISOString(),
      JSON.stringify(sectionStats),
      JSON.stringify(questionResults),
      JSON.stringify(wrongQuestionIds),
      JSON.stringify({
        scoringVersion: 1,
        computedTotalScore,
        configuredTotalScore,
        entryId: session.entryId,
        contentNodeId: session.contentNodeId,
      }),
    ],
  );
  const report = reportResult.rows[0];

  for (const section of sectionStats) {
    await client.query(
      `
      insert into public.practice_session_report_sections (
        tenant_id, report_id, practice_session_id, section_key, section_name, question_type,
        question_count, answered_count, correct_count, wrong_count, unanswered_count,
        score, total_score, accuracy, sort_order, metadata
      )
      values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, '{}'::jsonb)
      on conflict (tenant_id, report_id, section_key)
      do update set section_name = excluded.section_name,
                    question_type = excluded.question_type,
                    question_count = excluded.question_count,
                    answered_count = excluded.answered_count,
                    correct_count = excluded.correct_count,
                    wrong_count = excluded.wrong_count,
                    unanswered_count = excluded.unanswered_count,
                    score = excluded.score,
                    total_score = excluded.total_score,
                    accuracy = excluded.accuracy,
                    sort_order = excluded.sort_order,
                    updated_at = now()
      `,
      [
        tenantId,
        report.id,
        practiceSessionId,
        section.key,
        section.title,
        section.questionType,
        section.questionCount,
        section.answeredCount,
        section.correctCount,
        section.wrongCount,
        section.unansweredCount,
        section.score,
        section.totalScore,
        section.accuracy,
        section.sortOrder,
      ],
    );
  }

  await client.query(
    `
    update public.practice_sessions
    set finished_at = coalesce(finished_at, $4::timestamptz),
        metadata = metadata || jsonb_build_object(
          'submittedReportId', $5::uuid,
          'score', $6::numeric,
          'accuracy', $7::numeric,
          'submittedAt', $4::timestamptz
        )
    where tenant_id = $1 and user_id = $2 and id = $3
    `,
    [tenantId, userId, practiceSessionId, submittedAt.toISOString(), report.id, score, round4(totalQuestions ? correctCount / totalQuestions : 0)],
  );

  return report;
}

export async function submitPracticeSessionRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const practiceSessionId = requiredString(body, 'practiceSessionId');

  const result = await transaction<PracticeSessionReportWithBadges>(async client => {
    const report = await buildPracticeSessionReport(client, tenantId, userId, practiceSessionId);
    const autoBadges = await grantLearningBadgesForPracticeReport(client, { tenantId, userId, report });
    return { report, autoBadges };
  });

  return {
    item: {
      ...formatReport(result.report),
      ...(result.autoBadges.length ? { autoBadges: result.autoBadges } : {}),
    },
  };
}

export async function practiceSessionReportRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const practiceSessionId = stringParam(ctx, 'practiceSessionId');
  if (!practiceSessionId) {
    throw new HttpError(400, 'practiceSessionId is required', 'PRACTICE_SESSION_ID_REQUIRED');
  }

  const report = await queryOne<PracticeSessionReportRow>(
    `
    select id, tenant_id as "tenantId", user_id as "userId",
           practice_session_id as "practiceSessionId",
           blueprint_id as "blueprintId", collection_id as "collectionId", mode,
           total_questions as "totalQuestions", answered_count as "answeredCount",
           correct_count as "correctCount", wrong_count as "wrongCount",
           unanswered_count as "unansweredCount",
           score, total_score as "totalScore", accuracy,
           duration_seconds as "durationSeconds",
           started_at as "startedAt", submitted_at as "submittedAt",
           section_stats as "sectionStats", question_results as "questionResults",
           wrong_question_ids as "wrongQuestionIds", metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.practice_session_reports
    where tenant_id = $1 and user_id = $2 and practice_session_id = $3
    limit 1
    `,
    [tenantId, userId, practiceSessionId],
  );
  if (!report) {
    throw new HttpError(404, 'Practice session report not found', 'PRACTICE_REPORT_NOT_FOUND');
  }

  return { item: formatReport(report) };
}

export async function practiceSessionDetailRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const practiceSessionId = stringParam(ctx, 'practiceSessionId');
  if (!practiceSessionId) {
    throw new HttpError(400, 'practiceSessionId is required', 'PRACTICE_SESSION_ID_REQUIRED');
  }

  const session = await queryOne<PracticeSessionDetailRow & Record<string, unknown>>(
    `
    select ps.id, ps.tenant_id as "tenantId", ps.user_id as "userId", ps.mode,
           ps.target_type as "targetType", ps.target_id as "targetId",
           ps.blueprint_id as "blueprintId", ps.collection_id as "collectionId",
           ps.entry_id as "entryId", ps.content_node_id as "contentNodeId",
           ps.question_ids as "questionIds", ps.question_count as "questionCount",
           ps.duration_minutes as "durationMinutes", ps.total_score as "totalScore",
           ps.access_mode as "accessMode", ps.access_entitlement_id as "accessEntitlementId",
           ps.consumed_free_quota as "consumedFreeQuota", ps.access_snapshot as "accessSnapshot",
           ps.started_at as "startedAt", ps.finished_at as "finishedAt",
           ps.expires_at as "expiresAt", ps.metadata
    from public.practice_sessions ps
    where ps.tenant_id = $1 and ps.user_id = $2 and ps.id = $3
    limit 1
    `,
    [tenantId, userId, practiceSessionId],
  );
  if (!session) {
    throw new HttpError(404, 'Practice session not found', 'PRACTICE_SESSION_NOT_FOUND');
  }

  const questionIds = idArrayFromJson(session.questionIds);
  const [questions, answers] = await Promise.all([
    questionIds.length ? query(
      `
      select q.id, q.legacy_id as "legacyId", q.entry_id as "entryId",
             q.content_node_id as "contentNodeId",
             q.primary_collection_id as "primaryCollectionId",
             q.subject_id as "subjectId", q.category_id as "categoryId", q.node_id as "nodeId",
             q.type, q.type_label as "typeLabel", q.difficulty, q.tags,
             q.exam_markers as "examMarkers",
             q.media_url as "mediaUrl", q.has_video_explanation as "hasVideoExplanation",
             v.id as "versionId", v.content, v.options,
             v.correct_option_index as "correctOptionIndex",
             v.correct_option_indices as "correctOptionIndices",
             v.answer_text as "answerText", v.explanation, v.sub_questions as "subQuestions",
             v.code_lang as "codeLang", v.code_template as "codeTemplate",
             q.created_at as "createdAt", q.updated_at as "updatedAt"
      from public.questions q
      left join public.question_versions v on v.id = q.current_version_id
      where q.tenant_id = $1 and q.id = any($2::uuid[])
      `,
      [tenantId, questionIds],
    ) : Promise.resolve([]),
    questionIds.length ? query(
      `
      select distinct on (question_id)
             id, question_id as "questionId", question_version_id as "questionVersionId",
             selected_options as "selectedOptions", answer_text as "answerText",
             answer_payload as "answerPayload", is_correct as "isCorrect", answered_at as "answeredAt"
      from public.answer_records
      where tenant_id = $1
        and user_id = $2
        and practice_session_id = $3
        and question_id = any($4::uuid[])
      order by question_id, answered_at desc
      `,
      [tenantId, userId, practiceSessionId, questionIds],
    ) : Promise.resolve([]),
  ]);
  const questionById = new Map((questions as Array<Record<string, unknown> & { id: string }>).map(item => [item.id, item]));
  const orderedQuestions = questionIds.map(id => questionById.get(id)).filter(Boolean);
  const answersByQuestion = Object.fromEntries(
    (answers as Array<Record<string, unknown> & { questionId: string }>).map(answer => [answer.questionId, {
      ...answer,
      selectedOptions: normalizeStringArray(answer.selectedOptions),
      answerPayload: jsonObject(answer.answerPayload),
    }]),
  );

  return {
    item: {
      ...formatPracticeSession(session),
      questions: orderedQuestions,
      answersByQuestion,
    },
  };
}

export async function practiceReportsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const blueprintId = stringParam(ctx, 'blueprintId');
  const mode = stringParam(ctx, 'mode');
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query<PracticeSessionReportRow>(
    `
    select id, tenant_id as "tenantId", user_id as "userId",
           practice_session_id as "practiceSessionId",
           blueprint_id as "blueprintId", collection_id as "collectionId", mode,
           total_questions as "totalQuestions", answered_count as "answeredCount",
           correct_count as "correctCount", wrong_count as "wrongCount",
           unanswered_count as "unansweredCount",
           score, total_score as "totalScore", accuracy,
           duration_seconds as "durationSeconds",
           started_at as "startedAt", submitted_at as "submittedAt",
           section_stats as "sectionStats", question_results as "questionResults",
           wrong_question_ids as "wrongQuestionIds", metadata,
           created_at as "createdAt", updated_at as "updatedAt"
    from public.practice_session_reports
    where tenant_id = $1 and user_id = $2
      and ($3::uuid is null or blueprint_id = $3::uuid)
      and ($4::text = '' or mode = $4::text)
    order by submitted_at desc
    limit $5
    `,
    [tenantId, userId, blueprintId || null, mode, limit],
  );

  return { items: items.map(formatReport) };
}

export async function practiceHistoryRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const mode = stringParam(ctx, 'mode');
  const status = stringParam(ctx, 'status');
  const limit = intParam(ctx, 'limit', 50, 200);

  const items = await query(
    `
    select ps.id, ps.mode, ps.target_type as "targetType", ps.target_id as "targetId",
           ps.blueprint_id as "blueprintId", pb.name as "blueprintName",
           ps.collection_id as "collectionId", qc.name as "collectionName",
           ps.entry_id as "entryId", ce.name as "entryName",
           ps.content_node_id as "contentNodeId", cn.name as "contentNodeName",
           ps.question_count as "questionCount", ps.duration_minutes as "durationMinutes",
           ps.total_score as "sessionTotalScore", ps.access_mode as "accessMode",
           ps.started_at as "startedAt", ps.finished_at as "finishedAt",
           ps.expires_at as "expiresAt",
           case
             when ps.finished_at is not null then 'finished'
             when ps.expires_at is not null and ps.expires_at <= now() then 'expired'
             else 'active'
           end as status,
           coalesce(ar.answered_count, 0)::integer as "answeredCount",
           coalesce(ar.correct_count, 0)::integer as "correctCount",
           coalesce(ar.wrong_count, 0)::integer as "wrongCount",
           r.id as "reportId", r.score, r.total_score as "reportTotalScore",
           r.accuracy, r.submitted_at as "submittedAt"
    from public.practice_sessions ps
    left join public.practice_blueprints pb on pb.id = ps.blueprint_id and pb.tenant_id = ps.tenant_id
    left join public.question_collections qc on qc.id = ps.collection_id and qc.tenant_id = ps.tenant_id
    left join public.content_entries ce on ce.id = ps.entry_id and ce.tenant_id = ps.tenant_id
    left join public.content_nodes cn on cn.id = ps.content_node_id and cn.tenant_id = ps.tenant_id
    left join public.practice_session_reports r on r.practice_session_id = ps.id and r.tenant_id = ps.tenant_id and r.user_id = ps.user_id
    left join lateral (
      select count(distinct question_id)::integer as answered_count,
             count(distinct question_id) filter (where is_correct is true)::integer as correct_count,
             count(distinct question_id) filter (where is_correct is false)::integer as wrong_count
      from public.answer_records
      where tenant_id = ps.tenant_id
        and user_id = ps.user_id
        and practice_session_id = ps.id
    ) ar on true
    where ps.tenant_id = $1 and ps.user_id = $2
      and ($3::text = '' or ps.mode = $3::text)
      and (
        $4::text = ''
        or ($4::text = 'finished' and ps.finished_at is not null)
        or ($4::text = 'active' and ps.finished_at is null and (ps.expires_at is null or ps.expires_at > now()))
        or ($4::text = 'expired' and ps.finished_at is null and ps.expires_at is not null and ps.expires_at <= now())
      )
    order by coalesce(ps.finished_at, ps.started_at) desc
    limit $5
    `,
    [tenantId, userId, mode, status, limit],
  );

  return { items };
}

export async function learningStatsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const days = intParam(ctx, 'days', 30, 365);

  const [answerStats, sessionStats, reportStats, wrongStats, favoriteStats, typeStats] = await Promise.all([
    queryOne<{
      totalAnswered: string;
      correctCount: string;
      wrongCount: string;
      todayAnswered: string;
      latestAnsweredAt: string | null;
    }>(
      `
      select count(*)::text as "totalAnswered",
             count(*) filter (where is_correct is true)::text as "correctCount",
             count(*) filter (where is_correct is false)::text as "wrongCount",
             count(*) filter (where answered_at::date = current_date)::text as "todayAnswered",
             max(answered_at) as "latestAnsweredAt"
      from public.answer_records
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId],
    ),
    queryOne<{
      totalSessions: string;
      activeSessions: string;
      finishedSessions: string;
      latestStartedAt: string | null;
    }>(
      `
      select count(*)::text as "totalSessions",
             count(*) filter (where finished_at is null and (expires_at is null or expires_at > now()))::text as "activeSessions",
             count(*) filter (where finished_at is not null)::text as "finishedSessions",
             max(started_at) as "latestStartedAt"
      from public.practice_sessions
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId],
    ),
    queryOne<{
      reportCount: string;
      avgAccuracy: string | null;
      bestScore: string | null;
      latestSubmittedAt: string | null;
    }>(
      `
      select count(*)::text as "reportCount",
             avg(accuracy)::text as "avgAccuracy",
             max(score)::text as "bestScore",
             max(submitted_at) as "latestSubmittedAt"
      from public.practice_session_reports
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId],
    ),
    queryOne<{
      unresolvedWrong: string;
      resolvedWrong: string;
      totalWrongBook: string;
    }>(
      `
      select count(*) filter (where resolved_at is null)::text as "unresolvedWrong",
             count(*) filter (where resolved_at is not null)::text as "resolvedWrong",
             count(*)::text as "totalWrongBook"
      from public.wrong_questions
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId],
    ),
    queryOne<{ favoriteQuestions: string }>(
      `
      select count(*)::text as "favoriteQuestions"
      from public.favorite_questions
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId],
    ),
    query<{
      questionType: string;
      typeLabel: string | null;
      answeredCount: string;
      correctCount: string;
      wrongCount: string;
    }>(
      `
      select q.type as "questionType", q.type_label as "typeLabel",
             count(*)::text as "answeredCount",
             count(*) filter (where ar.is_correct is true)::text as "correctCount",
             count(*) filter (where ar.is_correct is false)::text as "wrongCount"
      from public.answer_records ar
      join public.questions q on q.id = ar.question_id and q.tenant_id = ar.tenant_id
      where ar.tenant_id = $1
        and ar.user_id = $2
        and ar.answered_at >= current_date - ($3::integer * interval '1 day')
      group by q.type, q.type_label
      order by count(*) desc, q.type asc
      limit 20
      `,
      [tenantId, userId, days],
    ),
  ]);

  const totalAnswered = Number(answerStats?.totalAnswered || 0);
  const correctCount = Number(answerStats?.correctCount || 0);
  const wrongCount = Number(answerStats?.wrongCount || 0);
  return {
    item: {
      windowDays: days,
      answers: {
        totalAnswered,
        correctCount,
        wrongCount,
        todayAnswered: Number(answerStats?.todayAnswered || 0),
        accuracy: round4(totalAnswered ? correctCount / totalAnswered : 0),
        latestAnsweredAt: answerStats?.latestAnsweredAt || null,
      },
      sessions: {
        totalSessions: Number(sessionStats?.totalSessions || 0),
        activeSessions: Number(sessionStats?.activeSessions || 0),
        finishedSessions: Number(sessionStats?.finishedSessions || 0),
        latestStartedAt: sessionStats?.latestStartedAt || null,
      },
      reports: {
        reportCount: Number(reportStats?.reportCount || 0),
        avgAccuracy: round4(finiteNumber(reportStats?.avgAccuracy, 0)),
        bestScore: round2(finiteNumber(reportStats?.bestScore, 0)),
        latestSubmittedAt: reportStats?.latestSubmittedAt || null,
      },
      wrongBook: {
        unresolvedWrong: Number(wrongStats?.unresolvedWrong || 0),
        resolvedWrong: Number(wrongStats?.resolvedWrong || 0),
        totalWrongBook: Number(wrongStats?.totalWrongBook || 0),
      },
      favorites: {
        favoriteQuestions: Number(favoriteStats?.favoriteQuestions || 0),
      },
      questionTypes: typeStats.map(item => {
        const answered = Number(item.answeredCount || 0);
        const correct = Number(item.correctCount || 0);
        return {
          questionType: item.questionType,
          typeLabel: item.typeLabel,
          answeredCount: answered,
          correctCount: correct,
          wrongCount: Number(item.wrongCount || 0),
          accuracy: round4(answered ? correct / answered : 0),
        };
      }),
    },
  };
}

export async function learningTrendRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const days = intParam(ctx, 'days', 14, 365);
  const from = dateParam(ctx, 'from');
  const to = dateParam(ctx, 'to');

  const rows = await query<TrendMetric>(
    `
    with bounds as (
      select
        coalesce($3::date, current_date - (($5::integer - 1) * interval '1 day'))::date as start_date,
        coalesce($4::date, current_date)::date as end_date
    ),
    dates as (
      select generate_series(start_date, end_date, interval '1 day')::date as stat_date
      from bounds
    ),
    answers as (
      select answered_at::date as stat_date,
             count(*)::integer as answered_count,
             count(*) filter (where is_correct is true)::integer as correct_count,
             count(*) filter (where is_correct is false)::integer as wrong_count
      from public.answer_records, bounds
      where tenant_id = $1 and user_id = $2
        and answered_at::date between bounds.start_date and bounds.end_date
      group by answered_at::date
    ),
    sessions as (
      select started_at::date as stat_date, count(*)::integer as session_count
      from public.practice_sessions, bounds
      where tenant_id = $1 and user_id = $2
        and started_at::date between bounds.start_date and bounds.end_date
      group by started_at::date
    ),
    reports as (
      select submitted_at::date as stat_date,
             count(*)::integer as report_count,
             coalesce(sum(score), 0)::numeric as score,
             coalesce(sum(total_score), 0)::numeric as total_score
      from public.practice_session_reports, bounds
      where tenant_id = $1 and user_id = $2
        and submitted_at::date between bounds.start_date and bounds.end_date
      group by submitted_at::date
    )
    select dates.stat_date::text as date,
           coalesce(answers.answered_count, 0)::integer as "answeredCount",
           coalesce(answers.correct_count, 0)::integer as "correctCount",
           coalesce(answers.wrong_count, 0)::integer as "wrongCount",
           case when coalesce(answers.answered_count, 0) = 0 then 0
                else round((answers.correct_count::numeric / answers.answered_count::numeric), 4)
           end as accuracy,
           coalesce(sessions.session_count, 0)::integer as "sessionCount",
           coalesce(reports.report_count, 0)::integer as "reportCount",
           coalesce(reports.score, 0)::numeric as score,
           coalesce(reports.total_score, 0)::numeric as "totalScore"
    from dates
    left join answers on answers.stat_date = dates.stat_date
    left join sessions on sessions.stat_date = dates.stat_date
    left join reports on reports.stat_date = dates.stat_date
    order by dates.stat_date asc
    `,
    [tenantId, userId, from || null, to || null, days],
  );

  return {
    items: rows.map(row => ({
      ...row,
      answeredCount: Number(row.answeredCount || 0),
      correctCount: Number(row.correctCount || 0),
      wrongCount: Number(row.wrongCount || 0),
      accuracy: finiteNumber(row.accuracy, 0),
      sessionCount: Number(row.sessionCount || 0),
      reportCount: Number(row.reportCount || 0),
      score: finiteNumber(row.score, 0),
      totalScore: finiteNumber(row.totalScore, 0),
    })),
  };
}

export async function wrongQuestionReviewPlanRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 20, 100);
  const includeResolved = stringParam(ctx, 'includeResolved') === 'true';

  const items = await query(
    `
    select wq.question_id as "questionId", wq.wrong_count as "wrongCount",
           wq.last_wrong_at as "lastWrongAt", wq.resolved_at as "resolvedAt",
           q.type, q.type_label as "typeLabel", q.subject_id as "subjectId",
           s.name as "subjectName", q.category_id as "categoryId",
           c.name as "categoryName", q.content_node_id as "contentNodeId",
           cn.name as "contentNodeName", v.content,
           coalesce(ar.answer_attempts, 0)::integer as "answerAttempts",
           ar.last_answered_at as "lastAnsweredAt",
           case
             when wq.resolved_at is not null then 'resolved'
             when wq.wrong_count >= 3 then 'high_priority'
             when wq.last_wrong_at <= now() - interval '3 days' then 'due'
             else 'new'
           end as "reviewStatus",
           (
             wq.last_wrong_at
             + case
                 when wq.wrong_count >= 3 then interval '1 day'
                 when wq.wrong_count = 2 then interval '2 days'
                 else interval '3 days'
               end
           ) as "suggestedReviewAt"
    from public.wrong_questions wq
    join public.questions q on q.id = wq.question_id and q.tenant_id = wq.tenant_id
    left join public.question_versions v on v.id = q.current_version_id
    left join public.subjects s on s.id = q.subject_id and s.tenant_id = q.tenant_id
    left join public.categories c on c.id = q.category_id and c.tenant_id = q.tenant_id
    left join public.content_nodes cn on cn.id = q.content_node_id and cn.tenant_id = q.tenant_id
    left join lateral (
      select count(*)::integer as answer_attempts, max(answered_at) as last_answered_at
      from public.answer_records
      where tenant_id = wq.tenant_id
        and user_id = wq.user_id
        and question_id = wq.question_id
    ) ar on true
    where wq.tenant_id = $1
      and wq.user_id = $2
      and ($3::boolean = true or wq.resolved_at is null)
      and q.status = 'published'
    order by
      case
        when wq.resolved_at is not null then 4
        when wq.wrong_count >= 3 then 0
        when wq.last_wrong_at <= now() - interval '3 days' then 1
        else 2
      end,
      wq.wrong_count desc,
      wq.last_wrong_at asc
    limit $4
    `,
    [tenantId, userId, includeResolved, limit],
  );

  return {
    items,
    nextAction: {
      mode: 'wrong_review',
      endpoint: 'POST /api/learning/practice-sessions',
      body: { mode: 'wrong_review', questionLimit: Math.min(limit, 50) },
    },
  };
}

export async function favoriteQuestionsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 100, 500);

  const items = await query(
    `
    select fq.question_id as "questionId", fq.source, fq.created_at as "createdAt",
           q.subject_id as "subjectId", q.category_id as "categoryId",
           q.type, q.type_label as "typeLabel", v.content
    from public.favorite_questions fq
    join public.questions q on q.id = fq.question_id and q.tenant_id = fq.tenant_id
    left join public.question_versions v on v.id = q.current_version_id
    where fq.tenant_id = $1 and fq.user_id = $2
    order by fq.created_at desc
    limit $3
    `,
    [tenantId, userId, limit],
  );

  return { items };
}

export async function toggleFavoriteQuestionRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const questionId = requiredString(body, 'questionId');
  const favorite = body.favorite !== false;

  if (favorite) {
    await query(
      `
      insert into public.favorite_questions (tenant_id, user_id, question_id, source)
      values ($1, $2, $3, 'api')
      on conflict (tenant_id, user_id, question_id) do nothing
      `,
      [tenantId, userId, questionId],
    );
  } else {
    await query(
      `
      delete from public.favorite_questions
      where tenant_id = $1 and user_id = $2 and question_id = $3
      `,
      [tenantId, userId, questionId],
    );
  }

  return { ok: true, favorite };
}

export async function wrongQuestionsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const unresolvedOnly = stringParam(ctx, 'status') !== 'all';
  const limit = intParam(ctx, 'limit', 100, 500);

  const items = await query(
    `
    select wq.question_id as "questionId", wq.wrong_count as "wrongCount",
           wq.last_wrong_at as "lastWrongAt", wq.resolved_at as "resolvedAt",
           q.subject_id as "subjectId", q.category_id as "categoryId",
           q.type, q.type_label as "typeLabel", v.content
    from public.wrong_questions wq
    join public.questions q on q.id = wq.question_id and q.tenant_id = wq.tenant_id
    left join public.question_versions v on v.id = q.current_version_id
    where wq.tenant_id = $1 and wq.user_id = $2
      and ($3::boolean = false or wq.resolved_at is null)
    order by wq.last_wrong_at desc
    limit $4
    `,
    [tenantId, userId, unresolvedOnly, limit],
  );

  return { items };
}

export async function resolveWrongQuestionRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const questionId = requiredString(body, 'questionId');

  await query(
    `
    update public.wrong_questions
    set resolved_at = now()
    where tenant_id = $1 and user_id = $2 and question_id = $3
    `,
    [tenantId, userId, questionId],
  );

  return { ok: true };
}

function normalizeWordStatus(value: string) {
  if (['new', 'learning', 'mastered', 'reviewing'].includes(value)) return value;
  return 'learning';
}

function normalizeWordReviewResult(value: string) {
  if (['correct', 'known', 'mastered'].includes(value)) return 'known';
  if (['wrong', 'unknown', 'again'].includes(value)) return 'unknown';
  return 'unknown';
}

function computeWordReviewSchedule(input: {
  result: 'known' | 'unknown';
  correctCount: number;
  wrongCount: number;
  reviewCount: number;
  correctStreak: number;
  easeFactor: number;
}) {
  const known = input.result === 'known';
  const correctCount = input.correctCount + (known ? 1 : 0);
  const wrongCount = input.wrongCount + (known ? 0 : 1);
  const reviewCount = input.reviewCount + 1;
  const correctStreak = known ? input.correctStreak + 1 : 0;
  const easeFactor = Math.max(1.3, Math.min(3, input.easeFactor + (known ? 0.08 : -0.2)));
  const intervalDays = known
    ? correctStreak <= 1
      ? 1
      : correctStreak === 2
        ? 3
        : correctStreak === 3
          ? 7
          : Math.min(60, Math.round(7 * Math.pow(easeFactor, correctStreak - 3)))
    : 0;
  const status = known
    ? correctStreak >= 4
      ? 'mastered'
      : 'reviewing'
    : 'learning';
  const dueLevel = known
    ? status === 'mastered'
      ? 'mastered'
      : correctStreak <= 1
        ? 'soon'
        : 'later'
    : 'again';
  const nextReviewDate = new Date();
  if (known) {
    nextReviewDate.setDate(nextReviewDate.getDate() + intervalDays);
  } else {
    nextReviewDate.setHours(nextReviewDate.getHours() + 6);
  }

  return {
    correctCount,
    wrongCount,
    reviewCount,
    correctStreak,
    easeFactor: round2(easeFactor),
    intervalDays,
    status,
    dueLevel,
    nextReviewDate: nextReviewDate.toISOString(),
  };
}

export async function wordProgressRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const unitId = stringParam(ctx, 'unitId');
  const status = stringParam(ctx, 'status');
  const limit = intParam(ctx, 'limit', 500, 2000);

  const items = await query(
    `
    select p.id, p.word_id as "wordId", p.status,
           p.correct_count as "correctCount", p.wrong_count as "wrongCount",
           p.review_count as "reviewCount", p.correct_streak as "correctStreak",
           p.ease_factor as "easeFactor", p.last_result as "lastResult",
           p.due_level as "dueLevel",
           p.last_review_date as "lastReviewDate", p.next_review_date as "nextReviewDate",
           p.created_at as "createdAt", p.updated_at as "updatedAt",
           w.unit_id as "unitId", w.word, w.phonetic, w.meaning
    from public.user_word_progress p
    join public.vocabulary_words w on w.id = p.word_id and w.tenant_id = p.tenant_id
    where p.tenant_id = $1 and p.user_id = $2
      and ($3::uuid is null or w.unit_id = $3::uuid)
      and ($4::text = '' or p.status = $4::text)
    order by p.updated_at desc
    limit $5
    `,
    [tenantId, userId, unitId || null, status, limit],
  );

  return { items };
}

export async function wordReviewPlanRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const unitId = stringParam(ctx, 'unitId');
  const reviewLimit = intParam(ctx, 'reviewLimit', 30, 200);
  const newLimit = intParam(ctx, 'newLimit', 20, 100);

  const dueWords = await query(
    `
    select p.word_id as "wordId", p.status,
           p.correct_count as "correctCount", p.wrong_count as "wrongCount",
           p.review_count as "reviewCount", p.correct_streak as "correctStreak",
           p.ease_factor as "easeFactor", p.due_level as "dueLevel",
           p.last_review_date as "lastReviewDate", p.next_review_date as "nextReviewDate",
           w.unit_id as "unitId", w.word, w.phonetic, w.meaning,
           w.example, w.example_translation as "exampleTranslation",
           w.difficulty, w.tags
    from public.user_word_progress p
    join public.vocabulary_words w on w.id = p.word_id and w.tenant_id = p.tenant_id
    where p.tenant_id = $1
      and p.user_id = $2
      and w.is_active = true
      and ($3::uuid is null or w.unit_id = $3::uuid)
      and (
        p.next_review_date is null
        or p.next_review_date <= now()
        or p.due_level in ('again', 'new')
      )
      and p.status <> 'mastered'
    order by
      case p.due_level when 'again' then 0 when 'new' then 1 when 'soon' then 2 else 3 end,
      p.next_review_date asc nulls first,
      p.wrong_count desc,
      w.sort_order asc
    limit $4
    `,
    [tenantId, userId, unitId || null, reviewLimit],
  );

  const newWords = await query(
    `
    select w.id as "wordId", 'new' as status,
           0 as "correctCount", 0 as "wrongCount", 0 as "reviewCount",
           0 as "correctStreak", 2.5 as "easeFactor", 'new' as "dueLevel",
           null::timestamptz as "lastReviewDate", null::timestamptz as "nextReviewDate",
           w.unit_id as "unitId", w.word, w.phonetic, w.meaning,
           w.example, w.example_translation as "exampleTranslation",
           w.difficulty, w.tags
    from public.vocabulary_words w
    where w.tenant_id = $1
      and w.is_active = true
      and ($2::uuid is null or w.unit_id = $2::uuid)
      and not exists (
        select 1 from public.user_word_progress p
        where p.tenant_id = w.tenant_id
          and p.user_id = $3
          and p.word_id = w.id
      )
    order by w.sort_order asc, w.created_at asc
    limit $4
    `,
    [tenantId, unitId || null, userId, newLimit],
  );

  return {
    item: {
      unitId: unitId || null,
      reviewLimit,
      newLimit,
      dueCount: dueWords.length,
      newCount: newWords.length,
      totalPlanned: dueWords.length + newWords.length,
      dueWords,
      newWords,
      words: [...dueWords, ...newWords],
    },
  };
}

export async function reviewWordRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const wordId = requiredString(body, 'wordId');
  const result = normalizeWordReviewResult(optionalString(body, 'result') || optionalString(body, 'answerResult'));

  const item = await transaction(async client => {
    const word = await client.query<{ id: string }>(
      `
      select id from public.vocabulary_words
      where tenant_id = $1 and id = $2 and is_active = true
      limit 1
      `,
      [tenantId, wordId],
    );
    if (!word.rows[0]) throw new HttpError(404, 'Vocabulary word not found', 'WORD_NOT_FOUND');

    const existing = await client.query<WordProgressReviewRow>(
      `
      select id, word_id as "wordId", status,
             correct_count as "correctCount", wrong_count as "wrongCount",
             review_count as "reviewCount", correct_streak as "correctStreak",
             ease_factor as "easeFactor", next_review_date as "nextReviewDate"
      from public.user_word_progress
      where tenant_id = $1 and user_id = $2 and word_id = $3
      for update
      `,
      [tenantId, userId, wordId],
    );
    const current = existing.rows[0] || {
      id: '',
      wordId,
      status: 'new',
      correctCount: 0,
      wrongCount: 0,
      reviewCount: 0,
      correctStreak: 0,
      easeFactor: 2.5,
      nextReviewDate: null,
    };
    const schedule = computeWordReviewSchedule({
      result,
      correctCount: Number(current.correctCount || 0),
      wrongCount: Number(current.wrongCount || 0),
      reviewCount: Number(current.reviewCount || 0),
      correctStreak: Number(current.correctStreak || 0),
      easeFactor: finiteNumber(current.easeFactor, 2.5),
    });

    const progress = await client.query(
      `
      insert into public.user_word_progress (
        tenant_id, user_id, word_id, status, correct_count, wrong_count,
        review_count, correct_streak, ease_factor, last_result, due_level,
        last_review_date, next_review_date, metadata
      )
      values (
        $1, $2, $3, $4, $5, $6,
        $7, $8, $9, $10, $11,
        now(), $12::timestamptz, $13::jsonb
      )
      on conflict (tenant_id, user_id, word_id)
      do update set status = excluded.status,
                    correct_count = excluded.correct_count,
                    wrong_count = excluded.wrong_count,
                    review_count = excluded.review_count,
                    correct_streak = excluded.correct_streak,
                    ease_factor = excluded.ease_factor,
                    last_result = excluded.last_result,
                    due_level = excluded.due_level,
                    last_review_date = now(),
                    next_review_date = excluded.next_review_date,
                    metadata = public.user_word_progress.metadata || excluded.metadata,
                    updated_at = now()
      returning id, word_id as "wordId", status,
                correct_count as "correctCount", wrong_count as "wrongCount",
                review_count as "reviewCount", correct_streak as "correctStreak",
                ease_factor as "easeFactor", last_result as "lastResult",
                due_level as "dueLevel",
                last_review_date as "lastReviewDate", next_review_date as "nextReviewDate",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        tenantId,
        userId,
        wordId,
        schedule.status,
        schedule.correctCount,
        schedule.wrongCount,
        schedule.reviewCount,
        schedule.correctStreak,
        schedule.easeFactor,
        result,
        schedule.dueLevel,
        schedule.nextReviewDate,
        JSON.stringify({
          source: 'review_word',
          result,
          intervalDays: schedule.intervalDays,
          reviewedAt: new Date().toISOString(),
        }),
      ],
    );

    await client.query(
      `
      update public.student_profiles
      set mastered_words_count = (
            select count(*) from public.user_word_progress
            where tenant_id = $1 and user_id = $2 and status = 'mastered'
          ),
          updated_at = now()
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId],
    );

    const item = {
      ...progress.rows[0],
      review: {
        result,
        intervalDays: schedule.intervalDays,
        nextReviewDate: schedule.nextReviewDate,
      },
    };

    const autoBadges = await grantVocabularyMasteredBadges(client, {
      tenantId,
      userId,
      progress: item,
      source: 'review_word',
    });

    return {
      ...item,
      ...(autoBadges.length ? { autoBadges } : {}),
    };
  });

  return { item };
}

export async function updateWordProgressRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const wordId = requiredString(body, 'wordId');
  const status = normalizeWordStatus(optionalString(body, 'status') || 'learning');
  const correctDelta = Math.max(0, optionalInteger(body, 'correctDelta', status === 'mastered' ? 1 : 0));
  const wrongDelta = Math.max(0, optionalInteger(body, 'wrongDelta', 0));
  const nextReviewDate = optionalString(body, 'nextReviewDate') || null;
  const lastResult = correctDelta > 0 ? 'known' : wrongDelta > 0 ? 'unknown' : null;
  const dueLevel = status === 'mastered' ? 'mastered' : wrongDelta > 0 ? 'again' : status === 'new' ? 'new' : 'soon';

  const item = await transaction(async client => {
    const word = await client.query<{ id: string }>(
      `
      select id from public.vocabulary_words
      where tenant_id = $1 and id = $2 and is_active = true
      limit 1
      `,
      [tenantId, wordId],
    );
    if (!word.rows[0]) throw new HttpError(404, 'Vocabulary word not found', 'WORD_NOT_FOUND');

    const progress = await client.query(
      `
      insert into public.user_word_progress (
        tenant_id, user_id, word_id, status, correct_count, wrong_count,
        review_count, correct_streak, last_result, due_level,
        last_review_date, next_review_date
      )
      values ($1, $2, $3, $4, $5, $6, case when $5::integer + $6::integer > 0 then 1 else 0 end, $8, $9, $10, now(), $7::timestamptz)
      on conflict (tenant_id, user_id, word_id)
      do update set status = excluded.status,
                    correct_count = public.user_word_progress.correct_count + $5,
                    wrong_count = public.user_word_progress.wrong_count + $6,
                    review_count = public.user_word_progress.review_count + case when $5::integer + $6::integer > 0 then 1 else 0 end,
                    correct_streak = case when $5 > 0 then public.user_word_progress.correct_streak + 1 when $6 > 0 then 0 else public.user_word_progress.correct_streak end,
                    last_result = coalesce(excluded.last_result, public.user_word_progress.last_result),
                    due_level = excluded.due_level,
                    last_review_date = now(),
                    next_review_date = coalesce(excluded.next_review_date, public.user_word_progress.next_review_date),
                    updated_at = now()
      returning id, word_id as "wordId", status,
                correct_count as "correctCount", wrong_count as "wrongCount",
                review_count as "reviewCount", correct_streak as "correctStreak",
                ease_factor as "easeFactor", last_result as "lastResult",
                due_level as "dueLevel",
                last_review_date as "lastReviewDate", next_review_date as "nextReviewDate",
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [tenantId, userId, wordId, status, correctDelta, wrongDelta, nextReviewDate, correctDelta > 0 ? 1 : 0, lastResult, dueLevel],
    );

    await client.query(
      `
      update public.student_profiles
      set mastered_words_count = (
            select count(*) from public.user_word_progress
            where tenant_id = $1 and user_id = $2 and status = 'mastered'
          ),
          updated_at = now()
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId],
    );

    const item = progress.rows[0];
    const autoBadges = await grantVocabularyMasteredBadges(client, {
      tenantId,
      userId,
      progress: item,
      source: 'update_word_progress',
    });

    return {
      ...item,
      ...(autoBadges.length ? { autoBadges } : {}),
    };
  });

  return { item };
}

export async function favoriteWordsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const unitId = stringParam(ctx, 'unitId');
  const limit = intParam(ctx, 'limit', 500, 2000);

  const items = await query(
    `
    select f.id, f.word_id as "wordId", f.note,
           coalesce(f.favorited_at, f.created_at) as "favoritedAt",
           w.unit_id as "unitId", w.word, w.phonetic, w.meaning,
           w.example, w.example_translation as "exampleTranslation",
           w.difficulty, w.tags
    from public.user_word_favorites f
    join public.vocabulary_words w on w.id = f.word_id and w.tenant_id = f.tenant_id
    where f.tenant_id = $1 and f.user_id = $2
      and ($3::uuid is null or w.unit_id = $3::uuid)
    order by coalesce(f.favorited_at, f.created_at) desc
    limit $4
    `,
    [tenantId, userId, unitId || null, limit],
  );

  return { items };
}

export async function toggleFavoriteWordRoute(ctx: RequestContext) {
  const body = await readJsonBody(ctx);
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx, body);
  const wordId = requiredString(body, 'wordId');
  const favorite = body.favorite !== false;
  const note = optionalString(body, 'note') || null;

  if (favorite) {
    await query(
      `
      insert into public.user_word_favorites (tenant_id, user_id, word_id, note, favorited_at)
      values ($1, $2, $3, $4, now())
      on conflict (tenant_id, user_id, word_id)
      do update set note = coalesce(excluded.note, public.user_word_favorites.note),
                    favorited_at = coalesce(public.user_word_favorites.favorited_at, now()),
                    updated_at = now()
      `,
      [tenantId, userId, wordId, note],
    );
  } else {
    await query(
      `
      delete from public.user_word_favorites
      where tenant_id = $1 and user_id = $2 and word_id = $3
      `,
      [tenantId, userId, wordId],
    );
  }

  return { ok: true, favorite };
}

export async function wordStatsRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const unitId = stringParam(ctx, 'unitId');

  const item = await queryOne(
    `
    select
      count(w.id)::integer as "totalWords",
      count(p.word_id)::integer as "progressedWords",
      count(p.word_id) filter (where p.status = 'mastered')::integer as "masteredWords",
      count(p.word_id) filter (where p.status in ('learning', 'reviewing'))::integer as "learningWords",
      count(p.word_id) filter (where p.last_review_date::date = current_date)::integer as "todayReviewed",
      (
        select count(*)
        from public.user_word_favorites f
        join public.vocabulary_words fw on fw.id = f.word_id and fw.tenant_id = f.tenant_id
        where f.tenant_id = $1 and f.user_id = $2
          and ($3::uuid is null or fw.unit_id = $3::uuid)
      )::integer as "favoriteWords"
    from public.vocabulary_words w
    left join public.user_word_progress p
      on p.word_id = w.id and p.tenant_id = w.tenant_id and p.user_id = $2
    where w.tenant_id = $1 and w.is_active = true
      and ($3::uuid is null or w.unit_id = $3::uuid)
    `,
    [tenantId, userId, unitId || null],
  );

  return { item };
}
