import type pg from 'pg';
import { HttpError, type RequestContext } from '../../core/http.js';
import { intParam, optionalString, readJsonBody, requiredString, stringParam, tenantIdFrom, userIdFrom } from '../../core/request.js';
import { query, queryOne, transaction } from '../../core/db.js';
import { autoGrantBadges } from './badges.js';

type JsonMap = Record<string, unknown>;
type AvatarPreset = 'male' | 'female';

interface ProfileRow {
  id: string;
  userId: string;
  username: string | null;
  phone: string | null;
  email: string | null;
  name: string | null;
  avatarPreset: AvatarPreset;
  primaryRole: string;
  score: number;
  regionId: string | null;
  regionName: string | null;
  selectedSchoolId: string | null;
  selectedSchoolName: string | null;
  selectedMajorId: string | null;
  selectedMajorName: string | null;
  questionsAnsweredToday: number;
  masteredWordsCount: number;
  lastCheckInDate: string | null;
  stats: JsonMap;
  progress: JsonMap;
  moduleSelections: JsonMap;
  recentActivities: unknown[];
  createdAt: string;
  updatedAt: string;
}

interface CheckInProfileRow {
  profileId: string;
  lastCheckInDate: string | null;
  score: number;
  stats: Record<string, unknown>;
}

interface CheckInRewardTaskRow {
  id: string;
  code: string;
  title: string;
  rewardPoints: number;
  claimLimitPerUser: number;
  periodType: string;
  metadata: JsonMap;
}

interface CheckInRewardGrant {
  taskId: string;
  taskCode: string;
  title: string;
  points: number;
  periodKey: string;
  ledgerId: string;
  claimId: string | null;
  rule: JsonMap;
}

interface ScoreLedgerRow {
  id: string;
  eventType: string;
  points: number;
  balanceAfter: number;
  sourceType: string | null;
  sourceId?: string | null;
  createdAt: string;
}

interface PointActivityClaimRow {
  id: string;
  taskId: string;
  periodKey: string;
  scoreEventId: string | null;
  sourceType: string | null;
  sourceId: string | null;
  status: string;
  metadata: JsonMap;
  claimedAt: string;
}

function jsonBodyValue(value: unknown) {
  return JSON.stringify(value && typeof value === 'object' ? value : {});
}

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

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

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

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

function toDateOnly(value: unknown) {
  if (!value) return null;
  if (value instanceof Date && !Number.isNaN(value.getTime())) return shanghaiDateKey(value);
  if (typeof value === 'string' && value.trim()) return value.trim().slice(0, 10);
  return null;
}

function daysBetween(dateValue: unknown, today: Date) {
  const dateText = toDateOnly(dateValue);
  if (!dateText) return null;
  const target = new Date(`${dateText}T00:00:00.000Z`);
  if (Number.isNaN(target.getTime())) return null;
  const current = new Date(Date.UTC(today.getUTCFullYear(), today.getUTCMonth(), today.getUTCDate()));
  return Math.ceil((target.getTime() - current.getTime()) / 86_400_000);
}

const FEEDBACK_TYPES = ['question_error', 'content_error', 'video_error', 'asset_error', 'system_bug', 'suggestion', 'other'];
const BADGE_CATEGORIES = ['learning', 'practice', 'vocabulary', 'mock_exam', 'activity', 'feedback', 'sales', 'system', 'custom'];
const AVATAR_PRESETS = ['male', 'female'];

function avatarPresetFrom(value: unknown): AvatarPreset | null {
  if (value === undefined) return null;
  const preset = nullableString(value);
  if (!preset || !AVATAR_PRESETS.includes(preset)) {
    throw new HttpError(400, 'avatarPreset must be male or female', 'INVALID_AVATAR_PRESET');
  }
  return preset as AvatarPreset;
}

function avatarDisplayUrl(preset: AvatarPreset) {
  return `/assets/avatars/default-${preset}.svg`;
}

function shanghaiDateParts(date = new Date()) {
  const formatter = new Intl.DateTimeFormat('en-US', {
    timeZone: 'Asia/Shanghai',
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
  });
  return Object.fromEntries(formatter.formatToParts(date).map(part => [part.type, part.value])) as {
    year: string;
    month: string;
    day: string;
  };
}

function shanghaiDateKey(date = new Date()) {
  const parts = shanghaiDateParts(date);
  return `${parts.year}-${parts.month}-${parts.day}`;
}

function shanghaiWeekKey(date = new Date()) {
  const current = new Date(`${shanghaiDateKey(date)}T00:00:00.000Z`);
  const day = current.getUTCDay() || 7;
  current.setUTCDate(current.getUTCDate() + 4 - day);
  const yearStart = new Date(Date.UTC(current.getUTCFullYear(), 0, 1));
  const week = Math.ceil((((current.getTime() - yearStart.getTime()) / 86_400_000) + 1) / 7);
  return `${current.getUTCFullYear()}-W${String(week).padStart(2, '0')}`;
}

function checkInTaskPeriodKey(task: CheckInRewardTaskRow, today: string, now = new Date()) {
  if (task.periodType === 'daily') return today;
  if (task.periodType === 'weekly') return shanghaiWeekKey(now);
  if (task.periodType === 'monthly') {
    const parts = shanghaiDateParts(now);
    return `${parts.year}-${parts.month}`;
  }
  if (task.periodType === 'once') return `once:${task.id}`;
  return `check_in:${today}`;
}

function metadataNumber(metadata: JsonMap, keys: string[]) {
  for (const key of keys) {
    if (!Object.hasOwn(metadata, key)) continue;
    const parsed = Number(metadata[key]);
    if (Number.isFinite(parsed)) return Math.trunc(parsed);
  }
  return null;
}

function checkInRuleFromMetadata(metadata: JsonMap) {
  return {
    requiredStreak: metadataNumber(metadata, ['requiredStreak', 'required_streak', 'streakDay', 'streak_day']),
    minStreak: metadataNumber(metadata, ['minStreak', 'min_streak']),
    maxStreak: metadataNumber(metadata, ['maxStreak', 'max_streak']),
    streakMultipleOf: metadataNumber(metadata, ['streakMultipleOf', 'streak_multiple_of']),
  };
}

function checkInTaskMatches(task: CheckInRewardTaskRow, streak: number) {
  const metadata = objectValue(task.metadata);
  const rule = checkInRuleFromMetadata(metadata);
  if (rule.requiredStreak !== null && streak !== rule.requiredStreak) return false;
  if (rule.minStreak !== null && streak < rule.minStreak) return false;
  if (rule.maxStreak !== null && streak > rule.maxStreak) return false;
  if (rule.streakMultipleOf !== null && (rule.streakMultipleOf <= 0 || streak % rule.streakMultipleOf !== 0)) return false;
  return true;
}

async function loadCheckInRewardTasks(client: pg.PoolClient, tenantId: string) {
  const result = await client.query<CheckInRewardTaskRow>(
    `
    select id, code::text, title, reward_points as "rewardPoints",
           claim_limit_per_user as "claimLimitPerUser",
           period_type as "periodType", metadata
    from public.point_activity_tasks
    where tenant_id = $1
      and task_type = 'daily_check_in'
      and status = 'active'
      and (valid_from is null or valid_from <= now())
      and (valid_to is null or valid_to >= now())
    order by sort_order asc, created_at asc
    limit 50
    for update
    `,
    [tenantId],
  );
  return result.rows;
}

async function grantConfiguredCheckInRewards(
  client: pg.PoolClient,
  input: {
    tenantId: string;
    userId: string;
    profileId: string;
    today: string;
    now: Date;
    streak: number;
    scoreBefore: number;
    tasks: CheckInRewardTaskRow[];
  },
) {
  const rewardBreakdown: CheckInRewardGrant[] = [];
  const ledgers: ScoreLedgerRow[] = [];
  const claims: PointActivityClaimRow[] = [];
  let pointsAdded = 0;
  let scoreCursor = input.scoreBefore;

  for (const task of input.tasks) {
    if (!checkInTaskMatches(task, input.streak)) continue;
    const periodKey = checkInTaskPeriodKey(task, input.today, input.now);
    const claimCounts = await client.query<{ totalCount: string; periodCount: string }>(
      `
      select count(*)::text as "totalCount",
             count(*) filter (where period_key = $4)::text as "periodCount"
      from public.user_point_activity_claims
      where tenant_id = $1 and user_id = $2 and task_id = $3 and status = 'claimed'
      `,
      [input.tenantId, input.userId, task.id, periodKey],
    );
    const periodic = ['daily', 'weekly', 'monthly'].includes(task.periodType);
    const currentLimitCount = Number(periodic ? claimCounts.rows[0]?.periodCount || 0 : claimCounts.rows[0]?.totalCount || 0);
    const effectiveLimit = periodic ? 1 : Math.max(1, Number(task.claimLimitPerUser || 1));
    if (currentLimitCount >= effectiveLimit) continue;

    const points = Number(task.rewardPoints || 0);
    scoreCursor += points;
    const metadata = objectValue(task.metadata);
    const rule = checkInRuleFromMetadata(metadata) as JsonMap;
    const ledger = await client.query<{
      id: string;
      eventType: string;
      points: number;
      balanceAfter: number;
      sourceType: string | null;
      sourceId: string | null;
      createdAt: string;
    }>(
      `
      insert into public.user_score_events (
        tenant_id, user_id, event_type, points, balance_after,
        source_type, source_id, idempotency_key, metadata
      )
      values ($1, $2, 'check_in', $3, $4, 'point_activity_tasks', $5, $6, $7::jsonb)
      on conflict (tenant_id, idempotency_key) do nothing
      returning id, event_type as "eventType", points, balance_after as "balanceAfter",
                source_type as "sourceType", source_id as "sourceId", created_at as "createdAt"
      `,
      [
        input.tenantId,
        input.userId,
        points,
        scoreCursor,
        task.id,
        `check_in:${task.id}:user:${input.userId}:period:${periodKey}`,
        JSON.stringify({
          taskId: task.id,
          taskCode: task.code,
          taskType: 'daily_check_in',
          checkInDate: input.today,
          streak: input.streak,
          periodKey,
          rule,
        }),
      ],
    );

    if (!ledger.rows[0]) {
      scoreCursor -= points;
      continue;
    }

    const claim = await client.query<PointActivityClaimRow>(
      `
      insert into public.user_point_activity_claims (
        tenant_id, user_id, task_id, period_key, score_event_id,
        source_type, source_id, metadata
      )
      values ($1, $2, $3, $4, $5, 'student_profiles', $6, $7::jsonb)
      on conflict (tenant_id, user_id, task_id, period_key) do nothing
      returning id, task_id as "taskId", period_key as "periodKey",
                score_event_id as "scoreEventId", source_type as "sourceType",
                source_id as "sourceId", status, metadata, claimed_at as "claimedAt"
      `,
      [
        input.tenantId,
        input.userId,
        task.id,
        periodKey,
        ledger.rows[0].id,
        input.profileId,
        JSON.stringify({
          source: 'check_in',
          checkInDate: input.today,
          streak: input.streak,
          taskCode: task.code,
          rule,
        }),
      ],
    );

    if (!claim.rows[0]) {
      await client.query(
        `
        delete from public.user_score_events
        where tenant_id = $1 and id = $2
        `,
        [input.tenantId, ledger.rows[0].id],
      );
      scoreCursor -= points;
      continue;
    }

    pointsAdded += points;
    ledgers.push(ledger.rows[0]);
    claims.push(claim.rows[0]);
    rewardBreakdown.push({
      taskId: task.id,
      taskCode: task.code,
      title: task.title,
      points,
      periodKey,
      ledgerId: ledger.rows[0].id,
      claimId: claim.rows[0]?.id || null,
      rule,
    });
  }

  return {
    pointsAdded,
    scoreAfter: scoreCursor,
    rewardBreakdown,
    ledgers,
    claims: claims.filter(Boolean),
  };
}

export async function profileMeRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'recentLimit', 8, 50);

  const profile = await queryOne<ProfileRow>(
    `
    select sp.id, u.id as "userId", u.username, u.phone, u.email::text, u.name,
           coalesce(sp.avatar_preset, 'male') as "avatarPreset",
           u.primary_role as "primaryRole", u.score,
           sp.region_id as "regionId", r.name as "regionName",
           sp.selected_school_id as "selectedSchoolId", s.name as "selectedSchoolName",
           sp.selected_major_id as "selectedMajorId", m.name as "selectedMajorName",
           sp.questions_answered_today as "questionsAnsweredToday",
           sp.mastered_words_count as "masteredWordsCount",
           sp.last_check_in_date as "lastCheckInDate",
           sp.stats, sp.progress, sp.module_selections as "moduleSelections",
           sp.recent_activities as "recentActivities",
           sp.created_at as "createdAt", sp.updated_at as "updatedAt"
    from public.student_profiles sp
    join public.platform_users u on u.id = sp.user_id
    left join public.regions r on r.id = sp.region_id and r.tenant_id = sp.tenant_id
    left join public.schools s on s.id = sp.selected_school_id and s.tenant_id = sp.tenant_id
    left join public.majors m on m.id = sp.selected_major_id and m.tenant_id = sp.tenant_id
    where sp.tenant_id = $1 and sp.user_id = $2
    limit 1
    `,
    [tenantId, userId],
  );

  if (!profile) {
    throw new HttpError(404, 'Student profile not found', 'PROFILE_NOT_FOUND');
  }

  const recentPractices = await query(
    `
    select id, practice_type as "practiceType", target_legacy_id as "targetLegacyId",
           target_name as "targetName", progress, color,
           last_access_at as "lastAccessAt", last_practice_at as "lastPracticeAt",
           metadata, created_at as "createdAt", updated_at as "updatedAt"
    from public.recent_practices
    where tenant_id = $1 and user_id = $2
    order by last_practice_at desc nulls last, last_access_at desc nulls last, updated_at desc
    limit $3
    `,
    [tenantId, userId, limit],
  );

  const answerStats = await queryOne<{
    totalAnswered: string;
    correctCount: string;
    wrongCount: 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",
           max(answered_at) as "latestAnsweredAt"
    from public.answer_records
    where tenant_id = $1 and user_id = $2
    `,
    [tenantId, userId],
  );

  const wordStats = await queryOne<{
    totalWords: string;
    progressedWords: string;
    masteredWords: string;
    learningWords: string;
    favoriteWords: string;
  }>(
    `
    select
      (select count(*) from public.vocabulary_words where tenant_id = $1 and is_active = true)::text as "totalWords",
      count(*)::text as "progressedWords",
      count(*) filter (where status = 'mastered')::text as "masteredWords",
      count(*) filter (where status in ('learning', 'reviewing'))::text as "learningWords",
      (select count(*) from public.user_word_favorites where tenant_id = $1 and user_id = $2)::text as "favoriteWords"
    from public.user_word_progress
    where tenant_id = $1 and user_id = $2
    `,
    [tenantId, userId],
  );

  const entitlement = await queryOne(
    `
    select id, entitlement_type as "entitlementType", scope_type as "scopeType",
           scope_id as "scopeId", starts_at as "startsAt", expires_at as "expiresAt",
           status, metadata
    from public.entitlements
    where tenant_id = $1 and user_id = $2
      and entitlement_type = 'svip'
      and status = 'active'
      and starts_at <= now()
      and (expires_at is null or expires_at > now())
    order by expires_at desc nulls first, created_at desc
    limit 1
    `,
    [tenantId, userId],
  );

  const orderSummary = await queryOne<{
    totalOrders: string;
    paidOrders: string;
    paidAmountCents: string;
  }>(
    `
    select count(*)::text as "totalOrders",
           count(*) filter (where status = 'paid')::text as "paidOrders",
           coalesce(sum(amount_cents) filter (where status = 'paid'), 0)::text as "paidAmountCents"
    from public.orders
    where tenant_id = $1 and user_id = $2
    `,
    [tenantId, userId],
  );

  return {
    item: {
      ...profile,
      target: {
        regionId: profile.regionId,
        regionName: profile.regionName,
        schoolId: profile.selectedSchoolId,
        schoolName: profile.selectedSchoolName,
        majorId: profile.selectedMajorId,
        majorName: profile.selectedMajorName,
      },
      membership: {
        isSvip: !!entitlement,
        entitlement,
      },
      avatar: {
        preset: profile.avatarPreset,
        displayUrl: avatarDisplayUrl(profile.avatarPreset),
      },
      stats: {
        ...profile.stats,
        answers: {
          totalAnswered: Number(answerStats?.totalAnswered || 0),
          correctCount: Number(answerStats?.correctCount || 0),
          wrongCount: Number(answerStats?.wrongCount || 0),
          latestAnsweredAt: answerStats?.latestAnsweredAt || null,
        },
        vocabulary: {
          totalWords: Number(wordStats?.totalWords || 0),
          progressedWords: Number(wordStats?.progressedWords || 0),
          masteredWords: Number(wordStats?.masteredWords || 0),
          learningWords: Number(wordStats?.learningWords || 0),
          favoriteWords: Number(wordStats?.favoriteWords || 0),
        },
        orders: {
          totalOrders: Number(orderSummary?.totalOrders || 0),
          paidOrders: Number(orderSummary?.paidOrders || 0),
          paidAmountCents: Number(orderSummary?.paidAmountCents || 0),
        },
      },
      recentPractices,
    },
  };
}

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

  const name = optionalString(body, 'name') || null;
  if (Object.hasOwn(body, 'avatarUrl')) {
    throw new HttpError(
      400,
      'avatarUrl cannot be updated directly. Use avatarPreset.',
      'PROFILE_AVATAR_URL_DIRECT_UPDATE_REJECTED',
    );
  }
  const avatarPreset = avatarPresetFrom(body.avatarPreset);
  const regionId = optionalString(body, 'regionId') || null;
  const selectedSchoolId = optionalString(body, 'selectedSchoolId') || null;
  const selectedMajorId = optionalString(body, 'selectedMajorId') || null;

  const item = await queryOne(
    `
    with updated_user as (
      update public.platform_users
      set name = coalesce($3, name),
          updated_at = now()
      where id = $2
      returning id
    )
    insert into public.student_profiles (
      tenant_id, user_id, avatar_preset, region_id, selected_school_id, selected_major_id,
      stats, progress, module_selections, recent_activities
    )
    values ($1, $2, coalesce($4, 'male'), $5::uuid, $6::uuid, $7::uuid, $8::jsonb, $9::jsonb, $10::jsonb, $11::jsonb)
    on conflict (tenant_id, user_id)
    do update set avatar_preset = coalesce($15, public.student_profiles.avatar_preset),
                  region_id = coalesce(excluded.region_id, public.student_profiles.region_id),
                  selected_school_id = coalesce(excluded.selected_school_id, public.student_profiles.selected_school_id),
                  selected_major_id = coalesce(excluded.selected_major_id, public.student_profiles.selected_major_id),
                  stats = case when $12::boolean then excluded.stats else public.student_profiles.stats end,
                  progress = case when $13::boolean then excluded.progress else public.student_profiles.progress end,
                  module_selections = case when $14::boolean then excluded.module_selections else public.student_profiles.module_selections end,
                  recent_activities = case when $16::boolean then excluded.recent_activities else public.student_profiles.recent_activities end,
                  updated_at = now()
    returning tenant_id as "tenantId", user_id as "userId", avatar_preset as "avatarPreset", region_id as "regionId",
              selected_school_id as "selectedSchoolId", selected_major_id as "selectedMajorId",
              stats, progress, module_selections as "moduleSelections",
              recent_activities as "recentActivities", updated_at as "updatedAt"
    `,
    [
      tenantId,
      userId,
      name,
      avatarPreset,
      regionId,
      selectedSchoolId,
      selectedMajorId,
      jsonBodyValue(body.stats),
      jsonBodyValue(body.progress),
      jsonBodyValue(body.moduleSelections),
      jsonArrayBodyValue(body.recentActivities),
      Object.hasOwn(body, 'stats'),
      Object.hasOwn(body, 'progress'),
      Object.hasOwn(body, 'moduleSelections'),
      avatarPreset,
      Object.hasOwn(body, 'recentActivities'),
    ],
  );

  return { item };
}

export async function checkInRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);

  const item = await transaction(async client => {
    const now = new Date();
    const today = shanghaiDateKey(now);
    const existing = await client.query<CheckInProfileRow>(
      `
      select sp.id as "profileId", sp.last_check_in_date as "lastCheckInDate", u.score, sp.stats
      from public.student_profiles sp
      join public.platform_users u on u.id = sp.user_id
      where sp.tenant_id = $1 and sp.user_id = $2
      limit 1
      for update of sp, u
      `,
      [tenantId, userId],
    );
    const profile = existing.rows[0];
    if (!profile) throw new HttpError(404, 'Student profile not found', 'PROFILE_NOT_FOUND');
    if (toDateOnly(profile.lastCheckInDate) === today) {
      return {
        checkedIn: false,
        alreadyCheckedIn: true,
        pointsAdded: 0,
        score: Number(profile.score || 0),
        lastCheckInDate: today,
        rewardBreakdown: [],
      };
    }

    const yesterdayText = shanghaiDateKey(new Date(now.getTime() - 86_400_000));
    const stats = objectValue(profile.stats);
    const previousStreak = Number(stats.checkInStreak || 0);
    const streak = toDateOnly(profile.lastCheckInDate) === yesterdayText ? previousStreak + 1 : 1;
    const scoreBefore = Number(profile.score || 0);
    const configuredTasks = await loadCheckInRewardTasks(client, tenantId);
    const configuredRewards = await grantConfiguredCheckInRewards(client, {
      tenantId,
      userId,
      profileId: profile.profileId,
      today,
      now,
      streak,
      scoreBefore,
      tasks: configuredTasks,
    });
    let rewardMode = configuredTasks.length ? 'configured_tasks' : 'legacy_formula';
    let pointsAdded = configuredRewards.pointsAdded;
    let ledgers = configuredRewards.ledgers;
    let claims = configuredRewards.claims;
    let rewardBreakdown: Array<CheckInRewardGrant | Record<string, unknown>> = configuredRewards.rewardBreakdown;
    let balanceAfter = configuredRewards.scoreAfter;

    if (!configuredTasks.length) {
      pointsAdded = 10 + Math.min(Math.max(streak - 1, 0), 6);
      balanceAfter = scoreBefore + pointsAdded;
      const ledger = await client.query(
        `
        insert into public.user_score_events (
          tenant_id, user_id, event_type, points, balance_after,
          source_type, idempotency_key, metadata
        )
        values ($1, $2, 'check_in', $3, $4, 'student_profiles', $5, $6::jsonb)
        on conflict (tenant_id, idempotency_key) do nothing
        returning id, event_type as "eventType", points, balance_after as "balanceAfter",
                  source_type as "sourceType", created_at as "createdAt"
        `,
        [
          tenantId,
          userId,
          pointsAdded,
          balanceAfter,
          `check_in:${userId}:${today}`,
          JSON.stringify({ checkInDate: today, streak, rewardMode }),
        ],
      );

      if (!ledger.rows[0]) {
        return {
          checkedIn: false,
          alreadyCheckedIn: true,
          pointsAdded: 0,
          score: scoreBefore,
          lastCheckInDate: today,
          rewardBreakdown: [],
        };
      }

      ledgers = ledger.rows;
      rewardBreakdown = [{
        rewardType: 'legacy_formula',
        title: '每日签到',
        points: pointsAdded,
        streak,
      }];
    }

    const nextStats = {
      ...stats,
      checkInStreak: streak,
      lastCheckInPoints: pointsAdded,
      lastCheckInRewardMode: rewardMode,
      checkInRewardBreakdown: rewardBreakdown.map(item => ({
        taskId: 'taskId' in item ? item.taskId : undefined,
        taskCode: 'taskCode' in item ? item.taskCode : undefined,
        title: item.title,
        points: item.points,
        periodKey: 'periodKey' in item ? item.periodKey : undefined,
        rewardType: 'rewardType' in item ? item.rewardType : undefined,
      })),
    };

    let score = balanceAfter;
    if (pointsAdded > 0) {
      const updatedUser = await client.query<{ score: number }>(
        `
        update public.platform_users
        set score = score + $2,
            updated_at = now()
        where id = $1
        returning score
        `,
        [userId, pointsAdded],
      );
      score = Number(updatedUser.rows[0]?.score || balanceAfter);
    }

    await client.query(
      `
      update public.student_profiles
      set last_check_in_date = $3::date,
          stats = $4::jsonb,
          updated_at = now()
      where tenant_id = $1 and user_id = $2
      `,
      [tenantId, userId, today, JSON.stringify(nextStats)],
    );

    const autoBadges = [
      ...(await autoGrantBadges(client, {
        tenantId,
        userId,
        trigger: 'check_in',
        evidence: {
          checkInDate: today,
          checkInStreak: streak,
          lastCheckInPoints: pointsAdded,
          rewardBreakdown,
          rewardMode,
          score,
        },
      })),
      ...(pointsAdded > 0
        ? await autoGrantBadges(client, {
            tenantId,
            userId,
            trigger: 'score',
            evidence: {
              source: 'check_in',
              checkInDate: today,
              checkInStreak: streak,
              lastCheckInPoints: pointsAdded,
              rewardBreakdown,
              rewardMode,
              score,
            },
          })
        : []),
    ];

    return {
      checkedIn: true,
      alreadyCheckedIn: false,
      pointsAdded,
      streak,
      score,
      lastCheckInDate: today,
      rewardMode,
      rewardBreakdown,
      claims,
      ledgers,
      ledger: ledgers[ledgers.length - 1] || null,
      autoBadges,
    };
  });

  return { item };
}

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

  const items = await query(
    `
    select id, event_type as "eventType", points, balance_after as "balanceAfter",
           source_type as "sourceType", source_id as "sourceId",
           metadata, created_at as "createdAt"
    from public.user_score_events
    where tenant_id = $1 and user_id = $2
    order by created_at desc
    limit $3
    `,
    [tenantId, userId, limit],
  );
  return { items };
}

export async function profileBadgesRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 100, 300);
  const includeLocked = ctx.url.searchParams.get('includeLocked') === 'true';
  const category = stringParam(ctx, 'category');
  const params: unknown[] = [tenantId, userId];
  const filters = ['b.tenant_id = $1', 'b.is_active = true'];
  if (category) {
    if (!BADGE_CATEGORIES.includes(category)) {
      throw new HttpError(400, `Invalid badge category: ${category}`, 'INVALID_BADGE_CATEGORY');
    }
    params.push(category);
    filters.push(`b.category = $${params.length}`);
  }
  if (!includeLocked) {
    filters.push('ub.id is not null');
  }
  params.push(limit);

  const items = await query<{ unlocked: boolean } & Record<string, unknown>>(
    `
    select b.id as "badgeId", b.legacy_id as "legacyId", b.name,
           b.description, b.category, b.icon_url as "iconUrl", b.level,
           b.unlock_type as "unlockType", b.condition_field as "conditionField",
           b.condition_operator as "conditionOperator", b.condition_value as "conditionValue",
           b.condition_extra as "conditionExtra", b.metadata,
           b.sort_order as "order",
           ub.id as "grantId", ub.note, ub.metadata as "grantMetadata",
           ub.granted_at as "grantedAt",
           (ub.id is not null) as unlocked
    from public.badges b
    left join public.user_badges ub
      on ub.tenant_id = b.tenant_id
     and ub.badge_id = b.id
     and ub.user_id = $2
    where ${filters.join(' and ')}
    order by (ub.id is not null) desc,
             coalesce(ub.granted_at, ub.created_at) desc nulls last,
             b.sort_order asc,
             b.level asc nulls last,
             b.created_at desc
    limit $${params.length}
    `,
    params,
  );

  return {
    items,
    summary: {
      total: items.length,
      unlocked: items.filter(item => item.unlocked).length,
      includeLocked,
    },
  };
}

export async function feedbacksRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 50, 200);
  const status = stringParam(ctx, 'status');
  const params: unknown[] = [tenantId, userId];
  const filters = ['r.tenant_id = $1', 'r.user_id = $2'];
  if (status) {
    params.push(status);
    filters.push(`r.status = $${params.length}`);
  }
  params.push(limit);

  const items = await query(
    `
    select r.id, r.question_id as "questionId", q.type as "questionType",
           r.type, r.category, r.title, r.description, r.status, r.priority,
           r.resolution, r.handled_by as "handledBy", r.handled_at as "handledAt",
           r.attachments, r.metadata, r.created_at as "createdAt", r.updated_at as "updatedAt"
    from public.reports r
    left join public.questions q on q.tenant_id = r.tenant_id and q.id = r.question_id
    where ${filters.join(' and ')}
    order by r.created_at desc
    limit $${params.length}
    `,
    params,
  );
  return { items };
}

export async function submitFeedbackRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const body = await readJsonBody(ctx);
  const userId = await userIdFrom(ctx, body);
  const questionId = nullableString(body.questionId);
  const type = optionalChoice(body.type, FEEDBACK_TYPES, 'question_error');
  const description = requiredString(body, 'description');
  const attachments = Array.isArray(body.attachments) ? body.attachments : [];

  const item = await transaction(async client => {
    if (questionId) {
      const question = await client.query<{ id: string }>(
        'select id from public.questions where tenant_id = $1 and id = $2 and status <> \'archived\' limit 1',
        [tenantId, questionId],
      );
      if (!question.rows[0]) throw new HttpError(404, 'Question not found for this tenant', 'QUESTION_NOT_FOUND');
    }

    const result = await client.query(
      `
      insert into public.reports (
        tenant_id, question_id, user_id, type, category, title, description,
        status, priority, contact, attachments, metadata
      )
      values ($1, $2::uuid, $3, $4, $5, $6, $7, 'pending', $8, $9, $10::jsonb, $11::jsonb)
      returning id, question_id as "questionId", user_id as "userId", type, category,
                title, description, status, priority, contact, attachments, metadata,
                created_at as "createdAt", updated_at as "updatedAt"
      `,
      [
        tenantId,
        questionId,
        userId,
        type,
        nullableString(body.category),
        nullableString(body.title),
        description,
        optionalChoice(body.priority, ['low', 'normal', 'high', 'urgent'], 'normal'),
        nullableString(body.contact),
        JSON.stringify(attachments),
        JSON.stringify(objectValue(body.metadata)),
      ],
    );

    await client.query(
      `
      insert into public.report_status_events (tenant_id, report_id, from_status, to_status, note, actor_user_id)
      values ($1, $2, null, 'pending', 'student submitted feedback', $3)
      `,
      [tenantId, result.rows[0].id, userId],
    );

    return result.rows[0];
  });

  return { item };
}

export async function examCountdownRoute(ctx: RequestContext) {
  const tenantId = await tenantIdFrom(ctx);
  const userId = await userIdFrom(ctx);
  const limit = intParam(ctx, 'limit', 5, 20);

  const profile = await queryOne<{
    regionId: string | null;
    selectedSchoolId: string | null;
  }>(
    `
    select region_id as "regionId", selected_school_id as "selectedSchoolId"
    from public.student_profiles
    where tenant_id = $1 and user_id = $2
    limit 1
    `,
    [tenantId, userId],
  );
  if (!profile) throw new HttpError(404, 'Student profile not found', 'PROFILE_NOT_FOUND');

  const items = await query<{
    id: string;
    examName: string;
    examDate: string | null;
  } & Record<string, unknown>>(
    `
    select id, region_id as "regionId", school_id as "schoolId",
           exam_name as "examName", exam_date::text as "examDate",
           exam_type as "examType", description, metadata,
           sort_order as "order", is_active as "isActive",
           created_at as "createdAt", updated_at as "updatedAt"
    from public.exam_dates
    where tenant_id = $1
      and is_active = true
      and ($2::uuid is null or region_id is null or region_id = $2::uuid)
      and ($3::uuid is null or school_id is null or school_id = $3::uuid)
    order by exam_date asc nulls last, sort_order asc
    limit $4
    `,
    [tenantId, profile.regionId, profile.selectedSchoolId, limit],
  );

  const today = new Date();
  return {
    items: items.map(item => ({
      ...item,
      daysLeft: daysBetween(item.examDate, today),
    })),
    target: {
      regionId: profile.regionId,
      schoolId: profile.selectedSchoolId,
    },
  };
}
